[Home] [Help]
781: controls.DELETION_APPROVAL_DATE,
782: control.CONTROL_REV_ID
783: FROM
784: AMW_RISK_ASSOCIATIONS risks,
785: AMW_CONTROL_ASSOCIATIONS controls,
786: AMW_CONTROLS_B control
787: WHERE
788: controls.object_type='RISK_ORG'
789: and control.CURR_APPROVED_FLAG = 'Y'
805: controls.DELETION_APPROVAL_DATE,
806: control.CONTROL_REV_ID
807: FROM
808: AMW_RISK_ASSOCIATIONS risks,
809: AMW_CONTROL_ASSOCIATIONS controls,
810: AMW_CONTROLS_B control
811: WHERE
812: controls.object_type='ENTITY_CONTROL'
813: and control.CURR_APPROVED_FLAG = 'Y'
873: -- Initialize API return status to SUCCESS
874: x_return_status := FND_API.G_RET_STS_SUCCESS;
875:
876:
877: SELECT COUNT(1) INTO l_count FROM AMW_CONTROL_ASSOCIATIONS
878: WHERE OBJECT_TYPE = 'RISK_FINCERT'
879: and PK1 = p_certification_id;
880:
881: IF (l_count = 0) THEN
886: OPEN last_evaluation(control_rec.pk1, control_rec.control_id);
887: FETCH last_evaluation INTO m_opinion_log_id;
888: CLOSE last_evaluation;
889:
890: INSERT INTO AMW_CONTROL_ASSOCIATIONS(
891: CONTROL_ASSOCIATION_ID,
892: CONTROL_ID,
893: PK1,
894: PK2,
906: APPROVAL_DATE,
907: DELETION_DATE,
908: DELETION_APPROVAL_DATE,
909: CONTROL_REV_ID)
910: VALUES (AMW_CONTROL_ASSOCIATIONS_S.nextval,
911: control_rec.control_id,
912: p_certification_id,
913: control_rec.PK1,
914: control_rec.PK2,
987: ap.DELETION_APPROVAL_DATE,
988: apb.AUDIT_PROCEDURE_REV_ID
989: FROM
990: AMW_AP_ASSOCIATIONS ap,
991: AMW_CONTROL_ASSOCIATIONS controls,
992: AMW_AUDIT_PROCEDURES_B apb
993: WHERE
994: ap.object_type='CTRL_ORG'
995: and apb.CURR_APPROVED_FLAG = 'Y'
1011: ap.DELETION_APPROVAL_DATE,
1012: apb.AUDIT_PROCEDURE_REV_ID
1013: FROM
1014: AMW_AP_ASSOCIATIONS ap,
1015: AMW_CONTROL_ASSOCIATIONS controls,
1016: AMW_AUDIT_PROCEDURES_B apb
1017: WHERE
1018: ap.object_type='ENTITY_CTRL_AP'
1019: and apb.CURR_APPROVED_FLAG = 'Y'
1628: OBJECT_VERSION_NUMBER )
1629: SELECT distinct 'ACCOUNT' OBJECT_TYPE , fin_certification_id, statement_group_id, financial_statement_id, null financial_item_id,
1630: account_group_id, natural_account_id, organization_id, control_id, control_rev_id, pk5 opinion_log_id, 1, sysdate, 1, sysdate, 1, null, 1
1631: from amw_fin_cert_scope scp,
1632: amw_control_associations ctrl
1633: where ctrl.pk1 = scp.fin_certification_id
1634: and ctrl.object_type = 'RISK_FINCERT'
1635: and scp.natural_account_id is not null
1636: and scp.organization_id = ctrl.pk2
1639: union all
1640: select distinct 'FINANCIAL ITEM' OBJECT_TYPE, fin_certification_id, statement_group_id, financial_statement_id, financial_item_id,
1641: 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
1642: from amw_fin_cert_scope scp,
1643: amw_control_associations ctrl
1644: where ctrl.pk1 = scp.fin_certification_id
1645: and ctrl.object_type = 'RISK_FINCERT'
1646: and scp.organization_id = ctrl.pk2
1647: and scp.process_id = ctrl.pk3
1649: union all
1650: select distinct 'FINANCIAL STATEMENT' OBJECT_TYPE, fin_certification_id, statement_group_id, financial_statement_id, null financial_item_id,
1651: 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
1652: from amw_fin_cert_scope scp,
1653: amw_control_associations ctrl
1654: where ctrl.pk1 = scp.fin_certification_id
1655: and ctrl.object_type = 'RISK_FINCERT'
1656: and scp.organization_id = ctrl.pk2
1657: and scp.process_id = ctrl.pk3