[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_LOADERS
Source
1 PACKAGE BODY MSC_CL_LOADERS AS -- body
2 /* $Header: MSCCLLDB.pls 120.4 2008/01/26 09:29:36 abhikuma 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
205 PROCEDURE LAUNCH_MONITOR( ERRBUF OUT NOCOPY VARCHAR2,
206 RETCODE OUT NOCOPY NUMBER,
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_demand_class IN VARCHAR2 DEFAULT NULL,
213 p_bom_headers IN VARCHAR2 DEFAULT NULL,
214 p_bom_components IN VARCHAR2 DEFAULT NULL,
215 p_bom_comp_subs IN VARCHAR2 DEFAULT NULL,
216 p_items IN VARCHAR2 DEFAULT NULL,
217 p_routing_headers IN VARCHAR2 DEFAULT NULL,
218 p_routing_ops IN VARCHAR2 DEFAULT NULL,
219 p_routing_op_resources IN VARCHAR2 DEFAULT NULL,
220 p_resource_groups IN VARCHAR2 DEFAULT NULL,
221 p_dept_resources IN VARCHAR2 DEFAULT NULL,
222 p_resource_changes IN VARCHAR2 DEFAULT NULL,
223 p_resource_shifts IN VARCHAR2 DEFAULT NULL,
224 p_project_tasks IN VARCHAR2 DEFAULT NULL,
225 p_uoms IN VARCHAR2 DEFAULT NULL,
226 p_uom_class_convs IN VARCHAR2 DEFAULT NULL,
227 p_uom_convs IN VARCHAR2 DEFAULT NULL,
228 p_category_sets IN VARCHAR2 DEFAULT NULL,
229 p_item_categories IN VARCHAR2 DEFAULT NULL,
230 p_item_sourcings IN VARCHAR2 DEFAULT NULL,
231 p_calendars IN VARCHAR2 DEFAULT NULL,
232 p_workday_patterns IN VARCHAR2 DEFAULT NULL,
233 p_shift_times IN VARCHAR2 DEFAULT NULL,
234 p_calendar_exceptions IN VARCHAR2 DEFAULT NULL,
235 p_shift_exceptions IN VARCHAR2 DEFAULT NULL,
236 p_resource_requirements IN VARCHAR2 DEFAULT NULL,
237 p_item_suppliers IN VARCHAR2 DEFAULT NULL,
238 p_supplier_capacities IN VARCHAR2 DEFAULT NULL,
239 p_supplier_flexFences IN VARCHAR2 DEFAULT NULL,
240 p_safety_stocks IN VARCHAR2 DEFAULT NULL,
241 p_trading_partners IN VARCHAR2 DEFAULT NULL,
242 p_trading_partner_sites IN VARCHAR2 DEFAULT NULL,
243 p_loc_associations IN VARCHAR2 DEFAULT NULL,
244 p_sub_inventories IN VARCHAR2 DEFAULT NULL,
245 p_partner_contacts IN VARCHAR2 DEFAULT NULL,
246 p_ship_methods IN VARCHAR2 DEFAULT NULL,
247 p_supply_work_order IN VARCHAR2 DEFAULT NULL,
248 p_supply_requisitions IN VARCHAR2 DEFAULT NULL,
249 p_supply_onhand IN VARCHAR2 DEFAULT NULL,
250 p_supply_intransit IN VARCHAR2 DEFAULT NULL,
251 p_supply_PO IN VARCHAR2 DEFAULT NULL,
252 p_planOrder_designators IN VARCHAR2 DEFAULT NULL,
253 p_supply_plan_orders IN VARCHAR2 DEFAULT NULL,
254 p_supply_ASN IN VARCHAR2 DEFAULT NULL,
255 p_demand_SO IN VARCHAR2 DEFAULT NULL,
256 p_forecast_designators IN VARCHAR2 DEFAULT NULL,
257 p_demand_forecast IN VARCHAR2 DEFAULT NULL,
258 p_MDS_designators IN VARCHAR2 DEFAULT NULL,
259 p_demand_MDS IN VARCHAR2 DEFAULT NULL,
260 p_demand_WO_component IN VARCHAR2 DEFAULT NULL,
261 -- p_demand_flow_component IN VARCHAR2 DEFAULT NULL,
262 p_reservations IN VARCHAR2 DEFAULT NULL,
263 p_item_cst IN VARCHAR2 DEFAULT NULL,
264 p_sce_supp_dem IN VARCHAR2 DEFAULT NULL,
265 p_sce_comp_users IN VARCHAR2 DEFAULT NULL,
266 p_item_substitute IN VARCHAR2 DEFAULT NULL, -- Product Item Substitue
267 p_planners IN VARCHAR2 DEFAULT NULL, -- Planners
268 p_operation_networks IN VARCHAR2 DEFAULT NULL, -- Operation Networks (OSFM)
269 p_co_products IN VARCHAR2 DEFAULT NULL, -- Co Products (OSFM)
270 p_job_op_network IN VARCHAR2 DEFAULT NULL, -- Lot based Job details (OSFM)
271 p_job_operations IN VARCHAR2 DEFAULT NULL,
272 p_job_req_ops IN VARCHAR2 DEFAULT NULL,
273 p_job_op_resources IN VARCHAR2 DEFAULT NULL,
274 p_profiles IN VARCHAR2 DEFAULT NULL, -- profiles
275 p_calendar_assignments IN VARCHAR2 DEFAULT NULL, -- Calendar Assignment
276 p_regions IN VARCHAR2 DEFAULT NULL, -- Regions
277 p_zone_regions IN VARCHAR2 DEFAULT NULL,
278 p_region_locations IN VARCHAR2 DEFAULT NULL,
279 p_region_sites IN VARCHAR2 DEFAULT NULL,
280 p_iro_supplies IN VARCHAR2 DEFAULT NULL,
281 p_iro_demand IN VARCHAR2 DEFAULT NULL,
282 p_ero_supplies IN VARCHAR2 DEFAULT NULL,
283 p_ero_demand IN VARCHAR2 DEFAULT NULL)
284 IS
285
286 lc_i PLS_INTEGER;
287 lv_process_time NUMBER:= 0;
288 lv_check_point NUMBER:= 0;
289 lv_request_id NUMBER:= -1;
290 lv_start_time DATE;
291
292 lv_active_loaders NUMBER:=0;
293
294 EX_PROCESS_TIME_OUT EXCEPTION;
295
296 BEGIN
297 -- ===== Switch on debug based on MRP: Debug Profile
298
299 v_debug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
300
301 -- print the parameters coming in
302
303 IF v_debug THEN
304 LOG_MESSAGE('p_timeout: '||p_timeout);
305 LOG_MESSAGE('p_path_separator: '||p_path_separator);
306 LOG_MESSAGE('p_ctl_file_path: '||p_ctl_file_path);
307 LOG_MESSAGE('p_directory_path: '||p_directory_path);
308 LOG_MESSAGE('p_total_worker_num: '||p_total_worker_num);
309 LOG_MESSAGE('p_demand_class: '||p_demand_class);
310 LOG_MESSAGE('p_bom_headers: '||p_bom_headers);
311 LOG_MESSAGE('p_bom_components: '||p_bom_components);
312 LOG_MESSAGE('p_bom_comp_subs: '||p_bom_comp_subs);
313 LOG_MESSAGE('p_items: '||p_items);
314 LOG_MESSAGE('p_routing_headers: '||p_routing_headers);
315 LOG_MESSAGE('p_routing_ops: '||p_routing_ops);
316 LOG_MESSAGE('p_routing_op_resources: '||p_routing_op_resources);
317 LOG_MESSAGE('p_resource_groups: '||p_resource_groups);
318 LOG_MESSAGE('p_dept_resources: '||p_dept_resources);
319 LOG_MESSAGE('p_resource_changes: '||p_resource_changes);
320 LOG_MESSAGE('p_resource_shifts: '||p_resource_shifts);
321 LOG_MESSAGE('p_project_tasks: '||p_project_tasks);
322 LOG_MESSAGE('p_uoms: '||p_uoms);
323 LOG_MESSAGE('p_uom_class_convs: '||p_uom_class_convs);
324 LOG_MESSAGE('p_uom_convs: '||p_uom_convs);
325 LOG_MESSAGE('p_category_sets: '||p_category_sets);
326 LOG_MESSAGE('p_item_categories: '||p_item_categories);
327 LOG_MESSAGE('p_item_sourcings: '||p_item_sourcings);
328 LOG_MESSAGE('p_calendars: '||p_calendars);
329 LOG_MESSAGE('p_workday_patterns: '||p_workday_patterns);
330 LOG_MESSAGE('p_shift_times: '||p_shift_times);
331 LOG_MESSAGE('p_calendar_exceptions: '||p_calendar_exceptions);
332 LOG_MESSAGE('p_shift_exceptions: '||p_shift_exceptions);
333 LOG_MESSAGE('p_resource_requirements: '||p_resource_requirements);
334 LOG_MESSAGE('p_item_suppliers: '||p_item_suppliers);
335 LOG_MESSAGE('p_supplier_capacities: '||p_supplier_capacities);
336 LOG_MESSAGE('p_supplier_flexFences: '||p_supplier_flexFences);
337 LOG_MESSAGE('p_safety_stocks: '||p_safety_stocks);
338 LOG_MESSAGE('p_trading_partners: '||p_trading_partners);
339 LOG_MESSAGE('p_trading_partner_sites: '||p_trading_partner_sites);
340 LOG_MESSAGE('p_loc_associations: '||p_loc_associations);
341 LOG_MESSAGE('p_sub_inventories: '||p_sub_inventories);
342 LOG_MESSAGE('p_partner_contacts: '||p_partner_contacts);
343 LOG_MESSAGE('p_ship_methods: '||p_ship_methods);
344 LOG_MESSAGE('p_supply_work_order: '||p_supply_work_order);
345 LOG_MESSAGE('p_supply_requisitions: '||p_supply_requisitions);
346 LOG_MESSAGE('p_supply_onhand: '||p_supply_onhand);
347 LOG_MESSAGE('p_supply_intransit: '||p_supply_intransit);
348 LOG_MESSAGE('p_supply_PO: '||p_supply_PO);
349 LOG_MESSAGE('p_planOrder_designators: '||p_planOrder_designators);
350 LOG_MESSAGE('p_supply_plan_orders: '||p_supply_plan_orders);
351 LOG_MESSAGE('p_supply_ASN: '||p_supply_ASN);
352 LOG_MESSAGE('p_demand_SO: '||p_demand_SO);
353 LOG_MESSAGE('p_forecast_designators: '||p_forecast_designators);
354 LOG_MESSAGE('p_demand_forecast: '||p_demand_forecast);
355 LOG_MESSAGE('p_MDS_designators: '||p_MDS_designators);
356 LOG_MESSAGE('p_demand_MDS: '||p_demand_MDS);
357 LOG_MESSAGE('p_demand_WO_component: '||p_demand_WO_component);
358 -- LOG_MESSAGE('p_demand_flow_component: '||p_demand_flow_component);
359 LOG_MESSAGE('p_reservations: '||p_reservations);
360 LOG_MESSAGE('p_item_cst: '||p_item_cst);
361 LOG_MESSAGE('p_sce_supp_dem: '||p_sce_supp_dem);
362 LOG_MESSAGE('p_item_substitute: '||p_item_substitute); -- Product Item Substitue
363 LOG_MESSAGE('p_planners: '||p_planners); -- Planners
364 LOG_MESSAGE('p_operation_networks: '||p_operation_networks); -- Operation Networks (OSFM)
365 LOG_MESSAGE('p_co_products: '||p_co_products); -- Co Products (OSFM)
366 LOG_MESSAGE('p_job_op_network: '||p_job_op_network);
367 LOG_MESSAGE('p_job_operations: '||p_job_operations);
368 LOG_MESSAGE('p_job_req_ops: '||p_job_req_ops);
369 LOG_MESSAGE('p_job_op_resources: '||p_job_op_resources);
370 LOG_MESSAGE('p_profiles: '|| p_profiles);
371 LOG_MESSAGE('p_calendar_assignments: '||p_calendar_assignments); -- Calenadr Assignments
372 LOG_MESSAGE('p_regions: ' || p_regions);
373 LOG_MESSAGE('p_zone_regions: ' || p_zone_regions);
374 LOG_MESSAGE('p_region_locations: ' || p_region_locations);
375 LOG_MESSAGE('p_region_sites: ' || p_region_sites);
376 LOG_MESSAGE('p_iro_supplies: ' || p_iro_supplies);
377 LOG_MESSAGE('p_iro_demand: ' || p_iro_demand);
378 LOG_MESSAGE('p_ero_supplies: ' || p_ero_supplies);
379 LOG_MESSAGE('p_ero_demand: ' || p_ero_demand);
380 END IF;
381
382 -- get the ctl file path. If last character is not path seperator add it
383
384 v_path_seperator:= p_path_separator;
385
386 v_ctl_file_path := p_ctl_file_path;
387
388 IF v_ctl_file_path IS NOT NULL THEN
389 IF SUBSTR(v_ctl_file_path,-1,1) = v_path_seperator then
390 v_ctl_file_path:= v_ctl_file_path;
391 ELSE
392 v_ctl_file_path:= v_ctl_file_path || v_path_seperator;
393 END IF;
394 END IF;
395
396 -- ===== Assign the data file directory path to a global variable ===========
397
398 -- If last character is not path seperator, add it. User may specify the path in the
399 -- file name itself. Hence, if path is null, do not add seperator
400
401 IF p_directory_path IS NOT NULL THEN
402 IF SUBSTR(p_directory_path,-1,1) = v_path_seperator then
403 v_dat_file_path:= p_directory_path;
404 ELSE
405 v_dat_file_path:= p_directory_path || v_path_seperator;
406 END IF;
407 END IF;
408
409 -- ===== create the Control, Data, Bad, Discard Files lists ==================
410 IF p_demand_class IS NOT NULL THEN
411 GET_FILE_NAMES( pDataFileName => p_demand_class, pCtlFileName => 'MSC_ST_DEMAND_CLASSES.ctl');
412 END IF;
413 IF p_bom_headers IS NOT NULL THEN
414 GET_FILE_NAMES( pDataFileName => p_bom_headers, pCtlFileName => 'MSC_ST_BOMS.ctl');
415 END IF;
416 IF p_bom_components IS NOT NULL THEN
417 GET_FILE_NAMES( pDataFileName => p_bom_components, pCtlFileName => 'MSC_ST_BOM_COMPONENTS.ctl');
418 END IF;
419 IF p_bom_comp_subs IS NOT NULL THEN
420 GET_FILE_NAMES( pDataFileName => p_bom_comp_subs, pCtlFileName => 'MSC_ST_COMPONENT_SUBSTITUTES.ctl');
421 END IF;
422 IF p_items IS NOT NULL THEN
423 GET_FILE_NAMES( pDataFileName => p_items, pCtlFileName => 'MSC_ST_SYSTEM_ITEMS.ctl');
424 END IF;
425 IF p_routing_headers IS NOT NULL THEN
426 GET_FILE_NAMES( pDataFileName => p_routing_headers, pCtlFileName => 'MSC_ST_ROUTINGS.ctl');
427 END IF;
428 IF p_routing_ops IS NOT NULL THEN
429 GET_FILE_NAMES( pDataFileName => p_routing_ops, pCtlFileName => 'MSC_ST_ROUTING_OPERATIONS.ctl');
430 END IF;
431 IF p_routing_op_resources IS NOT NULL THEN
432 GET_FILE_NAMES( pDataFileName => p_routing_op_resources, pCtlFileName => 'MSC_ST_OPERATION_RESOURCES.ctl');
433 END IF;
434 IF p_resource_groups IS NOT NULL THEN
435 GET_FILE_NAMES( pDataFileName => p_resource_groups, pCtlFileName => 'MSC_ST_RESOURCE_GROUPS.ctl');
436 END IF;
437 IF p_dept_resources IS NOT NULL THEN
438 GET_FILE_NAMES( pDataFileName => p_dept_resources, pCtlFileName => 'MSC_ST_DEPARTMENT_RESOURCES.ctl');
439 END IF;
440 IF p_resource_changes IS NOT NULL THEN
441 GET_FILE_NAMES( pDataFileName => p_resource_changes, pCtlFileName => 'MSC_ST_RESOURCE_CHANGES.ctl');
442 END IF;
443 IF p_resource_shifts IS NOT NULL THEN
444 GET_FILE_NAMES( pDataFileName => p_resource_shifts, pCtlFileName => 'MSC_ST_RESOURCE_SHIFTS.ctl');
445 END IF;
446 IF p_project_tasks IS NOT NULL THEN
447 GET_FILE_NAMES( pDataFileName => p_project_tasks, pCtlFileName => 'MSC_ST_PROJECT_TASKS.ctl');
448 END IF;
449 IF p_uoms IS NOT NULL THEN
450 GET_FILE_NAMES( pDataFileName => p_uoms, pCtlFileName => 'MSC_ST_UNITS_OF_MEASURE.ctl');
451 END IF;
452 IF p_uom_class_convs IS NOT NULL THEN
453 GET_FILE_NAMES( pDataFileName => p_uom_class_convs, pCtlFileName => 'MSC_ST_UOM_CLASS_CONVERSIONS.ctl');
454 END IF;
455 IF p_uom_convs IS NOT NULL THEN
456 GET_FILE_NAMES( pDataFileName => p_uom_convs, pCtlFileName => 'MSC_ST_UOM_CONVERSIONS.ctl');
457 END IF;
458 IF p_category_sets IS NOT NULL THEN
459 GET_FILE_NAMES( pDataFileName => p_category_sets, pCtlFileName => 'MSC_ST_CATEGORY_SETS.ctl');
460 END IF;
461 IF p_item_categories IS NOT NULL THEN
462 GET_FILE_NAMES( pDataFileName => p_item_categories, pCtlFileName => 'MSC_ST_ITEM_CATEGORIES.ctl');
463 END IF;
464 IF p_item_sourcings IS NOT NULL THEN
465 GET_FILE_NAMES( pDataFileName => p_item_sourcings, pCtlFileName => 'MSC_ST_ITEM_SOURCING.ctl');
466 END IF;
467 IF p_calendars IS NOT NULL THEN
468 GET_FILE_NAMES( pDataFileName => p_calendars, pCtlFileName => 'MSC_ST_CALENDARS.ctl');
469 END IF;
470 IF p_workday_patterns IS NOT NULL THEN
471 GET_FILE_NAMES( pDataFileName => p_workday_patterns, pCtlFileName => 'MSC_ST_WORKDAY_PATTERNS.ctl');
472 END IF;
473 IF p_shift_times IS NOT NULL THEN
474 GET_FILE_NAMES( pDataFileName => p_shift_times, pCtlFileName => 'MSC_ST_SHIFT_TIMES.ctl');
475 END IF;
476 IF p_calendar_exceptions IS NOT NULL THEN
477 GET_FILE_NAMES( pDataFileName => p_calendar_exceptions, pCtlFileName => 'MSC_ST_CALENDAR_EXCEPTIONS.ctl');
478 END IF;
479 IF p_shift_exceptions IS NOT NULL THEN
480 GET_FILE_NAMES( pDataFileName => p_shift_exceptions, pCtlFileName => 'MSC_ST_SHIFT_EXCEPTIONS.ctl');
481 END IF;
482 IF p_resource_requirements IS NOT NULL THEN
483 GET_FILE_NAMES( pDataFileName => p_resource_requirements, pCtlFileName => 'MSC_ST_RESOURCE_REQUIREMENTS.ctl');
484 END IF;
485 IF p_item_suppliers IS NOT NULL THEN
486 GET_FILE_NAMES( pDataFileName => p_item_suppliers, pCtlFileName => 'MSC_ST_ITEM_SUPPLIERS.ctl');
487 END IF;
488 IF p_supplier_capacities IS NOT NULL THEN
489 GET_FILE_NAMES( pDataFileName => p_supplier_capacities, pCtlFileName => 'MSC_ST_SUPPLIER_CAPACITIES.ctl');
490 END IF;
491 IF p_supplier_flexFences IS NOT NULL THEN
492 GET_FILE_NAMES( pDataFileName => p_supplier_flexFences, pCtlFileName => 'MSC_ST_SUPPLIER_FLEX_FENCES.ctl');
493 END IF;
494 IF p_safety_stocks IS NOT NULL THEN
495 GET_FILE_NAMES( pDataFileName => p_safety_stocks, pCtlFileName => 'MSC_ST_SAFETY_STOCKS.ctl');
496 END IF;
497 IF p_trading_partners IS NOT NULL THEN
498 GET_FILE_NAMES( pDataFileName => p_trading_partners, pCtlFileName => 'MSC_ST_TRADING_PARTNERS.ctl');
499 END IF;
500 IF p_trading_partner_sites IS NOT NULL THEN
501 GET_FILE_NAMES( pDataFileName => p_trading_partner_sites, pCtlFileName => 'MSC_ST_TRADING_PARTNER_SITES.ctl');
502 END IF;
503 IF p_loc_associations IS NOT NULL THEN
504 GET_FILE_NAMES( pDataFileName => p_loc_associations, pCtlFileName => 'MSC_ST_LOCATION_ASSOCIATIONS.ctl');
505 END IF;
506 IF p_sub_inventories IS NOT NULL THEN
507 GET_FILE_NAMES( pDataFileName => p_sub_inventories, pCtlFileName => 'MSC_ST_SUB_INVENTORIES.ctl');
508 END IF;
509 IF p_partner_contacts IS NOT NULL THEN
510 GET_FILE_NAMES( pDataFileName => p_partner_contacts, pCtlFileName => 'MSC_ST_PARTNER_CONTACTS.ctl');
511 END IF;
512 IF p_ship_methods IS NOT NULL THEN
513 GET_FILE_NAMES( pDataFileName => p_ship_methods, pCtlFileName =>
514 'MSC_ST_INTERORG_SHIP_METHODS.ctl');
515 END IF;
516
517 IF p_supply_work_order IS NOT NULL THEN
518 GET_FILE_NAMES( pDataFileName => p_supply_work_order, pCtlFileName => 'MSC_ST_SUPPLIES_WO.ctl');
519 END IF;
520 IF p_supply_requisitions IS NOT NULL THEN
521 GET_FILE_NAMES( pDataFileName => p_supply_requisitions, pCtlFileName => 'MSC_ST_SUPPLIES_REQ.ctl');
522 END IF;
523 IF p_supply_onhand IS NOT NULL THEN
524 GET_FILE_NAMES( pDataFileName => p_supply_onhand, pCtlFileName => 'MSC_ST_SUPPLIES_ONHAND.ctl');
525 END IF;
526 IF p_supply_intransit IS NOT NULL THEN
527 GET_FILE_NAMES( pDataFileName => p_supply_intransit, pCtlFileName => 'MSC_ST_SUPPLIES_INTRANSIT.ctl');
528 END IF;
529 IF p_supply_PO IS NOT NULL THEN
530 GET_FILE_NAMES( pDataFileName => p_supply_PO, pCtlFileName => 'MSC_ST_SUPPLIES_PO.ctl');
531 END IF;
532 IF p_planOrder_designators IS NOT NULL THEN
533 GET_FILE_NAMES( pDataFileName => p_planOrder_designators, pCtlFileName =>'MSC_ST_DESIGNATORS_PLAN_ORDERS.ctl');
534 END IF;
535 IF p_supply_plan_orders IS NOT NULL THEN
536 GET_FILE_NAMES( pDataFileName => p_supply_plan_orders, pCtlFileName => 'MSC_ST_SUPPLIES_PLAN_ORDERS.ctl');
537 END IF;
538 IF p_supply_ASN IS NOT NULL THEN
539 GET_FILE_NAMES( pDataFileName => p_supply_ASN, pCtlFileName =>'MSC_ST_SUPPLIES_ASN.ctl' );
540 END IF;
541 IF p_demand_SO IS NOT NULL THEN
542 GET_FILE_NAMES( pDataFileName => p_demand_SO, pCtlFileName => 'MSC_ST_SALES_ORDERS.ctl');
543 END IF;
544 IF p_forecast_designators IS NOT NULL THEN
545 GET_FILE_NAMES( pDataFileName => p_forecast_designators, pCtlFileName => 'MSC_ST_DESIGNATORS_FORECAST.ctl');
546 END IF;
547 IF p_demand_forecast IS NOT NULL THEN
548 GET_FILE_NAMES( pDataFileName => p_demand_forecast, pCtlFileName => 'MSC_ST_DEMANDS_FORECAST.ctl');
549 END IF;
550 IF p_MDS_designators IS NOT NULL THEN
551 GET_FILE_NAMES( pDataFileName => p_MDS_designators , pCtlFileName => 'MSC_ST_DESIGNATORS_MDS.ctl');
552 END IF;
553 IF p_demand_MDS IS NOT NULL THEN
554 GET_FILE_NAMES( pDataFileName => p_demand_MDS, pCtlFileName => 'MSC_ST_DEMANDS_MDS.ctl');
555 END IF;
556 IF p_demand_WO_component IS NOT NULL THEN
557 GET_FILE_NAMES( pDataFileName => p_demand_WO_component, pCtlFileName => 'MSC_ST_DEMANDS_WORK_ORDER.ctl');
558 END IF;
559 /* IF p_demand_flow_component IS NOT NULL THEN
560 GET_FILE_NAMES( pDataFileName => p_demand_flow_component, pCtlFileName => 'MSC_ST_DEMANDS_FLOW_SCHEDULE.ctl');
561 END IF;*/
562 IF p_reservations IS NOT NULL THEN
563 GET_FILE_NAMES( pDataFileName => p_reservations, pCtlFileName => 'MSC_ST_RESERVATIONS.ctl');
564 END IF;
565 IF p_item_cst IS NOT NULL THEN
566 GET_FILE_NAMES( pDataFileName => p_item_cst, pCtlFileName => 'MSC_ST_ITEM_CUSTOMERS.ctl');
567 END IF;
568 IF p_sce_supp_dem IS NOT NULL THEN
569 GET_FILE_NAMES( pDataFileName => p_sce_supp_dem, pCtlFileName => 'MSC_SUPDEM_LINES_INTERFACE.ctl');
570 END IF;
571 IF p_sce_comp_users IS NOT NULL THEN
572 GET_FILE_NAMES( pDataFileName => p_sce_comp_users, pCtlFileName => 'MSC_ST_COMPANY_USERS.ctl');
573 END IF;
574
575 -- Product Substitution
576
577 IF p_item_substitute IS NOT NULL THEN
578 GET_FILE_NAMES( pDataFileName => p_item_substitute, pCtlFileName => 'MSC_ST_ITEM_SUBSTITUTES.ctl');
579 END IF;
580
581 -- Planners
582
583 IF p_planners IS NOT NULL THEN
584 GET_FILE_NAMES( pDataFileName => p_planners, pCtlFileName => 'MSC_ST_PLANNERS.ctl');
585 END IF;
586 -- Opeartion Networks (OSFM)
587 IF p_operation_networks IS NOT NULL THEN
588 GET_FILE_NAMES( pDataFileName => p_operation_networks, pCtlFileName => 'MSC_ST_OPERATION_NETWORKS.ctl');
589 END IF;
590 -- Co Products (OSFM)
591 IF p_co_products IS NOT NULL THEN
592 GET_FILE_NAMES( pDataFileName => p_co_products, pCtlFileName => 'MSC_ST_CO_PRODUCTS.ctl');
593 END IF;
594
595 -- Lot based Job details (OSFM)
596
597 IF p_job_op_network IS NOT NULL THEN
598 GET_FILE_NAMES( pDataFileName => p_job_op_network, pCtlFileName => 'MSC_ST_JOB_OPERATION_NETWORKS.ctl');
599 END IF;
600
601 IF p_job_operations IS NOT NULL THEN
602 GET_FILE_NAMES( pDataFileName => p_job_operations, pCtlFileName => 'MSC_ST_JOB_OPERATIONS.ctl');
603 END IF;
604
605 IF p_job_req_ops IS NOT NULL THEN
606 GET_FILE_NAMES( pDataFileName => p_job_req_ops, pCtlFileName => 'MSC_ST_JOB_REQUIREMENT_OPS.ctl');
607 END IF;
608
609 IF p_job_op_resources IS NOT NULL THEN
610 GET_FILE_NAMES( pDataFileName => p_job_op_resources, pCtlFileName => 'MSC_ST_JOB_OP_RESOURCES.ctl');
611 END IF;
612
613 -- profiles
614
615 IF p_profiles IS NOT NULL THEN
616 GET_FILE_NAMES( pDataFileName => p_profiles, pCtlFileName => 'MSC_ST_APPS_INSTANCES.ctl');
617 END IF;
618
619 -- Calendar Assignments
620 IF p_calendar_assignments IS NOT NULL THEN
621 GET_FILE_NAMES( pDataFileName => p_calendar_assignments, pCtlFileName => 'MSC_ST_CALENDAR_ASSIGNMENTS.ctl');
622 END IF;
623
624 IF p_regions IS NOT NULL THEN
625 GET_FILE_NAMES( pDataFileName => p_regions, pCtlFileName => 'MSC_ST_REGIONS.ctl');
626 END IF;
627
628 IF p_zone_regions IS NOT NULL THEN
629 GET_FILE_NAMES( pDataFileName => p_zone_regions, pCtlFileName => 'MSC_ST_ZONE_REGIONS.ctl');
630 END IF;
631
632 IF p_region_locations IS NOT NULL THEN
633 GET_FILE_NAMES( pDataFileName => p_region_locations, pCtlFileName => 'MSC_ST_REGION_LOCATIONS.ctl');
634 END IF;
635
636 IF p_region_sites IS NOT NULL THEN
637 GET_FILE_NAMES( pDataFileName => p_region_sites, pCtlFileName => 'MSC_ST_REGION_SITES.ctl');
638 END IF;
639
640 IF p_iro_supplies IS NOT NULL THEN
641 GET_FILE_NAMES( pDataFileName => p_iro_supplies, pCtlFileName => 'MSC_ST_SUPPLIES_IRO.ctl');
642 END IF;
643
644 IF p_iro_demand IS NOT NULL THEN
645 GET_FILE_NAMES( pDataFileName => p_iro_demand, pCtlFileName => 'MSC_ST_DEMANDS_IRO_COMP.ctl');
646 END IF;
647
648 IF p_ero_supplies IS NOT NULL THEN
649 GET_FILE_NAMES( pDataFileName => p_ero_supplies, pCtlFileName => 'MSC_ST_SUPPLIES_ERO.ctl');
650 END IF;
651
652 IF p_ero_demand IS NOT NULL THEN
653 GET_FILE_NAMES( pDataFileName => p_ero_demand, pCtlFileName => 'MSC_ST_DEMANDS_ERO_COMP.ctl');
654 END IF;
655
656 v_request_id.EXTEND(v_task_pointer);
657
658 v_task_pointer:= 0;
659
660
661
662
663
664 -- ============ Lauch the Loaders here ===============
665
666 LOOP
667
668 IF active_loaders < p_total_worker_num THEN
669
670 EXIT WHEN is_request_status_running <> SYS_YES;
671
672 IF v_task_pointer < (v_ctl_file.LAST - 1) THEN
673
674 v_task_pointer:= v_task_pointer + 1;
675
676 lv_request_id:= LAUNCH_LOADER (ERRBUF => ERRBUF,
677 RETCODE => RETCODE);
678
679 IF lv_request_id <> -1 THEN
680 v_request_id(v_task_pointer):= lv_request_id;
681 END IF;
682
683 ELSIF active_loaders = 0 THEN
684
685 EXIT;
686
687 ELSE
688
689 select (SYSDATE- START_TIME) into lv_process_time from dual;
690
691 IF lv_process_time > p_timeout/1440.0 THEN Raise EX_PROCESS_TIME_OUT; END IF;
692
693 DBMS_LOCK.SLEEP( 5);
694
695 END IF;
696
697 ELSE
698 -- ============= Check the execution time ==============
699
700 select (SYSDATE- START_TIME) into lv_process_time from dual;
701
702 IF lv_process_time > p_timeout/1440.0 THEN Raise EX_PROCESS_TIME_OUT; END IF;
703
704 DBMS_LOCK.SLEEP( 5);
705
706 END IF;
707
708 END LOOP;
709
710 lv_check_point:= 3;
711
712 IF RETCODE= G_ERROR THEN RETURN; END IF;
713
714 EXCEPTION
715
716 WHEN EX_PROCESS_TIME_OUT THEN
717
718 ROLLBACK;
719
720 FND_MESSAGE.SET_NAME('MSC', 'MSC_TIMEOUT');
721 ERRBUF:= FND_MESSAGE.GET;
722 RETCODE:= G_ERROR;
723 LOG_MESSAGE( ERRBUF);
724
725 WHEN others THEN
726
727 ROLLBACK;
728
729 ERRBUF := SQLERRM;
730 RETCODE:= G_ERROR;
731 LOG_MESSAGE( ERRBUF);
732
733 END LAUNCH_MONITOR;
734
735 END MSC_CL_LOADERS;