APPS.AMW_SCOPE_PVT SQL Statements

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

Line: 20

PROCEDURE raise_scope_update_event(
		p_entity_type	IN VARCHAR2,
		p_entity_id	IN NUMBER,
		p_org_id	IN NUMBER := NULL,
		p_mode		IN VARCHAR2)
IS
  l_item_key         wf_items.ITEM_KEY%TYPE;

Line: 30

  SELECT to_char(amw_scope_event_s.nextval)
    INTO l_item_key
    FROM dual;

Line: 51

	 p_event_name     => 'oracle.apps.amw.proccert.scope.update',
	 p_event_key      => l_item_key,
	 p_parameters     => l_parameter_list);

Line: 60

	 p_event_name     => 'oracle.apps.amw.engagement.scope.update',
	 p_event_key      => l_item_key,
	 p_parameters     => l_parameter_list);

Line: 65

END raise_scope_update_event;

Line: 125

SELECT flv.flex_value_id
FROM amw_audit_units_v auv,fnd_flex_values flv
WHERE auv.subsidiary_valueset = flv.flex_value_set_id
AND   auv.company_code = flv.flex_value
AND   organization_id = l_organization_id;

Line: 132

SELECT flv.flex_value_id
FROM amw_audit_units_v auv,fnd_flex_values flv
WHERE auv.lob_valueset = flv.flex_value_set_id
AND   auv.lob_code = flv.flex_value
AND   organization_id = l_organization_id;

Line: 139

SELECT 'Y'
FROM AMW_AUDIT_UNITS_V
--WHERE LOB_valueset = p_LOB_vs
WHERE LOB_valueset IS NOT NULL
AND organization_id = l_organization_id;

Line: 146

SELECT  nvl( flv2.flex_value_id, -1) parent_id
FROM fnd_flex_values_vl flv, FND_FLEX_VALUE_CHILDREN_V fchild,fnd_flex_values_vl flv2
WHERE flv.flex_value = fchild.flex_value (+)
AND flv.flex_value_set_id = fchild.flex_value_set_id (+)
AND flv2.flex_value(+) = fchild.parent_flex_value
AND flv2.flex_value_set_id(+) = fchild.flex_value_set_id
AND flv.flex_value_id = l_child_id;

Line: 155

SELECT distinct object_id
FROM AMW_ENTITY_HIERARCHIES
WHERE entity_type = p_entity_type
AND entity_id = p_entity_id
AND delete_flag = 'Y'
AND object_type = l_object_type;

Line: 186

	-- Whenever there is a change to hierarchy, the system will delete the existing hierarchy and rebuild it.
	-- To begin with mark all records in the table as DELETED
	UPDATE AMW_ENTITY_HIERARCHIES
	SET DELETE_FLAG = 'Y'
	WHERE ENTITY_ID = p_entity_id
	AND ENTITY_TYPE = p_entity_type;

Line: 194

	-- The new list is existing orgs UNION newly selected orgs
	generate_organization_list
	(p_entity_id    => p_entity_id,
	p_entity_type  => p_entity_type,
	p_org_tbl      => p_org_tbl,
	p_org_new_tbl  => p_org_new_tbl);

Line: 203

		delete from amw_execution_scope where entity_type = p_entity_type
		and entity_id = p_entity_id and organization_id = p_org_tbl(each_rec).org_id;

Line: 244

	--Step 11: Populate selected CustomORGS into table and build hierarchy
	--Step 12: Call to populate amw_execution_scope with processes/org relation
	--Step 13: Call to populate association tables with object_type = 'BUSIPROC_CERTIFICATION'

	--Step 1 : Find all relevant organizations
	-- loop through all organizations in the list
	FOR each_rec IN 1..p_org_new_tbl.count LOOP

		found_sub_parent := false;

Line: 261

		INSERT INTO AMW_ENTITY_HIERARCHIES
			(ENTITY_HIERARCHY_ID,
			 ENTITY_TYPE,
			 ENTITY_ID,
			 CREATED_BY,
			 CREATION_DATE,
			 LAST_UPDATE_DATE,
			 LAST_UPDATED_BY,
             		 LAST_UPDATE_LOGIN,
			 OBJECT_TYPE,
			 OBJECT_ID,
			 PARENT_OBJECT_TYPE,
			 PARENT_OBJECT_ID,
			 LEVEL_ID)
		 SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
			p_entity_type,
			p_entity_id,
			FND_GLOBAL.USER_ID,
			SYSDATE,
			SYSDATE,
			FND_GLOBAL.USER_ID,
			FND_GLOBAL.USER_ID,
			'ORGANIZATION',
			p_org_new_tbl(each_rec).org_id,
			'SUBSIDIARY',
			l_temp_sub_parent_id,
			1
		FROM dual;

Line: 305

			INSERT INTO AMW_ENTITY_HIERARCHIES
			(ENTITY_HIERARCHY_ID,
			 ENTITY_TYPE,
			 ENTITY_ID,
			 CREATED_BY,
			 CREATION_DATE,
			 LAST_UPDATE_DATE,
			 LAST_UPDATED_BY,
             		 LAST_UPDATE_LOGIN,
			 OBJECT_TYPE,
			 OBJECT_ID,
			 PARENT_OBJECT_TYPE,
			 PARENT_OBJECT_ID,
			 LEVEL_ID )
			SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
			p_entity_type,
			p_entity_id,
			FND_GLOBAL.USER_ID,
			SYSDATE,
			SYSDATE,
			FND_GLOBAL.USER_ID,
			FND_GLOBAL.USER_ID,
			'ORGANIZATION',
			p_org_new_tbl(each_rec).org_id,
			'LINEOFBUSINESS',
			l_temp_parent_id,
			1
			FROM dual;

Line: 337

			INSERT INTO AMW_ENTITY_HIERARCHIES
				(ENTITY_HIERARCHY_ID,
				 ENTITY_TYPE,
				 ENTITY_ID,
				 CREATED_BY,
				 CREATION_DATE,
				 LAST_UPDATE_DATE,
			 	 LAST_UPDATED_BY,
                 	  	 LAST_UPDATE_LOGIN,
				 OBJECT_TYPE,
				 OBJECT_ID,
				 PARENT_OBJECT_TYPE,
				 PARENT_OBJECT_ID,
				 LEVEL_ID )
			 SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
				p_entity_type,
				p_entity_id,
				FND_GLOBAL.USER_ID,
				SYSDATE,
				SYSDATE,
				FND_GLOBAL.USER_ID,
				FND_GLOBAL.USER_ID,
				'ORGANIZATION',
				p_org_new_tbl(each_rec).org_id,
				'DUMMYLOB',
				-999,
				1
				FROM dual;

Line: 414

			INSERT INTO AMW_ENTITY_HIERARCHIES
				(ENTITY_HIERARCHY_ID,
				 ENTITY_TYPE,
				 ENTITY_ID,
				 CREATED_BY,
				 CREATION_DATE,
				 LAST_UPDATE_DATE,
				 LAST_UPDATED_BY,
				 LAST_UPDATE_LOGIN,
				 OBJECT_TYPE,
				 OBJECT_ID,
				 PARENT_OBJECT_TYPE,
				 PARENT_OBJECT_ID,
				 LEVEL_ID )
			SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
				p_entity_type,
				p_entity_id,
				FND_GLOBAL.USER_ID,
				SYSDATE,
				SYSDATE,
				FND_GLOBAL.USER_ID,
				FND_GLOBAL.USER_ID,
				'SUBSIDIARY',
				p_sub_new_tbl(each_sub).subsidiary_id,
				'SUBSIDIARY',
				l_temp_sub_parent_id,
				1
				FROM dual;

Line: 445

			INSERT INTO AMW_ENTITY_HIERARCHIES
				(ENTITY_HIERARCHY_ID,
				 ENTITY_TYPE,
				 ENTITY_ID,
				 CREATED_BY,
				 CREATION_DATE,
				 LAST_UPDATE_DATE,
				 LAST_UPDATED_BY,
				 LAST_UPDATE_LOGIN,
				 OBJECT_TYPE,
				 OBJECT_ID,
				 PARENT_OBJECT_TYPE,
				 PARENT_OBJECT_ID,
				 LEVEL_ID )
			SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
				p_entity_type,
				p_entity_id,
				FND_GLOBAL.USER_ID,
				SYSDATE,
				SYSDATE,
				FND_GLOBAL.USER_ID,
				FND_GLOBAL.USER_ID,
				'SUBSIDIARY',
				p_sub_new_tbl(each_sub).subsidiary_id,
				'ROOTNODE',
				-1,
				1
				FROM dual;

Line: 523

			INSERT INTO AMW_ENTITY_HIERARCHIES
				(ENTITY_HIERARCHY_ID,
				 ENTITY_TYPE,
				 ENTITY_ID,
				 CREATED_BY,
				 CREATION_DATE,
				 LAST_UPDATE_DATE,
				 LAST_UPDATED_BY,
				 LAST_UPDATE_LOGIN,
				 OBJECT_TYPE,
				 OBJECT_ID,
				 PARENT_OBJECT_TYPE,
				 PARENT_OBJECT_ID,
				 LEVEL_ID )
			SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
				p_entity_type,
				p_entity_id,
				FND_GLOBAL.USER_ID,
				SYSDATE,
				SYSDATE,
				FND_GLOBAL.USER_ID,
				FND_GLOBAL.USER_ID,
				'LINEOFBUSINESS',
				p_lob_new_tbl(each_lob).lob_id,
				'LINEOFBUSINESS',
				l_temp_lob_parent_id,
				1
				FROM dual;

Line: 554

			INSERT INTO AMW_ENTITY_HIERARCHIES
				(ENTITY_HIERARCHY_ID,
				 ENTITY_TYPE,
				 ENTITY_ID,
				 CREATED_BY,
				 CREATION_DATE,
				 LAST_UPDATE_DATE,
				 LAST_UPDATED_BY,
				 LAST_UPDATE_LOGIN,
				 OBJECT_TYPE,
				 OBJECT_ID,
				 PARENT_OBJECT_TYPE,
				 PARENT_OBJECT_ID,
				 LEVEL_ID )
			SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
				p_entity_type,
				p_entity_id,
				FND_GLOBAL.USER_ID,
				SYSDATE,
				SYSDATE,
				FND_GLOBAL.USER_ID,
				FND_GLOBAL.USER_ID,
				'LINEOFBUSINESS',
				p_lob_new_tbl(each_lob).lob_id,
				'ROOTNODE',
				-1,
				1
				FROM dual;

