[Home] [Help]
PACKAGE BODY: APPS.MSD_DP_SCENARIO_OUTPUT_LVL_PKG
Source
1 PACKAGE BODY msd_dp_scenario_output_lvl_pkg AS
2 /* $Header: msddpsolb.pls 120.0 2005/05/25 19:54:25 appldev noship $ */
3
4 /* Public Procedures */
5
6 PROCEDURE LOAD_ROW(P_DEMAND_PLAN_NAME in varchar2,
7 P_SCENARIO_NAME in varchar2,
8 P_LEVEL_ID in varchar2,
9 P_OWNER in varchar2,
10 P_DELETEABLE_FLAG in varchar2,
11 P_LAST_UPDATE_DATE in varchar2,
12 P_ENABLE_NONSEED_FLAG in VARCHAR2,
13 P_CUSTOM_MODE in VARCHAR2
14 )
15 is
16
17 l_demand_plan_id number;
18 l_scenario_id number;
19 f_luby number; -- entity owner in file
20 f_ludate date; -- entity update date in file
21 db_luby number; -- entity owner in db
22 db_ludate date; -- entity update date in db
23
24 BEGIN
25
26 -- translate values to IDs
27 select DEMAND_PLAN_ID
28 into l_demand_plan_id
29 from MSD_DEMAND_PLANS
30 where DEMAND_PLAN_NAME = P_DEMAND_PLAN_NAME;
31
32 select SCENARIO_ID
33 into l_scenario_id
34 from MSD_DP_SCENARIOS
35 where DEMAND_PLAN_ID = l_demand_plan_id
36 and SCENARIO_NAME = P_SCENARIO_NAME;
37
38
39 -- Translate owner to file_last_updated_by
40 f_luby := fnd_load_util.owner_id(P_OWNER);
41
42 -- Translate char last_update_date to date
43 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
44 begin
45 select LAST_UPDATED_BY, LAST_UPDATE_DATE
46 into db_luby, db_ludate
47 from msd_dp_scenario_output_levels
48 where demand_plan_id = l_demand_plan_id
49 and scenario_id = l_scenario_id
50 and level_id = p_level_id;
51
52 -- Test for customization and version
53 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
54 db_ludate, P_CUSTOM_MODE)) then
55 -- Update existing row
56 update msd_dp_scenario_output_levels
57 set last_update_date = f_ludate ,
58 deleteable_flag = P_DELETEABLE_FLAG ,
59 enable_nonseed_flag = P_ENABLE_NONSEED_FLAG ,
60 last_updated_by = f_luby ,
61 last_update_login = 0
62 where demand_plan_id = l_demand_plan_id
63 and scenario_id = l_scenario_id
64 and level_id = p_level_id;
65
66 end if;
67 exception
68 when no_data_found then
69 -- Record doesn't exist - insert in all cases
70 insert into msd_dp_scenario_output_levels
71 (DEMAND_PLAN_ID ,
72 SCENARIO_ID ,
73 LEVEL_ID ,
74 CREATION_DATE ,
75 CREATED_BY ,
76 LAST_UPDATE_DATE ,
77 LAST_UPDATED_BY ,
78 LAST_UPDATE_LOGIN ,
79 ENABLE_NONSEED_FLAG ,
80 DELETEABLE_FLAG
81 )
82 values
83 (l_demand_plan_id ,
84 l_scenario_id ,
85 P_LEVEL_ID ,
86 f_ludate ,
87 f_luby ,
88 f_ludate ,
89 f_luby ,
90 0 ,
91 P_ENABLE_NONSEED_FLAG ,
92 P_DELETEABLE_FLAG
93 );
94 end;
95
96 END;
97
98 END msd_dp_scenario_output_lvl_pkg ;