Удаление пустых объектов

Удаление права

select 
r.id
,r.re_id
,r.REG_NO
,r.TYPE_CODE
,r.SECTION
,r.R_GROUP
,r.PART
,r.CON_DESC
,r.RS_PERIOD
,r.RS_S_DATE
,r.RS_E_DATE
,r.SERV_OWNER
,r.BRG_ID
,r.RE_PRICE
,r.RT_CLAIM
,r.RT_LEGAL_CLAIM
,r.LEGALITY
,r.S_DATE
,r.S_REG_ID
,r.S_REMARK
,r.E_DATE
,r.E_REG_ID
,r.E_REMARK
,r.STATUS
,r.DEPT_ID
,r.MOVED
,r.INSERTED
,r.INSERTED_BY
,r.UPDATED
,r.UPDATED_BY
,r.CURRENCY
,r.PRICE_DESC
,to_char(r.RS_DESC) "RS_DESC"
,r.PART_MUCH_HOUSE 
from
rt_rights r
where
r.re_id in (select o.id from re_objects o where o.obj_num = '(неизвестно)') 
and R.TYPE_CODE in (205,206,260)
and r.INSERTED < to_date('07.06.2007')

Удаление связи RT_RS

select * 
--delete
from rt_rs q
where 
q.rt_id in
(
 select r.id
 from
 rt_rights r
 where
 r.re_id in (select o.id from re_objects o where o.obj_num = '(неизвестно)') 
 and R.TYPE_CODE in (205,206,260)
 and r.INSERTED < to_date('07.06.2007')
)

Удаление связи APP_RT

select * 
--delete
from APP_RT q
where 
q.rt_id in
(
 select r.id
 from
 rt_rights r
 where
 r.re_id in (select o.id from re_objects o where o.obj_num = '(неизвестно)') 
 and R.TYPE_CODE in (205,206,260)
 and r.INSERTED < to_date('07.06.2007')
)

Удаление связи RT_ENT

select * 
--delete
from RT_ENT q
where 
q.rt_id in
(
 select r.id
 from
 rt_rights r
 where
 r.re_id in (select o.id from re_objects o where o.obj_num = '(неизвестно)') 
 and R.TYPE_CODE in (205,206,260)
 and r.INSERTED < to_date('07.06.2007')
)

Удаление связи DOC_REL

select * 
--delete
from DOC_REL q
where 
--q.rt_S_id in
q.rt_e_id in
(
 select r.id
 from
 rt_rights r
 where
 r.re_id in (select o.id from re_objects o where o.obj_num = '(неизвестно)') 
 and R.TYPE_CODE in (205,206,260)
 and r.INSERTED < to_date('07.06.2007')
)

Удаление Налоговая

delete
select * 
--delete
from NAL_EXPORTS_DET q
where 
q.rt_id in
(
 select r.id
 from
 rt_rights r
 where
 r.re_id in (select o.id from re_objects o where o.obj_num = '(неизвестно)') 
 and R.TYPE_CODE in (205,206,260)
 and r.INSERTED < to_date('07.06.2007')
)

Удаление ПРАВ

delete
from
rt_rights r
where
r.re_id in (select o.id from re_objects o where o.obj_num = '(неизвестно)') 
and R.TYPE_CODE in (205,206,260)
and r.INSERTED < to_date('07.06.2007')

------------------------- проверка на связи

