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

CREATE FUNCTION convertquote(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pQuheadid ALIAS FOR $1;
  _qunumber TEXT;
  _ponumber TEXT;
  _soheadid INTEGER;
  _soitemid INTEGER;
  _orderid INTEGER;
  _ordertype CHARACTER(1);
  _creditstatus	TEXT;
  _usespos BOOLEAN := false;
  _blanketpos BOOLEAN := true;
  _showConvertedQuote BOOLEAN := false;
  _prospectid	INTEGER;
  _r RECORD;
  _soNum TEXT;

BEGIN

-- Check to make sure the quote has not expired
  IF (SELECT COALESCE(quhead_expire, endOfTime()) < CURRENT_DATE
        FROM quhead
       WHERE(quhead_id=pQuheadid)) THEN
    RETURN -6;
  END IF;

--  Check to make sure that all of the quote items have a valid itemsite
  SELECT quitem_id INTO _r
    FROM quitem LEFT OUTER JOIN itemsite ON (quitem_itemsite_id=itemsite_id)
   WHERE ((itemsite_id IS NULL)
     AND  (quitem_quhead_id=pQuheadid));
  IF (FOUND) THEN
    PERFORM postEvent('CannotConvertQuote', 'Q', quhead_id,
                      quhead_warehous_id, quhead_number,
                      NULL, NULL, NULL, NULL)
    FROM quhead
    WHERE (quhead_id=pQuheadid);

    RETURN -1;
  END IF;

  SELECT cust_creditstatus, cust_usespos, cust_blanketpos
    INTO _creditstatus, _usespos, _blanketpos
  FROM quhead, custinfo
  WHERE ((quhead_cust_id=cust_id)
    AND  (quhead_id=pQuheadid));

  IF (NOT FOUND) THEN
    SELECT prospect_id INTO _prospectid
    FROM quhead, prospect
    WHERE ((quhead_cust_id=prospect_id)
      AND  (quhead_id=pQuheadid));
    IF (NOT FOUND) THEN
      RETURN -2;
    ELSE
      RETURN -3;
    END IF;
  ELSIF (_creditstatus = 'H' AND NOT checkPrivilege('CreateSOForHoldCustomer')) THEN
    RETURN -4;
  ELSIF (_creditstatus = 'W' AND NOT checkPrivilege('CreateSOForWarnCustomer')) THEN
    RETURN -5;
  END IF;

  IF (_usespos) THEN
    SELECT quhead_number, COALESCE(quhead_custponumber, ''), cohead_id INTO _qunumber, _ponumber, _soheadid
    FROM quhead LEFT OUTER JOIN cohead ON ( (cohead_cust_id=quhead_cust_id) AND
                                            (UPPER(cohead_custponumber)=UPPER(quhead_custponumber)) )
    WHERE (quhead_id=pQuheadid);
    IF (_ponumber = '') THEN
      RAISE EXCEPTION 'Customer PO required for Quote % [xtuple: convertQuote, -7, %]',
                      _qunumber, _qunumber;
    END IF;
  
    IF ( (NOT _blanketpos) AND (_soheadid IS NOT NULL) ) THEN
      RAISE EXCEPTION 'Duplicate Customer PO % for Quote % [xtuple: convertQuote, -8, %, %]',
                      _ponumber, _qunumber,
                      _ponumber, _qunumber;
    END IF;
  END IF;
  
  PERFORM quhead_number, cohead_id 
  FROM quhead, cohead 
  WHERE quhead_id = pQuheadid
  AND cohead_number = quhead_number;

  IF (FOUND) THEN
    SELECT fetchSoNumber() INTO _soNum;
  ELSE
    SELECT quhead_number INTO _soNum
    FROM quhead
    WHERE quhead_id = pQuheadid;
  END IF;

  SELECT NEXTVAL('cohead_cohead_id_seq') INTO _soheadid;
  INSERT INTO cohead
  ( cohead_id, cohead_number, cohead_cust_id,
    cohead_orderdate, cohead_packdate,
    cohead_custponumber, cohead_warehous_id,
    cohead_billtoname, cohead_billtoaddress1,
    cohead_billtoaddress2, cohead_billtoaddress3,
    cohead_billtocity, cohead_billtostate, cohead_billtozipcode,
    cohead_billtocountry,
    cohead_shipto_id, cohead_shiptoname, cohead_shiptoaddress1,
    cohead_shiptoaddress2, cohead_shiptoaddress3,
    cohead_shiptocity, cohead_shiptostate, cohead_shiptozipcode,
    cohead_shiptocountry,
    cohead_salesrep_id, cohead_commission,
    cohead_terms_id, cohead_shipchrg_id, cohead_shipform_id,
    cohead_fob, cohead_shipvia,
    cohead_ordercomments, cohead_shipcomments,
    cohead_freight, cohead_misc, cohead_misc_accnt_id, cohead_misc_descrip,
    cohead_holdtype, cohead_quote_number, cohead_prj_id,
    cohead_curr_id, cohead_taxzone_id, cohead_taxtype_id,
    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_ophead_id,
    cohead_calcfreight, cohead_saletype_id, cohead_shipzone_id )
  SELECT _soheadid, _soNum, quhead_cust_id,
         CURRENT_DATE, quhead_packdate,
         quhead_custponumber, quhead_warehous_id,
         quhead_billtoname, quhead_billtoaddress1,
         quhead_billtoaddress2, quhead_billtoaddress3,
         quhead_billtocity, quhead_billtostate, quhead_billtozip,
         quhead_billtocountry,
         quhead_shipto_id, quhead_shiptoname, quhead_shiptoaddress1,
         quhead_shiptoaddress2, quhead_shiptoaddress3,
         quhead_shiptocity, quhead_shiptostate, quhead_shiptozipcode,
         quhead_shiptocountry,
         quhead_salesrep_id, quhead_commission,
         quhead_terms_id, cust_shipchrg_id, cust_shipform_id,
         quhead_fob, quhead_shipvia,
         quhead_ordercomments, quhead_shipcomments,
         quhead_freight, quhead_misc, quhead_misc_accnt_id, quhead_misc_descrip,
         'N', quhead_number, quhead_prj_id,
	 quhead_curr_id, quhead_taxzone_id, quhead_taxtype_id,
	 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, quhead_ophead_id,
         quhead_calcfreight, quhead_saletype_id, quhead_shipzone_id
  FROM quhead JOIN custinfo ON (cust_id=quhead_cust_id)
  WHERE (quhead_id=pQuheadid);

  -- Move Documents
  UPDATE url SET url_source_id = _soheadid,
                 url_source = 'S'
  WHERE ((url_source='Q') AND (url_source_id = pQuheadid));

  UPDATE imageass SET imageass_source_id = _soheadid,
                      imageass_source = 'S'
  WHERE ((imageass_source='Q') AND (imageass_source_id = pQuheadid));

  UPDATE docass SET docass_source_id = _soheadid,
                    docass_source_type = 'S'
  WHERE ((docass_source_type='Q') AND (docass_source_id = pQuheadid));

  -- Move Email
  IF (fetchMetricBool('EnableBatchManager')) THEN
    UPDATE xtbatch.emlassc SET emlassc_type='S',
                               emlassc_assc_id=_soheadid
    WHERE ((emlassc_type='Q') AND (emlassc_assc_id=pQuheadid));
  END IF;

  -- Copy Characteristics
  INSERT INTO charass
        (charass_target_type, charass_target_id, charass_char_id, charass_value, charass_default, charass_price)
  SELECT 'SO', _soheadid, charass_char_id, charass_value, charass_default, charass_price
    FROM charass
   WHERE ((charass_target_type='QU')
     AND  (charass_target_id=pQuheadid));

  -- Copy Comments
  INSERT INTO comment
  ( comment_cmnttype_id, comment_source, comment_source_id, comment_date, comment_user, comment_text, comment_public )
  SELECT comment_cmnttype_id, 'S', _soheadid, comment_date, comment_user, ('Quote-' || comment_text), comment_public
  FROM comment
  WHERE ( (comment_source='Q')
    AND   (comment_source_id=pQuheadid) );

  FOR _r IN SELECT quitem.*,
                   quhead_number, quhead_prj_id, quhead_saletype_id,
                   itemsite_item_id, itemsite_leadtime,
                   itemsite_createsopo, itemsite_createsopr,
                   item_type, COALESCE(quitem_itemsrc_id, itemsrc_id, -1) AS itemsrcid
            FROM quhead JOIN quitem ON (quitem_quhead_id=quhead_id)
                        JOIN itemsite ON (itemsite_id=quitem_itemsite_id)
                        JOIN item ON (item_id=itemsite_item_id)
                        LEFT OUTER JOIN itemsrc ON ( (itemsrc_item_id=item_id) AND
                                                     (itemsrc_default) )
            WHERE (quhead_id=pQuheadid)
            ORDER BY quitem_linenumber LOOP

    SELECT NEXTVAL('coitem_coitem_id_seq') INTO _soitemid;

    INSERT INTO coitem
    ( coitem_id, coitem_cohead_id, coitem_linenumber, coitem_itemsite_id,
      coitem_status, coitem_scheddate, coitem_promdate,
      coitem_price, coitem_custprice, 
      coitem_qtyord, coitem_qtyshipped, coitem_qtyreturned,
      coitem_qty_uom_id, coitem_qty_invuomratio,
      coitem_price_uom_id, coitem_price_invuomratio,
      coitem_unitcost, coitem_prcost,
      coitem_custpn, coitem_memo, coitem_taxtype_id, coitem_order_id )
    VALUES
    ( _soitemid, _soheadid, _r.quitem_linenumber, _r.quitem_itemsite_id,
      'O', _r.quitem_scheddate, _r.quitem_promdate,
      _r.quitem_price, _r.quitem_custprice,
      _r.quitem_qtyord, 0, 0,
      _r.quitem_qty_uom_id, _r.quitem_qty_invuomratio,
      _r.quitem_price_uom_id, _r.quitem_price_invuomratio,
      _r.quitem_unitcost, _r.quitem_prcost,
      _r.quitem_custpn, _r.quitem_memo, _r.quitem_taxtype_id, -1 );

    IF (fetchMetricBool('enablextcommissionission')) THEN
      PERFORM xtcommission.getSalesReps(quhead_cust_id, quhead_shipto_id,
                                        _r.itemsite_item_id, _r.quhead_saletype_id,
                                        _r.quitem_price, _r.quitem_custprice,
                                        _soitemid, 'SalesItem')
      FROM quhead
      WHERE (quhead_id=pQuheadid);
    END IF;

    -- Copy Characteristics
    INSERT INTO charass
          (charass_target_type, charass_target_id, charass_char_id, charass_value, charass_default, charass_price)
    SELECT 'SI', _soitemid, charass_char_id, charass_value, charass_default, charass_price
      FROM charass
     WHERE ((charass_target_type='QI')
       AND  (charass_target_id=_r.quitem_id));

    -- Copy Comments
    INSERT INTO comment
    ( comment_cmnttype_id, comment_source, comment_source_id, comment_date, comment_user, comment_text )
    SELECT comment_cmnttype_id, 'SI', _soitemid, comment_date, comment_user, ('Quote-' || comment_text)
    FROM comment
    WHERE ( (comment_source='QI')
      AND   (comment_source_id=_r.quitem_id) );

    _orderid := -1;
    _ordertype := '';
    IF (_r.quitem_createorder) THEN

      IF (_r.item_type IN ('M')) THEN
        SELECT createWo( CAST(_soNum AS INTEGER), supply.itemsite_id, 1,
                         (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
                         _r.itemsite_leadtime, _r.quitem_scheddate, _r.quitem_memo,
                         'S', _soitemid, _r.quhead_prj_id ) INTO _orderId
        FROM itemsite sold, itemsite supply
        WHERE ((sold.itemsite_item_id=supply.itemsite_item_id)
         AND (supply.itemsite_warehous_id=_r.quitem_order_warehous_id)
         AND (sold.itemsite_id=_r.quitem_itemsite_id) );
        _orderType := 'W';

        INSERT INTO charass
              (charass_target_type, charass_target_id, charass_char_id, charass_value)
        SELECT 'W', _orderId, charass_char_id, charass_value
          FROM charass
         WHERE ((charass_target_type='QI')
           AND  (charass_target_id=_r.quitem_id));

      ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopr) ) THEN
        SELECT createPr( CAST(_soNum AS INTEGER), _r.quitem_itemsite_id,
                         (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
                         _r.quitem_scheddate, '', 'S', _soitemid ) INTO _orderId;
        _orderType := 'R';
        UPDATE pr SET pr_prj_id=_r.quhead_prj_id WHERE pr_id=_orderId;
      ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopo) ) THEN
        IF (_r.quitem_prcost=0) THEN
          SELECT createPurchaseToSale(_soitemid, _r.itemsrcid, _r.quitem_dropship) INTO _orderId;
        ELSE
          SELECT createPurchaseToSale(_soitemid, _r.itemsrcid, _r.quitem_dropship, _r.quitem_prcost) INTO _orderId;
        END IF;
        _orderType := 'P';
      END IF;

      UPDATE coitem SET coitem_order_type=_ordertype, coitem_order_id=_orderid
      WHERE (coitem_id=_soitemid);

    END IF;

  END LOOP;

  SELECT metric_value INTO _showConvertedQuote
  FROM metric WHERE metric_name = 'ShowQuotesAfterSO';

  -- bug 26513 - mobilized databases delete the quote in a cohead trigger when cohead_wasquote
  -- on INSERT. set the flag late, otherwise quitems may be removed before they can be copied
  UPDATE cohead SET cohead_wasquote = TRUE WHERE cohead_id = _soheadid;

  IF (_showConvertedQuote) THEN
    UPDATE quhead
    SET quhead_status= 'C'
    WHERE (quhead_id = pQuheadid);
  ELSE
  PERFORM deleteQuote(pQuheadid);
  END IF;

  RETURN _soheadid;

