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

CREATE FUNCTION explodekit(integer, integer, integer, integer, numeric, 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
  pSoheadid ALIAS FOR $1;
  pLinenumber ALIAS FOR $2;
  pSubnumber ALIAS FOR $3;
  pItemsiteid ALIAS FOR $4;
  pQty ALIAS FOR $5;
  pScheddate ALIAS FOR $6;
  pPromdate ALIAS FOR $7;
BEGIN
  RETURN explodeKit(pSoheadid, pLinenumber, pSubnumber, pItemsiteid, pQty, CURRENT_DATE, NULL, '');
END;
$_$;


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

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

CREATE FUNCTION explodekit(integer, integer, integer, integer, numeric, date, date, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSoheadid ALIAS FOR $1;
  pLinenumber ALIAS FOR $2;
  pSubnumber ALIAS FOR $3;
  pItemsiteid ALIAS FOR $4;
  pQty ALIAS FOR $5;
  pScheddate ALIAS FOR $6;
  pPromdate ALIAS FOR $7;
  pMemo ALIAS FOR $8;
  _subnumber INTEGER := COALESCE(pSubnumber,0);
  _revid INTEGER;
  _itemid INTEGER;
  _warehousid INTEGER;
  _item RECORD;
  _type TEXT;
  _coitemid INTEGER;
  _count INTEGER;
  _orderid INTEGER := 0;
  _itemsrcid INTEGER;
BEGIN

  SELECT getActiveRevId('BOM',itemsite_item_id), itemsite_warehous_id, itemsite_item_id
    INTO _revid, _warehousid, _itemid
    FROM itemsite
   WHERE(itemsite_id=pItemsiteid);
  IF(NOT FOUND) THEN
    RAISE EXCEPTION 'No Item Site for the specified line was found.';
  END IF;

  FOR _item IN
  SELECT bomitem_id, 
         itemsite_id,
         itemsite_warehous_id,
         COALESCE((itemsite_active AND item_active), false) AS active,
         COALESCE((itemsite_sold AND item_sold), false) AS sold,
         item_id,
         item_type,
         item_price_uom_id,
         itemsite_createsopr,itemsite_createwo,itemsite_createsopo, itemsite_dropship,
         bomitem_uom_id,
         itemuomtouomratio(item_id, bomitem_uom_id, item_inv_uom_id) AS invuomratio,
         roundQty(itemuomfractionalbyuom(bomitem_item_id, bomitem_uom_id),(bomitem_qtyfxd + bomitem_qtyper * pQty) * (1 + bomitem_scrap)) AS qty
    FROM bomitem JOIN item ON (item_id=bomitem_item_id)
                  LEFT OUTER JOIN itemsite ON ((itemsite_item_id=item_id) AND (itemsite_warehous_id=_warehousid))
   WHERE((bomitem_parent_item_id=_itemid)
     AND (bomitem_rev_id=_revid)
     AND (CURRENT_DATE BETWEEN bomitem_effective AND (bomitem_expires - 1)))
   ORDER BY bomitem_seqnumber LOOP
    IF (NOT _item.active) THEN
      RAISE EXCEPTION 'One or more of the components for the kit is inactive for the selected item site.';
    ELSIF (NOT _item.sold) THEN
      RAISE EXCEPTION 'One or more of the components for the kit is not sold for the selected item site.';
    ELSIF (_item.item_type='F') THEN
      SELECT explodeKit(pSoheadid, pLinenumber, _subnumber, _item.itemsite_id, _item.qty)
        INTO _subnumber;
    ELSE
      IF (_item.itemsite_createsopr) THEN
        _type := 'R';
      ELSIF (_item.itemsite_createsopo) THEN
        _type := 'P';
      ELSIF (_item.itemsite_createwo) THEN
        _type := 'W';
      ELSE
        _type := NULL;
      END IF;
      _subnumber := _subnumber + 1;
      _coitemid = nextval('coitem_coitem_id_seq');
      raise notice 'coitem id: %',_coitemid;
      INSERT INTO coitem
            (coitem_id, coitem_cohead_id,
             coitem_linenumber, coitem_subnumber,
             coitem_itemsite_id, coitem_status,
             coitem_scheddate, coitem_promdate,
             coitem_qtyord, coitem_qty_uom_id, coitem_qty_invuomratio,
             coitem_qtyshipped, coitem_qtyreturned,
             coitem_unitcost, coitem_custprice,
             coitem_price, coitem_price_uom_id, coitem_price_invuomratio,
             coitem_order_type, coitem_order_id,
             coitem_custpn, coitem_memo,
             coitem_prcost)
      VALUES (_coitemid, pSoheadid,
             pLinenumber, _subnumber,
             _item.itemsite_id, 'O',
             pScheddate, pPromdate,
             _item.qty, _item.bomitem_uom_id, _item.invuomratio,
             0, 0,
             stdCost(_item.item_id), 0,
             0, _item.item_price_uom_id, 1,
             _type, -1,
             '', pMemo,
             0);

      IF (_item.itemsite_createsopr) THEN
        SELECT createPR(cohead_number::INTEGER, 'S', _coitemid) INTO _orderid
        FROM cohead
        WHERE (cohead_id=pSoheadid);
        IF (_orderid > 0) THEN
          UPDATE coitem SET coitem_order_id=_orderid
          WHERE (coitem_id=_coitemid);
        ELSE
          RAISE EXCEPTION 'Could not explode kit. CreatePR failed, result=%', _orderid; 
        END IF;
      END IF;

      IF (_item.itemsite_createsopo) THEN
        SELECT itemsrc_id INTO _itemsrcid
        FROM itemsrc
        WHERE ((itemsrc_item_id=_item.item_id)
        AND (itemsrc_default));

        GET DIAGNOSTICS _count = ROW_COUNT;
        IF (_count > 0) THEN
          PERFORM createPurchaseToSale(_coitemid, _itemsrcid, _item.itemsite_dropship);
        ELSE
          RAISE WARNING 'One or more Kit items are flagged as purchase-to-order for this site, but no default item source is defined.';
        END IF;
      END IF;
     
    END IF;
  END LOOP;

  RETURN _subnumber;
END;
$_$;


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

--
-- TOC entry 1530 (class 1255 OID 146566102)
-- Dependencies: 4536 8
-- Name: explodephantomorder(integer, integer, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION explodephantomorder(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
  pPlanordid ALIAS FOR $1;
  pPhantomid ALIAS FOR $2;
  pQty       ALIAS FOR $3;
  _b RECORD;

BEGIN
  FOR _b IN SELECT planord_number, c.itemsite_id AS componentsiteid,
                   calculatenextworkingdate(c.itemsite_warehous_id, planord_startdate, (c.itemsite_leadtime * -1)) AS startdate,
                   planord_startdate AS duedate,
                   bomitem_createwo, c.itemsite_planning_type AS planningtype,
                   (itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, (bomitem_qtyfxd + pQty * bomitem_qtyper) * (1 + bomitem_scrap))) AS qtyreq,
                   item_type
              FROM bomitem, planord, itemsite AS p, itemsite AS c, item
             WHERE ((bomitem_parent_item_id=p.itemsite_item_id)
               AND (bomitem_rev_id=getActiveRevId('BOM',bomitem_parent_item_id))
               AND (bomitem_item_id=c.itemsite_item_id)
               AND (p.itemsite_warehous_id=c.itemsite_warehous_id)
               AND (c.itemsite_item_id=item_id)
               AND (woEffectiveDate(planord_startdate) BETWEEN bomitem_effective AND (bomitem_expires - 1))
               AND (p.itemsite_id=pPhantomid)
               AND (planord_id=pPlanordid)) LOOP

    IF (_b.item_type = 'F') THEN
      PERFORM explodePhantomOrder(pPlanordid, _b.componentsiteid, _b.qtyreq);
    ELSE
--  Create the Planned Requirement
      INSERT INTO planreq
      ( planreq_source, planreq_source_id,
        planreq_itemsite_id, planreq_qty )
      VALUES
      ( 'P', pPlanordid,
        _b.componentsiteid, _b.qtyreq );

      IF (_b.bomitem_createwo AND _b.planningtype != 'N') THEN
        PERFORM createPlannedOrder( pPlanordid, _b.planord_number, _b.componentsiteid,
                                    _b.qtyreq, _b.startdate, _b.duedate,
                                    FALSE, FALSE, NULL, NULL);
      END IF;
    END IF;

  END LOOP;

  RETURN pPlanordid;
END;
$_$;


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

--
-- TOC entry 1531 (class 1255 OID 146566103)
-- Dependencies: 4536 8
-- Name: explodewo(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION explodewo(integer, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWoid ALIAS FOR $1;
  pExplodeChildren ALIAS FOR $2;
  resultCode INTEGER;
  newWo RECORD;
  _newwoid INTEGER;
  _p RECORD;
  _r RECORD;
  _bbom BOOLEAN;

BEGIN
-- Find out if Breeder BOMs are enabled
  SELECT metric_value='t' INTO _bbom
         FROM metric
         WHERE (metric_name='BBOM');

--  Make sure that this W/O is Open
  SELECT wo_id INTO resultCode
  FROM wo
  WHERE ((wo_status='O')
   AND (wo_id=pWoid));
  IF (NOT FOUND) THEN
    RETURN -4;
  END IF;

--  Make sure that all Component Item Sites exist and are valid
--  Item Sites must be active and not Job Costed
  SELECT bomitem_id INTO resultCode
  FROM wo, bomitem, itemsite
  WHERE ( (wo_itemsite_id=itemsite_id)
   AND (itemsite_item_id=bomitem_parent_item_id)
   AND (woEffectiveDate(wo_startdate) BETWEEN bomitem_effective AND (bomitem_expires - 1))
   AND (wo_id=pWoid)
   AND (bomitem_rev_id=wo_bom_rev_id)
   AND (bomitem_item_id NOT IN
        ( SELECT component.itemsite_item_id
          FROM itemsite AS component, itemsite AS parent
          WHERE ( (wo_itemsite_id=parent.itemsite_id)
           AND (parent.itemsite_item_id=bomitem_parent_item_id)
           AND (bomitem_item_id=component.itemsite_item_id)
           AND (woEffectiveDate(wo_startdate) BETWEEN bomitem_effective AND (bomitem_expires - 1))
           AND (component.itemsite_active)
           AND (component.itemsite_warehous_id=parent.itemsite_warehous_id) ) ) ) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

--  If the Parent Item is a Breeder, make sure that all the
--  Co-Product/By-Product Item Sites exist
  IF (_bbom) THEN

    IF ( ( SELECT (item_type='B')
           FROM wo, itemsite, item
           WHERE ( (wo_itemsite_id=itemsite_id)
            AND (itemsite_item_id=item_id)
            AND (wo_id=pWoid) ) ) ) THEN
      SELECT bbomitem_id INTO resultCode
      FROM wo, xtmfg.bbomitem, itemsite
      WHERE ( (wo_itemsite_id=itemsite_id)
       AND (itemsite_item_id=bbomitem_parent_item_id)
       AND (woEffectiveDate(wo_startdate) BETWEEN bbomitem_effective AND (bbomitem_expires - 1))
       AND (wo_id=pWoid)
       AND (bbomitem_item_id NOT IN
            ( SELECT component.itemsite_item_id
              FROM itemsite AS component, itemsite AS parent
              WHERE ( (wo_itemsite_id=parent.itemsite_id)
               AND (parent.itemsite_item_id=bbomitem_parent_item_id)
               AND (bbomitem_item_id=component.itemsite_item_id)
               AND (woEffectiveDate(wo_startdate) BETWEEN bbomitem_effective AND (bbomitem_expires - 1))
               AND (component.itemsite_active)
               AND (component.itemsite_warehous_id=parent.itemsite_warehous_id) ) ) ) )
      LIMIT 1;
      IF (FOUND) THEN
        RETURN -3;
      END IF;
    END IF;
  END IF;

--  Create the W/O Material Requirements
  INSERT INTO womatl
  ( womatl_wo_id, womatl_bomitem_id, womatl_wooper_id, womatl_schedatwooper,
    womatl_itemsite_id, womatl_duedate,
    womatl_uom_id, womatl_qtyfxd, womatl_qtyper, womatl_scrap,
    womatl_qtyreq,
    womatl_qtyiss, womatl_qtywipscrap,
    womatl_lastissue, womatl_lastreturn, womatl_cost,
    womatl_picklist, womatl_createwo, womatl_issuewo,
    womatl_issuemethod, womatl_notes, womatl_ref,
    womatl_price )
  SELECT wo_id, bomitem_id, bomitem_booitem_seq_id, bomitem_schedatwooper,
         matl_itemsite, duedate,
         bomitem_uom_id, bomitem_qtyfxd, bomitem_qtyper, bomitem_scrap,
         qtyreq, 0, 0,
         startOfTime(), startOfTime(), 0,
         item_picklist, ( (item_type='M') AND (bomitem_createwo) ), issuewo,
         bomitem_issuemethod, bomitem_notes, bomitem_ref,
         CASE WHEN (price=-9999.0) THEN 0.0
              ELSE price
         END
  FROM (SELECT *, cs.itemsite_id AS matl_itemsite,
               CASE WHEN bomitem_schedatwooper THEN COALESCE(calcWooperStartStub(wo_id,bomitem_booitem_seq_id), wo_startdate)
                    ELSE wo_startdate
               END AS duedate,
               roundQty(itemuomfractionalbyuom(bomitem_item_id, bomitem_uom_id), (bomitem_qtyfxd + bomitem_qtyper * wo_qtyord) * (1 + bomitem_scrap)) AS qtyreq,
               CASE WHEN ( (item_type='M') AND (bomitem_issuewo) ) THEN TRUE
                    WHEN (cs.itemsite_costmethod='J') THEN TRUE
                    ELSE FALSE
               END AS issuewo,
               CASE WHEN (cohead_id IS NULL) THEN item_listprice
                    ELSE (SELECT itemprice_price
                          FROM itemIpsPrice(item_id, cohead_cust_id, cohead_shipto_id, 
                                      roundQty(itemuomfractionalbyuom(bomitem_item_id, bomitem_uom_id), (bomitem_qtyfxd + bomitem_qtyper * wo_qtyord) * (1 + bomitem_scrap)),
                                      bomitem_uom_id, bomitem_uom_id, cohead_curr_id, CURRENT_DATE, CURRENT_DATE, cohead_warehous_id) LIMIT 1)
               END AS price
        FROM wo JOIN itemsite ps ON (ps.itemsite_id=wo_itemsite_id)
                JOIN bomitem ON (bomitem_parent_item_id=ps.itemsite_item_id AND
                                 bomitem_rev_id=wo_bom_rev_id AND
                                 woEffectiveDate(wo_startdate) BETWEEN bomitem_effective and (bomitem_expires - 1))
                JOIN itemsite cs ON (cs.itemsite_item_id=bomitem_item_id AND
                                     cs.itemsite_warehous_id=ps.itemsite_warehous_id)
                JOIN item ON (item_id=cs.itemsite_item_id)
                LEFT OUTER JOIN coitem ON (wo_ordtype='S' AND
                                           wo_ordid=coitem_id)
                LEFT OUTER JOIN cohead ON (cohead_id=coitem_cohead_id)
        WHERE ( (wo_id=pWoid)
          AND   ((bomitem_char_id IS NULL)
             OR  EXISTS (
                 SELECT charass_id
                 FROM coitem,charass
                 WHERE ((charass_target_type='SI')
                   AND  (charass_target_id=coitem_id)
                   AND  (charass_char_id=bomitem_char_id)
                   AND  (charass_value=bomitem_value)
                   AND  (wo_ordtype='S')
                   AND  (coitem_id=wo_ordid)))) )
        ) AS data;

--  Update any created P/R's the have the project id as the parent WO.
  UPDATE pr SET pr_prj_id=wo_prj_id
    FROM womatl, wo
   WHERE ((wo_id=pWoid)
     AND  (womatl_wo_id=wo_id)
     AND  (pr_order_type='W')
     AND  (pr_order_id=womatl_id));

--  If the parent Item is a Breeder, create the brddist
--  records for the Co-Products and By-Products
  IF (_bbom) THEN

    INSERT INTO xtmfg.brddist
    ( brddist_wo_id, brddist_wo_qty, brddist_itemsite_id,
      brddist_stdqtyper, brddist_qty, brddist_posted )
    SELECT wo_id, 0, cs.itemsite_id,
           bbomitem_qtyper, 0, FALSE
    FROM wo, xtmfg.bbomitem,
         itemsite AS ps, itemsite AS cs, item
    WHERE ( (bbomitem_parent_item_id=ps.itemsite_item_id)
     AND (wo_itemsite_id=ps.itemsite_id)
     AND (ps.itemsite_item_id=item_id)
     AND (item_type='B')
     AND (bbomitem_item_id=cs.itemsite_item_id)
     AND (cs.itemsite_warehous_id=ps.itemsite_warehous_id)
     AND (wo_id=pWoid) );

  END IF;

--  Insert the W/O Operations if routings enabled
  IF ( ( SELECT (metric_value='t')
         FROM metric
         WHERE (metric_name='Routings') ) ) THEN

    INSERT INTO xtmfg.wooper
    ( wooper_wo_id, wooper_booitem_id, wooper_seqnumber,
      wooper_wrkcnt_id, wooper_stdopn_id,
      wooper_descrip1, wooper_descrip2, wooper_toolref,
      wooper_sutime, wooper_sucosttype, wooper_surpt,
      wooper_rntime, wooper_rncosttype, wooper_rnrpt,
      wooper_rnqtyper,
      wooper_produom, wooper_invproduomratio,
      wooper_issuecomp, wooper_rcvinv,
      wooper_suconsumed, wooper_sucomplete,
      wooper_rnconsumed, wooper_rncomplete,
      wooper_qtyrcv, wooper_instruc, wooper_scheduled,
      wooper_wip_location_id, wooper_price )
    SELECT wo_id, booitem_id, booitem_seqnumber,
           booitem_wrkcnt_id, booitem_stdopn_id,
           booitem_descrip1, booitem_descrip2, booitem_toolref,
           CASE WHEN (booitem_surpt) THEN booitem_sutime
                ELSE 0
           END, booitem_sucosttype, booitem_surpt,
           CASE WHEN ((booitem_rnqtyper = 0) OR (booitem_invproduomratio = 0)) THEN 0
                WHEN (NOT booitem_rnrpt) THEN 0
                ELSE ( ( booitem_rntime /
                         booitem_rnqtyper /
                         booitem_invproduomratio ) * wo_qtyord )
           END, booitem_rncosttype, booitem_rnrpt,
           CASE WHEN (booitem_rnqtyper = 0) THEN 0
                WHEN (NOT booitem_rnrpt) THEN 0
                ELSE (booitem_rntime / booitem_rnqtyper)
           END,
           booitem_produom, booitem_invproduomratio,
           booitem_issuecomp, booitem_rcvinv,
           0::NUMERIC, FALSE,
           0::NUMERIC, FALSE,
           0::NUMERIC, booitem_instruc,
           calculatenextworkingdate(itemsite_warehous_id,wo_startdate,booitem_execday-1),
           booitem_wip_location_id,
           (xtmfg.directlaborcostoper(booitem_id) + xtmfg.overheadcostoper(booitem_id) + xtmfg.machineoverheadcostoper(booitem_id))
    FROM xtmfg.booitem, wo, itemsite
    WHERE ((wo_itemsite_id=itemsite_id)
     AND (itemsite_item_id=booitem_item_id)
     AND (booitem_rev_id=wo_boo_rev_id)
     AND (woEffectiveDate(wo_startdate) BETWEEN booitem_effective AND (booitem_expires - 1))
     AND (wo_id=pWoid));

--  Update womatls item to link to wooper items when the respective
--  bomitem record indicates a booitem issue link.
    UPDATE womatl
    SET womatl_wooper_id=wooper_id
    FROM wo,xtmfg.wooper,xtmfg.booitem
    WHERE ((womatl_wooper_id=booitem_seq_id)
     AND (wooper_booitem_id=booitem_id)
     AND (womatl_wo_id=wo_id)
     AND (wooper_wo_id=wo_id)
     AND (wo_boo_rev_id=booitem_rev_id)
     AND (wo_id=pWoid));
    END IF;

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

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

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

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

    END LOOP;
  END LOOP;

--  Create W/Os for manufactured component items
  FOR newWo IN SELECT wo_number, nextWoSubnumber(wo_number) AS nextSubnumber,
                      itemsite_id, itemsite_leadtime, womatl_duedate,
                      womatl_wo_id, womatl_qtyreq, womatl_uom_id, wo_prj_id,
                      item_id, item_inv_uom_id, womatl_id
               FROM womatl, wo, itemsite, item
               WHERE ( (womatl_wo_id=wo_id)
                AND (womatl_itemsite_id=itemsite_id)
                AND (womatl_createwo)
                AND (itemsite_wosupply)
                AND (itemsite_item_id=item_id)
                AND (wo_id=pWoid) )
               ORDER BY womatl_id LOOP

    SELECT createWo( newWo.wo_number, newWo.itemsite_id, 1, 
                     itemuomtouom(newWo.item_id,newWo.womatl_uom_id,newWo.item_inv_uom_id,newWo.womatl_qtyreq),
                      newWo.itemsite_leadtime, newWo.womatl_duedate, '',
                      'W', newWo.womatl_wo_id, newWo.wo_prj_id ) INTO _newwoid;

    UPDATE wo SET wo_womatl_id = newWo.womatl_id WHERE wo_id=_newwoid;

  END LOOP;

  UPDATE wo
  SET wo_status='E', wo_adhoc=FALSE
  WHERE (wo_id=pWoid);

  IF (pExplodeChildren) THEN
    SELECT MAX(explodeWo(wo_id, TRUE)) INTO resultCode
    FROM wo
    WHERE ( (wo_ordtype='W')
     AND (wo_ordid=pWoid) );
  END IF;

  RETURN pWoid;
END;
$_$;


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

--
-- TOC entry 1532 (class 1255 OID 146566105)
-- Dependencies: 4536 8
-- Name: explodewoeffective(); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT metric_value INTO _value
  FROM metric
  WHERE (metric_name='ExplodeWOEffective');

  RETURN _value;

END;
$$;


ALTER FUNCTION public.explodewoeffective() OWNER TO admin;

--
-- TOC entry 1533 (class 1255 OID 146566106)
-- Dependencies: 8
-- Name: fetchapmemonumber(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchapmemonumber() RETURNS integer
    LANGUAGE sql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT fetchNextNumber('APMemoNumber')::INTEGER;
$$;


ALTER FUNCTION public.fetchapmemonumber() OWNER TO admin;

--
-- TOC entry 1534 (class 1255 OID 146566107)
-- Dependencies: 8
-- Name: fetcharmemonumber(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetcharmemonumber() RETURNS text
    LANGUAGE sql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT fetchNextNumber('ARMemoNumber');
$$;


ALTER FUNCTION public.fetcharmemonumber() OWNER TO admin;

--
-- TOC entry 1535 (class 1255 OID 146566108)
-- Dependencies: 8
-- Name: fetchcashrcptnumber(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchcashrcptnumber() RETURNS text
    LANGUAGE sql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT fetchNextNumber('CashRcptNumber');
$$;


ALTER FUNCTION public.fetchcashrcptnumber() OWNER TO admin;

--
-- TOC entry 1536 (class 1255 OID 146566109)
-- Dependencies: 8
-- Name: fetchcmnumber(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchcmnumber() RETURNS text
    LANGUAGE sql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT fetchNextNumber('CmNumber');
$$;


ALTER FUNCTION public.fetchcmnumber() OWNER TO admin;

--
-- TOC entry 1537 (class 1255 OID 146566110)
-- Dependencies: 8
-- Name: fetchcrmaccountnumber(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchcrmaccountnumber() RETURNS integer
    LANGUAGE sql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT fetchNextNumber('CRMAccountNumber')::INTEGER;
$$;


ALTER FUNCTION public.fetchcrmaccountnumber() OWNER TO admin;

--
-- TOC entry 1538 (class 1255 OID 146566111)
-- Dependencies: 8
-- Name: fetchdefaultfob(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchdefaultfob(pwarehousid integer) RETURNS text
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT warehous_fob
    FROM whsinfo
   WHERE (warehous_id=$1);
$_$;


ALTER FUNCTION public.fetchdefaultfob(pwarehousid integer) OWNER TO admin;

--
-- TOC entry 1539 (class 1255 OID 146566112)
-- Dependencies: 4536 8
-- Name: fetchdefaultshipvia(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchdefaultshipvia() RETURNS text
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal TEXT;
BEGIN
  SELECT shipvia_code INTO _returnVal
  FROM shipvia
  WHERE shipvia_id=
	(SELECT CAST(metric_value AS integer)
	FROM metric
	WHERE metric_name = 'DefaultShipViaId');
  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.fetchdefaultshipvia() OWNER TO admin;

--
-- TOC entry 1540 (class 1255 OID 146566113)
-- Dependencies: 4536 8
-- Name: fetchglsequence(); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

    SELECT NEXTVAL('gltrans_sequence_seq') INTO _sequence;
    RETURN _sequence;

END;
$$;


ALTER FUNCTION public.fetchglsequence() OWNER TO admin;

--
-- TOC entry 1541 (class 1255 OID 146566114)
-- Dependencies: 8
-- Name: fetchincidentnumber(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchincidentnumber() RETURNS integer
    LANGUAGE sql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT fetchNextNumber('IncidentNumber')::integer;
$$;


ALTER FUNCTION public.fetchincidentnumber() OWNER TO admin;

--
-- TOC entry 1542 (class 1255 OID 146566115)
-- Dependencies: 8
-- Name: fetchinvcnumber(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchinvcnumber() RETURNS integer
    LANGUAGE sql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT fetchNextNumber('InvcNumber')::integer;
$$;


ALTER FUNCTION public.fetchinvcnumber() OWNER TO admin;

--
-- TOC entry 1543 (class 1255 OID 146566116)
-- Dependencies: 4536 8
-- Name: fetchitemuomconvtypes(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  _cnt := 0;

  FOR _p IN SELECT
    uomtype_name
  FROM itemuomconv, itemuom, uomtype
  WHERE ((itemuomconv_id=pItemUomConvId)
  AND (itemuomconv_id=itemuom_itemuomconv_id)
  AND (itemuom_uomtype_id=uomtype_id))
  LOOP
    _result[_cnt] := _p.uomtype_name; 
    _cnt := _cnt + 1;
  END LOOP;

  RETURN _result;
END;
$_$;


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

--
-- TOC entry 1544 (class 1255 OID 146566117)
-- Dependencies: 4536 8
-- Name: fetchjournalnumber(text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT nextval('journal_number_seq') INTO _number;

  INSERT INTO jrnluse
  (jrnluse_date, jrnluse_number, jrnluse_use)
  VALUES
  (CURRENT_TIMESTAMP, _number, pUse);

  RETURN _number;
  
END;
$_$;


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

--
-- TOC entry 1545 (class 1255 OID 146566118)
-- Dependencies: 4536 8
-- Name: fetchmetricbool(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchmetricbool(text) RETURNS boolean
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _pMetricName ALIAS FOR $1;
  _returnVal BOOLEAN;
BEGIN
  SELECT CASE 
    WHEN MIN(metric_value) = 't' THEN
     true
    ELSE
     false
    END INTO _returnVal
    FROM metric
   WHERE metric_name = _pMetricName;
  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1546 (class 1255 OID 146566119)
-- Dependencies: 4536 8
-- Name: fetchmetrictext(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchmetrictext(text) RETURNS text
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _pMetricName ALIAS FOR $1;
  _returnVal TEXT;
BEGIN
  SELECT metric_value::TEXT INTO _returnVal
    FROM metric
   WHERE metric_name = _pMetricName;
  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1547 (class 1255 OID 146566120)
-- Dependencies: 4536 8
-- Name: fetchmetricvalue(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchmetricvalue(text) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _pMetricName ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  SELECT CASE WHEN (isNumeric(metric_value)) THEN metric_value::INTEGER
              ELSE NULL
         END INTO _returnVal
    FROM metric
   WHERE metric_name = _pMetricName;
  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1548 (class 1255 OID 146566121)
-- Dependencies: 4536 8
-- Name: fetchnextchecknumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchnextchecknumber(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;
  _nextChkNumber INTEGER;

BEGIN

  SELECT bankaccnt_nextchknum INTO _nextChkNumber
  FROM bankaccnt
  WHERE (bankaccnt_id=pBankaccntid);

  UPDATE bankaccnt
  SET bankaccnt_nextchknum = (bankaccnt_nextchknum + 1)
  WHERE (bankaccnt_id=pBankaccntid);

  RETURN _nextChkNumber;

END;
$_$;


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

--
-- TOC entry 1549 (class 1255 OID 146566122)
-- Dependencies: 4536 8
-- Name: fetchnextnumber(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchnextnumber(text) RETURNS text
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  psequence	ALIAS FOR $1;
  _number	TEXT;
  _numcol	TEXT;
  _select	TEXT;
  _table	TEXT;
  _test		TEXT;
  _nextnum	INTEGER;
  _seqiss       seqiss;
  __seqiss      seqiss[];
  _not_issued       BOOLEAN;

BEGIN
  SELECT CAST(orderseq_number AS text), orderseq_number, orderseq_table, orderseq_numcol, COALESCE(orderseq_seqiss, ARRAY[]::seqiss[])
    INTO _number, _nextnum, _table, _numcol, __seqiss
  FROM orderseq
  WHERE (orderseq_name=psequence) FOR UPDATE;

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Invalid orderseq_name %', psequence;
  END IF;
  
  LOOP

    _seqiss := (_nextnum, now());

    SELECT count(*) = 0 INTO _not_issued
    FROM (SELECT UNNEST(__seqiss) AS issued) data
    WHERE (issued).seqiss_number = _nextnum;

    _nextnum := _nextnum + 1;

    -- Test if the number has been issued, but not committed
    IF (_not_issued) THEN

      -- Test if the number has been committed
      _select := 'SELECT ' || quote_ident(_numcol) ||
	         ' FROM '  || quote_ident(_table) ||
	         ' WHERE (' || quote_ident(_numcol) || '=' ||
                 quote_literal(_number) || ');';

      EXECUTE _select INTO _test;

      IF (_test IS NULL OR NOT FOUND) THEN
        EXIT;
      END IF;

    END IF;

    -- Number in use, try again
    _number = _nextnum::text;

  END LOOP;

  UPDATE orderseq SET 
    orderseq_number = _nextnum
  WHERE (orderseq_name=psequence);

  IF (fetchMetricBool('EnableGaplessNumbering')) THEN
    UPDATE orderseq SET 
      orderseq_seqiss = orderseq_seqiss || _seqiss
    WHERE (orderseq_name=psequence);
  END IF;

  RETURN _number;

END;
$_$;


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

--
-- TOC entry 1550 (class 1255 OID 146566123)
-- Dependencies: 8
-- Name: fetchponumber(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchponumber() RETURNS text
    LANGUAGE sql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT fetchNextNumber('PoNumber');
$$;


ALTER FUNCTION public.fetchponumber() OWNER TO admin;

--
-- TOC entry 1551 (class 1255 OID 146566124)
-- Dependencies: 4536 8
-- Name: fetchprefwarehousid(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchprefwarehousid() RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (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 CAST(usrpref_value AS INTEGER) INTO _result
    FROM usrpref
    WHERE ((usrpref_username=getEffectiveXtUser())
    AND (usrpref_name='PreferredWarehouse'));

    RETURN _result;
END;
$$;


ALTER FUNCTION public.fetchprefwarehousid() OWNER TO admin;

--
-- TOC entry 1552 (class 1255 OID 146566125)
-- Dependencies: 8
-- Name: fetchprnumber(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchprnumber() RETURNS integer
    LANGUAGE sql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT fetchNextNumber('PrNumber')::INTEGER;
$$;


ALTER FUNCTION public.fetchprnumber() OWNER TO admin;

--
-- TOC entry 1553 (class 1255 OID 146566126)
-- Dependencies: 8
-- Name: fetchqunumber(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchqunumber() RETURNS text
    LANGUAGE sql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT fetchNextNumber('QuNumber');
$$;


ALTER FUNCTION public.fetchqunumber() OWNER TO admin;

--
-- TOC entry 1554 (class 1255 OID 146566127)
-- Dependencies: 4536 8
-- Name: fetchshipmentnumber(); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  LOOP

    SELECT CAST(nextval('shipment_number_seq') AS TEXT) INTO _number;
    
    SELECT shiphead_id INTO _test
      FROM shiphead
     WHERE (shiphead_number=_number);
    IF (NOT FOUND) THEN
      EXIT;
    END IF;

  END LOOP;

  RETURN _number;
  
END;
$$;


ALTER FUNCTION public.fetchshipmentnumber() OWNER TO admin;

--
-- TOC entry 1555 (class 1255 OID 146566128)
-- Dependencies: 8
-- Name: fetchsonumber(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchsonumber() RETURNS text
    LANGUAGE sql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT fetchNextNumber('SoNumber');
$$;


ALTER FUNCTION public.fetchsonumber() OWNER TO admin;

--
-- TOC entry 1556 (class 1255 OID 146566129)
-- Dependencies: 8
-- Name: fetchtonumber(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchtonumber() RETURNS text
    LANGUAGE sql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT fetchNextNumber('ToNumber');
$$;


ALTER FUNCTION public.fetchtonumber() OWNER TO admin;

--
-- TOC entry 1557 (class 1255 OID 146566130)
-- Dependencies: 4536 8
-- Name: fetchusrprefbool(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchusrprefbool(text) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _pPrefName ALIAS FOR $1;
  _returnVal BOOLEAN;
BEGIN
  SELECT CASE 
    WHEN MIN(usrpref_value) = 't' THEN
     true
    ELSE
     false
    END INTO _returnVal
  FROM usrpref
  WHERE ( (usrpref_username=getEffectiveXtUser())
    AND   (usrpref_name=_pPrefName) );
  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1558 (class 1255 OID 146566131)
-- Dependencies: 8
-- Name: fetchvonumber(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchvonumber() RETURNS integer
    LANGUAGE sql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT fetchNextNumber('VcNumber')::INTEGER;
$$;


ALTER FUNCTION public.fetchvonumber() OWNER TO admin;

--
-- TOC entry 1559 (class 1255 OID 146566132)
-- Dependencies: 8
-- Name: fetchwonumber(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fetchwonumber() RETURNS integer
    LANGUAGE sql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT fetchNextNumber('WoNumber')::INTEGER;
$$;


ALTER FUNCTION public.fetchwonumber() OWNER TO admin;

--
-- TOC entry 1560 (class 1255 OID 146566133)
-- Dependencies: 4536 8
-- Name: financialreport(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT financialreport(pFlheadid,pPeriodid,'M', pPrjid) INTO _result;

  RETURN _result;
END;
$_$;


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

--
-- TOC entry 1561 (class 1255 OID 146566134)
-- Dependencies: 4536 8
-- Name: financialreport(integer, integer, character, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  _r RECORD;
  _t RECORD;
  _s RECORD;

BEGIN

-- Validate Interval
   IF pInterval <> 'M' AND pInterval <> 'Q' AND pInterval <> 'Y' THEN
     RAISE EXCEPTION 'Invalid Interval --> %', pInterval;
   END IF;

-- Get rid of any old reporting done by this user for the specified criteria
  DELETE FROM flrpt
   WHERE ((flrpt_flhead_id=pFlheadid)
     AND  (flrpt_period_id=pPeriodId)
     AND  (flrpt_interval=pInterval)
     AND  (flrpt_username=getEffectiveXtUser()));

-- Find out if we need to show a Grand Total and which if any of the values
-- we want to show in that grand total.
  SELECT flhead_showtotal,
         CASE WHEN(flhead_showstart) THEN 0.00
              ELSE NULL
         END AS beginning,
         CASE WHEN(flhead_showend) THEN 0.00
              ELSE NULL
         END AS ending,
         CASE WHEN(flhead_showdelta) THEN 0.00
              ELSE NULL
         END AS debits,
         CASE WHEN(flhead_showdelta) THEN 0.00
              ELSE NULL
         END AS credits,
         CASE WHEN(flhead_showbudget) THEN 0.00
              ELSE NULL
         END AS budget,
         CASE WHEN(flhead_showdiff) THEN 0.00
              ELSE NULL
         END AS diff,
         CASE WHEN(flhead_showcustom) THEN 0.00
              ELSE NULL
         END AS custom,
         CASE WHEN(flhead_usealttotal) THEN flhead_alttotal
              ELSE NULL
         END AS altname INTO _r
    FROM flhead
   WHERE (flhead_id=pFlheadid);
  IF (NOT FOUND) THEN
    return FALSE;
  END IF;

-- If showing a Grand Total then create a record as a Group which acts
-- as a parent to the whole report. This allows the code to update as
-- it would for normal group total values.
  IF (_r.flhead_showtotal) THEN
    INSERT INTO flrpt
           (flrpt_flhead_id, flrpt_period_id, flrpt_username,
            flrpt_order, flrpt_level, flrpt_type, flrpt_type_id,
            flrpt_beginning, flrpt_ending,
            flrpt_debits, flrpt_credits, flrpt_budget, flrpt_diff,
            flrpt_custom, flrpt_altname, flrpt_interval )
    VALUES (pFlheadid, pPeriodid, getEffectiveXtUser(),
            0, -1, 'G', -1,
            _r.beginning, _r.ending,
            _r.debits, _r.credits, _r.budget, _r.diff,
            _r.custom, _r.altname, pInterval );
  END IF;

  PERFORM insertFlGroup(pFlheadid, pPeriodid, -1, 0, FALSE, pInterval, pPrjid);

-- go through the list of records that need percentages calculated and perform
-- those calculations.
  FOR _t IN SELECT flrpt_order, CASE WHEN(flgrp_prcnt_flgrp_id = -1) THEN flgrp_flgrp_id ELSE flgrp_prcnt_flgrp_id END AS flgrp_id
              FROM flrpt, flgrp
             WHERE ((flrpt_flhead_id=pFlheadid)
               AND  (flrpt_period_id=pPeriodid)
               AND  (flrpt_interval=pInterval)
               AND  (flrpt_username=getEffectiveXtUser())
               AND  (flrpt_type='G')
               AND  (flrpt_type_id=flgrp_id))
             UNION
            SELECT flrpt_order, CASE WHEN(flitem_prcnt_flgrp_id = -1) THEN flitem_flgrp_id ELSE flitem_prcnt_flgrp_id END AS flgrp_id
              FROM flrpt, flitem
             WHERE ((flrpt_flhead_id=pFlheadid)
               AND  (flrpt_period_id=pPeriodid)
               AND  (flrpt_interval=pInterval)
               AND  (flrpt_username=getEffectiveXtUser())
               AND  (flrpt_type='I')
               AND  (flrpt_type_id=flitem_id))
             UNION
            SELECT flrpt_order, CASE WHEN(flspec_prcnt_flgrp_id = -1) THEN flspec_flgrp_id ELSE flspec_prcnt_flgrp_id END AS flgrp_id
              FROM flrpt, flspec
             WHERE ((flrpt_flhead_id=pFlheadid)
               AND  (flrpt_period_id=pPeriodid)
               AND  (flrpt_interval=pInterval)
               AND  (flrpt_username=getEffectiveXtUser())
               AND  (flrpt_type='S')
               AND  (flrpt_type_id=flspec_id)) LOOP

    IF( (_t.flgrp_id=-1) OR (NOT (SELECT flgrp_summarize FROM flgrp WHERE flgrp_id=_t.flgrp_id)) ) THEN
      SELECT COALESCE(SUM(flrpt_beginning),0) AS beginningTotal,
             COALESCE(SUM(flrpt_ending),0) AS endingTotal,
             COALESCE(SUM(flrpt_debits),0) AS debitsTotal,
             COALESCE(SUM(flrpt_credits),0) AS creditsTotal,
             COALESCE(SUM(flrpt_budget),0) AS budgetTotal,
             COALESCE(SUM(flrpt_diff), 0) AS diffTotal,
             COALESCE(SUM(flrpt_custom), 0) AS customTotal INTO _s
        FROM flrpt
       WHERE ((flrpt_flhead_id=pFlheadid)
         AND  (flrpt_period_id=pPeriodid)
         AND  (flrpt_interval=pInterval)
         AND  (flrpt_username=getEffectiveXtUser())
         AND  (flrpt_type != 'T')
         AND  (flrpt_parent_id=_t.flgrp_id));
    ELSE
      SELECT COALESCE(SUM(flrpt_beginning),0) AS beginningTotal,
             COALESCE(SUM(flrpt_ending),0) AS endingTotal,
             COALESCE(SUM(flrpt_debits),0) AS debitsTotal,
             COALESCE(SUM(flrpt_credits),0) AS creditsTotal,
             COALESCE(SUM(flrpt_budget),0) AS budgetTotal,
             COALESCE(SUM(flrpt_diff), 0) AS diffTotal,
             COALESCE(SUM(flrpt_custom), 0) AS customTotal INTO _s
        FROM flrpt
       WHERE ((flrpt_flhead_id=pFlheadid)
         AND  (flrpt_period_id=pPeriodid)
         AND  (flrpt_interval=pInterval)
         AND  (flrpt_username=getEffectiveXtUser())
         AND  (flrpt_type = 'G')
         AND  (flrpt_type_id=_t.flgrp_id));
    END IF;

    UPDATE flrpt SET flrpt_beginningprcnt = flrpt_beginningprcnt + flrpt_beginning / CASE WHEN (_s.beginningTotal=0) THEN 1 ELSE _s.beginningTotal END,
                     flrpt_endingprcnt = flrpt_endingprcnt + flrpt_ending / CASE WHEN (_s.endingTotal=0) THEN 1 ELSE _s.endingTotal END,
                     flrpt_debitsprcnt = flrpt_debitsprcnt + flrpt_debits / CASE WHEN (_s.debitsTotal=0) THEN 1 ELSE _s.debitsTotal END,
                     flrpt_creditsprcnt = flrpt_creditsprcnt + flrpt_credits / CASE WHEN (_s.creditsTotal=0) THEN 1 ELSE _s.creditsTotal END,
                     flrpt_budgetprcnt = flrpt_budgetprcnt + flrpt_budget / CASE WHEN (_s.budgetTotal=0) THEN 1 ELSE _s.budgetTotal END,
                     flrpt_diffprcnt = flrpt_diffprcnt + flrpt_diff / CASE WHEN (_s.diffTotal=0) THEN 1 ELSE _s.diffTotal END,
                     flrpt_customprcnt = flrpt_customprcnt + flrpt_custom / CASE WHEN (_s.customTotal=0) THEN 1 ELSE _s.customTotal END
     WHERE ((flrpt_flhead_id=pFlheadid)
       AND  (flrpt_period_id=pPeriodid)
       AND  (flrpt_interval=pInterval)
       AND  (flrpt_username=getEffectiveXtUser())
       AND  (flrpt_order=_t.flrpt_order));
  END LOOP;


-- Update any subtotal records to reflect the percentage values of the parents
-- since those are calculated after the subtotal records were created.
  FOR _t IN SELECT a.flrpt_order AS flrpt_order,
                   b.flrpt_beginningprcnt AS flrpt_beginningprcnt,
                   b.flrpt_endingprcnt AS flrpt_endingprcnt,
                   b.flrpt_debitsprcnt AS flrpt_debitsprcnt,
                   b.flrpt_creditsprcnt AS flrpt_creditsprcnt,
                   b.flrpt_budgetprcnt AS flrpt_budgetprcnt,
                   b.flrpt_diffprcnt AS flrpt_diffprcnt,
                   b.flrpt_customprcnt AS flrpt_customprcnt
              FROM flrpt AS a, flrpt AS b
             WHERE ((a.flrpt_flhead_id=pFlheadid)
               AND  (a.flrpt_period_id=pPeriodid)
               AND  (a.flrpt_interval=pInterval)
               AND  (a.flrpt_username=getEffectiveXtUser())
               AND  (a.flrpt_type='T')
               AND  (b.flrpt_flhead_id=a.flrpt_flhead_id)
               AND  (b.flrpt_period_id=a.flrpt_period_id)
               AND  (b.flrpt_interval=pInterval)
               AND  (b.flrpt_username=a.flrpt_username)
               AND  (b.flrpt_type='G')
               AND  (b.flrpt_type_id=a.flrpt_parent_id)) LOOP
    UPDATE flrpt SET flrpt_beginningprcnt=flrpt_beginningprcnt + _t.flrpt_beginningprcnt,
                     flrpt_endingprcnt=flrpt_endingprcnt + _t.flrpt_endingprcnt,
                     flrpt_debitsprcnt=flrpt_debitsprcnt + _t.flrpt_debitsprcnt,
                     flrpt_creditsprcnt=flrpt_creditsprcnt + _t.flrpt_creditsprcnt,
                     flrpt_budgetprcnt=flrpt_budgetprcnt + _t.flrpt_budgetprcnt,
                     flrpt_diffprcnt=flrpt_diffprcnt + _t.flrpt_diffprcnt,
                     flrpt_customprcnt=flrpt_customprcnt + _t.flrpt_customprcnt
               WHERE ((flrpt_flhead_id=pFlheadid)
                 AND  (flrpt_period_id=pPeriodid)
                 AND  (flrpt_interval=pInterval)
                 AND  (flrpt_username=getEffectiveXtUser())
                 AND  (flrpt_order=_t.flrpt_order));
  END LOOP;

-- If showing a Grand total then move the record we created early to the
-- end of the report and marked as a Total record.
  IF (_r.flhead_showtotal) THEN
    UPDATE flrpt
       SET flrpt_order = COALESCE((SELECT MAX(flrpt_order)
                                     FROM flrpt
                                    WHERE ((flrpt_flhead_id=pFlheadid)
                                      AND  (flrpt_period_id=pPeriodid)
                                      AND  (flrpt_interval=pInterval)
                                      AND  (flrpt_username=getEffectiveXtUser()))
                                  ), 0) + 1,
           flrpt_level = 0,
           flrpt_type = 'T'
     WHERE ((flrpt_flhead_id=pFlheadid)
       AND  (flrpt_period_id=pPeriodid)
       AND  (flrpt_interval=pInterval)
       AND  (flrpt_username=getEffectiveXtUser())
       AND  (flrpt_order=0)
       AND  (flrpt_level = -1)
       AND  (flrpt_type = 'G')
       AND  (flrpt_type_id=-1));
  END IF;

  return TRUE;
END;
$_$;


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

--
-- TOC entry 1463 (class 1255 OID 146566136)
-- Dependencies: 2678 4536 8
-- Name: financialreport(integer, integer[], character, boolean, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION financialreport(integer, integer[], character, boolean, integer) RETURNS SETOF fltrenditem
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pFlheadId ALIAS FOR $1;
  pPeriodIds ALIAS FOR $2;
  pInterval ALIAS FOR $3;
  pShowNumbers ALIAS FOR $4;
  pPrjid ALIAS FOR $5;
  _row fltrenditem%ROWTYPE;
  _type CHAR;
  _p RECORD;
  _count INTEGER;
  _n NUMERIC;
  _fld NUMERIC[];
  _grndttl NUMERIC;
  _i INTEGER;
  _first BOOLEAN;
  _prevlevel INTEGER;
  _subgrp INTEGER;

BEGIN
        _first := true;
        _subgrp := 0;

        IF ARRAY_UPPER(pPeriodIds,1) <= 12 THEN
                _count := ARRAY_UPPER(pPeriodIds,1);
        ELSE
                _count := 12;
        END IF;

        --Get Type
        SELECT flhead_type FROM flhead INTO _type WHERE flhead_id=pFlheadId;

        --Build Financial Data
        FOR _i IN 1.._count
        LOOP
                PERFORM financialreport(pFlheadId,pPeriodIds[_i],pInterval,pPrjid);
        END LOOP;

        --Get Row Data
        FOR _p IN
        SELECT flrpt_flhead_id,
                flrpt_username,
                flrpt_order,
                flrpt_level,
                flrpt_type,
                flrpt_type_id,
                flrpt_parent_id,
                flrpt_accnt_id,
                formatindent(flgrp.flgrp_name,flrpt.flrpt_level) AS flrpt_name,
                CASE
                        WHEN (flgrp_summarize AND (_type IN ('I','C'))) THEN
                                (COALESCE(flrpt_diff,0))
                        WHEN (flgrp_summarize AND (_type = 'B')) THEN
                                (COALESCE(flrpt_ending,0))
                        ELSE NULL
                END AS f_fld1,
                flgrp_summarize AS display
        FROM flrpt,flgrp
        WHERE ((flrpt_flhead_id=pFlheadId)
        AND (flgrp_id=flrpt_type_id)
        AND (flrpt_type='G')
        AND (flrpt_period_id=pPeriodIds[1])
        AND (flrpt_interval=pInterval)
        AND (flrpt_username=getEffectiveXtUser()))
        UNION
        SELECT flrpt_flhead_id,
                flrpt_username,
                flrpt_order,
                flrpt_level,
                flrpt_type,
                flrpt_type_id,
                flrpt_parent_id,
                flrpt_accnt_id,
                formatindent(accnt_descrip,flrpt.flrpt_level) AS flrpt_name,
                CASE
                        WHEN (_type IN ('I','C')) THEN
                                (COALESCE(flrpt_diff,0))
                        WHEN (_type = 'B') THEN
                                (COALESCE(flrpt_ending,0))
                        ELSE NULL
                END AS f_fld1,
                true AS display
        FROM flrpt,flitem,accnt
        WHERE ((flrpt_flhead_id=pFlheadId)
        AND (flrpt_accnt_id=accnt_id)
        AND (flitem_id=flrpt_type_id)
        AND (flrpt_type='I')
        AND (flrpt_period_id=pPeriodIds[1])
        AND (flrpt_interval=pInterval)
        AND (flrpt_username=getEffectiveXtUser()))
        UNION
        SELECT flrpt_flhead_id,
                flrpt_username,
                flrpt_order,
                flrpt_level,
                flrpt_type,
                flrpt_type_id,
                flrpt_parent_id,
                flrpt_accnt_id,
                CASE
                        WHEN (flrpt.flrpt_type='T' AND flrpt.flrpt_level=0) THEN
                                COALESCE(flrpt.flrpt_altname, 'Total')
                        WHEN (flrpt.flrpt_type='T') THEN
                                formatindent(COALESCE(flrpt.flrpt_altname, 'Subtotal') ,flrpt.flrpt_level) 

                        ELSE
                                formatindent(('Type ' || flrpt.flrpt_type || ' ' || text(flrpt.flrpt_type_id)), flrpt.flrpt_level)
                END AS flstmtitem_name,
                CASE
                        WHEN (_type IN ('I','C')) THEN
                                (COALESCE(flrpt_diff,0))
                        WHEN (_type = 'B') THEN
                                (COALESCE(flrpt_ending,0))
                        ELSE NULL
                END AS f_fld1,
                true AS display
        FROM flrpt
        WHERE ((flrpt_flhead_id=pFlheadId)
        AND (flrpt_type NOT IN ('I','S','G'))
        AND (flrpt_period_id=pPeriodIds[1])
        AND (flrpt_interval=pInterval)
        AND (flrpt_username=getEffectiveXtUser()))
        ORDER BY flrpt_order
        LOOP

                IF _type IN ('I','C') THEN
                        _grndttl := _p.f_fld1;
                END IF;

                --Loop through and calculate period column values
                IF (_p.display) THEN
                        FOR _i IN 2.._count
                        LOOP
                                SELECT
                                CASE
                                        WHEN (_type IN ('I','C')) THEN
                                                COALESCE(flrpt_diff,0)
                                        WHEN (_type = 'B') THEN
                                                COALESCE(flrpt_ending,0)
                                        ELSE NULL
                                END INTO _n
                                FROM flrpt
                                WHERE ((flrpt_flhead_id=pFlheadId)
                                AND (flrpt_period_id=pPeriodIds[_i])
                                AND (flrpt_interval=pInterval)
                                AND (flrpt_username=getEffectiveXtUser())
                                AND (flrpt_order=_p.flrpt_order));
                                _fld[_i-1] := _n;
                                IF _type IN ('I','C') THEN
                                        _grndttl := _grndttl+_n;
                                END IF;
                        END LOOP;
                END IF;

                --Send it all back to the caller
                IF _prevlevel > _p.flrpt_level THEN
                        _subgrp := _subgrp+1;
                END IF;
                _prevlevel:=_p.flrpt_level;
                _row.fltrenditem_subgrp := _subgrp;

                IF NOT _first THEN
                        RETURN NEXT _row;
                END IF;

                _first := FALSE;

                _row.fltrenditem_flhead_id := _p.flrpt_flhead_id;
                _row.fltrenditem_username := _p.flrpt_username;
                _row.fltrenditem_order := _p.flrpt_order;
                _row.fltrenditem_level := _p.flrpt_level;
                _row.fltrenditem_type := _p.flrpt_type;
                _row.fltrenditem_type_id := _p.flrpt_type_id;
                _row.fltrenditem_parent_id := _p.flrpt_parent_id;
                _row.fltrenditem_accnt_id := _p.flrpt_accnt_id;
                _row.fltrenditem_name := _p.flrpt_name;
                IF (_p.display) THEN
                        _row.fltrenditem_fld1 := (_p.f_fld1);
                        _row.fltrenditem_fld2 := (_fld[1]);
                        _row.fltrenditem_fld3 := (_fld[2]);
                        _row.fltrenditem_fld4 := (_fld[3]);
                        _row.fltrenditem_fld5 := (_fld[4]);
                        _row.fltrenditem_fld6 := (_fld[5]);
                        _row.fltrenditem_fld7 := (_fld[6]);
                        _row.fltrenditem_fld8 := (_fld[7]);
                        _row.fltrenditem_fld9 := (_fld[8]);
                        _row.fltrenditem_fld10 := (_fld[9]);
                        _row.fltrenditem_fld11 := (_fld[10]);
                        _row.fltrenditem_fld12 := (_fld[11]);
                        _row.fltrenditem_grndttl := (_grndttl);
                ELSE
                        _row.fltrenditem_fld1 := NULL;
                        _row.fltrenditem_fld2 := NULL;
                        _row.fltrenditem_fld3 := NULL;
                        _row.fltrenditem_fld4 := NULL;
                        _row.fltrenditem_fld5 := NULL;
                        _row.fltrenditem_fld6 := NULL;
                        _row.fltrenditem_fld7 := NULL;
                        _row.fltrenditem_fld8 := NULL;
                        _row.fltrenditem_fld9 := NULL;
                        _row.fltrenditem_fld10 := NULL;
                        _row.fltrenditem_fld11 := NULL;
                        _row.fltrenditem_fld12 := NULL;
                        _row.fltrenditem_grndttl := NULL;
                END IF;

        END LOOP;

        _row.fltrenditem_subgrp := _subgrp + 1;
        RETURN NEXT _row;

END;
$_$;


ALTER FUNCTION public.financialreport(integer, integer[], character, boolean, integer) OWNER TO admin;

--
-- TOC entry 1478 (class 1255 OID 146566138)
-- Dependencies: 2672 4536 8
-- Name: financialreport(integer, integer, boolean, boolean, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION financialreport(integer, integer, boolean, boolean, integer) RETURNS SETOF flstmtitem
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pFlcolid ALIAS FOR $1;
  pPeriodid ALIAS FOR $2;
  pShowNumbers ALIAS FOR $3;
  pIndentName ALIAS FOR $4;
  pPrjid ALIAS FOR $5;
  _row flstmtitem%ROWTYPE;
  _p RECORD;
  _x RECORD;
  _priorMoPeriodId INTEGER;
  _priorQtPeriodId INTEGER;
  _priorYrPeriodId INTEGER;
  _first BOOLEAN;
  _prevlevel INTEGER;
  _subgrp INTEGER;
  _qtrInterval TEXT;
  _yrInterval TEXT;

BEGIN

  _priorMoPeriodId := -1;
  _priorQtPeriodId := -1;
  _priorYrPeriodId := -1;
  _first := TRUE;
  _prevlevel :=0;
  _subgrp := 0;

--Get Layout Data
  SELECT flhead_id,flhead_type,
        flcol_month,flcol_quarter,flcol_year,flcol_priortype,
        flcol_priormonth,flcol_priorquarter,flcol_prioryear,
        flcol_priordiff,flcol_priordiffprcnt,flcol_priorprcnt,
        flcol_budget,flcol_budgetdiff,flcol_budgetdiffprcnt,
        flcol_budgetprcnt INTO _p
  FROM flhead,flcol
  WHERE ((flcol_id=pFlcolid)
  AND (flhead_id=flcol_flhead_id));

  IF (_p.flhead_type='B') THEN
    _qtrInterval := 'M';
    _yrInterval := 'M';
  ELSE
    _qtrInterval := 'Q';
    _yrInterval := 'Y';
  END IF;

--Delete old data from all periods
  DELETE FROM flrpt
  WHERE ((flrpt_username=getEffectiveXtUser())
  AND (flrpt_flhead_id=_p.flhead_id));

--Populate report data...
--...for Month
        IF (_p.flcol_month) THEN

        PERFORM financialreport(_p.flhead_id,pPeriodid,'M',pPrjid);

                IF ((_p.flcol_priortype = 'P') AND (_p.flcol_priormonth)) THEN

                        SELECT COALESCE(pp.period_id,-1) INTO _priorMoPeriodId
                        FROM period cp, period pp
                        WHERE ((cp.period_id=pPeriodId)
                        AND (cp.period_start > pp.period_start))
                        ORDER BY pp.period_start DESC LIMIT 1;

                        IF (_priorMoPeriodId IS NOT NULL) THEN
                                PERFORM financialreport(_p.flhead_id,_priorMoPeriodId,'M',pPrjid);
                        END IF;

                        ELSE IF ((_p.flcol_priortype='Y')AND (_p.flcol_priormonth)) THEN

                                SELECT COALESCE(pp.period_id,-1) INTO _priorMoPeriodId
                                FROM period cp, period pp
                                WHERE ((cp.period_id=pPeriodId)
                                AND (cp.period_id != pp.period_id)
                                AND (cp.period_start > pp.period_start)
                                AND (cp.period_number = pp.period_number))
                                ORDER BY pp.period_start DESC LIMIT 1;

                                IF (_priorMoPeriodId IS NOT NULL) THEN
                                        PERFORM financialreport(_p.flhead_id,_priorMoPeriodId,'M',pPrjid);
                                END IF;

                        END IF;

                END IF;
        END IF;

--...for Quarter
        IF (_p.flcol_quarter) THEN

        PERFORM financialreport(_p.flhead_id,pPeriodid,'Q',pPrjid);

        END IF;

        IF ((_p.flcol_priortype='P') AND (_p.flcol_priorquarter)) THEN

                SELECT COALESCE(pp.period_id,-1) INTO _priorQtPeriodId
                FROM period cp, period pp
                WHERE ((cp.period_id=pPeriodId)
                AND (cp.period_start > pp.period_start)
                AND (pp.period_quarter=
                        CASE WHEN cp.period_quarter > 1 THEN
                                cp.period_quarter - 1
                        ELSE 4 END)
                AND (pp.period_start >= cp.period_start - interval '1 year'))
                ORDER BY pp.period_start DESC LIMIT 1;

                IF (_priorQtPeriodId IS NOT NULL) THEN
                        PERFORM financialreport(_p.flhead_id,_priorQtPeriodId,'Q',pPrjid);
                END IF;

                ELSE IF ((_p.flcol_priortype='Y')AND (_p.flcol_priorquarter)) THEN

                        SELECT pp.period_id INTO _priorQtPeriodId
                        FROM period cp, period pp, yearperiod cy, yearperiod py
                        WHERE ((cp.period_id=pPeriodId)
                        AND (cp.period_yearperiod_id=cy.yearperiod_id)
                        AND (pp.period_yearperiod_id=py.yearperiod_id)
                        AND (cp.period_quarter=pp.period_quarter)
                        AND (cy.yearperiod_start > py.yearperiod_start))
                        ORDER BY py.yearperiod_start DESC, pp.period_start DESC LIMIT 1;

                        IF (_priorQtPeriodId IS NOT NULL) THEN
                                PERFORM financialreport(_p.flhead_id,_priorQtPeriodId,'Q',pPrjid);
                        END IF;

                END IF;
        END IF;

--...for Year
        IF (_p.flcol_year) THEN

                PERFORM financialreport(_p.flhead_id,pPeriodid,'Y',pPrjid);

        END IF;

        IF (_p.flcol_prioryear='D') THEN

                SELECT COALESCE(pp.period_id,-1) INTO _priorYrPeriodId
                FROM period cp, period pp
                WHERE ((cp.period_id = pPeriodId)
                AND (cp.period_number = pp.period_number)
                AND (cp.period_start > pp.period_start))
                ORDER BY pp.period_start DESC LIMIT 1;

                IF (_priorYrPeriodId IS NOT NULL) THEN
                        PERFORM financialreport(_p.flhead_id,_priorYrPeriodId,'Y',pPrjid);
                END IF;

                ELSE IF (_p.flcol_prioryear='F') THEN

                        SELECT pp.period_id INTO _priorYrPeriodId
                        FROM period cp, period pp, yearperiod cy, yearperiod py
                        WHERE ((cp.period_id=pPeriodId)
                        AND (cp.period_yearperiod_id=cy.yearperiod_id)
                        AND (pp.period_yearperiod_id=py.yearperiod_id)
                        AND (cy.yearperiod_start > py.yearperiod_start))
                        ORDER BY pp.period_start DESC LIMIT 1;

                        IF (_priorYrPeriodId IS NOT NULL) THEN
                                PERFORM financialreport(_p.flhead_id,_priorYrPeriodId,'Y',pPrjid);
                        END IF;

                END IF;
        END IF;

--Return the data
  FOR _x IN
        SELECT
        flrpt.flrpt_flhead_id AS flstmtitem_flhead_id,
        flrpt.flrpt_period_id AS flstmtitem_period_id,
        flrpt.flrpt_username AS flstmtitem_username,
        flrpt.flrpt_order AS flstmtitem_order,
        flrpt.flrpt_level AS flstmtitem_level,
        flrpt.flrpt_type AS flstmtitem_type,
        flrpt.flrpt_type_id AS flstmtitem_type_id,
        flrpt.flrpt_parent_id AS flstmtitem_parent_id,
        NULL AS flstmtitem_accnt_id,
        CASE
                WHEN (pIndentName) THEN
                        formatindent(flgrp.flgrp_name,flrpt.flrpt_level)
                ELSE flgrp.flgrp_name
        END AS flstmtitem_name,
        CASE
                WHEN (flgrp_summarize AND (flhead_type IN ('I','C'))) THEN
                        (COALESCE(flrptmo.flrpt_diff,0))
                WHEN (flgrp_summarize AND (flhead_type = 'B')) THEN
                        (COALESCE(flrptmo.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_month,
        CASE
                WHEN (flgrp_summarize) THEN
                        (COALESCE(flrptmo.flrpt_debits,0))
                ELSE NULL
        END AS flstmtitem_monthdb,
        CASE
                WHEN (flgrp_summarize) THEN
                        (COALESCE(flrptmo.flrpt_credits,0))
                ELSE NULL
        END AS flstmtitem_monthcr,
        CASE
                WHEN (flgrp_summarize AND flgrp_showdiffprcnt) THEN
                        (COALESCE(flrptmo.flrpt_diffprcnt,0))
                WHEN (flgrp_summarize AND flgrp_showendprcnt) THEN
                        (COALESCE(flrptmo.flrpt_endingprcnt,0))
                ELSE NULL
        END AS flstmtitem_monthprcnt,
        CASE
                WHEN (flgrp_summarize) THEN
                        (flrptmo.flrpt_budget)
                ELSE NULL
        END AS flstmtitem_monthbudget,
        CASE
                WHEN (flgrp_summarize) THEN
                        (flrptmo.flrpt_budgetprcnt)
                ELSE NULL
        END AS flstmtitem_monthbudgetprcnt,
        CASE
                WHEN (flgrp_summarize AND flhead_type IN ('I','C')) THEN
                        (COALESCE((flrptmo.flrpt_diff-flrptmo.flrpt_budget),0))
                WHEN (flgrp_summarize AND flhead_type = 'B') THEN
                        (COALESCE((flrptmo.flrpt_ending-flrptmo.flrpt_budget),0))
                ELSE NULL
        END AS flstmtitem_monthbudgetdiff,
        CASE
                WHEN (flgrp_summarize AND (flhead_type IN ('I','C')) AND (flrptmo.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptmo.flrpt_diff-flrptmo.flrpt_budget)/flrptmo.flrpt_budget),0))
                WHEN (flgrp_summarize AND (flhead_type='B') AND (flrptmo.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptmo.flrpt_ending-flrptmo.flrpt_budget)/flrptmo.flrpt_budget),0))
                WHEN (flgrp_summarize AND (flrptmo.flrpt_budget = 0)) THEN
                        NULL
                ELSE NULL
        END AS flstmtitem_monthbudgetdiffprcnt,
        CASE
                WHEN (flgrp_summarize AND flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptqt.flrpt_diff,0))
                WHEN (flgrp_summarize AND flhead_type = 'B') THEN
                        (COALESCE(flrptqt.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_qtr,
        CASE
                WHEN (flgrp_summarize) THEN
                        (COALESCE(flrptqt.flrpt_debits,0))
                ELSE NULL
        END AS flstmtitem_qtrdb,
        CASE
                WHEN (flgrp_summarize) THEN
                        (COALESCE(flrptqt.flrpt_credits,0))
                ELSE NULL
        END AS flstmtitem_qtrcr,
        CASE
                WHEN (flgrp_summarize AND flgrp_showdiffprcnt) THEN
                        (flrptqt.flrpt_diffprcnt)
                WHEN (flgrp_summarize AND flgrp_showendprcnt) THEN
                        (flrptqt.flrpt_endingprcnt)
                ELSE NULL
        END AS flstmtitem_qtrprcnt,
        CASE
                WHEN (flgrp_summarize) THEN
                        (COALESCE(flrptqt.flrpt_budget,0))
                ELSE NULL
        END AS flstmtitem_qtrbudget,
        CASE
                WHEN (flgrp_summarize) THEN
                        (flrptqt.flrpt_budgetprcnt)
                ELSE NULL
        END AS flstmtitem_qtrbudgetprcnt,
        CASE
                WHEN (flgrp_summarize AND flhead_type IN ('I','C')) THEN
                        (COALESCE((flrptqt.flrpt_diff-flrptqt.flrpt_budget),0))
                WHEN (flgrp_summarize AND flhead_type = 'B') THEN
                        (COALESCE((flrptqt.flrpt_ending-flrptqt.flrpt_budget),0))
                ELSE NULL
        END AS flstmtitem_qtrbudgetdiff,
        CASE
                WHEN (flgrp_summarize AND (flhead_type IN ('I','C')) AND (flrptqt.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptqt.flrpt_diff-flrptqt.flrpt_budget)/flrptqt.flrpt_budget),0))
                WHEN (flgrp_summarize AND (flhead_type='B') AND (flrptqt.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptqt.flrpt_ending-flrptqt.flrpt_budget)/flrptqt.flrpt_budget),0))
                ELSE NULL
        END AS flstmtitem_qtrbudgetdiffprcnt,
        CASE
                WHEN (flgrp_summarize AND flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptyr.flrpt_diff,0))
                WHEN (flgrp_summarize AND flhead_type = 'B') THEN
                        (COALESCE(flrptyr.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_year,
        CASE
                WHEN (flgrp_summarize) THEN
                        (COALESCE(flrptyr.flrpt_debits,0))
                ELSE NULL
        END AS flstmtitem_yeardb,
        CASE
                WHEN (flgrp_summarize) THEN
                        (COALESCE(flrptyr.flrpt_credits,0))
                ELSE NULL
        END AS flstmtitem_yearcr,
        CASE
                WHEN (flgrp_summarize AND flgrp_showdiffprcnt) THEN
                        (COALESCE(flrptyr.flrpt_diffprcnt,0))
                WHEN (flgrp_summarize AND flgrp_showendprcnt) THEN
                        (COALESCE(flrptyr.flrpt_endingprcnt,0))
                ELSE NULL
        END AS flstmtitem_yearprcnt,
        CASE
                WHEN (flgrp_summarize) THEN
                        (flrptyr.flrpt_budget)
                ELSE NULL
        END AS  flstmtitem_yearbudget,
        CASE
                WHEN (flgrp_summarize) THEN
                        (flrptyr.flrpt_budgetprcnt)
                ELSE NULL
        END AS flstmtitem_yearbudgetprcnt,
        CASE
                WHEN (flgrp_summarize AND flhead_type IN ('I','C')) THEN
                        (COALESCE((flrptyr.flrpt_diff-flrptyr.flrpt_budget),0))
                WHEN (flgrp_summarize AND flhead_type = 'B') THEN
                        (COALESCE((flrptyr.flrpt_ending-flrptyr.flrpt_budget),0))
                ELSE NULL
        END AS flstmtitem_yearbudgetdiff,
        CASE
                WHEN (flgrp_summarize AND (flhead_type IN ('I','C')) AND (flrptyr.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptyr.flrpt_diff-flrptyr.flrpt_budget)/flrptyr.flrpt_budget),0))
                WHEN (flgrp_summarize AND (flhead_type = 'B') AND (flrptyr.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptyr.flrpt_ending-flrptyr.flrpt_budget)/flrptyr.flrpt_budget),0))
                WHEN (flgrp_summarize AND (flrptyr.flrpt_budget = 0)) THEN
                        NULL
                ELSE NULL
        END AS flstmtitem_yearbudgetdiffprcnt,
        CASE
                WHEN (flgrp_summarize AND flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptprmo.flrpt_diff,0))
                WHEN (flgrp_summarize AND flhead_type = 'B') THEN
                        (COALESCE(flrptprmo.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_prmonth,
        CASE
                WHEN (flgrp_summarize AND flgrp_showdiffprcnt) THEN
                        (flrptprmo.flrpt_diffprcnt)
                WHEN (flgrp_summarize AND flgrp_showendprcnt) THEN
                        (flrptprmo.flrpt_endingprcnt)
                ELSE NULL
        END AS flstmtitem_prmonthprcnt,
        CASE
                WHEN (flgrp_summarize AND flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptmo.flrpt_diff-flrptprmo.flrpt_diff,0))
                WHEN (flgrp_summarize AND flhead_type = 'B') THEN
                        (COALESCE(flrptmo.flrpt_ending-flrptprmo.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_prmonthdiff,
        CASE
                WHEN (flgrp_summarize AND (flhead_type IN ('I','C')) AND (flrptprmo.flrpt_diff > 0)) THEN
                        (COALESCE((flrptmo.flrpt_diff-flrptprmo.flrpt_diff)/flrptprmo.flrpt_diff,0))
                WHEN (flgrp_summarize AND (flhead_type = 'B') AND (flrptprmo.flrpt_ending > 0)) THEN
                        (COALESCE((flrptmo.flrpt_ending-flrptprmo.flrpt_ending)/flrptprmo.flrpt_ending,0))
                WHEN (flgrp_summarize AND (flrptprmo.flrpt_ending = 0)) THEN
                        NULL
                ELSE NULL
        END AS flstmtitem_prmonthdiffprcnt,
        CASE
                WHEN (flgrp_summarize AND flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptprqt.flrpt_diff,0))
                WHEN (flgrp_summarize AND flhead_type = 'B') THEN
                        (COALESCE(flrptprqt.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_prqtr,
        CASE
                WHEN (flgrp_summarize AND flgrp_showdiffprcnt) THEN
                        (flrptprqt.flrpt_diffprcnt)
                WHEN (flgrp_summarize AND flgrp_showendprcnt) THEN
                        (flrptprqt.flrpt_endingprcnt)
                ELSE NULL
        END AS flstmtitem_prqtrprcnt,
        CASE
                WHEN (flgrp_summarize AND flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptqt.flrpt_diff-flrptprqt.flrpt_diff,0))
                WHEN (flgrp_summarize AND flhead_type = 'B') THEN
                        (COALESCE(flrptqt.flrpt_ending-flrptprqt.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_prqtrdiff,
        CASE
                WHEN (flgrp_summarize AND (flhead_type IN ('I','C')) AND (flrptprqt.flrpt_diff > 0)) THEN
                        (COALESCE((flrptqt.flrpt_diff-flrptprqt.flrpt_diff)/flrptprqt.flrpt_diff,0))
                WHEN (flgrp_summarize AND (flhead_type = 'B') AND (flrptprqt.flrpt_ending > 0)) THEN
                        (COALESCE((flrptqt.flrpt_ending-flrptprqt.flrpt_ending)/flrptprqt.flrpt_ending,0))
                WHEN (flgrp_summarize AND (flrptprqt.flrpt_ending = 0)) THEN
                        NULL
                ELSE NULL
        END AS flstmtitem_prqtrdiffprcnt,
        CASE
                WHEN (flgrp_summarize AND flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptpryr.flrpt_diff,0))
                WHEN (flgrp_summarize AND flhead_type = 'B') THEN
                        (COALESCE(flrptpryr.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_pryear,
        CASE
                WHEN (flgrp_summarize AND flgrp_showdiffprcnt) THEN
                        (flrptpryr.flrpt_diffprcnt)
                WHEN (flgrp_summarize AND flgrp_showendprcnt) THEN
                        (flrptpryr.flrpt_endingprcnt)
                ELSE NULL
        END AS flstmtitem_pryearprcnt,
        CASE
                WHEN (flgrp_summarize AND flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptyr.flrpt_diff-flrptpryr.flrpt_diff,0))
                WHEN (flgrp_summarize AND flhead_type = 'B') THEN
                        (COALESCE(flrptyr.flrpt_ending-flrptpryr.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_pryeardiff,
        CASE
                WHEN (flgrp_summarize AND (flhead_type IN ('I','C')) AND (flrptpryr.flrpt_diff > 0)) THEN
                        (COALESCE((flrptyr.flrpt_diff-flrptpryr.flrpt_diff)/flrptpryr.flrpt_diff,0))
                WHEN (flgrp_summarize AND (flhead_type = 'B' ) AND (flrptpryr.flrpt_ending > 0)) THEN
                        (COALESCE((flrptyr.flrpt_ending-flrptpryr.flrpt_ending)/flrptpryr.flrpt_ending,0))
                WHEN (flgrp_summarize AND (flrptpryr.flrpt_ending = 0)) THEN
                        NULL
                ELSE NULL
        END AS flstmtitem_pryeardiffprcnt
        FROM flgrp,flhead,
                (SELECT DISTINCT
                        flrpt_flhead_id,
                        flrpt_period_id,
                        flrpt_username,
                        flrpt_order,
                        flrpt_level,
                        flrpt_type,
                        flrpt_type_id,
                        flrpt_parent_id
                FROM flrpt
                WHERE ((flrpt_type='G')
                AND (flrpt_flhead_id=_p.flhead_id)
                AND (flrpt_period_id=pPeriodId)
                AND (flrpt_username=getEffectiveXtUser()))) AS flrpt
                        LEFT OUTER JOIN flrpt flrptmo
                                ON ((flrptmo.flrpt_type=flrpt.flrpt_type)
                                AND (flrptmo.flrpt_type_id=flrpt.flrpt_type_id)
                                AND (flrptmo.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptmo.flrpt_period_id=flrpt.flrpt_period_id)
                                AND (flrptmo.flrpt_interval='M')
                                AND (flrptmo.flrpt_username=flrpt.flrpt_username)
                                AND (flrptmo.flrpt_order=flrpt.flrpt_order))
                        LEFT OUTER JOIN flrpt flrptqt
                                ON ((flrptqt.flrpt_type=flrpt.flrpt_type)
                                AND (flrptqt.flrpt_type_id=flrpt.flrpt_type_id)
                                AND (flrptqt.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptqt.flrpt_period_id=flrpt.flrpt_period_id)
                                AND (flrptqt.flrpt_interval=_qtrInterval)
                                AND (flrptqt.flrpt_username=flrpt.flrpt_username)
                                AND (flrptqt.flrpt_order=flrpt.flrpt_order))
                        LEFT OUTER JOIN flrpt flrptyr
                                ON ((flrptyr.flrpt_type=flrpt.flrpt_type)
                                AND (flrptyr.flrpt_type_id=flrpt.flrpt_type_id)
                                AND (flrptyr.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptyr.flrpt_period_id=flrpt.flrpt_period_id)
                                AND (flrptyr.flrpt_interval=_yrInterval)
                                AND (flrptyr.flrpt_username=flrpt.flrpt_username)
                                AND (flrptyr.flrpt_order=flrpt.flrpt_order))
                        LEFT OUTER JOIN flrpt flrptprmo
                                ON ((flrptprmo.flrpt_type=flrpt.flrpt_type)
                                AND (flrptprmo.flrpt_type_id=flrpt.flrpt_type_id)
                                AND (flrptprmo.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptprmo.flrpt_period_id=_priorMoPeriodId)
                                AND (flrptprmo.flrpt_interval='M')
                                AND (flrptprmo.flrpt_username=flrpt.flrpt_username)
                                AND (flrptprmo.flrpt_order=flrpt.flrpt_order))
                        LEFT OUTER JOIN flrpt flrptprqt
                                ON ((flrptprqt.flrpt_type=flrpt.flrpt_type)
                                AND (flrptprqt.flrpt_type_id=flrpt.flrpt_type_id)
                                AND (flrptprqt.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptprqt.flrpt_period_id=_priorQtPeriodId)
                                AND (flrptprqt.flrpt_interval='Q')
                                AND (flrptprqt.flrpt_username=flrpt.flrpt_username)
                                AND (flrptprqt.flrpt_order=flrpt.flrpt_order))
                        LEFT OUTER JOIN flrpt flrptpryr
                                ON ((flrptpryr.flrpt_type=flrpt.flrpt_type)
                                AND (flrptpryr.flrpt_type_id=flrpt.flrpt_type_id)
                                AND (flrptpryr.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptpryr.flrpt_period_id=_priorYrPeriodId)
                                AND (flrptpryr.flrpt_interval='Y')
                                AND (flrptpryr.flrpt_username=flrpt.flrpt_username)
                                AND (flrptpryr.flrpt_order=flrpt.flrpt_order))
        WHERE ((flgrp_id = flrpt.flrpt_type_id)
        AND (flhead_id = flgrp_flhead_id))
        UNION
        SELECT
        flrpt.flrpt_flhead_id AS flstmtitem_flhead_id,
        flrpt.flrpt_period_id AS flstmtitem_period_id,
        flrpt.flrpt_username AS flstmtitem_username,
        flrpt.flrpt_order AS flstmtitem_order,
        flrpt.flrpt_level AS flstmtitem_level,
        flrpt.flrpt_type AS flstmtitem_type,
        flrpt.flrpt_type_id AS flstmtitem_type_id,
        flrpt.flrpt_parent_id AS flstmtitem_parent_id,
        flrpt.flrpt_accnt_id AS flstmtitem_accnt_id,
        CASE
                WHEN (pIndentName) THEN
                        formatindent(flrpt.flrpt_name,flrpt.flrpt_level)
                ELSE flrpt.flrpt_name
        END AS flstmtitem_name,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptmo.flrpt_diff,0))
                ELSE (COALESCE(flrptmo.flrpt_ending,0))
        END AS flstmtitem_month,
        (COALESCE(flrptmo.flrpt_debits,0)) AS flstmtitem_monthdb,
        (COALESCE(flrptmo.flrpt_credits,0)) AS flstmtitem_monthcr,
        CASE
                WHEN (flitem_showdiffprcnt) THEN
                        (flrptmo.flrpt_diffprcnt)
                WHEN (flitem_showendprcnt) THEN
                        (flrptmo.flrpt_endingprcnt)
                ELSE NULL
        END AS flstmtitem_monthprcnt,
        (COALESCE(flrptmo.flrpt_budget,0)) AS flstmtitem_monthbudget,
        (flrptmo.flrpt_budgetprcnt) AS flstmtitem_monthbudgetprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE((flrptmo.flrpt_diff-flrptmo.flrpt_budget),0))
                ELSE (COALESCE((flrptmo.flrpt_ending-flrptmo.flrpt_budget),0))
        END AS flstmtitem_monthbudgetdiff,
        CASE
                WHEN ((flhead_type IN ('I','C')) AND (flrptmo.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptmo.flrpt_diff-flrptmo.flrpt_budget)/flrptmo.flrpt_budget),0))
                WHEN ((flhead_type='B') AND (flrptmo.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptmo.flrpt_ending-flrptmo.flrpt_budget)/flrptmo.flrpt_budget),0))
                ELSE NULL
        END AS flstmtitem_monthbudgetdiffprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptqt.flrpt_diff,0))
                ELSE (COALESCE(flrptqt.flrpt_ending,0))
        END AS flstmtitem_qtr,
        (COALESCE(flrptqt.flrpt_debits,0)) AS flstmtitem_qtrdb,
        (COALESCE(flrptqt.flrpt_credits,0)) AS flstmtitem_qtrcr,
        CASE
                WHEN (flitem_showdiffprcnt) THEN
                        (COALESCE(flrptqt.flrpt_diffprcnt,0))
                WHEN (flitem_showendprcnt) THEN
                        (COALESCE(flrptqt.flrpt_endingprcnt,0))
                ELSE NULL
        END AS flstmtitem_qtrprcnt,
        (COALESCE(flrptqt.flrpt_budget,0)) AS flstmtitem_qtrbudget,
        (flrptqt.flrpt_budgetprcnt) AS flstmtitem_qtrbudgetprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE((flrptqt.flrpt_diff-flrptqt.flrpt_budget),0))
                ELSE (COALESCE((flrptqt.flrpt_ending-flrptqt.flrpt_budget),0))
        END AS flstmtitem_qtrbudgetdiff,
        CASE
                WHEN ((flhead_type IN ('I','C')) AND (flrptqt.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptqt.flrpt_diff-flrptqt.flrpt_budget)/flrptqt.flrpt_budget),0))
                WHEN ((flhead_type='B') AND (flrptqt.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptqt.flrpt_ending-flrptqt.flrpt_budget)/flrptqt.flrpt_budget),0))
                ELSE NULL
        END AS flstmtitem_qtrbudgetdiffprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptyr.flrpt_diff,0))
                ELSE (COALESCE(flrptyr.flrpt_ending,0))
        END AS flstmtitem_year,
        (COALESCE(flrptyr.flrpt_debits,0)) AS flstmtitem_yeardb,
        (COALESCE(flrptyr.flrpt_credits,0)) AS flstmtitem_yearcr,
        CASE
                WHEN (flitem_showdiffprcnt) THEN
                        (flrptyr.flrpt_diffprcnt)
                WHEN (flitem_showendprcnt) THEN
                        (flrptyr.flrpt_endingprcnt)
                ELSE NULL
        END AS flstmtitem_yearprcnt,
        (COALESCE(flrptyr.flrpt_budget,0)) AS  flstmtitem_yearbudget,
        (flrptyr.flrpt_budgetprcnt) AS flstmtitem_yearbudgetprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE((flrptyr.flrpt_diff-flrptyr.flrpt_budget),0))
                ELSE (COALESCE((flrptyr.flrpt_ending-flrptyr.flrpt_budget),0))
        END AS flstmtitem_yearbudgetdiff,
        CASE
                WHEN ((flhead_type IN ('I','C')) AND (flrptyr.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptyr.flrpt_diff-flrptyr.flrpt_budget)/flrptyr.flrpt_budget),0))
                WHEN ((flhead_type = 'B') AND (flrptyr.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptyr.flrpt_ending-flrptyr.flrpt_budget)/flrptyr.flrpt_budget),0))
                ELSE NULL
        END AS flstmtitem_yearbudgetdiffprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptprmo.flrpt_diff,0))
                ELSE (COALESCE(flrptprmo.flrpt_ending,0))
        END AS flstmtitem_prmonth,
        CASE
                WHEN (flitem_showdiffprcnt) THEN
                        (flrptprmo.flrpt_diffprcnt)
                WHEN (flitem_showendprcnt) THEN
                        (flrptprmo.flrpt_endingprcnt)
                ELSE NULL
        END AS flstmtitem_prmonthprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptmo.flrpt_diff-flrptprmo.flrpt_diff,0))
                ELSE (COALESCE(flrptmo.flrpt_ending-flrptprmo.flrpt_ending,0))
        END AS flstmtitem_prmonthdiff,
        CASE
                WHEN ((flhead_type IN ('I','C')) AND (flrptprmo.flrpt_diff > 0)) THEN
                        (COALESCE((flrptmo.flrpt_diff-flrptprmo.flrpt_diff)/flrptprmo.flrpt_diff,0))
                WHEN ((flhead_type = 'B') AND (flrptprmo.flrpt_ending > 0)) THEN
                        (COALESCE((flrptmo.flrpt_ending-flrptprmo.flrpt_ending)/flrptprmo.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_prmonthdiffprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptprqt.flrpt_diff,0))
                ELSE (COALESCE(flrptprqt.flrpt_ending,0))
        END AS flstmtitem_prqtr,
        CASE
                WHEN (flitem_showdiffprcnt) THEN
                        (flrptprqt.flrpt_diffprcnt)
                WHEN (flitem_showendprcnt) THEN
                        (flrptprqt.flrpt_endingprcnt)
                ELSE NULL
        END AS flstmtitem_prqtrprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptqt.flrpt_diff-flrptprqt.flrpt_diff,0))
                ELSE (COALESCE(flrptqt.flrpt_ending-flrptprqt.flrpt_ending,0))
        END AS flstmtitem_prqtrdiff,
        CASE
                WHEN ((flhead_type IN ('I','C')) AND (flrptprqt.flrpt_diff > 0)) THEN
                        (COALESCE((flrptqt.flrpt_diff-flrptprqt.flrpt_diff)/flrptprqt.flrpt_diff,0))
                WHEN ((flhead_type = 'B') AND (flrptprqt.flrpt_ending > 0)) THEN
                        (COALESCE((flrptqt.flrpt_ending-flrptprqt.flrpt_ending)/flrptprqt.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_prqtrdiffprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptpryr.flrpt_diff,0))
                ELSE (COALESCE(flrptpryr.flrpt_ending,0))
        END AS flstmtitem_pryear,
        CASE
                WHEN (flitem_showdiffprcnt) THEN
                        (flrptpryr.flrpt_diffprcnt)
                WHEN (flitem_showendprcnt) THEN
                        (flrptpryr.flrpt_endingprcnt)
                ELSE NULL
        END AS flstmtitem_pryearprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE((flrptyr.flrpt_diff-flrptpryr.flrpt_diff),0))
                ELSE (COALESCE((flrptyr.flrpt_ending-flrptpryr.flrpt_ending),0))
        END AS flstmtitem_pryeardiff,
        CASE
                WHEN ((flhead_type IN ('I','C')) AND (flrptpryr.flrpt_diff > 0)) THEN
                        (COALESCE((flrptyr.flrpt_diff-flrptpryr.flrpt_diff)/flrptpryr.flrpt_diff,0))
                WHEN ((flhead_type = 'B' ) AND (flrptpryr.flrpt_ending > 0)) THEN
                        (COALESCE((flrptyr.flrpt_ending-flrptpryr.flrpt_ending)/flrptpryr.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_pryeardiffprcnt
        FROM flitem,flhead,
                (SELECT DISTINCT
                        flrpt_flhead_id,
                        flrpt_period_id,
                        flrpt_username,
                        flrpt_order,
                        flrpt_level,
                        flrpt_type,
                        flrpt_type_id,
                        flrpt_parent_id,
                        accnt_id AS flrpt_accnt_id,
                        CASE WHEN (pShowNumbers) THEN
                                (formatGLAccount(accnt_id) || '-' || accnt_descrip)
                        ELSE accnt_descrip END AS flrpt_name
                FROM flrpt,accnt
                WHERE ((flrpt_type='I')
                AND (flrpt_flhead_id=_p.flhead_id)
                AND (flrpt_period_id=pPeriodid)
                AND (flrpt_username=getEffectiveXtUser())
                AND (accnt_id=flrpt_accnt_id))) AS flrpt
                        LEFT OUTER JOIN flrpt flrptmo
                                ON ((flrptmo.flrpt_type=flrpt.flrpt_type)
                                AND (flrptmo.flrpt_type_id=flrpt.flrpt_type_id)
                                AND (flrptmo.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptmo.flrpt_period_id=flrpt.flrpt_period_id)
                                AND (flrptmo.flrpt_interval='M')
                                AND (flrptmo.flrpt_username=flrpt.flrpt_username)
                                AND (flrptmo.flrpt_order=flrpt.flrpt_order))
                        LEFT OUTER JOIN flrpt flrptqt
                                ON ((flrptqt.flrpt_type=flrpt.flrpt_type)
                                AND (flrptqt.flrpt_type_id=flrpt.flrpt_type_id)
                                AND (flrptqt.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptqt.flrpt_period_id=flrpt.flrpt_period_id)
                                AND (flrptqt.flrpt_interval=_qtrInterval)
                                AND (flrptqt.flrpt_username=flrpt.flrpt_username)
                                AND (flrptqt.flrpt_order=flrpt.flrpt_order))
                        LEFT OUTER JOIN flrpt flrptyr
                                ON ((flrptyr.flrpt_type=flrpt.flrpt_type)
                                AND (flrptyr.flrpt_type_id=flrpt.flrpt_type_id)
                                AND (flrptyr.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptyr.flrpt_period_id=flrpt.flrpt_period_id)
                                AND (flrptyr.flrpt_interval=_yrInterval)
                                AND (flrptyr.flrpt_username=flrpt.flrpt_username)
                                AND (flrptyr.flrpt_order=flrpt.flrpt_order))
                        LEFT OUTER JOIN flrpt flrptprmo
                                ON ((flrptprmo.flrpt_type=flrpt.flrpt_type)
                                AND (flrptprmo.flrpt_type_id=flrpt.flrpt_type_id)
                                AND (flrptprmo.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptprmo.flrpt_period_id=_priorMoPeriodId)
                                AND (flrptprmo.flrpt_interval='M')
                                AND (flrptprmo.flrpt_username=flrpt.flrpt_username)
                                AND (flrptprmo.flrpt_order=flrpt.flrpt_order))
                        LEFT OUTER JOIN flrpt flrptprqt
                                ON ((flrptprqt.flrpt_type=flrpt.flrpt_type)
                                AND (flrptprqt.flrpt_type_id=flrpt.flrpt_type_id)
                                AND (flrptprqt.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptprqt.flrpt_period_id=_priorQtPeriodId)
                                AND (flrptprqt.flrpt_interval='Q')
                                AND (flrptprqt.flrpt_username=flrpt.flrpt_username)
                                AND (flrptprqt.flrpt_order=flrpt.flrpt_order))
                        LEFT OUTER JOIN flrpt flrptpryr
                                ON ((flrptpryr.flrpt_type=flrpt.flrpt_type)
                                AND (flrptpryr.flrpt_type_id=flrpt.flrpt_type_id)
                                AND (flrptpryr.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptpryr.flrpt_period_id=_priorYrPeriodId)
                                AND (flrptpryr.flrpt_interval='Y')
                                AND (flrptpryr.flrpt_username=flrpt.flrpt_username)
                                AND (flrptpryr.flrpt_order=flrpt.flrpt_order) )
        WHERE ((flitem_id = flrpt.flrpt_type_id)
        AND (flhead_id = flitem_flhead_id))
        UNION
        SELECT
        flrpt.flrpt_flhead_id AS flstmtitem_flhead_id,
        flrpt.flrpt_period_id AS flstmtitem_period_id,
        flrpt.flrpt_username AS flstmtitem_username,
        flrpt.flrpt_order AS flstmtitem_order,
        flrpt.flrpt_level AS flstmtitem_level,
        flrpt.flrpt_type AS flstmtitem_type,
        flrpt.flrpt_type_id AS flstmtitem_type_id,
        flrpt.flrpt_parent_id AS flstmtitem_parent_id,
        NULL AS flstmtitem_accnt_id,
        CASE WHEN(flrpt.flrpt_type='T' AND flrpt.flrpt_level=0) THEN
                        COALESCE(flrpt.flrpt_altname, 'Total')
                WHEN(flrpt.flrpt_type='T') THEN
                        formatindent(COALESCE(flrpt.flrpt_altname, 'Subtotal') ,
                        (CASE WHEN pIndentName THEN flrpt.flrpt_level ELSE 0 END))
                ELSE formatindent(('Type ' || flrpt.flrpt_type || ' ' || text(flrpt.flrpt_type_id)),
                        (CASE WHEN pIndentName THEN flrpt.flrpt_level ELSE 0 END))
                END AS flstmtitem_name,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptmo.flrpt_diff,0))
                ELSE (COALESCE(flrptmo.flrpt_ending,0))
        END AS flstmtitem_month,
        (COALESCE(flrptmo.flrpt_debits,0)) AS flstmtitem_monthdb,
        (COALESCE(flrptmo.flrpt_credits,0)) AS flstmtitem_monthcr,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (flrptmo.flrpt_diffprcnt)
                ELSE (flrptmo.flrpt_endingprcnt)
        END AS flstmtitem_monthprcnt,
        (COALESCE(flrptmo.flrpt_budget,0)) AS flstmtitem_monthbudget,
        (flrptmo.flrpt_budgetprcnt) AS flstmtitem_monthbudgetprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE((flrptmo.flrpt_diff-flrptmo.flrpt_budget),0))
                ELSE (COALESCE((flrptmo.flrpt_ending-flrptmo.flrpt_budget),0))
        END AS flstmtitem_monthbudgetdiff,
        CASE
                WHEN ((flhead_type IN ('I','C')) AND (flrptmo.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptmo.flrpt_diff-flrptmo.flrpt_budget)/flrptmo.flrpt_budget),0))
                WHEN ((flhead_type='B') AND (flrptmo.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptmo.flrpt_ending-flrptmo.flrpt_budget)/flrptmo.flrpt_budget),0))
                ELSE NULL
        END AS flstmtitem_monthbudgetdiffprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptqt.flrpt_diff,0))
                ELSE (COALESCE(flrptqt.flrpt_ending,0))
        END AS flstmtitem_qtr,
        (COALESCE(flrptqt.flrpt_debits,0)) AS flstmtitem_qtrdb,
        (COALESCE(flrptqt.flrpt_credits,0)) AS flstmtitem_qtrcr,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (flrptqt.flrpt_diffprcnt)
                ELSE (flrptqt.flrpt_endingprcnt)
        END AS flstmtitem_qtrprcnt,
        (COALESCE(flrptqt.flrpt_budget,0)) AS flstmtitem_qtrbudget,
        (flrptqt.flrpt_budgetprcnt) AS flstmtitem_qtrbudgetprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE((flrptqt.flrpt_diff-flrptqt.flrpt_budget),0))
                ELSE (COALESCE((flrptqt.flrpt_ending-flrptqt.flrpt_budget),0))
        END AS flstmtitem_qtrbudgetdiff,
        CASE
                WHEN ((flhead_type IN ('I','C')) AND (flrptqt.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptqt.flrpt_diff-flrptqt.flrpt_budget)/flrptqt.flrpt_budget),0))
                WHEN ((flhead_type='B') AND (flrptqt.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptqt.flrpt_ending-flrptqt.flrpt_budget)/flrptqt.flrpt_budget),0))
                ELSE NULL
        END AS flstmtitem_qtrbudgetdiffprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptyr.flrpt_diff,0))
                ELSE (COALESCE(flrptyr.flrpt_ending,0))
        END AS flstmtitem_year,
        (COALESCE(flrptyr.flrpt_debits,0)) AS flstmtitem_yeardb,
        (COALESCE(flrptyr.flrpt_credits,0)) AS flstmtitem_yearcr,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (flrptyr.flrpt_diffprcnt)
                ELSE (flrptyr.flrpt_endingprcnt)
        END AS flstmtitem_yearprcnt,
        (COALESCE(flrptyr.flrpt_budget,0)) AS  flstmtitem_yearbudget,
        (flrptyr.flrpt_budgetprcnt) AS flstmtitem_yearbudgetprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE((flrptyr.flrpt_diff-flrptyr.flrpt_budget),0))
                ELSE (COALESCE((flrptyr.flrpt_ending-flrptyr.flrpt_budget),0))
        END AS flstmtitem_yearbudgetdiff,
        CASE
                WHEN ((flhead_type IN ('I','C')) AND (flrptyr.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptyr.flrpt_diff-flrptyr.flrpt_budget)/flrptyr.flrpt_budget),0))
                WHEN ((flhead_type = 'B') AND (flrptyr.flrpt_budget > 0)) THEN
                        (COALESCE(((flrptyr.flrpt_ending-flrptyr.flrpt_budget)/flrptyr.flrpt_budget),0))
                ELSE NULL
        END AS flstmtitem_yearbudgetdiffprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptprmo.flrpt_diff,0))
                ELSE (COALESCE(flrptprmo.flrpt_ending,0))
        END AS flstmtitem_prmonth,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (flrptprmo.flrpt_diffprcnt)
                ELSE (flrptprmo.flrpt_endingprcnt)
        END AS flstmtitem_prmonthprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptmo.flrpt_diff-flrptprmo.flrpt_diff,0))
                ELSE (COALESCE(flrptmo.flrpt_ending-flrptprmo.flrpt_ending,0))
        END AS flstmtitem_prmonthdiff,
        CASE
                WHEN ((flhead_type IN ('I','C')) AND (flrptprmo.flrpt_diff > 0)) THEN
                        (COALESCE((flrptmo.flrpt_diff-flrptprmo.flrpt_diff)/flrptprmo.flrpt_diff,0))
                WHEN ((flhead_type = 'B') AND (flrptprmo.flrpt_ending > 0)) THEN
                        (COALESCE((flrptmo.flrpt_ending-flrptprmo.flrpt_ending)/flrptprmo.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_prmonthdiffprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptprqt.flrpt_diff,0))
                ELSE (COALESCE(flrptprqt.flrpt_ending,0))
        END AS flstmtitem_prqtr,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (flrptprqt.flrpt_diffprcnt)
                ELSE (flrptprqt.flrpt_endingprcnt)
        END AS flstmtitem_prqtrprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptqt.flrpt_diff-flrptprqt.flrpt_diff,0))
                ELSE (COALESCE(flrptqt.flrpt_ending-flrptprqt.flrpt_ending,0))
        END AS flstmtitem_prqtrdiff,
        CASE
                WHEN ((flhead_type IN ('I','C')) AND (flrptprqt.flrpt_diff > 0)) THEN
                        (COALESCE((flrptqt.flrpt_diff-flrptprqt.flrpt_diff)/flrptprqt.flrpt_diff,0))
                WHEN ((flhead_type = 'B') AND (flrptprqt.flrpt_ending > 0)) THEN
                        (COALESCE((flrptqt.flrpt_ending-flrptprqt.flrpt_ending)/flrptprqt.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_prqtrdiffprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptpryr.flrpt_diff,0))
                ELSE (COALESCE(flrptpryr.flrpt_ending,0))
        END AS flstmtitem_pryear,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (flrptpryr.flrpt_diffprcnt)
                ELSE (flrptpryr.flrpt_endingprcnt)
        END AS flstmtitem_pryearprcnt,
        CASE
                WHEN (flhead_type IN ('I','C')) THEN
                        (COALESCE(flrptyr.flrpt_diff-flrptpryr.flrpt_diff,0))
                ELSE (COALESCE(flrptyr.flrpt_ending-flrptpryr.flrpt_ending,0))
        END AS flstmtitem_pryeardiff,
        CASE
                WHEN ((flhead_type IN ('I','C')) AND (flrptpryr.flrpt_diff > 0)) THEN
                        (COALESCE((flrptyr.flrpt_diff-flrptpryr.flrpt_diff)/flrptpryr.flrpt_diff,0))
                WHEN ((flhead_type = 'B' ) AND (flrptpryr.flrpt_ending > 0)) THEN
                        (COALESCE((flrptyr.flrpt_ending-flrptpryr.flrpt_ending)/flrptpryr.flrpt_ending,0))
                ELSE NULL
        END AS flstmtitem_pryeardiffprcnt
        FROM flhead CROSS JOIN (SELECT DISTINCT
                        flrpt_flhead_id,
                        flrpt_period_id,
                        flrpt_username,
                        flrpt_order,
                        flrpt_level,
                        flrpt_type,
                        flrpt_type_id,
                        flrpt_parent_id,
                        flrpt_altname
                FROM flrpt
                WHERE ((NOT (flrpt_type IN ('G','I','S')))
                AND (flrpt_flhead_id=_p.flhead_id)
                AND (flrpt_period_id=pPeriodId)
                AND (flrpt_username=getEffectiveXtUser()))) AS flrpt
                        LEFT OUTER JOIN flrpt flrptmo
                                ON ((flrptmo.flrpt_type=flrpt.flrpt_type)
                                AND (flrptmo.flrpt_order=flrpt.flrpt_order)
                                AND (flrptmo.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptmo.flrpt_period_id=flrpt.flrpt_period_id)
                                AND (flrptmo.flrpt_interval='M')
                                AND (flrptmo.flrpt_username=flrpt.flrpt_username))
                        LEFT OUTER JOIN flrpt flrptqt
                                ON ((flrptqt.flrpt_type=flrpt.flrpt_type)
                                AND (flrptqt.flrpt_order=flrpt.flrpt_order)
                                AND (flrptqt.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptqt.flrpt_period_id=flrpt.flrpt_period_id)
                                AND (flrptqt.flrpt_interval=_qtrInterval)
                                AND (flrptqt.flrpt_username=flrpt.flrpt_username))
                        LEFT OUTER JOIN flrpt flrptyr
                                ON ((flrptyr.flrpt_type=flrpt.flrpt_type)
                                AND (flrptyr.flrpt_order=flrpt.flrpt_order)
                                AND (flrptyr.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptyr.flrpt_period_id=flrpt.flrpt_period_id)
                                AND (flrptyr.flrpt_interval=_yrInterval)
                                AND (flrptyr.flrpt_username=flrpt.flrpt_username))
                        LEFT OUTER JOIN flrpt flrptprmo
                                ON ((flrptprmo.flrpt_type=flrpt.flrpt_type)
                                AND (flrptprmo.flrpt_order=flrpt.flrpt_order)
                                AND (flrptprmo.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptprmo.flrpt_period_id=_priorMoPeriodId)
                                AND (flrptprmo.flrpt_interval='M')
                                AND (flrptprmo.flrpt_username=flrpt.flrpt_username))
                        LEFT OUTER JOIN flrpt flrptprqt
                                ON ((flrptprqt.flrpt_type=flrpt.flrpt_type)
                                AND (flrptprqt.flrpt_order=flrpt.flrpt_order)
                                AND (flrptprqt.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptprqt.flrpt_period_id=_priorQtPeriodId)
                                AND (flrptprqt.flrpt_interval='Q')
                                AND (flrptprqt.flrpt_username=flrpt.flrpt_username))
                        LEFT OUTER JOIN flrpt flrptpryr
                                ON ((flrptpryr.flrpt_type=flrpt.flrpt_type)
                                AND (flrptpryr.flrpt_order=flrpt.flrpt_order)
                                AND (flrptpryr.flrpt_flhead_id=flrpt.flrpt_flhead_id)
                                AND (flrptpryr.flrpt_period_id=_priorYrPeriodId)
                                AND (flrptpryr.flrpt_interval='Y')
                                AND (flrptpryr.flrpt_username=flrpt.flrpt_username))
        WHERE (flhead_id=flrpt.flrpt_flhead_id)
        ORDER BY flstmtitem_order
        LOOP
                IF _prevlevel > _x.flstmtitem_level THEN
                        _subgrp := _subgrp+1;
                END IF;
                _prevlevel:=_x.flstmtitem_level;
                _row.flstmtitem_subgrp := _subgrp;

                IF NOT _first THEN
                        RETURN NEXT _row;
                END IF;

                _first := FALSE;

                _row.flstmtitem_flhead_id := _x.flstmtitem_flhead_id;
                _row.flstmtitem_period_id := _x.flstmtitem_period_id;
                _row.flstmtitem_username := _x.flstmtitem_username;
                _row.flstmtitem_order := _x.flstmtitem_order;
                _row.flstmtitem_level := _x.flstmtitem_level;
                _row.flstmtitem_type := _x.flstmtitem_type;
                _row.flstmtitem_type_id := _x.flstmtitem_type_id;
                _row.flstmtitem_parent_id := _x.flstmtitem_parent_id;
                _row.flstmtitem_accnt_id := _x.flstmtitem_accnt_id;
                _row.flstmtitem_name := _x.flstmtitem_name;
                _row.flstmtitem_month := _x.flstmtitem_month;
                _row.flstmtitem_monthdb := _x.flstmtitem_monthdb;
                _row.flstmtitem_monthcr := _x.flstmtitem_monthcr;
                _row.flstmtitem_monthprcnt := _x.flstmtitem_monthprcnt;
                _row.flstmtitem_monthbudget := _x.flstmtitem_monthbudget;
                _row.flstmtitem_monthbudgetprcnt := _x.flstmtitem_monthbudgetprcnt;
                _row.flstmtitem_monthbudgetdiff := _x.flstmtitem_monthbudgetdiff;
                _row.flstmtitem_monthbudgetdiffprcnt := _x.flstmtitem_monthbudgetdiffprcnt;
                _row.flstmtitem_qtr := _x.flstmtitem_qtr;
                _row.flstmtitem_qtrdb := _x.flstmtitem_qtrdb;
                _row.flstmtitem_qtrcr := _x.flstmtitem_qtrcr;
                _row.flstmtitem_qtrprcnt := _x.flstmtitem_qtrprcnt;
                _row.flstmtitem_qtrbudget := _x.flstmtitem_qtrbudget;
                _row.flstmtitem_qtrbudgetprcnt := _x.flstmtitem_qtrbudgetprcnt;
                _row.flstmtitem_qtrbudgetdiff := _x.flstmtitem_qtrbudgetdiff;
                _row.flstmtitem_qtrbudgetdiffprcnt := _x.flstmtitem_qtrbudgetdiffprcnt;
                _row.flstmtitem_year := _x.flstmtitem_year;
                _row.flstmtitem_yeardb := _x.flstmtitem_yeardb;
                _row.flstmtitem_yearcr := _x.flstmtitem_yearcr;
                _row.flstmtitem_yearprcnt := _x.flstmtitem_yearprcnt;
                _row.flstmtitem_yearbudget := _x.flstmtitem_yearbudget;
                _row.flstmtitem_yearbudgetprcnt := _x.flstmtitem_yearbudgetprcnt;
                _row.flstmtitem_yearbudgetdiff := _x.flstmtitem_yearbudgetdiff;
                _row.flstmtitem_yearbudgetdiffprcnt := _x.flstmtitem_yearbudgetdiffprcnt;
                _row.flstmtitem_prmonth := _x.flstmtitem_prmonth;
                _row.flstmtitem_prmonthprcnt := _x.flstmtitem_prmonthprcnt;
                _row.flstmtitem_prmonthdiff := _x.flstmtitem_prmonthdiff;
                _row.flstmtitem_prmonthdiffprcnt := _x.flstmtitem_prmonthdiffprcnt;
                _row.flstmtitem_prqtr := _x.flstmtitem_prqtr;
                _row.flstmtitem_prqtrprcnt := _x.flstmtitem_prqtrprcnt;
                _row.flstmtitem_prqtrdiff := _x.flstmtitem_prqtrdiff;
                _row.flstmtitem_prqtrdiffprcnt := _x.flstmtitem_prqtrdiffprcnt;
                _row.flstmtitem_pryear := _x.flstmtitem_pryear;
                _row.flstmtitem_pryearprcnt := _x.flstmtitem_pryearprcnt;
                _row.flstmtitem_pryeardiff := _x.flstmtitem_pryeardiff;
                _row.flstmtitem_pryeardiffprcnt := _x.flstmtitem_pryeardiffprcnt;

        END LOOP;

        _row.flstmtitem_subgrp := _subgrp + 1;
        RETURN NEXT _row;
