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

CREATE FUNCTION voidpostedapcheck(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RAISE WARNING 'voidPostedAPCheck() is deprecated - use voidPostedCheck() instead';
  RETURN voidPostedCheck($1, fetchJournalNumber('AP-CK'), CURRENT_DATE);
END;
$_$;


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

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

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


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

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

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


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

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

CREATE FUNCTION voidpostedcheck(integer, integer, date) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCheckid		ALIAS FOR $1;
  pJournalNumber	ALIAS FOR $2;
  pVoidDate		ALIAS FOR $3;
  _amount_base		NUMERIC := 0;
  _result               INTEGER;
  _apopenid		INTEGER;
  _credit_glaccnt	INTEGER;
  _docnumber		TEXT;
  _exchGain		NUMERIC := 0;
  _exchGainTmp		NUMERIC := 0;
  _gltransNote		TEXT;
  _p			RECORD;
  _r			RECORD;
  _sequence		INTEGER;
  _amount_check         NUMERIC := 0;

BEGIN

  SELECT fetchGLSequence() INTO _sequence;

  SELECT checkhead.*,
         checkhead_amount / checkhead_curr_rate AS checkhead_amount_base,
         bankaccnt_accnt_id AS bankaccntid,
         findPrepaidAccount(checkhead_recip_id) AS prepaidaccntid,
	 checkrecip.* INTO _p
  FROM bankaccnt, checkhead LEFT OUTER JOIN
       checkrecip ON ((checkrecip_type=checkhead_recip_type)
		  AND (checkrecip_id=checkhead_recip_id))
  WHERE ((checkhead_bankaccnt_id=bankaccnt_id)
    AND  (checkhead_id=pCheckid));

  IF (NOT _p.checkhead_posted) THEN
    RETURN -10;
  END IF;

  IF (_p.checkrecip_id IS NULL) THEN	-- outer join failed
    RETURN -11;
  END IF;

  -- Cannot void if already reconciled
  SELECT trans_id INTO _result
  FROM ( SELECT gltrans_id AS trans_id
         FROM gltrans
              LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='GL' AND bankrecitem_source_id=gltrans_id)
         WHERE ( (gltrans_doctype='CK')
           AND   (gltrans_misc_id=_p.checkhead_id)
           AND   ((gltrans_rec) OR (bankrecitem_id IS NOT NULL)) )
         UNION ALL
         SELECT sltrans_id AS trans_id
         FROM sltrans
              LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='GL' AND bankrecitem_source_id=sltrans_id)
         WHERE ( (sltrans_doctype='CK')
           AND   (sltrans_misc_id=_p.checkhead_id)
           AND   ((sltrans_rec) OR (bankrecitem_id IS NOT NULL)) )
       ) AS data;
  IF (FOUND) THEN
    RETURN -14;
  END IF;

  _gltransNote := 'Void Posted Check #' || _p.checkhead_number || ' ' ||
		  _p.checkrecip_number || '-' || _p.checkrecip_name;

  IF (_p.checkhead_misc) THEN
    IF (COALESCE(_p.checkhead_expcat_id, -1) < 0) THEN
      IF (_p.checkhead_recip_type = 'V') THEN
	PERFORM createAPDebitMemo(_p.checkhead_recip_id, pJournalNumber,
				  CAST(fetchAPMemoNumber() AS text), '',
				  pVoidDate, _p.checkhead_amount,
				  _gltransNote || ' '|| _p.checkhead_notes,
				  -1, pVoidDate, -1, _p.checkhead_curr_id );
	_credit_glaccnt := findAPPrepaidAccount(_p.checkhead_recip_id);

      ELSIF (_p.checkhead_recip_type = 'C') THEN
	PERFORM createARCreditMemo(NULL, _p.checkhead_recip_id,
				  fetchARMemoNumber(), '', 
				  pVoidDate, _p.checkhead_amount,
				  _gltransNote || ' '|| _p.checkhead_notes,
				  -1, -1, -1, pVoidDate, -1, NULL, 0.0,
				  pJournalNumber, _p.checkhead_curr_id );
	_credit_glaccnt := _p.prepaidaccntid;

      ELSIF (_p.checkhead_recip_type = 'T') THEN
	-- TODO: should we create a debit memo for the tax authority? how?
	_credit_glaccnt := _p.checkrecip_accnt_id;

      END IF; -- recip type

    ELSE
      SELECT expcat_exp_accnt_id INTO _credit_glaccnt
      FROM expcat
      WHERE (expcat_id=_p.checkhead_expcat_id);
      IF (NOT FOUND) THEN
        RETURN -12;
      END IF;
    END IF;

    IF (COALESCE(_credit_glaccnt, -1) < 0) THEN
      RETURN -13;
    END IF;

    PERFORM insertIntoGLSeries( _sequence, _p.checkrecip_gltrans_source, 'CK',
				text(_p.checkhead_number),
				_credit_glaccnt,
				round(_p.checkhead_amount_base, 2),
				pVoidDate, _gltransNote, pCheckid);

    _amount_base := _p.checkhead_amount_base;

  ELSE
    FOR _r IN SELECT checkitem_amount, checkitem_discount,
                     CASE WHEN (checkitem_apopen_id IS NOT NULL AND apopen_doctype='C') THEN
                            checkitem_amount / apopen_curr_rate * -1.0
                          WHEN (checkitem_apopen_id IS NOT NULL) THEN
                            checkitem_amount / apopen_curr_rate
                          ELSE
                            currToBase(checkitem_curr_id,
                                       checkitem_amount,
                                       COALESCE(checkitem_docdate, _p.checkhead_checkdate)) 
                     END AS checkitem_amount_base,
                     currTocurr(checkitem_curr_id, _p.checkhead_curr_id,
                                CASE WHEN (checkitem_apopen_id IS NOT NULL AND apopen_doctype='C') THEN
                                          checkitem_amount * -1.0
                                     ELSE checkitem_amount END,
                                  _p.checkhead_checkdate) AS amount_check,
                     apopen_id, apopen_doctype, apopen_docnumber, apopen_curr_rate, apopen_docdate,
                     aropen_id, aropen_doctype, aropen_docnumber,
                     checkitem_curr_id, checkitem_curr_rate,
                     COALESCE(checkitem_docdate, _p.checkhead_checkdate) AS docdate
              FROM (checkitem LEFT OUTER JOIN
		    apopen ON (checkitem_apopen_id=apopen_id)) LEFT OUTER JOIN
		    aropen ON (checkitem_aropen_id=aropen_id)
              WHERE (checkitem_checkhead_id=pcheckid) LOOP

      _exchGainTmp := 0;
      IF (_r.apopen_id IS NOT NULL) THEN
	-- undo the APDiscount Credit Memo if a discount was taken
        IF(_r.checkitem_discount > 0) THEN
          SELECT NEXTVAL('apopen_apopen_id_seq') INTO _apopenid;
          SELECT fetchAPMemoNumber() INTO _docnumber;
          INSERT INTO apopen
          ( apopen_id, apopen_username, apopen_journalnumber,
            apopen_vend_id, apopen_docnumber, apopen_doctype, apopen_ponumber,
            apopen_docdate, apopen_duedate, apopen_distdate, apopen_terms_id,
            apopen_amount, apopen_paid, apopen_open,
	    apopen_notes,
	    apopen_accnt_id, apopen_curr_id, apopen_discount, apopen_curr_rate,
            apopen_closedate )
          VALUES
          ( _apopenid, getEffectiveXtUser(), pJournalNumber,
            _p.checkhead_recip_id, _docnumber, 'D', '',
            pVoidDate, pVoidDate, pVoidDate, -1,
            _r.checkitem_discount, _r.checkitem_discount, FALSE,
            ('Reverse Posted Discount ' || _r.apopen_doctype || ' ' ||
	      _r.apopen_docnumber),
	    -1, _p.checkhead_curr_id, TRUE, _r.apopen_curr_rate,
            current_date );


          PERFORM insertIntoGLSeries( _sequence, _p.checkrecip_gltrans_source,
                                      'DS', _r.apopen_docnumber,
                                      findAPDiscountAccount(_p.checkhead_recip_id),
                                      round(_r.checkitem_discount / _r.apopen_curr_rate, 2) * -1,
                                      pVoidDate, _gltransNote, pCheckid);

          PERFORM insertIntoGLSeries( _sequence, _p.checkrecip_gltrans_source,
                                      'DS', _r.apopen_docnumber,
                                      findAPAccount(_p.checkhead_recip_id),
                                      round(_r.checkitem_discount / _r.apopen_curr_rate, 2),
                                      pVoidDate, _gltransNote, pCheckid);

	  --  Post the application
          INSERT INTO apapply
          ( apapply_vend_id, apapply_postdate, apapply_username,
            apapply_source_apopen_id, apapply_source_doctype, apapply_source_docnumber,
            apapply_target_apopen_id, apapply_target_doctype, apapply_target_docnumber,
            apapply_journalnumber, apapply_amount, apapply_curr_id )
          VALUES
          ( _p.checkhead_recip_id, pVoidDate, getEffectiveXtUser(),
            _apopenid, 'D', _docnumber,
            _r.apopen_id, _r.apopen_doctype, _r.apopen_docnumber,
            pJournalNumber, (_r.checkitem_discount * -1), _r.checkitem_curr_id );
        END IF; -- discount was taken

        UPDATE apopen
       SET apopen_paid = round(apopen_paid -
				(_r.checkitem_amount + noNeg(_r.checkitem_discount)), 2),
            apopen_open = round(apopen_amount, 2) >
			  round(apopen_paid -
				(_r.checkitem_amount + noNeg(_r.checkitem_discount)), 2),
            apopen_closedate = CASE WHEN (round(apopen_amount, 2) >
			                  round(apopen_paid -
				           (_r.checkitem_amount + noNeg(_r.checkitem_discount)))) THEN NULL ELSE apopen_closedate END
        WHERE (apopen_id=_r.apopen_id);

	--  Post the application
        INSERT INTO apapply
        ( apapply_vend_id, apapply_postdate, apapply_username,
          apapply_source_apopen_id, apapply_source_doctype, apapply_source_docnumber,
          apapply_target_apopen_id, apapply_target_doctype, apapply_target_docnumber,
          apapply_journalnumber, apapply_amount, apapply_curr_id )
        VALUES
        ( _p.checkhead_recip_id, pVoidDate, getEffectiveXtUser(),
          -1, 'K', _p.checkhead_number,
          _r.apopen_id, _r.apopen_doctype, _r.apopen_docnumber,
          pJournalNumber, (_r.checkitem_amount * -1), _r.checkitem_curr_id );
      END IF; -- if check item's apopen_id is not null

      IF (_r.aropen_id IS NOT NULL) THEN
        UPDATE aropen
        SET aropen_paid = round(aropen_paid -_r.checkitem_amount, 2),
            aropen_open = round(aropen_amount, 2) >
			  round(aropen_paid - _r.checkitem_amount, 2)
        WHERE (aropen_id=_r.aropen_id);

	--  Post the application
        INSERT INTO arapply
        ( arapply_cust_id, arapply_postdate, arapply_distdate, arapply_username,
          arapply_source_aropen_id, arapply_source_doctype, arapply_source_docnumber,
          arapply_target_aropen_id, arapply_target_doctype, arapply_target_docnumber,
          arapply_journalnumber, arapply_applied, arapply_curr_id )
        VALUES
        ( _p.checkhead_recip_id, pVoidDate, pVoidDate, getEffectiveXtUser(),
          -1, 'K', _p.checkhead_number,
          _r.aropen_id, _r.aropen_doctype, _r.aropen_docnumber,
          pJournalNumber, (_r.checkitem_amount * -1), _r.checkitem_curr_id );

      END IF; -- if check item's aropen_id is not null

--  calculate currency gain/loss
      IF (_r.apopen_id IS NOT NULL) THEN
        IF (_p.checkhead_curr_id = _r.checkitem_curr_id) THEN
          IF (_r.apopen_docdate > _p.checkhead_checkdate) THEN
            _exchGainTmp := ((_r.checkitem_amount/_p.checkhead_curr_rate) - (_r.checkitem_amount / _r.apopen_curr_rate)) * -1;
          ELSE
            _exchGainTmp := ((_r.checkitem_amount / _r.apopen_curr_rate) - (_r.checkitem_amount/_p.checkhead_curr_rate));
          END IF;
        ELSE
          -- unusual condition where bank overridden and different currency from voucher
          -- this does not work for all situations
          --IF (_r.apopen_docdate > _p.checkhead_checkdate) THEN
          --  _exchGainTmp := ((_r.checkitem_amount/_r.checkitem_curr_rate) - (_r.checkitem_amount / _r.apopen_curr_rate)) * -1;
          --ELSE
          --  _exchGainTmp := ((_r.checkitem_amount / _r.apopen_curr_rate) - (_r.checkitem_amount/_r.checkitem_curr_rate));
          --END IF;
          _exchGainTmp := 0.0;
        END IF;
      ELSE
        SELECT arCurrGain(_r.aropen_id,_r.checkitem_curr_id, _r.checkitem_amount,
                        _p.checkhead_checkdate)
              INTO _exchGainTmp;
      END IF;
      _exchGain := _exchGain + _exchGainTmp;

      PERFORM insertIntoGLSeries( _sequence, _p.checkrecip_gltrans_source,
				  'CK', text(_p.checkhead_number),
                                  _p.checkrecip_accnt_id,
                                  round(_r.checkitem_amount_base, 2),
                                  pVoidDate, _gltransNote, pCheckid);
      IF (_exchGainTmp <> 0) THEN
          PERFORM insertIntoGLSeries( _sequence, _p.checkrecip_gltrans_source,
				      'CK', text(_p.checkhead_number),
				      getGainLossAccntId(_p.checkrecip_accnt_id),
				      round(_exchGainTmp, 2) * -1,
				      pVoidDate, _gltransNote, pCheckid);
      END IF;

      _amount_check := (_amount_check + _r.amount_check);
      _amount_base := (_amount_base + _r.checkitem_amount_base);

    END LOOP;

    IF( (_amount_check - _p.checkhead_amount) <> 0.0 ) THEN 
      _exchGainTmp :=  (_amount_check - _p.checkhead_amount) / _p.checkhead_curr_rate;
      _exchGain := _exchGain + _exchGainTmp;
    END IF;

    --  ensure that the check balances, attribute rounding errors to gain/loss
    IF round(_amount_base, 2) - round(_exchGain, 2) <> round(_p.checkhead_amount_base, 2) THEN
      IF round(_amount_base - _exchGain, 2) = round(_p.checkhead_amount_base, 2) THEN
	PERFORM insertIntoGLSeries( _sequence, _p.checkrecip_gltrans_source,
				    'CK',
				    text(_p.checkhead_number), getGainLossAccntId(_p.bankaccntid),
				    (round(_amount_base, 2) -
				     round(_exchGain, 2) -
				     round(_p.checkhead_amount_base, 2)) * -1,
				    pVoidDate, _gltransNote, pCheckid);
      ELSE
	RAISE EXCEPTION 'checkhead_id % does not balance (% - % <> %)', pCheckid,
	      _amount_base, _exchGain, _p.checkhead_amount_base;
      END IF;
    END IF;
  END IF;

  PERFORM insertIntoGLSeries( _sequence, _p.checkrecip_gltrans_source, 'CK',
			      text(_p.checkhead_number),
                              _p.bankaccntid,
			      round(_p.checkhead_amount_base, 2) * -1,
                              pVoidDate, _gltransNote, pCheckid);

  PERFORM postGLSeries(_sequence, pJournalNumber);

  UPDATE gltrans
     SET gltrans_misc_id=pCheckid
   WHERE gltrans_sequence=_sequence;

  UPDATE checkhead
  SET checkhead_posted=false,
      checkhead_void=true,
      checkhead_journalnumber=pJournalNumber
  WHERE (checkhead_id=pCheckid);

  RETURN pJournalNumber;

END;
$_$;


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