END;
$_$;


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

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

CREATE FUNCTION convertquotetoinvoice(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2015 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;
  _qunumber TEXT;
  _ponumber TEXT;
  _iheadid INTEGER;
  _iitemid INTEGER;
  _orderid INTEGER;
  _ordertype CHARACTER(1);
  _creditstatus	TEXT;
  _usespos BOOLEAN := false;
  _blanketpos BOOLEAN := true;
  _showConvertedQuote BOOLEAN := false;
  _prospectid	INTEGER;
  _r RECORD;
  _inNum TEXT;

BEGIN

-- Check to make sure the quote has not expired
  IF (SELECT COALESCE(quhead_expire, endOfTime()) < CURRENT_DATE
        FROM quhead
       WHERE(quhead_id=pQuheadid)) THEN
    RETURN -6;
  END IF;

--  Check to make sure that all of the quote items have a valid itemsite
  SELECT quitem_id INTO _r
    FROM quitem LEFT OUTER JOIN itemsite ON (quitem_itemsite_id=itemsite_id)
   WHERE ((itemsite_id IS NULL)
     AND  (quitem_quhead_id=pQuheadid));
  IF (FOUND) THEN
    PERFORM postEvent('CannotConvertQuote', 'Q', quhead_id,
                      quhead_warehous_id, quhead_number,
                      NULL, NULL, NULL, NULL)
    FROM quhead
    WHERE (quhead_id=pQuheadid);

    RETURN -1;
  END IF;

-- Get Credit Stat, Uses POs and Blanket POs

  SELECT cust_creditstatus, cust_usespos, cust_blanketpos
    INTO _creditstatus, _usespos, _blanketpos
  FROM quhead, custinfo
  WHERE ((quhead_cust_id=cust_id)
    AND  (quhead_id=pQuheadid));

-- Check to see if customer or prospect

  IF (NOT FOUND) THEN
    SELECT prospect_id INTO _prospectid
    FROM quhead, prospect
    WHERE ((quhead_cust_id=prospect_id)
      AND  (quhead_id=pQuheadid));
    IF (NOT FOUND) THEN
      RETURN -2;
    ELSE
      RETURN -3;
    END IF;
  ELSIF (_creditstatus = 'H' AND NOT hasPriv('CreateSOForHoldCustomer')) THEN
    RETURN -4;
  ELSIF (_creditstatus = 'W' AND NOT hasPriv('CreateSOForWarnCustomer')) THEN
    RETURN -5;
  END IF;

-- PO/blanket PO checks
  IF (_usespos) THEN
    SELECT quhead_number, COALESCE(quhead_custponumber, ''), invchead_id INTO _qunumber, _ponumber, _iheadid
    FROM quhead LEFT OUTER JOIN invchead ON ( (invchead_cust_id=quhead_cust_id) AND
                                              (UPPER(invchead_ponumber)=UPPER(quhead_custponumber)) )
    WHERE (quhead_id=pQuheadid);
    IF (_ponumber = '') THEN
      RAISE EXCEPTION 'Customer PO required for Quote % [xtuple: convertQuote, -7, %]',
                      _qunumber, _qunumber;
    END IF;
  
    IF ( (NOT _blanketpos) AND (_iheadid IS NOT NULL) ) THEN
      RAISE EXCEPTION 'Duplicate Customer PO % for Quote % [xtuple: convertQuote, -8, %, %]',
                      _ponumber, _qunumber,
                      _ponumber, _qunumber;
    END IF;
  END IF;
  

  IF (_usespos) THEN
    SELECT quhead_number INTO _qunumber
    FROM quhead
    WHERE (quhead_id=pQuheadid)
      AND (COALESCE(quhead_custponumber, '') = '');
    IF (FOUND) THEN
      RAISE EXCEPTION 'Customer PO required for Quote % [xtuple: convertQuote, -7, %]',
                      _qunumber, _qunumber;
    END IF;
  END IF;
  
  IF ( (_usespos) AND (NOT _blanketpos) ) THEN
    SELECT quhead_number, quhead_custponumber INTO _qunumber, _ponumber
    FROM quhead JOIN invchead ON ( (invchead_cust_id=quhead_cust_id) AND
                                   (UPPER(invchead_ponumber)=UPPER(quhead_custponumber)) )
    WHERE (quhead_id=pQuheadid);
    IF (FOUND) THEN
      RAISE EXCEPTION 'Duplicate Customer PO % for Quote % [xtuple: convertQuote, -8, %, %]',
                      _ponumber, _qunumber,
                      _ponumber, _qunumber;
    END IF;
  END IF;
  
--Check to see if an invoice exists with the quote number
  
  PERFORM quhead_number, invchead_id 
  FROM quhead, invchead 
  WHERE quhead_id = pQuheadid
  AND invchead_invcnumber = quhead_number;

-- If it does then get a new Invoice number otherwise use the quote number as the invoice number

  IF (FOUND) THEN
    SELECT fetchinvcnumber() INTO _inNum;
  ELSE
    SELECT quhead_number INTO _inNum
    FROM quhead
    WHERE quhead_id = pQuheadid;
  END IF;

--Insert quote info into invoice tables

  SELECT NEXTVAL('invchead_invchead_id_seq') INTO _iheadid;
  INSERT INTO invchead
  ( invchead_ordernumber, invchead_shipdate, invchead_recurring,
    invchead_id, invchead_invcnumber, invchead_cust_id,
    invchead_orderdate, invchead_ponumber, 
    invchead_billto_name, invchead_billto_address1,
    invchead_billto_address2, invchead_billto_address3,
    invchead_billto_city, invchead_billto_state, invchead_billto_zipcode, invchead_billto_country,
    invchead_shipto_id, invchead_shipto_name, invchead_shipto_address1,
    invchead_shipto_address2, invchead_shipto_address3,
    invchead_shipto_city, invchead_shipto_state, invchead_shipto_zipcode, invchead_shipto_country, 
    invchead_salesrep_id, invchead_commission,
    invchead_terms_id, invchead_shipchrg_id, invchead_fob, invchead_shipvia,
    invchead_notes, invchead_freight, 
    invchead_misc_amount, invchead_misc_accnt_id, invchead_misc_descrip,
    invchead_prj_id, invchead_curr_id, invchead_taxzone_id,
    invchead_posted, invchead_printed, invchead_invcdate,
    invchead_saletype_id, invchead_shipzone_id
    --invchead_taxtype_id,
    --invchead_shipto_cntct_id, invchead_shipto_cntct_honorific, invchead_shipto_cntct_first_name,
    --invchead_shipto_cntct_middle, invchead_shipto_cntct_last_name, invchead_shipto_cntct_suffix,
    --invchead_shipto_cntct_phone, invchead_shipto_cntct_title, invchead_shipto_cntct_fax, 
    --invchead_shipto_cntct_email,
    --invchead_billto_cntct_id, invchead_billto_cntct_honorific,
    --invchead_billto_cntct_first_name, invchead_billto_cntct_middle, invchead_billto_cntct_last_name, 
    --invchead_billto_cntct_suffix, invchead_billto_cntct_phone, invchead_billto_cntct_title, 
    --invchead_billto_cntct_fax, invchead_billto_cntct_email, 
    --invchead_ophead_id,
    --invchead_calcfreight 
    )
  SELECT quhead_number, quhead_packdate, 'f',
         _iheadid, _inNum, quhead_cust_id,
         CURRENT_DATE, quhead_custponumber, 
         quhead_billtoname, quhead_billtoaddress1,
         quhead_billtoaddress2, quhead_billtoaddress3,
         quhead_billtocity, quhead_billtostate, quhead_billtozip, quhead_billtocountry,
         quhead_shipto_id, quhead_shiptoname, quhead_shiptoaddress1,
         quhead_shiptoaddress2, quhead_shiptoaddress3,
         quhead_shiptocity, quhead_shiptostate, quhead_shiptozipcode, quhead_shiptocountry,
         quhead_salesrep_id, quhead_commission,
         quhead_terms_id, cust_shipchrg_id, quhead_fob, quhead_shipvia,
         quhead_ordercomments,  quhead_freight,
         quhead_misc, quhead_misc_accnt_id, quhead_misc_descrip,
         quhead_prj_id, quhead_curr_id, quhead_taxzone_id,
         'f','f',current_date,
         quhead_saletype_id, quhead_shipzone_id
         --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, quhead_ophead_id,
         --quhead_calcfreight
  FROM quhead JOIN custinfo ON (cust_id=quhead_cust_id)
  WHERE (quhead_id=pQuheadid);

  -- Copy characteristics from the quhead to the invoice head   
  INSERT INTO charass
        ( charass_target_type, charass_target_id, charass_char_id,
          charass_value, charass_default, charass_price )
  SELECT  'INV', _iheadid, charass_char_id,
          charass_value, charass_default, charass_price 
    FROM charass 
    JOIN char    ON char_id = charass_char_id
    JOIN charuse ON char_id = charuse_char_id AND charuse_target_type = 'INV'
   WHERE charass_target_type = 'QU'
     AND charass_target_id   = pQuheadid;

-- Attachments on Invoice not supported but leaving this in for future use:
/*
  UPDATE url SET url_source_id = _iheadid,
                 url_source = 'I'
  WHERE ((url_source='Q') AND (url_source_id = pQuheadid));

  UPDATE imageass SET imageass_source_id = _iheadid,
                      imageass_source = 'I'
  WHERE ((imageass_source='Q') AND (imageass_source_id = pQuheadid));

  UPDATE docass SET docass_source_id = _iheadid,
                    docass_source_type = 'I'
  WHERE ((docass_source_type='Q') AND (docass_source_id = pQuheadid));
*/


-- Comments not supported on Invoice but leaving this in for future use:

/*  
  INSERT INTO comment
  ( comment_cmnttype_id, comment_source, comment_source_id, comment_date, comment_user, comment_text, comment_public )
  SELECT comment_cmnttype_id, 'I', _iheadid, comment_date, comment_user, ('Quote-' || comment_text), comment_public
  FROM comment
  WHERE ( (comment_source='Q')
    AND   (comment_source_id=pQuheadid) );
*/

  FOR _r IN SELECT quitem.*,
                   quhead_number, quhead_prj_id, quhead_saletype_id,
                   itemsite_item_id, itemsite_leadtime,
                   itemsite_createsopo, itemsite_createsopr,
                   item_type, COALESCE(quitem_itemsrc_id, itemsrc_id, -1) AS itemsrcid
            FROM quhead JOIN quitem ON (quitem_quhead_id=quhead_id)
                        JOIN itemsite ON (itemsite_id=quitem_itemsite_id)
                        JOIN item ON (item_id=itemsite_item_id)
                        LEFT OUTER JOIN itemsrc ON ( (itemsrc_item_id=item_id) AND
                                                     (itemsrc_default) )
            WHERE (quhead_id=pQuheadid) LOOP

    SELECT NEXTVAL('invcitem_invcitem_id_seq') INTO _iitemid;

    INSERT INTO invcitem
    ( invcitem_id, invcitem_invchead_id, invcitem_linenumber, 
      invcitem_item_id,
      invcitem_warehous_id,
      --invcitem_status, 
      --invcitem_scheddate, invcitem_promdate,
      invcitem_price, invcitem_custprice, 
      invcitem_ordered, invcitem_billed,
      invcitem_qty_uom_id, invcitem_qty_invuomratio,
      invcitem_price_uom_id, invcitem_price_invuomratio,
      invcitem_custpn, invcitem_notes, invcitem_taxtype_id )
    VALUES
    ( _iitemid, _iheadid, _r.quitem_linenumber, 
      (SELECT itemsite_item_id FROM itemsite WHERE itemsite_id = _r.quitem_itemsite_id),
      (SELECT itemsite_warehous_id FROM itemsite WHERE itemsite_id = _r.quitem_itemsite_id),
      --'O', 
      --_r.quitem_scheddate, _r.quitem_promdate,
      _r.quitem_price, _r.quitem_custprice,
      _r.quitem_qtyord, _r.quitem_qtyord,
      _r.quitem_qty_uom_id, _r.quitem_qty_invuomratio,
      _r.quitem_price_uom_id, _r.quitem_price_invuomratio,
      _r.quitem_custpn, _r.quitem_memo, _r.quitem_taxtype_id );

    IF (fetchMetricBool('enablextcommissionission')) THEN
      PERFORM xtcommission.getSalesReps(quhead_cust_id, quhead_shipto_id,
                                        _r.itemsite_item_id, _r.quhead_saletype_id,
                                        _r.quitem_price, _r.quitem_custprice,
                                        _iitemid, 'InvoiceItem')
      FROM quhead
      WHERE (quhead_id=pQuheadid);
    END IF;

/*
    INSERT INTO charass
          (charass_target_type, charass_target_id, charass_char_id, charass_value, charass_default, charass_price)
    SELECT 'SI', _iitemid, charass_char_id, charass_value, charass_default, charass_price
      FROM charass
     WHERE ((charass_target_type='QI')
       AND  (charass_target_id=_r.quitem_id));
*/


-- Comments not supported but leaving in for future use

/*
    INSERT INTO comment
    ( comment_cmnttype_id, comment_source, comment_source_id, comment_date, comment_user, comment_text )
    SELECT comment_cmnttype_id, 'SI', _iitemid, comment_date, comment_user, ('Quote-' || comment_text)
    FROM comment
    WHERE ( (comment_source='QI')
      AND   (comment_source_id=_r.quitem_id) );
*/

    _orderid := -1;
    _ordertype := '';
    IF (_r.quitem_createorder) THEN

      IF (_r.item_type IN ('M')) THEN
        SELECT createWo( CAST(_r.quhead_number AS INTEGER), supply.itemsite_id, 1, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
                         _r.itemsite_leadtime, _r.quitem_scheddate, _r.quitem_memo, 'Q', _iitemid, _r.quhead_prj_id ) INTO _orderId
        FROM itemsite sold, itemsite supply
        WHERE ((sold.itemsite_item_id=supply.itemsite_item_id)
         AND (supply.itemsite_warehous_id=_r.quitem_order_warehous_id)
         AND (sold.itemsite_id=_r.quitem_itemsite_id) );
        _orderType := 'W';

        INSERT INTO charass
              (charass_target_type, charass_target_id, charass_char_id, charass_value)
        SELECT 'W', _orderId, charass_char_id, charass_value
          FROM charass
         WHERE ((charass_target_type='QI')
           AND  (charass_target_id=_r.quitem_id));

      ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopr) ) THEN
        SELECT createPr( CAST(_r.quhead_number AS INTEGER), _r.quitem_itemsite_id, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
                         _r.quitem_scheddate, '', 'S', _iitemid ) INTO _orderId;
        _orderType := 'R';
        UPDATE pr SET pr_prj_id=_r.quhead_prj_id WHERE pr_id=_orderId;
      ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopo) ) THEN
        IF (_r.quitem_prcost=0) THEN
          SELECT createpurchasetoquote(_r.quitem_id, _r.itemsrcid, _r.quitem_dropship) INTO _orderId;
        ELSE
          SELECT createpurchasetoquote(_r.quitem_id, _r.itemsrcid, _r.quitem_dropship, _r.quitem_prcost) INTO _orderId;
        END IF;
        _orderType := 'P';
      END IF;

