execute block  returns(p1 VARCHAR(40)
,p2 varchar(1280)
,p3 varchar(1280)
,p4 varchar(1280)
,p5 varchar(1280)
, p6 varchar(1280)
,p7 varchar(30)
, p8 varchar(10240)
,p9 varchar(256)
,p10 varchar(1280)
, p11 varchar(1280)
, p12 varchar(1280)
, p13 varchar(1280)
, p14 varchar(1280)) 
as 
declare StanNaDzien DATE='01 01 2023';   /* 'MM DD RRRR' */
declare id int;
declare idAdr int;
declare kdk int;
declare tjr int;
declare idJR int;
declare a varchar(128);
begin 
for with dla as 
( SELECT distinct p.id_pdm,p.nazwa  
   FROM instytucje i 
   join podmioty p on p.typ='P' and p.id_os=i.id  
  where i.status<>2 and  i.sti=3 and (i.dtu is null or i.dtu<:StanNaDzien) and (i.status in (0,1)  or :StanNaDzien< i.dtw))
 SELECT distinct j.id,j.tjr,j.TERYT||'.'||LPAD(j.id_obr,4,'0')||case when j.TJR=1 then '.G' when j.tjr=2 then 'B' else 'L' end ||j.IJR, LIST(dla.nazwa || ' ' ||u.ud,'; ') as wl
    FROM  dla
    join udzialy u on u.id_podm=dla.id_pdm  and u.sti<>2 and (u.dtu is null or u.dtu<:StanNaDzien) and (u.sti in (0,1)  or :StanNaDzien< u.dtw)
    join jedn_rej j on j.ID=u.id_jedn and j.sti<>2 and (j.dtu is null or j.dtu<:StanNaDzien) and (j.sti in (0,1)  or :StanNaDzien< j.dtw)
    group by 1,2,3
 	order by 3    