END;
$_$;


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

--
-- TOC entry 1562 (class 1255 OID 146566140)
-- Dependencies: 4536 8
-- Name: findapaccount(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF (NOT fetchMetricBool('InterfaceAPToGL')) THEN
    RETURN 0;
  END IF;

  SELECT apaccnt_ap_accnt_id INTO _accntid
    FROM apaccnt
    JOIN vendinfo ON (apaccnt_vendtype_id=vend_vendtype_id)
  WHERE (vend_id=pVendid);
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

  SELECT apaccnt_ap_accnt_id INTO _accntid
    FROM apaccnt
    JOIN vendtype ON (vendtype_code ~ apaccnt_vendtype)
    JOIN vendinfo ON (vend_vendtype_id=vendtype_id)
  WHERE ((apaccnt_vendtype_id=-1)
     AND (vend_id=pVendid));
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

  RETURN -1;

END;
$_$;


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

--
-- TOC entry 1563 (class 1255 OID 146566141)
-- Dependencies: 4536 8
-- Name: findapdiscountaccount(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION findapdiscountaccount(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;
  _accntid INTEGER;

BEGIN

  IF (NOT fetchMetricBool('InterfaceAPToGL')) THEN
    RETURN 0;
  END IF;

  SELECT apaccnt_discount_accnt_id INTO _accntid
    FROM apaccnt
    JOIN vendinfo ON (apaccnt_vendtype_id=vend_vendtype_id)
  WHERE (vend_id=pVendid);
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

  SELECT apaccnt_discount_accnt_id INTO _accntid
    FROM apaccnt
    JOIN vendtype ON (vendtype_code ~ apaccnt_vendtype)
    JOIN vendinfo ON (vend_vendtype_id=vendtype_id)
  WHERE ((apaccnt_vendtype_id=-1)
     AND (vend_id=pVendid));
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

  RETURN -1;

END;
$_$;


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

--
-- TOC entry 1564 (class 1255 OID 146566142)
-- Dependencies: 4536 8
-- Name: findapprepaidaccount(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION findapprepaidaccount(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;
  _accntid INTEGER;

BEGIN

  IF (NOT fetchMetricBool('InterfaceAPToGL')) THEN
    RETURN 0;
  END IF;

--  Check for a Vendor Type specific Account
  SELECT apaccnt_prepaid_accnt_id INTO _accntid
    FROM apaccnt
    JOIN vendinfo ON (apaccnt_vendtype_id=vend_vendtype_id)
  WHERE (vend_id=pVendid);
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

--  Check for a Vendor Type pattern
  SELECT apaccnt_prepaid_accnt_id INTO _accntid
    FROM apaccnt
    JOIN vendtype ON (vendtype_code ~ apaccnt_vendtype)
    JOIN vendinfo ON (vend_vendtype_id=vendtype_id)
  WHERE ((apaccnt_vendtype_id=-1)
     AND (vend_id=pVendid));
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

  RETURN -1;

END;
$_$;


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

--
-- TOC entry 1565 (class 1255 OID 146566143)
-- Dependencies: 4536 8
-- Name: findaraccount(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF (NOT fetchMetricBool('InterfaceARToGL')) THEN
    RETURN 0;
  END IF;

--  Check for a Customer Type specific Account
  SELECT araccnt_ar_accnt_id INTO _accntid
  FROM araccnt, custinfo
  WHERE ( (araccnt_custtype_id=cust_custtype_id)
   AND (cust_id=pCustid) );
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

--  Check for a Customer Type pattern
  SELECT araccnt_ar_accnt_id INTO _accntid
  FROM araccnt, custinfo, custtype
  WHERE ( (custtype_code ~ araccnt_custtype)
   AND (araccnt_custtype_id=-1)
   AND (cust_custtype_id=custtype_id)
   AND (cust_id=pCustid) );
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

  RETURN -1;

END;
$_$;


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

--
-- TOC entry 1566 (class 1255 OID 146566144)
-- Dependencies: 4536 8
-- Name: findardiscountaccount(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION findardiscountaccount(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;
  _accntid INTEGER;

BEGIN

  IF (NOT fetchMetricBool('InterfaceARToGL')) THEN
    RETURN 0;
  END IF;

--  Check for a Customer Type specific Account
  SELECT araccnt_discount_accnt_id INTO _accntid
  FROM araccnt, custinfo
  WHERE ( (araccnt_custtype_id=cust_custtype_id)
   AND (cust_id=pCustid) );
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

--  Check for a Customer Type pattern
  SELECT araccnt_discount_accnt_id INTO _accntid
  FROM araccnt, custinfo, custtype
  WHERE ( (custtype_code ~ araccnt_custtype)
   AND (cust_custtype_id=custtype_id)
   AND (araccnt_custtype_id=-1)
   AND (cust_id=pCustid) );
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

  RETURN -1;

END;
$_$;


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

--
-- TOC entry 1567 (class 1255 OID 146566145)
-- Dependencies: 4536 8
-- Name: findcalendarorigin(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT calhead_origin INTO _originType
  FROM calhead
  WHERE (calhead_id=pCalheadid);

  IF (NOT FOUND) THEN
    _origin := NULL;

  ELSIF (_originType = 'D') THEN
    _origin := CURRENT_DATE;

  ELSIF (_originType = 'E') THEN
    _origin := (CURRENT_DATE + 1);

  ELSIF (_originType = 'W') THEN
    _origin := (CURRENT_DATE - EXTRACT(DOW FROM CURRENT_DATE)::INTEGER);

  ELSIF (_originType = 'X') THEN
    _origin := ((CURRENT_DATE - EXTRACT(DOW FROM CURRENT_DATE)::INTEGER) + INTERVAL '1 week');

  ELSIF (_originType = 'M') THEN
    _origin := date_trunc('month', CURRENT_DATE);

  ELSIF (_originType = 'N') THEN
    _origin := (date_trunc('month', CURRENT_DATE) + INTERVAL '1 month');

  ELSIF (_originType = 'L') THEN
    _origin := (date_trunc('year', CURRENT_DATE) - INTERVAL '1 year');

  ELSIF (_originType = 'Y') THEN
    _origin := date_trunc('year', CURRENT_DATE);

  ELSIF (_originType = 'Z') THEN
    _origin := (date_trunc('year', CURRENT_DATE) + INTERVAL '1 year');

  ELSE
    _origin := NULL;
  END IF;

  RETURN _origin;

  END;
$_$;


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

--
-- TOC entry 1568 (class 1255 OID 146566146)
-- Dependencies: 4536 8
-- Name: findcustomerform(integer, character); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

--  Check for a Customer Type specific Form
  SELECT custform.* INTO _f
    FROM custform
    JOIN custinfo ON (custform_custtype_id=cust_custtype_id)
  WHERE (cust_id=pCustid);

  IF (FOUND) THEN
    _found := TRUE;
  ELSE
--  Check for a Customer Type pattern
    SELECT custform.* INTO _f
      FROM custform
      JOIN custtype ON (custtype_code ~ custform_custtype)
      JOIN custinfo ON (cust_custtype_id=custtype_id)
    WHERE ((custform_custtype_id=-1)
       AND (cust_id=pCustid));

    IF (FOUND) THEN
      _found := TRUE;
    ELSE
      _found := FALSE;
    END IF;
  END IF;

  IF (_found) THEN
    IF ( (pFormType = 'I') AND (_f.custform_invoice_report_name IS NOT NULL) ) THEN
      RETURN _f.custform_invoice_report_name;

    ELSIF ( (pFormType = 'C') AND (_f.custform_creditmemo_report_name IS NOT NULL) ) THEN
      RETURN _f.custform_creditmemo_report_name;

    ELSIF ( (pFormType = 'S') AND (_f.custform_statement_report_name IS NOT NULL) ) THEN
      RETURN _f.custform_statement_report_name;

    ELSIF ( (pFormType = 'Q') AND (_f.custform_quote_report_name IS NOT NULL) ) THEN
      RETURN _f.custform_quote_report_name;

    ELSIF ( (pFormType = 'P') AND (_f.custform_packinglist_report_name IS NOT NULL) ) THEN
      RETURN _f.custform_packinglist_report_name;

    ELSIF ( (pFormType = 'L') AND (_f.custform_sopicklist_report_name IS NOT NULL) ) THEN
      RETURN _f.custform_sopicklist_report_name;
    END IF;

  END IF;

  IF (pFormType = 'I') THEN
    RETURN 'Invoice';
  ELSIF (pFormType = 'C') THEN
    RETURN 'CreditMemo';
  ELSIF (pFormType = 'S') THEN
    RETURN 'Statement';
  ELSIF (pFormType = 'Q') THEN
    RETURN 'Quote';
  ELSIF (pFormType = 'P') THEN
    RETURN 'PackingList-Shipment';
  ELSIF (pFormType = 'L') THEN
    RETURN 'PackingList';
  END IF;

END;
$_$;


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

--
-- TOC entry 1570 (class 1255 OID 146566147)
-- Dependencies: 4536 8
-- Name: finddeferredaccount(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF (NOT fetchMetricBool('InterfaceARToGL')) THEN
    RETURN 0;
  END IF;

--  Check for a Customer Type specific Account
  SELECT araccnt_deferred_accnt_id INTO _accntid
    FROM araccnt
    JOIN custinfo ON (araccnt_custtype_id=cust_custtype_id)
  WHERE (cust_id=pCustid);
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

--  Check for a Customer Type pattern
  SELECT araccnt_deferred_accnt_id INTO _accntid
    FROM araccnt
    JOIN custtype ON (custtype_code ~ araccnt_custtype)
    JOIN custinfo ON (cust_custtype_id=custtype_id)
  WHERE ((araccnt_custtype_id=-1)
     AND (cust_id=pCustid));
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

  RETURN -1;

END;
$_$;


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

--
-- TOC entry 1571 (class 1255 OID 146566148)
-- Dependencies: 4536 8
-- Name: findfreightaccount(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION findfreightaccount(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;
  _accntid INTEGER;

BEGIN

  IF (NOT fetchMetricBool('InterfaceARToGL')) THEN
    RETURN 0;
  END IF;

--  Check for a Customer Type specific Account
  SELECT araccnt_freight_accnt_id INTO _accntid
  FROM araccnt, custinfo
  WHERE ( (araccnt_custtype_id=cust_custtype_id)
   AND (cust_id=pCustid) );
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

--  Check for a Customer Type pattern
  SELECT araccnt_freight_accnt_id INTO _accntid
  FROM araccnt, custinfo, custtype
  WHERE ( (custtype_code ~ araccnt_custtype)
   AND (cust_custtype_id=custtype_id)
   AND (araccnt_custtype_id=-1)
   AND (cust_id=pCustid) );
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

--  Find the default
  SELECT metric_value::INTEGER INTO _accntid
  FROM metric
  WHERE (metric_name='FreightAccount');
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

  RETURN -1;

END;
$_$;


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

--
-- TOC entry 1572 (class 1255 OID 146566149)
-- Dependencies: 4536 8
-- Name: findperiodend(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION findperiodend(integer) RETURNS date
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCalitemid ALIAS FOR $1;
  _calType CHAR(1);
  _calItem RECORD;
  _start DATE;
  _loop INTEGER;

BEGIN

  SELECT calhead_type INTO _calType
  FROM calhead, acalitem
  WHERE ((acalitem_calhead_id=calhead_id)
   AND (acalitem_id=pCalitemid));

  IF (NOT FOUND) THEN
    SELECT calhead_type INTO _calType
    FROM calhead, rcalitem
    WHERE ((rcalitem_calhead_id=calhead_id)
     AND (rcalitem_id=pCalitemid));

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

  IF (_calType = 'A') THEN
    RETURN ( SELECT (findPeriodStart(acalitem_id) + acalitem_periodlength - 1)
             FROM acalitem
             WHERE (acalitem_id=pCalitemid) );

  ELSIF (_calType = 'R') THEN

--  Grab the relative calitem's particulars
    SELECT rcalitem_periodtype, rcalitem_periodcount INTO _calitem
    FROM rcalitem
    WHERE (rcalitem_id=pCalitemid);

    IF (NOT FOUND) THEN
      RETURN NULL;
    END If;

--  Grab the origin of the calitem
    SELECT findPeriodStart(pCalitemid) INTO _start;

    IF (_start IS NULL) THEN

--  If days...
    ELSIF (_calitem.rcalitem_periodtype = 'D') THEN
      _start := (_start + _calitem.rcalitem_periodcount - 1);

--  If weeks... (gotta be a better way)
    ELSIF (_calitem.rcalitem_periodtype = 'W') THEN
      _loop := _calitem.rcalitem_periodcount;

      WHILE (_loop > 0) LOOP
        _start := (_start + INTERVAL '1 week');
        _loop := (_loop - 1);
      END LOOP;

      _start := (_start - 1);

--  If months... (gotta be a better way)
    ELSIF (_calitem.rcalitem_periodtype = 'M') THEN
      _loop := _calitem.rcalitem_periodcount;

      WHILE (_loop > 0) LOOP
        _start := (_start + INTERVAL '1 month');
        _loop := (_loop - 1);
      END LOOP;

      _start := (_start - 1);

--  If years... (gotta be a better way)
    ELSIF (_calitem.rcalitem_periodtype = 'Y') THEN
      _loop := _calitem.rcalitem_periodcount;

      WHILE (_loop > 0) LOOP
        _start := (_start + INTERVAL '1 year');
        _loop := (_loop - 1);
      END LOOP;

      _start := (_start - 1);

    ELSE
      _start := NULL;
    END IF;

  ELSE
    _start := NULL;
  END IF;

  RETURN _start;

END;
$_$;


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

--
-- TOC entry 1573 (class 1255 OID 146566150)
-- Dependencies: 4536 8
-- Name: findperiodstart(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION findperiodstart(integer) RETURNS date
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCalitemid ALIAS FOR $1;
  _calType CHAR(1);
  _calItem RECORD;
  _start DATE;
  _loop INTEGER;

BEGIN

  SELECT calhead_type INTO _calType
  FROM calhead, acalitem
  WHERE ((acalitem_calhead_id=calhead_id)
   AND (acalitem_id=pCalitemid));

  IF (NOT FOUND) THEN
    SELECT calhead_type INTO _calType
    FROM calhead, rcalitem
    WHERE ((rcalitem_calhead_id=calhead_id)
     AND (rcalitem_id=pCalitemid));

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

  IF (_calType = 'A') THEN
    RETURN ( SELECT acalitem_periodstart
             FROM acalitem
             WHERE (acalitem_id=pCalitemid) );

  ELSIF (_calType = 'R') THEN

--  Grab the relative calitem's particulars
    SELECT rcalitem_offsettype, rcalitem_offsetcount INTO _calitem
    FROM rcalitem
    WHERE (rcalitem_id=pCalitemid);

    IF (NOT FOUND) THEN
      RETURN NULL;
    END If;

--  Grab the origin of the calitem's parend calhead
    SELECT findCalendarOrigin(calhead_id) INTO _start
    FROM calhead, rcalitem
    WHERE ((rcalitem_calhead_id=calhead_id)
     AND (rcalitem_id=pCalitemid));

--  If days...
    IF (_calitem.rcalitem_offsettype = 'D') THEN
      _start := (_start + _calitem.rcalitem_offsetcount);

--  If weeks...
    ELSIF (_calitem.rcalitem_offsettype = 'W') THEN
      _start := (_start + (_calitem.rcalitem_offsetcount * 7));

--  If months... (gotta be a better way)
    ELSIF (_calitem.rcalitem_offsettype = 'M') THEN
      _loop := _calitem.rcalitem_offsetcount;

      IF (_loop > 0) THEN
        WHILE (_loop > 0) LOOP
          _start := (_start + INTERVAL '1 month');
          _loop := _loop - 1;
        END LOOP;
      ELSE
        WHILE (_loop < 0) LOOP
          _start := (_start - INTERVAL '1 month');
          _loop := _loop + 1;
        END LOOP;
      END IF;

--  If years... (gotta be a better way)
    ELSIF (_calitem.rcalitem_offsettype = 'Y') THEN
      _loop := _calitem.rcalitem_offsetcount;

      IF (_loop > 0) THEN
        WHILE (_loop > 0) LOOP
          _start := (_start + INTERVAL '1 year');
          _loop := _loop - 1;
        END LOOP;
      ELSE
        WHILE (_loop < 0) LOOP
          _start := (_start - INTERVAL '1 year');
          _loop := _loop + 1;
        END LOOP;
      END IF;

    ELSE
      _start := NULL;
    END IF;

  ELSE
    _start := NULL;
  END IF;

  RETURN _start;

END;
$_$;


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

--
-- TOC entry 1574 (class 1255 OID 146566151)
-- Dependencies: 4536 8
-- Name: findprepaidaccount(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION findprepaidaccount(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;
  _accntid INTEGER;

BEGIN

  IF (NOT fetchMetricBool('InterfaceARToGL')) THEN
    RETURN 0;
  END IF;

--  Check for a Customer Type specific Account
  SELECT araccnt_prepaid_accnt_id INTO _accntid
    FROM araccnt
    JOIN custinfo ON (araccnt_custtype_id=cust_custtype_id)
  WHERE (cust_id=pCustid);
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

--  Check for a Customer Type pattern
  SELECT araccnt_prepaid_accnt_id INTO _accntid
    FROM araccnt
    JOIN custtype ON (custtype_code ~ araccnt_custtype)
    JOIN custinfo ON (cust_custtype_id=custtype_id)
  WHERE ((araccnt_custtype_id=-1)
     AND (cust_id=pCustid) );
  IF (FOUND) THEN
    RETURN _accntid;
  END IF;

  RETURN -1;

END;
$_$;


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

--
-- TOC entry 1575 (class 1255 OID 146566152)
-- Dependencies: 4536 8
-- Name: findsalesaccnt(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1576 (class 1255 OID 146566153)
-- Dependencies: 4536 8
-- Name: findsalesaccnt(integer, text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION findsalesaccnt(integer, 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.
BEGIN
  RETURN findSalesAccnt($1, $2, $3, NULL, NULL);
END;
$_$;


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

--
-- TOC entry 1577 (class 1255 OID 146566154)
-- Dependencies: 4536 8
-- Name: findsalesaccnt(integer, text, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION findsalesaccnt(pid integer, pidtype text, pcustid integer, psaletypeid integer, pshipzoneid 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
  _s RECORD;

BEGIN

  IF (pidType = 'I') THEN
    --  Check for a custtype specific rule
    SELECT salesaccnt_id,
           CASE WHEN (salesaccnt_warehous_id<>-1) THEN 1 ELSE 0 END +
           CASE WHEN (salesaccnt_custtype_id<>-1) THEN 2 ELSE 0 END +
           CASE WHEN (salesaccnt_prodcat_id<>-1) THEN 3 ELSE 0 END +
           CASE WHEN (salesaccnt_shipzone_id<>-1) THEN 4 ELSE 0 END +
           CASE WHEN (salesaccnt_saletype_id<>-1) THEN 5 ELSE 0 END AS orderby
    INTO _s
    FROM salesaccnt, item, prodcat, custinfo, custtype
    WHERE ( (salesaccnt_warehous_id=-1)
      AND  (item_prodcat_id=prodcat_id)
      AND  (cust_custtype_id=custtype_id)
      AND  ( (salesaccnt_prodcat='.*') OR
	    ( (salesaccnt_prodcat_id=-1) AND
	      (salesaccnt_prodcat<>'') AND
	      (prodcat_code ~ salesaccnt_prodcat) ) OR
	    ( (salesaccnt_prodcat_id=prodcat_id) ) )
      AND  ( (salesaccnt_custtype='.*') OR
	    ( (salesaccnt_custtype_id=-1) AND
	      (salesaccnt_custtype<>'') AND
	      (custtype_code ~ salesaccnt_custtype) ) OR
	    ( (salesaccnt_custtype_id=custtype_id) ) )
      AND  ( (salesaccnt_shipzone_id=-1) OR
	     (salesaccnt_shipzone_id=pShipzoneid) )
      AND  ( (salesaccnt_saletype_id=-1) OR
	     (salesaccnt_saletype_id=pSaletypeid) )
      AND (item_id=pid)
      AND (cust_id=pCustid) )
    ORDER BY orderby DESC, salesaccnt_custtype DESC, salesaccnt_prodcat DESC,
             salesaccnt_saletype_id DESC, salesaccnt_shipzone_id DESC
     LIMIT 1;

  ELSIF (pidType = 'IS') THEN
    --  Check for a custtype specific rule
    SELECT salesaccnt_id,
           CASE WHEN (salesaccnt_warehous_id<>-1) THEN 1 ELSE 0 END +
           CASE WHEN (salesaccnt_custtype_id<>-1) THEN 2 ELSE 0 END +
           CASE WHEN (salesaccnt_prodcat_id<>-1) THEN 3 ELSE 0 END +
           CASE WHEN (salesaccnt_shipzone_id<>-1) THEN 4 ELSE 0 END +
           CASE WHEN (salesaccnt_saletype_id<>-1) THEN 5 ELSE 0 END AS orderby
    INTO _s
    FROM salesaccnt, itemsite, item, prodcat, custinfo, custtype
    WHERE ( ( (salesaccnt_warehous_id=-1) OR
	      (salesaccnt_warehous_id=itemsite_warehous_id) )
     AND (itemsite_item_id=item_id)
     AND (item_prodcat_id=prodcat_id)
     AND (cust_custtype_id=custtype_id)
     AND ( (salesaccnt_prodcat='.*') OR
	   ( (salesaccnt_prodcat_id=-1) AND
	     (salesaccnt_prodcat<>'') AND
	     (prodcat_code ~ salesaccnt_prodcat) ) OR
	   ( (salesaccnt_prodcat_id=prodcat_id) ) )
     AND ( (salesaccnt_custtype='.*') OR
	   ( (salesaccnt_custtype_id=-1) AND
	     (salesaccnt_custtype<>'') AND
	     (custtype_code ~ salesaccnt_custtype) ) OR
	   ( (salesaccnt_custtype_id=custtype_id) ) )
     AND  ( (salesaccnt_shipzone_id=-1) OR
            (salesaccnt_shipzone_id=pShipzoneid) )
     AND  ( (salesaccnt_saletype_id=-1) OR
            (salesaccnt_saletype_id=pSaletypeid) )
     AND (itemsite_id=pid)
     AND (cust_id=pCustid) ) 
    ORDER BY orderby DESC, salesaccnt_custtype DESC, salesaccnt_prodcat DESC, salesaccnt_warehous_id DESC,
             salesaccnt_saletype_id DESC, salesaccnt_shipzone_id DESC
    LIMIT 1;

  ELSE
    RETURN -2;	-- invalid pidType
  END IF;

  IF (FOUND) THEN
    RETURN _s.salesaccnt_id;
  END IF;

  RETURN -1;

END;
$$;


ALTER FUNCTION public.findsalesaccnt(pid integer, pidtype text, pcustid integer, psaletypeid integer, pshipzoneid integer) OWNER TO admin;

--
-- TOC entry 1579 (class 1255 OID 146566155)
-- Dependencies: 4536 8
-- Name: findspecialfinancial(text, text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION findspecialfinancial(text, text, 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
  pUnit ALIAS FOR $1;
  pType ALIAS FOR $2;
  pPeriodid ALIAS FOR $3;

  _value NUMERIC;
BEGIN

  _value := 0.00;

  IF ('OpenAR' = pType) THEN
    IF ( pUnit IN ('D','E') ) THEN
      SELECT SUM( CASE WHEN (aropen_doctype IN ('C', 'R')) THEN ((aropen_amount - aropen_paid) * -1)
                       ELSE (aropen_amount - aropen_paid) END ) INTO _value
        FROM aropen, period
       WHERE ((aropen_open)
         AND  (aropen_duedate BETWEEN period_start AND period_end)
         AND  (period_id=pPeriodid));

      IF ('E' = pUnit) THEN
        _value := 0.00 - _value;
      END IF;
    END IF;
  END IF;

  IF ('OpenAP' = pType) THEN
    IF ( pUnit IN ('C','E') ) THEN
      SELECT SUM( CASE WHEN (apopen_doctype='C') THEN ((apopen_amount - apopen_paid) * -1)
                       ELSE (apopen_amount - apopen_paid) END ) INTO _value
        FROM apopen, period
       WHERE ((apopen_open)
         AND  (apopen_duedate BETWEEN period_start AND period_end)
         AND  (period_id=pPeriodid));
    END IF;
  END IF;

  RETURN _value;

END;
$_$;


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

--
-- TOC entry 1580 (class 1255 OID 146566156)
-- Dependencies: 4536 8
-- Name: findxt(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION findxt() RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
  IF EXISTS (select relname from pg_class where relname = 'ext')
THEN
PERFORM xt.js_init();
END IF;
END;
$$;


ALTER FUNCTION public.findxt() OWNER TO admin;

--
-- TOC entry 1581 (class 1255 OID 146566157)
-- Dependencies: 8
-- Name: first_agg(anyelement, anyelement); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION first_agg(anyelement, anyelement) RETURNS anyelement
    LANGUAGE sql STABLE
    AS $_$
  SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
$_$;


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

--
-- TOC entry 1582 (class 1255 OID 146566158)
-- Dependencies: 4536 8
-- Name: firstline(text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  SELECT regexp_replace(pSource, E'^(\r?\n)*([^\r\n]*)\r?\n.*', E'\\2') INTO _result;
  RETURN _result;
END;
$_$;


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

--
-- TOC entry 1583 (class 1255 OID 146566159)
-- Dependencies: 4536 8
-- Name: fixacl(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION fixacl() RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _r        RECORD;
  _count    INTEGER := 0;
  _oldgrp   BOOLEAN := false;
  _objtype  TEXT;
  _table    TEXT;
  _schema   TEXT;

BEGIN
  IF EXISTS(SELECT 1 FROM pg_group WHERE groname = 'openmfg') THEN
    _oldgrp := true;
  END IF;
  
  FOR _r IN SELECT relname, nspname, relkind,
                   CASE relkind WHEN 'r' THEN 1
                                WHEN 'v' THEN 2
                                WHEN 'S' THEN 3
                                ELSE 4
                   END AS seq
            FROM pg_catalog.pg_class c, pg_namespace n
            WHERE ((n.oid=c.relnamespace)
              AND  (nspname in ('public', 'api') OR
                    nspname in (SELECT pkghead_name FROM pkghead))
              AND  (relkind in ('S', 'r', 'v')))
            ORDER BY seq
  LOOP
    _schema := quote_ident(_r.nspname);
    _table  := quote_ident(_r.relname);

    RAISE DEBUG '%.%', _schema, _table;
    
    IF (_oldgrp) THEN
      EXECUTE 'REVOKE ALL ON ' || _schema || '.' || _table || ' FROM openmfg;';
    END IF;
    EXECUTE 'REVOKE ALL ON ' || _schema || '.' || _table || ' FROM PUBLIC;';
    EXECUTE 'GRANT ALL ON '  || _schema || '.' || _table || ' TO GROUP xtrole;';
    _count := _count + 1;

    _objtype := CASE _r.relkind WHEN 'S' THEN 'SEQUENCE'
                                WHEN 'r' THEN 'TABLE'
                                WHEN 'v' THEN 'VIEW'
                                ELSE NULL
                END;
    IF (_objtype IS NOT NULL) THEN
      BEGIN
        EXECUTE 'ALTER ' || _objtype || ' ' ||
                _schema || '.' || _table || ' OWNER TO admin;';
      EXCEPTION WHEN OTHERS THEN
        RAISE WARNING 'Could not change ownership of %.% to admin',
                      _schema, _table;
      END;
    END IF;

  END LOOP;

  RETURN _count;
END;
$$;


ALTER FUNCTION public.fixacl() OWNER TO admin;

--
-- TOC entry 1584 (class 1255 OID 146566160)
-- Dependencies: 2648 4536 8
-- Name: formatabachecks(integer, integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatabachecks(integer, integer, text) RETURNS SETOF achline
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;   -- all unprinted checks for this bankaccnt
  pcheckheadid     ALIAS FOR $2;   -- but if 2nd arg not null then just 1 check
  penckey          ALIAS FOR $3;
  _bank            RECORD;
  _batchcount      INTEGER := 0;
  _batchdate       DATE;
  _check           RECORD;
  _vendnumber      TEXT;
  _vendname        TEXT;
  _filenum         TEXT;
  _prevsec         TEXT;
  _row             achline%ROWTYPE;
  _totalcr         NUMERIC := 0;
  _totaldb         NUMERIC := 0;
  _detailcount     INTEGER := 0;     -- count of type 1 entries
  _vendbsb         TEXT;

BEGIN
  -- General notes:
  -- Numeric values are formatted using RPAD(TO_CHAR(#, '0..0SG', #)).
  --    TO_CHAR(#, ...) (at least in the default server locale) puts a space at
  --    the beginning of the string for numbers >= 0 and '-' for numbers < 0.
  --    'SG' pushes the sign char to the end, then RPAD cuts it off.
  -- This whole thing is for Australian bank transactions only, and generates entries for an ABA file.
  -- Currently restricted to checks to Vendor; there's no support for checks to
  --    customers or tax authorities, or for debits or corrections.
  -- This function has been adapted from the US-centric ACH formatACHChecks function.

  IF (NOT fetchMetricBool('ACHEnabled')) THEN
    RAISE EXCEPTION 'Cannot format the ABA file because the system is not configured for ABA file generation.';
  END IF;
  IF (LENGTH(COALESCE(penckey, '')) <= 0) THEN
    RAISE EXCEPTION 'Cannot format the ABA file because there is no encryption key.';
  END IF;

  SELECT * INTO _bank
  FROM bankaccnt
  WHERE (bankaccnt_id=pbankaccntid);

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Could not find the bank information to create the ABA file.';
  ELSIF (NOT _bank.bankaccnt_ach_enabled) THEN
    RAISE EXCEPTION 'Cannot format the ABA file because the Bank Account % is not configured for ABA transactions.',
      _bank.bankaccnt_name;
  ELSIF (LENGTH(COALESCE(_bank.bankaccnt_routing, '')) <= 0) THEN 
    RAISE EXCEPTION 'Cannot format the ABA file because the Bank Account % has no BSB number.', _bank.bankaccnt_name;
  END IF;

  -- Check the BSB number is in the right format and then re-format for output.
  -- Valid format is \d{3}-\d{3}|\d{6}000
  IF (_bank.bankaccnt_routing ~ E'^(\\d{3})(?:-(?=\\d{3}$)|(?=\\d{3}0{3}$))(\\d{3})(0{3})?$') THEN
    _bank.bankaccnt_routing := regexp_replace(
      _bank.bankaccnt_routing,
      E'^(\\d{3})(?:-(?=\\d{3}$)|(?=\\d{3}0{3}$))(\\d{3})(0{3})?$', E'\\1-\\2'
    );
  ELSE RAISE EXCEPTION 'Cannot format the ABA file because the Bank Account % has an invalid BSB number.',
    _bank.bankaccnt_name;
  END IF;


  _filenum := LPAD(fetchNextNumber('ACHBatch'), 8, '0');

  IF (COALESCE(_bank.bankaccnt_ach_lastdate,startOfTime()) < CURRENT_DATE
    OR _bank.bankaccnt_ach_lastfileid IS NULL) THEN
    _bank.bankaccnt_ach_lastfileid = '0';
  ELSIF (_bank.bankaccnt_ach_lastfileid = '9') THEN
    _bank.bankaccnt_ach_lastfileid = 'A';
  ELSIF (_bank.bankaccnt_ach_lastfileid = 'Z') THEN
    RAISE EXCEPTION 'Cannot write % check % to an ABA file because too many files have been written for this bank already today.',
      _bank.bankaccnt_name, _check.checkhead_number;
  ELSE
    _bank.bankaccnt_ach_lastfileid = CHR(ASCII(_bank.bankaccnt_ach_lastfileid) + 1);
  END IF;
  

  _row.achline_checkhead_id := NULL;
  _row.achline_batch := _filenum;
  _row.achline_type := 'HEADER';
  _row.achline_value := RPAD(
    RPAD('0',18)                                    -- Record Type 0 blank filled with 17 spaces
    || '01'                                         -- Reel sequence number 
    || RPAD(_bank.bankaccnt_bankname,3)             -- Approved financial instition abbreviation.
    || RPAD('',7)                                   -- blank filled
    || RPAD(fetchMetricText('ACHCompanyName'), 26)  -- Name of user supplying ABA file
    || LPAD(fetchMetricText('ACHCompanyId'),6)      -- User identification number APCA issued
    || RPAD('PAYMENT',12)                           -- description of entries on file
                                                    --  currently only use payment description
    || TO_CHAR(CURRENT_DATE,      'DDMMYY'),        -- date to be processed
    120                                             -- blank filled to 120 characters
  );
  RETURN NEXT _row;

  FOR _check IN SELECT *
    FROM checkhead
    JOIN vendinfo ON (checkhead_recip_type='V'
      AND checkhead_recip_id=vend_id
      AND vend_ach_enabled)
    JOIN curr_symbol ON (checkhead_curr_id=curr_id)
    LEFT OUTER JOIN crmacct ON (crmacct_vend_id=vend_id)
    WHERE ((checkhead_bankaccnt_id=pbankaccntid)
      AND (checkhead_amount > 0)
      AND (checkhead_id=pcheckheadid OR pcheckheadid IS NULL)
      AND NOT checkhead_posted
      AND NOT checkhead_replaced
      AND NOT checkhead_deleted
      AND NOT checkhead_void
      AND NOT checkhead_printed
      AND (LENGTH(COALESCE(checkhead_ach_batch,'')) <= 0)
      AND (curr_abbr='AUD'))
    ORDER BY checkhead_checkdate, vend_name LOOP

    IF (COALESCE(_check.checkhead_number, -1) <= 0
      AND _bank.bankaccnt_ach_genchecknum) THEN
        _check.checkhead_number := fetchNextCheckNumber(_check.checkhead_bankaccnt_id);
    END IF;

    -- Although a crmacct record is not required for used in this function
    -- this code is retained for consistancy with the original formatachchecks function.
    IF (_check.crmacct_id IS NULL) THEN
      RAISE NOTICE 'Vendor % does not have a corresponding crmacct record.',
        _check.checkhead_recip_id;
    ELSIF (_check.crmacct_type IS NULL) THEN
      RAISE NOTICE 'crmacct for vendor % does not have a valid crmacct_type.',
         _check.checkhead_recip_id;
    END IF;

    _vendnumber := CASE WHEN _check.vend_ach_use_vendinfo THEN _check.vend_number
      ELSE _check.vend_ach_indiv_number
      END;
    _vendname := CASE WHEN _check.vend_ach_use_vendinfo THEN _check.vend_name
      ELSE _check.vend_ach_indiv_name
      END;

    IF (COALESCE(_check.vend_ach_routingnumber, '') = '') THEN
      RAISE EXCEPTION 'Cannot write % check % to an ABA file because the BSB number for % has not been supplied.',
        _bank.bankaccnt_name, _check.checkhead_number, _vendnumber;
    ELSIF (COALESCE(_check.vend_ach_accntnumber, '') = '') THEN
      RAISE EXCEPTION 'Cannot write % check % to an ABA file because the account number for % has not been supplied.',
        _bank.bankaccnt_name, _check.checkhead_number, _vendnumber;
    END IF;
    _check.vend_ach_routingnumber := decrypt(setbytea(_check.vend_ach_routingnumber),
      setbytea(penckey), 'bf');
    _check.vend_ach_accntnumber   := decrypt(setbytea(_check.vend_ach_accntnumber),
      setbytea(penckey), 'bf');
    
    -- Check the BSB number is in the right format and then re-format for output.
    -- Valid format is \d{3}-\d{3}|\d{6}000
    IF (formatbytea(_check.vend_ach_routingnumber) ~ E'^(\\d{3})(?:-(?=\\d{3}$)|(?=\\d{3}0{3}$))(\\d{3})(0{3})?$') THEN
      _vendbsb := regexp_replace(
        formatbytea(_check.vend_ach_routingnumber),
        E'^(\\d{3})(?:-(?=\\d{3}$)|(?=\\d{3}0{3}$))(\\d{3})(0{3})?$', E'\\1-\\2'
      );
    ELSE RAISE EXCEPTION 'Cannot write % check % to an ABA file because the BSB number for % is not valid.',
      _bank.bankaccnt_name, _check.checkhead_number, _vendnumber;
    END IF;

    _row.achline_checkhead_id := _check.checkhead_id;
    _row.achline_batch        := _filenum;
    _row.achline_type         := 'DETAIL';

    _totaldb      := _totaldb + _check.checkhead_amount;                -- Total debits for balancing entry
    _detailcount  := _detailcount + 1;                                  -- Detail record counter (type 1)
    _totalcr      := _totalcr + _check.checkhead_amount;                -- Total credits from payments
                                                                        
    _row.achline_value := RPAD('1'                                      -- record type 1
      || _vendbsb                                                       -- vendor BSB #
      || LPAD(formatbytea(_check.vend_ach_accntnumber), 9)              -- vendor account no.
      ||' '                                                             -- withholding tax indicator
      ||'50'                                                            -- transaction code, this should be calculated.
      || LPAD(to_char(_check.checkhead_amount,'FM99999999V99'),10,'0')  -- amount
      || RPAD(_vendname,   32)                                          -- vendor name
      || RPAD('Deposit',8) || RPAD('#'  , 2) || LPAD (_filenum,8,' ')   -- Lodgement Reference
      || _bank.bankaccnt_routing                                        -- BSB #
      || RPAD(_bank.bankaccnt_accntnumber, 9)                           -- company account number
      || RPAD(fetchMetricText('ACHCompanyName'), 16)                    -- company account name
      || LPAD('', 8, '0'),                                              -- Witholding Tax Amount
      120                                                               -- line width
    );
    RETURN NEXT _row;

    UPDATE checkhead
    SET checkhead_ach_batch=_filenum,
      checkhead_number=_check.checkhead_number
    WHERE (checkhead_id=_check.checkhead_id);

  END LOOP;

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Cannot write an ABA file for % because there are no checks pending in AUD for EFT-enabled Vendors.',
      _bank.bankaccnt_name;
  END IF;

  -- Place a final balancing detail record.
  -- Check that the balancing record actually balances.
  IF (_totalcr != _totaldb) THEN
    RAISE EXCEPTION 'Cannot write an ABA file for % because the total credits: % does not equal the total debits: %, file will not balance.',
    _bank.bankaccnt_name, _totalcr, _totaldb;
  END IF;
  

  _detailcount := _detailcount + 1;
  _row.achline_checkhead_id := NULL;
  _row.achline_batch := _filenum;
  _row.achline_type := 'BALANCING';
  -- keep in sync with the other batchcontrol record format above
  -- THE FOLLOWING IS THE DEBIT BALANCING RECORD
  _row.achline_value := RPAD('1'                          -- record type 1
    || _bank.bankaccnt_routing                            -- Austalian BSB #
    || LPAD(_bank.bankaccnt_accntnumber, 9)               -- company account no.
    || ' '                                                -- withholding tax indicator
    || '13'                                               -- transaction code
    || to_char(_totaldb,'FM09999999V99')                  -- the balancing amount
    || RPAD(fetchMetricText('ACHCompanyName'),   32)      -- company name
    || RPAD('DIRECT DEPOSIT',18)                          
    || _bank.bankaccnt_routing                            -- Austalian BSB #
    || RPAD(_bank.bankaccnt_accntnumber, 9)               -- company account number
    || RPAD(fetchMetricText('ACHCompanyName'), 16)        -- company account name
    || LPAD('', 8, '0'),                                  -- Witholding Tax Amount
    120                                                   -- line width
  );
  RETURN NEXT _row;

  RAISE DEBUG 'formatABAChecks building TRAILER with _totaldb %, _totalcr %, _detailcount %',
               _totaldb, _totalcr, _detailcount;
  -- file control record
  _row.achline_checkhead_id := NULL;
  _row.achline_batch := _filenum;
  _row.achline_type := 'TRAILER';
  _row.achline_value := RPAD('7'                                    -- record type 7
    || RPAD('999-999',   7)                                         -- BSB format filler
    || RPAD('' , 12)                                                -- blank
    || LPAD(to_char((_totaldb - _totalcr),'FM09999999V99'),10,'0')  -- net total amount
    || LPAD(to_char(_totalcr, 'FM09999999V99'),10,'0')              -- total credit amount
    || LPAD(to_char(_totaldb, 'FM09999999V99'),10,'0')              -- total debit amount
    || RPAD('', 24)                                                 -- blank
    || RPAD(to_char(_detailcount, 'FM000000'), 6,'0'),              -- count of type 1 records
    120                                                             -- blank fill
  );

  RETURN NEXT _row;


  UPDATE bankaccnt
  SET bankaccnt_ach_lastdate=CURRENT_DATE,
    bankaccnt_ach_lastfileid=_bank.bankaccnt_ach_lastfileid
  WHERE (bankaccnt_id=_bank.bankaccnt_id);

  RETURN;

END;
$_$;


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

--
-- TOC entry 1585 (class 1255 OID 146566162)
-- Dependencies: 2648 4536 8
-- Name: formatachchecks(integer, integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatachchecks(integer, integer, text) RETURNS SETOF achline
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;   -- all unprinted checks for this bankaccnt
  pcheckheadid     ALIAS FOR $2;   -- but if 2nd arg not null then just 1 check
  penckey          ALIAS FOR $3;
  _bank            RECORD;
  _batchcount      INTEGER := 0;
  _batchcr         NUMERIC := 0;
  _batchdate       DATE;
  _batchdb         NUMERIC := 0;
  _batchhash       INTEGER := 0;
  _check           RECORD;
  _ccdnumber       TEXT;
  _ccdname         TEXT;
  _entrycount      INTEGER := 0;
  _filenum         TEXT;
  _prevsec         TEXT;
  _row             achline%ROWTYPE;
  _rowcount        INTEGER := 0;
  _sec             TEXT;
  _serviceclass    TEXT := '200';    -- 220 = credits, 225 = debits, 200 = mixed
  _totalcr         NUMERIC := 0;
  _totaldb         NUMERIC := 0;
  _totalentrycnt   INTEGER := 0;
  _totalhash       INTEGER := 0;
  _transactionprefix TEXT;

BEGIN
  -- General notes:
  -- Numeric values are formatted using RPAD(TO_CHAR(#, '0..0SG', #)).
  --    TO_CHAR(#, ...) (at least in the default server locale) puts a space at
  --    the beginning of the string for numbers >= 0 and '-' for numbers < 0.
  --    'SG' pushes the sign char to the end, then RPAD cuts it off.
  -- This whole thing is US-centric, as that's where the NACHA is.
  -- Currently restricted to checks to Vendor; there's no support for checks to
  --    customers or tax authorities, or for debits or corrections.

  IF (NOT fetchMetricBool('ACHEnabled')) THEN
    RAISE EXCEPTION 'Cannot format the ACH file because the system is not configured for ACH file generation.';
  END IF;
  IF (LENGTH(COALESCE(penckey, '')) <= 0) THEN
    RAISE EXCEPTION 'Cannot format the ACH file because there is no encryption key.';
  END IF;

  SELECT * INTO _bank
  FROM bankaccnt
  WHERE (bankaccnt_id=pbankaccntid);

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Could not find the bank information to create the ACH file.';
  ELSIF (NOT _bank.bankaccnt_ach_enabled) THEN
    RAISE EXCEPTION 'Cannot format the ACH file because the Bank Account % is not configured for ACH transactions.',
      _bank.bankaccnt_name;
  ELSIF (LENGTH(COALESCE(_bank.bankaccnt_routing, '')) <= 0) THEN 
    RAISE EXCEPTION 'Cannot format the ACH file because the Bank Account % has no routing number.',
      _bank.bankaccnt_name;
  END IF;

  _filenum := LPAD(fetchNextNumber('ACHBatch'), 8, '0');

  IF (COALESCE(_bank.bankaccnt_ach_lastdate,startOfTime()) < CURRENT_DATE
      OR _bank.bankaccnt_ach_lastfileid IS NULL) THEN
    _bank.bankaccnt_ach_lastfileid = '0';
  ELSIF (_bank.bankaccnt_ach_lastfileid = '9') THEN
    _bank.bankaccnt_ach_lastfileid = 'A';
  ELSIF (_bank.bankaccnt_ach_lastfileid = 'Z') THEN
    RAISE EXCEPTION 'Cannot write % check % to an ACH file because too many files have been written for this bank already today.',
                  _bank.bankaccnt_name, _check.checkhead_number;
  ELSE
    _bank.bankaccnt_ach_lastfileid = CHR(ASCII(_bank.bankaccnt_ach_lastfileid) + 1);
  END IF;

  _rowcount := _rowcount + 1;
  _row.achline_checkhead_id := NULL;
  _row.achline_batch := _filenum;
  _row.achline_type := 'FILEHEADER';
  _row.achline_value := RPAD('1'
                          || '01'
                          || RPAD(CASE WHEN _bank.bankaccnt_ach_desttype = 'B' THEN ' ' || _bank.bankaccnt_routing
                                       WHEN _bank.bankaccnt_ach_desttype = 'F' THEN ' ' || _bank.bankaccnt_ach_fed_dest
                                       ELSE _bank.bankaccnt_ach_dest END, 10)
                          || RPAD(CASE WHEN _bank.bankaccnt_ach_origintype = 'B' THEN ' ' || _bank.bankaccnt_routing
                                       WHEN _bank.bankaccnt_ach_origintype = 'I' THEN formatAchCompanyId()
                                       ELSE _bank.bankaccnt_ach_origin END, 10)
                          || TO_CHAR(CURRENT_DATE,      'YYMMDD')
                          || TO_CHAR(CURRENT_TIMESTAMP, 'HH24MM')
                          || UPPER(_bank.bankaccnt_ach_lastfileid)
                          || '094'
                          || '10'
                          || '1'
                          || RPAD(CASE WHEN _bank.bankaccnt_ach_desttype = 'B' THEN _bank.bankaccnt_bankname
                                       WHEN _bank.bankaccnt_ach_desttype = 'F' THEN 'Federal Reserve'
                                       ELSE _bank.bankaccnt_ach_destname END, 23)
                          || RPAD(CASE WHEN _bank.bankaccnt_ach_origintype = 'B' THEN ' ' || _bank.bankaccnt_bankname
                                       WHEN _bank.bankaccnt_ach_origintype = 'I' THEN fetchMetricText('ACHCompanyName')
                                       ELSE _bank.bankaccnt_ach_originname END, 23)
                          || RPAD(_filenum, 8),
                          94);
  RETURN NEXT _row;

  FOR _check IN SELECT *
                FROM checkhead
                JOIN vendinfo ON (checkhead_recip_type='V'
                              AND checkhead_recip_id=vend_id
                              AND vend_ach_enabled)
                JOIN curr_symbol ON (checkhead_curr_id=curr_id)
                LEFT OUTER JOIN crmacct ON (crmacct_vend_id=vend_id)
                WHERE ((checkhead_bankaccnt_id=pbankaccntid)
                   AND (checkhead_amount > 0)
                   AND (checkhead_id=pcheckheadid OR pcheckheadid IS NULL)
                   AND NOT checkhead_posted
                   AND NOT checkhead_replaced
                   AND NOT checkhead_deleted
                   AND NOT checkhead_void
                   AND NOT checkhead_printed
                   AND (LENGTH(COALESCE(checkhead_ach_batch,'')) <= 0)
                   AND (curr_abbr='USD'))
                ORDER BY checkhead_checkdate, vend_name LOOP

    IF (COALESCE(_check.checkhead_number, -1) <= 0
        AND _bank.bankaccnt_ach_genchecknum) THEN
      _check.checkhead_number := fetchNextCheckNumber(_check.checkhead_bankaccnt_id);
    END IF;

    _prevsec := _sec;

    IF (_check.crmacct_type = 'I') THEN
      _sec := 'PPD';
    ELSE
      _sec := 'CCD';
      IF (_check.crmacct_id IS NULL) THEN
        RAISE NOTICE 'Vendor % does not have a corresponding crmacct record.',
                     _check.checkhead_recip_id;
      ELSIF (_check.crmacct_type IS NULL) THEN
        RAISE NOTICE 'crmacct for vendor % does not have a valid crmacct_type.',
                     _check.checkhead_recip_id;
      END IF;
    END IF;

    _ccdnumber := CASE WHEN _check.vend_ach_use_vendinfo THEN _check.vend_number
                       ELSE _check.vend_ach_indiv_number
                  END;
    _ccdname := CASE WHEN _check.vend_ach_use_vendinfo THEN _check.vend_name
                     ELSE _check.vend_ach_indiv_name
                END;

    IF (COALESCE(_check.vend_ach_routingnumber, '') = '') THEN
      RAISE EXCEPTION 'Cannot write % check % to an ACH file because the routing number for % has not been supplied.',
                  _bank.bankaccnt_name, _check.checkhead_number, _ccdnumber;
    ELSIF (COALESCE(_check.vend_ach_accntnumber, '') = '') THEN
      RAISE EXCEPTION 'Cannot write % check % to an ACH file because the account number for % has not been supplied.',
                  _bank.bankaccnt_name, _check.checkhead_number, _ccdnumber;
    END IF;
    _check.vend_ach_routingnumber := decrypt(setbytea(_check.vend_ach_routingnumber),
                                         setbytea(penckey), 'bf');
    _check.vend_ach_accntnumber   := decrypt(setbytea(_check.vend_ach_accntnumber),
                                         setbytea(penckey), 'bf');
    _transactionprefix := CASE WHEN (_check.vend_ach_accnttype = 'K') THEN '2'
                               WHEN (_check.vend_ach_accnttype = 'C') THEN '3'
                          END;

    -- create separate batches for each check date and for PPD vs CCD
    IF (COALESCE(_batchdate, startOfTime()) != _check.checkhead_checkdate
        OR (_prevsec != _sec)) THEN
      IF (_batchcount > 0) THEN
        _rowcount := _rowcount + 1;
        _row.achline_checkhead_id := NULL;
        _row.achline_batch := _filenum;
        _row.achline_type := 'BATCHCONTROL';
        -- keep in sync with the other batchcontrol record format below
        _row.achline_value := RPAD('8'
                                || _serviceclass
                                || RPAD(TO_CHAR(_entrycount, '000000SG'), 6)
                                || RPAD(TO_CHAR(_batchhash % 10000000000,
                                                '0000000000SG'), 10)
                                || RPAD(TO_CHAR(_batchdb, '0000000000V99SG'), 12)
                                || RPAD(TO_CHAR(_batchcr, '0000000000V99SG'), 12)
                                || RPAD(formatAchCompanyId(), 10)
                                || RPAD(' ', 19)
                                || RPAD(' ',  6)
                                || RPAD(_bank.bankaccnt_routing, 8)
                                || RPAD(TO_CHAR(_batchcount, '0000000SG'), 7),
                                94);
        RETURN NEXT _row;
      END IF;

      _batchhash     := 0;
      _batchcr       := 0;
      _batchdb       := 0;
      _batchdate     := _check.checkhead_checkdate;
      _entrycount    := 0;
      _rowcount      := _rowcount + 1;
      _batchcount    := _batchcount + 1;
      _row.achline_checkhead_id := NULL;
      _row.achline_batch := _filenum;
      _row.achline_type := 'BATCHHEADER';

      -- effective entry date = 1 or 2 banking days after the banking day
      -- of processing (the following accounts for weekends but not holidays)

      _row.achline_value := RPAD('5'
                              || _serviceclass
                              || RPAD(fetchMetricText('ACHCompanyName'), 16)
                              || RPAD('', 20)   -- TODO: find a use
                              || RPAD(formatAchCompanyId(), 10)
                              || _sec
                              || RPAD('xTuple ERP', 10)
                              || TO_CHAR(_check.checkhead_checkdate, 'YYMMDD')
                              || TO_CHAR(CURRENT_DATE +
                                         COALESCE(_bank.bankaccnt_ach_leadtime,1) +
                                         CASE WHEN EXTRACT(DOW FROM CURRENT_DATE) = 5 THEN 2
                                              WHEN EXTRACT(DOW FROM CURRENT_DATE) = 6 THEN 1
                                              ELSE 0 END,
                                         'YYMMDD')
                              || RPAD('', 3)
                              || '1'
                              || RPAD(_bank.bankaccnt_routing, 8)
                              || RPAD(TO_CHAR(_batchcount, '0000000SG'), 7),
                              94);
      RETURN NEXT _row;
    END IF;

    _row.achline_checkhead_id := _check.checkhead_id;
    _row.achline_batch        := _filenum;
    _row.achline_type         := _sec;

    IF (_sec = 'CCD' OR _sec = 'PPD') THEN
      _rowcount      := _rowcount + 1;
      _entrycount    := _entrycount + 1;
      _totalentrycnt := _totalentrycnt + 1;
      _batchhash     := _batchhash + CAST(SUBSTRING(_bank.bankaccnt_routing FOR 8) AS INTEGER);
      _totalhash     := _totalhash + CAST(SUBSTRING(_bank.bankaccnt_routing FOR 8) AS INTEGER);
      _batchdb       := _batchdb + _check.checkhead_amount;
      _totaldb       := _totaldb + _check.checkhead_amount;

      _row.achline_value := RPAD('6'
                              || _transactionprefix || '7'              -- debit
                              || RPAD(_bank.bankaccnt_routing,      9)  -- 2 fields
                              || RPAD(_bank.bankaccnt_accntnumber, 17)
                              || RPAD(TO_CHAR(_check.checkhead_amount,
                                              '00000000V99SG'), 10)
                              || RPAD(fetchMetricText('ACHCompanyId'),   15)
                              || RPAD(fetchMetricText('ACHCompanyName'), 22)
                              || RPAD(TO_CHAR(_check.checkhead_id % 100, '00SG'),
                                      2)        -- last 2 digits of checkhead_id
                              || '0'
                              || RPAD(_bank.bankaccnt_routing, 9)  -- split field
                              || RPAD(TO_CHAR(_entrycount, '000000SG'), 15-9),
                              94);
      RETURN NEXT _row;

      _rowcount      := _rowcount + 1;
      _entrycount    := _entrycount + 1;
      _totalentrycnt := _totalentrycnt + 1;
      _batchhash     := _batchhash + CAST(SUBSTRING(formatbytea(_check.vend_ach_routingnumber) FOR 8) AS INTEGER);
      _totalhash     := _totalhash + CAST(SUBSTRING(formatbytea(_check.vend_ach_routingnumber) FOR 8) AS INTEGER);
      _totalcr       := _totalcr + _check.checkhead_amount;
      _batchcr       := _batchcr + _check.checkhead_amount;
      _row.achline_value := RPAD('6'
                              || _transactionprefix || '2'              -- credit
                              || RPAD(formatbytea(_check.vend_ach_routingnumber), 9)   -- 2 fields
                              || RPAD(formatbytea(_check.vend_ach_accntnumber), 17)
                              || RPAD(TO_CHAR(_check.checkhead_amount,
                                              '00000000V99SG'), 10)
                              || RPAD(_ccdnumber, 15)
                              || RPAD(_ccdname,   22)
                              || RPAD(TO_CHAR(_check.checkhead_id % 100, '00SG'),
                                      2)        -- last 2 digits of checkhead_id
                              || '0'
                              || RPAD(_bank.bankaccnt_routing, 9)  -- split field
                              || RPAD(TO_CHAR(_entrycount, '000000SG'), 15-9),
                              94);
      RETURN NEXT _row;

    ELSE
      RAISE EXCEPTION 'Cannot write % check % to an ACH file because % is not a supported SEC code.',
                    _bank.bankaccnt_name, _check.checkhead_number, _sec;
    END IF;

    UPDATE checkhead
    SET checkhead_ach_batch=_filenum,
        checkhead_number=_check.checkhead_number
    WHERE (checkhead_id=_check.checkhead_id);

  END LOOP;

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Cannot write an ACH file for % because there are no checks pending in USD for ACH-enabled Vendors.',
                    _bank.bankaccnt_name;
  END IF;

  -- place a final batch control record
  IF (_batchcount > 0) THEN
    _rowcount := _rowcount + 1;
    _row.achline_checkhead_id := NULL;
    _row.achline_batch := _filenum;
    _row.achline_type := 'BATCHCONTROL';
    -- keep in sync with the other batchcontrol record format above
    _row.achline_value := RPAD('8'
                            || _serviceclass
                            || RPAD(TO_CHAR(_entrycount, '000000SG'), 6)
                            || RPAD(TO_CHAR(_batchhash % 10000000000,
                                            '0000000000SG'), 10)
                            || RPAD(TO_CHAR(_batchdb, '0000000000V99SG'), 12)
                            || RPAD(TO_CHAR(_batchcr, '0000000000V99SG'), 12)
                            || RPAD(formatAchCompanyId(), 10)
                            || RPAD(' ', 19)
                            || RPAD(' ',  6)
                            || RPAD(_bank.bankaccnt_routing, 8)
                            || RPAD(TO_CHAR(_batchcount, '0000000SG'), 7),
                            94);
    RETURN NEXT _row;
  END IF;

  -- and end with a file control record
  _rowcount := _rowcount + 1;
  _row.achline_checkhead_id := NULL;
  _row.achline_batch := _filenum;
  _row.achline_type := 'FILECONTROL';
  _row.achline_value := RPAD('9'
                          || RPAD(TO_CHAR(_batchcount,    '000000SG'),   6)
                          || RPAD(TO_CHAR(_rowcount,      '000000SG'),   6)
                          || RPAD(TO_CHAR(_totalentrycnt, '00000000SG'), 8)
                          || RPAD(TO_CHAR(_totalhash % 10000000000,
                                          '0000000000SG'), 10)
                          || RPAD(TO_CHAR(_totaldb, '0000000000V99SG'), 12)
                          || RPAD(TO_CHAR(_totalcr, '0000000000V99SG'), 12)
                          || RPAD('', 39),
                          94);

  RETURN NEXT _row;

  -- file must be a multiple of 10 lines long
  _row.achline_checkhead_id := NULL;
  _row.achline_batch := _filenum;
  _row.achline_type := 'BLOCKFILL';
  WHILE (_rowcount % 10 > 0) LOOP
    _rowcount := _rowcount + 1;
    _row.achline_value := RPAD('99999999999999999999'
                            || '99999999999999999999'
                            || '99999999999999999999'
                            || '99999999999999999999'
                            || '99999999999999999999', 94);
    RETURN NEXT _row;
  END LOOP;

  UPDATE bankaccnt
  SET bankaccnt_ach_lastdate=CURRENT_DATE,
      bankaccnt_ach_lastfileid=_bank.bankaccnt_ach_lastfileid
  WHERE (bankaccnt_id=_bank.bankaccnt_id);

  RETURN;

END;
$_$;


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

--
-- TOC entry 1586 (class 1255 OID 146566164)
-- Dependencies: 4536 8
-- Name: formatachcompanyid(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatachcompanyid() RETURNS text
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RETURN CASE WHEN fetchMetricText('ACHCompanyIdType') = 'D' THEN '3'
              WHEN fetchMetricText('ACHCompanyIdType') = 'E' THEN '1'
              WHEN fetchMetricText('ACHCompanyIdType') = 'O' THEN '9'
         END ||
         CASE WHEN fetchMetricText('ACHCompanyIdType') = 'D' OR
                   fetchMetricText('ACHCompanyIdType') = 'E' THEN
                 REPLACE(fetchMetricText('ACHCompanyId'), '-', '')
              ELSE fetchMetricText('ACHCompanyId')
         END;
END;
$$;


ALTER FUNCTION public.formatachcompanyid() OWNER TO admin;

--
-- TOC entry 1569 (class 1255 OID 146566168)
-- Dependencies: 4536 8
-- Name: formataddr(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  -- US conventions
  SELECT formatAddr(addr_line1, addr_line2, addr_line3,
                    addr_city || ', ' || addr_state || ' ' || addr_postalcode,
                    addr_country) INTO _return
  FROM addr
  WHERE (addr_id=pAddrId);

  RETURN _return;
END;
$_$;


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

--
-- TOC entry 1587 (class 1255 OID 146566165)
-- Dependencies: 4536 8
-- Name: formataddr(text, text, text, text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formataddr(text, text, text, text, integer) RETURNS text
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  f_addr1 ALIAS FOR $1;
  f_addr2 ALIAS FOR $2;
  f_addr3 ALIAS FOR $3;
  csz     ALIAS FOR $4;
  line    ALIAS FOR $5;

BEGIN
  RETURN formatAddr(f_addr1, f_addr2, f_addr3, csz, '', line);
END;
$_$;


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

--
-- TOC entry 1589 (class 1255 OID 146566167)
-- Dependencies: 4536 8
-- Name: formataddr(text, text, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formataddr(text, text, text, text, text) RETURNS text
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  f_addr1 ALIAS FOR $1;
  f_addr2 ALIAS FOR $2;
  f_addr3 ALIAS FOR $3;
  csz     ALIAS FOR $4;
  country ALIAS FOR $5;
  addr TEXT:='';

BEGIN

  IF (LENGTH(TRIM(both from f_addr1)) > 0) THEN
    addr:=f_addr1;
  END IF;

  IF (LENGTH(TRIM(both from f_addr2)) > 0)  THEN
        IF (LENGTH(TRIM(both from addr)) > 0) THEN
                addr:=addr || E'\n';
        END IF;
    addr:=addr || f_addr2;
  END IF;

  IF (LENGTH(TRIM(both from f_addr3)) > 0)  THEN
        IF (LENGTH(TRIM(both from addr)) > 0) THEN
                addr:=addr || E'\n';
        END IF;
    addr:=addr || f_addr3;
  END IF;

  IF (LENGTH(TRIM(both from csz)) > 0)  THEN
        IF (LENGTH(TRIM(both from addr)) > 0) THEN
                addr:=addr || E'\n';
        END IF;
    addr:=addr || csz;
  END IF;

  IF (LENGTH(TRIM(both from country)) > 0)  THEN
        IF (LENGTH(TRIM(both from addr)) > 0) THEN
                addr:=addr || E'\n';
        END IF;
    addr:=addr || country;
  END IF;

  RETURN addr;

END;
$_$;


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

--
-- TOC entry 1588 (class 1255 OID 146566166)
-- Dependencies: 4536 8
-- Name: formataddr(text, text, text, text, text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formataddr(text, text, text, text, text, integer) RETURNS text
    LANGUAGE plpgsql
    AS $_$ 
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  f_addr1 ALIAS FOR $1;
  f_addr2 ALIAS FOR $2;
  f_addr3 ALIAS FOR $3;
  csz     ALIAS FOR $4;
  country ALIAS FOR $5;
  line    ALIAS FOR $6;

  i int:=0;

BEGIN

  IF (LENGTH(TRIM(both from f_addr1)) > 0) THEN
    i:=i+1;
  END IF;

  IF (i=line) THEN
    RETURN f_addr1;
  END IF;

  IF (LENGTH(TRIM(both from f_addr2)) > 0)  THEN
    i:=i+1;
  END IF;

  IF (i=line) THEN
    RETURN f_addr2;
  END IF;

  IF (LENGTH(TRIM(both from f_addr3)) > 0) THEN
    i:=i+1;
  END IF;

  IF (i=line) THEN
    RETURN f_addr3;
  END IF;

  IF (LENGTH(TRIM(both from csz)) > 0) THEN
    i:=i+1;
  END IF;

  IF (i=line) THEN
    RETURN csz;
  END IF;

  IF (LENGTH(TRIM(both from country)) > 0) THEN
    i:=i+1;
  END IF;

  IF (i=line) THEN
    RETURN country;
  END IF;

  RETURN ' ';

END;
$_$;


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

--
-- TOC entry 1578 (class 1255 OID 146566169)
-- Dependencies: 4536 8
-- Name: formatboolyn(boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatboolyn(boolean) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE pBool ALIAS FOR $1;
BEGIN
  IF (pBool) THEN
    RETURN 'Yes';
  ELSE
    RETURN 'No';
  END IF;
END;
$_$;


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

--
-- TOC entry 1591 (class 1255 OID 146566170)
-- Dependencies: 4536 8
-- Name: formatbooseq(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  IF (fetchMetricBool('Routings')) THEN
    SELECT booitem_seqnumber::text INTO _result
    FROM xtmfg.booitem(pItemid)
    WHERE (booitem_seq_id=pBooitemSeqId);

    RETURN _result;
  ELSE
    RETURN NULL;
  END IF;

END;
$_$;


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

--
-- TOC entry 1592 (class 1255 OID 146566171)
-- Dependencies: 4536 8
-- Name: formatbytea(bytea); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  output_field := pField;

  RETURN output_field;

END;
$_$;


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

--
-- TOC entry 1593 (class 1255 OID 146566172)
-- Dependencies: 4536 8
-- Name: formatccdashes(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF (pCCardType = 'A')  THEN
    _returnCard := pCCard;
    RETURN _returnCard;
  END IF;

  card_length := length(pCcard);

  if (card_length = 16) THEN
    _returnCard := substr(pCCard, 1, 4) || '-' || substr(pCCard, 5, 4) || '-' || substr(pCCard, 9, 4) || '-' || substr(pCCard, 13, 4);
  ELSE
    _returnCard := substr(pCCard, 1, 4) || '-' || substr(pCCard, 5, 4) || '-' || substr(pCCard, 9, 4) || '-' || substr(pCCard, 13, 1);
  END IF;

  RETURN _returnCard;

END;
$_$;


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

--
-- TOC entry 1594 (class 1255 OID 146566173)
-- Dependencies: 4536 8
-- Name: formatccnumber(text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  card_length := length(pCcardnum);

  IF (card_length = 13) THEN
    output_cardnum := '*********' || substr(pCcardnum, 10, 4);  
  END IF;

  IF (card_length = 14) THEN
    output_cardnum := '**********' || substr(pCcardnum, 11, 4);  
  END IF;

  IF (card_length = 15) THEN
    output_cardnum := '***********' || substr(pCcardnum, 12, 4);  
  END IF;

  IF (card_length = 16) THEN
    output_cardnum := '************' || substr(pCcardnum, 13, 4);  
  END IF;

  RETURN output_cardnum;

END;
$_$;


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

--
-- TOC entry 1595 (class 1255 OID 146566174)
-- Dependencies: 4536 8
-- Name: formatccnumber(bytea); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  card_length := length(pCcardnum);

  IF (card_length = 13) THEN
    output_cardnum := '*********' || substr(pCcardnum, 10, 4);  
  END IF;

  IF (card_length = 14) THEN
    output_cardnum := '**********' || substr(pCcardnum, 11, 4);  
  END IF;

  IF (card_length = 15) THEN
    output_cardnum := '***********' || substr(pCcardnum, 12, 4);  
  END IF;

  IF (card_length = 16) THEN
    output_cardnum := '************' || substr(pCcardnum, 13, 4);  
  END IF;

  RETURN output_cardnum;

END;
$_$;


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

--
-- TOC entry 1596 (class 1255 OID 146566175)
-- Dependencies: 4536 8
-- Name: formatcntctname(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT cntct_honorific, cntct_first_name, cntct_middle, 
    cntct_last_name, cntct_suffix INTO _r
  FROM cntct
  WHERE (cntct_id=pCntctId);

  GET DIAGNOSTICS _rows = ROW_COUNT;
  IF (_rows = 0) THEN
    RETURN '';
  END IF;

  RETURN formatCntctName(_r.cntct_honorific, _r.cntct_first_name, _r.cntct_middle, _r.cntct_last_name, _r.cntct_suffix);

END;
$_$;


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

--
-- TOC entry 1597 (class 1255 OID 146566176)
-- Dependencies: 4536 8
-- Name: formatcntctname(text, text, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatcntctname(text, text, text, text, text) RETURNS text
    LANGUAGE plpgsql
    AS $_$ 
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pHonorific ALIAS FOR $1;
  pFirstName ALIAS FOR $2;
  pMiddle ALIAS FOR $3;
  pLastName ALIAS FOR $4;
  pSuffix ALIAS FOR $5;
  _name TEXT := '';

BEGIN

  IF (LENGTH(TRIM(both from COALESCE(pHonorific,''))) > 0) THEN
    IF (POSITION('.' IN COALESCE(pHonorific, '')) > 0) THEN
      _name:= pHonorific;
    ELSE
      _name:= pHonorific || '.';
    END IF;
  END IF;

  IF (LENGTH(TRIM(both from COALESCE(pFirstName,''))) > 0)  THEN
        IF (LENGTH(TRIM(both from _name)) > 0) THEN
                _name:=_name || ' ';
        END IF;
    _name:=_name || pFirstName;
  END IF;

  IF (LENGTH(TRIM(both from COALESCE(pMiddle,''))) > 0)  THEN
        IF (LENGTH(TRIM(both from _name)) > 0) THEN
                _name:=_name || ' ';
        END IF;
    IF (POSITION('.' IN COALESCE(pHonorific, '')) > 0) THEN
      _name:=_name || pMiddle;
    ELSE
      _name:=_name || pMiddle || '.';
    END IF;
  END IF;

  IF (LENGTH(TRIM(both from COALESCE(pLastName,''))) > 0)  THEN
        IF (LENGTH(TRIM(both from _name)) > 0) THEN
                _name:=_name || ' ';
        END IF;
    _name:=_name || pLastName;
  END IF;

  IF (LENGTH(TRIM(both from COALESCE(pSuffix,''))) > 0)  THEN
        IF (LENGTH(TRIM(both from _name)) > 0) THEN
                _name:=_name || ' ';
        END IF;
    _name:=_name || pSuffix;
  END IF;

  RETURN _name;

END;
$_$;


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

--
-- TOC entry 1598 (class 1255 OID 146566177)
-- Dependencies: 4536 8
-- Name: formatcost(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatcost(numeric) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RETURN formatNumeric($1, 'cost');
END;$_$;


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

--
-- TOC entry 1599 (class 1255 OID 146566178)
-- Dependencies: 4536 8
-- Name: formatcounttagbarcode(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  SELECT ( E'\138CTXX' ||
           LTRIM(TO_CHAR(LENGTH(invcnt_tagnumber), '00')) || invcnt_tagnumber ) INTO _barcode
  FROM invcnt
  WHERE (invcnt_id=pCnttagid);

  RETURN _barcode;

END;
$_$;


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

--
-- TOC entry 1600 (class 1255 OID 146566179)
-- Dependencies: 4536 8
-- Name: formatcreditmemonumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  RETURN ( SELECT COALESCE(cmhead_number::TEXT, '')
           FROM cmhead
           WHERE (cmhead_id=pCmheadid) );
END;
$_$;


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

--
-- TOC entry 1601 (class 1255 OID 146566180)
-- Dependencies: 8
-- Name: formatdate(date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatdate(date) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT TO_CHAR($1, COALESCE((SELECT locale_dateformat
                             FROM locale, usr
                             WHERE ((usr_locale_id=locale_id)
                              AND (usr_username=getEffectiveXtUser())) ),
                            'yyyy-mm-dd') ) AS result
$_$;


ALTER FUNCTION public.formatdate(date) OWNER TO admin;

--
-- TOC entry 1603 (class 1255 OID 146566182)
-- Dependencies: 8
-- Name: formatdate(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatdate(timestamp with time zone) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT TO_CHAR($1, COALESCE((SELECT locale_dateformat
                             FROM locale, usr
                             WHERE ((usr_locale_id=locale_id)
                              AND (usr_username=getEffectiveXtUser())) ),
                            'yyyy-mm-dd' )) AS result
$_$;


ALTER FUNCTION public.formatdate(timestamp with time zone) OWNER TO admin;

--
-- TOC entry 1602 (class 1255 OID 146566181)
-- Dependencies: 4536 8
-- Name: formatdate(date, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF ( (pDate = startOfTime()) OR
       (pDate = endOfTime()) OR
       (pDate IS NULL) ) THEN
    RETURN pString;
  ELSE
    RETURN formatDate(pDate);
  END IF;

END;
$_$;


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

--
-- TOC entry 1604 (class 1255 OID 146566183)
-- Dependencies: 8
-- Name: formatdatetime(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatdatetime(timestamp with time zone) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT TO_CHAR($1, COALESCE((SELECT locale_timestampformat
                             FROM locale, usr
                             WHERE ((usr_locale_id=locale_id)
                              AND (usr_username=getEffectiveXtUser())) ),
                            'yyyy-mm-dd HH24:MI:SS')) AS result
$_$;


ALTER FUNCTION public.formatdatetime(timestamp with time zone) OWNER TO admin;

--
-- TOC entry 1605 (class 1255 OID 146566184)
-- Dependencies: 8
-- Name: formatdatetime(timestamp without time zone); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatdatetime(timestamp without time zone) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT TO_CHAR($1, COALESCE((SELECT locale_timestampformat
                             FROM locale, usr
                             WHERE ((usr_locale_id=locale_id)
                              AND (usr_username=getEffectiveXtUser())) ),
                            'yyyy-mm-dd HH24:MI:SS')) AS result
$_$;


ALTER FUNCTION public.formatdatetime(timestamp without time zone) OWNER TO admin;

--
-- TOC entry 1606 (class 1255 OID 146566185)
-- Dependencies: 4536 8
-- Name: formatextprice(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatextprice(numeric) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RETURN formatNumeric($1, 'extprice');
END;$_$;


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

--
-- TOC entry 1607 (class 1255 OID 146566186)
-- Dependencies: 4536 8
-- Name: formatflitemdescrip(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  SELECT flitem_accnt_id, flitem_company, flitem_profit, flitem_number,
        flitem_sub, flitem_type, flitem_subaccnttype_code,
        accnt_id, accnt_descrip INTO _x
  FROM flitem LEFT OUTER JOIN accnt
        ON flitem_accnt_id=accnt_id
  WHERE flitem_id=pFlitemId;

  IF _x.flitem_accnt_id > -1 THEN

    SELECT (formatGLAccount(_x.accnt_id) || '-' || _x.accnt_descrip) INTO _descrip;

  ELSE

    _descrip:='';

    IF _x.flitem_type = 'A' THEN
      _descrip:='Type=' || 'Asset';
      ELSE IF _x.flitem_type='L' THEN
        _descrip:='Type=' || 'Liability';
        ELSE IF _x.flitem_type='R' THEN
          _descrip:='Type=' || 'Revenue';
          ELSE IF _x.flitem_type='E' THEN
            _descrip:='Type=' || 'Expense';
            ELSE IF _x.flitem_type='Q' THEN
              _descrip:='Type=' || 'Equity';
            END IF;
          END IF;
        END IF;
      END IF;
    END IF;

    IF _x.flitem_subaccnttype_code <> 'All' THEN
      IF (LENGTH(TRIM(both from _descrip)) > 0) THEN
        _descrip:=_descrip || ', ';
      END IF;
      _descrip:=_descrip || 'Sub Accnt Type=' || _x.flitem_subaccnttype_code;
    END IF;

    IF _x.flitem_company <> 'All' THEN
      IF (LENGTH(TRIM(both from _descrip)) > 0) THEN
        _descrip:=_descrip || ', ';
      END IF;
      _descrip:=_descrip || 'Company=' || _x.flitem_company;
    END IF;

    IF _x.flitem_profit <> 'All' THEN
      IF (LENGTH(TRIM(both from _descrip)) > 0) THEN
        _descrip:=_descrip || ', ';
      END IF;
      _descrip:=_descrip || 'Profit=' || _x.flitem_profit;
    END IF;

    IF _x.flitem_number <> 'All' THEN
      IF (LENGTH(TRIM(both from _descrip)) > 0) THEN
        _descrip:=_descrip || ', ';
      END IF;
      _descrip:=_descrip || 'Number=' || _x.flitem_number;
    END IF;

    IF _x.flitem_sub <> 'All' THEN
      IF (LENGTH(TRIM(both from _descrip)) > 0) THEN
        _descrip:=_descrip || ', ';
      END IF;
      _descrip:=_descrip || 'Sub Accnt=' || _x.flitem_sub;
    END IF;
  END IF;

  RETURN _descrip;

END;
$_$;


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

--
-- TOC entry 1608 (class 1255 OID 146566187)
-- Dependencies: 4536 8
-- Name: formatglaccount(text, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatglaccount(text, text, text, text) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCompany ALIAS FOR $1;
  pProfit ALIAS FOR $2;
  pNumber ALIAS FOR $3;
  pSub    ALIAS FOR $4;
  _number TEXT := '';

BEGIN

  IF ( ( SELECT metric_value::INTEGER
         FROM metric
         WHERE (metric_name='GLCompanySize') ) > 0 ) THEN
    _number := pCompany || '-';
  END IF;

  IF ( ( SELECT metric_value::INTEGER
         FROM metric
         WHERE (metric_name='GLProfitSize') ) > 0 ) THEN
    _number := _number || pProfit || '-';
  END IF;

  _number := _number || pNumber;

  IF ( ( SELECT metric_value::INTEGER
         FROM metric
         WHERE (metric_name='GLSubaccountSize') ) > 0 ) THEN
    _number := _number || '-' || pSub;
  END IF;

  RETURN _number;

END;
$_$;


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

--
-- TOC entry 1609 (class 1255 OID 146566188)
-- Dependencies: 4536 8
-- Name: formatglaccountlong(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT (formatGLAccount(accnt_id) || '-' || accnt_descrip) INTO _result
  FROM accnt
  WHERE (accnt_id=pAccntid);

  RETURN _result;

END;
$_$;


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

--
-- TOC entry 1610 (class 1255 OID 146566189)
-- Dependencies: 4536 8
-- Name: formatindent(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  _result := '';
  _i := 0;

  WHILE _i < pIndent LOOP
    _result := _result || '  ';
    _i := _i + 1;
  END LOOP;

  _result := _result || pText;
  RETURN _result;
END;
$_$;


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

--
-- TOC entry 1611 (class 1255 OID 146566190)
-- Dependencies: 8
-- Name: formatinterval(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatinterval(numeric) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT TO_CHAR(('@ ' || trunc($1) || ' min ' ||
                             ($1 - trunc($1)) * 60 || ' sec')::INTERVAL,
                 ( SELECT locale_intervalformat
                       FROM locale, usr
                       WHERE ((usr_locale_id=locale_id)
                         AND  (usr_username=getEffectiveXtUser())) ) ) AS result
$_$;


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

--
-- TOC entry 1612 (class 1255 OID 146566191)
-- Dependencies: 8
-- Name: formatinterval(interval); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatinterval(interval) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT TO_CHAR($1, ( SELECT locale_intervalformat
                       FROM locale, usr
                       WHERE ((usr_locale_id=locale_id)
                         AND  (usr_username=getEffectiveXtUser())) ) ) AS result
$_$;


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

--
-- TOC entry 1613 (class 1255 OID 146566192)
-- Dependencies: 4536 8
-- Name: formatinvcnumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  RETURN ( SELECT COALESCE(cobmisc_invcnumber::TEXT, '')
           FROM cobmisc
           WHERE (cobmisc_id=pCobmiscid) );
END;
$_$;


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

--
-- TOC entry 1614 (class 1255 OID 146566193)
-- Dependencies: 4536 8
-- Name: formatitemsitebarcode(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  SELECT ( E'\138ISXX' ||
           LTRIM(TO_CHAR(LENGTH(item_number), '00')) || LENGTH(warehous_code)::TEXT ||
           item_number || warehous_code ) INTO _barcode
  FROM itemsite, item, whsinfo
  WHERE ( (itemsite_item_id=item_id)
   AND (itemsite_warehous_id=warehous_id)
   AND (itemsite_id=pItemsiteid) );

  RETURN _barcode;

END;
$_$;


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

--
-- TOC entry 1615 (class 1255 OID 146566194)
-- Dependencies: 4536 8
-- Name: formatlocationbarcode(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  SELECT ( E'\138LOXX' ||
           LENGTH(warehous_code)::TEXT || LTRIM(TO_CHAR(LENGTH(location_name::TEXT), '00')) ||
           warehous_code || location_name ) INTO _barcode
  FROM location, whsinfo
  WHERE ( (location_warehous_id=warehous_id)
   AND (location_id=pLocationid) );

  RETURN _barcode;

END;
$_$;


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

--
-- TOC entry 1616 (class 1255 OID 146566195)
-- Dependencies: 4536 8
-- Name: formatlocationcontentsbarcode(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  SELECT ( E'\138LOCN' ||
           LENGTH(warehous_code)::TEXT || LTRIM(TO_CHAR(LENGTH(location_name), '00')) ||
           warehous_code || location_name ) INTO _barcode
  FROM location, whsinfo
  WHERE ( (location_warehous_id=warehous_id)
   AND (location_id=pLocationid) );

  RETURN _barcode;

END;
$_$;


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

--
-- TOC entry 1617 (class 1255 OID 146566196)
-- Dependencies: 4536 8
-- Name: formatlocationissuebarcode(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  SELECT ( E'\138LOIS' ||
           LENGTH(warehous_code)::TEXT || LTRIM(TO_CHAR(LENGTH(location_name), '00')) ||
           warehous_code || location_name ) INTO _barcode
  FROM location, whsinfo
  WHERE ( (location_warehous_id=warehous_id)
   AND (location_id=pLocationid) );

  RETURN _barcode;

END;
$_$;


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

--
-- TOC entry 1618 (class 1255 OID 146566197)
-- Dependencies: 4536 8
-- Name: formatlotserialnumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  --See if lot serial control turned on (Postbooks will not ever have this)
  IF (fetchmetricbool('LotSerialControl')) THEN
    SELECT ls_number INTO _lotserial
    FROM ls
    WHERE (ls_id=pLotSerialId);
  END IF;

  RETURN COALESCE(_lotserial,'');

END;
$_$;


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

--
-- TOC entry 1619 (class 1255 OID 146566198)
-- Dependencies: 4536 8
-- Name: formatmoney(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatmoney(numeric) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RETURN formatNumeric($1, 'curr');
END;$_$;


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

--
-- TOC entry 1620 (class 1255 OID 146566199)
-- Dependencies: 4536 8
-- Name: formatnumeric(numeric, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatnumeric(numeric, text) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _value        NUMERIC := $1;
  _type         TEXT    := LOWER(COALESCE($2, 'curr'));
  _abs          NUMERIC;
  _magnitudecnt NUMERIC(1000);
  _wholefmt     TEXT    := '0';
  _scale        INTEGER;
  _neg          TEXT;
  _decimal      TEXT;
  _group        TEXT;
  _string       TEXT;
  _debug        BOOL := false;
  _r            RECORD;

BEGIN
  -- If the value passed in is NULL then we want to pass back an empty string
  IF(_value IS NULL) THEN
    RETURN '';
  END IF;

  SELECT * INTO _r
  FROM locale
  WHERE (locale_id=getUsrLocaleId());

  _decimal := COALESCE(SUBSTRING(_r.locale_qtyformat FROM 1 FOR 1), '.');
  _neg     := COALESCE(SUBSTRING(_r.locale_qtyformat FROM 2 FOR 1), '-');
  _group   := COALESCE(SUBSTRING(_r.locale_qtyformat FROM 3 FOR 1), ',');

  _scale   := CASE WHEN _type = 'cost'       THEN _r.locale_cost_scale
                   WHEN _type = 'extprice'   THEN _r.locale_extprice_scale
                   WHEN _type = 'percent'    THEN _r.locale_percent_scale
                   WHEN _type = 'purchprice' THEN _r.locale_purchprice_scale
                   WHEN _type = 'qty'        THEN _r.locale_qty_scale
                   WHEN _type = 'qtyper'     THEN _r.locale_qtyper_scale
                   WHEN _type = 'salesprice' THEN _r.locale_salesprice_scale
                   WHEN _type = 'uomratio'   THEN _r.locale_uomratio_scale
                   WHEN _type = 'weight'     THEN _r.locale_weight_scale
                   WHEN SUBSTRING(_type FOR 4) = 'curr' THEN _r.locale_curr_scale
                   ELSE 2
              END;

  _value        := round(_value, _scale);
  _abs          := ABS(_value);
  _magnitudecnt := TRUNC(_abs / 10);

  IF (_debug) THEN
    RAISE NOTICE '_value % _abs % _scale % _neg % _decimal % _group % ',
                 _value, _abs, _scale, _decimal, _group, _scale;
  END IF;

  IF (_value < 0) THEN
    _string := _neg;
  ELSE
    _string := '';
  END IF;

  WHILE (_magnitudecnt >= 1) LOOP
    _magnitudecnt := TRUNC(_magnitudecnt / 10);
    IF (LENGTH(_wholefmt) % 3 = 0) THEN
      _wholefmt := '"' || _group || '"' || _wholefmt;
    END IF;
    _wholefmt := '9' || _wholefmt;
  END LOOP;

  IF (_scale > 0) THEN
    _abs := (_abs * (10 ^ _scale));
    _abs := TRUNC(_abs);
    _wholefmt := _wholefmt || '"' || _decimal || '"' || REPEAT('0', _scale);
  END IF;

  _wholefmt := 'FM' || _wholefmt;
  _string := _string || to_char(_abs, _wholefmt);

  RETURN _string;
END;$_$;


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

--
-- TOC entry 1621 (class 1255 OID 146566200)
-- Dependencies: 4536 8
-- Name: formatperiodname(integer, character); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

--...for Month

   IF (pInterval='M') THEN
        SELECT
          (CASE
                      WHEN period_name='' THEN
                        formatdate(period_start) || '-' || formatdate(period_end)
                      ELSE period_name
          END) INTO _result
        FROM period
        WHERE (period_id=pPeriodId);

        RETURN _result;

--...for Quarter

        ELSE IF (pInterval='Q') THEN
                SELECT
                        ('Q' || period_quarter || '-' || EXTRACT(year from yearperiod_end)) INTO _result
                FROM period, yearperiod
                WHERE ((period_id=pPeriodId)
                AND (period_yearperiod_id=yearperiod_id));

                RETURN _result;
--...for Year
        ELSE
                SELECT
                        EXTRACT(year FROM yearperiod_end) INTO _result
                FROM period,yearperiod
                WHERE ((period_id=pPeriodId)
                AND (period_yearperiod_id=yearperiod_id));

                RETURN _result;
        END IF;
   END IF;

   RETURN 'Err';

END;
$_$;


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

--
-- TOC entry 1622 (class 1255 OID 146566201)
-- Dependencies: 4536 8
-- Name: formatplonumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT (TEXT(planord_number) || '-' || TEXT(planord_subnumber)) INTO _result
  FROM planord
  WHERE (planord_id=pPlanordid);

  RETURN _result;

END;
$_$;


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

--
-- TOC entry 1623 (class 1255 OID 146566202)
-- Dependencies: 8
-- Name: formatprcnt(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatprcnt(numeric) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT formatNumeric($1 * 100, 'percent')  AS result
$_$;


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

--
-- TOC entry 1624 (class 1255 OID 146566203)
-- Dependencies: 8
-- Name: formatprice(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatprice(numeric) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT formatNumeric($1, 'salesprice') AS result;
$_$;


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

--
-- TOC entry 1625 (class 1255 OID 146566204)
-- Dependencies: 4536 8
-- Name: formatpurchprice(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatpurchprice(numeric) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RETURN formatNumeric($1, 'purchprice');
END;$_$;


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

--
-- TOC entry 1626 (class 1255 OID 146566205)
-- Dependencies: 4536 8
-- Name: formatqty(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatqty(numeric) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RETURN formatNumeric($1, 'qty');
END;$_$;


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

--
-- TOC entry 1627 (class 1255 OID 146566206)
-- Dependencies: 4536 8
-- Name: formatqtyper(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatqtyper(numeric) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RETURN formatNumeric($1, 'qtyper');
END;$_$;


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

--
-- TOC entry 1590 (class 1255 OID 146566207)
-- Dependencies: 8
-- Name: formatratio(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatratio(numeric) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT formatNumeric($1, 'uomratio');
$_$;


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

--
-- TOC entry 1628 (class 1255 OID 146566208)
-- Dependencies: 4536 8
-- Name: formatrevnumber(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  --See if revision control turned on (Postbooks will not ever have this)
  IF (fetchmetricbool('RevControl')) THEN
    SELECT rev_number INTO _revision
    FROM rev
    WHERE ((rev_target_type=pRevType)
    AND (rev_id=pRevId));
  END IF;

  RETURN COALESCE(_revision,'');

END;
$_$;


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

--
-- TOC entry 1629 (class 1255 OID 146566209)
-- Dependencies: 4536 8
-- Name: formatsalesprice(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatsalesprice(numeric) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RETURN formatNumeric($1, 'salesprice');
END;$_$;


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

--
-- TOC entry 1630 (class 1255 OID 146566210)
-- Dependencies: 8
-- Name: formatscrap(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatscrap(numeric) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT formatNumeric(($1 * 100), 'percent') AS result
$_$;


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

--
-- TOC entry 1631 (class 1255 OID 146566211)
-- Dependencies: 4536 8
-- Name: formatshipmentnumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatshipmentnumber(integer) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pshipheadid    ALIAS FOR $1;
BEGIN
  RETURN ( SELECT COALESCE(shiphead_number::TEXT, '')
           FROM shiphead
           WHERE (shiphead_id=pshipheadid) );
END;
$_$;


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

--
-- TOC entry 1632 (class 1255 OID 146566212)
-- Dependencies: 4536 8
-- Name: formatsobarcode(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  SELECT ( E'\138SOXX' ||
           LENGTH(TEXT(cohead_number)) || TEXT(cohead_number) ) INTO _barcode
  FROM cohead
  WHERE (cohead_id=pSoheadid);

  RETURN _barcode;

END;
$_$;


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

--
-- TOC entry 1633 (class 1255 OID 146566213)
-- Dependencies: 4536 8
-- Name: formatsoitembarcode(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  SELECT ( E'\138SOLI' ||
           LENGTH(TEXT(cohead_number)) || LENGTH(formatsolinenumber(coitem_id)) ||
           TEXT(cohead_number) || formatsolinenumber(coitem_id) ) INTO _barcode
  FROM cohead, coitem
  WHERE ( (coitem_cohead_id=cohead_id)
   AND (coitem_id=pSoitemid) );

  RETURN _barcode;

END;
$_$;


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

--
-- TOC entry 1634 (class 1255 OID 146566214)
-- Dependencies: 4536 8
-- Name: formatsoitemnumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatsoitemnumber(integer) RETURNS text
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  targetSoitemid ALIAS FOR $1;
BEGIN
  RETURN ( SELECT (cohead_number::TEXT || '-' || formatsolinenumber(coitem_id))
           FROM cohead, coitem
           WHERE ((coitem_cohead_id=cohead_id)
            AND (coitem_id=targetSoitemid)) );
END;
$_$;


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

--
-- TOC entry 1635 (class 1255 OID 146566215)
-- Dependencies: 8
-- Name: formatsonumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatsonumber(integer) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT COALESCE((SELECT (text(cohead_number) || '-' || formatSoLineNumber(coitem_id))
                   FROM coitem JOIN cohead ON (coitem_cohead_id=cohead_id)
                  WHERE (coitem_id=($1))),'DELETED');
$_$;


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

--
-- TOC entry 1636 (class 1255 OID 146566216)
-- Dependencies: 8
-- Name: formattime(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formattime(timestamp with time zone) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT TO_CHAR($1, ( SELECT locale_timeformat
                       FROM locale, usr
                       WHERE ((usr_locale_id=locale_id)
                        AND (usr_username=getEffectiveXtUser())) ) ) AS result
$_$;


ALTER FUNCTION public.formattime(timestamp with time zone) OWNER TO admin;

--
-- TOC entry 1637 (class 1255 OID 146566217)
-- Dependencies: 8
-- Name: formattime(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formattime(numeric) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT LTRIM(TO_CHAR(COALESCE($1, 0), '999999990.0'));
$_$;


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

--
-- TOC entry 1638 (class 1255 OID 146566218)
-- Dependencies: 4536 8
-- Name: formatuomratio(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatuomratio(numeric) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RETURN formatNumeric($1, 'uomratio');
END;$_$;


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

--
-- TOC entry 1639 (class 1255 OID 146566219)
-- Dependencies: 4536 8
-- Name: formatuserbarcode(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  SELECT formatUserBarcode(usr_username) INTO _barcode
    FROM usr
   WHERE(usr_id=pUserid);

  RETURN _barcode;

END;
$_$;


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

--
-- TOC entry 1640 (class 1255 OID 146566220)
-- Dependencies: 4536 8
-- Name: formatuserbarcode(text); Type: FUNCTION; Schema: public; Owner: admin
--

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

  _barcode := ( E'\138USER' || LENGTH(pUsername)::TEXT || pUsername );

  RETURN _barcode;

END;
$_$;


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

--
-- TOC entry 1641 (class 1255 OID 146566221)
-- Dependencies: 8
-- Name: formatweight(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatweight(numeric) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT formatNumeric($1, 'weight') AS result
$_$;


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

--
-- TOC entry 1642 (class 1255 OID 146566222)
-- Dependencies: 4536 8
-- Name: formatwobarcode(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  SELECT ( E'\138WOXX' ||
           LENGTH(wo_number::TEXT) || LENGTH(wo_subnumber::TEXT) ||
           wo_number::TEXT || wo_subnumber::TEXT ) INTO _barcode
  FROM wo
  WHERE (wo_id=pWoid);

  RETURN _barcode;

END;
$_$;


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

--
-- TOC entry 1643 (class 1255 OID 146566223)
-- Dependencies: 4536 8
-- Name: formatwonumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RETURN ( SELECT (wo_number::TEXT || '-' || wo_subnumber::TEXT)
           FROM wo
           WHERE (wo_id=pWoid) );

END;
$_$;


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

--
-- TOC entry 1644 (class 1255 OID 146566224)
-- Dependencies: 4536 8
-- Name: formatwooperseq(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF pWooperId = -1 THEN
    RETURN '';
  ELSE
    SELECT wooper_seqnumber INTO _result
    FROM xtmfg.wooper
    WHERE (wooper_id=pWooperId);
  END IF;

  RETURN _result;
END;
$_$;


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

--
-- TOC entry 1645 (class 1255 OID 146566225)
-- Dependencies: 4536 8
-- Name: forwardupdateaccount(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  SELECT trialbal_id, trialbal_dirty,
         CASE WHEN (trialbal_dirty) THEN 0
              ELSE 1
         END AS dirty_seq INTO _r
    FROM trialbal, period
   WHERE ((trialbal_period_id=period_id)
     AND  (trialbal_accnt_id=pAccntid))
   ORDER BY dirty_seq, period_start
   LIMIT 1;
  IF (FOUND) THEN
    IF (_r.trialbal_dirty) THEN
      RETURN forwardUpdateTrialBalance(_r.trialbal_id);
    ELSE
      RETURN _r.trialbal_id;
    END IF;
  ELSE
      _trialbalid := nextval('trialbal_trialbal_id_seq');
      
      INSERT INTO trialbal
      ( trialbal_id,
        trialbal_period_id, trialbal_accnt_id,
        trialbal_beginning, trialbal_ending,
        trialbal_debits, trialbal_credits, trialbal_dirty )
      SELECT
        _trialbalid,
        period_id, pAccntid,
        0, 0,
        0, 0, FALSE
      FROM period
      ORDER BY period_start LIMIT 1;

      RETURN forwardUpdateTrialBalance(_trialbalid);
  END IF;

  RETURN -1;
END;
$_$;


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

--
-- TOC entry 1646 (class 1255 OID 146566226)
-- Dependencies: 4536 8
-- Name: forwardupdateinvbalance(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION forwardupdateinvbalance(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
  pInvbalid ALIAS FOR $1;
  _p RECORD;
  _r RECORD;
  _qohEnding NUMERIC;
  _valueEnding NUMERIC;
  _nnEnding NUMERIC;
  _nnvalEnding NUMERIC;

BEGIN

  SELECT invbal_itemsite_id, 
         invbal_qoh_ending,
         invbal_value_ending,
         invbal_nn_ending,
         invbal_nnval_ending,
         period_end INTO _p
  FROM invbal
    JOIN period ON (invbal_period_id=period_id)
    JOIN itemsite ON (invbal_itemsite_id=itemsite_id)
  WHERE (invbal_id=pInvbalid);

  _qohEnding = _p.invbal_qoh_ending;
  _valueEnding = _p.invbal_value_ending;
  _nnEnding = _p.invbal_nn_ending;
  _nnvalEnding = _p.invbal_nnval_ending;

--  Find all of the subsequent periods and their inventory balance, if they exist
  FOR _r IN SELECT period_id, period_end,
                   invbal_id, 
                   invbal_qty_in, invbal_qty_out,
                   invbal_value_in, invbal_value_out,
                   invbal_nn_in, invbal_nn_out,
                   invbal_nnval_in, invbal_nnval_out
            FROM period 
              LEFT OUTER JOIN invbal
                 ON ( (invbal_period_id=period_id) AND (invbal_itemsite_id=_p.invbal_itemsite_id) )
            WHERE (period_start > _p.period_end)
            ORDER BY period_start LOOP

    IF (_r.invbal_id IS NULL) THEN

      INSERT INTO invbal
      ( invbal_period_id, invbal_itemsite_id,
        invbal_qoh_beginning, invbal_qoh_ending,
        invbal_qty_in, invbal_qty_out,
        invbal_value_beginning, invbal_value_ending,
        invbal_value_in, invbal_value_out, 
        invbal_nn_beginning, invbal_nn_ending,
        invbal_nn_in, invbal_nn_out,
        invbal_nnval_beginning, invbal_nnval_ending,
        invbal_nnval_in, invbal_nnval_out, 
        invbal_dirty )
      VALUES
      ( _r.period_id, _p.invbal_itemsite_id,
        _qohEnding, _qohEnding,
        0, 0, 
        _valueEnding, _valueEnding,
        0, 0,
        _nnEnding, _nnEnding,
        0, 0, 
        _nnvalEnding, _nnvalEnding,
        0, 0,
        FALSE );
    ELSE
      UPDATE invbal
      SET invbal_qoh_beginning = (_qohEnding),
          invbal_qoh_ending = (_qohEnding + _r.invbal_qty_in - _r.invbal_qty_out),
          invbal_value_beginning = (_valueEnding),
          invbal_value_ending = (_valueEnding + _r.invbal_value_in - _r.invbal_value_out),
          invbal_nn_beginning = (_nnEnding),
          invbal_nn_ending = (_nnEnding + _r.invbal_nn_in - _r.invbal_nn_out),
          invbal_nnval_beginning = (_nnvalEnding),
          invbal_nnval_ending = (_nnvalEnding + _r.invbal_nnval_in - _r.invbal_nnval_out),
          invbal_dirty = FALSE
      WHERE (invbal_id=_r.invbal_id);

      _qohEnding = (_qohEnding + _r.invbal_qty_in - _r.invbal_qty_out);
      _valueEnding = (_valueEnding + _r.invbal_value_in - _r.invbal_value_out);
      _nnEnding = (_nnEnding + _r.invbal_nn_in - _r.invbal_nn_out);
      _nnvalEnding = (_nnvalEnding + _r.invbal_nnval_in - _r.invbal_nnval_out);
    END IF;
  END LOOP;

  UPDATE invbal
  SET invbal_dirty = false
  WHERE (invbal_id=pInvbalid);

  RETURN pInvbalid;

END;
$_$;


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

--
-- TOC entry 1647 (class 1255 OID 146566227)
-- Dependencies: 4536 8
-- Name: forwardupdateitemsite(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION forwardupdateitemsite(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;
  _r RECORD;
  _invbalid INTEGER;

BEGIN
  SELECT invbal_id INTO _r
  FROM invbal
      JOIN period ON (invbal_period_id=period_id)
  WHERE (invbal_itemsite_id=pItemsiteid)
  ORDER BY period_start
  LIMIT 1;

  IF (FOUND) THEN
    RETURN forwardUpdateInvbalance(_r.invbal_id);
  ELSE
      _invbalid := nextval('invbal_invbal_id_seq');
      
      INSERT INTO invbal
      ( invbal_id,
        invbal_period_id, invbal_itemsite_id,
        invbal_qoh_beginning, invbal_qoh_ending,
        invbal_qty_in, invbal_qty_out,
        invbal_value_beginning, invbal_value_ending,
        invbal_value_in, invbal_value_out,
        invbal_nn_beginning, invbal_nn_ending,
        invbal_nn_in, invbal_nn_out,
        invbal_nnval_beginning, invbal_nnval_ending,
        invbal_nnval_in, invbal_nnval_out,
        invbal_dirty )
      SELECT
        _invbalid,
        period_id, pItemsiteid,
        0, 0, 0, 0, 0, 0, 0, 0,
        0, 0, 0, 0, 0, 0, 0, 0,
        false
      FROM period
      ORDER BY period_start LIMIT 1;

      RETURN forwardUpdateInvbalance(_invbalid);
  END IF;

  RETURN -1;
END;
$_$;


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

--
-- TOC entry 1648 (class 1255 OID 146566228)
-- Dependencies: 4536 8
-- Name: forwardupdatetrialbalance(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION forwardupdatetrialbalance(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
  pTrialbalid ALIAS FOR $1;
  _p RECORD;
  _r RECORD;
  _ending NUMERIC;
  _prevYear INTEGER;
  _currYear INTEGER;
  _prevYearClosed BOOLEAN;
  _currYearClosed BOOLEAN;
  _result INTEGER;

BEGIN

  SELECT trialbal_accnt_id, trialbal_ending,
         yearperiod_id, yearperiod_closed,
         period_end, accnt_type IN ('E', 'R') AS revexp INTO _p
  FROM trialbal, period, yearperiod, accnt
  WHERE ( (trialbal_period_id=period_id)
   AND (yearperiod_id=period_yearperiod_id)
   AND (trialbal_accnt_id=accnt_id)
   AND (trialbal_id=pTrialbalid) );

  _ending = _p.trialbal_ending;

  SELECT yearperiod_id, yearperiod_closed INTO _prevYear, _prevYearClosed
    FROM yearperiod
   WHERE (_p.period_end BETWEEN yearperiod_start AND yearperiod_end);
  IF (NOT FOUND) THEN
    _prevYear := -1;
    _prevYearClosed := false;
  END IF;

--  Find all of the subsequent periods and their trialbal, if they exist
  FOR _r IN SELECT period_id, period_end,
                   trialbal_id, trialbal_debits, trialbal_credits,
                   trialbal_yearend
            FROM period LEFT OUTER JOIN trialbal
                 ON ( (trialbal_period_id=period_id) AND (trialbal_accnt_id=_p.trialbal_accnt_id) )
            WHERE (period_start > _p.period_end)
            ORDER BY period_start LOOP

    SELECT yearperiod_id, yearperiod_closed INTO _currYear, _currYearClosed
      FROM yearperiod
     WHERE (_r.period_end BETWEEN yearperiod_start AND yearperiod_end);
    IF (NOT FOUND) THEN
      _currYear := -1;
      _currYearClosed := false;
    END IF;

    IF (_p.revexp AND _currYear != _prevYear) THEN
      _ending := 0;
      IF (_prevYearClosed) THEN
        SELECT updateRetainedEarnings(_prevYear) INTO _result;
        IF (_result < 0) THEN
          RETURN _result;
        END IF;
      END IF;
    END IF;

    _prevYear := _currYear;
    _prevYearClosed := _currYearClosed;

    IF (_r.trialbal_id IS NULL) THEN
      -- SELECT SUM(gltrans_amount) INTO _glAmount
       -- FROM gltrans
       -- WHERE ( (gltrans_date BETWEEN _r.period_start and _r.period_end )
         -- AND   (gltrans_accnt_id=_p.trialbal_accnt_id)
         -- AND   (gltrans_posted) );
        -- and change 2nd and 3rd VALUES line of INSERT to read
        --      _ending, _ending + _glAmount,
        --      noneg(0 - _glAmount), noneg(_glAmount), FALSE );

      INSERT INTO trialbal
      ( trialbal_period_id, trialbal_accnt_id,
        trialbal_beginning, trialbal_ending,
        trialbal_debits, trialbal_credits, trialbal_dirty )
      VALUES
      ( _r.period_id, _p.trialbal_accnt_id,
        _ending, _ending,
        0, 0, FALSE );
    ELSE
      UPDATE trialbal
      SET trialbal_beginning = (_ending + trialbal_yearend),
          trialbal_ending = (_ending + trialbal_yearend - _r.trialbal_debits + _r.trialbal_credits),
          trialbal_dirty = FALSE
      WHERE (trialbal_id=_r.trialbal_id);

      _ending = (_ending + _r.trialbal_yearend - _r.trialbal_debits + _r.trialbal_credits);
    END IF;
  END LOOP;

  UPDATE trialbal
  SET trialbal_dirty = FALSE
  WHERE (trialbal_id=pTrialbalid);

  RETURN pTrialbalid;

END;
$_$;


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

--
-- TOC entry 1649 (class 1255 OID 146566229)
-- Dependencies: 4536 8
-- Name: freezeaccountingperiod(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

--  Check to make use that the period is not already frozen
  IF ( ( SELECT period_freeze
         FROM period
         WHERE (period_id=pPeriodid) ) ) THEN
    RETURN -2;
  END IF;

--  Set the period_freeze flag
  UPDATE period
  SET period_freeze=TRUE
  WHERE (period_id=pPeriodid);

  RETURN pPeriodid;

END;
$_$;


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

--
-- TOC entry 1652 (class 1255 OID 146566230)
-- Dependencies: 2681 4536 8
-- Name: freightdetail(text, integer, integer, integer, date, text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION freightdetail(text, integer, integer, integer, date, text, integer) RETURNS SETOF freightdata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pOrderType ALIAS FOR $1;
  pOrderId ALIAS FOR $2;
  pCustId ALIAS FOR $3;
  pShiptoId ALIAS FOR $4;
  pOrderDate ALIAS FOR $5;
  pShipVia ALIAS FOR $6;
  pCurrId ALIAS FOR $7;

  _row freightData%ROWTYPE;
  _order RECORD;
  _weights RECORD;
  _includepkgweight BOOLEAN := FALSE;
  _qry TEXT;
  _debug BOOLEAN := FALSE;

BEGIN
  IF (_debug) THEN
    RAISE NOTICE 'pOrderType = %', pOrderType;
    RAISE NOTICE 'pOrderId = %', pOrderId;
    RAISE NOTICE 'pCustId = %', pCustId;
    RAISE NOTICE 'pShiptoId = %', pShiptoId;
    RAISE NOTICE 'pOrderDate = %', pOrderDate;
    RAISE NOTICE 'pShipVia = %', pShipVia;
    RAISE NOTICE 'pCurrId = %', pCurrId;
  END IF;

  SELECT fetchMetricBool('IncludePackageWeight') INTO _includepkgweight;

  --Get the order header information need to match
  --against price schedules
  IF (pOrderType = 'SO') THEN
    SELECT
      cust_id AS cust_id,
      custtype_id,
      custtype_code,
      COALESCE(shipto_id, -1) AS shipto_id,
      COALESCE(shipto_num, '') AS shipto_num,
      COALESCE(pOrderDate, cohead_orderdate) AS orderdate,
      COALESCE(pShipVia, cohead_shipvia) AS shipvia,
      shipto_shipzone_id AS shipzone_id,
      COALESCE(pCurrId, cohead_curr_id) AS curr_id,
      currConcat(COALESCE(pCurrId, cohead_curr_id)) AS currAbbr
    INTO _order
      FROM cohead
      JOIN custinfo ON (cust_id=COALESCE(pCustId, cohead_cust_id))
      JOIN custtype ON (custtype_id=cust_custtype_id)
      LEFT OUTER JOIN shiptoinfo ON (shipto_id=COALESCE(pShiptoId, cohead_shipto_id))
    WHERE (cohead_id=pOrderId);

  ELSIF (pOrderType = 'QU') THEN
    SELECT
      quhead_cust_id AS cust_id,
      custtype_id,
      custtype_code,
      COALESCE(shipto_id, -1) AS shipto_id,
      COALESCE(shipto_num, '') AS shipto_num,
      quhead_quotedate AS orderdate,
      quhead_shipvia AS shipvia,
      shipto_shipzone_id AS shipzone_id,
      quhead_curr_id AS curr_id,
      currConcat(quhead_curr_id) AS currAbbr
    INTO _order
      FROM quhead
      JOIN custinfo ON (cust_id=quhead_cust_id)
      JOIN custtype ON (custtype_id=cust_custtype_id)
      LEFT OUTER JOIN shiptoinfo ON (shipto_id=quhead_shipto_id)
    WHERE (quhead_id=pOrderId);

  ELSIF (pOrderType = 'RA') THEN
    SELECT
      cust_id AS cust_id,
      custtype_id,
      custtype_code,
      COALESCE(shipto_id, -1) AS shipto_id,
      COALESCE(shipto_num, '') AS shipto_num,
      COALESCE(pOrderDate, rahead_authdate) AS orderdate,
      ''::text AS shipvia,
      shipto_shipzone_id AS shipzone_id,
      COALESCE(pCurrId, rahead_curr_id) AS curr_id,
      currConcat(COALESCE(pCurrId, rahead_curr_id)) AS currAbbr
    INTO _order
      FROM rahead
      JOIN custinfo ON (cust_id=COALESCE(pCustId, rahead_cust_id))
      JOIN custtype ON (custtype_id=cust_custtype_id)
      LEFT OUTER JOIN shiptoinfo ON (shipto_id=COALESCE(pShiptoId, rahead_shipto_id))
    WHERE (rahead_id=pOrderId);

  ELSE
    RAISE EXCEPTION 'Invalid order type.';
  END IF;

  IF (_debug) THEN
    RAISE NOTICE 'cust_id = %', _order.cust_id;
    RAISE NOTICE 'custtype_id = %', _order.custtype_id;
    RAISE NOTICE 'shipto_id = %', _order.shipto_id;
    RAISE NOTICE 'shipto_num = %', _order.shipto_num;
    RAISE NOTICE 'orderdate = %', _order.orderdate;
    RAISE NOTICE 'shipvia = %', _order.shipvia;
    RAISE NOTICE 'shipzone_id = %', _order.shipzone_id;
    RAISE NOTICE 'curr_id = %', _order.curr_id;
    RAISE NOTICE 'currAbbr = %', _order.currAbbr;
  END IF;

  --Get a list of aggregated weights from sites and
  --freight classes used on order lines

  IF (_includePkgWeight) THEN
    _qry := 'SELECT SUM(orderitem_qty_ordered * orderitem_qty_invuomratio * (item_prodweight + item_packweight)) AS weight, ';
  ELSE
    _qry := 'SELECT SUM(orderitem_qty_ordered * orderitem_qty_invuomratio * item_prodweight) AS weight, ';
  END IF;

  _qry := _qry || 'itemsite_warehous_id, COALESCE(item_freightclass_id, -1) AS item_freightclass_id
    FROM orderitem
    JOIN itemsite ON (itemsite_id=orderitem_itemsite_id)
    JOIN item ON (item_id=itemsite_item_id) ';

  IF (pOrderType = 'RA') THEN
    _qry := _qry || 'JOIN raitem ON ((orderitem_id=raitem_id)
    AND (raitem_disposition IN (''C'',''R'',''P''))) ';
  END IF;

  _qry := _qry || '
    WHERE ( (orderitem_orderhead_type=' || quote_literal(pOrderType) || ')
      AND (orderitem_orderhead_id=' || quote_literal(pOrderId) || ')
      AND (orderitem_status <> ''X'') )
    GROUP BY itemsite_warehous_id, item_freightclass_id;';

  FOR _weights IN
    EXECUTE _qry LOOP

    _row := calculateFreightDetail(
      _order.cust_id, --pCustId
      _order.custtype_id, --pCustTypeId
      _order.custtype_code, --pCustTypeCode
      _order.shipto_id, --pShiptoId
      _order.shipzone_id, --pShipZoneId
      _order.shipto_num, --pShiptoNum
      _order.orderdate, --pOrderDate
      _order.shipvia, --pShipVia
      _order.curr_id, --pCurrId
      _order.currAbbr, --pCurrAbbr
      _weights.itemsite_warehous_id, --pItemSiteWhsId
      _weights.item_freightclass_id, --pItemFreightclassId
      _weights.weight --pWeight
      );

    RETURN NEXT _row;

  END LOOP;
  RETURN;
END;
$_$;


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

--
-- TOC entry 1653 (class 1255 OID 146566231)
-- Dependencies: 2681 4536 8
-- Name: freightdetailquote(integer, text, integer, text, date, text, text, text[]); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION freightdetailquote(integer, text, integer, text, date, text, text, text[]) RETURNS SETOF freightdata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;
  pCustNumber ALIAS FOR $2;
  pShiptoId ALIAS FOR $3;
  pShiptoNum ALIAS FOR $4;
  pOrderDate ALIAS FOR $5;
  pShipVia ALIAS FOR $6;
  pItemArrayType ALIAS FOR $7;
  pItemQty ALIAS FOR $8;
    -- Array item_id format = ARRAY[['300','3'],['310','50']]
    -- Array item_number format = ARRAY[['YTRUCK1','3'],['RTRUCK1','50']]
    -- Array itemsite_id format = ARRAY[['293','3'],['302','50']]

  _cust RECORD;
  _shipto RECORD;
  _curr RECORD;
  _includepkgweight BOOLEAN := FALSE;
  _order_date DATE;
  _ship_via TEXT;
  _item_num RECORD;
  _item_id RECORD;
  _weights RECORD;
  _row freightData%ROWTYPE;
  _debug BOOLEAN := FALSE;

BEGIN
-- Parameters are setup to allow this function to be called multiple ways.
-- Check parameters and lookup what is NULL.

  -- Check pCustId and pCustNumber.
  IF (pCustId IS NULL AND (pCustNumber IS NULL OR pCustNumber = '')) THEN
    RAISE EXCEPTION 'You must specify a Customer ID or Number to get a freight quote.';
  ELSIF (pCustId IS NULL AND pCustNumber IS NOT NULL) THEN
    -- Get customer info using pCustNumber.
    SELECT
      cust_id,
      cust_number,
      custtype_id,
      custtype_code,
      cust_curr_id,
      cust_shipvia
    INTO _cust
    FROM custinfo
    LEFT JOIN custtype ON cust_custtype_id = custtype_id
    WHERE 1=1
      AND cust_number = pCustNumber;
  ELSE
    -- Get customer info using pCustId.
    SELECT
      cust_id,
      cust_number,
      custtype_id,
      custtype_code,
      cust_curr_id,
      cust_shipvia
    INTO _cust
    FROM custinfo
    LEFT JOIN custtype ON cust_custtype_id = custtype_id
    WHERE 1=1
      AND cust_id = pCustId;
  END IF;

  IF(NOT FOUND) THEN
    RAISE EXCEPTION 'Invalid Customer specified when trying to get a freight quote.';
  END IF;

  -- Check pShiptoId and pShiptoNum.
  IF (pShiptoId IS NULL AND (pShiptoNum IS NULL OR pShiptoNum = '')) THEN
    -- Get Customer's default shipto.
    SELECT
      shipto_id,
      shipto_name,
      shipto_shipzone_id,
      shipto_shipvia,
      shipto_num
    INTO _shipto
    FROM shiptoinfo
    WHERE 1=1
      AND shipto_cust_id = _cust.cust_id
      AND shipto_default;
  ELSIF (pShiptoId IS NULL AND pShiptoNum IS NOT NULL) THEN
    -- Get shipto info using pShiptoNum.
    SELECT
      shipto_id,
      shipto_name,
      shipto_shipzone_id,
      shipto_shipvia,
      shipto_num
    INTO _shipto
    FROM shiptoinfo
    WHERE 1=1
      AND shipto_cust_id = _cust.cust_id
      AND shipto_num = pShiptoNum;
  ELSE
    -- Get shipto info using pShiptoId.
    SELECT
      shipto_id,
      shipto_name,
      shipto_shipzone_id,
      shipto_shipvia,
      shipto_num
    INTO _shipto
    FROM shiptoinfo
    WHERE 1=1
      AND shipto_cust_id = _cust.cust_id
      AND shipto_id = pShiptoId;
  END IF;

  IF(NOT FOUND) THEN
    RAISE EXCEPTION 'Invalid Ship-to specified when trying to get a freight quote.';
  END IF;

  -- Get curr info.
  SELECT
    curr_id,
    curr_abbr
  INTO _curr
  FROM curr_symbol
  WHERE 1=1
    AND curr_id = _cust.cust_curr_id;

  IF(NOT FOUND) THEN
    RAISE EXCEPTION 'Could not find currency when trying to get a freight quote.';
  END IF;

  -- Check pOrderDate.
  IF (pOrderDate IS NULL) THEN
    _order_date := CURRENT_DATE;
  ELSE
    _order_date := pOrderDate;
  END IF;

  -- Check pShipVia.
  IF (pShipVia IS NULL OR pShipVia = '') THEN
    IF (_shipto.shipto_shipvia IS NULL OR _shipto.shipto_shipvia = '') THEN
      _ship_via := _cust.cust_shipvia;
    ELSE
      _ship_via := _shipto.shipto_shipvia;
    END IF;
  ELSE
    _ship_via := pShipVia;
  END IF;

  -- Determine if package weight should be included in freight calculation.
  SELECT fetchMetricBool('IncludePackageWeight') INTO _includepkgweight;

  -- Check pItemQty.
  IF (pItemQty IS NULL OR array_upper(pItemQty,1) IS NULL) THEN
    -- Item Array is NULL.
    RAISE EXCEPTION 'You must specify an Item ID, Item Number or Itemsite ID to get a freight quote.';
  ELSIF (pItemArrayType = 'item_number' AND (array_upper(pItemQty,1) > 0)) THEN
    -- Using item_number.
    FOR _weights IN
      -- Get a list of aggregated weights from sites and freight classes for items.
      SELECT
        CASE WHEN _includepkgweight THEN
          SUM(qty * (item_prodweight + item_packweight))
        ELSE
          SUM(qty * (item_prodweight))
        END AS weight,
        itemsite_warehous_id,
        COALESCE(item_freightclass_id, -1) AS item_freightclass_id
      FROM
        -- Create item_number -> qty record from array.
        (SELECT
          unnest((SELECT pItemQty[1:array_upper(pItemQty,1)][1])) AS item_number,
          unnest((SELECT pItemQty[1:array_upper(pItemQty,1)][2:array_ndims(pItemQty)]))::numeric AS qty
        ) AS itemnum_qty
        JOIN item USING (item_number)
        JOIN itemsite ON item_id=itemsite_item_id
      WHERE 1=1
        AND itemsite_warehous_id = fetchprefwarehousid()
      GROUP BY
        itemsite_warehous_id,
        item_freightclass_id
    LOOP
      -- Calculate the freight detail for these item weights.
      _row := calculateFreightDetail(
        _cust.cust_id, --pCustId
        _cust.custtype_id, --pCustTypeId
        _cust.custtype_code, --pCustTypeCode
        _shipto.shipto_id, --pShiptoId
        _shipto.shipto_shipzone_id, --pShipZoneId
        _shipto.shipto_num, --pShiptoNum
        _order_date, --pOrderDate
        _ship_via, --pShipVia
        _curr.curr_id, --pCurrId
        _curr.curr_abbr, --pCurrAbbr
        _weights.itemsite_warehous_id, --pItemSiteWhsId
        _weights.item_freightclass_id, --pItemFreightclassId
        _weights.weight --pWeight
        );

      RETURN NEXT _row;
    END LOOP;

  ELSIF (pItemArrayType = 'item_id' AND (array_upper(pItemQty,1) > 0)) THEN
    -- Using item_id.
    FOR _weights IN
      -- Get a list of aggregated weights from sites and freight classes for items.
      SELECT
        CASE WHEN _includepkgweight THEN
          SUM(qty * (item_prodweight + item_packweight))
        ELSE
          SUM(qty * (item_prodweight))
        END AS weight,
        itemsite_warehous_id,
        COALESCE(item_freightclass_id, -1) AS item_freightclass_id
      FROM
        -- Create item_id -> qty record from array.
        (SELECT
          unnest((SELECT pItemQty[1:array_upper(pItemQty,1)][1]))::integer AS item_id,
          unnest((SELECT pItemQty[1:array_upper(pItemQty,1)][2:array_ndims(pItemQty)]))::numeric AS qty
        ) AS itemid_qty
        JOIN item USING (item_id)
        JOIN itemsite ON item_id=itemsite_item_id
      WHERE 1=1
        AND itemsite_warehous_id = fetchprefwarehousid()
      GROUP BY
        itemsite_warehous_id,
        item_freightclass_id
    LOOP
      -- Calculate the freight detail for these item weights.
      _row := calculateFreightDetail(
        _cust.cust_id, --pCustId
        _cust.custtype_id, --pCustTypeId
        _cust.custtype_code, --pCustTypeCode
        _shipto.shipto_id, --pShiptoId
        _shipto.shipto_shipzone_id, --pShipZoneId
        _shipto.shipto_num, --pShiptoNum
        _order_date, --pOrderDate
        _ship_via, --pShipVia
        _curr.curr_id, --pCurrId
        _curr.curr_abbr, --pCurrAbbr
        _weights.itemsite_warehous_id, --pItemSiteWhsId
        _weights.item_freightclass_id, --pItemFreightclassId
        _weights.weight --pWeight
        );

      RETURN NEXT _row;
    END LOOP;
  ELSIF (pItemArrayType = 'itemsite_id' AND (array_upper(pItemQty,1) > 0)) THEN
    -- Using itemsite_id.
    FOR _weights IN
      -- Get a list of aggregated weights from sites and freight classes for items.
      SELECT
        CASE WHEN _includepkgweight THEN
          SUM(qty * (item_prodweight + item_packweight))
        ELSE
          SUM(qty * (item_prodweight))
        END AS weight,
        itemsite_warehous_id,
        COALESCE(item_freightclass_id, -1) AS item_freightclass_id
      FROM
        -- Create itemsite_id -> qty record from array.
        (SELECT
          unnest((SELECT pItemQty[1:array_upper(pItemQty,1)][1]))::integer AS itemsite_id,
          unnest((SELECT pItemQty[1:array_upper(pItemQty,1)][2:array_ndims(pItemQty)]))::numeric AS qty
        ) AS itemsiteid_qty
        JOIN itemsite USING (itemsite_id)
        JOIN item ON item_id=itemsite_item_id
      WHERE 1=1
      GROUP BY
        itemsite_warehous_id,
        item_freightclass_id
    LOOP
      -- Calculate the freight detail for these item weights.
      _row := calculateFreightDetail(
        _cust.cust_id, --pCustId
        _cust.custtype_id, --pCustTypeId
        _cust.custtype_code, --pCustTypeCode
        _shipto.shipto_id, --pShiptoId
        _shipto.shipto_shipzone_id, --pShipZoneId
        _shipto.shipto_num, --pShiptoNum
        _order_date, --pOrderDate
        _ship_via, --pShipVia
        _curr.curr_id, --pCurrId
        _curr.curr_abbr, --pCurrAbbr
        _weights.itemsite_warehous_id, --pItemSiteWhsId
        _weights.item_freightclass_id, --pItemFreightclassId
        _weights.weight --pWeight
        );

      RETURN NEXT _row;
    END LOOP;
  ELSE -- The item array provided is invalid.
    RAISE EXCEPTION 'The Item/Itemsite array provided when trying to get a freight quote is invalid.';
  END IF;

  IF(NOT FOUND) THEN
    RAISE EXCEPTION 'Error trying to aggregated weights when getting a freight quote.';
  END IF;

  -- Print debug.
  IF (_debug) THEN
    RAISE NOTICE 'pCustId = %', _cust.cust_id;
    RAISE NOTICE 'pCustTypeId = %', _cust.custtype_id;
    RAISE NOTICE 'pCustTypeCode = %', _cust.custtype_code;
    RAISE NOTICE 'pShiptoId = %', _shipto.shipto_id;
    RAISE NOTICE 'pShipZoneId = %', _shipto.shipto_shipzone_id;
    RAISE NOTICE 'pShiptoNum = %', _shipto.shipto_num;
    RAISE NOTICE 'pOrderDate = %', _order_date;
    RAISE NOTICE 'pShipVia = %', _ship_via;
    RAISE NOTICE 'pCurrId = %', _curr.curr_id;
    RAISE NOTICE 'pCurrAbbr = %', _curr.curr_abbr;
  END IF;

  RETURN;
END;
$_$;


ALTER FUNCTION public.freightdetailquote(integer, text, integer, text, date, text, text, text[]) OWNER TO admin;

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

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

BEGIN
  IF (pordertype = 'TO' AND NOT fetchMetricBool('MultiWhs')) THEN
    RETURN 0;
  ELSIF (pordertype = 'RA' AND NOT fetchMetricBool('EnableReturnAuth')) THEN
    RETURN 0;
  END IF;

  SELECT SUM(COALESCE(recv_freight, 0)) INTO _freight
  FROM recv
  WHERE ((recv_orderitem_id=porderitemid)
    AND  (recv_posted = pposted)
    AND  (recv_order_type=pordertype));

  RETURN COALESCE(_freight, 0.0);

END;
$_$;


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

--
-- TOC entry 1655 (class 1255 OID 146566234)
-- Dependencies: 8
-- Name: gen_salt(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION gen_salt(text) RETURNS text
    LANGUAGE c STRICT
    AS '$libdir/pgcrypto', 'pg_gen_salt';


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

--
-- TOC entry 1656 (class 1255 OID 146566235)
-- Dependencies: 8
-- Name: gen_salt(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION gen_salt(text, integer) RETURNS text
    LANGUAGE c STRICT
    AS '$libdir/pgcrypto', 'pg_gen_salt_rounds';


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

--
-- TOC entry 1657 (class 1255 OID 146566236)
-- Dependencies: 4536 8
-- Name: getactiverevid(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  --See if revcontrol turned on
  IF (fetchmetricbool('RevControl')) THEN

    IF (pTargetType='BOM') THEN
      SELECT rev_id INTO _revid
      FROM rev
      WHERE ((rev_target_type='BOM')
      AND (rev_target_id=pTargetid)
      AND (rev_status='A'));
      IF (NOT FOUND) THEN
        _revid:=-1;
      END IF;

      ELSE IF (pTargetType='BOO') THEN
      SELECT rev_id INTO _revid
      FROM rev
      WHERE ((rev_target_type='BOO')
      AND (rev_target_id=pTargetid)
      AND (rev_status='A'));
      IF (NOT FOUND) THEN
        _revid:=-1;
      END IF;

      ELSE
        RAISE EXCEPTION 'Invalid Revision Type';
      END IF;
    END IF;

  ELSE
    _revid:=-1;
  END IF;

  RETURN _revid;

END;
$_$;


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

--
-- TOC entry 1658 (class 1255 OID 146566237)
-- Dependencies: 4536 8
-- Name: getaddrid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getaddrid(paddressnumber text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  IF (pAddressNumber IS NULL OR pAddressNumber = '') THEN
    RETURN NULL;
  END IF;

  SELECT addr_id INTO _returnVal
  FROM addr
  WHERE (addr_number=pAddressNumber);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Address Number % not found.', pAddressNumber;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getaddrid(paddressnumber text) OWNER TO admin;

--
-- TOC entry 1659 (class 1255 OID 146566238)
-- Dependencies: 4536 8
-- Name: getadjustmenttaxtypeid(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getadjustmenttaxtypeid() RETURNS integer
    LANGUAGE plpgsql IMMUTABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _taxtypeid INTEGER;
BEGIN
  SELECT taxtype_id
    INTO _taxtypeid
  FROM taxtype
  WHERE (taxtype_name='Adjustment');

  RETURN _taxtypeid;
END;
$$;


ALTER FUNCTION public.getadjustmenttaxtypeid() OWNER TO admin;

--
-- TOC entry 1660 (class 1255 OID 146566239)
-- Dependencies: 4536 8
-- Name: getaropenid(text, character, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getaropenid(text, character, 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
  pCustNumber ALIAS FOR $1;
  pDocType ALIAS FOR $2;
  pDocNumber ALIAS FOR $3;
  _returnVal INTEGER;
BEGIN
  IF ((pCustNumber IS NULL) OR (pDocType IS NULL) OR (pDocNumber IS NULL)) THEN
	RETURN NULL;
  END IF;

  SELECT aropen_id INTO _returnVal
  FROM aropen
  WHERE ((aropen_cust_id=getCustId(pCustNumber,true))
    AND  (UPPER(aropen_doctype)=UPPER(pDocType))
    AND  (UPPER(aropen_docnumber)=UPPER(pDocNumber)));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'AR Open Item % not found.', pDocNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1661 (class 1255 OID 146566240)
-- Dependencies: 4536 8
-- Name: getbankaccntid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getbankaccntid(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
  pBankAccntName ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pBankAccntName IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT bankaccnt_id INTO _returnVal
  FROM bankaccnt
  WHERE (UPPER(bankaccnt_name)=UPPER(pBankAccntName));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Bank Account % not found.', pBankAccntName;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1662 (class 1255 OID 146566241)
-- Dependencies: 4536 8
-- Name: getbomitemid(text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getbomitemid(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
  pItemNumber ALIAS FOR $1;
  pRevision ALIAS FOR $2;
  pSeqNumber ALIAS FOR $3;
  _returnVal INTEGER;
  
BEGIN
  IF ((pItemNumber IS NULL) OR (pSeqNumber IS NULL) OR (pItemNumber = '') OR (pSeqNumber = '') ) THEN
    RETURN NULL;
  END IF;

  SELECT bomitem_id INTO _returnVal
  FROM bomitem(getItemId(pItemNumber),COALESCE(getRevId('BOM',pItemNumber,pRevision)))
  WHERE (bomitem_seqnumber=pSeqNumber);
    
  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Sequence % on Bill of Material % Revision % not found.', pSeqNumber, pItemNumber, pRevision;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1650 (class 1255 OID 146566242)
-- Dependencies: 4536 8
-- Name: getbooitemseqid(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getbooitemseqid(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
  pItemNumber ALIAS FOR $1;
  pSeqNumber ALIAS FOR $2;
  _revid INTEGER;
  _returnVal INTEGER;
  
BEGIN
  IF ((pItemNumber IS NULL) OR (pSeqNumber IS NULL)) THEN
    RETURN NULL;
  END IF;

  IF (NOT fetchMetricBool('Routings')) THEN
    RETURN -1;
  ELSE
    SELECT booitem_seq_id INTO _returnVal
    FROM booitem(getItemId(pItemNumber))
    WHERE (booitem_seqnumber=CAST(pSeqNumber AS integer));
  END IF;
    
  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Boo Sequence % for Item % not found.', pSeqNumber, pItemNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1663 (class 1255 OID 146566243)
-- Dependencies: 4536 8
-- Name: getbudgheadid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getbudgheadid(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
  pBudghead ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pBudghead IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT budghead_id INTO _returnVal
  FROM budghead
  WHERE (budghead_name=(pBudghead));

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Budget % not found.', pBudghead;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1664 (class 1255 OID 146566244)
-- Dependencies: 4536 8
-- Name: getcashrcptid(text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcashrcptid(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
  pCustNumber ALIAS FOR $1;
  pFundsType ALIAS FOR $2;
  pDocNumber ALIAS FOR $3;
  _returnVal INTEGER;
BEGIN
  IF ((pCustNumber IS NULL) OR (pFundsType IS NULL) OR (pDocNumber IS NULL)) THEN
	RETURN NULL;
  END IF;

  SELECT cashrcpt_id INTO _returnVal
  FROM cashrcpt
  WHERE ((cashrcpt_cust_id=getCustId(pCustNumber,true))
    AND  (UPPER(cashrcpt_fundstype)=UPPER(pFundsType))
    AND  (UPPER(cashrcpt_docnumber)=UPPER(pDocNumber)));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Cash Receipt % not found.', pDocNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1665 (class 1255 OID 146566245)
-- Dependencies: 4536 8
-- Name: getcharid(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcharid(pchar text, ptype text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  IF (COALESCE(pChar, '') = '') THEN
    RETURN NULL;
  END IF;

  SELECT char_id INTO _returnVal
  FROM char
  WHERE ((char_name=pChar)
  AND ((pType IN ('C','CT') AND char_customers)
    OR (pType IN ('I','SI','QI','W','PI','TI') AND char_items)
    OR (pType='CRMACCT' AND char_crmaccounts)
    OR (pType='ADDR' AND char_addresses)
    OR (pType='CNTCT' AND char_contacts)
    OR (pType='LS' AND char_lotserial)
    OR (pType='EMP' AND char_employees)
    OR (pType='INCDT' AND char_incidents)
    )) LIMIT 1;

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Characteristic % not found.', pChar;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getcharid(pchar text, ptype text) OWNER TO admin;

--
-- TOC entry 1666 (class 1255 OID 146566246)
-- Dependencies: 4536 8
-- Name: getclasscodeid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getclasscodeid(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
  pClassCode ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pClassCode IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT classcode_id INTO _returnVal
  FROM classcode
  WHERE (classcode_code=pClassCode);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Class Code % not found.', pClassCode;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1667 (class 1255 OID 146566247)
-- Dependencies: 4536 8
-- Name: getcmheadid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcmheadid(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
  pCreditMemoNumber ALIAS FOR $1;
BEGIN
  RETURN getCmheadId(pCreditMemoNumber, NULL);
END;
$_$;


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

--
-- TOC entry 1668 (class 1255 OID 146566248)
-- Dependencies: 4536 8
-- Name: getcmheadid(text, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcmheadid(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
  pCreditMemoNumber ALIAS FOR $1;
  pPosted ALIAS FOR $2;
  _returnVal INTEGER;
BEGIN
  IF (pCreditMemoNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT cmhead_id INTO _returnVal
  FROM cmhead
  WHERE (UPPER(cmhead_number)=UPPER(pCreditMemoNumber))
    AND ((pPosted IS NULL) OR (cmhead_posted=pPosted));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Credit Memo % not found.', pCreditMemoNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1669 (class 1255 OID 146566249)
-- Dependencies: 4536 8
