Запросы для статистики

1 - Субъекты "Образования"
2 - Документ основания: "Договор участия в долевом строительстве"
3 - Назначение Жилое

select
en.name "Субъект"
,(case
     when o.TSQ_UNITS=10 then O.TOTAL_SQ
     when o.TSQ_UNITS=20 then O.TOTAL_SQ*10000
   end) "Площадь"
,O.ADR_DESC "Адрес"
,R.S_DATE "ДатаРег"
,R.E_DATE "ДатаПрек"
    
from
  rt_rights r
  ,ent_entities en
  ,rt_ent rte
  ,re_objects o
  ,re_purposes rps
where
  en.id = RTE.ENT_ID
  and r.id = RTE.RT_ID
  and r.re_id = o.id
  and rps.re_id = o.id
  
  and EN.R_TYPE = 'О'
  and RPS.TYPE_CODE like '21%'
  
  and r.id in (
    select DR.RT_S_ID from
        doc_rel dr
        ,doc_foundations d
    where
        DR.DOC_ID = d.ID
        and DR.RT_S_ID = R.ID
        and d.r_type=70
        --and d.r_type=562
  )
   and R.S_DATE between TO_DATE ('&start#', 'dd.mm.yyyy') and TO_DATE ('&end#', 'dd.mm.yyyy')

1 - Субъекты "Образования"
2 - Документ основания: "Акт ввода в эксплуатацию"
3 - Назначение Жилое

select
en.name "Субъект"
,(case
     when o.TSQ_UNITS=10 then O.TOTAL_SQ
     when o.TSQ_UNITS=20 then O.TOTAL_SQ*10000
   end) "Площадь"
,O.ADR_DESC "Адрес"
,R.S_DATE "ДатаРег"
,R.E_DATE "ДатаПрек"
    
from
  rt_rights r
  ,ent_entities en
  ,rt_ent rte
  ,re_objects o
  ,re_purposes rps
where
  en.id = RTE.ENT_ID
  and r.id = RTE.RT_ID
  and r.re_id = o.id
  and rps.re_id = o.id
  
  and EN.R_TYPE = 'О'
  and RPS.TYPE_CODE like '21%'
  
  and r.id in (
    select DR.RT_S_ID from
        doc_rel dr
        ,doc_foundations d
    where
        DR.DOC_ID = d.ID
        and DR.RT_S_ID = R.ID
        --and d.r_type=70
        and d.r_type=562
  )
  and R.S_DATE between TO_DATE ('&start#', 'dd.mm.yyyy') and TO_DATE ('&end#', 'dd.mm.yyyy')

права физлиц
1 - Земельные участки
2 - Земли населенных пунктов
3 - Назначение: Для жилищного строительства, для идивидуального жилищного строительства, для ведения личного подсобного хоз-ва

select
O.ADR_DESC "Адрес"
,(select to_char(rpv.NAME) from re_purpose_voc rpv where rpv.CODE=rps.TYPE_CODE) "НазначениеОбъекта"
,R.S_DATE "ДатаРег"
,R.E_DATE "ДатаПрек"
    
from
  rt_rights r
  
  ,re_objects o
  ,re_purposes rps
where
  r.re_id = o.id
  and rps.re_id = o.id
  
  and r.id in ( 
      select rte.rt_id from 
          ent_entities en  ,rt_ent rte
      where  
          en.id = RTE.ENT_ID
          and r.id = RTE.RT_ID
          and EN.R_TYPE = 'Ф'
  )
  and RPS.TYPE_CODE in('190100','190110','191100')
  and r.id in (
    select min(rr.id) from RT_RIGHTS rr where  o.id = rr.re_id
  )
  
  and R.S_DATE between TO_DATE ('&start#', 'dd.mm.yyyy') and TO_DATE ('&end#', 'dd.mm.yyyy')

права на объекты жилого назначения
1 - док.основания св-во о наследстве

select
  O.CAD_NUM||O.OBJ_NUM "КадУслНо"
  ,o.name "Объект"
  ,O.ADR_DESC "Адрес"
  ,r.reg_no "РегНо"
  ,r.s_date "ДатаРег"
  ,r.e_date "ДатаПр"
  ,decode(r.TYPE_CODE,r.TYPE_CODE,(select RV.NAME from rt_rights_voc rv where rv.code=r.type_code)) "ВидПрава"
  ,RT.ENT_DESC (r.id) "Субъект"
from
  re_objects o
  ,rt_rights r
  ,re_purposes rps
where
  o.id = r.re_id
  and R.R_GROUP = 'П'
  and o.id=RPS.RE_ID
  
  and RPS.TYPE_CODE like '21%'
  
  and r.id in (
  select min(rr.id) from RT_RIGHTS rr where  o.id = rr.re_id
  )
  
   and r.id in (
    select DR.RT_S_ID from
        doc_rel dr
        ,doc_foundations d
    where
        DR.DOC_ID = d.ID
        and DR.RT_S_ID = R.ID
        and d.r_type in (12, 531, 532)
  )

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

