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

CREATE FUNCTION copybom(psitemid integer, ptitemid 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
  _result INTEGER;

BEGIN

  SELECT copyBOM (pSItemid, PTItemid, FALSE) into _result;

  RETURN _result;

END;
$$;


ALTER FUNCTION public.copybom(psitemid integer, ptitemid integer) OWNER TO admin;

--
-- TOC entry 1248 (class 1255 OID 146565793)
-- Dependencies: 4536 8
-- Name: copybom(integer, integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION copybom(psitemid integer, ptitemid integer, pcopyusedat 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
  _bh RECORD;
  _bi RECORD;
  _bomheadfound BOOLEAN := FALSE;
  _bomheadid INTEGER;
  _bomitemid INTEGER;
  _bomworksetid INTEGER;
  _temp INTEGER;
  _schedatwooper BOOLEAN;
  _booitemseqid INTEGER;

BEGIN

--  Cache source bomhead
  SELECT * INTO _bh
  FROM bomhead
  WHERE ((bomhead_item_id=pSItemid)
    AND  (bomhead_rev_id=getActiveRevID('BOM', pSItemid)));

--  bomhead may not exist
--  IF (NOT FOUND) THEN
--    RETURN -1;
--  END IF;
    IF (FOUND) THEN
      _bomheadfound := TRUE;
    END IF;

--  Make sure that source bomitems exist
  SELECT bomitem_id INTO _bomitemid
  FROM bomitem
  WHERE ((bomitem_parent_item_id=pSItemid)
    AND  (bomitem_rev_id=getActiveRevID('BOM', pSItemid)))
  LIMIT 1;

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

--  Make sure that target bomitems do not exist
  SELECT bomitem_id INTO _bomitemid
  FROM bomitem
  WHERE ((bomitem_parent_item_id=pTItemid)
    AND  (bomitem_rev_id= -1))
  LIMIT 1;

  IF (FOUND) THEN
    RETURN -3;
  END IF;

--  Make sure that the parent is not used in the component at some level
  IF ( SELECT (item_type IN ('M', 'F'))
       FROM item
       WHERE (item_id=pSItemid) ) THEN
    SELECT indentedWhereUsed(pTItemid) INTO _bomworksetid;
    SELECT bomwork_id INTO _temp
    FROM bomwork
    WHERE ( (bomwork_set_id=_bomworksetid)
     AND (bomwork_item_id=pSItemid) )
    LIMIT 1;
    IF (FOUND) THEN
      PERFORM deleteBOMWorkset(_bomworksetid);
      RETURN -4;
    END IF;
    PERFORM deleteBOMWorkset(_bomworksetid);
  END IF;

--  Check for existing target bomhead
  SELECT bomhead_id INTO _bomheadid
  FROM bomhead
  WHERE ((bomhead_item_id=pTItemid)
    AND  (bomhead_rev_id= -1));

  IF (NOT FOUND) THEN
    IF (_bomheadfound) THEN
      INSERT INTO bomhead
      ( bomhead_item_id, bomhead_serial, bomhead_docnum,
        bomhead_batchsize, bomhead_requiredqtyper )
      VALUES
      ( pTItemid, _bh.bomhead_serial, _bh.bomhead_docnum,
        _bh.bomhead_batchsize, _bh.bomhead_requiredqtyper );
    END IF;
  END IF;

  FOR _bi IN SELECT bomitem.*
             FROM bomitem(pSItemid) 
             WHERE (bomitem_expires>CURRENT_DATE) LOOP

    SELECT NEXTVAL('bomitem_bomitem_id_seq') INTO _bomitemid;

    IF (pCopyUsedAt) THEN
      _schedatwooper := _bi.bomitem_schedatwooper;
      _booitemseqid := _bi.bomitem_booitem_seq_id;
    ELSE
      _schedatwooper := FALSE;
      _booitemseqid := -1;
    END IF;

    INSERT INTO bomitem
    ( bomitem_id, bomitem_parent_item_id, bomitem_seqnumber, bomitem_item_id,
      bomitem_uom_id, bomitem_qtyfxd, bomitem_qtyper, bomitem_scrap, bomitem_schedatwooper,
      bomitem_booitem_seq_id,
      bomitem_effective, bomitem_expires, bomitem_ecn,
      bomitem_createwo, bomitem_issuemethod, bomitem_moddate, bomitem_subtype,
      bomitem_notes, bomitem_ref )
    VALUES
    ( _bomitemid, pTItemid, _bi.bomitem_seqnumber, _bi.bomitem_item_id,
      _bi.bomitem_uom_id, _bi.bomitem_qtyfxd, _bi.bomitem_qtyper, _bi.bomitem_scrap, _schedatwooper,
      _booitemseqid,
      CURRENT_DATE, _bi.bomitem_expires, _bi.bomitem_ecn,
      _bi.bomitem_createwo, _bi.bomitem_issuemethod, CURRENT_DATE, _bi.bomitem_subtype,
      _bi.bomitem_notes, _bi.bomitem_ref );

    INSERT INTO bomitemsub
    ( bomitemsub_bomitem_id, bomitemsub_item_id,
      bomitemsub_uomratio, bomitemsub_rank )
    SELECT _bomitemid, bomitemsub_item_id,
           bomitemsub_uomratio, bomitemsub_rank
    FROM bomitemsub
    WHERE (bomitemsub_bomitem_id=_bi.bomitem_id);

  END LOOP;

  RETURN pTItemid;

END;
$$;


ALTER FUNCTION public.copybom(psitemid integer, ptitemid integer, pcopyusedat boolean) OWNER TO admin;

--
-- TOC entry 1249 (class 1255 OID 146565794)
-- Dependencies: 4536 8
-- Name: copybudget(integer, text, text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION copybudget(integer, text, text, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pBudgheadid ALIAS FOR $1;
  pName ALIAS FOR $2;
  pDescrip ALIAS FOR $3;
  pInterval ALIAS FOR $4;
  _budgheadid INTEGER;
  _periodid INTEGER;
  _result INTEGER;

BEGIN
  SELECT 1 INTO _result
    FROM budgitem
   WHERE ((budgitem_budghead_id=pBudgheadid)
     AND  (nextPeriodByInterval(budgitem_period_id, pInterval)=-1))
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  SELECT nextval('budghead_budghead_id_seq') INTO _budgheadid;
  INSERT INTO budghead
        (budghead_id, budghead_name, budghead_descrip)
  VALUES(_budgheadid, pName, pDescrip);

  INSERT INTO budgitem (budgitem_budghead_id, budgitem_period_id,
                        budgitem_accnt_id, budgitem_amount)
  SELECT _budgheadid, nextPeriodByInterval(budgitem_period_id, pInterval),
         budgitem_accnt_id, budgitem_amount
    FROM budgitem
   WHERE (budgitem_budghead_id=pBudgheadid);

  RETURN _budgheadid;
END;
$_$;


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

--
-- TOC entry 1250 (class 1255 OID 146565795)
-- Dependencies: 4536 8
-- Name: copycmd(integer, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION copycmd(integer, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCmdId	ALIAS FOR $1;
  pModule	ALIAS FOR $2;
  pTitle	ALIAS FOR $3;
  _cmdId	INTEGER;
BEGIN
    SELECT nextval('cmd_cmd_id_seq') INTO _cmdId;

    INSERT INTO cmd 
      SELECT _cmdId, pModule, pTitle, cmd_descrip, cmd_privname, cmd_executable
      FROM cmd
      WHERE (cmd_id=pCmdId);

    INSERT INTO cmdarg (cmdarg_cmd_id, cmdarg_order, cmdarg_arg)
      SELECT _cmdId, cmdarg_order, cmdarg_arg
      FROM cmdarg
      WHERE (cmdarg_cmd_id=pCmdId);

    RETURN 1;
END;
$_$;


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

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

CREATE FUNCTION copycontract(pcontrctid integer, pnumber text, peffective date, pexpires 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
  _contrctid INTEGER;
  _itemsrcid INTEGER;
  _r RECORD;

BEGIN

  INSERT INTO contrct
  ( contrct_number,
    contrct_vend_id,
    contrct_descrip,
    contrct_effective,
    contrct_expires,
    contrct_note )
  SELECT
    pNumber,
    contrct_vend_id,
    contrct_descrip,
    pEffective,
    pExpires,
    contrct_note
  FROM contrct
  WHERE (contrct_id=pContrctid)
  RETURNING contrct_id INTO _contrctid;

  FOR _r IN
  SELECT * FROM itemsrc WHERE (itemsrc_contrct_id=pContrctid)
  LOOP
  INSERT INTO itemsrc
    ( itemsrc_item_id,
      itemsrc_vend_id,
      itemsrc_vend_item_number,
      itemsrc_vend_item_descrip,
      itemsrc_comments,
      itemsrc_vend_uom,
      itemsrc_invvendoruomratio,
      itemsrc_minordqty,
      itemsrc_multordqty,
      itemsrc_leadtime,
      itemsrc_ranking,
      itemsrc_active,
      itemsrc_manuf_name,
      itemsrc_manuf_item_number,
      itemsrc_manuf_item_descrip,
      itemsrc_default,
      itemsrc_upccode,
      itemsrc_effective,
      itemsrc_expires,
      itemsrc_contrct_id )
    VALUES
    ( _r.itemsrc_item_id,
      _r.itemsrc_vend_id,
      _r.itemsrc_vend_item_number,
      _r.itemsrc_vend_item_descrip,
      _r.itemsrc_comments,
      _r.itemsrc_vend_uom,
      _r.itemsrc_invvendoruomratio,
      _r.itemsrc_minordqty,
      _r.itemsrc_multordqty,
      _r.itemsrc_leadtime,
      _r.itemsrc_ranking,
      _r.itemsrc_active,
      _r.itemsrc_manuf_name,
      _r.itemsrc_manuf_item_number,
      _r.itemsrc_manuf_item_descrip,
      _r.itemsrc_default,
      _r.itemsrc_upccode,
      pEffective,
      pExpires,
      _contrctid )
    RETURNING itemsrc_id INTO _itemsrcid;

  INSERT INTO itemsrcp
    ( itemsrcp_itemsrc_id,
      itemsrcp_qtybreak,
      itemsrcp_price,
      itemsrcp_updated,
      itemsrcp_curr_id,
      itemsrcp_dropship,
      itemsrcp_warehous_id,
      itemsrcp_type,
      itemsrcp_discntprcnt,
      itemsrcp_fixedamtdiscount )
    SELECT
      _itemsrcid,
      itemsrcp_qtybreak,
      itemsrcp_price,
      CURRENT_DATE,
      itemsrcp_curr_id,
      itemsrcp_dropship,
      itemsrcp_warehous_id,
      itemsrcp_type,
      itemsrcp_discntprcnt,
      itemsrcp_fixedamtdiscount
    FROM itemsrcp
    WHERE (itemsrcp_itemsrc_id=_r.itemsrc_id);

  END LOOP;

  RETURN _contrctid;

END;
$$;


ALTER FUNCTION public.copycontract(pcontrctid integer, pnumber text, peffective date, pexpires date) OWNER TO admin;

--
-- TOC entry 1252 (class 1255 OID 146565797)
-- Dependencies: 4536 8
-- Name: copyfinancialgroup(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION copyfinancialgroup(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
  pSourceGroup ALIAS FOR $1;
  pFlheadid ALIAS FOR $2;
  pParentFlgrpid ALIAS FOR $3;

  _flgrpid INTEGER;

BEGIN

  SELECT nextval('flgrp_flgrp_id_seq') INTO _flgrpid;

-- Copy the group item
  INSERT INTO flgrp
         (flgrp_id, flgrp_flhead_id, flgrp_flgrp_id,
          flgrp_order, flgrp_name, flgrp_descrip,
          flgrp_subtotal, flgrp_summarize, flgrp_subtract,
          flgrp_showstart, flgrp_showend,
          flgrp_showdelta, flgrp_showbudget, flgrp_showdiff, flgrp_showcustom,
          flgrp_showstartprcnt, flgrp_showendprcnt,
          flgrp_showdeltaprcnt, flgrp_showbudgetprcnt, flgrp_showdiffprcnt, flgrp_showcustomprcnt,
          flgrp_usealtsubtotal, flgrp_altsubtotal,flgrp_prcnt_flgrp_id)
  SELECT _flgrpid, pFlheadid, pParentFlgrpid,
         flgrp_order, flgrp_name, flgrp_descrip,
         flgrp_subtotal, flgrp_summarize, flgrp_subtract,
         flgrp_showstart, flgrp_showend,
         flgrp_showdelta, flgrp_showbudget, flgrp_showdiff, flgrp_showcustom,
         flgrp_showstartprcnt, flgrp_showendprcnt,
         flgrp_showdeltaprcnt, flgrp_showbudgetprcnt, flgrp_showdiffprcnt, flgrp_showcustomprcnt,
         flgrp_usealtsubtotal, flgrp_altsubtotal,flgrp_prcnt_flgrp_id
    FROM flgrp
   WHERE (flgrp_id=pSourceGroup);

-- Store temporary cross ref info
   
   EXECUTE ' INSERT INTO tmp_flgrpxref' || getEffectiveXtUser() || ' (flgrpxref_oldid,flgrpxref_newid) VALUES (' || pSourceGroup || ',' || _flgrpid || ');';

-- Copy any children flitems
  INSERT INTO flitem
         (flitem_flhead_id, flitem_flgrp_id,
          flitem_order, flitem_accnt_id, flitem_showstart,
          flitem_showend, flitem_showdelta, flitem_showbudget, flitem_showdiff, flitem_showcustom,
          flitem_subtract, flitem_showstartprcnt,
          flitem_showendprcnt, flitem_showdeltaprcnt,
          flitem_showbudgetprcnt, flitem_showdiffprcnt, flitem_showcustomprcnt,
          flitem_custom_source, flitem_company, flitem_profit, flitem_number,
          flitem_sub, flitem_type, flitem_subaccnttype_code, flitem_prcnt_flgrp_id)
  SELECT pFlheadid, _flgrpid,
         flitem_order, flitem_accnt_id, flitem_showstart,
         flitem_showend, flitem_showdelta, flitem_showbudget, flitem_showdiff, flitem_showcustom,
         flitem_subtract, flitem_showstartprcnt,
         flitem_showendprcnt, flitem_showdeltaprcnt,
         flitem_showbudgetprcnt, flitem_showdiffprcnt, flitem_showcustomprcnt,
         flitem_custom_source, flitem_company, flitem_profit, flitem_number,
          flitem_sub, flitem_type, flitem_subaccnttype_code, flitem_prcnt_flgrp_id
    FROM flitem
   WHERE (flitem_flgrp_id=pSourceGroup);

-- Copy any children flspecs
  INSERT INTO flspec
         (flspec_flhead_id, flspec_flgrp_id,
          flspec_order, flspec_name, flspec_type, flspec_showstart,
          flspec_showend, flspec_showdelta, flspec_showbudget, flspec_showdiff, flspec_showcustom,
          flspec_subtract, flspec_showstartprcnt,
          flspec_showendprcnt, flspec_showdeltaprcnt,
          flspec_showbudgetprcnt, flspec_showdiffprcnt, flspec_showcustomprcnt,
          flspec_custom_source, flspec_prcnt_flgrp_id)
  SELECT pFlheadid, _flgrpid,
         flspec_order, flspec_name, flspec_type, flspec_showstart,
         flspec_showend, flspec_showdelta, flspec_showbudget, flspec_showdiff, flspec_showcustom,
         flspec_subtract, flspec_showstartprcnt,
         flspec_showendprcnt, flspec_showdeltaprcnt,
         flspec_showbudgetprcnt, flspec_showdiffprcnt, flspec_showcustomprcnt,
         flspec_custom_source, flspec_prcnt_flgrp_id
    FROM flspec
   WHERE (flspec_flgrp_id=pSourceGroup);

-- Copy the groups
  PERFORM copyFinancialGroup(flgrp_id, pFlheadid, _flgrpid)
     FROM flgrp
    WHERE (flgrp_flgrp_id=pSourceGroup);

  RETURN _flgrpid;
END;
$_$;


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

--
-- TOC entry 1253 (class 1255 OID 146565798)
-- Dependencies: 4536 8
-- Name: copyfinanciallayout(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

  _flheadid INTEGER;
  _tblName TEXT;

BEGIN

-- Check for the flhead to be copy that it exists
  PERFORM flhead_id
     FROM flhead
    WHERE (flhead_id=pSourceFlheadid);
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

-- Check that the name is valid
  IF (pDestName IS NULL OR pDestName = '') THEN
    RETURN -2;
  END IF;

-- Check for the name to copy to does not exist
  PERFORM flhead_id
     FROM flhead
    WHERE (flhead_name=pDestName);
  IF (FOUND) THEN
    RETURN -3;
  END IF;

-- Copy the flhead record
  SELECT nextval('flhead_flhead_id_seq') INTO _flheadid;
  INSERT INTO flhead
         (flhead_id, flhead_name, flhead_descrip,
          flhead_showtotal, flhead_showstart,
          flhead_showend, flhead_showdelta, flhead_showbudget,
          flhead_showdiff, flhead_showcustom,
          flhead_custom_label,
          flhead_usealttotal, flhead_alttotal,
          flhead_usealtbegin, flhead_altbegin,
          flhead_usealtend, flhead_altend,
          flhead_usealtdebits, flhead_altdebits,
          flhead_usealtcredits, flhead_altcredits,
          flhead_usealtbudget, flhead_altbudget,
          flhead_usealtdiff, flhead_altdiff,
          flhead_type, flhead_active, flhead_sys
)
  SELECT _flheadid, pDestName, flhead_descrip,
         flhead_showtotal, flhead_showstart,
         flhead_showend, flhead_showdelta, flhead_showbudget,
         flhead_showdiff, flhead_showcustom,
         flhead_custom_label,
         flhead_usealttotal, flhead_alttotal,
         flhead_usealtbegin, flhead_altbegin,
         flhead_usealtend, flhead_altend,
         flhead_usealtdebits, flhead_altdebits,
         flhead_usealtcredits, flhead_altcredits,
         flhead_usealtbudget, flhead_altbudget,
         flhead_usealtdiff, flhead_altdiff,
         flhead_type, flhead_active, false
    FROM flhead
   WHERE (flhead_id=pSourceFlheadid);

-- Create temporary table so old and new group ids can be stored
 SELECT relname FROM pg_class INTO _tblName
 WHERE relname = 'tmp_flgrpxref';
 IF (_tblName IS NULL) THEN
  EXECUTE 'CREATE TEMPORARY TABLE tmp_flgrpxref' || getEffectiveXtUser() || ' 
  (
	flgrpxref_oldid int4,
	flgrpxref_newid int4
  ) ON COMMIT DROP;';
  END IF;

-- Copy the top level groups
  PERFORM copyFinancialGroup(flgrp_id, _flheadid, -1)
     FROM flgrp
    WHERE ((flgrp_flhead_id=pSourceFlheadid)
      AND  (flgrp_flgrp_id=-1));

-- Update Group Percent settings
  EXECUTE 'UPDATE flgrp
  SET flgrp_prcnt_flgrp_id=flgrpxref_newid
  FROM tmp_flgrpxref' || getEffectiveXtUser() || ' 
  WHERE ((flgrp_flhead_id=' || _flheadid || ')
  AND (flgrp_prcnt_flgrp_id=flgrpxref_oldid));';

  EXECUTE 'UPDATE flitem
  SET flitem_prcnt_flgrp_id=flgrpxref_newid
  FROM tmp_flgrpxref' || getEffectiveXtUser() || ' 
  WHERE ((flitem_flhead_id=' || _flheadid || ')
  AND (flitem_prcnt_flgrp_id=flgrpxref_oldid));';

  EXECUTE 'UPDATE flspec
  SET flspec_prcnt_flgrp_id=flgrpxref_newid
  FROM tmp_flgrpxref' || getEffectiveXtUser() || ' 
  WHERE ((flspec_flhead_id=' || _flheadid || ')
  AND (flspec_prcnt_flgrp_id=flgrpxref_oldid));';

-- Copy Column Layounts
  INSERT INTO flcol
        (flcol_flhead_id,
        flcol_name,
        flcol_descrip,
        flcol_report_id,
        flcol_month,
        flcol_quarter,
        flcol_year,
        flcol_showdb,
        flcol_prcnt,
        flcol_priortype,
        flcol_priormonth,
        flcol_priorquarter,
        flcol_prioryear,
        flcol_priorprcnt,
        flcol_priordiff,
        flcol_priordiffprcnt,
        flcol_budget,
        flcol_budgetprcnt,
        flcol_budgetdiff,
        flcol_budgetdiffprcnt
)
SELECT
        _flheadid,flcol_name,flcol_descrip,
        flcol_report_id,flcol_month,flcol_quarter,
        flcol_year,flcol_showdb,flcol_prcnt,
        flcol_priortype,flcol_priormonth,flcol_priorquarter,
        flcol_prioryear,flcol_priorprcnt,flcol_priordiff,
        flcol_priordiffprcnt,flcol_budget,flcol_budgetprcnt,
        flcol_budgetdiff,flcol_budgetdiffprcnt
FROM flcol
WHERE (flcol_flhead_id=pSourceFlheadid);

  RETURN _flheadid;
END;
$_$;


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

--
-- TOC entry 1214 (class 1255 OID 146565799)
-- Dependencies: 4536 8
-- Name: copyglseries(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION copyglseries(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;
  _sequence INTEGER := fetchGLSequence();
  _journal INTEGER;	

BEGIN

  SELECT gltrans_journalnumber INTO _journal
  FROM gltrans
  WHERE ( gltrans_sequence=pSequence )
  LIMIT 1;

  IF (FOUND) THEN
    INSERT INTO glseries
    ( glseries_sequence, glseries_source, glseries_doctype, glseries_docnumber,
      glseries_notes, glseries_accnt_id, glseries_amount, glseries_distdate )
    SELECT _sequence, gltrans_source, gltrans_doctype, gltrans_docnumber,
           gltrans_notes, gltrans_accnt_id,
           gltrans_amount, gltrans_date
    FROM gltrans
    WHERE ( gltrans_sequence=pSequence );
  ELSE
    RAISE EXCEPTION 'g/l transaction sequence not found';
  END IF;

  RETURN _sequence;
END;
$_$;


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

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

CREATE FUNCTION copyincdt(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
  pparentid   ALIAS FOR $1;
  ptimestamp  TIMESTAMP WITH TIME ZONE := COALESCE($2, CURRENT_TIMESTAMP);

  _alarmid    INTEGER;
  _incdtid    INTEGER;
  _todoitemid INTEGER;

BEGIN
  INSERT INTO incdt(incdt_number,          incdt_crmacct_id,
                    incdt_cntct_id,        incdt_summary,
                    incdt_descrip,         incdt_item_id,
                    incdt_timestamp,       incdt_incdtcat_id,
                    incdt_incdtseverity_id,incdt_incdtpriority_id,
                    incdt_owner_username,  incdt_recurring_incdt_id
           ) SELECT fetchIncidentNumber(), incdt_crmacct_id,
                    incdt_cntct_id,        incdt_summary,
                    incdt_descrip,         incdt_item_id,
                    ptimestamp,            incdt_incdtcat_id,
                    incdt_incdtseverity_id,incdt_incdtpriority_id,
                    incdt_owner_username,  incdt_recurring_incdt_id
               FROM incdt
              WHERE (incdt_id=pparentid)
  RETURNING incdt_id INTO _incdtid;

  IF (_incdtid IS NULL) THEN
    RETURN -10;
  END IF;

  SELECT MIN(copyTodoitem(todoitem_id, CAST(ptimestamp AS DATE), _incdtid))
            INTO _todoitemid
    FROM todoitem
   WHERE (todoitem_incdt_id=pparentid);

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

  SELECT saveAlarm(NULL, NULL, CAST(ptimestamp AS DATE),
                   CAST(alarm_time - DATE_TRUNC('day',alarm_time) AS TIME),
                   alarm_time_offset,
                   alarm_time_qualifier,
                   alarm_event_recipient  IS NOT NULL, alarm_event_recipient,
                   alarm_email_recipient  IS NOT NULL, alarm_email_recipient,
                   alarm_sysmsg_recipient IS NOT NULL, alarm_sysmsg_recipient,
                   'INCDT', _incdtid, 'CHANGEONE')
    INTO _alarmid
    FROM alarm
   WHERE ((alarm_source='INCDT')
      AND (alarm_source_id=pparentid));

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

   INSERT INTO docass (docass_source_id, docass_source_type,
                       docass_target_id, docass_target_type, docass_purpose
              ) SELECT _incdtid,       'INCDT',
                       docass_target_id, docass_target_type, docass_purpose
                  FROM docass
                 WHERE ((docass_source_id=pparentid)
                    AND (docass_source_type='INCDT'));

  RETURN _incdtid;
END;
$_$;


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

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

CREATE FUNCTION copyinvoice(integer, date) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pInvcheadid ALIAS FOR $1;
  _invcheadid INTEGER;
  _invcnumber TEXT;
  _invcdate DATE := COALESCE($2, CURRENT_DATE);
  _i RECORD;
  _l RECORD;
  _invcitemid INTEGER;

BEGIN
  SELECT *
    INTO _i
    FROM invchead
   WHERE(invchead_id=pInvcheadid);
  IF(NOT FOUND) THEN
    RETURN -1;
  END IF;

  _invcnumber := fetchInvcNumber();
  _invcheadid := nextval('invchead_invchead_id_seq');

  INSERT INTO invchead
        (invchead_id,
         invchead_cust_id, invchead_shipto_id,
         invchead_ordernumber, invchead_orderdate,
         invchead_posted, invchead_printed,
         invchead_invcnumber, invchead_invcdate, invchead_shipdate,
         invchead_ponumber, invchead_shipvia,
         invchead_fob, invchead_billto_name,
         invchead_billto_address1, invchead_billto_address2,
         invchead_billto_address3, invchead_billto_city,
         invchead_billto_state, invchead_billto_zipcode,
         invchead_billto_phone, invchead_shipto_name,
         invchead_shipto_address1, invchead_shipto_address2,
         invchead_shipto_address3, invchead_shipto_city,
         invchead_shipto_state, invchead_shipto_zipcode,
         invchead_shipto_phone, invchead_salesrep_id,
         invchead_commission,
         invchead_terms_id, invchead_freight,
         invchead_misc_amount,
         invchead_misc_descrip, invchead_misc_accnt_id,
         invchead_payment, invchead_paymentref,
         invchead_notes,
         invchead_billto_country, invchead_shipto_country,
         invchead_prj_id, invchead_curr_id,
         invchead_taxzone_id,
         invchead_recurring_invchead_id,
         invchead_saletype_id, invchead_shipzone_id)
  VALUES(_invcheadid,
         _i.invchead_cust_id, _i.invchead_shipto_id,
         _i.invchead_ordernumber, _i.invchead_orderdate,
         false, false,
         _invcnumber, _invcdate, _i.invchead_shipdate,
         _i.invchead_ponumber, _i.invchead_shipvia,
         _i.invchead_fob, _i.invchead_billto_name,
         _i.invchead_billto_address1, _i.invchead_billto_address2,
         _i.invchead_billto_address3, _i.invchead_billto_city,
         _i.invchead_billto_state, _i.invchead_billto_zipcode,
         _i.invchead_billto_phone, _i.invchead_shipto_name,
         _i.invchead_shipto_address1, _i.invchead_shipto_address2,
         _i.invchead_shipto_address3, _i.invchead_shipto_city,
         _i.invchead_shipto_state, _i.invchead_shipto_zipcode,
         _i.invchead_shipto_phone, _i.invchead_salesrep_id,
         _i.invchead_commission,
         _i.invchead_terms_id, _i.invchead_freight,
         _i.invchead_misc_amount,
         _i.invchead_misc_descrip, _i.invchead_misc_accnt_id,
         _i.invchead_payment, _i.invchead_paymentref,
         _i.invchead_notes,
         _i.invchead_billto_country, _i.invchead_shipto_country,
         _i.invchead_prj_id, _i.invchead_curr_id,
         _i.invchead_taxzone_id,
         _i.invchead_recurring_invchead_id,
         _i.invchead_saletype_id, _i.invchead_shipzone_id);

  FOR _l IN SELECT *
            FROM invcitem
            WHERE (invcitem_invchead_id=pInvcheadid) LOOP
    SELECT NEXTVAL('invcitem_invcitem_id_seq') INTO _invcitemid;

    INSERT INTO invcitem
        (invcitem_id, invcitem_invchead_id,
         invcitem_linenumber, invcitem_item_id,
         invcitem_warehous_id, invcitem_custpn,
         invcitem_number, invcitem_descrip,
         invcitem_ordered, invcitem_billed,
         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_coitem_id)
    VALUES
        (_invcitemid, _invcheadid,
         _l.invcitem_linenumber, _l.invcitem_item_id,
         _l.invcitem_warehous_id, _l.invcitem_custpn,
         _l.invcitem_number, _l.invcitem_descrip,
         _l.invcitem_ordered, _l.invcitem_billed,
         _l.invcitem_custprice, _l.invcitem_price,
         _l.invcitem_notes, _l.invcitem_salescat_id,
         _l.invcitem_taxtype_id,
         _l.invcitem_qty_uom_id, _l.invcitem_qty_invuomratio,
         _l.invcitem_price_uom_id, _l.invcitem_price_invuomratio,
         _l.invcitem_coitem_id);

  END LOOP;

  RETURN _invcheadid;
END;
$_$;


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

--
-- TOC entry 1256 (class 1255 OID 146565802)
-- Dependencies: 4536 8
-- Name: copyitem(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT NEXTVAL('item_item_id_seq') INTO _itemid;
  INSERT INTO item
  ( item_id, item_number, item_descrip1, item_descrip2,
    item_classcode_id, item_type,
    item_active, item_picklist, item_sold, item_fractional,
    item_maxcost, item_prodweight, item_packweight,
    item_prodcat_id,item_exclusive, item_listprice, item_listcost,
    item_config, item_comments, item_extdescrip,
    item_upccode, item_inv_uom_id, item_price_uom_id )
  SELECT _itemid, pTItemNumber, item_descrip1, item_descrip2,
         item_classcode_id, item_type,
         item_active, item_picklist, item_sold, item_fractional,
         item_maxcost, item_prodweight, item_packweight,
         item_prodcat_id, item_exclusive, item_listprice, item_listcost,
         item_config, item_comments, item_extdescrip,
         item_upccode, item_inv_uom_id, item_price_uom_id
  FROM item
  WHERE (item_id=pSItemid);

  INSERT INTO imageass
  (imageass_source_id, imageass_source, imageass_image_id, imageass_purpose)
  SELECT _itemid, 'I', imageass_image_id, imageass_purpose
  FROM imageass
  WHERE ((imageass_source_id=pSItemid)
  AND (imageass_source='I'));
  
  INSERT INTO url
  (url_source_id, url_source, url_title, url_url)
  SELECT _itemid, 'I', url_title, url_url
  FROM url
  WHERE ((url_source_id=pSItemid)
  AND (url_source='I'));

  INSERT INTO itemtax
        (itemtax_item_id, itemtax_taxzone_id, itemtax_taxtype_id)
  SELECT _itemid, itemtax_taxzone_id, itemtax_taxtype_id
    FROM itemtax
   WHERE(itemtax_item_id=pSItemid);

  INSERT INTO charass
  ( charass_target_type, charass_target_id,
    charass_char_id, charass_value )
  SELECT 'I', _itemid, charass_char_id, charass_value
  FROM charass
  WHERE ( (charass_target_type='I')
   AND (charass_target_id=pSItemid) );

  FOR _r IN SELECT itemuomconv_id,
                   itemuomconv_from_uom_id,
                   itemuomconv_from_value,
                   itemuomconv_to_uom_id,
                   itemuomconv_to_value,
                   itemuomconv_fractional
              FROM itemuomconv
             WHERE(itemuomconv_item_id=pSItemid) LOOP
    SELECT nextval('itemuomconv_itemuomconv_id_seq') INTO _id;
    INSERT INTO itemuomconv
          (itemuomconv_id, itemuomconv_item_id,
           itemuomconv_from_uom_id, itemuomconv_from_value,
           itemuomconv_to_uom_id, itemuomconv_to_value,
           itemuomconv_fractional)
    VALUES(_id, _itemid,
           _r.itemuomconv_from_uom_id, _r.itemuomconv_from_value,
           _r.itemuomconv_to_uom_id, _r.itemuomconv_to_value,
           _r.itemuomconv_fractional);

    INSERT INTO itemuom
          (itemuom_itemuomconv_id, itemuom_uomtype_id)
    SELECT _id, itemuom_uomtype_id
      FROM itemuom
     WHERE(itemuom_itemuomconv_id=_r.itemuomconv_id);
  END LOOP;

  RETURN _itemid;

END;
$_$;


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

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

CREATE FUNCTION copyitem(integer, text, 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
  pSItemid      ALIAS FOR $1;
  pTItemNumber  ALIAS FOR $2;
  pCopyBOM      ALIAS FOR $3;
  pCopyCosts    ALIAS FOR $4;

  _itemid       INTEGER;
BEGIN

  _itemid := copyItem(pSItemid, pTItemNumber);

  IF (pCopyBOM) THEN
    PERFORM copyBOM(pSItemid, _itemid, FALSE);
  END IF;

  IF (pCopyCosts) THEN
    INSERT INTO itemcost
    ( itemcost_item_id, itemcost_costelem_id, itemcost_lowlevel,
      itemcost_stdcost, itemcost_posted,
      itemcost_actcost, itemcost_curr_id, itemcost_updated )
    SELECT _itemid, itemcost_costelem_id, itemcost_lowlevel,
      itemcost_stdcost, CURRENT_DATE,
      itemcost_actcost, itemcost_curr_id, CURRENT_DATE
    FROM itemcost
    WHERE (itemcost_item_id=pSItemid);
  END IF;

  RETURN _itemid;

END;
$_$;


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

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

CREATE FUNCTION copyitem(integer, text, boolean, boolean, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSItemid ALIAS FOR $1;
  pTItemNumber ALIAS FOR $2;
  pCopyBOM ALIAS FOR $3;
  pCopyBOO ALIAS FOR $4;        -- deprecated - xtmfg-specific
  pCopyCosts ALIAS FOR $5;
BEGIN
  RAISE NOTICE 'copyItem(INTEGER, TEXT, BOOLEAN, BOOLEAN, BOOLEAN) has been deprecated.  Use copyItem(INTEGER, TEXT) or copyItem(INTEGER, TEXT, BOOLEAN, BOOLEAN) or a package-specific version instead.';
  RETURN copyItem(pSItemid, pTItemNumber, pCopyBOM, pCopyCosts);
END;
$_$;


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

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

CREATE FUNCTION copyitem(integer, text, boolean, boolean, boolean, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSItemid ALIAS FOR $1;
  pTItemNumber ALIAS FOR $2;
  pCopyBOM ALIAS FOR $3;
  pCopyBOO ALIAS FOR $4;        -- deprecated - xtmfg-specific
  pCopyCosts ALIAS FOR $5;
  pCopyUsedAt ALIAS FOR $6;     -- deprecated - xtmfg-specific
BEGIN
  RETURN copyItem(pSItemid, pTItemNumber, pCopyBOM, pCopyCosts);
END;
$_$;


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

--
-- TOC entry 1260 (class 1255 OID 146565806)
-- Dependencies: 4536 8
-- Name: copyitemsite(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION copyitemsite(pitemsiteid integer, pdestwhsid 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 copyItemSite(pitemsiteid, pdestwhsid, NULL);

END;
$$;


ALTER FUNCTION public.copyitemsite(pitemsiteid integer, pdestwhsid integer) OWNER TO admin;

--
-- TOC entry 1261 (class 1255 OID 146565807)
-- Dependencies: 4536 8
-- Name: copyitemsite(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION copyitemsite(pitemsiteid integer, pdestwhsid integer, pdestitemid 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;
  pdestwhsid	ALIAS FOR $2;
  _destwhs	whsinfo%ROWTYPE;
  _new		itemsite%ROWTYPE;

BEGIN
  -- make a copy of the old itemsite
  SELECT * INTO _new
  FROM itemsite
  WHERE (itemsite_id=pitemsiteid);
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  -- if there is no dest warehouse then perhaps the user is manually copying it
  IF (pdestwhsid IS NOT NULL) THEN
    SELECT * INTO _destwhs
    FROM whsinfo
    WHERE (warehous_id=pdestwhsid);
    IF (NOT FOUND) THEN
      RETURN -2;
    END IF;
  END IF;

  IF (NOT checkPrivilege('MaintainItemSites')) THEN
    RETURN -3;
  END IF;

  SELECT itemsite_id INTO _new.itemsite_id
  FROM itemsite
  WHERE ((itemsite_item_id=COALESCE(pdestitemid, _new.itemsite_item_id))
    AND  (itemsite_warehous_id=pdestwhsid OR
	  (itemsite_warehous_id IS NULL AND pdestwhsid IS NULL)));
  IF (FOUND) THEN
    RETURN _new.itemsite_id;
  END IF;

  -- now override the things we know have to change
  _new.itemsite_id		:= NEXTVAL('itemsite_itemsite_id_seq');
  _new.itemsite_warehous_id	:= pdestwhsid;
  _new.itemsite_qtyonhand	:= 0;
  _new.itemsite_value           := 0;
  _new.itemsite_datelastcount	:= NULL;
  _new.itemsite_datelastused	:= NULL;
  _new.itemsite_nnqoh		:= 0;
  _new.itemsite_location_id    := -1;

  IF (_destwhs.warehous_transit) THEN
    _new.itemsite_reorderlevel	:= 0;
    _new.itemsite_ordertoqty	:= 0;
    _new.itemsite_soldranking	:= NULL;
    _new.itemsite_posupply	:= FALSE;
    _new.itemsite_wosupply	:= FALSE;
    _new.itemsite_loccntrl	:= FALSE;
    _new.itemsite_safetystock	:= 0;
    _new.itemsite_minordqty	:= 0;
    _new.itemsite_multordqty	:= 0;
    _new.itemsite_leadtime	:= 0;
    _new.itemsite_controlmethod	:= 'R';
    IF(_new.itemsite_costmethod='N') THEN
      _new.itemsite_costmethod := 'S';
    END IF;
    _new.itemsite_active	:= TRUE;
    -- ? _new.itemsite_plancode_id	:= -1;
    -- ? _new.itemsite_costcat_id	:= -1;
    _new.itemsite_eventfence	:= 1;
    _new.itemsite_sold		:= FALSE;
    _new.itemsite_stocked	:= FALSE;
    _new.itemsite_location_id	:= -1;
    _new.itemsite_useparams	:= FALSE;
    _new.itemsite_useparamsmanual := FALSE;
    _new.itemsite_createpr	:= FALSE;
    _new.itemsite_location	:= NULL;
    _new.itemsite_location_comments := NULL;
    _new.itemsite_notes		:= 'Transit Warehouse';
    _new.itemsite_nnqoh		:= 0;
    _new.itemsite_createwo	:= FALSE;
    _new.itemsite_costcat_id	:= _destwhs.warehous_costcat_id;
  END IF;

  INSERT INTO itemsite (
    itemsite_id,			itemsite_item_id,
    itemsite_warehous_id,		itemsite_qtyonhand,
    itemsite_costmethod,                itemsite_value,
    itemsite_reorderlevel,		itemsite_ordertoqty,
    itemsite_cyclecountfreq,		itemsite_datelastcount,
    itemsite_datelastused,
    itemsite_posupply,			itemsite_wosupply,
    itemsite_loccntrl,
    itemsite_safetystock,		itemsite_minordqty,
    itemsite_multordqty,		itemsite_leadtime,
    itemsite_abcclass,			itemsite_issuemethod,
    itemsite_controlmethod,		itemsite_active,
    itemsite_plancode_id,		itemsite_costcat_id,
    itemsite_eventfence,		itemsite_sold,
    itemsite_stocked,			itemsite_freeze,
    itemsite_location_id,
    itemsite_useparams,			itemsite_useparamsmanual,
    itemsite_soldranking,		itemsite_createpr,
    itemsite_location,			itemsite_location_comments,
    itemsite_notes,			itemsite_perishable,
    itemsite_nnqoh,			itemsite_autoabcclass,
    itemsite_ordergroup,		itemsite_disallowblankwip,
    itemsite_maxordqty,			itemsite_mps_timefence,
    itemsite_createwo,			itemsite_warrpurc,
    itemsite_autoreg,
    itemsite_planning_type,             itemsite_supply_itemsite_id
  ) VALUES (
    _new.itemsite_id,			COALESCE(pdestitemid, _new.itemsite_item_id),
    _new.itemsite_warehous_id,		_new.itemsite_qtyonhand,
    _new.itemsite_costmethod,           _new.itemsite_value,
    _new.itemsite_reorderlevel,	        _new.itemsite_ordertoqty,
    _new.itemsite_cyclecountfreq,	_new.itemsite_datelastcount,
    _new.itemsite_datelastused,
    _new.itemsite_posupply,		_new.itemsite_wosupply,
    _new.itemsite_loccntrl,
    _new.itemsite_safetystock,		_new.itemsite_minordqty,
    _new.itemsite_multordqty,		_new.itemsite_leadtime,
    _new.itemsite_abcclass,		_new.itemsite_issuemethod,
    _new.itemsite_controlmethod,	_new.itemsite_active,
    _new.itemsite_plancode_id,		_new.itemsite_costcat_id,
    _new.itemsite_eventfence,		_new.itemsite_sold,
    _new.itemsite_stocked,		_new.itemsite_freeze,
    _new.itemsite_location_id,
    _new.itemsite_useparams,		_new.itemsite_useparamsmanual,
    _new.itemsite_soldranking,		_new.itemsite_createpr,
    _new.itemsite_location,		_new.itemsite_location_comments,
    _new.itemsite_notes,		_new.itemsite_perishable,
    _new.itemsite_nnqoh,		_new.itemsite_autoabcclass,
    _new.itemsite_ordergroup,		_new.itemsite_disallowblankwip,
    _new.itemsite_maxordqty,		_new.itemsite_mps_timefence,
    _new.itemsite_createwo,   	        _new.itemsite_warrpurc,
    _new.itemsite_autoreg,
    _new.itemsite_planning_type,        _new.itemsite_supply_itemsite_id
    );

  RETURN _new.itemsite_id;
END;
$_$;


ALTER FUNCTION public.copyitemsite(pitemsiteid integer, pdestwhsid integer, pdestitemid integer) OWNER TO admin;

--
-- TOC entry 1246 (class 1255 OID 146565808)
-- Dependencies: 4536 8
-- Name: copylocale(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION copylocale(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
  pLocaleid ALIAS FOR $1;
  _localecode TEXT;
  _localeid INTEGER;

BEGIN

  SELECT locale_code INTO _localecode
  FROM locale
  WHERE (locale_id=pLocaleid);

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Attempt to copy a non-existent locale-id.';
  END IF;

  IF (EXISTS(SELECT locale_id
             FROM locale
             WHERE (locale_code = (_localecode || '-COPY')))) THEN
    RAISE EXCEPTION 'Attempt to copy a Locale Code that already exists.';
  END IF;

  SELECT NEXTVAL('locale_locale_id_seq') INTO _localeid;

  INSERT INTO locale
        (locale_id, locale_code, locale_descrip,
         locale_lang_file,
         locale_dateformat,
         locale_currformat,
         locale_qtyformat,
         locale_comments,
         locale_qtyperformat,
         locale_salespriceformat,
         locale_extpriceformat,
         locale_timeformat,
         locale_timestampformat,
         local_costformat,
         locale_costformat,
         locale_purchpriceformat,
         locale_uomratioformat,
         locale_intervalformat,
         locale_lang_id,
         locale_country_id,
         locale_error_color,
         locale_warning_color,
         locale_emphasis_color,
         locale_altemphasis_color,
         locale_expired_color,
         locale_future_color,
         locale_curr_scale,
         locale_salesprice_scale,
         locale_purchprice_scale,
         locale_extprice_scale,
         locale_cost_scale,
         locale_qty_scale,
         locale_qtyper_scale,
         locale_uomratio_scale)
  SELECT _localeid, locale_code || '-COPY', '',
         locale_lang_file,
         locale_dateformat,
         locale_currformat,
         locale_qtyformat,
         locale_comments,
         locale_qtyperformat,
         locale_salespriceformat,
         locale_extpriceformat,
         locale_timeformat,
         locale_timestampformat,
         local_costformat,
         locale_costformat,
         locale_purchpriceformat,
         locale_uomratioformat,
         locale_intervalformat,
         locale_lang_id,
         locale_country_id,
         locale_error_color,
         locale_warning_color,
         locale_emphasis_color,
         locale_altemphasis_color,
         locale_expired_color,
         locale_future_color,
         locale_curr_scale,
         locale_salesprice_scale,
         locale_purchprice_scale,
         locale_extprice_scale,
         locale_cost_scale,
         locale_qty_scale,
         locale_qtyper_scale,
         locale_uomratio_scale
    FROM locale
   WHERE(locale_id=pLocaleid);

  RETURN _localeid;

END;
$_$;


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

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

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

  _tgtid		INTEGER;
  _orderdate		DATE;
  _head			RECORD;
  _itemsrc		RECORD;
  _lineitem		RECORD;
  _qty			NUMERIC;
  _unitprice		NUMERIC;
  _uomratio		NUMERIC;
  _vend_restrictpurch	BOOLEAN;

BEGIN
  SELECT * INTO _head FROM pohead WHERE pohead_id = pSrcid;
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;
  IF (_head.pohead_vend_id != pVendid) THEN
    RETURN -2;		-- not supported now but should be in the future
  END IF;		-- when enabled, set pRecheckVendinfo if vendors don't match

  IF (pOrderdate IS NULL) THEN
    _orderdate := CURRENT_DATE;
  ELSE
    _orderdate := pOrderdate;
  END IF;

  INSERT INTO pohead (pohead_status, pohead_number,
		      pohead_orderdate, pohead_vend_id,
		      pohead_fob, pohead_shipvia,
		      pohead_freight, pohead_printed,
		      pohead_terms_id, pohead_warehous_id,
		      pohead_vendaddr_id, pohead_agent_username,
		      pohead_curr_id, pohead_saved,
                      pohead_taxtype_id, pohead_taxzone_id,
                      pohead_dropship, pohead_vend_cntct_id,
                      pohead_vend_cntct_honorific, pohead_vend_cntct_first_name,
                      pohead_vend_cntct_middle, pohead_vend_cntct_last_name,
                      pohead_vend_cntct_suffix, pohead_vend_cntct_phone,
                      pohead_vend_cntct_title, pohead_vend_cntct_fax,
                      pohead_vend_cntct_email, pohead_vendaddress1,
                      pohead_vendaddress2, pohead_vendaddress3,
                      pohead_vendcity, pohead_vendstate,
                      pohead_vendzipcode, pohead_vendcountry,
                      pohead_shipto_cntct_id,
                      pohead_shipto_cntct_honorific, pohead_shipto_cntct_first_name,
                      pohead_shipto_cntct_middle, pohead_shipto_cntct_last_name,
                      pohead_shipto_cntct_suffix, pohead_shipto_cntct_phone,
                      pohead_shipto_cntct_title, pohead_shipto_cntct_fax,
                      pohead_shipto_cntct_email, pohead_shiptoaddress_id,
                      pohead_shiptoaddress1,
                      pohead_shiptoaddress2, pohead_shiptoaddress3,
                      pohead_shiptocity, pohead_shiptostate,
                      pohead_shiptozipcode, pohead_shiptocountry
	      ) VALUES (
		      'U', fetchPoNumber(),
		      _orderdate, _head.pohead_vend_id,
		      _head.pohead_fob, _head.pohead_shipvia,
		      _head.pohead_freight, false,
		      _head.pohead_terms_id, _head.pohead_warehous_id,
		      _head.pohead_vendaddr_id, _head.pohead_agent_username,
		      _head.pohead_curr_id, true,
                      _head.pohead_taxtype_id, _head.pohead_taxzone_id,
                      false, _head.pohead_vend_cntct_id,
                      _head.pohead_vend_cntct_honorific, _head.pohead_vend_cntct_first_name,
                      _head.pohead_vend_cntct_middle, _head.pohead_vend_cntct_last_name,
                      _head.pohead_vend_cntct_suffix, _head.pohead_vend_cntct_phone,
                      _head.pohead_vend_cntct_title, _head.pohead_vend_cntct_fax,
                      _head.pohead_vend_cntct_email, _head.pohead_vendaddress1,
                      _head.pohead_vendaddress2, _head.pohead_vendaddress3,
                      _head.pohead_vendcity, _head.pohead_vendstate,
                      _head.pohead_vendzipcode, _head.pohead_vendcountry,
                      _head.pohead_shipto_cntct_id,
                      _head.pohead_shipto_cntct_honorific, _head.pohead_shipto_cntct_first_name,
                      _head.pohead_shipto_cntct_middle, _head.pohead_shipto_cntct_last_name,
                      _head.pohead_shipto_cntct_suffix, _head.pohead_shipto_cntct_phone,
                      _head.pohead_shipto_cntct_title, _head.pohead_shipto_cntct_fax,
                      _head.pohead_shipto_cntct_email, _head.pohead_shiptoaddress_id,
                      _head.pohead_shiptoaddress1,
                      _head.pohead_shiptoaddress2, _head.pohead_shiptoaddress3,
                      _head.pohead_shiptocity, _head.pohead_shiptostate,
                      _head.pohead_shiptozipcode, _head.pohead_shiptocountry);

  _tgtid := CURRVAL('pohead_pohead_id_seq');

  IF (pRecheckVendinfo) THEN
    SELECT vend_restrictpurch INTO _vend_restrictpurch
      FROM vendinfo WHERE (vend_id = pVendid);

    FOR _lineitem IN SELECT *
		  FROM poitem 
		  WHERE (poitem_pohead_id = pSrcid) LOOP

      SELECT * INTO _itemsrc
      FROM itemsrc, itemsite
      WHERE (itemsrc_active
        AND  (itemsrc_id = _lineitem.poitem_itemsrc_id)
	AND  (itemsite_id = _lineitem.poitem_itemsite_id));
      IF (NOT FOUND AND _vend_restrictpurch) THEN
	RETURN -3;
      END IF;

      -- handle changes to the uom ratio and consequent qty changes
      _uomratio := COALESCE(_itemsrc.itemsrc_invvendoruomratio, _lineitem.poitem_invvenduomratio);
      IF (_itemsrc.itemsrc_invvendoruomratio IS NULL
	  OR _itemsrc.itemsrc_invvendoruomratio != _lineitem.poitem_invvenduomratio) THEN
	_qty := _lineitem.poitem_qty_ordered;

      ELSE
	_qty := _lineitem.poitem_qty_ordered * _lineitem.poitem_invvenduomratio /
					       _itemsrc.itemsrc_invvendoruomratio;
	IF (_itemsrc.itemsrc_minordqty IS NOT NULL) THEN
	  IF (_qty < _itemsrc.itemsrc_minordqty) THEN
	    _qty := _itemsrc.itemsrc_minordqty;
	  ELSIF (_itemsrc.itemsrc_multordqty > 0
		   AND _qty % _itemsrc.itemsrc_multordqty > 0) THEN
	    _qty = _qty % _itemsrc.itemsrc_multordqty + _itemsrc.itemsrc_multordqty;
	  END IF;
	END IF;
      END IF;

      IF (_itemsrc.itemsrc_id IS NULL) THEN
	_unitprice = _lineitem.poitem_unitprice;
      ELSE
        SELECT itemsrcPrice(_itemsrc.itemsrc_id, _head.pohead_warehous_id, _head.pohead_dropship,
                            _lineitem.poitem_qty_ordered, _head.pohead_curr_id, CURRENT_DATE) INTO _unitprice;
	IF (_unitprice IS NULL) THEN
	  RETURN -4;
	END IF;
      END IF;

      INSERT INTO poitem (poitem_status, poitem_pohead_id, poitem_linenumber,
			  poitem_duedate,
			  poitem_itemsite_id,
			  poitem_vend_item_descrip,
			  poitem_vend_uom,
			  poitem_invvenduomratio,
			  poitem_qty_ordered, poitem_unitprice,
			  poitem_vend_item_number,
			  poitem_comments, poitem_expcat_id,
			  poitem_itemsrc_id,
			  poitem_freight,
			  poitem_stdcost,
			  poitem_manuf_name,
			  poitem_manuf_item_number,
			  poitem_manuf_item_descrip,
                          poitem_taxtype_id
		    ) VALUES (
			  'U', _tgtid, _lineitem.poitem_linenumber,
			  _orderdate + COALESCE(_itemsrc.itemsrc_leadtime, 0),
			  _lineitem.poitem_itemsite_id,
			  COALESCE(_itemsrc.itemsrc_vend_item_descrip,
				   _lineitem.poitem_vend_item_descrip),
			  COALESCE(_itemsrc.itemsrc_vend_uom, _lineitem.poitem_vend_uom),
			  COALESCE(_itemsrc.itemsrc_invvendoruomratio,
				   _lineitem.poitem_invvenduomratio),
			  _qty, _unitprice,
			  COALESCE(_itemsrc.itemsrc_vend_item_number,
				   _lineitem.poitem_vend_item_number),
			  _lineitem.poitem_comments, _lineitem.poitem_expcat_id,
			  COALESCE(_itemsrc.itemsrc_id, -1),
			  _lineitem.poitem_freight,
			  stdcost(_itemsrc.itemsite_item_id),
			  COALESCE(_itemsrc.itemsrc_manuf_name,
				   _lineitem.poitem_manuf_name),
		          COALESCE(_itemsrc.itemsrc_manuf_item_number,
				   _lineitem.poitem_manuf_item_number),
			  COALESCE(_itemsrc.itemsrc_manuf_item_descrip,
				   _lineitem.poitem_manuf_item_descrip),
                          _lineitem.poitem_taxtype_id);

    END LOOP;
  ELSE
    INSERT INTO poitem (poitem_status, poitem_pohead_id, poitem_linenumber,
			poitem_duedate, poitem_itemsite_id,
			poitem_vend_item_descrip, poitem_vend_uom,
			poitem_invvenduomratio, poitem_qty_ordered,
			poitem_unitprice,
			poitem_vend_item_number, poitem_comments,
			poitem_expcat_id, poitem_itemsrc_id, poitem_freight,
			poitem_stdcost, poitem_manuf_name, 
			poitem_manuf_item_number, poitem_manuf_item_descrip,
                        poitem_taxtype_id
		) SELECT 'U', _tgtid, poitem_linenumber,
			_orderdate + COALESCE(itemsrc_leadtime, 0), poitem_itemsite_id,
			poitem_vend_item_descrip, poitem_vend_uom,
			poitem_invvenduomratio, poitem_qty_ordered,
			poitem_unitprice,
			poitem_vend_item_number, poitem_comments,
			poitem_expcat_id, poitem_itemsrc_id, poitem_freight,
			stdcost(itemsite_item_id), poitem_manuf_name,
			poitem_manuf_item_number, poitem_manuf_item_descrip,
                        poitem_taxtype_id
		  FROM poitem
		    LEFT OUTER JOIN itemsrc ON (itemsrc_id=poitem_itemsrc_id)
		    LEFT OUTER JOIN itemsite ON (itemsite_id=poitem_itemsite_id)
		  WHERE (poitem_pohead_id = pSrcid);
  END IF;

  -- Todo: recalculate tax?

  RETURN _tgtid;

END;
$_$;


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

--
-- TOC entry 1262 (class 1255 OID 146565811)
-- Dependencies: 4536 8
-- Name: copypricingschedule(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION copypricingschedule(pipsheadid 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
  _ipsheadid INTEGER;
  _ipsitemid INTEGER;
  _ipsfreightid INTEGER;
  _x RECORD;

BEGIN

  _ipsheadid := nextval('ipshead_ipshead_id_seq');
  INSERT INTO ipshead 
  ( ipshead_id, ipshead_name, ipshead_descrip,
    ipshead_effective, ipshead_expires, 
    ipshead_curr_id, ipshead_updated ) 
  SELECT _ipsheadid, orig.ipshead_name || (SELECT CAST((COUNT(cnt.ipshead_id)+1) AS text)
				            FROM ipshead cnt
				            WHERE (SUBSTRING(cnt.ipshead_name FROM 0 FOR char_length(orig.ipshead_name)+1) = orig.ipshead_name)),
	 orig.ipshead_descrip, orig.ipshead_effective, orig.ipshead_expires, 
	 orig.ipshead_curr_id, CURRENT_DATE
  FROM ipshead orig
  WHERE (orig.ipshead_id=pIpsheadId);

  FOR _x IN
    SELECT ipsitem_id FROM ipsiteminfo WHERE (ipsitem_ipshead_id=pIpsheadid)
  LOOP 
      INSERT INTO ipsiteminfo 
          (ipsitem_ipshead_id, ipsitem_item_id, ipsitem_prodcat_id,
           ipsitem_qtybreak, ipsitem_price,
           ipsitem_qty_uom_id, ipsitem_price_uom_id,
           ipsitem_discntprcnt, ipsitem_fixedamtdiscount,
           ipsitem_type, ipsitem_warehous_id) 
      SELECT _ipsheadid, ipsitem_item_id, ipsitem_prodcat_id,
           ipsitem_qtybreak, ipsitem_price,
           ipsitem_qty_uom_id, ipsitem_price_uom_id,
           ipsitem_discntprcnt, ipsitem_fixedamtdiscount,
           ipsitem_type, ipsitem_warehous_id
      FROM ipsiteminfo 
      WHERE (ipsitem_id=_x.ipsitem_id)
      RETURNING ipsitem_id INTO _ipsitemid; 

      INSERT INTO ipsitemchar
        ( ipsitemchar_ipsitem_id, ipsitemchar_char_id,
          ipsitemchar_value, ipsitemchar_price)
      SELECT  _ipsitemid, ipsitemchar_char_id,
          ipsitemchar_value, ipsitemchar_price
      FROM ipsitemchar
      WHERE (ipsitemchar_ipsitem_id=_x.ipsitem_id);
  END LOOP;

  FOR _x IN
    SELECT ipsfreight_id FROM ipsfreight WHERE (ipsfreight_ipshead_id=pIpsheadid)
  LOOP 
      _ipsfreightid := nextval('ipsfreight_ipsfreight_id_seq');
      INSERT INTO ipsfreight
          (ipsfreight_id, ipsfreight_ipshead_id, 
           ipsfreight_qtybreak, ipsfreight_price,
           ipsfreight_type, ipsfreight_warehous_id,
           ipsfreight_shipzone_id,ipsfreight_freightclass_id,
           ipsfreight_shipvia) 
      SELECT _ipsfreightid, _ipsheadid, ipsfreight_qtybreak, 
           ipsfreight_price,ipsfreight_type, 
           ipsfreight_warehous_id,ipsfreight_shipzone_id,
           ipsfreight_freightclass_id,ipsfreight_shipvia
      FROM ipsfreight
      WHERE (ipsfreight_id=_x.ipsfreight_id); 

  END LOOP;

  RETURN _ipsheadid;

END;
$$;


ALTER FUNCTION public.copypricingschedule(pipsheadid integer) OWNER TO admin;

--
-- TOC entry 1263 (class 1255 OID 146565812)
-- Dependencies: 4536 8
-- Name: copyprj(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION copyprj(integer, date) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pparentid   ALIAS FOR $1;
  _counter    INTEGER;
  _duedate    DATE := COALESCE($2, CURRENT_DATE);
  _alarmid    INTEGER;
  _i          INTEGER;
  _newnumber  TEXT;
  _p          RECORD;
  _prjid      INTEGER;
  _testnumber TEXT;

BEGIN
  RAISE DEBUG 'copyPrj(%, %) entered', pparentid, _duedate;

  SELECT * INTO _p
  FROM prj
  WHERE (prj_id=pparentid);

  -- new number = old number up to but not including -, followed by _duedate
  -- e.g. REPAIR-FRIDGE becomes REPAIR-2010-05-15
  --  but REPAIR_FRIDGE becomes REPAIR_FRIDGE-2010-05-15
  IF (_p.prj_recurring_prj_id IS NULL) THEN
    _newnumber := _p.prj_number;
  ELSE
    _newnumber := SUBSTRING(_p.prj_number FROM '[^-]*');
    IF (_newnumber IS NULL) THEN
      _newnumber := _p.prj_number;
    END IF;
  END IF;
  _newnumber := _newnumber || '-' || to_char(_duedate, 'YYYY-MM-DD');
  
  RAISE DEBUG 'copyPrj checking if _newnumber % exists', _newnumber;
  SELECT MAX(prj_number) INTO _testnumber
    FROM prj
   WHERE (prj_number ~ ('^' || _newnumber));
  IF (_testnumber = _newnumber) THEN
    _newnumber := _newnumber || '-001';
  ELSIF (_testnumber IS NOT NULL) THEN
    _counter := CAST(SUBSTRING(_testnumber FROM '...$') AS INTEGER);
    _counter := _counter + 1;
    _newnumber := REGEXP_REPLACE(_testnumber, '...$', to_char(_counter, 'FM009'));
  END IF;
  RAISE DEBUG 'copyPrj _newnumber is now %', _newnumber;

  INSERT INTO prj(
            prj_number,     prj_name,           prj_descrip,
            prj_status,     prj_so,             prj_wo,
            prj_po,         prj_owner_username,
            prj_due_date,   prj_username,       prj_recurring_prj_id
  ) SELECT  _newnumber,     _p.prj_name,        _p.prj_descrip,
            'P',            _p.prj_so,          _p.prj_wo,
            _p.prj_po,      _p.prj_owner_username,
            _duedate,       _p.prj_username,    _p. prj_recurring_prj_id
      FROM prj
     WHERE (prj_id=pparentid)
  RETURNING prj_id INTO _prjid;

  IF (_prjid IS NULL) THEN
    RETURN -1;
  END IF;

  SELECT saveAlarm(NULL, NULL, _duedate,
                   CAST(alarm_time - DATE_TRUNC('day',alarm_time) AS TIME),
                   alarm_time_offset,
                   alarm_time_qualifier,
                   alarm_event_recipient  IS NOT NULL, alarm_event_recipient,
                   alarm_email_recipient  IS NOT NULL, alarm_email_recipient,
                   alarm_sysmsg_recipient IS NOT NULL, alarm_sysmsg_recipient,
                   'J', _prjid, 'CHANGEONE')
    INTO _alarmid
    FROM alarm
   WHERE ((alarm_source='J')
      AND (alarm_source_id=pparentid));

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

  RETURN _prjid;
END;
$_$;


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

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

CREATE FUNCTION copyproject(integer, text, text, 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
  pPrjId ALIAS FOR $1;
  pPrjNumber ALIAS FOR $2;
  pPrjName ALIAS FOR $3;
  pDueDate ALIAS FOR $4;
  _prjid INTEGER;
  _offset INTEGER;

BEGIN

  IF (COALESCE(pPrjNumber, '') = '') THEN
    RETURN -1;
  END IF;

  IF (COALESCE(pPrjName, '') = '') THEN
    RETURN -1;
  END IF;

  IF (EXISTS(SELECT prj_id FROM prj WHERE UPPER(prj_number)=UPPER(pPrjNumber))) THEN
    RETURN -2;
  END IF;

  IF (NOT EXISTS(SELECT prj_id FROM prj WHERE prj_id=pPrjId)) THEN
    RETURN -3;
  END IF;

  IF (pDueDate IS NULL) THEN
    RETURN -4;
  END IF;

  SELECT (pDueDate - prj_due_date) INTO _offset
   FROM prj
   WHERE (prj_id=pPrjId);

  SELECT NEXTVAL('prj_prj_id_seq') INTO _prjid;

  INSERT INTO prj
  ( prj_id, prj_number, prj_name, 
    prj_descrip, prj_status,
    prj_so, prj_wo, prj_po,
    prj_owner_username, prj_start_date,
    prj_due_date, prj_assigned_date, prj_completed_date,
    prj_username, prj_recurring_prj_id,
    prj_crmacct_id, prj_cntct_id )
  SELECT _prjid, UPPER(pPrjNumber), pPrjName,
         prj_descrip, 'P',
         prj_so, prj_wo, prj_po,
         prj_owner_username, NULL,
         (prj_due_date + COALESCE(_offset, 0)),
         CASE WHEN (prj_username IS NULL) THEN NULL ELSE CURRENT_DATE END, NULL,
         prj_username, prj_recurring_prj_id,
         prj_crmacct_id, prj_cntct_id
  FROM prj
  WHERE (prj_id=pPrjId);

  INSERT INTO prjtask
  ( prjtask_number, prjtask_name, prjtask_descrip,
    prjtask_prj_id, prjtask_anyuser, prjtask_status,
    prjtask_hours_budget, prjtask_hours_actual,
    prjtask_exp_budget, prjtask_exp_actual,
    prjtask_owner_username, prjtask_start_date,
    prjtask_due_date, prjtask_assigned_date,
    prjtask_completed_date, prjtask_username )
  SELECT prjtask_number, prjtask_name, prjtask_descrip,
         _prjid, prjtask_anyuser, 'P',
         prjtask_hours_budget, 0.0,
         prjtask_exp_budget, 0.0,
         prjtask_owner_username, NULL,
         (prjtask_due_date + COALESCE(_offset, 0)),
         CASE WHEN (prjtask_username IS NULL) THEN NULL ELSE CURRENT_DATE END,
         NULL, prjtask_username
  FROM prjtask
  WHERE (prjtask_prj_id=pPrjId);

  INSERT INTO docass
  ( docass_source_id, docass_source_type,
    docass_target_id, docass_target_type,
    docass_purpose )
  SELECT _prjid, docass_source_type,
         docass_target_id, docass_target_type,
         docass_purpose
  FROM docass
  WHERE ((docass_source_id=pPrjId)
    AND  (docass_source_type='J'));

  RETURN _prjid;

END;
$_$;


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

--
-- TOC entry 1265 (class 1255 OID 146565814)
-- Dependencies: 4536 8
-- Name: copyquote(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT NEXTVAL('quhead_quhead_id_seq') INTO _quheadid;
  IF (fetchMetricText('QUNumberGeneration') = 'S') THEN
    SELECT fetchSoNumber() INTO _qunumber;
  ELSE
    SELECT fetchQuNumber() INTO _qunumber;
  END IF;

  INSERT INTO quhead
  ( quhead_id, quhead_number, quhead_cust_id, quhead_prj_id,
    quhead_quotedate, quhead_packdate, quhead_fob,
    quhead_warehous_id, quhead_terms_id, quhead_salesrep_id,
    quhead_custponumber, quhead_shipvia,
    quhead_shipto_id, quhead_shiptoname, quhead_shiptoaddress1, quhead_shiptoaddress2, quhead_shiptoaddress3,
    quhead_shiptocity, quhead_shiptostate, quhead_shiptozipcode, quhead_shiptophone, quhead_shiptocountry,
    quhead_billtoname, quhead_billtoaddress1, quhead_billtoaddress2, quhead_billtoaddress3,
    quhead_billtocity, quhead_billtostate, quhead_billtozip,
    quhead_misc_accnt_id, quhead_misc_descrip, quhead_misc, quhead_freight, quhead_commission,
    quhead_ordercomments, quhead_shipcomments,
    quhead_imported, quhead_curr_id, quhead_taxzone_id, quhead_taxtype_id, quhead_ophead_id, quhead_status,
    quhead_shipto_cntct_id, quhead_shipto_cntct_honorific, quhead_shipto_cntct_first_name, quhead_shipto_cntct_middle,
    quhead_shipto_cntct_last_name, quhead_shipto_cntct_suffix, quhead_shipto_cntct_phone, quhead_shipto_cntct_title,
    quhead_shipto_cntct_fax, quhead_shipto_cntct_email, quhead_billto_cntct_id, quhead_billto_cntct_honorific,
    quhead_billto_cntct_first_name, quhead_billto_cntct_middle, quhead_billto_cntct_last_name, quhead_billto_cntct_suffix,
    quhead_billto_cntct_phone, quhead_billto_cntct_title, quhead_billto_cntct_fax, quhead_billto_cntct_email )
  SELECT _quheadid, _qunumber, quhead_cust_id, quhead_prj_id,
         CURRENT_DATE, COALESCE(pSchedDate, quhead_packdate), quhead_fob,
         quhead_warehous_id, quhead_terms_id, quhead_salesrep_id,
         quhead_custponumber, quhead_shipvia,
         quhead_shipto_id, quhead_shiptoname, quhead_shiptoaddress1, quhead_shiptoaddress2, quhead_shiptoaddress3,
         quhead_shiptocity, quhead_shiptostate, quhead_shiptozipcode, quhead_shiptophone, quhead_shiptocountry,
         quhead_billtoname, quhead_billtoaddress1, quhead_billtoaddress2, quhead_billtoaddress3,
         quhead_billtocity, quhead_billtostate, quhead_billtozip,
         quhead_misc_accnt_id, quhead_misc_descrip, quhead_misc, quhead_freight, quhead_commission,
         quhead_ordercomments, quhead_shipcomments,
         FALSE, quhead_curr_id, quhead_taxzone_id, quhead_taxtype_id, quhead_ophead_id, 'O',
         quhead_shipto_cntct_id, quhead_shipto_cntct_honorific, quhead_shipto_cntct_first_name, quhead_shipto_cntct_middle,
         quhead_shipto_cntct_last_name, quhead_shipto_cntct_suffix, quhead_shipto_cntct_phone, quhead_shipto_cntct_title,
         quhead_shipto_cntct_fax, quhead_shipto_cntct_email, quhead_billto_cntct_id, quhead_billto_cntct_honorific,
         quhead_billto_cntct_first_name, quhead_billto_cntct_middle, quhead_billto_cntct_last_name, quhead_billto_cntct_suffix,
         quhead_billto_cntct_phone, quhead_billto_cntct_title, quhead_billto_cntct_fax, quhead_billto_cntct_email
  FROM quhead
  WHERE (quhead_id=pQuheadid);

  INSERT INTO quitem
  ( quitem_quhead_id, quitem_linenumber, quitem_itemsite_id,
    quitem_scheddate, quitem_promdate, quitem_qtyord,
    quitem_price, quitem_custprice, quitem_unitcost,
    quitem_qty_uom_id, quitem_price_uom_id,
    quitem_qty_invuomratio, quitem_price_invuomratio,
    quitem_memo, quitem_custpn, quitem_imported, quitem_taxtype_id,
    quitem_createorder, quitem_order_warehous_id, quitem_item_id, quitem_prcost,
    quitem_dropship, quitem_itemsrc_id, quitem_pricemode )
  SELECT _quheadid, quitem_linenumber, quitem_itemsite_id,
         COALESCE(pSchedDate, quitem_scheddate),
         quitem_promdate,
         quitem_qtyord,
         quitem_price, quitem_custprice, stdCost(itemsite_item_id),
         quitem_qty_uom_id, quitem_price_uom_id,
         quitem_qty_invuomratio, quitem_price_invuomratio,
         quitem_memo, quitem_custpn, FALSE, quitem_taxtype_id,
         quitem_createorder, quitem_order_warehous_id, quitem_item_id, quitem_prcost,
         quitem_dropship, quitem_itemsrc_id, quitem_pricemode
  FROM quitem, itemsite
  WHERE ( (quitem_itemsite_id=itemsite_id)
   AND (quitem_quhead_id=pQuheadid));

  INSERT INTO charass
        (charass_target_type, charass_target_id,
         charass_char_id, charass_value)
  SELECT charass_target_type, b.quitem_id,
         charass_char_id, charass_value
    FROM quitem a, charass, quitem b
   WHERE ((charass_target_type='SI')
     AND  (charass_target_id=a.quitem_id)
     AND  (a.quitem_quhead_id=pQuheadid)
     AND  (b.quitem_quhead_id=_quheadid)
     AND  (a.quitem_linenumber=b.quitem_linenumber)
     );

  RETURN _quheadid;

END;
$_$;


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

--
-- TOC entry 1266 (class 1255 OID 146565815)
-- Dependencies: 4536 8
-- Name: copyso(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION copyso(psoheadid integer, pscheddate 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
  _soheadid INTEGER;
  _soitemid INTEGER;
  _soitem RECORD;

BEGIN

  SELECT NEXTVAL('cohead_cohead_id_seq') INTO _soheadid;

  INSERT INTO cohead
  ( cohead_id,
    cohead_number,
    cohead_cust_id,
    cohead_custponumber,
    cohead_type,
    cohead_orderdate,
    cohead_warehous_id,
    cohead_shipto_id,
    cohead_shiptoname,
    cohead_shiptoaddress1,
    cohead_shiptoaddress2,
    cohead_shiptoaddress3,
    cohead_shiptoaddress4,
    cohead_shiptoaddress5,
    cohead_salesrep_id,
    cohead_terms_id,
    cohead_fob,
    cohead_shipvia,
    cohead_shiptocity,
    cohead_shiptostate,
    cohead_shiptozipcode,
    cohead_freight,
    cohead_misc,
    cohead_imported,
    cohead_ordercomments,
    cohead_shipcomments,
    cohead_shiptophone,
    cohead_shipchrg_id,
    cohead_shipform_id,
    cohead_billtoname,
    cohead_billtoaddress1,
    cohead_billtoaddress2,
    cohead_billtoaddress3,
    cohead_billtocity,
    cohead_billtostate,
    cohead_billtozipcode,
    cohead_misc_accnt_id,
    cohead_misc_descrip,
    cohead_commission,
    cohead_miscdate,
    cohead_holdtype,
    cohead_packdate,
    cohead_prj_id,
    cohead_wasquote,
    cohead_lastupdated,
    cohead_shipcomplete,
    cohead_created,
    cohead_creator,
    cohead_quote_number,
    cohead_billtocountry,
    cohead_shiptocountry,
    cohead_curr_id,
    cohead_calcfreight,
    cohead_shipto_cntct_id,
    cohead_shipto_cntct_honorific,
    cohead_shipto_cntct_first_name,
    cohead_shipto_cntct_middle,
    cohead_shipto_cntct_last_name,
    cohead_shipto_cntct_suffix,
    cohead_shipto_cntct_phone,
    cohead_shipto_cntct_title,
    cohead_shipto_cntct_fax,
    cohead_shipto_cntct_email,
    cohead_billto_cntct_id,
    cohead_billto_cntct_honorific,
    cohead_billto_cntct_first_name,
    cohead_billto_cntct_middle,
    cohead_billto_cntct_last_name,
    cohead_billto_cntct_suffix,
    cohead_billto_cntct_phone,
    cohead_billto_cntct_title,
    cohead_billto_cntct_fax,
    cohead_billto_cntct_email,
    cohead_taxzone_id,
    cohead_taxtype_id,
    cohead_ophead_id,
    cohead_status,
    cohead_saletype_id,
    cohead_shipzone_id )
  SELECT
    _soheadid,
    fetchSoNumber(),
    cohead_cust_id,
    cohead_custponumber,
    cohead_type,
    CURRENT_DATE,
    cohead_warehous_id,
    cohead_shipto_id,
    cohead_shiptoname,
    cohead_shiptoaddress1,
    cohead_shiptoaddress2,
    cohead_shiptoaddress3,
    cohead_shiptoaddress4,
    cohead_shiptoaddress5,
    cohead_salesrep_id,
    cohead_terms_id,
    cohead_fob,
    cohead_shipvia,
    cohead_shiptocity,
    cohead_shiptostate,
    cohead_shiptozipcode,
    cohead_freight,
    cohead_misc,
    FALSE,
    cohead_ordercomments,
    cohead_shipcomments,
    cohead_shiptophone,
    cohead_shipchrg_id,
    cohead_shipform_id,
    cohead_billtoname,
    cohead_billtoaddress1,
    cohead_billtoaddress2,
    cohead_billtoaddress3,
    cohead_billtocity,
    cohead_billtostate,
    cohead_billtozipcode,
    cohead_misc_accnt_id,
    cohead_misc_descrip,
    cohead_commission,
    cohead_miscdate,
    cohead_holdtype,
    COALESCE(pSchedDate, cohead_packdate),
    cohead_prj_id,
    FALSE,
    cohead_lastupdated,
    cohead_shipcomplete,
    NULL,
    getEffectiveXtUser(),
    NULL,
    cohead_billtocountry,
    cohead_shiptocountry,
    cohead_curr_id,
    cohead_calcfreight,
    cohead_shipto_cntct_id,
    cohead_shipto_cntct_honorific,
    cohead_shipto_cntct_first_name,
    cohead_shipto_cntct_middle,
    cohead_shipto_cntct_last_name,
    cohead_shipto_cntct_suffix,
    cohead_shipto_cntct_phone,
    cohead_shipto_cntct_title,
    cohead_shipto_cntct_fax,
    cohead_shipto_cntct_email,
    cohead_billto_cntct_id,
    cohead_billto_cntct_honorific,
    cohead_billto_cntct_first_name,
    cohead_billto_cntct_middle,
    cohead_billto_cntct_last_name,
    cohead_billto_cntct_suffix,
    cohead_billto_cntct_phone,
    cohead_billto_cntct_title,
    cohead_billto_cntct_fax,
    cohead_billto_cntct_email,
    cohead_taxzone_id,
    cohead_taxtype_id,
    cohead_ophead_id,
    cohead_status,
    cohead_saletype_id,
    cohead_shipzone_id
  FROM cohead
  WHERE (cohead_id=pSoheadid);

  FOR _soitem IN
    SELECT *
    FROM coitem JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
    WHERE ( (coitem_cohead_id=pSoheadid)
      AND   (coitem_status <> 'X')
      AND   (coitem_subnumber = 0) ) LOOP

    SELECT NEXTVAL('coitem_coitem_id_seq') INTO _soitemid;

    -- insert characteristics first so they can be copied to associated supply order
    INSERT INTO charass
          (charass_target_type, charass_target_id,
           charass_char_id, charass_value)
    SELECT charass_target_type, _soitemid,
           charass_char_id, charass_value
      FROM charass
     WHERE ((charass_target_type='SI')
       AND  (charass_target_id=_soitem.coitem_id));

    INSERT INTO coitem
    ( coitem_id,
      coitem_cohead_id,
      coitem_linenumber,
      coitem_itemsite_id,
      coitem_status,
      coitem_scheddate,
      coitem_promdate,
      coitem_qtyord,
      coitem_unitcost,
      coitem_price,
      coitem_custprice,
      coitem_qtyshipped,
      coitem_order_id,
      coitem_memo,
      coitem_imported,
      coitem_qtyreturned,
      coitem_closedate,
      coitem_custpn,
      coitem_order_type,
      coitem_close_username,
--      coitem_lastupdated,
      coitem_substitute_item_id,
      coitem_created,
      coitem_creator,
      coitem_prcost,
      coitem_qty_uom_id,
      coitem_qty_invuomratio,
      coitem_price_uom_id,
      coitem_price_invuomratio,
      coitem_warranty,
      coitem_cos_accnt_id,
      coitem_qtyreserved,
      coitem_subnumber,
      coitem_firm,
      coitem_taxtype_id )
    VALUES
    ( _soitemid,
      _soheadid,
      _soitem.coitem_linenumber,
      _soitem.coitem_itemsite_id,
      'O',
      COALESCE(pSchedDate, _soitem.coitem_scheddate),
      _soitem.coitem_promdate,
      _soitem.coitem_qtyord,
      stdCost(_soitem.itemsite_item_id),
      _soitem.coitem_price,
      _soitem.coitem_custprice,
      0.0,
      -1,
      _soitem.coitem_memo,
      FALSE,
      0.0,
      NULL,
      _soitem.coitem_custpn,
      _soitem.coitem_order_type,
      NULL,
--      NULL,
      _soitem.coitem_substitute_item_id,
      NULL,
      getEffectiveXtUser(),
      _soitem.coitem_prcost,
      _soitem.coitem_qty_uom_id,
      _soitem.coitem_qty_invuomratio,
      _soitem.coitem_price_uom_id,
      _soitem.coitem_price_invuomratio,
      _soitem.coitem_warranty,
      _soitem.coitem_cos_accnt_id,
      0.0,
      _soitem.coitem_subnumber,
      _soitem.coitem_firm,
      _soitem.coitem_taxtype_id );

  END LOOP;

  RETURN _soheadid;

END;
$$;


ALTER FUNCTION public.copyso(psoheadid integer, pscheddate date) OWNER TO admin;

--
-- TOC entry 1268 (class 1255 OID 146565816)
-- Dependencies: 4536 8
-- Name: copytodoitem(integer, date, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION copytodoitem(integer, date, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pparentid   ALIAS FOR $1;
  _duedate    DATE := COALESCE($2, CURRENT_DATE);
  pincdtid    ALIAS FOR $3;
  _alarmid    INTEGER;
  _todoitemid INTEGER;

BEGIN
  INSERT INTO todoitem(
            todoitem_name,      todoitem_description,
            todoitem_incdt_id,
            todoitem_creator_username,                  todoitem_status,
            todoitem_active,    todoitem_due_date,
            todoitem_assigned_date,
            todoitem_seq,       todoitem_notes,         todoitem_crmacct_id,
            todoitem_ophead_id, todoitem_owner_username,todoitem_priority_id,
            todoitem_username,  todoitem_recurring_todoitem_id
  ) SELECT  todoitem_name,      todoitem_description,
            CASE WHEN pincdtid IS NULL THEN todoitem_incdt_id ELSE pincdtid END,
            getEffectiveXtUser(),                               'N',
            TRUE,               _duedate,
            CASE WHEN (todoitem_username IS NOT NULL) THEN CURRENT_DATE
                 ELSE NULL
            END,
            todoitem_seq,       todoitem_notes,         todoitem_crmacct_id,
            todoitem_ophead_id, todoitem_owner_username,todoitem_priority_id,
            todoitem_username,  todoitem_recurring_todoitem_id
      FROM todoitem
     WHERE (todoitem_id=pparentid)
  RETURNING todoitem_id INTO _todoitemid;

  IF (_todoitemid IS NULL) THEN
    RETURN -10;
  END IF;

  SELECT saveAlarm(NULL, NULL, _duedate,
                   CAST(alarm_time - DATE_TRUNC('day',alarm_time) AS TIME),
                   alarm_time_offset,
                   alarm_time_qualifier,
                   (alarm_event_recipient IS NOT NULL), alarm_event_recipient,
                   (alarm_email_recipient IS NOT NULL AND fetchMetricBool('EnableBatchManager')), alarm_email_recipient,
                   (alarm_sysmsg_recipient IS NOT NULL), alarm_sysmsg_recipient,
                   'TODO', _todoitemid, 'CHANGEONE')
    INTO _alarmid
    FROM alarm
   WHERE ((alarm_source='TODO')
      AND (alarm_source_id=pparentid));

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

  RETURN _todoitemid;
END;
$_$;


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

--
-- TOC entry 1269 (class 1255 OID 146565817)
-- Dependencies: 4536 8
-- Name: copyvoucher(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION copyvoucher(integer, date) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pVoheadid ALIAS FOR $1;
  _voheadid INTEGER;
  _vonumber TEXT;
  _vodate DATE := COALESCE($2, CURRENT_DATE);
  _i RECORD;
  _l RECORD;
  _vodistid INTEGER;

BEGIN
  SELECT *
    INTO _i
    FROM vohead
   WHERE(vohead_id=pVoheadid);
  IF(NOT FOUND) THEN
    RETURN -1;
  END IF;

  _vonumber := fetchVoNumber();
  _voheadid := nextval('vohead_vohead_id_seq');

  INSERT INTO vohead
        (vohead_id,
         vohead_number, vohead_pohead_id,
         vohead_posted, vohead_duedate,
         vohead_invcnumber, vohead_amount,
         vohead_docdate, vohead_1099,
         vohead_distdate, vohead_reference,
         vohead_terms_id, vohead_vend_id,
         vohead_curr_id, vohead_adjtaxtype_id,
         vohead_freighttaxtype_id, vohead_gldistdate,
         vohead_misc, vohead_taxzone_id,
         vohead_taxtype_id, vohead_notes,
         vohead_recurring_vohead_id )
  VALUES(_voheadid,
         _vonumber, _i.vohead_pohead_id,
         false, determineDueDate(_i.vohead_terms_id, _vodate),
         _i.vohead_invcnumber, _i.vohead_amount,
         _vodate, _i.vohead_1099,
         _vodate, _i.vohead_reference,
         _i.vohead_terms_id, _i.vohead_vend_id,
         _i.vohead_curr_id, _i.vohead_adjtaxtype_id,
         _i.vohead_freighttaxtype_id, _vodate,
         _i.vohead_misc, _i.vohead_taxzone_id,
         _i.vohead_taxtype_id, _i.vohead_notes,
         _i.vohead_recurring_vohead_id);

  FOR _l IN SELECT *
            FROM vodist
            WHERE (vodist_vohead_id=pVoheadid) LOOP
    SELECT NEXTVAL('vodist_vodist_id_seq') INTO _vodistid;

    INSERT INTO vodist
        (vodist_id, vodist_poitem_id,
         vodist_vohead_id, vodist_costelem_id,
         vodist_accnt_id, vodist_amount,
         vodist_qty, vodist_expcat_id,
         vodist_tax_id, vodist_discountable,
         vodist_notes)
    VALUES
        (_vodistid, _l.vodist_poitem_id,
         _voheadid, _l.vodist_costelem_id,
         _l.vodist_accnt_id, _l.vodist_amount,
         _l.vodist_qty, _l.vodist_expcat_id,
         _l.vodist_tax_id, _l.vodist_discountable,
         _l.vodist_notes);

  END LOOP;

  RETURN _voheadid;
END;
$_$;


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

--
-- TOC entry 1270 (class 1255 OID 146565818)
-- Dependencies: 4536 8
-- Name: correctporeceipt(integer, numeric, numeric, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION correctporeceipt(integer, numeric, 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
  pPorecvid ALIAS FOR $1;
  pQty ALIAS FOR $2;
  pFreight ALIAS FOR $3;
  pItemlocSeries ALIAS FOR $4;

BEGIN
  RETURN correctReceipt('PO', $1, $2, $3, $4, NULL, NULL);
END;
$_$;


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

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

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


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

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

CREATE FUNCTION correctproduction(integer, numeric, boolean, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RAISE NOTICE 'correctProduction(INTEGER, NUMERIC, BOOLEAN, BOOLEAN) has been deprecated. Use corrrectProduction(INTEGER, NUMERIC, BOOLEAN, INTEGER) or a package-specific version instead.';
  RETURN  correctProduction($1, $2, $3, 0, now());
END;
$_$;


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

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

CREATE FUNCTION correctproduction(integer, numeric, boolean, boolean, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RAISE NOTICE 'correctProduction(INTEGER, NUMERIC, BOOLEAN, BOOLEAN, INTEGER) has been deprecated. Use corrrectProduction(INTEGER, NUMERIC, BOOLEAN, INTEGER) or a package-specific version instead.';
  RETURN correctProduction($1, $2, $3, $5, now());
END;
$_$;


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

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

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


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

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

CREATE FUNCTION correctproduction(integer, numeric, boolean, 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
  pWoid          ALIAS FOR $1;
  pQty           ALIAS FOR $2;
  pBackflush     ALIAS FOR $3;
  pItemlocSeries ALIAS FOR $4;
  pGlDistTS      ALIAS FOR $5;
  pInvhistId     ALIAS FOR $6;
  _invhistid        INTEGER;
  _itemlocSeries    INTEGER;
  _r                RECORD;
  _parentWIPAccntid INTEGER;
  _parentQty        NUMERIC;
  _qty              NUMERIC;
  _wipPost          NUMERIC;
  _sense            TEXT;
  _status           TEXT;
  _type             TEXT;
  _qtyfxd           NUMERIC := 0;

BEGIN

  -- Qty is positive for Assembly W/O
  -- Qty is negative for Disassembly W/O
  IF (pQty = 0) THEN
    RETURN pItemlocseries;
  ELSIF (pQty > 0) THEN
    _sense := 'from';
  ELSE
    _sense := 'to';
  END IF;

  SELECT item_type, roundQty(item_fractional, pQty), wo_status
    INTO _type, _parentQty, _status
    FROM wo JOIN itemsite ON (itemsite_id=wo_itemsite_id)
            JOIN item ON (item_id=itemsite_item_id)
   WHERE (wo_id=pWoid);
  
  IF (_status != 'I') THEN
    RETURN -1;
  END IF;

  IF (_type = 'J') THEN
    RETURN -2;
  END IF;

  IF (pItemlocSeries = 0) THEN
    SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
  ELSE
    _itemlocSeries := pItemlocSeries;
  END IF;

  --  Calculate the WIP to correct 
  SELECT CASE WHEN (wo_cosmethod = 'D') THEN wo_postedvalue
              ELSE  round(((wo_postedvalue - wo_wipvalue) / wo_qtyrcv * _parentQty), 2)
         END INTO _wipPost
  FROM wo
  WHERE (wo_id=pWoid);

  --  Post the inventory transaction
  SELECT postInvTrans( itemsite_id, 'RM', (_parentQty * -1.0),
                       'W/O', 'WO', formatwonumber(pWoid), '',
                       ('Correct Receive Inventory ' || item_number || ' ' || _sense || ' Manufacturing'),
                       costcat_asset_accnt_id, getPrjAccntId(wo_prj_id, costcat_wip_accnt_id), _itemlocSeries, pGlDistTS,
                       (_wipPost * -1.0), -- only used when cost is average
                       pInvhistId) INTO _invhistid
  FROM wo JOIN itemsite ON (itemsite_id=wo_itemsite_id)
          JOIN item ON (item_id=itemsite_item_id)
          JOIN costcat ON (costcat_id=itemsite_costcat_id)
  WHERE (wo_id=pWoid);

  --  Decrease this W/O's qty. received and increase its WIP value
  UPDATE wo
  SET wo_qtyrcv = (wo_qtyrcv - _parentQty),
      wo_wipvalue = (wo_wipvalue + (CASE WHEN(itemsite_costmethod IN ('A','J'))
                                              THEN _wipPost
                                         WHEN(itemsite_costmethod='S')
                                              THEN stdcost(itemsite_item_id) * _parentQty
                                         ELSE 0.0 END))
  FROM itemsite
  WHERE ( (wo_itemsite_id=itemsite_id)
   AND (wo_id=pWoid) );

  IF (pBackflush) THEN
    FOR _r IN SELECT item_id, item_fractional,
                      itemsite_id, itemsite_warehous_id,
                      itemsite_controlmethod, itemsite_loccntrl,
                      itemsite_costmethod, 
                      wo_qtyrcv, wo_prj_id,
                      womatl_id, womatl_qtyfxd, womatl_qtyper,
                      womatl_scrap, womatl_issuemethod, womatl_uom_id
               FROM wo JOIN womatl ON (womatl_wo_id=wo_id AND womatl_issuemethod='L')
                       JOIN itemsite ON (itemsite_id=womatl_itemsite_id)
                       JOIN item ON (item_id=itemsite_item_id)
               WHERE (wo_id=pWoid) LOOP

      --  Cache the qty to be issued
      -- If going back to beginning, unissue fixed qty as well
      IF (_r.wo_qtyrcv - _parentQty > 0) THEN
        _qtyfxd := 0;
      ELSE
        _qtyfxd := _r.womatl_qtyfxd;
      END IF;
      
      _qty = roundQty(_r.item_fractional, (_qtyfxd + _parentQty * _r.womatl_qtyper) * (1 + _r.womatl_scrap));

      IF (_qty > 0) THEN
        SELECT returnWoMaterial(_r.womatl_id, _qty, _itemlocSeries, pGlDistTS) INTO _itemlocSeries;
      END IF;

    END LOOP;

  	--  BEGIN ROB Decrease this W/O's WIP value for custom costing
	  UPDATE wo
	  SET wo_wipvalue = (wo_wipvalue - (itemcost_stdcost * _parentQty)) 
	FROM costelem, itemcost, costcat, itemsite, item
	WHERE 
	  ((wo_id=pWoid) AND
	  (wo_itemsite_id=itemsite_id) AND
	  (itemsite_item_id=item_id) AND
	  (costelem_id = itemcost_costelem_id) AND
	  (itemcost_item_id = itemsite_item_id) AND
	  (itemsite_costcat_id = costcat_id) AND
	  (costelem_exp_accnt_id) IS NOT NULL  AND 
	  (costelem_sys = false));

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

  END IF;

  RETURN _itemlocSeries;

END;
$_$;


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

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

CREATE FUNCTION correctreceipt(integer, numeric, numeric, integer, integer, date) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
BEGIN
  RETURN correctReceipt($1, $2, $3, $4, $5, $6, NULL);
END;
$_$;


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

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

CREATE FUNCTION correctreceipt(integer, numeric, numeric, integer, integer, date, numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  precvid		ALIAS FOR $1;
  pQty			ALIAS FOR $2;
  pFreight		ALIAS FOR $3;
  _itemlocSeries	INTEGER := COALESCE($4, 0);
  _currid		INTEGER := $5;
  pEffective		ALIAS FOR $6;
  pRecvCost		ALIAS FOR $7;
  _freight		NUMERIC;
  _qty			NUMERIC;
  _invhistid		INTEGER;
  _o			RECORD;
  _r			RECORD;
  _recvcost		NUMERIC;
  _tmp        INTEGER;
  _pricevar             NUMERIC := 0.00;
  _journalNumber INTEGER := fetchJournalNumber('GL-MISC');

BEGIN
  SELECT recv_qty, recv_date::DATE AS recv_date, recv_freight_curr_id,
	 recv_orderitem_id,
	 round(currToCurr(recv_freight_curr_id,
			  COALESCE(_currid, recv_freight_curr_id),
         recv_freight, recv_date::DATE),2) AS recv_freight,
         recv_posted, recv_order_type,
         COALESCE(itemsite_id, -1) AS itemsiteid,
	 itemsite_item_id, itemsite_costmethod, itemsite_controlmethod,
	 (recv_splitfrom_id IS NOT NULL
	 OR (SELECT (count(*) > 0) 
	     FROM recv
	     WHERE (recv_splitfrom_id=recv_id))) AS split INTO _r
  FROM recv LEFT OUTER JOIN itemsite ON (recv_itemsite_id=itemsite_id)
  WHERE (recv_id=precvid);

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

  IF (NOT _r.recv_order_type IN ('PO', 'RA', 'TO')) THEN
    RETURN -11;
  END IF;

  IF (_r.split) THEN
    RETURN -12;
  END IF;

  SELECT currToBase(orderitem_unitcost_curr_id, orderitem_unitcost,
		    _r.recv_date::DATE) AS unitprice_base,
	 orderhead_number, orderitem_linenumber,
	 orderhead_curr_id AS freight_curr_id,
	 orderitem_orderhead_type,
	 orderitem_qty_invuomratio INTO _o
  FROM orderhead, orderitem
  WHERE ((orderhead_id=orderitem_orderhead_id)
    AND  (orderhead_type=orderitem_orderhead_type)
    AND  (orderitem_id=_r.recv_orderitem_id)
    AND  (orderitem_orderhead_type=_r.recv_order_type));

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

  -- Default to _o.orderitem_unitcost if recv_purchcost is not supplied
  -- Note: this should never happen, a value is always supplied
  if (pRecvCost IS NULL) THEN
    _recvcost := _o.orderitem_unitcost;
  ELSE
    -- Note: if the receipt has already been posted, pRecvCost will always 
    --       equal the original recv_purchcost (cannot be modified in GUI)
    _recvcost := pRecvCost; 
  END IF;

  IF (_r.recv_posted) THEN
    _qty := (pQty - _r.recv_qty);
    IF (_qty <> 0) THEN
      IF (_r.itemsiteid = -1) THEN
        PERFORM insertGLTransaction( _journalNumber,'S/R',
                                     _r.recv_order_type,
                                     _o.orderhead_number,
                                     'Receive Non-Inventory from ' || _r.recv_order_type,
                                     expcat_liability_accnt_id,
                                     getPrjAccntId(poitem_prj_id, expcat_exp_accnt_id),
                                     -1,
                                     ROUND(_o.unitprice_base * _qty, 2),
                                     pEffective )
        FROM poitem, expcat
        WHERE ((poitem_expcat_id=expcat_id)
          AND  (poitem_id=_r.recv_orderitem_id)
          AND  (_o.orderitem_orderhead_type='PO'));

        UPDATE recv
        SET recv_qty=pQty,
            recv_value=(recv_value + ROUND(_o.unitprice_base * _qty, 2)),
            recv_date = pEffective
        WHERE (recv_id=precvid);
      ELSEIF (_r.itemsite_controlmethod = 'N') THEN
        PERFORM insertGLTransaction( _journalNumber,'S/R',
                                     _r.recv_order_type,
                                     _o.orderhead_number,
                                     'Receive Non-Controlled Inventory from ' || _r.recv_order_type,
                                     costcat_liability_accnt_id,
                                     getPrjAccntId(poitem_prj_id, costcat_exp_accnt_id),
                                     -1,
                                     ROUND(_o.unitprice_base * _qty, 2),
                                     pEffective )
        FROM poitem, itemsite, costcat
        WHERE ((poitem_itemsite_id=itemsite_id)
          AND  (itemsite_costcat_id=costcat_id)
          AND  (poitem_id=_r.recv_orderitem_id)
          AND  (_o.orderitem_orderhead_type='PO'));

        UPDATE recv
        SET recv_qty=pQty,
            recv_value=(recv_value + ROUND(_o.unitprice_base * _qty, 2)),
            recv_date = pEffective
        WHERE (recv_id=precvid);
      ELSE
        IF (_itemlocSeries = 0 OR _itemlocSeries IS NULL) THEN
          _itemlocSeries := NEXTVAL('itemloc_series_seq');
        END IF;

  SELECT postInvTrans( itemsite_id, 'RP',
			     (_qty * _o.orderitem_qty_invuomratio),
			     'S/R', _r.recv_order_type,
			     _o.orderhead_number::TEXT || '-' || _o.orderitem_linenumber::TEXT, '',
			     'Receive Inventory from ' || _r.recv_order_type,
			     costcat_asset_accnt_id,
			     costcat_liability_accnt_id,
			     _itemlocSeries, pEffective,
           ROUND(_recvcost * _qty, 2) -- alway passing since it is ignored if not average costed item
                           ) INTO _tmp
	FROM itemsite, costcat
	WHERE ((itemsite_costcat_id=costcat_id)
    AND  (itemsite_id=_r.itemsiteid) );

        IF(_r.itemsite_costmethod='A') THEN
	  UPDATE recv
	     SET recv_qty=pQty,
	         recv_value=(recv_value + _recvcost * _qty * _o.orderitem_qty_invuomratio),
                 recv_date = pEffective
	   WHERE(recv_id=precvid);
        ELSE
	  UPDATE recv
	     SET recv_qty=pQty,
	         recv_value=(recv_value + stdcost(_r.itemsite_item_id) * _qty * _o.orderitem_qty_invuomratio),
                 recv_date = pEffective
	   WHERE(recv_id=precvid);
        END IF;
    END IF;

      IF (_r.recv_order_type = 'PO') THEN
	UPDATE poitem
	SET poitem_qty_received=(poitem_qty_received + _qty)
	WHERE (poitem_id=_r.recv_orderitem_id);
      ELSIF (_r.recv_order_type = 'RA' AND fetchMetricBool('EnableReturnAuth')) THEN
	UPDATE raitem
	SET raitem_qtyreceived=(raitem_qtyreceived + _qty)
	WHERE (raitem_id=_r.recv_orderitem_id);
      ELSIF (_r.recv_order_type = 'TO' AND fetchMetricBool('MultiWhs')) THEN
	UPDATE toitem
	SET toitem_qty_received=(toitem_qty_received + _qty)
	WHERE (toitem_id=_r.recv_orderitem_id);
      END IF;

    END IF;

       IF (fetchMetricBool('RecordPPVonReceipt')) THEN -- If the 'Purchase Price Variance on Receipt' option is true
         _invhistid := _tmp;
         -- Find the difference in the purchase price value expected from the P/O and the value of the transaction
         SELECT (((currToBase(pohead_curr_id,
         COALESCE(recv_purchcost, poitem_unitprice),
         recv_date::DATE)) * _qty) - (invhist_value_after - invhist_value_before)) INTO _pricevar
         FROM invhist, recv, pohead, poitem
         WHERE ((recv_orderitem_id=poitem_id)
           AND  (poitem_pohead_id=pohead_id)
           AND  (recv_id=precvid)
           AND  (invhist_id = _invhistid));

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

    _freight := (pFreight - _r.recv_freight);
    IF (_freight <> 0) THEN

      IF (_r.itemsiteid = -1) THEN
  PERFORM insertGLTransaction( _journalNumber,'S/R', _r.recv_order_type,
				     _o.orderhead_number,
				    'Receive Non-Inventory Freight from ' || _r.recv_order_type,
             expcat_liability_accnt_id, getPrjAccntId(poitem_prj_id, expcat_freight_accnt_id), -1,
				      ROUND(currToBase(_currid, _freight,
						    pEffective), 2),
				     pEffective )
	FROM poitem, expcat
	WHERE ((poitem_expcat_id=expcat_id)
	  AND  (poitem_id=_r.recv_orderitem_id)
	  AND  (_r.recv_order_type='PO'));
      ELSE
  PERFORM insertGLTransaction(_journalNumber,'S/R', _r.recv_order_type,
				    _o.orderhead_number, 
				    'Receive Non-Inventory Freight from ' ||
							    _r.recv_order_type,
				   costcat_liability_accnt_id,
				   costcat_freight_accnt_id, -1,
				   round(currToBase(_currid, _freight,
						    pEffective), 2),
				   pEffective )
	FROM itemsite, costcat
	WHERE ( (itemsite_costcat_id=costcat_id)
	  AND   (itemsite_id=_r.itemsiteid) );
      END IF;

      IF (_r.recv_order_type = 'PO') THEN
	UPDATE poitem
	SET poitem_freight_received=(poitem_freight_received +
				   currToCurr(_currid, _o.freight_curr_id,
					      _freight, pEffective))
	WHERE (poitem_id=_r.recv_orderitem_id);

      -- raitem does not track freight

      ELSEIF (_r.recv_order_type = 'TO' AND fetchMetricBool('MultiWhs')) THEN
	UPDATE toitem
	SET toitem_freight_received=(toitem_freight_received +
				   currToCurr(_currid, _o.freight_curr_id,
					      _freight, pEffective))
	WHERE (toitem_id=_r.recv_orderitem_id);
      END IF;

      UPDATE recv
      SET recv_freight=currToCurr(_currid, recv_freight_curr_id, pFreight,
				  pEffective),
	  recv_date = pEffective
      WHERE (recv_id=precvid);
    END IF;

  ELSE

-- Receipt not posted yet
    UPDATE recv SET recv_qty=pQty, recv_freight=pFreight, recv_purchcost=_recvcost WHERE recv_id=precvid;
  END IF;

RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 1278 (class 1255 OID 146565828)
-- Dependencies: 4536 8
-- Name: cosbycustomervalue(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION cosbycustomervalue(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;
  _startDate DATE;
  _endDate DATE;

BEGIN

  _startDate := findPeriodStart(pPeriodid);
  _endDate := findPeriodEnd(pPeriodid);

-- Returns value in base currency
-- ToDo: is cohist_shipdate the right DATE to use?
  SELECT SUM(cohist_qtyshipped * currToBase(cohist_curr_id, cohist_unitcost, cohist_shipdate)) INTO _value
  FROM cohist
  WHERE ( (cohist_cust_id=pCustid)
   AND (cohist_invcdate BETWEEN _startDate AND _endDate) );

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

  RETURN _value;

END;
$_$;


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

--
-- TOC entry 1279 (class 1255 OID 146565829)
-- Dependencies: 4536 8
-- Name: costsbycustomerbyitemsite(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION costsbycustomerbyitemsite(integer, 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;
  pItemsiteid ALIAS FOR $2;
  pPeriodid ALIAS FOR $3;
  _value NUMERIC;
  _startDate DATE;
  _endDate DATE;

BEGIN

  _startDate := findPeriodStart(pPeriodid);
  _endDate := findPeriodEnd(pPeriodid);

-- Returns value in base currency
-- ToDo: is cohist_shipdate the right DATE to use?
  SELECT SUM(cohist_qtyshipped * currToBase(cohist_curr_id, cohist_unitcost, cohist_shipdate)) INTO _value
  FROM cohist
  WHERE ( (cohist_itemsite_id<>pItemsiteid)
   AND (cohist_cust_id=pCustid)
   AND (cohist_invcdate BETWEEN _startDate AND _endDate) );

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

  RETURN _value;

END;
$_$;


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

--
-- TOC entry 1281 (class 1255 OID 146565830)
-- Dependencies: 4536 8
-- Name: costsbycustomervalue(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION costsbycustomervalue(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;
  _startDate DATE;
  _endDate DATE;

BEGIN

  _startDate := findPeriodStart(pPeriodid);
  _endDate := findPeriodEnd(pPeriodid);

-- Returns value in base currency
-- ToDo: is cohist_shipdate the right DATE to use?
  SELECT SUM(cohist_qtyshipped * currToBase(cohist_curr_id, cohist_unitcost, cohist_shipdate)) INTO _value
  FROM cohist
  WHERE ( (cohist_itemsite_id<>-1)
   AND (cohist_cust_id=pCustid)
   AND (cohist_invcdate BETWEEN _startDate AND _endDate) );

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

  RETURN _value;

END;
$_$;


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

--
-- TOC entry 1282 (class 1255 OID 146565831)
-- Dependencies: 4536 8
-- Name: costsbycustomervalue(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION costsbycustomervalue(integer, 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;
  pProdcatid ALIAS FOR $3;
  _value NUMERIC;
  _startDate DATE;
  _endDate DATE;

BEGIN

  _startDate := findPeriodStart(pPeriodid);
  _endDate := findPeriodEnd(pPeriodid);

-- Returns value in base currency
-- ToDo: is cohist_shipdate the right DATE to use?
  SELECT SUM(cohist_qtyshipped * currToBase(cohist_curr_id, cohist_unitcost, cohist_shipdate)) INTO _value
  FROM cohist, itemsite, item
  WHERE ( (cohist_cust_id=pCustid)
   AND (cohist_itemsite_id=itemsite_id)
   AND (itemsite_item_id=item_id)
   AND (item_prodcat_id=pProdcatid)
   AND (cohist_invcdate BETWEEN _startDate AND _endDate) );

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

  RETURN _value;

END;
$_$;


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

--
-- TOC entry 1283 (class 1255 OID 146565832)
-- Dependencies: 4536 8
-- Name: costsbycustomervalue(integer, integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION costsbycustomervalue(integer, 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
  pCustid ALIAS FOR $1;
  pPeriodid ALIAS FOR $2;
  pProdcat ALIAS FOR $3;
  _value NUMERIC;
  _startDate DATE;
  _endDate DATE;

BEGIN

  _startDate := findPeriodStart(pPeriodid);
  _endDate := findPeriodEnd(pPeriodid);

-- Returns value in base currency
-- ToDo: is cohist_shipdate the right date to use?
  SELECT SUM(cohist_qtyshipped * currToBase(cohist_curr_id, cohist_unitcost, cohist_shipdate)) INTO _value
  FROM cohist, itemsite, item, prodcat
  WHERE ( (cohist_cust_id=pCustid)
   AND (cohist_itemsite_id=itemsite_id)
   AND (itemsite_item_id=item_id)
   AND (item_prodcat_id=prodcat_id)
   AND (prodcat_code ~ pProdcat)
   AND (cohist_invcdate BETWEEN _startDate AND _endDate) );

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

  RETURN _value;

END;
$_$;


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

--
-- TOC entry 1284 (class 1255 OID 146565833)
-- Dependencies: 4536 8
-- Name: createaccountingperiod(date, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RETURN createAccountingPeriod(pStartDate, pEndDate, NULL, NULL);

END;
$_$;


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

--
-- TOC entry 1285 (class 1255 OID 146565834)
-- Dependencies: 4536 8
-- Name: createaccountingperiod(date, date, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createaccountingperiod(date, date, 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
  pStartDate ALIAS FOR $1;
  pEndDate ALIAS FOR $2;
  pYearPeriodId ALIAS FOR $3;
  pQuarter ALIAS FOR $4;
  _periodid INTEGER;
  _check INTEGER;
  _r RECORD;
  _initial BOOLEAN;
  _number INTEGER;

BEGIN

--  Make that the passed start date doesn't fall into any existing period
  SELECT period_id INTO _check
  FROM period
  WHERE (pStartDate BETWEEN period_start AND period_end);
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Make that the passed end date doesn't fall into any existing period
  SELECT period_id INTO _check
  FROM period
  WHERE (pEndDate BETWEEN period_start AND period_end);
  IF (FOUND) THEN
    RETURN -2;
  END IF;

--  Make that the passed start and end dates don't enclose an existing period
  SELECT period_id INTO _check
  FROM period
  WHERE ( (period_start >= pStartDate)
   AND (period_end <= pEndDate) );
  IF (FOUND) THEN
    RETURN -3;
  END IF;

-- Make sure period is inside fiscal year
  SELECT yearperiod_id INTO _check
  FROM yearperiod
  WHERE ((yearperiod_id=pYearPeriodId)
  AND (pStartDate>=yearperiod_start)
  AND (pEndDate<=yearperiod_end));
  IF NOT (FOUND) THEN
    RETURN -4;
  END IF;

--  Determine if this is the initial accounting period
  SELECT CASE WHEN(count(*) > 0) THEN FALSE
              ELSE TRUE
         END INTO _initial
  FROM period;

-- Determine the next number
  SELECT COALESCE(MAX(period_number),0) + 1 INTO _number
  FROM period
  WHERE (period_yearperiod_id=pYearPeriodId);

--  Create the new accounting period
  SELECT NEXTVAL('period_period_id_seq') INTO _periodid;
  INSERT INTO period
  ( period_id, period_start, period_end, period_closed, period_freeze, 
    period_initial, period_number, period_yearperiod_id, period_quarter )
  VALUES
  ( _periodid, pStartDate, pEndDate, FALSE, FALSE, _initial, _number, pYearPeriodId, pQuarter );

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

  RETURN _periodid;

END;
$_$;


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

--
-- TOC entry 1286 (class 1255 OID 146565835)
-- Dependencies: 4536 8
-- Name: createaccountingyearperiod(date, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createaccountingyearperiod(date, date) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pStartDate ALIAS FOR $1;
  pEndDate ALIAS FOR $2;
  _yearperiodid INTEGER;
  _check INTEGER;
  _checkBool BOOLEAN;
  _r RECORD;
  _initial BOOLEAN;

BEGIN

--  Make that the passed start date doesn't fall into any existing yearperiod
  SELECT yearperiod_id INTO _check
  FROM yearperiod
  WHERE (pStartDate BETWEEN yearperiod_start AND yearperiod_end);
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Make that the passed end date doesn't fall into any existing yearperiod
  SELECT yearperiod_id INTO _check
  FROM yearperiod
  WHERE (pEndDate BETWEEN yearperiod_start AND yearperiod_end);
  IF (FOUND) THEN
    RETURN -2;
  END IF;

--  Make that the passed start and end dates don't enclose an existing yearperiod
  SELECT yearperiod_id INTO _check
  FROM yearperiod
  WHERE ( (yearperiod_start >= pStartDate)
   AND (yearperiod_end <= pEndDate) );
  IF (FOUND) THEN
    RETURN -3;
  END IF;

--  Make sure that the passed start is prior to the end date
  SELECT (pStartDate > pEndDate) INTO _checkBool;
  IF (_checkBool) THEN
    RETURN -5;
  END IF;

--  Determine if this is the initial accounting yearperiod
  SELECT CASE WHEN(count(*) > 0) THEN FALSE
              ELSE TRUE
         END INTO _initial
  FROM yearperiod;

--  Create the new accounting yearperiod
  SELECT NEXTVAL('yearperiod_yearperiod_id_seq') INTO _yearperiodid;
  INSERT INTO yearperiod
  ( yearperiod_id, yearperiod_start, yearperiod_end, yearperiod_closed )
  VALUES
  ( _yearperiodid, pStartDate, pEndDate, FALSE );

  RETURN _yearperiodid;

END;
$_$;


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

--
-- TOC entry 1287 (class 1255 OID 146565836)
-- Dependencies: 4536 8
-- Name: createapchecks(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createapchecks(integer, date) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RAISE NOTICE 'createAPChecks() is deprecated - use createChecks() instead';
  RETURN createChecks($1, $2);
END;
$_$;


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

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

CREATE FUNCTION createapcreditmemo(integer, text, text, date, numeric, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pVendid ALIAS FOR $1;
  pDocNumber ALIAS FOR $2;
  pPoNumber ALIAS FOR $3;
  pDocDate ALIAS FOR $4;
  pAmount ALIAS FOR $5;
  pNotes ALIAS FOR $6;
  _result INTEGER;

BEGIN

  SELECT createAPCreditMemo( pVendid, fetchJournalNumber('AP-MISC'),
                             pDocNumber, pPoNumber, pDocDate, pAmount, pNotes, -1, pDocDate, -1, baseCurrId() ) INTO _result;

  RETURN _result;

END;
$_$;


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

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

CREATE FUNCTION createapcreditmemo(integer, integer, text, text, date, numeric, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pVendid ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  pDocNumber ALIAS FOR $3;
  pPoNumber ALIAS FOR $4;
  pDocDate ALIAS FOR $5;
  pAmount ALIAS FOR $6;
  pNotes ALIAS FOR $7;

BEGIN
  RETURN createAPCreditMemo(pVendid, pJournalNumber, pDocNumber, pPoNumber, pDocDate, pAmount, pNotes, -1, pDocDate, -1, baseCurrId() );
END;
$_$;


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

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

CREATE FUNCTION createapcreditmemo(integer, text, text, date, numeric, 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
  pVendid ALIAS FOR $1;
  pDocNumber ALIAS FOR $2;
  pPoNumber ALIAS FOR $3;
  pDocDate ALIAS FOR $4;
  pAmount ALIAS FOR $5;
  pNotes ALIAS FOR $6;
  pAccntid ALIAS FOR $7;
  _result INTEGER;

BEGIN

  SELECT createAPCreditMemo( pVendid, fetchJournalNumber('AP-MISC'),
                             pDocNumber, pPoNumber, pDocDate, pAmount, pNotes, pAccntid, pDocDate, -1, baseCurrId() ) INTO _result;

  RETURN _result;

END;
$_$;


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

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

CREATE FUNCTION createapcreditmemo(integer, integer, text, text, date, numeric, 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
  pVendid ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  pDocNumber ALIAS FOR $3;
  pPoNumber ALIAS FOR $4;
  pDocDate ALIAS FOR $5;
  pAmount ALIAS FOR $6;
  pNotes ALIAS FOR $7;
  pAccntid ALIAS FOR $8;
BEGIN
  RETURN createAPCreditMemo( pVendid, pJournalNumber,
                             pDocNumber, pPoNumber, pDocDate, pAmount, pNotes, pAccntid, pDocDate, -1, baseCurrId() );
END;
$_$;


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

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

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

BEGIN

  SELECT createAPCreditMemo( pVendid, fetchJournalNumber('AP-MISC'),
                             pDocNumber, pPoNumber, pDocDate, pAmount, pNotes, pAccntid, pDueDate, pTermsid, baseCurrId() ) INTO _result;

  RETURN _result;

END;
$_$;


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

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

CREATE FUNCTION createapcreditmemo(integer, integer, text, text, date, numeric, text, integer, date, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pVendid ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  pDocNumber ALIAS FOR $3;
  pPoNumber ALIAS FOR $4;
  pDocDate ALIAS FOR $5;
  pAmount ALIAS FOR $6;
  pNotes ALIAS FOR $7;
  pAccntid ALIAS FOR $8;
  pDueDate ALIAS FOR $9;
  pTermsid ALIAS FOR $10;
BEGIN
  RETURN createAPCreditMemo( pVendid, pJournalNumber, pDocNumber, pPoNumber, pDocDate, pAmount, pNotes, pAccntid, pDueDate, pTermsid, baseCurrId() );
END;
$_$;


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

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

CREATE FUNCTION createapcreditmemo(integer, integer, text, text, date, numeric, text, integer, date, 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
  pVendid ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  pDocNumber ALIAS FOR $3;
  pPoNumber ALIAS FOR $4;
  pDocDate ALIAS FOR $5;
  pAmount ALIAS FOR $6;
  pNotes ALIAS FOR $7;
  pAccntid ALIAS FOR $8;
  pDueDate ALIAS FOR $9;
  pTermsid ALIAS FOR $10;
  pCurrId ALIAS FOR $11;
BEGIN
  RETURN createAPCreditMemo( NULL, pVendid, pJournalNumber, pDocNumber, pPoNumber, pDocDate, pAmount, pNotes, pAccntid, pDueDate, pTermsid, pCurrId );
END;
$_$;


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

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

CREATE FUNCTION createapcreditmemo(integer, integer, integer, text, text, date, numeric, text, integer, date, 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
  pId ALIAS FOR $1;
  pVendid ALIAS FOR $2;
  pJournalNumber ALIAS FOR $3;
  pDocNumber ALIAS FOR $4;
  pPoNumber ALIAS FOR $5;
  pDocDate ALIAS FOR $6;
  pAmount ALIAS FOR $7;
  pNotes ALIAS FOR $8;
  pAccntid ALIAS FOR $9;
  pDueDate ALIAS FOR $10;
  pTermsid ALIAS FOR $11;
  pCurrId ALIAS FOR $12;
  _vendName TEXT;
  _apAccntid INTEGER;
  _prepaidAccntid INTEGER;
  _accntid INTEGER;
  _glSequence INTEGER;
  _journalNumber INTEGER;
  _apopenid INTEGER;
  _baseAmount NUMERIC;
  _taxBaseValue NUMERIC;
  _test INTEGER;

BEGIN

  _apopenid := pId;

  SELECT findAPAccount(pVendid) INTO _apAccntid;
  SELECT findAPPrepaidAccount(pVendid) INTO _prepaidAccntid;

  SELECT vend_name INTO _vendName
  FROM vendinfo
  WHERE (vend_id=pVendid);
  
  _accntid := pAccntid;

  PERFORM accnt_id
     FROM accnt
    WHERE (accnt_id=_accntid);
  IF (FOUND) THEN
    _prepaidAccntid := _accntid;
  ELSE
    _accntid := -1;
  END IF;

  IF(pJournalNumber IS NULL) THEN
    SELECT fetchJournalNumber('AP-MISC') INTO _journalNumber;
  ELSE
    _journalNumber := pJournalNumber;
  END IF;

  SELECT fetchGLSequence() INTO _glSequence;

  IF (_apopenid IS NOT NULL) THEN
    UPDATE apopen SET
      apopen_username=getEffectiveXtUser(), apopen_journalnumber=_journalNumber,
      apopen_vend_id=pVendid, apopen_docnumber=pDocNumber,
      apopen_doctype='C', apopen_ponumber=pPoNumber,
      apopen_docdate=pDocDate, apopen_duedate=pDueDate,
      apopen_distdate=pDocDate, apopen_terms_id=pTermsid,
      apopen_amount=pAmount, apopen_paid=0,
      apopen_open=(pAmount <> 0), apopen_notes=pNotes,
      apopen_accnt_id=_accntid, apopen_curr_id=pCurrId,
      apopen_closedate=CASE WHEN (pAmount = 0) THEN pDocdate END
    WHERE apopen_id = _apopenid;
  ELSE
    SELECT NEXTVAL('apopen_apopen_id_seq') INTO _apopenid;
    INSERT INTO apopen
    ( apopen_id, apopen_username, apopen_journalnumber,
      apopen_vend_id, apopen_docnumber, apopen_doctype, apopen_ponumber,
      apopen_docdate, apopen_duedate, apopen_distdate, apopen_terms_id,
      apopen_amount, apopen_paid, apopen_open, apopen_notes, apopen_accnt_id, apopen_curr_id,
      apopen_closedate )
    VALUES
    ( _apopenid, getEffectiveXtUser(), _journalNumber,
      pVendid, pDocNumber, 'C', pPoNumber,
      pDocDate, pDueDate, pDocDate, pTermsid,
      pAmount, 0, (pAmount <> 0), pNotes, _accntid, pCurrId,
      CASE WHEN (pAmount = 0) THEN pDocDate END );
  END IF;

  _baseAmount := round(currToBase(pCurrId, pAmount, pDocDate), 2);

  -- Debit the A/P account for the full amount
  SELECT insertIntoGLSeries ( _glSequence, 'A/P', 'CM',
                              pDocNumber, _apAccntid,
                              (_baseAmount * -1),
                              pDocDate, (_vendName || ' ' || pNotes) ) INTO _test;

  -- Credit the Tax account for the tax amount
  _taxBaseValue := addTaxToGLSeries(_glSequence,
				      'A/P', 'CM', pDocNumber,
				      pCurrId, pDocDate, pDocDate,
                                      'apopentax', _apopenid,
                                      _vendName);

  UPDATE apopentax SET taxhist_journalnumber = _journalNumber
  WHERE taxhist_parent_id=_apopenid;

  -- Credit the Prepaid account for the basis amount
  SELECT insertIntoGLSeries ( _glSequence, 'A/P', 'CM',
                              pDocNumber, _prepaidAccntid,
                              (_baseAmount - _taxBaseValue),
                              pDocDate, (_vendName || ' ' || pNotes) ) INTO _test;

  --  Commit the GLSeries;
  SELECT postGLSeries(_glSequence, _journalNumber) INTO _test;
  IF (_test < 0) THEN
    DELETE FROM apopen WHERE (apopen_id=_apopenid);
    PERFORM deleteGLSeries(_glSequence);
    RAISE EXCEPTION 'postGLSeries commit failed with %', _test;
  END IF;

  RETURN pJournalNumber;

END;
$_$;


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

--
-- TOC entry 1294 (class 1255 OID 146565845)
-- Dependencies: 4536 8
-- Name: createapcreditmemoapplication(integer, integer, numeric, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createapcreditmemoapplication(psourceapopenid integer, ptargetapopenid integer, pamount numeric, pcurrid 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
  _apCreditApplyId	INTEGER;

BEGIN
  IF (pAmount > (SELECT ROUND(currToCurr(apopen_curr_id, pCurrId, (apopen_amount - apopen_paid), apopen_docdate), 2)
                 FROM apopen
                 WHERE (apopen_id=pTargetApopenId))) THEN
    RETURN -1;
  END IF;

  IF (pAmount > (SELECT ROUND((apopen_amount - apopen_paid) - 
		       COALESCE(SUM(currToCurr(apcreditapply_curr_id,
						apopen_curr_id, 
						apcreditapply_amount, 
						apopen_docdate)), 0), 2)
             FROM apopen LEFT OUTER JOIN apcreditapply 
               ON ((apcreditapply_source_apopen_id=apopen_id) 
              AND (apcreditapply_target_apopen_id<>pTargetApopenId)) 
             WHERE (apopen_id=pSourceApopenId) 
             GROUP BY apopen_amount, apopen_paid)) THEN
      RETURN -2;
    END IF;

  SELECT apcreditapply_id INTO _apCreditApplyId
    FROM apcreditapply
   WHERE ((apcreditapply_source_apopen_id=pSourceApopenId)
     AND  (apcreditapply_target_apopen_id=pTargetApopenId));

  IF (FOUND) THEN
    UPDATE apcreditapply SET apcreditapply_amount=pAmount,
			     apcreditapply_curr_id=pCurrId
    WHERE (apcreditapply_id=_apCreditApplyId);
  ELSE
    INSERT INTO apcreditapply (
      apcreditapply_source_apopen_id,
      apcreditapply_target_apopen_id,
      apcreditapply_amount, apcreditapply_curr_id
    ) VALUES (
      pSourceApopenId,
      pTargetApopenId,
      pAmount, pCurrId)
    RETURNING apcreditapply_id INTO _apCreditApplyId;
  END IF;

  RETURN _apCreditApplyId;

END;
$$;


ALTER FUNCTION public.createapcreditmemoapplication(psourceapopenid integer, ptargetapopenid integer, pamount numeric, pcurrid integer) OWNER TO admin;

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

CREATE FUNCTION createapdebitmemo(integer, text, text, date, numeric, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pVendid ALIAS FOR $1;
  pDocNumber ALIAS FOR $2;
  pPoNumber ALIAS FOR $3;
  pDocDate ALIAS FOR $4;
  pAmount ALIAS FOR $5;
  pNotes ALIAS FOR $6;
  _result INTEGER;

BEGIN

  SELECT createAPDebitMemo( pVendid, fetchJournalNumber('AP-MISC'),
                            pDocNumber, pPoNumber, pDocDate, pAmount, pNotes, -1, pDocDate, -1, baseCurrId() ) INTO _result;

  RETURN _result;

END;
$_$;


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

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

CREATE FUNCTION createapdebitmemo(integer, integer, text, text, date, numeric, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pVendid ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  pDocNumber ALIAS FOR $3;
  pPoNumber ALIAS FOR $4;
  pDocDate ALIAS FOR $5;
  pAmount ALIAS FOR $6;
  pNotes ALIAS FOR $7;

BEGIN

  RETURN createAPDebitMemo(pVendid, pJournalNumber, pDocNumber, pPoNumber, pDocDate, pAmount, pNotes, -1, pDocDate, -1, baseCurrId() );

END;
$_$;


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

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

CREATE FUNCTION createapdebitmemo(integer, text, text, date, numeric, 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
  pVendid ALIAS FOR $1;
  pDocNumber ALIAS FOR $2;
  pPoNumber ALIAS FOR $3;
  pDocDate ALIAS FOR $4;
  pAmount ALIAS FOR $5;
  pNotes ALIAS FOR $6;
  pAccntid ALIAS FOR $7;
  _result INTEGER;

BEGIN

  SELECT createAPDebitMemo( pVendid, fetchJournalNumber('AP-MISC'),
                            pDocNumber, pPoNumber, pDocDate, pAmount, pNotes, pAccntid, pDocDate, -1, baseCurrId() ) INTO _result;

  RETURN _result;

END;
$_$;


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

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

CREATE FUNCTION createapdebitmemo(integer, integer, text, text, date, numeric, 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
  pVendid ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  pDocNumber ALIAS FOR $3;
  pPoNumber ALIAS FOR $4;
  pDocDate ALIAS FOR $5;
  pAmount ALIAS FOR $6;
  pNotes ALIAS FOR $7;
  pAccntid ALIAS FOR $8;
BEGIN
  RETURN createAPDebitMemo( pVendid, pJournalNumber,
                            pDocNumber, pPoNumber, pDocDate, pAmount, pNotes, pAccntid, pDocDate, -1, baseCurrId() );
END;
$_$;


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

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

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

BEGIN

  SELECT createAPDebitMemo( pVendid, fetchJournalNumber('AP-MISC'),
                            pDocNumber, pPoNumber, pDocDate, pAmount, pNotes, pAccntid, pDueDate, pTermsid, baseCurrId() ) INTO _result;

  RETURN _result;

END;
$_$;


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

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

CREATE FUNCTION createapdebitmemo(integer, integer, text, text, date, numeric, text, integer, date, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pVendid ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  pDocNumber ALIAS FOR $3;
  pPoNumber ALIAS FOR $4;
  pDocDate ALIAS FOR $5;
  pAmount ALIAS FOR $6;
  pNotes ALIAS FOR $7;
  pAccntid ALIAS FOR $8;
  pDueDate ALIAS FOR $9;
  pTermsid ALIAS FOR $10;

BEGIN
  RETURN createAPDebitMemo(pVendid, pJournalNumber, pDocNumber, pPoNumber, pDocDate, pAmount, pNotes, pAccntid, pDueDate, pTermsid, baseCurrId() );
END;
$_$;


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

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

CREATE FUNCTION createapdebitmemo(integer, integer, text, text, date, numeric, text, integer, date, 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
  pVendid ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  pDocNumber ALIAS FOR $3;
  pPoNumber ALIAS FOR $4;
  pDocDate ALIAS FOR $5;
  pAmount ALIAS FOR $6;
  pNotes ALIAS FOR $7;
  pAccntid ALIAS FOR $8;
  pDueDate ALIAS FOR $9;
  pTermsid ALIAS FOR $10;
  pCurrId ALIAS FOR $11;

BEGIN
  RETURN createAPDebitMemo(NULL, pVendid, pJournalNumber, pDocNumber, pPoNumber, pDocDate, pAmount, pNotes, pAccntid, pDueDate, pTermsid, pCurrId );
END;
$_$;


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

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

CREATE FUNCTION createapdebitmemo(integer, integer, integer, text, text, date, numeric, text, integer, date, 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
  pId ALIAS FOR $1;
  pVendid ALIAS FOR $2;
  pJournalNumber ALIAS FOR $3;
  pDocNumber ALIAS FOR $4;
  pPoNumber ALIAS FOR $5;
  pDocDate ALIAS FOR $6;
  pAmount ALIAS FOR $7;
  pNotes ALIAS FOR $8;
  pAccntid ALIAS FOR $9;
  pDueDate ALIAS FOR $10;
  pTermsid ALIAS FOR $11;
  pCurrId ALIAS FOR $12;
  _vendName TEXT;
  _apAccntid INTEGER;
  _prepaidAccntid INTEGER;
  _accntid INTEGER;
  _glSequence INTEGER;
  _journalNumber INTEGER;
  _apopenid INTEGER;
  _baseAmount NUMERIC;
  _taxBaseValue NUMERIC;
  _test INTEGER;

BEGIN

  _apopenid := pId;

  SELECT findAPAccount(pVendid) INTO _apAccntid;
  SELECT findAPPrepaidAccount(pVendid) INTO _prepaidAccntid;

  SELECT vend_name INTO _vendName
  FROM vendinfo
  WHERE (vend_id=pVendid);

  _accntid := pAccntid;

  PERFORM accnt_id
     FROM accnt
    WHERE (accnt_id=_accntid);
  IF (FOUND) THEN
    _prepaidAccntid := _accntid;
  ELSE
    _accntid := -1;
  END IF;

  IF(pJournalNumber IS NULL) THEN
    SELECT fetchJournalNumber('AP-MISC') INTO _journalNumber;
  ELSE
    _journalNumber := pJournalNumber;
  END IF;

  SELECT fetchGLSequence() INTO _glSequence;

  IF (_apopenid IS NOT NULL) THEN
    UPDATE apopen SET
      apopen_username=getEffectiveXtUser(), apopen_journalnumber=_journalNumber,
      apopen_vend_id=pVendid, apopen_docnumber=pDocNumber,
      apopen_doctype='D', apopen_ponumber=pPoNumber,
      apopen_docdate=pDocDate, apopen_duedate=pDueDate,
      apopen_distdate=pDocDate, apopen_terms_id=pTermsid,
      apopen_amount=pAmount, apopen_paid=0,
      apopen_open=(pAmount <> 0), apopen_notes=pNotes,
      apopen_accnt_id=_accntid, apopen_curr_id=pCurrId,
      apopen_closedate=CASE WHEN (pAmount = 0) THEN pDocdate END
    WHERE apopen_id = _apopenid;
  ELSE
    SELECT NEXTVAL('apopen_apopen_id_seq') INTO _apopenid;
    INSERT INTO apopen
    ( apopen_id, apopen_username, apopen_journalnumber,
      apopen_vend_id, apopen_docnumber, apopen_doctype, apopen_ponumber,
      apopen_docdate, apopen_duedate, apopen_distdate, apopen_terms_id,
      apopen_amount, apopen_paid, apopen_discountable_amount, apopen_open, apopen_notes, apopen_accnt_id, apopen_curr_id,
      apopen_closedate )
    VALUES
    ( _apopenid, getEffectiveXtUser(), _journalNumber,
      pVendid, pDocNumber, 'D', pPoNumber,
      pDocDate, pDueDate, pDocDate, pTermsid,
      pAmount, 0, 0, (pAmount <> 0), pNotes, _accntid, pCurrId,
      CASE WHEN (pAmount = 0) THEN pDocDate END );
  END IF;

  _baseAmount := round(currToBase(pCurrId, pAmount, pDocDate), 2);

  -- Credit the A/P account for the full amount
  SELECT insertIntoGLSeries ( _glSequence, 'A/P', 'DM',
                              pDocNumber, _apAccntid,
                              _baseAmount,
                              pDocDate, (_vendName || ' ' || pNotes) ) INTO _test;

  -- Debit the Tax account for the tax amount
  _taxBaseValue := addTaxToGLSeries(_glSequence,
				      'A/P', 'DM', pDocNumber,
				      pCurrId, pDocDate, pDocDate,
                                      'apopentax', _apopenid,
                                      _vendName);

  UPDATE apopentax SET taxhist_journalnumber = _journalNumber
  WHERE taxhist_parent_id=_apopenid;

  -- Debit the Prepaid account for the basis amount
  -- Note, the taxBaseValue is negative so it is added
  SELECT insertIntoGLSeries ( _glSequence, 'A/P', 'DM',
                              pDocNumber, _prepaidAccntid,
                              (_baseAmount + _taxBaseValue) * -1,
                              pDocDate, (_vendName || ' ' || pNotes) ) INTO _test;

  --  Commit the GLSeries;
  SELECT postGLSeries(_glSequence, _journalNumber) INTO _test;
  IF (_test < 0) THEN
    DELETE FROM apopen WHERE (apopen_id=_apopenid);
    PERFORM deleteGLSeries(_glSequence);
    RAISE EXCEPTION 'postGLSeries commit failed with %', _test;
  END IF;

  RETURN _apopenid;

END;
$_$;


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

--
-- TOC entry 1303 (class 1255 OID 146565854)
-- Dependencies: 4536 8
-- Name: createapdiscount(integer, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createapdiscount(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
  pApopenid ALIAS FOR $1;
  pAmount ALIAS FOR $2;
  _result INTEGER;
  
BEGIN

  SELECT createAPDiscount(pApopenid, fetchJournalNumber('AP-MISC'), pAmount) INTO _result;

  RETURN _result;

END;
$_$;


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

--
-- TOC entry 1304 (class 1255 OID 146565855)
-- Dependencies: 4536 8
-- Name: createapdiscount(integer, integer, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createapdiscount(integer, integer, numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pApopenid ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  pAmount ALIAS FOR $3;
  _ap RECORD;
  _sequence INTEGER;
  _apopenid INTEGER;
  _apcreditapplyid INTEGER;
  _result INTEGER;
  _crAccnt INTEGER;
  _dbAccnt INTEGER;
  _reference    TEXT;
  _discountDateAmt NUMERIC;

BEGIN

  SELECT NEXTVAL('apopen_apopen_id_seq') INTO _apopenid;

  SELECT * INTO _ap
  FROM apopen
  WHERE (apopen_id = pApopenid);
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  _crAccnt := findAPDiscountAccount(_ap.apopen_vend_id);
  _dbAccnt := findAPAccount(_ap.apopen_vend_id);
  _reference := ('Discount for ' || _ap.apopen_doctype || ' ' || _ap.apopen_docnumber);

  SELECT fetchGLSequence() INTO _sequence;

  _discountDateAmt = round(pAmount / _ap.apopen_curr_rate, 2);
  PERFORM insertIntoGLSeries( _sequence, 'A/P', 'DS', _ap.apopen_docnumber,
                              _dbAccnt,
                              _discountDateAmt * -1,
                              CURRENT_DATE,
                              _reference);
  PERFORM insertIntoGLSeries( _sequence, 'A/P', 'DS', _ap.apopen_docnumber,
                              _crAccnt,
                              _discountDateAmt,
                              CURRENT_DATE,
                              _reference);

  PERFORM postGLSeries(_sequence, pJournalNumber);

  INSERT INTO apopen
  ( apopen_id, apopen_username, apopen_journalnumber,
    apopen_vend_id, apopen_docnumber, apopen_doctype, apopen_ponumber,
    apopen_docdate, apopen_duedate, apopen_distdate, apopen_terms_id, apopen_curr_id,
    apopen_amount, apopen_paid, apopen_open, apopen_notes, apopen_discount, apopen_curr_rate )
  SELECT _apopenid, getEffectiveXtUser(), pJournalNumber,
         apopen_vend_id, apopen_docnumber, 'C', apopen_ponumber,
         CURRENT_DATE, CURRENT_DATE, CURRENT_DATE, -1, apopen_curr_id,
         pAmount, 0, TRUE, _reference, TRUE, apopen_curr_rate
    FROM apopen
   WHERE (apopen_id=pApopenid);

  SELECT apcreditapply_id INTO _apcreditapplyid
    FROM apcreditapply
   WHERE ( (apcreditapply_source_apopen_id=_apopenid)
     AND   (apcreditapply_target_apopen_id=pApopenid) );
  IF (FOUND) THEN
    UPDATE apcreditapply
       SET apcreditapply_amount=pAmount
     WHERE (apcreditapply_id=_apcreditapplyid);
  ELSE
    SELECT nextval('apcreditapply_apcreditapply_id_seq') INTO _apcreditapplyid;
    INSERT INTO apcreditapply
           ( apcreditapply_id, apcreditapply_source_apopen_id,
             apcreditapply_target_apopen_id, apcreditapply_amount,
             apcreditapply_curr_id )
    VALUES ( _apcreditapplyid, _apopenid, pApopenid, pAmount, _ap.apopen_curr_id );
  END IF;

  SELECT postAPCreditMemoApplication(_apopenid) INTO _result;

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

  RETURN pJournalNumber;

END;
$_$;


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

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

CREATE FUNCTION createarcashdeposit(integer, text, text, date, numeric, text, integer, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCustid ALIAS FOR $1;
  pDocNumber ALIAS FOR $2;
  pOrderNumber ALIAS FOR $3;
  pDocDate ALIAS FOR $4;
  pAmount ALIAS FOR $5;
  pNotes ALIAS FOR $6;
  pJournalNumber ALIAS FOR $7;
  pCurrId ALIAS FOR $8;
  _prepaidaccntid INTEGER;
  _deferredaccntid INTEGER;
  _glSequence INTEGER;
  _aropenid INTEGER;

BEGIN

  IF (pAmount <= 0) THEN
    RETURN 0;
  END IF;

  _prepaidaccntid := findPrepaidAccount(pCustid);
  IF (_prepaidaccntid = -1) THEN
    RAISE EXCEPTION 'There was an error creating the Customer Deposit GL Transactions. No Prepaid Account is assigned.';
  END IF;

  _deferredaccntid := findDeferredAccount(pCustid);
  IF (_deferredaccntid = -1) THEN
    RAISE EXCEPTION 'There was an error creating the Customer Deposit GL Transactions. No Deferred Account is assigned.';
  END IF;

  SELECT NEXTVAL('aropen_aropen_id_seq') INTO _aropenid;

  SELECT insertGLTransaction( pJournalNumber, 'A/R', 'CD',
                              pDocNumber, pNotes, _deferredaccntid, _prepaidaccntid,
                              _aropenid,
                              round(currToBase(pCurrId, pAmount, pDocDate), 2),
                              pDocDate) INTO _glSequence;

  INSERT INTO aropen
  ( aropen_id, aropen_username, aropen_journalnumber,
    aropen_cust_id, aropen_docnumber, aropen_doctype, aropen_ordernumber,
    aropen_docdate, aropen_duedate, aropen_distdate, aropen_terms_id, aropen_salesrep_id,
    aropen_amount, aropen_paid, aropen_commission_due, aropen_commission_paid,
    aropen_applyto, aropen_ponumber, aropen_cobmisc_id,
    aropen_open, aropen_notes, aropen_rsncode_id,
    aropen_salescat_id, aropen_accnt_id, aropen_curr_id )
  VALUES
  ( _aropenid, getEffectiveXtUser(), pJournalNumber,
    pCustid, pDocNumber, 'R', pOrderNumber,
    pDocDate, pDocDate, pDocDate, -1, NULL,
    round(pAmount, 2), 0, 0.0, FALSE,
    '', '', -1,
    TRUE, pNotes, -1,
    -1, -1, pCurrId );

  RETURN _aropenid;

END;
$_$;


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

--
-- TOC entry 1307 (class 1255 OID 146565857)
-- Dependencies: 907 4536 8
-- Name: createarcreditmemo(integer, integer, text, text, date, numeric, text, integer, integer, integer, date, integer, integer, numeric, integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createarcreditmemo(pid integer, pcustid integer, pdocnumber text, pordernumber text, pdocdate date, pamount numeric, pnotes text, prsncodeid integer, psalescatid integer, paccntid integer, pduedate date, ptermsid integer, psalesrepid integer, pcommissiondue numeric DEFAULT 0, pjournalnumber integer DEFAULT NULL::integer, pcurrid integer DEFAULT basecurrid(), paraccntid integer DEFAULT NULL::integer, pcoccpayid 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
  _accntid        INTEGER;
  _arAccntid      INTEGER;
  _aropenid       INTEGER;
  _cohistid       INTEGER;
  _custName       TEXT;
  _duedate        DATE    := COALESCE(pDueDate, pDocDate);
  _glSequence     INTEGER;
  _journalNumber  INTEGER;
  _prepaidAccntid INTEGER;
  _salescatid     INTEGER;
  _taxBaseValue   NUMERIC;
  _test           INTEGER;
  _tmp            INTEGER;

BEGIN

  _aropenid := pId;

  IF (pAmount <= 0) THEN
    RETURN 0;
  END IF;

  _arAccntid := COALESCE(pARAccntid, findARAccount(pCustid));
  _prepaidAccntid := findPrepaidAccount(pCustid);

  _accntid := pAccntid;
  _salescatid := pSalescatid;

  SELECT cust_name INTO _custName
  FROM custinfo
  WHERE (cust_id=pCustid);

  IF EXISTS(SELECT 1 FROM accnt WHERE (accnt_id=_accntid)) THEN
    _prepaidAccntid := _accntid;
  ELSE
    _accntid := -1;
  END IF;

  SELECT accnt_id INTO _tmp
    FROM salescat, accnt
   WHERE ((salescat_prepaid_accnt_id=accnt_id)
     AND  (salescat_id=_salescatid));
  IF (FOUND) THEN
    _accntid := -1;
    _prepaidAccntid := _tmp;
  ELSE
    _salescatid = -1;
  END IF;

  IF(pJournalNumber IS NULL) THEN
    SELECT fetchJournalNumber('AR-MISC') INTO _journalNumber;
  ELSE
    _journalNumber := pJournalNumber;
  END IF;

  _glSequence := fetchGLSequence();

  -- CreatelUpdate aropen for full amount
  IF (_aropenid IS NOT NULL) THEN
    UPDATE aropen SET
      aropen_username=getEffectiveXtUser(), aropen_journalnumber=_journalNumber,
      aropen_cust_id=pCustid, aropen_docnumber=pDocNumber, aropen_doctype='C',
      aropen_ordernumber=pOrderNumber,aropen_docdate=pDocDate, aropen_duedate=_duedate,
      aropen_distdate=pDocDate, aropen_terms_id=pTermsid,
      aropen_salesrep_id=pSalesrepid, aropen_amount=round(pAmount, 2), aropen_paid=0,
      aropen_commission_due=pCommissiondue, aropen_commission_paid=FALSE,
      aropen_applyto='', aropen_ponumber='', aropen_cobmisc_id=-1,
      aropen_open=TRUE, aropen_notes=pNotes, aropen_rsncode_id=pRsncodeid,
      aropen_salescat_id=_salescatid, aropen_accnt_id=_accntid, aropen_curr_id=pCurrId
    WHERE aropen_id = pId;
  ELSE
    SELECT NEXTVAL('aropen_aropen_id_seq') INTO _aropenid;
    INSERT INTO aropen
    ( aropen_id, aropen_username, aropen_journalnumber,
      aropen_cust_id, aropen_docnumber, aropen_doctype, aropen_ordernumber,
      aropen_docdate, aropen_duedate, aropen_distdate, aropen_terms_id, aropen_salesrep_id,
      aropen_amount, aropen_paid, aropen_commission_due, aropen_commission_paid,
      aropen_applyto, aropen_ponumber, aropen_cobmisc_id,
      aropen_open, aropen_notes, aropen_rsncode_id,
      aropen_salescat_id, aropen_accnt_id, aropen_curr_id )
    VALUES
    ( _aropenid, getEffectiveXtUser(), _journalNumber,
      pCustid, pDocNumber, 'C', pOrderNumber,
      pDocDate, _duedate, pDocDate, pTermsid, pSalesrepid,
      round(pAmount, 2), 0, pCommissiondue, FALSE,
      '', '', -1,
      TRUE, pNotes, pRsncodeid,
      _salescatid, _accntid, pCurrId );
  END IF;

  -- Credit the A/R account for the full amount
  SELECT insertIntoGLSeries ( _glSequence, 'A/R', 'CM',
                              pDocNumber, _arAccntid,
                              round(currToBase(pCurrId, pAmount, pDocDate), 2),
                              pDocDate, (_custName || ' ' || pNotes)) INTO _test;

  -- Debit the Tax account for the tax amount
  _taxBaseValue := addTaxToGLSeries(_glSequence,
				      'A/R', 'CM', pDocNumber,
				      pCurrId, pDocDate, pDocDate,
                                      'aropentax', _aropenid,
                                      (_custName || ' ' || pNotes));

  UPDATE aropentax SET taxhist_journalnumber = _journalNumber
  WHERE taxhist_parent_id=_aropenid;

  -- Debit the Prepaid account for the basis amount
  -- Note, _taxBaseValue is negative so it is added to pAmount
  SELECT insertIntoGLSeries ( _glSequence, 'A/R', 'CM',
                              pDocNumber, _prepaidAccntid,
                              round(currToBase(pCurrId, pAmount * -1, pDocDate) + _taxBaseValue * -1, 2),
                              pDocDate, (_custName || ' ' || pNotes)) INTO _test;

  --  Commit the GLSeries;
  SELECT postGLSeries(_glSequence, _journalNumber) INTO _test;
  IF (_test < 0) THEN
    DELETE FROM aropen WHERE (aropen_id=_aropenid);
    PERFORM deleteGLSeries(_glSequence);
    RAISE EXCEPTION 'postGLSeries commit failed with %', _test;
  END IF;

  --  Record Sales History
  INSERT INTO cohist
  ( cohist_cust_id,
   cohist_itemsite_id, cohist_shipto_id,
    cohist_misc_type, cohist_misc_descrip,
    cohist_shipdate, cohist_shipvia,
    cohist_ordernumber, cohist_ponumber, cohist_orderdate,
    cohist_doctype, cohist_invcnumber, cohist_invcdate,
    cohist_qtyshipped, cohist_unitprice, cohist_unitcost,
    cohist_salesrep_id,
    cohist_commission, cohist_commissionpaid,
    cohist_curr_id, cohist_sequence, cohist_cohead_ccpay_id)
  VALUES
  (CASE WHEN pCustid < 0 THEN NULL ELSE pCustid END,
   -1, -1,
    'M', 'A/R Misc Credit Memo',
    pDocDate, '',
    pOrderNumber, '', pDocDate,
    'C', pDocNumber, pDocDate,
    1, (pAmount + _taxBaseValue) * -1, 0, -- taxBaseValue is negative, so we add
    CASE WHEN pSalesrepid < 0 THEN NULL ELSE pSalesrepid END,
    (pCommissiondue * -1.0), FALSE,
    pCurrId, _glSequence, pCoCcpayId)
  RETURNING cohist_id INTO _cohistid;

  INSERT INTO cohisttax
  ( taxhist_parent_id, taxhist_taxtype_id, taxhist_tax_id,
    taxhist_basis, taxhist_basis_tax_id, taxhist_sequence,
    taxhist_percent, taxhist_amount, taxhist_tax,
    taxhist_docdate, taxhist_distdate, taxhist_curr_id, taxhist_curr_rate,
    taxhist_journalnumber )
  SELECT _cohistid, taxhist_taxtype_id, taxhist_tax_id,
         taxhist_basis, taxhist_basis_tax_id, taxhist_sequence,
         taxhist_percent, taxhist_amount, taxhist_tax,
         taxhist_docdate, taxhist_distdate, taxhist_curr_id, taxhist_curr_rate,
         taxhist_journalnumber
  FROM aropentax
  WHERE (taxhist_parent_id=_aropenid);

  RETURN _aropenid;

END;
$$;


ALTER FUNCTION public.createarcreditmemo(pid integer, pcustid integer, pdocnumber text, pordernumber text, pdocdate date, pamount numeric, pnotes text, prsncodeid integer, psalescatid integer, paccntid integer, pduedate date, ptermsid integer, psalesrepid integer, pcommissiondue numeric, pjournalnumber integer, pcurrid integer, paraccntid integer, pcoccpayid integer) OWNER TO admin;

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

CREATE FUNCTION createardebitmemo(integer, integer, integer, text, text, date, numeric, text, integer, integer, integer, date, integer, 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
  pId			ALIAS FOR $1;
  pCustid		ALIAS FOR $2;
  pJournalNumber	ALIAS FOR $3;
  pDocNumber		ALIAS FOR $4;
  pOrderNumber		ALIAS FOR $5;
  pDocDate		ALIAS FOR $6;
  pAmount		ALIAS FOR $7;
  pNotes		ALIAS FOR $8;
  pRsncodeid		ALIAS FOR $9;
  pSalescatid		ALIAS FOR $10;
  pAccntid		ALIAS FOR $11;
  pDueDate		ALIAS FOR $12;
  pTermsid		ALIAS FOR $13;
  pSalesrepid		ALIAS FOR $14;
  pCommissiondue	ALIAS FOR $15;
  pCurrId		ALIAS FOR $16;
  _custName TEXT;
  _journalNumber INTEGER;
  _arAccntid INTEGER;
  _prepaidAccntid INTEGER;
  _salescatid INTEGER;
  _accntid INTEGER;
  _glSequence INTEGER;
  _aropenid INTEGER;
  _cohistid INTEGER;
  _tmp INTEGER;
  _test INTEGER;
  _taxBaseValue NUMERIC;

BEGIN
  _aropenid=pId;
  
  IF (pAmount <= 0) THEN
    RETURN 0;
  END IF;

  SELECT findARAccount(pCustid) INTO _arAccntid;
  SELECT findPrepaidAccount(pCustid) INTO _prepaidAccntid;

  _accntid := pAccntid;
  _salescatid := pSalescatid;

  SELECT cust_name INTO _custName
  FROM custinfo
  WHERE (cust_id=pCustid);
  
  PERFORM accnt_id
     FROM accnt
    WHERE (accnt_id=_accntid);
  IF (FOUND) THEN
    _prepaidAccntid := _accntid;
  ELSE
    _accntid := -1;
  END IF;

  SELECT accnt_id INTO _tmp
    FROM salescat, accnt
   WHERE ((salescat_prepaid_accnt_id=accnt_id)
     AND  (salescat_id=_salescatid));
  IF (FOUND) THEN
    _accntid := -1;
    _prepaidAccntid := _tmp;
  ELSE
    _salescatid = -1;
  END IF;

  IF (pJournalNumber IS NULL) THEN
    _journalNumber := fetchJournalNumber('AR-MISC');
  ELSE
    _journalNumber := pJournalNumber;
  END IF;

  SELECT fetchGLSequence() INTO _glSequence;

  -- CreatelUpdate aropen for full amount
  IF (_aropenid IS NOT NULL) THEN
    UPDATE aropen SET
      aropen_username=getEffectiveXtUser(), aropen_journalnumber=_journalNumber,
      aropen_cust_id=pCustid, aropen_docnumber=pDocNumber, aropen_doctype='D', 
      aropen_ordernumber=pOrderNumber,aropen_docdate=pDocDate, aropen_duedate=pDueDate, 
      aropen_distdate=pDocDate, aropen_terms_id=pTermsid, 
      aropen_salesrep_id=pSalesrepid, aropen_amount=round(pAmount, 2), aropen_paid=0, 
      aropen_commission_due=pCommissiondue, aropen_commission_paid=FALSE,
      aropen_applyto='', aropen_ponumber='', aropen_cobmisc_id=-1,
      aropen_open=TRUE, aropen_notes=pNotes, aropen_rsncode_id=pRsncodeid,
      aropen_salescat_id=_salescatid, aropen_accnt_id=_accntid, aropen_curr_id=pCurrId
    WHERE aropen_id = pId;
  ELSE
    SELECT NEXTVAL('aropen_aropen_id_seq') INTO _aropenid;
    INSERT INTO aropen
    ( aropen_id, aropen_username, aropen_journalnumber,
      aropen_cust_id, aropen_docnumber, aropen_doctype, aropen_ordernumber,
      aropen_docdate, aropen_duedate, aropen_distdate, aropen_terms_id, aropen_salesrep_id,
      aropen_amount, aropen_paid, aropen_commission_due, aropen_commission_paid,
      aropen_applyto, aropen_ponumber, aropen_cobmisc_id,
      aropen_open, aropen_notes, aropen_rsncode_id,
      aropen_salescat_id, aropen_accnt_id, aropen_curr_id )
    VALUES
    ( _aropenid, getEffectiveXtUser(), _journalNumber,
      pCustid, pDocNumber, 'D', pOrderNumber,
      pDocDate, pDueDate, pDocDate, pTermsid, pSalesrepid,
      round(pAmount, 2), 0, pCommissiondue, FALSE,
      '', '', -1,
      TRUE, pNotes, pRsncodeid,
      _salescatid, _accntid, pCurrId );
  END IF;

  -- Debit the A/R account for the full amount
  SELECT insertIntoGLSeries ( _glSequence, 'A/R', 'DM',
                              pDocNumber, _arAccntid,
                              round(currToBase(pCurrId, pAmount, pDocDate) * -1, 2),
                              pDocDate, (_custName || ' ' || pNotes)) INTO _test;

  -- Credit the Tax account for the tax amount
  _taxBaseValue := addTaxToGLSeries(_glSequence,
				      'A/R', 'DM', pDocNumber,
				      pCurrId, pDocDate, pDocDate,
                                      'aropentax', _aropenid,
                                      (_custName || ' ' || pNotes));

  UPDATE aropentax SET taxhist_journalnumber = _journalNumber
  WHERE taxhist_parent_id=_aropenid;

  -- Credit the Prepaid account for the basis amount
  SELECT insertIntoGLSeries ( _glSequence, 'A/R', 'DM',
                              pDocNumber, _prepaidAccntid,
                              round(currToBase(pCurrId, (pAmount), pDocDate), 2) - _taxBaseValue,
                              pDocDate, (_custName || ' ' || pNotes)) INTO _test;

  --  Commit the GLSeries;
  SELECT postGLSeries(_glSequence, _journalNumber) INTO _test;
  IF (_test < 0) THEN
    DELETE FROM aropen WHERE (aropen_id=_aropenid);
    PERFORM deleteGLSeries(_glSequence);
    RAISE EXCEPTION 'postGLSeries commit failed with %', _test;
  END IF;

  --  Record Sales History
  SELECT nextval('cohist_cohist_id_seq') INTO _cohistid;
  INSERT INTO cohist
  ( cohist_id, cohist_cust_id, cohist_itemsite_id, cohist_shipto_id,
    cohist_misc_type, cohist_misc_descrip,
    cohist_shipdate, cohist_shipvia,
    cohist_ordernumber, cohist_ponumber, cohist_orderdate,
    cohist_doctype, cohist_invcnumber, cohist_invcdate,
    cohist_qtyshipped, cohist_unitprice, cohist_unitcost,
    cohist_salesrep_id, cohist_commission, cohist_commissionpaid,
    cohist_curr_id, cohist_sequence )
  VALUES
  ( _cohistid, pCustid, -1, -1,
    'M', 'A/R Misc Debit Memo',
    pDocDate, '',
    '', '', pDocDate,
    'D', pDocNumber, pDocDate,
    1, (pAmount - _taxBaseValue), 0,
    pSalesrepid, pCommissiondue, FALSE,
    pCurrId, _glSequence );
  INSERT INTO cohisttax
  ( taxhist_parent_id, taxhist_taxtype_id, taxhist_tax_id,
    taxhist_basis, taxhist_basis_tax_id, taxhist_sequence,
    taxhist_percent, taxhist_amount, taxhist_tax,
    taxhist_docdate, taxhist_distdate, taxhist_curr_id, taxhist_curr_rate,
    taxhist_journalnumber )
  SELECT _cohistid, taxhist_taxtype_id, taxhist_tax_id,
         taxhist_basis, taxhist_basis_tax_id, taxhist_sequence,
         taxhist_percent, taxhist_amount, taxhist_tax,
         taxhist_docdate, taxhist_distdate, taxhist_curr_id, taxhist_curr_rate,
         taxhist_journalnumber
  FROM aropentax
  WHERE (taxhist_parent_id=_aropenid);

  RETURN _aropenid;

END;
$_$;


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

--
-- TOC entry 1309 (class 1255 OID 146565861)
-- Dependencies: 4536 8
-- Name: createbillingheader(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createbillingheader(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSoheadid		ALIAS FOR $1;
  _cobmiscid		INTEGER;
  _cohead		cohead%ROWTYPE;
  _miscApplied          NUMERIC := 0.0;
  _freight		NUMERIC;
  _freighttypeid        INTEGER;
  _invcDate		DATE;
  _schedDate		DATE;
  _shipDate		DATE;
  _shipVia		TEXT;
  _tax			NUMERIC;

BEGIN

  --  Fetch cohead
  SELECT * INTO _cohead
  FROM cohead
  WHERE (cohead_id=pSoheadid);

  --  Check for an existing cobmisc
  SELECT cobmisc_id INTO _cobmiscid
  FROM cobmisc
  WHERE ( (NOT cobmisc_posted)
   AND (cobmisc_cohead_id=pSoheadid) );

  IF (FOUND) THEN
  --  Find a Shipping-Entered freight charge
    SELECT SUM(currToCurr(shiphead_freight_curr_id, _cohead.cohead_curr_id,
                          shiphead_freight, CURRENT_DATE)) INTO _freight
    FROM (
    SELECT shiphead_id, shiphead_freight_curr_id, shiphead_freight
    FROM shiphead JOIN shipitem ON (shipitem_shiphead_id=shiphead_id AND NOT shipitem_invoiced)
    WHERE ((shiphead_order_type='SO')
      AND  (shiphead_order_id=pSoheadid))
    GROUP BY shiphead_id, shiphead_freight_curr_id, shiphead_freight) AS data;

    IF (_freight IS NOT NULL) THEN
      UPDATE cobmisc SET cobmisc_freight = _freight
      WHERE (cobmisc_id=_cobmiscid);
    END IF;

    RETURN _cobmiscid;
  END IF;

  --  Find misc charges that have already been applied for the S/O
  SELECT COALESCE(SUM(cobmisc_misc), 0.0) INTO _miscApplied
  FROM cobmisc
  WHERE (cobmisc_cohead_id=pSoheadid);

  SELECT NEXTVAL('cobmisc_cobmisc_id_seq') INTO _cobmiscid;

  --  Check for a valid shipdate
  SELECT MIN(shiphead_shipdate) INTO _shipDate
  FROM shiphead, shipitem
  WHERE ( (shipitem_shiphead_id=shiphead_id)
   AND (NOT shipitem_invoiced)
   AND (shiphead_shipped)
   AND (shiphead_order_type='SO')
   AND (shiphead_order_id=pSoheadid) );

  --  Schema shouldn't allow, but we'll try for now
  IF (_shipDate IS NULL) THEN
    SELECT MAX(shipitem_shipdate) INTO _shipDate
    FROM shipitem, shiphead
    WHERE ( (shipitem_shiphead_id=shiphead_id)
     AND (shiphead_order_type='SO')
     AND (shiphead_order_id=pSoheadid) );

    --  How about a transaction date
    IF (_shipDate IS NULL) THEN
      SELECT COALESCE(MAX(shipitem_transdate), CURRENT_DATE) INTO _shipDate
      FROM shipitem, shiphead
      WHERE ((shipitem_shiphead_id=shiphead_id)
        AND  (shiphead_order_type='SO')
        AND  (shiphead_order_id=pSoheadid) );
    END IF;
  END IF;

  --  Get the earliest schedule date for this order.
  SELECT MIN(coitem_scheddate) INTO _schedDate
    FROM coitem
   WHERE ((coitem_status <> 'X') AND (coitem_cohead_id=pSoheadid));

  IF (_schedDate IS NULL) THEN
    _schedDate := _shipDate;
  END IF;

  --  Find a Shipping-Entered freight charge
  SELECT SUM(currToCurr(shiphead_freight_curr_id, _cohead.cohead_curr_id,
                        shiphead_freight, CURRENT_DATE)), shiphead_shipvia
         INTO _freight, _shipVia
  FROM (
  SELECT shiphead_id, shiphead_freight_curr_id, shiphead_freight, shiphead_shipvia
  FROM shiphead JOIN shipitem ON (shipitem_shiphead_id=shiphead_id AND NOT shipitem_invoiced)
  WHERE ((shiphead_order_type='SO')
    AND  (shiphead_order_id=pSoheadid))
  GROUP BY shiphead_id, shiphead_freight_curr_id, shiphead_freight, shiphead_shipvia) AS data
  GROUP BY shiphead_shipvia;

  --  Nope, use the cohead freight charge
  IF (_freight IS NULL) THEN
    _freight	   := _cohead.cohead_freight;
  END IF;

  --  Finally, look for a Shipping-Entered Ship Via
  SELECT shiphead_shipvia INTO _shipVia
  FROM shiphead, shipitem
  WHERE ( (shipitem_shiphead_id=shiphead_id)
   AND (NOT shipitem_invoiced)
   AND (shiphead_order_type='SO')
   AND (shiphead_order_id=pSoheadid) )
  LIMIT 1;
  IF (NOT FOUND) THEN
    _shipVia := _cohead.cohead_shipvia;
  END IF;

  --Determine any tax

  SELECT 
  getFreightTaxTypeId() INTO _freighttypeid;
  SELECT SUM(COALESCE(taxdetail_tax, 0.00)) INTO _tax
  FROM calculatetaxdetail(_cohead.cohead_taxzone_id, _freighttypeid, _cohead.cohead_orderdate,_cohead.cohead_curr_id, _freight);

  --  Determine if we are using the _shipDate or _schedDate or current_date for the _invcDate
  IF( fetchMetricText('InvoiceDateSource')='scheddate') THEN
    _invcDate := _schedDate;
  ELSIF( fetchMetricText('InvoiceDateSource')='shipdate') THEN
    _invcDate := _shipDate;
  ELSE
    _invcDate := current_date;
  END IF;

   INSERT INTO cobmisc (
	cobmisc_id, cobmisc_cohead_id, cobmisc_shipvia, cobmisc_freight, cobmisc_misc, cobmisc_payment 
	,cobmisc_notes,cobmisc_shipdate ,cobmisc_invcdate,cobmisc_posted ,cobmisc_misc_accnt_id 
	,cobmisc_misc_descrip,cobmisc_closeorder,cobmisc_curr_id
	,cobmisc_taxtype_id,cobmisc_taxzone_id
	)
	SELECT
	_cobmiscid,_cohead.cohead_id,_shipVia,_freight,
        CASE WHEN (_cohead.cohead_misc - _miscApplied = 0.0) THEN 0.0
             ELSE (_cohead.cohead_misc - _miscApplied) END,0,
        _cohead.cohead_ordercomments,_shipDate,_invcDate,FALSE,_cohead.cohead_misc_accnt_id,
        _cohead.cohead_misc_descrip,NOT(cust_backorder),_cohead.cohead_curr_id,
	_cohead.cohead_taxtype_id,_cohead.cohead_taxzone_id
	FROM custinfo
	WHERE (cust_id=_cohead.cohead_cust_id);

  RETURN _cobmiscid;

END;
$_$;


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

--
-- TOC entry 1310 (class 1255 OID 146565863)
-- Dependencies: 4536 8
-- Name: createbomitem(integer, integer, integer, integer, character, integer, numeric, numeric, date, date, boolean, integer, boolean, text, character, integer, integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createbomitem(integer, integer, integer, integer, character, integer, numeric, numeric, date, date, boolean, integer, boolean, text, character, integer, integer, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pBomitemid ALIAS FOR $1;
  pParentItemid ALIAS FOR $2;
  pComponentItemid ALIAS FOR $3;
  pSeqNumber ALIAS FOR $4;
  pIssueMethod ALIAS FOR $5;
  pUomId ALIAS FOR $6;
  pQtyPer ALIAS FOR $7;
  pScrap ALIAS FOR $8;
  pEffective ALIAS FOR $9;
  pExpires ALIAS FOR $10;
  pCreateWo ALIAS FOR $11;
  pBOOItemseqid ALIAS FOR $12;
  pSchedAtWooper ALIAS FOR $13;
  pECN ALIAS FOR $14;
  pSubType ALIAS FOR $15;
  pRevisionid ALIAS FOR $16;
  pCharId ALIAS FOR $17;
  pCharVal ALIAS FOR $18;
  _bomworksetid INTEGER;
  _temp INTEGER;
  _bomitemid INTEGER;

BEGIN

  SELECT createBOMItem( pBomitemid, pParentItemid, pComponentItemid,
                        pSeqNumber, pIssueMethod,
                        pUomId, pQtyPer, pScrap,
                        pEffective, pExpires,
                        pCreateWo, pBOOItemseqid, pSchedAtWooper, pECN, pSubType, pRevisionid, pCharId, pCharVal, NULL, NULL ) INTO _bomitemid;

  RETURN _bomitemid;
  
END;
$_$;


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

--
-- TOC entry 1312 (class 1255 OID 146565865)
-- Dependencies: 4536 8
-- Name: createbomitem(integer, integer, integer, character, integer, numeric, numeric, date, date, boolean, integer, boolean, text, character, integer, integer, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createbomitem(integer, integer, integer, character, integer, numeric, numeric, date, date, boolean, integer, boolean, text, character, integer, integer, text, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pBomitemid ALIAS FOR $1;
  pParentItemid ALIAS FOR $2;
  pComponentItemid ALIAS FOR $3;
  pIssueMethod ALIAS FOR $4;
  pUomId ALIAS FOR $5;
  pQtyPer ALIAS FOR $6;
  pScrap ALIAS FOR $7;
  pEffective ALIAS FOR $8;
  pExpires ALIAS FOR $9;
  pCreateWo ALIAS FOR $10;
  pBOOItemseqid ALIAS FOR $11;
  pSchedAtWooper ALIAS FOR $12;
  pECN ALIAS FOR $13;
  pSubType ALIAS FOR $14;
  pRevisionid ALIAS FOR $15;
  pCharId ALIAS FOR $16;
  pCharVal ALIAS FOR $17;
  pNotes ALIAS FOR $18;
  pRef ALIAS FOR $19;
  _seqNumber INTEGER;
  _bomitemid INTEGER;

BEGIN

--  Grab the next Sequence Number, if any
  SELECT MAX(bomitem_seqnumber) INTO _seqNumber
  FROM bomitem(pParentItemid,pRevisionid);

  IF (_seqNumber IS NOT NULL) THEN
   _seqNumber := (_seqNumber + 10);
  ELSE
   _seqNumber := 10;
  END IF;

  SELECT createBOMItem( pBomitemid, pParentItemid, pComponentItemid,
                        _seqNumber, pIssueMethod,
                        pUomId, pQtyPer, pScrap,
                        pEffective, pExpires,
                        pCreateWo, pBOOItemseqid, pSchedAtWooper, pECN, pSubType, pRevisionid, pCharId, pCharVal, pNotes, pRef ) INTO _bomitemid;

  RETURN _bomitemid;

END;
$_$;


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

--
-- TOC entry 1315 (class 1255 OID 146565867)
-- Dependencies: 4536 8
-- Name: createbomitem(integer, integer, integer, integer, character, integer, numeric, numeric, numeric, date, date, boolean, integer, boolean, text, character, integer, integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createbomitem(integer, integer, integer, integer, character, integer, numeric, numeric, numeric, date, date, boolean, integer, boolean, text, character, integer, integer, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pBomitemid ALIAS FOR $1;
  pParentItemid ALIAS FOR $2;
  pComponentItemid ALIAS FOR $3;
  pSeqNumber ALIAS FOR $4;
  pIssueMethod ALIAS FOR $5;
  pUomId ALIAS FOR $6;
  pQtyFxd ALIAS FOR $7;
  pQtyPer ALIAS FOR $8;
  pScrap ALIAS FOR $9;
  pEffective ALIAS FOR $10;
  pExpires ALIAS FOR $11;
  pCreateWo ALIAS FOR $12;
  pBOOItemseqid ALIAS FOR $13;
  pSchedAtWooper ALIAS FOR $14;
  pECN ALIAS FOR $15;
  pSubType ALIAS FOR $16;
  pRevisionid ALIAS FOR $17;
  pCharId ALIAS FOR $18;
  pCharVal ALIAS FOR $19;
  _bomworksetid INTEGER;
  _temp INTEGER;
  _bomitemid INTEGER;

BEGIN

  SELECT createBOMItem( pBomitemid, pParentItemid, pComponentItemid,
                        pSeqNumber, pIssueMethod,
                        pUomId, pQtyFxd, pQtyPer, pScrap,
                        pEffective, pExpires,
                        pCreateWo, pBOOItemseqid, pSchedAtWooper, pECN, pSubType, pRevisionid, pCharId, pCharVal, NULL, NULL ) INTO _bomitemid;

  RETURN _bomitemid;
  
END;
$_$;


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

--
-- TOC entry 1311 (class 1255 OID 146565864)
-- Dependencies: 4536 8
-- Name: createbomitem(integer, integer, integer, integer, character, integer, numeric, numeric, date, date, boolean, integer, boolean, text, character, integer, integer, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createbomitem(integer, integer, integer, integer, character, integer, numeric, numeric, date, date, boolean, integer, boolean, text, character, integer, integer, text, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pBomitemid ALIAS FOR $1;
  pParentItemid ALIAS FOR $2;
  pComponentItemid ALIAS FOR $3;
  pSeqNumber ALIAS FOR $4;
  pIssueMethod ALIAS FOR $5;
  pUomId ALIAS FOR $6;
  pQtyPer ALIAS FOR $7;
  pScrap ALIAS FOR $8;
  pEffective ALIAS FOR $9;
  pExpires ALIAS FOR $10;
  pCreateWo ALIAS FOR $11;
  pBOOItemseqid ALIAS FOR $12;
  pSchedAtWooper ALIAS FOR $13;
  pECN ALIAS FOR $14;
  pSubType ALIAS FOR $15;
  pRevisionid ALIAS FOR $16;
  pCharId ALIAS FOR $17;
  pCharVal ALIAS FOR $18;
  pNotes ALIAS FOR $19;
  pRef ALIAS FOR $20;
  _bomworksetid INTEGER;
  _temp INTEGER;

BEGIN

--  Make sure that the parent and component are not the same
  IF (pParentItemid = pComponentItemid) THEN
    RETURN -1;
  END IF;

--  Make sure that the parent is not used in the component at some level
  IF ( SELECT (item_type IN ('M', 'F'))
       FROM item
       WHERE (item_id=pComponentItemid) ) THEN
    SELECT indentedWhereUsed(pParentItemid) INTO _bomworksetid;
    SELECT bomwork_id INTO _temp
    FROM bomwork
    WHERE ( (bomwork_set_id=_bomworksetid)
     AND (bomwork_item_id=pComponentItemid) )
    LIMIT 1;
    IF (FOUND) THEN
      PERFORM deleteBOMWorkset(_bomworksetid);
      RETURN -2;
    END IF;
  END IF;

  PERFORM deleteBOMWorkset(_bomworksetid);

--  Create the BOM Item
  INSERT INTO bomitem
  ( bomitem_id, bomitem_parent_item_id, bomitem_item_id,
    bomitem_seqnumber, bomitem_issuemethod,
    bomitem_uom_id, bomitem_qtyper, bomitem_scrap,
    bomitem_effective, bomitem_expires,
    bomitem_createwo,
    bomitem_booitem_seq_id, bomitem_schedatwooper,
    bomitem_ecn, bomitem_subtype, bomitem_moddate, bomitem_rev_id,
    bomitem_char_id, bomitem_value, bomitem_notes, bomitem_ref )
  VALUES
  ( pBomitemid, pParentItemid, pComponentItemid,
    pSeqNumber, pIssueMethod,
    pUomId, pQtyPer, pScrap,
    pEffective, pExpires,
    pCreateWo,
    pBOOItemseqid, COALESCE(pSchedAtWooper, FALSE),
    pECN, pSubType, CURRENT_DATE, pRevisionid,
    pCharId,pCharVal,pNotes, pRef );

  RETURN pBomitemid;

END;
$_$;


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

--
-- TOC entry 1316 (class 1255 OID 146565868)
-- Dependencies: 4536 8
-- Name: createbomitem(integer, integer, integer, character, integer, numeric, numeric, numeric, date, date, boolean, integer, boolean, text, character, integer, integer, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createbomitem(integer, integer, integer, character, integer, numeric, numeric, numeric, date, date, boolean, integer, boolean, text, character, integer, integer, text, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pBomitemid ALIAS FOR $1;
  pParentItemid ALIAS FOR $2;
  pComponentItemid ALIAS FOR $3;
  pIssueMethod ALIAS FOR $4;
  pUomId ALIAS FOR $5;
  pQtyFxd ALIAS FOR $6;
  pQtyPer ALIAS FOR $7;
  pScrap ALIAS FOR $8;
  pEffective ALIAS FOR $9;
  pExpires ALIAS FOR $10;
  pCreateWo ALIAS FOR $11;
  pBOOItemseqid ALIAS FOR $12;
  pSchedAtWooper ALIAS FOR $13;
  pECN ALIAS FOR $14;
  pSubType ALIAS FOR $15;
  pRevisionid ALIAS FOR $16;
  pCharId ALIAS FOR $17;
  pCharVal ALIAS FOR $18;
  pNotes ALIAS FOR $19;
  pRef ALIAS FOR $20;
  _seqNumber INTEGER;
  _bomitemid INTEGER;

BEGIN

--  Grab the next Sequence Number, if any
  SELECT MAX(bomitem_seqnumber) INTO _seqNumber
  FROM bomitem(pParentItemid,pRevisionid);

  IF (_seqNumber IS NOT NULL) THEN
   _seqNumber := (_seqNumber + 10);
  ELSE
   _seqNumber := 10;
  END IF;

  SELECT createBOMItem( pBomitemid, pParentItemid, pComponentItemid,
                        _seqNumber, pIssueMethod,
                        pUomId, pQtyFxd, pQtyPer, pScrap,
                        pEffective, pExpires,
                        pCreateWo, pBOOItemseqid, pSchedAtWooper, pECN, pSubType, pRevisionid, pCharId, pCharVal, pNotes, pRef ) INTO _bomitemid;

  RETURN _bomitemid;

END;
$_$;


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

--
-- TOC entry 1314 (class 1255 OID 146565866)
-- Dependencies: 4536 8
-- Name: createbomitem(integer, integer, integer, integer, character, integer, numeric, numeric, numeric, date, date, boolean, integer, boolean, text, character, integer, integer, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createbomitem(integer, integer, integer, integer, character, integer, numeric, numeric, numeric, date, date, boolean, integer, boolean, text, character, integer, integer, text, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pBomitemid ALIAS FOR $1;
  pParentItemid ALIAS FOR $2;
  pComponentItemid ALIAS FOR $3;
  pSeqNumber ALIAS FOR $4;
  pIssueMethod ALIAS FOR $5;
  pUomId ALIAS FOR $6;
  pQtyFxd ALIAS FOR $7;
  pQtyPer ALIAS FOR $8;
  pScrap ALIAS FOR $9;
  pEffective ALIAS FOR $10;
  pExpires ALIAS FOR $11;
  pCreateWo ALIAS FOR $12;
  pBOOItemseqid ALIAS FOR $13;
  pSchedAtWooper ALIAS FOR $14;
  pECN ALIAS FOR $15;
  pSubType ALIAS FOR $16;
  pRevisionid ALIAS FOR $17;
  pCharId ALIAS FOR $18;
  pCharVal ALIAS FOR $19;
  pNotes ALIAS FOR $20;
  pRef ALIAS FOR $21;
  _bomworksetid INTEGER;
  _temp INTEGER;

BEGIN

--  Make sure that the parent and component are not the same
  IF (pParentItemid = pComponentItemid) THEN
    RETURN -1;
  END IF;

--  Make sure that the parent is not used in the component at some level
  SELECT indentedWhereUsed(pParentItemid) INTO _bomworksetid;
  SELECT bomwork_id INTO _temp
  FROM bomwork
  WHERE ( (bomwork_set_id=_bomworksetid)
   AND (bomwork_item_id=pComponentItemid) )
  LIMIT 1;
  IF (FOUND) THEN
    PERFORM deleteBOMWorkset(_bomworksetid);
    RETURN -2;
  END IF;

  PERFORM deleteBOMWorkset(_bomworksetid);

--  Create the BOM Item
  INSERT INTO bomitem
  ( bomitem_id, bomitem_parent_item_id, bomitem_item_id,
    bomitem_seqnumber, bomitem_issuemethod,
    bomitem_uom_id, bomitem_qtyfxd, bomitem_qtyper, bomitem_scrap,
    bomitem_effective, bomitem_expires,
    bomitem_createwo,
    bomitem_booitem_seq_id, bomitem_schedatwooper,
    bomitem_ecn, bomitem_subtype, bomitem_moddate, bomitem_rev_id,
    bomitem_char_id, bomitem_value, bomitem_notes, bomitem_ref )
  VALUES
  ( pBomitemid, pParentItemid, pComponentItemid,
    pSeqNumber, pIssueMethod,
    pUomId, pQtyFxd, pQtyPer, pScrap,
    pEffective, pExpires,
    pCreateWo,
    pBOOItemseqid, COALESCE(pSchedAtWooper, FALSE),
    pECN, pSubType, CURRENT_DATE, pRevisionid,
    pCharId,pCharVal,pNotes, pRef );

  RETURN pBomitemid;

END;
$_$;


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

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

CREATE FUNCTION createcheck(integer, text, integer, date, numeric, integer, integer, integer, text, text, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pBankaccntid		ALIAS FOR  $1;
  pRecipType		ALIAS FOR  $2;
  pRecipId		ALIAS FOR  $3;
  pCheckDate		ALIAS FOR  $4;
  pAmount		ALIAS FOR  $5;
  pCurrid		ALIAS FOR  $6;
  pExpcatid		ALIAS FOR  $7;
  _journalNumber	INTEGER := $8;
  pFor			ALIAS FOR  $9;
  pNotes		ALIAS FOR $10;
  pMisc			ALIAS FOR $11;
  _checkid INTEGER;
BEGIN

  SELECT createCheck(pBankaccntid,pRecipType,pRecipId,pCheckDate,pAmount,pCurrid,pExpcatid,_journalNumber,pFor,pNotes,pMisc,NULL) INTO _checkid;
  RETURN _checkid;

END;
$_$;


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

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

CREATE FUNCTION createcheck(integer, text, integer, date, numeric, integer, integer, integer, text, text, boolean, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pBankaccntid		ALIAS FOR  $1;
  pRecipType		ALIAS FOR  $2;
  pRecipId		ALIAS FOR  $3;
  pCheckDate		ALIAS FOR  $4;
  pAmount		ALIAS FOR  $5;
  pCurrid		ALIAS FOR  $6;
  pExpcatid		ALIAS FOR  $7;
  _journalNumber	INTEGER := $8;
  pFor			ALIAS FOR  $9;
  pNotes		ALIAS FOR $10;
  pMisc			ALIAS FOR $11;
  pAropenid             ALIAS FOR $12;
  _checkid		INTEGER;
  _check_curr_rate      NUMERIC;
  _bankaccnt_currid	INTEGER;

BEGIN
  SELECT bankaccnt_curr_id,currRate(bankaccnt_curr_id,pCheckDate) INTO _bankaccnt_currid, _check_curr_rate
  FROM bankaccnt
  WHERE bankaccnt_id = pBankaccntid;
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  IF (pRecipType NOT IN ('C', 'T', 'V')) THEN
    RETURN -2;
  END IF;

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

  IF (pAmount <= 0) THEN
    RETURN -4;
  END IF;

  IF (pCurrid IS NULL
      OR NOT EXISTS(SELECT * FROM curr_symbol WHERE (curr_id=pCurrid))) THEN
    RETURN -5;
  END IF;

  IF (pExpcatid IS NOT NULL
      AND NOT EXISTS(SELECT * FROM expcat WHERE (expcat_id=pExpcatid))) THEN
    RETURN -6;
  END IF;

-- Do not assign Journal Number until check is posted
--  if (_journalNumber IS NULL) THEN
--    _journalNumber := fetchJournalNumber('AP-CK');
--  END IF;

  _checkid := NEXTVAL('checkhead_checkhead_id_seq');

  INSERT INTO checkhead
  ( checkhead_id,		checkhead_recip_type,	checkhead_recip_id,
    checkhead_bankaccnt_id,	checkhead_number,
    checkhead_amount,
    checkhead_checkdate,	checkhead_misc,		checkhead_expcat_id,
    checkhead_journalnumber,	checkhead_for,		checkhead_notes,
    checkhead_curr_id )
  VALUES
  ( _checkid,			pRecipType,		pRecipId,
    pBankaccntid,		-1, --fetchNextCheckNumber(pBankaccntid),
    currToCurr(pCurrid, _bankaccnt_currid, pAmount, pCheckDate),
    pCheckDate,			COALESCE(pMisc, FALSE),	pExpcatid,
    _journalNumber,		pFor,			pNotes,
    _bankaccnt_currid );

  IF (pAropenid IS NOT NULL AND fetchmetricbool('EnableReturnAuth')) THEN
    INSERT INTO checkitem (checkitem_checkhead_id,checkitem_amount,checkitem_discount,checkitem_ponumber,
                           checkitem_aropen_id,checkitem_docdate,checkitem_curr_id,checkitem_cmnumber,
                           checkitem_ranumber, checkitem_curr_rate)
    SELECT _checkid, currToCurr(checkhead_curr_id, aropen_curr_id, pAmount, checkhead_checkdate),
      0,cmhead_custponumber,pAropenid,aropen_docdate,aropen_curr_id,cmhead_number,rahead_number,
      1 / (_check_curr_rate / aropen_curr_rate)
    FROM checkhead, aropen
      LEFT OUTER JOIN cmhead ON (aropen_docnumber=cmhead_number)
      LEFT OUTER JOIN rahead ON (cmhead_rahead_id=rahead_id)
    WHERE ((aropen_id=pAropenid)
     AND (checkhead_id=_checkid));
  ELSIF (pAropenid IS NOT NULL) THEN
    INSERT INTO checkitem (checkitem_checkhead_id,checkitem_amount,checkitem_discount,checkitem_ponumber,
                           checkitem_aropen_id,checkitem_docdate,checkitem_curr_id,checkitem_cmnumber,
                           checkitem_ranumber, checkitem_curr_rate)
    SELECT _checkid,currToCurr(checkhead_curr_id, aropen_curr_id, pAmount, checkhead_checkdate),
      0,cmhead_custponumber,pAropenid,aropen_docdate,aropen_curr_id,cmhead_number,NULL,
      1 / (_check_curr_rate / aropen_curr_rate)
    FROM checkhead, aropen
      LEFT OUTER JOIN cmhead ON (aropen_docnumber=cmhead_number)
    WHERE ((aropen_id=pAropenid)
     AND (checkhead_id=_checkid));
  END IF;
  

  RETURN _checkid;

END;
$_$;


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

--
-- TOC entry 1320 (class 1255 OID 146565871)
-- Dependencies: 4536 8
-- Name: createchecks(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createchecks(pbankaccntid integer, pcheckdate 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
  _v RECORD;
  _r RECORD;
  _c RECORD;
  _checkid		INTEGER;
  _counter		INTEGER := 0;
  _check_curr_id	INTEGER;
  _check_curr_rate      NUMERIC;

BEGIN

  SELECT bankaccnt_curr_id, currRate(bankaccnt_curr_id, pCheckDate) 
    INTO _check_curr_id, _check_curr_rate
    FROM bankaccnt
    WHERE ( bankaccnt_id = pBankaccntid );
  FOR _v IN SELECT DISTINCT vend_id, vend_number, vend_name
              FROM apselect
              JOIN apopen   ON (apselect_apopen_id=apopen_id)
              JOIN vendinfo ON (apopen_vend_id=vend_id)
            WHERE ((apselect_bankaccnt_id=pBankaccntid)
               AND (apselect_date <= pCheckDate)) LOOP

    -- if we owe this vendor anything (we might not) then create a check
    -- allow $0 checks
    IF ((SELECT SUM(CASE apopen_doctype WHEN 'C' THEN (apselect_amount * -1.0)
                                        ELSE apselect_amount END * _check_curr_rate / apopen_curr_rate)          
	 FROM apselect JOIN apopen ON (apopen_id=apselect_apopen_id)
	 WHERE ((apopen_vend_id=_v.vend_id)
	   AND  (apselect_bankaccnt_id=pBankaccntid)) ) >= 0) THEN
      -- 0.01 is a temporary amount; we''ll update the check amount later
      _checkid := createCheck(pBankaccntid,	'V',	_v.vend_id,
			      pCheckDate,		0.01,	_check_curr_id,
			      NULL,		NULL, '',	'',	FALSE);

      FOR _r IN SELECT apopen_id, apselect_id,
		       apopen_docnumber, apopen_invcnumber, apopen_ponumber,
		       apopen_docdate, apselect_curr_id,
		       apselect_amount, apselect_discount
		FROM apselect, apopen
		WHERE ( (apselect_apopen_id=apopen_id)
		 AND (apopen_vend_id=_v.vend_id)
		 AND (apselect_bankaccnt_id=pBankaccntid) ) LOOP
	INSERT INTO checkitem
	( checkitem_checkhead_id, checkitem_apopen_id,
	  checkitem_vouchernumber, checkitem_invcnumber, checkitem_ponumber,
	  checkitem_amount, checkitem_discount, checkitem_docdate,
          checkitem_curr_id, checkitem_curr_rate )
	VALUES
	( _checkid, _r.apopen_id,
	  _r.apopen_docnumber, _r.apopen_invcnumber, _r.apopen_ponumber,
	  _r.apselect_amount, _r.apselect_discount, _r.apopen_docdate,
	  _r.apselect_curr_id, 
          1 / (_check_curr_rate / currRate(_r.apselect_curr_id, pCheckdate))  );

	DELETE FROM apselect
	WHERE (apselect_id=_r.apselect_id);

      END LOOP;

      -- one check can pay for purchases on multiple dates in multiple currencies
      UPDATE checkhead
      SET checkhead_amount = (SELECT SUM(CASE WHEN (apopen_doctype='C') THEN checkitem_amount / checkitem_curr_rate * -1.0
                                              ELSE checkitem_amount / checkitem_curr_rate END)
			      FROM checkitem LEFT OUTER JOIN apopen ON (apopen_id=checkitem_apopen_id)
			      WHERE (checkitem_checkhead_id=checkhead_id))
      WHERE (checkhead_id=_checkid);

      _counter := (_counter + 1);
    END IF;

  END LOOP;

  RETURN _counter;

END;
$_$;


ALTER FUNCTION public.createchecks(pbankaccntid integer, pcheckdate date) OWNER TO admin;

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

CREATE FUNCTION createcounttag(integer, text, 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
  pItemsiteid ALIAS FOR $1;
  pComments ALIAS FOR $2;
  pPriority ALIAS FOR $3;
  pFreeze ALIAS FOR $4;
BEGIN
  RETURN createCountTag(pItemsiteid, pComments, pPriority, pFreeze, NULL);
END;
$_$;


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

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

CREATE FUNCTION createcounttag(integer, text, boolean, boolean, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$

-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemsiteid ALIAS FOR $1;
  pComments ALIAS FOR $2;
  pPriority ALIAS FOR $3;
  pFreeze ALIAS FOR $4;
  pLocationid ALIAS FOR $5;
  _invcntid INTEGER;
  _whs		RECORD;
  _type CHARACTER;
  _controlmethod        CHARACTER;

BEGIN

  SELECT item_type, itemsite_controlmethod INTO _type, _controlmethod
    FROM itemsite, item
   WHERE ((itemsite_item_id=item_id)
     AND  (itemsite_id=pItemsiteid));

  IF (NOT FOUND OR _type IN ('F', 'R', 'L','J') OR _controlmethod = 'N') THEN
    RETURN 0; -- We simply do not do these item types.
  END IF;

  -- Test for existing tags
   IF (pLocationid IS NULL) THEN
       SELECT invcnt_id INTO _invcntid
       FROM invcnt
       WHERE ((NOT invcnt_posted)
       AND (invcnt_location_id IS NULL)
       AND (invcnt_itemsite_id=pItemsiteid));
  
  ELSE

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

  IF (NOT FOUND) THEN
    SELECT NEXTVAL('invcnt_invcnt_id_seq') INTO _invcntid;

    SELECT whsinfo.* INTO _whs
      FROM whsinfo, itemsite
     WHERE ((warehous_id=itemsite_warehous_id)
       AND  (itemsite_id=pItemsiteid));

    INSERT INTO invcnt (
      invcnt_id, invcnt_itemsite_id, invcnt_tagdate,
      invcnt_tagnumber,
      invcnt_tag_username, invcnt_posted,
      invcnt_priority, invcnt_comments, invcnt_location_id
    ) VALUES (
      _invcntid, pItemsiteid, CURRENT_TIMESTAMP,
      (_whs.warehous_counttag_prefix || _whs.warehous_counttag_number::TEXT),
      getEffectiveXtUser(), FALSE,
      pPriority, pComments, pLocationid
    );

    UPDATE whsinfo
    SET warehous_counttag_number=(warehous_counttag_number + 1)
    WHERE (warehous_id=_whs.warehous_id);

    IF (pFreeze) THEN
      UPDATE itemsite
      SET itemsite_freeze=TRUE
      WHERE (itemsite_id=pItemsiteid);
    END IF;

  END IF;

  RETURN _invcntid;
END;
$_$;


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

--
-- TOC entry 1323 (class 1255 OID 146565874)
-- Dependencies: 4536 8
-- Name: createcustomer(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createcustomer(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
  DECLARE
    pcrmacctId  ALIAS FOR $1;
    _custId     INTEGER := 0;
  BEGIN
    IF (pcrmacctId < 0 OR pcrmacctId IS NULL) THEN
      RETURN -1;
    END IF;

    SELECT crmacct_cust_id INTO _custId
    FROM crmacct WHERE crmacct_id = pcrmacctId;

    IF (_custId IS NOT NULL AND _custId <= 0) THEN
      RETURN -2;
    END IF;

    INSERT INTO _customer (active, customer_number, customer_name)
      SELECT crmacct_active, crmacct_number, crmacct_name
      FROM crmacct
      WHERE crmacct_id = pcrmacctId;
    _custId := CURRVAL('cust_cust_id_seq');

    UPDATE crmacct SET crmacct_prospect_id = NULL, crmacct_cust_id = _custId
    WHERE crmacct_id = pcrmacctId;

    RETURN _custId;
  END;
$_$;


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

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

CREATE FUNCTION createcyclecountsbywarehouse(integer, integer, text, 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
  pWarehousid ALIAS FOR $1;
  pMaxNumber ALIAS FOR $2;
  pComments ALIAS FOR $3;
  pPriority ALIAS FOR $4;
  pFreeze ALIAS FOR $5;

BEGIN
  RETURN createCycleCountsByWarehouse(pWarehousid, pMaxNumber, pComments, pPriority, pFreeze, NULL, FALSE);
END;
$_$;


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

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

CREATE FUNCTION createcyclecountsbywarehouse(integer, integer, integer, text, 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
  pWarehousid ALIAS FOR $1;
  pClasscodeid ALIAS FOR $2;
  pMaxNumber ALIAS FOR $3;
  pComments ALIAS FOR $4;
  pPriority ALIAS FOR $5;
  pFreeze ALIAS FOR $6;
BEGIN
  RETURN createCycleCountsByWarehouseByClassCode(pWarehousid, pClasscodeid, pMaxNumber, pComments, pPriority, pFreeze, NULL, FALSE);
END;
$_$;


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

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

CREATE FUNCTION createcyclecountsbywarehouse(integer, text, integer, text, 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
  pWarehousid ALIAS FOR $1;
  pClasscodePattern ALIAS FOR $2;
  pMaxNumber ALIAS FOR $3;
  pComments ALIAS FOR $4;
  pPriority ALIAS FOR $5;
  pFreeze ALIAS FOR $6;
BEGIN
  RETURN createCycleCountsByWarehouseByClassCode(pWarehousid, pClasscodePattern, pMaxNumber, pComments, pPriority, pFreeze, NULL, FALSE);
END;
$_$;


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

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

CREATE FUNCTION createcyclecountsbywarehouse(integer, integer, text, boolean, boolean, integer, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWarehousid ALIAS FOR $1;
  pMaxNumber ALIAS FOR $2;
  pComments ALIAS FOR $3;
  pPriority ALIAS FOR $4;
  pFreeze ALIAS FOR $5;
  pLocationid ALIAS FOR $6;
  pIgnoreZeroBalance ALIAS FOR $7;
  _itemsites RECORD;
  _returnVal	INTEGER;
  
BEGIN

IF (pLocationid IS NULL) THEN
  FOR _itemsites IN SELECT itemsite_id, itemsite_warehous_id, itemsite_qtyonhand
                    FROM itemsite, item
                    WHERE ( (itemsite_active)
                     AND (itemsite_item_id=item_id)
                     AND (itemsite_cyclecountfreq > 0)
                     AND ((COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq) < CURRENT_DATE)
                     AND (itemsite_id NOT IN ( SELECT invcnt_itemsite_id
                                               FROM invcnt, itemsite
                                               WHERE ( (invcnt_itemsite_id=itemsite_id)
                                                AND (itemsite_warehous_id=pWarehousid)
						AND (invcnt_location_id IS NULL)
                                                AND (NOT invcnt_posted) ) ) )
                     AND ((NOT pIgnoreZeroBalance) OR (itemsite_qtyonhand <> 0))
                     AND ((pLocationid IS NULL) OR (validLocation(pLocationid, itemsite_id)))
                     AND (itemsite_warehous_id=pWarehousid) )
                    ORDER BY (COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq), itemsite_abcclass, item_number

LIMIT pMaxNumber LOOP
    _returnVal := createCountTag(_itemsites.itemsite_id, pComments,
				    pPriority, pFreeze, pLocationid);
    IF (_returnVal < 0) THEN
      RETURN _returnVal;
    END IF;
  END LOOP;            

ELSE
  FOR _itemsites IN SELECT itemsite_id, itemsite_warehous_id, SUM(itemloc_qty)
                    FROM itemsite, itemloc
                    WHERE ( (itemsite_active)
                     AND (itemsite_cyclecountfreq > 0)
                     AND ((COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq) < CURRENT_DATE)
                     AND ((NOT pIgnoreZeroBalance) OR (itemsite_qtyonhand <> 0))
                     AND (pLocationid = itemloc_location_id)
                     AND (itemloc_itemsite_id = itemsite_id)
                     AND (itemsite_warehous_id=pWarehousid) )
		    GROUP BY itemsite_id, itemsite_warehous_id,
			     itemsite_datelastcount, itemsite_cyclecountfreq,
			     itemsite_abcclass
                    ORDER BY (COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq), itemsite_abcclass

LIMIT pMaxNumber LOOP
    _returnVal := createCountTag(_itemsites.itemsite_id, pComments,
				    pPriority, pFreeze, pLocationid);
    IF (_returnVal < 0) THEN
      RETURN _returnVal;
    END IF;
  END LOOP;  
                   
END IF;

  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION createcyclecountsbywarehousebyclasscode(integer, integer, integer, text, boolean, boolean, integer, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWarehousid ALIAS FOR $1;
  pClasscodeid ALIAS FOR $2;
  pMaxNumber ALIAS FOR $3;
  pComments ALIAS FOR $4;
  pPriority ALIAS FOR $5;
  pFreeze ALIAS FOR $6;
  pLocationid ALIAS FOR $7;
  pIgnoreZeroBalance ALIAS FOR $8;
  _itemsites RECORD;
  _returnVal	INTEGER;
  
BEGIN

IF (pLocationid IS NULL) THEN
  FOR _itemsites IN SELECT itemsite_id, itemsite_warehous_id, itemsite_qtyonhand
                    FROM itemsite, item
                    WHERE ( (itemsite_active)
                     AND (itemsite_item_id=item_id)
                     AND (itemsite_cyclecountfreq > 0)
                     AND ((COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq) < CURRENT_DATE)
                     AND (itemsite_id NOT IN ( SELECT invcnt_itemsite_id
                                               FROM invcnt, itemsite
                                               WHERE ( (invcnt_itemsite_id=itemsite_id)
                                                AND (itemsite_warehous_id=pWarehousid)
						AND (invcnt_location_id IS NULL)
                                                AND (NOT invcnt_posted) ) ) )
                     AND ((NOT pIgnoreZeroBalance) OR (itemsite_qtyonhand <> 0))
                     AND ((pLocationid IS NULL) OR (validLocation(pLocationid, itemsite_id)))
                     AND (itemsite_warehous_id=pWarehousid)
                     AND (item_classcode_id=pClasscodeid) )
                    ORDER BY (COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq), itemsite_abcclass, item_number
                    LIMIT pMaxNumber LOOP
    _returnVal := createCountTag(_itemsites.itemsite_id, pComments,
				    pPriority, pFreeze, pLocationid);
    IF (_returnVal < 0) THEN
      RETURN _returnVal;
    END IF;
  END LOOP;

ELSE
  FOR _itemsites IN SELECT itemsite_id, itemsite_warehous_id, SUM(itemloc_qty)
                    FROM itemsite, item, itemloc
                    WHERE ( (itemsite_active)
                     AND (itemsite_item_id=item_id)
                     AND (itemsite_cyclecountfreq > 0)
                     AND ((COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq) < CURRENT_DATE)
                     AND ((NOT pIgnoreZeroBalance) OR (itemsite_qtyonhand <> 0))
                     AND (pLocationid = itemloc_location_id)
                     AND (itemloc_itemsite_id = itemsite_id)
                     AND (itemsite_warehous_id=pWarehousid) 
                     AND (item_classcode_id=pClasscodeid) )
		    GROUP BY itemsite_id, itemsite_warehous_id,
			     itemsite_datelastcount, itemsite_cyclecountfreq,
			     itemsite_abcclass
                    ORDER BY (COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq), itemsite_abcclass
                    LIMIT pMaxNumber LOOP
    _returnVal := createCountTag(_itemsites.itemsite_id, pComments,
				    pPriority, pFreeze, pLocationid);
    IF (_returnVal < 0) THEN
      RETURN _returnVal;
    END IF;
  END LOOP;

END IF;

  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION createcyclecountsbywarehousebyclasscode(integer, text, integer, text, boolean, boolean, integer, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWarehousid ALIAS FOR $1;
  pClasscodePattern ALIAS FOR $2;
  pMaxNumber ALIAS FOR $3;
  pComments ALIAS FOR $4;
  pPriority ALIAS FOR $5;
  pFreeze ALIAS FOR $6;
  pLocationid ALIAS FOR $7;
  pIgnoreZeroBalance ALIAS FOR $8;
  _itemsites RECORD;
  _returnVal	INTEGER;
  
BEGIN

IF (pLocationid IS NULL) THEN
  FOR _itemsites IN SELECT itemsite_id, itemsite_warehous_id, itemsite_qtyonhand
                    FROM itemsite, item, classcode
                    WHERE ( (itemsite_active)
                     AND (itemsite_item_id=item_id)
                     AND (item_classcode_id=classcode_id)
                     AND (itemsite_cyclecountfreq > 0)
                     AND ((COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq) < CURRENT_DATE)
                     AND (itemsite_id NOT IN ( SELECT invcnt_itemsite_id
                                               FROM invcnt, itemsite
                                               WHERE ( (invcnt_itemsite_id=itemsite_id)
                                                AND (itemsite_warehous_id=pWarehousid)
						AND (invcnt_location_id IS NULL)
                                                AND (NOT invcnt_posted) ) ) )
                     AND ((NOT pIgnoreZeroBalance) OR (itemsite_qtyonhand <> 0))
                     AND ((pLocationid IS NULL) OR (validLocation(pLocationid, itemsite_id)))
                     AND (itemsite_warehous_id=pWarehousid)
                     AND (classcode_code ~ pClasscodePattern) )
                    ORDER BY (COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq), itemsite_abcclass, item_number
                    LIMIT pMaxNumber LOOP
    _returnVal := createCountTag(_itemsites.itemsite_id, pComments,
				    pPriority, pFreeze, pLocationid);
    IF (_returnVal < 0) THEN
      RETURN _returnVal;
    END IF;
  END LOOP;

ELSE
  FOR _itemsites IN SELECT itemsite_id, itemsite_warehous_id, SUM(itemloc_qty)
                    FROM itemsite, item, classcode, itemloc
                    WHERE ( (itemsite_active)
                     AND (itemsite_item_id=item_id)
                     AND (item_classcode_id=classcode_id)
                     AND (itemsite_cyclecountfreq > 0)
                     AND ((COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq) < CURRENT_DATE)
                     AND ((NOT pIgnoreZeroBalance) OR (itemsite_qtyonhand <> 0))
                     AND (pLocationid = itemloc_location_id)
                     AND (itemloc_itemsite_id = itemsite_id)
                     AND (itemsite_warehous_id=pWarehousid)
                     AND (classcode_code ~ pClasscodePattern) )
		    GROUP BY itemsite_id, itemsite_warehous_id,
			     itemsite_datelastcount, itemsite_cyclecountfreq,
			     itemsite_abcclass
                    ORDER BY (COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq), itemsite_abcclass
                    LIMIT pMaxNumber LOOP
    _returnVal := createCountTag(_itemsites.itemsite_id, pComments,
				    pPriority, pFreeze, pLocationid);
    IF (_returnVal < 0) THEN
      RETURN _returnVal;
    END IF;
  END LOOP;

END IF;

  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION createcyclecountsbywarehousebyplannercode(integer, text, integer, text, boolean, boolean, integer, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWarehousid ALIAS FOR $1;
  pPlancodePattern ALIAS FOR $2;
  pMaxNumber ALIAS FOR $3;
  pComments ALIAS FOR $4;
  pPriority ALIAS FOR $5;
  pFreeze ALIAS FOR $6;
  pLocationid ALIAS FOR $7;
  pIgnoreZeroBalance ALIAS FOR $8;
  _itemsites RECORD;
  _returnVal	INTEGER;
  
BEGIN

IF (pLocationid IS NULL) THEN
  FOR _itemsites IN SELECT itemsite_id, itemsite_warehous_id, itemsite_qtyonhand
                    FROM itemsite, item, plancode
                    WHERE ( (itemsite_active)
                     AND (itemsite_item_id=item_id)
                     AND (itemsite_plancode_id=plancode_id)
                     AND (itemsite_cyclecountfreq > 0)
                     AND ((COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq) < CURRENT_DATE)
                     AND (itemsite_id NOT IN ( SELECT invcnt_itemsite_id
                                               FROM invcnt, itemsite
                                               WHERE ( (invcnt_itemsite_id=itemsite_id)
                                                AND (itemsite_warehous_id=pWarehousid)
						AND (invcnt_location_id IS NULL)
                                                AND (NOT invcnt_posted) ) ) )
                     AND ((NOT pIgnoreZeroBalance) OR (itemsite_qtyonhand <> 0))
                     AND ((pLocationid IS NULL) OR (validLocation(pLocationid, itemsite_id)))
                     AND (itemsite_warehous_id=pWarehousid)
                     AND (plancode_code ~ pPlancodePattern) )
                    ORDER BY (COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq), itemsite_abcclass, item_number
                    LIMIT pMaxNumber LOOP
    _returnVal := createCountTag(_itemsites.itemsite_id, pComments,
				    pPriority, pFreeze, pLocationid);
    IF (_returnVal < 0) THEN
      RETURN _returnVal;
    END IF;
  END LOOP;

ELSE
  FOR _itemsites IN SELECT itemsite_id, itemsite_warehous_id, SUM(itemloc_qty)
                    FROM itemsite, plancode, itemloc
                    WHERE ( (itemsite_active)
                     AND (itemsite_plancode_id=plancode_id)
                     AND (itemsite_cyclecountfreq > 0)
                     AND ((COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq) < CURRENT_DATE)
                     AND ((NOT pIgnoreZeroBalance) OR (itemsite_qtyonhand <> 0))
                     AND (pLocationid = itemloc_location_id)
                     AND (itemloc_itemsite_id = itemsite_id)
                     AND (itemsite_warehous_id=pWarehousid)
                     AND (plancode_code ~ pPlancodePattern) )
		    GROUP BY itemsite_id, itemsite_warehous_id,
			     itemsite_datelastcount, itemsite_cyclecountfreq,
			     itemsite_abcclass
                    ORDER BY (COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq), itemsite_abcclass
                    LIMIT pMaxNumber LOOP
    _returnVal := createCountTag(_itemsites.itemsite_id, pComments,
				    pPriority, pFreeze, pLocationid);
    IF (_returnVal < 0) THEN
      RETURN _returnVal;
    END IF;
  END LOOP;

END IF;

  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION createcyclecountsbywarehousebyplannercode(integer, integer, integer, text, boolean, boolean, integer, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWarehousid ALIAS FOR $1;
  pPlancodeid ALIAS FOR $2;
  pMaxNumber ALIAS FOR $3;
  pComments ALIAS FOR $4;
  pPriority ALIAS FOR $5;
  pFreeze ALIAS FOR $6;
  pLocationid ALIAS FOR $7;
  pIgnoreZeroBalance ALIAS FOR $8;
  _itemsites RECORD;
  _returnVal	INTEGER;
  
BEGIN

IF (pLocationid IS NULL) THEN
  FOR _itemsites IN SELECT itemsite_id, itemsite_warehous_id, itemsite_qtyonhand
                    FROM itemsite, item
                    WHERE ( (itemsite_active)
                     AND (itemsite_item_id=item_id)
                     AND (itemsite_cyclecountfreq > 0)
                     AND ((COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq) < CURRENT_DATE)
                     AND (itemsite_id NOT IN ( SELECT invcnt_itemsite_id
                                               FROM invcnt, itemsite
                                               WHERE ( (invcnt_itemsite_id=itemsite_id)
                                                AND (itemsite_warehous_id=pWarehousid)
						AND (invcnt_location_id IS NULL)
                                                AND (NOT invcnt_posted) ) ) )
                     AND ((NOT pIgnoreZeroBalance) OR (itemsite_qtyonhand <> 0))
                     AND ((pLocationid IS NULL) OR (validLocation(pLocationid, itemsite_id)))
                     AND (itemsite_warehous_id=pWarehousid)
                     AND (itemsite_plancode_id=pPlancodeid) )
                    ORDER BY (COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq), itemsite_abcclass, item_number
                    LIMIT pMaxNumber LOOP
    _returnVal := createCountTag(_itemsites.itemsite_id, pComments,
				    pPriority, pFreeze, pLocationid);
    IF (_returnVal < 0) THEN
      RETURN _returnVal;
    END IF;
  END LOOP;

ELSE
  FOR _itemsites IN SELECT itemsite_id, itemsite_warehous_id, SUM(itemloc_qty)
                    FROM itemsite, itemloc
                    WHERE ( (itemsite_active)
                     AND (itemsite_cyclecountfreq > 0)
                     AND ((COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq) < CURRENT_DATE)
                     AND ((NOT pIgnoreZeroBalance) OR (itemsite_qtyonhand <> 0))
                     AND (itemloc_itemsite_id = itemsite_id)
                     AND (itemsite_warehous_id=pWarehousid)
                     AND (pLocationid = itemloc_location_id)
                     AND (itemsite_plancode_id=pPlancodeid) )
		    GROUP BY itemsite_id, itemsite_warehous_id,
			     itemsite_datelastcount, itemsite_cyclecountfreq,
			     itemsite_abcclass
                    ORDER BY (COALESCE(itemsite_datelastcount, startOfTime()) + itemsite_cyclecountfreq), itemsite_abcclass
                    LIMIT pMaxNumber LOOP
    _returnVal := createCountTag(_itemsites.itemsite_id, pComments,
				    pPriority, pFreeze, pLocationid);
    IF (_returnVal < 0) THEN
      RETURN _returnVal;
    END IF;
  END LOOP;

END IF;

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1331 (class 1255 OID 146565883)
-- Dependencies: 4536 8
-- Name: createfile(text, text, bytea); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createfile(text, text, bytea) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
declare
  pTitle ALIAS FOR $1;
  pDescription ALIAS FOR $2;
  pStream ALIAS FOR $3;
  _id integer;
begin
  _id := nextval('file_file_id_seq');
  insert into file (file_id, file_title, file_descrip, file_stream) values (_id, pTitle, pDescription, pStream);
  return _id;
end;
$_$;


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

--
-- TOC entry 1332 (class 1255 OID 146565884)
-- Dependencies: 4536 8
-- Name: createinvoice(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createinvoice(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCobmiscid ALIAS FOR $1;
  _invcheadid INTEGER;
  _invcitemid INTEGER;
  _qtyToInvoice	NUMERIC;
  _r		RECORD;
  _s		RECORD;
  _lastlinenumber INTEGER := 1;
  
BEGIN

  IF ( ( SELECT cobmisc_posted
         FROM cobmisc
         WHERE (cobmisc_id=pCobmiscid) ) ) THEN
    RETURN -1;
  END IF;

  SELECT NEXTVAL('invchead_invchead_id_seq') INTO _invcheadid;

--  Give this selection a number if it has not been assigned one
  UPDATE cobmisc
  SET cobmisc_invcnumber=fetchInvcNumber()
  WHERE ( (cobmisc_invcnumber IS NULL)
   AND (cobmisc_id=pCobmiscid) );

--  Create the Invoice header
  INSERT INTO invchead
  ( 
	invchead_id,invchead_cust_id,invchead_shipto_id,invchead_ordernumber,invchead_orderdate,
	invchead_posted,invchead_printed,invchead_invcnumber,invchead_invcdate,invchead_shipdate,
	invchead_ponumber,invchead_shipvia,invchead_fob,invchead_billto_name,invchead_billto_address1,
	invchead_billto_address2,invchead_billto_address3,invchead_billto_city,invchead_billto_state,invchead_billto_zipcode,
	invchead_billto_phone,invchead_billto_country,invchead_shipto_name,invchead_shipto_address1,invchead_shipto_address2,
	invchead_shipto_address3,invchead_shipto_city,invchead_shipto_state,invchead_shipto_zipcode,invchead_shipto_phone,
	invchead_shipto_country,invchead_salesrep_id,invchead_commission,invchead_terms_id,invchead_freight,
	invchead_misc_amount,invchead_misc_descrip,invchead_misc_accnt_id,invchead_payment,
	invchead_paymentref,invchead_notes,invchead_prj_id,invchead_curr_id,
	invchead_taxzone_id, invchead_shipchrg_id,
        invchead_saletype_id, invchead_shipzone_id
   )
  SELECT 
	_invcheadid,cohead_cust_id,cohead_shipto_id,cohead_number,cohead_orderdate,
	FALSE,FALSE,cobmisc_invcnumber,cobmisc_invcdate,cobmisc_shipdate,
	cohead_custponumber,cobmisc_shipvia,cohead_fob,cohead_billtoname,cohead_billtoaddress1,
	cohead_billtoaddress2,cohead_billtoaddress3,cohead_billtocity,cohead_billtostate,cohead_billtozipcode,
	cntct_phone AS cust_phone,cohead_billtocountry,cohead_shiptoname,cohead_shiptoaddress1,cohead_shiptoaddress2,
	cohead_shiptoaddress3,cohead_shiptocity,cohead_shiptostate,cohead_shiptozipcode,cohead_shipto_cntct_phone,
	cohead_shiptocountry,cohead_salesrep_id,COALESCE(cohead_commission,0),cohead_terms_id,cobmisc_freight,
	COALESCE(cobmisc_misc, 0.00),cobmisc_misc_descrip,cobmisc_misc_accnt_id,cobmisc_payment,
	cobmisc_paymentref,cobmisc_notes,cohead_prj_id,cobmisc_curr_id,
	cobmisc_taxzone_id, cohead_shipchrg_id,
        cohead_saletype_id, cohead_shipzone_id
    FROM cobmisc, cohead, custinfo
    LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
  WHERE ( (cobmisc_cohead_id=cohead_id)
   AND (cohead_cust_id=cust_id)
   AND (cobmisc_id=pCobmiscid) );

	INSERT INTO invcheadtax(taxhist_parent_id, taxhist_taxtype_id, taxhist_tax_id, taxhist_basis, 
			taxhist_basis_tax_id, taxhist_sequence, taxhist_percent, taxhist_amount, taxhist_tax, taxhist_docdate)
        SELECT _invcheadid,taxhist_taxtype_id, taxhist_tax_id, taxhist_basis, 
			taxhist_basis_tax_id, taxhist_sequence, taxhist_percent, taxhist_amount, taxhist_tax, taxhist_docdate
        FROM cobmisctax 
	WHERE taxhist_parent_id = pCobmiscid 
	AND taxhist_taxtype_id = getadjustmenttaxtypeid();

--  Create the Invoice Characteristic Assignments
    INSERT INTO charass
          (charass_target_type, charass_target_id, charass_char_id, charass_value, charass_default, charass_price)
    SELECT 'INV', _invcheadid, charass_char_id, charass_value, charass_default, charass_price
      FROM cobmisc JOIN cohead ON (cohead_id=cobmisc_cohead_id)
                   JOIN charass ON ((charass_target_type='SO') AND (charass_target_id=cohead_id))
                   JOIN char ON (char_id=charass_char_id AND char_invoices)
    WHERE (cobmisc_id=pCobmiscid);

--  Create the Invoice items
  FOR _r IN SELECT coitem_id, coitem_linenumber, coitem_subnumber, coitem_custpn,
                   coitem_qtyord, cobill_qty,
                   coitem_qty_uom_id, coitem_qty_invuomratio,
                   coitem_custprice, coitem_price,
                   coitem_price_uom_id, coitem_price_invuomratio,
                   coitem_memo, coitem_rev_accnt_id,
                   itemsite_item_id, itemsite_warehous_id,
                   cobill_taxtype_id,
                   formatSoItemNumber(coitem_id) AS ordnumber
            FROM coitem, cobill, itemsite
            WHERE ( (cobill_coitem_id=coitem_id)
             AND (coitem_itemsite_id=itemsite_id)
             AND (cobill_cobmisc_id=pCobmiscid) )
            ORDER BY coitem_linenumber, coitem_subnumber LOOP

    SELECT NEXTVAL('invcitem_invcitem_id_seq') INTO _invcitemid;
    INSERT INTO invcitem
    ( invcitem_id, invcitem_invchead_id,
      invcitem_linenumber, invcitem_item_id, invcitem_warehous_id,
      invcitem_custpn, invcitem_number, invcitem_descrip,
      invcitem_ordered, invcitem_billed,
      invcitem_qty_uom_id, invcitem_qty_invuomratio,
      invcitem_custprice, invcitem_price,
      invcitem_price_uom_id, invcitem_price_invuomratio,
      invcitem_notes, invcitem_taxtype_id,
      invcitem_coitem_id, invcitem_rev_accnt_id )
    VALUES
    ( _invcitemid, _invcheadid,
      _lastlinenumber,
      _r.itemsite_item_id, _r.itemsite_warehous_id,
      _r.coitem_custpn, '', '',
      _r.coitem_qtyord, _r.cobill_qty,
      _r.coitem_qty_uom_id, _r.coitem_qty_invuomratio,
      _r.coitem_custprice, _r.coitem_price,
      _r.coitem_price_uom_id, _r.coitem_price_invuomratio,
      _r.coitem_memo, _r.cobill_taxtype_id,
      _r.coitem_id, _r.coitem_rev_accnt_id );

--  Find and mark any Lot/Serial invdetail records associated with this bill
    UPDATE invdetail SET invdetail_invcitem_id = _invcitemid
     WHERE (invdetail_id IN (SELECT invdetail_id
                               FROM invhist JOIN invdetail ON (invdetail_invhist_id=invhist_id)
                              WHERE ( (invhist_ordnumber = _r.ordnumber)
                                AND   (invhist_ordtype = 'SO')
                                AND   (invhist_transtype = 'SH')
                                AND   (invdetail_invcitem_id IS NULL) ) ));

--  Mark any shipped, uninvoiced shipitems for the current coitem as invoiced
    _qtyToInvoice :=  _r.cobill_qty;
    FOR _s IN SELECT shipitem.*, shipitem_qty = _r.cobill_qty AS matched
	      FROM shipitem, shiphead
	      WHERE ((shipitem_shiphead_id=shiphead_id)
	        AND  (shipitem_orderitem_id=_r.coitem_id)
	        AND  (shiphead_shipped)
		AND  (shiphead_order_type='SO')
	        AND  (NOT shipitem_invoiced))
	      ORDER BY matched DESC, shipitem_qty DESC FOR UPDATE LOOP
      IF (_qtyToInvoice >= _s.shipitem_qty) THEN
	UPDATE shipitem
	SET shipitem_invoiced=TRUE, shipitem_invcitem_id=_invcitemid
	WHERE (shipitem_id=_s.shipitem_id);
	_qtyToInvoice := _qtyToInvoice - _s.shipitem_qty;
      END IF;
      IF (_qtyToInvoice <= 0) THEN
	EXIT;
      END IF;
    END LOOP;

    UPDATE cobill SET cobill_invcnum=cobmisc_invcnumber,
		      cobill_invcitem_id=invcitem_id
    FROM invcitem, coitem, cobmisc
    WHERE ((invcitem_linenumber=_lastlinenumber)
      AND  (coitem_id=cobill_coitem_id)
      AND  (cobmisc_id=cobill_cobmisc_id)
      AND  (cobill_cobmisc_id=pCobmiscid)
      AND  (invcitem_invchead_id=_invcheadid));
    
    _lastlinenumber := _lastlinenumber + 1;

  END LOOP;

--  Close all requested coitem's
  IF ( ( SELECT cobmisc_closeorder
         FROM cobmisc
         WHERE (cobmisc_id=pCobmiscid) ) ) THEN
    UPDATE coitem
    SET coitem_status='C'
    FROM cobmisc
    WHERE ( (coitem_status NOT IN ('C', 'X'))
     AND (coitem_cohead_id=cobmisc_cohead_id)
     AND (cobmisc_id=pCobmiscid) );
  ELSE
    UPDATE coitem
    SET coitem_status='C'
    FROM cobill
    WHERE ( (cobill_coitem_id=coitem_id)
     AND (coitem_status <> 'X')
     AND (cobill_toclose)
     AND (cobill_cobmisc_id=pCobmiscid) );
  END IF;

--  Mark the cobmisc as posted
  UPDATE cobmisc
  SET cobmisc_posted=TRUE, cobmisc_invchead_id=_invcheadid
  WHERE (cobmisc_id=pCobmiscid);

--  All done
  RETURN _invcheadid;

END;
$_$;


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

--
-- TOC entry 1333 (class 1255 OID 146565886)
-- Dependencies: 4536 8
-- Name: createinvoiceconsolidated(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createinvoiceconsolidated(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCustid ALIAS FOR $1;
  _invcheadid INTEGER;
  _invcitemid INTEGER;
  _qtyToInvoice	NUMERIC;
  _r		RECORD;
  _s		RECORD;
  _c		RECORD;
  _i		RECORD;
  _count      INTEGER;
  _invcnumber INTEGER;
  _lastlinenumber INTEGER;

BEGIN
  _count := 0;

  FOR _c IN SELECT min(cobmisc_id) AS cobmisc_id, count(*) AS cnt,
-- there are the key values for consolidation
                   cohead_billtoname, cohead_billtoaddress1,
                   cohead_billtoaddress2, cohead_billtoaddress3,
                   cohead_billtocity, cohead_billtostate,
                   cohead_billtozipcode, cntct_phone AS cust_phone,
                   cohead_billtocountry,
                   cohead_salesrep_id, cohead_commission,
                   cohead_terms_id,
                   cobmisc_misc_accnt_id,
                   cohead_prj_id, cobmisc_curr_id,
                   cobmisc_taxzone_id,
                   cohead_shipchrg_id,
                   cohead_saletype_id,
                   cohead_shipzone_id,
                   
		-- the following are consolidated values to use in creating the header
                   MIN(cohead_number) AS cohead_number,
                   MIN(cohead_orderdate) AS cohead_orderdate,
                   MIN(cobmisc_invcdate) AS cobmisc_invcdate,
                   MIN(cobmisc_shipdate) AS cobmisc_shipdate,
                   SUM(cobmisc_freight) AS cobmisc_freight,
                   SUM(cobmisc_misc) AS cobmisc_misc,
                   SUM(cobmisc_payment) AS cobmisc_payment
                   
              FROM cobmisc
              JOIN cohead   ON (cobmisc_cohead_id=cohead_id)
              JOIN custinfo ON (cohead_cust_id=cust_id)
              LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
             WHERE(NOT cobmisc_posted
               AND (cohead_cust_id=pCustid)
               )
          GROUP BY cohead_billtoname, cohead_billtoaddress1,
                   cohead_billtoaddress2, cohead_billtoaddress3,
                   cohead_billtocity, cohead_billtostate,
                   cohead_billtozipcode, cust_phone,
                   cohead_billtocountry,
                   cohead_salesrep_id, cohead_commission,
                   cohead_terms_id,
                   cobmisc_misc_accnt_id,
                   cohead_prj_id, cobmisc_curr_id,
                   cobmisc_taxzone_id,
                   cohead_shipchrg_id,
                   cohead_saletype_id,
                   cohead_shipzone_id
		LOOP

    IF(_c.cnt = 1) THEN
      PERFORM createInvoice(_c.cobmisc_id);
      _count := (_count + 1);
    ELSE
      SELECT NEXTVAL('invchead_invchead_id_seq'), fetchInvcNumber() INTO _invcheadid, _invcnumber;
  
  --  Create the Invoice header
      INSERT INTO invchead
      ( invchead_id, invchead_cust_id, invchead_shipto_id,
        invchead_ordernumber, invchead_orderdate,
        invchead_posted, invchead_printed,
        invchead_invcnumber, invchead_invcdate, invchead_shipdate,
        invchead_ponumber, invchead_shipvia, invchead_fob,
        invchead_billto_name, invchead_billto_address1,
        invchead_billto_address2, invchead_billto_address3,
        invchead_billto_city, invchead_billto_state,
        invchead_billto_zipcode, invchead_billto_phone,
        invchead_billto_country,
        invchead_shipto_name, invchead_shipto_address1,
        invchead_shipto_address2, invchead_shipto_address3,
        invchead_shipto_city, invchead_shipto_state,
        invchead_shipto_zipcode, invchead_shipto_phone,
        invchead_shipto_country,
        invchead_salesrep_id, invchead_commission,
        invchead_terms_id,
        invchead_freight,
        invchead_misc_amount, invchead_misc_descrip, invchead_misc_accnt_id,
        invchead_payment, invchead_paymentref,
        invchead_notes, invchead_prj_id, invchead_curr_id,
        invchead_taxzone_id,
        invchead_shipchrg_id,
        invchead_saletype_id, invchead_shipzone_id )
      VALUES(_invcheadid,
             pCustid, -1,
             NULL, _c.cohead_orderdate,
             FALSE, FALSE,
             _invcnumber, _c.cobmisc_invcdate, _c.cobmisc_shipdate,
             'MULTIPLE', '', '',
             _c.cohead_billtoname, _c.cohead_billtoaddress1,
             _c.cohead_billtoaddress2, _c.cohead_billtoaddress3,
             _c.cohead_billtocity, _c.cohead_billtostate,
             _c.cohead_billtozipcode, _c.cust_phone,
             _c.cohead_billtocountry,
             '', '', '', '', '', '', '', '', '',
             _c.cohead_salesrep_id, COALESCE(_c.cohead_commission, 0),
             _c.cohead_terms_id,
             _c.cobmisc_freight,
             _c.cobmisc_misc, CASE WHEN(_c.cobmisc_misc <> 0) THEN 'Multiple' ELSE '' END,
             _c.cobmisc_misc_accnt_id,
             _c.cobmisc_payment, '',
             'Multiple Sales Order # Invoice', _c.cohead_prj_id, _c.cobmisc_curr_id,
             _c.cobmisc_taxzone_id,
             _c.cohead_shipchrg_id,
             _c.cohead_saletype_id, _c.cohead_shipzone_id
             );
 
    _lastlinenumber := 1;
    FOR _i IN SELECT cobmisc_id
                FROM cobmisc
                JOIN cohead   ON (cobmisc_cohead_id=cohead_id)
                JOIN custinfo ON (cohead_cust_id=cust_id)
                LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
               WHERE(NOT cobmisc_posted
                 AND (cohead_cust_id=pCustid)
                 AND (COALESCE(cohead_billtoname,'')         = COALESCE(_c.cohead_billtoname,''))
                 AND (COALESCE(cohead_billtoaddress1,'')     = COALESCE(_c.cohead_billtoaddress1,''))
                 AND (COALESCE(cohead_billtoaddress2,'')     = COALESCE(_c.cohead_billtoaddress2,''))
                 AND (COALESCE(cohead_billtoaddress3,'')     = COALESCE(_c.cohead_billtoaddress3,''))
                 AND (COALESCE(cohead_billtocity,'')         = COALESCE(_c.cohead_billtocity,''))
                 AND (COALESCE(cohead_billtostate,'')        = COALESCE(_c.cohead_billtostate,''))
                 AND (COALESCE(cohead_billtozipcode,'')      = COALESCE(_c.cohead_billtozipcode,''))
                 AND (COALESCE(cntct_phone,'')               = COALESCE(_c.cust_phone,''))
                 AND (COALESCE(cohead_billtocountry,'')      = COALESCE(_c.cohead_billtocountry,''))
                 AND (COALESCE(cohead_salesrep_id, 0)        = COALESCE(_c.cohead_salesrep_id, 0))
                 AND (COALESCE(cohead_commission, 0)         = COALESCE(_c.cohead_commission, 0))
                 AND (COALESCE(cohead_terms_id, 0)           = COALESCE(_c.cohead_terms_id, 0))
                 AND (COALESCE(cobmisc_misc_accnt_id, 0)     = COALESCE(_c.cobmisc_misc_accnt_id, 0))
                 AND (COALESCE(cohead_prj_id, 0)             = COALESCE(_c.cohead_prj_id, 0))
                 AND (COALESCE(cobmisc_curr_id, 0)           = COALESCE(_c.cobmisc_curr_id, 0))
                 AND (COALESCE(cobmisc_taxzone_id, 0)        = COALESCE(_c.cobmisc_taxzone_id, 0))
                 AND (COALESCE(cohead_saletype_id, 0)        = COALESCE(_c.cohead_saletype_id, 0))
                 AND (COALESCE(cohead_shipzone_id, 0)        = COALESCE(_c.cohead_shipzone_id, 0))
                ) LOOP

    --  Create the Invoice Head tax
        INSERT INTO invcheadtax(taxhist_parent_id, taxhist_taxtype_id, taxhist_tax_id, taxhist_basis, 
                                taxhist_basis_tax_id, taxhist_sequence, taxhist_percent, taxhist_amount, taxhist_tax, taxhist_docdate)
        SELECT _invcheadid,taxhist_taxtype_id, taxhist_tax_id, taxhist_basis, 
               taxhist_basis_tax_id, taxhist_sequence, taxhist_percent, taxhist_amount, taxhist_tax, taxhist_docdate
        FROM cobmisctax 
        WHERE taxhist_parent_id = _i.cobmisc_id
          AND taxhist_taxtype_id = getadjustmenttaxtypeid();

    --  Give this selection a number if it has not been assigned one
        UPDATE cobmisc
           SET cobmisc_invcnumber=_invcnumber
         WHERE(cobmisc_id=_i.cobmisc_id);
      
    --  Create the Invoice items
        FOR _r IN SELECT coitem_id, coitem_linenumber, coitem_subnumber, coitem_custpn,
                         coitem_qtyord, cobill_qty,
                         coitem_qty_uom_id, coitem_qty_invuomratio,
                         coitem_custprice, coitem_price,
                         coitem_price_uom_id, coitem_price_invuomratio,
                         coitem_memo,
                         itemsite_item_id, itemsite_warehous_id,
                         cobill_taxtype_id
                    FROM cohead, coitem, cobill, itemsite
                   WHERE((cobill_coitem_id=coitem_id)
                     AND (cohead_id=coitem_cohead_id)
                     AND (coitem_itemsite_id=itemsite_id)
                     AND (cobill_cobmisc_id=_i.cobmisc_id) ) 
                   ORDER BY cohead_number, coitem_linenumber, coitem_subnumber LOOP
      
          SELECT NEXTVAL('invcitem_invcitem_id_seq') INTO _invcitemid;
          INSERT INTO invcitem
          ( invcitem_id, invcitem_invchead_id,
            invcitem_linenumber, invcitem_item_id, invcitem_warehous_id,
            invcitem_custpn, invcitem_number, invcitem_descrip,
            invcitem_ordered, invcitem_billed,
            invcitem_qty_uom_id, invcitem_qty_invuomratio,
            invcitem_custprice, invcitem_price,
            invcitem_price_uom_id, invcitem_price_invuomratio,
            invcitem_notes,
            invcitem_taxtype_id,
            invcitem_coitem_id )
          VALUES
          ( _invcitemid, _invcheadid,
            _lastlinenumber,
            _r.itemsite_item_id, _r.itemsite_warehous_id,
            _r.coitem_custpn, '', '',
            _r.coitem_qtyord, _r.cobill_qty,
            _r.coitem_qty_uom_id, _r.coitem_qty_invuomratio,
            _r.coitem_custprice, _r.coitem_price,
            _r.coitem_price_uom_id, _r.coitem_price_invuomratio,
            _r.coitem_memo,
            _r.cobill_taxtype_id,
            _r.coitem_id );
          
      --  Find and mark any Lot/Serial invdetail records associated with this bill
          UPDATE invdetail SET invdetail_invcitem_id = _invcitemid
           WHERE (invdetail_id IN (SELECT invdetail_id
                                     FROM coitem, cohead, invhist, invdetail
                                    WHERE ((coitem_cohead_id=cohead_id)
                                      AND  (invdetail_invhist_id=invhist_id)
                                      AND  (invhist_ordnumber = text(cohead_number||'-'||formatSoLineNumber(coitem_id)))
                                      AND  (invdetail_invcitem_id IS NULL)
                                      AND  (coitem_id=_r.coitem_id)) ) );
      
      --  Mark any shipped, uninvoiced shipitems for the current coitem as invoiced
          _qtyToInvoice :=  _r.cobill_qty;
          FOR _s IN SELECT shipitem.*, shipitem_qty = _r.cobill_qty AS matched
	            FROM shipitem, shiphead
	            WHERE ((shipitem_shiphead_id=shiphead_id)
	              AND  (shipitem_orderitem_id=_r.coitem_id)
	              AND  (shiphead_shipped)
		      AND  (shiphead_order_type='SO')
	              AND  (NOT shipitem_invoiced))
	            ORDER BY matched DESC, shipitem_qty DESC FOR UPDATE LOOP
            IF (_qtyToInvoice >= _s.shipitem_qty) THEN
	      UPDATE shipitem
	      SET shipitem_invoiced=TRUE, shipitem_invcitem_id=_invcitemid
	      WHERE (shipitem_id=_s.shipitem_id);
	      _qtyToInvoice := _qtyToInvoice - _s.shipitem_qty;
            END IF;
            IF (_qtyToInvoice <= 0) THEN
	      EXIT;
            END IF;
          END LOOP;

          UPDATE cobill SET cobill_invcnum=cobmisc_invcnumber,
		          cobill_invcitem_id=invcitem_id
          FROM invcitem, coitem, cobmisc
          WHERE ((invcitem_linenumber=_lastlinenumber )
            AND  (coitem_id=cobill_coitem_id)
            AND  (cobmisc_id=cobill_cobmisc_id)
            AND  (cobill_cobmisc_id=_i.cobmisc_id)
            AND  (invcitem_invchead_id=_invcheadid));


          _lastlinenumber := _lastlinenumber + 1;
          
        END LOOP;
  
      --  Close all requested coitem's
        IF ( ( SELECT cobmisc_closeorder
               FROM cobmisc
               WHERE (cobmisc_id=_i.cobmisc_id) ) ) THEN
          UPDATE coitem
          SET coitem_status='C'
          FROM cobmisc
          WHERE ( (coitem_status NOT IN ('C', 'X'))
           AND (coitem_cohead_id=cobmisc_cohead_id)
           AND (cobmisc_id=_i.cobmisc_id) );
        ELSE
          UPDATE coitem
          SET coitem_status='C'
          FROM cobill
          WHERE ( (cobill_coitem_id=coitem_id)
           AND (coitem_status <> 'X')
           AND (cobill_toclose)
           AND (cobill_cobmisc_id=_i.cobmisc_id) );
        END IF;
      
      --  Mark the cobmisc as posted
        UPDATE cobmisc
        SET cobmisc_posted=TRUE, cobmisc_invchead_id=_invcheadid
        WHERE (cobmisc_id=_i.cobmisc_id);
    
      --  All done
        _count := (_count + 1);
      END LOOP;
    END IF;
  END LOOP;
  RETURN _count;
END;
$_$;


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

--
-- TOC entry 1334 (class 1255 OID 146565888)
-- Dependencies: 4536 8
-- Name: createinvoices(); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  _counter := 0;

  FOR _cobmisc IN SELECT cobmisc_id
                  FROM cobmisc
                  WHERE (NOT cobmisc_posted) LOOP

    PERFORM createinvoice(_cobmisc.cobmisc_id);
    _counter := (_counter + 1);

  END LOOP;

  RETURN _counter;
END;
$$;


ALTER FUNCTION public.createinvoices() OWNER TO admin;

--
-- TOC entry 1335 (class 1255 OID 146565889)
-- Dependencies: 4536 8
-- Name: createinvoices(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createinvoices(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 createinvoices($1, false);
END;
$_$;


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

--
-- TOC entry 1336 (class 1255 OID 146565890)
-- Dependencies: 4536 8
-- Name: createinvoices(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  _counter := 0;

  IF (pConsolidate) THEN
    FOR _cobmisc IN SELECT DISTINCT cust_id
                      FROM cobmisc, cohead, custinfo
                     WHERE((NOT cobmisc_posted)
                       AND (cohead_id=cobmisc_cohead_id)
                       AND (cust_id=cohead_cust_id)
                       AND (cust_custtype_id=pCustTypeId)) LOOP

      SELECT createinvoiceConsolidated(_cobmisc.cust_id)
        INTO _tcounter;
      _counter := (_counter + _tcounter);
    END LOOP;
  ELSE
    FOR _cobmisc IN SELECT cobmisc_id
                      FROM cobmisc, cohead, custinfo
                     WHERE((NOT cobmisc_posted)
                       AND (cohead_id=cobmisc_cohead_id)
                       AND (cust_id=cohead_cust_id)
                       AND (cust_custtype_id=pCustTypeId)) LOOP
  
      PERFORM createinvoice(_cobmisc.cobmisc_id);
      _counter := (_counter + 1);

    END LOOP;
  END IF;

  RETURN _counter;
END;
$_$;


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

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

CREATE FUNCTION createmiscapcheck(integer, integer, date, numeric, integer, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RAISE NOTICE 'createMiscAPCheck() is deprecated - use createCheck() instead';
  RETURN createCheck($1, 'V', $2, $3, $4, baseCurrId(), $5, NULL, $6, $7, FALSE);
END;
$_$;


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

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

CREATE FUNCTION createmiscapcheck(integer, integer, date, numeric, integer, integer, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RAISE NOTICE 'createMiscAPCheck() is deprecated - use createCheck() instead';
  RETURN createCheck($1, 'V', $2, $3, pAmount, $5, $6, NULL, $7, $8, FALSE);
END;
$_$;


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

--
-- TOC entry 1339 (class 1255 OID 146565893)
-- Dependencies: 4536 8
-- Name: createpkgschema(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createpkgschema(text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pname         ALIAS FOR $1;
  pcomment      ALIAS FOR $2;
  _createtable  TEXT;
  _debug        BOOL    := true;
  _namespaceoid INTEGER := -1;
  _tabs         TEXT[] := ARRAY['cmd',  'cmdarg', 'image',  'metasql',
                                'priv', 'report', 'script', 'uiform'] ;
  _pkgtab       TEXT;

BEGIN
  IF (LENGTH(COALESCE(pname, '')) <= 0) THEN
    RAISE EXCEPTION 'Cannot create a schema for this package without a name.';
  END IF;

  SELECT oid INTO _namespaceoid
  FROM pg_namespace
  WHERE (LOWER(nspname)=LOWER(pname));
  IF (NOT FOUND) THEN
    EXECUTE 'CREATE SCHEMA ' || LOWER(pname);
    EXECUTE 'GRANT ALL ON SCHEMA ' || LOWER(pname) || ' TO GROUP xtrole;';

    SELECT oid INTO _namespaceoid
    FROM pg_namespace
    WHERE (LOWER(nspname)=LOWER(pname));
  END IF;

  FOR i IN ARRAY_LOWER(_tabs,1)..ARRAY_UPPER(_tabs,1) LOOP
    _pkgtab := pname || '.pkg' || _tabs[i];

    IF NOT EXISTS(SELECT oid
                  FROM pg_class
                  WHERE ((relname=_pkgtab)
                     AND (relnamespace=_namespaceoid))) THEN
      _createtable := 'CREATE TABLE ' || _pkgtab || ' () INHERITS (' || _tabs[i] || ');';
      IF (_debug) THEN RAISE NOTICE '%', _createtable; END IF;
      EXECUTE _createtable;

      EXECUTE 'ALTER TABLE ' || _pkgtab ||
              ' ALTER ' || _tabs[i] || '_id SET NOT NULL,' ||
              ' ADD PRIMARY KEY (' || _tabs[i] || '_id),' ||
              ' ALTER ' || _tabs[i] || '_id SET DEFAULT NEXTVAL(''' ||
              _tabs[i] || '_' || _tabs[i] || '_id_seq'');';

      EXECUTE 'REVOKE ALL ON ' || _pkgtab || ' FROM PUBLIC;';
      EXECUTE 'GRANT  ALL ON ' || _pkgtab || ' TO GROUP xtrole;';

      IF (_tabs[i] = 'cmdarg') THEN
        EXECUTE 'ALTER TABLE ' || _pkgtab ||
                ' ADD FOREIGN KEY (cmdarg_cmd_id) REFERENCES ' ||
                pname || '.pkgcmd(cmd_id);';
      END IF;

      EXECUTE 'SELECT dropIfExists(''TRIGGER'', ''pkg' ||
                                   _tabs[i] || 'beforetrigger'', ''' ||
                                   pname || ''');' ;
      EXECUTE 'CREATE TRIGGER pkg' || _tabs[i] || 'beforetrigger ' ||
              'BEFORE INSERT OR UPDATE OR DELETE ON ' || _pkgtab ||
              ' FOR EACH ROW EXECUTE PROCEDURE _pkg' || _tabs[i] || 'beforetrigger();';

      EXECUTE 'SELECT dropIfExists(''TRIGGER'', ''pkg' ||
                                   _tabs[i] || 'altertrigger'', ''' ||
                                   pname || ''');' ;
      EXECUTE 'CREATE TRIGGER pkg' || _tabs[i] || 'altertrigger ' ||
              'BEFORE INSERT OR UPDATE OR DELETE ON ' || _pkgtab ||
              ' FOR EACH ROW EXECUTE PROCEDURE _pkg' || _tabs[i] || 'altertrigger();';

      EXECUTE 'SELECT dropIfExists(''TRIGGER'', ''pkg' ||
                                   _tabs[i] || 'aftertrigger'', ''' ||
                                   pname || ''');' ;
      EXECUTE 'CREATE TRIGGER pkg' || _tabs[i] || 'aftertrigger ' ||
              'AFTER INSERT OR UPDATE OR DELETE ON ' || _pkgtab ||
              ' FOR EACH ROW EXECUTE PROCEDURE _pkg' || _tabs[i] || 'aftertrigger();';

    END IF;
  END LOOP;

  EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(pname) || ' IS ' ||
           quote_literal(pcomment) || ';';

  RETURN _namespaceoid;
END;
$_$;


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

--
-- TOC entry 1341 (class 1255 OID 146565896)
-- Dependencies: 4536 8
-- Name: createpr(character, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF (pParentType = 'W') THEN
    SELECT wo_number INTO _orderNumber
    FROM wo, womatl
    WHERE ((womatl_wo_id=wo_id)
     AND (womatl_id=pParentId));

  ELSIF (pParentType = 'S') THEN
    SELECT CAST(cohead_number AS INTEGER) INTO _orderNumber
    FROM cohead, coitem
    WHERE ((coitem_cohead_id=cohead_id)
     AND (coitem_id=pParentId));

  ELSIF (pParentType = 'F') THEN
    SELECT fetchPrNumber() INTO _orderNumber;

  ELSE
    RETURN -2;
  END IF;

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

  SELECT createPr(_orderNumber, pParentType, pParentId) INTO _prid;

  RETURN _prid;

END;
$_$;


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

--
-- TOC entry 1340 (class 1255 OID 146565895)
-- Dependencies: 4536 8
-- Name: createpr(integer, character, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createpr(integer, character, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pOrderNumber ALIAS FOR $1;
  pParentType ALIAS FOR $2;
  pParentId ALIAS FOR $3;
  _parent RECORD;
  _prid INTEGER;
  _orderNumber INTEGER;

BEGIN

  IF (pOrderNumber = -1) THEN
    SELECT fetchPrNumber() INTO _orderNumber;
  ELSE
    _orderNumber := pOrderNumber;
  END IF;

  IF (pParentType = 'W') THEN
    SELECT womatl_itemsite_id AS itemsiteid,
           itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq) AS qty,
           womatl_duedate AS duedate, wo_prj_id AS prjid,
           womatl_notes AS notes INTO _parent
    FROM wo, womatl, itemsite
    WHERE ((womatl_wo_id=wo_id)
     AND (womatl_itemsite_id=itemsite_id)
     AND (womatl_id=pParentId));

  ELSIF (pParentType = 'S') THEN
    SELECT coitem_itemsite_id AS itemsiteid,
           (coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS qty,
           coitem_scheddate AS duedate, cohead_prj_id AS prjid,
           coitem_memo AS notes INTO _parent
    FROM coitem, cohead
    WHERE ((cohead_id=coitem_cohead_id)
     AND (coitem_id=pParentId));

  ELSIF (pParentType = 'F') THEN
    SELECT planord_itemsite_id AS itemsiteid,
           planord_qty AS qty,
           planord_duedate AS duedate, NULL::INTEGER AS prjid,
           planord_comments AS notes INTO _parent
    FROM planord
    WHERE (planord_id=pParentId);

  ELSE
    RETURN -2;
  END IF;

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

  SELECT NEXTVAL('pr_pr_id_seq') INTO _prid;
  INSERT INTO pr
  ( pr_id, pr_number, pr_subnumber, pr_status,
    pr_order_type, pr_order_id, pr_prj_id,
    pr_itemsite_id, pr_qtyreq,
    pr_duedate, pr_releasenote )
  VALUES
  ( _prid, _orderNumber, nextPrSubnumber(_orderNumber), 'O',
    pParentType, pParentId, _parent.prjid,
    _parent.itemsiteid, validateOrderQty(_parent.itemsiteid, _parent.qty, TRUE),
    _parent.duedate, _parent.notes );

  RETURN _prid;

END;
$_$;


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

--
-- TOC entry 1343 (class 1255 OID 146565898)
-- Dependencies: 4536 8
-- Name: createpr(integer, character, integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createpr(integer, character, integer, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pOrderNumber ALIAS FOR $1;
  pParentType ALIAS FOR $2;
  pParentId ALIAS FOR $3;
  pParentNotes ALIAS FOR $4;
  _parent RECORD;
  _prid INTEGER;
  _orderNumber INTEGER;

BEGIN

  IF (pOrderNumber = -1) THEN
    SELECT fetchPrNumber() INTO _orderNumber;
  ELSE
    _orderNumber := pOrderNumber;
  END IF;

  IF (pParentType = 'W') THEN
    SELECT womatl_itemsite_id AS itemsiteid,
           itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq) AS qty,
           womatl_duedate AS duedate, wo_prj_id AS prjid INTO _parent
    FROM wo, womatl, itemsite
    WHERE ((womatl_wo_id=wo_id)
     AND (womatl_itemsite_id=itemsite_id)
     AND (womatl_id=pParentId));

  ELSIF (pParentType = 'S') THEN
    SELECT coitem_itemsite_id AS itemsiteid,
           (coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS qty,
           coitem_scheddate AS duedate, cohead_prj_id AS prjid INTO _parent
    FROM coitem, cohead
    WHERE ((cohead_id=coitem_cohead_id)
     AND (coitem_id=pParentId));

  ELSIF (pParentType = 'F') THEN
    SELECT planord_itemsite_id AS itemsiteid,
           planord_qty AS qty,
           planord_duedate AS duedate, NULL::INTEGER AS prjid 
           INTO _parent
    FROM planord
    WHERE (planord_id=pParentId);

  ELSE
    RETURN -2;
  END IF;

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

  SELECT NEXTVAL('pr_pr_id_seq') INTO _prid;
  INSERT INTO pr
  ( pr_id, pr_number, pr_subnumber, pr_status,
    pr_order_type, pr_order_id, pr_prj_id,
    pr_itemsite_id, pr_qtyreq, pr_duedate, pr_releasenote )
  VALUES
  ( _prid, _orderNumber, nextPrSubnumber(_orderNumber), 'O',
    pParentType, pParentId, _parent.prjid,
    _parent.itemsiteid, validateOrderQty(_parent.itemsiteid, _parent.qty, TRUE),
    _parent.duedate, pParentNotes );

  RETURN _prid;

END;
$_$;


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

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

CREATE FUNCTION createpr(integer, 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
  pOrderNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pQty ALIAS FOR $3;
  pDueDate ALIAS FOR $4;
  pNotes ALIAS FOR $5;
  _prid INTEGER;

BEGIN

  SELECT NEXTVAL('pr_pr_id_seq') INTO _prid;
  INSERT INTO pr
  ( pr_id, pr_number, pr_subnumber, pr_status,
    pr_order_type, pr_order_id,
    pr_itemsite_id, pr_qtyreq, pr_duedate, pr_releasenote )
  VALUES
  ( _prid, pOrderNumber, nextPrSubnumber(pOrderNumber), 'O',
    'M', -1,
    pItemsiteid, pQty, pDuedate, pNotes);

  RETURN _prid;

END;
$_$;


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

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

CREATE FUNCTION createpr(integer, integer, numeric, date, text, character, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pOrderNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pQty ALIAS FOR $3;
  pDueDate ALIAS FOR $4;
  pNotes ALIAS FOR $5;
  pOrderType ALIAS FOR $6;
  pOrderId ALIAS FOR $7;
  _prid INTEGER;

BEGIN

  SELECT NEXTVAL('pr_pr_id_seq') INTO _prid;
  INSERT INTO pr
  ( pr_id, pr_number, pr_subnumber, pr_status,
    pr_order_type, pr_order_id,
    pr_itemsite_id, pr_qtyreq, pr_duedate, pr_releasenote )
  VALUES
  ( _prid, pOrderNumber, nextPrSubnumber(pOrderNumber), 'O',
    pOrderType, pOrderId,
    pItemsiteid, pQty, pDuedate, pNotes );

  RETURN _prid;

END;
$_$;


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

--
-- TOC entry 1344 (class 1255 OID 146565899)
-- Dependencies: 4536 8
-- Name: createpriv(text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

  SELECT priv_id
    INTO _id
    FROM priv
   WHERE(priv_name=pName);

  IF (FOUND) THEN
    UPDATE priv
       SET priv_module=pModule,
           priv_descrip=pDesc
     WHERE(priv_id=_id);
  ELSE
    SELECT nextval('priv_priv_id_seq') INTO _id;
    INSERT INTO priv
          (priv_id, priv_module, priv_name, priv_descrip)
    VALUES(_id, pModule, pName, pDesc);
  END IF;

  RETURN _id;
END;
$_$;


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

--
-- TOC entry 1345 (class 1255 OID 146565900)
-- Dependencies: 4536 8
-- Name: createpurchasetosale(integer, integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createpurchasetosale(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
  pCoitemId ALIAS FOR $1;
  pItemSourceId ALIAS FOR $2;
  pDropShip ALIAS FOR $3;

BEGIN

  RETURN createPurchaseToSale(pCoitemId, pItemSourceId, pDropShip, NULL, NULL, NULL);

END;
$_$;


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

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

CREATE FUNCTION createpurchasetosale(integer, integer, boolean, 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
  pCoitemId ALIAS FOR $1;
  pItemSourceId ALIAS FOR $2;
  pDropShip ALIAS FOR $3;
  pPrice ALIAS FOR $4;

BEGIN

  RETURN createPurchaseToSale(pCoitemId, pItemSourceId, pDropShip, NULL, NULL, pPrice);

END;
$_$;


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

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

CREATE FUNCTION createpurchasetosale(integer, integer, boolean, numeric, date, numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCoitemId ALIAS FOR $1;
  pItemSourceId ALIAS FOR $2;
  pDropShip ALIAS FOR $3;
  pQty ALIAS FOR $4;
  pDueDate ALIAS FOR $5;
  pPrice ALIAS FOR $6;

  _s RECORD;
  _w RECORD;
  _i RECORD;
  _shipto RECORD;
  _poheadid INTEGER := -1;
  _poitemid INTEGER := -1;
  _taxtypeid INTEGER := -1;
  _polinenumber INTEGER;
  _ponumber NUMERIC;
  _price NUMERIC;
  _temp INTEGER;

BEGIN

  -- Check for existing poitem for this coitem
  SELECT poitem_id INTO _poitemid
  FROM poitem
  WHERE (poitem_order_id=pCoitemId)
    AND (poitem_order_type='S');
  IF (FOUND) THEN
    RETURN _poitemid;
  END IF;

  SELECT *,
         COALESCE(roundQty(item_fractional, (coitem_qtyord * coitem_qty_invuomratio)), 0.0) AS orderqty
  INTO _s
  FROM coitem JOIN cohead ON (cohead_id = coitem_cohead_id)
              LEFT OUTER JOIN shiptoinfo ON (cohead_shipto_id = shipto_id)
              LEFT OUTER JOIN addr ON (shipto_addr_id = addr_id)
              LEFT OUTER JOIN cntct ON (shipto_cntct_id = cntct_id)
              LEFT OUTER JOIN itemsite ON (coitem_itemsite_id = itemsite_id)
              LEFT OUTER JOIN item ON (item_id = itemsite_item_id)
  WHERE (coitem_id = pCoitemId);
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  SELECT * INTO _w
  FROM itemsite JOIN whsinfo ON (warehous_id = itemsite_warehous_id)
                LEFT OUTER JOIN addr ON (warehous_addr_id = addr_id)
                LEFT OUTER JOIN cntct ON (warehous_cntct_id = cntct_id)
  WHERE (itemsite_id = _s.itemsite_id);

  SELECT * INTO _i
  FROM itemsrc JOIN vendinfo ON (itemsrc_vend_id = vend_id)
               LEFT OUTER JOIN cntct ON (vend_cntct1_id = cntct_id)
               LEFT OUTER JOIN addr ON (vend_addr_id = addr_id)
  WHERE (itemsrc_id = pItemSourceId);
  IF (NOT FOUND) THEN
    RETURN -2;
  END IF;

  IF (pDropShip) THEN
    SELECT COALESCE(pohead_id, -1) INTO _temp
    FROM pohead
    WHERE ( (pohead_status = 'U')
      AND (pohead_vend_id = _i.itemsrc_vend_id)
      AND (pohead_shiptoname = COALESCE(_s.cohead_shiptoname, _s.shipto_name, ''))
      AND (pohead_shiptoaddress1 = COALESCE(_s.cohead_shiptoaddress1, _s.addr_line1, ''))
      AND (pohead_shiptoaddress2 = COALESCE(_s.cohead_shiptoaddress2, _s.addr_line2, ''))
      AND (pohead_shiptoaddress3 = COALESCE(_s.cohead_shiptoaddress3, _s.addr_line3, ''))
      AND (pohead_shiptocity = COALESCE(_s.cohead_shiptocity, _s.addr_city, ''))
      AND (pohead_shiptostate = COALESCE(_s.cohead_shiptostate, _s.addr_state, ''))
      AND (pohead_shiptozipcode = COALESCE(_s.cohead_shiptozipcode, _s.addr_postalcode, ''))
      AND (pohead_shiptocountry = COALESCE(_s.cohead_shiptocountry, _s.addr_country, '')) );
  ELSE
    SELECT COALESCE(pohead_id, -1) INTO _temp
    FROM pohead
    WHERE ( (pohead_status = 'U')
      AND (pohead_vend_id = _i.itemsrc_vend_id)
      AND (pohead_shiptoaddress1 = COALESCE(_w.addr_line1, ''))
      AND (pohead_shiptoaddress2 = COALESCE(_w.addr_line2, ''))
      AND (pohead_shiptoaddress3 = COALESCE(_w.addr_line3, ''))
      AND (pohead_shiptocity = COALESCE(_w.addr_city, ''))
      AND (pohead_shiptostate = COALESCE(_w.addr_state, ''))
      AND (pohead_shiptozipcode = COALESCE(_w.addr_postalcode, ''))
      AND (pohead_shiptocountry = COALESCE(_w.addr_country, '')) );
  END IF;

  IF (FOUND) THEN
    _poheadid := _temp;
    UPDATE pohead
    SET pohead_dropship = pDropShip
    WHERE (pohead_id = _poheadid);
  ELSE
    SELECT NEXTVAL('pohead_pohead_id_seq') INTO _poheadid;
    SELECT fetchPoNumber() INTO _ponumber;

    IF (pDropShip) THEN
      INSERT INTO pohead
        ( pohead_id, pohead_number, pohead_status, pohead_dropship,
          pohead_agent_username, pohead_vend_id, pohead_taxzone_id,
          pohead_orderdate, pohead_curr_id, pohead_cohead_id,
          pohead_warehous_id, pohead_shipvia,
          pohead_terms_id, pohead_shipto_cntct_id,
          pohead_shipto_cntct_honorific, pohead_shipto_cntct_first_name,
          pohead_shipto_cntct_middle, pohead_shipto_cntct_last_name,
          pohead_shipto_cntct_suffix, pohead_shipto_cntct_phone,
          pohead_shipto_cntct_title, pohead_shipto_cntct_fax, 
          pohead_shipto_cntct_email, pohead_shiptoaddress_id,
          pohead_shiptoname,
          pohead_shiptoaddress1,
          pohead_shiptoaddress2,
          pohead_shiptoaddress3,
          pohead_shiptocity, 
          pohead_shiptostate, pohead_shiptozipcode,
          pohead_shiptocountry, pohead_vend_cntct_id,
          pohead_vend_cntct_honorific, pohead_vend_cntct_first_name,
          pohead_vend_cntct_middle, pohead_vend_cntct_last_name,
          pohead_vend_cntct_suffix, pohead_vend_cntct_phone,
          pohead_vend_cntct_title, pohead_vend_cntct_fax,
          pohead_vend_cntct_email, pohead_vendaddress1,
          pohead_vendaddress2, pohead_vendaddress3,
          pohead_vendcity, pohead_vendstate,
          pohead_vendzipcode, pohead_vendcountry, pohead_comments )
      VALUES
        ( _poheadid, _ponumber, 'U', pDropShip,
          getEffectiveXtUser(), _i.itemsrc_vend_id, _i.vend_taxzone_id,
	  CURRENT_DATE, COALESCE(_i.vend_curr_id, basecurrid()), _s.cohead_id,
          COALESCE(_s.cohead_warehous_id, -1), COALESCE(_i.vend_shipvia, TEXT('')),
          COALESCE(_i.vend_terms_id, -1), COALESCE(_s.cohead_shipto_cntct_id, _s.shipto_cntct_id),
          COALESCE(_s.cohead_shipto_cntct_honorific, _s.cntct_honorific), COALESCE(_s.cohead_shipto_cntct_first_name, _s.cntct_first_name),
          COALESCE(_s.cohead_shipto_cntct_middle, _s.cntct_middle), COALESCE(_s.cohead_shipto_cntct_last_name, _s.cntct_last_name),
          COALESCE(_s.cohead_shipto_cntct_suffix, _s.cntct_suffix), COALESCE(_s.cohead_shipto_cntct_phone, _s.cntct_phone),
          COALESCE(_s.cohead_shipto_cntct_title, _s.cntct_title), COALESCE(_s.cohead_shipto_cntct_fax, _s.cntct_fax),
          COALESCE(_s.cohead_shipto_cntct_email, _s.cntct_email), COALESCE(_s.shipto_addr_id, _s.addr_id),
          COALESCE(_s.cohead_shiptoname, _s.shipto_name, ''),
          COALESCE(_s.cohead_shiptoaddress1, _s.addr_line1, ''),
          COALESCE(_s.cohead_shiptoaddress2, _s.addr_line2, ''),
          COALESCE(_s.cohead_shiptoaddress3, _s.addr_line3, ''),
          COALESCE(_s.cohead_shiptocity, _s.addr_city, ''),
          COALESCE(_s.cohead_shiptostate, _s.addr_state, ''), COALESCE(_s.cohead_shiptozipcode, _s.addr_postalcode, ''),
          COALESCE(_s.cohead_shiptocountry, _s.addr_country, ''), _i.cntct_id,
          COALESCE(_i.cntct_honorific, TEXT('')), COALESCE(_i.cntct_first_name, TEXT('')),
          COALESCE(_i.cntct_middle, TEXT('')), COALESCE(_i.cntct_last_name, TEXT('')),
          COALESCE(_i.cntct_suffix, TEXT('')), COALESCE(_i.cntct_phone, TEXT('')),
          COALESCE(_i.cntct_title, TEXT('')), COALESCE(_i.cntct_fax, TEXT('')),
          COALESCE(_i.cntct_email, TEXT('')), COALESCE(_i.addr_line1, TEXT('')),
          COALESCE(_i.addr_line2, TEXT('')), COALESCE(_i.addr_line3, TEXT('')),
          COALESCE(_i.addr_city, TEXT('')), COALESCE(_i.addr_state, TEXT('')),
          COALESCE(_i.addr_postalcode, TEXT('')), COALESCE(_i.addr_country, TEXT('')), COALESCE(_s.cohead_shipcomments, TEXT('')) );
    ELSE
      INSERT INTO pohead
        ( pohead_id, pohead_number, pohead_status, pohead_dropship,
          pohead_agent_username, pohead_vend_id, pohead_taxzone_id,
          pohead_orderdate, pohead_curr_id, pohead_cohead_id,
          pohead_warehous_id, pohead_shipvia,
          pohead_terms_id, pohead_shipto_cntct_id,
          pohead_shipto_cntct_honorific, pohead_shipto_cntct_first_name,
          pohead_shipto_cntct_middle, pohead_shipto_cntct_last_name,
          pohead_shipto_cntct_suffix, pohead_shipto_cntct_phone,
          pohead_shipto_cntct_title, pohead_shipto_cntct_fax, 
          pohead_shipto_cntct_email, pohead_shiptoaddress_id,
          pohead_shiptoaddress1,
          pohead_shiptoaddress2,
          pohead_shiptoaddress3,
          pohead_shiptocity, 
          pohead_shiptostate, pohead_shiptozipcode,
          pohead_shiptocountry, pohead_vend_cntct_id,
          pohead_vend_cntct_honorific, pohead_vend_cntct_first_name,
          pohead_vend_cntct_middle, pohead_vend_cntct_last_name,
          pohead_vend_cntct_suffix, pohead_vend_cntct_phone,
          pohead_vend_cntct_title, pohead_vend_cntct_fax,
          pohead_vend_cntct_email, pohead_vendaddress1,
          pohead_vendaddress2, pohead_vendaddress3,
          pohead_vendcity, pohead_vendstate,
          pohead_vendzipcode, pohead_vendcountry )
      VALUES
        ( _poheadid, _ponumber, 'U', pDropShip,
          getEffectiveXtUser(), _i.itemsrc_vend_id, _i.vend_taxzone_id,
	  CURRENT_DATE, COALESCE(_i.vend_curr_id, basecurrid()), _s.cohead_id,
          COALESCE(_s.cohead_warehous_id, -1), COALESCE(_i.vend_shipvia, TEXT('')),
          COALESCE(_i.vend_terms_id, -1), _w.cntct_id,
          _w.cntct_honorific, _w.cntct_first_name,
          _w.cntct_middle, _w.cntct_last_name,
          _w.cntct_suffix, _w.cntct_phone,
          _w.cntct_title, _w.cntct_fax,
          _w.cntct_email, _w.addr_id,
          COALESCE(_w.addr_line1, ''),
          COALESCE(_w.addr_line2, ''),
          COALESCE(_w.addr_line3, ''),
          COALESCE(_w.addr_city, ''),
          COALESCE(_w.addr_state, ''), COALESCE(_w.addr_postalcode, ''),
          COALESCE(_w.addr_country, ''), _i.cntct_id,
          COALESCE(_i.cntct_honorific, TEXT('')), COALESCE(_i.cntct_first_name, TEXT('')),
          COALESCE(_i.cntct_middle, TEXT('')), COALESCE(_i.cntct_last_name, TEXT('')),
          COALESCE(_i.cntct_suffix, TEXT('')), COALESCE(_i.cntct_phone, TEXT('')),
          COALESCE(_i.cntct_title, TEXT('')), COALESCE(_i.cntct_fax, TEXT('')),
          COALESCE(_i.cntct_email, TEXT('')), COALESCE(_i.addr_line1, TEXT('')),
          COALESCE(_i.addr_line2, TEXT('')), COALESCE(_i.addr_line3, TEXT('')),
          COALESCE(_i.addr_city, TEXT('')), COALESCE(_i.addr_state, TEXT('')),
          COALESCE(_i.addr_postalcode, TEXT('')), COALESCE(_i.addr_country, TEXT('')) );
    END IF;
  END IF;

  SELECT NEXTVAL('poitem_poitem_id_seq') INTO _poitemid;

  SELECT (COALESCE(MAX(poitem_linenumber), 0) + 1) INTO _polinenumber
  FROM poitem
  WHERE (poitem_pohead_id = _poheadid);

  SELECT COALESCE(itemtax_taxtype_id, -1) INTO _taxtypeid
  FROM itemtax
  WHERE (itemtax_item_id = _i.itemsrc_item_id);

  IF (pPrice IS NULL) THEN
    SELECT itemsrcPrice(pItemSourceId,
                        COALESCE(_s.cohead_warehous_id, -1),
                        pDropShip,
                        (COALESCE(pQty, _s.orderqty) / COALESCE(_i.itemsrc_invvendoruomratio, 1.00)),
                        COALESCE(_i.vend_curr_id, baseCurrId()),
                        CURRENT_DATE) INTO _price;
  ELSE
    _price := pPrice;
  END IF;
  raise notice '_price=%', _price;

  IF (pDropShip) THEN
    INSERT INTO poitem
      ( poitem_id, poitem_status, poitem_pohead_id, poitem_linenumber, 
        poitem_duedate, poitem_itemsite_id,
        poitem_vend_item_descrip, poitem_vend_uom,
        poitem_invvenduomratio, poitem_qty_ordered, 
        poitem_unitprice, poitem_vend_item_number, 
        poitem_itemsrc_id, poitem_order_id, poitem_order_type, poitem_prj_id, poitem_stdcost, 
        poitem_manuf_name, poitem_manuf_item_number, 
        poitem_manuf_item_descrip, poitem_taxtype_id, poitem_comments )
    VALUES
      ( _poitemid, 'U', _poheadid, _polinenumber,
        COALESCE(pDueDate, _s.coitem_scheddate), _s.coitem_itemsite_id,
        COALESCE(_i.itemsrc_vend_item_descrip, TEXT('')), COALESCE(_i.itemsrc_vend_uom, TEXT('')),
        COALESCE(_i.itemsrc_invvendoruomratio, 1.00), (COALESCE(pQty, _s.orderqty) / COALESCE(_i.itemsrc_invvendoruomratio, 1.00)),
        _price, COALESCE(_i.itemsrc_vend_item_number, TEXT('')),
        pItemSourceId, pCoitemId, 'S', _s.cohead_prj_id, stdcost(_i.itemsrc_item_id),
        COALESCE(_i.itemsrc_manuf_name, TEXT('')), COALESCE(_i.itemsrc_manuf_item_number, TEXT('')),
        COALESCE(_i.itemsrc_manuf_item_descrip, TEXT('')), _taxtypeid,
        COALESCE(_s.coitem_memo, TEXT('')));
  ELSE
    INSERT INTO poitem
      ( poitem_id, poitem_status, poitem_pohead_id, poitem_linenumber, 
        poitem_duedate, poitem_itemsite_id,
        poitem_vend_item_descrip, poitem_vend_uom,
        poitem_invvenduomratio, poitem_qty_ordered, 
        poitem_unitprice, poitem_vend_item_number, 
        poitem_itemsrc_id, poitem_order_id, poitem_order_type, poitem_prj_id, poitem_stdcost, 
        poitem_manuf_name, poitem_manuf_item_number, 
        poitem_manuf_item_descrip, poitem_taxtype_id, poitem_comments )
    VALUES
      ( _poitemid, 'U', _poheadid, _polinenumber,
        COALESCE(pDueDate, _s.coitem_scheddate), _s.coitem_itemsite_id,
        COALESCE(_i.itemsrc_vend_item_descrip, TEXT('')), COALESCE(_i.itemsrc_vend_uom, TEXT('')),
        COALESCE(_i.itemsrc_invvendoruomratio, 1.00), (COALESCE(pQty, _s.orderqty) / COALESCE(_i.itemsrc_invvendoruomratio, 1.00)),
        _price, COALESCE(_i.itemsrc_vend_item_number, TEXT('')),
        pItemSourceId, pCoitemId, 'S', _s.cohead_prj_id, stdcost(_i.itemsrc_item_id),
        COALESCE(_i.itemsrc_manuf_name, TEXT('')), COALESCE(_i.itemsrc_manuf_item_number, TEXT('')),
        COALESCE(_i.itemsrc_manuf_item_descrip, TEXT('')), _taxtypeid,
        COALESCE(_s.coitem_memo, TEXT('')));
  END IF;
  -- Copy characteristics from the coitem to the poitem
  INSERT INTO charass
    ( charass_target_type, charass_target_id, charass_char_id,
      charass_value, charass_default, charass_price )
  SELECT 'PI', _poitemid, charass_char_id,
         charass_value, charass_default, charass_price
  FROM charass
  WHERE ( (charass_target_type='SI')
    AND   (charass_target_id=pCoitemId) );

  UPDATE coitem
  SET coitem_order_type = 'P',
      coitem_order_id = _poitemid
  WHERE ( coitem_id = pCoitemId );

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

  RETURN _poitemid;

END;
$_$;


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

--
-- TOC entry 1348 (class 1255 OID 146565904)
-- Dependencies: 4536 8
