I actually got a script from support as it is a known issue in GP2015 & fixed in GP2016. Here is the script to fix existing trx & then you can run another to fix for future trx on each company db.
1. The following script can be used to remove the time beyond seconds from the CREATETIME field for existing records:
UPDATE CO00104 set CREATETIME = convert(varchar(20),CREATETIME,120) where AttachmentStatus = 'Workflow Message'
2. Below is a script, which will modify the existing wfSubmit stored procedure. This can be run against each company database and will cause future records to be inserted without milliseconds in the timestamp.
/****** Object: StoredProcedure [dbo].[wfSubmit] Script Date: 11/12/2015 3:54:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[wfSubmit] @Workflow_Business_Object_Key char(201),
@Workflow_Type_Name char(51),
@Workflow_User char(85),
@Resubmit_Workflow tinyint,
@Workflow_Comments char(2000),
@TempFilePath nvarchar(300) = '',
@Workflow_Error int OUTPUT
AS
BEGIN
set NOCOUNT ON
declare @FormID smallint,
@DICTID smallint
declare @WorkflowInstanceID char(37),
@WF_Alt_FinalApprover char(37)
declare @Workflow_Step_Name char(51)
declare @WorkflowStepInstanceID char(37)
declare @valid_step tinyint,
@assignment_status tinyint,
@tasks_created tinyint
declare @Workflow_Step_Condition smallint,
@Workflow_Step_Type smallint
declare @Workflow_Step_Conditions char(37)
declare @Workflow_Step_Assign_To char(37)
declare @Workflow_Managers char(37)
declare @FieldsListGuid char(37)
declare @user_assignment_table TABLE
(
workflow_assigned_to char(128)
)
declare @calculate_due_date tinyint,
@Workflow_Require_One_App tinyint,
@action_taken tinyint,
@escalate tinyint,
@approval_tasks_created tinyint,
@workflow_action tinyint,
@WF_Use_Alt_Final_Approv tinyint
declare @where_clause varchar(4000)
declare @main_table char(50)
declare @Workflow_Name char(51),
@Workflow_Version int
declare @Workflow_Due_Date datetime,
@Workflow_Due_Time datetime,
@Workflow_Completion_Date datetime,
@Workflow_Completion_Time datetime
declare @Comments_for_History char(2000)
declare @DocAttachBusObjKey varchar(201)
declare @DrillBack varchar(255)
declare @Sql nvarchar(2000)
declare @DocAttachBusObjKeyFunction varchar(100)
declare @DrillBackFunction varchar(100)
declare @ParamDef nvarchar(1000)
declare @WF_SendNotificatications tinyint,
@Workflow_Manually_Delega tinyint,
@WF_AllowOrigApprover tinyint,
@WF_Overdue_Task_Action smallint,
@Workflow_Delegate_To char(37)
declare @DocumentUpdatedForSubmit tinyint
declare @WFIncludeDocumentAttach tinyint
declare @DocAttachUpdateStatus tinyint
select @Workflow_User = UPPER(@Workflow_User)
select @Workflow_Error = 0,
@tasks_created = 0,
@DocAttachUpdateStatus = 0
select @FormID = FormID,
@DICTID = DICTID,
@Workflow_Managers = Workflow_Managers,
@FieldsListGuid = FieldsListGuid,
@DocAttachBusObjKeyFunction = DocAttachBusObjKeyFuncti,
@DrillBackFunction = DocumentDrillDownFunctio
from WF100001
where Workflow_Type_Name = @Workflow_Type_Name
select @DocumentUpdatedForSubmit = 0
if ( @DocAttachBusObjKeyFunction is not null
and len(@DocAttachBusObjKeyFunction) > 0 )
begin
Begin Try
set @ParamDef = '@DocAttachBusObjKeyOut varchar(201) output'
set @Sql = 'select @DocAttachBusObjKeyOut = [dbo].['
+ ltrim(rtrim(@DocAttachBusObjKeyFunction))
+ ']('''
+ ltrim(rtrim(@Workflow_Business_Object_Key))
+ ''', '''
+ ltrim(rtrim(@Workflow_Type_Name)) + ''')'
execute sp_executesql
@Sql,
@ParamDef,
@DocAttachBusObjKeyOut = @DocAttachBusObjKey output
select @DocAttachBusObjKey = isnull(@DocAttachBusObjKey, '')
end try
begin catch
select @DocAttachBusObjKey = ''
end catch
end
else
begin
select @DocAttachBusObjKey = ''
end
if ( @DrillBackFunction is not null
and len(@DrillBackFunction) > 0 )
begin
begin try
set @ParamDef = '@DrillBackOUT varchar(255) output'
set @Sql = 'select @DrillBackOUT = [dbo].['
+ ltrim(rtrim(@DrillBackFunction)) + ']('''
+ ltrim(rtrim(@Workflow_Business_Object_Key))
+ ''', '''
+ ltrim(rtrim(@Workflow_Type_Name)) + ''')'
execute sp_executesql
@Sql,
@ParamDef,
@DrillBackOUT = @DrillBack output
select @DrillBack = isnull(@DrillBack, '')
end try
begin catch
select @DrillBack = ''
end catch
end
else
begin
select @DrillBack = ''
end
if @Resubmit_Workflow > 0
begin
select top 1 @Workflow_Name = Workflow_Name,
@Workflow_Version = Workflow_Version
from WFI10002
where Workflow_Type_Name = @Workflow_Type_Name
and WfBusObjKey = @Workflow_Business_Object_Key
select top 1 @WF_Use_Alt_Final_Approv = WF_Use_Alt_Final_Approv,
@WF_Alt_FinalApprover = WF_Alt_FinalApprover
from WF100002
where Workflow_Name = @Workflow_Name
and Workflow_Version = @Workflow_Version
end
else
begin
select top 1 @Workflow_Name = Workflow_Name,
@Workflow_Version = Workflow_Version,
@WF_Alt_FinalApprover = WF_Alt_FinalApprover
from WF100002
where ACTIVE = 1
and Workflow_Type_Name = @Workflow_Type_Name
end
if @Resubmit_Workflow > 0
begin
select @WorkflowInstanceID = WorkflowInstanceID,
@where_clause = Workflow_Where_Clause
from WFI10002
where Workflow_Name = @Workflow_Name
and WfBusObjKey = @Workflow_Business_Object_Key
select @WF_SendNotificatications = WF_SendNotificatications,
@Workflow_Manually_Delega = Workflow_Manually_Delega,
@WF_AllowOrigApprover = WF_AllowOrigApprover,
@Workflow_Require_One_App = Workflow_Require_One_App,
@WF_Use_Alt_Final_Approv = WF_Use_Alt_Final_Approv,
@WF_Alt_FinalApprover = WF_Alt_FinalApprover,
@WF_Overdue_Task_Action = WF_Overdue_Task_Action,
@Workflow_Delegate_To = Workflow_Delegate_To
from WF100002
where Workflow_Name = @Workflow_Name
and Workflow_Version = @Workflow_Version
update WFI10002
set WF_SendNotificatications = @WF_SendNotificatications,
Workflow_Manually_Delega = @Workflow_Manually_Delega,
WF_AllowOrigApprover = @WF_AllowOrigApprover,
Workflow_Require_One_App = @Workflow_Require_One_App,
WF_Use_Alt_Final_Approv = @WF_Use_Alt_Final_Approv,
WF_Alt_FinalApprover = @WF_Alt_FinalApprover,
WF_Overdue_Task_Action = @WF_Overdue_Task_Action,
Workflow_Delegate_To = @Workflow_Delegate_To,
Workflow_Originator = @Workflow_User,
Workflow_Status = 3
where WorkflowInstanceID = @WorkflowInstanceID
select @DocumentUpdatedForSubmit = 1
end
else
begin
select @WorkflowInstanceID = convert(char(37), newid())
exec wfGenerateDocumentWhereClause
@Workflow_Business_Object_Key,
@Workflow_Type_Name,
0,
0,
1,
@main_table OUTPUT,
@where_clause OUTPUT
insert into WFI10002
select @WorkflowInstanceID,
Workflow_Name,
@Workflow_Business_Object_Key,
Workflow_Version,
Workflow_Description,
Workflow_Type_Name,
@FormID,
@DICTID,
3,
WF_SendNotificatications,
Workflow_Manually_Delega,
@Workflow_User,
WF_AllowOrigApprover,
Workflow_Require_One_App,
WF_Use_Alt_Final_Approv,
WF_Alt_FinalApprover,
WF_Overdue_Task_Action,
Workflow_Delegate_To,
@main_table,
@DrillBack,
0,
@DocAttachBusObjKey,
SYSDATETIME(),
@where_clause
from WF100002
where Workflow_Name = @Workflow_Name
and Workflow_Version = @Workflow_Version
select @DocumentUpdatedForSubmit = 1
end
if @Resubmit_Workflow = 0
begin
select @Workflow_Step_Name = 'Workflow Originated',
@action_taken = 1
end
else
begin
select @Workflow_Step_Name = 'Resubmit Workflow',
@action_taken = 2
end
select @Workflow_Due_Date = cast(SYSDATETIME() as date),
@Workflow_Due_Time = cast(SYSDATETIME() as time)
select @Workflow_Completion_Date = @Workflow_Due_Date,
@Workflow_Completion_Time = @Workflow_Due_Time
exec wfCreateHistoryRecord
@WorkflowInstanceID,
'',
@Workflow_User,
@Workflow_Name,
@Workflow_Step_Name,
'',
@action_taken,
@Workflow_Due_Date,
@Workflow_Due_Time,
@Workflow_Completion_Date,
@Workflow_Completion_Time,
@Workflow_Comments,
@Workflow_Error OUTPUT
declare Workflow_Steps cursor local fast_forward for
select Workflow_Step_Name,
Workflow_Step_Condition,
Workflow_Step_Conditions,
Workflow_Step_Assign_To
from WF100003
where Workflow_Name = @Workflow_Name
and Workflow_Version = @Workflow_Version
and WF_Step_Predecessor = ''
open Workflow_Steps
fetch next from Workflow_Steps into @Workflow_Step_Name, @Workflow_Step_Condition, @Workflow_Step_Conditions, @Workflow_Step_Assign_To
if @@FETCH_STATUS = 0
begin
while @@FETCH_STATUS = 0
begin
delete from @user_assignment_table
select @WorkflowStepInstanceID = convert(char(37), newid())
insert into WFI10003
select @WorkflowStepInstanceID,
Workflow_Step_Name,
@WorkflowInstanceID,
Workflow_Name,
Workflow_Version,
WF_Step_Description,
2,
Workflow_Step_Sequence,
Workflow_Step_Type,
Workflow_Step_Order,
Workflow_Step_Condition,
Workflow_Step_Conditions,
WF_Step_Predecessor,
Workflow_Step_Assign_To,
EmailMessageID,
Workflow_Step_Send_Email,
Workflow_Step_Time_Limit,
WF_Step_Time_Limit_UofM,
WF_Step_Completion_Polic,
WF_Apply_WF_Calendar,
NOTEINDX,
SYSDATETIME()
from WF100003
where Workflow_Step_Name = @Workflow_Step_Name
and Workflow_Name = @Workflow_Name
and Workflow_Version = @Workflow_Version
select @valid_step = 0,
@assignment_status = 0
if @Workflow_Step_Condition = 1
begin
exec wfEvaluateStep
@WorkflowInstanceID,
@FieldsListGuid,
@Workflow_Step_Conditions,
@valid_step OUTPUT
end
else
begin
select @valid_step = 1
end
if @valid_step > 0
begin
select @WFIncludeDocumentAttach = WFIncludeDocumentAttach
from WF100003
where Workflow_Name = @Workflow_Name
and Workflow_Step_Name = @Workflow_Step_Name
if @WFIncludeDocumentAttach = 1
begin
exec wfDocAttach
@Workflow_Type_Name,
@Workflow_Business_Object_Key,
@WorkflowStepInstanceID
end
select @calculate_due_date = 1,
@escalate = 0
exec wfAssignTasks
@WorkflowStepInstanceID,
@Workflow_Step_Assign_To,
@Workflow_User,
@calculate_due_date,
@escalate,
@TempFilePath,
@assignment_status OUTPUT,
@approval_tasks_created OUTPUT
if @assignment_status = 0
begin
update WFI10002
set Workflow_Status = 4
where WorkflowInstanceID = @WorkflowInstanceID
exec wfUpdateDocument
@WorkflowInstanceID,
1,
4,
'',
'',
@Workflow_Error OUTPUT
select @DocumentUpdatedForSubmit = 0
if @WFIncludeDocumentAttach = 1
begin
set @DocAttachUpdateStatus=1
end
select @tasks_created = 1
end
else
begin
select @workflow_action = 5
select @Comments_for_History = 'The workflow was automatically rejected by the system. Alternate final approval was required, but no approver was found.'
exec wfReject
@WorkflowStepInstanceID,
@Workflow_User,
1,
@Comments_for_History,
@Workflow_Error OUTPUT
return
end
end
else
begin
update WFI10003
set Workflow_Step_Status = 1
where WorkflowStepInstanceID = @WorkflowStepInstanceID
select @Workflow_Completion_Date = cast(SYSDATETIME() as date),
@Workflow_Completion_Time = cast(SYSDATETIME() as time)
select @Workflow_Step_Assign_To = Workflow_Step_Assign_To,
@Workflow_Step_Type = Workflow_Step_Type
from WFI10003
where WorkflowStepInstanceID = @WorkflowStepInstanceID
if @Workflow_Step_Type > 1
begin
select @workflow_action = 4
select @Comments_for_History = 'No action is required for this step.'
end
else
begin
select @workflow_action = 3
select @Comments_for_History = 'No approval is required for this step.'
end
exec wfCreateHistoryRecord
@WorkflowInstanceID,
@WorkflowStepInstanceID,
'',
@Workflow_Name,
@Workflow_Step_Name,
'',
@workflow_action,
'',
'',
@Workflow_Completion_Date,
@Workflow_Completion_Time,
@Comments_for_History,
@Workflow_Error OUTPUT
end
fetch next from Workflow_Steps into @Workflow_Step_Name, @Workflow_Step_Condition, @Workflow_Step_Conditions, @Workflow_Step_Assign_To
end
end
close Workflow_Steps
deallocate Workflow_Steps
if @tasks_created = 0
begin
select @Workflow_Require_One_App = Workflow_Require_One_App,
@WF_Use_Alt_Final_Approv = WF_Use_Alt_Final_Approv
from WFI10002
where WorkflowInstanceID = @WorkflowInstanceID
if @Workflow_Require_One_App = 0
begin
update WFI10002
set Workflow_Status = 3,
Workflow_Originator = @Workflow_User
where WorkflowInstanceID = @WorkflowInstanceID
exec wfUpdateDocument
@WorkflowInstanceID,
1,
3,
'',
'',
@Workflow_Error OUTPUT
select @DocumentUpdatedForSubmit = 0
end
else
begin
select @Workflow_Step_Name = 'Alternate Final Approval'
select @WorkflowStepInstanceID = convert(char(37), newid())
insert into WFI10003
select @WorkflowStepInstanceID,
@Workflow_Step_Name,
@WorkflowInstanceID,
Workflow_Name,
Workflow_Version,
@Workflow_Step_Name,
2,
0,
1,
0,
0,
'',
'',
@WF_Alt_FinalApprover,
'',
0,
8,
1,
0,
1,
0,
SYSDATETIME()
from WF100002
where Workflow_Name = @Workflow_Name
and Workflow_Version = @Workflow_Version
select @WFIncludeDocumentAttach = WFIncludeDocumentAttach
from WF100003
where Workflow_Name = @Workflow_Name
and Workflow_Step_Name = @Workflow_Step_Name
if @WFIncludeDocumentAttach = 1
begin
exec wfDocAttach
@Workflow_Type_Name,
@Workflow_Business_Object_Key,
@WorkflowStepInstanceID
end
select @calculate_due_date = 0,
@escalate = 0
if @WF_Alt_FinalApprover = ''
begin
select @assignment_status = 1
end
else
begin
exec wfAssignTasks
@WorkflowStepInstanceID,
@WF_Alt_FinalApprover,
@Workflow_User,
@calculate_due_date,
@escalate,
@TempFilePath,
@assignment_status OUTPUT,
@approval_tasks_created OUTPUT
if @assignment_status = 0
if @WFIncludeDocumentAttach = 1
begin
set @DocAttachUpdateStatus=1
end
end
if @assignment_status > 0
begin
update WFI10003
set Workflow_Step_Assign_To = @Workflow_Managers
where WorkflowStepInstanceID = @WorkflowStepInstanceID
exec wfAssignTasks
@WorkflowStepInstanceID,
@Workflow_Managers,
@Workflow_User,
@calculate_due_date,
@escalate,
@TempFilePath,
@assignment_status OUTPUT,
@approval_tasks_created OUTPUT
if @assignment_status > 0
begin
select @Workflow_User = 'System'
select @Workflow_Comments = 'The task assignment was automatically rejected by the system.'
exec wfReject
@WorkflowStepInstanceID,
@Workflow_User,
1,
@Workflow_Comments,
@Workflow_Error OUTPUT
end
else if @assignment_status = 0
begin
if @WFIncludeDocumentAttach = 1
set @DocAttachUpdateStatus=1
end
end
if @assignment_status = 0
begin
update WFI10002
set Workflow_Status = 4,
Workflow_Originator = @Workflow_User
where WorkflowInstanceID = @WorkflowInstanceID
exec wfUpdateDocument
@WorkflowInstanceID,
1,
4,
'',
'',
@Workflow_Error OUTPUT
select @DocumentUpdatedForSubmit = 0
select @tasks_created = 1
end
end
end
if @DocAttachUpdateStatus = 1
begin
insert into CO00104
select BusObjKey,
Attachment_ID,
'Workflow Message',
CONVERT (date, SYSDATETIME()),
--CONVERT (time, SYSDATETIME()),
CONVERT(varchar(12),SYSDATETIME(),108),
CRUSRID
from CO00102
where WorkflowStepInstanceID = @WorkflowStepInstanceID
end
if @DocumentUpdatedForSubmit = 1
begin
exec wfUpdateDocument
@WorkflowInstanceID,
1,
3,
'',
'',
@Workflow_Error OUTPUT
end
END
GO
Share
Report
All responses (
Answers (
15
