[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSET_ATTR_IMPORT_PVT
Source
1 PACKAGE BODY EAM_ASSET_ATTR_IMPORT_PVT AS
2 /* $Header: EAMVAAIB.pls 120.3 2010/09/15 12:25:45 vboddapa ship $*/
3 -- Start of comments
4 -- API name : import_asset_attr_values
5 -- Type : Private
6 -- Function :
7 -- Pre-reqs : None.
8 -- Parameters :
9 -- IN p_api_version IN NUMBER Required
10 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
11 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
12 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
13 --
14 -- p_interface_header_id IN NUMBER Required,
15 -- p_purge_option IN VARCHAR2 Optional Default = 'N'
16 --
17 -- OUT x_return_status OUT VARCHAR2(1)
18 -- x_msg_count OUT NUMBER
19 -- x_msg_data OUT VARCHAR2(2000)
20 --
21 -- x_sql_stmt OUT VARCHAR2
22 -- Version Initial version 1.0 Anirban Dey
23 --
24 -- Notes : This private API imports extensible asset attributes values into
25 -- MTL_EAM_ASET_ATTR_VALUES
26 --
27 --
28 -- End of comments
29
30 g_pkg_name CONSTANT VARCHAR2(30):= 'EAM_ASSET_ATTR_VALUES_PVT';
31
32 -- global variable to turn on/off debug logging.
33
34
35 PROCEDURE import_asset_attr_values
36 (
37 p_api_version IN NUMBER,
38 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
39 p_commit IN VARCHAR2 := fnd_api.g_false,
40 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
41 p_interface_header_id IN NUMBER,
42 p_import_mode IN NUMBER,
43 p_purge_option IN VARCHAR2 := fnd_api.g_false,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2
47 ) IS
48
49 l_api_name CONSTANT VARCHAR2(30) := 'import_asset_attr_values';
50 l_api_version CONSTANT NUMBER := 1.0;
51 --l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
52 l_stmt_generated VARCHAR2(1);
53 CUR INTEGER;
54 RET INTEGER;
55 l_sql_stmt1 VARCHAR2(2000);
56 l_miss_attr_value VARCHAR2(150);
57 l_concatenated_segments VARCHAR2(2000);
58 l_temp_varchar2 VARCHAR2(2000);
59 l_exists_count NUMBER;
60
61 l_application_id CONSTANT NUMBER := 401;
62 l_application_code CONSTANT VARCHAR2(3) := 'INV';
63 l_descriptive_flexfield_name CONSTANT VARCHAR2(30) := 'MTL_EAM_ASSET_ATTR_VALUES';
64
65 l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
66 l_commit VARCHAR2(1) := fnd_api.g_false;
67 l_validation_level NUMBER := fnd_api.g_valid_level_full;
68 l_rowid urowid;
69 l_association_id number;
70 l_inventory_item_id number;
71 l_serial_number varchar2(30);
72 l_organization_id number;
73 l_object_id number;
74 l_attribute_category varchar2(30);
75
76 l_request_id number;
77 l_program_application_id number;
78 l_program_id number;
79 l_program_update_date date;
80 l_last_update_date date := sysdate;
81 l_last_updated_by number := FND_GLOBAL.USER_ID;
82 l_creation_date date := sysdate;
83 l_created_by number := FND_GLOBAL.USER_ID;
84 l_last_update_login number := FND_GLOBAL.LOGIN_ID;
85 l_return_status varchar2(1);
86 l_msg_count number;
87 l_msg_data varchar2(240);
88 l_instance_number varchar2(30);
89 l_instance_id number;
90
91 l_module varchar2(200) ;
92 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
93 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
94 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
95 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
96 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
97
98
99 attr_import_failed EXCEPTION;
100
101 TYPE varchar_table IS TABLE OF varchar2(150)
102 INDEX BY BINARY_INTEGER;
103 TYPE number_table IS TABLE OF number
104 INDEX BY BINARY_INTEGER;
105 TYPE date_table IS TABLE OF date
106 INDEX BY BINARY_INTEGER;
107 TYPE CurTyp IS REF CURSOR;
108 CUROBJ CurTyp;
109 p_i integer;
110 p_ncols integer;
111
112 l_c_attribute varchar_table;
113 l_d_attribute date_table;
114 l_n_attribute number_table;
115
116 p_substr varchar(2);
117 p_substr1 varchar(1);
118 p_num integer := null;
119
120 -- Cursor for the Asset Number in this processing group and Header
121
122 CURSOR asset_numbers_cur IS
123 SELECT instance_number
124 FROM MTL_EAM_ASSET_NUM_INTERFACE meani
125 WHERE meani.interface_header_id = p_interface_header_id
126 ;
127
128 -- Cursor for all attribute Group in this processing group
129 CURSOR attr_group_cur IS
130 SELECT DISTINCT meavi.application_id,
131 meavi.descriptive_flexfield_name,
132 meavi.attribute_category,
133 meavi.association_id
134 FROM MTL_EAM_ATTR_VAL_INTERFACE meavi
135 WHERE meavi.interface_header_id = p_interface_header_id
136 AND meavi.process_status = 'P';
137
138 -- Cursor for every attribute in this processing group
139 CURSOR attr_cur (
140 l_application_id NUMBER,
141 l_descr_flexfield_name VARCHAR2,
142 l_descr_flex_context_code VARCHAR2) IS
143 SELECT meavi.application_column_name,
144 meavi.line_type,
145 meavi.attribute_varchar2_value,
146 meavi.attribute_number_value,
147 meavi.attribute_date_value
148 FROM MTL_EAM_ATTR_VAL_INTERFACE meavi
149 WHERE meavi.interface_header_id = p_interface_header_id
150 AND meavi.application_id = l_application_id
151 AND meavi.descriptive_flexfield_name = l_descr_flexfield_name
152 AND meavi.attribute_category = l_descr_flex_context_code
153 AND meavi.process_status = 'P';
154
155 -- Cursor for missing attribute values in the Interface Table
156 CURSOR missing_attr_cur
157 (
158 l_application_id NUMBER,
159 l_descr_flexfield_name VARCHAR2,
160 l_descr_flex_context_code VARCHAR2) IS
161 SELECT fdfcu.application_column_name
162 FROM fnd_descr_flex_column_usages fdfcu
163 WHERE fdfcu.descriptive_flexfield_name = l_descr_flexfield_name
164 AND fdfcu.descriptive_flex_context_code = l_descr_flex_context_code
165 AND fdfcu.application_id = l_application_id
166 AND fdfcu.application_column_name
167 NOT IN
168 (SELECT meavi.application_column_name
169 FROM mtl_eam_attr_val_interface meavi
170 WHERE meavi.interface_header_id = p_interface_header_id
171 AND meavi.application_id = l_application_id
172 AND meavi.descriptive_flexfield_name = l_descr_flexfield_name
173 AND meavi.attribute_category = l_descr_flex_context_code
174 AND meavi.process_status = 'P');
175
176 BEGIN
177 if(l_ulog) then
178 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
179 end if;
180 -- bug 2834438
181
182 IF (l_plog) THEN
183 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, '===== Entering EAM_ASSET_ATTR_IMPORT_PVT.import_asset_attr_values =====');
184 END IF;
185
186 -- Standard Start of API savepoint
187 SAVEPOINT import_asset_attr_values_pvt;
188
189 -- Standard call to check for call compatibility.
190 IF NOT fnd_api.compatible_api_call(
191 l_api_version
192 ,p_api_version
193 ,l_api_name
194 ,g_pkg_name) THEN
195 RAISE fnd_api.g_exc_unexpected_error;
196 END IF;
197
198 -- Initialize message list if p_init_msg_list is set to TRUE.
199 IF fnd_api.to_boolean(p_init_msg_list) THEN
200 fnd_msg_pub.initialize;
201 END IF;
202
203 -- Initialize API return status to success
204 x_return_status := fnd_api.g_ret_sts_success;
205
206
207
208 -- 2002-01-02: chrng: To fix bug 2167188, check that derived columns are NULL.
209 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
210 SET meavi.error_number = 9999,
211 meavi.process_status = 'E',
212 meavi.error_message = 'Derived column APPLICATION_ID should be NULL'
213 WHERE meavi.interface_header_id = p_interface_header_id
214 AND meavi.process_status = 'P'
215 AND meavi.application_id IS NOT NULL;
216
217 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
218 SET meavi.error_number = 9999,
219 meavi.process_status = 'E',
220 meavi.error_message = 'Derived column DESCRITIVE_FLEXFIELD_NAME should be NULL'
221 WHERE meavi.interface_header_id = p_interface_header_id
222 AND meavi.process_status = 'P'
223 AND meavi.descriptive_flexfield_name IS NOT NULL;
224
225 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
226 SET meavi.error_number = 9999,
227 meavi.process_status = 'E',
228 meavi.error_message = 'Derived column APPLICATION_COLUMN_NAME should be NULL'
229 WHERE meavi.interface_header_id = p_interface_header_id
230 AND meavi.process_status = 'P'
231 AND meavi.application_column_name IS NOT NULL;
232
233 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
234 SET meavi.error_number = 9999,
235 meavi.process_status = 'E',
236 meavi.error_message = 'Derived column ASSOCIATION_ID should be NULL'
237 WHERE meavi.interface_header_id = p_interface_header_id
238 AND meavi.process_status = 'P'
239 AND meavi.association_id IS NOT NULL;
240
241
242 -- Update all rows for this set with proper application_id and Desc Flex Name
243
244 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
245 SET meavi.application_id = l_application_id,
246 meavi.descriptive_flexfield_name = l_descriptive_flexfield_name
247 WHERE meavi.interface_header_id = p_interface_header_id
248 AND meavi.process_status = 'P';
249
250 -- validate flexfield details from Desc FlexField Column Usages Table
251
252 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
253 SET meavi.error_number = 9999,
254 meavi.process_status = 'E',
255 meavi.error_message = 'Decriptive Flexfield Details are Invalid'
256 WHERE meavi.interface_header_id = p_interface_header_id
257 AND meavi.process_status = 'P'
258 AND NOT EXISTS
259 (SELECT 'S'
260 FROM FND_DESCR_FLEX_COLUMN_USAGES mdfcu
261 WHERE meavi.application_id = mdfcu.application_id
262 AND meavi.descriptive_flexfield_name = mdfcu.descriptive_flexfield_name
263 AND meavi.attribute_category = mdfcu.descriptive_flex_context_code
264 AND meavi.end_user_column_name = mdfcu.end_user_column_name
265 AND meavi.application_id = l_application_id
266 AND meavi.descriptive_flexfield_name = l_descriptive_flexfield_name
267 AND meavi.interface_header_id = p_interface_header_id);
268
269 -- Obtain the application column name for each row in the interface table
270
271 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
272 SET meavi.application_column_name = EAM_ASSET_SEARCH_PVT.GET_ATTRIBUTE_COLUMN_NAME
273 (meavi.application_id,
274 meavi.descriptive_flexfield_name,
275 meavi.attribute_category,
276 meavi.end_user_column_name
277 )
278 WHERE meavi.interface_header_id = p_interface_header_id
279 AND meavi.process_status = 'P';
280
281 -- Mark rows as error is application column name is NULL
282
283 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
284 SET meavi.error_number = 9999,
285 meavi.process_status = 'E',
286 meavi.error_message = 'Application Column Name not found'
287 WHERE meavi.interface_header_id = p_interface_header_id
288 AND meavi.process_status = 'P'
289 AND meavi.application_column_name IS NULL;
290
291 -- Obtain associationId if available from asset atribute groups table
292
293 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
294 SET (meavi.association_id) =
295 (SELECT meaag.association_id
296 FROM MTL_EAM_ASSET_ATTR_GROUPS meaag,
297 MTL_EAM_ASSET_NUM_INTERFACE meani
298 WHERE meani.inventory_item_id = meaag.inventory_item_id
299 AND meani.interface_header_id = meavi.interface_header_id
300 AND meavi.application_id = meaag.application_id
301 AND meavi.descriptive_flexfield_name = meaag.descriptive_flexfield_name
302 AND meavi.attribute_category = meaag.descriptive_flex_context_code
303 AND UPPER(NVL(meaag.enabled_flag,'Y')) = 'Y')
304 WHERE meavi.interface_header_id = p_interface_header_id
305 AND meavi.process_status = 'P';
306
307 -- 2001-12-28: chrng: To fix bug 2156429, flag rows without association_id
308 -- (Attribute Group not associated with Asset Group) as Error.
309 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
310 SET meavi.error_number = 9999,
311 meavi.process_status = 'E',
312 meavi.error_message = 'Attribute Group not associated with Asset Group'
313 WHERE meavi.interface_header_id = p_interface_header_id
314 AND meavi.process_status = 'P'
315 AND meavi.association_id IS NULL;
316
317
318 -- 2001-12-26: chrng: To fix bug 2156483, check line_type not out-of-range
319 -- Validate line_type
320 UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
321 SET meavi.error_number = 9999,
322 meavi.process_status = 'E',
323 meavi.error_message = 'line_type must be 1 (VARCHAR2), 2 (NUMBER), or 3 (DATE)'
324 WHERE meavi.interface_header_id = p_interface_header_id
325 AND meavi.process_status = 'P'
326 AND meavi.line_type NOT IN (1, 2, 3);
327
328 -- Open loop for all asset numbers in this processing group
329
330
331 FOR asset IN asset_numbers_cur LOOP
332
333 IF (l_slog) THEN
334 -- bug 2834438
335 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'(asset_number_cur)asset.instance_number='||asset.instance_number);
336 END IF;
337
338 -- Open loop for all attribute groups for an Asset Number
339 FOR attr_group IN attr_group_cur
340 LOOP
341
342 IF (l_slog) THEN
343 -- bug 2834438
344 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, '(attr_group_cur)attr_group.application_id=' || attr_group.application_id);
345 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, '(attr_group_cur)attr_group.descriptive_flexfield_name=' ||
346 attr_group.descriptive_flexfield_name);
347 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, '(attr_group_cur)attr_group.attribute_category=' || attr_group.attribute_category);
348 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, '(attr_group_cur)attr_group.association_id=' || attr_group.association_id);
349
350 END IF;
351
352 l_stmt_generated := 'S';
353
354 l_association_id := attr_group.association_id;
355 l_instance_number := asset.instance_number;
356 l_attribute_category := attr_group.attribute_category;
357
358 select instance_id into l_instance_id from csi_item_instances where instance_number = l_instance_number;
359
360
361 -- 2002-01-11: chrng: Fixed bug 2180770.
362 -- Check if Attribute has not existed (for Create Mode), or exists (for Update Mode)
363 IF (p_import_mode = 0) THEN -- Create Mode
364
365 -- before executing create, make sure that duplicate do not exists.
366 SELECT count(*)
367 INTO l_exists_count
368 FROM MTL_EAM_ASSET_ATTR_VALUES meaav
369 WHERE meaav.maintenance_object_type = 3
370 AND meaav.maintenance_object_id = l_instance_id
371 AND meaav.application_id = attr_group.application_id
372 AND meaav.descriptive_flexfield_name = attr_group.descriptive_flexfield_name
373 AND meaav.attribute_category = attr_group.attribute_category;
374
375 IF (l_exists_count >= 1 ) THEN
376
377 UPDATE mtl_eam_attr_val_interface meavi
378 SET meavi.process_status = 'E',
379 meavi.error_number = 9999,
380 meavi.error_message = 'Attribute Group Already exists'
381 WHERE meavi.interface_header_id = p_interface_header_id
382 AND meavi.application_id = attr_group.application_id
383 AND meavi.descriptive_flexfield_name = attr_group.descriptive_flexfield_name
384 AND meavi.attribute_category = attr_group.attribute_category
385 AND meavi.process_status = 'P';
386
387 l_stmt_generated := 'E';
388
389 END IF;
390
391 ELSIF (p_import_mode = 1) THEN -- Update Mode
392
393 BEGIN
394 SELECT meaav.rowid
395 INTO l_rowid
396 FROM MTL_EAM_ASSET_ATTR_VALUES meaav
397 WHERE meaav.maintenance_object_type = 3
398 AND meaav.maintenance_object_id = l_instance_id
399 AND meaav.application_id = attr_group.application_id
400 AND meaav.descriptive_flexfield_name = attr_group.descriptive_flexfield_name
401 AND meaav.attribute_category = attr_group.attribute_category;
402
403 EXCEPTION
404 -- 2001-12-24: chrng: to fix bug 2157642
405 -- Error if Attribute Group does not exist
406 WHEN NO_DATA_FOUND
407 THEN
408
409 UPDATE mtl_eam_attr_val_interface meavi
410 SET meavi.process_status = 'E',
411 meavi.error_number = 9999,
412 meavi.error_message = 'Attribute Group does not exist'
413 WHERE meavi.interface_header_id = p_interface_header_id
414 AND meavi.application_id = attr_group.application_id
415 AND meavi.descriptive_flexfield_name = attr_group.descriptive_flexfield_name
416 AND meavi.attribute_category = attr_group.attribute_category
417 AND meavi.process_status = 'P';
418
419 l_stmt_generated := 'E';
420
421 END; -- BEGIN, EXCEPTION, END block
422
423 ELSE
424 -- Neither Import or Update Mode
425 -- Should not occur, checked in EAMVANIB.pls
426 NULL;
427 END IF;
428
429 -- bug 2834438
430 -- Initialize
431 p_ncols := 20;
432 FOR p_i in 1..p_ncols LOOP
433 l_c_attribute(p_i) := '';
434 END LOOP;
435
436 p_ncols := 10;
437 FOR p_i in 1..p_ncols LOOP
438 l_d_attribute(p_i) := null;
439 l_n_attribute(p_i) := null;
440 END LOOP;
441
442 /* Bug 3371507
443 IF(p_import_mode = 1) THEN
444 l_c_attribute(1) := fnd_api.g_miss_char;
445 l_c_attribute(2) := fnd_api.g_miss_char;
446 l_c_attribute(3) := fnd_api.g_miss_char;
447 l_c_attribute(4) := fnd_api.g_miss_char;
448 l_c_attribute(5) := fnd_api.g_miss_char;
449 l_c_attribute(6) := fnd_api.g_miss_char;
450 l_c_attribute(7) := fnd_api.g_miss_char;
451 l_c_attribute(8) := fnd_api.g_miss_char;
452 l_c_attribute(9) := fnd_api.g_miss_char;
453 l_c_attribute(10) := fnd_api.g_miss_char;
454 l_c_attribute(11) := fnd_api.g_miss_char;
455 l_c_attribute(12) := fnd_api.g_miss_char;
456 l_c_attribute(13) := fnd_api.g_miss_char;
457 l_c_attribute(14) := fnd_api.g_miss_char;
458 l_c_attribute(15) := fnd_api.g_miss_char;
459 l_c_attribute(16) := fnd_api.g_miss_char;
460 l_c_attribute(17) := fnd_api.g_miss_char;
461 l_c_attribute(18) := fnd_api.g_miss_char;
462 l_c_attribute(19) := fnd_api.g_miss_char;
463 l_c_attribute(20) := fnd_api.g_miss_char;
464 l_d_attribute(1) := fnd_api.g_miss_date;
465 l_d_attribute(2) := fnd_api.g_miss_date;
466 l_d_attribute(3) := fnd_api.g_miss_date;
467 l_d_attribute(4) := fnd_api.g_miss_date;
468 l_d_attribute(5) := fnd_api.g_miss_date;
469 l_d_attribute(6) := fnd_api.g_miss_date;
470 l_d_attribute(7) := fnd_api.g_miss_date;
471 l_d_attribute(8) := fnd_api.g_miss_date;
472 l_d_attribute(9) := fnd_api.g_miss_date;
473 l_d_attribute(10) := fnd_api.g_miss_date;
474 l_n_attribute(1) := fnd_api.g_miss_num;
475 l_n_attribute(2) := fnd_api.g_miss_num;
476 l_n_attribute(3) := fnd_api.g_miss_num;
477 l_n_attribute(4) := fnd_api.g_miss_num;
478 l_n_attribute(5) := fnd_api.g_miss_num;
479 l_n_attribute(6) := fnd_api.g_miss_num;
480 l_n_attribute(7) := fnd_api.g_miss_num;
481 l_n_attribute(8) := fnd_api.g_miss_num;
482 l_n_attribute(9) := fnd_api.g_miss_num;
483 l_n_attribute(10) := fnd_api.g_miss_num;
484 END IF;
485 */
486
487 -- Initialize the server side flex validation API with context value
488 FND_FLEX_DESCVAL.set_context_value(attr_group.attribute_category);
489
490 FOR num_v_attr_col IN 1..20
491 LOOP
492 fnd_flex_descval.set_column_value('C_ATTRIBUTE' || TO_CHAR(num_v_attr_col), '');
493 END LOOP;
494 FOR num_nd_attr_col IN 1..10
495 LOOP
496 fnd_flex_descval.set_column_value('N_ATTRIBUTE' || TO_CHAR(num_nd_attr_col), TO_NUMBER(NULL));
497 fnd_flex_descval.set_column_value('D_ATTRIBUTE' || TO_CHAR(num_nd_attr_col), TO_DATE(NULL));
498 END LOOP;
499
500 -- Open loop for all attributes for an attribute group of an asset number
501 FOR attr IN attr_cur
502 (
503 attr_group.application_id,
504 attr_group.descriptive_flexfield_name,
505 attr_group.attribute_category
506 ) LOOP
507
508 IF (l_slog) THEN
509 -- bug 2834438
510 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, '(attr_cur)attr.application_column_name=' || attr.application_column_name);
511 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, '(attr_cur)attr.line_type=' || attr.line_type);
512 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, '(attr_cur)attr.attribute_varchar2_value=' || attr.attribute_varchar2_value);
513 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, '(attr_cur)attr.attribute_number_value=' || attr.attribute_number_value);
514 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, '(attr_cur)attr.attribute_date_value=' || attr.attribute_date_value);
515 END IF;
516
517
518 p_substr := substr(attr.application_column_name,-1,1);
519 p_substr1 := substr(attr.application_column_name,-2,1);
520
521 IF ((p_substr1 = '1') OR (p_substr1 = '2')) THEN
522 p_substr := p_substr1 || p_substr;
523 END IF;
524
525 p_num := to_number(p_substr);
526
527 IF (l_slog) THEN
528 -- bug 2834438
529 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'p_num=' || p_num);
530 END IF;
531
532
533 IF (attr.line_type = 1) THEN
534 l_c_attribute(p_num) := trim(attr.attribute_varchar2_value);
535
536 fnd_flex_descval.set_column_value(attr.application_column_name,
537 trim(attr.attribute_varchar2_value));
538 ELSIF (attr.line_type = 2) THEN
539 l_n_attribute(p_num) := attr.attribute_number_value;
540 fnd_flex_descval.set_column_value(attr.application_column_name,
541 TO_NUMBER(attr.attribute_number_value));
542
543 ELSIF (attr.line_type = 3) THEN
544 l_d_attribute(p_num) := attr.attribute_date_value;
545 -- Bug # 3373134
546 fnd_flex_descval.set_column_value(attr.application_column_name,
547 TO_DATE(TO_CHAR(attr.attribute_date_value, 'yyyy-mm-dd'),'yyyy-mm-dd'));
548 END IF;
549
550 END LOOP; -- End loop for attributes
551
552
553 IF ( p_import_mode = 1) THEN -- Update Mode
554 -- Populate flex validation API with segment values that are missing
555 -- in the interface table from the base table mtl_eam_asset_attr_values
556
557 FOR missing_attr IN missing_attr_cur
558 (
559 attr_group.application_id,
560 attr_group.descriptive_flexfield_name,
561 attr_group.attribute_category
562 ) LOOP
563
564
565
566 -- Bug: 2094907, added the following to remove DBMS_SQL
567 BEGIN
568 -- Bug # 3373134
569 IF (SUBSTR(missing_attr.application_column_name, 1, 1) = 'D') THEN
570 l_sql_stmt1 := 'SELECT to_char(meaav.'|| missing_attr.application_column_name ||', ''yyyy-mm-dd'')' ;
571 ELSE
572 l_sql_stmt1 := 'SELECT meaav.'|| missing_attr.application_column_name ;
573 END IF;
574 l_sql_stmt1 := l_sql_stmt1 || ' FROM MTL_EAM_ASSET_ATTR_VALUES meaav '
575 || ' WHERE meaav.maintenance_object_type =3 and maintenance_object_id = :instance_id '
576 || ' AND meaav.application_id = :application_id '
577 || ' AND meaav.descriptive_flexfield_name = :descriptive_flexfield_name '
578 || ' AND meaav.attribute_category = :attribute' ;
579 --EXECUTE IMMEDIATE curobj USING asset.serial_number, asset.inventory_item_id,asset.organization_id, attr_group.application_id,attr_group.descriptive_flexfield_name, attr_group.attribute_category;
580
581 OPEN curobj
582 FOR l_sql_stmt1
583 USING l_instance_id,
584 attr_group.application_id,
585 attr_group.descriptive_flexfield_name,
586 attr_group.attribute_category;
587 -- Initialize the server side flex validation API with context value
588 LOOP
589 FETCH curobj INTO l_miss_attr_value;
590 EXIT WHEN curobj%NOTFOUND;
591
592 -- 2002-01-11: chrng: Fixed bug 2181053.
593 -- Have to rely on the naming of the column to determine its type
594 IF (SUBSTR(missing_attr.application_column_name, 1, 1) = 'C') THEN
595 fnd_flex_descval.set_column_value(missing_attr.application_column_name,
596 l_miss_attr_value);
597 ELSIF (SUBSTR(missing_attr.application_column_name, 1, 1) = 'N') THEN
598 fnd_flex_descval.set_column_value(missing_attr.application_column_name,
599 TO_NUMBER(l_miss_attr_value));
600 -- Bug # 3373134
601 ELSIF (SUBSTR(missing_attr.application_column_name, 1, 1) = 'D') THEN
602 fnd_flex_descval.set_column_value(missing_attr.application_column_name,
603 TO_DATE(l_miss_attr_value, 'yyyy-mm-dd'));
604 END IF;
605
606 END LOOP;
607 CLOSE curobj;
608
609 END;
610
611 END LOOP; -- Loop for missing flexfield attribute columns
612
613 END IF; -- End if UPDATE mode
614
615 -- Call to validate descriptive flex values using value sets
616 IF (NOT FND_FLEX_DESCVAL.validate_desccols(l_application_code,
617 l_descriptive_flexfield_name,
618 'I',
619 SYSDATE)) THEN
620
621 -- Value Set validation failed, mark rows as error
622 UPDATE mtl_eam_attr_val_interface meavi
623 SET meavi.process_status = 'E',
624 meavi.error_number = 9999,
625 meavi.error_message = FND_FLEX_DESCVAL.error_message
626 WHERE meavi.interface_header_id = p_interface_header_id
627 AND meavi.application_id = attr_group.application_id
628 AND meavi.descriptive_flexfield_name = attr_group.descriptive_flexfield_name
629 AND meavi.attribute_category = attr_group.attribute_category
630 AND meavi.process_status = 'P';
631
632 l_stmt_generated := 'E';
633
634 -- ELSE
635 -- commented the following by sraval as this statement raises exception
636 -- l_concatenated_segments := FND_FLEX_DESCVAL.concatenated_ids;
637
638 END IF; -- end value valid
639
640
641 -- Check if any attribute has failed. Even if one has failed, don't insert/update row.
642 DECLARE
643 CURSOR failed_meavi_row_cur IS
644 SELECT meavi.interface_line_id
645 FROM mtl_eam_attr_val_interface meavi
646 WHERE meavi.interface_header_id = p_interface_header_id
647 AND meavi.application_id = attr_group.application_id
648 AND meavi.descriptive_flexfield_name = attr_group.descriptive_flexfield_name
649 AND meavi.attribute_category = attr_group.attribute_category
650 AND meavi.process_status = 'E'
651 AND meavi.error_number IS NOT NULL;
652
653 failed_meavi_row_rec failed_meavi_row_cur%ROWTYPE;
654
655 BEGIN
656 OPEN failed_meavi_row_cur;
657 FETCH failed_meavi_row_cur INTO failed_meavi_row_rec;
658 IF failed_meavi_row_cur%FOUND
659 THEN
660 -- some row has failed, don't insert/update row
661 l_stmt_generated := 'E';
662 END IF;
663
664 -- To fix bug 2834438
665 CLOSE failed_meavi_row_cur;
666
667 EXCEPTION
668 WHEN OTHERS
669 THEN
670 IF failed_meavi_row_cur%ISOPEN
671 THEN
672 CLOSE failed_meavi_row_cur;
673 END IF;
674
675 IF (l_exlog) THEN
676 -- bug 2834438
677 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module, 'FETCH failed_meavi_row_cur failed. Raising Exception.');
678 END IF;
679
680 RAISE;
681 END;
682
683
684 IF (l_slog) THEN
685 -- bug 2834438
686 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'l_stmt_generated=' || l_stmt_generated);
687 END IF;
688
689
690 IF (l_stmt_generated = 'S') THEN
691 -- If none of the attr has failed, then validation passed, insert row.
692 BEGIN
693
694 if(p_import_mode = 0) then
695 EAM_ASSET_ATTR_PVT.INSERT_ROW(
696 p_api_version => l_api_version,
697 p_init_msg_list => l_init_msg_list,
698 p_commit => l_commit ,
699 p_validation_level => l_validation_level,
700 p_rowid => l_rowid ,
701 p_association_id => l_association_id ,
702 p_application_id => l_application_id,
703 p_descriptive_flexfield_name => l_descriptive_flexfield_name ,
704 p_inventory_item_id => l_inventory_item_id,
705 p_serial_number => l_serial_number ,
706 p_organization_id => l_organization_id,
707 p_attribute_category => l_attribute_category ,
708 p_c_attribute1 => l_c_attribute(1 ),
709 p_c_attribute2 => l_c_attribute(2 ),
710 p_c_attribute3 => l_c_attribute(3),
711 p_c_attribute4 => l_c_attribute(4 ),
712 p_c_attribute5 => l_c_attribute(5 ),
713 p_c_attribute6 => l_c_attribute(6 ),
714 p_c_attribute7 => l_c_attribute(7 ),
715 p_c_attribute8 => l_c_attribute(8 ),
716 p_c_attribute9 => l_c_attribute(9),
717 p_c_attribute10 => l_c_attribute(10),
718 p_c_attribute11 => l_c_attribute(11),
719 p_c_attribute12 => l_c_attribute(12),
720 p_c_attribute13 => l_c_attribute(13 ),
721 p_c_attribute14 => l_c_attribute(14),
722 p_c_attribute15 => l_c_attribute(15 ),
723 p_c_attribute16 => l_c_attribute(16),
724 p_c_attribute17 => l_c_attribute(17),
725 p_c_attribute18 => l_c_attribute(18),
726 p_c_attribute19 => l_c_attribute(19 ),
727 p_c_attribute20 => l_c_attribute(20 ),
728 p_d_attribute1 => l_d_attribute(1),
729 p_d_attribute2 => l_d_attribute(2),
730 p_d_attribute3 => l_d_attribute(3),
731 p_d_attribute4 => l_d_attribute(4),
732 p_d_attribute5 => l_d_attribute(5 ),
733 p_d_attribute6 => l_d_attribute(6),
734 p_d_attribute7 => l_d_attribute(7),
735 p_d_attribute8 => l_d_attribute(8 ),
736 p_d_attribute9 => l_d_attribute(9),
737 p_d_attribute10 => l_d_attribute(10),
738 p_n_attribute1 => l_n_attribute(1),
739 p_n_attribute2 => l_n_attribute(2 ),
740 p_n_attribute3 => l_n_attribute(3),
741 p_n_attribute4 => l_n_attribute(4),
742 p_n_attribute5 => l_n_attribute(5),
743 p_n_attribute6 => l_n_attribute(6 ),
744 p_n_attribute7 => l_n_attribute(7),
745 p_n_attribute8 => l_n_attribute(8),
746 p_n_attribute9 => l_n_attribute(9 ),
747 p_n_attribute10 => l_n_attribute(10),
748 p_last_update_date => l_last_update_date,
749 p_last_updated_by => l_last_updated_by ,
750
751 p_maintenance_object_type => 3,
752 p_maintenance_object_id => l_instance_id,
753 p_creation_organization_id => l_organization_id,
754
755 p_creation_date => l_creation_date,
756 p_created_by => l_created_by,
757 p_last_update_login => l_last_update_login ,
758 x_return_status => l_return_status,
759 x_msg_count => l_msg_count ,
760 x_msg_data => l_msg_data
761 );
762
763 IF (l_slog) THEN
764 -- bug 2834438
765 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'EAM_ASSET_ATTR_PVT.Insert_Row return status=' || l_return_status);
766 END IF;
767
768
769
770 IF NOT l_return_status = fnd_api.g_ret_sts_success THEN
771 RAISE NO_DATA_FOUND;
772 END IF;
773
774 end if;
775
776 if(p_import_mode = 1) then
777 EAM_ASSET_ATTR_PVT.UPDATE_ROW(
778 p_api_version => l_api_version,
779 p_init_msg_list => l_init_msg_list,
780 p_commit => l_commit,
781 p_validation_level => l_validation_level,
782 p_rowid => l_rowid,
783 p_c_attribute1 => l_c_attribute(1),
784 p_c_attribute2 => l_c_attribute(2),
785 p_c_attribute3 => l_c_attribute(3),
786 p_c_attribute4 => l_c_attribute(4),
787 p_c_attribute5 => l_c_attribute(5),
788 p_c_attribute6 => l_c_attribute(6),
789 p_c_attribute7 => l_c_attribute(7),
790 p_c_attribute8 => l_c_attribute(8),
791 p_c_attribute9 => l_c_attribute(9),
792 p_c_attribute10 => l_c_attribute(10),
793 p_c_attribute11 => l_c_attribute(11),
794 p_c_attribute12 => l_c_attribute(12),
795 p_c_attribute13 => l_c_attribute(13),
796 p_c_attribute14 => l_c_attribute(14),
797 p_c_attribute15 => l_c_attribute(15),
798 p_c_attribute16 => l_c_attribute(16),
799 p_c_attribute17 => l_c_attribute(17),
800 p_c_attribute18 => l_c_attribute(18),
801 p_c_attribute19 => l_c_attribute(19),
802 p_c_attribute20 => l_c_attribute(20),
803 p_d_attribute1 => l_d_attribute(1),
804 p_d_attribute2 => l_d_attribute(2),
805 p_d_attribute3 => l_d_attribute(3),
806 p_d_attribute4 => l_d_attribute(4),
807 p_d_attribute5 => l_d_attribute(5 ),
808 p_d_attribute6 => l_d_attribute(6),
809 p_d_attribute7 => l_d_attribute(7),
810 p_d_attribute8 => l_d_attribute(8 ),
811 p_d_attribute9 => l_d_attribute(9),
812 p_d_attribute10 => l_d_attribute(10),
813 p_n_attribute1 => l_n_attribute(1),
814 p_n_attribute2 => l_n_attribute(2 ),
815 p_n_attribute3 => l_n_attribute(3),
816 p_n_attribute4 => l_n_attribute(4),
817 p_n_attribute5 => l_n_attribute(5),
818 p_n_attribute6 => l_n_attribute(6 ),
819 p_n_attribute7 => l_n_attribute(7),
820 p_n_attribute8 => l_n_attribute(8),
821 p_n_attribute9 => l_n_attribute(9 ),
822 p_n_attribute10 => l_n_attribute(10),
823 p_maintenance_object_type => 3,
824 p_maintenance_object_id => l_instance_id,
825 p_last_update_date => l_last_update_date,
826 p_last_updated_by => l_last_updated_by ,
827 p_last_update_login => l_last_update_login ,
828 x_return_status => l_return_status,
829 x_msg_count => l_msg_count ,
830 x_msg_data => l_msg_data
831 );
832
833 IF (l_slog) THEN
834 -- bug 2834438
835 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'EAM_ASSET_ATTR_PVT.Update_Row return status=' || l_return_status);
836 END IF;
837
838
839 IF NOT l_return_status = fnd_api.g_ret_sts_success THEN
840 RAISE NO_DATA_FOUND;
841 END IF;
842
843 END IF;
844
845 UPDATE mtl_eam_attr_val_interface meavi
846 SET meavi.process_status = 'S',
847 meavi.error_number = NULL,
848 meavi.error_message = 'Success'
849 WHERE meavi.interface_header_id = p_interface_header_id
850 AND meavi.application_id = attr_group.application_id
851 AND meavi.descriptive_flexfield_name = attr_group.descriptive_flexfield_name
852 AND meavi.attribute_category = attr_group.attribute_category
853 AND meavi.process_status = 'P';
854
855
856 EXCEPTION WHEN OTHERS THEN
857
858 IF dbms_sql.is_open(cur) THEN
859 dbms_sql.close_cursor(cur);
860 END IF;
861
862 IF (l_exlog) THEN
863 -- bug 2834438
864 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module, 'Insert/Update call to EAM_ASSET_ATTR_PVT failed. Raising exception.');
865 END IF;
866 Raise;
867
868 END;
869
870 END IF; -- l_stmt_generated = 'S'
871
872 END LOOP; -- End loop for attribute Groups
873
874 END LOOP; -- End Loop for Asset Numbers
875
876 IF (l_slog) THEN
877 -- bug 2834438
878 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'Out of loop for Asset Numbers');
879 END IF;
880 -- Check if any attribute associated with the interface_header_id has failed.
881 -- Even if one has failed, fail the whole procedure.
882 DECLARE
883 CURSOR all_failed_meavi_row_cur IS
884 SELECT meavi.interface_line_id
885 FROM mtl_eam_attr_val_interface meavi
886 WHERE meavi.interface_header_id = p_interface_header_id
887 -- Since the following fields can have errors
888 -- AND meavi.application_id = l_application_id
889 -- AND meavi.descriptive_flexfield_name = l_descriptive_flexfield_name
890 AND meavi.process_status = 'E'
891 AND meavi.error_number IS NOT NULL;
892
893 all_failed_meavi_row_rec all_failed_meavi_row_cur%ROWTYPE;
894
895 BEGIN
896 OPEN all_failed_meavi_row_cur;
897 FETCH all_failed_meavi_row_cur INTO all_failed_meavi_row_rec;
898
899 IF all_failed_meavi_row_cur%FOUND
900 THEN
901 IF (l_slog) THEN
902 -- bug 2834438
903 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'all_failed_meavi_row_cur%FOUND. Raising exception attr_import_failed');
904 END IF;
905 -- some row has failed, so the while import procedure fails
906 RAISE attr_import_failed;
907 END IF;
908
909 -- To fix bug 2834438
910 CLOSE all_failed_meavi_row_cur;
911
912 EXCEPTION
913 WHEN OTHERS
914 THEN
915 IF all_failed_meavi_row_cur%ISOPEN
916 THEN
917 CLOSE all_failed_meavi_row_cur;
918 END IF;
919
920 IF (l_exlog) THEN
921 -- bug 2834438
922 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module, 'FETCH all_failed_meavi_row_cur failed. Raising exception.');
923 END IF;
924 RAISE;
925 END;
926
927 -- Purge Search Criteria from Temp table
928 IF p_purge_option = 'Y' THEN
929
930 DELETE MTL_EAM_ATTR_VAL_INTERFACE meavi
931 WHERE meavi.interface_header_id = p_interface_header_id
932 AND ERROR_NUMBER IS NULL
933 AND PROCESS_STATUS = 'S';
934 END IF;
935
936
937 -- End of API body.
938 -- Standard check of p_commit.
939 IF fnd_api.to_boolean(p_commit) THEN
940 COMMIT WORK;
941 END IF;
942
943 -- Standard call to get message count and if count is 1, get message info.
944 fnd_msg_pub.count_and_get(
945 p_encoded => fnd_api.g_false
946 ,p_count => x_msg_count
947 ,p_data => x_msg_data);
948
949 IF (l_plog) THEN
950 -- bug 2834438
951 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, '===== Exiting EAM_ASSET_ATTR_IMPORT_PVT.import_asset_attr_values =====');
952 END IF;
953 EXCEPTION
954
955 WHEN attr_import_failed THEN
956 -- Update all records as error if one of the attributes rows have errored out
957 -- within any attribute group of the asset
958 UPDATE mtl_eam_attr_val_interface meavi
959 SET meavi.process_status = 'E',
960 meavi.error_number = 9999,
961 meavi.error_message = 'Failed as another Attribute of this Asset Number has failed validation'
962 WHERE meavi.interface_header_id = p_interface_header_id
963 -- AND meavi.application_id = l_application_id
964 -- AND meavi.descriptive_flexfield_name = l_descriptive_flexfield_name
965 -- AND meavi.attribute_category = attr_group.attribute_category
966 AND meavi.process_status <> 'E' -- could be 'S' or 'P'
967 -- AND meavi.process_status = 'P'
968 AND meavi.error_number IS NULL
969 AND EXISTS(
970 SELECT meavi.process_status
971 FROM mtl_eam_attr_val_interface meavi
972 WHERE meavi.interface_header_id = p_interface_header_id
973 AND meavi.application_id = l_application_id
974 AND meavi.descriptive_flexfield_name = l_descriptive_flexfield_name
975 -- AND meavi.attribute_category = attr_group.attribute_category
976 AND meavi.process_status = 'E'
977 AND meavi.error_number IS NOT NULL);
978
979 x_return_status := fnd_api.g_ret_sts_error;
980 fnd_msg_pub.count_and_get(
981 p_encoded => fnd_api.g_false
982 ,p_count => x_msg_count
983 ,p_data => x_msg_data);
984
985 IF (l_exlog) THEN
986 -- bug 2834438
987 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module, 'EXEPTION attr_import_failed.');
988 END IF;
989 WHEN fnd_api.g_exc_error THEN
990 -- ROLLBACK TO import_asset_attr_values_pvt;
991 x_return_status := fnd_api.g_ret_sts_error;
992 fnd_msg_pub.count_and_get(
993 p_encoded => fnd_api.g_false
994 ,p_count => x_msg_count
995 ,p_data => x_msg_data);
996
997 IF (l_exlog) THEN
998 -- bug 2834438
999 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module, 'EXEPTION fnd_api.g_exc_error.');
1000 END IF;
1001
1002 WHEN fnd_api.g_exc_unexpected_error THEN
1003 -- ROLLBACK TO import_asset_attr_values_pvt;
1004 x_return_status := fnd_api.g_ret_sts_unexp_error;
1005 fnd_msg_pub.count_and_get(
1006 p_encoded => fnd_api.g_false
1007 ,p_count => x_msg_count
1008 ,p_data => x_msg_data);
1009
1010 IF (l_exlog) THEN
1011 -- bug 2834438
1012 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module, 'EXEPTION fnd_api.g_exc_unexpected_error.');
1013 END IF;
1014
1015 WHEN OTHERS THEN
1016 -- ROLLBACK TO import_asset_attr_values_pvt;
1017 x_return_status := fnd_api.g_ret_sts_unexp_error;
1018
1019 IF fnd_msg_pub.check_msg_level(
1020 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1021 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
1022 END IF;
1023
1024 fnd_msg_pub.count_and_get(
1025 p_encoded => fnd_api.g_false
1026 ,p_count => x_msg_count
1027 ,p_data => x_msg_data);
1028
1029 IF (l_exlog) THEN
1030 -- bug 2834438
1031 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module, 'EXEPTION OTHERS.');
1032 END IF;
1033
1034 END import_asset_attr_values;
1035
1036
1037 END EAM_ASSET_ATTR_IMPORT_PVT;