APPS.AMW_ASSOC_POST_TXN SQL Statements

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

Line: 39

     SELECT control_association_id ,control_id from amw_control_associations
            where object_type='RISK_ORG'
            and pk1 In (
            select risk_association_id
            from amw_risk_associations
            where object_type='PROCESS_ORG' and pk1= p_process_organization_id
            and risk_id = p_risk_id );

Line: 47

     delete_ctrl_org                    cc5 %ROWTYPE;

Line: 75

		select organization_id
		into l_org_id
		from amw_process_organization
		where process_organization_id = p_process_organization_id;

Line: 85

			select risk_association_id into l_risk_assoc_id from amw_risk_associations where risk_id = p_risk_id and pk1 = p_process_organization_id and object_type = 'PROCESS_ORG';

Line: 87

			insert into amw_control_associations
			(CONTROL_ASSOCIATION_ID,
			 LAST_UPDATE_DATE,
			 LAST_UPDATED_BY,
			 CREATION_DATE,
			 CREATED_BY,
			 LAST_UPDATE_LOGIN,
			 CONTROL_ID,
			 PK1,
			 OBJECT_TYPE,
			 ATTRIBUTE_CATEGORY,
			 ATTRIBUTE1,
			 ATTRIBUTE2,
			 ATTRIBUTE3,
			 ATTRIBUTE4,
			 ATTRIBUTE5,
			 ATTRIBUTE6,
			 ATTRIBUTE7,
			 ATTRIBUTE8,
			 ATTRIBUTE9,
			 ATTRIBUTE10,
			 ATTRIBUTE11,
			 ATTRIBUTE12,
			 ATTRIBUTE13,
			 ATTRIBUTE14,
			 ATTRIBUTE15,
			 OBJECT_VERSION_NUMBER,
			 EFFECTIVE_DATE_FROM,
			 EFFECTIVE_DATE_TO )
			( select
			amw_control_associations_s.nextval,
			sysdate,
			FND_GLOBAL.USER_ID,
			sysdate,
			FND_GLOBAL.USER_ID,
			FND_GLOBAL.CONC_LOGIN_ID,
			control_id,
			l_risk_assoc_id,
			'RISK_ORG',
			ATTRIBUTE_CATEGORY,
			ATTRIBUTE1,
			ATTRIBUTE2,
			ATTRIBUTE3,
			ATTRIBUTE4,
			ATTRIBUTE5,
			ATTRIBUTE6,
			ATTRIBUTE7,
			ATTRIBUTE8,
			ATTRIBUTE9,
			ATTRIBUTE10,
			ATTRIBUTE11,
			ATTRIBUTE12,
			ATTRIBUTE13,
			ATTRIBUTE14,
			ATTRIBUTE15,
			1,
        		 EFFECTIVE_DATE_FROM,
			 EFFECTIVE_DATE_TO
			from amw_control_associations
			where object_type = 'RISK'
			and pk1 = p_risk_id);

Line: 149

			SELECT  organization_id into l_org_id2 from amw_process_organization where process_organization_id = p_process_organization_id;

Line: 150

			SELECT  process_id into l_process_id2 from amw_process_organization where process_organization_id = p_process_organization_id;

Line: 152

			insert into amw_ap_associations
			(AP_ASSOCIATION_ID,
			 LAST_UPDATE_DATE,
			 LAST_UPDATED_BY,
			 CREATION_DATE,
			 CREATED_BY,
			 LAST_UPDATE_LOGIN,
			 PK1,
			 PK2,
			 PK3,
			 OBJECT_TYPE,
			 AUDIT_PROCEDURE_ID,
			 ATTRIBUTE_CATEGORY,
			 ATTRIBUTE1,
			 ATTRIBUTE2,
			 ATTRIBUTE3,
			 ATTRIBUTE4,
			 ATTRIBUTE5,
			 ATTRIBUTE6,
			 ATTRIBUTE7,
			 ATTRIBUTE8,
			 ATTRIBUTE9,
			 ATTRIBUTE10,
			 ATTRIBUTE11,
			 ATTRIBUTE12,
			 ATTRIBUTE13,
			 ATTRIBUTE14,
			 ATTRIBUTE15,
			 OBJECT_VERSION_NUMBER,
			 DESIGN_EFFECTIVENESS,
			 OP_EFFECTIVENESS )
			( select
			amw_ap_associations_s.nextval,
			sysdate,
			FND_GLOBAL.USER_ID,
			sysdate,
			FND_GLOBAL.USER_ID,
			FND_GLOBAL.CONC_LOGIN_ID,
			l_org_id,
			l_process_id2,
			pk1,
			'CTRL_ORG',
			AUDIT_PROCEDURE_ID,
			ATTRIBUTE_CATEGORY,
			ATTRIBUTE1,
			ATTRIBUTE2,
			ATTRIBUTE3,
			ATTRIBUTE4,
			ATTRIBUTE5,
			ATTRIBUTE6,
			ATTRIBUTE7,
			ATTRIBUTE8,
			ATTRIBUTE9,
			ATTRIBUTE10,
			ATTRIBUTE11,
			ATTRIBUTE12,
			ATTRIBUTE13,
			ATTRIBUTE14,
			ATTRIBUTE15,
			1,
			DESIGN_EFFECTIVENESS,
			OP_EFFECTIVENESS
			from amw_ap_associations
			where object_type = 'CTRL'
			and pk1 in (select control_id from amw_control_associations
					where object_type = 'RISK'
					and pk1 = p_risk_id));

