[Home] [Help]
10479: --need check opinion framework doc
10480: --in ap association table, if type = 'CTRL_FINCERT', pk1=certification_id, pk2=organization_id, pk3=process_id, pk4=control_id, pk5=opinion_id
10481: CURSOR last_evaluation(l_audit_procedure_id number, l_organization_id number, l_control_id number) IS
10482: SELECT distinct aov.opinion_id
10483: FROM AMW_OPINION_M_V aov
10484: WHERE
10485: aov.object_name = 'AMW_ORG_AP_CONTROL'
10486: AND aov.opinion_type_code = 'EVALUATION'
10487: AND aov.opinion_component_code = 'OVERALL'
10649:
10650: l_stmt := 'SELECT COUNT(1) FROM
10651: (Select distinct fin.PROCESS_ID, fin.ORGANIZATION_ID
10652: FROM
10653: AMW_OPINION_M_V aov,
10654: amw_fin_cert_scope fin
10655: WHERE aov.OPINION_TYPE_CODE = ''EVALUATION''
10656: and aov.object_name = ''AMW_ORG_PROCESS''
10657: and aov.opinion_component_code = ''OVERALL''
10711:
10712: l_stmt := 'select count(1) from (
10713: select distinct fin.ORGANIZATION_ID
10714: FROM
10715: AMW_OPINION_M_V aov,
10716: amw_fin_cert_scope fin
10717: WHERE aov.OPINION_TYPE_CODE = ''EVALUATION''
10718: and aov.object_name = ''AMW_ORGANIZATION''
10719: and aov.opinion_component_code = ''OVERALL''
10772: /*********** replace with the following query that uses opinion_log_id directly
10773: l_stmt := 'select count(1) from (
10774: select distinct fin.risk_id ,fin.organization_id, fin.Process_ID
10775: FROM
10776: AMW_OPINION_M_V aov,
10777: amw_fin_item_acc_risk fin
10778: WHERE
10779: aov.OPINION_TYPE_CODE = ''EVALUATION''
10780: and aov.object_name = ''AMW_ORG_PROCESS_RISK''
10790:
10791: l_stmt := 'select count(1) from (
10792: select distinct fin.risk_id ,fin.organization_id, fin.Process_ID
10793: FROM
10794: amw_opinion_m_v aov,
10795: amw_opinions_log aol,
10796: amw_fin_item_acc_risk fin
10797: WHERE
10798: aov.OPINION_TYPE_CODE = ''EVALUATION''
10857: /********replace to use opinion_log_id in amw_fin_item_acc_ctrl
10858: l_stmt := 'select count(1) from(
10859: select distinct fin.control_id, fin.organization_id
10860: FROM
10861: AMW_OPINION_M_V aov,
10862: amw_fin_item_acc_ctrl fin
10863: WHERE aov.OPINION_TYPE_CODE = ''EVALUATION''
10864: AND aov.object_name = ''AMW_ORG_CONTROL''
10865: and aov.opinion_component_code = ''OVERALL''
10873:
10874: l_stmt := 'select count(1) from(
10875: select distinct fin.control_id, fin.organization_id
10876: FROM
10877: amw_opinion_m_v aov,
10878: amw_opinions_log aol,
10879: amw_fin_item_acc_ctrl fin
10880: WHERE aov.OPINION_TYPE_CODE = ''EVALUATION''
10881: and aov.object_name = ''AMW_ORG_CONTROL''