[Home] [Help]
PACKAGE BODY: APPS.CN_CNSYTC_TABLES_PVT
Source
1 PACKAGE BODY cn_cnsytc_tables_pvt AS
2 /* $Header: cnsytblb.pls 120.2 2005/09/16 06:55:57 rramakri noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'cn_cnsytc_tables_pvt' ;
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnsytblb.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 G_ROWID VARCHAR2(30) ;
12 G_PROGRAM_TYPE VARCHAR2(30) ;
13
14 --======================================================================
15 PROCEDURE get_new_object_id ( x_object_id OUT NOCOPY NUMBER )
16 IS
17 l_object_id NUMBER(15);
18 BEGIN
19 SELECT cn_objects_s.NEXTVAL
20 INTO l_object_id
21 FROM sys.dual;
22
23 x_object_id := l_object_id;
24
25 END get_new_object_id;
26 --======================================================================
27
28 PROCEDURE insert_into_cn_objects( p_table_rec IN OUT NOCOPY table_rec_type,
29 x_ext_obj_id OUT NOCOPY NUMBER
30 )
31 IS
32 l_new_object_id NUMBER;
33 l_new_object_version_no NUMBER;
34 BEGIN
35 -- get_new_object_id( l_new_object_id );
36 l_new_object_version_no:=1;
37
38 cn_obj_tables_pkg.begin_record(
39 P_OPERATION => 'INSERT'
40 , P_OBJECT_ID => p_table_rec.object_id
41 , P_NAME => p_table_rec.name
42 , P_DESCRIPTION => p_table_rec.description
43 , P_DEPENDENCY_MAP_COMPLETE => 'N'
44 , P_STATUS => 'A'
45 , P_REPOSITORY_ID => p_table_rec.repository_id
46 , P_ALIAS => p_table_rec.alias
47 , P_TABLE_LEVEL => NULL
48 , P_TABLE_TYPE => 'T'
49 , P_OBJECT_TYPE => 'TBL'
50 , P_SCHEMA => p_table_rec.schema
51 , P_CALC_ELIGIBLE_FLAG => p_table_rec.calc_eligible_flag
52 , P_USER_NAME => p_table_rec.user_name
53 , p_data_length => NULL
54 , p_data_type => NULL
55 , p_calc_formula_flag => NULL
56 , p_table_id => NULL
57 , p_column_datatype => NULL
58 , x_object_version_number =>l_new_object_version_no
59 , p_org_id =>p_table_rec.org_id
60 );
61
62 --+
63 -- Return This Id back to Calling Form for
64 -- Bringing up columns
65 --+
66 x_ext_obj_id := p_table_rec.object_id;
67 END insert_into_cn_objects;
68 --========================================================================
69 --Change made by Sundar Venkat on 02/11/2002 in procedure insert_ext_cols
70 --Included data_type IN (CHAR,NCHAR,VARCHAR2,VARCHAR,NVARCHAR2,LONG,NUMBER,DATE)
71
72
73
74
75 PROCEDURE insert_ext_cols(p_table_rec IN OUT NOCOPY table_rec_type,
76 p_ext_tbl_id IN NUMBER
77 )
78 IS
79 CURSOR ext_cols_cur IS
80 SELECT column_name,
81 data_type,
82 data_length
83 FROM all_tab_columns
84 WHERE owner = p_table_rec.schema
85 AND table_name = p_table_rec.name
86 AND data_type IN ('CHAR','NCHAR','VARCHAR2','VARCHAR','NVARCHAR2','LONG','NUMBER','DATE');
87
88 l_col_name VARCHAR2(30);
89 l_data_type VARCHAR2(9);
90 l_data_len NUMBER(15);
91 l_new_object_id NUMBER(15);
92 l_column_data_type VARCHAR2(30);
93 l_new_object_version_no NUMBER;
94 BEGIN
95 l_new_object_version_no:=1;
96 OPEN ext_cols_cur;
97 LOOP
98 FETCH ext_cols_cur
99 INTO l_col_name,
100 l_data_type,
101 l_data_len;
102 EXIT WHEN ext_cols_cur%notfound;
103 get_new_object_id( l_new_object_id );
104 --+
105 -- Set Column_Datatype to what ever is the data type of
106 -- the native column
107 --+
108 IF l_data_type = 'NUMBER' THEN
109 l_column_data_type := 'NUMB';
110 ELSIF l_data_type = 'DATE' THEN
111 l_column_data_type := 'DATE';
112 ELSE
113 l_column_data_type := 'ALPN';
114 END IF;
115 cn_obj_tables_pkg.begin_record(
116 P_OPERATION => 'INSERT'
117 , P_OBJECT_ID => l_new_object_id
118 , P_NAME => l_col_name
119 , P_DESCRIPTION => p_table_rec.description
120 , P_DEPENDENCY_MAP_COMPLETE => 'N'
121 , P_STATUS => 'A'
122 , P_REPOSITORY_ID => p_table_rec.repository_id
123 , P_ALIAS => p_table_rec.alias
124 , P_TABLE_LEVEL => NULL
125 , P_TABLE_TYPE => NULL
126 , P_OBJECT_TYPE => 'COL'
127 , P_SCHEMA => p_table_rec.schema
128 , P_CALC_ELIGIBLE_FLAG => p_table_rec.calc_eligible_flag
129 , P_USER_NAME => l_col_name
130 , p_data_type => l_data_type
131 , p_data_length => l_data_len
132 , p_calc_formula_flag => 'N'
133 , p_table_id => p_table_rec.object_id
134 , p_column_datatype => l_column_data_type
135 , x_object_version_number => l_new_object_version_no
136 , p_org_id => p_table_rec.org_id
137 );
138 END LOOP;
139 CLOSE ext_cols_cur ;
140 END;
141 --========================================================================
142 --
143 --
144 --
145 --
146 --
147 -- x_return_status OUT VARCHAR2
148 -- x_msg_count OUT NUMBER
149 -- x_msg_data OUT VARCHAR2
150 -- x_loading_status OUT VARCHAR2
151 -- p_api_version IN NUMBER
152 -- p_init_msg_list IN VARCHAR2
153 -- p_commit IN VARCHAR2
154 -- p_validation_level IN VARCHAR2
155 -- p_table_mapping_rec IN p_table_mapping_rec_type
156 -- p_column_mapping_tbl IN p_column_mapping_tbl_type
157 --
158 --
159 --
160 --
161 --
162 --
163 --
164 --
165 --
166 --
167 --
168 --
169
170 PROCEDURE create_tables(
171 x_return_status OUT NOCOPY VARCHAR2
172 , x_msg_count OUT NOCOPY NUMBER
173 , x_msg_data OUT NOCOPY VARCHAR2
174 , x_loading_status OUT NOCOPY VARCHAR2
175 , p_api_version IN NUMBER
176 , p_init_msg_list IN VARCHAR2
177 , p_commit IN VARCHAR2
178 , p_validation_level IN VARCHAR2
179 , p_table_rec IN OUT NOCOPY table_rec_type
180 )
181 IS
182 l_api_name CONSTANT VARCHAR2(30)
183 := 'CREATE_TABLES_PVT';
184 l_api_version CONSTANT NUMBER := 1.0;
185 l_int_obj_id NUMBER(15);
186 l_ext_obj_id NUMBER(15);
187 l_repository_id NUMBER(15);
188 x_status NUMBER;
189 BEGIN
190 -- +
191 -- Standard Start of API savepoint
192 -- +
193 SAVEPOINT cn_obj_tables ;
194 --+
195 -- Standard call to check for call compatibility.
196 --+
197 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
198 p_api_version ,
199 l_api_name ,
200 G_PKG_NAME )
201 THEN
202 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
203 END IF;
204 --+
205 -- Initialize message list if p_init_msg_list is set to TRUE.
206 -- +
207 IF FND_API.to_Boolean( p_init_msg_list ) THEN
208 FND_MSG_PUB.initialize;
209 END IF;
210 -- +
211 -- Initialize API return status to success
212 -- +
213 x_return_status := FND_API.G_RET_STS_SUCCESS;
214 x_loading_status := 'CN_INSERTED';
215 -- +
216 --+
217 -- +
218 -- Repository Id of EXternal Table = Repository_Id of Internal Table
219 -- Insert EXTERNAL_TABLE_NAME INTO CN_OBJECTS
220 -- AND GET THE EXTERNAL_TABLE ID FROM CN_OBJECTS
221 insert_into_cn_objects( p_table_rec,l_ext_obj_id);
222 -- +
223 -- CALL TABLE HANDLER TO INSERT INTO CN_TABLE_MAPPINGS
224 -- +
225 insert_ext_cols(p_table_rec,l_ext_obj_id);
226
227 -- +
228 -- Standard Check to p_commit
229 --+
230
231 IF( FND_API.to_boolean(p_commit)) THEN
232 COMMIT WORK;
233 END IF ;
234
235 -- +
236 -- Standard Call to get Message count if count > 1 get message
237 -- +
238 FND_MSG_PUB.count_and_get
239 (
240 p_count => x_msg_count ,
241 p_data => x_msg_data ,
242 p_encoded => FND_API.G_FALSE
243 );
244
245 EXCEPTION
246 WHEN FND_API.G_EXC_ERROR THEN
247 ROLLBACK TO cn_obj_tables ;
248 x_return_status := FND_API.G_RET_STS_ERROR ;
249 FND_MSG_PUB.Count_And_Get
250 (
251 p_count => x_msg_count ,
252 p_data => x_msg_data ,
253 p_encoded => FND_API.G_FALSE
254 );
255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
256 ROLLBACK TO cn_obj_tables ;
257 x_loading_status := 'UNEXPECTED_ERR';
258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
259 FND_MSG_PUB.Count_And_Get
260 (
261 p_count => x_msg_count ,
262 p_data => x_msg_data ,
263 p_encoded => FND_API.G_FALSE
264 );
265 WHEN OTHERS THEN
266 ROLLBACK TO cn_obj_tables ;
267 x_loading_status := 'UNEXPECTED_ERR';
268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
269 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
270 THEN
271 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
272 END IF;
273 FND_MSG_PUB.Count_And_Get
274 (
275 p_count => x_msg_count ,
276 p_data => x_msg_data ,
277 p_encoded => FND_API.G_FALSE
278 );
279
280 END create_tables;
281 --
282 END cn_cnsytc_tables_pvt;