Dumped on 2007-06-13

Index of database - lsmb13-new

• public
• acc_trans
• ap
• ar
• assembly
• audittrail
• business
• chart
• company
• company_to_contact
• company_to_entity
• company_to_location
• contact_class
• country
• custom_field_catalog
• custom_table_catalog
• customer
• customertax
• defaults
• department
• dpt_trans
• employee
• entity
• entity_class
• entity_class_to_entity
• entity_other_name
• exchangerate
• gifi
• gl
• inventory
• invoice
• jcitems
• language
• location
• makemodel
• oe
• oe_class
• orderitems
• parts
• partscustomer
• partsgroup
• partstax
• partsvendor
• person
• person_to_company
• person_to_contact
• person_to_entity
• pricegroup
• project
• recurring
• recurringemail
• recurringprint
• salutation
• 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

Index - Schema public

Table: ap

Index - Schema public

Table: ar

Index - Schema public

Table: assembly

Index - Schema public

Table: audittrail

Index - Schema public

Table: business

Index - Schema public

Table: chart

Tables referencing this one via Foreign Key Constraints:

• acc_trans

• partstax

• tax

Index - Schema public

Table: company

Tables referencing this one via Foreign Key Constraints:

• company_to_contact

• company_to_entity

• company_to_location

Index - Schema public

Table: company_to_contact

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

Index - Schema public

Table: company_to_entity

Index - Schema public

Table: company_to_location

Index - Schema public

Table: contact_class

Tables referencing this one via Foreign Key Constraints:

• company_to_contact

• person_to_contact

Index - Schema public

Table: country

Tables referencing this one via Foreign Key Constraints:

• location

Index - Schema public

Table: custom_field_catalog

Index - Schema public

Table: custom_table_catalog

Tables referencing this one via Foreign Key Constraints:

• custom_field_catalog

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

Index - Schema public

Table: customertax

Index - Schema public

Table: defaults

Index - Schema public

Table: department

Index - Schema public

Table: dpt_trans

Index - Schema public

Table: employee

Is a metadata table specific to employees

Index - Schema public

Table: entity

The primary entity table to map to all contacts

Tables referencing this one via Foreign Key Constraints:

• ap

• ar

• company

• company_to_entity

• customer

• employee

• entity_class_to_entity

• entity_other_name

• oe

• partsvendor

• person

• person_to_entity

• vendor

Index - Schema public

Table: entity_class

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

Tables referencing this one via Foreign Key Constraints:

• company

• entity

• entity_class_to_entity

Index - Schema public

Table: entity_class_to_entity

Relation builder for classes to entity

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.

Index - Schema public

Table: exchangerate

Index - Schema public

Table: gifi

Index - Schema public

Table: gl

Index - Schema public

Table: inventory

Index - Schema public

Table: invoice

Index - Schema public

Table: jcitems

Index - Schema public

Table: language

Index - Schema public

Table: location

Tables referencing this one via Foreign Key Constraints:

• company

• company_to_location

• person

• person_to_company

Index - Schema public

Table: makemodel

Index - Schema public

Table: oe

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

Tables referencing this one via Foreign Key Constraints:

• oe

Index - Schema public

Table: orderitems

Index - Schema public

Table: parts

Tables referencing this one via Foreign Key Constraints:

• partstax

Index - Schema public

Table: partscustomer

Index - Schema public

Table: partsgroup

Index - Schema public

Table: partstax

Index - Schema public

Table: partsvendor

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.

Tables referencing this one via Foreign Key Constraints:

• ap

• ar

• audittrail

• gl

• inventory

• jcitems

• oe

• person_to_company

• person_to_contact

• person_to_entity

Index - Schema public

Table: person_to_company

Index - Schema public

Table: person_to_contact

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

Index - Schema public

Table: person_to_entity

Index - Schema public

Table: pricegroup

Index - Schema public

Table: project

Index - Schema public

Table: recurring

Index - Schema public

Table: recurringemail

Index - Schema public

Table: recurringprint

Index - Schema public

Table: salutation

Tables referencing this one via Foreign Key Constraints:

• person

Index - Schema public

Table: shipto

Index - Schema public

Table: sic

Index - Schema public

Table: status

Index - Schema public

Table: tax

Index - Schema public

Table: taxcategory

Tables referencing this one via Foreign Key Constraints:

• partstax

Index - Schema public

Table: taxmodule

Tables referencing this one via Foreign Key Constraints:

• tax

• taxcategory

Index - Schema public

Table: transactions

Index - Schema public

Table: translation

Index - Schema public

Table: vendor

Now a meta data table

Index - Schema public

Table: vendortax

Index - Schema public

Table: warehouse

Index - Schema public

Table: yearend

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