[Home] [Help]
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 */
17: /* Deleting successful production inserts, based on profile */
18: /* */
869: IS
870: CURSOR c_control_obj IS
871: SELECT lookup_code
872: FROM AMW_LOOKUPS
873: WHERE lookup_type='AMW_CONTROL_OBJECTIVES'
874: AND enabled_flag='Y'
875: AND tag=p_lookup_tag;
876:
877: l_ctrl_obj_code VARCHAR2(30);
901:
902: if (l_ctrl_obj_flag = 'N') then
903: select count(*)
904: into l_obj_exists
905: from amw_control_objectives
906: where control_rev_id = vx_control_rev_id
907: AND objective_code = l_ctrl_obj_code;
908:
909: if(l_obj_exists > 0) then
906: where control_rev_id = vx_control_rev_id
907: AND objective_code = l_ctrl_obj_code;
908:
909: if(l_obj_exists > 0) then
910: delete from amw_control_objectives
911: where control_rev_id = vx_control_rev_id
912: AND objective_code = l_ctrl_obj_code;
913: end if;
914: end if;
917: --- this control objective has been selected
918: --- need to check if it is already present or not
919: select count(*)
920: into l_obj_exists
921: from amw_control_objectives
922: where control_rev_id = vx_control_rev_id
923: AND objective_code = l_ctrl_obj_code;
924:
925: if(l_obj_exists = 0) then
923: AND objective_code = l_ctrl_obj_code;
924:
925: if(l_obj_exists = 0) then
926: ---IF SQL%NOTFOUND THEN
927: INSERT INTO amw_control_objectives
928: (control_objective_id
929: ,last_update_date
930: ,last_updated_by
931: ,creation_date
936: ---,effective_date_from
937: ---,effective_date_to
938: ,OBJECT_VERSION_NUMBER
939: ) VALUES (
940: amw_control_objectives_s.NEXTVAL
941: ,SYSDATE
942: ,v_user_id
943: ,SYSDATE
944: ,v_user_id
961: || p_lookup_tag
962: || 'using interface id of: '
963: || v_interface_id
964: || ' flag must be Y/N';
965: v_table_name := 'AMW_CONTROL_OBJECTIVES';
966: update_interface_with_error (v_err_msg
967: ,v_table_name
968: ,v_interface_id
969: );
974: || p_lookup_tag
975: || 'using interface id of: '
976: || v_interface_id
977: || ' code does not exist';
978: v_table_name := 'AMW_CONTROL_OBJECTIVES';
979: update_interface_with_error (v_err_msg
980: ,v_table_name
981: ,v_interface_id
982: );
988: || 'using interface id of: '
989: || v_interface_id
990: || ' '
991: || SUBSTR (SQLERRM, 1, 100);
992: v_table_name := 'AMW_CONTROL_OBJECTIVES';
993: update_interface_with_error (v_err_msg
994: ,v_table_name
995: ,v_interface_id
996: );