Overview
This command will list all current plans with a released revision; it will also display the parties responsible for the plan and the number of risks and controls associated with it.
Related tables
Table
|
Description
|
Primary key (PK)
|
Connection tables
|
GNGENTYPE
|
Generic type
|
CDGENTYPE
|
GNELETRONICFILECFG
GNEVAL
GNFAVORITE
GNREVCONFIG
GNMASK
GNTYPEROLE
ADCHECKLIST
GNASSOC
GNACTIVITYTIMECFG
GNMASK
GNTYPEROLE
GNTRANSLATION
GNSECURITY
|
RIPLANTYPE
|
Plan type record
|
CDPLANTYPE
|
GNGENTYPE
GNEVAL
GNTYPEROLE
RIFORMULARESIDEVAL
|
RIPLAN
|
Plan record
|
CDPLAN
CDREVISION
|
RIPLANTYPE
GNMASK
GNASSOC
GNEVALREVISION
GNFAVORITE
GNTYPEROLE
GNTOOLSANALISYS
GNREVISION
ADALLUSERS
ADTEAM
RIPROFILE
ADDEPARTMENT
ADAPPROVALROUTE
RIFORMULARESIDEVAL
|
GNREVISION
|
Revisions
|
CDREVISION
|
GNASSOC
GNACTIONASSOC
GNREVCONFIG
GNREASON
GNREVISIONSTATUS
ADALLUSERS
|
ADUSER
|
User record
|
CDUSER
|
|
ADTEAM
|
Team record
|
CDTEAM
|
GNPERMISSION
ADAUTHLDAPCONFIG
GNMAPPING
|
Oracle/Postgres/SQL Server
SELECT GNGENTYPE.IDGENTYPE,
GNGENTYPE.NMGENTYPE,
RIPLAN.IDPLAN,
RIPLAN.NMPLAN,
ADUSER.NMUSER,
ADTEAM.NMTEAM,
GNREVISION.IDREVISION,
GNREVISION.FGSTATUS,
(SELECT COUNT(1)
FROM GNASSOCRISKANA
WHERE GNASSOCRISKANA.CDREVISION = RIPLAN.CDREVISION
) AS QTDRISK,
(SELECT COUNT(1)
FROM GNASSOCCONTROLANA
WHERE GNASSOCCONTROLANA.CDREVISION = RIPLAN.CDREVISION
) AS QTDCONTROL,
RIPLAN.CDISOSYSTEM
FROM GNGENTYPE
INNER JOIN RIPLANTYPE ON RIPLANTYPE.CDPLANTYPE = GNGENTYPE.CDGENTYPE
INNER JOIN RIPLAN ON RIPLAN.CDGENTYPE = RIPLANTYPE.CDPLANTYPE
INNER JOIN GNREVISION ON RIPLAN.CDREVISION = GNREVISION.CDREVISION
LEFT OUTER JOIN ADUSER ON ADUSER.CDUSER = RIPLAN.CDPLANUSERRESP
LEFT OUTER JOIN ADTEAM ON ADTEAM.CDTEAM = RIPLAN.CDPLANTEAMRESP
WHERE RIPLAN.FGTEMPLATE = 2 /* NOTE1 */
AND RIPLAN.FGSTATUS = 1 /* NOTE2 */
AND RIPLAN.FGCURRENT = 1 /* NOTE3 */
AND GNREVISION.FGSTATUS = 6 /* NOTE4 */
|
Notes
▪1: The FGTEMPLATE field of the RIPLAN table informs whether the plan is a template. If it is, the value will be equal to 1; if it is not, it will be equal to 2. ▪2: The FGSTATUS field of the RIPLAN table determines whether the plan is enabled (value = 1) or disabled (value = 3). ▪3: The FGCURRENT field of the RIPLAN table determines whether the risk plan is enabled and is a revision of the current plan. Current and released plans have a value equal to 1; plans that are in the draft step or are obsolete have a value equal to 2. ▪4: The FGSTATUS field of the GNREVISION table indicates the revision status, defined as: 1 = Draft; 2 = Review; 3 = Approval; 4 = Release; 5 = Released; 6 = Closed.
Fields used on the view screen
#
|
Field ID
|
Type
|
Description
|
1
|
IDGENTYPE
|
VARCHAR(50)
|
Plan type ID #
|
2
|
NMGENTYPE
|
VARCHAR(255)
|
Plan type name
|
3
|
IDPLAN
|
VARCHAR(50)
|
Plan ID #
|
4
|
NMPLAN
|
VARCHAR(255)
|
Plan name
|
5
|
NMUSER
|
VARCHAR(255)
|
Name of the user responsible for the plan
|
6
|
NMTEAM
|
VARCHAR(255)
|
Name of the team responsible for the plan
|
7
|
IDREVISION
|
VARCHAR(50)
|
Plan revision
|
8
|
CDISOSYSTEM
|
NUMERIC(10)
|
Code of the component that is part of the plan scope:
215- SoftExpert Risk
109- SoftExpert Asset
101- SoftExpert Process
138- SoftExpert Performance
41- SoftExpert Project
|
9
|
FGSTATUS
|
NUMERIC(2)
|
Plan revision status:
1 - Draft
2 - Review
3 - Approval
4 - Release
5 - Released
6 - Closed
|
10
|
QTDRISK
|
NUMERIC(10)
|
Number of risk analyses associated with the plan
|
11
|
QTDCONTROL
|
NUMERIC(10)
|
Number of control analyses associated with the plan
|
|
