APPS.AMW_ORG_CERT_EVAL_SUM_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 29

SELECT distinct CERTIFICATION_ID
FROM AMW_CERTIFICATION_VL
WHERE OBJECT_TYPE = 'PROCESS'
AND certification_status in ('ACTIVE','DRAFT');

Line: 79

SELECT DISTINCT organization_id
FROM 	amw_execution_scope
WHERE	entity_type = 'BUSIPROC_CERTIFICATION'
AND 	entity_id = p_certification_id
AND 	organization_id is not null;

Line: 127

SELECT opinion.opinion_id
FROM   amw_opinions_v opinion
WHERE opinion.pk1_value = p_org_id
AND   opinion.pk2_value = p_certification_id
AND   opinion.opinion_type_code = 'CERTIFICATION'
AND   opinion.object_name = 'AMW_ORGANIZATION';

Line: 136

SELECT  opinion.opinion_id
FROM    amw_opinions_v opinion
WHERE	(opinion.authored_date IN (SELECT MAX(opinion2.authored_date)
				   FROM amw_opinions_v opinion2
				   WHERE opinion2.object_opinion_type_id = opinion.object_opinion_type_id
				   AND   opinion2.pk1_value = opinion.pk1_value
				   )
	)
AND	opinion.pk1_value = p_org_id
AND	opinion.opinion_type_code = 'EVALUATION'
AND	opinion.object_name = 'AMW_ORGANIZATION';

Line: 150

SELECT  opinion.opinion_log_id
FROM    amw_opinions_log_v opinion
WHERE	(opinion.authored_date IN (SELECT MAX(opinion2.authored_date)
				   FROM amw_opinions opinion2
				   WHERE opinion2.object_opinion_type_id = opinion.object_opinion_type_id
				   AND   opinion2.pk1_value = opinion.pk1_value)
	)
AND	opinion.pk1_value = p_org_id
AND	opinion.opinion_type_code = 'EVALUATION'
AND	opinion.object_name = 'AMW_ORGANIZATION';

Line: 163

SELECT count(1)
FROM 	(SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
	FROM amw_risk_associations ara,amw_opinions_v aov
	WHERE ara.object_type 	  = 'BUSIPROC_CERTIFICATION'
	AND ara.pk1 		  = p_certification_id
	AND ara.pk2               = p_org_id
	AND aov.object_name 	  = 'AMW_ORG_PROCESS_RISK'
	AND aov.opinion_type_code = 'EVALUATION'
	AND aov.pk1_value 	  = ara.risk_id
	AND aov.pk3_value 	  = p_org_id
	AND NVL(aov.pk4_value,-1)
				  = NVL(ara.pk3, -1) --process_id
	AND aov.audit_result_code <> 'EFFECTIVE'
	AND aov.authored_date = (SELECT MAX(aov2.authored_date)
				  FROM amw_opinions aov2
				  WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
				  AND NVL(aov2.pk4_value, -1)
						= NVL(aov.pk4_value, -1)
				  AND aov2.pk3_value = aov.pk3_value
				  AND aov2.pk1_value = aov.pk1_value)

	);

Line: 188

SELECT count(1)
FROM 	(SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
	FROM amw_risk_associations ara,amw_opinions_v aov
	WHERE ara.object_type 	  = 'BUSIPROC_CERTIFICATION'
	AND ara.pk1 		  = p_certification_id
	AND ara.pk2               = p_org_id
	AND aov.object_name 	  = 'AMW_ORG_PROCESS_RISK'
	AND aov.opinion_type_code = 'EVALUATION'
	AND aov.pk1_value 	  = ara.risk_id
	AND aov.pk3_value 	  = ara.pk2 --org_id
	AND NVL(aov.pk4_value, -1)
	    	  = NVL(ara.pk3, -1) --process_id
	AND aov.audit_result_code IS NOT NULL
	);

Line: 207

SELECT count(1) from (
select distinct ara.pk3, ara.risk_id
FROM amw_risk_associations ara
WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
AND ara.pk1 		  = p_certification_id
AND ara.pk2           = p_org_id);

Line: 216

