[Home] [Help]
PACKAGE BODY: APPS.CN_RATE_DIMENSIONS_PVT
Source
1 PACKAGE BODY CN_RATE_DIMENSIONS_PVT AS
2 /*$Header: cnvrdimb.pls 120.9 2007/08/08 19:21:44 jxsingh ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) :='CN_RATE_DIMENSIONS_PVT';
5
6 -- validate dimension name and dim_unit_code
7 PROCEDURE validate_dimension
8 (p_rate_dimension_id IN CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE := NULL,
9 p_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
10 p_dim_unit_code IN CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE,
11 p_number_tier IN CN_RATE_DIMENSIONS.NUMBER_TIER%TYPE,
12 p_tiers_tbl IN tiers_tbl_type := g_miss_tiers_tbl,
13 --R12 MOAC Changes--Start
14 p_org_id IN CN_RATE_DIMENSIONS.ORG_ID%TYPE)
15 --R12 MOAC Changes--End
16 IS
17 l_prompt CN_LOOKUPS.MEANING%TYPE;
18 l_dummy NUMBER;
19
20 CURSOR exp_info(p_calc_sql_exp_id NUMBER) IS
21 SELECT 0
22 FROM dual
23 WHERE NOT exists (SELECT 1 FROM cn_calc_sql_exps WHERE calc_sql_exp_id = p_calc_sql_exp_id)
24 UNION ALL
25 SELECT 1
26 FROM cn_calc_sql_exps
27 WHERE calc_sql_exp_id = p_calc_sql_exp_id
28 AND exp_type_code like '%DDT%'
29 UNION ALL
30 SELECT 2
31 FROM cn_calc_sql_exps
32 WHERE calc_sql_exp_id = p_calc_sql_exp_id
33 AND (exp_type_code IS NULL OR exp_type_code NOT LIKE '%DDT%');
34
35 CURSOR name_exists IS
36 SELECT 1
37 FROM cn_rate_dimensions
38 WHERE name = p_name
39 AND (p_rate_dimension_id IS NULL OR p_rate_dimension_id <> rate_dimension_id)
40 --R12 MOAC Changes--Start
41 AND org_id = p_org_id;
42 --R12 MOAC Changes--End
43 BEGIN
44 IF (p_name IS NULL) THEN
45 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
46 l_prompt := cn_api.get_lkup_meaning('DIMENSION_NAME', 'CN_PROMPTS');
47 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
48 fnd_message.set_token('OBJ_NAME', l_prompt);
49 fnd_msg_pub.ADD;
50 END IF;
51 RAISE fnd_api.g_exc_error;
52 END IF;
53
54 OPEN name_exists;
55 FETCH name_exists INTO l_dummy;
56 CLOSE name_exists;
57
58 IF (l_dummy = 1) THEN
59 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
60 fnd_message.set_name('CN', 'CN_NAME_NOT_UNIQUE');
61 fnd_msg_pub.ADD;
62 END IF;
63 RAISE fnd_api.g_exc_error;
64 END IF;
65
66 -- validate dim_unit_code
67 IF (p_dim_unit_code NOT IN ('AMOUNT', 'PERCENT', 'STRING', 'EXPRESSION')) THEN
68 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
69 fnd_message.set_name('CN', 'CN_INVALID_DIM_UOM');
70 fnd_msg_pub.ADD;
71 END IF;
72 RAISE fnd_api.g_exc_error;
73 END IF;
74
75 -- if p_tiers_tbl is not empty, then p_number_tier should be equal to the number of records in p_tiers_tbl
76 IF (p_tiers_tbl.COUNT > 0 AND p_number_tier <> p_tiers_tbl.count) THEN
77 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
78 fnd_message.set_name('CN', 'CN_X_NUMBER_TIER');
79 fnd_msg_pub.ADD;
80 END IF;
81 RAISE fnd_api.g_exc_error;
82 END IF;
83
84 -- 3. if p_dim_unit_code is AMOUNT or PERCENT, then min_amount > max_amount
85 IF (p_dim_unit_code IN ('AMOUNT', 'PERCENT') AND p_tiers_tbl.COUNT > 0) THEN
86 FOR i IN p_tiers_tbl.first..p_tiers_tbl.last LOOP
87 -- if minimum_amount is greater than maximum_amount, then error
88 IF (p_tiers_tbl(i).minimum_amount >= p_tiers_tbl(i).maximum_amount) THEN
89 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
90 fnd_message.set_name('CN', 'CN_MIN_G_MAX');
91 fnd_msg_pub.ADD;
92 END IF;
93 RAISE fnd_api.g_exc_error;
94 END IF;
95
96 -- if minimum_amount is not equal to previous tier's maximum_amount, then error
97 IF (i > 1 AND p_tiers_tbl(i).minimum_amount <> p_tiers_tbl(i-1).maximum_amount) THEN
98 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
99 fnd_message.set_name('CN', 'CN_MIN_NE_MAX');
100 fnd_msg_pub.ADD;
101 END IF;
102 RAISE fnd_api.g_exc_error;
103 END IF;
104 END LOOP;
105 END IF;
106
107 -- if p_dim_unit_code is EXPRESSION, min_exp_id and max_exp_id should be
108 -- foreign keys to cn_calc_sql_exps
109 -- and exp_type_code should be available for dynamic dimensions
110 IF (p_dim_unit_code = 'EXPRESSION' AND p_tiers_tbl.COUNT > 0) THEN
111 FOR i IN p_tiers_tbl.first..p_tiers_tbl.last LOOP
112 OPEN exp_info(p_tiers_tbl(i).min_exp_id);
113 FETCH exp_info INTO l_dummy;
114 CLOSE exp_info;
115
116 IF (l_dummy = 0) THEN
117 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
118 fnd_message.set_name('CN', 'CN_EXP_NOT_EXIST');
119 fnd_msg_pub.ADD;
120 END IF;
121 RAISE fnd_api.g_exc_error;
122 ELSIF (l_dummy = 2) THEN
123 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
124 fnd_message.set_name('CN', 'CN_EXP_NOT_MATCH');
125 fnd_msg_pub.ADD;
126 END IF;
127 RAISE fnd_api.g_exc_error;
128 END IF;
129
130 OPEN exp_info(p_tiers_tbl(i).max_exp_id);
131 FETCH exp_info INTO l_dummy;
132 CLOSE exp_info;
133
134 IF (l_dummy = 0) THEN
135 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
136 fnd_message.set_name('CN', 'CN_EXP_NOT_EXIST');
137 fnd_msg_pub.ADD;
138 END IF;
139 RAISE fnd_api.g_exc_error;
140 ELSIF (l_dummy = 2) THEN
141 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
142 fnd_message.set_name('CN', 'CN_EXP_NOT_MATCH');
143 fnd_msg_pub.ADD;
144 END IF;
145 RAISE fnd_api.g_exc_error;
146 END IF;
147 END LOOP;
148 END IF;
149
150 END validate_dimension;
151
152 -- Notes : Create rate dimensions and dimension tiers
153 -- 1) Validate dimension name (should be unique)
154 -- 2) Validate dim_unit_code (valid values are AMOUNT,
155 -- PERCENT, STRING, EXPRESSION)
156 -- 3) Validate number_tier which should equal the number of
157 -- tiers in p_tiers_tbl if it is not empty
158 -- 4) Validate dimension tiers (max_amount > min_amount)
159 PROCEDURE Create_Dimension
160 (p_api_version IN NUMBER ,
161 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
162 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
163 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
164 p_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
165 p_description IN CN_RATE_DIMENSIONS.DESCRIPTION%TYPE := NULL,
166 p_dim_unit_code IN CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE,
167 p_number_tier IN CN_RATE_DIMENSIONS.NUMBER_TIER%TYPE, -- not used
168 p_tiers_tbl IN tiers_tbl_type := g_miss_tiers_tbl,
169 --R12 MOAC Changes--Start
170 p_org_id IN CN_RATE_DIMENSIONS.ORG_ID%TYPE, --new
171 x_rate_dimension_id IN OUT NOCOPY CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE, --changed
172 --R12 MOAC Changes--End
173 x_return_status OUT NOCOPY VARCHAR2 ,
174 x_msg_count OUT NOCOPY NUMBER ,
175 x_msg_data OUT NOCOPY VARCHAR2 )
176 IS
177 l_api_name CONSTANT VARCHAR2(30) := 'Create_Dimension';
178 l_api_version CONSTANT NUMBER := 1.0;
179
180 l_temp_id CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE;
181 l_number_tier CN_RATE_DIMENSIONS.NUMBER_TIER%TYPE;
182
183 --R12 Notes Hoistory
184 l_dimension_name VARCHAR2(30);
185 l_note_msg VARCHAR2(240);
186 l_note_id NUMBER;
187
188 BEGIN
189 -- Standard Start of API savepoint
190 SAVEPOINT Create_Dimension;
191 -- Standard call to check for call compatibility.
192 IF NOT FND_API.Compatible_API_Call
193 (l_api_version ,
194 p_api_version ,
195 l_api_name ,
196 G_PKG_NAME )
197 THEN
198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
199 END IF;
200 -- Initialize message list if p_init_msg_list is set to TRUE.
201 IF FND_API.to_Boolean( p_init_msg_list ) THEN
202 FND_MSG_PUB.initialize;
203 END IF;
204 -- Initialize API return status to success
205 x_return_status := FND_API.G_RET_STS_SUCCESS;
206
207 -- API body
208
209 -- calculate number of tiers (p_number_tier not used)
210 -- set number_tier := number of tiers of p_tiers_tbl
211 l_number_tier := p_tiers_tbl.count;
212
213 validate_dimension(NULL,
214 p_name,
215 p_dim_unit_code,
216 l_number_tier,
217 p_tiers_tbl,
218 --R12 MOAC Changes--Start
219 p_org_id);
220 --R12 MOAC Changes--End
221
222 -- call table handler to create dimension record in cn_rate_dimensions
223 cn_rate_dimensions_pkg.insert_row
224 (x_rate_dimension_id => x_rate_dimension_id,
225 x_name => p_name,
226 x_description => p_description,
227 x_dim_unit_code => p_dim_unit_code,
228 x_number_tier => l_number_tier,
229 --R12 MOAC Changes--Start
230 x_org_id => p_org_id
231 --R12 MOAC Changes--End
232 );
233
234 -- *********************************************************************
235 -- ************ Start - R12 Notes History ************** ***************
236 -- *********************************************************************
237 select name into l_dimension_name
238 from cn_rate_dimensions
239 where rate_dimension_id = x_rate_dimension_id;
240
241 fnd_message.set_name('CN', 'CNR12_NOTE_RT_DIM_CREATE');
242 fnd_message.set_token('RT_DIM', l_dimension_name);
243 l_note_msg := fnd_message.get;
244
245 jtf_notes_pub.create_note
246 (p_api_version => 1.0,
247 x_return_status => x_return_status,
248 x_msg_count => x_msg_count,
249 x_msg_data => x_msg_data,
250 p_source_object_id => x_rate_dimension_id,
251 p_source_object_code => 'CN_RATE_DIMENSIONS',
252 p_notes => l_note_msg,
253 p_notes_detail => l_note_msg,
254 p_note_type => 'CN_SYSGEN', -- for system generated
255 x_jtf_note_id => l_note_id -- returned
256 );
257
258 -- *********************************************************************
259 -- ************ End - R12 Notes History **************** ***************
260 -- *********************************************************************
261
262 -- *********************************************************************
263 -- ************ Start - This code is not required in R12 ***************
264 -- *** Start - This code is introduced back in R12+ Import Plan Copy ***
265 -- *********************************************************************
266 -- Start - Bug#6325544 fixed for Import Plan Copy
267
268 -- call table handler to create dimension tiers
269 IF (p_tiers_tbl.COUNT > 0) THEN
270 FOR i IN p_tiers_tbl.first..p_tiers_tbl.last LOOP
271 l_temp_id := NULL;
272 cn_rate_dim_tiers_pkg.insert_row
273 (x_rate_dim_tier_id => l_temp_id,
274 x_rate_dimension_id => x_rate_dimension_id,
275 x_minimum_amount => p_tiers_tbl(i).minimum_amount,
276 x_maximum_amount => p_tiers_tbl(i).maximum_amount,
277 x_min_exp_id => p_tiers_tbl(i).min_exp_id,
278 x_max_exp_id => p_tiers_tbl(i).max_exp_id,
279 x_string_value => p_tiers_tbl(i).string_value,
280 x_tier_sequence => p_tiers_tbl(i).tier_sequence,
281 --R12 MOAC Changes--Start
282 x_org_id => p_org_id
283 --R12 MOAC Changes--End
284 );
285 END LOOP;
286 END IF;
287
288 -- End - Bug#6325544 fixed for Import Plan Copy
289 -- *********************************************************************
290 -- **** End - This code is introduced back in R12+ Import Plan Copy ****
291 -- ************ End - This code is not required in R12 *****************
292 -- *********************************************************************
293 -- End of API body.
294
295 -- Standard check of p_commit.
296 IF FND_API.To_Boolean( p_commit ) THEN
297 COMMIT WORK;
298 END IF;
299 -- Standard call to get message count and if count is 1, get message info.
300 FND_MSG_PUB.Count_And_Get
301 (p_count => x_msg_count ,
302 p_data => x_msg_data ,
303 p_encoded => FND_API.G_FALSE );
304 EXCEPTION
305 WHEN FND_API.G_EXC_ERROR THEN
306 ROLLBACK TO Create_Dimension;
307 x_return_status := FND_API.G_RET_STS_ERROR ;
308 FND_MSG_PUB.count_and_get
309 (p_count => x_msg_count ,
310 p_data => x_msg_data ,
311 p_encoded => FND_API.G_FALSE );
312 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
313 ROLLBACK TO Create_Dimension;
314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
315 FND_MSG_PUB.count_and_get
316 (p_count => x_msg_count ,
317 p_data => x_msg_data ,
318 p_encoded => FND_API.G_FALSE );
319 WHEN OTHERS THEN
320 ROLLBACK TO Create_Dimension;
321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
322 IF FND_MSG_PUB.check_msg_level
323 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
324 THEN
325 FND_MSG_PUB.add_exc_msg
326 (G_PKG_NAME ,
327 l_api_name );
328 END IF;
329 FND_MSG_PUB.count_and_get
330 (p_count => x_msg_count ,
331 p_data => x_msg_data ,
332 p_encoded => FND_API.G_FALSE );
333 END Create_Dimension;
334
335 -- Notes : Update rate dimensions and dimension tiers
336 -- 1) Validate dimension name (should be unique)
337 -- 2) Validate dim_unit_code (valid values are AMOUNT,
338 -- PERCENT, STRING, EXPRESSION)
339 -- 3) Validate number_tier which should equal the number of
340 -- tiers in p_tiers_tbl if it is not empty
341 -- 4) Validate dimension tiers (max_amount > min_amount)
342 -- 5) Insert new tiers and delete obsolete tiers
343 -- 6) If this dimension is used in a rate table which is in
344 -- turn used in a formula, then dim_unit_code
345 -- can not be updated
346 PROCEDURE Update_Dimension
347 (p_api_version IN NUMBER ,
348 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
349 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
350 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
351 p_rate_dimension_id IN CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE,
352 p_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
353 p_description IN CN_RATE_DIMENSIONS.DESCRIPTION%TYPE := NULL,
354 p_dim_unit_code IN CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE,
355 p_number_tier IN CN_RATE_DIMENSIONS.NUMBER_TIER%TYPE, -- not used
356 p_tiers_tbl IN tiers_tbl_type := g_miss_tiers_tbl,
357 --R12 MOAC Changes--Start
358 p_org_id IN CN_RATE_DIMENSIONS.ORG_ID%TYPE, --new
359 p_object_version_number IN OUT NOCOPY CN_RATE_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE, --Changed
360 --R12 MOAC Changes--End
361 x_return_status OUT NOCOPY VARCHAR2 ,
362 x_msg_count OUT NOCOPY NUMBER ,
363 x_msg_data OUT NOCOPY VARCHAR2 )
364 IS
365 l_api_name CONSTANT VARCHAR2(30) := 'Update_Dimension';
366 l_api_version CONSTANT NUMBER := 1.0;
367
368 l_temp_id CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE;
369 l_dim_unit_code CN_RATE_DIM_TIERS.DIM_UNIT_CODE%TYPE;
370 l_delete_flag VARCHAR2(1);
371 l_dummy NUMBER;
372 l_number_tier CN_RATE_DIMENSIONS.NUMBER_TIER%TYPE;
373
374 --R12 Notes Hoistory
375 l_dimension_name_old VARCHAR2(30);
376 l_type_old VARCHAR2(30);
377 l_note_msg VARCHAR2(240);
378 l_note_id NUMBER;
379 l_consolidated_note VARCHAR2(2000);
380 CURSOR dim_unit_code IS
381 SELECT dim_unit_code
382 FROM cn_rate_dimensions
383 WHERE rate_dimension_id = p_rate_dimension_id;
384
385 CURSOR formula_info IS
386 SELECT 1
387 FROM dual
388 WHERE exists (SELECT 1
389 FROM cn_rate_sch_dims rsd
390 WHERE rsd.rate_dimension_id = p_rate_dimension_id
391 AND exists (SELECT 1
392 FROM cn_rt_formula_asgns
393 WHERE rate_schedule_id = rsd.rate_schedule_id));
394
395 CURSOR db_tiers IS
396 SELECT rate_dim_tier_id
397 FROM cn_rate_dim_tiers
398 WHERE rate_dimension_id = p_rate_dimension_id;
399 BEGIN
400 -- Standard Start of API savepoint
401 SAVEPOINT Update_Dimension;
402 -- Standard call to check for call compatibility.
403 IF NOT FND_API.Compatible_API_Call
404 (l_api_version ,
405 p_api_version ,
406 l_api_name ,
407 G_PKG_NAME )
408 THEN
409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
410 END IF;
411 -- Initialize message list if p_init_msg_list is set to TRUE.
412 IF FND_API.to_Boolean( p_init_msg_list ) THEN
413 FND_MSG_PUB.initialize;
414 END IF;
415 -- Initialize API return status to success
416 x_return_status := FND_API.G_RET_STS_SUCCESS;
417
418 -- API body
419 -- calculate number of tiers (p_number_tier not used)
420 -- set number_tier := number of tiers of p_tiers_tbl
421 l_number_tier := p_tiers_tbl.count;
422
423 validate_dimension(p_rate_dimension_id,
424 p_name,
425 p_dim_unit_code,
426 l_number_tier,
427 p_tiers_tbl,
428 --R12 MOAC Changes--Start
429 p_org_id);
430 --R12 MOAC Changes--End
431
432 OPEN dim_unit_code;
433 FETCH dim_unit_code INTO l_dim_unit_code;
434 CLOSE dim_unit_code;
435
436 IF (l_dim_unit_code <> p_dim_unit_code) THEN
437 OPEN formula_info;
438 FETCH formula_info INTO l_dummy;
439 CLOSE formula_info;
440
441 -- if it is used in a formula, then can not update dim_unit_code
442 IF (l_dummy = 1) THEN
443 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
444 fnd_message.set_name('CN', 'CN_X_UPDATE_DUC1');
445 fnd_msg_pub.ADD;
446 END IF;
447 RAISE fnd_api.g_exc_error;
448 END IF;
449
450 -- dim_unit_code can be changed only between AMOUNT and PERCENT
451 IF (p_dim_unit_code NOT IN ('AMOUNT', 'PERCENT') OR
452 l_dim_unit_code NOT IN ('AMOUNT', 'PERCENT')) THEN
453 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
454 fnd_message.set_name('CN', 'CN_X_UPDATE_DUC2');
455 fnd_msg_pub.ADD;
456 END IF;
457 RAISE fnd_api.g_exc_error;
458 END IF;
459 END IF;
460
461 -- *********************************************************************
462 -- ************ Start - This code is not required in R12 ***************
463 -- *********************************************************************
464 /*
465 IF (p_tiers_tbl.COUNT > 0) THEN
466 -- delete the obsolete tiers
467 FOR db_tier IN db_tiers LOOP
468 l_delete_flag := 'Y';
469 FOR j IN p_tiers_tbl.first..p_tiers_tbl.last LOOP
470 IF (p_tiers_tbl(j).rate_dim_tier_id IS NOT NULL AND
471 p_tiers_tbl(j).rate_dim_tier_id = db_tier.rate_dim_tier_id) THEN
472 l_delete_flag := 'N';
473 EXIT;
474 END IF;
475 END LOOP;
476
477 IF (l_delete_flag = 'Y') THEN
478 delete_tier(p_api_version => 1.0,
479 p_rate_dim_tier_id => db_tier.rate_dim_tier_id,
480 x_return_status => x_return_status,
481 x_msg_count => x_msg_count,
482 x_msg_data => x_msg_data);
483 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
484 RAISE fnd_api.g_exc_error;
485 END IF;
486 END IF;
487 END LOOP;
488
489 -- update the existing tiers
490 FOR i IN p_tiers_tbl.first..p_tiers_tbl.last LOOP
491 IF (p_tiers_tbl(i).rate_dim_tier_id IS NOT NULL) THEN
492 cn_rate_dim_tiers_pkg.lock_row
493 (x_rate_dim_tier_id => p_tiers_tbl(i).rate_dim_tier_id,
494 x_object_version_number => p_tiers_tbl(i).object_version_number);
495 cn_rate_dim_tiers_pkg.update_row
496 (x_rate_dim_tier_id => p_tiers_tbl(i).rate_dim_tier_id,
497 x_rate_dimension_id => p_rate_dimension_id,
498 x_minimum_amount => p_tiers_tbl(i).minimum_amount,
499 x_maximum_amount => p_tiers_tbl(i).maximum_amount,
500 x_min_exp_id => p_tiers_tbl(i).min_exp_id,
501 x_max_exp_id => p_tiers_tbl(i).max_exp_id,
502 x_string_value => p_tiers_tbl(i).string_value,
503 x_tier_sequence => p_tiers_tbl(i).tier_sequence,
504 x_object_version_number => p_tiers_tbl(i).object_version_number);
505 END IF;
506 END LOOP;
507
508 -- create the new tiers
509 FOR i IN p_tiers_tbl.first..p_tiers_tbl.last LOOP
510 IF (p_tiers_tbl(i).rate_dim_tier_id IS NULL) then
511 l_temp_id := NULL;
512 create_tier(p_api_version => 1.0,
513 p_rate_dimension_id => p_rate_dimension_id,
514 p_dim_unit_code => p_dim_unit_code,
515 p_minimum_amount => p_tiers_tbl(i).minimum_amount,
516 p_maximum_amount => p_tiers_tbl(i).maximum_amount,
517 p_min_exp_id => p_tiers_tbl(i).min_exp_id,
518 p_max_exp_id => p_tiers_tbl(i).max_exp_id,
519 p_string_value => p_tiers_tbl(i).string_value,
520 p_tier_sequence => p_tiers_tbl(i).tier_sequence,
521 --R12 MOAC Changes--Start
522 p_org_id => p_org_id,
523 --R12 MOAC Changes--End
524 x_rate_dim_tier_id => l_temp_id,
525 x_return_status => x_return_status,
526 x_msg_count => x_msg_count,
527 x_msg_data => x_msg_data);
528
529 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
530 RAISE fnd_api.g_exc_error;
531 END IF;
532 END IF;
533 END LOOP;
534 END IF;
535 */
536 -- *********************************************************************
537 -- ************ End - This code is not required in R12 *****************
538 -- *********************************************************************
539
540 -- Start - R12 Notes History Query for old Dimension Name
541 select name into l_dimension_name_old
542 from cn_rate_dimensions
543 where rate_dimension_id = p_rate_dimension_id;
544
545 select dim_unit_code into l_type_old
546 from cn_rate_dimensions
547 where rate_dimension_id = p_rate_dimension_id;
548 -- End - R12 Notes History Query for old Dimension Name
549
550 -- call table handler to update dimension record in cn_rate_dimensions
551 -- get the appropriate number of tiers
552 select count(*) into l_number_tier from cn_rate_dim_tiers
553 where rate_dimension_id = p_rate_dimension_id;
554
555 cn_rate_dimensions_pkg.lock_row
556 (x_rate_dimension_id => p_rate_dimension_id,
557 x_object_version_number => p_object_version_number);
558
559 cn_rate_dimensions_pkg.update_row
560 (x_rate_dimension_id => p_rate_dimension_id,
561 x_name => p_name,
562 x_description => p_description,
563 x_dim_unit_code => p_dim_unit_code,
564 x_number_tier => l_number_tier,
565 x_object_version_number => p_object_version_number);
566
567 -- *********************************************************************
568 -- ************ Start - R12 Notes History ************** ***************
569 -- *********************************************************************
570 l_consolidated_note := '';
571 IF (p_name <> l_dimension_name_old) THEN
572 fnd_message.set_name('CN', 'CNR12_NOTE_RT_DIM_UPDATE');
573 fnd_message.set_token('OLD_RT_DIM', l_dimension_name_old);
574 fnd_message.set_token('NEW_RT_DIM', p_name);
575 l_note_msg := fnd_message.get;
576 l_consolidated_note := l_note_msg || fnd_global.local_chr(10);
577 /*jtf_notes_pub.create_note
578 (p_api_version => 1.0,
579 x_return_status => x_return_status,
580 x_msg_count => x_msg_count,
581 x_msg_data => x_msg_data,
582 p_source_object_id => p_rate_dimension_id,
583 p_source_object_code => 'CN_RATE_DIMENSIONS',
584 p_notes => l_note_msg,
585 p_notes_detail => l_note_msg,
586 p_note_type => 'CN_SYSGEN', -- for system generated
587 x_jtf_note_id => l_note_id -- returned
588 );*/
589 END IF;
590
591 IF (p_dim_unit_code <> l_type_old) THEN
592 fnd_message.set_name('CN', 'CNR12_NOTE_RT_DIM_TYPE_UPDATE');
593 fnd_message.set_token('OLD_DIM_TYPE', cn_api.get_lkup_meaning(l_type_old, 'UNIT_OF_MEASURE'));
594 fnd_message.set_token('NEW_DIM_TYPE', cn_api.get_lkup_meaning(p_dim_unit_code, 'UNIT_OF_MEASURE'));
595 l_note_msg := fnd_message.get;
596 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
597 /* jtf_notes_pub.create_note
598 (p_api_version => 1.0,
599 x_return_status => x_return_status,
600 x_msg_count => x_msg_count,
601 x_msg_data => x_msg_data,
602 p_source_object_id => p_rate_dimension_id,
603 p_source_object_code => 'CN_RATE_DIMENSIONS',
604 p_notes => l_note_msg,
605 p_notes_detail => l_note_msg,
606 p_note_type => 'CN_SYSGEN', -- for system generated
607 x_jtf_note_id => l_note_id -- returned
608 ); */
609 END IF;
610
611 IF LENGTH(l_consolidated_note) > 1 THEN
612 jtf_notes_pub.create_note
613 (p_api_version => 1.0,
614 x_return_status => x_return_status,
615 x_msg_count => x_msg_count,
616 x_msg_data => x_msg_data,
617 p_source_object_id => p_rate_dimension_id,
618 p_source_object_code => 'CN_RATE_DIMENSIONS',
619 p_notes => l_consolidated_note,
620 p_notes_detail => l_consolidated_note,
621 p_note_type => 'CN_SYSGEN', -- for system generated
622 x_jtf_note_id => l_note_id -- returned
623 );
624 END IF;
625
626 -- *********************************************************************
627 -- ************ End - R12 Notes History ********************************
628 -- *********************************************************************
629
630 -- End of API body.
631
632 -- Standard check of p_commit.
633 IF FND_API.To_Boolean( p_commit ) THEN
634 COMMIT WORK;
635 END IF;
636 -- Standard call to get message count and if count is 1, get message info.
637 FND_MSG_PUB.count_and_get
638 (p_count => x_msg_count ,
639 p_data => x_msg_data ,
640 p_encoded => FND_API.G_FALSE );
641 EXCEPTION
642 WHEN FND_API.G_EXC_ERROR THEN
643 ROLLBACK TO Update_Dimension;
644 x_return_status := FND_API.G_RET_STS_ERROR ;
645 FND_MSG_PUB.count_and_get
646 (p_count => x_msg_count ,
647 p_data => x_msg_data ,
648 p_encoded => FND_API.G_FALSE );
649 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
650 ROLLBACK TO Update_Dimension;
651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
652 FND_MSG_PUB.count_and_get
653 (p_count => x_msg_count ,
654 p_data => x_msg_data ,
655 p_encoded => FND_API.G_FALSE );
656 WHEN OTHERS THEN
657 ROLLBACK TO Update_Dimension;
658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
659 IF FND_MSG_PUB.check_msg_level
660 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
661 THEN
662 FND_MSG_PUB.add_exc_msg
663 (G_PKG_NAME ,
664 l_api_name );
665 END IF;
666 FND_MSG_PUB.count_and_get
667 (p_count => x_msg_count ,
668 p_data => x_msg_data ,
669 p_encoded => FND_API.G_FALSE );
670 END Update_Dimension;
671
672 -- Notes : Delete rate dimensions and dimension tiers
673 -- 1) If it is used in a rate table, it can not be deleted
674 PROCEDURE Delete_Dimension
675 (p_api_version IN NUMBER ,
676 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
677 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
678 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
679 p_rate_dimension_id IN CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE,
680 --R12 MOAC Changes--Start
681 p_object_version_number IN CN_RATE_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE, --new
682 --R12 MOAC Changes--End
683 x_return_status OUT NOCOPY VARCHAR2 ,
684 x_msg_count OUT NOCOPY NUMBER ,
685 x_msg_data OUT NOCOPY VARCHAR2 )
686 IS
687 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Dimension';
688 l_api_version CONSTANT NUMBER := 1.0;
689 l_dummy pls_integer;
690
691 --R12 Notes Hoistory
692 l_dimension_name VARCHAR2(30);
693 l_org_id Number;
694 l_note_msg VARCHAR2(240);
695 l_note_id NUMBER;
696
697 CURSOR parent_table_exist IS
698 SELECT 1
699 FROM dual
700 WHERE exists (SELECT 1
701 FROM cn_rate_sch_dims
702 WHERE rate_dimension_id = p_rate_dimension_id);
703 BEGIN
704 -- Standard Start of API savepoint
705 SAVEPOINT Delete_Dimension;
706 -- Standard call to check for call compatibility.
707 IF NOT FND_API.Compatible_API_Call
708 (l_api_version ,
709 p_api_version ,
710 l_api_name ,
711 G_PKG_NAME )
712 THEN
713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
714 END IF;
715 -- Initialize message list if p_init_msg_list is set to TRUE.
716 IF FND_API.to_Boolean( p_init_msg_list ) THEN
717 FND_MSG_PUB.initialize;
718 END IF;
719 -- Initialize API return status to success
720 x_return_status := FND_API.G_RET_STS_SUCCESS;
721
722 -- API body
723
724 OPEN parent_table_exist;
725 FETCH parent_table_exist INTO l_dummy;
726 CLOSE parent_table_exist;
727
728 IF (l_dummy = 1) THEN
729 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
730 fnd_message.set_name('CN', 'CN_DIMENSION_IN_USE');
731 fnd_msg_pub.ADD;
732 END IF;
733 RAISE fnd_api.g_exc_error;
734 END IF;
735
736 /* Start - R12 Notes History */
737 SELECT org_id INTO l_org_id
738 FROM cn_rate_dimensions
739 WHERE rate_dimension_id = p_rate_dimension_id;
740
741 SELECT name INTO l_dimension_name
742 FROM cn_rate_dimensions
743 WHERE rate_dimension_id = p_rate_dimension_id;
744 /* End - R12 Notes History */
745
746
747 -- table handler does cascading delete of dimension tiers
748 cn_rate_dimensions_pkg.delete_row(p_rate_dimension_id);
749
750 -- *********************************************************************
751 -- ************ Start - R12 Notes History ******************************
752 -- *********************************************************************
753 IF (l_org_id <> -999) THEN
754 fnd_message.set_name('CN', 'CNR12_NOTE_RT_DIM_DELETE');
755 fnd_message.set_token('RT_DIM', l_dimension_name);
756 l_note_msg := fnd_message.get;
757
758 jtf_notes_pub.create_note
759 (p_api_version => 1.0,
760 x_return_status => x_return_status,
761 x_msg_count => x_msg_count,
762 x_msg_data => x_msg_data,
763 p_source_object_id => l_org_id,
764 p_source_object_code => 'CN_DELETED_OBJECTS',
765 p_notes => l_note_msg,
766 p_notes_detail => l_note_msg,
767 p_note_type => 'CN_SYSGEN', -- for system generated
768 x_jtf_note_id => l_note_id -- returned
769 );
770 END IF;
771
772 -- *********************************************************************
773 -- ************ End - R12 Notes History ********************************
774 -- *********************************************************************
775 -- End of API body.
776
777 -- Standard check of p_commit.
778 IF FND_API.To_Boolean( p_commit ) THEN
779 COMMIT WORK;
780 END IF;
781 -- Standard call to get message count and if count is 1, get message info.
782 FND_MSG_PUB.count_and_get
783 (p_count => x_msg_count ,
784 p_data => x_msg_data ,
785 p_encoded => FND_API.G_FALSE );
786 EXCEPTION
787 WHEN FND_API.G_EXC_ERROR THEN
788 ROLLBACK TO Delete_Dimension;
789 x_return_status := FND_API.G_RET_STS_ERROR ;
790 FND_MSG_PUB.count_and_get
791 (p_count => x_msg_count ,
792 p_data => x_msg_data ,
793 p_encoded => FND_API.G_FALSE );
794 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
795 ROLLBACK TO Delete_Dimension;
796 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
797 FND_MSG_PUB.count_and_get
798 (p_count => x_msg_count ,
799 p_data => x_msg_data ,
800 p_encoded => FND_API.G_FALSE );
801 WHEN OTHERS THEN
802 ROLLBACK TO Delete_Dimension;
803 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
804 IF FND_MSG_PUB.check_msg_level
805 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
806 THEN
807 FND_MSG_PUB.add_exc_msg
808 (G_PKG_NAME ,
809 l_api_name );
810 END IF;
811 FND_MSG_PUB.count_and_get
812 (p_count => x_msg_count ,
813 p_data => x_msg_data ,
814 p_encoded => FND_API.G_FALSE );
815 END Delete_Dimension;
816
817 -- Notes : Delete dimension tiers
818 -- 1) If the dimension is used in a rate table, at least one
819 -- tier should be left in the rate dimension
820 -- 2) If it is used in a rate table, delete the corresponding
821 -- records in cn_sch_dim_tiers,
822 -- cn_srp_rate_assigns, cn_rate_tiers, and cn_rate_dim_tiers
823 -- 3) update cn_rate_dimensions.number_tier
824 -- 4) tier_sequence is not adjusted here, users should take
825 -- care of the adjustment by calling update_tier
826 -- 5) the other validations should be done by users also
827 -- (like minimum_amount < maximum_amount, etc.)
828 PROCEDURE delete_tier
829 (p_api_version IN NUMBER ,
830 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
831 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
832 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
833 p_rate_dim_tier_id IN CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE,
834 x_return_status OUT NOCOPY VARCHAR2 ,
835 x_msg_count OUT NOCOPY NUMBER ,
836 x_msg_data OUT NOCOPY VARCHAR2 )
837 IS
838 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Tier';
839 l_api_version CONSTANT NUMBER := 1.0;
840
841 l_rate_dim_sequence CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE;
842 l_tier_sequence CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE;
843 l_dummy pls_integer;
844 l_rate_dimension_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE;
845
846 --R12 Notes Hoistory
847 l_from VARCHAR2(100);
848 l_to VARCHAR2(100);
849 l_org_old Number;
850 l_note_msg VARCHAR2(240);
851 l_note_id NUMBER;
852
853 CURSOR rate_tables IS
854 SELECT rate_schedule_id
855 FROM cn_rate_sch_dims
856 WHERE rate_dimension_id = l_rate_dimension_id;
857
858 CURSOR last_tier IS
859 SELECT 1
860 FROM dual
861 WHERE NOT exists (SELECT 1
862 FROM cn_rate_dim_tiers
863 WHERE rate_dimension_id = l_rate_dimension_id
864 AND rate_dim_tier_id <> p_rate_dim_tier_id);
865
866 --R12 History Cursor
867 CURSOR get_old_rec IS
868 Select minimum_amount, maximum_amount, min_exp_id, max_exp_id,
869 string_value, org_id
870 from cn_rate_dim_tiers
871 where rate_dim_tier_id = p_rate_dim_tier_id;
872
873 l_old_rec get_old_rec%ROWTYPE;
874
875 BEGIN
876 -- Standard Start of API savepoint
877 SAVEPOINT Delete_Tier;
878 -- Standard call to check for call compatibility.
879 IF NOT FND_API.Compatible_API_Call
880 (l_api_version ,
881 p_api_version ,
882 l_api_name ,
883 G_PKG_NAME )
884 THEN
885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
886 END IF;
887 -- Initialize message list if p_init_msg_list is set to TRUE.
888 IF FND_API.to_Boolean( p_init_msg_list ) THEN
889 FND_MSG_PUB.initialize;
890 END IF;
891 -- Initialize API return status to success
892 x_return_status := FND_API.G_RET_STS_SUCCESS;
893
894 -- API body
895
896 -- get dimension ID
897 begin
898 SELECT rate_dimension_id, tier_sequence
899 INTO l_rate_dimension_id, l_tier_sequence
900 FROM cn_rate_dim_tiers
901 WHERE rate_dim_tier_id = p_rate_dim_tier_id;
902 exception
903 when no_data_found then
904 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
905 fnd_msg_pub.add;
906 raise fnd_api.g_exc_unexpected_error;
907 end;
908
909 FOR rate_table IN rate_tables LOOP
910 OPEN last_tier;
911 FETCH last_tier INTO l_dummy;
912 CLOSE last_tier;
913
914 IF (l_dummy = 1) THEN
915 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
916 fnd_message.set_name('CN', 'CN_DIM_LAST_TIER');
917 fnd_msg_pub.ADD;
918 END IF;
919 RAISE fnd_api.g_exc_error;
920 END IF;
921
922 SELECT rate_dim_sequence
923 INTO l_rate_dim_sequence
924 FROM cn_rate_sch_dims
925 WHERE rate_schedule_id = rate_table.rate_schedule_id
926 AND rate_dimension_id = l_rate_dimension_id;
927
928 -- delete corresponding records in cn_rate_tiers
929 cn_multi_rate_schedules_pvt.delete_rate_tiers
930 (p_rate_schedule_id => rate_table.rate_schedule_id,
931 p_rate_dim_sequence => l_rate_dim_sequence,
932 p_tier_sequence => l_tier_sequence);
933 END LOOP;
934
935 /* Start - R12 Notes History */
936 Open get_old_rec;
937 Fetch get_old_rec into l_old_rec;
938 close get_old_rec;
939
940 /* End - R12 Notes History */
941
942 -- delete this tier in cn_rate_dim_tiers
943 cn_rate_dim_tiers_pkg.delete_row(p_rate_dim_tier_id);
944
945 -- *********************************************************************
946 -- ************ Start - R12 Notes History ******************************
947 -- *********************************************************************
948 IF (l_old_rec.org_id <> -999) THEN
949 if (l_old_rec.minimum_amount is NOT NULL AND l_old_rec.maximum_amount is NOT NULL)
950 then
951 l_from := l_old_rec.minimum_amount;
952 l_to := l_old_rec.maximum_amount;
953 fnd_message.set_name('CN', 'CNR12_NOTE_RT_DIM_TI_DELETE');
954 fnd_message.set_token('FROM', l_from);
955 fnd_message.set_token('TO', l_to);
956 end if;
957 if (l_old_rec.min_exp_id is NOT NULL AND l_old_rec.max_exp_id is NOT NULL)
958 then
959 select name into l_from from cn_calc_sql_exps
960 where calc_sql_exp_id = l_old_rec.min_exp_id;
961 select name into l_to from cn_calc_sql_exps
962 where calc_sql_exp_id = l_old_rec.max_exp_id;
963 fnd_message.set_name('CN', 'CNR12_NOTE_RT_DIM_TI_DELETE');
964 fnd_message.set_token('FROM', l_from);
965 fnd_message.set_token('TO', l_to);
966 end if;
967 if (l_old_rec.string_value is NOT NULL)
968 then
969 fnd_message.set_name('CN', 'CNR12_NOTE_RT_DIM_TI_ST_DELETE');
970 fnd_message.set_token('STR_VAL', l_old_rec.string_value);
971 end if;
972
973 l_note_msg := fnd_message.get;
974
975 jtf_notes_pub.create_note
976 (p_api_version => 1.0,
977 x_return_status => x_return_status,
978 x_msg_count => x_msg_count,
979 x_msg_data => x_msg_data,
980 p_source_object_id => l_rate_dimension_id,
981 p_source_object_code => 'CN_RATE_DIMENSIONS',
982 p_notes => l_note_msg,
983 p_notes_detail => l_note_msg,
984 p_note_type => 'CN_SYSGEN', -- for system generated
985 x_jtf_note_id => l_note_id -- returned
986 );
987 END IF;
988
989 -- *********************************************************************
990 -- ************ End - R12 Notes History ********************************
991 -- *********************************************************************
992
993
994 -- push tier sequence numbers down by one
995 update cn_rate_dim_tiers set tier_sequence = tier_sequence - 1
996 where rate_dimension_id = l_rate_dimension_id
997 and tier_sequence >= l_tier_sequence;
998
999 -- update rate dimension (number_tier is treated as a "virtual column" - just a
1000 -- count(*) of tiers assigned to the rate_dimension... it is not ovn controlled here
1001 UPDATE cn_rate_dimensions
1002 SET number_tier = (select count(*) from cn_rate_dim_tiers
1003 where rate_dimension_id = l_rate_dimension_id)
1004 WHERE rate_dimension_id = l_rate_dimension_id;
1005
1006 -- End of API body.
1007
1008 -- Standard check of p_commit.
1009 IF FND_API.To_Boolean( p_commit ) THEN
1010 COMMIT WORK;
1011 END IF;
1012 -- Standard call to get message count and if count is 1, get message info.
1013 FND_MSG_PUB.Count_And_Get
1014 (p_count => x_msg_count ,
1015 p_data => x_msg_data ,
1016 p_encoded => FND_API.G_FALSE );
1017 EXCEPTION
1018 WHEN FND_API.G_EXC_ERROR THEN
1019 ROLLBACK TO Delete_Tier;
1020 x_return_status := FND_API.G_RET_STS_ERROR ;
1021 FND_MSG_PUB.count_and_get
1022 (p_count => x_msg_count ,
1023 p_data => x_msg_data ,
1024 p_encoded => FND_API.G_FALSE );
1025 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1026 ROLLBACK TO Delete_Tier;
1027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1028 FND_MSG_PUB.count_and_get
1029 (p_count => x_msg_count ,
1030 p_data => x_msg_data ,
1031 p_encoded => FND_API.G_FALSE );
1032 WHEN OTHERS THEN
1033 ROLLBACK TO Delete_Tier;
1034 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1035 IF FND_MSG_PUB.check_msg_level
1036 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1037 THEN
1038 FND_MSG_PUB.add_exc_msg
1039 (G_PKG_NAME ,
1040 l_api_name );
1041 END IF;
1042 FND_MSG_PUB.count_and_get
1043 (p_count => x_msg_count ,
1044 p_data => x_msg_data ,
1045 p_encoded => FND_API.G_FALSE );
1046 END Delete_Tier;
1047
1048 -- Notes : Update dimension tiers
1049 PROCEDURE update_tier
1050 (p_api_version IN NUMBER ,
1051 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1052 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1053 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1054 p_rate_dim_tier_id IN CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE,
1055 p_rate_dimension_id IN CN_RATE_DIM_TIERS.RATE_DIMENSION_ID%TYPE,
1056 p_dim_unit_code IN CN_RATE_DIM_TIERS.DIM_UNIT_CODE%TYPE,
1057 p_minimum_amount IN CN_RATE_DIM_TIERS.MINIMUM_AMOUNT%TYPE := cn_api.g_miss_num,
1058 p_maximum_amount IN CN_RATE_DIM_TIERS.MAXIMUM_AMOUNT%TYPE := cn_api.g_miss_num,
1059 p_min_exp_id IN CN_RATE_DIM_TIERS.MIN_EXP_ID%TYPE := cn_api.g_miss_num,
1060 p_max_exp_id IN CN_RATE_DIM_TIERS.MAX_EXP_ID%TYPE := cn_api.g_miss_num,
1061 p_string_value IN CN_RATE_DIM_TIERS.STRING_VALUE%TYPE := cn_api.g_miss_char,
1062 p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE := cn_api.g_miss_num,
1063 -- R12 MOAC Changes --Start
1064 p_object_version_number IN OUT NOCOPY CN_RATE_DIM_TIERS.OBJECT_VERSION_NUMBER%TYPE, --changed
1065 -- R12 MOAC Changes --End
1066 x_return_status OUT NOCOPY VARCHAR2 ,
1067 x_msg_count OUT NOCOPY NUMBER ,
1068 x_msg_data OUT NOCOPY VARCHAR2 )
1069 IS
1070 l_api_name CONSTANT VARCHAR2(30) := 'Update_Tier';
1071 l_api_version CONSTANT NUMBER := 1.0;
1072
1073 --R12 Notes Hoistory
1074 l_from_old VARCHAR2(100);
1075 l_to_old VARCHAR2(100);
1076 l_from_new VARCHAR2(100);
1077 l_to_new VARCHAR2(100);
1078 l_note_msg VARCHAR2(240);
1079 l_note_id NUMBER;
1080
1081 --R12 History Cursor
1082 CURSOR get_old_rec IS
1083 Select minimum_amount, maximum_amount, min_exp_id, max_exp_id, string_value
1084 from cn_rate_dim_tiers
1085 where rate_dim_tier_id = p_rate_dim_tier_id;
1086
1087 l_old_rec get_old_rec%ROWTYPE;
1088
1089
1090 BEGIN
1091 -- Standard Start of API savepoint
1092 SAVEPOINT Update_tier;
1093 -- Standard call to check for call compatibility.
1094 IF NOT FND_API.Compatible_API_Call
1095 (l_api_version ,
1096 p_api_version ,
1097 l_api_name ,
1098 G_PKG_NAME )
1099 THEN
1100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1101 END IF;
1102 -- Initialize message list if p_init_msg_list is set to TRUE.
1103 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1104 FND_MSG_PUB.initialize;
1105 END IF;
1106 -- Initialize API return status to success
1107 x_return_status := FND_API.G_RET_STS_SUCCESS;
1108
1109 -- API body
1110
1111 IF (p_dim_unit_code IN ('AMOUNT', 'PERCENT')) THEN
1112 IF (p_minimum_amount = fnd_api.g_miss_num OR
1113 p_maximum_amount = fnd_api.g_miss_num OR
1114 p_minimum_amount IS NULL OR
1115 p_maximum_amount IS NULL)
1116 THEN
1117 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1118 fnd_message.set_name('CN', 'CN_TIER_NULL_MISS');
1119 fnd_msg_pub.ADD;
1120 END IF;
1121 RAISE fnd_api.g_exc_error;
1122 END IF;
1123
1124 IF (p_minimum_amount >= p_maximum_amount) THEN
1125 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1126 fnd_message.set_name('CN', 'CN_MIN_G_MAX');
1127 fnd_msg_pub.ADD;
1128 END IF;
1129 RAISE fnd_api.g_exc_error;
1130 END IF;
1131 END IF;
1132
1133 /* Start - R12 Notes History */
1134 Open get_old_rec;
1135 Fetch get_old_rec into l_old_rec;
1136 close get_old_rec;
1137 /* End - R12 Notes History */
1138
1139 -- update this tier in cn_rate_dim_tiers
1140 cn_rate_dim_tiers_pkg.lock_row
1141 (x_rate_dim_tier_id => p_rate_dim_tier_id,
1142 x_object_version_number => p_object_version_number);
1143
1144 cn_rate_dim_tiers_pkg.update_row
1145 (x_rate_dim_tier_id => p_rate_dim_tier_id,
1146 x_rate_dimension_id => p_rate_dimension_id,
1147 x_minimum_amount => p_minimum_amount,
1148 x_maximum_amount => p_maximum_amount,
1149 x_min_exp_id => p_min_exp_id,
1150 x_max_exp_id => p_max_exp_id,
1151 x_string_value => p_string_value,
1152 x_tier_sequence => p_tier_sequence,
1153 x_object_version_number => p_object_version_number);
1154
1155
1156 -- *********************************************************************
1157 -- ************ Start - R12 Notes History ******************************
1158 -- *********************************************************************
1159 IF ((l_old_rec.minimum_amount <> p_minimum_amount) OR
1160 (l_old_rec.maximum_amount <> p_maximum_amount) OR
1161 (l_old_rec.min_exp_id <> p_min_exp_id) OR
1162 (l_old_rec.max_exp_id <> p_max_exp_id) OR
1163 (l_old_rec.string_value <> p_string_value))
1164 THEN
1165 if (p_minimum_amount is NOT NULL AND p_maximum_amount is NOT NULL)
1166 then
1167 l_from_old := l_old_rec.minimum_amount;
1168 l_to_old := l_old_rec.maximum_amount;
1169 l_from_new := p_minimum_amount;
1170 l_to_new := p_maximum_amount;
1171 fnd_message.set_name('CN', 'CNR12_NOTE_RT_DIM_TI_UPDATE');
1172 fnd_message.set_token('OLD_FROM', l_from_old);
1173 fnd_message.set_token('OLD_TO', l_to_old);
1174 fnd_message.set_token('NEW_FROM', l_from_new);
1175 fnd_message.set_token('NEW_TO', l_to_new);
1176 end if;
1177 if (p_min_exp_id is NOT NULL AND p_max_exp_id is NOT NULL)
1178 then
1179 select name into l_from_old from cn_calc_sql_exps
1180 where calc_sql_exp_id = l_old_rec.min_exp_id;
1181 select name into l_to_old from cn_calc_sql_exps
1182 where calc_sql_exp_id = l_old_rec.max_exp_id;
1183 select name into l_from_new from cn_calc_sql_exps
1184 where calc_sql_exp_id = p_min_exp_id;
1185 select name into l_to_new from cn_calc_sql_exps
1186 where calc_sql_exp_id = p_max_exp_id;
1187 fnd_message.set_name('CN', 'CNR12_NOTE_RT_DIM_TI_UPDATE');
1188 fnd_message.set_token('OLD_FROM', l_from_old);
1189 fnd_message.set_token('OLD_TO', l_to_old);
1190 fnd_message.set_token('NEW_FROM', l_from_new);
1191 fnd_message.set_token('NEW_TO', l_to_new);
1192 end if;
1193 if (p_string_value is not null) then
1194 fnd_message.set_name('CN', 'CNR12_NOTE_RT_DIM_TI_ST_UPDATE');
1195 fnd_message.set_token('OLD_STR_VAL', l_old_rec.string_value);
1196 fnd_message.set_token('NEW_STR_VAL', p_string_value);
1197 end if;
1198
1199 l_note_msg := fnd_message.get;
1200
1201 jtf_notes_pub.create_note
1202 (p_api_version => 1.0,
1203 x_return_status => x_return_status,
1204 x_msg_count => x_msg_count,
1205 x_msg_data => x_msg_data,
1206 p_source_object_id => p_rate_dimension_id,
1207 p_source_object_code => 'CN_RATE_DIMENSIONS',
1208 p_notes => l_note_msg,
1209 p_notes_detail => l_note_msg,
1210 p_note_type => 'CN_SYSGEN', -- for system generated
1211 x_jtf_note_id => l_note_id -- returned
1212 );
1213 END IF;
1214
1215 -- *********************************************************************
1216 -- ************ End - R12 Notes History ********************************
1217 -- *********************************************************************
1218
1219 -- End of API body.
1220
1221 -- Standard check of p_commit.
1222 IF FND_API.To_Boolean( p_commit ) THEN
1223 COMMIT WORK;
1224 END IF;
1225 -- Standard call to get message count and if count is 1, get message info.
1226 FND_MSG_PUB.Count_And_Get
1227 (p_count => x_msg_count ,
1228 p_data => x_msg_data ,
1229 p_encoded => FND_API.G_FALSE );
1230 EXCEPTION
1231 WHEN FND_API.G_EXC_ERROR THEN
1232 ROLLBACK TO Update_tier;
1233 x_return_status := FND_API.G_RET_STS_ERROR ;
1234 FND_MSG_PUB.count_and_get
1235 (p_count => x_msg_count ,
1236 p_data => x_msg_data ,
1237 p_encoded => FND_API.G_FALSE );
1238 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1239 ROLLBACK TO Update_tier;
1240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1241 FND_MSG_PUB.count_and_get
1242 (p_count => x_msg_count ,
1243 p_data => x_msg_data ,
1244 p_encoded => FND_API.G_FALSE );
1245 WHEN OTHERS THEN
1246 ROLLBACK TO Update_tier;
1247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1248 IF FND_MSG_PUB.check_msg_level
1249 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1250 THEN
1251 FND_MSG_PUB.add_exc_msg
1252 (G_PKG_NAME ,
1253 l_api_name );
1254 END IF;
1255 FND_MSG_PUB.count_and_get
1256 (p_count => x_msg_count ,
1257 p_data => x_msg_data ,
1258 p_encoded => FND_API.G_FALSE );
1259 END Update_tier;
1260
1261 -- Notes : Create dimension tiers
1262 -- 1) If it is used in a rate table, update cn_sch_dim_tiers,
1263 -- cn_srp_rate_assigns, and cn_rate_tiers,
1264 -- and adjust cn_rate_tiers.rate_sequence
1265 -- 2) update cn_rate_dimensions.number_tier
1266 -- 3) tier_sequence is not adjusted here, users should do it by calling
1267 -- update_tier
1268 -- 4) minimum_amount < maximum_amount
1269 -- 5) validation of minimum_amount = previous maximum_amount should be
1270 -- done by users
1271 PROCEDURE create_tier
1272 (p_api_version IN NUMBER ,
1273 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1274 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1275 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1276 p_rate_dimension_id IN CN_RATE_DIM_TIERS.RATE_DIMENSION_ID%TYPE,
1277 p_dim_unit_code IN CN_RATE_DIM_TIERS.DIM_UNIT_CODE%TYPE,
1278 p_minimum_amount IN CN_RATE_DIM_TIERS.MINIMUM_AMOUNT%TYPE := null,
1279 p_maximum_amount IN CN_RATE_DIM_TIERS.MAXIMUM_AMOUNT%TYPE := null,
1280 p_min_exp_id IN CN_RATE_DIM_TIERS.MIN_EXP_ID%TYPE := null,
1281 p_max_exp_id IN CN_RATE_DIM_TIERS.MAX_EXP_ID%TYPE := null,
1282 p_string_value IN CN_RATE_DIM_TIERS.STRING_VALUE%TYPE := null,
1283 p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE := null,
1284 -- R12 MOAC Changes --Start
1285 p_org_id IN CN_RATE_DIM_TIERS.ORG_ID%TYPE, --new
1286 x_rate_dim_tier_id IN OUT NOCOPY CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE, --changed
1287 -- R12 MOAC Changes --End
1288 x_return_status OUT NOCOPY VARCHAR2 ,
1289 x_msg_count OUT NOCOPY NUMBER ,
1290 x_msg_data OUT NOCOPY VARCHAR2 )
1291 IS
1292 l_api_name CONSTANT VARCHAR2(30) := 'Create_Tier';
1293 l_api_version CONSTANT NUMBER := 1.0;
1294
1295 l_rate_dim_sequence CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE;
1296 i pls_integer := 1;
1297
1298 --R12 Notes Hoistory
1299 l_from VARCHAR2(100);
1300 l_to VARCHAR2(100);
1301 l_note_msg VARCHAR2(240);
1302 l_note_id NUMBER;
1303
1304 CURSOR rate_tables IS
1305 SELECT rate_schedule_id
1306 FROM cn_rate_sch_dims
1307 WHERE rate_dimension_id = p_rate_dimension_id;
1308 BEGIN
1309 -- Standard Start of API savepoint
1310 SAVEPOINT Create_tier;
1311 -- Standard call to check for call compatibility.
1312 IF NOT FND_API.Compatible_API_Call
1313 (l_api_version ,
1314 p_api_version ,
1315 l_api_name ,
1316 G_PKG_NAME )
1317 THEN
1318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1319 END IF;
1320 -- Initialize message list if p_init_msg_list is set to TRUE.
1321 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1322 FND_MSG_PUB.initialize;
1323 END IF;
1324 -- Initialize API return status to success
1325 x_return_status := FND_API.G_RET_STS_SUCCESS;
1326
1327 -- API body
1328
1329 IF (p_dim_unit_code IN ('AMOUNT', 'PERCENT')) THEN
1330 IF (p_minimum_amount = fnd_api.g_miss_num OR
1331 p_maximum_amount = fnd_api.g_miss_num OR
1332 p_minimum_amount IS NULL OR
1333 p_maximum_amount IS NULL)
1334 THEN
1335 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1336 fnd_message.set_name('CN', 'CN_TIER_NULL_MISS');
1337 fnd_msg_pub.ADD;
1338 END IF;
1339 RAISE fnd_api.g_exc_error;
1340 END IF;
1341
1342 IF (p_minimum_amount >= p_maximum_amount) THEN
1343 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1344 fnd_message.set_name('CN', 'CN_MIN_G_MAX');
1345 fnd_msg_pub.ADD;
1346 END IF;
1347 RAISE fnd_api.g_exc_error;
1348 END IF;
1349 END IF;
1350
1351 FOR rate_table IN rate_tables LOOP
1352 -- NOTE: a rate table can not have two dimensions with the same rate_dimension_id
1353 SELECT rate_dim_sequence
1354 INTO l_rate_dim_sequence
1355 FROM cn_rate_sch_dims
1356 WHERE rate_dimension_id = p_rate_dimension_id
1357 AND rate_schedule_id = rate_table.rate_schedule_id;
1358
1359 cn_multi_rate_schedules_pvt.create_rate_tiers
1360 (p_rate_schedule_id => rate_table.rate_schedule_id,
1361 p_rate_dim_sequence => l_rate_dim_sequence,
1362 p_tier_sequence => p_tier_sequence,
1363 -- R12 MOAC Changes --Start
1364 p_org_id => p_org_id);
1365 -- R12 MOAC Changes --End
1366 END LOOP;
1367
1368 -- push tiers with higher sequence numbers than p_tier_sequence up by one
1369 update cn_rate_dim_tiers set tier_sequence = tier_sequence + 1
1370 where rate_dimension_id = p_rate_dimension_id
1371 and tier_sequence >= p_tier_sequence;
1372
1373 -- create this tier in cn_rate_dim_tiers
1374 cn_rate_dim_tiers_pkg.insert_row
1375 (x_rate_dim_tier_id => x_rate_dim_tier_id,
1376 x_rate_dimension_id => p_rate_dimension_id,
1377 x_minimum_amount => p_minimum_amount,
1378 x_maximum_amount => p_maximum_amount,
1379 x_min_exp_id => p_min_exp_id,
1380 x_max_exp_id => p_max_exp_id,
1381 x_string_value => p_string_value,
1382 x_tier_sequence => p_tier_sequence,
1383 -- R12 MOAC Changes --Start
1384 x_org_id => p_org_id
1385 -- R12 MOAC Changes --End
1386 );
1387
1388 -- *********************************************************************
1389 -- ************ Start - R12 Notes History ************** ***************
1390 -- *********************************************************************
1391 if (p_minimum_amount is NOT NULL AND p_maximum_amount is NOT NULL)
1392 then
1393 l_from := p_minimum_amount;
1394 l_to := p_maximum_amount;
1395 fnd_message.set_name('CN', 'CNR12_NOTE_RT_DIM_TI_CREATE');
1396 fnd_message.set_token('FROM', l_from);
1397 fnd_message.set_token('TO', l_to);
1398 end if;
1399 if (p_min_exp_id is NOT NULL AND p_max_exp_id is NOT NULL)
1400 then
1401 select name into l_from from cn_calc_sql_exps
1402 where calc_sql_exp_id = p_min_exp_id;
1403 select name into l_to from cn_calc_sql_exps
1404 where calc_sql_exp_id = p_max_exp_id;
1405 fnd_message.set_name('CN', 'CNR12_NOTE_RT_DIM_TI_CREATE');
1406 fnd_message.set_token('FROM', l_from);
1407 fnd_message.set_token('TO', l_to);
1408 end if;
1409 if (p_string_value is NOT NULL) then
1410 fnd_message.set_name('CN', 'CNR12_NOTE_RT_DIM_TI_ST_CREATE');
1411 fnd_message.set_token('STR_VAL', p_string_value);
1412 end if;
1413
1414 l_note_msg := fnd_message.get;
1415
1416 jtf_notes_pub.create_note
1417 (p_api_version => 1.0,
1418 x_return_status => x_return_status,
1419 x_msg_count => x_msg_count,
1420 x_msg_data => x_msg_data,
1421 p_source_object_id => p_rate_dimension_id,
1422 p_source_object_code => 'CN_RATE_DIMENSIONS',
1423 p_notes => l_note_msg,
1424 p_notes_detail => l_note_msg,
1425 p_note_type => 'CN_SYSGEN', -- for system generated
1426 x_jtf_note_id => l_note_id -- returned
1427 );
1428
1429 -- *********************************************************************
1430 -- ************ End - R12 Notes History ********************************
1431 -- *********************************************************************
1432
1433 -- update rate dimension (number_tier is treated as a "virtual column" - just a
1434 -- count(*) of tiers assigned to the rate_dimension... it is not ovn controlled here
1435 UPDATE cn_rate_dimensions
1436 SET number_tier = (select count(*) from cn_rate_dim_tiers
1437 where rate_dimension_id = p_rate_dimension_id)
1438 WHERE rate_dimension_id = p_rate_dimension_id;
1439
1440 -- End of API body.
1441
1442 -- Standard check of p_commit.
1443 IF FND_API.To_Boolean( p_commit ) THEN
1444 COMMIT WORK;
1445 END IF;
1446 -- Standard call to get message count and if count is 1, get message info.
1447 FND_MSG_PUB.Count_And_Get
1448 (p_count => x_msg_count ,
1449 p_data => x_msg_data ,
1450 p_encoded => FND_API.G_FALSE );
1451 EXCEPTION
1452 WHEN FND_API.G_EXC_ERROR THEN
1453 ROLLBACK TO Create_tier;
1454 x_return_status := FND_API.G_RET_STS_ERROR ;
1455 FND_MSG_PUB.count_and_get
1456 (p_count => x_msg_count ,
1457 p_data => x_msg_data ,
1458 p_encoded => FND_API.G_FALSE );
1459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1460 ROLLBACK TO Create_tier;
1461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1462 FND_MSG_PUB.count_and_get
1463 (p_count => x_msg_count ,
1464 p_data => x_msg_data ,
1465 p_encoded => FND_API.G_FALSE );
1466 WHEN OTHERS THEN
1467 ROLLBACK TO Create_tier;
1468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1469 IF FND_MSG_PUB.check_msg_level
1470 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1471 THEN
1472 FND_MSG_PUB.add_exc_msg
1473 (G_PKG_NAME ,
1474 l_api_name );
1475 END IF;
1476 FND_MSG_PUB.count_and_get
1477 (p_count => x_msg_count ,
1478 p_data => x_msg_data ,
1479 p_encoded => FND_API.G_FALSE );
1480 END Create_tier;
1481
1482 END CN_RATE_DIMENSIONS_PVT;