eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AMW_FINSTMT_CERT_PVT dependencies on AMW_OPINIONS_V

Line 73: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype

69: -- Fetch the certification result for each next level subprocess
70:
71:
72: select opinion.audit_result INTO l_audit_result
73: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
74: WHERE
75:
76: (opinion.AUTHORED_DATE in (Select MAX(opinion.AUTHORED_DATE)
77: from AMW_OPINIONS_V opinion

Line 77: from AMW_OPINIONS_V opinion

73: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
74: WHERE
75:
76: (opinion.AUTHORED_DATE in (Select MAX(opinion.AUTHORED_DATE)
77: from AMW_OPINIONS_V opinion
78: Group By PK1_VALUE)) AND
79:
80: opinion.PK3_VALUE = p_org_id AND
81: opinion.PK2_VALUE = p_certification_id AND

Line 139: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype

135:
136: Update AMW_PROC_CERT_EVAL_SUM
137: SET (CERTIFICATION_OPINION_ID) =
138: (select opinion.OPINION_ID
139: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
140: WHERE
141:
142: (opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)
143: from AMW_OPINIONS_V

Line 142: (opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)

138: (select opinion.OPINION_ID
139: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
140: WHERE
141:
142: (opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)
143: from AMW_OPINIONS_V
144: Group By PK1_VALUE)) AND
145: opinion.PK3_VALUE = p_org_id AND
146: opinion.PK2_VALUE = p_certification_id AND

Line 143: from AMW_OPINIONS_V

139: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
140: WHERE
141:
142: (opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)
143: from AMW_OPINIONS_V
144: Group By PK1_VALUE)) AND
145: opinion.PK3_VALUE = p_org_id AND
146: opinion.PK2_VALUE = p_certification_id AND
147: opinion.PK1_VALUE = p_process_id AND

Line 204: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype

200: Update AMW_PROC_CERT_EVAL_SUM
201: SET ORG_PROCESS_CERT =
202: (
203: select distinct count(*)
204: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
205: WHERE
206:
207: (opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)
208: from AMW_OPINIONS_V

Line 207: (opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)

203: select distinct count(*)
204: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
205: WHERE
206:
207: (opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)
208: from AMW_OPINIONS_V
209: Group By PK1_VALUE)) AND
210: opinion.PK2_VALUE = p_certification_id AND
211: opinion.PK1_VALUE = p_process_id AND

Line 208: from AMW_OPINIONS_V

204: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
205: WHERE
206:
207: (opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)
208: from AMW_OPINIONS_V
209: Group By PK1_VALUE)) AND
210: opinion.PK2_VALUE = p_certification_id AND
211: opinion.PK1_VALUE = p_process_id AND
212: opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND

Line 256: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype

252: Update AMW_PROC_CERT_EVAL_SUM
253: SET (EVALUATION_OPINION_ID) =
254: (
255: select opinion.OPINION_ID
256: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
257: WHERE
258:
259: (opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)
260: from AMW_OPINIONS_V

Line 259: (opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)

255: select opinion.OPINION_ID
256: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
257: WHERE
258:
259: (opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)
260: from AMW_OPINIONS_V
261: Group By PK1_VALUE)) AND
262: opinion.PK1_VALUE = p_process_id AND
263: opinion.PK3_VALUE = p_org_id AND

Line 260: from AMW_OPINIONS_V

256: FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
257: WHERE
258:
259: (opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)
260: from AMW_OPINIONS_V
261: Group By PK1_VALUE)) AND
262: opinion.PK1_VALUE = p_process_id AND
263: opinion.PK3_VALUE = p_org_id AND
264: opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND

Line 308: amw_opinions_v opinionstable,

304: amw_wf_org_hierarchy_main_v processtable,
305: fnd_objects fo,
306: amw_opinion_types_tl optypes,
307: AMW_OBJECT_OPINION_TYPES objoptypes,
308: amw_opinions_v opinionstable,
309: pa_project_lists_v pap,
310: AMW_PROCESS_ORGANIZATION procorg
311:
312: where assoctable.object_type = 'PROCESS_ORG'

Line 328: from amw_opinions_v a