SELECT count(1)
---07.05.2005 npanandi: changed below query to have a distinct on
---certificationId, organizationId, controlId
---instead of having a distinct on
---certificationId, organizationId, processId, riskId, controlId
---FROM 	(SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.pk4 risk_id, aca.control_id
FROM 	(SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
	FROM amw_control_associations aca,amw_opinions_v aov
	WHERE aca.object_type     = 'BUSIPROC_CERTIFICATION'
	AND aca.pk1 		  = p_certification_id
	AND aca.pk2               = p_org_id
	AND aov.object_name       = 'AMW_ORG_CONTROL'
	AND aov.opinion_type_code = 'EVALUATION'
	AND aov.pk1_value 	  = aca.control_id
	AND aov.pk3_value 	  = aca.pk2
	AND aov.audit_result_code <> 'EFFECTIVE'
	AND aov.authored_date = (SELECT MAX(aov2.authored_date)
				FROM amw_opinions aov2
				WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
				AND aov2.pk3_value = aov.pk3_value
				AND aov2.pk1_value = aov.pk1_value)
	);

Line: 242

SELECT count(1)
---07.05.2005 npanandi: changed below query to have a distinct on
---certificationId, organizationId, controlId
---instead of having a distinct on
---certificationId, organizationId, processId, riskId, controlId
---FROM 	(SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.pk4 risk_id, aca.control_id
FROM 	(SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
	FROM amw_control_associations aca,amw_opinions_v aov
	WHERE aca.object_type     = 'BUSIPROC_CERTIFICATION'
	AND aca.pk1 		  = p_certification_id
	AND aca.pk2               = p_org_id
	AND aov.object_name       = 'AMW_ORG_CONTROL'
	AND aov.opinion_type_code = 'EVALUATION'
	AND aov.pk1_value 	  = aca.control_id
	AND aov.pk3_value 	  = aca.pk2
	AND aov.audit_result_code IS NOT NULL
	);

Line: 262

SELECT count(DISTINCT aca.control_id)
FROM amw_control_associations aca
WHERE aca.object_type     = 'BUSIPROC_CERTIFICATION'
AND aca.pk1 		  = p_certification_id
AND aca.pk2               = p_org_id;

Line: 270

SELECT count(distinct process_id)
FROM   amw_execution_scope
WHERE  organization_id   = p_org_id
AND    entity_id         = p_certification_id
---07.05.2005 npanandi: added entity_type below, bugfix for bug 4471783
AND    entity_type       = 'BUSIPROC_CERTIFICATION';

Line: 281

SELECT count(DISTINCT process_id)
FROM   amw_execution_scope amw_exec
WHERE EXISTS (SELECT  opinion.opinion_id
	FROM amw_opinions_v opinion
	WHERE opinion.pk1_value = amw_exec.process_id
	AND   opinion.pk3_value = p_org_id
	AND   opinion.pk2_value = p_certification_id
	AND   opinion.opinion_type_code = 'CERTIFICATION'
	AND   opinion.object_name = 'AMW_ORG_PROCESS'
);

Line: 296

SELECT count(DISTINCT process_id)
FROM   amw_execution_scope amw_exec
WHERE EXISTS (SELECT  opinion.opinion_id
	FROM amw_opinions_v opinion
	WHERE opinion.pk1_value = amw_exec.process_id
	AND   opinion.pk3_value = p_org_id
	AND   opinion.pk2_value = p_certification_id
	AND   opinion.opinion_type_code = 'CERTIFICATION'
	AND   opinion.object_name = 'AMW_ORG_PROCESS'
	AND   opinion.audit_result_code <> 'EFFECTIVE'
);

Line: 314

SELECT count(DISTINCT process_id)
FROM   amw_execution_scope amw_exec
WHERE amw_exec.entity_type = 'BUSIPROC_CERTIFICATION'
AND amw_exec.entity_id = p_certification_id
AND EXISTS (SELECT  opinion.opinion_id
	FROM amw_opinions_v opinion
	WHERE opinion.pk1_value = amw_exec.process_id
	--AND   opinion.pk2_value = p_certification_id
	AND   opinion.pk3_value = p_org_id
	AND   opinion.opinion_type_code = 'EVALUATION'
	AND   opinion.object_name = 'AMW_ORG_PROCESS'
	AND   opinion.audit_result_code IS NOT NULL
	AND    opinion.authored_date = (SELECT MAX(aov2.authored_date)
                                FROM amw_opinions aov2
                                WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
                                AND aov2.pk3_value = opinion.pk3_value
                                AND aov2.pk1_value = opinion.pk1_value)
);

Line: 339

SELECT count(DISTINCT process_id)
FROM   amw_execution_scope amw_exec
WHERE amw_exec.entity_type = 'BUSIPROC_CERTIFICATION'
AND amw_exec.entity_id = p_certification_id
AND EXISTS (SELECT  opinion.opinion_id
	FROM amw_opinions_v opinion
	WHERE opinion.pk1_value = amw_exec.process_id
	--AND   opinion.pk2_value = p_certification_id
	AND   opinion.pk3_value = p_org_id
	AND   opinion.opinion_type_code = 'EVALUATION'
	AND   opinion.object_name = 'AMW_ORG_PROCESS'
	AND   opinion.audit_result_code <> 'EFFECTIVE'
	AND    opinion.authored_date = (SELECT MAX(aov2.authored_date)
                                FROM amw_opinions aov2
                                WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
                                AND aov2.pk3_value = opinion.pk3_value
                                AND aov2.pk1_value = opinion.pk1_value)
);

Line: 360

SELECT count(distinct object_id)
FROM   amw_entity_hierarchies ent
WHERE EXISTS (SELECT  opinion.opinion_id
		FROM  amw_opinions_v opinion
		WHERE opinion.pk1_value = p_org_id
		AND   opinion.pk2_value = p_certification_id
		AND   opinion.opinion_type_code = 'CERTIFICATION'
		AND   opinion.object_name = 'AMW_ORGANIZATION'
	     )
START WITH parent_object_id = p_org_id
       AND parent_object_type = 'ORG'
       AND entity_id = p_certification_id
       AND entity_type = 'BUSIPROC_CERTIFICATION'
CONNECT BY PRIOR object_id = PRIOR parent_object_id
             AND object_type = PRIOR parent_object_type
	     AND entity_id = PRIOR entity_id
	     AND entity_type = PRIOR entity_type;

Line: 380

SELECT count(distinct object_id)
FROM   amw_entity_hierarchies ent
WHERE EXISTS (SELECT  opinion.opinion_id
		FROM  amw_opinions_v opinion
		WHERE opinion.pk1_value = p_org_id
		AND   opinion.pk2_value = p_certification_id
		AND   opinion.opinion_type_code = 'CERTIFICATION'
		AND   opinion.object_name = 'AMW_ORGANIZATION'
		AND   opinion.audit_result_code <> 'EFFECTIVE'
	     )
START WITH parent_object_id = p_org_id
       AND parent_object_type = 'ORG'
       AND entity_id = p_certification_id
       AND entity_type = 'BUSIPROC_CERTIFICATION'
CONNECT BY PRIOR object_id = PRIOR parent_object_id
             AND object_type = PRIOR parent_object_type
	     AND entity_id = PRIOR entity_id
	     AND entity_type = PRIOR entity_type;

Line: 401

SELECT count(distinct object_id)
FROM   amw_entity_hierarchies ent
START WITH parent_object_id = p_org_id
       AND parent_object_type = 'ORG'
       AND entity_id = p_certification_id
	   ---07.05.2005 npanandi: add entityType, bugfix 4471783
	   and entity_type='BUSIPROC_CERTIFICATION'
CONNECT BY PRIOR object_id = PRIOR parent_object_id
     	     AND object_type = PRIOR parent_object_type
	     AND entity_id = PRIOR entity_id
		 ---07.05.2005 npanandi: add entityType, bugfix 4471783
	     and entity_type=prior entity_type;

Line: 416

SELECT count(distinct aes.process_id)
FROM amw_execution_scope aes
WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
AND aes.level_id = 4
AND aes.parent_process_id = -1
AND aes.entity_id = p_certification_id
AND aes.organization_id = p_org_id;

Line: 426

SELECT count(distinct aes.process_id)
FROM amw_execution_scope aes
WHERE aes.entity_type 	= 'BUSIPROC_CERTIFICATION'
AND aes.level_id 	= 4
AND aes.parent_process_id = -1   --need to verify if this is -2
AND aes.entity_id 	= p_certification_id
AND aes.organization_id = p_org_id
AND NOT EXISTS (SELECT 'Y'
     		FROM AMW_OPINIONS_V aov
    		WHERE aov.object_name = 'AMW_ORG_PROCESS'
    		  AND aov.opinion_type_code = 'CERTIFICATION'
    		  AND aov.pk3_value = aes.organization_id
    		  AND aov.pk2_value = p_certification_id
    		  AND aov.pk1_value = aes.process_id);

Line: 583

	UPDATE AMW_ORG_CERT_EVAL_SUM
	SET certification_opinion_id = l_certification_opinion_id,
	    evaluation_opinion_id    = l_evaluation_opinion_id,
	    evaluation_opinion_log_id= l_evaluation_opinion_log_id,
	    unmitigated_risks        = l_unmitigated_risks,
	    evaluated_risks          = l_evaluated_risks,
	    total_risks              = l_total_risks,
	    ineffective_controls     = l_ineffective_controls,
	    evaluated_controls       = l_evaluated_controls,
	    total_controls           = l_total_controls,
	    processes_certified      = l_processes_certified,
	    evaluated_processes      = l_evaluated_processes,
	    ineff_processes          = l_ineffective_processes,
	    total_processes          = l_processes_total,
	    sub_org_cert   	     = l_sub_orgs,
	    total_sub_org	     = l_all_orgs,
	    top_org_processes	     = l_top_org_processes,
	    top_org_proc_pending_cert= l_top_orgproc_pend_cert,
	    open_findings            = l_open_findings,
	    open_issues		     = l_open_issues,
	    last_update_date 	     = SYSDATE,
	    last_updated_by          = G_USER_ID,
	    last_update_login        = G_LOGIN_ID,
	    SUB_ORG_CERT_ISSUES	     = l_sub_org_cert_issues,
	    PROC_CERT_ISSUES	     = l_proc_cert_issues,
	    INEFF_PROCESSES_PRCNT    =
		decode(l_processes_total, 0, 0, round(l_ineffective_processes/l_processes_total*100)),
	    UNMITIGATED_RISKS_PRCNT  =
	        decode(l_total_risks, 0, 0, round(l_unmitigated_risks/l_total_risks*100)),
	    INEFF_CONTROLS_PRCNT     =
	        decode(l_total_controls, 0, 0, round(l_ineffective_controls/l_total_controls*100))
	WHERE certification_id       = p_certification_id
	AND organization_id          = p_org_id;

Line: 620

		INSERT INTO AMW_ORG_CERT_EVAL_SUM(certification_opinion_id,
						   evaluation_opinion_id,
						   evaluation_opinion_log_id,
						   unmitigated_risks,
						   evaluated_risks,
	    					   total_risks,
	    					   ineffective_controls,
	    					   evaluated_controls,
	    					   total_controls,
						   processes_certified,
						   evaluated_processes,
						   ineff_processes,
						   total_processes,
						   sub_org_cert,
						   total_sub_org,
						   top_org_processes,
		   				   top_org_proc_pending_cert,
						   open_findings,
						   open_issues,
						   certification_id,
						   organization_id,
						   created_by,
						   creation_date,
						   last_updated_by,
						   last_update_date,
						   last_update_login,
						   sub_org_cert_issues,
						   proc_cert_issues,
						   INEFF_PROCESSES_PRCNT,
						   UNMITIGATED_RISKS_PRCNT,
						   INEFF_CONTROLS_PRCNT)

		VALUES (l_certification_opinion_id,
			l_evaluation_opinion_id,
			l_evaluation_opinion_log_id,
			l_unmitigated_risks,
			l_evaluated_risks,
			l_total_risks,
			l_ineffective_controls,
			l_evaluated_controls,
			l_total_controls,
			l_processes_certified,
			l_evaluated_processes,
			l_ineffective_processes,
			l_processes_total,
			l_sub_orgs,
			l_all_orgs,
			l_top_org_processes,
			l_top_orgproc_pend_cert,
			l_open_findings,
			l_open_issues,
			p_certification_id,
			p_org_id,
			G_USER_ID,
			sysdate,
			G_USER_ID,
			sysdate,
			G_LOGIN_ID,
			l_sub_org_cert_issues,
			l_proc_cert_issues,
			decode(l_processes_total, 0, 0, round(l_ineffective_processes/l_processes_total*100)),
			decode(l_total_risks, 0, 0, round(l_unmitigated_risks/l_total_risks*100)),
			decode(l_total_controls, 0, 0, round(l_ineffective_controls/l_total_controls*100)));