Database freemoney

This is the description of tables for The FreeMoney Project.

To start learning about how the system is build look at table ar_payment, table co_contact, table co_company, table gl_balance and table so_invoice.

For more information go to http://www.freemoney.org

Tables
Views
Functions
Fields

List of tables

ar_contact
ar_custacct
ar_journal
ar_payment
co_address
co_addresstype
co_businesssector
co_company
co_contact
co_contactnotes
co_contactselect
co_contacttype
co_country
co_creditterms
co_custgrp
co_customer
co_customerresponsibility
co_customertype
co_deliveryterms
co_freightcode
co_paymentterms
co_person
co_postalcodes
co_selection
co_telephone
co_telephonetype
co_url
co_urltype
gl_accounttype
gl_acctgrid
gl_acctname
gl_amounttype
gl_balance
gl_batch
gl_chartofaccount
gl_currency
gl_currencyrate
gl_currencytype
gl_dimension
gl_journal
gl_repaccount
gl_repcolumn
gl_report
gl_system
pd_arthist
pd_article
pd_artprice
pd_deliverytype
pd_inventory
pd_pricetype
pd_productgroup
pd_vat
so_delivery
so_invoice
so_invoicebatch
so_invoicetemplate
so_invtplchoice
so_orderhead
so_orderline
su_event
su_eventrelease
su_eventtime
su_subscriptarticle
su_subscription
sy_ownercompany
sy_typetext

List of functions

Tables in freemoney

Table ar_contact

Totals per month for customer

ar_contact
Field	Type	Description
contact_id	int4	not null Reference to ar_custacct
periodnr	date	not null Period of totals of this customer
sales	numeric	Total sales this period
paid	numeric	Amount paid this period
lastdate	date	??
due	numeric	??
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table ar_custacct

AR information about the customer.

ar_custacct
Field	Type	Description
contact_id	int4	not null Reference to co_contact table See also: table co_contact.
type	int4	Type of AR customer. Values - ??
disctype	int4	Discount type. 1 - amount 2 - percent
discvalue	numeric	Discount value. Depends on disctype
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table ar_journal

All transactions for the customer

ar_journal
Field	Type	Description
ar_id	int4	not null default nextval('ar_arsequence'::text) Sequence number
contact_id	int4	Customer reference
periodnr	date	Period nr of journal
invoicenr	int4	Initiating invoice nr. Null if no invoice
total	numeric	Total amount
paid	numeric	default 0 Amount already paid
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table ar_payment

Payments

ar_payment
Field	Type	Description
pay_id	int4	not null default nextval('ar_paysequence'::text) Sequence number
ar_id	int4	Reference to ar_journal (which invoice is paid)
contact_id	int4	Customer
description	varchar	Descriptive text of payment
amount	numeric	Paid amount
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_address

All addresses are placed here

co_address
Field	Type	Description
address_id	int4	not null default nextval('co_addresssequence'::text) Sequence number
country_id	int4	not null In which is the address?
contact_id	int4	not null The person or company at that address
type	int4	Type. E.g. 1 - Main address 2 - Delivery address 3 - Payment address
address1	varchar	First address line
address2	varchar	Second address line
address3	varchar	Third address line
city	varchar	City
state	varchar	State
postalcode	varchar	Postal code
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_addresstype

COMMENT ON TABLE co_addresstype IS '';

co_addresstype
Field	Type	Description
type	int4	not null COMMENT ON COLUMN co_addresstype.type IS '';
name	varchar	COMMENT ON COLUMN co_addresstype.name IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN co_addresstype.updated IS '';

Table co_businesssector

Some kind of business information. Idea not developed.

co_businesssector
Field	Type	Description
bsect_id	int4	not null default nextval('co_bsectsequence'::text) Sequence number
name	varchar	not null Business sector name
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_company

Table co_contact

Central table for all contact information

Every contact, independent of type (customer, employee, lessor...) will be here

co_contact
Field	Type	Description
contact_id	int4	not null default nextval('co_contactsequence'::text) Sequence number
contact_nr	numeric	??
type	int4	Type. E.g. 1 - Customer 2 - Owner company 3 - Employee
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_contactnotes

Contact notes.

The idea is to have an unlimited number of notes per customer, person or company

co_contactnotes
Field	Type	Description
note_id	int4	not null default nextval('co_notesequence'::text) Sequence number
contact_id	int4	Which contact?
description	varchar	The note
enddate	timestamp	Maybe it will expire someday
end_event	varchar	Or maybe it will end because something happens
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_contactselect

Table for holding the selected contacts.

The idea is to store selected customers until they're actively released by the user

co_contactselect
Field	Type	Description
select_id	int4	not null Reference to selection
contact_id	int4	not null Reference to contact
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_contacttype

Different contact types; customer, owner...

co_contacttype
Field	Type	Description
type	int4	not null COMMENT ON COLUMN co_contacttype.type IS '';
name	varchar	COMMENT ON COLUMN co_contacttype.name IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN co_contacttype.updated IS '';