Line: 587

	--Step 11: Populate selected CustomORGS into table and build hierarchy
	--Note that CUSTOM ORGS will be stored with OBJECT TYPE as 'ORG'
    	populate_custom_hierarchy
    	(
        p_org_tbl	=>  p_org_new_tbl,
        p_entity_id     =>  p_entity_id,
        p_entity_type   =>  p_entity_type
    	);

Line: 651

	DELETE FROM AMW_ENTITY_HIERARCHIES
	WHERE DELETE_FLAG = 'Y'
	AND ENTITY_ID = p_entity_id
	AND ENTITY_TYPE = p_entity_type;

Line: 656

        raise_scope_update_event(
		p_entity_type	=> p_entity_type,
		p_entity_id	=> p_entity_id,
		p_mode		=> 'AddToScope');

Line: 692

SELECT organization_id_parent
FROM per_org_structure_elements
WHERE org_structure_version_id =(SELECT org_structure_version_id
				FROM per_org_structure_versions
				WHERE organization_structure_id =(SELECT organization_structure_id
								 FROM per_organization_structures
								 WHERE name = hiername)
				and date_to is null)
AND organization_id_child = l_organization_id;

Line: 753

			--Step 11: Populate selected CustomORGS into table and build hierarchy
			INSERT INTO AMW_ENTITY_HIERARCHIES
			(ENTITY_HIERARCHY_ID,
			 ENTITY_TYPE,
			 ENTITY_ID,
			 CREATED_BY,
			 CREATION_DATE,
			 LAST_UPDATE_DATE,
			 LAST_UPDATED_BY,
			 LAST_UPDATE_LOGIN,
			 OBJECT_TYPE,
			 OBJECT_ID,
			 PARENT_OBJECT_TYPE,
			 PARENT_OBJECT_ID,
			 LEVEL_ID )
			 SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
				p_entity_type,
				p_entity_id,
				FND_GLOBAL.USER_ID,
				SYSDATE,
				SYSDATE,
				FND_GLOBAL.USER_ID,
				FND_GLOBAL.USER_ID,
				'ORG',
				p_org_tbl(each_rec).org_id,
				'ORG',
				l_temp_parent_id,
				1
			FROM dual;

Line: 784

			--Step 11: Populate selected CustomORGS into table and build hierarchy
			INSERT INTO AMW_ENTITY_HIERARCHIES
			(ENTITY_HIERARCHY_ID,
			 ENTITY_TYPE,
			 ENTITY_ID,
			 CREATED_BY,
			 CREATION_DATE,
			 LAST_UPDATE_DATE,
			 LAST_UPDATED_BY,
			 LAST_UPDATE_LOGIN,
             		 OBJECT_TYPE,
			 OBJECT_ID,
			 PARENT_OBJECT_TYPE,
			 PARENT_OBJECT_ID,
			 LEVEL_ID )
			 SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
				p_entity_type,
				p_entity_id,
				FND_GLOBAL.USER_ID,
				SYSDATE,
				SYSDATE,
				FND_GLOBAL.USER_ID,
				FND_GLOBAL.USER_ID,
				'ORG',
				p_org_tbl(each_rec).org_id,
				'ROOTNODE',
				-1,
				1
			FROM dual;

Line: 829

SELECT distinct object_id
FROM AMW_ENTITY_HIERARCHIES
WHERE entity_type = p_entity_type
AND entity_id = p_entity_id
AND delete_flag = 'Y'
AND object_type = l_object_type;

Line: 883

'SELECT DISTINCT flv.flex_value_id
FROM amw_audit_units_v auv,fnd_flex_values flv
WHERE auv.subsidiary_valueset = flv.flex_value_set_id
AND auv.company_code = flv.flex_value';

Line: 948

'SELECT DISTINCT flv.flex_value_id
FROM amw_audit_units_v auv,fnd_flex_values flv
WHERE auv.lob_valueset = flv.flex_value_set_id
AND auv.lob_code = flv.flex_value';

Line: 1013

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

        SELECT 'Y'
        FROM amw_audit_units_v
        WHERE subsidiary_valueset = l_sub_vs
        AND   company_code        = l_sub_code
        AND   lob_valueset        = l_lob_vs
        AND   lob_code            = l_lob_code;

Line: 1040

           'SELECT org_v.child_process_id as top_process_id,
            org_v.child_process_org_rev_id as process_org_rev_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: 1086

/*	DELETE FROM AMW_EXECUTION_SCOPE
	WHERE entity_id = p_entity_id
	AND entity_type = p_entity_type
	and level_id < 4;*/

Line: 1095

			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,
				PROCESS_ORG_REV_ID,
				SCOPE_MODIFIED_DATE)
			SELECT 	amw_execution_scope_s.nextval,
				p_entity_type,
				p_entity_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,
				null,
				sysdate
			FROM DUAL
			WHERE not exists (SELECT 'Y'
					FROM AMW_EXECUTION_SCOPE
					WHERE entity_type=p_entity_type
					AND entity_id= p_entity_id
					AND subsidiary_vs =  audit_rec.subsidiary_valueset
					AND subsidiary_code= audit_rec.company_code
					AND level_id=1);

Line: 1158

				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,
					PROCESS_ORG_REV_ID,
					SCOPE_MODIFIED_DATE)
				SELECT  amw_execution_scope_s.nextval,
					p_entity_type,
					p_entity_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,
					null,
					SYSDATE
				FROM DUAL
				WHERE not exists (SELECT 'Y'
						FROM AMW_EXECUTION_SCOPE
						WHERE entity_type=p_entity_type
						AND entity_id= p_entity_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: 1210

				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,
					PROCESS_ORG_REV_ID,
					SCOPE_MODIFIED_DATE				)
				SELECT  amw_execution_scope_s.nextval,
					p_entity_type,
					p_entity_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,
					null,
					SYSDATE
				FROM DUAL
				WHERE NOT EXISTS (SELECT 'Y'
						FROM AMW_EXECUTION_SCOPE
						WHERE entity_type=p_entity_type
						AND entity_id= p_entity_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: 1263

				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,
					PROCESS_ORG_REV_ID,
					SCOPE_MODIFIED_DATE)
				SELECT  amw_execution_scope_s.nextval,
					p_entity_type,
					p_entity_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,
					'-999',
					'AMW_DUMMY_LOBCODE',
					null,
					null,
					null,
					null,
					null,
					SYSDATE
				FROM DUAL
				WHERE not exists (SELECT 'Y'
						FROM AMW_EXECUTION_SCOPE
						WHERE entity_type=p_entity_type
						AND entity_id= p_entity_id
						AND subsidiary_vs =  audit_rec.subsidiary_valueset
						AND subsidiary_code= audit_rec.company_code
						AND lob_vs = '-999'
						AND lob_code = 'AMW_DUMMY_LOBCODE'
						AND level_id=2);

Line: 1316

				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,
					PROCESS_ORG_REV_ID,
					SCOPE_MODIFIED_DATE				)
				SELECT  amw_execution_scope_s.nextval,
					p_entity_type,
					p_entity_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,
					'-999',
					'AMW_DUMMY_LOBCODE',
					audit_rec.organization_id,
					null,
					null,
					null,
					null,
					SYSDATE
				FROM DUAL
				WHERE not exists (SELECT 'Y'
						FROM AMW_EXECUTION_SCOPE
						WHERE entity_type=p_entity_type
						AND entity_id= p_entity_id
						AND subsidiary_vs =  audit_rec.subsidiary_valueset
						AND subsidiary_code= audit_rec.company_code
						AND lob_vs = '-999'
						AND lob_code = 'AMW_DUMMY_LOBCODE'
						AND organization_id = audit_rec.organization_id
						AND level_id=3);

Line: 1399

			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,
				PROCESS_ORG_REV_ID,
				SCOPE_MODIFIED_DATE)
			SELECT amw_execution_scope_s.nextval,
				p_entity_type,
				p_entity_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,
				l_process_org_rev_id,
				SYSDATE
			FROM DUAL
			WHERE not exists (SELECT 'Y'
					FROM AMW_EXECUTION_SCOPE
					WHERE entity_type=p_entity_type
					AND entity_id= p_entity_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 process_org_rev_id = l_process_org_rev_id
					AND level_id=4);

Line: 1453

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

Line: 1483

PROCEDURE Insert_Process
(
	p_level_id           IN NUMBER,
	p_parent_process_id  IN NUMBER,
	p_top_process_id     IN NUMBER,
	p_process_org_rev_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_entity_type        IN VARCHAR2,
	p_entity_id          IN NUMBER
) IS
       CURSOR c_process IS
           SELECT apv.child_process_id process_id, apv.child_process_org_rev_id process_org_rev_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: 1504

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

Line: 1506

        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,
		   PROCESS_ORG_REV_ID,
		   SCOPE_MODIFIED_DATE)
	SELECT amw_execution_scope_s.nextval,
 	       p_entity_type,
		   p_entity_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,
		   proc_rec.process_org_rev_id,
		   SYSDATE
         FROM DUAL;

Line: 1549

END Insert_Process;

Line: 1564

        SELECT 'Y' FROM AMW_AUDIT_PROJECTS
        WHERE audit_project_id = p_audit_project_id
        FOR UPDATE nowait;

Line: 1569

        SELECT organization_id,
	       process_id,
	       scope_changed_status
	  FROM AMW_EXECUTION_SCOPE
	 WHERE entity_type='PROJECT'
	   AND entity_id=p_audit_project_id
           AND organization_id IS NOT NULL
	   AND process_id IS NULL
	   AND SCOPE_CHANGED_STATUS = 'C';

Line: 1580

        SELECT organization_id,
	       process_id,
	       scope_changed_status
	  FROM AMW_EXECUTION_SCOPE
	 WHERE entity_type='PROJECT'
	   AND entity_id=p_audit_project_id
	   AND process_id IS NOT NULL
	   AND SCOPE_CHANGED_STATUS = 'C';

Line: 1590

        SELECT fad.entity_name, fad.pk1_value, fad.pk2_value, fad.pk3_value, fad.pk4_value, fad.pk5_value
          FROM fnd_attached_documents fad
         WHERE fad.entity_name = 'AMW_PROJECT_AP'
           AND fad.pk1_value = p_audit_project_id
           AND NOT EXISTS (select 'Y'
                           from amw_ap_associations ap_assoc
                           where ap_assoc.object_type='PROJECT'
                           and ap_assoc.pk1 = fad.pk1_value
                           and ap_assoc.pk2 = fad.pk2_value
                           and ap_assoc.pk4 = fad.pk3_value
                           and ap_assoc.audit_procedure_rev_id = fad.pk4_value);