select
o.ID "ID"
,O.CAD_NUM||O.OBJ_NUM "КадУслНо"
,o.name "Name"
,O.ADR_PRN_DESC "ADR"
,(select '('||count(rps.id)||') '||max(rps.ID) from re_purposes rps where rps.re_id = o.id ) "RE_PURPOSES"
,(select '('||count(r.id)||') '||max(r.ID) from rt_rights r where r.re_id = o.id ) "RT_RIGHTS"
,(select '('||count(dr.id)||') '||max(dr.ID) from doc_rel dr where dr.re_s_id = o.id ) "doc_rel_S"
,(select '('||count(dr.id)||') '||max(dr.ID) from doc_rel dr where dr.re_e_id = o.id ) "doc_rel_E"
,(select '('||count(are.id)||') '||max(are.ID) from app_re are where are.re_id = o.id ) "APP_RE"
,(select '('||count(arc.id)||') '||max(arc.ID) from ar_re_candidats arc where arc.re_id = o.id ) "ar_re_candidats"
,(select '('||count(qrc.id)||') '||max(qrc.ID) from qu_re_candidats qrc where qrc.re_id = o.id ) "qu_re_candidats"
,(select '('||count(arv.id)||') '||max(arv.ID) from arch_re_values arv where arv.re_id = o.id ) "arch_re_values"
,(select '('||count(art.id)||') '||max(art.ID) from arch_rt_rights art where art.re_id = o.id ) "arch_rt_rights"
,(select '('||count(cc.id)||') '||max(cc.ID) from cse_cases cc where cc.re_id = o.id ) "CSE_CASES"

from
re_objects o
where
o.id=69554001

удаляем связи

DELETE FROM qu_re_candidats WHERE ID =29062001;

Удаляем связи app_re

select *
--delete
from
 app_re q
where
q.re_id in 
    (
    select
        o.id
    from
        re_objects o
    where
        o.id not in (select r.re_id from rt_rights r )  --Права
        and o.obj_num = '(неизвестно)'
    )
    and Q.INSERTED < to_date('07.06.2007')

Список объектов без прав

select
*
from
re_objects o
where
--o.id not in (select are.re_id from app_re are ) --КУВД
--and o.id not in (select qrc.re_id from qu_re_candidats qrc ) --КВИ
--and o.id not in (select arc.re_id from ar_re_candidats arc ) --КА
o.id not in (select r.re_id from rt_rights r )  --Права
and o.INSERTED < to_date('07.06.2007')

Удаляем связи ar_re_candidats

select *
--delete
from
 ar_re_candidats q
where
q.re_id in
    (
    select
        o.id
    from
        re_objects o
    where
        o.id not in (select r.re_id from rt_rights r )  --Права
        and o.obj_num = '(неизвестно)'
    )
    and
    (
    Q.INSERTED < to_date('07.06.2007')
    or Q.STATE in ('П','У')
    )

Удаляем связи qu_re_candidats

select *
--delete 
from
 qu_re_candidats q
where
q.re_id in 
    (
    select
        o.id
    from
        re_objects o
    where
        o.id not in (select r.re_id from rt_rights r )  --Права
        and o.obj_num = '(неизвестно)'
        and o.INSERTED < to_date('07.06.2007')
    )

Удаляем связи re_purposes

select *
--delete 
from
 re_purposes q
where
q.re_id in 
    (
    select
        o.id
    from
        re_objects o
    where
        o.id not in (select r.re_id from rt_rights r )  --Права
        and o.obj_num = '(неизвестно)'
        and o.INSERTED < to_date('07.06.2007')
    )

Удаляем связи cse_orders

select *
--delete
from
 cse_orders o
where
o.csf_id in 
    (
select id
--delete
from
 cse_folders f
where
f.cse_id in 
    (
select q.id
--delete
from
 cse_cases q
where
q.re_id in 
    (
    select
        o.id
    from
        re_objects o
    where
        o.id not in (select r.re_id from rt_rights r )  --Права
        and o.obj_num = '(неизвестно)'
        and o.INSERTED < to_date('07.06.2007')
    )
    
    )
        
    )

Удаляем связи cse_folders

select *
--delete
from
 cse_folders f
where
f.cse_id in 
    (
select q.id
--delete
from
 cse_cases q
where
q.re_id in 
    (
    select
        o.id
    from
        re_objects o
    where
        o.id not in (select r.re_id from rt_rights r )  --Права
        and o.obj_num = '(неизвестно)'
        and o.INSERTED < to_date('07.06.2007')
    )
    
    )
   and (select count(cd.id) from cse_doc cd where cd.csf_id=f.id)=0  

