APPS.AMW_PROJECT_EVENT_PVT SQL Statements

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

Line: 20

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

Line: 32

    SELECT organization_id
      FROM AMW_AUDIT_SCOPE_ORGANIZATIONS
     WHERE audit_project_id = c_audit_proj_id;

Line: 38

    SELECT organization_id, process_id
      FROM AMW_AUDIT_SCOPE_PROCESSES
     WHERE audit_project_id = c_audit_proj_id
       AND unmitigated_risks IS NULL;

Line: 44

    SELECT process_id
      FROM AMW_AUDIT_SCOPE_PROCESSES
     WHERE audit_project_id = c_audit_proj_id
       AND organization_id = c_org_id;

Line: 54

  SAVEPOINT Scope_Update_Event;

Line: 63

      update_org_summary_table (
		p_audit_project_id => l_audit_proj_id,
		p_org_id	   => org_rec.organization_id);

Line: 74

      update_proc_summary_table (
		p_audit_project_id => l_audit_proj_id,
		p_org_id	   => proc_rec.organization_id,
		p_proc_id	   => proc_rec.process_id);

Line: 82

    update_org_summary_table (
		p_audit_project_id => l_audit_proj_id,
		p_org_id	   => l_org_id);

Line: 87

      update_proc_summary_table (
		p_audit_project_id => l_audit_proj_id,
		p_org_id	   => l_org_id,
		p_proc_id	   => proc_rec.process_id);

Line: 96

      update_org_summary_table (
		p_audit_project_id => l_audit_proj_id,
		p_org_id	   => org_rec.organization_id);

Line: 106

     ROLLBACK TO Scope_Update_Event;

Line: 112

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

Line: 117

END Scope_Update;

Line: 120

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: 133

    SELECT audit_result_code
      FROM amw_opinions_log_v
     WHERE opinion_log_id = (SELECT max(opinion_log_id)
			       FROM amw_opinions_log_v
			      WHERE opinion_id = c_opinion_id
			        AND opinion_log_id < c_opinion_log_id);

Line: 158

  SAVEPOINT Evaluation_Update_Event;

Line: 172

  select decode(l_prev_eval, null, 1, 0)
    into l_evaluated_diff
    from dual;

Line: 176

  select decode(l_new_eval,
                l_prev_eval, 0,
                'EFFECTIVE', decode(l_prev_eval, null, 0, -1),
		decode(l_prev_eval, 'EFFECTIVE', 1, null, 1, 0))
    into l_ineff_diff
    from dual;

Line: 184

    UPDATE amw_audit_scope_organizations
       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
     WHERE organization_id = l_pk1
       AND audit_project_id = l_pk2;

Line: 192

    UPDATE amw_audit_scope_organizations
       SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
	   sub_orgs_evaluated = least(sub_orgs_evaluated+l_evaluated_diff,total_sub_orgs),
           ineffective_sub_orgs =
		least(greatest(0,ineffective_sub_orgs+l_ineff_diff),sub_orgs_evaluated+l_evaluated_diff,total_sub_orgs)
     WHERE organization_id IN (
                   SELECT parent_object_id
		     FROM amw_entity_hierarchies
	       START WITH entity_type = 'PROJECT'
		      AND entity_id = l_pk2
		      AND object_type = 'ORG'
		      AND object_id = l_pk1
               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)
       AND audit_project_id = l_pk2;

Line: 213

    UPDATE amw_audit_scope_processes
       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
     WHERE process_id = l_pk1
       AND audit_project_id = l_pk2
       AND organization_id = l_pk3;

Line: 222

    UPDATE amw_audit_scope_organizations
       SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
	   processes_evaluated = least(processes_evaluated+l_evaluated_diff,total_processes),
           ineffective_processes =
		least(greatest(0,ineffective_processes+l_ineff_diff),processes_evaluated+l_evaluated_diff,total_processes)
     WHERE audit_project_id = l_pk2
       AND organization_id = l_pk3;

