TEC-23 – Agregar los campos del solicitante y usuario final en todas las tareas y validaciones

Versión(s)

ECALDIMA 2.0 y superior

Descripción

Durante una validación o una tarea, es conveniente ver los datos del solicitante y del usuario final de una solicitud de servicio.
Los campos se pueden agregar manualmente desde un clic derecho en la validación o tarea en el árbol de procesos del Diseñador.

Si desea que aparezcan estos campos sistemáticamente, el siguiente script lo hará en una sola acción.

Instalación

Los valores de las variables al comienzo del script se pueden modificar para determinar los campos que deberían aparecer
De forma predeterminada, los campos Compañía, Departamento y Ubicación de usuario final y solicitante se asociaran con todas las validaciones y tareas.
Indicar el valor 0 (cero) para no utilizar un campo.
El script puede ser ejecutado varias veces.
La modificación será visible sobre las nuevas solicitudes.

Uso

USE ECALDIMA
GO
--Parameters
--Set the parameter to 1 to associate the field to the tasks and validations. Otherwise set it to 0 (zero)
Declare @AddRequesterCompany int = 1
Declare @AddRequesterDepartment int = 1
Declare @AddRequesterLocation int = 1
Declare @AddEndUserCompany int = 1
Declare @AddEndUserDepartment int = 1
Declare @AddEndUserLocation int = 1

--End of parameters


Declare @ServiceId int
Declare @ProcessId int
Declare @HasEndUserField int
Declare @ProcessNodeId int

Declare c_cur CURSOR FOR
SELECT Id, ProcessId FROM [Service]
JOIN Service_ProcessAssociation ON [Service].Id = Service_ProcessAssociation.ServiceId



SELECT @HasEndUserField = 0
OPEN c_cur
FETCH NEXT FROM c_cur INTO @ServiceId,@ProcessId
WHILE @@FETCH_STATUS = 0
BEGIN
       SELECT @HasEndUserField = 0
       SELECT @HasEndUserField = COUNT(*) FROM Service_FieldAssociation WHERE ServiceId = @ServiceId AND FieldId = -7

       Declare obj_cur CURSOR FOR SELECT Id FROM dbo.fn_GetProcessTreeByRootId(@ProcessId) WHERE NodeType IN ('VALIDATION','TASK')
       OPEN obj_cur
       FETCH NEXT FROM obj_cur INTO @ProcessNodeId
       WHILE @@FETCH_STATUS = 0
       BEGIN
              IF NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -3) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-3,1,-8)
              IF @AddRequesterCompany = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -12) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-12,1,-7)
              IF @AddRequesterDepartment = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -9) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-9,1,-5)
              IF @AddRequesterLocation = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -10) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-10,1,-6)
              
              If @HasEndUserField = 1
              BEGIN
                     IF NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -7) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-7,1,-4)
                     IF @AddEndUserCompany = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -16) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-16,1,-3)
                     IF @AddEndUserDepartment = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -13) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-13,1,-1)
                     IF @AddEndUserLocation = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -14) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-14,1,-2)
              END
              FETCH NEXT FROM obj_cur INTO @ProcessNodeId
       END
       CLOSE obj_cur
       DEALLOCATE obj_cur

       
       FETCH NEXT FROM c_cur INTO @ServiceId,@ProcessId
END
CLOSE c_cur
DEALLOCATE c_cur

Declare @ArticleId int
Declare c_cur CURSOR FOR
SELECT ServiceId, Article_ProcessAssociation.ArticleId, ProcessId 
FROM Article_ProcessAssociation
JOIN Service_ArticleAssociation ON Service_ArticleAssociation.ArticleId = Article_ProcessAssociation.ArticleId

SELECT @HasEndUserField = 0
OPEN c_cur
FETCH NEXT FROM c_cur INTO @ServiceId,@ArticleId,@ProcessId
WHILE @@FETCH_STATUS = 0
BEGIN
       SELECT @HasEndUserField = 0
       SELECT @HasEndUserField = COUNT(*) FROM Service_FieldAssociation WHERE ServiceId = @ServiceId AND FieldId = -7
       Declare obj_cur CURSOR FOR SELECT Id FROM dbo.fn_GetProcessTreeByRootId(@ProcessId) WHERE NodeType IN ('VALIDATION','TASK')
       OPEN obj_cur
       FETCH NEXT FROM obj_cur INTO @ProcessNodeId
       WHILE @@FETCH_STATUS = 0
       BEGIN
              IF NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -3) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-3,1,-8)
              IF @AddRequesterCompany = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -12) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-12,1,-7)
              IF @AddRequesterDepartment = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -9) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-9,1,-5)
              IF @AddRequesterLocation = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -10) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-10,1,-6)
              If @HasEndUserField = 1
              BEGIN
                     IF NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -7) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-7,1,-4)
                     IF @AddEndUserCompany = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -16) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-16,1,-3)
                     IF @AddEndUserDepartment = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -13) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-13,1,-1)
                     IF @AddEndUserLocation = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -14) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-14,1,-2)
              END
              FETCH NEXT FROM obj_cur INTO @ProcessNodeId
       END
       CLOSE obj_cur
       DEALLOCATE obj_cur
       FETCH NEXT FROM c_cur INTO @ServiceId,@ArticleId,@ProcessId
END
CLOSE c_cur
DEALLOCATE c_cur