Удаление права
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
