Dumped on 2007-06-13

Index of database - lsmb13-new


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 serial PRIMARY KEY
invnumber text
transdate date DEFAULT ('now'::text)::date
entity.id entity_id integer

Used to be customer_id, but customer is now metadata. You need to push to entity
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
person.id person_id integer NOT NULL
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_id_key id ap_invnumber_key invnumber ap_ordnumber_key ordnumber ap_quonumber_key quonumber ap_transdate_key transdate

Index - Schema public


Table: ar

ar Structure
F-Key Name Type Description
id serial PRIMARY KEY
invnumber text
transdate date DEFAULT ('now'::text)::date
entity.id entity_id integer

Used to be customer_id, but customer is now metadata. You need to push to entity
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
person.id person_id integer NOT NULL
till character varying(20)
quonumber text
intnotes text
department_id integer
shipvia text
language_code character varying(6)
ponumber text
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()
person.id person_id integer NOT NULL
entry_id bigserial PRIMARY KEY
audittrail_trans_id_key trans_id

Index - Schema public


Table: business

business Structure
F-Key Name Type Description
id serial PRIMARY KEY
description text
discount numeric

Index - Schema public


Table: chart

chart Structure
F-Key Name Type Description
id serial PRIMARY KEY
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:

chart_category_key category chart_gifi_accno_key gifi_accno chart_id_key id chart_link_key link

Index - Schema public


Table: company

company Structure
F-Key Name Type Description
id serial UNIQUE NOT NULL
entity.id entity_id integer NOT NULL
legal_name text PRIMARY KEY
entity_class.id entity_class_id integer NOT NULL
location.id primary_location_id integer PRIMARY KEY

This is the location that should show up by default for any forms
tax_id text

In the US this would be a EIN.

 

company Constraints
Name Constraint
company_legal_name_check CHECK ((legal_name ~ '[[:alnum:]_]'::text))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public


Table: company_to_contact

To keep track of the relationship between multiple contact methods and a single company

company_to_contact Structure
F-Key Name Type Description
company.id company_id integer PRIMARY KEY
contact_class.id contact_class_id integer PRIMARY KEY
contact text PRIMARY KEY

 

company_to_contact Constraints
Name Constraint
company_to_contact_contact_check CHECK ((contact ~ '[[:alnum:]_]'::text))

Index - Schema public


Table: company_to_entity

company_to_entity Structure
F-Key Name Type Description
company.id company_id integer PRIMARY KEY
entity.id entity_id integer PRIMARY KEY
related_how text
created date NOT NULL DEFAULT ('now'::text)::date

 

company_to_entity Constraints
Name Constraint
company_to_entity_check CHECK ((entity_id <> company_id))

Index - Schema public


Table: company_to_location

company_to_location Structure
F-Key Name Type Description
location.id location_id integer PRIMARY KEY
company.id company_id integer PRIMARY KEY

Index - Schema public


Table: contact_class

contact_class Structure
F-Key Name Type Description
id serial UNIQUE NOT NULL
class text PRIMARY KEY

 

contact_class Constraints
Name Constraint
contact_class_class_check CHECK (("class" ~ '[[:alnum:]_]'::text))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public


Table: country

country Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text NOT NULL
short_name text NOT NULL
itu text

The ITU Telecommunication Standardization Sector code for calling internationally. For example, the US is 1, Great Britain is 44

 

country Constraints
Name Constraint
country_name_check CHECK ((name ~ '[[:alnum:]_]'::text))
country_short_name_check CHECK ((short_name ~ '[[:alnum:]_]'::text))

Tables referencing this one via Foreign Key Constraints:

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:

Index - Schema public


Table: customer

This is now a metadata table that holds information specific to customers. Source info is not part of the entity management

customer Structure
F-Key Name Type Description
id serial PRIMARY KEY
entity.id entity_id integer

This is the relationship between entities and customers
discount numeric
taxincluded boolean DEFAULT false
creditlimit numeric
terms smallint
customernumber character varying(32)
cc text
bcc text
business_id integer
sic_code character varying(6)
iban character varying(34)
bic character varying(11)
language_code character varying(6)
pricegroup_id integer
curr character(3)
startdate date DEFAULT ('now'::text)::date
enddate date
invoice_notes text
customer_customernumber_key customernumber customer_id_key id

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 serial PRIMARY KEY
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

Is a metadata table specific to employees

employee Structure
F-Key Name Type Description
id serial PRIMARY KEY
entity.id entity_id integer NOT NULL
login text
startdate date DEFAULT ('now'::text)::date
enddate date
notes text
role character varying(20)
sales boolean DEFAULT false
ssn character varying(20)
iban character varying(34)
bic character varying(11)
managerid integer
employeenumber character varying(32)
dob date

Index - Schema public


Table: entity

The primary entity table to map to all contacts

entity Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text

This is the common name of an entity. If it was a person it may be Joshua Drake, a company Acme Corp. You may also choose to use a domain such as commandprompt.com
entity_class.id entity_class integer NOT NULL

 

entity Constraints
Name Constraint
entity_name_check CHECK ((name ~ '[[:alnum:]_]'::text))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public


Table: entity_class

Defines the class type such as vendor, customer, contact, employee

entity_class Structure
F-Key Name Type Description
id serial PRIMARY KEY

The first 7 values are reserved and permanent
class text NOT NULL
active boolean NOT NULL DEFAULT true

 

