Dumped on 2017-01-20

Index of database - omscore129

Schema acc

Table: acc.bank_accounts

acc.bank_accounts Structure
F-Key Name Type Description
acc.financial_accounts.financial_account_id financial_account_id uuid PRIMARY KEY
bank_account_bsb text NOT NULL
bank_account_number text NOT NULL
bank_account_holder text
bank_account_organisation_name text

Index - Schema acc

Table: acc.bpay_billers

acc.bpay_billers Structure
F-Key Name Type Description
acc.financial_accounts.financial_account_id financial_account_id uuid PRIMARY KEY
bpay_biller_active boolean
bpay_biller_code text NOT NULL
bpay_biller_payment_method text NOT NULL
bpay_biller_crn_seed_lower_bound integer NOT NULL
bpay_biller_crn_seed_upper_bound integer

Index - Schema acc

Table: acc.credit_card_types

acc.credit_card_types Structure
F-Key Name Type Description
credit_card_type_id integer PRIMARY KEY
credit_card_type_description text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acc

Table: acc.debtors_ledger

acc.debtors_ledger Structure
F-Key Name Type Description
debtors_ledger_id uuid PRIMARY KEY
debtors_ledger_parent_id uuid
debtors_ledger_locked boolean DEFAULT false
debtors_ledger_description text NOT NULL
debtors_ledger_date date NOT NULL
debtors_ledger_amount integer NOT NULL
debtors_ledger_details text
debtors_ledger_auto_post boolean NOT NULL DEFAULT true
user_account_full_name text NOT NULL
acc.financial_cost_centres.financial_cost_centre_id financial_cost_centre_id uuid NOT NULL
acc.financial_accounts.financial_account_id financial_account_debit_id uuid NOT NULL
acc.financial_accounts.financial_account_id financial_account_credit_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

debtors_ledger_credit_id_idx financial_account_credit_id debtors_ledger_debit_id_idx financial_account_debit_id debtors_ledger_debtors_ledger_parent_id_idx debtors_ledger_parent_id debtors_ledger_financial_cost_centre_id_idx financial_cost_centre_id

Index - Schema acc

Table: acc.debtors_ledger_offsets

acc.debtors_ledger_offsets Structure
F-Key Name Type Description
debtors_ledger_offset_id uuid PRIMARY KEY
debtors_ledger_offset_amount integer NOT NULL
debtors_ledger_offset_timestamp timestamp without time zone NOT NULL
user_account_full_name text NOT NULL
acc.debtors_ledger.debtors_ledger_id debtors_ledger_debit_id uuid NOT NULL
acc.debtors_ledger.debtors_ledger_id debtors_ledger_credit_id uuid NOT NULL

Tables referencing this one via Foreign Key Constraints:

debtors_ledger_offsets_debtors_ledger_credit_id_idx debtors_ledger_credit_id debtors_ledger_offsets_debtors_ledger_debit_id_idx debtors_ledger_debit_id

Index - Schema acc

Table: acc.debtors_ledger_offsets_reversals_journal

acc.debtors_ledger_offsets_reversals_journal Structure
F-Key Name Type Description
id_pk uuid PRIMARY KEY
debtors_ledger_offsets_reversals_journal_reason text
acc.debtors_ledger_offsets.debtors_ledger_offset_id debtors_ledger_offset_source_id uuid UNIQUE NOT NULL
acc.debtors_ledger_offsets.debtors_ledger_offset_id debtors_ledger_offset_reversal_id uuid UNIQUE NOT NULL

Index - Schema acc

Table: acc.debtors_ledger_reversals_journal

acc.debtors_ledger_reversals_journal Structure
F-Key Name Type Description
id_pk uuid PRIMARY KEY
debtors_ledger_reversals_journal_reason text
acc.debtors_ledger.debtors_ledger_id debtors_ledger_source_id uuid UNIQUE NOT NULL
acc.debtors_ledger.debtors_ledger_id debtors_ledger_reversal_id uuid UNIQUE NOT NULL

Index - Schema acc

Table: acc.financial_account_attribute_associations

acc.financial_account_attribute_associations Structure
F-Key Name Type Description
id_pk uuid PRIMARY KEY
acc.financial_accounts.financial_account_id financial_account_id uuid NOT NULL
acc.financial_account_attributes.financial_account_attribute_id financial_account_attribute_id uuid NOT NULL

Index - Schema acc

Table: acc.financial_account_attributes

acc.financial_account_attributes Structure
F-Key Name Type Description
financial_account_attribute_id uuid PRIMARY KEY
financial_account_attribute_description text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acc

Table: acc.financial_accounts

acc.financial_accounts Structure
F-Key Name Type Description
financial_account_id uuid PRIMARY KEY UNIQUE#1
financial_account_name text NOT NULL
financial_account_user_selectable boolean NOT NULL DEFAULT true
financial_account_user_defined boolean NOT NULL DEFAULT false
financial_account_auto_offset boolean NOT NULL
financial_account_closed date
acc.posting_account_types.posting_account_type_id posting_account_type_id uuid NOT NULL
org.organisations.organisation_id organisation_id uuid UNIQUE#2 UNIQUE#3 UNIQUE#1 NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
financial_account_organisation_sequence_value integer UNIQUE#2
financial_account_reference text UNIQUE#3
keywords text NOT NULL DEFAULT ''::text
system_keywords text NOT NULL DEFAULT ''::text

Tables referencing this one via Foreign Key Constraints:

financial_accounts_keywords_trgm_idx keywords gin_trgm_ops

Index - Schema acc

Table: acc.financial_cost_centres

acc.financial_cost_centres Structure
F-Key Name Type Description
financial_cost_centre_id uuid PRIMARY KEY
financial_cost_centre_name text NOT NULL
org.organisations.organisation_id organisation_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema acc

Table: acc.general_ledger_account_types

acc.general_ledger_account_types Structure
F-Key Name Type Description
general_ledger_account_type_id integer PRIMARY KEY
general_ledger_account_type_name text NOT NULL
general_ledger_account_type_description text
general_ledger_account_type_display_order smallint NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acc

Table: acc.invoice_items

acc.invoice_items Structure
F-Key Name Type Description
invoice_item_id uuid PRIMARY KEY
acc.invoices.financial_account_id#1 financial_account_id uuid UNIQUE#1 NOT NULL
acc.debtors_ledger.debtors_ledger_id debtors_ledger_id uuid UNIQUE#1 NOT NULL
acc.invoices.invoice_id#1 invoice_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
invoice_item_notifiable boolean NOT NULL DEFAULT true
invoice_items_invoice_id_financial_account_id_idx invoice_id, financial_account_id

Index - Schema acc

Table: acc.invoice_notifications

acc.invoice_notifications Structure
F-Key Name Type Description
invoice_notification_id uuid PRIMARY KEY
acc.invoices.invoice_id invoice_id uuid NOT NULL
invoice_notification_method text NOT NULL
invoice_notification_recipient text NOT NULL
invoice_notification_sent timestamp with time zone NOT NULL DEFAULT now()
user_account_full_name text NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
invoice_notifications_invoice_id_idx invoice_id

Index - Schema acc

Table: acc.invoices

acc.invoices Structure
F-Key Name Type Description
invoice_id uuid UNIQUE#2 PRIMARY KEY
invoice_reference text UNIQUE#1 NOT NULL
invoice_issued date NOT NULL
acc.financial_accounts.financial_account_id#1 financial_account_id uuid UNIQUE#2 NOT NULL
acc.financial_accounts.organisation_id#1 organisation_id uuid UNIQUE#3 UNIQUE#1 NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
invoice_notifiable boolean NOT NULL DEFAULT true
user_account_full_name text NOT NULL
invoice_organisation_sequence_value integer UNIQUE#3

Tables referencing this one via Foreign Key Constraints:

invoices_financial_account_id_idx financial_account_id invoices_financial_account_id_organisation_id_idx financial_account_id, organisation_id

Index - Schema acc

Table: acc.payment_methods

acc.payment_methods Structure
F-Key Name Type Description
payment_method_id integer PRIMARY KEY
payment_method_description text NOT NULL
payment_method_display_order smallint NOT NULL DEFAULT 1

Tables referencing this one via Foreign Key Constraints:

Index - Schema acc

Table: acc.posting_account_types

acc.posting_account_types Structure
F-Key Name Type Description
posting_account_type_id uuid PRIMARY KEY
posting_account_type_name text NOT NULL
posting_account_type_display_order smallint NOT NULL
acc.general_ledger_account_types.general_ledger_account_type_id general_ledger_account_type_id integer NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acc

Table: acc.posting_accounts

acc.posting_accounts Structure
F-Key Name Type Description
posting_account_id uuid PRIMARY KEY
acc.posting_account_types.posting_account_type_id posting_account_type_id uuid NOT NULL
acc.financial_cost_centres.financial_cost_centre_id financial_cost_centre_id uuid NOT NULL
general_ledger_code text
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Index - Schema acc

Table: acc.receipt_items

acc.receipt_items Structure
F-Key Name Type Description
receipt_item_id uuid PRIMARY KEY
receipt_item_receipt_number text
receipt_item_amount integer NOT NULL
acc.payment_methods.payment_method_id payment_method_id integer NOT NULL
acc.debtors_ledger.debtors_ledger_id debtors_ledger_id uuid NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acc

