APPS.AMW_PROCCERT_EVENT_PVT SQL Statements

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

Line: 23

    SELECT aoot.object_opinion_type_id
      FROM AMW_OBJECT_OPINION_TYPES aoot, AMW_OPINION_TYPES_B aot,
           FND_OBJECTS fo
     WHERE aoot.OPINION_TYPE_ID = aot.OPINION_TYPE_ID
       AND aoot.OBJECT_ID = fo.OBJECT_ID
       AND aot.opinion_type_code = 'EVALUATION'
       AND fo.obj_name = c_obj_name;

Line: 38

  UPDATE amw_risk_associations assoc
     SET last_update_date = sysdate,
         last_updated_by = fnd_global.user_id,
	 last_update_login = fnd_global.conc_login_id,
         pk4 = (SELECT max(opinion_log_id)
                  FROM amw_opinions_log opin
		 WHERE opin.object_opinion_type_id = l_obj_opinion_type_id
		   AND opin.pk1_value = assoc.risk_id
		   AND opin.pk3_value = assoc.pk2	-- organization_id
		   AND NVL(opin.pk4_value, -1)
		          = NVL(assoc.pk3, -1))	-- process_id
   WHERE pk1 = p_certification_id
     AND pk4 IS NULL;

Line: 58

  UPDATE amw_control_associations assoc
     SET last_update_date = sysdate,
         last_updated_by = fnd_global.user_id,
	 last_update_login = fnd_global.conc_login_id,
	 pk5 = (SELECT max(opinion_log_id)
                  FROM amw_opinions_log opin
		 WHERE opin.object_opinion_type_id = l_obj_opinion_type_id
		   AND opin.pk1_value = assoc.control_id
		   AND opin.pk3_value = assoc.pk2)	-- organization_id
   WHERE pk1 = p_certification_id
     AND pk5 IS NULL;

Line: 74

  UPDATE amw_ap_associations assoc
     SET last_update_date = sysdate,
         last_updated_by = fnd_global.user_id,
	 last_update_login = fnd_global.conc_login_id,
	 pk4 = (SELECT max(opinion_log_id)
                  FROM amw_opinions_log opin
		 WHERE opin.object_opinion_type_id = l_obj_opinion_type_id
		   AND opin.pk1_value = assoc.pk3
		   AND opin.pk3_value = assoc.pk2 	-- organization_id
		   AND opin.pk4_value = assoc.audit_procedure_id) -- control_id
   WHERE pk1 = p_certification_id
     AND pk4 IS NULL;

Line: 89

FUNCTION Scope_Update
( p_subscription_guid   in     raw,
  p_event               in out NOCOPY wf_event_t
) return VARCHAR2
IS
  CURSOR c_new_org(c_cert_id NUMBER) IS
    SELECT organization_id
      FROM amw_org_cert_eval_sum
     WHERE certification_id = c_cert_id
       AND unmitigated_risks IS NULL;

Line: 101

    SELECT organization_id, process_id
      FROM amw_proc_cert_eval_sum
     WHERE certification_id = c_cert_id
       AND unmitigated_risks IS NULL;

Line: 107

    SELECT process_id
      FROM amw_proc_cert_eval_sum
     WHERE certification_id = c_cert_id
       AND organization_id = c_org_id;

Line: 113

    	SELECT period.start_date
          FROM AMW_CERTIFICATION_B cert, AMW_GL_PERIODS_V period
         WHERE cert.certification_period_name = period.period_name
           AND cert.certification_period_set_name = period.period_set_name
           AND cert.certification_id = c_cert_id;

Line: 120

       select flex_value_set_id
         from fnd_flex_value_sets
        where flex_value_set_name = 'FCH_ICM_ENTITY_VALUE_SET';

Line: 141

  SAVEPOINT Scope_Update_Event;

Line: 155

      AMW_PROCESS_CERT_SUMMARY.update_summary_table (
		p_certification_id => l_cert_id,
		p_org_id	   => proc_rec.organization_id,
		p_process_id	   => proc_rec.process_id);

Line: 178

      AMW_PROCESS_CERT_SUMMARY.update_summary_table (
		p_certification_id => l_cert_id,
		p_org_id	   => l_org_id,
		p_process_id	   => proc_rec.process_id);

Line: 212

     ROLLBACK TO Scope_Update_Event;

Line: 218

     WF_CORE.CONTEXT('AMW_PROCCERT_EVENT_PVT', 'SCOPE_UPDATE', p_event.getEventName(), p_subscription_guid);

Line: 223

END Scope_Update;

Line: 226

FUNCTION Evaluation_Update
( p_subscription_guid   in     raw,
  p_event               in out NOCOPY wf_event_t
) return VARCHAR2
IS
  CURSOR c_opinion_rec (c_opinion_log_id NUMBER) IS
    SELECT opinion_id, object_name, audit_result_code,
	   pk1_value, pk2_value, pk3_value, pk4_value, pk5_value,
	   pk6_value, pk7_value, pk8_value
      FROM amw_opinions_log_v
     WHERE opinion_log_id = c_opinion_log_id;