--
-- Name: woeffectivedate(date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION woeffectivedate(date) 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
  pStartDate ALIAS FOR $1;

BEGIN

  IF (explodeWoEffective() = 'E') THEN
    RETURN CURRENT_DATE;
  ELSE
    RETURN pStartDate;
  END IF;

END;
$_$;


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

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

CREATE FUNCTION woinvavail(integer, integer, boolean, boolean) RETURNS SETOF woinvav
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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; 
   plevel ALIAS FOR $2;
   pshowshortage ALIAS FOR $3;
   pshowlowinventory ALIAS FOR $4;
  _row woinvav%ROWTYPE;
  _x RECORD;
  _subx RECORD;
  _index INTEGER;
  _level INTEGER;
  _qry TEXT;   
BEGIN   
    FOR _x IN
          SELECT wo_id,
                itemsite_id,
                item_type,
                wo_number,
                wo_subnumber,                             
                item_number,
                item_descrip1, 
                item_descrip2, 
                uom_name,
                qoh, 
                wobalance, 
                allocated, 
                ordered,                        
                reorderlevel,
                (qoh + ordered - wobalance) AS woavail,
                (qoh + ordered - allocated) AS totalavail 
         FROM(SELECT wo_id,
                itemsite_id,
                item_type,
                wo_number,
                wo_subnumber,                             
                item_number,
                item_descrip1, 
                item_descrip2, 
                uom_name,
                noNeg(qtyAvailable(itemsite_id)) AS qoh, 
                noNeg(wo_qtyord - wo_qtyrcv) AS wobalance, 
                qtyAllocated(itemsite_id, wo_duedate) AS allocated, 
                qtyOrdered(itemsite_id, wo_duedate) AS ordered,                        
                CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel 
          FROM wo, itemsite, item, uom     
         WHERE ((wo_ordid = pwoid AND wo_ordtype = 'W')
           AND NOT (wo_status = 'C')          
           AND (itemsite_id = wo_itemsite_id)
           AND (itemsite_item_id=item_id)
           AND (item_inv_uom_id=uom_id))               
         ORDER BY wo_number, wo_subnumber) AS data
       LOOP       
         _row.woinvav_itemsite_id := _x.itemsite_id;            
         _row.woinvav_womatl_id := -1;
         _row.woinvav_type := _x.item_type;          
         _row.woinvav_item_wo_number := _x.wo_number || '-' || _x.wo_subnumber;
         _row.woinvav_descrip := _x.item_descrip1 || ' ' || _x.item_descrip2;
         _row.woinvav_uomname := _x.uom_name;
         _row.woinvav_qoh := _x.qoh;
         _row.woinvav_balance := _x.wobalance;
         _row.woinvav_allocated := _x.allocated;     
         _row.woinvav_ordered := _x.ordered;         
         _row.woinvav_woavail := _x.woavail;
         _row.woinvav_totalavail := _x.totalavail;
         _row.woinvav_reorderlevel := _x.reorderlevel;               
         _row.woinvav_level := plevel;                       
         RETURN NEXT _row;         
        --get materials for this level
        FOR _subx IN
          SELECT * FROM woinvavailmatl(_x.wo_id, plevel + 1, pshowshortage, pshowlowinventory) 
        LOOP                                            
	  RETURN NEXT _subx;
	END LOOP;
        --get next level wo
        FOR _subx IN
          SELECT * FROM woinvavail(_x.wo_id, plevel + 1, pshowshortage, pshowlowinventory) 
        LOOP                                            
	  RETURN NEXT _subx;
	END LOOP;
      END LOOP;   
  RETURN;
END;
$_$;


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

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

CREATE FUNCTION woinvavail(integer, boolean, boolean, boolean, boolean) RETURNS SETOF woinvav
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;   
   pshowchildindent ALIAS FOR $2;    
   pshowchildsum ALIAS FOR $3;
   pshowshortage ALIAS FOR $4;
   pshowlowinventory ALIAS FOR $5;
  _row woinvav%ROWTYPE;
  _subrow woinvav%ROWTYPE;
  _wonumber TEXT;
  _x RECORD;
  _subx RECORD;  
  _qry TEXT;
   
BEGIN   
    
    IF(pshowchildindent) THEN 
      --get top level order   
      FOR _x IN
          SELECT wo_id,
                itemsite_id,
                item_type,
                wo_number,
                wo_subnumber,                             
                item_number,
                item_descrip1, 
                item_descrip2, 
                uom_name,
                qoh, 
                wobalance, 
                allocated, 
                ordered,                        
                reorderlevel,
                (qoh + ordered - wobalance) AS woavail,
                (qoh + ordered - allocated) AS totalavail 
         FROM(SELECT wo_id,
                itemsite_id,
                item_type,
                wo_number,
                wo_subnumber,                             
                item_number,
                item_descrip1, 
                item_descrip2, 
                uom_name,
                noNeg(qtyAvailable(itemsite_id)) AS qoh, 
                noNeg(wo_qtyord - wo_qtyrcv) AS wobalance, 
                qtyAllocated(itemsite_id, wo_duedate) AS allocated, 
                qtyOrdered(itemsite_id, wo_duedate) AS ordered,                        
                CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel 
          FROM wo, itemsite, item, uom     
         WHERE ((wo_id = pwoid)          
           AND (itemsite_id = wo_itemsite_id)
           AND (itemsite_item_id=item_id)
           AND (item_inv_uom_id=uom_id))               
         ORDER BY wo_number, wo_subnumber) AS data
       LOOP       
         _row.woinvav_itemsite_id := _x.itemsite_id;            
         _row.woinvav_womatl_id := -1;
         _row.woinvav_type := _x.item_type;          
         _row.woinvav_item_wo_number := _x.wo_number || '-' || _x.wo_subnumber;
         _row.woinvav_descrip := _x.item_descrip1 || ' ' || _x.item_descrip2;
         _row.woinvav_uomname := _x.uom_name;
         _row.woinvav_qoh := _x.qoh;
         _row.woinvav_balance := _x.wobalance;
         _row.woinvav_allocated := _x.allocated;     
         _row.woinvav_ordered := _x.ordered;         
         _row.woinvav_woavail := _x.woavail;
         _row.woinvav_totalavail := _x.totalavail;
         _row.woinvav_reorderlevel := _x.reorderlevel;
         _row.woinvav_level := 0;                       
         RETURN NEXT _row;                
        --get materials for this level        
        FOR _subx IN
          SELECT * FROM woinvavailmatl(_x.wo_id, 1, pshowshortage, pshowlowinventory) 
        LOOP                                            
	  RETURN NEXT _subx;
	END LOOP;  
	FOR _subx IN
          SELECT * FROM woinvavail(_x.wo_id, 1, pshowshortage, pshowlowinventory)
        LOOP                                            
	  RETURN NEXT _subx;
	END LOOP;  
     END LOOP;             
    ELSE
       SELECT wo_number FROM wo WHERE wo_id=pwoid LIMIT 1 INTO _wonumber;   
       --display a single level sum of work order requirements
       _qry := 'SELECT  wo_id,
                        itemsite_id, 
		        womatl_id,
			item_type,
			wo_number,                         
                        item_number, 
                        item_descrip1, 
                        item_descrip2, 
                        uom_name,                         
                        qoh, 
                        wobalance, 
                        allocated, 
                        ordered,                        
                        reorderlevel,
                        (qoh + ordered - wobalance) AS woavail,
                        (qoh + ordered - allocated) AS totalavail
	          FROM (SELECT wo_id,
	                itemsite_id, 
			womatl_id,
			item_type,			
			wo_number,                         
                        item_number, 
                        item_descrip1, 
                        item_descrip2, 
                        uom_name,                         
                        noNeg(qtyAvailable(itemsite_id)) AS qoh, 
                        noNeg(itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq - womatl_qtyiss)) AS wobalance, 
                        qtyAllocated(itemsite_id, womatl_duedate) AS allocated, 
                        qtyOrdered(itemsite_id, womatl_duedate) AS ordered,                        
                        CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel 
                   FROM wo, womatl, itemsite, item, uom  
                 WHERE (womatl_wo_id=wo_id) 
                   AND (womatl_itemsite_id=itemsite_id) 
                   AND (itemsite_item_id=item_id) 
                   AND (item_inv_uom_id=uom_id) ';
                IF(pshowchildsum) THEN
                  _qry := _qry || ' AND (wo_number=' || _wonumber || ')';
                ELSE  
                  _qry := _qry || ' AND (womatl_wo_id=' || pwoid || ')';                  
                END IF; 
                 _qry := _qry || ' ORDER BY item_number) AS data ';  
                IF(pshowshortage) THEN
                  _qry := _qry || ' WHERE (((qoh + ordered - allocated) < 0) OR ((qoh + ordered - wobalance) < 0)) '; 
                END IF;
                IF(pshowlowinventory AND NOT pshowshortage) THEN                 
                  _qry := _qry || ' WHERE (((qoh - allocated) < 0) OR ((qoh - wobalance) < 0)) '; 
                END IF;                 
                
      FOR _x IN   
         EXECUTE _qry
      LOOP        
        _row.woinvav_itemsite_id := _x.itemsite_id;            
        _row.woinvav_womatl_id := _x.womatl_id;  
        _row.woinvav_type := _x.item_type;      
        _row.woinvav_item_wo_number := _x.item_number;
        _row.woinvav_descrip := _x.item_descrip1 || ' ' || _x.item_descrip2;
        _row.woinvav_uomname := _x.uom_name;
        _row.woinvav_qoh := _x.qoh;
        _row.woinvav_balance := _x.wobalance;
        _row.woinvav_allocated := _x.allocated;     
        _row.woinvav_ordered := _x.ordered;         
        _row.woinvav_woavail := _x.woavail;
        _row.woinvav_totalavail := _x.totalavail;
        _row.woinvav_reorderlevel := _x.reorderlevel;
        _row.woinvav_level := 0;                
        RETURN NEXT _row;  
  END LOOP;
  END IF;                     
  RETURN;
END;
$_$;


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

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

CREATE FUNCTION woinvavailmatl(integer, integer, boolean, boolean) RETURNS SETOF woinvav
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;    
   plevel ALIAS FOR $2;
   pshowshortage ALIAS FOR $3;
   pshowlowinventory ALIAS FOR $4;
  _subrow woinvav%ROWTYPE;  
  _subx RECORD;
  _qry TEXT;
BEGIN
  
   _qry := 'SELECT itemsite_id, 
           womatl_id,
           item_type, 
           wo_number, 
           wo_subnumber, 
           womatl_ref, 
           womatl_notes, 
           item_number,
           item_descrip1, 
           item_descrip2, 
           uom_name,
           qoh, 
           wobalance, 
           allocated, 
           ordered,
           (qoh + ordered - wobalance) AS woavail,
           (qoh + ordered - allocated) AS totalavail,
           reorderlevel 
    FROM(SELECT itemsite_id, 
                womatl_id,
                item_type,                 
                wo_number, 
                wo_subnumber, 
                womatl_ref, 
                womatl_notes, 
                item_number,
                item_descrip1, 
                item_descrip2, 
                uom_name,
                noNeg(qtyAvailable(itemsite_id)) AS qoh, 
                noNeg(itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq - womatl_qtyiss)) AS wobalance, 
                qtyAllocated(itemsite_id, womatl_duedate) AS allocated, 
                qtyOrdered(itemsite_id, womatl_duedate) AS ordered,
                CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
    FROM womatl, wo, itemsite, item, uom
    WHERE ((wo_id = womatl_wo_id)     
     AND (womatl_itemsite_id = itemsite_id)
     AND (itemsite_item_id=item_id)
     AND (womatl_uom_id=uom_id)
     AND (NOT womatl_createwo OR womatl_createwo IS NULL))';
     _qry := _qry || ' AND (wo_id=' || pwoid || ') ORDER BY item_number) AS data';
     IF(pshowshortage) THEN
     _qry := _qry || ' WHERE (((qoh + ordered - allocated) < 0) OR ((qoh + ordered - wobalance) < 0)) '; 
     END IF;
     IF(pshowlowinventory AND NOT pshowshortage) THEN                 
     _qry := _qry || ' WHERE (((qoh - allocated) < 0) OR ((qoh - wobalance) < 0)) '; 
     END IF;
        
     
     
  FOR _subx IN
      EXECUTE _qry
  LOOP
     _subrow.woinvav_itemsite_id := _subx.itemsite_id;            
     _subrow.woinvav_womatl_id := _subx.womatl_id;
     _subrow.woinvav_type := _subx.item_type;            
     _subrow.woinvav_item_wo_number := _subx.item_number;
     _subrow.woinvav_descrip := _subx.item_descrip1 || ' ' || _subx.item_descrip2;
     _subrow.woinvav_uomname := _subx.uom_name;
     _subrow.woinvav_qoh := _subx.qoh;
     _subrow.woinvav_balance := _subx.wobalance;
     _subrow.woinvav_allocated := _subx.allocated;     
     _subrow.woinvav_ordered := _subx.ordered;         
     _subrow.woinvav_woavail := _subx.woavail;
     _subrow.woinvav_totalavail := _subx.totalavail;
     _subrow.woinvav_reorderlevel := _subx.reorderlevel;
     _subrow.woinvav_level := plevel;                                     
    RETURN NEXT _subrow; 
  END LOOP;            
  RETURN;
END;
$_$;


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

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

CREATE FUNCTION wostarted(pwoid 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
  _result BOOLEAN := FALSE;
   
BEGIN   
  -- is it really this simple?
  SELECT (wo_wipvalue > 0) INTO _result
    FROM wo
   WHERE wo_id=pWoid;
  
  RETURN COALESCE(_result, FALSE);
END;
$$;


ALTER FUNCTION public.wostarted(pwoid integer) OWNER TO admin;


--
-- Name: calcrate(numeric, character); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION calcrate(numeric, character) 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
pAmount ALIAS FOR $1;
pPeriod ALIAS FOR $2;
_state integer;
_count integer;

BEGIN
  -- Convert amount to hourly rate
  IF (pPeriod = 'H') THEN  -- hourly
    RETURN round(pAmount,2);
  ELSIF (pPeriod = 'D') THEN -- daily
    RETURN round(pAmount / 8, 2);
  ELSIF (pPeriod = 'W') THEN  -- weekly
    RETURN round(pAmount / 40, 2);
  ELSIF (pPeriod = 'BW') THEN  -- bi-weekly
    RETURN round(pAmount / 80, 2);
  ELSIF (pPeriod = 'M') THEN -- monthly
    RETURN round(pAmount / 160, 2);
  ELSIF (pPeriod = 'Y') THEN -- annually 
    RETURN round(pAmount / 2080, 2);
  ELSE
    RAISE EXCEPTION 'Unknown period type passed: %', pPeriod;
  END IF;

END;
$_$;


ALTER FUNCTION te.calcrate(numeric, character) OWNER TO admin;

--
-- Name: copyitem(integer, text); Type: FUNCTION; Schema: te; Owner: admin
--

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

BEGIN
  _itemid := public.copyItem(pSItemid, pTItemNumber);

  INSERT INTO te.teexp
  SELECT _itemid, teexp_expcat_id, teexp_accnt_id
  FROM te.teexp src
  WHERE (src.teexp_id=pSItemid);

  RETURN _itemid;
END;
$_$;


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

--
-- Name: invoicesheets(integer[]); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION invoicesheets(pheadids integer[]) RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.

BEGIN
  RETURN te.invoicesheets(pHeadIDs, CURRENT_DATE);
END;
$$;


ALTER FUNCTION te.invoicesheets(pheadids integer[]) OWNER TO admin;

--
-- Name: invoicesheets(integer[], date); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION invoicesheets(pheadids integer[], pinvcdate date) RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2012 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_invcnum text;
_invcheadid integer;
_invcitemid integer;
_s record;
_t record;
_linenum integer;

BEGIN
       -- Loop through time sheet items with matching criteria and make invoices
       FOR _s in SELECT DISTINCT 
                   teitem_cust_id, 
                   teitem_po, 
                   prj_id, 
                   teitem_curr_id
       FROM te.tehead 
         JOIN te.teitem ON (teitem_tehead_id=tehead_id AND teitem_billable)
         JOIN prjtask ON (teitem_prjtask_id=prjtask_id)
         JOIN prj ON (prjtask_prj_id=prj_id)
       WHERE ((tehead_id IN (SELECT * FROM te.unnest(pHeadIDs) ) )
        AND (teitem_billable)
        AND (teitem_invcitem_id IS NULL))

       -- loop thru records and create invoices by customer, by PO for the provided headid
       LOOP
         --select nextval('invchead_invchead_id_seq') into _invcid;
         _invcnum := CAST(fetchInvcNumber() AS TEXT);
         _invcheadid := nextval('invchead_invchead_id_seq');
         _linenum := 1;

         INSERT INTO invchead
               (invchead_id, invchead_cust_id, invchead_shipto_id,
                invchead_ordernumber, invchead_orderdate, invchead_posted,
                invchead_printed, invchead_invcnumber, invchead_invcdate,
                invchead_shipdate, invchead_ponumber, invchead_shipvia,
                invchead_fob, invchead_billto_name, invchead_billto_address1,
                invchead_billto_address2, invchead_billto_address3, invchead_billto_city,
                invchead_billto_state, invchead_billto_zipcode, invchead_billto_phone,
                invchead_shipto_name, invchead_shipto_address1, invchead_shipto_address2,
                invchead_shipto_address3, invchead_shipto_city, invchead_shipto_state,
                invchead_shipto_zipcode, invchead_shipto_phone, invchead_salesrep_id,
                invchead_commission, invchead_terms_id, invchead_freight,
                invchead_misc_amount, invchead_misc_descrip, invchead_misc_accnt_id,
                invchead_payment, invchead_paymentref, invchead_notes,
                invchead_billto_country, invchead_shipto_country, invchead_prj_id,
                invchead_curr_id, invchead_gldistdate, invchead_recurring,
                invchead_recurring_interval, invchead_recurring_type, invchead_recurring_until,
                invchead_recurring_invchead_id, invchead_shipchrg_id, invchead_taxzone_id,
                invchead_void, invchead_saletype_id, invchead_shipzone_id)
         SELECT _invcheadid, cust_id, -1,
                '', current_date, false,
                false, _invcnum, COALESCE(pInvcdate, current_date),
                current_date, _s.teitem_po, '',
                '', cust_name, COALESCE(addr_line1,''),
                COALESCE(addr_line2,''), COALESCE(addr_line3,''), COALESCE(addr_city,''),
                COALESCE(addr_state,''), COALESCE(addr_postalcode,''), cntct_phone, 
                '', '', '',
                '', '', '',
                '', '', cust_salesrep_id,
                salesrep_commission, cust_terms_id, 0,
                0, '', -1,
                0, '', '',
                COALESCE(addr_country,''), '', _s.prj_id, 
                _s.teitem_curr_id, current_date, false,
                null, null, null,
                null, null, cust_taxzone_id,
                false, null, null
         FROM custinfo
           JOIN salesrep ON (cust_salesrep_id=salesrep_id)
           LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
           LEFT OUTER JOIN addr ON (cntct_addr_id=addr_id)
         WHERE (cust_id=_s.teitem_cust_id);

          -- loop thru all lines of the sheet
          FOR _t IN SELECT 
               teitem_id,
               teitem_linenumber, 
               tehead_warehous_id,
               teitem_type,
               tehead_emp_id,
               cust_taxzone_id,
               item_number,
               teitem_cust_id,
               teitem_po,
               teitem_item_id,
               teitem_qty,
               teitem_uom_id,
               teitem_rate,
               teitem_notes
             FROM te.teitem
               JOIN te.tehead ON (teitem_tehead_id = tehead_id)
               JOIN custinfo ON (cust_id = teitem_cust_id)
               JOIN item ON (item_id = teitem_item_id)
               JOIN prjtask ON (teitem_prjtask_id=prjtask_id)
               JOIN prj ON (prjtask_prj_id=prj_id)
             WHERE ((tehead_id IN (SELECT * FROM te.unnest(pHeadIDs) ) )
              AND (teitem_billable)
              AND (teitem_invcitem_id IS NULL)
              AND (item_id = teitem_item_id)
              AND (teitem_cust_id = _s.teitem_cust_id)
              AND (teitem_po = _s.teitem_po)
              AND (prj_id = _s.prj_id)
              AND (teitem_curr_id = _s.teitem_curr_id))
             ORDER BY teitem_linenumber
          LOOP
            _invcitemid := nextval('invcitem_invcitem_id_seq');

            INSERT INTO invcitem
                  (invcitem_id, invcitem_invchead_id, invcitem_linenumber,
                   invcitem_item_id, invcitem_warehous_id, invcitem_custpn,
                   invcitem_number, invcitem_descrip, invcitem_ordered,
                   invcitem_billed, invcitem_custprice, invcitem_price,
                   invcitem_notes, invcitem_salescat_id, invcitem_taxtype_id,
                   invcitem_qty_uom_id, invcitem_qty_invuomratio,
                   invcitem_price_uom_id, invcitem_price_invuomratio,
                   invcitem_coitem_id, invcitem_updateinv, invcitem_rev_accnt_id)
            SELECT _invcitemid, _invcheadid, _linenum,
                   _t.teitem_item_id, _t.tehead_warehous_id, '',
                   '', '', _t.teitem_qty,
                   _t.teitem_qty, _t.teitem_rate, _t.teitem_rate,
                   _t.teitem_notes, -1, getItemTaxType(item_id, _t.cust_taxzone_id), 
                   _t.teitem_uom_id, itemuomtouomratio(item_id, _t.teitem_uom_id, item_inv_uom_id),
                   _t.teitem_uom_id, itemuomtouomratio(item_id, _t.teitem_uom_id, item_inv_uom_id),
                   null, false, null
            FROM item
            WHERE (item_id=_t.teitem_item_id);

            _linenum := _linenum + 1;
            
            -- Update the time sheet item record
            UPDATE te.teitem SET teitem_invcitem_id = _invcitemid WHERE (teitem_id = _t.teitem_id);
            
          END LOOP;
       END LOOP;

RETURN 1;
END;
$$;


ALTER FUNCTION te.invoicesheets(pheadids integer[], pinvcdate date) OWNER TO admin;

--
-- Name: postsheet(integer, text, text); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION postsheet(integer, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
pTeheadId ALIAS FOR $1;
pPhrase1 ALIAS FOR $2;
pPhrase2 ALIAS FOR $3; 
_r record;
_notes TEXT;
_value NUMERIC;
_olaccntid INTEGER;
_expaccntid INTEGER;
_count INTEGER;

BEGIN
  -- Validate: No posting for contractors
  IF (SELECT (count(teemp_id) > 0) 
      FROM te.tehead
        JOIN te.teemp ON (tehead_emp_id=teemp_emp_id)
      WHERE ((tehead_id=pTeheadId)
        AND (teemp_contractor))) THEN
    RAISE EXCEPTION 'Time and Expense Sheets can not be posted for contractors.  Voucher instead.';
  END IF;
  
  -- Get labor and overhead account
  SELECT accnt_id INTO _olaccntid
  FROM accnt
  WHERE (accnt_id=fetchmetricvalue('PrjLaborAndOverhead'));

  GET DIAGNOSTICS _count = ROW_COUNT;
  IF (_count = 0) THEN
    RAISE EXCEPTION 'No valid Project Labor and Overhead Account Defined';
  END IF;

  -- Get applicable time sheets
  FOR _r IN 
    SELECT tehead_number,
      teitem_id, teitem_linenumber, teitem_type, teitem_notes,
      item_descrip1, teitem_qty,
      teexp_expcat_id, teexp_accnt_id, 
      emp_code, emp_wage, emp_wage_period,
      prj_id, prj_number
    FROM te.tehead
     JOIN te.teitem ON (teitem_tehead_id=tehead_id)
     JOIN item ON (teitem_item_id=item_id)
     JOIN te.teexp ON (teitem_item_id=teexp_id)
     JOIN emp ON (tehead_emp_id=emp_id)
     JOIN prjtask ON (prjtask_id=teitem_prjtask_id)
     JOIN prj ON (prj_id=prjtask_prj_id)
    WHERE ((tehead_id = pTeheadId)
     AND (NOT teitem_posted)
     AND (teitem_vodist_id IS NULL)
     AND (teitem_type = 'T'))

  LOOP  
    -- Determine value
    _value := te.calcRate(_r.emp_wage, _r.emp_wage_period) * _r.teitem_qty;

    -- Determine G/L account to post to
    IF (_r.teexp_accnt_id > 1) THEN
      _expaccntid := getPrjAccntId(_r.prj_id, _r.teexp_accnt_id);
    ELSE
      SELECT getPrjAccntId(_r.prj_id, expcat_exp_accnt_id) INTO _expaccntid
      FROM expcat
      WHERE (expcat_id=_r.teexp_expcat_id);
    END IF;

    -- Execute the posting
    _notes := (pPhrase1 || _r.item_descrip1 || '/' || _r.emp_code || pPhrase2 || ' ' || _r.prj_number);
    PERFORM insertGLTransaction( 'T/E', 'TE', _r.tehead_number, _notes,
                                 _olaccntid, _expaccntid, -1,
                                 _value, current_date );

    -- Update the time sheet item
    UPDATE te.teitem SET 
      teitem_posted = true,
      teitem_postedvalue = teitem_postedvalue + _value
    WHERE (teitem_id=_r.teitem_id);
                        
  END LOOP;
          
RETURN 1;
END;
$_$;


ALTER FUNCTION te.postsheet(integer, text, text) OWNER TO admin;

--
-- Name: sheetstate(integer, character); Type: FUNCTION; Schema: te; Owner: admin
--

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

BEGIN
  -- Check and return the process state of the sheet
  --    1 = All processed
  --    0 = Processing required
  --   -1 = Not Applicable

  IF (pType = 'I') THEN
    SELECT MIN(CASE teitem_invcitem_id IS NULL WHEN TRUE THEN 0 ELSE 1 END) INTO _state
      FROM te.teitem
     WHERE ((teitem_tehead_id=pTeheadId)
        AND (teitem_billable)
        AND (teitem_qty >= 0));

  ELSIF (pType = 'V') THEN
    -- todo: why outer join then check teitem_type and vend_id is not null?
    SELECT MIN(CASE teitem_vodist_id IS NULL WHEN TRUE THEN 0 ELSE 1 END) INTO _state
      FROM te.tehead
        JOIN emp ON (tehead_emp_id=emp_id)
        LEFT OUTER JOIN te.teemp ON (emp_id=teemp_emp_id)
        LEFT OUTER JOIN te.teitem ON (teitem_tehead_id=tehead_id)
        LEFT OUTER JOIN vendinfo ON (UPPER(emp_code)=UPPER(vend_number))
      WHERE ((teitem_tehead_id=pTeheadId)
         AND ((teitem_type = 'E' AND NOT teitem_prepaid) 
           OR (teitem_type = 'T' AND COALESCE(teemp_contractor,false)))
         AND (vend_id IS NOT NULL)
         AND (teitem_qty > 0));

  ELSIF (pType = 'P') THEN
    SELECT MIN(CASE teitem_posted WHEN FALSE THEN 0 ELSE 1 END) INTO _state
      FROM te.teitem
      JOIN te.tehead ON (teitem_tehead_id=tehead_id)
      LEFT JOIN te.teemp ON (tehead_emp_id=teemp_emp_id)
     WHERE ((teitem_tehead_id=pTeheadId)
        AND (teitem_type = 'T')
        AND (teemp_contractor IS NULL or teemp_contractor = false));

  ELSE
    -- TODO: either make ErrorReporter::error find this or use xtuple
    RAISE EXCEPTION 'Unknown process type % [xtte: sheetstate, -2, %]',
                    pType, pType;
  END IF;

  RETURN _state;

END;
$_$;


ALTER FUNCTION te.sheetstate(integer, character) OWNER TO admin;

--
-- Name: triggertehead(); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION triggertehead() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_r RECORD;
_sense INTEGER := 0;

BEGIN

  -- Determine whether we are adding or subtracting totals
  IF (TG_OP = 'UPDATE') THEN
    IF (OLD.tehead_status = 'O' AND NEW.tehead_status = 'A') THEN
    -- Approving so add
      _sense := 1;
    ELSIF  (OLD.tehead_status = 'A' AND NEW.tehead_status = 'O') THEN
    -- Unapproving so subtract
      _sense := -1;
    END IF;
  END IF;

  IF (_sense != 0) THEN
    -- Loop thru all lines of the sheet and update project
    FOR _r in 
      SELECT teitem_prjtask_id, teitem_type, teitem_qty, teitem_total 
      FROM te.teitem
      WHERE teitem_tehead_id = NEW.tehead_id

    LOOP
      IF (_r.teitem_type = 'T') THEN
        UPDATE prjtask SET 
          prjtask_hours_actual = prjtask_hours_actual + _r.teitem_qty * _sense
        WHERE prjtask_id = _r.teitem_prjtask_id;
      ELSE
        UPDATE prjtask SET 
          prjtask_exp_actual = prjtask_exp_actual + _r.teitem_total * _sense
        WHERE prjtask_id = _r.teitem_prjtask_id;
      END IF;
            
    END LOOP;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION te.triggertehead() OWNER TO admin;

--
-- Name: triggerteitem(); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION triggerteitem() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_r RECORD;
_status CHAR(1) := 'O';

BEGIN
  -- Validate whether we can take this action
  IF (TG_OP = 'UPDATE') THEN
    IF ((OLD.teitem_type != NEW.teitem_type)
      OR (OLD.teitem_workdate != NEW.teitem_workdate)
      OR (OLD.teitem_cust_id != NEW.teitem_cust_id)
      OR (OLD.teitem_po != NEW.teitem_po)
      OR (OLD.teitem_item_id != NEW.teitem_item_id)
      OR (OLD.teitem_qty != NEW.teitem_qty)
      OR (OLD.teitem_rate != NEW.teitem_rate)
      OR (OLD.teitem_total != NEW.teitem_total)
      OR (OLD.teitem_billable != NEW.teitem_billable)
      OR (OLD.teitem_prepaid != NEW.teitem_prepaid)
      OR (OLD.teitem_notes != NEW.teitem_notes)) THEN
      
      SELECT tehead_status INTO _status FROM te.tehead WHERE tehead_id=NEW.teitem_tehead_id;
    END IF;
  ELSIF (TG_OP = 'INSERT') THEN
    SELECT tehead_status INTO _status FROM te.tehead WHERE tehead_id=NEW.teitem_tehead_id;
  ELSE -- Must be delete
    SELECT tehead_status INTO _status FROM te.tehead WHERE tehead_id=OLD.teitem_tehead_id;
  END IF;

  IF (_status != 'O') THEN
    RAISE EXCEPTION 'Time and Expense Sheets may only be edited or deleted when the status is Open';
  END IF;

  _status := 'C';
  
  -- Update header status, default is to close if all processing complete
  IF (TG_OP = 'UPDATE') THEN
    IF ((COALESCE(OLD.teitem_invcitem_id,-1) != COALESCE(NEW.teitem_invcitem_id,-1))
      OR (COALESCE(OLD.teitem_vodist_id,-1) != COALESCE(NEW.teitem_vodist_id,-1))
      OR (OLD.teitem_posted != NEW.teitem_posted)) THEN

      SELECT 
        te.sheetstate(NEW.teitem_tehead_id, 'I') AS invoiced,
        te.sheetstate(NEW.teitem_tehead_id, 'V') AS vouchered,
        te.sheetstate(NEW.teitem_tehead_id, 'P') AS posted
      INTO _r;

      IF (_r.invoiced = 0 OR _r.vouchered = 0 OR _r.posted = 0) THEN
        _status := 'A'; -- Something is still open, so approved
      END IF;
    
      UPDATE te.tehead SET tehead_status = _status WHERE (tehead_id=NEW.teitem_tehead_id);
    END IF;
  END IF;

  -- Update header with last use info
  IF (TG_OP = 'DELETE') THEN
    UPDATE te.tehead SET
      tehead_lastupdated=('now'::text)::timestamp(6) with time zone
    WHERE (tehead_id=OLD.teitem_tehead_id);
  ELSE
    UPDATE te.tehead SET
      tehead_lastupdated=('now'::text)::timestamp(6) with time zone,
      tehead_username=getEffectiveXtUser()
    WHERE (tehead_id=NEW.teitem_tehead_id);
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION te.triggerteitem() OWNER TO admin;

--
-- Name: triggerteprj(); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION triggerteprj() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _update BOOLEAN := false;
BEGIN

  IF (TG_OP = 'INSERT') THEN
    _update = true;
  ELSIF (TG_OP = 'UPDATE') THEN
    IF (COALESCE(OLD.teprj_cust_id,-1) != COALESCE(NEW.teprj_cust_id,-1)) THEN
      _update = true;
    END IF;
  END IF;

  IF (_update) THEN
      UPDATE te.teprjtask SET teprjtask_cust_id=NEW.teprj_cust_id
      FROM prjtask
      WHERE ((teprjtask_prjtask_id=prjtask_id)
      AND (prjtask_prj_id=NEW.teprj_prj_id));
  END IF;
  
  RETURN NEW;
END;
$$;


ALTER FUNCTION te.triggerteprj() OWNER TO admin;

--
-- Name: unnest(anyarray); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION unnest(anyarray) RETURNS SETOF anyelement
    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 $1[i] FROM
    generate_series(array_lower($1,1),
                    array_upper($1,1)) i;
$_$;


ALTER FUNCTION te.unnest(anyarray) OWNER TO admin;

--
-- Name: vouchersheet(integer); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION vouchersheet(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
  pHeadID ALIAS FOR $1;
  _distamt  NUMERIC;
  _glaccnt  INTEGER;
  _notes    TEXT;
  _s        RECORD;
  _total    NUMERIC := 0;
  _v        RECORD;
  _vodistid INTEGER;
  _voheadid INTEGER;

BEGIN
  FOR _v IN
    -- distinct filters duplicate rows returned because of the teitem join
    -- TODO: can we push the teitem down into the loop and avoid the distinct?
    SELECT DISTINCT
           tehead_id, tehead_number,   tehead_weekending,          tehead_notes,
           teitem_curr_id,
           emp_wage, emp_wage_period,
           vend_id,   vend_taxzone_id, vend_terms_id, vend_number, vend_1099,
           COALESCE(teemp_contractor, false) AS isContractor
      FROM te.tehead
      JOIN te.teitem ON (teitem_tehead_id=tehead_id)
      JOIN emp       ON (tehead_emp_id=emp_id)
      JOIN vendinfo  ON (UPPER(emp_code)=UPPER(vend_number))
      LEFT OUTER JOIN te.teemp ON (emp_id=teemp_emp_id)
    WHERE ((tehead_id      = pHeadID)
       AND (teitem_prepaid = false)
       AND (teitem_vodist_id IS NULL)
       AND (teitem_type = 'E' OR (COALESCE(teemp_contractor,false) AND (teitem_empcost > 0 OR emp_wage > 0 ))))  LOOP

     INSERT INTO vohead (vohead_id,        vohead_number,     vohead_vend_id,
                         vohead_distdate,  vohead_docdate,
                         vohead_duedate,
                         vohead_terms_id,  vohead_taxzone_id, vohead_invcnumber,
                         vohead_reference, vohead_amount,     vohead_1099,
                         vohead_curr_id,   vohead_notes,      vohead_posted,
                         vohead_misc,      vohead_pohead_id)
                 VALUES (DEFAULT,              fetchVoNumber(),    _v.vend_id,
                         _v.tehead_weekending, _v.tehead_weekending,
                         determineDueDate(_v.vend_terms_id, _v.tehead_weekending),
                         _v.vend_terms_id,     _v.vend_taxzone_id, 'N/A',
                         ('T&E Sheet ' || _v.tehead_number),    0, _v.vend_1099,
                         _v.teitem_curr_id,    _v.tehead_notes, false,
                         true,                 -1)
     RETURNING vohead_id INTO _voheadid;

     FOR _s IN
       SELECT teitem_id,       teitem_linenumber, teitem_workdate, teitem_type,
              item_number,     teitem_item_id,    teitem_qty,      prjtask_prj_id,
              CASE
                WHEN teitem_empcost > 0 THEN teitem_empcost
                ELSE te.calcRate(_v.emp_wage, _v.emp_wage_period)
              END AS rate,
              teitem_total,    teitem_type, teitem_notes,
              teexp_expcat_id, teexp_accnt_id
         FROM te.teitem
         JOIN te.teexp ON (teitem_item_id=teexp_id)
         JOIN item     ON (teitem_item_id=item_id)
         JOIN prjtask  ON (teitem_prjtask_id=prjtask_id)
        WHERE ((teitem_tehead_id = _v.tehead_id)
           AND (teitem_curr_id   = _v.teitem_curr_id)
           AND (teitem_prepaid   = false)
           AND (teitem_vodist_id IS NULL)
           AND (teitem_type = 'E' OR (_v.isContractor AND (teitem_empcost > 0 OR _v.emp_wage > 0 )))) 


       -- Loop thru records and create vouchers by supplier for the provided headid
     LOOP
        -- insert vodist records here
        _vodistid = nextval('vodist_vodist_id_seq');

        -- Map expense directly to account so we can get project account mapping if applicable
        IF (_s.teexp_accnt_id > 1) THEN
          _glaccnt := getPrjAccntId(_s.prjtask_prj_id, _s.teexp_accnt_id);
        ELSE
          SELECT getPrjAccntId(_s.prjtask_prj_id, expcat_exp_accnt_id) INTO _glaccnt
            FROM expcat
           WHERE (expcat_id=_s.teexp_expcat_id);
        END IF;

        IF (_s.teitem_type = 'T') THEN -- Time sheet record
          _notes := formatdate(_s.teitem_workdate) || E'\t' || _s.item_number ||
                    E'\t' || formatQty(_s.teitem_qty) || ' hours' || E'\t';
          _distamt := _s.rate * _s.teitem_qty;
        ELSE -- Expense record
          _notes := formatdate(_s.teitem_workdate) || E'\t' || _s.item_number ||
                    E'\t' || _s.teitem_notes || E'\t';
          _distamt := _s.teitem_total;
        END IF;

        INSERT INTO vodist (vodist_id,          vodist_vohead_id, vodist_poitem_id,
                            vodist_costelem_id, vodist_accnt_id,  vodist_amount,
                            vodist_expcat_id,   vodist_notes)
                    VALUES (_vodistid, _voheadid, -1,
                            -1,        _glaccnt,  _distamt,
                            -1,        _notes);
        _total := _total + _distamt;

        -- Update the te.teitem record with the relationship
        UPDATE te.teitem SET teitem_vodist_id = _vodistid WHERE teitem_id = _s.teitem_id;
     END LOOP;

    UPDATE vohead SET vohead_amount = _total WHERE (vohead_id=_voheadid);
     _total := 0;

  END LOOP;

  RETURN 1;
END;
$_$;


ALTER FUNCTION te.vouchersheet(integer) OWNER TO admin;


--
-- Name: add_column(text, text, text, text, text, text); Type: FUNCTION; Schema: xt; Owner: admin
--

CREATE FUNCTION add_column(table_name text, column_name text, type_name text, constraint_text text DEFAULT NULL::text, schema_name text DEFAULT 'xt'::text, column_comment text DEFAULT NULL::text) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
declare
  count integer;
  query text;
  comment_query text;
begin

  perform *
  from pg_class c, pg_namespace n, pg_attribute a, pg_type t
  where c.relname = table_name
   and n.nspname = schema_name
   and a.attname = column_name
   and n.oid = c.relnamespace
   and a.attnum > 0
   and a.attrelid = c.oid
   and a.atttypid = t.oid;

  get diagnostics count = row_count;

  if (count > 0) then
    return false;
  end if;

  query = 'alter table ' || schema_name || '.' || table_name || ' add column ' || column_name || ' ' || type_name || ' ' || coalesce(constraint_text, '');

  execute query;

  if (column_comment is not null) then
    comment_query = 'comment on column ' || schema_name || '.' || table_name || '.' || column_name || ' is ' || quote_literal(column_comment);
    execute comment_query;
  end if;

  return true;

end;
$$;


ALTER FUNCTION xt.add_column(table_name text, column_name text, type_name text, constraint_text text, schema_name text, column_comment text) OWNER TO admin;

--
-- Name: add_comment_type(text, text, text); Type: FUNCTION; Schema: xt; Owner: admin
--

CREATE FUNCTION add_comment_type(text, text, text) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
   _module  ALIAS FOR $1;
   _comment ALIAS FOR $2;
   _descr   ALIAS FOR $3;
   _cmntid INTEGER;

BEGIN
  SELECT source_id INTO _cmntid
	FROM source WHERE source_name = _comment;

  IF (NOT FOUND) THEN
    INSERT INTO source (source_module, source_name, source_descrip)
      VALUES (_module, _comment, _descr) 
      RETURNING source_id INTO _cmntid;

  INSERT INTO cmnttypesource (cmnttypesource_cmnttype_id, cmnttypesource_source_id)
  	VALUES (1, _cmntid);  -- General Comments
  INSERT INTO cmnttypesource (cmnttypesource_cmnttype_id, cmnttypesource_source_id)
	  VALUES (2, _cmntid);  -- ChangeLog Comments
  
  END IF;

  RETURN true;

END;

$_$;


ALTER FUNCTION xt.add_comment_type(text, text, text) OWNER TO admin;

--
-- Name: add_constraint(text, text, text, text); Type: FUNCTION; Schema: xt; Owner: admin
--

CREATE FUNCTION add_constraint(table_name text, constraint_name text, constraint_text text, schema_name text DEFAULT 'xt'::text) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
declare
  count integer;
  query text;
begin

  perform *
  from pg_constraint
    join pg_namespace on (connamespace=pg_namespace.oid)
    join pg_class f on (conrelid=f.oid)
  where f.relname = table_name
    and nspname = schema_name
    and conname = constraint_name;
  
  get diagnostics count = row_count;
  
  if (count > 0) then
    return false;
  end if;

  query = 'alter table ' || schema_name || '.' || table_name || ' add constraint ' || constraint_name || ' ' || constraint_text || ';';
  execute query;

  return true;
  
end;
$$;


ALTER FUNCTION xt.add_constraint(table_name text, constraint_name text, constraint_text text, schema_name text) OWNER TO admin;

--
-- Name: add_index(text, text, text, text, text); Type: FUNCTION; Schema: xt; Owner: admin
--

CREATE FUNCTION add_index(table_name text, column_names text, index_name text, using_name text DEFAULT 'btree'::text, schema_name text DEFAULT 'xt'::text) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
declare
  count integer;
  query text;
begin

  perform *
  from pg_index
    join pg_class s on (indexrelid=s.oid)
    join pg_class f on (indrelid=f.oid)
    join pg_namespace on (f.relnamespace=pg_namespace.oid)
  where f.relname = table_name
    and nspname = schema_name
    and s.relname = index_name;
  
  get diagnostics count = row_count;
  
  if (count > 0) then
    return false;
  end if;

  query = 'create index  ' || index_name || ' on ' || schema_name || '.' || table_name || ' using ' || using_name || '(' || column_names || ');';
  execute query;

  return true;
  
end;
$$;


ALTER FUNCTION xt.add_index(table_name text, column_names text, index_name text, using_name text, schema_name text) OWNER TO admin;

--
-- Name: add_primary_key(text, text, text); Type: FUNCTION; Schema: xt; Owner: admin
--

CREATE FUNCTION add_primary_key(table_name text, column_name text, schema_name text DEFAULT 'xt'::text) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
declare
  count integer;
  query text;
begin

  perform *
  from pg_constraint
    join pg_namespace on (connamespace=pg_namespace.oid)
    join pg_class f on (conrelid=f.oid)
  where f.relname = table_name
    and nspname = schema_name
    and contype = 'p';
  
  get diagnostics count = row_count;
  
  if (count > 0) then
    return false;
  end if;

  query = 'alter table ' || schema_name || '.' || table_name || ' add primary key (' || column_name || ');';
  execute query;

  return true;
  
end;
$$;


ALTER FUNCTION xt.add_primary_key(table_name text, column_name text, schema_name text) OWNER TO admin;

--
-- Name: create_table(text, text, boolean, text); Type: FUNCTION; Schema: xt; Owner: admin
--

CREATE FUNCTION create_table(table_name text, schema_name text DEFAULT 'xt'::text, with_oids boolean DEFAULT false, inherit_table text DEFAULT NULL::text) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
declare
  count integer;
  query text;
  with_clause text := '';
  inherit_from text := '';
begin

  perform *
  from pg_tables 
  where schemaname = schema_name and tablename = table_name;
  
  get diagnostics count = row_count;
  
  if (count > 0) then
    return false;
  end if;

  if (with_oids) then
    with_clause := 'with (OIDS=TRUE)';
  end if;

  if (inherit_table is not null) then
    inherit_from := ' INHERITS (' || inherit_table || ') ';
  end if;

  query = 'create table ' || schema_name || '.' || table_name || '()' || inherit_from || with_clause || '; 
           grant all on ' || schema_name || '.' || table_name || ' to xtrole;';
  execute query;

  return true;
  
end;
$$;


ALTER FUNCTION xt.create_table(table_name text, schema_name text, with_oids boolean, inherit_table text) OWNER TO admin;

--
-- Name: install_js(text, text, text, text, boolean); Type: FUNCTION; Schema: xt; Owner: admin
--

CREATE FUNCTION install_js(name_space text, type_name text, context text, javascript text, is_extension boolean DEFAULT false) RETURNS void
    LANGUAGE plv8
    AS $_$

  if(!name_space) throw new Error("A name space is required");
  if(!type_name) throw new Error("A type is required");
  if(!context) throw new Error("A context is required");

  sql = 'select js_id as "id", '
      + ' js_ext as "isExtension" '
      + 'from xt.js '
      + 'where js_namespace = $1 '
      + ' and js_type = $2 '
      + ' and js_context = $3';

  js = plv8.execute(sql, [name_space, type_name, context])[0];

  if(js) {
    if(js.isExtension !== is_extension) throw new Error("Can not change extension state for {namespace}.{type} in context {context}."
                                                        .replace(/{namespace}/, name_space)
                                                        .replace(/{type}/, type_name)
                                                        .replace(/{context}/, context));

    sql = 'update xt.js set '
        + ' js_text = $1 '
        + 'where js_id = $2';

    plv8.execute(sql, [javascript, js.id]);
  } else {
    sql = 'insert into xt.js ( js_namespace, js_type, js_context, js_text, js_ext ) values ($1, $2, $3, $4, $5)';

    plv8.execute(sql, [name_space, type_name, context, javascript, is_extension]);
  }

  // Reinitialize our plv8 code now that is has changed.
  plv8.execute("SELECT xt.js_init(false, true)");

$_$;


ALTER FUNCTION xt.install_js(name_space text, type_name text, context text, javascript text, is_extension boolean) OWNER TO admin;

--
-- Name: js_init(boolean, boolean); Type: FUNCTION; Schema: xt; Owner: admin
--

CREATE FUNCTION js_init(debug boolean DEFAULT false, initialize boolean DEFAULT false) RETURNS void
    LANGUAGE plv8
    AS $_$

return (function () {

  if (plv8.XT && !initialize && !debug) {
    return;
  }

  DEBUG = debug ? debug : false;

  if (plv8.version < '1.3.0'){
    plv8.elog(ERROR, 'plv8 version 1.3.0 or greater required. This version is = ', plv8.version);
  }


  // ..........................................................
  // METHODS
  //

  /**
    Return whether an array contains an item.

    @param {Any}
    @returns Boolean
  */
  Array.prototype.contains = function (item) {
    var i = this.length;
    while (i--) {
      if (this[i] === item) {
        return true;
      }
    }
    return false;
  }

  /**
    Return the index of an item in an array

    @param {Any}
    @returns Any
  */
  Array.prototype.indexOf = function (item) {
    var i = this.length;
    while (i--) {
      if (this[i] === item) {
        return i;
      }
    }
    return -1;
  }

  /**
    Remove an item from an array and return it.

    @param {Any}
    @returns Any
  */
  Array.prototype.remove = function (item) {
    return this.contains(item) ? this.splice(this.indexOf(item), 1) : false;
  }

  /**
     Returns an the first item in an array with a property matching the passed value.

     @param {String} property name to search on
     @param {Any} value to search for
     @param Object item found or null
  */
  Array.prototype.findProperty = function (key, value) {
    for (var i = 0; i < this.length; i++) {
      for (var prop in this[i]) {
        if (prop === key &&
            this[i][prop] === value) {
          return this[i];
        }
      }
    }
    return false;
  }

  /**
    Remove duplicates from an array.

    @returns Array with no duplicates.
  */
  /* TODO: Or add underscore.js support. */
  Array.prototype.unique = function () {
    var a = this.concat();
    for(var i=0; i<a.length; ++i) {
      for(var j=i+1; j<a.length; ++j) {
        if (typeof a[i] === 'string' && typeof a[j] === 'string') {
          if(a[i] === a[j]) {
            a.splice(j--, 1);
          }
        } else if (typeof a[i] === 'object' && typeof a[j] === 'object') {
          if(JSON.stringify(a[i]) === JSON.stringify(a[j])) {
            a.splice(j--, 1);
          }
        }
      }
    }

    return a;
  }

  /**
    Curry function
  */
  Function.prototype.curry = function () {
    if (arguments.length < 1) {
        return this; /* nothing to curry with - return function */
    }

    var __method = this,
      args = arguments[0];

    return function () {
      return __method.apply(this, args.concat(Array.prototype.slice.call(arguments)));
    }
  }


  /* TODO: How can we send back a JSON object with error message and more info like 'foocol is required'. */
  handleError = function (message, code) {
    var err = new Error();
    this.stack = err.stack;
    this.name = "handleError";
    this.message = (message || "");
    this.code = code || null;
  }
  handleError.prototype = new Error();

  /**
    Return the text after the first dot.
  */
  String.prototype.afterDot = function () {
    return this.replace(/\w+\./i, '');
  }

  /**
    Return the text before the first dot.
  */
  String.prototype.beforeDot = function () {
    return this.replace(/\.\w+/i, '');
  }

  /**
     Trim whitespace from a string.
  */
  String.prototype.trim = function () {
    return this.replace(/^\s+|\s+$/g,"");
  }

  /**
     Change sting with underscores '_' to camel case.

     @returns {String}
  */
  String.prototype.camelize = function () {
    var self = this,
        ret = self.replace( (/([\s|\-|\_|\n])([^\s|\-|\_|\n]?)/g), function(self, separater, character) {
          return character ? character.toUpperCase() : '';
    });
    var first = ret.charAt(0),
      lower = first.toLowerCase();
    return first !== lower ? lower + ret.slice(1) : ret;
  };

  /**
     Change a camel case string to snake case.

     @returns {String} The argument modified
  */
  String.prototype.camelToHyphen = function () {
    return this.replace((/([a-z])([A-Z])/g), '$1-$2').toLowerCase();
  }

  /**
     Converts the string into a class name. This method will camelize
     your string and then capitalize the first letter.

     @returns {String}
  */
  String.prototype.classify = function () {
    return this.slice(0, 1).toUpperCase() + this.slice(1).camelize();
  }

  /**
     Change a camel case string to snake case.

     @returns {String} The argument modified
  */
  String.prototype.decamelize = function () {
    return this.replace((/([a-z])([A-Z])/g), '$1_$2').toLowerCase();
  }

  /**
     Change a camelCase or ClassName string to capitalize the first letter and
     add space.

     @returns {String} The argument modified
  */
  String.prototype.humanize = function () {
    var spaced = this.replace((/([a-z])([A-Z])/g), '$1 $2');
    return human = spaced.charAt(0).toUpperCase() + spaced.slice(1);
  }

  /**
    Sets date to midnight of the current day.

    @returns Receiver
  */
  Date.prototype.toMidnight = function () {
    this.setHours(0);
    this.setMinutes(0);
    this.setSeconds(0);
    this.setMilliseconds(0);
    return this;
  }

  // ..........................................................
  // XT
  //

  plv8.XT = XT = {};

  /**
     Change properties names on an object with underscores '_' to camel case.
     Only changes immediate properties, it is not recursive.

     @param {Object | String}
     @returns {Object | String}
  */
  XT.camelize = function(obj) {
    var ret = {};
    if(typeof obj === "object") {
      for(var prop in obj) {
        for(var prop in obj) ret[prop.camelize()] = obj[prop];
      }
    }
    else if(typeof obj === "string") return obj.camelize();
    return ret;
  }

  /**
    Change camel case property names in an object to hyphen case.
     Only changes immediate properties, it is not recursive.

     @param {Object | String} The object to decamelize
     @returns {Object | String} The argument modified
  */
  XT.camelToHyphen = function(obj) {
    var ret = {};
    if(typeof obj === "object") {
      for(var prop in obj) ret[prop.camelToHyphen()] = obj[prop];
    }
    else if(typeof obj === "string") return obj.camelToHyphen();
    return ret;
  }

  /**
   * Get current_database().
   */
  XT.currentDb = function() {
    return plv8.execute("select current_database() as db;")[0].db;
  }

  /**
   * Wrap plv8's elog DEBUG1.
   *
   * For debug messages to show up, you need to set your postgresql.conf to:
   * client_min_messages = debug1
   *
   * @param {String} Debug message of where you are at, what you are doing or what's in args.
   * @param {Object|Array} The data you want logged.
   */
  XT.debug = function(msg, args) {
    var message = '';

    msg = typeof msg === 'string' ? msg || 'debug data = ' : 'debug data = ';
    args = args || null;

    if (args) {
      message = JSON.stringify(args, null, 2);
    }

    // TODO, this could be changed to "LOG" and you would need to set: client_min_messages = log
    // Then you would not get any of the "RAISE DEBUG;" messages from the PL/pgSQL code.
    /* Do a hard trim to 900 so something prints. */
    plv8.elog(DEBUG1, (msg + message).substring(0, 900));
  }

  /**
    Change camel case property names in an object to snake case.
     Only changes immediate properties, it is not recursive.

     @param {Object | String} The object to decamelize
     @returns {Object | String} The argument modified
  */
  XT.decamelize = function(obj) {
    var ret = {};
    if(typeof obj === "object") {
      for(var prop in obj) ret[prop.decamelize()] = obj[prop];
    }
    else if(typeof obj === "string") return obj.decamelize();
    return ret;
  }

  /*
    Mappings of document association sourceTypes to business objects.
    Extensible by extensions.
    https://github.com/xtuple/xtuple/pull/1964

    The following keys are already used by the core application:

    "C" = Customer
    "CRMA" = CRM Account
    "FILE" = File
    "I" = Item
    "INCDT" = Incident
    "INV" = Invoice
    "J" = Project
    "Qquhead_id" = Quote. Yes really.
    "S" = Sales Order
    "T" = Contact
    "URL" = Url

    If you're adding your own, choose a generous namespace and object name.
    The string is stored in postgres as a text field, so you've got plenty of characters.

  */

  XT.documentAssociations = {
    FILE: "FileRelation",
    I: "ItemRelation",
    URL: "Url"
  };


  /**
    Returns today's date at midnight.
    returns {Date}
  */
  XT.today = function () {
    var today = new Date();
    return today.toMidnight();
  }

  /**
   * Wrap plv8's elog ERROR to include a stack trace and function arguments when debugging.
   *
   * @param {Object} The caught error object from a try/catch.
   * @param {Array} Javascript's arguments array for the function throwing the error.
   * @param {Boolean|String} Set flag to indicate the error was handled.
   */
  XT.error = function (error) {
    /* Make sure XT.username gets unset on errors. */
    XT.username = undefined;

    var message = error.stack + "\n";

    if (error.name === "handleError") {
      /* This error was handled and a message sent to the client. Those massages are*/
      /* generic HTTP codes. Send the stack trace with detailed info on what happened. */
      XT.debug(message);
      XT.message(error.code, error.message.substring(0, 900));
      throw "handledError";
    } else {
      /* Some times the stack trace can eat up the full 1000 char message. */
      /* Do a hard trim to 900 so something prints. */
      XT.message(500, "Internal Server Error");
      plv8.elog(WARNING, message.substring(0, 900));
      throw "unhandledError";
    }
  }


  /*

    Start supporting functions for XT.executeFunction.

  */

  var errorMaps = [
    {"fromFunction":"closeAccountingYearPeriod","fromId":-1,"toFunction":"closeAccountingPeriod","toId":-1},
    {"fromFunction":"closeAccountingYearPeriod","fromId":-2,"toFunction":"closeAccountingPeriod","toId":-2},
    {"fromFunction":"closeAccountingYearPeriod","fromId":-3,"toFunction":"closeAccountingPeriod","toId":-3},
    {"fromFunction":"closeAccountingYearPeriod","fromId":-4,"toFunction":"closeAccountingPeriod","toId":-4},
    {"fromFunction":"closeAccountingYearPeriod","fromId":-5,"toFunction":"closeAccountingPeriod","toId":-5},
    {"fromFunction":"closeAccountingYearPeriod","fromId":-6,"toFunction":"closeAccountingPeriod","toId":-6},
    {"fromFunction":"convertCustomerToProspect","fromId":-1,"toFunction":"deleteCustomer","toId":-1},
    {"fromFunction":"convertCustomerToProspect","fromId":-2,"toFunction":"deleteCustomer","toId":-2},
    {"fromFunction":"convertCustomerToProspect","fromId":-3,"toFunction":"deleteCustomer","toId":-3},
    {"fromFunction":"convertCustomerToProspect","fromId":-4,"toFunction":"deleteCustomer","toId":-4},
    {"fromFunction":"convertCustomerToProspect","fromId":-5,"toFunction":"deleteCustomer","toId":-5},
    {"fromFunction":"convertProspectToCustomer","fromId":-1,"toFunction":"deleteProspect","toId":-1},
    {"fromFunction":"copyPrj","fromId":-10,"toFunction":"saveAlarm","toId":-10},
    {"fromFunction":"createARDebitMemo","fromId":-1,"toFunction":"createARCreditMemo","toId":-1},
    {"fromFunction":"deleteOpenRecurringItems","fromId":-1,"toFunction":"deleteIncident","toId":-1},
    {"fromFunction":"deleteOpenRecurringItems","fromId":-2,"toFunction":"deleteIncident","toId":-2},
    {"fromFunction":"enablePackage","fromId":-1,"toFunction":"disablePackage","toId":-1},
    {"fromFunction":"enablePackage","fromId":-2,"toFunction":"disablePackage","toId":-2},
    {"fromFunction":"issueAllBalanceToShipping","fromId":-1,"toFunction":"issueToShipping","toId":-1},
    {"fromFunction":"issueAllBalanceToShipping","fromId":-10,"toFunction":"issueToShipping","toId":-10},
    {"fromFunction":"issueAllBalanceToShipping","fromId":-12,"toFunction":"issueToShipping","toId":-12},
    {"fromFunction":"issueAllBalanceToShipping","fromId":-13,"toFunction":"issueToShipping","toId":-13},
    {"fromFunction":"issueAllBalanceToShipping","fromId":-14,"toFunction":"issueToShipping","toId":-14},
    {"fromFunction":"issueAllBalanceToShipping","fromId":-15,"toFunction":"issueToShipping","toId":-15},
    {"fromFunction":"issueAllBalanceToShipping","fromId":-20,"toFunction":"issueToShipping","toId":-20},
    {"fromFunction":"issueLineBalanceToShipping","fromId":-1,"toFunction":"issueToShipping","toId":-1},
    {"fromFunction":"issueLineBalanceToShipping","fromId":-10,"toFunction":"issueToShipping","toId":-10},
    {"fromFunction":"issueLineBalanceToShipping","fromId":-12,"toFunction":"issueToShipping","toId":-12},
    {"fromFunction":"issueLineBalanceToShipping","fromId":-13,"toFunction":"issueToShipping","toId":-13},
    {"fromFunction":"issueLineBalanceToShipping","fromId":-14,"toFunction":"issueToShipping","toId":-14},
    {"fromFunction":"issueLineBalanceToShipping","fromId":-15,"toFunction":"issueToShipping","toId":-15},
    {"fromFunction":"issueLineBalanceToShipping","fromId":-20,"toFunction":"issueToShipping","toId":-20},
    {"fromFunction":"issueToShipping","fromId":-1,"toFunction":"postInvTrans","toId":-1},
    {"fromFunction":"openAccountingYearPeriod","fromId":-1,"toFunction":"openAccountingPeriod","toId":-1},
    {"fromFunction":"postCCCashReceipt","fromId":-1,"toFunction":"createARCreditMemo","toId":-1},
    {"fromFunction":"postCCCashReceipt","fromId":-10,"toFunction":"postCCcredit","toId":-1},
    {"fromFunction":"postCountTagLocation","fromId":-1,"toFunction":"postCountTag","toId":-1},
    {"fromFunction":"postCountTagLocation","fromId":-2,"toFunction":"postCountTag","toId":-2},
    {"fromFunction":"postCountTagLocation","fromId":-3,"toFunction":"postCountTag","toId":-3},
    {"fromFunction":"postCountTagLocation","fromId":-4,"toFunction":"postCountTag","toId":-4},
    {"fromFunction":"postGLSeriesNoSumm","fromId":-4,"toFunction":"postGLSeries","toId":-4},
    {"fromFunction":"postGLSeriesNoSumm","fromId":-5,"toFunction":"postGLSeries","toId":-5},
    {"fromFunction":"postInvoice","fromId":-1,"toFunction":"insertIntoGLSeries","toId":-1},
    {"fromFunction":"postInvoice","fromId":-4,"toFunction":"insertIntoGLSeries","toId":-4},
    {"fromFunction":"postInvoice","fromId":-5,"toFunction":"postGLSeries","toId":-5},
    {"fromFunction":"postInvTrans","fromId":-3,"toFunction":"insertGLTransaction","toId":-3},
    {"fromFunction":"postInvTrans","fromId":-4,"toFunction":"insertGLTransaction","toId":-4},
    {"fromFunction":"postPoReceipt","fromId":-1,"toFunction":"postReceipt","toId":-1},
    {"fromFunction":"postPoReceipt","fromId":-2,"toFunction":"postReceipt","toId":-2},
    {"fromFunction":"postPoReceipt","fromId":-3,"toFunction":"postReceipt","toId":-3},
    {"fromFunction":"postPoReceipt","fromId":-4,"toFunction":"postReceipt","toId":-4},
    {"fromFunction":"postPoReceipt","fromId":-10,"toFunction":"postReceipt","toId":-10},
    {"fromFunction":"postPoReceipt","fromId":-11,"toFunction":"postReceipt","toId":-11},
    {"fromFunction":"postPoReceipt","fromId":-12,"toFunction":"postReceipt","toId":-12},
    {"fromFunction":"postReceipt","fromId":-1,"toFunction":"postInvTrans","toId":-1},
    {"fromFunction":"postReceipt","fromId":-2,"toFunction":"postInvTrans","toId":-2},
    {"fromFunction":"postReceipt","fromId":-3,"toFunction":"insertGLTransaction","toId":-3},
    {"fromFunction":"postReceipt","fromId":-4,"toFunction":"insertGLTransaction","toId":-4},
    {"fromFunction":"postReceipt","fromId":-20,"toFunction":"issueToShipping","toId":-1},
    {"fromFunction":"postReceipt","fromId":-21,"toFunction":"issueToShipping","toId":-10},
    {"fromFunction":"postReceipt","fromId":-22,"toFunction":"issueToShipping","toId":-12},
    {"fromFunction":"postReceipt","fromId":-23,"toFunction":"issueToShipping","toId":-13},
    {"fromFunction":"postReceipt","fromId":-24,"toFunction":"issueToShipping","toId":-14},
    {"fromFunction":"postReceipt","fromId":-25,"toFunction":"issueToShipping","toId":-15},
    {"fromFunction":"postReceipt","fromId":-26,"toFunction":"shipShipment","toId":-12},
    {"fromFunction":"postReceipt","fromId":-27,"toFunction":"shipShipment","toId":-13},
    {"fromFunction":"postReceipt","fromId":-28,"toFunction":"shipShipment","toId":-14},
    {"fromFunction":"postReceipt","fromId":-29,"toFunction":"shipShipment","toId":-15},
    {"fromFunction":"postReceipt","fromId":-30,"toFunction":"shipShipment","toId":-1},
    {"fromFunction":"postReceipt","fromId":-31,"toFunction":"shipShipment","toId":-3},
    {"fromFunction":"postReceipt","fromId":-32,"toFunction":"shipShipment","toId":-4},
    {"fromFunction":"postReceipt","fromId":-33,"toFunction":"shipShipment","toId":-5},
    {"fromFunction":"postReceipt","fromId":-34,"toFunction":"shipShipment","toId":-6},
    {"fromFunction":"postReceipt","fromId":-35,"toFunction":"shipShipment","toId":-8},
    {"fromFunction":"postReceipt","fromId":-36,"toFunction":"shipShipment","toId":-50},
    {"fromFunction":"postReceipt","fromId":-37,"toFunction":"shipShipment","toId":-99},
    {"fromFunction":"postPoReceipts","fromId":-1,"toFunction":"postPoReceipt","toId":-1},
    {"fromFunction":"postPoReceipts","fromId":-3,"toFunction":"postPoReceipt","toId":-3},
    {"fromFunction":"postPoReceipts","fromId":-4,"toFunction":"postPoReceipt","toId":-4},
    {"fromFunction":"postPoReceipts","fromId":-10,"toFunction":"postPoReceipt","toId":-10},
    {"fromFunction":"postPoReceipts","fromId":-12,"toFunction":"postPoReceipt","toId":-12},
    {"fromFunction":"postPoReturns","fromId":-1,"toFunction":"postInvTrans","toId":-1},
    {"fromFunction":"postPoReturns","fromId":-3,"toFunction":"insertGLTransaction","toId":-3},
    {"fromFunction":"postPoReturns","fromId":-4,"toFunction":"insertGLTransaction","toId":-4},
    {"fromFunction":"replaceAllVoidedChecks","fromId":-1,"toFunction":"replaceVoidedCheck","toId":-1},
    {"fromFunction":"returnItemShipments","fromId":-1,"toFunction":"postInvTrans","toId":-1},
    {"fromFunction":"shipShipment","fromId":-1,"toFunction":"postInvTrans","toId":-1},
    {"fromFunction":"shipShipment","fromId":-3,"toFunction":"insertGLTransaction","toId":-3},
    {"fromFunction":"shipShipment","fromId":-4,"toFunction":"insertGLTransaction","toId":-4},
    {"fromFunction":"splitRecurrence","fromId":-10,"toFunction":"createRecurringItems","toId":-10},
    {"fromFunction":"sufficientInventoryToShipOrder","fromId":-11,"toFunction":"sufficientInventoryToShipItem","toId":-11},
    {"fromFunction":"voidCreditMemo","fromId":-1,"toFunction":"insertIntoGLSeries","toId":-1},
    {"fromFunction":"voidCreditMemo","fromId":-4,"toFunction":"insertIntoGLSeries","toId":-4},
    {"fromFunction":"voidCreditMemo","fromId":-5,"toFunction":"postGLSeries","toId":-5},
    {"fromFunction":"voidInvoice","fromId":-1,"toFunction":"insertIntoGLSeries","toId":-1},
    {"fromFunction":"voidInvoice","fromId":-4,"toFunction":"insertIntoGLSeries","toId":-4},
    {"fromFunction":"voidInvoice","fromId":-5,"toFunction":"postGLSeries","toId":-5},
    {"fromFunction":"reserveSoLineBalance","fromId":-1,"toFunction":"reserveSoLineQty","toId":-1},
    {"fromFunction":"reserveSoLineBalance","fromId":-2,"toFunction":"reserveSoLineQty","toId":-2},
    {"fromFunction":"reserveSoLineBalance","fromId":-3,"toFunction":"reserveSoLineQty","toId":-3},
    {"fromFunction":"woClockIn","fromId":-1,"toFunction":"explodeWo","toId":-1},
    {"fromFunction":"woClockIn","fromId":-2,"toFunction":"explodeWo","toId":-2},
    {"fromFunction":"woClockIn","fromId":-3,"toFunction":"explodeWo","toId":-3}
  ];

  XT.getUserCulture = function() {
    var sql = "select lang_abbr2 || '_' || country_abbr as \"culture\" " +
      "from locale " +
      "join usr on usr_locale_id = locale_id " +
      "left join lang on locale_lang_id = lang_id " +
      "left join country on locale_country_id = country_id " +
      "where usr_username = $1;";
    return plv8.execute(sql, [XT.username])[0].culture;
  };

  XT.errorToString = function(functionName, errorCode, params) {
    params = params || [];
    var culture,
      dictSql,
      dictResult,
      errorMap = [true],
      stringsKey,
      i = 1;

    /* Trace the error code down to the underlying error based on our errorMap */
    while (errorMap.length) {
      errorMap = errorMaps.filter(function (errorMap) {
        return errorMap.fromFunction === functionName && errorMap.fromId === errorCode;
      });
      if (errorMap.length) {
        functionName = errorMap[0].toFunction;
        errorCode = errorMap[0].toId;
      }
    }

    /* cache the strings in JSON in XT.dbStrings */
    XT.dbStrings = XT.dbStrings || {};
    culture = XT.getUserCulture();
    if(!XT.dbStrings[culture]) {
      /* need to load in the strings for this culture into the database */
      dictSql = "select dict_strings from xt.dict where dict_is_database = true and dict_language_name = $1;";
      dictResult = plv8.execute(dictSql, [culture]);
      XT.dbStrings[culture] = JSON.parse(dictResult[0].dict_strings.toLowerCase());
    }
    /* this is the convention under which the translations are stored */
    stringsKey = "_xtdb_" + functionName + (-1 * errorCode);

    var returnVal = XT.dbStrings[culture][stringsKey.toLowerCase()];

    /* Replace parameters if applicable */
    params.forEach(function(param) {
      returnVal = returnVal.replace("%" + i, param);
      i++;
    })

    return returnVal || "Undocumented error: " + functionName + " " + errorCode;
  };

  /**
    Wrapper for plv8.execute() for calling postgres functions.
    If the postgres function returns an error in the form of
    a negative integer, this function finds the appropriate
    translation and throws that error.

    NOTE that you should not pass unsanitized user input into
    the functionName or the casts variables.

    @param {String} functionName.
    @param {Array} params
    @param {Array} casts. Optional. Array of strings
   */
  XT.executeFunction = function (functionName, params, casts) {
    params = params || [];
    var cast,
      errorString,
      i;

    var sql = "select " + functionName + "(";
    for (i = 0; i < params.length; i++) {
      cast = casts && casts[i];
      if (i > 0) {
        sql = sql + ",";
      }
      sql = sql + "$" + (i + 1);
      if(cast) {
        sql = sql + "::" + cast;
      }

    }
    sql = sql + ") as result";

    if (DEBUG) {
      XT.debug('sql =', sql);
      XT.debug('params =', params);
    }
    var result = plv8.execute(sql, params)[0].result;
    if(typeof result === 'number' && result < 0) {
      errorString = XT.errorToString(functionName, result);
      throw new handleError(errorString, 424);
    } else {
      return result;
    }
  };


  /**
   * Wrap PostgreSQL's format() function to format SQL Injection safe queires or general strings.
   * http://www.postgresql.org/docs/9.1/interactive/functions-string.html#FUNCTIONS-STRING-OTHER
   *
   * Example: var query = XT.format("select * from %I", ["cntct"]);
   *  Returns: 'select * from cntct'
   * Example: var query = XT.format("select %1$I.* from %2$I.%1$I {join} where %1$I.%3$I = $1", ["contact", "xm", "id"]);
   *  Returns: 'select contact.* from xm.contact {join} where contact.number = $1'
   *
   * SQL Injection attemp:
   * Example: var query = XT.format("SELECT * FROM %I", ["cntct; select * from pg_roles; --"]);
   * Safely escaped/quoted query:
   *  Returns: 'SELECT * FROM "cntct; select * from pg_roles; --"'
   *
   * @param {String} The string with format tokens to replace.
   * @param {Array} An array of replacement strings.
   * @returns {String} Safely escaped string with tokens replaced.
   */
  XT.format = function (string, args) {
    if (typeof string !== 'string' || XT.typeOf(args) !== 'array' || !args.length) {
      return false;
    }

    var query = "select format($1",
        params = "";

    for(var i = 0; i < args.length; i++) {
      params = params + ", $" + (i + 2);
    }
    query = query + params + ")";

    /* Pass 'string' to format() as the first parameter. */
    args.unshift(string);

    /*
    if (DEBUG) {
      XT.debug('XT.format sql =', query);
      XT.debug('XT.format args =', args);
    }
    */
    string = plv8.execute(query, args)[0].format;

    /* Remove 'string' from args to prevent reference errors. */
    args.shift();

    return string;
  };

  /**
   * Wrap formatDate()
   *
   * @param Object The date.
   * @returns {String} Date string in user's locale.
  */
  XT.formatDate = function (string) {
    var query = "select formatdate($1);"
    if (DEBUG) {
      XT.debug('XT.formatDate sql =', query);
      XT.debug('XT.formatDate arg =', string);
    }
    ret = plv8.execute(query, [string])[0];
    return ret;
  };

  /**
   * Wrap formatCost()
   *
   * @param Number The cost.
   * @returns {Number} Cost number in user's locale & scale.
  */
  XT.formatCost = function (numb) {
    var query = "select formatcost($1);"
    if (DEBUG) {
      XT.debug('XT.formatCost sql =', query);
      XT.debug('XT.formatCost arg =', numb);
    }
    ret = plv8.execute(query, [numb])[0];
    return ret;
  };

  /**
   * Wrap formatMoney()
   *
   * @param Number The money.
   * @returns {Number} Money in user's locale & scale.
  */
  XT.formatMoney = function (numb) {
    var query = "select formatmoney($1);"
    if (DEBUG) {
      XT.debug('XT.formatMoney sql =', query);
      XT.debug('XT.formatMoney arg =', numb);
    }
    ret = plv8.execute(query, [numb])[0];
    return ret;
  };

  /**
   * Wrap formatSalesPrice()
   *
   * @param Number The price.
   * @returns {Number} Price in user's locale & scale.
  */
  XT.formatSalesPrice = function (numb) {
    var query = "select formatsalesprice($1);"
    if (DEBUG) {
      XT.debug('XT.formatSalesPrice sql =', query);
      XT.debug('XT.formatSalesPrice arg =', numb);
    }
    ret = plv8.execute(query, [numb])[0];
    return ret;
  };

  /**
   * Wrap formatPurchPrice()
   *
   * @param Number The price.
   * @returns {Number} Price in user's locale & scale.
  */
  XT.formatPurchPrice = function (numb) {
    var query = "select formatpurchprice($1);"
    if (DEBUG) {
      XT.debug('XT.formatPurchPrice sql =', query);
      XT.debug('XT.formatPurchPrice arg =', numb);
    }
    ret = plv8.execute(query, [numb])[0];
    return ret;
  };

  /**
   * Wrap formatExtPrice()
   *
   * @param Number The price.
   * @returns {Number} Price in user's locale & scale.
  */
  XT.formatExtPrice = function (numb) {
    var query = "select formatExtPrice($1);"
    if (DEBUG) {
      XT.debug('XT.formatExtPrice sql =', query);
      XT.debug('XT.formatExtPrice arg =', numb);
    }
    ret = plv8.execute(query, [numb])[0];
    return ret;
  };

  /**
   * Wrap formatQty()
   *
   * @param Number The quantity.
   * @returns {Number} Quantity in user's locale & scale.
  */
  XT.formatQty = function (numb) {
    var query = "select formatqty($1);"
    if (DEBUG) {
      XT.debug('XT.formatQty sql =', query);
      XT.debug('XT.formatQty arg =', numb);
    }
    ret = plv8.execute(query, [numb])[0];
    return ret;
  };

  /**
   * Wrap formatQtyPer()
   *
   * @param Number The quantity per.
   * @returns {Number} Quantity per in user's locale & scale.
  */
  XT.formatQtyPer = function (numb) {
    var query = "select formatqtyper($1);"
    if (DEBUG) {
      XT.debug('XT.formatQtyPer sql =', query);
      XT.debug('XT.formatQtyPer arg =', numb);
    }
    ret = plv8.execute(query, [numb])[0];
    return ret;
  };

  /**
   * Wrap formatRatio()
   *
   * @param Number The ratio.
   * @returns {Number} Ratio in user's locale & scale.
  */
  XT.formatRatio = function (numb) {
    var query = "select formatRatio($1);"
    if (DEBUG) {
      XT.debug('XT.formatRatio sql =', query);
      XT.debug('XT.formatRatio arg =', numb);
    }
    ret = plv8.execute(query, [numb])[0];
    return ret;
  };

  /**
   * Wrap formatPrcnt()
   *
   * @param Number The percent.
   * @returns {Number} Percent in user's locale & scale.
  */
  XT.formatPrcnt = function (numb) {
    var query = "select formatprcnt($1);"
    if (DEBUG) {
      XT.debug('XT.formatPrcnt sql =', query);
      XT.debug('XT.formatPrcnt arg =', numb);
    }
    ret = plv8.execute(query, [numb])[0];
    return ret;
  };

  /**
   * Wrap formatWeight()
   *
   * @param Number The weight.
   * @returns {Number} Weight in user's locale & scale.
  */
  XT.formatWeight = function (numb) {
    var query = "select formatweight($1);"
    if (DEBUG) {
      XT.debug('XT.formatWeight sql =', query);
      XT.debug('XT.formatWeight arg =', numb);
    }
    ret = plv8.execute(query, [numb])[0];
    return ret;
  };

  /**
   * Wrap formatNumeric()
   *
   * @param Number.
   * @returns {Number} Number in user's locale & scale.
  */
  XT.formatNumeric = function (numb, text) {
    var query = "select formatNumeric($1, $2);"
    if (DEBUG) {
      XT.debug('XT.formatNumeric sql =', query);
      XT.debug('XT.formatNumeric arg1 =', numb);
      XT.debug('XT.formatNumeric arg2 =', text);
    }
    ret = plv8.execute(query, [numb, text])[0];
    return ret;
  };

  /**
    Return a universally unique identifier.

    We're using this solution:
    http://stackoverflow.com/a/8809472/251019
    From here:
    http://stackoverflow.com/questions/105034/how-to-create-a-guid-uuid-in-javascript

    @return {String}
  */
  XT.generateUUID = function () {
    var d = new Date().getTime();
    var uuid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function (c) {
      var r = (d + Math.random() * 16) % 16 | 0;
      d = Math.floor(d / 16);
      return (c === 'x' ? r : (r&0x7|0x8)).toString(16);
    });

    return uuid;
  };

  XT.message = function (code, message) {
    var msg = {code: code, message: message.substring(0, 90)};

    plv8.elog(INFO, JSON.stringify(msg));
    return 'Handled by XT.message';
  }

  /**
    Extended version of javascript 'typeof' that also recognizes arrays
  */
  XT.typeOf = function(value) {
    var str = typeof value;
    if (str === 'object') {
      if (value) {
        if (typeof value.length === 'number' &&
            !(value.propertyIsEnumerable('length')) &&
            typeof value.splice === 'function') {
          str = 'array';
        }
      } else str = 'null';
    }
    return str;
  }

  XT.extend = function(obj) {
    Array.prototype.slice.call(arguments, 1).forEach(function(source) {
      for (var prop in source) {
        obj[prop] = source[prop];
      }
    });
    return obj;
  }

  // ..........................................................
  // PROCESS
  //

  var res, sql, path;

  /* put xt at the front of the search path */
  path = plv8.execute('select show_search_path() as path');
  path = path[0].path;
  plv8.execute('set search_path to xt,' + path);

  /* create namespace objects for all registered javascript */
  sql = 'select distinct js_namespace as "nameSpace" '
        + 'from xt.js '
        + 'where js_active; '
  res = plv8.execute(sql);
  if(res.length) {
    for(var i = 0; i < res.length; i++) {
      if(!plv8[res[i].nameSpace]) {
        plv8[res[i].nameSpace] = eval([res[i].nameSpace] + " = {}");
      }
    }

    /* load up all active javascript installed in the database */
    /* TODO: What about dependencies? */
    sql = 'select js_type, js_text as "javascript" '
        + 'from xt.js '
        + 'where js_active '
        + 'order by js_ext, '
        + 'js_context = \'xtuple\' DESC';
    res = plv8.execute(sql);
    if(res.length) {
      for(var i = 0; i < res.length; i++) {
        if(DEBUG) XT.debug('loading javascript for type->', res[i].js_type);

        eval(res[i].javascript);

        var ns = eval(res[i].js_namespace);
        if (ns && ns[js_type]) {
          if (Object.isFrozen(ns[js_type])) {
            plv8.elog(WARNING, 'object already frozen: '+ ns + '.' + js_type);
          }
          Object.freeze(ns[js_type]);
        }
      }
    }
    plv8.__initialized = true;
  }

}());

$_$;


ALTER FUNCTION xt.js_init(debug boolean, initialize boolean) OWNER TO admin;

--
-- Name: parseediprofile(text, text); Type: FUNCTION; Schema: xt; Owner: admin
--

CREATE FUNCTION parseediprofile(ediprofile text, options text DEFAULT '{}'::text) RETURNS text
    LANGUAGE plv8
    AS $$

  var editext = '';

  /**
   * Call the parser with this functions parameters.
   */
  try {
    editext += XT.EDIProfile.parser.parse(ediprofile, JSON.parse(options));
  } catch (err) {
    plv8.elog(ERROR, 'Cannot parse EDI Profile Tokens. ERROR: ', err);
  }

  return editext;
$$;


ALTER FUNCTION xt.parseediprofile(ediprofile text, options text) OWNER TO admin;

--
-- Name: parsemetasql(text, text); Type: FUNCTION; Schema: xt; Owner: admin
--

CREATE FUNCTION parsemetasql(metasql text, options text DEFAULT '{}'::text) RETURNS text
    LANGUAGE plv8
    AS $$
  /**
   * xt.parsemetasql(text, text)
   * ===========================
   *
   * Wrapper function for the plv8 MetaSQL PEG parser:
   *                         - XT.MetaSQL.parser.parse(metasql, options);
   *
   * @param {string} metasql - The MetaSQL query text string.
   * @param {string} options - A JSON string of options in the format of:
   *                           '{"params":{"your-metasql-parameter-here":"your-value-here","Pn":"Vn"}}'
   * @returns {string}       - Returns a query text string with the MetaSQL
   *                         - parameters replaced with your values
   *
   * Example Usage:
   *   SELECT xt.parsemetasql(
   *     'SELECT <? value("foo") ?> AS baz;',
   *     '{"params":{"foo":"bar"}}'
   *   );
   *
   *   Returns: "SELECT 'bar' AS baz;"
   */
  var sql = '';

  /*
   * Call the parser with this functions parameters.
   */
  try {
    sql += XT.MetaSQL.parser.parse(metasql, JSON.parse(options));
  } catch (err) {
    plv8.elog(ERROR, 'Cannot parse MetaSQL. ERROR: ', err);
  }

  return sql;
$$;


ALTER FUNCTION xt.parsemetasql(metasql text, options text) OWNER TO admin;

--
-- Name: parsemetasql(text, text, text); Type: FUNCTION; Schema: xt; Owner: admin
--

CREATE FUNCTION parsemetasql(pgroup text, pname text, poptions text) RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
  qry TEXT;
BEGIN
  -- Overload for xt.parsemetasql(text, text) that loads the MetaSQL from the
  -- metasql table based on pGroup and pName then passes it to the parser along
  -- with pOptions.

  SELECT metasql_query INTO qry
  FROM metasql
  WHERE metasql_name = pName
    AND metasql_group = pGroup
  ORDER BY metasql_grade DESC
  LIMIT 1;

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'MetaSQL Query %-% was not found.', pGroup, pName;
  END IF;

  RETURN xt.parsemetasql(qry, pOptions);

END;
$$;


ALTER FUNCTION xt.parsemetasql(pgroup text, pname text, poptions text) OWNER TO admin;


--
-- Name: fetchwelcomehtml(); Type: FUNCTION; Schema: xtdesktop; Owner: admin
--

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

BEGIN

  _html := '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> 
<HTML> 
<HEAD> 
	<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=utf-8"> 
	<TITLE></TITLE> 
	<META NAME="GENERATOR" CONTENT="OpenOffice.org 3.0  (Unix)"> 
	<META NAME="CREATED" CONTENT="20100422;16091500"> 
	<META NAME="CHANGED" CONTENT="20100422;16095800"> 
	<META NAME="Info 1" CONTENT=""> 
	<META NAME="Info 2" CONTENT=""> 
	<META NAME="Info 3" CONTENT=""> 
	<META NAME="Info 4" CONTENT=""> 
	<STYLE TYPE="text/css"> 
	<!--
		@page { margin: 0.79in }
		P { margin-bottom: 0.08in }
	--> 
	</STYLE> 
</HEAD> 
<BODY LANG="en-US" DIR="LTR"> 
<P STYLE="margin-bottom: 0in"><B>Welcome to xTuple</B></P> 
<P STYLE="margin-bottom: 0in"><BR> 
</P> 
<P STYLE="margin-bottom: 0in">You are not connected to the internet
at this time</P> 
</BODY> 
</HTML>';

  RETURN _html;

END;
$$;


ALTER FUNCTION xtdesktop.fetchwelcomehtml() OWNER TO admin;

--
-- Name: fetchxtupleserverhtml(); Type: FUNCTION; Schema: xtdesktop; Owner: admin
--

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

BEGIN

  _html := '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> 
<HTML> 
<HEAD> 
	<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=utf-8"> 
	<TITLE></TITLE>
	<META NAME="CREATED" CONTENT="20100422;16091500"> 
	<META NAME="CHANGED" CONTENT="20100422;16095800"> 
	<META NAME="Info 1" CONTENT=""> 
	<META NAME="Info 2" CONTENT=""> 
	<META NAME="Info 3" CONTENT=""> 
	<META NAME="Info 4" CONTENT=""> 
	<STYLE TYPE="text/css"> 
	<!--
		@page { margin: 0.79in }
		P { margin-bottom: 0.08in }
	--> 
	</STYLE> 
</HEAD> 
<BODY LANG="en-US" DIR="LTR"> 
<P STYLE="margin-bottom: 0in"><B>Welcome to xTuple</B></P> 
<P STYLE="margin-bottom: 0in"><BR> 
</P> 
<P STYLE="margin-bottom: 0in">You are not connected to an xTuple Server
at this time.</P> 
<P STYLE="margin-bottom: 0in">You require a suitable xTuple Server to view Dashboards.</P> 
</BODY> 
</HTML>';

  RETURN _html;

END;
$$;


ALTER FUNCTION xtdesktop.fetchxtupleserverhtml() OWNER TO admin;

--
-- Name: usersonline(); Type: FUNCTION; Schema: xtdesktop; Owner: admin
--

CREATE FUNCTION usersonline() RETURNS SETOF usersonline
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
 _row xtdesktop.usersonline%ROWTYPE;
 _query TEXT;
BEGIN
  -- first part of our query doesn't care what PG version it is
  _query :=    'SELECT usr.usr_id, usr.usr_username, usr.usr_propername, usr.usr_email, '
            || 'min(backend_start) AS client_start, max(query_start) AS query_start, '
            || 'sum(CASE WHEN(database IS NULL) THEN 0 ELSE 1 END) AS cnt_internal, '
            || 'sum(CASE WHEN(database IS NULL) THEN 1 ELSE 0 END) AS cnt_external, '
            || 'client_addr '
            || 'FROM pg_stat_activity '
            || 'JOIN public.usr ON (usr.usr_id=usesysid) ';

   -- use the integer comparison to compare server version to 9.2.0, where procpid was changed to just pid
   IF (compareversion('9.2.0') <= 0)
   THEN
     _query := _query || 'LEFT OUTER JOIN pg_locks ON (database=datid AND classid=datid AND objid=pg_stat_activity.pid AND objsubid=2) ';
   ELSE
     _query := _query || 'LEFT OUTER JOIN pg_locks ON (database=datid AND classid=datid AND objid=procpid AND objsubid=2) ';
   END IF;

   -- the rest of the query
   _query := _query || 'WHERE(datname=current_database()) '
                    || 'GROUP BY usr_id, usr_username, usr_propername, usr_email, client_addr ';

   -- loop over the rows that executing this query returns, and return them to the calling user
   FOR _row IN
      EXECUTE _query
      LOOP
        RETURN NEXT _row;
      END LOOP;
   RETURN;

END;
$$;


ALTER FUNCTION xtdesktop.usersonline() OWNER TO admin;

--
-- Name: FUNCTION usersonline(); Type: COMMENT; Schema: xtdesktop; Owner: admin
--

COMMENT ON FUNCTION usersonline() IS 'A table function that returns a PostgreSQL-version aware count of users online.';



--
-- Name: concatagg(text); Type: AGGREGATE; Schema: public; Owner: admin
--

CREATE AGGREGATE concatagg(text) (
    SFUNC = concataggsfunc,
    STYPE = text,
    INITCOND = ''
);


ALTER AGGREGATE public.concatagg(text) OWNER TO admin;

--
-- Name: first(anyelement); Type: AGGREGATE; Schema: public; Owner: admin
--

CREATE AGGREGATE first(anyelement) (
    SFUNC = first_agg,
    STYPE = anyelement
);


ALTER AGGREGATE public.first(anyelement) OWNER TO admin;

--
-- Name: last(anyelement); Type: AGGREGATE; Schema: public; Owner: admin
--

CREATE AGGREGATE last(anyelement) (
    SFUNC = last_agg,
    STYPE = anyelement
);


ALTER AGGREGATE public.last(anyelement) OWNER TO admin;

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

CREATE TABLE addr (
    addr_id integer NOT NULL,
    addr_active boolean DEFAULT true,
    addr_line1 text DEFAULT ''::text,
    addr_line2 text DEFAULT ''::text,
    addr_line3 text DEFAULT ''::text,
    addr_city text DEFAULT ''::text,
    addr_state text DEFAULT ''::text,
    addr_postalcode text DEFAULT ''::text,
    addr_country text DEFAULT ''::text,
    addr_notes text DEFAULT ''::text,
    addr_number text NOT NULL,
    CONSTRAINT addr_addr_number_check CHECK ((addr_number <> ''::text))
);


ALTER TABLE addr OWNER TO admin;

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

COMMENT ON TABLE addr IS 'Postal Address';



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

CREATE VIEW account AS
    SELECT (c.crmacct_number)::character varying AS account_number, p.crmacct_number AS parent_account, c.crmacct_name AS account_name, c.crmacct_active AS active, CASE WHEN (c.crmacct_type = 'O'::bpchar) THEN 'Organization'::text ELSE 'Individual'::text END AS type, pc.cntct_number AS primary_contact_number, pc.cntct_honorific AS primary_contact_honorific, pc.cntct_first_name AS primary_contact_first, pc.cntct_middle AS primary_contact_middle, pc.cntct_last_name AS primary_contact_last, pc.cntct_suffix AS primary_contact_suffix, pc.cntct_title AS primary_contact_job_title, pc.cntct_phone AS primary_contact_voice, pc.cntct_fax AS primary_contact_fax, pc.cntct_email AS primary_contact_email, ''::text AS primary_contact_change, m.addr_number AS primary_contact_address_number, m.addr_line1 AS primary_contact_address1, m.addr_line2 AS primary_contact_address2, m.addr_line3 AS primary_contact_address3, m.addr_city AS primary_contact_city, m.addr_state AS primary_contact_state, m.addr_postalcode AS primary_contact_postalcode, m.addr_country AS primary_contact_country, ''::text AS primary_contact_address_change, sc.cntct_number AS secondary_contact_number, sc.cntct_honorific AS secondary_contact_honorific, sc.cntct_first_name AS secondary_contact_first, sc.cntct_middle AS secondary_contact_middle, sc.cntct_last_name AS secondary_contact_last, sc.cntct_suffix AS secondary_contact_suffix, sc.cntct_title AS secondary_contact_job_title, sc.cntct_phone AS secondary_contact_voice, sc.cntct_fax AS secondary_contact_fax, sc.cntct_email AS secondary_contact_email, sc.cntct_webaddr AS secondary_contact_web, ''::text AS secondary_contact_change, s.addr_number AS secondary_contact_address_number, s.addr_line1 AS secondary_contact_address1, s.addr_line2 AS secondary_contact_address2, s.addr_line3 AS secondary_contact_address3, s.addr_city AS secondary_contact_city, s.addr_state AS secondary_contact_state, s.addr_postalcode AS secondary_contact_postalcode, s.addr_country AS secondary_contact_country, ''::text AS secondary_contact_address_change, c.crmacct_notes AS notes FROM (((((public.crmacct c LEFT JOIN public.crmacct p ON ((c.crmacct_parent_id = p.crmacct_id))) LEFT JOIN public.cntct pc ON ((c.crmacct_cntct_id_1 = pc.cntct_id))) LEFT JOIN public.addr m ON ((pc.cntct_addr_id = m.addr_id))) LEFT JOIN public.cntct sc ON ((c.crmacct_cntct_id_2 = sc.cntct_id))) LEFT JOIN public.addr s ON ((sc.cntct_addr_id = s.addr_id)));


ALTER TABLE account OWNER TO admin;

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

COMMENT ON VIEW account IS 'Account';



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

CREATE TABLE "char" (
    char_id integer NOT NULL,
    char_name text NOT NULL,
    char_items boolean,
    char_options boolean,
    char_attributes boolean,
    char_lotserial boolean,
    char_notes text,
    char_customers boolean,
    char_crmaccounts boolean,
    char_addresses boolean,
    char_contacts boolean,
    char_opportunity boolean,
    char_employees boolean DEFAULT false,
    char_mask text,
    char_validator text,
    char_incidents boolean DEFAULT false,
    char_type integer DEFAULT 0 NOT NULL,
    char_order integer DEFAULT 0 NOT NULL,
    char_search boolean DEFAULT true NOT NULL,
    char_quotes boolean DEFAULT false,
    char_salesorders boolean DEFAULT false,
    char_invoices boolean DEFAULT false,
    char_vendors boolean DEFAULT false,
    char_purchaseorders boolean DEFAULT false,
    char_vouchers boolean DEFAULT false,
    char_projects boolean DEFAULT false,
    char_tasks boolean DEFAULT false,
    CONSTRAINT char_char_name_check CHECK ((char_name <> ''::text))
);


ALTER TABLE "char" OWNER TO admin;

--
-- Name: TABLE "char"; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE "char" IS 'Characteristic information';


--
-- Name: COLUMN "char".char_items; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_items IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type I';


--
-- Name: COLUMN "char".char_lotserial; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_lotserial IS 'DEPRECATED - this column has been replaced by rows in the charuse table with target_types LS and LSR';


--
-- Name: COLUMN "char".char_customers; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_customers IS 'DEPRECATED - this column has been replaced by rows in the charuse table with target_types C and CT';


--
-- Name: COLUMN "char".char_crmaccounts; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_crmaccounts IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type CRMA';


--
-- Name: COLUMN "char".char_addresses; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_addresses IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type ADDR';


--
-- Name: COLUMN "char".char_contacts; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_contacts IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type CNTCT';


--
-- Name: COLUMN "char".char_opportunity; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_opportunity IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type OPP';


--
-- Name: COLUMN "char".char_employees; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_employees IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type EMP';


--
-- Name: COLUMN "char".char_incidents; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_incidents IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type INCDT';


--
-- Name: COLUMN "char".char_quotes; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_quotes IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type QU';


--
-- Name: COLUMN "char".char_salesorders; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_salesorders IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type SO';


--
-- Name: COLUMN "char".char_invoices; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_invoices IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type INV';


--
-- Name: COLUMN "char".char_vendors; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_vendors IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type V';


--
-- Name: COLUMN "char".char_purchaseorders; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_purchaseorders IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type PO';


--
-- Name: COLUMN "char".char_vouchers; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_vouchers IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type VCH';


--
-- Name: COLUMN "char".char_projects; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_projects IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type PROJ';


--
-- Name: COLUMN "char".char_tasks; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN "char".char_tasks IS 'DEPRECATED - this column has been replaced by a row in the charuse table with target_type TASK';


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

CREATE TABLE charass (
    charass_id integer NOT NULL,
    charass_target_type text,
    charass_target_id integer,
    charass_char_id integer,
    charass_value text,
    charass_default boolean DEFAULT false NOT NULL,
    charass_price numeric(16,4) DEFAULT 0 NOT NULL
);


ALTER TABLE charass OWNER TO admin;

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

COMMENT ON TABLE charass IS 'Characteristic assignment information';



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

CREATE VIEW accountchar AS
    SELECT (crmacct.crmacct_number)::character varying AS account_number, ("char".char_name)::character varying AS characteristic, charass.charass_value AS value FROM public.crmacct, public."char", public.charass WHERE ((('CRMACCT'::text = charass.charass_target_type) AND (crmacct.crmacct_id = charass.charass_target_id)) AND (charass.charass_char_id = "char".char_id));


ALTER TABLE accountchar OWNER TO admin;

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

COMMENT ON VIEW accountchar IS 'Account Characteristics';



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

CREATE TABLE cmnttype (
    cmnttype_id integer NOT NULL,
    cmnttype_name text NOT NULL,
    cmnttype_descrip text NOT NULL,
    cmnttype_usedin text,
    cmnttype_sys boolean DEFAULT false NOT NULL,
    cmnttype_editable boolean DEFAULT false NOT NULL,
    cmnttype_order integer,
    CONSTRAINT cmnttype_cmnttype_name_check CHECK ((cmnttype_name <> ''::text))
);


ALTER TABLE cmnttype OWNER TO admin;

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

COMMENT ON TABLE cmnttype IS 'Comment Type information';


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

CREATE TABLE comment (
    comment_id integer DEFAULT nextval(('"comment_comment_id_seq"'::text)::regclass) NOT NULL,
    comment_source_id integer,
    comment_date timestamp with time zone,
    comment_user text,
    comment_text text,
    comment_cmnttype_id integer,
    comment_source text,
    comment_public boolean
);


ALTER TABLE comment OWNER TO admin;

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

COMMENT ON TABLE comment IS 'Comment information';



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

CREATE VIEW accountcomment AS
    SELECT (crmacct.crmacct_number)::character varying AS account_number, cmnttype.cmnttype_name AS type, comment.comment_date AS date, comment.comment_user AS username, comment.comment_text AS text FROM public.crmacct, public.cmnttype, public.comment WHERE (((comment.comment_source = 'CRMA'::text) AND (comment.comment_source_id = crmacct.crmacct_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id));


ALTER TABLE accountcomment OWNER TO admin;

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

COMMENT ON VIEW accountcomment IS 'Account Comment';



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

CREATE TABLE docass (
    docass_id integer NOT NULL,
    docass_source_id integer NOT NULL,
    docass_source_type text NOT NULL,
    docass_target_id integer NOT NULL,
    docass_target_type text DEFAULT 'URL'::text NOT NULL,
    docass_purpose character(1) DEFAULT 'S'::bpchar NOT NULL,
    CONSTRAINT docass_docass_purpose_check CHECK (((((((((docass_purpose = 'I'::bpchar) OR (docass_purpose = 'E'::bpchar)) OR (docass_purpose = 'M'::bpchar)) OR (docass_purpose = 'P'::bpchar)) OR (docass_purpose = 'A'::bpchar)) OR (docass_purpose = 'C'::bpchar)) OR (docass_purpose = 'S'::bpchar)) OR (docass_purpose = 'D'::bpchar)))
);


ALTER TABLE docass OWNER TO admin;

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

COMMENT ON TABLE docass IS 'Document Assignement References';


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

CREATE TABLE file (
    file_id integer NOT NULL,
    file_title text NOT NULL,
    file_stream bytea,
    file_descrip text NOT NULL
);


ALTER TABLE file OWNER TO admin;

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

CREATE TABLE urlinfo (
    url_id integer NOT NULL,
    url_title text NOT NULL,
    url_url text NOT NULL
);


ALTER TABLE urlinfo OWNER TO admin;

--
-- Name: url; Type: VIEW; Schema: public; Owner: admin
--

CREATE VIEW url AS
    SELECT docass.docass_id AS url_id, docass.docass_source_id AS url_source_id, docass.docass_source_type AS url_source, file.file_title AS url_title, file.file_descrip AS url_url, file.file_stream AS url_stream FROM (file JOIN docass ON (((docass.docass_target_id = file.file_id) AND (docass.docass_target_type = 'FILE'::text)))) UNION ALL SELECT docass.docass_id AS url_id, docass.docass_source_id AS url_source_id, docass.docass_source_type AS url_source, urlinfo.url_title, urlinfo.url_url, NULL::bytea AS url_stream FROM (urlinfo JOIN docass ON (((docass.docass_target_id = urlinfo.url_id) AND (docass.docass_target_type = 'URL'::text))));


ALTER TABLE url OWNER TO admin;


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

CREATE VIEW accountfile AS
    SELECT crmacct.crmacct_number AS account_number, url.url_title AS title, url.url_url AS url FROM public.crmacct, public.url WHERE ((crmacct.crmacct_id = url.url_source_id) AND (url.url_source = 'CRMA'::text));


ALTER TABLE accountfile OWNER TO admin;

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

COMMENT ON VIEW accountfile IS 'Account File';



--
-- Name: docass_docass_id_seq; Type: SEQUENCE; Schema: public; Owner: admin
--

CREATE SEQUENCE docass_docass_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE docass_docass_id_seq OWNER TO admin;

--
-- Name: docass_docass_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: admin
--

ALTER SEQUENCE docass_docass_id_seq OWNED BY docass.docass_id;


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

CREATE TABLE image (
    image_id integer DEFAULT nextval(('"image_image_id_seq"'::text)::regclass) NOT NULL,
    image_name text,
    image_descrip text,
    image_data text
);


ALTER TABLE image OWNER TO admin;

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

COMMENT ON TABLE image IS 'Image information';


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

CREATE TABLE imageass (
    imageass_id integer DEFAULT nextval('docass_docass_id_seq'::regclass) NOT NULL,
    imageass_source_id integer NOT NULL,
    imageass_source text NOT NULL,
    imageass_image_id integer NOT NULL,
    imageass_purpose character(1) NOT NULL,
    CONSTRAINT imageass_imageass_purpose_check CHECK (((((((((imageass_purpose = 'I'::bpchar) OR (imageass_purpose = 'E'::bpchar)) OR (imageass_purpose = 'M'::bpchar)) OR (imageass_purpose = 'P'::bpchar)) OR (imageass_purpose = 'A'::bpchar)) OR (imageass_purpose = 'C'::bpchar)) OR (imageass_purpose = 'D'::bpchar)) OR (imageass_purpose = 'S'::bpchar)))
);


ALTER TABLE imageass OWNER TO admin;

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

COMMENT ON TABLE imageass IS 'Image Assignement References';



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

CREATE VIEW accountimage AS
    SELECT crmacct.crmacct_number AS account_number, image.image_name FROM public.crmacct, public.imageass, public.image WHERE (((crmacct.crmacct_id = imageass.imageass_source_id) AND (imageass.imageass_source = 'CRMA'::text)) AND (imageass.imageass_image_id = image.image_id));


ALTER TABLE accountimage OWNER TO admin;

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

COMMENT ON VIEW accountimage IS 'Account Image';


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

CREATE VIEW address AS
    SELECT (addr.addr_number)::character varying AS address_number, addr.addr_line1 AS address1, addr.addr_line2 AS address2, addr.addr_line3 AS address3, addr.addr_city AS city, addr.addr_state AS state, addr.addr_postalcode AS postal_code, addr.addr_country AS country, addr.addr_active AS active, addr.addr_notes AS notes, ''::text AS change FROM public.addr;


ALTER TABLE address OWNER TO admin;

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

COMMENT ON VIEW address IS 'Address';


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

CREATE VIEW addresschar AS
    SELECT (addr.addr_number)::character varying AS address_number, ("char".char_name)::character varying AS characteristic, charass.charass_value AS value FROM public.addr, public."char", public.charass WHERE ((('ADDR'::text = charass.charass_target_type) AND (addr.addr_id = charass.charass_target_id)) AND (charass.charass_char_id = "char".char_id));


ALTER TABLE addresschar OWNER TO admin;

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

COMMENT ON VIEW addresschar IS 'Address Characteristics';


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

CREATE VIEW addresscomment AS
    SELECT (addr.addr_number)::character varying AS address_number, cmnttype.cmnttype_name AS type, comment.comment_date AS date, comment.comment_user AS username, comment.comment_text AS text FROM public.addr, public.cmnttype, public.comment WHERE (((comment.comment_source = 'ADDR'::text) AND (comment.comment_source_id = addr.addr_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id));


ALTER TABLE addresscomment OWNER TO admin;

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

COMMENT ON VIEW addresscomment IS 'Address Comment';



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

CREATE TABLE bomhead (
    bomhead_id integer DEFAULT nextval(('"bomhead_bomhead_id_seq"'::text)::regclass) NOT NULL,
    bomhead_item_id integer NOT NULL,
    bomhead_serial integer,
    bomhead_docnum text,
    bomhead_revision text,
    bomhead_revisiondate date,
    bomhead_batchsize numeric(18,6),
    bomhead_requiredqtyper numeric(20,8),
    bomhead_rev_id integer DEFAULT (-1),
    CONSTRAINT bomhead_bomhead_batchsize_check CHECK ((bomhead_batchsize > (0)::numeric))
);


ALTER TABLE bomhead OWNER TO admin;

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

COMMENT ON TABLE bomhead IS 'Bill of Materials (BOM) header information';



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

CREATE VIEW bom AS
    SELECT (item.item_number)::character varying AS item_number, (bomhead.bomhead_revision)::character varying AS revision, bomhead.bomhead_docnum AS document_number, bomhead.bomhead_revisiondate AS revision_date, bomhead.bomhead_batchsize AS batch_size, bomhead.bomhead_requiredqtyper AS total_qty_per FROM public.bomhead, public.item WHERE (bomhead.bomhead_item_id = item.item_id);


ALTER TABLE bom OWNER TO admin;

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

COMMENT ON VIEW bom IS 'Bill of Material Header';


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

CREATE VIEW bomitem AS
    SELECT bomitem.bomitem_id AS id, (p.item_number)::character varying AS bom_item_number, (bomhead.bomhead_revision)::character varying AS bom_revision, bomitem.bomitem_seqnumber AS sequence_number, i.item_number, CASE WHEN (bomitem.bomitem_effective = public.startoftime()) THEN 'Always'::text ELSE public.formatdate(bomitem.bomitem_effective) END AS effective, CASE WHEN (bomitem.bomitem_expires = public.endoftime()) THEN 'Never'::text ELSE public.formatdate(bomitem.bomitem_expires) END AS expires, bomitem.bomitem_qtyper AS qty_per, uom.uom_name AS issue_uom, bomitem.bomitem_scrap AS scrap, bomitem.bomitem_createwo AS create_child_wo, CASE WHEN (bomitem.bomitem_issuemethod = 'M'::bpchar) THEN 'Mixed'::text WHEN (bomitem.bomitem_issuemethod = 'L'::bpchar) THEN 'Pull'::text WHEN (bomitem.bomitem_issuemethod = 'S'::bpchar) THEN 'Push'::text ELSE NULL::text END AS issue_method, public.formatbooseq(p.item_id, bomitem.bomitem_booitem_seq_id) AS used_at, bomitem.bomitem_schedatwooper AS schedule_at_wo_operation, bomitem.bomitem_ecn AS ecn_number, bomitem.bomitem_notes AS notes, bomitem.bomitem_ref AS reference, CASE WHEN (bomitem.bomitem_subtype = 'N'::bpchar) THEN 'No'::text WHEN (bomitem.bomitem_subtype = 'I'::bpchar) THEN 'Item-Defined'::text WHEN (bomitem.bomitem_subtype = 'B'::bpchar) THEN 'BOM-Defined'::text ELSE NULL::text END AS substitutions, "char".char_name AS characteristic, bomitem.bomitem_value AS value FROM ((public.bomitem LEFT JOIN public.bomhead ON (((bomitem.bomitem_parent_item_id = bomhead.bomhead_item_id) AND (bomitem.bomitem_rev_id = bomhead.bomhead_rev_id)))) LEFT JOIN public."char" ON ((bomitem.bomitem_char_id = "char".char_id))), public.item p, public.item i, public.uom WHERE (((bomitem.bomitem_parent_item_id = p.item_id) AND (bomitem.bomitem_item_id = i.item_id)) AND (bomitem.bomitem_uom_id = uom.uom_id)) ORDER BY p.item_number, bomitem.bomitem_seqnumber;


ALTER TABLE bomitem OWNER TO admin;

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

COMMENT ON VIEW bomitem IS 'Bill of Material Item';


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

CREATE VIEW bomitemcomment AS
    SELECT bomitem.bomitem_id, cmnttype.cmnttype_name AS type, comment.comment_date AS date, comment.comment_user AS username, comment.comment_text AS text FROM (public.bomitem LEFT JOIN public.bomhead ON (((bomitem.bomitem_parent_item_id = bomhead.bomhead_item_id) AND (bomitem.bomitem_rev_id = bomhead.bomhead_rev_id)))), public.item, public.cmnttype, public.comment WHERE ((((comment.comment_source = 'BMI'::text) AND (comment.comment_source_id = bomitem.bomitem_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id)) AND (bomitem.bomitem_parent_item_id = item.item_id)) ORDER BY item.item_number, bomhead.bomhead_revision, bomitem.bomitem_seqnumber, comment.comment_date;


ALTER TABLE bomitemcomment OWNER TO admin;

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

COMMENT ON VIEW bomitemcomment IS 'Bill of Material Comment';



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

CREATE TABLE bomitemsub (
    bomitemsub_id integer NOT NULL,
    bomitemsub_bomitem_id integer NOT NULL,
    bomitemsub_item_id integer NOT NULL,
    bomitemsub_uomratio numeric(20,10) NOT NULL,
    bomitemsub_rank integer NOT NULL
);


ALTER TABLE bomitemsub OWNER TO admin;

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

COMMENT ON TABLE bomitemsub IS 'Bill of Materials (BOM) defined Substitutions information';



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

CREATE VIEW bomitemsubstitute AS
    SELECT bomitem.bomitem_id, (p.item_number)::character varying AS bom_item_number, (bomhead.bomhead_revision)::character varying AS bom_revision, bomitem.bomitem_seqnumber AS sequence_number, (s.item_number)::character varying AS substitute_item_number, bomitemsub.bomitemsub_uomratio AS sub_parent_uom_ratio, bomitemsub.bomitemsub_rank AS ranking FROM public.item p, public.item s, (public.bomitem LEFT JOIN public.bomhead ON (((bomitem.bomitem_parent_item_id = bomhead.bomhead_item_id) AND (bomitem.bomitem_rev_id = bomhead.bomhead_rev_id)))), public.bomitemsub WHERE (((p.item_id = bomitem.bomitem_parent_item_id) AND (s.item_id = bomitemsub.bomitemsub_item_id)) AND (bomitemsub.bomitemsub_bomitem_id = bomitem.bomitem_id));


ALTER TABLE bomitemsubstitute OWNER TO admin;

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

COMMENT ON VIEW bomitemsubstitute IS 'Bill of Material Item Substitute';



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

CREATE TABLE budghead (
    budghead_id integer NOT NULL,
    budghead_name text NOT NULL,
    budghead_descrip text,
    CONSTRAINT budghead_budghead_name_check CHECK ((budghead_name <> ''::text))
);


ALTER TABLE budghead OWNER TO admin;


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

CREATE VIEW budget AS
    SELECT budghead.budghead_name AS name, budghead.budghead_descrip AS description FROM public.budghead ORDER BY budghead.budghead_name;


ALTER TABLE budget OWNER TO admin;

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

COMMENT ON VIEW budget IS 'Budget Header';



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

CREATE TABLE accnt (
    accnt_id integer DEFAULT nextval(('accnt_accnt_id_seq'::text)::regclass) NOT NULL,
    accnt_number text,
    accnt_descrip text,
    accnt_comments text,
    accnt_profit text,
    accnt_sub text,
    accnt_type character(1) NOT NULL,
    accnt_extref text,
    accnt_company text,
    accnt_forwardupdate boolean,
    accnt_subaccnttype_code text,
    accnt_curr_id integer DEFAULT basecurrid(),
    accnt_active boolean DEFAULT true NOT NULL,
    accnt_name text,
    CONSTRAINT accnt_accnt_type_check CHECK ((accnt_type = ANY (ARRAY['A'::bpchar, 'E'::bpchar, 'L'::bpchar, 'Q'::bpchar, 'R'::bpchar])))
);


ALTER TABLE accnt OWNER TO admin;

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

COMMENT ON TABLE accnt IS 'General Ledger (G/L) Account Number information';


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

CREATE TABLE budgitem (
    budgitem_id integer NOT NULL,
    budgitem_budghead_id integer NOT NULL,
    budgitem_period_id integer NOT NULL,
    budgitem_accnt_id integer NOT NULL,
    budgitem_amount numeric(20,4) NOT NULL
);


ALTER TABLE budgitem OWNER TO admin;

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

CREATE TABLE period (
    period_id integer NOT NULL,
    period_start date,
    period_end date,
    period_closed boolean,
    period_freeze boolean,
    period_initial boolean DEFAULT false,
    period_name text,
    period_yearperiod_id integer,
    period_quarter integer,
    period_number integer NOT NULL
);


ALTER TABLE period OWNER TO admin;

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

COMMENT ON TABLE period IS 'Accounting Period information';



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

CREATE VIEW budgetentry AS
    SELECT budghead.budghead_name AS name, public.formatglaccount(accnt.accnt_id) AS account, period.period_start, budgitem.budgitem_amount AS amount FROM public.budgitem, public.budghead, public.period, public.accnt WHERE (((budgitem.budgitem_budghead_id = budghead.budghead_id) AND (budgitem.budgitem_period_id = period.period_id)) AND (budgitem.budgitem_accnt_id = accnt.accnt_id)) ORDER BY budghead.budghead_name, period.period_start, public.formatglaccount(accnt.accnt_id);


ALTER TABLE budgetentry OWNER TO admin;

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

COMMENT ON VIEW budgetentry IS 'Budget Entry';



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

CREATE TABLE bankaccnt (
    bankaccnt_id integer NOT NULL,
    bankaccnt_name text NOT NULL,
    bankaccnt_descrip text,
    bankaccnt_bankname text,
    bankaccnt_accntnumber text,
    bankaccnt_ar boolean,
    bankaccnt_ap boolean,
    bankaccnt_nextchknum integer,
    bankaccnt_type character(1),
    bankaccnt_accnt_id integer,
    bankaccnt_check_form_id integer,
    bankaccnt_userec boolean,
    bankaccnt_rec_accnt_id integer,
    bankaccnt_curr_id integer DEFAULT basecurrid(),
    bankaccnt_notes text,
    bankaccnt_routing text DEFAULT ''::text NOT NULL,
    bankaccnt_ach_enabled boolean DEFAULT false NOT NULL,
    bankaccnt_ach_origin text DEFAULT ''::text NOT NULL,
    bankaccnt_ach_genchecknum boolean DEFAULT false NOT NULL,
    bankaccnt_ach_leadtime integer,
    bankaccnt_ach_lastdate date,
    bankaccnt_ach_lastfileid character(1),
    bankaccnt_ach_origintype text,
    bankaccnt_ach_originname text,
    bankaccnt_ach_desttype text,
    bankaccnt_ach_fed_dest text,
    bankaccnt_ach_destname text,
    bankaccnt_ach_dest text,
    bankaccnt_prnt_check boolean DEFAULT true NOT NULL,
    CONSTRAINT bankaccnt_bankaccnt_name_check CHECK ((bankaccnt_name <> ''::text))
);


ALTER TABLE bankaccnt OWNER TO admin;

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

COMMENT ON TABLE bankaccnt IS 'Bank Account information';


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

CREATE TABLE cashrcpt (
    cashrcpt_id integer NOT NULL,
    cashrcpt_cust_id integer NOT NULL,
    cashrcpt_amount numeric(20,2) NOT NULL,
    cashrcpt_fundstype character(1) NOT NULL,
    cashrcpt_docnumber text,
    cashrcpt_bankaccnt_id integer NOT NULL,
    cashrcpt_notes text,
    cashrcpt_distdate date DEFAULT ('now'::text)::date,
    cashrcpt_salescat_id integer DEFAULT (-1),
    cashrcpt_curr_id integer DEFAULT basecurrid(),
    cashrcpt_usecustdeposit boolean DEFAULT false NOT NULL,
    cashrcpt_void boolean DEFAULT false NOT NULL,
    cashrcpt_number text NOT NULL,
    cashrcpt_docdate date,
    cashrcpt_posted boolean DEFAULT false NOT NULL,
    cashrcpt_posteddate date,
    cashrcpt_postedby text,
    cashrcpt_applydate date,
    cashrcpt_discount numeric(20,2) DEFAULT 0.00 NOT NULL,
    cashrcpt_curr_rate numeric NOT NULL,
    cashrcpt_alt_curr_rate numeric,
    CONSTRAINT cashrcpt_cashrcpt_number_check CHECK ((cashrcpt_number <> ''::text))
);


ALTER TABLE cashrcpt OWNER TO admin;

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

COMMENT ON TABLE cashrcpt IS 'Temporary table for storing Cash Receipt information before Cash Receipts are posted';



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

CREATE VIEW cashreceipt AS
    SELECT (custinfo.cust_number)::character varying AS customer_number, cashrcpt.cashrcpt_number AS cashreceipt_number, CASE WHEN (cashrcpt.cashrcpt_fundstype = 'C'::bpchar) THEN 'Check'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'T'::bpchar) THEN 'Certified Check'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'M'::bpchar) THEN 'Master Card'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'V'::bpchar) THEN 'Visa'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'A'::bpchar) THEN 'American Express'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'D'::bpchar) THEN 'Discover Card'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'R'::bpchar) THEN 'Other Credit Card'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'K'::bpchar) THEN 'Cash'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'W'::bpchar) THEN 'Wire Transfer'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'O'::bpchar) THEN 'Other'::character varying ELSE NULL::character varying END AS funds_type, (cashrcpt.cashrcpt_docnumber)::character varying AS check_document_number, custinfo.cust_name AS customer_name, m.addr_line1 AS customer_address, curr_symbol.curr_abbr AS currency, cashrcpt.cashrcpt_amount AS amount_received, bankaccnt.bankaccnt_name AS post_to, public.formatdate(cashrcpt.cashrcpt_distdate) AS distribution_date, CASE WHEN cashrcpt.cashrcpt_usecustdeposit THEN 'Customer Deposit'::text ELSE 'Credit Memo'::text END AS apply_balance_as, salescat.salescat_name AS sales_category, cashrcpt.cashrcpt_notes AS notes FROM ((((((public.cashrcpt LEFT JOIN public.custinfo ON ((custinfo.cust_id = cashrcpt.cashrcpt_cust_id))) LEFT JOIN public.cntct mc ON ((custinfo.cust_cntct_id = mc.cntct_id))) LEFT JOIN public.addr m ON ((mc.cntct_addr_id = m.addr_id))) LEFT JOIN public.curr_symbol ON ((curr_symbol.curr_id = cashrcpt.cashrcpt_curr_id))) LEFT JOIN public.bankaccnt ON ((bankaccnt.bankaccnt_id = cashrcpt.cashrcpt_bankaccnt_id))) LEFT JOIN public.salescat ON ((salescat.salescat_id = cashrcpt.cashrcpt_salescat_id)));


