Dumped on 2007-03-22

# Index of database - ledgersmb

- public

- acc_trans
- ap
- ar
- assembly
- audittrail
- business
- chart
- custom_field_catalog
- custom_table_catalog
- customer
- customertax
- defaults
- department
- dpt_trans
- employee
- exchangerate
- gifi
- gl
- inventory
- invoice
- jcitems
- language
- makemodel
- oe
- orderitems
- parts
- partscustomer
- partsgroup
- partstax
- partsvendor
- pricegroup
- project
- recurring
- recurringemail
- recurringprint
- shipto
- sic
- status
- tax
- taxcategory
- taxmodule
- transactions
- translation
- vendor
- vendortax
- warehouse
- yearend
- add_custom_field( character varying, character varying, character varying )
- avgcost( integer )
- check_department( )
- check_inventory( )
- del_customer( )
- del_department( )
- del_exchangerate( )
- del_recurring( )
- del_vendor( )
- del_yearend( )
- drop_custom_field( character varying, character varying )
- lastcost( integer )
- trigger_parts_short( )
- update_user( integer, text )

## Table: acc_trans

acc_trans Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| trans_id | integer |
chart.id | chart_id | integer | NOT NULL
| amount | numeric |
| transdate | date | DEFAULT ('now'::text)::date
| source | text |
| cleared | boolean | DEFAULT false
| fx_transaction | boolean | DEFAULT false
| project_id | integer |
| memo | text |
| invoice_id | integer |
| entry_id | serial | PRIMARY KEY

acc_trans_chart_id_key chart_id acc_trans_source_key lower(source) acc_trans_trans_id_key trans_id acc_trans_transdate_key transdate

Index - Schema public

## Table: ap

ap Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| invnumber | text |
| transdate | date | DEFAULT ('now'::text)::date
| vendor_id | integer |
| taxincluded | boolean | DEFAULT false
| amount | numeric |
| netamount | numeric |
| paid | numeric |
| datepaid | date |
| duedate | date |
| invoice | boolean | DEFAULT false
| ordnumber | text |
| curr | character(3) |
| notes | text |
| employee_id | integer |
| till | character varying(20) |
| quonumber | text |
| intnotes | text |
| department_id | integer |
| shipvia | text |
| language_code | character varying(6) |
| ponumber | text |
| shippingpoint | text |
| terms | smallint |

ap_employee_id_key employee_id ap_id_key id ap_invnumber_key invnumber ap_ordnumber_key ordnumber ap_quonumber_key quonumber ap_transdate_key transdate ap_vendor_id_key vendor_id

Index - Schema public

## Table: ar

ar Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| invnumber | text |
| transdate | date | DEFAULT ('now'::text)::date
| customer_id | integer |
| taxincluded | boolean |
| amount | numeric |
| netamount | numeric |
| paid | numeric |
| datepaid | date |
| duedate | date |
| invoice | boolean | DEFAULT false
| shippingpoint | text |
| terms | smallint |
| notes | text |
| curr | character(3) |
| ordnumber | text |
| employee_id | integer |
| till | character varying(20) |
| quonumber | text |
| intnotes | text |
| department_id | integer |
| shipvia | text |
| language_code | character varying(6) |
| ponumber | text |

ar_customer_id_key customer_id ar_employee_id_key employee_id ar_id_key id ar_invnumber_key invnumber ar_ordnumber_key ordnumber ar_quonumber_key quonumber ar_transdate_key transdate

Index - Schema public

## Table: assembly

assembly Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY
| parts_id | integer | PRIMARY KEY
| qty | numeric |
| bom | boolean |
| adj | boolean |

assembly_id_key id

Index - Schema public

## Table: audittrail

audittrail Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| trans_id | integer |
| tablename | text |
| reference | text |
| formname | text |
| action | text |
| transdate | timestamp without time zone | DEFAULT now()
| employee_id | integer |
| entry_id | bigserial | PRIMARY KEY

audittrail_trans_id_key trans_id

Index - Schema public

## Table: business

business Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| description | text |
| discount | numeric |

Index - Schema public

## Table: chart