Line: 239

    SELECT cert.certification_id, opin.audit_result_code old_eval
      FROM amw_certification_b cert, amw_proc_cert_eval_sum psum,
           amw_opinions_log_v opin
     WHERE cert.certification_status in ('ACTIVE','DRAFT')
       AND cert.certification_id = psum.certification_id
       AND psum.organization_id = c_org_id
       AND psum.process_id = c_proc_id
       AND psum.evaluation_opinion_log_id = opin.opinion_log_id(+);

Line: 249

    SELECT cert.certification_id, opin.audit_result_code old_eval
      FROM amw_certification_b cert, amw_risk_associations assoc,
           amw_opinions_log_v opin
     WHERE cert.certification_status in ('ACTIVE','DRAFT')
       AND cert.certification_id = assoc.pk1
       AND assoc.object_type = 'BUSIPROC_CERTIFICATION'
       AND assoc.risk_id = c_risk_id
       AND assoc.pk2 = c_org_id
       AND NVL(assoc.pk3, -1) = NVL(c_proc_id, -1)
       AND assoc.pk4 = opin.opinion_log_id(+);

Line: 261

    SELECT cert.certification_id, opin.audit_result_code old_eval
      FROM amw_certification_b cert, amw_control_associations assoc,
           amw_opinions_log_v opin
     WHERE cert.certification_status in ('ACTIVE','DRAFT')
       AND cert.certification_id = assoc.pk1
       AND assoc.object_type = 'BUSIPROC_CERTIFICATION'
       AND assoc.control_id = c_ctrl_id
       AND assoc.pk2 = c_org_id
       AND assoc.pk5 = opin.opinion_log_id(+);

Line: 274

SELECT ineff_processes, processes_certified, total_processes, evaluated_processes,unmitigated_risks,
evaluated_risks, total_risks, ineffective_controls, evaluated_controls, total_controls
FROM amw_org_cert_eval_sum
WHERE certification_id = l_cert_id
AND organization_id = l_org_id;

Line: 281

SELECT UNMITIGATED_RISKS, INEFFECTIVE_CONTROLS, PROC_INEFF_CONTROL, ORG_PROC_INEFF_CONTROL
FROM amw_cert_dashboard_sum
WHERE certification_id = l_cert_id;

Line: 286

SELECT  ineffective_controls, evaluated_controls, total_controls, unmitigated_risks, evaluated_risks, total_risks
FROM amw_proc_cert_eval_sum
 WHERE certification_id = l_cert_id
           AND organization_id = l_org_id
           AND process_id IN (
	          SELECT process_id
		    FROM amw_execution_scope
	      START WITH process_id = l_process_id
		     AND organization_id = l_org_id
		     AND entity_id = l_cert_id
		     AND entity_type = 'BUSIPROC_CERTIFICATION'
	      CONNECT BY process_id = PRIOR parent_process_id
	     	     AND organization_id = PRIOR organization_id
		     AND entity_id = PRIOR entity_id
		     AND entity_type = PRIOR entity_type);

Line: 303

SELECT certification_id
FROM amw_org_cert_eval_sum
WHERE organization_id = l_org_id
AND certification_id IN (
	          SELECT cert.certification_id
		    FROM amw_certification_b cert
                   WHERE cert.certification_status in ('ACTIVE','DRAFT')
		     AND cert.object_type = 'PROCESS');

Line: 313

       select flex_value_set_id
         from fnd_flex_value_sets
        where flex_value_set_name = 'FCH_ICM_ENTITY_VALUE_SET';

Line: 371

  SAVEPOINT Evaluation_Update_Event;

Line: 384

    UPDATE amw_org_cert_eval_sum
       SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
	   evaluation_opinion_id = l_opin_id,
           evaluation_opinion_log_id = l_opin_log_id
     WHERE organization_id = l_pk1
       AND certification_id IN (
	          SELECT cert.certification_id
		    FROM amw_certification_b cert, amw_execution_scope scope
                   WHERE cert.certification_status in ('ACTIVE','DRAFT')
		     AND cert.object_type = 'PROCESS'
                     AND scope.entity_type = 'BUSIPROC_CERTIFICATION'
		     AND scope.entity_id = cert.certification_id
		     AND scope.level_id = 3
		     AND scope.organization_id = l_pk1);

Line: 428

UPDATE amw_org_cert_eval_sum
	   SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       evaluated_processes = least(evaluated_processes+1,total_processes),
	       ineff_processes = least(ineff_processes+1,evaluated_processes+1,total_processes),
	       ineff_processes_prcnt = decode(total_processes, 0, 0,
	           round(least(ineff_processes+1,evaluated_processes+1,total_processes)/total_processes*100))
	 WHERE certification_id = proc_rec.certification_id
	   AND organization_id = l_pk3;

Line: 440

	  UPDATE amw_cert_dashboard_sum
             SET last_update_date = sysdate,
                 last_updated_by = fnd_global.user_id,
	         last_update_login = fnd_global.conc_login_id,
		 proc_ineff_control = proc_ineff_control+1
  	   WHERE certification_id = proc_rec.certification_id;

Line: 447

	  UPDATE amw_cert_dashboard_sum
             SET last_update_date = sysdate,
                 last_updated_by = fnd_global.user_id,
	         last_update_login = fnd_global.conc_login_id,
		 org_proc_ineff_control = org_proc_ineff_control+1
  	   WHERE certification_id = proc_rec.certification_id;

Line: 464

