更新流程有效期間

UPDATE ProcessDefinitionHeader  
SET validTo = '9999-12-31 23:59:59.000'  
,objectVersion = objectVersion + 1  
WHERE OID IN (  
    SELECT  
        ProcessDefinitionHeader.OID  
    FROM ProcessDefinition  
    INNER JOIN ProcessDefinitionHeader  
        ON ProcessDefinition.headerOID = ProcessDefinitionHeader.OID  
    INNER JOIN ProcessPackage_ProcessDef  
        ON ProcessDefinition.OID = ProcessPackage_ProcessDef.ProcessDefinitionOID  
    INNER JOIN ProcessPackage  
        ON ProcessPackage_ProcessDef.ProcessPackageOID = ProcessPackage.OID  
    INNER JOIN RedefinableHeader  
        ON ProcessPackage.redefinableHeaderOID = RedefinableHeader.OID  
    INNER JOIN ProcessPackageCmItem  
        ON ProcessPackage.id = ProcessPackageCmItem.id  
        AND RedefinableHeader.version = ProcessPackageCmItem.lastVersion  
    WHERE 1 = 1  
    AND validTo >= GETDATE() /**不更新舊流程*/  
    AND RedefinableHeader.publicationStatus = 'RELEASED' /**只更新RELEASE的*/  
);

建議也可將表單截止有效日欄位設為NULL以避免突然找不到表單的問題。

UPDATE FormDefinition SET validTo = NULL WHERE validTo IS NOT NULL

easyflow gp, sql — 2019年9月11日