APPS.AMW_PROCESS_CERT_SCOPE_PVT SQL Statements

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

Line: 21

PROCEDURE Insert_Process(
    p_level_id       IN NUMBER,
	p_parent_process_id  IN NUMBER,
	p_top_process_id    IN NUMBER,
	p_subsidiary_vs  IN VARCHAR2,
	p_subsidiary_code IN VARCHAR2,
	p_lob_vs          IN VARCHAR2,
	p_lob_code        IN VARCHAR2,
	p_organization_id IN NUMBER,
	p_certification_id IN NUMBER
) IS
       CURSOR c_process IS
           SELECT apv.child_process_id process_id
	   FROM AMW_CURR_APP_HIERARCHY_ORG_V apv
	   WHERE apv.PARENT_PROCESS_ID = p_parent_process_id
	   and apv.child_organization_id = p_organization_id;

Line: 39

        Insert_Process (p_level_id+1,proc_rec.process_id,p_top_process_id,p_subsidiary_vs,
		                p_subsidiary_code,p_lob_vs,p_lob_code,p_organization_id,p_certification_id);

Line: 41

        INSERT INTO AMW_EXECUTION_SCOPE (
	       EXECUTION_SCOPE_ID,
		   ENTITY_TYPE,
		   ENTITY_ID,
		   CREATED_BY,
		   CREATION_DATE,
		   LAST_UPDATE_DATE,
		   LAST_UPDATED_BY,
		   LAST_UPDATE_LOGIN,
		   SCOPE_CHANGED_STATUS,
		   LEVEL_ID,
		   SUBSIDIARY_VS,
		   SUBSIDIARY_CODE,
		   LOB_VS,
		   LOB_CODE,
		   ORGANIZATION_ID,
		   PROCESS_ID,
		   TOP_PROCESS_ID,
		   PARENT_PROCESS_ID)
	SELECT amw_execution_scope_s.nextval,
 	       'PROCESS_CERTIFICATION',
		   p_certification_id,
		   FND_GLOBAL.USER_ID,
		   SYSDATE,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   FND_GLOBAL.USER_ID,
		   'C',
		   p_level_id,
		   p_subsidiary_vs,
		   p_subsidiary_code,
		   p_lob_vs,
		   p_lob_code,
		   p_organization_id,
		   proc_rec.process_id,
		   p_top_process_id,
		   p_parent_process_id
         FROM DUAL;

Line: 80

END Insert_Process;

Line: 82

PROCEDURE Insert_Audit_Units(
    p_api_version_number        IN       NUMBER   := 1.0,
    p_init_msg_list             IN       VARCHAR2 := FND_API.g_false,
    p_commit                    IN       VARCHAR2 := FND_API.g_false,
    p_validation_level          IN       NUMBER := fnd_api.g_valid_level_full,
    p_certification_id	        IN	     NUMBER,
	x_return_status             OUT      nocopy VARCHAR2,
    x_msg_count                 OUT      nocopy NUMBER,
    x_msg_data                  OUT      nocopy VARCHAR2
)
  IS
       CURSOR c_audit_unit IS
       SELECT audit_v.company_code,audit_v.subsidiary_valueset,
	          audit_v.lob_code,audit_v.lob_valueset,
			  audit_v.organization_id
	   FROM amw_audit_units_v audit_v;

Line: 100

	    SELECT org_v.child_process_id as top_process_id,
	          org_v.child_organization_id as organization_id,
			  audit_v.company_code,audit_v.subsidiary_valueset,
	          audit_v.lob_code,audit_v.lob_valueset
	   FROM   AMW_CURR_APP_HIERARCHY_ORG_V org_v,
	          amw_audit_units_v audit_v
	   where org_v.parent_process_id = -2
	   and audit_v.organization_id = org_v.child_organization_id;

Line: 108

	   l_api_name VARCHAR2(150) := 'Insert_Audit_Units';

Line: 113

    SAVEPOINT INSERT_AUDIT_UNITS_PVT;

Line: 115

	delete from AMW_EXECUTION_SCOPE
	       where entity_id = p_certification_id
		   and entity_type = 'PROCESS_CERTIFICATION';