UPDATE amw_org_cert_eval_sum
	   SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       evaluated_processes = least(evaluated_processes+1, total_processes)
	 WHERE certification_id = proc_rec.certification_id
	   AND organization_id = l_pk3;

Line: 482

        UPDATE amw_org_cert_eval_sum
	   SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       ineff_processes = least(ineff_processes+1,evaluated_processes,total_processes),
	       ineff_processes_prcnt = decode(total_processes, 0, 0,
	           round(least(ineff_processes+1,evaluated_processes,total_processes)/total_processes*100))
	 WHERE certification_id = proc_rec.certification_id
	   AND organization_id = l_pk3;

Line: 493

	  UPDATE amw_cert_dashboard_sum
             SET last_update_date = sysdate,
                 last_updated_by = fnd_global.user_id,
	         last_update_login = fnd_global.conc_login_id,
		 proc_ineff_control = proc_ineff_control+1
  	   WHERE certification_id = proc_rec.certification_id;

Line: 500

	  UPDATE amw_cert_dashboard_sum
             SET last_update_date = sysdate,
                 last_updated_by = fnd_global.user_id,
	         last_update_login = fnd_global.conc_login_id,
		 org_proc_ineff_control = org_proc_ineff_control+1
  	   WHERE certification_id = proc_rec.certification_id;

Line: 517

        UPDATE amw_org_cert_eval_sum
	   SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       ineff_processes = greatest(0,ineff_processes-1),
	       ineff_processes_prcnt = decode(total_processes, 0, 0,
	           round(greatest(0,ineff_processes-1)/total_processes*100))
	 WHERE certification_id = proc_rec.certification_id
	   AND organization_id = l_pk3;

Line: 536

	  UPDATE amw_cert_dashboard_sum
             SET last_update_date = sysdate,
                 last_updated_by = fnd_global.user_id,
	         last_update_login = fnd_global.conc_login_id,
		 proc_ineff_control = greatest(0,proc_ineff_control-1)
  	   WHERE certification_id = proc_rec.certification_id;

Line: 550

 	UPDATE amw_cert_dashboard_sum
             	SET 	last_update_date = sysdate,
                 		last_updated_by = fnd_global.user_id,
	        	 last_update_login = fnd_global.conc_login_id,
		 org_proc_ineff_control = greatest(0,org_proc_ineff_control-1)
 	WHERE certification_id = proc_rec.certification_id;

Line: 562

    UPDATE amw_proc_cert_eval_sum
       SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
           evaluation_opinion_id = l_opin_id,
           evaluation_opinion_log_id = l_opin_log_id
     WHERE process_id = l_pk1
       AND organization_id = l_pk3
       AND certification_id in (
	          SELECT cert.certification_id
		    FROM amw_certification_b cert, amw_proc_cert_eval_sum psum
                   WHERE cert.certification_status in ('ACTIVE','DRAFT')
		     AND cert.certification_id = psum.certification_id
		     AND psum.organization_id = l_pk3
		     AND psum.process_id = l_pk1);

Line: 609

UPDATE amw_org_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       evaluated_risks = least(evaluated_risks+1,total_risks),
	       unmitigated_risks = least(unmitigated_risks+1,evaluated_risks+1,total_risks),
	       unmitigated_risks_prcnt = decode(total_risks, 0, 0,
	           round(least(unmitigated_risks+1,evaluated_risks+1,total_risks)/total_risks*100))
         WHERE certification_id = risk_rec.certification_id
           AND organization_id = l_pk3;

Line: 629

        UPDATE amw_proc_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       evaluated_risks = least(evaluated_risks+1,total_risks),
	       unmitigated_risks = least(unmitigated_risks+1,evaluated_risks+1,total_risks),
	       unmitigated_risks_prcnt = decode(total_risks, 0, 0,
	           round(least(unmitigated_risks+1,evaluated_risks+1,total_risks)/total_risks*100))
         WHERE certification_id = risk_rec.certification_id
           AND organization_id = l_pk3
           AND process_id IN (
	          SELECT process_id
		    FROM amw_execution_scope
	      START WITH process_id = l_pk4
		     AND organization_id = l_pk3
		     AND entity_id = risk_rec.certification_id
		     AND entity_type = 'BUSIPROC_CERTIFICATION'
	      CONNECT BY process_id = PRIOR parent_process_id
	     	     AND organization_id = PRIOR organization_id
		     AND entity_id = PRIOR entity_id
		     AND entity_type = PRIOR entity_type);

Line: 652

        UPDATE amw_cert_dashboard_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       unmitigated_risks = unmitigated_risks+1
  	 WHERE certification_id = risk_rec.certification_id;

Line: 668

        UPDATE amw_org_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       evaluated_risks = least(evaluated_risks+1,total_risks)
         WHERE certification_id = risk_rec.certification_id
           AND organization_id = l_pk3;

Line: 684

UPDATE amw_proc_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       evaluated_risks = least(evaluated_risks+1,total_risks)
         WHERE certification_id = risk_rec.certification_id
           AND organization_id = l_pk3
           AND process_id IN (
	          SELECT process_id
		    FROM amw_execution_scope
	      START WITH process_id = l_pk4
		     AND organization_id = l_pk3
		     AND entity_id = risk_rec.certification_id
		     AND entity_type = 'BUSIPROC_CERTIFICATION'
	      CONNECT BY process_id = PRIOR parent_process_id
	     	     AND organization_id = PRIOR organization_id
		     AND entity_id = PRIOR entity_id
		     AND entity_type = PRIOR entity_type);

