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