Line: 1603

        select distinct audit_procedure_rev_id, pk1,pk2, pk4
        from amw_ap_associations
        where object_type = 'PROJECT_NEW'
        and pk1 = p_audit_project_id
        and audit_procedure_rev_id is not null;

Line: 1640

    DELETE FROM amw_risk_associations ara
     WHERE object_type='PROJECT'
       AND pk1 = p_audit_project_id
       AND pk3 IS NOT NULL
       AND not exists
		(select 'Y'
		   from amw_execution_scope
		  where entity_type='PROJECT'
		    and entity_id = p_audit_project_id
		    and organization_id = ara.pk2
		    and process_id = ara.pk3);

Line: 1652

    DELETE FROM amw_control_associations aca
     WHERE object_type='PROJECT'
       AND pk1 = p_audit_project_id
       AND pk3 IS NOT NULL
       AND not exists
	        (select 'Y' from amw_risk_associations ara
		  where object_type = 'PROJECT'
		    and ara.pk1 = p_audit_project_id
		    and ara.pk2 = aca.pk2
		    and ara.pk3 = aca.pk3
		    and ara.risk_id = aca.pk4);

Line: 1664

    DELETE FROM amw_ap_associations apa
     WHERE object_type='PROJECT'
       AND pk1 = p_audit_project_id
       AND not exists
	        (select 'Y' from amw_control_associations aca
		  where aca.object_type = 'PROJECT'
		    and aca.pk1 = p_audit_project_id
		    and aca.pk2 = apa.pk2
--                    and aca.pk3 IS NOT NULL --process
		    and aca.control_id = apa.pk3)
	and pk2 <> -1 and pk3 <> -1;

Line: 1676

     DELETE FROM amw_ap_associations apa
     WHERE object_type='PROJECT'
       AND pk1 = p_audit_project_id
       AND not exists
	        (select 'Y' from amw_execution_scope aes
		   where aes.entity_type = 'PROJECT'
		    and aes.entity_id = p_audit_project_id
		    and aes.organization_id = apa.pk2)
            and pk3 = -1
            and pk2 <> -1 ;

Line: 1689

    DELETE FROM amw_risk_associations ara
     WHERE object_type='PROJECT'
       AND pk1 = p_audit_project_id
       AND pk3 IS NULL
       AND not exists
		(select 'Y'
		   from amw_execution_scope
		  where entity_type='PROJECT'
		    and entity_id = p_audit_project_id
		    and organization_id = ara.pk2
		    and process_id IS NULL);

Line: 1701

    DELETE FROM amw_control_associations aca
     WHERE object_type='PROJECT'
       AND pk1 = p_audit_project_id
       AND pk3 IS NULL
       AND not exists
	        (select 'Y' from amw_risk_associations ara
		  where object_type = 'PROJECT'
		    and ara.pk1 = p_audit_project_id
		    and ara.pk2 = aca.pk2
		    and ara.pk3 IS NULL
		    and ara.risk_id = aca.pk4);

Line: 1713

    DELETE FROM amw_ap_associations apa
     WHERE object_type='PROJECT'
       AND pk1 = p_audit_project_id
       AND not exists
                (select 'Y' from amw_control_associations aca
                  where aca.object_type = 'PROJECT'
                    and aca.pk1 = p_audit_project_id
                    and aca.pk2 = apa.pk2
--                    and aca.pk3 IS NULL --process
                    and aca.control_id = apa.pk3)
	and pk2 <> -1 and pk3 <> -1;

Line: 1725

     DELETE FROM amw_ap_associations apa
     WHERE object_type='PROJECT'
       AND pk1 = p_audit_project_id
       AND not exists
	        (select 'Y' from amw_execution_scope aes
		   where aes.entity_type = 'PROJECT'
		    and aes.entity_id = p_audit_project_id
		    and aes.organization_id = apa.pk2)
            and pk3 = -1
            and pk2 <> -1 ;

Line: 1739

        UPDATE AMW_RISK_ASSOCIATIONS ara
	SET ara.risk_rev_id = (SELECT risk.risk_rev_id
				FROM AMW_RISKS_B risk
				WHERE risk.risk_id = ara.risk_id
				AND risk.curr_approved_flag = 'Y')
	WHERE ara.object_type = 'PROJECT'
	AND ara.pk1           = p_audit_project_id
	AND ara.pk2           = scope_rec.organization_id
	AND ara.pk3           = scope_rec.process_id;

Line: 1749

	INSERT INTO AMW_RISK_ASSOCIATIONS
	(
		risk_association_id,
		last_update_date,
		last_updated_by,
		creation_date,
		created_by,
		last_update_login,
		risk_id,
		risk_rev_id,
		pk1,
		pk2,
		pk3,
		object_type,
		object_version_number
	)
	SELECT AMW_RISK_ASSOCIATIONS_S.nextval,
		   sysdate,
		   fnd_global.user_id,
		   sysdate,
		   fnd_global.user_id,
		   fnd_global.user_id,
		   risk.risk_id,
		   risk.risk_rev_id,
		   p_audit_project_id,
		   scope_rec.organization_id,
		   scope_rec.process_id,
		   'PROJECT',
		   1
	FROM amw_risk_associations ara, amw_risks_b risk
	WHERE ara.object_type        = 'PROCESS_ORG'
	AND ara.pk1                  = scope_rec.organization_id
	AND ara.pk2                  = scope_rec.process_id
	AND ara.risk_id              = risk.risk_id
	AND risk.curr_approved_flag  = 'Y'
        AND ara.approval_date IS NOT NULL
        AND ara.deletion_approval_date IS NULL
	AND not exists
	   (select 'Y' from amw_risk_associations ara2
	    where ara2.object_type = 'PROJECT'
	      and ara2.pk1         = p_audit_project_id
	      and ara2.pk2         = scope_rec.organization_id
	      and ara2.pk3         = scope_rec.process_id
	      and ara2.risk_id     = risk.risk_id
	      and ara2.risk_rev_id = risk.risk_rev_id
           );

Line: 1796

        UPDATE AMW_CONTROL_ASSOCIATIONS aca
		SET aca.control_rev_id = (SELECT control_rev_id
					    FROM AMW_CONTROLS_B control
					   WHERE control.control_id =  aca.control_id
					     AND control.curr_approved_flag = 'Y')
		WHERE aca.object_type = 'PROJECT'
		AND aca.pk1           = p_audit_project_id
		AND aca.pk2           = scope_rec.organization_id
		AND aca.pk3           = scope_rec.process_id;

Line: 1806

	INSERT INTO amw_control_associations
	(
		control_association_id,
		last_update_date,
		last_updated_by,
		creation_date,
		created_by,
		last_update_login,
		control_id,
		control_rev_id,
		pk1,
		pk2,
		pk3,
		pk4,
		object_type,
		object_version_number
	)
	SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   FND_GLOBAL.USER_ID,
		   control.control_id,
		   control.control_rev_id,
		   p_audit_project_id,
		   scope_rec.organization_id,
		   scope_rec.process_id,
		   ara.risk_id,
		   'PROJECT',
		   1
	FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
	WHERE aca.object_type          = 'RISK_ORG'
	AND aca.pk1                    = scope_rec.organization_id
	AND aca.pk2                    = scope_rec.process_id
	AND aca.pk3                    = ara.risk_id
	AND aca.control_id             = control.control_id
        AND aca.approval_date IS NOT NULL
        AND aca.deletion_approval_date IS NULL
	AND control.curr_approved_flag = 'Y'
	AND ara.object_type            = 'PROJECT'
	AND ara.pk1                    = p_audit_project_id
	AND ara.pk2                    = scope_rec.organization_id
	AND ara.pk3                    = scope_rec.process_id
	AND not exists
	   (SELECT 'Y' from amw_control_associations aca2
	    WHERE aca2.object_type    = 'PROJECT'
	      AND aca2.pk1            = p_audit_project_id
	      AND aca2.pk2            = scope_rec.organization_id
	      AND aca2.pk3            = scope_rec.process_id
	      AND aca2.pk4            = ara.risk_id
	      AND aca2.control_id     = control.control_id
	      AND aca2.control_rev_id = control.control_rev_id
          );

Line: 1862

        UPDATE AMW_AP_ASSOCIATIONS apa
	SET apa.audit_procedure_rev_id = (SELECT audit_procedure_rev_id
	  			          FROM amw_audit_procedures_b aapb1
	  			          WHERE aapb1.audit_procedure_id = apa.audit_procedure_id
	  			          AND aapb1.curr_approved_flag = 'Y')
	WHERE apa.object_type = 'PROJECT'
	AND apa.pk1           = p_audit_project_id
   	AND apa.pk2           = scope_rec.organization_id;

Line: 1871

	INSERT INTO amw_ap_associations (
		ap_association_id,
		last_update_date,
		last_updated_by,
		creation_date,
		created_by,
		last_update_login,
		audit_procedure_id,
		audit_procedure_rev_id,
		pk1,
		pk2,
		pk3,
		pk4,
		object_type,
		object_version_number)
	SELECT AMW_AP_ASSOCIATIONS_S.nextval,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   FND_GLOBAL.USER_ID,
		   ttt.audit_procedure_id,
		   ttt.audit_procedure_rev_id,
		   p_audit_project_id,
		   ttt.organization_id,
		   ttt.control_id,
		   NVL(ttt.task_id, -1),
		   'PROJECT_NEW',
		   1
	  FROM (SELECT distinct
		       aapb.audit_procedure_id,
		       aapb.audit_procedure_rev_id,
		       apa.pk1	organization_id,
		       apa.pk3  control_id,
		       pt2.task_id
		FROM amw_ap_associations apa,
		   amw_audit_procedures_b aapb,
		   amw_ap_tasks	       apt,
		   amw_control_associations aca,
		   amw_audit_projects_v pp,
		   amw_audit_tasks_v pt1,
		   amw_audit_tasks_v pt2
		WHERE apa.object_type = 'CTRL_ORG'
		  AND aca.object_type='PROJECT'
		  AND aca.pk1 = p_audit_project_id
		  AND aca.pk2 = scope_rec.organization_id
		  AND aca.pk3 = scope_rec.process_id
		  AND apa.pk1 = aca.pk2 -- organization_id
		  AND apa.pk2 = aca.pk3 -- process_id
		  AND apa.pk3 = aca.control_id
                  AND apa.association_creation_date IS NOT NULL
                  AND apa.deletion_date IS NULL
		  AND apa.audit_procedure_id = aapb.audit_procedure_id
		  AND aapb.curr_approved_flag='Y'
		  AND pp.audit_project_id = p_audit_project_id
		  AND decode(apt.source_code, 'ICM', pt1.audit_project_id,
						     pt1.project_id)
			 = pp.created_from_project_id
		  AND pt1.task_id = apt.task_id
		  AND pt1.task_number = pt2.task_number
		  and apt.audit_procedure_id = apa.audit_procedure_id
		  AND pt2.audit_project_id = p_audit_project_id) ttt
	  WHERE NOT EXISTS
		   (SELECT 'Y' from amw_ap_associations apa2
		    where apa2.object_type in ('PROJECT','PROJECT_NEW')
		      AND apa2.pk1 = p_audit_project_id
		      AND apa2.pk2 = ttt.organization_id
		      AND apa2.pk3 = ttt.control_id
		      AND apa2.pk4 = ttt.task_id
		      AND apa2.audit_procedure_id = ttt.audit_procedure_id
		      AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
		   );