Line: 711

UPDATE amw_org_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       unmitigated_risks = least(unmitigated_risks+1,evaluated_risks,total_risks),
	       unmitigated_risks_prcnt = decode(total_risks, 0, 0,
	           round(least(unmitigated_risks+1,evaluated_risks,total_risks)/total_risks*100))
         WHERE certification_id = risk_rec.certification_id
           AND organization_id = l_pk3;

Line: 730

 UPDATE amw_proc_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       unmitigated_risks = least(unmitigated_risks+1,evaluated_risks,total_risks),
	       unmitigated_risks_prcnt = decode(total_risks, 0, 0,
	           round(least(unmitigated_risks+1,evaluated_risks,total_risks)/total_risks*100))
         WHERE certification_id = risk_rec.certification_id
           AND organization_id = l_pk3
           AND process_id IN (
	          SELECT process_id
		    FROM amw_execution_scope
	      START WITH process_id = l_pk4
		     AND organization_id = l_pk3
		     AND entity_id = risk_rec.certification_id
		     AND entity_type = 'BUSIPROC_CERTIFICATION'
	      CONNECT BY process_id = PRIOR parent_process_id
	     	     AND organization_id = PRIOR organization_id
		     AND entity_id = PRIOR entity_id
		     AND entity_type = PRIOR entity_type);

Line: 751

        UPDATE amw_cert_dashboard_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       unmitigated_risks = unmitigated_risks+1
  	 WHERE certification_id = risk_rec.certification_id;

Line: 767

        UPDATE amw_org_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       unmitigated_risks = greatest(0,unmitigated_risks-1),
	       unmitigated_risks_prcnt = decode(total_risks, 0, 0,
	           round(greatest(0,unmitigated_risks-1)/total_risks*100))
         WHERE certification_id = risk_rec.certification_id
           AND organization_id = l_pk3;

Line: 786

UPDATE amw_proc_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       unmitigated_risks = greatest(0,unmitigated_risks-1),
	       unmitigated_risks_prcnt = decode(total_risks, 0, 0,
	           round(greatest(0,unmitigated_risks-1)/total_risks*100))
         WHERE certification_id = risk_rec.certification_id
           AND organization_id = l_pk3
           AND process_id IN (
	          SELECT process_id
		    FROM amw_execution_scope
	      START WITH process_id = l_pk4
		     AND organization_id = l_pk3
		     AND entity_id = risk_rec.certification_id
		     AND entity_type = 'BUSIPROC_CERTIFICATION'
	      CONNECT BY process_id = PRIOR parent_process_id
	     	     AND organization_id = PRIOR organization_id
		     AND entity_id = PRIOR entity_id
		     AND entity_type = PRIOR entity_type);

Line: 816

        UPDATE amw_cert_dashboard_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       unmitigated_risks = greatest(0,unmitigated_risks-1)
  	 WHERE certification_id = risk_rec.certification_id;

Line: 827

    UPDATE amw_risk_associations
       SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
           pk4 = l_opin_log_id
     WHERE object_type = 'BUSIPROC_CERTIFICATION'
       AND risk_id = l_pk1
       AND pk2 = l_pk3	-- organization_id
       AND NVL(pk3,-1) = NVL(l_pk4,-1)	-- process_id
       AND pk1 IN (
	          SELECT assoc.pk1
		    FROM amw_certification_b cert, amw_risk_associations assoc
                   WHERE cert.certification_status in ('ACTIVE','DRAFT')
		     AND cert.certification_id = assoc.pk1
		     AND assoc.object_type = 'BUSIPROC_CERTIFICATION'
		     AND assoc.risk_id = l_pk1
		     AND assoc.pk2 = l_pk3	-- organization_id
		     AND NVL(assoc.pk3, -1) = NVL(l_pk4, -1));	-- proccess_id

Line: 878

UPDATE amw_org_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       evaluated_controls = least(evaluated_controls+1,total_controls),
	       ineffective_controls = least(ineffective_controls+1,evaluated_controls+1,total_controls),
	       ineff_controls_prcnt = decode(total_controls, 0, 0,
	           round(least(ineffective_controls+1,evaluated_controls+1,total_controls)/total_controls*100))
         WHERE certification_id = ctrl_rec.certification_id
           AND organization_id = l_pk3;

Line: 898

        UPDATE amw_proc_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       evaluated_controls = least(evaluated_controls+1,total_controls),
	       ineffective_controls = least(ineffective_controls+1,evaluated_controls+1,total_controls),
	       ineffective_controls_prcnt = decode(total_controls, 0, 0,
	           round(least(ineffective_controls+1,evaluated_controls+1,total_controls)/total_controls*100))
         WHERE certification_id = ctrl_rec.certification_id
           AND organization_id = l_pk3
           AND process_id IN (
	          SELECT process_id
		    FROM amw_execution_scope --, amw_control_associations assoc
	      START WITH process_id IN (
				    SELECT pk3
				      FROM amw_control_associations
				     WHERE object_type = 'BUSIPROC_CERTIFICATION'
				       AND control_id = l_pk1
				       AND pk1 = ctrl_rec.certification_id
				       AND pk2 = l_pk3)  -- organization_id
		     AND organization_id = l_pk3
		     AND entity_id = ctrl_rec.certification_id
		     AND entity_type = 'BUSIPROC_CERTIFICATION'
	      CONNECT BY process_id = PRIOR parent_process_id
	     	     AND organization_id = PRIOR organization_id
		     AND entity_id = PRIOR entity_id
		     AND entity_type = PRIOR entity_type);

