Dumped on 2026-01-10

Index of database - mod1

Schema comptaproc

Function: comptaproc.account_add(p_id public.account_type(44), p_name character varying)

Returns: text

Language: PLPGSQL

declare
    nParent tmp_pcmn.pcm_val_parent%type;
    nCount integer;
    sReturn text;
begin
    -- patch 189
    sReturn:= format_account(p_id);
    select count(*) into nCount from tmp_pcmn where pcm_val=sReturn;
    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) returning pcm_val into sReturn;
    end if;
    return sReturn;
end ;

Function: comptaproc.account_alphanum()

Returns: boolean

Language: PLPGSQL

declare
	l_auto bool;
begin
	l_auto := true;
	select pr_value into l_auto from parameter where pr_id='MY_ALPHANUM';
	if l_auto = 'N' or l_auto is null then
		l_auto:=false;
	end if;
	return l_auto;
end;

Function: comptaproc.account_auto(p_fd_id integer)

Returns: boolean

Language: PLPGSQL

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;

Function: comptaproc.account_compute(p_f_id integer)

Returns: account_type(44)

Language: PLPGSQL

declare
    class_base fiche_def.fd_class_base%type;
    maxcode numeric;
    sResult account_type;
    bAlphanum bool;
    sName text;
    nCount integer;
    sNumber text;
begin
    -- patch 189
    select fd_class_base into class_base
    from
        fiche_def join fiche using (fd_id)
    where
            f_id=p_f_id;

    bAlphanum := account_alphanum();
    if bAlphanum = false  then
        select max (pcm_val::numeric) into maxcode
        from tmp_pcmn
        where pcm_val_parent = class_base and pcm_val !~* '[[:alpha:]]'  ;
        if maxcode is null	or length(maxcode::text) < length(class_base)+4 then
            maxcode:=class_base::numeric*10000+1;
        else
            select max (pcm_val::numeric) into maxcode
            from tmp_pcmn
            where pcm_val !~* '[[:alpha:]]'
              and pcm_val_parent = class_base
              and substr(pcm_val::text,1,length(class_base))=class_base;

            sNumber := substr(maxcode::text,length(class_base)+1);
            nCount := sNumber::numeric+1;
            sNumber := lpad (nCount::text,4,'0');

            maxcode:=class_base||sNumber;
        end if;
        sResult:=maxcode::account_type;
    else
        -- if alphanum, use name
        select ad_value into sName from fiche_detail where f_id=p_f_id and ad_id=1;
        if sName is null then
            raise exception 'Cannot compute an accounting without the name of the card for %',p_f_id;
        end if;
        sResult := account_compute_alpha(class_base,sName);
    end if;
    return sResult;
end;

Function: comptaproc.account_compute_alpha(p_class text, p_name text)

Returns: account_type(44)

Language: PLPGSQL

declare
    sResult account_type;
    sAccount account_type;
    sFormatedAccount account_type;
    nCount int;
    idx int :=0;
begin
    sFormatedAccount := comptaproc.format_account(p_name);

    sAccount := p_class||substring(sFormatedAccount for 5);
    nCount := 0;
    loop
        select count(*) into nCount from tmp_pcmn where pcm_val = comptaproc.format_account(sAccount);

        exit when nCount = 0;
        idx := idx + 1;
        sAccount := p_class || substring(sFormatedAccount for 5)||idx::text;
    end loop;
    sResult := comptaproc.format_account(sAccount);
    return sResult;
end;

Function: comptaproc.account_insert(p_f_id integer, p_account text)

Returns: text

Language: PLPGSQL

declare
    nParent tmp_pcmn.pcm_val_parent%type;
    sName varchar;
    sNew tmp_pcmn.pcm_val%type;
    bAuto bool;
    nFd_id integer;
    sClass_Base fiche_def.fd_class_base%TYPE;
    nCount integer;
    first text;
    second text;
    s_account text;
begin
    -- patch 189
    -- accouting is given
    if p_account is not null and length(trim(p_account)) != 0 then
        -- if there is coma in p_account, treat normally
        if position (',' in p_account) = 0 then
            s_account := format_account(substr( p_account,1 , 40)::account_type);
            select count(*)  into nCount from tmp_pcmn where pcm_val=s_account::account_type;
            if nCount !=0  then
                perform attribut_insert(p_f_id,5,s_account);
            else
                -- account doesn't exist, create it
                select ad_value into sName from
                    fiche_detail
                where
                        ad_id=1 and f_id=p_f_id;
                -- retrieve parent account from card
                select fd_class_base::account_type into nParent from fiche_def where fd_id=(select fd_id from fiche where f_id=p_f_id);
                if nParent = null or nParent = '' then
                    nParent:=account_parent(s_account::account_type);
                end if;
                insert into tmp_pcmn(pcm_val,pcm_lib,pcm_val_parent) values (s_account::account_type,sName,nParent);
                perform attribut_insert(p_f_id,5,s_account);

            end if;
            return s_account;
        else
            -- there is 2 accounts separated by a comma
            first := split_part(p_account,',',1);
            second := split_part(p_account,',',2);
            -- check there is no other coma

            if  position (',' in first) != 0 or position (',' in second) != 0 then
                raise exception 'Too many comas, invalid account';
            end if;
            perform attribut_insert(p_f_id,5,p_account);

        end if;
        return s_account;
    end if;

    select fd_id into nFd_id from fiche where f_id=p_f_id;
    bAuto:= account_auto(nFd_id);

    select fd_class_base into sClass_base from fiche_def where fd_id=nFd_id;
    if bAuto = true and sClass_base similar to '[[:digit:]]*'  then
        sNew:=account_compute(p_f_id);
        select ad_value into sName from
            fiche_detail
        where
                ad_id=1 and f_id=p_f_id;
        nParent:=sClass_Base::account_type;
        sNew := account_add  (sNew,sName);
        update tmp_pcmn set pcm_val_parent=nParent where pcm_val=sNew;
        perform attribut_insert(p_f_id,5,sNew);
        return sNew;
    else
        -- if there is an account_base then it is the default
        if trim(coalesce(sClass_base::text,'')) = '' then
            perform attribut_insert(p_f_id,5,null);
        else
            perform attribut_insert(p_f_id,5,sClass_base);
        end if;
        return sClass_base;
    end if;

    raise notice 'ai89.account_insert nothing done : error';

end;

Function: comptaproc.account_parent(p_account public.account_type(44))

Returns: account_type(44)

Language: PLPGSQL

declare
	sSubParent tmp_pcmn.pcm_val_parent%type;
	sResult tmp_pcmn.pcm_val_parent%type;
	nCount integer;
begin
	if p_account is NULL then
		return NULL;
	end if;
	sSubParent:=p_account;
	while true loop
		select count(*) into nCount
		from tmp_pcmn
		where
		pcm_val = sSubParent;
		if nCount != 0 then
			sResult:= sSubParent;
			exit;
		end if;
		sSubParent:= substr(sSubParent,1,length(sSubParent)-1);
		if length(sSubParent) <= 0 then
			raise exception 'Impossible de trouver le compte parent pour %',p_account;
		end if;
		raise notice 'sSubParent % % ',sSubParent,length(sSubParent);
	end loop;
	raise notice 'account_parent : Parent is %',sSubParent;
	return sSubParent;
end;

Function: comptaproc.account_update(p_f_id integer, p_account public.account_type(44))

Returns: integer

Language: PLPGSQL

declare
	nMax fiche.f_id%type;
	nCount integer;
	nParent tmp_pcmn.pcm_val_parent%type;
	sName varchar;
	first text;
	second text;
begin

	if length(trim(p_account)) != 0 then
		-- 2 accounts in card separated by comma
		if position (',' in p_account) = 0 then
			p_account := format_account(p_account);
			select count(*) into nCount from tmp_pcmn where pcm_val=p_account;
			if nCount = 0 then
				select ad_value into sName from
					fiche_detail
					where
					ad_id=1 and f_id=p_f_id;
					nParent:=account_parent(p_account);
					raise notice 'insert into tmp_pcmn % %',p_account,sName;
					insert into tmp_pcmn(pcm_val,pcm_lib,pcm_val_parent) values (p_account,sName,nParent);
			end if;
		else
			raise info 'presence of a comma';
			-- there is 2 accounts separated by a comma
			first := split_part(p_account,',',1);
			second := split_part(p_account,',',2);
			-- check there is no other coma
			raise info 'first value % second value %', first, second;
	
			if  position (',' in first) != 0 or position (',' in second) != 0 then
				raise exception 'Too many comas, invalid account';
			end if;
			-- check that both account are in PCMN

		end if;
	else
		-- account is null
		update fiche_detail set ad_value=null where f_id=p_f_id and ad_id=5 ;
		return 0;
	end if;

	update fiche_detail set ad_value=p_account where f_id=p_f_id and ad_id=5 ;

return 0;
end;

Function: comptaproc.action_gestion_ins_upd()

Returns: trigger

Language: PLPGSQL

begin
NEW.ag_title := substr(trim(NEW.ag_title),1,70);
NEW.ag_hour := substr(trim(NEW.ag_hour),1,5);
NEW.ag_owner := lower(NEW.ag_owner);
return NEW;
end;

Function: comptaproc.action_gestion_related_ins_up()

Returns: trigger

Language: PLPGSQL

declare
	nTmp bigint;
begin

if NEW.aga_least > NEW.aga_greatest then
	nTmp := NEW.aga_least;
	NEW.aga_least := NEW.aga_greatest;
	NEW.aga_greatest := nTmp;
end if;

if NEW.aga_least = NEW.aga_greatest then
	return NULL;
end if;

return NEW;

end;

Function: comptaproc.anc_correct_tvand()

Returns: void

Language: PLPGSQL

 
declare
        n_count numeric;
        i record;
        newrow_tva record;
begin
         for i in select * from operation_analytique where oa_jrnx_id_source is not null loop
         -- Get all the anc accounting from the base operation and insert the missing record for VAT 
                for newrow_tva in select *  from operation_analytique where j_id=i.oa_jrnx_id_source and po_id <> i.po_id loop
                    
                        -- check if the record is yet present
                        select count(*) into n_count from operation_analytique where  po_id=newrow_tva.po_id and oa_jrnx_id_source=i.oa_jrnx_id_source;

                        if n_count = 0 then
                          raise info 'insert operation analytique po_id = % oa_group = % ',i.po_id, i.oa_group;
                          insert into operation_analytique 
                          (po_id,oa_amount,oa_description,oa_debit,j_id,oa_group,oa_date,oa_jrnx_id_source,oa_positive)
                          values (newrow_tva.po_id,i.oa_amount,i.oa_description,i.oa_debit,i.j_id,i.oa_group,i.oa_date,i.oa_jrnx_id_source,i.oa_positive);
                        end if;
         
                end loop;

         
         end loop;
end;

Function: comptaproc.attribut_insert(p_f_id integer, p_ad_id integer, p_value character varying)

Returns: void

Language: PLPGSQL

declare 
	nResult bigint;
begin
	update fiche_detail set ad_value=p_value where ad_id=p_ad_id and f_id=p_f_id returning jft_id into nResult;
	if nResult is null then
		insert into fiche_detail (f_id,ad_id, ad_value) values (p_f_id,p_ad_id,p_value);
	end if;

return;
end;

Function: comptaproc.attribute_correct_order()

Returns: void

Language: PLPGSQL

declare
    crs_correct cursor for select A.jnt_id,A.jnt_order from jnt_fic_attr as A join jnt_fic_attr as B using (fd_id) where A.jnt_order=B.jnt_order and A.jnt_id > B.jnt_id;
    rec record;
begin
	open crs_correct;
	loop
	fetch crs_correct into rec;
	if NOT FOUND then
		close crs_correct;
		return;
	end if;
	update jnt_fic_attr set jnt_order=jnt_order + 1 where jnt_id = rec.jnt_id;
	end loop;
	close crs_correct;
	perform attribute_correct_order ();
end;

Function: comptaproc.card_after_delete()

Returns: trigger

Language: PLPGSQL


begin

	delete from action_gestion where f_id_dest = OLD.f_id;
	return OLD;

end;

Function: comptaproc.card_class_base(p_f_id integer)

Returns: text

Language: PLPGSQL

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;

Function: comptaproc.category_card_before_delete()

Returns: trigger

Language: PLPGSQL


begin
    if OLD.fd_id > 499000 then
        return null;
    end if;
    return OLD;

end;

Function: comptaproc.check_balance(p_grpt integer)

Returns: numeric

Language: PLPGSQL

declare
	amount_jrnx_debit numeric;
	amount_jrnx_credit numeric;
	amount_jrn numeric;
begin
	select coalesce(sum (j_montant),0) into amount_jrnx_credit
	from jrnx
		where
	j_grpt=p_grpt
	and j_debit=false;

	select coalesce(sum (j_montant),0) into amount_jrnx_debit
	from jrnx
		where
	j_grpt=p_grpt
	and j_debit=true;

	select coalesce(jr_montant,0) 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;

Function: comptaproc.check_periode()

Returns: trigger

Language: PLPGSQL

declare 
  nPeriode int;
  nExerciceLabel int;
begin
	nPeriode:=periode_exist(to_char(NEW.p_start,'DD.MM.YYYY'),NEW.p_id);
	if nPeriode <> -1 then
       raise info 'Overlap periode start % periode %',NEW.p_start,nPeriode;
		return null;
	end if;
	if new.p_exercice_label is null or trim (new.p_exercice_label ) = '' then
		new.p_exercice_label := new.p_exercice;
	end if;
	select count(*) into nExerciceLabel 
		from parm_periode 
		where 
		(p_exercice =new.p_exercice and p_exercice_label <> new.p_exercice_label) 
		or 
		(p_exercice <> new.p_exercice and p_exercice_label = new.p_exercice_label);
		
	if nExerciceLabel > 0 then
		raise exception 'a label cannot be on two exercices';
		return null;
	end if;

return NEW;
end;

Function: comptaproc.correct_sequence(p_sequence text, p_col text, p_table text)

Returns: integer

Language: PLPGSQL