Line: 1944

		INSERT INTO amw_ap_associations (
		ap_association_id,
		last_update_date,
		last_updated_by,
		creation_date,
		created_by,
		last_update_login,
		audit_procedure_id,
		audit_procedure_rev_id,
		pk1,
		pk2,
		pk3,
		pk4,
		object_type,
		object_version_number)
	SELECT AMW_AP_ASSOCIATIONS_S.nextval,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   FND_GLOBAL.USER_ID,
		   ttt.audit_procedure_id,
		   ttt.audit_procedure_rev_id,
		   p_audit_project_id,
		   ttt.organization_id,
		   ttt.control_id,
		   NVL(ttt.task_id, -1),
		   'PROJECT_NEW',
		   1
	  FROM (SELECT distinct
		       aapb.audit_procedure_id,
		       aapb.audit_procedure_rev_id,
		       apa.pk1	organization_id,
		       apa.pk3  control_id,
		       pt2.task_id
		FROM amw_ap_associations apa,
		   amw_audit_procedures_b aapb,
		   amw_ap_tasks	       apt,
		   amw_control_associations aca,
		   amw_audit_projects_v pp,
		   amw_template_tasks_v pt1,
		   amw_audit_tasks_v pt2
		WHERE apa.object_type = 'CTRL_ORG'
		  AND aca.object_type='PROJECT'
		  AND aca.pk1 = p_audit_project_id
		  AND aca.pk2 = scope_rec.organization_id
		  AND aca.pk3 = scope_rec.process_id
		  AND apa.pk1 = aca.pk2 -- organization_id
		  AND apa.pk2 = aca.pk3 -- process_id
		  AND apa.pk3 = aca.control_id
                  AND apa.association_creation_date IS NOT NULL
                  AND apa.deletion_date IS NULL
		  AND apa.audit_procedure_id = aapb.audit_procedure_id
		  AND aapb.curr_approved_flag='Y'
		  AND pp.audit_project_id = p_audit_project_id
		  AND apt.source_code = 'PA'
		  AND pt1.project_id = pp.created_from_project_id
		  AND pt1.task_id = apt.task_id
		  AND pt1.task_number = pt2.task_number
		  and apt.audit_procedure_id = apa.audit_procedure_id
		  AND pt2.audit_project_id = p_audit_project_id) ttt
	  WHERE NOT EXISTS
		   (SELECT 'Y' from amw_ap_associations apa2
		    where apa2.object_type in ('PROJECT','PROJECT_NEW')
		      AND apa2.pk1 = p_audit_project_id
		      AND apa2.pk2 = ttt.organization_id
		      AND apa2.pk3 = ttt.control_id
		      AND apa2.pk4 = ttt.task_id
		      AND apa2.audit_procedure_id = ttt.audit_procedure_id
		      AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
		   );

Line: 2016

	 INSERT INTO amw_ap_associations (
		ap_association_id,
		last_update_date,
		last_updated_by,
		creation_date,
		created_by,
		last_update_login,
		audit_procedure_id,
		audit_procedure_rev_id,
		pk1,
		pk2,
		pk3,
		pk4,
		object_type,
		object_version_number)
	 SELECT AMW_AP_ASSOCIATIONS_S.nextval,
		   sysdate,
		   fnd_global.user_id,
		   sysdate,
		   fnd_global.user_id,
		   fnd_global.user_id,
		   ttt.audit_procedure_id,
		   ttt.audit_procedure_rev_id,
		   p_audit_project_id,
		   ttt.organization_id,
		   ttt.control_id,
		   -1,
		   'PROJECT_NEW',
		   1
	  FROM (SELECT distinct
		       aapb.audit_procedure_id,
		       aapb.audit_procedure_rev_Id,
		       apa.pk1	organization_id,
		       apa.pk3  control_id
		FROM amw_ap_associations apa,
		   amw_audit_procedures_b aapb,
		   amw_control_associations aca
		WHERE apa.object_type = 'CTRL_ORG'
		  AND aca.object_type='PROJECT'
		  AND aca.pk1 = p_audit_project_id
		  AND aca.pk2 = scope_rec.organization_id
		  AND aca.pk3 = scope_rec.process_id
		  AND apa.pk1 = aca.pk2 -- organization_id
		  AND apa.pk2 = aca.pk3 -- process_id
		  AND apa.pk3 = aca.control_id
                  AND apa.association_creation_date IS NOT NULL
                  AND apa.deletion_date IS NULL
		  AND apa.audit_procedure_id = aapb.audit_procedure_id
		  AND aapb.curr_approved_flag='Y') ttt
	  WHERE NOT EXISTS
		   (SELECT 'Y' from amw_ap_associations apa2
		    WHERE apa2.object_type in ('PROJECT','PROJECT_NEW')
		      AND apa2.pk1 = p_audit_project_id
		      AND apa2.pk2 = ttt.organization_id
		      AND apa2.pk3 = ttt.control_id
		      AND apa2.audit_procedure_id = ttt.audit_procedure_id
		      AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
	   );

Line: 2080

        UPDATE AMW_RISK_ASSOCIATIONS ara
	SET ara.risk_rev_id = (SELECT risk.risk_rev_id
				FROM AMW_RISKS_B risk
				WHERE risk.risk_id = ara.risk_id
				AND risk.curr_approved_flag = 'Y')
	WHERE ara.object_type = 'PROJECT'
	AND ara.pk1           = p_audit_project_id
	AND ara.pk2           = scope_org_rec.organization_id
	AND ara.pk3 IS NULL;

Line: 2090

	INSERT INTO AMW_RISK_ASSOCIATIONS
	(
		risk_association_id,
		last_update_date,
		last_updated_by,
		creation_date,
		created_by,
		last_update_login,
		risk_id,
		risk_rev_id,
		pk1,
		pk2,
		pk3,
		object_type,
		object_version_number
	)
	SELECT AMW_RISK_ASSOCIATIONS_S.nextval,
		   sysdate,
		   fnd_global.user_id,
		   sysdate,
		   fnd_global.user_id,
		   fnd_global.user_id,
		   risk.risk_id,
		   risk.risk_rev_id,
		   p_audit_project_id,
		   scope_org_rec.organization_id,
		   null,
		   'PROJECT',
		   1
	FROM amw_risk_associations ara, amw_risks_b risk
	WHERE ara.object_type        = 'ENTITY_RISK'
	AND ara.pk1                  = scope_org_rec.organization_id
	AND ara.pk2 IS NULL
	AND ara.risk_id              = risk.risk_id
	AND risk.curr_approved_flag  = 'Y'
	AND not exists
	   (select 'Y' from amw_risk_associations ara2
	    where ara2.object_type = 'PROJECT'
	      and ara2.pk1         = p_audit_project_id
	      and ara2.pk2         = scope_org_rec.organization_id
	      and ara2.pk3 IS NULL
	      and ara2.risk_id     = risk.risk_id
	      and ara2.risk_rev_id = risk.risk_rev_id
           );

Line: 2135

        UPDATE AMW_CONTROL_ASSOCIATIONS aca
		SET aca.control_rev_id = (SELECT control_rev_id
					    FROM AMW_CONTROLS_B control
					   WHERE control.control_id =  aca.control_id
					     AND control.curr_approved_flag = 'Y')
		WHERE aca.object_type = 'PROJECT'
		AND aca.pk1           = p_audit_project_id
		AND aca.pk2           = scope_org_rec.organization_id
		AND aca.pk3 IS NULL;

Line: 2146

	INSERT INTO amw_control_associations
	(
		control_association_id,
		last_update_date,
		last_updated_by,
		creation_date,
		created_by,
		last_update_login,
		control_id,
		control_rev_id,
		pk1,
		pk2,
		pk3,
		pk4,
		object_type,
		object_version_number
	)
	SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   FND_GLOBAL.USER_ID,
		   control.control_id,
		   control.control_rev_id,
		   p_audit_project_id,
		   scope_org_rec.organization_id,
		   null,
		   ara.risk_id,
		   'PROJECT',
		   1
	FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
	WHERE aca.object_type          = 'ENTITY_CONTROL'
	AND aca.pk1                    = scope_org_rec.organization_id
	AND aca.pk2                    = ara.risk_id
	AND aca.pk3 IS NULL
	AND aca.control_id             = control.control_id
	AND control.curr_approved_flag = 'Y'
	AND ara.object_type            = 'PROJECT'
	AND ara.pk1                    = p_audit_project_id
	AND ara.pk2                    = scope_org_rec.organization_id
	AND ara.pk3 IS NULL
	AND not exists
	   (SELECT 'Y' from amw_control_associations aca2
	    WHERE aca2.object_type    = 'PROJECT'
	      AND aca2.pk1            = p_audit_project_id
	      AND aca2.pk2            = scope_org_rec.organization_id
	      AND aca2.pk3 IS NULL
	      AND aca2.pk4            = ara.risk_id
	      AND aca2.control_id     = control.control_id
	      AND aca2.control_rev_id = control.control_rev_id
          );

