-- 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 NOTICE 'voidPostedAPCheck() is deprecated - use voidPostedCheck() instead';
  RETURN voidPostedCheck($1, fetchJournalNumber('AP-CK'), CURRENT_DATE);
END;
$_$;


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

--
-- TOC entry 1145 (class 1255 OID 146567189)
-- Dependencies: 4536 8
-- 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 NOTICE 'voidPostedAPCheck() is deprecated - use voidPostedCheck() instead';
  RETURN voidPostedCheck($1, $2, CURRENT_DATE);
END;
$_$;


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

--
-- TOC entry 1215 (class 1255 OID 146567190)
-- Dependencies: 4536 8
-- 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 NOTICE 'voidPostedAPCheck() is deprecated - use voidPostedCheck() instead';
  RETURN voidPostedCheck($1, $2, $3);
END;
$_$;


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

--
-- TOC entry 1222 (class 1255 OID 146567191)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 1254 (class 1255 OID 146567193)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 2324 (class 1255 OID 146567195)
-- Dependencies: 2714 4536 8
-- 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(itemsite_qtyonhand) 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 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;

--
-- TOC entry 2323 (class 1255 OID 146567194)
-- Dependencies: 2714 4536 8
-- 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(itemsite_qtyonhand) 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(itemsite_qtyonhand) 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;

--
-- TOC entry 2325 (class 1255 OID 146567196)
-- Dependencies: 2714 4536 8
-- 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(itemsite_qtyonhand) 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;

--
-- TOC entry 2326 (class 1255 OID 146567197)
-- Dependencies: 4536 8
-- 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;


--
-- TOC entry 2327 (class 1255 OID 146567198)
-- Dependencies: 4536 9
-- Name: calcrate(numeric, character); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION calcrate(numeric, character) RETURNS numeric
    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
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;

--
-- TOC entry 2328 (class 1255 OID 146567199)
-- Dependencies: 4536 9
-- Name: copyitem(integer, text); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION copyitem(integer, text) 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
  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;

--
-- TOC entry 2332 (class 1255 OID 146567200)
-- Dependencies: 4536 9
-- Name: invoicesheets(integer[]); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION invoicesheets(integer[]) 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
pHeadIDs ALIAS FOR $1;

_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
         SELECT _invcheadid, cust_id, -1, '', current_date, false, false, _invcnum,
           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
         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
            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
            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(integer[]) OWNER TO admin;

--
-- TOC entry 2333 (class 1255 OID 146567201)
-- Dependencies: 4536 9
-- 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-2012 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;

--
-- TOC entry 2334 (class 1255 OID 146567202)
-- Dependencies: 4536 9
-- Name: sheetstate(integer, character); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION sheetstate(integer, character) 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
  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)
      JOIN te.teemp ON (tehead_emp_id=teemp_emp_id)
     WHERE ((teitem_tehead_id=pTeheadId)
        AND (teitem_type = 'T')
        AND (NOT teemp_contractor));

  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;

--
-- TOC entry 2335 (class 1255 OID 146567203)
-- Dependencies: 4536 9
-- Name: triggertehead(); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION triggertehead() RETURNS trigger
    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
_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;

--
-- TOC entry 2336 (class 1255 OID 146567204)
-- Dependencies: 4536 9
-- Name: triggerteitem(); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION triggerteitem() RETURNS trigger
    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
_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=current_user
    WHERE (tehead_id=NEW.teitem_tehead_id);
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION te.triggerteitem() OWNER TO admin;

--
-- TOC entry 2329 (class 1255 OID 146567205)
-- Dependencies: 4536 9
-- Name: triggerteprj(); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION triggerteprj() RETURNS trigger
    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
  _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;

--
-- TOC entry 2330 (class 1255 OID 146567206)
-- Dependencies: 9
-- Name: unnest(anyarray); Type: FUNCTION; Schema: te; Owner: admin
--

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


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

--
-- TOC entry 2331 (class 1255 OID 146567207)
-- Dependencies: 4536 9
-- Name: vouchersheet(integer); Type: FUNCTION; Schema: te; Owner: admin
--

CREATE FUNCTION vouchersheet(integer) 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
  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,
              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' || 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;


--
-- TOC entry 2337 (class 1255 OID 146567209)
-- Dependencies: 4536 10
-- Name: fetchwelcomehtml(); Type: FUNCTION; Schema: xtdesktop; Owner: admin
--

CREATE FUNCTION fetchwelcomehtml() RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _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;

--
-- TOC entry 2338 (class 1255 OID 146567210)
-- Dependencies: 2717 4536 10
-- Name: usersonline(); Type: FUNCTION; Schema: xtdesktop; Owner: admin
--

CREATE FUNCTION usersonline() RETURNS SETOF usersonline
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
 _row 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;

--
-- TOC entry 9080 (class 0 OID 0)
-- Dependencies: 2338
-- 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.';



--
-- TOC entry 4537 (class 1255 OID 146567211)
-- Dependencies: 1238 8
-- 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;

--
-- TOC entry 4538 (class 1255 OID 146567212)
-- Dependencies: 1581 8
-- 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;

--
-- TOC entry 4539 (class 1255 OID 146567213)
-- Dependencies: 1903 8
-- 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;

--
-- TOC entry 234 (class 1259 OID 146567214)
-- Dependencies: 6046 6047 6048 6049 6050 6051 6052 6053 6054 6056 8
-- 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 public.addr OWNER TO admin;

--
-- TOC entry 9081 (class 0 OID 0)
-- Dependencies: 234
-- Name: TABLE addr; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE addr IS 'Postal Address';



--
-- TOC entry 235 (class 1259 OID 146567230)
-- Dependencies: 8477 6
-- 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 api.account OWNER TO admin;

--
-- TOC entry 9083 (class 0 OID 0)
-- Dependencies: 235
-- Name: VIEW account; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW account IS 'Account';



--
-- TOC entry 236 (class 1259 OID 146567235)
-- Dependencies: 6057 6058 6059 6060 6061 6062 6063 6064 6065 6066 6067 6068 6069 6071 8
-- 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 public."char" OWNER TO admin;

--
-- TOC entry 9085 (class 0 OID 0)
-- Dependencies: 236
-- Name: TABLE "char"; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 237 (class 1259 OID 146567255)
-- Dependencies: 6073 6074 8
-- 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 public.charass OWNER TO admin;

--
-- TOC entry 9087 (class 0 OID 0)
-- Dependencies: 237
-- Name: TABLE charass; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE charass IS 'Characteristic assignment information';



--
-- TOC entry 238 (class 1259 OID 146567263)
-- Dependencies: 8478 6
-- 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 api.accountchar OWNER TO admin;

--
-- TOC entry 9089 (class 0 OID 0)
-- Dependencies: 238
-- Name: VIEW accountchar; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW accountchar IS 'Account Characteristics';



--
-- TOC entry 239 (class 1259 OID 146567267)
-- Dependencies: 6076 6077 6078 8
-- 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 public.cmnttype OWNER TO admin;

--
-- TOC entry 9091 (class 0 OID 0)
-- Dependencies: 239
-- Name: TABLE cmnttype; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE cmnttype IS 'Comment Type information';


--
-- TOC entry 240 (class 1259 OID 146567276)
-- Dependencies: 6079 8
-- 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 public.comment OWNER TO admin;

--
-- TOC entry 9093 (class 0 OID 0)
-- Dependencies: 240
-- Name: TABLE comment; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE comment IS 'Comment information';



--
-- TOC entry 241 (class 1259 OID 146567283)
-- Dependencies: 8479 6
-- 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 api.accountcomment OWNER TO admin;

--
-- TOC entry 9095 (class 0 OID 0)
-- Dependencies: 241
-- Name: VIEW accountcomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW accountcomment IS 'Account Comment';



--
-- TOC entry 242 (class 1259 OID 146567287)
-- Dependencies: 6081 6082 6083 8
-- 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 public.docass OWNER TO admin;

--
-- TOC entry 9097 (class 0 OID 0)
-- Dependencies: 242
-- Name: TABLE docass; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE docass IS 'Document Assignement References';


--
-- TOC entry 243 (class 1259 OID 146567296)
-- Dependencies: 8
-- 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 public.file OWNER TO admin;

--
-- TOC entry 244 (class 1259 OID 146567302)
-- Dependencies: 8
-- 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 public.urlinfo OWNER TO admin;

--
-- TOC entry 245 (class 1259 OID 146567308)
-- Dependencies: 8480 8
-- 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 public.url OWNER TO admin;


--
-- TOC entry 246 (class 1259 OID 146567313)
-- Dependencies: 8481 6
-- 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 api.accountfile OWNER TO admin;

--
-- TOC entry 9102 (class 0 OID 0)
-- Dependencies: 246
-- Name: VIEW accountfile; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW accountfile IS 'Account File';



--
-- TOC entry 247 (class 1259 OID 146567317)
-- Dependencies: 242 8
-- 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 public.docass_docass_id_seq OWNER TO admin;

--
-- TOC entry 9104 (class 0 OID 0)
-- Dependencies: 247
-- Name: docass_docass_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: admin
--

ALTER SEQUENCE docass_docass_id_seq OWNED BY docass.docass_id;


--
-- TOC entry 248 (class 1259 OID 146567319)
-- Dependencies: 6086 8
-- 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 public.image OWNER TO admin;

--
-- TOC entry 9106 (class 0 OID 0)
-- Dependencies: 248
-- Name: TABLE image; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE image IS 'Image information';


--
-- TOC entry 249 (class 1259 OID 146567326)
-- Dependencies: 6087 6088 8
-- 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 public.imageass OWNER TO admin;

--
-- TOC entry 9108 (class 0 OID 0)
-- Dependencies: 249
-- Name: TABLE imageass; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE imageass IS 'Image Assignement References';



--
-- TOC entry 250 (class 1259 OID 146567334)
-- Dependencies: 8482 6
-- 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 api.accountimage OWNER TO admin;

--
-- TOC entry 9110 (class 0 OID 0)
-- Dependencies: 250
-- Name: VIEW accountimage; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW accountimage IS 'Account Image';


--
-- TOC entry 251 (class 1259 OID 146567338)
-- Dependencies: 8483 6
-- 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 api.address OWNER TO admin;

--
-- TOC entry 9112 (class 0 OID 0)
-- Dependencies: 251
-- Name: VIEW address; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW address IS 'Address';


--
-- TOC entry 252 (class 1259 OID 146567342)
-- Dependencies: 8484 6
-- 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 api.addresschar OWNER TO admin;

--
-- TOC entry 9114 (class 0 OID 0)
-- Dependencies: 252
-- Name: VIEW addresschar; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW addresschar IS 'Address Characteristics';


