Dumped on 2006-08-01
The different privileges
| F-Key | Name | Type | Description |
|---|---|---|---|
| ac_id | integer | PRIMARY KEY | |
| ac_description | text | NOT NULL |
Tables referencing this one via Foreign Key Constraints:
Action for Managing
| F-Key | Name | Type | Description |
|---|---|---|---|
| ag_id | serial | PRIMARY KEY | |
| ag_type | integer | ||
| f_id_dest | integer | NOT NULL | |
| f_id_exp | integer | NOT NULL | |
| ag_title | character varying(70) | ||
| ag_timestamp | timestamp without time zone | DEFAULT now() | |
| ag_cal | character(1) | DEFAULT 'C'::bpchar | |
| ag_ref_ag_id | integer | ||
| ag_comment | text | ||
| ag_ref | text |
The available attributs for the cards
| F-Key | Name | Type | Description |
|---|---|---|---|
| ad_id | integer | PRIMARY KEY DEFAULT nextval(('s_attr_def'::text)::regclass) | |
| ad_text | text |
Tables referencing this one via Foreign Key Constraints:
The value of attributs for the cards
| F-Key | Name | Type | Description |
|---|---|---|---|
| fiche_def_ref.frd_id | frd_id | integer | |
| attr_def.ad_id | ad_id | integer |
| F-Key | Name | Type | Description |
|---|---|---|---|
| jnt_fic_att_value.jft_id | jft_id | integer | |
| av_text | text |
The centralized journal
| F-Key | Name | Type | Description |
|---|---|---|---|
| c_id | integer | PRIMARY KEY DEFAULT nextval(('s_centralized'::text)::regclass) | |
| c_j_id | integer | ||
| c_date | date | NOT NULL | |
| c_internal | text | NOT NULL | |
| c_montant | numeric(20,4) | NOT NULL | |
| c_debit | boolean | DEFAULT true | |
| jrn_def.jrn_def_id | c_jrn_def | integer | NOT NULL |
| tmp_pcmn.pcm_val | c_poste | poste_comptable | |
| c_description | text | ||
| c_grp | integer | NOT NULL | |
| c_comment | text | ||
| c_rapt | text | ||
| c_periode | integer | ||
| c_order | integer |
This table contains all the documents : summary and lob files
| F-Key | Name | Type | Description |
|---|---|---|---|
| d_id | serial | PRIMARY KEY | |
| ag_id | integer | NOT NULL | |
| d_lob | oid | ||
| d_number | bigint | NOT NULL | |
| d_filename | text | ||
| d_mimetype | text | ||
| d_state | integer |
contains all the template for the documents
| F-Key | Name | Type | Description |
|---|---|---|---|
| md_id | serial | PRIMARY KEY | |
| md_name | text | NOT NULL | |
| md_lob | oid | ||
| document_type.dt_id | md_type | integer | NOT NULL |
| md_filename | text | ||
| md_mimetype | text |
State of the document
| F-Key | Name | Type | Description |
|---|---|---|---|
| s_id | serial | PRIMARY KEY | |
| s_value | character varying(50) | NOT NULL |
Type of document : meeting, invoice,...
| F-Key | Name | Type | Description |
|---|---|---|---|
| dt_id | serial | PRIMARY KEY | |
| dt_value | character varying(80) |
Tables referencing this one via Foreign Key Constraints:
Cards
| F-Key | Name | Type | Description |
|---|---|---|---|
| f_id | integer | PRIMARY KEY DEFAULT nextval(('s_fiche'::text)::regclass) | |
| fiche_def.fd_id | fd_id | integer |
Tables referencing this one via Foreign Key Constraints:
Cards definition
| F-Key | Name | Type | Description |
|---|---|---|---|
| fd_id | integer | PRIMARY KEY DEFAULT nextval(('s_fdef'::text)::regclass) | |
| fd_class_base | poste_comptable | ||
| fd_label | text | NOT NULL | |
| fd_create_account | boolean | DEFAULT false | |
| fiche_def_ref.frd_id | frd_id | integer | NOT NULL |
Tables referencing this one via Foreign Key Constraints:
Family Cards definition
| F-Key | Name | Type | Description |
|---|---|---|---|
| frd_id | integer | PRIMARY KEY DEFAULT nextval(('s_fiche_def_ref'::text)::regclass) | |
| frd_text | text | ||
| frd_class_base | integer |
Tables referencing this one via Foreign Key Constraints:
Forms content
| F-Key | Name | Type | Description |
|---|---|---|---|
| fo_id | integer | PRIMARY KEY DEFAULT nextval(('s_form'::text)::regclass) | |
| formdef.fr_id | fo_fr_id | integer | |
| fo_pos | integer | ||
| fo_label | text | ||
| fo_formula | text |
| F-Key | Name | Type | Description |
|---|---|---|---|
| name | text | PRIMARY KEY | |
| include_file | text | NOT NULL |
| F-Key | Name | Type | Description |
|---|---|---|---|
| fr_id | integer | PRIMARY KEY DEFAULT nextval(('s_formdef'::text)::regclass) | |
| fr_label | text |
Tables referencing this one via Foreign Key Constraints:
Table temporaire pour l'importation des banques en format CSV
| F-Key | Name | Type | Description |
|---|---|---|---|
| code | text | NOT NULL | |
| date_exec | date | NOT NULL | |
| date_valeur | date | NOT NULL | |
| montant | numeric(20,4) | NOT NULL | |
| devise | text | ||
| compte_ordre | text | ||
| detail | text | ||
| num_compte | text | ||
| poste_comptable | text | ||
| bq_account | integer | NOT NULL | |
| jrn | integer | NOT NULL | |
| status | character varying(1) |
DEFAULT 'n'::character varying
Status doit être w pour en attente, t pour transfèrer ou d à effacer |
| Name | Constraint |
|---|---|
| chk_status | CHECK ((((((status)::text = 'n'::text) OR ((status)::text = 'w'::text)) OR ((status)::text = 'd'::text)) OR ((status)::text = 't'::text))) |
| F-Key | Name | Type | Description |
|---|---|---|---|
| iv_id | integer | PRIMARY KEY DEFAULT nextval(('s_invoice'::text)::regclass) | |
| iv_name | text | NOT NULL | |
| iv_file | oid |
join between the card and the attribut definition
| F-Key | Name | Type | Description |
|---|---|---|---|
| jft_id | integer | PRIMARY KEY DEFAULT nextval(('s_jnt_fic_att_value'::text)::regclass) | |
| fiche.f_id | f_id | integer | |
| attr_def.ad_id | ad_id | integer |
Tables referencing this one via Foreign Key Constraints:
join between the family card and the attribut definition
| F-Key | Name | Type | Description |
|---|---|---|---|
| fiche_def.fd_id | fd_id | integer | |
| attr_def.ad_id | ad_id | integer |
Journal: content one line for a group of accountancy writing
| F-Key | Name | Type | Description |
|---|---|---|---|
| jr_id | integer | PRIMARY KEY DEFAULT nextval(('s_jrn'::text)::regclass) | |
| jrn_def.jrn_def_id | jr_def_id | integer | PRIMARY KEY |
| jr_montant | numeric(20,4) | NOT NULL | |
| jr_comment | text | ||
| jr_date | date | ||
| jr_grpt_id | integer | NOT NULL | |
| jr_internal | text | ||
| jr_tech_date | timestamp without time zone | NOT NULL DEFAULT now() | |
| jr_tech_per | integer | NOT NULL | |
| jrn_ech | date | ||
| jr_ech | date | ||
| jr_rapt | text | ||
| jr_valid | boolean | DEFAULT true | |
| jr_opid | integer | ||
| jr_c_opid | integer | ||
| jr_pj | oid | ||
| jr_pj_name | text | ||
| jr_pj_type | text |
Possible action when we are in journal (menu)
| F-Key | Name | Type | Description |
|---|---|---|---|
| ja_id | integer | PRIMARY KEY DEFAULT nextval(('s_jrnaction'::text)::regclass) | |
| ja_name | text | NOT NULL | |
| ja_desc | text | ||
| ja_url | text | NOT NULL | |
| ja_action | text | NOT NULL | |
| ja_lang | text | DEFAULT 'FR'::text | |
| jrn_type.jrn_type_id | ja_jrn_type | character(3) |
Definition of a journal, his properties
| F-Key | Name | Type | Description |
|---|---|---|---|
| jrn_def_id | integer | PRIMARY KEY DEFAULT nextval(('s_jrn_def'::text)::regclass) | |
| jrn_def_name | text | UNIQUE NOT NULL | |
| jrn_def_class_deb | text | ||
| jrn_def_class_cred | text | ||
| jrn_def_fiche_deb | text | ||
| jrn_def_fiche_cred | text | ||
| jrn_deb_max_line | integer | DEFAULT 1 | |
| jrn_cred_max_line | integer | DEFAULT 1 | |
| jrn_def_ech | boolean | DEFAULT false | |
| jrn_def_ech_lib | text | ||
| jrn_type.jrn_type_id | jrn_def_type | character(3) | NOT NULL |
| jrn_def_code | text | NOT NULL |
Tables referencing this one via Foreign Key Constraints:
Rapprochement between operation
| F-Key | Name | Type | Description |
|---|---|---|---|
| jra_id | integer | PRIMARY KEY DEFAULT nextval(('s_jrn_rapt'::text)::regclass) | |
| jr_id | integer | NOT NULL | |
| jra_concerned | integer | NOT NULL |
Type of journal (Sell, Buy, Financial...)
| F-Key | Name | Type | Description |
|---|---|---|---|
| jrn_type_id | character(3) | PRIMARY KEY | |
| jrn_desc | text |
Tables referencing this one via Foreign Key Constraints:
Journal: content one line for each accountancy writing
| F-Key | Name | Type | Description |
|---|---|---|---|
| j_id | integer | PRIMARY KEY DEFAULT nextval(('s_jrn_op'::text)::regclass) | |
| j_date | date | DEFAULT now() | |
| j_montant | numeric(20,4) | ||
| tmp_pcmn.pcm_val | j_poste | poste_comptable | NOT NULL |
| j_grpt | integer | NOT NULL | |
| j_rapt | text | ||
| jrn_def.jrn_def_id | j_jrn_def | integer | NOT NULL |
| j_debit | boolean | DEFAULT true | |
| j_text | text | ||
| j_centralized | boolean | DEFAULT false | |
| j_internal | text | ||
| j_tech_user | text | NOT NULL | |
| j_tech_date | timestamp without time zone | NOT NULL DEFAULT now() | |
| j_tech_per | integer | NOT NULL | |
| j_qcode | text |
| F-Key | Name | Type | Description |
|---|---|---|---|
| pr_id | text | PRIMARY KEY | |
| pr_value | text |
| F-Key | Name | Type | Description |
|---|---|---|---|
| p_code | text | PRIMARY KEY | |
| p_value | text | ||
| p_comment | text |
Currency conversion
| F-Key | Name | Type | Description |
|---|---|---|---|
| pm_id | integer | DEFAULT nextval(('s_currency'::text)::regclass) | |
| pm_code | character(3) | PRIMARY KEY | |
| pm_rate | numeric(20,4) |
Periode definition
| F-Key | Name | Type | Description |
|---|---|---|---|
| p_id | integer | PRIMARY KEY DEFAULT nextval(('s_periode'::text)::regclass) | |
| p_start | date | UNIQUE NOT NULL | |
| p_end | date | ||
| p_exercice | text | NOT NULL DEFAULT to_char(now(), 'YYYY'::text) | |
| p_closed | boolean | DEFAULT false | |
| p_central | boolean | DEFAULT false |
| Name | Constraint |
|---|---|
| $1 | CHECK ((p_end >= p_start)) |
Contains about invoice for customer
| F-Key | Name | Type | Description |
|---|---|---|---|
| qs_id | integer | PRIMARY KEY DEFAULT nextval(('s_quantity'::text)::regclass) | |
| qs_internal | text | NOT NULL | |
| qs_fiche | integer | NOT NULL | |
| qs_quantite | integer | NOT NULL | |
| qs_price | numeric(20,4) | ||
| qs_vat | numeric(20,4) | ||
| qs_vat_code | integer | ||
| qs_client | integer | NOT NULL |
About the goods
| F-Key | Name | Type | Description |
|---|---|---|---|
| sg_id | integer | PRIMARY KEY DEFAULT nextval(('s_stock_goods'::text)::regclass) | |
| j_id | integer | ||
| f_id | integer | NOT NULL | |
| sg_code | text | ||
| sg_quantity | integer | ||
| sg_type | character(1) | NOT NULL DEFAULT 'c'::bpchar | |
| sg_date | date | ||
| sg_tech_date | date | DEFAULT now() | |
| sg_tech_user | text |
| Name | Constraint |
|---|---|
| stock_goods_sg_type | CHECK (((sg_type = 'c'::bpchar) OR (sg_type = 'd'::bpchar))) |
Plan comptable minimum normalisé
| F-Key | Name | Type | Description |
|---|---|---|---|
| pcm_val | poste_comptable | PRIMARY KEY | |
| pcm_lib | text | ||
| pcm_val_parent | poste_comptable | ||
| pcm_country | character(2) | NOT NULL DEFAULT 'BE'::bpchar |
Tables referencing this one via Foreign Key Constraints:
Rate of vat
| F-Key | Name | Type | Description |
|---|---|---|---|
| tva_id | integer | NOT NULL | |
| tva_label | text | NOT NULL | |
| tva_rate | numeric(8,4) | NOT NULL DEFAULT 0.0 | |
| tva_comment | text | ||
| tva_poste | text |
The user's local parameter
| F-Key | Name | Type | Description |
|---|---|---|---|
| user_id | text |
PRIMARY KEY
user's login |
|
| parameter_type | text |
PRIMARY KEY
the type of parameter |
|
| parameter_value | text |
the value of parameter |
| F-Key | Name | Type | Description |
|---|---|---|---|
| ua_id | integer | PRIMARY KEY DEFAULT nextval(('s_user_act'::text)::regclass) | |
| ua_login | text | ||
| action.ac_id | ua_act_id | integer |
| F-Key | Name | Type | Description |
|---|---|---|---|
| uj_id | integer | PRIMARY KEY DEFAULT nextval(('s_user_jrn'::text)::regclass) | |
| uj_login | text | ||
| jrn_def.jrn_def_id | uj_jrn_id | integer | |
| uj_priv | text |
| F-Key | Name | Type | Description |
|---|---|---|---|
| val | integer |
| F-Key | Name | Type | Description |
|---|---|---|---|
| f_id | integer | ||
| name | text | ||
| quick_code | text | ||
| tva_num | text | ||
| poste_comptable | text | ||
| rue | text | ||
| code_postal | text | ||
| pays | text | ||
| telephone | text | ||
| text |
SELECT a.f_id
, a.av_text AS name
, a1.av_text AS quick_code
, b.av_text AS tva_num
, c.av_text AS poste_comptable
, d.av_text AS rue
, e.av_text AS code_postal
, f.av_text AS pays
, g.av_text AS telephone
, h.av_text AS email
FROM (
(
(
(
(
(
(
(
(
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 1)
) a
JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 13)
) b
USING (f_id)
)
JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 23)
) a1
USING (f_id)
)
JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 5)
) c
USING (f_id)
)
JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 14)
) d
USING (f_id)
)
JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 15)
) e
USING (f_id)
)
JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 16)
) f
USING (f_id)
)
JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 17)
) g
USING (f_id)
)
LEFT JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 18)
) h
USING (f_id)
)
WHERE (a.frd_id = 9);
| F-Key | Name | Type | Description |
|---|---|---|---|
| f_id | integer | ||
| fd_id | integer | ||
| vw_name | text | ||
| vw_sell | text | ||
| vw_buy | text | ||
| tva_code | text | ||
| tva_id | integer | ||
| tva_rate | numeric(8,4) | ||
| tva_label | text | ||
| vw_addr | text | ||
| vw_cp | text | ||
| quick_code | text | ||
| frd_id | integer |
SELECT a.f_id
, a.fd_id
, a.av_text AS vw_name
, b.av_text AS vw_sell
, c.av_text AS vw_buy
, d.av_text AS tva_code
, tva_rate.tva_id
, tva_rate.tva_rate
, tva_rate.tva_label
, e.av_text AS vw_addr
, f.av_text AS vw_cp
, j.av_text AS quick_code
, fiche_def.frd_id
FROM (
(
(
(
(
(
(
(
(
SELECT fiche.f_id
, fiche.fd_id
, attr_value.av_text
FROM (
(
(fiche
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
JOIN attr_def
USING (ad_id)
)
WHERE (jnt_fic_att_value.ad_id = 1)
) a
LEFT JOIN (
SELECT fiche.f_id
, attr_value.av_text
FROM (
(
(fiche
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
JOIN attr_def
USING (ad_id)
)
WHERE (jnt_fic_att_value.ad_id = 6)
) b
ON (
(a.f_id = b.f_id)
)
)
LEFT JOIN (
SELECT fiche.f_id
, attr_value.av_text
FROM (
(
(fiche
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
JOIN attr_def
USING (ad_id)
)
WHERE (jnt_fic_att_value.ad_id = 7)
) c
ON (
(a.f_id = c.f_id)
)
)
LEFT JOIN (
SELECT fiche.f_id
, attr_value.av_text
FROM (
(
(fiche
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
JOIN attr_def
USING (ad_id)
)
WHERE (jnt_fic_att_value.ad_id = 2)
) d
ON (
(a.f_id = d.f_id)
)
)
LEFT JOIN (
SELECT fiche.f_id
, attr_value.av_text
FROM (
(
(fiche
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
JOIN attr_def
USING (ad_id)
)
WHERE (jnt_fic_att_value.ad_id = 14)
) e
ON (
(a.f_id = e.f_id)
)
)
LEFT JOIN (
SELECT fiche.f_id
, attr_value.av_text
FROM (
(
(fiche
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
JOIN attr_def
USING (ad_id)
)
WHERE (jnt_fic_att_value.ad_id = 15)
) f
ON (
(a.f_id = f.f_id)
)
)
LEFT JOIN (
SELECT fiche.f_id
, attr_value.av_text
FROM (
(
(fiche
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
JOIN attr_def
USING (ad_id)
)
WHERE (jnt_fic_att_value.ad_id = 23)
) j
ON (
(a.f_id = j.f_id)
)
)
LEFT JOIN tva_rate
ON (
(d.av_text =
(tva_rate.tva_id)::text
)
)
)
JOIN fiche_def
USING (fd_id)
);
all the attributs for card family
| F-Key | Name | Type | Description |
|---|---|---|---|
| fd_id | integer | ||
| ad_id | integer | ||
| ad_text | text | ||
| av_text | text | ||
| fd_class_base | poste_comptable | ||
| fd_label | text | ||
| fd_create_account | boolean | ||
| frd_id | integer |
SELECT jnt_fic_attr.fd_id
, jnt_fic_attr.ad_id
, attr_def.ad_text
, attr_value.av_text
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def.frd_id
FROM (
(
(
(
(jnt_fic_att_value
JOIN attr_value
USING (jft_id)
)
JOIN fiche
USING (f_id)
)
JOIN jnt_fic_attr
USING (fd_id)
)
JOIN attr_def
ON (
(attr_def.ad_id = jnt_fic_attr.ad_id)
)
)
JOIN fiche_def
USING (fd_id)
);
minimum attribut for reference card
| F-Key | Name | Type | Description |
|---|---|---|---|
| frd_id | integer | ||
| ad_id | integer | ||
| ad_text | text | ||
| frd_text | text | ||
| frd_class_base | integer |
SELECT attr_min.frd_id
, attr_min.ad_id
, attr_def.ad_text
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
FROM (
(attr_min
JOIN attr_def
USING (ad_id)
)
JOIN fiche_def_ref
USING (frd_id)
);
| F-Key | Name | Type | Description |
|---|---|---|---|
| f_id | integer | ||
| j_poste | text | ||
| j_qcode | text |
SELECT a.f_id
, a.av_text AS j_poste
, b.av_text AS j_qcode
FROM (
(
SELECT jnt_fic_att_value.f_id
, attr_value.av_text
FROM (attr_value
JOIN jnt_fic_att_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 5)
) a
JOIN (
SELECT jnt_fic_att_value.f_id
, attr_value.av_text
FROM (attr_value
JOIN jnt_fic_att_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 23)
) b
USING (f_id)
);
| F-Key | Name | Type | Description |
|---|---|---|---|
| f_id | integer | ||
| name | text | ||
| quick_code | text | ||
| tva_num | text | ||
| poste_comptable | text | ||
| rue | text | ||
| code_postal | text | ||
| pays | text | ||
| telephone | text | ||
| text |
SELECT a.f_id
, a.av_text AS name
, a1.av_text AS quick_code
, b.av_text AS tva_num
, c.av_text AS poste_comptable
, d.av_text AS rue
, e.av_text AS code_postal
, f.av_text AS pays
, g.av_text AS telephone
, h.av_text AS email
FROM (
(
(
(
(
(
(
(
(
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 1)
) a
JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 13)
) b
USING (f_id)
)
JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 23)
) a1
USING (f_id)
)
JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 5)
) c
USING (f_id)
)
JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 14)
) d
USING (f_id)
)
JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 15)
) e
USING (f_id)
)
JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 16)
) f
USING (f_id)
)
JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 17)
) g
USING (f_id)
)
LEFT JOIN (
SELECT jnt_fic_att_value.jft_id
, fiche.f_id
, fiche_def.frd_id
, fiche.fd_id
, fiche_def.fd_class_base
, fiche_def.fd_label
, fiche_def.fd_create_account
, fiche_def_ref.frd_text
, fiche_def_ref.frd_class_base
, jnt_fic_att_value.ad_id
, attr_value.av_text
FROM (
(
(
(fiche
JOIN fiche_def
USING (fd_id)
)
JOIN fiche_def_ref
USING (frd_id)
)
JOIN jnt_fic_att_value
USING (f_id)
)
JOIN attr_value
USING (jft_id)
)
WHERE (jnt_fic_att_value.ad_id = 18)
) h
USING (f_id)
)
WHERE (a.frd_id = 8);
declare nParent tmp_pcmn.pcm_val_parent%type; nCount integer; begin select count(*) into nCount from tmp_pcmn where pcm_val=p_id; if nCount = 0 then nParent=account_parent(p_id); insert into tmp_pcmn (pcm_val,pcm_lib,pcm_val_parent) values (p_id, p_name,nParent); end if; return; end ;
declare l_auto bool; begin select fd_create_account into l_auto from fiche_def where fd_id=p_fd_id; if l_auto is null then l_auto:=false; end if; return l_auto; end;
declare class_base poste_comptable; maxcode int8; begin -- Get the class base select fd_class_base into class_base from fiche_def join fiche using (fd_id) where f_id=p_f_id; raise notice 'class base %',class_base; select max(pcm_val) into maxcode from tmp_pcmn where pcm_val = class_base; if maxcode = class_base then maxcode=class_base*1000+1; end if; raise notice 'Max code %',maxcode; return maxcode+1; end;
declare nParent tmp_pcmn.pcm_val_parent%type; sName varchar; nNew tmp_pcmn.pcm_val%type; bAuto bool; nFd_id integer; nCount integer; begin -- if p_value empty if length(trim(p_account)) != 0 then -- does the account exist ? select * into nCount from tmp_pcmn where pcm_val=p_account; if nCount !=0 then -- retrieve name select av_text into sName from attr_value join jnt_fic_att_value using (jft_id) where ad_id=1 and f_id=p_f_id; -- get parent nParent:=account_parent(p_account); -- account doesn't exist we need to add id insert into tmp_pcmn(pcm_val,pcm_lib,pcm_val_parent) values (p_account,sName,nParent); -- insert as card's attribute perform attribut_insert(p_f_id,5,to_char(nNew,'999999999999')); end if; else select fd_id into nFd_id from fiche where f_id=p_f_id; bAuto:= account_auto(nFd_id); if bAuto = true then -- create automatically the account -- compute the next account nNew:=account_compute(p_f_id); raise debug 'nNew %', nNew; -- retrieve name select av_text into sName from attr_value join jnt_fic_att_value using (jft_id) where ad_id=1 and f_id=p_f_id; -- get parent nParent:=account_parent(nNew); -- account doesn't exist we need to add id perform account_add (nNew,sName); -- insert as card's attribute perform attribut_insert(p_f_id,5,to_char(nNew,'999999999999')); else perform attribut_insert(p_f_id,5,null); end if; end if; return 0; end;
declare nParent tmp_pcmn.pcm_val_parent%type; sParent varchar; nCount integer; begin sParent:=to_char(p_account,'9999999999999999'); sParent:=trim(sParent); nParent:=0; while nParent = 0 loop select count(*) into nCount from tmp_pcmn where pcm_val = to_number(sParent,'9999999999999999'); if nCount != 0 then nParent:=to_number(sParent,'9999999999999999'); end if; sParent:= substr(sParent,1,length(sParent)-1); if length(sParent) <= 0 then raise exception 'Impossible de trouver le compte parent pour %',p_account; end if; end loop; return nParent; end;
declare nMax fiche.f_id%type; nCount integer; nParent tmp_pcmn.pcm_val_parent%type; sName varchar; nJft_id attr_value.jft_id%type; begin -- if p_value empty if length(trim(p_account)) != 0 then -- does the account exist ? select count(*) into nCount from tmp_pcmn where pcm_val=p_account; if nCount = 0 then -- retrieve name select av_text into sName from attr_value join jnt_fic_att_value using (jft_id) where ad_id=1 and f_id=p_f_id; -- get parent nParent:=fiche_account_parent(p_f_id); -- account doesn't exist we need to add id insert into tmp_pcmn(pcm_val,pcm_lib,pcm_val_parent) values (p_account,sName,nParent); end if; end if; -- we retrieve jft_id select jft_id into njft_id from jnt_fic_att_value where f_id=p_f_id and ad_id=5; -- we update the account update attr_value set av_text=p_account where jft_id=njft_id; return njft_id; end;
declare
n_jft_id integer;
begin
select nextval('s_jnt_fic_att_value') into n_jft_id;
insert into jnt_fic_att_value (jft_id,f_id,ad_id) values (n_jft_id,p_f_id,p_ad_id);
insert into attr_value (jft_id,av_text) values (n_jft_id,p_value);
return;
end;
declare n_poste fiche_def.fd_class_base%type; begin select fd_class_base into n_poste from fiche_def join fiche using (fd_id) where f_id=p_f_id; if not FOUND then raise exception 'Invalid fiche card_class_base(%)',p_f_id; end if; return n_poste; end;
declare amount_jrnx_debit numeric; amount_jrnx_credit numeric; amount_jrn numeric; begin select sum (j_montant) into amount_jrnx_credit from jrnx where j_grpt=p_grpt and j_debit=false; select sum (j_montant) into amount_jrnx_debit from jrnx where j_grpt=p_grpt and j_debit=true; select jr_montant into amount_jrn from jrn where jr_grpt_id=p_grpt; if ( amount_jrnx_debit != amount_jrnx_credit ) then return abs(amount_jrnx_debit-amount_jrnx_credit); end if; if ( amount_jrn != amount_jrnx_credit) then return -1*abs(amount_jrn - amount_jrnx_credit); end if; return 0; end;
declare ret poste_comptable; begin select fd_class_base into ret from fiche_def join fiche using (fd_id) where f_id=p_f_id; if not FOUND then raise exception '% N''existe pas',p_f_id; end if; return ret; end;
declare sCode varchar; nCount_qcode integer; begin sCode=trim(p_qcode); -- if p_qcode is empty try to find one if length(sCode) = 0 or p_qcode is null then select count(*) into nCount_qcode from vw_poste_qcode where j_poste=p_poste; -- if we find only one q_code for a accountancy account -- then retrieve it if nCount_qcode = 1 then select j_qcode into sCode from vw_poste_qcode where j_poste=p_poste; else sCode=NULL; end if; end if; if p_montant = 0.0 then return; end if; insert into jrnx ( j_date, j_montant, j_poste, j_grpt, j_jrn_def, j_debit, j_tech_user, j_tech_per, j_qcode ) values ( to_date(p_date,'DD.MM.YYYY'), p_montant, p_poste, p_grpt, p_jrn_def, p_debit, p_tech_user, p_tech_per, sCode ); return; end;
declare fid_client integer; fid_good integer; begin select f_id into fid_client from attr_value join jnt_fic_att_value using (jft_id) where ad_id=23 and av_text=p_client; select f_id into fid_good from attr_value join jnt_fic_att_value using (jft_id) where ad_id=23 and av_text=p_fiche; insert into quant_sold (qs_internal,qs_fiche,qs_quantite,qs_price,qs_vat,qs_vat_code,qs_client) values (p_internal,fid_good,p_quant,p_price,p_vat,p_vat_code,fid_client); return; end;
declare
ns integer;
nExist integer;
tText text;
begin
tText := upper(trim(tav_text));
tText := replace(tText,' ','');
loop
-- take the next sequence
select nextval('s_jnt_fic_att_value') into ns;
if length (tText) = 0 or tText is null then
tText := 'FID'||ns;
end if;
-- av_text already used ?
select count(*) into nExist
from jnt_fic_att_value join attr_value using (jft_id)
where
ad_id=23 and av_text=upper(tText);
if nExist = 0 then
exit;
end if;
tText:='FID'||ns;
end loop;
-- insert into table jnt_fic_att_value
insert into jnt_fic_att_value values (ns,nf_id,23);
-- insert value into attr_value
insert into attr_value values (ns,upper(tText));
return ns;
end;
plpgsql_validator
declare diff numeric; tt integer; begin if TG_OP = 'INSERT' then tt=NEW.jr_grpt_id; diff:=check_balance(tt); if diff != 0 then raise exception 'balance error %',diff ; end if; return NEW; end if; end;
BEGIN
execute 'create sequence seq_doc_type_'||NEW.dt_id;
raise notice 'Creating sequence seq_doc_type_%',NEW.dt_id;
RETURN NEW;
END;
BEGIN
execute 'create sequence s_jrn_'||NEW.jrn_def_id;
raise notice 'Creating sequence s_jrn_%',NEW.jrn_def_id;
RETURN NEW;
END;
declare
modified import_tmp%ROWTYPE;
begin
modified:=NEW;
modified.devise=replace(new.devise,'"','');
modified.poste_comptable=replace(new.poste_comptable,'"','');
modified.compte_ordre=replace(NEW.COMPTE_ORDRE,'"','');
modified.detail=replace(NEW.DETAIL,'"','');
modified.num_compte=replace(NEW.NUM_COMPTE,'"','');
return modified;
end;
declare
modified format_csv_banque%ROWTYPE;
begin
modified.name=trim(NEW.NAME);
modified.include_file=trim(new.include_file);
if ( length(modified.name) = 0 ) then
modified.name=null;
end if;
if ( length(modified.include_file) = 0 ) then
modified.include_file=null;
end if;
return modified;
end;
declare p_tva_id alias for $1; nCount integer; begin nCount=0; select count(*) into nCount from quant_sold where qs_vat_code=p_tva_id; if nCount = 0 then delete from tva_rate where tva_id=p_tva_id; end if; return; end;
declare
p_tva_id alias for $1;
p_tva_label alias for $2;
p_tva_rate alias for $3;
p_tva_comment alias for $4;
p_tva_poste alias for $5;
debit text;
credit text;
nCount integer;
begin
if length(trim(p_tva_label)) = 0 then
return 3;
end if;
select count(*) into nCount from tva_rate
where tva_id=p_tva_id;
if nCount != 0 then
return 5;
end if;
if length(trim(p_tva_poste)) != 0 then
if position (',' in p_tva_poste) = 0 then return 4; end if;
debit = split_part(p_tva_poste,',',1);
credit = split_part(p_tva_poste,',',2);
select count(*) into nCount from tmp_pcmn where pcm_val=debit;
if nCount = 0 then return 4; end if;
select count(*) into nCount from tmp_pcmn where pcm_val=credit;
if nCount = 0 then return 4; end if;
end if;
insert into tva_rate(tva_id,tva_label,tva_rate,tva_comment,tva_poste)
values (p_tva_id,p_tva_label,p_tva_rate,p_tva_comment,p_tva_poste);
return 0;
end;
declare
p_tva_id alias for $1;
p_tva_label alias for $2;
p_tva_rate alias for $3;
p_tva_comment alias for $4;
p_tva_poste alias for $5;
debit text;
credit text;
nCount integer;
begin
if length(trim(p_tva_label)) = 0 then
return 3;
end if;
if length(trim(p_tva_poste)) != 0 then
if position (',' in p_tva_poste) = 0 then return 4; end if;
debit = split_part(p_tva_poste,',',1);
credit = split_part(p_tva_poste,',',2);
select count(*) into nCount from tmp_pcmn where pcm_val=debit;
if nCount = 0 then return 4; end if;
select count(*) into nCount from tmp_pcmn where pcm_val=credit;
if nCount = 0 then return 4; end if;
end if;
update tva_rate set tva_label=p_tva_label,tva_rate=p_tva_rate,tva_comment=p_tva_comment,tva_poste=p_tva_poste
where tva_id=p_tva_id;
return 0;
end;
declare
ns integer;
nExist integer;
tText text;
old_qcode varchar;
begin
-- get current value
select av_text into old_qcode from attr_value where jft_id=njft_id;
-- av_text didn't change so no update
if tav_text = upper( trim(old_qcode)) then
return 0;
end if;
tText := trim(upper(tav_text));
tText := replace(tText,' ','');
if length ( tText) = 0 or tText is null then
return 0;
end if;
ns := njft_id;
loop
-- av_text already used ?
select count(*) into nExist
from jnt_fic_att_value join attr_value using (jft_id)
where
ad_id=23 and av_text=upper(tText);
if nExist = 0 then
exit;
end if;
if tText = 'FID'||ns then
-- take the next sequence
select nextval('s_jnt_fic_att_value') into ns;
end if;
tText :='FID'||ns;
end loop;
update attr_value set av_text = tText where jft_id=njft_id;
update jrnx set j_qcode=tText where j_qcode = old_qcode;
return ns;
end;
Generated by PostgreSQL Autodoc