[Home] [Help]
918: and fin.fin_certification_id = :1 ';
919: ****************/
920:
921:
922: /*** use opinion_log_id in amw_control_associations instead of opinion_log_id in amw_fin_item_acc_ctrl
923: **** so that it consists with VO query and also reduces the maintainence ***********************/
924: l_stmt := 'select count(1) from(
925: select distinct fin.control_id, fin.organization_id
926: FROM
925: select distinct fin.control_id, fin.organization_id
926: FROM
927: amw_opinion_log_mv aov,
928: amw_fin_item_acc_ctrl fin,
929: amw_control_associations ctrls
930: WHERE
931: aov.opinion_log_id = ctrls.pk5
932: and ctrls.pk1 = fin.fin_certification_id
933: and ctrls.object_type = ''RISK_FINCERT''
1017: select distinct fin.control_id, fin.organization_id
1018: FROM
1019: AMW_OPINION_LOG_MV aov,
1020: amw_fin_item_acc_ctrl fin,
1021: amw_control_associations ctrls
1022: WHERE
1023: aov.opinion_log_id = ctrls.pk5
1024: and ctrls.pk1 = fin.fin_certification_id
1025: and ctrls.object_type = ''RISK_FINCERT''
2283: OBJECT_VERSION_NUMBER )
2284: SELECT distinct 'ACCOUNT' OBJECT_TYPE , fin_certification_id, statement_group_id, financial_statement_id, null financial_item_id,
2285: account_group_id, natural_account_id, organization_id, control_id, control_rev_id, pk5 opinion_log_id, 1, sysdate, 1, sysdate, 1, null, 1
2286: from amw_fin_cert_scope scp,
2287: amw_control_associations ctrl
2288: where ctrl.pk1 = scp.fin_certification_id
2289: and ctrl.object_type = 'RISK_FINCERT'
2290: and scp.natural_account_id is not null
2291: and scp.organization_id = ctrl.pk2
2294: union all
2295: select distinct 'FINANCIAL ITEM' OBJECT_TYPE, fin_certification_id, statement_group_id, financial_statement_id, financial_item_id,
2296: null account_group_id, null natural_account_id, organization_id, control_id, control_rev_id, pk5 opinion_log_id, 1, sysdate, 1, sysdate, 1, null, 1
2297: from amw_fin_cert_scope scp,
2298: amw_control_associations ctrl
2299: where ctrl.pk1 = scp.fin_certification_id
2300: and ctrl.object_type = 'RISK_FINCERT'
2301: and scp.organization_id = ctrl.pk2
2302: and scp.process_id = ctrl.pk3
2304: union all
2305: select distinct 'FINANCIAL STATEMENT' OBJECT_TYPE, fin_certification_id, statement_group_id, financial_statement_id, null financial_item_id,
2306: null account_group_id, null natural_account_id, organization_id, control_id, control_rev_id, pk5 opinion_log_id, 1, sysdate, 1, sysdate, 1, null, 1
2307: from amw_fin_cert_scope scp,
2308: amw_control_associations ctrl
2309: where ctrl.pk1 = scp.fin_certification_id
2310: and ctrl.object_type = 'RISK_FINCERT'
2311: and scp.organization_id = ctrl.pk2
2312: and scp.process_id = ctrl.pk3
3174: x_return_status => l_return_status,
3175: x_msg_count => l_msg_count,
3176: x_msg_data => l_msg_data);
3177:
3178: UPDATE AMW_CONTROL_ASSOCIATIONS SET
3179: LAST_UPDATE_DATE = sysdate,
3180: last_updated_by = fnd_global.user_id,
3181: last_update_login = fnd_global.conc_login_id,
3182: pk5 = l_opinion_log_id
3396: (p_certification_id => p_certification_id,
3397: x_return_status => l_return_status,
3398: x_msg_count => l_msg_count,
3399: x_msg_data => l_msg_data);
3400: fnd_file.put_line(fnd_file.LOG, 'finish populating amw_control_associations table:p_certification_id = ' || p_certification_id || to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
3401: Populate_Fin_AP_Ass_Sum
3402: (p_certification_id => p_certification_id,
3403: x_return_status => l_return_status,
3404: x_msg_count => l_msg_count,
4166:
4167: CURSOR ineffective_controls IS
4168: SELECT count(distinct aca.control_id)
4169: FROM
4170: AMW_CONTROL_ASSOCIATIONS aca,
4171: AMW_FIN_PROCESS_FLAT orgrel,
4172: AMW_OPINION_MV aov
4173: WHERE
4174: aca.object_type = 'RISK_ORG'
4197:
4198: CURSOR total_controls IS
4199: SELECT count(distinct aca.control_id)
4200: FROM
4201: AMW_CONTROL_ASSOCIATIONS aca,
4202: AMW_FIN_PROCESS_FLAT orgrel
4203: WHERE aca.object_type = 'RISK_ORG'
4204: and aca.pk1 = p_organization_id
4205: and orgrel.fin_certification_id = p_certification_id
4212:
4213: CURSOR verified_controls IS
4214: SELECT count(distinct aca.control_id)
4215: FROM
4216: AMW_CONTROL_ASSOCIATIONS aca,
4217: AMW_FIN_PROCESS_FLAT orgrel,
4218: AMW_OPINION_MV aov
4219: WHERE
4220: aca.object_type = 'RISK_ORG'
4929: controls.DELETION_APPROVAL_DATE,
4930: control.CONTROL_REV_ID
4931: FROM
4932: AMW_RISK_ASSOCIATIONS risks,
4933: AMW_CONTROL_ASSOCIATIONS controls,
4934: AMW_CONTROLS_B control
4935: WHERE
4936: controls.object_type='RISK_ORG'
4937: and control.CURR_APPROVED_FLAG = 'Y'
4957: controls.DELETION_APPROVAL_DATE,
4958: control.CONTROL_REV_ID
4959: FROM
4960: AMW_RISK_ASSOCIATIONS risks,
4961: AMW_CONTROL_ASSOCIATIONS controls,
4962: AMW_CONTROLS_B control
4963: WHERE
4964: controls.object_type='ENTITY_CONTROL'
4965: and control.CURR_APPROVED_FLAG = 'Y'
5023: -- Initialize API return status to SUCCESS
5024: x_return_status := FND_API.G_RET_STS_SUCCESS;
5025:
5026:
5027: SELECT COUNT(1) INTO l_count FROM AMW_CONTROL_ASSOCIATIONS
5028: WHERE OBJECT_TYPE = 'RISK_FINCERT'
5029: and PK1 = p_certification_id;
5030:
5031: IF (l_count = 0) THEN
5036: OPEN last_evaluation(control_rec.pk1, control_rec.control_id);
5037: FETCH last_evaluation INTO m_opinion_log_id;
5038: CLOSE last_evaluation;
5039:
5040: INSERT INTO AMW_CONTROL_ASSOCIATIONS(
5041: CONTROL_ASSOCIATION_ID,
5042: CONTROL_ID,
5043: PK1,
5044: PK2,
5056: APPROVAL_DATE,
5057: DELETION_DATE,
5058: DELETION_APPROVAL_DATE,
5059: CONTROL_REV_ID)
5060: VALUES (AMW_CONTROL_ASSOCIATIONS_S.nextval,
5061: control_rec.control_id,
5062: p_certification_id,
5063: control_rec.PK1,
5064: control_rec.PK2,
5088: OPEN last_evaluation(control_rec.pk1, control_rec.control_id);
5089: FETCH last_evaluation INTO m_opinion_log_id;
5090: CLOSE last_evaluation;
5091:
5092: UPDATE AMW_CONTROL_ASSOCIATIONS SET
5093: LAST_UPDATE_DATE = sysdate,
5094: last_updated_by = fnd_global.user_id,
5095: last_update_login = fnd_global.conc_login_id,
5096: pk5 = m_opinion_log_id
5264: ap.DELETION_APPROVAL_DATE,
5265: apb.AUDIT_PROCEDURE_REV_ID
5266: FROM
5267: AMW_AP_ASSOCIATIONS ap,
5268: AMW_CONTROL_ASSOCIATIONS controls,
5269: AMW_AUDIT_PROCEDURES_B apb
5270: WHERE
5271: ap.object_type='CTRL_ORG'
5272: and apb.CURR_APPROVED_FLAG = 'Y'
5290: ap.DELETION_APPROVAL_DATE,
5291: apb.AUDIT_PROCEDURE_REV_ID
5292: FROM
5293: AMW_AP_ASSOCIATIONS ap,
5294: AMW_CONTROL_ASSOCIATIONS controls,
5295: AMW_AUDIT_PROCEDURES_B apb
5296: WHERE
5297: ap.object_type='ENTITY_CTRL_AP'
5298: and apb.CURR_APPROVED_FLAG = 'Y'
5846: AND aov.pk2_value not in (select audit_project_id from amw_audit_projects where audit_project_status = 'CANC');
5847:
5848: CURSOR ineffective_controls IS
5849: SELECT count(distinct aca.control_id)
5850: FROM AMW_CONTROL_ASSOCIATIONS aca ,
5851: AMW_OPINION_MV aov
5852: WHERE aca.object_type = 'RISK_FINCERT'
5853: AND aca.pk1 = p_certification_id
5854: AND aca.pk2 = p_organization_id
5868:
5869:
5870: CURSOR total_controls IS
5871: SELECT count(distinct aca.control_id)
5872: FROM AMW_CONTROL_ASSOCIATIONS aca
5873: WHERE aca.object_type = 'RISK_FINCERT'
5874: AND aca.pk1 = p_certification_id
5875: AND aca.pk2 = p_organization_id;
5876:
5875: AND aca.pk2 = p_organization_id;
5876:
5877: CURSOR verified_controls IS
5878: SELECT count(distinct aca.control_id)
5879: FROM AMW_CONTROL_ASSOCIATIONS aca ,
5880: AMW_OPINION_MV aov
5881: WHERE aca.object_type = 'RISK_FINCERT'
5882: AND aca.pk1 = p_certification_id
5883: AND aca.pk2 = p_organization_id
6950:
6951:
6952: CURSOR new_controls_added IS
6953: SELECT count(1)
6954: FROM AMW_CONTROL_ASSOCIATIONS
6955: WHERE association_creation_date >= (SELECT period.start_date
6956: FROM AMW_CERTIFICATION_B cert, AMW_GL_PERIODS_V period
6957: WHERE cert.object_type = 'FIN_STMT' and cert.certification_period_name = period.period_name
6958: AND cert.certification_period_set_name = period.period_set_name
8029:
8030: --CURSOR TO GET OLD EVAL OPINION ID
8031: CURSOR Get_old_opinion_id(l_cert_id number, l_org_id number) IS
8032: SELECT PK5
8033: FROM AMW_CONTROL_ASSOCIATIONS
8034: WHERE OBJECT_TYPE = 'RISK_FINCERT'
8035: AND PK1 = l_cert_id
8036: AND PK2 = l_org_id
8037: AND CONTROL_ID = p_ctrl_id;
10230: controls.DELETION_APPROVAL_DATE,
10231: control.CONTROL_REV_ID
10232: FROM
10233: AMW_RISK_ASSOCIATIONS risks,
10234: AMW_CONTROL_ASSOCIATIONS controls,
10235: AMW_CONTROLS_B control
10236: WHERE
10237: controls.object_type='RISK_ORG'
10238: and control.CURR_APPROVED_FLAG = 'Y'
10254: controls.DELETION_APPROVAL_DATE,
10255: control.CONTROL_REV_ID
10256: FROM
10257: AMW_RISK_ASSOCIATIONS risks,
10258: AMW_CONTROL_ASSOCIATIONS controls,
10259: AMW_CONTROLS_B control
10260: WHERE
10261: controls.object_type='ENTITY_CONTROL'
10262: and control.CURR_APPROVED_FLAG = 'Y'
10324: -- Initialize API return status to SUCCESS
10325: x_return_status := FND_API.G_RET_STS_SUCCESS;
10326:
10327:
10328: SELECT COUNT(1) INTO l_count FROM AMW_CONTROL_ASSOCIATIONS
10329: WHERE OBJECT_TYPE = 'RISK_FINCERT'
10330: and PK1 = p_certification_id;
10331:
10332: IF (l_count = 0) THEN
10337: OPEN last_evaluation(control_rec.pk1, control_rec.control_id);
10338: FETCH last_evaluation INTO m_opinion_log_id;
10339: CLOSE last_evaluation;
10340:
10341: INSERT INTO AMW_CONTROL_ASSOCIATIONS(
10342: CONTROL_ASSOCIATION_ID,
10343: CONTROL_ID,
10344: PK1,
10345: PK2,
10357: APPROVAL_DATE,
10358: DELETION_DATE,
10359: DELETION_APPROVAL_DATE,
10360: CONTROL_REV_ID)
10361: VALUES (AMW_CONTROL_ASSOCIATIONS_S.nextval,
10362: control_rec.control_id,
10363: p_certification_id,
10364: control_rec.PK1,
10365: control_rec.PK2,
10438: ap.DELETION_APPROVAL_DATE,
10439: apb.AUDIT_PROCEDURE_REV_ID
10440: FROM
10441: AMW_AP_ASSOCIATIONS ap,
10442: AMW_CONTROL_ASSOCIATIONS controls,
10443: AMW_AUDIT_PROCEDURES_B apb
10444: WHERE
10445: ap.object_type='CTRL_ORG'
10446: and apb.CURR_APPROVED_FLAG = 'Y'
10462: ap.DELETION_APPROVAL_DATE,
10463: apb.AUDIT_PROCEDURE_REV_ID
10464: FROM
10465: AMW_AP_ASSOCIATIONS ap,
10466: AMW_CONTROL_ASSOCIATIONS controls,
10467: AMW_AUDIT_PROCEDURES_B apb
10468: WHERE
10469: ap.object_type='ENTITY_CTRL_AP'
10470: and apb.CURR_APPROVED_FLAG = 'Y'