--
-- TOC entry 253 (class 1259 OID 146567346)
-- Dependencies: 8485 6
-- 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 api.addresscomment OWNER TO admin;

--
-- TOC entry 9116 (class 0 OID 0)
-- Dependencies: 253
-- Name: VIEW addresscomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW addresscomment IS 'Address Comment';



--
-- TOC entry 254 (class 1259 OID 146567350)
-- Dependencies: 6089 6090 6091 8
-- 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 public.bomhead OWNER TO admin;

--
-- TOC entry 9118 (class 0 OID 0)
-- Dependencies: 254
-- Name: TABLE bomhead; Type: COMMENT; Schema: public; Owner: admin
--

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



--
-- TOC entry 255 (class 1259 OID 146567359)
-- Dependencies: 8486 6
-- 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 api.bom OWNER TO admin;

--
-- TOC entry 9120 (class 0 OID 0)
-- Dependencies: 255
-- Name: VIEW bom; Type: COMMENT; Schema: api; Owner: admin
--

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


--
-- TOC entry 256 (class 1259 OID 146567363)
-- Dependencies: 8487 6
-- 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 api.bomitem OWNER TO admin;

--
-- TOC entry 9122 (class 0 OID 0)
-- Dependencies: 256
-- Name: VIEW bomitem; Type: COMMENT; Schema: api; Owner: admin
--

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


--
-- TOC entry 257 (class 1259 OID 146567368)
-- Dependencies: 8488 6
-- 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 api.bomitemcomment OWNER TO admin;

--
-- TOC entry 9124 (class 0 OID 0)
-- Dependencies: 257
-- Name: VIEW bomitemcomment; Type: COMMENT; Schema: api; Owner: admin
--

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



--
-- TOC entry 258 (class 1259 OID 146567373)
-- Dependencies: 8
-- 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 public.bomitemsub OWNER TO admin;

--
-- TOC entry 9126 (class 0 OID 0)
-- Dependencies: 258
-- Name: TABLE bomitemsub; Type: COMMENT; Schema: public; Owner: admin
--

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



--
-- TOC entry 259 (class 1259 OID 146567376)
-- Dependencies: 8489 6
-- 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 api.bomitemsubstitute OWNER TO admin;

--
-- TOC entry 9128 (class 0 OID 0)
-- Dependencies: 259
-- Name: VIEW bomitemsubstitute; Type: COMMENT; Schema: api; Owner: admin
--

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



--
-- TOC entry 260 (class 1259 OID 146567381)
-- Dependencies: 6094 8
-- 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 public.budghead OWNER TO admin;


--
-- TOC entry 261 (class 1259 OID 146567388)
-- Dependencies: 8490 6
-- 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 api.budget OWNER TO admin;

--
-- TOC entry 9131 (class 0 OID 0)
-- Dependencies: 261
-- Name: VIEW budget; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW budget IS 'Budget Header';



--
-- TOC entry 262 (class 1259 OID 146567392)
-- Dependencies: 6095 6096 6097 6098 8
-- 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 public.accnt OWNER TO admin;

--
-- TOC entry 9133 (class 0 OID 0)
-- Dependencies: 262
-- Name: TABLE accnt; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 263 (class 1259 OID 146567402)
-- Dependencies: 8
-- 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 public.budgitem OWNER TO admin;

--
-- TOC entry 264 (class 1259 OID 146567405)
-- Dependencies: 6101 8
-- 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 public.period OWNER TO admin;

--
-- TOC entry 9136 (class 0 OID 0)
-- Dependencies: 264
-- Name: TABLE period; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE period IS 'Accounting Period information';



--
-- TOC entry 265 (class 1259 OID 146567412)
-- Dependencies: 8491 6
-- 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 api.budgetentry OWNER TO admin;

--
-- TOC entry 9138 (class 0 OID 0)
-- Dependencies: 265
-- Name: VIEW budgetentry; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW budgetentry IS 'Budget Entry';



--
-- TOC entry 266 (class 1259 OID 146567416)
-- Dependencies: 6102 6103 6104 6105 6106 6108 8
-- 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,
    CONSTRAINT bankaccnt_bankaccnt_name_check CHECK ((bankaccnt_name <> ''::text))
);


ALTER TABLE public.bankaccnt OWNER TO admin;

--
-- TOC entry 9140 (class 0 OID 0)
-- Dependencies: 266
-- Name: TABLE bankaccnt; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE bankaccnt IS 'Bank Account information';


--
-- TOC entry 267 (class 1259 OID 146567428)
-- Dependencies: 6109 6110 6111 6112 6113 6114 6115 6117 8
-- 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,
    CONSTRAINT cashrcpt_cashrcpt_number_check CHECK ((cashrcpt_number <> ''::text))
);


ALTER TABLE public.cashrcpt OWNER TO admin;

--
-- TOC entry 9142 (class 0 OID 0)
-- Dependencies: 267
-- 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';



--
-- TOC entry 268 (class 1259 OID 146567442)
-- Dependencies: 8492 6
-- 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 api.cashreceipt OWNER TO admin;

--
-- TOC entry 9144 (class 0 OID 0)
-- Dependencies: 268
-- 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';



--
-- TOC entry 269 (class 1259 OID 146567447)
-- Dependencies: 6119 6120 8
-- 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 public.cashrcptitem OWNER TO admin;

--
-- TOC entry 9146 (class 0 OID 0)
-- Dependencies: 269
-- 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';



--
-- TOC entry 270 (class 1259 OID 146567452)
-- Dependencies: 8493 6
-- 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 api.cashreceiptapply OWNER TO admin;

--
-- TOC entry 9148 (class 0 OID 0)
-- Dependencies: 270
-- 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';



--
-- TOC entry 271 (class 1259 OID 146567457)
-- Dependencies: 8
-- 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 public.cashrcptmisc OWNER TO admin;

--
-- TOC entry 9150 (class 0 OID 0)
-- Dependencies: 271
-- Name: TABLE cashrcptmisc; Type: COMMENT; Schema: public; Owner: admin
--

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



--
-- TOC entry 272 (class 1259 OID 146567463)
-- Dependencies: 8494 6
-- 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 api.cashreceiptapplymisc OWNER TO admin;

--
-- TOC entry 9152 (class 0 OID 0)
-- Dependencies: 272
-- 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';


--
-- TOC entry 273 (class 1259 OID 146567468)
-- Dependencies: 8495 6
-- 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 api.contact OWNER TO admin;

--
-- TOC entry 9154 (class 0 OID 0)
-- Dependencies: 273
-- Name: VIEW contact; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW contact IS 'Contact';


--
-- TOC entry 274 (class 1259 OID 146567473)
-- Dependencies: 8496 6
-- 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 api.contactchar OWNER TO admin;

--
-- TOC entry 9156 (class 0 OID 0)
-- Dependencies: 274
-- Name: VIEW contactchar; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW contactchar IS 'Contact Characteristics';


--
-- TOC entry 275 (class 1259 OID 146567477)
-- Dependencies: 8497 6
-- 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 api.contactcomment OWNER TO admin;

--
-- TOC entry 9158 (class 0 OID 0)
-- Dependencies: 275
-- Name: VIEW contactcomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW contactcomment IS 'Contact Comment';


--
-- TOC entry 276 (class 1259 OID 146567481)
-- Dependencies: 8498 6
-- 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 api.custchar OWNER TO admin;

--
-- TOC entry 9160 (class 0 OID 0)
-- Dependencies: 276
-- Name: VIEW custchar; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW custchar IS 'Customer Characteristics';


--
-- TOC entry 277 (class 1259 OID 146567486)
-- Dependencies: 8499 6
-- 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 api.custcomment OWNER TO admin;

--
-- TOC entry 9162 (class 0 OID 0)
-- Dependencies: 277
-- Name: VIEW custcomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW custcomment IS 'Customer Comment';



--
-- TOC entry 278 (class 1259 OID 146567491)
-- Dependencies: 6122 6123 6124 6125 6126 6127 6128 8
-- 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 public.ccard OWNER TO admin;

--
-- TOC entry 9164 (class 0 OID 0)
-- Dependencies: 278
-- Name: TABLE ccard; Type: COMMENT; Schema: public; Owner: admin
--

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



--
-- TOC entry 279 (class 1259 OID 146567504)
-- Dependencies: 8500 6
-- 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 api.custcreditcard OWNER TO admin;

--
-- TOC entry 9166 (class 0 OID 0)
-- Dependencies: 279
-- Name: VIEW custcreditcard; Type: COMMENT; Schema: api; Owner: admin
--

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



--
-- TOC entry 280 (class 1259 OID 146567509)
-- Dependencies: 6130 6131 6132 8
-- 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 public.custtype OWNER TO admin;

--
-- TOC entry 9168 (class 0 OID 0)
-- Dependencies: 280
-- Name: TABLE custtype; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE custtype IS 'Customer Type information';


--
-- TOC entry 281 (class 1259 OID 146567518)
-- Dependencies: 6134 8
-- 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 public.shipchrg OWNER TO admin;

--
-- TOC entry 9170 (class 0 OID 0)
-- Dependencies: 281
-- Name: TABLE shipchrg; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE shipchrg IS 'Shipping Charge information';


--
-- TOC entry 282 (class 1259 OID 146567525)
-- Dependencies: 6135 6136 8
-- 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 public.shipform OWNER TO admin;

--
-- TOC entry 9172 (class 0 OID 0)
-- Dependencies: 282
-- Name: TABLE shipform; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE shipform IS 'Shipping Form information';


--
-- TOC entry 9173 (class 0 OID 0)
-- Dependencies: 282
-- 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.';



--
-- TOC entry 283 (class 1259 OID 146567534)
-- Dependencies: 8501 6
-- 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 api.customer OWNER TO admin;

--
-- TOC entry 9175 (class 0 OID 0)
-- Dependencies: 283
-- Name: VIEW customer; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW customer IS 'Customer';



--
-- TOC entry 284 (class 1259 OID 146567539)
-- Dependencies: 6138 8
-- 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 public.taxauth OWNER TO admin;

--
-- TOC entry 9177 (class 0 OID 0)
-- Dependencies: 284
-- Name: TABLE taxauth; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 9178 (class 0 OID 0)
-- Dependencies: 284
-- 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.';


--
-- TOC entry 285 (class 1259 OID 146567549)
-- Dependencies: 6139 6140 6141 6143 8
-- 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 public.taxreg OWNER TO admin;

--
-- TOC entry 9180 (class 0 OID 0)
-- Dependencies: 285
-- 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.';


--
-- TOC entry 9181 (class 0 OID 0)
-- Dependencies: 285
-- 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.';