Удаляем связи cse_cases

select q.*
--delete
,(select count(c.id)from cse_folders c where c.cse_id=q.id) "cse_folders"
from
 cse_cases q
where
q.re_id in 
    (
    select
        o.id
    from
        re_objects o
    where
        o.id not in (select r.re_id from rt_rights r )  --Права
        and o.obj_num = '(неизвестно)'
        and o.INSERTED < to_date('07.06.2007')
    )
and (select count(cd.id) from cse_folders cd where cd.cse_id=q.id)=0 

Удаление документов doc_rel

select *
--delete
from
 doc_rel q
where
q.re_S_id in
--q.re_E_id in
    (
    select
        o.id
    from
        re_objects o
    where
        o.id not in (select r.re_id from rt_rights r )  
        and o.obj_num = '(неизвестно)'
        and O.INSERTED < to_date('07.06.2007')
    )

Удаляем выгрузки объектов в налоговую

--delete
select * 
--delete
from NAL_EXPORTS_DET q
where
q.re_id in
    (
    select
        o.id
    from
        re_objects o
    where
        o.id not in (select r.re_id from rt_rights r )  
        and o.obj_num = '(неизвестно)'
        and O.INSERTED < to_date('11.07.2007')
    )
    --and q.dept_id=6

Удаляем re_objects

select id , o.adr_desc
 
--delete 
--,(select '('||count(arc.id)||') '||max(arc.ID) from ar_re_candidats arc where arc.re_id = o.id ) "ar_re_candidats"
--,(select '('||count(qrc.id)||') '||max(qrc.ID) from qu_re_candidats qrc where qrc.re_id = o.id ) "qu_re_candidats"
--,(select count(dr.id) from doc_rel dr where dr.re_s_id=o.id) "re_s_id" 
--,(select count(dr.id) from doc_rel dr where dr.re_e_id=o.id) "re_e_id" 
--,(select count(dr.id) from cse_cases dr where dr.re_id=o.id) "cse_cases" 
--,(select count(dr.id) from ARCH_RE_OBJECTS dr where dr.ARCH_PAR_ID=o.id) "ARCH_PAR_ID" 
--,(select count(dr.id) from ARCH_RE_OBJECTS dr where dr.RE_INCLUDE_ID=o.id) "arch_RE_INCLUDE_ID" 
--,(select count(dr.id) from RE_OBJECTS dr where dr.RE_INCLUDE_ID=o.id) "RE_INCLUDE_ID" 
 
from
re_objects o
where
--o.id not in (select are.re_id from app_re are ) --КУВД
--and o.id not in (select qrc.re_id from qu_re_candidats qrc ) --КВИ
--and o.id not in (select arc.re_id from ar_re_candidats arc ) --КА
--AND (select count(dr.id) from cse_cases dr where dr.re_id=o.id)>0
--and o.id not in (select dr.re_s_id from doc_rel dr )  --
 
o.id not in (select r.re_id from rt_rights r )  --Права
and o.obj_num = '(неизвестно)'
and o.INSERTED < to_date('10.07.2007')
 
and (select count(dr.id) from ARCH_RE_OBJECTS dr where dr.RE_INCLUDE_ID=o.id) =0
and (select count(dr.id) from ARCH_RE_OBJECTS dr where dr.ARCH_PAR_ID=o.id) =0
and (select count(dr.id) from RE_OBJECTS dr where dr.RE_INCLUDE_ID=o.id) =0
and (select count(dr.id) from APP_RE dr where dr.RE_ID=o.id) =0
and (select count(dr.id) from AR_RE_CANDIDATS dr where dr.RE_ID=o.id) =0
and (select count(dr.id) from cse_cases dr where dr.RE_ID=o.id) =0