select l.sum_isxost, b.*, s.FIO,iif(s.priz=1,'оплата по счетчику','оплата по тарифу') as VID_RASH,
iif(coalesce(l.status_pov,0)!=0,'на поверке','') as POV_abon,
iif(s.kod_geu=1002 or s.kod_geu=2001,'частный сектор','многоэтажка') as MNOGOET,
b.period_opl,iif(s.kod_gaz=1,'пр.','сж.') as VID_GAZA,
iif(coalesce(b.status_sud,0)=1,'оплата по суду','') as N_OPL_SUD,
(select k.fio_kontr from sp_kontr k where k.kod_kontr=s.kmaster) as N_UTH,
(select t.naim from spr_tu_opl t where t.id=b.tip_ustr) as N_TU,
iif(b.status_kas_ap=1,'да','нет') as OPL_KAS_AP
from bopl b
inner join spr_abon s on (s.nom_abon = b.nom_abon)
inner join licev l on (l.nom_abon = b.nom_abon)
inner join mer m on (m.nom_abon = b.nom_abon)
where b.nom_abon=s.nom_abon
and b.nom_abon=l.nom_abon
and coalesce(s.stat_arhiv,0)=0
and b.auth_metod is null
and m.sum_peni > 0
and m.sum_peni < 1000

execute block (date_first date = :date_first, date_last date = :date_last )
returns(peni varchar(20),nom_abon integer), FIO varchar(100))
as                             
declare variable Fine varchar(20);
declare variable Number integer;
declare variable FName varchar(200);

begin
   for select (select m.sum_peni
               from mer m
               where b.nom_abon = m.nom_abon and m.sum_peni > 0 and m.sum_peni < 1000 and m.sum_peni is not null
               order by m.date_mer desc rows 1)as pen,
   b.nom_abon, s.FIO
   from bopl b
   inner join spr_abon s on (s.nom_abon = b.nom_abon)
   where b.nom_abon=s.nom_abon
   and coalesce(s.stat_arhiv,0)=0
   and b.auth_metod is null
   and b.date_opl between :date_first and :date_last
   into :Fine, :Number, :FName
   do begin
   peni=''; nom_abon=null;FIO='';
   if(Fine is not null)then
   begin
       peni = :Fine;
       nom_abon = :Number;
       FIO = :FName;
   end
   suspend;

   end

end