--      UPDATE invcitem SET invcitem_order_type=_ordertype, invcitem_order_id=_orderid
--      WHERE (invcitem_id=_iitemid);

    END IF;

  END LOOP;

  SELECT metric_value INTO _showConvertedQuote
  FROM metric WHERE metric_name = 'ShowQuotesAfterSO';

  IF (_showConvertedQuote) THEN
    UPDATE quhead
    SET quhead_status= 'C'
    WHERE (quhead_id = pQuheadid);
  ELSE
     PERFORM deleteQuote(pQuheadid);
  END IF;

  RETURN _iheadid;

END;
$_$;


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

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

CREATE FUNCTION copyaccountingyearperiod(pyearperiodid 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
  _newYear INTEGER;
BEGIN

  -- First create new fiscal year
  INSERT INTO yearperiod (yearperiod_start, yearperiod_end, yearperiod_closed)
    SELECT yearperiod_start + INTERVAL ' 1 year',  yearperiod_end + INTERVAL ' 1 year', FALSE
      FROM yearperiod
      WHERE yearperiod_id = pYearPeriodid
  RETURNING yearperiod_id INTO _newYear;

  IF (NOT COALESCE(_newYear, -1) > 0) THEN
    RAISE EXCEPTION 'An error occurred creating the Fiscal Year [xtuple: copyaccountingyearperiod, -1]';
  END IF;

-- Copy corresponding fiscal periods across to the new fiscal year
  INSERT INTO period (period_start, period_end,
                      period_closed, period_freeze, period_initial,
                      period_name,
                      period_yearperiod_id, period_quarter, period_number)
    SELECT (period_start + INTERVAL '1 year')::date, (period_end + INTERVAL '1 year')::date,
                      FALSE, FALSE, FALSE,
                      EXTRACT(year FROM period_start + INTERVAL '1 year') || '-' || to_char((period_start + INTERVAL '1 year'), 'Mon'),
                      _newYear, period_quarter, period_number
    FROM period
    WHERE period_yearperiod_id = pYearPeriodid;

--  Forward Update Accounting Periods for all G/L Accounts
  PERFORM forwardupdatetrialbalance(max(trialbal_id))
    FROM accnt LEFT OUTER JOIN trialbal ON ( (trialbal_accnt_id=accnt_id)  )
    GROUP BY accnt_id
    HAVING max(trialbal_id) IS NOT NULL
    ORDER BY accnt_id;

  RETURN _newYear;

END;
$$;


ALTER FUNCTION public.copyaccountingyearperiod(pyearperiodid integer) OWNER TO admin;

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

--
-- 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,
      bomitem_char_id, bomitem_value )
    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,
      _bi.bomitem_char_id, _bi.bomitem_value );

    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;

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- 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
  _destwhs  whsinfo%ROWTYPE;
  _new    itemsite%ROWTYPE;
  _supplywarehousid INTEGER := NULL;

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;

  -- find supply itemsite
  IF (_new.itemsite_supply_itemsite_id IS NOT NULL) THEN
    SELECT itemsite_warehous_id INTO _supplywarehousid
    FROM itemsite
    WHERE (itemsite_id=_new.itemsite_supply_itemsite_id);
    IF (FOUND) THEN
      SELECT itemsite_id INTO _new.itemsite_supply_itemsite_id
      FROM itemsite
      WHERE (itemsite_warehous_id=_supplywarehousid)
        AND (itemsite_item_id=pDestItemid);
    END IF;
  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_location_id          := -1;
  _new.itemsite_recvlocation_id      := -1;
  _new.itemsite_issuelocation_id     := -1;
  _new.itemsite_location_dist        := FALSE;
  _new.itemsite_recvlocation_dist    := FALSE;
  _new.itemsite_issuelocation_dist   := FALSE;

  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_createwo           := FALSE;
    _new.itemsite_costcat_id         := _destwhs.warehous_costcat_id;
    _new.itemsite_supply_itemsite_id := NULL;
  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_autoabcclass,
    itemsite_ordergroup,             itemsite_disallowblankwip,
    itemsite_maxordqty,              itemsite_mps_timefence,
    itemsite_createwo,               itemsite_warrpurc,
    itemsite_autoreg,
    itemsite_planning_type,          itemsite_supply_itemsite_id,
    itemsite_createsopr,             itemsite_createsopo,
    itemsite_dropship,
    itemsite_recvlocation_id,        itemsite_issuelocation_id,
    itemsite_location_dist,          itemsite_recvlocation_dist,
    itemsite_issuelocation_dist
  ) 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_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,
    _new.itemsite_createsopr,        _new.itemsite_createsopo,
    _new.itemsite_dropship,
    _new.itemsite_recvlocation_id,   _new.itemsite_issuelocation_id,
    _new.itemsite_location_dist,     _new.itemsite_recvlocation_dist,
    _new.itemsite_issuelocation_dist
    );

  RETURN _new.itemsite_id;
