Obsidian/Recognition/Work Related/프로젝트/스마트계류장/1.데이터베이스/EAI 연계 데이터 복구.md

------------------------------------------------------------------------------------------------------------------------
/*복구 대상 데이터 특정하여 임시테이블에 insert */

truncate table ueai.flt_restore;


insert into ueai.flt_restore(flt_item, dateandtime)  
select 'ARTS_ARR' as flt_item, dateandtime from ueai.EAI_ELECSTRIPARRARTS_RCV where recv_time >= '20240709000000000' union all
select 'ARTS_DEP' as flt_item, dateandtime from ueai.EAI_ELECSTRIPDEPARTS_RCV where recv_time >= '20240709000000000' union all
select 'ACDM_ARR' as flt_item, dateandtime from ueai.EAI_FLTACDMMLSTNARR_RCV where recv_time >= '20240709000000000' union all
select 'ACDM_DEP' as flt_item, dateandtime from ueai.EAI_FLTACDMMLSTNDEP_RCV where recv_time >= '20240709000000000' union all
select 'GAM' as flt_item, dateandtime from ueai.EAI_GRDACMVT_RCV where recv_time >= '20240709000000000' ;


select flt_item, count(dateandtime) as dataCnt
from ueai.flt_restore
group by flt_item ;


select flt_item, count(dateandtime) as dataCnt
from ueai.flt_restore
group by flt_item ;

------------------------------------------------------------------------------------------------------------------------
/*업무 테이블 데이터 삭제*/

--select count(*)
delete
from uic.SACP_FLT_DEP_HIST 
where if_dt in (
 select dateandtime from ueai.flt_restore where flt_item in ('ARTS_DEP', 'ACDM_DEP') );


--select count(*)
delete
from uic.SACP_FLT_ARR_HIST 
where if_dt in (
 select dateandtime from ueai.flt_restore where flt_item in ('ARTS_ARR', 'ACDM_ARR') ); 


--select count(*)
delete
from uic.SACP_FLT_APRON_MVMN_HIST 
where if_dt in (
 select dateandtime from ueai.flt_restore where flt_item in ('GAM') );


--select count(*)
delete
from uic.SACP_GAM_LAST 
where if_dt in (
 select dateandtime from ueai.flt_restore where flt_item in ('GAM') );


--select count(*)
delete
from uic.SACP_FLT_DEICING_HIST  
where if_dt in (
 select dateandtime from ueai.flt_restore where flt_item in ('ACDM_DEP') );


----------------------------------------------------------------------------------------------------------------------
/* 연계 테이블 연계상태 초기화*/

update ueai.EAI_ELECSTRIPARRARTS_RCV 
set deal_stat = 'S', DEAL_DESC = null
where DATEANDTIME in 
 ( select dateandtime 
    from ueai.flt_restore 
    where flt_item = 'ARTS_ARR' );

update ueai.EAI_ELECSTRIPDEPARTS_RCV 
set deal_stat = 'S', DEAL_DESC = null
where DATEANDTIME in 
 ( select dateandtime 
    from ueai.flt_restore 
    where flt_item = 'ARTS_DEP' );

update ueai.EAI_FLTACDMMLSTNARR_RCV 
set deal_stat = 'S', DEAL_DESC = null
where DATEANDTIME in 
 ( select dateandtime 
    from ueai.flt_restore 
    where flt_item = 'ACDM_ARR' );
    
update ueai.EAI_FLTACDMMLSTNDEP_RCV 
set deal_stat = 'S', DEAL_DESC = null
where DATEANDTIME in 
 ( select dateandtime 
    from ueai.flt_restore 
    where flt_item = 'ACDM_DEP' )  ;
    
update ueai.EAI_GRDACMVT_RCV 
set deal_stat = 'S', DEAL_DESC = null
where DATEANDTIME in 
 ( select dateandtime 
    from ueai.flt_restore 
    where flt_item = 'GAM' )  ;   
    
   
----------------------------------------------------------------------------------------------------------------------
/* 프로시져 실행 */

DECLARE
	-- PROCEDURE UEAI.PROC_EAI_INTRFC_ALL
	RET_MSG VARCHAR(100);
BEGIN
	UEAI.PROC_EAI_INTRFC_ALL(RET_MSG);

	COMMIT;
END;