[Home] [Help]
PACKAGE BODY: APPS.CN_CNSYTC_TABLES_PVT
Source
1 PACKAGE BODY cn_cnsytc_tables_pvt AS
2 /* $Header: cnsytblb.pls 120.2.12020000.2 2012/12/03 11:10:02 swpoddar ship $ */
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 col, user_synonyms syn
84 WHERE syn.synonym_name = p_table_rec.name
85 AND col.owner = syn.table_owner
86 AND col.table_name = syn.table_name
87 AND data_type IN ('CHAR','NCHAR','VARCHAR2','VARCHAR','NVARCHAR2','LONG','NUMBER','DATE');
88
89 l_col_name VARCHAR2(30);
90 l_data_type VARCHAR2(9);
91 l_data_len NUMBER(15);
92 l_new_object_id NUMBER(15);
93 l_column_data_type VARCHAR2(30);
94 l_new_object_version_no NUMBER;
95 BEGIN
96 l_new_object_version_no:=1;
97 OPEN ext_cols_cur;
98 LOOP
99 FETCH ext_cols_cur
100 INTO l_col_name,
101 l_data_type,
102 l_data_len;
103 EXIT WHEN ext_cols_cur%notfound;
104 get_new_object_id( l_new_object_id );
105 --+
106 -- Set Column_Datatype to what ever is the data type of
107 -- the native column
108 --+
109 IF l_data_type = 'NUMBER' THEN
110 l_column_data_type := 'NUMB';
111 ELSIF l_data_type = 'DATE' THEN
112 l_column_data_type := 'DATE';
113 ELSE
114 l_column_data_type := 'ALPN';
115 END IF;
116 cn_obj_tables_pkg.begin_record(
117 P_OPERATION => 'INSERT'
118 , P_OBJECT_ID => l_new_object_id
119 , P_NAME => l_col_name
120 , P_DESCRIPTION => p_table_rec.description
121 , P_DEPENDENCY_MAP_COMPLETE => 'N'
122 , P_STATUS => 'A'
123 , P_REPOSITORY_ID => p_table_rec.repository_id
124 , P_ALIAS => p_table_rec.alias
125 , P_TABLE_LEVEL => NULL
126 , P_TABLE_TYPE => NULL
127 , P_OBJECT_TYPE => 'COL'
128 , P_SCHEMA => p_table_rec.schema
129 , P_CALC_ELIGIBLE_FLAG => p_table_rec.calc_eligible_flag
130 , P_USER_NAME => l_col_name
131 , p_data_type => l_data_type
132 , p_data_length => l_data_len
133 , p_calc_formula_flag => 'N'
134 , p_table_id => p_table_rec.object_id
135 , p_column_datatype => l_column_data_type
136 , x_object_version_number => l_new_object_version_no
137 , p_org_id => p_table_rec.org_id
138 );
139 END LOOP;
140 CLOSE ext_cols_cur ;
141 END;
142 --========================================================================
143 --
144 --
145 --
146 --
147 --
148 -- x_return_status OUT VARCHAR2
149 -- x_msg_count OUT NUMBER
150 -- x_msg_data OUT VARCHAR2
151 -- x_loading_status OUT VARCHAR2
152 -- p_api_version IN NUMBER
153 -- p_init_msg_list IN VARCHAR2
154 -- p_commit IN VARCHAR2
155 -- p_validation_level IN VARCHAR2
156 -- p_table_mapping_rec IN p_table_mapping_rec_type
157 -- p_column_mapping_tbl IN p_column_mapping_tbl_type
158 --
159 --
160 --
161 --
162 --
163 --
164 --
165 --
166 --
167 --
168 --
169 --
170
171 PROCEDURE create_tables(
172 x_return_status OUT NOCOPY VARCHAR2
173 , x_msg_count OUT NOCOPY NUMBER
174 , x_msg_data OUT NOCOPY VARCHAR2
175 , x_loading_status OUT NOCOPY VARCHAR2
176 , p_api_version IN NUMBER
177 , p_init_msg_list IN VARCHAR2
178 , p_commit IN VARCHAR2
179 , p_validation_level IN VARCHAR2
180 , p_table_rec IN OUT NOCOPY table_rec_type
181 )
182 IS
183 l_api_name CONSTANT VARCHAR2(30)
184 := 'CREATE_TABLES_PVT';
185 l_api_version CONSTANT NUMBER := 1.0;
186 l_int_obj_id NUMBER(15);
187 l_ext_obj_id NUMBER(15);
188 l_repository_id NUMBER(15);
189 x_status NUMBER;
190 BEGIN
191 -- +
192 -- Standard Start of API savepoint
193 -- +
194 SAVEPOINT cn_obj_tables ;
195 --+
196 -- Standard call to check for call compatibility.
197 --+
198 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
199 p_api_version ,
200 l_api_name ,
201 G_PKG_NAME )
202 THEN
203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
204 END IF;
205 --+
206 -- Initialize message list if p_init_msg_list is set to TRUE.
207 -- +
208 IF FND_API.to_Boolean( p_init_msg_list ) THEN
209 FND_MSG_PUB.initialize;
210 END IF;
211 -- +
212 -- Initialize API return status to success
213 -- +
214 x_return_status := FND_API.G_RET_STS_SUCCESS;
215 x_loading_status := 'CN_INSERTED';
216 -- +
217 --+
218 -- +
219 -- Repository Id of EXternal Table = Repository_Id of Internal Table
220 -- Insert EXTERNAL_TABLE_NAME INTO CN_OBJECTS
221 -- AND GET THE EXTERNAL_TABLE ID FROM CN_OBJECTS
222 insert_into_cn_objects( p_table_rec,l_ext_obj_id);
223 -- +
224 -- CALL TABLE HANDLER TO INSERT INTO CN_TABLE_MAPPINGS
225 -- +
226 insert_ext_cols(p_table_rec,l_ext_obj_id);
227
228 -- +
229 -- Standard Check to p_commit
230 --+
231
232 IF( FND_API.to_boolean(p_commit)) THEN
233 COMMIT WORK;
234 END IF ;
235
236 -- +
237 -- Standard Call to get Message count if count > 1 get message
238 -- +
239 FND_MSG_PUB.count_and_get
240 (
241 p_count => x_msg_count ,
242 p_data => x_msg_data ,
243 p_encoded => FND_API.G_FALSE
244 );
245
246 EXCEPTION
247 WHEN FND_API.G_EXC_ERROR THEN
248 ROLLBACK TO cn_obj_tables ;
249 x_return_status := FND_API.G_RET_STS_ERROR ;
250 FND_MSG_PUB.Count_And_Get
251 (
252 p_count => x_msg_count ,
256 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
253 p_data => x_msg_data ,
254 p_encoded => FND_API.G_FALSE
255 );
257 ROLLBACK TO cn_obj_tables ;
258 x_loading_status := 'UNEXPECTED_ERR';
259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
260 FND_MSG_PUB.Count_And_Get
261 (
262 p_count => x_msg_count ,
263 p_data => x_msg_data ,
264 p_encoded => FND_API.G_FALSE
265 );
266 WHEN OTHERS THEN
267 ROLLBACK TO cn_obj_tables ;
268 x_loading_status := 'UNEXPECTED_ERR';
269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
270 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
271 THEN
272 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
273 END IF;
274 FND_MSG_PUB.Count_And_Get
275 (
276 p_count => x_msg_count ,
277 p_data => x_msg_data ,
278 p_encoded => FND_API.G_FALSE
279 );
280
281 END create_tables;
282 --
283 END cn_cnsytc_tables_pvt;