Line: 926

        UPDATE amw_cert_dashboard_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       ineffective_controls = ineffective_controls+1
  	 WHERE certification_id = ctrl_rec.certification_id;

Line: 942

UPDATE amw_org_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       evaluated_controls = least(evaluated_controls+1,total_controls)
         WHERE certification_id = ctrl_rec.certification_id
           AND organization_id = l_pk3;

Line: 960

 UPDATE amw_proc_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       evaluated_controls = least(evaluated_controls+1,total_controls)
         WHERE certification_id = ctrl_rec.certification_id
           AND organization_id = l_pk3
           AND process_id IN (
	          SELECT process_id
		    FROM amw_execution_scope --, amw_control_associations assoc
	      START WITH process_id IN (
				    SELECT pk3
				      FROM amw_control_associations
				     WHERE object_type = 'BUSIPROC_CERTIFICATION'
				       AND control_id = l_pk1
				       AND pk1 = ctrl_rec.certification_id
				       AND pk2 = l_pk3)  -- organization_id
		     AND organization_id = l_pk3
		     AND entity_id = ctrl_rec.certification_id
		     AND entity_type = 'BUSIPROC_CERTIFICATION'
	      CONNECT BY process_id = PRIOR parent_process_id
	     	     AND organization_id = PRIOR organization_id
		     AND entity_id = PRIOR entity_id
		     AND entity_type = PRIOR entity_type);

Line: 995

UPDATE amw_org_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       ineffective_controls = least(ineffective_controls+1,evaluated_controls,total_controls),
	       ineff_controls_prcnt = decode(total_controls, 0, 0,
	           round(least(ineffective_controls+1,evaluated_controls,total_controls)/total_controls*100))
         WHERE certification_id = ctrl_rec.certification_id
           AND organization_id = l_pk3;

Line: 1014

UPDATE amw_proc_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       ineffective_controls = least(ineffective_controls+1,evaluated_controls,total_controls),
	       ineffective_controls_prcnt = decode(total_controls, 0, 0,
	           round(least(ineffective_controls+1,evaluated_controls,total_controls)/total_controls*100))
         WHERE certification_id = ctrl_rec.certification_id
           AND organization_id = l_pk3
           AND process_id IN (
	          SELECT process_id
		    FROM amw_execution_scope --, amw_control_associations assoc
	      START WITH process_id IN (
				    SELECT pk3
				      FROM amw_control_associations
				     WHERE object_type = 'BUSIPROC_CERTIFICATION'
				       AND control_id = l_pk1
				       AND pk1 = ctrl_rec.certification_id
				       AND pk2 = l_pk3)  -- organization_id
		     AND organization_id = l_pk3
		     AND entity_id = ctrl_rec.certification_id
		     AND entity_type = 'BUSIPROC_CERTIFICATION'
	      CONNECT BY process_id = PRIOR parent_process_id
	     	     AND organization_id = PRIOR organization_id
		     AND entity_id = PRIOR entity_id
		     AND entity_type = PRIOR entity_type);

Line: 1042

        UPDATE amw_cert_dashboard_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       ineffective_controls = ineffective_controls+1
  	 WHERE certification_id = ctrl_rec.certification_id;

Line: 1058

 UPDATE amw_org_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       ineffective_controls = greatest(0,ineffective_controls-1),
	       ineff_controls_prcnt = decode(total_controls, 0, 0,
	           round(greatest(0,ineffective_controls-1)/total_controls*100))
         WHERE certification_id = ctrl_rec.certification_id
           AND organization_id = l_pk3;

Line: 1078

        UPDATE amw_proc_cert_eval_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       ineffective_controls = greatest(0,ineffective_controls-1),
	       ineffective_controls_prcnt = decode(total_controls, 0, 0,
	           round(greatest(0,ineffective_controls-1)/total_controls*100))
         WHERE certification_id = ctrl_rec.certification_id
           AND organization_id = l_pk3
           AND process_id IN (
	          SELECT process_id
		    FROM amw_execution_scope --, amw_control_associations assoc
	      START WITH process_id IN (
				    SELECT pk3
				      FROM amw_control_associations
				     WHERE object_type = 'BUSIPROC_CERTIFICATION'
				       AND control_id = l_pk1
				       AND pk1 = ctrl_rec.certification_id
				       AND pk2 = l_pk3)  -- organization_id
		     AND organization_id = l_pk3
		     AND entity_id = ctrl_rec.certification_id
		     AND entity_type = 'BUSIPROC_CERTIFICATION'
	      CONNECT BY process_id = PRIOR parent_process_id
	     	     AND organization_id = PRIOR organization_id
		     AND entity_id = PRIOR entity_id
		     AND entity_type = PRIOR entity_type);

