[Home] [Help]
PACKAGE BODY: APPS.CN_CALC_EXT_TABLES_PVT
Source
1 PACKAGE BODY CN_CALC_EXT_TABLES_PVT AS
2 --$Header: cnvexttb.pls 115.9 2002/11/21 21:13:14 hlchen ship $
3
4 --Global Variables
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_CALC_EXT_TABLES_PVT';
7 G_LAST_UPDATE_DATE DATE := Sysdate;
8 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
9 G_CREATION_DATE DATE := Sysdate;
10 G_CREATED_BY NUMBER := fnd_global.user_id;
11 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
12
13 --=========================================================================
14 -- Start of comments
15 -- API name : Create_Calc_Ext_Table
16 -- Type : Private
17 -- Function : This private API can be used to create External
18 -- Table Mapping
19 -- Pre-reqs : None.
20 -- Parameters :
21 -- IN : p_api_version IN NUMBER Required
22 -- p_init_msg_list IN VARCHAR2 Optional
23 -- Default = FND_API.G_FALSE
24 -- p_commit IN VARCHAR2 Optional
25 -- Default = FND_API.G_FALSE
26 -- p_validation_level IN NUMBER Optional
27 -- Default = FND_API.G_VALID_LEVEL_FULL
28 --
29 -- OUT : x_return_status OUT VARCHAR2(1)
30 -- x_msg_count OUT NUMBER
31 -- x_msg_data OUT VARCHAR2(2000)
32 --
33 -- Version : Current version 1.0
34 -- previous version y.y
35 -- Changed....
36 -- Initial version 1.0
37 --
38 -- Notes : Note text
39 --
40 -- End of comments
41 --=========================================================================
42 PROCEDURE create_calc_ext_table
43 ( p_api_version IN NUMBER,
44 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
45 p_commit IN VARCHAR2 := FND_API.G_FALSE,
46 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_count OUT NOCOPY NUMBER,
49 x_msg_data OUT NOCOPY VARCHAR2,
50 x_loading_status OUT NOCOPY VARCHAR2,
51 x_calc_ext_table_id OUT NOCOPY NUMBER,
52 p_calc_ext_table_rec IN CN_CALC_EXT_TABLES_PVT.calc_ext_table_rec_type
53 )
54 IS
55
56 l_api_name CONSTANT VARCHAR2(30) := 'Create_Calc_Ext_Table';
57 l_api_version CONSTANT NUMBER := 1.0;
58 l_loading_status VARCHAR2(4000);
59 l_error_status NUMBER;
60 l_error_parameter VARCHAR2(30);
61 l_rowid VARCHAR2(4000);
62 l_sequence_number NUMBER;
63 l_count NUMBER;
64 l_calc_ext_table_id NUMBER;
65
66 l_alias cn_calc_ext_tables.alias%TYPE;
67
68 cursor unique_alias is
69 Select count(*)
70 from cn_calc_ext_tables
71 Where external_table_id = p_calc_ext_table_rec.external_table_id
72 and calc_ext_table_id <> nvl( p_calc_ext_table_rec.calc_ext_table_id, -99);
73
74 l_table_id NUMBER;
75
76
77 cursor table_name_curs ( p_object_id NUMBER ) is
78 select name from cn_objects
79 where object_id = p_object_id
80 and object_type = 'TBL';
81
82 l_external_table_name cn_objects.name%TYPE;
83
84 BEGIN
85
86 -- Standard Start of API savepoint
87 SAVEPOINT Create_Calc_Ext_Table;
88 -- Standard call to check for call compatibility.
89 IF NOT FND_API.Compatible_API_Call ( l_api_version,
90 p_api_version,
91 l_api_name,
92 G_PKG_NAME )
93 THEN
94 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
95 END IF;
96
97 -- Initialize message list if p_init_msg_list is set to TRUE.
98 IF FND_API.to_Boolean( p_init_msg_list )
99 THEN
100 FND_MSG_PUB.initialize;
101 END IF;
102
103 -- Initialize API return status to success
104 x_return_status := FND_API.G_RET_STS_SUCCESS;
105 x_loading_status := 'CN_INSERTED';
106
107 -- API body
108
109 open unique_alias;
110 fetch unique_alias into l_count;
111 close unique_alias;
112
113 if l_count = 0 then
114 l_alias := p_calc_ext_table_rec.alias;
115 else
116 l_count := l_count + 1;
117 l_alias := p_calc_ext_table_rec.alias || l_count ;
118 end if;
119
120 --Check for missing parameters in the p_ruleset_rec parameter
121 IF (cn_api.chk_miss_null_char_para
122 ( p_calc_ext_table_rec.name,
123 cn_api.get_lkup_meaning('NAME', 'EXTERNAL_TABLE'),
124 x_loading_status,
125 x_loading_status) = FND_API.G_TRUE )
126 THEN
127 RAISE fnd_api.g_exc_error;
128 END IF;
129
130 IF (cn_api.chk_miss_null_num_para
131 ( p_calc_ext_table_rec.internal_table_id,
132 cn_api.get_lkup_meaning('INTERNAL_TABLE_ID', 'EXTERNAL_TABLE'),
133 x_loading_status,
134 x_loading_status) = FND_API.G_TRUE )
135 THEN
136 RAISE fnd_api.g_exc_error;
137 END IF;
138
139 if p_calc_ext_table_rec.internal_table_id = 0 THEN
140 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
141 THEN
142 fnd_message.set_name('CN', 'CN_INTERNAL_TABLE_NOT_NULL');
143 fnd_msg_pub.add;
144 END IF;
145 x_loading_status := 'CN_INTERNAL_TABLE_NOT_NULL';
146 RAISE FND_API.G_EXC_ERROR;
147 END IF;
148
149 IF (cn_api.chk_miss_null_num_para
150 ( p_calc_ext_table_rec.external_table_id,
151 cn_api.get_lkup_meaning('EXTERNAL_TABLE_ID', 'EXTERNAL_TABLE'),
152 x_loading_status,
153 x_loading_status) = FND_API.G_TRUE )
154 THEN
155 RAISE fnd_api.g_exc_error;
156 END IF;
157
158 if p_calc_ext_table_rec.external_table_id = 0 THEN
159 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
160 THEN
161 fnd_message.set_name('CN', 'CN_EXTERNAL_TABLE_NOT_NULL');
162 fnd_msg_pub.add;
163 END IF;
164 x_loading_status := 'CN_EXTERNAL_TABLE_NOT_NULL';
165 RAISE FND_API.G_EXC_ERROR;
166 END IF;
167
168 IF (cn_api.chk_miss_null_char_para
169 ( p_calc_ext_table_rec.used_flag,
170 cn_api.get_lkup_meaning('USED_FLAG', 'EXTERNAL_TABLE'),
171 x_loading_status,
172 x_loading_status) = FND_API.G_TRUE )
173 THEN
174 RAISE fnd_api.g_exc_error;
175 END IF;
176
177 IF (cn_api.chk_miss_null_char_para
178 ( p_calc_ext_table_rec.schema,
179 cn_api.get_lkup_meaning('SCHEMA', 'EXTERNAL_TABLE'),
180 x_loading_status,
181 x_loading_status) = FND_API.G_TRUE )
182 THEN
183 RAISE fnd_api.g_exc_error;
184 END IF;
185
186 /* IF (cn_api.chk_miss_null_char_para
187 ( p_calc_ext_table_rec.alias,
188 cn_api.get_lkup_meaning('ALIAS', 'EXTERNAL_TABLE'),
189 x_loading_status,
190 x_loading_status) = FND_API.G_TRUE )
191 THEN
192 RAISE fnd_api.g_exc_error;
193 END IF ;
194 */
195
196 open table_name_curs( p_calc_ext_table_rec.external_table_id);
197 fetch table_name_curs into l_external_table_name;
198 close table_name_curs;
199
200
201 CN_CALC_EXT_TABLE_PKG.insert_row
202 (x_calc_ext_table_id => x_calc_ext_table_id
203 ,p_name => p_calc_ext_table_rec.name
204 ,p_description => p_calc_ext_table_rec.description
205 ,p_internal_table_id => p_calc_ext_table_rec.internal_table_id
206 ,p_external_table_id => p_calc_ext_table_rec.external_table_id
207 ,p_used_flag => p_calc_ext_table_rec.used_flag
208 ,p_schema => p_calc_ext_table_rec.schema
209 ,p_external_table_name => l_external_table_name
210 ,p_alias => l_alias
211 ,p_creation_date => sysdate
212 ,p_created_by => g_created_by
213 ,p_last_update_date => sysdate
214 ,p_last_updated_by => g_last_updated_by
215 ,p_last_update_login => g_last_update_login);
216
217 -- End of API body.
218
219 -- Standard check of p_commit.
220 IF FND_API.To_Boolean( p_commit )
221 THEN
222 COMMIT WORK;
223 END IF;
224
225 FND_MSG_PUB.Count_And_Get
226 (
227 p_count => x_msg_count ,
228 p_data => x_msg_data ,
229 p_encoded => FND_API.G_FALSE
230 );
231 EXCEPTION
232 WHEN FND_API.G_EXC_ERROR THEN
233 ROLLBACK TO Create_Calc_Ext_Table;
234 x_return_status := FND_API.G_RET_STS_ERROR ;
235 FND_MSG_PUB.Count_And_Get
236 (
237 p_count => x_msg_count ,
238 p_data => x_msg_data ,
239 p_encoded => FND_API.G_FALSE
240 );
241 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
242 ROLLBACK TO Create_Calc_Ext_Table;
243 x_loading_status := 'UNEXPECTED_ERR';
244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
245 FND_MSG_PUB.Count_And_Get
246 (
247 p_count => x_msg_count ,
248 p_data => x_msg_data ,
249 p_encoded => FND_API.G_FALSE
250 );
251 WHEN OTHERS THEN
252 ROLLBACK TO Create_Calc_Ext_Table;
253 x_loading_status := 'UNEXPECTED_ERR';
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
255 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
256 THEN
257 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
258 END IF;
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 END Create_Calc_Ext_Table;
266 --=========================================================================
267 -- Start of comments
268 -- API name : Update_Calc_Ext_Table
269 -- Type : Public
270 -- Function : This Public API can be used to update a rule,
271 -- a ruleset or rule attributes in Oracle Sales
272 -- Compensation.
273 -- Pre-reqs : None.
274 -- Parameters :
275 -- IN : p_api_version IN NUMBER Required
276 -- p_init_msg_list IN VARCHAR2 Optional
277 -- Default = FND_API.G_FALSE
278 -- p_commit IN VARCHAR2 Optional
279 -- Default = FND_API.G_FALSE
280 -- p_validation_level IN NUMBER Optional
281 -- Default = FND_API.G_VALID_LEVEL_FULL
282 --
283 -- OUT : x_return_status OUT VARCHAR2(1)
284 -- x_msg_count OUT NUMBER
285 -- x_msg_data OUT VARCHAR2(2000)
286 --
287 -- Version : Current version 1.0
288 -- previous version y.y
289 -- Changed....
290 -- Initial version 1.0
291 --
292 -- Notes : Note text
293 --
294 -- End of comments
295 --=========================================================================
296
297 PROCEDURE Update_calc_ext_table
298 ( p_api_version IN NUMBER,
299 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
300 p_commit IN VARCHAR2 := FND_API.G_FALSE,
301 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
302 x_return_status OUT NOCOPY VARCHAR2,
303 x_msg_count OUT NOCOPY NUMBER,
304 x_msg_data OUT NOCOPY VARCHAR2,
305 x_loading_status OUT NOCOPY VARCHAR2,
306 p_old_calc_ext_table_rec IN OUT NOCOPY CN_CALC_EXT_TABLES_PVT.calc_ext_table_rec_type,
307 p_calc_ext_table_rec IN OUT NOCOPY CN_CALC_EXT_TABLES_PVT.calc_ext_table_rec_type
308 ) IS
309
310 /* CURSOR l_ovn_csr IS
311 SELECT nvl(object_version_number,1)
312 FROM cn_calc_ext_tables
313 WHERE calc_ext_table_id = p_old_calc_ext_table_rec.calc_ext_table_id;
314 */
315
316 cursor unique_alias is
317 Select count(*)
318 from cn_calc_ext_tables
319 Where external_table_id = p_calc_ext_table_rec.external_table_id
320 and calc_ext_table_id <> nvl( p_calc_ext_table_rec.calc_ext_table_id, -99);
321
322 l_api_name CONSTANT VARCHAR2(30) := 'Update_Calc_Ext_Table';
323 l_api_version CONSTANT NUMBER := 1.0;
324 l_loading_st VARCHAR2(4000);
325 l_count NUMBER;
326
327 l_ruleset_status VARCHAR2(100);
328 l_request_id NUMBER;
329 l_object_version_number NUMBER;
330
331 l_alias cn_calc_ext_tables.alias%TYPE;
332
333
334 cursor table_name_curs ( p_object_id NUMBER ) is
335 select name from cn_objects
336 where object_id = p_object_id
337 and object_type = 'TBL';
338
339 l_external_table_name cn_objects.name%TYPE;
340
341
342 cursor get_tbl_curs ( p_calc_ext_table_id NUMBER ) is
343 Select external_table_id, internal_table_id
344 from cn_calc_ext_tables
345 where calc_ext_table_id = p_calc_ext_table_id;
346
347
348 cursor get_dtl_curs ( p_calc_ext_table_id NUMBER ) is
349 Select count(1)
350 from cn_calc_ext_tbl_dtls
351 where calc_ext_table_id = p_calc_ext_table_id;
352
353
354 l_col_found NUMBER := 0;
355 l_old_external_table_id NUMBER := 0;
356 l_old_internal_table_id NUMBER := 0;
357
358
359 BEGIN
360
361 -- Standard Start of API savepoint
362 SAVEPOINT Update_CALC_EXT_TABLES;
363 -- Standard call to check for call compatibility.
364 IF NOT FND_API.Compatible_API_Call ( l_api_version,
365 p_api_version,
366 l_api_name,
367 G_PKG_NAME )
368 THEN
369 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
370 END IF;
371
372 -- Initialize message list if p_init_msg_list is set to TRUE.
373 IF FND_API.to_Boolean( p_init_msg_list )
374 THEN
375 FND_MSG_PUB.initialize;
376 END IF;
377
378 -- Initialize API return status to success
379 x_return_status := FND_API.G_RET_STS_SUCCESS;
380 x_loading_status := 'CN_UPDATED';
381
382 p_old_calc_ext_table_rec.calc_ext_table_id := p_calc_ext_table_rec.calc_ext_table_id;
383
384
385 open unique_alias;
386 fetch unique_alias into l_count;
387 close unique_alias;
388
389 if l_count = 0 then
390 l_alias := p_calc_ext_table_rec.alias;
391 else
392 l_count := l_count + 1;
393 l_alias := p_calc_ext_table_rec.alias || l_count;
394 end if;
395
396 IF (cn_api.chk_miss_null_char_para
397 ( p_calc_ext_table_rec.name,
398 cn_api.get_lkup_meaning('NAME', 'EXTERNAL_TABLE'),
399 x_loading_status,
400 x_loading_status) = FND_API.G_TRUE )
401 THEN
402 RAISE fnd_api.g_exc_error;
403 END IF;
404
405
406 IF (cn_api.chk_miss_null_num_para
407 ( p_calc_ext_table_rec.internal_table_id,
408 cn_api.get_lkup_meaning('INTERNAL_TABLE_ID', 'EXTERNAL_TABLE'),
409 x_loading_status,
410 x_loading_status) = FND_API.G_TRUE )
411 THEN
412 RAISE fnd_api.g_exc_error;
413 END IF ;
414
415 if p_calc_ext_table_rec.internal_table_id = 0 THEN
416 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
417 THEN
418 fnd_message.set_name('CN', 'CN_INTERNAL_TABLE_NOT_NULL');
419 fnd_msg_pub.add;
420 END IF;
421 x_loading_status := 'CN_INTERNAL_TABLE_NOT_NULL';
422 RAISE FND_API.G_EXC_ERROR;
423 END IF;
424
425 IF (cn_api.chk_miss_null_num_para
426 ( p_calc_ext_table_rec.external_table_id,
427 cn_api.get_lkup_meaning('EXTERNAL_TABLE_ID', 'EXTERNAL_TABLE'),
428 x_loading_status,
429 x_loading_status) = FND_API.G_TRUE )
430 THEN
431 RAISE fnd_api.g_exc_error;
432 END IF ;
433
434 IF (cn_api.chk_miss_null_char_para
435 ( p_calc_ext_table_rec.used_flag,
436 cn_api.get_lkup_meaning('USED_FLAG', 'EXTERNAL_TABLE'),
437 x_loading_status,
438 x_loading_status) = FND_API.G_TRUE )
439 THEN
440 RAISE fnd_api.g_exc_error;
441 END IF;
442
443 if p_calc_ext_table_rec.external_table_id = 0 THEN
444 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
445 THEN
446 fnd_message.set_name('CN', 'CN_EXTERNAL_TABLE_NOT_NULL');
447 fnd_msg_pub.add;
448 END IF;
449 x_loading_status := 'CN_EXTERNAL_TABLE_NOT_NULL';
450 RAISE FND_API.G_EXC_ERROR;
451 END IF;
452
453 IF (cn_api.chk_miss_null_char_para
454 ( p_calc_ext_table_rec.schema,
455 cn_api.get_lkup_meaning('SCHEMA', 'EXTERNAL_TABLE'),
456 x_loading_status,
457 x_loading_status) = FND_API.G_TRUE )
458 THEN
459 RAISE fnd_api.g_exc_error;
460 END IF ;
461
462 IF (cn_api.chk_miss_null_char_para
463 ( p_calc_ext_table_rec.alias,
464 cn_api.get_lkup_meaning('ALIAS', 'EXTERNAL_TABLE'),
465 x_loading_status,
466 x_loading_status) = FND_API.G_TRUE )
467 THEN
468 RAISE fnd_api.g_exc_error;
469 END IF ;
470
471 open table_name_curs( p_calc_ext_table_rec.external_table_id);
472 fetch table_name_curs into l_external_table_name;
473 close table_name_curs;
474
475 open get_tbl_curs( p_calc_ext_table_rec.calc_ext_table_id);
476 fetch get_tbl_curs into l_old_external_table_id, l_old_internal_table_id;
477 close get_tbl_curs;
478
479 if ( p_calc_ext_table_rec.internal_table_id <>
480 l_old_internal_table_id ) or
481 ( p_calc_ext_table_rec.external_table_id <>
482 l_old_external_table_id ) THEN
483
484 open get_dtl_curs( p_calc_ext_table_rec.calc_ext_table_id);
485 fetch get_dtl_curs into l_col_found;
486 close get_dtl_curs;
487
488 if l_col_found = 1 THEN
489 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
490 THEN
491 fnd_message.set_name('CN', 'CN_EXT_UPDATE_NOT_ALLOWED');
492 fnd_msg_pub.add;
493 END IF;
494 x_loading_status := 'CN_EXT_UPDATE_NOT_ALLOWED';
495 RAISE FND_API.G_EXC_ERROR;
496 END IF;
497 END IF;
498
499
500 CN_CALC_EXT_TABLE_PKG.update_row
501 (p_calc_ext_table_id => p_calc_ext_table_rec.calc_ext_table_id
502 ,p_name => p_calc_ext_table_rec.name
503 ,p_description => p_calc_ext_table_rec.description
504 ,p_internal_table_id => p_calc_ext_table_rec.internal_table_id
505 ,p_external_table_id => p_calc_ext_table_rec.external_table_id
506 ,p_used_flag => p_calc_ext_table_rec.used_flag
507 ,p_schema => p_calc_ext_table_rec.schema
508 ,p_external_table_name => l_external_table_name
509 ,p_alias => l_alias
510 ,p_last_update_date => sysdate
511 ,p_last_updated_by => g_last_updated_by
512 ,p_last_update_login => g_last_update_login);
513
514
515 -- End of API body.
516 -- Standard check of p_commit.
517 IF FND_API.To_Boolean( p_commit )
518 THEN
519 COMMIT WORK;
520 END IF;
521
522
523 -- Standard call to get message count and if count is 1, get message info.
524 FND_MSG_PUB.Count_And_Get
525 (
526 p_count => x_msg_count ,
527 p_data => x_msg_data ,
528 p_encoded => FND_API.G_FALSE
529 );
530
531 EXCEPTION
532 WHEN FND_API.G_EXC_ERROR THEN
533 ROLLBACK TO Update_CALC_EXT_TABLES;
534 x_return_status := FND_API.G_RET_STS_ERROR ;
535
536 FND_MSG_PUB.Count_And_Get
537 (
538 p_count => x_msg_count ,
539 p_data => x_msg_data ,
540 p_encoded => FND_API.G_FALSE
541 );
542
543 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
544 ROLLBACK TO Update_CALC_EXT_TABLES;
545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
546 FND_MSG_PUB.Count_And_Get
547 (
548 p_count => x_msg_count ,
549 p_data => x_msg_data ,
550 p_encoded => FND_API.G_FALSE
551 );
552
553 WHEN OTHERS THEN
554 ROLLBACK TO Update_CALC_EXT_TABLES;
555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
556 IF FND_MSG_PUB.Check_Msg_Level
557 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
558 THEN
559 FND_MSG_PUB.Add_Exc_Msg
560 (G_PKG_NAME,
561 l_api_name
562 );
563 END IF;
564
565 FND_MSG_PUB.Count_And_Get
566 (
567 p_count => x_msg_count ,
568 p_data => x_msg_data ,
569 p_encoded => FND_API.G_FALSE
570 );
571
572 END;
573 --=========================================================================
574 -- Start of comments
575 -- API name : Delete_CALC_EXT_TABLES
576 -- Type : Public
577 -- Function : This Public API can be used to delete External
578 -- Pre-reqs : None.
579 -- Parameters :
580 -- IN : p_api_version IN NUMBER Required
581 -- p_init_msg_list IN VARCHAR2 Optional
582 -- Default = FND_API.G_FALSE
583 -- p_commit IN VARCHAR2 Optional
584 -- Default = FND_API.G_FALSE
585 -- p_validation_level IN NUMBER Optional
586 -- Default = FND_API.G_VALID_LEVEL_FULL
587 -- p_ruleset_rec_type IN
588 --
589 -- OUT : x_return_status OUT VARCHAR2(1)
590 -- x_msg_count OUT NUMBER
591 -- x_msg_data OUT VARCHAR2(2000)
592 --
593 -- Version : Current version 1.0
594 -- previous version y.y
595 -- Changed....
596 -- Initial version 1.0
597 --
598 --
599 -- End of comments
600 --=========================================================================
601 PROCEDURE Delete_Calc_Ext_Table
602 ( p_api_version IN NUMBER,
603 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
604 p_commit IN VARCHAR2 := FND_API.G_FALSE,
605 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
606 x_return_status OUT NOCOPY VARCHAR2,
607 x_msg_count OUT NOCOPY NUMBER,
608 x_msg_data OUT NOCOPY VARCHAR2,
609 x_loading_status OUT NOCOPY VARCHAR2,
610 p_calc_ext_table_id IN NUMBER
611 ) IS
612
613
614 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Calc_Ext_Table';
615 l_api_version CONSTANT NUMBER := 1.0;
616 l_status NUMBER;
617
618 l_loading_status VARCHAR2(100);
619 l_error_parameter VARCHAR2(100);
620 l_error_status NUMBER;
621
622 cursor get_col is
623 select count(*)
624 from cn_calc_ext_tbl_dtls
625 where CALC_EXT_TABLE_ID = p_calc_ext_table_id ;
626
627 l_count number := 0;
628
629 BEGIN
630
631 -- Standard Start of API savepoint
632 SAVEPOINT Delete_CALC_EXT_TABLES;
633 -- Standard call to check for call compatibility.
634 IF NOT FND_API.Compatible_API_Call ( l_api_version,
635 p_api_version,
636 l_api_name,
637 G_PKG_NAME )
638 THEN
639 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
640 END IF;
641
642 -- Initialize message list if p_init_msg_list is set to TRUE.
643 IF FND_API.to_Boolean( p_init_msg_list )
644 THEN
645 FND_MSG_PUB.initialize;
646 END IF;
647
648 -- Initialize API return status to success
649 x_return_status := FND_API.G_RET_STS_SUCCESS;
650
651 -- API body
652
653 -- check delete allowed
654
655 open get_col;
656 fetch get_col into l_count;
657 close get_col;
658
659 IF l_count > 0 THEN
660 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
661 THEN
662 fnd_message.set_name('CN', 'CN_COLUMN_EXISTS');
663 fnd_msg_pub.add;
664 END IF;
665 x_loading_status := 'CN_COLUMN_EXISTS';
666 RAISE FND_API.G_EXC_ERROR;
667 END IF;
668
669
670 CN_CALC_EXT_TABLE_PKG.Delete_row(p_calc_ext_table_id);
671
672 -- End of API body.
673
674 -- Standard check of p_commit.
675 IF FND_API.To_Boolean( p_commit )
676 THEN
677 COMMIT WORK;
678 END IF;
679
680 -- Standard call to get message count and if count is 1, get message info.
681 FND_MSG_PUB.Count_And_Get
682 (
683 p_count => x_msg_count ,
684 p_data => x_msg_data ,
685 p_encoded => FND_API.G_FALSE
686 );
687
688 EXCEPTION
689 WHEN FND_API.G_EXC_ERROR THEN
690 ROLLBACK TO Delete_CALC_EXT_TABLES;
691 x_return_status := FND_API.G_RET_STS_ERROR ;
692 FND_MSG_PUB.Count_And_Get
693 (
694 p_count => x_msg_count ,
695 p_data => x_msg_data ,
696 p_encoded => FND_API.G_FALSE
697 );
698
699 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
700 ROLLBACK TO Delete_CALC_EXT_TABLES;
701 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
702
703 FND_MSG_PUB.Count_And_Get
704 (
705 p_count => x_msg_count ,
706 p_data => x_msg_data ,
707 p_encoded => FND_API.G_FALSE
708 );
709
710 WHEN OTHERS THEN
711 ROLLBACK TO Delete_CALC_EXT_TABLES;
712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
713 IF FND_MSG_PUB.Check_Msg_Level
714 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
715 THEN
716 FND_MSG_PUB.Add_Exc_Msg
717 (G_PKG_NAME,
718 l_api_name
719 );
720 END IF;
721
722 FND_MSG_PUB.Count_And_Get
723 (
724 p_count => x_msg_count ,
725 p_data => x_msg_data ,
726 p_encoded => FND_API.G_FALSE
727 );
728
729 END;
730
731
732 END CN_CALC_EXT_TABLES_PVT;