[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select child_process_id,parent_process_id,parent_process_name,process_name from amw_process_hierarchy_v
start with child_process_id=p_process_id
connect by prior child_process_id=parent_process_id;
select parent_process_id,child_process_id,parent_process_name,process_name
from amw_process_hierarchy_v
where parent_process_id=l_cpid;
select nvl(risk_count,0) as risk_count,
nvl(control_count,0) as control_count
from amw_process
where process_id=p_process_id;
select significant_process_flag,standard_process_flag,approval_status,
certification_status,process_category,process_owner_id,process_id,created_from,
request_id,program_application_id,program_id,program_update_date,
attribute_category,attribute1,attribute2,attribute3,attribute4,
attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,
attribute11,attribute12,attribute13,attribute14,attribute15,
security_group_id,object_version_number,
nvl(control_count,0) as control_count,
nvl(risk_count,0) as risk_count,nvl(org_count,0) as org_count,
finance_owner_id,application_owner_id
from amw_process where process_id=p_process_id;
l_do_insert varchar2(30) := 'INSERT';
select count(*) row_count from amw_process_organization
where process_id=p_process_id and organization_id=p_organization_id;
select count(*) row_count from amw_process_organization
where process_id=p_parent_process_id and organization_id=p_organization_id;
select end_date from amw_process_organization
where process_id=p_parent_process_id and organization_id=p_organization_id;
select count(*) row_count from amw_process_organization
where process_id=p_parent_process_id and organization_id=p_organization_id;
select end_date from amw_process_organization
where process_id=p_parent_process_id and organization_id=p_organization_id;
l_apo_type.last_update_date := sysdate;
l_apo_type.last_updated_by := G_USER_ID;
l_apo_type.LAST_UPDATE_LOGIN := G_LOGIN_ID;
l_apo_type.PROGRAM_UPDATE_DATE := l_amwp_rowtype.PROGRAM_UPDATE_DATE;
select count(*) into l_count from amw_process_organization
where process_id=p_process_id and organization_id=p_organization_id;
l_do_insert := 'UPDATE';
select count(*) into l_count from amw_process_organization
where process_id=p_parent_process_id and organization_id=p_organization_id;
select end_date into l_parent_process_end_date from amw_process_organization
where process_id=p_parent_process_id and organization_id=p_organization_id;
select count(*) into l_count from amw_process_organization
where process_id=p_parent_process_id and organization_id=p_organization_id;
l_do_insert := 'INSERT';
select end_date into l_parent_process_end_date from amw_process_organization
where process_id=p_parent_process_id and organization_id=p_organization_id;
l_do_insert := 'DELETE';
l_do_insert := 'DELETE';
p_do_insert => l_do_insert,
p_org_count => l_org_count,
p_rcm_assoc => p_rcm_assoc,
p_batch_id => p_batch_id,
p_rcm_org_intf_id => p_rcm_org_intf_id,
p_risk_id => p_risk_id,
p_control_id => p_control_id,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
p_do_insert in varchar2 := 'INSERT',
p_org_count in number := 0,
p_rcm_assoc in varchar2 := 'N',
p_batch_id in number := null,
p_rcm_org_intf_id in number := null,
p_risk_id in number := null,
p_control_id in number := null,
p_commit in varchar2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2
)
is
L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Process_Org';
SELECT AMW_PROCESS_organization_s.NEXTVAL FROM dual;
select nvl(risk_count,0) as risk_count,
nvl(control_count,0) as control_count from amw_process
where process_id=p_apo_type.process_id;
savepoint insert_in_amwpo_pvt;
if p_do_insert = 'INSERT' then
open c_proc_org_s;
insert into AMW_process_organization(
control_count,
risk_count,
top_process_id,
process_organization_id,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
creation_date,
created_by,
LAST_UPDATE_LOGIN,
process_id,
standard_process_flag,
risk_category,
approval_status,
certification_status,
last_audit_status,
organization_id,
last_certification_date,
last_audit_date,
next_audit_date,
application_owner_id,
process_owner_id,
process_category_code,
significant_process_flag,
finance_owner_id,
created_from,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
security_group_id,
object_version_number,
end_date) values(
p_apo_type.control_count,
p_apo_type.risk_count,
p_apo_type.top_process_id,
l_process_organization_id,
sysdate,
G_USER_ID,
sysdate,
G_USER_ID,
G_LOGIN_ID,
p_apo_type.process_id,
decode(p_apo_type.standard_process_flag,null,'Y',p_apo_type.standard_process_flag),
decode(p_apo_type.risk_category,null,'High',p_apo_type.risk_category),
p_apo_type.approval_status,
p_apo_type.certification_status,
p_apo_type.last_audit_status,
p_apo_type.organization_id,
p_apo_type.last_certification_date,
p_apo_type.last_audit_date,
p_apo_type.next_audit_date,
p_apo_type.application_owner_id,
p_apo_type.process_owner_id,
p_apo_type.process_category_code,
p_apo_type.significant_process_flag,
p_apo_type.finance_owner_id,
p_apo_type.created_from,
p_apo_type.request_id,
p_apo_type.program_application_id,
p_apo_type.program_id,
p_apo_type.program_update_date,
p_apo_type.attribute_category,
p_apo_type.attribute1,
p_apo_type.attribute2,
p_apo_type.attribute3,
p_apo_type.attribute4,
p_apo_type.attribute5,
p_apo_type.attribute6,
p_apo_type.attribute7,
p_apo_type.attribute8,
p_apo_type.attribute9,
p_apo_type.attribute10,
p_apo_type.attribute11,
p_apo_type.attribute12,
p_apo_type.attribute13,
p_apo_type.attribute14,
p_apo_type.attribute15,
p_apo_type.security_group_id,
1,
null
);
select nvl(object_version_number,1) into l_obj_num from amw_process where process_id=p_apo_type.process_id;
select nvl(org_count,0) into l_org_count from amw_process where process_id=p_apo_type.process_id;
/* update amw_process set org_count=l_org_count,
object_version_number=l_obj_num,
last_update_date=sysdate,last_updated_by=G_USER_ID,last_update_login=G_LOGIN_ID
where process_id=p_apo_type.process_id; */
elsif p_do_insert = 'UPDATE' then
--record already exists in some process hierarchy in amw_process_organization
--so, do not insert, just update with top_process_id,end_date set to null
select nvl(object_version_number,1) into l_obj_num from amw_process_organization
where process_id=p_apo_type.process_id and organization_id=p_apo_type.organization_id;
select end_date into process_end_date from amw_process_organization
where process_id=p_apo_type.process_id
and organization_id=p_apo_type.organization_id;
update amw_process_organization
set top_process_id=p_apo_type.top_process_id,
risk_count=l_risk_ctrl_count.risk_count,
control_count=l_risk_ctrl_count.control_count,
object_version_number=l_obj_num,
end_date=null,
last_updated_by=G_USER_ID,
last_update_date=sysdate,
last_update_login=G_LOGIN_ID
where process_id=p_apo_type.process_id and organization_id=p_apo_type.organization_id;
select nvl(object_version_number,1) into l_obj_num from amw_process where process_id=p_apo_type.process_id;
select nvl(org_count,0) into l_org_count from amw_process where process_id=p_apo_type.process_id;
/* update amw_process
set org_count=l_org_count,
object_version_number=l_obj_num,
last_update_date=sysdate,
last_updated_by=G_USER_ID,
last_update_login=G_LOGIN_ID
where process_id=p_apo_type.process_id; */
elsif p_do_insert = 'DELETE' then
l_org_count := p_org_count-1;
select nvl(object_version_number,1) into l_obj_num from amw_process_organization
where process_id=p_apo_type.process_id and organization_id=p_apo_type.organization_id;
update amw_process_organization
set end_date=sysdate,
object_version_number=l_obj_num,
risk_count=0,
control_count=0,
last_updated_by=G_USER_ID,
last_update_date=sysdate
where process_id=p_apo_type.process_id and organization_id=p_apo_type.organization_id;
select nvl(object_version_number,1) into l_obj_num from amw_process where process_id=p_apo_type.process_id;
select nvl(org_count,0) into l_org_count from amw_process where process_id=p_apo_type.process_id;
/* update amw_process
set org_count=l_org_count,
object_version_number=l_obj_num,
last_updated_by=G_USER_ID,
last_update_date=sysdate,
last_update_login=G_LOGIN_ID
where process_id=p_apo_type.process_id; */
select process_organization_id into l_process_organization_id from amw_process_organization
where process_id=p_apo_type.process_id and organization_id=p_apo_type.organization_id;
ROLLBACK TO insert_in_amwpo_pvt;
ROLLBACK TO insert_in_amwpo_pvt;
ROLLBACK TO insert_in_amwpo_pvt;
select risk_likelihood_code,
risk_impact_code,
upper(material) as material,
decode(nvl(upper(material),'N'),'N',null,material_value) as material_value
from amw_rcm_org_interface
where batch_id=p_batch_id
and rcm_org_interface_id=p_rcm_org_intf_id;
select ap_name,
upper(nvl(design_effectiveness,'N')) as design_effectiveness,
upper(nvl(op_effectiveness,'N')) as op_effectiveness
from amw_rcm_org_interface
where batch_id=p_batch_id
and rcm_org_interface_id=p_rcm_org_intf_id;
select risk_association_id
into l_risk_association_id
from amw_risk_associations
where object_type='PROCESS_ORG'
and pk1=p_process_organization_id
and risk_id=p_risk_id;
fnd_file.put_line(fnd_file.LOG, 'After Select, l_risk_association_id: '||l_risk_association_id);
UPDATE AMW_RISK_ASSOCIATIONS
SET OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
,RISK_LIKELIHOOD_CODE=L_C1_TYPE.RISK_LIKELIHOOD_CODE
,RISK_IMPACT_CODE=L_C1_TYPE.RISK_IMPACT_CODE
,MATERIAL=L_C1_TYPE.MATERIAL
,MATERIAL_VALUE=L_C1_TYPE.MATERIAL_VALUE
,LAST_UPDATE_DATE=SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
WHERE RISK_ASSOCIATION_ID=L_RISK_ASSOCIATION_ID;
fnd_file.put_line(fnd_file.LOG, 'UPDATED AMW_RISK_ASSOCIATIONS');
select amw_risk_associations_s.nextval into l_risk_association_id from dual;
fnd_file.put_line(fnd_file.LOG, 'VALUES TO BE INSERTED INTO AMW_RISK_ASSOCIATIONS');
insert into amw_risk_associations(risk_association_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
risk_id,
pk1,
object_type,
object_version_number,
effective_date_from,
risk_likelihood_code,
risk_impact_code,
material,
material_value) values (
---amw_risk_associations_s.nextval,
l_risk_association_id,
sysdate,
G_USER_ID,
sysdate,
G_USER_ID,
G_LOGIN_ID,
p_risk_id,
p_process_organization_id,
'PROCESS_ORG',
1,
sysdate,
l_c1_type.risk_likelihood_code,
l_c1_type.risk_impact_code,
l_c1_type.material,
l_c1_type.material_value);
select CONTROL_association_id
into l_CONTROL_association_id
from amw_CONTROL_associations
where object_type='RISK_ORG'
and pk1=l_risk_association_id
and CONTROL_id=p_CONTROL_id;
fnd_file.put_line(fnd_file.LOG, 'After Select, l_CONTROL_association_id: '||l_CONTROL_association_id);
select amw_CONTROL_associations_s.nextval into l_CONTROL_association_id from dual;
fnd_file.put_line(fnd_file.LOG, 'VALUES TO BE INSERTED INTO AMW_CONTROL_ASSOCIATIONS');
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,
object_version_number,
effective_date_from) values (
---amw_risk_associations_s.nextval,
l_CONTROL_association_id,
sysdate,
G_USER_ID,
sysdate,
G_USER_ID,
G_LOGIN_ID,
p_CONTROL_id,
l_risk_association_id,
'RISK_ORG',
1,
sysdate);
select acct_assoc_id,last_update_date,last_updated_by,
creation_date,created_by,last_update_login,object_type,pk1,pk2,pk3,pk4,pk5,
natural_account_id,statement_id,statement_line_id,attribute_category,
attribute1,attribute2,attribute3,attribute4,
attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,
attribute11,attribute12,---attribute13,
attribute14,attribute15,
security_group_id,object_version_number
from amw_acct_associations
where object_type='PROCESS'
and pk1=l_process_id;
select acct_assoc_id,natural_account_id
from amw_acct_associations where object_type='PROCESS_ORG'
and pk1=l_process_organization_id; ---and risk_id=;
l_update c2%rowtype;
SELECT 1
FROM amw_acct_associations
WHERE acct_assoc_id = l_id;
select count(*) into row_count from amw_acct_associations
where object_type='PROCESS_ORG'
and pk1=l_process_organization_id
and natural_account_id=l_aaa_row.natural_account_id;
select amw_acct_associations_s.nextval into l_acct_assoc_id from dual;
insert into amw_acct_associations (acct_assoc_id,last_update_date,
last_updated_by,creation_date,created_by,
last_update_login,object_type,pk1,pk2,pk3,pk4,pk5,
natural_account_id,statement_id,statement_line_id,
attribute_category,attribute1,attribute2,
attribute3,attribute4,attribute5,attribute6,attribute7,
attribute8,attribute9,attribute10,attribute11,attribute12,
---attribute13,
attribute14,attribute15,security_group_id,
object_version_number) values
(l_acct_assoc_id,sysdate,G_USER_ID,sysdate,G_USER_ID,
G_LOGIN_ID,'PROCESS_ORG',l_process_organization_id,l_aaa_row.pk2,
l_aaa_row.pk3,l_aaa_row.pk4,l_aaa_row.pk5,l_aaa_row.natural_account_id,
l_aaa_row.statement_id,l_aaa_row.statement_line_id,l_aaa_row.attribute_category,
l_aaa_row.attribute1,l_aaa_row.attribute2,l_aaa_row.attribute3,l_aaa_row.attribute4,
l_aaa_row.attribute5,l_aaa_row.attribute6,l_aaa_row.attribute7,l_aaa_row.attribute8,
l_aaa_row.attribute9,l_aaa_row.attribute10,l_aaa_row.attribute11,l_aaa_row.attribute12,
----l_aaa_row.attribute13,
l_aaa_row.attribute14,l_aaa_row.attribute15,
l_aaa_row.security_group_id,1);
fetch c2 into l_update;
delete from amw_acct_associations
where acct_assoc_id=l_update.acct_assoc_id
and object_type='PROCESS_ORG'
and pk1=p_process_organization_id;
select risk_association_id,last_update_date,last_updated_by,
creation_date,created_by,last_update_login,risk_id,pk1,pk2,pk3,pk4,pk5,
object_type,attribute_category,attribute1,attribute2,attribute3,attribute4,
attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,
attribute11,attribute12,attribute13,attribute14,attribute15,
security_group_id,object_version_number,effective_date_from,
effective_date_to,risk_likelihood_code,risk_impact_code
---added by npanandi on 01/22/2004 for 3362371 bug fix
,material,material_value
---finished addition on 01/22/2004
from amw_risk_associations
where object_type='PROCESS'
and pk1=l_process_id;
select risk_association_id,risk_id
from amw_risk_associations where object_type='PROCESS_ORG'
and pk1=l_process_organization_id; ---and risk_id=;
l_update c2%rowtype;
select count(*) into row_count from amw_risk_associations
where object_type='PROCESS_ORG'
and pk1=l_process_organization_id
and risk_id=l_ara_row.risk_id;
select amw_risk_associations_s.nextval into l_risk_association_id from dual;
insert into amw_risk_associations (risk_association_id,last_update_date,
last_updated_by,creation_date,created_by,
last_update_login,risk_id,pk1,pk2,pk3,pk4,pk5,
object_type,attribute_category,attribute1,attribute2,
attribute3,attribute4,attribute5,attribute6,attribute7,
attribute8,attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15,security_group_id,
object_version_number,effective_date_from,effective_date_to,
risk_likelihood_code,risk_impact_code
---added by npanandi on 01/22/2004 for 3362371 bug fix
,material,material_value
---finished addition on 01/22/2004
) values
(l_risk_association_id,sysdate,G_USER_ID,sysdate,G_USER_ID,
G_LOGIN_ID,l_ara_row.risk_id,l_process_organization_id,l_ara_row.pk2,
l_ara_row.pk3,l_ara_row.pk4,l_ara_row.pk5,'PROCESS_ORG',l_ara_row.attribute_category,
l_ara_row.attribute1,l_ara_row.attribute2,l_ara_row.attribute3,l_ara_row.attribute4,
l_ara_row.attribute5,l_ara_row.attribute6,l_ara_row.attribute7,l_ara_row.attribute8,
l_ara_row.attribute9,l_ara_row.attribute10,l_ara_row.attribute11,l_ara_row.attribute12,
l_ara_row.attribute13,l_ara_row.attribute14,l_ara_row.attribute15,
l_ara_row.security_group_id,1,
l_ara_row.effective_date_from,l_ara_row.effective_date_to,
l_ara_row.risk_likelihood_code,l_ara_row.risk_impact_code
---added by npanandi on 01/22/2004 for 3362371 bug fix
,l_ara_row.material,l_ara_row.material_value
---finished addition on 01/22/2004
);
fetch c2 into l_update;
delete from amw_risk_associations
where risk_association_id=l_update.risk_association_id
and object_type='PROCESS_ORG'
and pk1=p_process_organization_id;
p_risk_association_id => l_update.risk_association_id,
p_risk_id => l_update.risk_id,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
select control_association_id,last_update_date,last_updated_by,
creation_date,created_by,last_update_login,control_id,pk1,pk2,pk3,pk4,pk5,
object_type,attribute_category,attribute1,attribute2,attribute3,attribute4,
attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,
attribute11,attribute12,attribute13,attribute14,attribute15,
security_group_id,object_version_number,effective_date_from,
effective_date_to
from amw_control_associations
where object_type='RISK' and pk1=l_risk_id;
select control_association_id,control_id
from amw_control_associations where object_type='RISK_ORG'
and pk1=l_risk_association_id; ---and risk_id=;
l_update c2%rowtype;
select count(*) into row_count from amw_control_associations
where object_type='RISK_ORG'
and pk1=l_risk_association_id
and control_id=l_aca_row.control_id;
select amw_control_associations_s.nextval into l_control_association_id from dual;
insert into amw_control_associations(control_association_id,last_update_date,
last_updated_by,creation_date,created_by,
last_update_login,control_id,pk1,pk2,pk3,pk4,pk5,
object_type,attribute_category,attribute1,attribute2,
attribute3,attribute4,attribute5,attribute6,attribute7,
attribute8,attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15,security_group_id,
object_version_number,effective_date_from,effective_date_to)
values (l_control_association_id,sysdate,G_USER_ID,sysdate,G_USER_ID,
G_LOGIN_ID,l_aca_row.control_id,l_risk_association_id,l_aca_row.pk2,
l_aca_row.pk3,l_aca_row.pk4,l_aca_row.pk5,'RISK_ORG',l_aca_row.attribute_category,
l_aca_row.attribute1,l_aca_row.attribute2,l_aca_row.attribute3,l_aca_row.attribute4,
l_aca_row.attribute5,l_aca_row.attribute6,l_aca_row.attribute7,l_aca_row.attribute8,
l_aca_row.attribute9,l_aca_row.attribute10,l_aca_row.attribute11,l_aca_row.attribute12,
l_aca_row.attribute13,l_aca_row.attribute14,l_aca_row.attribute15,
l_aca_row.security_group_id,1,
l_aca_row.effective_date_from,l_aca_row.effective_date_to);
fetch c2 into l_update;
p_control_association_id => l_update.control_association_id,
p_control_id => l_update.control_id,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
delete from amw_control_associations
where control_association_id=l_update.control_association_id
and object_type='RISK_ORG'
and pk1=p_risk_association_id;
select ap_association_id,last_update_date,last_updated_by,
creation_date,created_by,last_update_login,pk1,pk2,pk3,pk4,pk5,
object_type,audit_procedure_id,attribute_category,attribute1,attribute2,
attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,
attribute15,security_group_id,object_version_number,design_effectiveness,
op_effectiveness
from amw_ap_associations where object_type='CTRL' and pk1=l_control_id;
select ap_association_id,audit_procedure_id
from amw_ap_associations where object_type='CTRL_ORG'
and pk1=l_control_association_id; ---and risk_id=;
select ap_association_id,audit_procedure_id
from amw_process_organization apo, amw_risk_associations ara, amw_control_associations aca, amw_ap_associations apa
where apo.process_organization_id = ara.pk1
and ara.risk_association_id = aca.pk1
and aca.control_association_id = p_control_association_id
and apa.pk1 = apo.organization_id
and apa.pk2 = apo.process_id
and apa.pk3 = aca.control_id
and apa.object_type='CTRL_ORG';
l_update c2%rowtype;
select apo.process_id, apo.organization_id
into l_process_id, l_org_id
from amw_process_organization apo, amw_risk_associations ara, amw_control_associations aca
where apo.process_organization_id = ara.pk1
and ara.risk_association_id = aca.pk1
and aca.control_association_id = p_control_association_id;
** select count(*) into row_count from amw_ap_associations
** where object_type='CTRL_ORG'
** and pk1=l_control_association_id
** and audit_procedure_id=l_aaa_row.audit_procedure_id;
select count(*) into row_count from amw_ap_associations
where object_type='CTRL_ORG'
and pk1=l_org_id
and pk2 = l_process_id
and pk3 = p_control_id
and audit_procedure_id=l_aaa_row.audit_procedure_id;
select amw_ap_associations_s.nextval into l_ap_association_id from dual;
insert into amw_ap_associations(ap_association_id,last_update_date,
last_updated_by,creation_date,created_by,
last_update_login,pk1,pk2,pk3,pk4,pk5,object_type,
audit_procedure_id,attribute_category,attribute1,attribute2,
attribute3,attribute4,attribute5,attribute6,attribute7,
attribute8,attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15,security_group_id,
object_version_number,design_effectiveness,op_effectiveness)
values
(l_ap_association_id,sysdate,G_USER_ID,sysdate,G_USER_ID,
G_LOGIN_ID,l_org_id,l_process_id, p_control_id,
l_aaa_row.pk4,l_aaa_row.pk5,'CTRL_ORG',l_aaa_row.audit_procedure_id,
l_aaa_row.attribute_category,l_aaa_row.attribute1,l_aaa_row.attribute2,
l_aaa_row.attribute3,l_aaa_row.attribute4,l_aaa_row.attribute5,l_aaa_row.attribute6,
l_aaa_row.attribute7,l_aaa_row.attribute8,l_aaa_row.attribute9,l_aaa_row.attribute10,
l_aaa_row.attribute11,l_aaa_row.attribute12,l_aaa_row.attribute13,l_aaa_row.attribute14,
l_aaa_row.attribute15,l_aaa_row.security_group_id,1,
l_aaa_row.design_effectiveness,l_aaa_row.op_effectiveness);
fetch c2 into l_update;
delete from amw_ap_associations
where ap_association_id=l_update.ap_association_id ;
select process_id,
nvl(risk_count,0) as risk_count,
nvl(control_count,0) as control_count,
process_organization_id,object_version_number
FROM amw_process_organization
WHERE organization_id = p_organization_id AND process_id IN (
SELECT DISTINCT p2.process_id
FROM amw_process p1,
amw_process p2,
amw_process_organization apo1,
amw_process_organization apo2,
wf_activities wa
WHERE (p2.NAME, p2.item_type) IN (
SELECT activity_name,
activity_item_type
FROM wf_process_activities
CONNECT BY activity_name = PRIOR process_name
AND activity_item_type = PRIOR process_item_type
START WITH activity_name = p1.NAME
AND activity_item_type = p1.item_type)
AND p2.NAME = wa.NAME
AND p2.item_type = wa.item_type
AND wa.end_date IS NULL
AND p2.process_id = apo2.process_id
AND apo2.organization_id = apo1.organization_id
AND p1.process_id = apo1.process_id
---and apo1.process_id=142
AND apo1.process_id = p_process_id
AND apo1.organization_id = p_organization_id);
UPDATE amw_process_organization
SET risk_count = assoc_risk.risk_count,
control_count = assoc_risk.control_count,
object_version_number = assoc_risk.object_version_number,
last_updated_by = g_user_id,
last_update_date = SYSDATE,
last_update_login = g_login_id
WHERE process_organization_id = assoc_risk.process_organization_id;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => x_return_status
);
select aphv.parent_process_id,apo.end_date
from amw_process_hierarchy_v aphv,amw_process_organization apo
where aphv.child_process_id=p_process_id
and apo.process_id=p_process_id
and apo.organization_id=p_organization_id;