[Home] [Help]
PACKAGE BODY: APPS.CN_MULTI_RATE_SCHEDULES_PVT
Source
1 PACKAGE BODY CN_MULTI_RATE_SCHEDULES_PVT AS
2 /*$Header: cnvrschb.pls 120.31 2007/10/25 19:11:41 jxsingh ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) :='CN_MULTI_RATE_SCHEDULES_PVT';
5
6 -- validate schedule name and commission_unit_code
7 PROCEDURE validate_schedule
8 (p_rate_schedule_id IN CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE := NULL,
9 p_name IN CN_RATE_SCHEDULES.NAME%TYPE,
10 p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE,
11 p_number_dim IN CN_RATE_SCHEDULES.NUMBER_DIM%TYPE,
12 p_dims_tbl IN dims_tbl_type := g_miss_dims_tbl,
13 --R12 MOAC Changes--Start
14 p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE --new
15 --R12 MOAC Changes--End
16 )
17 IS
18 l_prompt cn_lookups.meaning%TYPE;
19 l_dummy NUMBER;
20
21 CURSOR name_exists IS
22 SELECT 1
23 FROM cn_rate_schedules
24 WHERE name = p_name
25 AND (p_rate_schedule_id IS NULL OR p_rate_schedule_id <> rate_schedule_id)
26 --R12 MOAC Changes--Start
27 AND org_id = p_org_id;
28 --R12 MOAC Changes--End
29 BEGIN
30 IF (p_name IS NULL) THEN
31 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
32 l_prompt := cn_api.get_lkup_meaning('RATE_TABLE_NAME', 'CN_PROMPTS');
33 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
34 fnd_message.set_token('OBJ_NAME', l_prompt);
35 fnd_msg_pub.ADD;
36 END IF;
37 RAISE fnd_api.g_exc_error;
38 END IF;
39
40 OPEN name_exists;
41 FETCH name_exists INTO l_dummy;
42 CLOSE name_exists;
43
44 IF (l_dummy = 1) THEN
45 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
46 fnd_message.set_name('CN', 'CN_NAME_NOT_UNIQUE');
47 fnd_msg_pub.ADD;
48 END IF;
49 RAISE fnd_api.g_exc_error;
50 END IF;
51
52 -- validate commission_unit_code
53 IF (p_commission_unit_code NOT IN ('AMOUNT', 'PERCENT')) THEN
54 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
55 fnd_message.set_name('CN', 'CN_INVALID_CUC');
56 fnd_msg_pub.ADD;
57 END IF;
58 RAISE fnd_api.g_exc_error;
59 END IF;
60
61 -- if p_dims_tbl is not empty, then p_number_dim should be equal to the number of
62 -- records in p_dims_tbl. also the sequence numbers should be unique
63 IF (p_number_dim <> p_dims_tbl.count) THEN
64 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
65 fnd_message.set_name('CN', 'CN_X_NUMBER_DIM');
66 fnd_msg_pub.ADD;
67 END IF;
68 RAISE fnd_api.g_exc_error;
69 END IF;
70
71 IF (p_number_dim >= 2) THEN
72 for i in p_dims_tbl.first..p_dims_tbl.last-1 loop
73 for j in i+1..p_dims_tbl.last loop
74 -- for i in 1..p_number_dim-1 loop
75 -- for j in i+1..p_number_dim loop
76 if p_dims_tbl(i).rate_dim_sequence = p_dims_tbl(j).rate_dim_sequence then
77 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
78 fnd_message.set_name('CN', 'CN_SEQUENCE_NOT_UNIQUE');
79 fnd_msg_pub.ADD;
80 END IF;
81 RAISE fnd_api.g_exc_error;
82 END IF;
83 if p_dims_tbl(i).rate_dimension_id = p_dims_tbl(j).rate_dimension_id then
84 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
85 fnd_message.set_name('CN', 'CN_DUPLICATE_DIM_ASSIGN');
86 fnd_msg_pub.ADD;
87 END IF;
88 RAISE fnd_api.g_exc_error;
89 END IF;
90 end loop;
91 end loop;
92 END IF;
93
94 END validate_schedule;
95
96 PROCEDURE usage_check(p_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE,
97 x_usage_code OUT NOCOPY VARCHAR2)
98 IS
99 CURSOR usage_check IS
100 SELECT 'USED'
101 FROM dual
102 WHERE (exists (SELECT 1
103 FROM cn_rt_formula_asgns
104 WHERE rate_schedule_id = p_rate_schedule_id))
105 OR (exists (SELECT 1
106 FROM cn_rt_quota_asgns
107 WHERE rate_schedule_id = p_rate_schedule_id));
108 BEGIN
109 OPEN usage_check;
110 FETCH usage_check INTO x_usage_code;
111 CLOSE usage_check;
112
113 IF (x_usage_code IS NULL) THEN
114 x_usage_code := 'NOT_USED';
115 END IF;
116 END usage_check;
117
118
119 PROCEDURE create_note_bus_event
120 (p_srp_quota_assign_id IN NUMBER,
121 p_rt_quota_asgn_id IN NUMBER,
122 p_rate_sequence IN NUMBER,
123 p_old_amt IN NUMBER,
124 p_new_amt IN NUMBER,
125 p_key IN VARCHAR2,
126 p_rate_schedule_id IN VARCHAR2 DEFAULT 0) IS
127
128 x_note_id NUMBER;
129 x_msg_count NUMBER;
130 x_msg_data VARCHAR2(240);
131 x_return_status VARCHAR2(1);
132 l_note_msg VARCHAR2(2000);
133
134 l_seq number := p_rate_sequence - 1;
135 l_tier_seq number;
136
137 l_tiers varchar2(500);
138 l_tier varchar2(240);
139 l_pct number := 1;
140
141 l_key VARCHAR2(80);
142 l_event_name VARCHAR2(80);
143 l_list wf_parameter_list_t;
144
145 cursor get_rate_info is
146 select s.name, rqa.start_date, rqa.end_date, s.commission_unit_code, s.rate_schedule_id
147 from cn_rate_schedules s, cn_rt_quota_asgns rqa
148 where s.rate_schedule_id = rqa.rate_schedule_id
149 and rqa.rt_quota_asgn_id = p_rt_quota_asgn_id;
150 l_info_rec get_rate_info%rowtype;
151
152 cursor get_rate_info_for_mul_rate is
153 select s.name,null,null,s.commission_unit_code, s.rate_schedule_id
154 from cn_rate_schedules s
155 where s.rate_schedule_id =p_rate_schedule_id;
156
157 cursor get_rate_dims(p_rate_schedule_id NUMBER) is
158 select d.name, d.rate_dimension_id, d.number_tier
159 from cn_rate_sch_dims rsd, cn_rate_dimensions d
160 where rate_schedule_id = p_rate_schedule_id
161 and rsd.rate_dimension_id = d.rate_dimension_id
162 order by rsd.rate_dim_sequence desc;
163
164 cursor get_tier(p_rate_dimension_id number, p_sequence number) is
165 select decode(rd.dim_unit_code,
166 'AMOUNT', rdt.minimum_amount || ' - ' || rdt.maximum_amount,
167 'PERCENT', rdt.minimum_amount * 100 || '% - ' || rdt.maximum_amount * 100 || '%',
168 'STRING', rdt.string_value,
169 'EXPRESSION', e1.name || ' - ' || e2.name) tier
170 from cn_rate_dim_tiers_all rdt, cn_rate_dimensions_all rd, cn_calc_sql_exps e1, cn_calc_sql_exps e2
171 where rdt.rate_dimension_id = p_rate_dimension_id
172 and rdt.tier_sequence = p_sequence
173 and rd.rate_dimension_id = rdt.rate_dimension_id
174 and rdt.min_exp_id = e1.calc_sql_exp_id(+)
175 and rdt.max_exp_id = e2.calc_sql_exp_id(+);
176
177 BEGIN
178 IF p_old_amt = p_new_amt THEN
179 RETURN; -- no change -> no note
180 END IF;
181
182 if p_rate_schedule_id = 0 then
183 open get_rate_info;
184 fetch get_rate_info into l_info_rec;
185 close get_rate_info;
186 else
187 open get_rate_info_for_mul_rate;
188 fetch get_rate_info_for_mul_rate into l_info_rec;
189 close get_rate_info_for_mul_rate;
190
191 end if ;
192 if l_info_rec.commission_unit_code = 'PERCENT' then
193 l_pct := 100;
194 end if;
195
196 for d in get_rate_dims(l_info_rec.rate_schedule_id) loop
197 l_tier_seq := mod(l_seq, d.number_tier) + 1;
198 l_seq := floor(l_seq / d.number_tier);
199
200 open get_tier(d.rate_dimension_id, l_tier_seq);
201 fetch get_tier into l_tier;
202 close get_tier;
203
204 if l_tiers is not null then
205 l_tiers := ', ' || l_tiers;
206 end if;
207 l_tiers := d.name || ' (' || l_tier || ')' || l_tiers;
208 end loop;
209
210 if p_rate_schedule_id = 0 then
211 fnd_message.set_name('CN', 'CN_SRP_RATE_ASSIGNS_NOTE');
212 fnd_message.set_token('RATE_TABLE', l_info_rec.name);
213 fnd_message.set_token('START_DATE', l_info_rec.start_date);
214 fnd_message.set_token('END_DATE', l_info_rec.end_date);
215 fnd_message.set_token('DIMENSIONS', l_tiers);
216 fnd_message.set_token('OLD', p_old_amt * l_pct);
217 fnd_message.set_token('NEW', p_new_amt * l_pct);
218
219 l_note_msg := fnd_message.get();
220 jtf_notes_pub.create_note
221 ( p_api_version => 1.0,
222 x_return_status => x_return_status,
223 x_msg_count => x_msg_count,
224 x_msg_data => x_msg_data,
225 p_source_object_id => p_srp_quota_assign_id ,--p_srp_quota_assign_id
226 p_source_object_code => 'CN_SRP_QUOTA_ASSIGNS',--CN_SRP_QUOTA_ASSIGNS
227 p_notes => l_note_msg,
228 p_notes_detail => l_note_msg,
229 p_note_type => 'CN_SYSGEN', -- for system generated
230 x_jtf_note_id => x_note_id -- returned
231 );
232
233 else
234 fnd_message.set_name('CN', 'CN_MULTI_RATE_ASSIGNS_NOTE');
235 fnd_message.set_token('RATE_TABLE', l_info_rec.name);
236 fnd_message.set_token('DIMENSIONS', l_tiers);
237 fnd_message.set_token('OLD', p_old_amt * l_pct);
238 fnd_message.set_token('NEW', p_new_amt * l_pct);
239 l_note_msg := fnd_message.get();
240 jtf_notes_pub.create_note
241 ( p_api_version => 1.0,
242 x_return_status => x_return_status,
243 x_msg_count => x_msg_count,
244 x_msg_data => x_msg_data,
245 p_source_object_id => p_rate_schedule_id ,--p_srp_quota_assign_id
246 p_source_object_code => 'CN_RATE_SCHEDULES',--CN_SRP_QUOTA_ASSIGNS
247 p_notes => l_note_msg,
248 p_notes_detail => l_note_msg,
249 p_note_type => 'CN_SYSGEN', -- for system generated
250 x_jtf_note_id => x_note_id -- returned
251 );
252 end if;
253
254 -- create business event also
255 if p_rate_schedule_id = 0 then
256 l_event_name := 'oracle.apps.cn.resource.PlanAssign.UpdateRate';
257 l_key := l_event_name || '-' || p_key;
258
259 wf_event.AddParameterToList('SRP_QUOTA_ASSIGN_ID',p_srp_quota_assign_id,l_list);
260 wf_event.AddParameterToList('RT_QUOTA_ASGN_ID',p_rt_quota_asgn_id,l_list);
261 wf_event.AddParameterToList('RATE_SEQUENCE',p_rate_sequence,l_list);
262 wf_event.AddParameterToList('COMMISSION_AMOUNT',p_new_amt,l_list);
263
264 -- Raise Event
265 wf_event.raise
266 (p_event_name => l_event_name,
267 p_event_key => l_key,
268 p_parameters => l_list);
269
270 l_list.DELETE;
271 end if;
272
273 END create_note_bus_event;
274
275 -- Start of comments
276 -- API name : Create_Schedule
277 -- Type : Private.
278 -- Function :
279 -- Pre-reqs : None.
280 -- Parameters :
281 -- IN : p_api_version IN NUMBER Required
282 -- p_init_msg_list IN VARCHAR2 Optional
283 -- Default = FND_API.G_FALSE
284 -- p_commit IN VARCHAR2 Optional
285 -- Default = FND_API.G_FALSE
286 -- p_validation_level IN NUMBER Optional
287 -- Default = FND_API.G_VALID_LEVEL_FULL
288 -- p_name IN VARCHAR2 Required
289 -- p_commission_unit_code IN VARCHAR2 Required
290 -- p_number_dim IN NUMBER Required
291 -- p_dims_tbl IN dims_tbl_type Optional
292 -- Default = g_miss_dims_tbl
293 -- OUT : x_rate_schedule_id OUT NUMBER
294 -- x_return_status OUT VARCHAR2(1)
295 -- x_msg_count OUT NUMBER
296 -- x_msg_data OUT VARCHAR2(2000)
297 -- Version : Current version 1.0
298 -- Initial version 1.0
299 --
300 -- Notes : Create rate schedule and schedule dimensions
301 -- 1) Validate schedule name (should be unique)
302 -- 2) Validate commission_unit_code (valid values are AMOUNT, PERCENT)
303 -- 3) Validate number_dim which should equal the number of dimensions
304 -- in p_dims_tbl if it is not empty
305 --
306 -- End of comments
307 PROCEDURE Create_Schedule
308 (p_api_version IN NUMBER ,
309 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
310 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
311 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
312 p_name IN CN_RATE_SCHEDULES.NAME%TYPE ,
313 p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE,
314 p_number_dim IN CN_RATE_SCHEDULES.NUMBER_DIM%TYPE, -- not used
315 p_dims_tbl IN dims_tbl_type := g_miss_dims_tbl,
316 --R12 MOAC Changes--Start
317 p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE, --new
318 x_rate_schedule_id IN OUT NOCOPY CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE, --changed
319 --R12 MOAC Changes--End
320 x_return_status OUT NOCOPY VARCHAR2 ,
321 x_msg_count OUT NOCOPY NUMBER ,
322 x_msg_data OUT NOCOPY VARCHAR2 )
323 IS
324 l_api_name CONSTANT VARCHAR2(30) := 'Create_Schedule';
325 l_api_version CONSTANT NUMBER := 1.0;
326 l_number_dim CN_RATE_SCHEDULES.NUMBER_DIM%TYPE := 0;
327 l_temp_id CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE;
328
329 --R12 Notes Hoistory
330 l_rate_sch_name VARCHAR2(80);
331 l_note_msg VARCHAR2(240);
332 l_note_id NUMBER;
333
334 BEGIN
335 -- Standard Start of API savepoint
336 SAVEPOINT Create_Schedule;
337 -- Standard call to check for call compatibility.
338 IF NOT FND_API.Compatible_API_Call
339 (l_api_version ,
340 p_api_version ,
341 l_api_name ,
342 G_PKG_NAME )
343 THEN
344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
345 END IF;
346 -- Initialize message list if p_init_msg_list is set to TRUE.
347 IF FND_API.to_Boolean( p_init_msg_list ) THEN
348 FND_MSG_PUB.initialize;
349 END IF;
350 -- Initialize API return status to success
351 x_return_status := FND_API.G_RET_STS_SUCCESS;
352
353 -- calculate number of dimensions (p_number_dim not used)
354 -- set number_dim := number of dimensions of p_dims_tbl
355 l_number_dim := p_dims_tbl.count;
356
357 -- API body
358 validate_schedule(p_rate_schedule_id => NULL, -- validation for new record creation
359 p_name => p_name,
360 p_commission_unit_code => p_commission_unit_code,
361 p_number_dim => l_number_dim,
362 p_dims_tbl => p_dims_tbl,
363 --R12 MOAC Changes--Start
364 p_org_id => p_org_id
365 --R12 MOAC Changes--End
366 );
367
368 -- call table handler to create rate schedule record in cn_rate_schedules
369 cn_multi_rate_schedules_pkg.insert_row(x_rate_schedule_id => x_rate_schedule_id,
370 x_name => p_name,
371 x_commission_unit_code => p_commission_unit_code,
372 x_number_dim => l_number_dim,
373 --R12 MOAC Changes--Start
374 x_org_id => p_org_id);
375 --R12 MOAC Changes--End
376
377 -- *********************************************************************
378 -- ************ Start - R12 Notes History ************** ***************
379 -- *********************************************************************
380 select name into l_rate_sch_name
381 from cn_rate_schedules
382 where rate_schedule_id = x_rate_schedule_id;
383
384 fnd_message.set_name('CN', 'CNR12_NOTE_RT_CREATE');
385 fnd_message.set_token('RT_NAME', l_rate_sch_name);
386 l_note_msg := fnd_message.get;
387
388 jtf_notes_pub.create_note
389 (p_api_version => 1.0,
390 x_return_status => x_return_status,
391 x_msg_count => x_msg_count,
392 x_msg_data => x_msg_data,
393 p_source_object_id => x_rate_schedule_id,
394 p_source_object_code => 'CN_RATE_SCHEDULES',
395 p_notes => l_note_msg,
396 p_notes_detail => l_note_msg,
397 p_note_type => 'CN_SYSGEN', -- for system generated
398 x_jtf_note_id => l_note_id -- returned
399 );
400
401 -- *********************************************************************
402 -- ************ End - R12 Notes History ********************************
403 -- *********************************************************************
404
405 -- call table handler to create dimension assignments and populate cn_rate_tiers
406 IF (p_dims_tbl.COUNT > 0) THEN
407 FOR i IN p_dims_tbl.first..p_dims_tbl.last LOOP
408 l_temp_id := NULL;
409 cn_rate_sch_dims_pkg.insert_row(x_rate_sch_dim_id => l_temp_id,
410 x_rate_dimension_id => p_dims_tbl(i).rate_dimension_id,
411 x_rate_schedule_id => x_rate_schedule_id,
412 x_rate_dim_sequence => p_dims_tbl(i).rate_dim_sequence,
413 --R12 MOAC Changes--Start
414 x_org_id => p_org_id);
415 --R12 MOAC Changes--End
416 END LOOP;
417 END IF;
418
419 -- leave table empty and fill in tiers as needed for sparse impl
420 /*
421 -- create records in cn_rate_tiers (product[T_i] tiers for i=1..number of dims)
422 create_rate_tiers(p_rate_schedule_id => x_rate_schedule_id,
423 p_rate_dim_sequence => NULL);
424 */
425
426 -- End of API body.
427
428 -- Standard check of p_commit.
429 IF FND_API.To_Boolean( p_commit ) THEN
430 COMMIT WORK;
431 END IF;
432 -- Standard call to get message count and if count is 1, get message info.
433 FND_MSG_PUB.Count_And_Get
434 (p_count => x_msg_count ,
435 p_data => x_msg_data ,
436 p_encoded => FND_API.G_FALSE );
437 EXCEPTION
438 WHEN FND_API.G_EXC_ERROR THEN
439 ROLLBACK TO Create_Schedule;
440 x_return_status := FND_API.G_RET_STS_ERROR ;
441 FND_MSG_PUB.count_and_get
442 (p_count => x_msg_count ,
443 p_data => x_msg_data ,
444 p_encoded => FND_API.G_FALSE );
445 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
446 ROLLBACK TO Create_Schedule;
447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
448 FND_MSG_PUB.count_and_get
449 (p_count => x_msg_count ,
450 p_data => x_msg_data ,
451 p_encoded => FND_API.G_FALSE );
452 WHEN OTHERS THEN
453 ROLLBACK TO Create_Schedule;
454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
455 IF FND_MSG_PUB.check_msg_level
456 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
457 THEN
458 FND_MSG_PUB.add_exc_msg
459 (G_PKG_NAME ,
460 l_api_name );
461 END IF;
462 FND_MSG_PUB.count_and_get
463 (p_count => x_msg_count ,
464 p_data => x_msg_data ,
465 p_encoded => FND_API.G_FALSE );
466 END Create_Schedule;
467
468
469 -- Start of comments
470 -- API name : Update_Schedule
471 -- Type : Private.
472 -- Function :
473 -- Pre-reqs : None.
474 -- Parameters :
475 -- IN : p_api_version IN NUMBER Required
476 -- p_init_msg_list IN VARCHAR2 Optional
477 -- Default = FND_API.G_FALSE
478 -- p_commit IN VARCHAR2 Optional
479 -- Default = FND_API.G_FALSE
480 -- p_validation_level IN NUMBER Optional
481 -- Default = FND_API.G_VALID_LEVEL_FULL
482 -- p_rate_schedule_id IN NUMBER Required
483 -- p_name IN VARCHAR2 Required
484 -- p_commission_unit_code IN VARCHAR2 Required
485 -- p_number_dim IN NUMBER Required
486 -- p_dims_tbl IN dims_tbl_type Optional
487 -- Default = g_miss_dims_tbl
488 -- OUT : x_return_status OUT VARCHAR2(1)
489 -- x_msg_count OUT NUMBER
490 -- x_msg_data OUT VARCHAR2(2000)
491 -- Version : Current version 1.0
492 -- Initial version 1.0
493 --
494 -- Notes : Update rate schedule and schedule dimensions
495 -- 1) Validate schedule name (should be unique)
496 -- 2) Validate commission_unit_code (valid values are AMOUNT, PERCENT)
497 -- 3) Validate number_dim which should equal the number of dimensions
498 -- in p_dims_tbl if it is not empty
499 -- 4) Insert new dimensions and delete obsolete dimensions
500 -- 5) Update rate tiers also
501 -- 6) If this rate table is used, then update of dimensions and
502 -- commission_unit_code is not allowed
503 --
504 -- End of comments
505 PROCEDURE Update_Schedule
506 (p_api_version IN NUMBER ,
507 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
508 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
509 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
510 p_rate_schedule_id IN CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE,
511 p_name IN CN_RATE_SCHEDULES.NAME%TYPE,
512 p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE,
513 p_number_dim IN CN_RATE_SCHEDULES.NUMBER_DIM%TYPE, -- not used
514 --R12 MOAC Changes--Start
515 p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE, --new
516 p_object_version_number IN OUT NOCOPY CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE, -- Changed
517 --R12 MOAC Changes--End
518 p_dims_tbl IN dims_tbl_type := g_miss_dims_tbl,
519 x_return_status OUT NOCOPY VARCHAR2 ,
520 x_msg_count OUT NOCOPY NUMBER ,
521 x_msg_data OUT NOCOPY VARCHAR2 )
522 IS
523 l_api_name CONSTANT VARCHAR2(30) := 'Update_Schedule';
524 l_api_version CONSTANT NUMBER := 1.0;
525
526 l_temp_id CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE;
527 l_commission_unit_code_old CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE;
528 l_number_dim CN_RATE_SCHEDULES.NUMBER_DIM%TYPE := 0;
529 l_number_dim_old CN_RATE_SCHEDULES.NUMBER_DIM%TYPE := 0;
530 l_delete_flag VARCHAR2(1);
531 l_usage_code VARCHAR2(30);
532 i pls_integer;
533
534 --R12 Notes Hoistory
535 l_rate_sch_old VARCHAR2(80);
536 l_type_old VARCHAR2(30);
537 l_note_msg VARCHAR2(240);
538 l_note_id NUMBER;
539 l_consolidated_note VARCHAR2(2000);
540
541
542 CURSOR schedule_info IS
543 SELECT commission_unit_code, number_dim
544 FROM cn_rate_schedules
545 WHERE rate_schedule_id = p_rate_schedule_id;
546
547 CURSOR db_dim_assignments IS
548 SELECT rate_sch_dim_id
549 FROM cn_rate_sch_dims
550 WHERE rate_schedule_id = p_rate_schedule_id;
551 BEGIN
552 -- Standard Start of API savepoint
553 SAVEPOINT Update_Schedule;
554 -- Standard call to check for call compatibility.
555 IF NOT FND_API.Compatible_API_Call
556 (l_api_version ,
557 p_api_version ,
558 l_api_name ,
559 G_PKG_NAME )
560 THEN
561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
562 END IF;
563 -- Initialize message list if p_init_msg_list is set to TRUE.
564 IF FND_API.to_Boolean( p_init_msg_list ) THEN
565 FND_MSG_PUB.initialize;
566 END IF;
567 -- Initialize API return status to success
568 x_return_status := FND_API.G_RET_STS_SUCCESS;
569
570 -- calculate number of dimensions (p_number_dim not used)
571 -- set number_dim := number of dimensions of p_dims_tbl
572 l_number_dim := p_dims_tbl.count;
573
574 -- API body
575 validate_schedule(p_rate_schedule_id => p_rate_schedule_id,
576 p_name => p_name,
577 p_commission_unit_code => p_commission_unit_code,
578 p_number_dim => l_number_dim,
579 p_dims_tbl => p_dims_tbl,
580 --R12 MOAC Changes--Start
581 p_org_id => p_org_id
582 --R12 MOAC Changes--End
583 );
584
585 OPEN schedule_info;
586 FETCH schedule_info INTO l_commission_unit_code_old, l_number_dim_old;
587 CLOSE schedule_info;
588
589 -- if it is used, then can not update commission_unit_code and number_dim as well
590 -- as dimension assignments
591 usage_check(p_rate_schedule_id => p_rate_schedule_id,
592 x_usage_code => l_usage_code);
593 IF (l_usage_code = 'USED' AND
594 (l_commission_unit_code_old <> p_commission_unit_code OR l_number_dim > 0))
595 THEN
596 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
597 fnd_message.set_name('CN', 'CN_SCHEDULE_IN_USE');
598 fnd_msg_pub.ADD;
599 END IF;
600 RAISE fnd_api.g_exc_error;
601 END IF;
602
603 -- Start - R12 Notes History Query for old Rate Table Name
604 select name into l_rate_sch_old
605 from cn_rate_schedules
606 where rate_schedule_id = p_rate_schedule_id;
607
608 select commission_unit_code into l_type_old
609 from cn_rate_schedules
610 where rate_schedule_id = p_rate_schedule_id;
611 -- End - R12 Notes History Query for old Rate Table Name
612
613 -- lock rate schedule for update or delete
614 cn_multi_rate_schedules_pkg.lock_row
615 (x_rate_schedule_id => p_rate_schedule_id,
616 x_object_version_number => p_object_version_number);
617
618 IF (p_dims_tbl.COUNT > 0) THEN
619 -- we passed in a dimensions table... delete and re-create the table as the
620 -- dimensions have been changed
621
622 -- delete all rate tiers and rate_sch_dims
623 delete from cn_rate_sch_dims where rate_schedule_id = p_rate_schedule_id;
624 delete from cn_rate_tiers where rate_schedule_id = p_rate_schedule_id;
625
626 -- reassign rate schedule dimensions
627 FOR i IN p_dims_tbl.first..p_dims_tbl.last LOOP
628 l_temp_id := NULL;
629 cn_rate_sch_dims_pkg.insert_row
630 (x_rate_sch_dim_id => l_temp_id,
631 x_rate_dimension_id => p_dims_tbl(i).rate_dimension_id,
632 x_rate_schedule_id => p_rate_schedule_id,
633 x_rate_dim_sequence => p_dims_tbl(i).rate_dim_sequence,
634 --R12 MOAC Changes--Start
635 x_org_id => p_org_id
636 --R12 MOAC Changes--End
637 );
638 END LOOP;
639
640 -- rate table being built up again from scratch - purge existing tiers
641 delete from cn_rate_tiers where rate_schedule_id = p_rate_schedule_id;
642 /*
643 -- create records in cn_rate_tiers (product[T_i] tiers for i=1..number of dims)
644 create_rate_tiers(p_rate_schedule_id => p_rate_schedule_id,
645 p_rate_dim_sequence => NULL);
646 */
647 END IF;
648
649 -- get correct # of dims
650 OPEN schedule_info;
651 FETCH schedule_info INTO l_commission_unit_code_old, l_number_dim;
652 CLOSE schedule_info;
653
654 cn_multi_rate_schedules_pkg.update_row
655 (x_rate_schedule_id => p_rate_schedule_id,
656 x_name => p_name,
657 x_commission_unit_code => p_commission_unit_code,
658 x_number_dim => l_number_dim,
659 x_object_version_number => p_object_version_number);
660
661 -- *********************************************************************
662 -- ************ Start - R12 Notes History ************** ***************
663 -- *********************************************************************
664
665 l_consolidated_note := '';
666
667 IF (p_name <> l_rate_sch_old) THEN
668 fnd_message.set_name('CN', 'CNR12_NOTE_RT_NAME_UPDATE');
669 fnd_message.set_token('OLD_RT', l_rate_sch_old);
670 fnd_message.set_token('NEW_RT', p_name);
671 l_note_msg := fnd_message.get;
672 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
673
674 /*
675 jtf_notes_pub.create_note
676 (p_api_version => 1.0,
677 x_return_status => x_return_status,
678 x_msg_count => x_msg_count,
679 x_msg_data => x_msg_data,
680 p_source_object_id => p_rate_schedule_id,
681 p_source_object_code => 'CN_RATE_SCHEDULES',
682 p_notes => l_note_msg,
683 p_notes_detail => l_note_msg,
684 p_note_type => 'CN_SYSGEN', -- for system generated
685 x_jtf_note_id => l_note_id -- returned
686 );
687 */
688 END IF;
689
690 IF (p_commission_unit_code <> l_type_old) THEN
691 fnd_message.set_name('CN', 'CNR12_NOTE_RT_ASGN_TYPE_UPDATE');
692 fnd_message.set_token('OLD_RT_TYPE', l_type_old);
693 fnd_message.set_token('NEW_RT_TYPE', p_commission_unit_code);
694 l_note_msg := fnd_message.get;
695 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
696
697 /*
698 jtf_notes_pub.create_note
699 (p_api_version => 1.0,
700 x_return_status => x_return_status,
701 x_msg_count => x_msg_count,
702 x_msg_data => x_msg_data,
703 p_source_object_id => p_rate_schedule_id,
704 p_source_object_code => 'CN_RATE_SCHEDULES',
705 p_notes => l_note_msg,
706 p_notes_detail => l_note_msg,
707 p_note_type => 'CN_SYSGEN', -- for system generated
708 x_jtf_note_id => l_note_id -- returned
709 );
710 */
711 END IF;
712
713 if LENGTH(l_consolidated_note) > 1 THEN
714 jtf_notes_pub.create_note
715 (p_api_version => 1.0,
716 x_return_status => x_return_status,
717 x_msg_count => x_msg_count,
718 x_msg_data => x_msg_data,
719 p_source_object_id => p_rate_schedule_id,
720 p_source_object_code => 'CN_RATE_SCHEDULES',
721 p_notes => l_consolidated_note,
722 p_notes_detail => l_consolidated_note,
723 p_note_type => 'CN_SYSGEN', -- for system generated
724 x_jtf_note_id => l_note_id -- returned
725 );
726
727 END IF;
728
729 -- *********************************************************************
730 -- ************ End - R12 Notes History ********************************
731 -- *********************************************************************
732
733 -- End of API body.
734
735 -- Standard check of p_commit.
736 IF FND_API.To_Boolean( p_commit ) THEN
737 COMMIT WORK;
738 END IF;
739 -- Standard call to get message count and if count is 1, get message info.
740 FND_MSG_PUB.count_and_get
741 (p_count => x_msg_count ,
742 p_data => x_msg_data ,
743 p_encoded => FND_API.G_FALSE );
744 EXCEPTION
745 WHEN FND_API.G_EXC_ERROR THEN
746 ROLLBACK TO Update_Schedule;
747 x_return_status := FND_API.G_RET_STS_ERROR ;
748 FND_MSG_PUB.count_and_get
749 (p_count => x_msg_count ,
750 p_data => x_msg_data ,
751 p_encoded => FND_API.G_FALSE );
752 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
753 ROLLBACK TO Update_Schedule;
754 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
755 FND_MSG_PUB.count_and_get
756 (p_count => x_msg_count ,
757 p_data => x_msg_data ,
758 p_encoded => FND_API.G_FALSE );
759 WHEN OTHERS THEN
760 ROLLBACK TO Update_Schedule;
761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
762 IF FND_MSG_PUB.check_msg_level
763 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
764 THEN
765 FND_MSG_PUB.add_exc_msg
766 (G_PKG_NAME ,
767 l_api_name );
768 END IF;
769 FND_MSG_PUB.count_and_get
770 (p_count => x_msg_count ,
771 p_data => x_msg_data ,
772 p_encoded => FND_API.G_FALSE );
773 END Update_Schedule;
774
775 -- Start of comments
776 -- API name : Delete_Schedule
777 -- Type : Private.
778 -- Function :
779 -- Pre-reqs : None.
780 -- Parameters :
781 -- IN : p_api_version IN NUMBER Required
782 -- p_init_msg_list IN VARCHAR2 Optional
783 -- Default = FND_API.G_FALSE
784 -- p_commit IN VARCHAR2 Optional
785 -- Default = FND_API.G_FALSE
786 -- p_validation_level IN NUMBER Optional
787 -- Default = FND_API.G_VALID_LEVEL_FULL
788 -- p_rate_schedule_id IN NUMBER Required
789 -- p_name IN VARCHAR2 Required
790 -- OUT : x_return_status OUT VARCHAR2(1)
791 -- x_msg_count OUT NUMBER
792 -- x_msg_data OUT VARCHAR2(2000)
793 -- Version : Current version 1.0
794 -- Initial version 1.0
795 --
796 -- Notes : Delete rate schedule
797 -- 1) If it is used, it can not be deleted
798 -- 2) If it can be deleted, delete corresponding records in
799 -- cn_rate_sch_dims and cn_rate_tiers
800 --
801 -- End of comments
802 PROCEDURE Delete_Schedule
803 (p_api_version IN NUMBER ,
804 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
805 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
806 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
807 p_rate_schedule_id IN CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE,
808 --R12 MOAC Changes--Start
809 p_object_version_number IN CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE, -- new
810 --R12 MOAC Changes--End
811 x_return_status OUT NOCOPY VARCHAR2 ,
812 x_msg_count OUT NOCOPY NUMBER ,
813 x_msg_data OUT NOCOPY VARCHAR2 )
814 IS
815 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Schedule';
816 l_api_version CONSTANT NUMBER := 1.0;
817 l_usage_code VARCHAR2(30);
818
819 --R12 Notes Hoistory
820 l_rate_sch_name VARCHAR2(80);
821 l_org_id Number;
822 l_note_msg VARCHAR2(240);
823 l_note_id NUMBER;
824
825 BEGIN
826 -- Standard Start of API savepoint
827 SAVEPOINT Delete_Schedule;
828 -- Standard call to check for call compatibility.
829 IF NOT FND_API.Compatible_API_Call
830 (l_api_version ,
831 p_api_version ,
832 l_api_name ,
833 G_PKG_NAME )
834 THEN
835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
836 END IF;
837 -- Initialize message list if p_init_msg_list is set to TRUE.
838 IF FND_API.to_Boolean( p_init_msg_list ) THEN
839 FND_MSG_PUB.initialize;
840 END IF;
841 -- Initialize API return status to success
842 x_return_status := FND_API.G_RET_STS_SUCCESS;
843
844 -- API body
845
846 usage_check(p_rate_schedule_id => p_rate_schedule_id,
847 x_usage_code => l_usage_code);
848 IF (l_usage_code = 'USED') THEN
849 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
850 fnd_message.set_name('CN', 'CN_SCHEDULE_IN_USE');
851 fnd_msg_pub.ADD;
852 END IF;
853 RAISE fnd_api.g_exc_error;
854 END IF;
855
856 /* Start - R12 Notes History */
857 SELECT org_id INTO l_org_id
858 FROM cn_rate_schedules
859 WHERE rate_schedule_id = p_rate_schedule_id;
860
861 SELECT name INTO l_rate_sch_name
862 FROM cn_rate_schedules
863 WHERE rate_schedule_id = p_rate_schedule_id;
864 /* End - R12 Notes History */
865
866 -- deleting a rate schedule causes cascading delete
867 cn_multi_rate_schedules_pkg.delete_row(p_rate_schedule_id);
868
869 -- *********************************************************************
870 -- ************ Start - R12 Notes History ******************************
871 -- *********************************************************************
872 IF (l_org_id <> -999) THEN
873 fnd_message.set_name('CN', 'CNR12_NOTE_RT_DELETE');
874 fnd_message.set_token('RT_NAME', l_rate_sch_name);
875 l_note_msg := fnd_message.get;
876
877 jtf_notes_pub.create_note
878 (p_api_version => 1.0,
879 x_return_status => x_return_status,
880 x_msg_count => x_msg_count,
881 x_msg_data => x_msg_data,
882 p_source_object_id => l_org_id,
883 p_source_object_code => 'CN_DELETED_OBJECTS',
884 p_notes => l_note_msg,
885 p_notes_detail => l_note_msg,
886 p_note_type => 'CN_SYSGEN', -- for system generated
887 x_jtf_note_id => l_note_id -- returned
888 );
889 END IF;
890
891 -- *********************************************************************
892 -- ************ End - R12 Notes History ********************************
893 -- *********************************************************************
894
895 -- End of API body.
896
897 -- Standard check of p_commit.
898 IF FND_API.To_Boolean( p_commit ) THEN
899 COMMIT WORK;
900 END IF;
901 -- Standard call to get message count and if count is 1, get message info.
902 FND_MSG_PUB.count_and_get
903 (p_count => x_msg_count ,
904 p_data => x_msg_data ,
905 p_encoded => FND_API.G_FALSE );
906 EXCEPTION
907 WHEN FND_API.G_EXC_ERROR THEN
908 ROLLBACK TO Delete_Schedule;
909 x_return_status := FND_API.G_RET_STS_ERROR ;
910 FND_MSG_PUB.count_and_get
911 (p_count => x_msg_count ,
912 p_data => x_msg_data ,
913 p_encoded => FND_API.G_FALSE );
914 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
915 ROLLBACK TO Delete_Schedule;
916 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
917 FND_MSG_PUB.count_and_get
918 (p_count => x_msg_count ,
919 p_data => x_msg_data ,
920 p_encoded => FND_API.G_FALSE );
921 WHEN OTHERS THEN
922 ROLLBACK TO Delete_Schedule;
923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
924 IF FND_MSG_PUB.check_msg_level
925 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
926 THEN
927 FND_MSG_PUB.add_exc_msg
928 (G_PKG_NAME ,
929 l_api_name );
930 END IF;
931 FND_MSG_PUB.count_and_get
932 (p_count => x_msg_count ,
933 p_data => x_msg_data ,
934 p_encoded => FND_API.G_FALSE );
935 END Delete_Schedule;
936
937 -- Start of comments
938 -- API name : Delete_Dimension_Assign
939 -- Type : Private.
940 -- Function :
941 -- Pre-reqs : None.
942 -- Parameters :
943 -- IN : p_api_version IN NUMBER Required
944 -- p_init_msg_list IN VARCHAR2 Optional
945 -- Default = FND_API.G_FALSE
946 -- p_commit IN VARCHAR2 Optional
947 -- Default = FND_API.G_FALSE
948 -- p_validation_level IN NUMBER Optional
949 -- Default = FND_API.G_VALID_LEVEL_FULL
950 -- p_rate_sch_dim_id IN NUMBER
951 -- p_rate_schedule_id IN NUMBER
952 -- OUT : x_return_status OUT VARCHAR2(1)
953 -- x_msg_count OUT NUMBER
954 -- x_msg_data OUT VARCHAR2(2000)
955 -- Version : Current version 1.0
956 -- Initial version 1.0
957 --
958 -- Notes : Delete schedule dimension
959 -- 1) If the rate schedule is used, its dimensions can not be deleted
960 -- 2) delete the corresponding records in cn_rate_sch_dims and cn_rate_tiers
961 -- 3) update cn_rate_schedules.number_dim if not called from form
962 -- 4) rate_dim_sequence is not adjusted here, users should take care
963 -- of the adjustment by calling
964 -- update_dimension_assign
965 --
966 -- End of comments
967 PROCEDURE delete_dimension_assign
968 (p_api_version IN NUMBER ,
969 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
970 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
971 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
972 p_rate_sch_dim_id IN CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE,
973 p_rate_schedule_id IN CN_RATE_SCH_DIMS.RATE_SCHEDULE_ID%TYPE,
974 --R12 MOAC Changes--Start
975 p_object_version_number IN CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE, -- new
976 --R12 MOAC Changes--End
977 x_return_status OUT NOCOPY VARCHAR2 ,
978 x_msg_count OUT NOCOPY NUMBER ,
979 x_msg_data OUT NOCOPY VARCHAR2 )
980 IS
981 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Dimension_Assign';
982 l_api_version CONSTANT NUMBER := 1.0;
983
984 l_usage_code VARCHAR2(30);
985 l_rate_dim_sequence NUMBER;
986
987 --R12 Notes Hoistory
988 l_dimension_name_old VARCHAR2(30);
989 l_rate_sch_name_old VARCHAR2(80);
990 l_org_id Number;
991 l_note_msg VARCHAR2(240);
992 l_note_id NUMBER;
993 l_dimension_id NUMBER;
994
995 BEGIN
996 -- Standard Start of API savepoint
997 SAVEPOINT Delete_Dimension_Assign;
998 -- Standard call to check for call compatibility.
999 IF NOT FND_API.Compatible_API_Call
1000 (l_api_version ,
1001 p_api_version ,
1002 l_api_name ,
1003 G_PKG_NAME )
1004 THEN
1005 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1006 END IF;
1007 -- Initialize message list if p_init_msg_list is set to TRUE.
1008 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1009 FND_MSG_PUB.initialize;
1010 END IF;
1011 -- Initialize API return status to success
1012 x_return_status := FND_API.G_RET_STS_SUCCESS;
1013
1014 -- API body
1015
1016 usage_check(p_rate_schedule_id => p_rate_schedule_id,
1017 x_usage_code => l_usage_code);
1018 IF (l_usage_code = 'USED') THEN
1019 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1020 fnd_message.set_name('CN', 'CN_SCHEDULE_IN_USE');
1021 fnd_msg_pub.ADD;
1022 END IF;
1023 RAISE fnd_api.g_exc_error;
1024 END IF;
1025
1026 -- delete the records corresponding to this dimension in cn_rate_tiers
1027 BEGIN
1028 SELECT rate_dim_sequence
1029 INTO l_rate_dim_sequence
1030 FROM cn_rate_sch_dims
1031 WHERE rate_schedule_id = p_rate_schedule_id
1032 AND rate_sch_dim_id = p_rate_sch_dim_id;
1033 EXCEPTION
1034 when no_data_found then
1035 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
1036 fnd_msg_pub.add;
1037 raise fnd_api.g_exc_unexpected_error;
1038 END;
1039
1040 delete_rate_tiers(p_rate_schedule_id => p_rate_schedule_id,
1041 p_rate_dim_sequence => l_rate_dim_sequence);
1042
1043 /* Start - R12 Notes History */
1044
1045 SELECT org_id,rate_dimension_id INTO l_org_id,l_dimension_id
1046 FROM cn_rate_sch_dims
1047 WHERE rate_sch_dim_id = p_rate_sch_dim_id;
1048
1049 select name into l_dimension_name_old
1050 from cn_rate_dimensions
1051 where rate_dimension_id = l_dimension_id;
1052
1053 select name into l_rate_sch_name_old
1054 from cn_rate_schedules
1055 where rate_schedule_id = p_rate_schedule_id;
1056
1057 /* End - R12 Notes History */
1058
1059 -- delete records in cn_rate_sch_dims
1060 cn_rate_sch_dims_pkg.delete_row(x_rate_sch_dim_id => p_rate_sch_dim_id);
1061
1062 -- *********************************************************************
1063 -- ************ Start - R12 Notes History ******************************
1064 -- *********************************************************************
1065 IF (l_org_id <> -999) THEN
1066 fnd_message.set_name('CN', 'CNR12_NOTE_RT_ASGN_DIM_DELETE');
1067 fnd_message.set_token('OLD_DIM', l_dimension_name_old);
1068 fnd_message.set_token('RT_NAME', l_rate_sch_name_old);
1069 l_note_msg := fnd_message.get;
1070
1071 jtf_notes_pub.create_note
1072 (p_api_version => 1.0,
1073 x_return_status => x_return_status,
1074 x_msg_count => x_msg_count,
1075 x_msg_data => x_msg_data,
1076 p_source_object_id => p_rate_schedule_id,
1077 p_source_object_code => 'CN_RATE_SCHEDULES',
1078 p_notes => l_note_msg,
1079 p_notes_detail => l_note_msg,
1080 p_note_type => 'CN_SYSGEN', -- for system generated
1081 x_jtf_note_id => l_note_id -- returned
1082 );
1083 END IF;
1084
1085 -- *********************************************************************
1086 -- ************ End - R12 Notes History ********************************
1087 -- *********************************************************************
1088
1089 -- update rate schedule (number_dim is treated as a "virtual column" - just a
1090 -- count(*) of dimensions assigned to the rate_schedule... it is not ovn controlled here
1091 UPDATE cn_rate_schedules
1092 SET number_dim = (select count(*) from cn_rate_sch_dims
1093 where rate_schedule_id = p_rate_schedule_id)
1094 WHERE rate_schedule_id = p_rate_schedule_id;
1095
1096 -- push dimension sequence numbers down by one
1097 --update cn_rate_sch_dims set rate_dim_sequence = rate_dim_sequence - 1
1098 -- where rate_schedule_id = p_rate_schedule_id
1099 -- and rate_dim_sequence >= l_rate_dim_sequence;
1100
1101 -- End of API body.
1102
1103 -- Standard check of p_commit.
1104 IF FND_API.To_Boolean( p_commit ) THEN
1105 COMMIT WORK;
1106 END IF;
1107 -- Standard call to get message count and if count is 1, get message info.
1108 FND_MSG_PUB.Count_And_Get
1109 (p_count => x_msg_count ,
1110 p_data => x_msg_data ,
1111 p_encoded => FND_API.G_FALSE );
1112 EXCEPTION
1113 WHEN FND_API.G_EXC_ERROR THEN
1114 ROLLBACK TO Delete_Dimension_Assign;
1115 x_return_status := FND_API.G_RET_STS_ERROR ;
1116 FND_MSG_PUB.count_and_get
1117 (p_count => x_msg_count ,
1118 p_data => x_msg_data ,
1119 p_encoded => FND_API.G_FALSE );
1120 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1121 ROLLBACK TO Delete_Dimension_Assign;
1122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1123 FND_MSG_PUB.count_and_get
1124 (p_count => x_msg_count ,
1125 p_data => x_msg_data ,
1126 p_encoded => FND_API.G_FALSE );
1127 WHEN OTHERS THEN
1128 ROLLBACK TO Delete_Dimension_Assign;
1129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1130 IF FND_MSG_PUB.check_msg_level
1131 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1132 THEN
1133 FND_MSG_PUB.add_exc_msg
1134 (G_PKG_NAME ,
1135 l_api_name );
1136 END IF;
1137 FND_MSG_PUB.count_and_get
1138 (p_count => x_msg_count ,
1139 p_data => x_msg_data ,
1140 p_encoded => FND_API.G_FALSE );
1141 END Delete_Dimension_Assign;
1142
1143 -- Start of comments
1144 -- API name : Update_Dimension_Assign
1145 -- Type : Private.
1146 -- Function :
1147 -- Pre-reqs : None.
1148 -- Parameters :
1149 -- IN : p_api_version IN NUMBER Required
1150 -- p_init_msg_list IN VARCHAR2 Optional
1151 -- Default = FND_API.G_FALSE
1152 -- p_commit IN VARCHAR2 Optional
1153 -- Default = FND_API.G_FALSE
1154 -- p_validation_level IN NUMBER Optional
1155 -- Default = FND_API.G_VALID_LEVEL_FULL
1156 -- p_rate_sch_dim_id IN NUMBER
1157 -- p_rate_schedule_id IN NUMBER
1158 -- p_rate_dimension_id IN NUMBER
1159 -- p_rate_dim_sequence IN NUMBER
1160 -- OUT : x_return_status OUT VARCHAR2(1)
1161 -- x_msg_count OUT NUMBER
1162 -- x_msg_data OUT VARCHAR2(2000)
1163 -- Version : Current version 1.0
1164 -- Initial version 1.0
1165 --
1166 -- Notes : Update dimension assignment
1167 -- 1) If the rate table is used, then update is not allowed
1168 -- 2) If it can be updated, update records in cn_rate_sch_dims and cn_rate_tiers
1169 --
1170 -- End of comments
1171 PROCEDURE update_dimension_assign
1172 (p_api_version IN NUMBER ,
1173 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1174 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1175 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1176 p_rate_sch_dim_id IN CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE,
1177 p_rate_schedule_id IN CN_RATE_SCH_DIMS.RATE_SCHEDULE_ID%TYPE,
1178 p_rate_dimension_id IN CN_RATE_SCH_DIMS.RATE_DIMENSION_ID%TYPE := cn_api.g_miss_num,
1179 p_rate_dim_sequence IN CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE := cn_api.g_miss_num, -- not used
1180 --R12 MOAC Changes--Start
1181 p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE, --new
1182 p_object_version_number IN OUT NOCOPY CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE, --changed
1183 --R12 MOAC Changes--End
1184 x_return_status OUT NOCOPY VARCHAR2 ,
1185 x_msg_count OUT NOCOPY NUMBER ,
1186 x_msg_data OUT NOCOPY VARCHAR2 )
1187 IS
1188 l_api_name CONSTANT VARCHAR2(30) := 'Update_Dimension_Assign';
1189 l_api_version CONSTANT NUMBER := 1.0;
1190
1191 l_rate_dimension_id_old CN_RATE_SCH_DIMS.RATE_DIMENSION_ID%TYPE;
1192 l_number_tier_old CN_RATE_DIMENSIONS.NUMBER_TIER%TYPE;
1193 l_number_tier_new CN_RATE_DIMENSIONS.NUMBER_TIER%TYPE;
1194 l_usage_code VARCHAR2(30);
1195 l_rate_dim_sequence NUMBER;
1196 l_count NUMBER;
1197
1198 --R12 Notes Hoistory
1199 l_rate_dimension_id Number;
1200 l_dimension_name_old VARCHAR2(30);
1201 l_dimension_name_new VARCHAR2(30);
1202 l_note_msg VARCHAR2(240);
1203 l_note_id NUMBER;
1204
1205 CURSOR old_sch_dim IS
1206 SELECT rate_dimension_id, rate_dim_sequence
1207 FROM cn_rate_sch_dims
1208 WHERE rate_sch_dim_id = p_rate_sch_dim_id;
1209
1210 CURSOR old_dim_info IS
1211 SELECT number_tier
1212 FROM cn_rate_dimensions
1213 WHERE rate_dimension_id = l_rate_dimension_id_old;
1214
1215 CURSOR new_dim_info IS
1216 SELECT number_tier
1217 FROM cn_rate_dimensions
1218 WHERE rate_dimension_id = p_rate_dimension_id;
1219 BEGIN
1220 -- Standard Start of API savepoint
1221 SAVEPOINT Update_Dimension_Assign;
1222 -- Standard call to check for call compatibility.
1223 IF NOT FND_API.Compatible_API_Call
1224 (l_api_version ,
1225 p_api_version ,
1226 l_api_name ,
1227 G_PKG_NAME )
1228 THEN
1229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1230 END IF;
1231 -- Initialize message list if p_init_msg_list is set to TRUE.
1232 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1233 FND_MSG_PUB.initialize;
1234 END IF;
1235 -- Initialize API return status to success
1236 x_return_status := FND_API.G_RET_STS_SUCCESS;
1237
1238 -- API body
1239
1240 usage_check(p_rate_schedule_id,
1241 x_usage_code => l_usage_code);
1242 IF (l_usage_code = 'USED') THEN
1243 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1244 fnd_message.set_name('CN', 'CN_SCHEDULE_IN_USE');
1245 fnd_msg_pub.ADD;
1246 END IF;
1247 RAISE fnd_api.g_exc_error;
1248 END IF;
1249
1250 OPEN old_sch_dim;
1251 FETCH old_sch_dim INTO l_rate_dimension_id_old, l_rate_dim_sequence;
1252 CLOSE old_sch_dim;
1253
1254 OPEN old_dim_info;
1255 FETCH old_dim_info INTO l_number_tier_old;
1256 CLOSE old_dim_info;
1257
1258 OPEN new_dim_info;
1259 FETCH new_dim_info INTO l_number_tier_new;
1260 CLOSE new_dim_info;
1261
1262 -- if rate dimension is replaced, then adjust cn_rate_tiers also
1263 -- remove the dimension and re-create it
1264 IF (l_rate_dimension_id_old <> p_rate_dimension_id) THEN
1265 -- make sure the dimension hasn't already been assigned
1266 select count(*) into l_count from cn_rate_sch_dims
1267 where rate_schedule_id = p_rate_schedule_id
1268 and rate_dimension_id = p_rate_dimension_id;
1269 if l_count > 0 then
1270 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1271 fnd_message.set_name('CN', 'CN_DUPLICATE_DIM_ASSIGN');
1272 fnd_msg_pub.ADD;
1273 END IF;
1274 RAISE fnd_api.g_exc_error;
1275 END IF;
1276 IF (l_number_tier_new > l_number_tier_old) THEN
1277 create_rate_tiers(p_rate_schedule_id => p_rate_schedule_id,
1278 p_rate_dim_sequence => l_rate_dim_sequence,
1279 p_tier_sequence => l_number_tier_old + 1,
1280 p_num_tiers => l_number_tier_new - l_number_tier_old,
1281 --R12 MOAC Changes--Start
1282 p_org_id => p_org_id );
1283 --R12 MOAC Changes--End
1284 ELSIF (l_number_tier_new < l_number_tier_old) THEN
1285 delete_rate_tiers(p_rate_schedule_id => p_rate_schedule_id,
1286 p_rate_dim_sequence => l_rate_dim_sequence,
1287 p_tier_sequence => l_number_tier_new + 1,
1288 p_num_tiers => l_number_tier_old - l_number_tier_new);
1289 END IF;
1290 END IF;
1291
1292 -- Move Up/Down - Setting Commission rates to 0
1293 if ( (l_rate_dimension_id_old = p_rate_dimension_id) AND (p_rate_dim_sequence <> l_rate_dim_sequence)) THEN
1294 delete from cn_rate_tiers where rate_sequence <> 1 AND rate_schedule_id= p_rate_schedule_id AND org_id = p_org_id;
1295 END IF;
1296
1297 -- Start - R12 Notes History Query for old dimension id assigned to rate table
1298 select rate_dimension_id into l_rate_dimension_id
1299 from cn_rate_sch_dims
1300 where rate_sch_dim_id = p_rate_sch_dim_id;
1301
1302 select name into l_dimension_name_old
1303 from cn_rate_dimensions
1304 where rate_dimension_id = l_rate_dimension_id;
1305 -- End - R12 Notes History Query for old dimension id assigned to rate table
1306
1307 -- lock and update the row
1308 cn_rate_sch_dims_pkg.lock_row
1309 (x_rate_sch_dim_id => p_rate_sch_dim_id,
1310 x_object_version_number => p_object_version_number);
1311
1312 cn_rate_sch_dims_pkg.update_row
1313 (x_rate_sch_dim_id => p_rate_sch_dim_id,
1314 x_rate_schedule_id => p_rate_schedule_id,
1315 x_rate_dimension_id => p_rate_dimension_id,
1316 x_rate_dim_sequence => p_rate_dim_sequence,
1317 x_object_version_number => p_object_version_number);
1318
1319 -- *********************************************************************
1320 -- ************ Start - R12 Notes History ************** ***************
1321 -- *********************************************************************
1322 IF (p_rate_dimension_id <> l_rate_dimension_id) THEN
1323
1324 select name into l_dimension_name_new
1325 from cn_rate_dimensions
1326 where rate_dimension_id = p_rate_dimension_id;
1327
1328 fnd_message.set_name('CN', 'CNR12_NOTE_RT_ASGN_DIM_UPDATE');
1329 fnd_message.set_token('OLD_DIM', l_dimension_name_old);
1330 fnd_message.set_token('NEW_DIM', l_dimension_name_new);
1331 l_note_msg := fnd_message.get;
1332
1333 jtf_notes_pub.create_note
1334 (p_api_version => 1.0,
1335 x_return_status => x_return_status,
1336 x_msg_count => x_msg_count,
1337 x_msg_data => x_msg_data,
1338 p_source_object_id => p_rate_schedule_id,
1339 p_source_object_code => 'CN_RATE_SCHEDULES',
1340 p_notes => l_note_msg,
1341 p_notes_detail => l_note_msg,
1342 p_note_type => 'CN_SYSGEN', -- for system generated
1343 x_jtf_note_id => l_note_id -- returned
1344 );
1345 END IF;
1346 -- *********************************************************************
1347 -- ************ End - R12 Notes History ********************************
1348 -- *********************************************************************
1349 -- End of API body.
1350
1351 -- Standard check of p_commit.
1352 IF FND_API.To_Boolean( p_commit ) THEN
1353 COMMIT WORK;
1354 END IF;
1355 -- Standard call to get message count and if count is 1, get message info.
1356 FND_MSG_PUB.Count_And_Get
1357 (p_count => x_msg_count ,
1358 p_data => x_msg_data ,
1359 p_encoded => FND_API.G_FALSE );
1360 EXCEPTION
1361 WHEN FND_API.G_EXC_ERROR THEN
1362 ROLLBACK TO Update_Dimension_Assign;
1363 x_return_status := FND_API.G_RET_STS_ERROR ;
1364 FND_MSG_PUB.count_and_get
1365 (p_count => x_msg_count ,
1366 p_data => x_msg_data ,
1367 p_encoded => FND_API.G_FALSE );
1368 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1369 ROLLBACK TO Update_Dimension_Assign;
1370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1371 FND_MSG_PUB.count_and_get
1372 (p_count => x_msg_count ,
1373 p_data => x_msg_data ,
1374 p_encoded => FND_API.G_FALSE );
1375 WHEN OTHERS THEN
1376 ROLLBACK TO Update_Dimension_Assign;
1377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1378 IF FND_MSG_PUB.check_msg_level
1379 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1380 THEN
1381 FND_MSG_PUB.add_exc_msg
1382 (G_PKG_NAME ,
1383 l_api_name );
1384 END IF;
1385 FND_MSG_PUB.count_and_get
1386 (p_count => x_msg_count ,
1387 p_data => x_msg_data ,
1388 p_encoded => FND_API.G_FALSE );
1389 END Update_Dimension_Assign;
1390
1391 -- Start of comments
1392 -- API name : Create_Dimension_Assign
1393 -- Type : Private.
1394 -- Function :
1395 -- Pre-reqs : None.
1396 -- Parameters :
1397 -- IN : p_api_version IN NUMBER Required
1398 -- p_init_msg_list IN VARCHAR2 Optional
1399 -- Default = FND_API.G_FALSE
1400 -- p_commit IN VARCHAR2 Optional
1401 -- Default = FND_API.G_FALSE
1402 -- p_validation_level IN NUMBER Optional
1403 -- Default = FND_API.G_VALID_LEVEL_FULL
1404 -- p_rate_schedule_id IN NUMBER
1405 -- p_rate_dimension_id IN NUMBER
1406 -- p_rate_dim_sequence IN NUMBER
1407 -- OUT : x_rate_sch_dim_id OUT NUMBER
1408 -- x_return_status OUT VARCHAR2(1)
1409 -- x_msg_count OUT NUMBER
1410 -- x_msg_data OUT VARCHAR2(2000)
1411 -- Version : Current version 1.0
1412 -- Initial version 1.0
1413 --
1414 -- Notes : Create dimension assignment
1415 -- 1) If the rate table is used, new assignment can not be created
1416 -- 2) if the rate table is not used, update cn_rate_tiers;
1417 -- and adjust cn_rate_tiers.rate_sequence
1418 -- 3) update cn_rate_schedules.number_dim
1419 -- 4) rate_dim_sequence is not adjusted here, users should do it by
1420 -- calling update_dimension_assign
1421 --
1422 -- End of comments
1423 PROCEDURE create_dimension_assign
1424 (p_api_version IN NUMBER ,
1425 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1426 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1427 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1428 p_rate_schedule_id IN CN_RATE_SCH_DIMS.RATE_SCHEDULE_ID%TYPE,
1429 p_rate_dimension_id IN CN_RATE_SCH_DIMS.RATE_DIMENSION_ID%TYPE,
1430 p_rate_dim_sequence IN CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE,
1431 --R12 MOAC Changes--Start
1432 p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE, --new
1433 x_rate_sch_dim_id IN OUT NOCOPY CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE, --changed
1434 --R12 MOAC Changes--End
1435 x_return_status OUT NOCOPY VARCHAR2 ,
1436 x_msg_count OUT NOCOPY NUMBER ,
1437 x_msg_data OUT NOCOPY VARCHAR2 )
1438 IS
1439 l_api_name CONSTANT VARCHAR2(30) := 'Create_Dimension_Assign';
1440 l_api_version CONSTANT NUMBER := 1.0;
1441
1442 l_usage_code VARCHAR2(30);
1443 l_dummy NUMBER;
1444 l_count NUMBER;
1445 l_num_dims NUMBER;
1446 l_rate_dim_sequence NUMBER := p_rate_dim_sequence;
1447
1448 --R12 Notes Hoistory
1449 l_dimension_name VARCHAR2(30);
1450 l_rate_sch_name VARCHAR2(80);
1451 l_note_msg VARCHAR2(240);
1452 l_note_id NUMBER;
1453
1454 CURSOR tier_exist IS
1455 SELECT count(1) from cn_rate_tiers
1456 WHERE rate_schedule_id = p_rate_schedule_id;
1457 BEGIN
1458 -- Standard Start of API savepoint
1459 SAVEPOINT Create_Dimension_Assign;
1460 -- Standard call to check for call compatibility.
1461 IF NOT FND_API.Compatible_API_Call
1462 (l_api_version ,
1463 p_api_version ,
1464 l_api_name ,
1465 G_PKG_NAME )
1466 THEN
1467 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1468 END IF;
1469 -- Initialize message list if p_init_msg_list is set to TRUE.
1470 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1471 FND_MSG_PUB.initialize;
1472 END IF;
1473 -- Initialize API return status to success
1474 x_return_status := FND_API.G_RET_STS_SUCCESS;
1475
1476 -- API body
1477
1478 usage_check(p_rate_schedule_id,
1479 x_usage_code => l_usage_code);
1480 IF (l_usage_code = 'USED') THEN
1481 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1482 fnd_message.set_name('CN', 'CN_SCHEDULE_IN_USE');
1483 fnd_msg_pub.ADD;
1484 END IF;
1485 RAISE fnd_api.g_exc_error;
1486 END IF;
1487
1488 -- make sure the dimension hasn't already been assigned
1489 select count(*) into l_count from cn_rate_sch_dims
1490 where rate_schedule_id = p_rate_schedule_id
1491 and rate_dimension_id = p_rate_dimension_id;
1492 if l_count > 0 then
1493 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
1494 fnd_message.set_name('CN', 'CN_DUPLICATE_DIM_ASSIGN');
1495 fnd_msg_pub.ADD;
1496 END IF;
1497 RAISE fnd_api.g_exc_error;
1498 END IF;
1499
1500 -- if assigned rate sequence is too high, bring it down to a valid value
1501 select count(*) into l_num_dims
1502 from cn_rate_sch_dims
1503 where rate_schedule_id = p_rate_schedule_id;
1504
1505 --push dimensions with higher sequence numbers than l_rate_dim_sequence up by one
1506 -- l_rate_dim_sequence := l_num_dims + 1;
1507 --end if;
1508
1509 -- push dimensions with higher sequence numbers than l_rate_dim_sequence up by one
1510 --update cn_rate_sch_dims set rate_dim_sequence = rate_dim_sequence + 1
1511 -- where rate_schedule_id = p_rate_schedule_id
1512 -- and rate_dim_sequence >= l_rate_dim_sequence;
1513
1514 -- create this dimension assignment in cn_rate_sch_dims
1515 cn_rate_sch_dims_pkg.insert_row(x_rate_sch_dim_id => x_rate_sch_dim_id,
1516 x_rate_schedule_id => p_rate_schedule_id,
1517 x_rate_dimension_id => p_rate_dimension_id,
1518 x_rate_dim_sequence => l_rate_dim_sequence,
1519 --R12 MOAC Changes--Start
1520 x_org_id => p_org_id
1521 --R12 MOAC Changes--End
1522 );
1523
1524 -- *********************************************************************
1525 -- ************ Start - R12 Notes History ******************************
1526 -- *********************************************************************
1527 select name into l_dimension_name
1528 from cn_rate_dimensions
1529 where rate_dimension_id = p_rate_dimension_id;
1530
1531 select name into l_rate_sch_name
1532 from cn_rate_schedules
1533 where rate_schedule_id = p_rate_schedule_id;
1534
1535 fnd_message.set_name('CN', 'CNR12_NOTE_RT_ASGN_DIM_CREATE');
1536 fnd_message.set_token('RT_DIM', l_dimension_name);
1537 fnd_message.set_token('RT_NAME', l_rate_sch_name);
1538 l_note_msg := fnd_message.get;
1539
1540 jtf_notes_pub.create_note
1541 (p_api_version => 1.0,
1542 x_return_status => x_return_status,
1543 x_msg_count => x_msg_count,
1544 x_msg_data => x_msg_data,
1545 p_source_object_id => p_rate_schedule_id,
1546 p_source_object_code => 'CN_RATE_SCHEDULES',
1547 p_notes => l_note_msg,
1548 p_notes_detail => l_note_msg,
1549 p_note_type => 'CN_SYSGEN', -- for system generated
1550 x_jtf_note_id => l_note_id -- returned
1551 );
1552
1553 -- *********************************************************************
1554 -- ************ End - R12 Notes History ********************************
1555 -- *********************************************************************
1556
1557 -- update rate schedule (number_dim is treated as a "virtual column" - just a
1558 -- count(*) of dimensions assigned to the rate_schedule... it is not ovn controlled here
1559 UPDATE cn_rate_schedules
1560 SET number_dim = l_num_dims + 1
1561 WHERE rate_schedule_id = p_rate_schedule_id;
1562
1563 -- insert records into cn_rate_tiers
1564 OPEN tier_exist;
1565 FETCH tier_exist INTO l_dummy;
1566 CLOSE tier_exist;
1567
1568 IF (l_dummy > 0) THEN
1569 create_rate_tiers(p_rate_schedule_id => p_rate_schedule_id,
1570 p_rate_dim_sequence => l_rate_dim_sequence,
1571 --R12 MOAC Changes--Start
1572 p_org_id => p_org_id
1573 --R12 MOAC Changes--End
1574 );
1575 -- if table had no tiers, then nothing to migrate
1576 /*
1577 ELSE
1578 create_rate_tiers(p_rate_schedule_id => p_rate_schedule_id,
1579 p_rate_dim_sequence => NULL);
1580 */
1581 END IF;
1582
1583 -- End of API body.
1584
1585 -- Standard check of p_commit.
1586 IF FND_API.To_Boolean( p_commit ) THEN
1587 COMMIT WORK;
1588 END IF;
1589 -- Standard call to get message count and if count is 1, get message info.
1590 FND_MSG_PUB.Count_And_Get
1591 (p_count => x_msg_count ,
1592 p_data => x_msg_data ,
1593 p_encoded => FND_API.G_FALSE );
1594 EXCEPTION
1595 WHEN FND_API.G_EXC_ERROR THEN
1596 ROLLBACK TO Create_Dimension_Assign;
1597 x_return_status := FND_API.G_RET_STS_ERROR ;
1598 FND_MSG_PUB.count_and_get
1599 (p_count => x_msg_count ,
1600 p_data => x_msg_data ,
1601 p_encoded => FND_API.G_FALSE );
1602 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1603 ROLLBACK TO Create_Dimension_Assign;
1604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1605 FND_MSG_PUB.count_and_get
1606 (p_count => x_msg_count ,
1607 p_data => x_msg_data ,
1608 p_encoded => FND_API.G_FALSE );
1609 WHEN OTHERS THEN
1610 ROLLBACK TO Create_Dimension_Assign;
1611 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1612 IF FND_MSG_PUB.check_msg_level
1613 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1614 THEN
1615 FND_MSG_PUB.add_exc_msg
1616 (G_PKG_NAME ,
1617 l_api_name );
1618 END IF;
1619 FND_MSG_PUB.count_and_get
1620 (p_count => x_msg_count ,
1621 p_data => x_msg_data ,
1622 p_encoded => FND_API.G_FALSE );
1623 END Create_Dimension_Assign;
1624
1625 -- 1. if two or more tiers are inserted at the same time, create_rate_tiers will
1626 -- face problems. The solution is to use the actual number of tiers in the dimension
1627 -- instead of cn_rate_dimensions.number_tier
1628 -- 2. form processing changes in the following order: delete --> update --> insert
1629 PROCEDURE create_rate_tiers
1630 (p_rate_schedule_id CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
1631 p_rate_dim_sequence CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE := NULL,
1632 p_tier_sequence CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE := NULL,
1633 p_num_tiers NUMBER := 1,
1634 --R12 MOAC Changes--Start
1635 p_org_id IN CN_RATE_TIERS.ORG_ID%TYPE
1636 --R12 MOAC Changes--End
1637 ) IS
1638
1639 l_number_dim CN_RATE_SCHEDULES.NUMBER_DIM%TYPE;
1640 l_coords NUM_TBL_TYPE;
1641 l_seq NUMBER;
1642 l_new_seq NUMBER;
1643 l_dim_count NUMBER := 0; -- number of dimensions in cn_rate_sch_dims for this rate table
1644 dim_size_table num_tbl_type;
1645 l_num_tiers number := nvl(p_num_tiers,1);
1646
1647 CURSOR dims_info IS
1648 SELECT COUNT(*) number_tier
1649 FROM cn_rate_dim_tiers rdt,
1650 cn_rate_sch_dims rsd
1651 WHERE rdt.rate_dimension_id = rsd.rate_dimension_id
1652 AND rsd.rate_schedule_id = p_rate_schedule_id
1653 GROUP BY rsd.rate_dim_sequence
1654 ORDER BY rsd.rate_dim_sequence;
1655
1656 CURSOR get_rate_tiers IS
1657 SELECT rate_tier_id, rate_sequence
1658 FROM cn_rate_tiers
1659 WHERE rate_schedule_id = p_rate_schedule_id;
1660
1661 BEGIN
1662 SELECT number_dim
1663 INTO l_number_dim
1664 FROM cn_rate_schedules
1665 WHERE rate_schedule_id = p_rate_schedule_id;
1666
1667 -- build dimension size table
1668 FOR dim IN dims_info LOOP
1669 l_dim_count := l_dim_count + 1;
1670 dim_size_table(l_dim_count) := dim.number_tier;
1671 END LOOP;
1672
1673 -- each dimension must have at least one tier. otherwise, raise an exception
1674 FOR j IN 1..l_dim_count LOOP
1675 IF (dim_size_table(j) = 0) THEN
1676 fnd_message.set_name('CN', 'CN_EMPTY_DIMENSION');
1677 RAISE fnd_api.g_exc_error;
1678 END IF;
1679 END LOOP;
1680
1681 -- initialize coordinates
1682 for d in 1..l_dim_count loop
1683 l_coords(d) := 1;
1684 end loop;
1685
1686 -- note all arrays are 1-indexed
1687 for t in get_rate_tiers loop
1688 -- get coordinates of t
1689 l_seq := t.rate_sequence;
1690
1691 for d in reverse 1..l_dim_count loop
1692 -- get old coordinates of rate tier
1693 if p_tier_sequence is null then
1694 if d = p_rate_dim_sequence then
1695 l_coords(d) := 1;
1696 else
1697 l_coords(d) := mod(l_seq-1, dim_size_table(d)) + 1;
1698 l_seq := (l_seq - l_coords(d)) / dim_size_table(d) + 1;
1699 end if;
1700 else
1701 l_coords(d) := mod(l_seq-1, dim_size_table(d)) + 1;
1702 l_seq := (l_seq - l_coords(d)) / dim_size_table(d) + 1;
1703 if d = p_rate_dim_sequence and l_coords(d) >= p_tier_sequence then
1704 l_coords(d) := l_coords(d) + l_num_tiers;
1705 end if;
1706 end if;
1707 end loop;
1708
1709 l_new_seq := 1;
1710 -- get new dimensions of rate tier
1711 for d in 1..l_dim_count loop
1712 -- accomodate the expanded tier if creating rate tiers
1713 if p_tier_sequence is not null and d = p_rate_dim_sequence then
1714 l_new_seq := (l_new_seq - 1) *
1715 (dim_size_table(d) + l_num_tiers) + l_coords(d);
1716 else
1717 l_new_seq := (l_new_seq - 1) * dim_size_table(d) + l_coords(d);
1718 end if;
1719 end loop;
1720
1721 -- update table
1722 update cn_rate_tiers set rate_sequence = l_new_seq
1723 where rate_tier_id = t.rate_tier_id;
1724 end loop;
1725
1726 -- update cn_srp_rate_assigns.rate_sequence
1727 UPDATE cn_srp_rate_assigns sra
1728 SET rate_sequence = (SELECT rate_sequence
1729 FROM cn_rate_tiers
1730 WHERE rate_schedule_id = p_rate_schedule_id
1731 AND rate_tier_id = sra.rate_tier_id)
1732 WHERE rate_schedule_id = p_rate_schedule_id;
1733
1734 END create_rate_tiers;
1735
1736 PROCEDURE delete_rate_tiers
1737 (p_rate_schedule_id CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
1738 p_rate_dim_sequence CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE,
1739 p_tier_sequence CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE := NULL,
1740 p_num_tiers NUMBER := 1) IS
1741
1742 l_number_dim CN_RATE_SCHEDULES.NUMBER_DIM%TYPE;
1743 l_coords NUM_TBL_TYPE;
1744 l_seq NUMBER;
1745 l_new_seq NUMBER;
1746 l_dim_count NUMBER := 0; -- number of dimensions in cn_rate_sch_dims for this rate table
1747 dim_size_table num_tbl_type;
1748 l_num_tiers number := nvl(p_num_tiers,1);
1749 delete_flag boolean;
1750
1751 CURSOR dims_info IS
1752 SELECT COUNT(*) number_tier
1753 FROM cn_rate_dim_tiers rdt,
1754 cn_rate_sch_dims rsd
1755 WHERE rdt.rate_dimension_id = rsd.rate_dimension_id
1756 AND rsd.rate_schedule_id = p_rate_schedule_id
1757 GROUP BY rsd.rate_dim_sequence
1758 ORDER BY rsd.rate_dim_sequence;
1759
1760 CURSOR get_rate_tiers IS
1761 SELECT rate_tier_id, rate_sequence
1762 FROM cn_rate_tiers
1763 WHERE rate_schedule_id = p_rate_schedule_id;
1764
1765 BEGIN
1766 SELECT number_dim
1767 INTO l_number_dim
1768 FROM cn_rate_schedules
1769 WHERE rate_schedule_id = p_rate_schedule_id;
1770
1771 -- build dimension size table
1772 FOR dim IN dims_info LOOP
1773 l_dim_count := l_dim_count + 1;
1774 dim_size_table(l_dim_count) := dim.number_tier;
1775 END LOOP;
1776
1777 -- each dimension must have at least one tier. otherwise, raise an exception
1778 FOR j IN 1..l_dim_count LOOP
1779 IF (dim_size_table(j) = 0) THEN
1780 fnd_message.set_name('CN', 'CN_EMPTY_DIMENSION');
1781 RAISE fnd_api.g_exc_error;
1782 END IF;
1783 END LOOP;
1784
1785 -- note all arrays are 1-indexed
1786 for t in get_rate_tiers loop
1787 -- get coordinates of t
1788 l_seq := t.rate_sequence;
1789
1790 -- see if the tier needs to be deleted
1791 delete_flag := false;
1792 for d in reverse 1..l_dim_count loop
1793 -- get old coordinates of rate tier
1794 if p_tier_sequence is null then
1795 l_coords(d) := mod(l_seq-1, dim_size_table(d)) + 1;
1796 if d = p_rate_dim_sequence and l_coords(d) > 1 then
1797 delete_flag := true;
1798 else
1799 l_seq := (l_seq - l_coords(d)) / dim_size_table(d) + 1;
1800 end if;
1801 else
1802 l_coords(d) := mod(l_seq-1, dim_size_table(d)) + 1;
1803 l_seq := (l_seq - l_coords(d)) / dim_size_table(d) + 1;
1804 if d = p_rate_dim_sequence then
1805 if l_coords(d) between
1806 p_tier_sequence and p_tier_sequence + l_num_tiers - 1 then
1807 delete_flag := true;
1808 elsif l_coords(d) >= p_tier_sequence + l_num_tiers then
1809 l_coords(d) := l_coords(d) - l_num_tiers;
1810 end if;
1811 end if;
1812 end if;
1813 end loop;
1814
1815 if delete_flag = true then
1816 delete from cn_rate_tiers
1817 where rate_tier_id = t.rate_tier_id;
1818 delete from cn_srp_rate_assigns
1819 where rate_tier_id = t.rate_tier_id;
1820 else
1821 l_new_seq := 1;
1822 -- get new dimensions of rate tier
1823 for d in 1..l_dim_count loop
1824 -- accomodate the smaller tier if deleting rate tiers
1825 if p_tier_sequence is not null and d = p_rate_dim_sequence then
1826 l_new_seq := (l_new_seq - 1) *
1827 (dim_size_table(d) - l_num_tiers) + l_coords(d);
1828 elsif p_tier_sequence is not null or d <> p_rate_dim_sequence then
1829 l_new_seq := (l_new_seq - 1) * dim_size_table(d) + l_coords(d);
1830 end if;
1831 end loop;
1832
1833 -- update table
1834 update cn_rate_tiers set rate_sequence = l_new_seq
1835 where rate_tier_id = t.rate_tier_id;
1836 end if;
1837 end loop;
1838
1839 -- update cn_srp_rate_assigns.rate_sequence
1840 UPDATE cn_srp_rate_assigns sra
1841 SET rate_sequence = (SELECT rate_sequence
1842 FROM cn_rate_tiers
1843 WHERE rate_schedule_id = p_rate_schedule_id
1844 AND rate_tier_id = sra.rate_tier_id)
1845 WHERE rate_schedule_id = p_rate_schedule_id;
1846
1847 END delete_rate_tiers;
1848
1849 PROCEDURE update_rate
1850 (p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
1851 p_rate_sequence IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
1852 p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
1853 --R12 MOAC Changes--Start
1854 p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, --changed
1855 p_org_id CN_RATE_TIERS.ORG_ID%TYPE --new
1856 --R12 MOAC Changes--End
1857 ) IS
1858
1859
1860 x_return_status VARCHAR2(2000);
1861 x_msg_count NUMBER;
1862 x_msg_data VARCHAR2(2000);
1863 l_api_name CONSTANT VARCHAR2(30) := 'Update_Rate';
1864
1865 l_rate_tier_id CN_RATE_TIERS.RATE_TIER_ID%TYPE;
1866 l_commission_amount CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE;
1867
1868 CURSOR rate_tier_info IS
1869 SELECT rate_tier_id, commission_amount
1870 FROM cn_rate_tiers
1871 WHERE rate_schedule_id = p_rate_schedule_id
1872 AND rate_sequence = p_rate_sequence;
1873
1874 CURSOR get_sqa IS
1875 select sqa.srp_plan_assign_id,
1876 sqa.srp_quota_assign_id,
1877 sqa.quota_id,
1878 rqa.rt_quota_asgn_id,
1879 nvl(sqa.customized_flag, 'N') customized
1880 from cn_srp_quota_assigns sqa, cn_rt_quota_asgns rqa
1881 where rqa.rate_schedule_id = p_rate_schedule_id
1882 and sqa.quota_id = rqa.quota_id;
1883
1884 BEGIN
1885
1886 SAVEPOINT Update_rate;
1887 FND_MSG_PUB.initialize;
1888
1889 OPEN rate_tier_info;
1890 FETCH rate_tier_info INTO l_rate_tier_id, l_commission_amount;
1891 IF (rate_tier_info%notfound) THEN
1892 -- record may have to be created
1893 CLOSE rate_tier_info;
1894 if p_commission_amount <> 0 then
1895 cn_rate_tiers_pkg.insert_row
1896 (X_RATE_TIER_ID => l_rate_tier_id,
1897 X_RATE_SCHEDULE_ID => p_rate_schedule_id,
1898 X_COMMISSION_AMOUNT => p_commission_amount,
1899 X_RATE_SEQUENCE => p_rate_sequence,
1900 --R12 MOAC Changes--Start
1901 X_ORG_ID => p_org_id);
1902 --R12 MOAC Changes--End
1903
1904 -- don't need to create sra...if customized, leave as 0 (don't create)
1905 -- if non-customized, don't create (bug 3204833)
1906 end if;
1907 ELSE
1908 CLOSE rate_tier_info;
1909
1910 -- see if amt changed
1911 if p_commission_amount <> l_commission_amount then
1912
1913 -- lock and update the record
1914 cn_rate_tiers_pkg.lock_row
1915 (X_RATE_TIER_ID => l_rate_tier_id,
1916 X_OBJECT_VERSION_NUMBER => p_object_version_number);
1917
1918 cn_rate_tiers_pkg.update_row
1919 (X_RATE_TIER_ID => l_rate_tier_id,
1920 X_RATE_SCHEDULE_ID => p_rate_schedule_id,
1921 X_COMMISSION_AMOUNT => p_commission_amount,
1922 X_RATE_SEQUENCE => p_rate_sequence,
1923 X_OBJECT_VERSION_NUMBER => p_object_version_number);
1924
1925 create_note_bus_event (0,
1926 0 ,
1927 p_rate_sequence ,
1928 l_commission_amount,
1929 p_commission_amount,
1930 null ,
1931 p_rate_schedule_id );
1932
1933 -- sync up srp rate assignments where srps don't have customized rates
1934 update cn_srp_rate_assigns r
1935 set commission_amount = p_commission_amount
1936 where rate_schedule_id = p_rate_schedule_id
1937 and rate_sequence = p_rate_sequence
1938 and exists
1939 (select 1 from cn_srp_quota_assigns r2
1940 where r.srp_quota_assign_id = r2.srp_quota_assign_id
1941 and nvl(r2.customized_flag, 'N') = 'N');
1942 end if;
1943 END IF;
1944
1945 EXCEPTION
1946 WHEN FND_API.G_EXC_ERROR THEN
1947 ROLLBACK TO Update_rate;
1948 x_return_status := FND_API.G_RET_STS_ERROR ;
1949 FND_MSG_PUB.count_and_get
1950 (p_count => x_msg_count ,
1951 p_data => x_msg_data ,
1952 p_encoded => FND_API.G_FALSE );
1953 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1954 ROLLBACK TO Update_rate;
1955 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1956 FND_MSG_PUB.count_and_get
1957 (p_count => x_msg_count ,
1958 p_data => x_msg_data ,
1959 p_encoded => FND_API.G_FALSE );
1960 WHEN OTHERS THEN
1961 ROLLBACK TO Update_rate;
1962 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1963 IF FND_MSG_PUB.check_msg_level
1964 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1965 THEN
1966 FND_MSG_PUB.add_exc_msg
1967 (G_PKG_NAME ,
1968 l_api_name );
1969 END IF;
1970 FND_MSG_PUB.count_and_get
1971 (p_count => x_msg_count ,
1972 p_data => x_msg_data ,
1973 p_encoded => FND_API.G_FALSE );
1974
1975 END update_rate;
1976
1977 PROCEDURE update_srp_rate
1978 (p_srp_quota_assign_id IN CN_SRP_QUOTA_ASSIGNS.SRP_QUOTA_ASSIGN_ID%TYPE,
1979 p_rt_quota_asgn_id IN CN_SRP_RATE_ASSIGNS.RT_QUOTA_ASGN_ID%TYPE,
1980 p_rate_sequence IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
1981 p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
1982 p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, -- changed
1983 --R12 MOAC Changes--Start
1984 p_org_id CN_RATE_TIERS.ORG_ID%TYPE,
1985 --R12 MOAC Changes--End
1986 x_return_status OUT NOCOPY VARCHAR2,
1987 x_loading_status OUT NOCOPY VARCHAR2,
1988 x_msg_count OUT NOCOPY NUMBER,
1989 x_msg_data OUT NOCOPY VARCHAR2
1990
1991 ) IS
1992
1993 l_srp_rate_assign_id CN_SRP_RATE_ASSIGNS.SRP_RATE_ASSIGN_ID%TYPE;
1994 l_object_version_number CN_SRP_RATE_ASSIGNS.OBJECT_VERSION_NUMBER%TYPE;
1995 l_rate_schedule_id CN_SRP_RATE_ASSIGNS.RATE_SCHEDULE_ID%TYPE;
1996 l_rate_tier_id CN_SRP_RATE_ASSIGNS.RATE_TIER_ID%TYPE;
1997 l_commission_amount CN_SRP_RATE_ASSIGNS.COMMISSION_AMOUNT%TYPE;
1998 l_srp_plan_assign_id CN_SRP_RATE_ASSIGNS.SRP_PLAN_ASSIGN_ID%TYPE;
1999 l_quota_id CN_SRP_RATE_ASSIGNS.QUOTA_ID%TYPE;
2000 l_api_name CONSTANT VARCHAR2(30) := 'update_srp_rate';
2001
2002 CURSOR rate_tier_info IS
2003 SELECT srp_rate_assign_id, object_version_number, commission_amount
2004 FROM cn_srp_rate_assigns
2005 WHERE srp_quota_assign_id = p_srp_quota_assign_id
2006 AND rt_quota_asgn_id = p_rt_quota_asgn_id
2007 AND rate_sequence = p_rate_sequence
2008 FOR UPDATE OF srp_rate_assign_id nowait;
2009
2010 CURSOR get_rate_tier_id IS
2011 SELECT rate_tier_id
2012 from cn_rate_tiers
2013 where rate_schedule_id = l_rate_schedule_id
2014 and rate_sequence = p_rate_sequence;
2015
2016 CURSOR get_sqa_info IS
2017 SELECT srp_plan_assign_id, quota_id
2018 from CN_SRP_QUOTA_ASSIGNS
2019 where srp_quota_assign_id = p_srp_quota_assign_id;
2020
2021 BEGIN
2022 -- this should only be called if rates are customized
2023 SAVEPOINT update_srp_rate;
2024 FND_MSG_PUB.initialize;
2025
2026 x_return_status := fnd_api.g_ret_sts_success;
2027 x_loading_status := 'CN_INSERTED';
2028
2029 OPEN rate_tier_info;
2030 FETCH rate_tier_info INTO l_srp_rate_assign_id, l_object_version_number, l_commission_amount;
2031 IF (rate_tier_info%notfound) THEN
2032 close rate_tier_info;
2033
2034 -- if updating to 0 then nothing to do
2035 if p_commission_amount = 0 then
2036 return;
2037 end if;
2038
2039 -- get rate schedule
2040 select rqa.rate_schedule_id into l_rate_schedule_id
2041 from cn_srp_quota_assigns sqa, cn_rt_quota_asgns rqa
2042 where rqa.rt_quota_asgn_id = p_rt_quota_asgn_id
2043 and sqa.quota_id = rqa.quota_id
2044 and sqa.srp_quota_assign_id = p_srp_quota_assign_id;
2045
2046 -- see if rate tier already exists in main rate table
2047 OPEN get_rate_tier_id;
2048 FETCH get_rate_tier_id into l_rate_tier_id;
2049 CLOSE get_rate_tier_id;
2050 if l_rate_tier_id is null then
2051 -- insert rate tier into main rate table
2052 cn_rate_tiers_pkg.insert_row
2053 (X_RATE_TIER_ID => l_rate_tier_id,
2054 X_RATE_SCHEDULE_ID => l_rate_schedule_id,
2055 X_COMMISSION_AMOUNT => 0, -- place holder record
2056 X_RATE_SEQUENCE => p_rate_sequence,
2057 --R12 MOAC Changes--Start
2058 X_ORG_ID => p_org_id);
2059 --R12 MOAC Changes--End
2060 end if;
2061
2062 -- get srp_quota_assigns info
2063 OPEN get_sqa_info;
2064 FETCH get_sqa_info into l_srp_plan_assign_id, l_quota_id;
2065 CLOSE get_sqa_info;
2066
2067 -- we are assigning the rate for first time
2068 select cn_srp_rate_assigns_s.NEXTVAL into l_srp_rate_assign_id from dual;
2069
2070 insert into cn_srp_rate_assigns
2071 (srp_plan_assign_id,
2072 srp_quota_assign_id,
2073 srp_rate_assign_id,
2074 quota_id,
2075 rate_schedule_id,
2076 rt_quota_asgn_id,
2077 rate_tier_id,
2078 rate_sequence,
2079 commission_amount,
2080 last_update_date,
2081 last_updated_by,
2082 last_update_login,
2083 creation_date,
2084 created_by,
2085 org_id)
2086 values
2087 (l_srp_plan_assign_id,
2088 p_srp_quota_assign_id,
2089 l_srp_rate_assign_id,
2090 l_quota_id,
2091 l_rate_schedule_id,
2092 p_rt_quota_asgn_id,
2093 l_rate_tier_id,
2094 p_rate_sequence,
2095 p_commission_amount,
2096 sysdate,
2097 fnd_global.user_id,
2098 fnd_global.login_id,
2099 sysdate,
2100 fnd_global.user_id,
2101 p_org_id);
2102
2103 create_note_bus_event
2104 (p_srp_quota_assign_id => p_srp_quota_assign_id,
2105 p_rt_quota_asgn_id => p_rt_quota_asgn_id,
2106 p_rate_sequence => p_rate_sequence,
2107 p_old_amt => 0,
2108 p_new_amt => p_commission_amount,
2109 p_key => 'c' || l_srp_rate_assign_id);
2110
2111 p_object_version_number := 1;
2112 else
2113 -- srp rate tier exists - update it
2114 close rate_tier_info;
2115
2116 if (l_object_version_number <> p_object_version_number) then
2117 fnd_message.set_name('CN', 'CN_RECORD_CHANGED');
2118 fnd_msg_pub.add;
2119 x_loading_status := 'CN_RECORD_CHANGED';
2120 raise fnd_api.g_exc_error;
2121 end if;
2122
2123 -- if updating rate to 0, delete commission rate
2124 if p_commission_amount = 0 then
2125 delete from cn_srp_rate_assigns
2126 where srp_rate_assign_id = l_srp_rate_assign_id;
2127
2128 create_note_bus_event
2129 (p_srp_quota_assign_id => p_srp_quota_assign_id,
2130 p_rt_quota_asgn_id => p_rt_quota_asgn_id,
2131 p_rate_sequence => p_rate_sequence,
2132 p_old_amt => l_commission_amount,
2133 p_new_amt => p_commission_amount,
2134 p_key => 'd' || l_srp_rate_assign_id);
2135
2136 p_object_version_number := -1;
2137 else
2138 update cn_srp_rate_assigns set
2139 COMMISSION_AMOUNT = p_commission_amount,
2140 last_update_date = sysdate,
2141 last_updated_by = fnd_global.user_id,
2142 last_update_login = fnd_global.login_id,
2143 object_version_number = object_version_number + 1
2144 WHERE srp_rate_assign_id = l_srp_rate_assign_id;
2145
2146 p_object_version_number := p_object_version_number + 1;
2147
2148 create_note_bus_event
2149 (p_srp_quota_assign_id => p_srp_quota_assign_id,
2150 p_rt_quota_asgn_id => p_rt_quota_asgn_id,
2151 p_rate_sequence => p_rate_sequence,
2152 p_old_amt => l_commission_amount,
2153 p_new_amt => p_commission_amount,
2154 p_key => 'u' || l_srp_rate_assign_id || '-' ||
2155 p_object_version_number);
2156 end if;
2157 end if;
2158
2159 EXCEPTION
2160 WHEN FND_API.G_EXC_ERROR THEN
2161 ROLLBACK TO update_srp_rate;
2162 x_return_status := FND_API.G_RET_STS_ERROR ;
2163 FND_MSG_PUB.count_and_get
2164 (p_count => x_msg_count ,
2165 p_data => x_msg_data ,
2166 p_encoded => FND_API.G_FALSE );
2167 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2168 ROLLBACK TO update_srp_rate;
2169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2170 FND_MSG_PUB.count_and_get
2171 (p_count => x_msg_count ,
2172 p_data => x_msg_data ,
2173 p_encoded => FND_API.G_FALSE );
2174 WHEN OTHERS THEN
2175 ROLLBACK TO update_srp_rate;
2176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2177 IF FND_MSG_PUB.check_msg_level
2178 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2179 THEN
2180 FND_MSG_PUB.add_exc_msg
2181 (G_PKG_NAME ,
2182 l_api_name );
2183 END IF;
2184 FND_MSG_PUB.count_and_get
2185 (p_count => x_msg_count ,
2186 p_data => x_msg_data ,
2187 p_encoded => FND_API.G_FALSE );
2188
2189 END update_srp_rate;
2190
2191 -- utility function to get the rate_tier_id when given the tier combination
2192 PROCEDURE get_rate_tier_info
2193 (p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
2194 p_rate_dim_tier_id_tbl IN NUM_TBL_TYPE ,
2195 x_rate_tier_id OUT NOCOPY CN_RATE_TIERS.RATE_TIER_ID%TYPE,
2196 x_rate_sequence OUT NOCOPY CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
2197 x_commission_amount OUT NOCOPY CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
2198 x_object_version_number OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE) IS
2199
2200 l_base NUMBER := 1;
2201 l_tier_sequence CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE;
2202 l_rate_dimension_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE;
2203 l_rate_dim_sequence CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE;
2204 l_number_tier CN_RATE_DIMENSIONS.NUMBER_TIER%TYPE;
2205 l_number_dim CN_RATE_SCHEDULES.NUMBER_DIM%TYPE;
2206
2207 dim_size_table num_tbl_type;
2208 current_tier_table num_tbl_type;
2209
2210 CURSOR dim_info(p_rate_dimension_id NUMBER) IS
2211 SELECT rsd.rate_dim_sequence, rd.number_tier
2212 FROM cn_rate_sch_dims rsd,
2213 cn_rate_dimensions rd
2214 WHERE rsd.rate_schedule_id = p_rate_schedule_id
2215 AND rsd.rate_dimension_id = p_rate_dimension_id
2216 AND rd.rate_dimension_id = p_rate_dimension_id;
2217
2218 CURSOR tier_info(p_rate_dim_tier_id NUMBER) IS
2219 SELECT tier_sequence, rate_dimension_id
2220 FROM cn_rate_dim_tiers
2221 WHERE rate_dim_tier_id = p_rate_dim_tier_id;
2222
2223 CURSOR get_tier IS
2224 SELECT rate_tier_id, nvl(commission_amount,0), object_version_number
2225 FROM cn_rate_tiers
2226 WHERE rate_schedule_id = p_rate_schedule_id
2227 AND rate_sequence = x_rate_sequence;
2228
2229 BEGIN
2230 FOR i IN p_rate_dim_tier_id_tbl.first..p_rate_dim_tier_id_tbl.last LOOP
2231 OPEN tier_info(p_rate_dim_tier_id_tbl(i));
2232 FETCH tier_info INTO l_tier_sequence, l_rate_dimension_id;
2233 IF (tier_info%notfound) THEN
2234 CLOSE tier_info;
2235 RAISE no_data_found;
2236 END IF;
2237 CLOSE tier_info;
2238
2239 OPEN dim_info(l_rate_dimension_id);
2240 FETCH dim_info INTO l_rate_dim_sequence, l_number_tier;
2241 IF (dim_info%notfound) THEN
2242 CLOSE dim_info;
2243 RAISE no_data_found;
2244 END IF;
2245 CLOSE dim_info;
2246
2247 current_tier_table(l_rate_dim_sequence) := l_tier_sequence;
2248 dim_size_table(l_rate_dim_sequence) := l_number_tier;
2249 END LOOP;
2250
2251 l_number_dim := dim_size_table.COUNT;
2252 x_rate_sequence := 0;
2253 FOR i IN REVERSE 1..l_number_dim LOOP
2254 IF (i = l_number_dim) THEN
2255 x_rate_sequence := x_rate_sequence + current_tier_table(i);
2256 ELSE
2257 x_rate_sequence := x_rate_sequence + (current_tier_table(i) - 1) * l_base;
2258 END IF;
2259
2260 l_base := l_base * dim_size_table(i);
2261 END LOOP;
2262
2263 x_rate_tier_id := null;
2264 x_commission_amount := 0;
2265 x_object_version_number := 0;
2266 open get_tier;
2267 fetch get_tier into x_rate_tier_id, x_commission_amount, x_object_version_number;
2268 close get_tier;
2269
2270 END get_rate_tier_info;
2271
2272
2273 procedure tokenizer ( iStart IN NUMBER,
2274 sPattern in VARCHAR2,
2275 sBuffer in VARCHAR2,
2276 sResult OUT NOCOPY VARCHAR2,
2277 iNextPos OUT NOCOPY NUMBER)
2278 AS
2279 nPos1 number;
2280 nPos2 number;
2281 BEGIN
2282 nPos1 := Instr (sBuffer ,sPattern ,iStart);
2283 IF nPos1 = 0 then
2284 sResult := NULL ;
2285 ELSE
2286 nPos2 := Instr (sBuffer ,sPattern ,nPos1 + 1);
2287 IF nPos2 = 0 then
2288 sResult := Rtrim(Ltrim(Substr(sBuffer ,nPos1+1)));
2289 iNextPos := nPos2;
2290 else
2291 sResult := Substr(sBuffer ,nPos1 + 1 , nPos2 - nPos1 - 1);
2292 iNextPos := nPos2;
2293 END IF;
2294 END IF;
2295 END tokenizer ;
2296
2297
2298 Function get_sequence(x_schedule_id
2299 CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,sbuf varchar2) RETURN number
2300 is
2301 sepr varchar2(1);
2302 sres varchar2(200);
2303 pos number;
2304 istart number;
2305
2306 x_rate_tier_id CN_RATE_TIERS.RATE_TIER_ID%TYPE;
2307 x_rate_sequence CN_RATE_TIERS.RATE_SEQUENCE%TYPE;
2308 x_commission_amount CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE;
2309 x_object_version_number CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE;
2310 l_number_dim NUMBER;
2311
2312 --type tbl is table of number INDEX BY BINARY_INTEGER;
2313 l_tbl APPS.CN_MULTI_RATE_SCHEDULES_PVT.NUM_TBL_TYPE;
2314
2315 begin
2316 -- if rate schedule is 1-dimensional, then don't need to do lot of fancy parsing
2317 select number_dim into l_number_dim from cn_rate_schedules
2318 where rate_schedule_id = x_schedule_id;
2319
2320 if l_number_dim = 1 then
2321 -- strip off extra commas
2322 sres := replace(sbuf, ',');
2323 select rdt.tier_sequence into x_rate_sequence
2324 from cn_rate_sch_dims rsd, cn_rate_dim_tiers rdt
2325 where rsd.rate_schedule_id = x_schedule_id
2326 and rsd.rate_dimension_id = rdt.rate_dimension_id
2327 and rdt.rate_dim_tier_id = sres;
2328 return x_rate_sequence;
2329 end if;
2330
2331 --sbuf := ',10665,10667,10668';
2332 sepr := ',';
2333 istart := 1;
2334 tokenizer (istart ,sepr,sbuf,sres,pos);
2335 if (pos <> 0) then
2336 l_tbl(l_tbl.count+1) := sres;
2337 -- dbms_output.put_line (l_tbl(l_tbl.count));
2338 end if;
2339 while (pos <> 0)
2340 loop
2341 istart := pos;
2342 tokenizer (istart ,sepr,sbuf,sres,pos );
2343 --insert into l_tbl((sres));
2344 l_tbl(l_tbl.count+1) := sres;
2345 -- dbms_output.put_line (l_tbl(l_tbl.count));
2346 end loop;
2347
2348 get_rate_tier_info(x_schedule_id,l_tbl,x_rate_tier_id,x_rate_sequence,x_commission_amount,x_object_version_number
2349 );
2350 return x_rate_sequence;
2351 END get_sequence;
2352
2353 PROCEDURE update_comm_rate(p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
2354 x_result_tbl IN comm_tbl_type,
2355 --R12 MOAC Changes--Start
2356 p_org_id IN CN_RATE_TIERS.ORG_ID%TYPE --new
2357 --R12 MOAC Changes--End
2358 )
2359 IS
2360 x_ovn number;
2361
2362 BEGIN
2363
2364 FOR Lcntr IN x_result_tbl.first..x_result_tbl.last
2365 LOOP
2366 x_ovn := x_result_tbl(Lcntr).p_object_version_number;
2367 update_rate(p_rate_schedule_id ,x_result_tbl(Lcntr).p_rate_sequence,x_result_tbl(Lcntr).p_commission_amount,x_ovn,
2368 --R12 MOAC Changes--Start
2369 p_org_id);
2370 --R12 MOAC Changes--End
2371 END LOOP;
2372
2373 END;
2374
2375
2376 PROCEDURE duplicate_rate_Schedule
2377 (p_api_version IN NUMBER ,
2378 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2379 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2380 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2381 p_name IN OUT NOCOPY CN_RATE_SCHEDULES.NAME%TYPE ,
2382 p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE, --new
2383 --R12 MOAC Changes--End
2384 p_rate_schedule_id IN OUT NOCOPY CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE, --changed
2385 p_number_dim IN CN_RATE_SCHEDULES.NUMBER_DIM%TYPE,
2386 p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE,
2387 x_return_status OUT NOCOPY VARCHAR2,
2388 x_msg_count OUT NOCOPY NUMBER,
2389 x_msg_data OUT NOCOPY VARCHAR2
2390
2391 )
2392 IS
2393
2394 CURSOR rate_sch_dim IS
2395 select * from cn_rate_sch_dims_all
2396 where rate_schedule_id = p_rate_schedule_id
2397 and org_id = p_org_id;
2398
2399 cursor rate_dim_info(l_dim_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE)
2400 is
2401 select * from cn_rate_dimensions_all
2402 where rate_dimension_id = l_dim_id
2403 and org_id = p_org_id;
2404
2405
2406 CURSOR rate_sch_tiers_info(l_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE) IS
2407 select * from cn_rate_tiers_all
2408 where rate_schedule_id = l_rate_schedule_id
2409 and org_id = p_org_id;
2410
2411
2412
2413 l_new_name CN_RATE_SCHEDULES.NAME%TYPE;
2414 l_tbl_type dims_tbl_type;
2415 --l_rate_sch_rec rate_sch_dim%ROWTYPE;
2416 l_rate_dim_info_rec rate_dim_info%ROWTYPE;
2417 l_rate_tier_rec rate_sch_tiers_info%ROWTYPE ;
2418 old_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE;
2419
2420
2421
2422 next_row NUMBER ;
2423
2424
2425
2426 begin
2427
2428
2429
2430
2431 old_rate_schedule_id:=p_rate_schedule_id;
2432 next_row:=1;
2433
2434
2435 select name into p_name from cn_rate_schedules_all where rate_schedule_id = p_rate_schedule_id
2436 and org_id = p_org_id;
2437
2438
2439 l_new_name:=p_name;
2440 CN_PLANCOPY_UTIL_PVT.get_unique_name_for_component(p_rate_schedule_id,
2441 p_org_id,'RATETABLE',null,null,l_new_name,
2442 x_return_status,x_msg_count,
2443 x_msg_data);
2444
2445
2446
2447
2448 --open rate_sch_dim;
2449 for l_rate_sch_rec in rate_sch_dim
2450
2451
2452 LOOP
2453
2454
2455
2456
2457 l_tbl_type(next_row).rate_sch_dim_id := l_rate_sch_rec.rate_sch_dim_id;
2458
2459 l_tbl_type(next_row).rate_dimension_id := l_rate_sch_rec.rate_dimension_id;
2460
2461 l_tbl_type(next_row).rate_schedule_id := l_rate_sch_rec.rate_schedule_id;
2462
2463 l_tbl_type(next_row).rate_dim_sequence := l_rate_sch_rec.rate_dim_sequence;
2464
2465
2466 open rate_dim_info(l_rate_sch_rec.rate_dimension_id);
2467
2468 fetch rate_dim_info into l_rate_dim_info_rec;
2469
2470 l_tbl_type(next_row).rate_dim_name := l_rate_dim_info_rec.name;
2471
2472 l_tbl_type(next_row).number_tier := l_rate_dim_info_rec.number_tier;
2473
2474 l_tbl_type(next_row).dim_unit_code := l_rate_dim_info_rec.dim_unit_code;
2475
2476 l_tbl_type(next_row).object_version_number := 1;
2477
2478
2479 close rate_dim_info;
2480
2481 next_row:=next_row+1;
2482
2483
2484
2485
2486 END LOOP;
2487
2488
2489
2490
2491 p_rate_schedule_id := null;
2492 Create_Schedule (
2493
2494 p_api_version,
2495 p_init_msg_list ,
2496 p_commit ,
2497 p_validation_level ,
2498 l_new_name ,
2499 p_commission_unit_code ,
2500 p_number_dim , -- not used
2501 l_tbl_type ,
2502 --R12 MOAC Changes--Start
2503 p_org_id , --new
2504 p_rate_schedule_id , --changed
2505 x_return_status,
2506 x_msg_count,
2507 x_msg_data
2508 --R12 MOAC Changes--End
2509 );
2510
2511
2512
2513 p_name:= l_new_name;
2514
2515
2516
2517
2518 for l_rate_tier in rate_sch_tiers_info(old_rate_schedule_id)
2519 LOOP
2520
2521 update_rate(p_rate_schedule_id ,l_rate_tier.rate_sequence,l_rate_tier.commission_amount,l_rate_tier.object_version_number,
2522 --R12 MOAC Changes--Start
2523 p_org_id);
2524
2525 end loop;
2526
2527 commit;
2528
2529 END duplicate_rate_Schedule;
2530
2531
2532
2533 END CN_MULTI_RATE_SCHEDULES_PVT;