ALTER TABLE cashreceipt OWNER TO admin;

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

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



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

CREATE TABLE cashrcptitem (
    cashrcptitem_id integer NOT NULL,
    cashrcptitem_cashrcpt_id integer NOT NULL,
    cashrcptitem_aropen_id integer NOT NULL,
    cashrcptitem_amount numeric(20,2) NOT NULL,
    cashrcptitem_discount numeric(20,2) DEFAULT 0.00 NOT NULL,
    cashrcptitem_applied boolean DEFAULT true
);


ALTER TABLE cashrcptitem OWNER TO admin;

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

COMMENT ON TABLE cashrcptitem IS 'Temporary table for storing information about applications of Cash Receipts before Cash Receipts are posted';



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

CREATE VIEW cashreceiptapply AS
    SELECT (custinfo.cust_number)::character varying AS customer_number, CASE WHEN (cashrcpt.cashrcpt_fundstype = 'C'::bpchar) THEN 'Check'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'T'::bpchar) THEN 'Certified Check'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'M'::bpchar) THEN 'Master Card'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'V'::bpchar) THEN 'Visa'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'A'::bpchar) THEN 'American Express'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'D'::bpchar) THEN 'Discover Card'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'R'::bpchar) THEN 'Other Credit Card'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'K'::bpchar) THEN 'Cash'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'W'::bpchar) THEN 'Wire Transfer'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'O'::bpchar) THEN 'Other'::character varying ELSE NULL::character varying END AS funds_type, (cashrcpt.cashrcpt_docnumber)::character varying AS check_document_number, (aropen.aropen_doctype)::character varying AS doc_type, (aropen.aropen_docnumber)::character varying AS doc_number, custinfo.cust_name AS customer_name, m.addr_line1 AS customer_address, public.formatdate(aropen.aropen_docdate) AS doc_date, public.formatdate(aropen.aropen_duedate) AS due_date, curr_symbol.curr_abbr AS currency, aropen.aropen_amount AS open_amount, cashrcptitem.cashrcptitem_amount AS amount_to_apply, cashrcptitem.cashrcptitem_discount FROM ((((((public.cashrcptitem LEFT JOIN public.cashrcpt ON ((cashrcpt.cashrcpt_id = cashrcptitem.cashrcptitem_cashrcpt_id))) LEFT JOIN public.custinfo ON ((custinfo.cust_id = cashrcpt.cashrcpt_cust_id))) LEFT JOIN public.cntct mc ON ((custinfo.cust_cntct_id = mc.cntct_id))) LEFT JOIN public.addr m ON ((mc.cntct_addr_id = m.addr_id))) LEFT JOIN public.curr_symbol ON ((curr_symbol.curr_id = cashrcpt.cashrcpt_curr_id))) LEFT JOIN public.aropen ON ((aropen.aropen_id = cashrcptitem.cashrcptitem_aropen_id)));