Often the primary key is a sequence number and sometimes the value of the sequence is not synchronized with the primary key ( p_sequence : sequence name, p_col : col of the pk,p_table : concerned table

declare
last_sequence int8;
max_sequence int8;
n integer;
begin
	select count(*) into n from pg_class where relkind='S' and relname=lower(p_sequence);
	if n = 0 then
		raise exception  ' Unknow sequence  % ',p_sequence;
	end if;
	select count(*) into n from pg_class where relkind='r' and relname=lower(p_table);
	if n = 0 then
		raise exception ' Unknow table  % ',p_table;
	end if;

	execute 'select last_value   from '||p_sequence into last_sequence;
	raise notice 'Last value of the sequence is %', last_sequence;

	execute 'select max('||p_col||')  from '||p_table into max_sequence;
	if  max_sequence is null then
		max_sequence := 0;
	end if;
	raise notice 'Max value of the sequence is %', max_sequence;
	max_sequence:= max_sequence +1;	
	execute 'alter sequence '||p_sequence||' restart with '||max_sequence;
return 0;

end;

Function: comptaproc.create_missing_sequence()

Returns: integer

Language: PLPGSQL

declare
p_sequence text;
nSeq integer;
c1 cursor for select jrn_def_id from jrn_def;
begin
	open c1;
	loop
	   fetch c1 into nSeq;
	   if not FOUND THEN
	   	close c1;
	   	return 0;
	   end if;
	   p_sequence:='s_jrn_pj'||nSeq::text;
	execute 'create sequence '||p_sequence;
	end loop;
close c1;
return 0;

end;

Function: comptaproc.drop_index(p_constraint character varying)

Returns: void

Language: PLPGSQL

declare 
	nCount integer;
begin
	select count(*) into nCount from pg_indexes where indexname=p_constraint;
	if nCount = 1 then
	execute 'drop index '||p_constraint ;
	end if;
end;

Function: comptaproc.drop_it(p_constraint character varying)

Returns: void

Language: PLPGSQL

declare 
	nCount integer;
begin
	select count(*) into nCount from pg_constraint where conname=p_constraint;
	if nCount = 1 then
	execute 'alter table parm_periode drop constraint '||p_constraint ;
	end if;
end;

Function: comptaproc.extension_ins_upd()

Returns: trigger

Language: PLPGSQL

declare
 sCode text;
 sFile text;
begin
sCode:=trim(upper(NEW.ex_code));
sCode:=replace(sCode,' ','_');
sCode:=substr(sCode,1,15);
sCode=upper(sCode);
NEW.ex_code:=sCode;
sFile:=NEW.ex_file;
sFile:=replace(sFile,';','_');
sFile:=replace(sFile,'<','_');
sFile:=replace(sFile,'>','_');
sFile:=replace(sFile,'..','');
sFile:=replace(sFile,'&','');
sFile:=replace(sFile,'|','');



return NEW;

end;

Function: comptaproc.fiche_account_parent(p_f_id integer)

Returns: account_type(44)

Language: PLPGSQL

declare
ret tmp_pcmn.pcm_val%TYPE;
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;

Function: comptaproc.fiche_attribut_synchro(p_fd_id integer)

Returns: void

Language: PLPGSQL

declare
	-- this sql gives the f_id and the missing attribute (ad_id)
	list_missing cursor for select f_id,fd_id,ad_id,jnt_order from jnt_fic_attr join fiche as A using (fd_id) where fd_id=p_fd_id and ad_id not in (select ad_id from fiche join fiche_detail using (f_id) where fd_id=jnt_fic_attr.fd_id and A.f_id=f_id);
	rec record;
begin
	open list_missing;
	loop
	
	fetch list_missing into rec;
	IF NOT FOUND then
		exit;
	end if;
	
	-- now we insert into attr_value
	insert into fiche_detail (f_id,ad_id,ad_value) values (rec.f_id,rec.ad_id,null);
	end loop;
	close list_missing;
end;

Function: comptaproc.fiche_def_ins_upd()

Returns: trigger

Language: PLPGSQL

begin

if position (',' in NEW.fd_class_base) != 0 then
   NEW.fd_create_account='f';

end if;
return NEW;
end;

Function: comptaproc.fiche_detail_check()

Returns: trigger

Language: PLPGSQL

	BEGIN
		if new.ad_id = 23 and coalesce (new.ad_value,'') = '' then 
			raise exception  'QUICKCODE can not be empty';
		end if;
	if new.ad_id = 1 and coalesce (new.ad_value,'') = '' then 
			raise exception  'NAME can not be empty';
		end if;
	return new;
	END;

Function: comptaproc.fiche_detail_check_qcode()

Returns: trigger

Language: PLPGSQL

declare
    i record;
begin
    if NEW.ad_id=23 and NEW.ad_value != OLD.ad_value then
        update jrnx set j_qcode=NEW.ad_value where j_qcode = OLD.ad_value;
        update op_predef_detail set opd_poste=NEW.ad_value where opd_poste=OLD.ad_value;
        for i in select ad_id from attr_def where ad_type = 'card' or ad_id=25 loop
                update fiche_detail set ad_value=NEW.ad_value where ad_value=OLD.ad_value and ad_id=i.ad_id;
                if i.ad_id=19 then
                    update stock_goods set sg_code=NEW.ad_value where sg_code=OLD.ad_value;
                end if;

            end loop;
    end if;
    return NEW;
end;

Function: comptaproc.fill_quant_fin()

Returns: void

Language: PLPGSQL

declare
   sBank text;
   sCassa text;
   sCustomer text;
   sSupplier text;
   rec record;
   recBank record;
   recSupp_Cust record;
   nCount integer;
   nAmount numeric;
   nBank integer;
   nOther integer;
   nSupp_Cust integer;
begin
	select p_value into sBank from parm_code where p_code='BANQUE';
	select p_value into sCassa from parm_code where p_code='CAISSE';
	select p_value into sSupplier from parm_code where p_code='SUPPLIER';
	select p_value into sCustomer from parm_code where p_code='CUSTOMER';
	
	for rec in select jr_id,jr_grpt_id from jrn 
	    where jr_def_id in (select jrn_def_id from jrn_def where jrn_def_type='FIN')
		and jr_id not in (select jr_id from quant_fin)
	loop
		-- there are only 2 lines for bank operations
		-- first debit
		select count(j_id) into nCount from jrnx where j_grpt=rec.jr_grpt_id;
		if nCount > 2 then 
			raise notice 'Trop de valeur pour jr_grpt_id % count %',rec.jr_grpt_id,nCount;
			return;
		end if;
		nBank := 0; nOther:=0;
		for recBank in select  j_id, j_montant,j_debit,j_qcode,j_poste from jrnx where j_grpt=rec.jr_grpt_id
		loop
		if recBank.j_poste like sBank||'%' then
			-- retrieve f_id for bank
			select f_id into nBank from vw_poste_qcode where j_qcode=recBank.j_qcode;
			if recBank.j_debit = false then
				nAmount=recBank.j_montant*(-1);
			else 
				nAmount=recBank.j_montant;
			end if;
		else
			select f_id into nOther from vw_poste_qcode where j_qcode=recBank.j_qcode;
		end if;
		end loop;
		if nBank != 0 and nOther != 0 then
			insert into quant_fin (jr_id,qf_bank,qf_other,qf_amount) values (rec.jr_id,nBank,nOther,nAmount);
		end if;
	end loop;
	for rec in select jr_id,jr_grpt_id from jrn 
	    where jr_def_id in (select jrn_def_id from jrn_def where jrn_def_type='FIN') and jr_id not in (select jr_id from quant_fin)
	loop
		-- there are only 2 lines for bank operations
		-- first debit
		select count(j_id) into nCount from jrnx where j_grpt=rec.jr_grpt_id;
		if nCount > 2 then 
			raise notice 'Trop de valeur pour jr_grpt_id % count %',rec.jr_grpt_id,nCount;
			return;
		end if;
		nBank := 0; nOther:=0;
		for recBank in select  j_id, j_montant,j_debit,j_qcode,j_poste from jrnx where j_grpt=rec.jr_grpt_id
		loop
		if recBank.j_poste like sCassa||'%' then
			-- retrieve f_id for bank
			select f_id into nBank from vw_poste_qcode where j_qcode=recBank.j_qcode;
			if recBank.j_debit = false then
				nAmount=recBank.j_montant*(-1);
			else 
				nAmount=recBank.j_montant;
			end if;
		else
			select f_id into nOther from vw_poste_qcode where j_qcode=recBank.j_qcode;
		end if;
		end loop;
		if nBank != 0 and nOther != 0 then
			insert into quant_fin (jr_id,qf_bank,qf_other,qf_amount) values (rec.jr_id,nBank,nOther,nAmount);
		end if;
	end loop;

	for rec in select jr_id,jr_grpt_id from jrn 
	    where jr_def_id in (select jrn_def_id from jrn_def where jrn_def_type='FIN') and jr_id not in (select jr_id from quant_fin)
	loop
		-- there are only 2 lines for bank operations
		-- first debit
		select count(j_id) into nCount from jrnx where j_grpt=rec.jr_grpt_id;
		if nCount > 2 then 
			raise notice 'Trop de valeur pour jr_grpt_id % count %',rec.jr_grpt_id,nCount;
			return;
		end if;
		nSupp_Cust := 0; nOther:=0;
		for recSupp_Cust in select  j_id, j_montant,j_debit,j_qcode,j_poste from jrnx where j_grpt=rec.jr_grpt_id
		loop
		if recSupp_Cust.j_poste like sSupplier||'%'  then
			-- retrieve f_id for bank
			select f_id into nSupp_Cust from vw_poste_qcode where j_qcode=recSupp_Cust.j_qcode;
			if recSupp_Cust.j_debit = true then
				nAmount=recSupp_Cust.j_montant*(-1);
			else 
				nAmount=recSupp_Cust.j_montant;
			end if;
		else if  recSupp_Cust.j_poste like sCustomer||'%' then
			select f_id into nSupp_Cust from vw_poste_qcode where j_qcode=recSupp_Cust.j_qcode;
			if recSupp_Cust.j_debit = false then
				nAmount=recSupp_Cust.j_montant*(-1);
			else 
				nAmount=recSupp_Cust.j_montant;
			end if;
			else
			select f_id into nOther from vw_poste_qcode where j_qcode=recSupp_Cust.j_qcode;
			
			end if;
		end if;
		end loop;
		if nSupp_Cust != 0 and nOther != 0 then
			insert into quant_fin (jr_id,qf_bank,qf_other,qf_amount) values (rec.jr_id,nOther,nSupp_Cust,nAmount);
		end if;
	end loop;
	for rec in select jr_id,jr_grpt_id from jrn 
	    where jr_def_id in (select jrn_def_id from jrn_def where jrn_def_type='FIN') and jr_id not in (select jr_id from quant_fin)
	loop
		-- there are only 2 lines for bank operations
		-- first debit
		select count(j_id) into nCount from jrnx where j_grpt=rec.jr_grpt_id;
		if nCount > 2 then 
			raise notice 'Trop de valeur pour jr_grpt_id % count %',rec.jr_grpt_id,nCount;
			return;
		end if;
		nSupp_Cust := 0; nOther:=0;
		for recSupp_Cust in select  j_id, j_montant,j_debit,j_qcode,j_poste from jrnx where j_grpt=rec.jr_grpt_id
		loop
		if recSupp_Cust.j_poste like '441%'  then
			-- retrieve f_id for bank
			select f_id into nSupp_Cust from vw_poste_qcode where j_qcode=recSupp_Cust.j_qcode;
			if recSupp_Cust.j_debit = false then
				nAmount=recSupp_Cust.j_montant*(-1);
			else 
				nAmount=recSupp_Cust.j_montant;
			end if;
			else
			select f_id into nOther from vw_poste_qcode where j_qcode=recSupp_Cust.j_qcode;
			
			
		end if;
		end loop;
		if nSupp_Cust != 0 and nOther != 0 then
			insert into quant_fin (jr_id,qf_bank,qf_other,qf_amount) values (rec.jr_id,nOther,nSupp_Cust,nAmount);
		end if;
	end loop;
	return;
end;

Function: comptaproc.find_pcm_type(pp_value public.account_type(44))

Returns: text

Language: PLPGSQL

declare
	str_type parm_poste.p_type%TYPE;
	str_value parm_poste.p_type%TYPE;
	nLength integer;
begin
	str_value:=pp_value;
	nLength:=length(str_value::text);

	while nLength > 0 loop
		select p_type into str_type from parm_poste where p_value=str_value;
		if FOUND then
			raise info 'Type of %s is %s',str_value,str_type;
			return str_type;
		end if;
		nLength:=nLength-1;
		str_value:=substring(str_value::text from 1 for nLength)::account_type;
	end loop;
	str_value := pp_value;
	nLength:=length(str_value::text);
	str_value:=substring(str_value::text from 1 for nLength)::account_type;
	while nLength > 0 loop
		select pcm_type into str_type from tmp_pcmn tp where pcm_val=str_value;
		if FOUND then
			raise info 'Type of %s is %s',str_value,str_type;
			return str_type;
		end if;
		nLength:=nLength-1;
		str_value:=substring(str_value::text from 1 for nLength)::account_type;
	end loop;
return 'CON';
end;

Function: comptaproc.find_periode(p_date text)

Returns: integer

Language: PLPGSQL


declare n_p_id int4;
begin

select p_id into n_p_id
	from parm_periode
	where
		p_start <= to_date(p_date,'DD.MM.YYYY')
		and
		p_end >= to_date(p_date,'DD.MM.YYYY');

if NOT FOUND then
	return -1;
end if;

return n_p_id;

end;

Function: comptaproc.format_account(p_account public.account_type(44))

Returns: account_type(44)

Language: PLPGSQL

format the accounting : - upper case - remove space and special char. 


declare

sResult account_type;

begin
sResult := lower(p_account);

sResult := translate(sResult,E'éèêëàâäïîüûùöôç','eeeeaaaiiuuuooc');
sResult := translate(sResult,E' $€µ£%.+-/\\!(){}(),;_&|"#''^<>*','');

return upper(sResult);

end;

Function: comptaproc.format_quickcode(p_qcode text)

Returns: text

Language: PLPGSQL

Put in upper case and remove invalid char

declare
    tText text;
BEGIN
    tText := lower(trim(p_qcode));
    tText := replace(tText,' ','');
    tText:= translate(tText,E' $€µ£%+/\\!(){}(),;&|"#''^<>*','');
    tText := translate(tText,E'éèêëàâäïîüûùöôç','eeeeaaaiiuuuooc');

    return upper(tText);
END;

Function: comptaproc.four_upper_letter()

Returns: trigger

Language: PLPGSQL

Cut to the 4 first letter in uppercase

begin
    new.dc_code=transform_to_code(new.dc_code);
    new.dc_code:=substr(new.dc_code,1,4);
    return new;
END;

Function: comptaproc.get_follow_up_tree(action_gestion_id integer)

Returns: SET OF integer

Language: PLPGSQL

declare
    i int;
    x int;
    e int;
begin
    for x in select aga_least
             from action_gestion_related
             where
                 aga_greatest = action_gestion_id
        loop
            return next x;

            for e in select *  from  comptaproc.get_follow_up_tree(x)
                loop
                    return next e;
                end loop;

        end loop;
    return;
end;

Function: comptaproc.get_letter_jnt(a bigint)

Returns: bigint

Language: PLPGSQL

declare
 nResult bigint;
begin
   select jl_id into nResult from jnt_letter join letter_deb using (jl_id) where j_id = a;
   if NOT FOUND then
	select jl_id into nResult from jnt_letter join letter_cred using (jl_id) where j_id = a;
	if NOT found then
		return null;
	end if;
    end if;
return nResult;
end;

Function: comptaproc.get_menu_dependency(profile_menu_id integer)

Returns: SET OF integer

Language: PLPGSQL

declare
	i int;
	x int;
	e int;
begin
	for x in select pm_id,me_code
			from profile_menu
			where me_code_dep in (select me_code from profile_menu where pm_id=profile_menu_id)
			and p_id = (select p_id from profile_menu where pm_id=profile_menu_id)
	loop
		return next x;

	for e in select *  from comptaproc.get_menu_dependency(x)
		loop
			return next e;
		end loop;

	end loop;
	return;
end;

Function: comptaproc.get_menu_tree(p_code text, p_profile integer)

Returns: SET OF menu_tree

Language: PLPGSQL

declare
	i menu_tree;
	e menu_tree;
	a text;
	x v_all_menu%ROWTYPE;
begin
	for x in select *  from v_all_menu where me_code_dep=p_code::text and p_id=p_profile
	loop
		if x.me_code_dep is not null then
			i.code := x.me_code_dep||'/'||x.me_code;
		else
			i.code := x.me_code;
		end if;

		i.description := x.me_description;

		return next i;

	for e in select *  from get_menu_tree(x.me_code,p_profile)
		loop
			e.code:=x.me_code_dep||'/'||e.code;
			return next e;
		end loop;

	end loop;
	return;
end;

Function: comptaproc.get_pcm_tree(source public.account_type(44))

Returns: SET OF account_type(44)

Language: PLPGSQL

declare
	i account_type;
	e account_type;
begin
	for i in select pcm_val from tmp_pcmn where pcm_val_parent=source
	loop
		return next i;
		for e in select get_pcm_tree from get_pcm_tree(i)
		loop
			return next e;
		end loop;

	end loop;
	return;
end;

Function: comptaproc.get_profile_menu(p_profile integer)

Returns: SET OF menu_tree

Language: PLPGSQL

declare
	a menu_tree;
	e menu_tree;
begin
for a in select me_code,me_description from v_all_menu where p_id=p_profile
	and me_code_dep is null and me_type <> 'PR' and me_type <>'SP'
loop
		return next a;

		for e in select * from get_menu_tree(a.code,p_profile)
		loop
			return next e;
		end loop;

	end loop;
return;
end;

Function: comptaproc.group_analytic_ins_upd()

Returns: trigger

Language: PLPGSQL

declare 
name text;
begin
name:=upper(NEW.ga_id);
name:=trim(name);
name:=replace(name,' ','');
NEW.ga_id:=name;
return NEW;
end;

Function: comptaproc.group_analytique_del()

Returns: trigger

Language: PLPGSQL

begin
update poste_analytique set ga_id=null
where ga_id=OLD.ga_id;
return OLD;
end;

Function: comptaproc.html_quote(p_string text)

Returns: text

Language: PLPGSQL

remove harmfull HTML char

declare
	r text;
begin
	r:=p_string;
	r:=replace(r,'<','&lt;');
	r:=replace(r,'>','&gt;');
	r:=replace(r,'''','&quot;');
	return r;
end;

Function: comptaproc.info_def_ins_upd()

Returns: trigger

Language: PLPGSQL

declare 
	row_info_def info_def%ROWTYPE;
	str_type text;
begin
row_info_def:=NEW;
str_type:=upper(trim(NEW.id_type));
str_type:=replace(str_type,' ','');
str_type:=replace(str_type,',','');
str_type:=replace(str_type,';','');
if  length(str_type) =0 then
	raise exception 'id_type cannot be null';
end if;
row_info_def.id_type:=str_type;
return row_info_def;
end;

Function: comptaproc.insert_jrnx(p_date character varying, p_montant numeric, p_poste public.account_type(44), p_grpt integer, p_jrn_def integer, p_debit boolean, p_tech_user text, p_tech_per integer, p_qcode text, p_comment text)

Returns: void

Language: PLPGSQL

begin
	insert into jrnx
	(
		j_date,
		j_montant,
		j_poste,
		j_grpt,
		j_jrn_def,
		j_debit,
		j_text,
		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_comment,
		p_tech_user,
		p_tech_per,
		p_qcode
	);

return;
end;

Function: comptaproc.insert_quant_purchase(p_internal text, p_j_id numeric, p_fiche character varying, p_quant numeric, p_price numeric, p_vat numeric, p_vat_code integer, p_nd_amount numeric, p_nd_tva numeric, p_nd_tva_recup numeric, p_dep_priv numeric, p_client character varying, p_tva_sided numeric, p_price_unit numeric)

Returns: void

Language: PLPGSQL

declare
        fid_client integer;
        fid_good   integer;
        account_priv    account_type;
        fid_good_account account_type;
        n_dep_priv numeric;
begin
        n_dep_priv := p_dep_priv;
        select p_value into account_priv from parm_code where p_code='DEP_PRIV';
        select f_id into fid_client from
                fiche_detail where ad_id=23 and ad_value=upper(trim(p_client));
        select f_id into fid_good from
                 fiche_detail where ad_id=23 and ad_value=upper(trim(p_fiche));
        select ad_value into fid_good_account from fiche_detail where ad_id=5 and f_id=fid_good;
        if strpos( fid_good_account , account_priv ) = 1 then
                n_dep_priv=p_price;
        end if; 
            
        insert into quant_purchase
                (qp_internal,
                j_id,
                qp_fiche,
                qp_quantite,
                qp_price,
                qp_vat,
                qp_vat_code,
                qp_nd_amount,
                qp_nd_tva,
                qp_nd_tva_recup,
                qp_supplier,
                qp_dep_priv,
                qp_vat_sided,
                qp_unit)
        values
                (p_internal,
                p_j_id,
                fid_good,
                p_quant,
                p_price,
                p_vat,
                p_vat_code,
                p_nd_amount,
                p_nd_tva,
                p_nd_tva_recup,
                fid_client,
                n_dep_priv,
                p_tva_sided,
                p_price_unit);
        return;
end;

Function: comptaproc.insert_quant_sold(p_internal text, p_jid numeric, p_fiche character varying, p_quant numeric, p_price numeric, p_vat numeric, p_vat_code integer, p_client character varying, p_tva_sided numeric, p_price_unit numeric)

Returns: void

Language: PLPGSQL

declare
        fid_client integer;
        fid_good   integer;
begin

        select f_id into fid_client from
                fiche_detail where ad_id=23 and ad_value=upper(trim(p_client));
        select f_id into fid_good from
                fiche_detail where ad_id=23 and ad_value=upper(trim(p_fiche));
        insert into quant_sold
                (qs_internal,j_id,qs_fiche,qs_quantite,qs_price,qs_vat,qs_vat_code,qs_client,qs_valid,qs_vat_sided,qs_unit)
        values
                (p_internal,p_jid,fid_good,p_quant,p_price,p_vat,p_vat_code,fid_client,'Y',p_tva_sided,p_price_unit);
        return;
end;

Function: comptaproc.insert_quick_code(nf_id integer, tav_text text)

Returns: integer

Language: PLPGSQL

declare
    ns integer;
    nExist integer;
    tText text;
    tBase text;
    tName text;
    nCount Integer;
    nDuplicate Integer;
begin
    tText := comptaproc.format_quickcode(tav_text);
    nDuplicate := 0;
    tBase := tText;
    -- take the next sequence
    select nextval('s_jnt_fic_att_value') into ns;
    loop
        if length (tText) = 0 or tText is null then
            select count(*) into nCount from fiche_detail where f_id=nf_id and ad_id=1;
            if nCount = 0 then
                tBase := 'CRD';
            else
                select ad_value into tName from fiche_detail where f_id=nf_id and ad_id=1;
                tName := comptaproc.format_quickcode(tName);
                tName := substr(tName,1,6);
                tBase := tName;
                if nDuplicate = 0 then
                    tText := tName;
                else
                    tText := tBase||nDuplicate::text;
                end if;
            end if;
        end if;
        if coalesce(tText,'') = '' then
            tText := 'CRD';
        end if;
        -- av_text already used ?
        select count(*) into nExist
        from fiche_detail
        where
                ad_id=23 and  ad_value=tText;

        if nExist = 0 then
            exit;
        end if;
        nDuplicate := nDuplicate + 1 ;
        tText := tBase || nDuplicate::text;

        if nDuplicate > 99999 then
            raise Exception 'too many duplicate % duplicate# %',tText,nDuplicate;
        end if;
    end loop;


    insert into fiche_detail(jft_id,f_id,ad_id,ad_value) values (ns,nf_id,23,upper(tText));
    return ns;
end;

Function: comptaproc.is_closed(p_periode integer, p_jrn_def_id integer)

Returns: boolean

Language: PLPGSQL

declare
bClosed bool;
str_status text;
begin
select p_closed into bClosed from parm_periode
	where p_id=p_periode;

if bClosed = true then
	return bClosed;
end if;

select status into str_status from jrn_periode
       where p_id =p_periode and jrn_def_id=p_jrn_def_id;

if str_status <> 'OP' then
   return bClosed;
end if;
return false;
end;

Function: comptaproc.jnt_fic_attr_ins()

Returns: trigger

Language: PLPGSQL

declare
   r_record jnt_fic_attr%ROWTYPE;
   i_max integer;
begin
r_record=NEW;
perform comptaproc.fiche_attribut_synchro(r_record.fd_id);
select coalesce(max(jnt_order),0) into i_max from jnt_fic_attr where fd_id=r_record.fd_id;
i_max := i_max + 10;
NEW.jnt_order=i_max;
return NEW;
end;

Function: comptaproc.jrn_add_note(p_jrid bigint, p_note text)

Returns: void

Language: PLPGSQL

declare
	tmp bigint;
begin
	if length(trim(p_note)) = 0 then
	   delete from jrn_note where jr_id= p_jrid;
	   return;
	end if;
	
	select n_id into tmp from jrn_note where jr_id = p_jrid;
	
	if FOUND then
	   update jrn_note set n_text=trim(p_note) where jr_id = p_jrid;
	else 
	   insert into jrn_note (jr_id,n_text) values ( p_jrid, p_note);

	end if;
	
	return;
end;

Function: comptaproc.jrn_check_periode()

Returns: trigger

Language: PLPGSQL

declare
bClosed bool;
str_status text;
ljr_tech_per jrn.jr_tech_per%TYPE;
ljr_def_id jrn.jr_def_id%TYPE;
lreturn jrn%ROWTYPE;
begin
if TG_OP='UPDATE' then
    ljr_tech_per :=OLD.jr_tech_per ;
    NEW.jr_tech_per := comptaproc.find_periode(to_char(NEW.jr_date,'DD.MM.YYYY'));
    ljr_def_id :=OLD.jr_def_id;
    lreturn :=NEW;
    if NEW.jr_date = OLD.jr_date then
        return NEW;
    end if;
    if comptaproc.is_closed(NEW.jr_tech_per,NEW.jr_def_id) = true then
              raise exception 'Periode fermee';
    end if;
end if;

if TG_OP='INSERT' then
    NEW.jr_tech_per := comptaproc.find_periode(to_char(NEW.jr_date,'DD.MM.YYYY'));
    ljr_tech_per :=NEW.jr_tech_per ;
    ljr_def_id :=NEW.jr_def_id;
    lreturn :=NEW;
end if;

if TG_OP='DELETE' then
    ljr_tech_per :=OLD.jr_tech_per;
    ljr_def_id :=OLD.jr_def_id;
    lreturn :=OLD;
end if;

if comptaproc.is_closed (ljr_tech_per,ljr_def_id) = true then
       raise exception 'Periode fermee';
end if;

return lreturn;
end;

Function: comptaproc.jrn_currency()

Returns: trigger

Language: PLPGSQL

begin 
	if new.currency_id is null then 
		new.currency_id := 0;
                new.currency_rate := 1;
                new.currency_rate_ref := 1;
	end if;
	return new;
end;

Function: comptaproc.jrn_def_add()

Returns: trigger

Language: PLPGSQL

begin
execute 'insert into jrn_periode(p_id,jrn_def_id,status) select p_id,'||NEW.jrn_def_id||',
	case when p_central=true then ''CE''
	      when p_closed=true then ''CL''
	else ''OP''
	end
from
parm_periode ';
return NEW;
end;

Function: comptaproc.jrn_def_delete()

Returns: trigger

Language: PLPGSQL

declare 
nb numeric;
begin
select count(*) into nb from jrn where jr_def_id=OLD.jrn_def_id;

if nb <> 0 then
	raise exception 'EFFACEMENT INTERDIT: JOURNAL UTILISE';
end if;
return OLD;
end;

Function: comptaproc.jrn_del()

Returns: trigger

Language: PLPGSQL

declare
row jrn%ROWTYPE;
begin
row:=OLD;
insert into del_jrn ( jr_id,
       jr_def_id,
       jr_montant,
       jr_comment,
       jr_date,
       jr_grpt_id,
       jr_internal,
       jr_tech_date,
       jr_tech_per,
       jrn_ech,
       jr_ech,
       jr_rapt,
       jr_valid,
       jr_opid,
       jr_c_opid,
       jr_pj,
       jr_pj_name,
       jr_pj_type,
       jr_pj_number,
       del_jrn_date) 
       select  jr_id,
	      jr_def_id,
	      jr_montant,
	      jr_comment,
	      jr_date,
	      jr_grpt_id,
	      jr_internal,
	      jr_tech_date,
	      jr_tech_per,
	      jrn_ech,
	      jr_ech,
	      jr_rapt,
	      jr_valid,
	      jr_opid,
	      jr_c_opid,
	      jr_pj,
	      jr_pj_name,
	      jr_pj_type,
	      jr_pj_number
	      ,now() from jrn where jr_id=row.jr_id;
return row;
end;

Function: comptaproc.jrnx_del()

Returns: trigger

Language: PLPGSQL

declare
row jrnx%ROWTYPE;
begin
row:=OLD;


insert into del_jrnx(
            j_id, j_date, j_montant, j_poste, j_grpt, j_rapt, j_jrn_def, 
            j_debit, j_text, j_centralized, j_internal, j_tech_user, j_tech_date, 
            j_tech_per, j_qcode, f_id)  SELECT j_id, j_date, j_montant, j_poste, j_grpt, j_rapt, j_jrn_def, 
       j_debit, j_text, j_centralized, j_internal, j_tech_user, j_tech_date, 
       j_tech_per, j_qcode, f_id from jrnx where j_id=row.j_id;
return row;
end;

Function: comptaproc.jrnx_ins()

Returns: trigger

Language: PLPGSQL

declare
n_fid bigint;
nCount integer;
sQcode text;

begin
n_fid := NULL;
sQcode := NULL;

NEW.j_tech_per := comptaproc.find_periode(to_char(NEW.j_date,'DD.MM.YYYY'));
if NEW.j_tech_per = -1 then
        raise exception 'Période invalide';
end if;

if trim(coalesce(NEW.j_qcode,'')) = '' then
        -- how many card has this accounting
        select count(*) into nCount from fiche_detail where ad_id=5 and ad_value=NEW.j_poste;
        -- only one card is found , then we change the j_qcode by the card
        if nCount = 1 then
                select f_id into n_fid from fiche_detail where ad_id = 5 and ad_value=NEW.j_poste;
            if FOUND then
                select ad_value into sQcode  from fiche_detail where f_id=n_fid and ad_id = 23;
                NEW.f_id := n_fid;
                NEW.j_qcode = sQcode;
                raise info 'comptaproc.jrnx_ins : found card % qcode %',n_fid,sQcode;
            end if;
        end if;

end if;

NEW.j_qcode=trim(upper(coalesce(NEW.j_qcode,'')));

if length (coalesce(NEW.j_qcode,'')) = 0 then
    NEW.j_qcode=NULL;
    else
      select f_id into n_fid from fiche_detail  where ad_id=23 and ad_value=NEW.j_qcode;
      if NOT FOUND then
        raise exception 'La fiche dont le quick code est % n''existe pas',NEW.j_qcode;
    end if;
end if;

NEW.f_id:=n_fid;
return NEW;
end;

Function: comptaproc.jrnx_letter_del()

Returns: trigger

Language: PLPGSQL

declare
row jrnx%ROWTYPE;
begin
row:=OLD;
delete from jnt_letter 
	where (jl_id in (select jl_id from letter_deb) and jl_id not in(select jl_id from letter_cred )) 
		or (jl_id not in (select jl_id from letter_deb  ) and jl_id  in(select jl_id from letter_cred ));
return row;
end;

Function: comptaproc.menu_complete_dependency(n_profile numeric)

Returns: void

Language: PLPGSQL

declare 
 n_count integer;
 csr_root_menu cursor (p_profile numeric) is select pm_id,
	me_code,
	me_code_dep 
	
	from profile_menu 
	where 
	me_code in 
		(select a.me_code_dep 
			from profile_menu as a 
			join profile_menu as b on (a.me_code=b.me_code and a.me_code_dep=b.me_code_dep and a.pm_id <> b.pm_id and a.p_id=b.p_id) 
			where a.p_id=n_profile) 
		and p_id=p_profile;

begin
	for duplicate in csr_root_menu(n_profile)
	loop
		raise notice 'found %',duplicate;
		update profile_menu set pm_id_dep  = duplicate.pm_id 
			where pm_id in (select a.pm_id
				from profile_menu as a 
				left join profile_menu as b on (a.me_code=b.me_code and a.me_code_dep=b.me_code_dep)
				where 
				a.p_id=n_profile
				and b.p_id=n_profile
				and a.pm_id_dep is null 
				and a.me_code_dep = duplicate.me_code
				and a.pm_id < b.pm_id);
	end loop;
	
	for duplicate in csr_root_menu(n_profile) 
	loop
		select count(*) into n_count from profile_menu where p_id=n_profile and pm_id_dep = duplicate.pm_id;
		raise notice '% use % times',duplicate,n_count;
		if n_count = 0 then
			raise notice ' Update with %',duplicate;
			update profile_menu set pm_id_dep = duplicate.pm_id where p_id = n_profile and me_code_dep = duplicate.me_code and pm_id_dep is null;
		end if;

	end loop;
	
end;

Function: comptaproc.opd_limit_description()

Returns: trigger

Language: PLPGSQL

	declare
		sDescription text;
	begin
	sDescription := NEW.od_description;
	NEW.od_description := substr(sDescription,1,80);
	return NEW;
	end;

Function: comptaproc.periode_exist(p_date text, p_periode_id bigint)

Returns: integer

Language: PLPGSQL


declare n_p_id int4;
begin

select p_id into n_p_id
        from parm_periode
        where
                p_start <= to_date(p_date,'DD.MM.YYYY')
                and
                p_end >= to_date(p_date,'DD.MM.YYYY')
                and
                p_id <> p_periode_id;

if NOT FOUND then
        return -1;
end if;

return n_p_id;

end;

Function: comptaproc.plan_analytic_ins_upd()

Returns: trigger

Language: PLPGSQL

declare
   name text;
begin
   name:=upper(NEW.pa_name);
   name:=trim(name);
   name:=replace(name,' ','');
   NEW.pa_name:=name;
return NEW;
end;

Function: comptaproc.poste_analytique_ins_upd()

Returns: trigger

Language: PLPGSQL

declare
name text;
rCount record;

begin
name:=upper(NEW.po_name);
name:=trim(name);
name:=replace(name,' ','');		
NEW.po_name:=name;

if NEW.ga_id is NULL then
return NEW;
end if;

if length(trim(NEW.ga_id)) = 0 then
  NEW.ga_id:=NULL;
  return NEW;
end if;
perform 'select ga_id from groupe_analytique where ga_id='||NEW.ga_id;
if NOT FOUND then
   raise exception' Inexistent Group Analytic %',NEW.ga_id;
end if;
return NEW;
end;

Function: comptaproc.proc_check_balance()

Returns: trigger

Language: PLPGSQL

declare 
	diff numeric;
	tt integer;
begin
	if TG_OP = 'INSERT' or TG_OP='UPDATE' 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;

Function: comptaproc.quant_purchase_ins_upd()

Returns: trigger

Language: PLPGSQL

	begin
		if NEW.qp_price < 0 OR NEW.qp_quantite <0 THEN
			NEW.qp_price := abs (NEW.qp_price)*(-1);
			NEW.qp_quantite := abs (NEW.qp_quantite)*(-1);
		end if;
return NEW;
end;

Function: comptaproc.quant_sold_ins_upd()

Returns: trigger

Language: PLPGSQL

	begin
		if NEW.qs_price < 0 OR NEW.qs_quantite <0 THEN
			NEW.qs_price := abs (NEW.qs_price)*(-1);
			NEW.qs_quantite := abs (NEW.qs_quantite)*(-1);
		end if;
return NEW;
end;

Function: comptaproc.set_tech_user()

Returns: trigger

Language: PLPGSQL

declare
    /* variable */
    noalyss_user text;
begin
    new.tech_user := current_setting('noalyss.user_login');
    new.tech_date := now();
    return NEW;

exception when others then
    new.tech_date := now();
    new.tech_user := current_user;
    return NEW;
end ;

Function: comptaproc.t_document_modele_validate()

Returns: trigger

Language: PLPGSQL

declare 
    lText text;
    modified document_modele%ROWTYPE;
begin
    modified:=NEW;

	modified.md_filename:=replace(NEW.md_filename,' ','_');
	return modified;
end;

Function: comptaproc.t_document_type_insert()

Returns: trigger

Language: PLPGSQL

declare
nCounter integer;
    BEGIN
select count(*) into nCounter from pg_class where relname='seq_doc_type_'||NEW.dt_id;
if nCounter = 0 then
        execute  'create sequence seq_doc_type_'||NEW.dt_id;
end if;
        RETURN NEW;
    END;

Function: comptaproc.t_document_validate()

Returns: trigger

Language: PLPGSQL

declare
  lText text;
  modified document%ROWTYPE;
begin
    	modified:=NEW;
	modified.d_filename:=replace(NEW.d_filename,' ','_');
	return modified;
end;

Function: comptaproc.t_jrn_def_description()

Returns: trigger

Language: PLPGSQL

    declare
        str varchar(200);
    BEGIN
        str := substr(NEW.jrn_def_description,1,200);
        NEW.jrn_def_description := str;

        RETURN NEW;
    END;

Function: comptaproc.t_jrn_def_sequence()

Returns: trigger

Language: PLPGSQL

declare
nCounter integer;

    BEGIN
    select count(*) into nCounter 
       from pg_class where relname='s_jrn_'||NEW.jrn_def_id;
       if nCounter = 0 then
       	   execute  'create sequence s_jrn_'||NEW.jrn_def_id;
	   raise notice 'Creating sequence s_jrn_%',NEW.jrn_def_id;
	 end if;

        RETURN NEW;
    END;

Function: comptaproc.t_parameter_extra_code()

Returns: trigger

Language: PLPGSQL

begin
	new.pe_code := comptaproc.transform_to_code (new.pe_code);
        return new;
end;

Function: comptaproc.tmp_pcmn_alphanum_ins_upd()

Returns: trigger

Language: PLPGSQL

declare
   r_record tmp_pcmn%ROWTYPE;
begin
r_record := NEW;
r_record.pcm_val:=format_account(NEW.pcm_val);

return r_record;
end;

Function: comptaproc.tmp_pcmn_ins()

Returns: trigger

Language: PLPGSQL

declare
   r_record tmp_pcmn%ROWTYPE;
begin
r_record := NEW;
if  length(trim(r_record.pcm_type))=0 or r_record.pcm_type is NULL then
   r_record.pcm_type:=find_pcm_type(NEW.pcm_val);
   return r_record;
end if;
return NEW;
end;

Function: comptaproc.transform_to_code(p_account text)

Returns: text

Language: PLPGSQL


declare

sResult text;

begin
sResult := lower(p_account);

sResult := translate(sResult,E'éèêëàâäïîüûùöôç','eeeeaaaiiuuuooc');
sResult := translate(sResult,E' $€µ£%.+-/\\!(){}(),;&|"#''^<>*','');

return upper(sResult);

end;

Function: comptaproc.trg_profile_user_ins_upd()

Returns: trigger

Language: PLPGSQL


begin

NEW.user_name := lower(NEW.user_name);
return NEW;

end;

Function: comptaproc.trg_remove_script_tag()

Returns: trigger

Language: PLPGSQL


begin

    NEW.agc_comment_raw:= regexp_replace(NEW.agc_comment_raw, '<script', 'scritp', 'i');
    return NEW;

end;

Function: comptaproc.trg_todo_list_ins_upd()

Returns: trigger

Language: PLPGSQL


begin

NEW.use_login:= lower(NEW.use_login);
return NEW;

end;

Function: comptaproc.trg_todo_list_shared_ins_upd()

Returns: trigger

Language: PLPGSQL


begin

NEW.use_login:= lower(NEW.use_login);
return NEW;

end;

Function: comptaproc.trg_user_sec_act_ins_upd()

Returns: trigger

Language: PLPGSQL


begin

NEW.ua_login:= lower(NEW.ua_login);
return NEW;

end;

Function: comptaproc.trg_user_sec_jrn_ins_upd()

Returns: trigger

Language: PLPGSQL


begin

NEW.uj_login:= lower(NEW.uj_login);
return NEW;

end;

Function: comptaproc.trim_cvs_quote()

Returns: trigger

Language: PLPGSQL

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;

Function: comptaproc.trim_space_format_csv_banque()

Returns: trigger

Language: PLPGSQL

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;

Function: comptaproc.tva_delete(integer)

Returns: void

Language: PLPGSQL

 
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
                 return;
		
	end if;
	select count(*) into nCount from quant_purchase where qp_vat_code=p_tva_id;
	if nCount != 0 then
                 return;
		
	end if;

delete from tva_rate where tva_id=p_tva_id;
	return;
end;

Function: comptaproc.tva_insert(text, numeric, text, text, integer)

Returns: integer

Language: PLPGSQL

declare
	l_tva_id integer;
	p_tva_label alias for $1;
	p_tva_rate alias for $2;
	p_tva_comment alias for $3;
	p_tva_poste alias for $4;
	p_tva_both_side 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::account_type;
	if nCount = 0 then return 4; end if;
	select count(*) into nCount from tmp_pcmn where pcm_val=credit::account_type;
	if nCount = 0 then return 4; end if;

end if;
select into l_tva_id nextval('s_tva') ;
insert into tva_rate(tva_id,tva_label,tva_rate,tva_comment,tva_poste,tva_both_side)
	values (l_tva_id,p_tva_label,p_tva_rate,p_tva_comment,p_tva_poste,p_tva_both_side);
return 0;
end;

Function: comptaproc.tva_modify(integer, text, numeric, text, text, integer)

Returns: integer

Language: PLPGSQL

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;
	p_tva_both_side alias for $6;
	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::account_type;
	if nCount = 0 then return 4; end if;
	select count(*) into nCount from tmp_pcmn where pcm_val=credit::account_type;
	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,tva_both_side=p_tva_both_side
	where tva_id=p_tva_id;
return 0;
end;

Function: comptaproc.update_quick_code(njft_id integer, tav_text text)

Returns: integer

Language: PLPGSQL

update the qcode + related columns in other cards

declare
    ns integer;
    nExist integer;
    tText text;
    tBase text;
    old_qcode varchar;
    num_rows_jrnx integer;
    num_rows_predef integer;
    n_count integer;
begin
    n_count := 0;
    -- get current value
    select ad_value into old_qcode from fiche_detail where jft_id=njft_id;
    -- av_text didn't change so no update
    if tav_text = upper( trim(old_qcode)) then
        raise notice 'nothing to change % %' , tav_text,old_qcode;
        return 0;
    end if;

    tText := comptaproc.format_quickcode(tav_text);

    if length ( tText) = 0 or tText is null then
        return 0;
    end if;

    ns := njft_id;
    tBase := tText;
    loop
        -- av_text already used ?
        select count(*) into nExist
        from fiche_detail
        where
                ad_id=23 and ad_value=tText
          and jft_id <> njft_id;

        if nExist = 0 then
            exit;
        end if;
        tText := tBase || n_count::text;
        n_count := n_count + 1 ;

    end loop;
    update fiche_detail set ad_value = tText where jft_id=njft_id;

    -- update also the contact
    update fiche_detail set ad_value = tText
    where jft_id in
          ( select jft_id
            from fiche_detail
            where ad_id in (select ad_id from attr_def where ad_type='card') and ad_value=old_qcode);


    return ns;
end;

Schema public

standard public schema

Table: public.acc_other_tax

Additional tax for Sale or Purchase

public.acc_other_tax Structure
F-Key Name Type Description
ac_id serial PRIMARY KEY
ac_label text NOT NULL

Label of the tax
ac_rate numeric(5,2) NOT NULL

rate of the tax in percent
ajrn_def_id integer[]

array of to FK jrn_def (jrn_def_id)
ac_accounting account_type NOT NULL

FK tmp_pcmn (pcm_val)

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.action

The different privileges

public.action Structure
F-Key Name Type Description
ac_id integer PRIMARY KEY
ac_description text NOT NULL
ac_module text
ac_code character varying(30)

this code will be used in the code with the function User::check_action

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.action_comment_document

public.action_comment_document Structure
F-Key Name Type Description
acd_id bigserial PRIMARY KEY
public.document.d_id document_id bigint UNIQUE#1
public.action_gestion_comment.agc_id action_gestion_comment_id bigint UNIQUE#1

Index - Schema public

Table: public.action_detail

Detail of action_gestion, see class Action_Detail

public.action_detail Structure
F-Key Name Type Description
ad_id serial PRIMARY KEY
f_id bigint

the concerned card
ad_text text

Description
ad_pu numeric(20,4)

price per unit
ad_quant numeric(20,4)

quantity
ad_tva_id integer

tva_id
ad_tva_amount numeric(20,4)

tva_amount
ad_total_amount numeric(20,4)

total amount
public.action_gestion.ag_id ag_id integer NOT NULL

Index - Schema public

Table: public.action_gestion

Contains the details for the follow-up of customer, supplier, administration

public.action_gestion Structure
F-Key Name Type Description
ag_id integer PRIMARY KEY DEFAULT nextval('action_gestion_ag_id_seq'::regclass)
public.document_type.dt_id ag_type integer

type of action: see document_type
public.fiche.f_id f_id_dest integer

third party
ag_title text

title
ag_timestamp timestamp without time zone DEFAULT now()
ag_ref text

its reference
ag_hour text
ag_priority integer DEFAULT 2

Low, medium, important
public.profile.p_id ag_dest bigint NOT NULL DEFAULT '-1'::integer

is the profile which has to take care of this action
ag_owner text

is the owner of this action
ag_contact bigint

contact of the third part
ag_state integer

state of the action same as document_state
ag_remind_date date

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.action_gestion_comment

comment on action management

public.action_gestion_comment Structure
F-Key Name Type Description
agc_id bigserial PRIMARY KEY

PK
public.action_gestion.ag_id ag_id bigint

FK to action_gestion
agc_date timestamp with time zone DEFAULT now()
agc_comment text

comment
tech_user text

user_login
agc_comment_raw text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.action_gestion_filter

public.action_gestion_filter Structure
F-Key Name Type Description
af_id bigserial NOT NULL
af_user text NOT NULL
af_name text NOT NULL
af_search text NOT NULL

Index - Schema public

Table: public.action_gestion_operation

Operation linked on action

public.action_gestion_operation Structure
F-Key Name Type Description
ago_id bigserial PRIMARY KEY

pk
public.action_gestion.ag_id ag_id bigint

fk to action_gestion
public.jrn.jr_id jr_id bigint

fk to jrn

Index - Schema public

Table: public.action_gestion_related

link between action

public.action_gestion_related Structure
F-Key Name Type Description
aga_id bigserial PRIMARY KEY

pk
public.action_gestion.ag_id aga_least bigint UNIQUE#1 NOT NULL

fk to action_gestion, smallest ag_id
public.action_gestion.ag_id aga_greatest bigint UNIQUE#1 NOT NULL

fk to action_gestion greatest ag_id
public.link_action_type.l_id aga_type bigint

Type de liens

Index - Schema public

Table: public.action_person

Person involved in the action

public.action_person Structure
F-Key Name Type Description
ap_id serial PRIMARY KEY

pk
public.action_gestion.ag_id public.action_gestion.ag_id ag_id integer NOT NULL

fk to fiche
public.fiche.f_id public.fiche.f_id f_id integer NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.action_person_option

option for each contact

public.action_person_option Structure
F-Key Name Type Description
ap_id bigserial PRIMARY KEY
ap_value character varying

Value of the option
public.contact_option_ref.cor_id contact_option_ref_id bigint NOT NULL

FK to contact_option
public.action_person.ap_id action_person_id bigint NOT NULL

FK to action_person

Index - Schema public

Table: public.action_tags

Tags link to action

public.action_tags Structure
F-Key Name Type Description
at_id serial PRIMARY KEY
public.tags.t_id t_id integer
public.action_gestion.ag_id ag_id integer

Index - Schema public

Table: public.attr_def

The available attributs for the cards

public.attr_def Structure
F-Key Name Type Description
ad_id integer PRIMARY KEY DEFAULT nextval(('s_attr_def'::text)::regclass)
ad_text text
ad_type text
ad_size text NOT NULL
ad_extra text
ad_search_followup integer DEFAULT 1

1 : search available from followup , 0 : search not available in followup
ad_default_order integer

Default order of the attribute

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.attr_min

The value of attributs for the cards

public.attr_min Structure
F-Key Name Type Description
public.fiche_def_ref.frd_id frd_id integer PRIMARY KEY
public.attr_def.ad_id ad_id integer PRIMARY KEY
ad_default_order integer

Index - Schema public

Table: public.bilan

contains the template and the data for generating different documents

public.bilan Structure
F-Key Name Type Description
b_id integer PRIMARY KEY DEFAULT nextval('bilan_b_id_seq'::regclass)

primary key
b_name text UNIQUE NOT NULL

Name of the document
b_file_template text NOT NULL

path of the template (document/...)
b_file_form text

path of the file with forms
b_type text NOT NULL

type = ODS, RTF...

Index - Schema public

Table: public.bookmark

Bookmark of the connected user

public.bookmark Structure
F-Key Name Type Description
b_id serial PRIMARY KEY
b_order integer DEFAULT 1
b_action text
login text

Index - Schema public

Table: public.centralized

The centralized journal

public.centralized Structure
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
public.jrn_def.jrn_def_id c_jrn_def integer NOT NULL
public.tmp_pcmn.pcm_val c_poste account_type
c_description text
c_grp integer NOT NULL
c_comment text
c_rapt text
c_periode integer
c_order integer

Index - Schema public

Table: public.contact_option_ref

Option for the contact

public.contact_option_ref Structure
F-Key Name Type Description
cor_id bigserial PRIMARY KEY
cor_label character varying NOT NULL

Label de l'option
cor_type integer NOT NULL

0 text , 1 select ,2 nombre , 3 date
cor_value_select character varying

Select values

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.currency

currency

public.currency Structure
F-Key Name Type Description
id serial PRIMARY KEY
cr_code_iso character varying(10) UNIQUE

Code ISO
cr_name character varying(80)

Name of the currency

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.currency_history

currency values history

public.currency_history Structure
F-Key Name Type Description
id serial PRIMARY KEY

pk
ch_value numeric(20,8) NOT NULL

rate of currency depending of currency of the folder
ch_from date NOT NULL

Date when the rate is available
public.currency.id currency_id integer NOT NULL

FK to currency

 

public.currency_history Constraints
Name Constraint
currency_history_check CHECK ((ch_value > (0)::numeric))

Index - Schema public

Table: public.del_action

deleted actions

public.del_action Structure
F-Key Name Type Description
del_id serial PRIMARY KEY
del_name text NOT NULL
del_time timestamp without time zone

Index - Schema public

Table: public.del_jrn

deleted operation

public.del_jrn Structure
F-Key Name Type Description
jr_id integer NOT NULL
jr_def_id integer
jr_montant numeric(20,4)
jr_comment text
jr_date date
jr_grpt_id integer
jr_internal text
jr_tech_date timestamp without time zone
jr_tech_per integer
jrn_ech date
jr_ech date
jr_rapt text
jr_valid boolean
jr_opid integer
jr_c_opid integer
jr_pj oid
jr_pj_name text
jr_pj_type text
del_jrn_date timestamp without time zone
jr_pj_number text
dj_id serial PRIMARY KEY

Index - Schema public

Table: public.del_jrnx

delete operation details

public.del_jrnx Structure
F-Key Name Type Description
j_id integer NOT NULL
j_date date
j_montant numeric(20,4)
j_poste account_type
j_grpt integer
j_rapt text
j_jrn_def integer
j_debit boolean
j_text text
j_centralized boolean
j_internal text
j_tech_user text
j_tech_date timestamp without time zone
j_tech_per integer
j_qcode text
djx_id serial PRIMARY KEY
f_id bigint

Index - Schema public

Table: public.document

This table contains all the documents : summary and lob files

public.document Structure
F-Key Name Type Description
d_id integer PRIMARY KEY DEFAULT nextval('document_d_id_seq'::regclass)
ag_id integer NOT NULL
d_lob oid
d_number bigint NOT NULL
d_filename text
d_mimetype text
d_description text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.document_component

Give the component of NOALYSS that is using is

public.document_component Structure
F-Key Name Type Description
dc_id serial PRIMARY KEY

PK
dc_code text UNIQUE NOT NULL

Code used in document_modele
dc_comment text NOT NULL

Code used in document_modele

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.document_modele

contains all the template for the documents

public.document_modele Structure
F-Key Name Type Description
md_id integer PRIMARY KEY DEFAULT nextval('document_modele_md_id_seq'::regclass)
md_name text NOT NULL
md_lob oid
public.document_type.dt_id md_type integer NOT NULL
md_filename text
md_mimetype text
public.document_component.dc_code md_affect character varying(3) NOT NULL

Index - Schema public

Table: public.document_option

Reference of option addable to document_type

public.document_option Structure
F-Key Name Type Description
do_id bigserial PRIMARY KEY
do_code character varying(20) UNIQUE#1 NOT NULL

Code of the option to add
public.document_type.dt_id document_type_id bigint UNIQUE#1

FK to document_type
do_enable integer NOT NULL DEFAULT 1

1 the option is activated, 0 is inativated
do_option character varying

Option for the detail

Index - Schema public

Table: public.document_state

State of the document

public.document_state Structure
F-Key Name Type Description
s_id integer PRIMARY KEY DEFAULT nextval('document_state_s_id_seq'::regclass)
s_value character varying(50) NOT NULL
s_status character(1)

Index - Schema public

Table: public.document_type

Type of document : meeting, invoice,...

public.document_type Structure
F-Key Name Type Description
dt_id integer PRIMARY KEY DEFAULT nextval('document_type_dt_id_seq'::regclass)
dt_value character varying(80)
dt_prefix text

Prefix for ag_ref

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.extension

Content the needed information for the extension

public.extension Structure
F-Key Name Type Description
ex_id serial PRIMARY KEY

Primary key
ex_name character varying(30) NOT NULL

code of the extension
ex_code character varying(15) UNIQUE NOT NULL

code of the extension
ex_desc character varying(250)

Description of the extension
ex_file character varying NOT NULL

path to the extension to include
ex_enable "char" NOT NULL DEFAULT 'Y'::"char"

Y : enabled N : disabled

Index - Schema public

Table: public.fiche

Cards

public.fiche Structure
F-Key Name Type Description
f_id integer PRIMARY KEY DEFAULT nextval(('s_fiche'::text)::regclass)
public.fiche_def.fd_id fd_id integer
f_enable character(1) NOT NULL

value = 1 if card enable , otherwise 0

 

public.fiche Constraints
Name Constraint
f_enable_ck CHECK ((f_enable = ANY (ARRAY['0'::bpchar, '1'::bpchar])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.fiche_def

Cards definition

public.fiche_def Structure
F-Key Name Type Description
fd_id integer PRIMARY KEY DEFAULT nextval(('s_fdef'::text)::regclass)
fd_class_base text
fd_label text NOT NULL
fd_create_account boolean DEFAULT false
public.fiche_def_ref.frd_id frd_id integer NOT NULL
fd_description text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.fiche_def_ref

Family Cards definition

public.fiche_def_ref Structure
F-Key Name Type Description
frd_id integer PRIMARY KEY DEFAULT nextval(('s_fiche_def_ref'::text)::regclass)
frd_text text
frd_class_base account_type

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.fiche_detail

join between the card and the attribut definition

public.fiche_detail Structure
F-Key Name Type Description
jft_id integer PRIMARY KEY DEFAULT nextval(('s_jnt_fic_att_value'::text)::regclass)
public.fiche.f_id f_id integer
public.attr_def.ad_id ad_id integer
ad_value text

Index - Schema public

Table: public.forecast

contains the name of the forecast

public.forecast Structure
F-Key Name Type Description
f_id serial PRIMARY KEY
f_name text NOT NULL
public.parm_periode.p_id f_start_date bigint
public.parm_periode.p_id f_end_date bigint

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.forecast_category

Category of forecast

public.forecast_category Structure
F-Key Name Type Description
fc_id serial PRIMARY KEY

primary key
fc_desc text NOT NULL

text of the category
public.forecast.f_id f_id bigint NOT NULL

Foreign key, it is the parent from the table forecast
fc_order integer NOT NULL

Order of the category, used when displaid

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.forecast_item

items of forecast

public.forecast_item Structure
F-Key Name Type Description
fi_id serial PRIMARY KEY

Primary key
fi_text text

Label of the i tem
fi_account text

Accountancy entry
fi_order integer

Order of showing (not used)
public.forecast_category.fc_id fc_id integer
fi_amount numeric(20,4)

Amount
fi_pid integer

0 for every month, or the value parm_periode.p_id
fi_amount_initial numeric(20,4)

Index - Schema public

Table: public.form_definition

Simple Report name

public.form_definition Structure
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:

Index - Schema public

Table: public.form_detail

Simple report details with formula

public.form_detail Structure
F-Key Name Type Description
fo_id integer PRIMARY KEY DEFAULT nextval(('s_form'::text)::regclass)
public.form_definition.fr_id fo_fr_id integer NOT NULL
fo_pos integer
fo_label text
fo_formula text

Index - Schema public

Table: public.groupe_analytique

Group of analytic accountancy

public.groupe_analytique Structure
F-Key Name Type Description
ga_id character varying(10) PRIMARY KEY
public.plan_analytique.pa_id pa_id integer
ga_description text

Index - Schema public

Table: public.info_def

Contains the types of additionnal info we can add to a operation

public.info_def Structure
F-Key Name Type Description
id_type text PRIMARY KEY
id_description text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.jnt_document_option_contact

Many to many table between document and contact option

public.jnt_document_option_contact Structure
F-Key Name Type Description
jdoc_id bigserial PRIMARY KEY
jdoc_enable integer NOT NULL
public.document_type.dt_id document_type_id bigint UNIQUE#1
public.contact_option_ref.cor_id contact_option_ref_id bigint UNIQUE#1

 

public.jnt_document_option_contact Constraints
Name Constraint
jnt_document_option_contact_check CHECK ((jdoc_enable = ANY (ARRAY[0, 1])))

Index - Schema public

Table: public.jnt_fic_attr

join between the family card and the attribut definition

public.jnt_fic_attr Structure
F-Key Name Type Description
public.fiche_def.fd_id fd_id integer
public.attr_def.ad_id ad_id integer
jnt_id bigint PRIMARY KEY DEFAULT nextval('s_jnt_id'::regclass)
jnt_order integer NOT NULL

Index - Schema public

Table: public.jnt_letter

m2m tables for lettering

public.jnt_letter Structure
F-Key Name Type Description
jl_id serial PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.jnt_tag_group_tag

Many to Many table betwwen tag and tag group

public.jnt_tag_group_tag Structure
F-Key Name Type Description
public.tag_group.tg_id tag_group_id bigint UNIQUE#1 NOT NULL
public.tags.t_id tag_id bigint UNIQUE#1 NOT NULL
jt_id serial PRIMARY KEY

Index - Schema public

Table: public.jrn

Journal: content one line for a group of accountancy writing

public.jrn Structure
F-Key Name Type Description
jr_id integer PRIMARY KEY DEFAULT nextval(('s_jrn'::text)::regclass)
public.jrn_def.jrn_def_id jr_def_id integer NOT NULL
jr_montant numeric(20,4) NOT NULL
jr_comment text
jr_date date
jr_grpt_id integer NOT NULL
jr_internal text UNIQUE
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
jr_pj_number text
jr_mt text
jr_date_paid date
jr_optype character varying(3) DEFAULT 'NOR'::character varying

Type of operation , NOR = NORMAL , OPE opening , EXT extourne, CLO closing
public.currency.id currency_id bigint NOT NULL
currency_rate numeric(20,6) NOT NULL DEFAULT 1
currency_rate_ref numeric(20,6) NOT NULL DEFAULT 1

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.jrn_def

Definition of a journal, his properties

public.jrn_def Structure
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
public.jrn_type.jrn_type_id jrn_def_type character(3) NOT NULL
jrn_def_code text NOT NULL
jrn_def_pj_pref text
jrn_def_bank bigint
jrn_def_num_op integer
jrn_def_description text
jrn_enable integer DEFAULT 1

Set to 1 if the ledger is enable
public.currency.id currency_id integer NOT NULL

Default currency for financial ledger
jrn_def_negative_amount character(1) NOT NULL DEFAULT '0'::bpchar

1 echo a warning if you are not using an negative amount, default 0 for no warning
jrn_def_negative_warning text

Yell a warning if the amount if not negative , in the case of jrn_def_negative_amount is Y
jrn_def_quantity smallint NOT NULL DEFAULT 1

Use the quantity column, 0->disable,1->enable,used only with Sale and Purchase otherwise ignored
jrn_def_pj_padding integer

 

public.jrn_def Constraints
Name Constraint
negative_amount_ck CHECK ((jrn_def_negative_amount = ANY (ARRAY['1'::bpchar, '0'::bpchar])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.jrn_info

extra info for operation

public.jrn_info Structure
F-Key Name Type Description
ji_id serial PRIMARY KEY
public.jrn.jr_id jr_id integer NOT NULL
public.info_def.id_type id_type text NOT NULL
ji_value text

Index - Schema public

Table: public.jrn_note

Note about operation

public.jrn_note Structure
F-Key Name Type Description
n_id serial PRIMARY KEY
n_text text
public.jrn.jr_id jr_id bigint NOT NULL

Index - Schema public

Table: public.jrn_periode

Period by ledger

public.jrn_periode Structure
F-Key Name Type Description
public.jrn_def.jrn_def_id jrn_def_id integer UNIQUE#1 NOT NULL
public.parm_periode.p_id p_id integer UNIQUE#1 NOT NULL
status text
id bigint PRIMARY KEY DEFAULT nextval('jrn_periode_id_seq'::regclass)

Index - Schema public

Table: public.jrn_rapt

Rapprochement between operation

public.jrn_rapt Structure
F-Key Name Type Description
jra_id integer PRIMARY KEY DEFAULT nextval(('s_jrn_rapt'::text)::regclass)
public.jrn.jr_id jr_id integer NOT NULL
public.jrn.jr_id jra_concerned integer NOT NULL

Index - Schema public

Table: public.jrn_tax

public.jrn_tax Structure
F-Key Name Type Description
jt_id serial PRIMARY KEY
public.jrnx.j_id j_id bigint NOT NULL

fk jrnx
pcm_val account_type NOT NULL

FK tmp_pcmn
public.acc_other_tax.ac_id ac_id integer NOT NULL

FK to acc_other_tax

Index - Schema public

Table: public.jrn_type

Type of journal (Sell, Buy, Financial...)

public.jrn_type Structure
F-Key Name Type Description
jrn_type_id character(3) PRIMARY KEY
jrn_desc text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.jrnx

Journal: content one line for each accountancy writing

public.jrnx Structure
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)
public.tmp_pcmn.pcm_val j_poste account_type NOT NULL
j_grpt integer NOT NULL
j_rapt text
public.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
public.fiche.f_id f_id bigint

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.key_distribution

Distribution key for analytic

public.key_distribution Structure
F-Key Name Type Description
kd_id serial PRIMARY KEY

PK
kd_name text

Name of the key
kd_description text

Description of the key

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.key_distribution_activity

Contains the analytic account

public.key_distribution_activity Structure
F-Key Name Type Description
ka_id serial PRIMARY KEY

pk
public.key_distribution_detail.ke_id ke_id bigint NOT NULL

fk to key_distribution_detail
public.poste_analytique.po_id po_id bigint

fk to poste_analytique
public.plan_analytique.pa_id pa_id bigint NOT NULL

fk to plan_analytique

Index - Schema public

Table: public.key_distribution_detail

Row of activity and percent

public.key_distribution_detail Structure
F-Key Name Type Description
ke_id serial PRIMARY KEY

pk
public.key_distribution.kd_id kd_id bigint NOT NULL

fk to key_distribution
ke_row integer NOT NULL

group order
ke_percent numeric(20,4) NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.key_distribution_ledger

Legder where the distribution key can be used

public.key_distribution_ledger Structure
F-Key Name Type Description
kl_id serial PRIMARY KEY

pk
public.key_distribution.kd_id kd_id bigint NOT NULL

fk to key_distribution
public.jrn_def.jrn_def_id jrn_def_id bigint NOT NULL

fk to jrnd_def, ledger where this key is available

Index - Schema public

Table: public.letter_cred

Letter cred

public.letter_cred Structure
F-Key Name Type Description
lc_id serial PRIMARY KEY
public.jrnx.j_id j_id bigint UNIQUE NOT NULL
public.jnt_letter.jl_id jl_id bigint NOT NULL

Index - Schema public

Table: public.letter_deb

letter deb

public.letter_deb Structure
F-Key Name Type Description
ld_id serial PRIMARY KEY
public.jrnx.j_id j_id bigint UNIQUE NOT NULL
public.jnt_letter.jl_id jl_id bigint NOT NULL

Index - Schema public

Table: public.link_action_type

public.link_action_type Structure
F-Key Name Type Description
l_id bigserial PRIMARY KEY
l_desc character varying

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.menu_default

default menu for certains actions

public.menu_default Structure
F-Key Name Type Description
md_id serial PRIMARY KEY
md_code text UNIQUE NOT NULL
me_code text NOT NULL

Index - Schema public

Table: public.menu_ref

Definition of all the menu

public.menu_ref Structure
F-Key Name Type Description
me_code text PRIMARY KEY

Menu Code
me_menu text

Label to display
me_file text

if not empty file to include
me_url text

url
me_description text
me_parameter text
me_javascript text
me_type character varying(2)

ME for menu PR for Printing SP for special meaning (ex: return to line) PL for plugin
me_description_etendue text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.op_predef

predefined operation

public.op_predef Structure
F-Key Name Type Description
od_id integer PRIMARY KEY DEFAULT nextval('op_def_op_seq'::regclass)
public.jrn_def.jrn_def_id jrn_def_id integer UNIQUE#1 NOT NULL

jrn_id
od_name text UNIQUE#1 NOT NULL

name of the operation
od_item integer NOT NULL
od_jrn_type text NOT NULL
od_direct boolean NOT NULL
od_description text

Index - Schema public

Table: public.op_predef_detail

contains the detail of predefined operations

public.op_predef_detail Structure
F-Key Name Type Description
opd_id integer PRIMARY KEY DEFAULT nextval('op_predef_detail_opd_id_seq'::regclass)
od_id integer NOT NULL
opd_poste text NOT NULL
opd_amount numeric(20,4)
opd_tva_id text
opd_quantity numeric(20,4)
opd_debit boolean NOT NULL
opd_tva_amount numeric(20,4)
opd_comment text
opd_qc boolean

Index - Schema public

Table: public.operation_analytique

History of the analytic account

public.operation_analytique Structure
F-Key Name Type Description
oa_id integer PRIMARY KEY DEFAULT nextval('historique_analytique_ha_id_seq'::regclass)
public.poste_analytique.po_id po_id integer NOT NULL
oa_amount numeric(20,4) NOT NULL
oa_description text
oa_debit boolean NOT NULL DEFAULT true
public.jrnx.j_id j_id integer
oa_group integer NOT NULL DEFAULT nextval('s_oa_group'::regclass)
oa_date date NOT NULL
oa_row integer
oa_jrnx_id_source bigint

jrnx.j_id source of this amount, this amount is computed from an amount giving a ND VAT.Normally NULL is there is no ND VAT.
oa_positive character(1) NOT NULL DEFAULT 'Y'::bpchar

Sign of the amount
public.fiche.f_id f_id bigint

FK to fiche.f_id , used only with ODS

 

public.operation_analytique Constraints
Name Constraint
operation_analytique_oa_amount_check CHECK ((oa_amount >= (0)::numeric))

Index - Schema public

Table: public.operation_currency

Information about currency

public.operation_currency Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
oc_amount numeric(20,6) NOT NULL

amount in currency
oc_vat_amount numeric(20,6)

vat amount in currency
oc_price_unit numeric(20,6)

unit price in currency
public.jrnx.j_id j_id bigint NOT NULL

fk to jrnx

Index - Schema public

Table: public.operation_exercice

public.operation_exercice Structure
F-Key Name Type Description
oe_id bigserial PRIMARY KEY
oe_date date
oe_type text NOT NULL
oe_text text
oe_dossier_id integer NOT NULL
oe_exercice integer NOT NULL
jr_internal text
oe_transfer_date timestamp without time zone
tech_user text
tech_date timestamp without time zone DEFAULT now()

 

public.operation_exercice Constraints
Name Constraint
operation_exercice_oe_type_check CHECK (((oe_type = 'opening'::text) OR (oe_type = 'closing'::text)))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.operation_exercice_detail

public.operation_exercice_detail Structure
F-Key Name Type Description
oed_id bigserial PRIMARY KEY
public.operation_exercice.oe_id oe_id bigint NOT NULL
oed_poste account_type
oed_qcode text
oed_label text
oed_amount numeric(20,4)
oed_debit boolean

Index - Schema public

Table: public.operation_tag

Tag for operation

public.operation_tag Structure
F-Key Name Type Description
opt_id bigserial PRIMARY KEY
public.jrn.jr_id jrn_id bigint UNIQUE#1
public.tags.t_id tag_id integer UNIQUE#1

Index - Schema public

Table: public.parameter

parameter of the company

public.parameter Structure
F-Key Name Type Description
pr_id text PRIMARY KEY
pr_value text

Index - Schema public

Table: public.parameter_extra

Extra parameter for the folder

public.parameter_extra Structure
F-Key Name Type Description
id serial PRIMARY KEY
pe_code text UNIQUE

Code used in the Document:generate
pe_label text

Label description
pe_value text

Value which will replace the tag in Document:generate

Index - Schema public

Table: public.parameter_internal

Internal parameter, used by the application , it can't not be changed by the interface

public.parameter_internal Structure
F-Key Name Type Description
pi_id text PRIMARY KEY
pi_value text

Index - Schema public

Table: public.parm_appearance

public.parm_appearance Structure
F-Key Name Type Description
a_code text PRIMARY KEY
a_value text NOT NULL

Index - Schema public

Table: public.parm_code

Parameter code and accountancy

public.parm_code Structure
F-Key Name Type Description
p_code text PRIMARY KEY
p_value text
p_comment text

Index - Schema public

Table: public.parm_money

Currency conversion

public.parm_money Structure
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)

Index - Schema public

Table: public.parm_periode

Periode definition

public.parm_periode Structure
F-Key Name Type Description
p_id integer PRIMARY KEY DEFAULT nextval(('s_periode'::text)::regclass)
p_start date NOT NULL

Start date of periode
p_end date NOT NULL

End date of periode
p_exercice text NOT NULL DEFAULT to_char(now(), 'YYYY'::text)

Exercice
p_closed boolean DEFAULT false

is closed
p_central boolean DEFAULT false

is centralized (obsolete)
p_exercice_label text NOT NULL

label of the exercice

 

public.parm_periode Constraints
Name Constraint
parm_periode_check CHECK ((p_end >= p_start))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.parm_poste

Contains data for finding is the type of the account (asset)

public.parm_poste Structure
F-Key Name Type Description
p_value account_type PRIMARY KEY
p_type text NOT NULL

Index - Schema public

Table: public.payment_method

Contains the different media of payment and the corresponding ledger

public.payment_method Structure
F-Key Name Type Description
mp_id serial PRIMARY KEY
mp_lib text NOT NULL
public.jrn_def.jrn_def_id mp_jrn_def_id integer NOT NULL
public.fiche_def.fd_id mp_fd_id bigint
mp_qcode text
public.jrn_def.jrn_def_id jrn_def_id bigint

Ledger using this payment method

Index - Schema public

Table: public.plan_analytique

Plan Analytique (max 5)

public.plan_analytique Structure
F-Key Name Type Description
pa_id integer PRIMARY KEY DEFAULT nextval('plan_analytique_pa_id_seq'::regclass)
pa_name text UNIQUE NOT NULL DEFAULT 'Sans Nom'::text
pa_description text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.poste_analytique

Poste Analytique

public.poste_analytique Structure
F-Key Name Type Description
po_id integer PRIMARY KEY DEFAULT nextval('poste_analytique_po_id_seq'::regclass)
po_name text NOT NULL
public.plan_analytique.pa_id pa_id integer NOT NULL
po_amount numeric(20,4) NOT NULL DEFAULT 0.0
po_description text
ga_id character varying(10)
po_state integer NOT NULL DEFAULT 1

Analytic Account state : 0 disabled 0 enabled

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.profile

Available profile

public.profile Structure
F-Key Name Type Description
p_name text NOT NULL

Name of the profile
p_id serial PRIMARY KEY
p_desc text

description of the profile
with_calc boolean DEFAULT true

show the calculator
with_direct_form boolean DEFAULT true

show the direct form
with_search_card integer

Display a icon for searching card : 1 display, 0 not displaid

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.profile_menu

Join between the profile and the menu

public.profile_menu Structure
F-Key Name Type Description
pm_id serial PRIMARY KEY
public.menu_ref.me_code me_code text
me_code_dep text

menu code dependency
public.profile.p_id p_id integer

link to profile
p_order integer

order of displaying menu
public.profile_menu_type.pm_type p_type_display text NOT NULL

M is a module E is a menu S is a select (for plugin)
pm_default integer

default menu
pm_id_dep bigint

parent of this menu item

Index - Schema public

Table: public.profile_menu_type

Type of menu

public.profile_menu_type Structure
F-Key Name Type Description
pm_type text PRIMARY KEY
pm_desc text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.profile_mobile

Menu for mobile device

public.profile_mobile Structure
F-Key Name Type Description
pmo_id serial PRIMARY KEY

primary key
public.menu_ref.me_code me_code text UNIQUE#1 NOT NULL

Code of menu_ref to execute
pmo_order integer NOT NULL

item order in menu
public.profile.p_id p_id integer UNIQUE#1 NOT NULL

Profile id
pmo_default character(1) DEFAULT '1'::bpchar

possible values are 1 , the default HTML header (javascript,CSS,...) is loaded , 0 nothing is loaded from noalyss

Index - Schema public

Table: public.profile_sec_repository

Available profile for user

public.profile_sec_repository Structure
F-Key Name Type Description
ur_id bigserial PRIMARY KEY

pk
public.profile.p_id p_id bigint UNIQUE#1

fk to profile
public.stock_repository.r_id r_id bigint UNIQUE#1

fk to stock_repository
ur_right character(1)

Type of right : R for readonly W for write

 

public.profile_sec_repository Constraints
Name Constraint
user_sec_profile_ur_right_check CHECK ((ur_right = ANY (ARRAY['R'::bpchar, 'W'::bpchar])))

Index - Schema public

Table: public.profile_user

Contains the available profile for users

public.profile_user Structure
F-Key Name Type Description
user_name text UNIQUE#1 NOT NULL

fk to available_user : login
pu_id serial PRIMARY KEY
public.profile.p_id p_id integer UNIQUE#1

fk to profile

Index - Schema public

Table: public.quant_fin

Simple operation for financial

public.quant_fin Structure
F-Key Name Type Description
qf_id bigserial PRIMARY KEY
public.fiche.f_id qf_bank bigint
public.jrn.jr_id jr_id bigint
public.fiche.f_id qf_other bigint
qf_amount numeric(20,4)
public.jrnx.j_id j_id bigint

Index - Schema public

Table: public.quant_purchase

Supplemental info for purchase

public.quant_purchase Structure
F-Key Name Type Description
qp_id integer PRIMARY KEY DEFAULT nextval(('s_quantity'::text)::regclass)
public.jrn.jr_internal qp_internal text
public.jrnx.j_id j_id integer NOT NULL
qp_fiche integer NOT NULL
qp_quantite numeric(20,4) NOT NULL
qp_price numeric(20,4)
qp_vat numeric(20,4) DEFAULT 0.0
public.tva_rate.tva_id qp_vat_code integer
qp_nd_amount numeric(20,4) DEFAULT 0.0
qp_nd_tva numeric(20,4) DEFAULT 0.0
qp_nd_tva_recup numeric(20,4) DEFAULT 0.0
qp_supplier integer NOT NULL
qp_valid character(1) NOT NULL DEFAULT 'Y'::bpchar
qp_dep_priv numeric(20,4) DEFAULT 0.0
qp_vat_sided numeric(20,4) DEFAULT 0.0

amount of the VAT which avoid VAT, case of the VAT which add the same amount at the deb and cred
qp_unit numeric(20,4)

Index - Schema public

Table: public.quant_sold

Contains about invoice for customer

public.quant_sold Structure
F-Key Name Type Description
qs_id integer PRIMARY KEY DEFAULT nextval(('s_quantity'::text)::regclass)
public.jrn.jr_internal qs_internal text
qs_fiche integer NOT NULL
qs_quantite numeric(20,4) NOT NULL
qs_price numeric(20,4)
qs_vat numeric(20,4)
public.tva_rate.tva_id qs_vat_code integer
qs_client integer NOT NULL
qs_valid character(1) NOT NULL DEFAULT 'Y'::bpchar
public.jrnx.j_id j_id integer NOT NULL
qs_vat_sided numeric(20,4) DEFAULT 0.0
qs_unit numeric(20,4)

Index - Schema public

Table: public.stock_change

Change of stock

public.stock_change Structure
F-Key Name Type Description
c_id bigserial PRIMARY KEY
c_comment text
c_date date
tech_user text
public.stock_repository.r_id r_id bigint
tech_date time without time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.stock_goods

About the goods

public.stock_goods Structure
F-Key Name Type Description
sg_id integer PRIMARY KEY DEFAULT nextval(('s_stock_goods'::text)::regclass)
public.jrnx.j_id j_id integer
public.fiche.f_id f_id integer
sg_code text
sg_quantity numeric(8,4)
sg_type character(1) NOT NULL DEFAULT 'c'::bpchar
sg_date date
sg_tech_date date DEFAULT now()
sg_tech_user text
sg_comment character varying(80)
sg_exercice character varying(4)
r_id bigint
public.stock_change.c_id c_id bigint

 

public.stock_goods Constraints
Name Constraint
stock_goods_sg_type CHECK (((sg_type = 'c'::bpchar) OR (sg_type = 'd'::bpchar)))

Index - Schema public

Table: public.stock_repository

stock repository

public.stock_repository Structure
F-Key Name Type Description
r_id bigserial PRIMARY KEY

pk
r_name text

name of the stock
r_adress text

adress of the stock
r_country text

country of the stock
r_city text

City of the stock
r_phone text

Phone number

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.tag_group

Group of tags

public.tag_group Structure
F-Key Name Type Description
tg_id bigserial PRIMARY KEY
tg_name character varying NOT NULL

Nom du groupe

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.tags

Tags name

public.tags Structure
F-Key Name Type Description
t_id serial PRIMARY KEY
t_tag text NOT NULL
t_description text
t_actif character(1) DEFAULT 'Y'::bpchar

Y if the tag is activate and can be used
t_color integer DEFAULT 1

 

public.tags Constraints
Name Constraint
tags_check CHECK ((t_actif = ANY (ARRAY['N'::bpchar, 'Y'::bpchar])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.tmp_pcmn

Plan comptable minimum normalisé

public.tmp_pcmn Structure
F-Key Name Type Description
pcm_val account_type PRIMARY KEY
pcm_lib text
pcm_val_parent account_type
pcm_type text
id bigint UNIQUE NOT NULL DEFAULT nextval('tmp_pcmn_id_seq'::regclass)

allow to identify the row, it is unique and not null (pseudo pk)
pcm_direct_use character varying(1) NOT NULL DEFAULT 'Y'::character varying

Value are N or Y , N cannot be used directly , not even through a card

 

public.tmp_pcmn Constraints
Name Constraint
pcm_direct_use_ck CHECK (((pcm_direct_use)::text = ANY (ARRAY[('Y'::character varying)::text, ('N'::character varying)::text])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.tmp_stockgood

public.tmp_stockgood Structure
F-Key Name Type Description
s_id bigserial PRIMARY KEY
s_date timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.tmp_stockgood_detail

public.tmp_stockgood_detail Structure
F-Key Name Type Description
d_id bigserial PRIMARY KEY
public.tmp_stockgood.s_id s_id bigint
sg_code text
s_qin numeric(20,4)
s_qout numeric(20,4)
r_id bigint
f_id bigint

Index - Schema public

Table: public.todo_list

Todo list

public.todo_list Structure
F-Key Name Type Description
tl_id integer PRIMARY KEY DEFAULT nextval('todo_list_tl_id_seq'::regclass)
tl_date date NOT NULL
tl_title text NOT NULL
tl_desc text
use_login text NOT NULL
is_public character(1) NOT NULL DEFAULT 'N'::bpchar

Flag for the public parameter

 

public.todo_list Constraints
Name Constraint
ck_is_public CHECK ((is_public = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.todo_list_shared

Note of todo list shared with other users

public.todo_list_shared Structure
F-Key Name Type Description
id serial PRIMARY KEY
public.todo_list.tl_id todo_list_id integer UNIQUE#1 NOT NULL

fk to todo_list
use_login text UNIQUE#1 NOT NULL

user login

Index - Schema public

Table: public.tool_uos

public.tool_uos Structure
F-Key Name Type Description
uos_value bigint PRIMARY KEY DEFAULT nextval('uos_pk_seq'::regclass)

Index - Schema public

Table: public.tva_rate

Rate of vat

public.tva_rate Structure
F-Key Name Type Description
tva_id integer PRIMARY KEY DEFAULT nextval('s_tva'::regclass)
tva_label text NOT NULL

Label
tva_rate numeric(8,4) NOT NULL DEFAULT 0.0

Rate
tva_comment text

Description of VAT
tva_poste text

accounting
tva_both_side integer

If set to 1 , the amount VAT will be reversed (autoliquidation)
tva_payment_sale character(1) DEFAULT 'O'::bpchar

Check if the VAT on Sale must be declared when at the date of payment (P) or the date of operation (O)
tva_payment_purchase character(1) DEFAULT 'O'::bpchar

Check if the VAT on Purchase must be declared when at the date of payment (P) or the date of operation (O)
tva_code text UNIQUE NOT NULL
public.tmp_pcmn.pcm_val tva_reverse_account account_type

Accouting for reversed VAT
tva_peppol_code character(1)

Code for Peppol : S standard,Z zéro, AE Autoliquidation ,K autoliquidation intra, G : exempté TVA pour export

 

public.tva_rate Constraints
Name Constraint
tva_code_number_check CHECK ((tva_code !~ '^([0-9]+)$'::text))
tva_rate_purchase_check CHECK ((tva_payment_purchase = ANY (ARRAY['O'::bpchar, 'P'::bpchar])))
tva_rate_sale_check CHECK ((tva_payment_sale = ANY (ARRAY['O'::bpchar, 'P'::bpchar])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.user_active_security

Security for user

public.user_active_security Structure
F-Key Name Type Description
id serial PRIMARY KEY
us_login text NOT NULL

user's login
us_ledger character varying(1) NOT NULL

Flag Security for ledger
us_action character varying(1) NOT NULL

Security for action

 

public.user_active_security Constraints
Name Constraint
user_active_security_action_check CHECK (((us_action)::text = ANY (ARRAY[('Y'::character varying)::text, ('N'::character varying)::text])))
user_active_security_ledger_check CHECK (((us_ledger)::text = ANY (ARRAY[('Y'::character varying)::text, ('N'::character varying)::text])))

Index - Schema public

Table: public.user_filter

Filter for the search

public.user_filter Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
login text
nb_jrn integer
date_start character varying(10)
date_end character varying(10)
description text
amount_min numeric(20,4)
amount_max numeric(20,4)
qcode text
accounting text
r_jrn text
date_paid_start character varying(10)
date_paid_end character varying(10)
ledger_type character varying(5)
all_ledger integer
filter_name text NOT NULL
operation_filter text NOT NULL

Status of the operation : paid, unpaid or all operation
uf_tag text

Tag list
uf_tag_option integer

0 : all tags must be present, 1: at least one
uf_currency_code integer

correspond to currency.id
tva_id_search integer

VAT id

Index - Schema public

Table: public.user_local_pref

The user's local parameter

public.user_local_pref Structure
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

Index - Schema public

Table: public.user_sec_act

Security on actions for user

public.user_sec_act Structure
F-Key Name Type Description
ua_id integer PRIMARY KEY DEFAULT nextval(('s_user_act'::text)::regclass)
ua_login text
public.action.ac_id ua_act_id integer

Index - Schema public

Table: public.user_sec_action_profile

Available profile for user

public.user_sec_action_profile Structure
F-Key Name Type Description
ua_id bigserial PRIMARY KEY

pk
public.profile.p_id p_id bigint UNIQUE#1

fk to profile
public.profile.p_id p_granted bigint UNIQUE#1
ua_right character(1)

Type of right : R for readonly W for write

 

public.user_sec_action_profile Constraints
Name Constraint
user_sec_action_profile_ua_right_check CHECK ((ua_right = ANY (ARRAY['R'::bpchar, 'W'::bpchar, 'X'::bpchar, 'O'::bpchar])))

Index - Schema public

Table: public.user_sec_jrn

Security on ledger for users

public.user_sec_jrn Structure
F-Key Name Type Description
uj_id integer PRIMARY KEY DEFAULT nextval(('s_user_jrn'::text)::regclass)
uj_login text UNIQUE#1
public.jrn_def.jrn_def_id uj_jrn_id integer UNIQUE#1
uj_priv text

Index - Schema public

Table: public.user_widget

public.user_widget Structure
F-Key Name Type Description
uw_id serial PRIMARY KEY
use_login text NOT NULL
public.widget_dashboard.wd_id dashboard_widget_id integer NOT NULL
uw_parameter text
uw_order integer

Index - Schema public

View: public.v_all_account_currency

public.v_all_account_currency Structure
F-Key Name Type Description
sum_oc_amount numeric
sum_oc_vat_amount numeric
j_poste account_type
j_id integer 
SELECT sum
(oc.oc_amount) AS sum_oc_amount
,
    sum
(oc.oc_vat_amount) AS sum_oc_vat_amount
,
    x.j_poste
,
    x.j_id
   
FROM (
     (quant_fin q1
     
        JOIN (
            SELECT j1.j_id
                 ,
            j.jr_id
                 ,
            j1.f_id
                 ,
            j1.j_poste
           
              FROM (jrnx j1
             
                    JOIN jrn j 
                      ON (
                             (j1.j_grpt = j.jr_grpt_id)
                       )
                 )
           ) x 
          ON (
                 (q1.jr_id = x.jr_id)
           )
     )
     
  JOIN operation_currency oc 
    ON (
           (oc.j_id = q1.j_id)
     )
)
  
GROUP BY x.j_poste
, x.j_id

UNION ALL
 
SELECT sum
(oc.oc_amount) AS sum_oc_amount
,
    sum
(oc.oc_vat_amount) AS sum_oc_vat_amount
,
    x.j_poste
,
    x.j_id
   
FROM (
     (quant_purchase q1
     
        JOIN (
            SELECT j1.j_id
                 ,
            j.jr_id
                 ,
            j1.f_id
                 ,
            j1.j_poste
                 ,
            j.jr_internal
           
              FROM (jrnx j1
             
                    JOIN jrn j 
                      ON (
                             (j1.j_grpt = j.jr_grpt_id)
                       )
                 )
           ) x 
          ON (
                 (
                       (q1.qp_internal = x.jr_internal)
                     AND (
                             (x.f_id = q1.qp_fiche)
                            OR (x.f_id = q1.qp_supplier)
                       )
                 )
           )
     )
     
  JOIN operation_currency oc 
    ON (
           (oc.j_id = q1.j_id)
     )
)
  
GROUP BY x.j_poste
, x.j_id

UNION ALL
 
SELECT sum
(oc.oc_amount) AS sum_oc_amount
,
    sum
(oc.oc_vat_amount) AS sum_oc_vat_amount
,
    x.j_poste
,
    x.j_id
   
FROM (
     (quant_sold q1
     
        JOIN (
            SELECT j1.j_id
                 ,
            j.jr_id
                 ,
            j1.f_id
                 ,
            j1.j_poste
                 ,
            j.jr_internal
           
              FROM (jrnx j1
             
                    JOIN jrn j 
                      ON (
                             (j1.j_grpt = j.jr_grpt_id)
                       )
                 )
           ) x 
          ON (
                 (
                       (q1.qs_internal = x.jr_internal)
                     AND (
                             (x.f_id = q1.qs_fiche)
                            OR (x.f_id = q1.qs_client)
                       )
                 )
           )
     )
     
  JOIN operation_currency oc 
    ON (
           (oc.j_id = q1.j_id)
     )
)
  
GROUP BY x.j_poste
, x.j_id;

Index - Schema public

View: public.v_all_card_currency

public.v_all_card_currency Structure
F-Key Name Type Description
sum_oc_amount numeric
sum_oc_vat_amount numeric
f_id bigint
j_id bigint 
SELECT sum
(operation_currency.oc_amount) AS sum_oc_amount
,
    sum
(operation_currency.oc_vat_amount) AS sum_oc_vat_amount
,
    jrnx.f_id
,
    operation_currency.j_id
   
FROM (operation_currency
     
  JOIN jrnx 
 USING (j_id)
)
  
GROUP BY jrnx.f_id
, operation_currency.j_id;

Index - Schema public

View: public.v_all_menu

public.v_all_menu Structure
F-Key Name Type Description
me_code text
pm_id integer
me_code_dep text
p_order integer
p_type_display text
p_name text
p_desc text
me_menu text
me_file text
me_url text
me_parameter text
me_javascript text
me_type character varying(2)
p_id integer
me_description text 
SELECT pm.me_code
,
    pm.pm_id
,
    pm.me_code_dep
,
    pm.p_order
,
    pm.p_type_display
,
    p.p_name
,
    p.p_desc
,
    mr.me_menu
,
    mr.me_file
,
    mr.me_url
,
    mr.me_parameter
,
    mr.me_javascript
,
    mr.me_type
,
    pm.p_id
,
    mr.me_description
   
FROM (
     (profile_menu pm
     
        JOIN profile p 
          ON (
                 (p.p_id = pm.p_id)
           )
     )
     
  JOIN menu_ref mr 
 USING (me_code)
)
  
ORDER BY pm.p_order;

Index - Schema public

View: public.v_contact

public.v_contact Structure
F-Key Name Type Description
f_id integer
f_enable character(1)
contact_fname text
contact_name text
contact_qcode text
contact_company text
contact_mobile text
contact_phone text
contact_email text
contact_fax text
card_category integer 
 WITH contact_data AS 
(
         
SELECT f.f_id
     ,
            f.f_enable
     ,
            f.fd_id
           
  FROM (fiche f
             
        JOIN fiche_def fd 
          ON (
                 (f.fd_id = fd.fd_id)
           )
     )
          
 WHERE (fd.frd_id = 16)
        
)
 
SELECT f_id
,
    f_enable
,
    
(
SELECT fiche_detail.ad_value
           
  FROM fiche_detail
          
 WHERE (
           (fiche_detail.ad_id = 32)
         AND (fiche_detail.f_id = cd.f_id)
     )
) AS contact_fname
,
    
(
SELECT fiche_detail.ad_value
           
  FROM fiche_detail
          
 WHERE (
           (fiche_detail.ad_id = 1)
         AND (fiche_detail.f_id = cd.f_id)
     )
) AS contact_name
,
    
(
SELECT fiche_detail.ad_value
           
  FROM fiche_detail
          
 WHERE (
           (fiche_detail.ad_id = 23)
         AND (fiche_detail.f_id = cd.f_id)
     )
) AS contact_qcode
,
    
(
SELECT fiche_detail.ad_value
           
  FROM fiche_detail
          
 WHERE (
           (fiche_detail.ad_id = 25)
         AND (fiche_detail.f_id = cd.f_id)
     )
) AS contact_company
,
    
(
SELECT fiche_detail.ad_value
           
  FROM fiche_detail
          
 WHERE (
           (fiche_detail.ad_id = 27)
         AND (fiche_detail.f_id = cd.f_id)
     )
) AS contact_mobile
,
    
(
SELECT fiche_detail.ad_value
           
  FROM fiche_detail
          
 WHERE (
           (fiche_detail.ad_id = 17)
         AND (fiche_detail.f_id = cd.f_id)
     )
) AS contact_phone
,
    
(
SELECT fiche_detail.ad_value
           
  FROM fiche_detail
          
 WHERE (
           (fiche_detail.ad_id = 18)
         AND (fiche_detail.f_id = cd.f_id)
     )
) AS contact_email
,
    
(
SELECT fiche_detail.ad_value
           
  FROM fiche_detail
          
 WHERE (
           (fiche_detail.ad_id = 26)
         AND (fiche_detail.f_id = cd.f_id)
     )
) AS contact_fax
,
    fd_id AS card_category
   
FROM contact_data cd;

Index - Schema public

View: public.v_currency_last_value

public.v_currency_last_value Structure
F-Key Name Type Description
currency_id integer
cr_name character varying(80)
cr_code_iso character varying(10)
currency_history_id integer
ch_value numeric(20,8)
str_from text 
 WITH recent_rate AS 
(
         
SELECT currency_history.currency_id
     ,
            max
     (currency_history.ch_from) AS rc_from
           
  FROM currency_history
          
GROUP BY currency_history.currency_id
        
)
 
SELECT cr1.id AS currency_id
,
    cr1.cr_name
,
    cr1.cr_code_iso
,
    ch1.id AS currency_history_id
,
    ch1.ch_value
,
    to_char
(
     (recent_rate.rc_from)::timestamp with time zone
     ,'DD.MM.YYYY'::text
) AS str_from
   
FROM (
     (currency cr1
     
        JOIN recent_rate 
          ON (
                 (recent_rate.currency_id = cr1.id)
           )
     )
     
  JOIN currency_history ch1 
    ON (
           (
                 (recent_rate.currency_id = ch1.currency_id)
               AND (recent_rate.rc_from = ch1.ch_from)
           )
     )
);

Index - Schema public

View: public.v_detail_purchase

public.v_detail_purchase Structure
F-Key Name Type Description
jr_id integer
jr_date date
jr_date_paid date
jr_ech date
jr_tech_per integer
jr_comment text
jr_pj_number text
jr_internal text
jr_def_id integer
j_poste account_type
j_text text
j_qcode text
jr_rapt text
item_card integer
item_name text
qp_supplier integer
tiers_name text
quick_code text
tva_label text
tva_comment text
tva_both_side integer
vat_sided numeric(20,4)
vat_code integer
vat numeric(20,4)
price numeric(20,4)
quantity numeric(20,4)
price_per_unit numeric
non_ded_amount numeric(20,4)
non_ded_tva numeric(20,4)
non_ded_tva_recup numeric(20,4)
htva numeric
tot_vat numeric
tot_tva_np numeric
other_tax_amount numeric
oc_amount numeric(20,6)
oc_vat_amount numeric(20,6)
cr_code_iso character varying(10) 
 WITH m AS 
(
         
SELECT sum
     (quant_purchase_1.qp_price) AS htva
     ,
            sum
     (quant_purchase_1.qp_vat) AS tot_vat
     ,
            sum
     (quant_purchase_1.qp_vat_sided) AS tot_tva_np
     ,
            jrn_1.jr_id
           
  FROM (
           (quant_purchase quant_purchase_1
             
              JOIN jrnx jrnx_1 
             USING (j_id)
           )
             
        JOIN jrn jrn_1 
          ON (
                 (jrnx_1.j_grpt = jrn_1.jr_grpt_id)
           )
     )
          
GROUP BY jrn_1.jr_id
        
)
, other_tax AS 
(
         
SELECT jrnx_1.j_grpt
     ,
            sum
     (
                CASE
                    WHEN 
           (jrnx_1.j_debit IS FALSE) THEN 
           (
                 (0)::numeric - jrnx_1.j_montant
           )
                    ELSE jrnx_1.j_montant
                END
     ) AS other_tax_amount
           
  FROM (jrnx jrnx_1
             
        JOIN jrn_tax 
       USING (j_id)
     )
          
GROUP BY jrnx_1.j_grpt
        
)
 
SELECT jrn.jr_id
,
    jrn.jr_date
,
    jrn.jr_date_paid
,
    jrn.jr_ech
,
    jrn.jr_tech_per
,
    jrn.jr_comment
,
    jrn.jr_pj_number
,
    jrn.jr_internal
,
    jrn.jr_def_id
,
    jrnx.j_poste
,
    jrnx.j_text
,
    jrnx.j_qcode
,
    jrn.jr_rapt
,
    quant_purchase.qp_fiche AS item_card
,
    a.name AS item_name
,
    quant_purchase.qp_supplier
,
    b.vw_name AS tiers_name
,
    b.quick_code
,
    tva_rate.tva_label
,
    tva_rate.tva_comment
,
    tva_rate.tva_both_side
,
    quant_purchase.qp_vat_sided AS vat_sided
,
    quant_purchase.qp_vat_code AS vat_code
,
    quant_purchase.qp_vat AS vat
,
    quant_purchase.qp_price AS price
,
    quant_purchase.qp_quantite AS quantity
,
    
(quant_purchase.qp_price / quant_purchase.qp_quantite) AS price_per_unit
,
    quant_purchase.qp_nd_amount AS non_ded_amount
,
    quant_purchase.qp_nd_tva AS non_ded_tva
,
    quant_purchase.qp_nd_tva_recup AS non_ded_tva_recup
,
    m.htva
,
    m.tot_vat
,
    m.tot_tva_np
,
    ot.other_tax_amount
,
    oc.oc_amount
,
    oc.oc_vat_amount
,
    
(
SELECT currency.cr_code_iso
           
  FROM currency
          
 WHERE (jrn.currency_id = currency.id)
) AS cr_code_iso
   
FROM (
     (
           (
                 (
                       (
                             (
                                   (
                                         (jrn
     
                                            JOIN jrnx 
                                              ON (
                                                     (jrn.jr_grpt_id = jrnx.j_grpt)
                                               )
                                         )
     
                                      JOIN quant_purchase 
                                     USING (j_id)
                                   )
     
                                JOIN vw_fiche_name a 
                                  ON (
                                         (quant_purchase.qp_fiche = a.f_id)
                                   )
                             )
     
                          JOIN vw_fiche_attr b 
                            ON (
                                   (quant_purchase.qp_supplier = b.f_id)
                             )
                       )
     
               LEFT JOIN tva_rate 
                      ON (
                             (quant_purchase.qp_vat_code = tva_rate.tva_id)
                       )
                 )
     
              JOIN m 
                ON (
                       (m.jr_id = jrn.jr_id)
                 )
           )
     
   LEFT JOIN operation_currency oc 
          ON (
                 (oc.j_id = jrnx.j_id)
           )
     )
     
LEFT JOIN other_tax ot 
    ON (
           (ot.j_grpt = jrn.jr_grpt_id)
     )
);

Index - Schema public

View: public.v_detail_sale

public.v_detail_sale Structure
F-Key Name Type Description
jr_id integer
jr_date date
jr_date_paid date
jr_ech date
jr_tech_per integer
jr_comment text
jr_pj_number text
jr_internal text
jr_def_id integer
j_poste account_type
j_text text
j_qcode text
jr_rapt text
item_card integer
item_name text
qs_client integer
tiers_name text
quick_code text
tva_label text
tva_comment text
tva_both_side integer
vat_sided numeric(20,4)
vat_code integer
vat numeric(20,4)
price numeric(20,4)
quantity numeric(20,4)
price_per_unit numeric
htva numeric
tot_vat numeric
tot_tva_np numeric
other_tax_amount numeric
oc_amount numeric(20,6)
oc_vat_amount numeric(20,6)
cr_code_iso character varying(10) 
 WITH m AS 
(
         
SELECT sum
     (quant_sold_1.qs_price) AS htva
     ,
            sum
     (quant_sold_1.qs_vat) AS tot_vat
     ,
            sum
     (quant_sold_1.qs_vat_sided) AS tot_tva_np
     ,
            jrn_1.jr_id
           
  FROM (
           (quant_sold quant_sold_1
             
              JOIN jrnx jrnx_1 
             USING (j_id)
           )
             
        JOIN jrn jrn_1 
          ON (
                 (jrnx_1.j_grpt = jrn_1.jr_grpt_id)
           )
     )
          
GROUP BY jrn_1.jr_id
        
)
, other_tax AS 
(
         
SELECT jrnx_1.j_grpt
     ,
            sum
     (
                CASE
                    WHEN 
           (jrnx_1.j_debit IS TRUE) THEN 
           (
                 (0)::numeric - jrnx_1.j_montant
           )
                    ELSE jrnx_1.j_montant
                END
     ) AS other_tax_amount
           
  FROM (jrnx jrnx_1
             
        JOIN jrn_tax 
       USING (j_id)
     )
          
GROUP BY jrnx_1.j_grpt
        
)
 
SELECT jrn.jr_id
,
    jrn.jr_date
,
    jrn.jr_date_paid
,
    jrn.jr_ech
,
    jrn.jr_tech_per
,
    jrn.jr_comment
,
    jrn.jr_pj_number
,
    jrn.jr_internal
,
    jrn.jr_def_id
,
    jrnx.j_poste
,
    jrnx.j_text
,
    jrnx.j_qcode
,
    jrn.jr_rapt
,
    quant_sold.qs_fiche AS item_card
,
    a.name AS item_name
,
    quant_sold.qs_client
,
    b.vw_name AS tiers_name
,
    b.quick_code
,
    tva_rate.tva_label
,
    tva_rate.tva_comment
,
    tva_rate.tva_both_side
,
    quant_sold.qs_vat_sided AS vat_sided
,
    quant_sold.qs_vat_code AS vat_code
,
    quant_sold.qs_vat AS vat
,
    quant_sold.qs_price AS price
,
    quant_sold.qs_quantite AS quantity
,
    
(quant_sold.qs_price / quant_sold.qs_quantite) AS price_per_unit
,
    m.htva
,
    m.tot_vat
,
    m.tot_tva_np
,
    ot.other_tax_amount
,
    oc.oc_amount
,
    oc.oc_vat_amount
,
    
(
SELECT currency.cr_code_iso
           
  FROM currency
          
 WHERE (jrn.currency_id = currency.id)
) AS cr_code_iso
   
FROM (
     (
           (
                 (
                       (
                             (
                                   (
                                         (jrn
     
                                            JOIN jrnx 
                                              ON (
                                                     (jrn.jr_grpt_id = jrnx.j_grpt)
                                               )
                                         )
     
                                      JOIN quant_sold 
                                     USING (j_id)
                                   )
     
                                JOIN vw_fiche_name a 
                                  ON (
                                         (quant_sold.qs_fiche = a.f_id)
                                   )
                             )
     
                          JOIN vw_fiche_attr b 
                            ON (
                                   (quant_sold.qs_client = b.f_id)
                             )
                       )
     
               LEFT JOIN tva_rate 
                      ON (
                             (quant_sold.qs_vat_code = tva_rate.tva_id)
                       )
                 )
     
              JOIN m 
                ON (
                       (m.jr_id = jrn.jr_id)
                 )
           )
     
   LEFT JOIN operation_currency oc 
          ON (
                 (oc.j_id = jrnx.j_id)
           )
     )
     
LEFT JOIN other_tax ot 
    ON (
           (ot.j_grpt = jrn.jr_grpt_id)
     )
);

Index - Schema public

View: public.v_menu_dependency

public.v_menu_dependency Structure
F-Key Name Type Description
code text
pm_id integer
me_code text
me_description text
me_description_etendue text
me_file text
v1menu text
higher_dep integer
v2menu text
v3menu text
p_type_display text
javascript text
p_id integer
v2pid integer
v3pid integer 
 WITH t_menu AS 
(
         
SELECT pm.pm_id
     ,
            mr.me_menu
     ,
            pm.me_code
     ,
            pm.me_code_dep
     ,
            pm.p_type_display
     ,
            mr.me_file
     ,
            mr.me_javascript
     ,
            mr.me_description
     ,
            mr.me_description_etendue
     ,
            p.p_id
           
  FROM (
           (profile_menu pm
             
              JOIN profile p 
                ON (
                       (p.p_id = pm.p_id)
                 )
           )
             
        JOIN menu_ref mr 
       USING (me_code)
     )
        
)
 
SELECT DISTINCT 
(
     (COALESCE
           (
                 (v3.me_code || '/'::text)
                 ,''::text
           ) || COALESCE
           (v2.me_code
                 ,''::text
           )
     ) ||
        CASE
            WHEN 
     (v2.me_code IS NULL) THEN COALESCE
     (v1.me_code
           ,''::text
     )
            WHEN 
     (v2.me_code IS NOT NULL) THEN COALESCE
     (
           ('/'::text || v1.me_code)
           ,''::text
     )
            ELSE NULL::text
        END
) AS code
,
    v1.pm_id
,
    v1.me_code
,
    v1.me_description
,
    v1.me_description_etendue
,
    v1.me_file
,
    
('> '::text || v1.me_menu) AS v1menu
,
        CASE
            WHEN 
(v2.pm_id IS NOT NULL) THEN v2.pm_id
            WHEN 
(v3.pm_id IS NOT NULL) THEN v3.pm_id
            ELSE NULL::integer
        END AS higher_dep
,
        CASE
            WHEN 
(COALESCE
     (v3.me_menu
           ,''::text
     ) <> ''::text
) THEN 
(' > '::text || v2.me_menu)
            ELSE v2.me_menu
        END AS v2menu
,
    v3.me_menu AS v3menu
,
    v3.p_type_display
,
    COALESCE
(v1.me_javascript
     , COALESCE
     (v2.me_javascript
           , v3.me_javascript
     )
) AS javascript
,
    v1.p_id
,
    v2.p_id AS v2pid
,
    v3.p_id AS v3pid
   
FROM (
     (t_menu v1
     
   LEFT JOIN t_menu v2 
          ON (
                 (v1.me_code_dep = v2.me_code)
           )
     )
     
LEFT JOIN t_menu v3 
    ON (
           (v2.me_code_dep = v3.me_code)
     )
)
  
WHERE (
     (COALESCE
           (v2.p_id
                 , v1.p_id
           ) = v1.p_id
     )
   AND (COALESCE
           (v3.p_id
                 , v1.p_id
           ) = v1.p_id
     )
   AND (v1.p_type_display <> 'P'::text)
)
  
ORDER BY v1.pm_id;

Index - Schema public

View: public.v_menu_description

Description des menus

public.v_menu_description Structure
F-Key Name Type Description
code text
me_code text
me_description text
me_description_etendue text
me_file text
user_name text
v1menu text
v2menu text
v3menu text
p_type_display text
javascript text
pm_id integer
pm_id_dep bigint
p_id integer 
 WITH t_menu AS 
(
         
SELECT pm.pm_id
     ,
            pm.pm_id_dep
     ,
            pm.p_id
     ,
            mr.me_menu
     ,
            pm.me_code
     ,
            pm.me_code_dep
     ,
            pm.p_type_display
     ,
            pu.user_name
     ,
            mr.me_file
     ,
            mr.me_javascript
     ,
            mr.me_description
     ,
            mr.me_description_etendue
           
  FROM (
           (
                 (profile_menu pm
             
                    JOIN profile_user pu 
                      ON (
                             (pu.p_id = pm.p_id)
                       )
                 )
             
              JOIN profile p 
                ON (
                       (p.p_id = pm.p_id)
                 )
           )
             
        JOIN menu_ref mr 
       USING (me_code)
     )
        
)
 
SELECT DISTINCT 
(
     (COALESCE
           (
                 (v3.me_code || '/'::text)
                 ,''::text
           ) || COALESCE
           (v2.me_code
                 ,''::text
           )
     ) ||
        CASE
            WHEN 
     (v2.me_code IS NULL) THEN COALESCE
     (v1.me_code
           ,''::text
     )
            WHEN 
     (v2.me_code IS NOT NULL) THEN COALESCE
     (
           ('/'::text || v1.me_code)
           ,''::text
     )
            ELSE NULL::text
        END
) AS code
,
    v1.me_code
,
    v1.me_description
,
    v1.me_description_etendue
,
    v1.me_file
,
    v1.user_name
,
    
('> '::text || v1.me_menu) AS v1menu
,
        CASE
            WHEN 
(COALESCE
     (v3.me_menu
           ,''::text
     ) <> ''::text
) THEN 
(' > '::text || v2.me_menu)
            ELSE v2.me_menu
        END AS v2menu
,
    v3.me_menu AS v3menu
,
    v3.p_type_display
,
    COALESCE
(v1.me_javascript
     , COALESCE
     (v2.me_javascript
           , v3.me_javascript
     )
) AS javascript
,
    v1.pm_id
,
    v1.pm_id_dep
,
    v1.p_id
   
FROM (
     (t_menu v1
     
   LEFT JOIN t_menu v2 
          ON (
                 (v1.me_code_dep = v2.me_code)
           )
     )
     
LEFT JOIN t_menu v3 
    ON (
           (v2.me_code_dep = v3.me_code)
     )
)
  
WHERE (
     (v1.p_type_display <> 'P'::text)
   AND (
           (COALESCE
                 (v1.me_file
                       ,''::text
                 ) <> ''::text
           )
          OR (COALESCE
                 (v1.me_javascript
                       ,''::text
                 ) <> ''::text
           )
     )
);

Index - Schema public

View: public.v_menu_description_favori

public.v_menu_description_favori Structure
F-Key Name Type Description
code text
me_code text
me_description text
me_description_etendue text
me_file text
user_name text
v1menu text
v2menu text
v3menu text
p_type_display text
javascript text 
 WITH t_menu AS 
(
         
SELECT mr.me_menu
     ,
            pm.me_code
     ,
            pm.me_code_dep
     ,
            pm.p_type_display
     ,
            pu.user_name
     ,
            mr.me_file
     ,
            mr.me_javascript
     ,
            mr.me_description
     ,
            mr.me_description_etendue
           
  FROM (
           (
                 (profile_menu pm
             
                    JOIN profile_user pu 
                      ON (
                             (pu.p_id = pm.p_id)
                       )
                 )
             
              JOIN profile p 
                ON (
                       (p.p_id = pm.p_id)
                 )
           )
             
        JOIN menu_ref mr 
       USING (me_code)
     )
        
)
 
SELECT DISTINCT 
(
     (COALESCE
           (
                 (v3.me_code || '/'::text)
                 ,''::text
           ) || COALESCE
           (v2.me_code
                 ,''::text
           )
     ) ||
        CASE
            WHEN 
     (v2.me_code IS NULL) THEN COALESCE
     (v1.me_code
           ,''::text
     )
            WHEN 
     (v2.me_code IS NOT NULL) THEN COALESCE
     (
           ('/'::text || v1.me_code)
           ,''::text
     )
            ELSE NULL::text
        END
) AS code
,
    v1.me_code
,
    v1.me_description
,
    v1.me_description_etendue
,
    v1.me_file
,
    v1.user_name
,
    
('> '::text || v1.me_menu) AS v1menu
,
        CASE
            WHEN 
(COALESCE
     (v3.me_menu
           ,''::text
     ) <> ''::text
) THEN 
(' > '::text || v2.me_menu)
            ELSE v2.me_menu
        END AS v2menu
,
    v3.me_menu AS v3menu
,
    v3.p_type_display
,
    COALESCE
(v1.me_javascript
     , COALESCE
     (v2.me_javascript
           , v3.me_javascript
     )
) AS javascript
   
FROM (
     (t_menu v1
     
   LEFT JOIN t_menu v2 
          ON (
                 (v1.me_code_dep = v2.me_code)
           )
     )
     
LEFT JOIN t_menu v3 
    ON (
           (v2.me_code_dep = v3.me_code)
     )
)
  
WHERE (v1.p_type_display <> 'P'::text);

Index - Schema public

View: public.v_menu_profile

Give the profile and the menu + dependencies

public.v_menu_profile Structure
F-Key Name Type Description
code text
p_type_display text
pm_id_v3 integer
pm_id_v2 integer
pm_id_v1 integer
p_id integer 
 WITH t_menu AS 
(
         
SELECT pm.pm_id
     ,
            pm.pm_id_dep
     ,
            pm.me_code
     ,
            pm.me_code_dep
     ,
            pm.p_type_display
     ,
            pm.p_id
           
  FROM (profile_menu pm
             
        JOIN profile p 
          ON (
                 (p.p_id = pm.p_id)
           )
     )
        
)
 
SELECT DISTINCT 
(
     (COALESCE
           (
                 (v3.me_code || '/'::text)
                 ,''::text
           ) || COALESCE
           (v2.me_code
                 ,''::text
           )
     ) ||
        CASE
            WHEN 
     (v2.me_code IS NULL) THEN COALESCE
     (v1.me_code
           ,''::text
     )
            WHEN 
     (v2.me_code IS NOT NULL) THEN COALESCE
     (
           ('/'::text || v1.me_code)
           ,''::text
     )
            ELSE NULL::text
        END
) AS code
,
    v3.p_type_display
,
    COALESCE
(v3.pm_id
     , 0
) AS pm_id_v3
,
    COALESCE
(v2.pm_id
     , 0
) AS pm_id_v2
,
    v1.pm_id AS pm_id_v1
,
    v1.p_id
   
FROM (
     (t_menu v1
     
   LEFT JOIN t_menu v2 
          ON (
                 (v1.pm_id_dep = v2.pm_id)
           )
     )
     
LEFT JOIN t_menu v3 
    ON (
           (v2.pm_id_dep = v3.pm_id)
     )
)
  
WHERE (v1.p_type_display <> 'P'::text);

Index - Schema public

View: public.v_quant_detail

public.v_quant_detail Structure
F-Key Name Type Description
jr_id integer
tiers integer
price numeric
vat_code integer
vat_amount numeric
dep_priv numeric
nd_tva numeric
nd_tva_recup numeric
nd_amount numeric
vat_sided numeric
jrn_def_name text
jrn_def_type character(3)
name text
jr_comment text
jr_montant numeric(20,4)
tva_label text 
 WITH quant AS 
(
         
SELECT quant_purchase.j_id
     ,
            quant_purchase.qp_fiche AS fiche_id
     ,
            quant_purchase.qp_supplier AS tiers
     ,
            quant_purchase.qp_vat AS vat_amount
     ,
            quant_purchase.qp_price AS price
     ,
            quant_purchase.qp_vat_code AS vat_code
     ,
            quant_purchase.qp_dep_priv AS dep_priv
     ,
            quant_purchase.qp_nd_tva AS nd_tva
     ,
            quant_purchase.qp_nd_tva_recup AS nd_tva_recup
     ,
            quant_purchase.qp_nd_amount AS nd_amount
     ,
            quant_purchase.qp_vat_sided AS vat_sided
           
  FROM quant_purchase
        
UNION ALL
         
SELECT quant_sold.j_id
     ,
            quant_sold.qs_fiche
     ,
            quant_sold.qs_client
     ,
            quant_sold.qs_vat
     ,
            quant_sold.qs_price
     ,
            quant_sold.qs_vat_code
     ,
            0
     ,
            0
     ,
            0
     ,
            0
     ,
            quant_sold.qs_vat_sided
           
  FROM quant_sold
        
)
, sum_jrn AS 
(
         
SELECT jrn2.jr_id
     ,
            quant2.tiers
     ,
            sum
     (quant2.price) AS price
     ,
            quant2.vat_code
     ,
            sum
     (quant2.vat_amount) AS vat_amount
     ,
            sum
     (quant2.dep_priv) AS dep_priv
     ,
            sum
     (quant2.nd_tva) AS nd_tva
     ,
            sum
     (quant2.nd_tva_recup) AS nd_tva_recup
     ,
            sum
     (quant2.nd_amount) AS nd_amount
     ,
            sum
     (quant2.vat_sided) AS vat_sided
           
  FROM (
           (jrn jrn2
             
              JOIN jrnx 
                ON (
                       (jrnx.j_grpt = jrn2.jr_grpt_id)
                 )
           )
             
        JOIN quant quant2 
       USING (j_id)
     )
          
GROUP BY quant2.tiers
     , jrn2.jr_id
     , quant2.vat_code
        
)
 
SELECT jrn.jr_id
,
    sum_jrn.tiers
,
    sum_jrn.price
,
    sum_jrn.vat_code
,
    sum_jrn.vat_amount
,
    sum_jrn.dep_priv
,
    sum_jrn.nd_tva
,
    sum_jrn.nd_tva_recup
,
    sum_jrn.nd_amount
,
    sum_jrn.vat_sided
,
    jrn_def.jrn_def_name
,
    jrn_def.jrn_def_type
,
    vw_fiche_name.name
,
    jrn.jr_comment
,
    jrn.jr_montant
,
    tva_rate.tva_label
   
FROM (
     (
           (
                 (jrn
     
                    JOIN sum_jrn 
                      ON (
                             (sum_jrn.jr_id = jrn.jr_id)
                       )
                 )
     
         LEFT JOIN vw_fiche_name 
                ON (
                       (sum_jrn.tiers = vw_fiche_name.f_id)
                 )
           )
     
        JOIN jrn_def 
          ON (
                 (jrn_def.jrn_def_id = jrn.jr_def_id)
           )
     )
     
  JOIN tva_rate 
    ON (
           (tva_rate.tva_id = sum_jrn.vat_code)
     )
);

Index - Schema public

View: public.v_tva_rate

public.v_tva_rate Structure
F-Key Name Type Description
tva_id integer
tva_rate numeric(8,4)
tva_code text
tva_label text
tva_comment text
tva_reverse_account account_type
tva_purchase text
tva_sale text
tva_both_side integer
tva_payment_purchase character(1)
tva_payment_sale character(1) 
SELECT tva_id
,
    tva_rate
,
    tva_code
,
    tva_label
,
    tva_comment
,
    tva_reverse_account
,
    split_part
(tva_poste
     ,','::text
     , 1
) AS tva_purchase
,
    split_part
(tva_poste
     ,','::text
     , 2
) AS tva_sale
,
    tva_both_side
,
    tva_payment_purchase
,
    tva_payment_sale
   
FROM tva_rate;

Index - Schema public

Table: public.version

DB version

public.version Structure
F-Key Name Type Description
val integer PRIMARY KEY
v_description text
v_date timestamp without time zone DEFAULT now()

Index - Schema public

View: public.vw_client

public.vw_client Structure
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
email text 
SELECT fiche.f_id
,
    a1.ad_value AS name
,
    a.ad_value AS quick_code
,
    b.ad_value AS tva_num
,
    c.ad_value AS poste_comptable
,
    d.ad_value AS rue
,
    e.ad_value AS code_postal
,
    f.ad_value AS pays
,
    g.ad_value AS telephone
,
    h.ad_value AS email
   
FROM (
     (
           (
                 (
                       (
                             (
                                   (
                                         (
                                               (
                                                     (
                                                           (fiche
     
                                                              JOIN fiche_def 
                                                             USING (fd_id)
                                                           )
     
                                                        JOIN fiche_def_ref 
                                                       USING (frd_id)
                                                     )
     
                                             LEFT JOIN (
                                                      SELECT fiche_detail.jft_id
                                                           ,
            fiche_detail.f_id
                                                           ,
            fiche_detail.ad_id
                                                           ,
            fiche_detail.ad_value
           
                                                        FROM fiche_detail
          
                                                       WHERE (fiche_detail.ad_id = 1)
                                                     ) a1 
                                                 USING (f_id)
                                               )
     
                                       LEFT JOIN (
                                                SELECT fiche_detail.jft_id
                                                     ,
            fiche_detail.f_id
                                                     ,
            fiche_detail.ad_id
                                                     ,
            fiche_detail.ad_value
           
                                                  FROM fiche_detail
          
                                                 WHERE (fiche_detail.ad_id = 13)
                                               ) b 
                                           USING (f_id)
                                         )
     
                                 LEFT JOIN (
                                          SELECT fiche_detail.jft_id
                                               ,
            fiche_detail.f_id
                                               ,
            fiche_detail.ad_id
                                               ,
            fiche_detail.ad_value
           
                                            FROM fiche_detail
          
                                           WHERE (fiche_detail.ad_id = 23)
                                         ) a 
                                     USING (f_id)
                                   )
     
                           LEFT JOIN (
                                    SELECT fiche_detail.jft_id
                                         ,
            fiche_detail.f_id
                                         ,
            fiche_detail.ad_id
                                         ,
            fiche_detail.ad_value
           
                                      FROM fiche_detail
          
                                     WHERE (fiche_detail.ad_id = 5)
                                   ) c 
                               USING (f_id)
                             )
     
                     LEFT JOIN (
                              SELECT fiche_detail.jft_id
                                   ,
            fiche_detail.f_id
                                   ,
            fiche_detail.ad_id
                                   ,
            fiche_detail.ad_value
           
                                FROM fiche_detail
          
                               WHERE (fiche_detail.ad_id = 14)
                             ) d 
                         USING (f_id)
                       )
     
               LEFT JOIN (
                        SELECT fiche_detail.jft_id
                             ,
            fiche_detail.f_id
                             ,
            fiche_detail.ad_id
                             ,
            fiche_detail.ad_value
           
                          FROM fiche_detail
          
                         WHERE (fiche_detail.ad_id = 15)
                       ) e 
                   USING (f_id)
                 )
     
         LEFT JOIN (
                  SELECT fiche_detail.jft_id
                       ,
            fiche_detail.f_id
                       ,
            fiche_detail.ad_id
                       ,
            fiche_detail.ad_value
           
                    FROM fiche_detail
          
                   WHERE (fiche_detail.ad_id = 16)
                 ) f 
             USING (f_id)
           )
     
   LEFT JOIN (
            SELECT fiche_detail.jft_id
                 ,
            fiche_detail.f_id
                 ,
            fiche_detail.ad_id
                 ,
            fiche_detail.ad_value
           
              FROM fiche_detail
          
             WHERE (fiche_detail.ad_id = 17)
           ) g 
       USING (f_id)
     )
     
LEFT JOIN (
      SELECT fiche_detail.jft_id
           ,
            fiche_detail.f_id
           ,
            fiche_detail.ad_id
           ,
            fiche_detail.ad_value
           
        FROM fiche_detail
          
       WHERE (fiche_detail.ad_id = 18)
     ) h 
 USING (f_id)
)
  
WHERE (fiche_def_ref.frd_id = 9);

Index - Schema public

View: public.vw_fiche_attr

Some attribute for all cards

public.vw_fiche_attr Structure
F-Key Name Type Description
f_id integer
fd_id integer
vw_name text
vw_first_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
vw_description text
tva_num text
frd_id integer
accounting text
f_enable character(1) 
SELECT a.f_id
,
    a.fd_id
,
    a.ad_value AS vw_name
,
    k.ad_value AS vw_first_name
,
    b.ad_value AS vw_sell
,
    c.ad_value AS vw_buy
,
    d.ad_value AS tva_code
,
    tva_rate.tva_id
,
    tva_rate.tva_rate
,
    tva_rate.tva_label
,
    e.ad_value AS vw_addr
,
    f.ad_value AS vw_cp
,
    j.ad_value AS quick_code
,
    h.ad_value AS vw_description
,
    i.ad_value AS tva_num
,
    fiche_def.frd_id
,
    l.ad_value AS accounting
,
    a.f_enable
   
FROM (
     (
           (
                 (
                       (
                             (
                                   (
                                         (
                                               (
                                                     (
                                                           (
                                                                 (
                                                                       (
                                                                        SELECT fiche.f_id
                                                                             ,
            fiche.fd_id
                                                                             ,
            fiche.f_enable
                                                                             ,
            fiche_detail.ad_value
           
                                                                          FROM (fiche
             
                                                                           LEFT JOIN fiche_detail 
                                                                               USING (f_id)
                                                                             )
          
                                                                         WHERE (fiche_detail.ad_id = 1)
                                                                       ) a
     
                                                               LEFT JOIN (
                                                                        SELECT fiche_detail.f_id
                                                                             ,
            fiche_detail.ad_value
           
                                                                          FROM fiche_detail
          
                                                                         WHERE (fiche_detail.ad_id = 6)
                                                                       ) b 
                                                                      ON (
                                                                             (a.f_id = b.f_id)
                                                                       )
                                                                 )
     
                                                         LEFT JOIN (
                                                                  SELECT fiche_detail.f_id
                                                                       ,
            fiche_detail.ad_value
           
                                                                    FROM fiche_detail
          
                                                                   WHERE (fiche_detail.ad_id = 7)
                                                                 ) c 
                                                                ON (
                                                                       (a.f_id = c.f_id)
                                                                 )
                                                           )
     
                                                   LEFT JOIN (
                                                            SELECT fiche_detail.f_id
                                                                 ,
            fiche_detail.ad_value
           
                                                              FROM fiche_detail
          
                                                             WHERE (fiche_detail.ad_id = 2)
                                                           ) d 
                                                          ON (
                                                                 (a.f_id = d.f_id)
                                                           )
                                                     )
     
                                             LEFT JOIN (
                                                      SELECT fiche_detail.f_id
                                                           ,
            fiche_detail.ad_value
           
                                                        FROM fiche_detail
          
                                                       WHERE (fiche_detail.ad_id = 14)
                                                     ) e 
                                                    ON (
                                                           (a.f_id = e.f_id)
                                                     )
                                               )
     
                                       LEFT JOIN (
                                                SELECT fiche_detail.f_id
                                                     ,
            fiche_detail.ad_value
           
                                                  FROM fiche_detail
          
                                                 WHERE (fiche_detail.ad_id = 15)
                                               ) f 
                                              ON (
                                                     (a.f_id = f.f_id)
                                               )
                                         )
     
                                 LEFT JOIN (
                                          SELECT fiche_detail.f_id
                                               ,
            fiche_detail.ad_value
           
                                            FROM fiche_detail
          
                                           WHERE (fiche_detail.ad_id = 23)
                                         ) j 
                                        ON (
                                               (a.f_id = j.f_id)
                                         )
                                   )
     
                           LEFT JOIN (
                                    SELECT fiche_detail.f_id
                                         ,
            fiche_detail.ad_value
           
                                      FROM fiche_detail
          
                                     WHERE (fiche_detail.ad_id = 9)
                                   ) h 
                                  ON (
                                         (a.f_id = h.f_id)
                                   )
                             )
     
                     LEFT JOIN (
                              SELECT fiche_detail.f_id
                                   ,
            fiche_detail.ad_value
           
                                FROM fiche_detail
          
                               WHERE (fiche_detail.ad_id = 13)
                             ) i 
                            ON (
                                   (a.f_id = i.f_id)
                             )
                       )
     
               LEFT JOIN (
                        SELECT fiche_detail.f_id
                             ,
            fiche_detail.ad_value
           
                          FROM fiche_detail
          
                         WHERE (fiche_detail.ad_id = 32)
                       ) k 
                      ON (
                             (a.f_id = k.f_id)
                       )
                 )
     
         LEFT JOIN tva_rate 
                ON (
                       (d.ad_value = 
                             (tva_rate.tva_id)::text
                       )
                 )
           )
     
        JOIN fiche_def 
       USING (fd_id)
     )
     
LEFT JOIN (
      SELECT fiche_detail.f_id
           ,
            fiche_detail.ad_value
           
        FROM fiche_detail
          
       WHERE (fiche_detail.ad_id = 5)
     ) l 
    ON (
           (a.f_id = l.f_id)
     )
);

Index - Schema public

View: public.vw_fiche_def

all the attributs for card family

public.vw_fiche_def Structure
F-Key Name Type Description
fd_id integer
ad_id integer
ad_text text
fd_class_base text
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
,
    fiche_def.fd_class_base
,
    fiche_def.fd_label
,
    fiche_def.fd_create_account
,
    fiche_def.frd_id
   
FROM (
     (fiche_def
     
        JOIN jnt_fic_attr 
       USING (fd_id)
     )
     
  JOIN attr_def 
    ON (
           (attr_def.ad_id = jnt_fic_attr.ad_id)
     )
);

Index - Schema public

View: public.vw_fiche_min

public.vw_fiche_min Structure
F-Key Name Type Description
frd_id integer
ad_id integer
ad_text text
frd_text text
frd_class_base account_type 
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)
);

Index - Schema public

View: public.vw_fiche_name

public.vw_fiche_name Structure
F-Key Name Type Description
f_id integer
name text 
SELECT f_id
,
    ad_value AS name
   
FROM fiche_detail
  
WHERE (ad_id = 1);

Index - Schema public

View: public.vw_poste_qcode

public.vw_poste_qcode Structure
F-Key Name Type Description
f_id integer
j_poste text
j_qcode text 
SELECT c.f_id
,
    a.ad_value AS j_poste
,
    b.ad_value AS j_qcode
   
FROM (
     (fiche c
     
   LEFT JOIN (
            SELECT fiche_detail.f_id
                 ,
            fiche_detail.ad_value
           
              FROM fiche_detail
          
             WHERE (fiche_detail.ad_id = 5)
           ) a 
       USING (f_id)
     )
     
LEFT JOIN (
      SELECT fiche_detail.f_id
           ,
            fiche_detail.ad_value
           
        FROM fiche_detail
          
       WHERE (fiche_detail.ad_id = 23)
     ) b 
 USING (f_id)
);

Index - Schema public

View: public.vw_supplier

public.vw_supplier Structure
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
email text 
SELECT fiche.f_id
,
    a1.ad_value AS name
,
    a.ad_value AS quick_code
,
    b.ad_value AS tva_num
,
    c.ad_value AS poste_comptable
,
    d.ad_value AS rue
,
    e.ad_value AS code_postal
,
    f.ad_value AS pays
,
    g.ad_value AS telephone
,
    h.ad_value AS email
   
FROM (
     (
           (
                 (
                       (
                             (
                                   (
                                         (
                                               (
                                                     (
                                                           (fiche
     
                                                              JOIN fiche_def 
                                                             USING (fd_id)
                                                           )
     
                                                        JOIN fiche_def_ref 
                                                       USING (frd_id)
                                                     )
     
                                             LEFT JOIN (
                                                      SELECT fiche_detail.jft_id
                                                           ,
            fiche_detail.f_id
                                                           ,
            fiche_detail.ad_id
                                                           ,
            fiche_detail.ad_value
           
                                                        FROM fiche_detail
          
                                                       WHERE (fiche_detail.ad_id = 1)
                                                     ) a1 
                                                 USING (f_id)
                                               )
     
                                       LEFT JOIN (
                                                SELECT fiche_detail.jft_id
                                                     ,
            fiche_detail.f_id
                                                     ,
            fiche_detail.ad_id
                                                     ,
            fiche_detail.ad_value
           
                                                  FROM fiche_detail
          
                                                 WHERE (fiche_detail.ad_id = 13)
                                               ) b 
                                           USING (f_id)
                                         )
     
                                 LEFT JOIN (
                                          SELECT fiche_detail.jft_id
                                               ,
            fiche_detail.f_id
                                               ,
            fiche_detail.ad_id
                                               ,
            fiche_detail.ad_value
           
                                            FROM fiche_detail
          
                                           WHERE (fiche_detail.ad_id = 23)
                                         ) a 
                                     USING (f_id)
                                   )
     
                           LEFT JOIN (
                                    SELECT fiche_detail.jft_id
                                         ,
            fiche_detail.f_id
                                         ,
            fiche_detail.ad_id
                                         ,
            fiche_detail.ad_value
           
                                      FROM fiche_detail
          
                                     WHERE (fiche_detail.ad_id = 5)
                                   ) c 
                               USING (f_id)
                             )
     
                     LEFT JOIN (
                              SELECT fiche_detail.jft_id
                                   ,
            fiche_detail.f_id
                                   ,
            fiche_detail.ad_id
                                   ,
            fiche_detail.ad_value
           
                                FROM fiche_detail
          
                               WHERE (fiche_detail.ad_id = 14)
                             ) d 
                         USING (f_id)
                       )
     
               LEFT JOIN (
                        SELECT fiche_detail.jft_id
                             ,
            fiche_detail.f_id
                             ,
            fiche_detail.ad_id
                             ,
            fiche_detail.ad_value
           
                          FROM fiche_detail
          
                         WHERE (fiche_detail.ad_id = 15)
                       ) e 
                   USING (f_id)
                 )
     
         LEFT JOIN (
                  SELECT fiche_detail.jft_id
                       ,
            fiche_detail.f_id
                       ,
            fiche_detail.ad_id
                       ,
            fiche_detail.ad_value
           
                    FROM fiche_detail
          
                   WHERE (fiche_detail.ad_id = 16)
                 ) f 
             USING (f_id)
           )
     
   LEFT JOIN (
            SELECT fiche_detail.jft_id
                 ,
            fiche_detail.f_id
                 ,
            fiche_detail.ad_id
                 ,
            fiche_detail.ad_value
           
              FROM fiche_detail
          
             WHERE (fiche_detail.ad_id = 17)
           ) g 
       USING (f_id)
     )
     
LEFT JOIN (
      SELECT fiche_detail.jft_id
           ,
            fiche_detail.f_id
           ,
            fiche_detail.ad_id
           ,
            fiche_detail.ad_value
           
        FROM fiche_detail
          
       WHERE (fiche_detail.ad_id = 18)
     ) h 
 USING (f_id)
)
  
WHERE (fiche_def_ref.frd_id = 8);

Index - Schema public

Table: public.widget_dashboard

public.widget_dashboard Structure
F-Key Name Type Description
wd_id serial PRIMARY KEY

PK
wd_code text UNIQUE NOT NULL

Code
wd_name text NOT NULL

Name
wd_description text NOT NULL

Description
wd_parameter integer NOT NULL

presence of there is a parameter

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Function: public.bud_card_ins_upd()

Returns: trigger

Language: PLPGSQL

declare
 sCode text;
begin

sCode:=trim(upper(NEW.bc_code));
sCode:=replace(sCode,' ','_');
sCode:=substr(sCode,1,10);
NEW.bc_code:=sCode;
return NEW;
end;

Function: public.bud_detail_ins_upd()

Returns: trigger

Language: PLPGSQL

declare
mline bud_detail%ROWTYPE;
begin
mline:=NEW;
if mline.po_id = -1 then
   mline.po_id:=NULL;
end if;
return mline;
end;

Function: public.correct_quant_purchase()

Returns: void

Language: PLPGSQL

declare
	r_invalid quant_purchase;
	s_QuickCode text;
	b_j_debit bool;
	r_new record;
	r_jrnx record;
begin

for r_invalid in select * from quant_purchase where qp_valid='A'
loop

select j_qcode into s_QuickCode from vw_poste_qcode where f_id=r_invalid.qp_fiche;
raise notice 'qp_id % Quick code is %',r_invalid.qp_id,s_QuickCode;

select j_debit,j_grpt,j_jrn_def,j_montant into r_jrnx from jrnx where j_id=r_invalid.j_id;
if NOT FOUND then
	raise notice 'error not found jrnx %',r_invalid.j_id;
	update quant_purchase set qp_valid='Y' where qp_id=r_invalid.qp_id;
	continue;
end if;
raise notice 'j_debit % , j_grpt % ,j_jrn_def  % qp_price %',r_jrnx.j_debit,r_jrnx.j_grpt,r_jrnx.j_jrn_def ,r_invalid.qp_price;

select jr_internal,j_id,j_montant into r_new
	from jrnx join jrn on (j_grpt=jr_grpt_id)
	where 
	j_jrn_def=r_jrnx.j_jrn_def
	and j_id not in (select j_id from  quant_purchase)
	and j_qcode=s_QuickCode
	and j_montant=r_jrnx.j_montant
	and j_debit != r_jrnx.j_debit;

if NOT FOUND then
	raise notice 'error not found %', r_invalid.j_id;
	update quant_purchase set qp_valid='Y' where qp_id=r_invalid.qp_id;
	continue;     
end if;
raise notice 'j_id % found amount %',r_new.j_id,r_new.j_montant;

insert into quant_purchase (qp_internal,j_id,qp_fiche,qp_quantite,qp_price,qp_vat,qp_nd_amount,qp_nd_tva_recup,qp_valid,qp_dep_priv,qp_supplier,qp_vat_code)
values (r_new.jr_internal,r_invalid.j_id,r_invalid.qp_fiche,(r_invalid.qp_quantite * (-1)),r_invalid.qp_price * (-1),r_invalid.qp_vat*(-1),r_invalid.qp_nd_amount*(-1),r_invalid.qp_nd_tva_recup*(-1) ,'Y',r_invalid.qp_dep_priv*(-1),r_invalid.qp_supplier,r_invalid.qp_vat_code);

update quant_purchase set qp_valid='Y' where qp_id=r_invalid.qp_id;
end loop;
return;
end;

Function: public.correct_quant_sale()

Returns: void

Language: PLPGSQL

declare
	r_invalid quant_sold;
	s_QuickCode text;
	b_j_debit bool;
	r_new record;
	r_jrnx record;
begin

for r_invalid in select * from quant_sold where qs_valid='A'
loop

select j_qcode into s_QuickCode from vw_poste_qcode where f_id=r_invalid.qs_fiche;
raise notice 'qp_id % Quick code is %',r_invalid.qs_id,s_QuickCode;

select j_debit,j_grpt,j_jrn_def,j_montant into r_jrnx from jrnx where j_id=r_invalid.j_id;
if NOT FOUND then
	update quant_sold set qs_valid='Y' where qs_id=r_invalid.qs_id;
	raise notice 'error not found jrnx %',r_invalid.j_id;
	continue;
end if;
raise notice 'j_debit % , j_grpt % ,j_jrn_def  % qs_price %',r_jrnx.j_debit,r_jrnx.j_grpt,r_jrnx.j_jrn_def ,r_invalid.qs_price;

select jr_internal,j_id,j_montant into r_new
	from jrnx join jrn on (j_grpt=jr_grpt_id)
	where 
	j_jrn_def=r_jrnx.j_jrn_def
	and j_id not in (select j_id from  quant_sold)
	and j_qcode=s_QuickCode
	and j_montant=r_jrnx.j_montant
	and j_debit != r_jrnx.j_debit;

if NOT FOUND then
   update quant_sold set qs_valid='Y' where qs_id=r_invalid.qs_id;
	raise notice 'error not found %', r_invalid.j_id;
	continue;
end if;
raise notice 'j_id % found amount %',r_new.j_id,r_new.j_montant;


 insert into quant_sold (qs_internal,j_id,qs_fiche,qs_quantite,qs_price,qs_vat,qs_valid,qs_client,qs_vat_code)
 values (r_new.jr_internal,r_invalid.j_id,r_invalid.qs_fiche,(r_invalid.qs_quantite * (-1)),r_invalid.qs_price * (-1),r_invalid.qs_vat*(-1),'Y',r_invalid.qs_client,r_invalid.qs_vat_code);
 update quant_sold set qs_valid='Y' where qs_id=r_invalid.qs_id;
end loop;
return;
end;

Function: public.isdate(text, text)

Returns: boolean

Language: PLPGSQL

DECLARE x timestamp;
BEGIN
    x := to_date($1,$2);
    RETURN TRUE;
EXCEPTION WHEN others THEN
    RETURN FALSE;
END;

Function: public.modify_menu_system(n_profile numeric)

Returns: void

Language: PLPGSQL

declare 
r_duplicate profile_menu%ROWTYPE;
str_duplicate text;
n_lowest_id numeric; -- lowest pm_id : update the dependency in profile_menu
n_highest_id numeric; -- highest pm_id insert into profile_menu

begin

for str_duplicate in   
	select me_code 
	from profile_menu 
	where 
	p_id=n_profile and 
	p_type_display <> 'P' and
	pm_id_dep is null
	group by me_code 
	having count(*) > 1 
loop
	raise info 'str_duplicate %',str_duplicate;
	for r_duplicate in select * 
		from profile_menu 
		where 
		p_id=n_profile and
		me_code_dep=str_duplicate
	loop
		raise info 'r_duplicate %',r_duplicate;
		-- get the lowest 
		select a.pm_id into n_lowest_id from profile_menu a join profile_menu b on (a.me_code=b.me_code and a.p_id = b.p_id)
		where
		a.me_code=str_duplicate
		and a.p_id=n_profile
		and a.pm_id < b.pm_id;
		raise info 'lowest is %',n_lowest_id;
		-- get the highest
		select a.pm_id into n_highest_id from profile_menu a join profile_menu b on (a.me_code=b.me_code and a.p_id = b.p_id)
		where
		a.me_code=str_duplicate
		and a.p_id=n_profile
		and a.pm_id > b.pm_id;
		raise info 'highest is %',n_highest_id;

		-- update the first one
		update profile_menu set pm_id_dep = n_lowest_id where pm_id=r_duplicate.pm_id;
		-- insert a new one
		insert into profile_menu (me_code,
			me_code_dep,
			p_id,
			p_order,
			p_type_display,
			pm_default,
			pm_id_dep)
		values (r_duplicate.me_code,
			r_duplicate.me_code_dep,
			r_duplicate.p_id,
			r_duplicate.p_order,
			r_duplicate.p_type_display,
			r_duplicate.pm_default,
			n_highest_id);
		
	end loop;	

end loop;	
end;

Function: public.replace_menu_code(code_source text, code_destination text)

Returns: void

Language: PLPGSQL

begin
    /*code */

    update bookmark set b_action = replace(b_action,code_source,code_destination) where b_action ~ code_source;
    update menu_ref set me_code =code_destination where me_code = code_source;
    update profile_menu set me_code=code_destination where me_code = code_source;
    update profile_menu set me_code_dep=code_destination where me_code_dep = code_source;
end ;

Function: public.upgrade_repo(p_version integer)

Returns: void

Language: PLPGSQL

declare 
        is_mono integer;
begin
        select count (*) into is_mono from information_schema.tables where table_name='repo_version';
        if is_mono = 1 then
                update repo_version set val=p_version;
        else
                update version set val=p_version;
        end if;
end;

Schema tva_belge

Table: tva_belge.assujetti

tva_belge.assujetti Structure
F-Key Name Type Description
a_id serial PRIMARY KEY
start_date date NOT NULL
end_date date NOT NULL
xml_oid oid
periodicity character(1) NOT NULL
tva_name text
num_tva text
adress text
country text
date_decl date DEFAULT now()
periode_dec integer
exercice text

Tables referencing this one via Foreign Key Constraints:

Index - Schema tva_belge

Table: tva_belge.assujetti_chld

tva_belge.assujetti_chld Structure
F-Key Name Type Description
ac_id serial PRIMARY KEY
tva_belge.assujetti.a_id a_id bigint
ac_tvanum text NOT NULL
ac_amount numeric(20,4) NOT NULL
ac_vat numeric(20,4) NOT NULL
ac_qcode text NOT NULL
ac_name text NOT NULL
ac_periode text
exercice text

Index - Schema tva_belge

Table: tva_belge.declarant

tva_belge.declarant Structure
F-Key Name Type Description
d_id bigserial PRIMARY KEY
d_name text NOT NULL
d_street text NOT NULL
d_postcode text NOT NULL
d_city text NOT NULL
d_email text NOT NULL
d_phone text NOT NULL
d_vat_number text NOT NULL
d_countrycode character(2) NOT NULL

Index - Schema tva_belge

Table: tva_belge.declaration_amount

tva_belge.declaration_amount Structure
F-Key Name Type Description
da_id serial PRIMARY KEY
d00 numeric(20,4) NOT NULL DEFAULT 0.0
d01 numeric(20,4) NOT NULL DEFAULT 0.0
d02 numeric(20,4) NOT NULL DEFAULT 0.0
d03 numeric(20,4) NOT NULL DEFAULT 0.0
d44 numeric(20,4) NOT NULL DEFAULT 0.0
d45 numeric(20,4) NOT NULL DEFAULT 0.0
d46 numeric(20,4) NOT NULL DEFAULT 0.0
d47 numeric(20,4) NOT NULL DEFAULT 0.0
d48 numeric(20,4) NOT NULL DEFAULT 0.0
d49 numeric(20,4) NOT NULL DEFAULT 0.0
d81 numeric(20,4) NOT NULL DEFAULT 0.0
d82 numeric(20,4) NOT NULL DEFAULT 0.0
d83 numeric(20,4) NOT NULL DEFAULT 0.0
d84 numeric(20,4) NOT NULL DEFAULT 0.0
d85 numeric(20,4) NOT NULL DEFAULT 0.0
d86 numeric(20,4) NOT NULL DEFAULT 0.0
d87 numeric(20,4) NOT NULL DEFAULT 0.0
d88 numeric(20,4) NOT NULL DEFAULT 0.0
d54 numeric(20,4) NOT NULL DEFAULT 0.0
d55 numeric(20,4) NOT NULL DEFAULT 0.0
d56 numeric(20,4) NOT NULL DEFAULT 0.0
d57 numeric(20,4) NOT NULL DEFAULT 0.0
d61 numeric(20,4) NOT NULL DEFAULT 0.0
d63 numeric(20,4) NOT NULL DEFAULT 0.0
dxx numeric(20,4) NOT NULL DEFAULT 0.0
d59 numeric(20,4) NOT NULL DEFAULT 0.0
d62 numeric(20,4) NOT NULL DEFAULT 0.0
d64 numeric(20,4) NOT NULL DEFAULT 0.0
dyy numeric(20,4) NOT NULL DEFAULT 0.0
d71 numeric(20,4) NOT NULL DEFAULT 0.0
d72 numeric(20,4) NOT NULL DEFAULT 0.0
d91 numeric(20,4) NOT NULL DEFAULT 0.0
start_date date NOT NULL
end_date date NOT NULL
xml_oid oid
periodicity character(1) NOT NULL
tva_name text
num_tva text
adress text
country text
date_decl date DEFAULT now()
periode_dec integer
exercice text
form_id integer
ask_restitution smallint
client_listing smallint

Index - Schema tva_belge

Table: tva_belge.form

tva_belge.form Structure
F-Key Name Type Description
id serial PRIMARY KEY
f_name text NOT NULL
f_language character(2)

Index - Schema tva_belge

Table: tva_belge.form_detail

tva_belge.form_detail Structure
F-Key Name Type Description
id serial PRIMARY KEY
fd_code text NOT NULL
fd_label text NOT NULL
fd_type text NOT NULL
fd_tva_code text
form_id integer NOT NULL
fd_order integer NOT NULL

Index - Schema tva_belge

Table: tva_belge.intracomm

tva_belge.intracomm Structure
F-Key Name Type Description
i_id serial PRIMARY KEY
start_date date NOT NULL
end_date date NOT NULL
xml_oid oid
periodicity character(1) NOT NULL
tva_name text
num_tva text
adress text
country text
date_decl date DEFAULT now()
periode_dec integer
exercice text

Tables referencing this one via Foreign Key Constraints:

Index - Schema tva_belge

Table: tva_belge.intracomm_chld

tva_belge.intracomm_chld Structure
F-Key Name Type Description
ic_id serial PRIMARY KEY
tva_belge.intracomm.i_id i_id bigint
ic_tvanum text NOT NULL
ic_amount numeric(20,4) NOT NULL
ic_code character varying(1) NOT NULL
ic_periode character varying(6) NOT NULL
ic_qcode text NOT NULL
ic_name text NOT NULL

Index - Schema tva_belge

Table: tva_belge.parameter

tva_belge.parameter Structure
F-Key Name Type Description
pcode text PRIMARY KEY

Index - Schema tva_belge

Table: tva_belge.parameter_chld

Child table for parameters (TVA Plugin)

tva_belge.parameter_chld Structure
F-Key Name Type Description
pi_id bigserial PRIMARY KEY

PK
pcode text

FK to parameter
public.tva_rate.tva_id tva_id bigint

FK to public.tva_rate
pcm_val account_type

FK to tmp_pcmn

Index - Schema tva_belge

Table: tva_belge.representative

tva_belge.representative Structure
F-Key Name Type Description
rp_id bigserial PRIMARY KEY
rp_issued text
rp_type text
rp_name text
rp_street text
rp_postcode text
rp_city text
rp_email text
rp_phone text
rp_countrycode character(2) DEFAULT NULL::bpchar

Index - Schema tva_belge

Table: tva_belge.version

tva_belge.version Structure
F-Key Name Type Description
id serial PRIMARY KEY
vdate timestamp without time zone DEFAULT now()
vdesc text

Index - Schema tva_belge

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict