[Home] [Help]
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 ***/
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;
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:
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
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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:
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;
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:
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
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:
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
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;
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:
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
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
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:
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:
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);
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:
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:
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'));
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);
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:
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)
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;
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;
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;
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;
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);
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:
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.');
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;
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:
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:
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:
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);
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;
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:
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');
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$', ''));
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);
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 (
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;
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
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)
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
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;
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
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$',''));
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);
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:
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;
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
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,
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,
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
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
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);
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);
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;
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);
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);
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;
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)
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 ' ||
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 ' ||
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 ' ||
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;
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:
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:
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:
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');
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:
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;
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
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;
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);
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 (
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;
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
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);
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;
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'));
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;
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);
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;
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;
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';
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);
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;
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);
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:
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
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
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;
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 **/
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. */
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:
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)
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;
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;
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;
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;
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:
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;
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:
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:
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;
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'',
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
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;
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));
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;
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
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));
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;
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'));
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;
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 */
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
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;
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:
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
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;
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:
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);
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);
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;
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 */
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 ;
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;
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;
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 */
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);
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';
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;
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;
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:
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
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;
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 */
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:
3731:
3732: END KILL_JOBS;
3733:
3734:
3735: END MSD_DEM_SOP;