Table: acc.receipt_items_cheques

acc.receipt_items_cheques Structure
F-Key Name Type Description
receipt_item_cheque_id uuid PRIMARY KEY
receipt_item_cheque_number text
receipt_item_cheque_bsb text
receipt_item_cheque_drawer text
acc.receipt_items.receipt_item_id receipt_item_id uuid NOT NULL

Index - Schema acc

Table: acc.receipt_items_credit_cards

acc.receipt_items_credit_cards Structure
F-Key Name Type Description
receipt_item_credit_card_id uuid PRIMARY KEY
acc.credit_card_types.credit_card_type_id credit_card_type_id integer NOT NULL
acc.receipt_items.receipt_item_id receipt_item_id uuid NOT NULL

Index - Schema acc

View: acc.view_debtors_ledger_amounts

acc.view_debtors_ledger_amounts Structure
F-Key Name Type Description
debtors_ledger_id uuid
financial_account_id uuid
adj_financial_account_id uuid
financial_cost_centre_id uuid
debtors_ledger_date date
debtors_ledger_amount integer 
SELECT dl_signed_amounts.dl_id AS debtors_ledger_id
,
    dl_signed_amounts.fin_acc_id AS financial_account_id
,
    dl_signed_amounts.adj_fin_acc_id AS adj_financial_account_id
,
    dl_signed_amounts.fin_cost_centre_id AS financial_cost_centre_id
,
    dl_signed_amounts.dl_date AS debtors_ledger_date
,
    dl_signed_amounts.dl_signed_amount AS debtors_ledger_amount
   
FROM (
SELECT debtors_ledger.debtors_ledger_id AS dl_id
     ,
            financial_accounts.financial_account_id AS fin_acc_id
     ,
            debtors_ledger.financial_account_credit_id AS adj_fin_acc_id
     ,
            debtors_ledger.financial_cost_centre_id AS fin_cost_centre_id
     ,
            debtors_ledger.debtors_ledger_date AS dl_date
     ,
            debtors_ledger.debtors_ledger_amount AS dl_signed_amount
           
  FROM (acc.financial_accounts
             
        JOIN acc.debtors_ledger 
          ON (
                 (financial_accounts.financial_account_id = debtors_ledger.financial_account_debit_id)
           )
     )
        
UNION ALL
         
SELECT debtors_ledger.debtors_ledger_id AS dl_id
     ,
            financial_accounts.financial_account_id AS fin_acc_id
     ,
            debtors_ledger.financial_account_debit_id AS adj_fin_acc_id
     ,
            debtors_ledger.financial_cost_centre_id AS fin_cost_centre_id
     ,
            debtors_ledger.debtors_ledger_date AS dl_date
     ,
            
     (- debtors_ledger.debtors_ledger_amount) AS dl_signed_amount
           
  FROM (acc.financial_accounts
             
        JOIN acc.debtors_ledger 
          ON (
                 (financial_accounts.financial_account_id = debtors_ledger.financial_account_credit_id)
           )
     )
) dl_signed_amounts;

Index - Schema acc

View: acc.view_debtors_ledger_balances

acc.view_debtors_ledger_balances Structure
F-Key Name Type Description
debtors_ledger_id uuid
financial_account_id uuid
adj_financial_account_id uuid
financial_cost_centre_id uuid
debtors_ledger_date date
debtors_ledger_amount integer
debtors_ledger_balance bigint 
SELECT dl_signed_amounts.dl_id AS debtors_ledger_id
,
    dl_signed_amounts.fin_acc_id AS financial_account_id
,
    dl_signed_amounts.adj_fin_acc_id AS adj_financial_account_id
,
    dl_signed_amounts.fin_cost_centre_id AS financial_cost_centre_id
,
    dl_signed_amounts.dl_date AS debtors_ledger_date
,
    dl_signed_amounts.dl_signed_amount AS debtors_ledger_amount
,
    
(dl_signed_amounts.dl_signed_amount +
        CASE
            WHEN 
     (view_total_offsets_summed_by_ledger_id_by_fin_acc_id.total_offset_amount IS NULL) THEN 
     (0)::bigint
            ELSE view_total_offsets_summed_by_ledger_id_by_fin_acc_id.total_offset_amount
        END
) AS debtors_ledger_balance
   
FROM (
     (
      SELECT debtors_ledger.debtors_ledger_id AS dl_id
           ,
            financial_accounts.financial_account_id AS fin_acc_id
           ,
            debtors_ledger.financial_account_credit_id AS adj_fin_acc_id
           ,
            debtors_ledger.financial_cost_centre_id AS fin_cost_centre_id
           ,
            debtors_ledger.debtors_ledger_date AS dl_date
           ,
            debtors_ledger.debtors_ledger_amount AS dl_signed_amount
           
        FROM (acc.financial_accounts
             
              JOIN acc.debtors_ledger 
                ON (
                       (financial_accounts.financial_account_id = debtors_ledger.financial_account_debit_id)
                 )
           )
        
   UNION ALL
         
      SELECT debtors_ledger.debtors_ledger_id AS dl_id
           ,
            financial_accounts.financial_account_id AS fin_acc_id
           ,
            debtors_ledger.financial_account_debit_id AS adj_fin_acc_id
           ,
            debtors_ledger.financial_cost_centre_id AS fin_cost_centre_id
           ,
            debtors_ledger.debtors_ledger_date AS dl_date
           ,
            
           (- debtors_ledger.debtors_ledger_amount) AS dl_signed_amount
           
        FROM (acc.financial_accounts
             
              JOIN acc.debtors_ledger 
                ON (
                       (financial_accounts.financial_account_id = debtors_ledger.financial_account_credit_id)
                 )
           )
     ) dl_signed_amounts
     
LEFT JOIN acc.view_total_offsets_summed_by_ledger_id_by_fin_acc_id 
    ON (
           (
                 (dl_signed_amounts.dl_id = view_total_offsets_summed_by_ledger_id_by_fin_acc_id.debtors_ledger_id)
               AND (dl_signed_amounts.fin_acc_id = view_total_offsets_summed_by_ledger_id_by_fin_acc_id.financial_account_id)
           )
     )
);

Index - Schema acc

View: acc.view_debtors_ledger_balances_v2

acc.view_debtors_ledger_balances_v2 Structure
F-Key Name Type Description
debtors_ledger_id uuid
financial_account_id uuid
adj_financial_account_id uuid
financial_cost_centre_id uuid
debtors_ledger_date date
debtors_ledger_amount integer
debtors_ledger_balance bigint 
SELECT dl_signed_amounts.dl_id AS debtors_ledger_id
,
    dl_signed_amounts.fin_acc_id AS financial_account_id
,
    dl_signed_amounts.adj_fin_acc_id AS adj_financial_account_id
,
    dl_signed_amounts.fin_cost_centre_id AS financial_cost_centre_id
,
    dl_signed_amounts.dl_date AS debtors_ledger_date
,
    dl_signed_amounts.dl_signed_amount AS debtors_ledger_amount
,
    
(dl_signed_amounts.dl_signed_amount + COALESCE
     (acc.financial_account_debtors_ledger_summed_offsets
           (dl_signed_amounts.fin_acc_id
                 , dl_signed_amounts.dl_id
           )
           , (0)::bigint
     )
) AS debtors_ledger_balance
   
FROM (
SELECT debtors_ledger.debtors_ledger_id AS dl_id
     ,
            financial_accounts.financial_account_id AS fin_acc_id
     ,
            debtors_ledger.financial_account_credit_id AS adj_fin_acc_id
     ,
            debtors_ledger.financial_cost_centre_id AS fin_cost_centre_id
     ,
            debtors_ledger.debtors_ledger_date AS dl_date
     ,
            debtors_ledger.debtors_ledger_amount AS dl_signed_amount
           
  FROM (acc.financial_accounts
             
        JOIN acc.debtors_ledger 
          ON (
                 (financial_accounts.financial_account_id = debtors_ledger.financial_account_debit_id)
           )
     )
        
UNION ALL
         
SELECT debtors_ledger.debtors_ledger_id AS dl_id
     ,
            financial_accounts.financial_account_id AS fin_acc_id
     ,
            debtors_ledger.financial_account_debit_id AS adj_fin_acc_id
     ,
            debtors_ledger.financial_cost_centre_id AS fin_cost_centre_id
     ,
            debtors_ledger.debtors_ledger_date AS dl_date
     ,
            
     (- debtors_ledger.debtors_ledger_amount) AS dl_signed_amount
           
  FROM (acc.financial_accounts
             
        JOIN acc.debtors_ledger 
          ON (
                 (financial_accounts.financial_account_id = debtors_ledger.financial_account_credit_id)
           )
     )
) dl_signed_amounts;

Index - Schema acc

View: acc.view_debtors_ledger_offsets_reversals

acc.view_debtors_ledger_offsets_reversals Structure
F-Key Name Type Description
debtors_ledger_offset_id uuid 
SELECT debtors_ledger_offsets_reversals_journal.debtors_ledger_offset_source_id AS debtors_ledger_offset_id
   
FROM acc.debtors_ledger_offsets_reversals_journal
  
GROUP BY debtors_ledger_offsets_reversals_journal.debtors_ledger_offset_source_id