Line: 2199

    	INSERT INTO amw_control_associations
	(
		control_association_id,
		last_update_date,
		last_updated_by,
		creation_date,
		created_by,
		last_update_login,
		control_id,
		control_rev_id,
		pk1,
		pk2,
		pk3,
		pk4,
		object_type,
		object_version_number
	)
	SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   FND_GLOBAL.USER_ID,
		   control.control_id,
		   control.control_rev_id,
		   p_audit_project_id,
		   scope_org_rec.organization_id,
		   null,
		   ara.risk_id,
		   'PROJECT',
		   1
	FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
	WHERE aca.object_type          = 'ENTITY_CONTROL'
	AND aca.pk1                    = scope_org_rec.organization_id
	AND aca.pk2                    = ara.risk_id
	AND aca.pk3 IS NULL
	AND aca.control_id             = control.control_id
	AND control.curr_approved_flag = 'Y'
	AND ara.object_type            = 'PROJECT'
	AND ara.pk1                    = p_audit_project_id
	AND ara.pk2                    = scope_org_rec.organization_id
	AND ara.pk3 IS NULL
	AND not exists
	   (SELECT 'Y' from amw_control_associations aca2
	    WHERE aca2.object_type    = 'PROJECT'
	      AND aca2.pk1            = p_audit_project_id
	      AND aca2.pk2            = scope_org_rec.organization_id
	      AND aca2.pk3 IS NULL
	      AND aca2.pk4            = ara.risk_id
	      AND aca2.control_id     = control.control_id
	      AND aca2.control_rev_id = control.control_rev_id
          )
    and aca.control_id  in
    (select distinct control_id from amw_control_associations where pk1=p_source_project_id and object_type='PROJECT'
    and control_id not in (select pk1_value from  amw_opinions_v where  pk2_value =p_source_project_id
        and audit_result_code ='EFFECTIVE' and
    object_name='AMW_ORG_CONTROL')             );

Line: 2259

        UPDATE AMW_AP_ASSOCIATIONS apa
	SET apa.audit_procedure_rev_id = (SELECT audit_procedure_rev_id
	  			          FROM amw_audit_procedures_b aapb1
	  			          WHERE aapb1.audit_procedure_id = apa.audit_procedure_id
	  			          AND aapb1.curr_approved_flag = 'Y')
	WHERE apa.object_type = 'PROJECT'
	AND apa.pk1           = p_audit_project_id
   	AND apa.pk2           = scope_org_rec.organization_id;

Line: 2268

	INSERT INTO amw_ap_associations (
		ap_association_id,
		last_update_date,
		last_updated_by,
		creation_date,
		created_by,
		last_update_login,
		audit_procedure_id,
		audit_procedure_rev_id,
		pk1,
		pk2,
		pk3,
		pk4,
		object_type,
		object_version_number)
	SELECT AMW_AP_ASSOCIATIONS_S.nextval,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   FND_GLOBAL.USER_ID,
		   ttt.audit_procedure_id,
		   ttt.audit_procedure_rev_id,
		   p_audit_project_id,
		   ttt.organization_id,
		   ttt.control_id,
		   NVL(ttt.task_id, -1),
		   'PROJECT_NEW',
		   1
	  FROM (SELECT distinct
		       aapb.audit_procedure_id,
		       aapb.audit_procedure_rev_id,
		       apa.pk1	organization_id,
		       apa.pk2  control_id,
		       pt2.task_id
		FROM amw_ap_associations apa,
		   amw_audit_procedures_b aapb,
		   amw_ap_tasks	       apt,
		   amw_control_associations aca,
		   amw_audit_projects_v pp,
		   amw_audit_tasks_v pt1,
		   amw_audit_tasks_v pt2
		WHERE apa.object_type = 'ENTITY_AP'
		  AND aca.object_type='PROJECT'
		  AND aca.pk1 = p_audit_project_id
		  AND aca.pk2 = scope_org_rec.organization_id
		  AND aca.pk3 IS NULL
		  AND apa.pk1 = aca.pk2 -- organization_id
		  AND apa.pk2 = aca.control_id -- Control_id
		  AND apa.audit_procedure_id = aapb.audit_procedure_id
                  AND apa.association_creation_date IS NOT NULL
		  AND aapb.curr_approved_flag='Y'
		  AND pp.audit_project_id = p_audit_project_id
		  AND decode(apt.source_code, 'ICM', pt1.audit_project_id,
						     pt1.project_id)
			 = pp.created_from_project_id
		  AND pt1.task_id = apt.task_id
		  AND pt1.task_number = pt2.task_number
		  and apt.audit_procedure_id = apa.audit_procedure_id
		  AND pt2.audit_project_id = p_audit_project_id) ttt
	  WHERE NOT EXISTS
		   (SELECT 'Y' from amw_ap_associations apa2
		    where apa2.object_type in ('PROJECT','PROJECT_NEW')
		      AND apa2.pk1 = p_audit_project_id
		      AND apa2.pk2 = ttt.organization_id
		      AND apa2.pk3 = ttt.control_id
		      AND apa2.pk4 = ttt.task_id
		      AND apa2.audit_procedure_id = ttt.audit_procedure_id
		      AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
		   );

Line: 2339

	INSERT INTO amw_ap_associations (
		ap_association_id,
		last_update_date,
		last_updated_by,
		creation_date,
		created_by,
		last_update_login,
		audit_procedure_id,
		audit_procedure_rev_id,
		pk1,
		pk2,
		pk3,
		pk4,
		object_type,
		object_version_number)
	SELECT AMW_AP_ASSOCIATIONS_S.nextval,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   FND_GLOBAL.USER_ID,
		   ttt.audit_procedure_id,
		   ttt.audit_procedure_rev_id,
		   p_audit_project_id,
		   ttt.organization_id,
		   ttt.control_id,
		   NVL(ttt.task_id, -1),
		   'PROJECT_NEW',
		   1
	  FROM (SELECT distinct
		       aapb.audit_procedure_id,
		       aapb.audit_procedure_rev_id,
		       apa.pk1	organization_id,
		       apa.pk2  control_id,
		       pt2.task_id
		FROM amw_ap_associations apa,
		   amw_audit_procedures_b aapb,
		   amw_ap_tasks	       apt,
		   amw_control_associations aca,
		   amw_audit_projects_v pp,
		   amw_template_tasks_v pt1,
		   amw_audit_tasks_v pt2
		WHERE apa.object_type = 'ENTITY_AP'
		  AND aca.object_type='PROJECT'
		  AND aca.pk1 = p_audit_project_id
		  AND aca.pk2 = scope_org_rec.organization_id
		  AND aca.pk3 IS NULL
		  AND apa.pk1 = aca.pk2 -- organization_id
		  AND apa.pk2 = aca.control_id -- Control_id
		  AND apa.audit_procedure_id = aapb.audit_procedure_id
                  AND apa.association_creation_date IS NOT NULL
		  AND aapb.curr_approved_flag='Y'
		  AND pp.audit_project_id = p_audit_project_id
		  AND apt.source_code = 'PA'
		  AND pt1.project_id = pp.created_from_project_id
		  AND pt1.task_id = apt.task_id
		  AND pt1.task_number = pt2.task_number
		  and apt.audit_procedure_id = apa.audit_procedure_id
		  AND pt2.audit_project_id = p_audit_project_id) ttt
	  WHERE NOT EXISTS
		   (SELECT 'Y' from amw_ap_associations apa2
		    where apa2.object_type in ('PROJECT','PROJECT_NEW')
		      AND apa2.pk1 = p_audit_project_id
		      AND apa2.pk2 = ttt.organization_id
		      AND apa2.pk3 = ttt.control_id
		      AND apa2.pk4 = ttt.task_id
		      AND apa2.audit_procedure_id = ttt.audit_procedure_id
		      AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
		   );

Line: 2409

	 INSERT INTO amw_ap_associations (
		ap_association_id,
		last_update_date,
		last_updated_by,
		creation_date,
		created_by,
		last_update_login,
		audit_procedure_id,
		audit_procedure_rev_id,
		pk1,
		pk2,
		pk3,
		pk4,
		object_type,
		object_version_number)
	 SELECT AMW_AP_ASSOCIATIONS_S.nextval,
		   sysdate,
		   fnd_global.user_id,
		   sysdate,
		   fnd_global.user_id,
		   fnd_global.user_id,
		   ttt.audit_procedure_id,
		   ttt.audit_procedure_rev_id,
		   p_audit_project_id,
		   ttt.organization_id,
		   ttt.control_id,
		   -1,
		   'PROJECT_NEW',
		   1
	  FROM (SELECT distinct
		       aapb.audit_procedure_id,
		       aapb.audit_procedure_rev_Id,
		       apa.pk1	organization_id,
		       apa.pk2  control_id
		FROM amw_ap_associations apa,
		   amw_audit_procedures_b aapb,
		   amw_control_associations aca
		WHERE apa.object_type = 'ENTITY_AP'
		  AND aca.object_type='PROJECT'
		  AND aca.pk1 = p_audit_project_id
		  AND aca.pk2 = scope_org_rec.organization_id
		  AND aca.pk3 IS NULL
		  AND apa.pk1 = aca.pk2 -- organization_id
		  AND apa.pk2 = aca.control_id -- control_id
		  AND apa.audit_procedure_id = aapb.audit_procedure_id
                  AND apa.association_creation_date IS NOT NULL
		  AND aapb.curr_approved_flag='Y') ttt
	  WHERE NOT EXISTS
		   (SELECT 'Y' from amw_ap_associations apa2
		    WHERE apa2.object_type in ('PROJECT','PROJECT_NEW')
		      AND apa2.pk1 = p_audit_project_id
		      AND apa2.pk2 = ttt.organization_id
		      AND apa2.pk3 = ttt.control_id
		      AND apa2.audit_procedure_id = ttt.audit_procedure_id
		      AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
	   );

Line: 2469

    select category_id into v_category_id
    from fnd_document_categories where name = 'AMW_WORK_PAPERS';

Line: 2486

    UPDATE AMW_AP_ASSOCIATIONS SET object_type='PROJECT' WHERE object_type = 'PROJECT_NEW';

Line: 2491

      fnd_attached_documents2_pkg.delete_attachments(X_entity_name => ap_attachment_rec.entity_name,
                                                     X_pk1_value => ap_attachment_rec.pk1_value,
                                                     X_pk2_value => ap_attachment_rec.pk2_value,
                                                     X_pk3_value => ap_attachment_rec.pk3_value,
                                                     X_pk4_value => ap_attachment_rec.pk4_value);

Line: 2499

    UPDATE AMW_EXECUTION_SCOPE
    SET SCOPE_CHANGED_STATUS = null
    WHERE entity_type='PROJECT'
      AND entity_id=p_audit_project_id
      AND SCOPE_CHANGED_STATUS = 'C';

Line: 2505

    UPDATE AMW_AUDIT_PROJECTS
    SET scope_changed_flag = 'N'
    WHERE project_id = p_audit_project_id;

Line: 2532

	DELETE FROM amw_proc_cert_eval_sum pcert
	WHERE certification_id = p_entity_id
	AND NOT EXISTS (SELECT 'Y'
			FROM amw_execution_scope exec
			WHERE exec.entity_id = pcert.certification_id
			AND exec.entity_type = p_entity_type
			AND exec.process_id = pcert.process_id
			AND exec.organization_id = pcert.organization_id
			);

