Overview

This command will list all indicators with past due measurements, as well as the parties responsible for the indicator measurement input, the ID # and name of the scorecard indicator, ID # and name of the scorecard, and the access type linked to the indicator that causes the task to be displayed to the responsible user.

Related tables

TABLE	Description	Primary key (PK)	Connection tables
STSCMETRIC	Stores the scorecard indicator	CDSCMETRIC CDSCORECARD CDREVISION	STSCORECARD STSCMETRICACCESS
STSCMETRICACCESS	Stores the scorecard indicator securities	CDSCMETRICACCESS CDSCMETRIC CDSCORECARD CDREVISION	STSCMETRIC
STSCORECARD	Stores the scorecard	CDSCORECARD CDREVISION	STSCMETRIC
STMETRIC	Stores the indicator	CDMETRIC	STSCMETRIC
ADTEAMUSER	Stores team users	CDTEAM CDUSER	ADUSER
ADUSER	Stores internal system users	CDUSER
ADUSERDEPTPOS	Stores users in departments and positions	CDUSER CDDEPARTMENT CDPOSITION	ADUSER
#ADALLUSERS	Stores internal/external system users	CDUSER

Oracle

SELECT
   SUBQUERY.IDUSER AS 'User ID',
   SUBQUERY.IDSCMETRIC AS 'Scorecard indicator ID #',
   SUBQUERY.NMMETRIC AS 'Indicator name',
   SUBQUERY.IDSCORECARD AS 'Scorecard ID',
   SUBQUERY.NMSCORECARD AS 'Scorecard name',
   SUBQUERY.FGACCESSTYPE AS 'Access type',
   SUBQUERY.FGPERMISSION AS 'Permission'
