[Home] [Help]
763: FROM AMW_OPINIONS_V aov
764: WHERE aov.object_name = 'AMW_ORG_PROCESS'
765: AND aov.opinion_type_code = 'CERTIFICATION'
766: AND aov.pk3_value = hier.organization_id
767: AND aov.pk2_value in (select proc_cert_id from AMW_FIN_PROC_CERT_RELAN where fin_stmt_cert_id = p_certification_id)
768: AND aov.pk1_value = hier.child_process_id)
769: );
770:
771:
825: AND hier.up_down_ind = 'D'
826: AND aov.object_name = 'AMW_ORG_PROCESS'
827: AND aov.opinion_type_code = 'CERTIFICATION'
828: AND aov.pk3_value = hier.organization_id
829: AND aov.pk2_value in (select proc_cert_id from AMW_FIN_PROC_CERT_RELAN where fin_stmt_cert_id = p_certification_id)
830: AND aov.pk1_value = hier.child_process_id
831: AND aov.audit_result_code <> 'EFFECTIVE'
832: );
833:
887: FROM AMW_OPINIONS_V aov
888: WHERE aov.object_name = 'AMW_ORG_PROCESS'
889: AND aov.opinion_type_code = 'CERTIFICATION'
890: AND aov.pk3_value = hier.organization_id
891: AND aov.pk2_value in (select proc_cert_id from AMW_FIN_PROC_CERT_RELAN where fin_stmt_cert_id = p_certification_id)
892: AND aov.pk1_value = hier.child_process_id));
893:
894: CURSOR local_proc_with_issue IS
895: SELECT count(1)
946: AND hier.up_down_ind = 'D'
947: AND aov.object_name = 'AMW_ORG_PROCESS'
948: AND aov.opinion_type_code = 'CERTIFICATION'
949: AND aov.pk3_value = hier.organization_id
950: AND aov.pk2_value in (select proc_cert_id from AMW_FIN_PROC_CERT_RELAN where fin_stmt_cert_id = p_certification_id)
951: AND aov.pk1_value = hier.child_process_id
952: AND aov.audit_result_code <> 'EFFECTIVE');
953:
954: CURSOR global_proc_with_ineff_ctrl IS
1551: AND aov.opinion_type_code = 'CERTIFICATION'
1552: AND aov.pk3_value = p_organization_id
1553: -- AND aov.authored_date >= p_start_date
1554: -- AND aov.authored_date <= p_end_date
1555: AND aov.pk2_value in (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
1556: where fin_stmt_cert_id = p_certification_id)
1557: AND aov.pk1_value in (select distinct(orgrel.child_process_id)
1558: from AMW_PROCESS_ORG_RELATIONS orgrel
1559: START WITH orgrel.parent_process_id = p_process_id
1576: WHERE aov.object_name = 'AMW_ORG_PROCESS'
1577: AND aov.opinion_type_code = 'CERTIFICATION'
1578: AND aov.pk3_value <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
1579: AND aov.pk1_value = p_process_id
1580: AND aov.pk2_value in (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
1581: where fin_stmt_cert_id = p_certification_id);
1582: --AND aov.authored_date >= p_start_date
1583: --AND aov.authored_date <= p_end_date ;
1584:
1622: FROM AMW_OPINIONS_V aov
1623: WHERE aov.object_name = 'AMW_ORG_PROCESS'
1624: AND aov.opinion_type_code = 'CERTIFICATION'
1625: AND aov.pk3_value = p_organization_id
1626: AND aov.pk2_value in (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
1627: where fin_stmt_cert_id = p_certification_id)
1628: AND aov.pk1_value = p_process_id
1629: AND aov.authored_date = (select max(aov2.authored_date)
1630: from AMW_OPINIONS_V aov2
1631: where aov2.object_opinion_type_id = aov.object_opinion_type_id
1632: and aov2.pk3_value = aov.pk3_value
1633: and aov2.pk1_value = aov.pk1_value
1634: and aov2.pk2_value in
1635: (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
1636: where fin_stmt_cert_id = p_certification_id));
1637: -- and aov2.authored_date >= p_start_date
1638: -- and aov2.authored_date <= p_end_date) ;
1639:
2036: AND aov.pk3_value in (select distinct evalsum.organization_id
2037: from amw_fin_process_eval_sum evalsum
2038: where evalsum.fin_certification_id = p_cert_id
2039: and evalsum.process_id=p_process_id)
2040: AND aov.pk2_value in (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
2041: where fin_stmt_cert_id = p_cert_id);
2042:
2043: CURSOR total_org_processes(p_cert_id NUMBER,p_process_id NUMBER) IS
2044: select count(distinct evalsum.organization_id)
2065: --AND aov.pk3_value <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
2066: --AND aov.authored_date >= p_start_date
2067: --AND aov.authored_date <= p_end_date
2068: AND aov.pk2_value in (
2069: select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
2070: where fin_stmt_cert_id = p_cert_id)
2071: AND aov.pk1_value in (SELECT varproc.process_id
2072: FROM AMW_PROCESS varproc
2073: WHERE varproc.standard_variation = p_process_id
2394: AND po.organization_id = p_organization_id
2395: AND aov.object_name = 'AMW_ORG_PROCESS'
2396: AND aov.opinion_type_code = 'CERTIFICATION'
2397: AND aov.pk3_value = p_organization_id
2398: AND aov.pk2_value in (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
2399: where fin_stmt_cert_id = p_certification_id)
2400: AND aov.pk1_value = hier.parent_child_id
2401: AND aov.authored_date = (select max(aov2.authored_date)
2402: from AMW_OPINIONS_V aov2
2403: where aov2.object_opinion_type_id
2404: = aov.object_opinion_type_id
2405: and aov2.pk3_value = aov.pk3_value
2406: AND aov2.pk2_value in
2407: (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
2408: w where fin_stmt_cert_id = p_certification_id)
2409: and aov2.pk1_value = aov.pk1_value)
2410: AND aov.audit_result_code <> 'EFFECTIVE';
2411:
2462: AND po.organization_id = p_organization_id
2463: AND aov.object_name = 'AMW_ORG_PROCESS'
2464: AND aov.opinion_type_code = 'CERTIFICATION'
2465: AND aov.pk3_value = p_organization_id
2466: AND aov.pk2_value in (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
2467: where fin_stmt_cert_id = p_certification_id)
2468: AND aov.pk1_value = hier.parent_child_id
2469: AND aov.authored_date = (select max(aov2.authored_date)
2470: from AMW_OPINIONS_V aov2
2471: where aov2.object_opinion_type_id
2472: = aov.object_opinion_type_id
2473: and aov2.pk3_value = aov.pk3_value
2474: AND aov2.pk2_value in
2475: (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
2476: w where fin_stmt_cert_id = p_certification_id)
2477: and aov2.pk1_value = aov.pk1_value)
2478: AND aov.audit_result_code = 'EFFECTIVE';
2479:
4822: opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
4823: opiniontype.OPINION_TYPE_CODE = 'CERTIFICATION' AND
4824: fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
4825: fndobject.obj_name = 'AMW_ORG_PROCESS'
4826: and opinion.PK2_VALUE in (select PROC_CERT_ID from AMW_FIN_PROC_CERT_RELAN where FIN_STMT_CERT_ID = p_fin_cert_id)
4827: and (opinion.PK3_VALUE = orgprocess.ORGANIZATION_ID
4828: and opinion.PK1_VALUE = orgprocess.PROCESS_ID)
4829: and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
4830: in
4894: AMW_OPINIONS_V opinion2
4895: where
4896: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID
4897: and opinion2.PK2_VALUE in
4898: (select PROC_CERT_ID from AMW_FIN_PROC_CERT_RELAN where FIN_STMT_CERT_ID = p_fin_cert_id) and
4899: opinion2.PK1_VALUE = opinion.PK1_VALUE and
4900: opinion2.PK3_VALUE = opinion.PK3_VALUE
4901: ) AND
4902: opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
4905: fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
4906: fndobject.obj_name = 'AMW_ORG_PROCESS' and
4907: opinion.audit_result_CODE <> 'EFFECTIVE')
4908: and opinion.PK2_VALUE in
4909: (select PROC_CERT_ID from AMW_FIN_PROC_CERT_RELAN where FIN_STMT_CERT_ID = p_fin_cert_id)
4910: and (opinion.PK3_VALUE = orgprocess.ORGANIZATION_ID
4911: and opinion.PK1_VALUE = orgprocess.PROCESS_ID)
4912: and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
4913: in
6049: opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
6050: opiniontype.OPINION_TYPE_CODE = 'CERTIFICATION' AND
6051: fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
6052: fndobject.obj_name = 'AMW_ORG_PROCESS'
6053: and opinion.PK2_VALUE in (select PROC_CERT_ID from AMW_FIN_PROC_CERT_RELAN where FIN_STMT_CERT_ID = p_fin_cert_id)
6054: and (opinion.PK3_VALUE = orgprocess.ORGANIZATION_ID
6055: and opinion.PK1_VALUE = orgprocess.PROCESS_ID)
6056: and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
6057: in
6309: AMW_OPINIONS_V opinion2
6310: where
6311: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID
6312: and opinion2.PK2_VALUE in
6313: (select PROC_CERT_ID from AMW_FIN_PROC_CERT_RELAN where FIN_STMT_CERT_ID = p_fin_cert_id) and
6314: opinion2.PK1_VALUE = opinion.PK1_VALUE and
6315: opinion2.PK3_VALUE = opinion.PK3_VALUE ) AND
6316: opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
6317: opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
6318: opiniontype.OPINION_TYPE_CODE = 'CERTIFICATION' AND
6319: fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
6320: fndobject.obj_name = 'AMW_ORG_PROCESS' and
6321: opinion.audit_result_CODE <> 'EFFECTIVE')
6322: and opinion.PK2_VALUE in (select PROC_CERT_ID from AMW_FIN_PROC_CERT_RELAN where FIN_STMT_CERT_ID = p_fin_cert_id)
6323: and (opinion.PK3_VALUE = orgprocess.ORGANIZATION_ID
6324: and opinion.PK1_VALUE = orgprocess.PROCESS_ID)
6325: and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
6326: in