[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: /* */
2124: IS
2125: CURSOR c_control_obj IS
2126: SELECT lookup_code
2127: FROM AMW_LOOKUPS
2128: WHERE lookup_type='AMW_CONTROL_OBJECTIVES'
2129: AND enabled_flag='Y'
2130: AND tag=p_lookup_tag;
2131:
2132: l_ctrl_obj_code VARCHAR2(30);
2156:
2157: if (l_ctrl_obj_flag = 'N') then
2158: select count(*)
2159: into l_obj_exists
2160: from amw_control_objectives
2161: where control_rev_id = vx_control_rev_id
2162: AND objective_code = l_ctrl_obj_code;
2163:
2164: if(l_obj_exists > 0) then
2161: where control_rev_id = vx_control_rev_id
2162: AND objective_code = l_ctrl_obj_code;
2163:
2164: if(l_obj_exists > 0) then
2165: delete from amw_control_objectives
2166: where control_rev_id = vx_control_rev_id
2167: AND objective_code = l_ctrl_obj_code;
2168: end if;
2169: end if;
2172: --- this control objective has been selected
2173: --- need to check if it is already present or not
2174: select count(*)
2175: into l_obj_exists
2176: from amw_control_objectives
2177: where control_rev_id = vx_control_rev_id
2178: AND objective_code = l_ctrl_obj_code;
2179:
2180: if(l_obj_exists = 0) then
2178: AND objective_code = l_ctrl_obj_code;
2179:
2180: if(l_obj_exists = 0) then
2181: ---IF SQL%NOTFOUND THEN
2182: INSERT INTO amw_control_objectives
2183: (control_objective_id
2184: ,last_update_date
2185: ,last_updated_by
2186: ,creation_date
2188: ,control_rev_id
2189: ,objective_code
2190: ,OBJECT_VERSION_NUMBER
2191: ) VALUES (
2192: amw_control_objectives_s.NEXTVAL
2193: ,SYSDATE
2194: ,v_user_id
2195: ,SYSDATE
2196: ,v_user_id
2210: || p_lookup_tag
2211: || 'using interface id of: '
2212: || v_interface_id
2213: || ' flag must be Y/N';
2214: v_table_name := 'AMW_CONTROL_OBJECTIVES';
2215: update_interface_with_error (v_err_msg
2216: ,v_table_name
2217: ,v_interface_id
2218: );
2223: || p_lookup_tag
2224: || 'using interface id of: '
2225: || v_interface_id
2226: || ' code does not exist';
2227: v_table_name := 'AMW_CONTROL_OBJECTIVES';
2228: update_interface_with_error (v_err_msg
2229: ,v_table_name
2230: ,v_interface_id
2231: );
2237: || 'using interface id of: '
2238: || v_interface_id
2239: || ' '
2240: || SUBSTR (SQLERRM, 1, 100);
2241: v_table_name := 'AMW_CONTROL_OBJECTIVES';
2242: update_interface_with_error (v_err_msg
2243: ,v_table_name
2244: ,v_interface_id
2245: );