[Home] [Help]
PACKAGE BODY: APPS.CN_IMP_MAPS_PVT
Source
1 PACKAGE BODY CN_IMP_MAPS_PVT AS
2 -- $Header: cnvimmpb.pls 120.2 2005/08/07 23:03:33 vensrini noship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_IMP_MAPS_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvimmpb.pls';
6
7 -- Start of comments
8 -- API name : Create_Mapping
9 -- Type : Private.
10 -- Function :
11 -- Pre-reqs : None.
12 -- Parameters :
13 -- IN : p_api_version IN NUMBER Required
14 -- p_init_msg_list IN VARCHAR2 Optional
15 -- Default = FND_API.G_FALSE
16 -- p_commit IN VARCHAR2 Optional
17 -- Default = FND_API.G_FALSE
18 -- p_validation_level IN NUMBER Optional
19 -- Default = FND_API.G_VALID_LEVEL_FULL
20 -- p_imp_header_id IN NUMBER,
21 -- p_src_column_num IN NUMBER,
22 -- p_imp_map IN imp_maps_rec_type
23 -- p_source_fields IN v_Tbl_Type ,
24 -- p_target_fields IN v_Tbl_Type ,
25 -- OUT : x_return_status OUT VARCHAR2(1)
26 -- x_msg_count OUT NUMBER
27 -- x_msg_data OUT VARCHAR2(2000)
28 -- x_imp_map_id OUT NUMBER
29 -- Version : Current version 1.0
30 --
31 --
32 --
33 -- Notes : Note text
34 --
35 -- End of comments
36 PROCEDURE Create_Mapping
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 x_return_status OUT NOCOPY VARCHAR2 ,
42 x_msg_count OUT NOCOPY NUMBER ,
43 x_msg_data OUT NOCOPY VARCHAR2 ,
44 p_imp_header_id IN NUMBER,
45 p_src_column_num IN NUMBER,
46 p_imp_map IN imp_maps_rec_type,
47 p_source_fields IN map_field_tbl_type,
48 p_target_fields IN v_Tbl_Type ,
49 x_imp_map_id OUT NOCOPY NUMBER,
50 p_org_id IN NUMBER
51 ) IS
52
53
54 l_api_name CONSTANT VARCHAR2(30) := 'Create_Mapping';
55 l_api_version CONSTANT NUMBER := 1.0;
56
57 l_imp_map imp_maps_rec_type;
58 l_temp NUMBER;
59 l_target_fields v_tbl_type;
60 l_imp_map_field CN_IMP_MAP_FIELDS_PKG.imp_map_fields_rec_type;
61 l_nullable cn_objects.nullable%TYPE := 'N';
62 l_obj_type cn_objects.object_type%TYPE := 'COL';
63
64 BEGIN
65 -- Standard Start of API savepoint
66 SAVEPOINT Create_Mapping;
67 -- Standard call to check for call compatibility.
68 IF NOT FND_API.compatible_api_call
69 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
70 THEN
71 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
72 END IF;
73 -- Initialize message list if p_init_msg_list is set to TRUE.
74 IF FND_API.to_Boolean( p_init_msg_list ) THEN
75 FND_MSG_PUB.initialize;
76 END IF;
77 -- Initialize API return status to success
78 x_return_status := FND_API.G_RET_STS_SUCCESS;
79 -- API body
80 -- ----------------------
81 -- CREATE IMP_MAP
82 -- ----------------------
83 l_imp_map := p_imp_map;
84 IF p_imp_map.imp_map_id IS NULL THEN
85 -- create imp_map since not exist
86 create_imp_map
87 (p_api_version => 1.0,
88 x_return_status => x_return_status,
89 x_msg_count => x_msg_count ,
90 x_msg_data => x_msg_data ,
91 p_imp_map => p_imp_map,
92 x_imp_map_id => x_imp_map_id);
93 l_imp_map.imp_map_id := x_imp_map_id;
94 IF x_return_status <> FND_API.g_ret_sts_success THEN
95 RAISE FND_API.G_EXC_ERROR;
96 END IF;
97 ELSE
98 -- check locking mechanism
99 BEGIN
100 SELECT imp_map_id,object_version_number
101 INTO l_imp_map.imp_map_id,
102 l_imp_map.object_version_number
103 FROM cn_imp_maps
104 WHERE imp_map_id = p_imp_map.imp_map_id
105 ;
106 EXCEPTION
107 WHEN no_data_found THEN
108 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
109 THEN
110 FND_MESSAGE.SET_NAME ('CN','CN_REC_NOT_EXISTS');
111 FND_MSG_PUB.Add;
112 END IF;
113 RAISE FND_API.G_EXC_ERROR ;
114 END;
115 IF l_imp_map.object_version_number >
116 p_imp_map.object_version_number THEN
117 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
118 THEN
119 FND_MESSAGE.SET_NAME ('CN' , 'CN_RECORD_CHANGED');
120 FND_MSG_PUB.Add;
121 END IF;
122 RAISE FND_API.G_EXC_ERROR ;
123 END IF;
124 -- update cn_imp_maps object_version_number for new mapping
125 UPDATE cn_imp_maps
126 SET object_version_number = l_imp_map.object_version_number + 1
127 WHERE imp_map_id = p_imp_map.imp_map_id
128 ;
129 x_imp_map_id := p_imp_map.imp_map_id;
130 -- delete map_fields of old mapping
131 DELETE FROM cn_imp_map_fields
132 WHERE imp_map_id = p_imp_map.imp_map_id;
133 END IF;
134 -- ----------------------
135 -- CREATE MAPPING FIELDS
136 -- ----------------------
137 l_imp_map_field.imp_map_id := l_imp_map.imp_map_id;
138
139 -- get target_table_id,target_table_name
140 SELECT t.table_id,t.view_name
141 INTO l_imp_map_field.target_table_id,l_imp_map_field.target_table_name
142 FROM cn_imp_maps m, cn_import_types t
143 WHERE m.imp_map_id = l_imp_map.imp_map_id
144 AND m.import_type_code = t.import_type_code
145 ;
146
147 FOR i IN p_target_fields.first .. p_target_fields.last LOOP
148 -- get imp_map_field_id
149 SELECT cn_imp_map_fields_s.nextval
150 INTO l_imp_map_field.imp_map_field_id
151 FROM sys.dual ;
152
153 -- get target_object_id
154 SELECT object_id,name
155 INTO l_imp_map_field.target_object_id,
156 l_imp_map_field.target_column_name
157 FROM cn_objects
158 WHERE table_id = l_imp_map_field.target_table_id
159 AND object_id = p_target_fields(i)
160 AND object_type = 'COL' AND ORG_ID = p_org_id
161 ;
162 -- get source_column_name
163 l_imp_map_field.source_column := p_source_fields(i).value;
164 -- BUG 2399467
165 IF Length(p_source_fields(i).text) > 30 THEN
166 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
167 FND_MESSAGE.SET_NAME ('CN' , 'CN_IMP_MAP_HEADER_TOO_LARGE');
168 FND_MESSAGE.SET_TOKEN('COLUMN_HEADER',p_source_fields(i).text);
169 FND_MSG_PUB.Add;
170 END IF;
171 RAISE FND_API.G_EXC_ERROR ;
172 END IF;
173
174 l_imp_map_field.source_user_column := p_source_fields(i).text;
175
176 -- insert into cn_imp_map_fields
177 cn_imp_map_fields_pkg.insert_row
178 (p_imp_map_fields_rec => l_imp_map_field);
179
180 END LOOP;
181 -- ----------------------
182 -- CHECK REQUIRED FIELD
183 -- ----------------------
184 BEGIN
185 l_temp := 0 ;
186 SELECT COUNT(1)
187 INTO l_temp
188 FROM cn_objects o
189 WHERE o.table_id = l_imp_map_field.target_table_id
190 AND o.nullable = l_nullable
191 AND object_type = l_obj_type
192 AND o.name NOT IN ('IMP_LINE_ID','LAST_UPDATE_DATE','LAST_UPDATED_BY','CREATION_DATE','CREATED_BY','LAST_UPDATE_LOGIN','OBJECT_VERSION_NUMBER','IMP_HEADER_ID','STATUS_CODE','ERROR_CODE','ERROR_MSG','RECORD_NUM','IMPORT_TYPE_CODE')
193 AND NOT exists (SELECT 'X' FROM cn_imp_map_fields i
194 WHERE i.imp_map_id = l_imp_map.imp_map_id
195 AND i.target_object_id = o.object_id
196 )
197 AND o.ORG_ID = p_org_id
198 ;
199 IF l_temp > 0 THEN
200 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
201 THEN
202 FND_MESSAGE.SET_NAME ('CN','CN_IMP_REQFLD_NOT_MAP');
203 FND_MSG_PUB.Add;
204 END IF;
205 RAISE FND_API.G_EXC_ERROR ;
206 END IF;
207 EXCEPTION
208 WHEN no_data_found THEN
209 NULL;
210 END;
211
212 -- update cn_imp_headers with this mapping imp_map_id
213 UPDATE cn_imp_headers
214 SET imp_map_id = l_imp_map.imp_map_id,
215 source_column_num = p_src_column_num
216 WHERE imp_header_id = p_imp_header_id
217 ;
218
219 -- End of API body.
220
221 -- Standard check of p_commit.
222 IF FND_API.To_Boolean( p_commit ) THEN
223 COMMIT WORK;
224 END IF;
225 -- Standard call to get message count and if count is 1, get message info.
226 FND_MSG_PUB.Count_And_Get(
227 p_count => x_msg_count ,
228 p_data => x_msg_data ,
229 p_encoded => FND_API.G_FALSE
230 );
231
232 EXCEPTION
233 WHEN FND_API.G_EXC_ERROR THEN
234 ROLLBACK TO Create_Mapping ;
235 x_return_status := FND_API.G_RET_STS_ERROR ;
236 FND_MSG_PUB.Count_And_Get(
237 p_count => x_msg_count ,
238 p_data => x_msg_data ,
239 p_encoded => FND_API.G_FALSE
240 );
241
242 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
243 ROLLBACK TO Create_Mapping ;
244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
245 FND_MSG_PUB.Count_And_Get(
246 p_count => x_msg_count ,
247 p_data => x_msg_data ,
248 p_encoded => FND_API.G_FALSE
249 );
250
251 WHEN OTHERS THEN
252 ROLLBACK TO Create_Mapping ;
253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
254 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
255 THEN
256 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
257 END IF;
258 FND_MSG_PUB.Count_And_Get
259 (
260 p_count => x_msg_count ,
261 p_data => x_msg_data ,
262 p_encoded => FND_API.G_FALSE
263 );
264 END Create_Mapping;
265
266
267 -- Start of comments
268 -- API name : retrieve_Fields
269 -- Type : Private.
270 -- Function :
271 -- Pre-reqs : None.
272 -- Parameters :
273 -- IN : p_api_version IN NUMBER Required-- p_init_msg_list IN VARCHAR2 Optional
274 -- Default = FND_API.G_FALSE
275 -- p_commit IN VARCHAR2 Optional
276 -- Default = FND_API.G_FALSE
277 -- p_validation_level IN NUMBER Optional
278 -- Default = FND_API.G_VALID_LEVEL_FULL
279 -- p_imp_header_id IN NUMBER,
280 -- p_imp_map IN imp_maps_rec_type
281 -- p_source_fields IN MAP_FIELD_TBL_TYPE
282 -- p_target_fields IN MAP_FIELD_TBL_TYPE
283 -- p_mapped_fields IN MAP_FIELD_TBL_TYPE
284 -- OUT : x_return_status OUT VARCHAR2(1)
285 -- x_msg_count OUT NUMBER
286 -- x_msg_data OUT VARCHAR2(2000)
287 -- x_imp_map_id OUT NUMBER
288 -- x_map_obj_num OUT NUMBER
289 -- Version : Current version 1.0
290 --
291 --
292 -- Notes : Note text
293 --
294 -- End of comments
295 PROCEDURE retrieve_Fields
296 ( p_api_version IN NUMBER ,
297 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
298 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
299 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
300 x_return_status OUT NOCOPY VARCHAR2 ,
301 x_msg_count OUT NOCOPY NUMBER ,
302 x_msg_data OUT NOCOPY VARCHAR2 ,
303 p_imp_map_id IN NUMBER ,
304 p_import_type_code IN VARCHAR2 ,
305 x_source_fields OUT NOCOPY MAP_FIELD_TBL_TYPE,
306 x_target_fields OUT NOCOPY MAP_FIELD_TBL_TYPE ,
307 x_mapped_fields OUT NOCOPY MAP_FIELD_TBL_TYPE ,
308 x_map_obj_num OUT NOCOPY NUMBER,
309 p_org_id IN NUMBER
310 ) IS
311
312 l_api_name CONSTANT VARCHAR2(30) := 'retrieve_Fields';
313 l_api_version CONSTANT NUMBER := 1.0;
314
315 l_temp NUMBER;
316 l_table_id NUMBER;
317 l_index NUMBER;
318
319 CURSOR c_def_target_csr (c_table_id NUMBER) IS
320 SELECT object_id value,name colname,nullable,
321 Decode(Nvl(nullable,'Y'),'N','* ','') || user_name text
322 FROM cn_objects
323 WHERE table_id = c_table_id
324 AND object_type = 'COL'
325 AND name NOT IN ('IMP_LINE_ID','LAST_UPDATE_DATE','LAST_UPDATED_BY','CREATION_DATE','CREATED_BY','LAST_UPDATE_LOGIN','OBJECT_VERSION_NUMBER','IMP_HEADER_ID','STATUS_CODE','ERROR_CODE','ERROR_MSG','RECORD_NUM','IMPORT_TYPE_CODE')
326 AND ORG_ID = p_org_id
327 ORDER BY nullable,user_name
328 ;
329 l_def_target_csr c_def_target_csr%ROWTYPE;
330
331 CURSOR c_mapped_csr (c_imp_map_id NUMBER) IS
332 SELECT i.source_column || ' : ' || o.object_id m_value,
333 i.source_column || ' : ' || o.name m_colname,
334 i.source_user_column || ' : ' ||
335 Decode(Nvl(o.nullable,'Y'),'N','* ','') || o.user_name m_text,
336 i.source_user_column s_text, i.source_column s_value
337 FROM cn_imp_map_fields i, cn_objects o
338 WHERE i.imp_map_id = c_imp_map_id
339 AND i.target_object_id = o.object_id
340 AND o.ORG_ID = p_org_id
341 ;
342 l_mapped_csr c_mapped_csr%ROWTYPE;
343
344 CURSOR c_unmap_target_csr (c_table_id NUMBER,c_imp_map_id NUMBER) IS
345 SELECT o.object_id value,o.name colname,o.nullable,
346 Decode(Nvl(o.nullable,'Y'),'N','* ','') || o.user_name text
347 FROM cn_objects o
348 WHERE o.table_id = c_table_id
349 AND o.object_type = 'COL'
350 AND o.name NOT IN ('IMP_LINE_ID','LAST_UPDATE_DATE','LAST_UPDATED_BY','CREATION_DATE','CREATED_BY','LAST_UPDATE_LOGIN','OBJECT_VERSION_NUMBER','IMP_HEADER_ID','STATUS_CODE','ERROR_CODE','ERROR_MSG','RECORD_NUM','IMPORT_TYPE_CODE')
351 AND NOT exists
352 (SELECT 'X' FROM cn_imp_map_fields i
353 WHERE i.imp_map_id = c_imp_map_id
354 AND i.target_object_id = o.object_id)
355 AND o.ORG_ID = p_org_id
356 ORDER BY o.nullable, o.user_name
360 BEGIN
357 ;
358 l_unmap_target_csr c_unmap_target_csr%ROWTYPE;
359
361 -- Standard Start of API savepoint
362 SAVEPOINT retrieve_Fields;
363 -- Standard call to check for call compatibility.
364 IF NOT FND_API.compatible_api_call
365 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
366 THEN
367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
368 END IF;
369 -- Initialize message list if p_init_msg_list is set to TRUE.
370 IF FND_API.to_Boolean( p_init_msg_list ) THEN
371 FND_MSG_PUB.initialize;
372 END IF;
373 -- Initialize API return status to success
374 x_return_status := FND_API.G_RET_STS_SUCCESS;
375 -- API body
376 x_source_fields := G_MISS_MAP_FIELD_TBL;
377 x_target_fields := G_MISS_MAP_FIELD_TBL;
378 x_mapped_fields := G_MISS_MAP_FIELD_TBL;
379 -- get table_id
380 SELECT table_id INTO l_table_id
381 FROM cn_import_types
382 WHERE import_type_code = p_import_type_code
383 ;
384
385 -- if p_imp_map_id is null, get default source/target/mapped field
386 IF p_imp_map_id IS NULL THEN
387 -- get default target field
388 l_index := 1 ;
389 OPEN c_def_target_csr(l_table_id);
390 LOOP
391 FETCH c_def_target_csr INTO l_def_target_csr;
392 EXIT WHEN c_def_target_csr%notfound;
393 x_target_fields(l_index).colname := l_def_target_csr.colname;
394 x_target_fields(l_index).text := l_def_target_csr.text;
395 x_target_fields(l_index).value := l_def_target_csr.value;
396 l_index := l_index + 1;
397
398 END LOOP;
399 IF c_def_target_csr%ROWCOUNT = 0 THEN
400 x_target_fields := G_MISS_MAP_FIELD_TBL;
401 END IF;
402 CLOSE c_def_target_csr;
403 -- get mapped fields
404 x_mapped_fields := G_MISS_MAP_FIELD_TBL;
405 -- get source fields
406 x_source_fields := G_MISS_MAP_FIELD_TBL;
407 -- get imp_map obj version number : used by impHeaderEO.delete()
408 x_map_obj_num := 0;
409 ELSE
410 -- mapped existes, retrieved from db
411 l_index := 1 ;
412 OPEN c_unmap_target_csr(l_table_id,p_imp_map_id);
413 LOOP
414 FETCH c_unmap_target_csr INTO l_unmap_target_csr;
415 EXIT WHEN c_unmap_target_csr%notfound;
416 x_target_fields(l_index).colname := l_unmap_target_csr.colname;
417 x_target_fields(l_index).value := l_unmap_target_csr.value;
418 x_target_fields(l_index).text := l_unmap_target_csr.text;
419 l_index := l_index + 1;
420
421 END LOOP;
422 IF c_unmap_target_csr%ROWCOUNT = 0 THEN
423 x_target_fields := G_MISS_MAP_FIELD_TBL;
424 END IF;
425 CLOSE c_unmap_target_csr;
426 -- get mapped fields
427 l_index := 1 ;
428 OPEN c_mapped_csr(p_imp_map_id);
429 LOOP
430 FETCH c_mapped_csr INTO l_mapped_csr;
431 EXIT WHEN c_mapped_csr%notfound;
432 x_mapped_fields(l_index).colname := l_mapped_csr.m_colname;
433 x_mapped_fields(l_index).text := l_mapped_csr.m_text;
434 x_mapped_fields(l_index).value := l_mapped_csr.m_value;
435 x_source_fields(l_index).text := l_mapped_csr.s_text;
436 x_source_fields(l_index).value := l_mapped_csr.s_value;
437 l_index := l_index + 1;
438
439 END LOOP;
440 IF c_mapped_csr%ROWCOUNT = 0 THEN
441 x_mapped_fields := G_MISS_MAP_FIELD_TBL;
442 x_source_fields := G_MISS_MAP_FIELD_TBL;
443 END IF;
444 CLOSE c_mapped_csr;
445 -- get imp_map obj version number : used by impHeaderEO.delete()
446 BEGIN
447 SELECT object_version_number INTO x_map_obj_num
448 FROM cn_imp_maps
449 WHERE imp_map_id = p_imp_map_id;
450 EXCEPTION
451 WHEN no_data_found THEN
452 x_map_obj_num := 0 ;
453 END;
454 END IF;
455 -- End of API body.
456
457 -- Standard check of p_commit.
458 IF FND_API.To_Boolean( p_commit ) THEN
459 COMMIT WORK;
460 END IF;
461 -- Standard call to get message count and if count is 1, get message info.
462 FND_MSG_PUB.Count_And_Get(
463 p_count => x_msg_count ,
464 p_data => x_msg_data ,
465 p_encoded => FND_API.G_FALSE
466 );
467
468 EXCEPTION
469 WHEN FND_API.G_EXC_ERROR THEN
470 ROLLBACK TO retrieve_Fields ;
471 x_return_status := FND_API.G_RET_STS_ERROR ;
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 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
479 ROLLBACK TO retrieve_Fields ;
480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
481 FND_MSG_PUB.Count_And_Get(
482 p_count => x_msg_count ,
483 p_data => x_msg_data ,
484 p_encoded => FND_API.G_FALSE
485 );
486
487 WHEN OTHERS THEN
488 ROLLBACK TO retrieve_Fields ;
489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
490 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
491 THEN
492 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
493 END IF;
494 FND_MSG_PUB.Count_And_Get
495 (
496 p_count => x_msg_count ,
497 p_data => x_msg_data ,
501
498 p_encoded => FND_API.G_FALSE
499 );
500 END retrieve_Fields;
502
503 -- Start of comments
504 -- API name : Create_Imp_Map
505 -- Type : Private.
506 -- Function :
507 -- Pre-reqs : None.
508 -- Parameters :
509 -- IN : p_api_version IN NUMBER Required
510 -- p_init_msg_list IN VARCHAR2 Optional
511 -- Default = FND_API.G_FALSE
512 -- p_commit IN VARCHAR2 Optional
513 -- Default = FND_API.G_FALSE
514 -- p_validation_level IN NUMBER Optional
515 -- Default = FND_API.G_VALID_LEVEL_FULL
516 -- p_imp_map IN imp_maps_rec_type
517 -- OUT : x_return_status OUT VARCHAR2(1)
518 -- x_msg_count OUT NUMBER
519 -- x_msg_data OUT VARCHAR2(2000)
520 -- x_imp_map_id OUT NUMBER
521 -- Version : Current version 1.0
522 --
523 --
524 --
525 -- Notes : Note text
526 --
527 -- End of comments
528 PROCEDURE Create_Imp_Map
529 ( p_api_version IN NUMBER ,
530 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
531 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
532 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
533 x_return_status OUT NOCOPY VARCHAR2 ,
534 x_msg_count OUT NOCOPY NUMBER ,
535 x_msg_data OUT NOCOPY VARCHAR2 ,
536 p_imp_map IN imp_maps_rec_type,
537 x_imp_map_id OUT NOCOPY NUMBER
538 ) IS
539
540 l_api_name CONSTANT VARCHAR2(30) := 'Create_Imp_map';
541 l_api_version CONSTANT NUMBER := 1.0;
542
543 l_imp_map imp_maps_rec_type;
544 l_temp NUMBER;
545
546 BEGIN
547 -- Standard Start of API savepoint
548 SAVEPOINT Create_Imp_map;
549 -- Standard call to check for call compatibility.
550 IF NOT FND_API.compatible_api_call
551 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
552 THEN
553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
554 END IF;
555 -- Initialize message list if p_init_msg_list is set to TRUE.
556 IF FND_API.to_Boolean( p_init_msg_list ) THEN
557 FND_MSG_PUB.initialize;
558 END IF;
559 -- Initialize API return status to success
560 x_return_status := FND_API.G_RET_STS_SUCCESS;
561 -- API body
562 -- Check Name uniqueness
563 BEGIN
564 SELECT 1
565 INTO l_temp
566 FROM cn_imp_maps
567 WHERE name = p_imp_map.name
568 ;
569 EXCEPTION
570 WHEN no_data_found THEN
571 NULL;
572 END;
573 IF l_temp = 1 THEN
574 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
575 THEN
576 FND_MESSAGE.SET_NAME ('CN','CN_ALREADY_EXISTS');
577 FND_MSG_PUB.Add;
578 END IF;
579 RAISE FND_API.G_EXC_ERROR ;
580 END IF;
581
582 -- Call API to create cn_imp_maps
583 l_imp_map := p_imp_map ;
584
585 SELECT cn_imp_maps_s.nextval
586 INTO x_imp_map_id
587 FROM sys.dual ;
588 l_imp_map.imp_map_id := x_imp_map_id ;
589
590 cn_imp_maps_pkg.insert_row
591 (p_imp_maps_rec => l_imp_map);
592
593 -- End of API body.
594
595 -- Standard check of p_commit.
596 IF FND_API.To_Boolean( p_commit ) THEN
597 COMMIT WORK;
598 END IF;
599 -- Standard call to get message count and if count is 1, get message info.
600 FND_MSG_PUB.Count_And_Get(
601 p_count => x_msg_count ,
602 p_data => x_msg_data ,
603 p_encoded => FND_API.G_FALSE
604 );
605
606 EXCEPTION
607 WHEN FND_API.G_EXC_ERROR THEN
608 ROLLBACK TO Create_Imp_map ;
609 x_return_status := FND_API.G_RET_STS_ERROR ;
610 FND_MSG_PUB.Count_And_Get(
611 p_count => x_msg_count ,
612 p_data => x_msg_data ,
613 p_encoded => FND_API.G_FALSE
614 );
615
616 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
617 ROLLBACK TO Create_Imp_map ;
618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
619 FND_MSG_PUB.Count_And_Get(
620 p_count => x_msg_count ,
621 p_data => x_msg_data ,
622 p_encoded => FND_API.G_FALSE
623 );
624
625 WHEN OTHERS THEN
626 ROLLBACK TO Create_Imp_map ;
627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
628 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
629 THEN
630 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
631 END IF;
632 FND_MSG_PUB.Count_And_Get
633 (
634 p_count => x_msg_count ,
635 p_data => x_msg_data ,
636 p_encoded => FND_API.G_FALSE
637 );
638 END Create_Imp_map;
639
640
644 -- Function :
641 -- Start of comments
642 -- API name : Delete_Imp_map
643 -- Type : Private.
645 -- Pre-reqs : None.
646 -- Parameters :
647 -- IN : p_api_version IN NUMBER Required
648 -- p_init_msg_list IN VARCHAR2 Optional
649 -- Default = FND_API.G_FALSE
650 -- p_commit IN VARCHAR2 Optional
651 -- Default = FND_API.G_FALSE
652 -- p_validation_level IN NUMBER Optional
653 -- Default = FND_API.G_VALID_LEVEL_FULL
654 -- p_imp_map IN imp_maps_rec_type
655 -- OUT : x_return_status OUT VARCHAR2(1)
656 -- x_msg_count OUT NUMBER
657 -- x_msg_data OUT VARCHAR2(2000)
658 -- Version : Current version 1.0
659 --
660 --
661 --
662 -- Notes : Note text
663 --
664 -- End of comments
665
666 PROCEDURE Delete_Imp_Map
667 ( p_api_version IN NUMBER ,
668 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
669 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
670 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
671 x_return_status OUT NOCOPY VARCHAR2 ,
672 x_msg_count OUT NOCOPY NUMBER ,
673 x_msg_data OUT NOCOPY VARCHAR2 ,
674 p_imp_map IN imp_maps_rec_type
675 ) IS
676
677 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Imp_map';
678 l_api_version CONSTANT NUMBER := 1.0;
679
680 l_imp_map imp_maps_rec_type;
681 l_temp NUMBER;
682
683 BEGIN
684 -- Standard Start of API savepoint
685 SAVEPOINT Delete_Imp_map;
686 -- Standard call to check for call compatibility.
687 IF NOT FND_API.compatible_api_call
688 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
689 THEN
690 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
691 END IF;
692 -- Initialize message list if p_init_msg_list is set to TRUE.
693 IF FND_API.to_Boolean( p_init_msg_list ) THEN
694 FND_MSG_PUB.initialize;
695 END IF;
696 -- Initialize API return status to success
697 x_return_status := FND_API.G_RET_STS_SUCCESS;
698 -- API body
699 l_imp_map := p_imp_map ;
700 -- Check if record exist
701 BEGIN
702 SELECT imp_map_id,object_version_number
703 INTO l_imp_map.imp_map_id,
704 l_imp_map.object_version_number
705 FROM cn_imp_maps
706 WHERE imp_map_id = p_imp_map.imp_map_id
707 ;
708 EXCEPTION
709 WHEN no_data_found THEN
710 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
711 THEN
712 FND_MESSAGE.SET_NAME ('CN','CN_REC_NOT_EXISTS');
713 FND_MSG_PUB.Add;
714 END IF;
715 RAISE FND_API.G_EXC_ERROR ;
716 END;
717 -- check locking mechanism
718 IF l_imp_map.object_version_number >
719 p_imp_map.object_version_number THEN
720 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
721 THEN
722 FND_MESSAGE.SET_NAME ('CN' , 'CN_RECORD_CHANGED');
723 FND_MSG_PUB.Add;
724 END IF;
725 RAISE FND_API.G_EXC_ERROR ;
726 END IF;
727
728 -- delete imp_map_fields
729 DELETE FROM cn_imp_map_fields
730 WHERE imp_map_id = l_imp_map.imp_map_id;
731
732 IF x_return_status <> FND_API.g_ret_sts_success THEN
733 RAISE FND_API.G_EXC_ERROR;
734 END IF;
735
736 -- Call API to delete cn_imp_maps
737 cn_imp_maps_pkg.delete_row
738 (p_imp_map_id => l_imp_map.imp_map_id);
739 -- End of API body.
740
741 -- Standard check of p_commit.
742 IF FND_API.To_Boolean( p_commit ) THEN
743 COMMIT WORK;
744 END IF;
745 -- Standard call to get message count and if count is 1, get message info.
746 FND_MSG_PUB.Count_And_Get(
747 p_count => x_msg_count ,
748 p_data => x_msg_data ,
749 p_encoded => FND_API.G_FALSE
750 );
751
752 EXCEPTION
753 WHEN FND_API.G_EXC_ERROR THEN
754 ROLLBACK TO Delete_Imp_map ;
755 x_return_status := FND_API.G_RET_STS_ERROR ;
756 FND_MSG_PUB.Count_And_Get(
757 p_count => x_msg_count ,
758 p_data => x_msg_data ,
759 p_encoded => FND_API.G_FALSE
760 );
761
762 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
763 ROLLBACK TO Delete_Imp_map ;
764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
765 FND_MSG_PUB.Count_And_Get(
766 p_count => x_msg_count ,
767 p_data => x_msg_data ,
768 p_encoded => FND_API.G_FALSE
769 );
770
771 WHEN OTHERS THEN
772 ROLLBACK TO Delete_Imp_map ;
773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
774 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
775 THEN
776 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
777 END IF;
778 FND_MSG_PUB.Count_And_Get
779 (
780 p_count => x_msg_count ,
781 p_data => x_msg_data ,
782 p_encoded => FND_API.G_FALSE
783 );
784 END Delete_Imp_map;
785
786
787 END CN_IMP_MAPS_PVT;