--
-- TOC entry 286 (class 1259 OID 146567560)
-- Dependencies: 8502 6
-- 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 api.customertaxreg OWNER TO admin;

--
-- TOC entry 9183 (class 0 OID 0)
-- Dependencies: 286
-- Name: VIEW customertaxreg; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW customertaxreg IS 'Customer Tax Registrations';


--
-- TOC entry 287 (class 1259 OID 146567566)
-- Dependencies: 8503 6
-- 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 api.customertype OWNER TO admin;

--
-- TOC entry 9185 (class 0 OID 0)
-- Dependencies: 287
-- Name: VIEW customertype; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW customertype IS 'Customer Type';


--
-- TOC entry 288 (class 1259 OID 146567570)
-- Dependencies: 8504 6
-- 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 api.customertypechar OWNER TO admin;

--
-- TOC entry 9187 (class 0 OID 0)
-- Dependencies: 288
-- Name: VIEW customertypechar; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW customertypechar IS 'Customer Type Characteristics';


--
-- TOC entry 289 (class 1259 OID 146567574)
-- Dependencies: 8505 6
-- 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 api.custshipto OWNER TO admin;

--
-- TOC entry 9189 (class 0 OID 0)
-- Dependencies: 289
-- Name: VIEW custshipto; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW custshipto IS 'Customer Shipto Address';


--
-- TOC entry 290 (class 1259 OID 146567579)
-- Dependencies: 8506 6
-- 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 api.custtax OWNER TO admin;

--
-- TOC entry 9191 (class 0 OID 0)
-- Dependencies: 290
-- Name: VIEW custtax; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW custtax IS 'Customer Tax Registration';



--
-- TOC entry 291 (class 1259 OID 146567583)
-- Dependencies: 6145 8
-- 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 public.dept OWNER TO admin;

--
-- TOC entry 9193 (class 0 OID 0)
-- Dependencies: 291
-- Name: TABLE dept; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE dept IS 'List of Departments';


--
-- TOC entry 292 (class 1259 OID 146567590)
-- Dependencies: 6146 6147 6148 6150 6151 6152 6153 8
-- 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 public.emp OWNER TO admin;

--
-- TOC entry 9195 (class 0 OID 0)
-- Dependencies: 292
-- 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.';


--
-- TOC entry 9196 (class 0 OID 0)
-- Dependencies: 292
-- 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.';


--
-- TOC entry 9197 (class 0 OID 0)
-- Dependencies: 292
-- 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.';


--
-- TOC entry 9198 (class 0 OID 0)
-- Dependencies: 292
-- 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.';


--
-- TOC entry 9199 (class 0 OID 0)
-- Dependencies: 292
-- 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.';


--
-- TOC entry 9200 (class 0 OID 0)
-- Dependencies: 292
-- 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.';


--
-- TOC entry 9201 (class 0 OID 0)
-- Dependencies: 292
-- 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.';


--
-- TOC entry 9202 (class 0 OID 0)
-- Dependencies: 292
-- 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.';


--
-- TOC entry 293 (class 1259 OID 146567603)
-- Dependencies: 6155 8
-- 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 public.shift OWNER TO admin;

--
-- TOC entry 9204 (class 0 OID 0)
-- Dependencies: 293
-- Name: TABLE shift; Type: COMMENT; Schema: public; Owner: admin
--

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



--
-- TOC entry 294 (class 1259 OID 146567610)
-- Dependencies: 8507 6
-- 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))) JOIN public.curr_symbol ON ((e.emp_wage_curr_id = curr_symbol.curr_id)));


ALTER TABLE api.employee OWNER TO admin;

--
-- TOC entry 9206 (class 0 OID 0)
-- Dependencies: 294
-- Name: VIEW employee; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW employee IS 'Employee';


--
-- TOC entry 295 (class 1259 OID 146567615)
-- Dependencies: 8508 6
-- 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 api.employeechar OWNER TO admin;

--
-- TOC entry 9208 (class 0 OID 0)
-- Dependencies: 295
-- Name: VIEW employeechar; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW employeechar IS 'Employee Characteristics';


--
-- TOC entry 296 (class 1259 OID 146567619)
-- Dependencies: 8509 6
-- 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 api.employeecomment OWNER TO admin;

--
-- TOC entry 9210 (class 0 OID 0)
-- Dependencies: 296
-- Name: VIEW employeecomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW employeecomment IS 'Employee Comment';



--
-- TOC entry 297 (class 1259 OID 146567623)
-- Dependencies: 6156 6157 6158 6159 8
-- 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 public.shipdata OWNER TO admin;

--
-- TOC entry 9212 (class 0 OID 0)
-- Dependencies: 297
-- 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';



--
-- TOC entry 298 (class 1259 OID 146567633)
-- Dependencies: 8510 6
-- 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 api.extshipmaint OWNER TO admin;

--
-- TOC entry 9214 (class 0 OID 0)
-- Dependencies: 298
-- Name: VIEW extshipmaint; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW extshipmaint IS 'External Shipping Maintenance';



--
-- TOC entry 299 (class 1259 OID 146567638)
-- Dependencies: 6161 8
-- 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 public.freightclass OWNER TO admin;

--
-- TOC entry 9216 (class 0 OID 0)
-- Dependencies: 299
-- Name: TABLE freightclass; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 300 (class 1259 OID 146567645)
-- Dependencies: 6163 6164 8
-- 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 public.ipsfreight OWNER TO admin;

--
-- TOC entry 301 (class 1259 OID 146567653)
-- Dependencies: 6165 6166 6167 8
-- 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 public.ipshead OWNER TO admin;

--
-- TOC entry 9219 (class 0 OID 0)
-- Dependencies: 301
-- Name: TABLE ipshead; Type: COMMENT; Schema: public; Owner: admin
--

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



--
-- TOC entry 302 (class 1259 OID 146567662)
-- Dependencies: 8511 6
-- 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 api.freightpricingscheduleitem OWNER TO admin;

--
-- TOC entry 9221 (class 0 OID 0)
-- Dependencies: 302
-- Name: VIEW freightpricingscheduleitem; Type: COMMENT; Schema: api; Owner: admin
--

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


--
-- TOC entry 303 (class 1259 OID 146567667)
-- Dependencies: 8512 6
-- 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 api.glaccount OWNER TO admin;

--
-- TOC entry 9223 (class 0 OID 0)
-- Dependencies: 303
-- Name: VIEW glaccount; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW glaccount IS 'GL Account';



--
-- TOC entry 304 (class 1259 OID 146567672)
-- Dependencies: 6169 8
-- 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 public.incdtcat OWNER TO admin;

--
-- TOC entry 9225 (class 0 OID 0)
-- Dependencies: 304
-- Name: TABLE incdtcat; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE incdtcat IS 'Incident Category table';


--
-- TOC entry 305 (class 1259 OID 146567679)
-- Dependencies: 6171 8
-- 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 public.incdtpriority OWNER TO admin;

--
-- TOC entry 9227 (class 0 OID 0)
-- Dependencies: 305
-- Name: TABLE incdtpriority; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE incdtpriority IS 'Incident Priority table';


--
-- TOC entry 306 (class 1259 OID 146567686)
-- Dependencies: 6173 8
-- 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 public.incdtresolution OWNER TO admin;

--
-- TOC entry 9229 (class 0 OID 0)
-- Dependencies: 306
-- Name: TABLE incdtresolution; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE incdtresolution IS 'Incident Resolution table';


--
-- TOC entry 307 (class 1259 OID 146567693)
-- Dependencies: 6175 8
-- 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 public.incdtseverity OWNER TO admin;

--
-- TOC entry 9231 (class 0 OID 0)
-- Dependencies: 307
-- Name: TABLE incdtseverity; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE incdtseverity IS 'Incident Severity table';



--
-- TOC entry 308 (class 1259 OID 146567700)
-- Dependencies: 8513 6
-- 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 api.incident OWNER TO admin;

--
-- TOC entry 9233 (class 0 OID 0)
-- Dependencies: 308
-- Name: VIEW incident; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW incident IS 'Incident';


--
-- TOC entry 309 (class 1259 OID 146567705)
-- Dependencies: 8514 6
-- 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 api.incidentchar OWNER TO admin;

--
-- TOC entry 9235 (class 0 OID 0)
-- Dependencies: 309
-- Name: VIEW incidentchar; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW incidentchar IS 'Incident Characteristics';


--
-- TOC entry 310 (class 1259 OID 146567709)
-- Dependencies: 8515 6
-- 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 api.incidentcomment OWNER TO admin;

--
-- TOC entry 9237 (class 0 OID 0)
-- Dependencies: 310
-- Name: VIEW incidentcomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW incidentcomment IS 'Incident Comment';


--
-- TOC entry 311 (class 1259 OID 146567713)
-- Dependencies: 8516 6
-- 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 api.incidentfile OWNER TO admin;

--
-- TOC entry 9239 (class 0 OID 0)
-- Dependencies: 311
-- Name: VIEW incidentfile; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW incidentfile IS 'Incident File';


--
-- TOC entry 312 (class 1259 OID 146567717)
-- Dependencies: 8517 6
-- 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 api.incidentimage OWNER TO admin;

--
-- TOC entry 9241 (class 0 OID 0)
-- Dependencies: 312
-- Name: VIEW incidentimage; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW incidentimage IS 'Incident Image';



--
-- TOC entry 313 (class 1259 OID 146567721)
-- Dependencies: 6176 6177 8
-- 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 public.classcode OWNER TO admin;

--
-- TOC entry 9243 (class 0 OID 0)
-- Dependencies: 313
-- Name: TABLE classcode; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE classcode IS 'Class Code information';


--
-- TOC entry 314 (class 1259 OID 146567729)
-- Dependencies: 6178 6179 8
-- 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 public.prodcat OWNER TO admin;

--
-- TOC entry 9245 (class 0 OID 0)
-- Dependencies: 314
-- Name: TABLE prodcat; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE prodcat IS 'Product Category information';



--
-- TOC entry 315 (class 1259 OID 146567737)
-- Dependencies: 8518 6
-- 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 api.item OWNER TO admin;

--
-- TOC entry 9247 (class 0 OID 0)
-- Dependencies: 315
-- Name: VIEW item; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW item IS 'Item';



--
-- TOC entry 316 (class 1259 OID 146567742)
-- Dependencies: 6180 6181 8
-- 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 public.itemalias OWNER TO admin;

--
-- TOC entry 9249 (class 0 OID 0)
-- Dependencies: 316
-- Name: TABLE itemalias; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE itemalias IS 'Item Alias information';


--
-- TOC entry 9250 (class 0 OID 0)
-- Dependencies: 316
-- 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.';



