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