[Home] [Help]
PACKAGE BODY: APPS.CS_COUNTERS_EXT_PVT
Source
1 PACKAGE BODY CS_COUNTERS_EXT_PVT AS
2 -- $Header: csxvcteb.pls 120.2 2005/07/25 14:02:35 appldev ship $
3
4 -- ---------------------------------------------------------
5 -- Define global variables
6 -- ---------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_Counters_EXT_PVT';
8 --G_USER CONSTANT VARCHAR2(30) := FND_GLOBAL.USER_ID;
9 --------------------------------------------------------------------------
10
11 -- ---------------------------------------------------------
12 -- Private program units
13 -- ---------------------------------------------------------
14
15 /*
16 Object : Disp
17 Scope : Private to package
18 Description : Temporary for debug TO BE REMOVED
19 Parameters : str - Message String
20 */
21 procedure disp (str in varchar2) is
22 sStr varchar2(2000) ;
23 begin
24 sStr := str;
25 while length(sStr) > 0 loop
26 -- dbms_output.put_line(substr(sStr,1,80));
27 if length(sStr) > 80 then
28 sStr := substr(sStr,81);
29 else
30 sStr := null;
31 end if;
32 end loop;
33 end disp;
34
35 Procedure ExitWithErrMsg
36 (
37 p_msg_name in varchar2,
38 p_token1_name in varchar2 := null,
39 p_token1_val in varchar2 := null,
40 p_token2_name in varchar2 := null,
41 p_token2_val in varchar2 := null,
42 p_token3_name in varchar2 := null,
43 p_token3_val in varchar2 := null,
44 p_token4_name in varchar2 := null,
45 p_token4_val in varchar2 := null
46 ) is
47 begin
48 FND_MESSAGE.SET_NAME('CS',p_msg_name);
49 if p_token1_name is not null then
50 FND_MESSAGE.SET_TOKEN(p_token1_name, p_token1_val);
51 end if;
52 if p_token2_name is not null then
53 FND_MESSAGE.SET_TOKEN(p_token2_name, p_token2_val);
54 end if;
55 if p_token3_name is not null then
56 FND_MESSAGE.SET_TOKEN(p_token3_name, p_token3_val);
57 end if;
58 if p_token4_name is not null then
59 FND_MESSAGE.SET_TOKEN(p_token4_name, p_token4_val);
60 end if;
61 --
62 FND_MSG_PUB.Add;
63 RAISE FND_API.G_EXC_ERROR;
64 end ExitWithErrMsg;
65
66 PROCEDURE Initialize_Desc_Flex
67 (
68 p_desc_flex IN DFF_Rec_Type,
69 l_desc_flex OUT NOCOPY DFF_Rec_Type
70 ) IS
71
72 BEGIN
73 IF p_desc_flex.context = FND_API.G_MISS_CHAR THEN
74 l_desc_flex.context := NULL;
75 ELSE
76 l_desc_flex.context := p_desc_flex.context;
77 END IF;
78 IF p_desc_flex.attribute1 = FND_API.G_MISS_CHAR THEN
79 l_desc_flex.attribute1 := NULL;
80 ELSE
81 l_desc_flex.attribute1 := p_desc_flex.attribute1;
82 END IF;
83 IF p_desc_flex.attribute2 = FND_API.G_MISS_CHAR THEN
84 l_desc_flex.attribute2 := NULL;
85 ELSE
86 l_desc_flex.attribute2 := p_desc_flex.attribute2;
87 END IF;
88 IF p_desc_flex.attribute3 = FND_API.G_MISS_CHAR THEN
89 l_desc_flex.attribute3 := NULL;
90 ELSE
91 l_desc_flex.attribute3 := p_desc_flex.attribute3;
92 END IF;
93 IF p_desc_flex.attribute4 = FND_API.G_MISS_CHAR THEN
94 l_desc_flex.attribute4 := NULL;
95 ELSE
96 l_desc_flex.attribute4 := p_desc_flex.attribute4;
97 END IF;
98 IF p_desc_flex.attribute5 = FND_API.G_MISS_CHAR THEN
99 l_desc_flex.attribute5 := NULL;
100 ELSE
101 l_desc_flex.attribute5 := p_desc_flex.attribute5;
102 END IF;
103 IF p_desc_flex.attribute6 = FND_API.G_MISS_CHAR THEN
104 l_desc_flex.attribute6 := NULL;
105 ELSE
106 l_desc_flex.attribute6 := p_desc_flex.attribute6;
107 END IF;
108 IF p_desc_flex.attribute7 = FND_API.G_MISS_CHAR THEN
109 l_desc_flex.attribute7 := NULL;
110 ELSE
111 l_desc_flex.attribute7 := p_desc_flex.attribute7;
112 END IF;
113 IF p_desc_flex.attribute8 = FND_API.G_MISS_CHAR THEN
114 l_desc_flex.attribute8 := NULL;
115 ELSE
116 l_desc_flex.attribute8 := p_desc_flex.attribute8;
117 END IF;
118 IF p_desc_flex.attribute9 = FND_API.G_MISS_CHAR THEN
119 l_desc_flex.attribute9 := NULL;
120 ELSE
121 l_desc_flex.attribute9 := p_desc_flex.attribute9;
122 END IF;
123 IF p_desc_flex.attribute10 = FND_API.G_MISS_CHAR THEN
124 l_desc_flex.attribute10 := NULL;
125 ELSE
126 l_desc_flex.attribute10 := p_desc_flex.attribute10;
127 END IF;
128 IF p_desc_flex.attribute11 = FND_API.G_MISS_CHAR THEN
129 l_desc_flex.attribute11 := NULL;
130 ELSE
131 l_desc_flex.attribute11 := p_desc_flex.attribute11;
132 END IF;
133 IF p_desc_flex.attribute12 = FND_API.G_MISS_CHAR THEN
134 l_desc_flex.attribute12 := NULL;
135 ELSE
136 l_desc_flex.attribute12 := p_desc_flex.attribute12;
137 END IF;
138 IF p_desc_flex.attribute13 = FND_API.G_MISS_CHAR THEN
139 l_desc_flex.attribute13 := NULL;
140 ELSE
141 l_desc_flex.attribute13 := p_desc_flex.attribute13;
142 END IF;
143 IF p_desc_flex.attribute14 = FND_API.G_MISS_CHAR THEN
144 l_desc_flex.attribute14 := NULL;
145 ELSE
146 l_desc_flex.attribute14 := p_desc_flex.attribute14;
147 END IF;
148 IF p_desc_flex.attribute15 = FND_API.G_MISS_CHAR THEN
149 l_desc_flex.attribute15 := NULL;
150 ELSE
151 l_desc_flex.attribute15 := p_desc_flex.attribute15;
152 END IF;
153
154 END Initialize_Desc_Flex;
155
156 --
157 --
158 --
159 -- ---------------------------------------------------------
160 --
161 -- Public program units
162 --
163 -- ---------------------------------------------------------
164 --
165 PROCEDURE VALIDATE_FORMULA_CTR
166 (
167 p_api_version IN NUMBER,
168 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
169 p_commit IN VARCHAR2 := FND_API.G_FALSE,
170 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2,
174 p_counter_id IN NUMBER,
175 x_valid_flag OUT NOCOPY VARCHAR2
176 ) is
177 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_FORMULA_CTR';
178 l_api_version CONSTANT NUMBER := 1.0;
179 l_return_status_full VARCHAR2(1);
180 l_s_temp VARCHAR2(100);
181 --Cursor to select all bind variables in bvars table for passed counter
182 CURSOR ctr_bvars IS
183 SELECT bind_var_name, mapped_counter_id AS counter_id,
184 mapped_inv_item_id
185 FROM cs_ctr_formula_bvars
186 WHERE counter_id = p_counter_id;
187 l_cursor_handle INTEGER;
188 l_n_temp INTEGER;
189 l_formula varchar2(255);
190 l_counter_reading NUMBER;
191 l_bind_var_value NUMBER;
192 l_bind_var_name VARCHAR2(255);
193 BEGIN
194 -- Standard Start of API savepoint
195 SAVEPOINT CS_COUNTERS_EXT_PVT;
196 -- Standard call to check for call compatibility.
197 IF NOT FND_API.Compatible_API_Call ( l_api_version,
198 p_api_version,
199 l_api_name,
200 G_PKG_NAME)
201 THEN
202 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
203 END IF;
204 -- Initialize message list if p_init_msg_list is set to TRUE.
205 IF FND_API.to_Boolean( p_init_msg_list )
206 THEN
207 FND_MSG_PUB.initialize;
208 END IF;
209 -- Initialize API return status to SUCCESS
210 x_return_status := FND_API.G_RET_STS_SUCCESS;
211 --
212 -- API body
213 --
214 -- ******************************************************************
215 -- Validate Environment
216 -- ******************************************************************
217 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
218 THEN
219 -- Debug message
220 -- Invoke validation procedures
221 null;
222 END IF;
223 --Validate counter group id only when validation level is not none
224 IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE)
225 THEN
226 null;
227 END IF;
228 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
229 RAISE FND_API.G_EXC_ERROR;
230 END IF;
231 --Parameter Validations and initialization
232 x_valid_flag := 'N';
233 BEGIN
234 SELECT formula_text INTO l_formula
235 FROM cs_counters
236 WHERE counter_id= p_counter_id;
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 ExitWithErrMsg('CS_API_CTR_INVALID');
240 END;
241
242 -- Debug Message
243 --Start Process
244 begin
245 --Open the cursor
246 l_cursor_handle := dbms_sql.open_cursor;
247 l_formula := 'SELECT '||l_formula||' FROM DUAL';
248 disp('Formula:'||l_formula||', ctr id:'||p_counter_id);
249 -- parse the formula using dual table
250 -- if formula is :a/2, in a sql statement it will become 'select :a/2 from dual'
251 DBMS_SQL.PARSE(l_cursor_handle, l_formula, dbms_sql.native);
252 --define column to select value
253 DBMS_SQL.DEFINE_COLUMN(l_cursor_handle,1,l_counter_reading);
254 FOR bvars IN ctr_bvars LOOP
255 l_bind_var_value := 100;
256 l_bind_var_name := ':'||ltrim(bvars.bind_var_name);
257 disp('Bind Var:'||l_bind_var_name||', Value:'||l_bind_var_value);
258 DBMS_SQL.BIND_VARIABLE(l_cursor_handle, l_bind_var_name, l_bind_var_value);
259 END LOOP bvars;
260 l_n_temp := dbms_sql.execute(l_cursor_handle);
261 IF dbms_sql.fetch_rows(l_cursor_handle) > 0 THEN
262 dbms_sql.column_value(l_cursor_handle,1,l_counter_reading);
263 disp('Counter value:'||l_counter_reading);
264 x_valid_flag := 'Y';
265 END IF;
266 DBMS_SQL.close_cursor(l_cursor_handle);
267 EXCEPTION
268 WHEN OTHERS THEN
269 IF DBMS_SQL.IS_OPEN(l_cursor_handle) THEN
270 DBMS_SQL.CLOSE_cursor(l_cursor_handle);
271 END IF;
272 if sqlcode <> -1008 then
273 RAISE;
274 else
275 x_valid_flag := 'N';
276 end if;
277 END;
278 disp('formula validation done...');
279 x_return_status := FND_API.G_RET_STS_SUCCESS;
280 --
281 -- End of API body
282 --
283 -- Standard check for p_commit
284 IF FND_API.to_Boolean( p_commit )
285 THEN
286 COMMIT WORK;
287 END IF;
288 -- Standard call to get message count and if count is 1, get message info.
289 FND_MSG_PUB.Count_And_Get
290 ( p_count => x_msg_count,
291 p_data => x_msg_data
292 );
293 EXCEPTION
294 WHEN FND_API.G_EXC_ERROR THEN
295 disp('4');
296 ROLLBACK TO CS_COUNTERS_EXT_PVT;
297 x_return_status := FND_API.G_RET_STS_ERROR ;
298 FND_MSG_PUB.Count_And_Get
299 (p_count => x_msg_count,
300 p_data => x_msg_data
301 );
302 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
303 disp('5');
304 ROLLBACK TO CS_COUNTERS_EXT_PVT;
305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
306 FND_MSG_PUB.Count_And_Get
307 (
308 p_count => x_msg_count,
309 p_data => x_msg_data
310 );
311 WHEN OTHERS THEN
312 disp('6'||sqlerrm);
313 ROLLBACK TO CS_COUNTERS_EXT_PVT;
314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
315 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
316 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
317 END IF;
318 FND_MSG_PUB.Count_And_Get
319 (p_count => x_msg_count ,
320 p_data => x_msg_data
321 );
322 END VALIDATE_FORMULA_CTR;
323
324 PROCEDURE VALIDATE_GRPOP_CTR
325 (
326 p_api_version IN NUMBER,
327 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
328 p_commit IN VARCHAR2 := FND_API.G_FALSE,
329 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
330 x_return_status OUT NOCOPY VARCHAR2,
331 x_msg_count OUT NOCOPY NUMBER,
332 x_msg_data OUT NOCOPY VARCHAR2,
333 p_counter_id IN NUMBER,
334 x_valid_flag OUT NOCOPY VARCHAR2
335 )
336 is
337 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_GRPOP_CTR';
338 l_api_version CONSTANT NUMBER := 1.0;
339 l_return_status_full VARCHAR2(1);
340 l_s_temp VARCHAR2(100);
341 CURSOR ctrs_to_be_calc IS
342 SELECT distinct ctr.counter_id, ctr.derive_function,
343 ctr.derive_counter_id, ctr.derive_property_id
344 FROM cs_counters ctr
345 WHERE ctr.counter_id = p_counter_id;
346 CURSOR der_filters(b_counter_id number) IS
347 SELECT filt.counter_property_id, filt.seq_no,filt.left_paren,
348 filt.right_paren, filt.relational_operator,
349 filt.logical_operator, filt.right_value,
350 nvl(pro.default_value, 'NULL') as default_value,
351 pro.property_data_type
352 FROM cs_counter_der_filters filt, cs_counter_properties pro
353 WHERE filt.counter_id = b_counter_id
354 AND pro.counter_property_id(+) = filt.counter_property_id;
355 l_sqlstr varchar2(2000);
356 l_sqlwhere varchar2(1000);
357 l_sqlfrom varchar2(1000);
358 l_cursor_handle NUMBER;
359 l_ctr_value NUMBER;
360 l_n_temp NUMBER;
361
362 --variable and arrays for binding dbmssql
363 TYPE FILTS IS RECORD(
364 BINDNAME_DEFVAL VARCHAR2(240),
365 BINDVAL_DEFVAL VARCHAR2(240),
366 BINDNAME_RIGHTVAL VARCHAR2(240),
367 BINDVAL_RIGHTVAL VARCHAR2(240),
368 BINDNAME_CTRPROPID VARCHAR2(240),
369 BINDVAL_CTRPROPID NUMBER);
370
371 TYPE T1 is TABLE OF FILTS index by binary_integer;
375
372 T2 T1;
373 i NUMBER := 1;
374 lj NUMBER := 1;
376 BINDVAL_DERIVECTRID NUMBER;
377 l_bind_varname VARCHAR2(240);
378 l_bind_varvalc VARCHAR2(240);
379 l_bind_varvaln NUMBER;
380
381 BEGIN
382 -- Standard Start of API savepoint
383 SAVEPOINT CS_COUNTERS_EXT_PVT;
384 -- Standard call to check for call compatibility.
385 IF NOT FND_API.Compatible_API_Call ( l_api_version,
386 p_api_version,
387 l_api_name,
388 G_PKG_NAME)
389 THEN
390 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
391 END IF;
392 -- Initialize message list if p_init_msg_list is set to TRUE.
393 IF FND_API.to_Boolean( p_init_msg_list )
394 THEN
395 FND_MSG_PUB.initialize;
396 END IF;
397 -- Initialize API return status to SUCCESS
398 x_return_status := FND_API.G_RET_STS_SUCCESS;
399 --
400 -- API body
401 --
402 -- ******************************************************************
403 -- Validate Environment
404 -- ******************************************************************
405 disp('1');
406 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
407 THEN
408 -- Debug message
409 -- Invoke validation procedures
410 null;
411 END IF;
412 disp('12');
413 --Validate counter group id only when validation level is not none
414 IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE)
415 THEN
416 null;
417 END IF;
418 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
419 RAISE FND_API.G_EXC_ERROR;
420 END IF;
421 --Parameter Validations and initialization
422 x_valid_flag := 'N';
423 disp('2');
424
425 -- Debug Message
426 begin
427 FOR ctrs IN ctrs_to_be_calc LOOP
428 disp('Counter ID:'||ctrs.counter_id||', func:'||ctrs.derive_function||', der id:'||ctrs.derive_counter_id);
429
430 i := 1;
431 lj := 1;
432
433 l_sqlstr := 'select '||ctrs.derive_function||'( counter_reading )';
434 l_sqlstr := l_sqlstr || ' from cs_counter_values cv';
435 l_sqlstr := l_sqlstr || ' where counter_value_id in (';
436 l_sqlstr := l_sqlstr || ' select distinct cv.counter_value_id from ';
437 l_sqlfrom := ' cs_counter_values cv';
438 l_sqlwhere := '';
439 FOR filts IN der_filters(ctrs.counter_id) LOOP
440 disp('Open for ctr:'||ctrs.counter_id) ;
441 disp('ctr_prop:'||filts.counter_property_id||',seq:'||filts.seq_no||',left:'||filts.left_paren||',right:'|| filts.right_paren||',rel opr:'||
442 filts.relational_operator||',logical:'|| filts.logical_operator||',right val:'|| filts.right_value||',def val:'||
443 filts.default_value );
444
445 l_sqlfrom := l_sqlfrom ||', cs_counter_prop_values pv';
446 l_sqlfrom := l_sqlfrom ||ltrim(rtrim(filts.seq_no));
447 l_sqlwhere := l_sqlwhere || nvl(filts.left_paren,' ')||' nvl(pv';
448 l_sqlwhere := l_sqlwhere || ltrim(rtrim(filts.seq_no));
449 l_sqlwhere := l_sqlwhere || '.property_value, '; --||filts.default_value;
450
451 T2(i).BINDVAL_DEFVAL := filts.default_value;
452 T2(i).BINDNAME_DEFVAL := ':x_default_value'||ltrim(rtrim(filts.seq_no));
453
454 if filts.property_data_type = 'NUMBER' then
455 l_sqlwhere := l_sqlwhere ||':x_default_value'||ltrim(rtrim(filts.seq_no));
456 elsif filts.property_data_type = 'DATE' then
457 l_sqlwhere := l_sqlwhere || 'to_date( '||':x_default_value'||ltrim(rtrim(filts.seq_no))||','||'''DD-MON-RRRR'''||' )';
458 else
459 l_sqlwhere := l_sqlwhere || ':x_default_value'||ltrim(rtrim(filts.seq_no));
460 end if;
461
462 l_sqlwhere := l_sqlwhere ||')'||filts.relational_operator;
463
464 T2(i).BINDVAL_RIGHTVAL := filts.right_value;
465 T2(i).BINDNAME_RIGHTVAL := ':x_right_value'||ltrim(rtrim(filts.seq_no));
466
467 if filts.property_data_type = 'NUMBER' then
468 l_sqlwhere := l_sqlwhere || ':x_right_value'||ltrim(rtrim(filts.seq_no));
469 elsif filts.property_data_type = 'DATE' then
470 l_sqlwhere := l_sqlwhere || 'to_date( '||':x_right_value'||ltrim(rtrim(filts.seq_no))||','||'''DD-MON-RRRR'''||' )';
471 else
472 l_sqlwhere := l_sqlwhere || ':x_right_value'||ltrim(rtrim(filts.seq_no));
473 end if;
474
475 l_sqlwhere := l_sqlwhere || nvl(filts.right_paren,' ');
476 l_sqlwhere := l_sqlwhere || filts.logical_operator;
477 l_sqlwhere := l_sqlwhere || ' and pv'||ltrim(rtrim(filts.seq_no)) ;
478 l_sqlwhere := l_sqlwhere || '.counter_value_id = cv.counter_value_id ';
479 l_sqlwhere := l_sqlwhere || ' and pv'||ltrim(rtrim(filts.seq_no)) ;
480 l_sqlwhere := l_sqlwhere || '.counter_property_id = ';
481
482 T2(i).BINDVAL_CTRPROPID := filts.counter_property_id;
483 T2(i).BINDNAME_CTRPROPID := ':x_ctr_prop_id'||ltrim(rtrim(filts.seq_no));
484 l_sqlwhere := l_sqlwhere ||':x_ctr_prop_id'||ltrim(rtrim(filts.seq_no));
485
486 l_sqlwhere := l_sqlwhere || ' and cv.counter_id = ';
490 disp(l_sqlstr);
487 l_sqlwhere := l_sqlwhere || ':x_derive_counter_id';
488 END LOOP;
489 l_sqlstr := l_sqlstr || l_sqlfrom || ' where '||l_sqlwhere||')';
491 l_cursor_handle := dbms_sql.open_cursor;
492 DBMS_SQL.PARSE(l_cursor_handle, l_sqlstr, dbms_sql.native);
493 DBMS_SQL.DEFINE_COLUMN(l_cursor_handle,1,l_ctr_value);
494
495 BINDVAL_DERIVECTRID := ctrs.derive_counter_id;
496 DBMS_SQL.BIND_VARIABLE(l_cursor_handle, ':x_derive_counter_id',BINDVAL_DERIVECTRID);
497
498 while lj < i+1
499 loop
500 l_bind_varname := t2(lj).BINDNAME_DEFVAL;
501 l_bind_varvalc := t2(lj).BINDVAL_DEFVAL;
502 DBMS_SQL.BIND_VARIABLE(l_cursor_handle, l_bind_varname, l_bind_varvalc);
503 l_bind_varname := t2(lj).BINDNAME_RIGHTVAL;
504 l_bind_varvalc := t2(lj).BINDVAL_RIGHTVAL;
505 DBMS_SQL.BIND_VARIABLE(l_cursor_handle, l_bind_varname, l_bind_varvalc);
506 l_bind_varname := t2(lj).BINDNAME_CTRPROPID;
507 l_bind_varvaln := t2(lj).BINDVAL_CTRPROPID;
508 DBMS_SQL.BIND_VARIABLE(l_cursor_handle, l_bind_varname, l_bind_varvaln);
509 lj:= lj+1;
510 end loop;
511
512 l_n_temp := dbms_sql.execute(l_cursor_handle);
513 IF dbms_sql.fetch_rows(l_cursor_handle) > 0 THEN
514 dbms_sql.column_value(l_cursor_handle,1,l_ctr_value);
515 disp('Counter value:'||l_ctr_value);
516 END IF;
517 DBMS_SQL.close_cursor(l_cursor_handle);
518 x_valid_flag := 'Y';
519 END LOOP;
520 EXCEPTION
521 WHEN OTHERS THEN
522 IF DBMS_SQL.IS_OPEN(l_cursor_handle) THEN
523 DBMS_SQL.CLOSE_cursor(l_cursor_handle);
524 END IF;
525 RAISE;
526 END;
527
528 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
529 RAISE FND_API.G_EXC_ERROR;
530 END IF;
531 --
532 -- End of API body
533 --
534 -- Standard check for p_commit
535 IF FND_API.to_Boolean( p_commit )
536 THEN
537 COMMIT WORK;
538 END IF;
539 -- Standard call to get message count and if count is 1, get message info.
540 FND_MSG_PUB.Count_And_Get
541 ( p_count => x_msg_count,
542 p_data => x_msg_data
543 );
544 EXCEPTION
545 WHEN FND_API.G_EXC_ERROR THEN
546 disp('4');
547 ROLLBACK TO CS_COUNTERS_EXT_PVT;
548 x_return_status := FND_API.G_RET_STS_ERROR ;
549 FND_MSG_PUB.Count_And_Get
550 (p_count => x_msg_count,
551 p_data => x_msg_data
552 );
553 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
554 disp('5');
555 ROLLBACK TO CS_COUNTERS_EXT_PVT;
556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
557 FND_MSG_PUB.Count_And_Get
558 (
559 p_count => x_msg_count,
560 p_data => x_msg_data
561 );
562 WHEN OTHERS THEN
563 disp('6'||sqlerrm);
564 ROLLBACK TO CS_COUNTERS_EXT_PVT;
565 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
566 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
567 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
568 END IF;
569 FND_MSG_PUB.Count_And_Get
570 (p_count => x_msg_count ,
571 p_data => x_msg_data
572 );
573 End VALIDATE_GRPOP_CTR;
574
575 PROCEDURE Check_Reqd_Param
576 (
577 p_var1 IN NUMBER,
578 p_param_name IN VARCHAR2,
579 p_api_name IN VARCHAR2
580 ) IS
581 BEGIN
582 IF (NVL(p_var1,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) THEN
583 FND_MESSAGE.SET_NAME('CS','CS_API_ALL_MISSING_PARAM');
584 FND_MESSAGE.SET_TOKEN('API_NAME',p_api_name);
585 FND_MESSAGE.SET_TOKEN('MISSING_PARAM',p_param_name);
586 FND_MSG_PUB.Add;
587 RAISE FND_API.G_EXC_ERROR;
588 END IF;
589 END Check_Reqd_Param;
590
591
592 PROCEDURE Check_Reqd_Param
593 (
594 p_var1 IN VARCHAR2,
595 p_param_name IN VARCHAR2,
596 p_api_name IN VARCHAR2
597 ) IS
598 BEGIN
599 IF (NVL(p_var1,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR) THEN
600 FND_MESSAGE.SET_NAME('CS','CS_API_ALL_MISSING_PARAM');
601 FND_MESSAGE.SET_TOKEN('API_NAME',p_api_name);
602 FND_MESSAGE.SET_TOKEN('MISSING_PARAM',p_param_name);
603 FND_MSG_PUB.Add;
604 RAISE FND_API.G_EXC_ERROR;
605 END IF;
606 END Check_Reqd_Param;
607
608
609 PROCEDURE Check_Reqd_Param
610 (
611 p_var1 IN DATE,
612 p_param_name IN VARCHAR2,
613 p_api_name IN VARCHAR2
614 ) IS
615 BEGIN
616 IF (NVL(p_var1,FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE) THEN
617 FND_MESSAGE.SET_NAME('CS','CS_API_ALL_MISSING_PARAM');
618 FND_MESSAGE.SET_TOKEN('API_NAME',p_api_name);
619 FND_MESSAGE.SET_TOKEN('MISSING_PARAM',p_param_name);
620 FND_MSG_PUB.Add;
621 RAISE FND_API.G_EXC_ERROR;
622 END IF;
623 END Check_Reqd_Param;
624
625 FUNCTION Is_StartEndDate_Valid
626 (
627 p_st_dt IN DATE,
631
628 p_end_dt IN DATE,
629 p_stack_err_msg IN BOOLEAN := TRUE
630 ) RETURN BOOLEAN IS
632 l_return_value BOOLEAN := TRUE;
633
634 BEGIN
635 IF (p_st_dt > p_end_dt) THEN
636 l_return_value := FALSE;
637 IF ( p_stack_err_msg = TRUE ) THEN
638 FND_MESSAGE.SET_NAME('CS','CS_ALL_START_DATE_AFTER_END');
639 FND_MESSAGE.SET_TOKEN('START_DATE',p_st_dt);
640 FND_MESSAGE.SET_TOKEN('END_DATE',p_end_dt);
641 FND_MSG_PUB.Add;
642 END IF;
643 END IF;
644 RETURN l_return_value;
645
646 END Is_StartEndDate_Valid;
647
648 FUNCTION Is_Flag_YorNorNull
649 (
650 p_flag IN VARCHAR2,
651 p_stack_err_msg IN BOOLEAN := TRUE
652 ) RETURN BOOLEAN IS
653
654 l_return_value BOOLEAN := TRUE;
655
656 BEGIN
657 IF (p_flag NOT IN ('Y','N',NULL)) THEN
658 l_return_value := FALSE;
659 IF ( p_stack_err_msg = TRUE ) THEN
660 FND_MESSAGE.SET_NAME('CS','CS_API_INVALID_FLAG');
661 FND_MESSAGE.SET_TOKEN('FLAG',p_flag);
662 FND_MSG_PUB.Add;
663 END IF;
664 END IF;
665 RETURN l_return_value;
666
667 END Is_Flag_YorNorNull;
668
669 PROCEDURE Add_Desc_Flex_Msg
670 ( p_token_an IN VARCHAR2,
671 p_token_dfm IN VARCHAR2 )
672 IS
673 BEGIN
674 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
675 fnd_message.set_name('CS', 'CS_API_SR_DESC_FLEX_ERROR');
676 fnd_message.set_token('API_NAME', p_token_an);
677 fnd_message.set_token('DESC_FLEX_MSG', p_token_dfm);
678 fnd_msg_pub.add;
679 END IF;
680 END Add_Desc_Flex_Msg;
681
682 PROCEDURE Is_DescFlex_Valid
683 (
684 p_api_name IN VARCHAR2,
685 p_appl_short_name IN VARCHAR2 := 'CS',
686 p_desc_flex_name IN VARCHAR2,
687 p_seg_partial_name IN VARCHAR2,
688 p_num_of_attributes IN NUMBER,
689 p_seg_values IN DFF_Rec_Type,
690 p_stack_err_msg IN BOOLEAN := TRUE
691 ) IS
692
693 p_desc_context VARCHAR2(30);
694 p_desc_col_name1 VARCHAR2(30) := p_seg_partial_name||'1';
695 p_desc_col_name2 VARCHAR2(30) := p_seg_partial_name||'2';
696 p_desc_col_name3 VARCHAR2(30) := p_seg_partial_name||'3';
697 p_desc_col_name4 VARCHAR2(30) := p_seg_partial_name||'4';
698 p_desc_col_name5 VARCHAR2(30) := p_seg_partial_name||'5';
699 p_desc_col_name6 VARCHAR2(30) := p_seg_partial_name||'6';
700 p_desc_col_name7 VARCHAR2(30) := p_seg_partial_name||'7';
701 p_desc_col_name8 VARCHAR2(30) := p_seg_partial_name||'8';
702 p_desc_col_name9 VARCHAR2(30) := p_seg_partial_name||'9';
703 p_desc_col_name10 VARCHAR2(30) := p_seg_partial_name||'10';
704 p_desc_col_name11 VARCHAR2(30) := p_seg_partial_name||'11';
705 p_desc_col_name12 VARCHAR2(30) := p_seg_partial_name||'12';
706 p_desc_col_name13 VARCHAR2(30) := p_seg_partial_name||'13';
707 p_desc_col_name14 VARCHAR2(30) := p_seg_partial_name||'14';
708 p_desc_col_name15 VARCHAR2(30) := p_seg_partial_name||'15';
709 l_return_status VARCHAR2(1);
710 l_resp_appl_id NUMBER;
711 l_resp_id NUMBER;
712 l_return_value BOOLEAN := TRUE;
713
714 BEGIN
715 IF p_num_of_attributes > 15 THEN
716 /* More than 15 attributes not currently supported. Please contact developer. */
717 FND_MESSAGE.SET_NAME('CS','CS_API_NUM_OF_DESCFLEX_GT_MAX');
718 FND_MESSAGE.SET_TOKEN('API_NAME',p_api_name);
719 FND_MSG_PUB.Add;
720 RAISE FND_API.G_EXC_ERROR;
721 END IF;
722
723 Validate_Desc_Flex
724 (
725 p_api_name,
726 p_appl_short_name,
727 p_desc_flex_name,
728 p_desc_col_name1,
729 p_desc_col_name2,
730 p_desc_col_name3,
731 p_desc_col_name4,
732 p_desc_col_name5,
733 p_desc_col_name6,
734 p_desc_col_name7,
735 p_desc_col_name8,
736 p_desc_col_name9,
737 p_desc_col_name10,
738 p_desc_col_name11,
739 p_desc_col_name12,
740 p_desc_col_name13,
741 p_desc_col_name14,
742 p_desc_col_name15,
743 p_seg_values.attribute1,
744 p_seg_values.attribute2,
745 p_seg_values.attribute3,
746 p_seg_values.attribute4,
747 p_seg_values.attribute5,
748 p_seg_values.attribute6,
749 p_seg_values.attribute7,
750 p_seg_values.attribute8,
751 p_seg_values.attribute9,
752 p_seg_values.attribute10,
753 p_seg_values.attribute11,
754 p_seg_values.attribute12,
755 p_seg_values.attribute13,
756 p_seg_values.attribute14,
757 p_seg_values.attribute15,
758 p_seg_values.context,
759 l_resp_appl_id,
760 l_resp_id,
761 l_return_status );
762
763 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
764 RAISE FND_API.G_EXC_ERROR;
765 end if;
766 END Is_DescFlex_Valid;
767
768 ------------------------------------------------------------------------------
769 -- Procedure : Validate_Desc_Flex
770 ------------------------------------------------------------------------------
771
772 PROCEDURE Validate_Desc_Flex
773 ( p_api_name IN VARCHAR2,
774 p_appl_short_name IN VARCHAR2 := 'CS',
778 p_column_name3 IN VARCHAR2,
775 p_desc_flex_name IN VARCHAR2,
776 p_column_name1 IN VARCHAR2,
777 p_column_name2 IN VARCHAR2,
779 p_column_name4 IN VARCHAR2,
780 p_column_name5 IN VARCHAR2,
781 p_column_name6 IN VARCHAR2,
782 p_column_name7 IN VARCHAR2,
783 p_column_name8 IN VARCHAR2,
784 p_column_name9 IN VARCHAR2,
785 p_column_name10 IN VARCHAR2,
786 p_column_name11 IN VARCHAR2,
787 p_column_name12 IN VARCHAR2,
788 p_column_name13 IN VARCHAR2,
789 p_column_name14 IN VARCHAR2,
790 p_column_name15 IN VARCHAR2,
791 p_column_value1 IN VARCHAR2,
792 p_column_value2 IN VARCHAR2,
793 p_column_value3 IN VARCHAR2,
794 p_column_value4 IN VARCHAR2,
795 p_column_value5 IN VARCHAR2,
796 p_column_value6 IN VARCHAR2,
797 p_column_value7 IN VARCHAR2,
798 p_column_value8 IN VARCHAR2,
799 p_column_value9 IN VARCHAR2,
800 p_column_value10 IN VARCHAR2,
801 p_column_value11 IN VARCHAR2,
802 p_column_value12 IN VARCHAR2,
803 p_column_value13 IN VARCHAR2,
804 p_column_value14 IN VARCHAR2,
805 p_column_value15 IN VARCHAR2,
806 p_context_value IN VARCHAR2,
807 p_resp_appl_id IN NUMBER := NULL,
808 p_resp_id IN NUMBER := NULL,
809 x_return_status OUT NOCOPY VARCHAR2 )
810 IS
811 l_error_message VARCHAR2(2000);
812 BEGIN
813 x_return_status := fnd_api.g_ret_sts_success;
814
815 fnd_flex_descval.set_column_value(p_column_name1, p_column_value1);
816 fnd_flex_descval.set_column_value(p_column_name2, p_column_value2);
817 fnd_flex_descval.set_column_value(p_column_name3, p_column_value3);
818 fnd_flex_descval.set_column_value(p_column_name4, p_column_value4);
819 fnd_flex_descval.set_column_value(p_column_name5, p_column_value5);
820 fnd_flex_descval.set_column_value(p_column_name6, p_column_value6);
821 fnd_flex_descval.set_column_value(p_column_name7, p_column_value7);
822 fnd_flex_descval.set_column_value(p_column_name8, p_column_value8);
823 fnd_flex_descval.set_column_value(p_column_name9, p_column_value9);
824 fnd_flex_descval.set_column_value(p_column_name10, p_column_value10);
825 fnd_flex_descval.set_column_value(p_column_name11, p_column_value11);
826 fnd_flex_descval.set_column_value(p_column_name12, p_column_value12);
827 fnd_flex_descval.set_column_value(p_column_name13, p_column_value13);
828 fnd_flex_descval.set_column_value(p_column_name14, p_column_value14);
829 fnd_flex_descval.set_column_value(p_column_name15, p_column_value15);
830 fnd_flex_descval.set_context_value(p_context_value);
831 IF NOT fnd_flex_descval.validate_desccols
832 ( appl_short_name => p_appl_short_name,
833 desc_flex_name => p_desc_flex_name,
834 resp_appl_id => p_resp_appl_id,
835 resp_id => p_resp_id ) THEN
836 l_error_message := fnd_flex_descval.error_message;
837 add_desc_flex_msg(p_api_name, l_error_message);
838 x_return_status := fnd_api.g_ret_sts_error;
839 END IF;
840 END Validate_Desc_Flex;
841
842 END CS_COUNTERS_EXT_PVT;