--
-- TOC entry 317 (class 1259 OID 146567750)
-- Dependencies: 8519 6
-- 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, itemalias.itemalias_usedescrip AS use_description, itemalias.itemalias_descrip1 AS description1, itemalias.itemalias_descrip2 AS description2, itemalias.itemalias_comments AS comments FROM public.item, public.itemalias WHERE (item.item_id = itemalias.itemalias_item_id);


ALTER TABLE api.itemalias OWNER TO admin;

--
-- TOC entry 9252 (class 0 OID 0)
-- Dependencies: 317
-- Name: VIEW itemalias; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW itemalias IS 'Item Alias';


--
-- TOC entry 318 (class 1259 OID 146567754)
-- Dependencies: 8520 6
-- 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 api.itemchar OWNER TO admin;

--
-- TOC entry 9254 (class 0 OID 0)
-- Dependencies: 318
-- Name: VIEW itemchar; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW itemchar IS 'Item Characteristic';


--
-- TOC entry 319 (class 1259 OID 146567758)
-- Dependencies: 8521 6
-- 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 api.itemcomment OWNER TO admin;

--
-- TOC entry 9256 (class 0 OID 0)
-- Dependencies: 319
-- Name: VIEW itemcomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW itemcomment IS 'Item Comments';



--
-- TOC entry 320 (class 1259 OID 146567762)
-- Dependencies: 6182 6183 8
-- 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 public.costelem OWNER TO admin;

--
-- TOC entry 9258 (class 0 OID 0)
-- Dependencies: 320
-- Name: TABLE costelem; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE costelem IS 'Costing Element information';


--
-- TOC entry 321 (class 1259 OID 146567770)
-- Dependencies: 6184 6185 6186 6187 6188 8
-- 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 public.itemcost OWNER TO admin;

--
-- TOC entry 9260 (class 0 OID 0)
-- Dependencies: 321
-- Name: TABLE itemcost; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE itemcost IS 'Item Cost information';



--
-- TOC entry 322 (class 1259 OID 146567778)
-- Dependencies: 8522 6
-- 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 api.itemcost OWNER TO admin;

--
-- TOC entry 9262 (class 0 OID 0)
-- Dependencies: 322
-- Name: VIEW itemcost; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW itemcost IS 'Item Cost';


--
-- TOC entry 323 (class 1259 OID 146567783)
-- Dependencies: 8523 6
-- 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 api.itemfile OWNER TO admin;

--
-- TOC entry 9264 (class 0 OID 0)
-- Dependencies: 323
-- Name: VIEW itemfile; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW itemfile IS 'Item File';


--
-- TOC entry 324 (class 1259 OID 146567787)
-- Dependencies: 8524 6
-- 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 api.itemimage OWNER TO admin;

--
-- TOC entry 9266 (class 0 OID 0)
-- Dependencies: 324
-- Name: VIEW itemimage; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW itemimage IS 'Item Image';



--
-- TOC entry 325 (class 1259 OID 146567792)
-- Dependencies: 6189 6190 8
-- 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 public.costcat OWNER TO admin;

--
-- TOC entry 9268 (class 0 OID 0)
-- Dependencies: 325
-- Name: TABLE costcat; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE costcat IS 'Cost Category information';


--
-- TOC entry 326 (class 1259 OID 146567800)
-- Dependencies: 6191 6192 8
-- 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 public.plancode OWNER TO admin;

--
-- TOC entry 9270 (class 0 OID 0)
-- Dependencies: 326
-- Name: TABLE plancode; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE plancode IS 'Planner Code information';



--
-- TOC entry 327 (class 1259 OID 146567808)
-- Dependencies: 8525 6
-- 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 api.itemsite OWNER TO admin;

--
-- TOC entry 9272 (class 0 OID 0)
-- Dependencies: 327
-- Name: VIEW itemsite; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW itemsite IS 'Item Site';


--
-- TOC entry 328 (class 1259 OID 146567813)
-- Dependencies: 8526 6
-- 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 api.itemsitecomment OWNER TO admin;

--
-- TOC entry 9274 (class 0 OID 0)
-- Dependencies: 328
-- Name: VIEW itemsitecomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW itemsitecomment IS 'Item Site Comments';



--
-- TOC entry 329 (class 1259 OID 146567818)
-- Dependencies: 8
-- 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 public.contrct OWNER TO admin;

--
-- TOC entry 9276 (class 0 OID 0)
-- Dependencies: 329
-- 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.';


--
-- TOC entry 9277 (class 0 OID 0)
-- Dependencies: 329
-- Name: COLUMN contrct.contrct_id; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 9278 (class 0 OID 0)
-- Dependencies: 329
-- Name: COLUMN contrct.contrct_number; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 9279 (class 0 OID 0)
-- Dependencies: 329
-- Name: COLUMN contrct.contrct_vend_id; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 9280 (class 0 OID 0)
-- Dependencies: 329
-- Name: COLUMN contrct.contrct_descrip; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 9281 (class 0 OID 0)
-- Dependencies: 329
-- 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.';


--
-- TOC entry 9282 (class 0 OID 0)
-- Dependencies: 329
-- 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.';


--
-- TOC entry 9283 (class 0 OID 0)
-- Dependencies: 329
-- Name: COLUMN contrct.contrct_note; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 330 (class 1259 OID 146567824)
-- Dependencies: 6194 6195 6196 6197 6198 6199 6200 8
-- 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 public.itemsrc OWNER TO admin;

--
-- TOC entry 9285 (class 0 OID 0)
-- Dependencies: 330
-- Name: TABLE itemsrc; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE itemsrc IS 'Item Source information';


--
-- TOC entry 9286 (class 0 OID 0)
-- Dependencies: 330
-- 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.';


--
-- TOC entry 9287 (class 0 OID 0)
-- Dependencies: 330
-- 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.';


--
-- TOC entry 9288 (class 0 OID 0)
-- Dependencies: 330
-- 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.';



--
-- TOC entry 331 (class 1259 OID 146567837)
-- Dependencies: 8527 6
-- 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 api.itemsource OWNER TO admin;

--
-- TOC entry 9290 (class 0 OID 0)
-- Dependencies: 331
-- Name: VIEW itemsource; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW itemsource IS 'Item Source';



--
-- TOC entry 332 (class 1259 OID 146567842)
-- Dependencies: 6201 6202 6203 6204 6205 8
-- 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 public.itemsrcp OWNER TO admin;

--
-- TOC entry 9292 (class 0 OID 0)
-- Dependencies: 332
-- Name: TABLE itemsrcp; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 9293 (class 0 OID 0)
-- Dependencies: 332
-- 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.';


--
-- TOC entry 9294 (class 0 OID 0)
-- Dependencies: 332
-- 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.';


--
-- TOC entry 9295 (class 0 OID 0)
-- Dependencies: 332
-- 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.';


--
-- TOC entry 9296 (class 0 OID 0)
-- Dependencies: 332
-- Name: COLUMN itemsrcp.itemsrcp_discntprcnt; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 9297 (class 0 OID 0)
-- Dependencies: 332
-- 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.';



--
-- TOC entry 333 (class 1259 OID 146567850)
-- Dependencies: 8528 6
-- 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 api.itemsourceprice OWNER TO admin;

--
-- TOC entry 9299 (class 0 OID 0)
-- Dependencies: 333
-- Name: VIEW itemsourceprice; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW itemsourceprice IS 'Item Source Price';



--
-- TOC entry 334 (class 1259 OID 146567855)
-- Dependencies: 6206 8
-- 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 public.itemsub OWNER TO admin;

--
-- TOC entry 9301 (class 0 OID 0)
-- Dependencies: 334
-- Name: TABLE itemsub; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE itemsub IS 'Item Substitutes information';



--
-- TOC entry 335 (class 1259 OID 146567859)
-- Dependencies: 8529 6
-- 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 api.itemsubstitute OWNER TO admin;

--
-- TOC entry 9303 (class 0 OID 0)
-- Dependencies: 335
-- Name: VIEW itemsubstitute; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW itemsubstitute IS 'Item Substitute';



--
-- TOC entry 336 (class 1259 OID 146567863)
-- Dependencies: 8
-- 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 public.itemtax OWNER TO admin;

--
-- TOC entry 9305 (class 0 OID 0)
-- Dependencies: 336
-- 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.';



--
-- TOC entry 337 (class 1259 OID 146567866)
-- Dependencies: 8530 6
-- 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 api.itemtaxtype OWNER TO admin;

--
-- TOC entry 9307 (class 0 OID 0)
-- Dependencies: 337
-- Name: VIEW itemtaxtype; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW itemtaxtype IS 'Item Tax Type';



--
-- TOC entry 338 (class 1259 OID 146567870)
-- Dependencies: 6209 6210 8
-- 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,
    CONSTRAINT itemuomconv_uom CHECK ((itemuomconv_from_uom_id <> itemuomconv_to_uom_id))
);


ALTER TABLE public.itemuomconv OWNER TO admin;

--
-- TOC entry 9309 (class 0 OID 0)
-- Dependencies: 338
-- 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.';



--
-- TOC entry 339 (class 1259 OID 146567875)
-- Dependencies: 8531 6
-- 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 api.itemuomconversion OWNER TO admin;

--
-- TOC entry 9311 (class 0 OID 0)
-- Dependencies: 339
-- Name: VIEW itemuomconversion; Type: COMMENT; Schema: api; Owner: admin
--

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



--
-- TOC entry 340 (class 1259 OID 146567879)
-- Dependencies: 6211 6212 6213 6214 8
-- 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 public.gltrans OWNER TO admin;

--
-- TOC entry 9313 (class 0 OID 0)
-- Dependencies: 340
-- Name: TABLE gltrans; Type: COMMENT; Schema: public; Owner: admin
--

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



--
-- TOC entry 341 (class 1259 OID 146567889)
-- Dependencies: 8532 6
-- 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 api.journalentry OWNER TO admin;

--
-- TOC entry 9315 (class 0 OID 0)
-- Dependencies: 341
-- Name: VIEW journalentry; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW journalentry IS 'Journal Entry';



--
-- TOC entry 342 (class 1259 OID 146567894)
-- Dependencies: 6215 8
-- 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
);


ALTER TABLE public.location OWNER TO admin;

--
-- TOC entry 9317 (class 0 OID 0)
-- Dependencies: 342
-- Name: TABLE location; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE location IS 'Warehouse Location information';


--
-- TOC entry 343 (class 1259 OID 146567901)
-- Dependencies: 8
-- 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 public.whsezone OWNER TO admin;

--
-- TOC entry 9319 (class 0 OID 0)
-- Dependencies: 343
-- Name: TABLE whsezone; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE whsezone IS 'Warehouse Zone information';



--
-- TOC entry 344 (class 1259 OID 146567907)
-- Dependencies: 8533 6
-- 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_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 api.location OWNER TO admin;

