[Home] [Help]
PACKAGE BODY: APPS.CN_PLANCOPY_UTIL_PVT
Source
1 PACKAGE BODY CN_PLANCOPY_UTIL_PVT AS
2 /*$Header: cnpcutlb.pls 120.8 2007/10/05 17:40:07 sbadami noship $*/
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;
27 RAND_NUM_LENGTH number := 3;
28 l_length_expscn number;
29
30 BEGIN
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 BEGIN
128 DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
129
130 FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
131 LOOP
132 v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
133 DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
134 v_start := v_start + v_buffer;
135 END LOOP;
136
137 RETURN v_clob;
138 END blob_to_clob;
139
140
141 FUNCTION clob_to_xmltype (clob_in IN CLOB)
142 RETURN XMLTYPE
143 AS
144 v_xmltype XMLTYPE;
145 BEGIN
146 v_xmltype := XMLTYPE(clob_in);
147 RETURN v_xmltype;
148 END clob_to_xmltype;
149
150 PROCEDURE convert_blob_to_clob
151 ( p_api_version IN NUMBER ,
152 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
153 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
154 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
155 p_exp_imp_id IN CN_COPY_REQUESTS_ALL.EXP_IMP_REQUEST_ID%TYPE,
156 x_return_status OUT NOCOPY VARCHAR2,
157 x_msg_count OUT NOCOPY NUMBER,
158 x_msg_data OUT NOCOPY VARCHAR2
159 ) IS
160 l_api_name CONSTANT VARCHAR2(30) := 'convert_blob_to_clob';
161 l_blob blob;
162 l_clob clob;
163 l_api_version CONSTANT NUMBER := 1.0;
164
165
166 BEGIN
167
168 -- Standard Start of API savepoint
169 SAVEPOINT convert_blob_to_clob;
170
171 -- Standard call to check for call compatibility.
172 IF NOT FND_API.Compatible_API_Call
173 (l_api_version ,
174 p_api_version ,
175 l_api_name ,
176 G_PKG_NAME )
177 THEN
178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
179 END IF;
180 -- Initialize message list if p_init_msg_list is set to TRUE.
181 IF FND_API.to_Boolean( p_init_msg_list ) THEN
182 FND_MSG_PUB.initialize;
183 END IF;
184
185 -- Initialize API return status to success
186 x_return_status := FND_API.G_RET_STS_SUCCESS;
187
188 -- ******* API Begins ************
189 -- TODO : Error handling
190
191 select file_content_blob into l_blob from cn_copy_requests_all
192 where EXP_IMP_REQUEST_ID = p_exp_imp_id;
193
194
195 l_clob := blob_to_clob(l_blob);
196
197 update cn_copy_requests_all set file_content_clob = l_clob where EXP_IMP_REQUEST_ID = p_exp_imp_id;
198
199 -- ******* End of API body. ********
200 -- Standard check of p_commit.
201 IF FND_API.To_Boolean( p_commit ) THEN
202 COMMIT WORK;
203 END IF;
204
205 -- Standard call to get message count and if count is 1, get message info.
206 FND_MSG_PUB.Count_And_Get
207 (p_count => x_msg_count ,
208 p_data => x_msg_data ,
209 p_encoded => FND_API.G_FALSE );
210
211 EXCEPTION
212 WHEN FND_API.G_EXC_ERROR THEN
213 ROLLBACK TO convert_blob_to_clob;
214 x_return_status := FND_API.G_RET_STS_ERROR ;
215 FND_MSG_PUB.count_and_get
216 (p_count => x_msg_count ,
217 p_data => x_msg_data ,
218 p_encoded => FND_API.G_FALSE );
219 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
220 ROLLBACK TO convert_blob_to_clob;
221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
222 FND_MSG_PUB.count_and_get
223 (p_count => x_msg_count ,
224 p_data => x_msg_data ,
225 p_encoded => FND_API.G_FALSE );
226 WHEN OTHERS THEN
227 ROLLBACK TO convert_blob_to_clob;
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
229 IF FND_MSG_PUB.check_msg_level
230 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
231 THEN
232 FND_MSG_PUB.add_exc_msg
233 (G_PKG_NAME ,
234 l_api_name );
235 END IF;
236 FND_MSG_PUB.count_and_get
237 (p_count => x_msg_count ,
238 p_data => x_msg_data ,
239 p_encoded => FND_API.G_FALSE );
240 END convert_blob_to_clob;
241
242
243
244 PROCEDURE convert_clob_to_xmltype
245 ( p_api_version IN NUMBER ,
246 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
247 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
248 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
249 p_exp_imp_id IN CN_COPY_REQUESTS_ALL.EXP_IMP_REQUEST_ID%TYPE,
250 x_return_status OUT NOCOPY VARCHAR2,
251 x_msg_count OUT NOCOPY NUMBER,
252 x_msg_data OUT NOCOPY VARCHAR2
253 )
254 IS
255 l_api_name CONSTANT VARCHAR2(30) := 'convert_clob_to_xmltype';
256 l_api_version CONSTANT NUMBER := 1.0;
257
258 l_xmltype xmltype;
259 l_clob clob;
260 BEGIN
261
262 -- Standard Start of API savepoint
263 SAVEPOINT convert_clob_to_xmltype;
264
265 -- Standard call to check for call compatibility.
266 IF NOT FND_API.Compatible_API_Call
267 (l_api_version ,
268 p_api_version ,
269 l_api_name ,
270 G_PKG_NAME )
271 THEN
272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273 END IF;
274 -- Initialize message list if p_init_msg_list is set to TRUE.
275 IF FND_API.to_Boolean( p_init_msg_list ) THEN
276 FND_MSG_PUB.initialize;
277 END IF;
278
279 -- Initialize API return status to success
280 x_return_status := FND_API.G_RET_STS_SUCCESS;
281
282 -- ******* API Begins ************
283 -- TODO : Error handling, waiting for xmltype to be added
284
285 select file_content_clob into l_clob from cn_copy_requests_all
286 where EXP_IMP_REQUEST_ID = p_exp_imp_id;
287
288 l_xmltype := XMLTYPE(l_clob);
289
290 update cn_copy_requests_all set file_content_xmltype = l_xmltype where EXP_IMP_REQUEST_ID = p_exp_imp_id;
291
292 -- ******* End of API body. ********
293 -- Standard check of p_commit.
294 IF FND_API.To_Boolean( p_commit ) THEN
295 COMMIT WORK;
296 END IF;
297
298 -- Standard call to get message count and if count is 1, get message info.
299 FND_MSG_PUB.Count_And_Get
300 (p_count => x_msg_count ,
301 p_data => x_msg_data ,
302 p_encoded => FND_API.G_FALSE );
303
304 EXCEPTION
305 WHEN FND_API.G_EXC_ERROR THEN
306 ROLLBACK TO convert_clob_to_xmltype;
307 x_return_status := FND_API.G_RET_STS_ERROR ;
308 FND_MSG_PUB.count_and_get
309 (p_count => x_msg_count ,
310 p_data => x_msg_data ,
311 p_encoded => FND_API.G_FALSE );
312 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
313 ROLLBACK TO convert_clob_to_xmltype;
314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
315 FND_MSG_PUB.count_and_get
316 (p_count => x_msg_count ,
317 p_data => x_msg_data ,
318 p_encoded => FND_API.G_FALSE );
319 WHEN OTHERS THEN
320 ROLLBACK TO convert_clob_to_xmltype;
321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
325 FND_MSG_PUB.add_exc_msg
322 IF FND_MSG_PUB.check_msg_level
323 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
324 THEN
326 (G_PKG_NAME ,
327 l_api_name );
328 END IF;
329 FND_MSG_PUB.count_and_get
330 (p_count => x_msg_count ,
331 p_data => x_msg_data ,
332 p_encoded => FND_API.G_FALSE );
333 END convert_clob_to_xmltype;
334
335
336 PROCEDURE convert_blob_to_xmltype
337 ( p_api_version IN NUMBER ,
338 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
339 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
340 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
341 p_exp_imp_id IN CN_COPY_REQUESTS_ALL.EXP_IMP_REQUEST_ID%TYPE,
342 x_return_status OUT NOCOPY VARCHAR2,
343 x_msg_count OUT NOCOPY NUMBER,
344 x_msg_data OUT NOCOPY VARCHAR2
345 )
346 IS
347 l_api_name CONSTANT VARCHAR2(30) := 'convert_blob_to_xmltype';
348 l_api_version CONSTANT NUMBER := 1.0;
349 l_return_status VARCHAR2(2000);
350 l_msg_count NUMBER;
351 l_msg_data VARCHAR2(2000);
352
353 BEGIN
354
355 -- Standard Start of API savepoint
356 SAVEPOINT convert_blob_to_xmltype;
357
358 -- Standard call to check for call compatibility.
359 IF NOT FND_API.Compatible_API_Call
360 (l_api_version ,
361 p_api_version ,
362 l_api_name ,
363 G_PKG_NAME )
364 THEN
365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
366 END IF;
367 -- Initialize message list if p_init_msg_list is set to TRUE.
368 IF FND_API.to_Boolean( p_init_msg_list ) THEN
369 FND_MSG_PUB.initialize;
370 END IF;
371
372
373 -- Initialize API return status to success
374 x_return_status := FND_API.G_RET_STS_SUCCESS;
375
376 -- ******* API Begins ************
377 -- TODO : Error handling, calling the other two procedures
378 convert_blob_to_clob(p_api_version,
379 p_init_msg_list,
380 p_commit,
381 p_validation_level,
382 p_exp_imp_id,
383 l_return_status,
384 l_msg_count,
385 l_msg_data);
386
387 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
388 RAISE fnd_api.g_exc_error;
389 END IF;
390
391 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
392 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
393 END IF;
394
395
396 convert_clob_to_xmltype(p_api_version,
397 p_init_msg_list,
398 p_commit,
399 p_validation_level,
400 p_exp_imp_id,
401 l_return_status,
402 l_msg_count,
403 l_msg_data);
404
405 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
406 RAISE fnd_api.g_exc_error;
407 END IF;
408
409 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
410 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
411 END IF;
412
413 -- ******* End of API body. ********
414 -- Standard check of p_commit.
415 IF FND_API.To_Boolean(p_commit) THEN
416 COMMIT WORK;
417 END IF;
418
419 -- Standard call to get message count and if count is 1, get message info.
420 FND_MSG_PUB.Count_And_Get
421 (p_count => x_msg_count ,
422 p_data => x_msg_data ,
423 p_encoded => FND_API.G_FALSE );
424
425 EXCEPTION
426 WHEN FND_API.G_EXC_ERROR THEN
427 ROLLBACK TO convert_blob_to_xmltype;
428 x_return_status := FND_API.G_RET_STS_ERROR ;
429 FND_MSG_PUB.count_and_get
430 (p_count => x_msg_count ,
431 p_data => x_msg_data ,
432 p_encoded => FND_API.G_FALSE );
433 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
434 ROLLBACK TO convert_blob_to_xmltype;
435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
436 FND_MSG_PUB.count_and_get
437 (p_count => x_msg_count ,
438 p_data => x_msg_data ,
439 p_encoded => FND_API.G_FALSE );
440 WHEN OTHERS THEN
441 ROLLBACK TO convert_blob_to_xmltype;
442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
443 IF FND_MSG_PUB.check_msg_level
444 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
445 THEN
446 FND_MSG_PUB.add_exc_msg
447 (G_PKG_NAME ,
448 l_api_name );
449 END IF;
450 FND_MSG_PUB.count_and_get
451 (p_count => x_msg_count ,
452 p_data => x_msg_data ,
453 p_encoded => FND_API.G_FALSE );
454 END convert_blob_to_xmltype;
455
456
457 FUNCTION check_name_length (p_name VARCHAR2, p_org_id NUMBER,p_type varchar2,p_prefix varchar2)
458 RETURN VARCHAR2 IS
459 l_return_str varchar2(80) := null;
460 l_temp_str varchar2(100);
461 l_prefix_str varchar2(10);
462
463 BEGIN
464 l_prefix_str := p_prefix;
465 IF (p_name is null or p_name = '') THEN
466 l_return_str := NULL;
467 return l_return_str;
468 END IF;
469
470 IF (p_org_id is null) THEN
471 l_return_str := NULL;
472 return l_return_str;
473 END IF;
474
475 IF (p_type <> 'PLAN' and
476 p_type <> 'PLANELEMENT' and
477 p_type <> 'EXPRESSION' and
478 p_type <> 'RATETABLE' and
479 p_type <> 'RATEDIMENSION' and
480 p_type <> 'FORMULA') THEN
481 l_return_str := NULL;
482 return l_return_str;
483 END IF;
484
485 IF (p_prefix is null) THEN
486 l_return_str := NULL;
487 l_prefix_str := '';
488 END IF;
489
490 l_temp_str := CONCAT(p_prefix,p_name);
491
492
493 if (p_type = 'PLAN') THEN
494 SELECT SUBSTRB(l_temp_str,1,30) into l_return_str FROM DUAL;
495 ELSIF (p_type = 'PLANELEMENT') THEN
496 SELECT SUBSTRB(l_temp_str,1,80) into l_return_str FROM DUAL;
497 ELSIF (p_type = 'EXPRESSION') THEN
498 SELECT SUBSTRB(l_temp_str,1,30) into l_return_str FROM DUAL;
499 ELSIF (p_type = 'RATETABLE') THEN
500 SELECT SUBSTRB(l_temp_str,1,80) into l_return_str FROM DUAL;
501 ELSIF (p_type = 'RATEDIMENSION') THEN
502 SELECT SUBSTRB(l_temp_str,1,30) into l_return_str FROM DUAL;
503 ELSIF (p_type = 'FORMULA') THEN
504 SELECT SUBSTRB(l_temp_str,1,30) into l_return_str FROM DUAL;
505 END IF;
506
507 return l_return_str;
508 EXCEPTION
509 WHEN OTHERS THEN
510 RETURN NULL;
511 END check_name_length;
512
513
514 END CN_PLANCOPY_UTIL_PVT;