[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_SRC_MAPPING
Source
1 PACKAGE BODY AMS_LIST_SRC_MAPPING AS
2 /* $Header: amsvlsrb.pls 115.31 2004/06/15 20:44:37 solin ship $ */
3 --------------------------------------------------------------------------------
4 --
5 -- NAME
6 -- AMS_LIST_SRC_MAPPING
7 --
8 -- HISTORY
9 -- 24-MAY-2002 huili Added "Import_Type" for the "create_mapping" module
10 -- 29-MAY-2002 huili Change the "c_required_field_id" definition.
11 -- 12-AUG-2002 huili Bug #2502991 for both XML and NONE XML.
12 -- 14-AUG-2002 huili Bug #2441049 for both XML and NONE XML.
13 ------------------------------------------------------------------------------
14
15 PROCEDURE create_mapping(
16 p_api_version IN NUMBER,
17 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
21 x_msg_count OUT NOCOPY NUMBER,
18 p_commit IN VARCHAR2 := FND_API.g_false,
19 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
20 x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_data OUT NOCOPY VARCHAR2,
23 p_imp_list_header_id in NUMBER,
24 p_source_name in varchar2,
25 p_table_name in varchar2,
26 p_list_src_fields IN l_Tbl_Type ,
27 p_list_target_fields IN l_Tbl_Type ,
28 px_src_type_id in OUT NOCOPY number
29 ) IS
30 l_api_name constant varchar2(30) := 'create_mapping';
31 l_api_version CONSTANT NUMBER := 1.0;
32 l_list_source_type_id number;
33 l_list_source_field_id number;
34 l_list_target_fields l_Tbl_Type ;
35 l_temp_fields l_Tbl_Type ;
36
37 CURSOR c_get_id is
38 SELECT ams_list_src_types_s.NEXTVAL
39 FROM DUAL;
40
41 CURSOR c_get_field_id is
42 SELECT ams_list_src_fields_s.NEXTVAL
43 FROM DUAL;
44
45 CURSOR c_get_source_col_name(p_src_id number) is
46 SELECT source_column_name
47 FROM ams_list_src_fields_vl
48 WHERE list_source_type_id=p_src_id
49 ORDER BY LIST_SOURCE_FIELD_ID;
50
51 CURSOR c_get_source_name(p_src_type_id number) is
52 SELECT source_type_code FROM ams_list_src_types_vl
53 where list_source_type_id = p_src_type_id ;
54
55 CURSOR c_get_import_type(p_import_list_header_id number) is
56 SELECT import_type, b2b_flag
57 FROM AMS_IMP_LIST_HEADERS_ALL
58 WHERE IMPORT_LIST_HEADER_ID = p_import_list_header_id;
59 l_import_type c_get_import_type%ROWTYPE;
60
61 CURSOR c_get_list_source_name(name VARCHAR2) IS
62 SELECT 1
63 FROM ams_list_src_types_vl
64 WHERE list_source_name=name;
65
66 mapping_table dbms_utility.uncl_array;
67 l_no_of_rows number;
68 l_no_of_rows_saved number;
69 l_tmp_var number :=1;
70 l_old_map_name VARCHAR2(30);
71
72 --cursor c_count_import(cur_source_type_id number,
73 -- cur_table_name varchar2) is
74 --SELECT COUNT(1)
75 --FROM ams_imp_col_mapping ai
76 --WHERE ai.required_flag = 'Y'
77 --AND ai.table_name = cur_table_name
78 --AND NOT EXISTS ( SELECT 'x'
79 -- FROM ams_list_src_fields al
80 -- WHERE al.field_table_name = ai.table_name
81 -- AND al.field_column_name IS NOT NULL
82 -- AND NVL (al.field_column_name, '') = ai.column_name
83 -- AND al.list_source_type_id = cur_source_type_id );
84
85 cursor c_count_import(cur_source_type_id number,
86 p_import_type varchar2) is
87 SELECT COUNT(1)
88 FROM ak_region_items_vl ai
89 WHERE ai.region_code LIKE 'AMS_IMPH_METADATA_0%'
90 AND ai.required_flag = 'Y'
91 AND ai.SORTBY_VIEW_ATTRIBUTE_NAME = p_import_type
92 AND NOT EXISTS ( SELECT 'x'
93 FROM ams_list_src_fields al
94 WHERE UPPER(al.field_table_name) = UPPER(ai.view_usage_name)
95 AND al.field_column_name IS NOT NULL
96 AND UPPER(al.field_column_name) = UPPER(ai.view_attribute_name)
97 AND al.list_source_type_id = cur_source_type_id );
98
99 --CURSOR c_required_field_id (p_source_type_id NUMBER,
100 -- p_table_name VARCHAR2) IS
101 --SELECT ai.COL_MAPPING_ID
102 --FROM ams_imp_col_mapping ai
103 --WHERE ai.required_flag = 'Y'
104 --AND ai.table_name = p_table_name
105 --AND ai.column_name NOT IN ( SELECT al.field_column_name
106 -- FROM ams_list_src_fields al
107 -- WHERE al.field_table_name = ai.table_name
108 -- AND al.field_column_name IS NOT NULL
109 -- AND al.list_source_type_id = p_source_type_id );
110
111
112 l_count number;
113 l_dummy number;
114 l_debug_msg VARCHAR2(2000);
115 BEGIN
116
117 SAVEPOINT create_mapping;
118
119 IF FND_API.to_boolean(p_init_msg_list) THEN
120 FND_MSG_PUB.initialize;
121 END IF;
122
123 IF NOT FND_API.compatible_api_call
124 (
125 l_api_version,
126 p_api_version,
127 l_api_name,
128 g_pkg_name
129 )
130 THEN
131 RAISE FND_API.g_exc_unexpected_error;
132 END IF;
133 x_return_status := FND_API.G_RET_STS_SUCCESS;
134
135 OPEN c_get_import_type (p_imp_list_header_id);
136 FETCH c_get_import_type INTO l_import_type;
137 CLOSE c_get_import_type;
138
139 IF l_import_type.IMPORT_TYPE = 'CUSTOMER' AND l_import_type.b2b_flag = 'Y' THEN
140 l_import_type.IMPORT_TYPE := 'B2B';
141 ELSIF l_import_type.IMPORT_TYPE = 'CUSTOMER' AND l_import_type.b2b_flag = 'N' THEN
142 l_import_type.IMPORT_TYPE := 'B2C';
143 END IF;
144
145 IF px_src_type_id is not null then -- update
146
147 FOR l_rec IN c_get_source_col_name(px_src_type_id)
148 LOOP
149 l_temp_fields(l_tmp_var):=l_rec.source_column_name;
150 l_tmp_var := l_tmp_var +1;
151 END LOOP;
152
153 FOR l_rec IN c_get_source_name(px_src_type_id)
154 LOOP
155 l_old_map_name:=l_rec.source_type_code;
156
157 END LOOP;
158
159 l_no_of_rows_saved :=l_temp_fields.last;
160 l_no_of_rows := p_list_src_fields.last;
161
162 IF l_no_of_rows_saved <> l_no_of_rows THEN
163 FND_MESSAGE.Set_Name('AMS','AMS_INVALID_MAP');
164 FND_MSG_PUB.Add;
165 RAISE FND_API.g_exc_error;
166 END If;
167
168 for i in 1 .. l_no_of_rows
169 loop
170 begin
171 IF p_list_src_fields(i) <> l_temp_fields(i) THEN
172 FND_MESSAGE.Set_Name('AMS','AMS_INVALID_MAP');
173 FND_MSG_PUB.Add;
174 RAISE FND_API.g_exc_error;
175 END If;
176 end;
180 FETCH c_get_list_source_name INTO l_dummy;
177 end loop;
178 ELSE -- create, need to check the name
179 OPEN c_get_list_source_name(p_source_name);
181 IF (l_dummy is not null)
182 THEN
183 FND_MESSAGE.set_name('AMS', 'AMS_MAPPING_NAME_DUPLICATE');
184 FND_MSG_PUB.Add;
185 RAISE FND_API.g_exc_error;
186 END IF;
187 End if;
188
189 IF p_list_target_fields IS NULL OR p_list_target_fields.COUNT=0
190 THEN
191 FND_MESSAGE.Set_Name('AMS','AMS_INVALID_MAP');
192 FND_MSG_PUB.Add;
193 RAISE FND_API.g_exc_error;
194 END IF;
195
196 IF p_list_src_fields.last <> p_list_target_fields.last
197 THEN
198 FND_MESSAGE.Set_Name('AMS','AMS_MAX_COL_MAP');
199 FND_MSG_PUB.Add;
200 RAISE FND_API.g_exc_error;
201 END IF;
202
203 if px_src_type_id is null then
204 OPEN c_get_id;
205 FETCH c_get_id INTO l_list_source_type_id;
206 CLOSE c_get_id;
207 px_src_type_id := l_list_source_type_id;
208 else
209 /****
210 delete from ams_list_src_types
211 where list_source_type_id = px_src_type_id ;
212
213 delete from ams_list_src_types_tl
214 where list_source_type_id = px_src_type_id ;
215
216 DELETE FROM ams_list_src_fields_tl
217 WHERE list_source_field_id IN
218 (SELECT list_source_field_id FROM ams_list_src_fields
219 WHERE list_source_type_id =px_src_type_id);
220
221
222 delete from ams_list_src_fields
223 where list_source_type_id = px_src_type_id;
224
225 l_list_source_type_id := px_src_type_id ;
226 ****/
227 IF p_source_name = l_old_map_name THEN
228 update ams_imp_list_headers_all
229 set LIST_SOURCE_TYPE_ID = px_src_type_id,
230 last_update_date= SYSDATE
231 where IMPORT_LIST_HEADER_ID = p_imp_list_header_id;
232 RETURN;
233 END IF;
234 end if;
235 --ELSE
236 --create a new mapping with new name but leave the old one intact
237 OPEN c_get_id;
238 FETCH c_get_id INTO l_list_source_type_id;
239 CLOSE c_get_id;
240 px_src_type_id := l_list_source_type_id;
241
242 INSERT INTO ams_list_src_types (
243 list_source_type_id,
244 last_update_date,
245 last_updated_by,
246 creation_date,
247 created_by,
248 last_update_login,
249 object_version_number,
250 list_source_type,
251 source_type_code,
252 source_object_name,
253 master_source_type_flag,
254 source_object_pk_field,
255 enabled_flag,
256 view_application_id,
257 IMPORT_TYPE
258 )
259 values (
260 l_list_source_type_id,
261 sysdate,
262 fnd_global.user_id,
263 sysdate,
264 fnd_global.user_id,
265 fnd_global.conc_login_id,
266 1,
267 'IMPORT',
268 p_source_name,
269 'DATA FILE',
270 'Y',
271 '',
272 'Y',
273 530, --CHANGE!!
274 l_import_type.IMPORT_TYPE
275 );
276
277 insert into ams_list_src_types_tl (
278 language,
279 source_lang,
280 list_source_name,
281 description,
282 list_source_type_id,
283 last_update_date,
284 last_update_by,
285 creation_date,
286 created_by,
287 last_update_login
288 )
289 select
290 l.language_code,
291 userenv('LANG'),
292 p_source_name,
293 p_source_name,
294 l_list_source_type_id,
295 sysdate,
296 FND_GLOBAL.user_id,
297 sysdate,
298 FND_GLOBAL.user_id,
299 FND_GLOBAL.conc_login_id
300 from FND_LANGUAGES L
301 where L.INSTALLED_FLAG in ('I', 'B')
302 and not exists
303 (select NULL
304 from AMS_LIST_SRC_TYPES_TL T
305 where T.LIST_SOURCE_TYPE_ID = l_list_source_type_id
306 and T.LANGUAGE = L.LANGUAGE_CODE);
307
308 l_no_of_rows := p_list_src_fields.last;
309 for i in 1 .. l_no_of_rows
310 loop
311 begin
312 select column_name
313 into l_list_target_fields(i)
314 from ams_imp_col_mapping
315 where table_name = p_table_name
316 and meaning = p_list_target_fields(i) ;
317 exception
318 when no_data_found then
319 l_list_target_fields(i) := p_list_target_fields(i) ;
320 end;
321 end loop;
322
323 l_no_of_rows := p_list_src_fields.last;
324
325 --
326 -- Remove the following code since the target fields contain column names
327 --
328 --for i in 1 .. l_no_of_rows
329 --loop
330 -- begin
331 -- select column_name
332 -- into l_list_target_fields(i)
333 -- from ams_imp_col_mapping
334 -- where table_name = p_table_name
335 -- and meaning = p_list_target_fields(i) ;
336 -- exception
337 -- when no_data_found then
338 -- l_list_target_fields(i) := p_list_target_fields(i) ;
339 -- end;
340 --end loop;
341
342 for i in 1 .. l_no_of_rows
343 loop
344
345 OPEN c_get_field_id;
346 FETCH c_get_field_id INTO l_list_source_field_id;
347 CLOSE c_get_field_id;
348
349 INSERT INTO ams_list_src_fields (
350 list_source_field_id,
351 last_update_date,
352 last_updated_by,
353 creation_date,
354 created_by,
355 last_update_login,
356 object_version_number,
357 de_list_source_type_code,
358 list_source_type_id,
359 field_table_name,
360 field_column_name,
361 source_column_name,
362 -- source_column_meaning,
363 enabled_flag,
364 start_position,
365 end_position
366 )
367 (SELECT
368 l_list_source_field_id,
369 sysdate,
370 fnd_global.user_id,
371 sysdate,
372 fnd_global.user_id,
373 fnd_global.conc_login_id,
374 1,
375 p_source_name,
376 l_list_source_type_id,
377 p_table_name,
378 --l_list_target_fields(i),
379 p_list_target_fields(i),
380 p_list_src_fields(i) ,
381 -- p_list_src_fields(i) ,
382 'Y',
383 '',
384 '' from dual);
385
386 insert into ams_list_src_fields_tl (
387 language,
388 source_lang,
389 source_column_meaning,
390 list_source_field_id,
391 last_update_date,
392 last_update_by,
393 creation_date,
394 created_by,
395 last_update_login
396 )
397 select
398 l.language_code,
399 userenv('LANG'),
400 p_list_src_fields(i),
401 l_list_source_field_id,
402 sysdate,
403 FND_GLOBAL.user_id,
404 sysdate,
405 FND_GLOBAL.user_id,
406 FND_GLOBAL.conc_login_id
407 from FND_LANGUAGES L
408 where L.INSTALLED_FLAG in ('I', 'B')
409 and not exists
410 (select NULL
411 from AMS_LIST_SRC_FIELDS_TL T
412 where T.LIST_source_field_ID = l_list_source_field_id
413 and T.LANGUAGE = L.LANGUAGE_CODE);
414 end loop;
415
416 l_count := 0;
417
418 OPEN c_count_import (l_list_source_type_id, l_import_type.IMPORT_TYPE);
419 FETCH c_count_import INTO l_count;
420 CLOSE c_count_import;
421
422 if l_count > 0 then
423 FND_MESSAGE.Set_Name('AMS','AMS_REQ_FIELDS_NOT_MAPPED');
424 FND_MSG_PUB.Add;
425 RAISE FND_API.g_exc_error;
426 end if;
427
428 update ams_imp_list_headers_all
429 set LIST_SOURCE_TYPE_ID = l_list_source_type_id
430 where IMPORT_LIST_HEADER_ID = p_imp_list_header_id ;
431
432 IF x_return_status = fnd_api.g_ret_sts_error THEN
433 RAISE FND_API.g_exc_error;
434 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
435 RAISE FND_API.g_exc_unexpected_error;
436 END IF;
437 IF p_commit = FND_API.g_true then
438 COMMIT WORK;
439 END IF;
440
441 FND_MSG_PUB.Count_AND_Get
442 ( p_count => x_msg_count,
443 p_data => x_msg_data,
444 p_encoded => FND_API.G_FALSE );
445 --END IF;
446 --end if;
447
448 EXCEPTION
449 WHEN FND_API.G_EXC_ERROR THEN
450 x_return_status := FND_API.g_ret_sts_error ;
451 ROLLBACK TO create_mapping;
452 FND_MSG_PUB.Count_AND_Get
453 ( p_count => x_msg_count,
454 p_data => x_msg_data,
455 p_encoded => FND_API.G_FALSE
456 );
457 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
458 x_return_status := FND_API.g_ret_sts_unexp_error ;
459 ROLLBACK TO create_mapping;
460 FND_MSG_PUB.Count_AND_Get
461 ( p_count => x_msg_count,
462 p_data => x_msg_data,
463 p_encoded => FND_API.G_FALSE
464 );
465 WHEN OTHERS THEN
466 x_return_status := FND_API.g_ret_sts_unexp_error ;
467 ROLLBACK TO create_mapping;
468 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
469 THEN
470 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
471 END IF;
472 FND_MSG_PUB.Count_AND_Get
473 ( p_count => x_msg_count,
474 p_data => x_msg_data,
475 p_encoded => FND_API.G_FALSE
476 );
477
478 END;
479 END AMS_LIST_SRC_MAPPING ;