[Home] [Help]
PACKAGE BODY: APPS.CN_CALC_SQL_EXPS_PVT
Source
4
1 PACKAGE BODY cn_calc_sql_exps_pvt AS
2 /*$Header: cnvcexpb.pls 120.10.12010000.4 2010/03/15 18:36:42 rnagired ship $*/
3 g_pkg_name CONSTANT VARCHAR2(30) := 'CN_CALC_SQL_EXPS_PVT';
5 g_original_node_type VARCHAR2(2000);
6 g_original_node_id NUMBER;
7 g_node_type VARCHAR2(2000);
8 g_current_id NUMBER;
9 g_level NUMBER;
10 g_pe_arr num_tbl_type;
11
12 PROCEDURE get_usage_info(
13 p_exp_type_code IN cn_calc_sql_exps.exp_type_code%TYPE
14 , x_usage_info OUT NOCOPY VARCHAR2
15 ) IS
16 BEGIN
17 x_usage_info := fnd_message.get_string('CN', p_exp_type_code);
18 EXCEPTION
19 WHEN OTHERS THEN
20 x_usage_info := NULL;
21 END get_usage_info;
22
23 PROCEDURE classify_expression(
24 p_org_id IN cn_calc_sql_exps.org_id%TYPE
25 , p_sql_select IN VARCHAR2
29 , x_status IN OUT NOCOPY cn_calc_sql_exps.status%TYPE
26 , p_sql_from IN VARCHAR2
27 , p_piped_sql_select IN VARCHAR2
28 , p_piped_sql_from IN VARCHAR2
30 , x_exp_type_code IN OUT NOCOPY cn_calc_sql_exps.exp_type_code%TYPE
31 , x_msg_count OUT NOCOPY NUMBER
32 , x_msg_data OUT NOCOPY VARCHAR2
33 ) IS
34 l_dummy PLS_INTEGER;
35 l_pos PLS_INTEGER;
36 l_alias VARCHAR2(30);
37 l_new_sql VARCHAR2(4100);
38 l_pe_tbl num_tbl_type;
39
40 CURSOR external_table IS
41 SELECT 1
42 FROM cn_calc_ext_tables
43 WHERE alias = l_alias
44 AND (org_id = p_org_id)
45 AND internal_table_id IN(
46 SELECT object_id
47 FROM cn_objects
48 WHERE (NAME = 'CN_COMMISSION_LINES' OR NAME = 'CN_COMMISSION_HEADERS')
49 AND object_type = 'TBL'
50 AND (org_id = p_org_id));
51 BEGIN
52 -- parse the expression
53 IF LENGTH(p_sql_select) + LENGTH(p_sql_from) > 4000 THEN
54 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
55 fnd_message.set_name('CN', 'CN_EXP_TOO_LONG');
56 fnd_msg_pub.ADD;
57 END IF;
58
59 RAISE fnd_api.g_exc_error;
60 END IF;
61
62 DECLARE
63 l_sql_statement VARCHAR2(4100);
64
65 TYPE rc IS REF CURSOR;
66
67 dummy_cur rc;
68 dummy_val VARCHAR2(4000);
69 BEGIN
70 x_status := 'VALID';
71 l_sql_statement :=
72 'select ' || p_sql_select || ' from ' || p_sql_from || ' where rownum < 1';
73 l_sql_statement :=
74 REPLACE(
75 REPLACE(REPLACE(l_sql_statement, 'p_commission_line_id', '100'), 'RateResult', '100')
76 , 'ForecastAmount'
77 , '100'
78 );
79 -- if we see anything like [PlanElementID]PE.[something], replace it
80 -- with a constant 0
81 parse_plan_elements(l_sql_statement, l_pe_tbl, l_new_sql);
82 l_sql_statement := l_new_sql;
83
84 OPEN dummy_cur FOR l_sql_statement;
85 FETCH dummy_cur INTO dummy_val;
86 CLOSE dummy_cur;
87 EXCEPTION
88 WHEN OTHERS THEN
89 x_status := 'INVALID';
90
91 IF dummy_cur%ISOPEN THEN
92 CLOSE dummy_cur;
93 END IF;
94
95 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
96 fnd_message.set_name('CN', 'CN_INVALID_EXP');
97 fnd_message.set_token('EXPRESSION', SQLERRM);
98 fnd_msg_pub.ADD;
99 END IF;
100
101 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
102 , p_encoded => fnd_api.g_false);
103 END;
104
105 IF (p_piped_sql_select IS NULL OR p_piped_sql_from IS NULL) THEN
106 RETURN;
107 END IF;
108
109 -- check whether there is a column from cn_commission_lines/headers
110 -- or if there is a plan element
111 IF (
112 INSTR(p_piped_sql_select, 'CL.', 1, 1) = 1
113 OR INSTR(p_piped_sql_select, '|CL.', 1, 1) > 0
114 OR INSTR(p_piped_sql_select, '(CL.', 1, 1) > 0
115 OR INSTR(p_piped_sql_select, '+CL.', 1, 1) > 0
116 OR INSTR(p_piped_sql_select, '-CL.', 1, 1) > 0
117 OR INSTR(p_piped_sql_select, '*CL.', 1, 1) > 0
118 OR INSTR(p_piped_sql_select, '/CL.', 1, 1) > 0
119 OR INSTR(p_piped_sql_select, 'CH.', 1, 1) = 1
120 OR INSTR(p_piped_sql_select, '|CH.', 1, 1) > 0
121 OR INSTR(p_piped_sql_select, '(CH.', 1, 1) > 0
122 OR INSTR(p_piped_sql_select, '+CH.', 1, 1) > 0
123 OR INSTR(p_piped_sql_select, '-CH.', 1, 1) > 0
124 OR INSTR(p_piped_sql_select, '*CH.', 1, 1) > 0
125 OR INSTR(p_piped_sql_select, '/CH.', 1, 1) > 0
126 OR INSTR(p_piped_sql_select, 'p_commission_line_id', 1, 1) > 0
127 ) THEN
128 x_exp_type_code := 'Y';
129 ELSE
130 x_exp_type_code := 'N';
131 END IF;
132
133 -- check whether there is any column from a table which is mapped to cn_commission_lines/headers.
134 -- if there is any such column, the expression is considered trx_based.
135 IF (INSTR(x_exp_type_code, 'N', 1, 1) = 1) THEN
136 l_pos := 1;
137
138 LOOP
139 l_pos := INSTR(p_piped_sql_from, ' ', l_pos, 1);
140
141 IF (l_pos = 0) THEN
142 EXIT;
143 END IF;
144
145 l_pos := l_pos + 1;
146 l_alias := SUBSTR(p_piped_sql_from, l_pos, INSTR(p_piped_sql_from, '|', l_pos, 1) - l_pos);
147
148 OPEN external_table;
149 FETCH external_table INTO l_dummy;
150 CLOSE external_table;
151
152 IF (l_dummy = 1) THEN
153 x_exp_type_code := 'Y';
154 EXIT;
155 END IF;
156 END LOOP;
157 END IF;
158
159 -- check whether there is group function in the sql statement
160 IF (
161 INSTR(p_piped_sql_select, 'AVG(', 1, 1) > 0
162 OR INSTR(p_piped_sql_select, 'COUNT(', 1, 1) > 0
163 OR INSTR(p_piped_sql_select, 'MIN(', 1, 1) > 0
164 OR INSTR(p_piped_sql_select, 'MAX(', 1, 1) > 0
165 OR INSTR(p_piped_sql_select, 'STDDEV(', 1, 1) > 0
166 OR INSTR(p_piped_sql_select, 'SUM(', 1, 1) > 0
167 OR INSTR(p_piped_sql_select, 'VARIANCE(', 1, 1) > 0
168 ) THEN
172 END IF;
169 x_exp_type_code := x_exp_type_code || 'Y';
170 ELSE
171 x_exp_type_code := x_exp_type_code || 'N';
173
174 -- check whether RateResult is used
175 l_pos := INSTR(p_piped_sql_select, 'RateResult', 1, 1);
176
177 IF (l_pos > 0) THEN
178 x_exp_type_code := x_exp_type_code || 'Y';
179 ELSE
180 x_exp_type_code := x_exp_type_code || 'N';
181 END IF;
182
183 -- check whether RateResult is the first component and is used only once
184 IF (l_pos = 1 AND INSTR(p_piped_sql_select, 'RateResult', 11, 1) = 0) THEN
185 x_exp_type_code := x_exp_type_code || 'Y';
186 ELSE
187 -- deal with unnecessary leading and ending parentheses
188 x_exp_type_code := x_exp_type_code || 'N';
189 END IF;
190
191 -- check whether there is embedded formula
192 IF (INSTR(p_piped_sql_select, 'p_commission_line_id', 1, 1) > 0) THEN
193 x_exp_type_code := x_exp_type_code || 'Y';
194 ELSE
195 x_exp_type_code := x_exp_type_code || 'N';
196 END IF;
197
198 -- check whether ForecastAmount is used
199 IF (INSTR(p_piped_sql_select, 'ForecastAmount', 1, 1) > 0) THEN
200 x_exp_type_code := x_exp_type_code || 'Y';
201 ELSE
202 x_exp_type_code := x_exp_type_code || 'N';
203 END IF;
204
205 -- check whether the embedded formulas have the following flag setting:
206 -- trx_group_code = 'INDIVIDUAL', cumulative_flag = 'N' and itd_flag = 'N'
207 -- and threshold_all_tier_flag = 'N'
208 NULL; -- to be added later
209
210 -- convert x_exp_type_code to something that is easy to understand
211 IF (x_exp_type_code IN('YNYYYN', 'YNYYNN')) THEN
212 x_exp_type_code := 'IO';
213 ELSIF(x_exp_type_code IN('YNYNYN', 'YNYNNN')) THEN
214 x_exp_type_code := 'IO_ITDN';
215 ELSIF(x_exp_type_code IN('YYYYYN', 'YYYYNN', 'YYYNYN', 'YYYNNN')) THEN
216 x_exp_type_code := 'GO';
217 ELSIF(x_exp_type_code IN('YYNNYN', 'YYNNNN')) THEN
218 x_exp_type_code := 'GIGO';
219 ELSIF(x_exp_type_code = 'YNNNYN') THEN
220 x_exp_type_code := 'IIIO';
221 ELSIF(x_exp_type_code = 'YNNNNN') THEN
222 x_exp_type_code := 'IIIOIPGP';
223 ELSIF(x_exp_type_code IN('NNYYNY', 'NNYNNY')) THEN
224 x_exp_type_code := 'FO';
225 ELSIF(x_exp_type_code IN('NNYYNN', 'NNYNNN')) THEN
226 x_exp_type_code := 'IOGOBOFO';
227 ELSIF(x_exp_type_code = 'NNNNNY') THEN
228 x_exp_type_code := 'FIFO';
229 ELSIF(x_exp_type_code = 'NNNNNN') THEN
230 x_exp_type_code := 'IRIOIPGOGPBIBOBPFRFO';
231 ELSE
232 x_exp_type_code := NULL;
233 END IF;
234
235 -- check whether it can be used in dynamic rate tables also
236 IF (x_exp_type_code = 'IRIOIPGOGPBIBOBPFRFO') THEN
237 -- if all the tables used are only from cn_quotas_v, cn_period_quotas, cn_srp_quota_assigns,
238 -- and cn_srp_period_quotas, then it can be used in dynamic dimension tiers also
239 IF (p_piped_sql_from = 'DUAL|' OR p_piped_sql_from = 'SYS.DUAL|') THEN
240 x_exp_type_code := x_exp_type_code || 'DDT';
241 ELSE
242 l_pos := 1;
243
244 LOOP
245 l_pos := INSTR(p_piped_sql_from, ' ', l_pos, 1);
246
247 IF (l_pos = 0) THEN
248 x_exp_type_code := x_exp_type_code || 'DDT';
249 EXIT;
250 END IF;
251
252 l_pos := l_pos + 1;
253 l_alias := SUBSTR(p_piped_sql_from, l_pos, INSTR(p_piped_sql_from, '|', l_pos, 1) - l_pos);
254
255 IF (l_alias NOT IN('CQ', 'CPQ', 'CSQA', 'CSPQ')) THEN
256 EXIT;
257 END IF;
258 END LOOP;
259 END IF;
260 END IF;
261
262 -- see if expression includes plan element references
263 IF l_pe_tbl.COUNT > 0 THEN
264 IF x_exp_type_code IN('FO', 'FIFO') THEN
265 -- forecast and DDT expressions cannot be used with plan elements
266 x_exp_type_code := NULL;
267 ELSIF x_exp_type_code IN('IRIOIPGOGPBIBOBPFRFODDT', 'IRIOIPGOGPBIBOBPFRFO') THEN
268 x_exp_type_code := 'IIIOIPGOGPBIBOBP';
269 ELSIF x_exp_type_code = 'IOGOBOFO' THEN
270 x_exp_type_code := 'IOGOBO';
271 END IF;
272 END IF;
273 END classify_expression;
274
275 -- Start of comments
276 -- API name : Create_Expression
277 -- Type : Private.
278 -- Function :
279 -- Pre-reqs : None.
280 -- Parameters :
281 -- IN : p_api_version IN NUMBER Required
282 -- p_init_msg_list IN VARCHAR2 Optional
283 -- Default = FND_API.G_FALSE
284 -- p_commit IN VARCHAR2 Optional
285 -- Default = FND_API.G_FALSE
286 -- p_validation_level IN NUMBER Optional
287 -- Default = FND_API.G_VALID_LEVEL_FULL
288 -- p_name IN VARCHAR2 Required
289 -- p_description IN VARCHAR2 Optional
290 -- Default = null
291 -- p_expression_disp IN VARCHAR2 Optional
292 -- Default = null
293 -- p_sql_select IN VARCHAR2 Optional
294 -- Default = null
295 -- p_sql_from IN VARCHAR2 Optional
296 -- Default = null
297 -- p_piped_expression_disp IN VARCHAR2 Optional
298 -- Default = null
299 -- p_piped_sql_select IN VARCHAR2 Optional
303 -- OUT : x_calc_sql_exp_id OUT NUMBER
300 -- Default = null
301 -- p_piped_sql_from IN VARCHAR2 Optional
302 -- Default = null
304 -- x_exp_type_code OUT VARCHAR2(30)
305 -- x_status OUT VARCHAR2(30)
306 -- x_return_status OUT VARCHAR2(1)
307 -- x_msg_count OUT NUMBER
308 -- x_msg_data OUT VARCHAR2(4000)
309 -- Version : Current version 1.0
310 -- Initial version 1.0
311 --
312 -- Notes : Create SQL expressions that will be used in calculation.
313 -- 1) Validate the expression and return the result in x_status (Valid or Invalid)
314 -- 2) Classify expressions into sub types for formula validation and dynamic rate table validation
315 -- 3) If there are embedded expressions, record the embedding relations in cn_calc_edges
316 --
317 -- End of comments
318 PROCEDURE create_expression(
319 p_api_version IN NUMBER
320 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
321 , p_commit IN VARCHAR2 := fnd_api.g_false
322 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
323 , p_org_id IN cn_calc_sql_exps.org_id%TYPE
324 , p_name IN cn_calc_sql_exps.NAME%TYPE
325 , p_description IN cn_calc_sql_exps.description%TYPE := NULL
326 , p_expression_disp IN VARCHAR2 := NULL
327 , -- CLOBs
328 p_sql_select IN VARCHAR2 := NULL
329 , p_sql_from IN VARCHAR2 := NULL
330 , p_piped_expression_disp IN VARCHAR2 := NULL
331 , p_piped_sql_select IN VARCHAR2 := NULL
332 , p_piped_sql_from IN VARCHAR2 := NULL
333 , x_calc_sql_exp_id IN OUT NOCOPY cn_calc_sql_exps.calc_sql_exp_id%TYPE
334 , x_exp_type_code OUT NOCOPY cn_calc_sql_exps.exp_type_code%TYPE
335 , x_status OUT NOCOPY cn_calc_sql_exps.status%TYPE
336 , x_return_status OUT NOCOPY VARCHAR2
337 , x_msg_count OUT NOCOPY NUMBER
338 , x_msg_data OUT NOCOPY VARCHAR2
339 , x_object_version_number OUT NOCOPY cn_calc_sql_exps.object_version_number%TYPE
340 ) IS
341 l_api_name CONSTANT VARCHAR2(30) := 'Create_Expression';
342 l_api_version CONSTANT NUMBER := 1.0;
343 l_prompt cn_lookups.meaning%TYPE;
344 l_dummy PLS_INTEGER;
345 l_disp_start PLS_INTEGER;
346 l_select_start PLS_INTEGER;
347 l_disp_end PLS_INTEGER;
348 l_select_end PLS_INTEGER;
349 l_token VARCHAR2(4000);
350 l_calc_formula_id cn_calc_formulas.calc_formula_id%TYPE;
351
352 CURSOR exp_exists IS
353 SELECT 1
354 FROM cn_calc_sql_exps
355 WHERE NAME = p_name AND org_id = p_org_id;
356 BEGIN
357 -- Standard Start of API savepoint
358 SAVEPOINT create_expression;
359
360 -- Standard call to check for call compatibility.
361 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
362 RAISE fnd_api.g_exc_unexpected_error;
363 END IF;
364
365 -- Initialize message list if p_init_msg_list is set to TRUE.
366 IF fnd_api.to_boolean(p_init_msg_list) THEN
367 fnd_msg_pub.initialize;
368 END IF;
369
370 -- Initialize API return status to success
371 x_return_status := fnd_api.g_ret_sts_success;
372
373 -- API body
374 IF (p_name IS NULL) THEN
375 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
376 l_prompt := cn_api.get_lkup_meaning('EXP_NAME', 'CN_PROMPTS');
377 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
378 fnd_message.set_token('OBJ_NAME', l_prompt);
379 fnd_msg_pub.ADD;
380 END IF;
381
382 RAISE fnd_api.g_exc_error;
383 END IF;
384
385 OPEN exp_exists;
386 FETCH exp_exists INTO l_dummy;
387 CLOSE exp_exists;
388
389 IF (l_dummy = 1) THEN
390 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
391 fnd_message.set_name('CN', 'CN_NAME_NOT_UNIQUE');
392 fnd_msg_pub.ADD;
393 END IF;
394
395 RAISE fnd_api.g_exc_error;
396 END IF;
397
398 -- make sure name isn't too long
399 IF LENGTH(p_name) > 30 THEN
400 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
401 fnd_message.set_name('CN', 'CN_NAME_TOO_LONG');
402 fnd_message.set_token('LENGTH', 30);
403 fnd_msg_pub.ADD;
404 END IF;
405
406 RAISE fnd_api.g_exc_error;
407 END IF;
408
409 -- parse the expression and classify it into sub types
410 classify_expression(
411 p_org_id => p_org_id
412 , p_sql_select => p_sql_select
413 , p_sql_from => p_sql_from
414 , p_piped_sql_select => p_piped_sql_select
415 , p_piped_sql_from => p_piped_sql_from
416 , x_status => x_status
417 , x_exp_type_code => x_exp_type_code
418 , x_msg_count => x_msg_count
419 , x_msg_data => x_msg_data
420 );
421 -- call table handler to create the expression
422 cn_calc_sql_exps_pkg.insert_row(
423 x_org_id => p_org_id
427 , x_status => x_status
424 , x_calc_sql_exp_id => x_calc_sql_exp_id
425 , x_name => p_name
426 , x_description => p_description
428 , x_exp_type_code => x_exp_type_code
429 , x_expression_disp => p_expression_disp
430 , x_sql_select => p_sql_select
431 , x_sql_from => p_sql_from
432 , x_piped_sql_select => p_piped_sql_select
433 , x_piped_sql_from => p_piped_sql_from
434 , x_piped_expression_disp => p_piped_expression_disp
435 , x_object_version_number => x_object_version_number
436 );
437 -- record calc edges
438 l_disp_start := 1;
439 l_select_start := 1;
440
441 LOOP
442 l_disp_end := INSTR(p_piped_expression_disp, '|', l_disp_start, 1);
443
444 IF (l_disp_end IS NULL OR l_disp_end = 0) THEN
445 EXIT;
446 END IF;
447
448 l_token := SUBSTR(p_piped_expression_disp, l_disp_start, l_disp_end - l_disp_start);
449 l_disp_start := l_disp_end + 1;
450 l_select_end := INSTR(p_piped_sql_select, '|', l_select_start, 1);
451
452 -- if the corresponding piped select part is in parenthesis, it is an embedded expression
453 IF (
454 INSTR(p_piped_sql_select, '(', l_select_start, 1) = l_select_start
455 AND (l_select_end - l_select_start) > 1
456 ) THEN
457 -- insert calc edges (calc edges has no table handler)
458 INSERT INTO cn_calc_edges
459 (
460 org_id
461 , calc_edge_id
462 , parent_id
463 , child_id
464 , edge_type
465 , creation_date
466 , created_by
467 , last_update_login
468 , last_update_date
469 , last_updated_by
470 )
471 SELECT org_id
472 , cn_calc_edges_s.NEXTVAL
473 , x_calc_sql_exp_id
474 , calc_sql_exp_id
475 , 'EE'
476 , SYSDATE
477 , fnd_global.user_id
478 , fnd_global.login_id
479 , SYSDATE
480 , fnd_global.user_id
481 FROM cn_calc_sql_exps
482 WHERE NAME = l_token
483 AND org_id=p_org_id;
484 ELSIF(INSTR(p_piped_sql_select, 'cn_formula', l_select_start, 1) = l_select_start) THEN
485 l_dummy := INSTR(p_piped_sql_select, '_', l_select_start, 2) + 1;
486 l_calc_formula_id :=
487 TO_NUMBER(
488 SUBSTR(p_piped_sql_select, l_dummy, INSTR(p_piped_sql_select, '_', l_dummy, 1) - l_dummy)
489 );
490
491 INSERT INTO cn_calc_edges
492 (
493 org_id
494 , calc_edge_id
495 , parent_id
496 , child_id
497 , edge_type
498 , creation_date
499 , created_by
500 , last_update_login
501 , last_update_date
502 , last_updated_by
503 )
504 VALUES (
505 p_org_id
506 , cn_calc_edges_s.NEXTVAL
507 , x_calc_sql_exp_id
508 , l_calc_formula_id
509 , 'FE'
510 , SYSDATE
511 , fnd_global.user_id
512 , fnd_global.login_id
513 , SYSDATE
514 , fnd_global.user_id
515 );
516 END IF;
517
518 l_select_start := l_select_end + 1;
519 END LOOP;
520
521 -- End of API body.
522
523 -- Standard check of p_commit.
524 IF fnd_api.to_boolean(p_commit) THEN
525 COMMIT WORK;
526 END IF;
527
528 -- Standard call to get message count and if count is 1, get message info.
529 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
530 , p_encoded => fnd_api.g_false);
531 EXCEPTION
532 WHEN fnd_api.g_exc_error THEN
533 ROLLBACK TO create_expression;
534 x_return_status := fnd_api.g_ret_sts_error;
535 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
536 , p_encoded => fnd_api.g_false);
537 WHEN fnd_api.g_exc_unexpected_error THEN
538 ROLLBACK TO create_expression;
539 x_return_status := fnd_api.g_ret_sts_unexp_error;
540 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
541 , p_encoded => fnd_api.g_false);
542 WHEN OTHERS THEN
543 ROLLBACK TO create_expression;
544 x_return_status := fnd_api.g_ret_sts_unexp_error;
545
546 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
547 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
548 END IF;
549
550 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
551 , p_encoded => fnd_api.g_false);
552 END create_expression;
553
554 -- Start of comments
555 -- API name : Update_Expressions
556 -- Type : Private.
557 -- Function :
558 -- Pre-reqs : None.
559 -- Parameters :
560 -- IN : p_api_version IN NUMBER Required
561 -- p_init_msg_list IN VARCHAR2 Optional
562 -- Default = FND_API.G_FALSE
566 -- Default = FND_API.G_VALID_LEVEL_FULL
563 -- p_commit IN VARCHAR2 Optional
564 -- Default = FND_API.G_FALSE
565 -- p_validation_level IN NUMBER Optional
567 -- p_update_parent_also IN VARCHAR2 Optional
568 -- Default = FND_API.G_FALSE
569 -- p_calc_sql_exp_id IN NUMBER Required
570 -- p_name IN VARCHAR2 Required
571 -- p_description IN VARCHAR2 Optional
572 -- Default = null
573 -- p_expression_disp IN VARCHAR2 Optional
574 -- Default = null
575 -- p_sql_select IN VARCHAR2 Optional
576 -- Default = null
577 -- p_sql_from IN VARCHAR2 Optional
578 -- Default = null
579 -- p_piped_expression_disp IN VARCHAR2 Optional
580 -- Default = null
581 -- p_piped_sql_select IN VARCHAR2 Optional
582 -- Default = null
583 -- p_piped_sql_from IN VARCHAR2 Optional
584 -- Default = null
585 -- p_ovn IN NUMBER Required
586 -- OUT : x_exp_type_code OUT VARCHAR2(30)
587 -- x_status OUT VARCHAR2(30)
588 -- x_return_status OUT VARCHAR2(1)
589 -- x_msg_count OUT NUMBER
590 -- x_msg_data OUT VARCHAR2(4000)
591 -- Version : Current version 1.0
592 -- Initial version 1.0
593 --
594 -- Notes : Update SQL expressions that will be used in calculation.
595 -- 1) validate the expression and return the result in x_status (Valid or Invalid)
596 -- 2) re-classify expressions into sub types for formula validation and dynamic rate table validation
597 -- 3) adjust the corresponding embedding relations in cn_calc_edges
598 -- 4) if the expression is used, update the parent expressions, formulas accordingly
599 --
600 -- End of comments
601 PROCEDURE update_expression(
602 p_api_version IN NUMBER
603 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
604 , p_commit IN VARCHAR2 := fnd_api.g_false
605 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
606 , p_update_parent_also IN VARCHAR2 := fnd_api.g_false
607 , p_org_id IN cn_calc_sql_exps.org_id%TYPE
608 , p_calc_sql_exp_id IN cn_calc_sql_exps.calc_sql_exp_id%TYPE
609 , p_name IN cn_calc_sql_exps.NAME%TYPE
610 , p_description IN cn_calc_sql_exps.description%TYPE := NULL
611 , p_expression_disp IN VARCHAR2 := NULL
612 , -- CLOBs
613 p_sql_select IN VARCHAR2 := NULL
614 , p_sql_from IN VARCHAR2 := NULL
615 , p_piped_expression_disp IN VARCHAR2 := NULL
616 , p_piped_sql_select IN VARCHAR2 := NULL
617 , p_piped_sql_from IN VARCHAR2 := NULL
618 , p_ovn IN OUT NOCOPY cn_calc_sql_exps.object_version_number%TYPE
619 , x_exp_type_code OUT NOCOPY cn_calc_sql_exps.exp_type_code%TYPE
620 , x_status OUT NOCOPY cn_calc_sql_exps.status%TYPE
621 , x_return_status OUT NOCOPY VARCHAR2
622 , x_msg_count OUT NOCOPY NUMBER
623 , x_msg_data OUT NOCOPY VARCHAR2
624 ) IS
625 l_api_name CONSTANT VARCHAR2(30) := 'Update_Expression';
626 l_api_version CONSTANT NUMBER := 1.0;
627 l_prompt cn_lookups.meaning%TYPE;
628 l_dummy PLS_INTEGER;
629 l_disp_start PLS_INTEGER;
630 l_select_start PLS_INTEGER;
631 l_disp_end PLS_INTEGER;
632 l_select_end PLS_INTEGER;
633 l_token VARCHAR2(4000);
634 l_calc_formula_id cn_calc_formulas.calc_formula_id%TYPE;
635 l_exp_names VARCHAR2(4000) := '|';
636 l_formula_ids VARCHAR2(4000) := '|';
637
638 CURSOR parent_exist IS
639 SELECT 1
640 FROM DUAL
641 WHERE (EXISTS(SELECT 1
642 FROM cn_calc_edges
643 WHERE child_id = p_calc_sql_exp_id AND edge_type = 'EE'))
644 OR (
645 EXISTS(SELECT 1
646 FROM cn_calc_formulas
647 WHERE perf_measure_id = p_calc_sql_exp_id OR output_exp_id = p_calc_sql_exp_id)
648 )
649 OR (
650 EXISTS(
651 SELECT 1
652 FROM cn_formula_inputs
653 WHERE calc_sql_exp_id = p_calc_sql_exp_id
654 OR f_calc_sql_exp_id = p_calc_sql_exp_id)
655 )
656 OR (EXISTS(SELECT 1
657 FROM cn_rate_dim_tiers
658 WHERE min_exp_id = p_calc_sql_exp_id OR max_exp_id = p_calc_sql_exp_id));
659
660 CURSOR exp_exists IS
661 SELECT 1
662 FROM cn_calc_sql_exps
663 WHERE NAME = p_name AND org_id = p_org_id AND calc_sql_exp_id <> p_calc_sql_exp_id;
664 BEGIN
665 -- Standard Start of API savepoint
666 SAVEPOINT update_expression;
667
671 END IF;
668 -- Standard call to check for call compatibility.
669 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
670 RAISE fnd_api.g_exc_unexpected_error;
672
673 -- Initialize message list if p_init_msg_list is set to TRUE.
674 IF fnd_api.to_boolean(p_init_msg_list) THEN
675 fnd_msg_pub.initialize;
676 END IF;
677
678 -- Initialize API return status to success
679 x_return_status := fnd_api.g_ret_sts_success;
680
681 -- API body
682 IF (p_name IS NULL) THEN
683 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
684 l_prompt := cn_api.get_lkup_meaning('EXP_NAME', 'CN_PROMPTS');
685 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
686 fnd_message.set_token('OBJ_NAME', l_prompt);
687 fnd_msg_pub.ADD;
688 END IF;
689
690 RAISE fnd_api.g_exc_error;
691 END IF;
692
693 OPEN exp_exists;
694 FETCH exp_exists INTO l_dummy;
695 CLOSE exp_exists;
696
697 IF (l_dummy = 1) THEN
698 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
699 fnd_message.set_name('CN', 'CN_NAME_NOT_UNIQUE');
700 fnd_msg_pub.ADD;
701 END IF;
702
703 RAISE fnd_api.g_exc_error;
704 END IF;
705
706 -- see if expression is in use
707 OPEN parent_exist;
708 FETCH parent_exist INTO l_dummy;
709 CLOSE parent_exist;
710
711 IF (l_dummy = 1) THEN
712 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
713 fnd_message.set_name('CN', 'CN_EXP_IN_USE');
714 fnd_msg_pub.ADD;
715 END IF;
716
717 RAISE fnd_api.g_exc_error;
718 END IF;
719
720 -- parse the expression and classify it into sub types
721 classify_expression(
722 p_org_id => p_org_id
723 , p_sql_select => p_sql_select
724 , p_sql_from => p_sql_from
725 , p_piped_sql_select => p_piped_sql_select
726 , p_piped_sql_from => p_piped_sql_from
727 , x_status => x_status
728 , x_exp_type_code => x_exp_type_code
729 , x_msg_count => x_msg_count
730 , x_msg_data => x_msg_data
731 );
732 -- check ovn
733 cn_calc_sql_exps_pkg.lock_row(p_calc_sql_exp_id, p_ovn);
734 -- do update
735 cn_calc_sql_exps_pkg.update_row(
736 x_org_id => p_org_id
737 , x_calc_sql_exp_id => p_calc_sql_exp_id
738 , x_name => p_name
739 , x_description => p_description
740 , x_status => x_status
741 , x_exp_type_code => x_exp_type_code
742 , x_expression_disp => p_expression_disp
743 , x_sql_select => p_sql_select
744 , x_sql_from => p_sql_from
745 , x_piped_sql_select => p_piped_sql_select
746 , x_piped_sql_from => p_piped_sql_from
747 , x_piped_expression_disp => p_piped_expression_disp
748 , x_object_version_number => p_ovn
749 );
750 -- insert new calc edges
751 l_disp_start := 1;
752 l_select_start := 1;
753
754 LOOP
755 l_disp_end := INSTR(p_piped_expression_disp, '|', l_disp_start, 1);
756
757 IF (l_disp_end IS NULL OR l_disp_end = 0) THEN
758 EXIT;
759 END IF;
760
761 l_token := SUBSTR(p_piped_expression_disp, l_disp_start, l_disp_end - l_disp_start);
762 l_disp_start := l_disp_end + 1;
763 l_select_end := INSTR(p_piped_sql_select, '|', l_select_start, 1);
764
765 -- if the corresponding piped select part is in parenthesis, it is an embedded expression
766 IF (
767 INSTR(p_piped_sql_select, '(', l_select_start, 1) = l_select_start
768 AND (l_select_end - l_select_start) > 1
769 ) THEN
770 l_exp_names := l_exp_names || l_token || '|';
771
772 INSERT INTO cn_calc_edges
773 (
774 org_id
775 , calc_edge_id
776 , parent_id
777 , child_id
778 , edge_type
779 , creation_date
780 , created_by
781 , last_update_login
782 , last_update_date
783 , last_updated_by
784 )
785 SELECT org_id
786 , cn_calc_edges_s.NEXTVAL
787 , p_calc_sql_exp_id
788 , calc_sql_exp_id
789 , 'EE'
790 , SYSDATE
791 , fnd_global.user_id
792 , fnd_global.login_id
793 , SYSDATE
794 , fnd_global.user_id
795 FROM cn_calc_sql_exps
796 WHERE NAME = l_token
797 AND org_id= p_org_id
798 AND NOT EXISTS(
799 SELECT 1
800 FROM cn_calc_edges
801 WHERE parent_id = p_calc_sql_exp_id
802 AND child_id = (SELECT calc_sql_exp_id
803 FROM cn_calc_sql_exps
804 WHERE NAME = l_token AND org_id = p_org_id AND edge_type = 'EE'));
805 ELSIF(INSTR(p_piped_sql_select, 'cn_formula', l_select_start, 1) = l_select_start) THEN
806 l_dummy := INSTR(p_piped_sql_select, '_', l_select_start, 2) + 1;
807 l_calc_formula_id :=
808 TO_NUMBER(
812
809 SUBSTR(p_piped_sql_select, l_dummy, INSTR(p_piped_sql_select, '_', l_dummy, 1) - l_dummy)
810 );
811 l_formula_ids := l_formula_ids || l_calc_formula_id || '|';
813 INSERT INTO cn_calc_edges
814 (
815 org_id
816 , calc_edge_id
817 , parent_id
818 , child_id
819 , edge_type
820 , creation_date
821 , created_by
822 , last_update_login
823 , last_update_date
824 , last_updated_by
825 )
826 SELECT p_org_id
827 , cn_calc_edges_s.NEXTVAL
828 , p_calc_sql_exp_id
829 , l_calc_formula_id
830 , 'FE'
831 , SYSDATE
832 , fnd_global.user_id
833 , fnd_global.login_id
834 , SYSDATE
835 , fnd_global.user_id
836 FROM DUAL
837 WHERE NOT EXISTS(
838 SELECT 1
839 FROM cn_calc_edges
840 WHERE parent_id = p_calc_sql_exp_id
841 AND child_id = l_calc_formula_id
842 AND edge_type = 'FE');
843 END IF;
844
845 l_select_start := l_select_end + 1;
846 END LOOP;
847
848 -- delete obsolete calc edges
849 --IF (l_formula_ids <> '|') THEN
850 DELETE FROM cn_calc_edges
851 WHERE parent_id = p_calc_sql_exp_id
852 AND INSTR(l_formula_ids, '|' || child_id || '|', 1, 1) = 0
853 AND edge_type = 'FE';
854
855 --END IF;
856
857 --IF (l_exp_names <> '|') THEN
858 DELETE FROM cn_calc_edges a
859 WHERE a.parent_id = p_calc_sql_exp_id
860 AND a.edge_type = 'EE'
861 AND NOT EXISTS(
862 SELECT 1
863 FROM cn_calc_sql_exps b
864 WHERE a.child_id = b.calc_sql_exp_id
865 AND INSTR(l_exp_names, '|' || b.NAME || '|', 1, 1) > 0);
866
867 --END IF;
868
869 -- update parent expressions and formulas also
870 IF (fnd_api.to_boolean(p_update_parent_also)) THEN
871 NULL;
872 END IF;
873
874 -- End of API body.
875
876 -- Standard check of p_commit.
877 IF fnd_api.to_boolean(p_commit) THEN
878 COMMIT WORK;
879 END IF;
880
881 -- Standard call to get message count and if count is 1, get message info.
882 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
883 , p_encoded => fnd_api.g_false);
884 EXCEPTION
885 WHEN fnd_api.g_exc_error THEN
886 ROLLBACK TO update_expression;
887 x_return_status := fnd_api.g_ret_sts_error;
888 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
889 , p_encoded => fnd_api.g_false);
890 WHEN fnd_api.g_exc_unexpected_error THEN
891 ROLLBACK TO update_expression;
892 x_return_status := fnd_api.g_ret_sts_unexp_error;
893 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
894 , p_encoded => fnd_api.g_false);
895 WHEN OTHERS THEN
896 ROLLBACK TO update_expression;
897 x_return_status := fnd_api.g_ret_sts_unexp_error;
898
899 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
900 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
901 END IF;
902
903 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
904 , p_encoded => fnd_api.g_false);
905 END update_expression;
906
907 -- Start of comments
908 -- API name : Delete_Expression
909 -- Type : Private.
910 -- Function :
911 -- Pre-reqs : None.
912 -- Parameters :
913 -- IN : p_api_version IN NUMBER Required
914 -- p_init_msg_list IN VARCHAR2 Optional
915 -- Default = FND_API.G_FALSE
916 -- p_commit IN VARCHAR2 Optional
917 -- Default = FND_API.G_FALSE
918 -- p_validation_level IN NUMBER Optional
919 -- Default = FND_API.G_VALID_LEVEL_FULL
920 -- p_calc_sql_exp_id IN NUMBER
921 -- OUT : x_return_status OUT VARCHAR2(1)
922 -- x_msg_count OUT NUMBER
923 -- x_msg_data OUT VARCHAR2(4000)
924 -- Version : Current version 1.0
925 -- Initial version 1.0
926 --
927 -- Notes : Delete an expression
928 -- 1) if it is used, it can not be deleted
929 -- 2) delete the embedding relations in cn_calc_edges if there is any
930 --
931 -- End of comments
932 PROCEDURE delete_expression(
933 p_api_version IN NUMBER
934 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
935 , p_commit IN VARCHAR2 := fnd_api.g_false
936 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
937 , p_calc_sql_exp_id IN cn_calc_sql_exps.calc_sql_exp_id%TYPE
938 , x_return_status OUT NOCOPY VARCHAR2
939 , x_msg_count OUT NOCOPY NUMBER
940 , x_msg_data OUT NOCOPY VARCHAR2
941 ) IS
942 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Expression';
946 CURSOR parent_exist IS
943 l_api_version CONSTANT NUMBER := 1.0;
944 l_dummy PLS_INTEGER;
945
947 SELECT 1
948 FROM DUAL
949 WHERE (EXISTS(SELECT 1
950 FROM cn_calc_edges
951 WHERE child_id = p_calc_sql_exp_id AND edge_type = 'EE'))
952 OR (
953 EXISTS(SELECT 1
954 FROM cn_calc_formulas
955 WHERE perf_measure_id = p_calc_sql_exp_id OR output_exp_id = p_calc_sql_exp_id)
956 )
957 OR (
958 EXISTS(
959 SELECT 1
960 FROM cn_formula_inputs
961 WHERE calc_sql_exp_id = p_calc_sql_exp_id
962 OR f_calc_sql_exp_id = p_calc_sql_exp_id)
963 )
964 OR (EXISTS(SELECT 1
965 FROM cn_rate_dim_tiers
966 WHERE min_exp_id = p_calc_sql_exp_id OR max_exp_id = p_calc_sql_exp_id));
967 BEGIN
968 -- Standard Start of API savepoint
969 SAVEPOINT delete_expression;
970
971 -- Standard call to check for call compatibility.
972 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
973 RAISE fnd_api.g_exc_unexpected_error;
974 END IF;
975
976 -- Initialize message list if p_init_msg_list is set to TRUE.
977 IF fnd_api.to_boolean(p_init_msg_list) THEN
978 fnd_msg_pub.initialize;
979 END IF;
980
981 -- Initialize API return status to success
982 x_return_status := fnd_api.g_ret_sts_success;
983
984 -- API body
985 OPEN parent_exist;
986 FETCH parent_exist INTO l_dummy;
987 CLOSE parent_exist;
988
989 IF (l_dummy = 1) THEN
990 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
991 fnd_message.set_name('CN', 'CN_EXP_IN_USE');
992 fnd_msg_pub.ADD;
993 END IF;
994
995 RAISE fnd_api.g_exc_error;
996 END IF;
997
998 cn_calc_sql_exps_pkg.delete_row(x_calc_sql_exp_id => p_calc_sql_exp_id);
999
1000 DELETE FROM cn_calc_edges e
1001 WHERE edge_type IN('EE', 'FE') AND NOT EXISTS(SELECT 1
1002 FROM cn_calc_sql_exps
1003 WHERE calc_sql_exp_id = e.parent_id);
1004
1005 -- End of API body.
1006
1007 -- Standard check of p_commit.
1008 IF fnd_api.to_boolean(p_commit) THEN
1009 COMMIT WORK;
1010 END IF;
1011
1012 -- Standard call to get message count and if count is 1, get message info.
1013 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
1014 , p_encoded => fnd_api.g_false);
1015 EXCEPTION
1016 WHEN fnd_api.g_exc_error THEN
1017 ROLLBACK TO delete_expression;
1018 x_return_status := fnd_api.g_ret_sts_error;
1019 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
1020 , p_encoded => fnd_api.g_false);
1021 WHEN fnd_api.g_exc_unexpected_error THEN
1022 ROLLBACK TO delete_expression;
1023 x_return_status := fnd_api.g_ret_sts_unexp_error;
1024 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
1025 , p_encoded => fnd_api.g_false);
1026 WHEN OTHERS THEN
1027 ROLLBACK TO delete_expression;
1028 x_return_status := fnd_api.g_ret_sts_unexp_error;
1029
1030 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1031 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1032 END IF;
1033
1034 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
1035 , p_encoded => fnd_api.g_false);
1036 END delete_expression;
1037
1038 /*-- Start of comments
1039 -- API name : Get_Parent_Expressions
1040 -- Type : Private.
1041 -- Function :
1042 -- Pre-reqs : None.
1043 -- Parameters :
1044 -- IN : p_api_version IN NUMBER Required
1045 -- p_init_msg_list IN VARCHAR2 Optional
1046 -- Default = FND_API.G_FALSE
1047 -- p_commit IN VARCHAR2 Optional
1048 -- Default = FND_API.G_FALSE
1049 -- p_validation_level IN NUMBER Optional
1050 -- Default = FND_API.G_VALID_LEVEL_FULL
1051 -- p_calc_sql_exp_id IN NUMBER
1052 -- OUT : x_parents_tbl OUT expression_tbl_type
1053 -- x_return_status OUT VARCHAR2(1)
1054 -- x_msg_count OUT NUMBER
1055 -- x_msg_data OUT VARCHAR2(4000)
1056 -- Version : Current version 1.0
1057 -- Initial version 1.0
1058 --
1059 -- Notes : Get parent expressions if there is any
1060 --
1061 -- End of comments
1062 PROCEDURE Get_Parent_Expressions
1063 (p_api_version IN NUMBER ,
1064 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1065 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1066 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
1067 p_calc_sql_exp_id IN CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE,
1068 x_parents_tbl OUT NOCOPY parent_expression_tbl_type ,
1069 x_return_status OUT NOCOPY VARCHAR2 ,
1073 l_api_name CONSTANT VARCHAR2(30) := 'Get_Parent_Expressions';
1070 x_msg_count OUT NOCOPY NUMBER ,
1071 x_msg_data OUT NOCOPY VARCHAR2 )
1072 IS
1074 l_api_version CONSTANT NUMBER := 1.0;
1075
1076 i pls_integer := 0;
1077
1078 -- names of parent performance measures and formulas and dimensions
1079 CURSOR parent_names IS
1080 SELECT name
1081 FROM cn_calc_sql_exps
1082 WHERE calc_sql_exp_id IN (SELECT parent_id
1083 FROM cn_calc_edges
1084 CONNECT BY child_id = PRIOR parent_id
1085 AND edge_type = 'EE'
1086 START WITH child_id = p_calc_sql_exp_id
1087 AND edge_type = 'EE')
1088 UNION ALL
1089 SELECT name
1090 FROM cn_rate_dimensions
1091 WHERE rate_dimension_id in (SELECT rate_dimension_id
1092 FROM cn_rate_dim_tiers
1093 WHERE min_exp_id = p_calc_sql_exp_id
1094 OR max_exp_id = p_calc_sql_exp_id)
1095 UNION ALL
1096 SELECT name
1097 FROM cn_calc_formulas
1098 WHERE perf_measure_id = p_calc_sql_exp_id
1099 OR output_exp_id = p_calc_sql_exp_id
1100 OR f_output_exp_id = p_calc_sql_exp_id
1101 OR (calc_formula_id IN (SELECT calc_formula_id FROM cn_formula_inputs
1102 WHERE calc_sql_exp_id = p_calc_sql_exp_id
1103 OR f_calc_sql_exp_id = p_calc_sql_exp_id));
1104
1105 BEGIN
1106 -- Standard call to check for call compatibility.
1107 IF NOT FND_API.Compatible_API_Call
1108 (l_api_version ,
1109 p_api_version ,
1110 l_api_name ,
1111 G_PKG_NAME )
1112 THEN
1113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1114 END IF;
1115 -- Initialize message list if p_init_msg_list is set to TRUE.
1116 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1117 FND_MSG_PUB.initialize;
1118 END IF;
1119 -- Initialize API return status to success
1120 x_return_status := FND_API.G_RET_STS_SUCCESS;
1121
1122 -- API body
1123 FOR parent_name IN parent_names LOOP
1124 x_parents_tbl(i) := parent_name.name;
1125 i := i + 1;
1126 END LOOP;
1127
1128 -- End of API body.
1129
1130 -- Standard check of p_commit.
1131 IF FND_API.To_Boolean( p_commit ) THEN
1132 COMMIT WORK;
1133 END IF;
1134 -- Standard call to get message count and if count is 1, get message info.
1135 FND_MSG_PUB.count_and_get
1136 (p_count => x_msg_count ,
1137 p_data => x_msg_data ,
1138 p_encoded => FND_API.G_FALSE );
1139 EXCEPTION
1140 WHEN FND_API.G_EXC_ERROR THEN
1141 x_return_status := FND_API.G_RET_STS_ERROR ;
1142 FND_MSG_PUB.count_and_get
1143 (p_count => x_msg_count ,
1144 p_data => x_msg_data ,
1145 p_encoded => FND_API.G_FALSE );
1146 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1148 FND_MSG_PUB.count_and_get
1149 (p_count => x_msg_count ,
1150 p_data => x_msg_data ,
1151 p_encoded => FND_API.G_FALSE );
1152 WHEN OTHERS THEN
1153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1154 IF FND_MSG_PUB.check_msg_level
1155 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1156 THEN
1157 FND_MSG_PUB.add_exc_msg
1158 (G_PKG_NAME ,
1159 l_api_name );
1160 END IF;
1161 FND_MSG_PUB.count_and_get
1162 (p_count => x_msg_count ,
1163 p_data => x_msg_data ,
1164 p_encoded => FND_API.G_FALSE );
1165 END Get_Parent_Expressions; */
1166
1167 /* PROCEDURE get_expr_summary
1168 (p_first IN NUMBER,
1169 p_last IN NUMBER,
1170 p_srch_name IN VARCHAR2 := '%',
1171 x_total_rows OUT NOCOPY NUMBER,
1172 x_result_tbl OUT NOCOPY calc_expression_tbl_type) IS
1173
1174 l_count NUMBER := 0;
1175 l_srch_name varchar2(31) := upper(p_srch_name) || '%';
1176
1177 CURSOR get_rows IS
1178 select calc_sql_exp_id, name, description, status, exp_type_code
1179 from cn_calc_sql_exps
1180 where upper(name) like l_srch_name
1181 order by 2;
1182 CURSOR count_rows IS select count(1) from cn_calc_sql_exps
1183 where upper(name) like l_srch_name;
1184
1185 BEGIN
1186 open count_rows;
1187 fetch count_rows into x_total_rows;
1188 close count_rows;
1189 for c in get_rows loop
1190 l_count := l_count + 1;
1191 if l_count >= p_first then
1192 x_result_tbl(l_count) := c; -- record copy ok because of %types
1193 end if;
1194 if l_count = p_last then
1195 exit;
1196 end if;
1197 end loop;
1198 END get_expr_summary; */
1199
1200 /* PROCEDURE get_expr_detail
1201 (p_calc_sql_exp_id IN CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE,
1202 x_name OUT NOCOPY CN_CALC_SQL_EXPS.NAME%TYPE,
1206 x_expression_disp OUT NOCOPY VARCHAR2, -- CLOBs
1203 x_description OUT NOCOPY CN_CALC_SQL_EXPS.DESCRIPTION%TYPE,
1204 x_status OUT NOCOPY CN_CALC_SQL_EXPS.STATUS%TYPE,
1205 x_exp_type_code OUT NOCOPY CN_CALC_SQL_EXPS.EXP_TYPE_CODE%TYPE,
1207 x_sql_select OUT NOCOPY VARCHAR2,
1208 x_sql_from OUT NOCOPY VARCHAR2,
1209 x_piped_sql_select OUT NOCOPY VARCHAR2,
1210 x_piped_sql_from OUT NOCOPY VARCHAR2,
1211 x_piped_expression_disp OUT NOCOPY VARCHAR2,
1212 x_ovn OUT NOCOPY CN_CALC_SQL_EXPS.OBJECT_VERSION_NUMBER%TYPE) IS
1213
1214 CURSOR get_data IS
1215 select name, description, status, exp_type_code,
1216 dbms_lob.substr(expression_disp),
1217 dbms_lob.substr(sql_select),
1218 dbms_lob.substr(sql_from),
1219 dbms_lob.substr(piped_sql_select),
1220 dbms_lob.substr(piped_sql_from),
1221 dbms_lob.substr(piped_expression_disp),
1222 object_version_number
1223 from cn_calc_sql_exps where calc_sql_exp_id = p_calc_sql_exp_id;
1224 BEGIN
1225 OPEN get_data;
1226 FETCH get_data INTO x_name, x_description, x_status, x_exp_type_code,
1227 x_expression_disp, x_sql_select, x_sql_from,
1228 x_piped_sql_select, x_piped_sql_from, x_piped_expression_disp, x_ovn;
1229 CLOSE get_data;
1230 END get_expr_detail; */
1231
1232 /* FUNCTION add_tree_node(node_value VARCHAR2,
1233 node_label VARCHAR2,
1234 parent_node_value VARCHAR2,
1235 element VARCHAR2)
1236 RETURN expr_type_rec_type IS
1237 l_rec expr_type_rec_type;
1238 BEGIN
1239 l_rec.node_value := node_value;
1240 l_rec.node_label := nvl(node_label, node_value);
1241 l_rec.parent_node_value := parent_node_value;
1242 l_rec.element := element;
1243 return l_rec;
1244 END add_tree_node; */
1245
1246 /* PROCEDURE get_type_tree
1247 (x_types OUT NOCOPY expr_type_tbl_type) IS
1248 cursor osc_elements is
1249 select user_name, name, alias, object_id
1250 from cn_objects
1251 where calc_eligible_flag = 'Y'
1252 and object_type in ('TBL', 'VIEW')
1253 and user_name is not null
1254 and object_id < 0
1255 and name like 'CN%'
1256 and alias is not null
1257 order by user_name;
1258
1259 cursor table_columns(p_table_id number) is
1260 select user_name, name
1261 from cn_objects
1262 where table_id = p_table_id
1263 and calc_formula_flag = 'Y'
1264 and object_type = 'COL'
1265 order by user_name;
1266
1267 cursor calc_expressions is
1268 select calc_sql_exp_id, name, dbms_lob.substr(sql_select) node_value
1269 from cn_calc_sql_exps
1270 where status = 'VALID'
1271 and dbms_lob.getlength(sql_select) < 3999
1272 order by name;
1273
1274 cursor calc_formulas is
1275 select name, 'cn_formula_' || abs(calc_formula_id) || '_' || abs(org_id) ||
1276 '_pkg.get_result(p_commission_line_id)' node_value
1277 from cn_calc_formulas
1278 where formula_status = 'COMPLETE'
1279 and cumulative_flag = 'N'
1280 and trx_group_code = 'INDIVIDUAL'
1281 and itd_flag = 'N'
1282 and formula_type = 'C'
1283 order by name;
1284
1285 cursor ext_elements is
1286 select user_name, name, alias, object_id
1287 from cn_objects
1288 where calc_eligible_flag = 'Y'
1289 and object_type in ('TBL', 'VIEW')
1290 and user_name is not null
1291 and object_id > 0
1292 order by user_name;
1293
1294 cursor plan_elements is
1295 select quota_id, name
1296 from cn_quotas_v
1297 order by name;
1298
1299
1300
1301 TYPE vt is table of varchar2(30);
1302 num_functions vt := vt('ABS', 'CEIL', 'EXP', 'FLOOR', 'GREATEST', 'LEAST',
1303 'MOD', 'POWER', 'ROUND', 'SIGN', 'SQRT', 'TO_NUMBER', 'TRUNC');
1304 grp_functions vt := vt('AVG', 'COUNT', 'MAX', 'MIN', 'STDDEV',
1305 'SUM', 'VARIANCE');
1306 oth_functions vt := vt('DECODE', 'NVL');
1307 pe_columns vt := vt('TARGET_AMOUNT', 'COMMISSION_PAYED_PTD','ITD_TARGET',
1308 'PERIOD_PAYMENT', 'ITD_PAYMENT',
1309 'COMMISSION_PAYED_ITD', 'INPUT_ACHIEVED_PTD',
1310 'INPUT_ACHIEVED_ITD', 'PERF_ACHIEVED_PTD',
1311 'PERF_ACHIEVED_ITD');
1312 l_count number := 0;
1313 BEGIN
1314 -- add nodes of calculation value tree in DFS order
1315 x_types(l_count) :=
1316 add_tree_node('OSC_ELEMENTS',
1317 cn_api.get_lkup_meaning('OSC_ELEMENTS', 'EXPRESSION_TYPE'),
1318 null, null);
1319 l_count := l_count + 1;
1320 for t in osc_elements loop
1321 x_types(l_count) :=
1322 add_tree_node(t.name || '|' ||t.alias,t.user_name,'OSC_ELEMENTS',null);
1323 l_count := l_count + 1;
1324 for c in table_columns(t.object_id) loop
1325 x_types(l_count) :=
1326 add_tree_node(t.user_name || '.' || c.user_name,c.user_name, t.name,
1327 t.alias || '.' || c.name);
1328 l_count := l_count + 1;
1329 end loop;
1330 end loop;
1331
1332 x_types(l_count) :=
1333 add_tree_node('EXPRESSIONS',
1334 cn_api.get_lkup_meaning('EXPRESSIONS', 'EXPRESSION_TYPE'),
1335 null, null);
1336 l_count := l_count + 1;
1337 for e in calc_expressions loop
1338 x_types(l_count) :=
1342
1339 add_tree_node(e.name, e.name, 'EXPRESSIONS', '(' ||e.node_value|| ')');
1340 l_count := l_count + 1;
1341 end loop;
1343 x_types(l_count) :=
1344 add_tree_node('FORMULAS',
1345 cn_api.get_lkup_meaning('FORMULAS', 'EXPRESSION_TYPE'),null,
1346 null);
1347 l_count := l_count + 1;
1348 for f in calc_formulas loop
1349 x_types(l_count) :=
1350 add_tree_node(f.name, f.name, 'FORMULAS', f.node_value);
1351 l_count := l_count + 1;
1352 end loop;
1353
1354 x_types(l_count) :=
1355 add_tree_node('EXTERNAL_ELEMENTS',
1356 cn_api.get_lkup_meaning('EXTERNAL_ELEMENTS',
1357 'EXPRESSION_TYPE'),
1358 null, null);
1359 l_count := l_count + 1;
1360 for x in ext_elements loop
1361 x_types(l_count) :=
1362 add_tree_node(x.name || '|' || x.alias, x.user_name,
1363 'EXTERNAL_ELEMENTS',
1364 null);
1365 l_count := l_count + 1;
1366 for c in table_columns(x.object_id) loop
1367 x_types(l_count) :=
1368 add_tree_node(x.user_name || '.' || c.user_name,c.user_name, x.name,
1369 x.alias || '.' || c.name);
1370 l_count := l_count + 1;
1371 end loop;
1372 end loop;
1373
1374 x_types(l_count) :=
1375 add_tree_node('SQL_FUNCTIONS', cn_api.get_lkup_meaning('SQL_FUNCTIONS',
1376 'EXPRESSION_TYPE'),
1377 null, null);
1378 l_count := l_count + 1;
1379 x_types(l_count) :=
1380 add_tree_node('NUMBER_FUNCTIONS',
1381 cn_api.get_lkup_meaning('NUMBER_FUNCTIONS',
1382 'EXPRESSION_TYPE'),
1383 'SQL_FUNCTIONS', null);
1384 l_count := l_count + 1;
1385 for i in num_functions.first..num_functions.last loop
1386 x_types(l_count) :=
1387 add_tree_node(num_functions(i) || '(', num_functions(i),
1388 'NUMBER_FUNCTIONS', num_functions(i) || '(');
1389 l_count := l_count + 1;
1390 end loop;
1391 x_types(l_count) :=
1392 add_tree_node('GROUP_FUNCTIONS',
1393 cn_api.get_lkup_meaning('GROUP_FUNCTIONS',
1394 'EXPRESSION_TYPE'),
1395 'SQL_FUNCTIONS', null);
1396 l_count := l_count + 1;
1397 for i in grp_functions.first..grp_functions.last loop
1398 x_types(l_count) :=
1399 add_tree_node(grp_functions(i) || '(', grp_functions(i) || '()',
1400 'GROUP_FUNCTIONS', grp_functions(i) || '(');
1401 l_count := l_count + 1;
1402 end loop;
1403
1404 x_types(l_count) :=
1405 add_tree_node('OTHER_FUNCTIONS',
1406 cn_api.get_lkup_meaning('OTHERS', 'EXPRESSION_TYPE'),
1407 'SQL_FUNCTIONS', null);
1408 l_count := l_count + 1;
1409 for i in oth_functions.first..oth_functions.last loop
1410 x_types(l_count) :=
1411 add_tree_node(oth_functions(i) || '(', oth_functions(i) || '()',
1412 'OTHER_FUNCTIONS',
1413 oth_functions(i) || '(');
1414 l_count := l_count + 1;
1415 end loop;
1416
1417 -- Previously Commented out - START
1418
1419 x_types(l_count) :=
1420 add_tree_node('PLAN_ELEMENTS',
1421 cn_api.get_lkup_meaning('PLAN_ELTS', 'EXPRESSION_TYPE'),
1422 null, null);
1423 l_count := l_count + 1;
1424 for i in plan_elements loop
1425 x_types(l_count) :=
1426 add_tree_node(i.quota_id || 'PE', i.name, 'PLAN_ELEMENTS', null);
1427 l_count := l_count + 1;
1428 for j in pe_columns.first..pe_columns.last loop
1429 x_types(l_count) :=
1430 add_tree_node(i.name || '.' || pe_columns(j),
1431 i.name || '.' || pe_columns(j),
1432 i.quota_id || 'PE',
1433 '(' || i.quota_id || 'PE.' || pe_columns(j) || ')');
1434 l_count := l_count + 1;
1435 end loop;
1436 end loop;
1437
1438 -- Previously Commented out - END
1439
1440 x_types(l_count) :=
1441 add_tree_node('OTHERS', cn_api.get_lkup_meaning('OTHERS',
1442 'EXPRESSION_TYPE'),
1443 null, null);
1444 l_count := l_count + 1;
1445 x_types(l_count) :=
1446 add_tree_node(cn_api.get_lkup_meaning('RATE_TABLE_RESULT',
1447 'EXPRESSION_TYPE'),
1448 cn_api.get_lkup_meaning('RATE_TABLE_RESULT',
1449 'EXPRESSION_TYPE'),
1450 'OTHERS', 'RateResult');
1451 l_count := l_count + 1;
1452 x_types(l_count) :=
1453 add_tree_node(cn_api.get_lkup_meaning('FORECAST_AMOUNT',
1454 'EXPRESSION_TYPE'),
1455 cn_api.get_lkup_meaning('FORECAST_AMOUNT',
1456 'EXPRESSION_TYPE'),
1457 'OTHERS', 'ForecastAmount');
1458 l_count := l_count + 1;
1459 END get_type_tree; */
1460
1461 -- parse a sql select statement looking for included plan elements
1462 -- of the form (1234PE.COLUMN_NAME). if any are found, include them in
1463 -- the x_plan_elt_tbl and provide a parsed version of the sql select.
1464 PROCEDURE parse_plan_elements(
1465 p_sql_select IN VARCHAR2
1466 , x_plan_elt_tbl OUT NOCOPY num_tbl_type
1467 , x_parsed_sql_select OUT NOCOPY VARCHAR2
1468 ) IS
1469 s VARCHAR2(1); -- character before 'PE'
1470 pe VARCHAR2(30); -- plan element ID
1471 i NUMBER := 0; -- index vars
1472 ix NUMBER;
1473 openpar NUMBER; -- looking for parenthesis
1474 clspar NUMBER;
1475 CONTINUE BOOLEAN := TRUE;
1476 BEGIN
1477 ix := 0;
1478 x_parsed_sql_select := p_sql_select;
1479
1480 WHILE CONTINUE LOOP
1481 i := INSTR(x_parsed_sql_select, 'PE.', i + 1);
1482
1483 IF i = 0 THEN
1487 -- if so then it's a plan element
1484 CONTINUE := FALSE;
1485 ELSE
1486 -- see if character before 'PE' is a number...
1488 s := SUBSTR(x_parsed_sql_select, i - 1, 1);
1489
1490 IF s BETWEEN '0' AND '9' THEN
1491 -- get surrounding parenthesis
1492 openpar := INSTR(x_parsed_sql_select, '(', i - LENGTH(x_parsed_sql_select));
1493 clspar := INSTR(x_parsed_sql_select, ')', i);
1494 pe := SUBSTR(x_parsed_sql_select, openpar + 1, i - openpar - 1);
1495 x_parsed_sql_select :=
1496 SUBSTR(x_parsed_sql_select, 1, openpar) || '0'
1497 || SUBSTR(x_parsed_sql_select, clspar);
1498 ix := ix + 1;
1499 x_plan_elt_tbl(ix) := pe;
1500 END IF;
1501 END IF;
1502 END LOOP;
1503 END parse_plan_elements;
1504
1505 -- private procedure used in get_dependent_plan_elts
1506 PROCEDURE dfs(
1507 p_original_node_type VARCHAR2
1508 , p_original_node_id NUMBER
1509 , p_node_type VARCHAR2
1510 , p_current_id NUMBER
1511 , p_level NUMBER
1512 , p_pe_arr IN OUT NOCOPY num_tbl_type
1513 ) IS
1514 CURSOR get_formula_id IS
1515 SELECT calc_formula_id
1516 FROM cn_quotas_v
1517 WHERE quota_id = p_current_id;
1518
1519 CURSOR get_exp_ids IS
1520 SELECT ccse.calc_sql_exp_id
1521 FROM cn_calc_sql_exps ccse, cn_calc_formulas ccf, cn_formula_inputs cfi
1522 WHERE (
1523 (ccse.calc_sql_exp_id = ccf.perf_measure_id)
1524 OR (ccse.calc_sql_exp_id = ccf.output_exp_id)
1525 OR (ccse.calc_sql_exp_id = cfi.calc_sql_exp_id)
1526 OR (ccse.calc_sql_exp_id = cfi.f_calc_sql_exp_id)
1527 )
1528 AND cfi.calc_formula_id = ccf.calc_formula_id
1529 AND ccf.calc_formula_id = p_current_id;
1530
1531 CURSOR get_child_edges IS
1532 SELECT child_id
1533 FROM cn_calc_edges
1534 WHERE edge_type = 'FE' AND parent_id = p_current_id;
1535
1536 CURSOR get_sql_sel IS
1537 SELECT DBMS_LOB.SUBSTR(sql_select)
1538 FROM cn_calc_sql_exps
1539 WHERE calc_sql_exp_id = p_current_id;
1540
1541 l_current_id NUMBER;
1542 l_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
1543 l_sql_sel VARCHAR2(4000);
1544 l_junk VARCHAR2(4000);
1545 BEGIN
1546 IF p_node_type = p_original_node_type AND p_current_id = p_original_node_id AND p_level > 0 THEN
1547 fnd_message.set_name('CN', 'CN_PE_CANNOT_REF_ITSEF');
1548 fnd_msg_pub.ADD;
1549 RAISE fnd_api.g_exc_error;
1550 END IF;
1551
1552 IF p_node_type = 'P' THEN
1553 IF p_level > 0 THEN
1554 -- don't return the root as a dependence
1555 p_pe_arr(p_pe_arr.COUNT) := p_current_id;
1556 END IF;
1557
1558 l_current_id := NULL;
1559
1560 OPEN get_formula_id;
1561 FETCH get_formula_id INTO l_current_id;
1562 CLOSE get_formula_id;
1563
1564 IF l_current_id IS NOT NULL THEN
1565 dfs(p_original_node_type, p_original_node_id, 'F', l_current_id, p_level + 1, p_pe_arr);
1566 END IF;
1567 ELSIF p_node_type = 'F' THEN
1568 FOR x IN get_exp_ids LOOP
1569 dfs(p_original_node_type, p_original_node_id, 'E', x.calc_sql_exp_id, p_level + 1, p_pe_arr);
1570 END LOOP;
1571 ELSIF p_node_type = 'E' THEN
1572 OPEN get_sql_sel;
1573 FETCH get_sql_sel INTO l_sql_sel;
1574 CLOSE get_sql_sel;
1575
1576 cn_calc_sql_exps_pvt.parse_plan_elements(l_sql_sel, l_pe_tbl, l_junk);
1577
1578 FOR x IN 1 .. l_pe_tbl.COUNT LOOP
1579 dfs(p_original_node_type, p_original_node_id, 'P', l_pe_tbl(x), p_level + 1, p_pe_arr);
1580 END LOOP;
1581
1582 FOR x IN get_child_edges LOOP
1583 dfs(p_original_node_type, p_original_node_id, 'F', x.child_id, p_level + 1, p_pe_arr);
1584 END LOOP;
1585 END IF;
1586 END dfs;
1587
1588 -- given a plan element, formula, or expression, determine all the plan
1589 -- elements referenced directly or indirectly
1590 -- pass in a node type (formula=F, plan element=P, expression=E), and the ID
1591 PROCEDURE get_dependent_plan_elts(
1592 p_api_version IN NUMBER
1593 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1594 , p_commit IN VARCHAR2 := fnd_api.g_false
1595 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1596 , p_node_type IN VARCHAR2
1597 , p_node_id IN NUMBER
1598 , x_plan_elt_id_tbl OUT NOCOPY num_tbl_type
1599 , x_return_status OUT NOCOPY VARCHAR2
1600 , x_msg_count OUT NOCOPY NUMBER
1601 , x_msg_data OUT NOCOPY VARCHAR2
1602 ) IS
1603 l_api_name CONSTANT VARCHAR2(30) := 'get_dependent_plan_elts';
1604 l_api_version CONSTANT NUMBER := 1.0;
1605 BEGIN
1606 -- Standard call to check for call compatibility.
1607 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1608 RAISE fnd_api.g_exc_unexpected_error;
1609 END IF;
1610
1611 -- Initialize message list if p_init_msg_list is set to TRUE.
1612 IF fnd_api.to_boolean(p_init_msg_list) THEN
1613 fnd_msg_pub.initialize;
1614 END IF;
1615
1616 -- Initialize API return status to success
1617 x_return_status := fnd_api.g_ret_sts_success;
1618 -- API body
1619 dfs(p_node_type, p_node_id, p_node_type, p_node_id, 0, x_plan_elt_id_tbl);
1620
1621 -- Standard check of p_commit.
1622 IF fnd_api.to_boolean(p_commit) THEN
1626 -- Standard call to get message count and if count is 1, get message info.
1623 COMMIT WORK;
1624 END IF;
1625
1627 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
1628 , p_encoded => fnd_api.g_false);
1629 EXCEPTION
1630 WHEN fnd_api.g_exc_error THEN
1631 x_return_status := fnd_api.g_ret_sts_error;
1632 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
1633 , p_encoded => fnd_api.g_false);
1634 WHEN fnd_api.g_exc_unexpected_error THEN
1635 x_return_status := fnd_api.g_ret_sts_unexp_error;
1636 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
1637 , p_encoded => fnd_api.g_false);
1638 WHEN OTHERS THEN
1639 x_return_status := fnd_api.g_ret_sts_unexp_error;
1640
1641 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1642 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1643 END IF;
1644
1645 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
1646 , p_encoded => fnd_api.g_false);
1647 END get_dependent_plan_elts;
1648
1649 -- private procedure used in get_parent_plan_elts
1650 PROCEDURE dfs2(
1651 p_original_node_type VARCHAR2
1652 , p_original_node_id NUMBER
1653 , p_node_type VARCHAR2
1654 , p_current_id NUMBER
1655 , p_level NUMBER
1656 , p_pe_arr IN OUT NOCOPY num_tbl_type
1657 ) IS
1658 CURSOR get_quota_ids IS
1659 SELECT quota_id
1660 FROM cn_quotas_v
1661 WHERE calc_formula_id = p_current_id;
1662
1663 CURSOR get_exp_ids IS
1664 SELECT calc_sql_exp_id
1665 FROM cn_calc_sql_exps
1666 WHERE DBMS_LOB.SUBSTR(sql_select) LIKE '%(' || p_current_id || 'PE.%';
1667
1668 CURSOR get_formulas IS
1669 SELECT calc_formula_id
1670 FROM cn_formula_inputs
1671 WHERE calc_sql_exp_id = p_current_id OR f_calc_sql_exp_id = p_current_id
1672 UNION ALL
1673 SELECT calc_formula_id
1674 FROM cn_calc_formulas
1675 WHERE output_exp_id = p_current_id
1676 OR f_output_exp_id = p_current_id
1677 OR perf_measure_id = p_current_id;
1678
1679 CURSOR get_parent_exps IS
1680 SELECT parent_id exp_id
1681 FROM cn_calc_edges
1682 WHERE edge_type = 'FE' AND child_id = p_current_id;
1683
1684 l_current_id NUMBER;
1685 l_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
1686 BEGIN
1687 IF p_node_type = p_original_node_type AND p_current_id = p_original_node_id AND p_level > 0 THEN
1688 fnd_message.set_name('CN', 'CN_PE_CANNOT_REF_ITSEF');
1689 fnd_msg_pub.ADD;
1690 RAISE fnd_api.g_exc_error;
1691 END IF;
1692
1693 IF p_node_type = g_node_type AND
1694 p_original_node_type = g_original_node_type AND
1695 p_current_id = g_current_id AND
1696 p_original_node_id = g_original_node_id AND
1697 p_level = g_level THEN
1698 p_pe_arr := g_pe_arr;
1699 ELSE
1700 IF p_node_type = 'P' THEN
1701 -- Don't return the root as a dependence
1702 IF p_level > 0 THEN
1703 -- Dont add Duplicate Entries
1704 FOR i IN 0..p_pe_arr.COUNT LOOP
1705 -- If we have reached the end of the table, then we can add our element
1706 IF i = p_pe_arr.COUNT THEN
1707 p_pe_arr(p_pe_arr.COUNT) := p_current_id;
1708 EXIT;
1709 END IF;
1710
1711 EXIT WHEN p_pe_arr(i) = p_current_id;
1712 END LOOP;
1713 END IF;
1714
1715 FOR x IN get_exp_ids LOOP
1716 dfs2(p_original_node_type, p_original_node_id, 'E', x.calc_sql_exp_id, p_level + 1, p_pe_arr);
1717 END LOOP;
1718 ELSIF p_node_type = 'E' THEN
1719 FOR f IN get_formulas LOOP
1720 dfs2(p_original_node_type, p_original_node_id, 'F', f.calc_formula_id, p_level + 1, p_pe_arr);
1721 END LOOP;
1722 ELSIF p_node_type = 'F' THEN
1723 FOR x IN get_parent_exps LOOP
1724 dfs2(p_original_node_type, p_original_node_id, 'E', x.exp_id, p_level + 1, p_pe_arr);
1725 END LOOP;
1726
1727 FOR x IN get_quota_ids LOOP
1728 dfs2(p_original_node_type, p_original_node_id, 'P', x.quota_id, p_level + 1, p_pe_arr);
1729 END LOOP;
1730 END IF;
1731 g_original_node_type := p_original_node_type;
1732 g_original_node_id := p_original_node_id;
1733 g_node_type := p_node_type;
1734 g_current_id := p_current_id;
1735 g_level := p_level;
1736 g_pe_arr := p_pe_arr;
1737 END IF;
1738 END dfs2;
1739
1740 -- given a plan element, formula, or expression, determine all the plan
1741 -- elements that reference it directly or indirectly
1742 -- pass in a node type (formula=F, plan element=P, expression=E), and the ID
1743 PROCEDURE get_parent_plan_elts(
1744 p_api_version IN NUMBER
1745 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1746 , p_commit IN VARCHAR2 := fnd_api.g_false
1747 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1748 , p_node_type IN VARCHAR2
1749 , p_node_id IN NUMBER
1750 , x_plan_elt_id_tbl OUT NOCOPY num_tbl_type
1751 , x_return_status OUT NOCOPY VARCHAR2
1752 , x_msg_count OUT NOCOPY NUMBER
1753 , x_msg_data OUT NOCOPY VARCHAR2
1754 ) IS
1755 l_api_name CONSTANT VARCHAR2(30) := 'get_parent_plan_elts';
1756 l_api_version CONSTANT NUMBER := 1.0;
1757 BEGIN
1761 END IF;
1758 -- Standard call to check for call compatibility.
1759 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1760 RAISE fnd_api.g_exc_unexpected_error;
1762
1763 -- Initialize message list if p_init_msg_list is set to TRUE.
1764 IF fnd_api.to_boolean(p_init_msg_list) THEN
1765 fnd_msg_pub.initialize;
1766 END IF;
1767
1768 -- Initialize API return status to success
1769 x_return_status := fnd_api.g_ret_sts_success;
1770 -- API body
1771 dfs2(p_node_type, p_node_id, p_node_type, p_node_id, 0, x_plan_elt_id_tbl);
1772
1773 -- Standard check of p_commit.
1774 IF fnd_api.to_boolean(p_commit) THEN
1775 COMMIT WORK;
1776 END IF;
1777
1778 -- Standard call to get message count and if count is 1, get message info.
1779 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
1780 , p_encoded => fnd_api.g_false);
1781 EXCEPTION
1782 WHEN fnd_api.g_exc_error THEN
1783 x_return_status := fnd_api.g_ret_sts_error;
1784 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
1785 , p_encoded => fnd_api.g_false);
1786 WHEN fnd_api.g_exc_unexpected_error THEN
1787 x_return_status := fnd_api.g_ret_sts_unexp_error;
1788 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
1789 , p_encoded => fnd_api.g_false);
1790 WHEN OTHERS THEN
1791 x_return_status := fnd_api.g_ret_sts_unexp_error;
1792
1793 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1794 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1795 END IF;
1796
1797 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
1798 , p_encoded => fnd_api.g_false);
1799 END get_parent_plan_elts;
1800
1801 PROCEDURE parse_sql_select(
1802 p_api_version IN NUMBER
1803 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1804 , p_commit IN VARCHAR2 := fnd_api.g_false
1805 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1806 , p_sql_select IN OUT NOCOPY VARCHAR2
1807 , x_piped_sql_select OUT NOCOPY VARCHAR2
1808 , x_expr_disp OUT NOCOPY VARCHAR2
1809 , x_piped_expr_disp OUT NOCOPY VARCHAR2
1810 , x_sql_from OUT NOCOPY VARCHAR2
1811 , x_piped_sql_from OUT NOCOPY VARCHAR2
1812 , x_return_status OUT NOCOPY VARCHAR2
1813 , x_msg_count OUT NOCOPY NUMBER
1814 , x_msg_data OUT NOCOPY VARCHAR2
1815 ) IS
1816 l_sql_select_left VARCHAR2(4000) := p_sql_select;
1817 l_ix NUMBER;
1818 l_seg VARCHAR2(4000);
1819 l_ix2 NUMBER;
1820 l_seg2 VARCHAR2(4000);
1821 l_disp_seg VARCHAR2(4000);
1822 l_table_id NUMBER;
1823 l_table_name VARCHAR2(80);
1824
1825 TYPE vt IS TABLE OF VARCHAR2(80);
1826
1827 sel_pieces vt
1828 := vt(
1829 'RateResult'
1830 , 'ForecastAmount'
1831 , 'ABS('
1832 , 'CEIL('
1833 , 'EXP('
1834 , 'FLOOR('
1835 , 'GREATEST('
1836 , 'LEAST('
1837 , 'MOD('
1838 , 'POWER('
1839 , 'ROUND('
1840 , 'SIGN('
1841 , 'SQRT('
1842 , 'TO_NUMBER('
1843 , 'TRUNC('
1844 , 'AVG('
1845 , 'COUNT('
1846 , 'MAX('
1847 , 'MIN('
1848 , 'STDDEV('
1849 , 'SUM('
1850 , 'VARIANCE('
1851 , 'DECODE('
1852 , 'NVL('
1853 , '*'
1854 , '/'
1855 , '.'
1856 , '-'
1857 , '+'
1858 , ','
1859 , ')'
1860 , '('
1861 );
1862 disp_pieces vt := sel_pieces; -- almost the same
1863 opers vt := vt('/', '+', '*', '-', ' ', ',', ')');
1864 ct NUMBER := 0;
1865 success BOOLEAN;
1866 found_num BOOLEAN;
1867 l_api_name CONSTANT VARCHAR2(30) := 'parse_sql_select';
1868 l_api_version CONSTANT NUMBER := 1.0;
1869
1870 CURSOR get_formula_name(l_segment IN VARCHAR2) IS
1871 SELECT NAME
1872 FROM cn_calc_formulas
1873 WHERE 'cn_formula_'
1874 || calc_formula_id
1875 || '_'
1876 || org_id
1877 || '_pkg.get_result(p_commission_line_id)' = l_segment;
1878
1879 CURSOR get_pe_name(l_segment IN VARCHAR2) IS
1880 SELECT NAME
1881 FROM cn_quotas_v
1882 WHERE quota_id || 'PE' = l_segment;
1883
1887 , NAME
1884 CURSOR get_tbl(l_segment IN VARCHAR2) IS
1885 SELECT user_name
1886 , object_id
1888 FROM cn_objects
1889 WHERE calc_eligible_flag = 'Y'
1890 AND object_type IN('TBL', 'VIEW')
1891 AND user_name IS NOT NULL
1892 AND alias = l_segment;
1893
1894 CURSOR get_col(l_segment IN VARCHAR2, l_table_id IN NUMBER) IS
1895 SELECT user_name
1896 FROM cn_objects
1897 WHERE table_id = l_table_id
1898 AND calc_formula_flag = 'Y'
1899 AND object_type = 'COL'
1900 AND user_name IS NOT NULL
1901 AND NAME = l_segment;
1902
1903 CURSOR get_user_funcs IS
1904 SELECT object_name
1905 FROM user_objects
1906 WHERE object_type = 'FUNCTION' AND status = 'VALID';
1907 BEGIN
1908 -- Standard call to check for call compatibility.
1909 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1910 RAISE fnd_api.g_exc_unexpected_error;
1911 END IF;
1912
1913 -- Initialize message list if p_init_msg_list is set to TRUE.
1914 IF fnd_api.to_boolean(p_init_msg_list) THEN
1915 fnd_msg_pub.initialize;
1916 END IF;
1917
1918 -- Initialize API return status to success
1919 x_return_status := fnd_api.g_ret_sts_success;
1920 -- translate RateResult and ForecastAmount
1921 disp_pieces(1) := cn_api.get_lkup_meaning('RATE_TABLE_RESULT', 'EXPRESSION_TYPE');
1922 disp_pieces(2) := cn_api.get_lkup_meaning('FORECAST_AMOUNT', 'EXPRESSION_TYPE');
1923
1924 -- Bug 2295522
1925 -- set p_sql_select to upper
1926 SELECT UPPER(p_sql_select)
1927 INTO l_sql_select_left
1928 FROM DUAL;
1929
1930 -- next build piped sql select
1931 LOOP
1935 -- look for plan element
1932 ct := ct + 1; -- defend against infinite loop
1933 success := FALSE;
1934
1936 IF SUBSTR(l_sql_select_left, 1, 1) = '(' THEN
1937 -- get close parenthesis
1938 l_ix := INSTR(l_sql_select_left, '.');
1939 l_seg := SUBSTR(l_sql_select_left, 2, l_ix - 2);
1940 l_ix2 := INSTR(l_sql_select_left, ')');
1941 l_seg2 := SUBSTR(l_sql_select_left, l_ix + 1, l_ix2 - l_ix - 1);
1942 l_disp_seg := NULL;
1943
1944 OPEN get_pe_name(l_seg); -- get display name of PE
1945 FETCH get_pe_name INTO l_disp_seg;
1946 CLOSE get_pe_name;
1947
1948 IF l_disp_seg IS NOT NULL THEN
1949 l_sql_select_left := SUBSTR(l_sql_select_left, l_ix2 + 1);
1950 x_piped_sql_select := x_piped_sql_select || '(' || l_seg || '.' || l_seg2 || ')|';
1951 x_piped_expr_disp := x_piped_expr_disp || l_disp_seg || '.' || l_seg2 || '|';
1952 success := TRUE;
1953 END IF;
1954 END IF;
1955
1956 -- look for quoted constant
1957 IF SUBSTR(l_sql_select_left, 1, 1) = '''' AND success = FALSE THEN
1958 -- get close quote
1959 l_ix := INSTR(l_sql_select_left, '''', 2);
1960
1961 IF l_ix = 0 THEN
1962 fnd_message.set_name('CN', 'CN_SQL_SELECT_PARSE_ERR');
1963 fnd_msg_pub.ADD;
1964 RAISE fnd_api.g_exc_error;
1965 END IF;
1966
1967 x_piped_sql_select := x_piped_sql_select || SUBSTR(l_sql_select_left, 1, l_ix) || '|';
1968 x_piped_expr_disp := x_piped_expr_disp || SUBSTR(l_sql_select_left, 1, l_ix) || '|';
1969 l_sql_select_left := SUBSTR(l_sql_select_left, l_ix + 1);
1970 success := TRUE;
1971 END IF;
1972
1973 -- look for numeric value
1974 IF success = FALSE THEN
1975 found_num := FALSE;
1976
1977 WHILE SUBSTR(l_sql_select_left, 1, 1) BETWEEN '0' AND '9'
1978 OR SUBSTR(l_sql_select_left, 1, 1) = '.' LOOP
1979 x_piped_sql_select := x_piped_sql_select || SUBSTR(l_sql_select_left, 1, 1);
1980 x_piped_expr_disp := x_piped_expr_disp || SUBSTR(l_sql_select_left, 1, 1);
1981 l_sql_select_left := SUBSTR(l_sql_select_left, 2);
1982 found_num := TRUE;
1983 success := TRUE;
1984 END LOOP;
1985
1986 IF found_num THEN
1987 x_piped_expr_disp := x_piped_expr_disp || '|';
1988 x_piped_sql_select := x_piped_sql_select || '|';
1989 END IF;
1990 END IF;
1991
1992 -- look for canned value
1993 IF success = FALSE THEN
1994 FOR i IN 1 .. sel_pieces.COUNT LOOP
1995 IF SUBSTR(l_sql_select_left, 1, LENGTH(sel_pieces(i))) = UPPER(sel_pieces(i)) THEN
1996 l_sql_select_left := SUBSTR(l_sql_select_left, LENGTH(sel_pieces(i)) + 1);
1997 x_piped_sql_select := x_piped_sql_select || sel_pieces(i) || '|';
1998 x_piped_expr_disp := x_piped_expr_disp || disp_pieces(i) || '|';
1999 success := TRUE;
2000 EXIT;
2001 END IF;
2002 END LOOP;
2003 END IF;
2004
2005 -- look for formula value
2006 IF success = FALSE AND SUBSTR(l_sql_select_left, 1, 10) = 'cn_formula' THEN
2007 -- look for p_commission_line_id
2008 l_ix := INSTR(l_sql_select_left, 'p_commission_line_id');
2009 l_seg := SUBSTR(l_sql_select_left, 1, l_ix + 20);
2010 l_sql_select_left := SUBSTR(l_sql_select_left, l_ix + 21);
2011 x_piped_sql_select := x_piped_sql_select || l_seg || '|';
2012
2013 OPEN get_formula_name(l_seg);
2014 FETCH get_formula_name INTO l_seg;
2015 CLOSE get_formula_name;
2016
2017 x_piped_expr_disp := x_piped_expr_disp || l_seg || '|';
2018 success := TRUE;
2019 END IF;
2020
2021 -- look for user-defined function
2022 IF success = FALSE THEN
2023 FOR f IN get_user_funcs LOOP
2024 IF SUBSTR(l_sql_select_left, 1, LENGTH(f.object_name) + 1) = UPPER(f.object_name) || '(' THEN
2025 -- found a function
2026 x_piped_sql_select := x_piped_sql_select || f.object_name || '(|';
2027 x_piped_expr_disp := x_piped_expr_disp || f.object_name || '(|';
2028 l_sql_select_left := SUBSTR(l_sql_select_left, LENGTH(f.object_name) + 2);
2029 success := TRUE;
2030 END IF;
2031 END LOOP;
2032 END IF;
2033
2034 -- trim spaces
2035 IF success = FALSE AND SUBSTR(l_sql_select_left, 1, 1) = ' ' THEN
2036 l_sql_select_left := SUBSTR(l_sql_select_left, 2);
2037 success := TRUE;
2038 END IF;
2039
2040 -- now look for elements like [something].[something else]
2041 IF success = FALSE AND l_sql_select_left IS NOT NULL THEN
2042 -- look for dot and table alias
2043 l_ix := INSTR(l_sql_select_left, '.');
2044 l_seg := SUBSTR(l_sql_select_left, 1, l_ix - 1); -- the alias
2045 l_disp_seg := NULL;
2046
2047 OPEN get_tbl(l_seg);
2048 FETCH get_tbl INTO l_disp_seg, l_table_id, l_table_name;
2049 CLOSE get_tbl;
2050
2051 IF l_disp_seg IS NULL THEN
2052 fnd_message.set_name('CN', 'CN_SQL_SELECT_PARSE_ERR');
2053 fnd_msg_pub.ADD;
2054 RAISE fnd_api.g_exc_error;
2055 END IF;
2056
2057 -- add to sql from
2058 IF (x_piped_sql_from IS NULL OR INSTR(x_piped_sql_from, l_table_name) = 0) THEN
2059 x_piped_sql_from := x_piped_sql_from || l_table_name || ' ' || l_seg || '|'; -- don't include the same table twice
2060 END IF;
2061
2065 l_ix := LENGTH(l_sql_select_left) + 1;
2062 x_piped_sql_select := x_piped_sql_select || l_seg;
2063 x_piped_expr_disp := x_piped_expr_disp || l_disp_seg;
2064 l_sql_select_left := SUBSTR(l_sql_select_left, l_ix + 1);
2066
2067 FOR c IN 1 .. opers.COUNT LOOP
2068 IF INSTR(l_sql_select_left, opers(c)) BETWEEN 1 AND l_ix THEN
2069 l_ix := INSTR(l_sql_select_left, opers(c));
2070 END IF;
2071 END LOOP;
2072
2073 l_seg := SUBSTR(l_sql_select_left, 1, l_ix - 1);
2074 l_disp_seg := NULL;
2075
2076 OPEN get_col(l_seg, l_table_id);
2077 FETCH get_col INTO l_disp_seg;
2078 CLOSE get_col;
2079
2080 IF l_disp_seg IS NULL THEN
2081 fnd_message.set_name('CN', 'CN_SQL_SELECT_PARSE_ERR');
2082 fnd_msg_pub.ADD;
2083 RAISE fnd_api.g_exc_error;
2084 END IF;
2085
2086 x_piped_sql_select := x_piped_sql_select || '.' || l_seg || '|';
2087 x_piped_expr_disp := x_piped_expr_disp || '.' || l_disp_seg || '|';
2088 l_sql_select_left := SUBSTR(l_sql_select_left, l_ix);
2089 success := TRUE;
2090 END IF;
2091
2092 IF ct = 400 THEN
2093 fnd_message.set_name('CN', 'CN_SQL_SELECT_PARSE_ERR');
2094 fnd_msg_pub.ADD;
2095 RAISE fnd_api.g_exc_unexpected_error;
2096 END IF;
2097
2098 IF success = FALSE THEN
2099 EXIT;
2100 END IF; -- we're done
2101 END LOOP;
2102
2103 x_expr_disp := REPLACE(x_piped_expr_disp, '|', '');
2104 p_sql_select := REPLACE(x_piped_sql_select, '|', '');
2105
2106 IF x_piped_sql_from IS NULL THEN
2107 x_piped_sql_from := 'DUAL|';
2108 END IF;
2109
2110 x_sql_from := REPLACE(SUBSTR(x_piped_sql_from, 1, LENGTH(x_piped_sql_from) - 1), '|'
2111 , ', '); -- trim last comma
2112
2113 -- Standard check of p_commit.
2114 IF fnd_api.to_boolean(p_commit) THEN
2115 COMMIT WORK;
2116 END IF;
2117
2118 -- Standard call to get message count and if count is 1, get message info.
2119 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
2120 , p_encoded => fnd_api.g_false);
2121 EXCEPTION
2122 WHEN fnd_api.g_exc_error THEN
2123 x_return_status := fnd_api.g_ret_sts_error;
2124 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
2125 , p_encoded => fnd_api.g_false);
2126 WHEN fnd_api.g_exc_unexpected_error THEN
2127 x_return_status := fnd_api.g_ret_sts_unexp_error;
2128 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
2129 , p_encoded => fnd_api.g_false);
2130 WHEN OTHERS THEN
2131 x_return_status := fnd_api.g_ret_sts_unexp_error;
2132
2133 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2134 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2135 END IF;
2136
2137 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
2138 , p_encoded => fnd_api.g_false);
2139 END parse_sql_select;
2140
2141 PROCEDURE import(
2142 errbuf OUT NOCOPY VARCHAR2
2143 , retcode OUT NOCOPY VARCHAR2
2144 , p_imp_header_id IN NUMBER
2145 , p_org_id IN NUMBER
2146 ) IS
2147 l_api_name CONSTANT VARCHAR2(30) := 'import';
2148 l_status_code cn_imp_lines.status_code%TYPE := 'STAGE';
2149 l_imp_header cn_imp_headers_pvt.imp_headers_rec_type
2150 := cn_imp_headers_pvt.g_miss_imp_headers_rec;
2151 l_process_audit_id cn_process_audits.process_audit_id%TYPE;
2152 err_num NUMBER;
2153 l_msg_count NUMBER := 0;
2154 l_exp_id NUMBER;
2155 l_msg_data VARCHAR2(4000);
2156 l_processed_row NUMBER := 0;
2157 l_failed_row NUMBER := 0;
2158 l_message VARCHAR2(4000);
2159 l_app_sn VARCHAR2(30);
2160 l_error_code VARCHAR2(4000);
2161 l_header_list VARCHAR2(4000);
2162 l_sql_stmt VARCHAR2(4000);
2163 l_return_status VARCHAR2(1);
2164 l_type_code cn_calc_sql_exps.exp_type_code%TYPE;
2165 my_message VARCHAR2(4000);
2166 l_status VARCHAR2(30);
2167 l_sql_from VARCHAR2(4000);
2168 l_piped_sql_from VARCHAR2(4000);
2169 l_piped_sql_select VARCHAR2(4000);
2170 l_piped_expr_disp VARCHAR2(4000);
2171 l_expr_disp VARCHAR2(4000);
2172 l_object_version_number cn_calc_sql_exps.object_version_number%TYPE;
2173
2174 CURSOR get_api_recs IS
2175 SELECT *
2176 FROM cn_exp_api_imp_v
2177 WHERE imp_header_id = p_imp_header_id AND status_code = l_status_code;
2178
2179 l_api_rec get_api_recs%ROWTYPE;
2180 BEGIN
2181 retcode := 0;
2182 l_object_version_number := 0;
2183
2184 -- Get imp_header info
2185 SELECT NAME
2186 , status_code
2187 , server_flag
2188 , imp_map_id
2189 , source_column_num
2190 , import_type_code
2191 INTO l_imp_header.NAME
2192 , l_imp_header.status_code
2193 , l_imp_header.server_flag
2194 , l_imp_header.imp_map_id
2195 , l_imp_header.source_column_num
2196 , l_imp_header.import_type_code
2200 -- open process audit batch
2197 FROM cn_imp_headers
2198 WHERE imp_header_id = p_imp_header_id;
2199
2201 cn_message_pkg.begin_batch(
2202 x_process_type => l_imp_header.import_type_code
2203 , x_parent_proc_audit_id => p_imp_header_id
2204 , x_process_audit_id => l_process_audit_id
2205 , x_request_id => NULL
2206 , p_org_id => p_org_id
2207 );
2208 cn_message_pkg.WRITE(
2209 p_message_text => 'CALCEXPIMP: Start Transfer Data. imp_header_id = ' || TO_CHAR(p_imp_header_id)
2210 , p_message_type => 'MILESTONE'
2211 );
2212 -- Get source column name list and target column dynamic sql statement
2213 cn_import_pvt.build_error_rec(p_imp_header_id => p_imp_header_id
2214 , x_header_list => l_header_list, x_sql_stmt => l_sql_stmt);
2215
2216 OPEN get_api_recs;
2217
2218 LOOP
2219 FETCH get_api_recs INTO l_api_rec;
2220
2221 EXIT WHEN get_api_recs%NOTFOUND;
2222
2223 BEGIN
2224 l_processed_row := l_processed_row + 1;
2225 l_error_code := NULL; -- reset error code
2226 cn_message_pkg.WRITE(
2227 p_message_text => 'CALCEXPIMP:Record '
2228 || TO_CHAR(l_processed_row)
2229 || ' imp_line_id = '
2230 || TO_CHAR(l_api_rec.imp_line_id)
2231 , p_message_type => 'DEBUG'
2232 );
2233
2234 -- -------- Checking for all required fields ----------------- --
2235 -- Check required field
2236 IF l_api_rec.expression_name IS NULL OR l_api_rec.sql_select IS NULL THEN
2237 l_failed_row := l_failed_row + 1;
2238 l_error_code := 'CN_IMP_MISS_REQUIRED';
2239 l_message := fnd_message.get_string('CN', 'CN_IMP_MISS_REQUIRED');
2240 cn_import_pvt.update_imp_lines(
2241 p_imp_line_id => l_api_rec.imp_line_id
2242 , p_status_code => 'FAIL'
2243 , p_error_code => l_error_code
2244 );
2245 cn_import_pvt.update_imp_headers(
2246 p_imp_header_id => p_imp_header_id
2247 , p_status_code => 'IMPORT_FAIL'
2248 , p_failed_row => l_failed_row
2249 );
2250 cn_message_pkg.WRITE(
2251 p_message_text => 'Record ' || TO_CHAR(l_processed_row) || ':'
2252 || l_message
2253 , p_message_type => 'ERROR'
2254 );
2255 cn_import_pvt.write_error_rec(
2256 p_imp_header_id => p_imp_header_id
2257 , p_imp_line_id => l_api_rec.imp_line_id
2258 , p_header_list => l_header_list
2259 , p_sql_stmt => l_sql_stmt
2260 );
2261 retcode := 2;
2262 errbuf := l_message;
2263 GOTO end_loop;
2264 END IF;
2265
2266 -- build components of record
2267 parse_sql_select(
2268 p_api_version => 1.0
2269 , p_init_msg_list => fnd_api.g_true
2270 , p_sql_select => l_api_rec.sql_select
2271 , x_piped_sql_select => l_piped_sql_select
2272 , x_expr_disp => l_expr_disp
2273 , x_piped_expr_disp => l_piped_expr_disp
2274 , x_sql_from => l_sql_from
2275 , x_piped_sql_from => l_piped_sql_from
2276 , x_return_status => l_return_status
2277 , x_msg_count => l_msg_count
2278 , x_msg_data => l_msg_data
2279 );
2280
2281 IF l_return_status = fnd_api.g_ret_sts_success THEN
2282 -- do import here
2283 l_exp_id := NULL;
2284 create_expression(
2285 p_api_version => 1.0
2286 , p_init_msg_list => fnd_api.g_false
2287 , p_org_id => p_org_id
2288 , p_name => l_api_rec.expression_name
2289 , p_description => l_api_rec.description
2290 , p_expression_disp => l_expr_disp
2291 , p_sql_select => l_api_rec.sql_select
2292 , p_sql_from => l_sql_from
2293 , p_piped_expression_disp => l_piped_expr_disp
2294 , p_piped_sql_select => l_piped_sql_select
2295 , p_piped_sql_from => l_piped_sql_from
2296 , x_calc_sql_exp_id => l_exp_id
2297 , x_exp_type_code => l_type_code
2298 , x_status => l_status
2299 , x_return_status => l_return_status
2300 , x_msg_count => l_msg_count
2301 , x_msg_data => l_msg_data
2302 , x_object_version_number => l_object_version_number
2303 );
2304
2305 IF l_return_status = fnd_api.g_ret_sts_success THEN
2306 -- update attribute values appropriately since API doesn't
2307 -- handle flexfields
2308 UPDATE cn_calc_sql_exps
2309 SET attribute_category = l_api_rec.attribute_category
2310 , attribute1 = l_api_rec.attribute1
2311 , attribute2 = l_api_rec.attribute2
2312 , attribute3 = l_api_rec.attribute3
2313 , attribute4 = l_api_rec.attribute4
2314 , attribute5 = l_api_rec.attribute5
2315 , attribute6 = l_api_rec.attribute6
2316 , attribute7 = l_api_rec.attribute7
2320 , attribute11 = l_api_rec.attribute11
2317 , attribute8 = l_api_rec.attribute8
2318 , attribute9 = l_api_rec.attribute9
2319 , attribute10 = l_api_rec.attribute10
2321 , attribute12 = l_api_rec.attribute12
2322 , attribute13 = l_api_rec.attribute13
2323 , attribute14 = l_api_rec.attribute14
2324 , attribute15 = l_api_rec.attribute15
2325 WHERE calc_sql_exp_id = l_exp_id;
2326 END IF;
2327 END IF;
2328
2329 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2330 -- try to get correct message
2331 l_failed_row := l_failed_row + 1;
2332 my_message :=
2333 fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first
2334 , p_encoded => fnd_api.g_false);
2335
2336 WHILE(my_message IS NOT NULL) LOOP
2337 l_error_code := my_message;
2338 my_message := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
2339 END LOOP;
2340
2341 cn_import_pvt.update_imp_lines(
2342 p_imp_line_id => l_api_rec.imp_line_id
2343 , p_status_code => 'FAIL'
2344 , p_error_code => NULL
2345 , p_error_msg => NVL(l_error_code, 'Unexpected Error')
2346 );
2347 cn_import_pvt.update_imp_headers(
2348 p_imp_header_id => p_imp_header_id
2349 , p_status_code => 'IMPORT_FAIL'
2350 , p_failed_row => l_failed_row
2351 );
2352 cn_message_pkg.WRITE(
2353 p_message_text => 'Record ' || TO_CHAR(l_processed_row) || ':'
2354 || l_message
2355 , p_message_type => 'ERROR'
2356 );
2357 cn_import_pvt.write_error_rec(
2358 p_imp_header_id => p_imp_header_id
2359 , p_imp_line_id => l_api_rec.imp_line_id
2360 , p_header_list => l_header_list
2361 , p_sql_stmt => l_sql_stmt
2362 );
2363 retcode := 2;
2364 errbuf := l_message;
2365 GOTO end_loop;
2366 ELSE
2367 l_error_code := '';
2368 cn_import_pvt.update_imp_lines(
2369 p_imp_line_id => l_api_rec.imp_line_id
2370 , p_status_code => 'COMPLETE'
2371 , p_error_code => l_error_code
2372 );
2373 cn_message_pkg.WRITE(
2374 p_message_text => 'CALCEXPIMP:Import completed. exp id = '
2375 || TO_CHAR(l_exp_id)
2376 , p_message_type => 'DEBUG'
2377 );
2378 END IF;
2379
2380 <<end_loop>>
2381 -- update update_imp_headers:process_row
2382 cn_import_pvt.update_imp_headers(
2383 p_imp_header_id => p_imp_header_id
2384 , p_status_code => NULL
2385 , p_processed_row => l_processed_row
2386 );
2387 EXCEPTION
2388 WHEN OTHERS THEN
2389 l_failed_row := l_failed_row + 1;
2390 l_error_code := SQLCODE;
2391 l_message := SUBSTR(SQLERRM, 1, 2000);
2392 cn_import_pvt.update_imp_lines(
2393 p_imp_line_id => l_api_rec.imp_line_id
2394 , p_status_code => 'FAIL'
2395 , p_error_code => NULL
2396 , p_error_msg => l_message
2397 );
2398 cn_import_pvt.update_imp_headers(
2399 p_imp_header_id => p_imp_header_id
2400 , p_status_code => 'IMPORT_FAIL'
2401 , p_processed_row => l_processed_row
2402 , p_failed_row => l_failed_row
2403 );
2404 cn_message_pkg.WRITE(
2405 p_message_text => 'Record ' || TO_CHAR(l_processed_row) || ':'
2406 || l_message
2407 , p_message_type => 'ERROR'
2408 );
2409 cn_import_pvt.write_error_rec(
2410 p_imp_header_id => p_imp_header_id
2411 , p_imp_line_id => l_api_rec.imp_line_id
2412 , p_header_list => l_header_list
2413 , p_sql_stmt => l_sql_stmt
2414 );
2415 retcode := 2;
2416 errbuf := l_message;
2417 END;
2418 END LOOP; -- get_api_recs
2419
2420 IF get_api_recs%ROWCOUNT = 0 THEN
2421 l_processed_row := 0;
2422 END IF;
2423
2424 CLOSE get_api_recs;
2425
2426 IF l_failed_row = 0 AND retcode = 0 THEN
2427 -- update update_imp_headers
2428 cn_import_pvt.update_imp_headers(
2429 p_imp_header_id => p_imp_header_id
2430 , p_status_code => 'COMPLETE'
2431 , p_processed_row => l_processed_row
2432 , p_failed_row => l_failed_row
2433 );
2434 END IF;
2435
2436 cn_message_pkg.WRITE(
2437 p_message_text => 'CALCEXPIMP: End Transfer Data. imp_header_id = '
2438 || TO_CHAR(p_imp_header_id)
2439 , p_message_type => 'MILESTONE'
2440 );
2441 -- close process batch
2442 cn_message_pkg.end_batch(l_process_audit_id);
2443 -- Commit all imports
2444 COMMIT;
2445 EXCEPTION
2446 WHEN fnd_api.g_exc_unexpected_error THEN
2447 retcode := 2;
2451 WHEN OTHERS THEN
2448 cn_message_pkg.end_batch(l_process_audit_id);
2449 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => errbuf
2450 , p_encoded => fnd_api.g_false);
2452 err_num := SQLCODE;
2453
2454 IF err_num = -6501 THEN
2455 retcode := 2;
2456 errbuf := fnd_program.MESSAGE;
2457 ELSE
2458 retcode := 2;
2459
2460 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2461 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2462 END IF;
2463
2464 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => errbuf
2465 , p_encoded => fnd_api.g_false);
2466 END IF;
2467
2468 cn_message_pkg.set_error(l_api_name, errbuf);
2469 cn_message_pkg.end_batch(l_process_audit_id);
2470 END import;
2471
2472 -- export
2473 PROCEDURE export(
2474 errbuf OUT NOCOPY VARCHAR2
2475 , retcode OUT NOCOPY VARCHAR2
2476 , p_imp_header_id IN NUMBER
2477 , p_org_id IN NUMBER
2478 ) IS
2479 l_api_name CONSTANT VARCHAR2(30) := 'Export';
2480 l_process_audit_id cn_process_audits.process_audit_id%TYPE;
2481 l_return_status VARCHAR2(1);
2482 l_msg_data VARCHAR2(4000);
2483 l_msg_count NUMBER;
2484 l_col_names cn_import_pvt.char_data_set_type;
2485 l_data cn_import_pvt.char_data_set_type;
2486 l_rowcount NUMBER := 0;
2487 l_longcount NUMBER := 0;
2488 l_rec_num NUMBER := 0;
2489 l_message VARCHAR2(4000);
2490 l_name VARCHAR2(30);
2491 l_type VARCHAR2(30);
2492 l_view_name VARCHAR2(30);
2493 my_message VARCHAR2(4000);
2494 err_num NUMBER;
2495 g_max_field_length NUMBER := 150;
2496
2497 -- this is a workaround since you cannot declare arrays of a
2498 -- type declared remotely
2499 TYPE vt IS TABLE OF VARCHAR2(30);
2500
2501 l_col_names_tmp vt
2502 := vt(
2503 'RECORD_NUM'
2504 , 'EXPRESSION_NAME'
2505 , 'DESCRIPTION'
2506 , 'SQL_SELECT'
2507 , 'ATTRIBUTE_CATEGORY'
2508 , 'ATTRIBUTE1'
2509 , 'ATTRIBUTE2'
2510 , 'ATTRIBUTE3'
2511 , 'ATTRIBUTE4'
2512 , 'ATTRIBUTE5'
2513 , 'ATTRIBUTE6'
2514 , 'ATTRIBUTE7'
2515 , 'ATTRIBUTE8'
2516 , 'ATTRIBUTE9'
2517 , 'ATTRIBUTE10'
2518 , 'ATTRIBUTE11'
2519 , 'ATTRIBUTE12'
2520 , 'ATTRIBUTE13'
2521 , 'ATTRIBUTE14'
2522 , 'ATTRIBUTE15'
2523 );
2524
2525 CURSOR get_expressions IS
2526 SELECT NAME expression_name
2527 , description
2528 , DBMS_LOB.SUBSTR(sql_select, g_max_field_length) sql_select
2529 , attribute_category
2530 , attribute1
2531 , attribute2
2532 , attribute3
2533 , attribute4
2534 , attribute5
2535 , attribute6
2536 , attribute7
2537 , attribute8
2538 , attribute9
2539 , attribute10
2540 , attribute11
2541 , attribute12
2542 , attribute13
2543 , attribute14
2544 , attribute15
2545 FROM cn_calc_sql_exps
2546 WHERE org_id = p_org_id
2547 ORDER BY 1;
2548
2549 CURSOR get_rowcount IS
2550 SELECT COUNT(1)
2551 FROM cn_calc_sql_exps
2552 WHERE org_id = p_org_id;
2553
2554 CURSOR get_long_rowcount IS
2555 SELECT COUNT(1)
2556 FROM cn_calc_sql_exps
2557 WHERE DBMS_LOB.getlength(sql_select) > g_max_field_length AND org_id = p_org_id;
2558 BEGIN
2559 retcode := 0;
2560
2561 -- Get imp_header info
2562 SELECT h.NAME
2563 , h.import_type_code
2564 , t.view_name
2565 INTO l_name
2566 , l_type
2567 , l_view_name
2568 FROM cn_imp_headers h, cn_import_types t
2569 WHERE h.imp_header_id = p_imp_header_id AND t.import_type_code = h.import_type_code;
2570
2571 -- open process audit batch
2572 cn_message_pkg.begin_batch(
2573 x_process_type => l_type
2574 , x_parent_proc_audit_id => p_imp_header_id
2575 , x_process_audit_id => l_process_audit_id
2576 , x_request_id => NULL
2577 , p_org_id => p_org_id
2578 );
2579 cn_message_pkg.WRITE
2580 (
2581 p_message_text => 'CN_EXPCALCEXP: Start Transfer Data. imp_header_id = '
2582 || TO_CHAR(p_imp_header_id)
2583 , p_message_type => 'MILESTONE'
2584 );
2585
2586 -- API call here
2587 -- get column names
2588 FOR i IN 1 .. l_col_names_tmp.COUNT LOOP
2589 l_col_names(i) := l_col_names_tmp(i);
2590 END LOOP;
2591
2592 -- we have to get the rowcount first - since the data must be applied
2593 -- sequentially by column... indexes are like
2594 -- 1 n+1 ... 19n+1 (there are 20 columns)
2595 -- 2 n+2 ... 19n+2
2596 -- n 2n ... 20n
2597 OPEN get_rowcount;
2598 FETCH get_rowcount INTO l_rowcount;
2599 CLOSE get_rowcount;
2600
2601 OPEN get_long_rowcount;
2602 FETCH get_long_rowcount INTO l_longcount;
2606 FOR EXP IN get_expressions LOOP
2603 CLOSE get_long_rowcount;
2604
2605 -- now populate the data
2607 l_rec_num := l_rec_num + 1;
2608 l_data(l_rowcount * 0 + l_rec_num) := l_rec_num;
2609 l_data(l_rowcount * 1 + l_rec_num) := EXP.expression_name;
2610 l_data(l_rowcount * 2 + l_rec_num) := EXP.description;
2611 l_data(l_rowcount * 3 + l_rec_num) := EXP.sql_select;
2612 l_data(l_rowcount * 4 + l_rec_num) := EXP.attribute_category;
2613 l_data(l_rowcount * 5 + l_rec_num) := EXP.attribute1;
2614 l_data(l_rowcount * 6 + l_rec_num) := EXP.attribute2;
2615 l_data(l_rowcount * 7 + l_rec_num) := EXP.attribute3;
2616 l_data(l_rowcount * 8 + l_rec_num) := EXP.attribute4;
2617 l_data(l_rowcount * 9 + l_rec_num) := EXP.attribute5;
2618 l_data(l_rowcount * 10 + l_rec_num) := EXP.attribute6;
2619 l_data(l_rowcount * 11 + l_rec_num) := EXP.attribute7;
2620 l_data(l_rowcount * 12 + l_rec_num) := EXP.attribute8;
2621 l_data(l_rowcount * 13 + l_rec_num) := EXP.attribute9;
2622 l_data(l_rowcount * 14 + l_rec_num) := EXP.attribute10;
2623 l_data(l_rowcount * 15 + l_rec_num) := EXP.attribute11;
2624 l_data(l_rowcount * 16 + l_rec_num) := EXP.attribute12;
2625 l_data(l_rowcount * 17 + l_rec_num) := EXP.attribute13;
2626 l_data(l_rowcount * 18 + l_rec_num) := EXP.attribute14;
2627 l_data(l_rowcount * 19 + l_rec_num) := EXP.attribute15;
2628 END LOOP;
2629
2630 cn_import_client_pvt.insert_data(
2631 p_api_version => 1.0
2632 , p_imp_header_id => p_imp_header_id
2633 , p_import_type_code => l_type
2634 , p_table_name => l_view_name
2635 , p_col_names => l_col_names
2636 , p_data => l_data
2637 , p_row_count => l_rowcount
2638 , x_return_status => l_return_status
2639 , x_msg_count => l_msg_count
2640 , x_msg_data => l_msg_data
2641 );
2642
2643 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2644 cn_import_pvt.update_imp_headers(p_imp_header_id => p_imp_header_id, p_status_code => 'FAIL'
2645 , p_failed_row => l_rowcount);
2646 cn_message_pkg.WRITE(
2647 p_message_text => 'Export threw exception : rts sts ' || l_return_status
2648 , p_message_type => 'ERROR'
2649 );
2650 my_message := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
2651
2652 WHILE(my_message IS NOT NULL) LOOP
2653 l_message := l_message || my_message || '; ';
2654 my_message := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
2655 END LOOP;
2656
2657 cn_message_pkg.WRITE(p_message_text => l_message, p_message_type => 'ERROR');
2658 retcode := 2;
2659 errbuf := l_message;
2660 ELSE
2661 -- normal completion
2662 cn_import_pvt.update_imp_headers(
2663 p_imp_header_id => p_imp_header_id
2664 , p_status_code => 'COMPLETE'
2665 , p_processed_row => l_rowcount
2666 , p_staged_row => l_rowcount - l_longcount
2667 , p_failed_row => l_longcount
2668 );
2669
2670 -- set cn_imp_lines records status = 'COMPLETE'
2671 UPDATE cn_exp_api_imp_v
2672 SET status_code = 'COMPLETE'
2673 WHERE imp_header_id = p_imp_header_id;
2674
2675 -- set failed records - where expression was too long
2676 fnd_message.set_name('CN', 'CN_EXPORT_FIELD_TOO_LONG');
2677 fnd_message.set_token('LENGTH', g_max_field_length);
2678 my_message := fnd_message.get;
2679
2680 UPDATE cn_exp_api_imp_v
2681 SET status_code = 'FAIL'
2682 , error_msg = my_message
2683 WHERE imp_header_id = p_imp_header_id
2684 AND expression_name IN(SELECT NAME
2685 FROM cn_calc_sql_exps
2686 WHERE DBMS_LOB.getlength(sql_select) > g_max_field_length);
2687
2688 cn_message_pkg.WRITE
2689 (
2690 p_message_text => 'CN_EXPCALCEXP: End Transfer Data. imp_header_id = '
2691 || TO_CHAR(p_imp_header_id)
2692 , p_message_type => 'MILESTONE'
2693 );
2694 END IF;
2695
2696 -- close process batch
2697 cn_message_pkg.end_batch(l_process_audit_id);
2698 -- Commit all imports
2699 COMMIT;
2700 EXCEPTION
2701 WHEN fnd_api.g_exc_unexpected_error THEN
2702 retcode := 2;
2703 cn_message_pkg.end_batch(l_process_audit_id);
2704 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => errbuf
2705 , p_encoded => fnd_api.g_false);
2706 WHEN OTHERS THEN
2707 err_num := SQLCODE;
2708
2709 IF err_num = -6501 THEN
2710 retcode := 2;
2711 errbuf := fnd_program.MESSAGE;
2712 ELSE
2713 retcode := 2;
2714
2715 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2716 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2717 END IF;
2718
2719 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => errbuf
2720 , p_encoded => fnd_api.g_false);
2721 END IF;
2722
2723 cn_message_pkg.set_error(l_api_name, errbuf);
2724 cn_message_pkg.end_batch(l_process_audit_id);
2725 END export;
2726
2727 PROCEDURE duplicate_expression(
2728 p_api_version IN NUMBER
2729 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2730 , p_commit IN VARCHAR2 := fnd_api.g_false
2734 , x_new_expr_name OUT NOCOPY cn_calc_sql_exps.NAME%TYPE
2731 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2732 , p_old_expr_id IN NUMBER
2733 , x_new_expr_id OUT NOCOPY NUMBER
2735 , x_return_status OUT NOCOPY VARCHAR2
2736 , x_msg_count OUT NOCOPY NUMBER
2737 , x_msg_data OUT NOCOPY VARCHAR2
2738 ) IS
2739 l_api_name CONSTANT VARCHAR2(30) := 'Duplicate_Expression';
2740 l_api_version CONSTANT NUMBER := 1.0;
2741 l_org_id cn_calc_sql_exps.org_id%TYPE;
2742 l_description cn_calc_sql_exps.description%TYPE;
2743 l_expression_disp VARCHAR2(32767);
2744 l_sql_select VARCHAR2(32767);
2745 l_sql_from VARCHAR2(32767);
2746 l_piped_expression_disp VARCHAR2(32767);
2747 l_piped_sql_select VARCHAR2(32767);
2748 l_piped_sql_from VARCHAR2(32767);
2749 x_exp_type_code cn_calc_sql_exps.exp_type_code%TYPE;
2750 x_status cn_calc_sql_exps.status%TYPE;
2751 x_object_version_number cn_calc_sql_exps.object_version_number%TYPE;
2752 l_suffix VARCHAR2(10) := NULL;
2753 l_prefix VARCHAR2(10) := NULL;
2754 BEGIN
2755 -- Standard Start of API savepoint
2756 SAVEPOINT create_expression;
2757
2758 -- Standard call to check for call compatibility.
2759 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2760 RAISE fnd_api.g_exc_unexpected_error;
2761 END IF;
2762
2763 -- Initialize message list if p_init_msg_list is set to TRUE.
2764 IF fnd_api.to_boolean(p_init_msg_list) THEN
2765 fnd_msg_pub.initialize;
2766 END IF;
2767
2768 -- Initialize API return status to success
2769 x_return_status := fnd_api.g_ret_sts_success;
2770
2771 SELECT org_id
2772 , NAME
2773 , description
2774 , expression_disp
2775 , sql_select
2776 , sql_from
2777 , piped_expression_disp
2778 , piped_sql_select
2779 , piped_sql_from
2780 INTO l_org_id
2781 , x_new_expr_name
2782 , l_description
2783 , l_expression_disp
2784 , l_sql_select
2785 , l_sql_from
2786 , l_piped_expression_disp
2787 , l_piped_sql_select
2788 , l_piped_sql_from
2789 FROM cn_calc_sql_exps
2790 WHERE calc_sql_exp_id = p_old_expr_id;
2791
2792 -- x_new_expr_name := x_new_expr_name || '_2';
2793 cn_plancopy_util_pvt.get_unique_name_for_component(
2794 p_id => p_old_expr_id
2795 , p_org_id => l_org_id
2796 , p_type => 'EXPRESSION'
2797 , p_suffix => l_suffix
2798 , p_prefix => l_prefix
2799 , x_name => x_new_expr_name
2800 , x_return_status => x_return_status
2801 , x_msg_count => x_msg_count
2802 , x_msg_data => x_msg_data
2803 );
2804
2805 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
2806 RAISE fnd_api.g_exc_error;
2807 END IF;
2808
2809 create_expression(
2810 p_api_version => p_api_version
2811 , p_init_msg_list => p_init_msg_list
2812 , p_commit => p_commit
2813 , p_validation_level => p_validation_level
2814 , p_org_id => l_org_id
2815 , p_name => x_new_expr_name
2816 , p_description => l_description
2817 , p_expression_disp => l_expression_disp
2818 , p_sql_select => l_sql_select
2819 , p_sql_from => l_sql_from
2820 , p_piped_expression_disp => l_piped_expression_disp
2821 , p_piped_sql_select => l_piped_sql_select
2822 , p_piped_sql_from => l_piped_sql_from
2823 , x_calc_sql_exp_id => x_new_expr_id
2824 , x_exp_type_code => x_exp_type_code
2825 , x_status => x_status
2826 , x_return_status => x_return_status
2827 , x_msg_count => x_msg_count
2828 , x_msg_data => x_msg_data
2829 , x_object_version_number => x_object_version_number
2830 );
2831
2832 -- Standard check of p_commit.
2833 IF fnd_api.to_boolean(p_commit) THEN
2834 COMMIT WORK;
2835 END IF;
2836
2837 -- Standard call to get message count and if count is 1, get message info.
2838 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
2839 , p_encoded => fnd_api.g_false);
2840 EXCEPTION
2841 WHEN fnd_api.g_exc_error THEN
2842 ROLLBACK TO duplicate_expression;
2843 x_return_status := fnd_api.g_ret_sts_error;
2844 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
2845 , p_encoded => fnd_api.g_false);
2846 WHEN fnd_api.g_exc_unexpected_error THEN
2847 ROLLBACK TO duplicate_expression;
2848 x_return_status := fnd_api.g_ret_sts_unexp_error;
2849 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
2850 , p_encoded => fnd_api.g_false);
2851 WHEN OTHERS THEN
2852 ROLLBACK TO duplicate_expression;
2853 x_return_status := fnd_api.g_ret_sts_unexp_error;
2854
2855 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2856 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2857 END IF;
2858
2859 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data
2862 END cn_calc_sql_exps_pvt;
2860 , p_encoded => fnd_api.g_false);
2861 END duplicate_expression;