324: objoptypes.object_opinion_type_id
325:
326: and opinionstable.last_update_date in
327: ( select max ( a.last_update_date )
328: from amw_opinions_v a
329: where a.pk1_value = opinionstable.pk1_value
330: and a.pk3_value = opinionstable.pk3_value
331: and a.pk4_value = opinionstable.pk4_value
332: and a.object_opinion_type_id =

Line 389: amw_opinions_v opinionstable

385: fnd_objects fo,
386: amw_opinion_types_tl optypes,
387: AMW_OBJECT_OPINION_TYPES objoptypes,
388: pa_project_lists_v pap,
389: amw_opinions_v opinionstable
390:
391: where procorg.process_id = processtable.process_id
392: and procorg.process_id = p_process_id
393: and procorg.organization_id = orgtable.organization_id

Line 410: from amw_opinions_v a

406: objoptypes.object_opinion_type_id
407:
408: and opinionstable.last_update_date in
409: ( select max ( a.last_update_date )
410: from amw_opinions_v a
411: where a.pk1_value = opinionstable.pk1_value
412: and a.pk3_value = opinionstable.pk3_value
413: and a.object_opinion_type_id =
414: opinionstable.object_opinion_type_id

Line 763: FROM AMW_OPINIONS_V aov

759: AND apo.process_id = hier.process_id
760: AND apo.organization_id = hier.organization_id
761: AND hier.up_down_ind = 'D'
762: AND not exists (SELECT 'Y'
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)

Line 778: AMW_OPINIONS_V aov,

774: FROM (
775: SELECT distinct hier.organization_id, hier.child_process_id
776: FROM AMW_PROCESS_ORGANIZATION apo,
777: AMW_ACCT_ASSOCIATIONS acct_assoc,
778: AMW_OPINIONS_V aov,
779: (select process_id, organization_id, parent_child_id child_process_id, up_down_ind
780: from Amw_Org_Hierarchy_Denorm
781: union
782: select process_id, organization_id, process_id child_process_id, 'D' up_down_ind

Line 887: FROM AMW_OPINIONS_V aov

883: AND hier.process_id = apo.process_id
884: AND hier.organization_id = apo.organization_id
885: AND hier.up_down_ind = 'D'
886: AND not exists (SELECT 'Y'
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)

Line 899: AMW_OPINIONS_V aov,

895: SELECT count(1)
896: FROM (SELECT distinct hier.organization_id, hier.child_process_id
897: FROM AMW_PROCESS_ORGANIZATION apo,
898: AMW_ACCT_ASSOCIATIONS acct_assoc,
899: AMW_OPINIONS_V aov,
900: (select process_id, organization_id, parent_child_id child_process_id, up_down_ind
901: from Amw_Org_Hierarchy_Denorm
902: union
903: select process_id, organization_id, process_id child_process_id, 'D' up_down_ind

Line 958: AMW_OPINIONS_V aov,

954: CURSOR global_proc_with_ineff_ctrl IS
955: SELECT count(distinct hier.child_process_id)
956: FROM AMW_PROCESS_ORGANIZATION apo,
957: AMW_ACCT_ASSOCIATIONS acct_assoc,
958: AMW_OPINIONS_V aov,
959: (select process_id, organization_id, parent_child_id child_process_id, up_down_ind
960: from Amw_Org_Hierarchy_Denorm
961: union
962: select process_id, organization_id, process_id child_process_id, 'D' up_down_ind

Line 1011: from AMW_OPINIONS_V aov2

1007: AND aov.opinion_type_code = 'EVALUATION'
1008: AND aov.pk3_value = hier.organization_id
1009: AND aov.pk1_value = hier.child_process_id
1010: AND aov.authored_date = (select max(aov2.authored_date)
1011: from AMW_OPINIONS_V aov2
1012: where aov2.object_opinion_type_id = aov.object_opinion_type_id
1013: and aov2.pk3_value = aov.pk3_value
1014: and aov2.pk1_value = aov.pk1_value)
1015: and aov.audit_result_code <> 'EFFECTIVE';

Line 1022: AMW_OPINIONS_V aov,

1018: SELECT count(1)
1019: FROM (SELECT distinct hier.organization_id, hier.child_process_id
1020: FROM AMW_PROCESS_ORGANIZATION apo,
1021: AMW_ACCT_ASSOCIATIONS acct_assoc,
1022: AMW_OPINIONS_V aov,
1023: (select process_id, organization_id, parent_child_id child_process_id, up_down_ind
1024: from Amw_Org_Hierarchy_Denorm
1025: union
1026: select process_id, organization_id, process_id child_process_id, 'D' up_down_ind

Line 1076: from AMW_OPINIONS_V aov2

1072: AND aov.opinion_type_code = 'EVALUATION'
1073: AND aov.pk3_value = hier.organization_id
1074: AND aov.pk1_value = hier.child_process_id
1075: AND aov.authored_date = (select max(aov2.authored_date)
1076: from AMW_OPINIONS_V aov2
1077: where aov2.object_opinion_type_id = aov.object_opinion_type_id
1078: and aov2.pk3_value = aov.pk3_value
1079: AND aov.audit_result_code <> 'EFFECTIVE'
1080: and aov2.pk1_value = aov.pk1_value));

Line 1089: AMW_OPINIONS_V aov,

1085: FROM (SELECT distinct p_org.organization_id, p_org.process_id, ara.risk_id
1086: FROM AMW_PROCESS_ORGANIZATION apo,
1087: AMW_ACCT_ASSOCIATIONS acct_assoc,
1088: AMW_RISK_ASSOCIATIONS ara,
1089: AMW_OPINIONS_V aov,
1090: AMW_PROCESS_ORGANIZATION p_org,
1091: (select process_id, organization_id, parent_child_id child_process_id, up_down_ind
1092: from Amw_Org_Hierarchy_Denorm
1093: union

Line 1149: from AMW_OPINIONS_V aov2

1145: AND aov.pk4_value = p_org.process_id
1146: AND aov.pk1_value = ara.risk_id
1147: AND aov.authored_date =
1148: (select max(aov2.authored_date)
1149: from AMW_OPINIONS_V aov2
1150: where aov2.object_opinion_type_id = aov.object_opinion_type_id
1151: and aov2.pk4_value = aov.pk4_value
1152: and aov2.pk3_value = aov.pk3_value
1153: and aov2.pk1_value = aov.pk1_value)

Line 1162: AMW_OPINIONS_V aov,

1158: FROM (SELECT distinct p_org.organization_id, aca.control_id
1159: FROM AMW_PROCESS_ORGANIZATION apo,
1160: AMW_ACCT_ASSOCIATIONS acct_assoc,
1161: AMW_RISK_ASSOCIATIONS ara, AMW_CONTROL_ASSOCIATIONS aca,
1162: AMW_OPINIONS_V aov,
1163: AMW_PROCESS_ORGANIZATION p_org,
1164: (select process_id, organization_id, parent_child_id child_process_id, up_down_ind
1165: from Amw_Org_Hierarchy_Denorm
1166: union

Line 1223: from AMW_OPINIONS_V aov2

1219: AND aov.pk3_value = p_org.organization_id
1220: AND aov.pk1_value = aca.control_id
1221: AND aov.authored_date =
1222: (select max(aov2.authored_date)
1223: from AMW_OPINIONS_V aov2
1224: where aov2.object_opinion_type_id = aov.object_opinion_type_id
1225: and aov2.pk3_value = aov.pk3_value
1226: and aov2.pk1_value = aov.pk1_value)
1227: AND aov.audit_result_code <> 'EFFECTIVE');

Line 1285: FROM AMW_OPINIONS_V aov

1281: AND hier.up_down_ind = 'D'
1282: AND p_org.process_id = hier.child_process_id
1283: AND p_org.organization_id = hier.organization_id
1284: AND not exists (SELECT 'Y'
1285: FROM AMW_OPINIONS_V aov
1286: WHERE aov.object_name = 'AMW_ORG_PROCESS'
1287: AND aov.opinion_type_code = 'CERTIFICATION'
1288: AND aov.pk3_value = p_org.organization_id
1289: AND aov.pk2_value = p_certification_id

Line 1549: FROM AMW_OPINIONS_V aov

1545: IS
1546:
1547: CURSOR sub_processes_certified IS
1548: SELECT count(distinct aov.pk1_value)
1549: FROM AMW_OPINIONS_V aov
1550: WHERE aov.object_name = 'AMW_ORG_PROCESS'
1551: AND aov.opinion_type_code = 'CERTIFICATION'
1552: AND aov.pk3_value = p_organization_id
1553: -- AND aov.authored_date >= p_start_date

Line 1575: FROM AMW_OPINIONS_V aov

1571:
1572: /*
1573: CURSOR org_processes_certified IS
1574: SELECT count(distinct aov.pk3_value)
1575: FROM AMW_OPINIONS_V aov
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

Line 1622: FROM AMW_OPINIONS_V aov

1618: */
1619:
1620: CURSOR certification_result IS
1621: SELECT aov.opinion_id
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

Line 1630: from AMW_OPINIONS_V aov2

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

Line 1642: FROM AMW_OPINIONS_V aov

1638: -- and aov2.authored_date <= p_end_date) ;
1639:
1640: CURSOR last_evaluation IS
1641: SELECT aov.opinion_id
1642: FROM AMW_OPINIONS_V aov
1643: WHERE aov.object_name = 'AMW_ORG_PROCESS'
1644: AND aov.opinion_type_code = 'EVALUATION'
1645: AND aov.pk3_value = p_organization_id
1646: AND aov.pk1_value = p_process_id

Line 1648: from AMW_OPINIONS_V aov2

1644: AND aov.opinion_type_code = 'EVALUATION'
1645: AND aov.pk3_value = p_organization_id
1646: AND aov.pk1_value = p_process_id
1647: AND aov.authored_date = (select max(aov2.authored_date)
1648: from AMW_OPINIONS_V aov2
1649: where aov2.object_opinion_type_id = aov.object_opinion_type_id
1650: and aov2.pk3_value = aov.pk3_value
1651: and aov2.pk1_value = aov.pk1_value);
1652:

Line 1657: AMW_OPINIONS_V aov

1653: CURSOR unmitigated_risks IS
1654: SELECT count(1)
1655: FROM AMW_PROCESS_ORGANIZATION apo,
1656: AMW_RISK_ASSOCIATIONS ara,
1657: AMW_OPINIONS_V aov
1658: WHERE apo.organization_id = p_organization_id
1659: AND apo.process_id in ( select distinct(orgrel.child_process_id)
1660: from AMW_PROCESS_ORG_RELATIONS orgrel
1661: START WITH orgrel.child_process_id = p_process_id

Line 1673: from AMW_OPINIONS_V aov2

1669: AND aov.pk3_value = apo.organization_id
1670: AND aov.pk4_value = apo.process_id
1671: AND aov.pk1_value = ara.risk_id
1672: AND aov.authored_date = (select max(aov2.authored_date)
1673: from AMW_OPINIONS_V aov2
1674: where aov2.object_opinion_type_id = aov.object_opinion_type_id
1675: and aov2.pk4_value = aov.pk4_value
1676: and aov2.pk3_value = aov.pk3_value
1677: and aov2.pk1_value = aov.pk1_value)

Line 1690: AMW_OPINIONS_V aov

1686: SELECT count(distinct aca.control_id)
1687: FROM AMW_PROCESS_ORGANIZATION apo,
1688: AMW_RISK_ASSOCIATIONS ara,
1689: AMW_CONTROL_ASSOCIATIONS aca,
1690: AMW_OPINIONS_V aov
1691: WHERE apo.organization_id = p_organization_id
1692: AND apo.process_id in ( select distinct(orgrel.child_process_id)
1693: from AMW_PROCESS_ORG_RELATIONS orgrel
1694: START WITH orgrel.child_process_id = p_process_id

Line 1707: from AMW_OPINIONS_V aov2

1703: AND aov.opinion_type_code = 'EVALUATION'
1704: AND aov.pk3_value = apo.organization_id
1705: AND aov.pk1_value = aca.control_id
1706: AND aov.authored_date = (select max(aov2.authored_date)
1707: from AMW_OPINIONS_V aov2
1708: where aov2.object_opinion_type_id = aov.object_opinion_type_id
1709: and aov2.pk3_value = aov.pk3_value
1710: and aov2.pk1_value = aov.pk1_value)
1711: -- commented by qliu

Line 2031: FROM AMW_OPINIONS_V aov

2027:
2028: -- org process certified
2029: CURSOR org_processes_certified(p_cert_id NUMBER,p_process_id NUMBER) IS
2030: SELECT count(distinct aov.pk3_value)
2031: FROM AMW_OPINIONS_V aov
2032: WHERE aov.object_name = 'AMW_ORG_PROCESS'
2033: AND aov.opinion_type_code = 'CERTIFICATION'
2034: AND aov.pk3_value <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
2035: AND aov.pk1_value = p_process_id

Line 2062: FROM AMW_OPINIONS_V aov

2058:
2059: CURSOR org_var_processes_certified(p_cert_id NUMBER,p_start_date DATE,p_end_date DATE,p_process_id NUMBER) IS
2060: select count(1) from
2061: (SELECT distinct aov.pk3_value,aov.pk1_value
2062: FROM AMW_OPINIONS_V aov
2063: WHERE aov.object_name = 'AMW_ORG_PROCESS'
2064: AND aov.opinion_type_code = 'CERTIFICATION'
2065: --AND aov.pk3_value <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
2066: --AND aov.authored_date >= p_start_date

Line 2236: FROM AMW_OPINIONS_V aov

2232: )
2233: IS
2234: CURSOR last_evaluation IS
2235: SELECT aov.opinion_id
2236: FROM AMW_OPINIONS_V aov
2237: WHERE aov.object_name = 'AMW_ORGANIZATION'
2238: AND aov.opinion_type_code = 'EVALUATION'
2239: AND aov.pk1_value = p_organization_id
2240: AND aov.authored_date = (select max(aov2.authored_date)

Line 2241: from AMW_OPINIONS_V aov2

2237: WHERE aov.object_name = 'AMW_ORGANIZATION'
2238: AND aov.opinion_type_code = 'EVALUATION'
2239: AND aov.pk1_value = p_organization_id
2240: AND aov.authored_date = (select max(aov2.authored_date)
2241: from AMW_OPINIONS_V aov2
2242: where aov2.object_opinion_type_id = aov.object_opinion_type_id
2243: and aov2.pk1_value = aov.pk1_value);
2244:
2245: /*

Line 2286: FROM AMW_OPINIONS_V aov

2282: AND fika.financial_statement_id = cert.financial_statement_id
2283: AND cert.certification_id = p_certification_id
2284: AND apo.organization_id = p_organization_id
2285: AND not exists (SELECT 'Y'
2286: FROM AMW_OPINIONS_V aov
2287: WHERE aov.object_name = 'AMW_ORG_PROCESS'
2288: AND aov.opinion_type_code = 'CERTIFICATION'
2289: AND aov.pk3_value = p_organization_id
2290: AND aov.pk1_value = apo.process_id

Line 2353: AMW_OPINIONS_V aov

2349: (select process_id, organization_id, parent_child_id, up_down_ind
2350: from Amw_Org_Hierarchy_Denorm hier
2351: union select process_id, organization_id, process_id, 'D'
2352: from amw_process_organization) hier,
2353: AMW_OPINIONS_V aov
2354: WHERE aa.object_type = 'PROCESS_ORG'
2355: AND po.process_organization_id = aa.pk1
2356: AND hier.organization_id = po.organization_id
2357: AND hier.process_id = po.process_id

Line 2402: from AMW_OPINIONS_V aov2

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

Line 2421: AMW_OPINIONS_V aov

2417: (select process_id, organization_id, parent_child_id, up_down_ind
2418: from Amw_Org_Hierarchy_Denorm hier
2419: union select process_id, organization_id, process_id, 'D'
2420: from amw_process_organization) hier,
2421: AMW_OPINIONS_V aov
2422: WHERE aa.object_type = 'PROCESS_ORG'
2423: AND po.process_organization_id = aa.pk1
2424: AND hier.organization_id = po.organization_id
2425: AND hier.process_id = po.process_id

Line 2470: from AMW_OPINIONS_V aov2

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

Line 2488: AMW_OPINIONS_V aov

2484: (select process_id, organization_id, parent_child_id, up_down_ind
2485: from Amw_Org_Hierarchy_Denorm hier
2486: union select process_id, organization_id, process_id, 'D'
2487: from amw_process_organization) hier,
2488: AMW_OPINIONS_V aov
2489: WHERE aa.object_type = 'PROCESS_ORG'
2490: AND po.process_organization_id = aa.pk1
2491: AND hier.organization_id = po.organization_id
2492: AND hier.process_id = po.process_id

Line 2536: from AMW_OPINIONS_V aov2

2532: AND aov.pk3_value = hier.organization_id
2533: AND aov.pk1_value = hier.parent_child_id
2534: AND aov.authored_date =
2535: (select max(aov2.authored_date)
2536: from AMW_OPINIONS_V aov2
2537: where aov2.object_opinion_type_id = aov.object_opinion_type_id
2538: and aov2.pk3_value = aov.pk3_value
2539: and aov2.pk1_value = aov.pk1_value)
2540: AND aov.audit_result_code <> 'EFFECTIVE';

Line 2552: AMW_OPINIONS_V aov

2548: union select process_id, organization_id, process_id, 'D'
2549: from amw_process_organization) hier,
2550: AMW_PROCESS_ORGANIZATION po2,
2551: AMW_RISK_ASSOCIATIONS ara,
2552: AMW_OPINIONS_V aov
2553: WHERE aa.object_type = 'PROCESS_ORG'
2554: AND po.process_organization_id = aa.pk1
2555: AND hier.organization_id = po.organization_id
2556: AND hier.process_id = po.process_id

Line 2604: from AMW_OPINIONS_V aov2

2600: AND aov.pk3_value = po2.organization_id
2601: AND aov.pk4_value = po2.process_id
2602: AND aov.pk1_value = ara.risk_id
2603: AND aov.authored_date = (select max(aov2.authored_date)
2604: from AMW_OPINIONS_V aov2
2605: where aov2.object_opinion_type_id = aov.object_opinion_type_id
2606: and aov2.pk4_value = aov.pk4_value
2607: and aov2.pk3_value = aov.pk3_value
2608: and aov2.pk1_value = aov.pk1_value)

Line 2678: AMW_OPINIONS_V aov

2674: from amw_process_organization) hier,
2675: AMW_PROCESS_ORGANIZATION po2,
2676: AMW_RISK_ASSOCIATIONS ara,
2677: AMW_CONTROL_ASSOCIATIONS aca ,
2678: AMW_OPINIONS_V aov
2679: WHERE aa.object_type = 'PROCESS_ORG'
2680: AND po.process_organization_id = aa.pk1
2681: AND hier.organization_id = po.organization_id
2682: AND hier.process_id = po.process_id

Line 2731: from AMW_OPINIONS_V aov2

2727: AND aov.opinion_type_code = 'EVALUATION'
2728: AND aov.pk3_value = po2.organization_id
2729: AND aov.pk1_value = aca.control_id
2730: AND aov.authored_date = (select max(aov2.authored_date)
2731: from AMW_OPINIONS_V aov2
2732: where aov2.object_opinion_type_id = aov.object_opinion_type_id
2733: and aov2.pk3_value = aov.pk3_value
2734: and aov2.pk1_value = aov.pk1_value)
2735: AND aov.audit_result_code <> 'EFFECTIVE';

Line 4815: AMW_OPINIONS_V opinion,

4811: from
4812: (
4813: Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
4814: FROM
4815: AMW_OPINIONS_V opinion,
4816: AMW_OPINION_TYPES_B opiniontype,
4817: FND_OBJECTS fndobject,
4818: AMW_OBJECT_OPINION_TYPES objectopiniontype,
4819: amw_process_org_basicinfo_v orgprocess

Line 4884: AMW_OPINIONS_V opinion,

4880: from
4881: (
4882: Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
4883: FROM
4884: AMW_OPINIONS_V opinion,
4885: AMW_OPINION_TYPES_B opiniontype,
4886: FND_OBJECTS fndobject,
4887: AMW_OBJECT_OPINION_TYPES objectopiniontype,
4888: amw_process_org_basicinfo_v orgprocess

Line 4894: AMW_OPINIONS_V opinion2

4890: (opinion.AUTHORED_DATE in
4891: (Select
4892: MAX(opinion2.AUTHORED_DATE)
4893: from
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

Line 4965: AMW_OPINIONS_V opinion,

4961: count(1) into P_org_with_ineffective_ctrls
4962: from (
4963: select distinct orgprocess.ORGANIZATION_ID
4964: FROM
4965: AMW_OPINIONS_V opinion,
4966: AMW_OPINION_TYPES_B opiniontype,
4967: FND_OBJECTS fndobject,
4968: AMW_OBJECT_OPINION_TYPES objectopiniontype,
4969: amw_process_org_basicinfo_v orgprocess

Line 4975: AMW_OPINIONS_V opinion2

4971: (opinion.AUTHORED_DATE in
4972: (Select
4973: MAX(opinion2.AUTHORED_DATE)
4974: from
4975: AMW_OPINIONS_V opinion2
4976: where
4977: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
4978: opinion2.PK1_VALUE = opinion.PK1_VALUE
4979: --- Commented chekcing whether the opinion date falls within the period as

Line 5045: AMW_OPINIONS_V opinion,

5041: from
5042: (
5043: select distinct orgprocess.ORGANIZATION_ID
5044: FROM
5045: AMW_OPINIONS_V opinion,
5046: AMW_OPINION_TYPES_B opiniontype,
5047: FND_OBJECTS fndobject,
5048: AMW_OBJECT_OPINION_TYPES objectopiniontype,
5049: amw_process_org_basicinfo_v orgprocess

Line 5055: AMW_OPINIONS_V opinion2

5051: (opinion.AUTHORED_DATE in
5052: (Select
5053: MAX(opinion2.AUTHORED_DATE)
5054: from
5055: AMW_OPINIONS_V opinion2
5056: where
5057: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
5058: opinion2.PK1_VALUE = opinion.PK1_VALUE AND
5059: opinion2.AUTHORED_DATE >= p_start_date and

Line 5125: AMW_OPINIONS_V opinion,

5121: from
5122: (
5123: select distinct orgprocess.ORGANIZATION_ID
5124: FROM
5125: AMW_OPINIONS_V opinion,
5126: AMW_OPINION_TYPES_B opiniontype,
5127: FND_OBJECTS fndobject,
5128: AMW_OBJECT_OPINION_TYPES objectopiniontype,
5129: amw_process_org_basicinfo_v orgprocess

Line 5135: AMW_OPINIONS_V opinion2

5131: (opinion.AUTHORED_DATE in
5132: (Select
5133: MAX(opinion2.AUTHORED_DATE)
5134: from
5135: AMW_OPINIONS_V opinion2
5136: where
5137: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
5138: opinion2.PK1_VALUE = opinion.PK1_VALUE AND
5139: opinion2.AUTHORED_DATE >= p_start_date and

Line 5241: AMW_OPINIONS_V opinion,

5237: from
5238: (
5239: select distinct orgprocess.ORGANIZATION_ID
5240: FROM
5241: AMW_OPINIONS_V opinion,
5242: AMW_OPINION_TYPES_B opiniontype,
5243: FND_OBJECTS fndobject,
5244: AMW_OBJECT_OPINION_TYPES objectopiniontype,
5245: amw_process_org_basicinfo_v orgprocess

Line 5251: AMW_OPINIONS_V opinion2

5247: (opinion.AUTHORED_DATE in
5248: (Select
5249: MAX(opinion2.AUTHORED_DATE)
5250: from
5251: AMW_OPINIONS_V opinion2
5252: where
5253: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
5254: opinion2.PK1_VALUE = opinion.PK1_VALUE AND
5255: ----- **** opinion2.AUTHORED_DATE >= p_start_date and

Line 5321: AMW_OPINIONS_V opinion,

5317: count(1) into P_proc_with_ineffective_ctrls
5318: from (
5319: Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
5320: FROM
5321: AMW_OPINIONS_V opinion,
5322: AMW_OPINION_TYPES_B opiniontype,
5323: FND_OBJECTS fndobject,
5324: AMW_OBJECT_OPINION_TYPES objectopiniontype,
5325: amw_process_org_basicinfo_v orgprocess

Line 5331: AMW_OPINIONS_V opinion2

5327: (opinion.AUTHORED_DATE in
5328: (Select
5329: MAX(opinion2.AUTHORED_DATE)
5330: from
5331: AMW_OPINIONS_V opinion2
5332: where
5333: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID and
5334: opinion2.PK1_VALUE = opinion.PK1_VALUE and
5335: opinion2.PK3_VALUE = opinion.PK3_VALUE

Line 5404: AMW_OPINIONS_V opinion,

5400: count(1) into P_PROCS_EVALUATED from
5401: (
5402: Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
5403: FROM
5404: AMW_OPINIONS_V opinion,
5405: AMW_OPINION_TYPES_B opiniontype,
5406: FND_OBJECTS fndobject,
5407: AMW_OBJECT_OPINION_TYPES objectopiniontype,
5408: amw_process_org_basicinfo_v orgprocess

Line 5414: AMW_OPINIONS_V opinion2

5410: (opinion.AUTHORED_DATE in
5411: (Select
5412: MAX(opinion2.AUTHORED_DATE)
5413: from
5414: AMW_OPINIONS_V opinion2
5415: where
5416: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID and
5417: opinion2.PK1_VALUE = opinion.PK1_VALUE and
5418: opinion2.PK3_VALUE = opinion.PK3_VALUE AND

Line 5483: AMW_OPINIONS_V opinion,

5479: count(1) into p_ineffective_controls
5480: from (
5481: select distinct ctrlassoc.control_id, orgprocess.organization_id
5482: FROM
5483: AMW_OPINIONS_V opinion,
5484: AMW_OPINION_TYPES_B opiniontype,
5485: FND_OBJECTS fndobject,
5486: AMW_OBJECT_OPINION_TYPES objectopiniontype,
5487: amw_process_org_basicinfo_v orgprocess,

Line 5495: AMW_OPINIONS_V opinion2

5491: (opinion.AUTHORED_DATE in
5492: (Select
5493: MAX(opinion2.AUTHORED_DATE)
5494: from
5495: AMW_OPINIONS_V opinion2
5496: where
5497: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
5498: opinion2.PK1_VALUE = opinion.PK1_VALUE and
5499: opinion2.PK3_VALUE = opinion.PK3_VALUE

Line 5574: AMW_OPINIONS_V opinion,

5570: Select
5571: count(1) into p_unmitigated_risks
5572: from (select distinct riskassoc.risk_id ,orgprocess.organization_id, orgprocess.Process_ID
5573: FROM
5574: AMW_OPINIONS_V opinion,
5575: AMW_OPINION_TYPES_B opiniontype,
5576: FND_OBJECTS fndobject,
5577: AMW_OBJECT_OPINION_TYPES objectopiniontype,
5578: amw_process_org_basicinfo_v orgprocess,

Line 5585: AMW_OPINIONS_V opinion2

5581: (opinion.AUTHORED_DATE in
5582: (Select
5583: MAX(opinion2.AUTHORED_DATE)
5584: from
5585: AMW_OPINIONS_V opinion2
5586: where
5587: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
5588: opinion2.PK1_VALUE = opinion.PK1_VALUE and
5589: opinion2.PK3_VALUE = opinion.PK3_VALUE and

Line 5711: AMW_OPINIONS_V opinion,

5707: Select
5708: count(1) into p_risks_verified
5709: from (select distinct riskassoc.risk_id ,orgprocess.organization_id, orgprocess.Process_ID
5710: FROM
5711: AMW_OPINIONS_V opinion,
5712: AMW_OPINION_TYPES_B opiniontype,
5713: FND_OBJECTS fndobject,
5714: AMW_OBJECT_OPINION_TYPES objectopiniontype,
5715: amw_process_org_basicinfo_v orgprocess,

Line 5722: AMW_OPINIONS_V opinion2

5718: (opinion.AUTHORED_DATE in
5719: (Select
5720: MAX(opinion2.AUTHORED_DATE)
5721: from
5722: AMW_OPINIONS_V opinion2
5723: where
5724: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
5725: opinion2.PK1_VALUE = opinion.PK1_VALUE and
5726: opinion2.PK3_VALUE = opinion.PK3_VALUE and

Line 5855: AMW_OPINIONS_V opinion,

5851: COUNT(1) into p_controls_verified
5852: FROM
5853: (select distinct ctrlassoc.control_id, orgprocess.organization_id
5854: FROM
5855: AMW_OPINIONS_V opinion,
5856: AMW_OPINION_TYPES_B opiniontype,
5857: FND_OBJECTS fndobject,
5858: AMW_OBJECT_OPINION_TYPES objectopiniontype,
5859: amw_process_org_basicinfo_v orgprocess,

Line 5867: AMW_OPINIONS_V opinion2

5863: (opinion.AUTHORED_DATE in
5864: (Select
5865: MAX(opinion2.AUTHORED_DATE)
5866: from
5867: AMW_OPINIONS_V opinion2
5868: where
5869: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
5870: opinion2.PK1_VALUE = opinion.PK1_VALUE and
5871: opinion2.PK3_VALUE = opinion.PK3_VALUE AND

Line 6042: AMW_OPINIONS_V opinion,

6038:
6039: select count(1) into P_PROCS_IN_CERTIFICATION from (
6040: Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
6041: FROM
6042: AMW_OPINIONS_V opinion,
6043: AMW_OPINION_TYPES_B opiniontype,
6044: FND_OBJECTS fndobject,
6045: AMW_OBJECT_OPINION_TYPES objectopiniontype,
6046: amw_process_org_basicinfo_v orgprocess

Line 6164: AMW_OPINIONS_V opinion,

6160:
6161: select count(1) into P_PROCS_EVALUATED from (
6162: Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
6163: FROM
6164: AMW_OPINIONS_V opinion,
6165: AMW_OPINION_TYPES_B opiniontype,
6166: FND_OBJECTS fndobject,
6167: AMW_OBJECT_OPINION_TYPES objectopiniontype,
6168: amw_process_org_basicinfo_v orgprocess

Line 6174: AMW_OPINIONS_V opinion2

6170: (opinion.AUTHORED_DATE in
6171: (Select
6172: MAX(opinion2.AUTHORED_DATE)
6173: from
6174: AMW_OPINIONS_V opinion2
6175: where
6176: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID and
6177: opinion2.PK1_VALUE = opinion.PK1_VALUE and
6178: opinion2.PK3_VALUE = opinion.PK3_VALUE

Line 6299: AMW_OPINIONS_V opinion,

6295:
6296: select count(1) into P_PROC_CERTIFIED_WITH_ISSUES from (
6297: Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
6298: FROM
6299: AMW_OPINIONS_V opinion,
6300: AMW_OPINION_TYPES_B opiniontype,
6301: FND_OBJECTS fndobject,
6302: AMW_OBJECT_OPINION_TYPES objectopiniontype,
6303: amw_process_org_basicinfo_v orgprocess

Line 6309: AMW_OPINIONS_V opinion2

6305: (opinion.AUTHORED_DATE in
6306: (Select
6307: MAX(opinion2.AUTHORED_DATE)
6308: from
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

Line 6431: AMW_OPINIONS_V opinion,

6427:
6428: select count(1) into P_org_with_ineffective_ctrls from (
6429: select distinct orgprocess.ORGANIZATION_ID
6430: FROM
6431: AMW_OPINIONS_V opinion,
6432: AMW_OPINION_TYPES_B opiniontype,
6433: FND_OBJECTS fndobject,
6434: AMW_OBJECT_OPINION_TYPES objectopiniontype,
6435: amw_process_org_basicinfo_v orgprocess

Line 6441: AMW_OPINIONS_V opinion2

6437: (opinion.AUTHORED_DATE in
6438: (Select
6439: MAX(opinion2.AUTHORED_DATE)
6440: from
6441: AMW_OPINIONS_V opinion2
6442: where
6443: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
6444: opinion2.PK1_VALUE = opinion.PK1_VALUE
6445: --AND

Line 6660: AMW_OPINIONS_V opinion,

6656: /* ------------------------
6657: select count(1) into P_org_evaluated from (
6658: select distinct orgprocess.ORGANIZATION_ID
6659: FROM
6660: AMW_OPINIONS_V opinion,
6661: AMW_OPINION_TYPES_B opiniontype,
6662: FND_OBJECTS fndobject,
6663: AMW_OBJECT_OPINION_TYPES objectopiniontype,
6664: amw_process_org_basicinfo_v orgprocess

Line 6670: AMW_OPINIONS_V opinion2

6666: (opinion.AUTHORED_DATE in
6667: (Select
6668: MAX(opinion2.AUTHORED_DATE)
6669: from
6670: AMW_OPINIONS_V opinion2
6671: where
6672: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
6673: opinion2.PK1_VALUE = opinion.PK1_VALUE AND
6674: opinion2.AUTHORED_DATE >= p_start_date

Line 6790: AMW_OPINIONS_V opinion,

6786: select count(1) into P_org_certified from (
6787: select
6788: distinct orgprocess.ORGANIZATION_ID
6789: FROM
6790: AMW_OPINIONS_V opinion,
6791: AMW_OPINION_TYPES_B opiniontype,
6792: FND_OBJECTS fndobject,
6793: AMW_OBJECT_OPINION_TYPES objectopiniontype,
6794: amw_process_org_basicinfo_v orgprocess

Line 6800: AMW_OPINIONS_V opinion2

6796: (opinion.AUTHORED_DATE in
6797: (Select
6798: MAX(opinion2.AUTHORED_DATE)
6799: from
6800: AMW_OPINIONS_V opinion2
6801: where
6802: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
6803: opinion2.PK1_VALUE = opinion.PK1_VALUE AND
6804: opinion2.AUTHORED_DATE >= p_start_date

Line 6918: AMW_OPINIONS_V opinion,

6914:
6915: select count(1) into P_proc_with_ineffective_ctrls from (
6916: Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
6917: FROM
6918: AMW_OPINIONS_V opinion,
6919: AMW_OPINION_TYPES_B opiniontype,
6920: FND_OBJECTS fndobject,
6921: AMW_OBJECT_OPINION_TYPES objectopiniontype,
6922: amw_process_org_basicinfo_v orgprocess

Line 6928: AMW_OPINIONS_V opinion2

6924: (opinion.AUTHORED_DATE in
6925: (Select
6926: MAX(opinion2.AUTHORED_DATE)
6927: from
6928: AMW_OPINIONS_V opinion2
6929: where
6930: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID and
6931: opinion2.PK1_VALUE = opinion.PK1_VALUE and
6932: opinion2.PK3_VALUE = opinion.PK3_VALUE

Line 7053: AMW_OPINIONS_V opinion,

7049:
7050: select count(1) into p_ineffective_controls from(
7051: select distinct ctrlassoc.control_id, orgprocess.organization_id
7052: FROM
7053: AMW_OPINIONS_V opinion,
7054: AMW_OPINION_TYPES_B opiniontype,
7055: FND_OBJECTS fndobject,
7056: AMW_OBJECT_OPINION_TYPES objectopiniontype,
7057: amw_process_org_basicinfo_v orgprocess,

Line 7065: AMW_OPINIONS_V opinion2

7061: (opinion.AUTHORED_DATE in
7062: (Select
7063: MAX(opinion2.AUTHORED_DATE)
7064: from
7065: AMW_OPINIONS_V opinion2
7066: where
7067: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
7068: opinion2.PK1_VALUE = opinion.PK1_VALUE and
7069: opinion2.PK3_VALUE = opinion.PK3_VALUE

Line 7199: AMW_OPINIONS_V opinion,

7195:
7196: select count(1) into p_unmitigated_risks from (
7197: select distinct riskassoc.risk_id ,orgprocess.organization_id, orgprocess.Process_ID
7198: FROM
7199: AMW_OPINIONS_V opinion,
7200: AMW_OPINION_TYPES_B opiniontype,
7201: FND_OBJECTS fndobject,
7202: AMW_OBJECT_OPINION_TYPES objectopiniontype,
7203: amw_process_org_basicinfo_v orgprocess,

Line 7210: AMW_OPINIONS_V opinion2

7206: (opinion.AUTHORED_DATE in
7207: (Select
7208: MAX(opinion2.AUTHORED_DATE)
7209: from
7210: AMW_OPINIONS_V opinion2
7211: where
7212: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
7213: opinion2.PK1_VALUE = opinion.PK1_VALUE and
7214: opinion2.PK3_VALUE = opinion.PK3_VALUE and

Line 7439: AMW_OPINIONS_V opinion,

7435:
7436: select count(1) into p_risks_verified from (
7437: select distinct riskassoc.risk_id ,orgprocess.organization_id, orgprocess.Process_ID
7438: FROM
7439: AMW_OPINIONS_V opinion,
7440: AMW_OPINION_TYPES_B opiniontype,
7441: FND_OBJECTS fndobject,
7442: AMW_OBJECT_OPINION_TYPES objectopiniontype,
7443: amw_process_org_basicinfo_v orgprocess,

Line 7450: AMW_OPINIONS_V opinion2

7446: (opinion.AUTHORED_DATE in
7447: (Select
7448: MAX(opinion2.AUTHORED_DATE)
7449: from
7450: AMW_OPINIONS_V opinion2
7451: where
7452: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
7453: opinion2.PK1_VALUE = opinion.PK1_VALUE and
7454: opinion2.PK3_VALUE = opinion.PK3_VALUE and

Line 7685: AMW_OPINIONS_V opinion,

7681:
7682: select count(1) into p_controls_verified from (
7683: select distinct ctrlassoc.control_id, orgprocess.organization_id
7684: FROM
7685: AMW_OPINIONS_V opinion,
7686: AMW_OPINION_TYPES_B opiniontype,
7687: FND_OBJECTS fndobject,
7688: AMW_OBJECT_OPINION_TYPES objectopiniontype,
7689: amw_process_org_basicinfo_v orgprocess,

Line 7697: AMW_OPINIONS_V opinion2

7693: (opinion.AUTHORED_DATE in
7694: (Select
7695: MAX(opinion2.AUTHORED_DATE)
7696: from
7697: AMW_OPINIONS_V opinion2
7698: where
7699: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
7700: opinion2.PK1_VALUE = opinion.PK1_VALUE and
7701: opinion2.PK3_VALUE = opinion.PK3_VALUE AND

Line 7823: AMW_OPINIONS_V opinion,

7819: is begin
7820: select count(1) into P_org_cert_with_issues from (
7821: select distinct orgprocess.ORGANIZATION_ID
7822: FROM
7823: AMW_OPINIONS_V opinion,
7824: AMW_OPINION_TYPES_B opiniontype,
7825: FND_OBJECTS fndobject,
7826: AMW_OBJECT_OPINION_TYPES objectopiniontype,
7827: amw_process_org_basicinfo_v orgprocess

Line 7833: AMW_OPINIONS_V opinion2

7829: (opinion.AUTHORED_DATE in
7830: (Select
7831: MAX(opinion2.AUTHORED_DATE)
7832: from
7833: AMW_OPINIONS_V opinion2
7834: where
7835: opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
7836: opinion2.PK1_VALUE = opinion.PK1_VALUE AND
7837: opinion2.AUTHORED_DATE >= p_start_date