UNION ALL
 
SELECT debtors_ledger_offsets_reversals_journal.debtors_ledger_offset_reversal_id AS debtors_ledger_offset_id
   
FROM acc.debtors_ledger_offsets_reversals_journal
  
GROUP BY debtors_ledger_offsets_reversals_journal.debtors_ledger_offset_reversal_id;

Index - Schema acc

View: acc.view_debtors_ledger_reversals

acc.view_debtors_ledger_reversals Structure
F-Key Name Type Description
debtors_ledger_id uuid 
SELECT debtors_ledger_reversals_journal.debtors_ledger_source_id AS debtors_ledger_id
   
FROM acc.debtors_ledger_reversals_journal

UNION ALL
 
SELECT debtors_ledger_reversals_journal.debtors_ledger_reversal_id AS debtors_ledger_id
   
FROM acc.debtors_ledger_reversals_journal;

Index - Schema acc

View: acc.view_debtors_ledger_transactions

acc.view_debtors_ledger_transactions Structure
F-Key Name Type Description
debtors_ledger_id uuid
debtors_ledger_description text
debtors_ledger_date date
debtors_ledger_amount_signed integer
debtors_ledger_details text
debtors_ledger_auto_post boolean
created timestamp with time zone
user_account_full_name text
financial_cost_centre_id uuid
financial_account_id uuid
adj_financial_account_id uuid 
SELECT debtors_ledger.debtors_ledger_id
,
    debtors_ledger.debtors_ledger_description
,
    debtors_ledger.debtors_ledger_date
,
    debtors_ledger.debtors_ledger_amount AS debtors_ledger_amount_signed
,
    debtors_ledger.debtors_ledger_details
,
    debtors_ledger.debtors_ledger_auto_post
,
    debtors_ledger.created
,
    debtors_ledger.user_account_full_name
,
    debtors_ledger.financial_cost_centre_id
,
    debtors_ledger.financial_account_debit_id AS financial_account_id
,
    debtors_ledger.financial_account_credit_id AS adj_financial_account_id
   
FROM acc.debtors_ledger
  
WHERE (NOT 
     (debtors_ledger.debtors_ledger_id IN 
           (
            SELECT view_debtors_ledger_reversals.debtors_ledger_id
           
              FROM acc.view_debtors_ledger_reversals
           )
     )
)
UNION ALL
 
SELECT debtors_ledger.debtors_ledger_id
,
    debtors_ledger.debtors_ledger_description
,
    debtors_ledger.debtors_ledger_date
,
    
(- debtors_ledger.debtors_ledger_amount) AS debtors_ledger_amount_signed
,
    debtors_ledger.debtors_ledger_details
,
    debtors_ledger.debtors_ledger_auto_post
,
    debtors_ledger.created
,
    debtors_ledger.user_account_full_name
,
    debtors_ledger.financial_cost_centre_id
,
    debtors_ledger.financial_account_credit_id AS financial_account_id
,
    debtors_ledger.financial_account_debit_id AS adj_financial_account_id
   
FROM acc.debtors_ledger
  
WHERE (NOT 
     (debtors_ledger.debtors_ledger_id IN 
           (
            SELECT view_debtors_ledger_reversals.debtors_ledger_id
           
              FROM acc.view_debtors_ledger_reversals
           )
     )
);

Index - Schema acc

View: acc.view_offsets_by_ledger_id_by_fin_acc_id_by_date

acc.view_offsets_by_ledger_id_by_fin_acc_id_by_date Structure
F-Key Name Type Description
debtors_ledger_id uuid
financial_account_id uuid
offset_amount integer
offset_date date
adj_debtors_ledger_date date 
SELECT offsets.dl_id AS debtors_ledger_id
,
    offsets.fin_acc_id AS financial_account_id
,
    offsets.offset_amount
,
    offsets.debtors_ledger_date AS offset_date
,
    offsets.adj_debtors_ledger_date
   
FROM (
SELECT debit_debtors_ledger.debtors_ledger_id AS dl_id
     ,
            debit_debtors_ledger.financial_account_debit_id AS fin_acc_id
     ,
            debtors_ledger_offsets.debtors_ledger_offset_amount AS offset_amount
     ,
            debit_debtors_ledger.debtors_ledger_date
     ,
            credit_debtors_ledger.debtors_ledger_date AS adj_debtors_ledger_date
           
  FROM (
           (acc.debtors_ledger_offsets
             
              JOIN acc.debtors_ledger debit_debtors_ledger 
                ON (
                       (debtors_ledger_offsets.debtors_ledger_debit_id = debit_debtors_ledger.debtors_ledger_id)
                 )
           )
             
        JOIN acc.debtors_ledger credit_debtors_ledger 
          ON (
                 (debtors_ledger_offsets.debtors_ledger_credit_id = credit_debtors_ledger.debtors_ledger_id)
           )
     )
          
 WHERE (
           (debit_debtors_ledger.financial_account_debit_id = credit_debtors_ledger.financial_account_debit_id)
          OR (debit_debtors_ledger.financial_account_debit_id = credit_debtors_ledger.financial_account_credit_id)
     )
        
UNION ALL
         
SELECT debit_debtors_ledger.debtors_ledger_id AS dl_id
     ,
            debit_debtors_ledger.financial_account_credit_id AS fin_acc_id
     ,
            debtors_ledger_offsets.debtors_ledger_offset_amount AS offset_amount
     ,
            debit_debtors_ledger.debtors_ledger_date
     ,
            credit_debtors_ledger.debtors_ledger_date AS adj_debtors_ledger_date
           
  FROM (
           (acc.debtors_ledger_offsets
             
              JOIN acc.debtors_ledger debit_debtors_ledger 
                ON (
                       (debtors_ledger_offsets.debtors_ledger_debit_id = debit_debtors_ledger.debtors_ledger_id)
                 )
           )
             
        JOIN acc.debtors_ledger credit_debtors_ledger 
          ON (
                 (debtors_ledger_offsets.debtors_ledger_credit_id = credit_debtors_ledger.debtors_ledger_id)
           )
     )
          
 WHERE (
           (debit_debtors_ledger.financial_account_credit_id = credit_debtors_ledger.financial_account_debit_id)
          OR (debit_debtors_ledger.financial_account_credit_id = credit_debtors_ledger.financial_account_credit_id)
     )
        
UNION ALL
         
SELECT credit_debtors_ledger.debtors_ledger_id AS dl_id
     ,
            credit_debtors_ledger.financial_account_debit_id AS fin_acc_id
     ,
            
     (- debtors_ledger_offsets.debtors_ledger_offset_amount) AS offset_amount
     ,
            credit_debtors_ledger.debtors_ledger_date
     ,
            debit_debtors_ledger.debtors_ledger_date AS adj_debtors_ledger_date
           
  FROM (
           (acc.debtors_ledger_offsets
             
              JOIN acc.debtors_ledger debit_debtors_ledger 
                ON (
                       (debtors_ledger_offsets.debtors_ledger_debit_id = debit_debtors_ledger.debtors_ledger_id)
                 )
           )
             
        JOIN acc.debtors_ledger credit_debtors_ledger 
          ON (
                 (debtors_ledger_offsets.debtors_ledger_credit_id = credit_debtors_ledger.debtors_ledger_id)
           )
     )
          
 WHERE (
           (credit_debtors_ledger.financial_account_debit_id = debit_debtors_ledger.financial_account_debit_id)
          OR (credit_debtors_ledger.financial_account_debit_id = debit_debtors_ledger.financial_account_credit_id)
     )
        
UNION ALL
         
SELECT credit_debtors_ledger.debtors_ledger_id AS dl_id
     ,
            credit_debtors_ledger.financial_account_credit_id AS fin_acc_id
     ,
            
     (- debtors_ledger_offsets.debtors_ledger_offset_amount) AS offset_amount
     ,
            credit_debtors_ledger.debtors_ledger_date
     ,
            debit_debtors_ledger.debtors_ledger_date AS adj_debtors_ledger_date
           
  FROM (
           (acc.debtors_ledger_offsets
             
              JOIN acc.debtors_ledger debit_debtors_ledger 
                ON (
                       (debtors_ledger_offsets.debtors_ledger_debit_id = debit_debtors_ledger.debtors_ledger_id)
                 )
           )
             
        JOIN acc.debtors_ledger credit_debtors_ledger 
          ON (
                 (debtors_ledger_offsets.debtors_ledger_credit_id = credit_debtors_ledger.debtors_ledger_id)
           )
     )
          
 WHERE (
           (credit_debtors_ledger.financial_account_credit_id = debit_debtors_ledger.financial_account_debit_id)
          OR (credit_debtors_ledger.financial_account_credit_id = debit_debtors_ledger.financial_account_credit_id)
     )
) offsets;

Index - Schema acc

View: acc.view_total_offsets_summed_by_ledger_id_by_fin_acc_id

acc.view_total_offsets_summed_by_ledger_id_by_fin_acc_id Structure
F-Key Name Type Description
debtors_ledger_id uuid
financial_account_id uuid
total_offset_amount bigint 
SELECT offsets.dl_id AS debtors_ledger_id
,
    offsets.fin_acc_id AS financial_account_id
,
    sum
(offsets.offset_amount) AS total_offset_amount
   