Line: 232

    UPDATE amw_audit_scope_organizations
       SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
	   risks_evaluated =
                least(risks_evaluated+l_evaluated_diff,total_risks),
           unmitigated_risks =
		least(greatest(0,unmitigated_risks+l_ineff_diff),risks_evaluated+l_evaluated_diff,total_risks)
     WHERE audit_project_id = l_pk2
       AND organization_id = l_pk3;

Line: 243

    UPDATE amw_audit_scope_processes
       SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
	   risks_evaluated =
                least(risks_evaluated+l_evaluated_diff,total_risks),
           unmitigated_risks =
		least(greatest(0,unmitigated_risks+l_ineff_diff),risks_evaluated+l_evaluated_diff,total_risks)
     WHERE audit_project_id = l_pk2
       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 = l_pk2
		      AND entity_type = 'PROJECT'
	 CONNECT BY PRIOR parent_process_id = process_id
		      AND organization_id = PRIOR organization_id
		      AND entity_id = PRIOR entity_id
                      AND entity_type = PRIOR entity_type);

Line: 266

    UPDATE amw_audit_scope_organizations
       SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
	   controls_evaluated =
	        least(controls_evaluated+l_evaluated_diff,total_controls),
           ineffective_controls =
	        least(greatest(0,ineffective_controls+l_ineff_diff),controls_evaluated+l_evaluated_diff,total_controls)
     WHERE audit_project_id = l_pk2
       AND organization_id = l_pk3;

Line: 277

    UPDATE amw_audit_scope_processes
       SET last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
	   last_update_login = fnd_global.conc_login_id,
	   controls_evaluated =
	        least(controls_evaluated+l_evaluated_diff,total_controls),
           ineffective_controls =
	        least(greatest(0,ineffective_controls+l_ineff_diff),controls_evaluated+l_evaluated_diff,total_controls)
     WHERE audit_project_id = l_pk2
       AND organization_id = l_pk3
       AND process_id IN (SELECT process_id
		     FROM amw_execution_scope
	       START WITH process_id IN ( SELECT pk3
		            FROM amw_control_associations
			   WHERE object_type = 'PROJECT'
			     AND control_id = l_pk1
			     AND pk1 = l_pk2       --audit_project_id
			     AND pk2 = l_pk3       --organization_id
			     )
		      AND organization_id = l_pk3
		      AND entity_id = l_pk2
		      AND entity_type = 'PROJECT'
	 CONNECT BY PRIOR parent_process_id = process_id
		      AND organization_id = PRIOR organization_id
		      AND entity_id = PRIOR entity_id
                      AND entity_type = PRIOR entity_type);

Line: 315

     ROLLBACK TO Evaluation_Update_Event;

Line: 321

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

Line: 326

END Evaluation_Update;

Line: 329

PROCEDURE update_org_summary_table (
	  p_audit_project_id	IN 	NUMBER,
	  p_org_id 		IN 	NUMBER
) IS

  CURSOR get_total_sub_orgs IS
    SELECT count(distinct object_id)
      FROM amw_entity_hierarchies hier
     START WITH entity_id = p_audit_project_id
            AND entity_type = 'PROJECT'
            AND object_type = 'ORG'
            AND parent_object_id = p_org_id
   CONNECT BY entity_type = PRIOR entity_type
	   AND entity_id = PRIOR entity_id
	   AND object_type = PRIOR object_type
	   AND parent_object_id = PRIOR object_id;

Line: 347

    SELECT count(pk1_value)
      FROM amw_opinions_v
     WHERE opinion_type_code = 'EVALUATION'
       AND object_name = 'AMW_ORGANIZATION'
       AND pk2_value = p_audit_project_id
       AND pk1_value IN (
               SELECT object_id
	         FROM amw_entity_hierarchies
	   START WITH entity_id = p_audit_project_id
                  AND entity_type = 'PROJECT'
                  AND object_type = 'ORG'
                  AND parent_object_id = p_org_id
           CONNECT BY entity_type = PRIOR entity_type
	          AND entity_id = PRIOR entity_id
	          AND object_type = PRIOR object_type
	          AND parent_object_id = PRIOR object_id);

