[Home] [Help]
PACKAGE BODY: APPS.CN_EXT_TBL_MAP_PVT
Source
1 PACKAGE BODY cn_ext_tbl_map_pvt AS
2 /* $Header: cnvextbb.pls 115.6 2002/11/28 00:02:05 fting ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'cn_ext_tbl_map_pvt' ;
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvextbb.pls' ;
6 G_LAST_UPDATE_DATE DATE := sysdate ;
7 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id ;
8 G_CREATION_DATE DATE := sysdate ;
9 G_CREATED_BY NUMBER := fnd_global.user_id ;
10 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id ;
11
12
13 FUNCTION get_calc_ext_table_id RETURN NUMBER
14 IS
15 l_id NUMBER;
16 BEGIN
17 SELECT cn_calc_ext_tables_s.NEXTVAL
18 INTO l_id
19 FROM dual;
20 RETURN (l_id);
21 END;
22
23 FUNCTION get_mapping_status ( p_mapping_name VARCHAR2 ) RETURN NUMBER
24 IS
25 l_mapping_id NUMBER;
26 BEGIN
27 SELECT calc_ext_table_id
28 INTO l_mapping_id
29 FROM cn_calc_ext_tables
30 WHERE name = Ltrim(Rtrim(p_mapping_name));
31 RETURN(l_mapping_id);
32 EXCEPTION
33 WHEN no_data_found THEN
34 RETURN(0);
35 END get_mapping_status;
36
37 PROCEDURE create_external_mapping(
38 x_return_status OUT NOCOPY VARCHAR2 ,
39 x_msg_count OUT NOCOPY NUMBER ,
40 x_msg_data OUT NOCOPY VARCHAR2 ,
41 x_loading_status OUT NOCOPY VARCHAR2 ,
42 p_api_version IN NUMBER ,
43 p_init_msg_list IN VARCHAR2 ,
44 p_commit IN VARCHAR2 ,
45 p_validation_level IN VARCHAR2 ,
46 p_table_mapping_rec IN table_mapping_rec_type ,
47 p_column_mapping_tbl IN column_mapping_tbl_type ,
48 x_calc_ext_table_id OUT NOCOPY NUMBER
49 )
50 IS
51 l_api_name CONSTANT VARCHAR2(30) := 'Create_External_Mapping';
52 l_api_version CONSTANT NUMBER := 1.0;
53 x_status NUMBER;
54
55 l_calc_ext_table_id NUMBER;
56 l_rowid VARCHAR2(30);
57 BEGIN
58 -- +
59 -- Standard Start of API savepoint
60 -- +
61 SAVEPOINT create_external_mapping ;
62 --+
63 -- Standard call to check for call compatibility.
64 --+
65 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
66 p_api_version ,
67 l_api_name ,
68 G_PKG_NAME ) THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END IF;
71 --+
72 -- Initialize message list if p_init_msg_list is set to TRUE.
73 -- +
74 IF FND_API.to_Boolean( p_init_msg_list ) THEN
75 FND_MSG_PUB.initialize;
76 END IF;
77 -- +
78 -- Initialize API return status to success
79 -- +
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81 x_loading_status := 'CN_INSERTED';
82 -- +
83 -- +
84 x_status := get_mapping_status ( p_table_mapping_rec.name);
85 IF ( x_status = 0 ) THEN
86 l_calc_ext_table_id := get_calc_ext_table_id ;
87 cn_calc_ext_tables_pkg.insert_row(x_rowid => l_rowid,
88 x_calc_ext_table_id => l_calc_ext_table_id ,
89 x_schema => p_table_mapping_rec.schema ,
90 x_name => p_table_mapping_rec.name ,
91 x_description => p_table_mapping_rec.description ,
92 x_internal_table_id => p_table_mapping_rec.internal_table_id ,
93 x_external_table_id => p_table_mapping_rec.external_table_id ,
94 x_used_flag => p_table_mapping_rec.used_flag ,
95 x_external_table_name => p_table_mapping_rec.external_table_name ,
96 x_alias => p_table_mapping_rec.alias ,
97 x_attribute_category => p_table_mapping_rec.attribute_category ,
98 x_attribute1 => p_table_mapping_rec.attribute1 ,
99 x_attribute2 => p_table_mapping_rec.attribute2 ,
100 x_attribute3 => p_table_mapping_rec.attribute3 ,
101 x_attribute4 => p_table_mapping_rec.attribute4 ,
102 x_attribute5 => p_table_mapping_rec.attribute5 ,
103 x_attribute6 => p_table_mapping_rec.attribute6 ,
104 x_attribute7 => p_table_mapping_rec.attribute7 ,
105 x_attribute8 => p_table_mapping_rec.attribute8 ,
106 x_attribute9 => p_table_mapping_rec.attribute9 ,
107 x_attribute10 => p_table_mapping_rec.attribute10 ,
108 x_attribute11 => p_table_mapping_rec.attribute11 ,
109 x_attribute12 => p_table_mapping_rec.attribute12 ,
110 x_attribute13 => p_table_mapping_rec.attribute13 ,
111 x_attribute14 => p_table_mapping_rec.attribute14 ,
112 x_attribute15 => p_table_mapping_rec.attribute15 ,
113 x_creation_date => g_creation_date ,
114 x_created_by => g_created_by ,
115 x_last_update_date => g_last_update_date ,
116 x_last_updated_by => g_last_updated_by ,
117 x_last_update_login => g_last_update_login ) ;
118 ELSE
119 fnd_message.set_name('CN', 'NAME_NOT_UNIQUE');
120 RAISE fnd_api.g_exc_error;
121 END IF;
122
123 FOR i IN 1 .. p_column_mapping_tbl.COUNT LOOP
124 cn_calc_ext_tbl_dtls_pkg.insert_row
125 (x_rowid => l_rowid,
126 x_calc_ext_tbl_dtl_id => NULL,
127 x_external_column_id => p_column_mapping_tbl(i).external_column_id,
128 x_calc_ext_table_id => l_calc_ext_table_id,
129 x_internal_column_id => p_column_mapping_tbl(i).internal_column_id,
130 x_attribute_category => p_column_mapping_tbl(i).attribute_category,
131 x_attribute1 => p_column_mapping_tbl(i).attribute1,
132 x_attribute2 => p_column_mapping_tbl(i).attribute2,
133 x_attribute3 => p_column_mapping_tbl(i).attribute3,
134 x_attribute4 => p_column_mapping_tbl(i).attribute4,
135 x_attribute5 => p_column_mapping_tbl(i).attribute5,
136 x_attribute6 => p_column_mapping_tbl(i).attribute6,
137 x_attribute7 => p_column_mapping_tbl(i).attribute7,
138 x_attribute8 => p_column_mapping_tbl(i).attribute8,
139 x_attribute9 => p_column_mapping_tbl(i).attribute9,
140 x_attribute10 => p_column_mapping_tbl(i).attribute10,
141 x_attribute11 => p_column_mapping_tbl(i).attribute11,
142 x_attribute12 => p_column_mapping_tbl(i).attribute12,
143 x_attribute13 => p_column_mapping_tbl(i).attribute13,
144 x_attribute14 => p_column_mapping_tbl(i).attribute14,
145 x_attribute15 => p_column_mapping_tbl(i).attribute15,
146 x_creation_date => g_creation_date,
147 x_created_by => g_created_by,
148 x_last_update_date => g_last_update_date,
149 x_last_updated_by => g_last_updated_by,
150 x_last_update_login => g_last_update_login);
151
152 END LOOP;
153 -- +
154 -- Standard Check to p_commit
155 -- +
156 IF( FND_API.to_boolean(p_commit)) THEN
157 COMMIT WORK;
158 END IF ;
159 -- +
160 -- Standard Call to get Message count if count > 1 get message
161 -- +
162 FND_MSG_PUB.count_and_get
163 (
164 p_count => x_msg_count ,
165 p_data => x_msg_data ,
166 p_encoded => FND_API.G_FALSE
167 );
168
169 EXCEPTION
170 WHEN FND_API.G_EXC_ERROR THEN
171 ROLLBACK TO create_external_mapping ;
172 x_return_status := FND_API.G_RET_STS_ERROR ;
173 FND_MSG_PUB.Count_And_Get
174 (
175 p_count => x_msg_count ,
176 p_data => x_msg_data ,
177 p_encoded => FND_API.G_FALSE
178 );
179 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
180 ROLLBACK TO create_external_mapping ;
181 x_loading_status := 'UNEXPECTED_ERR';
182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
183 FND_MSG_PUB.Count_And_Get
184 (
185 p_count => x_msg_count ,
186 p_data => x_msg_data ,
187 p_encoded => FND_API.G_FALSE
188 );
189 WHEN OTHERS THEN
190 ROLLBACK TO create_external_mapping ;
191 x_loading_status := 'UNEXPECTED_ERR';
192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
193 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
194 THEN
195 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
196 END IF;
197 FND_MSG_PUB.Count_And_Get
198 (
199 p_count => x_msg_count ,
200 p_data => x_msg_data ,
201 p_encoded => FND_API.G_FALSE
202 );
203
204 END create_external_mapping;
205 --
206 END cn_ext_tbl_map_pvt;