chart Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| accno | text | NOT NULL
| description | text |
| charttype | character(1) | DEFAULT 'A'::bpchar
| category | character(1) |
| link | text |
| gifi_accno | text |
| contra | boolean | DEFAULT false

Tables referencing this one via Foreign Key Constraints:

- acc_trans

- partstax

- tax

chart_category_key category chart_gifi_accno_key gifi_accno chart_id_key id chart_link_key link

Index - Schema public

## Table: custom_field_catalog

custom_field_catalog Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| field_id | serial | PRIMARY KEY
custom_table_catalog.table_id | table_id | integer |
| field_name | text |

Index - Schema public

## Table: custom_table_catalog

custom_table_catalog Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| table_id | serial | PRIMARY KEY
| extends | text |
| table_name | text |

Tables referencing this one via Foreign Key Constraints:

- custom_field_catalog

Index - Schema public

## Table: customer

customer Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| name | character varying(64) |
| address1 | character varying(32) |
| address2 | character varying(32) |
| city | character varying(32) |
| state | character varying(32) |
| zipcode | character varying(10) |
| country | character varying(32) |
| contact | character varying(64) |
| phone | character varying(20) |
| fax | character varying(20) |
| email | text |
| notes | text |
| discount | numeric |
| taxincluded | boolean | DEFAULT false
| creditlimit | numeric |
| terms | smallint |
| customernumber | character varying(32) |
| cc | text |
| bcc | text |
| business_id | integer |
| taxnumber | character varying(32) |
| sic_code | character varying(6) |
| iban | character varying(34) |
| bic | character varying(11) |
| employee_id | integer |
| language_code | character varying(6) |
| pricegroup_id | integer |
| curr | character(3) |
| startdate | date |
| enddate | date |

customer_contact_key lower((contact)::text) customer_customernumber_key customernumber customer_id_key id customer_name_key lower((name)::text)

Index - Schema public

## Table: customertax

customertax Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| customer_id | integer | PRIMARY KEY
| chart_id | integer | PRIMARY KEY

customer_customer_id_key customer_id

Index - Schema public

## Table: defaults

defaults Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| setting_key | text | PRIMARY KEY
| value | text |

Index - Schema public

## Table: department

department Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| description | text |
| role | character(1) | DEFAULT 'P'::bpchar

department_id_key id

Index - Schema public

## Table: dpt_trans

dpt_trans Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| trans_id | integer | PRIMARY KEY
| department_id | integer |

Index - Schema public

## Table: employee

employee Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| login | text |
| name | character varying(64) |
| address1 | character varying(32) |
| address2 | character varying(32) |
| city | character varying(32) |
| state | character varying(32) |
| zipcode | character varying(10) |
| country | character varying(32) |
| workphone | character varying(20) |
| homephone | character varying(20) |
| startdate | date | DEFAULT ('now'::text)::date
| enddate | date |
| notes | text |
| role | character varying(20) |
| sales | boolean | DEFAULT false
| email | text |
| ssn | character varying(20) |
| iban | character varying(34) |
| bic | character varying(11) |
| managerid | integer |
| employeenumber | character varying(32) |
| dob | date |

employee_id_key id employee_name_key lower((name)::text)

Index - Schema public

## Table: exchangerate

exchangerate Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| curr | character(3) | PRIMARY KEY
| transdate | date | PRIMARY KEY
| buy | numeric |
| sell | numeric |

exchangerate_ct_key curr, transdate

Index - Schema public

## Table: gifi

gifi Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| accno | text | PRIMARY KEY
| description | text |

Index - Schema public

## Table: gl

gl Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| reference | text |
| description | text |
| transdate | date | DEFAULT ('now'::text)::date
| employee_id | integer |
| notes | text |
| department_id | integer |

gl_description_key lower(description) gl_employee_id_key employee_id gl_id_key id gl_reference_key reference gl_transdate_key transdate

Index - Schema public

## Table: inventory

inventory Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| warehouse_id | integer |
| parts_id | integer |
| trans_id | integer |
| orderitems_id | integer |
| qty | numeric |
| shippingdate | date |
| employee_id | integer |
| entry_id | serial | PRIMARY KEY

