1 PACKAGE BODY MSD_WS_DEM_RENAME_FORECAST AS
2 /* $Header: MSDWDRFB.pls 120.10.12010000.1 2008/05/01 18:20:12 appldev ship $ */
3
4
5 /*** PROCEDURES & FUNCTIONS ***
6 * ASSIGN_PLAN_NAME_TO_FORECAST
7 * ASSIGN_PLAN_NAME_TO_FORECAST_C
8 * GET_PLAN_SCENARIO_MEMBER_ID
9 */
10
11 /*
12 * Procedure Name - ASSIGN_PLAN_NAME_TO_FORECAST
13 * This procedure assigns a user specified name to the forecast
14 * uploaded from Demantra into the MSD_DP_SCN_ENTRIES_DENORM table.
15 *
16 * Parameters -
17 * NewPlanName - Name to be assigned to the recently exported
18 * forecast output.
19 * DataProfileName - Name of the Data Profile used to export
20 * forecast out of Demantra
21 *
22 * Given the parameter NewPlanName, create (or replace) an entry in the table MSD_DP_SCENARIOS.
23 * Demand Plan Id - (Hardcoded to) 5555555
24 * Scenario Id - Sequence starting from 8888888
25 *
26 * Using the DataProfileName, populate the table MSD_DP_SCENARIO_OUTPUT_LEVELS with
27 * the levels at which the forecast has been exported.
28 *
29 * Update the scenario id in the MSD_DP_SCN_ENTRIES_DENORM table to the Scenario Id generated
30 * for the given Plan Name.
31 *
32 * Return Values -
33 * The procedure returns a status. The possible return statuses are:
34 * SUCCESS, ERROR, INVALID_DATA_PROFILE
35 *
36 */
37 PROCEDURE ASSIGN_PLAN_NAME_TO_FORECAST (
38 status OUT NOCOPY VARCHAR2,
39 NewPlanName IN VARCHAR2,
40 DataProfileName IN VARCHAR2,
41 ArchiveFlag IN NUMBER)
42 IS
43
44 TYPE CUR_TYPE IS REF CURSOR;
45 x_cur_type CUR_TYPE;
46
47 x_sql_stmt VARCHAR2(2000) := NULL;
48
49 x_new_plan_name VARCHAR2(45) := NULL;
50 x_data_profile_name VARCHAR2(200) := NULL;
51
52 x_archive_flag NUMBER := NULL;
53 x_data_profile_id NUMBER := NULL;
54 x_scenario_id NUMBER := NULL;
55
56 /* For Planning Hub */
57 x_errbuf VARCHAR2(1000) := NULL;
58 x_retcode VARCHAR2(1000) := NULL;
59 x_plan_run_id NUMBER := NULL;
60
61 BEGIN
62
63
64 x_new_plan_name := substr(NewPlanName, 1, 45);
65 x_data_profile_name := DataProfileName;
66 x_archive_flag := ArchiveFlag;
67
68
69 /* Check if the Data Profile Name specified is present inside Demantra or not */
70 x_sql_stmt := 'SELECT nvl(sum(tq.id), 0) '
71 || ' FROM msd_dem_transfer_query tq '
72 || ' WHERE tq.query_name = ''' || x_data_profile_name || ''''
73 || ' AND msd_dem_upload_forecast.is_valid_scenario(tq.id) = 1 ';
74 EXECUTE IMMEDIATE x_sql_stmt INTO x_data_profile_id;
75
76
77 IF (x_data_profile_id <> 0)
78 THEN
79
80
81 /* Check if the given New Plan Name already exists */
82 x_sql_stmt := 'SELECT nvl(sum(scenario_id), 0) '
83 || ' FROM msd_dp_scenarios mds '
84 || ' WHERE mds.demand_plan_id = ' || MSD_DEM_UPLOAD_FORECAST.C_DEMAND_PLAN_ID
85 || ' AND mds.scenario_name = ''' || x_new_plan_name || '''';
86 EXECUTE IMMEDIATE x_sql_stmt INTO x_scenario_id;
87
88
89 /* Create/Update an entry for the Plan Name in the table msd_dp_scenarios */
90 IF (x_scenario_id = 0) THEN
91
92 SELECT MSD_DP_SCENARIOS_S.nextval
93 INTO x_scenario_id
94 FROM DUAL;
95 x_scenario_id := x_scenario_id + C_DUMMY_SCENARIO_ID_OFFSET;
96
97 INSERT INTO msd_dp_scenarios (
98 demand_plan_id,
99 scenario_id,
100 scenario_name,
101 forecast_based_on,
102 sc_type,
103 error_type,
104 associate_parameter,
105 last_update_date,
106 last_updated_by,
107 creation_date,
108 created_by )
109 VALUES (
110 MSD_DEM_UPLOAD_FORECAST.C_DEMAND_PLAN_ID,
111 x_scenario_id,
112 x_new_plan_name,
113 substr(x_data_profile_name,1,30),
114 msd_dem_upload_forecast.is_global_scenario(x_data_profile_id),
115 msd_dem_upload_forecast.get_error_type(x_data_profile_id),
116 C_ASSOCIATE_PARAMETER,
117 sysdate,
118 FND_GLOBAL.USER_ID,
119 sysdate,
120 FND_GLOBAL.USER_ID);
121
122 ELSE
123
124 UPDATE msd_dp_scenarios
125 SET
126 forecast_based_on = substr(x_data_profile_name,1,30),
127 sc_type = msd_dem_upload_forecast.is_global_scenario(x_data_profile_id),
128 error_type = msd_dem_upload_forecast.get_error_type(x_data_profile_id),
129 last_update_date = sysdate,
130 last_updated_by = FND_GLOBAL.USER_ID
131 WHERE scenario_id = x_scenario_id;
132
133 DELETE FROM msd_dp_scenario_output_levels
134 WHERE scenario_id = x_scenario_id;
135
136 DELETE FROM msd_dp_scn_entries_denorm
137 WHERE scenario_id = x_scenario_id;
138
139 END IF;
140
141
142 /* Populate output levels for the forecast data */
143 INSERT INTO msd_dp_scenario_output_levels (
144 demand_plan_id,
145 scenario_id,
146 level_id,
147 last_update_date,
148 last_updated_by,
149 creation_date,
150 created_by )
151 SELECT
152 MSD_DEM_UPLOAD_FORECAST.C_DEMAND_PLAN_ID,
153 x_scenario_id,
154 to_number(flv.lookup_code),
155 sysdate,
156 FND_GLOBAL.USER_ID,
157 sysdate,
158 FND_GLOBAL.USER_ID
159 FROM
160 msd_dem_transfer_query tq,
161 msd_dem_transfer_query_levels tql,
162 msd_dem_group_tables gt,
163 fnd_lookup_values_vl flv
164 WHERE
165 tq.id = x_data_profile_id
166 AND tql.id = tq.id
167 AND gt.group_table_id = tql.level_id
168 AND flv.lookup_type = 'MSD_DEM_LEVELS'
169 AND upper(flv.meaning) = upper(gt.table_label);
170
171
172 /* Update the scenario id in the denorm table */
173 UPDATE msd_dp_scn_entries_denorm
174 SET scenario_id = x_scenario_id
175 WHERE scenario_id = MSD_DEM_UPLOAD_FORECAST.C_SCENARIO_ID_OFFSET + x_data_profile_id;
176
177 ELSE
178 status := 'INVALID_DATA_PROFILE';
179 RETURN;
180 END IF;
181
182 COMMIT;
183 status := 'SUCCESS';
184
185
186 /* For Planning Hub */
187 msc_phub_pkg.populate_demantra_details (
188 x_errbuf,
189 x_retcode,
190 x_scenario_id,
191 x_plan_run_id,
192 x_archive_flag );
193
194 IF (x_retcode <> '0')
195 THEN
196 status := 'ERROR';
197 END IF;
198
199 EXCEPTION
200 WHEN OTHERS THEN
201 status := 'ERROR';
202 RETURN;
203
204
205 END ASSIGN_PLAN_NAME_TO_FORECAST;
206
207
208
209
210 PROCEDURE ASSIGN_PLAN_NAME_PUBLIC (
211 status OUT NOCOPY VARCHAR2,
212 UserName IN VARCHAR2,
213 RespName IN VARCHAR2,
214 RespApplName IN VARCHAR2,
215 SecurityGroupName IN VARCHAR2,
216 Language IN VARCHAR2,
217 NewPlanName IN VARCHAR2,
218 DataProfileName IN VARCHAR2,
219 ArchiveFlag IN NUMBER ) AS
220 userid number;
221 respid number;
222 l_String VARCHAR2(30);
223 error_tracking_num number;
224 l_SecutirtGroupId NUMBER;
225 BEGIN
226 error_tracking_num :=2010;
227 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
228 IF (l_String <> 'OK') THEN
229 Status := l_String;
230 RETURN;
231 END IF;
232
233 error_tracking_num :=2030;
234 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSD_DEM_WF_MGR', l_SecutirtGroupId);
235 IF (l_String <> 'OK') THEN
236 Status := l_String;
237 RETURN;
238 END IF;
239 error_tracking_num :=2040;
240
241
242 ASSIGN_PLAN_NAME_TO_FORECAST( Status,
243 NewPlanName,
244 DataProfileName,
245 ArchiveFlag );
246
247
248
249 EXCEPTION
250 WHEN others THEN
251 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
252
253 return;
254
255
256 END ASSIGN_PLAN_NAME_PUBLIC;
257
258 /*
259 * Procedure Name - ASSIGN_PLAN_NAME_TO_FORECAST_C
260 * This procedure calls the procedure ASSIGN_PLAN_NAME_TO_FORECAST
261 * This is used in the assign plan name concurrent program
262 */
263 PROCEDURE ASSIGN_PLAN_NAME_TO_FORECAST_C(
264 errbuf out NOCOPY varchar2,
265 retcode out NOCOPY varchar2,
266 NewPlanName IN VARCHAR2,
267 DataProfileName IN VARCHAR2,
268 ArchiveFlag IN NUMBER default 1 )
269 IS
270 x_status VARCHAR2(10) := NULL;
271 BEGIN
272 ASSIGN_PLAN_NAME_TO_FORECAST(x_status,NewPlanName,DataProfileName,ArchiveFlag);
273 if x_status <> 'SUCCESS' then
274 retcode := -1;
275 end if;
276 EXCEPTION
277 WHEN OTHERS THEN
278 retcode := -1;
279 RETURN;
280 END ASSIGN_PLAN_NAME_TO_FORECAST_C;
281
282
283 /*
284 * Function Name - GET_PLAN_SCENARIO_MEMBER_ID
285 * Given the id of a supply plan in ASCP, this function gets the plan scenario member id
286 * from Demantra.
287 *
288 * Parameters -
289 * PlanId - ID of the supply plan from the table MSC_PLANS.
290 *
291 * Return Values -
292 * The procedure returns the plan scenario member ID in Demantra. If not found or in case
293 * of any error it returns -1.
294 *
295 */
296 FUNCTION GET_PLAN_SCENARIO_MEMBER_ID (
297 PlanId IN NUMBER )
298 RETURN NUMBER
299 IS
300
301 x_member_id NUMBER := NULL;
302
303 BEGIN
304
305 EXECUTE IMMEDIATE ' SELECT supply_plan_id '
306 || ' FROM ' || fnd_profile.value('MSD_DEM_SCHEMA') || '.supply_plan sp'
307 || ' WHERE sp.plan_id = ' || PlanId
308 INTO x_member_id;
309
310 RETURN x_member_id;
311
312 EXCEPTION
313 WHEN OTHERS THEN
314 RETURN -1;
315
316 END GET_PLAN_SCENARIO_MEMBER_ID;
317
318 /*
319 * Procedure Name - REFRESH_MVIEW
320 * Given the name of a materialized view, this procedure refreshed the mview
321 *
322 * Parameters -
323 * MviewName - Name of the materialized view
324 *
325 */
326 PROCEDURE REFRESH_MVIEW(
327 mviewname IN VARCHAR2)
328 IS
329
330 x_small_sql VARCHAR2(200) := NULL;
331 x_schema VARCHAR2(30) := NULL;
332
333 BEGIN
334
335 /* Alter session to APPS */
336 x_schema := apps.fnd_profile.VALUE('MSD_DEM_SCHEMA');
337 x_small_sql := 'alter session set current_schema = APPS';
338 EXECUTE IMMEDIATE x_small_sql;
339
340 /*Refresh the mview */
341 dbms_mview.refresh(upper(mviewname), 'C');
342
343 /* Alter session to demantra schema */
344 x_small_sql := 'alter session set current_schema = ' || x_schema;
345 EXECUTE IMMEDIATE x_small_sql;
346
347 EXCEPTION
348 WHEN others THEN
349 x_small_sql := 'alter session set current_schema = ' || x_schema;
350 EXECUTE IMMEDIATE x_small_sql;
351
352 END REFRESH_MVIEW;
353 /*
354 * Procedure Name - DROP_MVIEW
355 * Given the name of a materialized view, this procedure drops the mview
356 *
357 * Parameters -
358 * MviewName - Name of the materialized view
359 *
360 */
361 PROCEDURE DROP_MVIEW(
362 mviewname IN VARCHAR2)
363 IS
364
365 x_small_sql VARCHAR2(200) := NULL;
366 x_schema VARCHAR2(30) := NULL;
367 x_mview VARCHAR2(30) := NULL;
368 x_drop_mview_sql VARCHAR2(300) := NULL;
369
370 BEGIN
371
372 x_schema := apps.fnd_profile.VALUE('MSD_DEM_SCHEMA');
373
374 /*Check if the Materialized view exists*/
375 SELECT object_name into x_mview
376 FROM dba_objects
377 WHERE owner = x_schema
378 AND object_type = 'MATERIALIZED VIEW'
379 AND object_name = upper(mviewname)
380 ORDER BY created DESC;
381 /*If the meview is present, drop it*/
382 IF (x_mview IS NOT NULL)
383 THEN
384 x_drop_mview_sql := 'DROP MATERIALIZED VIEW '||x_schema||'.'||mviewname;
385 EXECUTE IMMEDIATE x_drop_mview_sql;
386 END IF;
387
388
389
390 EXCEPTION
391 WHEN others THEN
392 x_small_sql := 'alter session set current_schema = ' || x_schema;
393 EXECUTE IMMEDIATE x_small_sql;
394 END DROP_MVIEW;
395
396
397 END MSD_WS_DEM_RENAME_FORECAST;
[Home] [Help]