Table co_country

Country information

co_country
Field	Type	Description
country_id	int4	not null default nextval('co_countrysequence'::text) Sequence number
code	bpchar	Official code. E.g. DK for Denmark
name	varchar	Country name
shipmodes	varchar	Shipping modes - from Interchange. Maybe not needed?
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_creditterms

Credit terms

co_creditterms
Field	Type	Description
creditcode	int4	not null Credit code. Unique ID
name	varchar	Name
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_custgrp

Customer group. Defines third dimension in accounting.

Could also be used to define discount levels

co_custgrp
Field	Type	Description
custgrp_id	int4	not null default nextval('co_custgrpsequence'::text) Sequence number
custgrp_nr	varchar	Freely assignable number
name	varchar	not null Name of customer group. E.g. "Dealers" or "Ordinary"
ac_id	int4	Reference to accounting dimension
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_customer

Customers

co_customer
Field	Type	Description
contact_id	int4	not null Reference to contact
type	int4	Customer type. E.g. ??
customernr	varchar	Customer number
custgrp_id	int4	Reference to customer group
bsect_id	int4	Reference to business sector. Currently not used
credit_code	int4	Credit code
deliverycode	int4	Delivery code
paycode	int4	Payment code
freightcode	int4	Freight code
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_customerresponsibility

Forgot what this table is for...

co_customerresponsibility
Field	Type	Description
contact_id	int4	not null Reference to contact
name	varchar	Name
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_customertype

COMMENT ON TABLE co_customertype IS '';

co_customertype
Field	Type	Description
type	int4	not null COMMENT ON COLUMN co_customertype.type IS '';
name	varchar	COMMENT ON COLUMN co_customertype.name IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN co_customertype.updated IS '';

Table co_deliveryterms

Delivery terms

co_deliveryterms
Field	Type	Description
deliverycode	int4	not null Delivery code. Unique ID
name	varchar	Name
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_freightcode

Freight code

co_freightcode
Field	Type	Description
freightcode	int4	not null Freight code. Unique ID
name	varchar	not null Name
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_paymentterms

Payment terms

co_paymentterms
Field	Type	Description
paycode	int4	not null Payment code. Unique ID
name	varchar	Name
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_person

Information about a person

co_person
Field	Type	Description
contact_id	int4	not null Reference to contact
firstname	varchar	First name
lastname	varchar	Last name
personid	varchar	Personal ID. E.g. social security number
updated	timestamp	not null default 'now' COMMENT ON COLUMN co_person.updated IS '';

Table co_postalcodes

Postal (zip) codes

co_postalcodes
Field	Type	Description
country_id	int4	not null COMMENT ON COLUMN co_postalcodes.country_id IS '';
postalcode	varchar	not null COMMENT ON COLUMN co_postalcodes.postalcode IS '';
name	varchar	COMMENT ON COLUMN co_postalcodes.name IS '';
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_selection

Contact selection.

The idea is to be able to select customers, persons or whatever either by choosing them individually, or by some rule

This could be used to find all customers with overdue payments and send them a reminder,

find potential customers for a product and much more

co_selection
Field	Type	Description
select_id	int4	not null default nextval('co_selectsequence'::text) Sequence number
name	varchar	Name
action	varchar	Some kind of selection rule, Not yet defined how this works
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_telephone

Telephone numbers

co_telephone
Field	Type	Description
phone_id	int4	not null default nextval('co_phonesequence'::text) Sequence number
contact_id	int4	Reference to contact
type	int4	Type. E.g. 1 - main line 2 - cell phone
phonenr	varchar	The number
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_telephonetype

COMMENT ON TABLE co_telephonetype IS '';

co_telephonetype
Field	Type	Description
type	int4	not null COMMENT ON COLUMN co_telephonetype.type IS '';
name	varchar	COMMENT ON COLUMN co_telephonetype.name IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN co_telephonetype.updated IS '';

Table co_url

Internet addresses

co_url
Field	Type	Description
url_id	int4	not null default nextval('co_urlsequence'::text) Sequence number
contact_id	int4	Reference to contact
type	int4	Type. E.g. 1 - Web site 2 - email 3 - ftp
url	varchar	The Internet address
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table co_urltype

COMMENT ON TABLE co_urltype IS '';

co_urltype
Field	Type	Description
type	int4	not null COMMENT ON COLUMN co_urltype.type IS '';
name	varchar	COMMENT ON COLUMN co_urltype.name IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN co_urltype.updated IS '';

Table gl_accounttype

COMMENT ON TABLE gl_accounttype IS '';

gl_accounttype
Field	Type	Description
type	int4	not null COMMENT ON COLUMN gl_accounttype.type IS '';
name	varchar	COMMENT ON COLUMN gl_accounttype.name IS '';
status	bool	COMMENT ON COLUMN gl_accounttype.status IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN gl_accounttype.updated IS '';

Table gl_acctgrid