Index - Schema public

## Table: invoice

invoice Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('invoiceid'::regclass)
| trans_id | integer |
| parts_id | integer |
| description | text |
| qty | integer |
| allocated | integer |
| sellprice | numeric |
| fxsellprice | numeric |
| discount | real |
| assemblyitem | boolean | DEFAULT false
| unit | character varying(5) |
| project_id | integer |
| deliverydate | date |
| serialnumber | text |
| notes | text |

invoice_id_key id invoice_trans_id_key trans_id

Index - Schema public

## Table: jcitems

jcitems Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('jcitemsid'::regclass)
| project_id | integer |
| parts_id | integer |
| description | text |
| qty | numeric |
| allocated | numeric |
| sellprice | numeric |
| fxsellprice | numeric |
| serialnumber | text |
| checkedin | timestamp with time zone |
| checkedout | timestamp with time zone |
| employee_id | integer |
| notes | text |

jcitems_id_key id

Index - Schema public

## Table: language

language Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| code | character varying(6) | PRIMARY KEY
| description | text |

Index - Schema public

## Table: makemodel

makemodel Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| parts_id | integer | PRIMARY KEY
| make | text |
| model | text |

makemodel_make_key lower(make) makemodel_model_key lower(model) makemodel_parts_id_key parts_id

Index - Schema public

## Table: oe

oe Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| ordnumber | text |
| transdate | date | DEFAULT ('now'::text)::date
| vendor_id | integer |
| customer_id | integer |
| amount | numeric |
| netamount | numeric |
| reqdate | date |
| taxincluded | boolean |
| shippingpoint | text |
| notes | text |
| curr | character(3) |
| employee_id | integer |
| closed | boolean | DEFAULT false
| quotation | boolean | DEFAULT false
| quonumber | text |
| intnotes | text |
| department_id | integer |
| shipvia | text |
| language_code | character varying(6) |
| ponumber | text |
| terms | smallint |

oe_employee_id_key employee_id oe_id_key id oe_ordnumber_key ordnumber oe_transdate_key transdate

Index - Schema public

## Table: orderitems

orderitems Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('orderitemsid'::regclass)
| trans_id | integer |
| parts_id | integer |
| description | text |
| qty | numeric |
| sellprice | numeric |
| discount | numeric |
| unit | character varying(5) |
| project_id | integer |
| reqdate | date |
| ship | numeric |
| serialnumber | text |
| notes | text |

orderitems_id_key id orderitems_trans_id_key trans_id

Index - Schema public

## Table: parts

parts Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| partnumber | text |
| description | text |
| unit | character varying(5) |
| listprice | numeric |
| sellprice | numeric |
| lastcost | numeric |
| priceupdate | date | DEFAULT ('now'::text)::date
| weight | numeric |
| onhand | numeric |
| notes | text |
| makemodel | boolean | DEFAULT false
| assembly | boolean | DEFAULT false
| alternate | boolean | DEFAULT false
| rop | real |
| inventory_accno_id | integer |
| income_accno_id | integer |
| expense_accno_id | integer |
| bin | text |
| obsolete | boolean | DEFAULT false
| bom | boolean | DEFAULT false
| image | text |
| drawing | text |
| microfiche | text |
| partsgroup_id | integer |
| project_id | integer |
| avgcost | numeric |

Tables referencing this one via Foreign Key Constraints:

- partstax

parts_description_key lower(description) parts_id_key id parts_partnumber_key lower(partnumber)

Index - Schema public

## Table: partscustomer

partscustomer Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| parts_id | integer |
| customer_id | integer |
| pricegroup_id | integer |
| pricebreak | numeric |
| sellprice | numeric |
| validfrom | date |
| validto | date |
| curr | character(3) |
| entry_id | serial | PRIMARY KEY

Index - Schema public

## Table: partsgroup

partsgroup Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| partsgroup | text |

partsgroup_id_key id

Index - Schema public

## Table: partstax

partstax Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
parts.id | parts_id | integer | PRIMARY KEY
chart.id | chart_id | integer | PRIMARY KEY
taxcategory.taxcategory_id | taxcategory_id | integer |

