[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: /* */
2240: IS
2241: CURSOR c_control_obj IS
2242: SELECT lookup_code
2243: FROM AMW_LOOKUPS
2244: WHERE lookup_type='AMW_CONTROL_OBJECTIVES'
2245: AND enabled_flag='Y'
2246: AND tag=p_lookup_tag;
2247:
2248: l_ctrl_obj_code VARCHAR2(30);
2272:
2273: if (l_ctrl_obj_flag = 'N') then
2274: select count(*)
2275: into l_obj_exists
2276: from amw_control_objectives
2277: where control_rev_id = vx_control_rev_id
2278: AND objective_code = l_ctrl_obj_code;
2279:
2280: if(l_obj_exists > 0) then
2277: where control_rev_id = vx_control_rev_id
2278: AND objective_code = l_ctrl_obj_code;
2279:
2280: if(l_obj_exists > 0) then
2281: delete from amw_control_objectives
2282: where control_rev_id = vx_control_rev_id
2283: AND objective_code = l_ctrl_obj_code;
2284: end if;
2285: end if;
2288: --- this control objective has been selected
2289: --- need to check if it is already present or not
2290: select count(*)
2291: into l_obj_exists
2292: from amw_control_objectives
2293: where control_rev_id = vx_control_rev_id
2294: AND objective_code = l_ctrl_obj_code;
2295:
2296: if(l_obj_exists = 0) then
2294: AND objective_code = l_ctrl_obj_code;
2295:
2296: if(l_obj_exists = 0) then
2297: ---IF SQL%NOTFOUND THEN
2298: INSERT INTO amw_control_objectives
2299: (control_objective_id
2300: ,last_update_date
2301: ,last_updated_by
2302: ,creation_date
2307: ---,effective_date_from
2308: ---,effective_date_to
2309: ,OBJECT_VERSION_NUMBER
2310: ) VALUES (
2311: amw_control_objectives_s.NEXTVAL
2312: ,SYSDATE
2313: ,v_user_id
2314: ,SYSDATE
2315: ,v_user_id
2332: || p_lookup_tag
2333: || 'using interface id of: '
2334: || v_interface_id
2335: || ' flag must be Y/N';
2336: v_table_name := 'AMW_CONTROL_OBJECTIVES';
2337: update_interface_with_error (v_err_msg
2338: ,v_table_name
2339: ,v_interface_id
2340: );
2345: || p_lookup_tag
2346: || 'using interface id of: '
2347: || v_interface_id
2348: || ' code does not exist';
2349: v_table_name := 'AMW_CONTROL_OBJECTIVES';
2350: update_interface_with_error (v_err_msg
2351: ,v_table_name
2352: ,v_interface_id
2353: );
2359: || 'using interface id of: '
2360: || v_interface_id
2361: || ' '
2362: || SUBSTR (SQLERRM, 1, 100);
2363: v_table_name := 'AMW_CONTROL_OBJECTIVES';
2364: update_interface_with_error (v_err_msg
2365: ,v_table_name
2366: ,v_interface_id
2367: );