Grid referenced by all dimensions. Currently 3: Account, product and customer

gl_acctgrid
Field	Type	Description
ag_id	int4	not null default nextval('gl_agsequence'::text) Sequence number
dim1	int4	First dimension - account
dim2	int4	Second dimension - product
dim3	int4	Third dimension - customer
dim4	int4	Not yet used
dim5	int4	Not yet used
type	int4	not null Type. 1 - Current 2 - budget 3 - best estimate 4 - last year
currency_id	int4	Currency
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table gl_acctname

Names for entries in chartofaccount, productgroup and customer group

gl_acctname
Field	Type	Description
ac_id	int4	not null default nextval('gl_acsequence'::text) Sequence number
name	varchar	Name
dimension	int4	Dimension. 1 - Chart of account 2 - Product 3 - Customer
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table gl_amounttype

COMMENT ON TABLE gl_amounttype IS '';

gl_amounttype
Field	Type	Description
type	int4	not null COMMENT ON COLUMN gl_amounttype.type IS '';
name	varchar	COMMENT ON COLUMN gl_amounttype.name IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN gl_amounttype.updated IS '';

Table gl_balance

Balance for period

gl_balance
Field	Type	Description
ag_id	int4	not null Reference to gl_acctgrid
periodnr	date	not null Period number
begindr	numeric	default 0 Beginning balance for debit
begincr	numeric	default 0 Beginning balance for credit
perioddr	numeric	default 0 This month's debit posting
periodcr	numeric	default 0 This month's credit posting
projecttodatedr	numeric	default 0 I have no idea
projecttodatecr	numeric	default 0 I have no idea
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table gl_batch

A batch is a logical way of grouping journal entries together.

A batch can be entered, but not accounted any time. A batch has to balance debit with credit before it can be accounted

gl_batch
Field	Type	Description
batch_id	int4	not null default nextval('gl_batchsequence'::text) Sequence number
currency_id	int4	Currency
system_id	int4	ID of originating system
type	int4	Type. ??
batchnr	int4	Used for - ?
name	varchar	Name
status	int4	Status. 100 - entered 200 - accounted
totaldr	numeric	Total debit of all journals of this batch
totalcr	numeric	Total credit of all journals of this batch
createddate	timestamp	Date of creation
posteddate	timestamp	Date of posting / accounting
soa	int4	COMMENT ON COLUMN gl_batch.soa IS '';
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table gl_chartofaccount

Chart of account

gl_chartofaccount
Field	Type	Description
coa_id	int4	not null default nextval('gl_coasequence'::text) Sequence number
ac_id	int4	not null Reference to acctname
type	int4	Type. 1 - Assets 2 - Liabilities...
currency_id	int4	Currency
number	varchar	Public chart of account number
vat_id	int4	COMMENT ON COLUMN gl_chartofaccount.vat_id IS '';
vat_coa	int4	COMMENT ON COLUMN gl_chartofaccount.vat_coa IS '';
soa	bool	COMMENT ON COLUMN gl_chartofaccount.soa IS '';
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table gl_currency

Currency information

gl_currency
Field	Type	Description
currency_id	int4	not null default nextval('gl_currencysequence'::text) Sequence number
code	varchar	not null Currency code
country_id	int4	not null Country reference
type	int4	not null Type
name	varchar	Name
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table gl_currencyrate

The currency rate towards the system rate

gl_currencyrate
Field	Type	Description
currency_id	int4	not null Sequence number
rate	numeric	Actual rate
startdate	timestamp	Starting date of this rate
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table gl_currencytype

COMMENT ON TABLE gl_currencytype IS '';

gl_currencytype
Field	Type	Description
type	int4	not null COMMENT ON COLUMN gl_currencytype.type IS '';
name	varchar	COMMENT ON COLUMN gl_currencytype.name IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN gl_currencytype.updated IS '';

Table gl_dimension

COMMENT ON TABLE gl_dimension IS '';

gl_dimension
Field	Type	Description
nr	int4	not null COMMENT ON COLUMN gl_dimension.nr IS '';
name	varchar	COMMENT ON COLUMN gl_dimension.name IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN gl_dimension.updated IS '';

Table gl_journal

A journal entry is the lowest atomic element of accounting

gl_journal
Field	Type	Description
journal_id	int4	not null default nextval('gl_journalsequence'::text) Sequence number
batch_id	int4	not null Reference to gl_batch
journalnr	int4	not null Journal number
ag_id	int4	Refence to acctgrid
entrydate	timestamp	Date of entry
entereddr	numeric	Entered debit amount
enteredcr	numeric	Entered credit amount
accounteddr	numeric	Accounted debit amount. (What for?)
accountedcr	numeric	Accounted credit amount. (What for?)
text	varchar	Text
accountingdate	timestamp	Time for accounting
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table gl_repaccount

Information about what the individual line contains

Should contain a line number too. And some way of picking individual acctgrid elements as you like

