[Home] [Help]
PACKAGE BODY: APPS.CSC_PROFILE_VARIABLE_PUB
Source
1 PACKAGE BODY CSC_Profile_Variable_PUB AS
2 /* $Header: cscppvab.pls 115.18 2002/11/28 09:35:45 bhroy ship $ */
3
4 /*********** Global Variables ********************************/
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSC_Profile_Variable_PUB' ;
7
8 ----------------------------------------------------------------------------
9 -- Convert_pub_to_pvt_rec Procedure
10 ----------------------------------------------------------------------------
11
12 PROCEDURE Convert_pub_to_pvt_Rec (
13 P_Profile_Variables_Rec IN CSC_profile_variable_PUB.ProfVar_Rec_Type,
14 x_pvt_Profile_Variables_rec OUT NOCOPY CSC_profile_variable_PVT.ProfVar_Rec_Type
15 )
16 IS
17 l_any_errors BOOLEAN := FALSE;
18 BEGIN
19
20 x_pvt_Profile_Variables_rec.BLOCK_ID := P_Profile_Variables_Rec.BLOCK_ID;
21 x_pvt_Profile_Variables_rec.BLOCK_NAME_CODE := P_Profile_Variables_Rec.BLOCK_NAME_CODE;
22 x_pvt_Profile_Variables_rec.START_DATE_ACTIVE := P_Profile_Variables_Rec.START_DATE_ACTIVE;
23 x_pvt_Profile_Variables_rec.END_DATE_ACTIVE := P_Profile_Variables_Rec.END_DATE_ACTIVE;
24 x_pvt_Profile_Variables_rec.SEEDED_FLAG := P_Profile_Variables_Rec.SEEDED_FLAG;
25 x_pvt_Profile_Variables_rec.OBJECT_CODE := P_Profile_Variables_Rec.OBJECT_CODE;
26 x_pvt_Profile_Variables_rec.SQL_STMNT_FOR_DRILLDOWN := P_Profile_Variables_Rec.SQL_STMNT_FOR_DRILLDOWN;
27 x_pvt_Profile_Variables_rec.SQL_STMNT := P_Profile_Variables_Rec.SQL_STMNT;
28 x_pvt_Profile_Variables_rec.SELECT_CLAUSE := P_Profile_Variables_Rec.SELECT_CLAUSE;
29 x_pvt_Profile_Variables_rec.CURRENCY_CODE := P_Profile_Variables_Rec.CURRENCY_CODE;
30 x_pvt_Profile_Variables_rec.FROM_CLAUSE := P_Profile_Variables_Rec.FROM_CLAUSE;
31 x_pvt_Profile_Variables_rec.WHERE_CLAUSE := P_Profile_Variables_Rec.WHERE_CLAUSE;
32 x_pvt_Profile_Variables_rec.ORDER_BY_CLAUSE := P_Profile_Variables_Rec.ORDER_BY_CLAUSE;
33 x_pvt_Profile_Variables_rec.OTHER_CLAUSE := P_Profile_Variables_Rec.OTHER_CLAUSE;
34 x_pvt_Profile_Variables_rec.BLOCK_LEVEL := P_Profile_Variables_Rec.BLOCK_LEVEL;
35 x_pvt_Profile_Variables_rec.CREATED_BY := P_Profile_Variables_Rec.CREATED_BY;
36 x_pvt_Profile_Variables_rec.CREATION_DATE := P_Profile_Variables_Rec.CREATION_DATE;
37 x_pvt_Profile_Variables_rec.LAST_UPDATED_BY := P_Profile_Variables_Rec.LAST_UPDATED_BY;
38 x_pvt_Profile_Variables_rec.LAST_UPDATE_DATE := P_Profile_Variables_Rec.LAST_UPDATE_DATE;
39 x_pvt_Profile_Variables_rec.LAST_UPDATE_LOGIN := P_Profile_Variables_Rec.LAST_UPDATE_LOGIN;
40 x_pvt_Profile_Variables_rec.OBJECT_VERSION_NUMBER := P_Profile_Variables_Rec.OBJECT_VERSION_NUMBER;
41 x_pvt_Profile_Variables_rec.APPLICATION_ID := P_Profile_Variables_Rec.APPLICATION_ID;
42
43 -- If there is an error in conversion precessing, raise an error.
44 IF l_any_errors
45 THEN
46 raise FND_API.G_EXC_ERROR;
47 END IF;
48
49 END Convert_pub_to_pvt_Rec;
50
51 ----------------------------------------------------------------------------
52 -- Start of Procedure Body Convert_Columns_to_Rec
53 ----------------------------------------------------------------------------
54
55 PROCEDURE Convert_Columns_to_Rec (
56 p_block_id IN NUMBER DEFAULT NULL,
57 p_block_name IN VARCHAR2,
58 p_block_name_code IN VARCHAR2,
59 p_description IN VARCHAR2,
60 p_sql_stmnt IN VARCHAR2,
61 p_seeded_flag IN VARCHAR2,
62 p_start_date_active IN DATE ,
63 p_end_date_active IN DATE ,
64 p_currency_code IN VARCHAR2,
65 --p_form_function_id IN NUMBER,
66 p_object_code IN VARCHAR2,
67 p_select_clause IN VARCHAR2,
68 p_from_clause IN VARCHAR2,
69 p_where_clause IN VARCHAR2,
70 p_other_clause IN VARCHAR2,
71 p_block_level IN VARCHAR2,
72 p_CREATED_BY IN NUMBER,
73 p_CREATION_DATE IN DATE ,
74 p_LAST_UPDATED_BY IN NUMBER,
75 p_LAST_UPDATE_DATE IN DATE ,
76 p_LAST_UPDATE_LOGIN IN NUMBER,
77 p_OBJECT_VERSION_NUMBER IN NUMBER DEFAULT NULL,
78 p_APPLICATION_ID IN NUMBER,
79 x_Profile_Variables_Rec OUT NOCOPY ProfVar_Rec_Type
80 )
81 IS
82 BEGIN
83 x_profile_variables_rec.block_id := p_block_id;
84 x_Profile_Variables_Rec.block_name := p_block_name;
85 x_Profile_Variables_Rec.block_name_code := p_block_name_code;
86 x_Profile_Variables_Rec.description := p_description;
87 x_Profile_Variables_Rec.currency_code := p_currency_code;
88 x_Profile_Variables_Rec.seeded_flag := p_seeded_flag;
89 --x_Profile_Variables_Rec.form_function_id := p_form_function_id;
90 x_Profile_Variables_Rec.object_code := p_object_code;
91 x_Profile_Variables_Rec.start_date_active := p_start_date_active;
92 x_Profile_Variables_Rec.end_date_active := p_end_date_active;
93 x_Profile_Variables_Rec.select_clause := p_select_clause;
94 x_Profile_Variables_Rec.from_clause := p_from_clause;
95 x_Profile_Variables_Rec.where_clause := p_where_clause;
96 x_Profile_Variables_Rec.other_clause := p_other_clause;
97 x_Profile_Variables_Rec.block_level := p_block_level;
98 x_Profile_Variables_Rec.created_by := p_created_by;
99 x_Profile_Variables_Rec.creation_date := p_creation_date;
100 x_Profile_Variables_Rec.last_updated_by := p_last_updated_by;
101 x_Profile_Variables_Rec.last_update_date := p_last_update_date;
102 x_Profile_Variables_Rec.last_update_login := p_last_update_login;
103 x_Profile_Variables_Rec.object_version_number := p_object_version_number;
104 x_Profile_Variables_Rec.application_id := p_application_id;
105
106 END Convert_Columns_to_Rec;
107
108 ----------------------------------------------------------------------------
109 -- Start of Public Procedure Body Create_Prof_Var
110 ----------------------------------------------------------------------------
111
112 PROCEDURE Create_Profile_Variable(
113 p_api_version_number IN NUMBER,
114 p_init_msg_list IN VARCHAR2,
115 p_commit IN VARCHAR2,
116 p_validation_level IN VARCHAR2,
117 x_return_status OUT NOCOPY VARCHAR2,
118 x_msg_count OUT NOCOPY NUMBER,
119 x_msg_data OUT NOCOPY VARCHAR2,
120 p_block_name IN VARCHAR2,
121 p_block_name_code IN VARCHAR2,
122 p_description IN VARCHAR2,
123 p_sql_stmnt IN VARCHAR2,
124 p_seeded_flag IN VARCHAR2,
125 p_start_date_active IN DATE ,
126 p_end_date_active IN DATE ,
127 p_currency_code IN VARCHAR2,
128 --p_form_function_id IN NUMBER := FND_API.G_MISS_NUM,
129 p_object_code IN VARCHAR2,
130 p_select_clause IN VARCHAR2,
131 p_from_clause IN VARCHAR2,
132 p_where_clause IN VARCHAR2,
133 p_other_clause IN VARCHAR2,
134 p_block_level IN VARCHAR2,
135 p_CREATED_BY IN NUMBER,
136 p_CREATION_DATE IN DATE,
137 p_LAST_UPDATED_BY IN NUMBER,
138 p_LAST_UPDATE_DATE IN DATE,
139 p_LAST_UPDATE_LOGIN IN NUMBER,
140 x_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
141 p_APPLICATION_ID IN NUMBER,
142 x_block_id OUT NOCOPY NUMBER )
143 IS
144 l_prof_var_rec ProfVar_Rec_Type;
145 l_seeded_flag VARCHAR2(2) := 'N';
146 BEGIN
147
148 Convert_Columns_to_Rec (
149 p_block_name => p_block_name,
150 p_block_name_code => p_block_name_code,
151 p_description => p_description,
152 p_sql_stmnt => p_sql_stmnt,
153 p_seeded_flag => p_seeded_flag,
154 p_start_date_active => p_start_date_active,
155 p_end_date_active => p_end_date_active,
156 p_currency_code => p_currency_code,
157 --p_form_function_id => p_form_function_id,
158 p_object_code => p_object_code,
159 p_select_clause => p_select_clause,
160 p_from_clause => p_from_clause,
161 p_where_clause => p_where_clause,
162 p_other_clause => p_other_clause,
163 p_block_level => p_block_level,
164 p_CREATED_BY => p_CREATED_BY,
165 p_CREATION_DATE => p_CREATION_DATE,
166 p_LAST_UPDATED_BY => p_LAST_UPDATED_BY,
167 p_LAST_UPDATE_DATE => p_LAST_UPDATE_DATE,
168 p_LAST_UPDATE_LOGIN => p_LAST_UPDATE_LOGIN,
169 p_APPLICATION_ID => p_APPLICATION_ID,
170 x_Profile_Variables_Rec => l_prof_var_rec
171 );
172
173
174 Create_Profile_Variable(
175 p_api_version_number => p_api_version_number,
176 p_init_msg_list => p_init_msg_list,
177 p_commit => p_Commit,
178 p_validation_level => p_validation_level,
179 p_prof_var_rec => l_prof_var_rec,
180 x_object_version_number => x_object_version_number,
181 x_msg_data => x_msg_data,
182 x_msg_count => x_msg_count,
183 x_return_status => x_return_status,
184 x_block_id => x_block_id );
185
186 END Create_Profile_Variable;
187 ---Actual procedure starts here
188
189 PROCEDURE Create_Profile_Variable(
190 P_Api_Version_Number IN NUMBER,
191 P_Init_Msg_List IN VARCHAR2,
192 P_Commit IN VARCHAR2,
193 P_Validation_Level IN NUMBER ,
194 P_Prof_Var_Rec IN ProfVar_Rec_Type,
195 X_Return_Status OUT NOCOPY VARCHAR2,
196 X_Msg_Count OUT NOCOPY NUMBER,
197 X_Msg_Data OUT NOCOPY VARCHAR2,
198 X_Block_Id OUT NOCOPY NUMBER ,
199 x_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER
200 )
201 IS
202
203 l_api_name CONSTANT VARCHAR2(60) := 'Create_Profile_Variable';
204 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
205 l_api_version CONSTANT NUMBER := 1.0 ;
206
207 -- pass the parameter record type into a local
208 -- record type so that you can assign values
209
210 l_prof_var_rec ProfVar_Rec_Type := p_prof_var_rec;
211 l_pvt_prof_var_rec CSC_Profile_Variable_PVT.ProfVar_Rec_Type;
212 BEGIN
213
214 -- Standard Start of API Savepoint
215 SAVEPOINT Create_Profile_Variable_Pub ;
216
217 -- Standard Call to check API compatibility
218 IF NOT FND_API.Compatible_API_Call( l_api_version,
219 p_api_version_number,
220 l_api_name,
221 G_PKG_NAME )
222 THEN
223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
224 END IF ;
225
226 -- Initialize the message list if p_msg_list is set to TRUE
227 IF FND_API.To_Boolean(p_init_msg_list) THEN
228 FND_MSG_PUB.initialize ;
229 END IF ;
230
231
232 Convert_pub_to_pvt_Rec (
233 P_Profile_Variables_Rec => p_prof_var_Rec,
234 x_pvt_Profile_Variables_rec => l_pvt_prof_var_Rec
235 );
236
237
238 -- Initialize the API Return Success to True
239 x_return_status := FND_API.G_RET_STS_SUCCESS ;
240
241 CSC_Profile_Variable_PVT.Create_Profile_Variable(
242 p_api_version_number => 1.0,
243 p_init_msg_list => FND_API.G_FALSE,
244 p_commit => FND_API.G_FALSE,
245 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
246 x_return_status => x_return_status,
247 x_msg_count => x_msg_count,
248 x_msg_data => x_msg_data,
249 p_prof_var_rec => l_pvt_Prof_Var_Rec,
250 x_block_id => x_block_id ,
251 x_object_version_number => x_object_version_number );
252
253 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
254 RAISE FND_API.G_EXC_ERROR;
255 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257 END IF;
258
259
260 -- End of API Body
261 -- Standard Check of p_commit
262 IF FND_API.To_Boolean( p_commit ) THEN
263 COMMIT WORK ;
264 END IF ;
265
266 -- Standard call to get message count and if count is 1 , get message info
267 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
268 p_data => x_msg_data) ;
269
270 -- Begin Exception Handling
271 EXCEPTION
272 WHEN FND_API.G_EXC_ERROR THEN
273 ROLLBACK TO Create_Profile_Variable_Pub ;
274 x_return_status := FND_API.G_RET_STS_ERROR ;
275 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
276 p_data => x_msg_data) ;
277 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
278 ROLLBACK TO Create_Profile_Variable_Pub ;
279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
280 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
281 p_data => x_msg_data) ;
282 WHEN OTHERS THEN
283 ROLLBACK TO Create_Profile_Variable_Pub ;
284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
285 IF FND_MSG_PUB.Check_Msg_Level
286 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
287 THEN
288 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
289 END IF ;
290 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
291 p_data => x_msg_data) ;
292
293 END Create_Profile_Variable ;
294
295 PROCEDURE Create_table_column(
296 P_Api_Version_Number IN NUMBER,
297 P_Init_Msg_List IN VARCHAR2,
298 P_Commit IN VARCHAR2,
299 P_Validation_level IN NUMBER,
300 p_Table_Column_Tbl IN CSC_Profile_Variable_pvt.Table_Column_Tbl_Type,
301 --p_Sql_Stmnt_For_Drilldown IN VARCHAR2 := FND_API.G_MISS_CHAR,
302 --p_BLOCK_ID IN NUMBER,
303 X_TABLE_COLUMN_ID OUT NOCOPY NUMBER,
304 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
305 X_Return_Status OUT NOCOPY VARCHAR2,
306 X_Msg_Count OUT NOCOPY NUMBER,
307 X_Msg_Data OUT NOCOPY VARCHAR2
308 )
309 IS
310 l_api_name CONSTANT VARCHAR2(30) := 'Create_Table_Column';
311 l_api_version_number CONSTANT NUMBER := 1.0;
312 BEGIN
313 -- Standard Start of API savepoint
314 SAVEPOINT CREATE_Table_Column_PUB;
315
316 -- Standard call to check for call compatibility.
317 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
318 p_api_version_number,
319 l_api_name,
320 G_PKG_NAME)
321 THEN
322 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
323 END IF;
324
325
326 -- Initialize message list if p_init_msg_list is set to TRUE.
327 IF FND_API.to_Boolean( p_init_msg_list )
328 THEN
329 FND_MSG_PUB.initialize;
330 END IF;
331
332
333 -- Initialize API return status to SUCCESS
334 x_return_status := FND_API.G_RET_STS_SUCCESS;
335
336 --
337 -- API body
338 --
339
340 -- Calling Private package: Create_Table_Column
341 -- Hint: Primary key needs to be returned
342
343 CSC_Profile_Variable_PVT.Create_table_column(
344 P_Api_Version_Number => l_api_version_number,
345 P_Init_Msg_List => FND_API.G_FALSE,
346 P_Commit => FND_API.G_FALSE,
347 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
348 p_Table_Column_Tbl => p_table_column_tbl,
349 --p_Sql_Stmnt_For_Drilldown => p_sql_stmnt_for_drilldown,
350 --p_BLOCK_ID => p_block_id,
351 X_TABLE_COLUMN_ID => x_TABLE_COLUMN_ID,
352 X_object_version_number => x_object_version_number,
353 X_Return_Status => x_return_status,
354 X_Msg_Count => x_msg_count,
355 X_Msg_Data => x_msg_data );
356
357 -- Check return status from the above procedure call
361 raise FND_API.G_EXC_UNEXPECTED_ERROR;
358 IF x_return_status = FND_API.G_RET_STS_ERROR then
359 raise FND_API.G_EXC_ERROR;
360 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
362 END IF;
363
364 --
365 -- End of API body.
366 --
367
368 -- Standard check for p_commit
369 IF FND_API.to_Boolean( p_commit )
370 THEN
371 COMMIT WORK;
372 END IF;
373
374
375 -- Standard call to get message count and if count is 1, get message info.
376 FND_MSG_PUB.Count_And_Get
377 ( p_count => x_msg_count,
378 p_data => x_msg_data
379 );
380
381 EXCEPTION
382 WHEN FND_API.G_EXC_ERROR THEN
383 ROLLBACK TO CREATE_Table_Column_PUB ;
384 x_return_status := FND_API.G_RET_STS_ERROR ;
385 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
386 p_data => x_msg_data) ;
387 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
388 ROLLBACK TO CREATE_Table_Column_PUB ;
389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
390 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
391 p_data => x_msg_data) ;
392 WHEN OTHERS THEN
393 ROLLBACK TO CREATE_Table_Column_PUB ;
394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
395 IF FND_MSG_PUB.Check_Msg_Level
396 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
397 THEN
398 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
399 END IF ;
400 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
401 p_data => x_msg_data) ;
402
403
404 End Create_table_column;
405
406
407 -----------------------------------------------------------------
408 -- This procedure updates CS_PROF_BLOCKS
409 ------------------------------------------------------------------
410
411 -- update without record type
412
413 PROCEDURE Update_Profile_Variable(
414 p_api_version_number IN NUMBER,
415 p_init_msg_list IN VARCHAR2,
416 p_commit IN VARCHAR2,
417 p_validation_level IN VARCHAR2,
418 x_return_status OUT NOCOPY VARCHAR2,
419 x_msg_count OUT NOCOPY NUMBER,
420 x_msg_data OUT NOCOPY VARCHAR2,
421 p_block_id IN NUMBER ,
422 p_block_name IN VARCHAR2,
423 p_block_name_code IN VARCHAR2,
424 p_description IN VARCHAR2,
425 p_currency_code IN VARCHAR2,
426 p_sql_stmnt IN VARCHAR2,
427 p_seeded_flag IN VARCHAR2,
428 --p_form_function_id IN NUMBER := FND_API.G_MISS_NUM,
429 p_object_code IN VARCHAR2,
430 p_start_date_active IN DATE,
431 p_end_date_active IN DATE,
432 p_select_clause IN VARCHAR2,
433 p_from_clause IN VARCHAR2,
434 p_where_clause IN VARCHAR2,
435 p_other_clause IN VARCHAR2,
436 p_block_level IN VARCHAR2,
437 p_CREATED_BY IN NUMBER,
438 p_CREATION_DATE IN DATE,
439 p_LAST_UPDATED_BY IN NUMBER,
440 p_LAST_UPDATE_DATE IN DATE,
441 p_LAST_UPDATE_LOGIN IN NUMBER,
442 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
443 p_APPLICATION_ID IN NUMBER)
444 IS
445 l_prof_var_rec ProfVar_Rec_Type;
446
447 BEGIN
448 -- convert all the parameters into a record type
449
450 Convert_Columns_to_Rec (
451 p_block_name => p_block_name,
452 p_block_name_code => p_block_name_code,
453 p_description => p_description,
454 p_sql_stmnt => p_sql_stmnt,
455 p_seeded_flag => p_seeded_flag,
456 p_start_date_active => p_start_date_active,
457 p_end_date_active => p_end_date_active,
458 p_currency_code => p_currency_code,
459 --p_form_function_id => p_form_function_id,
460 p_object_code => p_object_code,
461 p_select_clause => p_select_clause,
462 p_from_clause => p_from_clause,
463 p_where_clause => p_where_clause,
464 p_other_clause => p_other_clause,
465 p_block_level => p_block_level,
466 p_CREATED_BY => p_CREATED_BY,
467 p_CREATION_DATE => p_CREATION_DATE,
468 p_LAST_UPDATED_BY => p_LAST_UPDATED_BY,
469 p_LAST_UPDATE_DATE => p_LAST_UPDATE_DATE,
470 p_LAST_UPDATE_LOGIN => p_LAST_UPDATE_LOGIN,
471 p_OBJECT_VERSION_NUMBER => px_OBJECT_VERSION_NUMBER,
472 p_APPLICATION_ID => p_APPLICATION_ID,
473 x_Profile_Variables_Rec => l_prof_var_rec
474 );
475
476
477 Update_Profile_Variable(
478 p_api_version_number => p_api_version_number,
479 p_init_msg_list => p_init_msg_list,
480 p_commit => p_Commit ,
481 p_validation_level => p_validation_level,
482 p_prof_var_rec => l_prof_var_Rec,
483 px_OBJECT_VERSION_NUMBER => px_OBJECT_VERSION_NUMBER,
484 x_msg_data => x_msg_data,
485 x_msg_count => x_msg_count,
486 x_return_status => x_return_status );
487
488 End;
489
493 p_api_version_number IN VARCHAR2,
490 -- Update with record type...
491
492 PROCEDURE Update_Profile_Variable (
494 p_init_msg_list IN VARCHAR2,
495 p_commit IN VARCHAR2,
496 p_validation_level IN VARCHAR2,
497 p_prof_var_rec IN ProfVar_Rec_Type,
498 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER ,
499 x_return_status OUT NOCOPY VARCHAR2,
500 x_msg_data OUT NOCOPY VARCHAR2,
501 x_msg_count OUT NOCOPY NUMBER )
502 IS
503 l_api_name CONSTANT VARCHAR2(30) := 'Update_Profile_Variable' ;
504 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
505 l_api_version CONSTANT NUMBER := 1.0 ;
506
507 l_prof_var_rec ProfVar_Rec_Type := p_prof_var_rec;
508 l_pvt_prof_var_rec CSC_Profile_Variable_PVT.ProfVar_Rec_Type;
509 BEGIN
510
511 -- Standard Start of API Savepoint
512 SAVEPOINT Update_Profile_Variable_Pub ;
513
514 -- Standard Call to check API compatibility
515 IF NOT FND_API.Compatible_API_Call( l_api_version,
516 p_api_version_number,
517 l_api_name,
518 G_PKG_NAME )
519 THEN
520 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
521 END IF ;
522
523 -- Initialize the message list if p_msg_list is set to TRUE
524 IF FND_API.To_Boolean(p_init_msg_list) THEN
525 FND_MSG_PUB.initialize ;
526 END IF ;
527
528
529 -- Initialize the API Return Success to True
530 x_return_status := FND_API.G_RET_STS_SUCCESS ;
531
532 Convert_pub_to_pvt_Rec (
533 P_Profile_Variables_Rec => p_prof_var_Rec,
534 x_pvt_Profile_Variables_rec => l_pvt_prof_var_Rec
535 );
536
537
538
539 -- Call Private API to Update
540
541 CSC_Profile_Variable_PVT.Update_Profile_Variable(
542 p_api_version_number => 1.0,
543 p_init_msg_list => FND_API.G_FALSE,
544 p_commit => FND_API.G_FALSE,
545 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
546 p_prof_var_rec => l_pvt_prof_var_rec,
547 px_OBJECT_VERSION_NUMBER => px_OBJECT_VERSION_NUMBER,
548 x_return_status => x_return_status,
549 x_msg_count => x_msg_count,
550 x_msg_data => x_msg_data
551 );
552
553 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
554 RAISE FND_API.G_EXC_ERROR;
555 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
557 END IF;
558
559 -- End of API Body
560 -- Standard Check of p_commit
561 IF FND_API.To_Boolean( p_commit ) THEN
562 COMMIT WORK ;
563 END IF ;
564
565 -- Standard call to get message count and if count is 1 , get message info
566 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
567 p_data => x_msg_data) ;
568
569 -- Begin Exception Handling
570 EXCEPTION
571 WHEN FND_API.G_EXC_ERROR THEN
572 ROLLBACK TO Update_Profile_Variable_Pub ;
573 x_return_status := FND_API.G_RET_STS_ERROR ;
574 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
575 p_data => x_msg_data) ;
576
577 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
578 ROLLBACK TO Update_Profile_Variable_Pub ;
579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
580 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
581 p_data => x_msg_data) ;
582 WHEN OTHERS THEN
583 ROLLBACK TO Update_Profile_Variable_Pub ;
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
585 IF FND_MSG_PUB.Check_Msg_Level
586 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
587 THEN
588 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
589 END IF ;
590 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
591 p_data => x_msg_data) ;
592
593 END Update_Profile_Variable;
594
595
596 PROCEDURE Update_table_column(
597 P_Api_Version_Number IN NUMBER,
598 P_Init_Msg_List IN VARCHAR2,
599 P_Commit IN VARCHAR2,
600 P_Validation_Level IN NUMBER,
601 p_Table_Column_Rec IN CSC_Profile_Variable_PVT.Table_Column_Rec_Type,
602 --p_Sql_Stmnt_For_Drilldown IN VARCHAR2 := FND_API.G_MISS_CHAR,
603 --p_BLOCK_ID IN NUMBER := FND_API.G_MISS_NUM,
604 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER ,
605 X_Return_Status OUT NOCOPY VARCHAR2,
606 X_Msg_Count OUT NOCOPY NUMBER,
607 X_Msg_Data OUT NOCOPY VARCHAR2
608 )
609
610 IS
611 l_api_name CONSTANT VARCHAR2(30) := 'Update_table_column';
612 l_api_version_number CONSTANT NUMBER := 1.0;
613 BEGIN
614 -- Standard Start of API savepoint
615 SAVEPOINT UPDATE_Table_Column_PUB;
616
617 -- Standard call to check for call compatibility.
618 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
619 p_api_version_number,
620 l_api_name,
621 G_PKG_NAME)
622 THEN
623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
624 END IF;
625
626
627 -- Initialize message list if p_init_msg_list is set to TRUE.
628 IF FND_API.to_Boolean( p_init_msg_list )
629 THEN
630 FND_MSG_PUB.initialize;
631 END IF;
632
633 -- Initialize API return status to SUCCESS
634 x_return_status := FND_API.G_RET_STS_SUCCESS;
635
636 --
637 -- API body
638 --
639
640 CSC_profile_variable_PVT.Update_table_column(
641 P_Api_Version_Number => l_api_version_number,
642 P_Init_Msg_List => FND_API.G_FALSE,
643 P_Commit => p_commit,
644 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
645 p_Table_Column_REC => p_table_Column_REC,
646 --p_Sql_Stmnt_for_drilldown => p_sql_stmnt_for_drilldown,
647 px_OBJECT_VERSION_NUMBER => px_OBJECT_VERSION_NUMBER,
648 X_Return_Status => x_return_status,
649 X_Msg_Count => x_msg_count,
650 X_Msg_Data => x_msg_data);
651
652 -- Check return status from the above procedure call
653 IF x_return_status = FND_API.G_RET_STS_ERROR then
654 raise FND_API.G_EXC_ERROR;
655 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
656 raise FND_API.G_EXC_UNEXPECTED_ERROR;
657 END IF;
658
659 --
660 -- End of API body
661 --
662
663 -- Standard check for p_commit
664 IF FND_API.to_Boolean( p_commit )
665 THEN
666 COMMIT WORK;
667 END IF;
668
669
670 -- Standard call to get message count and if count is 1, get message info.
671 FND_MSG_PUB.Count_And_Get
672 ( p_count => x_msg_count,
673 p_data => x_msg_data
674 );
675
676 EXCEPTION
677 WHEN FND_API.G_EXC_ERROR THEN
678 ROLLBACK TO UPDATE_Table_Column_PUB ;
679 x_return_status := FND_API.G_RET_STS_ERROR ;
680 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
681 p_data => x_msg_data) ;
682 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
683 ROLLBACK TO UPDATE_Table_Column_PUB ;
684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
685 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
686 p_data => x_msg_data) ;
687 WHEN OTHERS THEN
688 ROLLBACK TO UPDATE_Table_Column_PUB ;
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
690 IF FND_MSG_PUB.Check_Msg_Level
691 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
692 THEN
693 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
694 END IF ;
695 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
696 p_data => x_msg_data) ;
697
698
699 End Update_table_column;
700 END;