FROM (
SELECT debit_debtors_ledger.debtors_ledger_id AS dl_id
     ,
            debit_debtors_ledger.financial_account_debit_id AS fin_acc_id
     ,
            debtors_ledger_offsets.debtors_ledger_offset_amount AS offset_amount
           
  FROM (
           (acc.debtors_ledger_offsets
             
              JOIN acc.debtors_ledger debit_debtors_ledger 
                ON (
                       (debtors_ledger_offsets.debtors_ledger_debit_id = debit_debtors_ledger.debtors_ledger_id)
                 )
           )
             
        JOIN acc.debtors_ledger credit_debtors_ledger 
          ON (
                 (debtors_ledger_offsets.debtors_ledger_credit_id = credit_debtors_ledger.debtors_ledger_id)
           )
     )
          
 WHERE (
           (debit_debtors_ledger.financial_account_debit_id = credit_debtors_ledger.financial_account_debit_id)
          OR (debit_debtors_ledger.financial_account_debit_id = credit_debtors_ledger.financial_account_credit_id)
     )
        
UNION ALL
         
SELECT debit_debtors_ledger.debtors_ledger_id AS dl_id
     ,
            debit_debtors_ledger.financial_account_credit_id AS fin_acc_id
     ,
            debtors_ledger_offsets.debtors_ledger_offset_amount AS offset_amount
           
  FROM (
           (acc.debtors_ledger_offsets
             
              JOIN acc.debtors_ledger debit_debtors_ledger 
                ON (
                       (debtors_ledger_offsets.debtors_ledger_debit_id = debit_debtors_ledger.debtors_ledger_id)
                 )
           )
             
        JOIN acc.debtors_ledger credit_debtors_ledger 
          ON (
                 (debtors_ledger_offsets.debtors_ledger_credit_id = credit_debtors_ledger.debtors_ledger_id)
           )
     )
          
 WHERE (
           (debit_debtors_ledger.financial_account_credit_id = credit_debtors_ledger.financial_account_debit_id)
          OR (debit_debtors_ledger.financial_account_credit_id = credit_debtors_ledger.financial_account_credit_id)
     )
        
UNION ALL
         
SELECT credit_debtors_ledger.debtors_ledger_id AS dl_id
     ,
            credit_debtors_ledger.financial_account_debit_id AS fin_acc_id
     ,
            
     (- debtors_ledger_offsets.debtors_ledger_offset_amount) AS offset_amount
           
  FROM (
           (acc.debtors_ledger_offsets
             
              JOIN acc.debtors_ledger debit_debtors_ledger 
                ON (
                       (debtors_ledger_offsets.debtors_ledger_debit_id = debit_debtors_ledger.debtors_ledger_id)
                 )
           )
             
        JOIN acc.debtors_ledger credit_debtors_ledger 
          ON (
                 (debtors_ledger_offsets.debtors_ledger_credit_id = credit_debtors_ledger.debtors_ledger_id)
           )
     )
          
 WHERE (
           (credit_debtors_ledger.financial_account_debit_id = debit_debtors_ledger.financial_account_debit_id)
          OR (credit_debtors_ledger.financial_account_debit_id = debit_debtors_ledger.financial_account_credit_id)
     )
        
UNION ALL
         
SELECT credit_debtors_ledger.debtors_ledger_id AS dl_id
     ,
            credit_debtors_ledger.financial_account_credit_id AS fin_acc_id
     ,
            
     (- debtors_ledger_offsets.debtors_ledger_offset_amount) AS offset_amount
           
  FROM (
           (acc.debtors_ledger_offsets
             
              JOIN acc.debtors_ledger debit_debtors_ledger 
                ON (
                       (debtors_ledger_offsets.debtors_ledger_debit_id = debit_debtors_ledger.debtors_ledger_id)
                 )
           )
             
        JOIN acc.debtors_ledger credit_debtors_ledger 
          ON (
                 (debtors_ledger_offsets.debtors_ledger_credit_id = credit_debtors_ledger.debtors_ledger_id)
           )
     )
          
 WHERE (
           (credit_debtors_ledger.financial_account_credit_id = debit_debtors_ledger.financial_account_debit_id)
          OR (credit_debtors_ledger.financial_account_credit_id = debit_debtors_ledger.financial_account_credit_id)
     )
) offsets
  
GROUP BY offsets.dl_id
, offsets.fin_acc_id;

Index - Schema acc

Function: acc.create_financial_accounts_organisation_sequence_trigger()

Returns: trigger

Language: PLPGSQL

 
    DECLARE 
        sql varchar := 'CREATE SEQUENCE acc."financial_accounts_organisation_seq_' || NEW.organisation_id || '"'; 
    BEGIN 
        EXECUTE sql; 
        return NEW; 
    END;

Function: acc.create_invoices_organisation_sequence_trigger()

Returns: trigger

Language: PLPGSQL

 
    DECLARE 
        sql varchar := 'CREATE SEQUENCE acc."invoices_organisation_seq_' || NEW.organisation_id || '"'; 
    BEGIN 
        EXECUTE sql; 
        return NEW; 
    END;

Function: acc.create_receipts_organisation_sequence_trigger()

Returns: trigger

Language: PLPGSQL

 
    DECLARE 
        sql varchar := 'CREATE SEQUENCE acc."receipts_organisation_seq_' || NEW.organisation_id || '" START 10000'; 
    BEGIN 
        EXECUTE sql; 
        return NEW; 
    END;

Function: acc.financial_account_balance(financial_account_id uuid, debtors_ledger_date date)

Returns: bigint

Language: SQL

 
  SELECT  
    COALESCE(acc.financial_account_debtors_ledger_summed_amounts($1, $2), 0::int) + 
    COALESCE(acc.financial_account_debtors_ledger_summed_offsets($1, $2), 0::int)  
    AS balance;

Function: acc.financial_account_balance(financial_account_id uuid, debtors_ledger_date date, financial_cost_centre_id uuid)

Returns: bigint

Language: SQL

  SELECT
    COALESCE(acc.financial_account_debtors_ledger_summed_amounts($1, $2, $3), 0::int) +
    COALESCE(acc.financial_account_debtors_ledger_summed_offsets($1, $2, $3), 0::int)
    AS balance;

Function: acc.financial_account_debtors_ledger_summed_amounts(financial_account_id uuid, debtors_ledger_date date)

Returns: bigint

Language: SQL

 
  SELECT  
    SUM( 
      CASE  
        WHEN debtors_ledger.financial_account_debit_id = $1   
        THEN debtors_ledger.debtors_ledger_amount   
        ELSE 0::integer  
      END +   
      CASE  
        WHEN debtors_ledger.financial_account_credit_id = $1   
        THEN - debtors_ledger.debtors_ledger_amount  
        ELSE 0::integer  
      END) AS debtors_ledger_amount 
  FROM acc.debtors_ledger 
  WHERE (acc.debtors_ledger.financial_account_debit_id = $1 OR acc.debtors_ledger.financial_account_credit_id = $1) AND acc.debtors_ledger.debtors_ledger_date <= $2

Function: acc.financial_account_debtors_ledger_summed_amounts(financial_account_id uuid, debtors_ledger_date date, financial_cost_centre_id uuid)

Returns: bigint

Language: SQL

  SELECT
    SUM(
      CASE
        WHEN debtors_ledger.financial_account_debit_id = $1
        THEN debtors_ledger.debtors_ledger_amount
        ELSE 0::integer
      END +
      CASE
        WHEN debtors_ledger.financial_account_credit_id = $1
        THEN - debtors_ledger.debtors_ledger_amount
        ELSE 0::integer
      END) AS debtors_ledger_amount
  FROM acc.debtors_ledger
  WHERE (acc.debtors_ledger.financial_account_debit_id = $1 OR acc.debtors_ledger.financial_account_credit_id = $1) 
  AND acc.debtors_ledger.debtors_ledger_date <= $2
  AND acc.debtors_ledger.financial_cost_centre_id = $3;

Function: acc.financial_account_debtors_ledger_summed_offsets(financial_account_id uuid, debtors_ledger_date date)

Returns: bigint

Language: SQL

 
    SELECT   
      SUM(  
      CASE  
        WHEN debit_debtors_ledger.debtors_ledger_date <= $2 AND debit_debtors_ledger.financial_account_debit_id = $1   
        THEN debtors_ledger_offsets.debtors_ledger_offset_amount   
        ELSE 0::integer  
      END +   
      CASE  
        WHEN debit_debtors_ledger.debtors_ledger_date <= $2 AND debit_debtors_ledger.financial_account_credit_id = $1   
        THEN debtors_ledger_offsets.debtors_ledger_offset_amount   
        ELSE 0::integer  
      END +   
      CASE  
        WHEN credit_debtors_ledger.debtors_ledger_date <= $2 AND credit_debtors_ledger.financial_account_debit_id = $1   
        THEN - debtors_ledger_offsets.debtors_ledger_offset_amount   
        ELSE 0::integer  
      END +   
      CASE  
        WHEN credit_debtors_ledger.debtors_ledger_date <= $2 AND credit_debtors_ledger.financial_account_credit_id = $1   
        THEN - debtors_ledger_offsets.debtors_ledger_offset_amount   
        ELSE 0::integer  
      END ) AS debtors_ledger_offset_amount  
    FROM acc.debtors_ledger_offsets debtors_ledger_offsets   
    JOIN acc.debtors_ledger debit_debtors_ledger   
      ON debtors_ledger_offsets.debtors_ledger_debit_id = debit_debtors_ledger.debtors_ledger_id    
      AND (debit_debtors_ledger.financial_account_debit_id = $1 OR debit_debtors_ledger.financial_account_credit_id = $1 )  
    JOIN acc.debtors_ledger credit_debtors_ledger   
      ON debtors_ledger_offsets.debtors_ledger_credit_id = credit_debtors_ledger.debtors_ledger_id   
      AND (credit_debtors_ledger.financial_account_debit_id = $1 OR credit_debtors_ledger.financial_account_credit_id = $1 )   
    WHERE debit_debtors_ledger.debtors_ledger_date <= $2 OR credit_debtors_ledger.debtors_ledger_date <= $2 ;

