[Home] [Help]
PACKAGE BODY: APPS.CN_PLANCOPY_UTIL_PVT
Source
1 PACKAGE BODY CN_PLANCOPY_UTIL_PVT AS
2 /*$Header: cnpcutlb.pls 120.8.12020000.3 2013/02/21 11:51:05 avnalam ship $*/
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PLANCOPY_UTIL_PVT';
4 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnpcutlb.pls';
5
6
7
8 PROCEDURE get_unique_name_for_component (
9 p_id IN NUMBER,
10 p_org_id IN NUMBER,
11 p_type IN VARCHAR2,
12 p_suffix IN VARCHAR2,
13 p_prefix IN VARCHAR2,
14 x_name OUT NOCOPY VARCHAR2,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_msg_count OUT NOCOPY NUMBER,
17 x_msg_data OUT NOCOPY VARCHAR2
18 ) IS
19
20 l_api_name CONSTANT VARCHAR2(30) := 'get_unique_name_for_component';
21 l_unique_identifier VARCHAR2(5);
22 l_temp_name VARCHAR2(200);
23 l_obj_name VARCHAR2 (80);
24 l_obj_length NUMBER;
25 l_msg_copy varchar2(10);
26 l_length number;
30 BEGIN
27 RAND_NUM_LENGTH number := 3;
28 l_length_expscn number;
29
31 -- Standard Start of API savepoint
32 SAVEPOINT get_unique_name_for_component;
33
34 -- Initialize API return status to success
35 x_return_status := FND_API.G_RET_STS_SUCCESS;
36
37
38 -- ******* All the code needs to be written here *****
39 -- PLAN ELEMENT Name : 80 characters
40 -- PLAN Name : 30 characters
41 -- RATE TABLE NAME : 80 characters
42 -- RATE DIM NAME : 30 characters
43 -- EXPRESSION Name : 30 Chararcters
44 -- Formula Name : 30 characters
45 -- Scenario Name : 80 characters
46
47 select substr(to_char(abs(dbms_random.random)),1,RAND_NUM_LENGTH) into l_unique_identifier from dual;
48 select fnd_message.get_string('CN','CN_COPY_MSG') into l_msg_copy from dual;
49
50 -- Magic number 2 is for an underscore and 1 extra character
51
52 l_length := RAND_NUM_LENGTH + 2 + LENGTHB(l_msg_copy);
53
54 l_length_expscn := RAND_NUM_LENGTH + 2 + LENGTHB(fnd_message.get_string('CN','CN_PLANS'));
55
56 -- Whereever the length of column is 30 is present substr of 23 is used to accomadate to add an hyphen and 5 characters of unique string.
57
58 if (p_type = 'PLAN') THEN
59 SELECT SUBSTRB(NAME,1,(30-l_length)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_comp_plans_all where org_id = p_org_id and comp_plan_id = p_id;
60 ELSIF (p_type = 'PLANELEMENT') THEN
61 SELECT SUBSTRB(NAME,1,(80-l_length)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_quotas_all where org_id = p_org_id and quota_id = p_id;
62 ELSIF (p_type = 'EXPRESSION') THEN
63 SELECT SUBSTRB(NAME,1,(30-l_length)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_calc_sql_exps_all where org_id = p_org_id and CALC_SQL_EXP_ID = p_id;
64 ELSIF (p_type = 'RATETABLE') THEN
65 SELECT SUBSTRB(NAME,1,(80-l_length)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_rate_schedules_all where org_id = p_org_id and RATE_SCHEDULE_ID = p_id;
66 ELSIF (p_type = 'RATEDIMENSION') THEN
67 SELECT SUBSTRB(NAME,1,(30-l_length)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_rate_dimensions_all where org_id = p_org_id and RATE_DIMENSION_ID = p_id;
68 ELSIF (p_type = 'FORMULA') THEN
69 SELECT SUBSTRB(NAME,1,(30-l_length)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_calc_formulas_all where org_id = p_org_id and CALC_FORMULA_ID = p_id;
70 ELSIF (p_type = 'SCENARIO') THEN
71 SELECT SUBSTRB(NAME,1,(80-l_length)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_scenarios_all where org_id = p_org_id and scenario_id = p_id;
72 ELSIF (p_type = 'EXPSCENARIO') THEN
73 SELECT SUBSTRB(NAME,1,(80-l_length_expscn)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_scenarios_all where org_id = p_org_id and scenario_id = p_id;
74 END IF;
75
76 if (p_type = 'EXPSCENARIO') THEN
77 x_name := l_obj_name || fnd_message.get_string('CN','CN_PLANS') || l_unique_identifier;
78 else
79 x_name := l_obj_name || '_'|| l_msg_copy || l_unique_identifier;
80 end if;
81 -- ******* End of API body. ********
82
83 -- Standard call to get message count and if count is 1, get message info.
84 FND_MSG_PUB.Count_And_Get
85 (p_count => x_msg_count ,
86 p_data => x_msg_data ,
87 p_encoded => FND_API.G_FALSE );
88 EXCEPTION
89 WHEN FND_API.G_EXC_ERROR THEN
90 ROLLBACK TO get_unique_name_for_component;
91 x_return_status := FND_API.G_RET_STS_ERROR ;
92 FND_MSG_PUB.count_and_get
93 (p_count => x_msg_count ,
94 p_data => x_msg_data ,
95 p_encoded => FND_API.G_FALSE );
96 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
97 ROLLBACK TO get_unique_name_for_component;
98 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
99 FND_MSG_PUB.count_and_get
100 (p_count => x_msg_count ,
101 p_data => x_msg_data ,
102 p_encoded => FND_API.G_FALSE );
103 WHEN OTHERS THEN
104 ROLLBACK TO get_unique_name_for_component;
105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
106 IF FND_MSG_PUB.check_msg_level
107 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
108 THEN
109 FND_MSG_PUB.add_exc_msg
110 (G_PKG_NAME ,
111 l_api_name );
112 END IF;
113 FND_MSG_PUB.count_and_get
114 (p_count => x_msg_count ,
115 p_data => x_msg_data ,
116 p_encoded => FND_API.G_FALSE );
117 END get_unique_name_for_component;
118
119
120 FUNCTION blob_to_clob (blob_in IN BLOB)
121 RETURN CLOB
122 AS
123 v_clob CLOB;
124 /* v_varchar VARCHAR2(32767);
125 v_start PLS_INTEGER := 1;
126 v_buffer PLS_INTEGER := 32767;*/
127
128 --Added to resolve bug 14598731
129
130 v_dest_offsset INTEGER := 1;
131 v_src_offsset INTEGER := 1;
132 v_lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
133 v_warning INTEGER :=0;
134 v_blob_csid NUMBER := nls_charset_id(fnd_profile.value('FND_NATIVE_CLIENT_ENCODING'));
135
136 --addition ends here
137
138 BEGIN
139 DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
140
141 -- fnd_file.put_line(fnd_file.LOG, 'conversion from blob to clob');
142 --commented below code to resolve the bug 14598731
143 /*
144 FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
148 v_start := v_start + v_buffer;
145 LOOP
146 v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
147 DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
149 END LOOP;
150
151 RETURN v_clob;
152 */ DBMS_LOB.CONVERTTOCLOB(dest_lob=>v_clob,
153 src_blob=>blob_in,
154 amount=>DBMS_LOB.LOBMAXSIZE,
155 dest_offset => v_dest_offsset,
156 src_offset => v_src_offsset,
157 blob_csid => v_blob_csid,
158 lang_context => v_lang_context,
159 warning => v_warning
160 );
161
162
163 IF v_warning=DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
164
165 fnd_file.put_line(fnd_file.LOG, 'Atleast one character is not converted properly from blob to clob');
166
167 RETURN NULL;
168
169 ELSE
170
171 RETURN v_clob;
172
173 END IF;
174
175
176
177 END blob_to_clob;
178
179
180 FUNCTION clob_to_xmltype (clob_in IN CLOB)
181 RETURN XMLTYPE
182 AS
183 v_xmltype XMLTYPE;
184 BEGIN
185 v_xmltype := XMLTYPE(clob_in);
186 RETURN v_xmltype;
187 END clob_to_xmltype;
188
189 PROCEDURE convert_blob_to_clob
190 ( p_api_version IN NUMBER ,
191 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
192 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
193 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
194 p_exp_imp_id IN CN_COPY_REQUESTS_ALL.EXP_IMP_REQUEST_ID%TYPE,
195 x_return_status OUT NOCOPY VARCHAR2,
196 x_msg_count OUT NOCOPY NUMBER,
197 x_msg_data OUT NOCOPY VARCHAR2
198 ) IS
199 l_api_name CONSTANT VARCHAR2(30) := 'convert_blob_to_clob';
200 l_blob blob;
201 l_clob clob;
202 l_api_version CONSTANT NUMBER := 1.0;
203
204
205 BEGIN
206
207 -- Standard Start of API savepoint
208 SAVEPOINT convert_blob_to_clob;
209
210 -- Standard call to check for call compatibility.
211 IF NOT FND_API.Compatible_API_Call
212 (l_api_version ,
213 p_api_version ,
214 l_api_name ,
215 G_PKG_NAME )
216 THEN
217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
218 END IF;
219 -- Initialize message list if p_init_msg_list is set to TRUE.
220 IF FND_API.to_Boolean( p_init_msg_list ) THEN
221 FND_MSG_PUB.initialize;
222 END IF;
223
224 -- Initialize API return status to success
225 x_return_status := FND_API.G_RET_STS_SUCCESS;
226
227 -- ******* API Begins ************
228 -- TODO : Error handling
229
230 select file_content_blob into l_blob from cn_copy_requests_all
231 where EXP_IMP_REQUEST_ID = p_exp_imp_id;
232
233
234 l_clob := blob_to_clob(l_blob);
235 --Added to resolve the bug 14598731
236 IF l_clob IS NULL THEN
237
238 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
239
240 END IF;
241
242 --addtion ends 14598731
243
244
245 update cn_copy_requests_all set file_content_clob = l_clob where EXP_IMP_REQUEST_ID = p_exp_imp_id;
246
247 -- ******* End of API body. ********
248 -- Standard check of p_commit.
249 IF FND_API.To_Boolean( p_commit ) THEN
250 COMMIT WORK;
251 END IF;
252
253 -- Standard call to get message count and if count is 1, get message info.
254 FND_MSG_PUB.Count_And_Get
255 (p_count => x_msg_count ,
256 p_data => x_msg_data ,
257 p_encoded => FND_API.G_FALSE );
258
259 EXCEPTION
260 WHEN FND_API.G_EXC_ERROR THEN
261 ROLLBACK TO convert_blob_to_clob;
262 x_return_status := FND_API.G_RET_STS_ERROR ;
263 FND_MSG_PUB.count_and_get
264 (p_count => x_msg_count ,
265 p_data => x_msg_data ,
266 p_encoded => FND_API.G_FALSE );
267 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
268 ROLLBACK TO convert_blob_to_clob;
269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
270 FND_MSG_PUB.count_and_get
271 (p_count => x_msg_count ,
272 p_data => x_msg_data ,
273 p_encoded => FND_API.G_FALSE );
274 WHEN OTHERS THEN
275 ROLLBACK TO convert_blob_to_clob;
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
277 IF FND_MSG_PUB.check_msg_level
278 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
279 THEN
280 FND_MSG_PUB.add_exc_msg
281 (G_PKG_NAME ,
282 l_api_name );
283 END IF;
284 FND_MSG_PUB.count_and_get
285 (p_count => x_msg_count ,
286 p_data => x_msg_data ,
287 p_encoded => FND_API.G_FALSE );
288 END convert_blob_to_clob;
289
290
291
292 PROCEDURE convert_clob_to_xmltype
293 ( p_api_version IN NUMBER ,
294 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
295 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
296 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
297 p_exp_imp_id IN CN_COPY_REQUESTS_ALL.EXP_IMP_REQUEST_ID%TYPE,
298 x_return_status OUT NOCOPY VARCHAR2,
299 x_msg_count OUT NOCOPY NUMBER,
300 x_msg_data OUT NOCOPY VARCHAR2
301 )
302 IS
303 l_api_name CONSTANT VARCHAR2(30) := 'convert_clob_to_xmltype';
304 l_api_version CONSTANT NUMBER := 1.0;
305
306 l_xmltype xmltype;
307 l_clob clob;
308 BEGIN
309
310 -- Standard Start of API savepoint
311 SAVEPOINT convert_clob_to_xmltype;
312
313 -- Standard call to check for call compatibility.
314 IF NOT FND_API.Compatible_API_Call
315 (l_api_version ,
316 p_api_version ,
317 l_api_name ,
318 G_PKG_NAME )
319 THEN
320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
321 END IF;
322 -- Initialize message list if p_init_msg_list is set to TRUE.
323 IF FND_API.to_Boolean( p_init_msg_list ) THEN
324 FND_MSG_PUB.initialize;
325 END IF;
326
327 -- Initialize API return status to success
328 x_return_status := FND_API.G_RET_STS_SUCCESS;
329
330 -- ******* API Begins ************
331 -- TODO : Error handling, waiting for xmltype to be added
332
333 select file_content_clob into l_clob from cn_copy_requests_all
334 where EXP_IMP_REQUEST_ID = p_exp_imp_id;
335
336 l_xmltype := XMLTYPE(l_clob);
337
338 update cn_copy_requests_all set file_content_xmltype = l_xmltype where EXP_IMP_REQUEST_ID = p_exp_imp_id;
339
340 -- ******* End of API body. ********
341 -- Standard check of p_commit.
342 IF FND_API.To_Boolean( p_commit ) THEN
343 COMMIT WORK;
344 END IF;
345
346 -- Standard call to get message count and if count is 1, get message info.
347 FND_MSG_PUB.Count_And_Get
348 (p_count => x_msg_count ,
349 p_data => x_msg_data ,
350 p_encoded => FND_API.G_FALSE );
351
352 EXCEPTION
353 WHEN FND_API.G_EXC_ERROR THEN
354 ROLLBACK TO convert_clob_to_xmltype;
355 x_return_status := FND_API.G_RET_STS_ERROR ;
356 FND_MSG_PUB.count_and_get
357 (p_count => x_msg_count ,
358 p_data => x_msg_data ,
359 p_encoded => FND_API.G_FALSE );
360 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
361 ROLLBACK TO convert_clob_to_xmltype;
362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
363 FND_MSG_PUB.count_and_get
364 (p_count => x_msg_count ,
365 p_data => x_msg_data ,
366 p_encoded => FND_API.G_FALSE );
367 WHEN OTHERS THEN
368 ROLLBACK TO convert_clob_to_xmltype;
369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
370 IF FND_MSG_PUB.check_msg_level
371 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
372 THEN
373 FND_MSG_PUB.add_exc_msg
374 (G_PKG_NAME ,
375 l_api_name );
376 END IF;
377 FND_MSG_PUB.count_and_get
378 (p_count => x_msg_count ,
379 p_data => x_msg_data ,
380 p_encoded => FND_API.G_FALSE );
381 END convert_clob_to_xmltype;
382
383
384 PROCEDURE convert_blob_to_xmltype
385 ( p_api_version IN NUMBER ,
386 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
387 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
388 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
389 p_exp_imp_id IN CN_COPY_REQUESTS_ALL.EXP_IMP_REQUEST_ID%TYPE,
390 x_return_status OUT NOCOPY VARCHAR2,
391 x_msg_count OUT NOCOPY NUMBER,
392 x_msg_data OUT NOCOPY VARCHAR2
393 )
394 IS
395 l_api_name CONSTANT VARCHAR2(30) := 'convert_blob_to_xmltype';
396 l_api_version CONSTANT NUMBER := 1.0;
397 l_return_status VARCHAR2(2000);
398 l_msg_count NUMBER;
399 l_msg_data VARCHAR2(2000);
400
401 BEGIN
402
403 -- Standard Start of API savepoint
404 SAVEPOINT convert_blob_to_xmltype;
405
406 -- Standard call to check for call compatibility.
407 IF NOT FND_API.Compatible_API_Call
408 (l_api_version ,
409 p_api_version ,
410 l_api_name ,
411 G_PKG_NAME )
412 THEN
413 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
414 END IF;
415 -- Initialize message list if p_init_msg_list is set to TRUE.
416 IF FND_API.to_Boolean( p_init_msg_list ) THEN
417 FND_MSG_PUB.initialize;
418 END IF;
419
420
421 -- Initialize API return status to success
422 x_return_status := FND_API.G_RET_STS_SUCCESS;
423
424 -- ******* API Begins ************
425 -- TODO : Error handling, calling the other two procedures
426 convert_blob_to_clob(p_api_version,
427 p_init_msg_list,
428 p_commit,
429 p_validation_level,
430 p_exp_imp_id,
431 l_return_status,
432 l_msg_count,
433 l_msg_data);
434
435 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
436 RAISE fnd_api.g_exc_error;
437 END IF;
438
439 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
440 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
441 END IF;
442
443
444 convert_clob_to_xmltype(p_api_version,
445 p_init_msg_list,
446 p_commit,
447 p_validation_level,
448 p_exp_imp_id,
449 l_return_status,
450 l_msg_count,
451 l_msg_data);
452
453 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
454 RAISE fnd_api.g_exc_error;
455 END IF;
456
457 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
458 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
459 END IF;
460
461 -- ******* End of API body. ********
462 -- Standard check of p_commit.
463 IF FND_API.To_Boolean(p_commit) THEN
464 COMMIT WORK;
465 END IF;
466
467 -- Standard call to get message count and if count is 1, get message info.
468 FND_MSG_PUB.Count_And_Get
469 (p_count => x_msg_count ,
470 p_data => x_msg_data ,
471 p_encoded => FND_API.G_FALSE );
472
473 EXCEPTION
474 WHEN FND_API.G_EXC_ERROR THEN
475 ROLLBACK TO convert_blob_to_xmltype;
476 x_return_status := FND_API.G_RET_STS_ERROR ;
477 FND_MSG_PUB.count_and_get
478 (p_count => x_msg_count ,
479 p_data => x_msg_data ,
480 p_encoded => FND_API.G_FALSE );
481 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
482 ROLLBACK TO convert_blob_to_xmltype;
483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
484 FND_MSG_PUB.count_and_get
485 (p_count => x_msg_count ,
486 p_data => x_msg_data ,
487 p_encoded => FND_API.G_FALSE );
488 WHEN OTHERS THEN
489 ROLLBACK TO convert_blob_to_xmltype;
490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
491 IF FND_MSG_PUB.check_msg_level
492 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
493 THEN
494 FND_MSG_PUB.add_exc_msg
495 (G_PKG_NAME ,
496 l_api_name );
497 END IF;
498 FND_MSG_PUB.count_and_get
499 (p_count => x_msg_count ,
500 p_data => x_msg_data ,
501 p_encoded => FND_API.G_FALSE );
502 END convert_blob_to_xmltype;
503
504
505 FUNCTION check_name_length (p_name VARCHAR2, p_org_id NUMBER,p_type varchar2,p_prefix varchar2)
506 RETURN VARCHAR2 IS
507 l_return_str varchar2(80) := null;
508 l_temp_str varchar2(100);
509 l_prefix_str varchar2(10);
510
511 BEGIN
512 l_prefix_str := p_prefix;
513 IF (p_name is null or p_name = '') THEN
514 l_return_str := NULL;
515 return l_return_str;
516 END IF;
517
521 END IF;
518 IF (p_org_id is null) THEN
519 l_return_str := NULL;
520 return l_return_str;
522
523 IF (p_type <> 'PLAN' and
524 p_type <> 'PLANELEMENT' and
525 p_type <> 'EXPRESSION' and
526 p_type <> 'RATETABLE' and
527 p_type <> 'RATEDIMENSION' and
528 p_type <> 'FORMULA') THEN
529 l_return_str := NULL;
530 return l_return_str;
531 END IF;
532
533 IF (p_prefix is null) THEN
534 l_return_str := NULL;
535 l_prefix_str := '';
536 END IF;
537
538 l_temp_str := CONCAT(p_prefix,p_name);
539
540
541 if (p_type = 'PLAN') THEN
542 SELECT SUBSTRB(l_temp_str,1,30) into l_return_str FROM DUAL;
543 ELSIF (p_type = 'PLANELEMENT') THEN
544 SELECT SUBSTRB(l_temp_str,1,80) into l_return_str FROM DUAL;
545 ELSIF (p_type = 'EXPRESSION') THEN
546 SELECT SUBSTRB(l_temp_str,1,30) into l_return_str FROM DUAL;
547 ELSIF (p_type = 'RATETABLE') THEN
548 SELECT SUBSTRB(l_temp_str,1,80) into l_return_str FROM DUAL;
549 ELSIF (p_type = 'RATEDIMENSION') THEN
550 SELECT SUBSTRB(l_temp_str,1,30) into l_return_str FROM DUAL;
551 ELSIF (p_type = 'FORMULA') THEN
552 SELECT SUBSTRB(l_temp_str,1,30) into l_return_str FROM DUAL;
553 END IF;
554
555 return l_return_str;
556 EXCEPTION
557 WHEN OTHERS THEN
558 RETURN NULL;
559 END check_name_length;
560 --Added by Naren to fix BT issue on 10thMARCH,2010
561
562 PROCEDURE update_existing_expression(
563 p_api_version IN NUMBER
564 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
565 , p_commit IN VARCHAR2 := fnd_api.g_false
566 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
567 , p_update_parent_also IN VARCHAR2 := fnd_api.g_false
568 , p_org_id IN cn_calc_sql_exps.org_id%TYPE
569 , p_calc_sql_exp_id IN cn_calc_sql_exps.calc_sql_exp_id%TYPE
570 , p_name IN cn_calc_sql_exps.NAME%TYPE
571 , p_description IN cn_calc_sql_exps.description%TYPE := NULL
572 , p_expression_disp IN VARCHAR2 := NULL
573 , -- CLOBs
574 p_sql_select IN VARCHAR2 := NULL
575 , p_sql_from IN VARCHAR2 := NULL
576 , p_piped_expression_disp IN VARCHAR2 := NULL
577 , p_piped_sql_select IN VARCHAR2 := NULL
578 , p_piped_sql_from IN VARCHAR2 := NULL
579 , p_ovn IN OUT NOCOPY cn_calc_sql_exps.object_version_number%TYPE
580 , p_exp_type_code IN cn_calc_sql_exps.exp_type_code%TYPE
581 , p_status IN cn_calc_sql_exps.status%TYPE
582 , x_return_status OUT NOCOPY VARCHAR2
583 , x_msg_count OUT NOCOPY NUMBER
584 , x_msg_data OUT NOCOPY VARCHAR2
585 ) IS
586 l_api_name CONSTANT VARCHAR2(30) := 'Update_Existing_Expression';
587 l_api_version CONSTANT NUMBER := 1.0;
588 l_prompt cn_lookups.meaning%TYPE;
589 l_dummy PLS_INTEGER;
590 l_disp_start PLS_INTEGER;
591 l_select_start PLS_INTEGER;
592 l_disp_end PLS_INTEGER;
593 l_select_end PLS_INTEGER;
594 l_token VARCHAR2(4000);
595 l_calc_formula_id cn_calc_formulas.calc_formula_id%TYPE;
596 l_exp_names VARCHAR2(4000) := '|';
597 l_formula_ids VARCHAR2(4000) := '|';
598
599
600 CURSOR exp_exists IS
601 SELECT 1
602 FROM cn_calc_sql_exps
603 WHERE NAME = p_name AND org_id = p_org_id AND calc_sql_exp_id <> p_calc_sql_exp_id;
604 BEGIN
605 -- Standard Start of API savepoint
606 SAVEPOINT update_expression;
607
608 -- Standard call to check for call compatibility.
609 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
610 RAISE fnd_api.g_exc_unexpected_error;
611 END IF;
612
613 -- Initialize message list if p_init_msg_list is set to TRUE.
614 IF fnd_api.to_boolean(p_init_msg_list) THEN
615 fnd_msg_pub.initialize;
616 END IF;
617
618 -- Initialize API return status to success
619 x_return_status := fnd_api.g_ret_sts_success;
620
621 -- API body
622 IF (p_name IS NULL) THEN
623 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
624 l_prompt := cn_api.get_lkup_meaning('EXP_NAME', 'CN_PROMPTS');
625 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
626 fnd_message.set_token('OBJ_NAME', l_prompt);
627 fnd_msg_pub.ADD;
628 END IF;
629
630 RAISE fnd_api.g_exc_error;
631 END IF;
632
633 OPEN exp_exists;
634 FETCH exp_exists INTO l_dummy;
635 CLOSE exp_exists;
636
637 IF (l_dummy = 1) THEN
638 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
639 fnd_message.set_name('CN', 'CN_NAME_NOT_UNIQUE');
640 fnd_msg_pub.ADD;
641 END IF;
642
643 RAISE fnd_api.g_exc_error;
644 END IF;
645
646 -- check ovn
647 cn_calc_sql_exps_pkg.lock_row(p_calc_sql_exp_id, p_ovn);
648 -- do update
649
650 fnd_file.put_line(fnd_file.Log, ' update_existing_expression: x_exp_type_code ' || p_exp_type_code || ' x_status :' || p_status);
651
652 cn_calc_sql_exps_pkg.update_row(
653 x_org_id => p_org_id
654 , x_calc_sql_exp_id => p_calc_sql_exp_id
655 , x_name => p_name
656 , x_description => p_description
657 , x_status => p_status
658 , x_exp_type_code => p_exp_type_code
659 , x_expression_disp => p_expression_disp
660 , x_sql_select => p_sql_select
661 , x_sql_from => p_sql_from
662 , x_piped_sql_select => p_piped_sql_select
663 , x_piped_sql_from => p_piped_sql_from
664 , x_piped_expression_disp => p_piped_expression_disp
665 , x_object_version_number => p_ovn
666 );
667
668
669 -- insert new calc edges
670 l_disp_start := 1;
671 l_select_start := 1;
672
673 LOOP
674 l_disp_end := INSTR(p_piped_expression_disp, '|', l_disp_start, 1);
675
676 IF (l_disp_end IS NULL OR l_disp_end = 0) THEN
677 EXIT;
678 END IF;
679
680 l_token := SUBSTR(p_piped_expression_disp, l_disp_start, l_disp_end - l_disp_start);
681 l_disp_start := l_disp_end + 1;
682 l_select_end := INSTR(p_piped_sql_select, '|', l_select_start, 1);
683
684 -- if the corresponding piped select part is in parenthesis, it is an embedded expression
685 IF (
686 INSTR(p_piped_sql_select, '(', l_select_start, 1) = l_select_start
687 AND (l_select_end - l_select_start) > 1
688 ) THEN
689 l_exp_names := l_exp_names || l_token || '|';
690
691 INSERT INTO cn_calc_edges
692 (
693 org_id
694 , calc_edge_id
695 , parent_id
696 , child_id
697 , edge_type
698 , creation_date
699 , created_by
700 , last_update_login
701 , last_update_date
702 , last_updated_by
703 )
704 SELECT org_id
705 , cn_calc_edges_s.NEXTVAL
706 , p_calc_sql_exp_id
707 , calc_sql_exp_id
708 , 'EE'
709 , SYSDATE
710 , fnd_global.user_id
711 , fnd_global.login_id
712 , SYSDATE
713 , fnd_global.user_id
714 FROM cn_calc_sql_exps
715 WHERE NAME = l_token
716 AND org_id= p_org_id
717 AND NOT EXISTS(
718 SELECT 1
719 FROM cn_calc_edges
720 WHERE parent_id = p_calc_sql_exp_id
721 AND child_id = (SELECT calc_sql_exp_id
722 FROM cn_calc_sql_exps
723 WHERE NAME = l_token AND org_id = p_org_id AND edge_type = 'EE'));
724 ELSIF(INSTR(p_piped_sql_select, 'cn_formula', l_select_start, 1) = l_select_start) THEN
725 l_dummy := INSTR(p_piped_sql_select, '_', l_select_start, 2) + 1;
726 l_calc_formula_id :=
727 TO_NUMBER(
728 SUBSTR(p_piped_sql_select, l_dummy, INSTR(p_piped_sql_select, '_', l_dummy, 1) - l_dummy)
729 );
730 l_formula_ids := l_formula_ids || l_calc_formula_id || '|';
731
732 INSERT INTO cn_calc_edges
733 (
734 org_id
735 , calc_edge_id
736 , parent_id
737 , child_id
738 , edge_type
739 , creation_date
740 , created_by
741 , last_update_login
742 , last_update_date
743 , last_updated_by
744 )
745 SELECT p_org_id
746 , cn_calc_edges_s.NEXTVAL
747 , p_calc_sql_exp_id
748 , l_calc_formula_id
749 , 'FE'
750 , SYSDATE
751 , fnd_global.user_id
752 , fnd_global.login_id
753 , SYSDATE
754 , fnd_global.user_id
755 FROM DUAL
756 WHERE NOT EXISTS(
757 SELECT 1
758 FROM cn_calc_edges
759 WHERE parent_id = p_calc_sql_exp_id
760 AND child_id = l_calc_formula_id
761 AND edge_type = 'FE');
762 END IF;
763
764 l_select_start := l_select_end + 1;
765 END LOOP;
766
767 -- delete obsolete calc edges
768 --IF (l_formula_ids <> '|') THEN
769 DELETE FROM cn_calc_edges
770 WHERE parent_id = p_calc_sql_exp_id
771 AND INSTR(l_formula_ids, '|' || child_id || '|', 1, 1) = 0
772 AND edge_type = 'FE';
773
774 --END IF;
775
776 --IF (l_exp_names <> '|') THEN
777 DELETE FROM cn_calc_edges a
778 WHERE a.parent_id = p_calc_sql_exp_id
779 AND a.edge_type = 'EE'
780 AND NOT EXISTS(
781 SELECT 1
782 FROM cn_calc_sql_exps b
783 WHERE a.child_id = b.calc_sql_exp_id
784 AND INSTR(l_exp_names, '|' || b.NAME || '|', 1, 1) > 0);
785
786 --END IF;
787
788 -- update parent expressions and formulas also
789 IF (fnd_api.to_boolean(p_update_parent_also)) THEN
790 NULL;
791 END IF;
792
793 -- End of API body.
794
795 -- Standard check of p_commit.
796 IF fnd_api.to_boolean(p_commit) THEN
797 COMMIT WORK;
798 END IF;
799
800 -- Standard call to get message count and if count is 1, get message info.
801 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
802 , p_encoded => fnd_api.g_false);
803 EXCEPTION
804 WHEN fnd_api.g_exc_error THEN
805 ROLLBACK TO update_expression;
806 x_return_status := fnd_api.g_ret_sts_error;
807 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
808 , p_encoded => fnd_api.g_false);
809 WHEN fnd_api.g_exc_unexpected_error THEN
810 ROLLBACK TO update_expression;
811 x_return_status := fnd_api.g_ret_sts_unexp_error;
812 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
813 , p_encoded => fnd_api.g_false);
814 WHEN OTHERS THEN
815 ROLLBACK TO update_expression;
816 x_return_status := fnd_api.g_ret_sts_unexp_error;
817
818 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
819 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
820 END IF;
821
822 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
823 , p_encoded => fnd_api.g_false);
824 END update_existing_expression;
825
826 --addition ends by Naren
827
828
829 END CN_PLANCOPY_UTIL_PVT;