--
-- TOC entry 9321 (class 0 OID 0)
-- Dependencies: 344
-- Name: VIEW location; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW location IS 'Location';


--
-- TOC entry 345 (class 1259 OID 146567912)
-- Dependencies: 8534 6
-- 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 api.misccounttag OWNER TO admin;

--
-- TOC entry 9323 (class 0 OID 0)
-- Dependencies: 345
-- Name: VIEW misccounttag; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW misccounttag IS 'Miscellaneous Count Tag';


--
-- TOC entry 346 (class 1259 OID 146567916)
-- Dependencies: 8535 6
-- 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 api.pricingschedule OWNER TO admin;

--
-- TOC entry 9325 (class 0 OID 0)
-- Dependencies: 346
-- Name: VIEW pricingschedule; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW pricingschedule IS 'Pricing Schedule';



--
-- TOC entry 347 (class 1259 OID 146567920)
-- Dependencies: 6218 8
-- 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 public.ipsass OWNER TO admin;

--
-- TOC entry 9327 (class 0 OID 0)
-- Dependencies: 347
-- Name: TABLE ipsass; Type: COMMENT; Schema: public; Owner: admin
--

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



--
-- TOC entry 348 (class 1259 OID 146567927)
-- Dependencies: 8536 6
-- 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 api.pricingscheduleassign OWNER TO admin;

--
-- TOC entry 9329 (class 0 OID 0)
-- Dependencies: 348
-- Name: VIEW pricingscheduleassign; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW pricingscheduleassign IS 'Pricing Schedule Assignments';



--
-- TOC entry 349 (class 1259 OID 146567932)
-- Dependencies: 6219 6220 6221 6222 8
-- 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 public.ipsiteminfo OWNER TO admin;

--
-- TOC entry 9331 (class 0 OID 0)
-- Dependencies: 349
-- Name: TABLE ipsiteminfo; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 9332 (class 0 OID 0)
-- Dependencies: 349
-- 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.';


--
-- TOC entry 9333 (class 0 OID 0)
-- Dependencies: 349
-- 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';


--
-- TOC entry 9334 (class 0 OID 0)
-- Dependencies: 349
-- 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.';



--
-- TOC entry 350 (class 1259 OID 146567939)
-- Dependencies: 8537 6
-- 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 api.pricingscheduleitem OWNER TO admin;

--
-- TOC entry 9336 (class 0 OID 0)
-- Dependencies: 350
-- Name: VIEW pricingscheduleitem; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW pricingscheduleitem IS 'Pricing Schedule Item';



--
-- TOC entry 351 (class 1259 OID 146567944)
-- Dependencies: 8
-- 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 public.ipsitemchar OWNER TO admin;

--
-- TOC entry 9338 (class 0 OID 0)
-- Dependencies: 351
-- Name: TABLE ipsitemchar; Type: COMMENT; Schema: public; Owner: admin
--

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



--
-- TOC entry 352 (class 1259 OID 146567950)
-- Dependencies: 8538 6
-- 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 api.pricingscheduleitemchar OWNER TO admin;

--
-- TOC entry 9340 (class 0 OID 0)
-- Dependencies: 352
-- Name: VIEW pricingscheduleitemchar; Type: COMMENT; Schema: api; Owner: admin
--

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


--
-- TOC entry 353 (class 1259 OID 146567955)
-- Dependencies: 8539 6
-- 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 api.project OWNER TO admin;

--
-- TOC entry 9342 (class 0 OID 0)
-- Dependencies: 353
-- Name: VIEW project; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW project IS 'Project';


--
-- TOC entry 354 (class 1259 OID 146567959)
-- Dependencies: 8540 6
-- 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 api.projectcomment OWNER TO admin;

--
-- TOC entry 9344 (class 0 OID 0)
-- Dependencies: 354
-- Name: VIEW projectcomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW projectcomment IS 'Project Comment';



--
-- TOC entry 355 (class 1259 OID 146567963)
-- Dependencies: 8
-- 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 public.cust_cust_id_seq OWNER TO admin;

--
-- TOC entry 356 (class 1259 OID 146567965)
-- Dependencies: 6224 6225 6226 6227 8
-- 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 public.prospect OWNER TO admin;

--
-- TOC entry 9347 (class 0 OID 0)
-- Dependencies: 356
-- Name: TABLE prospect; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE prospect IS 'Prospect Information';



--
-- TOC entry 357 (class 1259 OID 146567975)
-- Dependencies: 8541 6
-- 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 api.prospect OWNER TO admin;

--
-- TOC entry 9349 (class 0 OID 0)
-- Dependencies: 357
-- Name: VIEW prospect; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW prospect IS 'Prospect';



--
-- TOC entry 358 (class 1259 OID 146567980)
-- Dependencies: 6229 8
-- 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 public.expcat OWNER TO admin;

--
-- TOC entry 9351 (class 0 OID 0)
-- Dependencies: 358
-- Name: TABLE expcat; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE expcat IS 'Expense Category information';


--
-- TOC entry 359 (class 1259 OID 146567987)
-- Dependencies: 6230 6231 6232 6233 6234 6235 8
-- 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 public.womatl OWNER TO admin;

--
-- TOC entry 9353 (class 0 OID 0)
-- Dependencies: 359
-- Name: TABLE womatl; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE womatl IS 'Work Order Material Requirements information';


--
-- TOC entry 9354 (class 0 OID 0)
-- Dependencies: 359
-- Name: COLUMN womatl.womatl_qtyfxd; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN womatl.womatl_qtyfxd IS 'The fixed quantity required';



--
-- TOC entry 360 (class 1259 OID 146567999)
-- Dependencies: 8542 6
-- 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 api.purchaseline OWNER TO admin;

--
-- TOC entry 9356 (class 0 OID 0)
-- Dependencies: 360
-- Name: VIEW purchaseline; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW purchaseline IS 'Purchase Order Line';


--
-- TOC entry 361 (class 1259 OID 146568004)
-- Dependencies: 8543 6
-- 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 api.purchaselinechar OWNER TO admin;

--
-- TOC entry 9358 (class 0 OID 0)
-- Dependencies: 361
-- Name: VIEW purchaselinechar; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW purchaselinechar IS 'Purchase Order Line Item Characteristic';


--
-- TOC entry 362 (class 1259 OID 146568009)
-- Dependencies: 8544 6
-- 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 api.purchaselinecomment OWNER TO admin;

--
-- TOC entry 9360 (class 0 OID 0)
-- Dependencies: 362
-- Name: VIEW purchaselinecomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW purchaselinecomment IS 'Purchase Order Line Item Comment';



--
-- TOC entry 363 (class 1259 OID 146568014)
-- Dependencies: 6236 6237 8
-- 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,
    CONSTRAINT tax_tax_code_check CHECK ((tax_code <> ''::text))
);


ALTER TABLE public.tax OWNER TO admin;

--
-- TOC entry 9362 (class 0 OID 0)
-- Dependencies: 363
-- Name: TABLE tax; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE tax IS 'Tax information';


--
-- TOC entry 9363 (class 0 OID 0)
-- Dependencies: 363
-- 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';


--
-- TOC entry 364 (class 1259 OID 146568022)
-- Dependencies: 6238 8
-- 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 public.vendaddrinfo OWNER TO admin;

--
-- TOC entry 9365 (class 0 OID 0)
-- Dependencies: 364
-- Name: TABLE vendaddrinfo; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE vendaddrinfo IS 'Vendor Address information';



--
-- TOC entry 365 (class 1259 OID 146568029)
-- Dependencies: 8545 6
-- 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 api.purchaseorder OWNER TO admin;

--
-- TOC entry 9367 (class 0 OID 0)
-- Dependencies: 365
-- Name: VIEW purchaseorder; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW purchaseorder IS 'Purchase Order';


--
-- TOC entry 366 (class 1259 OID 146568034)
-- Dependencies: 8546 6
-- 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 api.purchaseordercomment OWNER TO admin;

--
-- TOC entry 9369 (class 0 OID 0)
-- Dependencies: 366
-- Name: VIEW purchaseordercomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW purchaseordercomment IS 'Purchase Order Comment';



--
-- TOC entry 367 (class 1259 OID 146568038)
-- Dependencies: 6239 6240 6241 6242 6243 6244 6245 6246 8
-- 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 public.quhead OWNER TO admin;

--
-- TOC entry 9371 (class 0 OID 0)
-- Dependencies: 367
-- Name: TABLE quhead; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE quhead IS 'Quote header information';


--
-- TOC entry 9372 (class 0 OID 0)
-- Dependencies: 367
-- 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.';


--
-- TOC entry 9373 (class 0 OID 0)
-- Dependencies: 367
-- 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.';



--
-- TOC entry 368 (class 1259 OID 146568052)
-- Dependencies: 8547 6
-- 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 api.quote OWNER TO admin;

--
-- TOC entry 9375 (class 0 OID 0)
-- Dependencies: 368
-- Name: VIEW quote; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW quote IS 'Quote';


--
-- TOC entry 369 (class 1259 OID 146568057)
-- Dependencies: 8548 6
-- 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 api.quotecomment OWNER TO admin;

--
-- TOC entry 9377 (class 0 OID 0)
-- Dependencies: 369
-- Name: VIEW quotecomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW quotecomment IS 'Quote Comment';



--
-- TOC entry 370 (class 1259 OID 146568061)
-- Dependencies: 6247 6248 6249 6250 6251 8
-- 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 public.quitem OWNER TO admin;

--
-- TOC entry 9379 (class 0 OID 0)
-- Dependencies: 370
-- Name: TABLE quitem; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE quitem IS 'Quote Line Item information';


--
-- TOC entry 9380 (class 0 OID 0)
-- Dependencies: 370
-- 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';



--
-- TOC entry 371 (class 1259 OID 146568072)
-- Dependencies: 8549 6
-- 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 api.quoteline OWNER TO admin;

--
-- TOC entry 9382 (class 0 OID 0)
-- Dependencies: 371
-- Name: VIEW quoteline; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW quoteline IS 'Quote Line Item';


--
-- TOC entry 372 (class 1259 OID 146568077)
-- Dependencies: 8550 6
-- 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 api.quotelinechar OWNER TO admin;

--
-- TOC entry 9384 (class 0 OID 0)
-- Dependencies: 372
-- Name: VIEW quotelinechar; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW quotelinechar IS 'Quote Line Item Characteristics';


--
-- TOC entry 373 (class 1259 OID 146568082)
-- Dependencies: 8551 6
-- 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 api.quotelinecomment OWNER TO admin;

--
-- TOC entry 9386 (class 0 OID 0)
-- Dependencies: 373
-- Name: VIEW quotelinecomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW quotelinecomment IS 'Quote Line Item Comment';