gl_repaccount
Field	Type	Description
glreport_id	int4	not null Reference to gl_report
coa_id	int4	Chart of account
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table gl_repcolumn

Information about the individual columns

gl_repcolumn
Field	Type	Description
glreport_id	int4	not null Reference til gl_report
colnr	int4	not null Column number
amounttype	int4	Type. 1 - Current 2 - Budget etc.
month	int4	month, quarter, year, ytd should be replaced by a report column type
quarter	int4	Type should be 1 - month 2 - quarter 3 - year 4 - ytd 5 - begining balance
year	int4	Or maybe begining balance as separate ?
ytd	int4	COMMENT ON COLUMN gl_repcolumn.ytd IS '';
roundinglevel	int4	How accurate will the report be - with decimal, whole numbers, thousands, etc.
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table gl_report

There's a need for an easy to do report manager for generel ledger.

This idea is still not developed.

gl_report
Field	Type	Description
glreport_id	int4	not null default nextval('gl_reportsequence'::text) Sequence number
reportnr	int4	Some people like to have a report number
name	varchar	Report name
description	varchar	Report desciption
updated	timestamp	not null default 'now' Timestamp for last update of this row

Table gl_system

COMMENT ON TABLE gl_system IS '';

gl_system
Field	Type	Description
nr	int4	not null COMMENT ON COLUMN gl_system.nr IS '';
name	varchar	COMMENT ON COLUMN gl_system.name IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN gl_system.updated IS '';

Table pd_arthist

COMMENT ON TABLE pd_arthist IS '';

pd_arthist
Field	Type	Description
art_id	int4	not null COMMENT ON COLUMN pd_arthist.art_id IS '';
type	int4	COMMENT ON COLUMN pd_arthist.type IS '';
period	date	COMMENT ON COLUMN pd_arthist.period IS '';
qty	numeric	COMMENT ON COLUMN pd_arthist.qty IS '';
revenue	numeric	COMMENT ON COLUMN pd_arthist.revenue IS '';
cost	numeric	COMMENT ON COLUMN pd_arthist.cost IS '';
stock	numeric	COMMENT ON COLUMN pd_arthist.stock IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN pd_arthist.updated IS '';

Table pd_article

COMMENT ON TABLE pd_article IS '';

pd_article
Field	Type	Description
art_id	int4	not null default nextval('pd_artsequence'::text) COMMENT ON COLUMN pd_article.art_id IS '';
pgrp_id	int4	COMMENT ON COLUMN pd_article.pgrp_id IS '';
articlenr	varchar	COMMENT ON COLUMN pd_article.articlenr IS '';
altartnr	varchar	COMMENT ON COLUMN pd_article.altartnr IS '';
description	varchar	COMMENT ON COLUMN pd_article.description IS '';
unitweight	numeric	COMMENT ON COLUMN pd_article.unitweight IS '';
delivery	int4	COMMENT ON COLUMN pd_article.delivery IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN pd_article.updated IS '';

Table pd_artprice

COMMENT ON TABLE pd_artprice IS '';

pd_artprice
Field	Type	Description
art_id	int4	not null COMMENT ON COLUMN pd_artprice.art_id IS '';
type	int4	not null COMMENT ON COLUMN pd_artprice.type IS '';
price	numeric	COMMENT ON COLUMN pd_artprice.price IS '';
validfrom	timestamp	not null COMMENT ON COLUMN pd_artprice.validfrom IS '';
validto	timestamp	COMMENT ON COLUMN pd_artprice.validto IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN pd_artprice.updated IS '';

Table pd_deliverytype

COMMENT ON TABLE pd_deliverytype IS '';

pd_deliverytype
Field	Type	Description
type	int4	not null COMMENT ON COLUMN pd_deliverytype.type IS '';
name	varchar	COMMENT ON COLUMN pd_deliverytype.name IS '';
form	varchar	COMMENT ON COLUMN pd_deliverytype.form IS '';
orderstatus	int4	COMMENT ON COLUMN pd_deliverytype.orderstatus IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN pd_deliverytype.updated IS '';

Table pd_inventory

COMMENT ON TABLE pd_inventory IS '';

pd_inventory
Field	Type	Description
art_id	int4	not null COMMENT ON COLUMN pd_inventory.art_id IS '';
unitcost	numeric	COMMENT ON COLUMN pd_inventory.unitcost IS '';
stockqty	numeric	COMMENT ON COLUMN pd_inventory.stockqty IS '';
minqty	numeric	COMMENT ON COLUMN pd_inventory.minqty IS '';
maxqty	numeric	COMMENT ON COLUMN pd_inventory.maxqty IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN pd_inventory.updated IS '';

Table pd_pricetype

COMMENT ON TABLE pd_pricetype IS '';

pd_pricetype
Field	Type	Description
type	int4	not null COMMENT ON COLUMN pd_pricetype.type IS '';
name	varchar	COMMENT ON COLUMN pd_pricetype.name IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN pd_pricetype.updated IS '';

Table pd_productgroup

