[Home] [Help]
PACKAGE BODY: APPS.CSC_PROFILE_VARIABLE_PVT
Source
1 PACKAGE BODY CSC_Profile_Variable_Pvt AS
2 /* $Header: cscvpvab.pls 120.3 2006/01/29 23:51:11 adhanara noship $ */
3
4
5 /*************GLOBAL VARIABLES*************************/
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSC_Profile_Variable_PVT' ;
8
9 /* ************************************************************************* *
10 * Forward Declaration of Local Procedures *
11 * *
12 * The following local procedures are called by the APIs in this package. *
13 * *
14 * ************************************************************************* */
15
16 --------------------------------------------------------------------------
17 -- Procedure Build_Sql_Stmnt
18 -- Description: Concatenates the select_Clause, from_clause, where_clause
19 -- and Other_clause to build an sql statement which will be stored in
20 -- the sql_statement column in cs_prof_blocks table.
21 -- Input Parameters
22 -- p_api_name, standard parameter for writting messages
23 -- p_validation_mode, whether an update or an insert uses CSC_CORE_UTILS_PVT.G_UPDATE
24 -- or CSC_CORE_UTILS_PVT.G_CREATE global variable
25 -- p_sql_statement, concatented field using select_Clause, from_clause
26 -- where_clause and Other_Clause columns using the Build_Sql_Stmnt
27 -- procedure
28 -- Out Parameters
29 -- x_return_status, standard parameter for the return status
30 --------------------------------------------------------------------------
31
32 PROCEDURE Build_Sql_Stmnt
33 ( p_api_name IN VARCHAR2,
34 p_select_clause IN VARCHAR2,
35 p_from_clause IN VARCHAR2,
36 p_where_clause IN VARCHAR2,
37 p_other_clause IN VARCHAR2,
38 x_sql_Stmnt OUT NOCOPY VARCHAR2,
39 x_return_status OUT NOCOPY VARCHAR2 )
40 IS
41 l_sql_stmnt VARCHAR2(2000);
42 BEGIN
43 -- initialize the return status
44 x_return_status := FND_API.G_RET_STS_SUCCESS;
45
46 -- check if the select_clause and the from_Clause
47 -- is NULL or missing, if so we cannot form an
48 -- sql_statement.
49
50 IF (p_Select_Clause IS NULL ) and
51 ( p_Select_Clause = CSC_CORE_UTILS_PVT.G_MISS_CHAR ) and
52 ( p_from_Clause IS NULL ) and
53 ( p_from_Clause = CSC_CORE_UTILS_PVT.G_MISS_CHAR )
54 THEN
55 -- invalid arguments exception
56 x_return_status := FND_API.G_RET_STS_ERROR;
57 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg( p_api_name => p_api_name,
58 p_argument_value => p_select_clause||' '||p_from_clause,
59 p_argument => 'p_Sql_Stmnt'
60 );
61 END IF;
62
63 CSC_CORE_UTILS_PVT.Build_Sql_Stmnt
64 ( p_select_clause => p_SELECT_CLAUSE,
65 p_from_clause => p_FROM_CLAUSE,
66 p_where_clause => p_WHERE_CLAUSE,
67 p_other_clause => p_OTHER_CLAUSE,
68 X_sql_Stmnt => X_SQL_STMNT,
69 X_return_status => x_return_status );
70
71 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
72 x_return_status := FND_API.G_RET_STS_ERROR;
73 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(
74 p_api_name => p_api_name,
75 p_argument_value => p_select_clause||' '||p_from_clause,
76 p_argument => 'P_SQL_STMNT'
77 );
78 END IF;
79
80
81 END Build_Sql_Stmnt;
82 /****************************
83 PROCEDURE Build_Drilldown_Sql_Stmnt(
84 p_block_id IN NUMBER,
85 P_TABLE_COLUMN_TBL IN Table_Column_Tbl_Type,
86 x_sql_stmnt OUT NOCOPY VARCHAR2 )
87 IS
88 l_select_clause VARCHAR2(1200) := 'SELECT ';
89 l_from_clause VARCHAR2(800) := 'FROM ';
90 l_table_count NUMBER := 0;
91 l_column_count NUMBER := 0;
92 l_alias VARCHAR2(80) := 'FIRST';
93 l_table_name VARCHAR2(600) := 'FIRST';
94 l_where_clause VARCHAR2(2000);
95 l_sql_stmnt VARCHAR2(2000);
96
97 Cursor C2 is
98 select where_clause
99 from csc_prof_blocks_b
100 where block_id = p_block_id;
101
102 BEGIN
103
104 FOR i in 1..p_table_column_tbl.count LOOP
105 IF ( l_table_name <> p_table_column_tbl(i).table_name OR
106 l_alias <> p_table_column_tbl(i).table_alias ) THEN
107 l_table_count := l_table_count + 1;
108 l_table_name := p_table_column_tbl(i).table_name;
109 l_alias := p_table_column_tbl(i).table_alias;
110 if l_table_count = 1 then
111 l_from_Clause := l_from_clause||' '||p_table_column_tbl(i).table_name||' '||l_alias;
112 else
113 l_from_Clause := l_from_clause||', '||p_table_column_tbl(i).table_name||' '||l_alias;
114 end if;
115 END IF;
116 l_column_count := l_column_count + 1;
117 IF l_column_count = 1 THEN
118 if l_alias is not null then
119 l_select_clause:= l_select_clause||' '||l_alias||'.'||p_table_column_tbl(i).column_name;
120 else
121 l_select_clause := l_select_clause||' '||p_table_column_tbl(i).column_name;
122 end if;
123 ELSE
124 if l_alias is not null then
125 l_select_clause := l_select_clause||', '||l_alias||'.'||p_table_column_tbl(i).column_name;
126 else
127 l_select_clause := l_select_clause||', '||p_table_column_tbl(i).column_name;
128 end if;
129 END IF;
130 END LOOP;
131
132 l_sql_stmnt := l_select_clause||' '||l_from_Clause;
133 Open c2;
134 Fetch C2 into l_where_clause;
135 Close C2;
136 IF l_where_clause is not null THEN
137 l_sql_stmnt := l_sql_stmnt||' WHERE '||l_where_clause;
138 END IF;
139 x_sql_stmnt := l_sql_stmnt;
140
141 END Build_Drilldown_Sql_Stmnt;
142 ****************************/
143 PROCEDURE Build_Drilldown_Sql_Stmnt(
144 p_block_id IN NUMBER,
145 P_TABLE_COLUMN_TBL IN Table_Column_Tbl_Type,
146 x_sql_stmnt OUT NOCOPY VARCHAR2 )
147 IS
148 l_select_clause VARCHAR2(1200) := 'SELECT ';
149 l_from_clause VARCHAR2(800) := 'FROM ';
150 l_table_count NUMBER := 0;
151 l_column_count NUMBER := 0;
152 l_alias VARCHAR2(80) := 'FIRST';
153 l_table_name VARCHAR2(600) := 'FIRST';
154 l_where_clause VARCHAR2(2000);
155 l_other_clause VARCHAR2(2000);
156 /* increased to 4000 for bug 4205145 */
157 l_sql_stmnt VARCHAR2(4000);
158
159 x_table_name varchar2(100);
160 x_column_name varchar2(100);
161 v_data_type varchar2(100);
162
163 -- NOTE : This cursor might have multiple rows of the same
164 -- table under different owners. Since we do not know the owner
165 -- of the table in SQL statement, we assume that the datatypes
166 -- and columns in the diff copies would be same and pick up only
167 -- the first match.
168
169 cursor datatype is
170 select data_type from sys.all_Tab_columns
171 where table_name = x_table_name
172 and column_name = x_column_name;
173
174
175 -- Changed cursor to include other_clause and from_clause
176 -- Bug 1395031
177 Cursor C2 is
178 select where_clause, other_clause,from_clause
179 from csc_prof_blocks_b
180 where block_id = p_block_id;
181
182 Cursor C3 is
183 select count(distinct column_name)
184 from sys.all_tab_columns
185 where table_name = 'CSC_PROF_DRILLDOWN_V'
186 and column_name <> 'ROW_ID';
187
188 l_count Number := 0;
189 l_diff_count Number := 0;
190 l_drilldown_count Number := 0;
191
192 BEGIN
193 --- To restrict the no. of columns in the select clause equal to
194 --- the columns in CSC_PROF_DRILLDOWN_V
195 Open C3;
196 Fetch C3 into l_drilldown_count;
197 Close C3;
198
199 l_count := p_table_column_tbl.count;
200 If p_table_column_tbl.count > l_drilldown_count then
201 l_count := l_drilldown_count;
202 end If;
203
204 FOR i in 1..l_count LOOP
205
206 x_table_name := p_table_column_tbl(i).table_name;
207 x_column_name := p_table_column_tbl(i).column_name;
208
209 open datatype;
210 fetch datatype into v_data_type;
211 close datatype;
212
213 IF ( l_table_name <> p_table_column_tbl(i).table_name OR
214 l_alias <> p_table_column_tbl(i).table_alias ) THEN
215 l_table_count := l_table_count + 1;
216 l_table_name := p_table_column_tbl(i).table_name;
217 l_alias := p_table_column_tbl(i).table_alias;
218 if l_table_count = 1 then
219 l_from_Clause := l_from_clause||' '||p_table_column_tbl(i).table_name||' '||l_alias;
220 else
221 l_from_Clause := l_from_clause||', '||p_table_column_tbl(i).table_name||' '||l_alias;
222 end if;
223 END IF;
224 l_column_count := l_column_count + 1;
225 IF l_column_count = 1 THEN
226 if l_alias is not null then
227 IF v_data_type = 'VARCHAR2' then
228 l_select_clause:= l_select_clause||' '||l_alias||'.'||p_table_column_tbl(i).column_name || ' as Column1';
229 ELSE
230 l_select_clause:= l_select_clause||' to_char('||l_alias||'.'||p_table_column_tbl(i).column_name||')' || ' as Column1';
231 END IF;
232 else
233 IF v_data_type = 'VARCHAR2' then
234 l_select_clause := l_select_clause||' '||p_table_column_tbl(i).column_name || ' as Column1';
235 ELSE
236 l_select_clause := l_select_clause||' to_char('||p_table_column_tbl(i).column_name||')' || ' as Column1';
237 END IF;
238 end if;
239 ELSE
240 if l_alias is not null then
241 IF v_data_type = 'VARCHAR2' then
242 l_select_clause := l_select_clause||', '||l_alias||'.'||p_table_column_tbl(i).column_name || ' as Column'||to_char(l_column_count);
243 ELSE
244 l_select_clause := l_select_clause||', to_char('||l_alias||'.'||p_table_column_tbl(i).column_name||')' || ' as Column'||to_char(l_column_count);
245 END IF;
246 else
247 IF v_data_type = 'VARCHAR2' then
248 l_select_clause := l_select_clause||', '||p_table_column_tbl(i).column_name || ' as Column'||to_char(l_column_count);
249 ELSE
250 l_select_clause := l_select_clause||', to_char('||p_table_column_tbl(i).column_name||')' || ' as Column'||to_char(l_column_count);
251 END IF;
252 end if;
253 END IF;
254 END LOOP;
255
256 l_diff_count := l_drilldown_count - l_count;
257 if l_count > 0 and l_diff_count > 0 Then
258 For l_index in 1..l_diff_count Loop
259 l_column_count := l_column_count + 1;
260 l_select_clause := l_select_clause||', '|| 'NULL' || ' as Column'||to_char(l_column_count);
261 End Loop;
262 End If;
263
264 -- l_sql_stmnt := l_select_clause||' '||l_from_Clause;
265 -- commented to use the from_clause from table.
266 -- Bug 1395031
267 Open c2;
268 Fetch C2 into l_where_clause,l_other_clause,l_from_clause;
269 Close C2;
270 IF l_from_clause is not null THEN
271 l_sql_stmnt := l_select_clause||' FROM ' ||l_from_clause;
272 END IF;
273 IF l_where_clause is not null THEN
274 l_sql_stmnt := l_sql_stmnt||' WHERE '||l_where_clause ;
275 END IF;
276 /*BUG 1900100 - CAN'T DEFINE A CUSTOMER CARE PROFILE VARIABLE THAT USES
277 AN "ORDER BY" CLAUSE - for this bug commenting the statement below*/
278 /*IF l_other_clause is not null THEN
279 l_sql_stmnt := l_sql_stmnt||' '||l_other_clause ;
280 END IF;
281 */
282 x_sql_stmnt := l_sql_stmnt;
283
284 END Build_Drilldown_Sql_Stmnt;
285
286 /*************************************************************************/
287 PROCEDURE Build_PLSQL_Table(
288 p_block_id IN NUMBER,
289 x_table_column_tbl OUT NOCOPY Table_Column_Tbl_Type )
290 IS
291 Cursor C1 is
292 Select table_name,column_name,label,alias_name
293 from csc_prof_table_columns_vl
294 where block_id = p_block_id
295 order by decode(drilldown_column_flag,'Y',1,2),column_sequence;
296 l_count Number := 0;
297 BEGIN
298 x_table_column_tbl.delete;
299 For c1_rec in c1 Loop
300 l_count := l_count + 1;
301 x_table_column_tbl(l_Count).TABLE_NAME := c1_rec.table_name;
302 x_table_column_tbl(l_Count).TABLE_ALIAS:= c1_rec.alias_name;
303 x_table_column_tbl(l_Count).COLUMN_NAME:= c1_rec.column_name;
304 x_table_column_tbl(l_Count).LABEL := c1_rec.label;
305 End loop;
306 END Build_PLSQL_Table;
307
308
309 PROCEDURE Build_Drilldown_Sql_Stmnt(
310 p_block_id NUMBER,
311 x_sql_stmnt OUT NOCOPY VARCHAR2 )
312 IS
313 l_table_column_tbl Table_Column_Tbl_Type;
314 BEGIN
315
316 Build_PLSQL_Table(
317 p_block_id,
318 l_table_column_tbl );
319
320 Build_Drilldown_Sql_Stmnt(
321 p_block_id ,
322 l_TABLE_COLUMN_TBL ,
323 x_sql_stmnt );
324
325 --replace :party_id, cust_acct_id,cust_acct_org_id
326 --with :global.party_id, :global.cust_acct_id,:global.cust_acct_org_id
327 --respectively in sql_Stmnt_for_drilldown which will be used in
328 --drill down form.
329 x_sql_stmnt := replace(x_sql_stmnt,':party_id',':global.csc_party_id');
330 x_sql_stmnt := replace(x_sql_stmnt,':cust_account_id',':global.csc_cust_account_id');
331 x_sql_stmnt := replace(x_sql_Stmnt,':org_id',':global.csc_org_id');
332 x_sql_stmnt := replace(x_sql_Stmnt,':employee_id',':global.csc_party_id');
333 END;
334
335
336 ----------------------------------------------------------------------------
337 -- Start of Procedure Body Convert_Columns_to_Rec
338 ----------------------------------------------------------------------------
339
340 PROCEDURE Convert_Columns_to_Rec (
341 p_block_id IN NUMBER := NULL,
342 p_block_name IN VARCHAR2 ,
343 p_block_name_code IN VARCHAR2 ,
344 p_description IN VARCHAR2 ,
345 p_sql_stmnt IN VARCHAR2 ,
346 p_batch_sql_stmnt IN VARCHAR2,
347 p_seeded_flag IN VARCHAR2 ,
348 p_sql_stmnt_for_drilldown IN VARCHAR2:=NULL ,
349 p_start_date_active IN DATE ,
350 p_end_date_active IN DATE ,
351 p_currency_code IN VARCHAR2,
352 p_object_code IN VARCHAR2 :=NULL ,
353 p_select_clause IN VARCHAR2 ,
354 p_from_clause IN VARCHAR2 ,
355 p_where_clause IN VARCHAR2 ,
356 p_order_by_clause IN VARCHAR2,
357 p_other_clause IN VARCHAR2 ,
358 p_block_level IN VARCHAR2,
359 p_CREATED_BY IN NUMBER ,
360 p_CREATION_DATE IN DATE ,
361 p_LAST_UPDATED_BY IN NUMBER ,
362 p_LAST_UPDATE_DATE IN DATE ,
363 p_LAST_UPDATE_LOGIN IN NUMBER ,
364 p_OBJECT_VERSION_NUMBER IN NUMBER := NULL,
365 p_APPLICATION_ID IN NUMBER ,
366 x_Profile_Variables_Rec OUT NOCOPY ProfVar_Rec_Type
367 )
368 IS
369 BEGIN
370 x_profile_variables_rec.block_id := p_block_id;
371 x_Profile_Variables_Rec.block_name := p_block_name;
372 x_Profile_Variables_Rec.block_name_code := p_block_name_code;
373 x_Profile_Variables_Rec.description := p_description;
374 x_Profile_Variables_Rec.currency_code := p_currency_code;
375 x_Profile_Variables_Rec.sql_stmnt := p_sql_stmnt;
376 x_Profile_Variables_Rec.batch_sql_stmnt := p_batch_sql_stmnt;
377 x_Profile_Variables_Rec.seeded_flag :=p_seeded_flag;
378 x_Profile_Variables_Rec.sql_stmnt_for_drilldown := p_sql_stmnt_for_drilldown;
379 x_Profile_Variables_Rec.object_code := p_object_code;
380 x_Profile_Variables_Rec.start_date_active := p_start_date_active;
381 x_Profile_Variables_Rec.end_date_active := p_end_date_active;
382 x_Profile_Variables_Rec.select_clause := p_select_clause;
383 x_Profile_Variables_Rec.from_clause := p_from_clause;
387 x_Profile_Variables_Rec.block_level := p_block_level;
384 x_Profile_Variables_Rec.where_clause := p_where_clause;
385 x_Profile_Variables_Rec.order_by_clause := p_order_by_clause;
386 x_Profile_Variables_Rec.other_clause := p_other_clause;
388 x_Profile_Variables_Rec.created_by := p_created_by;
389 x_Profile_Variables_Rec.creation_date := p_creation_date;
390 x_Profile_Variables_Rec.last_updated_by := p_last_updated_by;
391 x_Profile_Variables_Rec.last_update_date := p_last_update_date;
392 x_Profile_Variables_Rec.last_update_login := p_last_update_login;
393 x_Profile_Variables_Rec.object_version_number := p_object_version_number;
394 x_Profile_Variables_Rec.application_id := p_application_id;
395
396 END Convert_Columns_to_Rec;
397
398 ----------------------------------------------------------------------
399 -- Create_Profile_Variable
400 -----------------------------------------------------------------------
401 -- {Start Of Comments}
402 --
403 -- Description:
404 --
405 -- Access Status:
406 -- Internal Development Use Only.
407 --
408 -- {End Of Comments}
409 -- ----------------------------------------------------------------------------
410
411 PROCEDURE Create_Profile_Variable(
412 p_api_version_number IN NUMBER,
413 p_init_msg_list IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
414 p_commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
415 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
416 x_return_status OUT NOCOPY VARCHAR2,
417 x_msg_count OUT NOCOPY NUMBER,
418 x_msg_data OUT NOCOPY VARCHAR2,
419 p_block_name IN VARCHAR2,
420 p_block_name_code IN VARCHAR2 ,
421 p_description IN VARCHAR2 ,
422 p_sql_stmnt IN VARCHAR2 ,
423 p_batch_sql_stmnt IN VARCHAR2,
424 p_sql_stmnt_for_drilldown IN VARCHAR2 DEFAULT NULL,
425 p_seeded_flag IN VARCHAR2 ,
426 p_start_date_active IN DATE ,
427 p_end_date_active IN DATE ,
428 p_currency_code IN VARCHAR2,
429 p_object_code IN VARCHAR2 DEFAULT NULL,
430 p_select_clause IN VARCHAR2,
431 p_from_clause IN VARCHAR2,
432 p_where_clause IN VARCHAR2 ,
433 p_order_by_clause IN VARCHAR2 DEFAULT NULL,
434 p_other_clause IN VARCHAR2,
435 p_block_level IN VARCHAR2,
436 p_CREATED_BY IN NUMBER,
437 p_CREATION_DATE IN DATE ,
438 p_LAST_UPDATED_BY IN NUMBER,
439 p_LAST_UPDATE_DATE IN DATE ,
440 p_LAST_UPDATE_LOGIN IN NUMBER,
441 x_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
442 p_APPLICATION_ID IN NUMBER,
443 x_block_id OUT NOCOPY NUMBER
444 ) IS
445 l_prof_var_rec ProfVar_Rec_Type;
446 BEGIN
447
448 Convert_Columns_to_Rec (
449 p_block_name => p_block_name,
450 p_block_name_code => p_block_name_code,
451 p_description => p_description,
452 p_sql_stmnt => p_sql_stmnt,
453 p_batch_sql_stmnt => p_batch_sql_stmnt,
454 p_seeded_flag => p_seeded_flag,
455 p_sql_stmnt_for_drilldown => p_sql_stmnt_for_drilldown,
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_object_code => p_object_code,
460 p_select_clause => p_select_clause,
461 p_from_clause => p_from_clause,
462 p_where_clause => p_where_clause,
463 p_order_by_clause => p_order_by_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_APPLICATION_ID => p_APPLICATION_ID,
472 x_Profile_Variables_Rec => l_prof_var_rec
473 );
474
475 Create_Profile_Variable(
476 p_api_version_number => p_api_version_number,
477 p_Init_Msg_List => p_init_msg_list,
478 P_Commit => p_commit,
479 P_Validation_Level => p_validation_level,
480 P_prof_var_rec => l_prof_var_rec,
481 x_msg_data => x_msg_data,
482 x_msg_count => x_msg_count,
483 x_return_status => x_return_status,
484 x_block_id => x_block_id,
485 x_object_version_number => x_object_version_number
486 );
487
488 END;
489 --> with record type
490 PROCEDURE Create_Profile_Variable(
491 p_api_version_number IN NUMBER,
492 p_init_msg_list IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
493 p_commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
494 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
495 p_prof_var_rec IN ProfVar_Rec_Type := G_MISS_PROF_REC,
496 x_msg_data OUT NOCOPY VARCHAR2,
497 x_msg_count OUT NOCOPY NUMBER,
498 x_return_status OUT NOCOPY VARCHAR2,
499 x_block_id OUT NOCOPY NUMBER,
500 x_object_version_number OUT NOCOPY NUMBER
501 )
502 IS
506 l_block_id NUMBER;
503 l_api_version NUMBER := 1.0 ;
504 l_api_name VARCHAR2(30) := 'Create_Profile_Variable_PVT' ;
505 l_api_name_full VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
507
508 --local variables
509 l_prof_var_rec ProfVar_Rec_Type := p_prof_var_rec;
510
511 l_object_type_code VARCHAR2(80);
512 l_object_id number;
513 BEGIN
514
515 -- Standard start of API savepoint
516 SAVEPOINT Create_Profile_Variable_PVT;
517
518 -- Standard call to check for call compatibility
519 IF NOT FND_API.Compatible_API_Call(l_api_version,
520 p_api_version_number,
521 l_api_name,
522 G_PKG_NAME )
523 THEN
524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
525 END IF;
526
527
528 -- Initialize message list if p_init_msg_list is set to TRUE
529 IF FND_API.To_Boolean(p_init_msg_list)
530 THEN
531 FND_MSG_PUB.Initialize;
532 END IF;
533
534
535 -- Initialize API return status to success
536 x_return_status := FND_API.G_RET_STS_SUCCESS;
537
538
539 -- we have to build the sql statement in advance before
540 -- going into the validation part to validate the whole
541 -- sql statement
542 /* jamose duplicate validation; validated under build_sql_stmnt
543 IF p_prof_var_rec.select_clause = CSC_CORE_UTILS_PVT.G_MISS_CHAR THEN
544 l_prof_var_rec.select_clause := NULL;
545 END IF;
546 IF p_prof_var_rec.from_clause = CSC_CORE_UTILS_PVT.G_MISS_CHAR THEN
547 l_prof_var_rec.from_clause := NULL;
548 END IF;
549 -- jamose */
550 IF p_prof_var_rec.where_clause = CSC_CORE_UTILS_PVT.G_MISS_CHAR THEN
551 l_prof_var_rec.where_clause := NULL;
552 END IF;
553 IF p_prof_var_rec.other_clause = CSC_CORE_UTILS_PVT.G_MISS_CHAR THEN
554 l_prof_var_rec.other_clause := NULL;
555 END IF;
556 Build_Sql_Stmnt( p_api_name => l_api_name,
557 p_select_clause => l_prof_var_rec.select_clause,
558 p_from_clause => l_prof_var_rec.from_clause,
559 p_where_clause => l_prof_var_rec.where_clause,
560 p_other_clause => l_prof_var_rec.other_clause,
561 x_sql_stmnt => l_prof_var_rec.sql_stmnt,
562 x_return_status => x_return_status );
563 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
564 RAISE FND_API.G_EXC_ERROR;
565 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
566 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
567 END IF;
568
569
570 -- ----------------------------------------------------------------------
571 -- Apply business-rule validation to all required and passed parameters
572 -- if validation level is set.
573 -- ----------------------------------------------------------------------
574 IF (p_validation_level > CSC_CORE_UTILS_PVT.G_VALID_LEVEL_NONE) THEN
575 -- Check for all the required attributes that are being passed should be there
576 -- and should not be passed as null. If passed as null then we will raise error
577 -- Also Check if Seeded_Flag = 'Y' and then allow / disallow updates
578 -- of certain fields
579
580 Validate_Profile_Variables(
581 p_api_name => l_api_name_full,
582 p_validation_mode => CSC_CORE_UTILS_PVT.G_CREATE,
583 P_validate_rec => l_prof_var_rec,
584 x_return_status => x_return_status,
585 x_msg_count =>x_msg_count,
586 x_msg_data =>x_msg_data );
587
588 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
589 RAISE FND_API.G_EXC_ERROR;
590 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
592 END IF;
593
594 END IF ; /*** p_validation_level ****/
595
596 --replace :party_id, cust_acct_id,cust_acct_org_id
597 --with :global.party_id, :global.cust_acct_id,:global.cust_acct_org_id
598 --respectively in sql_Stmnt_for_drilldown which will be used in
599 --drill down form.
600 l_prof_var_rec.sql_stmnt_for_drilldown := replace(l_prof_var_rec.sql_stmnt_for_drilldown,
601 ':party_id',':global.csc_party_id');
602 l_prof_var_rec.sql_stmnt_for_drilldown := replace(l_prof_var_rec.sql_stmnt_for_drilldown,
603 ':cust_account_id',':global.csc_cust_account_id');
604 l_prof_var_rec.sql_stmnt_for_drilldown := replace(l_prof_var_rec.sql_Stmnt_for_drilldown,
605 ':org_id',':global.csc_org_id');
606
607 -- Build an insert record, check if any of the parameters
608 -- have been not been passed, if not assign a NULL.
609 -- The mandatory columns have already been validated in
610 -- Validate_Profile_Variables if the validation level is
611 -- set to FULL.
612
613 CSC_PROF_BLOCKS_PKG.Insert_Row(
614 px_BLOCK_ID => x_block_id,
615 p_CREATED_BY => FND_GLOBAL.USER_ID,
616 p_CREATION_DATE => sysdate,
617 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
618 p_LAST_UPDATE_DATE => sysdate,
619 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
620 p_BLOCK_NAME => l_prof_var_rec.BLOCK_NAME,
621 p_DESCRIPTION => l_prof_var_rec.DESCRIPTION,
622 p_START_DATE_ACTIVE => l_prof_var_rec.START_DATE_ACTIVE,
623 p_END_DATE_ACTIVE => l_prof_var_rec.END_DATE_ACTIVE,
627 p_SQL_STMNT_FOR_DRILLDOWN => l_prof_var_rec.SQL_STMNT_FOR_DRILLDOWN,
624 p_SEEDED_FLAG => l_prof_var_rec.SEEDED_FLAG,
625 p_BLOCK_NAME_CODE => l_prof_var_rec.BLOCK_NAME_CODE,
626 p_OBJECT_CODE => l_prof_var_rec.object_code,
628 p_SQL_STMNT => l_prof_var_rec.SQL_STMNT,
629 p_BATCH_SQL_STMNT => l_prof_var_rec.BATCH_SQL_STMNT,
630 p_SELECT_CLAUSE => l_prof_var_rec.SELECT_CLAUSE,
631 p_CURRENCY_CODE => l_prof_var_rec.CURRENCY_CODE,
632 p_FROM_CLAUSE => l_prof_var_rec.FROM_CLAUSE,
633 p_WHERE_CLAUSE => l_prof_var_rec.WHERE_CLAUSE,
634 p_OTHER_CLAUSE => l_prof_var_rec.other_clause,
635 p_BLOCK_LEVEL => l_prof_var_rec.block_level,
636 x_OBJECT_VERSION_NUMBER => x_OBJECT_VERSION_NUMBER,
637 p_APPLICATION_ID => l_prof_var_rec.APPLICATION_ID
638 );
639
640 --
641 -- Standard check of p_commit
642 IF FND_API.To_Boolean(p_commit) THEN
643 COMMIT WORK;
644 END IF;
645
646 -- Standard call to get message count and if count is 1, get message info
647
648 FND_MSG_PUB.Count_And_Get(
649 p_encoded => FND_API.G_FALSE,
650 p_count => x_msg_count,
651 p_data => x_msg_data );
652
653
654 EXCEPTION
655 WHEN FND_API.G_EXC_ERROR THEN
656 ROLLBACK TO Create_Profile_Variable_PVT;
657 x_return_status := FND_API.G_RET_STS_ERROR;
658 /* FND_MSG_PUB.Count_And_Get
659 ( p_encoded => FND_API.G_FALSE,
660 p_count => x_msg_count,
661 p_data => x_msg_data
662 ); */
663 APP_EXCEPTION.RAISE_EXCEPTION;
664 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
665 ROLLBACK TO Create_Profile_Variable_PVT;
666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
667 /* FND_MSG_PUB.Count_And_Get
668 ( p_encoded => FND_API.G_FALSE,
669 p_count => x_msg_count,
670 p_data => x_msg_data
671 ); */
672 APP_EXCEPTION.RAISE_EXCEPTION;
673 WHEN OTHERS THEN
674 ROLLBACK TO Create_Profile_Variable_PVT;
675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
676 --IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
677 -- FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
678 -- END IF;
679 FND_MSG_PUB.Build_Exc_Msg;
680 /* FND_MSG_PUB.Count_And_Get
681 ( p_encoded => FND_API.G_FALSE,
682 p_count => x_msg_count,
683 p_data => x_msg_data
684 ); */
685 APP_EXCEPTION.RAISE_EXCEPTION;
686 END Create_Profile_Variable ;
687
688 PROCEDURE Create_table_column(
689 P_Api_Version_Number IN NUMBER,
690 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
691 P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
692 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
693 p_Table_Column_Tbl IN Table_Column_Tbl_Type := G_MISS_Table_Column_TBL,
694 X_TABLE_COLUMN_ID OUT NOCOPY NUMBER,
695 X_object_version_number OUT NOCOPY NUMBER,
696 X_Return_Status OUT NOCOPY VARCHAR2,
697 X_Msg_Count OUT NOCOPY NUMBER,
698 X_Msg_Data OUT NOCOPY VARCHAR2
699 )
700 IS
701 l_table_column_rec Table_Column_Rec_Type := G_MISS_Table_Column_Rec;
702 BEGIN
703
704 FOR i in 1..p_Table_Column_Tbl.Count LOOP
705
706 l_table_Column_rec.Column_Name := p_Table_Column_tbl(i).Column_Name;
707 l_table_Column_rec.Table_Name := p_Table_Column_tbl(i).Table_Name;
708 l_table_Column_rec.Label := p_Table_Column_tbl(i).Label;
709 l_table_Column_rec.Table_Alias := p_Table_Column_Tbl(i).Table_Alias;
710 l_table_Column_rec.Column_Sequence := p_Table_Column_Tbl(i).Column_Sequence;
711 l_table_column_rec.block_id := p_table_column_tbl(i).block_id;
712
713 -- nullify the table column id so as not to pass the old id
714 x_table_column_id := NULL;
715
716 Create_table_column(
717 P_Api_Version_Number => p_api_version_number,
718 P_Init_Msg_List => p_init_msg_list,
719 P_Commit => p_commit,
720 p_validation_level => p_validation_level,
721 p_Table_Column_rec => l_table_column_rec,
722 X_TABLE_COLUMN_ID => x_table_column_id,
723 X_OBJECT_VERSION_NUMBER => x_object_version_number,
724 X_Return_Status => x_return_status,
725 X_Msg_Count => x_msg_count,
726 X_Msg_Data => x_msg_Data );
727
728 END LOOP;
729 /* --Need to work on this one...
730 -- If drilldown sql statement is not passed in or is null then
731 Select object_version_number
732 into l_object_version_number
733 from csc_prof_blocks_b
734 where block_id = p_table_column_rec.block_id;
735
736 Update_Profile_Variable(
737 p_api_version_number => 1.0,
738 p_validation_level => CSC_CORE_UTILS_PVT.G_VALID_LEVEL_NONE,
739 x_return_status => x_return_status,
740 x_msg_count => x_msg_count,
741 x_msg_data => x_msg_data,
742 px_object_version_number => l_object_version_number,
746 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
743 p_block_id => l_table_column_rec.block_id,
744 p_sql_stmnt_for_drilldown => ltrim(rtrim(l_sql_stmnt_for_drilldown)));
745
747 RAISE FND_API.G_EXC_ERROR;
748 END IF; */
749 END;
750
751 PROCEDURE Create_table_column(
752 P_Api_Version_Number IN NUMBER,
753 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
754 P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
755 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
756 p_Table_Column_REC IN Table_Column_Rec_Type := G_MISS_Table_Column_REC,
757 X_TABLE_COLUMN_ID OUT NOCOPY NUMBER,
758 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
759 X_Return_Status OUT NOCOPY VARCHAR2,
760 X_Msg_Count OUT NOCOPY NUMBER,
761 X_Msg_Data OUT NOCOPY VARCHAR2
762 )
763 IS
764 l_api_name CONSTANT VARCHAR2(30) := 'Create_table_column';
765 l_api_version_number CONSTANT NUMBER := 1.0;
766 l_table_Column_rec Table_Column_Rec_Type := p_table_column_rec;
767 BEGIN
768
769 -- Standard Start of API savepoint
770 SAVEPOINT CREATE_Table_Column_PVT;
771
772
773
774 -- Standard call to check for call compatibility.
775 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
776 p_api_version_number,
777 l_api_name,
778 G_PKG_NAME)
779 THEN
780 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
781 END IF;
782
783 -- Initialize message list if p_init_msg_list is set to TRUE.
784 IF FND_API.to_Boolean( p_init_msg_list )
785 THEN
786 FND_MSG_PUB.initialize;
787 END IF;
788
789 -- Initialize API return status to SUCCESS
790 x_return_status := FND_API.G_RET_STS_SUCCESS;
791
792 --
793 -- API body
794 --
795
796 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
797 THEN
798
799 -- Check for all the required attributes that are being passed should be there
800 -- and should not be passed as null. If passed as null then raise error
801
802 Validate_table_column(
803 p_api_name => l_api_name,
804 p_Init_Msg_List => p_Init_Msg_List,
805 p_validation_mode => CSC_CORE_UTILS_PVT.G_CREATE,
806 p_Validate_Rec => l_Table_Column_Rec,
807 x_return_status => x_return_status);
808 END IF;
809
810 IF (x_return_status<>FND_API.G_RET_STS_SUCCESS) THEN
811 RAISE FND_API.G_EXC_ERROR;
812 END IF;
813
814 -- Build an insert record, check if any of the parameters
815 -- have been not been passed, if not assign a NULL.
816 -- The mandatory columns have already been validated in
817 -- Validate_table_columns if the validation level is
818 -- set to FULL.
819
820 CSC_PROF_TABLE_COLUMNS_PKG.Insert_Row(
821 px_TABLE_COLUMN_ID => x_TABLE_COLUMN_ID,
822 p_BLOCK_ID => l_Table_Column_rec.BLOCK_ID,
823 p_TABLE_NAME => l_Table_Column_rec.TABLE_NAME,
824 p_COLUMN_NAME => l_Table_Column_rec.COLUMN_NAME,
825 p_LABEL => l_Table_Column_rec.LABEL,
826 p_TABLE_ALIAS => l_Table_Column_rec.Table_Alias,
827 p_COLUMN_SEQUENCE => l_Table_Column_rec.Column_Sequence,
828 p_DRILLDOWN_COLUMN_FLAG => l_Table_Column_rec.DRILLDOWN_COLUMN_FLAG,
829 p_LAST_UPDATE_DATE => SYSDATE,
830 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
831 p_CREATION_DATE => SYSDATE,
832 p_CREATED_BY => FND_GLOBAL.USER_ID,
833 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
834 p_SEEDED_FLAG => l_Table_Column_rec.seeded_flag,
835 x_OBJECT_VERSION_NUMBER => x_object_version_number );
836
837
838 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
839 RAISE FND_API.G_EXC_ERROR;
840 END IF;
841
842
843 --
844 -- End of API body
845 --
846
847
848 -- Standard check for p_commit
849 IF FND_API.to_Boolean( p_commit )
850 THEN
851 COMMIT WORK;
852 END IF;
853
854
855 -- Standard call to get message count and if count is 1, get message info.
856 FND_MSG_PUB.Count_And_Get(
857 p_count => x_msg_count,
858 p_data => x_msg_data);
859
860 EXCEPTION
861 WHEN FND_API.G_EXC_ERROR THEN
862 ROLLBACK TO CREATE_Table_Column_PVT;
863 x_return_status := FND_API.G_RET_STS_ERROR ;
864 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
865 p_data => x_msg_data) ;
866 APP_EXCEPTION.RAISE_EXCEPTION;
867 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
868 ROLLBACK TO CREATE_Table_Column_PVT;
869 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
870 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
871 p_data => x_msg_data) ;
872 APP_EXCEPTION.RAISE_EXCEPTION;
873 WHEN OTHERS THEN
877 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
874 ROLLBACK TO CREATE_Table_Column_PVT;
875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
876 --IF FND_MSG_PUB.Check_Msg_Level
878 --THEN
879 --FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
880 --END IF ;
881 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
882 p_data => x_msg_data) ;
883 APP_EXCEPTION.RAISE_EXCEPTION;
884 End Create_table_column;
885
886 ----------------------------------------------------------------------
887 -- Update_Profile_Variable
888 -----------------------------------------------------------------------
889 -- {Start Of Comments}
890 --
891 -- Description:
892 --
893 -- Access Status:
894 -- Internal Development Use Only.
895 --
896 -- {End Of Comments}
897 -- ----------------------------------------------------------------------------
898
899 PROCEDURE Update_Profile_Variable(
900 p_api_version_number IN NUMBER,
901 p_init_msg_list IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
902 p_commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
903 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
904 x_return_status OUT NOCOPY VARCHAR2,
905 x_msg_count OUT NOCOPY NUMBER,
906 x_msg_data OUT NOCOPY VARCHAR2,
907 p_block_id IN NUMBER ,
908 p_block_name IN VARCHAR2 DEFAULT NULL,
909 p_block_name_code IN VARCHAR2 DEFAULT NULL,
910 p_description IN VARCHAR2 DEFAULT NULL,
911 p_currency_code IN VARCHAR2 DEFAULT NULL,
912 p_sql_stmnt IN VARCHAR2 DEFAULT NULL,
913 p_batch_sql_stmnt IN VARCHAR2 DEFAULT NULL,
914 p_seeded_flag IN VARCHAR2 DEFAULT NULL,
915 --p_form_function_id IN NUMBER ,
916 p_object_code IN VARCHAR2 DEFAULT NULL ,
917 p_start_date_active IN DATE DEFAULT NULL,
918 p_end_date_active IN DATE DEFAULT NULL ,
919 p_sql_stmnt_for_drilldown IN VARCHAR2 DEFAULT NULL ,
920 p_select_clause IN VARCHAR2 DEFAULT NULL ,
921 p_from_clause IN VARCHAR2 DEFAULT NULL,
922 p_where_clause IN VARCHAR2 DEFAULT NULL,
923 p_order_by_clause IN VARCHAR2 DEFAULT NULL ,
924 p_other_clause IN VARCHAR2 DEFAULT NULL,
925 p_block_level IN VARCHAR2 DEFAULT NULL,
926 p_CREATED_BY IN NUMBER DEFAULT NULL,
927 p_CREATION_DATE IN DATE DEFAULT NULL ,
928 p_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
929 p_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
930 p_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL,
931 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER ,
932 p_APPLICATION_ID IN NUMBER DEFAULT NULL)
933
934 IS
935 l_prof_var_rec ProfVar_Rec_Type;
936 Begin
937 Convert_Columns_to_Rec (
938 p_block_id => p_block_id,
939 p_block_name => p_block_name,
940 p_block_name_code => p_block_name_code,
941 p_description => p_description,
942 p_sql_stmnt => p_sql_stmnt,
943 p_batch_sql_stmnt => p_batch_sql_stmnt,
944 p_seeded_flag => p_seeded_flag,
945 p_sql_stmnt_for_drilldown => p_sql_stmnt_for_drilldown,
946 p_start_date_active => p_start_date_active,
947 p_end_date_active => p_end_date_active,
948 p_currency_code => p_currency_code,
949 --p_form_function_id => p_form_function_id,
950 p_object_code => p_object_code,
951 p_select_clause => p_select_clause,
952 p_from_clause => p_from_clause,
953 p_where_clause => p_where_clause,
954 p_order_by_clause => p_order_by_clause,
955 p_other_clause => p_other_clause,
956 p_block_level => p_block_level,
957 p_CREATED_BY => p_CREATED_BY,
958 p_CREATION_DATE => p_CREATION_DATE,
959 p_LAST_UPDATED_BY => p_LAST_UPDATED_BY,
960 p_LAST_UPDATE_DATE => p_LAST_UPDATE_DATE,
961 p_LAST_UPDATE_LOGIN => p_LAST_UPDATE_LOGIN,
962 p_APPLICATION_ID => p_APPLICATION_ID,
963 x_Profile_Variables_Rec => l_prof_var_rec
964 );
965
966 Update_Profile_Variable(
967 p_api_version_number => p_api_version_number,
968 P_Init_Msg_List => p_init_msg_list,
969 P_Commit => p_commit,
970 P_Validation_Level => p_validation_level,
971 P_prof_var_rec => l_prof_var_rec,
972 Px_Object_version_number => px_object_version_number,
973 x_msg_data => x_msg_data,
974 x_msg_count => x_msg_count,
975 x_return_status => x_return_status
976 );
977
978 End Update_Profile_Variable;
979
980 ----------------------------------------------------------------------
981 -- Update_Profile_Variable
982 -----------------------------------------------------------------------
983 -- {Start Of Comments}
984 --
985 -- Description:
986 --
987 -- Access Status:
988 -- Internal Development Use Only.
989 --
990 -- {End Of Comments}
991 -- ----------------------------------------------------------------------------
992
993 PROCEDURE Update_Profile_Variable(
994 p_api_version_number IN NUMBER,
995 p_init_msg_list IN VARCHAR2:= CSC_CORE_UTILS_PVT.G_FALSE,
999 px_Object_Version_Number IN OUT NOCOPY NUMBER,
996 p_commit IN VARCHAR2:= CSC_CORE_UTILS_PVT.G_FALSE,
997 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
998 p_prof_var_rec IN ProfVar_Rec_Type := G_MISS_PROF_REC,
1000 x_msg_data OUT NOCOPY VARCHAR2,
1001 x_msg_count OUT NOCOPY VARCHAR2,
1002 x_return_status OUT NOCOPY VARCHAR2 )
1003
1004 IS
1005 l_api_name CONSTANT VARCHAR2(30) := 'Update_Profile_Variable' ;
1006 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
1007 l_api_version CONSTANT NUMBER := 1.0 ;
1008
1009 -- record to get the old records
1010 l_OLD_PROF_VAR_REC CSC_PROF_BLOCKS_VL%ROWTYPE ;
1011
1012 l_prof_var_rec ProfVar_Rec_Type := P_prof_Var_Rec;
1013
1014 BEGIN
1015 -- standard start of API savepoint
1016 SAVEPOINT Update_Profile_Variable_Pvt ;
1017
1018 -- Standard Call to check API compatibility
1019 IF NOT FND_API.Compatible_API_Call( l_api_version,
1020 p_api_version_number,
1021 l_api_name,
1022 G_PKG_NAME )
1023 THEN
1024 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1025 END IF ;
1026
1027
1028 -- Initialize the message list if p_msg_list is set to TRUE
1029 IF FND_API.To_Boolean(p_init_msg_list) THEN
1030 FND_MSG_PUB.initialize ;
1031 END IF ;
1032
1033
1034 -- Initialize the API Return Success to True
1035 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1036
1037 -- Fetch the existing Customer Profile Variable Record and lock the record
1038 -- for update.
1039 -- If lock fails we have to abort
1040 -- Get record INTO l_old_prof_var_rec variable
1041 GET_PROF_BLOCKS(
1042 p_api_name => l_api_name,
1043 p_BLOCK_ID => p_prof_var_rec.BLOCK_ID,
1044 p_object_version_number => px_object_version_number,
1045 X_PROF_BLOCKS_REC => l_OLD_PROF_VAR_REC,
1046 x_return_status => x_return_status );
1047 -- If any error abort the API
1048 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1049 RAISE FND_API.G_EXC_ERROR;
1050 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1051 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1052 END IF;
1053
1054
1055 -- we have to build the sql statement in advance before
1056 -- going into the validation part. Bcauz if any of the
1057 -- select, from, where or other columns change the whole
1058 -- sql statement gets effected.
1059 l_prof_var_rec.select_clause := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(l_prof_var_rec.select_clause,l_old_prof_var_rec.select_clause);
1060 l_prof_var_rec.from_clause := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(l_prof_var_rec.from_clause,l_old_prof_var_rec.from_clause);
1061 l_prof_var_rec.where_clause := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(l_prof_var_rec.where_clause,l_old_prof_var_rec.where_clause);
1062 l_prof_var_rec.other_clause := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(l_prof_var_rec.other_clause,l_old_prof_var_rec.other_clause);
1063 l_prof_var_rec.object_code := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(l_prof_var_rec.object_code,l_old_prof_var_rec.object_code);
1064 l_prof_var_rec.sql_stmnt_for_drilldown := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(l_prof_var_rec.sql_stmnt_for_drilldown,l_old_prof_var_rec.sql_stmnt_for_drilldown);
1065 l_prof_var_rec.block_name := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(l_prof_var_rec.block_name,l_old_prof_var_rec.block_name);
1066 l_prof_var_rec.block_name_code := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(l_prof_var_rec.block_name_code,l_old_prof_var_rec.block_name_code);
1067
1068
1069 -- build the sql statement if any of the columns have changed
1070
1071 IF ((l_prof_var_rec.select_clause <> l_old_prof_var_rec.select_clause)
1072 OR (l_prof_var_rec.from_clause <> l_old_prof_var_rec.from_clause)
1073 OR (l_prof_var_rec.where_clause <> l_old_prof_var_rec.where_clause)
1074 OR (l_prof_var_rec.other_clause <> l_old_prof_var_rec.other_clause))
1075 THEN
1076 Build_Sql_Stmnt(
1077 p_api_name => l_api_name,
1078 p_SELECT_CLAUSE => l_prof_var_rec.SELECT_CLAUSE,
1079 p_FROM_CLAUSE => l_prof_var_rec.FROM_CLAUSE,
1080 p_WHERE_CLAUSE => l_prof_var_rec.WHERE_CLAUSE,
1081 p_OTHER_CLAUSE => l_prof_var_rec.OTHER_CLAUSE,
1082 X_SQL_STMNT => l_prof_var_rec.SQL_STMNT,
1083 X_return_status => x_return_status );
1084
1085 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1086 RAISE FND_API.G_EXC_ERROR;
1087 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1088 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1089 END IF;
1090 ELSE
1091 l_prof_var_rec.sql_stmnt := l_old_prof_var_rec.sql_stmnt;
1092 END IF;
1093 -- ----------------------------------------------------------------------
1094 -- Apply business-rule validation to all required and passed parameters
1095 -- if validation level is set.
1096 -- ----------------------------------------------------------------------
1097
1098 IF (p_validation_level > CSC_CORE_UTILS_PVT.G_VALID_LEVEL_NONE) THEN
1099
1100 --
1101 -- Validate the user and login id
1102 --
1103 -- Check for all the required attributes that are being passed should be there
1104 -- and should not be passed as null. If passed as null then we will raise error
1105 -- Also Check if Seeded_Flag = 'Y' and then allow / disallow updates
1106 -- of certain fields
1107
1108 Validate_Profile_Variables (
1109 p_api_name => l_api_name_full,
1113 x_msg_count =>x_msg_count,
1110 p_validation_mode => CSC_CORE_UTILS_PVT.G_UPDATE,
1111 p_validate_rec => l_prof_var_rec,
1112 x_return_status => x_return_status,
1114 x_msg_data =>x_msg_data );
1115
1116 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1117 RAISE FND_API.G_EXC_ERROR;
1118 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1120 END IF;
1121 END IF;
1122
1123 -- Replace :party_id, cust_acct_id,cust_acct_org_id with
1124 -- :global.csc_party_id, :global.csc_cust_acct_id,
1125 -- :global.csc_cust_acct_org_id
1126 -- respectively in sql_Stmnt_for_drilldown which will be used in
1127 -- drill down form.
1128
1129 l_prof_var_rec.sql_stmnt_for_drilldown := replace(l_prof_var_rec.sql_stmnt_for_drilldown,
1130 ':party_id',':global.csc_party_id');
1131 l_prof_var_rec.sql_stmnt_for_drilldown := replace(l_prof_var_rec.sql_stmnt_for_drilldown,
1132 ':cust_account_id',':global.csc_cust_account_id');
1133 l_prof_var_rec.sql_stmnt_for_drilldown := replace(l_prof_var_rec.sql_Stmnt_for_drilldown,
1134 ':org_id',':global.csc_org_id');
1135
1136 -- Build an update record, check if any of the parameters
1137 -- have been not been passed, if not assign a NULL.
1138 -- The mandatory columns have already been validated in
1139 -- Validate_Profile_Variables if the validation level is
1140 -- set to FULL.
1141
1142 CSC_PROF_BLOCKS_PKG.Update_Row(
1143 p_BLOCK_ID => l_prof_var_rec.block_id,
1144 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1145 p_LAST_UPDATE_DATE =>sysdate,
1146 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1147 p_BLOCK_NAME => csc_core_utils_pvt.get_g_miss_char(l_prof_var_rec.block_name,l_old_prof_var_rec.block_name) ,
1148 p_DESCRIPTION => csc_core_utils_pvt.get_g_miss_char(l_prof_var_rec.description,l_old_prof_var_rec.description),
1149 p_START_DATE_ACTIVE => csc_core_utils_pvt.get_g_miss_date(l_prof_var_rec.start_date_active,l_old_prof_var_rec.start_date_active),
1150 p_END_DATE_ACTIVE => csc_core_utils_pvt.get_g_miss_date(l_prof_var_rec.end_date_active,l_old_prof_var_rec.end_date_active),
1151 p_SEEDED_FLAG => csc_core_utils_pvt.get_g_miss_char(l_prof_var_rec.seeded_flag,l_old_prof_var_rec.seeded_flag),
1152 p_BLOCK_NAME_CODE => csc_core_utils_pvt.get_g_miss_char(l_prof_var_rec.block_name_code,l_old_prof_var_rec.block_name_code),
1153 p_OBJECT_CODE => csc_core_utils_pvt.get_g_miss_char(l_prof_var_rec.object_code,l_old_prof_var_rec.object_code),
1154 p_SQL_STMNT_FOR_DRILLDOWN => csc_core_utils_pvt.get_g_miss_char(l_prof_var_rec.sql_stmnt_for_drilldown,l_old_prof_var_rec.sql_stmnt_for_drilldown),
1155 p_SQL_STMNT => csc_core_utils_pvt.get_g_miss_char(l_prof_var_rec.sql_stmnt,l_old_prof_var_rec.sql_stmnt),
1156 p_BATCH_SQL_STMNT => csc_core_utils_pvt.get_g_miss_char(l_prof_var_rec.batch_sql_stmnt,l_old_prof_var_rec.batch_sql_stmnt),
1157 p_SELECT_CLAUSE => l_prof_var_rec.select_clause,
1158 p_CURRENCY_CODE => csc_core_utils_pvt.get_g_miss_char(l_prof_var_rec.currency_code,l_old_prof_var_rec.currency_code),
1159 p_FROM_CLAUSE => l_prof_var_rec.from_clause,
1160 p_WHERE_CLAUSE => l_prof_var_rec.where_clause,
1161 p_OTHER_CLAUSE => l_prof_var_rec.other_clause,
1162 p_BLOCK_LEVEL => csc_core_utils_pvt.get_g_miss_char(l_prof_var_rec.block_level,l_old_prof_var_rec.block_level),
1163 px_OBJECT_VERSION_NUMBER => px_Object_Version_Number,
1164 p_APPLICATION_ID => csc_core_utils_pvt.get_g_miss_char(l_prof_var_rec.APPLICATION_ID,l_old_prof_var_rec.application_id));
1165
1166
1167 -- Standard check of p_commit
1168 IF FND_API.To_Boolean(p_commit) THEN
1169 COMMIT WORK;
1170 END IF;
1171
1172 -- Standard call to get message count and if count is 1, get message info
1173 FND_MSG_PUB.Count_And_Get(
1174 p_count => x_msg_count,
1175 p_data => x_msg_data );
1176 EXCEPTION
1177 WHEN FND_API.G_EXC_ERROR THEN
1178 ROLLBACK TO Update_Profile_Variable_Pvt;
1179 x_return_status := FND_API.G_RET_STS_ERROR;
1180 --FND_MSG_PUB.Count_And_Get
1181 -- ( p_count => x_msg_count,
1182 -- p_data => x_msg_data );
1183 APP_EXCEPTION.RAISE_EXCEPTION;
1184 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1185 ROLLBACK TO Update_Profile_Variable_Pvt;
1186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1187 --FND_MSG_PUB.Count_And_Get
1188 -- ( p_count => x_msg_count,
1189 -- p_data => x_msg_data );
1190 APP_EXCEPTION.RAISE_EXCEPTION;
1191 WHEN OTHERS THEN
1192 ROLLBACK TO Update_Profile_Variable_Pvt;
1193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1194 -- FND_MSG_PUB.Count_And_Get
1195 -- ( p_count => x_msg_count,
1196 -- p_data => x_msg_data );
1197 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1198 FND_MSG_PUB.Build_Exc_Msg(G_PKG_NAME, l_api_name);
1199 END IF;
1200 APP_EXCEPTION.RAISE_EXCEPTION;
1201 END Update_Profile_Variable;
1202
1203 PROCEDURE Update_table_column(
1204 P_Api_Version_Number IN NUMBER,
1205 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
1206 P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
1207 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
1211 X_Msg_Count OUT NOCOPY NUMBER,
1208 p_Table_Column_REC IN Table_Column_Rec_Type := G_MISS_TABLE_COLUMN_REC,
1209 px_Object_Version_Number IN OUT NOCOPY NUMBER,
1210 X_Return_Status OUT NOCOPY VARCHAR2,
1212 X_Msg_Data OUT NOCOPY VARCHAR2
1213 )
1214
1215 IS
1216
1217 l_api_name CONSTANT VARCHAR2(30) := 'Update_table_column';
1218 l_api_version_number CONSTANT NUMBER := 1.0;
1219
1220 -- Local Variables
1221 l_old_table_column_rec CSC_PROF_TABLE_COLUMNS_VL%ROWTYPE;
1222
1223 l_table_column_Rec Table_Column_Rec_Type := p_Table_Column_REC;
1224 BEGIN
1225 -- Standard Start of API savepoint
1226 SAVEPOINT UPDATE_Table_Column_PVT;
1227
1228 -- Standard call to check for call compatibility.
1229 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1230 p_api_version_number,
1231 l_api_name,
1232 G_PKG_NAME)
1233 THEN
1234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1235 END IF;
1236
1237
1238 -- Initialize message list if p_init_msg_list is set to TRUE.
1239 IF FND_API.to_Boolean( p_init_msg_list )
1240 THEN
1241 FND_MSG_PUB.initialize;
1242 END IF;
1243
1244
1245 -- Initialize API return status to SUCCESS
1246 x_return_status := FND_API.G_RET_STS_SUCCESS;
1247
1248 --
1249 -- Api body
1250 --
1251
1252 --Get the old record as well lock the record.
1253 GET_TABLE_COLUMN(
1254 p_Api_Name => l_api_name,
1255 p_Table_Column_Id => l_table_column_rec.table_column_id,
1256 p_object_version_number => px_object_version_number,
1257 X_Table_Column_Rec => l_old_table_column_rec,
1258 x_return_status => x_return_status);
1259 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1260 RAISE FND_API.G_EXC_ERROR;
1261 END IF;
1262
1263 IF ( P_validation_level >= CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL)
1264 THEN
1265 -- Invoke validation procedures
1266 Validate_table_column(
1267 p_api_name => l_api_name,
1268 p_init_msg_list => p_init_msg_list,
1269 p_validation_mode => CSC_CORE_UTILS_PVT.G_UPDATE,
1270 P_Validate_Rec => l_Table_Column_Rec,
1271 x_return_status => x_return_status);
1272 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1273 RAISE FND_API.G_EXC_ERROR;
1274 END IF;
1275
1276 END IF;
1277
1278 CSC_PROF_TABLE_COLUMNS_PKG.Update_Row(
1279 p_TABLE_COLUMN_ID => l_Table_Column_rec.TABLE_COLUMN_ID,
1280 p_BLOCK_ID => l_Table_Column_rec.BLOCK_ID,
1281 p_TABLE_NAME => l_Table_Column_rec.TABLE_NAME,
1282 p_COLUMN_NAME => l_Table_Column_rec.COLUMN_NAME,
1283 p_LABEL => l_Table_Column_rec.LABEL,
1284 p_TABLE_ALIAS => l_Table_Column_rec.TABLE_ALIAS,
1285 p_COLUMN_SEQUENCE => l_Table_Column_rec.COLUMN_SEQUENCE,
1286 p_DRILLDOWN_COLUMN_FLAG => l_Table_Column_rec.DRILLDOWN_COLUMN_FLAG,
1287 p_LAST_UPDATE_DATE => SYSDATE,
1288 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1289 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1290 p_SEEDED_FLAG => l_Table_Column_rec.seeded_flag,
1291 px_OBJECT_VERSION_NUMBER =>px_object_version_number );
1292
1293 --
1294 -- End of API body.
1295 --
1296
1297 -- Standard check for p_commit
1298 IF FND_API.to_Boolean( p_commit )
1299 THEN
1300 COMMIT WORK;
1301 END IF;
1302
1303
1304 -- Standard call to get message count and if count is 1, get message info.
1305 FND_MSG_PUB.Count_And_Get
1306 ( p_count => x_msg_count,
1307 p_data => x_msg_data
1308 );
1309
1310 EXCEPTION
1311
1312 WHEN FND_API.G_EXC_ERROR THEN
1313 ROLLBACK TO UPDATE_Table_Column_PVT;
1314 x_return_status := FND_API.G_RET_STS_ERROR ;
1315 FND_MSG_PUB.Count_And_Get(
1316 p_count =>x_msg_count,
1317 p_data => x_msg_data
1318 );
1319 APP_EXCEPTION.RAISE_EXCEPTION;
1320 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1321 ROLLBACK TO UPDATE_Table_Column_PVT ;
1322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1323 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
1324 p_data => x_msg_data) ;
1325 APP_EXCEPTION.RAISE_EXCEPTION;
1326 WHEN OTHERS THEN
1327 ROLLBACK TO UPDATE_Table_Column_PVT ;
1328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1329 IF FND_MSG_PUB.Check_Msg_Level
1330 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1331 THEN
1332 FND_MSG_PUB.Build_Exc_Msg(G_PKG_NAME,l_api_name);
1333 END IF ;
1334 --FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
1335 -- p_data => x_msg_data) ;
1336 APP_EXCEPTION.RAISE_EXCEPTION;
1337 End Update_table_column;
1338
1339 PROCEDURE Delete_profile_variables(
1340 P_Api_Version_Number IN NUMBER,
1344 p_BLOCK_ID IN NUMBER,
1341 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1342 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1343 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1345 p_OBJECT_VERSION_NUMBER IN NUMBER,
1346 X_Return_Status OUT NOCOPY VARCHAR2,
1347 X_Msg_Count OUT NOCOPY NUMBER,
1348 X_Msg_Data OUT NOCOPY VARCHAR2
1349 )
1350
1351 IS
1352 l_api_name CONSTANT VARCHAR2(30) := 'Delete_profile_variables';
1353 l_api_version_number CONSTANT NUMBER := 1.0;
1354 BEGIN
1355 -- Standard Start of API savepoint
1356 SAVEPOINT DELETE_Profile_Variables_PVT;
1357
1358 -- Standard call to check for call compatibility.
1359 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1360 p_api_version_number,
1361 l_api_name,
1362 G_PKG_NAME)
1363 THEN
1364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1365 END IF;
1366
1367
1368 -- Initialize message list if p_init_msg_list is set to TRUE.
1369 IF FND_API.to_Boolean( p_init_msg_list )
1370 THEN
1371 FND_MSG_PUB.initialize;
1372 END IF;
1373
1374
1375 -- Initialize API return status to SUCCESS
1376 x_return_status := FND_API.G_RET_STS_SUCCESS;
1377
1378 --
1379 -- Api body
1380 --
1381
1382 -- Invoke table handler(CSC_PROF_BLOCKS_B_PKG.Delete_Row)
1383 CSC_PROF_BLOCKS_PKG.Delete_Row(
1384 p_BLOCK_ID => P_BLOCK_ID,
1385 p_OBJECT_VERSION_NUMBER => p_OBJECT_VERSION_NUMBER);
1386 --
1387 -- End of API body
1388 --
1389
1390 -- Standard check for p_commit
1391 IF FND_API.to_Boolean( p_commit )
1392 THEN
1393 COMMIT WORK;
1394 END IF;
1395
1396
1397 -- Standard call to get message count and if count is 1, get message info.
1398 FND_MSG_PUB.Count_And_Get
1399 ( p_count => x_msg_count,
1400 p_data => x_msg_data
1401 );
1402
1403 EXCEPTION
1404 WHEN FND_API.G_EXC_ERROR THEN
1405 ROLLBACK TO DELETE_Table_Column_PVT;
1406 x_return_status := FND_API.G_RET_STS_ERROR ;
1407 FND_MSG_PUB.Count_And_Get(
1408 p_count =>x_msg_count,
1409 p_data => x_msg_data
1410 );
1411 APP_EXCEPTION.RAISE_EXCEPTION;
1412 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1413 ROLLBACK TO DELETE_Table_Column_PVT ;
1414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1415 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
1416 p_data => x_msg_data) ;
1417 APP_EXCEPTION.RAISE_EXCEPTION;
1418 WHEN OTHERS THEN
1419 ROLLBACK TO DELETE_Table_Column_PVT ;
1420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1421 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1422 THEN
1423 FND_MSG_PUB.Build_Exc_Msg(G_PKG_NAME,l_api_name);
1424 END IF ;
1425 --FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
1426 -- p_data => x_msg_data) ;
1427 APP_EXCEPTION.RAISE_EXCEPTION;
1428 End Delete_profile_variables;
1429
1430 PROCEDURE Delete_Table_Columns(
1431 P_Api_Version_Number IN NUMBER,
1432 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1433 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1434 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1435 p_BLOCK_ID IN NUMBER,
1436 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
1437 X_Return_Status OUT NOCOPY VARCHAR2,
1438 X_Msg_Count OUT NOCOPY NUMBER,
1439 X_Msg_Data OUT NOCOPY VARCHAR2
1440 )
1441
1442 IS
1443 l_api_name CONSTANT VARCHAR2(30) := 'Delete_table_columns';
1444 l_api_version_number CONSTANT NUMBER := 1.0;
1445 Cursor C1 IS
1446 Select table_column_id, Object_version_number
1447 From CSC_PROF_TABLE_COLUMNS_VL
1448 Where block_id = p_Block_id;
1449 BEGIN
1450 -- Standard Start of API savepoint
1451 SAVEPOINT DELETE_Table_Columns_PVT;
1452
1453 -- Standard call to check for call compatibility.
1454 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1455 p_api_version_number,
1456 l_api_name,
1457 G_PKG_NAME)
1458 THEN
1459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1460 END IF;
1461
1462
1463 -- Initialize message list if p_init_msg_list is set to TRUE.
1464 IF FND_API.to_Boolean( p_init_msg_list )
1465 THEN
1466 FND_MSG_PUB.initialize;
1467 END IF;
1468
1469
1470 -- Initialize API return status to SUCCESS
1471 x_return_status := FND_API.G_RET_STS_SUCCESS;
1472
1473 --
1474 -- Api body
1475 --
1479 p_TABLE_COLUMN_ID => C1_rec.TABLE_COLUMN_ID,
1476 FOR C1_rec in C1 LOOP
1477 -- Invoke table handler(CSC_PROF_BLOCKS_B_PKG.Delete_Row)
1478 CSC_PROF_TABLE_COLUMNS_PKG.Delete_Row(
1480 p_OBJECT_VERSION_NUMBER => C1_rec.OBJECT_VERSION_NUMBER);
1481 END LOOP;
1482
1483 Update_Profile_Variable(
1484 p_api_version_number => 1.0,
1485 p_validation_level => CSC_CORE_UTILS_PVT.G_VALID_LEVEL_NONE,
1486 p_block_id => p_block_id,
1487 p_sql_stmnt_for_drilldown => NULL,
1488 px_object_version_number => PX_OBJECT_VERSION_NUMBER,
1489 x_return_status => x_return_status,
1490 x_msg_count => x_msg_count,
1491 x_msg_data => x_msg_data);
1492
1493
1494 --
1495 -- End of API body
1496 --
1497
1498 -- Standard check for p_commit
1499 IF FND_API.to_Boolean( p_commit )
1500 THEN
1501 COMMIT WORK;
1502 END IF;
1503
1504 -- Standard call to get message count and if count is 1, get message info.
1505 FND_MSG_PUB.Count_And_Get
1506 ( p_count => x_msg_count,
1507 p_data => x_msg_data
1508 );
1509
1510 EXCEPTION
1511 WHEN FND_API.G_EXC_ERROR THEN
1512 ROLLBACK TO DELETE_Table_Columns_PVT;
1513 x_return_status := FND_API.G_RET_STS_ERROR ;
1514 FND_MSG_PUB.Count_And_Get(
1515 p_count =>x_msg_count,
1516 p_data => x_msg_data
1517 );
1518 APP_EXCEPTION.RAISE_EXCEPTION;
1519 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1520 ROLLBACK TO DELETE_Table_Columns_PVT ;
1521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1522 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
1523 p_data => x_msg_data) ;
1524 APP_EXCEPTION.RAISE_EXCEPTION;
1525 WHEN OTHERS THEN
1526 ROLLBACK TO DELETE_Table_Columns_PVT ;
1527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1528 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1529 THEN
1530 FND_MSG_PUB.Build_Exc_Msg(G_PKG_NAME,l_api_name);
1531 END IF ;
1532 --FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
1533 -- p_data => x_msg_data) ;
1534 APP_EXCEPTION.RAISE_EXCEPTION;
1535 End Delete_Table_Columns;
1536
1537 ----------------------------------------------------------------------
1538 -- Validates_Profile_Variables
1539 -----------------------------------------------------------------------
1540 -- {Start Of Comments}
1541 --
1542 -- Description:
1543 --
1544 -- Access Status:
1545 -- Internal Development Use Only.
1546 --
1547 -- {End Of Comments}
1548 -- ----------------------------------------------------------------------------
1549 --------------------------------------------------------------------------
1550 -- Procedure Validate_Block_Name
1551 -- Description: Validates the block_name using the table
1552 -- cs_prof_blocks. Block_name should not be duplicated in the table.
1553 -- Input Parameters
1554 -- p_api_name, standard parameter for writting messages
1555 -- p_validation_mode, whether an update or an insert uses CSC_CORE_UTILS_PVT.G_UPDATE
1556 -- or CSC_CORE_UTILS_PVT.G_CREATE global variable
1557 -- p_block_name, block_name to be validated
1558 -- Out Parameters
1559 -- x_return_status, standard parameter for the return status
1560 --------------------------------------------------------------------------
1561
1562 PROCEDURE Validate_Block_Name
1563 ( p_api_name IN VARCHAR2,
1564 p_validation_mode IN VARCHAR2,
1565 p_block_name IN VARCHAR2,
1566 p_block_id IN NUMBER,
1567 x_return_status OUT NOCOPY VARCHAR2
1568 ) IS
1569
1570 Cursor get_block_name is
1571 Select block_id
1572 from csc_prof_blocks_vl
1573 where block_name = p_block_name;
1574 l_dummy number;
1575 BEGIN
1576
1577 -- initialize the return status
1578 x_return_status := FND_API.G_RET_STS_SUCCESS;
1579
1580 -- Check if the validation is called for a create or an
1581 -- update
1582 IF p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE THEN
1583 -- check if the block name is passed in and is NOT NULL
1584 -- if so validate else its an mandatory argument error as its
1585 -- in Create Mode.
1586 IF (( p_block_name <> CSC_CORE_UTILS_PVT.G_MISS_CHAR ) AND
1587 ( p_block_name IS NOT NULL )) THEN
1588 Open get_block_name;
1589 Fetch get_block_name into l_dummy;
1590 if get_block_name%FOUND then
1591 x_return_status := FND_API.G_RET_STS_ERROR;
1592 CSC_CORE_UTILS_PVT.Add_Duplicate_Value_Msg
1593 ( p_api_name => p_api_name,
1594 p_argument => 'p_block_name' ,
1595 p_argument_value => p_block_name);
1596 x_return_status := FND_API.G_RET_STS_ERROR;
1597 end if;
1598 Close get_block_name;
1599 ELSE
1600 -- If the block name is not passed or if passed in as NULL write a
1601 -- mandatory attribute missing message
1602 x_return_status := FND_API.G_RET_STS_ERROR;
1603 CSC_CORE_UTILS_PVT.mandatory_arg_error(
1604 p_api_name => p_api_name,
1605 p_argument => 'p_block_name',
1606 p_argument_value => p_block_name);
1607
1608 END IF;
1612 -- its a mandatory argument error.
1609 -- If the validation is called for an Update
1610 ELSIF p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE THEN
1611 -- if the block name is passed in and as NULL then
1613 if ( p_block_name IS NULL ) then
1614 x_return_status := FND_API.G_RET_STS_ERROR;
1615 CSC_CORE_UTILS_PVT.mandatory_arg_error(
1616 p_api_name => p_api_name,
1617 p_argument => 'p_block_name',
1618 p_argument_value => p_block_name);
1619
1620 elsif ( p_block_name <> CSC_CORE_UTILS_PVT.G_MISS_CHAR ) then
1621 -- Valdiate the block name
1622 Open get_block_name;
1623 Loop
1624 Fetch get_block_name into l_dummy;
1625 -- if the block name passed in is the same as
1626 -- the present one then ignore else raise an
1627 -- duplicate value error as block name should
1628 -- be unique
1629 if l_dummy <> p_block_id then
1630 x_return_status := FND_API.G_RET_STS_ERROR;
1631 CSC_CORE_UTILS_PVT.Add_Duplicate_Value_Msg
1632 ( p_api_name => p_api_name,
1633 p_argument => 'p_block_name' ,
1634 p_argument_value => p_block_name);
1635 x_return_status := FND_API.G_RET_STS_ERROR;
1636 exit;
1637 else
1638 exit;
1639 end if;
1640 End Loop;
1641 Close get_block_name;
1642 end if;
1643 END IF;
1644 END Validate_Block_Name;
1645
1646 --------------------------------------------------------------------------
1647 -- Procedure Validate_Seeded_Flag
1648 -- Description: Validates the seeded_flag from the fnd_lookups table
1649 -- using CSC_CORE_UTILS_PVT.lookup_code_not_exists function.
1650 -- Input Parameters
1651 -- p_api_name, standard parameter for writting messages
1652 -- p_validation_mode, whether an update or an insert uses CSC_CORE_UTILS_PVT.G_UPDATE
1653 -- or CSC_CORE_UTILS_PVT.G_CREATE global variable
1654 -- p_seeded_flag, seeded_flag to be validated should be an YES or a NO
1655 -- Out Parameters
1656 -- x_return_status, standard parameter for the return status
1657 --------------------------------------------------------------------------
1658
1659 PROCEDURE Validate_Seeded_Flag
1660 ( p_api_name IN VARCHAR2,
1661 p_parameter_name IN VARCHAR2,
1662 p_seeded_flag IN VARCHAR2,
1663 x_return_status OUT NOCOPY VARCHAR2
1664 ) IS
1665 --
1666 BEGIN
1667 --
1668 x_return_status := FND_API.G_RET_STS_SUCCESS;
1669
1670 -- check if the seeded flag is passed in and is not
1671 -- null, if passed in check if the lookup code
1672 -- exists in fnd lookups for this date, if not
1673 -- its an invalid argument.
1674 IF (( p_seeded_flag <> CSC_CORE_UTILS_PVT.G_MISS_CHAR ) AND
1675 ( p_seeded_flag IS NOT NULL )) THEN
1676 IF CSC_CORE_UTILS_PVT.lookup_code_not_exists(
1677 p_effective_date => trunc(sysdate),
1678 p_lookup_type => 'YES_NO',
1679 p_lookup_code => p_seeded_flag ) <> FND_API.G_RET_STS_SUCCESS
1680 THEN
1681 x_return_status := FND_API.G_RET_STS_ERROR;
1682 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(p_api_name => p_api_name,
1683 p_argument_value => p_seeded_flag,
1684 p_argument => p_parameter_name);
1685 END IF;
1686 END IF;
1687 END Validate_Seeded_Flag;
1688
1689 PROCEDURE Validate_block_level
1690 ( p_api_name IN VARCHAR2,
1691 p_parameter_name IN VARCHAR2,
1692 p_block_level IN VARCHAR2,
1693 x_return_status OUT NOCOPY VARCHAR2
1694 ) IS
1695 --
1696 BEGIN
1697 --
1698 x_return_status := FND_API.G_RET_STS_SUCCESS;
1699
1700 -- check if the block_level is passed in and is not
1701 -- null
1702
1703 IF (( p_block_level <> CSC_CORE_UTILS_PVT.G_MISS_CHAR ) AND
1704 ( p_block_level IS NOT NULL )) THEN
1705 IF (p_block_level <> 'PARTY' AND p_block_level <> 'ACCOUNT' AND p_block_level <> 'CONTACT' AND p_block_level <> 'EMPLOYEE')
1706 THEN
1707 x_return_status := FND_API.G_RET_STS_ERROR;
1708 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(p_api_name => p_api_name,
1709 p_argument_value => p_block_level,
1710 p_argument => p_parameter_name);
1711 END IF;
1712 END IF;
1713 END Validate_Block_Level;
1714
1715
1716 --------------------------------------------------------------------------
1717 -- Procedure Validate_Currecny_Code
1718 -- Description: Validates the currency_Code from the fnd_currencies table
1719 -- using CSC_CORE_UTILS_PVT.currency_code_not_exists function.
1720 -- Input Parameters
1721 -- p_api_name, standard parameter for writting messages
1722 -- p_validation_mode, whether an update or an insert uses CSC_CORE_UTILS_PVT.G_UPDATE
1723 -- or CSC_CORE_UTILS_PVT.G_CREATE global variable
1724 -- p_currrency_code, currency_code to be validated
1725 -- Out Parameters
1726 -- x_return_status, standard parameter for the return status
1727 --------------------------------------------------------------------------
1728
1729 PROCEDURE Validate_Currency_Code
1730 ( p_api_name IN VARCHAR2,
1731 p_parameter_name IN VARCHAR2,
1732 p_currency_code IN VARCHAR2,
1733 x_return_status OUT NOCOPY VARCHAR2 )
1734 IS
1735 --
1736 BEGIN
1737 --
1738 x_return_status := FND_API.G_RET_STS_SUCCESS;
1739
1740 -- if the currency code is passed in and is not null,
1741 -- validate the currency code.
1745 IF CSC_CORE_UTILS_PVT.Currency_code_not_exists(
1742 IF (( p_currency_Code <> CSC_CORE_UTILS_PVT.G_MISS_CHAR ) AND
1743 ( p_currency_code IS NOT NULL )) then
1744
1746 p_effective_date => sysdate,
1747 p_currency_code => p_currency_code ) <> FND_API.G_RET_STS_SUCCESS THEN
1748
1749 -- if the currency code is not valid its an invalid argument
1750 x_return_status := FND_API.G_RET_STS_ERROR;
1751 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(p_api_name => p_api_name,
1752 p_argument_value => p_currency_code,
1753 p_argument => p_parameter_name);
1754
1755 END IF;
1756 END IF;
1757
1758 END Validate_Currency_Code;
1759
1760 --------------------------------------------------------------------------
1761 -- Procedure Validate_Sql_Stmnt
1762 -- Description: Validates the sql_statement using the dynamic sql
1763 -- Input Parameters
1764 -- p_api_name, standard parameter for writting messages
1765 -- p_validation_mode, whether an update or an insert uses CSC_CORE_UTILS_PVT.G_UPDATE
1766 -- or CSC_CORE_UTILS_PVT.G_CREATE global variable
1767 -- p_sql_statement, concatented field using select_Clause, from_clause
1768 -- where_clause and Other_Clause columns using the Build_Sql_Stmnt
1769 -- procedure
1770 -- Out Parameters
1771 -- x_return_status, standard parameter for the return status
1772 --------------------------------------------------------------------------
1773 PROCEDURE Validate_Sql_Stmnt(
1774 p_api_name IN VARCHAR2,
1775 p_parameter_Name IN VARCHAR2,
1776 p_sql_stmnt IN VARCHAR2,
1777 x_return_status OUT NOCOPY VARCHAR2 )
1778 IS
1779 l_sql_cur_hdl INT;
1780 BEGIN
1781
1782 -- initialize the return status to SUCCESS
1783 x_return_status := FND_API.G_RET_STS_SUCCESS;
1784
1785 -- if the sql_statement is passed in and if its NOT NULL then
1786 -- validate the sql_statement by parsing it using the dbms_sql
1787 -- package.
1788
1789 CSC_CORE_UTILS_PVT.Validate_Sql_Stmnt(
1790 p_sql_stmnt => p_sql_Stmnt,
1791 x_return_status => X_return_status);
1792
1793 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1794
1795 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1796 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(
1797 p_api_name => p_api_name,
1798 p_argument_value => p_sql_stmnt,
1799 p_argument => p_parameter_name);
1800
1801 END IF;
1802
1803 END Validate_Sql_Stmnt;
1804
1805 PROCEDURE Validate_Object_ID(
1806 p_api_name IN VARCHAR2,
1807 p_parameter_Name IN VARCHAR2,
1808 p_OBJECT_CODE IN VARCHAR2,
1809 x_return_status OUT NOCOPY VARCHAR2 )
1810 IS
1811
1812 l_dummy VARCHAR2(30);
1813
1814 Cursor get_object_csr is
1815 Select NULL
1816 from jtf_objects_vl
1817 where object_code = p_object_code;
1818 BEGIN
1819
1820 -- initialize the return status to SUCCESS
1821 x_return_status := FND_API.G_RET_STS_SUCCESS;
1822
1823 IF ((p_Object_Code IS NOT NULL) AND
1824 (p_Object_Code <> CSC_CORE_UTILS_PVT.G_MISS_CHAR )) THEN
1825 Open get_object_csr;
1826 Fetch get_object_csr into l_dummy;
1827 IF get_object_csr%NOTFOUND THEN
1828 x_return_status := FND_API.G_RET_STS_ERROR;
1829 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(
1830 p_api_name => p_api_name,
1831 p_argument_value => p_object_code,
1832 p_argument => p_parameter_name);
1833 END IF;
1834 Close get_object_csr;
1835 END IF;
1836 END;
1837
1838 -------------------------------------------------------------------------
1839 --Validate_Block_Name_Code
1840 -------------------------------------------------------------------------
1841
1842 PROCEDURE Validate_block_name_code
1843 (p_api_name IN VARCHAR2,
1844 p_parameter_Name IN VARCHAR2,
1845 p_block_name_code IN VARCHAR2,
1846 p_block_id IN NUMBER,
1847 p_validation_mode IN VARCHAR2,
1848 x_return_status OUT NOCOPY VARCHAR2 )
1849 IS
1850 l_dummy VARCHAR2(1);
1851
1852 cursor get_block_code is
1853 Select null
1854 from csc_prof_blocks_b
1855 where block_name_code = p_block_name_code;
1856
1857 BEGIN
1858 x_return_status := FND_API.G_RET_STS_SUCCESS;
1859
1860 -- Check if the validation is called for a Create or
1861 -- an update
1862 IF p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE THEN
1863 -- if the block_name_code is passed in and is not null
1864 -- validate the block_name_code
1865 if (( p_block_name_code IS NOT NULL ) and
1866 ( p_block_name_code <> CSC_CORE_UTILS_PVT.G_MISS_CHAR )) then
1867 open get_block_code;
1868 fetch get_block_code into l_dummy;
1869 if get_block_code%FOUND then
1870 -- if not valid an invalid argument message
1871 x_return_status := FND_API.G_RET_STS_ERROR;
1872 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(
1873 p_api_name => p_api_name,
1874 p_argument_value => p_block_name_code,
1875 p_argument => p_parameter_name );
1876 end if;
1877 close get_block_code;
1878 else
1879 -- if block_name_code is not passed in or if passed
1880 -- in as null raise a mandatory argument error.
1881 x_return_status := FND_API.G_RET_STS_ERROR;
1882 CSC_CORE_UTILS_PVT.mandatory_arg_error(
1883 p_api_name => p_api_name,
1884 p_argument => 'p_block_name_code',
1888 ELSIF p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE THEN
1885 p_argument_value => p_block_name_code);
1886 end if;
1887 -- if its an update mode
1889 -- if the block_name_code is passed in as NULL
1890 -- its an error else validate.
1891 if p_block_name_code IS NULL THEN
1892 -- write a mandatory attributes missing message
1893 x_return_status := FND_API.G_RET_STS_ERROR;
1894 CSC_CORE_UTILS_PVT.mandatory_arg_error(
1895 p_api_name => p_api_name,
1896 p_argument => 'p_block_name_code',
1897 p_argument_value => p_block_name_code);
1898 elsif p_block_name_code <> CSC_CORE_UTILS_PVT.G_MISS_CHAR then
1899 open get_block_code;
1900 loop
1901 fetch get_block_code into l_dummy;
1902 -- if the block_name_code passed in is the same as
1903 -- the present one then ignore else raise an duplicate
1904 -- value error as block_name_code should be unique
1905 if l_dummy <> p_block_id then
1906 x_return_status := FND_API.G_RET_STS_ERROR;
1907 CSC_CORE_UTILS_PVT.Add_Duplicate_Value_Msg
1908 ( p_api_name => p_api_name,
1909 p_argument => 'p_block_name_code' ,
1910 p_argument_value => p_block_name_code);
1911 exit;
1912 else
1913 exit;
1914 end if;
1915 end loop;
1916 close get_block_code;
1917 end if;
1918 END IF;
1919 END Validate_Block_Name_Code;
1920
1921 PROCEDURE Validate_Profile_Variables(
1922 p_api_name IN VARCHAR2,
1923 p_validation_mode IN VARCHAR2,
1924 p_validate_rec IN ProfVar_Rec_Type,
1925 x_return_status OUT NOCOPY VARCHAR2,
1926 x_msg_count OUT NOCOPY NUMBER,
1927 x_msg_data OUT NOCOPY VARCHAR2 )
1928 IS
1929 BEGIN
1930
1931 -- Initialize return status to success
1932 x_return_status := FND_API.G_RET_STS_SUCCESS;
1933
1934
1935 -- Validate Block_Name
1936 Validate_Block_Name(
1937 p_api_name => p_api_name,
1938 p_validation_mode => p_validation_mode,
1939 p_block_name => p_validate_rec.block_name,
1940 p_block_id => p_Validate_rec.block_id,
1941 x_return_status => x_return_status );
1942
1943 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1944 RAISE FND_API.G_EXC_ERROR;
1945 END IF;
1946
1947 -- validate start and end date
1948 CSC_CORE_UTILS_PVT.Validate_Start_End_Dt(
1949 p_api_name => p_Api_name,
1950 p_start_date => p_validate_rec.start_date_active,
1951 p_end_date => p_validate_rec.end_date_active,
1952 x_return_status => x_return_status );
1953
1954 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1955 RAISE FND_API.G_EXC_ERROR;
1956 END IF;
1957
1958 --Validate seeded flag
1959 Validate_Seeded_Flag(
1960 p_api_name => p_api_name,
1961 p_parameter_name => 'p_Seeded_Flag',
1962 p_seeded_flag => p_validate_rec.seeded_flag,
1963 x_return_status => x_return_status );
1964
1965 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1966 RAISE FND_API.G_EXC_ERROR;
1967 END IF;
1968
1969 --Validate Block_level
1970 Validate_Block_Level(
1971 p_api_name => p_api_name,
1972 p_parameter_name => 'p_Block_Level',
1973 p_block_level => p_validate_rec.block_level,
1974 x_return_status => x_return_status );
1975
1976 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1977 RAISE FND_API.G_EXC_ERROR;
1978 END IF;
1979
1980
1981 -- Validate Sql Statement
1982 Validate_Sql_stmnt(
1983 p_api_name => p_api_name,
1984 p_parameter_name => 'p_sql_stmnt',
1985 p_sql_Stmnt => p_validate_rec.sql_stmnt,
1986 x_return_status => x_return_status );
1987
1988 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1989 RAISE FND_API.G_EXC_ERROR;
1990 END IF;
1991
1992 -- Validate Batch Sql Statement
1993 Validate_Sql_stmnt(
1994 p_api_name => p_api_name,
1995 p_parameter_name => 'p_batch_sql_stmnt',
1996 p_sql_Stmnt => p_validate_rec.batch_sql_stmnt,
1997 x_return_status => x_return_status );
1998
1999 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2000 RAISE FND_API.G_EXC_ERROR;
2001 END IF;
2002
2003 -- Validate Currency code
2004 Validate_Currency_Code(
2005 p_Api_name => p_api_name,
2006 p_parameter_name => 'p_currency_code',
2007 p_currency_code => p_validate_rec.currency_code,
2008 x_return_status => x_return_status );
2009
2010 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2011 RAISE FND_API.G_EXC_ERROR;
2012 END IF;
2013
2014 -- validate block name code
2015 Validate_block_name_Code(
2016 p_api_name => p_api_name,
2017 p_parameter_Name => 'p_block_name_code',
2018 p_block_name_code => p_validate_rec.block_name_code,
2019 p_block_id => p_validate_rec.block_id,
2020 p_validation_mode => p_Validation_mode,
2021 x_return_status => x_return_status );
2022 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2023 RAISE FND_API.G_EXC_ERROR;
2024 END IF;
2025
2026 -- Validate object code
2027 Validate_Object_Id(
2028 p_api_name => p_api_name,
2032
2029 p_parameter_Name => 'P_OBJECT_CODE',
2030 p_object_code => p_validate_rec.object_code,
2031 x_return_status => x_return_status );
2033 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2034 RAISE FND_API.G_EXC_ERROR;
2035 END IF;
2036
2037 /* This Call added for Enhancement 1781726 for Validating Application_id*/
2038 CSC_CORE_UTILS_PVT.Validate_APPLICATION_ID (
2039 P_Init_Msg_List => CSC_CORE_UTILS_PVT.G_FALSE,
2040 P_Application_ID =>p_validate_rec.application_id,
2041 X_Return_Status => x_return_status,
2042 X_Msg_Count => x_msg_count,
2043 X_Msg_Data => x_msg_data,
2044 p_effective_date => SYSDATE );
2045
2046 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2047 RAISE FND_API.G_EXC_ERROR;
2048 END IF;
2049
2050 END Validate_Profile_Variables;
2051
2052
2053 -- -------------------------------------------------------------------
2054 -- Get Profile Variable Rec
2055 -- -------------------------------------------------------------------
2056 -- {Start Of Comments}
2057 --
2058 -- Description:
2059 --
2060 -- Access Status:
2061 -- Internal Development Use Only.
2062 --
2063 -- {End Of Comments}
2064 -- ----------------------------------------------------------------------------
2065
2066 PROCEDURE GET_PROF_BLOCKS(
2067 p_Api_Name IN VARCHAR2,
2068 p_BLOCK_ID IN NUMBER,
2069 p_object_version_number NUMBER,
2070 X_PROF_BLOCKS_REC OUT NOCOPY CSC_PROF_BLOCKS_VL%ROWTYPE,
2071 X_return_status OUT NOCOPY VARCHAR2 )
2072 IS
2073 --
2074 Cursor c_Get_prof_rec is
2075 Select *
2076 From CSC_PROF_BLOCKS_VL
2077 Where block_id = p_block_id
2078 and object_version_number = p_object_version_number
2079 For update nowait;
2080
2081 Begin
2082
2083 -- Initialize the p_return_status to TRUE
2084 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2085
2086
2087 OPEN C_get_prof_rec;
2088 FETCH C_get_prof_rec INTO X_PROF_BLOCKS_REC;
2089 IF C_get_prof_rec%NOTFOUND THEN
2090 CLOSE C_Get_prof_Rec;
2091 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2092 THEN
2093 CSC_CORE_UTILS_PVT.Record_IS_LOCKED_MSG(p_API_NAME=> 'CSC_PROF_BLOCKS');
2094 x_return_status := FND_API.G_RET_STS_ERROR;
2095 END IF;
2096 raise FND_API.G_EXC_ERROR;
2097 END IF;
2098 IF C_GET_prof_Rec%ISOPEN THEN
2099 CLOSE C_Get_Prof_Rec;
2100 END IF;
2101
2102 END GET_PROF_BLOCKS ;
2103
2104 -- -------------------------------------------------------------------
2105 -- Get_Table_Column_Rec
2106 -- -------------------------------------------------------------------
2107 -- {Start Of Comments}
2108 --
2109 -- Description:
2110 --
2111 -- Access Status:
2112 -- Internal Development Use Only.
2113 --
2114 -- {End Of Comments}
2115 -- ----------------------------------------------------------------------------
2116
2117 Procedure GET_TABLE_COLUMN(
2118 p_Api_Name in VARCHAR2,
2119 p_Table_Column_Id IN NUMBER,
2120 p_object_version_number IN NUMBER,
2121 X_Table_Column_Rec OUT NOCOPY CSC_PROF_TABLE_COLUMNS_VL%ROWTYPE,
2122 X_Return_status OUT NOCOPY VARCHAR2 )
2123 IS
2124 Cursor C_Get_table_column_rec IS
2125 Select *
2126 /* TABLE_COLUMN_ID,
2127 BLOCK_ID,
2128 TABLE_NAME,
2129 COLUMN_NAME,
2130 LABEL,
2131 LAST_UPDATE_DATE,
2132 LAST_UPDATED_BY,
2133 CREATION_DATE,
2134 CREATED_BY,
2135 LAST_UPDATE_LOGIN
2136 */
2137 From CSC_PROF_TABLE_COLUMNS_VL
2138 Where Table_Column_ID = p_Table_Column_ID
2139 And object_version_number = p_object_version_number
2140 For Update NOWAIT;
2141 BEGIN
2142 -- initialze the return status
2143 x_return_status := FND_API.G_RET_STS_SUCCESS;
2144
2145 OPEN C_get_table_column_rec;
2146 FETCH C_get_table_column_rec INTO X_TABLE_COLUMN_REC;
2147 IF C_get_table_column_rec%NOTFOUND THEN
2148 CLOSE C_Get_Table_Column_rec;
2149 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2150 THEN
2151 CSC_CORE_UTILS_PVT.RECORD_IS_LOCKED_MSG(p_API_NAME=>'CSC_PROF_TABLE_COLUMNS');
2152 x_return_status := FND_API.G_RET_STS_ERROR;
2153 END IF;
2154 raise FND_API.G_EXC_ERROR;
2155 END IF;
2156 IF C_Get_table_Column_rec%ISOPEN THEN
2157 CLOSE C_get_table_Column_rec;
2158 END IF;
2159
2160
2161 END GET_TABLE_COLUMN;
2162
2163 --------------------------------------------------------------------------
2164 -- Procedure Validate_Block_Id
2165 -- Description: Validates the block_id using the table
2166 -- cs_prof_blocks.
2167 -- Input Parameters
2168 -- p_api_name, standard parameter for writting messages
2169 -- p_validation_mode, whether an update or an insert uses CSC_CORE_UTILS_PVT.G_UPDATE
2170 -- or CSC_CORE_UTILS_PVT.G_CREATE global variable
2171 -- p_block_id, block_id to be validated while updating
2172 -- Out Parameters
2173 -- x_return_status, standard parameter for the return status
2177 p_api_name IN VARCHAR2,
2174 --------------------------------------------------------------------------
2175
2176 PROCEDURE Validate_Block_Id (
2178 p_validation_mode IN VARCHAR2,
2179 p_block_id IN NUMBER,
2180 x_return_status OUT NOCOPY VARCHAR2
2181 )
2182 IS
2183 Function chk_block_id (p_block_id IN NUMBER) RETURN VARCHAR2
2184 IS
2185 Cursor get_block_id is
2186 Select NULL
2187 From csc_prof_blocks_b
2188 Where block_id = p_block_id;
2189 l_dummy varchar2(10);
2190 l_return_status VARCHAR2(30);
2191 BEGIN
2192
2193 -- Initlaize the return status to SUCCESS
2194 l_return_status := FND_API.G_RET_STS_SUCCESS;
2195
2196 Open get_block_id;
2197 Fetch get_block_id into l_dummy;
2198 IF get_block_id%NOTFOUND THEN
2199 l_return_status := FND_API.G_RET_STS_ERROR;
2200 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(
2201 p_api_name => p_api_name,
2202 p_argument_value => to_char(p_block_id),
2203 p_argument => 'p_block_id' );
2204 END IF;
2205 Close get_block_id;
2206 return (l_return_status);
2207 END chk_block_id;
2208
2209 BEGIN
2210
2211
2212
2213 -- Initlaize the return status to SUCCESS
2214 x_return_status := FND_API.G_RET_STS_SUCCESS;
2215
2216
2217 --
2218 IF p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE THEN
2219
2220 IF ( p_block_id <> CSC_CORE_UTILS_PVT.G_MISS_NUM ) AND
2221 ( p_block_id IS NOT NULL )
2222 THEN
2223 x_return_status := chk_block_id( p_block_id );
2224 ELSE
2225 x_return_status := FND_API.G_RET_STS_ERROR;
2226 CSC_CORE_UTILS_PVT.mandatory_arg_error(
2227 p_api_name => p_api_name,
2228 p_argument => 'p_block_id',
2229 p_argument_value => p_block_id);
2230 END IF;
2231
2232 ELSIF p_Validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE THEN
2233
2234 IF ( p_block_id <> CSC_CORE_UTILS_PVT.G_MISS_NUM )
2235 THEN
2236 IF (p_block_id IS NOT NULL ) THEN
2237 x_return_status := chk_block_id( p_block_id );
2238 ELSE
2239 x_return_status := FND_API.G_RET_STS_ERROR;
2240 CSC_CORE_UTILS_PVT.mandatory_arg_error(
2241 p_api_name => p_api_name,
2242 p_argument => 'p_block_id',
2243 p_argument_value => p_block_id);
2244
2245 END IF;
2246 END IF;
2247 END IF;
2248
2249 END Validate_Block_Id;
2250
2251 PROCEDURE Validate_TABLE_NAME (
2252 p_api_name IN VARCHAR2,
2253 P_Validation_mode IN VARCHAR2,
2254 P_TABLE_NAME IN VARCHAR2,
2255 X_Return_Status OUT NOCOPY VARCHAR2
2256 )
2257 IS
2258
2259 l_dummy VARCHAR2(10);
2260
2261 Cursor get_tname_csr is
2262 Select NULL
2263 from fnd_tables
2264 where table_name = p_table_name;
2265
2266 Cursor get_vname_csr is
2267 Select NULL
2268 from fnd_views
2269 where view_name = p_table_name;
2270 BEGIN
2271
2272 -- Initialize API return status to SUCCESS
2273 x_return_status := FND_API.G_RET_STS_SUCCESS;
2274
2275 IF p_TABLE_NAME is not NULL and p_TABLE_NAME <> CSC_CORE_UTILS_PVT.G_MISS_CHAR
2276 THEN
2277 Open get_tname_csr;
2278 Fetch get_tname_csr into l_dummy;
2279 IF get_tname_csr%NOTFOUND THEN
2280 Open get_vname_csr;
2281 Fetch get_vname_csr into l_dummy;
2282 IF get_vname_csr%NOTFOUND THEN
2283 x_return_status := FND_API.G_RET_STS_ERROR;
2284 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(
2285 p_api_name => p_api_name,
2286 p_argument_value => p_table_name,
2287 p_argument => 'p_Table_Name' );
2288 END IF;
2289 Close get_vname_csr;
2290 END IF;
2291 Close get_tname_csr;
2292 END IF;
2293
2294 END Validate_TABLE_NAME;
2295
2296
2297 PROCEDURE Validate_COLUMN_NAME (
2298 p_api_name IN VARCHAR2,
2299 P_Validation_mode IN VARCHAR2,
2300 P_COLUMN_NAME IN VARCHAR2,
2301 X_Return_Status OUT NOCOPY VARCHAR2
2302 )
2303 IS
2304 Cursor get_cname_csr is
2305 Select NULL
2306 from fnd_columns
2307 where column_name = p_column_name;
2308
2309 Cursor get_vcname_csr is
2310 Select NULL
2311 from fnd_view_columns
2312 where column_name = p_column_name;
2313 l_dummy VARCHAR2(10);
2314 BEGIN
2315
2316 -- Initialize API return status to SUCCESS
2317 x_return_status := FND_API.G_RET_STS_SUCCESS;
2318
2319
2320 IF p_COLUMN_NAME is not NULL and p_COLUMN_NAME <> CSC_CORE_UTILS_PVT.G_MISS_CHAR
2321 THEN
2322 Open get_cname_csr;
2323 Fetch get_cname_csr into l_dummy;
2324 IF get_cname_csr%NOTFOUND THEN
2325 Open get_vcname_csr;
2326 Fetch get_vcname_csr into l_dummy;
2327 IF get_vcname_csr%NOTFOUND THEN
2328 x_return_status := FND_API.G_RET_STS_ERROR;
2329 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(
2330 p_api_name => p_api_name,
2331 p_argument_value => p_column_name,
2332 p_argument => 'p_Column_Name' );
2333 END IF;
2337 END IF;
2334 Close get_vcname_csr;
2335 END IF;
2336 Close get_cname_csr;
2338
2339 END Validate_COLUMN_NAME;
2340
2341 PROCEDURE Validate_Table_Column(
2342 P_Api_Name IN VARCHAR2,
2343 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
2344 P_Validation_mode IN VARCHAR2,
2345 p_validate_rec IN Table_Column_Rec_Type,
2346 X_Return_Status OUT NOCOPY VARCHAR2
2347 )
2348 IS
2349 BEGIN
2350
2351 -- Initialize API return status to SUCCESS
2352 x_return_status := FND_API.G_RET_STS_SUCCESS;
2353
2354 Validate_COLUMN_NAME (
2355 p_api_name => p_api_name,
2356 P_Validation_mode => p_validation_mode,
2357 P_COLUMN_NAME => p_Validate_rec.column_name,
2358 X_Return_Status => x_return_status
2359 );
2360 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2361 raise FND_API.G_EXC_ERROR;
2362 END IF;
2363
2364 Validate_TABLE_NAME (
2365 p_api_name => p_api_name,
2366 P_Validation_mode => p_validation_mode,
2367 P_TABLE_NAME => p_Validate_rec.table_name,
2368 X_Return_Status => x_return_status
2369 );
2370 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2371 raise FND_API.G_EXC_ERROR;
2372 END IF;
2373
2374 Validate_Block_Id (
2375 p_api_name => p_api_name,
2376 p_validation_mode => p_validation_mode,
2377 p_block_id => p_Validate_rec.block_id,
2378 x_return_status => x_return_status
2379 );
2380 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2381 raise FND_API.G_EXC_ERROR;
2382 END IF;
2383
2384
2385 IF p_validate_rec.COLUMN_SEQUENCE is NULL THEN
2386 CSC_CORE_UTILS_PVT.mandatory_arg_error(
2387 p_api_name => p_api_name,
2388 p_argument => 'p_Column_Sequence',
2389 p_argument_value => p_validate_rec.Column_Sequence);
2390 END IF;
2391
2392
2393 END Validate_table_column;
2394
2395 --
2396 END CSC_Profile_Variable_Pvt;