procedure CALC_SUMM_ON_VOLUME (
    LIC_SCH type of LIC_SCH_DOMAIN,
    SPR_SUB_USLUGI_ID integer,
    START_DATE timestamp,
    STOP_DATE timestamp)
returns (
    VOLUME numeric(15,5),
    TARIF numeric(15,5),
    PROCENT numeric(15,5),
    BEGIN_DATE timestamp,
    END_DATE timestamp)
as
declare variable EXVOL integer;
declare variable VOL numeric(18,5);
declare variable TS1 timestamp;
declare variable TS2 timestamp;
declare DATAPOKAZ cursor for (
    select DPS.VOLUME, DPS.PRED_DATE, DPS.DATA
    from DATA_POKAZ_SCH DPS
    where (DPS.LIC_SCH = :LIC_SCH) and
          (DPS.SPR_SUB_USLUGI_ID = :SPR_SUB_USLUGI_ID) and
          (DPS.DATA >= :START_DATE) and
          (DPS.DATA <= :STOP_DATE));
begin
  /* Procedure Text */
  ExVol=0;
  open datapokaz;
  while (1=1) do
  begin
        tarif=0;
        fetch datapokaz into :vol, :ts1, :ts2;
        if (:vol is null) then vol=0;
        if (:ts1 is null) then ts1=:start_date;
        if (:ts2 is null) then ts2=:stop_date;
             for select tarif, procent, begin_date, end_date from calc_summ_on_sub_uslugi (:lic_sch,:spr_sub_uslugi_id,:ts1,:ts2)
             into :tarif, :procent, :begin_date, :end_date
              do begin
                  if ((cast(:ts2 as date)-cast(:ts1 as date))>0) then
                  volume=:vol*(cast(:end_date as date) - cast(:begin_date as  date)+1)/(cast(:ts2 as date) - cast(:ts1 as date)+1);
                  else
                  volume=:vol;
                  if (:tarif is null) then tarif=0;
                  if (:procent is null) then procent=0;
                  exvol=1;
                  suspend;
                  end
       if  ((row_count=0) or (:ts2<:start_date))   then leave;
  end
  close datapokaz;  
end

procedure CALC_SUMM_ON_VOLUME (
    LIC_SCH type of LIC_SCH_DOMAIN,
    SPR_SUB_USLUGI_ID integer,
    START_DATE timestamp,
    STOP_DATE timestamp)
returns (
    VOLUME numeric(15,5),
    TARIF numeric(15,5),
    PROCENT numeric(15,5),
    BEGIN_DATE timestamp,
    END_DATE timestamp)
as
declare variable EXVOL integer;
declare variable VOL numeric(18,5);
declare variable TS1 timestamp;
declare variable TS2 timestamp;
begin
  /* Procedure Text */
  ExVol=0;

  for select dps.volume, dps.pred_date, dps.data from data_pokaz_sch dps where (dps.lic_sch=:lic_sch)
  and (dps.spr_sub_uslugi_id=:spr_sub_uslugi_id) and (dps.data>=:start_date) and (dps.data<=:stop_date)
  into :vol, :ts1, :ts2
  do begin
        tarif=0;
             for select tarif, procent, begin_date, end_date from calc_summ_on_sub_uslugi (:lic_sch,:spr_sub_uslugi_id,:ts1,:ts2)
             into :tarif, :procent, :begin_date, :end_date
              do begin
                  if ((cast(:ts2 as date)-cast(:ts1 as date))>0) then
                  volume=:vol*(cast(:end_date as date) - cast(:begin_date as  date)+1)/(cast(:ts2 as date) - cast(:ts1 as date)+1);
                  else
                  volume=:vol;
                  if (:tarif is null) then tarif=0;
                  if (:procent is null) then procent=0;
                  exvol=1;
                  suspend;
                 end 
     end
     if (:exvol=0) then
     for select tarif, procent, begin_date, end_date from calc_summ_on_sub_uslugi (:lic_sch,:spr_sub_uslugi_id,:stop_date,:stop_date)
             into :tarif, :procent, :begin_date, :end_date
             do begin
                  if (:tarif is null) then tarif=0;
                  if (:procent is null) then procent=0;
                  volume=0;
                  suspend;
                end
end

SET TERM !!;

CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES 
RETURNS(
  RNAME CHAR(31)
)AS
  DECLARE C CURSOR FOR (SELECT RDB$RELATION_NAME FROM RDB$RELATIONS);
BEGIN
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :RNAME;
    IF(ROW_COUNT = 0)THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
END!!

SET TERM ;!!