Line: 2542

	INSERT INTO amw_proc_cert_eval_sum(certification_id,
					   process_id,
					   organization_id,
					   process_org_rev_id,
					   created_by,
					   creation_date,
					   last_updated_by,
					   last_update_date,
					   last_update_login)
	SELECT  DISTINCT
	        entity_id,
		process_id,
		organization_id,
		process_org_rev_id,
		fnd_global.user_id,
		SYSDATE,
		fnd_global.user_id,
		SYSDATE,
		fnd_global.user_id
	FROM amw_execution_scope exec
	WHERE NOT EXISTS (SELECT 'Y'
			  FROM amw_proc_cert_eval_sum pcert
			  WHERE pcert.certification_id = exec.entity_id
			  AND pcert.organization_id = exec.organization_id
			  AND pcert.process_id = exec.process_id)
	AND entity_id     = p_entity_id
	AND entity_type   = p_entity_type
	AND scope_changed_status = 'C'
	AND level_id > 3;

Line: 2575

		INSERT INTO amw_org_cert_eval_sum(certification_id,
						organization_id,
						created_by,
						creation_date,
						last_updated_by,
						last_update_date,
						last_update_login)
		SELECT  entity_id,
			organization_id,
			fnd_global.user_id,
			SYSDATE,
			fnd_global.user_id,
			SYSDATE,
			fnd_global.user_id
		FROM amw_execution_scope exec
		WHERE NOT EXISTS (SELECT 'Y'
				  FROM amw_org_cert_eval_sum ocert
				  WHERE ocert.certification_id = exec.entity_id
				  AND ocert.organization_id = exec.organization_id
				  )
		AND entity_id     = p_entity_id
		AND entity_type   = p_entity_type
		AND scope_changed_status = 'C'
		AND level_id = 3;

Line: 2617

	SELECT organization_id,process_id, scope_changed_status
	FROM AMW_EXECUTION_SCOPE
	WHERE entity_type        = p_entity_type
	AND entity_id            = p_entity_id
	AND scope_changed_status = 'C'
	AND process_id IS NOT NULL
        FOR UPDATE NOWAIT;

Line: 2627

        SELECT organization_id,
	       process_id,
	       scope_changed_status
	  FROM AMW_EXECUTION_SCOPE
	 WHERE entity_type=p_entity_type
	   AND entity_id=p_entity_id
           AND organization_id IS NOT NULL
	   AND process_id IS NULL
	   AND SCOPE_CHANGED_STATUS = 'C';

Line: 2663

	DELETE FROM AMW_RISK_ASSOCIATIONS ara
	WHERE object_type = p_entity_type
	AND pk1         = p_entity_id
	AND pk3 IS NOT NULL
	AND NOT EXISTS
		(SELECT 'Y'
		   FROM AMW_EXECUTION_SCOPE
		  WHERE entity_type     = p_entity_type
		    AND entity_id       = p_entity_id
		    AND organization_id = ara.pk2
		    AND process_id      = ara.pk3);

Line: 2675

	DELETE FROM AMW_CONTROL_ASSOCIATIONS aca
	WHERE object_type = p_entity_type
	AND pk1         = p_entity_id
	AND pk3 IS NOT NULL
	AND NOT EXISTS
		(SELECT 'Y'
		   FROM AMW_RISK_ASSOCIATIONS ara
		  WHERE object_type = p_entity_type
		    AND ara.pk1     = p_entity_id
		    AND ara.pk2     = aca.pk2
		    AND ara.pk3     = aca.pk3
		    AND ara.risk_id = aca.pk4);

Line: 2692

    DELETE FROM amw_risk_associations ara
     WHERE object_type=p_entity_type
       AND pk1 = p_entity_id
       AND pk3 IS NULL
       AND not exists
		(select 'Y'
		   from amw_execution_scope
		  where entity_type=p_entity_type
		    and entity_id = p_entity_id
		    and organization_id = ara.pk2
		    and process_id IS NULL);

Line: 2704

    DELETE FROM amw_control_associations aca
     WHERE object_type=p_entity_type
       AND pk1 = p_entity_id
       AND pk3 IS NULL
       AND not exists
	        (select 'Y' from amw_risk_associations ara
		  where object_type = p_entity_type
		    and ara.pk1 = p_entity_id
		    and ara.pk2 = aca.pk2
		    and ara.pk3 IS NULL
		    and ara.risk_id = aca.pk4);

Line: 2718

	DELETE FROM AMW_AP_ASSOCIATIONS apa
	WHERE object_type = p_entity_type
	AND pk1         = p_entity_id
	AND NOT EXISTS
		(SELECT 'Y' FROM AMW_CONTROL_ASSOCIATIONS aca
		  WHERE aca.object_type = p_entity_type
		    AND aca.pk1         = p_entity_id
		    AND aca.pk2         = apa.pk2
		    AND aca.control_id  = apa.pk3)
	and pk2 <> -1 and pk3 <> -1;

Line: 2729

     DELETE FROM amw_ap_associations apa
     WHERE object_type = p_entity_type
       AND pk1 = p_entity_id
       AND not exists
	        (select 'Y' from amw_execution_scope aes
		   where aes.entity_type = p_entity_type
		    and aes.entity_id = p_entity_id
		    and aes.organization_id = apa.pk2)
            and pk3 = -1
            and pk2 <> -1 ;

Line: 2742

		UPDATE amw_risk_associations ara
		SET ara.risk_rev_id = (SELECT risk.risk_rev_id
					FROM amw_risks_b risk
					WHERE risk.risk_id = ara.risk_id
					AND risk.curr_approved_flag = 'Y')
		WHERE ara.object_type = p_entity_type
		AND ara.pk1           = p_entity_id
		AND ara.pk2           = each_rec.organization_id
		AND ara.pk3           = each_rec.process_id;

Line: 2752

		INSERT INTO amw_risk_associations (
			risk_association_id,
			last_update_date,
			last_updated_by,
			creation_date,
			created_by,
			last_update_login,
			risk_id,
			risk_rev_id,
			pk1,
			pk2,
			pk3,
			object_type,
			object_version_number)
		SELECT 	AMW_RISK_ASSOCIATIONS_S.nextval,
			sysdate,
			fnd_global.user_id,
			sysdate,
			fnd_global.user_id,
			fnd_global.user_id,
			risk.risk_id,
			risk.risk_rev_id,
			p_entity_id,
			ara.pk1,
			ara.pk2,
			p_entity_type,
			1
		FROM amw_risk_associations ara, amw_risks_b risk
		WHERE ara.object_type        = 'PROCESS_ORG'
		AND ara.pk1                  = each_rec.organization_id
		AND ara.pk2                  = each_rec.process_id
		AND ara.risk_id              = risk.risk_id
		AND ara.approval_date IS NOT NULL
		AND ara.deletion_approval_date IS NULL
		AND risk.curr_approved_flag  = 'Y'
		AND NOT EXISTS
			  (SELECT 'Y' FROM amw_risk_associations ara2
			   WHERE ara2.object_type=p_entity_type
			     AND ara2.pk1 = p_entity_id
			     AND ara2.pk2 = each_rec.organization_id
			     AND ara2.pk3 = each_rec.process_id
			     AND ara2.risk_id = risk.risk_id
			     AND ara2.risk_rev_id = risk.risk_rev_id
			     );

Line: 2798

		UPDATE amw_control_associations aca
		SET aca.control_rev_id = (SELECT control_rev_id
					    FROM amw_controls_b control
					   WHERE control.control_id =  aca.control_id
					     AND control.curr_approved_flag = 'Y')
		WHERE aca.object_type = p_entity_type
		AND aca.pk1           = p_entity_id
		AND aca.pk2           = each_rec.organization_id
		AND aca.pk3           = each_rec.process_id;

Line: 2808

		INSERT INTO amw_control_associations (
			control_association_id,
			last_update_date,
			last_updated_by,
			creation_date,
			created_by,
			last_update_login,
			control_id,
			control_rev_id,
			pk1,
			pk2,
			pk3,
			pk4,
			object_type,
			object_version_number)
		SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
			sysdate,
			fnd_global.user_id,
			sysdate,
			fnd_global.user_id,
			fnd_global.user_id,
			control.control_id,
			control.control_rev_id,
			p_entity_id,
			each_rec.organization_id,
			each_rec.process_id,
			ara.risk_id,
			p_entity_type,
			1
		FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
		WHERE aca.object_type          = 'RISK_ORG'
		AND aca.pk1                    = each_rec.organization_id
		AND aca.pk2                    = each_rec.process_id
		AND aca.pk3                    = ara.risk_id
		AND aca.control_id             = control.control_id
		AND aca.approval_date IS NOT NULL
		AND aca.deletion_approval_date IS NULL
		AND control.curr_approved_flag = 'Y'
		AND ara.object_type            = 'PROCESS_ORG'
		AND ara.pk1                    = each_rec.organization_id
		AND ara.pk2                    = each_rec.process_id
		AND NOT EXISTS
			   (SELECT 'Y' FROM amw_control_associations aca2
			    WHERE aca2.object_type = p_entity_type
			      AND aca2.pk1         = p_entity_id
			      AND aca2.pk2         = each_rec.organization_id
			      AND aca2.pk3         = each_rec.process_id
			      AND aca2.pk4         = ara.risk_id
			      AND aca2.control_id  = control.control_id
			      AND aca2.control_rev_id = control.control_rev_id
			   );

Line: 2860

		UPDATE amw_ap_associations apa
		SET apa.audit_procedure_rev_id = (SELECT audit_procedure_rev_id
						    FROM amw_audit_procedures_b aapb1
						   WHERE aapb1.audit_procedure_id = apa.audit_procedure_id
						     AND aapb1.curr_approved_flag = 'Y')
		WHERE apa.object_type = p_entity_type
		  AND apa.pk1         = p_entity_id
		  AND apa.pk2         = each_rec.organization_id;

