[Home] [Help]
1994: l_user_id := fnd_profile.value ('USER_ID');
1995: l_login_id := FND_PROFILE.VALUE('LOGIN_ID');
1996: if(p_src_scnId is not null and p_dest_scnId is not null) then
1997:
1998: insert into msc_scenario_plans (scenario_id,
1999: plan_type,
2000: plan_id,
2001: created_by,
2002: creation_Date ,
2017: l_login_id,
2018: status,
2019: run_date,
2020: plan_horizon,
2021: plan_run_id from msc_scenario_plans where scenario_id = p_src_scnId);
2022: end if;
2023: end copy_scn_plans;
2024:
2025: FUNCTION get_scn_users (p_scn_id in number) return varchar2 is
2048: function plan_scns_count(p_plan_id in number, p_scn_id in number, p_plan_run_id in number) return number is
2049: l_count number := 0;
2050:
2051: cursor c_plans is
2052: select count(*) from msc_scenario_plans
2053: where scenario_id <> p_scn_id and plan_id = p_plan_id and plan_run_id = p_plan_run_id;
2054:
2055: begin
2056: open c_plans;
2061: end;
2062:
2063: /* This procedure is called to archive a scenario.
2064: Logic
2065: Archive_Scn_Conc looks at all the plans to be archived (Archive_flag in msc_scenario_plans) and
2066: for every plan_id,
2067: generate plan_run_id and update plan_run_id field for it.
2068: call populate_Details api
2069: end for;
2075: procedure archive_scn_conc( errbuf out nocopy varchar2, retcode out nocopy varchar2 , p_scn_id in number) is
2076:
2077: cursor c_scn_plans is
2078: select plan_type,plan_id,run_date
2079: from msc_scenario_plans
2080: where scenario_id = p_scn_id and archive_flag = 'Y';
2081:
2082: l_plan_type number;
2083: l_plan_id number;
2108: l_login_id := FND_PROFILE.VALUE('LOGIN_ID');
2109:
2110: select msc_scn_scenarios_s.nextval into l_new_scn_id from dual;
2111:
2112: -- Insert rows into msc_scenarios,msc_scenario_plans,msc_scenario_users;
2113:
2114: insert into msc_scenarios(scenario_id,
2115: scenario_name,
2116: created_by,
2142: valid_to,
2143: null
2144: from msc_scenarios where scenario_id = p_scn_id);
2145:
2146: insert into msc_scenario_plans (scenario_id,
2147: plan_type,
2148: plan_id,
2149: created_by,
2150: creation_Date ,
2165: l_login_id,
2166: status,
2167: run_date,
2168: plan_horizon,
2169: plan_run_id from msc_scenario_plans where scenario_id = p_scn_id);
2170:
2171: insert into msc_scenario_users (scenario_id,
2172: user_id,
2173: created_by,
2207: --null; -- errored out
2208: msc_util.msc_debug('Archive Plan Failed for Plan:'||get_plan_name(l_plan_type,l_plan_id,l_plan_run_id));
2209: raise exc_error_plan_arch;
2210: end if;
2211: update msc_scenario_plans set plan_run_id = l_plan_run_id where
2212: scenario_id = p_scn_id and plan_id = l_plan_id and plan_type = l_plan_type;
2213:
2214: commit;
2215:
2233: procedure purge_scn_conc( errbuf out nocopy varchar2, retcode out nocopy varchar2 , p_scn_id in number) is
2234:
2235: cursor c_scn_plans is
2236: select plan_type,plan_id,plan_run_id
2237: from msc_scenario_plans
2238: where scenario_id = p_scn_id and purge_flag = 'Y' and plan_run_id is not null;
2239:
2240: cursor c_scn_version is
2241: select scn_version
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;
2321: --null; -- errored out
2322: raise exc_error_purge_plan;
2323: end if;
2324:
2325: delete from msc_scenario_plans where plan_id = p_plan_id and plan_type = p_plan_type;
2326: commit;
2327: EXCEPTION
2328: when exc_error_purge_plan then
2329: retcode := 2;
2403: manual activities and plans in that scenario.
2404:
2405: We will look into two tables i.e
2406: MSC_SCENARIO_ACTIVITIES
2407: MSC_SCENARIO_PLANS
2408: Logic for deriving Status is as follows:
2409:
2410: Scenario status When this condition is satisfied
2411: --------------- --------------------------------