SELECT
CASE
WHEN TE.FGSTATUS = 1
THEN CAST('Planning' AS VARCHAR (255))
WHEN TE.FGSTATUS = 2
THEN CAST('Start' AS VARCHAR (255))
WHEN TE.FGSTATUS = 3
THEN CAST('Execution' AS VARCHAR (255))
WHEN TE.FGSTATUS = 4
THEN CAST('Closed' AS VARCHAR (255))
WHEN TE.FGSTATUS = 5
THEN CAST('Preparation' AS VARCHAR (255))
WHEN TE.FGSTATUS = 6
THEN CAST('Awaiting release of result' AS VARCHAR (255))
WHEN TE.FGSTATUS = 8
THEN CAST('Cancelled' AS VARCHAR (255))
END AS NMFGSTATUS,
CASE
WHEN TE.FGTYPE = 1
THEN CAST('Self-assessment' AS VARCHAR (255))
WHEN TE.FGTYPE = 2
THEN CAST('Peers' AS VARCHAR (255))
WHEN TE.FGTYPE = 3
THEN CAST('Subordinates' AS VARCHAR (255))
WHEN TE.FGTYPE = 4
THEN CAST('Leader' AS VARCHAR (255))
WHEN TE.FGTYPE = 5
THEN CAST('Review' AS VARCHAR (255))
END AS NMFGTYPE,
CASE
WHEN US2.NMUSER IS NOT NULL
THEN US2.NMUSER
WHEN US2.NMUSER IS NULL
AND TE.FGTYPE = 5
THEN CAST('Automatic review' AS VARCHAR (255))
END AS NMUSEREVAL ,
TEE.NREXECPLAN ,
TEE.IDEVALEXEC ,
TEE.NMEVALEXEC ,
TE.DSEVAL ,
TE.DTREALSTART ,
TE.DTREALFINISH ,
US.IDUSER ,
US.NMUSER ,
TE.VLLEVEL ,
TE.VLPROGRESS ,
CASE
WHEN TEH.FGTYPEITEM = 1
THEN CAST('Competence' AS VARCHAR (255))
WHEN TEH.FGTYPEITEM = 2
THEN CAST('Evaluation item' AS VARCHAR (255))
WHEN TEH.FGTYPEITEM = 3
THEN CAST('Goal' AS VARCHAR (255))
END AS FGTYPEITEM,
TEH.NMHABILITY ,
TEH.NMEVALITEM ,
TEH.NMGOAL
FROM TREVAL TE
INNER JOIN ADALLUSERS US
ON (
US.CDUSER = TE.CDUSER
)
INNER JOIN TRHISTORICAL HI
ON (
HI.CDUSER = US.CDUSER
)
LEFT JOIN ADALLUSERS US2
ON (
US2.CDUSER = TE.CDUSEREVAL
)
INNER JOIN TREVALEXEC TEE
ON (
TEE.CDEVALEXEC = TE.CDEVALEXEC
AND TEE.FGSTATUS >= 3
)
INNER JOIN
(
SELECT 1 AS FGTYPEITEM ,
THB0.CDEVAL ,
THB0.CDHABILITY ,
NULL AS NMEVALITEM ,
TH.NMHABILITY ,
CAST (NULL AS VARCHAR(10)) AS NMGOAL
FROM TREVALHAB THB0
INNER JOIN TRHABILITY TH
ON (
TH.CDHABILITY = THB0.CDHABILITY
)
UNION ALL
SELECT 2 AS FGTYPEITEM ,
THB1.CDEVAL ,
THB1.CDHABILITY ,
TREV.NMEVALITEM ,
TH.NMHABILITY ,
CAST (NULL AS VARCHAR(10)) AS NMGOAL
FROM TREVALHAB THB1
INNER JOIN TRHABILITY TH
ON (
TH.CDHABILITY = THB1.CDHABILITY
)
INNER JOIN TREVALHABITEM TEVHBIT
ON (
TEVHBIT.CDEVAL = THB1.CDEVAL
AND TEVHBIT.CDHABILITY = THB1.CDHABILITY
)
INNER JOIN TREVALITEM TREV
ON (
TEVHBIT.CDEVALITEM = TREV.CDEVALITEM
)
UNION ALL
SELECT 3 AS FGTYPEITEM ,
THB2.CDEVAL ,
NULL AS CDHABILITY ,
NULL AS NMEVALITEM ,
CAST (NULL AS VARCHAR(10)) AS NMHABILITY ,
GNAG.NMACTIVITY AS NMGOAL
FROM CPEVALGOAL THB2
INNER JOIN CPGOAL CPG
ON (
CPG.CDGOAL = THB2.CDGOAL
)
INNER JOIN GNACTIVITY GNAG
ON (
GNAG.CDGENACTIVITY = CPG.CDGENACTIVITY
)
)
TEH
ON (
TE.CDEVAL = TEH.CDEVAL
)
WHERE 1 = 1
AND US.FGUSERENABLED = 1
AND TEE.IDEVALEXEC = 'ID' /*NOTE1*/
AND TEE.NREXECPLAN = 1 /*NOTE2*/
AND US.IDUSER = '0001'/*NOTE3*/
|