partstax_parts_id_key parts_id

Index - Schema public

## Table: partsvendor

partsvendor Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| vendor_id | integer |
| parts_id | integer |
| partnumber | text |
| leadtime | smallint |
| lastcost | numeric |
| curr | character(3) |
| entry_id | serial | PRIMARY KEY

partsvendor_parts_id_key parts_id partsvendor_vendor_id_key vendor_id

Index - Schema public

## Table: pricegroup

pricegroup Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| pricegroup | text |

pricegroup_id_key id pricegroup_pricegroup_key pricegroup

Index - Schema public

## Table: project

project Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| projectnumber | text |
| description | text |
| startdate | date |
| enddate | date |
| parts_id | integer |
| production | numeric |
| completed | numeric |
| customer_id | integer |

project_id_key id

Index - Schema public

## Table: recurring

recurring Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY
| reference | text |
| startdate | date |
| nextdate | date |
| enddate | date |
| repeat | smallint |
| unit | character varying(6) |
| howmany | integer |
| payment | boolean | DEFAULT false

Index - Schema public

## Table: recurringemail

recurringemail Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY
| formname | text |
| format | text |
| message | text |

Index - Schema public

## Table: recurringprint

recurringprint Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY
| formname | text |
| format | text |
| printer | text |

Index - Schema public

## Table: shipto

shipto Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| trans_id | integer |
| shiptoname | character varying(64) |
| shiptoaddress1 | character varying(32) |
| shiptoaddress2 | character varying(32) |
| shiptocity | character varying(32) |
| shiptostate | character varying(32) |
| shiptozipcode | character varying(10) |
| shiptocountry | character varying(32) |
| shiptocontact | character varying(64) |
| shiptophone | character varying(20) |
| shiptofax | character varying(20) |
| shiptoemail | text |
| entry_id | serial | PRIMARY KEY

shipto_trans_id_key trans_id

Index - Schema public

## Table: sic

sic Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| code | character varying(6) | PRIMARY KEY
| sictype | character(1) |
| description | text |

Index - Schema public

## Table: status

status Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| trans_id | integer | PRIMARY KEY
| formname | text |
| printed | boolean | DEFAULT false
| emailed | boolean | DEFAULT false
| spoolfile | text |

status_trans_id_key trans_id

Index - Schema public

## Table: tax

tax Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
chart.id | chart_id | integer | PRIMARY KEY
| rate | numeric |
| taxnumber | text |
| validto | date |
| pass | integer | NOT NULL
taxmodule.taxmodule_id | taxmodule_id | integer | NOT NULL DEFAULT 1

Index - Schema public

## Table: taxcategory

taxcategory Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| taxcategory_id | serial | PRIMARY KEY
| taxcategoryname | text | NOT NULL
taxmodule.taxmodule_id | taxmodule_id | integer | NOT NULL

Tables referencing this one via Foreign Key Constraints:

- partstax

Index - Schema public

## Table: taxmodule

taxmodule Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| taxmodule_id | serial | PRIMARY KEY
| taxmodulename | text | NOT NULL

Tables referencing this one via Foreign Key Constraints:

- tax

- taxcategory

Index - Schema public

## Table: transactions

transactions Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY
| table_name | text |

Index - Schema public

## Table: translation

translation Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| trans_id | integer | PRIMARY KEY
| language_code | character varying(6) | PRIMARY KEY
| description | text |

translation_trans_id_key trans_id

Index - Schema public

## Table: vendor

vendor Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| name | character varying(64) |
| address1 | character varying(32) |
| address2 | character varying(32) |
| city | character varying(32) |
| state | character varying(32) |
| zipcode | character varying(10) |
| country | character varying(32) |
| contact | character varying(64) |
| phone | character varying(20) |
| fax | character varying(20) |
| email | text |
| notes | text |
| terms | smallint |
| taxincluded | boolean | DEFAULT false
| vendornumber | character varying(32) |
| cc | text |
| bcc | text |
| gifi_accno | character varying(30) |
| business_id | integer |
| taxnumber | character varying(32) |
| sic_code | character varying(6) |
| discount | numeric |
| creditlimit | numeric |
| iban | character varying(34) |
| bic | character varying(11) |
| employee_id | integer |
| language_code | character varying(6) |
| pricegroup_id | integer |
| curr | character(3) |
| startdate | date |
| enddate | date |