Line: 2869

		INSERT INTO amw_ap_associations (
			ap_association_id,
			last_update_date,
			last_updated_by,
			creation_date,
			created_by,
			last_update_login,
			audit_procedure_id,
			audit_procedure_rev_id,
			pk1,
			pk2,
			pk3,
			object_type,
			object_version_number)
		SELECT  AMW_AP_ASSOCIATIONS_S.nextval,
			sysdate,
			fnd_global.user_id,
			sysdate,
			fnd_global.user_id,
			fnd_global.user_id,
			auditproc.audit_procedure_id,
			auditproc.audit_procedure_rev_id,
			p_entity_id,
			auditproc.organization_id,
			auditproc.control_id,
			p_entity_type,
			1
		FROM
		(SELECT DISTINCT
		        aapb.audit_procedure_id,
			aapb.audit_procedure_rev_id,
			apa.pk1 organization_id,
			aca.control_id
		 FROM amw_ap_associations apa,amw_audit_procedures_b aapb,amw_control_associations aca
		 WHERE apa.object_type        = 'CTRL_ORG'
		   AND apa.pk1                = each_rec.organization_id
		   AND apa.pk2                = each_rec.process_id
		   AND apa.pk3                = aca.control_id
		   AND aca.object_type        = 'RISK_ORG'
		   AND apa.pk1                = aca.pk1 -- organization_id
		   AND apa.pk2                = aca.pk2 -- process_id
		   AND apa.audit_procedure_id = aapb.audit_procedure_id
		   AND aapb.curr_approved_flag='Y'
		   AND NOT EXISTS
			(SELECT 'Y' FROM amw_ap_associations apa2
			   WHERE apa2.object_type          = p_entity_type
			   AND apa2.pk1                    = p_entity_id
			   AND apa2.pk2                    = each_rec.organization_id
			   AND apa2.pk3                    = aca.control_id
			   AND apa2.audit_procedure_id     = aapb.audit_procedure_id
			   AND apa2.audit_procedure_rev_id = aapb.audit_procedure_rev_id
			)
		) auditproc;

Line: 2928

        UPDATE AMW_RISK_ASSOCIATIONS ara
	SET ara.risk_rev_id = (SELECT risk.risk_rev_id
				FROM AMW_RISKS_B risk
				WHERE risk.risk_id = ara.risk_id
				AND risk.curr_approved_flag = 'Y')
	WHERE ara.object_type = p_entity_type
	AND ara.pk1           = p_entity_id
	AND ara.pk2           = scope_org_rec.organization_id
	AND ara.pk3 IS NULL;

Line: 2938

	INSERT INTO AMW_RISK_ASSOCIATIONS
	(
		risk_association_id,
		last_update_date,
		last_updated_by,
		creation_date,
		created_by,
		last_update_login,
		risk_id,
		risk_rev_id,
		pk1,
		pk2,
		pk3,
		object_type,
		object_version_number
	)
	SELECT AMW_RISK_ASSOCIATIONS_S.nextval,
		   sysdate,
		   fnd_global.user_id,
		   sysdate,
		   fnd_global.user_id,
		   fnd_global.user_id,
		   risk.risk_id,
		   risk.risk_rev_id,
		   p_entity_id,
		   scope_org_rec.organization_id,
		   null,
		   p_entity_type,
		   1
	FROM amw_risk_associations ara, amw_risks_b risk
	WHERE ara.object_type        = 'ENTITY_RISK'
	AND ara.pk1                  = scope_org_rec.organization_id
	AND ara.pk2 IS NULL
	AND ara.risk_id              = risk.risk_id
	AND risk.curr_approved_flag  = 'Y'
	AND not exists
	   (select 'Y' from amw_risk_associations ara2
	    where ara2.object_type = p_entity_type
	      and ara2.pk1         = p_entity_id
	      and ara2.pk2         = scope_org_rec.organization_id
	      and ara2.pk3 IS NULL
	      and ara2.risk_id     = risk.risk_id
	      and ara2.risk_rev_id = risk.risk_rev_id
           );

Line: 2983

        UPDATE AMW_CONTROL_ASSOCIATIONS aca
		SET aca.control_rev_id =
			      (SELECT control_rev_id
				 FROM AMW_CONTROLS_B control
				WHERE control.control_id =  aca.control_id
				  AND control.curr_approved_flag = 'Y')
		WHERE aca.object_type = p_entity_type
		AND aca.pk1           = p_entity_id
		AND aca.pk2           = scope_org_rec.organization_id
		AND aca.pk3 IS NULL;

Line: 2994

	INSERT INTO amw_control_associations
	(
		control_association_id,
		last_update_date,
		last_updated_by,
		creation_date,
		created_by,
		last_update_login,
		control_id,
		control_rev_id,
		pk1,
		pk2,
		pk3,
		pk4,
		object_type,
		object_version_number
	)
	SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   FND_GLOBAL.USER_ID,
		   control.control_id,
		   control.control_rev_id,
		   p_entity_id,
		   scope_org_rec.organization_id,
		   null,
		   ara.risk_id,
		   p_entity_type,
		   1
	FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
	WHERE aca.object_type          = 'ENTITY_CONTROL'
	AND aca.pk1                    = scope_org_rec.organization_id
	AND aca.pk2                    = ara.risk_id
	AND aca.pk3 IS NULL
	AND aca.control_id             = control.control_id
	AND control.curr_approved_flag = 'Y'
	AND ara.object_type            = p_entity_type
	AND ara.pk1                    = p_entity_id
	AND ara.pk2                    = scope_org_rec.organization_id
	AND ara.pk3 IS NULL
	AND not exists
	   (SELECT 'Y' from amw_control_associations aca2
	    WHERE aca2.object_type    = p_entity_type
	      AND aca2.pk1            = p_entity_id
	      AND aca2.pk2            = scope_org_rec.organization_id
	      AND aca2.pk3 IS NULL
	      AND aca2.pk4            = ara.risk_id
	      AND aca2.control_id     = control.control_id
	      AND aca2.control_rev_id = control.control_rev_id
          );

Line: 3048

        UPDATE AMW_AP_ASSOCIATIONS apa
	SET apa.audit_procedure_rev_id =
		      (SELECT audit_procedure_rev_id
	  		 FROM amw_audit_procedures_b aapb1
	  		WHERE aapb1.audit_procedure_id = apa.audit_procedure_id
		          AND aapb1.curr_approved_flag = 'Y')
	WHERE apa.object_type = p_entity_type
	AND apa.pk1           = p_entity_id
   	AND apa.pk2           = scope_org_rec.organization_id;

Line: 3059

	INSERT INTO amw_ap_associations (
			ap_association_id,
			last_update_date,
			last_updated_by,
			creation_date,
			created_by,
			last_update_login,
			audit_procedure_id,
			audit_procedure_rev_id,
			pk1,
			pk2,
			pk3,
			object_type,
			object_version_number)
	SELECT  AMW_AP_ASSOCIATIONS_S.nextval,
			sysdate,
			fnd_global.user_id,
			sysdate,
			fnd_global.user_id,
			fnd_global.user_id,
			auditproc.audit_procedure_id,
			auditproc.audit_procedure_rev_id,
			p_entity_id,
			auditproc.organization_id,
			auditproc.control_id,
			p_entity_type,
			1
	FROM
		(SELECT DISTINCT
		        aapb.audit_procedure_id,
			aapb.audit_procedure_rev_id,
			apa.pk1 organization_id,
			aca.control_id
		 FROM amw_ap_associations apa,amw_audit_procedures_b aapb,
		      amw_control_associations aca
		 WHERE apa.object_type        = 'ENTITY_AP'
		   AND aca.object_type	      = p_entity_type
		   AND aca.pk1		      = p_entity_id
		   AND aca.pk2		      = scope_org_rec.organization_id
		   AND aca.pk3		      IS NULL
		   AND apa.pk1                = aca.pk2
		   AND apa.pk2                = aca.control_id
		   AND apa.association_creation_date IS NOT NULL
		   AND apa.audit_procedure_id = aapb.audit_procedure_id
		   AND aapb.curr_approved_flag='Y'
		   AND NOT EXISTS
			(SELECT 'Y' FROM amw_ap_associations apa2
			   WHERE apa2.object_type          = p_entity_type
			   AND apa2.pk1                    = p_entity_id
			   AND apa2.pk2                    = scope_org_rec.organization_id
			   AND apa2.pk3                    = aca.control_id
			   AND apa2.audit_procedure_id     = aapb.audit_procedure_id
			   AND apa2.audit_procedure_rev_id = aapb.audit_procedure_rev_id
			)
		) auditproc;

Line: 3118

	UPDATE amw_execution_scope
	SET SCOPE_CHANGED_STATUS   =  null
	WHERE entity_type          = p_entity_type
	AND entity_id              = p_entity_id
	AND SCOPE_CHANGED_STATUS   = 'C';

Line: 3148

	SELECT audit_v.company_code,audit_v.lob_code,audit_v.organization_id
	FROM amw_audit_units_v audit_v;

Line: 3153

    	SELECT DISTINCT org_v.child_process_id as process_id
    	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 = p_org_id;

Line: 3262

    SELECT SUBSIDIARY_VS, SUBSIDIARY_CODE,LOB_VS, LOB_CODE
    FROM AMW_EXECUTION_SCOPE
    WHERE entity_type = p_entity_type
    AND entity_id     = p_entity_id
    AND organization_id = p_organization_id
    FOR UPDATE NOWAIT;

Line: 3270

    SELECT aorv.child_process_org_rev_id
    FROM amw_curr_app_hierarchy_org_v aorv,amw_execution_scope aes,amw_lookups lk
    WHERE aorv.child_organization_id = aes.organization_id(+)
      AND aorv.child_process_id      = aes.process_id(+)
      AND aorv.child_process_id      <> -2
      AND lk.lookup_type             = 'AMW_SCOPE_ENTITY_TYPE'
      AND lk.lookup_code             = 'PROCESS'
      AND aes.entity_type(+)         = p_entity_type
      AND aes.entity_id(+)           = p_entity_id
      AND aorv.child_organization_id = p_organization_id
      AND aorv.child_process_id      = p_process_id;

Line: 3325

    DELETE FROM amw_execution_scope
    WHERE entity_type = p_entity_type
      AND entity_id   = p_entity_id
      AND organization_id = p_organization_id
      AND level_id > 3;

Line: 3340

        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,
		   PROCESS_ORG_REV_ID,
		   SCOPE_MODIFIED_DATE)
	SELECT amw_execution_scope_s.nextval,
 	       p_entity_type,
		   p_entity_id,
		   FND_GLOBAL.USER_ID,
		   SYSDATE,
		   SYSDATE,
		   FND_GLOBAL.USER_ID,
		   FND_GLOBAL.USER_ID,
		   'C',
		   p_proc_hier_tbl(i).level_id,
		   l_subsidiary_vs,
		   l_sub_code,
		   l_lob_vs,
		   l_lob_code,
		   p_organization_id,
		   p_proc_hier_tbl(i).process_id,
		   p_proc_hier_tbl(i).top_process_id,
		   p_proc_hier_tbl(i).parent_process_id,
		   l_process_org_rev_id,
		   SYSDATE
         FROM dual where not exists (select 'Y' from amw_execution_scope
         where entity_type = p_entity_type and entity_id = p_entity_id
         and organization_id = p_organization_id and process_id = p_proc_hier_tbl(i).process_id
         and top_process_id = p_proc_hier_tbl(i).top_process_id and parent_process_id = p_proc_hier_tbl(i).parent_process_id
         and level_id = p_proc_hier_tbl(i).level_id
         and subsidiary_vs =  l_subsidiary_vs and subsidiary_code= l_sub_code
	 and lob_vs = l_lob_vs and lob_code = l_lob_code
	 and process_org_rev_id = l_process_org_rev_id);