Line: 222

			SELECT  organization_id into l_org_id2 from amw_process_organization where process_organization_id = p_process_organization_id;

Line: 223

			SELECT  process_id into l_process_id2 from amw_process_organization where process_organization_id = p_process_organization_id;

Line: 225

			insert into amw_ap_associations
			(AP_ASSOCIATION_ID,
			 LAST_UPDATE_DATE,
			 LAST_UPDATED_BY,
			 CREATION_DATE,
			 CREATED_BY,
			 LAST_UPDATE_LOGIN,
			 PK1,
			 PK2,
			 PK3,
			 OBJECT_TYPE,
			 AUDIT_PROCEDURE_ID,
			 ATTRIBUTE_CATEGORY,
			 ATTRIBUTE1,
			 ATTRIBUTE2,
			 ATTRIBUTE3,
			 ATTRIBUTE4,
			 ATTRIBUTE5,
			 ATTRIBUTE6,
			 ATTRIBUTE7,
			 ATTRIBUTE8,
			 ATTRIBUTE9,
			 ATTRIBUTE10,
			 ATTRIBUTE11,
			 ATTRIBUTE12,
			 ATTRIBUTE13,
			 ATTRIBUTE14,
			 ATTRIBUTE15,
			 OBJECT_VERSION_NUMBER,
			 DESIGN_EFFECTIVENESS,
			 OP_EFFECTIVENESS )
			( select
			amw_ap_associations_s.nextval,
			sysdate,
			FND_GLOBAL.USER_ID,
			sysdate,
			FND_GLOBAL.USER_ID,
			FND_GLOBAL.CONC_LOGIN_ID,
			l_org_id,
			l_process_id2,
			p_control_id,
			'CTRL_ORG',
			AUDIT_PROCEDURE_ID,
			ATTRIBUTE_CATEGORY,
			ATTRIBUTE1,
			ATTRIBUTE2,
			ATTRIBUTE3,
			ATTRIBUTE4,
			ATTRIBUTE5,
			ATTRIBUTE6,
			ATTRIBUTE7,
			ATTRIBUTE8,
			ATTRIBUTE9,
			ATTRIBUTE10,
			ATTRIBUTE11,
			ATTRIBUTE12,
			ATTRIBUTE13,
			ATTRIBUTE14,
			ATTRIBUTE15,
			1,
			DESIGN_EFFECTIVENESS,
			OP_EFFECTIVENESS
			from amw_ap_associations
			where object_type = 'CTRL'
			and pk1 = p_control_id);

Line: 310

                  FETCH cc5            INTO delete_ctrl_org;

Line: 314

                   delete   from amw_ap_associations
                   where object_type='CTRL_ORG'
                   and pk1  = ( SELECT  organization_id from amw_process_organization
                   where process_organization_id = p_process_organization_id )
                   and pk2  = ( SELECT  process_id from amw_process_organization
                   where process_organization_id = p_process_organization_id )
                   AND pk3 = delete_ctrl_org.control_id
                   and
                   not exists ( select control_id from amw_control_associations aca, amw_risk_associations ara
                                where aca.pk1= ara.risk_association_id
                                and ara.object_type = 'PROCESS_ORG'
                                and aca.object_type = 'RISK_ORG'
                                and control_id = delete_ctrl_org.control_id  ) ;

Line: 328

                   delete   from amw_control_associations
                   where control_association_id = delete_ctrl_org.control_association_id ;

Line: 335

                delete   from amw_ap_associations
                where object_type='CTRL_ORG'
                and pk1  = ( SELECT  organization_id from amw_process_organization
                where process_organization_id = p_process_organization_id )
                and pk2  = ( SELECT  process_id from amw_process_organization
                where process_organization_id = p_process_organization_id )
                AND pk3 = p_control_id
                and
                not exists ( select control_id from amw_control_associations aca, amw_risk_associations ara
                             where aca.pk1= ara.risk_association_id
                             and ara.object_type = 'PROCESS_ORG'
                             and aca.object_type = 'RISK_ORG'
                             and control_id = p_control_id  );