vendor_contact_key lower((contact)::text) vendor_id_key id vendor_name_key lower((name)::text) vendor_vendornumber_key vendornumber

Index - Schema public

## Table: vendortax

vendortax Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| vendor_id | integer | PRIMARY KEY
| chart_id | integer | PRIMARY KEY

vendortax_vendor_id_key vendor_id

Index - Schema public

## Table: warehouse

warehouse Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| id | integer | PRIMARY KEY DEFAULT nextval('id'::regclass)
| description | text |

Index - Schema public

## Table: yearend

yearend Structure
F-Key | Name | Type | Description
--- | --- | --- | ---
| trans_id | integer | PRIMARY KEY
| transdate | date |

Index - Schema public

## Function: add_custom_field( character varying, character varying, character varying )

### Returns: boolean

### Language: PLPGSQL

    DECLARE
    table_name ALIAS FOR $1;
    new_field_name ALIAS FOR $2;
    field_datatype ALIAS FOR $3;

    BEGIN
    	EXECUTE 'SELECT TABLE_ID FROM custom_table_catalog
    		WHERE extends = ''' || table_name || ''' ';
    	IF NOT FOUND THEN
    		BEGIN
    			INSERT INTO custom_table_catalog (extends)
    				VALUES (table_name);
    			EXECUTE 'CREATE TABLE custom_'||table_name ||
    				' (row_id INT PRIMARY KEY)';
    		EXCEPTION WHEN duplicate_table THEN
    			-- do nothing
    		END;
    	END IF;
    	EXECUTE 'INSERT INTO custom_field_catalog (field_name, table_id)
    	VALUES ( ''' || new_field_name ||''', (SELECT table_id FROM custom_table_catalog
    		WHERE extends = '''|| table_name || '''))';
    	EXECUTE 'ALTER TABLE custom_'||table_name || ' ADD COLUMN '
    		|| new_field_name || ' ' || field_datatype;
    	RETURN TRUE;
    END;

## Function: avgcost( integer )

### Returns: double precision

### Language: PLPGSQL

    DECLARE

    v_cost float;
    v_qty float;
    v_parts_id alias for $1;

    BEGIN

      SELECT INTO v_cost, v_qty SUM(i.sellprice * i.qty), SUM(i.qty)
      FROM invoice i
      JOIN ap a ON (a.id = i.trans_id)
      WHERE i.parts_id = v_parts_id;

      IF v_cost IS NULL THEN
        v_cost := 0;
      END IF;

      IF NOT v_qty IS NULL THEN
        IF v_qty = 0 THEN
          v_cost := 0;
        ELSE
          v_cost := v_cost/v_qty;
        END IF;
      END IF;

    RETURN v_cost;
    END;

## Function: check_department( )

### Returns: "trigger"

### Language: PLPGSQL

    declare
      dpt_id int;

    begin

      if new.department_id = 0 then
        delete from dpt_trans where trans_id = new.id;
        return NULL;
      end if;

      select into dpt_id trans_id from dpt_trans where trans_id = new.id;

      if dpt_id > 0 then
        update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
      else
        insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
      end if;
    return NULL;

    end;

## Function: check_inventory( )

### Returns: "trigger"

### Language: PLPGSQL

    declare
      itemid int;
      row_data inventory%rowtype;

    begin

      if not old.quotation then
        for row_data in select * from inventory where trans_id = old.id loop
          select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;

          if itemid is null then
    	delete from inventory where trans_id = old.id and orderitems_id = row_data.orderitems_id;
          end if;
        end loop;
      end if;
    return old;
    end;

## Function: del_customer( )

### Returns: "trigger"

### Language: PLPGSQL

    begin
      delete from shipto where trans_id = old.id;
      delete from customertax where customer_id = old.id;
      delete from partscustomer where customer_id = old.id;
      return NULL;
    end;

