[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSET_NUM_IMPORT_PUB
Source
1 PACKAGE BODY EAM_ASSET_NUM_IMPORT_PUB as
2 /* $Header: EAMPANIB.pls 120.12.12020000.2 2012/07/05 10:37:23 vpasupur ship $ */
3 -- Start of comments
4 -- API name : import_genealogy
5 -- Type : Public
6 -- Function :
7 -- Pre-reqs : None.
8 -- FILENAME : EAMPANIB.pls
9 -- DESCRIPTION
10 -- PL/SQL body for package EAM_ASSET_NUM_IMPORT_PUB. This public API imports
11 -- assets and their extensible attributes into MTL_SERIAL_NUMBERS and
12 -- MTL_EAM_ASSET_ATTR_VALUES. The import process is controlled by two
13 -- fields: SCOPE and MODE. These fields exist at the row level.
14 -- SCOPE implies what you want to import:
15 -- 0: Import both ASSETS and their EXTENSIBLE ATTRIBUTES
16 -- 1: Import only ASSETS
17 -- 2: Import only EXTENSIBLE ATTRIBUTES
18 -- MODE implies whether to create new records or update existing ones:
19 -- 0: CREATE new records
20 -- 1: UPDATE existing records
21 -- Further since this is the worker API for importing assets, the parameter
22 -- p_interface_group_id controls which records the worker will process. The
23 -- parameter p_purge_option specifies whether to delete successfully imported
24 -- records from the interface table after the worker completes.
25 -- HISTORY
26 -- 02-Aug-01 Created, Deepak Gupta, Anju Gupta
27 -- 07-Aug-01 Deepak Gupta: Dropped the dependent asset flag
28 -- 02-Jan-02 Chris Ng: Fixed bug 2167988
29 -- 08-Jan-02 Chris Ng: Fixed bug 2175340
30 -- 11-Jan-02 Chris Ng: Fixed bug 2180770.
31 -- 15-Jan-02 Chris Ng: Fixed bug 2184016.
32 -- 15-Jan-02 Chris Ng: Fixed bugs 2184517, 2184498, 2184448, 2184533,
33 -- 2184906.
34 -- 16-Jan-02 Chris Ng: Fixed bug 2185999.
35 -- 13-Mar-03 Himal Karmacharya Fixed bug 2850128
36 -- End of comments
37
38
39 g_pkg_name CONSTANT VARCHAR2(30):= 'EAM_ASSET_NUM_IMPORT_PUB';
40
41
42
43 --Converts boolean to varchar2
44
45 function bool2char(
46 p_bool BOOLEAN
47 ) return varchar2 is
48 begin
49 if p_bool then
50 return 'TRUE';
51 elsif (not p_bool) then
52 return 'FALSE';
53 else
54 return 'NULL';
55 end if;
56 end bool2char;
57
58 -- checks whether this id (a number only field) is to be skipped (in either mode)
59 function skip(
60 p_id NUMBER,
61 p_import_mode NUMBER
62 ) return boolean is
63 begin
64 if (p_import_mode = 0) then
65 return (p_id is null);
66 else --it must be update mode (1)
67 return (p_id = FND_API.G_MISS_NUM);
68 end if;
69 end skip;
70
71 -- checks whether this code (a char only field) is to be skipped (in either mode)
72 function skip(
73 p_code VARCHAR2,
74 p_import_mode NUMBER
75 ) return boolean is
76 begin
77 if (p_import_mode = 0) then
78 return (p_code is null);
79 else --it must be update mode (1)
80 return (p_code = FND_API.G_MISS_CHAR);
81 end if;
82 end skip;
83
84 -- checks whether this id/code combination field is to be skipped (in update mode)
85 function skip (
86 p_id IN OUT NOCOPY NUMBER,
87 p_code VARCHAR2,
88 p_import_mode NUMBER
89 ) return boolean is
90 l_skip_update BOOLEAN := false;
91 begin
92 if (p_import_mode = 0) then
93 l_skip_update := ((p_id is null) and (p_code is null));
94 else --it must be update mode (1)
95 l_skip_update := (((p_id is not null) and (p_id = FND_API.G_MISS_NUM)) OR
96 ((p_id is null) and (p_code is not null) and (p_code = FND_API.G_MISS_CHAR)));
97 --if id null, set to g_miss_num so that update (looks only at ids) works
98 if (l_skip_update) then
99 p_id := FND_API.G_MISS_NUM;
100 end if;
101 end if;
102 return l_skip_update;
103 end skip;
104
105 -- Updates the current table row with the error message and error code
106 procedure update_row_error (
107 p_error_code NUMBER,
108 p_error_message VARCHAR2,
109 p_asset_rec MTL_EAM_ASSET_NUM_INTERFACE%ROWTYPE
110 ) is
111 begin
112 UPDATE MTL_EAM_ASSET_NUM_INTERFACE SET
113 process_flag = 'E',
114 error_code = p_error_code,
115 error_message = p_error_message
116 WHERE interface_header_id = p_asset_rec.interface_header_id;
117
118 -- 2002-01-02: chrng: To fix bug 2167988, also flag the corresponding
119 -- rows in Asset Attribute interface table as error
120 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
121 SET meavi.error_number = 9999,
122 meavi.process_status = 'E',
123 meavi.error_message = 'Import of corresponding row in MTL_EAM_ASSET_NUM_INTERFACE failed'
124 WHERE meavi.interface_header_id = p_asset_rec.interface_header_id
125 AND meavi.process_status = 'P';
126
127 end update_row_error;
128
129 -- Bug # 3601150
130 -- Updates the remaining row in the batch with status as pending
131 procedure update_remaining_row_status (p_batch_id NUMBER) is
132 begin
133 UPDATE MTL_EAM_ASSET_NUM_INTERFACE
134 SET process_flag = 'P',
135 interface_group_id = NULL
136 WHERE batch_id = p_batch_id
137 AND process_flag = 'R';
138 end update_remaining_row_status;
139
140 -- Updates the current table row with the calculated values for derived
141 -- fields such as code from value, value from code, etc.
142 procedure update_row_calculated_values (
143 p_asset_rec MTL_EAM_ASSET_NUM_INTERFACE%ROWTYPE
144 ) is
145
146 l_api_name constant varchar2(30) := 'update_row_calculated_values';
147
148 l_module varchar2(200);
149 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
150 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
151 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
152 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
153
154 begin
155
156 if (l_ulog) then
157 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
158 end if;
159
160 if (l_slog) then
161 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
162 'Updating interface table with calculated values');
163 end if;
164
165 update mtl_eam_asset_num_interface set
166 owning_department_code = p_asset_rec.owning_department_code,
167 owning_department_id = p_asset_rec.owning_department_id,
168 asset_criticality_code = p_asset_rec.asset_criticality_code,
169 asset_criticality_id = p_asset_rec.asset_criticality_id,
170 fa_asset_number = p_asset_rec.fa_asset_number,
171 fa_asset_id = p_asset_rec.fa_asset_id,
172 location_codes = p_asset_rec.location_codes,
173 eam_location_id = p_asset_rec.eam_location_id,
174 current_status = p_asset_rec.current_status,
175 prod_organization_id = p_asset_rec.prod_organization_id,
176 prod_organization_code = p_asset_rec.prod_organization_code
177 where interface_header_id = p_asset_rec.interface_header_id;
178
179 --Start 8579859
180 update mtl_eam_asset_num_interface set
181 instance_number = (select instance_number from csi_item_instances
182 where serial_number = p_asset_rec.serial_number and
183 last_vld_organization_id =p_asset_rec.CURRENT_ORGANIZATION_ID and
184 inventory_item_id =p_asset_rec.inventory_item_id)
185 where interface_header_id = p_asset_rec.interface_header_id;
186
187 -- End 8579859
188
189 end update_row_calculated_values;
190 -- procedure to insert or update genealogy for PN locations
191 -- should only be called if pn_location_id is not null
192
193 procedure pn_genealogy_change (
194 p_pn_location_id in number,
195 p_serial_number in varchar2,
196 p_inventory_item_id in number,
197 p_current_organization_id in number,
198 p_start_date_active in date,
199 p_end_date_active in date,
200 p_parent_location_id in number,
201 p_parent_in_eam in varchar2,
202 p_parent_serial_number in varchar2,
203 p_parent_inventory_item_id in number,
204 p_import_mode in number,
205 x_return_status out NOCOPY varchar2,
206 x_msg_count out NOCOPY number,
207 x_msg_data out NOCOPY varchar2
208 ) is
209
210 l_pn_installed varchar2(1) ;
211 l_gen_object_id number;
212 l_pn_gen_mode number;
213 l_msn_status number;
214 l_end_date_active date;
215 l_hr_exists varchar2(1);
216 l_parent_object_id number;
217 l_return_status varchar2(1);
218 l_msg_data varchar2(2000);
219 l_msg_count number;
220
221 l_api_name CONSTANT VARCHAR2(30) := 'pn_genealogy_change';
222
223 l_module varchar2(200);
224 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
225 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
226 l_eLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
227 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
228 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
229
230 begin
231 if (l_ulog) then
232 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
233 end if;
234
235 l_pn_installed := 'N';
236 l_return_status := 'S';
237
238 SAVEPOINT pn_eam_genealogy;
239
240 begin
241 select status into l_pn_installed
242 from fnd_product_installations
243 where application_id = 240;
244 exception
245 when no_data_found then
246 l_pn_installed := 'N';
247 end;
248
249 if l_pn_installed = 'I' then
250
251 if p_end_date_active >= fnd_date.canonical_to_date('4712/12/31') then
252 l_end_date_active := null;
253 else
254 l_end_date_active := p_end_date_active;
255 end if;
256
257 if p_parent_location_id is not null then
258
259 if p_parent_in_eam = 'Y' then
260
261 SELECT current_status, gen_object_id
262 INTO l_msn_status, l_gen_object_id
263 FROM mtl_serial_numbers
264 WHERE serial_number = p_serial_number
265 and current_organization_id = p_current_organization_id
266 and inventory_item_id = p_inventory_item_id;
267
268 SELECT gen_object_id INTO l_parent_object_id
269 FROM mtl_serial_numbers
270 WHERE serial_number = p_parent_serial_number
271 and inventory_item_id = p_parent_inventory_item_id;
272
273
274 if p_import_mode = 0 then
275 l_pn_gen_mode := 0;
276 else
277 l_hr_exists := 'N';
278
279 begin
280 select 'Y' into l_hr_exists from dual
281 where exists
282 (select * from mtl_object_genealogy
283 where object_id = l_gen_object_id);
284 exception when no_data_found then
285 l_hr_exists := 'N';
286 end;
287
288 if l_hr_exists <> 'Y' then
289 l_pn_gen_mode := 0;
290 else
291 l_pn_gen_mode := 0;
292
293 declare
294 CURSOR genealogy_entry_cur IS
295 SELECT mog.start_date_active start_date_active,
296 mog.end_date_active
297 end_date_active, msn.serial_number
298 parent_serial_number
299 FROM mtl_object_genealogy mog, mtl_serial_numbers msn
300 WHERE mog.object_id = l_gen_object_id
301 and msn.gen_object_id = mog.parent_object_id
302 and mog.genealogy_type = 5;
303
304 begin
305
306
307 FOR i IN genealogy_entry_cur LOOP
308 IF i.end_date_active IS NOT NULL THEN
309 IF l_end_date_active IS NOT NULL THEN
310
311 if ((p_start_date_active = i.start_date_active)
312 and (l_end_date_active = i.end_date_active)) then
313 l_pn_gen_mode := 2;
314 elsif ((p_start_date_active <= i.start_date_active)
315 AND (l_end_date_active >= i.start_date_active))
316 OR ((p_start_date_active >= i.start_date_active)
317 AND (l_end_date_active <= i.end_date_active))
318 OR ((p_start_date_active <= i.start_date_active)
319 AND (l_end_date_active >= i.end_date_active))
320
321 OR ((p_start_date_active <= i.end_date_active)
322 AND (l_end_date_active >= i.end_date_active)) THEN
323
324 inv_genealogy_pub.delete_eam_row(
325 p_api_version=>1.0,
326 p_object_id => l_gen_object_id,
327 p_start_date_active=>i.start_date_active,
328 p_end_date_active=>i.end_date_active,
329 x_return_status => l_return_status,
330 x_msg_count => l_msg_count,
331 x_msg_data => l_msg_data);
332 l_pn_gen_mode := 0;
333 END IF;
334 ELSE
335 IF (p_start_date_active <= i.end_date_active) THEN
336 inv_genealogy_pub.delete_eam_row(
337 p_api_version=>1.0,
338 p_object_id => l_gen_object_id,
339 p_start_date_active=>i.start_date_active,
340 p_end_date_active=>i.end_date_active,
341 x_return_status => l_return_status,
342 x_msg_count => l_msg_count,
343 x_msg_data => l_msg_data);
344
345 l_pn_gen_mode := 0;
346 END IF;
347 END IF;
348 ELSE
349 IF l_end_date_active IS NULL THEN
350 if p_start_date_active = i.start_date_active then
351 l_pn_gen_mode := 2;
352 else
353 inv_genealogy_pub.delete_eam_row(
354 p_api_version=>1.0,
355 p_object_id => l_gen_object_id,
356 p_start_date_active=>i.start_date_active,
357 p_end_date_active=>i.end_date_active,
358 x_return_status => l_return_status,
359 x_msg_count => l_msg_count,
360 x_msg_data => l_msg_data);
361
362 l_pn_gen_mode := 0;
363 END IF;
364
365 ELSE
366 IF (p_start_date_active = i.start_date_active) THEN
367 l_pn_gen_mode := 1;
368 ELSIF (p_start_date_active >= i.start_date_active)
369 OR (l_end_date_active >= i.start_date_active) THEN
370 inv_genealogy_pub.delete_eam_row(
371 p_api_version=>1.0,
372 p_object_id => l_gen_object_id,
373 p_start_date_active=>i.start_date_active,
374 p_end_date_active=>i.end_date_active,
375 x_return_status => l_return_status,
376 x_msg_count => l_msg_count,
377 x_msg_data => l_msg_data);
378
379 l_pn_gen_mode := 0;
380 END IF;
381 END IF;
382 END IF;
383 END LOOP;
384 end;
385 END IF;
386 END IF;
387
388 if (l_pn_gen_mode = 0) then
389 inv_genealogy_pub.insert_genealogy(
390 p_api_version => 1.0,
391 p_object_type => 2,
392 p_parent_object_type => 2,
393 p_object_number => p_serial_number,
394 p_inventory_item_id => p_inventory_item_id,
395 p_org_id => p_current_organization_id,
396 p_parent_object_id => l_parent_object_id,
397 p_genealogy_origin => 3,
398 p_genealogy_type => 5,
399 p_start_date_active => p_start_date_active,
403 x_msg_data => l_msg_data);
400 p_end_date_active => l_end_date_active,
401 x_return_status => l_return_status,
402 x_msg_count => l_msg_count,
404
405 IF NOT l_return_status = fnd_api.g_ret_sts_success THEN
406 RAISE fnd_api.g_exc_error;
407 end if;
408
409 elsif (l_pn_gen_mode = 1) then
410 inv_genealogy_pub.update_genealogy(
411 p_api_version => 1.0,
412 p_object_type => 2,
413 p_object_number => p_serial_number,
414 p_inventory_item_id => p_inventory_item_id,
415 p_org_id => p_current_organization_id,
416 p_genealogy_origin => 3,
417 p_genealogy_type => 5,
418 p_end_date_active => l_end_date_active,
419 x_return_status => l_return_status,
420 x_msg_count => l_msg_count,
421 x_msg_data => l_msg_data);
422
423 IF NOT l_return_status = fnd_api.g_ret_sts_success THEN
424 RAISE fnd_api.g_exc_error;
425 end if;
426 END IF;
427
428 end if;
429 end if;
430 end if;
431
432 EXCEPTION
433 WHEN fnd_api.g_exc_error THEN
434 ROLLBACK TO pn_eam_genealogy;
435 x_return_status := fnd_api.g_ret_sts_error;
436 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
437 WHEN fnd_api.g_exc_unexpected_error THEN
438 ROLLBACK TO pn_eam_genealogy;
439 x_return_status := fnd_api.g_ret_sts_unexp_error;
440 fnd_msg_pub.count_and_get(
441 p_count => x_msg_count
442 ,p_data => x_msg_data);
443 WHEN OTHERS THEN
444 ROLLBACK TO pn_eam_genealogy;
445 x_return_status := fnd_api.g_ret_sts_unexp_error;
446
447 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
448
449 end pn_genealogy_change;
450
451
452
453 -- The worker procedure to import assets.
454 -- Note that in update mode, if id is null, then it does not mean that we update
455 -- with null, instead we see the code if code is also null then we update with
456 -- null, else if it is g_miss_num or g_miss_char we ignore it, else we update with
457 -- new id got from the code. If id is not null, we straightaway update id, get code.
458 -- In insert mode, no g_miss_num and g_miss_char values are expected. First see id
459 -- If id is null, then see code, if both are null, insert null.
460 PROCEDURE import_asset_numbers
461 (
462 errbuf OUT NOCOPY VARCHAR2,
463 retcode OUT NOCOPY NUMBER,
464 p_interface_group_id IN NUMBER,
465 p_purge_option IN VARCHAR2
466 ) IS
467
468 l_api_name CONSTANT VARCHAR2(30) := 'import_asset_numbers';
469 l_api_version CONSTANT NUMBER := 1.0;
470 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
471 l_stmt_num NUMBER;
472 l_error_message VARCHAR2(2000);
473 l_error_code NUMBER;
474 no_rows_updated EXCEPTION;
475
476 -- 2002-01-11: chrng: handle attribute import failure separately
477 attr_import_failed EXCEPTION;
478
479 l_application_id CONSTANT NUMBER := 401;
480 l_application_code CONSTANT VARCHAR2(3) := 'INV';
481 l_number1 NUMBER;
482 l_varchar2000 VARCHAR2(2000);
483 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
484 l_count NUMBER;
485 l_data VARCHAR2(240);
486 l_object_id NUMBER;
487 l_msg_data varchar2(2000);
488 l_msg_count number;
489
490 l_pn_exists_in_eam varchar2(1) := 'N';
491 l_parent_location_id number;
492 l_parent_in_eam varchar2(1) := 'N';
493 l_parent_serial_number varchar2(30);
494 l_parent_inventory_item_id number;
495
496 l_start_date_active date;
497 l_end_date_active date;
498
499 l_category_set_id NUMBER := -1;
500 l_is_create_asset BOOLEAN;
501 l_serial_rowid ROWID;
502 l_created_rowid VARCHAR2(100);
503 l_conc_status BOOLEAN;
504 l_equipment_type NUMBER;
505 l_skip_field1 BOOLEAN := false;
506 l_skip_field2 BOOLEAN := false;
507 l_skip_field3 BOOLEAN := false;
508 l_skip_all BOOLEAN := false;
509 l_prod_organization_id MTL_EAM_ASSET_NUM_INTERFACE.prod_organization_id%TYPE := NULL;
510 l_equipment_item_id MTL_EAM_ASSET_NUM_INTERFACE.equipment_item_id%TYPE := NULL;
511 l_eqp_serial_number MTL_EAM_ASSET_NUM_INTERFACE.eqp_serial_number%TYPE := NULL;
512
513 l_prod_equipment_type NUMBER;
514 l_asset_criticality_code VARCHAR2(30);
515
516 l_gen_object_id NUMBER;
517 -- Returned parameters from calling the Maintenance Object Instantiation API
518 l_x_moi_return_status VARCHAR2(1);
519 l_x_moi_msg_count NUMBER;
520 l_x_moi_msg_data VARCHAR2(20000);
521
522 l_module varchar2(200);
523 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
524 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
528 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
525 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
526 l_eLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
527 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
529
530 CURSOR asset_num_cur IS
531 SELECT *
532 FROM MTL_EAM_ASSET_NUM_INTERFACE
533 WHERE interface_group_id = p_interface_group_id
534 AND process_flag = 'R'
535 ORDER BY import_mode, import_scope, interface_header_id;
536
537 CURSOR PROPERTY_CUR(child1 in number, parent1 in number) IS
538 SELECT pn_location_id, serial_number, inventory_item_id
539 FROM CSI_ITEM_INSTANCES
540 WHERE pn_location_id in (child1, parent1);
541
542 asset_rec MTL_EAM_ASSET_NUM_INTERFACE%ROWTYPE;
543
544
545 BEGIN
546 --dbms_output.enable(100000);
547 if (l_ulog) then
548 l_module := 'eam.plsql.'|| l_full_name;
549 end if;
550
551 if (l_plog) then
552 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
553 'Entering ' || l_full_name);
554 end if;
555
556
557 -- Standard Start of API savepoint
558 l_stmt_num := 10;
559 SAVEPOINT import_asset_num_interface_pub;
560
561 l_stmt_num := 20;
562
563 -- Initialize message list
564 fnd_msg_pub.initialize;
565
566 l_stmt_num := 40;
567
568 -- Initialize API return status to success
569 -- l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', l_error_message);
570
571 l_stmt_num := 50;
572 -- API starts
573 l_error_code := 9999;
574 l_error_message := 'Unknown Exception';
575
576 if (l_slog) then
577 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'');
578
579 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
580 'Start Worker for Asset Import. Time now is ' ||
581 to_char(sysdate, 'Month DD, YYYY HH24:MI:SS'));
582
583 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'');
584 end if;
585
586
587
588 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
589 'Processing interface group ' || p_interface_group_id ||
590 ' with purge option ' || p_purge_option);
591 end if;
592
593 --store the category set id for eAM for use in every loop
594 -- 2002-01-08: chrng: Use category_set_id directly, see bug 2175340.
595 l_category_set_id := 1000000014;
596
597 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
598 'Opening cursor for records in interface table');
599 end if;
600
601 open asset_num_cur;
602 LOOP
603 BEGIN --this begin is needed for checking exception in every loop
604 SAVEPOINT LOOP_START;
605
606
607 if (l_slog) then
608 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
609 '___________________________________________________________');
610 end if;
611
612 FETCH asset_num_cur INTO asset_rec;
613 EXIT WHEN asset_num_cur%NOTFOUND;
614
615
616 if (l_slog) then
617 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
618 'Processing Record with HEADER ID: '|| asset_rec.interface_header_id);
619
620 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
621 ' for ORGANIZATION (Id/Code): ' ||
622 asset_rec.current_organization_id || '/' || asset_rec.organization_code ||
623 ', ITEM ID: ' || asset_rec.inventory_item_id ||
624 ', SERIAL: ' || asset_rec.serial_number);
625 end if;
626
627
628 if (l_slog) then
629 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
630 'Record will be processed with SCOPE: '
631 || asset_rec.import_scope || ', MODE: ' || asset_rec.import_mode);
632 end if;
633
634
635 if asset_rec.pn_location_id is not null then
636
637 l_error_code := 170;
638 l_error_message := 'Error while validating Property Manager row. The Property Manager location already exists in EAM';
639
640 begin
641
642 select active_start_date, active_end_date, parent_location_id
643 into l_start_date_active, l_end_date_active, l_parent_location_id
644 from pn_locations_all
645 where location_id = asset_rec.pn_location_id
646 and active_start_date <= sysdate
647 and active_end_date >= sysdate;
648
649 l_pn_exists_in_eam := 'N';
650 l_parent_in_eam := 'N';
651 l_parent_serial_number := null;
652 l_parent_inventory_item_id := null;
653
654 for l_property_rec in property_cur(asset_rec.pn_location_id, l_parent_location_id) loop
655
656 EXIT WHEN property_cur%NOTFOUND;
657
658 if l_property_rec.pn_location_id = asset_rec.pn_location_id then
659 l_pn_exists_in_eam := 'Y';
660 else
661
662 l_parent_in_eam := 'Y';
663 l_parent_serial_number := l_property_rec.serial_number;
664 l_parent_inventory_item_id := l_property_rec.inventory_item_id;
665 end if;
666 end loop;
667
668 if property_cur%isopen then
669 close property_cur;
670 end if;
671
672 exception
673 when no_data_found then
674 raise no_data_found;
675 end;
676
677 end if;
678
679
680 --Pass Location Id when location Codes are given
681 -- Added for Bug # 6271101
682 if ((asset_rec.location_codes is not null) and (asset_rec.eam_location_id is null)) then
683 begin
684 select location_id into asset_rec.eam_location_id
685 from mtl_eam_locations
689 when no_data_found then
686 where location_codes = asset_rec.location_codes
687 and organization_id = asset_rec.current_organization_id;
688 exception
690 raise no_data_found;
691 end;
692 end if;
693 --End Validate location
694
695 if ((asset_rec.prod_organization_code is not null) and (asset_rec.prod_organization_id is null)) then
696 begin
697 select organization_id into asset_rec.prod_organization_id
698 from mtl_parameters
699 where organization_code = asset_rec.prod_organization_code;
700 exception
701 when no_data_found then
702 raise no_data_found;
703 end;
704 end if;
705
706 --if mode = 0, create row
707 IF asset_rec.import_mode = 0 AND asset_rec.import_scope<>2 THEN
708
709 if (l_slog) then
710 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
711 'Now inserting asset...');
712 end if;
713
714 if asset_rec.pn_location_id is not null then
715
716 if l_pn_exists_in_eam = 'Y' then
717 raise no_data_found;
718 end if;
719 end if;
720
721
722 eam_assetnumber_pub.Insert_Asset_Number
723 (
724 p_api_version => l_api_version,
725
726 x_object_id => l_object_id,
727 x_return_status => l_return_status,
728 x_msg_count => l_count,
729 x_msg_data => l_data,
730 p_inventory_item_id => asset_rec.inventory_item_id,
731 p_serial_number => asset_rec.serial_number,
732 p_instance_number => asset_rec.instance_number,
733 p_current_status => asset_rec.current_status,
734 p_descriptive_text => asset_rec.descriptive_text,
735 p_current_organization_id => asset_rec.current_organization_id,
736 p_wip_accounting_class_code => asset_rec.wip_accounting_class_code,
737 p_maintainable_flag => asset_rec.maintainable_flag,
738 p_owning_department_id => asset_rec.owning_department_id,
739 p_network_asset_flag => nvl(asset_rec. network_asset_flag,'N'),
740 p_fa_asset_id => asset_rec. fa_asset_id,
741 p_pn_location_id => asset_rec.pn_location_id,
742 p_eam_location_id => asset_rec.eam_location_id,
743 p_asset_criticality_code => to_char(asset_rec.asset_criticality_id),
744 p_category_id => asset_rec.category_id,
745 p_prod_organization_id => asset_rec.prod_organization_id,
746 p_equipment_item_id => asset_rec.equipment_item_id,
747 p_eqp_serial_number => asset_rec.eqp_serial_number,
748 p_active_start_date => asset_rec.active_start_date,
749 p_active_end_date => asset_rec.active_end_date,
750 p_operational_log_flag => asset_rec.operational_log_flag,
751 p_checkin_status => asset_rec.checkin_status,
752 p_supplier_warranty_exp_date => asset_rec.supplier_warranty_exp_date
753 );
754
755 IF NOT l_return_status = fnd_api.g_ret_sts_success THEN
756 l_error_code := 150;
757
758 l_error_message := 'Asset insertion failed with return status: '
759 || l_return_status || ' and message: ' || l_data;
760
761
762 if (l_elog) then
763 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, l_module,l_error_message);
764 end if;
765
766 RAISE NO_DATA_FOUND;
767 ELSE
768 if (l_slog) then
769 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
770 'Asset was inserted.');
771 end if;
772 END IF;
773
774 -- pn integration changes
775 if asset_rec.pn_location_id is not null then
776 l_error_code := 180;
777 l_error_message := 'Failed while trying to insert genealogy for PN';
778
779 pn_genealogy_change (
780 p_pn_location_id => asset_rec.pn_location_id,
781 p_serial_number => asset_rec.serial_number,
782 p_inventory_item_id => asset_rec.inventory_item_id,
783 p_current_organization_id => asset_rec.current_organization_id,
784 p_start_date_active => l_start_date_active,
785 p_end_date_active => l_end_date_active,
786 p_parent_location_id => l_parent_location_id,
787 p_parent_in_eam => l_parent_in_eam,
788 p_parent_serial_number => l_parent_serial_number,
789 p_parent_inventory_item_id => l_parent_inventory_item_id,
790 p_import_mode => asset_rec.import_mode,
791 x_return_status => l_return_status,
792 x_msg_count => l_msg_count,
793 x_msg_data => l_msg_data);
794
795 if l_return_status <> 'S' then
796 raise no_data_found;
797 end if;
798 end if;
799 END IF;
800
801 -- if mode = 1 update record
802 IF asset_rec.import_mode = 1 THEN
803
804 if (l_slog) then
805 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
806 'Now updating asset with ROWID: ' || l_serial_rowid || '...');
807 end if;
808
809 -- 2002-01-16: chrng: Fixed bug 2185999.
810 -- If asset_criticality_id is FND_API.G_MISS_NUM,
811 -- then asset_criticality_code should be FND_API.G_MISS_CHAR
812 IF asset_rec.asset_criticality_id = FND_API.G_MISS_NUM
813 THEN
814 l_asset_criticality_code := FND_API.G_MISS_CHAR;
815 ELSE
816 l_asset_criticality_code := TO_CHAR(asset_rec.asset_criticality_id);
817 END IF;
818
819
820 -- pn integration changes
821 if asset_rec.pn_location_id is not null then
822 l_error_code := 180;
823 l_error_message := 'Failed while trying to insert/update genealogy for PN';
824
825 pn_genealogy_change (
829 p_current_organization_id => asset_rec.current_organization_id,
826 p_pn_location_id => asset_rec.pn_location_id,
827 p_serial_number => asset_rec.serial_number,
828 p_inventory_item_id => asset_rec.inventory_item_id,
830 p_start_date_active => l_start_date_active,
831 p_end_date_active => l_end_date_active,
832 p_parent_location_id => l_parent_location_id,
833 p_parent_in_eam => l_parent_in_eam,
834 p_parent_serial_number => l_parent_serial_number,
835 p_parent_inventory_item_id => l_parent_inventory_item_id,
836 p_import_mode => asset_rec.import_mode,
837 x_return_status => l_return_status,
838 x_msg_count => l_msg_count,
839 x_msg_data => l_msg_data);
840
841 if l_return_status <> 'S' then
842 raise no_data_found;
843 end if;
844 end if;
845
846 eam_assetnumber_pub.update_asset_number
847 (
848 p_api_version => l_api_version,
849 x_return_status => l_return_status,
850 x_msg_count => l_count,
851 x_msg_data => l_data,
852
853 p_inventory_item_id => asset_rec.inventory_item_id,
854 p_serial_number => asset_rec.serial_number,
855 p_instance_number => asset_rec.instance_number,
856 p_current_organization_id => asset_rec.current_organization_id,
857 p_descriptive_text => asset_rec.descriptive_text,
858 p_category_id => asset_rec.category_id,
859 p_prod_organization_id => asset_rec. prod_organization_id,
860 p_equipment_item_id => asset_rec.equipment_item_id,
861 p_eqp_serial_number => asset_rec.eqp_serial_number,
862 p_pn_location_id => asset_rec.pn_location_id,
863 p_eam_location_id => asset_rec.eam_location_id,
864 p_fa_asset_id => asset_rec. fa_asset_id,
865 p_asset_criticality_code => l_asset_criticality_code,
866 p_wip_accounting_class_code => asset_rec.wip_accounting_class_code,
867 p_maintainable_flag => asset_rec.maintainable_flag,
868 p_network_asset_flag => nvl(asset_rec. network_asset_flag,'N'),
869 p_owning_department_id => asset_rec.owning_department_id,
870 p_current_status => asset_rec.current_status,
871 p_active_end_date => asset_rec.active_end_date,
872 p_operational_log_flag => asset_rec.operational_log_flag,
873 p_checkin_status => asset_rec.checkin_status,
874 p_supplier_warranty_exp_date => asset_rec.supplier_warranty_exp_date
875 );
876
877
878 IF NOT l_return_status = fnd_api.g_ret_sts_success THEN
879 l_error_code := 160;
880 l_error_message := 'Asset update failed with return status: '
881 || l_return_status || ' and message: ' || l_data;
882
883 if (l_elog) then
884 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, l_module,l_error_message);
885 end if;
886
887 RAISE NO_DATA_FOUND;
888 ELSE
889 if (l_slog) then
890 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
891 'Asset was updated');
892 end if;
893
894 END IF;
895 END IF;
896
897 --update base table with the latest fields in record asset_rec
898 if (p_purge_option = 'N' or asset_rec.import_scope <> 1) then
899 update_row_calculated_values(asset_rec);
900 end if;
901
902 --Import Asset Extensible attributes
903 IF (asset_rec.import_scope <> 1) THEN
904
905 if (l_slog) then
906 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
907 'Now importing Attributes...');
908 end if;
909
910 EAM_ASSET_ATTR_IMPORT_PVT.import_asset_attr_values(
911 p_api_version => 1.0,
912 p_interface_header_id => asset_rec.interface_header_id,
913 p_import_mode => asset_rec.import_mode,
914 p_purge_option=>p_purge_option,
915 x_return_status => l_return_status,
916 x_msg_count => l_count,
917 x_msg_data => l_data
918 );
919 IF NOT l_return_status = fnd_api.g_ret_sts_success THEN
920 l_error_code := 300;
921
922 -- 2002-01-11: chrng: The following message is long and it causes an error.
923
924 l_error_message := 'Failed because Extensible Attributes import failed';
925
926 if (l_elog) then
927 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, l_module,l_error_message);
928 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, l_module,'Import attribute return status= ' || l_return_status);
929 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, l_module,'Import attribute msg count= ' || l_count);
930 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, l_module,'Import attribute msg data = ' || l_data);
931 end if;
932
933 RAISE attr_import_failed;
934
935 ELSE
936
937 if (l_slog) then
938 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
939 'Attributes were imported.');
940 end if;
941
942 END IF;
943 END IF;
944 --End of Import Asset Extensible attributes
945
946 --set process_flag to 'S'. If it has come to this point, it succeeded
947 UPDATE MTL_EAM_ASSET_NUM_INTERFACE
948 SET process_flag = 'S'
949 WHERE interface_header_id = asset_rec.interface_header_id;
950 COMMIT;
951
952 if (l_slog) then
953 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
954 'Transaction Commited.');
955 end if;
956
957 --end validations if scope =0 or 1
958
959 EXCEPTION
960 -- 2002-01-11: chrng: Fixed bug 2180770, handle attr import failure separately
961 WHEN attr_import_failed THEN
962
966 application_id,
963 DECLARE
964 CURSOR meavi_cur IS
965 SELECT interface_line_id,
967 descriptive_flexfield_name,
968 application_column_name,
969 association_id,
970 process_status,
971 error_number,
972 error_message
973 FROM mtl_eam_attr_val_interface
974 WHERE interface_header_id = asset_rec.interface_header_id;
975
976 TYPE meavi_tabtype IS TABLE OF meavi_cur%ROWTYPE
977 INDEX BY BINARY_INTEGER;
978 meavi_tab meavi_tabtype;
979
980 i BINARY_INTEGER;
981
982 BEGIN
983
984
985 if (l_exlog) then
986 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,
987 'ERROR: ' || l_error_code || ' - ' || l_error_message);
988 end if;
989
990 -- Save the error messages and other calculated columns in the MEAVI table
991 FOR meavi_row IN meavi_cur
992 LOOP
993 meavi_tab(meavi_row.interface_line_id) := meavi_row;
994 END LOOP;
995
996 ROLLBACK to LOOP_START;
997
998
999 if (l_exlog) then
1000 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,
1001 'After attr import failed: Transaction Rolled back to LOOP_START.');
1002 end if;
1003
1004 -- Update MEAVI with error messages
1005 i := meavi_tab.FIRST;
1006 LOOP
1007 EXIT WHEN i IS NULL;
1008
1009 UPDATE mtl_eam_attr_val_interface
1010 SET application_id = meavi_tab(i).application_id,
1011 descriptive_flexfield_name = meavi_tab(i).descriptive_flexfield_name,
1012 application_column_name = meavi_tab(i).application_column_name,
1013 association_id = meavi_tab(i).association_id,
1014 process_status = meavi_tab(i).process_status,
1015 error_number = meavi_tab(i).error_number,
1016 error_message = meavi_tab(i).error_message
1017 WHERE interface_line_id = meavi_tab(i).interface_line_id;
1018
1019 i := meavi_tab.NEXT(i);
1020 END LOOP;
1021
1022 -- Update MEANI with error messages
1023 UPDATE MTL_EAM_ASSET_NUM_INTERFACE
1024 SET process_flag = 'E',
1025 error_code = l_error_code,
1026 error_message = l_error_message
1027 WHERE interface_header_id = asset_rec.interface_header_id;
1028
1029 if (p_purge_option = 'N') then
1030 update_row_calculated_values(asset_rec);
1031 end if;
1032
1033 COMMIT;
1034
1035
1036 if (l_exlog) then
1037 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,
1038 'After attr import failed: Transaction Commited');
1039 end if;
1040
1041 END;
1042
1043 WHEN NO_DATA_FOUND or no_rows_updated THEN
1044
1045 if (l_exlog) then
1046 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,
1047 'ERROR: ' || l_error_code || ' - ' || l_error_message);
1048 end if;
1049
1050 ROLLBACK to LOOP_START;
1051
1052 if (l_exlog) then
1053 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,
1054 'Transaction Rolled back to LOOP_START.');
1055 end if;
1056
1057 update_row_error(l_error_code, l_error_message, asset_rec);
1058 if (p_purge_option = 'N') then
1059 update_row_calculated_values(asset_rec);
1060 end if;
1061 COMMIT;
1062
1063 if (l_exlog) then
1064 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,
1065 'Transaction Commited.');
1066 end if;
1067
1068 -- pn integration changes
1069 -- Bug # 3601150
1070 IF asset_rec.pn_location_id is not null then
1071 update_remaining_row_status(asset_rec.batch_id);
1072 EXIT; --from cursor
1073 END IF;
1074
1075 --dbms_output.put_line(g_msg);
1076 WHEN OTHERS THEN
1077
1078 if (l_exlog) then
1079 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,
1080 'UNEXPECTED ERROR: ' || SQLERRM);
1081 end if;
1082
1083 ROLLBACK to LOOP_START;
1084
1085 if (l_exlog) then
1086 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,
1087 'Transaction Rolled back to LOOP_START.');
1088 end if;
1089
1090 update_row_error(9999, l_varchar2000, asset_rec);
1091 if (p_purge_option = 'N') then
1092 update_row_calculated_values(asset_rec);
1093 end if;
1094 COMMIT;
1095
1096 if (l_exlog) then
1097 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,
1098 'Transaction Commited.');
1099 end if;
1100
1101 RAISE;
1102 END; --of nested BEGIN for exception. BEGIN was just after loop started
1103 END LOOP; -- end of Cursor for loop
1104 IF asset_num_cur%ISOPEN THEN
1105 CLOSE asset_num_cur;
1106 END IF;
1107 -- delete rows marked as success if purge option is true
1108 IF p_purge_option = 'Y' THEN
1109 DELETE FROM MTL_EAM_ASSET_NUM_INTERFACE
1110 WHERE process_flag = 'S'
1111 and interface_group_id = p_interface_group_id;
1112 END IF;
1113
1114 if (l_plog) then
1115 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1116 'Exiting ' || l_full_name);
1117
1121 end if;
1118 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1119 'End Worker for Asset Import. Time now is ' ||
1120 to_char(sysdate, 'Month DD, YYYY HH24:MI:SS'));
1122 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', l_error_message);
1123
1124 EXCEPTION
1125 WHEN OTHERS THEN
1126 l_error_message := 'UNEXPECTED ERROR IN OUTER BLOCK: ' || SQLERRM;
1127 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_error_message);
1128
1129 if (l_exlog) then
1130 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,l_error_message);
1131 end if;
1132
1133 ROLLBACK;
1134
1135 if (l_exlog) then
1136 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,'Transaction Rolled back.');
1137 end if;
1138
1139 IF asset_num_cur%ISOPEN THEN
1140 CLOSE asset_num_cur;
1141 END IF;
1142 -- delete rows marked as success if purge option is true
1143 IF p_purge_option = 'Y' THEN
1144 DELETE FROM MTL_EAM_ASSET_NUM_INTERFACE
1145 WHERE process_flag = 'S'
1146 and interface_group_id = p_interface_group_id;
1147 END IF;
1148 -- update all unprocessed records to errored status
1149 UPDATE MTL_EAM_ASSET_NUM_INTERFACE
1150 SET process_flag = 'E',
1151 error_message = l_error_message
1152 WHERE interface_group_id = p_interface_group_id
1153 and process_flag = 'R';
1154 COMMIT;
1155
1156 if (l_exlog) then
1157 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,'Transaction Commited.');
1158 end if;
1159
1160 if (l_exlog) then
1161 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,
1162 'End Worker for Asset Import. Time now is ' ||
1163 to_char(sysdate, 'Month DD, YYYY HH24:MI:SS'));
1164 end if;
1165
1166 END import_asset_numbers;
1167
1168 END EAM_ASSET_NUM_IMPORT_PUB;