[Home] [Help]
PACKAGE BODY: APPS.CN_CALC_FORMULAS_PVT
Source
1 PACKAGE BODY CN_CALC_FORMULAS_PVT AS
2 /*$Header: cnvformb.pls 120.17 2006/06/20 01:02:42 jxsingh noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_CALC_FORMULAS_PVT';
5 g_end_of_time CONSTANT DATE := to_date('12-31-9999','MM-DD-YYYY');
6
7 -- validate formula name and the flag combinations
8 PROCEDURE validate_name_flags
9 (p_calc_formula_id IN CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE := NULL,
10 p_name IN CN_CALC_FORMULAS.NAME%TYPE,
11 p_formula_type IN CN_CALC_FORMULAS.FORMULA_TYPE%TYPE,
12 p_trx_group_code IN CN_CALC_FORMULAS.TRX_GROUP_CODE%TYPE,
13 p_number_dim IN CN_CALC_FORMULAS.NUMBER_DIM%TYPE,
14 p_cumulative_flag IN CN_CALC_FORMULAS.CUMULATIVE_FLAG%TYPE,
15 p_itd_flag IN CN_CALC_FORMULAS.ITD_FLAG%TYPE,
16 p_split_flag IN CN_CALC_FORMULAS.SPLIT_FLAG%TYPE,
17 p_threshold_all_tier_flag IN CN_CALC_FORMULAS.THRESHOLD_ALL_TIER_FLAG%TYPE,
18 p_perf_measure_id IN CN_CALC_FORMULAS.PERF_MEASURE_ID%TYPE,
19 --R12 MOAC Changes--Start
20 p_org_id IN CN_CALC_FORMULAS.ORG_ID%TYPE
21 --R12 MOAC Changes--End
22 ) IS
23
24 l_prompt cn_lookups.meaning%TYPE;
25 l_dummy NUMBER;
26
27 CURSOR formula_exists IS
28 SELECT 1
29 FROM cn_calc_formulas
30 WHERE name = p_name
31 AND (p_calc_formula_id IS NULL OR p_calc_formula_id <> calc_formula_id)
32 --R12 MOAC Changes--Start
33 AND org_id = p_org_id;
34 --R12 MOAC Changes--End
35
36 BEGIN
37 IF (p_name IS NULL) THEN
38 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
39 l_prompt := cn_api.get_lkup_meaning('FORMULA_NAME', 'CN_PROMPTS');
40 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
41 fnd_message.set_token('OBJ_NAME', l_prompt);
42 fnd_msg_pub.ADD;
43 END IF;
44 RAISE fnd_api.g_exc_error;
45 END IF;
46
47 OPEN formula_exists;
48 FETCH formula_exists INTO l_dummy;
49 CLOSE formula_exists;
50
51 IF (l_dummy = 1) THEN
52 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
53 fnd_message.set_name('CN', 'CN_NAME_NOT_UNIQUE');
54 fnd_msg_pub.ADD;
55 END IF;
56 RAISE fnd_api.g_exc_error;
57 END IF;
58
59 -- validate the combination of flags
60 /****
61 -- 1. make sure splitting across multiple inputs is not allowed
62 IF (p_split_flag IN ('Y', 'P') AND p_number_dim > 1) THEN
63 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
64 fnd_message.set_name('CN', 'CN_SPLIT_ONE_INPUT');
65 fnd_msg_pub.ADD;
66 END IF;
67 RAISE fnd_api.g_exc_error;
68 END IF;
69
70 -- 2. make sure accumulation along multiple dimensions is not allowed
71 IF (p_cumulative_flag = 'Y' AND p_number_dim > 1) THEN
72 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
73 fnd_message.set_name('CN', 'CN_CUMULATE_ONE_INPUT');
74 fnd_msg_pub.ADD;
75 END IF;
76 RAISE fnd_api.g_exc_error;
77 END IF;
78 ***/
79
80 -- 3. make sure group by always goes with cumulative = 'Y' and itd_flag = 'N'
81 IF (p_trx_group_code = 'GROUP' AND (p_cumulative_flag = 'N' OR p_itd_flag = 'Y')) THEN
82 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
83 fnd_message.set_name('CN', 'CN_GROUP_CONSTRAINT');
84 fnd_msg_pub.ADD;
85 END IF;
86 RAISE fnd_api.g_exc_error;
87 END IF;
88
89 -- 4. make sure itd_flag = 'Y' always goes with cumulative_flag = 'Y'
90 IF (p_itd_flag = 'Y' AND p_cumulative_flag = 'N') THEN
91 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
92 fnd_message.set_name('CN', 'CN_ITD_CUMULATIVE');
93 fnd_msg_pub.ADD;
94 END IF;
95 RAISE fnd_api.g_exc_error;
96 END IF;
97
98 -- 5. make sure BONUS type formulas always have trx_group_code =
99 -- 'INDIVIDUAL' amd cumulative_flag = 'N' and itd_flag = 'N'
100 IF (p_formula_type = 'B' AND (p_trx_group_code = 'GROUP' OR
101 p_itd_flag = 'Y' OR p_cumulative_flag = 'Y')) THEN
102 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
103 fnd_message.set_name('CN', 'CN_BONUS_CONSTRAINT');
104 fnd_msg_pub.ADD;
105 END IF;
106 RAISE fnd_api.g_exc_error;
107 END IF;
108
109 -- 6. make sure THRESHOLD formulas have the following flag setting:
110 -- trx_group_code = 'INDIVIDUAL' and cumulative_flag = 'Y',
111 -- itd_flag = 'N' and split_flag = 'N'
112 IF (p_threshold_all_tier_flag = 'Y' AND
113 (p_trx_group_code = 'GROUP' or p_cumulative_flag = 'N' or
114 p_split_flag = 'Y' OR p_formula_type = 'B')) THEN
115 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
116 fnd_message.set_name('CN', 'CN_THRESHOLD_CONSTRAINT');
117 fnd_msg_pub.ADD;
118 END IF;
119 RAISE fnd_api.g_exc_error;
120 END IF;
121
122 /*
123 -- 7. make sure that perf_measure_id is not null ... not required anymore
124 IF (p_perf_measure_id IS NULL) THEN
125 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
126 fnd_message.set_name('CN', 'CN_PERF_CANT_NULL');
127 fnd_msg_pub.ADD;
128 END IF;
129 RAISE fnd_api.g_exc_error;
130 END IF;
131 */
132
133 END validate_name_flags;
134
135 PROCEDURE check_planning_exp (p_calc_sql_exp_id IN CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE) IS
136 CURSOR get_sql_select is
137 select dbms_lob.substr(sql_select)
138 from cn_calc_sql_exps
139 where calc_sql_exp_id = p_calc_sql_exp_id;
140
141 l_sql varchar2(2000);
142 l_pos number := 1;
143 BEGIN
144 IF p_calc_sql_exp_id IS NULL THEN
145 RETURN;
146 END IF;
147
148 -- make sure only variables that exist in exps are:
149 -- CH.TRANSACTION_AMOUNT
150 -- CH.QUANTITY
151 -- CSQA.TARGET
152 -- CSQA.PAYMENT_AMOUNT.
153
154 -- grab SQL select clause... look through each component.
155 -- for each '.' (indicating variable), see if following character is not a number
156 -- then make sure the '.' is included in one of the four legal variables.
157 OPEN get_sql_select;
158 FETCH get_sql_select INTO l_sql;
159 CLOSE get_sql_select;
160
161 while (l_pos > 0) loop
162 l_pos := instr(l_sql, '.', l_pos);
163 if l_pos > 0 then
164 if (substr(l_sql, l_pos+1, 1) not between '0' and '9' ) AND
165 (substr(l_sql, l_pos-2, 21) <> 'CH.TRANSACTION_AMOUNT' ) AND
166 (substr(l_sql, l_pos-2, 11) <> 'CH.QUANTITY' ) AND
167 (substr(l_sql, l_pos-4, 11) <> 'CSQA.TARGET' ) AND
168 (substr(l_sql, l_pos-4, 19) <> 'CSQA.PAYMENT_AMOUNT' ) THEN
169 FND_MESSAGE.SET_NAME('CN', 'CN_ILLEGAL_EXP_COMPONENT');
170 FND_MSG_PUB.ADD;
171 RAISE FND_API.G_EXC_ERROR;
172 end if;
173 l_pos := l_pos + 1;
174 end if;
175 end loop;
176 end check_planning_exp;
177
178 PROCEDURE check_modeling (p_calc_formula_id IN CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE,
179 p_cumulative_flag IN CN_CALC_FORMULAS.CUMULATIVE_FLAG%TYPE,
180 p_output_exp_id IN CN_CALC_FORMULAS.OUTPUT_EXP_ID%TYPE,
181 p_f_output_exp_id IN CN_CALC_FORMULAS.F_OUTPUT_EXP_ID%TYPE) IS
182 cursor rt_asgns is
183 select s.number_dim, d.dim_unit_code
184 from cn_rt_formula_asgns a, cn_rate_schedules s,
185 cn_rate_sch_dims r, cn_rate_dimensions d
186 where a.rate_schedule_id = s.rate_schedule_id
187 and s.rate_schedule_id = r.rate_schedule_id
188 and r.rate_dimension_id = d.rate_dimension_id
189 and a.calc_formula_id = p_calc_formula_id;
190
191 cursor inputs is
192 select calc_sql_exp_id, f_calc_sql_exp_id
193 from cn_formula_inputs where calc_formula_id = p_calc_formula_id;
194 BEGIN
195
196 -- 1. Formula should have only a single dimension rate table (no rate table is also allowed)
197 -- 2. The rate table can only have AMOUNT or PERCENT based tiers.
198 -- 3. The cumulative flag for the formula should be checked.
199 -- 4. Restricted set of variables that can appear in an input or output expression can be
200
201 for r in rt_asgns loop
202 IF r.dim_unit_code not in ('AMOUNT', 'PERCENT') OR
203 r.number_dim <> 1 THEN
204 -- condition 1 or 2 fails
205 FND_MESSAGE.SET_NAME('CN', 'CN_FORMULA_PLAN_RATE');
206 FND_MSG_PUB.ADD;
207 RAISE FND_API.G_EXC_ERROR;
208 END IF;
209 end loop;
210
211 IF p_cumulative_flag = 'N' THEN
212 -- condition 3 fails
213 FND_MESSAGE.SET_NAME('CN', 'CN_FORMULA_PLAN_CUM_FLAG');
214 FND_MSG_PUB.ADD;
215 RAISE FND_API.G_EXC_ERROR;
216 END IF;
217
218 /*
219 -- condition 4
220 check_planning_exp (p_output_exp_id);
221 check_planning_exp (p_f_output_exp_id);
222 for e in inputs loop
223 check_planning_exp (e.calc_sql_exp_id);
224 check_planning_exp (e.f_calc_sql_exp_id);
225 end loop;
226 */
227 -- if we made it through, then no error
228 END check_modeling;
229
230 -- Notes : Create calculation formula and generate formula packages
231 PROCEDURE Create_Formula
232 (p_api_version IN NUMBER ,
233 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
234 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
235 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
236 p_generate_packages IN VARCHAR2 := FND_API.G_TRUE ,
237 p_name IN CN_CALC_FORMULAS.NAME%TYPE,
238 p_description IN CN_CALC_FORMULAS.DESCRIPTION%TYPE
239 := null,
240 p_formula_type IN CN_CALC_FORMULAS.FORMULA_TYPE%TYPE,
241 p_trx_group_code IN CN_CALC_FORMULAS.TRX_GROUP_CODE%TYPE,
242 p_number_dim IN CN_CALC_FORMULAS.NUMBER_DIM%TYPE,
243 p_cumulative_flag IN CN_CALC_FORMULAS.CUMULATIVE_FLAG%TYPE,
244 p_itd_flag IN CN_CALC_FORMULAS.ITD_FLAG%TYPE,
245 p_split_flag IN CN_CALC_FORMULAS.SPLIT_FLAG%TYPE,
246 p_threshold_all_tier_flag IN CN_CALC_FORMULAS.THRESHOLD_ALL_TIER_FLAG%TYPE,
247 p_modeling_flag IN CN_CALC_FORMULAS.MODELING_FLAG%TYPE,
248 p_perf_measure_id IN CN_CALC_FORMULAS.PERF_MEASURE_ID%TYPE,
249 p_output_exp_id IN CN_CALC_FORMULAS.OUTPUT_EXP_ID%TYPE,
250 p_f_output_exp_id IN CN_CALC_FORMULAS.F_OUTPUT_EXP_ID%TYPE
251 := NULL,
252 p_input_tbl IN input_tbl_type := g_miss_input_tbl,
253 p_rt_assign_tbl IN rt_assign_tbl_type := g_miss_rt_assign_tbl,
254 --R12 MOAC Changes--Start
255 p_org_id IN CN_CALC_FORMULAS.ORG_ID%TYPE, --new
256 x_calc_formula_id IN OUT NOCOPY CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE, --changed
257 --R12 MOAC Changes--End
258 x_formula_status OUT NOCOPY CN_CALC_FORMULAS.FORMULA_STATUS%TYPE,
259 x_return_status OUT NOCOPY VARCHAR2,
260 x_msg_count OUT NOCOPY NUMBER,
261 x_msg_data OUT NOCOPY VARCHAR2) IS
262 l_api_name CONSTANT VARCHAR2(30) := 'Create_Formula';
263 l_api_version CONSTANT NUMBER := 1.0;
264
265 l_temp_id NUMBER;
266
267 /* Start - R12 Notes History */
268 l_formula_name VARCHAR2(30);
269 l_note_msg VARCHAR2(240);
270 l_note_id NUMBER;
271 l_output_exp_name VARCHAR2(30);
272 l_f_output_exp_name VARCHAR2(30);
273 l_perf_measure_name VARCHAR2(30);
274 l_consolidated_note VARCHAR2(2000);
275 /* End - R12 Notes History */
276 BEGIN
277 -- Standard Start of API savepoint
278 SAVEPOINT Create_Formula;
279 -- Standard call to check for call compatibility.
280 IF NOT FND_API.Compatible_API_Call
281 (l_api_version ,
282 p_api_version ,
283 l_api_name ,
284 G_PKG_NAME )
285 THEN
286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
287 END IF;
288 -- Initialize message list if p_init_msg_list is set to TRUE.
289 IF FND_API.to_Boolean( p_init_msg_list ) THEN
290 FND_MSG_PUB.initialize;
291 END IF;
292 -- Initialize API return status to success
293 x_return_status := FND_API.G_RET_STS_SUCCESS;
294
295 -- API body
296 validate_name_flags(NULL,
297 p_name,
298 p_formula_type,
299 p_trx_group_code,
300 p_number_dim,
301 p_cumulative_flag,
302 p_itd_flag,
303 p_split_flag,
304 p_threshold_all_tier_flag,
305 p_perf_measure_id,
306 --R12 MOAC Changes--Start
307 p_org_id
308 --R12 MOAC Changes--End
309 );
310
311 -- call table handler to create the formula record in cn_calc_formulas
312 cn_calc_formulas_pkg.insert_row
313 (x_calc_formula_id => x_calc_formula_id,
314 x_name => p_name,
315 x_description => p_description,
316 x_formula_type => p_formula_type,
317 x_trx_group_code => p_trx_group_code,
318 x_number_dim => p_number_dim,
319 x_cumulative_flag => p_cumulative_flag,
320 x_itd_flag => p_itd_flag,
321 x_split_flag => p_split_flag,
322 x_threshold_all_tier_flag => p_threshold_all_tier_flag,
323 x_modeling_flag => p_modeling_flag,
324 x_perf_measure_id => p_perf_measure_id,
325 x_output_exp_id => p_output_exp_id,
326 x_f_output_exp_id => p_f_output_exp_id,
327 --R12 MOAC Changes--Start
328 x_org_id => p_org_id
329 --R12 MOAC Changes--End
330 );
331
332 /* Start - R12 Notes History */
333
334 l_consolidated_note := '';
335
336 select name into l_formula_name
337 from cn_calc_formulas
338 where calc_formula_id = x_calc_formula_id;
339
340 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_CREATE');
341 fnd_message.set_token('FORMULA_NAME', l_formula_name);
342 l_note_msg := fnd_message.get;
343
344 jtf_notes_pub.create_note
345 (p_api_version => 1.0,
346 x_return_status => x_return_status,
347 x_msg_count => x_msg_count,
348 x_msg_data => x_msg_data,
349 p_source_object_id => x_calc_formula_id,
350 p_source_object_code => 'CN_CALC_FORMULAS',
351 p_notes => l_note_msg,
352 p_notes_detail => l_note_msg,
353 p_note_type => 'CN_SYSGEN', -- for system generated
354 x_jtf_note_id => l_note_id -- returned
355 );
356
357 IF p_output_exp_id IS NOT NULL THEN
358 select name INTO l_output_exp_name
359 from cn_calc_sql_exps
360 where calc_sql_exp_id = p_output_exp_id;
361
362 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_OPEXPR_CRE');
363 fnd_message.set_token('EXPR', l_output_exp_name);
364 l_note_msg := fnd_message.get;
365
366 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
367 /*
368 jtf_notes_pub.create_note
369 (p_api_version => 1.0,
370 x_return_status => x_return_status,
371 x_msg_count => x_msg_count,
372 x_msg_data => x_msg_data,
373 p_source_object_id => x_calc_formula_id,
374 p_source_object_code => 'CN_CALC_FORMULAS',
375 p_notes => l_note_msg,
376 p_notes_detail => l_note_msg,
377 p_note_type => 'CN_SYSGEN', -- for system generated
378 x_jtf_note_id => l_note_id -- returned
379 );
380 */
381 END IF;
382
383 IF p_f_output_exp_id IS NOT NULL THEN
384 select name INTO l_f_output_exp_name
385 from cn_calc_sql_exps
386 where calc_sql_exp_id = p_f_output_exp_id;
387
388 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_OPFORE_CRE');
389 fnd_message.set_token('EXPR', l_f_output_exp_name);
390 l_note_msg := fnd_message.get;
391
392 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
393 /*
394 jtf_notes_pub.create_note
395 (p_api_version => 1.0,
396 x_return_status => x_return_status,
397 x_msg_count => x_msg_count,
398 x_msg_data => x_msg_data,
399 p_source_object_id => x_calc_formula_id,
400 p_source_object_code => 'CN_CALC_FORMULAS',
401 p_notes => l_note_msg,
402 p_notes_detail => l_note_msg,
403 p_note_type => 'CN_SYSGEN', -- for system generated
404 x_jtf_note_id => l_note_id -- returned
405 );
406 */
407 END IF;
408
409 -- Consolidated all Formula Details changes notes in one Note
410 IF LENGTH(l_consolidated_note) > 1 THEN
411 jtf_notes_pub.create_note
412 (p_api_version => 1.0,
413 x_return_status => x_return_status,
414 x_msg_count => x_msg_count,
415 x_msg_data => x_msg_data,
416 p_source_object_id => x_calc_formula_id,
417 p_source_object_code => 'CN_CALC_FORMULAS',
418 p_notes => l_consolidated_note,
419 p_notes_detail => l_consolidated_note,
420 p_note_type => 'CN_SYSGEN', -- for system generated
421 x_jtf_note_id => l_note_id -- returned
422 );
423
424 END IF;
425
426 IF p_perf_measure_id IS NOT NULL THEN
427 select name INTO l_perf_measure_name
428 from cn_calc_sql_exps
429 where calc_sql_exp_id = p_perf_measure_id;
430
431 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_PERF_CREATE');
432 fnd_message.set_token('PERF_MEASURE', l_perf_measure_name);
433 l_note_msg := fnd_message.get;
434
435 jtf_notes_pub.create_note
436 (p_api_version => 1.0,
437 x_return_status => x_return_status,
438 x_msg_count => x_msg_count,
439 x_msg_data => x_msg_data,
440 p_source_object_id => x_calc_formula_id,
441 p_source_object_code => 'CN_CALC_FORMULAS',
442 p_notes => l_note_msg,
443 p_notes_detail => l_note_msg,
444 p_note_type => 'CN_SYSGEN', -- for system generated
445 x_jtf_note_id => l_note_id -- returned
446 );
447 END IF;
448
449 /* End - R12 Notes History */
450
451 -- call table handler to create the input records in cn_formula_inputs
452 IF (p_input_tbl.COUNT > 0) THEN
453 FOR i IN p_input_tbl.first..p_input_tbl.last LOOP
454 l_temp_id := NULL;
455 cn_formula_inputs_pkg.insert_row
456 (x_formula_input_id => l_temp_id,
457 x_calc_formula_id => x_calc_formula_id,
458 x_calc_sql_exp_id => p_input_tbl(i).calc_sql_exp_id,
459 x_f_calc_sql_exp_id => p_input_tbl(i).f_calc_sql_exp_id,
460 x_rate_dim_sequence => p_input_tbl(i).rate_dim_sequence,
461 x_cumulative_flag => p_input_tbl(i).cumulative_flag,
462 x_split_flag => p_input_tbl(i).split_flag,
463 --R12 MOAC Changes--Start
464 x_org_id => p_org_id
465 --R12 MOAC Changes--End
466 );
467 END LOOP;
468 END IF;
469
470 -- call table handler to create the rate table assignment records
471 -- in cn_rt_formula_asgns
472 IF (p_rt_assign_tbl.COUNT > 0) THEN
473 FOR i IN p_rt_assign_tbl.first..p_rt_assign_tbl.last LOOP
474 -- make sure no date ranges overlap and start_date <= end_date
475 for j in p_rt_assign_tbl.first..i-1 loop
476 if greatest(p_rt_assign_tbl(i).start_date, p_rt_assign_tbl(j).start_date) <=
477 least(nvl(p_rt_assign_tbl(i).end_date,g_end_of_time),
478 nvl(p_rt_assign_tbl(j).end_date,g_end_of_time)) then
479 FND_MESSAGE.SET_NAME('CN', 'CN_DATE_OVERLAP');
480 FND_MSG_PUB.ADD;
481 RAISE FND_API.G_EXC_ERROR;
482 end if;
483 end loop;
484
485 if p_rt_assign_tbl(i).start_date > nvl(p_rt_assign_tbl(i).end_date, g_end_of_time) then
486 FND_MESSAGE.SET_NAME('CN', 'ALL_INVALID_PERIOD_RANGE');
487 FND_MSG_PUB.ADD;
488 RAISE FND_API.G_EXC_ERROR;
489 end if;
490
491 l_temp_id := NULL;
492 cn_rt_formula_asgns_pkg.insert_row
493 (x_rt_formula_asgn_id => l_temp_id,
494 x_calc_formula_id => x_calc_formula_id,
495 x_rate_schedule_id => p_rt_assign_tbl(i).rate_schedule_id,
496 x_start_date => p_rt_assign_tbl(i).start_date,
497 x_end_date => p_rt_assign_tbl(i).end_date,
498 --R12 MOAC Changes--Start
499 x_org_id => p_org_id
500 --R12 MOAC Changes--End
501 );
502 END LOOP;
503 END IF;
504
505 -- easier to check modeling after the fact since data would already be in tables
506 if p_modeling_flag = 'Y' then
507 check_modeling(x_calc_formula_id, p_cumulative_flag, p_output_exp_id, p_f_output_exp_id);
508 end if;
509
510 -- Standard check of p_commit.
511 IF FND_API.To_Boolean( p_commit ) THEN
512 COMMIT WORK;
513 END IF;
514
515 -- this runs on its own separate commit cycle
516 IF fnd_api.to_boolean(p_generate_packages) THEN
517 generate_formula(p_api_version => 1.0,
518 p_calc_formula_id => x_calc_formula_id,
519 p_formula_type => p_formula_type,
520 p_trx_group_code => p_trx_group_code,
521 p_number_dim => p_number_dim,
522 p_itd_flag => p_itd_flag,
523 p_perf_measure_id => p_perf_measure_id,
524 p_output_exp_id => p_output_exp_id,
525 p_f_output_exp_id => p_f_output_exp_id,
526 x_formula_status => x_formula_status,
527 --R12 MOAC Changes--Start
528 p_org_id => p_org_id,
529 --R12 MOAC Changes--End
530 x_return_status => x_return_status,
531 x_msg_count => x_msg_count,
532 x_msg_data => x_msg_data);
533 END IF;
534
535 -- End of API body.
536
537 -- Standard call to get message count and if count is 1, get message info.
538 FND_MSG_PUB.Count_And_Get
539 (p_count => x_msg_count ,
540 p_data => x_msg_data ,
541 p_encoded => FND_API.G_FALSE );
542 EXCEPTION
543 WHEN FND_API.G_EXC_ERROR THEN
544 ROLLBACK TO Create_Formula;
545 x_return_status := FND_API.G_RET_STS_ERROR ;
546 FND_MSG_PUB.count_and_get
547 (p_count => x_msg_count ,
548 p_data => x_msg_data ,
549 p_encoded => FND_API.G_FALSE );
550 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
551 ROLLBACK TO Create_Formula;
552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
553 FND_MSG_PUB.count_and_get
554 (p_count => x_msg_count ,
555 p_data => x_msg_data ,
556 p_encoded => FND_API.G_FALSE );
557 WHEN OTHERS THEN
558 ROLLBACK TO Create_Formula;
559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
560 IF FND_MSG_PUB.check_msg_level
561 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
562 THEN
563 FND_MSG_PUB.add_exc_msg
564 (G_PKG_NAME ,
565 l_api_name );
566 END IF;
567 FND_MSG_PUB.count_and_get
568 (p_count => x_msg_count ,
569 p_data => x_msg_data ,
570 p_encoded => FND_API.G_FALSE );
571 END Create_Formula;
572
573 -- Notes : Update calculation formula and generate formula packages
574 PROCEDURE Update_Formula
575 (p_api_version IN NUMBER ,
576 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
577 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
578 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
579 p_generate_packages IN VARCHAR2 := FND_API.G_TRUE ,
580 p_calc_formula_id IN CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE,
581 p_name IN CN_CALC_FORMULAS.NAME%TYPE,
582 p_description IN CN_CALC_FORMULAS.DESCRIPTION%TYPE
583 := null,
584 p_formula_type IN CN_CALC_FORMULAS.FORMULA_TYPE%TYPE,
585 p_formula_status IN CN_CALC_FORMULAS.FORMULA_STATUS%TYPE,
586 p_trx_group_code IN CN_CALC_FORMULAS.TRX_GROUP_CODE%TYPE,
587 p_number_dim IN CN_CALC_FORMULAS.NUMBER_DIM%TYPE,
588 p_cumulative_flag IN CN_CALC_FORMULAS.CUMULATIVE_FLAG%TYPE,
589 p_itd_flag IN CN_CALC_FORMULAS.ITD_FLAG%TYPE,
590 p_split_flag IN CN_CALC_FORMULAS.SPLIT_FLAG%TYPE,
591 p_threshold_all_tier_flag IN CN_CALC_FORMULAS.THRESHOLD_ALL_TIER_FLAG%TYPE,
592 p_modeling_flag IN CN_CALC_FORMULAS.MODELING_FLAG%TYPE,
593 p_perf_measure_id IN CN_CALC_FORMULAS.PERF_MEASURE_ID%TYPE,
594 p_output_exp_id IN CN_CALC_FORMULAS.OUTPUT_EXP_ID%TYPE,
595 p_f_output_exp_id IN CN_CALC_FORMULAS.F_OUTPUT_EXP_ID%TYPE := NULL,
596 p_input_tbl IN input_tbl_type := g_miss_input_tbl,
597 p_rt_assign_tbl IN rt_assign_tbl_type := g_miss_rt_assign_tbl,
598 --R12 MOAC Changes--Start
599 p_org_id IN CN_CALC_FORMULAS.ORG_ID%TYPE, --new
600 p_object_version_number IN OUT NOCOPY CN_CALC_FORMULAS.OBJECT_VERSION_NUMBER%TYPE, --Changed
601 --R12 MOAC Changes--End
602 x_formula_status OUT NOCOPY CN_CALC_FORMULAS.FORMULA_STATUS%TYPE,
603 x_return_status OUT NOCOPY VARCHAR2,
604 x_msg_count OUT NOCOPY NUMBER,
605 x_msg_data OUT NOCOPY VARCHAR2) IS
606
607 l_api_name CONSTANT VARCHAR2(30) := 'Update_Formula';
608 l_api_version CONSTANT NUMBER := 1.0;
609 l_temp_id NUMBER;
610 l_update_count NUMBER := 0;
611 l_srp_prd_quota_ext_id cn_srp_period_quotas_ext.srp_period_quota_ext_id%TYPE;
612 l_num_dim CN_CALC_FORMULAS.NUMBER_DIM%TYPE;
613 l_count_formula_input NUMBER := 0;
614 l_plan_elt_tbl CN_CALC_SQL_EXPS_PVT.NUM_TBL_TYPE;
615
616 /* Start - R12 Notes History */
617 l_formula_name VARCHAR2(30);
618 l_old_formula_name VARCHAR2(30);
619 l_meaning VARCHAR2(80);
620 l_note_msg VARCHAR2(240);
621 l_note_id NUMBER;
622 l_output_exp_name VARCHAR2(30);
623 l_f_output_exp_name VARCHAR2(30);
624 l_perf_measure_name VARCHAR2(30);
625 l_output_exp_name_old VARCHAR2(30);
626 l_f_output_exp_name_old VARCHAR2(30);
627 l_perf_measure_name_old VARCHAR2(30);
628 l_new_meaning VARCHAR2(30);
629 l_old_meaning VARCHAR2(30);
630 l_consolidated_note VARCHAR2(2000);
631 l_consolidated_exp_note VARCHAR2(2000);
632 /* End - R12 Notes History */
633
634 CURSOR get_old_formula_rec(l_calc_formula_id number) IS
635 SELECT cf.calc_formula_id, cf.NAME, cf.FORMULA_TYPE, cf.TRX_GROUP_CODE,
636 cf.SPLIT_FLAG, cf.CUMULATIVE_FLAG, cf.ITD_FLAG, cf.MODELING_FLAG, cl.MEANING,
637 cf.output_exp_id, cf.f_output_exp_id, cf.perf_measure_id, cs1.name oname,
638 cs2.name fname, cs3.name pname
639 FROM cn_calc_formulas cf, cn_lookups cl,
640 cn_calc_sql_exps cs1, cn_calc_sql_exps cs2, cn_calc_sql_exps cs3
641 WHERE cl.lookup_code = cf.FORMULA_TYPE
642 AND cl.lookup_type = 'FORMULA_TYPE'
643 AND cf.OUTPUT_EXP_ID = cs1.CALC_SQL_EXP_ID (+)
644 AND cf.F_OUTPUT_EXP_ID = cs2.CALC_SQL_EXP_ID (+)
645 AND cf.PERF_MEASURE_ID = cs3.CALC_SQL_EXP_ID (+)
646 AND calc_formula_id = l_calc_formula_id;
647
648
649 l_old_rec get_old_formula_rec%ROWTYPE;
650 /* End - R12 Notes History */
651
652 CURSOR c_next_srp_qut_id IS
653 SELECT cn_srp_period_quotas_ext_s.NEXTVAL
654 FROM dual;
655
656 CURSOR c_srp_quota_detail IS
657 select spq.srp_period_quota_id
658 from cn_quotas_v qut, cn_srp_period_quotas spq
659 where qut.quota_id = spq.quota_id
660 and qut.calc_formula_id = p_calc_formula_id;
661
662 CURSOR c1 IS
663 SELECT
664 formula_type,
665 trx_group_code,
666 number_dim,
667 cumulative_flag,
668 itd_flag,
669 split_flag,
670 threshold_all_tier_flag,
671 modeling_flag,
672 perf_measure_id,
673 output_exp_id,
674 f_output_exp_id,
675 formula_status
676 FROM cn_calc_formulas
677 WHERE calc_formula_id = p_calc_formula_id;
678 rec_info c1%ROWTYPE;
679
680 cursor get_plans is
681 select distinct qa.comp_plan_id from cn_quota_assigns qa, cn_quotas_v q
682 where qa.quota_id = q.quota_id
683 and q.calc_formula_id = p_calc_formula_id;
684
685 BEGIN
686 -- Standard Start of API savepoint
687 SAVEPOINT Update_Formula;
688 -- Standard call to check for call compatibility.
689 IF NOT FND_API.Compatible_API_Call
690 (l_api_version ,
691 p_api_version ,
692 l_api_name ,
693 G_PKG_NAME )
694 THEN
695 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
696 END IF;
697 -- Initialize message list if p_init_msg_list is set to TRUE.
698 IF FND_API.to_Boolean( p_init_msg_list ) THEN
699 FND_MSG_PUB.initialize;
700 END IF;
701 -- Initialize API return status to success
702 x_return_status := FND_API.G_RET_STS_SUCCESS;
703
704 -- API body
705
706 -- validate name and flag combinations
707 validate_name_flags(p_calc_formula_id,
708 p_name,
709 p_formula_type,
710 p_trx_group_code,
711 p_number_dim,
712 p_cumulative_flag,
713 p_itd_flag,
714 p_split_flag,
715 p_threshold_all_tier_flag,
716 p_perf_measure_id,
717 --R12 MOAC Changes--Start
718 p_org_id
719 --R12 MOAC Changes--End
720 );
721
722 OPEN c1;
723 FETCH c1 INTO rec_info;
724 IF (c1%notfound) THEN
725 CLOSE c1;
726 RAISE no_data_found;
727 END IF;
728 CLOSE c1;
729
730 l_num_dim := p_number_dim;
731 x_formula_status := p_formula_status;
732 IF (rec_info.formula_type <> p_formula_type OR
733 rec_info.trx_group_code <> p_trx_group_code OR
734 rec_info.number_dim <> p_number_dim OR
735 rec_info.cumulative_flag <> p_cumulative_flag OR
736 rec_info.itd_flag <> p_itd_flag OR
737 rec_info.threshold_all_tier_flag <> p_threshold_all_tier_flag OR
738 rec_info.modeling_flag <> p_modeling_flag OR
739 rec_info.perf_measure_id <> p_perf_measure_id OR
740 rec_info.output_exp_id <> p_output_exp_id OR
741 rec_info.split_flag <> p_split_flag OR
742 rec_info.f_output_exp_id <> p_f_output_exp_id)
743 THEN
744 x_formula_status := 'INCOMPLETE';
745 END IF;
746
747 -- *********************************************************************
748 -- ************ Start - This code is not required in R12 ***************
749 -- *********************************************************************
750 /*
751 -- call table handler to insert/update/delete the input records in cn_formula_inputs
752 IF (p_input_tbl.COUNT > 0) THEN
753 x_formula_status := 'INCOMPLETE';
754 FOR i IN p_input_tbl.first..p_input_tbl.last LOOP
755 IF (p_input_tbl(i).formula_input_id IS NULL) THEN
756 l_temp_id := NULL;
757 cn_formula_inputs_pkg.insert_row
758 (x_formula_input_id => l_temp_id,
759 x_calc_formula_id => p_calc_formula_id,
760 x_calc_sql_exp_id => p_input_tbl(i).calc_sql_exp_id,
761 x_f_calc_sql_exp_id => p_input_tbl(i).f_calc_sql_exp_id,
762 x_rate_dim_sequence => p_input_tbl(i).rate_dim_sequence,
763 x_cumulative_flag => p_input_tbl(i).cumulative_flag,
764 x_split_flag => p_input_tbl(i).split_flag,
765 --R12 MOAC Changes--Start
766 x_org_id => p_org_id
767 --R12 MOAC Changes--End
768 );
769
770 l_update_count := l_update_count + 1;
771 ELSIF (p_input_tbl(i).calc_sql_exp_id IS NULL) THEN
772 cn_formula_inputs_pkg.delete_row(p_input_tbl(i).formula_input_id);
773 l_num_dim := l_num_dim - 1;
774 ELSE
775 cn_formula_inputs_pkg.lock_row
776 (x_formula_input_id => p_input_tbl(i).formula_input_id,
777 x_object_version_number => p_input_tbl(i).object_version_number);
778
779 cn_formula_inputs_pkg.update_row
780 (x_formula_input_id => p_input_tbl(i).formula_input_id,
781 x_calc_formula_id => p_calc_formula_id,
782 x_calc_sql_exp_id => p_input_tbl(i).calc_sql_exp_id,
783 x_f_calc_sql_exp_id => p_input_tbl(i).f_calc_sql_exp_id,
784 x_rate_dim_sequence => p_input_tbl(i).rate_dim_sequence,
785 x_cumulative_flag => p_input_tbl(i).cumulative_flag,
786 x_split_flag => p_input_tbl(i).split_flag,
787 x_object_version_number => p_input_tbl(i).object_version_number);
788
789 l_update_count := l_update_count + 1;
790 END IF;
791 END LOOP;
792
793 -- this is required to support accumulation along multiple dimensions
794 -- for each rec in cn_srp_period_quotas, add p_input_tbl.COUNT - 1 rows into the ext table
795 FOR form_rec IN c_srp_quota_detail LOOP
796 delete from cn_srp_period_quotas_ext where srp_period_quota_id = form_rec.srp_period_quota_id;
797 FOR i in 2.. l_update_count LOOP
798 OPEN c_next_srp_qut_id;
799 FETCH c_next_srp_qut_id INTO l_srp_prd_quota_ext_id;
800 IF (c_next_srp_qut_id%notfound) THEN
801 CLOSE c_next_srp_qut_id;
802 RAISE no_data_found;
803 END IF;
804 CLOSE c_next_srp_qut_id;
805
806 insert into cn_srp_period_quotas_ext
807 (srp_period_quota_ext_id,
808 srp_period_quota_id,
809 input_sequence,
810 input_achieved_ptd,
811 input_achieved_itd
812 ) values
813 (l_srp_prd_quota_ext_id,
814 form_rec.srp_period_quota_id,
815 i,
816 null,
817 null);
818 END LOOP;
819 END LOOP;
820 END IF;
821 */
822 -- *********************************************************************
823 -- ************ End - This code is not required in R12 *****************
824 -- *********************************************************************
825
826 /* Start - R12 Notes History */
827 OPEN get_old_formula_rec(p_calc_formula_id);
828 FETCH get_old_formula_rec INTO l_old_rec;
829 CLOSE get_old_formula_rec;
830 /* End - R12 Notes History */
831
832 -- call table handler to update the formula record in cn_calc_formulas
833 cn_calc_formulas_pkg.lock_row
834 (x_calc_formula_id => p_calc_formula_id,
835 x_object_version_number => p_object_version_number);
836 cn_calc_formulas_pkg.update_row
837 (x_calc_formula_id => p_calc_formula_id,
838 x_name => p_name,
839 x_description => p_description,
840 x_formula_status => x_formula_status,
841 x_formula_type => p_formula_type,
842 x_trx_group_code => p_trx_group_code,
843 x_number_dim => l_num_dim,
844 x_cumulative_flag => p_cumulative_flag,
845 x_itd_flag => p_itd_flag,
846 x_split_flag => p_split_flag,
847 x_threshold_all_tier_flag => p_threshold_all_tier_flag,
848 x_modeling_flag => p_modeling_flag,
849 x_perf_measure_id => p_perf_measure_id,
850 x_output_exp_id => p_output_exp_id,
851 x_f_output_exp_id => p_f_output_exp_id,
852 x_object_version_number => p_object_version_number);
853
854 /* Start - R12 Notes History */
855
856 l_consolidated_note := '';
857 l_consolidated_exp_note := '';
858
859 IF (l_old_rec.name <> p_name) THEN
860 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_NAME_CREATE');
861 fnd_message.set_token('FORMULA_OLD', l_old_rec.name);
862 fnd_message.set_token('FORMULA_NEW', p_name);
863 l_note_msg := fnd_message.get;
864
865 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
866 /*
867 jtf_notes_pub.create_note
868 (p_api_version => 1.0,
869 x_return_status => x_return_status,
870 x_msg_count => x_msg_count,
871 x_msg_data => x_msg_data,
872 p_source_object_id => p_calc_formula_id,
873 p_source_object_code => 'CN_CALC_FORMULAS',
874 p_notes => l_note_msg,
875 p_notes_detail => l_note_msg,
876 p_note_type => 'CN_SYSGEN', -- for system generated
877 x_jtf_note_id => l_note_id -- returned
878 );
879 */
880 END IF;
881
882 IF (l_old_rec.formula_type <> p_formula_type) THEN
883 SELECT meaning into l_meaning
884 FROM cn_calc_formulas cf, cn_lookups cl
885 WHERE cl.lookup_code = cf.FORMULA_TYPE
886 AND cl.lookup_type = 'FORMULA_TYPE'
887 AND calc_formula_id = p_calc_formula_id;
888
889 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_TYPE_UPDATE');
890 fnd_message.set_token('OLD_TYPE', l_old_rec.meaning);
891 fnd_message.set_token('NEW_TYPE', l_meaning);
892 l_note_msg := fnd_message.get;
893
894 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
895 /*
896 jtf_notes_pub.create_note
897 (p_api_version => 1.0,
898 x_return_status => x_return_status,
899 x_msg_count => x_msg_count,
900 x_msg_data => x_msg_data,
901 p_source_object_id => p_calc_formula_id,
902 p_source_object_code => 'CN_CALC_FORMULAS',
903 p_notes => l_note_msg,
904 p_notes_detail => l_note_msg,
905 p_note_type => 'CN_SYSGEN', -- for system generated
906 x_jtf_note_id => l_note_id -- returned
907 );
908 */
909 END IF;
910
911 IF (l_old_rec.TRX_GROUP_CODE <> p_trx_group_code) THEN
912 fnd_message.set_name('CN', 'CNR12_NOTE_FOR_PROTYPE_UPD');
913 fnd_message.set_token('OLD_OPTION', l_old_rec.TRX_GROUP_CODE);
914 fnd_message.set_token('NEW_OPTION', p_trx_group_code);
915 l_note_msg := fnd_message.get;
916
917 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
918 /*
919 jtf_notes_pub.create_note
920 (p_api_version => 1.0,
921 x_return_status => x_return_status,
922 x_msg_count => x_msg_count,
923 x_msg_data => x_msg_data,
924 p_source_object_id => p_calc_formula_id,
925 p_source_object_code => 'CN_CALC_FORMULAS',
926 p_notes => l_note_msg,
927 p_notes_detail => l_note_msg,
928 p_note_type => 'CN_SYSGEN', -- for system generated
929 x_jtf_note_id => l_note_id -- returned
930 );
931 */
932 END IF;
933
934 IF (l_old_rec.split_flag <> p_split_flag) THEN
935
936 SELECT meaning into l_new_meaning
937 FROM cn_lookups
938 WHERE lookup_type = 'SPLIT_FLAG'
939 AND lookup_code = p_split_flag;
940
941 SELECT meaning into l_old_meaning
942 FROM cn_lookups
943 WHERE lookup_type = 'SPLIT_FLAG'
944 AND lookup_code = l_old_rec.split_flag;
945
946 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_SPLIT_UPD');
947 fnd_message.set_token('OLD_OPTION', l_old_meaning);
948 fnd_message.set_token('NEW_OPTION', l_new_meaning);
949 l_note_msg := fnd_message.get;
950
951 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
952 /*
953 jtf_notes_pub.create_note
954 (p_api_version => 1.0,
955 x_return_status => x_return_status,
956 x_msg_count => x_msg_count,
957 x_msg_data => x_msg_data,
958 p_source_object_id => p_calc_formula_id,
959 p_source_object_code => 'CN_CALC_FORMULAS',
960 p_notes => l_note_msg,
961 p_notes_detail => l_note_msg,
962 p_note_type => 'CN_SYSGEN', -- for system generated
963 x_jtf_note_id => l_note_id -- returned
964 );
965 */
966 END IF;
967
968 IF (l_old_rec.cumulative_flag <> p_cumulative_flag) THEN
969 IF (p_cumulative_flag = 'Y') THEN
970 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_ACC1_UPDATE');
971 ELSE
972 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_ACC2_UPDATE');
973 END IF;
974
975 l_note_msg := fnd_message.get;
976
977 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
978 /*
979 jtf_notes_pub.create_note
980 (p_api_version => 1.0,
981 x_return_status => x_return_status,
982 x_msg_count => x_msg_count,
983 x_msg_data => x_msg_data,
984 p_source_object_id => p_calc_formula_id,
985 p_source_object_code => 'CN_CALC_FORMULAS',
986 p_notes => l_note_msg,
987 p_notes_detail => l_note_msg,
988 p_note_type => 'CN_SYSGEN', -- for system generated
989 x_jtf_note_id => l_note_id -- returned
990 );
991 */
992 END IF;
993
994 IF (l_old_rec.itd_flag <> p_itd_flag) THEN
995 IF (p_itd_flag = 'Y') THEN
996 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_ITD1_UPDATE');
997 ELSE
998 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_ITD2_UPDATE');
999 END IF;
1000
1001 l_note_msg := fnd_message.get;
1002
1003 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
1004 /*
1005 jtf_notes_pub.create_note
1006 (p_api_version => 1.0,
1007 x_return_status => x_return_status,
1008 x_msg_count => x_msg_count,
1009 x_msg_data => x_msg_data,
1010 p_source_object_id => p_calc_formula_id,
1011 p_source_object_code => 'CN_CALC_FORMULAS',
1012 p_notes => l_note_msg,
1013 p_notes_detail => l_note_msg,
1014 p_note_type => 'CN_SYSGEN', -- for system generated
1015 x_jtf_note_id => l_note_id -- returned
1016 );
1017 */
1018 END IF;
1019
1020 IF (l_old_rec.modeling_flag <> p_modeling_flag) THEN
1021 IF (p_modeling_flag = 'Y') THEN
1022 fnd_message.set_name('CN', 'CNR12_NOTE_FOR_PLANFL1_UPD');
1023 ELSE
1024 fnd_message.set_name('CN', 'CNR12_NOTE_FOR_PLANFL2_UPD');
1025 END IF;
1026
1027 l_note_msg := fnd_message.get;
1028
1029 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
1030 /*
1031 jtf_notes_pub.create_note
1032 (p_api_version => 1.0,
1033 x_return_status => x_return_status,
1034 x_msg_count => x_msg_count,
1035 x_msg_data => x_msg_data,
1036 p_source_object_id => p_calc_formula_id,
1037 p_source_object_code => 'CN_CALC_FORMULAS',
1038 p_notes => l_note_msg,
1039 p_notes_detail => l_note_msg,
1040 p_note_type => 'CN_SYSGEN', -- for system generated
1041 x_jtf_note_id => l_note_id -- returned
1042 );
1043 */
1044 END IF;
1045 -- Consolidated all Formula Details changes notes in one Note
1046 IF LENGTH(l_consolidated_note) > 1 THEN
1047 jtf_notes_pub.create_note
1048 (p_api_version => 1.0,
1049 x_return_status => x_return_status,
1050 x_msg_count => x_msg_count,
1051 x_msg_data => x_msg_data,
1052 p_source_object_id => p_calc_formula_id,
1053 p_source_object_code => 'CN_CALC_FORMULAS',
1054 p_notes => l_consolidated_note,
1055 p_notes_detail => l_consolidated_note,
1056 p_note_type => 'CN_SYSGEN', -- for system generated
1057 x_jtf_note_id => l_note_id -- returned
1058 );
1059
1060 END IF;
1061 IF (((p_output_exp_id IS NOT NULL AND l_old_rec.output_exp_id IS NOT NULL) AND
1062 (l_old_rec.output_exp_id <> p_output_exp_id)) OR
1063 (p_output_exp_id IS NOT NULL AND l_old_rec.output_exp_id IS NULL))THEN
1064
1065 select name INTO l_output_exp_name
1066 from cn_calc_sql_exps
1067 where calc_sql_exp_id = p_output_exp_id;
1068
1069 IF l_old_rec.output_exp_id IS NOT NULL THEN
1070 l_output_exp_name_old := l_old_rec.oname;
1071 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_OPEXPR_UPD');
1072 fnd_message.set_token('EXPR', l_output_exp_name_old);
1073 fnd_message.set_token('NEW_EXPR', l_output_exp_name);
1074 l_note_msg := fnd_message.get;
1075 ELSE
1076 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_OPEXPR_CRE');
1077 fnd_message.set_token('EXPR', l_output_exp_name);
1078 l_note_msg := fnd_message.get;
1079 END IF;
1080 l_consolidated_exp_note := l_consolidated_exp_note || l_note_msg || fnd_global.local_chr(10);
1081 /*
1082 jtf_notes_pub.create_note
1083 (p_api_version => 1.0,
1084 x_return_status => x_return_status,
1085 x_msg_count => x_msg_count,
1086 x_msg_data => x_msg_data,
1087 p_source_object_id => p_calc_formula_id,
1088 p_source_object_code => 'CN_CALC_FORMULAS',
1089 p_notes => l_note_msg,
1090 p_notes_detail => l_note_msg,
1091 p_note_type => 'CN_SYSGEN', -- for system generated
1092 x_jtf_note_id => l_note_id -- returned
1093 );
1094 */
1095 END IF;
1096
1097 IF (p_output_exp_id IS NULL AND l_old_rec.output_exp_id IS NOT NULL) THEN
1098 l_output_exp_name_old := l_old_rec.oname;
1099 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_OPEXPR_DEL');
1100 fnd_message.set_token('EXPR', l_output_exp_name_old);
1101 l_note_msg := fnd_message.get;
1102
1103 l_consolidated_exp_note := l_consolidated_exp_note || l_note_msg || fnd_global.local_chr(10);
1104 /*
1105 jtf_notes_pub.create_note
1106 (p_api_version => 1.0,
1107 x_return_status => x_return_status,
1108 x_msg_count => x_msg_count,
1109 x_msg_data => x_msg_data,
1110 p_source_object_id => p_calc_formula_id,
1111 p_source_object_code => 'CN_CALC_FORMULAS',
1112 p_notes => l_note_msg,
1113 p_notes_detail => l_note_msg,
1114 p_note_type => 'CN_SYSGEN', -- for system generated
1115 x_jtf_note_id => l_note_id -- returned
1116 );
1117 */
1118 END IF;
1119
1120 IF (((p_f_output_exp_id IS NOT NULL AND l_old_rec.f_output_exp_id IS NOT NULL) AND
1121 (l_old_rec.f_output_exp_id <> p_f_output_exp_id)) OR
1122 (p_f_output_exp_id IS NOT NULL AND l_old_rec.f_output_exp_id IS NULL))THEN
1123
1124 select name INTO l_f_output_exp_name
1125 from cn_calc_sql_exps
1126 where calc_sql_exp_id = p_f_output_exp_id;
1127
1128 IF l_old_rec.f_output_exp_id IS NOT NULL THEN
1129 l_f_output_exp_name_old := l_old_rec.fname;
1130 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_OPFORE_UPD');
1131 fnd_message.set_token('OLD_EXPR', l_f_output_exp_name_old);
1132 fnd_message.set_token('NEW_EXPR', l_f_output_exp_name);
1133 l_note_msg := fnd_message.get;
1134
1135 ELSE
1136 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_OPFORE_CRE');
1137 fnd_message.set_token('EXPR', l_f_output_exp_name);
1138 l_note_msg := fnd_message.get;
1139 END IF;
1140
1141 l_consolidated_exp_note := l_consolidated_exp_note || l_note_msg || fnd_global.local_chr(10);
1142 /*
1143 jtf_notes_pub.create_note
1144 (p_api_version => 1.0,
1145 x_return_status => x_return_status,
1146 x_msg_count => x_msg_count,
1147 x_msg_data => x_msg_data,
1148 p_source_object_id => p_calc_formula_id,
1149 p_source_object_code => 'CN_CALC_FORMULAS',
1150 p_notes => l_note_msg,
1151 p_notes_detail => l_note_msg,
1152 p_note_type => 'CN_SYSGEN', -- for system generated
1153 x_jtf_note_id => l_note_id -- returned
1154 );
1155 */
1156 END IF;
1157
1158 IF (p_f_output_exp_id IS NULL AND l_old_rec.f_output_exp_id IS NOT NULL) THEN
1159 l_f_output_exp_name_old := l_old_rec.fname;
1160 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_OPFORE_DEL');
1161 fnd_message.set_token('EXPR_NAME', l_f_output_exp_name_old);
1162 l_note_msg := fnd_message.get;
1163
1164 l_consolidated_exp_note := l_consolidated_exp_note || l_note_msg || fnd_global.local_chr(10);
1165 /*
1166 jtf_notes_pub.create_note
1167 (p_api_version => 1.0,
1168 x_return_status => x_return_status,
1169 x_msg_count => x_msg_count,
1170 x_msg_data => x_msg_data,
1171 p_source_object_id => p_calc_formula_id,
1172 p_source_object_code => 'CN_CALC_FORMULAS',
1173 p_notes => l_note_msg,
1174 p_notes_detail => l_note_msg,
1175 p_note_type => 'CN_SYSGEN', -- for system generated
1176 x_jtf_note_id => l_note_id -- returned
1177 );
1178 */
1179 END IF;
1180
1181 -- Consolidated all Output Expressions in one Note.
1182 IF LENGTH(l_consolidated_exp_note) > 1 THEN
1183 jtf_notes_pub.create_note
1184 (p_api_version => 1.0,
1185 x_return_status => x_return_status,
1186 x_msg_count => x_msg_count,
1187 x_msg_data => x_msg_data,
1188 p_source_object_id => p_calc_formula_id,
1189 p_source_object_code => 'CN_CALC_FORMULAS',
1190 p_notes => l_consolidated_exp_note,
1191 p_notes_detail => l_consolidated_exp_note,
1192 p_note_type => 'CN_SYSGEN', -- for system generated
1193 x_jtf_note_id => l_note_id -- returned
1194 );
1195
1196 END IF;
1197
1198 IF (((p_perf_measure_id IS NOT NULL AND l_old_rec.perf_measure_id IS NOT NULL) AND
1199 (l_old_rec.perf_measure_id <> p_perf_measure_id)) OR
1200 (p_perf_measure_id IS NOT NULL AND l_old_rec.perf_measure_id IS NULL))THEN
1201
1202 select name INTO l_perf_measure_name
1203 from cn_calc_sql_exps
1204 where calc_sql_exp_id = p_perf_measure_id;
1205
1206 IF l_old_rec.perf_measure_id IS NOT NULL THEN
1207 l_perf_measure_name_old := l_old_rec.pname;
1208 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_PERF_UPDATE');
1209 fnd_message.set_token('OLD_PERF_MEASURE', l_perf_measure_name_old);
1210 fnd_message.set_token('NEW_PERF_MEASURE', l_perf_measure_name);
1211 l_note_msg := fnd_message.get;
1212 ELSE
1213 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_PERF_CREATE');
1214 fnd_message.set_token('PERF_MEASURE', l_perf_measure_name);
1215 l_note_msg := fnd_message.get;
1216 END IF;
1217
1218 jtf_notes_pub.create_note
1219 (p_api_version => 1.0,
1220 x_return_status => x_return_status,
1221 x_msg_count => x_msg_count,
1222 x_msg_data => x_msg_data,
1223 p_source_object_id => p_calc_formula_id,
1224 p_source_object_code => 'CN_CALC_FORMULAS',
1225 p_notes => l_note_msg,
1226 p_notes_detail => l_note_msg,
1227 p_note_type => 'CN_SYSGEN', -- for system generated
1228 x_jtf_note_id => l_note_id -- returned
1229 );
1230 END IF;
1231 IF (p_perf_measure_id IS NULL AND l_old_rec.perf_measure_id IS NOT NULL) THEN
1232 l_perf_measure_name_old := l_old_rec.pname;
1233 fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_PERF_DELETE');
1234 fnd_message.set_token('PERF_MEASURE', l_perf_measure_name_old);
1235 l_note_msg := fnd_message.get;
1236
1237 jtf_notes_pub.create_note
1238 (p_api_version => 1.0,
1239 x_return_status => x_return_status,
1240 x_msg_count => x_msg_count,
1241 x_msg_data => x_msg_data,
1242 p_source_object_id => p_calc_formula_id,
1243 p_source_object_code => 'CN_CALC_FORMULAS',
1244 p_notes => l_note_msg,
1245 p_notes_detail => l_note_msg,
1246 p_note_type => 'CN_SYSGEN', -- for system generated
1247 x_jtf_note_id => l_note_id -- returned
1248 );
1249 END IF;
1250
1251 /* End - R12 Notes History */
1252
1253 -- if th cumulative flag or the split flag has been set to "N", then set the corros flags in the input table to "N"
1254 IF p_cumulative_flag = 'N' THEN
1255 update cn_formula_inputs set cumulative_flag = 'N' where calc_formula_id = p_calc_formula_id;
1256 END IF;
1257
1258 IF p_split_flag = 'N' THEN
1259 update cn_formula_inputs set split_flag = 'N' where calc_formula_id = p_calc_formula_id;
1260 END IF;
1261
1262 -- IF the split is changed, the changes has to be reflected
1263 IF rec_info.split_flag <> p_split_flag THEN
1264 update cn_formula_inputs set split_flag = p_split_flag
1265 where calc_formula_id = p_calc_formula_id and split_flag <> 'N' ;
1266
1267 -- changes for BUG#2797926
1268 IF (p_split_flag = 'Y') AND (p_split_flag IS NOT NULL )THEN
1269 -- check whether there is only one row in the
1270 -- formula inputs
1271 SELECT count(*)
1272 INTO l_count_formula_input
1273 FROM cn_formula_inputs
1274 WHERE calc_formula_id = p_calc_formula_id ;
1275
1276 -- if only one formula input is there
1277 -- update the split flag to 'Y'
1278 IF (l_count_formula_input = 1) THEN
1279 update cn_formula_inputs set split_flag = p_split_flag
1280 where calc_formula_id = p_calc_formula_id ;
1281 END IF;
1282 END IF;
1283 END IF;
1284
1285
1286 -- If the change made the formula invalid, change comp plans using
1287 -- this formula to incomplete
1288 if x_formula_status = 'INCOMPLETE' then
1289 for p in get_plans loop
1290 -- invalidate plans using this formula
1291 cn_comp_plans_pkg.set_status
1292 ( x_comp_plan_id => p.comp_plan_id
1293 ,x_quota_id => null
1294 ,x_rate_schedule_id => null
1295 ,x_status_code => 'INCOMPLETE'
1296 ,x_event => null);
1297 end loop;
1298 end if;
1299
1300 -- *********************************************************************
1301 -- ************ Start - This code is not required in R12 ***************
1302 -- *********************************************************************
1303 /*
1304 -- call table handler to insert/update/delete the rate table assignment
1305 -- records in cn_rt_formula_asgns
1306 IF (p_rt_assign_tbl.COUNT > 0) THEN
1307 FOR i IN p_rt_assign_tbl.first..p_rt_assign_tbl.last LOOP
1308 -- make sure no date ranges overlap and start_date <= end_date
1309 for j in p_rt_assign_tbl.first..i-1 loop
1310 if greatest(p_rt_assign_tbl(i).start_date, p_rt_assign_tbl(j).start_date) <=
1311 least(nvl(p_rt_assign_tbl(i).end_date,g_end_of_time),
1312 nvl(p_rt_assign_tbl(j).end_date,g_end_of_time)) then
1313 FND_MESSAGE.SET_NAME('CN', 'CN_DATE_OVERLAP');
1314 FND_MSG_PUB.ADD;
1315 RAISE FND_API.G_EXC_ERROR;
1316 end if;
1317 end loop;
1318
1319 if p_rt_assign_tbl(i).start_date > nvl(p_rt_assign_tbl(i).end_date, g_end_of_time) then
1320 FND_MESSAGE.SET_NAME('CN', 'ALL_INVALID_PERIOD_RANGE');
1321 FND_MSG_PUB.ADD;
1322 RAISE FND_API.G_EXC_ERROR;
1323 end if;
1324
1325 IF (p_rt_assign_tbl(i).rt_formula_asgn_id IS NULL) then
1326 l_temp_id := NULL;
1327 cn_rt_formula_asgns_pkg.insert_row
1328 (x_rt_formula_asgn_id => l_temp_id,
1329 x_calc_formula_id => p_calc_formula_id,
1330 x_rate_schedule_id => p_rt_assign_tbl(i).rate_schedule_id,
1331 x_start_date => p_rt_assign_tbl(i).start_date,
1332 x_end_date => p_rt_assign_tbl(i).end_date,
1333 --R12 MOAC Changes--Start
1334 x_org_id => p_org_id
1335 --R12 MOAC Changes--End
1336 );
1337 ELSIF (p_rt_assign_tbl(i).rate_schedule_id IS NULL) THEN
1338 cn_rt_formula_asgns_pkg.delete_row
1339 (p_rt_assign_tbl(i).rt_formula_asgn_id);
1340 ELSE
1341 cn_rt_formula_asgns_pkg.lock_row
1342 (x_rt_formula_asgn_id => p_rt_assign_tbl(i).rt_formula_asgn_id,
1343 x_object_version_number => p_rt_assign_tbl(i).object_version_number);
1344
1345 cn_rt_formula_asgns_pkg.update_row
1346 (x_rt_formula_asgn_id => p_rt_assign_tbl(i).rt_formula_asgn_id,
1347 x_calc_formula_id => p_calc_formula_id,
1348 x_rate_schedule_id => p_rt_assign_tbl(i).rate_schedule_id,
1349 x_start_date => p_rt_assign_tbl(i).start_date,
1350 x_end_date => p_rt_assign_tbl(i).end_date,
1351 x_object_version_number => p_rt_assign_tbl(i).object_version_number);
1352 END IF;
1353 END LOOP;
1354 END IF;
1355 */
1356 -- *********************************************************************
1357 -- ************ End - This code is not required in R12 *****************
1358 -- *********************************************************************
1359
1360 -- easier to check modeling after the fact since data would already be in tables
1361 if p_modeling_flag = 'Y' then
1362 check_modeling(p_calc_formula_id, p_cumulative_flag, p_output_exp_id, p_f_output_exp_id);
1363 end if;
1364
1365 -- make sure this formula wouldn't be involved in a cycle
1366 cn_calc_sql_exps_pvt.get_dependent_plan_elts
1367 (p_api_version => 1.0,
1368 p_node_type => 'F',
1369 p_node_id => p_calc_formula_id,
1370 x_plan_elt_id_tbl => l_plan_elt_tbl,
1371 x_return_status => x_return_status,
1372 x_msg_count => x_msg_count,
1373 x_msg_data => x_msg_data);
1374 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1375 RAISE FND_API.G_EXC_ERROR;
1376 end if;
1377
1378 -- Standard check of p_commit.
1379 IF FND_API.To_Boolean( p_commit ) THEN
1380 COMMIT WORK;
1381 END IF;
1382
1383 -- this runs on its own separate commit cycle
1384 IF fnd_api.to_boolean(p_generate_packages) THEN
1385 generate_formula(p_api_version => 1.0,
1386 p_calc_formula_id => p_calc_formula_id,
1387 p_formula_type => p_formula_type,
1388 p_trx_group_code => p_trx_group_code,
1389 p_number_dim => l_num_dim,
1390 p_itd_flag => p_itd_flag,
1391 p_perf_measure_id => p_perf_measure_id,
1392 p_output_exp_id => p_output_exp_id,
1393 p_f_output_exp_id => p_f_output_exp_id,
1394 x_formula_status => x_formula_status,
1395 --R12 MOAC Changes--Start
1396 p_org_id => p_org_id,
1397 --R12 MOAC Changes--End
1398 x_return_status => x_return_status,
1399 x_msg_count => x_msg_count,
1400 x_msg_data => x_msg_data);
1401 END IF;
1402 -- End of API body.
1403
1404 -- Standard call to get message count and if count is 1, get message info.
1405 FND_MSG_PUB.count_and_get
1406 (p_count => x_msg_count ,
1407 p_data => x_msg_data ,
1408 p_encoded => FND_API.G_FALSE );
1409 EXCEPTION
1410 WHEN FND_API.G_EXC_ERROR THEN
1411 ROLLBACK TO Update_Formula;
1412 x_return_status := FND_API.G_RET_STS_ERROR ;
1413 FND_MSG_PUB.count_and_get
1414 (p_count => x_msg_count ,
1415 p_data => x_msg_data ,
1416 p_encoded => FND_API.G_FALSE );
1417 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1418 ROLLBACK TO Update_Formula;
1419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1420 FND_MSG_PUB.count_and_get
1421 (p_count => x_msg_count ,
1422 p_data => x_msg_data ,
1423 p_encoded => FND_API.G_FALSE );
1424 WHEN OTHERS THEN
1425 ROLLBACK TO Update_Formula;
1426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1427 IF FND_MSG_PUB.check_msg_level
1428 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1429 THEN
1430 FND_MSG_PUB.add_exc_msg
1431 (G_PKG_NAME ,
1432 l_api_name );
1433 END IF;
1434 FND_MSG_PUB.count_and_get
1435 (p_count => x_msg_count ,
1436 p_data => x_msg_data ,
1437 p_encoded => FND_API.G_FALSE );
1438 END Update_Formula;
1439
1440 -- Notes : Delete a formula
1441 PROCEDURE Delete_Formula
1442 (p_api_version IN NUMBER ,
1443 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1444 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1445 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
1446 p_calc_formula_id IN CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE,
1447 p_org_id IN CN_CALC_FORMULAS.ORG_ID%TYPE, --SFP related change
1448 --R12 MOAC Changes--Start
1449 p_object_version_number IN CN_CALC_FORMULAS.OBJECT_VERSION_NUMBER%TYPE, --new
1450 --R12 MOAC Changes--End
1451 x_return_status OUT NOCOPY VARCHAR2 ,
1452 x_msg_count OUT NOCOPY NUMBER ,
1453 x_msg_data OUT NOCOPY VARCHAR2 ) IS
1454
1455 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Formula';
1456 l_api_version CONSTANT NUMBER := 1.0;
1457 l_dummy pls_integer;
1458 /* Start - R12 Notes History */
1459 l_formula_name VARCHAR2(30);
1460 l_org_id NUMBER := -999;
1461 l_note_msg VARCHAR2(240);
1462 l_note_id NUMBER;
1463 /* End - R12 Notes History */
1464
1465 CURSOR parent_exist IS
1466 SELECT 1
1467 FROM dual
1468 WHERE exists (SELECT 1 FROM cn_calc_edges
1469 WHERE child_id = p_calc_formula_id
1470 AND edge_type = 'FE');
1471 BEGIN
1472 -- Standard Start of API savepoint
1473 SAVEPOINT Delete_Formula;
1474 -- Standard call to check for call compatibility.
1475 IF NOT FND_API.Compatible_API_Call
1476 (l_api_version ,
1477 p_api_version ,
1478 l_api_name ,
1479 G_PKG_NAME )
1480 THEN
1481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1482 END IF;
1483 -- Initialize message list if p_init_msg_list is set to TRUE.
1484 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1485 FND_MSG_PUB.initialize;
1486 END IF;
1487 -- Initialize API return status to success
1488 x_return_status := FND_API.G_RET_STS_SUCCESS;
1489
1490 -- API body
1491
1492 -- make sure:
1493 -- 1) the formula is not used in cn_calc_edges
1494 -- 2) the formula is not assigned to a plan element or role
1495 -- 3) the formula is not used in modeling
1496
1497 OPEN parent_exist;
1498 FETCH parent_exist INTO l_dummy;
1499 CLOSE parent_exist;
1500
1501 IF (l_dummy = 1) THEN
1502 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1503 fnd_message.set_name('CN', 'CN_FORMULA_IN_USE');
1504 fnd_msg_pub.ADD;
1505 END IF;
1506 RAISE fnd_api.g_exc_error;
1507 END IF;
1508
1509 select count(1) into l_dummy
1510 from cn_quotas_v where calc_formula_id = p_calc_formula_id;
1511 if l_dummy > 0 THEN
1512 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1513 fnd_message.set_name('CN', 'CN_FORMULA_IN_USE');
1514 fnd_msg_pub.ADD;
1515 END IF;
1516 RAISE fnd_api.g_exc_error;
1517 END IF;
1518
1519 select count(1) into l_dummy
1520 from cn_calc_formulas f, cn_role_quota_cates r
1521 where f.calc_formula_id = p_calc_formula_id
1522 and f.calc_formula_id = r.calc_formula_id
1523 and f.modeling_flag = 'Y';
1524 if l_dummy > 0 THEN
1525 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1526 fnd_message.set_name('CN', 'CN_CANNOT_DEL_PLANNING_FORM');
1527 fnd_msg_pub.ADD;
1528 END IF;
1529 RAISE fnd_api.g_exc_error;
1530 END IF;
1531
1532 /* Start - R12 Notes History */
1533 --SELECT org_id, name INTO l_org_id, l_formula_name
1534 --FROM cn_calc_formulas
1535 --WHERE calc_formula_id = p_calc_formula_id;
1536
1537 SELECT name INTO l_formula_name
1538 FROM cn_calc_formulas
1539 WHERE calc_formula_id = p_calc_formula_id
1540 AND org_id = p_org_id;
1541
1542 /* End - R12 Notes History */
1543
1544 cn_calc_formulas_pkg.delete_row(p_calc_formula_id, p_org_id);
1545
1546 /* Start - R12 Notes History */
1547 IF (p_org_id <> -999) THEN
1548 fnd_message.set_name ('CN', 'CNR12_NOTE_FORMULA_DELETE');
1549 fnd_message.set_token ('FORMULA', l_formula_name);
1550 l_note_msg := fnd_message.get;
1551 jtf_notes_pub.create_note
1552 (p_api_version => 1.0,
1553 x_return_status => x_return_status,
1554 x_msg_count => x_msg_count,
1555 x_msg_data => x_msg_data,
1556 p_source_object_id => p_org_id,
1557 p_source_object_code => 'CN_DELETED_OBJECTS',
1558 p_notes => l_note_msg,
1559 p_notes_detail => l_note_msg,
1560 p_note_type => 'CN_SYSGEN', -- for system generated
1561 x_jtf_note_id => l_note_id -- returned
1562 );
1563 END IF;
1564 /* End - R12 Notes History */
1565
1566 -- delete formula inputs and rate table assignments
1567 DELETE FROM cn_formula_inputs WHERE calc_formula_id = p_calc_formula_id AND org_id = p_org_id;
1568 DELETE FROM cn_rt_formula_asgns WHERE calc_formula_id = p_calc_formula_id AND org_id = p_org_id;
1569
1570 -- delete formula packages and the records in cn_objects if they exist.
1571
1572 -- End of API body.
1573
1574 -- Standard check of p_commit.
1575 IF FND_API.To_Boolean( p_commit ) THEN
1576 COMMIT WORK;
1577 END IF;
1578 -- Standard call to get message count and if count is 1, get message info.
1579 FND_MSG_PUB.count_and_get
1580 (p_count => x_msg_count ,
1581 p_data => x_msg_data ,
1582 p_encoded => FND_API.G_FALSE );
1583 EXCEPTION
1584 WHEN FND_API.G_EXC_ERROR THEN
1585 ROLLBACK TO Delete_Formula;
1586 x_return_status := FND_API.G_RET_STS_ERROR ;
1587 FND_MSG_PUB.count_and_get
1588 (p_count => x_msg_count ,
1589 p_data => x_msg_data ,
1590 p_encoded => FND_API.G_FALSE );
1591 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1592 ROLLBACK TO Delete_Formula;
1593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1594 FND_MSG_PUB.count_and_get
1595 (p_count => x_msg_count ,
1596 p_data => x_msg_data ,
1597 p_encoded => FND_API.G_FALSE );
1598 WHEN OTHERS THEN
1599 ROLLBACK TO Delete_Formula;
1600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1601 IF FND_MSG_PUB.check_msg_level
1602 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1603 THEN
1604 FND_MSG_PUB.add_exc_msg
1605 (G_PKG_NAME ,
1606 l_api_name );
1607 END IF;
1608 FND_MSG_PUB.count_and_get
1609 (p_count => x_msg_count ,
1610 p_data => x_msg_data ,
1611 p_encoded => FND_API.G_FALSE );
1612 END Delete_Formula;
1613
1614
1615 -- Notes : Generate the PL/SQL packages for the given formula
1616 PROCEDURE generate_formula
1617 (p_api_version IN NUMBER ,
1618 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1619 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1620 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1621 p_calc_formula_id IN CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE,
1622 p_formula_type IN CN_CALC_FORMULAS.FORMULA_TYPE%TYPE,
1623 p_trx_group_code IN CN_CALC_FORMULAS.TRX_GROUP_CODE%TYPE,
1624 p_number_dim IN CN_CALC_FORMULAS.NUMBER_DIM%TYPE,
1625 p_itd_flag IN CN_CALC_FORMULAS.ITD_FLAG%TYPE,
1626 p_perf_measure_id IN CN_CALC_FORMULAS.PERF_MEASURE_ID%TYPE,
1627 p_output_exp_id IN CN_CALC_FORMULAS.OUTPUT_EXP_ID%TYPE,
1628 p_f_output_exp_id IN CN_CALC_FORMULAS.F_OUTPUT_EXP_ID%TYPE,
1629 x_formula_status OUT NOCOPY CN_CALC_FORMULAS.FORMULA_STATUS%TYPE,
1630 --R12 MOAC Changes--Start
1631 p_org_id IN CN_CALC_FORMULAS.ORG_ID%TYPE,
1632 --R12 MOAC Changes--End
1633 x_return_status OUT NOCOPY VARCHAR2 ,
1634 x_msg_count OUT NOCOPY NUMBER ,
1635 x_msg_data OUT NOCOPY VARCHAR2 ) IS
1636
1637 l_api_name CONSTANT VARCHAR2(30) := 'Generate_Formula';
1638 l_api_version CONSTANT NUMBER := 1.0;
1639
1640 l_process_audit_id NUMBER;
1641 l_input_count pls_integer := 0;
1642 l_dummy pls_integer;
1643 l_ii_flag VARCHAR2(30) := 'N';
1644 l_status CN_CALC_FORMULAS.FORMULA_STATUS%TYPE;
1645 l_f_status CN_CALC_FORMULAS.FORMULA_STATUS%TYPE;
1646 l_exp_type_code CN_CALC_SQL_EXPS.EXP_TYPE_CODE%TYPE;
1647 l_f_exp_type_code CN_CALC_SQL_EXPS.EXP_TYPE_CODE%TYPE;
1648 l_formula_type CN_CALC_FORMULAS.FORMULA_TYPE%TYPE;
1649 l_trx_group_code CN_CALC_FORMULAS.TRX_GROUP_CODE%TYPE;
1650 l_number_dim CN_CALC_FORMULAS.NUMBER_DIM%TYPE;
1651 l_itd_flag CN_CALC_FORMULAS.ITD_FLAG%TYPE;
1652 l_perf_measure_id CN_CALC_FORMULAS.PERF_MEASURE_ID%TYPE;
1653 l_output_exp_id CN_CALC_FORMULAS.OUTPUT_EXP_ID%TYPE;
1654 l_f_output_exp_id CN_CALC_FORMULAS.F_OUTPUT_EXP_ID%TYPE;
1655 l_count_formula_input NUMBER := 0;
1656 l_split_flag CN_CALC_FORMULAS.SPLIT_FLAG%TYPE;
1657
1658 --clku
1659 l_name CN_CALC_FORMULAS.NAME%TYPE;
1660
1661
1662 CURSOR formula_info IS
1663 SELECT
1664 formula_type,
1665 trx_group_code,
1666 number_dim,
1667 itd_flag,
1668 perf_measure_id,
1669 output_exp_id,
1670 f_output_exp_id
1671 FROM cn_calc_formulas
1672 WHERE calc_formula_id = p_calc_formula_id;
1673
1674 CURSOR formula_split_info IS
1675 SELECT
1676 split_flag
1677 FROM cn_calc_formulas
1678 WHERE calc_formula_id = p_calc_formula_id;
1679
1680
1681 CURSOR perf_measure IS
1682 SELECT status, exp_type_code
1683 FROM cn_calc_sql_exps
1684 WHERE calc_sql_exp_id = l_perf_measure_id;
1685
1686 CURSOR inputs IS
1687 SELECT a.status, a.exp_type_code, b.status f_status,
1688 b.exp_type_code f_exp_type_code
1689 FROM cn_calc_sql_exps a,
1690 cn_calc_sql_exps b,
1691 cn_formula_inputs c
1692 WHERE a.calc_sql_exp_id = c.calc_sql_exp_id
1693 AND b.calc_sql_exp_id(+) = c.f_calc_sql_exp_id
1694 AND c.calc_formula_id = p_calc_formula_id;
1695
1696 CURSOR output IS
1697 SELECT status, exp_type_code
1698 FROM cn_calc_sql_exps
1699 WHERE calc_sql_exp_id = l_output_exp_id;
1700
1701 CURSOR f_output IS
1702 SELECT status, exp_type_code
1703 FROM cn_calc_sql_exps
1704 WHERE calc_sql_exp_id = l_f_output_exp_id;
1705
1706 CURSOR check_dimensions IS
1707 SELECT 1
1708 FROM cn_rate_schedules
1709 WHERE number_dim <> l_number_dim
1710 AND rate_schedule_id IN (SELECT rate_schedule_id
1711 FROM cn_rt_formula_asgns
1712 WHERE calc_formula_id = p_calc_formula_id);
1713 BEGIN
1714 -- Standard Start of API savepoint
1715 SAVEPOINT Generate_Formula;
1716 -- Standard call to check for call compatibility.
1717 IF NOT FND_API.Compatible_API_Call
1718 (l_api_version ,
1719 p_api_version ,
1720 l_api_name ,
1721 G_PKG_NAME )
1722 THEN
1723 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1724 END IF;
1725 -- Initialize message list if p_init_msg_list is set to TRUE.
1726 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1727 FND_MSG_PUB.initialize;
1728 END IF;
1729 -- Initialize API return status to success
1730 x_return_status := FND_API.G_RET_STS_SUCCESS;
1731
1732 -- API body
1733
1734 -- get formula info if not provided by caller
1735 IF (p_formula_type = fnd_api.g_miss_char OR
1736 p_trx_group_code = fnd_api.g_miss_char OR
1737 p_number_dim = fnd_api.g_miss_num OR
1738 p_itd_flag = fnd_api.g_miss_char OR
1739 p_perf_measure_id = fnd_api.g_miss_num OR
1740 p_output_exp_id = fnd_api.g_miss_num OR
1741 p_f_output_exp_id = fnd_api.g_miss_num)
1742 THEN
1743 OPEN formula_info;
1744 FETCH formula_info INTO
1745 l_formula_type,
1746 l_trx_group_code,
1747 l_number_dim,
1748 l_itd_flag,
1749 l_perf_measure_id,
1750 l_output_exp_id,
1751 l_f_output_exp_id;
1752 CLOSE formula_info;
1753 ELSE
1754 l_formula_type := p_formula_type;
1755 l_trx_group_code := p_trx_group_code;
1756 l_number_dim := p_number_dim;
1757 l_itd_flag := p_itd_flag;
1758 l_perf_measure_id := p_perf_measure_id;
1759 l_output_exp_id := p_output_exp_id;
1760 l_f_output_exp_id := p_f_output_exp_id;
1761 END IF;
1762
1763 if l_perf_measure_id is not null then
1764 -- if a perf measure is assigned then make sure that the
1765 -- perf_measure assigned matches this formula
1766 OPEN perf_measure;
1767 FETCH perf_measure INTO l_status, l_exp_type_code;
1768 CLOSE perf_measure;
1769
1770 IF (l_status <> 'VALID') THEN
1771 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1772 fnd_message.set_name('CN', 'CN_INVALID_PERF');
1773 fnd_msg_pub.ADD;
1774 END IF;
1775 RAISE fnd_api.g_exc_error;
1776 ELSE
1777 IF (l_formula_type = 'C') THEN
1778 IF (l_exp_type_code NOT IN ('IIIOIPGP', 'IRIOIPGOGPBIBOBPFRFO', 'IRIOIPGOGPBIBOBPFRFODDT')) THEN
1779 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1780 fnd_message.set_name('CN', 'CN_UNMATCHED_PERF');
1781 fnd_msg_pub.ADD;
1782 END IF;
1783 RAISE fnd_api.g_exc_error;
1784 END IF;
1785 ELSIF (l_formula_type = 'B') THEN
1786 IF (l_exp_type_code NOT IN ('IRIOIPGOGPBIBOBPFRFO', 'IRIOIPGOGPBIBOBPFRFODDT', 'IIIOIPGOGPBIBOBP')) THEN
1787 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1788 fnd_message.set_name('CN', 'CN_UNMATCHED_PERF');
1789 fnd_msg_pub.ADD;
1790 END IF;
1791 RAISE fnd_api.g_exc_error;
1792 END IF;
1793 END IF;
1794 END IF;
1795 END IF;
1796
1797 -- check and make sure that the output expression matches this formula
1798 l_status := NULL;
1799 l_exp_type_code := NULL;
1800 OPEN output;
1801 FETCH output INTO l_status, l_exp_type_code;
1802 CLOSE output;
1803
1804 OPEN f_output;
1805 FETCH f_output INTO l_f_status, l_f_exp_type_code;
1806 CLOSE f_output;
1807
1808 IF (l_status <> 'VALID' OR
1809 l_exp_type_code IS NULL OR
1810 (l_f_output_exp_id IS NOT NULL AND (l_f_status <> 'VALID' OR l_f_exp_type_code IS NULL))) THEN
1811 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1812 fnd_message.set_name('CN', 'CN_INVALID_OUTPUT');
1813 fnd_msg_pub.ADD;
1814 END IF;
1815 RAISE fnd_api.g_exc_error;
1816 ELSE
1817 IF (l_formula_type = 'C' AND l_trx_group_code = 'INDIVIDUAL' AND l_itd_flag = 'Y') THEN
1818 IF (l_exp_type_code NOT IN ('IO', 'IIIO', 'IOGOBOFO', 'IIIOIPGP', 'IOGOBO', 'IRIOIPGOGPBIBOBPFRFO', 'IRIOIPGOGPBIBOBPFRFODDT', 'IIIOIPGOGPBIBOBP')) THEN
1819 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1820 fnd_message.set_name('CN', 'CN_UNMATCHED_OUTPUT');
1821 fnd_msg_pub.ADD;
1822 END IF;
1823 RAISE fnd_api.g_exc_error;
1824 END IF;
1825 ELSIF (l_formula_type = 'C' AND l_trx_group_code = 'INDIVIDUAL' AND l_itd_flag = 'N') THEN
1826 IF (l_exp_type_code NOT IN ('IO', 'IO_ITDN', 'IIIO', 'IIIOIPGP', 'IOGOBOFO', 'IOGOBO', 'IRIOIPGOGPBIBOBPFRFO', 'IRIOIPGOGPBIBOBPFRFODDT', 'IIIOIPGOGPBIBOBP')) THEN
1827 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1828 fnd_message.set_name('CN', 'CN_UNMATCHED_OUTPUT');
1829 fnd_msg_pub.ADD;
1830 END IF;
1831 RAISE fnd_api.g_exc_error;
1832 END IF;
1833 ELSIF (l_formula_type = 'C' AND l_trx_group_code = 'GROUP') THEN
1834 IF (l_exp_type_code NOT IN ('GO', 'GIGO', 'IOGOBOFO', 'IRIOIPGOGPBIBOBPFRFO', 'IRIOIPGOGPBIBOBPFRFODDT', 'IOGOBO', 'IIIOIPGOGPBIBOBP')) THEN
1835 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1836 fnd_message.set_name('CN', 'CN_UNMATCHED_OUTPUT');
1837 fnd_msg_pub.ADD;
1838 END IF;
1839 RAISE fnd_api.g_exc_error;
1840 END IF;
1841 ELSIF (l_formula_type = 'B') THEN
1842 IF (l_exp_type_code NOT IN ('IOGOBOFO', 'IRIOIPGOGPBIBOBPFRFO', 'IRIOIPGOGPBIBOBPFRFODDT', 'IOGOBO', 'IIIOIPGOGPBIBOBP')) THEN
1843 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1844 fnd_message.set_name('CN', 'CN_UNMATCHED_OUTPUT');
1845 fnd_msg_pub.ADD;
1846 END IF;
1847 RAISE fnd_api.g_exc_error;
1848 END IF;
1849 END IF;
1850 END IF;
1851
1852 -- check and make sure that number_dim is correct and input expressions match this formula
1853 FOR input IN inputs LOOP
1854 IF (input.status <> 'VALID' OR input.exp_type_code IS NULL) THEN
1855 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1856 fnd_message.set_name('CN', 'CN_INVALID_INPUT');
1857 fnd_msg_pub.ADD;
1858 END IF;
1859 RAISE fnd_api.g_exc_error;
1860 ELSE
1861 IF (l_formula_type = 'C' AND l_trx_group_code = 'INDIVIDUAL') THEN
1862 IF (input.exp_type_code NOT IN ('IIIOIPGP', 'IIIO', 'IRIOIPGOGPBIBOBPFRFO', 'IRIOIPGOGPBIBOBPFRFODDT', 'IIIOIPGOGPBIBOBP')) THEN
1863 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1864 fnd_message.set_name('CN', 'CN_UNMATCHED_INPUT');
1865 fnd_msg_pub.ADD;
1866 END IF;
1867 RAISE fnd_api.g_exc_error;
1868 END IF;
1869
1870 IF (input.exp_type_code IN ('IIIOIPGP', 'IIIO', 'IIIOIPGOGPBIBOBP')) THEN
1871 l_ii_flag := 'Y';
1872 END IF;
1873 ELSIF (l_formula_type = 'C' AND l_trx_group_code = 'GROUP') THEN
1874 IF (input.exp_type_code <> 'GIGO') THEN
1875 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1876 fnd_message.set_name('CN', 'CN_UNMATCHED_INPUT');
1877 fnd_msg_pub.ADD;
1878 END IF;
1879 RAISE fnd_api.g_exc_error;
1880 END IF;
1881 l_ii_flag := 'Y';
1882 ELSIF (l_formula_type = 'B') THEN
1883 IF (input.exp_type_code NOT IN ('IRIOIPGOGPBIBOBPFRFO', 'IRIOIPGOGPBIBOBPFRFODDT', 'IIIOIPGOGPBIBOBP')) THEN
1884 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1885 fnd_message.set_name('CN', 'CN_UNMATCHED_INPUT');
1886 fnd_msg_pub.ADD;
1887 END IF;
1888 RAISE fnd_api.g_exc_error;
1889 END IF;
1890 l_ii_flag := 'Y';
1891 END IF;
1892 END IF;
1893
1894 l_input_count := l_input_count + 1;
1895 END LOOP;
1896
1897 -- Commission type formulas with trx_group_code = 'INDIVIDUAL' must have at
1898 -- least one column from cn_commission_lines/headers
1899 -- in one of its input definition
1900 IF (l_ii_flag <> 'Y') THEN
1901 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1902 fnd_message.set_name('CN', 'CN_NO_LINE_HEADER');
1903 fnd_msg_pub.ADD;
1904 END IF;
1905 RAISE fnd_api.g_exc_error;
1906 END IF;
1907
1908 -- changes for BUG#2797926
1909 OPEN formula_split_info;
1910 FETCH formula_split_info INTO l_split_flag;
1911 CLOSE formula_split_info;
1912 IF (l_split_flag = 'Y') THEN
1913
1914 SELECT count(*)
1915 INTO l_count_formula_input
1916 FROM cn_formula_inputs
1917 WHERE calc_formula_id = p_calc_formula_id
1918 AND split_flag = 'Y';
1919
1920 IF l_count_formula_input = 0 THEN
1921 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1922 fnd_message.set_name('CN', 'CN_NO_SPLIT_INPUT_EXP');
1923 fnd_msg_pub.ADD;
1924 END IF;
1925 RAISE fnd_api.g_exc_error;
1926 END IF;
1927 END IF;
1928
1929 IF (l_input_count <> l_number_dim) THEN
1930 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1931 fnd_message.set_name('CN', 'CN_WRONG_NUMBER_DIM');
1932 fnd_msg_pub.ADD;
1933 END IF;
1934 RAISE fnd_api.g_exc_error;
1935 END IF;
1936
1937 -- check and make sure that the number of inputs matches the number of
1938 -- dimensions in the assigned rate tables.
1939 OPEN check_dimensions;
1940 FETCH check_dimensions INTO l_dummy;
1941 CLOSE check_dimensions;
1942 IF (l_dummy = 1) THEN
1943 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1944 fnd_message.set_name('CN', 'CN_RT_NOT_MATCH');
1945 fnd_msg_pub.ADD;
1946 END IF;
1947 RAISE fnd_api.g_exc_error;
1948 END IF;
1949
1950 cn_formula_gen_pkg.generate_formula
1951 (p_api_version => 1.0,
1952 x_return_status => x_return_status,
1953 x_msg_count => x_msg_count,
1954 x_msg_data => x_msg_data,
1955 p_formula_id => p_calc_formula_id,
1956 --R12 MOAC Changes--Start
1957 p_org_id => p_org_id,
1958 --R12 MOAC Changes--End
1959 x_process_audit_id => l_process_audit_id);
1960
1961 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
1962 x_formula_status := 'COMPLETE';
1963
1964 -- clku, bug 2805095, call mark event here instead of call in table trigger
1965 select name into
1966 l_name
1967 from cn_calc_formulas
1968 where calc_formula_id = p_calc_formula_id;
1969
1970 cn_mark_events_pkg.mark_event_formula
1971 (p_event_name => 'CHANGE_FORMULA',
1972 p_object_name => l_name,
1973 p_object_id => p_calc_formula_id,
1974 p_start_date => NULL,
1975 p_start_date_old => NULL,
1976 p_end_date => NULL,
1977 p_end_date_old => NULL,
1978 --R12 MOAC Changes--Start
1979 p_org_id => p_org_id
1980 --R12 MOAC Changes--End
1981 );
1982
1983 ELSE
1984 x_formula_status := 'INCOMPLETE';
1985 END IF;
1986
1987 -- End of API body.
1988
1989 -- Standard check of p_commit.
1990 IF FND_API.To_Boolean( p_commit ) THEN
1991 COMMIT WORK;
1992 END IF;
1993 -- Standard call to get message count and if count is 1, get message info.
1994 FND_MSG_PUB.Count_And_Get
1995 (p_count => x_msg_count ,
1996 p_data => x_msg_data ,
1997 p_encoded => FND_API.G_FALSE );
1998 EXCEPTION
1999 WHEN FND_API.G_EXC_ERROR THEN
2000 ROLLBACK TO Generate_Formula;
2001 x_return_status := FND_API.G_RET_STS_ERROR ;
2002 FND_MSG_PUB.count_and_get
2003 (p_count => x_msg_count ,
2004 p_data => x_msg_data ,
2005 p_encoded => FND_API.G_FALSE );
2006 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2007 ROLLBACK TO Generate_Formula;
2008 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2009 FND_MSG_PUB.count_and_get
2010 (p_count => x_msg_count ,
2011 p_data => x_msg_data ,
2012 p_encoded => FND_API.G_FALSE );
2013 WHEN OTHERS THEN
2014 ROLLBACK TO Generate_Formula;
2015 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2016 IF FND_MSG_PUB.check_msg_level
2017 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2018 THEN
2019 FND_MSG_PUB.add_exc_msg
2020 (G_PKG_NAME ,
2021 l_api_name );
2022 END IF;
2023 FND_MSG_PUB.count_and_get
2024 (p_count => x_msg_count ,
2025 p_data => x_msg_data ,
2026 p_encoded => FND_API.G_FALSE );
2027 END generate_formula;
2028
2029 END CN_CALC_FORMULAS_PVT;