[Home] [Help]
1998: l_user_id := fnd_profile.value ('USER_ID');
1999: l_login_id := FND_PROFILE.VALUE('LOGIN_ID');
2000: if(p_src_scnId is not null and p_dest_scnId is not null) then
2001:
2002: insert into msc_scenario_plans (scenario_id,
2003: plan_type,
2004: plan_id,
2005: created_by,
2006: creation_Date ,
2021: l_login_id,
2022: status,
2023: run_date,
2024: plan_horizon,
2025: plan_run_id from msc_scenario_plans where scenario_id = p_src_scnId);
2026: end if;
2027: end copy_scn_plans;
2028:
2029: FUNCTION get_scn_users (p_scn_id in number) return varchar2 is
2052: function plan_scns_count(p_plan_id in number, p_scn_id in number, p_plan_run_id in number) return number is
2053: l_count number := 0;
2054:
2055: cursor c_plans is
2056: select count(*) from msc_scenario_plans
2057: where scenario_id <> p_scn_id and plan_id = p_plan_id and plan_run_id = p_plan_run_id;
2058:
2059: begin
2060: open c_plans;
2065: end;
2066:
2067: /* This procedure is called to archive a scenario.
2068: Logic
2069: Archive_Scn_Conc looks at all the plans to be archived (Archive_flag in msc_scenario_plans) and
2070: for every plan_id,
2071: generate plan_run_id and update plan_run_id field for it.
2072: call populate_Details api
2073: end for;
2079: procedure archive_scn_conc( errbuf out nocopy varchar2, retcode out nocopy varchar2 , p_scn_id in number) is
2080:
2081: cursor c_scn_plans is
2082: select plan_type,plan_id,run_date,plan_run_id
2083: from msc_scenario_plans
2084: where scenario_id = p_scn_id and archive_flag = 'Y';
2085:
2086: l_plan_type number;
2087: l_plan_id number;
2114: l_login_id := FND_PROFILE.VALUE('LOGIN_ID');
2115:
2116: select msc_scn_scenarios_s.nextval into l_new_scn_id from dual;
2117:
2118: -- Insert rows into msc_scenarios,msc_scenario_plans,msc_scenario_users;
2119:
2120: insert into msc_scenarios(scenario_id,
2121: scenario_name,
2122: created_by,
2154: gs_name,
2155: gs_name_orig
2156: from msc_scenarios where scenario_id = p_scn_id);
2157:
2158: insert into msc_scenario_plans (scenario_id,
2159: plan_type,
2160: plan_id,
2161: created_by,
2162: creation_Date ,
2177: l_login_id,
2178: status,
2179: run_date,
2180: plan_horizon,
2181: plan_run_id from msc_scenario_plans where scenario_id = p_scn_id);
2182:
2183: insert into msc_scenario_users (scenario_id,
2184: user_id,
2185: created_by,
2218: --null; -- errored out
2219: msc_util.msc_debug('Archive Plan Failed for Plan:'||get_plan_name(l_plan_type,l_plan_id,l_plan_run_id));
2220: raise exc_error_plan_arch;
2221: end if;
2222: update msc_scenario_plans set plan_run_id = l_plan_run_id where
2223: scenario_id = p_scn_id and plan_id = l_plan_id and plan_type = l_plan_type;
2224:
2225: commit;
2226:
2244: procedure purge_scn_conc( errbuf out nocopy varchar2, retcode out nocopy varchar2 , p_scn_id in number) is
2245:
2246: cursor c_scn_plans is
2247: select r.plan_run_name, r.plan_run_id
2248: from msc_scenario_plans sp, msc_plan_runs r
2249: where sp.scenario_id = p_scn_id and sp.purge_flag = 'Y'
2250: and sp.plan_run_id = r.plan_run_id;
2251:
2252: cursor c_scn_version is
2281:
2282: end if;
2283: delete msc_scenarios where scenario_id = p_scn_id;
2284:
2285: delete msc_scenario_plans where scenario_id = p_scn_id;
2286:
2287: delete msc_Scenario_users where scenario_id = p_scn_id;
2288:
2289: delete msc_Scenario_set_details where scenario_id = p_scn_id;
2323: --null; -- errored out
2324: raise exc_error_purge_plan;
2325: end if;
2326:
2327: delete from msc_scenario_plans where plan_run_id in (select plan_run_id from msc_plan_runs where plan_name=l_plan_name);
2328: commit;
2329: end if;
2330: EXCEPTION
2331: when exc_error_purge_plan then
2406: manual activities and plans in that scenario.
2407:
2408: We will look into two tables i.e
2409: MSC_SCENARIO_ACTIVITIES
2410: MSC_SCENARIO_PLANS
2411: Logic for deriving Status is as follows:
2412:
2413: Scenario status When this condition is satisfied
2414: --------------- --------------------------------
2837: where plan_id = ll_plan_id;
2838:
2839: cursor c_scn_plan (p_scn_id number, p_plan_id number) is
2840: select count(*)
2841: from msc_scenario_plans
2842: where scenario_id = p_scn_id
2843: and plan_id = p_plan_id;
2844:
2845: begin
2870: l_user_id := 0;
2871:
2872: select msc_scn_scenarios_s.nextval into l_scn_id from dual;
2873:
2874: --Insert rows into msc_scenarios,msc_scenario_plans,msc_scenario_users;
2875:
2876: insert into msc_scenarios (scenario_id, scenario_name,
2877: created_by, creation_date, last_update_date, last_updated_by, last_update_login,
2878: parent_scn_id, description, owner,
2901: open c_plan_type(p_plan_id_arr(i));
2902: fetch c_plan_type into l_plan_type;
2903: close c_plan_type;
2904:
2905: insert into msc_scenario_plans (scenario_id, plan_type, plan_id,
2906: created_by, creation_Date, last_update_date, last_updated_by, last_update_login,
2907: status, run_date, plan_horizon, plan_run_id)
2908: values
2909: (l_scn_id, l_plan_type, p_plan_id_arr(i),