Line: 122

        INSERT INTO AMW_EXECUTION_SCOPE (
	       EXECUTION_SCOPE_ID,
		   ENTITY_TYPE,
		   ENTITY_ID,
		   CREATED_BY,
		   CREATION_DATE,
		   LAST_UPDATE_DATE,
		   LAST_UPDATED_BY,
		   LAST_UPDATE_LOGIN,
		   SCOPE_CHANGED_STATUS,
		   LEVEL_ID,
		   SUBSIDIARY_VS,
		   SUBSIDIARY_CODE,
		   LOB_VS,
		   LOB_CODE,
		   ORGANIZATION_ID,
		   PROCESS_ID,
		   TOP_PROCESS_ID,
		   PARENT_PROCESS_ID)
	SELECT amw_execution_scope_s.nextval,
 	       'PROCESS_CERTIFICATION',
		   p_certification_id,
		   FND_GLOBAL.USER_ID,
		   SYSDATE,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   FND_GLOBAL.USER_ID,
		   'C',
		   1,
		   audit_rec.subsidiary_valueset,
		   audit_rec.company_code,
		   null,
		   null,
		   null,
		   null,
		   null,
		   null
         FROM DUAL
		      WHERE not exists (SELECT 'Y'
		           FROM AMW_EXECUTION_SCOPE
		           WHERE entity_type='PROCESS_CERTIFICATION'
		           AND entity_id= p_certification_id
			       AND subsidiary_vs =  audit_rec.subsidiary_valueset
			       AND subsidiary_code= audit_rec.company_code
			       AND level_id=1);

Line: 169

        INSERT INTO AMW_EXECUTION_SCOPE (
	       EXECUTION_SCOPE_ID,
		   ENTITY_TYPE,
		   ENTITY_ID,
		   CREATED_BY,
		   CREATION_DATE,
		   LAST_UPDATE_DATE,
		   LAST_UPDATED_BY,
		   LAST_UPDATE_LOGIN,
		   SCOPE_CHANGED_STATUS,
		   LEVEL_ID,
		   SUBSIDIARY_VS,
		   SUBSIDIARY_CODE,
		   LOB_VS,
		   LOB_CODE,
		   ORGANIZATION_ID,
		   PROCESS_ID,
		   TOP_PROCESS_ID,
		   PARENT_PROCESS_ID)
	SELECT amw_execution_scope_s.nextval,
 	       'PROCESS_CERTIFICATION',
		   p_certification_id,
		   FND_GLOBAL.USER_ID,
		   SYSDATE,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   FND_GLOBAL.USER_ID,
		   'C',
		   2,
		   audit_rec.subsidiary_valueset,
		   audit_rec.company_code,
		   audit_rec.lob_valueset,
		   audit_rec.lob_code,
		   null,
		   null,
		   null,
		   null
         FROM DUAL
		           WHERE not exists (SELECT 'Y'
		           FROM AMW_EXECUTION_SCOPE
		           WHERE entity_type='PROCESS_CERTIFICATION'
		           AND entity_id= p_certification_id
			       AND subsidiary_vs =  audit_rec.subsidiary_valueset
			       AND subsidiary_code= audit_rec.company_code
				   AND lob_vs = audit_rec.lob_valueset
				   AND lob_code = audit_rec.lob_code
			       AND level_id=2);

Line: 218

        INSERT INTO AMW_EXECUTION_SCOPE (
	       EXECUTION_SCOPE_ID,
		   ENTITY_TYPE,
		   ENTITY_ID,
		   CREATED_BY,
		   CREATION_DATE,
		   LAST_UPDATE_DATE,
		   LAST_UPDATED_BY,
		   LAST_UPDATE_LOGIN,
		   SCOPE_CHANGED_STATUS,
		   LEVEL_ID,
		   SUBSIDIARY_VS,
		   SUBSIDIARY_CODE,
		   LOB_VS,
		   LOB_CODE,
		   ORGANIZATION_ID,
		   PROCESS_ID,
		   TOP_PROCESS_ID,
		   PARENT_PROCESS_ID)
	SELECT amw_execution_scope_s.nextval,
 	       'PROCESS_CERTIFICATION',
		   p_certification_id,
		   FND_GLOBAL.USER_ID,
		   SYSDATE,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   FND_GLOBAL.USER_ID,
		   'C',
		   3,
		   audit_rec.subsidiary_valueset,
		   audit_rec.company_code,
		   audit_rec.lob_valueset,
		   audit_rec.lob_code,
		   audit_rec.organization_id,
		   null,
		   null,
		   null
         FROM DUAL
		           WHERE not exists (SELECT 'Y'
		           FROM AMW_EXECUTION_SCOPE
		           WHERE entity_type='PROCESS_CERTIFICATION'
		           AND entity_id= p_certification_id
			       AND subsidiary_vs =  audit_rec.subsidiary_valueset
			       AND subsidiary_code= audit_rec.company_code
				   AND lob_vs = audit_rec.lob_valueset
				   AND lob_code = audit_rec.lob_code
				   AND organization_id = audit_rec.organization_id
			       AND level_id=3);