FROM (
   SELECT
   STSCMETRIC.IDSCMETRIC,
   STMETRIC.NMMETRIC,
   STSCORECARD.IDSCORECARD,
   STSCORECARD.NMSCORECARD,
   CASE WHEN MACCESS.FGACCESSTYPE = 1 THEN 'Team' WHEN MACCESS.FGACCESSTYPE = 2 THEN 'Department' WHEN MACCESS.FGACCESSTYPE = 3 THEN 'Department/Position' WHEN MACCESS.FGACCESSTYPE = 4 THEN 'Position' WHEN MACCESS.FGACCESSTYPE = 5 THEN 'User' WHEN MACCESS.FGACCESSTYPE = 6 THEN 'All' END AS FGACCESSTYPE,
   CASE WHEN MACCESS.FGACCESSTYPE = 1 THEN ADUTEAM.IDUSER WHEN MACCESS.FGACCESSTYPE = 2 THEN ADUDEPART.IDUSER WHEN MACCESS.FGACCESSTYPE = 3 THEN ADUDEPTPOS.IDUSER WHEN MACCESS.FGACCESSTYPE = 4 THEN ADUPOS.IDUSER WHEN MACCESS.FGACCESSTYPE = 5 THEN ADUSER.IDUSER WHEN MACCESS.FGACCESSTYPE = 6 THEN ADALLUSERS.IDUSER END AS IDUSER,
   MACCESS.FGPERMISSION
   FROM STSCMETRIC
   INNER JOIN STSCMETRICACCESS MACCESS ON(MACCESS.CDSCMETRIC = STSCMETRIC.CDSCMETRIC AND MACCESS.CDSCORECARD = STSCMETRIC.CDSCORECARD AND MACCESS.CDREVISION = STSCMETRIC.CDREVISION AND MACCESS.FGACCESSACTUAL = 1) /* NOTE1 */
   INNER JOIN STSCORECARD ON(STSCORECARD.CDSCORECARD = STSCMETRIC.CDSCORECARD AND STSCORECARD.CDREVISION = STSCMETRIC.CDREVISION AND STSCORECARD.FGCURRENT = 1 AND STSCORECARD.FGSTATUS = 1) /* NOTE2 */
   INNER JOIN STMETRIC ON (STMETRIC.CDMETRIC = STSCMETRIC.CDMETRIC AND STMETRIC.FGENABLED = 1) /* NOTE3 */
   LEFT OUTER JOIN ADTEAMUSER ADTU ON(ADTU.CDTEAM = MACCESS.CDTEAM AND MACCESS.FGACCESSTYPE = 1) /* NOTE4 */
   LEFT OUTER JOIN ADUSER ADUTEAM ON(ADUTEAM.CDUSER = ADTU.CDUSER)
   LEFT OUTER JOIN ADUSERDEPTPOS DEPARTMENT ON(DEPARTMENT.CDDEPARTMENT = MACCESS.CDDEPARTMENT AND MACCESS.FGACCESSTYPE = 2) /* NOTE5 */
   LEFT OUTER JOIN ADUSER ADUDEPART ON(ADUDEPART.CDUSER = DEPARTMENT.CDUSER)
   LEFT OUTER JOIN ADUSERDEPTPOS DEPTPOS ON(DEPTPOS.CDDEPARTMENT = MACCESS.CDDEPARTMENT AND DEPTPOS.CDDEPARTMENT = MACCESS.CDDEPARTMENT AND MACCESS.FGACCESSTYPE = 3) /* NOTE6 */
   LEFT OUTER JOIN ADUSER ADUDEPTPOS ON(DEPTPOS.CDUSER = ADUDEPTPOS.CDUSER)
   LEFT OUTER JOIN ADUSERDEPTPOS POSITION ON(POSITION.CDPOSITION = MACCESS.CDPOSITION AND MACCESS.FGACCESSTYPE = 4) /* NOTE7 */
   LEFT OUTER JOIN ADUSER ADUPOS ON(POSITION.CDUSER = ADUPOS.CDUSER)
   LEFT OUTER JOIN ADUSER ON(ADUSER.CDUSER = MACCESS.CDUSER AND MACCESS.FGACCESSTYPE = 5) /* NOTE8 */
   LEFT OUTER JOIN ADALLUSERS ON(MACCESS.FGACCESSTYPE = 6) /* NOTE9 */
   WHERE STSCMETRIC.DTDUEMEASURE < CURRENT_DATE /* NOTE10 */
   AND STSCMETRIC.FGMETRICENABLED = 1 /* NOTE11 */
   AND STSCMETRIC.FGTODOTASK = 1 /* NOTE12 */
) SUBQUERY
WHERE SUBQUERY.IDUSER IS NOT NULL
GROUP BY SUBQUERY.IDUSER, SUBQUERY.IDSCMETRIC, SUBQUERY.NMMETRIC, SUBQUERY.IDSCORECARD, SUBQUERY.NMSCORECARD, SUBQUERY.FGACCESSTYPE, SUBQUERY.FGPERMISSION
HAVING MAX(SUBQUERY.FGPERMISSION) = 1 /* NOTE13 */

Postgres

SELECT
   SUBQUERY.IDUSER AS 'User ID',
   SUBQUERY.IDSCMETRIC AS 'Scorecard indicator ID',
   SUBQUERY.NMMETRIC AS 'Indicator name',
   SUBQUERY.IDSCORECARD AS 'Scorecard ID',
   SUBQUERY.NMSCORECARD AS 'Scorecard name',
   SUBQUERY.FGACCESSTYPE AS 'Access type',
   SUBQUERY.FGPERMISSION AS 'Permission'