Жилые дома белоярского сельсовета

select
  O.CAD_NUM||O.OBJ_NUM "КадУслНо"
  ,o.name "Объект"
  ,O.ADR_DESC "Адрес"
  ,(case
     when o.TSQ_UNITS=10 then O.TOTAL_SQ
     when o.TSQ_UNITS=20 then O.TOTAL_SQ*10000
   end) "Площадь" 
  ,r.reg_no "РегНо"
  ,r.s_date "ДатаРег"
  ,decode(r.TYPE_CODE,r.TYPE_CODE,(select RV.NAME from rt_rights_voc rv where rv.code=r.type_code)) "ВидПрава"

from
  re_objects o
  ,rt_rights r
  ,re_purposes rps
where
  o.id = r.re_id
  and R.R_GROUP = 'П'
  and o.id=RPS.RE_ID
  
  and RPS.TYPE_CODE like '21%'
  
  and r.id in (
  select min(rr.id) from RT_RIGHTS rr where  o.id = rr.re_id
  )
  
  and
  ( lower(O.ADR_DESC) like '%кайбалы%'
    or  lower(O.ADR_DESC) like '%белый%яр%' 
  ) 
  
  and R.S_DATE between TO_DATE ('&start#', 'dd.mm.yyyy') and TO_DATE ('&end#', 'dd.mm.yyyy')

Бесхозяйные

select
R.S_DATE "ДатаРег"
,O.NAME "Объект"
,R.E_DATE "ДатаПр"
,RT.DOC_DESC(r.id) "ДокОсн"
from
re_objects o
,rt_rights r
where
O.ID=r.re_id
and R.TYPE_CODE=109

Правка субъектов

select
en.id "ID"
,RP.DEPT_NAME(EN.DEPT_ID) "Филиал"
,en.r_type "Тип"
,en.name "Субъект"
,EN.CITIZEN "Страна"
,EA.ADR_DESC "АдрОпис"
,adr.create_descr(ea.adr_id) "АдрСпр" 
from
 ent_entities en
,ent_address ea

where
EN.ID=ea.id
and en.id in (select rte.ent_id from rt_ent rte)
and lower(EA.ADR_DESC) not like '%республика%хакасия%'
--and lower(adr.create_descr(ea.adr_id)) not like '%республика%хакасия%'
and lower(EA.ADR_DESC) like lower('%!!!НЕКОРРЕКТНЫЙ АДРЕС!!!%')

select
en.id "ID"
,RP.DEPT_NAME(EN.DEPT_ID) "Филиал"
,en.r_type "Тип"
,en.name "Субъект"
,EN.CITIZEN "Страна"
,EA.ADR_DESC "АдрОпис"
,adr.create_descr(ea.adr_id) "АдрСпр"
,(select AA.R_LEVEL from adr_address_voc aa where aa.id=ea.adr_id ) "АдрУр"
,length(adr.create_descr(ea.adr_id)) "АдрДл"
 
from
 ent_entities en
,ent_address ea

where
EN.ID=ea.id
and en.id in (select rte.ent_id from rt_ent rte)
and lower(EA.ADR_DESC) not like '%республика%хакасия%'
and lower(adr.create_descr(ea.adr_id)) not like '%республика%хакасия%'
--and lower(EA.ADR_DESC) like lower('%!!!НЕКОРРЕКТНЫЙ АДРЕС!!!%')

order by length(adr.create_descr(ea.adr_id))

select
--выборка субъектов с необщей верхней частью адреса aav.R_LEVEL < 40
en.id "ID"
,RP.DEPT_NAME(EN.DEPT_ID) "Филиал"
,en.r_type "Тип"
,en.name "Субъект"
,EN.CITIZEN "Страна"
,EA.ADR_DESC "АдрОпис"
,ea.adr_id
,adr.create_descr(ea.adr_id) "АдрСпр"
from
 ent_entities en
,ent_address ea
 
where
EN.ID=ea.id
and en.id in (select rte.ent_id from rt_ent rte)
and (
    SELECT 
    sum(case 
        when aav.R_LEVEL < 40 and aav.DEPT_ID is not null then 
          (case 
            when (
                    select count(eam.old_ID) 
                    from 
                        exp_voc_meanings eam
                        ,adr_address_voc a2
                    where 
                        eam.OLD_ID=aav.ID 
                        and eam.tab_name='ADR_ADDRESS_VOC'
                        and eam.NEW_ID=a2.id
                        and a2.DEPT_ID is null
                 ) = 0 then  aav.DEPT_ID
            else null
          end)
        else null
    end)
    FROM adr_address_voc aav

    START WITH aav.ID = ea.adr_id
    CONNECT BY aav.ID = PRIOR aav.par_id
) >0

--and lower(EA.ADR_DESC) not like '%республика%хакасия%'
--and lower(adr.create_descr(ea.adr_id)) not like '%республика%хакасия%'
--and lower(EA.ADR_DESC) like lower('%!!!НЕКОРРЕКТНЫЙ АДРЕС!!!%'