Zeoslib Portal

Code: Select all

SET TERM ^ ;

CREATE OR ALTER PROCEDURE GET_PROV_BUD_GLOB (
    mychap char(5),
    myart char(2),
    mypar char(2),
    myaln char(2))
returns (
    tot_bp decimal(15,2),
    tot_bs decimal(15,2),
    tot_br decimal(15,2),
    tot_vir_e decimal(15,2),
    tot_vir_d decimal(15,2))
as
begin
      /* BP */
      select sum(dotation) from budget
      where budget='P' and chap=:myChap and Art=:myArt and
            par=:myPar and aln=:myAln
      into tot_bp;
    
      /* BS */
      select sum(dotation) from budget
      where budget='S' and chap=:myChap and Art=:myArt and
            par=:myPar and aln=:myAln
      into tot_bs;
    
      /* BR */
      select sum(dotation) from budget
      where budget='R' and chap=:myChap and Art=:myArt and
            par=:myPar and aln=:myAln
      into tot_bR;
    
      /* VIR D */
      select sum(MONTANT) from virement
      where Sens='D' and chap=:myChap and Art=:myArt and
            par=:myPar and aln=:myAln
      into tot_vir_D;
    
      /* VIR E */
      select sum(MONTANT) from virement
      where Sens='E' and chap=:myChap and Art=:myArt and
            par=:myPar and aln=:myAln
      into tot_vir_E;
  suspend;
end^

SET TERM ; ^

GRANT SELECT ON BUDGET TO PROCEDURE GET_PROV_BUD_GLOB;

GRANT SELECT ON VIREMENT TO PROCEDURE GET_PROV_BUD_GLOB;

GRANT EXECUTE ON PROCEDURE GET_PROV_BUD_GLOB TO SYSDBA;