Function: acc.financial_account_debtors_ledger_summed_offsets(financial_account_id uuid, debtors_ledger_date date, financial_cost_centre_id uuid)

Returns: bigint

Language: SQL

    
    SELECT
      SUM(
      CASE
        WHEN debit_debtors_ledger.debtors_ledger_date <= $2 AND debit_debtors_ledger.financial_account_debit_id = $1
        THEN debtors_ledger_offsets.debtors_ledger_offset_amount
        ELSE 0::integer
      END +
      CASE
        WHEN debit_debtors_ledger.debtors_ledger_date <= $2 AND debit_debtors_ledger.financial_account_credit_id = $1
        THEN debtors_ledger_offsets.debtors_ledger_offset_amount
        ELSE 0::integer
      END +
      CASE
        WHEN credit_debtors_ledger.debtors_ledger_date <= $2 AND credit_debtors_ledger.financial_account_debit_id = $1
        THEN - debtors_ledger_offsets.debtors_ledger_offset_amount
        ELSE 0::integer
      END +
      CASE
        WHEN credit_debtors_ledger.debtors_ledger_date <= $2 AND credit_debtors_ledger.financial_account_credit_id = $1
        THEN - debtors_ledger_offsets.debtors_ledger_offset_amount
        ELSE 0::integer
      END ) AS debtors_ledger_offset_amount
    FROM acc.debtors_ledger_offsets debtors_ledger_offsets
    JOIN acc.debtors_ledger debit_debtors_ledger
      ON debtors_ledger_offsets.debtors_ledger_debit_id = debit_debtors_ledger.debtors_ledger_id
      AND (debit_debtors_ledger.financial_account_debit_id = $1 OR debit_debtors_ledger.financial_account_credit_id = $1 )
    JOIN acc.debtors_ledger credit_debtors_ledger
      ON debtors_ledger_offsets.debtors_ledger_credit_id = credit_debtors_ledger.debtors_ledger_id
      AND (credit_debtors_ledger.financial_account_debit_id = $1 OR credit_debtors_ledger.financial_account_credit_id = $1 ) 
    WHERE (debit_debtors_ledger.debtors_ledger_date <= $2 AND debit_debtors_ledger.financial_cost_centre_id = $3) 
      AND (credit_debtors_ledger.debtors_ledger_date <= $2 AND credit_debtors_ledger.financial_cost_centre_id = $3);

Function: acc.financial_account_debtors_ledger_summed_offsets(financial_account_id uuid, debtors_ledger_id uuid)

Returns: bigint

Language: SQL

  SELECT
    SUM(CASE WHEN debtors_ledger_offsets.debtors_ledger_debit_id = $2 AND debit_debtors_ledger.financial_account_debit_id = $1 THEN       debtors_ledger_offsets.debtors_ledger_offset_amount ELSE 0::integer END 
      + CASE WHEN debtors_ledger_offsets.debtors_ledger_debit_id = $2 AND debit_debtors_ledger.financial_account_credit_id = $1 THEN       debtors_ledger_offsets.debtors_ledger_offset_amount ELSE 0::integer END 
      + CASE WHEN debtors_ledger_offsets.debtors_ledger_credit_id = $2 AND credit_debtors_ledger.financial_account_debit_id = $1 THEN 
      - debtors_ledger_offsets.debtors_ledger_offset_amount ELSE 0::integer END 
      + CASE WHEN debtors_ledger_offsets.debtors_ledger_credit_id = $2 AND credit_debtors_ledger.financial_account_credit_id = $1 THEN 
      - debtors_ledger_offsets.debtors_ledger_offset_amount ELSE 0::integer END) AS offset_balance 
  FROM acc.debtors_ledger_offsets debtors_ledger_offsets
  JOIN acc.debtors_ledger debit_debtors_ledger 
    ON debtors_ledger_offsets.debtors_ledger_debit_id = debit_debtors_ledger.debtors_ledger_id 
    AND (debit_debtors_ledger.financial_account_debit_id = $1 OR debit_debtors_ledger.financial_account_credit_id = $1 )
  JOIN acc.debtors_ledger credit_debtors_ledger 
    ON debtors_ledger_offsets.debtors_ledger_credit_id = credit_debtors_ledger.debtors_ledger_id 
    AND (credit_debtors_ledger.financial_account_debit_id = $1 OR credit_debtors_ledger.financial_account_credit_id = $1 )
 WHERE debtors_ledger_offsets.debtors_ledger_debit_id = $2 OR debtors_ledger_offsets.debtors_ledger_credit_id = $2 ;

Function: acc.financial_account_debtors_ledger_summed_offsets(financial_account_id uuid, debtors_ledger_id uuid, offset_date date)

Returns: bigint

Language: SQL

  SELECT
    SUM(CASE WHEN debtors_ledger_offsets.debtors_ledger_debit_id = $2 AND debit_debtors_ledger.financial_account_debit_id = $1 THEN       debtors_ledger_offsets.debtors_ledger_offset_amount ELSE 0::integer END 
      + CASE WHEN debtors_ledger_offsets.debtors_ledger_debit_id = $2 AND debit_debtors_ledger.financial_account_credit_id = $1 THEN       debtors_ledger_offsets.debtors_ledger_offset_amount ELSE 0::integer END 
      + CASE WHEN debtors_ledger_offsets.debtors_ledger_credit_id = $2 AND credit_debtors_ledger.financial_account_debit_id = $1 THEN 
      - debtors_ledger_offsets.debtors_ledger_offset_amount ELSE 0::integer END 
      + CASE WHEN debtors_ledger_offsets.debtors_ledger_credit_id = $2 AND credit_debtors_ledger.financial_account_credit_id = $1 THEN 
      - debtors_ledger_offsets.debtors_ledger_offset_amount ELSE 0::integer END) AS offset_balance 
  FROM acc.debtors_ledger_offsets debtors_ledger_offsets
  JOIN acc.debtors_ledger as debit_debtors_ledger 
    ON debtors_ledger_offsets.debtors_ledger_debit_id = debit_debtors_ledger.debtors_ledger_id 
    AND (debit_debtors_ledger.financial_account_debit_id = $1 OR debit_debtors_ledger.financial_account_credit_id = $1 )
  JOIN acc.debtors_ledger as credit_debtors_ledger 
    ON debtors_ledger_offsets.debtors_ledger_credit_id = credit_debtors_ledger.debtors_ledger_id 
    AND (credit_debtors_ledger.financial_account_debit_id = $1 OR credit_debtors_ledger.financial_account_credit_id = $1 )
 WHERE (debtors_ledger_offsets.debtors_ledger_debit_id = $2 OR debtors_ledger_offsets.debtors_ledger_credit_id = $2)
    AND (debit_debtors_ledger.debtors_ledger_date <= $3 AND credit_debtors_ledger.debtors_ledger_date <= $3);

Function: acc.financial_accounts_organisation_sequence_value_trigger()

Returns: trigger

Language: PLPGSQL

 
    BEGIN 
        NEW.financial_account_organisation_sequence_value := nextval('acc."financial_accounts_organisation_seq_' || NEW.organisation_id || '"'); 
        IF NEW.financial_account_reference IS NULL OR NEW.financial_account_reference = '''' THEN 
            NEW.financial_account_reference := NEW.financial_account_organisation_sequence_value::TEXT; 
        END IF; 
        RETURN NEW; 
    END;

Function: acc.invoices_organisation_sequence_value_trigger()

Returns: trigger

Language: PLPGSQL

 
    BEGIN 
        NEW.invoice_organisation_sequence_value := nextval('acc."invoices_organisation_seq_' || NEW.organisation_id || '"'); 
        IF NEW.invoice_reference IS NULL OR NEW.invoice_reference = '''' THEN 
            NEW.invoice_reference := NEW.invoice_organisation_sequence_value::TEXT; 
        END IF; 
        RETURN NEW; 
    END;

Schema eft

Table: eft.bank_accounts

eft.bank_accounts Structure
F-Key Name Type Description
eft.credentials.credential_id credential_id uuid PRIMARY KEY
bank_account_bsb text NOT NULL
bank_account_number text NOT NULL
bank_account_holder text
bank_account_organisation_name text
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Index - Schema eft

Table: eft.credentials

eft.credentials Structure
F-Key Name Type Description
credential_id uuid PRIMARY KEY
acc.financial_accounts.financial_account_id financial_account_id uuid NOT NULL
credential_active boolean NOT NULL DEFAULT true
credential_from date NOT NULL
credential_to date
user_account_full_name text NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema eft