Line: 365

    SELECT count(pk1_value)
      FROM amw_opinions_v
     WHERE opinion_type_code = 'EVALUATION'
       AND object_name = 'AMW_ORGANIZATION'
       AND audit_result_code <> 'EFFECTIVE'
       AND pk2_value = p_audit_project_id
       AND pk1_value IN (
               SELECT object_id
	         FROM amw_entity_hierarchies
	   START WITH entity_id = p_audit_project_id
                  AND entity_type = 'PROJECT'
                  AND object_type = 'ORG'
                  AND parent_object_id = p_org_id
           CONNECT BY entity_type = PRIOR entity_type
	          AND entity_id = PRIOR entity_id
	          AND object_type = PRIOR object_type
	          AND parent_object_id = PRIOR object_id);

Line: 384

    SELECT count(distinct process_id)
      FROM amw_execution_scope
     WHERE entity_type = 'PROJECT'
       AND entity_id = p_audit_project_id
       AND organization_id = p_org_id;

Line: 391

    SELECT count(pk1_value)
      FROM amw_opinions_v opin
     WHERE opinion_type_code = 'EVALUATION'
       AND object_name = 'AMW_ORG_PROCESS'
       AND pk2_value = p_audit_project_id
       AND pk3_value = p_org_id
       AND exists (select 'Y' from amw_execution_scope scope
	       where scope.entity_type='PROJECT'
	         and scope.entity_id=p_audit_project_id
		 and scope.organization_id=opin.pk3_value
		 and scope.process_id=opin.pk1_value);

Line: 404

    SELECT count(pk1_value)
      FROM amw_opinions_v opin
     WHERE opinion_type_code = 'EVALUATION'
       AND object_name = 'AMW_ORG_PROCESS'
       AND audit_result_code <> 'EFFECTIVE'
       AND pk2_value = p_audit_project_id
       AND pk3_value = p_org_id
       AND exists (select 'Y' from amw_execution_scope scope
	       where scope.entity_type='PROJECT'
	         and scope.entity_id=p_audit_project_id
		 and scope.organization_id=opin.pk3_value
		 and scope.process_id=opin.pk1_value);

Line: 418

    SELECT count(risk_id)
      FROM amw_risk_associations
     WHERE object_type = 'PROJECT'
       AND pk1 = p_audit_project_id
       AND pk2 = p_org_id;

Line: 425

    SELECT count(opin.pk1_value)
      FROM amw_risk_associations assoc, amw_opinions_v opin
     WHERE assoc.object_type = 'PROJECT'
       AND assoc.pk1 = p_audit_project_id
       AND assoc.pk2 = p_org_id
       AND opin.opinion_type_code = 'EVALUATION'
       AND opin.object_name = 'AMW_ORG_PROCESS_RISK'
       AND opin.pk1_value = assoc.risk_id
       AND opin.pk2_value = assoc.pk1
       AND opin.pk3_value = assoc.pk2;

Line: 437

    SELECT count(opin.pk1_value)
      FROM amw_risk_associations assoc, amw_opinions_v opin
     WHERE assoc.object_type = 'PROJECT'
       AND assoc.pk1 = p_audit_project_id
       AND assoc.pk2 = p_org_id
       AND opin.opinion_type_code = 'EVALUATION'
       AND opin.object_name = 'AMW_ORG_PROCESS_RISK'
       AND opin.pk1_value = assoc.risk_id
       AND opin.pk2_value = assoc.pk1
       AND opin.pk3_value = assoc.pk2
       AND opin.audit_result_code <> 'EFFECTIVE';

