特殊规则更改时间

评级流程在一天内间隔30分钟

--1.更新发起时间和立项时间
update tcrt_project t
   set t.p_savedate  =to_char(to_date(&基准时间,'yyyy-mm-dd hh:mi')+1/96,'yyyy-mm-dd hh24:mi'),
       t.p_startdate = to_char(to_date(&基准时间,'yyyy-mm-dd hh:mi')+2/96,'yyyy-mm-dd hh24:mi')
where t.p_code =&"项目编码";
--2.
update txf_result_task t
   set t.m_create_time =
       (select to_char(to_date(&基准时间,'yyyy-mm-dd hh:mi')+t1.nums/48,'yyyy-mm-dd hh24:mi') from (select 
               m_id,
               dense_rank() over(partition by p_code order by p_number) nums
          from txf_result_task
          where p_code = &项目编码)t1 where t.m_id=t1.m_id ),
    t.task_deal_time=  (select to_char(to_date(&基准时间,'yyyy-mm-dd hh:mi')+(t1.nums)/48+1/96,'yyyy-mm-dd hh24:mi') from (select 
               m_id,
               dense_rank() over(partition by p_code order by p_number) nums
          from txf_result_task
          where p_code = &项目编码)t1 where t.m_id=t1.m_id )    
 where t.p_code = &项目编码;
 --3.
update txf_result t
set t.r_begtime= (select p_savedate from tcrt_project t1 where t.p_code=t1.p_code),
t.r_endtime=(select max(task_deal_time) from txf_result_task t2 where t.p_code=t2.p_code)
where t.p_code = &项目编码;
--4.
update txf_note_result t
set t.v_savetime=(select to_char(to_date(max(t1.task_deal_time),'yyyy-mm-dd hh:mi'),'yyyy-mm-dd hh24:mi:ss') from txf_result_task t1
where t1.p_code=&项目编码 and m_content in ('修改投票','参与投票')
and t1.m_to_user=t.v_voter)
where t.r_code=(select r_code from txf_result where p_code = &项目编码);
--5
update txf_result_step t
   set t.f_ctime =(select  to_char(to_date(max(t1.task_deal_time), 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
   from txf_result_task t1 where t1.r_code=t.r_code 
   and t1.f_code=t.f_code)
   where t.r_code in (select r_code from txf_result where p_code = &项目编码);
--6
update tcrt_result t
   set (t.savedate,t.savetime,t.update_time,t.beg_date,t.end_date) =(
   select 
   to_char(to_date(max(t1.task_deal_time), 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') ,
   to_char(to_date(max(t1.task_deal_time), 'yyyy-mm-dd hh24:mi:ss'),'hh24:mi'),
   to_date(max(t1.task_deal_time), 'yyyy-mm-dd hh24:mi:ss'),
   to_char(to_date(max(t1.task_deal_time), 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd'),
    to_char((to_date(max(t1.task_deal_time), 'yyyy-mm-dd hh24:mi:ss'))+numtoyminterval(1,'year'),'yyyy-mm-dd')
   from txf_result_task t1 where t.p_code=t1.p_code
   and m_content in ('修改投票','参与投票'))
 where t.p_code = to_char(&项目编码);
--7
update tcrt_document t
   set t.f_savedate =(select min(t1.task_deal_time) from txf_result_task t1 
   where t.n_f_code=t1.f_code
   and t.r_code=t1.r_code
   and t.f_uploader=t1.m_to_user)
 where t.r_code = (select r_code from txf_result where p_code = &项目编码);
update tcrt_document t
   set t.f_savedate =(select t1.p_startdate from tcrt_project t1 where t.r_code=t1.p_code)
 where t.r_code =to_char(&项目编码);
--8
update tcrt_event t
   set t.e_savedate =
       (select
       to_char((to_date(min(t1.task_deal_time), 'yyyy-mm-dd hh24:mi:ss'))-1/96,'yyyy-mm-dd hh24:mi')                                 
 from txf_result_task t1
 where t1.m_type = '1'
   and t.r_code = t1.r_code
   and t.n_f_code = t1.f_code
   and t.e_editor = t1.m_to_user)
 where t.r_code = (select r_code from txf_result where p_code = &项目编码);