COMMENT ON TABLE pd_productgroup IS '';

pd_productgroup
Field	Type	Description
pgrp_id	int4	not null default nextval('pd_pgrpsequence'::text) COMMENT ON COLUMN pd_productgroup.pgrp_id IS '';
vat_id	int4	COMMENT ON COLUMN pd_productgroup.vat_id IS '';
name	varchar	COMMENT ON COLUMN pd_productgroup.name IS '';
ac_id	int4	COMMENT ON COLUMN pd_productgroup.ac_id IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN pd_productgroup.updated IS '';

Table pd_vat

COMMENT ON TABLE pd_vat IS '';

pd_vat
Field	Type	Description
vat_id	int4	not null default nextval('pd_vatsequence'::text) COMMENT ON COLUMN pd_vat.vat_id IS '';
name	varchar	COMMENT ON COLUMN pd_vat.name IS '';
percent	numeric	COMMENT ON COLUMN pd_vat.percent IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN pd_vat.updated IS '';

Table so_delivery

COMMENT ON TABLE so_delivery IS '';

so_delivery
Field	Type	Description
ordernr	int4	not null COMMENT ON COLUMN so_delivery.ordernr IS '';
status	int4	COMMENT ON COLUMN so_delivery.status IS '';
form	varchar	COMMENT ON COLUMN so_delivery.form IS '';
tracking	varchar	COMMENT ON COLUMN so_delivery.tracking IS '';
weight	numeric	COMMENT ON COLUMN so_delivery.weight IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN so_delivery.updated IS '';

Table so_invoice

COMMENT ON TABLE so_invoice IS '';

so_invoice
Field	Type	Description
invoicenr	int4	not null default nextval('so_invoicesequence'::text) COMMENT ON COLUMN so_invoice.invoicenr IS '';
ordernr	int4	not null COMMENT ON COLUMN so_invoice.ordernr IS '';
batchnr	int4	not null COMMENT ON COLUMN so_invoice.batchnr IS '';
invoicetotal	numeric	COMMENT ON COLUMN so_invoice.invoicetotal IS '';
invoicedate	timestamp	COMMENT ON COLUMN so_invoice.invoicedate IS '';
status	int4	COMMENT ON COLUMN so_invoice.status IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN so_invoice.updated IS '';

Table so_invoicebatch

COMMENT ON TABLE so_invoicebatch IS '';

so_invoicebatch
Field	Type	Description
batchnr	int4	not null default nextval('so_invbatchsequence'::text) COMMENT ON COLUMN so_invoicebatch.batchnr IS '';
ordernr	int4	COMMENT ON COLUMN so_invoicebatch.ordernr IS '';
page	int4	COMMENT ON COLUMN so_invoicebatch.page IS '';
status	int4	COMMENT ON COLUMN so_invoicebatch.status IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN so_invoicebatch.updated IS '';

Table so_invoicetemplate

COMMENT ON TABLE so_invoicetemplate IS '';

so_invoicetemplate
Field	Type	Description
invtplnr	int4	not null default nextval('so_invtplsequence'::text) COMMENT ON COLUMN so_invoicetemplate.invtplnr IS '';
template	varchar	COMMENT ON COLUMN so_invoicetemplate.template IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN so_invoicetemplate.updated IS '';

Table so_invtplchoice

COMMENT ON TABLE so_invtplchoice IS '';

so_invtplchoice
Field	Type	Description
invtplcnr	int4	not null default nextval('so_invtplcsequence'::text) COMMENT ON COLUMN so_invtplchoice.invtplcnr IS '';
invtplnr	int4	COMMENT ON COLUMN so_invtplchoice.invtplnr IS '';
ordernr	int4	COMMENT ON COLUMN so_invtplchoice.ordernr IS '';
contact_id	int4	COMMENT ON COLUMN so_invtplchoice.contact_id IS '';
custgrp_id	int4	COMMENT ON COLUMN so_invtplchoice.custgrp_id IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN so_invtplchoice.updated IS '';

Table so_orderhead

COMMENT ON TABLE so_orderhead IS '';

so_orderhead
Field	Type	Description
ordernr	int4	not null default nextval('so_ordersequence'::text) COMMENT ON COLUMN so_orderhead.ordernr IS '';
contact_id	int4	not null COMMENT ON COLUMN so_orderhead.contact_id IS '';
status	int4	COMMENT ON COLUMN so_orderhead.status IS '';
currency_id	int4	COMMENT ON COLUMN so_orderhead.currency_id IS '';
disctype	int4	COMMENT ON COLUMN so_orderhead.disctype IS '';
discount	numeric	COMMENT ON COLUMN so_orderhead.discount IS '';
ourref	varchar	COMMENT ON COLUMN so_orderhead.ourref IS '';
yourref	varchar	COMMENT ON COLUMN so_orderhead.yourref IS '';
credit_code	int4	COMMENT ON COLUMN so_orderhead.credit_code IS '';
deliverycode	int4	COMMENT ON COLUMN so_orderhead.deliverycode IS '';
paycode	int4	COMMENT ON COLUMN so_orderhead.paycode IS '';
freightcode	int4	COMMENT ON COLUMN so_orderhead.freightcode IS '';
orderdate	timestamp	default 'now' COMMENT ON COLUMN so_orderhead.orderdate IS '';
deliverydate	timestamp	COMMENT ON COLUMN so_orderhead.deliverydate IS '';
ordertotal	numeric	COMMENT ON COLUMN so_orderhead.ordertotal IS '';
vattotal	numeric	COMMENT ON COLUMN so_orderhead.vattotal IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN so_orderhead.updated IS '';