## Function: del_department( )

### Returns: "trigger"

### Language: PLPGSQL

    begin
      delete from dpt_trans where trans_id = old.id;
      return NULL;
    end;

## Function: del_exchangerate( )

### Returns: "trigger"

### Language: PLPGSQL

    declare
      t_transdate date;
      t_curr char(3);
      t_id int;
      d_curr text;

    begin

      select into d_curr substr(value,1,3) from defaults where setting_key = 'curr';

      if TG_RELNAME = 'ar' then
        select into t_curr, t_transdate curr, transdate from ar where id = old.id;
      end if;
      if TG_RELNAME = 'ap' then
        select into t_curr, t_transdate curr, transdate from ap where id = old.id;
      end if;
      if TG_RELNAME = 'oe' then
        select into t_curr, t_transdate curr, transdate from oe where id = old.id;
      end if;

      if d_curr != t_curr then

        select into t_id a.id from acc_trans ac
        join ar a on (a.id = ac.trans_id)
        where a.curr = t_curr
        and ac.transdate = t_transdate

        except select a.id from ar a where a.id = old.id

        union

        select a.id from acc_trans ac
        join ap a on (a.id = ac.trans_id)
        where a.curr = t_curr
        and ac.transdate = t_transdate

        except select a.id from ap a where a.id = old.id

        union

        select o.id from oe o
        where o.curr = t_curr
        and o.transdate = t_transdate

        except select o.id from oe o where o.id = old.id;

        if not found then
          delete from exchangerate where curr = t_curr and transdate = t_transdate;
        end if;
      end if;
    return old;

    end;

## Function: del_recurring( )

### Returns: "trigger"

### Language: PLPGSQL

    BEGIN
      DELETE FROM recurring WHERE id = old.id;
      DELETE FROM recurringemail WHERE id = old.id;
      DELETE FROM recurringprint WHERE id = old.id;
      RETURN NULL;
    END;

## Function: del_vendor( )

### Returns: "trigger"

### Language: PLPGSQL

    begin
      delete from shipto where trans_id = old.id;
      delete from vendortax where vendor_id = old.id;
      delete from partsvendor where vendor_id = old.id;
      return NULL;
    end;

## Function: del_yearend( )

### Returns: "trigger"

### Language: PLPGSQL

    begin
      delete from yearend where trans_id = old.id;
      return NULL;
    end;

## Function: drop_custom_field( character varying, character varying )

### Returns: boolean

### Language: PLPGSQL

    DECLARE
    table_name ALIAS FOR $1;
    custom_field_name ALIAS FOR $2;
    BEGIN
    	DELETE FROM custom_field_catalog
    	WHERE field_name = custom_field_name AND
    		table_id = (SELECT table_id FROM custom_table_catalog
    			WHERE extends = table_name);
    	EXECUTE 'ALTER TABLE custom_' || table_name ||
    		' DROP COLUMN ' || custom_field_name;
    	RETURN TRUE;
    END;

## Function: lastcost( integer )

### Returns: double precision

### Language: PLPGSQL

    DECLARE

    v_cost float;
    v_parts_id alias for $1;

    BEGIN

      SELECT INTO v_cost sellprice FROM invoice i
      JOIN ap a ON (a.id = i.trans_id)
      WHERE i.parts_id = v_parts_id
      ORDER BY a.transdate desc, a.id desc
      LIMIT 1;

      IF v_cost IS NULL THEN
        v_cost := 0;
      END IF;

    RETURN v_cost;
    END;

## Function: trigger_parts_short( )

### Returns: "trigger"

### Language: PLPGSQL

    BEGIN
      IF NEW.onhand >= NEW.rop THEN
        NOTIFY parts_short;
      END IF;
      RETURN NEW;
    END;

## Function: update_user( integer, text )

### Returns: integer

### Language: SQL

Takes int4 which is users.id and text which is username. Will update username based on id. Username is unique

       UPDATE users SET username = '$2' WHERE id = $1;
       SELECT 1;

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict