SQL Queries - CAST Central Base - Queries on applications - How to get the Critical and Non-Critical Violations for an Application

SELECT DISTINCT zz.F_SNAPSHOT_ID                         ,
                zz.F_APP_ID                              ,
                zz.F_B_CRITERION_ID                      ,
                m1.metric_description AS B_CRITERION_name,
                zz.F_T_CRITERION_ID                      ,
                m2.metric_description AS T_CRITERION_name,
                zz.F_M_WEIGHT                            ,
                zz.F_M_CRIT                              ,
                m3.metric_description AS QR_name         ,
                zz.F_METRIC_ID                           ,
                zz.F_OBJECT_ID                           ,
                zz.F_OBJECT_FULL_NAME                    ,
                zz.F_METRIC_NUM_VALUE                    ,
                zz.F_STATUS                              ,
                zz.F_JUSTIFY                             ,
                zz.ACTION_DEF                            ,
                zz.F_PRIORITY                            ,
                zz.F_ACCESS_ACTION                       ,
                zz.F_ACCESS_EXCEPT
FROM            ( SELECT         cd1.SNAPSHOT_ID   AS F_SNAPSHOT_ID   ,
                                cpt.SYST_ID        AS F_SYST_ID       ,
                                cpt.APP_ID         AS F_APP_ID        ,
                                cqt.B_CRITERION_ID AS F_B_CRITERION_ID,
                                cqt.T_CRITERION_ID AS F_T_CRITERION_ID,
                                cqt.M_WEIGHT       AS F_M_WEIGHT      ,
                                cqt.M_CRIT         AS F_M_CRIT        ,
                                cqt.METRIC_ID      AS F_METRIC_ID     ,
                                cd1.OBJECT_ID      AS F_OBJECT_ID     ,
                                (SELECT dso.OBJECT_FULL_NAME
                                FROM    DSS_OBJECTS dso
                                WHERE   dso.OBJECT_ID = cd1.OBJECT_ID
                                )
                                                                  AS F_OBJECT_FULL_NAME,
                                COALESCE(cpxl.METRIC_NUM_VALUE,0) AS F_METRIC_NUM_VALUE, (
                                CASE
                                                WHEN Ocur.OBJECT_CHECKSUM IS NULL
                                                THEN 2
                                                WHEN Oprev.OBJECT_CHECKSUM IS NULL
                                                THEN 1
                                                WHEN Ocur.OBJECT_CHECKSUM = Oprev.OBJECT_CHECKSUM
                                                THEN 0
                                                ELSE 3
                                END)                         AS F_STATUS       ,
                                COALESCE(OE.JUSTIFY,'NA')    AS F_JUSTIFY      ,
                                COALESCE(AP.ACTION_DEF,'NA') AS ACTION_DEF     ,
                                COALESCE(AP.PRIORITY,2)      AS F_PRIORITY     ,
                                doe.ACCESS_ACTION            AS F_ACCESS_ACTION,
                                doe.ACCESS_EXCEPT            AS F_ACCESS_EXCEPT
                FROM            ( SELECT DSSD.OBJECT_ID    AS      OBJECT_ID    ,
                                        DSSS.SNAPSHOT_DATE AS      SNAPSHOT_DATE,
                                        DSSD.SNAPSHOT_ID   AS      SNAPSHOT_ID  ,
                                        DSSD.DIAG_ID       AS      DIAG_ID      ,
                                        DSSD.CONTEXT_ID    AS      CONTEXT_ID
                                FROM    DSS_DIAGDETAILS DSSD
                                        JOIN DSS_SNAPSHOTS DSSS
                                        ON      DSSS.SNAPSHOT_ID = DSSD.SNAPSHOT_ID
                                )
                                cd1
                                JOIN DSS_QUALITY_TREE cqt
                                ON              (
                                                                cqt.METRIC_ID = cd1.DIAG_ID
                                                )
                                JOIN DSS_PORTF_TREE cpt
                                ON              (
                                                                cd1.CONTEXT_ID  = cpt.MODULE_ID
                                                AND             cpt.SNAPSHOT_ID = cd1.SNAPSHOT_ID
                                                AND             cpt.APP_ID      = <app_id>     -- here 3
                                                AND             cpt.SNAPSHOT_ID = <snapshot_id>-- snapshot id
                                                )
                                JOIN DSS_OBJECTS_EX doe
                                ON              (
                                                                doe.OBJECT_ID   = cpt.APP_ID
                                                AND             doe.ACCESS_LIST = 1
                                                )
                                LEFT JOIN
                                                (SELECT dmr.METRIC_NUM_VALUE,
                                                        OBJECT_ID           ,
                                                        SNAPSHOT_ID
                                                FROM    DSS_METRIC_RESULTS dmr
                                                WHERE   dmr.METRIC_ID = 65005
                                                AND     snapshot_id   = <snapshot_id>
                                                )
                                                cpxl
                                ON              cpxl.OBJECT_ID   = cd1.OBJECT_ID
                                AND             cpxl.SNAPSHOT_ID = cd1.SNAPSHOT_ID
                                LEFT JOIN DSS_OBJECT_EXCEPTIONS OE
                                ON              (
                                                                OE.OBJECT_ID    = cd1.OBJECT_ID
                                                AND             OE.METRIC_ID    = cqt.METRIC_ID
                                                AND             cd1.SNAPSHOT_ID < OE.LAST_SNAPSHOT_ID
                                                )
                                CROSS JOIN VIEWER_DUAL VD
                                LEFT JOIN VIEWER_ACTION_PLANS AP
                                ON              (
                                                                AP.OBJECT_ID            = cd1.OBJECT_ID
                                                AND             AP.METRIC_ID            = cqt.METRIC_ID
                                                AND             AP.FIRST_SNAPSHOT_DATE <= cd1.SNAPSHOT_DATE
                                                AND             AP.LAST_SNAPSHOT_DATE   = VD.OMEGA_DATE
                                                )
                                JOIN ADG_DELTA_SNAPSHOTS d
                                ON              (
                                                                d.APPLICATION_ID = cpt.APP_ID
                                                AND             d.SNAPSHOT_ID    = cd1.SNAPSHOT_ID
                                                )
                                LEFT OUTER JOIN DSS_OBJECT_INFO Ocur
                                ON              (
                                                                Ocur.SNAPSHOT_ID = d.SNAPSHOT_ID
                                                AND             Ocur.OBJECT_ID   = cd1.OBJECT_ID
                                                AND             Ocur.SNAPSHOT_ID = <snapshot_id>
                                                )
                                LEFT OUTER JOIN DSS_OBJECT_INFO Oprev
                                ON              (
                                                                Oprev.SNAPSHOT_ID = d.PREV_SNAPSHOT_ID
                                                AND             Oprev.OBJECT_ID   = cd1.OBJECT_ID
                                                )
                )
                zz
                JOIN dss_metric_descriptions m1
                ON              m1.metric_id = zz.F_B_CRITERION_ID
                JOIN dss_metric_descriptions m2
                ON              m2.metric_id = zz.F_T_CRITERION_ID
                JOIN dss_metric_descriptions m3
                ON              m3.metric_id = zz.F_METRIC_ID
WHERE           1                            =1
AND             m1.language                  = 'ENGLISH'
AND             m1.description_type_id       = 0
AND             m2.language                  = 'ENGLISH'
AND             m2.description_type_id       = 0
AND             m3.language                  = 'ENGLISH'
AND             m3.description_type_id       = 0
ORDER BY        6 DESC,
                1 DESC,
                4 DESC,
                9 DESC