[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_PROFILE_LOADERS
Source
1 PACKAGE BODY MSC_CL_PROFILE_LOADERS AS -- body
2 /* $Header: MSCPROFLDB.pls 115.0 2003/01/19 19:01:14 rawasthi noship $ */
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_completed (p_request_id NUMBER) RETURN NUMBER
84 IS
85
86 l_call_status boolean;
87 l_phase varchar2(80);
88 l_status varchar2(80);
89 l_dev_phase varchar2(80);
90 l_dev_status varchar2(80);
91 l_message varchar2(2048);
92 l_num number;
93 l_request_id number;
94
95 BEGIN
96
97 l_request_id := p_request_id;
98
99 LOOP
100 dbms_lock.sleep(30);
101 l_call_status:= FND_CONCURRENT.GET_REQUEST_STATUS
102 ( l_request_id,
103 NULL,
104 NULL,
105 l_phase,
106 l_status,
107 l_dev_phase,
108 l_dev_status,
109 l_message);
110
111 IF l_call_status=FALSE THEN
112 LOG_MESSAGE( l_message);
113 RETURN 0;
114 END IF;
115 exit when l_dev_phase='COMPLETE';
116 END LOOP;
117
118 IF
119 l_dev_phase='COMPLETE' and l_dev_status='NORMAL' then
120 RETURN 1;
121 ELSIF l_dev_phase='COMPLETE' and l_dev_status='ERROR' then
122 RETURN 2;
123 END IF;
124
125 END is_request_completed;
126
127
128 FUNCTION is_request_status_running RETURN NUMBER
129 IS
130 l_call_status boolean;
131 l_phase varchar2(80);
132 l_status varchar2(80);
133 l_dev_phase varchar2(80);
134 l_dev_status varchar2(80);
135 l_message varchar2(2048);
136
137 l_request_id NUMBER;
138
139 BEGIN
140
141 l_request_id:= FND_GLOBAL.CONC_REQUEST_ID;
142
143 l_call_status:= FND_CONCURRENT.GET_REQUEST_STATUS
144 ( l_request_id,
145 NULL,
146 NULL,
147 l_phase,
148 l_status,
149 l_dev_phase,
150 l_dev_status,
151 l_message);
152
153 IF l_call_status=FALSE THEN
154 LOG_MESSAGE( l_message);
155 RETURN SYS_NO;
156 END IF;
157
158 IF l_dev_phase='RUNNING' THEN
159 RETURN SYS_YES;
160 ELSE
161 RETURN SYS_NO;
162 END IF;
163
164 END is_request_status_running;
165
166 FUNCTION active_loaders RETURN NUMBER IS
167 l_call_status boolean;
168 l_phase varchar2(80);
169 l_status varchar2(80);
170 l_dev_phase varchar2(80);
171 l_dev_status varchar2(80);
172 l_message varchar2(2048);
173 l_request_id NUMBER;
174 l_active_loaders NUMBER:= 0 ;
175
176 BEGIN
177
178 FOR lc_i IN 1..(v_request_id.COUNT) LOOP
179
180 l_request_id:= v_request_id(lc_i);
181
182 l_call_status:= FND_CONCURRENT.GET_REQUEST_STATUS
183 ( l_request_id,
184 NULL,
185 NULL,
186 l_phase,
187 l_status,
188 l_dev_phase,
189 l_dev_status,
190 l_message);
191
192 IF l_call_status=FALSE THEN
193 LOG_MESSAGE( l_message);
194 END IF;
195
196 IF l_dev_phase IN ( 'PENDING','RUNNING') THEN
197 l_active_loaders:= l_active_loaders + 1;
198 END IF;
199
200 END LOOP;
201
202 RETURN l_active_loaders;
203
204 END active_loaders;
205
206 FUNCTION LAUNCH_LOADER ( ERRBUF OUT NOCOPY VARCHAR2,
207 RETCODE OUT NOCOPY NUMBER)
208 RETURN NUMBER IS
209
210 lv_request_id NUMBER;
211 lv_parameters VARCHAR2(2000):= '';
212
213 BEGIN
214
215 lv_request_id:= FND_REQUEST.SUBMIT_REQUEST(
216 'MSC',
217 'MSCSLD', /* loader program called */
218 NULL, -- description
219 NULL, -- start date
220 FALSE, -- TRUE,
221 v_ctl_file(v_task_pointer),
222 v_dat_file(v_task_pointer),
223 v_dis_file(v_task_pointer),
224 v_bad_file(v_task_pointer),
225 null,
226 '10000000' ); -- NUM_OF_ERRORS
227 COMMIT;
228
229 IF lv_request_id = 0 THEN
230 FND_MESSAGE.SET_NAME('MSC', 'MSC_PP_LAUNCH_LOADER_FAIL');
231 ERRBUF:= FND_MESSAGE.GET;
232 LOG_MESSAGE( ERRBUF);
233 RETCODE:= G_ERROR;
234 RETURN -1;
235 ELSE
236 FND_MESSAGE.SET_NAME('MSC', 'MSC_PP_LOADER_REQUEST_ID');
237 FND_MESSAGE.SET_TOKEN('REQUEST_ID', lv_request_id);
238 LOG_MESSAGE(FND_MESSAGE.GET);
239 END IF;
240
241 IF is_request_completed(lv_request_id)=2 THEN
242 LOG_MESSAGE('ATTENTION: If Loader Worker has completed with ERROR, then user has to delete the records from MSC_ST_PROFILES with process flag 1 for that particular preference set,correct the data file and load it again');
243 LOG_MESSAGE('The syntax of deleting from staging is:');
244 LOG_MESSAGE('delete from MSC_ST_PROFILES where preference_set_name=PREFERENCE_SET_NAME and process_flag=1');
245 ERRBUF := SQLERRM;
246 RETCODE := G_ERROR;
247 END IF;
248
249 RETURN lv_request_id;
250 EXCEPTION
251 WHEN OTHERS THEN
252 LOG_MESSAGE( SQLERRM);
253 RETURN -1;
254 END LAUNCH_LOADER;
255
256 -- ===============================================================
257
258 PROCEDURE LAUNCH_PROFILE_MON( ERRBUF OUT NOCOPY VARCHAR2,
259 RETCODE OUT NOCOPY NUMBER,
260 p_timeout IN NUMBER,
261 p_path_separator IN VARCHAR2 DEFAULT '/',
262 p_ctl_file_path IN VARCHAR2,
263 p_directory_path IN VARCHAR2,
264 p_total_worker_num IN NUMBER,
265 p_get_profile_value IN VARCHAR2 DEFAULT NULL)
266 IS
267
268 lc_i PLS_INTEGER;
269 lv_process_time NUMBER:= 0;
270 lv_check_point NUMBER:= 0;
271 lv_request_id NUMBER:= -1;
272 lv_start_time DATE;
273
274 lv_active_loaders NUMBER:=0;
275
276 EX_PROCESS_TIME_OUT EXCEPTION;
277
278 BEGIN
279 -- ===== Switch on debug based on MRP: Debug Profile
280
281 v_debug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
282
283 -- print the parameters coming in
284
285 IF v_debug THEN
286 LOG_MESSAGE('p_timeout: '||p_timeout);
287 LOG_MESSAGE('p_path_separator: '||p_path_separator);
288 LOG_MESSAGE('p_ctl_file_path: '||p_ctl_file_path);
289 LOG_MESSAGE('p_directory_path: '||p_directory_path);
290 LOG_MESSAGE('p_total_worker_num: '||p_total_worker_num);
291 LOG_MESSAGE('p_get_profile_value: '||p_get_profile_value);
292
293 END IF;
294
295 -- get the ctl file path. If last character is not path seperator add it
296
297 v_path_seperator:= p_path_separator;
298
299 v_ctl_file_path := p_ctl_file_path;
300
301 IF v_ctl_file_path IS NOT NULL THEN
302 IF SUBSTR(v_ctl_file_path,-1,1) = v_path_seperator then
303 v_ctl_file_path:= v_ctl_file_path;
304 ELSE
305 v_ctl_file_path:= v_ctl_file_path || v_path_seperator;
306 END IF;
307 END IF;
308
309 -- ===== Assign the data file directory path to a global variable ===========
310
311 -- If last character is not path seperator, add it. User may specify the path in the
312 -- file name itself. Hence, if path is null, do not add seperator
313
314 IF p_directory_path IS NOT NULL THEN
315 IF SUBSTR(p_directory_path,-1,1) = v_path_seperator then
316 v_dat_file_path:= p_directory_path;
317 ELSE
318 v_dat_file_path:= p_directory_path || v_path_seperator;
319 END IF;
320 END IF;
321
322 -- ===== create the Control, Data, Bad, Discard Files lists ==================
323
324 IF p_get_profile_value IS NOT NULL THEN
325 GET_FILE_NAMES( pDataFileName => p_get_profile_value, pCtlFileName => 'MSC_ST_PROFILE_VALUES.ctl');
326 END IF;
327
328
329 v_request_id.EXTEND(v_task_pointer);
330
331 v_task_pointer:= 0;
332
333
334
335
336
337 -- ============ Lauch the Loaders here ===============
338
339 LOOP
340
341 IF active_loaders < p_total_worker_num THEN
342
343 EXIT WHEN is_request_status_running <> SYS_YES;
344
345 IF v_task_pointer < (v_ctl_file.LAST - 1) THEN
346
347 v_task_pointer:= v_task_pointer + 1;
348
349 lv_request_id:= LAUNCH_LOADER (ERRBUF => ERRBUF,
350 RETCODE => RETCODE);
351
352 IF lv_request_id <> -1 THEN
353 v_request_id(v_task_pointer):= lv_request_id;
354 END IF;
355
356 ELSIF active_loaders = 0 THEN
357
358 EXIT;
359
360 ELSE
361
362 select (SYSDATE- START_TIME) into lv_process_time from dual;
363
364 IF lv_process_time > p_timeout/1440.0 THEN Raise EX_PROCESS_TIME_OUT; END IF;
365
366 DBMS_LOCK.SLEEP( 5);
367
368 END IF;
369
370 ELSE
371 -- ============= Check the execution time ==============
372
373 select (SYSDATE- START_TIME) into lv_process_time from dual;
374
375 IF lv_process_time > p_timeout/1440.0 THEN Raise EX_PROCESS_TIME_OUT; END IF;
376
377 DBMS_LOCK.SLEEP( 5);
378
379 END IF;
380
381 END LOOP;
382
383 lv_check_point:= 3;
384
385 IF RETCODE= G_ERROR THEN RETURN; END IF;
386
387 EXCEPTION
388
389 WHEN EX_PROCESS_TIME_OUT THEN
390
391 ROLLBACK;
392
393 FND_MESSAGE.SET_NAME('MSC', 'MSC_TIMEOUT');
394 ERRBUF:= FND_MESSAGE.GET;
395 RETCODE:= G_ERROR;
396 LOG_MESSAGE( ERRBUF);
397
398 WHEN others THEN
399
400 ROLLBACK;
401
402 ERRBUF := SQLERRM;
403 RETCODE:= G_ERROR;
404 LOG_MESSAGE( ERRBUF);
405
406 END LAUNCH_PROFILE_MON;
407
408 END MSC_CL_PROFILE_LOADERS;