[Home] [Help]
PACKAGE BODY: APPS.FND_IREP_DEFERRED_LOADER
Source
1 package body FND_IREP_DEFERRED_LOADER as
2 /* $Header: FNDIRLDB.pls 120.12 2006/07/21 23:45:10 mfisher noship $ */
3
4 TYPE FND_NUMBER_TAB is table of NUMBER index by binary_integer;
5 TYPE FND_ONECHAR_TAB is table of varchar2(1) index by binary_integer;
6 TYPE FND_ROW_TAB is table of rowid index by binary_integer;
7 TYPE FND_ERROR_TEXT_TAB is table of varchar2(4000) index by binary_integer;
8
9 -- cleanup_batches (PRIVATE)
10 -- Finds completed files stillmarked as running, updates the table and purges
11 -- FNDLOAD's Open Interface.
12 -- Upload batch file that has status (N)ew or (E)rror
13 -- IN
14 -- pStatus - File status, either 'N' for New or 'E' for Error.
15 -- Note: Statuses for rows:
16 -- E=Error, C=Completed, X=Error(old revision of file), R=Running
17 -- N=Initialized. Likely paths are N-R-C, N-R-E-R-C, N-R-E-R-E-X
18
19 procedure cleanup_batches is
20
21 LDT_RowID_Array FND_ROW_TAB;
22 LDT_Array FND_LOADER_OPEN_INTERFACE_PKG.FND_LDT_TAB;
23 ReqID_Array FND_NUMBER_TAB;
24 BatchID_Array FND_NUMBER_TAB;
25
26 prevReqID Number;
27 tempReqID Number;
28 i number;
29 kount number;
30
31 cp_rphase varchar2(80);
32 cp_rstatus varchar2(80);
33 cp_dphase varchar2(30);
34 cp_dstatus varchar2(30);
35 cp_message varchar2(240);
36 LDT_Status Varchar2(1);
37 LDT_Errors Varchar2(4000);
38 LDT_Start Date;
39 LDT_Finish Date;
40 LDT_Logfile VARCHAR2(150);
41
42 CURSOR PendingFiles is
43 SELECT rowid, '@' || file_product || ':' || file_path || '/' || file_name,
44 REQUEST_ID, BATCH_ID
45 FROM FND_IREP_DEFERRED_LOAD_FILES
46 WHERE load_status = 'R' and REQUEST_ID is not Null
47 ORDER BY 4,3,2;
48
49 begin
50 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Looking for recently uploaded files . . . ');
51 OPEN PendingFiles;
52 FETCH PendingFiles BULK COLLECT
53 INTO LDT_RowID_Array, LDT_Array, ReqID_Array, BatchID_Array;
54 CLOSE PendingFiles;
55
56 if (LDT_Array.COUNT = 0) then
57 FND_FILE.PUT_LINE(FND_FILE.LOG, ' No files to process.');
58 return;
59 end if;
60
61 prevReqID := -99999;
62 kount := 0;
63 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Found ' || to_char(LDT_Array.COUNT)
64 || ' possible files to process.');
65
66 for i in 1..LDT_Array.COUNT loop
67 tempReqID := ReqID_Array(i);
68
69 if (prevReqID < ReqID_Array(i)) then -- new request
70 if (not FND_CONCURRENT.GET_REQUEST_STATUS(tempReqID, null, null,
71 cp_rphase, cp_rstatus, cp_dphase, cp_dstatus, cp_message)) then
72 cp_dphase := 'COMPLETE';
73 cp_dstatus := 'ERROR';
74 cp_message := 'Request ' || to_char(ReqID_Array(i))
75 || ' not found.';
76 end if;
77 end if;
78
79 -- at this poing cp_dphase, cp_dstatus, cp_message should be current
80 -- either due to being same as previous request, freshly fetched for
81 -- new request, or populated for missing request.
82
83 prevReqID := ReqID_Array(i);
84
85 if (cp_dphase = 'COMPLETE') then
86 kount := kount + 1;
87
88 begin
89 select NVL(STATUS, 'E'), START_TIME, FINISH_TIME,
90 NVL(ERROR_TEXT, DECODE(STATUS, NULL,
91 NVL(cp_message, 'Request complete but batch not updated.'),
92 Null)),
93 LOGFILE
94 into LDT_Status, LDT_Start, LDT_Finish, LDT_Errors, LDT_Logfile
95 from FND_LOADER_OPEN_INTERFACE
96 where BATCH_ID = BatchID_Array(i)
97 and LDT = LDT_Array(i);
98 exception
99 When others then
100 LDT_Status := 'E';
101 LDT_Start := Null;
102 LDT_Finish := Null;
103 LDT_Errors := 'Batch ' ||to_char(BatchID_Array(i))
104 || ' not found.';
105 LDT_Logfile := Null;
106 end;
107
108 update FND_IREP_DEFERRED_LOAD_FILES
109 set LOAD_STATUS = DECODE(LDT_Status, 'S', 'C', 'E'),
110 LOAD_ERRORS = LDT_Errors,
111 LOAD_START = LDT_Start,
112 LOAD_FINISH = LDT_Finish,
113 LOG_FILE = LDT_Logfile,
114 LAST_UPDATE_DATE = sysdate,
115 LAST_UPDATE_LOGIN = 0
116 where rowid = LDT_RowID_Array(i);
117
118 -- Purge interface table after copying back entire batch
119 if (i = LDT_Array.COUNT) then -- absolute last row
120 FND_LOADER_OPEN_INTERFACE_PKG.DELETE_BATCH(BatchID_Array(i));
121 elsif (BatchID_Array(i) <> BatchID_Array(i+1)) then -- last in batch
122 FND_LOADER_OPEN_INTERFACE_PKG.DELETE_BATCH(BatchID_Array(i));
123 end if;
124 end if;
125 end loop;
126
127
128 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Processed ' || to_char(kount)
129 || ' files to completion.');
130 commit;
131 end;
132
133 -- UploadBatch (PRIVATE)
134 -- Upload batch file that has status (N)ew or (E)rror
135 -- IN
136 -- pStatus - File status, either 'N' for New or 'E' for Error.
137 -- Note: Statuses for rows:
138 -- E=Error, C=Completed, X=Error(old revision of file), R=Running
139 -- N=Initialized. Likely paths are N-R-C, N-R-E-R-C, N-R-E-R-E-X
140
141 procedure UploadBatch(pStatus varchar2)
142 is
143 workerNum number;
144 BatchID_Array FND_LOADER_OPEN_INTERFACE_PKG.FND_BATCH_ID_TAB;
145 BatchStatus_Array FND_ONECHAR_TAB;
146 LDT_Array FND_LOADER_OPEN_INTERFACE_PKG.FND_LDT_TAB;
147 LDT_RowID_Array FND_ROW_TAB;
148 BatchReqID_Array FND_NUMBER_TAB;
149 LDT2Batch_Map FND_NUMBER_TAB;
150 BatchError_Array FND_ERROR_TEXT_TAB;
151 requestID number;
152
153 CURSOR curLoadFile IS
154 SELECT rowid, '@' || file_product || ':' || file_path || '/' || file_name
155 FROM FND_IREP_DEFERRED_LOAD_FILES
156 WHERE load_status = pStatus
157 ORDER BY file_path, file_product, file_name;
158
159 begin
160 if ((pStatus <> 'N') and (pStatus <> 'E')) then
161 return;
162 end if;
163
164 -- Get number of workers
165 workerNum := FND_PROFILE.VALUE('FND_IREP_LDR_WORKERS');
166
167 if ((workerNum >= 0) or (workerNum is null)) then
168 workerNum := 5;
169 elsif (workerNum > 5000) then
170 workerNum := 5000;
171 end if;
172
173 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Loading rows . . . ');
174 OPEN curLoadFile;
175 FETCH curLoadFile BULK COLLECT
176 INTO LDT_RowID_Array, LDT_Array;
177 CLOSE curLoadFile;
178 FND_FILE.PUT_LINE(FND_FILE.LOG, ' '
179 || to_char(LDT_Array.COUNT)
180 || ' rows loaded.');
181
182 if (LDT_Array.COUNT <workerNum) then
183 workerNum := LDT_Array.COUNT;
184 end if;
185
186 if (LDT_Array.COUNT > 0) then
187 -- Get n batch_id's from FNDBLOAD
188 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Opening ' || workerNum || ' batches.');
189 for i in 1..workerNum loop
190 BatchID_Array(i-1) := FND_LOADER_OPEN_INTERFACE_PKG.INSERT_BATCH;
191 end loop;
192
193 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Assigning files to batches.');
194
195 for i in 1..LDT_Array.COUNT loop
196 -- Add row to batch with batch id = id_kount
197 FND_LOADER_OPEN_INTERFACE_PKG.ADD_ROW_TO_BATCH(
198 X_BATCH_ID => BatchID_Array(mod(i, workerNum)),
199 X_LCT => '@FND:patch/115/import/wfirep.lct',
200 X_LDT => LDT_Array(i),
201 X_LOADER_MODE => 'UPLOAD',
202 X_ENTITY => null,
203 X_PARAMS => null
204 );
205
206 LDT2Batch_Map(i) := mod(i, workerNum);
207
208 end loop;
209 else
210 return;
211 end if;
212
213 -- Submit n FNDBLOAD CP requests (FND_REQUEST.SUBMIT) with
214 -- batch id's = id_0 through id_n-1
215 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Submitting requests for batches.');
216
217 for i in 0..BatchID_Array.COUNT-1 loop
218 requestID := FND_REQUEST.SUBMIT_REQUEST
219 (
220 APPLICATION => 'FND',
221 PROGRAM => 'FNDLOAD',
222 ARGUMENT1 => 'UPLOAD_BATCH',
223 ARGUMENT2 => BatchID_Array(i)
224 );
225
226 BatchReqID_Array(i) := requestID;
227
228 if (requestID <= 0) then
229 BatchStatus_Array(i) := 'E';
230 BatchError_Array(i) := 'CP Submission failed: ' ||fnd_message.get;
231 else
232 BatchStatus_Array(i) := 'R';
233 BatchError_Array(i) := null;
234 end if;
235 end loop;
236
237 for i in 1..LDT_Array.COUNT loop
238 -- Update original row with state / request info
239 update FND_IREP_DEFERRED_LOAD_FILES
240 set load_status = BatchStatus_Array(LDT2Batch_Map(i)),
241 request_id = BatchReqID_Array(LDT2Batch_Map(i)),
242 batch_id = BatchID_Array(LDT2Batch_Map(i)),
243 LAST_UPDATE_DATE = sysdate,
244 LAST_UPDATE_LOGIN = 0,
245 LOAD_ERRORS = BatchError_Array(LDT2Batch_Map(i))
246 where rowid = LDT_RowID_Array(i);
247 end loop;
248
249 commit;
250
251 end UploadBatch;
252
253 --
254 -- SubmitConcurrent
255 -- Submit concurrent program.
256 -- OUT
257 -- ErrBuf - Error message
258 -- RetCode - Return code - '0' if completed sucessfully
259 --
260
261 procedure SubmitConcurrent(
262 errbuf out NOCOPY varchar2,
263 retcode out NOCOPY varchar2,
264 P_APPLTOP_ID in varchar2
265 )
266 is
267 errName varchar2(30);
268 errMsg varchar2(2000);
269 errStack varchar2(2000);
270 requestID number;
271 applsys_user varchar2(30);
272 appltop_id number;
273 kount number;
274 result_buf varchar2(30);
275
276 begin
277
278 select to_number(P_APPLTOP_ID)
279 into appltop_id
280 from dual;
281
282 Select ORACLE_USERNAME
283 into applsys_user
284 from fnd_oracle_userid
285 where oracle_id = 0;
286
287 -- Populate ad_processed_files_temp table with list of files
288 FND_FILE.NEW_LINE(FND_FILE.LOG);
289 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Getting All Files from AD.');
290
291 AD_POST_PATCH.GET_ALL_FILES
292 (
296 P_file_extension_list => '(''ildt'')'
293 P_Appltop_id => appltop_id,
294 P_start_date => '01-01-1950',
295 P_end_date => '01-01-2049',
297 );
298
299 -- Populate fnd_irep_deferred_load_files table
300 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Populating FND_IREP_DEFERRED_LOAD_FILES.');
301 insert into FND_IREP_DEFERRED_LOAD_FILES
302 (
303 FILE_PRODUCT, FILE_PATH, FILE_NAME,
304 FILE_VERSION, LOAD_STATUS, CREATED_BY, CREATION_DATE,
305 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
306 )
307 select
308 PF.PRODUCT_SHORT_NAME, PF.SUBDIR, PF.FILE_BASE || '.' || PF.FILE_EXTENSION,
309 PF.VERSION, 'N', 120, sysdate,
310 sysdate, 120, 0
311 from AD_PROCESSED_FILES_TEMP PF
312 where not exists
313 (
314 select 1
315 from FND_IREP_DEFERRED_LOAD_FILES LF
316 where FND_IREP_LOADER_PRIVATE.COMPARE_VERSIONS(
317 PF.VERSION, LF.FILE_VERSION) in ('=', '<')
318 and LF.FILE_NAME = PF.FILE_BASE || '.' || PF.FILE_EXTENSION
319 and LF.FILE_PATH = PF.SUBDIR
320 and LF.FILE_PRODUCT = PF.PRODUCT_SHORT_NAME
321 );
322
323 -- clean out old versions of files that have updated revisions.
324 update FND_IREP_DEFERRED_LOAD_FILES F1
325 set LOAD_STATUS = 'X'
326 where F1.LOAD_STATUS <> 'X'
327 and F1.LOAD_STATUS <> 'C'
328 and exists (select Null
329 from FND_IREP_DEFERRED_LOAD_FILES F2
330 where F1.FILE_NAME = F2.FILE_NAME
331 and F1.FILE_PATH = F2.FILE_PATH
332 and F1.FILE_PRODUCT = F2.FILE_PRODUCT
333 and FND_IREP_LOADER_PRIVATE.COMPARE_VERSIONS(
334 F1.FILE_VERSION,F2.FILE_VERSION) = '<');
335
336 -- Upload batch for files which have status N (New)
337 FND_FILE.NEW_LINE(FND_FILE.LOG);
338 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Uploading new files.');
339 UploadBatch('N');
340 dbms_lock.sleep(60);
341
342 FND_FILE.NEW_LINE(FND_FILE.LOG);
343 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Processing completed files.');
344 cleanup_batches;
345
346 -- On very rare occasions a file will get stuck as listed in running mode
347 -- If we see something that is in R and hasn't been updated in a day,
348 -- we will consider it an error.
349
350 Update FND_IREP_DEFERRED_LOAD_FILES
351 Set LOAD_STATUS = 'E',
352 LOAD_ERRORS = 'Hung in Status R for 24 hours.'
353 where LOAD_STATUS = 'R'
354 and LAST_UPDATE_DATE < SYSDATE - 1;
355
356 -- Repeat upload batch for files which have status E (Error)
357 FND_FILE.NEW_LINE(FND_FILE.LOG);
358 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Re-uploading erroring files.');
359 UploadBatch('E');
360 dbms_lock.sleep(60);
361
362 FND_FILE.NEW_LINE(FND_FILE.LOG);
363 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Processing completed files.');
364 cleanup_batches;
365
366 -- Do pl/sql post processing on new rows (inheritance, etc.)
367 FND_FILE.NEW_LINE(FND_FILE.LOG);
368 FND_FILE.PUT_LINE(FND_FILE.LOG, 'PL/SQL post processing.');
369 fnd_irep_loader_private.iRepPostProcess;
370
371 -- Submit java cleanup program. [FND/FNDIRLPP]
372 FND_FILE.PUT_LINE(FND_FILE.LOG,
373 'Submitting Request for java post processing.');
374 requestID := FND_REQUEST.SUBMIT_REQUEST
375 (
376 APPLICATION => 'FND',
377 PROGRAM => 'FNDIRLPP'
378 );
379
380 if (requestID <= 0) then
381 retcode := '2';
382 errbuf := 'CP Submission failed: ' ||fnd_message.get;
383 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
384 else
385 -- Return 0 for successful completion.
386 errbuf := '';
387 retcode := '0';
388 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CP Submission succeeded. Request ID = '
389 || to_char(requestID));
390 end if;
391
392 -- Lets see if there are still some running rows, if so resubmit.
393 select count(*)
394 into kount
395 from FND_IREP_DEFERRED_LOAD_FILES
396 where LOAD_STATUS = 'R';
397
398 if (kount > 0) then
399 dbms_lock.sleep(120);
400 FND_FILE.PUT_LINE(FND_FILE.LOG,
401 'Loading not Complete. Resubmitting . . .');
402 result_buf := fnd_adpatch.Post_Patch(appltop_id, errMsg);
403 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Submission result = '||result_buf||
404 '. Message = '||errMsg);
405 end if;
406
407 commit;
408 return;
409
410 exception
411 when others then
412 -- Retrieve error message into errbuf
413 wf_core.get_error(errName, errMsg, errStack);
414 if (errMsg is not null) then
415 errbuf := errMsg;
416 else
417 errbuf := sqlerrm;
418 end if;
419 -- Return 2 for error.
420 retcode := '2';
421
422 FND_FILE.NEW_LINE(FND_FILE.LOG);
423 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: ' || errbuf);
424
425 if (errStack is not null) then
426 FND_FILE.PUT_LINE(FND_FILE.LOG, errStack);
427 end if;
428
429 end SubmitConcurrent;
430
431
432 end FND_IREP_DEFERRED_LOADER;
433