Dumped on 2017-02-16

Index of database - LedgerSMB 1.5.3

Table: ac_tax_form

Mapping journal_line to country_tax_form for reporting purposes.

ac_tax_form Structure
F-Key Name Type Description
acc_trans.entry_id entry_id integer PRIMARY KEY
reportable boolean

Index - Schema public

Table: acc_trans

This table stores line items for financial transactions. Please note that payments in 1.3 are not full-fledged transactions.

acc_trans Structure
F-Key Name Type Description
transactions.id trans_id integer NOT NULL
account.id chart_id integer NOT NULL
amount numeric NOT NULL
transdate date DEFAULT ('now'::text)::date
source text

Document Source identifier for individual line items, usually used for payments.
cleared boolean DEFAULT false
fx_transaction boolean DEFAULT false

When 'f', indicates that the amount column states the amount in the currency as specified in the associated ar, ap, payment or gl record. When 't', indicates that the amount column states the difference between the foreighn currency amount and the base amount so that their sum equals the base amount.
memo text
invoice.id invoice_id integer
approved boolean DEFAULT true
cleared_on date
reconciled_on date
voucher.id voucher_id integer
entry_id serial PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

acc_trans_transdate_year_idx transdate, date_part('YEAR'::text, transdate)) WHERE (transdate IS NOT NULL acc_trans_voucher_id_idx voucher_id

Index - Schema public

Table: account

This table stores the main account info.

account Structure
F-Key Name Type Description
id serial UNIQUE NOT NULL
accno text PRIMARY KEY
description text
is_temp boolean NOT NULL DEFAULT false

Only affects equity accounts. If set, close at end of year.
category character(1) NOT NULL

A=asset,L=liability,Q=Equity,I=Income,E=expense
gifi_accno text
account_heading.id heading integer NOT NULL
contra boolean NOT NULL DEFAULT false
tax boolean NOT NULL DEFAULT false
obsolete boolean NOT NULL DEFAULT false

 

account Constraints
Name Constraint
account_category_check CHECK ((category = ANY (ARRAY['A'::bpchar, 'L'::bpchar, 'Q'::bpchar, 'I'::bpchar, 'E'::bpchar])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: account_checkpoint

This table holds account balances at various dates. Transactions MUST NOT be posted prior to the latest end_date in this table, and no unapproved transactions (vouchers or drafts) can remain in the closed period.

account_checkpoint Structure
F-Key Name Type Description
end_date date PRIMARY KEY
account.id account_id integer PRIMARY KEY
amount numeric NOT NULL
id serial UNIQUE NOT NULL
debits numeric
credits numeric

Index - Schema public

Table: account_heading

This table holds the account headings in the system. Each account must belong to a heading, and a heading can belong to another heading. In this way it is possible to nest accounts for reporting purposes.

account_heading Structure
F-Key Name Type Description
id serial UNIQUE NOT NULL
accno text PRIMARY KEY
account_heading.id parent_id integer
description text
category character(1)

Same as the column account.category, except that if NULL the category is automatically derived from the linked accounts.

 

account_heading Constraints
Name Constraint
account_heading_category_check CHECK ((category = ANY (ARRAY['A'::bpchar, 'L'::bpchar, 'Q'::bpchar, 'I'::bpchar, 'E'::bpchar])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

View: account_heading_derived_category

Lists for each row the derived category for each heading, based on the categories of the linked accounts.

account_heading_derived_category Structure
F-Key Name Type Description
id integer
accno text
description text
parent_id integer
original_category character(1)
asset_count bigint
liability_count bigint
expense_count bigint
income_count bigint
equity_count bigint
derived_category text
category bpchar 
SELECT derivation.id
,
    derivation.accno
,
    derivation.description
,
    derivation.parent_id
,
    derivation.original_category
,
    derivation.asset_count
,
    derivation.liability_count
,
    derivation.expense_count
,
    derivation.income_count
,
    derivation.equity_count
,
    derivation.derived_category
,
    COALESCE
(derivation.original_category
     , (derivation.derived_category)::bpchar
) AS category
   
FROM (
SELECT category_counts.id
     ,
            category_counts.accno
     ,
            category_counts.description
     ,
            category_counts.parent_id
     ,
            category_counts.original_category
     ,
            category_counts.asset_count
     ,
            category_counts.liability_count
     ,
            category_counts.expense_count
     ,
            category_counts.income_count
     ,
            category_counts.equity_count
     ,
                CASE
                    WHEN 
     (category_counts.equity_count > 0) THEN 'Q'::text
                    WHEN 
     (
           (category_counts.income_count > 0)
         AND (category_counts.expense_count > 0)
     ) THEN 'Q'::text
                    WHEN 
     (
           (category_counts.asset_count > 0)
         AND (category_counts.liability_count > 0)
     ) THEN 'Q'::text
                    WHEN 
     (category_counts.asset_count > 0) THEN 'A'::text
                    WHEN 
     (category_counts.liability_count > 0) THEN 'L'::text
                    WHEN 
     (category_counts.expense_count > 0) THEN 'E'::text
                    WHEN 
     (category_counts.income_count > 0) THEN 'I'::text
                    ELSE NULL::text
                END AS derived_category
           
  FROM (
      SELECT ah.id
           ,
                    ah.accno
           ,
                    ah.description
           ,
                    ah.parent_id
           ,
                    ah.category AS original_category
           ,
                    count
           (
                        CASE
                            WHEN 
                 (acc.category = 'A'::bpchar) THEN acc.category
                            ELSE NULL::bpchar
                        END
           ) AS asset_count
           ,
                    count
           (
                        CASE
                            WHEN 
                 (acc.category = 'L'::bpchar) THEN acc.category
                            ELSE NULL::bpchar
                        END
           ) AS liability_count
           ,
                    count
           (
                        CASE
                            WHEN 
                 (acc.category = 'E'::bpchar) THEN acc.category
                            ELSE NULL::bpchar
                        END
           ) AS expense_count
           ,
                    count
           (
                        CASE
                            WHEN 
                 (acc.category = 'I'::bpchar) THEN acc.category
                            ELSE NULL::bpchar
                        END
           ) AS income_count
           ,
                    count
           (
                        CASE
                            WHEN 
                 (acc.category = 'Q'::bpchar) THEN acc.category
                            ELSE NULL::bpchar
                        END
           ) AS equity_count
                   
        FROM (
                 (account_heading_descendant ahd
                     
                    JOIN account_heading ah 
                      ON (
                             (ahd.id = ah.id)
                       )
                 )
                     
         LEFT JOIN account acc 
                ON (
                       (ahd.descendant_id = acc.heading)
                 )
           )
                  
    GROUP BY ah.id
           , ah.accno
           , ah.description
           , ah.parent_id
           , ah.category
     ) category_counts
) derivation;

Index - Schema public

View: account_heading_descendant

Returns rows for each heading listing its immediate children, children of children, etc., etc. This is primarily practical when calculating subtotals for PNL and B/S headings.

account_heading_descendant Structure
F-Key Name Type Description
id integer
level integer
descendant_id integer
accno text
descendant_accno text 
 WITH RECURSIVE account_headings AS 
(
         
SELECT account_heading.id
     ,
            1 AS level
     ,
            account_heading.id AS descendant_id
     ,
            account_heading.accno
     ,
            account_heading.accno AS descendant_accno
           
  FROM account_heading
        
UNION ALL
         
SELECT at.id
     ,
            
     (at.level + 1) AS level
     ,
            ah.id AS descendant_id
     ,
            at.accno
     ,
            ah.accno AS descendant_accno
           
  FROM (account_heading ah
             
        JOIN account_headings at 
          ON (
                 (ah.parent_id = at.descendant_id)
           )
     )
        
)
 
SELECT account_headings.id
,
    account_headings.level
,
    account_headings.descendant_id
,
    account_headings.accno
,
    account_headings.descendant_accno
   
FROM account_headings;

Index - Schema public

Table: account_heading_translation

Translations for account heading descriptions.

account_heading_translation Structure
F-Key Name Type Description
account_heading.id trans_id integer PRIMARY KEY
language_code character varying(6) PRIMARY KEY
description text

Table account_heading_translation Inherits translation,

Index - Schema public

View: account_heading_tree

Returns in the 'path' field an array which contains the path of the heading to its associated root.

account_heading_tree Structure
F-Key Name Type Description
id integer
accno text
description text
level integer
path integer[] 
 WITH RECURSIVE account_headings AS 
(
         
SELECT account_heading.id
     ,
            account_heading.accno
     ,
            account_heading.description
     ,
            1 AS level
     ,
            ARRAY[account_heading.id] AS path
           
  FROM account_heading
          
 WHERE (account_heading.parent_id IS NULL)
        
UNION ALL
         
SELECT ah.id
     ,
            ah.accno
     ,
            ah.description
     ,
            
     (at.level + 1) AS level
     ,
            array_append
     (at.path
           , ah.id
     ) AS path
           
  FROM (account_heading ah
             
        JOIN account_headings at 
          ON (
                 (ah.parent_id = at.id)
           )
     )
        
)
 
SELECT account_headings.id
,
    account_headings.accno
,
    account_headings.description
,
    account_headings.level
,
    account_headings.path
   
FROM account_headings;

Index - Schema public

Table: account_link

account_link Structure
F-Key Name Type Description
account.id account_id integer PRIMARY KEY
account_link_description.description description text PRIMARY KEY

Index - Schema public

Table: account_link_description

This is a lookup table which provide basic information as to categories and dropdowns of accounts. In general summary accounts cannot belong to more than one category (an AR summary account cannot appear in other dropdowns for example). Custom fields are not overwritten when the account is edited from the front-end.

account_link_description Structure
F-Key Name Type Description
description text PRIMARY KEY
summary boolean NOT NULL
custom boolean NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: account_translation

Translations for account descriptions.

account_translation Structure
F-Key Name Type Description
account.id trans_id integer PRIMARY KEY
language_code character varying(6) PRIMARY KEY
description text

Table account_translation Inherits translation,

Index - Schema public

Table: ap

Summary/header information for AP transactions and vendor invoices. Note that some constraints here are hard to enforce because we haven not gotten to rewriting the relevant code here. HV TODO drop entity_id

ap Structure
F-Key Name Type Description
transactions.id id integer PRIMARY KEY DEFAULT nextval('id'::regclass)
invnumber text

Text identifier for the invoice. Must be unique.
transdate date DEFAULT ('now'::text)::date
entity.id entity_id integer
taxincluded boolean DEFAULT false
amount numeric

This stores the total amount (including taxes) for the transaction.
netamount numeric

Total amount excluding taxes for the transaction.
paid numeric
datepaid date
duedate date
invoice boolean DEFAULT false

True if the transaction tracks goods/services purchase using the invoice table. False otherwise.
ordnumber text

Order Number
curr character(3)

3 letters to identify the currency.
notes text

These notes are displayed on the invoice when printed or emailed
entity_employee.entity_id person_id integer

Person who created the transaction
till character varying(20)
quonumber text

Quotation Number
intnotes text

These notes are not displayed when the invoice is printed or emailed and may be updated without reposting hte invocie.
shipvia text
language_code character varying(6)
ponumber text

Purchase Order Number
shippingpoint text
on_hold boolean DEFAULT false
approved boolean DEFAULT true

Only show in financial reports if true.
reverse boolean DEFAULT false

If true numbers are displayed after multiplying by -1
terms smallint
description text
force_closed boolean

Not exposed to the UI, but can be set to prevent an invoice from showing up for payment or in outstanding reports.
crdate date
is_return boolean DEFAULT false
entity_credit_account.id entity_credit_account integer NOT NULL

reference for the vendor account used.

 

ap Constraints
Name Constraint
ap_check CHECK ((((amount IS NULL) AND (curr IS NULL)) OR ((amount IS NOT NULL) AND (curr IS NOT NULL))))

Index - Schema public

Table: ar

Summary/header information for AR transactions and sales invoices. Note that some constraints here are hard to enforce because we haven not gotten to rewriting the relevant code here. HV TODO drop entity_id

ar Structure
F-Key Name Type Description
transactions.id id integer PRIMARY KEY DEFAULT nextval('id'::regclass)
invnumber text

Text identifier for the invoice. Must be unique.
transdate date DEFAULT ('now'::text)::date
entity.id entity_id integer
taxincluded boolean
amount numeric

This stores the total amount (including taxes) for the transaction.
netamount numeric

Total amount excluding taxes for the transaction.
paid numeric
datepaid date
duedate date
invoice boolean DEFAULT false

True if the transaction tracks goods/services purchase using the invoice table. False otherwise.
shippingpoint text
terms smallint
notes text

These notes are displayed on the invoice when printed or emailed
curr character(3)

3 letters to identify the currency.
ordnumber text

Order Number
entity_employee.entity_id person_id integer

Person who created the transaction
till character varying(20)
quonumber text

Quotation Number
intnotes text

These notes are not displayed when the invoice is printed or emailed and may be updated without reposting hte invocie.
shipvia text
language_code character varying(6)
ponumber text

Purchase Order Number
on_hold boolean DEFAULT false
reverse boolean DEFAULT false

If true numbers are displayed after multiplying by -1
approved boolean DEFAULT true

Only show in financial reports if true.
entity_credit_account.id entity_credit_account integer NOT NULL

reference for the customer account used.
force_closed boolean

Not exposed to the UI, but can be set to prevent an invoice from showing up for payment or in outstanding reports.
description text
is_return boolean DEFAULT false
crdate date
setting_sequence text

 

ar Constraints
Name Constraint
ar_check CHECK ((((amount IS NULL) AND (curr IS NULL)) OR ((amount IS NOT NULL) AND (curr IS NOT NULL))))
ar_check1 CHECK (((invnumber IS NOT NULL) OR (NOT approved)))

Index - Schema public

Table: assembly

Holds mapping for parts that are members of assemblies.

assembly Structure
F-Key Name Type Description
parts.id id integer PRIMARY KEY

This is the id of the assembly the part is being mapped to.
parts.id parts_id integer PRIMARY KEY

ID of part that is a member of the assembly.
qty numeric
bom boolean
adj boolean
assembly_id_key id

Index - Schema public

Table: asset_class

The account fields here set the defaults for the individual asset items. They are non-authoritative.

asset_class Structure
F-Key Name Type Description
id serial UNIQUE NOT NULL
label text PRIMARY KEY
account.id asset_account_id integer
account.id dep_account_id integer
asset_dep_method.id method integer

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: asset_dep_method

Stores asset depreciation methods, and their relevant stored procedures. The fixed asset system is such depreciation methods can be plugged in via this table.

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

These are keyed to specific stored procedures. Currently only "straight_line" is supported
sproc text UNIQUE NOT NULL

The sproc mentioned here is a stored procedure which must have the following arguments: (in_asset_ids int[], in_report_date date, in_report_id int). Here in_asset_ids are the assets to be depreciated, in_report_date is the date of the report, and in_report_id is the id of the report. The sproc MUST insert the relevant lines into asset_report_line.
unit_label text NOT NULL
short_name text UNIQUE NOT NULL
asset_unit_class.id unit_class integer NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: asset_disposal_method

asset_disposal_method Structure
F-Key Name Type Description
label text PRIMARY KEY
id serial UNIQUE NOT NULL
multiple integer
short_label character(1)

 

asset_disposal_method Constraints
Name Constraint
asset_disposal_method_multiple_check CHECK ((multiple = ANY (ARRAY[1, 0, '-1'::integer])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: asset_item

Stores details of asset items. The account fields here are authoritative, while the ones in the asset_class table are defaults.

asset_item Structure
F-Key Name Type Description
id serial PRIMARY KEY
description text
tag text UNIQUE#1 NOT NULL

This can be plugged into other routines to generate it automatically via ALTER TABLE .... SET DEFAULT.....
purchase_value numeric
salvage_value numeric
usable_life numeric
purchase_date date NOT NULL
start_depreciation date NOT NULL
warehouse.id location_id integer
business_unit.id department_id integer
eca_invoice.journal_id invoice_id integer
account.id asset_account_id integer
account.id dep_account_id integer
account.id exp_account_id integer
asset_item.id obsolete_by integer UNIQUE#1
asset_class.id asset_class_id integer

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: asset_note

asset_note Structure
F-Key Name Type Description
id integer NOT NULL DEFAULT nextval('note_id_seq'::regclass)
note_class integer NOT NULL DEFAULT 4
note text NOT NULL
vector tsvector NOT NULL DEFAULT ''::tsvector
created timestamp without time zone NOT NULL DEFAULT now()
created_by text DEFAULT "session_user"()
asset_item.id ref_key integer NOT NULL
subject text

Table asset_note Inherits note,

 

asset_note Constraints
Name Constraint
asset_note_note_class_check CHECK ((note_class = 4))

Index - Schema public

Table: asset_report

Asset reports are discrete sets of depreciation or disposal transctions, and each one may be turned into no more than one GL transaction.

asset_report Structure
F-Key Name Type Description
id serial PRIMARY KEY
report_date date
gl.id gl_id bigint UNIQUE
asset_class.id asset_class bigint
asset_report_class.id report_class integer
entity.id entered_by bigint NOT NULL DEFAULT person__get_my_entity_id()
entity.id approved_by bigint
entered_at timestamp without time zone DEFAULT now()
approved_at timestamp without time zone
depreciated_qty numeric
dont_approve boolean DEFAULT false
submitted boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: asset_report_class

By default only four types of asset reports are supported. In the future others may be added. Please correspond on the list before adding more types.

asset_report_class Structure
F-Key Name Type Description
id integer UNIQUE NOT NULL
class text PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: asset_report_line

asset_report_line Structure
F-Key Name Type Description
asset_item.id asset_id bigint PRIMARY KEY
asset_report.id report_id bigint PRIMARY KEY
amount numeric
business_unit.id department_id integer

In case assets are moved between departments, we have to store this here.
warehouse.id warehouse_id integer

Index - Schema public

Table: asset_rl_to_disposal_method

Maps disposal method to line items in the asset disposal report.

asset_rl_to_disposal_method Structure
F-Key Name Type Description
asset_report.id report_id integer PRIMARY KEY
asset_item.id asset_id integer PRIMARY KEY
asset_disposal_method.id disposal_method_id integer PRIMARY KEY
percent_disposed numeric

Index - Schema public

Table: asset_unit_class

asset_unit_class Structure
F-Key Name Type Description
id integer UNIQUE NOT NULL
class text PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: audittrail

This stores information on who entered or updated rows in the ar, ap, or gl tables.

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.entity_id person_id integer NOT NULL
entry_id bigserial PRIMARY KEY
audittrail_trans_id_key trans_id

Index - Schema public

Table: batch

Stores batch header info. Batches are groups of vouchers that are posted together.

batch Structure
F-Key Name Type Description
id serial PRIMARY KEY
batch_class.id batch_class_id integer NOT NULL

Note that this field is largely used for sorting the vouchers. A given batch is NOT restricted to this type.
control_code text NOT NULL
description text
default_date date NOT NULL
approved_on date
entity_employee.entity_id approved_by integer
entity_employee.entity_id created_by integer
session.session_id locked_by integer
created_on date DEFAULT now()

 

batch Constraints
Name Constraint
batch_control_code_check CHECK ((length(control_code) > 0))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: batch_class

These values are hard-coded. Please coordinate before adding standard values. Values from 900 to 999 are reserved for local use.

batch_class Structure
F-Key Name Type Description
id serial UNIQUE NOT NULL
class character varying PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: bu_class_to_module

bu_class_to_module Structure
F-Key Name Type Description
business_unit_class.id bu_class_id integer PRIMARY KEY
lsmb_module.id module_id integer PRIMARY KEY

Index - Schema public

Table: budget_info

budget_info Structure
F-Key Name Type Description
id serial UNIQUE NOT NULL
start_date date NOT NULL
end_date date NOT NULL
reference text PRIMARY KEY
description text NOT NULL
entity.id entered_by integer NOT NULL DEFAULT person__get_my_entity_id()
entity.id approved_by integer
entity.id obsolete_by integer
entered_at timestamp without time zone NOT NULL DEFAULT now()
approved_at timestamp without time zone
obsolete_at timestamp without time zone

 

budget_info Constraints
Name Constraint
budget_info_check CHECK ((start_date < end_date))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: budget_line

budget_line Structure
F-Key Name Type Description
budget_info.id budget_id integer PRIMARY KEY
account.id account_id integer PRIMARY KEY
description text
amount numeric NOT NULL

Index - Schema public

Table: budget_note

budget_note Structure
F-Key Name Type Description
id integer PRIMARY KEY DEFAULT nextval('note_id_seq'::regclass)
note_class integer NOT NULL DEFAULT 6
note text NOT NULL
vector tsvector NOT NULL DEFAULT ''::tsvector
created timestamp without time zone NOT NULL DEFAULT now()
created_by text DEFAULT "session_user"()
budget_info.id ref_key integer NOT NULL
subject text

Table budget_note Inherits note,

 

budget_note Constraints
Name Constraint
budget_note_note_class_check CHECK ((note_class = 6))

Index - Schema public

Table: budget_to_business_unit

budget_to_business_unit Structure
F-Key Name Type Description
budget_info.id budget_id integer UNIQUE PRIMARY KEY
business_unit.id bu_id integer NOT NULL
business_unit_class.id bu_class integer PRIMARY KEY

Index - Schema public

Table: business

Groups of Customers assigned joint discounts.

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

Index - Schema public

Table: business_unit

Tracks Projects, Departments, Funds, Etc.

business_unit Structure
F-Key Name Type Description
id serial UNIQUE#1 PRIMARY KEY
business_unit_class.id class_id integer UNIQUE#2 UNIQUE#1 NOT NULL
control_code text UNIQUE#2
description text
start_date date
end_date date
business_unit.id parent_id integer
entity_credit_account.id credit_id integer

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: business_unit_ac

business_unit_ac Structure
F-Key Name Type Description
acc_trans.entry_id entry_id integer PRIMARY KEY