[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_SOP
Source
1 PACKAGE BODY MSD_DEM_SOP AS
2 /* $Header: msddemsopb.pls 120.17.12010000.3 2008/08/28 12:02:14 syenamar 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
71
72
73 /*** PRIVATE PROCEDURES ***
74 * LOG_DEBUG
75 * LOG_MESSAGE
76 * TRUNCATE_STAGING_TABLES
77 * LOAD_SERIES_DATA
78 * PRE_DOWNLOAD_HOOK
79 * LOAD_ASCP_DATA
80 * PUSH_TIME_DATA
81 * LOAD_SUP_PLAN_GL
82 * LOAD_PLAN_GL
83 * LOAD_RESOURCE_GL
84 * LOAD_ITEM_LOCS
85 */
86
87
88 /*
89 * This procedure logs a given debug message text in ???
90 * only if the profile MSD_DEM_DEBUG is set to 'Yes'.
91 * param: p_buff - debug message text to be logged.
92 */
93 PROCEDURE LOG_DEBUG ( p_buff IN VARCHAR2)
94 IS
95 BEGIN
96 IF (C_MSD_DEM_DEBUG = 'Y') THEN
97 NULL;
98
99 /***** REMOVE AFTER USE *****/
100 /***** INS IN SJ_T1 VAL (p_buff); *****/
101 /***** REMOVE AFTER USE *****/
102
103 END IF;
104
105 END LOG_DEBUG;
106
107
108
109 /*
110 * This procedure logs a given message text in ???
111 * param: p_buff - message text to be logged.
112 */
113 PROCEDURE LOG_MESSAGE ( p_buff IN VARCHAR2)
114 IS
115 BEGIN
116 NULL;
117
118 /***** REMOVE AFTER USE *****/
119 /***** INS IN SJ_T1 VAL (p_buff); *****/
120 /***** REMOVE AFTER USE *****/
121
122 END LOG_MESSAGE;
123
124
125
126 /*
127 * This procedure truncates all the staging tables for ascp plan related series
128 * NOTE: Must be called ONLY after global variable g_schema has been set.
129 */
130 PROCEDURE TRUNCATE_STAGING_TABLES (
131 errbuf OUT NOCOPY VARCHAR2,
132 retcode OUT NOCOPY VARCHAR2)
133 IS
134 x_table_name VARCHAR2(60) := NULL;
135 BEGIN
136
137 log_debug ('Entering: msd_dem_sop.truncate_staging_tables - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
138
139
140 x_table_name := g_schema || '.BIIO_RESOURCE_CAPACITY';
141 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
142 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
143
144 x_table_name := g_schema || '.BIIO_RESOURCE_CAPACITY_ERR';
145 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
146 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
147
148 x_table_name := g_schema || '.BIIO_OTHER_PLAN_DATA';
149 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
150 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
151
152 x_table_name := g_schema || '.BIIO_OTHER_PLAN_DATA_ERR';
153 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
154 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
155
156 x_table_name := g_schema || '.BIIO_PURGE_PLAN';
157 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
158 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
159
160 x_table_name := g_schema || '.BIIO_PURGE_PLAN_ERR';
161 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
162 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
163
164 x_table_name := g_schema || '.BIIO_PURGE_PLAN_RESOURCE';
165 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
166 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
167
168 x_table_name := g_schema || '.BIIO_PURGE_PLAN_RESOURCE_ERR';
169 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
170 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
171
172 x_table_name := g_schema || '.BIIO_RESOURCES';
173 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
174 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
175
176 x_table_name := g_schema || '.BIIO_RESOURCES_ERR';
177 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
178 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
179
180 x_table_name := g_schema || '.BIIO_SCENARIO_RESOURCES';
181 log_message ('Deleting data from table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
182 EXECUTE IMMEDIATE 'DELETE FROM ' || x_table_name;
183
184 x_table_name := g_schema || '.BIIO_SCENARIO_RESOURCES_ERR';
185 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
186 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
187
188 x_table_name := g_schema || '.BIIO_SCENARIO_RESOURCE_POP';
189 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
190 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
191
192 x_table_name := g_schema || '.BIIO_SCENARIO_RESOURCE_POP_ERR';
193 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
194 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
195
196 x_table_name := g_schema || '.BIIO_SUPPLY_PLANS';
197 log_message ('Deleting data from table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
198 EXECUTE IMMEDIATE 'DELETE FROM ' || x_table_name;
199
200 x_table_name := g_schema || '.BIIO_SUPPLY_PLANS_ERR';
201 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
202 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
203
204 x_table_name := g_schema || '.BIIO_SUPPLY_PLANS_POP';
205 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
206 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
207
208 x_table_name := g_schema || '.BIIO_SUPPLY_PLANS_POP_ERR';
209 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
210 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
211
212 x_table_name := g_schema || '.T_SRC_ITEM_TMPL';
213 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
214 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
215
216 x_table_name := g_schema || '.T_SRC_ITEM_TMPL_ERR';
217 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
218 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
219
220 x_table_name := g_schema || '.T_SRC_LOC_TMPL';
221 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
222 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
223
224 x_table_name := g_schema || '.T_SRC_LOC_TMPL_ERR';
225 log_message ('Truncating table ' || x_table_name || ' ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
226 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_table_name;
227
228 log_debug ('Exiting: msd_dem_sop.truncate_staging_tables - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 retcode := -1;
233 errbuf := substr(SQLERRM,1,150);
234 log_message ('Exception(1): msd_dem_sop.truncate_staging_tables - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
235 RETURN;
236 END TRUNCATE_STAGING_TABLES;
237
238
239
240
241 /*
242 *
243 */
244 PROCEDURE LOAD_SERIES_DATA (
245 errbuf OUT NOCOPY VARCHAR2,
246 retcode OUT NOCOPY VARCHAR2,
247 p_series_id IN NUMBER,
248 p_plan_id IN NUMBER,
249 p_dm_time_level IN NUMBER)
250 IS
251
252 CURSOR c_get_series_info
253 IS
254 SELECT
255 series_name,
256 series_type,
257 identifier,
258 custom_view_name,
259 ps_view_name,
260 stg_series_col_name
261 FROM
262 msd_dem_series
263 WHERE
264 series_id = p_series_id;
265
266 /*** LOCAL VARIABLES - BEGIN ***/
267
268 x_errbuf VARCHAR2(200) := NULL;
269 x_retcode VARCHAR2(100) := NULL;
270
271 x_series_name VARCHAR2(250) := NULL;
272 x_series_type NUMBER := NULL;
273 x_identifier VARCHAR2(30) := NULL;
274 x_custom_view_name VARCHAR2(30) := NULL;
275 x_view_name VARCHAR2(30) := NULL;
276 x_is_custom NUMBER := NULL;
277 x_stg_series_col_name VARCHAR2(30) := NULL;
278 x_key_values VARCHAR2(4000) := NULL;
279 x_large_sql VARCHAR2(32000) := NULL;
280
281
282 /*** LOCAL VARIABLES - END ***/
283
284 BEGIN
285 log_debug ('Entering: msd_dem_sop.load_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
286
287 /* Validate INPUT Parameters */
288 IF (p_series_id IS NULL OR p_plan_id IS NULL)
289 THEN
290 log_message ('Error(1): msd_dem_sop.load_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') ||
291 ' - One or Both the parameters passed to the procedure is/are NULL.');
292 RETURN;
293 END IF;
294
295
296 /* Get Series Info */
297 OPEN c_get_series_info;
298 FETCH c_get_series_info INTO x_series_name,
299 x_series_type,
300 x_identifier,
301 x_custom_view_name,
302 x_view_name,
303 x_stg_series_col_name;
304 IF ( x_series_name IS NULL
305 OR x_series_type IS NULL
306 OR x_identifier IS NULL)
307 THEN
308 log_message ('Error(2): msd_dem_sop.load_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') ||
309 ' - Unable to get series info for id - ' || to_char(p_series_id));
310 RETURN;
311 END IF;
312
313 /* Check if custom view is specified for the series */
314 IF (x_custom_view_name IS NULL)
315 THEN
316 x_is_custom := 0;
317 ELSE
318 x_is_custom := 1;
319 END IF;
320
321
322 IF (p_dm_time_level = 1)
323 THEN
324 x_key_values := '$C_PLAN_ID#' || to_char(p_plan_id)
325 || '$C_DEST_DATE#' || 'mdbr.sdate'
326 || '$C_SERIES_QTY#' || x_stg_series_col_name
327 || '$C_DEM_SCHEMA#' || g_schema
328 || '$C_TIME_CLAUSE# $';
329 ELSE
330 x_key_values := '$C_PLAN_ID#' || to_char(p_plan_id)
331 || '$C_DEST_DATE#' || 'inp.datet'
332 || '$C_SERIES_QTY#' || x_stg_series_col_name
333 || '$C_DEM_SCHEMA#' || g_schema
334 || '$C_TIME_CLAUSE#' || ', msd_dem_dates inp WHERE mdbr.sdate BETWEEN inp.start_date AND inp.end_date$';
335 END IF;
336
337 /* Get the query */
338 msd_dem_query_utilities.get_query3 (
339 x_retcode,
340 x_large_sql,
341 x_identifier,
342 null,
343 x_key_values,
344 x_is_custom,
345 x_custom_view_name,
346 x_series_type,
347 x_view_name);
348
349 IF (x_retcode = -1)
350 THEN
351 log_message ('Error(3): msd_dem_sop.load_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') ||
352 ' - Unable to get query for identifier - ' || x_identifier);
353 RETURN;
354 END IF;
355
356 log_debug ('Query - ');
357 log_debug (x_large_sql);
358
359 log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
360
361 BEGIN
362 EXECUTE IMMEDIATE x_large_sql;
363 EXCEPTION
364 WHEN OTHERS THEN
365 retcode := -1;
366 errbuf := substr(SQLERRM,1,150);
367 log_message ('Exception(1): msd_dem_sop.load_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
368 RETURN;
369 END;
370
371
372 log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
373
374 log_debug ('Exiting: msd_dem_sop.load_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
375 EXCEPTION
376 WHEN OTHERS THEN
377 retcode := -1;
378 errbuf := substr(SQLERRM,1,150);
379 log_message ('Exception(2): msd_dem_sop.load_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
380 RETURN;
381 END LOAD_SERIES_DATA;
382
383
384
385 /*
386 *
387 */
388 PROCEDURE PRE_DOWNLOAD_HOOK (
389 errbuf OUT NOCOPY VARCHAR2,
390 retcode OUT NOCOPY VARCHAR2,
391 p_plan_id IN NUMBER)
392 IS
393
394 CURSOR c_get_series_for_purge
395 IS
396 SELECT
397 series_id
398 FROM msd_dem_series
399 WHERE series_id IN (112, 113);
400
401 Cursor c_plan_start_date is
402 select curr_start_date
403 from msc_plans
404 where plan_id = p_plan_id;
405
406
407 /*** LOCAL VARIABLES - BEGIN ***/
408
409 x_errbuf VARCHAR2(200) := NULL;
410 x_retcode VARCHAR2(100) := NULL;
411
412 l_plan_start_date date := NULL;
413 x_to_date date := NULL;
414 x_from_date date := NULL;
415 l_profile_id1 number := NULL;
416 l_profile_id2 number := NULL;
417 l_sql varchar2(1000) := NULL;
418 g_schema varchar2(50) := NULL;
419
420
421 /*** LOCAL VARIABLES - END ***/
422
423 BEGIN
424 log_debug ('Entering: msd_dem_sop.pre_download_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
425
426 /***** 1. DECIDE UPON HOW TO HANDLE CONCURRENCY *****/
427 /***** Handled in WAIT_UNTIL_DOWNLOAD_COMPLETE *****/
428
429
430 /***** 2. Load rows for purging existing plan related data *****/
431
432 FOR rec IN c_get_series_for_purge
433 LOOP
434 log_debug ('Start Of Loop for series id - ' || to_number(rec.series_id) || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
435
436 load_series_data (
437 x_errbuf,
438 x_retcode,
439 rec.series_id,
440 p_plan_id,
441 1);
442 IF (x_retcode = -1)
443 THEN
444 retcode := -1;
445 errbuf := x_errbuf;
446 log_message ('Error(2): msd_dem_sop.pre_download_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
447 RETURN;
448 END IF;
449
450 log_debug ('End Of Loop for series id - ' || to_number(rec.series_id) || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
451 END LOOP;
452
453
454 /***** 3. UPDATE START DATES AND END DATES OF THE PURGE PLAN DATA *****/
455
456 OPEN c_plan_start_date;
457 FETCH c_plan_start_date INTO l_plan_start_date;
458 CLOSE c_plan_start_date;
459
460
461
462 g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
463
464 if (g_schema is not null)
465 then
466
467 l_sql := 'select datet from '|| g_schema ||'.inputs where datet > '''||l_plan_start_date||''' and rownum = 1 order by datet asc';
468 execute immediate l_sql into x_from_date;
469
470 l_sql := 'select max(datet) from '||g_schema||'.inputs ';
471 execute immediate l_sql into x_to_date;
472
473 /* Setting start and end dates for Purge Plan Data data profile */
474
475 l_sql := 'select id from '||g_schema||'.transfer_query where query_name = ''Purge Plan Data''';
476 execute immediate l_sql into l_profile_id1;
477
478 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_FDATE('||l_profile_id1||', '''|| x_from_date||''', '''||x_to_date||'''); end;';
479 execute immediate l_sql;
480
481
482 /* Calling API to notify the application server to refresh its engine */
483 msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
484
485 l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id1 ||'); end;';
486 execute immediate l_sql;
487
488 /* Setting start and end dates for Purge Resource Data data profile*/
489
490 l_sql := 'select id from '||g_schema||'.transfer_query where query_name = ''Purge Resource Data''';
491 execute immediate l_sql into l_profile_id2;
492
493 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_FDATE('||l_profile_id2||', '''|| x_from_date||''', '''||x_to_date||'''); end;';
494 execute immediate l_sql;
495
496
497 /* Calling API to notify the application server to refresh its engine */
498 msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
499
500 l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id2 ||'); end;';
501 execute immediate l_sql;
502 else
503 msd_dem_common_utilities.log_message('Demantra Schema not set');
504 end if;
505
506
507
508 log_debug ('Exiting: msd_dem_sop.pre_download_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
509 EXCEPTION
510 WHEN OTHERS THEN
511 retcode := -1;
512 errbuf := substr(SQLERRM,1,150);
513 log_message ('Exception: msd_dem_sop.pre_download_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
514 RETURN;
515 END PRE_DOWNLOAD_HOOK;
516
517
518
519 /*
520 *
521 */
522 PROCEDURE LOAD_ASCP_DATA (
523 errbuf OUT NOCOPY VARCHAR2,
524 retcode OUT NOCOPY VARCHAR2,
525 p_plan_id IN NUMBER)
526 IS
527
528 CURSOR c_get_all_series
529 IS
530 SELECT
531 series_id
532 FROM
533 msd_dem_series
534 WHERE
535 series_id IN (101, 102, 103, 104, 105, 106, 107, 108, 109, 110);
536
537 /*** LOCAL VARIABLES - BEGIN ***/
538
539 x_errbuf VARCHAR2(200) := NULL;
540 x_retcode VARCHAR2(100) := NULL;
541
542 x_dm_time_level NUMBER := NULL;
543 x_dm_time_bucket VARCHAR2(30) := NULL;
544
545 x_sql VARCHAR2(2000) := NULL;
546
547 /*** LOCAL VARIABLES - END ***/
548
549 BEGIN
550 log_debug ('Entering: msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
551
552 /* Get the lowest time bucket */
553 x_dm_time_bucket := msd_dem_common_utilities.dm_time_level;
554 IF (x_dm_time_bucket IS NULL)
555 THEN
556 retcode := -1;
557 errbuf := 'Unable to get lowest time bucket';
558 log_message('Error(1): msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
559 RETURN;
560 ELSIF (upper(x_dm_time_bucket) = 'DAY')
561 THEN
562 x_dm_time_level := 1;
563 ELSE
564 x_dm_time_level := 2;
565 END IF;
566
567
568 FOR rec IN c_get_all_series
569 LOOP
570 log_debug ('Start Of Loop for series id - ' || to_number(rec.series_id) || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
571
572 load_series_data (
573 x_errbuf,
574 x_retcode,
575 rec.series_id,
576 p_plan_id,
577 x_dm_time_level);
578 IF (x_retcode = -1)
579 THEN
580 retcode := -1;
581 errbuf := x_errbuf;
582 log_message ('Error(2): msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
583 RETURN;
584 END IF;
585
586 COMMIT;
587
588 log_debug ('End Of Loop for series id - ' || to_number(rec.series_id) || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
589 END LOOP;
590
591 /* Delete non-saleable items from the staging table BIIO_OTHER_PLAN_DATA */
592 x_sql := ' DELETE FROM ' || g_schema || '.BIIO_OTHER_PLAN_DATA bopd '
593 || ' WHERE NOT EXISTS ( SELECT 1 '
594 || ' FROM ' || g_schema || '.t_ep_item tei, '
595 || g_schema || '.mdp_matrix mm '
596 || ' WHERE tei.item = bopd.level2 '
597 || ' AND mm.t_ep_item_ep_id = tei.t_ep_item_ep_id '
598 || ' AND mm.is_fictive = 0 '
599 || ' AND rownum < 2 ) '
600 || ' AND avail_sup_std_cap IS NULL '
601 || ' AND required_sup_cap IS NULL ';
602
603 log_debug ('Query - ');
604 log_debug (x_sql);
605
606 log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
607
608 BEGIN
609 EXECUTE IMMEDIATE x_sql;
610 EXCEPTION
611 WHEN OTHERS THEN
612 retcode := -1;
613 errbuf := substr(SQLERRM,1,150);
614 log_message ('Exception(1): msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
615 RETURN;
616 END;
617
618 COMMIT;
619
620 log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
621
622 /* Updated non-supplier series to NULL for non-saleable:buy:critical items from the staging table BIIO_OTHER_PLAN_DATA */
623 x_sql := ' UPDATE ' || g_schema || '.BIIO_OTHER_PLAN_DATA bopd '
624 || ' SET bopd.constrained_fcst = NULL, '
625 || ' bopd.prod_plan = NULL, '
626 || ' bopd.safety_stk = NULL, '
627 || ' bopd.beginning_on_hand = NULL, '
628 || ' bopd.dependent_demand = NULL, '
629 || ' bopd.planned_shipments = NULL '
630 || ' WHERE NOT EXISTS ( SELECT 1 '
631 || ' FROM ' || g_schema || '.t_ep_item tei, '
632 || g_schema || '.mdp_matrix mm '
633 || ' WHERE tei.item = bopd.level2 '
634 || ' AND mm.t_ep_item_ep_id = tei.t_ep_item_ep_id '
635 || ' AND mm.is_fictive = 0 '
636 || ' AND rownum < 2 ) '
637 || ' AND ( avail_sup_std_cap IS NOT NULL '
638 || ' OR required_sup_cap IS NOT NULL ) ';
639
640 log_debug ('Query - ');
641 log_debug (x_sql);
642
643 log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
644
645 BEGIN
646 EXECUTE IMMEDIATE x_sql;
647 EXCEPTION
648 WHEN OTHERS THEN
649 retcode := -1;
650 errbuf := substr(SQLERRM,1,150);
651 log_message ('Exception(2): msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
652 RETURN;
653 END;
654
655 COMMIT;
656
657 log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
658
659 log_debug ('Exiting: msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
660 EXCEPTION
661 WHEN OTHERS THEN
662 retcode := -1;
663 errbuf := substr(SQLERRM,1,150);
664 log_message ('Exception(3): msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
665 RETURN;
666 END LOAD_ASCP_DATA;
667
668
669
670 /*
671 *
672 */
673 PROCEDURE PUSH_TIME_DATA (
674 errbuf OUT NOCOPY VARCHAR2,
675 retcode OUT NOCOPY VARCHAR2 )
676 IS
677
678 /*** LOCAL VARIABLES - BEGIN ***/
679
680 x_sql VARCHAR2(1000) := NULL;
681
682 x_dm_table VARCHAR2(100) := NULL;
683 x_source_time_table VARCHAR2(100) := NULL;
684 x_start_date VARCHAR2(100) := NULL;
685 x_end_date VARCHAR2(100) := NULL;
686
687 x_time_bucket VARCHAR2(30) := NULL;
688 x_first_day_of_week VARCHAR2(30) := NULL;
689 x_aggregation_method NUMBER(1) := NULL;
690 x_actual_agg_method NUMBER(1) := NULL;
691
692 /*** LOCAL VARIABLES - END ***/
693
694 BEGIN
695
696 log_debug ('Entering: msd_dem_sop.push_time_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
697
698 IF (C_MSD_DEM_PUSH_TIME = 'N')
699 THEN
700 log_debug ('Table msd_dem_dates has already been populated for this session.');
701 RETURN;
702 END IF;
703
704 log_debug ('Deleting time data from msd_dem_dates');
705 EXECUTE IMMEDIATE 'DELETE FROM msd_dem_dates';
706
707 x_dm_table := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'DM_WIZ_DM_DEF');
708
709 /* Get the time level info for the active data model */
710 x_sql := 'SELECT time_bucket, first_day_of_week, aggregation_method ' ||
711 ' FROM ' || x_dm_table ||
712 ' WHERE dm_or_template = 2 ' ||
713 ' AND is_active = 1 ';
714
715 EXECUTE IMMEDIATE x_sql INTO x_time_bucket, x_first_day_of_week, x_aggregation_method;
716
717
718 IF (upper(x_time_bucket) = 'DAY')
719 THEN
720 log_debug ('Lowest Time Bucket - Day : Time data not inserted into source msd_dem_dates');
721 RETURN;
722 ELSIF (upper(x_time_bucket) = 'WEEK')
723 THEN
724 x_actual_agg_method := x_aggregation_method;
725 ELSIF (upper(x_time_bucket) = 'MONTH')
726 THEN
727 /* Aggregate backwards */
728 x_actual_agg_method := 2;
729 ELSE
730 retcode := -1;
731 errbuf := 'Invalid time bucket';
732 log_message ('Error(1): msd_dem_push_setup_parameters.push_time_data - ' || 'Invalid time bucket - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
733 RETURN;
734 END IF;
735
736 --bug#7361558 syenamar
737 --adjust x_end_date so that time is set to 23hrs 59mins 59secs on that date
738 IF (x_actual_agg_method = 1) /* Forward */
739 THEN
740 x_start_date := ' datet - num_of_days + 1, ';
741 x_end_date := ' datet ';
742 ELSE
743 x_start_date := ' datet, ';
744 x_end_date := ' datet + num_of_days - 1 ';
745 END IF;
746
747 x_end_date := 'trunc(' || x_end_date || ') + 86399/86400, ';
748
749 x_source_time_table := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'INPUTS');
750
751 log_debug ('Inserting time data into msd_dem_dates');
752 x_sql := 'INSERT INTO msd_dem_dates' ||
753 ' (datet, num_of_days, start_date, end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
754 ' SELECT datet, num_of_days, ' || x_start_date || x_end_date ||
755 ' sysdate, :1, sysdate, :2, :3 ' ||
756 ' FROM ' || x_source_time_table;
757 EXECUTE IMMEDIATE x_sql USING fnd_global.user_id, fnd_global.user_id, fnd_global.login_id;
758
759 COMMIT;
760
761 C_MSD_DEM_PUSH_TIME := 'N';
762
763 log_debug ('Exiting: msd_dem_sop.push_time_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
764
765 EXCEPTION
766 WHEN OTHERS THEN
767 retcode := -1;
768 errbuf := substr(SQLERRM,1,150);
769 log_message ('Exception: msd_dem_sop.push_time_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
770 RETURN;
771 END PUSH_TIME_DATA;
772
773
774
775
776
777 Procedure load_sup_plan_gl(p_plan_id in number,
778 p_compile_designator in varchar2,
779 p_plan_start_date in date,
780 p_end_date in date,
781 p_sop_enabled in number,
782 p_plan_type in number,
783 p_populate in number) is
784
785 x_plan_type varchar2(10);
786 x_plan_code varchar2(100);
787 x_plan_desc varchar2(240);
788 l_dem_sched varchar2(200);
789 x_dem_sched varchar2(2000):= NULL;
790
791 Type ref_cur is Ref Cursor;
792 c_dem_sched ref_cur;
793
794 c_scenario_status_id ref_cur;
795 c_scenario_status_code ref_cur;
796
797
798 l_scenario_status_id number;
799 l_scenario_status_code number;
800
801
802 l_stmt varchar2(240):= NULL;
803 l_sql varchar2(2000):= NULL;
804 g_schema varchar2(30):= NULL;
805
806 Begin
807
808 if(p_plan_type <> 6) THEN
809 x_plan_type := 'ASCP';
810 x_plan_code := p_compile_designator;
811 x_plan_desc := p_compile_designator;
812 elsif(p_plan_type = 6) THEN
813 x_plan_type := 'SNO';
814 x_plan_code := p_compile_designator;
815 x_plan_desc := p_compile_designator;
816 end if;
817
818 l_stmt := 'select input_name from msc_plan_sched_v where plan_id= ' ||p_plan_id ;
819
820 open c_dem_sched for l_stmt;
821 loop
822 fetch c_dem_sched into l_dem_sched;
823 exit when c_dem_sched%NOTFOUND;
824 x_dem_sched := x_dem_sched ||', ' || l_dem_sched;
825 end loop;
826 close c_dem_sched;
827
828 x_dem_sched := substr(x_dem_sched, 2, 200);
829
830 g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
831 IF (g_schema IS NULL)
832 THEN
833 log_message ('Error(1): msd_dem_sop.load_plan_data - Unable to find schema name');
834 END IF;
835
836 l_sql := 'select scenario_status_id from '|| g_schema||'.supply_plan where plan_id = '||p_plan_id;
837
838 open c_scenario_status_id for l_sql;
839 fetch c_scenario_status_id into l_scenario_status_id;
840 close c_scenario_status_id;
841
842 if (l_scenario_status_id is null) then
843 l_scenario_status_code := 4;
844 else
845 l_sql := 'select scenario_status_code from '|| g_schema||'.scenario_status where scenario_status_id = '||l_scenario_status_id;
846
847 open c_scenario_status_code for l_sql;
848 fetch c_scenario_status_code into l_scenario_status_code;
849 close c_scenario_status_code;
850
851 end if;
852
853
854 l_sql := 'insert into ' || g_schema||'.biio_supply_plans(supply_plan_code,
855 supply_plan_desc,
856 scenario_status_code,
857 plan_id,
858 method_status,
859 demand_schedules,
860 plan_type,
861 start_date,
862 end_date,
863 last_imported)
864 VALUES ('''
865 ||x_plan_code ||''','
866 ||''''||x_plan_desc ||''','
867 ||''||l_scenario_status_code ||','
868 ||''||p_plan_id ||','
869 ||''''|| NULL ||''','
870 ||''''||x_dem_sched ||''','
871 ||''''||x_plan_type ||''','
872 ||''''||p_plan_start_date ||''','
873 ||''''||p_end_date ||''','
874 ||''''||sysdate ||''')' ;
875
876 execute immediate l_sql;
877
878 If( p_populate = 0 ) then
879 l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
880 from_date,
881 until_date,
882 filter_level,
883 level_order,
884 filter_member)
885 values ('''
886 ||x_plan_code ||''','
887 ||''''||p_plan_start_date ||''','
888 ||''''||sysdate ||''','
889 ||''''|| 'ITEM'||''','
890 ||''||'2' ||','
891 ||''''||'0' ||''')' ;
892 execute immediate l_sql;
893
894 l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
895 from_date,
896 until_date,
897 filter_level,
898 level_order,
899 filter_member)
900 values ('''
901 ||x_plan_code ||''','
902 ||''''||p_plan_start_date ||''','
903 ||''''||sysdate ||''','
904 ||''''|| 'DEMAND CLASS'||''','
905 ||''||'2' ||','
906 ||''''||'0' ||''')' ;
907 execute immediate l_sql;
908
909 l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
910 from_date,
911 until_date,
912 filter_level,
913 level_order,
914 filter_member)
915 values ('''
916 ||x_plan_code ||''','
917 ||''''||p_plan_start_date ||''','
918 ||''''||sysdate ||''','
919 ||''''|| 'ORGANIZATION'||''','
920 ||''||'1' ||','
921 ||''''||'0' ||''')' ;
922 execute immediate l_sql;
923
924 l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
925 from_date,
926 until_date,
927 filter_level,
928 level_order,
929 filter_member)
930 values ('''
931 ||x_plan_code ||''','
932 ||''''||p_plan_start_date ||''','
933 ||''''||sysdate ||''','
934 ||''''|| 'SITE'||''','
935 ||''||'1' ||','
936 ||''''||'0' ||''')' ;
937 execute immediate l_sql;
938
939 l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
940 from_date,
941 until_date,
942 filter_level,
943 level_order,
944 filter_member)
945 values ('''
946 ||x_plan_code ||''','
947 ||''''||p_plan_start_date ||''','
948 ||''''||sysdate ||''','
949 ||''''|| 'SALES CHANNEL'||''','
950 ||''||'1' ||','
951 ||''''||'0' ||''')' ;
952 execute immediate l_sql;
953
954 end if;
955 commit;
956
957 end;
958
959
960
961
962 procedure load_plan_gl(p_plan_id number,
963 p_populate number)
964
965 is
966
967 cursor get_plan_info is
968 select sr_instance_id, compile_designator, plan_type, curr_start_date, cutoff_date
969 from msc_plans
970 where plan_id = p_plan_id;
971
972 l_plan_type number;
973
974 l_plan_name varchar2(250);
975
976 l_instance_id number;
977
978 l_start_date date;
979 l_end_date date;
980
981 l_stmt varchar2(4000);
982 l_retcode number;
983
984 Cursor c_sup_plan is
985 select compile_designator, curr_start_date, cutoff_date, sop_enabled, plan_type
986 from msc_plans
987 where plan_id=p_plan_id;
988
989 Type all_sup_plans is Ref Cursor;
990 c_all_sup_plans all_sup_plans;
991
992 p_compile_designator VARCHAR2(240);
993 p_plan_start_date DATE;
994 p_end_date DATE;
995 p_sop_enabled VARCHAR2(10);
996 p_plan_type NUMBER;
997
998 Type dem_sched is Ref Cursor;
999 c_dem_sched dem_sched;
1000
1001 l_dem_sched varchar2(2000);
1002 x_dem_sched varchar2(2000):= NULL;
1003
1004
1005 x_plan_id number;
1006
1007 begin
1008
1009
1010 if p_populate <> 1 then
1011
1012 If(p_plan_id is null) then
1013
1014 l_stmt := 'select plan_id, compile_designator, curr_start_date, cutoff_date, sop_enabled, plan_type
1015 from msc_plans
1016 where sop_enabled = 1 ';
1017
1018 /* Do not supply plan members which are already present inside Demantra */
1019 l_stmt := l_stmt || ' AND compile_designator NOT IN '
1020 || ' ( SELECT supply_plan_code FROM '
1021 || g_schema || '.supply_plan )';
1022
1023
1024 open c_all_sup_plans for l_stmt;
1025 loop
1026 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;
1027 exit when c_all_sup_plans%NOTFOUND;
1028 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);
1029 end loop;
1030 close c_all_sup_plans;
1031
1032 Else
1033 open c_sup_plan;
1034 fetch c_sup_plan into p_compile_designator, p_plan_start_date, p_end_date, p_sop_enabled, p_plan_type;
1035 close c_sup_plan;
1036
1037 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);
1038
1039 end if;
1040
1041 return;
1042
1043 end if;
1044
1045 open get_plan_info;
1046 fetch get_plan_info into l_instance_id, l_plan_name, l_plan_type, l_start_date, l_end_date;
1047 close get_plan_info;
1048
1049
1050 if l_plan_name is not null then
1051
1052 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_DELETE_STMT', l_instance_id);
1053
1054 l_stmt := replace(l_stmt, 'C_TABLE_NAME', fnd_profile.value('MSD_DEM_SCHEMA') || '.biio_supply_plans');
1055
1056 execute immediate l_stmt;
1057
1058 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_DELETE_STMT', l_instance_id);
1059
1060 l_stmt := replace(l_stmt, 'C_TABLE_NAME', fnd_profile.value('MSD_DEM_SCHEMA') || '.biio_supply_plans_pop');
1061
1062 execute immediate l_stmt;
1063
1064 l_stmt := 'select input_name from msc_plan_sched_v where plan_id= ' ||p_plan_id ;
1065
1066 open c_dem_sched for l_stmt;
1067 loop
1068 fetch c_dem_sched into l_dem_sched;
1069 exit when c_dem_sched%NOTFOUND;
1070 x_dem_sched := x_dem_sched ||', ' || l_dem_sched;
1071 end loop;
1072 close c_dem_sched;
1073
1074 x_dem_sched := substr(x_dem_sched, 2);
1075
1076 end if;
1077
1078 l_stmt := ' SELECT spd.from_date FROM ' || g_schema || '.supply_plan sp, '
1079 || g_schema || '.supply_plan_dates spd '
1080 || ' WHERE sp.plan_id = ' || p_plan_id
1081 || ' AND spd.supply_plan_id = sp.supply_plan_id ';
1082 EXECUTE IMMEDIATE l_stmt INTO l_start_date;
1083
1084 if l_plan_type <> 6 then
1085
1086 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_PLANS', l_instance_id);
1087
1088 if l_stmt is null then
1089 return;
1090 end if;
1091
1092 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1093
1094 execute immediate l_stmt using l_plan_name, l_plan_name, 'ASCP', 2, l_start_date, l_end_date, p_plan_id, substr(x_dem_sched,1,200);
1095
1096 if p_populate = 1 then
1097
1098 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_PLANS_POP_EBS', l_instance_id);
1099
1100 if l_stmt is null then
1101 return;
1102 end if;
1103
1104 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1105
1106 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;
1107 end if;
1108
1109 end if;
1110
1111 if l_plan_type = 6 then
1112
1113 /* Get the end date for the SNO plan */
1114 l_end_date := msd_dem_common_utilities.get_sno_plan_cutoff_date (p_plan_id);
1115
1116 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_PLANS', l_instance_id);
1117
1118 if l_stmt is null then
1119 return;
1120 end if;
1121
1122 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1123
1124 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);
1125
1126 if p_populate = 1 then
1127
1128 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_PLANS_POP_SNO', l_instance_id);
1129
1130 if l_stmt is null then
1131 return;
1132 end if;
1133
1134 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1135
1136 execute immediate l_stmt using l_plan_name, l_start_date, l_end_date, p_plan_id, p_plan_id;
1137
1138 end if;
1139
1140 end if;
1141
1142 commit;
1143
1144
1145 end load_plan_gl;
1146
1147
1148 procedure load_resource_gl(p_plan_id number)
1149
1150 is
1151
1152 cursor get_plan_info is
1153 select sr_instance_id, compile_designator, plan_type, curr_start_date, cutoff_date
1154 from msc_plans
1155 where plan_id = p_plan_id;
1156
1157 l_plan_type number;
1158
1159 l_plan_name varchar2(250);
1160
1161 l_instance_id number;
1162
1163 l_start_date date;
1164 l_end_date date;
1165
1166 l_stmt varchar2(4000);
1167 l_retcode number;
1168
1169 begin
1170
1171 open get_plan_info;
1172 fetch get_plan_info into l_instance_id, l_plan_name, l_plan_type, l_start_date, l_end_date;
1173 close get_plan_info;
1174
1175 l_stmt := ' SELECT spd.from_date FROM ' || g_schema || '.supply_plan sp, '
1176 || g_schema || '.supply_plan_dates spd '
1177 || ' WHERE sp.plan_id = ' || p_plan_id
1178 || ' AND spd.supply_plan_id = sp.supply_plan_id ';
1179 EXECUTE IMMEDIATE l_stmt INTO l_start_date;
1180
1181
1182 if l_plan_type <> 6 then
1183
1184 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_SCENARIO_RESOURCES_EBS', l_instance_id);
1185
1186 if l_stmt is null then
1187 return;
1188 end if;
1189
1190 l_stmt := replace(l_stmt, 'C_PLAN_NAME', '''' || l_plan_name || '''');
1191 l_stmt := replace(l_stmt, 'C_PLAN_ID', p_plan_id);
1192 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1193
1194 execute immediate l_stmt;
1195
1196 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_RESOURCES_EBS', l_instance_id);
1197
1198 if l_stmt is null then
1199 return;
1200 end if;
1201
1202 l_stmt := replace(l_stmt, 'C_RESOURCE_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1203 l_stmt := replace(l_stmt, 'C_PLAN_NAME', '''' || l_plan_name || '''');
1204 l_stmt := replace(l_stmt, 'C_PLAN_ID', p_plan_id);
1205 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1206
1207 execute immediate l_stmt;
1208
1209 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_RESOURCE_POP_EBS', l_instance_id);
1210
1211 l_stmt := replace(l_stmt, 'C_PLAN_NAME', '''' || l_plan_name || '''');
1212 l_stmt := replace(l_stmt, 'C_PLAN_ID', p_plan_id);
1213 l_stmt := replace(l_stmt, 'C_PLAN_START_DATE', 'to_date(''' || to_char(l_start_date, 'dd-mm-yyyy') || ''',''dd-mm-yyyy'')');
1214 l_stmt := replace(l_stmt, 'C_PLAN_END_DATE', 'to_date(''' || to_char(l_end_date, 'dd-mm-yyyy') || ''',''dd-mm-yyyy'')');
1215 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1216
1217 execute immediate l_stmt;
1218
1219 end if;
1220
1221 if l_plan_type = 6 then
1222
1223 /* Get the end date for the SNO plan */
1224 l_end_date := msd_dem_common_utilities.get_sno_plan_cutoff_date (p_plan_id);
1225
1226 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_SCENARIO_RESOURCES_SNO', l_instance_id);
1227
1228 if l_stmt is null then
1229 return;
1230 end if;
1231
1232 l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1233 l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));
1234 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1235
1236 execute immediate l_stmt;
1237
1238 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_RESOURCES_SNO', l_instance_id);
1239
1240 if l_stmt is null then
1241 return;
1242 end if;
1243
1244 l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1245 l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));
1246 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1247
1248 execute immediate l_stmt;
1249
1250 msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_RESOURCE_POP_SNO', l_instance_id);
1251
1252 if l_stmt is null then
1253 return;
1254 end if;
1255
1256 l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1257 l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));
1258 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1259 l_stmt := replace(l_stmt, 'C_PLAN_START_DATE', 'to_date(''' || to_char(l_start_date, 'dd-mm-yyyy') || ''',''dd-mm-yyyy'')');
1260 l_stmt := replace(l_stmt, 'C_PLAN_END_DATE', 'to_date(''' || to_char(l_end_date, 'dd-mm-yyyy') || ''',''dd-mm-yyyy'')');
1261
1262 execute immediate l_stmt;
1263
1264 end if;
1265
1266 commit;
1267
1268
1269 end;
1270
1271
1272
1273
1274 procedure load_item_locs( p_plan_id number)
1275 is
1276
1277 errbuf varchar2(1000);
1278 retcode number;
1279
1280 cursor get_instance_id is
1281 select sr_instance_id
1282 from msc_plans
1283 where plan_id = p_plan_id;
1284
1285 l_instance_id number;
1286
1287 begin
1288
1289 open get_instance_id;
1290 fetch get_instance_id into l_instance_id;
1291 close get_instance_id;
1292
1293
1294 msd_dem_collect_level_types.collect_levels(errbuf, retcode, l_instance_id, 2, p_plan_id);
1295
1296 msd_dem_collect_level_types.collect_levels(errbuf, retcode, l_instance_id, 1, p_plan_id);
1297
1298
1299 end load_item_locs;
1300
1301
1302
1303 /*** PUBLIC PROCEDURES ***
1304 * SET_PLAN_ATTRIBUTES
1305 * LOAD_PLAN_DATA
1306 * LOAD_PLAN_MEMBERS
1307 * POST_DOWNLOAD_HOOK
1308 * LOAD_ITEM_COST
1309 * WAIT_UNTIL_DOWNLOAD_COMPLETE
1310 * COLLECT_SCI_DATA
1311 * LAUNCH_SCI_DATA_LOADS
1312 */
1313
1314
1315
1316 procedure set_plan_attributes(p_member_id in number) is
1317
1318 Type pln_id is Ref Cursor;
1319 c_plan_id pln_id;
1320
1321 p_plan_id number;
1322 l_sql varchar2(1000) := NULL;
1323
1324 g_schema varchar2(50);
1325 x_small_sql varchar2(240);
1326
1327
1328 Begin
1329
1330 /* Inserting an entry into Integ_Status table
1331 that loading of plan is running. */
1332
1333 l_sql := 'Insert into integ_status(user_name, process, stage, status, info, status_date) values (''DMTRA_TEMPLATE'',
1334 ''LOAD_PLAN_DATA'',
1335 ''LOAD_PLAN_DATA'',
1336 ''RUNNING'', '
1337 ||''''|| ' ' ||''','
1338 ||''''||sysdate ||''')' ;
1339
1340 execute immediate l_sql;
1341
1342 l_sql := 'select plan_id from supply_plan
1343 where supply_plan_id = ' ||p_member_id;
1344
1345 /* Get the ASCP or SNO plan_id and call procedure load_plan_gl */
1346
1347 open c_plan_id for l_sql;
1348 fetch c_plan_id into p_plan_id;
1349 close c_plan_id ;
1350
1351 /* Alter session to APPS */
1352 x_small_sql := 'alter session set current_schema = APPS';
1353 EXECUTE IMMEDIATE x_small_sql;
1354
1355 g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
1356 IF (g_schema IS NULL)
1357 THEN
1358 log_message ('Error: msd_dem_sop.set_plan_attributes - Unable to find schema name');
1359 END IF;
1360
1361 load_plan_gl(p_plan_id, 0);
1362
1363 /* Alter session to demantra schema */
1364 x_small_sql := 'alter session set current_schema = ' || g_schema;
1365 EXECUTE IMMEDIATE x_small_sql;
1366
1367 EXCEPTION
1368 when others then
1369 return;
1370 End set_plan_attributes;
1371
1372
1373
1374
1375 /*
1376 *
1377 */
1378 PROCEDURE LOAD_PLAN_DATA (
1379 p_member_id IN NUMBER )
1380 IS
1381
1382 /*** LOCAL VARIABLES - BEGIN ***/
1383
1384 x_errbuf VARCHAR2(200) := NULL;
1385 x_retcode VARCHAR2(100) := NULL;
1386
1387 x_plan_id NUMBER := NULL;
1388 x_plan_type NUMBER := NULL;
1389 x_small_sql VARCHAR2(600) := NULL;
1390 x_sno_sql VARCHAR2(600) := NULL;
1391
1392
1393 /*** LOCAL VARIABLES - END ***/
1394
1395 BEGIN
1396
1397
1398 /* Alter session to APPS */
1399 x_small_sql := 'alter session set current_schema = APPS';
1400 EXECUTE IMMEDIATE x_small_sql;
1401
1402 g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
1403 IF (g_schema IS NULL)
1404 THEN
1405 log_message ('Error(1): msd_dem_sop.load_plan_data - Unable to find schema name');
1406 RETURN;
1407 END IF;
1408
1409 /* Alter session to demantra schema */
1410 x_small_sql := 'alter session set current_schema = ' || g_schema;
1411
1412 log_debug ('Entering: msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1413
1414 /* Get plan id of the supply plan */
1415 x_plan_id := get_plan_id (p_member_id);
1416 IF (x_plan_id IS NULL)
1417 THEN
1418 log_message ('Error(2): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') ||
1419 ' - Unable to get plan id for the given plan scenario member id : ' || to_char(p_member_id));
1420 EXECUTE IMMEDIATE x_small_sql;
1421 RETURN;
1422 END IF;
1423
1424 /* Get plan type of the supply plan */
1425 x_plan_type := get_plan_type (p_member_id);
1426 IF (x_plan_type IS NULL)
1427 THEN
1428 log_message ('Error(3): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') ||
1429 ' - Unable to get plan type for the given plan scenario member id : ' || to_char(p_member_id));
1430 EXECUTE IMMEDIATE x_small_sql;
1431 RETURN;
1432 END IF;
1433
1434 /* Truncate all staging tables */
1435 truncate_staging_tables (
1436 x_errbuf,
1437 x_retcode);
1438 IF (x_retcode = -1)
1439 THEN
1440 log_message ('Error(4): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') ||
1441 ' - ' || x_errbuf );
1442 EXECUTE IMMEDIATE x_small_sql;
1443 RETURN;
1444 END IF;
1445
1446
1447 /* Call Pre-Download Hook */
1448 pre_download_hook (
1449 x_errbuf,
1450 x_retcode,
1451 x_plan_id);
1452 IF (x_retcode = -1)
1453 THEN
1454 log_message ('Error(4): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || x_errbuf);
1455 EXECUTE IMMEDIATE x_small_sql;
1456 RETURN;
1457 END IF;
1458
1459
1460 /* For ASCP plan , call Load ASCP Plan */
1461 IF (x_plan_type = 0)
1462 THEN
1463
1464 push_time_data (
1465 x_errbuf,
1466 x_retcode);
1467 IF (x_retcode = -1)
1468 THEN
1469 log_message ('Error(5): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || x_errbuf);
1470 EXECUTE IMMEDIATE x_small_sql;
1471 RETURN;
1472 END IF;
1473
1474 load_ascp_data (
1475 x_errbuf,
1476 x_retcode,
1477 x_plan_id);
1478 IF (x_retcode = -1)
1479 THEN
1480 log_message ('Error(6): msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || x_errbuf);
1481 EXECUTE IMMEDIATE x_small_sql;
1482 RETURN;
1483 END IF;
1484
1485 ELSE /* For SNO plan, call Load SNO Plan */
1486 BEGIN
1487 x_sno_sql := 'BEGIN ' || g_schema || '.SNOP_DATA_LOAD.SNO_LOAD_DATA(''' || x_plan_id || '''); END;';
1488 EXECUTE IMMEDIATE x_sno_sql;
1489 EXCEPTION
1490 WHEN OTHERS THEN
1491 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');
1492 EXECUTE IMMEDIATE x_small_sql;
1493 RETURN;
1494 END;
1495 END IF;
1496
1497
1498 /* Load Plan Scenario GL */
1499 load_plan_gl (x_plan_id, 1);
1500
1501 /* Load Plan Resource GL */
1502 load_resource_gl (x_plan_id);
1503
1504 /* Load Item Location */
1505 load_item_locs (x_plan_id);
1506
1507
1508 log_debug ('Exiting: msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1509
1510
1511 /* Alter session to demantra schema */
1512 x_small_sql := 'alter session set current_schema = ' || g_schema;
1513 EXECUTE IMMEDIATE x_small_sql;
1514
1515 EXCEPTION
1516 WHEN OTHERS THEN
1517 log_message ('Exception: msd_dem_sop.load_plan_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1518 log_message (substr(SQLERRM,1,150));
1519
1520 /* Alter session to demantra schema */
1521 x_small_sql := 'alter session set current_schema = ' || g_schema;
1522 EXECUTE IMMEDIATE x_small_sql;
1523
1524 RETURN;
1525
1526 END LOAD_PLAN_DATA;
1527
1528
1529
1530
1531 /*
1532 *
1533 */
1534 PROCEDURE LOAD_PLAN_MEMBERS
1535 IS
1536 x_small_sql varchar2(1000);
1537 BEGIN
1538
1539 log_debug ('Entering: msd_dem_sop.load_plan_members - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1540
1541 /* Alter session to APPS */
1542 x_small_sql := 'alter session set current_schema = APPS';
1543 EXECUTE IMMEDIATE x_small_sql;
1544
1545 g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
1546 IF (g_schema IS NULL)
1547 THEN
1548 log_message ('Error(1): msd_dem_sop.load_plan_data - Unable to find schema name');
1549 END IF;
1550
1551 load_plan_gl(NULL, 0);
1552
1553 /* Alter session to demantra schema */
1554 x_small_sql := 'alter session set current_schema = ' || g_schema;
1555 EXECUTE IMMEDIATE x_small_sql;
1556
1557 log_debug ('Exiting: msd_dem_sop.load_plan_members - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1558 EXCEPTION
1559 WHEN OTHERS THEN
1560 log_message ('Exception: msd_dem_sop.load_plan_members - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1561 log_message (substr(SQLERRM,1,150));
1562
1563 /* Alter session to demantra schema */
1564 x_small_sql := 'alter session set current_schema = ' || g_schema;
1565 EXECUTE IMMEDIATE x_small_sql;
1566 RETURN;
1567
1568 END LOAD_PLAN_MEMBERS;
1569
1570
1571
1572
1573 /*
1574 *
1575 */
1576 PROCEDURE POST_DOWNLOAD_HOOK (
1577 p_member_id IN NUMBER )
1578 IS
1579 BEGIN
1580 --log_debug ('Entering: msd_dem_sop.post_download_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1581 NULL;
1582 --log_debug ('Exiting: msd_dem_sop.post_download_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1583 EXCEPTION
1584 WHEN OTHERS THEN
1585 --log_message ('Exception: msd_dem_sop.post_download_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1586 --log_message (substr(SQLERRM,1,150));
1587 RETURN;
1588
1589 END POST_DOWNLOAD_HOOK;
1590
1591
1592
1593
1594 /*
1595 * This procedure loads item cost information from planning server ODS
1596 * for all DM enabled organizations into the import integration
1597 * staging table - BIIO_ITEM_COST
1598 */
1599 PROCEDURE LOAD_ITEM_COST
1600 IS
1601
1602 /*** LOCAL VARIABLES - BEGIN ***/
1603
1604 x_errbuf VARCHAR2(200) := NULL;
1605 x_retcode VARCHAR2(100) := NULL;
1606
1607 x_small_sql VARCHAR2(600) := NULL;
1608
1609 /*** LOCAL VARIABLES - END ***/
1610
1611 BEGIN
1612
1613 /* Alter session to APPS */
1614 x_small_sql := 'alter session set current_schema = APPS';
1615 EXECUTE IMMEDIATE x_small_sql;
1616
1617 g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
1618 IF (g_schema IS NULL)
1619 THEN
1620 log_message ('Error(1): msd_dem_sop.load_item_cost - Unable to find schema name');
1621 END IF;
1622
1623
1624 log_debug ('Entering: msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1625
1626 /* Delete all data (if any) from the staging table */
1627 x_small_sql := 'TRUNCATE TABLE ' || g_schema || '.BIIO_ITEM_COST';
1628 EXECUTE IMMEDIATE x_small_sql;
1629
1630 /* Delete all data (if any) from the ERR staging table */
1631 x_small_sql := 'TRUNCATE TABLE ' || g_schema || '.BIIO_ITEM_COST_ERR';
1632 EXECUTE IMMEDIATE x_small_sql;
1633
1634 push_time_data (
1635 x_errbuf,
1636 x_retcode);
1637 IF (x_retcode = -1)
1638 THEN
1639 log_message ('Error(2): msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || x_errbuf);
1640 RAISE NO_DATA_FOUND;
1641 END IF;
1642
1643 /* Load data for series Item Cost */
1644 load_series_data (
1645 x_errbuf,
1646 x_retcode,
1647 C_MSD_DEM_SOP_ITEM_COST,
1648 -1,
1649 1);
1650 IF (x_retcode = -1)
1651 THEN
1652 log_message ('Error(3): msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || x_errbuf);
1653 RAISE NO_DATA_FOUND;
1654 END IF;
1655
1656 COMMIT;
1657
1658 log_debug ('Exiting: msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1659
1660
1661 /* Alter session to demantra schema */
1662 x_small_sql := 'alter session set current_schema = ' || g_schema;
1663 EXECUTE IMMEDIATE x_small_sql;
1664
1665 EXCEPTION
1666 WHEN OTHERS THEN
1667 log_message ('Exception: msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1668 log_message (substr(SQLERRM,1,150));
1669
1670 COMMIT;
1671
1672 /* Alter session to demantra schema */
1673 x_small_sql := 'alter session set current_schema = ' || g_schema;
1674 EXECUTE IMMEDIATE x_small_sql;
1675
1676 RETURN;
1677
1678 END LOAD_ITEM_COST;
1679
1680
1681
1682
1683 /*
1684 * This procedure is called by the Wait step of the Download Plan Scenario Data workflow.
1685 *
1686 * If WF_PROCESS_LOG lists a workflow instance as running and not in the Wait step,
1687 * then this procedure will sleep for a random number of seconds and then loop. It will
1688 * exit when no workflow instances are running that are not in the Wait step.
1689 *
1690 */
1691 PROCEDURE WAIT_UNTIL_DOWNLOAD_COMPLETE IS
1692 v_schema_id number;
1693 v_sql varchar2(4000);
1694 v_status varchar2(100);
1695 BEGIN
1696 -- get id of download plan scenario data wf
1697 v_sql := 'select schema_id from wf_schemas where schema_name = ''Download Plan Scenario Data'' ';
1698 execute immediate v_sql into v_schema_id;
1699
1700 v_status := 'Running';
1701 WHILE (v_status = 'Running') LOOP
1702 -- check if an instance is running
1703 v_sql := 'select nvl((select ''Running'' from wf_process_log ' ||
1704 'where schema_id = :1 and step_id <> ''Wait'' ' ||
1705 'and status not in(0,-1,-2) and rownum = 1), ''Not Running'') from dual';
1706 execute immediate v_sql into v_status using v_schema_id;
1707
1708 -- if another workflow is running, sleep for 1 to 3 minutes
1709 IF (v_status = 'Running') THEN
1710 dbms_lock.sleep(dbms_random.value(60,180));
1711 END IF;
1712 END LOOP;
1713
1714 EXCEPTION
1715 WHEN OTHERS THEN
1716 null;
1717
1718 END WAIT_UNTIL_DOWNLOAD_COMPLETE;
1719
1720
1721
1722
1723 /*
1724 *
1725 */
1726 PROCEDURE COLLECT_SCI_DATA (
1727 errbuf OUT NOCOPY VARCHAR2,
1728 retcode OUT NOCOPY VARCHAR2,
1729 p_sr_instance_id IN NUMBER,
1730 p_collection_group IN VARCHAR2 DEFAULT '-999',
1731 p_collection_method IN NUMBER,
1732 p_hidden_param1 IN VARCHAR2,
1733 p_date_range_type IN NUMBER,
1734 p_collection_window IN NUMBER,
1735 p_from_date IN VARCHAR2,
1736 p_to_date IN VARCHAR2 )
1737 IS
1738 lv_request_id1 NUMBER := to_number(NULL);
1739 lv_request_id2 NUMBER := to_number(NULL);
1740
1741 BEGIN
1742 msd_dem_common_utilities.log_message ('Entering: msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1743
1744
1745 /* Launching two Conc. Programs (for each Import Integration
1746 Interfact definde for SCI Data) to have the collection
1747 in parallel mode.
1748 */
1749
1750 BEGIN
1751 lv_request_id1 := fnd_request.submit_request('MSD',
1752 'MSDDEMSCI',
1753 NULL,
1754 NULL,
1755 FALSE,
1756 p_sr_instance_id,
1757 p_collection_group,
1758 p_collection_method,
1759 p_date_range_type,
1760 p_collection_window,
1761 p_from_date,
1762 p_to_date,
1763 G_SCI_BACKLOG );
1764
1765 --commit;
1766 EXCEPTION
1767 WHEN OTHERS THEN
1768 msd_dem_common_utilities.log_message ('Error launching concurrent program for SCI BACKLOG Integration Interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1769 msd_dem_common_utilities.log_message (errbuf);
1770 END;
1771
1772 BEGIN
1773 lv_request_id2 := fnd_request.submit_request('MSD',
1774 'MSDDEMSCI',
1775 NULL,
1776 NULL,
1777 FALSE,
1778 p_sr_instance_id,
1779 p_collection_group,
1780 p_collection_method,
1781 p_date_range_type,
1782 p_collection_window,
1783 p_from_date,
1784 p_to_date,
1785 G_SCI_OTHER );
1786
1787 --commit;
1788 EXCEPTION
1789 WHEN OTHERS THEN
1790 msd_dem_common_utilities.log_message ('Error launching concurrent program for SCI OTHER Integration Interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1791 msd_dem_common_utilities.log_message (errbuf);
1792 END;
1793
1794 IF ( lv_request_id1 <> 0 ) AND ( lv_request_id2 <> 0 ) THEN
1795
1796 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. '
1797 || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1798 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'));
1799 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'));
1800
1801 ELSE
1802
1803 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'));
1804 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'));
1805 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'));
1806 retcode := -1 ;
1807
1808 END IF;
1809
1810 COMMIT;
1811
1812 msd_dem_common_utilities.log_message ('Exiting: msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1813 EXCEPTION
1814 WHEN OTHERS THEN
1815 retcode := -1 ;
1816 errbuf := substr(SQLERRM,1,150);
1817 msd_dem_common_utilities.log_message ('Exception: msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1818 msd_dem_common_utilities.log_message (errbuf);
1819 RETURN;
1820
1821 END COLLECT_SCI_DATA;
1822
1823
1824
1825
1826 PROCEDURE LAUNCH_SCI_DATA_LOADS (
1827 errbuf OUT NOCOPY VARCHAR2,
1828 retcode OUT NOCOPY VARCHAR2,
1829 p_sr_instance_id IN NUMBER,
1830 p_collection_group IN VARCHAR2 DEFAULT '-999',
1831 p_collection_method IN NUMBER,
1832 p_date_range_type IN NUMBER,
1833 p_collection_window IN NUMBER,
1834 p_from_date IN VARCHAR2,
1835 p_to_date IN VARCHAR2,
1836 p_entity IN NUMBER )
1837 IS
1838
1839 l_errbuff1 VARCHAR2(1000) := to_char(NULL);
1840 l_retcode1 NUMBER := 0;
1841
1842 l_errbuff2 VARCHAR2(1000) := to_char(NULL);
1843 l_retcode2 NUMBER := 0;
1844
1845 x_dem_schema VARCHAR2(50) := NULL;
1846 x_dest_table VARCHAR2(100) := NULL;
1847 l_sql_stmnt VARCHAR2(5000) := NULL;
1848
1849 CURSOR c_get_dm_schema
1850 IS
1851 SELECT owner
1852 FROM dba_objects
1853 WHERE owner = owner
1854 AND object_type = 'TABLE'
1855 AND object_name = 'MDP_MATRIX'
1856 ORDER BY created desc;
1857
1858
1859 BEGIN
1860
1861 msd_dem_common_utilities.log_message ('Entering: msd_dem_sop.launch_sci_data_loads - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1862
1863 retcode := 0;
1864
1865 /* SCI BACKLOG */
1866 IF ( p_entity = G_SCI_BACKLOG )
1867 THEN
1868
1869 /* Total Backlog */
1870 BEGIN
1871
1872 MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA
1873 (l_errbuff1,
1874 l_retcode1,
1875 p_sr_instance_id,
1876 p_collection_group,
1877 p_collection_method,
1878 p_date_range_type,
1879 p_collection_window,
1880 p_from_date,
1881 p_to_date,
1882 'BIIO_SCI_BACKLOG:MSD_TOTAL_BACKLOG',
1883 1
1884 );
1885
1886
1887
1888 IF l_retcode1 = -1
1889 THEN
1890 retcode := l_retcode1;
1891 errbuf := l_errbuff1;
1892 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'));
1893 RETURN;
1894 END IF;
1895
1896 EXCEPTION
1897 WHEN OTHERS THEN
1898 retcode := -1 ;
1899 errbuf := substr(SQLERRM,1,150);
1900 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'));
1901 msd_dem_common_utilities.log_message (errbuf);
1902 RETURN;
1903 END;
1904
1905 /* Past Due Backlog*/
1906 BEGIN
1907
1908 MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA
1909 (l_errbuff2,
1910 l_retcode2,
1911 p_sr_instance_id,
1912 p_collection_group,
1913 p_collection_method,
1914 p_date_range_type,
1915 p_collection_window,
1916 p_from_date,
1917 p_to_date,
1918 'BIIO_SCI_BACKLOG:MSD_PAST_DUE_BACKLOG',
1919 2
1920 );
1921
1922
1923
1924 IF l_retcode2 = -1
1925 THEN
1926 retcode := l_retcode2;
1927 errbuf := l_errbuff2;
1928 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'));
1929 RETURN;
1930 END IF;
1931
1932
1933 EXCEPTION
1934 WHEN OTHERS THEN
1935 retcode := -1 ;
1936 errbuf := substr(SQLERRM,1,150);
1937 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'));
1938 msd_dem_common_utilities.log_message (errbuf);
1939 RETURN;
1940 END;
1941
1942 IF ( l_retcode1 = 1 ) OR ( l_retcode2 = 1)
1943 THEN
1944 retcode := 1;
1945 msd_dem_common_utilities.log_message ('Warning Text for Total Backlog Insertion is - ' || l_errbuff1);
1946 msd_dem_common_utilities.log_message ('Warning Text for Past Due Backlog Merge is - ' || l_errbuff2);
1947 errbuf := l_errbuff1 || l_errbuff2 ;
1948 END IF;
1949
1950 /* Call - Level Code Generation Query */
1951 OPEN c_get_dm_schema;
1952 FETCH c_get_dm_schema INTO x_dem_schema;
1953 CLOSE c_get_dm_schema;
1954
1955 /* Demantra is Installed */
1956 IF (x_dem_schema IS NOT NULL)
1957 THEN
1958 x_dest_table := fnd_profile.value('MSD_DEM_SCHEMA')||'.BIIO_SCI_BACKLOG ';
1959 END IF;
1960
1961 l_sql_stmnt := ' update '||x_dest_table||' bsb '
1962 ||' set level3 = ( select mtp.partner_name '
1963 ||' from msc_trading_partners mtp, '
1964 ||' msc_tp_id_lid mtil '
1965 ||' where mtil.sr_tp_id = bsb.level3_sr_pk '
1966 ||' and mtil.sr_instance_id = (select instance_id '
1967 ||' from msc_apps_instances mai '
1968 ||' where mai.instance_code = substr(bsb.level2,1,instr(bsb.level2,'':'')-1) '
1969 ||' ) '
1970 ||' and mtil.partner_type = 2 '
1971 ||' and mtil.tp_id = mtp.partner_id '
1972 ||' )';
1973
1974 msd_dem_common_utilities.log_message(l_sql_stmnt);
1975
1976 begin
1977 execute immediate l_sql_stmnt;
1978 exception
1979 when others then
1980 null;
1981 end;
1982
1983 l_sql_stmnt := ' UPDATE ' || x_dest_table
1984 || ' SET level3 = ''' || msd_dem_sr_util.get_null_code || ''' '
1985 || ' WHERE level3 IS NULL ';
1986
1987 msd_dem_common_utilities.log_message(l_sql_stmnt);
1988
1989 begin
1990 execute immediate l_sql_stmnt;
1991 exception
1992 when others then
1993 null;
1994 end;
1995
1996 /* update dmtra_template.BIIO_SCI_BACKLOG bsb
1997 set level3 = ( select mtp.partner_name
1998 from msc_trading_partners mtp,
1999 msc_tp_id_lid mtil
2000 where mtil.sr_tp_id = bsb.level3_sr_pk
2001 and mtil.sr_instance_id = (select instance_id
2002 from msc_apps_instances mai
2003 where mai.instance_code = substr(bsb.level2,1,instr(bsb.level2,':')-1)
2004 )
2005 and mtil.partner_type = 2
2006 and mtil.tp_id = mtp.partner_id
2007 ); */
2008 /* Ends - Level Code Generation Query */
2009
2010 /* SCI OTHER */
2011
2012 ELSIF ( p_entity = G_SCI_OTHER )
2013 THEN
2014
2015 -- On-Hand Inventory
2016
2017 BEGIN
2018 MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA
2019 (l_errbuff1,
2020 l_retcode1,
2021 p_sr_instance_id,
2022 p_collection_group,
2023 p_collection_method,
2024 p_date_range_type,
2025 p_collection_window,
2026 p_from_date,
2027 p_to_date,
2028 'BIIO_SCI:MSD_ON_HAND_INVENTORY',
2029 1
2030 );
2031
2032
2033
2034
2035 IF l_retcode1 = -1
2036 THEN
2037 retcode := l_retcode1;
2038 errbuf := l_errbuff1;
2039 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'));
2040 RETURN;
2041 END IF;
2042 EXCEPTION
2043 WHEN OTHERS THEN
2044 retcode := -1 ;
2045 errbuf := substr(SQLERRM,1,150);
2046 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'));
2047 msd_dem_common_utilities.log_message (errbuf);
2048 RETURN;
2049
2050 END;
2051
2052 -- Actual Production
2053 BEGIN
2054
2055 MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA
2056 (l_errbuff2,
2057 l_retcode2,
2058 p_sr_instance_id,
2059 p_collection_group,
2060 p_collection_method,
2061 p_date_range_type,
2062 p_collection_window,
2063 p_from_date,
2064 p_to_date,
2065 'BIIO_SCI:MSD_ACTUAL_PRODUCTION',
2066 2
2067 );
2068
2069
2070
2071 IF l_retcode2 = -1
2072 THEN
2073 retcode := l_retcode2;
2074 errbuf := l_errbuff2;
2075 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'));
2076 RETURN;
2077 END IF;
2078
2079
2080 EXCEPTION
2081 WHEN OTHERS THEN
2082 retcode := -1 ;
2083 errbuf := substr(SQLERRM,1,150);
2084 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'));
2085 msd_dem_common_utilities.log_message (errbuf);
2086 RETURN;
2087 END;
2088
2089 IF ( l_retcode1 = 1 ) OR ( l_retcode2 = 1)
2090 THEN
2091 retcode := 1;
2092 msd_dem_common_utilities.log_message ('Warning Text for On-Hand Inventory Insertion is - ' || l_errbuff1);
2093 msd_dem_common_utilities.log_message ('Warning Text for Actual Production Merge is - ' || l_errbuff2);
2094 errbuf := l_errbuff1 || l_errbuff2 ;
2095 END IF;
2096 END IF; -- IF ( p_entity = G_SCI_BACKLOG )
2097
2098 msd_dem_common_utilities.log_message ('Exiting: msd_dem_sop.launch_sci_data_loads - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2099
2100 EXCEPTION
2101 WHEN OTHERS THEN
2102 retcode := -1 ;
2103 errbuf := substr(SQLERRM,1,150);
2104 msd_dem_common_utilities.log_message ('Exception: msd_dem_sop.launch_sci_data_loads- ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2105 msd_dem_common_utilities.log_message (errbuf);
2106 RETURN;
2107
2108 END LAUNCH_SCI_DATA_LOADS;
2109
2110
2111
2112 END MSD_DEM_SOP;