Line: 1114

 UPDATE amw_cert_dashboard_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       ineffective_controls = greatest(0,ineffective_controls-1)
  	 WHERE certification_id = ctrl_rec.certification_id;

Line: 1125

    UPDATE amw_control_associations
       SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
	   pk5 = l_opin_log_id
     WHERE object_type = 'BUSIPROC_CERTIFICATION'
       AND control_id = l_pk1
       AND pk2 = l_pk3	-- organization_id
       AND pk1 IN (
	          SELECT assoc.pk1
		    FROM amw_certification_b cert, amw_control_associations assoc
                   WHERE cert.certification_status in ('ACTIVE','DRAFT')
		     AND cert.certification_id = assoc.pk1
		     AND assoc.object_type = 'BUSIPROC_CERTIFICATION'
		     AND assoc.control_id = l_pk1
		     AND assoc.pk2 = l_pk3);	-- organization_id

Line: 1143

    UPDATE amw_ap_associations
       SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
	   pk4 = l_opin_log_id
     WHERE object_type = 'BUSIPROC_CERTIFICATION'
       AND audit_procedure_id = l_pk1
       AND pk2 = l_pk3	-- organization_id
       AND pk3 = l_pk4	-- control_id
       AND pk1 IN (
	          SELECT assoc.pk1
		    FROM amw_certification_b cert, amw_ap_associations assoc
                   WHERE cert.certification_status in ('ACTIVE','DRAFT')
		     AND cert.certification_id = assoc.pk1
		     AND assoc.object_type = 'BUSIPROC_CERTIFICATION'
		     AND assoc.audit_procedure_id = l_pk1
		     AND assoc.pk2 = l_pk3 	-- organization_id
		     AND assoc.pk3 = l_pk4);    -- control_id

Line: 1199

       amw_org_cert_aggr_pkg.update_org_cert_aggr_rows(FCH_Get_cert_info_rec.certification_id, l_fch_org_id);

Line: 1215

     ROLLBACK TO Evaluation_Update_Event;

Line: 1221

     WF_CORE.CONTEXT('AMW_PROCCERT_EVENT_PVT', 'EVALUATION_UPDATE', p_event.getEventName(), p_subscription_guid);

Line: 1226

END Evaluation_Update;

Line: 1228

FUNCTION Certification_Update
( p_subscription_guid   in     raw,
  p_event               in out NOCOPY wf_event_t
) return VARCHAR2
IS
  CURSOR c_opinion_rec (c_opinion_log_id NUMBER) IS
    SELECT opinion_id, object_name, audit_result_code,
	   pk1_value, pk2_value, pk3_value, pk4_value, pk5_value,
	   pk6_value, pk7_value, pk8_value
      FROM amw_opinions_log_v
     WHERE opinion_log_id = c_opinion_log_id;

Line: 1241

    SELECT audit_result_code
      FROM amw_opinions_log_v
     WHERE opinion_log_id =
			  (SELECT max(v2.opinion_log_id)
			     FROM amw_opinions_log_v v1, amw_opinions_log_v v2
			    WHERE v1.opinion_log_id = c_opin_log_id
			      AND v1.opinion_id = v2.opinion_id
			      AND v2.opinion_log_id < c_opin_log_id);

Line: 1251

    SELECT count(distinct aes.organization_id)
      FROM AMW_EXECUTION_SCOPE aes
     WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
       AND aes.entity_id = c_cert_id
       AND aes.level_id = 4
       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 = c_cert_id
                          AND aov.pk1_value = aes.process_id);

Line: 1265

SELECT organization_id, sub_org_cert,  total_sub_org , sub_org_cert_issues, proc_cert_issues, processes_certified, total_processes
FROM amw_org_cert_eval_sum
WHERE certification_id = l_cert_id
AND organization_id IN (
	           SELECT parent_object_id
		     FROM amw_entity_hierarchies
	       START WITH entity_type = 'BUSIPROC_CERTIFICATION'
		      AND entity_id = l_org_id
		      AND object_type = 'ORG'
		      AND object_id = l_cert_id
               CONNECT BY entity_type = PRIOR entity_type
		      AND entity_id = PRIOR entity_id
		      AND object_type = PRIOR object_type
		      AND object_id = PRIOR parent_object_id);

Line: 1284

SELECT orgs_pending_certification,  processes_cert_issues, processes_not_cert, org_process_cert_issues, org_process_not_cert
FROM amw_cert_dashboard_sum
WHERE certification_id = l_cert_id;

Line: 1289

SELECT processes_not_cert, org_process_not_cert, processes_cert_issues, org_process_cert_issues
FROM amw_cert_dashboard_sum
WHERE certification_id = l_cert_id;

Line: 1294

SELECT processes_certified, total_processes, proc_cert_issues
FROM amw_org_cert_eval_sum
WHERE certification_id = l_cert_id
AND organization_id = l_org_id;

Line: 1301

SELECT certification_id, organization_id, process_id, sub_process_cert, total_sub_process_cert, sub_process_cert_issues
FROM amw_proc_cert_eval_sum
       WHERE certification_id = l_cert_id
         AND organization_id = l_org_id
	 AND process_id in (
	          SELECT parent_process_id
		    FROM amw_execution_scope
	      START WITH entity_type = 'BUSIPROC_CERTIFICATION'
		     AND entity_id = l_cert_id
		     AND organization_id = l_org_id
		     AND process_id = l_process_id
              CONNECT BY entity_type = PRIOR entity_type
		     AND entity_id = PRIOR entity_id
		     AND organization_id = PRIOR organization_id
		     AND process_id = PRIOR parent_process_id);

