1 PACKAGE BODY EAM_ASSET_NUM_IMPORT_PVT as
2 /* $Header: EAMVANIB.pls 120.2 2006/07/17 07:40:26 sshahid noship $*/
3
4 -- Start of comments
5 -- API name : Load_Asset_Number
6 -- Type : Private
7 -- Function :
8 -- Pre-reqs : None.
9 -- Parameters :
10 -- IN p_batch_id IN NUMBER Required,
11 -- p_purge_option IN VARCHAR2 Optional Default = 'N'
12 -- OUT ERRBUF OUT VARCHAR2,
13 -- RETCODE OUT VARCHAR2
14 --
15 -- Version Initial version 1.0 Anirban Dey
16 --
17 -- Notes : This public API imports asset numbers into
18 -- MTL_SERIAL_NUMBERS
19 --
20 -- End of comments
21
22 -- global variable to turn on/off debug logging.
23
24 g_pkg_name CONSTANT VARCHAR2(30):= 'EAM_ASSET_NUM_IMPORT_PVT';
25
26 PROCEDURE Load_Asset_Numbers
27 (ERRBUF OUT NOCOPY VARCHAR2,
28 RETCODE OUT NOCOPY VARCHAR2,
29 p_batch_id IN NUMBER,
30 p_purge_option IN VARCHAR2 := 'N'
31 ) IS
32
33 l_retcode Number;
34 CONC_STATUS BOOLEAN;
35
36 l_api_name CONSTANT VARCHAR2(30) := 'Load_Asset_Numbers';
37
38 l_module varchar2(200);
39 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
40 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
41 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
42 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
43
44
45 BEGIN
46 if(l_ulog) then
47 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
48 end if;
49 l_retcode := import_asset_numbers(p_batch_id, p_purge_option);
50
51 if l_retcode = 1 then
52
53 IF (l_slog) THEN
54 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Completed Successfully.');
55 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,Current_Error_Code);
56 END IF;
57
58 RETCODE := 'Success';
59
60 while FND_CONCURRENT.CHILDREN_DONE(Interval => 20, Max_Wait => 120) = FALSE loop
61 fnd_file.put_line(FND_FILE.LOG, 'Waiting for all the workers to complete.');
62 end loop;
63
64 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
65 elsif l_retcode = 3 then
66
67 IF (l_slog) THEN
68 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Completed with Warning.');
69 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,Current_Error_Code);
70 END IF;
71
72 RETCODE := 'Warning';
73 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',Current_Error_Code);
74 else
75
76 IF (l_slog) THEN
77 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Completed with Error.');
78 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,Current_Error_Code);
79 END IF;
80
81 RETCODE := 'Error';
82 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
83 end if;
84
85 END Load_Asset_Numbers;
86
87 PROCEDURE Launch_Worker
88 (
89 p_group_id NUMBER,
90 p_batch_id NUMBER,
91 p_purge_option VARCHAR2,
92 p_count NUMBER
93 ) IS
94
95 l_request_id NUMBER := 0;
96 l_api_name CONSTANT VARCHAR2(30) := 'Launch_Worker';
97
98 l_module varchar2(200);
99 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
100 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
101 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
102 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
103
104 BEGIN
105 if(l_ulog) then
106 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
107 end if;
108
109 IF (l_slog) THEN
110 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Submitting Import Worker '||p_group_id
111 || ' to process ' || p_count || ' assets');
112 END IF;
113
114 --have to commit here so that worker process sees the changes
115 COMMIT;
116 l_request_id := FND_REQUEST.submit_request(
117 'EAM',
118 'EAMANIMW',
119 NULL,
120 NULL,
121 FALSE,
122 p_group_id,
123 p_purge_option
124 );
125
126 IF (l_request_id = 0 OR l_request_id IS NULL) then
127 -- failed to launch the process
128 UPDATE mtl_eam_asset_num_interface meani
129 SET meani.error_code = 9999,
130 meani.process_flag = 'E',
131 meani.error_message = 'Failed to submit worker '||p_group_id
132 WHERE meani.process_flag = 'P'
133 AND meani.batch_id = p_batch_id
134 AND meani.interface_group_id = p_group_id;
135 COMMIT;
136
137
138 IF (l_slog) THEN
139 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Failed to submit worker '||p_group_id);
140 END IF;
141
142
143 RAISE fnd_api.g_exc_error;
144 ELSE
145
146 IF (l_slog) THEN
147 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Request id '|| l_request_id
148 || ' for Import Worker '||p_group_id||' successfully submitted');
149 END IF;
150
151 END IF;
152
153 IF (l_slog) THEN
154 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'');
155 END IF;
156
157 END Launch_Worker;
158
159 FUNCTION Import_Asset_Numbers
160 (
161 p_batch_id IN NUMBER,
162 p_purge_option IN VARCHAR2 := 'N'
163 ) RETURN Number IS
164
165 l_max_rows_to_process NUMBER := 500;
166 l_counter NUMBER := 0;
167 l_num_assets NUMBER := 0;
168 l_num_assets_to_workers NUMBER := 0;
169 l_num_workers NUMBER := 0;
170 error_number NUMBER := NULL;
171 error_message VARCHAR2(2000) := NULL;
172 error_counter NUMBER := 0;
173 curr_error VARCHAR2(9) := 'APP-00000';
174 l_group_id NUMBER := 0;
175
176 l_success NUMBER := 1;
177
178 l_api_name CONSTANT VARCHAR2(30) := 'Import_Asset_Numbers';
179
180 l_module varchar2(200);
181 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
182 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
183 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
184 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
185 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
186
187 CURSOR asset_rows_cur IS
188 SELECT meani.current_organization_id,
189 meani.inventory_item_id,
190 meani.serial_number,
191 count(*) as total
192 FROM MTL_EAM_ASSET_NUM_INTERFACE meani
193 WHERE meani.batch_id = p_batch_id
194 AND meani.interface_group_id IS NULL
195 AND meani.process_flag = 'P'
196 AND meani.error_code IS NULL
197 AND meani.error_message IS NULL
198 GROUP BY
199 meani.current_organization_id,
200 meani.inventory_item_id,
201 meani.serial_number;
202
203 -- Cursor for picking out invalid Scope in meani
204 CURSOR invalid_scope_asset_cur IS
205 SELECT meani.interface_header_id
206 FROM MTL_EAM_ASSET_NUM_INTERFACE meani
207 WHERE meani.batch_id = p_batch_id
208 AND meani.import_scope NOT IN (0,1,2)
209 AND meani.process_flag = 'P'
210 FOR UPDATE;
211
212 invalid_scope_asset_rec invalid_scope_asset_cur%ROWTYPE;
213
214 -- Cursor for picking out invalid Mode in meani
215 CURSOR invalid_mode_asset_cur IS
216 SELECT meani.interface_header_id
217 FROM MTL_EAM_ASSET_NUM_INTERFACE meani
218 WHERE meani.batch_id = p_batch_id
219 AND meani.import_mode NOT IN (0,1)
220 AND meani.process_flag = 'P'
221 FOR UPDATE;
222 invalid_mode_asset_rec invalid_mode_asset_cur%ROWTYPE;
223
224 -- Cursor for picking out NULL organization in meani
225 CURSOR null_org_asset_cur IS
226 SELECT meani.interface_header_id
227 FROM MTL_EAM_ASSET_NUM_INTERFACE meani
228 WHERE batch_id = p_batch_id
229 AND (current_organization_id is null
230 OR organization_code is null)
231 AND process_flag = 'P'
232 FOR UPDATE;
233
234 null_org_asset_rec null_org_asset_cur%ROWTYPE;
235
236
237 BEGIN
238
239 if(l_ulog) then
240 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
241 end if;
242
243 IF (l_slog) THEN
244 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Batch Id = '||p_batch_id);
245 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Purge Option = '||p_purge_option);
246 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Max Rows to process = '||l_max_rows_to_process);
247 END IF;
248
249
250
251 -- Validate Scope Values:
252 -- 0: Both Asset and Attributes
253 -- 1: Asset Only
254 -- 2: Attributes Only
255
256
257 IF (l_slog) THEN
258 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Validating Scope Values.');
259 END IF;
260
261
262 -- Use Cursor to pick out rows with invalid Scope to synchronize the conditions
263 -- for updating the row in meani and the corresponding rows in meavi
264 /* ==
265 UPDATE mtl_eam_asset_num_interface meani
266 SET meani.process_flag = 'E',
267 meani.error_code = 9999,
268 meani.error_message = 'Incorrect Scope Value'
269 WHERE meani.batch_id = p_batch_id
270 AND meani.import_scope NOT IN (0,1,2)
271 AND meani.process_flag = 'P';
272 === */
273 OPEN invalid_scope_asset_cur;
274 LOOP
275 FETCH invalid_scope_asset_cur INTO invalid_scope_asset_rec;
276 IF invalid_scope_asset_cur%NOTFOUND
277 THEN
278 EXIT;
279 ELSE
280 UPDATE mtl_eam_asset_num_interface meani
281 SET meani.process_flag = 'E',
282 meani.error_code = 9999,
283 meani.error_message = 'Incorrect Scope Value'
284 WHERE CURRENT OF invalid_scope_asset_cur;
285
286 -- 2001-12-28: chrng: To fix bug 2162520
287 -- Flag corresponding rows in meavi as Error as well.
288 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
289 SET meavi.error_number = 9999,
290 meavi.process_status = 'E',
291 meavi.error_message = 'Corresponding row in MTL_EAM_ASSET_NUM_INTERFACE has invalid Scope value'
292 WHERE meavi.process_status = 'P'
293 AND meavi.interface_header_id = invalid_scope_asset_rec.interface_header_id;
294 END IF;
295 END LOOP;
296 CLOSE invalid_scope_asset_cur;
297
298 -- Validate Mode Values:
299 -- 0: Create
300 -- 1: Update
301
302
303 IF (l_slog) THEN
304 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Validating Mode Values.');
305 END IF;
306
307
308 -- Use Cursor to pick out rows with invalid Mode to synchronize the conditions
309 -- for updating the row in meani and the corresponding rows in meavi
310 /* ==
311 UPDATE mtl_eam_asset_num_interface meani
312 SET meani.process_flag = 'E',
313 meani.error_code = 9999,
314 meani.error_message = 'Incorrect Mode Value'
315 WHERE meani.batch_id = p_batch_id
316 AND meani.import_mode NOT IN (0,1)
317 AND meani.process_flag = 'P';
318 == */
319
320 OPEN invalid_mode_asset_cur;
321 LOOP
322 FETCH invalid_mode_asset_cur INTO invalid_mode_asset_rec;
323 IF invalid_mode_asset_cur%NOTFOUND
324 THEN
325 EXIT;
326 ELSE
327 UPDATE mtl_eam_asset_num_interface meani
328 SET meani.process_flag = 'E',
329 meani.error_code = 9999,
330 meani.error_message = 'Incorrect Mode Value'
331 WHERE CURRENT OF invalid_mode_asset_cur;
332
333 -- Flag corresponding rows in meavi as Error as well.
334 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
335 SET meavi.error_number = 9999,
336 meavi.process_status = 'E',
337 meavi.error_message = 'Corresponding row in MTL_EAM_ASSET_NUM_INTERFACE has invalid Mode value'
338 WHERE meavi.process_status = 'P'
339 AND meavi.interface_header_id = invalid_mode_asset_rec.interface_header_id;
340 END IF;
341 END LOOP;
342 CLOSE invalid_mode_asset_cur;
343
344
345 -- Validate Organization Code:
346 -- If both organization_code and organization_id is provided, organization_id will be used.
347
348 IF (l_slog) THEN
349 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Validating Organization Code.');
350 END IF;
351
352
353 UPDATE mtl_eam_asset_num_interface meani
354 SET organization_code= (select organization_code from mtl_parameters
355 where organization_id = meani.current_organization_id
356 and maint_organization_id is NOT NULL)
357 WHERE batch_id = p_batch_id
358 AND current_organization_id is not null
359 AND process_flag = 'P';
360
361 UPDATE mtl_eam_asset_num_interface meani
362 SET current_organization_id= (select organization_id from mtl_parameters
363 where organization_code = meani.organization_code
364 and maint_organization_id is NOT NULL)
365 WHERE batch_id = p_batch_id
366 AND current_organization_id is null
367 AND organization_code is not null
368 AND process_flag = 'P';
369
370 -- Use Cursor to pick out rows with NULL org to synchronize the conditions
371 -- for updating the row in meani and the corresponding rows in meavi
372 /* ==
373 UPDATE mtl_eam_asset_num_interface
374 SET process_flag = 'E' ,
375 error_code = 9999,
376 error_message = 'Invalid Organization. Check that it is NOT NULL, EXISTS and is EAM ENABLED'
377 WHERE batch_id = p_batch_id
378 AND (current_organization_id is null
379 OR organization_code is null)
380 AND process_flag = 'P';
381 COMMIT;
382 == */
383
384 OPEN null_org_asset_cur;
385 LOOP
386 FETCH null_org_asset_cur INTO null_org_asset_rec;
387 IF null_org_asset_cur%NOTFOUND
388 THEN
389 EXIT;
390 ELSE
391 UPDATE mtl_eam_asset_num_interface
392 SET process_flag = 'E' ,
393 error_code = 9999,
394 error_message = 'Invalid Organization. Check that it is NOT NULL, EXISTS and is EAM ENABLED'
395 WHERE CURRENT OF null_org_asset_cur;
396
397 -- Flag corresponding rows in meavi as Error as well.
398 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
399 SET meavi.error_number = 9999,
400 meavi.process_status = 'E',
404 END IF;
401 meavi.error_message = 'Corresponding row in MTL_EAM_ASSET_NUM_INTERFACE has invalid organzation'
402 WHERE meavi.process_status = 'P'
403 AND meavi.interface_header_id = null_org_asset_rec.interface_header_id;
405 END LOOP;
406 CLOSE null_org_asset_cur;
407
408
409 -- ===== Start Processing ======
410
411 IF (l_slog) THEN
412 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Start record pre-processing. Time now is ' ||
413 to_char(sysdate, 'Month DD, YYYY HH24:MI:SS'));
414 END IF;
415
416
417 FOR asset IN asset_rows_cur
418 LOOP
419 --get a group id for worker
420 if (l_counter = 0) then
421 SELECT MTL_EAM_ASSET_NUM_INTERFACE_S.nextval
422 INTO l_group_id
423 FROM dual;
424 end if;
425 --have to update after each record ...
426 UPDATE mtl_eam_asset_num_interface meani
427 SET meani.interface_group_id = l_group_id,
428 meani.process_flag = 'R'
429 WHERE meani.process_flag = 'P'
430 AND meani.current_organization_id = asset.current_organization_id
431 AND meani.inventory_item_id = asset.inventory_item_id
432 AND meani.serial_number = asset.serial_number
433 AND meani.batch_id = p_batch_id
434 AND meani.interface_group_id IS NULL;
435
436 -- do not commit here, commit after each row is performance degrading
437 -- commit just before launching worker
438
439 l_counter := l_counter + asset.total;
440 l_num_assets := l_num_assets + asset.total;
441
442 IF (l_counter > l_max_rows_to_process) THEN
443 Launch_Worker(l_group_id, p_batch_id, p_purge_option, l_counter);
444 l_num_assets_to_workers := l_num_assets_to_workers + l_counter;
445 l_counter := 0;
446 l_num_workers := l_num_workers + 1;
447 END IF;
448 END LOOP;
449
450 if (l_num_assets > l_num_assets_to_workers) then -- we still have more to process
451 Launch_Worker(l_group_id, p_batch_id, p_purge_option, l_counter);
452 l_num_assets_to_workers := l_num_assets_to_workers + l_counter;
453 l_num_workers := l_num_workers + 1;
454 end if;
455
456 IF (l_slog) THEN
457 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Stop record pre-processing. Time now is ' ||
458 to_char(sysdate, 'Month DD, YYYY HH24:MI:SS'));
459 END IF;
460
461 IF (l_slog) THEN
462 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, l_num_workers ||' Import Workers are processing '
463 ||l_num_assets_to_workers || ' Assets');
464 END IF;
465
466 if not (l_num_assets_to_workers = l_num_assets) then
467
468 IF (l_slog) THEN
469 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'WARNING: There is a discrepancy. Total Assets ('
470 || l_num_assets || ') does not match total given to workers ('
471 || l_num_assets_to_workers || ')');
472 END IF;
473
474 end if;
475
476 IF (l_slog) THEN
477 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'');
478 END IF;
479
480
481 COMMIT;
482 Return l_success;
483
484 EXCEPTION
485
486 WHEN Error THEN
487
488 l_success := 3;
489 error_counter := error_counter + 1;
490 error_number := SQLCODE;
491 error_message := SUBSTR(SQLERRM, 1, 512);
492
493 IF invalid_scope_asset_cur%ISOPEN
494 THEN
495 CLOSE invalid_scope_asset_cur;
496 END IF;
497 IF invalid_mode_asset_cur%ISOPEN
498 THEN
499 CLOSE invalid_mode_asset_cur;
500 END IF;
501 IF null_org_asset_cur%ISOPEN
502 THEN
503 CLOSE null_org_asset_cur;
504 END IF;
505
506 IF (l_exlog) THEN
507 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,error_number);
508 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,error_message);
509 END IF;
510
511 COMMIT;
512 Return l_success;
513
514 WHEN OTHERS THEN
515
516 l_success := 2;
517 error_counter := error_counter + 1;
518 error_number := SQLCODE;
519 error_message := SUBSTR(SQLERRM, 1, 512);
520
521 IF invalid_scope_asset_cur%ISOPEN
522 THEN
523 CLOSE invalid_scope_asset_cur;
524 END IF;
525 IF invalid_mode_asset_cur%ISOPEN
526 THEN
527 CLOSE invalid_mode_asset_cur;
528 END IF;
529 IF null_org_asset_cur%ISOPEN
530 THEN
531 CLOSE null_org_asset_cur;
532 END IF;
533
534 IF (l_exlog) THEN
535 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,error_number);
536 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,error_message);
537 END IF;
538
539 COMMIT;
540 Return l_success;
541
542 END Import_Asset_Numbers;
543
544 END EAM_ASSET_NUM_IMPORT_PVT;
[Home] [Help]