[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.12010000.2 2010/02/24 15:46:08 rajukum 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 --Fix for Bug 9401416
1905 create_note_bus_event (0,
1906 0 ,
1907 p_rate_sequence ,
1908 0,
1909 p_commission_amount,
1910 null ,
1911 p_rate_schedule_id );
1912
1913 -- don't need to create sra...if customized, leave as 0 (don't create)
1914 -- if non-customized, don't create (bug 3204833)
1915 end if;
1916 ELSE
1917 CLOSE rate_tier_info;
1918
1919 -- see if amt changed
1920 if p_commission_amount <> l_commission_amount then
1921
1922 -- lock and update the record
1923 cn_rate_tiers_pkg.lock_row
1924 (X_RATE_TIER_ID => l_rate_tier_id,
1925 X_OBJECT_VERSION_NUMBER => p_object_version_number);
1926
1927 cn_rate_tiers_pkg.update_row
1928 (X_RATE_TIER_ID => l_rate_tier_id,
1929 X_RATE_SCHEDULE_ID => p_rate_schedule_id,
1930 X_COMMISSION_AMOUNT => p_commission_amount,
1931 X_RATE_SEQUENCE => p_rate_sequence,
1932 X_OBJECT_VERSION_NUMBER => p_object_version_number);
1933
1934 create_note_bus_event (0,
1935 0 ,
1936 p_rate_sequence ,
1937 l_commission_amount,
1938 p_commission_amount,
1939 null ,
1940 p_rate_schedule_id );
1941
1942 -- sync up srp rate assignments where srps don't have customized rates
1943 update cn_srp_rate_assigns r
1944 set commission_amount = p_commission_amount
1945 where rate_schedule_id = p_rate_schedule_id
1946 and rate_sequence = p_rate_sequence
1947 and exists
1948 (select 1 from cn_srp_quota_assigns r2
1949 where r.srp_quota_assign_id = r2.srp_quota_assign_id
1950 and nvl(r2.customized_flag, 'N') = 'N');
1951 end if;
1952 END IF;
1953
1954 EXCEPTION
1955 WHEN FND_API.G_EXC_ERROR THEN
1956 ROLLBACK TO Update_rate;
1957 x_return_status := FND_API.G_RET_STS_ERROR ;
1958 FND_MSG_PUB.count_and_get
1959 (p_count => x_msg_count ,
1960 p_data => x_msg_data ,
1961 p_encoded => FND_API.G_FALSE );
1962 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1963 ROLLBACK TO Update_rate;
1964 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1965 FND_MSG_PUB.count_and_get
1966 (p_count => x_msg_count ,
1967 p_data => x_msg_data ,
1968 p_encoded => FND_API.G_FALSE );
1969 WHEN OTHERS THEN
1970 ROLLBACK TO Update_rate;
1971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1972 IF FND_MSG_PUB.check_msg_level
1973 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1974 THEN
1975 FND_MSG_PUB.add_exc_msg
1976 (G_PKG_NAME ,
1977 l_api_name );
1978 END IF;
1979 FND_MSG_PUB.count_and_get
1980 (p_count => x_msg_count ,
1981 p_data => x_msg_data ,
1982 p_encoded => FND_API.G_FALSE );
1983
1984 END update_rate;
1985
1986 PROCEDURE update_srp_rate
1987 (p_srp_quota_assign_id IN CN_SRP_QUOTA_ASSIGNS.SRP_QUOTA_ASSIGN_ID%TYPE,
1988 p_rt_quota_asgn_id IN CN_SRP_RATE_ASSIGNS.RT_QUOTA_ASGN_ID%TYPE,
1989 p_rate_sequence IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
1990 p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
1991 p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, -- changed
1992 --R12 MOAC Changes--Start
1993 p_org_id CN_RATE_TIERS.ORG_ID%TYPE,
1994 --R12 MOAC Changes--End
1995 x_return_status OUT NOCOPY VARCHAR2,
1996 x_loading_status OUT NOCOPY VARCHAR2,
1997 x_msg_count OUT NOCOPY NUMBER,
1998 x_msg_data OUT NOCOPY VARCHAR2
1999
2000 ) IS
2001
2002 l_srp_rate_assign_id CN_SRP_RATE_ASSIGNS.SRP_RATE_ASSIGN_ID%TYPE;
2003 l_object_version_number CN_SRP_RATE_ASSIGNS.OBJECT_VERSION_NUMBER%TYPE;
2004 l_rate_schedule_id CN_SRP_RATE_ASSIGNS.RATE_SCHEDULE_ID%TYPE;
2005 l_rate_tier_id CN_SRP_RATE_ASSIGNS.RATE_TIER_ID%TYPE;
2006 l_commission_amount CN_SRP_RATE_ASSIGNS.COMMISSION_AMOUNT%TYPE;
2007 l_srp_plan_assign_id CN_SRP_RATE_ASSIGNS.SRP_PLAN_ASSIGN_ID%TYPE;
2008 l_quota_id CN_SRP_RATE_ASSIGNS.QUOTA_ID%TYPE;
2009 l_api_name CONSTANT VARCHAR2(30) := 'update_srp_rate';
2010
2011 CURSOR rate_tier_info IS
2012 SELECT srp_rate_assign_id, object_version_number, commission_amount
2013 FROM cn_srp_rate_assigns
2014 WHERE srp_quota_assign_id = p_srp_quota_assign_id
2015 AND rt_quota_asgn_id = p_rt_quota_asgn_id
2016 AND rate_sequence = p_rate_sequence
2017 FOR UPDATE OF srp_rate_assign_id nowait;
2018
2019 CURSOR get_rate_tier_id IS
2020 SELECT rate_tier_id
2021 from cn_rate_tiers
2022 where rate_schedule_id = l_rate_schedule_id
2023 and rate_sequence = p_rate_sequence;
2024
2025 CURSOR get_sqa_info IS
2026 SELECT srp_plan_assign_id, quota_id
2027 from CN_SRP_QUOTA_ASSIGNS
2028 where srp_quota_assign_id = p_srp_quota_assign_id;
2029
2030 BEGIN
2031 -- this should only be called if rates are customized
2032 SAVEPOINT update_srp_rate;
2033 FND_MSG_PUB.initialize;
2034
2035 x_return_status := fnd_api.g_ret_sts_success;
2036 x_loading_status := 'CN_INSERTED';
2037
2038 OPEN rate_tier_info;
2039 FETCH rate_tier_info INTO l_srp_rate_assign_id, l_object_version_number, l_commission_amount;
2040 IF (rate_tier_info%notfound) THEN
2041 close rate_tier_info;
2042
2043 -- if updating to 0 then nothing to do
2044 if p_commission_amount = 0 then
2045 return;
2046 end if;
2047
2048 -- get rate schedule
2049 select rqa.rate_schedule_id into l_rate_schedule_id
2050 from cn_srp_quota_assigns sqa, cn_rt_quota_asgns rqa
2051 where rqa.rt_quota_asgn_id = p_rt_quota_asgn_id
2052 and sqa.quota_id = rqa.quota_id
2053 and sqa.srp_quota_assign_id = p_srp_quota_assign_id;
2054
2055 -- see if rate tier already exists in main rate table
2056 OPEN get_rate_tier_id;
2057 FETCH get_rate_tier_id into l_rate_tier_id;
2058 CLOSE get_rate_tier_id;
2059 if l_rate_tier_id is null then
2060 -- insert rate tier into main rate table
2061 cn_rate_tiers_pkg.insert_row
2062 (X_RATE_TIER_ID => l_rate_tier_id,
2063 X_RATE_SCHEDULE_ID => l_rate_schedule_id,
2064 X_COMMISSION_AMOUNT => 0, -- place holder record
2065 X_RATE_SEQUENCE => p_rate_sequence,
2066 --R12 MOAC Changes--Start
2067 X_ORG_ID => p_org_id);
2068 --R12 MOAC Changes--End
2069 end if;
2070
2071 -- get srp_quota_assigns info
2072 OPEN get_sqa_info;
2073 FETCH get_sqa_info into l_srp_plan_assign_id, l_quota_id;
2074 CLOSE get_sqa_info;
2075
2076 -- we are assigning the rate for first time
2077 select cn_srp_rate_assigns_s.NEXTVAL into l_srp_rate_assign_id from dual;
2078
2079 insert into cn_srp_rate_assigns
2080 (srp_plan_assign_id,
2081 srp_quota_assign_id,
2082 srp_rate_assign_id,
2083 quota_id,
2084 rate_schedule_id,
2085 rt_quota_asgn_id,
2086 rate_tier_id,
2087 rate_sequence,
2088 commission_amount,
2089 last_update_date,
2090 last_updated_by,
2091 last_update_login,
2092 creation_date,
2093 created_by,
2094 org_id)
2095 values
2096 (l_srp_plan_assign_id,
2097 p_srp_quota_assign_id,
2098 l_srp_rate_assign_id,
2099 l_quota_id,
2100 l_rate_schedule_id,
2101 p_rt_quota_asgn_id,
2102 l_rate_tier_id,
2103 p_rate_sequence,
2104 p_commission_amount,
2105 sysdate,
2106 fnd_global.user_id,
2107 fnd_global.login_id,
2108 sysdate,
2109 fnd_global.user_id,
2110 p_org_id);
2111
2112 create_note_bus_event
2113 (p_srp_quota_assign_id => p_srp_quota_assign_id,
2114 p_rt_quota_asgn_id => p_rt_quota_asgn_id,
2115 p_rate_sequence => p_rate_sequence,
2116 p_old_amt => 0,
2117 p_new_amt => p_commission_amount,
2118 p_key => 'c' || l_srp_rate_assign_id);
2119
2120 p_object_version_number := 1;
2121 else
2122 -- srp rate tier exists - update it
2123 close rate_tier_info;
2124
2125 if (l_object_version_number <> p_object_version_number) then
2126 fnd_message.set_name('CN', 'CN_RECORD_CHANGED');
2127 fnd_msg_pub.add;
2128 x_loading_status := 'CN_RECORD_CHANGED';
2129 raise fnd_api.g_exc_error;
2130 end if;
2131
2132 -- if updating rate to 0, delete commission rate
2133 if p_commission_amount = 0 then
2134 delete from cn_srp_rate_assigns
2135 where srp_rate_assign_id = l_srp_rate_assign_id;
2136
2137 create_note_bus_event
2138 (p_srp_quota_assign_id => p_srp_quota_assign_id,
2139 p_rt_quota_asgn_id => p_rt_quota_asgn_id,
2140 p_rate_sequence => p_rate_sequence,
2141 p_old_amt => l_commission_amount,
2142 p_new_amt => p_commission_amount,
2143 p_key => 'd' || l_srp_rate_assign_id);
2144
2145 p_object_version_number := -1;
2146 else
2147 update cn_srp_rate_assigns set
2148 COMMISSION_AMOUNT = p_commission_amount,
2149 last_update_date = sysdate,
2150 last_updated_by = fnd_global.user_id,
2151 last_update_login = fnd_global.login_id,
2152 object_version_number = object_version_number + 1
2153 WHERE srp_rate_assign_id = l_srp_rate_assign_id;
2154
2155 p_object_version_number := p_object_version_number + 1;
2156
2157 create_note_bus_event
2158 (p_srp_quota_assign_id => p_srp_quota_assign_id,
2159 p_rt_quota_asgn_id => p_rt_quota_asgn_id,
2160 p_rate_sequence => p_rate_sequence,
2161 p_old_amt => l_commission_amount,
2162 p_new_amt => p_commission_amount,
2163 p_key => 'u' || l_srp_rate_assign_id || '-' ||
2164 p_object_version_number);
2165 end if;
2166 end if;
2167
2168 EXCEPTION
2169 WHEN FND_API.G_EXC_ERROR THEN
2170 ROLLBACK TO update_srp_rate;
2171 x_return_status := FND_API.G_RET_STS_ERROR ;
2172 FND_MSG_PUB.count_and_get
2173 (p_count => x_msg_count ,
2174 p_data => x_msg_data ,
2175 p_encoded => FND_API.G_FALSE );
2176 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2177 ROLLBACK TO update_srp_rate;
2178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2179 FND_MSG_PUB.count_and_get
2180 (p_count => x_msg_count ,
2181 p_data => x_msg_data ,
2182 p_encoded => FND_API.G_FALSE );
2183 WHEN OTHERS THEN
2184 ROLLBACK TO update_srp_rate;
2185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2186 IF FND_MSG_PUB.check_msg_level
2187 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2188 THEN
2189 FND_MSG_PUB.add_exc_msg
2190 (G_PKG_NAME ,
2191 l_api_name );
2192 END IF;
2193 FND_MSG_PUB.count_and_get
2194 (p_count => x_msg_count ,
2195 p_data => x_msg_data ,
2196 p_encoded => FND_API.G_FALSE );
2197
2198 END update_srp_rate;
2199
2200 -- utility function to get the rate_tier_id when given the tier combination
2201 PROCEDURE get_rate_tier_info
2202 (p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
2203 p_rate_dim_tier_id_tbl IN NUM_TBL_TYPE ,
2204 x_rate_tier_id OUT NOCOPY CN_RATE_TIERS.RATE_TIER_ID%TYPE,
2205 x_rate_sequence OUT NOCOPY CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
2206 x_commission_amount OUT NOCOPY CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
2207 x_object_version_number OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE) IS
2208
2209 l_base NUMBER := 1;
2210 l_tier_sequence CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE;
2211 l_rate_dimension_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE;
2212 l_rate_dim_sequence CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE;
2213 l_number_tier CN_RATE_DIMENSIONS.NUMBER_TIER%TYPE;
2214 l_number_dim CN_RATE_SCHEDULES.NUMBER_DIM%TYPE;
2215
2216 dim_size_table num_tbl_type;
2217 current_tier_table num_tbl_type;
2218
2219 CURSOR dim_info(p_rate_dimension_id NUMBER) IS
2220 SELECT rsd.rate_dim_sequence, rd.number_tier
2221 FROM cn_rate_sch_dims rsd,
2222 cn_rate_dimensions rd
2223 WHERE rsd.rate_schedule_id = p_rate_schedule_id
2224 AND rsd.rate_dimension_id = p_rate_dimension_id
2225 AND rd.rate_dimension_id = p_rate_dimension_id;
2226
2227 CURSOR tier_info(p_rate_dim_tier_id NUMBER) IS
2228 SELECT tier_sequence, rate_dimension_id
2229 FROM cn_rate_dim_tiers
2230 WHERE rate_dim_tier_id = p_rate_dim_tier_id;
2231
2232 CURSOR get_tier IS
2233 SELECT rate_tier_id, nvl(commission_amount,0), object_version_number
2234 FROM cn_rate_tiers
2235 WHERE rate_schedule_id = p_rate_schedule_id
2236 AND rate_sequence = x_rate_sequence;
2237
2238 BEGIN
2239 FOR i IN p_rate_dim_tier_id_tbl.first..p_rate_dim_tier_id_tbl.last LOOP
2240 OPEN tier_info(p_rate_dim_tier_id_tbl(i));
2241 FETCH tier_info INTO l_tier_sequence, l_rate_dimension_id;
2242 IF (tier_info%notfound) THEN
2243 CLOSE tier_info;
2244 RAISE no_data_found;
2245 END IF;
2246 CLOSE tier_info;
2247
2248 OPEN dim_info(l_rate_dimension_id);
2249 FETCH dim_info INTO l_rate_dim_sequence, l_number_tier;
2250 IF (dim_info%notfound) THEN
2251 CLOSE dim_info;
2252 RAISE no_data_found;
2253 END IF;
2254 CLOSE dim_info;
2255
2256 current_tier_table(l_rate_dim_sequence) := l_tier_sequence;
2257 dim_size_table(l_rate_dim_sequence) := l_number_tier;
2258 END LOOP;
2259
2260 l_number_dim := dim_size_table.COUNT;
2261 x_rate_sequence := 0;
2262 FOR i IN REVERSE 1..l_number_dim LOOP
2263 IF (i = l_number_dim) THEN
2264 x_rate_sequence := x_rate_sequence + current_tier_table(i);
2265 ELSE
2266 x_rate_sequence := x_rate_sequence + (current_tier_table(i) - 1) * l_base;
2267 END IF;
2268
2269 l_base := l_base * dim_size_table(i);
2270 END LOOP;
2271
2272 x_rate_tier_id := null;
2273 x_commission_amount := 0;
2274 x_object_version_number := 0;
2275 open get_tier;
2276 fetch get_tier into x_rate_tier_id, x_commission_amount, x_object_version_number;
2277 close get_tier;
2278
2279 END get_rate_tier_info;
2280
2281
2282 procedure tokenizer ( iStart IN NUMBER,
2283 sPattern in VARCHAR2,
2284 sBuffer in VARCHAR2,
2285 sResult OUT NOCOPY VARCHAR2,
2286 iNextPos OUT NOCOPY NUMBER)
2287 AS
2288 nPos1 number;
2289 nPos2 number;
2290 BEGIN
2291 nPos1 := Instr (sBuffer ,sPattern ,iStart);
2292 IF nPos1 = 0 then
2293 sResult := NULL ;
2294 ELSE
2295 nPos2 := Instr (sBuffer ,sPattern ,nPos1 + 1);
2296 IF nPos2 = 0 then
2297 sResult := Rtrim(Ltrim(Substr(sBuffer ,nPos1+1)));
2298 iNextPos := nPos2;
2299 else
2300 sResult := Substr(sBuffer ,nPos1 + 1 , nPos2 - nPos1 - 1);
2301 iNextPos := nPos2;
2302 END IF;
2303 END IF;
2304 END tokenizer ;
2305
2306
2307 Function get_sequence(x_schedule_id
2308 CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,sbuf varchar2) RETURN number
2309 is
2310 sepr varchar2(1);
2311 sres varchar2(200);
2312 pos number;
2313 istart number;
2314
2315 x_rate_tier_id CN_RATE_TIERS.RATE_TIER_ID%TYPE;
2316 x_rate_sequence CN_RATE_TIERS.RATE_SEQUENCE%TYPE;
2317 x_commission_amount CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE;
2318 x_object_version_number CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE;
2319 l_number_dim NUMBER;
2320
2321 --type tbl is table of number INDEX BY BINARY_INTEGER;
2322 l_tbl APPS.CN_MULTI_RATE_SCHEDULES_PVT.NUM_TBL_TYPE;
2323
2324 begin
2325 -- if rate schedule is 1-dimensional, then don't need to do lot of fancy parsing
2326 select number_dim into l_number_dim from cn_rate_schedules
2327 where rate_schedule_id = x_schedule_id;
2328
2329 if l_number_dim = 1 then
2330 -- strip off extra commas
2331 sres := replace(sbuf, ',');
2332 select rdt.tier_sequence into x_rate_sequence
2333 from cn_rate_sch_dims rsd, cn_rate_dim_tiers rdt
2334 where rsd.rate_schedule_id = x_schedule_id
2335 and rsd.rate_dimension_id = rdt.rate_dimension_id
2336 and rdt.rate_dim_tier_id = sres;
2337 return x_rate_sequence;
2338 end if;
2339
2340 --sbuf := ',10665,10667,10668';
2341 sepr := ',';
2342 istart := 1;
2343 tokenizer (istart ,sepr,sbuf,sres,pos);
2344 if (pos <> 0) then
2345 l_tbl(l_tbl.count+1) := sres;
2346 -- dbms_output.put_line (l_tbl(l_tbl.count));
2347 end if;
2348 while (pos <> 0)
2349 loop
2350 istart := pos;
2351 tokenizer (istart ,sepr,sbuf,sres,pos );
2352 --insert into l_tbl((sres));
2353 l_tbl(l_tbl.count+1) := sres;
2354 -- dbms_output.put_line (l_tbl(l_tbl.count));
2355 end loop;
2356
2357 get_rate_tier_info(x_schedule_id,l_tbl,x_rate_tier_id,x_rate_sequence,x_commission_amount,x_object_version_number
2358 );
2359 return x_rate_sequence;
2360 END get_sequence;
2361
2362 PROCEDURE update_comm_rate(p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
2363 x_result_tbl IN comm_tbl_type,
2364 --R12 MOAC Changes--Start
2365 p_org_id IN CN_RATE_TIERS.ORG_ID%TYPE --new
2366 --R12 MOAC Changes--End
2367 )
2368 IS
2369 x_ovn number;
2370
2371 BEGIN
2372
2373 FOR Lcntr IN x_result_tbl.first..x_result_tbl.last
2374 LOOP
2375 x_ovn := x_result_tbl(Lcntr).p_object_version_number;
2376 update_rate(p_rate_schedule_id ,x_result_tbl(Lcntr).p_rate_sequence,x_result_tbl(Lcntr).p_commission_amount,x_ovn,
2377 --R12 MOAC Changes--Start
2378 p_org_id);
2379 --R12 MOAC Changes--End
2380 END LOOP;
2381
2382 END;
2383
2384
2385 PROCEDURE duplicate_rate_Schedule
2386 (p_api_version IN NUMBER ,
2387 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2388 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2389 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2390 p_name IN OUT NOCOPY CN_RATE_SCHEDULES.NAME%TYPE ,
2391 p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE, --new
2392 --R12 MOAC Changes--End
2393 p_rate_schedule_id IN OUT NOCOPY CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE, --changed
2394 p_number_dim IN CN_RATE_SCHEDULES.NUMBER_DIM%TYPE,
2395 p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE,
2396 x_return_status OUT NOCOPY VARCHAR2,
2397 x_msg_count OUT NOCOPY NUMBER,
2398 x_msg_data OUT NOCOPY VARCHAR2
2399
2400 )
2401 IS
2402
2403 CURSOR rate_sch_dim IS
2404 select * from cn_rate_sch_dims_all
2405 where rate_schedule_id = p_rate_schedule_id
2406 and org_id = p_org_id;
2407
2408 cursor rate_dim_info(l_dim_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE)
2409 is
2410 select * from cn_rate_dimensions_all
2411 where rate_dimension_id = l_dim_id
2412 and org_id = p_org_id;
2413
2414
2415 CURSOR rate_sch_tiers_info(l_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE) IS
2416 select * from cn_rate_tiers_all
2417 where rate_schedule_id = l_rate_schedule_id
2418 and org_id = p_org_id;
2419
2420
2421
2422 l_new_name CN_RATE_SCHEDULES.NAME%TYPE;
2423 l_tbl_type dims_tbl_type;
2424 --l_rate_sch_rec rate_sch_dim%ROWTYPE;
2425 l_rate_dim_info_rec rate_dim_info%ROWTYPE;
2426 l_rate_tier_rec rate_sch_tiers_info%ROWTYPE ;
2427 old_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE;
2428
2429
2430
2431 next_row NUMBER ;
2432
2433
2434
2435 begin
2436
2437
2438
2439
2440 old_rate_schedule_id:=p_rate_schedule_id;
2441 next_row:=1;
2442
2443
2444 select name into p_name from cn_rate_schedules_all where rate_schedule_id = p_rate_schedule_id
2445 and org_id = p_org_id;
2446
2447
2448 l_new_name:=p_name;
2449 CN_PLANCOPY_UTIL_PVT.get_unique_name_for_component(p_rate_schedule_id,
2450 p_org_id,'RATETABLE',null,null,l_new_name,
2451 x_return_status,x_msg_count,
2452 x_msg_data);
2453
2454
2455
2456
2457 --open rate_sch_dim;
2458 for l_rate_sch_rec in rate_sch_dim
2459
2460
2461 LOOP
2462
2463
2464
2465
2466 l_tbl_type(next_row).rate_sch_dim_id := l_rate_sch_rec.rate_sch_dim_id;
2467
2468 l_tbl_type(next_row).rate_dimension_id := l_rate_sch_rec.rate_dimension_id;
2469
2470 l_tbl_type(next_row).rate_schedule_id := l_rate_sch_rec.rate_schedule_id;
2471
2472 l_tbl_type(next_row).rate_dim_sequence := l_rate_sch_rec.rate_dim_sequence;
2473
2474
2475 open rate_dim_info(l_rate_sch_rec.rate_dimension_id);
2476
2477 fetch rate_dim_info into l_rate_dim_info_rec;
2478
2479 l_tbl_type(next_row).rate_dim_name := l_rate_dim_info_rec.name;
2480
2481 l_tbl_type(next_row).number_tier := l_rate_dim_info_rec.number_tier;
2482
2483 l_tbl_type(next_row).dim_unit_code := l_rate_dim_info_rec.dim_unit_code;
2484
2485 l_tbl_type(next_row).object_version_number := 1;
2486
2487
2488 close rate_dim_info;
2489
2490 next_row:=next_row+1;
2491
2492
2493
2494
2495 END LOOP;
2496
2497
2498
2499
2500 p_rate_schedule_id := null;
2501 Create_Schedule (
2502
2503 p_api_version,
2504 p_init_msg_list ,
2505 p_commit ,
2506 p_validation_level ,
2507 l_new_name ,
2508 p_commission_unit_code ,
2509 p_number_dim , -- not used
2510 l_tbl_type ,
2511 --R12 MOAC Changes--Start
2512 p_org_id , --new
2513 p_rate_schedule_id , --changed
2514 x_return_status,
2515 x_msg_count,
2516 x_msg_data
2517 --R12 MOAC Changes--End
2518 );
2519
2520
2521
2522 p_name:= l_new_name;
2523
2524
2525
2526
2527 for l_rate_tier in rate_sch_tiers_info(old_rate_schedule_id)
2528 LOOP
2529
2530 update_rate(p_rate_schedule_id ,l_rate_tier.rate_sequence,l_rate_tier.commission_amount,l_rate_tier.object_version_number,
2531 --R12 MOAC Changes--Start
2532 p_org_id);
2533
2534 end loop;
2535
2536 commit;
2537
2538 END duplicate_rate_Schedule;
2539
2540
2541
2542 END CN_MULTI_RATE_SCHEDULES_PVT;