ALTER TABLE cashreceiptapply OWNER TO admin;

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

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



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

CREATE TABLE cashrcptmisc (
    cashrcptmisc_id integer NOT NULL,
    cashrcptmisc_cashrcpt_id integer NOT NULL,
    cashrcptmisc_accnt_id integer NOT NULL,
    cashrcptmisc_amount numeric(20,2) NOT NULL,
    cashrcptmisc_notes text
);


ALTER TABLE cashrcptmisc OWNER TO admin;

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

COMMENT ON TABLE cashrcptmisc IS 'Cash Receipt Miscellaneous Application information';



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

CREATE VIEW cashreceiptapplymisc AS
    SELECT (custinfo.cust_number)::character varying AS customer_number, CASE WHEN (cashrcpt.cashrcpt_fundstype = 'C'::bpchar) THEN 'Check'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'T'::bpchar) THEN 'Certified Check'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'M'::bpchar) THEN 'Master Card'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'V'::bpchar) THEN 'Visa'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'A'::bpchar) THEN 'American Express'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'D'::bpchar) THEN 'Discover Card'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'R'::bpchar) THEN 'Other Credit Card'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'K'::bpchar) THEN 'Cash'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'W'::bpchar) THEN 'Wire Transfer'::character varying WHEN (cashrcpt.cashrcpt_fundstype = 'O'::bpchar) THEN 'Other'::character varying ELSE NULL::character varying END AS funds_type, (cashrcpt.cashrcpt_docnumber)::character varying AS check_document_number, (public.formatglaccount(accnt.accnt_id))::character varying AS account, custinfo.cust_name AS customer_name, m.addr_line1 AS customer_address, accnt.accnt_descrip AS account_description, curr_symbol.curr_abbr AS currency, cashrcptmisc.cashrcptmisc_amount AS amount_to_distribute, cashrcptmisc.cashrcptmisc_notes AS notes FROM ((((((public.cashrcptmisc LEFT JOIN public.cashrcpt ON ((cashrcpt.cashrcpt_id = cashrcptmisc.cashrcptmisc_cashrcpt_id))) LEFT JOIN public.custinfo ON ((custinfo.cust_id = cashrcpt.cashrcpt_cust_id))) LEFT JOIN public.cntct mc ON ((custinfo.cust_cntct_id = mc.cntct_id))) LEFT JOIN public.addr m ON ((mc.cntct_addr_id = m.addr_id))) LEFT JOIN public.curr_symbol ON ((curr_symbol.curr_id = cashrcpt.cashrcpt_curr_id))) LEFT JOIN public.accnt ON ((accnt.accnt_id = cashrcptmisc.cashrcptmisc_accnt_id)));