Table so_orderline

COMMENT ON TABLE so_orderline IS '';

so_orderline
Field	Type	Description
ordernr	int4	not null COMMENT ON COLUMN so_orderline.ordernr IS '';
linenr	int4	not null COMMENT ON COLUMN so_orderline.linenr IS '';
art_id	int4	COMMENT ON COLUMN so_orderline.art_id IS '';
description	varchar	COMMENT ON COLUMN so_orderline.description IS '';
qty	numeric	COMMENT ON COLUMN so_orderline.qty IS '';
qtyshipped	numeric	COMMENT ON COLUMN so_orderline.qtyshipped IS '';
unitprice	numeric	COMMENT ON COLUMN so_orderline.unitprice IS '';
disctype	numeric	COMMENT ON COLUMN so_orderline.disctype IS '';
discount	numeric	COMMENT ON COLUMN so_orderline.discount IS '';
linetotal	numeric	COMMENT ON COLUMN so_orderline.linetotal IS '';
linevat	numeric	COMMENT ON COLUMN so_orderline.linevat IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN so_orderline.updated IS '';

Table su_event

Central event table

su_event
Field	Type	Description
event_id	int4	not null default nextval('su_event_id'::text) Unique identifier.
type	int4	Event type. 1 - time, 2 - new release
updated	timestamp	not null default 'now' Time stamp for update.

Table su_eventrelease

Release related events

su_eventrelease
Field	Type	Description
event_id	int4	not null Reference to su_event
name	varchar	Name of event
releasedate	timestamp	Date of release
active	int4	1 - active, 2 - not active
updated	timestamp	not null default 'now' Time stamp for update.

Table su_eventtime

Time related events

su_eventtime
Field	Type	Description
event_id	int4	not null Reference to su_event
name	varchar	Name of event
intervaltype	int4	1 - day, 2 - week, 3 - month, 4 - year
intervalperiod	int4	Length of period
partial	int4	If there's partial payment if cancelled in period. 1 - no, 2 - yes
updated	timestamp	not null default 'now' Time stamp for update.

Table su_subscriptarticle

Subscribed articles

su_subscriptarticle
Field	Type	Description
subscription_id	int4	not null Reference to subscription
linenr	int4	not null Order of articles in subscription
art_id	int4	Reference to article
qty	int4	Quantity
updated	timestamp	not null default 'now' Time stamp for update.

Table su_subscription

Subscriptions, linking contact with events

su_subscription
Field	Type	Description
subscription_id	int4	not null default nextval('su_subscription_id'::text) Unique identifier.
contact_id	int4	not null Reference to contact
event_id	int4	not null Reference to event
startdate	timestamp	not null Start date for subscription
nextdate	timestamp	Next time this subscription will be activated
invoiced	timestamp	Latest invoice date
enddate	timestamp	not null default 'infinity' Date of cancellation, if any
updated	timestamp	not null default 'now' Time stamp for update.

Table sy_ownercompany

System owner

sy_ownercompany
Field	Type	Description
owner_id	int4	not null default nextval('sy_ownersequence'::text) Sequence number. Not decided yet if more than one owner is allowed
contact_id	int4	Reference to contact
periodfrom	date	From period (accounting)
periodto	date	To period (accounting)
debtcoaid	int4	COMMENT ON COLUMN sy_ownercompany.debtcoaid IS '';
invat_id	int4	COMMENT ON COLUMN sy_ownercompany.invat_id IS '';
outvat_id	int4	COMMENT ON COLUMN sy_ownercompany.outvat_id IS '';
artext	varchar	COMMENT ON COLUMN sy_ownercompany.artext IS '';
arbatchtext	varchar	COMMENT ON COLUMN sy_ownercompany.arbatchtext IS '';
amounttype	int4	COMMENT ON COLUMN sy_ownercompany.amounttype IS '';
currencyid	int4	COMMENT ON COLUMN sy_ownercompany.currencyid IS '';
updated	timestamp	not null default 'now' Timestamp for latest update of this row

Table sy_typetext

COMMENT ON TABLE sy_typetext IS '';

sy_typetext
Field	Type	Description
type	int4	not null COMMENT ON COLUMN sy_typetext.type IS '';
name	varchar	COMMENT ON COLUMN sy_typetext.name IS '';
updated	timestamp	not null default 'now' COMMENT ON COLUMN sy_typetext.updated IS '';

Views in freemoney

Functions in freemoney

No functions defined

Fields in freemoney

ac_id: co_custgrp, gl_acctname, gl_chartofaccount, pd_productgroup
accountedcr: gl_journal
accounteddr: gl_journal
accountingdate: gl_journal
action: co_selection
active: su_eventrelease
address1: co_address
address2: co_address
address3: co_address
address_id: co_address
ag_id: gl_acctgrid, gl_balance, gl_journal
altartnr: pd_article
amount: ar_payment
amounttype: gl_repcolumn, sy_ownercompany
ar_id: ar_journal, ar_payment
arbatchtext: sy_ownercompany
art_id: pd_arthist, pd_article, pd_artprice, pd_inventory, so_orderline, su_subscriptarticle
artext: sy_ownercompany
articlenr: pd_article
attention: co_company
batch_id: gl_batch, gl_journal
batchnr: gl_batch, so_invoice, so_invoicebatch
begincr: gl_balance
begindr: gl_balance
bsect_id: co_businesssector, co_customer
city: co_address
coa_id: gl_chartofaccount, gl_repaccount
code: co_country, gl_currency
colnr: gl_repcolumn
contact_id: ar_contact, ar_custacct, ar_journal, ar_payment, co_address, co_company, co_contact, co_contactnotes, co_contactselect, co_customer, co_customerresponsibility, co_person, co_telephone, co_url, so_invtplchoice, so_orderhead, su_subscription, sy_ownercompany
contact_nr: co_contact
cost: pd_arthist
country_id: co_address, co_country, co_postalcodes, gl_currency
createddate: gl_batch
credit_code: co_customer, so_orderhead
creditcode: co_creditterms
currency_id: gl_acctgrid, gl_batch, gl_chartofaccount, gl_currency, gl_currencyrate, so_orderhead
currencyid: sy_ownercompany
custgrp_id: co_custgrp, co_customer, so_invtplchoice
custgrp_nr: co_custgrp
customernr: co_customer
debtcoaid: sy_ownercompany
delivery: pd_article
deliverycode: co_customer, co_deliveryterms, so_orderhead
deliverydate: so_orderhead
description: ar_payment, co_contactnotes, gl_report, pd_article, so_orderline
dim1: gl_acctgrid
dim2: gl_acctgrid
dim3: gl_acctgrid
dim4: gl_acctgrid
dim5: gl_acctgrid
dimension: gl_acctname
discount: so_orderhead, so_orderline
disctype: ar_custacct, so_orderhead, so_orderline
discvalue: ar_custacct
due: ar_contact
end_event: co_contactnotes
enddate: co_contactnotes, su_subscription
enteredcr: gl_journal
entereddr: gl_journal
entrydate: gl_journal
event_id: su_event, su_eventrelease, su_eventtime, su_subscription
firstname: co_person
form: pd_deliverytype, so_delivery
freightcode: co_customer, co_freightcode, so_orderhead
glreport_id: gl_repaccount, gl_repcolumn, gl_report
intervalperiod: su_eventtime
intervaltype: su_eventtime
invat_id: sy_ownercompany
invoiced: su_subscription
invoicedate: so_invoice
invoicenr: ar_journal, so_invoice
invoicetotal: so_invoice
invtplcnr: so_invtplchoice
invtplnr: so_invoicetemplate, so_invtplchoice
journal_id: gl_journal
journalnr: gl_journal
lastdate: ar_contact
lastname: co_person
linenr: so_orderline, su_subscriptarticle
linetotal: so_orderline
linevat: so_orderline
maxqty: pd_inventory
minqty: pd_inventory
month: gl_repcolumn
name: co_addresstype, co_businesssector, co_company, co_contacttype, co_country, co_creditterms, co_custgrp, co_customerresponsibility, co_customertype, co_deliveryterms, co_freightcode, co_paymentterms, co_postalcodes, co_selection, co_telephonetype, co_urltype, gl_accounttype, gl_acctname, gl_amounttype, gl_batch, gl_currency, gl_currencytype, gl_dimension, gl_report, gl_system, pd_deliverytype, pd_pricetype, pd_productgroup, pd_vat, su_eventrelease, su_eventtime, sy_typetext
nextdate: su_subscription
note_id: co_contactnotes
nr: gl_dimension, gl_system
number: gl_chartofaccount
orderdate: so_orderhead
ordernr: so_delivery, so_invoice, so_invoicebatch, so_invtplchoice, so_orderhead, so_orderline
orderstatus: pd_deliverytype
ordertotal: so_orderhead
ourref: so_orderhead
outvat_id: sy_ownercompany
owner_id: sy_ownercompany
page: so_invoicebatch
paid: ar_contact, ar_journal
partial: su_eventtime
pay_id: ar_payment
paycode: co_customer, co_paymentterms, so_orderhead
percent: pd_vat
period: pd_arthist
periodcr: gl_balance
perioddr: gl_balance
periodfrom: sy_ownercompany
periodnr: ar_contact, ar_journal, gl_balance
periodto: sy_ownercompany
personid: co_person
pgrp_id: pd_article, pd_productgroup
phone_id: co_telephone
phonenr: co_telephone
postalcode: co_address, co_postalcodes
posteddate: gl_batch
price: pd_artprice
projecttodatecr: gl_balance
projecttodatedr: gl_balance
qty: pd_arthist, so_orderline, su_subscriptarticle
qtyshipped: so_orderline
quarter: gl_repcolumn
rate: gl_currencyrate
regnr: co_company
releasedate: su_eventrelease
reportnr: gl_report
revenue: pd_arthist
roundinglevel: gl_repcolumn
sales: ar_contact
select_id: co_contactselect, co_selection
shipmodes: co_country
soa: gl_batch, gl_chartofaccount
startdate: gl_currencyrate, su_subscription
state: co_address
status: gl_accounttype, gl_batch, so_delivery, so_invoice, so_invoicebatch, so_orderhead
stock: pd_arthist
stockqty: pd_inventory
subscription_id: su_subscriptarticle, su_subscription
system_id: gl_batch
template: so_invoicetemplate
text: gl_journal
total: ar_journal
totalcr: gl_batch
totaldr: gl_batch
tracking: so_delivery
type: ar_custacct, co_address, co_addresstype, co_contact, co_contacttype, co_customer, co_customertype, co_telephone, co_telephonetype, co_url, co_urltype, gl_accounttype, gl_acctgrid, gl_amounttype, gl_batch, gl_chartofaccount, gl_currency, gl_currencytype, pd_arthist, pd_artprice, pd_deliverytype, pd_pricetype, su_event, sy_typetext
unitcost: pd_inventory
unitprice: so_orderline
unitweight: pd_article
updated: ar_contact, ar_custacct, ar_journal, ar_payment, co_address, co_addresstype, co_businesssector, co_company, co_contact, co_contactnotes, co_contactselect, co_contacttype, co_country, co_creditterms, co_custgrp, co_customer, co_customerresponsibility, co_customertype, co_deliveryterms, co_freightcode, co_paymentterms, co_person, co_postalcodes, co_selection, co_telephone, co_telephonetype, co_url, co_urltype, gl_accounttype, gl_acctgrid, gl_acctname, gl_amounttype, gl_balance, gl_batch, gl_chartofaccount, gl_currency, gl_currencyrate, gl_currencytype, gl_dimension, gl_journal, gl_repaccount, gl_repcolumn, gl_report, gl_system, pd_arthist, pd_article, pd_artprice, pd_deliverytype, pd_inventory, pd_pricetype, pd_productgroup, pd_vat, so_delivery, so_invoice, so_invoicebatch, so_invoicetemplate, so_invtplchoice, so_orderhead, so_orderline, su_event, su_eventrelease, su_eventtime, su_subscriptarticle, su_subscription, sy_ownercompany, sy_typetext
url: co_url
url_id: co_url
validfrom: pd_artprice
validto: pd_artprice
vat_coa: gl_chartofaccount
vat_id: gl_chartofaccount, pd_productgroup, pd_vat
vattotal: so_orderhead
weight: so_delivery
year: gl_repcolumn
yourref: so_orderhead
ytd: gl_repcolumn

Index

Database freemoney
Fields in freemoney
Functions in freemoney
List of functions
List of tables
List of views
Table ar_contact
Table ar_custacct
Table ar_journal
Table ar_payment
Table co_address
Table co_addresstype
Table co_businesssector
Table co_company
Table co_contact
Table co_contactnotes
Table co_contactselect
Table co_contacttype
Table co_country
Table co_creditterms
Table co_custgrp
Table co_customer
Table co_customerresponsibility
Table co_customertype
Table co_deliveryterms
Table co_freightcode
Table co_paymentterms
Table co_person
Table co_postalcodes
Table co_selection
Table co_telephone
Table co_telephonetype
Table co_url
Table co_urltype
Table gl_accounttype
Table gl_acctgrid
Table gl_acctname
Table gl_amounttype
Table gl_balance
Table gl_batch
Table gl_chartofaccount
Table gl_currency
Table gl_currencyrate
Table gl_currencytype
Table gl_dimension
Table gl_journal
Table gl_repaccount
Table gl_repcolumn
Table gl_report
Table gl_system
Table pd_arthist
Table pd_article
Table pd_artprice
Table pd_deliverytype
Table pd_inventory
Table pd_pricetype
Table pd_productgroup
Table pd_vat
Table so_delivery
Table so_invoice
Table so_invoicebatch
Table so_invoicetemplate
Table so_invtplchoice
Table so_orderhead
Table so_orderline
Table su_event
Table su_eventrelease
Table su_eventtime
Table su_subscriptarticle
Table su_subscription
Table sy_ownercompany
Table sy_typetext
Tables in freemoney
Top
Views in freemoney