Table: eft.credit_cards

eft.credit_cards Structure
F-Key Name Type Description
eft.credentials.credential_id credential_id uuid PRIMARY KEY
credit_card_number text NOT NULL
credit_card_expiry date NOT NULL
credit_card_holder text NOT NULL
credit_card_security text
acc.credit_card_types.credit_card_type_id credit_card_type_id integer NOT NULL
credit_card_number_hint text
credit_card_token text
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Index - Schema eft

Table: eft.transaction_frequencies

eft.transaction_frequencies Structure
F-Key Name Type Description
transaction_frequency_id uuid PRIMARY KEY
transaction_frequency_name text NOT NULL
transaction_frequency_period text NOT NULL
transaction_frequency_period_amount integer NOT NULL
org.organisations.organisation_id organisation_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema eft

Table: eft.transaction_processor_accounts

eft.transaction_processor_accounts Structure
F-Key Name Type Description
eft.credentials.credential_id credential_id uuid PRIMARY KEY
transaction_processor_account_reference text NOT NULL
transaction_processor_account_holder text NOT NULL
transaction_processor_account_options text
transaction_processor_submitted_version timestamp with time zone
eft.transaction_processors.transaction_processor_id transaction_processor_id uuid NOT NULL
eft.credentials.credential_id source_credential_id uuid
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Index - Schema eft

Table: eft.transaction_processors

eft.transaction_processors Structure
F-Key Name Type Description
transaction_processor_id uuid PRIMARY KEY
transaction_processor_name text NOT NULL
transaction_processor_active boolean
transaction_processor_provider text NOT NULL
transaction_processor_options text
acc.financial_accounts.financial_account_id receivable_financial_account_id uuid NOT NULL
acc.financial_accounts.financial_account_id expense_financial_account_id uuid NOT NULL
acc.financial_accounts.financial_account_id income_financial_account_id uuid NOT NULL
user_account_full_name text NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema eft

Table: eft.transaction_schedules

eft.transaction_schedules Structure
F-Key Name Type Description
transaction_schedule_id uuid PRIMARY KEY
transaction_schedule_from date NOT NULL
transaction_schedule_to date
transaction_schedule_max_credit integer NOT NULL
acc.financial_accounts.financial_account_id financial_account_id uuid NOT NULL
acc.financial_cost_centres.financial_cost_centre_id financial_cost_centre_id uuid NOT NULL
eft.transaction_frequencies.transaction_frequency_id transaction_frequency_id uuid NOT NULL
eft.transaction_processors.transaction_processor_id transaction_processor_id uuid NOT NULL
user_account_full_name text NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

transaction_schedules_financial_account_id_idx financial_account_id

Index - Schema eft

Table: eft.transactions

eft.transactions Structure
F-Key Name Type Description
acc.debtors_ledger.debtors_ledger_id debtors_ledger_id uuid PRIMARY KEY
eft_request_lock_expiry timestamp with time zone
eft_request_submitted timestamp with time zone
eft_request_reference text
eft.credentials.credential_id issuer_credential_id uuid
eft.credentials.credential_id acquirer_credential_id uuid
eft.transaction_processors.transaction_processor_id transaction_processor_id uuid
eft.transactions.debtors_ledger_id cancellation_debtors_ledger_id uuid
eft_response_received timestamp with time zone
eft_response_amount integer
eft_response_success boolean
eft_response_status text
eft_response_description text
eft_response_issuer_authorization_number text
eft_response_reference text
eft_response_cleared date
eft_response_rejected date
eft_response_settlement date
eft.transaction_schedules.transaction_schedule_id transaction_schedule_id uuid UNIQUE#1
debtors_ledger_date date UNIQUE#1
user_account_full_name text NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

transactions_transaction_processor_id_idx transaction_processor_id

Index - Schema eft

Schema oms

Table: oms.account_holders

oms.account_holders Structure
F-Key Name Type Description
account_holder_id uuid PRIMARY KEY
oms.accounts.financial_account_id financial_account_id uuid UNIQUE#1 UNIQUE#2 NOT NULL
oms.persons.person_id person_id uuid UNIQUE#1 NOT NULL
account_holder_primary boolean UNIQUE#2
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
account_holders_financial_account_id_idx financial_account_id account_holders_person_id_idx person_id

Index - Schema oms

Table: oms.accounts

oms.accounts Structure
F-Key Name Type Description
acc.financial_accounts.financial_account_id financial_account_id uuid PRIMARY KEY
org.administration_units.administration_unit_id administration_unit_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
text_search text NOT NULL

Tables referencing this one via Foreign Key Constraints:

accounts_administration_unit_id_idx administration_unit_id accounts_text_search_trgm_idx text_search gin_trgm_ops

Index - Schema oms

Table: oms.associations

oms.associations Structure
F-Key Name Type Description
association_id uuid PRIMARY KEY
association_from date NOT NULL
association_to date
oms.persons.person_id person_id uuid NOT NULL
oms.groups.group_id group_id uuid UNIQUE#1 NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
association_name text
association_group_sequence_value integer UNIQUE#1
keywords text NOT NULL DEFAULT ''::text
system_keywords text NOT NULL DEFAULT ''::text

Tables referencing this one via Foreign Key Constraints:

associations_group_id_idx group_id associations_keywords_trgm_idx keywords gin_trgm_ops associations_person_id_idx person_id

Index - Schema oms

Table: oms.cost_centres

oms.cost_centres Structure
F-Key Name Type Description
acc.financial_cost_centres.financial_cost_centre_id financial_cost_centre_id uuid PRIMARY KEY
org.administration_units.administration_unit_id administration_unit_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

cost_centres_administration_unit_id_idx administration_unit_id

Index - Schema oms

Table: oms.groups

oms.groups Structure
F-Key Name Type Description
group_id uuid PRIMARY KEY
group_name text NOT NULL
org.administration_units.administration_unit_id administration_unit_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
text_search text NOT NULL
keywords text NOT NULL DEFAULT ''::text
system_keywords text NOT NULL DEFAULT ''::text

Tables referencing this one via Foreign Key Constraints:

groups_administration_unit_id_idx administration_unit_id groups_keywords_trgm_idx keywords gin_trgm_ops groups_text_search_trgm_idx text_search gin_trgm_ops

Index - Schema oms

Table: oms.membership_associations

oms.membership_associations Structure
F-Key Name Type Description
oms.associations.association_id association_id uuid PRIMARY KEY
membership_association_reference text
oms.membership_types.membership_type_id membership_type_id uuid
membership_association_unfinancial boolean
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
membership_associations_membership_type_id_idx membership_type_id

Index - Schema oms

Table: oms.membership_groups

oms.membership_groups Structure
F-Key Name Type Description
oms.groups.group_id group_id uuid PRIMARY KEY
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema oms

Table: oms.membership_types

oms.membership_types Structure
F-Key Name Type Description
membership_type_id uuid PRIMARY KEY
membership_type_name text NOT NULL
oms.membership_groups.group_id group_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

membership_types_group_id_idx group_id

Index - Schema oms

Table: oms.persons

oms.persons Structure
F-Key Name Type Description
person_id uuid PRIMARY KEY
person_dob date
org.organisations.organisation_id organisation_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
person_first_names text NOT NULL
person_last_name text
person_home_phone text
person_mobile_phone text
person_email_address text
org.genders.gender_id gender_id text
org.addresses.address_id address_id uuid
text_search text NOT NULL
person_alias text
keywords text NOT NULL DEFAULT ''::text
system_keywords text NOT NULL DEFAULT ''::text

Tables referencing this one via Foreign Key Constraints:

persons_address_id_idx address_id persons_keywords_trgm_idx keywords gin_trgm_ops persons_organisation_id_idx organisation_id persons_text_search_trgm_idx text_search gin_trgm_ops

Index - Schema oms

View: oms.view_billing_period_transaction_ids

oms.view_billing_period_transaction_ids Structure
F-Key Name Type Description
administration_unit_id uuid
financial_account_id uuid
debtors_ledger_id uuid
debtors_ledger_amount_sign integer
posting_account_type_id uuid
debtors_ledger_date date 
SELECT accounts.administration_unit_id
,
    accounts.financial_account_id
,
    debtors_ledger.debtors_ledger_id
,
    1 AS debtors_ledger_amount_sign
,
    financial_accounts.posting_account_type_id
,
    debtors_ledger.debtors_ledger_date
   
FROM (
     (oms.accounts
     
        JOIN acc.debtors_ledger 
          ON (
                 (accounts.financial_account_id = debtors_ledger.financial_account_debit_id)
           )
     )
     
  JOIN acc.financial_accounts 
    ON (
           (debtors_ledger.financial_account_credit_id = financial_accounts.financial_account_id)
     )
)
UNION
 
SELECT accounts.administration_unit_id
,
    accounts.financial_account_id
,
    debtors_ledger.debtors_ledger_id
,
    '-1'::integer AS debtors_ledger_amount_sign
,
    financial_accounts.posting_account_type_id
,
    debtors_ledger.debtors_ledger_date
   
FROM (
     (oms.accounts
     
        JOIN acc.debtors_ledger 
          ON (
                 (accounts.financial_account_id = debtors_ledger.financial_account_credit_id)
           )
     )
     
  JOIN acc.financial_accounts 
    ON (
           (debtors_ledger.financial_account_debit_id = financial_accounts.financial_account_id)
     )
);

Index - Schema oms

Function: oms.associations_group_sequence_value_trigger()

Returns: trigger

Language: PLPGSQL

 
    BEGIN 
        NEW.association_group_sequence_value := nextval('oms."associations_group_seq_' || NEW.group_id || '"'); 
        RETURN NEW; 
    END;

Function: oms.create_associations_group_sequence_trigger()

Returns: trigger

Language: PLPGSQL

 
    DECLARE 
        sql varchar := 'CREATE SEQUENCE oms."associations_group_seq_' || NEW.group_id || '"'; 
    BEGIN 
        EXECUTE sql; 
        return NEW; 
    END;

Schema oms_committees

Table: oms_committees.committee_associations

oms_committees.committee_associations Structure
F-Key Name Type Description
oms.associations.association_id association_id uuid PRIMARY KEY
committee_association_role text
committee_association_description text
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Index - Schema oms_committees

Table: oms_committees.committees

oms_committees.committees Structure
F-Key Name Type Description
oms.groups.group_id group_id uuid PRIMARY KEY
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Index - Schema oms_committees

Schema oms_employments

Table: oms_employments.contract_methods

oms_employments.contract_methods Structure
F-Key Name Type Description
contract_method_id uuid PRIMARY KEY
contract_method_type text NOT NULL
contract_method_name text NOT NULL
oms_employments.employers.group_id group_id uuid NOT NULL
contract_method_from date NOT NULL
contract_method_to date
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

contract_methods_group_id_idx group_id

Index - Schema oms_employments

Table: oms_employments.employers

oms_employments.employers Structure
F-Key Name Type Description
oms.groups.group_id group_id uuid PRIMARY KEY
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
oms_employments.employers.group_id parent_group_id uuid

Tables referencing this one via Foreign Key Constraints:

Index - Schema oms_employments

Table: oms_employments.employment_worksites

oms_employments.employment_worksites Structure
F-Key Name Type Description
employment_worksite_id uuid PRIMARY KEY
employment_worksite_from date NOT NULL
employment_worksite_to date
org.addresses.address_id address_id uuid NOT NULL
oms.associations.association_id association_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
employment_worksites_address_id_idx address_id employment_worksites_association_id_idx association_id

Index - Schema oms_employments

Table: oms_employments.employments

oms_employments.employments Structure
F-Key Name Type Description
oms.associations.association_id association_id uuid PRIMARY KEY
employment_title text
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
oms_employments.contract_methods.contract_method_id contract_method_id uuid
oms_employments.work_classifications.work_classification_id work_classification_id uuid
employment_fte integer
employment_annual_income integer
oms_employments.payrolls.payroll_id payroll_id uuid
employment_payroll_reference text
employments_payroll_id_idx payroll_id employments_work_classification_id_idx work_classification_id

Index - Schema oms_employments

Table: oms_employments.payrolls

oms_employments.payrolls Structure
F-Key Name Type Description
payroll_id uuid PRIMARY KEY
payroll_name text NOT NULL
payroll_active boolean NOT NULL
oms_employments.employers.group_id group_id uuid NOT NULL
oms.accounts.financial_account_id receivable_financial_account_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
text_search text NOT NULL
keywords text NOT NULL DEFAULT ''::text

Tables referencing this one via Foreign Key Constraints:

payrolls_group_id_idx group_id payrolls_keywords_trgm_idx keywords gin_trgm_ops payrolls_receivable_financial_account_id_idx receivable_financial_account_id payrolls_text_search_trgm_idx text_search gin_trgm_ops

Index - Schema oms_employments

Table: oms_employments.work_classifications

oms_employments.work_classifications Structure
F-Key Name Type Description
work_classification_id uuid PRIMARY KEY
work_classification_name text NOT NULL
work_classification_path text NOT NULL
oms_employments.work_classifications.work_classification_id parent_work_classification_id uuid
org.administration_units.administration_unit_id administration_unit_id uuid NOT NULL
text_search text NOT NULL DEFAULT ''::text
keywords text NOT NULL DEFAULT ''::text
system_keywords text NOT NULL DEFAULT ''::text
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

work_classifications_administration_unit_id_idx administration_unit_id work_classifications_keywords_trgm_idx keywords gin_trgm_ops work_classifications_parent_work_classification_id_idx parent_work_classification_id work_classifications_text_search_trgm_idx text_search gin_trgm_ops

Index - Schema oms_employments

Table: oms_employments.worksite_associations

oms_employments.worksite_associations Structure
F-Key Name Type Description
oms.associations.association_id association_id uuid PRIMARY KEY
oms_employments.worksites.address_id address_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
worksite_associations_address_id_idx address_id

Index - Schema oms_employments

Table: oms_employments.worksites

oms_employments.worksites Structure
F-Key Name Type Description
org.addresses.address_id address_id uuid PRIMARY KEY
oms_employments.employers.group_id group_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
worksite_name text
text_search text NOT NULL
keywords text NOT NULL DEFAULT ''::text
system_keywords text NOT NULL DEFAULT ''::text

Tables referencing this one via Foreign Key Constraints:

worksites_group_id_idx group_id worksites_keywords_trgm_idx keywords gin_trgm_ops worksites_text_search_trgm_idx text_search gin_trgm_ops

Index - Schema oms_employments

Schema oms_subs

Table: oms_subs.subscription_fixed_fees

oms_subs.subscription_fixed_fees Structure
F-Key Name Type Description
subscription_fixed_fee_id uuid PRIMARY KEY
subscription_fixed_fee_from date UNIQUE#1 NOT NULL
subscription_fixed_fee_amount integer NOT NULL
oms_subs.subscription_methods.subscription_method_id subscription_method_id uuid UNIQUE#1 NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
subscription_fixed_fees_subscription_method_id_idx subscription_method_id

Index - Schema oms_subs

Table: oms_subs.subscription_frequencies

oms_subs.subscription_frequencies Structure
F-Key Name Type Description
subscription_frequency_id uuid PRIMARY KEY
subscription_frequency_name text NOT NULL
subscription_frequency_period text NOT NULL
subscription_frequency_period_amount integer NOT NULL
org.administration_units.administration_unit_id administration_unit_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema oms_subs

Table: oms_subs.subscription_methods

oms_subs.subscription_methods Structure
F-Key Name Type Description
subscription_method_id uuid PRIMARY KEY
subscription_method_name text NOT NULL
subscription_method_from date NOT NULL
subscription_method_to date
oms_subs.subscription_frequencies.subscription_frequency_id subscription_frequency_id uuid
subscription_billing_algorithm_id text NOT NULL
oms.groups.group_id group_id uuid NOT NULL
oms.accounts.financial_account_id financial_account_id uuid NOT NULL
oms.cost_centres.financial_cost_centre_id financial_cost_centre_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

subscription_methods_financial_account_id_idx financial_account_id subscription_methods_financial_cost_centre_id_idx financial_cost_centre_id subscription_methods_group_id_idx group_id

Index - Schema oms_subs

Table: oms_subs.subscription_periods

oms_subs.subscription_periods Structure
F-Key Name Type Description
subscription_period_id uuid PRIMARY KEY
subscription_period_from date UNIQUE#1 NOT NULL
subscription_period_to date UNIQUE#1 NOT NULL
oms.associations.association_id association_id uuid UNIQUE#1 NOT NULL
oms_subs.subscription_methods.subscription_method_id subscription_method_id uuid
acc.debtors_ledger.debtors_ledger_id debtors_ledger_id uuid UNIQUE NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
subscription_periods_association_id_idx association_id

Index - Schema oms_subs

Table: oms_subs.subscription_quoted_fees

oms_subs.subscription_quoted_fees Structure
F-Key Name Type Description
oms_subs.subscription_renewals.subscription_renewal_id subscription_renewal_id uuid PRIMARY KEY
subscription_quoted_fee_amount integer NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Index - Schema oms_subs

Table: oms_subs.subscription_renewals

oms_subs.subscription_renewals Structure
F-Key Name Type Description
subscription_renewal_id uuid PRIMARY KEY
subscription_renewal_from date NOT NULL
subscription_renewal_to date
oms.associations.association_id association_id uuid NOT NULL
acc.financial_accounts.financial_account_id financial_account_id uuid NOT NULL
oms_subs.subscription_methods.subscription_method_id subscription_method_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

subscription_renewals_association_id_idx association_id subscription_renewals_financial_account_id_idx financial_account_id subscription_renewals_subscription_method_id_idx subscription_method_id

Index - Schema oms_subs

View: oms_subs.view_subscription_period_financial_accounts

oms_subs.view_subscription_period_financial_accounts Structure
F-Key Name Type Description
subscription_period_id uuid
financial_account_id uuid 
SELECT subscription_periods.subscription_period_id
,
    debtors_ledger.financial_account_debit_id AS financial_account_id
   
FROM oms_subs.subscription_periods
,
    acc.debtors_ledger
  
WHERE (subscription_periods.debtors_ledger_id = debtors_ledger.debtors_ledger_id)
UNION ALL
 