ALTER TABLE cashreceiptapplymisc OWNER TO admin;

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

COMMENT ON VIEW cashreceiptapplymisc IS '
This view can be used as an interface to import Cash Receipt Miscellaneous Application
data directly into the system.  Required fields will be checked and default values
will be populated';


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

CREATE VIEW contact AS
    SELECT (cntct.cntct_number)::character varying AS contact_number, cntct.cntct_honorific AS honorific, cntct.cntct_first_name AS first, cntct.cntct_middle AS middle, cntct.cntct_last_name AS last, cntct.cntct_suffix AS suffix, cntct.cntct_initials AS initials, crmacct.crmacct_number AS crm_account, cntct.cntct_active AS active, cntct.cntct_title AS job_title, cntct.cntct_phone AS voice, cntct.cntct_phone2 AS alternate, cntct.cntct_fax AS fax, cntct.cntct_email AS email, cntct.cntct_webaddr AS web, ''::text AS contact_change, addr.addr_number AS address_number, addr.addr_line1 AS address1, addr.addr_line2 AS address2, addr.addr_line3 AS address3, addr.addr_city AS city, addr.addr_state AS state, addr.addr_postalcode AS postal_code, addr.addr_country AS country, cntct.cntct_notes AS notes, ''::text AS address_change FROM ((public.cntct LEFT JOIN public.addr ON ((cntct.cntct_addr_id = addr.addr_id))) LEFT JOIN public.crmacct ON ((cntct.cntct_crmacct_id = crmacct.crmacct_id)));


ALTER TABLE contact OWNER TO admin;

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

COMMENT ON VIEW contact IS 'Contact';


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

CREATE VIEW contactchar AS
    SELECT cntct.cntct_number AS contact_number, ("char".char_name)::character varying AS characteristic, charass.charass_value AS value FROM public.cntct, public."char", public.charass WHERE ((('CNTCT'::text = charass.charass_target_type) AND (cntct.cntct_id = charass.charass_target_id)) AND (charass.charass_char_id = "char".char_id));


ALTER TABLE contactchar OWNER TO admin;

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

COMMENT ON VIEW contactchar IS 'Contact Characteristics';


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

