goosegle

cuadro de mando auditorias 2

Jan 14th, 2021
663
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH InfoProcedimientos (ID, Procedimientos, Realizados, Terminados)
  2. AS
  3. (
  4.     SELECT CA.ID AS ID, COUNT(P.ID) AS Procedimientos, COUNT(P.RealizadoPorId) AS Realizados, COUNT(P.RevisadoPorId) AS Terminados
  5.     FROM ControlAuditoria CA
  6.         INNER JOIN ProgramaTrabajo PT ON (CA.ProgramaTrabajo_ID = PT.ID)
  7.         INNER JOIN Objetivos O ON (O.ProgramaTrabajoId = PT.ID)
  8.         INNER JOIN Procedimientos P ON (P.ObjetivoId = O.ID)
  9.     WHERE P.NoAplica = 0
  10.     GROUP BY CA.ID, CA.Nombre
  11. ),
  12. UltimoInforme (Nombre, ControlAuditoriaId)
  13. AS
  14. (
  15.     SELECT CV.Nombre, VA.ControlAuditoriaId
  16.     FROM DefinicionVersionInforme CV
  17.         LEFT JOIN VersionInformeAuditoria VA ON (VA.DefinicionVersionInforme_ID = CV.ID)
  18.     WHERE VA.ID = (SELECT MAX(ID) FROM VersionInformeAuditoria V2
  19.                    WHERE V2.ControlAuditoriaId = VA.ControlAuditoriaId GROUP BY V2.ControlAuditoriaId)
  20. )
  21. SELECT DISTINCT PC.Nombre AS PlanControl, PC.AnyoDelPlan, MT.Nombre AS Marco, C.ID, Lote.Nombre AS Lote, C.Nombre, W.StateKey AS Estado, I.Nombre AS Informe, Procedimientos, Realizados, Terminados
  22. FROM ControlAuditoria C
  23.     INNER JOIN LoteControl LC ON (C.ID = LC.ControlAuditoriaId)
  24.     INNER JOIN Lote ON (LC.LoteId = Lote.ID)
  25.     INNER JOIN PlanControl PC ON (PC.ID = Lote.PlanControlId)
  26.     INNER JOIN MarcosTrabajo MT ON (MT.ID = PC.MarcoTrabajoId)
  27.     INNER JOIN WorkflowPersistence W ON (W.EntityId = C.ID)
  28.     INNER JOIN InfoProcedimientos PR ON (PR.ID = C.ID)
  29.     LEFT JOIN UltimoInforme I ON (I.ControlAuditoriaId = C.ID)
  30. WHERE MT.IsDeleted = 0
  31.     AND PC.IsDeleted = 0
  32.     AND C.IsDeleted = 0
  33.     AND W.WorkflowTypeIdentifier = 'controlauditoria'
  34. ORDER BY PC.Nombre, PC.AnyoDelPlan DESC, MT.Nombre
  35.  
RAW Paste Data