[Home] [Help]
PACKAGE BODY: APPS.MSD_DP_SCENARIO_PKG
Source
1 PACKAGE BODY msd_dp_scenario_pkg AS
2 /* $Header: msddpscb.pls 120.1 2006/03/31 07:13:26 brampall noship $ */
3
4 /* Public Procedures */
5
6 PROCEDURE LOAD_ROW(P_DEMAND_PLAN_NAME in varchar2,
7 P_SCENARIO_NAME in varchar2,
8 P_OWNER in varchar2,
9 P_DESCRIPTION in varchar2,
10 P_OUTPUT_PERIOD_TYPE in varchar2,
11 P_HORIZON_START_DATE in varchar2,
12 P_HORIZON_END_DATE in varchar2,
13 P_FORECAST_DATE_USED in varchar2,
14 P_FORECAST_BASED_ON in varchar2,
15 P_SCENARIO_TYPE in varchar2,
16 P_STATUS in varchar2,
17 P_HISTORY_START_DATE in varchar2,
18 P_HISTORY_END_DATE in varchar2,
19 P_PUBLISH_FLAG in varchar2,
20 P_ENABLE_FLAG in varchar2,
21 P_PRICE_LIST_NAME in varchar2,
22 P_LAST_REVISION in varchar2,
23 P_PARAMETER_NAME in varchar2,
24 P_CONSUME_FLAG in varchar2,
25 P_ERROR_TYPE in varchar2,
26 P_DELETEABLE_FLAG in varchar2,
27 P_LAST_UPDATE_DATE in varchar2,
28 P_SUPPLY_PLAN_FLAG in varchar2,
29 P_ENABLE_NONSEED_FLAG in VARCHAR2,
30 P_SCENARIO_DESIGNATOR in VARCHAR2,
31 P_CUSTOM_MODE in VARCHAR2,
32 P_SC_TYPE in VARCHAR2,
33 P_ASSOCIATE_PARAMETER in VARCHAR2
34 )
35 is
36
37 l_demand_plan_id number;
38 l_scenario_id number;
39 f_luby number; -- entity owner in file
40 f_ludate date; -- entity update date in file
41 db_luby number; -- entity owner in db
42 db_ludate date; -- entity update date in db
43
44 BEGIN
45
46 -- translate values to IDs
47 select DEMAND_PLAN_ID
48 into l_demand_plan_id
49 from MSD_DEMAND_PLANS
50 where DEMAND_PLAN_NAME = P_DEMAND_PLAN_NAME;
51
52
53 -- Translate owner to file_last_updated_by
54 f_luby := fnd_load_util.owner_id(P_OWNER);
55
56 -- Translate char last_update_date to date
57 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
58 begin
59 select LAST_UPDATED_BY, LAST_UPDATE_DATE
60 into db_luby, db_ludate
61 from msd_dp_scenarios
62 where DEMAND_PLAN_ID = l_demand_plan_id
63 and SCENARIO_NAME = P_SCENARIO_NAME;
64
65 -- Test for customization and version
66 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
67 db_ludate, P_CUSTOM_MODE)) then
68 -- Update existing row
69 update msd_dp_scenarios
70 set supply_plan_flag = p_supply_plan_flag,
71 output_period_type = p_output_period_type
72 ,description = p_description
73 ,horizon_start_date = to_date(p_horizon_start_date, 'YYYY/MM/DD')
74 ,horizon_end_date = to_date(p_horizon_end_date, 'YYYY/MM/DD')
75 ,forecast_date_used = p_forecast_date_used
76 ,forecast_based_on = p_forecast_based_on
77 ,scenario_type = p_scenario_type
78 ,status = p_status
79 ,history_start_date = to_date(p_history_start_date, 'YYYY/MM/DD')
80 ,history_end_date = to_date(p_history_end_date, 'YYYY/MM/DD')
81 ,publish_flag = p_publish_flag
82 ,enable_flag = p_enable_flag
83 ,price_list_name = p_price_list_name
84 ,last_revision = p_last_revision
85 ,parameter_name = parameter_name
86 ,consume_flag = p_consume_flag
87 ,error_type = p_error_type ,
88 last_update_date = f_ludate ,
89 deleteable_flag = P_DELETEABLE_FLAG ,
90 enable_nonseed_flag = P_ENABLE_NONSEED_FLAG ,
91 scenario_designator = p_scenario_designator,
92 last_updated_by = f_luby ,
93 last_update_login = 0,
94 sc_type = P_SC_TYPE,
95 associate_parameter = P_ASSOCIATE_PARAMETER
96 where DEMAND_PLAN_ID = l_demand_plan_id
97 and SCENARIO_NAME = P_SCENARIO_NAME;
98
99 begin
100
101 select SCENARIO_ID
102 into l_scenario_id
103 from msd_dp_scenarios
104 where demand_plan_id = l_demand_Plan_id
105 and scenario_name = p_scenario_name;
106
107 -- Update existing row
108 update msd_dp_scenarios_tl
109 set description = p_description,
110 last_update_date = f_ludate,
111 last_updated_by = f_luby,
112 last_update_login = 0,
113 source_lang = userenv('LANG')
114 where DEMAND_PLAN_ID = l_demand_plan_id
115 and SCENARIO_ID = l_scenario_id
116 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
117
118 exception
119 when no_data_found then
120 -- Record doesn't exist - insert in all cases
121 insert into msd_dp_scenarios_tl
122 (
123 demand_plan_id,
124 scenario_id,
125 description,
126 language,
127 source_lang,
128 created_by,
129 creation_date,
130 last_updated_by,
131 last_update_date ,
132 last_update_login
133 )
134 Select
135 l_demand_plan_id,
136 l_scenario_id,
137 p_description,
138 l.language_code,
139 userenv('LANG'),
140 f_luby,
141 f_ludate,
142 f_luby,
143 f_ludate,
144 0
145 from fnd_languages l
146 where l.installed_flag in ('I','B');
147
148 end;
149
150 end if;
151 exception
152 when no_data_found then
153
154 select msd_dp_scenarios_s.nextval into l_scenario_id from dual;
155
156 -- Record doesn't exist - insert in all cases
157 insert into msd_dp_scenarios
158 (DEMAND_PLAN_ID
159 ,SCENARIO_ID
160 ,SCENARIO_NAME
161 ,DESCRIPTION
162 ,OUTPUT_PERIOD_TYPE
163 ,HORIZON_START_DATE
164 ,HORIZON_END_DATE
165 ,FORECAST_DATE_USED
166 ,FORECAST_BASED_ON
167 ,SCENARIO_TYPE
168 ,STATUS
169 ,HISTORY_START_DATE
170 ,HISTORY_END_DATE
171 ,PUBLISH_FLAG
172 ,ENABLE_FLAG
173 ,PRICE_LIST_NAME
174 ,LAST_REVISION
175 ,PARAMETER_NAME
176 ,CONSUME_FLAG
177 ,ERROR_TYPE ,
178 SUPPLY_PLAN_FLAG ,
179 CREATION_DATE ,
180 CREATED_BY ,
181 LAST_UPDATE_DATE ,
182 LAST_UPDATED_BY ,
183 LAST_UPDATE_LOGIN ,
184 ENABLE_NONSEED_FLAG ,
185 SCENARIO_DESIGNATOR ,
186 DELETEABLE_FLAG,
187 SC_TYPE,
188 ASSOCIATE_PARAMETER
189 )
190 values
191 (l_demand_plan_id
192 ,l_scenario_id
193 ,P_SCENARIO_NAME
194 ,P_DESCRIPTION
195 ,P_OUTPUT_PERIOD_TYPE
196 ,to_date(P_HORIZON_START_DATE, 'YYYY/MM/DD')
197 ,to_date(P_HORIZON_END_DATE, 'YYYY/MM/DD')
198 ,P_FORECAST_DATE_USED
199 ,P_FORECAST_BASED_ON
200 ,P_SCENARIO_TYPE
201 ,P_STATUS
202 ,to_date(P_HISTORY_START_DATE, 'YYYY/MM/DD')
203 ,to_date(P_HISTORY_END_DATE, 'YYYY/MM/DD')
204 ,P_PUBLISH_FLAG
205 ,P_ENABLE_FLAG
206 ,P_PRICE_LIST_NAME
207 ,P_LAST_REVISION
208 ,P_PARAMETER_NAME
209 ,P_CONSUME_FLAG
210 ,P_ERROR_TYPE ,
211 P_SUPPLY_PLAN_FLAG ,
212 f_ludate ,
213 f_luby ,
214 f_ludate ,
215 f_luby ,
216 0 ,
217 P_ENABLE_NONSEED_FLAG ,
218 P_SCENARIO_DESIGNATOR ,
219 P_DELETEABLE_FLAG,
220 P_SC_TYPE,
221 P_ASSOCIATE_PARAMETER
222 );
223
224 insert into msd_dp_scenarios_tl
225 (
226 demand_plan_id,
227 scenario_id,
228 description,
229 language,
230 source_lang,
231 created_by,
232 creation_date,
233 last_updated_by,
234 last_update_date ,
235 last_update_login
236 )
237 Select
238 l_demand_plan_id,
239 l_scenario_id,
240 p_description,
241 l.language_code,
242 userenv('LANG'),
243 f_luby,
244 f_ludate,
245 f_luby,
246 f_ludate,
247 0
248 from fnd_languages l
249 where l.installed_flag in ('I','B');
250
251 end;
252
253 END;
254
255 PROCEDURE TRANSLATE_ROW(P_DEMAND_PLAN_NAME in varchar2,
256 P_SCENARIO_NAME in varchar2,
257 P_DESCRIPTION in varchar2,
258 P_OWNER in varchar2)
259
260 IS
261
262 f_luby number; -- entity owner in file
263
264 BEGIN
265
266 -- Translate owner to file_last_updated_by
267 f_luby := fnd_load_util.owner_id(P_OWNER);
268
269
270 update msd_dp_scenarios_tl set
271 description = p_description,
272 LAST_UPDATE_DATE = sysdate,
273 LAST_UPDATED_BY = f_luby,
274 LAST_UPDATE_LOGIN = 0,
275 SOURCE_LANG = userenv('LANG')
276 where
277 demand_plan_id = (select demand_plan_id from msd_demand_plans where demand_plan_name = p_demand_plan_name)
278 and scenario_id = (select scenario_id from msd_dp_scenarios where scenario_name = p_scenario_name
279 and demand_plan_id = (select demand_plan_id from msd_demand_plans where demand_plan_name = p_demand_plan_name))
280 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
281 END;
282
283 Procedure ADD_LANGUAGE
284 is
285 begin
286 delete from MSD_DP_SCENARIOS_TL T
287 where not exists
288 (select NULL
289 from MSD_DP_SCENARIOS B
290 where B.DEMAND_PLAN_ID = T.DEMAND_PLAN_ID
291 and B.SCENARIO_ID = T.SCENARIO_ID
292 );
293
294 update MSD_DP_SCENARIOS_TL T set (
295 DESCRIPTION
296 ) = (select
297 B.DESCRIPTION
298 from MSD_DP_SCENARIOS_TL B
299 where B.DEMAND_PLAN_ID = T.DEMAND_PLAN_ID
300 and B.SCENARIO_ID = T.SCENARIO_ID
301 and B.LANGUAGE = T.SOURCE_LANG)
302 where (
303 T.DEMAND_PLAN_ID,
304 T.LANGUAGE
305 ) in (select
306 SUBT.DEMAND_PLAN_ID,
307 SUBT.LANGUAGE
308 from MSD_DP_SCENARIOS_TL SUBB, MSD_DP_SCENARIOS_TL SUBT
309 where SUBB.DEMAND_PLAN_ID = SUBT.DEMAND_PLAN_ID
310 and SUBB.SCENARIO_ID = SUBT.SCENARIO_ID
311 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
312 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
313 ));
314
315 insert into MSD_DP_SCENARIOS_TL (
316 DEMAND_PLAN_ID,
317 SCENARIO_ID,
318 DESCRIPTION,
319 CREATION_DATE,
320 CREATED_BY,
321 LAST_UPDATE_DATE,
322 LAST_UPDATED_BY,
323 LAST_UPDATE_LOGIN,
324 REQUEST_ID,
325 PROGRAM_APPLICATION_ID,
326 PROGRAM_ID,
327 PROGRAM_UPDATE_DATE,
328 LANGUAGE,
329 SOURCE_LANG
330 ) select
331 B.DEMAND_PLAN_ID,
332 B.SCENARIO_ID,
333 B.DESCRIPTION,
334 B.CREATION_DATE,
335 B.CREATED_BY,
336 B.LAST_UPDATE_DATE,
337 B.LAST_UPDATED_BY,
338 B.LAST_UPDATE_LOGIN,
339 B.REQUEST_ID,
340 B.PROGRAM_APPLICATION_ID,
341 B.PROGRAM_ID,
342 B.PROGRAM_UPDATE_DATE,
343 L.LANGUAGE_CODE,
344 B.SOURCE_LANG
345 from MSD_DP_SCENARIOS_TL B, FND_LANGUAGES L
346 where L.INSTALLED_FLAG in ('I', 'B')
347 and B.LANGUAGE = userenv('LANG')
348 and not exists
349 (select NULL
350 from MSD_DP_SCENARIOS_TL T
351 where T.DEMAND_PLAN_ID = B.DEMAND_PLAN_ID
352 and T.SCENARIO_ID = B.SCENARIO_ID
353 and T.LANGUAGE = L.LANGUAGE_CODE);
354
355 End ADD_LANGUAGE;
356
357
358 END msd_dp_scenario_pkg ;