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