[Home] [Help]
PACKAGE BODY: APPS.CN_SYS_TABLES_PVT
Source
1 PACKAGE body CN_SYS_TABLES_PVT AS
2 --$Header: cnvsytbb.pls 120.3.12020000.2 2012/12/03 11:12:03 swpoddar ship $
3 --Changed g_pkg_name to cn_sys_tables_pvt
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CN_SYS_TABLES_PVT';
5
6 --{{{ check_table_rec
7 procedure check_table_rec(p_table_rec IN table_rec_type,
8 p_operation IN varchar2) IS
9 CURSOR l_schema_csr IS
10 SELECT username
11 FROM all_users
12 WHERE username NOT IN
13 ('SYS','SYSTEM', 'APPLSYS', 'APPLSYSPUB', 'APPS_READ_ONLY')
14 AND username = p_table_rec.schema;
15
16 CURSOR l_table_csr IS
17 SELECT object_name
18 FROM all_objects
19 WHERE owner = p_table_rec.schema
20 AND object_type IN ('TABLE','VIEW')
21 AND object_name NOT IN
22 ( select name from cn_obj_tables_v
23 where org_id=p_table_rec.org_id)
24 and object_name = p_table_rec.name;
25
26 l_schema varchar2(30);
27 l_table varchar2(30);
28 BEGIN
29
30 IF (p_operation = 'INSERT') THEN
31 OPEN l_schema_csr;
32 FETCH l_schema_csr INTO l_schema;
33 CLOSE l_schema_csr;
34
35 IF (l_schema is null) THEN
36 fnd_message.set_name('CN', 'CN_TBL_NO_SUCH_SCH');
37 fnd_msg_pub.ADD;
38 RAISE fnd_api.g_exc_error;
39 END IF;
40
41
42 OPEN l_table_csr;
43 FETCH l_table_csr INTO l_table;
44 CLOSE l_table_csr;
45
46 IF (l_table is null) THEN
47 fnd_message.set_name('CN', 'CN_TBL_NO_SUCH_TBL');
48 fnd_msg_pub.ADD;
49 RAISE fnd_api.g_exc_error;
50 END IF;
51 END IF;
52
53 IF (p_table_rec.calc_eligible_flag <> 'Y' AND
54 p_table_rec.calc_eligible_flag <> 'C' AND
55 p_table_rec.calc_eligible_flag <> 'N') THEN
56 fnd_message.set_name('CN', 'CN_TBL_INC_CALC_FLAG');
57 fnd_msg_pub.ADD;
58 RAISE fnd_api.g_exc_error;
59 END IF;
60 END check_table_rec;
61 --}}}
62
63 --{{{ Create_Table
64 -- Start of comments
65 -- API name : Create_Table
66 -- Type : Private.
67 -- Function : Create the information for the table in cn_objects
68 -- Also create the columns associated with the table
69 -- Pre-reqs : None.
70 -- Parameters :
71 -- IN : p_api_version IN NUMBER Required
72 -- p_init_msg_list IN VARCHAR2 Optional
73 -- Default = FND_API.G_FALSE
74 -- p_commit IN VARCHAR2 Optional
75 -- Default = FND_API.G_FALSE
76 -- p_validation_level IN NUMBER Optional
77 -- Default = FND_API.G_VALID_LEVEL_FULL
78 -- p_table_rec IN table_rec_type Required
79 -- OUT : x_return_status OUT VARCHAR2(1)
80 -- x_msg_count OUT NUMBER
81 -- x_msg_data OUT VARCHAR2(2000)
82 -- Version : Current version 1.0
83 -- Changed....
84 -- Initial version 1.0
85 --
86 -- Notes : Note text
87 --
88 -- End of comments
89 PROCEDURE Create_Table
90 (p_api_version IN NUMBER ,
91 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
92 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
93 p_validation_level IN NUMBER :=
94 FND_API.G_VALID_LEVEL_FULL ,
95 p_table_rec IN OUT NOCOPY table_rec_type ,
96 x_return_status OUT NOCOPY VARCHAR2 ,
97 x_msg_count OUT NOCOPY NUMBER ,
98 x_msg_data OUT NOCOPY VARCHAR2 ) IS
99 l_api_name CONSTANT VARCHAR2(30)
100 := 'Create_Table';
101 l_api_version CONSTANT NUMBER := 1.0;
102
103 l_repository_id number := 0;
104 l_alias varchar2(80);
105 l_count number := 0;
106 l_object_id number := 0;
107
108 G_LAST_UPDATE_DATE DATE := Sysdate;
109 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
110 G_CREATION_DATE DATE := Sysdate;
111 G_CREATED_BY NUMBER := fnd_global.user_id;
112 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
113
114 CURSOR l_repository_csr IS
115 SELECT repository_id
116 FROM cn_repositories
117 WHERE repository_id > 0
118 AND org_id=p_table_rec.org_id
119 AND application_type = 'CN';
120
121 l_loading_status varchar2(80);
122 l_table_rec cn_cnsytc_tables_pvt.table_rec_type;
123 BEGIN
124 -- Standard Start of API savepoint
125 SAVEPOINT create_table_pvt;
126 -- Standard call to check for call compatibility.
127 IF NOT FND_API.Compatible_API_Call
128 (l_api_version ,
129 p_api_version ,
130 l_api_name ,
131 G_PKG_NAME )
132 THEN
133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134 END IF;
135 -- Initialize message list if p_init_msg_list is set to TRUE.
136 IF FND_API.to_Boolean( p_init_msg_list ) THEN
137 FND_MSG_PUB.initialize;
138 END IF;
139 -- Initialize API return status to success
140 x_return_status := FND_API.G_RET_STS_SUCCESS;
141 -- API body
142 OPEN l_repository_csr;
143 FETCH l_repository_csr INTO l_repository_id;
144 CLOSE l_repository_csr;
145
146 check_table_rec(p_table_rec, 'INSERT');
147
148 if (p_table_rec.name is not null) then
149 l_alias := substr(p_table_rec.name, 1, 1);
150 l_count := instr(p_table_rec.name, '_', 1, 1);
151
152 while (l_count > 0) loop
153 l_alias := l_alias || substr(p_table_rec.name, l_count + 1, 1);
154 l_count := instr(p_table_rec.name, '_', l_count + 1, 1);
155 end loop;
156
157 select cn_objects_s1.nextval
158 into l_count
159 from dual;
160
161 l_alias := l_alias || l_count;
162 end if;
163
164 SELECT nvl(p_table_rec.object_id,cn_objects_s.nextval)
165 INTO l_object_id
166 FROM dual;
167
168 l_table_rec.object_id := l_object_id ;
169 l_table_rec.name := p_table_rec.name ;
170 l_table_rec.description := p_table_rec.description ;
171 l_table_rec.status := 'A' ;
172 l_table_rec.repository_id := l_repository_id ;
173 l_table_rec.alias := l_alias ;
174 l_table_rec.table_level := NULL ;
175 l_table_rec.table_type := 'T' ;
176 l_table_rec.object_type := 'TBL' ;
177 l_table_rec.schema := p_table_rec.schema ;
178 l_table_rec.calc_eligible_flag := p_table_rec.calc_eligible_flag;
179 l_table_rec.user_name := p_table_rec.user_name ;
180 l_table_rec.object_version_number := p_table_rec.object_version_number ;
181 l_table_rec.org_id := p_table_rec.org_id ;
182
183 p_table_rec.alias:=l_alias;
184 cn_cnsytc_tables_pvt.create_tables
185 ( x_return_status => x_return_status
186 , x_msg_count => x_msg_count
187 , x_msg_data => x_msg_data
188 , x_loading_status => l_loading_status
189 , p_api_version => l_api_version
190 , p_init_msg_list => p_init_msg_list
191 , p_commit => p_commit
192 , p_validation_level => p_validation_level
193 , p_table_rec => l_table_rec);
194
195 -- End of API body.
196 -- Standard check of p_commit.
197 IF FND_API.To_Boolean( p_commit ) THEN
198 COMMIT WORK;
199 END IF;
200 -- Standard call to get message count and if count is 1, get message info.
201 FND_MSG_PUB.Count_And_Get
202 (p_count => x_msg_count ,
203 p_data => x_msg_data ,
204 p_encoded => FND_API.G_FALSE );
205 EXCEPTION
206 WHEN FND_API.G_EXC_ERROR THEN
207 ROLLBACK TO create_table_pvt;
208 x_return_status := FND_API.G_RET_STS_ERROR ;
209 FND_MSG_PUB.Count_And_Get
210 (p_count => x_msg_count ,
211 p_data => x_msg_data ,
212 p_encoded => FND_API.G_FALSE );
213 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
214 ROLLBACK TO create_table_pvt;
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
216 FND_MSG_PUB.Count_And_Get
217 (p_count => x_msg_count ,
218 p_data => x_msg_data ,
219 p_encoded => FND_API.G_FALSE );
220 WHEN OTHERS THEN
221 ROLLBACK TO create_table_pvt;
222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
223 IF FND_MSG_PUB.Check_Msg_Level
224 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
225 THEN
226 FND_MSG_PUB.Add_Exc_Msg
227 (G_PKG_NAME ,
228 l_api_name );
229 END IF;
230 FND_MSG_PUB.Count_And_Get
231 (p_count => x_msg_count ,
232 p_data => x_msg_data ,
233 p_encoded => FND_API.G_FALSE );
234 END Create_Table;
235 --}}}
236
237 --{{{ Update_Table
238 -- Start of comments
239 -- API name : Update_Table
240 -- Type : Private.
241 -- Function : Update table information
242 -- Pre-reqs : None.
243 -- Parameters :
244 -- IN : p_api_version IN NUMBER Required
245 -- p_init_msg_list IN VARCHAR2 Optional
246 -- Default = FND_API.G_FALSE
247 -- p_commit IN VARCHAR2 Optional
248 -- Default = FND_API.G_FALSE
249 -- p_validation_level IN NUMBER Optional
250 -- Default = FND_API.G_VALID_LEVEL_FULL
251 -- p_table_rec IN table_rec_type Required
252 -- OUT : x_return_status OUT VARCHAR2(1)
253 -- x_msg_count OUT NUMBER
254 -- x_msg_data OUT VARCHAR2(2000)
255 -- Version : Current version 1.0
256 -- Initial version 1.0
257 --
258 -- Notes : Note text
259 --
260 -- End of comments
261
262
263 PROCEDURE Update_Table
264 (p_api_version IN NUMBER ,
265 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
266 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
267 p_validation_level IN NUMBER :=
268 FND_API.G_VALID_LEVEL_FULL ,
269 p_table_rec IN OUT NOCOPY table_rec_type ,
270 x_return_status OUT NOCOPY VARCHAR2 ,
271 x_msg_count OUT NOCOPY NUMBER ,
272 x_msg_data OUT NOCOPY VARCHAR2 ) IS
273 l_api_name CONSTANT VARCHAR2(30)
274 := 'Update_Table';
275 l_api_version CONSTANT NUMBER := 1.0;
276 l_row cn_obj_tables_v%ROWTYPE;
277 l_repository_id number;
278
279 G_LAST_UPDATE_DATE DATE := Sysdate;
280 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
281 G_CREATION_DATE DATE := Sysdate;
282 G_CREATED_BY NUMBER := fnd_global.user_id;
283 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
284
285 CURSOR l_old_row_csr IS
286 SELECT *
287 FROM cn_obj_tables_v
288 WHERE table_id = p_table_rec.object_id
289 and org_id=p_table_rec.ORG_ID;
290
291 CURSOR l_repository_csr IS
292 SELECT repository_id
293 FROM cn_repositories
294 WHERE repository_id > 0
295 and org_id=p_table_rec.ORG_ID
296 AND application_type = 'CN';
297 BEGIN
298 -- Standard Start of API savepoint
299 SAVEPOINT update_table_pvt;
300 -- Standard call to check for call compatibility.
301 IF NOT FND_API.Compatible_API_Call
302 (l_api_version ,
303 p_api_version ,
304 l_api_name ,
305 G_PKG_NAME )
306 THEN
307 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
308 END IF;
309 -- Initialize message list if p_init_msg_list is set to TRUE.
310 IF FND_API.to_Boolean( p_init_msg_list ) THEN
311 FND_MSG_PUB.initialize;
312 END IF;
313 -- Initialize API return status to success
314 x_return_status := FND_API.G_RET_STS_SUCCESS;
315 -- API body
316 /* seeded tables can be updated
317 if (p_table_rec.object_id < 0) then
318 fnd_message.set_name('CN', 'CN_SD_TBL_NO_UPD');
319 fnd_msg_pub.ADD;
320 RAISE fnd_api.g_exc_error;
321 end if;
322 */
323
324
325
326 OPEN l_old_row_csr;
327 FETCH l_old_row_csr INTO l_row;
328 CLOSE l_old_row_csr;
329
330 check_table_rec(p_table_rec, 'UPDATE');
331
332 if (l_row.schema <> p_table_rec.schema OR
333 l_row.name <> p_table_rec.name OR
334 l_row.ALIAS <> p_table_rec.ALIAS) then
335 fnd_message.set_name('CN', 'CN_TBL_ATTR_NO_UPD');
336 fnd_msg_pub.ADD;
337 RAISE fnd_api.g_exc_error;
338 end if;
339
340 OPEN l_repository_csr;
341 FETCH l_repository_csr INTO l_repository_id;
342 CLOSE l_repository_csr;
343
344 cn_obj_tables_pkg.begin_record(
345 P_OPERATION => 'UPDATE'
346 , P_OBJECT_ID => p_table_rec.object_id
347 , P_NAME => l_row.name
348 , P_DESCRIPTION => p_table_rec.description
349 , P_DEPENDENCY_MAP_COMPLETE => 'N'
350 , P_STATUS => 'A'
351 , P_REPOSITORY_ID => l_repository_id
352 , P_ALIAS => l_row.alias
353 , P_TABLE_LEVEL => NULL
354 , P_TABLE_TYPE => 'T'
355 , P_OBJECT_TYPE => 'TBL'
356 , P_SCHEMA => l_row.schema
357 , P_CALC_ELIGIBLE_FLAG => p_table_rec.calc_eligible_flag
358 , P_USER_NAME => p_table_rec.user_name
359 , p_data_length => NULL
360 , p_data_type => NULL
361 , p_calc_formula_flag => NULL
362 , p_table_id => NULL
363 , p_column_datatype => NULL
364 , x_object_version_number =>p_table_rec.object_version_number
365 , p_org_id =>p_table_rec.ORG_ID
366 );
367
368
369 -- End of API body.
370 -- Standard check of p_commit.
371 IF FND_API.To_Boolean( p_commit ) THEN
372 COMMIT WORK;
373 END IF;
374 -- Standard call to get message count and if count is 1, get message info.
375 FND_MSG_PUB.Count_And_Get
376 (p_count => x_msg_count ,
377 p_data => x_msg_data ,
378 p_encoded => FND_API.G_FALSE );
379 EXCEPTION
380 WHEN FND_API.G_EXC_ERROR THEN
381 ROLLBACK TO update_table_pvt;
382 x_return_status := FND_API.G_RET_STS_ERROR ;
383 FND_MSG_PUB.Count_And_Get
384 (p_count => x_msg_count ,
385 p_data => x_msg_data ,
386 p_encoded => FND_API.G_FALSE );
387 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
388 ROLLBACK TO update_table_pvt;
389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
390 FND_MSG_PUB.Count_And_Get
391 (p_count => x_msg_count ,
392 p_data => x_msg_data ,
393 p_encoded => FND_API.G_FALSE );
394 WHEN OTHERS THEN
395 ROLLBACK TO update_table_pvt;
396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
397 IF FND_MSG_PUB.Check_Msg_Level
398 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
399 THEN
400 FND_MSG_PUB.Add_Exc_Msg
401 (G_PKG_NAME ,
402 l_api_name );
403 END IF;
404 FND_MSG_PUB.Count_And_Get
405 (p_count => x_msg_count ,
406 p_data => x_msg_data ,
407 p_encoded => FND_API.G_FALSE );
408 END Update_Table;
409 --}}}
410
411
412
413
414 --{{{ Delete_Table
415 -- Start of comments
416 -- API name : Delete_Table
417 -- Type : Private.
418 -- Function : Delete table information
419 -- Pre-reqs : None.
420 -- Parameters :
421 -- IN : p_api_version IN NUMBER Required
422 -- p_init_msg_list IN VARCHAR2 Optional
423 -- Default = FND_API.G_FALSE
424 -- p_commit IN VARCHAR2 Optional
425 -- Default = FND_API.G_FALSE
426 -- p_validation_level IN NUMBER Optional
427 -- Default = FND_API.G_VALID_LEVEL_FULL
428 -- p_table_rec IN table_rec_type Required
429 -- OUT : x_return_status OUT VARCHAR2(1)
430 -- x_msg_count OUT NUMBER
431 -- x_msg_data OUT VARCHAR2(2000)
432 -- Version : Current version 1.0
433 -- Initial version 1.0
434 --
435 -- Notes : Note text
436 --
437 -- End of comments
438 PROCEDURE Delete_Table
439 (p_api_version IN NUMBER ,
440 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
441 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
442 p_validation_level IN NUMBER :=
443 FND_API.G_VALID_LEVEL_FULL ,
444 p_table_rec IN table_rec_type ,
445 x_return_status OUT NOCOPY VARCHAR2 ,
446 x_msg_count OUT NOCOPY NUMBER ,
447 x_msg_data OUT NOCOPY VARCHAR2 ) IS
448 l_api_name CONSTANT VARCHAR2(30)
449 := 'Delete_Table';
450 l_api_version CONSTANT NUMBER := 1.0;
451 G_LAST_UPDATE_DATE DATE := Sysdate;
452 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
453 G_CREATION_DATE DATE := Sysdate;
454 G_CREATED_BY NUMBER := fnd_global.user_id;
455 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
456
457 -- By Hithanki For Bug Fix : 2698989
458 -------
459 l_dest_count NUMBER := 0;
460 l_sorc_count NUMBER := 0;
461 -------
462
463 BEGIN
464 -- Standard Start of API savepoint
465 SAVEPOINT delete_table_pvt;
466 -- Standard call to check for call compatibility.
467 IF NOT FND_API.Compatible_API_Call
468 (l_api_version ,
469 p_api_version ,
470 l_api_name ,
471 G_PKG_NAME )
472 THEN
473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
474 END IF;
475 -- Initialize message list if p_init_msg_list is set to TRUE.
476 IF FND_API.to_Boolean( p_init_msg_list ) THEN
477 FND_MSG_PUB.initialize;
478 END IF;
479 -- Initialize API return status to success
480 x_return_status := FND_API.G_RET_STS_SUCCESS;
481 -- API body
482 if (p_table_rec.object_id < 0) then
483 fnd_message.set_name('CN', 'CN_SD_TBL_NO_DEL');
484 fnd_msg_pub.ADD;
485 RAISE fnd_api.g_exc_error;
486 end if;
487
488 -- By Hithanki For Bug Fix : 2698989
489 -------
490 --
491 -- Check IF Table is used as a Part Of Transaction Source.
492 --
493 SELECT COUNT(*)
494 INTO l_dest_count
495 FROM cn_table_maps_all
496 WHERE source_table_id = p_table_rec.object_id
497 and org_id=p_table_rec.org_id;
498
499 SELECT COUNT(*)
500 INTO l_sorc_count
501 FROM cn_table_maps_all
502 WHERE destination_table_id = p_table_rec.object_id
503 and org_id=p_table_rec.org_id;
504 --
505 -- IF Yes, Do Not Allow User To Delete That Table
506 --
507 IF ( l_dest_count > 0 OR l_sorc_count > 0 )
508 THEN fnd_message.set_name('CN','CN_TBL_MAP_EXIST');
509 fnd_msg_pub.ADD;
510 RAISE fnd_api.g_exc_error;
511 END IF;
512 -------
513
514 --no table handler exists
515 --add delete table handler later
516
517 --first delete all columns
518 DELETE FROM cn_obj_columns_v
519 WHERE table_id = p_table_rec.object_id;
520
521 --then delete the table itself
522 DELETE FROM cn_obj_tables_v
523 WHERE table_id = p_table_rec.object_id;
524
525 -- End of API body.
526 -- Standard check of p_commit.
527 IF FND_API.To_Boolean( p_commit ) THEN
528 COMMIT WORK;
529 END IF;
530 -- Standard call to get message count and if count is 1, get message info.
531 FND_MSG_PUB.Count_And_Get
532 (p_count => x_msg_count ,
533 p_data => x_msg_data ,
534 p_encoded => FND_API.G_FALSE );
535 EXCEPTION
536 WHEN FND_API.G_EXC_ERROR THEN
537 ROLLBACK TO delete_table_pvt;
538 x_return_status := FND_API.G_RET_STS_ERROR ;
539 FND_MSG_PUB.Count_And_Get
540 (p_count => x_msg_count ,
541 p_data => x_msg_data ,
542 p_encoded => FND_API.G_FALSE );
543 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
544 ROLLBACK TO delete_table_pvt;
545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
546 FND_MSG_PUB.Count_And_Get
547 (p_count => x_msg_count ,
548 p_data => x_msg_data ,
549 p_encoded => FND_API.G_FALSE );
550 WHEN OTHERS THEN
551 ROLLBACK TO delete_table_pvt;
552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
553 IF FND_MSG_PUB.Check_Msg_Level
554 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
555 THEN
556 FND_MSG_PUB.Add_Exc_Msg
557 (G_PKG_NAME ,
558 l_api_name );
559 END IF;
560 FND_MSG_PUB.Count_And_Get
561 (p_count => x_msg_count ,
562 p_data => x_msg_data ,
563 p_encoded => FND_API.G_FALSE );
564 END Delete_Table;
565 --}}}
566
567
568
569
570 --{{{ Update_Column
571 -- Start of comments
572 -- API name : Update_Column
573 -- Type : Private.
574 -- Function : Update column information
575 --
576 -- Pre-reqs : None.
577 -- Parameters :
578 -- IN : p_api_version IN NUMBER Required
579 -- p_init_msg_list IN VARCHAR2 Optional
580 -- Default = FND_API.G_FALSE
581 -- p_commit IN VARCHAR2 Optional
582 -- Default = FND_API.G_FALSE
583 -- p_validation_level IN NUMBER Optional
584 -- Default = FND_API.G_VALID_LEVEL_FULL
585 -- p_column_rec IN column_rec_type Required
586 -- OUT : x_return_status OUT VARCHAR2(1)
587 -- x_msg_count OUT NUMBER
588 -- x_msg_data OUT VARCHAR2(2000)
589 -- Version : Current version 1.0
590 -- Changed....
591 -- Initial version 1.0
592 --
593 -- Notes : Note text
594 --
595 -- End of comments
596
597 PROCEDURE Update_Column
598 (p_api_version IN NUMBER ,
599 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
600 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
601 p_validation_level IN NUMBER :=
602 FND_API.G_VALID_LEVEL_FULL ,
603 p_column_rec IN column_rec_type ,
604 x_return_status OUT NOCOPY VARCHAR2 ,
605 x_msg_count OUT NOCOPY NUMBER ,
606 x_msg_data OUT NOCOPY VARCHAR2 ) IS
607 l_api_name CONSTANT VARCHAR2(30)
608 := 'Update_Column';
609 l_api_version CONSTANT NUMBER := 1.0;
610
611 l_dimension_id number := 0;
612 l_table_id number := 0;
613 l_table_name varchar2(30);
614 l_dim_value_ctr number := 0;
615
616 G_LAST_UPDATE_DATE DATE := Sysdate;
617 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
618 G_CREATION_DATE DATE := Sysdate;
619 G_CREATED_BY NUMBER := fnd_global.user_id;
620 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
621
622 CURSOR l_table_csr IS
623 SELECT cotv.table_id, cotv.name
624 FROM cn_obj_columns_v cocv, cn_obj_tables_v cotv
625 WHERE cocv.column_id= p_column_rec.object_id
626 AND cocv.table_id = cotv.table_id
627 AND cocv.org_id=p_column_rec.org_id AND
628 cocv.org_id=cotv.org_id;
629
630 CURSOR l_dim_value_csr IS
631 SELECT count(user_column_name)
632 FROM cn_obj_columns_v
633 WHERE table_id = l_table_id
634 AND user_column_name = 'Y'
635 AND org_id=p_column_rec.org_id
636 AND column_id <> p_column_rec.object_id;
637
638 CURSOR l_dim_csr IS
639 SELECT d.dimension_id
640 FROM cn_dimensions d, cn_dimension_tables_v dt
641 WHERE d.dimension_id = dt.dimension_id
642 AND org_id=p_column_rec.org_id
643 AND upper(dt.table_name) = l_table_name;
644
645 CURSOR l_rule_csr( p_object_id NUMBER) IS
646 SELECT distinct ruleset_id
647 FROM cn_attribute_rules
648 WHERE column_id = p_object_id
649 AND org_id=p_column_rec.org_id
650 and dimension_hierarchy_id is null;
651
652 CURSOR l_col_csr ( p_object_id number ) IS
653 SELECT object_id, column_datatype,org_id
654 FROM cn_objects
655 WHERE object_id = p_object_id
656 AND org_id=p_column_rec.org_id
657 AND table_id = -11803;
658
659 l_col_rec l_col_csr%ROWTYPE;
660
661 BEGIN
662 -- Standard Start of API savepoint
663 SAVEPOINT update_column_pvt;
664 -- Standard call to check for call compatibility.
665 IF NOT FND_API.Compatible_API_Call
666 (l_api_version ,
667 p_api_version ,
668 l_api_name ,
669 G_PKG_NAME )
670 THEN
671 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
672 END IF;
673 -- Initialize message list if p_init_msg_list is set to TRUE.
674 IF FND_API.to_Boolean( p_init_msg_list ) THEN
675 FND_MSG_PUB.initialize;
676 END IF;
677 -- Initialize API return status to success
678 x_return_status := FND_API.G_RET_STS_SUCCESS;
679 -- API body
680 -- first check if both pk and fk are Y
681 if (p_column_rec.primary_key = 'Y' and p_column_rec.foreign_key = 'Y') then
682 fnd_message.set_name('CN', 'CN_TBL_PK_FK_ERR');
683 fnd_msg_pub.ADD;
684 RAISE fnd_api.g_exc_error;
685 end if;
686
687 --next check if dim is correct
688 OPEN l_table_csr;
689 FETCH l_table_csr INTO l_table_id, l_table_name;
690 CLOSE l_table_csr;
691
692 if (p_column_rec.primary_key = 'Y' and
693 p_column_rec.dimension_id is not null) THEN
694 OPEN l_dim_csr;
695 FETCH l_dim_csr INTO l_dimension_id;
696 CLOSE l_dim_csr;
697
698 if (l_dimension_id = 0 OR
699 l_dimension_id <> p_column_rec.dimension_id) then
700 fnd_message.set_name('CN', 'DIM_PK_ALERT');
701 fnd_msg_pub.ADD;
702 RAISE fnd_api.g_exc_error;
703 end if;
704 end if;
705
706 /* Not Enforced ???
707 if (p_column_rec.foreign_key = 'Y' and
708 p_column_rec.dimension_id is null) then
709 fnd_message.set_name('CN', 'CN_TBL_DIM_FK_ERR');
710 fnd_msg_pub.ADD;
711 RAISE fnd_api.g_exc_error;
712 end if;
713 */
714
715
716 OPEN l_dim_value_csr;
717 FETCH l_dim_value_csr INTO l_dim_value_ctr;
718 CLOSE l_dim_value_csr;
719
720 if (l_dim_value_ctr = 1 AND p_column_rec.user_column_name = 'Y') then
721 fnd_message.set_name('CN', 'CN_TBL_DIM_VAL_ERR');
722 fnd_msg_pub.ADD;
723 RAISE fnd_api.g_exc_error;
724 end if;
725
726 --
727 -- Added by Kumar Sivasankaran
728 -- Date: 02/14/2002
729 open l_col_csr( p_column_rec.object_id );
730 fetch l_col_csr into l_col_rec;
731 close l_col_csr;
732
733 if l_col_rec.column_datatype <> p_column_rec.column_datatype and
734 l_col_rec.object_id IS NOT NULL and
735 p_column_rec.column_datatype <> 'ALPN' THEN
736
737 FOR rec IN l_rule_csr( p_column_rec.object_id) LOOP
738
739 cn_syin_rules_pkg.unsync_ruleset(rec.ruleset_id,p_column_rec.org_id);
740
741 END LOOP;
742
743 end if;
744
745
746 -- we are ok now proceed with update.
747 UPDATE cn_obj_columns_v
748 SET calc_formula_flag = p_column_rec.usage,
749 user_name = p_column_rec.user_name,
750 foreign_key = p_column_rec.foreign_key,
751 dimension_id = p_column_rec.dimension_id,
752 user_column_name = p_column_rec.user_column_name,
753 classification_column = p_column_rec.classification_column,
754 column_datatype = p_column_rec.column_datatype,
755 value_set_id = p_column_rec.value_set_id,
756 primary_key = p_column_rec.primary_key,
757 position = p_column_rec.position,
758 custom_call = p_column_rec.custom_call
759 WHERE column_id = p_column_rec.object_id
760 AND org_id=p_column_rec.org_id;
761
762 -- End of API body.
763 -- Standard check of p_commit.
764 IF FND_API.To_Boolean( p_commit ) THEN
765 COMMIT WORK;
766 END IF;
767 -- Standard call to get message count and if count is 1, get message info.
768 FND_MSG_PUB.Count_And_Get
769 (p_count => x_msg_count ,
770 p_data => x_msg_data ,
771 p_encoded => FND_API.G_FALSE );
772 EXCEPTION
773 WHEN FND_API.G_EXC_ERROR THEN
774 ROLLBACK TO update_column_pvt;
775 x_return_status := FND_API.G_RET_STS_ERROR ;
776 FND_MSG_PUB.Count_And_Get
777 (p_count => x_msg_count ,
778 p_data => x_msg_data ,
779 p_encoded => FND_API.G_FALSE );
780 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
781 ROLLBACK TO update_column_pvt;
782 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
783 FND_MSG_PUB.Count_And_Get
784 (p_count => x_msg_count ,
785 p_data => x_msg_data ,
786 p_encoded => FND_API.G_FALSE );
787 WHEN OTHERS THEN
788 ROLLBACK TO update_column_pvt;
789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
790 IF FND_MSG_PUB.Check_Msg_Level
791 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
792 THEN
793 FND_MSG_PUB.Add_Exc_Msg
794 (G_PKG_NAME ,
795 l_api_name );
796 END IF;
797 FND_MSG_PUB.Count_And_Get
798 (p_count => x_msg_count ,
799 p_data => x_msg_data ,
800 p_encoded => FND_API.G_FALSE );
801 END Update_Column;
802 --}}}
803
804
805
806
807
808 --{{{ Insert_Column
809 -- Start of comments
810 -- API name : Insert_Column
811 -- Type : Private.
812 -- Function : Insert column information
813 --
814 -- Pre-reqs : None.
815 -- Parameters :
816 -- IN : p_api_version IN NUMBER Required
817 -- p_init_msg_list IN VARCHAR2 Optional
818 -- Default = FND_API.G_FALSE
819 -- p_commit IN VARCHAR2 Optional
820 -- Default = FND_API.G_FALSE
821 -- p_validation_level IN NUMBER Optional
822 -- Default = FND_API.G_VALID_LEVEL_FULL
823 -- p_column_rec IN column_rec_type Required
824 -- OUT : x_return_status OUT VARCHAR2(1)
825 -- x_msg_count OUT NUMBER
826 -- x_msg_data OUT VARCHAR2(2000)
827 -- Version : Current version 1.0
828 -- Changed....
829 -- Initial version 1.0
830 --
831 -- Notes : Note text
832 --
833 -- End of comments
834
835 PROCEDURE Insert_Column
836 (p_api_version IN NUMBER ,
837 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
838 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
839 p_validation_level IN NUMBER :=
840 FND_API.G_VALID_LEVEL_FULL ,
841 p_schema_name IN varchar2 ,
842 p_table_name IN varchar2 ,
843 p_column_name IN varchar2 ,
844 p_column_rec IN column_rec_type ,
845 x_return_status OUT NOCOPY VARCHAR2 ,
846 x_msg_count OUT NOCOPY NUMBER ,
847 x_msg_data OUT NOCOPY VARCHAR2 ) IS
848 l_api_name CONSTANT VARCHAR2(30)
849 := 'Insert_Column';
850 l_api_version CONSTANT NUMBER := 1.0;
851
852 G_LAST_UPDATE_DATE DATE := Sysdate;
853 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
854 G_CREATION_DATE DATE := Sysdate;
855 G_CREATED_BY NUMBER := fnd_global.user_id;
856 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
857
858 l_table_id number := 0;
859 l_col_count number := 0;
860 l_data_type VARCHAR2(9);
861 l_data_len NUMBER(15);
862 l_column_data_type VARCHAR2(30);
863 l_table_rec cn_objects%ROWTYPE;
864 l_column_id number;
865 l_object_version_number number;
866
867 l_return_status varchar2(1);
868 l_msg_count number;
869 l_msg_data varchar2(2000);
870
871 CURSOR l_tbl_csr IS
872 SELECT *
873 FROM cn_objects
874 WHERE name = p_table_name
875 AND schema = p_schema_name
876 AND org_id=p_column_rec.org_id
877 AND object_type = 'TBL';
878
879 CURSOR l_valid_col_csr(p_table_id number) IS
880 SELECT count(*)
881 FROM all_tab_columns col, user_synonyms syn
882 WHERE syn.synonym_name = p_table_name
883 AND col.owner = syn.table_owner
884 AND col.table_name = syn.table_name
885 AND col.column_name = p_column_name
886 AND NOT EXISTS
887 (SELECT name
888 FROM cn_objects
889 WHERE table_id = p_table_id
890 AND name = p_column_name
891 AND org_id=p_column_rec.org_id
892 AND object_type = 'COL');
893
894 CURSOR l_col_data_csr IS
895 SELECT data_type, data_length
896 FROM all_tab_columns col, user_synonyms syn
897 WHERE syn.synonym_name = p_table_name
898 AND col.owner = syn.table_owner
899 AND col.table_name = syn.table_name
900 AND col.column_name = p_column_name
901 AND data_type IN
902 ('CHAR','NCHAR','VARCHAR2','VARCHAR','NVARCHAR2','LONG','NUMBER','DATE');
903
904 BEGIN
905 -- Standard Start of API savepoint
906 l_object_version_number:=1;
907 SAVEPOINT insert_column_pvt;
908 -- Standard call to check for call compatibility.
909 IF NOT FND_API.Compatible_API_Call
910 (l_api_version ,
911 p_api_version ,
912 l_api_name ,
913 G_PKG_NAME )
914 THEN
915 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
916 END IF;
917 -- Initialize message list if p_init_msg_list is set to TRUE.
918 IF FND_API.to_Boolean( p_init_msg_list ) THEN
919 FND_MSG_PUB.initialize;
920 END IF;
921 -- Initialize API return status to success
922 x_return_status := FND_API.G_RET_STS_SUCCESS;
923 -- API body
924 --first get the table data so that we can use it FOR comparison/insert later
925 OPEN l_tbl_csr;
926 FETCH l_tbl_csr INTO l_table_rec;
927 CLOSE l_tbl_csr;
928
929 --check IF the column is a valid column FOR the given table
930 --and IF it already exists
931 OPEN l_valid_col_csr(l_table_rec.object_id);
932 FETCH l_valid_col_csr INTO l_col_count;
933 CLOSE l_valid_col_csr;
934
935 IF (l_col_count = 0) THEN
936 fnd_message.set_name('CN', 'CN_TBL_NO_SUCH_DUP_COL');
937 fnd_msg_pub.ADD;
938 RAISE fnd_api.g_exc_error;
939 END IF;
940
941 OPEN l_col_data_csr;
942 FETCH l_col_data_csr INTO l_data_type, l_data_len;
943
944 --+
945 -- Set Column_Datatype to what ever is the data type of
946 -- the native column
947 --+
948 IF l_data_type = 'NUMBER' THEN
949 l_column_data_type := 'NUMB';
950 ELSIF l_data_type = 'DATE' THEN
951 l_column_data_type := 'DATE';
952 ELSE
953 l_column_data_type := 'ALPN';
954 END IF;
955
956 SELECT cn_objects_s.nextval
957 INTO l_column_id
958 FROM dual;
959
960 cn_obj_tables_pkg.begin_record(
961 P_OPERATION => 'INSERT',
962 P_OBJECT_ID => l_column_id,
963 P_NAME => p_column_name,
964 P_DESCRIPTION => l_table_rec.description,
965 P_DEPENDENCY_MAP_COMPLETE => 'N',
966 P_STATUS => 'A',
967 P_REPOSITORY_ID => l_table_rec.repository_id,
968 P_ALIAS => l_table_rec.ALIAS,
969 P_TABLE_LEVEL => NULL,
970 P_TABLE_TYPE => NULL,
971 P_OBJECT_TYPE => 'COL',
972 P_SCHEMA => l_table_rec.schema,
973 P_CALC_ELIGIBLE_FLAG => l_table_rec.calc_eligible_flag,
974 P_USER_NAME => p_column_name,
975 p_data_type => l_data_type,
976 p_data_length => l_data_len,
977 p_calc_formula_flag => 'N',
978 p_table_id => l_table_rec.object_id,
979 p_column_datatype => l_column_data_type,
980 x_object_version_number => l_object_version_number,
981 p_org_id => p_column_rec.org_id);
982
983 --after we insert the essential data we will call update to ensure that
984 --the rest of the column data is correct and IF yes, perform an update
985 update_column
986 (p_api_version => 1.0,
987 p_init_msg_list => FND_API.G_FALSE,
988 p_commit => FND_API.G_FALSE,
989 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
990 p_column_rec => insert_column.p_column_rec,
991 x_return_status => l_return_status,
992 x_msg_count => l_msg_count,
993 x_msg_data => l_msg_data);
994
995 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
996 RAISE fnd_api.g_exc_error;
997 END IF;
998
999 CLOSE l_col_data_csr;
1000
1001 -- End of API body.
1002 -- Standard check of p_commit.
1003 IF FND_API.To_Boolean( p_commit ) THEN
1004 COMMIT WORK;
1005 END IF;
1006 -- Standard call to get message count and if count is 1, get message info.
1007 FND_MSG_PUB.Count_And_Get
1008 (p_count => x_msg_count ,
1009 p_data => x_msg_data ,
1010 p_encoded => FND_API.G_FALSE );
1011 EXCEPTION
1012 WHEN FND_API.G_EXC_ERROR THEN
1013 ROLLBACK TO insert_column_pvt;
1014 x_return_status := FND_API.G_RET_STS_ERROR ;
1015 FND_MSG_PUB.Count_And_Get
1016 (p_count => x_msg_count ,
1017 p_data => x_msg_data ,
1018 p_encoded => FND_API.G_FALSE );
1019 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1020 ROLLBACK TO insert_column_pvt;
1021 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1022 FND_MSG_PUB.Count_And_Get
1023 (p_count => x_msg_count ,
1024 p_data => x_msg_data ,
1025 p_encoded => FND_API.G_FALSE );
1026 WHEN OTHERS THEN
1027 ROLLBACK TO insert_column_pvt;
1028 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1029 IF FND_MSG_PUB.Check_Msg_Level
1030 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1031 THEN
1032 FND_MSG_PUB.Add_Exc_Msg
1033 (G_PKG_NAME ,
1034 l_api_name );
1035 END IF;
1036 FND_MSG_PUB.Count_And_Get
1037 (p_count => x_msg_count ,
1038 p_data => x_msg_data ,
1039 p_encoded => FND_API.G_FALSE );
1040 END Insert_Column;
1041 --}}}
1042
1043
1044
1045
1046 --{{{ Delete_Column
1047
1048 -- Start of comments
1049 -- API name : Delete_Column
1050 -- Type : Private.
1051 -- Function : Delete column information
1052 --
1053 -- Pre-reqs : None.
1054 -- Parameters :
1055 -- IN : p_api_version IN NUMBER Required
1056 -- p_init_msg_list IN VARCHAR2 Optional
1057 -- Default = FND_API.G_FALSE
1058 -- p_commit IN VARCHAR2 Optional
1059 -- Default = FND_API.G_FALSE
1060 -- p_validation_level IN NUMBER Optional
1061 -- Default = FND_API.G_VALID_LEVEL_FULL
1062 -- p_column_rec IN column_rec_type Required
1063 -- OUT : x_return_status OUT VARCHAR2(1)
1064 -- x_msg_count OUT NUMBER
1065 -- x_msg_data OUT VARCHAR2(2000)
1066 -- Version : Current version 1.0
1067 -- Changed....
1068 -- Initial version 1.0
1069 --
1070 -- Notes : Note text
1071 --
1072 -- End of comments
1073
1074 PROCEDURE Delete_Column
1075 (p_api_version IN NUMBER ,
1076 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1077 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1078 p_validation_level IN NUMBER :=
1079 FND_API.G_VALID_LEVEL_FULL ,
1080 p_column_id IN number ,
1081 x_return_status OUT NOCOPY VARCHAR2 ,
1082 x_msg_count OUT NOCOPY NUMBER ,
1083 x_msg_data OUT NOCOPY VARCHAR2 ) IS
1084 l_api_name CONSTANT VARCHAR2(30)
1085 := 'Delete_Column';
1086 l_api_version CONSTANT NUMBER := 1.0;
1087
1088 l_table_id number := 0;
1089
1090 G_LAST_UPDATE_DATE DATE := Sysdate;
1091 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
1092 G_CREATION_DATE DATE := Sysdate;
1093 G_CREATED_BY NUMBER := fnd_global.user_id;
1094 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
1095
1096 CURSOR l_delete_csr ( p_column_id number ) IS
1097 SELECT table_id
1098 FROM cn_obj_columns_v
1099 WHERE column_id = p_column_id
1100 AND object_type = 'COL';
1101
1102 BEGIN
1103 -- Standard Start of API savepoint
1104 SAVEPOINT delete_column_pvt;
1105 -- Standard call to check for call compatibility.
1106 IF NOT FND_API.Compatible_API_Call
1107 (l_api_version ,
1108 p_api_version ,
1109 l_api_name ,
1110 G_PKG_NAME )
1111 THEN
1112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1113 END IF;
1114 -- Initialize message list if p_init_msg_list is set to TRUE.
1115 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1116 FND_MSG_PUB.initialize;
1117 END IF;
1118 -- Initialize API return status to success
1119 x_return_status := FND_API.G_RET_STS_SUCCESS;
1120 -- API body
1121 open l_delete_csr( p_column_id );
1122 fetch l_delete_csr into l_table_id;
1123 close l_delete_csr;
1124
1125 if (l_table_id < 0) then
1126 fnd_message.set_name('CN', 'CN_SD_TBL_COL_NO_DEL');
1127 fnd_msg_pub.ADD;
1128 RAISE fnd_api.g_exc_error;
1129 end if;
1130
1131 DELETE FROM cn_obj_columns_v
1132 WHERE column_id = p_column_id
1133 AND object_type = 'COL';
1134
1135 -- End of API body.
1136 -- Standard check of p_commit.
1137 IF FND_API.To_Boolean( p_commit ) THEN
1138 COMMIT WORK;
1139 END IF;
1140 -- Standard call to get message count and if count is 1, get message info.
1141 FND_MSG_PUB.Count_And_Get
1142 (p_count => x_msg_count ,
1143 p_data => x_msg_data ,
1144 p_encoded => FND_API.G_FALSE );
1145 EXCEPTION
1146 WHEN FND_API.G_EXC_ERROR THEN
1147 ROLLBACK TO delete_column_pvt;
1148 x_return_status := FND_API.G_RET_STS_ERROR ;
1149 FND_MSG_PUB.Count_And_Get
1150 (p_count => x_msg_count ,
1151 p_data => x_msg_data ,
1152 p_encoded => FND_API.G_FALSE );
1153 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1154 ROLLBACK TO delete_column_pvt;
1155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1156 FND_MSG_PUB.Count_And_Get
1157 (p_count => x_msg_count ,
1158 p_data => x_msg_data ,
1159 p_encoded => FND_API.G_FALSE );
1160 WHEN OTHERS THEN
1161 ROLLBACK TO delete_column_pvt;
1162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1163 IF FND_MSG_PUB.Check_Msg_Level
1164 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1165 THEN
1166 FND_MSG_PUB.Add_Exc_Msg
1167 (G_PKG_NAME ,
1168 l_api_name );
1169 END IF;
1170 FND_MSG_PUB.Count_And_Get
1171 (p_count => x_msg_count ,
1172 p_data => x_msg_data ,
1173 p_encoded => FND_API.G_FALSE );
1174 END Delete_Column;
1175
1176 --}}}
1177 END CN_SYS_TABLES_PVT;