1 PACKAGE MSD_DEM_UPLOAD_FORECAST AUTHID DEFINER AS
2 /* $Header: msddemufs.pls 120.16.12020000.2 2012/10/18 07:11:09 nallkuma ship $ */
3
4 /*** CONSTANTS ***/
5
6 C_IMPORT_DATA_PROFILE NUMBER := 1;
7 C_EXPORT_DATA_PROFILE NUMBER := 2;
8 C_IMPORT_EXPORT_DATA_PROFILE NUMBER := 3;
9
10 C_DEMAND_PLAN_ID NUMBER := 5555555;
11 C_SCENARIO_ID_OFFSET NUMBER := 5555555;
12
13 C_BUCKET_TYPE_DAY NUMBER := 1;
14 C_BUCKET_TYPE_WEEK NUMBER := 2;
15 C_BUCKET_TYPE_MONTH NUMBER := 3;
16
17 C_PSNT_TYPE_CODE NUMBER := 1;
18 C_PSNT_TYPE_DESC NUMBER := 0;
19
20 C_EXPORT_TYPE_FULL NUMBER := 1;
21 C_EXPORT_TYPE_INCR NUMBER := 2;
22
23 C_TIME_LEVEL_DAY NUMBER := 7;
24 C_TIME_LEVEL_WEEK NUMBER := 6;
25 C_TIME_LEVEL_MONTH NUMBER := 5;
26
27
28 C_ITEM VARCHAR2(50) := 'ITEM';
29 C_PRODUCT_FAMILY VARCHAR2(50) := 'PRODUCT FAMILY';
30 C_ORGANIZATION VARCHAR2(50) := 'ORGANIZATION';
31 C_SITE VARCHAR2(50) := 'SITE';
32 C_CUSTOMER VARCHAR2(50) := 'ACCOUNT';
33 C_CUSTOMER_ZONE VARCHAR2(50) := 'TRADING PARTER ZONE';
34 C_ZONE VARCHAR2(50) := 'ZONE';
35 C_DEMAND_CLASS VARCHAR2(50) := 'DEMAND CLASS';
36 C_PARENT_ITEM VARCHAR2(50) := 'PARENT ITEM';
37 C_ASSET_GROUP VARCHAR2(50) := 'ASSET_GROUP';
38 C_CLASS_CODE VARCHAR2(50) := 'CLASS_CODE';
39 C_ITEM_TYPE VARCHAR2(50) := 'ITEM_TYPE';
40 C_SPF_VISIT_TYPE VARCHAR2(50) := 'SPF_VISIT_TYPE';
41 C_SPF_VISIT_STAGE_TYPE VARCHAR2(50) := 'SPF_VISIT_STAGE_TYPE';
42 C_MASTER_ITEM VARCHAR2(50) := 'MASTER_ITEM';
43 C_SPF_MAINTENANCE_TYPE VARCHAR2(50) := 'SPF_MAINTENANCE_TYPE';
44 C_ASSET_GROUP_ATTRIBUTE_2 VARCHAR2(50) := 'ASSET_GROUP_ATTRIBUTE_2';
45 C_ASSET_GROUP_ATTRIBUTE_1 VARCHAR2(50) := 'ASSET_GROUP_ATTRIBUTE_1';
46 C_CTO_BASE_MODEL VARCHAR2(50) := 'CTO_BASE_MODEL';
47
48 /* YES/NO */
49 C_YES NUMBER := 1;
50 C_NO NUMBER := 2;
51 C_ROUNDOFF_PLACES NUMBER := 6;
52
53 C_FORECAST_SERIES_PREFIX VARCHAR2(10) := 'FCST_';
54 C_FCST_ACRY_SERIES_PREFIX VARCHAR2(10) := 'ACRY_';
55 C_DEMAND_PRTY_SERIES_PREFIX VARCHAR2(10) := 'PRTY_';
56 C_DKEY_SERIES_PREFIX VARCHAR2(10) := 'DKEY_';
57
58 C_DKEY_ITEM VARCHAR2(30) := 'ITEM';
59 C_DKEY_SITE VARCHAR2(30) := 'SITE';
60 C_DKEY_ORG VARCHAR2(30) := 'ORG';
61
62
63
64 /*** FUNCTIONS ***
65 * GET_SR_INSTANCE_ID_FOR_GLOBAL
66 * IS_VALID_SCENARIO
67 * GET_SR_INSTANCE_ID_FOR_PROFILE
68 * GET_ERROR_TYPE
69 * IS_GLOBAL_SCENARIO
70 * GET_CUSTOMER_FROM_TPZONE
71 * GET_ZONE_FROM_TPZONE
72 * CHECK_UPLOAD_LAST_RUN
73 */
74
75 /*
76 * This function returns the sr_instance_id to be used for a global forecast
77 */
78 FUNCTION GET_SR_INSTANCE_ID_FOR_GLOBAL
79 RETURN NUMBER;
80
81 /* This function returns 1 if the data profile is fit for upload to ASCP
82 * Current check only includes that a forecast series with internal name
83 * starting 'FCST_' must be present.
84 */
85 FUNCTION IS_VALID_SCENARIO (
86 p_data_profile_id IN NUMBER)
87 RETURN NUMBER;
88
89 FUNCTION UPLOAD_TO_CP (
90 p_data_profile_id IN NUMBER)
91 RETURN NUMBER;
92
93 /* This function returns -23453 if the data profile contains non-global
94 * forecast, else it returns the id of the source instance for which
95 * global forecasting is being done.
96 */
97 FUNCTION GET_SR_INSTANCE_ID_FOR_PROFILE (
98 p_data_profile_id IN NUMBER)
99 RETURN NUMBER;
100
101 /* This function gets the error type 'MAD' or 'MAPE' given the data
102 * profile id
103 */
104 FUNCTION GET_ERROR_TYPE (
105 p_data_profile_id IN NUMBER)
106 RETURN VARCHAR2;
107
108 /* This function return 'Y' if the data profile contains global forecast
109 * else returns 'N'.
110 */
111 FUNCTION IS_GLOBAL_SCENARIO (
112 p_data_profile_id IN NUMBER)
113 RETURN VARCHAR2;
114
115 /* This function returns the source key of the customer, given the customer
116 * zone
117 */
118 FUNCTION GET_CUSTOMER_FROM_TPZONE (
119 p_tp_zone IN VARCHAR2,
120 p_sr_instance_id IN NUMBER)
121 RETURN NUMBER;
122
123 /* This function returns the source key of the zone, given the customer zone
124 */
125 FUNCTION GET_ZONE_FROM_TPZONE (
126 p_tp_zone IN VARCHAR2,
127 p_sr_instance_id IN NUMBER)
128 RETURN NUMBER;
129
130 /*
131 * This function will check, when did the last upload has run
132 * Arguments:
133 * 1. appl id of given forecast data profile
134 * Returns
135 * = '0' to launch SPF Upload Data workflow
136 * = '1' to launch SPF Incremental Upload workflow
137 * Logic
138 * Compares the timestamp of engine run and SPF Upload Data workflow
139 * IF engine_run timestamp is later than SPF Upload Data workflow
140 * THEN launch SPF Upload Data workflow
141 * ELSE launch SPF Incemental Upload workflow
142 */
143 FUNCTION CHECK_UPLOAD_LAST_RUN (
144 p_dp_final_forecast_appid IN VARCHAR2 )
145 RETURN NUMBER;
146
147 /*** PROCEDURES ***/
148 /*
149 * UPLOAD_FORECAST
150 * GET_TIME_STRINGS
151 * UPLOAD_PLANNING_PERCENTAGES
152 * UPLOAD_TOTAL_DEMAND
153 * UPLOAD_FORECAST_WITH_APP_ID
154 * UPLOAD_PLNG_PCTG_WITH_APP_ID
155 * UPLOAD_CTO_FCST_WITH_APP_ID
156 * UPLOAD_SPF_METRICS
157 * UPLOAD_SPF_INC_FORECAST
158 * WAIT_UNTIL_UPLOAD
159 */
160 /*
161 * This procedure, given the export integration interface name, pushes the
162 * forecast data along with forecast accuracy and demand priority from the
163 * export view to table MSD_DP_SCN_ENTRIES_DENORM. The member codes are
164 * transformed to the corresponding source identifiers. The 'Organization'
165 * level member is used to find out the source instance to which the record
166 * belongs.
167 * The internal names of the series will be used to get the semantic of the
168 * series. They are as follows -
169 * 1. Forecast Series - The internal name should start with 'FCST_'
170 * 2. Demand Priority Series - The internal name should start with 'PRTY_'
171 * 3. Forecast Accuracy Series - The internal name should start with 'ACRY_'
172 * 4. Destination Key Series - The internal name should start with 'DKEY_'
173 */
174 PROCEDURE UPLOAD_FORECAST (
175 p_export_data_profile IN VARCHAR2,
176 p_ind_fcst_series_iname IN VARCHAR2 DEFAULT NULL,
177 p_dep_fcst_series_iname IN VARCHAR2 DEFAULT NULL,
178 p_for_spf IN VARCHAR2 DEFAULT 2,
179 p_acry_series_iname IN VARCHAR2 DEFAULT NULL,
180 p_upload_unplanned_components IN VARCHAR2 DEFAULT 0 --adding for bug#13393529, upload ind fcst for unplanned items with pick_components_flag = 'Y'
181 );
182
183
184
185 PROCEDURE GET_TIME_STRINGS (
186 p_bucket_type OUT NOCOPY NUMBER,
187 p_start_time OUT NOCOPY VARCHAR2,
188 p_end_time OUT NOCOPY VARCHAR2,
189 p_res_type OUT NOCOPY NUMBER,
190 p_time_from_clause OUT NOCOPY VARCHAR2,
191 p_time_res IN NUMBER,
192 p_ppct_direct_flag IN VARCHAR2 DEFAULT 0 --adding for bug#13393529, flag to indicate call from upload_cto_plng_pct_direct procedure
193 );
194
195
196
197
198 /*
199 * This procedure export the planning percentages from Demantra to the table
200 * MSD_DP_PLANNING_PCT_DENORM table.
201 * The parameters are -
202 * p_pp_export_data_profile - Export data profile used to export planning
203 * percentages
204 * p_fcst_export_data_profile - Export data profile used to export total demand
205 * p_parent_item_series_iname - Internal Name of the series which holds parent
206 * item total demand
207 * p_option_item_series_iname - Internal Name of the series which holds the option
208 * item dependent demand
209 */
210 PROCEDURE UPLOAD_PLANNING_PERCENTAGES (
211 p_pp_export_data_profile IN VARCHAR2,
212 p_fcst_export_data_profile IN VARCHAR2,
213 p_pctg_series_iname IN VARCHAR2,
214 p_parent_item_series_iname IN VARCHAR2 DEFAULT NULL,
215 p_option_item_series_iname IN VARCHAR2 DEFAULT NULL );
216
217
218
219
220 /*
221 * This procedure exports total demand, forecast error and demand priority from Demantra
222 * to the table MSD_DP_SCN_ENTRIES_DENOM.
223 * The parameters are -
224 * p_ind_export_data_profile - Export Data Profile used to export independent demand
225 * p_dep_export_data_profile - Export Data Profile used to export dependent demand
226 * p_ind_fcst_series_iname - Internal Name of the series for independent demand
227 * p_dep_fcst_series_iname - Internal Name of the series for dependent demand
228 */
229 PROCEDURE UPLOAD_TOTAL_DEMAND (
230 p_ind_export_data_profile IN VARCHAR2,
231 p_dep_export_data_profile IN VARCHAR2,
232 p_ind_fcst_series_iname IN VARCHAR2,
233 p_dep_fcst_series_iname IN VARCHAR2,
234 p_upload_unplanned_components IN VARCHAR2 DEFAULT 0 --adding for bug#13393529, upload ind fcst for unplanned items with pick_components_flag = 'Y'
235 );
236
237
238
239
240 /*
241 * This procedure is a wrapper on top of existing procedure UPLOAD_FORECAST
242 * This procedure accepts Application_IDs as arguments instead of data profile names.
243 * The procedure gets the data profile names from Demantra and then calls UPLOAD FORECAST
244 * The parameters are -
245 * p_export_data_profile_wai - Application Id of the export data profile
246 * p_ind_fcst_series_wai - Application Id of the independent demand series
247 * p_dep_fcst_series_wai - Application Id of the dependent demand series
248 * p_member_id - Memeber Id of the SOP Sceanrio, Demantra 7.4 and higher
249 */
250 PROCEDURE UPLOAD_FORECAST_WITH_APP_ID (
251 p_export_data_profile_wai IN VARCHAR2,
252 p_ind_fcst_series_wai IN VARCHAR2 DEFAULT NULL,
253 p_dep_fcst_series_wai IN VARCHAR2 DEFAULT NULL,
254 p_upload_unplanned_components IN VARCHAR2 DEFAULT 0 --adding for bug#13393529, upload ind fcst for unplanned items with pick_components_flag = 'Y'
255 );
256
257
258
259
260 /*
261 * This procedure export the planning percentages from Demantra to a temporary table
262 * and then to the table MSD_DP_PLANNING_PCT_DENORM table.
263 * The export profile's view is not used but the internal tables created by transfer step are used.
264 * The parameters are -
265 * p_pp_export_data_profile - Export data profile used to export planning
266 * percentages
267 * p_fcst_export_data_profile - Export data profile used to export total demand
268 * p_parent_item_series_iname - Internal Name of the series which holds parent
269 * item total demand
270 * p_option_item_series_iname - Internal Name of the series which holds the option
271 * item dependent demand
272 */
273
274
275
276 PROCEDURE UPLOAD_CTO_PLNG_PCT_DIRECT (
277 p_pp_export_data_profile IN VARCHAR2,
278 p_fcst_export_data_profile IN VARCHAR2,
279 p_pctg_series_iname IN VARCHAR2,
280 p_parent_item_series_iname IN VARCHAR2 DEFAULT NULL,
281 p_option_item_series_iname IN VARCHAR2 DEFAULT NULL,
282 p_upload_pct_by_base_model IN VARCHAR2,
283 p_exclude_pto_model IN NUMBER );
284
285 /*
286 * This procedure is a wrapper on top of existing procedure UPLOAD_PLANNING_PERCENTAGES
287 * This procedure accepts Application_IDs as arguments instead of data profile names.
288 * The procedure gets the data profile names from Demantra and then calls UPLOAD_PLANNING_PERCENTAGES
289 * or UPLOAD_CTO_PLNG_PCT_DIRECT based on the flag (p_use_export_profile_view) specified.
290 * The parameters are -
291 * p_pp_export_data_profile_wai - Application Id of the export data profile used to export planning percentages
292 * p_fcst_export_data_profile_wai - Application Id of the export data profile used to export total demand
293 * p_pctg_series_wai - Application Id of the series which holds final planning percentages
294 * p_parent_item_series_wai - Application Id of the series which holds parent total demand
295 * p_option_item_series_wai - Application Id of the series which holds the option demand
296 * p_exclude_pto_model - flag to indicate whether to export by model or not
297 * p_use_export_profile_view - flag to indicate whether indicate direct export / export using data profile view
298 */
299
300
301 PROCEDURE UPLOAD_PLNG_PCTG_WITH_APP_ID (
302 p_pp_export_data_profile_wai IN VARCHAR2,
303 p_fcst_export_data_profile_wai IN VARCHAR2,
304 p_pctg_series_wai IN VARCHAR2,
305 p_parent_item_series_wai IN VARCHAR2 DEFAULT NULL,
306 p_option_item_series_wai IN VARCHAR2 DEFAULT NULL,
307 p_exclude_pto_model IN NUMBER DEFAULT 1,
308 p_use_export_profile_view IN VARCHAR2 DEFAULT 1
309 );
310
311
312 /*
313 * This procedure is a wraper on top of existing procedure UPLOAD_TOTAL_DEMAND
314 * This procedure accepts Application_IDs as arguments instead of data profile names.
315 * The procedure gets the data profile names from Demantra and then calls UPLOAD_TOTAL_DEMAND
316 * The parameters are -
317 * p_ind_export_data_profile_wai - Application Id of the export data profile used to export independent demand
318 * p_dep_export_data_profile_wai - Application Id of the export data profile used to export dependent demand
322 PROCEDURE UPLOAD_CTO_FCST_WITH_APP_ID (
319 * p_ind_fcst_series_wai - Application Id of the series which holds independent demand
320 * p_dep_fcst_series_wai - Application Id of the series which holds dependent demand
321 */
323 p_ind_export_data_profile_wai IN VARCHAR2,
324 p_dep_export_data_profile_wai IN VARCHAR2,
325 p_ind_fcst_series_wai IN VARCHAR2,
326 p_dep_fcst_series_wai IN VARCHAR2,
327 p_upload_unplanned_components IN VARCHAR2 DEFAULT 0 --adding for bug#13393529, upload ind fcst for unplanned items with pick_components_flag = 'Y'
328 );
329
330 /*
331 * This procedure will load data into MSC_DMD_SCN_METRICS from
332 * demantra's data profile - SPF Upload Metrics's view.
333 * Arguments -
334 * 1. SPF Upload Metrics data profile appl id
335 * 2. SPF Upload Final Forecast data profile appl id
336 * Fetches SPF Upload Metrics and SPF Upload Final Forecast
337 * export data profile info.
338 * Uses the 'SPF Upload Final Forecast' export profile id as
339 * the scenario id for loading data/metrics into MSC_DMD_SCN_METRICS
340 * Data is deleted first in the table against the scenario_id
341 * then re-loaded with the new data.
342 * Info about the loading of the data is logged in integ_status table.
343 */
344 PROCEDURE UPLOAD_SPF_METRICS (
345 p_dp_metrics_appid IN VARCHAR2,
346 p_dp_final_forecast_appid IN VARCHAR2 );
347
348
349 /*
350 * This procedure will identify the item/org tuples that have changed
351 * after the last upload (full or incremental) was done,
352 * then updates msd_dp_scn_entries_denorm (quantity column) and
353 * msc_dmd_scn_metrics (mape_in_sample,mape_out_sample,forecast_volatility,
354 * avg_demand columns) tables with new values in the 5 series
355 * (SPF Final Forecast, SPF Forecast MAPE (In Sample), SPF Forecast MAPE (Out of Sample),
356 * SPF Forecast Volatility, SPF Average Demand)
357 * Arguments -
358 * 1. SPF Upload Final Forecast data profile appl id
359 * 2. SPF Upload Metrics data profile appl id
360 * 3. SPF Final Forecast series appl id
361 */
362 PROCEDURE UPLOAD_SPF_INC_FORECAST (
363 p_dp_final_forecast_appid IN VARCHAR2,
364 p_dp_metrics_appid IN VARCHAR2,
365 p_ff_series_appid IN VARCHAR2 DEFAULT NULL);
366
367
368 /*
369 * This procedure will keep the last launced workflow in WAIT state
370 * till the previously launched workflow completes.
371 * Arguments:
372 * 1. p_wf_appid - workflow application id
373 * 2. p_wf_stepid - workflow's wait step id (internal)
374 * Logic
375 * Fetches the status of the previously launched workflow instance
376 * IF Running then sleep for 1 to 3 minutes and run the loop
377 * IF NOT Running then exit loop/procedure, hence releasing the
378 * newly launched workflow from 'wait' to 'running' state.
379 */
380 PROCEDURE WAIT_UNTIL_UPLOAD (
381 p_wf_appid IN VARCHAR2,
382 p_wf_stepid IN VARCHAR2 );
383
384
385 /*
386 * This procedure, will upload cmro planning factors to source table
387 * Actions performed.
388 * 1. Launch workflow, Default:"Export SPF Planning factors"
389 * 2. Get column names for levels selected in DP, Default:"SPF PLANNING FACTORS"
390 * 3. Delete from AHL_NR_PLANNING_REQMENTS table on source
391 * 4. Insert into AHL_NR_PLANNING_REQMENTS table on source
392 *
393 * ------------ PARAMETERS LIST ----------------
394 * p_sr_instance_id : Instance Id
395 * p_export_data_profile : Demantra Export Data profile name
396 * p_workflow_lookup_code : Demantra Workflow EBS lookup code
397 *
398 */
399
400 PROCEDURE UPLOAD_CMRO_PLN_FCTRS (
401 errbuf OUT NOCOPY VARCHAR2,
402 retcode OUT NOCOPY VARCHAR2,
403 p_sr_instance_id IN NUMBER,
404 p_export_data_profile IN VARCHAR2,
405 p_workflow_lookup_code IN VARCHAR2 ,
406 p_synchronous IN NUMBER DEFAULT C_YES,
407 p_check_interval IN NUMBER DEFAULT 60,
408 p_time_out IN NUMBER DEFAULT 1440
409
410 );
411
412
413 /*
414 * This procedure is used to export the forecast of the following -
415 * 1. Independent demand of non-maintenance products and materials
416 * 2. Work order demand of materials.
417 * The parameters are -
418 * p_ind_export_data_profile_wai - Application Id of the export data profile used to export independent demand
419 * p_wod_export_data_profile_wai - Application Id of the export data profile used to export work order demand
420 * p_ind_fcst_series_wai - Application Id of the series which holds independent demand
421 * p_wod_fcst_series_wai - Application Id of the series which holds work order demand
422 * p_wod_fcst_acry_series_wai - Application Id of the series which holds the work order demand MAPE value in MDP_MATRIX
423 */
424 PROCEDURE UPLOAD_TOTAL_DEMAND_WO (
425 p_ind_export_data_profile_wai IN VARCHAR2,
426 p_wod_export_data_profile_wai IN VARCHAR2,
427 p_ind_fcst_series_wai IN VARCHAR2,
428 p_wod_fcst_series_wai IN VARCHAR2,
429 p_wod_fcst_acry_series_wai IN VARCHAR2 DEFAULT 'COMPUTED_FIELD:4577');
430
431
432
433
434 /*
435 * This procedure will load SPF forecast data into MSD_DP_SCN_ENTRIES_DENORM and
436 * SPF metrics into MSC_DMD_SCN_METRICS.
437 * Arguments -
438 * 1. SPF Upload Forecast and Metrics data profile appl id
439 * Process -
440 * a. First forecast and metrics data is loaded to MSD_DP_SCN_ENTRIES_DENORM
441 * b. Then aggregated (time independent) metrics data is copied over to
442 * MSC_DMD_SCN_METRICS
443 *
444 * Note - This way the Demantra export view is queries only once and hence
445 * improves the total run time.
446 *
447 * Info about the loading of the data is logged in integ_status table.
448 */
449 PROCEDURE UPLOAD_SPF_FORECAST_METRICS (
450 p_dp_spf_fcst_metrics_appid IN VARCHAR2,
451 p_acry_series_wai IN VARCHAR2 DEFAULT NULL,
452 p_parallel_degree IN NUMBER DEFAULT NULL );
453
454
455 END MSD_DEM_UPLOAD_FORECAST;
[Home] [Help]