FROM (
   SELECT
   STSCMETRIC.IDSCMETRIC,
   STMETRIC.NMMETRIC,
   STSCORECARD.IDSCORECARD,
   STSCORECARD.NMSCORECARD,
   CASE WHEN ACCESS.FGACCESSTYPE = 1 THEN 'Team' WHEN ACCESS.FGACCESSTYPE = 2 THEN 'Department' WHEN ACCESS.FGACCESSTYPE = 3 THEN 'Department/Position' WHEN ACCESS.FGACCESSTYPE = 4 THEN 'Position' WHEN ACCESS.FGACCESSTYPE = 5 THEN 'User' WHEN ACCESS.FGACCESSTYPE = 6 THEN 'All' END AS FGACCESSTYPE,
   CASE WHEN ACCESS.FGACCESSTYPE = 1 THEN ADUTEAM.IDUSER WHEN ACCESS.FGACCESSTYPE = 2 THEN ADUDEPART.IDUSER WHEN ACCESS.FGACCESSTYPE = 3 THEN ADUDEPTPOS.IDUSER WHEN ACCESS.FGACCESSTYPE = 4 THEN ADUPOS.IDUSER WHEN ACCESS.FGACCESSTYPE = 5 THEN ADUSER.IDUSER WHEN ACCESS.FGACCESSTYPE = 6 THEN ADALLUSERS.IDUSER END AS IDUSER,
   ACCESS.FGPERMISSION
   FROM STSCMETRIC
   INNER JOIN STSCMETRICACCESS ACCESS ON(ACCESS.CDSCMETRIC = STSCMETRIC.CDSCMETRIC AND ACCESS.CDSCORECARD = STSCMETRIC.CDSCORECARD AND ACCESS.CDREVISION = STSCMETRIC.CDREVISION AND ACCESS.FGACCESSACTUAL = 1) /* NOTE1 */
   INNER JOIN STSCORECARD ON(STSCORECARD.CDSCORECARD = STSCMETRIC.CDSCORECARD AND STSCORECARD.CDREVISION = STSCMETRIC.CDREVISION AND STSCORECARD.FGCURRENT = 1 AND STSCORECARD.FGSTATUS = 1) /* NOTE2 */
     INNER JOIN STMETRIC ON (STMETRIC.CDMETRIC = STSCMETRIC.CDMETRIC AND STMETRIC.FGENABLED = 1) /* NOTE3 */
     LEFT OUTER JOIN ADTEAMUSER ADTU ON(ADTU.CDTEAM = ACCESS.CDTEAM AND ACCESS.FGACCESSTYPE = 1) /* NOTE4 */
     LEFT OUTER JOIN ADUSER ADUTEAM ON(ADUTEAM.CDUSER = ADTU.CDUSER)
     LEFT OUTER JOIN ADUSERDEPTPOS DEPARTMENT ON(DEPARTMENT.CDDEPARTMENT = ACCESS.CDDEPARTMENT AND ACCESS.FGACCESSTYPE = 2) /* NOTE5 */
     LEFT OUTER JOIN ADUSER ADUDEPART ON(ADUDEPART.CDUSER = DEPARTMENT.CDUSER)
     LEFT OUTER JOIN ADUSERDEPTPOS DEPTPOS ON(DEPTPOS.CDDEPARTMENT = ACCESS.CDDEPARTMENT AND DEPTPOS.CDDEPARTMENT = ACCESS.CDDEPARTMENT AND ACCESS.FGACCESSTYPE = 3) /* NOTE6 */
     LEFT OUTER JOIN ADUSER ADUDEPTPOS ON(DEPTPOS.CDUSER = ADUDEPTPOS.CDUSER)
     LEFT OUTER JOIN ADUSERDEPTPOS POSITION ON(POSITION.CDPOSITION = ACCESS.CDPOSITION AND ACCESS.FGACCESSTYPE = 4) /* NOTE7 */
     LEFT OUTER JOIN ADUSER ADUPOS ON(POSITION.CDUSER = ADUPOS.CDUSER)
     LEFT OUTER JOIN ADUSER ON(ADUSER.CDUSER = ACCESS.CDUSER AND ACCESS.FGACCESSTYPE = 5) /* NOTE8 */
     LEFT OUTER JOIN ADALLUSERS ON(ACCESS.FGACCESSTYPE = 6) /* NOTE9 */
   WHERE STSCMETRIC.DTDUEMEASURE < CURRENT_DATE /* NOTE10 */
     AND STSCMETRIC.FGMETRICENABLED = 1 /* NOTE11 */
     AND STSCMETRIC.FGTODOTASK = 1 /* NOTE12 */
) SUBQUERY
WHERE SUBQUERY.IDUSER IS NOT NULL
GROUP BY SUBQUERY.IDUSER, SUBQUERY.IDSCMETRIC, SUBQUERY.NMMETRIC, SUBQUERY.IDSCORECARD, SUBQUERY.NMSCORECARD, SUBQUERY.FGACCESSTYPE, SUBQUERY.FGPERMISSION
HAVING MAX(SUBQUERY.FGPERMISSION) = 1 /* NOTE13 */

SQL Server

SELECT
   SUBQUERY.IDUSER AS 'User ID',
   SUBQUERY.IDSCMETRIC AS 'Scorecard indicator ID',
   SUBQUERY.NMMETRIC AS 'Indicator name',
   SUBQUERY.IDSCORECARD AS 'Scorecard ID',
   SUBQUERY.NMSCORECARD AS 'Scorecard name',
   SUBQUERY.FGACCESSTYPE AS 'Access type',
   SUBQUERY.FGPERMISSION AS 'Permission'
FROM (
   SELECT
   STSCMETRIC.IDSCMETRIC,
   STMETRIC.NMMETRIC,
   STSCORECARD.IDSCORECARD,
   STSCORECARD.NMSCORECARD,
   CASE WHEN ACCESS.FGACCESSTYPE = 1 THEN 'Team' WHEN ACCESS.FGACCESSTYPE = 2 THEN 'Department' WHEN ACCESS.FGACCESSTYPE = 3 THEN 'Department/Position' WHEN ACCESS.FGACCESSTYPE = 4 THEN 'Position' WHEN ACCESS.FGACCESSTYPE = 5 THEN 'User' WHEN ACCESS.FGACCESSTYPE = 6 THEN 'All' END AS FGACCESSTYPE,
   CASE WHEN ACCESS.FGACCESSTYPE = 1 THEN ADUTEAM.IDUSER WHEN ACCESS.FGACCESSTYPE = 2 THEN ADUDEPART.IDUSER WHEN ACCESS.FGACCESSTYPE = 3 THEN ADUDEPTPOS.IDUSER WHEN ACCESS.FGACCESSTYPE = 4 THEN ADUPOS.IDUSER WHEN ACCESS.FGACCESSTYPE = 5 THEN ADUSER.IDUSER WHEN ACCESS.FGACCESSTYPE = 6 THEN ADALLUSERS.IDUSER END AS IDUSER,
   ACCESS.FGPERMISSION
   FROM STSCMETRIC
   INNER JOIN STSCMETRICACCESS ACCESS ON(ACCESS.CDSCMETRIC = STSCMETRIC.CDSCMETRIC AND ACCESS.CDSCORECARD = STSCMETRIC.CDSCORECARD AND ACCESS.CDREVISION = STSCMETRIC.CDREVISION AND ACCESS.FGACCESSACTUAL = 1) /* NOTE1 */
   INNER JOIN STSCORECARD ON(STSCORECARD.CDSCORECARD = STSCMETRIC.CDSCORECARD AND STSCORECARD.CDREVISION = STSCMETRIC.CDREVISION AND STSCORECARD.FGCURRENT = 1 AND STSCORECARD.FGSTATUS = 1) /* NOTE2 */
   INNER JOIN STMETRIC ON(STMETRIC.CDMETRIC = STSCMETRIC.CDMETRIC AND STMETRIC.FGENABLED = 1) /* NOTE3 */
   LEFT OUTER JOIN ADTEAMUSER ADTU ON(ADTU.CDTEAM = ACCESS.CDTEAM AND ACCESS.FGACCESSTYPE = 1) /* NOTE4 */
   LEFT OUTER JOIN ADUSER ADUTEAM ON(ADUTEAM.CDUSER = ADTU.CDUSER)LEFT OUTER JOIN ADUSERDEPTPOS DEPARTMENT ON(DEPARTMENT.CDDEPARTMENT = ACCESS.CDDEPARTMENT AND ACCESS.FGACCESSTYPE = 2) /* NOTE5 */
   LEFT OUTER JOIN ADUSER ADUDEPART ON(ADUDEPART.CDUSER = DEPARTMENT.CDUSER)LEFT OUTER JOIN ADUSERDEPTPOS DEPTPOS ON(DEPTPOS.CDDEPARTMENT = ACCESS.CDDEPARTMENT AND DEPTPOS.CDDEPARTMENT = ACCESS.CDDEPARTMENT AND ACCESS.FGACCESSTYPE = 3) /* NOTE6 */
   LEFT OUTER JOIN ADUSER ADUDEPTPOS ON(DEPTPOS.CDUSER = ADUDEPTPOS.CDUSER)LEFT OUTER JOIN ADUSERDEPTPOS POSITION ON(POSITION.CDPOSITION = ACCESS.CDPOSITION AND ACCESS.FGACCESSTYPE = 4) /* NOTE7 */
   LEFT OUTER JOIN ADUSER ADUPOS ON(POSITION.CDUSER = ADUPOS.CDUSER)LEFT OUTER JOIN ADUSER ON(ADUSER.CDUSER = ACCESS.CDUSER AND ACCESS.FGACCESSTYPE = 5) /* NOTE8 */
   LEFT OUTER JOIN ADALLUSERS ON(ACCESS.FGACCESSTYPE = 6) /* NOTE9 */
   WHERE STSCMETRIC.DTDUEMEASURE < GETDATE() /* NOTE10 */
     AND STSCMETRIC.FGMETRICENABLED = 1 /* NOTE11 */
     AND STSCMETRIC.FGTODOTASK = 1 /* NOTE12 */
) SUBQUERY
WHERE SUBQUERY.IDUSER IS NOT NULL
GROUP BY SUBQUERY.IDUSER, SUBQUERY.IDSCMETRIC, SUBQUERY.NMMETRIC, SUBQUERY.IDSCORECARD, SUBQUERY.NMSCORECARD, SUBQUERY.FGACCESSTYPE, SUBQUERY.FGPERMISSION
HAVING MAX(SUBQUERY.FGPERMISSION) = 1 /* NOTE13 */

