[Home] [Help]
PACKAGE BODY: APPS.CN_MULTI_RATE_SCHEDULES_PUB
Source
1 PACKAGE BODY CN_MULTI_RATE_SCHEDULES_PUB AS
2 /*$Header: cnprschb.pls 120.2 2005/11/07 16:56:14 jxsingh noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_MULTI_RATE_SCHEDULES_PUB';
5
6 -- local util functions
7 -- to get a rate schedule ID from its name
8 FUNCTION get_rate_schedule_id(p_name in CN_RATE_SCHEDULES.NAME%TYPE)
9 RETURN CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE IS
10
11 l_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE;
12
13 BEGIN
14 select RATE_SCHEDULE_ID into l_rate_schedule_id
15 from CN_RATE_SCHEDULES
16 where name = p_name;
17
18 RETURN l_rate_schedule_id;
19 EXCEPTION
20 when others then
21 fnd_message.set_name('CN', 'CN_RATE_SCHEDULE_NOT_EXIST');
22 fnd_msg_pub.ADD;
23 RAISE fnd_api.g_exc_error;
24 END get_rate_schedule_id;
25
26 -- to get a rate dimension ID from its name
27 FUNCTION get_rate_dimension_id(p_name in CN_RATE_DIMENSIONS.NAME%TYPE)
28 RETURN CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE IS
29
30 l_rate_dimension_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE;
31
32 BEGIN
33 select RATE_DIMENSION_ID into l_rate_dimension_id
34 from CN_RATE_DIMENSIONS
35 where name = p_name;
36
37 RETURN l_rate_dimension_id;
38 EXCEPTION
39 when others then
40 fnd_message.set_name('CN', 'CN_RATE_DIMENSION_NOT_EXIST');
41 fnd_msg_pub.ADD;
42 RAISE fnd_api.g_exc_error;
43 END get_rate_dimension_id;
44
45 -- to get a rate_sch_dim_id from a given rate dimension ID and rate schedule ID
46 FUNCTION get_rate_sch_dim_id(p_rate_schedule_id in CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE,
47 p_rate_dimension_id in CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE)
48 RETURN CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE IS
49
50 l_rate_sch_dim_id CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE;
51 BEGIN
52 select rate_sch_dim_id into l_rate_sch_dim_id
53 from cn_rate_sch_dims
54 where rate_schedule_id = p_rate_schedule_id
55 and rate_dimension_id = p_rate_dimension_id;
56
57 RETURN l_rate_sch_dim_id;
58 EXCEPTION
59 when others THEN
60 fnd_message.set_name('CN', 'CN_RATE_DIM_ASSIGN_NOT_EXIST');
61 fnd_msg_pub.ADD;
62 RAISE fnd_api.g_exc_error;
63 END get_rate_sch_dim_id;
64
65 -- to get an expression ID from its name
66 FUNCTION get_exp_id(p_name in CN_CALC_SQL_EXPS.NAME%TYPE)
67 RETURN CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE IS
68
69 l_calc_sql_exp_id CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE;
70 BEGIN
71 select CALC_SQL_EXP_ID into l_calc_sql_exp_id
72 from CN_CALC_SQL_EXPS
73 where name = p_name;
74
75 RETURN l_calc_sql_exp_id;
76 EXCEPTION
77 when others THEN
78 fnd_message.set_name('CN', 'CN_EXP_NOT_EXIST');
79 fnd_msg_pub.ADD;
80 RAISE fnd_api.g_exc_error;
81 END get_exp_id;
82
83 -- to get a rate_dim_tier_id from the rate_dimension_id and its sequence
84 FUNCTION get_rate_dim_tier_id(p_rate_dimension_id in CN_RATE_DIM_TIERS.RATE_DIMENSION_ID%TYPE,
85 p_tier_sequence in CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE)
86 RETURN CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE IS
87
88 l_rate_dim_tier_id CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE;
89 BEGIN
90 select rate_dim_tier_id into l_rate_dim_tier_id
91 from cn_rate_dim_tiers
92 where rate_dimension_id = p_rate_dimension_id
93 and tier_sequence = p_tier_sequence;
94
95 RETURN l_rate_dim_tier_id;
96 EXCEPTION
97 when others THEN
98 fnd_message.set_name('CN', 'CN_RATE_DIM_TIER_NOT_EXIST');
99 fnd_msg_pub.ADD;
100 RAISE fnd_api.g_exc_error;
101 END get_rate_dim_tier_id;
102
103 -- to get a rate_dim_tier_id from the rate_schedule and sequence numbers
104 FUNCTION get_rate_dim_tier_id(p_rate_schedule_id in CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE,
105 p_rate_dim_sequence in CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE,
106 p_tier_sequence in CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE)
107 RETURN CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE IS
108
109 l_rate_dim_tier_id CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE;
110 BEGIN
111 select rdt.rate_dim_tier_id into l_rate_dim_tier_id
112 from cn_rate_sch_dims rsd, cn_rate_dim_tiers rdt
113 where rsd.rate_schedule_id = p_rate_schedule_id
114 and rsd.rate_dim_sequence = p_rate_dim_sequence
115 and rdt.rate_dimension_id = rsd.rate_dimension_id
116 and rdt.tier_sequence = p_tier_sequence;
117
118 RETURN l_rate_dim_tier_id;
119 EXCEPTION
120 when others THEN
121 fnd_message.set_name('CN', 'CN_RATE_DIM_TIER_NOT_EXIST');
122 fnd_msg_pub.ADD;
123 RAISE fnd_api.g_exc_error;
124 END get_rate_dim_tier_id;
125
126 PROCEDURE translate_values(p_dim_unit_code IN CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE,
127 p_value1 IN VARCHAR2,
128 p_value2 IN VARCHAR2,
129 p_minimum_amount OUT NOCOPY CN_RATE_DIM_TIERS.MINIMUM_AMOUNT%TYPE,
130 p_maximum_amount OUT NOCOPY CN_RATE_DIM_TIERS.MAXIMUM_AMOUNT%TYPE,
131 p_min_exp_id OUT NOCOPY CN_RATE_DIM_TIERS.MIN_EXP_ID%TYPE,
132 p_max_exp_id OUT NOCOPY CN_RATE_DIM_TIERS.MAX_EXP_ID%TYPE,
133 p_string_value OUT NOCOPY CN_RATE_DIM_TIERS.STRING_VALUE%TYPE) IS
134 BEGIN
135 if p_dim_unit_code = 'AMOUNT' OR p_dim_unit_code = 'PERCENT' then
136 p_minimum_amount := p_value1;
137 p_maximum_amount := p_value2;
138 elsif p_dim_unit_code = 'EXPRESSION' then
139 p_min_exp_id := get_exp_id(p_value1);
140 p_max_exp_id := get_exp_id(p_value2);
141 elsif p_dim_unit_code = 'STRING' then
142 p_string_value := p_value1; -- value2 not used for strings
143 else
144 fnd_message.set_name('CN', 'CN_INVALID_DIM_UOM');
145 fnd_msg_pub.ADD;
146 RAISE fnd_api.g_exc_error;
147 end if;
148
149 EXCEPTION
150 when others THEN
151 fnd_message.set_name('CN', 'CN_INVALID_TIER');
152 fnd_msg_pub.ADD;
153 RAISE fnd_api.g_exc_error;
154 END translate_values;
155
156 -- to raise an error according to the return status passed in
157 PROCEDURE check_ret_sts(l_return_status IN VARCHAR2) IS
158 BEGIN
159 if l_return_status = FND_API.G_RET_STS_ERROR then
160 RAISE FND_API.G_EXC_ERROR;
161 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
163 end if;
164 END check_ret_sts;
165
166
167 ----------------------------- actual public API's ----------------------------
168 PROCEDURE Create_Schedule
169 (p_api_version IN NUMBER,
170 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
171 p_commit IN VARCHAR2 := FND_API.G_FALSE,
172 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
173 p_name IN CN_RATE_SCHEDULES.NAME%TYPE,
174 p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE,
175 p_dims_tbl IN dim_assign_tbl_type := g_miss_dim_assign_tbl,
176 -- Start - MOAC Change
177 p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE := NULL,
178 -- End - MOAC Change
179 x_return_status OUT NOCOPY VARCHAR2,
180 x_msg_count OUT NOCOPY NUMBER,
181 x_msg_data OUT NOCOPY VARCHAR2) IS
182
183 l_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE;
184 l_api_name CONSTANT VARCHAR2(30) := 'Create_Schedule';
185 l_api_version CONSTANT NUMBER := 1.0;
186 l_dims_tbl CN_MULTI_RATE_SCHEDULES_PVT.dims_tbl_type;
187 -- Start - MOAC Change
188 l_org_id NUMBER;
189 l_status VARCHAR2(1);
190 -- End - MOAC Change
191 BEGIN
192 -- Standard Start of API savepoint
193 SAVEPOINT Create_Schedule;
194 -- Standard call to check for call compatibility.
195 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
196 p_api_version ,
197 l_api_name ,
198 G_PKG_NAME )
199 THEN
200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
201 END IF;
202
203 -- Initialize message list if p_init_msg_list is set to TRUE.
204 IF FND_API.to_Boolean( p_init_msg_list ) THEN
205 FND_MSG_PUB.initialize;
206 END IF;
207
208 -- Initialize API return status to success
209 x_return_status := FND_API.G_RET_STS_SUCCESS;
210
211 -- API body
212
213 -- Start - R12 MOAC Changes
214 l_org_id := p_org_id;
215 mo_global.validate_orgid_pub_api(org_id => l_org_id,
216 status => l_status);
217 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
218 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
219 'cn.plsql.cn_multi_rate_schedules_pub.create_schedule.org_validate',
220 'Validated org_id = ' || l_org_id || ' status = '||l_status);
221 end if;
222 -- End - R12 MOAC Changes
223
224 -- get ID's in the dims_tbl to pass on to private API
225 if p_dims_tbl.count > 0 then
226 for i in 1..p_dims_tbl.count loop
227 l_dims_tbl(i).rate_dimension_id :=
228 get_rate_dimension_id(p_dims_tbl(i).rate_dim_name);
229 l_dims_tbl(i).rate_dim_sequence := p_dims_tbl(i).rate_dim_sequence;
230 end loop;
231 end if;
232
233 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
234 'CREATE_SCHEDULE', 'B', 'C') then
235 CN_MULTI_RATE_SCHEDULES_CUHK.CREATE_SCHEDULE_PRE
236 (p_name => p_name,
237 p_commission_unit_code => p_commission_unit_code,
238 p_dims_tbl => p_dims_tbl,
239 x_return_status => x_return_status,
240 x_msg_count => x_msg_count,
241 x_msg_data => x_msg_data);
242
243 check_ret_sts(x_return_status);
244 end if;
245
246 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
247 'CREATE_SCHEDULE', 'B', 'V') then
248 CN_MULTI_RATE_SCHEDULES_VUHK.CREATE_SCHEDULE_PRE
249 (p_name => p_name,
250 p_commission_unit_code => p_commission_unit_code,
251 p_dims_tbl => p_dims_tbl,
252 x_return_status => x_return_status,
253 x_msg_count => x_msg_count,
254 x_msg_data => x_msg_data);
255
256 check_ret_sts(x_return_status);
257 end if;
258
259 CN_MULTI_RATE_SCHEDULES_PVT.Create_Schedule
260 (p_api_version => p_api_version,
261 p_init_msg_list => p_init_msg_list,
262 p_commit => p_commit,
263 p_validation_level => p_validation_level,
264 p_name => p_name,
265 p_commission_unit_code => p_commission_unit_code,
266 p_number_dim => null, -- not used
267 p_dims_tbl => l_dims_tbl,
268 -- Start - R12 MOAC Changes
269 p_org_id => p_org_id,
270 -- End - R12 MOAC Changes
271 x_rate_schedule_id => l_rate_schedule_id,
272 x_return_status => x_return_status,
273 x_msg_count => x_msg_count,
274 x_msg_data => x_msg_data);
275
276 check_ret_sts(x_return_status);
277
278 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
279 'CREATE_SCHEDULE', 'A', 'V') then
280 CN_MULTI_RATE_SCHEDULES_VUHK.CREATE_SCHEDULE_POST
281 (p_name => p_name,
282 p_commission_unit_code => p_commission_unit_code,
283 p_dims_tbl => p_dims_tbl,
284 x_return_status => x_return_status,
285 x_msg_count => x_msg_count,
286 x_msg_data => x_msg_data);
287
288 check_ret_sts(x_return_status);
289 end if;
290
291 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
292 'CREATE_SCHEDULE', 'A', 'C') then
293 CN_MULTI_RATE_SCHEDULES_CUHK.CREATE_SCHEDULE_POST
294 (p_name => p_name,
295 p_commission_unit_code => p_commission_unit_code,
296 p_dims_tbl => p_dims_tbl,
297 x_return_status => x_return_status,
298 x_msg_count => x_msg_count,
299 x_msg_data => x_msg_data);
300
301 check_ret_sts(x_return_status);
302 end if;
303
304 -- End of API body.
305
306 -- Standard check of p_commit.
307 IF FND_API.To_Boolean( p_commit ) THEN
308 COMMIT WORK;
309 END IF;
310 -- Standard call to get message count and if count is 1, get message info.
311 FND_MSG_PUB.Count_And_Get
312 (p_count => x_msg_count ,
313 p_data => x_msg_data ,
314 p_encoded => FND_API.G_FALSE );
315 EXCEPTION
316 WHEN FND_API.G_EXC_ERROR THEN
317 ROLLBACK TO Create_Schedule;
318 x_return_status := FND_API.G_RET_STS_ERROR ;
319 FND_MSG_PUB.count_and_get
320 (p_count => x_msg_count ,
321 p_data => x_msg_data ,
322 p_encoded => FND_API.G_FALSE );
323 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
324 ROLLBACK TO Create_Schedule;
325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
326 FND_MSG_PUB.count_and_get
327 (p_count => x_msg_count ,
328 p_data => x_msg_data ,
329 p_encoded => FND_API.G_FALSE );
330 WHEN OTHERS THEN
331 ROLLBACK TO Create_Schedule;
332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
333 IF FND_MSG_PUB.check_msg_level
334 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
335 THEN
339 END IF;
336 FND_MSG_PUB.add_exc_msg
337 (G_PKG_NAME ,
338 l_api_name );
340 FND_MSG_PUB.count_and_get
341 (p_count => x_msg_count ,
342 p_data => x_msg_data ,
343 p_encoded => FND_API.G_FALSE );
344 END Create_Schedule;
345
346 PROCEDURE Update_Schedule
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_original_name IN CN_RATE_SCHEDULES.NAME%TYPE,
352 p_new_name IN CN_RATE_SCHEDULES.NAME%TYPE :=
353 cn_api.g_miss_char,
354 p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE :=
355 cn_api.g_miss_char,
356 p_object_version_number IN OUT NOCOPY CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE,
357 p_dims_tbl IN dim_assign_tbl_type := g_miss_dim_assign_tbl,
358 -- Start - MOAC Change
359 p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE,
360 -- End - MOAC Change
361 x_return_status OUT NOCOPY VARCHAR2,
362 x_msg_count OUT NOCOPY NUMBER,
363 x_msg_data OUT NOCOPY VARCHAR2) IS
364
365 l_api_name CONSTANT VARCHAR2(30) := 'Update_Schedule';
366 l_api_version CONSTANT NUMBER := 1.0;
367 l_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE;
368 l_name CN_RATE_SCHEDULES.NAME%TYPE;
369 l_original_comm_unit_code CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE;
370 l_comm_unit_code CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE;
371 l_dims_tbl CN_MULTI_RATE_SCHEDULES_PVT.dims_tbl_type;
372 -- Start - MOAC Change
373 l_org_id NUMBER;
374 -- End - MOAC Change
375 BEGIN
376 -- Standard Start of API savepoint
377 SAVEPOINT Update_Schedule;
378 -- Standard call to check for call compatibility.
379 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
380 p_api_version ,
381 l_api_name ,
382 G_PKG_NAME )
383 THEN
384 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
385 END IF;
386
387 -- Initialize message list if p_init_msg_list is set to TRUE.
388 IF FND_API.to_Boolean( p_init_msg_list ) THEN
389 FND_MSG_PUB.initialize;
390 END IF;
391
392 -- Initialize API return status to success
393 x_return_status := FND_API.G_RET_STS_SUCCESS;
394
395 -- API body
396 l_rate_schedule_id := get_rate_schedule_id(p_original_name);
397
398 -- Start - MOAC Change
399 SELECT org_id INTO l_org_id
400 FROM cn_rate_schedules
401 WHERE rate_schedule_id = l_rate_schedule_id;
402
403 IF (l_org_id <> p_org_id)
404 THEN
405 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
406 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
407 THEN
408 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
409 'cn.plsql.cn_multi_rate_schedule_pub.update_schedule.error',
410 true);
411 END IF;
412
413 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
414 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
415 FND_MSG_PUB.Add;
416 END IF;
417
418 RAISE FND_API.G_EXC_ERROR ;
419 END IF;
420 -- End - MOAC Change
421
422 -- handle G_MISSES (validation for this select already performed)
423 select commission_unit_code into l_original_comm_unit_code
424 from cn_rate_schedules
425 where rate_schedule_id = l_rate_schedule_id;
426
427 select decode(p_new_name, cn_api.g_miss_char, p_original_name, p_new_name),
428 decode(p_commission_unit_code, cn_api.g_miss_char,
429 l_original_comm_unit_code, p_commission_unit_code)
430 into l_name, l_comm_unit_code from dual;
431
432 -- get ID's in the dims_tbl to pass on to private API
433 if p_dims_tbl.count > 0 then
434 for i in 1..p_dims_tbl.count loop
435 l_dims_tbl(i).rate_dimension_id :=
436 get_rate_dimension_id(p_dims_tbl(i).rate_dim_name);
437 l_dims_tbl(i).rate_dim_sequence := p_dims_tbl(i).rate_dim_sequence;
438 end loop;
439 end if;
440
441 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
442 'UPDATE_SCHEDULE', 'B', 'C') then
443 CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_SCHEDULE_PRE
444 (p_original_name => p_original_name,
445 p_new_name => p_new_name,
446 p_commission_unit_code => p_commission_unit_code,
447 p_object_version_number => p_object_version_number,
448 x_return_status => x_return_status,
449 x_msg_count => x_msg_count,
450 x_msg_data => x_msg_data);
451
452 check_ret_sts(x_return_status);
453 end if;
454
455 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
456 'UPDATE_SCHEDULE', 'B', 'V') then
460 p_commission_unit_code => p_commission_unit_code,
457 CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_SCHEDULE_PRE
458 (p_original_name => p_original_name,
459 p_new_name => p_new_name,
461 p_object_version_number => p_object_version_number,
462 x_return_status => x_return_status,
463 x_msg_count => x_msg_count,
464 x_msg_data => x_msg_data);
465
466 check_ret_sts(x_return_status);
467 end if;
468
469 CN_MULTI_RATE_SCHEDULES_PVT.Update_Schedule
470 (p_api_version => p_api_version,
471 p_init_msg_list => p_init_msg_list,
472 p_commit => p_commit,
473 p_validation_level => p_validation_level,
474 p_rate_schedule_id => l_rate_schedule_id,
475 p_name => l_name,
476 p_commission_unit_code => l_comm_unit_code,
477 p_number_dim => null, -- not used
478 -- Start - R12 MOAC Changes
479 p_org_id => p_org_id,
480 -- End - R12 MOAC Changes
481 p_object_version_number => p_object_version_number,
482 p_dims_tbl => l_dims_tbl,
483 x_return_status => x_return_status,
484 x_msg_count => x_msg_count,
485 x_msg_data => x_msg_data);
486
487 check_ret_sts(x_return_status);
488
489 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
490 'UPDATE_SCHEDULE', 'A', 'V') then
491 CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_SCHEDULE_POST
492 (p_original_name => p_original_name,
493 p_new_name => p_new_name,
494 p_commission_unit_code => p_commission_unit_code,
495 p_object_version_number => p_object_version_number,
496 x_return_status => x_return_status,
497 x_msg_count => x_msg_count,
498 x_msg_data => x_msg_data);
499
500 check_ret_sts(x_return_status);
501 end if;
502
503 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
504 'UPDATE_SCHEDULE', 'A', 'C') then
505 CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_SCHEDULE_POST
506 (p_original_name => p_original_name,
507 p_new_name => p_new_name,
508 p_commission_unit_code => p_commission_unit_code,
509 p_object_version_number => p_object_version_number,
510 x_return_status => x_return_status,
511 x_msg_count => x_msg_count,
512 x_msg_data => x_msg_data);
513
514 check_ret_sts(x_return_status);
515 end if;
516
517 -- End of API body.
518
519 -- Standard check of p_commit.
520 IF FND_API.To_Boolean( p_commit ) THEN
521 COMMIT WORK;
522 END IF;
523 -- Standard call to get message count and if count is 1, get message info.
524 FND_MSG_PUB.count_and_get
525 (p_count => x_msg_count ,
526 p_data => x_msg_data ,
527 p_encoded => FND_API.G_FALSE );
528 EXCEPTION
529 WHEN FND_API.G_EXC_ERROR THEN
530 ROLLBACK TO Update_Schedule;
531 x_return_status := FND_API.G_RET_STS_ERROR ;
532 FND_MSG_PUB.count_and_get
533 (p_count => x_msg_count ,
534 p_data => x_msg_data ,
535 p_encoded => FND_API.G_FALSE );
536 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
537 ROLLBACK TO Update_Schedule;
538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
539 FND_MSG_PUB.count_and_get
540 (p_count => x_msg_count ,
541 p_data => x_msg_data ,
542 p_encoded => FND_API.G_FALSE );
543 WHEN OTHERS THEN
544 ROLLBACK TO Update_Schedule;
545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
546 IF FND_MSG_PUB.check_msg_level
547 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
548 THEN
549 FND_MSG_PUB.add_exc_msg
550 (G_PKG_NAME ,
551 l_api_name );
552 END IF;
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 END Update_Schedule;
558
559
560 PROCEDURE Delete_Schedule
561 (p_api_version IN NUMBER,
562 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
563 p_commit IN VARCHAR2 := FND_API.G_FALSE,
564 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
565 p_name IN CN_RATE_SCHEDULES.NAME%TYPE,
566 -- Start - R12 MOAC Changes
567 p_object_version_number IN CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE, -- new
568 -- End - R12 MOAC Changes
569 x_return_status OUT NOCOPY VARCHAR2,
570 x_msg_count OUT NOCOPY NUMBER,
571 x_msg_data OUT NOCOPY VARCHAR2) IS
572
573 l_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE := 0;
574 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Schedule';
575 l_api_version CONSTANT NUMBER := 1.0;
576 BEGIN
580 IF NOT FND_API.Compatible_API_Call
577 -- Standard Start of API savepoint
578 SAVEPOINT Delete_Schedule;
579 -- Standard call to check for call compatibility.
581 (l_api_version ,
582 p_api_version ,
583 l_api_name ,
584 G_PKG_NAME )
585 THEN
586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
587 END IF;
588 -- Initialize message list if p_init_msg_list is set to TRUE.
589 IF FND_API.to_Boolean( p_init_msg_list ) THEN
590 FND_MSG_PUB.initialize;
591 END IF;
592 -- Initialize API return status to success
593 x_return_status := FND_API.G_RET_STS_SUCCESS;
594
595 -- API body
596
597 -- validate ID
598 l_rate_schedule_id := get_rate_schedule_id(p_name);
599
600 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
601 'DELETE_SCHEDULE', 'B', 'C') then
602 CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_SCHEDULE_PRE
603 (p_name => p_name,
604 x_return_status => x_return_status,
605 x_msg_count => x_msg_count,
606 x_msg_data => x_msg_data);
607
608 check_ret_sts(x_return_status);
609 end if;
610
611 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
612 'DELETE_SCHEDULE', 'B', 'V') then
613 CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_SCHEDULE_PRE
614 (p_name => p_name,
615 x_return_status => x_return_status,
616 x_msg_count => x_msg_count,
617 x_msg_data => x_msg_data);
618
619 check_ret_sts(x_return_status);
620 end if;
621
622 CN_MULTI_RATE_SCHEDULES_PVT.Delete_Schedule
623 (p_api_version => p_api_version,
624 p_init_msg_list => p_init_msg_list,
625 p_commit => p_commit,
626 p_validation_level => p_validation_level,
627 p_rate_schedule_id => l_rate_schedule_id,
628 -- Start - R12 MOAC Changes
629 p_object_version_number => p_object_version_number,
630 -- End - R12 MOAC Changes
631 x_return_status => x_return_status,
632 x_msg_count => x_msg_count,
633 x_msg_data => x_msg_data);
634
635 check_ret_sts(x_return_status);
636
637 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
638 'DELETE_SCHEDULE', 'A', 'V') then
639 CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_SCHEDULE_POST
640 (p_name => p_name,
641 x_return_status => x_return_status,
642 x_msg_count => x_msg_count,
643 x_msg_data => x_msg_data);
644
645 check_ret_sts(x_return_status);
646 end if;
647
648 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
649 'DELETE_SCHEDULE', 'A', 'C') then
650 CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_SCHEDULE_POST
651 (p_name => p_name,
652 x_return_status => x_return_status,
653 x_msg_count => x_msg_count,
654 x_msg_data => x_msg_data);
655
656 check_ret_sts(x_return_status);
657 end if;
658
659 -- End of API body.
660
661 -- Standard check of p_commit.
662 IF FND_API.To_Boolean( p_commit ) THEN
663 COMMIT WORK;
664 END IF;
665 -- Standard call to get message count and if count is 1, get message info.
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 EXCEPTION
671 WHEN FND_API.G_EXC_ERROR THEN
672 ROLLBACK TO Delete_Schedule;
673 x_return_status := FND_API.G_RET_STS_ERROR ;
674 FND_MSG_PUB.count_and_get
675 (p_count => x_msg_count ,
676 p_data => x_msg_data ,
677 p_encoded => FND_API.G_FALSE );
678 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
679 ROLLBACK TO Delete_Schedule;
680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
681 FND_MSG_PUB.count_and_get
682 (p_count => x_msg_count ,
683 p_data => x_msg_data ,
684 p_encoded => FND_API.G_FALSE );
685 WHEN OTHERS THEN
686 ROLLBACK TO Delete_Schedule;
687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
688 IF FND_MSG_PUB.check_msg_level
689 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
690 THEN
691 FND_MSG_PUB.add_exc_msg
692 (G_PKG_NAME ,
693 l_api_name );
694 END IF;
695 FND_MSG_PUB.count_and_get
696 (p_count => x_msg_count ,
697 p_data => x_msg_data ,
698 p_encoded => FND_API.G_FALSE );
699 END Delete_Schedule;
700
701 PROCEDURE Create_Dimension_Assign
702 (p_api_version IN NUMBER,
703 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
704 p_commit IN VARCHAR2 := FND_API.G_FALSE,
708 p_rate_dim_sequence IN CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE,
705 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
706 p_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
707 p_rate_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
709 -- Start - MOAC Change
710 p_org_id IN CN_RATE_DIMENSIONS.ORG_ID%TYPE := NULL,
711 -- End - MOAC Change
712 x_return_status OUT NOCOPY VARCHAR2,
713 x_msg_count OUT NOCOPY NUMBER,
714 x_msg_data OUT NOCOPY VARCHAR2) IS
715
716 l_api_name CONSTANT VARCHAR2(30) := 'Create_Dimension_Assign';
717 l_api_version CONSTANT NUMBER := 1.0;
718 l_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE;
719 l_rate_dimension_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE;
720 l_rate_sch_dim_id CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE;
721 -- Start - MOAC Change
722 l_org_id NUMBER;
723 l_status VARCHAR2(1);
724 -- End - MOAC Change
725
726 BEGIN
727 -- Standard Start of API savepoint
728 SAVEPOINT Create_Dimension_Assign;
729 -- Standard call to check for call compatibility.
730 IF NOT FND_API.Compatible_API_Call
731 (l_api_version ,
732 p_api_version ,
733 l_api_name ,
734 G_PKG_NAME )
735 THEN
736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
737 END IF;
738 -- Initialize message list if p_init_msg_list is set to TRUE.
739 IF FND_API.to_Boolean( p_init_msg_list ) THEN
740 FND_MSG_PUB.initialize;
741 END IF;
742 -- Initialize API return status to success
743 x_return_status := FND_API.G_RET_STS_SUCCESS;
744
745 -- API body
746
747 -- Start - R12 MOAC Changes
748 l_org_id := p_org_id;
749 mo_global.validate_orgid_pub_api(org_id => l_org_id,
750 status => l_status);
751 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
752 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
753 'cn.plsql.cn_multi_rate_schedules_pub.create_dimension_assign.org_validate',
754 'Validated org_id = ' || l_org_id || ' status = '||l_status);
755 end if;
756 -- End - R12 MOAC Changes
757
758 -- look up ID's
759 l_rate_schedule_id := get_rate_schedule_id (p_rate_schedule_name);
760 l_rate_dimension_id := get_rate_dimension_id(p_rate_dimension_name);
761
762 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
763 'CREATE_DIMENSION_ASSIGN', 'B', 'C') then
764 CN_MULTI_RATE_SCHEDULES_CUHK.CREATE_DIMENSION_ASSIGN_PRE
765 (p_rate_schedule_name => p_rate_schedule_name,
766 p_rate_dimension_name => p_rate_dimension_name,
767 p_rate_dim_sequence => p_rate_dim_sequence,
768 x_return_status => x_return_status,
769 x_msg_count => x_msg_count,
770 x_msg_data => x_msg_data);
771
772 check_ret_sts(x_return_status);
773 end if;
774
775 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
776 'CREATE_DIMENSION_ASSIGN', 'B', 'V') then
777 CN_MULTI_RATE_SCHEDULES_VUHK.CREATE_DIMENSION_ASSIGN_PRE
778 (p_rate_schedule_name => p_rate_schedule_name,
779 p_rate_dimension_name => p_rate_dimension_name,
780 p_rate_dim_sequence => p_rate_dim_sequence,
781 x_return_status => x_return_status,
782 x_msg_count => x_msg_count,
783 x_msg_data => x_msg_data);
784
785 check_ret_sts(x_return_status);
786 end if;
787
788 CN_MULTI_RATE_SCHEDULES_PVT.create_dimension_assign
789 (p_api_version => p_api_version,
790 p_init_msg_list => p_init_msg_list,
791 p_commit => p_commit,
792 p_validation_level => p_validation_level,
793 p_rate_schedule_id => l_rate_schedule_id,
794 p_rate_dimension_id => l_rate_dimension_id,
795 p_rate_dim_sequence => p_rate_dim_sequence,
796 -- Start - R12 MOAC Changes
797 p_org_id => p_org_id,
798 -- End - R12 MOAC Changes
799 x_rate_sch_dim_id => l_rate_sch_dim_id,
800 x_return_status => x_return_status,
801 x_msg_count => x_msg_count,
802 x_msg_data => x_msg_data);
803
804 check_ret_sts(x_return_status);
805
806 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
807 'CREATE_DIMENSION_ASSIGN', 'A', 'V') then
808 CN_MULTI_RATE_SCHEDULES_VUHK.CREATE_DIMENSION_ASSIGN_POST
809 (p_rate_schedule_name => p_rate_schedule_name,
810 p_rate_dimension_name => p_rate_dimension_name,
811 p_rate_dim_sequence => p_rate_dim_sequence,
812 x_return_status => x_return_status,
813 x_msg_count => x_msg_count,
814 x_msg_data => x_msg_data);
815
816 check_ret_sts(x_return_status);
817 end if;
818
819 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
820 'CREATE_DIMENSION_ASSIGN', 'A', 'C') then
821 CN_MULTI_RATE_SCHEDULES_CUHK.CREATE_DIMENSION_ASSIGN_POST
822 (p_rate_schedule_name => p_rate_schedule_name,
823 p_rate_dimension_name => p_rate_dimension_name,
827 x_msg_data => x_msg_data);
824 p_rate_dim_sequence => p_rate_dim_sequence,
825 x_return_status => x_return_status,
826 x_msg_count => x_msg_count,
828
829 check_ret_sts(x_return_status);
830 end if;
831
832 -- End of API body.
833
834 -- Standard check of p_commit.
835 IF FND_API.To_Boolean( p_commit ) THEN
836 COMMIT WORK;
837 END IF;
838 -- Standard call to get message count and if count is 1, get message info.
839 FND_MSG_PUB.Count_And_Get
840 (p_count => x_msg_count ,
841 p_data => x_msg_data ,
842 p_encoded => FND_API.G_FALSE );
843 EXCEPTION
844 WHEN FND_API.G_EXC_ERROR THEN
845 ROLLBACK TO Create_Dimension_Assign;
846 x_return_status := FND_API.G_RET_STS_ERROR ;
847 FND_MSG_PUB.count_and_get
848 (p_count => x_msg_count ,
849 p_data => x_msg_data ,
850 p_encoded => FND_API.G_FALSE );
851 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
852 ROLLBACK TO Create_Dimension_Assign;
853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
854 FND_MSG_PUB.count_and_get
855 (p_count => x_msg_count ,
856 p_data => x_msg_data ,
857 p_encoded => FND_API.G_FALSE );
858 WHEN OTHERS THEN
859 ROLLBACK TO Create_Dimension_Assign;
860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
861 IF FND_MSG_PUB.check_msg_level
862 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
863 THEN
864 FND_MSG_PUB.add_exc_msg
865 (G_PKG_NAME ,
866 l_api_name );
867 END IF;
868 FND_MSG_PUB.count_and_get
869 (p_count => x_msg_count ,
870 p_data => x_msg_data ,
871 p_encoded => FND_API.G_FALSE );
872 END Create_Dimension_Assign;
873
874
875 PROCEDURE Update_Dimension_Assign
876 (p_api_version IN NUMBER,
877 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
878 p_commit IN VARCHAR2 := FND_API.G_FALSE,
879 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
880 p_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
881 p_orig_rate_dim_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
882 p_new_rate_dim_name IN CN_RATE_DIMENSIONS.NAME%TYPE := cn_api.g_miss_char,
883 p_rate_dim_sequence IN CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE :=
884 cn_api.g_miss_num,
885 p_object_version_number IN OUT NOCOPY CN_RATE_SCH_DIMS.OBJECT_VERSION_NUMBER%TYPE,
886 -- Start - MOAC Change
887 p_org_id IN CN_RATE_DIMENSIONS.ORG_ID%TYPE,
888 -- End - MOAC Change
889 x_return_status OUT NOCOPY VARCHAR2,
890 x_msg_count OUT NOCOPY NUMBER,
891 x_msg_data OUT NOCOPY VARCHAR2) IS
892
893 l_api_name CONSTANT VARCHAR2(30) := 'Update_Dimension_Assign';
894 l_api_version CONSTANT NUMBER := 1.0;
895 l_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE;
896 l_rate_dimension_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE;
897 l_rate_dim_sequence CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE;
898 l_rate_sch_dim_id CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE;
899 -- Start - MOAC Change
900 l_org_id NUMBER;
901 -- End - MOAC Change
902 BEGIN
903 -- Standard Start of API savepoint
904 SAVEPOINT Update_Dimension_Assign;
905 -- Standard call to check for call compatibility.
906 IF NOT FND_API.Compatible_API_Call
907 (l_api_version ,
908 p_api_version ,
909 l_api_name ,
910 G_PKG_NAME )
911 THEN
912 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
913 END IF;
914 -- Initialize message list if p_init_msg_list is set to TRUE.
915 IF FND_API.to_Boolean( p_init_msg_list ) THEN
916 FND_MSG_PUB.initialize;
917 END IF;
918 -- Initialize API return status to success
919 x_return_status := FND_API.G_RET_STS_SUCCESS;
920
921 -- API body
922
923 -- get ID's
924 l_rate_schedule_id := get_rate_schedule_id (p_rate_schedule_name);
925 l_rate_dimension_id := get_rate_dimension_id(p_orig_rate_dim_name);
926 l_rate_sch_dim_id := get_rate_sch_dim_id (l_rate_schedule_id, l_rate_dimension_id);
927
928 -- Start - MOAC Change
929 SELECT org_id INTO l_org_id
930 FROM cn_rate_sch_dims
931 WHERE rate_sch_dim_id = l_rate_sch_dim_id;
932
933 IF (l_org_id <> p_org_id)
934 THEN
935 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
936 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
937 THEN
938 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
939 'cn.plsql.cn_multi_rate_schedule_pub.update_dimension_assign.error',
940 true);
941 END IF;
942
943 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
947
944 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
945 FND_MSG_PUB.Add;
946 END IF;
948 RAISE FND_API.G_EXC_ERROR ;
949 END IF;
950 -- End - MOAC Change
951 -- get rate_dim_sequence (validation for this select already performed)
952 select rate_dim_sequence into l_rate_dim_sequence
953 from cn_rate_sch_dims
954 where rate_sch_dim_id = l_rate_sch_dim_id;
955
956 -- get new rate dim name if necessary
957 if p_new_rate_dim_name <> cn_api.g_miss_char then
958 l_rate_dimension_id := get_rate_dimension_id(p_new_rate_dim_name);
959 end if;
960
961 if p_rate_dim_sequence <> cn_api.g_miss_num then
962 l_rate_dim_sequence := p_rate_dim_sequence;
963 end if;
964
965 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
966 'UPDATE_DIMENSION_ASSIGN', 'B', 'C') then
967 CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_DIMENSION_ASSIGN_PRE
968 (p_rate_schedule_name => p_rate_schedule_name,
969 p_orig_rate_dim_name => p_orig_rate_dim_name,
970 p_new_rate_dim_name => p_new_rate_dim_name,
971 p_rate_dim_sequence => p_rate_dim_sequence,
972 p_object_version_number => p_object_version_number,
973 x_return_status => x_return_status,
974 x_msg_count => x_msg_count,
975 x_msg_data => x_msg_data);
976
977 check_ret_sts(x_return_status);
978 end if;
979
980 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
981 'UPDATE_DIMENSION_ASSIGN', 'B', 'V') then
982 CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_DIMENSION_ASSIGN_PRE
983 (p_rate_schedule_name => p_rate_schedule_name,
984 p_orig_rate_dim_name => p_orig_rate_dim_name,
985 p_new_rate_dim_name => p_new_rate_dim_name,
986 p_rate_dim_sequence => p_rate_dim_sequence,
987 p_object_version_number => p_object_version_number,
988 x_return_status => x_return_status,
989 x_msg_count => x_msg_count,
990 x_msg_data => x_msg_data);
991
992 check_ret_sts(x_return_status);
993 end if;
994
995 CN_MULTI_RATE_SCHEDULES_PVT.update_dimension_assign
996 (p_api_version => p_api_version,
997 p_init_msg_list => p_init_msg_list,
998 p_commit => p_commit,
999 p_validation_level => p_validation_level,
1000 p_rate_sch_dim_id => l_rate_sch_dim_id,
1001 p_rate_schedule_id => l_rate_schedule_id,
1002 p_rate_dimension_id => l_rate_dimension_id,
1003 p_rate_dim_sequence => l_rate_dim_sequence,
1004 -- Start - MOAC Change
1005 p_org_id => p_org_id,
1006 -- End - MOAC Change
1007 p_object_version_number => p_object_version_number,
1008 x_return_status => x_return_status,
1009 x_msg_count => x_msg_count,
1010 x_msg_data => x_msg_data);
1011
1012 check_ret_sts(x_return_status);
1013
1014 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1015 'UPDATE_DIMENSION_ASSIGN', 'A', 'V') then
1016 CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_DIMENSION_ASSIGN_POST
1017 (p_rate_schedule_name => p_rate_schedule_name,
1018 p_orig_rate_dim_name => p_orig_rate_dim_name,
1019 p_new_rate_dim_name => p_new_rate_dim_name,
1020 p_rate_dim_sequence => p_rate_dim_sequence,
1021 p_object_version_number => p_object_version_number,
1022 x_return_status => x_return_status,
1023 x_msg_count => x_msg_count,
1024 x_msg_data => x_msg_data);
1025
1026 check_ret_sts(x_return_status);
1027 end if;
1028
1029 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1030 'UPDATE_DIMENSION_ASSIGN', 'A', 'C') then
1031 CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_DIMENSION_ASSIGN_POST
1032 (p_rate_schedule_name => p_rate_schedule_name,
1033 p_orig_rate_dim_name => p_orig_rate_dim_name,
1034 p_new_rate_dim_name => p_new_rate_dim_name,
1035 p_rate_dim_sequence => p_rate_dim_sequence,
1036 p_object_version_number => p_object_version_number,
1037 x_return_status => x_return_status,
1038 x_msg_count => x_msg_count,
1039 x_msg_data => x_msg_data);
1040
1041 check_ret_sts(x_return_status);
1042 end if;
1043
1044 -- End of API body.
1045
1046 -- Standard check of p_commit.
1047 IF FND_API.To_Boolean( p_commit ) THEN
1048 COMMIT WORK;
1049 END IF;
1050 -- Standard call to get message count and if count is 1, get message info.
1051 FND_MSG_PUB.Count_And_Get
1052 (p_count => x_msg_count ,
1053 p_data => x_msg_data ,
1054 p_encoded => FND_API.G_FALSE );
1055 EXCEPTION
1056 WHEN FND_API.G_EXC_ERROR THEN
1057 ROLLBACK TO Update_Dimension_Assign;
1058 x_return_status := FND_API.G_RET_STS_ERROR ;
1059 FND_MSG_PUB.count_and_get
1060 (p_count => x_msg_count ,
1061 p_data => x_msg_data ,
1062 p_encoded => FND_API.G_FALSE );
1063 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1064 ROLLBACK TO Update_Dimension_Assign;
1068 p_data => x_msg_data ,
1065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1066 FND_MSG_PUB.count_and_get
1067 (p_count => x_msg_count ,
1069 p_encoded => FND_API.G_FALSE );
1070 WHEN OTHERS THEN
1071 ROLLBACK TO Update_Dimension_Assign;
1072 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1073 IF FND_MSG_PUB.check_msg_level
1074 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1075 THEN
1076 FND_MSG_PUB.add_exc_msg
1077 (G_PKG_NAME ,
1078 l_api_name );
1079 END IF;
1080 FND_MSG_PUB.count_and_get
1081 (p_count => x_msg_count ,
1082 p_data => x_msg_data ,
1083 p_encoded => FND_API.G_FALSE );
1084 END Update_Dimension_Assign;
1085
1086 PROCEDURE Delete_Dimension_Assign
1087 (p_api_version IN NUMBER,
1088 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1089 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1090 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1091 p_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
1092 p_rate_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
1093 -- Start - R12 MOAC Changes
1094 p_object_version_number IN CN_RATE_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE, -- new
1095 -- End - R12 MOAC Changes
1096 x_return_status OUT NOCOPY VARCHAR2,
1097 x_msg_count OUT NOCOPY NUMBER,
1098 x_msg_data OUT NOCOPY VARCHAR2) IS
1099
1100 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Dimension_Assign';
1101 l_api_version CONSTANT NUMBER := 1.0;
1102 l_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE;
1103 l_rate_dimension_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE;
1104 l_rate_sch_dim_id CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE;
1105 BEGIN
1106 -- Standard Start of API savepoint
1107 SAVEPOINT Delete_Dimension_Assign;
1108 -- Standard call to check for call compatibility.
1109 IF NOT FND_API.Compatible_API_Call
1110 (l_api_version ,
1111 p_api_version ,
1112 l_api_name ,
1113 G_PKG_NAME )
1114 THEN
1115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1116 END IF;
1117 -- Initialize message list if p_init_msg_list is set to TRUE.
1118 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1119 FND_MSG_PUB.initialize;
1120 END IF;
1121 -- Initialize API return status to success
1122 x_return_status := FND_API.G_RET_STS_SUCCESS;
1123
1124 -- API body
1125
1126 l_rate_schedule_id := get_rate_schedule_id (p_rate_schedule_name);
1127 l_rate_dimension_id := get_rate_dimension_id(p_rate_dimension_name);
1128 l_rate_sch_dim_id := get_rate_sch_dim_id (l_rate_schedule_id, l_rate_dimension_id);
1129
1130 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1131 'DELETE_DIMENSION_ASSIGN', 'B', 'C') then
1132 CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_DIMENSION_ASSIGN_PRE
1133 (p_rate_schedule_name => p_rate_schedule_name,
1134 p_rate_dimension_name => p_rate_dimension_name,
1135 x_return_status => x_return_status,
1136 x_msg_count => x_msg_count,
1137 x_msg_data => x_msg_data);
1138
1139 check_ret_sts(x_return_status);
1140 end if;
1141
1142 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1143 'DELETE_DIMENSION_ASSIGN', 'B', 'V') then
1144 CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_DIMENSION_ASSIGN_PRE
1145 (p_rate_schedule_name => p_rate_schedule_name,
1146 p_rate_dimension_name => p_rate_dimension_name,
1147 x_return_status => x_return_status,
1148 x_msg_count => x_msg_count,
1149 x_msg_data => x_msg_data);
1150
1151 check_ret_sts(x_return_status);
1152 end if;
1153
1154 CN_MULTI_RATE_SCHEDULES_PVT.delete_dimension_assign
1155 (p_api_version => p_api_version,
1156 p_init_msg_list => p_init_msg_list,
1157 p_commit => p_commit,
1158 p_validation_level => p_validation_level,
1159 p_rate_sch_dim_id => l_rate_sch_dim_id,
1160 p_rate_schedule_id => l_rate_schedule_id,
1161 -- Start - R12 MOAC Changes
1162 p_object_version_number => p_object_version_number,
1163 -- End - R12 MOAC Changes
1164 x_return_status => x_return_status,
1165 x_msg_count => x_msg_count,
1166 x_msg_data => x_msg_data);
1167
1168 check_ret_sts(x_return_status);
1169
1170 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1171 'DELETE_DIMENSION_ASSIGN', 'A', 'V') then
1172 CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_DIMENSION_ASSIGN_POST
1173 (p_rate_schedule_name => p_rate_schedule_name,
1174 p_rate_dimension_name => p_rate_dimension_name,
1175 x_return_status => x_return_status,
1176 x_msg_count => x_msg_count,
1177 x_msg_data => x_msg_data);
1178
1179 check_ret_sts(x_return_status);
1180 end if;
1181
1182 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1183 'DELETE_DIMENSION_ASSIGN', 'A', 'C') then
1187 x_return_status => x_return_status,
1184 CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_DIMENSION_ASSIGN_POST
1185 (p_rate_schedule_name => p_rate_schedule_name,
1186 p_rate_dimension_name => p_rate_dimension_name,
1188 x_msg_count => x_msg_count,
1189 x_msg_data => x_msg_data);
1190
1191 check_ret_sts(x_return_status);
1192 end if;
1193
1194 -- End of API body.
1195
1196 -- Standard check of p_commit.
1197 IF FND_API.To_Boolean( p_commit ) THEN
1198 COMMIT WORK;
1199 END IF;
1200 -- Standard call to get message count and if count is 1, get message info.
1201 FND_MSG_PUB.Count_And_Get
1202 (p_count => x_msg_count ,
1203 p_data => x_msg_data ,
1204 p_encoded => FND_API.G_FALSE );
1205 EXCEPTION
1206 WHEN FND_API.G_EXC_ERROR THEN
1207 ROLLBACK TO Delete_Dimension_Assign;
1208 x_return_status := FND_API.G_RET_STS_ERROR ;
1209 FND_MSG_PUB.count_and_get
1210 (p_count => x_msg_count ,
1211 p_data => x_msg_data ,
1212 p_encoded => FND_API.G_FALSE );
1213 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1214 ROLLBACK TO Delete_Dimension_Assign;
1215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1216 FND_MSG_PUB.count_and_get
1217 (p_count => x_msg_count ,
1218 p_data => x_msg_data ,
1219 p_encoded => FND_API.G_FALSE );
1220 WHEN OTHERS THEN
1221 ROLLBACK TO Delete_Dimension_Assign;
1222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1223 IF FND_MSG_PUB.check_msg_level
1224 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1225 THEN
1226 FND_MSG_PUB.add_exc_msg
1227 (G_PKG_NAME ,
1228 l_api_name );
1229 END IF;
1230 FND_MSG_PUB.count_and_get
1231 (p_count => x_msg_count ,
1232 p_data => x_msg_data ,
1233 p_encoded => FND_API.G_FALSE );
1234 END Delete_Dimension_Assign;
1235
1236 PROCEDURE Update_Rate
1237 (p_api_version IN NUMBER,
1238 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1239 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1240 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1241 p_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
1242 p_tier_coordinates_tbl IN tier_coordinates_tbl,
1243 p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
1244 p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE,
1245 -- Start - MOAC Change
1246 p_org_id IN CN_RATE_TIERS.ORG_ID%TYPE,
1247 -- End - MOAC Change
1248 x_return_status OUT NOCOPY VARCHAR2,
1249 x_msg_count OUT NOCOPY NUMBER,
1250 x_msg_data OUT NOCOPY VARCHAR2) IS
1251
1252 l_api_name CONSTANT VARCHAR2(30) := 'Update_Rate';
1253 l_api_version CONSTANT NUMBER := 1.0;
1254 l_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE;
1255 l_rate_tier_id CN_RATE_TIERS.RATE_TIER_ID%TYPE;
1256 l_rate_sequence CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE;
1257 l_commission_amount CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE;
1258 l_object_version_number CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE;
1259 l_rate_dim_tier_id_tbl CN_MULTI_RATE_SCHEDULES_PVT.num_tbl_type;
1260 -- Start - MOAC Change
1261 l_org_id NUMBER;
1262 -- End - MOAC Change
1263
1264 BEGIN
1265 -- Standard Start of API savepoint
1266 SAVEPOINT Update_Rate;
1267 -- Standard call to check for call compatibility.
1268 IF NOT FND_API.Compatible_API_Call
1269 (l_api_version ,
1270 p_api_version ,
1271 l_api_name ,
1272 G_PKG_NAME )
1273 THEN
1274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1275 END IF;
1276 -- Initialize message list if p_init_msg_list is set to TRUE.
1277 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1278 FND_MSG_PUB.initialize;
1279 END IF;
1280 -- Initialize API return status to success
1281 x_return_status := FND_API.G_RET_STS_SUCCESS;
1282
1283 -- API body
1284
1285 l_rate_schedule_id := get_rate_schedule_id(p_rate_schedule_name);
1286
1287 -- Start - MOAC Change
1288 SELECT org_id INTO l_org_id
1289 FROM cn_rate_tiers
1290 WHERE rate_schedule_id = l_rate_schedule_id;
1291
1292 IF (l_org_id <> p_org_id)
1293 THEN
1294 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
1295 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1296 THEN
1297 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
1298 'cn.plsql.cn_multi_rate_schedule_pub.update_rate.error',
1299 true);
1300 END IF;
1301
1302 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1303 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
1304 FND_MSG_PUB.Add;
1305 END IF;
1306
1307 RAISE FND_API.G_EXC_ERROR ;
1311 for i in p_tier_coordinates_tbl.first..p_tier_coordinates_tbl.last loop
1308 END IF;
1309 -- End - MOAC Change
1310
1312 l_rate_dim_tier_id_tbl(i) := get_rate_dim_tier_id
1313 (l_rate_schedule_id, i, p_tier_coordinates_tbl(i));
1314 end loop;
1315
1316 CN_MULTI_RATE_SCHEDULES_PVT.get_rate_tier_info
1317 (p_rate_schedule_id => l_rate_schedule_id,
1318 p_rate_dim_tier_id_tbl => l_rate_dim_tier_id_tbl,
1319 x_rate_tier_id => l_rate_tier_id,
1320 x_rate_sequence => l_rate_sequence,
1321 x_commission_amount => l_commission_amount, -- not used
1322 x_object_version_number => l_object_version_number); -- not used
1323
1324 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1325 'UPDATE_RATE', 'B', 'C') then
1326 CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_RATE_PRE
1327 (p_rate_schedule_name => p_rate_schedule_name,
1328 p_tier_coordinates_tbl => p_tier_coordinates_tbl,
1329 p_commission_amount => p_commission_amount,
1330 p_object_version_number => p_object_version_number,
1331 x_return_status => x_return_status,
1332 x_msg_count => x_msg_count,
1333 x_msg_data => x_msg_data);
1334
1335 check_ret_sts(x_return_status);
1336 end if;
1337
1338 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1339 'UPDATE_RATE', 'B', 'V') then
1340 CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_RATE_PRE
1341 (p_rate_schedule_name => p_rate_schedule_name,
1342 p_tier_coordinates_tbl => p_tier_coordinates_tbl,
1343 p_commission_amount => p_commission_amount,
1344 p_object_version_number => p_object_version_number,
1345 x_return_status => x_return_status,
1346 x_msg_count => x_msg_count,
1347 x_msg_data => x_msg_data);
1348
1349 check_ret_sts(x_return_status);
1350 end if;
1351
1352 CN_MULTI_RATE_SCHEDULES_PVT.update_rate
1353 (p_rate_schedule_id => l_rate_schedule_id,
1354 p_rate_sequence => l_rate_sequence,
1355 p_commission_amount => p_commission_amount,
1356 p_object_version_number => p_object_version_number,
1357 -- Start - MOAC Change
1358 p_org_id => p_org_id);
1359 -- End - MOAC Change
1360
1361
1362 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1363 'UPDATE_RATE', 'A', 'V') then
1364 CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_RATE_POST
1365 (p_rate_schedule_name => p_rate_schedule_name,
1366 p_tier_coordinates_tbl => p_tier_coordinates_tbl,
1367 p_commission_amount => p_commission_amount,
1368 p_object_version_number => p_object_version_number,
1369 x_return_status => x_return_status,
1370 x_msg_count => x_msg_count,
1371 x_msg_data => x_msg_data);
1372
1373 check_ret_sts(x_return_status);
1374 end if;
1375
1376 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1377 'UPDATE_RATE', 'A', 'C') then
1378 CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_RATE_POST
1379 (p_rate_schedule_name => p_rate_schedule_name,
1380 p_tier_coordinates_tbl => p_tier_coordinates_tbl,
1381 p_commission_amount => p_commission_amount,
1382 p_object_version_number => p_object_version_number,
1383 x_return_status => x_return_status,
1384 x_msg_count => x_msg_count,
1385 x_msg_data => x_msg_data);
1386
1387 check_ret_sts(x_return_status);
1388 end if;
1389
1390 -- End of API body.
1391
1392 -- Standard check of p_commit.
1393 IF FND_API.To_Boolean( p_commit ) THEN
1394 COMMIT WORK;
1395 END IF;
1396 -- Standard call to get message count and if count is 1, get message info.
1397 FND_MSG_PUB.Count_And_Get
1398 (p_count => x_msg_count ,
1399 p_data => x_msg_data ,
1400 p_encoded => FND_API.G_FALSE );
1401 EXCEPTION
1402 WHEN FND_API.G_EXC_ERROR THEN
1403 ROLLBACK TO Update_Rate;
1404 x_return_status := FND_API.G_RET_STS_ERROR ;
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 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1410 ROLLBACK TO Update_Rate;
1411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1412 FND_MSG_PUB.count_and_get
1413 (p_count => x_msg_count ,
1414 p_data => x_msg_data ,
1415 p_encoded => FND_API.G_FALSE );
1416 WHEN OTHERS THEN
1417 ROLLBACK TO Update_Rate;
1418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1419 IF FND_MSG_PUB.check_msg_level
1420 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1421 THEN
1422 FND_MSG_PUB.add_exc_msg
1423 (G_PKG_NAME ,
1424 l_api_name );
1425 END IF;
1426 FND_MSG_PUB.count_and_get
1427 (p_count => x_msg_count ,
1428 p_data => x_msg_data ,
1429 p_encoded => FND_API.G_FALSE );
1430 END Update_Rate;
1431
1435 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1432 PROCEDURE Create_Dimension
1433 (p_api_version IN NUMBER,
1434 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1436 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1437 p_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
1438 p_description IN CN_RATE_DIMENSIONS.DESCRIPTION%TYPE := NULL,
1439 p_dim_unit_code IN CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE,
1440 p_tiers_tbl IN rate_tier_tbl_type := g_miss_rate_tier_tbl,
1441 -- Start - MOAC Change
1442 p_org_id IN CN_RATE_DIMENSIONS.ORG_ID%TYPE := NULL,
1443 -- End - MOAC Change
1444 x_return_status OUT NOCOPY VARCHAR2,
1445 x_msg_count OUT NOCOPY NUMBER,
1446 x_msg_data OUT NOCOPY VARCHAR2) IS
1447
1448 l_api_name CONSTANT VARCHAR2(30) := 'Create_Dimension';
1449 l_api_version CONSTANT NUMBER := 1.0;
1450 l_rate_dimension_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE;
1451 l_tiers_tbl CN_RATE_DIMENSIONS_PVT.TIERS_TBL_TYPE;
1452 -- Start - MOAC Change
1453 l_org_id NUMBER;
1454 l_status VARCHAR2(1);
1455 -- End - MOAC Change
1456
1457 BEGIN
1458
1459 -- Standard Start of API savepoint
1460 SAVEPOINT Create_Dimension;
1461 -- Standard call to check for call compatibility.
1462 IF NOT FND_API.Compatible_API_Call
1463 (l_api_version ,
1464 p_api_version ,
1465 l_api_name ,
1466 G_PKG_NAME )
1467 THEN
1468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1469 END IF;
1470 -- Initialize message list if p_init_msg_list is set to TRUE.
1471 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1472 FND_MSG_PUB.initialize;
1473 END IF;
1474 -- Initialize API return status to success
1475 x_return_status := FND_API.G_RET_STS_SUCCESS;
1476
1477 -- API body
1478
1479 -- Start - R12 MOAC Changes
1480 l_org_id := p_org_id;
1481 mo_global.validate_orgid_pub_api(org_id => l_org_id,
1482 status => l_status);
1483 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1484 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1485 'cn.plsql.cn_multi_rate_schedules_pub.create_dimension.org_validate',
1486 'Validated org_id = ' || l_org_id || ' status = '||l_status);
1487 end if;
1488 -- End - R12 MOAC Changes
1489
1490 -- build l_tiers_tbl from the p_tiers_tbl
1491 if p_tiers_tbl.count > 0 then
1492 for i in p_tiers_tbl.first..p_tiers_tbl.last loop
1493 l_tiers_tbl(i).tier_sequence := p_tiers_tbl(i).tier_sequence;
1494 translate_values(p_dim_unit_code, p_tiers_tbl(i).value1, p_tiers_tbl(i).value2,
1495 l_tiers_tbl(i).minimum_amount, l_tiers_tbl(i).maximum_amount,
1496 l_tiers_tbl(i).min_exp_id, l_tiers_tbl(i).max_exp_id,
1497 l_tiers_tbl(i).string_value);
1498 end loop;
1499 end if;
1500
1501 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1502 'CREATE_DIMENSION', 'B', 'C') then
1503 CN_MULTI_RATE_SCHEDULES_CUHK.CREATE_DIMENSION_PRE
1504 (p_name => p_name,
1505 p_description => p_description,
1506 p_dim_unit_code => p_dim_unit_code,
1507 p_tiers_tbl => p_tiers_tbl,
1508 x_return_status => x_return_status,
1509 x_msg_count => x_msg_count,
1510 x_msg_data => x_msg_data);
1511
1512 check_ret_sts(x_return_status);
1513 end if;
1514
1515 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1516 'CREATE_DIMENSION', 'B', 'V') then
1517 CN_MULTI_RATE_SCHEDULES_VUHK.CREATE_DIMENSION_PRE
1518 (p_name => p_name,
1519 p_description => p_description,
1520 p_dim_unit_code => p_dim_unit_code,
1521 p_tiers_tbl => p_tiers_tbl,
1522 x_return_status => x_return_status,
1523 x_msg_count => x_msg_count,
1524 x_msg_data => x_msg_data);
1525
1526 check_ret_sts(x_return_status);
1527 end if;
1528
1529 CN_RATE_DIMENSIONS_PVT.Create_Dimension
1530 (p_api_version => p_api_version,
1531 p_init_msg_list => p_init_msg_list,
1532 p_commit => p_commit,
1533 p_validation_level => p_validation_level,
1534 p_name => p_name,
1535 p_description => p_description,
1536 p_dim_unit_code => p_dim_unit_code,
1537 p_number_tier => l_tiers_tbl.count,
1538 p_tiers_tbl => l_tiers_tbl,
1539 -- Start - MOAC Change
1540 p_org_id => p_org_id,
1541 -- End - MOAC Change
1542 x_rate_dimension_id => l_rate_dimension_id,
1543 x_return_status => x_return_status,
1544 x_msg_count => x_msg_count,
1545 x_msg_data => x_msg_data);
1546
1547 check_ret_sts(x_return_status);
1548
1549 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1550 'CREATE_DIMENSION', 'A', 'V') then
1551 CN_MULTI_RATE_SCHEDULES_VUHK.CREATE_DIMENSION_POST
1555 p_tiers_tbl => p_tiers_tbl,
1552 (p_name => p_name,
1553 p_description => p_description,
1554 p_dim_unit_code => p_dim_unit_code,
1556 x_return_status => x_return_status,
1557 x_msg_count => x_msg_count,
1558 x_msg_data => x_msg_data);
1559
1560 check_ret_sts(x_return_status);
1561 end if;
1562
1563 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1564 'CREATE_DIMENSION', 'A', 'C') then
1565 CN_MULTI_RATE_SCHEDULES_CUHK.CREATE_DIMENSION_POST
1566 (p_name => p_name,
1567 p_description => p_description,
1568 p_dim_unit_code => p_dim_unit_code,
1569 p_tiers_tbl => p_tiers_tbl,
1570 x_return_status => x_return_status,
1571 x_msg_count => x_msg_count,
1572 x_msg_data => x_msg_data);
1573
1574 check_ret_sts(x_return_status);
1575 end if;
1576
1577 -- End of API body.
1578
1579 -- Standard check of p_commit.
1580 IF FND_API.To_Boolean( p_commit ) THEN
1581 COMMIT WORK;
1582 END IF;
1583 -- Standard call to get message count and if count is 1, get message info.
1584 FND_MSG_PUB.Count_And_Get
1585 (p_count => x_msg_count ,
1586 p_data => x_msg_data ,
1587 p_encoded => FND_API.G_FALSE );
1588 EXCEPTION
1589 WHEN FND_API.G_EXC_ERROR THEN
1590 ROLLBACK TO Create_Dimension;
1591 x_return_status := FND_API.G_RET_STS_ERROR ;
1592 FND_MSG_PUB.count_and_get
1593 (p_count => x_msg_count ,
1594 p_data => x_msg_data ,
1595 p_encoded => FND_API.G_FALSE );
1596 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1597 ROLLBACK TO Create_Dimension;
1598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1599 FND_MSG_PUB.count_and_get
1600 (p_count => x_msg_count ,
1601 p_data => x_msg_data ,
1602 p_encoded => FND_API.G_FALSE );
1603 WHEN OTHERS THEN
1604 ROLLBACK TO Create_Dimension;
1605 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1606 IF FND_MSG_PUB.check_msg_level
1607 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1608 THEN
1609 FND_MSG_PUB.add_exc_msg
1610 (G_PKG_NAME ,
1611 l_api_name );
1612 END IF;
1613 FND_MSG_PUB.count_and_get
1614 (p_count => x_msg_count ,
1615 p_data => x_msg_data ,
1616 p_encoded => FND_API.G_FALSE );
1617 END Create_Dimension;
1618
1619 PROCEDURE Update_Dimension
1620 (p_api_version IN NUMBER,
1621 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1622 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1623 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1624 p_original_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
1625 p_new_name IN CN_RATE_DIMENSIONS.NAME%TYPE :=
1626 cn_api.g_miss_char,
1627 p_description IN CN_RATE_DIMENSIONS.DESCRIPTION%TYPE :=
1628 cn_api.g_miss_char,
1629 p_dim_unit_code IN CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE :=
1630 cn_api.g_miss_char,
1631 p_tiers_tbl IN rate_tier_tbl_type :=
1632 g_miss_rate_tier_tbl,
1633 p_object_version_number IN OUT NOCOPY CN_RATE_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE,
1634 -- Start - MOAC Change
1635 p_org_id IN CN_RATE_DIMENSIONS.ORG_ID%TYPE,
1636 -- End - MOAC Change
1637 x_return_status OUT NOCOPY VARCHAR2,
1638 x_msg_count OUT NOCOPY NUMBER,
1639 x_msg_data OUT NOCOPY VARCHAR2) IS
1640
1641 l_api_name CONSTANT VARCHAR2(30) := 'Update_Dimension';
1642 l_api_version CONSTANT NUMBER := 1.0;
1643 l_rate_dimension_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE;
1644 l_description CN_RATE_DIMENSIONS.DESCRIPTION%TYPE;
1645 l_name CN_RATE_DIMENSIONS.NAME%TYPE;
1646 l_tiers_tbl CN_RATE_DIMENSIONS_PVT.TIERS_TBL_TYPE;
1647 -- Start - MOAC Change
1648 l_org_id NUMBER;
1649 -- End - MOAC Change
1650
1651 CURSOR get_rdt_id(p_rate_dimension_id IN CN_RATE_DIM_TIERS.RATE_DIMENSION_ID%TYPE,
1652 p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE) IS
1653 select rate_dim_tier_id from cn_rate_dim_tiers
1654 where rate_dimension_id = p_rate_dimension_id
1655 and tier_sequence = p_tier_sequence;
1656
1657 BEGIN
1658 -- Standard Start of API savepoint
1659 SAVEPOINT Update_Dimension;
1660 -- Standard call to check for call compatibility.
1661 IF NOT FND_API.Compatible_API_Call
1662 (l_api_version ,
1663 p_api_version ,
1664 l_api_name ,
1665 G_PKG_NAME )
1666 THEN
1667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1668 END IF;
1669 -- Initialize message list if p_init_msg_list is set to TRUE.
1673 -- Initialize API return status to success
1670 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1671 FND_MSG_PUB.initialize;
1672 END IF;
1674 x_return_status := FND_API.G_RET_STS_SUCCESS;
1675
1676 -- API body
1677
1678 l_rate_dimension_id := get_rate_dimension_id(p_original_name);
1679
1680 -- Start - MOAC Change
1681 SELECT org_id INTO l_org_id
1682 FROM cn_rate_dimensions
1683 WHERE rate_dimension_id = l_rate_dimension_id;
1684
1685 IF (l_org_id <> p_org_id)
1686 THEN
1687 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
1688 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1689 THEN
1690 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
1691 'cn.plsql.cn_multi_rate_schedule_pub.update_dimenstion.error',
1692 true);
1693 END IF;
1694
1695 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1696 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
1697 FND_MSG_PUB.Add;
1698 END IF;
1699
1700 RAISE FND_API.G_EXC_ERROR ;
1701 END IF;
1702 -- End - MOAC Change
1703
1704 select description into l_description from cn_rate_dimensions
1705 where rate_dimension_id = l_rate_dimension_id;
1706
1707 select decode(p_new_name, cn_api.g_miss_char, p_original_name, p_new_name),
1708 decode(p_description, cn_api.g_miss_char, l_description, p_description)
1709 into l_name, l_description from dual;
1710
1711 -- build l_tiers_tbl from the p_tiers_tbl
1712 if p_tiers_tbl.count > 0 then
1713 for i in p_tiers_tbl.first..p_tiers_tbl.last loop
1714 l_tiers_tbl(i).tier_sequence := p_tiers_tbl(i).tier_sequence;
1715 open get_rdt_id(l_rate_dimension_id, p_tiers_tbl(i).tier_sequence);
1716 fetch get_rdt_id into l_tiers_tbl(i).rate_dim_tier_id;
1717 close get_rdt_id; -- if ID not found, then row is to be newly created
1718
1719 translate_values(p_dim_unit_code, p_tiers_tbl(i).value1, p_tiers_tbl(i).value2,
1720 l_tiers_tbl(i).minimum_amount, l_tiers_tbl(i).maximum_amount,
1721 l_tiers_tbl(i).min_exp_id, l_tiers_tbl(i).max_exp_id,
1722 l_tiers_tbl(i).string_value);
1723 l_tiers_tbl(i).object_version_number := p_tiers_tbl(i).object_version_number;
1724 end loop;
1725 end if;
1726
1727 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1728 'UPDATE_DIMENSION', 'B', 'C') then
1729 CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_DIMENSION_PRE
1730 (p_original_name => p_original_name,
1731 p_new_name => p_new_name,
1732 p_description => p_description,
1733 p_dim_unit_code => p_dim_unit_code,
1734 p_tiers_tbl => p_tiers_tbl,
1735 p_object_version_number => p_object_version_number,
1736 x_return_status => x_return_status,
1737 x_msg_count => x_msg_count,
1738 x_msg_data => x_msg_data);
1739
1740 check_ret_sts(x_return_status);
1741 end if;
1742
1743 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1744 'UPDATE_DIMENSION', 'B', 'V') then
1745 CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_DIMENSION_PRE
1746 (p_original_name => p_original_name,
1747 p_new_name => p_new_name,
1748 p_description => p_description,
1749 p_dim_unit_code => p_dim_unit_code,
1750 p_tiers_tbl => p_tiers_tbl,
1751 p_object_version_number => p_object_version_number,
1752 x_return_status => x_return_status,
1753 x_msg_count => x_msg_count,
1754 x_msg_data => x_msg_data);
1755
1756 check_ret_sts(x_return_status);
1757 end if;
1758
1759 CN_RATE_DIMENSIONS_PVT.Update_Dimension
1760 (p_api_version => p_api_version,
1761 p_init_msg_list => p_init_msg_list,
1762 p_commit => p_commit,
1763 p_validation_level => p_validation_level,
1764 p_rate_dimension_id => l_rate_dimension_id,
1765 p_name => l_name,
1766 p_description => l_description,
1767 p_dim_unit_code => p_dim_unit_code,
1768 p_number_tier => l_tiers_tbl.count,
1769 p_tiers_tbl => l_tiers_tbl,
1770 -- Start - MOAC Change
1771 p_org_id => p_org_id,
1772 -- End - MOAC Change
1773 p_object_version_number => p_object_version_number,
1774 x_return_status => x_return_status,
1775 x_msg_count => x_msg_count,
1776 x_msg_data => x_msg_data);
1777
1778 check_ret_sts(x_return_status);
1779
1780 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1781 'UPDATE_DIMENSION', 'A', 'V') then
1782 CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_DIMENSION_POST
1783 (p_original_name => p_original_name,
1784 p_new_name => p_new_name,
1785 p_description => p_description,
1786 p_dim_unit_code => p_dim_unit_code,
1787 p_tiers_tbl => p_tiers_tbl,
1788 p_object_version_number => p_object_version_number,
1789 x_return_status => x_return_status,
1790 x_msg_count => x_msg_count,
1791 x_msg_data => x_msg_data);
1792
1796 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1793 check_ret_sts(x_return_status);
1794 end if;
1795
1797 'UPDATE_DIMENSION', 'A', 'C') then
1798 CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_DIMENSION_POST
1799 (p_original_name => p_original_name,
1800 p_new_name => p_new_name,
1801 p_description => p_description,
1802 p_dim_unit_code => p_dim_unit_code,
1803 p_tiers_tbl => p_tiers_tbl,
1804 p_object_version_number => p_object_version_number,
1805 x_return_status => x_return_status,
1806 x_msg_count => x_msg_count,
1807 x_msg_data => x_msg_data);
1808
1809 check_ret_sts(x_return_status);
1810 end if;
1811
1812 -- End of API body.
1813
1814 -- Standard check of p_commit.
1815 IF FND_API.To_Boolean( p_commit ) THEN
1816 COMMIT WORK;
1817 END IF;
1818 -- Standard call to get message count and if count is 1, get message info.
1819 FND_MSG_PUB.count_and_get
1820 (p_count => x_msg_count ,
1821 p_data => x_msg_data ,
1822 p_encoded => FND_API.G_FALSE );
1823 EXCEPTION
1824 WHEN FND_API.G_EXC_ERROR THEN
1825 ROLLBACK TO Update_Dimension;
1826 x_return_status := FND_API.G_RET_STS_ERROR ;
1827 FND_MSG_PUB.count_and_get
1828 (p_count => x_msg_count ,
1829 p_data => x_msg_data ,
1830 p_encoded => FND_API.G_FALSE );
1831 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1832 ROLLBACK TO Update_Dimension;
1833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1834 FND_MSG_PUB.count_and_get
1835 (p_count => x_msg_count ,
1836 p_data => x_msg_data ,
1837 p_encoded => FND_API.G_FALSE );
1838 WHEN OTHERS THEN
1839 ROLLBACK TO Update_Dimension;
1840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1841 IF FND_MSG_PUB.check_msg_level
1842 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1843 THEN
1844 FND_MSG_PUB.add_exc_msg
1845 (G_PKG_NAME ,
1846 l_api_name );
1847 END IF;
1848 FND_MSG_PUB.count_and_get
1849 (p_count => x_msg_count ,
1850 p_data => x_msg_data ,
1851 p_encoded => FND_API.G_FALSE );
1852 END Update_Dimension;
1853
1854 PROCEDURE Delete_Dimension
1855 (p_api_version IN NUMBER,
1856 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1857 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1858 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1859 p_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
1860 -- Start - R12 MOAC Changes
1861 p_object_version_number IN CN_RATE_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE, -- new
1862 -- End - R12 MOAC Changes
1863 x_return_status OUT NOCOPY VARCHAR2,
1864 x_msg_count OUT NOCOPY NUMBER,
1865 x_msg_data OUT NOCOPY VARCHAR2) IS
1866
1867 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Dimension';
1868 l_api_version CONSTANT NUMBER := 1.0;
1869 l_rate_dimension_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE;
1870
1871 BEGIN
1872 -- Standard Start of API savepoint
1873 SAVEPOINT Delete_Dimension;
1874 -- Standard call to check for call compatibility.
1875 IF NOT FND_API.Compatible_API_Call
1876 (l_api_version ,
1877 p_api_version ,
1878 l_api_name ,
1879 G_PKG_NAME )
1880 THEN
1881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1882 END IF;
1883 -- Initialize message list if p_init_msg_list is set to TRUE.
1884 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1885 FND_MSG_PUB.initialize;
1886 END IF;
1887 -- Initialize API return status to success
1888 x_return_status := FND_API.G_RET_STS_SUCCESS;
1889
1890 -- API body
1891
1892 l_rate_dimension_id := get_rate_dimension_id(p_name);
1893
1894 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1895 'DELETE_DIMENSION', 'B', 'C') then
1896 CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_DIMENSION_PRE
1897 (p_name => p_name,
1898 x_return_status => x_return_status,
1899 x_msg_count => x_msg_count,
1900 x_msg_data => x_msg_data);
1901
1902 check_ret_sts(x_return_status);
1903 end if;
1904
1905 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1906 'DELETE_DIMENSION', 'B', 'V') then
1907 CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_DIMENSION_PRE
1908 (p_name => p_name,
1909 x_return_status => x_return_status,
1910 x_msg_count => x_msg_count,
1911 x_msg_data => x_msg_data);
1912
1913 check_ret_sts(x_return_status);
1914 end if;
1915
1916 CN_RATE_DIMENSIONS_PVT.Delete_Dimension
1917 (p_api_version => p_api_version,
1918 p_init_msg_list => p_init_msg_list,
1919 p_commit => p_commit,
1923 p_object_version_number => p_object_version_number,
1920 p_validation_level => p_validation_level,
1921 p_rate_dimension_id => l_rate_dimension_id,
1922 -- Start - MOAC Change
1924 -- End - MOAC Change
1925 x_return_status => x_return_status,
1926 x_msg_count => x_msg_count,
1927 x_msg_data => x_msg_data);
1928
1929 check_ret_sts(x_return_status);
1930
1931 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1932 'DELETE_DIMENSION', 'A', 'V') then
1933 CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_DIMENSION_POST
1934 (p_name => p_name,
1935 x_return_status => x_return_status,
1936 x_msg_count => x_msg_count,
1937 x_msg_data => x_msg_data);
1938
1939 check_ret_sts(x_return_status);
1940 end if;
1941
1942 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
1943 'DELETE_DIMENSION', 'A', 'C') then
1944 CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_DIMENSION_POST
1945 (p_name => p_name,
1946 x_return_status => x_return_status,
1947 x_msg_count => x_msg_count,
1948 x_msg_data => x_msg_data);
1949
1950 check_ret_sts(x_return_status);
1951 end if;
1952
1953 -- End of API body.
1954
1955 -- Standard check of p_commit.
1956 IF FND_API.To_Boolean( p_commit ) THEN
1957 COMMIT WORK;
1958 END IF;
1959 -- Standard call to get message count and if count is 1, get message info.
1960 FND_MSG_PUB.count_and_get
1961 (p_count => x_msg_count ,
1962 p_data => x_msg_data ,
1963 p_encoded => FND_API.G_FALSE );
1964 EXCEPTION
1965 WHEN FND_API.G_EXC_ERROR THEN
1966 ROLLBACK TO Delete_Dimension;
1967 x_return_status := FND_API.G_RET_STS_ERROR ;
1968 FND_MSG_PUB.count_and_get
1969 (p_count => x_msg_count ,
1970 p_data => x_msg_data ,
1971 p_encoded => FND_API.G_FALSE );
1972 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1973 ROLLBACK TO Delete_Dimension;
1974 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1975 FND_MSG_PUB.count_and_get
1976 (p_count => x_msg_count ,
1977 p_data => x_msg_data ,
1978 p_encoded => FND_API.G_FALSE );
1979 WHEN OTHERS THEN
1980 ROLLBACK TO Delete_Dimension;
1981 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1982 IF FND_MSG_PUB.check_msg_level
1983 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1984 THEN
1985 FND_MSG_PUB.add_exc_msg
1986 (G_PKG_NAME ,
1987 l_api_name );
1988 END IF;
1989 FND_MSG_PUB.count_and_get
1990 (p_count => x_msg_count ,
1991 p_data => x_msg_data ,
1992 p_encoded => FND_API.G_FALSE );
1993 END Delete_Dimension;
1994
1995 PROCEDURE Create_Tier
1996 (p_api_version IN NUMBER,
1997 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1998 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1999 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2000 p_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
2001 p_value1 IN VARCHAR2,
2002 p_value2 IN VARCHAR2,
2003 p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE,
2004 -- Start - MOAC Change
2005 p_org_id IN CN_RATE_TIERS.ORG_ID%TYPE := NULL,
2006 -- End - MOAC Change
2007 x_return_status OUT NOCOPY VARCHAR2,
2008 x_msg_count OUT NOCOPY NUMBER,
2009 x_msg_data OUT NOCOPY VARCHAR2) IS
2010
2011 l_api_name CONSTANT VARCHAR2(30) := 'Create_Tier';
2012 l_api_version CONSTANT NUMBER := 1.0;
2013 l_rate_dimension_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE;
2014 l_rate_dim_tier_id CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE;
2015 l_dim_unit_code CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE;
2016 l_minimum_amount CN_RATE_DIM_TIERS.MINIMUM_AMOUNT%TYPE;
2017 l_maximum_amount CN_RATE_DIM_TIERS.MAXIMUM_AMOUNT%TYPE;
2018 l_min_exp_id CN_RATE_DIM_TIERS.MIN_EXP_ID%TYPE;
2019 l_max_exp_id CN_RATE_DIM_TIERS.MAX_EXP_ID%TYPE;
2020 l_string_value CN_RATE_DIM_TIERS.STRING_VALUE%TYPE;
2021 -- Start - MOAC Change
2022 l_org_id NUMBER;
2023 l_status VARCHAR2(1);
2024 -- End - MOAC Change
2025
2026 BEGIN
2027 -- Standard Start of API savepoint
2028 SAVEPOINT Create_tier;
2029 -- Standard call to check for call compatibility.
2030 IF NOT FND_API.Compatible_API_Call
2031 (l_api_version ,
2032 p_api_version ,
2033 l_api_name ,
2034 G_PKG_NAME )
2035 THEN
2036 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2037 END IF;
2038 -- Initialize message list if p_init_msg_list is set to TRUE.
2039 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2040 FND_MSG_PUB.initialize;
2041 END IF;
2045 -- API body
2042 -- Initialize API return status to success
2043 x_return_status := FND_API.G_RET_STS_SUCCESS;
2044
2046
2047 -- Start - R12 MOAC Changes
2048 l_org_id := p_org_id;
2049 mo_global.validate_orgid_pub_api(org_id => l_org_id,
2050 status => l_status);
2051 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2052 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2053 'cn.plsql.cn_multi_rate_schedules_pub.create_tier.org_validate',
2054 'Validated org_id = ' || l_org_id || ' status = '||l_status);
2055 end if;
2056 -- End - R12 MOAC Changes
2057
2058 l_rate_dimension_id := get_rate_dimension_id(p_dimension_name);
2059
2060 -- get dim_unit_code from the dimension
2061 select dim_unit_code into l_dim_unit_code from cn_rate_dimensions
2062 where rate_dimension_id = l_rate_dimension_id;
2063
2064 translate_values(l_dim_unit_code, p_value1, p_value2, l_minimum_amount, l_maximum_amount,
2065 l_min_exp_id, l_max_exp_id, l_string_value);
2066
2067 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
2068 'CREATE_TIER', 'B', 'C') then
2069 CN_MULTI_RATE_SCHEDULES_CUHK.CREATE_TIER_PRE
2070 (p_dimension_name => p_dimension_name,
2071 p_value1 => p_value1,
2072 p_value2 => p_value2,
2073 p_tier_sequence => p_tier_sequence,
2074 x_return_status => x_return_status,
2075 x_msg_count => x_msg_count,
2076 x_msg_data => x_msg_data);
2077
2078 check_ret_sts(x_return_status);
2079 end if;
2080
2081 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
2082 'CREATE_TIER', 'B', 'V') then
2083 CN_MULTI_RATE_SCHEDULES_VUHK.CREATE_TIER_PRE
2084 (p_dimension_name => p_dimension_name,
2085 p_value1 => p_value1,
2086 p_value2 => p_value2,
2087 p_tier_sequence => p_tier_sequence,
2088 x_return_status => x_return_status,
2089 x_msg_count => x_msg_count,
2090 x_msg_data => x_msg_data);
2091
2092 check_ret_sts(x_return_status);
2093 end if;
2094
2095 CN_RATE_DIMENSIONS_PVT.create_tier
2096 (p_api_version => p_api_version,
2097 p_init_msg_list => p_init_msg_list,
2098 p_commit => p_commit,
2099 p_validation_level => p_validation_level,
2100 p_rate_dimension_id => l_rate_dimension_id,
2101 p_dim_unit_code => l_dim_unit_code,
2102 p_minimum_amount => l_minimum_amount,
2103 p_maximum_amount => l_maximum_amount,
2104 p_min_exp_id => l_min_exp_id,
2105 p_max_exp_id => l_max_exp_id,
2106 p_string_value => l_string_value,
2107 p_tier_sequence => p_tier_sequence,
2108 -- Start - MOAC Change
2109 p_org_id => p_org_id,
2110 -- End - MOAC Change
2111 x_rate_dim_tier_id => l_rate_dim_tier_id,
2112 x_return_status => x_return_status,
2113 x_msg_count => x_msg_count,
2114 x_msg_data => x_msg_data);
2115
2116 check_ret_sts(x_return_status);
2117
2118 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
2119 'CREATE_TIER', 'A', 'V') then
2120 CN_MULTI_RATE_SCHEDULES_VUHK.CREATE_TIER_POST
2121 (p_dimension_name => p_dimension_name,
2122 p_value1 => p_value1,
2123 p_value2 => p_value2,
2124 p_tier_sequence => p_tier_sequence,
2125 x_return_status => x_return_status,
2126 x_msg_count => x_msg_count,
2127 x_msg_data => x_msg_data);
2128
2129 check_ret_sts(x_return_status);
2130 end if;
2131
2132 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
2133 'CREATE_TIER', 'A', 'C') then
2134 CN_MULTI_RATE_SCHEDULES_CUHK.CREATE_TIER_POST
2135 (p_dimension_name => p_dimension_name,
2136 p_value1 => p_value1,
2137 p_value2 => p_value2,
2138 p_tier_sequence => p_tier_sequence,
2139 x_return_status => x_return_status,
2140 x_msg_count => x_msg_count,
2141 x_msg_data => x_msg_data);
2142
2143 check_ret_sts(x_return_status);
2144 end if;
2145
2146 -- End of API body.
2147
2148 -- Standard check of p_commit.
2149 IF FND_API.To_Boolean( p_commit ) THEN
2150 COMMIT WORK;
2151 END IF;
2152 -- Standard call to get message count and if count is 1, get message info.
2153 FND_MSG_PUB.Count_And_Get
2154 (p_count => x_msg_count ,
2155 p_data => x_msg_data ,
2156 p_encoded => FND_API.G_FALSE );
2157 EXCEPTION
2158 WHEN FND_API.G_EXC_ERROR THEN
2159 ROLLBACK TO Create_tier;
2160 x_return_status := FND_API.G_RET_STS_ERROR ;
2161 FND_MSG_PUB.count_and_get
2162 (p_count => x_msg_count ,
2163 p_data => x_msg_data ,
2164 p_encoded => FND_API.G_FALSE );
2168 FND_MSG_PUB.count_and_get
2165 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2166 ROLLBACK TO Create_tier;
2167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2169 (p_count => x_msg_count ,
2170 p_data => x_msg_data ,
2171 p_encoded => FND_API.G_FALSE );
2172 WHEN OTHERS THEN
2173 ROLLBACK TO Create_tier;
2174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2175 IF FND_MSG_PUB.check_msg_level
2176 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2177 THEN
2178 FND_MSG_PUB.add_exc_msg
2179 (G_PKG_NAME ,
2180 l_api_name );
2181 END IF;
2182 FND_MSG_PUB.count_and_get
2183 (p_count => x_msg_count ,
2184 p_data => x_msg_data ,
2185 p_encoded => FND_API.G_FALSE );
2186 END Create_tier;
2187
2188 PROCEDURE Update_Tier
2189 (p_api_version IN NUMBER,
2190 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2191 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2192 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2193 p_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
2194 p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE,
2195 p_value1 IN VARCHAR2,
2196 p_value2 IN VARCHAR2,
2197 p_object_version_number IN OUT NOCOPY CN_RATE_DIM_TIERS.OBJECT_VERSION_NUMBER%TYPE,
2198 -- Start - MOAC Change
2199 p_org_id IN CN_RATE_TIERS.ORG_ID%TYPE,
2200 -- End - MOAC Change
2201 x_return_status OUT NOCOPY VARCHAR2,
2202 x_msg_count OUT NOCOPY NUMBER,
2203 x_msg_data OUT NOCOPY VARCHAR2) IS
2204
2205 l_api_name CONSTANT VARCHAR2(30) := 'Update_Tier';
2206 l_api_version CONSTANT NUMBER := 1.0;
2207 l_rate_dimension_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE;
2208 l_rate_dim_tier_id CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE;
2209 l_dim_unit_code CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE;
2210 l_minimum_amount CN_RATE_DIM_TIERS.MINIMUM_AMOUNT%TYPE;
2211 l_maximum_amount CN_RATE_DIM_TIERS.MAXIMUM_AMOUNT%TYPE;
2212 l_min_exp_id CN_RATE_DIM_TIERS.MIN_EXP_ID%TYPE;
2213 l_max_exp_id CN_RATE_DIM_TIERS.MAX_EXP_ID%TYPE;
2214 l_string_value CN_RATE_DIM_TIERS.STRING_VALUE%TYPE;
2215 -- Start - MOAC Change
2216 l_org_id NUMBER;
2217 -- End - MOAC Change
2218 BEGIN
2219 -- Standard Start of API savepoint
2220 SAVEPOINT Update_tier;
2221 -- Standard call to check for call compatibility.
2222 IF NOT FND_API.Compatible_API_Call
2223 (l_api_version ,
2224 p_api_version ,
2225 l_api_name ,
2226 G_PKG_NAME )
2227 THEN
2228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2229 END IF;
2230 -- Initialize message list if p_init_msg_list is set to TRUE.
2231 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2232 FND_MSG_PUB.initialize;
2233 END IF;
2234 -- Initialize API return status to success
2235 x_return_status := FND_API.G_RET_STS_SUCCESS;
2236
2237 -- API body
2238
2239 l_rate_dimension_id := get_rate_dimension_id(p_dimension_name);
2240 l_rate_dim_tier_id := get_rate_dim_tier_id(l_rate_dimension_id, p_tier_sequence);
2241
2242 -- Start - MOAC Change
2243 SELECT org_id INTO l_org_id
2244 FROM cn_rate_dim_tiers
2245 WHERE rate_dim_tier_id = l_rate_dim_tier_id;
2246
2247 IF (l_org_id <> p_org_id)
2248 THEN
2249 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
2250 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2251 THEN
2252 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
2253 'cn.plsql.cn_multi_rate_schedule_pub.update_tier.error',
2254 true);
2255 END IF;
2256
2257 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2258 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
2259 FND_MSG_PUB.Add;
2260 END IF;
2261
2262 RAISE FND_API.G_EXC_ERROR ;
2263 END IF;
2264 -- End - MOAC Change
2265
2266 -- get dim_unit_code from the dimension
2267 select dim_unit_code into l_dim_unit_code from cn_rate_dimensions
2268 where rate_dimension_id = l_rate_dimension_id;
2269
2270 translate_values(l_dim_unit_code, p_value1, p_value2, l_minimum_amount, l_maximum_amount,
2271 l_min_exp_id, l_max_exp_id, l_string_value);
2272
2273 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
2274 'UPDATE_TIER', 'B', 'C') then
2275 CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_TIER_PRE
2276 (p_dimension_name => p_dimension_name,
2277 p_tier_sequence => p_tier_sequence,
2278 p_value1 => p_value1,
2279 p_value2 => p_value2,
2280 p_object_version_number => p_object_version_number,
2281 x_return_status => x_return_status,
2282 x_msg_count => x_msg_count,
2283 x_msg_data => x_msg_data);
2284
2285 check_ret_sts(x_return_status);
2286 end if;
2287
2288 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
2289 'UPDATE_TIER', 'B', 'V') then
2290 CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_TIER_PRE
2291 (p_dimension_name => p_dimension_name,
2292 p_tier_sequence => p_tier_sequence,
2293 p_value1 => p_value1,
2294 p_value2 => p_value2,
2295 p_object_version_number => p_object_version_number,
2296 x_return_status => x_return_status,
2297 x_msg_count => x_msg_count,
2298 x_msg_data => x_msg_data);
2299
2300 check_ret_sts(x_return_status);
2301 end if;
2302
2303 CN_RATE_DIMENSIONS_PVT.update_tier
2304 (p_api_version => p_api_version,
2305 p_init_msg_list => p_init_msg_list,
2306 p_commit => p_commit,
2307 p_validation_level => p_validation_level,
2308 p_rate_dim_tier_id => l_rate_dim_tier_id,
2309 p_rate_dimension_id => l_rate_dimension_id,
2310 p_dim_unit_code => l_dim_unit_code,
2311 p_minimum_amount => l_minimum_amount,
2312 p_maximum_amount => l_maximum_amount,
2313 p_min_exp_id => l_min_exp_id,
2314 p_max_exp_id => l_max_exp_id,
2315 p_string_value => l_string_value,
2316 p_tier_sequence => p_tier_sequence,
2317 p_object_version_number => p_object_version_number,
2318 x_return_status => x_return_status,
2319 x_msg_count => x_msg_count,
2320 x_msg_data => x_msg_data);
2321
2322 check_ret_sts(x_return_status);
2323
2324 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
2325 'UPDATE_TIER', 'A', 'V') then
2326 CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_TIER_POST
2327 (p_dimension_name => p_dimension_name,
2328 p_tier_sequence => p_tier_sequence,
2329 p_value1 => p_value1,
2330 p_value2 => p_value2,
2331 p_object_version_number => p_object_version_number,
2332 x_return_status => x_return_status,
2333 x_msg_count => x_msg_count,
2334 x_msg_data => x_msg_data);
2335
2336 check_ret_sts(x_return_status);
2337 end if;
2338
2339 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
2340 'UPDATE_TIER', 'A', 'C') then
2341 CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_TIER_POST
2342 (p_dimension_name => p_dimension_name,
2343 p_tier_sequence => p_tier_sequence,
2344 p_value1 => p_value1,
2345 p_value2 => p_value2,
2346 p_object_version_number => p_object_version_number,
2347 x_return_status => x_return_status,
2348 x_msg_count => x_msg_count,
2349 x_msg_data => x_msg_data);
2350
2351 check_ret_sts(x_return_status);
2352 end if;
2353
2354 -- End of API body.
2355
2356 -- Standard check of p_commit.
2357 IF FND_API.To_Boolean( p_commit ) THEN
2358 COMMIT WORK;
2359 END IF;
2360 -- Standard call to get message count and if count is 1, get message info.
2361 FND_MSG_PUB.Count_And_Get
2362 (p_count => x_msg_count ,
2363 p_data => x_msg_data ,
2364 p_encoded => FND_API.G_FALSE );
2365 EXCEPTION
2366 WHEN FND_API.G_EXC_ERROR THEN
2367 ROLLBACK TO Update_tier;
2368 x_return_status := FND_API.G_RET_STS_ERROR ;
2369 FND_MSG_PUB.count_and_get
2370 (p_count => x_msg_count ,
2371 p_data => x_msg_data ,
2372 p_encoded => FND_API.G_FALSE );
2373 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2374 ROLLBACK TO Update_tier;
2375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2376 FND_MSG_PUB.count_and_get
2377 (p_count => x_msg_count ,
2378 p_data => x_msg_data ,
2379 p_encoded => FND_API.G_FALSE );
2380 WHEN OTHERS THEN
2381 ROLLBACK TO Update_tier;
2382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2383 IF FND_MSG_PUB.check_msg_level
2384 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2385 THEN
2386 FND_MSG_PUB.add_exc_msg
2387 (G_PKG_NAME ,
2388 l_api_name );
2389 END IF;
2390 FND_MSG_PUB.count_and_get
2391 (p_count => x_msg_count ,
2392 p_data => x_msg_data ,
2393 p_encoded => FND_API.G_FALSE );
2394 END Update_tier;
2395
2396 PROCEDURE Delete_Tier
2397 (p_api_version IN NUMBER,
2398 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2399 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2400 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2401 p_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
2402 p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE,
2403 -- Start - R12 MOAC Changes
2404 p_object_version_number IN CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, -- new
2405 -- End - R12 MOAC Changes
2406 x_return_status OUT NOCOPY VARCHAR2,
2407 x_msg_count OUT NOCOPY NUMBER,
2408 x_msg_data OUT NOCOPY VARCHAR2) IS
2409
2410 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Tier';
2414 BEGIN
2411 l_api_version CONSTANT NUMBER := 1.0;
2412 l_rate_dimension_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE;
2413 l_rate_dim_tier_id CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE;
2415 -- Standard Start of API savepoint
2416 SAVEPOINT Delete_Tier;
2417 -- Standard call to check for call compatibility.
2418 IF NOT FND_API.Compatible_API_Call
2419 (l_api_version ,
2420 p_api_version ,
2421 l_api_name ,
2422 G_PKG_NAME )
2423 THEN
2424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2425 END IF;
2426 -- Initialize message list if p_init_msg_list is set to TRUE.
2427 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2428 FND_MSG_PUB.initialize;
2429 END IF;
2430 -- Initialize API return status to success
2431 x_return_status := FND_API.G_RET_STS_SUCCESS;
2432
2433 -- API body
2434
2435 l_rate_dimension_id := get_rate_dimension_id(p_dimension_name);
2436 l_rate_dim_tier_id := get_rate_dim_tier_id(l_rate_dimension_id, p_tier_sequence);
2437
2438 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
2439 'DELETE_TIER', 'B', 'C') then
2440 CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_TIER_PRE
2441 (p_dimension_name => p_dimension_name,
2442 p_tier_sequence => p_tier_sequence,
2443 x_return_status => x_return_status,
2444 x_msg_count => x_msg_count,
2445 x_msg_data => x_msg_data);
2446
2447 check_ret_sts(x_return_status);
2448 end if;
2449
2450 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
2451 'DELETE_TIER', 'B', 'V') then
2452 CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_TIER_PRE
2453 (p_dimension_name => p_dimension_name,
2454 p_tier_sequence => p_tier_sequence,
2455 x_return_status => x_return_status,
2456 x_msg_count => x_msg_count,
2457 x_msg_data => x_msg_data);
2458
2459 check_ret_sts(x_return_status);
2460 end if;
2461
2462 CN_RATE_DIMENSIONS_PVT.delete_tier
2463 (p_api_version => p_api_version,
2464 p_init_msg_list => p_init_msg_list,
2465 p_commit => p_commit,
2466 p_validation_level => p_validation_level,
2467 p_rate_dim_tier_id => l_rate_dim_tier_id,
2468 x_return_status => x_return_status,
2469 x_msg_count => x_msg_count,
2470 x_msg_data => x_msg_data);
2471
2472 check_ret_sts(x_return_status);
2473
2474 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
2475 'DELETE_TIER', 'A', 'V') then
2476 CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_TIER_POST
2477 (p_dimension_name => p_dimension_name,
2478 p_tier_sequence => p_tier_sequence,
2479 x_return_status => x_return_status,
2480 x_msg_count => x_msg_count,
2481 x_msg_data => x_msg_data);
2482
2483 check_ret_sts(x_return_status);
2484 end if;
2485
2486 if JTF_USR_HKS.Ok_to_Execute('CN_MULTI_RATE_SCHEDULES_PUB',
2487 'DELETE_TIER', 'A', 'C') then
2488 CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_TIER_POST
2489 (p_dimension_name => p_dimension_name,
2490 p_tier_sequence => p_tier_sequence,
2491 x_return_status => x_return_status,
2492 x_msg_count => x_msg_count,
2493 x_msg_data => x_msg_data);
2494
2495 check_ret_sts(x_return_status);
2496 end if;
2497
2498 -- End of API body.
2499
2500 -- Standard check of p_commit.
2501 IF FND_API.To_Boolean( p_commit ) THEN
2502 COMMIT WORK;
2503 END IF;
2504 -- Standard call to get message count and if count is 1, get message info.
2505 FND_MSG_PUB.Count_And_Get
2506 (p_count => x_msg_count ,
2507 p_data => x_msg_data ,
2508 p_encoded => FND_API.G_FALSE );
2509 EXCEPTION
2510 WHEN FND_API.G_EXC_ERROR THEN
2511 ROLLBACK TO Delete_Tier;
2512 x_return_status := FND_API.G_RET_STS_ERROR ;
2513 FND_MSG_PUB.count_and_get
2514 (p_count => x_msg_count ,
2515 p_data => x_msg_data ,
2516 p_encoded => FND_API.G_FALSE );
2517 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2518 ROLLBACK TO Delete_Tier;
2519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2520 FND_MSG_PUB.count_and_get
2521 (p_count => x_msg_count ,
2522 p_data => x_msg_data ,
2523 p_encoded => FND_API.G_FALSE );
2524 WHEN OTHERS THEN
2525 ROLLBACK TO Delete_Tier;
2526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2527 IF FND_MSG_PUB.check_msg_level
2528 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2529 THEN
2530 FND_MSG_PUB.add_exc_msg
2531 (G_PKG_NAME ,
2532 l_api_name );
2533 END IF;
2534 FND_MSG_PUB.count_and_get
2535 (p_count => x_msg_count ,
2536 p_data => x_msg_data ,
2537 p_encoded => FND_API.G_FALSE );
2538 END Delete_Tier;
2539
2540 END CN_MULTI_RATE_SCHEDULES_PUB;