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