asset_close
Task, wo luegt, öb en Asset cha gschlosse wärde
select ob.id as ID
from obj_asset oa
,obj ob
,obj_class ocl
,code_obj_class cc
,(select asset_id as asset_id
from pos_serpil
where serpil_id in (select id from serpil where serie_type_id = 21
and done= '+'
and date_type_id = 4
and trunc (period_end) >= trunc (sysdate) -30)
and amount <> 0
group by asset_id) pos_count_list_30
,(select asset_id as asset_id
from pos_serpil
where serpil_id in (select id from serpil where serie_type_id = 21
and done= '+'
and date_type_id = 4
and trunc (period_end) >= trunc (sysdate) -120)
and amount <> 0
and asset_id in (select aa.obj_id from obj_asset aa, obj_class bb
where country_domi_id = 2014
and aa.OBJ_ID = bb.OBJ_ID
and bb.OBJ_CLASS_ID = 8032)
group by asset_id) pos_count_list_japan_120
,(select undrly_id as asset_id from ASSET_COMPO_CONV acc, obj_asset_compo oac, obj o
where acc.compo_id = oac.compo_id (+)
and oac.asset_id = o.id (+)
and undrly_id is not null
and o.close_date is null
group by undrly_id) conv_count_list
,(select oau.asset_undrly_id as asset_id
from obj_asset oau, obj o
where oau.obj_id = o.id (+)
and o.close_date is null
and oau.asset_undrly_id is not null
group by oau.asset_undrly_id) undrly_count_list
,(select asset_id as asset_id
from doc d
where d.meta_typ_id in (1, 7, 9, 27, 210, 218, 231, 900, 901, 902, 903, 904,911, 912, 913, 914, 915)
and d.asset_id is not null
and d.wfc_status_id in (select id from wfc_status where meta_typ_id = d.meta_typ_id and is_in_work is not null)
group by asset_id) open_orders_count_list
where oa.obj_id = pos_count_list_30.asset_id (+)
and oa.obj_id = pos_count_list_japan_120.asset_id (+)
and oa.obj_id = conv_count_list.asset_id (+)
and oa.OBJ_ID = undrly_count_list.asset_id (+)
and oa.OBJ_ID = open_orders_count_list.asset_id (+)
and ocl.obj_classif_id = 117
and ocl.obj_class_id in (8031, 8032, 8033, 8034, 8037, 8038, 8064)
and ocl.obj_class_id = cc.id
and oa.obj_id = ocl.obj_id
and oa.obj_id = ob.id
and (ob.block_id is null or ob.block_id <> 18)
and pos_count_list_30.asset_id is null
and pos_count_list_japan_120.asset_id is null
and open_orders_count_list.asset_id is null
and conv_count_list.asset_id is null
and undrly_count_list.asset_id is null
and close_date is null
and oa.obj_id not in (select distinct obj_id from OBJ_MPF_HOBS_SERIES where valid_to = to_date( '31.12.4712', 'dd.mm.yyyy'))
and not exists (select *
from obj_collect_item oci
where item_id = oa.obj_id
and not exists(select 1 from obj where id = oci.obj_id and close_date <= sysdate))
group by ob.id
having (select count(*)
from evt_pkt3 pe
,obj ob
,evt3 ev
where 1=1
and pe.evt_id = ev.id
and ob.id = pe.pos_id
and ob.instru_id = ob.id
and ev.done_date > sysdate) = 0