--
-- TOC entry 374 (class 1259 OID 146568087)
-- Dependencies: 6252 6253 8
-- 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 public.aropenalloc OWNER TO admin;


--
-- TOC entry 375 (class 1259 OID 146568092)
-- Dependencies: 8552 6
-- 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 api.salescredit OWNER TO admin;

--
-- TOC entry 9389 (class 0 OID 0)
-- Dependencies: 375
-- Name: VIEW salescredit; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW salescredit IS 'Payments (credit memos) pre-applied to sales orders';



--
-- TOC entry 376 (class 1259 OID 146568097)
-- Dependencies: 6254 6255 6256 8
-- 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 public.cohist OWNER TO admin;

--
-- TOC entry 9391 (class 0 OID 0)
-- Dependencies: 376
-- Name: TABLE cohist; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE cohist IS 'Sales Order history';


--
-- TOC entry 9392 (class 0 OID 0)
-- Dependencies: 376
-- Name: COLUMN cohist.cohist_cohead_ccpay_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN cohist.cohist_cohead_ccpay_id IS 'Credit card payments made at sales order time (as opposed to invoice time) need special treatment. This field allows checking for this case.';


--
-- TOC entry 9393 (class 0 OID 0)
-- Dependencies: 376
-- Name: COLUMN cohist.cohist_saletype_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN cohist.cohist_saletype_id IS 'Associated sale type for sales history.';


--
-- TOC entry 9394 (class 0 OID 0)
-- Dependencies: 376
-- Name: COLUMN cohist.cohist_shipzone_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN cohist.cohist_shipzone_id IS 'Associated shipping zone for sales history.';


--
-- TOC entry 377 (class 1259 OID 146568106)
-- Dependencies: 8
-- Name: taxhist; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE taxhist (
    taxhist_id integer NOT NULL,
    taxhist_parent_id integer NOT NULL,
    taxhist_taxtype_id integer,
    taxhist_tax_id integer NOT NULL,
    taxhist_basis numeric(16,2) NOT NULL,
    taxhist_basis_tax_id integer,
    taxhist_sequence integer,
    taxhist_percent numeric(10,6) NOT NULL,
    taxhist_amount numeric(16,2) NOT NULL,
    taxhist_tax numeric(16,6) NOT NULL,
    taxhist_docdate date NOT NULL,
    taxhist_distdate date,
    taxhist_curr_id integer,
    taxhist_curr_rate numeric,
    taxhist_journalnumber integer
);


ALTER TABLE public.taxhist OWNER TO admin;

--
-- TOC entry 9396 (class 0 OID 0)
-- Dependencies: 377
-- Name: TABLE taxhist; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE taxhist IS 'A table type to record tax transaction history. Inherited by other tables that actually record history. As the parent, queries can be run against it that will join all child tables. ';


--
-- TOC entry 9397 (class 0 OID 0)
-- Dependencies: 377
-- Name: COLUMN taxhist.taxhist_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxhist.taxhist_id IS 'Primary key';


--
-- TOC entry 9398 (class 0 OID 0)
-- Dependencies: 377
-- Name: COLUMN taxhist.taxhist_parent_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxhist.taxhist_parent_id IS 'Source parent id.';


--
-- TOC entry 9399 (class 0 OID 0)
-- Dependencies: 377
-- Name: COLUMN taxhist.taxhist_taxtype_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxhist.taxhist_taxtype_id IS 'Tax type id';


--
-- TOC entry 9400 (class 0 OID 0)
-- Dependencies: 377
-- Name: COLUMN taxhist.taxhist_tax_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxhist.taxhist_tax_id IS 'Tax code id.';


--
-- TOC entry 9401 (class 0 OID 0)
-- Dependencies: 377
-- Name: COLUMN taxhist.taxhist_basis; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxhist.taxhist_basis IS 'Base price amount on which the tax calculation is based.';


--
-- TOC entry 9402 (class 0 OID 0)
-- Dependencies: 377
-- Name: COLUMN taxhist.taxhist_basis_tax_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxhist.taxhist_basis_tax_id IS 'Tax rate calculation basis.  If null, then the amount of the parent document, otherwise calculated on the result amount of the tax code id referenced.';


--
-- TOC entry 9403 (class 0 OID 0)
-- Dependencies: 377
-- Name: COLUMN taxhist.taxhist_amount; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxhist.taxhist_amount IS 'Flat tax amount.';


--
-- TOC entry 9404 (class 0 OID 0)
-- Dependencies: 377
-- Name: COLUMN taxhist.taxhist_tax; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxhist.taxhist_tax IS 'Calculated tax amount.';


--
-- TOC entry 9405 (class 0 OID 0)
-- Dependencies: 377
-- Name: COLUMN taxhist.taxhist_docdate; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxhist.taxhist_docdate IS 'The date of the parent document.';


--
-- TOC entry 9406 (class 0 OID 0)
-- Dependencies: 377
-- Name: COLUMN taxhist.taxhist_distdate; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxhist.taxhist_distdate IS 'The G/L distribution date of the parent document.';


--
-- TOC entry 379 (class 1259 OID 146568114)
-- Dependencies: 377 8
-- Name: cohisttax; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE cohisttax (
)
INHERITS (taxhist);


ALTER TABLE public.cohisttax OWNER TO admin;