into idJR,tjr,p6, p9 do begin
        p1=null;
        p2=null;
        p3=null;
        p4=null;                        
        p5=null;
        IF (TJR=1) THEN  p14=null; ELSE BEGIN P14=P6; P6=NULL; END        
        p7=null;
        p8=null;
        p10=null;
        p11=null;
        p12=null;
        p13=null;
       select  list(iif((p.typ in ('P','I')),p.nazwa,''),'; '),list(distinct rd.nazwa,'; ') 
         from udzialy u
         join podmioty p on p.id_pdm=u.id_podm       
         join rodz_wlada rd on rd.ID=u.rwd2     
         where  u.id_jedn=:idJR  and u.rodzaj=1 and u.sti<>2 and (u.dtu is null or u.dtu<:StanNaDzien) and (u.sti in (0,1)  or :StanNaDzien< u.dtw)
      into p10,p11;
     if (tjr=1) then begin
       for select  d.id, d.teryt||'.' || LPAD(d.idobr,4,'0') || trim(iif(POSITION('.',d.IDD)=0,'   .','.AR_')|| d.idd), cast(cast(d.pew/10000 as  numeric(10,4)) as varchar(10))
          from dzialka d 
          where d.rjdr=:idJR and d.status<>2 and (d.dtu is null or d.dtu<:StanNaDzien) and (d.status in (0,1)  or :StanNaDzien< d.dtw)
       into id, p1, p7 do begin 
          p5='';
          for select ad.naz
              from adresy_dzialki a
              join adresy ad on ad.id=a.idadr
          where a.IDDZ=:id and  a.status<>2 and (a.dtu is null or a.dtu<:StanNaDzien) and (a.status in (0,1)  or :StanNaDzien< a.dtw)  
          into a do if (p5='') then p5=a; else p5=p5||'; '|| a;
          p2='';
          p4='';        
          p3='';
          for select dk.kdk, dk.syg_pelna 
              from dokumenty_dzialki_rpwl rpwl
              join dokumenty dk on dk.id=rpwl.iddok
          where rpwl.IDDZ=:id and  rpwl.status<>2 and (rpwl.dtu is null or rpwl.dtu<:StanNaDzien) and (rpwl.status in (0,1)  or :StanNaDzien< rpwl.dtw)  
          into kdk,a do BEGIN 
              if(kdk=5) 
                then  if (p3='') then p3=a; else p3=p3||'; '|| a;        
                else if (p2='') then p2=a; else p2=p2||'; '|| a;        
          end
          select list(dk.syg_pelna,'; ') 
              from dokumenty_dzialki_rpwd rpwl
              join dokumenty dk on dk.id=rpwl.iddok
          where rpwl.IDDZ=:id and  rpwl.status<>2 and (rpwl.dtu is null or rpwl.dtu<:StanNaDzien) and (rpwl.status in (0,1)  or :StanNaDzien< rpwl.dtw)  
          into p4;
          select list( coalesce(RPWL.OFU,'')||coalesce(RPWL.OZU,'')||coalesce(RPWL.OZK,'')||' ' || cast(rpwl.pew/10000 as numeric(10,4)),'; ')
              from uzytki rpwl
          where rpwl.rdze=:id and  rpwl.status<>2 and (rpwl.dtu is null or rpwl.dtu<:StanNaDzien) and (rpwl.status in (0,1)  or :StanNaDzien< rpwl.dtw)  
          into p8;        
          SUSPEND;        		  
        end
     end   
     If (tjr<>3) then begin
        p1=null;
        p2=null;
        p3=null;
        p4=null;                        
        p5=null;
        p7=null;		
        p8=null;
        p10=null;
        p11=null;
        p12=null;
        p13=null;
         for select b.id, b.teryt||'.' || LPAD(b.idobr,4,'0') || trim(iif(POSITION('.',b.IDB)=0,'   .','.AR_')|| Replace(b.idb,';','.')) || '_BUD'
                  , gml.nzs,b.lkon, b.lkonp, b.pew
                  ,(with l  as (select l.id, l.pew from lokale l where l.RBUD=b.id and l.status<>2 and (l.dtu is null or l.dtu<:StanNaDzien) and (l.status in (0,1)  or :StanNaDzien< l.dtw ))
                    SELECT sum(l.pew + coalesce((
                    select sum(pp.pew) from pomprzyn pp where pp.rlok=l.id and pp.status<>2 and (pp.dtu is null or pp.dtu<:StanNaDzien) and (pp.status in (0,1)  or :StanNaDzien< pp.dtw )),0))
                    from L) 
          from budynki b 
          join slownikGML gml on gml.ID=b.rodzkst
          where b.rjdr=:idJR and b.status<>2 and (b.dtu is null or b.dtu<:StanNaDzien) and (b.status in (0,1)  or :StanNaDzien< b.dtw)
          into id, P1,p5,p6,P7, P8,p9  do begin   
          select list(DISTINCT d.teryt||'.' || LPAD(d.idobr,4,'0') || trim(iif(POSITION('.',d.IDD)=0,'   .','.AR_')|| d.idd)  ,'; ')
           from dzialki_budynki db
           join dzialka d on d.id=db.iddz and d.status<>2 and (d.dtu is null or d.dtu<:StanNaDzien) and (d.status in (0,1)  or :StanNaDzien< d.dtw)
           where db.idbud=:id and db.status<>2 and (db.dtu is null or db.dtu<:StanNaDzien) and (db.status in (0,1)  or :StanNaDzien< db.dtw)
          into p10;     
          p11='';
          p12='';        
          if (tjr=2) then begin
            for select dk.kdk, dk.syg_pelna 
                from dokumenty_budynki_rpwl rpwl
                join dokumenty dk on dk.id=rpwl.iddok
            where rpwl.IDbud=:id and  rpwl.status<>2 and (rpwl.dtu is null or rpwl.dtu<:StanNaDzien) and (rpwl.status in (0,1)  or :StanNaDzien< rpwl.dtw)  
            into kdk,a do BEGIN 
                if(kdk=5) 
                  then  if (p12='') then p12=a; else p12=p12||'; '|| a;        
                  else if (p11='') then p11=a; else p11=p11||'; '|| a;        
            end
          end  
          p13='';
          for select ad.naz
              from adresy_budynki a
              join adresy ad on ad.id=a.idadr
          where a.IDbud=:id and  a.status<>2 and (a.dtu is null or a.dtu<:StanNaDzien) and (a.status in (0,1)  or :StanNaDzien< a.dtw)  
          into a do if (p13='') then p13=a; else p13=p13||'; '|| a;          
           SUSPEND;          
         END

     END
     If (tjr=3) then begin
        p1=null;
        p2=null;
        p3=null;
        p4=null;                        
        p5=null;
        p7=null;
        p8=null;
        p9=null;
        p10=null;
        p11=null;
        p12=null;
        p13=null;
         for select b.id,b.teryt||'.' || LPAD(b.idobr,4,'0') || trim(iif(POSITION('.',b.IDB)=0,'   .','.AR_')|| Replace(b.idb,';','.')) || '_BUD'|| '.' || l.idl || '_LOK'
                  , l.pew,(select sum(pp.pew) from POMPRZYN pp where pp.rlok=:id and pp.status<>2 and (pp.dtu is null or pp.dtu<:StanNaDzien) and (pp.status in (0,1)  or :StanNaDzien< pp.dtw))
                  ,RADR
          from LOKALE L
          JOIN budynki b ON B.ID=L.RBUD and  b.status<>2 and (b.dtu is null or b.dtu<:StanNaDzien) and (b.status in (0,1)  or :StanNaDzien< b.dtw)
          where L.rjdr=:idJR and L.status<>2 and (L.dtu is null or L.dtu<:StanNaDzien) and (L.status in (0,1)  or :StanNaDzien< L.dtw)
          into id, P1,p8,p9,idAdr   do begin   
          if (tjr=3) then begin
            for select dk.kdk, dk.syg_pelna 
                from dokumenty_LOKALE rpwl
                join dokumenty dk on dk.id=rpwl.iddok
            where rpwl.IDLOK=:id and  rpwl.status<>2 and (rpwl.dtu is null or rpwl.dtu<:StanNaDzien) and (rpwl.status in (0,1)  or :StanNaDzien< rpwl.dtw)  
            into kdk,a do BEGIN 
                if(kdk=5) 
                  then  if (p12='') then p12=a; else p12=p12||'; '|| a;        
                  else if (p11='') then p11=a; else p11=p11||'; '|| a;        
            end
          end  
         select ad.naz from adresy ad where aD.ID=:idAdr into p13;          
         SUSPEND;          
         END
     END     
     
end    
end