Line: 270

	      INSERT INTO AMW_EXECUTION_SCOPE (
	       EXECUTION_SCOPE_ID,
		   ENTITY_TYPE,
		   ENTITY_ID,
		   CREATED_BY,
		   CREATION_DATE,
		   LAST_UPDATE_DATE,
		   LAST_UPDATED_BY,
		   LAST_UPDATE_LOGIN,
		   SCOPE_CHANGED_STATUS,
		   LEVEL_ID,
		   SUBSIDIARY_VS,
		   SUBSIDIARY_CODE,
		   LOB_VS,
		   LOB_CODE,
		   ORGANIZATION_ID,
		   PROCESS_ID,
		   TOP_PROCESS_ID,
		   PARENT_PROCESS_ID)
	SELECT amw_execution_scope_s.nextval,
 	       'PROCESS_CERTIFICATION',
		   p_certification_id,
		   FND_GLOBAL.USER_ID,
		   SYSDATE,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   FND_GLOBAL.USER_ID,
		   'C',
		   4,
		   org_process_rec.subsidiary_valueset,
		   org_process_rec.company_code,
		   org_process_rec.lob_valueset,
		   org_process_rec.lob_code,
		   org_process_rec.organization_id,
		   org_process_rec.top_process_id,
		   org_process_rec.top_process_id,
		   -1
         FROM DUAL
		           WHERE not exists (SELECT 'Y'
		           FROM AMW_EXECUTION_SCOPE
		           WHERE entity_type='PROCESS_CERTIFICATION'
		           AND entity_id= p_certification_id
			       AND subsidiary_vs =  org_process_rec.subsidiary_valueset
			       AND subsidiary_code= org_process_rec.company_code
				   AND lob_vs = org_process_rec.lob_valueset
				   AND lob_code = org_process_rec.lob_code
				   AND process_id = org_process_rec.top_process_id
			       AND level_id=4);

Line: 319

	   -- Insert All the processes in the process Hierarchy using the top_process_id's
	      Insert_Process(5,org_process_rec.top_process_id,org_process_rec.top_process_id,org_process_rec.subsidiary_valueset,
		                 org_process_rec.company_code,org_process_rec.lob_valueset,org_process_rec.lob_code,
						 org_process_rec.organization_id,p_certification_id);

Line: 326

    rollback to INSERT_AUDIT_UNITS_PVT;

Line: 333

END Insert_Audit_Units;

Line: 339

PROCEDURE insert_specific_audit_units(
    p_api_version_number        IN       NUMBER := 1.0,
    p_init_msg_list             IN       VARCHAR2 := FND_API.g_false,
    p_commit                    IN       VARCHAR2 := FND_API.g_false,
    p_validation_level          IN       NUMBER := fnd_api.g_valid_level_full,
    p_certification_id		    IN	     NUMBER,
    p_org_tbl                   IN       AMW_POPULATE_HIERARCHIES_PVT.g_org_tbl%TYPE,
    p_process_tbl               IN       AMW_POPULATE_HIERARCHIES_PVT.g_process_tbl%TYPE,
    x_return_status             OUT      nocopy VARCHAR2,
    x_msg_count                 OUT      nocopy NUMBER,
    x_msg_data                  OUT      nocopy VARCHAR2
) IS

        CURSOR c_audit_unit(p_org_id NUMBER) IS
        SELECT audit_v.company_code,audit_v.subsidiary_valueset,
	          audit_v.lob_code,audit_v.lob_valueset,
	          audit_v.organization_id
        FROM amw_audit_units_v audit_v
        WHERE organization_id = p_org_id;