END;
$$;


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

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

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

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

--
-- 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_prjtype_id,
            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, _p.prj_prjtype_id,
            _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;

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

--
-- 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='QI')
     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;

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

CREATE FUNCTION copyquotetocustomer(pquheadid integer, pcustomerid 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
  _isCustomer BOOLEAN;
  _quotecustomer INTEGER;
  _quheadid INTEGER;
  _qunumber TEXT;
  _b     RECORD;

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;

  SELECT * INTO _b FROM (
    SELECT cust_name as name,  addr_line1, addr_line2, addr_line3, addr_city, addr_state, addr_postalcode, addr_country,
       cntct_id, cntct_honorific, cntct_first_name, cntct_middle, cntct_last_name, cntct_suffix, cntct_phone, cntct_title, cntct_fax, cntct_email    
      FROM custinfo 
      LEFT JOIN cntct ON (cust_cntct_id = cntct_id)
      LEFT JOIN addr ON (cntct_addr_id = addr_id)
      WHERE cust_id = pCustomerid
    UNION
    SELECT prospect_name AS name,  addr_line1, addr_line2, addr_line3, addr_city, addr_state, addr_postalcode, addr_country,
        cntct_id, cntct_honorific, cntct_first_name, cntct_middle, cntct_last_name, cntct_suffix, cntct_phone, cntct_title, cntct_fax, cntct_email
      FROM prospect
      LEFT JOIN cntct ON (prospect_cntct_id = cntct_id)
      LEFT JOIN addr ON (cntct_addr_id = addr_id)  
      WHERE prospect_id = pCustomerid ) data;

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Invalid Customer supplied: %', pCustomerid;
  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,
         pCustomerid,  -- use new customer or prospect
         quhead_prj_id,
         CURRENT_DATE,  COALESCE(pSchedDate,  quhead_packdate),  quhead_fob,
         quhead_warehous_id,  quhead_terms_id,  quhead_salesrep_id,
         quhead_custponumber,  quhead_shipvia,
         NULL,    -- shipto_id         
         '',   -- shipto_name 	
         '',   -- addr_line1 	
         '',   -- addr_line2 	
         '',   -- addr_line3	
         '',   -- addr_city	
         '',   -- addr_state	
         '',   -- addr_postalcode	
         NULL,  
         '',  
         -- new billto info
         _b.name,		-- quhead_billtoname,
         _b.addr_line1,		-- quhead_billtoaddress1,
         _b.addr_line2, 	-- quhead_billtoaddress2  
         _b.addr_line3,		-- quhead_billtoaddress3,
         _b.addr_city,		-- quhead_billtocity  
         _b.addr_state,		-- quhead_billtostate,
         _b.addr_postalcode,	-- 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',
         
 	 NULL, -- 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,
	
         _b.cntct_id,			-- quhead_billto_cntct_id,
         _b.cntct_honorific,	-- quhead_billto_cntct_honorific,
         _b.cntct_first_name,	-- quhead_billto_cntct_first_name,
         _b.cntct_middle,		-- quhead_billto_cntct_middle,
         _b.cntct_last_name,	-- quhead_billto_cntct_last_name,
         _b.cntct_suffix,		-- quhead_billto_cntct_suffix,
         _b.cntct_phone,		-- quhead_billto_cntct_phone,
         _b.cntct_title,		-- quhead_billto_cntct_title,
         _b.cntct_fax,			-- quhead_billto_cntct_fax,
         _b.cntct_email		-- 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.copyquotetocustomer(pquheadid integer, pcustomerid integer, pscheddate date) OWNER TO admin;

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

CREATE FUNCTION copyso(psoheadid integer, pcustomer 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;
  _newCustomer BOOLEAN;
  _customer RECORD;

BEGIN

  SELECT NEXTVAL('cohead_cohead_id_seq') INTO _soheadid;

-- Check whether we are copying to a new customer
  SELECT (cohead_cust_id <> COALESCE(pCustomer, cohead_cust_id)) INTO _newCustomer
    FROM cohead
    WHERE cohead_id = pSoheadid;

  IF (NOT _newCustomer) THEN

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

  ELSE -- Copy SO to New Customer 
    -- Get Customer details
    SELECT cust_id, cust_name, cust_salesrep_id, cust_terms_id, cust_shipvia, cust_shipchrg_id,
      cust_shipform_id, cust_commprcnt, cust_partialship, cust_taxzone_id, cust_curr_id,
      bc.cntct_id AS billing_contact_id, bc.cntct_honorific AS billing_honorific, bc.cntct_first_name AS billing_first_name, bc.cntct_middle AS billing_middle, bc.cntct_last_name AS billing_last_name,
      bc.cntct_suffix AS billing_suffix, bc.cntct_phone AS billing_phone, bc.cntct_title AS billing_title, bc.cntct_fax AS billing_fax, bc.cntct_email AS billing_email,
      ba.addr_line1 AS billing1, ba.addr_line2 AS billing2, ba.addr_line3 AS billing3, ba.addr_city AS billing_city, ba.addr_state AS billing_state, ba.addr_postalcode AS billing_postalcode, ba.addr_country AS billing_country,
      shipto_id, shipto_name, sc.cntct_id AS shipto_contact_id, sc.cntct_honorific AS shipto_honorific, sc.cntct_phone AS shipto_phone, sc.cntct_first_name AS shipto_first_name, sc.cntct_middle AS shipto_middle, sc.cntct_last_name AS shipto_last_name,
      sc.cntct_suffix AS shipto_suffix, sc.cntct_phone AS shipto_phone, sc.cntct_title AS shipto_title, sc.cntct_fax AS shipto_fax, sc.cntct_email AS shipto_email,
      sa.addr_line1 AS shipto1, sa.addr_line2 AS shipto2, sa.addr_line3 AS shipto3, sa.addr_city AS shipto_city, sa.addr_state AS shipto_state, sa.addr_postalcode AS shipto_postalcode, sa.addr_country AS shipto_country
    INTO _customer  
    FROM custinfo 
    LEFT OUTER join cntct bc ON (cust_cntct_id=bc.cntct_id)
    LEFT OUTER JOIN addr ba ON (cntct_addr_id=ba.addr_id)
    LEFT OUTER JOIN shiptoinfo ON ((shipto_cust_id=cust_id)
                                         AND (shipto_default)) 
    LEFT OUTER join cntct sc ON (shipto_cntct_id=sc.cntct_id)
    LEFT OUTER JOIN addr sa ON (shipto_addr_id=sa.addr_id)                                         
    WHERE cust_id=pCustomer;

  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_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(),
    pCustomer,
    '',
    cohead_type,
    CURRENT_DATE,
    cohead_warehous_id,
    COALESCE(_customer.shipto_id, NULL),
    _customer.shipto_name,
    _customer.shipto1,
    _customer.shipto2,
    _customer.shipto3,  
    _customer.cust_salesrep_id,
    _customer.cust_terms_id,
    cohead_fob,
    _customer.cust_shipvia,
    _customer.shipto_city,
    _customer.shipto_state,
    _customer.shipto_postalcode,
    cohead_freight,
    cohead_misc,
    FALSE,
    cohead_ordercomments,
    cohead_shipcomments,
    _customer.shipto_phone,
    _customer.cust_shipchrg_id,
    _customer.cust_shipform_id,
    _customer.cust_name,
    _customer.billing1,
    _customer.billing2,
    _customer.billing3,
    _customer.billing_city,
    _customer.billing_state,
    _customer.billing_postalcode,
    cohead_misc_accnt_id,
    cohead_misc_descrip,
    _customer.cust_commprcnt,
    cohead_miscdate,
    cohead_holdtype,
    COALESCE(pSchedDate, cohead_packdate),
    NULL, --cohead_prj_id,
    FALSE,
    CURRENT_DATE,
    _customer.cust_partialship,
    NULL,
    getEffectiveXtUser(),
    NULL,
    _customer.billing_country,
    _customer.shipto_country,
    _customer.cust_curr_id,
    cohead_calcfreight,
    _customer.shipto_contact_id,
    _customer.shipto_honorific,
    _customer.shipto_first_name,
    _customer.shipto_middle,
    _customer.shipto_last_name,
    _customer.shipto_suffix,
    _customer.shipto_phone,
    _customer.shipto_title,
    _customer.shipto_fax,
    _customer.shipto_email,    
    _customer.billing_contact_id,
    _customer.billing_honorific,
    _customer.billing_first_name,
    _customer.billing_middle,
    _customer.billing_last_name,
    _customer.billing_suffix,
    _customer.billing_phone,
    _customer.billing_title,
    _customer.billing_fax,
    _customer.billing_email,
    _customer.cust_taxzone_id,
    cohead_taxtype_id,
    cohead_ophead_id,
    'O',
    cohead_saletype_id,
    cohead_shipzone_id
  FROM cohead
  WHERE (cohead_id=pSoheadid);

  END IF;  -- Order Header population

  INSERT INTO charass
        (charass_target_type, charass_target_id,
         charass_char_id, charass_value)
  SELECT charass_target_type, _soheadid,
         charass_char_id, charass_value
    FROM charass
   WHERE ((charass_target_type='SO')
     AND  (charass_target_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, pcustomer integer, pscheddate date) OWNER TO admin;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--  Forward Update Accounting Periods for all G/L Accounts
  PERFORM forwardupdatetrialbalance(max(trialbal_id))
    FROM accnt LEFT OUTER JOIN trialbal ON ( (trialbal_accnt_id=accnt_id)  )
    GROUP BY accnt_id
    HAVING max(trialbal_id) IS NOT NULL
    ORDER BY accnt_id;

  RETURN _periodid;

END;
$_$;


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

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

--
-- 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 WARNING 'createAPChecks() is deprecated - use createChecks() instead';
  RETURN createChecks($1, $2);
END;
$_$;


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- 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;
  _checknumber		INTEGER := -1;
  _check_curr_rate      NUMERIC;
  _bankaccnt_currid	INTEGER;
  _bankaccnt_prnt_check BOOLEAN;

BEGIN
  SELECT bankaccnt_curr_id,currRate(bankaccnt_curr_id,pCheckDate), bankaccnt_prnt_check INTO _bankaccnt_currid, _check_curr_rate, _bankaccnt_prnt_check
  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;

  IF (NOT _bankaccnt_prnt_check) THEN
    SELECT fetchNextCheckNumber(pBankaccntid) INTO _checknumber;
  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,		_checknumber,
    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;

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE FUNCTION createdoctype(pdocassnum integer, ptype text, pdocass text, pcharass text, pfull text, ptable text, pkey text, pnumber text, pname text, pdesc text, pwidget text DEFAULT ''::text, pjoin text DEFAULT ''::text, pparam text DEFAULT ''::text, pui text DEFAULT ''::text, ppriv text DEFAULT ''::text, pmodule text DEFAULT 'System'::text) RETURNS integer
    LANGUAGE plpgsql
    AS $$
/* Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple.
   See www.xtuple.com/CPAL for the full text of the software license.

   Create a record in the "source" table corresponding to this document type
   or update existing records. The pDocAssNum MUST match the corresponding
   Documents::DocumentSources (qt-client/widgets/documents.h) if the record
   type is there. Otherwise pass NULL or 0 and let the `source` table trigger
   set a value.
   See the source table comments for a description of the other arguments.
 */
declare
  _id integer := -1;
begin
  select source_id into _id
    from source where source_name = pType
    for update;

  if not found then
    insert into source (source_docass_num, source_module,
        source_name,      source_descrip,      source_table,
        source_docass,    source_charass,
        source_key_field, source_number_field, source_name_field,
        source_desc_field, source_joins,       source_widget,
        source_key_param, source_uiform_name,  source_create_priv
      ) values (coalesce(pDocAssNum, 0), pModule,
         pType,   pFull,   pTable,
         pDocAss, pCharAss,
         pKey,    pNumber, pName,
         pDesc,   pJoin,   pWidget,
         pParam,  pUi,     pPriv)
      returning source_id into _id;
  else
    update source
       set source_docass_num   = coalesce(pDocAssNum, 0),
           source_module       = pModule,
           source_docass       = pDocAss,
           source_charass      = pCharass,
           source_descrip      = pFull,
           source_table        = pTable,
           source_key_field    = pKey,
           source_number_field = pNumber,
           source_name_field   = pName,
           source_desc_field   = pDesc,
           source_joins        = pJoin,
           source_widget       = pWidget,
           source_key_param    = pParam,
           source_uiform_name  = pUi,
           source_create_priv  = pPriv
     where source_name = pType
     returning source_id into _id;
  end if;

  return _id;
end;
$$;


ALTER FUNCTION public.createdoctype(pdocassnum integer, ptype text, pdocass text, pcharass text, pfull text, ptable text, pkey text, pnumber text, pname text, pdesc text, pwidget text, pjoin text, pparam text, pui text, ppriv text, pmodule text) OWNER TO admin;

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

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

CREATE FUNCTION createinvoice(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2015 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
                   JOIN charuse ON char_id = charuse_char_id AND charuse_target_type = 'INV'
    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;

--