CREATE VIEW contactcomment AS
    SELECT cntct.cntct_number AS contact_number, cmnttype.cmnttype_name AS type, comment.comment_date AS date, comment.comment_user AS username, comment.comment_text AS text FROM public.cntct, public.cmnttype, public.comment WHERE (((comment.comment_source = 'T'::text) AND (comment.comment_source_id = cntct.cntct_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id));


ALTER TABLE contactcomment OWNER TO admin;

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

COMMENT ON VIEW contactcomment IS 'Contact Comment';


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

CREATE VIEW custchar AS
    SELECT (custinfo.cust_number)::character varying AS customer_number, ("char".char_name)::character varying AS characteristic, charass.charass_value AS value FROM public.custinfo, public."char", public.charass WHERE ((('C'::text = charass.charass_target_type) AND (custinfo.cust_id = charass.charass_target_id)) AND (charass.charass_char_id = "char".char_id));


ALTER TABLE custchar OWNER TO admin;

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

COMMENT ON VIEW custchar IS 'Customer Characteristics';


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

CREATE VIEW custcomment AS
    SELECT (custinfo.cust_number)::character varying AS customer_number, cmnttype.cmnttype_name AS type, comment.comment_date AS date, comment.comment_user AS username, comment.comment_text AS text FROM public.custinfo, public.cmnttype, public.comment WHERE (((comment.comment_source = 'C'::text) AND (comment.comment_source_id = custinfo.cust_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id));


ALTER TABLE custcomment OWNER TO admin;

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

COMMENT ON VIEW custcomment IS 'Customer Comment';



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

CREATE TABLE ccard (
    ccard_id integer NOT NULL,
    ccard_seq integer DEFAULT 10 NOT NULL,
    ccard_cust_id integer NOT NULL,
    ccard_active boolean DEFAULT true,
    ccard_name bytea,
    ccard_address1 bytea,
    ccard_address2 bytea,
    ccard_city bytea,
    ccard_state bytea,
    ccard_zip bytea,
    ccard_country bytea,
    ccard_number bytea,
    ccard_debit boolean DEFAULT false,
    ccard_month_expired bytea,
    ccard_year_expired bytea,
    ccard_type character(1) NOT NULL,
    ccard_date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL,
    ccard_lastupdated timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL,
    ccard_added_by_username text DEFAULT geteffectivextuser() NOT NULL,
    ccard_last_updated_by_username text DEFAULT geteffectivextuser() NOT NULL
);


ALTER TABLE ccard OWNER TO admin;

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

COMMENT ON TABLE ccard IS 'Credit Card Information - all bytea data is encrypted';



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

CREATE VIEW custcreditcard AS
    SELECT (custinfo.cust_number)::character varying AS customer_number, CASE WHEN (ccard.ccard_type = 'V'::bpchar) THEN 'Visa'::text WHEN (ccard.ccard_type = 'M'::bpchar) THEN 'Master Card'::text WHEN (ccard.ccard_type = 'A'::bpchar) THEN 'American Express'::text WHEN (ccard.ccard_type = 'D'::bpchar) THEN 'Discover'::text ELSE 'Not Supported'::text END AS credit_card_type, ccard.ccard_active AS active, ccard.ccard_number AS credit_card_number, ccard.ccard_name AS name, ccard.ccard_address1 AS street_address1, ccard.ccard_address2 AS street_address2, ccard.ccard_city AS city, ccard.ccard_state AS state, ccard.ccard_zip AS postal_code, ccard.ccard_country AS country, ccard.ccard_month_expired AS expiration_month, ccard.ccard_year_expired AS expiration_year, ''::text AS key FROM public.ccard, public.custinfo WHERE (ccard.ccard_cust_id = custinfo.cust_id);


ALTER TABLE custcreditcard OWNER TO admin;

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

COMMENT ON VIEW custcreditcard IS 'Customer Credit Cards.';



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

CREATE TABLE custtype (
    custtype_id integer DEFAULT nextval(('custtype_custtype_id_seq'::text)::regclass) NOT NULL,
    custtype_code text NOT NULL,
    custtype_descrip text NOT NULL,
    custtype_char boolean DEFAULT false NOT NULL,
    CONSTRAINT custtype_custtype_code_check CHECK ((custtype_code <> ''::text))
);


ALTER TABLE custtype OWNER TO admin;

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

COMMENT ON TABLE custtype IS 'Customer Type information';


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

CREATE TABLE shipchrg (
    shipchrg_id integer NOT NULL,
    shipchrg_name text NOT NULL,
    shipchrg_descrip text,
    shipchrg_custfreight boolean,
    shipchrg_handling character(1),
    CONSTRAINT shipchrg_shipchrg_name_check CHECK ((shipchrg_name <> ''::text))
);


ALTER TABLE shipchrg OWNER TO admin;

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

COMMENT ON TABLE shipchrg IS 'Shipping Charge information';


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

CREATE TABLE shipform (
    shipform_id integer DEFAULT nextval(('"shipform_shipform_id_seq"'::text)::regclass) NOT NULL,
    shipform_name text NOT NULL,
    shipform_report_id integer,
    shipform_report_name text,
    CONSTRAINT shipform_shipform_name_check CHECK ((shipform_name <> ''::text))
);


ALTER TABLE shipform OWNER TO admin;

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

COMMENT ON TABLE shipform IS 'Shipping Form information';


--
-- Name: COLUMN shipform.shipform_report_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN shipform.shipform_report_id IS 'Obsolete -- reference shipform_report_name instead.';



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

CREATE VIEW customer AS
    SELECT (custinfo.cust_number)::character varying AS customer_number, custtype.custtype_code AS customer_type, custinfo.cust_name AS customer_name, custinfo.cust_active AS active, salesrep.salesrep_number AS sales_rep, (custinfo.cust_commprcnt * (100)::numeric) AS commission, custinfo.cust_shipvia AS ship_via, shipform.shipform_name AS ship_form, shipchrg.shipchrg_name AS shipping_charges, custinfo.cust_backorder AS accepts_backorders, custinfo.cust_partialship AS accepts_partial_shipments, custinfo.cust_ffshipto AS allow_free_form_shipto, custinfo.cust_ffbillto AS allow_free_form_billto, whsinfo.warehous_code AS preferred_selling_site, taxzone.taxzone_code AS default_tax_zone, terms.terms_code AS default_terms, CASE WHEN (custinfo.cust_balmethod = 'B'::bpchar) THEN 'Balance Forward'::text ELSE 'Open Item'::text END AS balance_method, custinfo.cust_discntprcnt AS default_discount, dc.curr_abbr AS default_currency, clc.curr_abbr AS credit_limit_currency, custinfo.cust_creditlmt AS credit_limit, CASE WHEN (COALESCE(custinfo.cust_gracedays, 0) > 0) THEN custinfo.cust_gracedays ELSE NULL::integer END AS alternate_grace_days, custinfo.cust_creditrating AS credit_rating, CASE WHEN (custinfo.cust_creditstatus = 'G'::bpchar) THEN 'In Good Standing'::text WHEN (custinfo.cust_creditstatus = 'W'::bpchar) THEN 'On Credit Warning'::text ELSE 'On Credit Hold'::text END AS credit_status, custinfo.cust_autoupdatestatus AS credit_status_exceed_warn, custinfo.cust_autoholdorders AS credit_status_exceed_hold, custinfo.cust_usespos AS uses_purchase_orders, custinfo.cust_blanketpos AS uses_blanket_pos, mc.cntct_number AS billing_contact_number, mc.cntct_honorific AS billing_contact_honorific, mc.cntct_first_name AS billing_contact_first, mc.cntct_middle AS billing_contact_middle, mc.cntct_last_name AS billing_contact_last, mc.cntct_suffix AS billing_contact_suffix, mc.cntct_title AS billing_contact_job_title, mc.cntct_phone AS billing_contact_voice, mc.cntct_phone2 AS billing_contact_alternate, mc.cntct_fax AS billing_contact_fax, mc.cntct_email AS billing_contact_email, mc.cntct_webaddr AS billing_contact_web, ''::text AS billing_contact_change, m.addr_number AS billing_contact_address_number, m.addr_line1 AS billing_contact_address1, m.addr_line2 AS billing_contact_address2, m.addr_line3 AS billing_contact_address3, m.addr_city AS billing_contact_city, m.addr_state AS billing_contact_state, m.addr_postalcode AS billing_contact_postalcode, m.addr_country AS billing_contact_country, ''::text AS billing_contact_address_change, cc.cntct_number AS correspond_contact_number, cc.cntct_honorific AS correspond_contact_honorific, cc.cntct_first_name AS correspond_contact_first, cc.cntct_middle AS correspond_contact_middle, cc.cntct_last_name AS correspond_contact_last, cc.cntct_suffix AS correspond_contact_suffix, cc.cntct_title AS correspond_contact_job_title, cc.cntct_phone AS correspond_contact_voice, cc.cntct_phone2 AS correspond_contact_alternate, cc.cntct_fax AS correspond_contact_fax, cc.cntct_email AS correspond_contact_email, cc.cntct_webaddr AS correspond_contact_web, ''::text AS correspond_contact_change, c.addr_number AS correspond_contact_address_number, c.addr_line1 AS correspond_contact_address1, c.addr_line2 AS correspond_contact_address2, c.addr_line3 AS correspond_contact_address3, c.addr_city AS correspond_contact_city, c.addr_state AS correspond_contact_state, c.addr_postalcode AS correspond_contact_postalcode, c.addr_country AS correspond_contact_country, ''::text AS correspond_contact_address_change, custinfo.cust_comments AS notes FROM ((((((((public.custinfo LEFT JOIN public.shipchrg ON ((custinfo.cust_shipchrg_id = shipchrg.shipchrg_id))) LEFT JOIN public.whsinfo ON ((custinfo.cust_preferred_warehous_id = whsinfo.warehous_id))) LEFT JOIN public.cntct mc ON ((custinfo.cust_cntct_id = mc.cntct_id))) LEFT JOIN public.addr m ON ((mc.cntct_addr_id = m.addr_id))) LEFT JOIN public.cntct cc ON ((custinfo.cust_corrcntct_id = cc.cntct_id))) LEFT JOIN public.addr c ON ((cc.cntct_addr_id = c.addr_id))) LEFT JOIN public.taxzone ON ((custinfo.cust_taxzone_id = taxzone.taxzone_id))) LEFT JOIN public.shipform ON ((custinfo.cust_shipform_id = shipform.shipform_id))), public.custtype, public.salesrep, public.curr_symbol dc, public.curr_symbol clc, public.terms WHERE (((((custinfo.cust_custtype_id = custtype.custtype_id) AND (custinfo.cust_salesrep_id = salesrep.salesrep_id)) AND (custinfo.cust_curr_id = dc.curr_id)) AND (custinfo.cust_creditlmt_curr_id = clc.curr_id)) AND (custinfo.cust_terms_id = terms.terms_id));


ALTER TABLE customer OWNER TO admin;

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

COMMENT ON VIEW customer IS 'Customer';



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

CREATE TABLE taxauth (
    taxauth_id integer NOT NULL,
    taxauth_code text NOT NULL,
    taxauth_name text,
    taxauth_extref text,
    taxauth_addr_id integer,
    taxauth_curr_id integer,
    taxauth_county text,
    taxauth_accnt_id integer,
    CONSTRAINT taxauth_taxauth_code_check CHECK ((taxauth_code <> ''::text))
);


ALTER TABLE taxauth OWNER TO admin;

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

COMMENT ON TABLE taxauth IS 'The Tax Authority table.';


--
-- Name: COLUMN taxauth.taxauth_curr_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxauth.taxauth_curr_id IS 'The required currency for recording tax information as. NULL means no preference.';


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

CREATE TABLE taxreg (
    taxreg_id integer NOT NULL,
    taxreg_rel_id integer NOT NULL,
    taxreg_rel_type character(1),
    taxreg_taxauth_id integer,
    taxreg_number text NOT NULL,
    taxreg_taxzone_id integer,
    taxreg_effective date DEFAULT startoftime(),
    taxreg_expires date DEFAULT endoftime(),
    taxreg_notes text DEFAULT ''::text,
    CONSTRAINT taxreg_taxreg_number_check CHECK ((taxreg_number <> ''::text))
);


ALTER TABLE taxreg OWNER TO admin;

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

COMMENT ON TABLE taxreg IS 'Stores Tax Registration numbers related to objects and a given tax authority. The rel_id specifies the object id and teh rel_type specifies the object type. See column comment for additional detail on types.';


--
-- Name: COLUMN taxreg.taxreg_rel_type; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxreg.taxreg_rel_type IS 'The type of relation this record is for. Known values are C=Customer, V=Vendor, NULL=This Manufacturer in which case taxreg_rel_id is meaningless and should be -1.';



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

CREATE VIEW customertaxreg AS
    SELECT (custinfo.cust_number)::character varying AS customer_number, (COALESCE(taxzone.taxzone_code, 'Any'::text))::character varying AS tax_zone, (taxauth.taxauth_code)::character varying AS tax_authority, taxreg.taxreg_number AS registration_number, CASE WHEN (taxreg.taxreg_effective = public.startoftime()) THEN 'Always'::text ELSE public.formatdate(taxreg.taxreg_effective) END AS start_date, CASE WHEN (taxreg.taxreg_expires = public.endoftime()) THEN 'Never'::text ELSE public.formatdate(taxreg.taxreg_expires) END AS end_date, taxreg.taxreg_notes AS notes FROM (((public.taxreg LEFT JOIN public.custinfo ON ((custinfo.cust_id = taxreg.taxreg_rel_id))) LEFT JOIN public.taxauth ON ((taxauth.taxauth_id = taxreg.taxreg_taxauth_id))) LEFT JOIN public.taxzone ON ((taxzone.taxzone_id = taxreg.taxreg_taxzone_id))) WHERE (taxreg.taxreg_rel_type = 'C'::bpchar) ORDER BY custinfo.cust_number, taxreg.taxreg_number;


ALTER TABLE customertaxreg OWNER TO admin;

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

COMMENT ON VIEW customertaxreg IS 'Customer Tax Registrations';


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

CREATE VIEW customertype AS
    SELECT (custtype.custtype_code)::character varying AS code, custtype.custtype_descrip AS description, custtype.custtype_char AS enable_characteristics_profile FROM public.custtype ORDER BY custtype.custtype_code;


ALTER TABLE customertype OWNER TO admin;

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

COMMENT ON VIEW customertype IS 'Customer Type';


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

CREATE VIEW customertypechar AS
    SELECT (custtype.custtype_code)::character varying AS customer_type, ("char".char_name)::character varying AS characteristic, charass.charass_value AS value, charass.charass_default AS is_default FROM public.custtype, public."char", public.charass WHERE ((('CT'::text = charass.charass_target_type) AND (custtype.custtype_id = charass.charass_target_id)) AND (charass.charass_char_id = "char".char_id));


ALTER TABLE customertypechar OWNER TO admin;

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

COMMENT ON VIEW customertypechar IS 'Customer Type Characteristics';


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

CREATE VIEW custshipto AS
    SELECT (custinfo.cust_number)::character varying AS customer_number, (shiptoinfo.shipto_num)::character varying AS shipto_number, shiptoinfo.shipto_active AS active, shiptoinfo.shipto_name AS name, shiptoinfo.shipto_default AS default_flag, addr.addr_number AS address_number, addr.addr_line1 AS address1, addr.addr_line2 AS address2, addr.addr_line3 AS address3, addr.addr_city AS city, addr.addr_state AS state, addr.addr_postalcode AS postal_code, addr.addr_country AS country, ''::text AS address_change, cntct.cntct_number AS contact_number, cntct.cntct_honorific AS honorific, cntct.cntct_first_name AS first, cntct.cntct_middle AS middle, cntct.cntct_last_name AS last, cntct.cntct_suffix AS suffix, cntct.cntct_title AS job_title, cntct.cntct_phone AS phone, cntct.cntct_fax AS fax, cntct.cntct_email AS email, ''::text AS contact_change, salesrep.salesrep_number AS sales_rep, (shiptoinfo.shipto_commission * 100.0) AS commission, shipzone.shipzone_name AS zone, taxzone.taxzone_code AS tax_zone, shiptoinfo.shipto_shipvia AS ship_via, shipform.shipform_name AS ship_form, shipchrg.shipchrg_name AS shipping_charges, CASE WHEN (shiptoinfo.shipto_ediprofile_id = (-1)) THEN 'No EDI'::text WHEN (shiptoinfo.shipto_ediprofile_id = (-2)) THEN 'Use Customer Master'::text ELSE public.getediprofilename(shiptoinfo.shipto_ediprofile_id) END AS edi_profile, shiptoinfo.shipto_comments AS general_notes, shiptoinfo.shipto_shipcomments AS shipping_notes FROM public.custinfo, ((((((public.shiptoinfo LEFT JOIN public.shipchrg ON ((shiptoinfo.shipto_shipchrg_id = shipchrg.shipchrg_id))) LEFT JOIN public.cntct ON ((shiptoinfo.shipto_cntct_id = cntct.cntct_id))) LEFT JOIN public.addr ON ((shiptoinfo.shipto_addr_id = addr.addr_id))) LEFT JOIN public.taxzone ON ((shiptoinfo.shipto_taxzone_id = taxzone.taxzone_id))) LEFT JOIN public.shipzone ON ((shiptoinfo.shipto_shipzone_id = shipzone.shipzone_id))) LEFT JOIN public.salesrep ON ((shiptoinfo.shipto_salesrep_id = salesrep.salesrep_id))), public.shipform WHERE ((custinfo.cust_id = shiptoinfo.shipto_cust_id) AND (custinfo.cust_shipform_id = shipform.shipform_id));


ALTER TABLE custshipto OWNER TO admin;

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

COMMENT ON VIEW custshipto IS 'Customer Shipto Address';


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

CREATE VIEW custtax AS
    SELECT (custinfo.cust_number)::character varying AS customer_number, (taxauth.taxauth_code)::character varying AS tax_authority, taxreg.taxreg_number AS registration_number FROM public.custinfo, public.taxauth, public.taxreg WHERE (((taxreg.taxreg_rel_type = 'C'::bpchar) AND (taxreg.taxreg_rel_id = custinfo.cust_id)) AND (taxreg.taxreg_taxauth_id = taxauth.taxauth_id));


ALTER TABLE custtax OWNER TO admin;

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

COMMENT ON VIEW custtax IS 'Customer Tax Registration';



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

CREATE TABLE dept (
    dept_id integer NOT NULL,
    dept_number text NOT NULL,
    dept_name text NOT NULL,
    CONSTRAINT dept_dept_number_check CHECK ((dept_number <> ''::text))
);


ALTER TABLE dept OWNER TO admin;

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

COMMENT ON TABLE dept IS 'List of Departments';


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

CREATE TABLE emp (
    emp_id integer NOT NULL,
    emp_code text NOT NULL,
    emp_number text NOT NULL,
    emp_active boolean DEFAULT true NOT NULL,
    emp_cntct_id integer,
    emp_warehous_id integer,
    emp_mgr_emp_id integer,
    emp_wage_type text NOT NULL,
    emp_wage numeric,
    emp_wage_curr_id integer DEFAULT basecurrid(),
    emp_wage_period text NOT NULL,
    emp_dept_id integer,
    emp_shift_id integer,
    emp_notes text,
    emp_image_id integer,
    emp_username text,
    emp_extrate numeric,
    emp_extrate_period text NOT NULL,
    emp_startdate date DEFAULT ('now'::text)::date,
    emp_name text NOT NULL,
    CONSTRAINT emp_check CHECK (((((COALESCE(emp_wage_type, ''::text) = ''::text) OR (COALESCE(emp_wage_type, ''::text) = 'H'::text)) OR (COALESCE(emp_wage_type, ''::text) = 'S'::text)) AND ((COALESCE(emp_wage, (0)::numeric) = (0)::numeric) OR ((COALESCE(emp_wage_type, ''::text) <> ''::text) AND (emp_wage IS NOT NULL))))),
    CONSTRAINT emp_emp_code_check CHECK ((emp_code <> ''::text)),
    CONSTRAINT emp_emp_number_check CHECK ((emp_number <> ''::text)),
    CONSTRAINT emp_emp_wage_period_check CHECK ((((((((COALESCE(emp_wage_period, ''::text) = ''::text) OR (COALESCE(emp_wage_period, ''::text) = 'H'::text)) OR (COALESCE(emp_wage_period, ''::text) = 'D'::text)) OR (COALESCE(emp_wage_period, ''::text) = 'W'::text)) OR (COALESCE(emp_wage_period, ''::text) = 'BW'::text)) OR (COALESCE(emp_wage_period, ''::text) = 'M'::text)) OR (COALESCE(emp_wage_period, ''::text) = 'Y'::text)))
);


ALTER TABLE emp OWNER TO admin;

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

COMMENT ON TABLE emp IS 'Employee table describing the basic properties of an employee. Employees need not be system users.';


--
-- Name: COLUMN emp.emp_code; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN emp.emp_code IS 'Short, human-readable name for employee. This value is kept synchronized with usr_username and salesrep_number, and so is unique across all three tables emp, usr, and salesrep.';


--
-- Name: COLUMN emp.emp_number; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN emp.emp_number IS 'Official employee number. This might be used for ID badges, payroll accounting, or other purposes.';


--
-- Name: COLUMN emp.emp_mgr_emp_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN emp.emp_mgr_emp_id IS 'Internal ID of this employee''s manager/supervisor.';


--
-- Name: COLUMN emp.emp_wage_type; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN emp.emp_wage_type IS 'The nature of the wage or employment agreement. ''H'' indicates this employee is paid on an hourly basis (or some other period) while ''S'' indicates this employee is salaried.';


--
-- Name: COLUMN emp.emp_wage_period; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN emp.emp_wage_period IS 'The periodicity of wage payment: ''H'' for hourly, ''D'' for daily, ''W'' for weekly, ''BW'' for biweekly, ''M'' for monthly, ''Y'' for yearly.';


--
-- Name: COLUMN emp.emp_username; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN emp.emp_username IS 'DEPRECATED - the relationship between Employee and User is now maintained through the crmacct table.';


--
-- Name: COLUMN emp.emp_extrate_period; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN emp.emp_extrate_period IS 'The periodicity of external rate payment: ''H'' for hourly, ''D'' for daily, ''W'' for weekly, ''BW'' for biweekly, ''M'' for monthly, ''Y'' for yearly.';


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

CREATE TABLE shift (
    shift_id integer NOT NULL,
    shift_number text NOT NULL,
    shift_name text NOT NULL,
    CONSTRAINT shift_shift_number_check CHECK ((shift_number <> ''::text))
);


ALTER TABLE shift OWNER TO admin;

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

COMMENT ON TABLE shift IS 'List of work Shifts';



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

CREATE VIEW employee AS
    SELECT (e.emp_code)::character varying AS code, (e.emp_number)::character varying AS number, e.emp_active AS active, e.emp_startdate AS start_date, cntct.cntct_number AS contact_number, cntct.cntct_honorific AS honorific, cntct.cntct_first_name AS first, cntct.cntct_middle AS middle, cntct.cntct_last_name AS last, cntct.cntct_suffix AS suffix, cntct.cntct_title AS job_title, cntct.cntct_phone AS voice, cntct.cntct_phone2 AS alternate, cntct.cntct_fax AS fax, cntct.cntct_email AS email, cntct.cntct_webaddr AS web, ''::text AS contact_change, addr.addr_number AS address_number, addr.addr_line1 AS address1, addr.addr_line2 AS address2, addr.addr_line3 AS address3, addr.addr_city AS city, addr.addr_state AS state, addr.addr_postalcode AS postalcode, addr.addr_country AS country, ''::text AS address_change, whsinfo.warehous_code AS site, m.emp_code AS manager_code, CASE WHEN (e.emp_wage_type = 'H'::text) THEN 'Hourly'::text WHEN (e.emp_wage_type = 'S'::text) THEN 'Salaried'::text WHEN (e.emp_wage_type IS NULL) THEN NULL::text ELSE 'Error'::text END AS wage_type, e.emp_wage AS wage, curr_symbol.curr_abbr AS wage_currency, CASE WHEN (e.emp_wage_period = 'H'::text) THEN 'Hour'::text WHEN (e.emp_wage_period = 'D'::text) THEN 'Day'::text WHEN (e.emp_wage_period = 'W'::text) THEN 'Week'::text WHEN (e.emp_wage_period = 'BW'::text) THEN 'Biweek'::text WHEN (e.emp_wage_period = 'M'::text) THEN 'Month'::text WHEN (e.emp_wage_period = 'Y'::text) THEN 'Year'::text WHEN (e.emp_wage_period IS NULL) THEN NULL::text ELSE 'Error'::text END AS wage_period, dept.dept_number AS department, shift.shift_number AS shift, (crmacct.crmacct_usr_username IS NOT NULL) AS is_user, (salesrep.salesrep_id IS NOT NULL) AS is_salesrep, (vendinfo.vend_id IS NOT NULL) AS is_vendor, e.emp_notes AS notes, image.image_name AS image, e.emp_extrate AS rate, curr_symbol.curr_abbr AS billing_currency, CASE WHEN (e.emp_extrate_period = 'H'::text) THEN 'Hour'::text WHEN (e.emp_extrate_period = 'D'::text) THEN 'Day'::text WHEN (e.emp_extrate_period = 'W'::text) THEN 'Week'::text WHEN (e.emp_extrate_period = 'BW'::text) THEN 'Biweek'::text WHEN (e.emp_extrate_period = 'M'::text) THEN 'Month'::text WHEN (e.emp_extrate_period = 'Y'::text) THEN 'Year'::text WHEN (e.emp_extrate_period IS NULL) THEN NULL::text ELSE 'Error'::text END AS billing_period FROM (((((((((((public.emp e JOIN public.crmacct ON ((e.emp_id = crmacct.crmacct_emp_id))) LEFT JOIN public.cntct ON ((e.emp_cntct_id = cntct.cntct_id))) LEFT JOIN public.addr ON ((cntct.cntct_addr_id = addr.addr_id))) LEFT JOIN public.whsinfo ON ((e.emp_warehous_id = whsinfo.warehous_id))) LEFT JOIN public.emp m ON ((e.emp_mgr_emp_id = m.emp_id))) LEFT JOIN public.dept ON ((e.emp_dept_id = dept.dept_id))) LEFT JOIN public.shift ON ((e.emp_shift_id = shift.shift_id))) LEFT JOIN public.salesrep ON ((crmacct.crmacct_salesrep_id = salesrep.salesrep_id))) LEFT JOIN public.vendinfo ON ((crmacct.crmacct_vend_id = vendinfo.vend_id))) LEFT JOIN public.image ON ((e.emp_image_id = image.image_id))) LEFT JOIN public.curr_symbol ON ((e.emp_wage_curr_id = curr_symbol.curr_id)));


ALTER TABLE employee OWNER TO admin;

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

COMMENT ON VIEW employee IS 'Employee';


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

CREATE VIEW employeechar AS
    SELECT (emp.emp_code)::character varying AS employee_code, ("char".char_name)::character varying AS characteristic, charass.charass_value AS value FROM public.emp, public."char", public.charass WHERE ((('EMP'::text = charass.charass_target_type) AND (emp.emp_id = charass.charass_target_id)) AND (charass.charass_char_id = "char".char_id));


ALTER TABLE employeechar OWNER TO admin;

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

COMMENT ON VIEW employeechar IS 'Employee Characteristics';


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

CREATE VIEW employeecomment AS
    SELECT (emp.emp_code)::character varying AS code, (cmnttype.cmnttype_name)::character varying AS type, comment.comment_date AS date, comment.comment_user AS username, comment.comment_text AS text FROM public.emp, public.cmnttype, public.comment WHERE (((comment.comment_source = 'EMP'::text) AND (comment.comment_source_id = emp.emp_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id));


ALTER TABLE employeecomment OWNER TO admin;

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

COMMENT ON VIEW employeecomment IS 'Employee Comment';



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

CREATE TABLE shipdata (
    shipdata_cohead_number text NOT NULL,
    shipdata_cosmisc_tracknum text NOT NULL,
    shipdata_cosmisc_packnum_tracknum text NOT NULL,
    shipdata_weight numeric(16,4),
    shipdata_base_freight numeric(16,4),
    shipdata_total_freight numeric(16,4),
    shipdata_shipper text DEFAULT 'UPS'::text,
    shipdata_billing_option text,
    shipdata_package_type text,
    shipdata_void_ind character(1) NOT NULL,
    shipdata_lastupdated timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL,
    shipdata_shiphead_number text,
    shipdata_base_freight_curr_id integer DEFAULT basecurrid(),
    shipdata_total_freight_curr_id integer DEFAULT basecurrid()
);


ALTER TABLE shipdata OWNER TO admin;

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

COMMENT ON TABLE shipdata IS 'Shipping Interface information - note that the shipdata_cohead_nember is text and not int.  That is due to ODBC chopping off during the transfer';



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

CREATE VIEW extshipmaint AS
    SELECT (shipdata.shipdata_cohead_number)::character varying AS so_number, (shipdata.shipdata_shiphead_number)::character varying AS shipment_number, (shipdata.shipdata_shipper)::character varying AS shipper, (shipdata.shipdata_cosmisc_packnum_tracknum)::character varying AS package_tracking_number, shipdata.shipdata_void_ind AS void, shipdata.shipdata_billing_option AS billing_option, shipdata.shipdata_weight AS weight, shipdata.shipdata_base_freight AS base_freight, base.curr_abbr AS base_freight_currency, shipdata.shipdata_total_freight AS total_freight, total.curr_abbr AS total_freight_currency, shipdata.shipdata_package_type AS package_type, shipdata.shipdata_cosmisc_tracknum AS tracking_number, shipdata.shipdata_lastupdated AS last_updated FROM public.shipdata, public.curr_symbol base, public.curr_symbol total WHERE ((shipdata.shipdata_base_freight_curr_id = base.curr_id) AND (shipdata.shipdata_total_freight_curr_id = total.curr_id)) ORDER BY shipdata.shipdata_cohead_number, shipdata.shipdata_shiphead_number;


ALTER TABLE extshipmaint OWNER TO admin;

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

COMMENT ON VIEW extshipmaint IS 'External Shipping Maintenance';



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

CREATE TABLE freightclass (
    freightclass_id integer NOT NULL,
    freightclass_code text NOT NULL,
    freightclass_descrip text,
    CONSTRAINT freightclass_freightclass_code_check CHECK ((freightclass_code <> ''::text))
);


ALTER TABLE freightclass OWNER TO admin;

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

COMMENT ON TABLE freightclass IS 'This table is the freight price schedules.';


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

CREATE TABLE ipsfreight (
    ipsfreight_id integer NOT NULL,
    ipsfreight_ipshead_id integer NOT NULL,
    ipsfreight_qtybreak numeric DEFAULT 0 NOT NULL,
    ipsfreight_price numeric DEFAULT 0 NOT NULL,
    ipsfreight_type character(1) NOT NULL,
    ipsfreight_warehous_id integer,
    ipsfreight_shipzone_id integer,
    ipsfreight_freightclass_id integer,
    ipsfreight_shipvia text
);


ALTER TABLE ipsfreight OWNER TO admin;

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

CREATE TABLE ipshead (
    ipshead_id integer DEFAULT nextval(('"ipshead_ipshead_id_seq"'::text)::regclass) NOT NULL,
    ipshead_name text NOT NULL,
    ipshead_descrip text,
    ipshead_effective date,
    ipshead_expires date,
    ipshead_curr_id integer DEFAULT basecurrid() NOT NULL,
    ipshead_updated date,
    CONSTRAINT ipshead_ipshead_name_check CHECK ((ipshead_name <> ''::text))
);


ALTER TABLE ipshead OWNER TO admin;

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

COMMENT ON TABLE ipshead IS 'Pricing Schedule header information';



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

CREATE VIEW freightpricingscheduleitem AS
    SELECT (ipshead.ipshead_name)::character varying AS pricing_schedule, ipsfreight.ipsfreight_qtybreak AS qty_break, (qtyuom.uom_name)::character varying AS qty_uom, ipsfreight.ipsfreight_price AS price, CASE WHEN (ipsfreight.ipsfreight_type = 'F'::bpchar) THEN 'Flat Rate'::text ELSE 'Price Per UOM'::text END AS price_type, COALESCE(whsinfo.warehous_code, 'Any'::text) AS from_site, COALESCE(shipzone.shipzone_name, 'Any'::text) AS to_shipzone, COALESCE(ipsfreight.ipsfreight_shipvia, 'Any'::text) AS ship_via, COALESCE(freightclass.freightclass_code, 'Any'::text) AS freight_class FROM (((((public.ipsfreight JOIN public.ipshead ON ((ipsfreight.ipsfreight_ipshead_id = ipshead.ipshead_id))) LEFT JOIN public.uom qtyuom ON (qtyuom.uom_item_weight)) LEFT JOIN public.whsinfo ON ((whsinfo.warehous_id = ipsfreight.ipsfreight_warehous_id))) LEFT JOIN public.shipzone ON ((shipzone.shipzone_id = ipsfreight.ipsfreight_shipzone_id))) LEFT JOIN public.freightclass ON ((freightclass.freightclass_id = ipsfreight.ipsfreight_freightclass_id)));


ALTER TABLE freightpricingscheduleitem OWNER TO admin;

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

COMMENT ON VIEW freightpricingscheduleitem IS 'Freight Pricing Schedule Item';


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

CREATE VIEW glaccount AS
    SELECT (accnt.accnt_company)::character varying AS company, (accnt.accnt_profit)::character varying AS profit_center, (accnt.accnt_number)::character varying AS account_number, (accnt.accnt_sub)::character varying AS sub_account, accnt.accnt_descrip AS description, accnt.accnt_extref AS ext_reference, CASE WHEN (accnt.accnt_type = 'A'::bpchar) THEN 'Asset'::text WHEN (accnt.accnt_type = 'L'::bpchar) THEN 'Liability'::text WHEN (accnt.accnt_type = 'E'::bpchar) THEN 'Expense'::text WHEN (accnt.accnt_type = 'R'::bpchar) THEN 'Revenue'::text WHEN (accnt.accnt_type = 'Q'::bpchar) THEN 'Equity'::text ELSE '?'::text END AS type, accnt.accnt_subaccnttype_code AS sub_type, accnt.accnt_forwardupdate AS forward_update_trial_balances, accnt.accnt_comments AS notes FROM public.accnt ORDER BY accnt.accnt_company, accnt.accnt_profit, accnt.accnt_number, accnt.accnt_sub;


ALTER TABLE glaccount OWNER TO admin;

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

COMMENT ON VIEW glaccount IS 'GL Account';



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

CREATE TABLE incdtcat (
    incdtcat_id integer NOT NULL,
    incdtcat_name text NOT NULL,
    incdtcat_order integer,
    incdtcat_descrip text,
    incdtcat_ediprofile_id integer,
    CONSTRAINT incdtcat_incdtcat_name_check CHECK ((incdtcat_name <> ''::text))
);


ALTER TABLE incdtcat OWNER TO admin;

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

COMMENT ON TABLE incdtcat IS 'Incident Category table';


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

CREATE TABLE incdtpriority (
    incdtpriority_id integer NOT NULL,
    incdtpriority_name text NOT NULL,
    incdtpriority_order integer,
    incdtpriority_descrip text,
    CONSTRAINT incdtpriority_incdtpriority_name_check CHECK ((incdtpriority_name <> ''::text))
);


ALTER TABLE incdtpriority OWNER TO admin;

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

COMMENT ON TABLE incdtpriority IS 'Incident Priority table';


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

CREATE TABLE incdtresolution (
    incdtresolution_id integer NOT NULL,
    incdtresolution_name text NOT NULL,
    incdtresolution_order integer,
    incdtresolution_descrip text,
    CONSTRAINT incdtresolution_incdtresolution_name_check CHECK ((incdtresolution_name <> ''::text))
);


ALTER TABLE incdtresolution OWNER TO admin;

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

COMMENT ON TABLE incdtresolution IS 'Incident Resolution table';


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

CREATE TABLE incdtseverity (
    incdtseverity_id integer NOT NULL,
    incdtseverity_name text NOT NULL,
    incdtseverity_order integer,
    incdtseverity_descrip text,
    CONSTRAINT incdtseverity_incdtseverity_name_check CHECK ((incdtseverity_name <> ''::text))
);


ALTER TABLE incdtseverity OWNER TO admin;

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

COMMENT ON TABLE incdtseverity IS 'Incident Severity table';



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

CREATE VIEW incident AS
    SELECT incdt.incdt_number AS incident_number, incdtcat.incdtcat_name AS category, incdt.incdt_summary AS description, crmacct.crmacct_number AS crm_account, incdt.incdt_assigned_username AS assigned_to, CASE WHEN (incdt.incdt_status = 'N'::bpchar) THEN 'New'::text WHEN (incdt.incdt_status = 'F'::bpchar) THEN 'Feedback'::text WHEN (incdt.incdt_status = 'C'::bpchar) THEN 'Confirmed'::text WHEN (incdt.incdt_status = 'A'::bpchar) THEN 'Assigned'::text WHEN (incdt.incdt_status = 'R'::bpchar) THEN 'Resolved'::text WHEN (incdt.incdt_status = 'L'::bpchar) THEN 'Closed'::text ELSE '?'::text END AS status, incdtseverity.incdtseverity_name AS severity, incdtpriority.incdtpriority_name AS priority, incdtresolution.incdtresolution_name AS resolution, cntct.cntct_number AS contact_number, cntct.cntct_honorific AS honorific, cntct.cntct_first_name AS first, cntct.cntct_middle AS middle, cntct.cntct_last_name AS last, cntct.cntct_suffix AS suffix, cntct.cntct_title AS job_title, cntct.cntct_phone AS phone, cntct.cntct_fax AS fax, cntct.cntct_email AS email, ''::text AS contact_change, incdt.incdt_descrip AS notes, item.item_number, incdt.incdt_lotserial AS lot_serial_number, CASE WHEN (aropen.aropen_doctype = 'C'::bpchar) THEN 'C/M'::text WHEN (aropen.aropen_doctype = 'D'::bpchar) THEN 'D/M'::text WHEN (aropen.aropen_doctype = 'I'::bpchar) THEN 'Invoice'::text WHEN (aropen.aropen_doctype = 'R'::bpchar) THEN 'C/D'::text ELSE ''::text END AS ar_doc_type, aropen.aropen_docnumber AS ar_doc_number FROM ((((((((public.incdt LEFT JOIN public.incdtcat ON ((incdtcat.incdtcat_id = incdt.incdt_incdtcat_id))) LEFT JOIN public.crmacct ON ((crmacct.crmacct_id = incdt.incdt_crmacct_id))) LEFT JOIN public.incdtseverity ON ((incdtseverity.incdtseverity_id = incdt.incdt_incdtseverity_id))) LEFT JOIN public.incdtpriority ON ((incdtpriority.incdtpriority_id = incdt.incdt_incdtpriority_id))) LEFT JOIN public.incdtresolution ON ((incdtresolution.incdtresolution_id = incdt.incdt_incdtresolution_id))) LEFT JOIN public.cntct ON ((cntct.cntct_id = incdt.incdt_cntct_id))) LEFT JOIN public.item ON ((item.item_id = incdt.incdt_item_id))) LEFT JOIN public.aropen ON ((aropen.aropen_id = incdt.incdt_aropen_id)));


ALTER TABLE incident OWNER TO admin;

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

COMMENT ON VIEW incident IS 'Incident';


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

CREATE VIEW incidentchar AS
    SELECT incdt.incdt_number AS incident_number, ("char".char_name)::character varying AS characteristic, charass.charass_value AS value FROM public.incdt, public."char", public.charass WHERE ((('INCDT'::text = charass.charass_target_type) AND (incdt.incdt_id = charass.charass_target_id)) AND (charass.charass_char_id = "char".char_id));


ALTER TABLE incidentchar OWNER TO admin;

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

COMMENT ON VIEW incidentchar IS 'Incident Characteristics';


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

CREATE VIEW incidentcomment AS
    SELECT incdt.incdt_number AS incident_number, cmnttype.cmnttype_name AS type, comment.comment_date AS date, comment.comment_user AS username, comment.comment_text AS text, comment.comment_public AS public FROM public.incdt, public.cmnttype, public.comment WHERE (((comment.comment_source = 'INCDT'::text) AND (comment.comment_source_id = incdt.incdt_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id));


ALTER TABLE incidentcomment OWNER TO admin;

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

COMMENT ON VIEW incidentcomment IS 'Incident Comment';


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

CREATE VIEW incidentfile AS
    SELECT incdt.incdt_number AS incident_number, url.url_title AS title, url.url_url AS url FROM public.incdt, public.url WHERE ((incdt.incdt_id = url.url_source_id) AND (url.url_source = 'INCDT'::text));


ALTER TABLE incidentfile OWNER TO admin;

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

COMMENT ON VIEW incidentfile IS 'Incident File';


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

CREATE VIEW incidentimage AS
    SELECT incdt.incdt_number AS incident_number, image.image_name FROM public.incdt, public.imageass, public.image WHERE (((incdt.incdt_id = imageass.imageass_source_id) AND (imageass.imageass_source = 'INCDT'::text)) AND (imageass.imageass_image_id = image.image_id));


ALTER TABLE incidentimage OWNER TO admin;

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

COMMENT ON VIEW incidentimage IS 'Incident Image';



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

CREATE TABLE classcode (
    classcode_id integer DEFAULT nextval(('classcode_classcode_id_seq'::text)::regclass) NOT NULL,
    classcode_code text NOT NULL,
    classcode_descrip text,
    classcode_mfg boolean,
    classcode_creator text,
    classcode_created timestamp without time zone,
    classcode_modifier text,
    classcode_modified timestamp without time zone,
    classcode_type text,
    CONSTRAINT classcode_classcode_code_check CHECK ((classcode_code <> ''::text))
);


ALTER TABLE classcode OWNER TO admin;

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

COMMENT ON TABLE classcode IS 'Class Code information';


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

CREATE TABLE prodcat (
    prodcat_id integer DEFAULT nextval(('prodcat_prodcat_id_seq'::text)::regclass) NOT NULL,
    prodcat_code text NOT NULL,
    prodcat_descrip text,
    CONSTRAINT prodcat_prodcat_code_check CHECK ((prodcat_code <> ''::text))
);


ALTER TABLE prodcat OWNER TO admin;

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

COMMENT ON TABLE prodcat IS 'Product Category information';



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

CREATE VIEW item AS
    SELECT (item.item_number)::character varying AS item_number, item.item_active AS active, item.item_descrip1 AS description1, item.item_descrip2 AS description2, CASE WHEN (item.item_type = 'P'::bpchar) THEN 'Purchased'::text WHEN (item.item_type = 'M'::bpchar) THEN 'Manufactured'::text WHEN (item.item_type = 'J'::bpchar) THEN 'Job'::text WHEN (item.item_type = 'K'::bpchar) THEN 'Kit'::text WHEN (item.item_type = 'F'::bpchar) THEN 'Phantom'::text WHEN (item.item_type = 'R'::bpchar) THEN 'Reference'::text WHEN (item.item_type = 'S'::bpchar) THEN 'Costing'::text WHEN (item.item_type = 'T'::bpchar) THEN 'Tooling'::text WHEN (item.item_type = 'O'::bpchar) THEN 'Outside Process'::text WHEN (item.item_type = 'L'::bpchar) THEN 'Planning'::text WHEN (item.item_type = 'B'::bpchar) THEN 'Breeder'::text WHEN (item.item_type = 'C'::bpchar) THEN 'Co-Product'::text WHEN (item.item_type = 'Y'::bpchar) THEN 'By-Product'::text ELSE NULL::text END AS item_type, item.item_maxcost AS maximum_desired_cost, classcode.classcode_code AS class_code, i.uom_name AS inventory_uom, item.item_picklist AS pick_list_item, item.item_fractional AS fractional, item.item_config AS configured, item.item_sold AS item_is_sold, prodcat.prodcat_code AS product_category, item.item_exclusive AS exclusive, item.item_listprice AS list_price, item.item_listcost AS list_cost, p.uom_name AS list_price_uom, item.item_upccode AS upc_code, item.item_prodweight AS product_weight, item.item_packweight AS packaging_weight, item.item_comments AS notes, item.item_extdescrip AS ext_description FROM (public.item LEFT JOIN public.prodcat ON ((item.item_prodcat_id = prodcat.prodcat_id))), public.classcode, public.uom i, public.uom p WHERE (((item.item_classcode_id = classcode.classcode_id) AND (item.item_inv_uom_id = i.uom_id)) AND (item.item_price_uom_id = p.uom_id)) ORDER BY (item.item_number)::character varying;


ALTER TABLE item OWNER TO admin;

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

COMMENT ON VIEW item IS 'Item';



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

CREATE TABLE itemalias (
    itemalias_id integer DEFAULT nextval(('"itemalias_itemalias_id_seq"'::text)::regclass) NOT NULL,
    itemalias_item_id integer NOT NULL,
    itemalias_number text NOT NULL,
    itemalias_comments text,
    itemalias_usedescrip boolean NOT NULL,
    itemalias_descrip1 text,
    itemalias_descrip2 text,
    itemalias_crmacct_id integer,
    CONSTRAINT itemalias_itemalias_number_check CHECK ((itemalias_number <> ''::text))
);


ALTER TABLE itemalias OWNER TO admin;

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

COMMENT ON TABLE itemalias IS 'Item Alias information';


--
-- Name: COLUMN itemalias.itemalias_crmacct_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN itemalias.itemalias_crmacct_id IS 'Associated crmacct for item alias.';



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

CREATE VIEW itemalias AS
    SELECT (item.item_number)::character varying AS item_number, itemalias.itemalias_number AS alias_number, COALESCE(crmacct.crmacct_number, ''::text) AS crmacct_number, itemalias.itemalias_usedescrip AS use_description, itemalias.itemalias_descrip1 AS description1, itemalias.itemalias_descrip2 AS description2, itemalias.itemalias_comments AS comments FROM ((public.item JOIN public.itemalias ON ((item.item_id = itemalias.itemalias_item_id))) LEFT JOIN public.crmacct ON ((crmacct.crmacct_id = itemalias.itemalias_crmacct_id)));


ALTER TABLE itemalias OWNER TO admin;

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

COMMENT ON VIEW itemalias IS 'Item Alias';


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

CREATE VIEW itemchar AS
    SELECT (item.item_number)::character varying AS item_number, ("char".char_name)::character varying AS characteristic, charass.charass_value AS value, charass.charass_default AS is_default FROM public.item, public."char", public.charass WHERE ((('I'::text = charass.charass_target_type) AND (item.item_id = charass.charass_target_id)) AND (charass.charass_char_id = "char".char_id));


ALTER TABLE itemchar OWNER TO admin;

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

COMMENT ON VIEW itemchar IS 'Item Characteristic';


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

CREATE VIEW itemcomment AS
    SELECT (item.item_number)::character varying AS item_number, cmnttype.cmnttype_name AS type, comment.comment_date AS date, comment.comment_user AS username, comment.comment_text AS text FROM public.item, public.cmnttype, public.comment WHERE (((comment.comment_source = 'I'::text) AND (comment.comment_source_id = item.item_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id));


ALTER TABLE itemcomment OWNER TO admin;

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

COMMENT ON VIEW itemcomment IS 'Item Comments';



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

CREATE TABLE costelem (
    costelem_id integer DEFAULT nextval(('costelem_costelem_id_seq'::text)::regclass) NOT NULL,
    costelem_type text NOT NULL,
    costelem_sys boolean,
    costelem_po boolean,
    costelem_active boolean,
    costelem_exp_accnt_id integer,
    costelem_cost_item_id integer,
    CONSTRAINT costelem_costelem_type_check CHECK ((costelem_type <> ''::text))
);


ALTER TABLE costelem OWNER TO admin;

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

COMMENT ON TABLE costelem IS 'Costing Element information';


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

CREATE TABLE itemcost (
    itemcost_id integer DEFAULT nextval(('itemcost_itemcost_id_seq'::text)::regclass) NOT NULL,
    itemcost_item_id integer NOT NULL,
    itemcost_costelem_id integer NOT NULL,
    itemcost_lowlevel boolean DEFAULT false NOT NULL,
    itemcost_stdcost numeric(16,6) DEFAULT 0 NOT NULL,
    itemcost_posted date,
    itemcost_actcost numeric(16,6) DEFAULT 0 NOT NULL,
    itemcost_updated date,
    itemcost_curr_id integer DEFAULT basecurrid() NOT NULL
);


ALTER TABLE itemcost OWNER TO admin;

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

COMMENT ON TABLE itemcost IS 'Item Cost information';



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

CREATE VIEW itemcost AS
    SELECT (item.item_number)::character varying(100) AS item_number, (costelem.costelem_type)::character varying(100) AS costing_element, itemcost.itemcost_actcost AS actual_cost, curr_symbol.curr_abbr AS currency, false AS post_to_standard FROM (((public.itemcost LEFT JOIN public.item ON ((itemcost.itemcost_item_id = item.item_id))) LEFT JOIN public.costelem ON ((itemcost.itemcost_costelem_id = costelem.costelem_id))) LEFT JOIN public.curr_symbol ON ((itemcost.itemcost_curr_id = curr_symbol.curr_id))) ORDER BY (item.item_number)::character varying(100), (costelem.costelem_type)::character varying(100);


ALTER TABLE itemcost OWNER TO admin;

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

COMMENT ON VIEW itemcost IS 'Item Cost';


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

CREATE VIEW itemfile AS
    SELECT (item.item_number)::character varying AS item_number, url.url_title AS title, url.url_url AS url FROM public.item, public.url WHERE ((item.item_id = url.url_source_id) AND (url.url_source = 'I'::text));


ALTER TABLE itemfile OWNER TO admin;

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

COMMENT ON VIEW itemfile IS 'Item File';


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

CREATE VIEW itemimage AS
    SELECT (item.item_number)::character varying AS item_number, CASE WHEN (imageass.imageass_purpose = 'P'::bpchar) THEN 'Product Description'::text WHEN (imageass.imageass_purpose = 'I'::bpchar) THEN 'Inventory Description'::text WHEN (imageass.imageass_purpose = 'E'::bpchar) THEN 'Engineering Reference'::text WHEN (imageass.imageass_purpose = 'M'::bpchar) THEN 'Miscellaneous'::text ELSE 'Other'::text END AS purpose, image.image_name FROM public.item, public.imageass, public.image WHERE (((item.item_id = imageass.imageass_source_id) AND (imageass.imageass_source = 'I'::text)) AND (imageass.imageass_image_id = image.image_id));


ALTER TABLE itemimage OWNER TO admin;

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

COMMENT ON VIEW itemimage IS 'Item Image';



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

CREATE TABLE costcat (
    costcat_id integer DEFAULT nextval(('costcat_costcat_id_seq'::text)::regclass) NOT NULL,
    costcat_code text NOT NULL,
    costcat_descrip text,
    costcat_asset_accnt_id integer,
    costcat_liability_accnt_id integer,
    costcat_adjustment_accnt_id integer,
    costcat_matusage_accnt_id integer,
    costcat_purchprice_accnt_id integer,
    costcat_laboroverhead_accnt_id integer,
    costcat_scrap_accnt_id integer,
    costcat_invcost_accnt_id integer,
    costcat_wip_accnt_id integer,
    costcat_shipasset_accnt_id integer,
    costcat_mfgscrap_accnt_id integer,
    costcat_transform_accnt_id integer,
    costcat_freight_accnt_id integer,
    costcat_toliability_accnt_id integer,
    costcat_exp_accnt_id integer,
    CONSTRAINT costcat_costcat_code_check CHECK ((costcat_code <> ''::text))
);


ALTER TABLE costcat OWNER TO admin;

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

COMMENT ON TABLE costcat IS 'Cost Category information';


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

CREATE TABLE plancode (
    plancode_id integer DEFAULT nextval(('plancode_plancode_id_seq'::text)::regclass) NOT NULL,
    plancode_code text NOT NULL,
    plancode_name text,
    plancode_mpsexplosion character(1),
    plancode_consumefcst boolean,
    plancode_mrpexcp_resched boolean,
    plancode_mrpexcp_delete boolean,
    CONSTRAINT plancode_plancode_code_check CHECK ((plancode_code <> ''::text))
);


ALTER TABLE plancode OWNER TO admin;

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

COMMENT ON TABLE plancode IS 'Planner Code information';



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

CREATE VIEW itemsite AS
    SELECT (item.item_number)::character varying AS item_number, (whsinfo.warehous_code)::character varying AS site, itemsite.itemsite_active AS active, itemsite.itemsite_wosupply AS wo_supplied_at_site, itemsite.itemsite_createwo AS create_wos, itemsite.itemsite_posupply AS po_supplied_at_site, itemsite.itemsite_createpr AS create_prs, itemsite.itemsite_createsopr AS create_soprs, itemsite.itemsite_createsopo AS create_sopos, itemsite.itemsite_dropship AS dropship, itemsite.itemsite_sold AS sold_from_site, itemsite.itemsite_soldranking AS ranking, CASE WHEN (itemsite.itemsite_costmethod = 'N'::bpchar) THEN 'None'::text WHEN (itemsite.itemsite_costmethod = 'A'::bpchar) THEN 'Average'::text WHEN (itemsite.itemsite_costmethod = 'S'::bpchar) THEN 'Standard'::text WHEN (itemsite.itemsite_costmethod = 'J'::bpchar) THEN 'Job'::text ELSE NULL::text END AS cost_method, CASE WHEN (itemsite.itemsite_controlmethod = 'N'::bpchar) THEN 'None'::text WHEN (itemsite.itemsite_controlmethod = 'R'::bpchar) THEN 'Regular'::text WHEN (itemsite.itemsite_controlmethod = 'S'::bpchar) THEN 'Serial #'::text WHEN (itemsite.itemsite_controlmethod = 'L'::bpchar) THEN 'Lot #'::text ELSE NULL::text END AS control_method, plancode.plancode_code AS planner_code, costcat.costcat_code AS cost_category, itemsite.itemsite_stocked AS stocked, itemsite.itemsite_abcclass AS abc_class, itemsite.itemsite_autoabcclass AS allow_automatic_updates, itemsite.itemsite_cyclecountfreq AS cycl_cnt_freq, itemsite.itemsite_eventfence AS event_fence, itemsite.itemsite_loccntrl AS multiple_location_control, public.formatlocationname(itemsite.itemsite_location_id) AS location, public.formatlocationname(itemsite.itemsite_recvlocation_id) AS receive_location, public.formatlocationname(itemsite.itemsite_issuelocation_id) AS issue_location, itemsite.itemsite_location_dist AS auto_distr_location, itemsite.itemsite_recvlocation_dist AS auto_distr_receive_location, itemsite.itemsite_issuelocation_dist AS auto_distr_issue_location, itemsite.itemsite_location AS user_defined_location, itemsite.itemsite_location_comments AS location_comment, itemsite.itemsite_disallowblankwip AS disallow_blank_wip_locations, itemsite.itemsite_useparams AS enforce_order_parameters, itemsite.itemsite_reorderlevel AS reorder_level, itemsite.itemsite_ordertoqty AS order_up_to, itemsite.itemsite_minordqty AS minimum_order, itemsite.itemsite_maxordqty AS maximum_order, itemsite.itemsite_multordqty AS order_multiple, itemsite.itemsite_useparamsmanual AS enforce_on_manual_orders, CASE WHEN (itemsite.itemsite_planning_type = 'N'::bpchar) THEN 'None'::text WHEN (itemsite.itemsite_planning_type = 'M'::bpchar) THEN 'MRP'::text WHEN (itemsite.itemsite_planning_type = 'S'::bpchar) THEN 'MPS'::text ELSE NULL::text END AS planning_system, itemsite.itemsite_ordergroup AS group_mps_mrp_orders, itemsite.itemsite_ordergroup_first AS first_group, itemsite.itemsite_mps_timefence AS mps_time_fence, itemsite.itemsite_leadtime AS lead_time, itemsite.itemsite_safetystock AS safety_stock, COALESCE((SELECT whsinfo.warehous_code FROM (public.itemsite supplysite JOIN public.whsinfo ON ((whsinfo.warehous_id = supplysite.itemsite_warehous_id))) WHERE (supplysite.itemsite_id = itemsite.itemsite_supply_itemsite_id)), 'None'::text) AS supplied_from_site, itemsite.itemsite_notes AS notes, itemsite.itemsite_perishable AS perishable, itemsite.itemsite_warrpurc AS require_warranty, itemsite.itemsite_autoreg AS auto_register FROM ((((public.itemsite JOIN public.item ON ((item.item_id = itemsite.itemsite_item_id))) JOIN public.plancode ON ((plancode.plancode_id = itemsite.itemsite_plancode_id))) JOIN public.costcat ON ((costcat.costcat_id = itemsite.itemsite_costcat_id))) JOIN public.whsinfo ON ((whsinfo.warehous_id = itemsite.itemsite_warehous_id)));


ALTER TABLE itemsite OWNER TO admin;

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

COMMENT ON VIEW itemsite IS 'Item Site';


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

CREATE VIEW itemsitecomment AS
    SELECT (item.item_number)::character varying AS item_number, (whsinfo.warehous_code)::character varying AS site, cmnttype.cmnttype_name AS type, comment.comment_date AS date, comment.comment_user AS username, comment.comment_text AS text FROM public.itemsite, public.item, public.whsinfo, public.cmnttype, public.comment WHERE ((itemsite.itemsite_item_id = item.item_id) AND ((((itemsite.itemsite_warehous_id = whsinfo.warehous_id) AND (comment.comment_source = 'IS'::text)) AND (comment.comment_source_id = itemsite.itemsite_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id)));


ALTER TABLE itemsitecomment OWNER TO admin;

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

COMMENT ON VIEW itemsitecomment IS 'Item Site Comments';



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

CREATE TABLE contrct (
    contrct_id integer NOT NULL,
    contrct_number text NOT NULL,
    contrct_vend_id integer NOT NULL,
    contrct_descrip text,
    contrct_effective date NOT NULL,
    contrct_expires date NOT NULL,
    contrct_note text
);


ALTER TABLE contrct OWNER TO admin;

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

COMMENT ON TABLE contrct IS 'Grouping of Item Sources for a Vendor with common effective and expiration dates.';


--
-- Name: COLUMN contrct.contrct_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN contrct.contrct_id IS 'Sequence identifier for contract.';


--
-- Name: COLUMN contrct.contrct_number; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN contrct.contrct_number IS 'User defined identifier for contract.';


--
-- Name: COLUMN contrct.contrct_vend_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN contrct.contrct_vend_id IS 'Vendor associated with contract.';


--
-- Name: COLUMN contrct.contrct_descrip; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN contrct.contrct_descrip IS 'Description for contract.';


--
-- Name: COLUMN contrct.contrct_effective; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN contrct.contrct_effective IS 'Effective date for contract.  Constraint for overlap.';


--
-- Name: COLUMN contrct.contrct_expires; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN contrct.contrct_expires IS 'Expiration date for contract.  Constraint for overlap.';


--
-- Name: COLUMN contrct.contrct_note; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN contrct.contrct_note IS 'Notes for contract.';


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

CREATE TABLE itemsrc (
    itemsrc_id integer DEFAULT nextval(('"itemsrc_itemsrc_id_seq"'::text)::regclass) NOT NULL,
    itemsrc_item_id integer NOT NULL,
    itemsrc_vend_id integer NOT NULL,
    itemsrc_vend_item_number text,
    itemsrc_vend_item_descrip text,
    itemsrc_comments text,
    itemsrc_vend_uom text NOT NULL,
    itemsrc_invvendoruomratio numeric(20,10) NOT NULL,
    itemsrc_minordqty numeric(18,6) NOT NULL,
    itemsrc_multordqty numeric(18,6) NOT NULL,
    itemsrc_leadtime integer NOT NULL,
    itemsrc_ranking integer NOT NULL,
    itemsrc_active boolean NOT NULL,
    itemsrc_manuf_name text DEFAULT ''::text NOT NULL,
    itemsrc_manuf_item_number text DEFAULT ''::text NOT NULL,
    itemsrc_manuf_item_descrip text,
    itemsrc_default boolean,
    itemsrc_upccode text,
    itemsrc_effective date DEFAULT startoftime() NOT NULL,
    itemsrc_expires date DEFAULT endoftime() NOT NULL,
    itemsrc_contrct_id integer,
    itemsrc_contrct_max numeric(18,6) DEFAULT 0.0 NOT NULL,
    itemsrc_contrct_min numeric(18,6) DEFAULT 0.0 NOT NULL
);


ALTER TABLE itemsrc OWNER TO admin;

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

COMMENT ON TABLE itemsrc IS 'Item Source information';


--
-- Name: COLUMN itemsrc.itemsrc_effective; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN itemsrc.itemsrc_effective IS 'Effective date for item source.  Constraint for overlap.';


--
-- Name: COLUMN itemsrc.itemsrc_expires; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN itemsrc.itemsrc_expires IS 'Expiration date for item source.  Constraint for overlap.';


--
-- Name: COLUMN itemsrc.itemsrc_contrct_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN itemsrc.itemsrc_contrct_id IS 'Associated contract for item source.  Inherits effective, expiration dates.';



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

CREATE VIEW itemsource AS
    SELECT (item.item_number)::character varying AS item_number, (vendinfo.vend_number)::character varying AS vendor, itemsrc.itemsrc_vend_item_number AS vendor_item_number, itemsrc.itemsrc_active AS active, itemsrc.itemsrc_default, itemsrc.itemsrc_vend_uom AS vendor_uom, itemsrc.itemsrc_invvendoruomratio AS inventory_vendor_uom_ratio, itemsrc.itemsrc_minordqty AS minimum_order, itemsrc.itemsrc_multordqty AS order_multiple, itemsrc.itemsrc_ranking AS vendor_ranking, itemsrc.itemsrc_leadtime AS lead_time, itemsrc.itemsrc_comments AS notes, itemsrc.itemsrc_vend_item_descrip AS vendor_description, itemsrc.itemsrc_manuf_name AS manufacturer_name, itemsrc.itemsrc_manuf_item_number AS manufacturer_item_number, itemsrc.itemsrc_manuf_item_descrip AS manufacturer_description, itemsrc.itemsrc_upccode AS bar_code, contrct.contrct_number AS contract_number, itemsrc.itemsrc_effective AS effective_date, itemsrc.itemsrc_expires AS expires_date FROM (((public.itemsrc LEFT JOIN public.item ON ((itemsrc.itemsrc_item_id = item.item_id))) LEFT JOIN public.vendinfo ON ((itemsrc.itemsrc_vend_id = vendinfo.vend_id))) LEFT JOIN public.contrct ON ((itemsrc.itemsrc_contrct_id = contrct.contrct_id))) ORDER BY (item.item_number)::character varying(100), (vendinfo.vend_number)::character varying(100);


ALTER TABLE itemsource OWNER TO admin;

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

COMMENT ON VIEW itemsource IS 'Item Source';



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

CREATE TABLE itemsrcp (
    itemsrcp_id integer DEFAULT nextval(('itemsrcp_itemsrcp_id_seq'::text)::regclass) NOT NULL,
    itemsrcp_itemsrc_id integer NOT NULL,
    itemsrcp_qtybreak numeric(18,6) NOT NULL,
    itemsrcp_price numeric(16,6),
    itemsrcp_updated date,
    itemsrcp_curr_id integer DEFAULT basecurrid() NOT NULL,
    itemsrcp_dropship boolean DEFAULT false NOT NULL,
    itemsrcp_warehous_id integer DEFAULT (-1) NOT NULL,
    itemsrcp_type character(1) NOT NULL,
    itemsrcp_discntprcnt numeric(16,6),
    itemsrcp_fixedamtdiscount numeric(16,6),
    CONSTRAINT valid_itemsrcp_type CHECK ((itemsrcp_type = ANY (ARRAY['N'::bpchar, 'D'::bpchar])))
);


ALTER TABLE itemsrcp OWNER TO admin;

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

COMMENT ON TABLE itemsrcp IS 'Item Source Price information';


--
-- Name: COLUMN itemsrcp.itemsrcp_dropship; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN itemsrcp.itemsrcp_dropship IS 'Used to determine if item source price applies only to drop ship purchase orders.';


--
-- Name: COLUMN itemsrcp.itemsrcp_warehous_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN itemsrcp.itemsrcp_warehous_id IS 'Used to determine if item source price applies only to specific site on purchase orders.';


--
-- Name: COLUMN itemsrcp.itemsrcp_type; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN itemsrcp.itemsrcp_type IS 'Pricing type for item source price.  Valid values are N-nominal and D-discount.';


--
-- Name: COLUMN itemsrcp.itemsrcp_discntprcnt; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN itemsrcp.itemsrcp_discntprcnt IS 'Discount percent for item source price.';


--
-- Name: COLUMN itemsrcp.itemsrcp_fixedamtdiscount; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN itemsrcp.itemsrcp_fixedamtdiscount IS 'Fixed amount discount for item source price.';



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

CREATE VIEW itemsourceprice AS
    SELECT (item.item_number)::character varying AS item_number, (vendinfo.vend_number)::character varying AS vendor, itemsrcp.itemsrcp_qtybreak AS qty_break, CASE WHEN (itemsrcp.itemsrcp_type = 'N'::bpchar) THEN 'Nominal'::text ELSE 'Discount'::text END AS pricing_type, CASE WHEN (itemsrcp.itemsrcp_warehous_id = (-1)) THEN 'All'::text ELSE whsinfo.warehous_code END AS pricing_site, itemsrcp.itemsrcp_dropship AS dropship_only, itemsrcp.itemsrcp_price AS price_per_unit, curr_symbol.curr_abbr AS currency, (itemsrcp.itemsrcp_discntprcnt * 100.0) AS discount_percent, itemsrcp.itemsrcp_fixedamtdiscount AS discount_fixed_amount FROM (((((public.itemsrcp LEFT JOIN public.itemsrc ON ((itemsrc.itemsrc_id = itemsrcp.itemsrcp_itemsrc_id))) LEFT JOIN public.item ON ((itemsrc.itemsrc_item_id = item.item_id))) LEFT JOIN public.vendinfo ON ((itemsrc.itemsrc_vend_id = vendinfo.vend_id))) LEFT JOIN public.curr_symbol ON ((itemsrcp.itemsrcp_curr_id = curr_symbol.curr_id))) LEFT JOIN public.whsinfo ON ((itemsrcp.itemsrcp_warehous_id = whsinfo.warehous_id))) ORDER BY (item.item_number)::character varying(100), (vendinfo.vend_number)::character varying(100);


ALTER TABLE itemsourceprice OWNER TO admin;

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

COMMENT ON VIEW itemsourceprice IS 'Item Source Price';



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

CREATE TABLE itemsub (
    itemsub_id integer DEFAULT nextval(('itemsub_itemsub_id_seq'::text)::regclass) NOT NULL,
    itemsub_parent_item_id integer NOT NULL,
    itemsub_sub_item_id integer NOT NULL,
    itemsub_uomratio numeric(20,10) NOT NULL,
    itemsub_rank integer NOT NULL
);


ALTER TABLE itemsub OWNER TO admin;

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

COMMENT ON TABLE itemsub IS 'Item Substitutes information';



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

CREATE VIEW itemsubstitute AS
    SELECT (p.item_number)::character varying AS root_item_number, (s.item_number)::character varying AS substitute_item_number, itemsub.itemsub_uomratio AS sub_parent_uom_ratio, itemsub.itemsub_rank AS ranking FROM public.item p, public.item s, public.itemsub WHERE ((p.item_id = itemsub.itemsub_parent_item_id) AND (s.item_id = itemsub.itemsub_sub_item_id));


ALTER TABLE itemsubstitute OWNER TO admin;

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

COMMENT ON VIEW itemsubstitute IS 'Item Substitute';



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

CREATE TABLE itemtax (
    itemtax_id integer NOT NULL,
    itemtax_item_id integer NOT NULL,
    itemtax_taxtype_id integer NOT NULL,
    itemtax_taxzone_id integer
);


ALTER TABLE itemtax OWNER TO admin;

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

COMMENT ON TABLE itemtax IS 'This table associates tax types in a specified tax authority for the given item.';



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

CREATE VIEW itemtaxtype AS
    SELECT (item.item_number)::character varying AS item_number, CASE WHEN (taxzone.taxzone_id IS NULL) THEN 'Any'::character varying ELSE (taxzone.taxzone_code)::character varying END AS tax_zone, taxtype.taxtype_name AS tax_type FROM public.item, (public.itemtax LEFT JOIN public.taxzone ON ((itemtax.itemtax_taxzone_id = taxzone.taxzone_id))), public.taxtype WHERE ((item.item_id = itemtax.itemtax_item_id) AND (itemtax.itemtax_taxtype_id = taxtype.taxtype_id));


ALTER TABLE itemtaxtype OWNER TO admin;

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

COMMENT ON VIEW itemtaxtype IS 'Item Tax Type';



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

CREATE TABLE itemuomconv (
    itemuomconv_id integer NOT NULL,
    itemuomconv_item_id integer NOT NULL,
    itemuomconv_from_uom_id integer NOT NULL,
    itemuomconv_from_value numeric(20,10) NOT NULL,
    itemuomconv_to_uom_id integer NOT NULL,
    itemuomconv_to_value numeric(20,10) NOT NULL,
    itemuomconv_fractional boolean DEFAULT false NOT NULL,
    itemuomconv_active boolean DEFAULT true NOT NULL,
    CONSTRAINT itemuomconv_uom CHECK ((itemuomconv_from_uom_id <> itemuomconv_to_uom_id))
);


ALTER TABLE itemuomconv OWNER TO admin;

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

COMMENT ON TABLE itemuomconv IS 'UOM conversion information. From Unit to To Unit with a value per.';


--
-- Name: COLUMN itemuomconv.itemuomconv_active; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN itemuomconv.itemuomconv_active IS 'Item UOM conversion active/inactive';



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

CREATE VIEW itemuomconversion AS
    SELECT (item.item_number)::character varying AS item_number, (f.uom_name)::character varying AS uom, itemuomconv.itemuomconv_from_value AS uom_value, p.uom_name AS per_uom, itemuomconv.itemuomconv_to_value AS per_uom_value, itemuomconv.itemuomconv_fractional AS fractional, public.fetchitemuomconvtypes(itemuomconv.itemuomconv_id) AS selected_types FROM public.item, public.itemuomconv, public.uom f, public.uom p WHERE (((item.item_id = itemuomconv.itemuomconv_item_id) AND (itemuomconv.itemuomconv_from_uom_id = f.uom_id)) AND (itemuomconv.itemuomconv_to_uom_id = p.uom_id));


ALTER TABLE itemuomconversion OWNER TO admin;

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

COMMENT ON VIEW itemuomconversion IS 'Item Unit of Measure Conversion';



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

CREATE TABLE gltrans (
    gltrans_id integer DEFAULT nextval(('"gltrans_gltrans_id_seq"'::text)::regclass) NOT NULL,
    gltrans_exported boolean,
    gltrans_created timestamp with time zone,
    gltrans_date date NOT NULL,
    gltrans_sequence integer,
    gltrans_accnt_id integer NOT NULL,
    gltrans_source text,
    gltrans_docnumber text,
    gltrans_misc_id integer,
    gltrans_amount numeric(20,2) NOT NULL,
    gltrans_notes text,
    gltrans_journalnumber integer,
    gltrans_posted boolean NOT NULL,
    gltrans_doctype text,
    gltrans_rec boolean DEFAULT false NOT NULL,
    gltrans_username text DEFAULT geteffectivextuser() NOT NULL,
    gltrans_deleted boolean DEFAULT false
);


ALTER TABLE gltrans OWNER TO admin;

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

COMMENT ON TABLE gltrans IS 'General Ledger (G/L) transaction information';



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

CREATE VIEW journalentry AS
    SELECT curr_symbol.curr_abbr AS currency, c.gltrans_amount AS amount, c.gltrans_date AS dist_date, c.gltrans_docnumber AS doc_number, public.formatglaccount(da.accnt_id) AS debit, public.formatglaccount(ca.accnt_id) AS credit, c.gltrans_notes AS notes FROM public.gltrans d, public.gltrans c, public.accnt da, public.accnt ca, public.curr_symbol WHERE ((((((((d.gltrans_sequence = c.gltrans_sequence) AND (d.gltrans_accnt_id = da.accnt_id)) AND (c.gltrans_accnt_id = ca.accnt_id)) AND (d.gltrans_amount < (0)::numeric)) AND (c.gltrans_amount > (0)::numeric)) AND (d.gltrans_doctype = 'JE'::text)) AND (c.gltrans_doctype = 'JE'::text)) AND (curr_symbol.curr_id = public.basecurrid())) ORDER BY d.gltrans_date DESC;


ALTER TABLE journalentry OWNER TO admin;

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

COMMENT ON VIEW journalentry IS 'Journal Entry';



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

CREATE TABLE location (
    location_id integer DEFAULT nextval(('location_location_id_seq'::text)::regclass) NOT NULL,
    location_warehous_id integer NOT NULL,
    location_name text NOT NULL,
    location_descrip text,
    location_restrict boolean,
    location_netable boolean,
    location_whsezone_id integer,
    location_aisle text,
    location_rack text,
    location_bin text,
    location_formatname text,
    location_usable boolean
);


ALTER TABLE location OWNER TO admin;

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

COMMENT ON TABLE location IS 'Warehouse Location information';


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

CREATE TABLE whsezone (
    whsezone_id integer NOT NULL,
    whsezone_warehous_id integer NOT NULL,
    whsezone_name text NOT NULL,
    whsezone_descrip text
);


ALTER TABLE whsezone OWNER TO admin;

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

COMMENT ON TABLE whsezone IS 'Warehouse Zone information';



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

CREATE VIEW location AS
    SELECT (whsinfo.warehous_code)::character varying AS site, (location.location_aisle)::character varying AS aisle, (location.location_rack)::character varying AS rack, (location.location_bin)::character varying AS bin, (location.location_name)::character varying AS location, whsezone.whsezone_name AS zone, location.location_netable AS netable, location.location_usable AS usable, location.location_restrict AS restricted, location.location_descrip AS description FROM ((public.location LEFT JOIN public.whsinfo ON ((whsinfo.warehous_id = location.location_warehous_id))) LEFT JOIN public.whsezone ON ((whsezone.whsezone_id = location.location_whsezone_id)));


ALTER TABLE location OWNER TO admin;

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

COMMENT ON VIEW location IS 'Location';


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

CREATE VIEW misccounttag AS
    SELECT 'This view is for inserts only'::text AS site, 'This view is for inserts only'::text AS item_number, (0)::numeric AS quantity, 'This view is for inserts only'::text AS comment;


ALTER TABLE misccounttag OWNER TO admin;

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

COMMENT ON VIEW misccounttag IS 'Miscellaneous Count Tag';


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

CREATE VIEW pricingschedule AS
    SELECT (ipshead.ipshead_name)::character varying AS name, ipshead.ipshead_descrip AS description, public.formatdate(ipshead.ipshead_effective, 'Always'::text) AS effective, public.formatdate(ipshead.ipshead_expires, 'Never'::text) AS expires, curr_symbol.curr_abbr AS currency FROM public.ipshead, public.curr_symbol WHERE (curr_symbol.curr_id = ipshead.ipshead_curr_id);


ALTER TABLE pricingschedule OWNER TO admin;

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

COMMENT ON VIEW pricingschedule IS 'Pricing Schedule';



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

CREATE TABLE ipsass (
    ipsass_id integer NOT NULL,
    ipsass_ipshead_id integer NOT NULL,
    ipsass_cust_id integer,
    ipsass_custtype_id integer,
    ipsass_custtype_pattern text,
    ipsass_shipto_id integer DEFAULT (-1),
    ipsass_shipto_pattern text
);


ALTER TABLE ipsass OWNER TO admin;

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

COMMENT ON TABLE ipsass IS 'Pricing Schedule assignment information';



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

CREATE VIEW pricingscheduleassign AS
    SELECT (COALESCE(custinfo.cust_number, 'Any'::text))::character varying AS customer_number, (COALESCE(shiptoinfo.shipto_num, 'Any'::text))::character varying AS customer_shipto, (COALESCE(ipsass.ipsass_shipto_pattern, 'N/A'::text))::character varying AS customer_shipto_pattern, (COALESCE(custtype.custtype_code, 'N/A'::text))::character varying AS customer_type, (COALESCE(ipsass.ipsass_custtype_pattern, 'N/A'::text))::character varying AS customer_type_pattern, (ipshead.ipshead_name)::character varying AS pricing_schedule FROM ((((public.ipshead JOIN public.ipsass ON ((ipshead.ipshead_id = ipsass.ipsass_ipshead_id))) LEFT JOIN public.custinfo ON ((ipsass.ipsass_cust_id = custinfo.cust_id))) LEFT JOIN public.custtype ON ((ipsass.ipsass_custtype_id = custtype.custtype_id))) LEFT JOIN public.shiptoinfo ON ((ipsass.ipsass_shipto_id = shiptoinfo.shipto_id))) ORDER BY (COALESCE(custinfo.cust_number, 'Any'::text))::character varying, (COALESCE(shiptoinfo.shipto_num, 'Any'::text))::character varying, (COALESCE(custtype.custtype_code, 'N/A'::text))::character varying, (COALESCE(ipsass.ipsass_custtype_pattern, 'N/A'::text))::character varying, (ipshead.ipshead_name)::character varying;


ALTER TABLE pricingscheduleassign OWNER TO admin;

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

COMMENT ON VIEW pricingscheduleassign IS 'Pricing Schedule Assignments';



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

CREATE TABLE ipsiteminfo (
    ipsitem_id integer DEFAULT nextval(('"ipsitem_ipsitem_id_seq"'::text)::regclass) NOT NULL,
    ipsitem_ipshead_id integer,
    ipsitem_item_id integer,
    ipsitem_qtybreak numeric(18,6) NOT NULL,
    ipsitem_price numeric(16,4) NOT NULL,
    ipsitem_qty_uom_id integer,
    ipsitem_price_uom_id integer,
    ipsitem_discntprcnt numeric(10,6) DEFAULT 0.00 NOT NULL,
    ipsitem_fixedamtdiscount numeric(16,4) DEFAULT 0.00 NOT NULL,
    ipsitem_prodcat_id integer,
    ipsitem_type character(1) NOT NULL,
    ipsitem_warehous_id integer,
    CONSTRAINT valid_ipsitem_type CHECK ((ipsitem_type = ANY (ARRAY['N'::bpchar, 'D'::bpchar, 'M'::bpchar])))
);


ALTER TABLE ipsiteminfo OWNER TO admin;

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

COMMENT ON TABLE ipsiteminfo IS 'Pricing Schedule Item information';


--
-- Name: COLUMN ipsiteminfo.ipsitem_prodcat_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN ipsiteminfo.ipsitem_prodcat_id IS 'Product category for pricing schedule item.';


--
-- Name: COLUMN ipsiteminfo.ipsitem_type; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN ipsiteminfo.ipsitem_type IS 'Pricing type for pricing schedule item.  Valid values are N-nominal, D-discount, and M-markup';


--
-- Name: COLUMN ipsiteminfo.ipsitem_warehous_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN ipsiteminfo.ipsitem_warehous_id IS 'Site for pricing schedule item which enables pricing by site.';



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

CREATE VIEW pricingscheduleitem AS
    SELECT (ipshead.ipshead_name)::character varying AS pricing_schedule, CASE WHEN (COALESCE(ipsiteminfo.ipsitem_item_id, (-1)) > 0) THEN 'Item'::character varying ELSE 'Product Category'::character varying END AS type, (COALESCE(item.item_number, ''::text))::character varying AS item_number, (COALESCE(prodcat.prodcat_code, ''::text))::character varying AS product_category, ipsiteminfo.ipsitem_qtybreak AS qty_break, (qtyuom.uom_name)::character varying AS qty_uom, (priceuom.uom_name)::character varying AS price_uom, ipsiteminfo.ipsitem_price AS price, ipsiteminfo.ipsitem_discntprcnt AS percent, ipsiteminfo.ipsitem_fixedamtdiscount AS fixedamt, CASE WHEN (ipsiteminfo.ipsitem_type = 'N'::bpchar) THEN 'Nominal'::character varying WHEN (ipsiteminfo.ipsitem_type = 'D'::bpchar) THEN 'Discount'::character varying WHEN (ipsiteminfo.ipsitem_type = 'M'::bpchar) THEN 'Markup'::character varying ELSE NULL::character varying END AS pricing_type FROM (((((public.ipsiteminfo JOIN public.ipshead ON ((ipsiteminfo.ipsitem_ipshead_id = ipshead.ipshead_id))) LEFT JOIN public.item ON ((ipsiteminfo.ipsitem_item_id = item.item_id))) LEFT JOIN public.prodcat ON ((ipsiteminfo.ipsitem_prodcat_id = prodcat.prodcat_id))) LEFT JOIN public.uom qtyuom ON ((ipsiteminfo.ipsitem_qty_uom_id = qtyuom.uom_id))) LEFT JOIN public.uom priceuom ON ((ipsiteminfo.ipsitem_price_uom_id = priceuom.uom_id)));


ALTER TABLE pricingscheduleitem OWNER TO admin;

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

COMMENT ON VIEW pricingscheduleitem IS 'Pricing Schedule Item';



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

CREATE TABLE ipsitemchar (
    ipsitemchar_id integer NOT NULL,
    ipsitemchar_ipsitem_id integer NOT NULL,
    ipsitemchar_char_id integer NOT NULL,
    ipsitemchar_value text NOT NULL,
    ipsitemchar_price numeric(16,4)
);


ALTER TABLE ipsitemchar OWNER TO admin;

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

COMMENT ON TABLE ipsitemchar IS 'Item Price Schedule Characteristic Prices.';



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

CREATE VIEW pricingscheduleitemchar AS
    SELECT (ipshead.ipshead_name)::character varying AS pricing_schedule, (item.item_number)::character varying AS item_number, ipsiteminfo.ipsitem_qtybreak AS qty_break, (qtyuom.uom_name)::character varying AS qty_uom, (priceuom.uom_name)::character varying AS price_uom, ("char".char_name)::character varying AS characteristic, (ipsitemchar.ipsitemchar_value)::character varying AS value, ipsitemchar.ipsitemchar_price AS price FROM public.ipshead, public.ipsiteminfo, public.ipsitemchar, public.item, public."char", public.uom qtyuom, public.uom priceuom WHERE ((((((ipshead.ipshead_id = ipsiteminfo.ipsitem_ipshead_id) AND (ipsiteminfo.ipsitem_id = ipsitemchar.ipsitemchar_ipsitem_id)) AND (ipsiteminfo.ipsitem_item_id = item.item_id)) AND (ipsiteminfo.ipsitem_qty_uom_id = qtyuom.uom_id)) AND (ipsiteminfo.ipsitem_price_uom_id = priceuom.uom_id)) AND (ipsitemchar.ipsitemchar_char_id = "char".char_id));


ALTER TABLE pricingscheduleitemchar OWNER TO admin;

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

COMMENT ON VIEW pricingscheduleitemchar IS 'Pricing Schedule Item Characteristics';


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

CREATE VIEW project AS
    SELECT prj.prj_number AS number, prj.prj_name AS name, prj.prj_descrip AS description, prj.prj_owner_username AS owner, prj.prj_username AS assigned_to, prj.prj_so AS sales_orders, prj.prj_wo AS work_orders, prj.prj_po AS purchase_orders, CASE WHEN (prj.prj_status = 'P'::bpchar) THEN 'Concept'::text WHEN (prj.prj_status = 'O'::bpchar) THEN 'In-Process'::text WHEN (prj.prj_status = 'C'::bpchar) THEN 'Closed'::text ELSE 'Error'::text END AS status, prj.prj_due_date AS due, prj.prj_assigned_date AS assigned, prj.prj_start_date AS started, prj.prj_completed_date AS completed FROM public.prj;


ALTER TABLE project OWNER TO admin;

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

COMMENT ON VIEW project IS 'Project';


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

CREATE VIEW projectcomment AS
    SELECT (prj.prj_number)::character varying AS project_number, cmnttype.cmnttype_name AS type, comment.comment_date AS date, comment.comment_user AS username, comment.comment_text AS text FROM public.prj, public.cmnttype, public.comment WHERE (((comment.comment_source = 'J'::text) AND (comment.comment_source_id = prj.prj_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id));


ALTER TABLE projectcomment OWNER TO admin;

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

COMMENT ON VIEW projectcomment IS 'Project Comment';



--
-- Name: cust_cust_id_seq; Type: SEQUENCE; Schema: public; Owner: admin
--

CREATE SEQUENCE cust_cust_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE cust_cust_id_seq OWNER TO admin;

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

CREATE TABLE prospect (
    prospect_id integer DEFAULT nextval('cust_cust_id_seq'::regclass) NOT NULL,
    prospect_active boolean DEFAULT true NOT NULL,
    prospect_number text NOT NULL,
    prospect_name text NOT NULL,
    prospect_cntct_id integer,
    prospect_comments text,
    prospect_created date DEFAULT ('now'::text)::date NOT NULL,
    prospect_salesrep_id integer,
    prospect_warehous_id integer,
    prospect_taxzone_id integer,
    CONSTRAINT prospect_prospect_number_check CHECK ((prospect_number <> ''::text))
);


ALTER TABLE prospect OWNER TO admin;

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

COMMENT ON TABLE prospect IS 'Prospect Information';



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

CREATE VIEW prospect AS
    SELECT (prospect.prospect_number)::character varying AS prospect_number, prospect.prospect_name, prospect.prospect_active AS active, salesrep.salesrep_number AS sales_rep, whsinfo.warehous_code AS site_code, taxzone.taxzone_code AS default_tax_zone, prospect.prospect_comments AS notes, cntct.cntct_number AS contact_number, cntct.cntct_honorific AS contact_honorific, cntct.cntct_first_name AS contact_first, cntct.cntct_middle AS contact_middle, cntct.cntct_last_name AS contact_last, cntct.cntct_suffix AS contact_suffix, cntct.cntct_title AS contact_job_title, cntct.cntct_phone AS contact_voice, cntct.cntct_phone2 AS contact_alternate, cntct.cntct_fax AS contact_fax, cntct.cntct_email AS contact_email, cntct.cntct_webaddr AS contact_web, ''::text AS contact_change, addr.addr_number AS contact_address_number, addr.addr_line1 AS contact_address1, addr.addr_line2 AS contact_address2, addr.addr_line3 AS contact_address3, addr.addr_city AS contact_city, addr.addr_state AS contact_state, addr.addr_postalcode AS contact_postalcode, addr.addr_country AS contact_country, ''::text AS contact_address_change FROM (((((public.prospect LEFT JOIN public.cntct ON ((prospect.prospect_cntct_id = cntct.cntct_id))) LEFT JOIN public.addr ON ((cntct.cntct_addr_id = addr.addr_id))) LEFT JOIN public.taxzone ON ((prospect.prospect_taxzone_id = taxzone.taxzone_id))) LEFT JOIN public.salesrep ON ((prospect.prospect_salesrep_id = salesrep.salesrep_id))) LEFT JOIN public.whsinfo ON ((prospect.prospect_warehous_id = whsinfo.warehous_id)));


ALTER TABLE prospect OWNER TO admin;

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

COMMENT ON VIEW prospect IS 'Prospect';



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

CREATE TABLE expcat (
    expcat_id integer NOT NULL,
    expcat_code text NOT NULL,
    expcat_descrip text,
    expcat_exp_accnt_id integer,
    expcat_liability_accnt_id integer,
    expcat_active boolean,
    expcat_purchprice_accnt_id integer,
    expcat_freight_accnt_id integer,
    CONSTRAINT expcat_expcat_code_check CHECK ((expcat_code <> ''::text))
);


ALTER TABLE expcat OWNER TO admin;

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

COMMENT ON TABLE expcat IS 'Expense Category information';


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

CREATE TABLE womatl (
    womatl_id integer DEFAULT nextval(('womatl_womatl_id_seq'::text)::regclass) NOT NULL,
    womatl_wo_id integer,
    womatl_itemsite_id integer,
    womatl_qtyper numeric(20,8) NOT NULL,
    womatl_scrap numeric(8,4) NOT NULL,
    womatl_qtyreq numeric(18,6) NOT NULL,
    womatl_qtyiss numeric(18,6) NOT NULL,
    womatl_qtywipscrap numeric(18,6) NOT NULL,
    womatl_lastissue date,
    womatl_lastreturn date,
    womatl_cost numeric(16,6),
    womatl_picklist boolean,
    womatl_status character(1),
    womatl_imported boolean DEFAULT false,
    womatl_createwo boolean,
    womatl_issuemethod character(1),
    womatl_wooper_id integer,
    womatl_bomitem_id integer,
    womatl_duedate date,
    womatl_schedatwooper boolean,
    womatl_uom_id integer NOT NULL,
    womatl_notes text,
    womatl_ref text,
    womatl_scrapvalue numeric(16,6) DEFAULT 0,
    womatl_qtyfxd numeric(20,8) DEFAULT 0 NOT NULL,
    womatl_issuewo boolean DEFAULT false NOT NULL,
    womatl_price numeric(16,6) DEFAULT 0 NOT NULL
);


ALTER TABLE womatl OWNER TO admin;

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

COMMENT ON TABLE womatl IS 'Work Order Material Requirements information';


--
-- Name: COLUMN womatl.womatl_qtyfxd; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN womatl.womatl_qtyfxd IS 'The fixed quantity required';



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

CREATE VIEW purchaseline AS
    SELECT (pohead.pohead_number)::character varying AS order_number, poitem.poitem_linenumber AS line_number, item.item_number, whsinfo.warehous_code AS site, expcat.expcat_code AS expense_category, poitem.poitem_qty_ordered AS qty_ordered, poitem.poitem_unitprice AS unit_price, poitem.poitem_freight AS freight, poitem.poitem_duedate AS due_date, prj.prj_number AS project_number, poitem.poitem_vend_item_number AS vend_item_number, poitem.poitem_vend_item_descrip AS vendor_description, poitem.poitem_manuf_name AS manufacturer_name, poitem.poitem_manuf_item_number AS manufacturer_item_number, poitem.poitem_manuf_item_descrip AS manufacturer_description, poitem.poitem_comments AS notes, public.formatrevnumber('BOM'::text, poitem.poitem_bom_rev_id) AS bill_of_materials_revision, public.formatrevnumber('BOO'::text, poitem.poitem_boo_rev_id) AS bill_of_operations_revision, public.formatsonumber(coitem.coitem_id) AS sales_order_number, public.formatwonumber(womatl.womatl_wo_id) AS work_order_number FROM ((((((((public.pohead JOIN public.poitem ON ((pohead.pohead_id = poitem.poitem_pohead_id))) LEFT JOIN public.prj ON ((poitem.poitem_prj_id = prj.prj_id))) LEFT JOIN public.expcat ON ((poitem.poitem_expcat_id = expcat.expcat_id))) LEFT JOIN public.itemsite ON ((poitem.poitem_itemsite_id = itemsite.itemsite_id))) LEFT JOIN public.item ON ((itemsite.itemsite_item_id = item.item_id))) LEFT JOIN public.whsinfo ON ((itemsite.itemsite_warehous_id = whsinfo.warehous_id))) LEFT JOIN public.coitem ON (((coitem.coitem_id = poitem.poitem_order_id) AND (poitem.poitem_order_type = 'S'::bpchar)))) LEFT JOIN public.womatl ON (((womatl.womatl_id = poitem.poitem_order_id) AND (poitem.poitem_order_type = 'W'::bpchar)))) ORDER BY pohead.pohead_number, poitem.poitem_linenumber;


ALTER TABLE purchaseline OWNER TO admin;

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

COMMENT ON VIEW purchaseline IS 'Purchase Order Line';


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

CREATE VIEW purchaselinechar AS
    SELECT (data.order_number)::character varying AS order_number, data.line_number, data.characteristic, COALESCE(pi.charass_value, i3.charass_value) AS value FROM (((((SELECT DISTINCT "char".char_id, poitem.poitem_id, poitem.poitem_itemsite_id, pohead.pohead_number AS order_number, poitem.poitem_linenumber AS line_number, "char".char_name AS characteristic FROM public.pohead, public.poitem, public.itemsite, public.item, public.charass, public."char" WHERE ((((((pohead.pohead_id = poitem.poitem_pohead_id) AND (poitem.poitem_itemsite_id = itemsite.itemsite_id)) AND (itemsite.itemsite_item_id = item.item_id)) AND (charass.charass_char_id = "char".char_id)) AND (charass.charass_target_type = 'I'::text)) AND (charass.charass_target_id = item.item_id))) data LEFT JOIN public.charass pi ON ((((data.poitem_id = pi.charass_target_id) AND ('PI'::text = pi.charass_target_type)) AND (pi.charass_char_id = data.char_id)))) LEFT JOIN public.itemsite i1 ON ((data.poitem_itemsite_id = i1.itemsite_id))) LEFT JOIN public.item i2 ON ((i1.itemsite_item_id = i2.item_id))) LEFT JOIN public.charass i3 ON (((((i2.item_id = i3.charass_target_id) AND ('I'::text = i3.charass_target_type)) AND (i3.charass_char_id = data.char_id)) AND i3.charass_default))) ORDER BY (data.order_number)::character varying, data.line_number, data.characteristic;


ALTER TABLE purchaselinechar OWNER TO admin;

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

COMMENT ON VIEW purchaselinechar IS 'Purchase Order Line Item Characteristic';


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

CREATE VIEW purchaselinecomment AS
    SELECT (pohead.pohead_number)::character varying AS order_number, poitem.poitem_linenumber AS line_number, cmnttype.cmnttype_name AS type, comment.comment_date AS date, comment.comment_user AS username, comment.comment_text AS text FROM public.pohead, public.poitem, public.cmnttype, public.comment WHERE ((((pohead.pohead_id = poitem.poitem_pohead_id) AND (comment.comment_source = 'PI'::text)) AND (comment.comment_source_id = poitem.poitem_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id)) ORDER BY pohead.pohead_number, poitem.poitem_linenumber, comment.comment_date DESC;


ALTER TABLE purchaselinecomment OWNER TO admin;

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

COMMENT ON VIEW purchaselinecomment IS 'Purchase Order Line Item Comment';



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

CREATE TABLE tax (
    tax_id integer DEFAULT nextval(('"tax_tax_id_seq"'::text)::regclass) NOT NULL,
    tax_code text NOT NULL,
    tax_descrip text,
    tax_sales_accnt_id integer,
    tax_taxclass_id integer,
    tax_taxauth_id integer,
    tax_basis_tax_id integer,
    tax_dist_accnt_id integer,
    CONSTRAINT tax_tax_code_check CHECK ((tax_code <> ''::text))
);


ALTER TABLE tax OWNER TO admin;

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

COMMENT ON TABLE tax IS 'Tax information';


--
-- Name: COLUMN tax.tax_sales_accnt_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN tax.tax_sales_accnt_id IS 'Deprecated column - DO NOT USE';


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

CREATE TABLE vendaddrinfo (
    vendaddr_id integer DEFAULT nextval(('vendaddr_vendaddr_id_seq'::text)::regclass) NOT NULL,
    vendaddr_vend_id integer,
    vendaddr_code text,
    vendaddr_name text,
    vendaddr_comments text,
    vendaddr_cntct_id integer,
    vendaddr_addr_id integer,
    vendaddr_taxzone_id integer
);


ALTER TABLE vendaddrinfo OWNER TO admin;

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

COMMENT ON TABLE vendaddrinfo IS 'Vendor Address information';



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

CREATE VIEW purchaseorder AS
    SELECT (pohead.pohead_number)::character varying AS order_number, pohead.pohead_orderdate AS order_date, terms.terms_code AS terms, taxzone.taxzone_code AS tax_zone, whsinfo.warehous_code AS receiving_site, pohead.pohead_agent_username AS purchasing_agent, vendinfo.vend_number AS vendor_number, COALESCE(ua.vendaddr_code, 'MAIN'::text) AS alt_address, pohead.pohead_fob AS fob, pohead.pohead_shipvia AS ship_via, curr_symbol.curr_abbr AS currency, (SELECT COALESCE(sum(data.tax), 0.00) AS tax FROM (SELECT round(sum(calculatetaxdetailsummary.taxdetail_tax), 2) AS tax FROM (public.tax JOIN public.calculatetaxdetailsummary('PO'::text, pohead.pohead_id, 'T'::text) calculatetaxdetailsummary(taxdetail_tax_id, taxdetail_tax_code, taxdetail_tax_descrip, taxdetail_tax_basis_tax_id, taxdetail_taxrate_percent, taxdetail_taxrate_amount, taxdetail_level, taxdetail_taxclass_id, taxdetail_taxclass_code, taxdetail_taxclass_sequence, taxdetail_tax, taxdetail_curr_id, taxdetail_curr_abbr) ON ((calculatetaxdetailsummary.taxdetail_tax_id = tax.tax_id))) GROUP BY tax.tax_id) data) AS tax, pohead.pohead_freight AS freight, pohead.pohead_comments AS notes, pohead.pohead_dropship AS dropship, vc.cntct_number AS vend_contact_number, pohead.pohead_vend_cntct_honorific AS vend_cntct_honorific, pohead.pohead_vend_cntct_first_name AS vend_cntct_first_name, pohead.pohead_vend_cntct_middle AS vend_cntct_middle, pohead.pohead_vend_cntct_last_name AS vend_cntct_last_name, pohead.pohead_vend_cntct_suffix AS vend_cntct_suffix, pohead.pohead_vend_cntct_phone AS vend_cntct_phone, pohead.pohead_vend_cntct_title AS vend_cntct_title, pohead.pohead_vend_cntct_fax AS vend_cntct_fax, pohead.pohead_vend_cntct_email AS vend_cntct_email, pohead.pohead_vendaddress1 AS vendaddress1, pohead.pohead_vendaddress2 AS vendaddress2, pohead.pohead_vendaddress3 AS vendaddress3, pohead.pohead_vendcity AS vendcity, pohead.pohead_vendstate AS vendstate, pohead.pohead_vendzipcode AS vendzipcode, pohead.pohead_vendcountry AS vendcountry, sc.cntct_number AS shipto_contact_number, pohead.pohead_shipto_cntct_honorific AS shipto_cntct_honorific, pohead.pohead_shipto_cntct_first_name AS shipto_cntct_first_name, pohead.pohead_shipto_cntct_middle AS shipto_cntct_middle, pohead.pohead_shipto_cntct_last_name AS shipto_cntct_last_name, pohead.pohead_shipto_cntct_suffix AS shipto_cntct_suffix, pohead.pohead_shipto_cntct_phone AS shipto_cntct_phone, pohead.pohead_shipto_cntct_title AS shipto_cntct_title, pohead.pohead_shipto_cntct_fax AS shipto_cntct_fax, pohead.pohead_shipto_cntct_email AS shipto_cntct_email, addr.addr_number AS shiptoaddress_number, pohead.pohead_shiptoaddress1 AS shiptoaddress1, pohead.pohead_shiptoaddress2 AS shiptoaddress2, pohead.pohead_shiptoaddress3 AS shiptoaddress3, pohead.pohead_shiptocity AS shiptocity, pohead.pohead_shiptostate AS shiptostate, pohead.pohead_shiptozipcode AS shiptozipcode, pohead.pohead_shiptocountry AS shiptocountry, cohead.cohead_number AS sales_order_number FROM ((((((((((public.pohead LEFT JOIN public.cntct vc ON ((pohead.pohead_vend_cntct_id = vc.cntct_id))) LEFT JOIN public.cntct sc ON ((pohead.pohead_shipto_cntct_id = sc.cntct_id))) LEFT JOIN public.addr ON ((pohead.pohead_shiptoaddress_id = addr.addr_id))) LEFT JOIN public.terms ON ((pohead.pohead_terms_id = terms.terms_id))) LEFT JOIN public.taxzone ON ((pohead.pohead_taxzone_id = taxzone.taxzone_id))) LEFT JOIN public.whsinfo ON ((pohead.pohead_warehous_id = whsinfo.warehous_id))) LEFT JOIN public.vendaddrinfo ua ON ((pohead.pohead_vendaddr_id = ua.vendaddr_id))) LEFT JOIN public.cohead ON ((pohead.pohead_cohead_id = cohead.cohead_id))) JOIN public.vendinfo ON ((pohead.pohead_vend_id = vendinfo.vend_id))) JOIN public.curr_symbol ON ((pohead.pohead_curr_id = curr_symbol.curr_id))) ORDER BY pohead.pohead_number;


ALTER TABLE purchaseorder OWNER TO admin;

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

COMMENT ON VIEW purchaseorder IS 'Purchase Order';


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

CREATE VIEW purchaseordercomment AS
    SELECT (pohead.pohead_number)::character varying AS order_number, cmnttype.cmnttype_name AS type, comment.comment_date AS date, comment.comment_user AS username, comment.comment_text AS text FROM public.pohead, public.cmnttype, public.comment WHERE (((comment.comment_source = 'P'::text) AND (comment.comment_source_id = pohead.pohead_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id));


ALTER TABLE purchaseordercomment OWNER TO admin;

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

COMMENT ON VIEW purchaseordercomment IS 'Purchase Order Comment';



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

CREATE TABLE quhead (
    quhead_id integer DEFAULT nextval(('"quhead_quhead_id_seq"'::text)::regclass) NOT NULL,
    quhead_number text NOT NULL,
    quhead_cust_id integer NOT NULL,
    quhead_quotedate date,
    quhead_shipto_id integer,
    quhead_shiptoname text,
    quhead_shiptoaddress1 text,
    quhead_shiptoaddress2 text,
    quhead_shiptoaddress3 text,
    quhead_shiptocity text,
    quhead_shiptostate text,
    quhead_shiptozipcode text,
    quhead_shiptophone text,
    quhead_salesrep_id integer,
    quhead_terms_id integer,
    quhead_freight numeric(16,4),
    quhead_ordercomments text,
    quhead_shipcomments text,
    quhead_billtoname text,
    quhead_billtoaddress1 text,
    quhead_billtoaddress2 text,
    quhead_billtoaddress3 text,
    quhead_billtocity text,
    quhead_billtostate text,
    quhead_billtozip text,
    quhead_commission numeric(16,4),
    quhead_custponumber text,
    quhead_fob text,
    quhead_shipvia text,
    quhead_warehous_id integer,
    quhead_packdate date,
    quhead_prj_id integer,
    quhead_misc numeric(16,4) DEFAULT 0 NOT NULL,
    quhead_misc_accnt_id integer,
    quhead_misc_descrip text,
    quhead_billtocountry text,
    quhead_shiptocountry text,
    quhead_curr_id integer DEFAULT basecurrid(),
    quhead_imported boolean DEFAULT false,
    quhead_expire date,
    quhead_calcfreight boolean DEFAULT false NOT NULL,
    quhead_shipto_cntct_id integer,
    quhead_shipto_cntct_honorific text,
    quhead_shipto_cntct_first_name text,
    quhead_shipto_cntct_middle text,
    quhead_shipto_cntct_last_name text,
    quhead_shipto_cntct_suffix text,
    quhead_shipto_cntct_phone text,
    quhead_shipto_cntct_title text,
    quhead_shipto_cntct_fax text,
    quhead_shipto_cntct_email text,
    quhead_billto_cntct_id integer,
    quhead_billto_cntct_honorific text,
    quhead_billto_cntct_first_name text,
    quhead_billto_cntct_middle text,
    quhead_billto_cntct_last_name text,
    quhead_billto_cntct_suffix text,
    quhead_billto_cntct_phone text,
    quhead_billto_cntct_title text,
    quhead_billto_cntct_fax text,
    quhead_billto_cntct_email text,
    quhead_taxzone_id integer,
    quhead_taxtype_id integer,
    quhead_ophead_id integer,
    quhead_status text,
    quhead_saletype_id integer,
    quhead_shipzone_id integer,
    CONSTRAINT quhead_check CHECK ((((quhead_misc = (0)::numeric) AND (quhead_misc_accnt_id IS NULL)) OR ((quhead_misc <> (0)::numeric) AND (quhead_misc_accnt_id IS NOT NULL)))),
    CONSTRAINT quhead_quhead_number_check CHECK ((quhead_number <> ''::text)),
    CONSTRAINT quhead_quhead_status_check CHECK ((((quhead_status = 'O'::text) OR (quhead_status = 'C'::text)) OR (quhead_status = 'X'::text)))
);


ALTER TABLE quhead OWNER TO admin;

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

COMMENT ON TABLE quhead IS 'Quote header information';


--
-- Name: COLUMN quhead.quhead_saletype_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN quhead.quhead_saletype_id IS 'Associated sale type for quote.';


--
-- Name: COLUMN quhead.quhead_shipzone_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN quhead.quhead_shipzone_id IS 'Associated shipping zone for quote.';



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

CREATE VIEW quote AS
    SELECT (quhead.quhead_number)::character varying AS quote_number, whsinfo.warehous_code AS site, quhead.quhead_quotedate AS quote_date, quhead.quhead_packdate AS pack_date, saletype.saletype_code AS sale_type, salesrep.salesrep_number AS sales_rep, quhead.quhead_commission AS commission, taxzone.taxzone_code AS tax_zone, taxtype.taxtype_name AS tax_type, terms.terms_code AS terms, prj.prj_number AS project_number, COALESCE(custinfo.cust_number, prospect.prospect_number) AS customer_number, quhead.quhead_billtoname AS billto_name, quhead.quhead_billtoaddress1 AS billto_address1, quhead.quhead_billtoaddress2 AS billto_address2, quhead.quhead_billtoaddress3 AS billto_address3, quhead.quhead_billtocity AS billto_city, quhead.quhead_billtostate AS billto_state, quhead.quhead_billtozip AS billto_postal_code, quhead.quhead_billtocountry AS billto_country, shiptoinfo.shipto_num AS shipto_number, quhead.quhead_shiptoname AS shipto_name, quhead.quhead_shiptophone AS shipto_phone, quhead.quhead_shiptoaddress1 AS shipto_address1, quhead.quhead_shiptoaddress2 AS shipto_address2, quhead.quhead_shiptoaddress3 AS shipto_address3, quhead.quhead_shiptocity AS shipto_city, quhead.quhead_shiptostate AS shipto_state, quhead.quhead_shiptozipcode AS shipto_postal_code, quhead.quhead_shiptocountry AS shipto_country, shipzone.shipzone_name AS shipto_shipzone, quhead.quhead_custponumber AS cust_po_number, quhead.quhead_fob AS fob, quhead.quhead_shipvia AS ship_via, curr_symbol.curr_abbr AS currency, quhead.quhead_misc_descrip AS misc_charge_description, CASE WHEN (quhead.quhead_misc_accnt_id IS NULL) THEN NULL::text ELSE public.formatglaccount(quhead.quhead_misc_accnt_id) END AS misc_account_number, quhead.quhead_misc AS misc_charge, quhead.quhead_freight AS freight, quhead.quhead_ordercomments AS order_notes, quhead.quhead_shipcomments AS shipping_notes, false AS add_to_packing_list_batch, quhead.quhead_expire AS expire_date, CASE WHEN (quhead.quhead_status = 'C'::text) THEN 'Converted'::text ELSE 'Open'::text END AS status FROM public.curr_symbol, (((((((((((public.quhead LEFT JOIN public.whsinfo ON ((quhead.quhead_warehous_id = whsinfo.warehous_id))) LEFT JOIN public.prj ON ((quhead.quhead_prj_id = prj.prj_id))) LEFT JOIN public.shiptoinfo ON ((quhead.quhead_shipto_id = shiptoinfo.shipto_id))) LEFT JOIN public.taxzone ON ((quhead.quhead_taxzone_id = taxzone.taxzone_id))) LEFT JOIN public.taxtype ON ((quhead.quhead_taxtype_id = taxtype.taxtype_id))) LEFT JOIN public.custinfo ON ((quhead.quhead_cust_id = custinfo.cust_id))) LEFT JOIN public.prospect ON ((quhead.quhead_cust_id = prospect.prospect_id))) LEFT JOIN public.salesrep ON ((quhead.quhead_salesrep_id = salesrep.salesrep_id))) LEFT JOIN public.terms ON ((quhead.quhead_terms_id = terms.terms_id))) LEFT JOIN public.saletype ON ((quhead.quhead_saletype_id = saletype.saletype_id))) LEFT JOIN public.shipzone ON ((quhead.quhead_shipzone_id = shipzone.shipzone_id))) WHERE (quhead.quhead_curr_id = curr_symbol.curr_id);


ALTER TABLE quote OWNER TO admin;

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

COMMENT ON VIEW quote IS 'Quote';


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

CREATE VIEW quotecomment AS
    SELECT quhead.quhead_number AS quote_number, cmnttype.cmnttype_name AS type, comment.comment_date AS date, comment.comment_user AS username, comment.comment_text AS text FROM public.quhead, public.cmnttype, public.comment WHERE (((comment.comment_source = 'Q'::text) AND (comment.comment_source_id = quhead.quhead_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id));


ALTER TABLE quotecomment OWNER TO admin;

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

COMMENT ON VIEW quotecomment IS 'Quote Comment';



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

CREATE TABLE quitem (
    quitem_id integer DEFAULT nextval(('"quitem_quitem_id_seq"'::text)::regclass) NOT NULL,
    quitem_quhead_id integer,
    quitem_linenumber integer,
    quitem_itemsite_id integer,
    quitem_scheddate date,
    quitem_qtyord numeric(18,6),
    quitem_unitcost numeric(16,6),
    quitem_price numeric(16,4),
    quitem_custprice numeric(16,4),
    quitem_memo text,
    quitem_custpn text,
    quitem_createorder boolean,
    quitem_order_warehous_id integer,
    quitem_item_id integer,
    quitem_prcost numeric(16,6),
    quitem_imported boolean DEFAULT false,
    quitem_qty_uom_id integer NOT NULL,
    quitem_qty_invuomratio numeric(20,10) NOT NULL,
    quitem_price_uom_id integer NOT NULL,
    quitem_price_invuomratio numeric(20,10) NOT NULL,
    quitem_promdate date,
    quitem_taxtype_id integer,
    quitem_dropship boolean DEFAULT false,
    quitem_itemsrc_id integer,
    quitem_pricemode character(1) DEFAULT 'D'::bpchar NOT NULL,
    CONSTRAINT valid_quitem_pricemode CHECK ((quitem_pricemode = ANY (ARRAY['D'::bpchar, 'M'::bpchar])))
);


ALTER TABLE quitem OWNER TO admin;

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

COMMENT ON TABLE quitem IS 'Quote Line Item information';


--
-- Name: COLUMN quitem.quitem_pricemode; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN quitem.quitem_pricemode IS 'Pricing mode for quote item.  Valid values are D-discount, and M-markup';



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

CREATE VIEW quoteline AS
    SELECT quhead.quhead_number AS quote_number, quitem.quitem_linenumber AS line_number, l.item_number, quitem.quitem_custpn AS customer_pn, i.warehous_code AS sold_from_site, quitem.quitem_qtyord AS qty_ordered, q.uom_name AS qty_uom, quitem.quitem_price AS net_unit_price, p.uom_name AS price_uom, quitem.quitem_scheddate AS scheduled_date, COALESCE((SELECT taxtype.taxtype_name FROM public.taxtype WHERE (taxtype.taxtype_id = public.getitemtaxtype(l.item_id, quhead.quhead_taxzone_id))), 'None'::text) AS tax_type, CASE WHEN (quitem.quitem_price = (0)::numeric) THEN '100'::text WHEN (quitem.quitem_custprice = (0)::numeric) THEN 'N/A'::text ELSE (round((((1)::numeric - (quitem.quitem_price / quitem.quitem_custprice)) * (100)::numeric), 4))::text END AS discount_pct_from_list, quitem.quitem_createorder AS create_order, s.warehous_code AS supplying_site, quitem.quitem_prcost AS overwrite_po_price, quitem.quitem_memo AS notes FROM public.quhead, public.uom q, public.uom p, (public.quitem LEFT JOIN public.whsinfo s ON ((quitem.quitem_order_warehous_id = s.warehous_id))), public.itemsite il, public.item l, public.whsinfo i WHERE ((((((quhead.quhead_id = quitem.quitem_quhead_id) AND (quitem.quitem_itemsite_id = il.itemsite_id)) AND (il.itemsite_item_id = l.item_id)) AND (il.itemsite_warehous_id = i.warehous_id)) AND (quitem.quitem_qty_uom_id = q.uom_id)) AND (quitem.quitem_price_uom_id = p.uom_id)) ORDER BY quhead.quhead_number, quitem.quitem_linenumber;


ALTER TABLE quoteline OWNER TO admin;

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

COMMENT ON VIEW quoteline IS 'Quote Line Item';


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

CREATE VIEW quotelinechar AS
    SELECT DISTINCT quhead.quhead_number AS quote_number, quitem.quitem_linenumber AS line_number, "char".char_name AS characteristic, COALESCE((SELECT b.charass_value FROM public.charass b WHERE (((b.charass_target_type = 'QI'::text) AND (b.charass_target_id = quitem.quitem_id)) AND (b.charass_char_id = "char".char_id))), (SELECT c.charass_value FROM public.charass c WHERE ((((c.charass_target_type = 'I'::text) AND (c.charass_target_id = item.item_id)) AND c.charass_default) AND (c.charass_char_id = "char".char_id)) LIMIT 1)) AS value FROM public.quhead, public.quitem, public.itemsite, public.item, public.charass a, public."char" WHERE ((((((quhead.quhead_id = quitem.quitem_quhead_id) AND (quitem.quitem_itemsite_id = itemsite.itemsite_id)) AND (itemsite.itemsite_item_id = item.item_id)) AND (a.charass_char_id = "char".char_id)) AND (a.charass_target_type = 'I'::text)) AND (a.charass_target_id = item.item_id)) ORDER BY quhead.quhead_number, quitem.quitem_linenumber, "char".char_name;


ALTER TABLE quotelinechar OWNER TO admin;

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

COMMENT ON VIEW quotelinechar IS 'Quote Line Item Characteristics';


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

CREATE VIEW quotelinecomment AS
    SELECT quhead.quhead_number AS quote_number, quitem.quitem_linenumber AS line_number, cmnttype.cmnttype_name AS type, comment.comment_date AS date, comment.comment_user AS username, comment.comment_text AS text FROM public.quhead, public.quitem, public.cmnttype, public.comment WHERE ((((quhead.quhead_id = quitem.quitem_quhead_id) AND (comment.comment_source = 'QI'::text)) AND (comment.comment_source_id = quitem.quitem_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id)) ORDER BY quhead.quhead_number, quitem.quitem_linenumber, comment.comment_date DESC;


ALTER TABLE quotelinecomment OWNER TO admin;

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

COMMENT ON VIEW quotelinecomment IS 'Quote Line Item Comment';



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

CREATE TABLE aropenalloc (
    aropenalloc_aropen_id integer NOT NULL,
    aropenalloc_doctype character(1) NOT NULL,
    aropenalloc_doc_id integer NOT NULL,
    aropenalloc_amount numeric(20,2) DEFAULT 0.00 NOT NULL,
    aropenalloc_curr_id integer DEFAULT basecurrid()
);


ALTER TABLE aropenalloc OWNER TO admin;


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

CREATE VIEW salescredit AS
    SELECT custinfo.cust_number AS customer_number, aropen.aropen_docnumber AS cm_number, cohead.cohead_number AS so_number, (aropenalloc.aropenalloc_amount)::numeric(16,4) AS amount, curr.curr_abbr AS currency FROM ((((public.aropenalloc LEFT JOIN public.aropen ON ((aropen.aropen_id = aropenalloc.aropenalloc_aropen_id))) LEFT JOIN public.custinfo ON ((custinfo.cust_id = aropen.aropen_cust_id))) LEFT JOIN public.cohead ON (((aropenalloc.aropenalloc_doctype = 'S'::bpchar) AND (cohead.cohead_id = aropenalloc.aropenalloc_doc_id)))) LEFT JOIN public.curr_symbol curr ON ((curr.curr_id = aropenalloc.aropenalloc_curr_id))) WHERE (aropenalloc.aropenalloc_doctype = 'S'::bpchar);


ALTER TABLE salescredit OWNER TO admin;

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

COMMENT ON VIEW salescredit IS 'Payments (credit memos) pre-applied to sales orders';



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

CREATE TABLE cohist (
    cohist_id integer DEFAULT nextval(('cohist_cohist_id_seq'::text)::regclass) NOT NULL,
    cohist_cust_id integer,
    cohist_itemsite_id integer,
    cohist_shipdate date,
    cohist_shipvia text,
    cohist_ordernumber text,
    cohist_orderdate date,
    cohist_invcnumber text,
    cohist_invcdate date,
    cohist_qtyshipped numeric(18,6),
    cohist_unitprice numeric(16,4),
    cohist_shipto_id integer,
    cohist_salesrep_id integer,
    cohist_duedate date,
    cohist_imported boolean DEFAULT false,
    cohist_billtoname text,
    cohist_billtoaddress1 text,
    cohist_billtoaddress2 text,
    cohist_billtoaddress3 text,
    cohist_billtocity text,
    cohist_billtostate text,
    cohist_billtozip text,
    cohist_shiptoname text,
    cohist_shiptoaddress1 text,
    cohist_shiptoaddress2 text,
    cohist_shiptoaddress3 text,
    cohist_shiptocity text,
    cohist_shiptostate text,
    cohist_shiptozip text,
    cohist_commission numeric(16,4),
    cohist_commissionpaid boolean,
    cohist_unitcost numeric(18,6),
    cohist_misc_type character(1),
    cohist_misc_descrip text,
    cohist_misc_id integer,
    cohist_doctype text,
    cohist_promisedate date,
    cohist_ponumber text,
    cohist_curr_id integer DEFAULT basecurrid(),
    cohist_sequence integer,
    cohist_taxtype_id integer,
    cohist_taxzone_id integer,
    cohist_cohead_ccpay_id integer,
    cohist_saletype_id integer,
    cohist_shipzone_id integer
);


ALTER TABLE cohist OWNER TO admin;

--