Line: 359

	    l_api_name VARCHAR2(150) := 'Insert_Specific_Audit_Units';

Line: 366

           'SELECT org_v.child_process_id as top_process_id,
	           org_v.child_organization_id as organization_id,
    		   audit_v.company_code,
                   audit_v.subsidiary_valueset,
	           audit_v.lob_code,
                   audit_v.lob_valueset
    	   FROM   AMW_CURR_APP_HIERARCHY_ORG_V org_v,
	          amw_audit_units_v audit_v
	   WHERE org_v.parent_process_id = -2
    	   AND audit_v.organization_id = org_v.child_organization_id
           AND audit_v.organization_id =';

Line: 390

    SAVEPOINT INSERT_SPEC_AUDIT_UNITS_PVT;

Line: 410

	DELETE FROM AMW_EXECUTION_SCOPE
	WHERE entity_id = p_certification_id
	AND entity_type = 'PROCESS_CERTIFICATION';

Line: 420

			INSERT INTO AMW_EXECUTION_SCOPE (
				EXECUTION_SCOPE_ID,
				ENTITY_TYPE,
				ENTITY_ID,
				CREATED_BY,
				CREATION_DATE,
				LAST_UPDATE_DATE,
				LAST_UPDATED_BY,
				LAST_UPDATE_LOGIN,
				SCOPE_CHANGED_STATUS,
				LEVEL_ID,
				SUBSIDIARY_VS,
				SUBSIDIARY_CODE,
				LOB_VS,
				LOB_CODE,
				ORGANIZATION_ID,
				PROCESS_ID,
				TOP_PROCESS_ID,
				PARENT_PROCESS_ID)
			SELECT 	amw_execution_scope_s.nextval,
				'PROCESS_CERTIFICATION',
				p_certification_id,
				FND_GLOBAL.USER_ID,
				SYSDATE,
				SYSDATE,
				FND_GLOBAL.USER_ID,
				FND_GLOBAL.USER_ID,
				'C',
				1,
				audit_rec.subsidiary_valueset,
				audit_rec.company_code,
				null,
				null,
				null,
				null,
				null,
				null
			FROM DUAL
			WHERE not exists (SELECT 'Y'
					FROM AMW_EXECUTION_SCOPE
					WHERE entity_type='PROCESS_CERTIFICATION'
					AND entity_id= p_certification_id
					AND subsidiary_vs =  audit_rec.subsidiary_valueset
					AND subsidiary_code= audit_rec.company_code
					AND level_id=1);

Line: 466

			INSERT INTO AMW_EXECUTION_SCOPE (
				EXECUTION_SCOPE_ID,
				ENTITY_TYPE,
				ENTITY_ID,
				CREATED_BY,
				CREATION_DATE,
				LAST_UPDATE_DATE,
				LAST_UPDATED_BY,
				LAST_UPDATE_LOGIN,
				SCOPE_CHANGED_STATUS,
				LEVEL_ID,
				SUBSIDIARY_VS,
				SUBSIDIARY_CODE,
				LOB_VS,
				LOB_CODE,
				ORGANIZATION_ID,
				PROCESS_ID,
				TOP_PROCESS_ID,
				PARENT_PROCESS_ID)
			SELECT  amw_execution_scope_s.nextval,
				'PROCESS_CERTIFICATION',
				p_certification_id,
				FND_GLOBAL.USER_ID,
				SYSDATE,
				SYSDATE,
				FND_GLOBAL.USER_ID,
				FND_GLOBAL.USER_ID,
				'C',
				2,
				audit_rec.subsidiary_valueset,
				audit_rec.company_code,
				audit_rec.lob_valueset,
				audit_rec.lob_code,
				null,
				null,
				null,
				null
			FROM DUAL
			WHERE not exists (SELECT 'Y'
					FROM AMW_EXECUTION_SCOPE
					WHERE entity_type='PROCESS_CERTIFICATION'
					AND entity_id= p_certification_id
					AND subsidiary_vs =  audit_rec.subsidiary_valueset
					AND subsidiary_code= audit_rec.company_code
					AND lob_vs = audit_rec.lob_valueset
					AND lob_code = audit_rec.lob_code
					AND level_id=2);