Notes

▪1: The FGACCESSACTUAL field equal to 1 determines whether the user/team/department/position has permission to add measurement values to the indicator.

▪2: The FGSTATUS field equal to 1 and the FGCURRENT field equal to 1 determine, respectively, the scorecard status (1-Enabled) and the scorecard validity (1-Released).

▪3: The FGENABLED field equal to 1 determines whether the indicator in the indicator record menu is enabled.

▪4: The FGACCESSTYPE field equal to 1 determines whether the access type in the indicator security is by team.

▪5: The FGACCESSTYPE field equal to 2 determines whether the access type in the indicator security is by department.

▪6: The FGACCESSTYPE field equal to 3 determines whether the access type in the indicator security is by department/position.

▪7: The FGACCESSTYPE field equal to 4 determines whether the access type in the indicator security is by position.

▪8: The FGACCESSTYPE field equal to 5 determines whether the access type in the indicator security is by user.

▪9: The FGACCESSTYPE field equal to 6 determines whether the access type in the indicator security is for all.

▪10: The DTDUEMEASURE field determines the validity date of the next measurement to be entered,. Thus, it is used in the WHERE condition, which uses the (CURRENT_DATE|GETDATE) function to return the current date and check whether the indicator is past due.

▪11: The FGENABLED field equal to 1 determines whether the scorecard indicator is enabled in the scorecard structure.

▪12: The FGTODOTASK field equal to 1 determines whether the indicator is generating a target/measurement addition task.