sql 根据单据终止流程，删除相关的消息

//查询来源编码为二开采购入库单编码的 且业务日期  为5月1号至 5月7号之间，在流程中的标准出库单
//-------------------------------------------------------
 select * from T_WFR_ProcInst where  FPROCINSTID    in  (  select   distinct  FPROCINSTID    from   T_WFR_Assign   where  FBIZOBJID in  ( 
select  FID from T_IM_PurInWarehsBill   where  FSOURCEBILLID  in  (  select fid from CT_DUC_OutStoragetz  )    and  FBIZDATE   between  to_date('2020-05-01','yyyy-MM-dd') AND to_date('2020-05-7','yyyy-MM-dd') 
  )) 

 

//open.running  运行中      

UPDATE  T_WFR_ProcInst    SET   FSTATE='closed.aborted' WHERE    FPROCINSTID   = (  select  top 1 FPROCINSTID    from   T_WFR_Assign   where  FBIZOBJID='10YAAALva5Z4MGHj'  ) and  FINITIATORNAME_L2='钟卫平' and  FPROCDEFNAME_L2='屠宰采购入库单审核后到应付'

 //单据 的消息中心 用户收到的关于这个单据的消息

select * from   T_WFR_Assign   where  FBIZOBJID='10YAAALva5Z4MGHj'

//---------------------------------------------------------------------------------------------------------------------------------------------------------------

//查询来源编码为二开采购入库单编码的 且业务日期  为5月1号至 5月7号之间，在流程中的标准出库单
//-------------------------------------------------------
     select *  from  T_WFR_ProcInst where  FPROCINSTID    in  (  select   distinct  FPROCINSTID    from   T_WFR_Assign   where  FBIZOBJID in  ( 
select  FID from T_IM_PurInWarehsBill   where  FSOURCEBILLID  in  (  select fid from CT_DUC_OutStoragetz  )    and  FBIZDATE   between  to_date('2020-05-01','yyyy-MM-dd') AND to_date('2020-05-7','yyyy-MM-dd') 
  ))  and  FCONTROLUNITID='10YAAAAEbdvM567U'

//-------------------------------------------------------------------------------------------------------
select * from   T_WFR_Assign   where  FBIZOBJID='10YAAALva5Z4MGHj'            
// 根据流程实例 删除这个流程实例相关的消息中心
select * from   T_WFR_Assign   where  FPROCINSTID in  (      select  FPROCINSTID from  T_WFR_ProcInst where  FPROCINSTID    in  (  select   distinct  FPROCINSTID    from   T_WFR_Assign   where  FBIZOBJID in  ( 
select  FID from T_IM_PurInWarehsBill   where  FSOURCEBILLID  in  (  select fid from CT_DUC_OutStoragetz  )    and  FBIZDATE   between  to_date('2020-05-01','yyyy-MM-dd') AND to_date('2020-05-7','yyyy-MM-dd') 
  ))  and  FCONTROLUNITID='10YAAAAEbdvM567U'
 )  

//最后的更改语句

//查询来源编码为二开采购入库单编码的 且业务日期  为5月1号至 5月7号之间，在流程中的标准出库单
//-------------------------------------------------------
 
//--------------------------------------------------------------------------------------------------------
   UPDATE  T_WFR_ProcInst    SET   FSTATE='closed.aborted'       where  FPROCINSTID      in  (  select   distinct  FPROCINSTID    from   T_WFR_Assign   where  FBIZOBJID in  ( 
select  FID from T_IM_PurInWarehsBill   where  FSOURCEBILLID  in  (  select fid from CT_DUC_OutStoragetz  )    and  FBIZDATE   between  to_date('2020-05-01','yyyy-MM-dd') AND to_date('2020-05-7','yyyy-MM-dd') 
  ))  

//-------------------------------------------------------------------------------------------------------
select * from   T_WFR_Assign   where  FBIZOBJID='10YAAALva5Z4MGHj'            
// 根据流程实例 删除这个流程实例相关的消息中心 
      delete from    T_WFR_Assign   where  FPROCINSTID in  (      select  FPROCINSTID from  T_WFR_ProcInst where  FPROCINSTID    in  (  select   distinct  FPROCINSTID    from   T_WFR_Assign   where  FBIZOBJID in  ( 
select  FID from T_IM_PurInWarehsBill   where  FSOURCEBILLID  in  (  select fid from CT_DUC_OutStoragetz  )    and  FBIZDATE   between  to_date('2020-05-01','yyyy-MM-dd') AND to_date('2020-05-7','yyyy-MM-dd') 
  ))  
 )