[Home] [Help]
8: /* following tables: */
9: /* INSERTS OR UPDATES ARE DONE AGAINIST THE FOLLOWING TABLES */
10: /* Insert into AMW_RISKS_B and AMW_RISKS_TL */
11: /* Insert into AMW_CONTROLS_B and AMW_CONTROLS_TL */
12: /* Insert into AMW_CONTROL_ASSOCIATIONS */
13: /* Insert into AMW_RISK_ASSOCIATIONS */
14: /* Insert into AMW_CONTROL_OBJECTIVES */
15: /* Insert into AMW_CONTROL_ASSERTIONS */
16: /* Updates amw_risk-ctrl_interface, with error messages */
1071: --CHECK FOR EXISTING LATEST_REVISION OF THIS ASSOCIATION, IF ANY
1072: BEGIN
1073: SELECT CONTROL_ASSOCIATION_ID,APPROVAL_DATE
1074: INTO L_CONTROL_ASSOCIATION_ID,L_APPROVAL_DATE
1075: FROM AMW_CONTROL_ASSOCIATIONS
1076: WHERE CONTROL_ID=LX_CONTROL_ID
1077: AND PK1=RC_REC.ORGANIZATION_ID
1078: AND PK2=L_PROCESS_ID
1079: AND PK3=LX_RISK_ID
1106: IF(L_APPROVAL_DATE IS NOT NULL) THEN
1107: --THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
1108: --AND IS APPROVED, SO
1109: BEGIN
1110: UPDATE AMW_CONTROL_ASSOCIATIONS
1111: SET DELETION_DATE=SYSDATE
1112: ,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
1113: ,LAST_UPDATE_DATE=SYSDATE
1114: ,LAST_UPDATED_BY=G_USER_ID
1143: --check for existing latest_revision of this association, if any
1144: begin
1145: select control_association_id,approval_date
1146: into l_control_association_id,l_approval_date
1147: from amw_control_associations
1148: where control_id=lx_control_id
1149: and pk1=rc_rec.organization_id
1150: and pk2=lx_risk_id
1151: and object_type='ENTITY_CONTROL'
1175: if(l_approval_date is not null) then
1176: --this means that this is latest revision for this association
1177: --and is approved, so
1178: begin
1179: update amw_control_associations
1180: set deletion_date=sysdate
1181: ,object_version_number=object_version_number+1
1182: ,last_update_date=sysdate
1183: ,last_updated_by=g_user_id
2699: )
2700: IS
2701:
2702: BEGIN
2703: INSERT INTO amw_control_associations(
2704: control_association_id
2705: ,last_update_date
2706: ,last_updated_by
2707: ,creation_date
2715: ,effective_date_to
2716: ,ASSOCIATION_CREATION_DATE
2717: ,OBJECT_VERSION_NUMBER)
2718: VALUES (
2719: amw_control_associations_s.NEXTVAL
2720: ,SYSDATE
2721: ,v_user_id
2722: ,SYSDATE
2723: ,v_user_id
3245: ,p_control_id in number)
3246: is
3247:
3248: begin
3249: insert into amw_control_associations(
3250: control_association_id
3251: ,last_update_date
3252: ,last_updated_by
3253: ,creation_date
3260: ,effective_date_to
3261: ,association_creation_date
3262: ,object_version_number)
3263: values (
3264: amw_control_associations_s.nextval
3265: ,sysdate
3266: ,v_user_id
3267: ,sysdate
3268: ,v_user_id