entity_class Constraints
Name Constraint
entity_class_class_check CHECK (("class" ~ '[[:alnum:]_]'::text))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public


Table: entity_class_to_entity

Relation builder for classes to entity

entity_class_to_entity Structure
F-Key Name Type Description
entity_class.id entity_class_id integer PRIMARY KEY
entity.id entity_id integer PRIMARY KEY

Index - Schema public


Table: entity_other_name

Similar to company_other_name, a person may be jd, Joshua Drake, linuxpoet... all are the same person.

entity_other_name Structure
F-Key Name Type Description
entity.id entity_id integer PRIMARY KEY
other_name text PRIMARY KEY

 

entity_other_name Constraints
Name Constraint
entity_other_name_other_name_check CHECK ((other_name ~ '[[:alnum:]_]'::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 serial PRIMARY KEY
reference text
description text
transdate date DEFAULT ('now'::text)::date
person.id person_id integer
notes text
department_id integer
gl_description_key lower(description) 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
person.id person_id integer NOT NULL
entry_id serial PRIMARY KEY

Index - Schema public


Table: invoice

invoice Structure
F-Key Name Type Description
id serial PRIMARY KEY
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 serial PRIMARY KEY
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
person.id person_id integer NOT NULL
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: location

location Structure
F-Key Name Type Description
id serial PRIMARY KEY
line_one text NOT NULL
line_two text
line_three text
city_province text NOT NULL
country.id country_id integer NOT NULL
mail_code text NOT NULL

 

location Constraints
Name Constraint
location_city_province_check CHECK ((city_province ~ '[[:alnum:]_]'::text))
location_line_one_check CHECK ((line_one ~ '[[:alnum:]_]'::text))
location_mail_code_check CHECK ((mail_code ~ '[[:alnum:]_]'::text))

Tables referencing this one via Foreign Key Constraints:

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 serial PRIMARY KEY
ordnumber text
transdate date DEFAULT ('now'::text)::date
entity.id entity_id integer NOT NULL
amount numeric
netamount numeric
reqdate date
taxincluded boolean
shippingpoint text
notes text
curr character(3)
person.id person_id integer NOT NULL
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_class.id oe_class_id integer NOT NULL
oe_id_key id oe_ordnumber_key ordnumber oe_transdate_key transdate

Index - Schema public


Table: oe_class

This could probably be done better. But I need to remove the customer_id/vendor_id relationship and instead rely on a classification

oe_class Structure
F-Key Name Type Description
id smallint UNIQUE
oe_class text PRIMARY KEY

 

oe_class Constraints
Name Constraint
oe_class_id_check CHECK (((id = 1) OR (id = 2)))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public


Table: orderitems

orderitems Structure
F-Key Name Type Description
id serial PRIMARY KEY
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 serial PRIMARY KEY
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:

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 serial PRIMARY KEY
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
entity.id entity_id integer NOT NULL
parts_id integer
partnumber text
leadtime smallint
lastcost numeric
curr character(3)
entry_id serial PRIMARY KEY
partsvendor_parts_id_key parts_id

Index - Schema public


Table: person

Every person, must have an entity to derive a common or display name. The correct way to get class information on a person would be person.entity_id->entity_class_to_entity.entity_id.

person Structure
F-Key Name Type Description
id serial PRIMARY KEY
entity.id entity_id integer NOT NULL
salutation.id salutation_id integer
first_name text NOT NULL
middle_name text
last_name text NOT NULL
location.id primary_location_id integer

 

person Constraints
Name Constraint
person_first_name_check CHECK ((first_name ~ '[[:alnum:]_]'::text))
person_last_name_check CHECK ((last_name ~ '[[:alnum:]_]'::text))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public


Table: person_to_company

person_to_company Structure
F-Key Name Type Description
location.id location_id integer PRIMARY KEY
person.id person_id integer PRIMARY KEY

Index - Schema public


Table: person_to_contact

To keep track of the relationship between multiple contact methods and a single individual

person_to_contact Structure
F-Key Name Type Description
person.id person_id integer PRIMARY KEY
contact_class.id contact_class_id integer PRIMARY KEY
contact text PRIMARY KEY

 

person_to_contact Constraints
Name Constraint
person_to_contact_contact_check CHECK ((contact ~ '[[:alnum:]_]'::text))

Index - Schema public


Table: person_to_entity

person_to_entity Structure
F-Key Name Type Description
person.id person_id integer PRIMARY KEY
entity.id entity_id integer PRIMARY KEY
related_how text
created date NOT NULL DEFAULT ('now'::text)::date

 

person_to_entity Constraints
Name Constraint
person_to_entity_check CHECK ((entity_id <> person_id))

Index - Schema public


Table: pricegroup

pricegroup Structure
F-Key Name Type Description
id serial PRIMARY KEY
pricegroup text
pricegroup_id_key id pricegroup_pricegroup_key pricegroup

Index - Schema public


Table: project

project Structure
F-Key Name Type Description
id serial PRIMARY KEY
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: salutation

salutation Structure
F-Key Name Type Description
id serial UNIQUE NOT NULL
salutation text PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

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:

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:

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

Now a meta data table

vendor Structure
F-Key Name Type Description
id serial PRIMARY KEY
entity.id entity_id integer NOT NULL
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)
language_code character varying(6)
pricegroup_id integer
curr character(3)
startdate date
enddate date
vendor_id_key id 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

Index - Schema public


Table: warehouse

warehouse Structure
F-Key Name Type Description
id serial PRIMARY KEY
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