Форма 8 сделки по застройщикам считали

select
*
from
FORM_8_BRG_CIS b
where
B.BRG_TYPE in (1,2)
and b.BRG_DATE between TO_DATE ('&start#', 'dd.mm.yyyy') and TO_DATE ('&end#', 'dd.mm.yyyy')

КУВД без принятых решений но с правами

select
(select ap.NO from app_applications ap where ap.id=ape.app_id) "КУВД"
,ape.*
from
app_re ape
where
APE.r_type is null
and APE.APP_ID in (
select APR.APP_ID
from app_rt apr
where APR.APP_ID = APE.APP_ID
)

Сделки с-х земель с ценами ипотек и аренд

select
(select max(O.ADR_PRN_DESC) from re_objects o 
 where o.id in (select r.re_id from rt_rights r where r.re_id = o.id and R.BRG_ID = b.id) 
)"Адрес"
,(select max(case
     when o.TSQ_UNITS=10 then O.TOTAL_SQ
     when o.TSQ_UNITS=20 then O.TOTAL_SQ*10000
   end) from re_objects o 
 where o.id in (select r.re_id from rt_rights r where r.re_id = o.id and R.BRG_ID = b.id) 
)"Площадь"
,decode(b.TYPE_CODE,b.TYPE_CODE,(select BV.NAME from BRG_BARGAINS_VOC bv where bv.code=b.type_code)) "ТипСделки"
,B.REG_NO "РегНоСд"
,(select max(r.RE_PRICE) from rt_rights r where r.brg_id= b.id and r.type_code = 204)"ЦенаУчСд"
,(select max(r.PRICE_DESC) from rt_rights r where r.brg_id= b.id and r.type_code = 204)"ЦенаУчСдОп"
,(select max(r.RE_PRICE) from rt_rights r where r.brg_id= b.id and r.type_code = 202)"ЦенаИп"
,(select max(r.PRICE_DESC) from rt_rights r where r.brg_id= b.id and r.type_code = 202)"ЦенаИпОп"
,(select wm_concat( (case 
                            when r.RS_DESC is null then ' '
                            else substr(to_char(r.RS_DESC),1,200)
                            end) 
                  ||',') from rt_rights r where r.brg_id= b.id and ROWNUM <  20 )"ПредметСд"
from
brg_bargains b
where
b.id in
(
select R.BRG_ID 
from
re_objects o
,rt_rights r
,re_purposes rps

where
r.re_id = o.id
and O.R_TYPE = 1
and rps.re_id = o.id
and RPS.TYPE_CODE like '11____'

) 

and b.S_DATE between TO_DATE ('&start#', 'dd.mm.yyyy') and TO_DATE ('&end#', 'dd.mm.yyyy')

Земли федералов

select
o.name "Объект"
,O.ADR_PRN_DESC "Адрес"
,O.CON_DESC "Описание"
,(case
     when o.TSQ_UNITS=10 then O.TOTAL_SQ
     when o.TSQ_UNITS=20 then O.TOTAL_SQ*10000
   end) "Площадь"
,decode(r.TYPE_CODE,r.TYPE_CODE,(select RV.NAME from rt_rights_voc rv where rv.code=r.type_code)) "ТипПрава"
,R.REG_NO "РегНо"
,r.s_date "ДатаРег"
,r.e_date "ДатаПрек"
,RT.DOC_DESC(r.id) "Документы"
,EN.NAME "Субъект"
from
ent_entities en
,rt_ent rte
,rt_rights r
,re_objects o
where
rte.ent_id=en.id
and rte.rt_id = r.id
and r.re_id=o.id

and O.R_TYPE = 1

and(lower(EN.NAME) like 'российская федерация'
    or lower(EN.NAME) like '%фгу%'
    or lower(EN.NAME) like '%фбу%'
    or lower(EN.NAME) like '%федерал%государ%уч%'
    or lower(EN.NAME) like '%федерал%бюдже%уч%'
    or lower(EN.NAME) like '%фгуп%'
    or lower(EN.NAME) like '%федерал%предпр%'
    )

Отказы и приостановления

select
o.name "Объект"
,O.ADR_DESC "Адрес"
,O.ADR_PRN_DESC "Адрес2"
,O.CON_DESC "Описание"
,(case
     when o.TSQ_UNITS=10 then O.TOTAL_SQ
     when o.TSQ_UNITS=20 then O.TOTAL_SQ*10000
   end) "Площадь"
,decode(apo.R_TYPE,apo.R_TYPE,(select crc.RV_ABBREVIATION from cg_ref_codes crc where crc.RV_DOMAIN = 'APP_РЕШЕНИЯ_О_РЕГИСТРАЦИИ(СПИСОК)' and crc.RV_LOW_VALUE = apo.R_TYPE)) "Решение"
--,APP.ENT_DESC(ap.id)"Субъект" 
,EN.NAME "Субъект"
,EN_RP.NAME "СубъектПР"
from
app_applications ap
,app_re apo
,re_objects o
,app_ent ape
,ent_entities en
,ENT_REPRESENTATIVES erp
,ent_entities en_rp

where
ap.id=apo.app_id
and apo.re_id = o.id
and O.R_TYPE = 1
and APO.R_TYPE in (3, 6, 8, 9)
and ape.app_id=ap.id
and ape.ent_id=en_rp.id
and ERP.ENT_ID = en.id
and ERP.REP_ID = en_rp.id
         
and (lower(EN.NAME) like 'российская федерация'
            or lower(EN.NAME) like '%фгу%'
            or lower(EN.NAME) like '%фбу%'
            or lower(EN.NAME) like '%федерал%государ%уч%'
            or lower(EN.NAME) like '%федерал%бюдже%уч%'
            or lower(EN.NAME) like '%фгуп%'
            or lower(EN.NAME) like '%федерал%предпр%'
    )