1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
|
-- Function: get_journal_next_sequence("varchar")
-- DROP FUNCTION get_journal_next_sequence("int4");
CREATE OR REPLACE FUNCTION verif_ecriture(int4)
RETURNS integer AS
$BODY$
-- -------------------------------------------------------------------------------------------
-- nom verif_ecriture
-- type procedure PLSQL
-- version 1.0
-- variables argument si journal id est nul alors toules journaux
-- exemple
-- description Vérification des ecritures
-- test select * from verif_ecriture(0);
-- RAISE NOTICE '*%*',v_var;
-- -------------------------------------------------------------------------------------------
DECLARE
v_journal_id ALIAS FOR $1;
v_sum_verif numeric (10,2);
v_ecr_code varchar;
cur_journal RECORD;
cur_ecr RECORD;
BEGIN
FOR cur_journal IN SELECT journal_id FROM journal where statut = true LOOP
IF (coalesce(v_journal_id, 0) = 0 OR coalesce(v_journal_id, 0) = cur_journal.journal_id ) THEN
select sum(coalesce(ecr_deb_mnt, 0)) - sum(coalesce(ecr_cre_mnt, 0)) into v_sum_verif
from ecriture
where journal_id = cur_journal.journal_id
and statut = true;
IF (v_sum_verif != 0 ) THEN
return -210;
END IF;
END IF;
END LOOP;
return 10;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
ALTER FUNCTION get_sequence("varchar") OWNER TO postgres; |
Partager