Line: 450

    SELECT count(distinct control_id)
      FROM amw_control_associations
     WHERE object_type = 'PROJECT'
       AND pk1 = p_audit_project_id
       AND pk2 = p_org_id;

Line: 457

    SELECT count(pk1_value)
      FROM amw_opinions_v
     WHERE opinion_type_code = 'EVALUATION'
       AND object_name = 'AMW_ORG_CONTROL'
       AND pk2_value = p_audit_project_id
       AND pk3_value = p_org_id
       AND exists (select 'Y' FROM amw_control_associations
		    WHERE object_type = 'PROJECT'
		      AND pk1 = p_audit_project_id
		      AND pk2 = p_org_id
		      AND control_id = pk1_value);

Line: 470

    SELECT count(pk1_value)
      FROM amw_opinions_v
     WHERE opinion_type_code = 'EVALUATION'
       AND object_name = 'AMW_ORG_CONTROL'
       AND audit_result_code <> 'EFFECTIVE'
       AND pk2_value = p_audit_project_id
       AND pk3_value = p_org_id
       AND exists (select 'Y' FROM amw_control_associations
		    WHERE object_type = 'PROJECT'
		      AND pk1 = p_audit_project_id
		      AND pk2 = p_org_id
		      AND control_id = pk1_value);

Line: 560

  UPDATE amw_audit_scope_organizations
     SET sub_orgs_evaluated	  = l_evaluated_sub_orgs,
         ineffective_sub_orgs	  = l_ineff_sub_orgs,
	 total_sub_orgs		  = l_total_sub_orgs,
	 processes_evaluated	  = l_evaluated_processes,
	 ineffective_processes	  = l_ineff_processes,
	 total_processes	  = l_total_processes,
	 unmitigated_risks        = l_unmitigated_risks,
	 risks_evaluated          = l_evaluated_risks,
	 total_risks              = l_total_risks,
	 ineffective_controls     = l_ineff_controls,
	 controls_evaluated       = l_evaluated_controls,
	 total_controls           = l_total_controls,
	 open_findings            = l_open_findings,
	 last_update_date 	  = SYSDATE,
	 last_updated_by          = G_USER_ID,
	 last_update_login        = G_LOGIN_ID
   WHERE audit_project_id         = p_audit_project_id
     AND organization_id          = p_org_id;

Line: 581

    INSERT INTO amw_audit_scope_organizations (
	   audit_project_id,
	   subsidiary_vs,
	   subsidiary_code,
	   lob_vs,
	   lob_code,
	   organization_id,
	   sub_orgs_evaluated,
	   ineffective_sub_orgs,
	   total_sub_orgs,
	   processes_evaluated,
	   ineffective_processes,
	   total_processes,
	   risks_evaluated,
	   unmitigated_risks,
	   total_risks,
	   controls_evaluated,
	   ineffective_controls,
	   total_controls,
	   open_findings,
	   created_by,
	   creation_date,
	   last_updated_by,
	   last_update_date,
	   last_update_login,
	   object_version_number)
    SELECT p_audit_project_id,
	   subsidiary_valueset,
	   company_code,
	   lob_valueset,
	   lob_code,
	   p_org_id,
	   l_evaluated_sub_orgs,
	   l_ineff_sub_orgs,
	   l_total_sub_orgs,
	   l_evaluated_processes,
	   l_ineff_processes,
	   l_total_processes,
	   l_evaluated_risks,
	   l_unmitigated_risks,
	   l_total_risks,
	   l_evaluated_controls,
	   l_ineff_controls,
	   l_total_controls,
	   l_open_findings,
	   g_user_id,
	   sysdate,
	   g_user_id,
	   sysdate,
	   g_login_id,
	   1
      FROM amw_audit_units_v
     WHERE organization_id = p_org_id;

Line: 638

    fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in update_org_summary_table'
	|| SUBSTR (SQLERRM, 1, 100), 1, 200));

Line: 642

    fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in update_org_summary_table'
	|| SUBSTR (SQLERRM, 1, 100), 1, 200));

Line: 644

END update_org_summary_table;

Line: 647

PROCEDURE update_proc_summary_table (
	  p_audit_project_id	IN 	NUMBER,
	  p_org_id 		IN 	NUMBER,
	  p_proc_id		IN	NUMBER
) IS

  CURSOR get_total_risks IS
    SELECT count(risk_id)
      FROM amw_risk_associations
     WHERE object_type = 'PROJECT'
       AND pk1 = p_audit_project_id
       AND pk2 = p_org_id
       AND pk3 IN (SELECT process_id
		     FROM amw_execution_scope
	       START WITH process_id = p_proc_id
		      AND organization_id = p_org_id
		      AND entity_id = p_audit_project_id
		      AND entity_type = 'PROJECT'
	 CONNECT BY PRIOR process_id = parent_process_id
		      AND organization_id = PRIOR organization_id
		      AND entity_id = PRIOR entity_id
                      AND entity_type = PRIOR entity_type);

Line: 671

    SELECT count(pk1_value)
      FROM amw_opinions_v
     WHERE opinion_type_code = 'EVALUATION'
       AND object_name = 'AMW_ORG_PROCESS_RISK'
       AND pk2_value = p_audit_project_id
       AND pk3_value = p_org_id
       AND pk4_value IN (SELECT process_id
		     FROM amw_execution_scope
	       START WITH process_id = p_proc_id
		      AND organization_id = p_org_id
		      AND entity_id = p_audit_project_id
		      AND entity_type = 'PROJECT'
	 CONNECT BY PRIOR process_id = parent_process_id
		      AND organization_id = PRIOR organization_id
		      AND entity_id = PRIOR entity_id
                      AND entity_type = PRIOR entity_type)
       AND exists (select 'Y' FROM amw_risk_associations
		    WHERE object_type = 'PROJECT'
		      AND pk1 = p_audit_project_id
		      AND pk2 = p_org_id
		      AND pk3 = pk4_value
		      AND risk_id = pk1_value);

Line: 695

    SELECT count(pk1_value)
      FROM amw_opinions_v
     WHERE opinion_type_code = 'EVALUATION'
       AND object_name = 'AMW_ORG_PROCESS_RISK'
       AND audit_result_code <> 'EFFECTIVE'
       AND pk2_value = p_audit_project_id
       AND pk3_value = p_org_id
       AND pk4_value IN (SELECT process_id
		     FROM amw_execution_scope
	       START WITH process_id = p_proc_id
		      AND organization_id = p_org_id
		      AND entity_id = p_audit_project_id
		      AND entity_type = 'PROJECT'
	 CONNECT BY PRIOR process_id = parent_process_id
		      AND organization_id = PRIOR organization_id
		      AND entity_id = PRIOR entity_id
                      AND entity_type = PRIOR entity_type)
      AND exists (select 'Y' FROM amw_risk_associations
		    WHERE object_type = 'PROJECT'
		      AND pk1 = p_audit_project_id
		      AND pk2 = p_org_id
		      AND pk3 = pk4_value
		      AND risk_id = pk1_value);

Line: 720

    SELECT count(distinct control_id)
      FROM amw_control_associations
     WHERE object_type = 'PROJECT'
       AND pk1 = p_audit_project_id
       AND pk2 = p_org_id
       AND pk3 IN (SELECT process_id
		     FROM amw_execution_scope
	       START WITH process_id = p_proc_id
		      AND organization_id = p_org_id
		      AND entity_id = p_audit_project_id
		      AND entity_type = 'PROJECT'
	 CONNECT BY PRIOR process_id = parent_process_id
		      AND organization_id = PRIOR organization_id
		      AND entity_id = PRIOR entity_id
                      AND entity_type = PRIOR entity_type);

