[Home] [Help]
PACKAGE BODY: APPS.MSD_CL_LOADERS
Source
1 PACKAGE BODY MSD_CL_LOADERS AS -- body
2 /* $Header: MSDCLLDB.pls 120.6 2010/07/06 07:13:31 sjagathe ship $ */
3 -- ========= Global Parameters ===========
4
5 -- User Environment --
6 v_current_date DATE:= sysdate;
7 v_current_user NUMBER;
8 v_applsys_schema VARCHAR2(32);
9 v_monitor_request_id NUMBER;
10 v_request_id NumTblTyp:= NumTblTyp(0);
11 v_ctl_file VarcharTblTyp:= VarcharTblTyp(0);
12 v_dat_file VarcharTblTyp:= VarcharTblTyp(0);
13 v_bad_file VarcharTblTyp:= VarcharTblTyp(0);
14 v_dis_file VarcharTblTyp:= VarcharTblTyp(0);
15 v_dat_file_path VARCHAR2(1000):='';
16 v_path_seperator VARCHAR2(5):= '/';
17 v_ctl_file_path VARCHAR2(1000):= '';
18
19 v_task_pointer NUMBER:= 0;
20
21 v_debug boolean := FALSE;
22
23 -- =========== Private Functions =============
24
25 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
26 IS
27 BEGIN
28 IF fnd_global.conc_request_id > 0 THEN
29 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
30 ELSE
31 null;
32 --DBMS_OUTPUT.PUT_LINE( pBUFF);
33 END IF;
34 EXCEPTION
35 WHEN OTHERS THEN
36 RETURN;
37 END LOG_MESSAGE;
38
39 -- =====Local Procedures =========
40
41 PROCEDURE GET_FILE_NAMES( pDataFileName VARCHAR2, pCtlFileName VARCHAR2)
42 IS
43 lv_file_name_length NUMBER:= 0;
44 lv_bad_file_name VARCHAR2(1000):= '';
45 lv_dis_file_name VARCHAR2(1000):= '';
46
47 BEGIN
48 v_ctl_file.EXTEND;
49 v_dat_file.EXTEND;
50 v_bad_file.EXTEND;
51 v_dis_file.EXTEND;
52
53 v_task_pointer:= v_task_pointer + 1;
54
55 lv_file_name_length:= instr(pDataFileName, '.', -1);
56
57 IF lv_file_name_length = 0 then
58
59 lv_bad_file_name:= pDataFileName ||'.bad';
60 lv_dis_file_name:= pDataFileName ||'.dis';
61
62 ELSE
63
64 lv_bad_file_name:= substr(pDataFileName, 1, lv_file_name_length)||'bad';
65 lv_dis_file_name:= substr(pDataFileName, 1, lv_file_name_length)||'dis';
66
67 END IF;
68
69 v_ctl_file(v_task_pointer):= v_ctl_file_path || pCtlFileName;
70 v_dat_file(v_task_pointer):= v_dat_file_path || pDataFileName;
71 v_bad_file(v_task_pointer):= v_dat_file_path || lv_bad_file_name;
72 v_dis_file(v_task_pointer):= v_dat_file_path || lv_dis_file_name;
73
74 IF v_debug THEN
75 LOG_MESSAGE('v_ctl_file('||v_task_pointer||'): '||v_ctl_file(v_task_pointer));
76 LOG_MESSAGE('v_dat_file('||v_task_pointer||'): '||v_dat_file(v_task_pointer));
77 LOG_MESSAGE('v_bad_file('||v_task_pointer||'): '||v_bad_file(v_task_pointer));
78 LOG_MESSAGE('v_dis_file('||v_task_pointer||'): '||v_dis_file(v_task_pointer));
79 END IF;
80
81 END GET_FILE_NAMES;
82
83 FUNCTION is_request_status_running RETURN NUMBER
84 IS
85 l_call_status boolean;
86 l_phase varchar2(80);
87 l_status varchar2(80);
88 l_dev_phase varchar2(80);
89 l_dev_status varchar2(80);
90 l_message varchar2(2048);
91
92 l_request_id NUMBER;
93
94 BEGIN
95
96 l_request_id:= FND_GLOBAL.CONC_REQUEST_ID;
97
98 l_call_status:= FND_CONCURRENT.GET_REQUEST_STATUS
99 ( l_request_id,
100 NULL,
101 NULL,
102 l_phase,
103 l_status,
104 l_dev_phase,
105 l_dev_status,
106 l_message);
107
108 IF l_call_status=FALSE THEN
109 LOG_MESSAGE( l_message);
110 RETURN SYS_NO;
111 END IF;
112
113 IF l_dev_phase='RUNNING' THEN
114 RETURN SYS_YES;
115 ELSE
116 RETURN SYS_NO;
117 END IF;
118
119 END is_request_status_running;
120
121 FUNCTION active_loaders RETURN NUMBER IS
122 l_call_status boolean;
123 l_phase varchar2(80);
124 l_status varchar2(80);
125 l_dev_phase varchar2(80);
126 l_dev_status varchar2(80);
127 l_message varchar2(2048);
128 l_request_id NUMBER;
129 l_active_loaders NUMBER:= 0 ;
130
131 BEGIN
132
133 FOR lc_i IN 1..(v_request_id.COUNT) LOOP
134
135 l_request_id:= v_request_id(lc_i);
136
137 l_call_status:= FND_CONCURRENT.GET_REQUEST_STATUS
138 ( l_request_id,
139 NULL,
140 NULL,
141 l_phase,
142 l_status,
143 l_dev_phase,
144 l_dev_status,
145 l_message);
146
147 IF l_call_status=FALSE THEN
148 LOG_MESSAGE( l_message);
149 END IF;
150
151 IF l_dev_phase IN ( 'PENDING','RUNNING') THEN
152 l_active_loaders:= l_active_loaders + 1;
153 END IF;
154
155 END LOOP;
156
157 RETURN l_active_loaders;
158
159 END active_loaders;
160
161 FUNCTION LAUNCH_LOADER( ERRBUF OUT NOCOPY VARCHAR2,
162 RETCODE OUT NOCOPY NUMBER)
163 RETURN NUMBER IS
164
165 lv_request_id NUMBER;
166 lv_parameters VARCHAR2(2000):= '';
167
168 BEGIN
169
170 lv_request_id:= FND_REQUEST.SUBMIT_REQUEST(
171 'MSC',
172 'MSCSLD', /* loader program called */
173 NULL, -- description
174 NULL, -- start date
175 FALSE, -- TRUE,
176 v_ctl_file(v_task_pointer),
177 v_dat_file(v_task_pointer),
178 v_dis_file(v_task_pointer),
179 v_bad_file(v_task_pointer),
180 null,
181 '10000000'); -- NUM_OF_ERRORS
182 COMMIT;
183
184 IF lv_request_id = 0 THEN
185 FND_MESSAGE.SET_NAME('MSC', 'MSC_PP_LAUNCH_LOADER_FAIL');
186 ERRBUF:= FND_MESSAGE.GET;
187 LOG_MESSAGE( ERRBUF);
188 RETCODE:= G_ERROR;
189 RETURN -1;
190 ELSE
191 FND_MESSAGE.SET_NAME('MSC', 'MSC_PP_LOADER_REQUEST_ID');
192 FND_MESSAGE.SET_TOKEN('REQUEST_ID', lv_request_id);
193 LOG_MESSAGE(FND_MESSAGE.GET);
194 END IF;
195
196 RETURN lv_request_id;
197 EXCEPTION
198 WHEN OTHERS THEN
199 LOG_MESSAGE( SQLERRM);
200 RETURN -1;
201 END LAUNCH_LOADER;
202
203 -- ===============================================================
204 PROCEDURE LAUNCH_MONITOR( ERRBUF OUT NOCOPY VARCHAR2,
205 RETCODE OUT NOCOPY NUMBER,
206 p_instance_id IN NUMBER DEFAULT NULL,
207 p_timeout IN NUMBER,
208 p_path_separator IN VARCHAR2 DEFAULT '/',
209 p_ctl_file_path IN VARCHAR2,
210 p_directory_path IN VARCHAR2,
211 p_total_worker_num IN NUMBER,
212 p_calendars IN VARCHAR2 DEFAULT NULL,
213 p_workday_patterns IN VARCHAR2 DEFAULT NULL,
214 p_shift_times IN VARCHAR2 DEFAULT NULL,
215 p_calendar_exceptions IN VARCHAR2 DEFAULT NULL,
216 p_shift_exceptions IN VARCHAR2 DEFAULT NULL,
217 p_demand_class IN VARCHAR2 DEFAULT NULL,
218 p_trading_partners IN VARCHAR2 DEFAULT NULL,
219 p_trading_partner_sites IN VARCHAR2 DEFAULT NULL,
220 p_price_list IN VARCHAR2 DEFAULT NULL,
221 p_category_set IN VARCHAR2 DEFAULT NULL,
222 p_items IN VARCHAR2 DEFAULT NULL,
223 p_item_categories IN VARCHAR2 DEFAULT NULL,
224 p_bom_headers IN VARCHAR2 DEFAULT NULL,
225 p_bom_components IN VARCHAR2 DEFAULT NULL,
226 p_uom IN VARCHAR2 DEFAULT NULL,
227 p_uom_conv IN VARCHAR2 DEFAULT NULL,
228 p_currency_conv IN VARCHAR2 DEFAULT NULL,
229 p_setup_parameters IN VARCHAR2 DEFAULT NULL,
230 p_fiscal_cal IN VARCHAR2 DEFAULT NULL,
231 p_composite_cal IN VARCHAR2 DEFAULT NULL,
232 p_level_value IN VARCHAR2 DEFAULT NULL,
233 p_level_associations IN VARCHAR2 DEFAULT NULL,
234 p_booking_data IN VARCHAR2 DEFAULT NULL,
235 p_shipment_data IN VARCHAR2 DEFAULT NULL,
236 p_mfg_forecast IN VARCHAR2 DEFAULT NULL,
237 p_cs_data IN VARCHAR2 DEFAULT NULL,
238 p_level_org_asscns IN VARCHAR2 DEFAULT NULL,
239 p_item_relationships IN VARCHAR2 DEFAULT NULL,
240 p_sales_history IN VARCHAR2 DEFAULT NULL,
241 p_auto_run_download IN NUMBER DEFAULT NULL,
242 p_install_base_history IN VARCHAR2 DEFAULT NULL,
243 p_fld_ser_usg_history IN VARCHAR2 DEFAULT NULL,
244 p_dpt_rep_usg_history IN VARCHAR2 DEFAULT NULL,
245 p_ser_part_ret_history IN VARCHAR2 DEFAULT NULL,
246 p_failure_rates IN VARCHAR2 DEFAULT NULL,
247 p_prd_ret_history IN VARCHAR2 DEFAULT NULL,
248 p_forecast_data IN VARCHAR2 DEFAULT NULL,
249 p_spf_usage_data IN VARCHAR2 DEFAULT NULL)
250 IS
251
252 lc_i PLS_INTEGER;
253
254 lv_process_time NUMBER:= 0;
255 lv_check_point NUMBER:= 0;
256 lv_request_id NUMBER:= -1;
257 lv_start_time DATE;
258
259 lv_active_loaders NUMBER:=0;
260
261 EX_PROCESS_TIME_OUT EXCEPTION;
262
263 BEGIN
264 -- ===== Switch on debug based on MRP: Debug Profile
265
266 v_debug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
267
268 -- print the parameters coming in
269
270 IF v_debug THEN
271 LOG_MESSAGE('p_timeout: '||p_timeout);
272 LOG_MESSAGE('p_path_separator: '||p_path_separator);
273 LOG_MESSAGE('p_ctl_file_path: '||p_ctl_file_path);
274 LOG_MESSAGE('p_directory_path: '||p_directory_path);
275 LOG_MESSAGE('p_total_worker_num: '||p_total_worker_num);
276 LOG_MESSAGE('p_calendars: '||p_calendars);
277 LOG_MESSAGE('p_workday_patterns: '||p_workday_patterns);
278 LOG_MESSAGE('p_shift_times:'||p_shift_times);
279 LOG_MESSAGE('p_calendar_exceptions:'||p_calendar_exceptions);
280 LOG_MESSAGE('p_shift_exceptions:'||p_shift_exceptions);
281 LOG_MESSAGE('p_setup_parameters:'||p_setup_parameters);
282 LOG_MESSAGE('p_bom_headers: '||p_bom_headers);
283 LOG_MESSAGE('p_bom_components: '||p_bom_components);
284 LOG_MESSAGE('p_items: '||p_items);
285 LOG_MESSAGE('p_category_set : '||p_category_set);
286 LOG_MESSAGE('p_item_categories: '||p_item_categories);
287 LOG_MESSAGE('p_trading_partners: '||p_trading_partners);
288 LOG_MESSAGE('p_trading_partner_sites: '||p_trading_partner_sites);
289 LOG_MESSAGE('p_level_value: '||p_level_value);
290 LOG_MESSAGE('p_level_associations: '||p_level_associations);
291 LOG_MESSAGE('p_booking_data: '||p_booking_data);
292 LOG_MESSAGE('p_shipment_data: '||p_shipment_data);
293 LOG_MESSAGE('p_mfg_forecast: '||p_mfg_forecast);
294 LOG_MESSAGE('p_price_list: '||p_price_list);
295 -- LOG_MESSAGE('p_item_list_price: '||p_item_list_price);
296 LOG_MESSAGE('p_cs_data: '||p_cs_data);
297 LOG_MESSAGE('p_item_relationships: '||p_item_relationships);
298 LOG_MESSAGE('p_level_org_asscns: '||p_level_org_asscns);
299 LOG_MESSAGE('p_currency_conv: '||p_currency_conv);
300 LOG_MESSAGE('p_uom : '||p_uom);
301 LOG_MESSAGE('p_uom_conv: '||p_uom_conv);
302 LOG_MESSAGE('p_fiscal_cal: '||p_fiscal_cal);
303 LOG_MESSAGE('p_composite_cal: '||p_composite_cal);
304 LOG_MESSAGE('p_demand_class : '||p_demand_class);
305 LOG_MESSAGE('p_sales_history : '||p_sales_history);
306 LOG_MESSAGE('p_install_base_history : '||p_install_base_history);
307 LOG_MESSAGE('p_fld_ser_usg_history : '||p_fld_ser_usg_history);
308 LOG_MESSAGE('p_dpt_rep_usg_history : '||p_dpt_rep_usg_history);
309 LOG_MESSAGE('p_ser_part_ret_history : '||p_ser_part_ret_history);
310 LOG_MESSAGE('p_failure_rates : '||p_failure_rates);
311 LOG_MESSAGE('p_prd_ret_history : '||p_prd_ret_history);
312 LOG_MESSAGE('p_forecast_data : '||p_forecast_data);
313 LOG_MESSAGE('p_spf_usage_data : '||p_spf_usage_data);
314
315 END IF;
316
317 -- get the ctl file path. If last character is not path seperator add it
318
319 v_path_seperator:= p_path_separator;
320
321 v_ctl_file_path := p_ctl_file_path;
322
323 IF v_ctl_file_path IS NOT NULL THEN
324 IF SUBSTR(v_ctl_file_path,-1,1) = v_path_seperator then
325 v_ctl_file_path:= v_ctl_file_path;
326 ELSE
327 v_ctl_file_path:= v_ctl_file_path || v_path_seperator;
328 END IF;
329 END IF;
330
331 -- ===== Assign the data file directory path to a global variable ===========
332
333 -- If last character is not path seperator, add it. User may specify the path in the
334 -- file name itself. Hence, if path is null, do not add seperator
335
336 IF p_directory_path IS NOT NULL THEN
337 IF SUBSTR(p_directory_path,-1,1) = v_path_seperator then
338 v_dat_file_path:= p_directory_path;
339 ELSE
340 v_dat_file_path:= p_directory_path || v_path_seperator;
341 END IF;
342 END IF;
343
344 -- ===== create the Control, Data, Bad, Discard Files lists ==================
345 IF p_calendars IS NOT NULL THEN
346 GET_FILE_NAMES( pDataFileName => p_calendars, pCtlFileName => 'MSC_ST_CALENDARS.ctl');
347 END IF;
348
349 IF p_workday_patterns IS NOT NULL THEN
350 GET_FILE_NAMES( pDataFileName => p_workday_patterns, pCtlFileName => 'MSC_ST_WORKDAY_PATTERNS.ctl');
351 END IF;
352
353 IF p_shift_times IS NOT NULL THEN
354 GET_FILE_NAMES( pDataFileName => p_shift_times, pCtlFileName => 'MSC_ST_SHIFT_TIMES.ctl');
355 END IF;
356
357 IF p_calendar_exceptions IS NOT NULL THEN
358 GET_FILE_NAMES( pDataFileName => p_calendar_exceptions, pCtlFileName => 'MSC_ST_CALENDAR_EXCEPTIONS.ctl');
359 END IF;
360
361 IF p_shift_exceptions IS NOT NULL THEN
362 GET_FILE_NAMES( pDataFileName => p_shift_exceptions, pCtlFileName => 'MSC_ST_SHIFT_EXCEPTIONS.ctl');
363 END IF;
364
365 IF p_setup_parameters IS NOT NULL THEN
366 GET_FILE_NAMES( pDataFileName => p_setup_parameters, pCtlFileName => 'MSD_ST_SETUP_PARAMETERS.ctl');
367 END IF;
368
369 IF p_bom_headers IS NOT NULL THEN
370 GET_FILE_NAMES( pDataFileName => p_bom_headers, pCtlFileName => 'MSC_ST_BOMS.ctl');
371 END IF;
372
373 IF p_bom_components IS NOT NULL THEN
374 GET_FILE_NAMES( pDataFileName => p_bom_components, pCtlFileName => 'MSC_ST_BOM_COMPONENTS.ctl');
375 END IF;
376
377 IF p_items IS NOT NULL THEN
378 GET_FILE_NAMES( pDataFileName => p_items, pCtlFileName => 'MSC_ST_SYSTEM_ITEMS.ctl');
379 END IF;
380
381 IF p_category_set IS NOT NULL THEN
382 GET_FILE_NAMES( pDataFileName => p_category_set, pCtlFileName => 'MSC_ST_CATEGORY_SETS.ctl');
383 END IF;
384
385 IF p_item_categories IS NOT NULL THEN
386 GET_FILE_NAMES( pDataFileName => p_item_categories, pCtlFileName => 'MSC_ST_ITEM_CATEGORIES.ctl');
387 END IF;
388
389 IF p_trading_partners IS NOT NULL THEN
390 GET_FILE_NAMES( pDataFileName => p_trading_partners, pCtlFileName => 'MSC_ST_TRADING_PARTNERS.ctl');
391 END IF;
392
393 IF p_trading_partner_sites IS NOT NULL THEN
394 GET_FILE_NAMES( pDataFileName => p_trading_partner_sites, pCtlFileName => 'MSC_ST_TRADING_PARTNER_SITES.ctl');
395 END IF;
396
397 IF p_level_value IS NOT NULL THEN
398 GET_FILE_NAMES( pDataFileName => p_level_value, pCtlFileName => 'MSD_ST_LEVEL_VALUES.ctl');
399 END IF;
400
401 IF p_level_associations IS NOT NULL THEN
402 GET_FILE_NAMES( pDataFileName => p_level_associations, pCtlFileName => 'MSD_ST_LEVEL_ASSOCIATIONS.ctl');
403 END IF;
404
405 IF p_booking_data IS NOT NULL THEN
406 GET_FILE_NAMES( pDataFileName => p_booking_data, pCtlFileName => 'MSD_ST_BOOKING_DATA.ctl');
407 END IF;
408
409 IF p_shipment_data IS NOT NULL THEN
410 GET_FILE_NAMES( pDataFileName => p_shipment_data, pCtlFileName => 'MSD_ST_SHIPMENT_DATA.ctl');
411 END IF;
412
413 IF p_mfg_forecast IS NOT NULL THEN
414 GET_FILE_NAMES( pDataFileName => p_mfg_forecast, pCtlFileName => 'MSD_ST_MFG_FORECAST.ctl');
415 END IF;
416
417 IF p_price_list IS NOT NULL THEN
418 GET_FILE_NAMES( pDataFileName => p_price_list, pCtlFileName => 'MSD_ST_PRICE_LIST.ctl');
419 END IF;
420 /*
421 IF p_item_list_price IS NOT NULL THEN
422 GET_FILE_NAMES( pDataFileName => p_item_list_price, pCtlFileName => 'MSD_ST_ITEM_LIST_PRICE.ctl');
423 END IF;
424 */
425 IF p_cs_data IS NOT NULL THEN
426 GET_FILE_NAMES( pDataFileName => p_cs_data, pCtlFileName => 'MSD_ST_CS_DATA.ctl');
427 END IF;
428
429 IF p_item_relationships IS NOT NULL THEN
430 GET_FILE_NAMES( pDataFileName => p_item_relationships, pCtlFileName => 'MSD_ST_ITEM_RELATIONSHIPS.ctl');
431 END IF;
432
433 IF p_level_org_asscns IS NOT NULL THEN
434 GET_FILE_NAMES( pDataFileName => p_level_org_asscns, pCtlFileName => 'MSD_ST_LEVEL_ORG_ASSCNS.ctl');
435 END IF;
436
437 IF p_currency_conv IS NOT NULL THEN
438 GET_FILE_NAMES( pDataFileName => p_currency_conv, pCtlFileName => 'MSD_ST_CURRENCY_CONVERSIONS.ctl');
439 END IF;
440
441 IF p_uom IS NOT NULL THEN
442 GET_FILE_NAMES( pDataFileName => p_uom, pCtlFileName => 'MSC_ST_UNITS_OF_MEASURE.ctl');
443 END IF;
444
445 IF p_uom_conv IS NOT NULL THEN
446 GET_FILE_NAMES( pDataFileName => p_uom_conv, pCtlFileName => 'MSD_ST_UOM_CONVERSIONS.ctl');
447 END IF;
448
449 IF p_fiscal_cal IS NOT NULL THEN
450 GET_FILE_NAMES( pDataFileName => p_fiscal_cal, pCtlFileName => 'MSD_ST_TIME.ctl');
451 END IF;
452
453 IF p_composite_cal IS NOT NULL THEN
454 GET_FILE_NAMES( pDataFileName => p_composite_cal, pCtlFileName => 'MSD_ST_COMPOSITE_CALENDARS.ctl');
455 END IF;
456
457 IF p_demand_class IS NOT NULL THEN
458 GET_FILE_NAMES( pDataFileName => p_demand_class, pCtlFileName => 'MSC_ST_DEMAND_CLASSES.ctl');
459 END IF;
460
461 IF p_sales_history IS NOT NULL THEN
462 GET_FILE_NAMES( pDataFileName => p_sales_history, pCtlFileName => 'T_SRC_SALES_TMPL.ctl');
463 END IF;
464
465 IF p_install_base_history IS NOT NULL THEN
466 GET_FILE_NAMES( pDataFileName => p_install_base_history, pCtlFileName => 'MSD_DEM_INS_BASE_HISTORY.ctl');
467 END IF;
468
469 IF p_dpt_rep_usg_history IS NOT NULL THEN
470 GET_FILE_NAMES( pDataFileName => p_dpt_rep_usg_history, pCtlFileName => 'MSD_DEM_DPT_REP_USG_HISTORY.ctl');
471 END IF;
472
473 IF p_fld_ser_usg_history IS NOT NULL THEN
474 GET_FILE_NAMES( pDataFileName => p_fld_ser_usg_history, pCtlFileName => 'MSD_DEM_FLD_SER_USG_HISTORY.ctl');
475 END IF;
476
477 IF p_ser_part_ret_history IS NOT NULL THEN
478 GET_FILE_NAMES( pDataFileName => p_ser_part_ret_history, pCtlFileName => 'MSD_DEM_SRP_RETURN_HISTORY.ctl');
479 END IF;
480
481 IF p_failure_rates IS NOT NULL THEN
482 GET_FILE_NAMES( pDataFileName => p_failure_rates, pCtlFileName => 'MSC_ITEM_FAILURE_RATES.ctl');
483 END IF;
484
485 IF p_prd_ret_history IS NOT NULL THEN
486 GET_FILE_NAMES( pDataFileName => p_prd_ret_history, pCtlFileName => 'MSD_DEM_RETURN_HISTORY.ctl');
487 END IF;
488
489 IF p_forecast_data IS NOT NULL THEN
490 GET_FILE_NAMES( pDataFileName => p_forecast_data, pCtlFileName => 'MSD_DP_SCENARIO_ENTRIES.ctl');
491 END IF;
492
493 IF p_spf_usage_data IS NOT NULL THEN
494 GET_FILE_NAMES( pDataFileName => p_spf_usage_data, pCtlFileName => 'MSD_SPF_ST_SPARE_USAGE.ctl');
495 END IF;
496
497 v_request_id.EXTEND(v_task_pointer);
498
499 v_task_pointer:= 0;
500
501 -- ============ Lauch the Loaders here ===============
502
503 LOOP
504
505 IF active_loaders < p_total_worker_num THEN
506
507 EXIT WHEN is_request_status_running <> SYS_YES;
508
509 IF v_task_pointer < (v_ctl_file.LAST - 1) THEN
510
511 v_task_pointer:= v_task_pointer + 1;
512
513 lv_request_id:= LAUNCH_LOADER (ERRBUF => ERRBUF,
514 RETCODE => RETCODE);
515
516 IF lv_request_id <> -1 THEN
517 v_request_id(v_task_pointer):= lv_request_id;
518 END IF;
519
520 ELSIF active_loaders = 0 THEN
521
522 EXIT;
523
524 ELSE
525
526 select (SYSDATE- START_TIME) into lv_process_time from dual;
527
528 IF lv_process_time > p_timeout/1440.0 THEN Raise EX_PROCESS_TIME_OUT; END IF;
529
530 DBMS_LOCK.SLEEP( 5);
531
532 END IF;
533
534 ELSE
535 -- ============= Check the execution time ==============
536
537 select (SYSDATE- START_TIME) into lv_process_time from dual;
538
539 IF lv_process_time > p_timeout/1440.0 THEN Raise EX_PROCESS_TIME_OUT; END IF;
540
541 DBMS_LOCK.SLEEP( 5);
542
543 END IF;
544
545 END LOOP;
546
547 lv_check_point:= 3;
548
549 IF RETCODE= G_ERROR THEN RETURN; END IF;
550
551 EXCEPTION
552
553 WHEN EX_PROCESS_TIME_OUT THEN
554
555 ROLLBACK;
556
557 FND_MESSAGE.SET_NAME('MSC', 'MSC_TIMEOUT');
558 ERRBUF:= FND_MESSAGE.GET;
559 RETCODE:= G_ERROR;
560 LOG_MESSAGE( ERRBUF);
561
562 WHEN others THEN
563
564 ROLLBACK;
565
566 ERRBUF := SQLERRM;
567 RETCODE:= G_ERROR;
568 LOG_MESSAGE( ERRBUF);
569
570 END LAUNCH_MONITOR;
571
572 END MSD_CL_LOADERS;