[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_SOP
Source
1 PACKAGE BODY MSD_DEM_SOP AS
2 /* $Header: msddemsopb.pls 120.62.12020000.8 2013/01/17 06:48:14 kkhatri ship $ */
3
4
5 /*** CUSTOM DATA TYPES ***/
6
7
8 /*** CONSTANTS ***/
9
10
11 /*** GLOBAL VARIABLES ***/
12 g_schema VARCHAR(50) := NULL;
13
14
15 /*** PRIVATE FUNCTIONS ***
16 * GET_PLAN_ID
17 * GET_PLAN_TYPE
18 */
19
20
21 /*
22 * This functions returns the plan_id in msc_plans given
23 * the member_id of a supply plan
24 * param: p_member_id - member_id of the supply plan level member
25 */
26 FUNCTION GET_PLAN_ID ( p_member_id IN NUMBER )
27 RETURN NUMBER
28 IS
29 x_plan_id NUMBER := NULL;
30 BEGIN
31
32 EXECUTE IMMEDIATE 'SELECT plan_id FROM ' ||
33 msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'SUPPLY_PLAN') ||
34 ' WHERE supply_plan_id = ' || p_member_id
35 INTO x_plan_id;
36
37 RETURN x_plan_id;
38
39 EXCEPTION
40 WHEN OTHERS THEN
41 RETURN NULL;
42
43 END GET_PLAN_ID;
44
45
46 /*
47 * This functions returns the plan_type given
48 * the member_id of a supply plan
49 * param: p_member_id - member_id of the supply plan level member
50 */
51 FUNCTION GET_PLAN_TYPE ( p_member_id IN NUMBER )
52 RETURN NUMBER
53 IS
54 x_plan_type NUMBER := NULL;
55 BEGIN
56
57 EXECUTE IMMEDIATE 'SELECT plan_type FROM ' ||
58 msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'SUPPLY_PLAN') ||
59 ' WHERE supply_plan_id = ' || p_member_id
60 INTO x_plan_type;
61
62 RETURN x_plan_type;
63
64 EXCEPTION
65 WHEN OTHERS THEN
66 RETURN NULL;
67
68 END GET_PLAN_TYPE;
69
70 /* This function returns the start date for the aggregration period.
71 * Gets the aggreration level set for Maintenance WO plan option
72 * 1 = Individual, 2 = Aggregation
73 * Gets the start date from msc_plan_buckets
74 * bucket_type: 1 = daily, 2 = week, 3 = period
75 */
76
77 FUNCTION GET_AGGREGATION_START_DATE (p_plan_id in number)
78 RETURN DATE
79 IS
80 cursor get_aggregation_info is
81 select curr_start_date, cutoff_date,
82 daily_mwo_aggr_lvl,weekly_mwo_aggr_lvl,period_mwo_aggr_lvl
83 from msc_plans
84 where plan_id = p_plan_id;
85
86 l_start_date date:= NULL;
87 l_end_date date:= NULL;
88 l_aggregation_start_date date:= NULL;
89 l_daily_mwo_aggr_lvl number:= NULL;
90 l_weekly_mwo_aggr_lvl number:= NULL;
91 l_period_mwo_aggr_lvl number:= NULL;
92 x_errbuf varchar2(1000) := NULL;
93
94 Begin
95 open get_aggregation_info;
96 fetch get_aggregation_info into
97 l_start_date, l_end_date, l_daily_mwo_aggr_lvl, l_weekly_mwo_aggr_lvl, l_period_mwo_aggr_lvl ;
98 close get_aggregation_info;
99
100
101 IF((l_daily_mwo_aggr_lvl =1) AND (l_weekly_mwo_aggr_lvl=1) AND (l_period_mwo_aggr_lvl=1) ) then
102 l_aggregation_start_date := l_end_date;
103
104 ELSIF ((l_daily_mwo_aggr_lvl =2) AND (l_weekly_mwo_aggr_lvl=2) AND (l_period_mwo_aggr_lvl=2)) then
105 l_aggregation_start_date := l_start_date;
106
107 ELSIF ((l_daily_mwo_aggr_lvl = 1) AND (l_weekly_mwo_aggr_lvl = 2) AND (l_PERIOD_mwo_aggr_lvl =2)) then
108
109 select min(bkt_start_date) into l_aggregation_start_date
110 from msc_plan_buckets
111 where plan_id = p_plan_id and bucket_type = 2 ;
112
113 ELSIF ((l_daily_mwo_aggr_lvl = 1) AND (l_weekly_mwo_aggr_lvl = 1) AND (l_PERIOD_mwo_aggr_lvl =2)) then
114
115 select min(bkt_start_date) into l_aggregation_start_date
116 from msc_plan_buckets
117 where plan_id = p_plan_id and bucket_type = 3 ;
118
119 ELSIF ((l_daily_mwo_aggr_lvl is NULL ) AND (l_weekly_mwo_aggr_lvl is NULL ) AND (l_PERIOD_mwo_aggr_lvl is NULL )) then
120 RETURN nvl(l_aggregation_start_date,l_end_date);
121 ELSE
122 x_errbuf := 'Incorrect Aggregation type for Maintenance Work Order Aggregation Level in the plan. ' ;
123 x_errbuf := 'Error(8): msd_dem_sop.get_aggregation_start_date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || x_errbuf;
124 raise_application_error(-20007, x_errbuf);
125 END IF;
126
127 RETURN l_aggregation_start_date;
128
129 EXCEPTION
130 WHEN OTHERS THEN
131 RETURN NULL;
132
133 END GET_AGGREGATION_START_DATE;
134
135
136
137 FUNCTION GET_BASE_WO_TXN_ID (
138 p_sr_instance_id in number,
139 p_plan_id in number,
140 p_dem_sup_id in number,
141 p_dem_sup_flag in number default 1,
142 p_pegging_id IN NUMBER DEFAULT NULL -- Bug#13429046
143 )
144 RETURN NUMBER
145 IS
146 x_sql varchar2(1000) := null;
147 x_end_pegging_id number := null;
148 x_return_val number := null;
149 begin
150 x_sql := 'select nvl(ms.transaction_id , mfp.transaction_id) ' ||
151 ' from (select * from ( ' ||
152 ' select sr_instance_id, plan_id, transaction_id, demand_id ' ||
153 ' from msc_full_pegging ' ||
154 ' where sr_instance_id = :1 and plan_id = :2 and supply_type in (70,92)' ||
155 ' start with sr_instance_id = :3 and plan_id = :4 ' || case when p_pegging_id is not null then 'and pegging_id = ' || to_char(p_pegging_id) else ' ' end ||
156 ' and ' || case when nvl(p_dem_sup_flag, 1)= 1 then 'transaction_id' else 'demand_id' end || ' = :5 ' ||
157 ' connect by nocycle prior prev_pegging_id = pegging_id and prior plan_id = plan_id and prior sr_instance_id = sr_instance_id ' ||
158 ' order by level desc) ' ||
159 ' where rownum <= 1) mfp, ' ||
160 ' msc_demands md, msc_supplies ms ' ||
161 ' where md.sr_instance_id(+) = mfp.sr_instance_id and md.plan_id(+) = mfp.plan_id ' ||
162 ' and md.demand_id(+) = mfp.demand_id ' ||
163 ' and ms.sr_instance_id(+) = md.sr_instance_id and ms.plan_id(+) = md.plan_id ' ||
164 ' and ms.transaction_id(+) = md.disposition_id and ms.order_type(+) in (70,92) ' ;
165 execute immediate x_sql into x_return_val using p_sr_instance_id, p_plan_id, p_sr_instance_id, p_plan_id, p_dem_sup_id;
166 return x_return_val;
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 return null;
170 END GET_BASE_WO_TXN_ID;
171
172
173
174 /*** PRIVATE PROCEDURES ***
175 * LOG_DEBUG
176 * LOG_MESSAGE
177 * TRUNCATE_STAGING_TABLES
178 * LOAD_SERIES_DATA
179 * PRE_DOWNLOAD_HOOK
180 * LOAD_ASCP_DATA
181 * PUSH_TIME_DATA
182 * LOAD_SUP_PLAN_GL
183 * LOAD_PLAN_GL
184 * LOAD_RESOURCE_GL
185 * LOAD_ITEM_LOCS
186 */
187
188
189 /*
190 * This procedure logs a given debug message text in ???
191 * only if the profile MSD_DEM_DEBUG is set to 'Yes'.
192 * param: p_buff - debug message text to be logged.
193 */
194 PROCEDURE LOG_DEBUG ( p_buff IN VARCHAR2)
195 IS
196 x_sql varchar2(500) := null;
197 BEGIN
198 g_schema := C_MSD_DEM_SCHEMA;
199 IF (C_MSD_DEM_DEBUG = 'Y') THEN
200 x_sql := 'begin '|| g_schema || '.dbex(:1, :2); end;';
201 execute immediate x_sql using p_buff, 'msd_dem_sop';
202 END IF;
203 END LOG_DEBUG;
204
205
206
207 /*
208 * This procedure logs a given message text in ???
209 * param: p_buff - message text to be logged.
210 */
211 PROCEDURE LOG_MESSAGE ( p_buff IN VARCHAR2)
212 IS
213 x_sql varchar2(500) := null;
214 BEGIN
215 g_schema := C_MSD_DEM_SCHEMA;
216 x_sql := 'begin '|| g_schema || '.dbex(:1, :2); end;';
217 execute immediate x_sql using p_buff, 'msd_dem_sop';
218 END LOG_MESSAGE;
219
220 /*
221 * This procedure truncates all the staging tables for ascp plan related series
222 * NOTE: Must be called ONLY after global variable g_schema has been set.
223 */
224 PROCEDURE TRUNCATE_STAGING_TABLES (
225 errbuf OUT NOCOPY VARCHAR2,
226 retcode OUT NOCOPY VARCHAR2)
227 IS
228 x_table_name VARCHAR2(60) := NULL;
229 BEGIN
230
231 log_debug ('Entering: msd_dem_sop.truncate_staging_tables - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
232
233
234 x_table_name := g_schema || '.BIIO_RESOURCE_CAPACITY';
235 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
236 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
237
238 x_table_name := g_schema || '.BIIO_RESOURCE_CAPACITY_ERR';
239 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
240 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
241
242 x_table_name := g_schema || '.BIIO_OTHER_PLAN_DATA';
243 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
244 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
245
246 x_table_name := g_schema || '.BIIO_OTHER_PLAN_DATA_ERR';
247 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
248 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
249
250 x_table_name := g_schema || '.BIIO_PURGE_PLAN';
251 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
252 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
253
254 x_table_name := g_schema || '.BIIO_PURGE_PLAN_ERR';
255 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
256 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
257
258 x_table_name := g_schema || '.BIIO_PURGE_PLAN_RESOURCE';
259 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
260 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
261
262 x_table_name := g_schema || '.BIIO_PURGE_PLAN_RESOURCE_ERR';
263 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
264 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
265
266 x_table_name := g_schema || '.BIIO_RESOURCES';
267 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
268 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
269
270 x_table_name := g_schema || '.BIIO_RESOURCES_ERR';
271 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
272 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
273
274 x_table_name := g_schema || '.BIIO_SCENARIO_RESOURCES';
275 log_message ('Info: msd_dem_sop.truncate_staging_tables - Deleting data from table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
276 EXECUTE IMMEDIATE 'DELETE FROM ' || x_table_name;
277
278 x_table_name := g_schema || '.BIIO_SCENARIO_RESOURCES_ERR';
279 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
280 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
281
282 x_table_name := g_schema || '.BIIO_SCENARIO_RESOURCE_POP';
283 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
284 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
285
286 x_table_name := g_schema || '.BIIO_SCENARIO_RESOURCE_POP_ERR';
287 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
288 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
289
290 x_table_name := g_schema || '.BIIO_SUPPLY_PLANS';
291 log_message ('Info: msd_dem_sop.truncate_staging_tables - Deleting data from table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
292 EXECUTE IMMEDIATE 'DELETE FROM ' || x_table_name;
293
294 x_table_name := g_schema || '.BIIO_SUPPLY_PLANS_ERR';
295 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
296 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
297
298 x_table_name := g_schema || '.BIIO_SUPPLY_PLANS_POP';
299 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
300 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
301
302 x_table_name := g_schema || '.BIIO_SUPPLY_PLANS_POP_ERR';
303 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
304 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
305
306 x_table_name := g_schema || '.T_SRC_ITEM_TMPL';
307 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
308 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
309
310 x_table_name := g_schema || '.T_SRC_ITEM_TMPL_ERR';
311 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
312 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
313
314 x_table_name := g_schema || '.T_SRC_LOC_TMPL';
315 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
316 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
317
318 x_table_name := g_schema || '.T_SRC_LOC_TMPL_ERR';
319 log_message ('Info: msd_dem_sop.truncate_staging_tables - Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
320 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
321
322 log_debug ('Exiting: msd_dem_sop.truncate_staging_tables - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
323
324 EXCEPTION
325 WHEN OTHERS THEN
326 retcode := -1;
327 errbuf := substr(SQLERRM,1,150);
328 log_message ('Exception(1): msd_dem_sop.truncate_staging_tables - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
329 RETURN;
330 END TRUNCATE_STAGING_TABLES;
331
332
333 /*
334 *
335 */
336 PROCEDURE LOAD_SERIES_DATA (
337 errbuf OUT NOCOPY VARCHAR2,
338 retcode OUT NOCOPY VARCHAR2,
339 p_series_id IN NUMBER,
340 p_plan_id IN NUMBER,
341 p_dm_time_level IN NUMBER,
342 p_filter_org_id IN NUMBER DEFAULT NULL,
343 p_start_date IN VARCHAR2 DEFAULT NULL ,
344 p_end_date IN VARCHAR2 DEFAULT NULL ,
345 p_data_source_pds IN NUMBER DEFAULT 1)
346 IS
347
348 CURSOR c_get_series_info
349 IS
350 SELECT
351 series_name,
352 series_type,
353 identifier,
354 custom_view_name,
355 ps_view_name,
356 stg_series_col_name
357 FROM
358 msd_dem_series
359 WHERE
360 series_id = p_series_id;
361
362
363 CURSOR c_get_plan_schedules
364 IS
365 SELECT input_schedule_id from msc_plan_schedules
366 where plan_id = p_plan_id;
367
368 /*** LOCAL VARIABLES - BEGIN ***/
369
370 x_errbuf VARCHAR2(200) := NULL;
371 x_retcode VARCHAR2(100) := NULL;
372
373 x_series_name VARCHAR2(250) := NULL;
374 x_series_type NUMBER := NULL;
375 x_identifier VARCHAR2(50) := NULL;
376 x_custom_view_name VARCHAR2(30) := NULL;
377 x_view_name VARCHAR2(30) := NULL;
378 x_is_custom NUMBER := NULL;
379 x_stg_series_col_name VARCHAR2(30) := NULL;
380 x_key_values VARCHAR2(4000) := NULL;
381 x_large_sql VARCHAR2(32000) := NULL;
382 x_input_schedule_id VARCHAR2(8000) := NULL;
383 x_demantra_start_Date VARCHAR2(150) :=NULL;
384 x_sql VARCHAR2(4000) := NULL;
385
386 /*** LOCAL VARIABLES - END ***/
387
388 BEGIN
389 log_debug ('Entering: msd_dem_sop.load_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
390
391 /* Validate INPUT Parameters */
392 IF (p_series_id IS NULL OR p_plan_id IS NULL)
393 THEN
394 log_message ('Error(1): msd_dem_sop.load_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') ||
395 ' - One or Both the parameters passed to the procedure is/are NULL.');
396 RETURN;
397 END IF;
398
399 if (p_data_source_pds =3)
400 then
401 x_sql := ' Select to_char(bucket_date,''YYYY-MM-DD HH24:MI:SS'') from msd_Dem_day_dates mdd where mdd.day_date=TRUNC(TO_DATE(''' || p_start_date || ''',''YYYY-MM-DD HH24:MI:SS'') )';
402 EXECUTE IMMEDIATE x_sql into x_demantra_start_Date;
403
404 END IF;
405
406 /* Get Series Info */
407 OPEN c_get_series_info;
408 FETCH c_get_series_info INTO x_series_name,
409 x_series_type,
410 x_identifier,
411 x_custom_view_name,
412 x_view_name,
413 x_stg_series_col_name;
414 IF ( x_series_name IS NULL
415 OR x_series_type IS NULL
416 OR x_identifier IS NULL)
417 THEN
418 log_message ('Error(2): msd_dem_sop.load_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') ||
419 ' - Unable to get series info for id - ' || to_char(p_series_id));
420 RETURN;
421 END IF;
422 -- Bug#14151347
423 -- 0 = from ODS , 1 = from PDS
424 IF (p_data_source_pds = 0 or p_data_source_pds = 3) then
425
426 if ( x_identifier = 'MSD_DEM_SOP_WO_DEMANDS') then
427 x_identifier := 'MSD_DEM_SOP_WO_DEMANDS_AGGR';
428 elsif ( x_identifier = 'MSD_DEM_SOP_WO_REQR_RES_CAP') then
429 x_identifier := 'MSD_DEM_SOP_WO_REQR_RES_CAP_AGGR';
430 END IF;
431
432 -- Bug#14151347
433 -- collect all the scheduled plan added into ascp plan
434 FOR shedrec IN c_get_plan_schedules
435 LOOP
436 x_input_schedule_id := x_input_schedule_id || ' msup.schedule_designator_id = ' || to_char(shedrec.input_schedule_id) || ' OR ';
437 END LOOP;
438
439 x_key_values:= '$C_SCHED_ID_CLAUSE#' || nvl(x_input_schedule_id, ' ') ;
440
441 END IF;
442
443 /* Check if custom view is specified for the series */
444 IF (x_custom_view_name IS NULL)
445 THEN
446 x_is_custom := 0;
447 ELSE
448 x_is_custom := 1;
449 END IF;
450
451
452 IF (p_dm_time_level = 1)
453 THEN
454 x_key_values := x_key_values || '$C_PLAN_ID#' || to_char(p_plan_id)
455 || '$C_DEST_DATE#' || 'mdbr.sdate'
456 || '$C_SERIES_QTY#' || x_stg_series_col_name
457 || '$C_DEM_SCHEMA#' || g_schema
458 ||'$C_START_DATE_WO#' || p_start_date
459 ||'$C_END_DATE_WO#' || p_end_date
460 || '$C_DEM_PLAN_START_DATE#' || x_demantra_start_Date
461 || '$C_TIME_CLAUSE# $';
462
463 ELSE
464 x_key_values := x_key_values || '$C_PLAN_ID#' || to_char(p_plan_id)
465 || '$C_DEST_DATE#' || 'inp.datet'
466 || '$C_SERIES_QTY#' || x_stg_series_col_name
467 || '$C_DEM_SCHEMA#' || g_schema
468 ||'$C_START_DATE_WO#' || p_start_date
469 ||'$C_END_DATE_WO#' || p_end_date
470 || '$C_DEM_PLAN_START_DATE#' || x_demantra_start_Date
471 || '$C_TIME_CLAUSE#' || ', msd_dem_dates inp WHERE mdbr.sdate BETWEEN inp.start_date AND inp.end_date$';
472 END IF;
473 ---- Bug 14329031
474 if (p_data_source_pds =3)
475 then
476 x_key_values := x_key_values || 'C_ALL_AGG#'|| '1$' ;
477 else
478 x_key_values := x_key_values || 'C_ALL_AGG#'|| '0$' ;
479
480 END IF;
481
482 if (p_filter_org_id is null) then
483 x_key_values := x_key_values || 'C_ORG_FILTER# IS NOT NULL $';
484 else
485 x_key_values := x_key_values || 'C_ORG_FILTER# = ' || p_filter_org_id || ' $';
486 end if;
487
488 x_key_values := x_key_values || 'C_SIM_SET_ID#' || nvl(fnd_profile.value('MSD_DEM_SIMULATION_SET_NAME'), -1) || '$' ;
489
490 /* Get the query */
491 msd_dem_query_utilities.get_query3 (
492 x_retcode,
493 x_large_sql,
494 x_identifier,
495 null,
496 x_key_values,
497 x_is_custom,
498 x_custom_view_name,
499 x_series_type,
500 x_view_name);
501
502 IF (x_retcode = -1)
503 THEN
504 log_message ('Error(3): msd_dem_sop.load_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') ||
505 ' - Unable to get query for identifier - ' || x_identifier);
506 RETURN;
507 END IF;
508
509 log_debug ('Query - '|| x_identifier);
510 log_debug (x_large_sql);
511
512 log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
513
514 BEGIN
515 EXECUTE IMMEDIATE x_large_sql;
516 EXCEPTION
517 WHEN OTHERS THEN
518 retcode := -1;
519 errbuf := substr(SQLERRM,1,150);
520 log_message ('Exception(1): msd_dem_sop.load_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
521 RETURN;
522 END;
523
524
525 log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
526
527 log_debug ('Exiting: msd_dem_sop.load_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
528 EXCEPTION
529 WHEN OTHERS THEN
530 retcode := -1;
531 errbuf := substr(SQLERRM,1,150);
532 log_message ('Exception(2): msd_dem_sop.load_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
533 RETURN;
534 END LOAD_SERIES_DATA;
535
536
537 /*
538 *
539 */
540 PROCEDURE PRE_DOWNLOAD_HOOK (
541 errbuf OUT NOCOPY VARCHAR2,
542 retcode OUT NOCOPY VARCHAR2,
543 p_plan_id IN NUMBER)
544 IS
545
546 CURSOR c_get_series_for_purge
547 IS
548 SELECT
549 series_id
550 FROM msd_dem_series
551 WHERE series_id IN (112, 113);
552
553 Cursor c_plan_start_date is
554 select curr_start_date
555 from msc_plans
556 where plan_id = p_plan_id;
557
558
559 /*** LOCAL VARIABLES - BEGIN ***/
560
561 x_errbuf VARCHAR2(200) := NULL;
562 x_retcode VARCHAR2(100) := NULL;
563
564 l_plan_start_date date := NULL;
565 x_to_date date := NULL;
566 x_from_date date := NULL;
567 l_profile_id1 number := NULL;
568 l_profile_id2 number := NULL;
569 l_sql varchar2(1000) := NULL;
570 g_schema varchar2(50) := NULL;
571
572
573 /*** LOCAL VARIABLES - END ***/
574
575 BEGIN
576 log_debug ('Entering: msd_dem_sop.pre_download_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
577
578 /***** 1. DECIDE UPON HOW TO HANDLE CONCURRENCY *****/
579 /***** Handled in WAIT_UNTIL_DOWNLOAD_COMPLETE *****/
580
581
582 /***** 2. Load rows for purging existing plan related data *****/
583
584 FOR rec IN c_get_series_for_purge
585 LOOP
586 log_debug ('Start Of Loop for series id - ' || to_number(rec.series_id) || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
587
588 load_series_data (
589 x_errbuf,
590 x_retcode,
591 rec.series_id,
592 p_plan_id,
593 1);
594 IF (x_retcode = -1)
595 THEN
596 retcode := -1;
597 errbuf := x_errbuf;
598 log_message ('Error(2): msd_dem_sop.pre_download_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
599 RETURN;
600 END IF;
601
602 log_debug ('End Of Loop for series id - ' || to_number(rec.series_id) || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
603 END LOOP;
604
605
606 /***** 3. UPDATE START DATES AND END DATES OF THE PURGE PLAN DATA *****/
607
608 OPEN c_plan_start_date;
609 FETCH c_plan_start_date INTO l_plan_start_date;
610 CLOSE c_plan_start_date;
611
612
613
614 g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
615
616 if (g_schema is not null)
617 then
618
619 l_sql := 'select datet from '|| g_schema ||'.inputs where datet > '''||l_plan_start_date||''' and rownum = 1 order by datet asc';
620 execute immediate l_sql into x_from_date;
621
622 l_sql := 'select max(datet) from '||g_schema||'.inputs ';
623 execute immediate l_sql into x_to_date;
624
625 /* Setting start and end dates for Purge Plan Data data profile */
626
627 /* Bug#8224935 - APP ID */ -- nallkuma
628 l_profile_id1 := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
629 'PROFILE_PURGE_PLAN_DATA',
630 1,
631 'id'));
632
633 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_FDATE('||l_profile_id1||', '''|| x_from_date||''', '''||x_to_date||'''); end;';
634 execute immediate l_sql;
635
636
637 /* Calling API to notify the application server to refresh its engine */
638 msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
639
640 l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id1 ||'); end;';
641 execute immediate l_sql;
642
643 /* Setting start and end dates for Purge Resource Data data profile*/
644
645 /* Bug#8224935 - APP ID */ -- nallkuma
646 l_profile_id2 := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
647 'PROFILE_PURGE_RESOURCE_DATA',
648 1,
649 'id'));
650
651 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_FDATE('||l_profile_id2||', '''|| x_from_date||''', '''||x_to_date||'''); end;';
652 execute immediate l_sql;
653
654
655 /* Calling API to notify the application server to refresh its engine */
656 msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
657
658 l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id2 ||'); end;';
659 execute immediate l_sql;
660 -- syenamar
661 else
662 msd_dem_common_utilities.log_message('Demantra Schema not set');
663 end if;
664
665
666
667 log_debug ('Exiting: msd_dem_sop.pre_download_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
668 EXCEPTION
669 WHEN OTHERS THEN
670 retcode := -1;
671 errbuf := substr(SQLERRM,1,150);
672 log_message ('Exception: msd_dem_sop.pre_download_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
673 RETURN;
674 END PRE_DOWNLOAD_HOOK;
675
676
677
678 /*
679 *
680 */
681 PROCEDURE LOAD_ASCP_DATA (
682 errbuf OUT NOCOPY VARCHAR2,
683 retcode OUT NOCOPY VARCHAR2,
684 p_plan_id IN NUMBER)
685 IS
686
687 CURSOR c_get_all_series
688 IS
689 SELECT
690 series_id
691 FROM
692 msd_dem_series
693 WHERE
694 series_id IN (101, 102, 103, 104, 105, 106, 107, 108, 109, 110);
695
696 /*** LOCAL VARIABLES - BEGIN ***/
697
698 x_errbuf VARCHAR2(200) := NULL;
699 x_retcode VARCHAR2(100) := NULL;
700
701 x_dm_time_level NUMBER := NULL;
702 x_dm_time_bucket VARCHAR2(30) := NULL;
703
704 x_sql VARCHAR2(2000) := NULL;
705
706 /*** LOCAL VARIABLES - END ***/
707
708 BEGIN
709 log_debug ('Entering: msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
710
711 /* Get the lowest time bucket */
712 x_dm_time_bucket := msd_dem_common_utilities.dm_time_level;
713 IF (x_dm_time_bucket IS NULL)
714 THEN
715 retcode := -1;
716 errbuf := 'Unable to get lowest time bucket';
717 log_message('Error(1): msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
718 RETURN;
719 ELSIF (upper(x_dm_time_bucket) = 'DAY')
720 THEN
721 x_dm_time_level := 1;
722 ELSE
723 x_dm_time_level := 2;
724 END IF;
725
726
727 FOR rec IN c_get_all_series
728 LOOP
729 log_debug ('Start Of Loop for series id - ' || to_number(rec.series_id) || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
730
731 load_series_data (
732 x_errbuf,
733 x_retcode,
734 rec.series_id,
735 p_plan_id,
736 x_dm_time_level);
737 IF (x_retcode = -1)
738 THEN
739 retcode := -1;
740 errbuf := x_errbuf;
741 log_message ('Error(2): msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
742 RETURN;
743 END IF;
744
745 COMMIT;
746
747 log_debug ('End Of Loop for series id - ' || to_number(rec.series_id) || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
748 END LOOP;
749
750 /* Delete non-saleable items from the staging table BIIO_OTHER_PLAN_DATA */
751 -- syenamar
752 -- In case 'Include dependent demand' is true, delete non-saleable items only if they are non-cto
753 -- if false, no change in existing behaviour (any non-saleable item will be removed)
754 x_sql := ' DELETE FROM ' || g_schema || '.BIIO_OTHER_PLAN_DATA bopd '
755 || ' WHERE ';
756
757
758
759 IF (fnd_profile.value('MSD_DEM_INCLUDE_DEPENDENT_DEMAND') = 1)
760 THEN
761 x_sql := x_sql || ' NOT EXISTS '
762 || ' (SELECT 1 from '
763 || g_schema || '.t_ep_item tei, '
764 || g_schema || '.t_ep_cto_matrix tcm, '
765 || g_schema || '.items itm '
766 || ' where tei.item = bopd.level2 '
767 || ' and tei.t_ep_item_ep_id = itm.t_ep_item_ep_id '
768 || ' and itm.item_id = tcm.item_id '
769 || ' and rownum < 2) '
770 || ' AND ';
771 END IF;
772
773 x_sql := x_sql || ' NOT EXISTS ( SELECT 1 '
774 || ' FROM ' || g_schema || '.t_ep_item tei '
775 || ' WHERE tei.item = bopd.level2 '
776 || ' AND EXISTS ( SELECT 1 FROM ' || g_schema || '.mdp_matrix mm '
777 || ' WHERE mm.t_ep_item_ep_id = tei.t_ep_item_ep_id '
778 || ' AND rownum < 2 ) ) '
779 || ' AND avail_sup_std_cap IS NULL '
780 || ' AND required_sup_cap IS NULL ';
781 --syenamar
782
783 log_debug ('Query - ');
784 log_debug (x_sql);
785
786 log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
787
788 BEGIN
789 EXECUTE IMMEDIATE x_sql;
790 EXCEPTION
791 WHEN OTHERS THEN
792 retcode := -1;
793 errbuf := substr(SQLERRM,1,150);
794 log_message ('Exception(1): msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
795 RETURN;
796 END;
797
798 COMMIT;
799
800 log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
801
802 /* Updated non-supplier series to NULL for non-saleable:buy:critical items from the staging table BIIO_OTHER_PLAN_DATA */
803 x_sql := ' UPDATE ' || g_schema || '.BIIO_OTHER_PLAN_DATA bopd '
804 || ' SET bopd.constrained_fcst = NULL, '
805 || ' bopd.prod_plan = NULL, '
806 || ' bopd.safety_stk = NULL, '
807 || ' bopd.beginning_on_hand = NULL, '
808 || ' bopd.dependent_demand = NULL, '
809 || ' bopd.planned_shipments = NULL '
810 || ' WHERE NOT EXISTS ( SELECT 1 '
811 || ' FROM ' || g_schema || '.t_ep_item tei, '
812 || g_schema || '.mdp_matrix mm '
813 || ' WHERE tei.item = bopd.level2 '
814 || ' AND mm.t_ep_item_ep_id = tei.t_ep_item_ep_id '
815 || ' AND mm.is_fictive = 0 '
816 || ' AND rownum < 2 ) '
817 || ' AND ( avail_sup_std_cap IS NOT NULL '
818 || ' OR required_sup_cap IS NOT NULL ) ';
819
820 log_debug ('Query - ');
821 log_debug (x_sql);
822
823 log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
824
825 BEGIN
826 EXECUTE IMMEDIATE x_sql;
827 EXCEPTION
828 WHEN OTHERS THEN
829 retcode := -1;
830 errbuf := substr(SQLERRM,1,150);
831 log_message ('Exception(2): msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
832 RETURN;
833 END;
834
835 COMMIT;
836
837 log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
838
839 log_debug ('Exiting: msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
840 EXCEPTION
841 WHEN OTHERS THEN
842 retcode := -1;
843 errbuf := substr(SQLERRM,1,150);
844 log_message ('Exception(3): msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
845 RETURN;
846 END LOAD_ASCP_DATA;
847
848
849
850 /*
851 *
852 */
853 PROCEDURE PUSH_TIME_DATA (
854 errbuf OUT NOCOPY VARCHAR2,
855 retcode OUT NOCOPY VARCHAR2 )
856 IS
857
858 /*** LOCAL VARIABLES - BEGIN ***/
859
860 x_sql VARCHAR2(1000) := NULL;
861
862 x_dm_table VARCHAR2(100) := NULL;
863 x_source_time_table VARCHAR2(100) := NULL;
864 x_start_date VARCHAR2(100) := NULL;
865 x_end_date VARCHAR2(100) := NULL;
866
867 x_time_bucket VARCHAR2(30) := NULL;
868 x_first_day_of_week VARCHAR2(30) := NULL;
869 x_aggregation_method NUMBER(1) := NULL;
870 x_actual_agg_method NUMBER(1) := NULL;
871
872 /*** LOCAL VARIABLES - END ***/
873
874 BEGIN
875
876 log_debug ('Entering: msd_dem_sop.push_time_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
877
878 IF (C_MSD_DEM_PUSH_TIME = 'N')
879 THEN
880 log_debug ('Table msd_dem_dates has already been populated for this session.');
881 RETURN;
882 END IF;
883
884 log_debug ('Deleting time data from msd_dem_dates');
885 EXECUTE IMMEDIATE 'DELETE FROM msd_dem_dates';
886
887 log_debug ('Deleting time data from msd_dem_day_dates');
888 EXECUTE IMMEDIATE 'DELETE FROM msd_dem_day_dates';
889
890 x_dm_table := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'DM_WIZ_DM_DEF');
891
892 /* Get the time level info for the active data model */
893 x_sql := 'SELECT time_bucket, first_day_of_week, aggregation_method ' ||
894 ' FROM ' || x_dm_table ||
895 ' WHERE dm_or_template = 2 ' ||
896 ' AND is_active = 1 ';
897
898 EXECUTE IMMEDIATE x_sql INTO x_time_bucket, x_first_day_of_week, x_aggregation_method;
899
900
901 IF (upper(x_time_bucket) = 'DAY')
902 THEN
903 log_debug ('Lowest Time Bucket - Day : Time data not inserted into source msd_dem_dates');
904 RETURN;
905 ELSIF (upper(x_time_bucket) = 'WEEK')
906 THEN
907 x_actual_agg_method := x_aggregation_method;
908 ELSIF (upper(x_time_bucket) = 'MONTH')
909 THEN
910 /* Aggregate backwards */
911 x_actual_agg_method := 2;
912 ELSE
913 retcode := -1;
914 errbuf := 'Invalid time bucket';
915 log_message ('Error(1): msd_dem_push_setup_parameters.push_time_data - ' || 'Invalid time bucket - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
916 RETURN;
917 END IF;
918
919 --bug#7361574 syenamar
920 --adjust x_end_date so that time is set to 23hrs 59mins 59secs on that date
921 IF (x_actual_agg_method = 1) /* Forward */
922 THEN
923 x_start_date := ' datet - num_of_days + 1 start_date, ';
924 x_end_date := ' datet ';
925 ELSE
926 x_start_date := ' datet start_date, ';
927 x_end_date := ' datet + num_of_days - 1 ';
928 END IF;
929
930 x_end_date := 'trunc(' || x_end_date || ') + 86399/86400 end_date ';
931
932 x_source_time_table := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'INPUTS');
933
934 log_debug ('Inserting time data into msd_dem_dates');
935 x_sql := 'INSERT INTO msd_dem_dates' ||
936 ' (datet, num_of_days, start_date, end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
937 ' SELECT datet, num_of_days, ' || x_start_date || x_end_date || ', ' ||
938 ' sysdate, :1, sysdate, :2, :3 ' ||
939 ' FROM ' || x_source_time_table;
940 EXECUTE IMMEDIATE x_sql USING fnd_global.user_id, fnd_global.user_id, fnd_global.login_id;
941
942 x_sql := 'INSERT INTO msd_dem_day_dates' ||
943 ' (day_date, bucket_date, bucket_start_date, bucket_end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
944 ' with dem_dates as '||
945 ' (SELECT datet, num_of_days, ' || x_start_date || x_end_date ||
946 ' FROM ' || x_source_time_table || ') ' ||
947 ' select start_date + i day_date, datet, start_date, end_date, sysdate, :1, sysdate, :2, :3 ' ||
948 ' from dem_dates, xmltable(''for $i in 0 to xs:int(D)-1 return $i'' passing xmlelement(d, num_of_days)
949 columns i integer path ''.'')' ;
950 execute immediate x_sql using fnd_global.user_id, fnd_global.user_id, fnd_global.login_id;
951 COMMIT;
952
953 C_MSD_DEM_PUSH_TIME := 'N';
954
955 log_debug ('Exiting: msd_dem_sop.push_time_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
956
957 EXCEPTION
958 WHEN OTHERS THEN
959 retcode := -1;
960 errbuf := substr(SQLERRM,1,150);
961 log_message ('Exception: msd_dem_sop.push_time_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
962 RETURN;
963 END PUSH_TIME_DATA;
964
965
966
967
968
969 Procedure load_sup_plan_gl(p_plan_id in number,
970 p_compile_designator in varchar2,
971 p_plan_start_date in date,
972 p_end_date in date,
973 p_sop_enabled in number,
974 p_plan_type in number,
975 p_populate in number) is
976
977 x_plan_type varchar2(10);
978 x_plan_code varchar2(100);
979 x_plan_desc varchar2(240);
980 l_dem_sched varchar2(200);
981 x_dem_sched varchar2(2000):= NULL;
982
983 Type ref_cur is Ref Cursor;
984 c_dem_sched ref_cur;
985
986 c_scenario_status_id ref_cur;
987 c_scenario_status_code ref_cur;
988
989
990 l_scenario_status_id number;
991 l_scenario_status_code number;
992
993
994 l_stmt varchar2(240):= NULL;
995 l_sql varchar2(2000):= NULL;
996 g_schema varchar2(30):= NULL;
997
998 Begin
999
1000 -- BUG#9211268 syenamar
1001 --Enable download of RP plans into demantra (7.3 only)
1002 IF (p_plan_type in (101, 102, 103, 105)) THEN
1003 x_plan_type := 'Rapid Plan';
1004 ELSIF (p_plan_type <> 6) THEN
1005 x_plan_type := 'ASCP';
1006 ELSIF(p_plan_type = 6) THEN
1007 x_plan_type := 'SNO';
1008 end if;
1009
1010 x_plan_code := p_compile_designator;
1011 x_plan_desc := p_compile_designator;
1012 --syenamar
1013
1014 -- bug 14693176 kkhatri
1015 l_stmt := 'select input_name from msc_plan_sched_v where plan_id = ' ||p_plan_id || ' group by input_name ';
1016
1017
1018 open c_dem_sched for l_stmt;
1019 loop
1020 fetch c_dem_sched into l_dem_sched;
1021 exit when c_dem_sched%NOTFOUND;
1022 x_dem_sched := x_dem_sched ||', ' || l_dem_sched;
1023 end loop;
1024 close c_dem_sched;
1025
1026 x_dem_sched := substr(x_dem_sched, 2, 200);
1027
1028 g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
1029 IF (g_schema IS NULL)
1030 THEN
1031 log_message ('Error(1): msd_dem_sop.load_plan_data - Unable to find schema name');
1032 END IF;
1033
1034 l_sql := 'select scenario_status_id from '|| g_schema||'.supply_plan where plan_id = '||p_plan_id;
1035
1036 open c_scenario_status_id for l_sql;
1037 fetch c_scenario_status_id into l_scenario_status_id;
1038 close c_scenario_status_id;
1039
1040 if (l_scenario_status_id is null) then
1041 l_scenario_status_code := 4;
1042 else
1043 l_sql := 'select scenario_status_code from '|| g_schema||'.scenario_status where scenario_status_id = '||l_scenario_status_id;
1044
1045 open c_scenario_status_code for l_sql;
1046 fetch c_scenario_status_code into l_scenario_status_code;
1047 close c_scenario_status_code;
1048
1049 end if;
1050
1051
1052 l_sql := 'insert into ' || g_schema||'.biio_supply_plans(supply_plan_code,
1053 supply_plan_desc,
1054 scenario_status_code,
1055 plan_id,
1056 method_status,
1057 demand_schedules,
1058 plan_type,
1059 start_date,
1060 end_date,
1061 last_imported)
1062 VALUES ('''
1063 ||x_plan_code ||''','
1064 ||''''||x_plan_desc ||''','
1065 ||''||l_scenario_status_code ||','
1066 ||''||p_plan_id ||','
1067 ||''''|| NULL ||''','
1068 ||''''||x_dem_sched ||''','
1069 ||''''||x_plan_type ||''','
1070 ||''''||p_plan_start_date ||''','
1071 ||''''||p_end_date ||''','
1072 ||''''||sysdate ||''')' ;
1073
1074 execute immediate l_sql;
1075
1076 If( p_populate = 0 ) then
1077 /* Bug#8224935 - APP ID - Use get_app_id_text function instead of hard-coded level names */
1078 l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
1079 from_date,
1080 until_date,
1081 filter_level,
1082 level_order,
1083 filter_member)
1084 values ('''
1085 ||x_plan_code ||''','
1086 ||''''||p_plan_start_date ||''','
1087 ||''''||sysdate ||''','
1088 ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
1089 'LEVEL_ITEM',
1090 1,
1091 'table_label') ||''','
1092 ||''||'2' ||','
1093 ||''''||'0' ||''')' ;
1094 execute immediate l_sql;
1095
1096 l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
1097 from_date,
1098 until_date,
1099 filter_level,
1100 level_order,
1101 filter_member)
1102 values ('''
1103 ||x_plan_code ||''','
1104 ||''''||p_plan_start_date ||''','
1105 ||''''||sysdate ||''','
1106 ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
1107 'LEVEL_DEMAND_CLASS',
1108 1,
1109 'table_label') ||''','
1110 ||''||'2' ||','
1111 ||''''||'0' ||''')' ;
1112 execute immediate l_sql;
1113
1114 l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
1115 from_date,
1116 until_date,
1117 filter_level,
1118 level_order,
1119 filter_member)
1120 values ('''
1121 ||x_plan_code ||''','
1122 ||''''||p_plan_start_date ||''','
1123 ||''''||sysdate ||''','
1124 ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
1125 'LEVEL_ORGANIZATION',
1126 1,
1127 'table_label') ||''','
1128 ||''||'1' ||','
1129 ||''''||'0' ||''')' ;
1130 execute immediate l_sql;
1131
1132 l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
1133 from_date,
1134 until_date,
1135 filter_level,
1136 level_order,
1137 filter_member)
1138 values ('''
1139 ||x_plan_code ||''','
1140 ||''''||p_plan_start_date ||''','
1141 ||''''||sysdate ||''','
1142 ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
1143 'LEVEL_SITE',
1144 1,
1145 'table_label') ||''','
1146 ||''||'1' ||','
1147 ||''''||'0' ||''')' ;
1148 execute immediate l_sql;
1149
1150 l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
1151 from_date,
1152 until_date,
1153 filter_level,
1154 level_order,
1155 filter_member)
1156 values ('''
1157 ||x_plan_code ||''','
1158 ||''''||p_plan_start_date ||''','
1159 ||''''||sysdate ||''','
1160 ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
1161 'LEVEL_SALES_CHANNEL',
1162 1,
1163 'table_label') ||''','
1164 ||''||'1' ||','
1165 ||''''||'0' ||''')' ;
1166 execute immediate l_sql;
1167
1168 end if;
1169 commit;
1170
1171 end;
1172
1173
1174
1175
1176 procedure load_plan_gl(p_plan_id number,
1177 p_populate number)
1178
1179 is
1180
1181 cursor get_plan_info is
1182 select sr_instance_id, compile_designator, plan_type, curr_start_date, cutoff_date
1183 from msc_plans
1184 where plan_id = p_plan_id;
1185
1186 l_plan_type number;
1187
1188 l_plan_name varchar2(250);
1189
1190 l_instance_id number;
1191
1192 l_start_date date;
1193 l_end_date date;
1194
1195 l_stmt varchar2(6000);
1196 l_retcode number;
1197
1198 Cursor c_sup_plan is
1199 select compile_designator, curr_start_date, cutoff_date, sop_enabled, plan_type
1200 from msc_plans
1201 where plan_id=p_plan_id;
1202
1203 Type all_sup_plans is Ref Cursor;
1204 c_all_sup_plans all_sup_plans;
1205
1206 p_compile_designator VARCHAR2(240);
1207 p_plan_start_date DATE;
1208 p_end_date DATE;
1209 p_sop_enabled VARCHAR2(10);
1210 p_plan_type NUMBER;
1211
1212 Type dem_sched is Ref Cursor;
1213 c_dem_sched dem_sched;
1214
1215 l_dem_sched varchar2(2000);
1216 x_dem_sched varchar2(2000):= NULL;
1217
1218
1219 x_plan_id number;
1220 x_dem_plan_type varchar2(100) := null;
1221 x_dem_version NUMBER;
1222
1223 begin
1224
1225
1226 if p_populate <> 1 then
1227
1228 If(p_plan_id is null) then
1229
1230 l_stmt := 'select plan_id, compile_designator, curr_start_date, cutoff_date, sop_enabled, plan_type
1231 from msc_plans
1232 where sop_enabled = 1 ';
1233
1234 -- BUG#9211268 syenamar
1235 --Enable download of RP plans into demantra (7.3 only)
1236 x_dem_version := to_number(msd_dem_common_utilities.get_demantra_version);
1237
1238 IF (x_dem_version < 7.3) THEN
1239 l_stmt := l_stmt || ' and plan_type not in (101, 102, 103, 105)';
1240 END IF;
1241
1242
1243 /* Do not supply plan members which are already present inside Demantra */
1244 l_stmt := l_stmt || ' AND compile_designator NOT IN '
1245 || ' ( SELECT supply_plan_code FROM '
1246 || g_schema || '.supply_plan )';
1247
1248
1249 open c_all_sup_plans for l_stmt;
1250 loop
1251 fetch c_all_sup_plans into x_plan_id, p_compile_designator, p_plan_start_date, p_end_date, p_sop_enabled, p_plan_type;
1252 exit when c_all_sup_plans%NOTFOUND;
1253 load_sup_plan_gl(x_plan_id, p_compile_designator, p_plan_start_date, p_end_date, p_sop_enabled, p_plan_type, p_populate);
1254 end loop;
1255 close c_all_sup_plans;
1256
1257 Else
1258 open c_sup_plan;
1259 fetch c_sup_plan into p_compile_designator, p_plan_start_date, p_end_date, p_sop_enabled, p_plan_type;
1260 close c_sup_plan;
1261
1262 load_sup_plan_gl(p_plan_id, p_compile_designator, p_plan_start_date, p_end_date, p_sop_enabled, p_plan_type, p_populate);
1263
1264 end if;
1265
1266 return;
1267
1268 end if;
1269
1270 open get_plan_info;
1271 fetch get_plan_info into l_instance_id, l_plan_name, l_plan_type, l_start_date, l_end_date;
1272 close get_plan_info;
1273
1274
1275 if l_plan_name is not null then
1276
1277 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_DELETE_STMT', l_instance_id);
1278
1279 l_stmt := replace(l_stmt, 'C_TABLE_NAME', fnd_profile.value('MSD_DEM_SCHEMA') || '.biio_supply_plans');
1280
1281 execute immediate l_stmt;
1282
1283 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_DELETE_STMT', l_instance_id);
1284
1285 l_stmt := replace(l_stmt, 'C_TABLE_NAME', fnd_profile.value('MSD_DEM_SCHEMA') || '.biio_supply_plans_pop');
1286
1287 execute immediate l_stmt;
1288
1289 -- bug 14693176 kkhatri
1290 l_stmt := 'select input_name from msc_plan_sched_v where plan_id = ' ||p_plan_id || ' group by input_name ';
1291
1292
1293 open c_dem_sched for l_stmt;
1294 loop
1295 fetch c_dem_sched into l_dem_sched;
1296 exit when c_dem_sched%NOTFOUND;
1297 x_dem_sched := x_dem_sched ||', ' || l_dem_sched;
1298 end loop;
1299 close c_dem_sched;
1300
1301 x_dem_sched := substr(x_dem_sched, 2);
1302
1303 end if;
1304
1305 l_stmt := ' SELECT spd.from_date FROM ' || g_schema || '.supply_plan sp, '
1306 || g_schema || '.supply_plan_dates spd '
1307 || ' WHERE sp.plan_id = ' || p_plan_id
1308 || ' AND spd.supply_plan_id = sp.supply_plan_id ';
1309 EXECUTE IMMEDIATE l_stmt INTO l_start_date;
1310
1311 if l_plan_type <> 6 then
1312
1313 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_PLANS', l_instance_id);
1314
1315 if l_stmt is null then
1316 return;
1317 end if;
1318
1319 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1320
1321 -- BUG#9211268 syenamar
1322 --Enable download of RP plans into demantra (7.3 only)
1323 IF (l_plan_type in (101, 102, 103, 105)) THEN
1324 x_dem_plan_type := 'Rapid Plan';
1325 ELSE
1326 x_dem_plan_type := 'ASCP';
1327 END IF;
1328
1329 execute immediate l_stmt using l_plan_name, l_plan_name, x_dem_plan_type, 2, l_start_date, l_end_date, p_plan_id, substr(x_dem_sched,1,200);
1330 --syenamar
1331
1332 if p_populate = 1 then
1333
1334 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_PLANS_POP_EBS', l_instance_id);
1335
1336 if l_stmt is null then
1337 return;
1338 end if;
1339
1340 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1341
1342 execute immediate l_stmt using l_plan_name, l_start_date, l_end_date, p_plan_id, p_plan_id, p_plan_id, p_plan_id, p_plan_id, p_plan_id;
1343 end if;
1344
1345 end if;
1346
1347 if l_plan_type = 6 then
1348
1349 /* Get the end date for the SNO plan */
1350 l_end_date := msd_dem_common_utilities.get_sno_plan_cutoff_date (p_plan_id);
1351
1352 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_PLANS', l_instance_id);
1353
1354 if l_stmt is null then
1355 return;
1356 end if;
1357
1358 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1359
1360 execute immediate l_stmt using l_plan_name, l_plan_name, 'SNO', 2, l_start_date, l_end_date, p_plan_id, substr(x_dem_sched,1,200);
1361
1362 if p_populate = 1 then
1363
1364 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_PLANS_POP_SNO', l_instance_id);
1365
1366 if l_stmt is null then
1367 return;
1368 end if;
1369
1370 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1371
1372 execute immediate l_stmt using l_plan_name, l_start_date, l_end_date, p_plan_id, p_plan_id;
1373
1374 end if;
1375
1376 end if;
1377
1378 commit;
1379
1380
1381 end load_plan_gl;
1382
1383
1384 procedure load_resource_gl(p_plan_id number)
1385
1386 is
1387
1388 cursor get_plan_info is
1389 select sr_instance_id, compile_designator, plan_type, curr_start_date, cutoff_date
1390 from msc_plans
1391 where plan_id = p_plan_id;
1392
1393 l_plan_type number;
1394
1395 l_plan_name varchar2(250);
1396
1397 l_instance_id number;
1398
1399 l_start_date date;
1400 l_end_date date;
1401
1402 l_stmt varchar2(4000);
1403 l_retcode number;
1404
1405 begin
1406
1407 open get_plan_info;
1408 fetch get_plan_info into l_instance_id, l_plan_name, l_plan_type, l_start_date, l_end_date;
1409 close get_plan_info;
1410
1411 l_stmt := ' SELECT spd.from_date FROM ' || g_schema || '.supply_plan sp, '
1412 || g_schema || '.supply_plan_dates spd '
1413 || ' WHERE sp.plan_id = ' || p_plan_id
1414 || ' AND spd.supply_plan_id = sp.supply_plan_id ';
1415 EXECUTE IMMEDIATE l_stmt INTO l_start_date;
1416
1417
1418 if l_plan_type <> 6 then
1419
1420 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_SCENARIO_RESOURCES_EBS', l_instance_id);
1421
1422 if l_stmt is null then
1423 return;
1424 end if;
1425
1426 l_stmt := replace(l_stmt, 'C_PLAN_NAME', '''' || l_plan_name || '''');
1427 l_stmt := replace(l_stmt, 'C_PLAN_ID', p_plan_id);
1428 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1429
1430 execute immediate l_stmt;
1431
1432 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_RESOURCES_EBS', l_instance_id);
1433
1434 if l_stmt is null then
1435 return;
1436 end if;
1437
1438 l_stmt := replace(l_stmt, 'C_RESOURCE_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1439 l_stmt := replace(l_stmt, 'C_PLAN_NAME', '''' || l_plan_name || '''');
1440 l_stmt := replace(l_stmt, 'C_PLAN_ID', p_plan_id);
1441 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1442
1443 execute immediate l_stmt;
1444
1445 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_RESOURCE_POP_EBS', l_instance_id);
1446
1447 l_stmt := replace(l_stmt, 'C_PLAN_NAME', '''' || l_plan_name || '''');
1448 l_stmt := replace(l_stmt, 'C_PLAN_ID', p_plan_id);
1449 l_stmt := replace(l_stmt, 'C_PLAN_START_DATE', 'to_date(''' || to_char(l_start_date, 'dd-mm-yyyy') || ''',''dd-mm-yyyy'')');
1450 l_stmt := replace(l_stmt, 'C_PLAN_END_DATE', 'to_date(''' || to_char(l_end_date, 'dd-mm-yyyy') || ''',''dd-mm-yyyy'')');
1451 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1452
1453 execute immediate l_stmt;
1454
1455 end if;
1456
1457 if l_plan_type = 6 then
1458
1459 /* Get the end date for the SNO plan */
1460 l_end_date := msd_dem_common_utilities.get_sno_plan_cutoff_date (p_plan_id);
1461
1462 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_SCENARIO_RESOURCES_SNO', l_instance_id);
1463
1464 if l_stmt is null then
1465 return;
1466 end if;
1467
1468 l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1469 l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));
1470 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1471
1472 execute immediate l_stmt;
1473
1474 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_RESOURCES_SNO', l_instance_id);
1475
1476 if l_stmt is null then
1477 return;
1478 end if;
1479
1480 l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1481 l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));
1482 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1483
1484 execute immediate l_stmt;
1485
1486 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_RESOURCE_POP_SNO', l_instance_id);
1487
1488 if l_stmt is null then
1489 return;
1490 end if;
1491
1492 l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1493 l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));
1494 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1495 l_stmt := replace(l_stmt, 'C_PLAN_START_DATE', 'to_date(''' || to_char(l_start_date, 'dd-mm-yyyy') || ''',''dd-mm-yyyy'')');
1496 l_stmt := replace(l_stmt, 'C_PLAN_END_DATE', 'to_date(''' || to_char(l_end_date, 'dd-mm-yyyy') || ''',''dd-mm-yyyy'')');
1497
1498 execute immediate l_stmt;
1499
1500 end if;
1501
1502 commit;
1503
1504
1505 end;
1506
1507
1508
1509
1510 procedure load_item_locs( p_plan_id number)
1511 is
1512
1513 errbuf varchar2(1000);
1514 retcode number;
1515
1516 cursor get_instance_id is
1517 select sr_instance_id
1518 from msc_plans
1519 where plan_id = p_plan_id;
1520
1521 l_instance_id number;
1522
1523 begin
1524
1525 open get_instance_id;
1526 fetch get_instance_id into l_instance_id;
1527 close get_instance_id;
1528
1529
1530 msd_dem_collect_level_types.collect_levels(errbuf, retcode, l_instance_id, 2, p_plan_id);
1531
1532 msd_dem_collect_level_types.collect_levels(errbuf, retcode, l_instance_id, 1, p_plan_id);
1533
1534
1535 end load_item_locs;
1536
1537 PROCEDURE TRUNCATE_WO_STAGING_TABLES (
1538 errbuf OUT NOCOPY VARCHAR2,
1539 retcode OUT NOCOPY VARCHAR2)
1540 IS
1541 x_table_name VARCHAR2(60) := NULL;
1542 BEGIN
1543
1544 x_table_name := 'MSD.MSD_ST_SOP_WO_DEMANDS';
1545 log_debug ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1546 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
1547
1548 x_table_name := 'MSD.MSD_ST_SOP_WO_REQR_RES_CAP';
1549 log_debug ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1550 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
1551
1552 x_table_name := 'MSD.MSD_ST_SOP_WO_SUPPLIES';
1553 log_debug ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1554 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
1555
1556 x_table_name := g_schema || '.BIIO_SUPPLY_PLAN_DATA_WO';
1557 log_debug ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1558 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
1559
1560 x_table_name := g_schema || '.BIIO_SUPPLY_PLAN_DATA_WO_ERR';
1561 log_debug ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1562 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
1563
1564 EXCEPTION
1565 WHEN OTHERS THEN
1566 retcode := -1;
1567 errbuf := substr(SQLERRM,1,150);
1568 log_message ('Exception(1): msd_dem_sop.truncate_wo_staging_tables - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1569 RETURN;
1570 END TRUNCATE_WO_STAGING_TABLES;
1571
1572
1573 procedure CHECK_JOBS( errbuf OUT NOCOPY VARCHAR2,
1574 retcode OUT NOCOPY VARCHAR2,
1575 p_job_check_interval IN NUMBER default 60,
1576 p_jobs_timeout IN NUMBER default 86400)
1577 is
1578 x_sql varchar2(500) := NULL;
1579 x_jobs_running number := NULL;
1580 x_check_start_time date := NULL;
1581 x_jobs_time_elapsed number := NULL;
1582 begin
1583 x_check_start_time := sysdate;
1584 x_sql := 'select 1 from dual where exists ( select 1 from dba_jobs where (upper(what) like ''MSD_DEM_SOP.LOAD_SERIES_DATA_BATCH%'' or upper(what) like ''MSD_DEM_SOP.LOAD_PLAN_DATA_WO_PDS%'' or ';
1585 x_sql := x_sql || 'upper(what) like ''MSD_DEM_SOP.LOAD_PLAN_DATA_WO_ODS%'' ) and upper(broken) = ''N'' ) ';
1586
1587 loop
1588 dbms_lock.sleep(p_job_check_interval);
1589 execute immediate x_sql into x_jobs_running;
1590 x_jobs_time_elapsed := to_number(to_char(sysdate, 'J')) - to_number(to_char(x_check_start_time, 'J'))
1591 + to_number(to_char(sysdate, 'SSSSS')) - to_number(to_char(x_check_start_time, 'SSSSS'));
1592 exit when x_jobs_time_elapsed >= p_jobs_timeout;
1593 end loop;
1594
1595 retcode := -1;
1596 errbuf := 'Jobs still running after specified timeout has elapsed';
1597
1598 exception
1599 when no_data_found then
1600 retcode := 0; --no jobs running
1601 return;
1602 when others then
1603 retcode := -1;
1604 errbuf := substr(sqlerrm, 1, 200);
1605 end CHECK_JOBS;
1606
1607 /* Load supply plan data collects data from PDS for series
1608 * 1) Available Supplier Capacity 2) Safety Stock
1609 * 3) Demands (WO) 4) Required Resource Plan (WO)
1610 * 5) Supplies (WO) 6) Available Resource Plan (WO)
1611 * collects for individual date range of the plan.
1612 *
1613 */
1614
1615 PROCEDURE LOAD_PLAN_DATA_WO_PDS (
1616 p_plan_id IN NUMBER,
1617 p_start_date IN VARCHAR2,
1618 p_end_date IN VARCHAR2,
1619 p_aggregation_start_date IN VARCHAR2,
1620 p_dm_time_level IN NUMBER,
1621 p_use_jobs IN NUMBER default 0,
1622 p_split_batch_by_org IN NUMBER default 0,
1623 p_job_check_interval IN NUMBER default 60,
1624 p_jobs_timeout IN NUMBER default 86400
1625
1626 ) IS
1627
1628 CURSOR c_get_all_series
1629 IS
1630 SELECT series_id FROM msd_dem_series
1631 WHERE
1632 series_id IN (105, 107, 115, 116, 117, 118);
1633
1634 CURSOR c_get_plan_orgs
1635 IS
1636 SELECT organization_id FROM msc_plan_organizations
1637 WHERE plan_id = p_plan_id;
1638
1639 errbuf VARCHAR2(200) := NULL;
1640 retcode VARCHAR2(100) := NULL;
1641 x_job_proc_name VARCHAR2(800) := NULL;
1642 x_dm_time_level NUMBER := NULL;
1643 x_dm_time_bucket VARCHAR2(100) := NULL;
1644 x_job_number BINARY_INTEGER := NULL;
1645 x_start_date VARCHAR2(100) := NULL;
1646 x_end_date VARCHAR2(100) := NULL;
1647 l_end_date date := NULL;
1648 x_aggregation_start_date date :=NULL;
1649 x_series_id NUMBER := NULL;
1650 x_small_sql varchar2(10000) :=NULL;
1651 x_data_source_pds NUMBER:=NULL;
1652 BEGIN
1653
1654
1655 log_debug ('Entering: msd_dem_sop.load_plan_data_wo_pds ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1656
1657 x_data_source_pds:=1;
1658 x_dm_time_level :=p_dm_time_level;
1659
1660 if (p_use_jobs = 1) then
1661 log_debug ('Info:loading series data from PDS using DB jobs');
1662 end if;
1663
1664 if (p_split_batch_by_org = 1) then
1665 log_debug ('Info: series data load from PDS using batches split by org');
1666 end if;
1667
1668 log_debug ('Info: load all series data from PDS except costs');
1669 FOR rec IN c_get_all_series
1670 LOOP
1671 log_debug ('Info: msd_dem_sop.load_plan_data_wo_pds - Start Of Loop for series id - ' || to_number(rec.series_id) || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1672
1673 if(rec.series_id = 115 OR rec.series_id = 116) then
1674
1675 x_aggregation_start_date :=to_date(p_aggregation_start_date,'yyyy-mm-dd HH24:MI:SS');
1676 l_end_date :=to_date(p_end_date,'yyyy-mm-dd HH24:MI:SS');
1677
1678 if (x_aggregation_start_date < l_end_date) then
1679 l_end_date := x_aggregation_start_date -1;
1680 x_end_date :=to_char(l_end_date,'yyyy-mm-dd HH24:MI:SS');
1681 end if;
1682 else
1683 x_end_date := p_end_date;
1684 end if;
1685 x_start_date := p_start_date;
1686 x_series_id := rec.series_id;
1687
1688 if (p_use_jobs = 1) then
1689
1690 x_job_proc_name := 'msd_dem_sop.load_series_data_batch(' || rec.series_id || ', ' || p_plan_id || ', ' || x_dm_time_level ||','''|| x_start_date ||''','''|| x_end_date ||''','|| x_data_source_pds || '$ORG_PARAM$);';
1691
1692 begin
1693 if (p_split_batch_by_org = 1) then
1694 for orgrec in c_get_plan_orgs
1695 loop
1696 dbms_job.submit(x_job_number, replace(x_job_proc_name, '$ORG_PARAM$', orgrec.organization_id));
1697 commit;
1698 log_debug(x_job_proc_name);
1699 log_debug('Info: msd_dem_sop.load_plan_data_wo_pds - Submitted job -> ' || x_job_number);
1700 end loop;
1701 else
1702
1703 dbms_job.submit(x_job_number, replace(x_job_proc_name, '$ORG_PARAM$', ''));
1704 commit;
1705 log_debug(x_job_proc_name);
1706 log_debug('Info: msd_dem_sop.load_plan_data_wo_pds - Submitted job -> ' || x_job_number);
1707 end if;
1708 exception when others then
1709 retcode := -1;
1710 errbuf := 'Error when submitting jobs for data load from PDS - ' || substr(sqlerrm, 1, 200);
1711 log_message ('Error(5): msd_dem_sop.load_plan_data_wo_pds - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
1712 return;
1713 end;
1714 else
1715 load_series_data (
1716 errbuf,
1717 retcode,
1718 rec.series_id,
1719 p_plan_id,
1720 x_dm_time_level,
1721 NULL,
1722 x_start_date,
1723 x_end_date);
1724
1725 IF (retcode = -1) THEN
1726 errbuf := 'Error when during series data load from PDS- ' || errbuf;
1727 log_message ('Error(6): msd_dem_sop.load_plan_data_wo_pds - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
1728 RETURN;
1729 END IF;
1730
1731 COMMIT;
1732 end if;
1733
1734 log_debug ('Info: msd_dem_sop.load_plan_data_wo_pds - End Of Loop for series id - ' || to_number(rec.series_id) || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1735 END LOOP;
1736
1737 EXCEPTION
1738 WHEN OTHERS THEN
1739 log_message ('Exception(1): msd_dem_sop.load_plan_data_wo_pds - Series(' || x_series_id || ') ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || substr(sqlerrm, 1, 200));
1740 END LOAD_PLAN_DATA_WO_PDS;
1741
1742 /* Load supply plan data collects data from ODS for series
1743 * 1) Work Order Demand 2) Required Resource Plan (WO)
1744 * collects for aggr date range of the plan.
1745 *
1746 */
1747 PROCEDURE LOAD_PLAN_DATA_WO_ODS (
1748 p_plan_id IN NUMBER,
1749 p_start_date IN VARCHAR2,
1750 p_end_date IN VARCHAR2,
1751 p_aggregation_start_date IN VARCHAR2,
1752 p_dm_time_level IN NUMBER,
1753 p_use_jobs IN NUMBER default 0,
1754 p_split_batch_by_org IN NUMBER default 0,
1755 p_job_check_interval IN NUMBER default 60,
1756 p_jobs_timeout IN NUMBER default 86400
1757
1758 ) IS
1759
1760 CURSOR c_get_all_series
1761 IS
1762 SELECT series_id FROM msd_dem_series
1763 WHERE
1764 series_id IN (115, 116);
1765
1766 CURSOR c_get_plan_orgs
1767 IS
1768 SELECT organization_id FROM msc_plan_organizations
1769 WHERE plan_id = p_plan_id;
1770
1771
1772 errbuf VARCHAR2(200) := NULL;
1773 retcode VARCHAR2(100) := NULL;
1774 x_start_date VARCHAR2(100) := NULL;
1775 x_end_date VARCHAR2(100) := NULL;
1776 x_aggregation_start_date VARCHAR2(100) :=NULL;
1777 x_data_source_pds NUMBER := NULL ;
1778 x_job_proc_name VARCHAR2(800) := NULL;
1779 x_dm_time_level NUMBER := NULL;
1780 x_dm_time_bucket VARCHAR2(30) := NULL;
1781 x_job_number BINARY_INTEGER := NULL;
1782 x_series_id NUMBER := NULL;
1783 x_small_sql varchar2(10000) :=NULL;
1784 BEGIN
1785
1786 log_debug ('Entering: msd_dem_sop.load_plan_data_wo_ods ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1787
1788 x_dm_time_level :=p_dm_time_level;
1789
1790 if (p_use_jobs = 1) then
1791 log_debug ('Info:loading series data from ODS using DB jobs');
1792 end if;
1793
1794 if (p_split_batch_by_org = 1) then
1795 log_debug ('Info: series data load from ODS using batches split by org');
1796 end if;
1797
1798 log_debug ('Info: load all series data except costs');
1799 FOR rec IN c_get_all_series
1800 LOOP
1801 log_debug ('Info: msd_dem_sop.load_plan_data_wo_ods - Start Of Loop for series id - ' || to_number(rec.series_id) || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1802
1803 x_data_source_pds :=0 ;
1804 x_start_date := p_aggregation_start_date;
1805 x_series_id := rec.series_id;
1806 x_end_date := p_end_date;
1807 if(to_date(p_aggregation_start_date,'yyyy-mm-dd HH24:MI:SS')<= to_date(p_start_date,'yyyy-mm-dd HH24:MI:SS')) then
1808 x_data_source_pds :=3;
1809 end if;
1810
1811 if (p_use_jobs = 1) then
1812
1813 x_job_proc_name := 'msd_dem_sop.load_series_data_batch(' || rec.series_id || ', ' || p_plan_id || ', ' || x_dm_time_level ||','''|| x_start_date
1814 ||''','''|| x_end_date ||''','|| x_data_source_pds || '$ORG_PARAM$);';
1815
1816 begin
1817 if (p_split_batch_by_org = 1) then
1818
1819 for orgrec in c_get_plan_orgs
1820 loop
1821
1822 dbms_job.submit(x_job_number, replace(x_job_proc_name, '$ORG_PARAM$', orgrec.organization_id));
1823 commit;
1824 log_debug(x_job_proc_name);
1825 log_debug('Info: msd_dem_sop.load_plan_data_wo_ods - Submitted job -> ' || x_job_number);
1826 end loop;
1827 else
1828
1829 dbms_job.submit(x_job_number, replace(x_job_proc_name, '$ORG_PARAM$',''));
1830 commit;
1831 log_debug(x_job_proc_name);
1832 log_debug('Info: msd_dem_sop.load_plan_data_wo_ods - Submitted job -> ' || x_job_number);
1833 end if;
1834 exception when others then
1835 retcode := -1;
1836 errbuf := 'Error when submitting jobs for data load from ODS- ' || substr(sqlerrm, 1, 200);
1837 log_message ('Error(5): msd_dem_sop.load_plan_data_wo_ods - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
1838 return;
1839 end;
1840 else
1841
1842
1843 load_series_data (
1844 errbuf,
1845 retcode,
1846 rec.series_id,
1847 p_plan_id,
1848 x_dm_time_level,
1849 NULL,
1850 x_start_date,
1851 x_end_date,
1852 x_data_source_pds);
1853
1854 IF (retcode = -1) THEN
1855 errbuf := 'Error when during series data load from ODS - ' || errbuf;
1856 log_message ('Error(6): msd_dem_sop.load_plan_data_wo_ods - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
1857 RETURN;
1858 END IF;
1859
1860 COMMIT;
1861 end if;
1862
1863 log_debug ('Info: msd_dem_sop.load_plan_data_wo_ods - End Of Loop for series id - ' || to_number(rec.series_id) || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1864 END LOOP;
1865
1866 EXCEPTION
1867 WHEN OTHERS THEN
1868 log_message ('Exception: msd_dem_sop.load_plan_data_wo_ods - Series(' || x_series_id || ') ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || substr(sqlerrm, 1, 200));
1869 END LOAD_PLAN_DATA_WO_ODS;
1870
1871 /* Load supply plan data striped by workorder attributes */
1872 procedure LOAD_PLAN_DATA_WO(errbuf OUT NOCOPY VARCHAR2,
1873 retcode OUT NOCOPY VARCHAR2,
1874 p_plan_id IN NUMBER,
1875 p_use_jobs IN NUMBER default 0,
1876 p_split_batch_by_org IN NUMBER default 0,
1877 p_job_check_interval IN NUMBER default 60,
1878 p_jobs_timeout IN NUMBER default 86400)
1879 is
1880 CURSOR c_get_all_series
1881 IS
1882 SELECT series_id FROM msd_dem_series
1883 WHERE
1884 series_id IN (105, 107, 115, 116, 117, 118);
1885
1886 CURSOR c_get_cost_series
1887 IS
1888 SELECT series_id FROM msd_dem_series
1889 WHERE
1890 series_id IN (119, 120);
1891
1892 CURSOR c_get_plan_orgs
1893 IS
1894 SELECT organization_id FROM msc_plan_organizations
1895 WHERE plan_id = p_plan_id;
1896
1897 x_dm_time_level NUMBER := NULL;
1898 x_dm_time_bucket VARCHAR2(30) := NULL;
1899 x_job_number BINARY_INTEGER := NULL;
1900 x_job_proc_name VARCHAR2(200) := NULL;
1901
1902 cursor get_plan_info is
1903 select sr_instance_id, compile_designator, plan_type, curr_start_date, cutoff_date
1904 from msc_plans
1905 where plan_id = p_plan_id;
1906
1907 Type dem_sched is Ref Cursor;
1908 c_dem_sched dem_sched;
1909
1910 cursor get_instances is
1911 select instance_id
1912 from msc_apps_instances;
1913
1914 l_master_org NUMBER := NULL;
1915 l_category_set_id NUMBER := NULL;
1916
1917 l_master_string VARCHAR2(1000) := NULL;
1918 l_category_string VARCHAR2(1000) := NULL;
1919
1920 l_dem_sched varchar2(2000);
1921 l_instance_id number;
1922 l_plan_name varchar2(250);
1923 l_plan_type number;
1924 l_start_date date;
1925 l_end_date date;
1926 l_stmt varchar2(10000);
1927 l_stmt1 varchar2(10000);
1928
1929 l_aggregation_start_date date;
1930 x_start_date varchar2(100);
1931 x_end_date varchar2(100);
1932 x_aggregation_start_date varchar2(100);
1933 x_dem_sched varchar2(2000):= NULL;
1934 x_dem_plan_type varchar2(100) := null;
1935 x_small_sql varchar2(10000) :=NULL;
1936 x_data_source_pds NUMBER:=NULL;
1937
1938 begin
1939
1940 log_debug('Entering: msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1941
1942 -- populate msd_dem_day_dates
1943 push_time_data (
1944 errbuf,
1945 retcode);
1946 IF (retcode = -1) THEN
1947 log_message ('Error(1): msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
1948 RETURN;
1949 END IF;
1950
1951 /* Get the lowest time bucket */
1952 x_dm_time_bucket := msd_dem_common_utilities.dm_time_level;
1953 IF (x_dm_time_bucket IS NULL) THEN
1954 retcode := -1;
1955 errbuf := 'Unable to get lowest time bucket';
1956 log_message('Error(2): msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
1957 RETURN;
1958 ELSIF (upper(x_dm_time_bucket) = 'DAY') THEN
1959 x_dm_time_level := 1;
1960 ELSE
1961 x_dm_time_level := 2;
1962 END IF;
1963
1964 open get_plan_info;
1965 fetch get_plan_info into l_instance_id, l_plan_name, l_plan_type, l_start_date, l_end_date;
1966 close get_plan_info;
1967
1968 -- Bug#14151347
1969 l_aggregation_start_date := GET_AGGREGATION_START_DATE (p_plan_id );
1970
1971 if l_plan_type = 6 then
1972 retcode := -1;
1973 errbuf := 'Plan data load with workorder attributes is not supported for plan type ' || l_plan_type;
1974 log_message('Error(3): msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
1975 return;
1976 end if;
1977
1978 -- bug 14693176 kkhatri
1979 l_stmt := 'select input_name from msc_plan_sched_v where plan_id = ' ||p_plan_id || ' group by input_name ';
1980
1981
1982 open c_dem_sched for l_stmt;
1983 loop
1984 fetch c_dem_sched into l_dem_sched;
1985 exit when c_dem_sched%NOTFOUND;
1986 x_dem_sched := x_dem_sched ||', ' || l_dem_sched;
1987 end loop;
1988 close c_dem_sched;
1989
1990 x_dem_sched := substr(x_dem_sched, 2);
1991
1992 l_stmt := ' SELECT spd.from_date FROM ' || g_schema || '.supply_plan sp, '
1993 || g_schema || '.supply_plan_dates spd '
1994 || ' WHERE sp.plan_id = ' || p_plan_id
1995 || ' AND spd.supply_plan_id = sp.supply_plan_id ';
1996 EXECUTE IMMEDIATE l_stmt INTO l_start_date;
1997
1998 msd_dem_query_utilities.get_query(retcode, l_stmt, 'MSD_DEM_PLANS', l_instance_id);
1999 if l_stmt is null then
2000 retcode := -1;
2001 errbuf := 'Unable to get query for entity MSD_DEM_PLANS';
2002 log_message('Error(4): msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
2003 return;
2004 end if;
2005
2006 l_stmt := replace(l_stmt, 'C_SCHEMANAME', g_schema);
2007
2008 IF (l_plan_type in (101, 102, 103, 105)) THEN
2009 x_dem_plan_type := 'Rapid Plan';
2010 ELSE
2011 x_dem_plan_type := 'ASCP';
2012 END IF;
2013 execute immediate l_stmt using l_plan_name, l_plan_name, x_dem_plan_type, 2, l_start_date, l_end_date, p_plan_id, substr(x_dem_sched,1,200);
2014
2015 -- Bug#14151347
2016 if(l_aggregation_start_date < l_end_date) then
2017 x_start_date :=to_char(l_start_date,'yyyy-mm-dd HH24:MI:SS');
2018 x_end_date :=to_char(l_end_date,'yyyy-mm-dd HH24:MI:SS');
2019 x_aggregation_start_date :=to_char(l_aggregation_start_date,'yyyy-mm-dd HH24:MI:SS');
2020
2021 if ( p_use_jobs = 0 ) THEN
2022 msd_dem_sop.load_plan_data_wo_ods(p_plan_id ,x_start_date ,x_end_date ,x_aggregation_start_date ,x_dm_time_level ,p_use_jobs ,p_split_batch_by_org , p_job_check_interval , p_jobs_timeout );
2023 else
2024 x_job_proc_name := 'msd_dem_sop.load_plan_data_wo_ods(' || p_plan_id || ', ''' || x_start_date || ''', ''' || x_end_date || ''', ''' || x_aggregation_start_date || ''', ' || x_dm_time_level || ', ' ;
2025 x_job_proc_name :=x_job_proc_name || p_use_jobs || ', ' || p_split_batch_by_org || ', ' || p_job_check_interval || ', ' || p_jobs_timeout || ');';
2026 log_debug(x_job_proc_name);
2027 dbms_job.submit(x_job_number,x_job_proc_name);
2028 commit;
2029 end IF;
2030 commit;
2031 END IF;
2032
2033 x_start_date :=to_char(l_start_date,'yyyy-mm-dd HH24:MI:SS');
2034 x_end_date :=to_char(l_end_date,'yyyy-mm-dd HH24:MI:SS');
2035 x_aggregation_start_date :=to_char(l_aggregation_start_date,'yyyy-mm-dd HH24:MI:SS');
2036
2037 if ( p_use_jobs = 0 ) THEN
2038 msd_dem_sop.load_plan_data_wo_pds(p_plan_id ,x_start_date ,x_end_date ,x_aggregation_start_date ,x_dm_time_level ,p_use_jobs ,p_split_batch_by_org , p_job_check_interval , p_jobs_timeout );
2039 else
2040 x_job_proc_name := 'msd_dem_sop.load_plan_data_wo_pds(' || p_plan_id || ', ''' || x_start_date || ''', ''' || x_end_date || ''', ''' || x_aggregation_start_date || ''', ' || x_dm_time_level || ', ' ;
2041 x_job_proc_name :=x_job_proc_name || p_use_jobs || ', ' || p_split_batch_by_org || ', ' || p_job_check_interval || ', ' || p_jobs_timeout || ');';
2042 log_debug(x_job_proc_name);
2043 dbms_job.submit(x_job_number,x_job_proc_name);
2044 commit;
2045 end IF;
2046 commit;
2047 --check for job completion
2048 IF ( p_use_jobs = 1 ) THEN
2049 log_debug ('Info: msd_dem_sop.load_plan_data_wo - Waiting for jobs to complete - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2050 check_jobs(errbuf, retcode, p_job_check_interval, p_jobs_timeout);
2051 IF (retcode = -1) THEN
2052 errbuf := 'Error when checking for data load jobs - ' || errbuf;
2053 log_message ('Error(7): msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
2054 RETURN;
2055 END IF;
2056 log_debug ('Info: msd_dem_sop.load_plan_data_wo - Jobs complete - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2057 END IF;
2058
2059 --remove records without data for any series
2060 --(from MSD_ST_SOP_WO_DEMANDS, MSD_ST_SOP_WO_REQR_RES_CAP, MSD_ST_SOP_WO_SUPPLIES, BIIO_OTHER_PLAN_DATA)
2061 l_stmt := 'DELETE FROM MSD_ST_SOP_WO_DEMANDS ' ||
2062 ' WHERE NVL(CONSTRAINED_FCST, 0) = 0 AND NVL(DEPENDENT_DEMAND, 0) = 0 ' ||
2063 ' AND NVL(PLANNED_SHIPMENTS, 0) = 0 AND NVL(WORK_ORDER_DEMAND, 0) = 0 ';
2064 log_debug ('Info: msd_dem_sop.load_plan_data_wo - Remove records without data for any series in MSD_ST_SOP_WO_DEMANDS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2065 log_debug (l_stmt);
2066 execute immediate l_stmt;
2067
2068 l_stmt := 'DELETE FROM MSD_ST_SOP_WO_REQR_RES_CAP ' ||
2069 ' WHERE NVL(REQUIRED_RES_PLAN, 0) = 0 ';
2070 log_debug ('Info: msd_dem_sop.load_plan_data_wo - Remove records without data for any series in MSD_ST_SOP_WO_REQR_RES_CAP - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2071 log_debug (l_stmt);
2072 execute immediate l_stmt;
2073
2074 l_stmt := 'DELETE FROM MSD_ST_SOP_WO_SUPPLIES ' ||
2075 ' WHERE NVL(PROD_PLAN, 0) = 0 AND NVL(INTRANSIT_DEFECTIVES, 0) = 0 ' ||
2076 ' AND NVL(REQUIRED_SUPPLIER_CAP, 0) = 0 AND NVL(BEGINNING_ONHAND, 0) = 0 ' ||
2077 ' AND NVL(BEGINNING_ONHAND_DEFECTIVES, 0) = 0 ';
2078 log_debug ('Info: msd_dem_sop.load_plan_data_wo - Remove records without data for any series in MSD_ST_SOP_WO_SUPPLIES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2079 log_debug (l_stmt);
2080 execute immediate l_stmt;
2081
2082 l_stmt := 'DELETE FROM ' || g_schema || '.BIIO_OTHER_PLAN_DATA ' ||
2083 ' WHERE NVL(AVAIL_RES_STD_CAP, 0) = 0 AND NVL(AVAIL_SUP_STD_CAP, 0) = 0 ' ||
2084 ' AND NVL(SAFETY_STK, 0) = 0 ';
2085 log_debug ('Info: msd_dem_sop.load_plan_data_wo - Remove records without data for any series in MSD_ST_SOP_WO_DEMANDS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2086 log_debug (l_stmt);
2087 execute immediate l_stmt;
2088
2089 commit;
2090
2091 /* update AG, CC attributes in MSD_ST_SOP_WO_DEMANDS, MSD_ST_SOP_WO_REQR_RES_CAP, MSD_ST_SOP_WO_SUPPLIES*/
2092 -- Bug#14151347: added date filter with bind variable for aggregation_start_date
2093
2094 l_stmt := 'MERGE INTO C_TABLE_NAME a
2095 USING
2096 (select distinct
2097 msup.sr_instance_id,
2098 msup.plan_id,
2099 wsup.organization_id,
2100 wsup.wo_supply_id,
2101 decode(nvl(maintenance_object_source,2),
2102 1, (select item_name from msc_system_items
2103 where sr_instance_id = msup.sr_instance_id and inventory_item_id = msup.asset_item_id
2104 and plan_id = msup.plan_id and organization_id = msup.organization_id),
2105 msup.product_classification
2106 ) asset_group,
2107 decode(nvl(maintenance_object_source,2),
2108 1, msup.class_code,
2109 msup.maintenance_reqt
2110 ) class_code
2111 from (select distinct sr_instance_id, plan_id, organization_id, wo_supply_id, sdate from C_TABLE_NAME) wsup,
2112 msc_supplies msup
2113 where msup.sr_instance_id = wsup.sr_instance_id
2114 and msup.plan_id= wsup.plan_id
2115 and msup.transaction_id = wsup.wo_supply_id
2116 and wsup.sdate < :1
2117 ) b
2118 ON (
2119 a.sr_instance_id = b.sr_instance_id
2120 and a.plan_id = b.plan_id
2121 and a.organization_id = b.organization_id
2122 and a.wo_supply_id = b.wo_supply_id
2123 )
2124 WHEN MATCHED THEN
2125 UPDATE SET
2126 a.asset_group = b.asset_group,
2127 a.class_code = b.class_code ';
2128
2129 -- Bug#14151347 - added l_aggregation_start_date
2130 log_debug ('Info: msd_dem_sop.load_plan_data_wo - Updating AssetGroup and ClassCode in MSD_ST_SOP_WO_DEMANDS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2131 l_stmt1 := replace(l_stmt, 'C_TABLE_NAME', 'MSD_ST_SOP_WO_DEMANDS');
2132 log_debug (l_stmt1);
2133 execute immediate l_stmt1 using l_aggregation_start_date;
2134
2135 log_debug ('Info: msd_dem_sop.load_plan_data_wo - Updating Asset Group and Class Code in MSD_ST_SOP_WO_REQR_RES_CAP - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2136 l_stmt1 := replace(l_stmt, 'C_TABLE_NAME', 'MSD_ST_SOP_WO_REQR_RES_CAP');
2137 log_debug (l_stmt1);
2138 execute immediate l_stmt1 using l_aggregation_start_date;
2139
2140 log_debug ('Info: msd_dem_sop.load_plan_data_wo - Updating Asset Group and Class Code in MSD_ST_SOP_WO_SUPPLIES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2141 l_stmt1 := replace(l_stmt, 'C_TABLE_NAME', 'MSD_ST_SOP_WO_SUPPLIES');
2142 log_debug (l_stmt1);
2143 execute immediate l_stmt1 using l_aggregation_start_date;
2144
2145
2146 -- insert records to BIIO_SUPPLY_PLAN_DATA_WO from ST tables
2147 l_stmt := ' INSERT /*+ APPEND */ INTO ' || g_schema || '.BIIO_SUPPLY_PLAN_DATA_WO NOLOGGING '
2148 || '(SDATE, LEVEL1, LEVEL2, LEVEL3, LEVEL4, LEVEL5, LEVEL6, LEVEL7, LEVEL8, '
2149 || ' PROD_PLAN, REQUIRED_SUP_CAP, REQUIRED_RES_PLAN, BEGINNING_ON_HAND, '
2150 || ' CONSTRAINED_FCST, DEPENDENT_DEMAND, PLANNED_SHIPMENTS, INTRANSIT_DEFECTIVES, '
2151 || ' BEGINNING_ONHAND_DEFECTIVES, WORK_ORDER_DEMAND) '
2152 || ' SELECT '
2153 || ' SDATE, :1, nvl(ASSET_GROUP, du.default_code), nvl(CLASS_CODE, du.default_code), ITEM_CODE, ORGANIZATION_CODE, '
2154 || ' du.default_code, SITE_CODE, du.default_code, '
2155 || ' sum(PROD_PLAN), sum(REQUIRED_SUPPLIER_CAP), sum(REQUIRED_RES_PLAN), sum(BEGINNING_ONHAND), '
2156 || ' sum(CONSTRAINED_FCST), sum(DEPENDENT_DEMAND), sum(PLANNED_SHIPMENTS), sum(INTRANSIT_DEFECTIVES), '
2157 || ' sum(BEGINNING_ONHAND_DEFECTIVES), sum(WORK_ORDER_DEMAND) '
2158 || ' from ( '
2159 || ' select SDATE, ASSET_GROUP, CLASS_CODE, ITEM_CODE, ORGANIZATION_CODE, dfu.default_code site_code, '
2160 || ' null PROD_PLAN, null REQUIRED_SUPPLIER_CAP, null REQUIRED_RES_PLAN, null BEGINNING_ONHAND, '
2161 || ' CONSTRAINED_FCST, DEPENDENT_DEMAND, PLANNED_SHIPMENTS, null INTRANSIT_DEFECTIVES, '
2162 || ' null BEGINNING_ONHAND_DEFECTIVES, WORK_ORDER_DEMAND'
2163 || ' from MSD_ST_SOP_WO_DEMANDS, '
2164 || ' (select msd_dem_sr_util.get_null_code default_code from dual) dfu '
2165 || ' UNION ALL '
2166 || ' select SDATE, ASSET_GROUP, CLASS_CODE, RESOURCE_CODE ITEM_CODE, ORGANIZATION_CODE, dfu.default_code site_code, '
2167 || ' null PROD_PLAN, null REQUIRED_SUPPLIER_CAP, REQUIRED_RES_PLAN, null BEGINNING_ONHAND, '
2168 || ' null CONSTRAINED_FCST, null DEPENDENT_DEMAND, null PLANNED_SHIPMENTS, null INTRANSIT_DEFECTIVES, '
2169 || ' null BEGINNING_ONHAND_DEFECTIVES, null WORK_ORDER_DEMAND'
2170 || ' from MSD_ST_SOP_WO_REQR_RES_CAP, '
2171 || ' (select msd_dem_sr_util.get_null_code default_code from dual) dfu '
2172 || ' UNION ALL '
2173 || ' select SDATE, ASSET_GROUP, CLASS_CODE, ITEM_CODE, ORGANIZATION_CODE, SITE_CODE, '
2174 || ' PROD_PLAN, REQUIRED_SUPPLIER_CAP, null REQUIRED_RES_PLAN, BEGINNING_ONHAND, '
2175 || ' null CONSTRAINED_FCST, null DEPENDENT_DEMAND, null PLANNED_SHIPMENTS, INTRANSIT_DEFECTIVES, '
2176 || ' BEGINNING_ONHAND_DEFECTIVES, null WORK_ORDER_DEMAND'
2177 || ' from MSD_ST_SOP_WO_SUPPLIES '
2178 || ' ) data, '
2179 || ' (select msd_dem_sr_util.get_null_code default_code from dual) du '
2180 || ' group by '
2181 || ' SDATE, :2, ASSET_GROUP, CLASS_CODE, ITEM_CODE, ORGANIZATION_CODE, '
2182 || ' du.default_code, SITE_CODE, du.default_code';
2183
2184 log_debug('Info: msd_dem_sop.load_plan_data_wo - Populating BIIO_SUPPLY_PLAN_DATA_WO - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2185 log_debug (l_stmt);
2186 execute immediate l_stmt using l_plan_name, l_plan_name;
2187
2188 log_debug ('Info: msd_dem_sop.load_plan_data_wo - load cost series data');
2189 FOR rec IN c_get_cost_series
2190 LOOP
2191 log_debug ('Info: msd_dem_sop.load_plan_data_wo - Start Of Loop for series id - ' || to_number(rec.series_id) || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2192
2193 if (p_use_jobs = 1) then
2194
2195 x_start_date :=NULL;
2196 x_end_date :=NULL;
2197 x_data_source_pds :=1;
2198 x_job_proc_name := 'msd_dem_sop.load_series_data_batch(' || rec.series_id || ', ' || p_plan_id || ', ' || x_dm_time_level ||','''|| x_start_date ||''','''|| x_end_date ||''','|| x_data_source_pds || '$ORG_PARAM$);';
2199
2200 begin
2201 if (p_split_batch_by_org = 1) then
2202 for orgrec in c_get_plan_orgs
2203 loop
2204 dbms_job.submit(x_job_number, replace(x_job_proc_name, '$ORG_PARAM$', ', ' || orgrec.organization_id));
2205 commit;
2206 log_debug('Info: msd_dem_sop.load_plan_data_wo - Submitted job -> ' || x_job_number);
2207 end loop;
2208 else
2209 dbms_job.submit(x_job_number, replace(x_job_proc_name, '$ORG_PARAM$', ''));
2210 commit;
2211 log_debug('Info: msd_dem_sop.load_plan_data_wo - Submitted job -> ' || x_job_number);
2212 end if;
2213 exception when others then
2214 retcode := -1;
2215 errbuf := 'Error when submitting jobs for data load - ' || substr(sqlerrm, 1, 200);
2216 log_message ('Error(8): msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
2217 return;
2218 end;
2219 else
2220 load_series_data (
2221 errbuf,
2222 retcode,
2223 rec.series_id,
2224 p_plan_id,
2225 x_dm_time_level);
2226
2227 IF (retcode = -1) THEN
2228 errbuf := 'Error when during series data load - ' || errbuf;
2229 log_message ('Error(9): msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
2230 RETURN;
2231 END IF;
2232
2233 COMMIT;
2234 end if;
2235
2236 log_debug ('Info: msd_dem_sop.load_plan_data_wo - End Of Loop for series id - ' || to_number(rec.series_id) || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2237 END LOOP;
2238
2239 --check for job completion
2240 IF ( p_use_jobs = 1 ) THEN
2241 log_debug ('Info: msd_dem_sop.load_plan_data_wo - Waiting for jobs to complete - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2242 check_jobs(errbuf, retcode, p_job_check_interval, p_jobs_timeout);
2243 IF (retcode = -1) THEN
2244 errbuf := 'Error when checking for data load jobs - ' || errbuf;
2245 log_message ('Error(10): msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
2246 RETURN;
2247 END IF;
2248 log_debug ('Info: msd_dem_sop.load_plan_data_wo - Jobs complete - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2249 END IF;
2250
2251 -- get population based on BIIO_OTHER_PLAN_DATA
2252 log_debug('Info: msd_dem_sop.load_plan_data_wo - get population based on BIIO_OTHER_PLAN_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2253 msd_dem_query_utilities.get_query(retcode, l_stmt, 'MSD_DEM_PLANS_POP_EBS', l_instance_id);
2254
2255 if l_stmt is null then
2256 retcode := -1;
2257 errbuf := 'Unable to get query for entity MSD_DEM_PLANS_POP_EBS';
2258 log_message('Error(11): msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
2259 return;
2260 end if;
2261
2262 l_stmt := replace(l_stmt, 'C_SCHEMANAME', g_schema);
2263 execute immediate l_stmt using l_plan_name, l_start_date, l_end_date, p_plan_id, p_plan_id, p_plan_id, p_plan_id, p_plan_id, p_plan_id;
2264
2265 -- get population based on BIIO_SUPPLY_PLAN_DATA_WO
2266 log_debug('Info: msd_dem_sop.load_plan_data_wo - get population based on MSD_DEM_PLANS_POP_EBS_WO - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2267 msd_dem_query_utilities.get_query(retcode, l_stmt, 'MSD_DEM_PLANS_POP_EBS_WO', l_instance_id);
2268
2269 if l_stmt is null then
2270 retcode := -1;
2271 errbuf := 'Unable to get query for entity MSD_DEM_PLANS_POP_EBS_WO';
2272 log_message('Error(12): msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
2273 return;
2274 end if;
2275
2276 l_stmt := replace(l_stmt, 'C_SCHEMANAME', g_schema);
2277 execute immediate l_stmt using l_plan_name, l_start_date, l_end_date, p_plan_id, p_plan_id, p_plan_id, p_plan_id;
2278
2279
2280 -- load item staging table
2281 log_debug('Info: msd_dem_sop.load_plan_data_wo - load item staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2282 msd_dem_query_utilities.get_query(retcode, l_stmt, 'EQ_SOP_WO_ITEM_TMPL', l_instance_id);
2283
2284 if l_stmt is null then
2285 retcode := -1;
2286 errbuf := 'Unable to get query for entity EQ_SOP_WO_ITEM_TMPL';
2287 log_message('Error(13): msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
2288 return;
2289 end if;
2290
2291 l_master_string := 'decode(itt.sr_instance_id';
2292 l_category_string := 'decode(item_master.sr_instance_id';
2293
2294 for get_instances_rec in get_instances loop
2295 l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
2296 get_instances_rec.instance_id,
2297 'MSD_DEM_MASTER_ORG'));
2298 l_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
2299 get_instances_rec.instance_id,
2300 'MSD_DEM_CATEGORY_SET_NAME'));
2301
2302 l_master_string := l_master_string || ', ' || get_instances_rec.instance_id || ', ' || l_master_org ;
2303 l_category_string := l_category_string || ', ' || get_instances_rec.instance_id || ', ' || nvl(to_char(l_category_set_id), 'null');
2304
2305 end loop;
2306
2307 l_master_string := l_master_string || ')';
2308 l_category_string := l_category_string || ')';
2309
2310 l_stmt := replace(l_stmt, 'C_SCHEMANAME', g_schema);
2311 l_stmt := replace(l_stmt, 'C_PLAN_ID', p_plan_id);
2312 l_stmt := replace(l_stmt, 'C_INSTANCE_STRING', l_master_string);
2313 l_stmt := replace(l_stmt, 'C_CATEGORY_STRING', l_category_string);
2314 -- bug#14694463 kkhatri
2315 l_stmt := replace(l_stmt, 'C_MATERIAL', CS_DEM_MATERIAL);
2316 l_stmt := replace(l_stmt, 'C_RESOURCE', CS_DEM_RESOURCE);
2317
2318
2319 log_debug(l_stmt);
2320 execute immediate l_stmt;
2321
2322 -- load location staging table
2323 log_debug('Info: msd_dem_sop.load_plan_data_wo - load location staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2324 msd_dem_collect_level_types.collect_levels(errbuf, retcode, l_instance_id, 1, p_plan_id);
2325 IF (retcode = -1) THEN
2326 errbuf := 'Error in collect_level_types (location) - ' || errbuf;
2327 log_message ('Error(14): msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
2328 RETURN;
2329 END IF;
2330
2331 commit;
2332
2333 log_debug ('Exiting: msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2334 EXCEPTION
2335 WHEN OTHERS THEN
2336 retcode := -1;
2337 errbuf := substr(SQLERRM,1,200);
2338 log_message ('Exception: msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);
2339 RETURN;
2340 end LOAD_PLAN_DATA_WO;
2341
2342
2343 /* procedure to be used for loading series data via db job */
2344 PROCEDURE LOAD_SERIES_DATA_BATCH (
2345 p_series_id in number,
2346 p_plan_id in number,
2347 p_dm_time_level in number,
2348 p_start_date IN VARCHAR2 DEFAULT NULL ,
2349 p_end_date IN VARCHAR2 DEFAULT NULL ,
2350 p_data_source_pds IN NUMBER DEFAULT 1,
2351 p_filter_org_id in number default null
2352 ) IS
2353 x_errbuf VARCHAR2(200) := NULL;
2354 x_retcode VARCHAR2(100) := NULL;
2355 BEGIN
2356 log_debug ('Entering: msd_dem_sop.load_series_data_batch - Series(' || p_series_id || ') ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2357
2358 load_series_data(x_errbuf, x_retcode, p_series_id, p_plan_id, p_dm_time_level, p_filter_org_id, p_start_date, p_end_date, p_data_source_pds );
2359 IF (x_retcode = -1)
2360 THEN
2361 log_message ('Error(1): msd_dem_sop.load_series_data_batch - Series(' || p_series_id || ') ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || x_errbuf);
2362 END IF;
2363
2364 log_debug ('Exiting: msd_dem_sop.load_series_data_batch - Series(' || p_series_id || ') ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2365 EXCEPTION
2366 WHEN OTHERS THEN
2367 log_message ('Exception(1): msd_dem_sop.load_series_data_batch - Series(' || p_series_id || ') ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || substr(sqlerrm, 1, 200));
2368 END LOAD_SERIES_DATA_BATCH;
2369
2370
2371 /*** PUBLIC FUNCTIONS **/
2372
2373 /* Function to get base work order supply ID for a given demand */
2374 FUNCTION GET_WO_SUPPLY_ID_FROM_DEMAND(
2375 p_plan_id IN NUMBER,
2376 p_sr_instance_id IN NUMBER,
2377 p_organization_id IN NUMBER,
2378 p_demand_id IN NUMBER,
2379 p_pegging_id IN NUMBER DEFAULT NULL -- Bug#13429046
2380 ) RETURN NUMBER
2381 IS
2382 BEGIN
2383 return GET_BASE_WO_TXN_ID (p_sr_instance_id, p_plan_id, p_demand_id,0,p_pegging_id); -- Bug#13429046
2384 END;
2385
2386 /* Function to get base work order supply ID for a given supply */
2387 FUNCTION GET_WO_SUPPLY_ID_FROM_SUPPLY(
2388 p_plan_id IN NUMBER,
2389 p_sr_instance_id IN NUMBER,
2390 p_organization_id IN NUMBER,
2391 p_supply_id IN NUMBER,
2392 p_pegging_id IN NUMBER DEFAULT NULL -- Bug#13429046
2393 ) RETURN NUMBER
2394 IS
2395 BEGIN
2396 return GET_BASE_WO_TXN_ID (p_sr_instance_id, p_plan_id, p_supply_id, 1,p_pegging_id); -- Bug#13429046
2397 END;
2398
2399 /*** PUBLIC PROCEDURES ***
2400 * SET_PLAN_ATTRIBUTES
2401 * LOAD_PLAN_DATA
2402 * LOAD_PLAN_MEMBERS
2403 * POST_DOWNLOAD_HOOK
2404 * LOAD_ITEM_COST
2405 * WAIT_UNTIL_DOWNLOAD_COMPLETE
2406 * COLLECT_SCI_DATA
2407 * LAUNCH_SCI_DATA_LOADS
2408 */
2409
2410
2411
2412 procedure set_plan_attributes(p_member_id in number) is
2413
2414 Type pln_id is Ref Cursor;
2415 c_plan_id pln_id;
2416
2417 p_plan_id number;
2418 l_sql varchar2(1000) := NULL;
2419
2420 g_schema varchar2(50);
2421 x_small_sql varchar2(240);
2422
2423
2424 Begin
2425 -- syenamar
2426 -- this code will be executed with definer(apps) rights due to 'AUTHID DEFINER' clause in package spec, so need to specify demantra schema for demantra tables
2427
2428 /* Alter session to APPS */
2429 x_small_sql := 'alter session set current_schema = APPS';
2430 EXECUTE IMMEDIATE x_small_sql;
2431
2432 g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
2433 IF (g_schema IS NULL)
2434 THEN
2435 log_message ('Error: msd_dem_sop.set_plan_attributes - Unable to find schema name');
2436 END IF;
2437
2438 /* Inserting an entry into Integ_Status table
2439 that loading of plan is running. */
2440
2441 l_sql := 'Insert into ' || g_schema || '.integ_status(username, process, stage, status, info, status_date) values (''' || g_schema || ''',' || -- bug 14134116
2442 '''LOAD_PLAN_DATA'',
2443 ''LOAD_PLAN_DATA'',
2444 ''RUNNING'', '
2445 ||''''|| ' ' ||''','
2446 ||''''||sysdate ||''')' ;
2447
2448 execute immediate l_sql;
2449
2450 l_sql := 'select plan_id from ' || g_schema || '.supply_plan
2451 where supply_plan_id = ' ||p_member_id;
2452
2453 /* Get the ASCP or SNO plan_id and call procedure load_plan_gl */
2454
2455 open c_plan_id for l_sql;
2456 fetch c_plan_id into p_plan_id;
2457 close c_plan_id ;
2458
2459 load_plan_gl(p_plan_id, 0);
2460
2461 /* Alter session to demantra schema */
2462 x_small_sql := 'alter session set current_schema = ' || g_schema;
2463 EXECUTE IMMEDIATE x_small_sql;
2464
2465 EXCEPTION
2466 when others then
2467 return;
2468 End set_plan_attributes;
2469
2470
2471
2472
2473 /*
2474 *
2475 */
2476 PROCEDURE LOAD_PLAN_DATA (
2477 p_member_id IN NUMBER,
2478 p_delete_item_pop IN NUMBER DEFAULT 1,
2479 p_delete_dc_site_sc_pop IN NUMBER DEFAULT 1,
2480 p_use_jobs IN NUMBER default 0,
2481 p_split_batch_by_org IN NUMBER default 0,
2482 p_job_check_interval IN NUMBER default 60,
2483 p_jobs_timeout IN NUMBER default 86400)
2484 IS
2485
2486 /*** LOCAL VARIABLES - BEGIN ***/
2487
2488 x_errbuf VARCHAR2(600) := NULL;
2489 x_retcode VARCHAR2(100) := NULL;
2490
2491 x_plan_id NUMBER := NULL;
2492 x_plan_type NUMBER := NULL;
2493 x_small_sql VARCHAR2(600) := NULL;
2494 x_sno_sql VARCHAR2(600) := NULL;
2495 v_demantra_version NUMBER;
2496 l_sql varchar2(1000) := NULL; -- bug 14134116
2497
2498 -- bug#12546892
2499 x_series_01 VARCHAR2(60) := 'SERIES_BEGIN_ON_HAND' ; -- Beginning On Hand
2500 x_series_02 VARCHAR2(60) := 'SERIES_CONST_FCST' ; -- Constrained Forecast
2501 x_series_03 VARCHAR2(60) := 'SERIES_DEP_DEMAND' ; -- Dependent Demand
2502 x_series_04 VARCHAR2(60) := 'SERIES_PLANNED_SHIPMENTS' ; -- Planned Shipments
2503 x_series_05 VARCHAR2(60) := 'SERIES_PROD_PLAN' ; -- Production Plan
2504 x_series_06 VARCHAR2(60) := 'SERIES_REQR_SUPL_CAP' ; -- Required Supplier Capacity
2505
2506 x_opd_dp VARCHAR2(60) := 'PROFILE_OTHER_PLAN_DATA' ;
2507
2508
2509 /*** LOCAL VARIABLES - END ***/
2510
2511 BEGIN
2512
2513 g_schema := C_MSD_DEM_SCHEMA;
2514 /* Alter session to APPS */
2515 x_small_sql := 'alter session set current_schema = APPS';
2516 EXECUTE IMMEDIATE x_small_sql;
2517
2518 /*g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
2519 IF (g_schema IS NULL)
2520 THEN
2521 x_errbuf := 'Error(1): msd_dem_sop.load_plan_data - Unable to find schema name';
2522 EXECUTE IMMEDIATE x_small_sql;
2523 raise_application_error(-20001, x_errbuf);
2524 END IF;*/
2525
2526 /* Alter session to demantra schema */
2527 x_small_sql := 'alter session set current_schema = ' || g_schema;
2528
2529 log_debug ('Entering: msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2530
2531 /* Get plan id of the supply plan */
2532 x_plan_id := get_plan_id (p_member_id);
2533 IF (x_plan_id IS NULL)
2534 THEN
2535 x_errbuf := 'Error(2): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') ||
2536 ' - Unable to get plan id for the given plan scenario member id : ' || to_char(p_member_id);
2537 EXECUTE IMMEDIATE x_small_sql;
2538 raise_application_error(-20002, x_errbuf);
2539 END IF;
2540
2541 /* Get plan type of the supply plan */
2542 x_plan_type := get_plan_type (p_member_id);
2543 IF (x_plan_type IS NULL)
2544 THEN
2545 x_errbuf := 'Error(3): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') ||
2546 ' - Unable to get plan type for the given plan scenario member id : ' || to_char(p_member_id);
2547 EXECUTE IMMEDIATE x_small_sql;
2548 raise_application_error(-20003, x_errbuf);
2549 END IF;
2550
2551 /* Truncate all staging tables */
2552 truncate_staging_tables (
2553 x_errbuf,
2554 x_retcode);
2555 IF (x_retcode = -1)
2556 THEN
2557 x_errbuf := 'Error(4): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') ||
2558 ' - ' || x_errbuf ;
2559 EXECUTE IMMEDIATE x_small_sql;
2560 raise_application_error(-20004, x_errbuf);
2561 END IF;
2562
2563 if ( nvl(fnd_profile.value('MSD_DEM_ENABLE_SUPPLY_PLAN_WO'), 2) = 1 ) then
2564 --truncate wo staging tables
2565 truncate_wo_staging_tables(
2566 x_errbuf,
2567 x_retcode);
2568 IF (x_retcode = -1) THEN
2569 x_errbuf := 'Error(5): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') ||' - ' || x_errbuf ;
2570 EXECUTE IMMEDIATE x_small_sql;
2571 raise_application_error(-20005, x_errbuf);
2572 END IF;
2573 end if;
2574
2575 -- bug#12546892
2576 IF (nvl(fnd_profile.value('MSD_DEM_ENABLE_SUPPLY_PLAN_WO'), 2) = 1) THEN -- profile set to YES
2577
2578 log_message ('INFO: Setting "No load" option and "No Purge" option for series under "other plan data" dp.' );
2579
2580 -- set to "No load" option and "No Purge" option for the series in the data profile
2581 msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_01,2,0);
2582 msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,2,0);
2583 msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,2,0);
2584 msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,2,0);
2585 msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,2,0);
2586 msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_06,2,0,1);
2587
2588 ELSIF (nvl(fnd_profile.value('MSD_DEM_ENABLE_SUPPLY_PLAN_WO'), 2) = 2) THEN -- profile set to NO
2589
2590 log_message ('INFO: Setting "OVERRIDE" option and "No Purge" option for series under "other plan data" dp.' );
2591
2592 -- set to "OVERRIDE" option and "No Purge" option for the series in the data profile
2593 msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_01,0,0);
2594 msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,0,0);
2595 msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,0,0);
2596 msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,0,0);
2597 msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,0,0);
2598 msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_06,0,0,1);
2599
2600
2601 END IF;
2602
2603 /* Call Pre-Download Hook */
2604 pre_download_hook (
2605 x_errbuf,
2606 x_retcode,
2607 x_plan_id);
2608 IF (x_retcode = -1)
2609 THEN
2610 x_errbuf := 'Error(6): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || x_errbuf;
2611 EXECUTE IMMEDIATE x_small_sql;
2612 raise_application_error(-20006, x_errbuf);
2613 END IF;
2614
2615 -- for ASCP or RP plan, load data with workorder attributes if the profile 'MSD_DEM: Enable Supply Plan Download for work orders' is set
2616 if ( (x_plan_type = 0 or x_plan_type = 2) and nvl(fnd_profile.value('MSD_DEM_ENABLE_SUPPLY_PLAN_WO'), 2) = 1 ) then
2617 -- call procedure to load data with WO attributes
2618 load_plan_data_wo(x_errbuf, x_retcode, x_plan_id,p_use_jobs,p_split_batch_by_org,p_job_check_interval,p_jobs_timeout);
2619
2620 if (p_delete_dc_site_sc_pop = 1) then
2621 EXECUTE IMMEDIATE 'DELETE FROM ' || g_schema || '.BIIO_SUPPLY_PLANS_POP WHERE LEVEL_ORDER in (3,4,5)';
2622 COMMIT;
2623 end if;
2624
2625 --alter session back to demantra schema
2626 execute immediate x_small_sql;
2627 IF (x_retcode = -1) THEN
2628 x_errbuf := 'Error(7): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || x_errbuf;
2629 raise_application_error(-20007, x_errbuf);
2630 END IF;
2631
2632 return;
2633 end if;
2634
2635 /* For ASCP plan or RP plan, call Load ASCP Plan */
2636 -- BUG#9211268
2637 --Enable download of RP plan data into demantra
2638 IF (x_plan_type = 0 or x_plan_type = 2)
2639 THEN
2640
2641 push_time_data (
2642 x_errbuf,
2643 x_retcode);
2644 IF (x_retcode = -1)
2645 THEN
2646 log_message ('Error(5): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || x_errbuf);
2647 EXECUTE IMMEDIATE x_small_sql;
2648 RETURN;
2649 END IF;
2650
2651 load_ascp_data (
2652 x_errbuf,
2653 x_retcode,
2654 x_plan_id);
2655 IF (x_retcode = -1)
2656 THEN
2657 log_message ('Error(6): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || x_errbuf);
2658 EXECUTE IMMEDIATE x_small_sql;
2659 RETURN;
2660 END IF;
2661
2662 ELSE /* For SNO plan, call Load SNO Plan */
2663 BEGIN
2664 x_sno_sql := 'BEGIN ' || g_schema || '.SNOP_DATA_LOAD.SNO_LOAD_DATA(''' || x_plan_id || '''); END;';
2665 EXECUTE IMMEDIATE x_sno_sql;
2666 EXCEPTION
2667 WHEN OTHERS THEN
2668 log_message ('Error(7): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || 'Error in call to SNOP_DATA_LOAD.SNO_LOAD_DATA');
2669 EXECUTE IMMEDIATE x_small_sql;
2670 RETURN;
2671 END;
2672 END IF;
2673
2674
2675 /* Load Plan Scenario GL */
2676 load_plan_gl (x_plan_id, 1);
2677
2678 /* Load Plan Resource GL */
2679 load_resource_gl (x_plan_id);
2680
2681 /* Load Item Location */
2682 load_item_locs (x_plan_id);
2683
2684 -- delet item population from staging table only in 7.2, if p_delete_item_pop is true
2685 v_demantra_version := to_number(msd_dem_common_utilities.get_demantra_version);
2686 if (v_demantra_version < 7.3 and p_delete_item_pop = 1) then
2687 -- bug#8266960
2688 EXECUTE IMMEDIATE 'DELETE FROM ' || g_schema || '.BIIO_SUPPLY_PLANS_POP WHERE LEVEL_ORDER = 2';
2689 COMMIT;
2690 end if;
2691
2692 if (p_delete_dc_site_sc_pop = 1) then
2693 EXECUTE IMMEDIATE 'DELETE FROM ' || g_schema || '.BIIO_SUPPLY_PLANS_POP WHERE LEVEL_ORDER in (3,4,5)';
2694 COMMIT;
2695 end if;
2696
2697 log_debug ('Exiting: msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2698 -- bug 14134116
2699 l_sql := 'Insert into ' || g_schema || '.integ_status(username, process, stage, status, info, status_date) values (''' || g_schema || ''',' ||
2700 '''LOAD_PLAN_DATA'',
2701 ''LOAD_PLAN_DATA'',
2702 ''SUCCEEDED'', '
2703 ||''''|| ' ' ||''','
2704 ||''''||sysdate ||''')' ;
2705
2706 /* Alter session to demantra schema */
2707 x_small_sql := 'alter session set current_schema = ' || g_schema;
2708 EXECUTE IMMEDIATE x_small_sql;
2709 log_debug ('Exiting: msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2710
2711
2712 EXCEPTION
2713 WHEN OTHERS THEN
2714 log_message ('Exception: msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2715 log_message (substr(SQLERRM,1,150));
2716
2717 /* Alter session to demantra schema */
2718 x_small_sql := 'alter session set current_schema = ' || g_schema;
2719 EXECUTE IMMEDIATE x_small_sql;
2720
2721 RETURN;
2722
2723 END LOAD_PLAN_DATA;
2724
2725
2726
2727
2728 /*
2729 *
2730 */
2731 PROCEDURE LOAD_PLAN_MEMBERS
2732 IS
2733 x_small_sql varchar2(1000);
2734 BEGIN
2735 g_schema := C_MSD_DEM_SCHEMA;
2736
2737 log_debug ('Entering: msd_dem_sop.load_plan_members - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2738
2739 /* Alter session to APPS */
2740 x_small_sql := 'alter session set current_schema = APPS';
2741 EXECUTE IMMEDIATE x_small_sql;
2742
2743 /*g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
2744 IF (g_schema IS NULL)
2745 THEN
2746 log_message ('Error(1): msd_dem_sop.load_plan_data - Unable to find schema name');
2747 END IF;*/
2748
2749 load_plan_gl(NULL, 0);
2750
2751 /* Alter session to demantra schema */
2752 x_small_sql := 'alter session set current_schema = ' || g_schema;
2753 EXECUTE IMMEDIATE x_small_sql;
2754
2755 log_debug ('Exiting: msd_dem_sop.load_plan_members - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2756 EXCEPTION
2757 WHEN OTHERS THEN
2758 log_message ('Exception: msd_dem_sop.load_plan_members - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2759 log_message (substr(SQLERRM,1,150));
2760
2761 /* Alter session to demantra schema */
2762 x_small_sql := 'alter session set current_schema = ' || g_schema;
2763 EXECUTE IMMEDIATE x_small_sql;
2764 RETURN;
2765
2766 END LOAD_PLAN_MEMBERS;
2767
2768
2769
2770
2771 /*
2772 *
2773 */
2774 PROCEDURE POST_DOWNLOAD_HOOK (
2775 p_member_id IN NUMBER )
2776 IS
2777 BEGIN
2778 --log_debug ('Entering: msd_dem_sop.post_download_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2779 NULL;
2780 --log_debug ('Exiting: msd_dem_sop.post_download_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2781 EXCEPTION
2782 WHEN OTHERS THEN
2783 --log_message ('Exception: msd_dem_sop.post_download_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2784 --log_message (substr(SQLERRM,1,150));
2785 RETURN;
2786
2787 END POST_DOWNLOAD_HOOK;
2788
2789
2790
2791
2792 /*
2793 * This procedure loads item cost information from planning server ODS
2794 * for all DM enabled organizations into the import integration
2795 * staging table - BIIO_ITEM_COST
2796 */
2797 PROCEDURE LOAD_ITEM_COST
2798 IS
2799
2800 /*** LOCAL VARIABLES - BEGIN ***/
2801
2802 x_errbuf VARCHAR2(200) := NULL;
2803 x_retcode VARCHAR2(100) := NULL;
2804
2805 x_small_sql VARCHAR2(600) := NULL;
2806
2807 /*** LOCAL VARIABLES - END ***/
2808
2809 BEGIN
2810
2811 /* Alter session to APPS */
2812 x_small_sql := 'alter session set current_schema = APPS';
2813 EXECUTE IMMEDIATE x_small_sql;
2814
2815 g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
2816 IF (g_schema IS NULL)
2817 THEN
2818 log_message ('Error(1): msd_dem_sop.load_item_cost - Unable to find schema name');
2819 END IF;
2820
2821
2822 log_debug ('Entering: msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2823
2824 /* Delete all data (if any) from the staging table */
2825 x_small_sql := 'TRUNCATE TABLE ' || g_schema || '.BIIO_ITEM_COST';
2826 EXECUTE IMMEDIATE x_small_sql;
2827
2828 /* Delete all data (if any) from the ERR staging table */
2829 x_small_sql := 'TRUNCATE TABLE ' || g_schema || '.BIIO_ITEM_COST_ERR';
2830 EXECUTE IMMEDIATE x_small_sql;
2831
2832 push_time_data (
2833 x_errbuf,
2834 x_retcode);
2835 IF (x_retcode = -1)
2836 THEN
2837 log_message ('Error(2): msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || x_errbuf);
2838 RAISE NO_DATA_FOUND;
2839 END IF;
2840
2841 /* Load data for series Item Cost */
2842 load_series_data (
2843 x_errbuf,
2844 x_retcode,
2845 C_MSD_DEM_SOP_ITEM_COST,
2846 -1,
2847 1);
2848 IF (x_retcode = -1)
2849 THEN
2850 log_message ('Error(3): msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || x_errbuf);
2851 RAISE NO_DATA_FOUND;
2852 END IF;
2853
2854 COMMIT;
2855
2856 log_debug ('Exiting: msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2857
2858
2859 /* Alter session to demantra schema */
2860 x_small_sql := 'alter session set current_schema = ' || g_schema;
2861 EXECUTE IMMEDIATE x_small_sql;
2862
2863 EXCEPTION
2864 WHEN OTHERS THEN
2865 log_message ('Exception: msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2866 log_message (substr(SQLERRM,1,150));
2867
2868 COMMIT;
2869
2870 /* Alter session to demantra schema */
2871 x_small_sql := 'alter session set current_schema = ' || g_schema;
2872 EXECUTE IMMEDIATE x_small_sql;
2873
2874 RETURN;
2875
2876 END LOAD_ITEM_COST;
2877
2878
2879
2880
2881 /*
2882 * This procedure is called by the Wait step of the Download Plan Scenario Data workflow.
2883 *
2884 * If WF_PROCESS_LOG lists a workflow instance as running and not in the Wait step,
2885 * then this procedure will sleep for a random number of seconds and then loop. It will
2886 * exit when no workflow instances are running that are not in the Wait step.
2887 *
2888 */
2889 PROCEDURE WAIT_UNTIL_DOWNLOAD_COMPLETE (
2890 p_wait_step_id IN VARCHAR2 DEFAULT '',
2891 p_exception_step_id IN VARCHAR2 DEFAULT '')
2892 IS
2893 v_schema_id number;
2894 v_sql varchar2(4000);
2895 v_status varchar2(100);
2896 v_not_in_steps VARCHAR2(255);
2897 v_demantra_version NUMBER;
2898 g_schema varchar2(30) := null;
2899 BEGIN
2900 -- syenamar
2901 -- this code will be executed with definer(apps) rights due to 'AUTHID DEFINER' clause in package spec, so need to specify demantra schema for demantra tables
2902 g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
2903 IF (g_schema IS NULL)
2904 THEN
2905 log_message ('Error: msd_dem_sop.set_plan_attributes - Unable to find schema name');
2906 END IF;
2907 -- syenamar
2908
2909 -- get id of download plan scenario data wf
2910 /* Bug#8224935 - APP ID */ -- nallkuma
2911 v_schema_id := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
2912 'WF_DOWNLOAD_PLAN_SCENARIO_DATA',
2913 1,
2914 'schema_id'));
2915 --syenamar
2916
2917 v_demantra_version := to_number(msd_dem_common_utilities.get_demantra_version);
2918 IF (v_demantra_version >= 7.3)
2919 THEN
2920 v_not_in_steps := '(''' || nvl(p_wait_step_id, 'Wait') || ''', ''' || nvl(p_exception_step_id, 'Step2') || ''')';
2921 ELSE
2922 v_not_in_steps := '(''Wait'')';
2923 END IF;
2924
2925 v_status := 'Running';
2926 WHILE (v_status = 'Running') LOOP
2927 -- check if an instance is running
2928 -- prepend demantra schema when referring demantra tables
2929 v_sql := 'select nvl((select ''Running'' from ' || g_schema || '.wf_process_log ' ||
2930 'where schema_id = :1 and step_id not in ' || v_not_in_steps ||
2931 ' and status not in(0,-1,-2) and rownum = 1), ''Not Running'') from dual';
2932 execute immediate v_sql into v_status using v_schema_id;
2933
2934 -- if another workflow is running, sleep for 1 to 3 minutes
2935 IF (v_status = 'Running') THEN
2936 dbms_lock.sleep(dbms_random.value(60,180));
2937 END IF;
2938 END LOOP;
2939
2940 EXCEPTION
2941 WHEN OTHERS THEN
2942 null;
2943
2944 END WAIT_UNTIL_DOWNLOAD_COMPLETE;
2945
2946
2947
2948
2949 /*
2950 *
2951 */
2952 PROCEDURE COLLECT_SCI_DATA (
2953 errbuf OUT NOCOPY VARCHAR2,
2954 retcode OUT NOCOPY VARCHAR2,
2955 p_sr_instance_id IN NUMBER,
2956 p_collection_group IN VARCHAR2 DEFAULT '-999',
2957 p_collection_method IN NUMBER,
2958 p_hidden_param1 IN VARCHAR2,
2959 p_date_range_type IN NUMBER,
2960 p_collection_window IN NUMBER,
2961 p_from_date IN VARCHAR2,
2962 p_to_date IN VARCHAR2 )
2963 IS
2964 lv_request_id1 NUMBER := to_number(NULL);
2965 lv_request_id2 NUMBER := to_number(NULL);
2966
2967 x_errbuf VARCHAR2(200) := NULL;
2968 x_errbuf1 VARCHAR2(200) := NULL;
2969 x_retcode VARCHAR2(100) := NULL;
2970 x_retcode1 VARCHAR2(100) := NULL;
2971
2972 x_from_date DATE := NULL;
2973 x_to_date DATE := NULL;
2974
2975 l_sql VARCHAR2(1000) := NULL;
2976 l_profile_id NUMBER := NULL;
2977 l_table_name varchar2(240) := NULL;
2978 l_start_date date := NULL;
2979 l_until_date date := NULL;
2980 l_schema_name VARCHAR2(100) := NULL;
2981 g_schema VARCHAR2(50) := NULL;
2982 l_tbl_id NUMBER := NULL;
2983 l_pdbl_id NUMBER := NULL;
2984
2985 CURSOR c_get_dm_schema --jarora
2986 IS
2987 SELECT owner
2988 FROM dba_objects
2989 WHERE owner = owner
2990 AND object_type = 'TABLE'
2991 AND object_name = 'MDP_MATRIX'
2992 ORDER BY created desc;
2993 BEGIN
2994 BEGIN
2995 msd_dem_common_utilities.log_message ('Entering: msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2996
2997 OPEN c_get_dm_schema; --jarora
2998 FETCH c_get_dm_schema INTO g_schema;
2999 CLOSE c_get_dm_schema;
3000
3001 if (g_schema is not null) then
3002 g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
3003 end if;
3004
3005 if (g_schema is not null)
3006 then
3007
3008 -- Bug#6741262 - kkhatri
3009 l_profile_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
3010 'PROFILE_PURGE_BACKLOG_DATA',
3011 1,
3012 'id'));
3013
3014 l_sql := 'select table_name, from_date, until_date from '|| g_schema || '.transfer_query where id = ' || l_profile_id;
3015 execute immediate l_sql into l_table_name, l_start_date, l_until_date;
3016
3017
3018
3019
3020 /* Get the start date and end dates for collection */
3021
3022 msd_dem_common_utilities.log_debug ('Begin get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3023
3024 IF (p_collection_method = 1) /* Refresh*/
3025 THEN
3026 x_from_date := to_date('01/01/1000', 'DD/MM/YYYY');
3027 x_to_date := to_date('31/12/4000', 'DD/MM/YYYY');
3028 ELSE /* Net Change */
3029 IF (p_date_range_type = 1) /* Absolute*/
3030 THEN
3031
3032 IF (p_from_date IS NULL)
3033 THEN
3034 x_from_date := to_date('01/01/1000', 'DD/MM/YYYY');
3035 ELSE
3036 x_from_date := fnd_date.canonical_to_date (p_from_date);
3037 END IF;
3038
3039 IF (p_to_date IS NULL)
3040 THEN
3041 x_to_date := to_date('31/12/4000', 'DD/MM/YYYY');
3042 ELSE
3043 x_to_date := fnd_date.canonical_to_date (p_to_date);
3044 END IF;
3045
3046 /* Error if p_from_date is greater than p_to_date */
3047 IF (x_from_date > x_to_date)
3048 THEN
3049 retcode := -1;
3050 errbuf := 'From Date should not be greater than To Date.';
3051 msd_dem_common_utilities.log_message ('Error(6): msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3052 msd_dem_common_utilities.log_message (errbuf);
3053 RETURN;
3054 END IF;
3055
3056 ELSE /* Rolling */
3057
3058 IF (p_collection_window < 0)
3059 THEN
3060 retcode := -1;
3061 errbuf := 'History Collection Window must be a positive number.';
3062 msd_dem_common_utilities.log_message ('Error(7): msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3063 msd_dem_common_utilities.log_message (errbuf);
3064 RETURN;
3065 ELSE
3066 x_to_date := trunc(sysdate);
3067 x_from_date := x_to_date - p_collection_window + 1;
3068 END IF;
3069 END IF;
3070 END IF;
3071
3072 msd_dem_common_utilities.log_debug ('End get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3073
3074
3075
3076 IF (x_retcode1 = -1)
3077 THEN
3078 retcode := -1;
3079 errbuf := x_errbuf1;
3080
3081 msd_dem_common_utilities.log_message ('Error while refreshing Purge Series Data. ');
3082 END IF;
3083
3084 msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_FDATE - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3085
3086 l_sql := 'select datet from '|| g_schema ||'.inputs where datet >= '''||x_from_date||''' and rownum = 1 order by datet asc';
3087 execute immediate l_sql into x_from_date;
3088
3089 l_sql := 'select datet from '|| g_schema ||'.inputs where datet <= '''||x_to_date||''' and rownum = 1 order by datet desc';
3090 execute immediate l_sql into x_to_date;
3091
3092 if (x_from_date > x_to_date) then
3093 x_to_date := x_from_date;
3094 end if;
3095
3096 msd_dem_common_utilities.log_message ('For the series, the old data will be purged from ''' || x_from_date ||''' to '''||x_to_date ||'''');
3097
3098 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_FDATE('||l_profile_id||', '''|| x_from_date||''' , '''||x_to_date||'''); end;';
3099 execute immediate l_sql;
3100
3101 msd_dem_common_utilities.log_message ('For the series, options will be NO LOAD and PURGE WITHIN DATE RANGE');
3102
3103 l_tbl_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
3104 'SERIES_TOTAL_BACKLOG',
3105 1,
3106 'forecast_type_id'));
3107
3108 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_tbl_id||', 2, 2); end;';
3109 execute immediate l_sql;
3110
3111
3112 l_pdbl_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
3113 'SERIES_PAST_DUE_BACKLOG',
3114 1,
3115 'forecast_type_id'));
3116
3117 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_pdbl_id||', 2, 2); end;';
3118 execute immediate l_sql;
3119
3120
3121 /* Calling API to notify the application server to refresh its engine */
3122 msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3123
3124 l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id ||'); end;';
3125 msd_dem_common_utilities.log_debug (l_sql);
3126 execute immediate l_sql;
3127
3128 l_sql := 'truncate table '|| g_schema ||'.'||l_table_name ;
3129 msd_dem_common_utilities.log_debug (l_sql);
3130 execute immediate l_sql;
3131
3132 l_sql := 'insert into '|| g_schema ||'.'||l_table_name||'(sdate, level1)'||
3133 'select '''||x_from_date||''', teo.organization from '||g_schema||'.t_ep_organization teo '||
3134 'where teo.organization in
3135 (SELECT mtp.organization_code
3136 FROM msc_instance_orgs mio,
3137 msc_trading_partners mtp
3138 WHERE mio.sr_instance_id = '||p_sr_instance_id||
3139 ' AND nvl(mio.org_group, ''-888'') = decode('''||p_collection_group||''', ''-999'', nvl(mio.org_group, ''-888''), '''||p_collection_group||''')'||
3140 ' AND nvl(mio.dp_enabled_flag, mio.enabled_flag) = 1 '||
3141 ' AND mtp.sr_instance_id = mio.sr_instance_id ' ||
3142 ' AND mtp.sr_tp_id = mio.organization_id '||
3143 ' AND mtp.partner_type = 3) ';
3144
3145 msd_dem_common_utilities.log_debug (l_sql);
3146
3147 execute immediate l_sql;
3148
3149
3150 else
3151 msd_dem_common_utilities.log_message('Demantra Schema not set');
3152 end if;
3153 EXCEPTION
3154 WHEN OTHERS THEN
3155 retcode := 1 ;
3156 errbuf := substr(SQLERRM,1,150);
3157 msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
3158 msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
3159 msd_dem_common_utilities.log_debug (errbuf);
3160 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3161
3162 RETURN;
3163 end;
3164
3165
3166
3167
3168 /* Launching two Conc. Programs (for each Import Integration
3169 Interfact definde for SCI Data) to have the collection
3170 in parallel mode.
3171 */
3172
3173 BEGIN
3174 lv_request_id1 := fnd_request.submit_request('MSD',
3175 'MSDDEMSCI',
3176 NULL,
3177 NULL,
3178 FALSE,
3179 p_sr_instance_id,
3180 p_collection_group,
3181 p_collection_method,
3182 p_date_range_type,
3183 p_collection_window,
3184 p_from_date,
3185 p_to_date,
3186 G_SCI_BACKLOG );
3187
3188 --commit;
3189 EXCEPTION
3190 WHEN OTHERS THEN
3191 msd_dem_common_utilities.log_message ('Error launching concurrent program for SCI BACKLOG Integration Interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3192 msd_dem_common_utilities.log_message (errbuf);
3193 END;
3194
3195 BEGIN
3196 lv_request_id2 := fnd_request.submit_request('MSD',
3197 'MSDDEMSCI',
3198 NULL,
3199 NULL,
3200 FALSE,
3201 p_sr_instance_id,
3202 p_collection_group,
3203 p_collection_method,
3204 p_date_range_type,
3205 p_collection_window,
3206 p_from_date,
3207 p_to_date,
3208 G_SCI_OTHER );
3209
3210 --commit;
3211 EXCEPTION
3212 WHEN OTHERS THEN
3213 msd_dem_common_utilities.log_message ('Error launching concurrent program for SCI OTHER Integration Interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3214 msd_dem_common_utilities.log_message (errbuf);
3215 END;
3216
3217 IF ( lv_request_id1 <> 0 ) AND ( lv_request_id2 <> 0 ) THEN
3218
3219 msd_dem_common_utilities.log_message ('Successfully launched concurrent programs for SCI Integration Interfaces. Please see the following concurrent programs for the individual request logs. '
3220 || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3221 msd_dem_common_utilities.log_message ('Request ID for the SCI BACKLOG Integration Interface concurrent program is - '|| lv_request_id1 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3222 msd_dem_common_utilities.log_message ('Request ID for the SCI OTHER Integration Interface concurrent program is - '|| lv_request_id2 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3223
3224 ELSE
3225
3226 msd_dem_common_utilities.log_message ('Request ID for the SCI BACKLOG Integration Interface concurrent program is - '|| lv_request_id1 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3227 msd_dem_common_utilities.log_message ('Request ID for the SCI OTHER Integration Interface concurrent program is - '|| lv_request_id2 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3228 msd_dem_common_utilities.log_message ('Error launching concurrent programs for SCI Integration Interfaces. Please relaunch the SCI Collections. ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3229 retcode := -1 ;
3230
3231 END IF;
3232
3233 COMMIT;
3234
3235 msd_dem_common_utilities.log_message ('Exiting: msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3236 EXCEPTION
3237 WHEN OTHERS THEN
3238 retcode := -1 ;
3239 errbuf := substr(SQLERRM,1,150);
3240 msd_dem_common_utilities.log_message ('Exception: msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3241 msd_dem_common_utilities.log_message (errbuf);
3242 RETURN;
3243
3244 END COLLECT_SCI_DATA;
3245
3246
3247
3248
3249 PROCEDURE LAUNCH_SCI_DATA_LOADS (
3250 errbuf OUT NOCOPY VARCHAR2,
3251 retcode OUT NOCOPY VARCHAR2,
3252 p_sr_instance_id IN NUMBER,
3253 p_collection_group IN VARCHAR2 DEFAULT '-999',
3254 p_collection_method IN NUMBER,
3255 p_date_range_type IN NUMBER,
3256 p_collection_window IN NUMBER,
3257 p_from_date IN VARCHAR2,
3258 p_to_date IN VARCHAR2,
3259 p_entity IN NUMBER )
3260 IS
3261
3262 l_errbuff1 VARCHAR2(1000) := to_char(NULL);
3263 l_retcode1 NUMBER := 0;
3264
3265 l_errbuff2 VARCHAR2(1000) := to_char(NULL);
3266 l_retcode2 NUMBER := 0;
3267
3268 x_dem_schema VARCHAR2(50) := NULL;
3269 x_dest_table VARCHAR2(100) := NULL;
3270 l_sql_stmnt VARCHAR2(5000) := NULL;
3271
3272 CURSOR c_get_dm_schema
3273 IS
3274 SELECT owner
3275 FROM dba_objects
3276 WHERE owner = owner
3277 AND object_type = 'TABLE'
3278 AND object_name = 'MDP_MATRIX'
3279 ORDER BY created desc;
3280
3281
3282 BEGIN
3283
3284 msd_dem_common_utilities.log_message ('Entering: msd_dem_sop.launch_sci_data_loads - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3285
3286 retcode := 0;
3287
3288 /* SCI BACKLOG */
3289 IF ( p_entity = G_SCI_BACKLOG )
3290 THEN
3291
3292 /* Total Backlog */
3293 BEGIN
3294
3295 MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA
3296 (l_errbuff1,
3297 l_retcode1,
3298 p_sr_instance_id,
3299 p_collection_group,
3300 p_collection_method,
3301 p_date_range_type,
3302 p_collection_window,
3303 p_from_date,
3304 p_to_date,
3305 'BIIO_SCI_BACKLOG:MSD_TOTAL_BACKLOG',
3306 1
3307 );
3308
3309
3310
3311 IF l_retcode1 = -1
3312 THEN
3313 retcode := l_retcode1;
3314 errbuf := l_errbuff1;
3315 msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while inserting Total Backlog Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3316 RETURN;
3317 END IF;
3318
3319 EXCEPTION
3320 WHEN OTHERS THEN
3321 retcode := -1 ;
3322 errbuf := substr(SQLERRM,1,150);
3323 msd_dem_common_utilities.log_message ('Exception while inserting Total Backlog Data in the table BIIIO_SCI_BACKLOG - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3324 msd_dem_common_utilities.log_message (errbuf);
3325 RETURN;
3326 END;
3327
3328 /* Past Due Backlog*/
3329 BEGIN
3330
3331 MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA
3332 (l_errbuff2,
3333 l_retcode2,
3334 p_sr_instance_id,
3335 p_collection_group,
3336 p_collection_method,
3337 p_date_range_type,
3338 p_collection_window,
3339 p_from_date,
3340 p_to_date,
3341 'BIIO_SCI_BACKLOG:MSD_PAST_DUE_BACKLOG',
3342 2
3343 );
3344
3345
3346
3347 IF l_retcode2 = -1
3348 THEN
3349 retcode := l_retcode2;
3350 errbuf := l_errbuff2;
3351 msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while merging Past Due Backlog Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3352 RETURN;
3353 END IF;
3354
3355
3356 EXCEPTION
3357 WHEN OTHERS THEN
3358 retcode := -1 ;
3359 errbuf := substr(SQLERRM,1,150);
3360 msd_dem_common_utilities.log_message ('Exception while merging Past Due Backlog Data in the table BIIIO_SCI_BACKLOG - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3361 msd_dem_common_utilities.log_message (errbuf);
3362 RETURN;
3363 END;
3364
3365 IF ( l_retcode1 = 1 ) OR ( l_retcode2 = 1)
3366 THEN
3367 retcode := 1;
3368 msd_dem_common_utilities.log_message ('Warning Text for Total Backlog Insertion is - ' || l_errbuff1);
3369 msd_dem_common_utilities.log_message ('Warning Text for Past Due Backlog Merge is - ' || l_errbuff2);
3370 errbuf := l_errbuff1 || l_errbuff2 ;
3371 END IF;
3372
3373 /* Call - Level Code Generation Query */
3374 OPEN c_get_dm_schema;
3375 FETCH c_get_dm_schema INTO x_dem_schema;
3376 CLOSE c_get_dm_schema;
3377
3378 /* Demantra is Installed */
3379 IF (x_dem_schema IS NOT NULL)
3380 THEN
3381 x_dest_table := fnd_profile.value('MSD_DEM_SCHEMA')||'.BIIO_SCI_BACKLOG ';
3382 END IF;
3383
3384 -- bug#7419035, syenamar
3385 -- update level3(customer), replace customer name with the unique partner_id from ASCP
3386 IF (msd_dem_common_utilities.is_use_new_site_format <> 0)
3387 THEN
3388 l_sql_stmnt := ' update '||x_dest_table||' bsb '
3389 ||' set level3 = ( select mtp.partner_id '
3390 ||' from msc_trading_partners mtp '
3391 ||' where mtp.partner_name = bsb.level3 '
3392 ||' and mtp.partner_type = 2 )'
3393 ||' WHERE LEVEL3 <> ''' || msd_dem_sr_util.get_null_code || ''' ';
3394
3395 msd_dem_common_utilities.log_message(l_sql_stmnt);
3396
3397 begin
3398 execute immediate l_sql_stmnt;
3399 exception
3400 when others then
3401 null;
3402 end;
3403 END IF;
3404
3405 l_sql_stmnt := ' UPDATE ' || x_dest_table
3406 || ' SET level3 = ''' || msd_dem_sr_util.get_null_code || ''' '
3407 || ' WHERE level3 IS NULL ';
3408
3409 msd_dem_common_utilities.log_message(l_sql_stmnt);
3410
3411 begin
3412 execute immediate l_sql_stmnt;
3413 exception
3414 when others then
3415 null;
3416 end;
3417
3418 /* update dmtra_template.BIIO_SCI_BACKLOG bsb
3419 set level3 = ( select mtp.partner_name
3420 from msc_trading_partners mtp,
3421 msc_tp_id_lid mtil
3422 where mtil.sr_tp_id = bsb.level3_sr_pk
3423 and mtil.sr_instance_id = (select instance_id
3424 from msc_apps_instances mai
3425 where mai.instance_code = substr(bsb.level2,1,instr(bsb.level2,':')-1)
3426 )
3427 and mtil.partner_type = 2
3428 and mtil.tp_id = mtp.partner_id
3429 ); */
3430 /* Ends - Level Code Generation Query */
3431
3432 /* SCI OTHER */
3433
3434 ELSIF ( p_entity = G_SCI_OTHER )
3435 THEN
3436
3437 -- On-Hand Inventory
3438
3439 BEGIN
3440 MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA
3441 (l_errbuff1,
3442 l_retcode1,
3443 p_sr_instance_id,
3444 p_collection_group,
3445 p_collection_method,
3446 p_date_range_type,
3447 p_collection_window,
3448 p_from_date,
3449 p_to_date,
3450 'BIIO_SCI:MSD_ON_HAND_INVENTORY',
3451 1
3452 );
3453
3454
3455
3456
3457 IF l_retcode1 = -1
3458 THEN
3459 retcode := l_retcode1;
3460 errbuf := l_errbuff1;
3461 msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while inserting On Hand Inventory Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3462 RETURN;
3463 END IF;
3464 EXCEPTION
3465 WHEN OTHERS THEN
3466 retcode := -1 ;
3467 errbuf := substr(SQLERRM,1,150);
3468 msd_dem_common_utilities.log_message ('Exception while inserting ON-Hand Inventory Data in the table BIIIO_SCI - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3469 msd_dem_common_utilities.log_message (errbuf);
3470 RETURN;
3471
3472 END;
3473
3474 -- Actual Production
3475 BEGIN
3476
3477 MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA
3478 (l_errbuff2,
3479 l_retcode2,
3480 p_sr_instance_id,
3481 p_collection_group,
3482 p_collection_method,
3483 p_date_range_type,
3484 p_collection_window,
3485 p_from_date,
3486 p_to_date,
3487 'BIIO_SCI:MSD_ACTUAL_PRODUCTION',
3488 2
3489 );
3490
3491
3492
3493 IF l_retcode2 = -1
3494 THEN
3495 retcode := l_retcode2;
3496 errbuf := l_errbuff2;
3497 msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while merging Actual Production Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3498 RETURN;
3499 END IF;
3500
3501
3502 EXCEPTION
3503 WHEN OTHERS THEN
3504 retcode := -1 ;
3505 errbuf := substr(SQLERRM,1,150);
3506 msd_dem_common_utilities.log_message ('Exception while merging Actual Production Data in the table BIIIO_SCI - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3507 msd_dem_common_utilities.log_message (errbuf);
3508 RETURN;
3509 END;
3510
3511 IF ( l_retcode1 = 1 ) OR ( l_retcode2 = 1)
3512 THEN
3513 retcode := 1;
3514 msd_dem_common_utilities.log_message ('Warning Text for On-Hand Inventory Insertion is - ' || l_errbuff1);
3515 msd_dem_common_utilities.log_message ('Warning Text for Actual Production Merge is - ' || l_errbuff2);
3516 errbuf := l_errbuff1 || l_errbuff2 ;
3517 END IF;
3518 END IF; -- IF ( p_entity = G_SCI_BACKLOG )
3519
3520 msd_dem_common_utilities.log_message ('Exiting: msd_dem_sop.launch_sci_data_loads - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3521
3522 EXCEPTION
3523 WHEN OTHERS THEN
3524 retcode := -1 ;
3525 errbuf := substr(SQLERRM,1,150);
3526 msd_dem_common_utilities.log_message ('Exception: msd_dem_sop.launch_sci_data_loads- ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3527 msd_dem_common_utilities.log_message (errbuf);
3528 RETURN;
3529
3530 END LAUNCH_SCI_DATA_LOADS;
3531
3532 PROCEDURE LOAD_COST(
3533 errbuf OUT NOCOPY VARCHAR2,
3534 retcode OUT NOCOPY VARCHAR2,
3535 p_sr_instance_id IN Number,
3536 launchDownload IN Number) AS
3537
3538 var_schema VARCHAR2(100) := NULL;
3539 var_sim_set_id VARCHAR2(100) := NULL;
3540 x_small_sql VARCHAR2(600) := NULL;
3541 sql_statement VARCHAR2(32766):= NULL;
3542 var_request_id VARCHAR2(100) := NULL;
3543
3544 x_errbuf VARCHAR2(200) := NULL;
3545 x_retcode VARCHAR2(100) := NULL;
3546 x_dblink VARCHAR2(100) := NULL;
3547 x_master_org NUMBER := NULL;
3548 BEGIN
3549
3550 msd_dem_common_utilities.log_debug ('Entering: msd_dem_sop.load_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3551
3552 var_schema := fnd_profile.value('MSD_DEM_SCHEMA');
3553 IF (var_schema IS NULL) THEN
3554 retcode:= -1;
3555 msd_dem_common_utilities.log_message ('Error(1): msd_dem_sop.load_cost - Unable to find schema name');
3556 RETURN;
3557 END IF;
3558
3559 /* Get dblink for source instance */
3560 msd_dem_common_utilities.get_dblink (x_errbuf, x_retcode, p_sr_instance_id, x_dblink);
3561 IF (x_retcode = '-1')
3562 THEN
3563 retcode := -1;
3564 errbuf := x_errbuf;
3565 msd_dem_common_utilities.log_message ('Error(1): msd_dem_sop.load_cost - Error in getting dblink' || sysdate);
3566 msd_dem_common_utilities.log_message (x_errbuf);
3567 RETURN;
3568 END IF;
3569 msd_dem_common_utilities.log_debug ('Source DB Link - ' || x_dblink);
3570
3571 /* Get master organization */
3572 x_master_org := to_number(msd_dem_common_utilities.get_parameter_value(p_sr_instance_id, 'MSD_DEM_MASTER_ORG'));
3573 msd_dem_common_utilities.log_debug ('Source Master Organization from which list price is collected - ' || to_char(x_master_org));
3574
3575 var_sim_set_id := fnd_profile.value('MSD_DEM_SIMULATION_SET_NAME');
3576 IF (var_sim_set_id IS NULL) THEN
3577 var_sim_set_id := nvl(var_sim_set_id,-999);
3578 retcode:= 1;
3579 msd_dem_common_utilities.log_message ('Warning(1): msd_dem_sop.load_cost - Unable to find simulation set name name. Repair Cost will not be loaded.');
3580 END IF;
3581
3582 /*Delete all data (if any) from the staging table */
3583 x_small_sql := 'TRUNCATE TABLE ' || var_schema || '.BIIO_SOP_ITEM_COST';
3584 EXECUTE IMMEDIATE x_small_sql;
3585
3586 /*Delete all data (if any) from the ERR staging table */
3587 x_small_sql := 'TRUNCATE TABLE ' || var_schema || '.BIIO_SOP_ITEM_COST_ERR';
3588 EXECUTE IMMEDIATE x_small_sql;
3589
3590 sql_statement:= ' INSERT INTO ' || var_schema || '. BIIO_SOP_ITEM_COST (SDATE, LEVEL1, COST_PRODUCE,
3591 COST_PURCHASE, COST_REPAIR, COST_RESOURCE)
3592 Select (Select Bucket_date from msd_dem_day_dates where day_date=trunc(sysdate)),tei.item item_name,null,null,null,
3593 avg(mds.resource_cost) ResourceCost
3594 from msc_Department_Resources mds,' || var_schema || '.T_EP_Organization teo,msc_trading_partners mtp,
3595 ' || var_schema || '.T_EP_Item tei
3596 where mds.plan_id=-1
3597 and mds.sr_instance_id= mtp.sr_instance_id
3598 and mds.sr_instance_id='''||p_sr_instance_id||'''
3599 and mds.resource_code=tei.item
3600 and mtp.partner_type=3
3601 and mds.organization_id=mtp.sr_tp_id
3602 and mtp.organization_code = teo.organization
3603 and mds.resource_cost is not null
3604 group by tei.item
3605 union all
3606 select
3607 (Select Bucket_date from msd_dem_day_dates where day_date=trunc(sysdate)),tei.item item_name,
3608 avg(msi.standard_cost) StandardCost,
3609 avg(mis.item_price) ItemPrice,
3610 avg(mia.repair_cost) RepairCost,null
3611 from
3612 ' || var_schema || '.t_ep_organization teo,
3613 msc_trading_partners mtp,
3614 ' || var_schema || '.t_ep_item tei,
3615 msc_system_items msi,
3616 msc_item_suppliers mis,
3617 msc_item_attributes mia
3618 where
3619 mtp.organization_code = teo.organization
3620 and mtp.partner_type = 3
3621 and msi.plan_id = -1
3622 and msi.sr_instance_id = mtp.sr_instance_id
3623 and msi.organization_id = mtp.sr_tp_id
3624 and msi.Sr_instance_id='''||p_sr_instance_id||'''
3625 and msi.item_name = tei.item
3626 and mis.plan_id (+) = msi.plan_id
3627 and mis.sr_instance_id (+) = msi.sr_instance_id
3628 and mis.organization_id(+) = msi.organization_id
3629 and mis.inventory_item_id(+) = msi.inventory_item_id
3630 and mia.simulation_set_id(+) = '''||var_sim_set_id||'''
3631 and mia.plan_id(+) = msi.plan_id
3632 and mia.sr_instance_id(+) = msi.sr_instance_id
3633 and mia.organization_id(+) = msi.organization_id
3634 and mia.inventory_item_id(+) = msi.inventory_item_id
3635 and (msi.standard_cost IS NOT NULL OR mis.item_price IS NOT NULL OR mia.repair_cost IS NOT NULL )
3636 group by tei.item';
3637
3638 msd_dem_common_utilities.log_debug ('The query to be executed is -' || sql_statement);
3639 msd_dem_common_utilities.log_debug ('Executing the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3640
3641 EXECUTE IMMEDIATE sql_statement;
3642 msd_dem_common_utilities. log_debug ('Executed the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3643 msd_dem_common_utilities.log_debug('The number of records inserted is - ' ||SQL%ROWCOUNT);
3644 COMMIT;
3645
3646 msd_dem_common_utilities.log_debug('The insert statement was successful');
3647
3648 /* Update Purchase Cost from Source */
3649 sql_statement := 'UPDATE ' || var_schema || '.BIIO_SOP_ITEM_COST target '
3650 || ' SET COST_PURCHASE = nvl((SELECT source.list_price_per_unit FROM mtl_system_items_kfv' || x_dblink || ' source '
3651 || ' WHERE source.concatenated_segments = target.level1 '
3652 || ' AND source.organization_id = ' || to_char(x_master_org) || '), target.cost_purchase) '
3653 || ' WHERE cost_purchase IS NULL ';
3654
3655 msd_dem_common_utilities.log_debug ('The query to be executed is -' || sql_statement);
3656 msd_dem_common_utilities.log_debug ('Executing the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3657
3658 EXECUTE IMMEDIATE sql_statement;
3659 msd_dem_common_utilities.log_debug ('Executed the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3660 msd_dem_common_utilities.log_debug('The number of records updated is - ' ||SQL%ROWCOUNT);
3661 COMMIT;
3662
3663 msd_dem_common_utilities.log_debug('The update statement was successful');
3664
3665 IF (launchDownload = 1) THEN
3666 msd_dem_common_utilities.log_debug ('LaunchDownload is set to YES. Launching the workflow');
3667
3668 BEGIN
3669 var_request_id := null;
3670 var_request_id := fnd_request.submit_request ('MSD', 'MSDDEMLDW', NULL, NULL, FALSE,
3671 'WF_SPF_IMPORT_COSTS');
3672 msd_dem_common_utilities.log_message ('Request Id for "Launch Demantra Workflow" concurrent program - ' || var_request_id);
3673
3674 EXCEPTION
3675 WHEN OTHERS THEN
3676 retcode := -1;
3677 errbuf := substr(SQLERRM,1,150);
3678 msd_dem_common_utilities.log_message ('Error(2): msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3679 msd_dem_common_utilities.log_message ('Error while submitting request for "Launch Demantra Workflow" concurrent program.');
3680 msd_dem_common_utilities.log_message (errbuf);
3681 msd_dem_common_utilities.log_message (' ');
3682 RETURN;
3683 END;
3684
3685 ELSE
3686 msd_dem_common_utilities.log_debug ('LaunchDownload set to NO. Not launching the workflow');
3687 END IF;
3688
3689 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3690
3691 EXCEPTION
3692 WHEN OTHERS THEN
3693 retcode:= -1;
3694 msd_dem_common_utilities.log_message ('Exception: msd_dem_sop.load_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3695 msd_dem_common_utilities.log_message (substr(SQLERRM,1,150));
3696 Return;
3697 END LOAD_COST;
3698
3699
3700 /*
3701 * This procedure is used to kill all existing plan data load jobs.
3702 */
3703 PROCEDURE KILL_JOBS (
3704 p_job_name IN VARCHAR2 DEFAULT NULL )
3705 IS
3706 v_job_name VARCHAR2(100) := p_job_name;
3707 v_schema_name VARCHAR2(100) := C_MSD_DEM_SCHEMA;
3708 BEGIN
3709
3710 /* Log Start */
3711 EXECUTE IMMEDIATE 'BEGIN ' || v_schema_name || '.DBEX(''SOP Kill Jobs - Started'', ''MSD_DEM_SOP.LOAD_PLAN_DATA''); END;';
3712
3713 /* If job name not specified, then default to MSD_DEM_SOP.LOAD_SERIES_DATA_BATCH */
3714 IF (p_job_name IS NULL)
3715 THEN
3716 v_job_name := 'MSD_DEM_SOP.LOAD_SERIES_DATA_BATCH';
3717 END IF;
3718
3719 FOR rec IN (SELECT job from user_jobs WHERE upper(what) like v_job_name || '%' and upper(broken) = 'N')
3720 LOOP
3721 dbms_job.remove (rec.job);
3722 END LOOP;
3723
3724 /* Log End */
3725 EXECUTE IMMEDIATE 'BEGIN ' || v_schema_name || '.DBEX(''SOP Kill Jobs - Completed'', ''MSD_DEM_SOP.LOAD_PLAN_DATA''); END;';
3726
3727 EXCEPTION
3728 WHEN OTHERS THEN
3729 /* Log Exception */
3730 EXECUTE IMMEDIATE 'BEGIN ' || v_schema_name || '.DBEX(''SOP Kill Jobs - Exception - '|| substr(SQLERRM,1,150) || ''', ''MSD_DEM_SOP.LOAD_PLAN_DATA''); END;';
3731
3732 END KILL_JOBS;
3733
3734
3735 END MSD_DEM_SOP;