[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(1)
FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb
WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
AND ara.pk1 = p_certification_id
AND ara.pk2 = p_org_id
AND ara.risk_rev_id = arb.risk_rev_id
AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = ara.pk2 --org_id
AND nvl(aov.pk4_value, -1) = nvl(ara.pk3, -1) --process_id
AND aov.pk1_value = ara.risk_id
AND aov.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1)
AND nvl(aov2.pk4_value, -1) = nvl(aov.pk4_value, -1)
AND aov2.pk3_value = aov.pk3_value
AND aov2.pk1_value = aov.pk1_value)
AND aov.audit_result_code <> 'EFFECTIVE'
);
SELECT count(1)
FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb
WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
AND ara.pk1 = p_certification_id
AND ara.pk2 = p_org_id
AND ara.risk_rev_id = arb.risk_rev_id
AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = ara.pk2 --org_id
AND nvl(aov.pk4_value, -1) = nvl(ara.pk3, -1) --process_id
AND aov.pk1_value = ara.risk_id
AND aov.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1)
AND nvl(aov2.pk4_value, -1) = nvl(aov.pk4_value, -1) --process_id
AND aov2.pk3_value = aov.pk3_value
AND aov2.pk1_value = aov.pk1_value)
AND aov.audit_result_code IS NOT NULL
);
SELECT count(1)
FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
FROM amw_risk_associations ara, amw_risks_b arb
WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
AND ara.risk_rev_id = arb.risk_rev_id
AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
AND ara.pk1 = p_certification_id
AND ara.pk2 = p_org_id);
SELECT count(1)
FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
FROM amw_risk_associations ara, amw_risks_b arb
WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
AND ara.risk_rev_id = arb.risk_rev_id
AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
AND ara.pk1 = p_certification_id
AND ara.pk2 = p_org_id);
SELECT count(1)
FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb
WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
AND ara.pk1 = p_certification_id
AND ara.pk2 = p_org_id
AND ara.risk_rev_id = arb.risk_rev_id
AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = ara.pk2 --org_id
AND nvl(aov.pk4_value, -1) = nvl(ara.pk3, -1) --process_id
AND aov.pk1_value = ara.risk_id
AND aov.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1)
AND nvl(aov2.pk4_value, -1) = nvl(aov.pk4_value, -1)
AND aov2.pk3_value = aov.pk3_value
AND aov2.pk1_value = aov.pk1_value)
AND aov.audit_result_code <> 'EFFECTIVE'
);
SELECT count(1)
FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND acb.control_rev_id = aca.control_rev_id
AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
AND aca.pk1 = p_certification_id
AND aca.pk2 = p_org_id
AND aov.object_name = 'AMW_ORG_CONTROL'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = p_org_id
AND aov.pk1_value = aca.control_id
AND aov.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
AND aov2.pk3_value = aov.pk3_value
AND aov2.pk1_value = aov.pk1_value
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1))
AND aov.audit_result_code IS NOT NULL);
SELECT count(1)
FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
FROM amw_control_associations aca, amw_controls_b acb
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND acb.control_rev_id = aca.control_rev_id
AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
AND aca.pk1 = p_certification_id
AND aca.pk2 = p_org_id);
SELECT count(1)
FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND acb.control_rev_id = aca.control_rev_id
AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
AND aca.pk1 = p_certification_id
AND aca.pk2 = p_org_id
AND aov.object_name = 'AMW_ORG_CONTROL'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = p_org_id
AND aov.pk1_value = aca.control_id
AND aov.audit_result_code <> 'EFFECTIVE'
AND aov.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
AND aov2.pk3_value = aov.pk3_value
AND aov2.pk1_value = aov.pk1_value
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1))
);
SELECT count(1)
FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND acb.control_rev_id = aca.control_rev_id
AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
AND aca.pk1 = p_certification_id
AND aca.pk2 = p_org_id
AND aov.object_name = 'AMW_ORG_CONTROL'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = p_org_id
AND aov.pk1_value = aca.control_id
AND aov.audit_result_code <> 'EFFECTIVE'
AND aov.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
AND aov2.pk3_value = aov.pk3_value
AND aov2.pk1_value = aov.pk1_value
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1))
);
SELECT count(1)
FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
FROM amw_control_associations aca, amw_controls_b acb
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND acb.control_rev_id = aca.control_rev_id
AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
AND aca.pk1 = p_certification_id
AND aca.pk2 = p_org_id);
SELECT count(distinct process_id)
FROM amw_execution_scope
WHERE organization_id = p_org_id
AND entity_id = p_certification_id
AND level_id > 4;
SELECT count(DISTINCT aes.process_id)
FROM amw_execution_scope aes, amw_opinions_log_v opinion, amw_process_organization apo
WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
AND aes.organization_id = p_org_id
AND aes.entity_id = p_certification_id
AND aes.level_id > 4
AND apo.process_org_rev_id = aes.process_org_rev_id
AND nvl(apo.significant_process_flag, 'N') = nvl(p_sig_process, nvl(apo.significant_process_flag, 'N'))
AND opinion.pk1_value = aes.process_id
AND opinion.pk3_value = aes.organization_id
AND opinion.pk2_value = aes.entity_id
AND opinion.opinion_type_code = 'CERTIFICATION'
AND opinion.object_name = 'AMW_PROCESS_ORG'
AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
AND aov2.pk3_value = opinion.pk3_value
AND aov2.pk1_value = opinion.pk1_value
AND aov2.pk2_value = opinion.pk2_value
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1));
SELECT count(DISTINCT aes.process_id)
FROM amw_execution_scope aes, amw_opinions_log_v opinion, amw_process_organization apo
WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
AND aes.organization_id = p_org_id
AND aes.entity_id = p_certification_id
AND aes.level_id > 4
AND apo.process_org_rev_id = aes.process_org_rev_id
AND nvl(apo.significant_process_flag, 'N') = nvl(p_sig_process, nvl(apo.significant_process_flag, 'N'))
AND opinion.pk1_value = aes.process_id
AND opinion.pk3_value = aes.organization_id
AND opinion.pk2_value = aes.entity_id
AND opinion.opinion_type_code = 'CERTIFICATION'
AND opinion.object_name = 'AMW_PROCESS_ORG'
AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
AND aov2.pk3_value = opinion.pk3_value
AND aov2.pk1_value = opinion.pk1_value
AND aov2.pk2_value = opinion.pk2_value
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1))
AND opinion.audit_result_code <> 'EFFECTIVE';
SELECT count(1)
FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.control_id
FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND acb.control_rev_id = aca.control_rev_id
AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
AND aca.pk1 = p_certification_id
AND aca.pk2 = p_org_id
AND aca.pk3 IN (SELECT DISTINCT process_id
FROM amw_execution_scope
START WITH process_id = p_process_id
AND entity_type = aca.object_type
AND organization_id = p_org_id
AND entity_id = p_certification_id
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type
)
AND aov.object_name = 'AMW_ORG_CONTROL'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = p_org_id
AND aov.pk1_value = aca.control_id
AND aov.audit_result_code <> 'EFFECTIVE'
AND aov.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
AND aov2.pk3_value = aov.pk3_value
AND aov2.pk1_value = aov.pk1_value
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1))
);
SELECT count(1)
FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.control_id
FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND acb.control_rev_id = aca.control_rev_id
AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
AND aca.pk1 = p_certification_id
AND aca.pk2 = p_org_id
AND aca.pk3 IN (SELECT DISTINCT process_id
FROM amw_execution_scope
START WITH process_id = p_process_id
AND entity_type = aca.object_type
AND organization_id = p_org_id
AND entity_id = p_certification_id
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type
)
AND aov.object_name = 'AMW_ORG_CONTROL'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = p_org_id
AND aov.pk1_value = aca.control_id
AND aov.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
AND aov2.pk3_value = aov.pk3_value
AND aov2.pk1_value = aov.pk1_value
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1))
AND aov.audit_result_code IS NOT NULL);
SELECT count(1)
FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.control_id
FROM amw_control_associations aca, amw_controls_b acb
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND acb.control_rev_id = aca.control_rev_id
AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
AND aca.pk1 = p_certification_id
AND aca.pk2 = p_org_id
AND aca.pk3 IN (SELECT DISTINCT process_id
FROM amw_execution_scope
START WITH process_id = p_process_id
AND entity_type = aca.object_type
AND organization_id = p_org_id
AND entity_id = p_certification_id
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type
));
SELECT count(1)
FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.control_id
FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND acb.control_rev_id = aca.control_rev_id
AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
AND aca.pk1 = p_certification_id
AND aca.pk2 = p_org_id
AND aca.pk3 IN (SELECT DISTINCT process_id
FROM amw_execution_scope
START WITH process_id = p_process_id
AND entity_type = aca.object_type
AND organization_id = p_org_id
AND entity_id = p_certification_id
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type
)
AND aov.object_name = 'AMW_ORG_CONTROL'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = p_org_id
AND aov.pk1_value = aca.control_id
AND aov.audit_result_code <> 'EFFECTIVE'
AND aov.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
AND aov2.pk3_value = aov.pk3_value
AND aov2.pk1_value = aov.pk1_value
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1))
);
SELECT count(1)
FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.control_id
FROM amw_control_associations aca, amw_controls_b acb
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND acb.control_rev_id = aca.control_rev_id
AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
AND aca.pk1 = p_certification_id
AND aca.pk2 = p_org_id
AND aca.pk3 IN (SELECT DISTINCT process_id
FROM amw_execution_scope
START WITH process_id = p_process_id
AND entity_type = aca.object_type
AND organization_id = p_org_id
AND entity_id = p_certification_id
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type
));
SELECT count(1)
FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
FROM amw_risk_associations ara, amw_risks_b arb
WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
AND ara.risk_rev_id = arb.risk_rev_id
AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
AND ara.pk1 = p_certification_id
AND ara.pk2 = p_org_id
AND ara.pk3 IN (SELECT DISTINCT process_id
FROM amw_execution_scope
START WITH process_id = p_process_id
AND entity_type = ara.object_type
AND organization_id = p_org_id
AND entity_id = p_certification_id
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type
));
SELECT count(1)
FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb
WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
AND ara.pk1 = p_certification_id
AND ara.pk2 = p_org_id
AND ara.risk_rev_id = arb.risk_rev_id
AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
AND ara.pk3 IN (SELECT DISTINCT process_id
FROM amw_execution_scope
START WITH process_id = p_process_id
AND entity_type = ara.object_type
AND organization_id = p_org_id
AND entity_id = p_certification_id
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type
)
AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = ara.pk2 --org_id
AND aov.pk4_value = ara.pk3 --process_id
AND aov.pk1_value = ara.risk_id
AND aov.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1)
AND aov2.pk4_value = aov.pk4_value
AND aov2.pk3_value = aov.pk3_value
AND aov2.pk1_value = aov.pk1_value)
AND aov.audit_result_code <> 'EFFECTIVE'
);
SELECT count(1)
FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
FROM amw_risk_associations ara, amw_risks_b arb
WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
AND ara.risk_rev_id = arb.risk_rev_id
AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
AND ara.pk1 = p_certification_id
AND ara.pk2 = p_org_id
AND ara.pk3 IN (SELECT DISTINCT process_id
FROM amw_execution_scope
START WITH process_id = p_process_id
AND entity_type = ara.object_type
AND organization_id = p_org_id
AND entity_id = p_certification_id
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type
));
SELECT count(1)
FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb
WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
AND ara.pk1 = p_certification_id
AND ara.pk2 = p_org_id
AND ara.risk_rev_id = arb.risk_rev_id
AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
AND ara.pk3 IN (SELECT DISTINCT process_id
FROM amw_execution_scope
START WITH process_id = p_process_id
AND entity_type = ara.object_type
AND organization_id = p_org_id
AND entity_id = p_certification_id
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type
)
AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = ara.pk2 --org_id
AND aov.pk4_value = ara.pk3 --process_id
AND aov.pk1_value = ara.risk_id
AND aov.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1)
AND aov2.pk4_value = aov.pk4_value
AND aov2.pk3_value = aov.pk3_value
AND aov2.pk1_value = aov.pk1_value)
AND aov.audit_result_code IS NOT NULL
);
SELECT count(1)
FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb
WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
AND ara.pk1 = p_certification_id
AND ara.pk2 = p_org_id
AND ara.risk_rev_id = arb.risk_rev_id
AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
AND ara.pk3 IN (SELECT DISTINCT process_id
FROM amw_execution_scope
START WITH process_id = p_process_id
AND entity_type = ara.object_type
AND organization_id = p_org_id
AND entity_id = p_certification_id
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type
)
AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = ara.pk2 --org_id
AND aov.pk4_value = ara.pk3 --process_id
AND aov.pk1_value = ara.risk_id
AND aov.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1)
AND aov2.pk4_value = aov.pk4_value
AND aov2.pk3_value = aov.pk3_value
AND aov2.pk1_value = aov.pk1_value)
AND aov.audit_result_code <> 'EFFECTIVE'
);
SELECT count(distinct processorg.organization_id)
FROM amw_execution_scope processorg
WHERE processorg.process_id = p_process_id
AND processorg.organization_id IN
(SELECT object_id
FROM amw_entity_hierarchies
START WITH parent_object_id = p_org_id
AND object_type = 'ORG'
AND entity_id = p_certification_id
AND entity_type='BUSIPROC_CERTIFICATION'
CONNECT BY parent_object_id = PRIOR object_id
AND parent_object_type = PRIOR object_type
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type)
AND processorg.entity_id=p_certification_id
AND processorg.entity_type='BUSIPROC_CERTIFICATION';
SELECT count(1)
FROM (SELECT distinct procorg.organization_id, procorg.process_id
FROM amw_execution_scope scp,
amw_process_organization procorg
WHERE scp.process_org_rev_id = procorg.process_org_rev_id
AND procorg.standard_variation IN
(select process_rev_id
from amw_process
where process_id = p_process_id)
AND scp.organization_id IN
(SELECT object_id
FROM amw_entity_hierarchies
START WITH parent_object_id = p_org_id
AND object_type = 'ORG'
AND entity_id = p_certification_id
AND entity_type='BUSIPROC_CERTIFICATION'
CONNECT BY parent_object_id = PRIOR object_id
AND parent_object_type = PRIOR object_type
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type)
AND scp.entity_id=p_certification_id
AND scp.entity_type='BUSIPROC_CERTIFICATION');
SELECT count(distinct pk3_value)
FROM amw_opinions_log_v opinion
WHERE opinion.pk2_value = p_certification_id
AND opinion.pk1_value = p_process_id
AND opinion.opinion_type_code = 'CERTIFICATION'
AND opinion.object_name = 'AMW_ORG_PROCESS'
AND opinion.audit_result_code <> 'EFFECTIVE'
AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1)
AND aov2.pk2_value = opinion.pk2_value
AND aov2.pk3_value = opinion.pk3_value
AND aov2.pk1_value = opinion.pk1_value)
AND opinion.pk3_value IN
(SELECT object_id
FROM amw_entity_hierarchies
START WITH parent_object_id = p_org_id
AND object_type = 'ORG'
AND entity_id = p_certification_id
AND entity_type='BUSIPROC_CERTIFICATION'
CONNECT BY parent_object_id = PRIOR object_id
AND parent_object_type = PRIOR object_type
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type)
AND exists (select 'Y' from amw_execution_scope scope
where scope.entity_type='BUSIPROC_CERTIFICATION'
and scope.entity_id=p_certification_id
and scope.organization_id=opinion.pk3_value
and scope.process_id=opinion.pk1_value);
SELECT count(1)
FROM (SELECT distinct opinion.pk1_value, opinion.pk3_value
FROM amw_opinions_log_v opinion,
amw_execution_scope scp,
amw_process_organization procorg
WHERE opinion.pk2_value = p_certification_id
AND opinion.pk1_value = scp.process_id
AND opinion.pk3_value = scp.organization_id
AND scp.entity_type = 'BUSIPROC_CERTIFICATION'
AND scp.entity_id = p_certification_id
AND scp.process_org_rev_id = procorg.process_org_rev_id
AND procorg.standard_variation in
(select process_rev_id
from amw_process
where process_id = p_process_id)
AND scp.organization_id IN
(SELECT object_id
FROM amw_entity_hierarchies
START WITH parent_object_id = p_org_id
AND object_type = 'ORG'
AND entity_id = p_certification_id
AND entity_type='BUSIPROC_CERTIFICATION'
CONNECT BY parent_object_id = PRIOR object_id
AND parent_object_type = PRIOR object_type
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type)
AND opinion.opinion_type_code = 'CERTIFICATION'
AND opinion.object_name = 'AMW_ORG_PROCESS'
AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1)
AND aov2.pk2_value = opinion.pk2_value
AND aov2.pk3_value = opinion.pk3_value
AND aov2.pk1_value = opinion.pk1_value)
AND opinion.audit_result_code <> 'EFFECTIVE');
SELECT count(distinct pk3_value)
FROM amw_opinions_log_v opinion
WHERE opinion.pk2_value = p_certification_id
AND opinion.pk1_value = p_process_id
AND opinion.opinion_type_code = 'CERTIFICATION'
AND opinion.object_name = 'AMW_ORG_PROCESS'
AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1)
AND aov2.pk2_value = opinion.pk2_value
AND aov2.pk3_value = opinion.pk3_value
AND aov2.pk1_value = opinion.pk1_value)
AND opinion.pk3_value IN
(SELECT object_id
FROM amw_entity_hierarchies
START WITH parent_object_id = p_org_id
AND object_type = 'ORG'
AND entity_id = p_certification_id
AND entity_type='BUSIPROC_CERTIFICATION'
CONNECT BY parent_object_id = PRIOR object_id
AND parent_object_type = PRIOR object_type
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type)
AND exists (select 'Y' from amw_execution_scope scope
where scope.entity_type='BUSIPROC_CERTIFICATION'
and scope.entity_id=p_certification_id
and scope.organization_id=opinion.pk3_value
and scope.process_id=opinion.pk1_value);
SELECT count(1)
FROM (SELECT distinct opinion.pk1_value, opinion.pk3_value
FROM amw_opinions_log_v opinion,
amw_execution_scope scp,
amw_process_organization procorg
WHERE opinion.pk2_value = p_certification_id
AND opinion.pk1_value = scp.process_id
AND opinion.pk3_value = scp.organization_id
AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions_log aov2
WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
AND aov2.authored_date < nvl(p_to_date, sysdate+1)
AND aov2.pk2_value = opinion.pk2_value
AND aov2.pk3_value = opinion.pk3_value
AND aov2.pk1_value = opinion.pk1_value)
AND scp.entity_type = 'BUSIPROC_CERTIFICATION'
AND scp.entity_id = p_certification_id
AND scp.process_org_rev_id = procorg.process_org_rev_id
AND procorg.standard_variation in
(select process_rev_id
from amw_process
where process_id = p_process_id)
AND scp.organization_id IN
(SELECT object_id
FROM amw_entity_hierarchies
START WITH parent_object_id = p_org_id
AND object_type = 'ORG'
AND entity_id = p_certification_id
AND entity_type='BUSIPROC_CERTIFICATION'
CONNECT BY parent_object_id = PRIOR object_id
AND parent_object_type = PRIOR object_type
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type)
AND opinion.opinion_type_code = 'CERTIFICATION'
AND opinion.object_name = 'AMW_ORG_PROCESS');
SELECT count(distinct process_id)
FROM amw_execution_scope
START WITH parent_process_id = p_process_id
AND organization_id = p_org_id
AND entity_id = p_certification_id
AND entity_type = 'BUSIPROC_CERTIFICATION'
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type;
SELECT count(distinct process_id)
FROM amw_execution_scope amw_exec
WHERE EXISTS (SELECT opinion.opinion_id
FROM amw_opinions_v opinion
WHERE opinion.pk1_value = amw_exec.process_id
AND opinion.pk3_value = p_org_id
AND opinion.pk2_value = p_certification_id
AND opinion.opinion_type_code = 'CERTIFICATION'
AND opinion.object_name = 'AMW_ORG_PROCESS'
)
START WITH parent_process_id = p_process_id
AND organization_id = p_org_id
AND entity_id = p_certification_id
AND entity_type = 'BUSIPROC_CERTIFICATION'
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type;
SELECT count(distinct process_id)
FROM amw_execution_scope amw_exec
WHERE EXISTS (SELECT opinion.opinion_id
FROM amw_opinions_v opinion
WHERE opinion.pk1_value = amw_exec.process_id
AND opinion.pk3_value = p_org_id
AND opinion.pk2_value = p_certification_id
AND opinion.opinion_type_code = 'CERTIFICATION'
AND opinion.object_name = 'AMW_ORG_PROCESS'
AND opinion.audit_result_code <> 'EFFECTIVE'
)
START WITH parent_process_id = p_process_id
AND organization_id = p_org_id
AND entity_id = p_certification_id
AND entity_type = 'BUSIPROC_CERTIFICATION'
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
AND entity_type = PRIOR entity_type;