Line: 3395

    UPDATE amw_execution_scope
       SET scope_changed_status = 'C'
     WHERE entity_type = p_entity_type
       AND entity_id   = p_entity_id
       AND organization_id = p_organization_id
       AND level_id = 3;

Line: 3442

    raise_scope_update_event(
		p_entity_type	=> p_entity_type,
		p_entity_id	=> p_entity_id,
		p_org_id	=> p_organization_id,
		p_mode		=> 'ManageProc');

Line: 3480

    SELECT 'Y'
    FROM AMW_ENTITY_HIERARCHIES
    WHERE entity_id = p_entity_id
    FOR UPDATE NOWAIT;

Line: 3487

    SELECT object_type
    FROM AMW_ENTITY_HIERARCHIES
    WHERE object_id = l_object_id
    AND entity_id = l_entity_id
    AND entity_type = p_entity_type;

Line: 3509

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

    DELETE FROM AMW_ENTITY_HIERARCHIES
    WHERE object_type   = p_object_type
    AND object_id       = p_object_id
    AND entity_type     = p_entity_type
    AND entity_id       = p_entity_id;

Line: 3562

            DELETE FROM AMW_ENTITY_HIERARCHIES
            WHERE object_id            = l_object_tbl(each_rec).org_id
            AND object_type            = p_object_type
            AND entity_id              = p_entity_id
            AND entity_type            = p_entity_type;

Line: 3568

            DELETE FROM AMW_ENTITY_HIERARCHIES
            WHERE parent_object_id     = l_object_tbl(each_rec).org_id
            AND parent_object_type     = p_object_type
            AND entity_id              = p_entity_id
            AND entity_type            = p_entity_type;

Line: 3595

				DELETE FROM AMW_EXECUTION_SCOPE WHERE entity_id = p_entity_id
				and entity_type = p_entity_type and subsidiary_vs = audit_rec.subsidiary_valueset
				and SUBSIDIARY_CODE = audit_rec.company_code;

Line: 3602

				DELETE FROM AMW_EXECUTION_SCOPE WHERE entity_id = p_entity_id
				and entity_type = p_entity_type and subsidiary_vs = audit_rec.subsidiary_valueset
				and SUBSIDIARY_CODE = audit_rec.company_code
 				AND NVL(lob_vs, 'AMW_NULL_CODE') = NVL(audit_rec.lob_valueset, NVL(lob_vs, 'AMW_NULL_CODE'))
    				AND NVL(lob_code, 'AMW_NULL_CODE') = NVL(audit_rec.lob_code, NVL(lob_code, 'AMW_NULL_CODE'))
    				AND level_id > 1;

Line: 3627

    DELETE FROM AMW_EXECUTION_SCOPE
    WHERE entity_type = p_entity_type
    AND   entity_id   = p_entity_id
    AND subsidiary_vs = p_subsidiary_vs
    AND subsidiary_code = p_subsidiary_code
    AND NVL(lob_vs, 'AMW_NULL_CODE') = NVL(p_lob_vs, NVL(lob_vs, 'AMW_NULL_CODE'))
    AND NVL(lob_code, 'AMW_NULL_CODE') = NVL(p_lob_code, NVL(lob_code, 'AMW_NULL_CODE'))
    AND NVL(organization_id, -999) = NVL(p_organization_id, NVL(organization_id, -999));

Line: 3639

        UPDATE amw_audit_projects
        SET SCOPE_CHANGED_FLAG = 'Y'
        WHERE project_id = p_entity_id;

Line: 3654

    raise_scope_update_event(
		p_entity_type	=> p_entity_type,
		p_entity_id	=> p_entity_id,
		p_mode		=> 'RemoveFromScope');

Line: 3720

    DELETE FROM AMW_ENTITY_HIERARCHIES
    WHERE object_id     = p_object_id
    AND entity_id       = p_entity_id
    AND entity_type     = p_entity_type;

Line: 3728

        DELETE FROM AMW_PROC_CERT_EVAL_SUM
	WHERE organization_id = p_object_id
	AND certification_id  = p_entity_id;

Line: 3732

    	DELETE FROM AMW_ORG_CERT_EVAL_SUM
	WHERE organization_id = p_object_id
	AND certification_id  = p_entity_id;

Line: 3736

        DELETE FROM amw_audit_scope_processes
	WHERE organization_id = p_object_id
	AND audit_project_id  = p_entity_id;

Line: 3740

    	DELETE FROM amw_audit_scope_organizations
	WHERE organization_id = p_object_id
	AND audit_project_id  = p_entity_id;

Line: 3753

        DELETE FROM AMW_ENTITY_HIERARCHIES
        WHERE object_id            = l_object_tbl(each_rec).org_id
        AND object_type            = 'ORG'
        AND parent_object_type     = 'ORG'
        AND entity_id              = p_entity_id
        AND entity_type            = p_entity_type;

Line: 3760

        DELETE FROM AMW_ENTITY_HIERARCHIES
        WHERE parent_object_id     = l_object_tbl(each_rec).org_id
        AND parent_object_type     = 'ORG'
        AND object_type            = 'ORG'
        AND entity_id              = p_entity_id
        AND entity_type            = p_entity_type;

Line: 3769

		DELETE FROM AMW_PROC_CERT_EVAL_SUM
		WHERE organization_id = l_object_tbl(each_rec).org_id
		AND certification_id  = p_entity_id;

Line: 3773

	    	DELETE FROM AMW_ORG_CERT_EVAL_SUM
		WHERE organization_id = l_object_tbl(each_rec).org_id
		AND certification_id  = p_entity_id;

Line: 3804

    SELECT object_id
    FROM AMW_ENTITY_HIERARCHIES
    WHERE parent_object_id   = l_object_id
    AND entity_id            = l_entity_id
    AND entity_type          = p_entity_type
    AND object_type          = 'ORG'
    AND parent_object_type   = 'ORG';

Line: 3841

    SELECT object_id
    FROM AMW_ENTITY_HIERARCHIES
    WHERE parent_object_id   = l_object_id
    AND entity_id            = l_entity_id
    AND entity_type          = p_entity_type
    AND parent_object_type   = p_object_type;

Line: 3868

        select t2.task_id
          from pa_projects_all pp,
	       pa_tasks t1,
	       pa_tasks t2
	 where pp.project_id = p_project_id
	   and t1.project_id = pp.created_from_project_id
	   and t1.task_id = p_task_id
	   and t1.task_number = t2.task_number
	   and t2.project_id = p_project_id;

Line: 3890

  DELETE FROM amw_audit_scope_organizations
   WHERE audit_project_id = p_audit_project_id
     AND organization_id NOT IN
			(SELECT organization_id
			   FROM amw_execution_scope
			  WHERE entity_type = 'PROJECT'
			    AND entity_id = p_audit_project_id
                            AND level_id = 3);

Line: 3899

  INSERT INTO amw_audit_scope_organizations (
	   audit_project_id,
	   subsidiary_vs,
	   subsidiary_code,
	   lob_vs,
	   lob_code,
	   organization_id,
	   created_by,
	   creation_date,
	   last_updated_by,
	   last_update_date,
	   last_update_login,
	   object_version_number)
    SELECT distinct p_audit_project_id,
	   au.subsidiary_valueset,
	   au.company_code,
	   au.lob_valueset,
	   au.lob_code,
	   au.organization_id,
	   g_user_id,
	   sysdate,
	   g_user_id,
	   sysdate,
	   g_login_id,
	   1
      FROM amw_audit_units_v au, amw_execution_scope es
     WHERE au.organization_id = es.organization_id
       AND es.entity_type = 'PROJECT'
       AND es.entity_id = p_audit_project_id
       AND es.level_id = 3
       AND es.organization_id NOT IN (
			   SELECT organization_id
			     FROM amw_audit_scope_organizations
			    WHERE audit_project_id = p_audit_project_id);

Line: 3934

  DELETE FROM amw_audit_scope_processes
   WHERE audit_project_id = p_audit_project_id
     AND (organization_id, process_id) NOT IN
			(SELECT organization_id, process_id
			   FROM amw_execution_scope
			  WHERE entity_type = 'PROJECT'
			    AND entity_id = p_audit_project_id
                            AND process_id IS NOT NULL);

Line: 3943

  INSERT INTO amw_audit_scope_processes (
	   audit_project_id,
	   organization_id,
	   process_id,
	   process_org_rev_id,
	   created_by,
	   creation_date,
	   last_updated_by,
	   last_update_date,
	   last_update_login,
	   object_version_number)
    SELECT distinct p_audit_project_id,
	   organization_id,
	   process_id,
	   process_org_rev_id,
	   g_user_id,
	   sysdate,
	   g_user_id,
	   sysdate,
	   g_login_id,
	   1
      FROM amw_execution_scope
     WHERE entity_type = 'PROJECT'
       AND entity_id = p_audit_project_id
       AND level_id > 3
       AND (organization_id, process_id) NOT IN (
			     SELECT organization_id, process_id
			       FROM amw_audit_scope_processes
			      WHERE audit_project_id = p_audit_project_id);

Line: 3985

    SELECT object_id
      FROM amw_entity_Hierarchies
     WHERE entity_id = p_entity_id
       AND entity_type = p_entity_type
       AND parent_object_type = 'ORG'
       AND parent_object_id = p_org_id;

Line: 4020

    SELECT object_id
      FROM amw_entity_Hierarchies
     WHERE entity_id = p_entity_id
       AND entity_type = p_entity_type
       AND parent_object_type = 'ROOTNODE'
       AND object_type = 'ORG';

Line: 4110

    SELECT process_id
      FROM amw_execution_scope
     WHERE entity_id = p_entity_id
       AND entity_type = p_entity_type
       AND organization_id = p_org_id
       AND parent_process_id = p_proc_id;

Line: 4148

    SELECT process_id
      FROM amw_execution_scope
     WHERE entity_id = p_entity_id
       AND entity_type = p_entity_type
       AND level_id=4;