Line: 1318

SELECT certification_id, organization_id, process_id, org_process_cert, org_process_cert_issues, total_org_process_cert
FROM amw_proc_cert_eval_sum
 WHERE certification_id = l_cert_id
         AND (process_id = l_process_id
	      OR
              process_id IN (
	         SELECT proc.process_id
		   FROM amw_execution_scope scp,
		        amw_process_organization procorg,
			amw_process proc
		  WHERE scp.entity_id = l_cert_id
		    AND scp.entity_type = 'BUSIPROC_CERTIFICATION'
		    AND scp.organization_id = l_org_id
		    AND scp.process_id = l_process_id
		    AND scp.process_org_rev_id = procorg.process_org_rev_id
		    AND procorg.standard_variation = proc.process_rev_id))
	 AND organization_id in (
                   SELECT parent_object_id
		     FROM amw_entity_hierarchies
	       START WITH entity_type = 'BUSIPROC_CERTIFICATION'
		      AND entity_id = l_cert_id
		      AND object_type = 'ORG'
		      AND object_id = l_org_id
               CONNECT BY entity_type = PRIOR entity_type
		      AND entity_id = PRIOR entity_id
		      AND object_type = PRIOR object_type
		      AND object_id = PRIOR parent_object_id);

Line: 1347

       select flex_value_set_id
         from fnd_flex_value_sets
        where flex_value_set_name = 'FCH_ICM_ENTITY_VALUE_SET';

Line: 1388

  SAVEPOINT Certification_Update_Event;

Line: 1408

    UPDATE amw_org_cert_eval_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
             certification_opinion_id = l_opin_id
       WHERE certification_id = l_pk2
         AND organization_id = l_pk1;

Line: 1431

UPDATE amw_cert_dashboard_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
	     orgs_pending_certification = greatest(0,orgs_pending_certification-1)
       WHERE certification_id = l_pk1;

Line: 1456

        UPDATE amw_org_cert_eval_sum
         SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
	   sub_org_cert = least(sub_org_cert+1,total_sub_org)
       WHERE certification_id = l_pk1
       AND organization_id = Get_org_cert_Rec.organization_id;

Line: 1475

        UPDATE amw_org_cert_eval_sum
         SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
	   sub_org_cert = least(sub_org_cert+1,total_sub_org),
	   sub_org_cert_issues = least(sub_org_cert_issues+1,sub_org_cert+1,total_sub_org)
       WHERE certification_id = l_pk1
       AND organization_id = Get_org_cert_Rec.organization_id;

Line: 1495

          UPDATE amw_org_cert_eval_sum
         SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
	   sub_org_cert_issues = least(sub_org_cert_issues+1,sub_org_cert,total_sub_org)
       WHERE certification_id = l_pk1
       AND organization_id = Get_org_cert_Rec.organization_id;

Line: 1514

        UPDATE amw_org_cert_eval_sum
         SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
	   sub_org_cert_issues = greatest(0,sub_org_cert_issues-1)
       WHERE certification_id = l_pk1
       AND organization_id = Get_org_cert_Rec.organization_id;

Line: 1541

     UPDATE amw_proc_cert_eval_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
	     certification_opinion_id = l_opin_id
       WHERE certification_id = l_pk2
         AND organization_id = l_pk3
	 AND process_id = l_pk1;

Line: 1576

      UPDATE amw_org_cert_eval_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
	     processes_certified = least(processes_certified+1,total_processes)
       WHERE certification_id = l_pk2
         AND organization_id = l_pk3;

Line: 1596

	UPDATE amw_cert_dashboard_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       orgs_pending_in_scope = l_orgs_pending,
	       processes_not_cert = greatest(0,processes_not_cert-1)
  	 WHERE certification_id = l_pk2;

Line: 1615

	UPDATE amw_cert_dashboard_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
               last_update_login = fnd_global.conc_login_id,
	       orgs_pending_in_scope = l_orgs_pending,
	       org_process_not_cert = greatest(0,org_process_not_cert-1)
  	 WHERE certification_id = l_pk2;

Line: 1636

 UPDATE amw_org_cert_eval_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
	     processes_certified = least(processes_certified+1,total_processes),
	     proc_cert_issues = least(proc_cert_issues+1,processes_certified+1,total_processes)
       WHERE certification_id = l_pk2
         AND organization_id = l_pk3;

Line: 1647

	UPDATE amw_cert_dashboard_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       orgs_pending_in_scope = l_orgs_pending,
	       processes_not_cert = greatest(0,processes_not_cert-1),
	       processes_cert_issues = processes_cert_issues+1
  	 WHERE certification_id = l_pk2;

Line: 1657

	UPDATE amw_cert_dashboard_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
               last_update_login = fnd_global.conc_login_id,
	       orgs_pending_in_scope = l_orgs_pending,
	       org_process_not_cert = greatest(0,org_process_not_cert-1),
	       org_process_cert_issues = org_process_cert_issues+1
  	 WHERE certification_id = l_pk2;