Line: 737

    SELECT count(distinct opin.pk1_value)
      FROM amw_control_associations assoc, amw_opinions_v opin
     WHERE assoc.object_type = 'PROJECT'
       AND assoc.pk1 = p_audit_project_id
       AND assoc.pk2 = p_org_id
       AND assoc.pk3 IN (SELECT process_id
		     FROM amw_execution_scope
	       START WITH process_id = p_proc_id
		      AND organization_id = p_org_id
		      AND entity_id = p_audit_project_id
		      AND entity_type = 'PROJECT'
	 CONNECT BY PRIOR process_id = parent_process_id
		      AND organization_id = PRIOR organization_id
		      AND entity_id = PRIOR entity_id
                      AND entity_type = PRIOR entity_type)
       AND opin.opinion_type_code = 'EVALUATION'
       AND opin.object_name = 'AMW_ORG_CONTROL'
       AND opin.pk1_value = assoc.control_id
       AND opin.pk2_value = p_audit_project_id
       AND opin.pk3_value = p_org_id;

Line: 759

    SELECT count(distinct opin.pk1_value)
      FROM amw_control_associations assoc, amw_opinions_v opin
     WHERE assoc.object_type = 'PROJECT'
       AND assoc.pk1 = p_audit_project_id
       AND assoc.pk2 = p_org_id
       AND assoc.pk3 IN (SELECT process_id
		     FROM amw_execution_scope
	       START WITH process_id = p_proc_id
		      AND organization_id = p_org_id
		      AND entity_id = p_audit_project_id
		      AND entity_type = 'PROJECT'
	 CONNECT BY PRIOR process_id = parent_process_id
		      AND organization_id = PRIOR organization_id
		      AND entity_id = PRIOR entity_id
                      AND entity_type = PRIOR entity_type)
       AND opin.opinion_type_code = 'EVALUATION'
       AND opin.object_name = 'AMW_ORG_CONTROL'
       AND opin.audit_result_code <> 'EFFECTIVE'
       AND opin.pk1_value = assoc.control_id
       AND opin.pk2_value = p_audit_project_id
       AND opin.pk3_value = p_org_id;

Line: 827

  UPDATE amw_audit_scope_processes
     SET unmitigated_risks        = l_unmitigated_risks,
	 risks_evaluated          = l_evaluated_risks,
	 total_risks              = l_total_risks,
	 ineffective_controls     = l_ineff_controls,
	 controls_evaluated       = l_evaluated_controls,
	 total_controls           = l_total_controls,
	 open_findings            = l_open_findings,
	 last_update_date 	  = SYSDATE,
	 last_updated_by          = G_USER_ID,
	 last_update_login        = G_LOGIN_ID
   WHERE audit_project_id = p_audit_project_id
     AND organization_id = p_org_id
     AND process_id = p_proc_id;

Line: 843

    INSERT INTO amw_audit_scope_processes (
	   audit_project_id,
	   organization_id,
	   process_id,
	   risks_evaluated,
	   unmitigated_risks,
	   total_risks,
	   controls_evaluated,
	   ineffective_controls,
	   total_controls,
	   open_findings,
	   created_by,
	   creation_date,
	   last_updated_by,
	   last_update_date,
	   last_update_login,
	   object_version_number)
    SELECT p_audit_project_id,
	   p_org_id,
	   p_proc_id,
	   l_evaluated_risks,
	   l_unmitigated_risks,
	   l_total_risks,
	   l_evaluated_controls,
	   l_ineff_controls,
	   l_total_controls,
	   l_open_findings,
	   g_user_id,
	   sysdate,
	   g_user_id,
	   sysdate,
	   g_login_id,
	   1
      FROM dual;

Line: 881

    fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in update_proc_summary_table'
	|| SUBSTR (SQLERRM, 1, 100), 1, 200));