Line: 514

			INSERT INTO AMW_EXECUTION_SCOPE (
				EXECUTION_SCOPE_ID,
				ENTITY_TYPE,
				ENTITY_ID,
				CREATED_BY,
				CREATION_DATE,
				LAST_UPDATE_DATE,
				LAST_UPDATED_BY,
				LAST_UPDATE_LOGIN,
				SCOPE_CHANGED_STATUS,
				LEVEL_ID,
				SUBSIDIARY_VS,
				SUBSIDIARY_CODE,
				LOB_VS,
				LOB_CODE,
				ORGANIZATION_ID,
				PROCESS_ID,
				TOP_PROCESS_ID,
				PARENT_PROCESS_ID)
			SELECT  amw_execution_scope_s.nextval,
				'PROCESS_CERTIFICATION',
				p_certification_id,
				FND_GLOBAL.USER_ID,
				SYSDATE,
				SYSDATE,
				FND_GLOBAL.USER_ID,
				FND_GLOBAL.USER_ID,
				'C',
				3,
				audit_rec.subsidiary_valueset,
				audit_rec.company_code,
				audit_rec.lob_valueset,
				audit_rec.lob_code,
				audit_rec.organization_id,
				null,
				null,
				null
			FROM DUAL
			WHERE not exists (SELECT 'Y'
					FROM AMW_EXECUTION_SCOPE
					WHERE entity_type='PROCESS_CERTIFICATION'
					AND entity_id= p_certification_id
					AND subsidiary_vs =  audit_rec.subsidiary_valueset
					AND subsidiary_code= audit_rec.company_code
					AND lob_vs = audit_rec.lob_valueset
					AND lob_code = audit_rec.lob_code
					AND organization_id = audit_rec.organization_id
					AND level_id=3);

Line: 591

			INSERT INTO AMW_EXECUTION_SCOPE (
				EXECUTION_SCOPE_ID,
				ENTITY_TYPE,
				ENTITY_ID,
				CREATED_BY,
				CREATION_DATE,
				LAST_UPDATE_DATE,
				LAST_UPDATED_BY,
				LAST_UPDATE_LOGIN,
				SCOPE_CHANGED_STATUS,
				LEVEL_ID,
				SUBSIDIARY_VS,
				SUBSIDIARY_CODE,
				LOB_VS,
				LOB_CODE,
				ORGANIZATION_ID,
				PROCESS_ID,
				TOP_PROCESS_ID,
				PARENT_PROCESS_ID)
			SELECT amw_execution_scope_s.nextval,
				'PROCESS_CERTIFICATION',
				p_certification_id,
				FND_GLOBAL.USER_ID,
				SYSDATE,
				SYSDATE,
				FND_GLOBAL.USER_ID,
				FND_GLOBAL.USER_ID,
				'C',
				4,
				l_subsidiary_valueset,
				l_company_code,
				l_lob_valueset,
				l_lob_code,
				l_organization_id,
				l_process_id,
				l_process_id,
				-1
			FROM DUAL
			WHERE not exists (SELECT 'Y'
					FROM AMW_EXECUTION_SCOPE
					WHERE entity_type='PROCESS_CERTIFICATION'
					AND entity_id= p_certification_id
					AND subsidiary_vs =  l_subsidiary_valueset
					AND subsidiary_code= l_company_code
					AND lob_vs = l_lob_valueset
					AND lob_code = l_lob_code
					AND process_id = l_process_id
					AND level_id=4);

Line: 640

			-- Insert All the processes in the process Hierarchy using the top_process_id's
			Insert_Process(5,
				       l_process_id,
				       l_process_id,
				       l_subsidiary_valueset,
				       l_company_code,
				       l_lob_valueset,
				       l_lob_code,
				       l_organization_id,
				       p_certification_id);

Line: 658

		rollback to INSERT_SPEC_AUDIT_UNITS_PVT;

Line: 665

	END insert_specific_audit_units;