--
-- TOC entry 380 (class 1259 OID 146568120)
-- Dependencies: 8553 6
-- Name: saleshistory; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW saleshistory AS
    SELECT custinfo.cust_number AS customer_number, item.item_number, whsinfo.warehous_code AS site_code, cohist.cohist_shipdate AS ship_date, cohist.cohist_shipvia AS ship_via, cohist.cohist_ordernumber AS order_number, cohist.cohist_ponumber AS purchase_order_number, cohist.cohist_orderdate AS order_date, cohist.cohist_invcnumber AS invoice_number, cohist.cohist_invcdate AS invoice_date, cohist.cohist_qtyshipped AS quantity_shipped, cohist.cohist_unitprice AS unit_price, shiptoinfo.shipto_num AS shipto_number, salesrep.salesrep_number AS sales_rep, cohist.cohist_duedate AS due_date, cohist.cohist_promisedate AS promise_date, cohist.cohist_imported AS imported, cohist.cohist_billtoname AS billto_name, cohist.cohist_billtoaddress1 AS billto_address1, cohist.cohist_billtoaddress2 AS billto_address2, cohist.cohist_billtoaddress3 AS billto_address3, cohist.cohist_billtocity AS billto_city, cohist.cohist_billtostate AS billto_state, cohist.cohist_billtozip AS billto_zip, cohist.cohist_shiptoname AS shipto_name, cohist.cohist_shiptoaddress1 AS shipto_address1, cohist.cohist_shiptoaddress2 AS shipto_address2, cohist.cohist_shiptoaddress3 AS shipto_address3, cohist.cohist_shiptocity AS shipto_city, cohist.cohist_shiptostate AS shipto_state, cohist.cohist_shiptozip AS shipto_zip, cohist.cohist_commission AS commission, cohist.cohist_commissionpaid AS commission_paid, cohist.cohist_unitcost AS unit_cost, CASE WHEN (cohist.cohist_misc_type IS NULL) THEN ''::text WHEN (cohist.cohist_misc_type = 'M'::bpchar) THEN 'Misc. Charge'::text WHEN (cohist.cohist_misc_type = 'F'::bpchar) THEN 'Freight'::text ELSE 'Unknown'::text END AS misc_type, cohist.cohist_misc_descrip AS misc_description, CASE WHEN (cohist.cohist_misc_id IS NULL) THEN ''::text WHEN (cohist.cohist_misc_type = 'M'::bpchar) THEN public.formatglaccount(cohist.cohist_misc_id) ELSE 'Unknown'::text END AS misc_info, taxzone.taxzone_code AS tax_zone, taxtype.taxtype_name AS tax_type, CASE WHEN (cohist.cohist_doctype = 'I'::text) THEN 'Invoice'::text WHEN (cohist.cohist_doctype = 'C'::text) THEN 'Credit Memo'::text ELSE 'Unknown'::text END AS document_type, curr_symbol.curr_abbr AS currency, cohist.cohist_sequence AS gl_sequence, (SELECT sum(cohisttax.taxhist_tax) AS sum FROM public.cohisttax WHERE (cohisttax.taxhist_parent_id = cohist.cohist_id)) AS tax FROM (((((((((public.cohist LEFT JOIN public.custinfo ON ((cohist.cohist_cust_id = custinfo.cust_id))) LEFT JOIN public.shiptoinfo ON ((cohist.cohist_shipto_id = shiptoinfo.shipto_id))) LEFT JOIN public.taxzone ON ((cohist.cohist_taxzone_id = taxzone.taxzone_id))) LEFT JOIN public.taxtype ON ((cohist.cohist_taxtype_id = taxtype.taxtype_id))) LEFT JOIN public.salesrep ON ((cohist.cohist_salesrep_id = salesrep.salesrep_id))) LEFT JOIN public.itemsite ON ((cohist.cohist_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.curr_symbol ON ((cohist.cohist_curr_id = curr_symbol.curr_id)));


ALTER TABLE api.saleshistory OWNER TO admin;

--
-- TOC entry 9409 (class 0 OID 0)
-- Dependencies: 380
-- Name: VIEW saleshistory; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW saleshistory IS 'Sales History';


--
-- TOC entry 381 (class 1259 OID 146568125)
-- Dependencies: 8554 6
-- Name: saleslinechar; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW saleslinechar AS
    SELECT (data.order_number)::character varying AS order_number, data.line_number, data.characteristic, COALESCE(si.charass_value, i3.charass_value) AS value, (COALESCE(si.charass_price, public.itemcharprice(data.item_id, data.char_id, COALESCE(si.charass_value, i3.charass_value), data.cohead_cust_id, data.cohead_shipto_id, data.coitem_qtyord, data.cohead_curr_id, data.cohead_orderdate), (0)::numeric))::numeric(16,4) AS price FROM (((((SELECT DISTINCT cohead.cohead_cust_id, cohead.cohead_shipto_id, cohead.cohead_curr_id, cohead.cohead_orderdate, coitem.coitem_id, coitem.coitem_itemsite_id, coitem.coitem_qtyord, "char".char_id, item.item_id, cohead.cohead_number AS order_number, CASE WHEN (coitem.coitem_subnumber = 0) THEN ((coitem.coitem_linenumber)::character varying)::text ELSE ((((coitem.coitem_linenumber)::character varying)::text || ('.'::character varying)::text) || ((coitem.coitem_subnumber)::character varying)::text) END AS line_number, "char".char_name AS characteristic FROM public.cohead, public.coitem, public.itemsite, public.item, public.charass, public."char" WHERE ((((((cohead.cohead_id = coitem.coitem_cohead_id) AND (coitem.coitem_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 si ON ((((data.coitem_id = si.charass_target_id) AND ('SI'::text = si.charass_target_type)) AND (si.charass_char_id = data.char_id)))) LEFT JOIN public.itemsite i1 ON ((data.coitem_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 api.saleslinechar OWNER TO admin;

--
-- TOC entry 9411 (class 0 OID 0)
-- Dependencies: 381
-- Name: VIEW saleslinechar; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW saleslinechar IS 'Sales Order Line Item Characteristic';


--
-- TOC entry 382 (class 1259 OID 146568130)
-- Dependencies: 8555 6
-- Name: saleslinecomment; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW saleslinecomment AS
    SELECT (cohead.cohead_number)::character varying AS order_number, CASE WHEN (coitem.coitem_subnumber = 0) THEN ((coitem.coitem_linenumber)::character varying)::text ELSE ((((coitem.coitem_linenumber)::character varying)::text || ('.'::character varying)::text) || ((coitem.coitem_subnumber)::character varying)::text) END 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.cohead, public.coitem, public.cmnttype, public.comment WHERE ((((cohead.cohead_id = coitem.coitem_cohead_id) AND (comment.comment_source = 'SI'::text)) AND (comment.comment_source_id = coitem.coitem_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id)) ORDER BY cohead.cohead_number, coitem.coitem_linenumber, comment.comment_date DESC;


ALTER TABLE api.saleslinecomment OWNER TO admin;

--
-- TOC entry 9413 (class 0 OID 0)
-- Dependencies: 382
-- Name: VIEW saleslinecomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW saleslinecomment IS 'Sales Order Line Item Comment';


--
-- TOC entry 383 (class 1259 OID 146568135)
-- Dependencies: 8556 6
-- Name: salesorder; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW salesorder AS
    SELECT (cohead.cohead_number)::character varying AS order_number, whsinfo.warehous_code AS site, cohead.cohead_orderdate AS order_date, cohead.cohead_packdate AS pack_date, saletype.saletype_code AS sale_type, salesrep.salesrep_number AS sales_rep, cohead.cohead_commission AS commission, COALESCE(taxzone.taxzone_code, 'None'::text) AS tax_zone, terms.terms_code AS terms, prj.prj_number AS project_number, custinfo.cust_number AS customer_number, bc.cntct_number AS billto_contact_number, cohead.cohead_billto_cntct_honorific AS billto_contact_name, cohead.cohead_billto_cntct_first_name AS billto_contact_first, cohead.cohead_billto_cntct_middle AS billto_contact_middle, cohead.cohead_billto_cntct_last_name AS billto_contact_last, cohead.cohead_billto_cntct_suffix AS billto_contact_suffix, cohead.cohead_billto_cntct_phone AS billto_contact_phone, cohead.cohead_billto_cntct_title AS billto_contact_title, cohead.cohead_billto_cntct_fax AS billto_contct_fax, cohead.cohead_billto_cntct_email AS billto_contact_email, cohead.cohead_billtoname AS billto_name, cohead.cohead_billtoaddress1 AS billto_address1, cohead.cohead_billtoaddress2 AS billto_address2, cohead.cohead_billtoaddress3 AS billto_address3, cohead.cohead_billtocity AS billto_city, cohead.cohead_billtostate AS billto_state, cohead.cohead_billtozipcode AS billto_postal_code, cohead.cohead_billtocountry AS billto_country, shiptoinfo.shipto_num AS shipto_number, sc.cntct_number AS shipto_contact_number, cohead.cohead_shipto_cntct_honorific AS shipto_contact_honorific, cohead.cohead_shipto_cntct_first_name AS shipto_contact_first, cohead.cohead_shipto_cntct_middle AS shipto_contact_middle, cohead.cohead_shipto_cntct_last_name AS shipto_contact_last, cohead.cohead_shipto_cntct_suffix AS shipto_contact_suffix, cohead.cohead_shipto_cntct_phone AS shipto_contact_phone, cohead.cohead_shipto_cntct_title AS shipto_contact_title, cohead.cohead_shipto_cntct_fax AS shipto_contact_fax, cohead.cohead_shipto_cntct_email AS shipto_contact_email, cohead.cohead_shiptoname AS shipto_name, cohead.cohead_shiptophone AS shipto_phone, cohead.cohead_shiptoaddress1 AS shipto_address1, cohead.cohead_shiptoaddress2 AS shipto_address2, cohead.cohead_shiptoaddress3 AS shipto_address3, cohead.cohead_shiptocity AS shipto_city, cohead.cohead_shiptostate AS shipto_state, cohead.cohead_shiptozipcode AS shipto_postal_code, cohead.cohead_shiptocountry AS shipto_country, shipzone.shipzone_name AS shipto_shipzone, cohead.cohead_custponumber AS cust_po_number, cohead.cohead_fob AS fob, cohead.cohead_shipvia AS ship_via, CASE WHEN (cohead.cohead_holdtype = 'N'::bpchar) THEN 'None'::text WHEN (cohead.cohead_holdtype = 'C'::bpchar) THEN 'Credit'::text WHEN (cohead.cohead_holdtype = 'S'::bpchar) THEN 'Shipping'::text WHEN (cohead.cohead_holdtype = 'P'::bpchar) THEN 'Packing'::text WHEN (cohead.cohead_holdtype = 'R'::bpchar) THEN 'Return'::text ELSE 'Error'::text END AS hold_type, shipchrg.shipchrg_name AS shipping_chgs, shipform.shipform_name AS shipping_form, cohead.cohead_shipcomplete AS ship_complete, curr_symbol.curr_abbr AS currency, cohead.cohead_misc_descrip AS misc_charge_description, CASE WHEN (cohead.cohead_misc_accnt_id IS NULL) THEN NULL::text ELSE public.formatglaccount(cohead.cohead_misc_accnt_id) END AS misc_account_number, cohead.cohead_misc AS misc_charge, cohead.cohead_freight AS freight, cohead.cohead_calcfreight AS calculate_freight, cohead.cohead_ordercomments AS order_notes, cohead.cohead_shipcomments AS shipping_notes, false AS add_to_packing_list_batch FROM (((((((((public.cohead LEFT JOIN public.cntct bc ON ((cohead.cohead_billto_cntct_id = bc.cntct_id))) LEFT JOIN public.cntct sc ON ((cohead.cohead_shipto_cntct_id = sc.cntct_id))) LEFT JOIN public.whsinfo ON ((cohead.cohead_warehous_id = whsinfo.warehous_id))) LEFT JOIN public.prj ON ((cohead.cohead_prj_id = prj.prj_id))) LEFT JOIN public.shiptoinfo ON ((cohead.cohead_shipto_id = shiptoinfo.shipto_id))) LEFT JOIN public.shipchrg ON ((cohead.cohead_shipchrg_id = shipchrg.shipchrg_id))) LEFT JOIN public.taxzone ON ((cohead.cohead_taxzone_id = taxzone.taxzone_id))) LEFT JOIN public.saletype ON ((cohead.cohead_saletype_id = saletype.saletype_id))) LEFT JOIN public.shipzone ON ((cohead.cohead_shipzone_id = shipzone.shipzone_id))), public.custinfo, public.shipform, public.salesrep, public.terms, public.curr_symbol WHERE (((((cohead.cohead_cust_id = custinfo.cust_id) AND (cohead.cohead_shipform_id = shipform.shipform_id)) AND (cohead.cohead_salesrep_id = salesrep.salesrep_id)) AND (cohead.cohead_terms_id = terms.terms_id)) AND (cohead.cohead_curr_id = curr_symbol.curr_id));


ALTER TABLE api.salesorder OWNER TO admin;

--
-- TOC entry 9415 (class 0 OID 0)
-- Dependencies: 383
-- Name: VIEW salesorder; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW salesorder IS 'Sales Order';


--
-- TOC entry 384 (class 1259 OID 146568140)
-- Dependencies: 8557 6
-- Name: salesordercomment; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW salesordercomment AS
    SELECT (cohead.cohead_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.cohead, public.cmnttype, public.comment WHERE (((comment.comment_source = 'S'::text) AND (comment.comment_source_id = cohead.cohead_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id));


ALTER TABLE api.salesordercomment OWNER TO admin;

--
-- TOC entry 9417 (class 0 OID 0)
-- Dependencies: 384
-- Name: VIEW salesordercomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW salesordercomment IS 'Sales Order Comment';


--
-- TOC entry 385 (class 1259 OID 146568145)
-- Dependencies: 8558 6
-- Name: salesrep; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW salesrep AS
    SELECT (salesrep.salesrep_number)::character varying AS number, salesrep.salesrep_active AS active, salesrep.salesrep_name AS name, (salesrep.salesrep_commission * (100)::numeric) AS commission_percent, emp.emp_number AS employee FROM (public.salesrep LEFT JOIN public.emp ON ((emp.emp_id = salesrep.salesrep_emp_id))) ORDER BY salesrep.salesrep_number;


ALTER TABLE api.salesrep OWNER TO admin;

--
-- TOC entry 9419 (class 0 OID 0)
-- Dependencies: 385
-- Name: VIEW salesrep; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW salesrep IS 'Sales Rep';



--
-- TOC entry 386 (class 1259 OID 146568150)
-- Dependencies: 6259 6260 8
-- Name: shipvia; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE shipvia (
    shipvia_id integer DEFAULT nextval(('shipvia_shipvia_id_seq'::text)::regclass) NOT NULL,
    shipvia_code text NOT NULL,
    shipvia_descrip text,
    CONSTRAINT shipvia_shipvia_code_check CHECK ((shipvia_code <> ''::text))
);


ALTER TABLE public.shipvia OWNER TO admin;

--
-- TOC entry 9421 (class 0 OID 0)
-- Dependencies: 386
-- Name: TABLE shipvia; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE shipvia IS 'Ship Via information';


--
-- TOC entry 387 (class 1259 OID 146568158)
-- Dependencies: 6262 8
-- Name: sitetype; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE sitetype (
    sitetype_id integer NOT NULL,
    sitetype_name text NOT NULL,
    sitetype_descrip text,
    CONSTRAINT sitetype_sitetype_name_check CHECK ((sitetype_name <> ''::text))
);


ALTER TABLE public.sitetype OWNER TO admin;

--
-- TOC entry 9423 (class 0 OID 0)
-- Dependencies: 387
-- Name: TABLE sitetype; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE sitetype IS 'This table is the different types of sites.';



--
-- TOC entry 388 (class 1259 OID 146568165)
-- Dependencies: 8559 6
-- Name: site; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW site AS
    SELECT (whsinfo.warehous_code)::character varying AS code, st.sitetype_name AS type, whsinfo.warehous_active AS active, whsinfo.warehous_descrip AS description, m.addr_number AS address_number, m.addr_line1 AS address1, m.addr_line2 AS address2, m.addr_line3 AS address3, m.addr_city AS city, m.addr_state AS state, m.addr_postalcode AS postal_code, m.addr_country AS country, ''::text AS address_change, c.cntct_number AS contact_number, c.cntct_honorific AS honorific, c.cntct_first_name AS first, c.cntct_middle AS middle, c.cntct_last_name AS last, c.cntct_suffix AS suffix, c.cntct_title AS job_title, c.cntct_phone AS phone, c.cntct_fax AS fax, c.cntct_email AS email, ''::text AS contact_change, public.formatglaccount(a.accnt_id) AS post_unassigned_transactions_to, a.accnt_descrip AS post_unassigned_transactions_to_description, whsinfo.warehous_transit AS transit_type, CASE WHEN whsinfo.warehous_transit THEN false ELSE true END AS inventory_type, CASE WHEN whsinfo.warehous_transit THEN ''::text ELSE whsinfo.warehous_bol_prefix END AS next_bill_of_lading_prefix, CASE WHEN whsinfo.warehous_transit THEN 0 ELSE whsinfo.warehous_bol_number END AS next_bill_of_lading_number, CASE WHEN whsinfo.warehous_transit THEN false ELSE whsinfo.warehous_shipping END AS shipping_site, CASE WHEN whsinfo.warehous_transit THEN ''::text ELSE whsinfo.warehous_counttag_prefix END AS next_count_tag_prefix, CASE WHEN whsinfo.warehous_transit THEN 0 ELSE whsinfo.warehous_counttag_number END AS next_count_tag_number, CASE WHEN whsinfo.warehous_transit THEN false ELSE whsinfo.warehous_useslips END AS force_the_use_of_count_slips, CASE WHEN whsinfo.warehous_transit THEN false ELSE whsinfo.warehous_usezones END AS force_the_use_of_zones, CASE WHEN whsinfo.warehous_transit THEN 0 ELSE whsinfo.warehous_sequence END AS scheduling_sequence, CASE WHEN whsinfo.warehous_transit THEN (0)::numeric ELSE (whsinfo.warehous_shipping_commission * 100.0) END AS shipping_commission, CASE WHEN whsinfo.warehous_transit THEN ''::text ELSE t.taxzone_code END AS tax_zone, CASE WHEN whsinfo.warehous_transit THEN ''::text ELSE whsinfo.warehous_fob END AS default_fob, CASE WHEN whsinfo.warehous_transit THEN s.shipvia_code ELSE ''::text END AS default_ship_via, CASE WHEN whsinfo.warehous_transit THEN f.shipform_name ELSE ''::text END AS default_shipping_form, CASE WHEN whsinfo.warehous_transit THEN cc.costcat_code ELSE ''::text END AS default_cost_category, CASE WHEN whsinfo.warehous_transit THEN whsinfo.warehous_shipcomments ELSE ''::text END AS shipping_comments, CASE WHEN whsinfo.warehous_transit THEN false ELSE whsinfo.warehous_enforcearbl END AS enforce_arbl_naming_convention, CASE WHEN whsinfo.warehous_transit THEN 0 WHEN whsinfo.warehous_enforcearbl THEN whsinfo.warehous_aislesize ELSE 0 END AS aisle_size, CASE WHEN whsinfo.warehous_transit THEN false WHEN (whsinfo.warehous_enforcearbl AND whsinfo.warehous_aislealpha) THEN true ELSE false END AS aisle_allow_alpha_characters, CASE WHEN whsinfo.warehous_transit THEN 0 WHEN whsinfo.warehous_enforcearbl THEN whsinfo.warehous_racksize ELSE 0 END AS rack_size, CASE WHEN whsinfo.warehous_transit THEN false WHEN (whsinfo.warehous_enforcearbl AND whsinfo.warehous_rackalpha) THEN true ELSE false END AS rack_allow_alpha_characters, CASE WHEN whsinfo.warehous_transit THEN 0 WHEN whsinfo.warehous_enforcearbl THEN whsinfo.warehous_binsize ELSE 0 END AS bin_size, CASE WHEN whsinfo.warehous_transit THEN false WHEN (whsinfo.warehous_enforcearbl AND whsinfo.warehous_binalpha) THEN true ELSE false END AS bin_allow_alpha_characters, CASE WHEN whsinfo.warehous_transit THEN 0 WHEN whsinfo.warehous_enforcearbl THEN whsinfo.warehous_locationsize ELSE 0 END AS location_size, CASE WHEN whsinfo.warehous_transit THEN false WHEN (whsinfo.warehous_enforcearbl AND whsinfo.warehous_locationalpha) THEN true ELSE false END AS location_allow_alpha_characters FROM ((((((((public.whsinfo LEFT JOIN public.addr m ON ((whsinfo.warehous_addr_id = m.addr_id))) LEFT JOIN public.cntct c ON ((whsinfo.warehous_cntct_id = c.cntct_id))) LEFT JOIN public.accnt a ON ((whsinfo.warehous_default_accnt_id = a.accnt_id))) LEFT JOIN public.taxzone t ON ((whsinfo.warehous_taxzone_id = t.taxzone_id))) LEFT JOIN public.shipvia s ON ((whsinfo.warehous_shipvia_id = s.shipvia_id))) LEFT JOIN public.shipform f ON ((whsinfo.warehous_shipform_id = f.shipform_id))) LEFT JOIN public.costcat cc ON ((whsinfo.warehous_costcat_id = cc.costcat_id))) LEFT JOIN public.sitetype st ON ((whsinfo.warehous_sitetype_id = st.sitetype_id))) ORDER BY whsinfo.warehous_code;


ALTER TABLE api.site OWNER TO admin;

--
-- TOC entry 9425 (class 0 OID 0)
-- Dependencies: 388
-- Name: VIEW site; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW site IS 'Site';


--
-- TOC entry 389 (class 1259 OID 146568170)
-- Dependencies: 8560 6
-- Name: sitezone; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW sitezone AS
    SELECT (whsinfo.warehous_code)::character varying AS site, (whsezone.whsezone_name)::character varying AS name, whsezone.whsezone_descrip AS description FROM (public.whsezone LEFT JOIN public.whsinfo ON ((whsinfo.warehous_id = whsezone.whsezone_warehous_id)));


ALTER TABLE api.sitezone OWNER TO admin;

--
-- TOC entry 9427 (class 0 OID 0)
-- Dependencies: 389
-- Name: VIEW sitezone; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW sitezone IS 'Site Zone';


--
-- TOC entry 390 (class 1259 OID 146568175)
-- Dependencies: 8561 6
-- Name: task; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW task AS
    SELECT prj.prj_number AS project_number, prjtask.prjtask_number AS number, CASE WHEN (prjtask.prjtask_status = 'P'::bpchar) THEN 'Concept'::text WHEN (prjtask.prjtask_status = 'O'::bpchar) THEN 'In-Process'::text WHEN (prjtask.prjtask_status = 'C'::bpchar) THEN 'Closed'::text ELSE 'Error'::text END AS status, prjtask.prjtask_name AS name, prjtask.prjtask_descrip AS description, prjtask.prjtask_owner_username AS owner, prjtask.prjtask_username AS assigned_to, prjtask.prjtask_hours_budget AS hours_budgeted, prjtask.prjtask_hours_actual AS hours_actual, prjtask.prjtask_exp_budget AS expenses_budgeted, prjtask.prjtask_exp_actual AS expenses_actual, prjtask.prjtask_due_date AS due, prjtask.prjtask_assigned_date AS assigned, prjtask.prjtask_start_date AS started, prjtask.prjtask_completed_date AS completed FROM (public.prjtask JOIN public.prj ON ((prj.prj_id = prjtask.prjtask_prj_id)));


ALTER TABLE api.task OWNER TO admin;

--
-- TOC entry 9429 (class 0 OID 0)
-- Dependencies: 390
-- Name: VIEW task; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW task IS 'Task';


--
-- TOC entry 391 (class 1259 OID 146568180)
-- Dependencies: 8562 6
-- Name: taskcomment; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW taskcomment AS
    SELECT (prj.prj_number)::character varying AS project_number, (prjtask.prjtask_number)::character varying AS task_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.prjtask, public.cmnttype, public.comment WHERE ((((comment.comment_source = 'TA'::text) AND (prj.prj_id = prjtask.prjtask_prj_id)) AND (comment.comment_source_id = prjtask.prjtask_id)) AND (comment.comment_cmnttype_id = cmnttype.cmnttype_id));


ALTER TABLE api.taskcomment OWNER TO admin;

--
-- TOC entry 9431 (class 0 OID 0)
-- Dependencies: 391
-- Name: VIEW taskcomment; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW taskcomment IS 'Task Comment';


--
-- TOC entry 392 (class 1259 OID 146568184)
-- Dependencies: 8563 6
-- Name: todo; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW todo AS
    SELECT todoitem.todoitem_id AS task_number, todoitem.todoitem_owner_username AS owner, todoitem.todoitem_username AS assigned_to, todoitem.todoitem_name AS task_name, incdtpriority.incdtpriority_name AS priority, incdt.incdt_number AS incident, ophead.ophead_name AS opportunity, crmacct.crmacct_number AS account, public.formatdate(todoitem.todoitem_due_date) AS date_due, public.formatdate(todoitem.todoitem_assigned_date) AS date_assigned, public.formatdate(todoitem.todoitem_start_date) AS date_started, public.formatdate(todoitem.todoitem_completed_date) AS date_completed, CASE WHEN (todoitem.todoitem_status = 'P'::bpchar) THEN 'Pending Input'::text WHEN (todoitem.todoitem_status = 'D'::bpchar) THEN 'Deferred'::text ELSE 'Neither'::text END AS status, todoitem.todoitem_active AS active, todoitem.todoitem_description AS description, todoitem.todoitem_notes AS notes FROM ((((public.todoitem LEFT JOIN public.incdt ON ((incdt.incdt_id = todoitem.todoitem_incdt_id))) LEFT JOIN public.ophead ON ((ophead.ophead_id = todoitem.todoitem_ophead_id))) LEFT JOIN public.crmacct ON ((crmacct.crmacct_id = todoitem.todoitem_crmacct_id))) LEFT JOIN public.incdtpriority ON ((incdtpriority.incdtpriority_id = todoitem.todoitem_priority_id)));


ALTER TABLE api.todo OWNER TO admin;

--
-- TOC entry 9433 (class 0 OID 0)
-- Dependencies: 392