Line: 1677

      UPDATE amw_org_cert_eval_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
	     proc_cert_issues = greatest(0,proc_cert_issues-1)
       WHERE certification_id = l_pk2
         AND organization_id = l_pk3;

Line: 1697

	UPDATE amw_cert_dashboard_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       orgs_pending_in_scope = l_orgs_pending,
	       processes_cert_issues = greatest(0,processes_cert_issues-1)
  	 WHERE certification_id = l_pk2;

Line: 1715

	UPDATE amw_cert_dashboard_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
               last_update_login = fnd_global.conc_login_id,
	       orgs_pending_in_scope = l_orgs_pending,
	       org_process_cert_issues = greatest(org_process_cert_issues-1,0)
  	 WHERE certification_id = l_pk2;

Line: 1735

      UPDATE amw_org_cert_eval_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
	     proc_cert_issues = least(proc_cert_issues+1,processes_certified,total_processes)
       WHERE certification_id = l_pk2
         AND organization_id = l_pk3;

Line: 1745

	UPDATE amw_cert_dashboard_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
	       last_update_login = fnd_global.conc_login_id,
	       orgs_pending_in_scope = l_orgs_pending,
	       processes_cert_issues = processes_cert_issues+1
  	 WHERE certification_id = l_pk2;

Line: 1753

	UPDATE amw_cert_dashboard_sum
           SET last_update_date = sysdate,
               last_updated_by = fnd_global.user_id,
               last_update_login = fnd_global.conc_login_id,
	       orgs_pending_in_scope = l_orgs_pending,
	       org_process_cert_issues = org_process_cert_issues+1
  	 WHERE certification_id = l_pk2;

Line: 1777

        UPDATE amw_proc_cert_eval_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
	     sub_process_cert = least(sub_process_cert+1,total_sub_process_cert)
       WHERE certification_id = Get_parent_process_Rec.certification_id
         AND organization_id = Get_parent_process_Rec.organization_id
         AND process_id = Get_parent_process_Rec.process_id;

Line: 1799

     UPDATE amw_proc_cert_eval_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
	     sub_process_cert = least(sub_process_cert+1,total_sub_process_cert),
	     sub_process_cert_issues = least(sub_process_cert_issues+1,sub_process_cert+1,total_sub_process_cert)
       WHERE certification_id = Get_parent_process_Rec.certification_id
        	 AND organization_id = Get_parent_process_Rec.organization_id
         	AND process_id = Get_parent_process_Rec.process_id;

Line: 1820

      UPDATE amw_proc_cert_eval_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
	     sub_process_cert_issues = greatest(0,sub_process_cert_issues-1)
WHERE certification_id = Get_parent_process_Rec.certification_id
        	 AND organization_id = Get_parent_process_Rec.organization_id
         	AND process_id = Get_parent_process_Rec.process_id;

Line: 1839

   UPDATE amw_proc_cert_eval_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
	     sub_process_cert_issues = least(sub_process_cert_issues+1,sub_process_cert,total_sub_process_cert)
	WHERE certification_id = Get_parent_process_Rec.certification_id
        	 AND organization_id = Get_parent_process_Rec.organization_id
         	AND process_id = Get_parent_process_Rec.process_id;

Line: 1865

     UPDATE amw_proc_cert_eval_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
	     org_process_cert = least(org_process_cert+1,total_org_process_cert)
       WHERE certification_id = Get_related_org_proc_Rec.certification_id
         AND process_id = Get_related_org_proc_Rec.process_id
         AND organization_id = Get_related_org_proc_Rec.organization_id;

Line: 1888

  UPDATE amw_proc_cert_eval_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
	     org_process_cert = least(org_process_cert+1,total_org_process_cert),
	     org_process_cert_issues = least(org_process_cert_issues+1,org_process_cert+1,total_org_process_cert)
    WHERE certification_id = Get_related_org_proc_Rec.certification_id
         AND process_id = Get_related_org_proc_Rec.process_id
         AND organization_id = Get_related_org_proc_Rec.organization_id;

Line: 1909

       UPDATE amw_proc_cert_eval_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
	     org_process_cert_issues = greatest(0,org_process_cert_issues-1)
       WHERE certification_id = Get_related_org_proc_Rec.certification_id
         AND process_id = Get_related_org_proc_Rec.process_id
         AND organization_id = Get_related_org_proc_Rec.organization_id;

Line: 1929

      UPDATE amw_proc_cert_eval_sum
         SET last_update_date = sysdate,
             last_updated_by = fnd_global.user_id,
	     last_update_login = fnd_global.conc_login_id,
	     org_process_cert_issues = least(org_process_cert_issues+1,org_process_cert,total_org_process_cert)
   WHERE certification_id = Get_related_org_proc_Rec.certification_id
         AND process_id = Get_related_org_proc_Rec.process_id
         AND organization_id = Get_related_org_proc_Rec.organization_id;

Line: 1978

       amw_org_cert_aggr_pkg.update_org_cert_aggr_rows(l_fch_cert_id, l_fch_org_id);

Line: 1990

     ROLLBACK TO Certification_Update_Event;

Line: 1996

     WF_CORE.CONTEXT('AMW_PROCCERT_EVENT_PVT', 'CERTIFICAITON_UPDATE',
		p_event.getEventName(), p_subscription_guid);

Line: 2002

END Certification_Update;