Line: 885

    fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in update_proc_summary_table'
	|| SUBSTR (SQLERRM, 1, 100), 1, 200));

Line: 887

END update_proc_summary_table;

Line: 898

    Select audit_project_id
    from AMW_AUDIT_PROJECTS
    where AUDIT_PROJECT_STATUS = 'ACTI';

Line: 903

    SELECT organization_id
      FROM AMW_AUDIT_SCOPE_ORGANIZATIONS
     WHERE audit_project_id = l_audit_project_id;

Line: 908

    SELECT process_id
      FROM AMW_AUDIT_SCOPE_PROCESSES
     WHERE audit_project_id = l_audit_project_id
       AND organization_id = l_org_id;

Line: 923

      update_org_summary_table(p_audit_project_id,org_rec.organization_id);

Line: 926

        update_proc_summary_table(p_audit_project_id,org_rec.organization_id,proc_rec.process_id);

Line: 932

        update_org_summary_table(eng_rec.audit_project_id,org_rec.organization_id);

Line: 935

          update_proc_summary_table(eng_rec.audit_project_id,org_rec.organization_id,proc_rec.process_id);

Line: 953

FUNCTION Update_Eng_Sign_Off_Status
( p_subscription_guid   in     raw,
  p_event               in out NOCOPY wf_event_t
) return VARCHAR2
IS

 l_key                      varchar2(240) := p_event.GetEventKey();

Line: 976

    select change_mgmt_type_code into l_change_mgmt_type_code
    from eng_engineering_changes
    where change_id =  l_change_id
    and organization_id = -1
    and rownum < 2;

Line: 982

    select pk1_value into l_audit_project_id
    from eng_change_subjects_v
    where entity_name = 'PROJECT'
      and change_id = l_change_id;

Line: 1007

    /* Update the signOffStatus. */
    UPDATE AMW_AUDIT_PROJECTS
    SET sign_off_status = l_sign_off_status
    WHERE AUDIT_PROJECT_ID = l_audit_project_id;

Line: 1012

    /* update the Engagement status */
    IF l_sign_off_status = 'APPROVED' THEN
      UPDATE AMW_AUDIT_PROJECTS
      SET audit_project_status = 'SIGN'
      WHERE AUDIT_PROJECT_ID = l_audit_project_id
        AND AUDIT_PROJECT_STATUS = 'ACTI';

Line: 1027

     WF_CORE.CONTEXT('AMW_PROJECT_EVENT_PVT', 'Update_Eng_Sign_Off_Status', p_event.getEventName(), p_subscription_guid);

Line: 1031

END Update_Eng_Sign_Off_Status;

Line: 1034

procedure UPDATE_SIGNOFF_STATUS(
   p_change_id 			in number
  ,p_base_change_mgmt_type_code in varchar2
  ,p_new_approval_status_code   in varchar2
  ,p_workflow_status_code	in varchar2
  ,x_return_status		out nocopy varchar2
  ,x_msg_count			out nocopy number
  ,x_msg_data 			out nocopy varchar2
)
is

 l_audit_project_id         NUMBER;

Line: 1053

    select pk1_value into l_audit_project_id
    from eng_change_subjects_v
    where entity_name = 'PROJECT'
      and change_id = p_change_id;

Line: 1076

    /* Update the signOffStatus. */
    UPDATE AMW_AUDIT_PROJECTS
    SET sign_off_status = l_sign_off_status
    WHERE AUDIT_PROJECT_ID = l_audit_project_id;

Line: 1081

    /* update the Engagement status */
    IF l_sign_off_status = 'APPROVED' THEN
      UPDATE AMW_AUDIT_PROJECTS
      SET audit_project_status = 'SIGN'
      WHERE AUDIT_PROJECT_ID = l_audit_project_id
        AND AUDIT_PROJECT_STATUS = 'ACTI';

Line: 1099

end UPDATE_SIGNOFF_STATUS;