SELECT subscription_periods.subscription_period_id
,
    debtors_ledger.financial_account_credit_id AS financial_account_id
   
FROM oms_subs.subscription_periods
,
    acc.debtors_ledger
  
WHERE (subscription_periods.debtors_ledger_id = debtors_ledger.debtors_ledger_id);

Index - Schema oms_subs

Schema org

Table: org.addresses

org.addresses Structure
F-Key Name Type Description
address_id uuid PRIMARY KEY
address_sub_street text
address_street text
address_locality text

AU - suburb US - city or town DE - city or town
address_administrative_area text

AU - state US - state DE - unused
address_sub_administrative_area text

AU - unused US - county DE - unused
address_postal_code text
address_country_code text NOT NULL
org.organisations.organisation_id organisation_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
address_formatted text
address_longitude_latitude point
text_search text NOT NULL

Tables referencing this one via Foreign Key Constraints:

addresses_address_country_code_address_administrative_area__idx address_country_code, address_administrative_area, address_locality, address_postal_code addresses_organisation_id_idx organisation_id addresses_text_search_trgm_idx text_search gin_trgm_ops

Index - Schema org

Table: org.administration_unit_associations

org.administration_unit_associations Structure
F-Key Name Type Description
administration_unit_association_id uuid PRIMARY KEY
org.administration_units.administration_unit_id administration_unit_parent_id uuid NOT NULL
org.administration_units.administration_unit_id administration_unit_id uuid NOT NULL

Index - Schema org

Table: org.administration_units

org.administration_units Structure
F-Key Name Type Description
administration_unit_id uuid PRIMARY KEY
administration_unit_name text NOT NULL
administration_unit_short_name text NOT NULL
administration_unit_time_zone text NOT NULL
administration_unit_phone text
administration_unit_fax text
administration_unit_mobile text
administration_unit_email_address text
administration_unit_web_site text
administration_unit_australian_business_number text
org.addresses.address_id address_id uuid
org.organisations.organisation_id organisation_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema org

Table: org.attributes

org.attributes Structure
F-Key Name Type Description
attribute_name text PRIMARY KEY
attribute_value text NOT NULL

Index - Schema org

Table: org.extended_attributes

org.extended_attributes Structure
F-Key Name Type Description
extended_attribute_id uuid PRIMARY KEY
extended_attribute_reference_id uuid UNIQUE#1 NOT NULL
extended_attribute_name text UNIQUE#1 NOT NULL
extended_attribute_value text
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
extended_attributes_extended_attribute_reference_id_idx extended_attribute_reference_id extended_attributes_extended_attribute_value_idx extended_attribute_value

Index - Schema org

Table: org.genders

org.genders Structure
F-Key Name Type Description
gender_id text PRIMARY KEY
gender_name text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema org

Table: org.keyword_suggestions

org.keyword_suggestions Structure
F-Key Name Type Description
keyword_suggestion_id uuid PRIMARY KEY
keyword text UNIQUE#1 NOT NULL
keyword_suggestion_role text UNIQUE#1 NOT NULL
keyword_suggestion_scope text UNIQUE#1 NOT NULL
keyword_suggestion_scope_reference_id uuid UNIQUE#1 NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Index - Schema org

Table: org.notes

org.notes Structure
F-Key Name Type Description
note_id uuid PRIMARY KEY
note_reference_id uuid NOT NULL
note_name text NOT NULL
note_value text NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Index - Schema org

Table: org.organisations

org.organisations Structure
F-Key Name Type Description
organisation_id uuid PRIMARY KEY
organisation_name text NOT NULL
organisation_addr text
organisation_phone_1 text
organisation_phone_2 text
organisation_fax text
organisation_australian_business_number text
organisation_email_address text
organisation_web_site text
organisation_closed_date date
organisation_smtp_host text
organisation_smtp_username text
organisation_smtp_password text
organisation_user_account_name_postfix text

Tables referencing this one via Foreign Key Constraints:

Index - Schema org

Table: org.storage_containers

org.storage_containers Structure
F-Key Name Type Description
storage_container_id uuid PRIMARY KEY
storage_container_name text UNIQUE#1 NOT NULL
storage_container_service_url text
storage_container_read_auth_psk text
storage_container_create_auth_psk text
org.organisations.organisation_id organisation_id uuid UNIQUE#1 NOT NULL
user_account_full_name text NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()
storage_containers_organisation_id_idx organisation_id

Index - Schema org

Table: org.tagged_items

org.tagged_items Structure
F-Key Name Type Description
tagged_item_id uuid PRIMARY KEY
tagged_item_reference_id uuid UNIQUE#1 NOT NULL
org.tags.tag_id tag_id uuid UNIQUE#1 NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
tagged_items_tagged_id_idx tag_id tagged_items_tagged_item_reference_id_idx tagged_item_reference_id

Index - Schema org

Table: org.tags

org.tags Structure
F-Key Name Type Description
tag_id uuid PRIMARY KEY
tag_name text UNIQUE#1 NOT NULL
tag_type text UNIQUE#1 NOT NULL
org.organisations.organisation_id organisation_id uuid NOT NULL
created timestamp with time zone NOT NULL DEFAULT now()
modified timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema org

Table: org.user_accounts

org.user_accounts Structure
F-Key Name Type Description
user_account_id uuid PRIMARY KEY
user_account_name text NOT NULL
user_account_password text NOT NULL
user_account_full_name text NOT NULL
user_account_is_active boolean DEFAULT false
org.administration_units.administration_unit_id administration_unit_id uuid NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema org

Table: org.user_accounts_user_permissions

org.user_accounts_user_permissions Structure
F-Key Name Type Description
id_pk uuid PRIMARY KEY
org.user_accounts.user_account_id user_account_id uuid NOT NULL
org.user_permissions.user_permission_id user_permission_id uuid NOT NULL

Index - Schema org

Table: org.user_permissions

org.user_permissions Structure
F-Key Name Type Description
user_permission_id uuid PRIMARY KEY
user_permission_name text NOT NULL
user_permission_description text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema org

Table: org.user_roles

org.user_roles Structure
F-Key Name Type Description
user_role_id uuid PRIMARY KEY
user_role_name text NOT NULL
user_role_description text NOT NULL
org.organisations.organisation_id organisation_id uuid NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema org

Table: org.user_roles_user_permissions

org.user_roles_user_permissions Structure
F-Key Name Type Description
user_role_user_permission_id uuid PRIMARY KEY
org.user_roles.user_role_id user_role_id uuid NOT NULL
org.user_permissions.user_permission_id user_permission_id uuid NOT NULL

Index - Schema org

View: org.view_user_accounts_permissions

org.view_user_accounts_permissions Structure
F-Key Name Type Description
user_account_id uuid
user_account_name text
user_permission_id uuid
user_permission_name text 
SELECT user_accounts.user_account_id
,
    user_accounts.user_account_name
,
    user_permissions.user_permission_id
,
    user_permissions.user_permission_name
   
FROM org.user_accounts_user_permissions
,
    org.user_accounts
,
    org.user_permissions
  
WHERE (
     (user_accounts_user_permissions.user_account_id = user_accounts.user_account_id)
   AND (user_accounts_user_permissions.user_permission_id = user_permissions.user_permission_id)
);

Index - Schema org

Schema public

standard public schema

Function: public.gin_extract_query_trgm(text, internal, smallint, internal, internal, internal, internal)

Returns: internal

Language: C

gin_extract_query_trgm

Function: public.gin_extract_value_trgm(text, internal)

Returns: internal

Language: C

gin_extract_value_trgm

Function: public.gin_trgm_consistent(internal, smallint, text, integer, internal, internal, internal, internal)

Returns: boolean

Language: C

gin_trgm_consistent

Function: public.gtrgm_compress(internal)

Returns: internal

Language: C

gtrgm_compress

Function: public.gtrgm_consistent(internal, text, integer, oid, internal)

Returns: boolean

Language: C

gtrgm_consistent

Function: public.gtrgm_decompress(internal)

Returns: internal

Language: C

gtrgm_decompress

Function: public.gtrgm_distance(internal, text, integer, oid)

Returns: double precision

Language: C

gtrgm_distance

Function: public.gtrgm_in(cstring)

Returns: gtrgm

Language: C

gtrgm_in

Function: public.gtrgm_out(public.gtrgm)

Returns: cstring

Language: C

gtrgm_out

Function: public.gtrgm_penalty(internal, internal, internal)

Returns: internal

Language: C

gtrgm_penalty

Function: public.gtrgm_picksplit(internal, internal)

Returns: internal

Language: C

gtrgm_picksplit

Function: public.gtrgm_same(public.gtrgm, public.gtrgm, internal)

Returns: internal

Language: C

gtrgm_same

Function: public.gtrgm_union(bytea, internal)

Returns: integer[]

Language: C

gtrgm_union

Function: public.set_limit(real)

Returns: real

Language: C

set_limit

Function: public.show_limit()

Returns: real

Language: C

show_limit

Function: public.show_trgm(text)

Returns: text[]

Language: C

show_trgm

Function: public.similarity(text, text)

Returns: real

Language: C

similarity

Function: public.similarity_dist(text, text)

Returns: real

Language: C

similarity_dist

Function: public.similarity_op(text, text)

Returns: boolean

Language: C

similarity_op

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict