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
Totals per month for customer
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 |
AR information about the customer.
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 |
All transactions for the customer
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 |
Payments
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 |
All addresses are placed here
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 |
COMMENT ON TABLE co_addresstype IS '';
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 ''; |
Some kind of business information. Idea not developed.
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 |
Company information
Field | Type | Description |
---|---|---|
contact_id | int4 | not null Reference to contact |
name | varchar | Company name |
regnr | varchar | Officiel registration number |
attention | varchar | Contact person |
updated | timestamp | not null default 'now' Timestamp for last update of this row |
Central table for all contact information
Every contact, independent of type (customer, employee, lessor...) will be here
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 |
Contact notes.
The idea is to have an unlimited number of notes per customer, person or company
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 for holding the selected contacts.
The idea is to store selected customers until they're actively released by the user
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 |
Different contact types; customer, owner...
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 ''; |
Country information
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 |
Credit terms
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 |
Customer group. Defines third dimension in accounting.
Could also be used to define discount levels
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 |
Customers
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 |
Forgot what this table is for...
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 |
COMMENT ON TABLE co_customertype IS '';
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 ''; |
Delivery terms
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 |
Freight code
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 |
Payment terms
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 |
Information about a 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 ''; |
Postal (zip) codes
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 |
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
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 |
Telephone numbers
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 |
COMMENT ON TABLE co_telephonetype IS '';
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 ''; |
Internet addresses
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 |
COMMENT ON TABLE co_urltype IS '';
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 ''; |
COMMENT ON TABLE gl_accounttype IS '';
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 ''; |
Grid referenced by all dimensions. Currently 3: Account, product and customer
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 |
Names for entries in chartofaccount, productgroup and customer group
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 |
COMMENT ON TABLE gl_amounttype IS '';
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 ''; |
Balance for period
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 |
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
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 |
Chart of account
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 |
Currency information
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 |
The currency rate towards the system rate
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 |
COMMENT ON TABLE gl_currencytype IS '';
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 ''; |
COMMENT ON TABLE gl_dimension IS '';
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 ''; |
A journal entry is the lowest atomic element of accounting
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 |
Information about what the individual line contains
Should contain a line number too. And some way of picking individual acctgrid elements as you like
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 |
Information about the individual columns
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 |
There's a need for an easy to do report manager for generel ledger.
This idea is still not developed.
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 |
COMMENT ON TABLE gl_system IS '';
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 ''; |
COMMENT ON TABLE pd_arthist IS '';
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 ''; |
COMMENT ON TABLE pd_article IS '';
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 ''; |
COMMENT ON TABLE pd_artprice IS '';
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 ''; |
COMMENT ON TABLE pd_deliverytype IS '';
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 ''; |
COMMENT ON TABLE pd_inventory IS '';
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 ''; |
COMMENT ON TABLE pd_pricetype IS '';
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 ''; |
COMMENT ON TABLE pd_productgroup IS '';
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 ''; |
COMMENT ON TABLE pd_vat IS '';
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 ''; |
COMMENT ON TABLE so_delivery IS '';
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 ''; |
COMMENT ON TABLE so_invoice IS '';
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 ''; |
COMMENT ON TABLE so_invoicebatch IS '';
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 ''; |
COMMENT ON TABLE so_invoicetemplate IS '';
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 ''; |
COMMENT ON TABLE so_invtplchoice IS '';
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 ''; |
COMMENT ON TABLE so_orderhead IS '';
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 ''; |
COMMENT ON TABLE so_orderline IS '';
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 ''; |
Central event table
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. |
Release related events
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. |
Time related events
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. |
Subscribed articles
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. |
Subscriptions, linking contact with events
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. |
System owner
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 |
COMMENT ON TABLE sy_typetext IS '';
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 ''; |