[Home] [Help]
PACKAGE BODY: APPS.CN_RT_QUOTA_ASGN_PVT
Source
1 PACKAGE BODY cn_rt_quota_asgn_pvt AS
2 /*$Header: cnvrtqab.pls 120.5 2006/07/06 10:56:34 chanthon ship $*/
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CN_RT_QUOTA_ASGN_PVT';
4
5 --------------------------------------------------------------------------------+
6 -- Procedure Name Insert Node Record
7 --------------------------------------------------------------------------------+
8 PROCEDURE insert_formula_rate_tables (
9 p_quota_id IN NUMBER,
10 p_calc_formula_id IN NUMBER,
11 p_rate_tables IN OUT NOCOPY rt_quota_asgn_tbl_type
12 )
13 IS
14 CURSOR rate_formula_date_curs
15 IS
16 SELECT rf.start_date,
17 rf.end_date,
18 rf.rate_schedule_id,
19 rs.NAME,
20 fml.NAME calc_formula_name
21 FROM cn_rt_formula_asgns rf,
22 cn_rate_schedules_all rs,
23 cn_calc_formulas fml
24 WHERE fml.calc_formula_id = p_calc_formula_id AND rf.rate_schedule_id = rs.rate_schedule_id AND fml.calc_formula_id = rf.calc_formula_id;
25
26 l_rec rt_quota_asgn_rec_type;
27 rt_date rate_formula_date_curs%ROWTYPE;
28 l_quota_start_date DATE := NULL;
29 l_quota_end_date DATE := NULL;
30 l_rt_start_date DATE := NULL;
31 l_rt_end_date DATE := NULL;
32 l_start_date DATE := NULL;
33 l_end_date DATE := NULL;
34 l_org_id NUMBER;
35 l_key NUMBER;
36 BEGIN
37 --clku
38 SELECT start_date,
39 end_date,
40 org_id
41 INTO l_quota_start_date,
42 l_quota_end_date,
43 l_org_id
44 FROM cn_quotas_all
45 WHERE quota_id = p_quota_id;
46
47 FOR rt_date IN rate_formula_date_curs
48 LOOP
49 l_rt_start_date := rt_date.start_date;
50 l_rt_end_date := rt_date.end_date;
51 -- bug 3602452 - reinitialize variables
52 l_start_date := NULL;
53 l_end_date := NULL;
54
55 -- 4 cases to get the overlap of l_rt_dates and l_quota_dates
56 IF (l_rt_end_date IS NULL AND l_quota_end_date IS NULL)
57 THEN
58 IF TRUNC (l_rt_start_date) >= TRUNC (l_quota_start_date)
59 THEN
60 l_start_date := l_rt_start_date;
61 ELSE
62 l_start_date := l_quota_start_date;
63 END IF;
64
65 l_end_date := NULL;
66 ELSIF (l_rt_end_date IS NULL AND (TRUNC (l_quota_end_date) > TRUNC (l_rt_start_date)))
67 THEN
68 IF TRUNC (l_rt_start_date) >= TRUNC (l_quota_start_date)
69 THEN
70 l_start_date := l_rt_start_date;
71 ELSE
72 l_start_date := l_quota_start_date;
73 END IF;
74
75 l_end_date := l_quota_end_date;
76 ELSIF (l_quota_end_date IS NULL AND (TRUNC (l_rt_end_date) > TRUNC (l_quota_start_date)))
77 THEN
78 IF TRUNC (l_rt_start_date) >= TRUNC (l_quota_start_date)
79 THEN
80 l_start_date := l_rt_start_date;
81 ELSE
82 l_start_date := l_quota_start_date;
83 END IF;
84
85 l_end_date := l_rt_end_date;
86 ELSIF ((TRUNC (l_rt_end_date) > TRUNC (l_quota_start_date)) OR (TRUNC (l_quota_end_date) > TRUNC (l_rt_start_date)))
87 THEN
88 IF TRUNC (l_rt_start_date) >= TRUNC (l_quota_start_date)
89 THEN
90 l_start_date := l_rt_start_date;
91 ELSE
92 l_start_date := l_quota_start_date;
93 END IF;
94
95 IF TRUNC (l_rt_end_date) <= TRUNC (l_quota_end_date)
96 THEN
97 l_end_date := l_rt_end_date;
98 ELSE
99 l_end_date := l_quota_end_date;
100 END IF;
101 END IF;
102
103 -- we only insert if there are overlap
104 -- clku, fix the date not overlap issue
105 IF ((l_start_date IS NOT NULL) AND (TRUNC (l_start_date) <= TRUNC (NVL (l_end_date, l_start_date))))
106 THEN
107 SELECT cn_rt_quota_asgns_s.NEXTVAL,
108 p_calc_formula_id,
109 p_quota_id,
110 l_start_date,
111 l_end_date,
112 rt_date.rate_schedule_id,
113 rt_date.NAME,
114 rt_date.calc_formula_name,
115 l_org_id
116 INTO l_rec.rt_quota_asgn_id,
117 l_rec.calc_formula_id,
118 l_rec.quota_id,
119 l_rec.start_date,
120 l_rec.end_date,
121 l_rec.rate_schedule_id,
122 l_rec.NAME,
123 l_rec.calc_formula_name,
124 l_rec.org_id
125 FROM DUAL;
126
127 p_rate_tables (l_rec.rt_quota_asgn_id) := l_rec;
128 END IF;
129 END LOOP;
130 END insert_formula_rate_tables;
131
132
133 -- -------------------------------------------------------------------------+-+
134 --| Procedure: add_system_note
135 --| Description: Insert notes for the create, update and delete
136 --| operations.
137 --| Called From: Create_quota_rule, Update_quota_rule
138 --| Delete_quota_rule
139 -- -------------------------------------------------------------------------+-+
140 PROCEDURE add_system_note(
141 p_rt_quota_asgn IN OUT NOCOPY rt_quota_asgn_rec_type,
142 p_old_rt_quota_asgn IN cn_rt_quota_asgns%ROWTYPE,
143 p_operation IN VARCHAR2,
144 x_return_status OUT NOCOPY VARCHAR2,
145 x_msg_count OUT NOCOPY NUMBER,
146 x_msg_data OUT NOCOPY VARCHAR2
147 )
148 IS
149
150 l_note_msg VARCHAR2 (2000);
151 l_plan_element_id NUMBER;
152 l_note_id NUMBER;
153 l_temp_old VARCHAR2 (200);
154 l_temp_new VARCHAR2 (200);
155 l_old_rt_name VARCHAR2 (200);
156
157 BEGIN
158 -- Initialize to success
159 x_return_status := fnd_api.g_ret_sts_success;
160 -- Initialize other fields
161 x_msg_data := fnd_api.g_null_char;
162 x_msg_count := fnd_api.g_null_num;
163 -- Getting the rate table name
164 SELECT NAME INTO l_temp_old
165 FROM CN_RATE_SCHEDULES
166 WHERE RATE_SCHEDULE_ID = p_rt_quota_asgn.rate_schedule_id
167 AND ORG_ID = p_rt_quota_asgn.org_id;
168
169 IF (p_operation = 'create') THEN
170 fnd_message.set_name('CN','CNR12_NOTE_PE_RT_ASGN_CRE');
171 fnd_message.set_token('RT_TAB', l_temp_old);
172 fnd_message.set_token('ST_DT', p_rt_quota_asgn.start_date);
173 fnd_message.set_token('END_DT', p_rt_quota_asgn.end_date);
174 l_plan_element_id := p_rt_quota_asgn.quota_id;
175 l_temp_new := 'CN_QUOTAS';
176 END IF;
177 IF (p_operation = 'delete') THEN
178 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_RT_ASGN_DEL');
179 fnd_message.set_token('RT_TAB', l_temp_old);
180 fnd_message.set_token('ST_DATE', p_rt_quota_asgn.start_date);
181 fnd_message.set_token('END_DATE', p_rt_quota_asgn.end_date);
182 l_plan_element_id := p_rt_quota_asgn.quota_id;
183 l_temp_new := 'CN_QUOTAS';
184 END IF;
185 IF (p_operation = 'update') THEN
186 SELECT NAME INTO l_old_rt_name
187 FROM CN_RATE_SCHEDULES
188 WHERE RATE_SCHEDULE_ID = p_old_rt_quota_asgn.rate_schedule_id
189 AND ORG_ID = p_old_rt_quota_asgn.org_id;
190
191 fnd_message.set_name('CN','CNR12_NOTE_PE_RT_ASGN_UPD');
192 fnd_message.set_token('OLD_RT_TAB', l_old_rt_name);
193 fnd_message.set_token('OLD_ST_DT', p_old_rt_quota_asgn.start_date);
194 fnd_message.set_token('OLD_END_DT', p_old_rt_quota_asgn.end_date);
195 fnd_message.set_token('NEW_RT_TAB', l_temp_old);
196 fnd_message.set_token('NEW_ST_DT', p_rt_quota_asgn.start_date);
197 fnd_message.set_token('NEW_END_DT', p_rt_quota_asgn.end_date);
198 l_plan_element_id := p_rt_quota_asgn.quota_id;
199 l_temp_new := 'CN_QUOTAS';
200 END IF;
201
202 l_note_msg := fnd_message.get;
203 jtf_notes_pub.create_note
204 (p_api_version => 1.0,
205 x_return_status => x_return_status,
206 x_msg_count => x_msg_count,
207 x_msg_data => x_msg_data,
208 p_source_object_id => l_plan_element_id,
209 p_source_object_code => l_temp_new,
210 p_notes => l_note_msg,
211 p_notes_detail => l_note_msg,
212 p_note_type => 'CN_SYSGEN', -- for system generated
213 x_jtf_note_id => l_note_id -- returned
214 );
215 EXCEPTION
216 WHEN fnd_api.g_exc_error
217 THEN
218 x_return_status := fnd_api.g_ret_sts_error;
219 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
220 WHEN fnd_api.g_exc_unexpected_error
221 THEN
222 x_return_status := fnd_api.g_ret_sts_unexp_error;
223 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
224 WHEN OTHERS
225 THEN
226 x_return_status := fnd_api.g_ret_sts_unexp_error;
227 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
228 THEN
229 fnd_msg_pub.add_exc_msg (g_pkg_name, 'add_system_note');
230 END IF;
231 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
232
233 END add_system_note;
234
235 -- Start of comments
236 -- API name : Create_Rt_Quota_Asgn
237 -- Type : Private.
238 -- Function :
239 -- Pre-reqs : None.
240 -- Parameters :
241 -- IN : p_api_version IN NUMBER Required
242 -- p_init_msg_list IN VARCHAR2 Optional
243 -- Default = FND_API.G_FALSE
244 -- p_commit IN VARCHAR2 Optional
245 -- Default = FND_API.G_FALSE
246 -- p_validation_level IN NUMBER Optional
247 -- Default = FND_API.G_VALID_LEVEL_FULL
248 -- p_rt_quota_asgn IN rt_quota_asgn_rec_type
249 -- OUT : x_return_status OUT VARCHAR2(1)
250 -- x_msg_count OUT NUMBER
251 -- x_msg_data OUT VARCHAR2(2000)
252 -- x_rt_quota_asgn_id OUT NUMBER
253 -- Version : Current version 1.0
254 -- Notes : Note text
255 --
256 -- End of comments
257 PROCEDURE create_rate_table_assignment (
258 p_api_version IN NUMBER,
259 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
260 p_commit IN VARCHAR2 := fnd_api.g_false,
261 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
262 p_rt_quota_asgn IN OUT NOCOPY rt_quota_asgn_rec_type,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2
266 )
267 IS
268 l_api_name CONSTANT VARCHAR2 (30) := 'create_rate_table_assignment';
269 l_api_version CONSTANT NUMBER := 1.0;
270 x_loading_status VARCHAR2 (30) := ' ';
271 l_temp_count NUMBER;
272 l_calc_formula_id NUMBER;
273 g_rowid VARCHAR2 (30);
274 g_program_type VARCHAR2 (30);
275 p_org_id NUMBER;
276 p_rt_quota_asgn_id NUMBER;
277 p_quota_id NUMBER;
278 p_start_date DATE;
279 p_end_date DATE;
280 p_rate_schedule_id NUMBER;
281 p_calc_formula_id NUMBER;
282 p_attribute_category VARCHAR2 (150);
283 p_attribute1 VARCHAR2 (150);
284 p_attribute2 VARCHAR2 (150);
285 p_attribute3 VARCHAR2 (150);
286 p_attribute4 VARCHAR2 (150);
287 p_attribute5 VARCHAR2 (150);
288 p_attribute6 VARCHAR2 (150);
289 p_attribute7 VARCHAR2 (150);
290 p_attribute8 VARCHAR2 (150);
291 p_attribute9 VARCHAR2 (150);
292 p_attribute10 VARCHAR2 (150);
293 p_attribute11 VARCHAR2 (150);
294 p_attribute12 VARCHAR2 (150);
295 p_attribute13 VARCHAR2 (150);
296 p_attribute14 VARCHAR2 (150);
297 p_attribute15 VARCHAR2 (150);
298 p_created_by NUMBER;
299 p_creation_date DATE;
300 p_last_update_login NUMBER;
301 p_last_update_date DATE;
302 p_last_updated_by NUMBER;
303 p_object_version_number NUMBER;
304 BEGIN
305 -- Standard Start of API savepoint
306 SAVEPOINT create_rt_quota_asgn;
307
308 -- Standard call to check for call compatibility.
309 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
310 THEN
311 RAISE fnd_api.g_exc_unexpected_error;
312 END IF;
313
314 -- Initialize message list if p_init_msg_list is set to TRUE.
315 IF fnd_api.to_boolean (p_init_msg_list)
316 THEN
317 fnd_msg_pub.initialize;
318 END IF;
319
320 -- Initialize API return status to success
321 x_return_status := fnd_api.g_ret_sts_success;
322
323 -- API body
324 SELECT NULL,
325 fnd_global.user_id,
326 SYSDATE,
327 fnd_global.login_id,
328 SYSDATE,
329 fnd_global.user_id,
330 1
331 INTO p_rt_quota_asgn.rt_quota_asgn_id,
332 p_rt_quota_asgn.created_by,
333 p_rt_quota_asgn.creation_date,
334 p_rt_quota_asgn.last_update_login,
335 p_rt_quota_asgn.last_update_date,
336 p_rt_quota_asgn.last_updated_by,
337 p_rt_quota_asgn.object_version_number
338 FROM DUAL;
339
340 -- validate this assignment
341 validate_rate_table_assignment (p_api_version => p_api_version,
342 p_rt_quota_asgn => p_rt_quota_asgn,
343 p_action => 'CREATE',
344 x_return_status => x_return_status,
345 x_msg_count => x_msg_count,
346 x_msg_data => x_msg_data
347 );
348
349 IF x_return_status <> fnd_api.g_ret_sts_success
350 THEN
351 RAISE fnd_api.g_exc_error;
352 END IF;
353
354 cn_rt_quota_asgns_pkg.begin_record (x_org_id => p_rt_quota_asgn.org_id,
355 x_operation => 'INSERT',
356 x_rowid => g_rowid,
357 x_rt_quota_asgn_id => p_rt_quota_asgn.rt_quota_asgn_id,
358 x_calc_formula_id => p_rt_quota_asgn.calc_formula_id,
359 x_quota_id => p_rt_quota_asgn.quota_id,
360 x_start_date => p_rt_quota_asgn.start_date,
361 x_end_date => p_rt_quota_asgn.end_date,
362 x_rate_schedule_id => p_rt_quota_asgn.rate_schedule_id,
363 x_attribute_category => p_rt_quota_asgn.attribute_category,
364 x_attribute1 => p_rt_quota_asgn.attribute1,
365 x_attribute2 => p_rt_quota_asgn.attribute2,
366 x_attribute3 => p_rt_quota_asgn.attribute3,
367 x_attribute4 => p_rt_quota_asgn.attribute4,
368 x_attribute5 => p_rt_quota_asgn.attribute5,
369 x_attribute6 => p_rt_quota_asgn.attribute6,
370 x_attribute7 => p_rt_quota_asgn.attribute7,
371 x_attribute8 => p_rt_quota_asgn.attribute8,
372 x_attribute9 => p_rt_quota_asgn.attribute9,
373 x_attribute10 => p_rt_quota_asgn.attribute10,
374 x_attribute11 => p_rt_quota_asgn.attribute11,
375 x_attribute12 => p_rt_quota_asgn.attribute12,
376 x_attribute13 => p_rt_quota_asgn.attribute13,
377 x_attribute14 => p_rt_quota_asgn.attribute14,
378 x_attribute15 => p_rt_quota_asgn.attribute15,
379 x_last_update_date => p_rt_quota_asgn.last_update_date,
380 x_last_updated_by => p_rt_quota_asgn.last_updated_by,
381 x_creation_date => p_rt_quota_asgn.creation_date,
382 x_created_by => p_rt_quota_asgn.created_by,
383 x_last_update_login => p_rt_quota_asgn.last_updated_by,
384 x_program_type => g_program_type,
385 x_object_version_number => p_rt_quota_asgn.object_version_number
386 );
387
388 -- Calling proc to add system note for create
389 add_system_note(
390 p_rt_quota_asgn,
391 null,
392 'create',
393 x_return_status,
394 x_msg_count,
395 x_msg_data
396 );
397 IF (x_return_status <> fnd_api.g_ret_sts_success)
398 THEN
399 RAISE fnd_api.g_exc_error;
400 END IF;
401
402
403
404 -- End of API body.
405 -- Standard check of p_commit.
406 IF fnd_api.to_boolean (p_commit)
407 THEN
408 COMMIT WORK;
409 END IF;
410
411 -- Standard call to get message count and if count is 1, get message info.
412 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
413 EXCEPTION
414 WHEN fnd_api.g_exc_error
415 THEN
416 ROLLBACK TO create_rt_quota_asgn;
417 x_return_status := fnd_api.g_ret_sts_error;
418 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
419 WHEN fnd_api.g_exc_unexpected_error
420 THEN
421 ROLLBACK TO create_rt_quota_asgn;
422 x_return_status := fnd_api.g_ret_sts_unexp_error;
423 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
424 WHEN OTHERS
425 THEN
426 ROLLBACK TO create_rt_quota_asgn;
427 x_return_status := fnd_api.g_ret_sts_unexp_error;
428
429 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
430 THEN
431 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
432 END IF;
433
434 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
435 END create_rate_table_assignment;
436
437 -- Start of comments
438 -- API name : Update_Rt_Quota_Asgn
439 -- Type : Private.
440 -- Function :
441 -- Pre-reqs : None.
442 -- Parameters :
443 -- IN : p_api_version IN NUMBER Required
444 -- p_init_msg_list IN VARCHAR2 Optional
445 -- Default = FND_API.G_FALSE
446 -- p_commit IN VARCHAR2 Optional
447 -- Default = FND_API.G_FALSE
448 -- p_validation_level IN NUMBER Optional
449 -- Default = FND_API.G_VALID_LEVEL_FULL
450 -- p_rt_quota_asgn IN rt_quota_asgn_rec_type
451 -- OUT : x_return_status OUT VARCHAR2(1)
452 -- x_msg_count OUT NUMBER
453 -- x_msg_data OUT VARCHAR2(2000)
454 -- Version : Current version 1.0
455 -- Notes : Note text
456 --
457 -- End of comments
458 PROCEDURE update_rate_table_assignment (
459 p_api_version IN NUMBER,
460 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
461 p_commit IN VARCHAR2 := fnd_api.g_false,
462 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
463 p_rt_quota_asgn IN OUT NOCOPY rt_quota_asgn_rec_type,
464 x_return_status OUT NOCOPY VARCHAR2,
465 x_msg_count OUT NOCOPY NUMBER,
466 x_msg_data OUT NOCOPY VARCHAR2
467 )
468 IS
469 l_api_name CONSTANT VARCHAR2 (30) := 'update_rate_table_assignment';
470 l_api_version CONSTANT NUMBER := 1.0;
471 l_err_message VARCHAR2 (30);
472 x_loading_status VARCHAR2 (30) := ' ';
473
474 CURSOR l_old_rt_quota_asgn_cr
475 IS
476 SELECT *
477 FROM cn_rt_quota_asgns
478 WHERE rt_quota_asgn_id = p_rt_quota_asgn.rt_quota_asgn_id;
479
480 l_old_rt_quota_asgn l_old_rt_quota_asgn_cr%ROWTYPE;
481 l_rt_quota_asgn rt_quota_asgn_rec_type;
482 l_temp_count NUMBER;
483 l_start_date DATE;
484 l_end_date DATE;
485 g_rowid VARCHAR2 (30);
486 g_program_type VARCHAR2 (30);
487 p_org_id NUMBER;
488 p_rt_quota_asgn_id NUMBER;
489 p_quota_id NUMBER;
490 p_start_date DATE;
491 p_end_date DATE;
492 p_rate_schedule_id NUMBER;
493 p_calc_formula_id NUMBER;
494 p_attribute_category VARCHAR2 (150);
495 p_attribute1 VARCHAR2 (150);
496 p_attribute2 VARCHAR2 (150);
497 p_attribute3 VARCHAR2 (150);
498 p_attribute4 VARCHAR2 (150);
499 p_attribute5 VARCHAR2 (150);
500 p_attribute6 VARCHAR2 (150);
501 p_attribute7 VARCHAR2 (150);
502 p_attribute8 VARCHAR2 (150);
503 p_attribute9 VARCHAR2 (150);
504 p_attribute10 VARCHAR2 (150);
505 p_attribute11 VARCHAR2 (150);
506 p_attribute12 VARCHAR2 (150);
507 p_attribute13 VARCHAR2 (150);
508 p_attribute14 VARCHAR2 (150);
509 p_attribute15 VARCHAR2 (150);
510 p_created_by NUMBER;
511 p_creation_date DATE;
512 p_last_update_login NUMBER;
513 p_last_update_date DATE;
514 p_last_updated_by NUMBER;
515 p_object_version_number NUMBER;
516 BEGIN
517 -- Standard Start of API savepoint
518 SAVEPOINT update_rt_quota_asgn;
519
520 -- Standard call to check for call compatibility.
521 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
522 THEN
523 RAISE fnd_api.g_exc_unexpected_error;
524 END IF;
525
526 -- Initialize message list if p_init_msg_list is set to TRUE.
527 IF fnd_api.to_boolean (p_init_msg_list)
528 THEN
529 fnd_msg_pub.initialize;
530 END IF;
531
532 -- Initialize API return status to success
533 x_return_status := fnd_api.g_ret_sts_success;
534
535 -- API body
536 OPEN l_old_rt_quota_asgn_cr;
537
538 FETCH l_old_rt_quota_asgn_cr
539 INTO l_old_rt_quota_asgn;
540
541 CLOSE l_old_rt_quota_asgn_cr;
542
543 SELECT DECODE (p_rt_quota_asgn.org_id, fnd_api.g_miss_num, l_old_rt_quota_asgn.org_id, p_rt_quota_asgn.org_id),
544 DECODE (p_rt_quota_asgn.rt_quota_asgn_id, fnd_api.g_miss_num, l_old_rt_quota_asgn.rt_quota_asgn_id, p_rt_quota_asgn.rt_quota_asgn_id),
545 DECODE (p_rt_quota_asgn.quota_id, fnd_api.g_miss_num, l_old_rt_quota_asgn.quota_id, p_rt_quota_asgn.quota_id),
546 DECODE (p_rt_quota_asgn.start_date, fnd_api.g_miss_date, TRUNC (l_old_rt_quota_asgn.start_date), TRUNC (p_rt_quota_asgn.start_date)),
547 DECODE (p_rt_quota_asgn.end_date, fnd_api.g_miss_date, TRUNC (l_old_rt_quota_asgn.end_date), TRUNC (p_rt_quota_asgn.end_date)),
548 DECODE (p_rt_quota_asgn.rate_schedule_id, fnd_api.g_miss_num, l_old_rt_quota_asgn.rate_schedule_id, p_rt_quota_asgn.rate_schedule_id),
549 DECODE (p_rt_quota_asgn.calc_formula_id, fnd_api.g_miss_num, l_old_rt_quota_asgn.calc_formula_id, p_rt_quota_asgn.calc_formula_id),
550 DECODE (p_rt_quota_asgn.attribute_category,
551 fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute_category,
552 p_rt_quota_asgn.attribute_category
553 ),
554 DECODE (p_rt_quota_asgn.attribute1, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute1, p_rt_quota_asgn.attribute1),
555 DECODE (p_rt_quota_asgn.attribute2, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute2, p_rt_quota_asgn.attribute2),
556 DECODE (p_rt_quota_asgn.attribute3, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute3, p_rt_quota_asgn.attribute3),
557 DECODE (p_rt_quota_asgn.attribute4, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute4, p_rt_quota_asgn.attribute4),
558 DECODE (p_rt_quota_asgn.attribute5, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute5, p_rt_quota_asgn.attribute5),
559 DECODE (p_rt_quota_asgn.attribute6, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute6, p_rt_quota_asgn.attribute6),
560 DECODE (p_rt_quota_asgn.attribute7, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute7, p_rt_quota_asgn.attribute7),
561 DECODE (p_rt_quota_asgn.attribute8, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute8, p_rt_quota_asgn.attribute8),
562 DECODE (p_rt_quota_asgn.attribute9, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute9, p_rt_quota_asgn.attribute9),
563 DECODE (p_rt_quota_asgn.attribute10, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute10, p_rt_quota_asgn.attribute10),
564 DECODE (p_rt_quota_asgn.attribute11, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute11, p_rt_quota_asgn.attribute11),
565 DECODE (p_rt_quota_asgn.attribute12, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute12, p_rt_quota_asgn.attribute12),
566 DECODE (p_rt_quota_asgn.attribute13, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute13, p_rt_quota_asgn.attribute13),
567 DECODE (p_rt_quota_asgn.attribute14, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute14, p_rt_quota_asgn.attribute14),
568 DECODE (p_rt_quota_asgn.attribute15, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute15, p_rt_quota_asgn.attribute15),
569 fnd_global.user_id,
570 SYSDATE,
571 fnd_global.login_id,
572 SYSDATE,
573 fnd_global.user_id,
574 p_rt_quota_asgn.object_version_number
575 INTO p_rt_quota_asgn.org_id,
576 p_rt_quota_asgn.rt_quota_asgn_id,
577 p_rt_quota_asgn.quota_id,
578 p_rt_quota_asgn.start_date,
579 p_rt_quota_asgn.end_date,
580 p_rt_quota_asgn.rate_schedule_id,
581 p_rt_quota_asgn.calc_formula_id,
582 p_rt_quota_asgn.attribute_category,
583 p_rt_quota_asgn.attribute1,
584 p_rt_quota_asgn.attribute2,
585 p_rt_quota_asgn.attribute3,
586 p_rt_quota_asgn.attribute4,
587 p_rt_quota_asgn.attribute5,
588 p_rt_quota_asgn.attribute6,
589 p_rt_quota_asgn.attribute7,
590 p_rt_quota_asgn.attribute8,
591 p_rt_quota_asgn.attribute9,
592 p_rt_quota_asgn.attribute10,
593 p_rt_quota_asgn.attribute11,
594 p_rt_quota_asgn.attribute12,
595 p_rt_quota_asgn.attribute13,
596 p_rt_quota_asgn.attribute14,
597 p_rt_quota_asgn.attribute15,
598 p_rt_quota_asgn.created_by,
599 p_rt_quota_asgn.creation_date,
600 p_rt_quota_asgn.last_update_login,
601 p_rt_quota_asgn.last_update_date,
602 p_rt_quota_asgn.last_updated_by,
603 p_rt_quota_asgn.object_version_number
604 FROM DUAL;
605
606 -- validate
607 validate_rate_table_assignment (p_api_version => p_api_version,
608 p_rt_quota_asgn => p_rt_quota_asgn,
609 p_action => 'UPDATE',
610 x_return_status => x_return_status,
611 x_msg_count => x_msg_count,
612 x_msg_data => x_msg_data
613 );
614
615 IF x_return_status <> fnd_api.g_ret_sts_success
616 THEN
617 RAISE fnd_api.g_exc_error;
618 END IF;
619
620 -- 3. check object version number
621 IF l_old_rt_quota_asgn.object_version_number <> p_rt_quota_asgn.object_version_number
622 THEN
623 fnd_message.set_name ('CN', 'CN_RECORD_CHANGED');
624 fnd_msg_pub.ADD;
625 RAISE fnd_api.g_exc_error;
626 END IF;
627
628 cn_rt_quota_asgns_pkg.begin_record (x_org_id => p_rt_quota_asgn.org_id,
629 x_operation => 'UPDATE',
630 x_rowid => g_rowid,
631 x_rt_quota_asgn_id => p_rt_quota_asgn.rt_quota_asgn_id,
632 x_calc_formula_id => p_rt_quota_asgn.calc_formula_id,
633 x_quota_id => p_rt_quota_asgn.quota_id,
634 x_start_date => p_rt_quota_asgn.start_date,
635 x_end_date => p_rt_quota_asgn.end_date,
636 x_rate_schedule_id => p_rt_quota_asgn.rate_schedule_id,
637 x_attribute_category => p_rt_quota_asgn.attribute_category,
638 x_attribute1 => p_rt_quota_asgn.attribute1,
639 x_attribute2 => p_rt_quota_asgn.attribute2,
640 x_attribute3 => p_rt_quota_asgn.attribute3,
641 x_attribute4 => p_rt_quota_asgn.attribute4,
642 x_attribute5 => p_rt_quota_asgn.attribute5,
643 x_attribute6 => p_rt_quota_asgn.attribute6,
644 x_attribute7 => p_rt_quota_asgn.attribute7,
645 x_attribute8 => p_rt_quota_asgn.attribute8,
646 x_attribute9 => p_rt_quota_asgn.attribute9,
647 x_attribute10 => p_rt_quota_asgn.attribute10,
648 x_attribute11 => p_rt_quota_asgn.attribute11,
649 x_attribute12 => p_rt_quota_asgn.attribute12,
650 x_attribute13 => p_rt_quota_asgn.attribute13,
651 x_attribute14 => p_rt_quota_asgn.attribute14,
652 x_attribute15 => p_rt_quota_asgn.attribute15,
653 x_last_update_date => p_rt_quota_asgn.last_update_date,
654 x_last_updated_by => p_rt_quota_asgn.last_updated_by,
655 x_creation_date => p_rt_quota_asgn.creation_date,
656 x_created_by => p_rt_quota_asgn.created_by,
657 x_last_update_login => p_rt_quota_asgn.last_updated_by,
658 x_program_type => g_program_type,
659 x_object_version_number => p_rt_quota_asgn.object_version_number
660 );
661
662 -- Calling proc to add system note for update
663 add_system_note(
664 p_rt_quota_asgn,
665 l_old_rt_quota_asgn,
666 'update',
667 x_return_status,
668 x_msg_count,
669 x_msg_data
670 );
671 IF (x_return_status <> fnd_api.g_ret_sts_success)
672 THEN
673 RAISE fnd_api.g_exc_error;
674 END IF;
675
676
677 -- End of API body.
678 -- Standard check of p_commit.
679 IF fnd_api.to_boolean (p_commit)
680 THEN
681 COMMIT WORK;
682 END IF;
683
684 -- Standard call to get message count and if count is 1, get message info.
685 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
686 EXCEPTION
687 WHEN fnd_api.g_exc_error
688 THEN
689 ROLLBACK TO update_rt_quota_asgn;
690 x_return_status := fnd_api.g_ret_sts_error;
691 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
692 WHEN fnd_api.g_exc_unexpected_error
693 THEN
694 ROLLBACK TO update_rt_quota_asgn;
695 x_return_status := fnd_api.g_ret_sts_unexp_error;
696 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
697 WHEN OTHERS
698 THEN
699 ROLLBACK TO update_rt_quota_asgn;
700 x_return_status := fnd_api.g_ret_sts_unexp_error;
701
702 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
703 THEN
704 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
705 END IF;
706
707 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
708 END update_rate_table_assignment;
709
710 -- Start of comments
711 -- API name : Delete_Rt_Quota_Asgn
712 -- Type : Private.
713 -- Function :
714 -- Pre-reqs : None.
715 -- Parameters :
716 -- IN : p_api_version IN NUMBER Required
717 -- p_init_msg_list IN VARCHAR2 Optional
718 -- Default = FND_API.G_FALSE
719 -- p_commit IN VARCHAR2 Optional
720 -- Default = FND_API.G_FALSE
721 -- p_validation_level IN NUMBER Optional
722 -- Default = FND_API.G_VALID_LEVEL_FULL
723 -- p_rt_quota_asgn IN quota_asgn_rec_type
724 -- OUT : x_return_status OUT VARCHAR2(1)
725 -- x_msg_count OUT NUMBER
726 -- x_msg_data OUT VARCHAR2(2000)
727 -- Version : Current version 1.0
728 -- Notes : Note text
729 --
730 -- End of comments
731 PROCEDURE delete_rate_table_assignment (
732 p_api_version IN NUMBER,
733 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
734 p_commit IN VARCHAR2 := fnd_api.g_false,
735 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
736 p_rt_quota_asgn IN OUT NOCOPY rt_quota_asgn_rec_type,
737 x_return_status OUT NOCOPY VARCHAR2,
738 x_msg_count OUT NOCOPY NUMBER,
739 x_msg_data OUT NOCOPY VARCHAR2
740 )
741 IS
742 l_api_name CONSTANT VARCHAR2 (30) := 'delete_rate_table_assignment';
743 l_api_version CONSTANT NUMBER := 1.0;
744 l_temp_count NUMBER;
745 g_rowid VARCHAR2 (30);
746 g_program_type VARCHAR2 (30);
747 p_org_id NUMBER;
748 p_rt_quota_asgn_id NUMBER;
749 p_quota_id NUMBER;
750 p_start_date DATE;
751 p_end_date DATE;
752 p_rate_schedule_id NUMBER;
753 p_calc_formula_id NUMBER;
754 p_attribute_category VARCHAR2 (150);
755 p_attribute1 VARCHAR2 (150);
756 p_attribute2 VARCHAR2 (150);
757 p_attribute3 VARCHAR2 (150);
758 p_attribute4 VARCHAR2 (150);
759 p_attribute5 VARCHAR2 (150);
760 p_attribute6 VARCHAR2 (150);
761 p_attribute7 VARCHAR2 (150);
762 p_attribute8 VARCHAR2 (150);
763 p_attribute9 VARCHAR2 (150);
764 p_attribute10 VARCHAR2 (150);
765 p_attribute11 VARCHAR2 (150);
766 p_attribute12 VARCHAR2 (150);
767 p_attribute13 VARCHAR2 (150);
768 p_attribute14 VARCHAR2 (150);
769 p_attribute15 VARCHAR2 (150);
770 p_created_by NUMBER;
771 p_creation_date DATE;
772 p_last_update_login NUMBER;
773 p_last_update_date DATE;
774 p_last_updated_by NUMBER;
775 p_object_version_number NUMBER;
776 /* CURSOR rt_quota_asgns_cr(l_rt_quota_asgn_id NUMBER) IS
777 SELECT rate_schedule_id,
778 quota_id
779 FROM cn_rt_quota_asgns
780 WHERE rt_quota_asgn_id = l_rt_quota_asgn_id;
781
782 l_rt_quota_asgn rt_quota_asgns_cr%ROWTYPE;*/
783 BEGIN
784 -- Standard Start of API savepoint
785 SAVEPOINT delete_rt_quota_asgn;
786
787 -- Standard call to check for call compatibility.
788 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
789 THEN
790 RAISE fnd_api.g_exc_unexpected_error;
791 END IF;
792
793 -- Initialize message list if p_init_msg_list is set to TRUE.
794 IF fnd_api.to_boolean (p_init_msg_list)
795 THEN
796 fnd_msg_pub.initialize;
797 END IF;
798
799 -- Initialize API return status to success
800 x_return_status := fnd_api.g_ret_sts_success;
801
802 -- API body
803
804 /*select count(1)
805 into l_temp_count
806 from cn_srp_plan_assigns
807 where quota_rule_id = p_quota_rule.quota_rule_id
808 and rownum = 1;
809
810 IF l_temp_count > 0 THEN
811
812 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
813 FND_MESSAGE.SET_NAME ('CN' , 'CN_QUOTA_RULE_ASGNED');
814 FND_MSG_PUB.Add;
815 END IF;
816 RAISE FND_API.G_EXC_ERROR ;
817 END IF;*/
818 SELECT p_rt_quota_asgn.org_id,
819 p_rt_quota_asgn.rt_quota_asgn_id,
820 p_rt_quota_asgn.quota_id,
821 p_rt_quota_asgn.start_date,
822 p_rt_quota_asgn.end_date,
823 p_rt_quota_asgn.rate_schedule_id,
824 p_rt_quota_asgn.calc_formula_id,
825 p_rt_quota_asgn.attribute_category,
826 p_rt_quota_asgn.attribute1,
827 p_rt_quota_asgn.attribute2,
828 p_rt_quota_asgn.attribute3,
829 p_rt_quota_asgn.attribute4,
830 p_rt_quota_asgn.attribute5,
831 p_rt_quota_asgn.attribute6,
832 p_rt_quota_asgn.attribute7,
833 p_rt_quota_asgn.attribute8,
834 p_rt_quota_asgn.attribute9,
835 p_rt_quota_asgn.attribute10,
836 p_rt_quota_asgn.attribute11,
837 p_rt_quota_asgn.attribute12,
838 p_rt_quota_asgn.attribute13,
839 p_rt_quota_asgn.attribute14,
840 p_rt_quota_asgn.attribute15,
841 fnd_global.user_id,
842 SYSDATE,
843 fnd_global.login_id,
844 SYSDATE,
845 fnd_global.user_id,
846 p_rt_quota_asgn.object_version_number + 1
847 INTO p_org_id,
848 p_rt_quota_asgn_id,
849 p_quota_id,
850 p_start_date,
851 p_end_date,
852 p_rate_schedule_id,
853 p_calc_formula_id,
854 p_attribute_category,
855 p_attribute1,
856 p_attribute2,
857 p_attribute3,
858 p_attribute4,
859 p_attribute5,
860 p_attribute6,
861 p_attribute7,
862 p_attribute8,
863 p_attribute9,
864 p_attribute10,
865 p_attribute11,
866 p_attribute12,
867 p_attribute13,
868 p_attribute14,
869 p_attribute15,
870 p_created_by,
871 p_creation_date,
872 p_last_update_login,
873 p_last_update_date,
874 p_last_updated_by,
875 p_object_version_number
876 FROM DUAL;
877
878 cn_comp_plans_pkg.set_status (x_comp_plan_id => NULL,
879 x_quota_id => p_quota_id,
880 x_rate_schedule_id => p_rate_schedule_id,
881 x_status_code => 'INCOMPLETE',
882 x_event => NULL
883 );
884 cn_rt_quota_asgns_pkg.begin_record (x_org_id => p_rt_quota_asgn.org_id,
885 x_operation => 'DELETE',
886 x_rowid => g_rowid,
887 x_rt_quota_asgn_id => p_rt_quota_asgn.rt_quota_asgn_id,
888 x_calc_formula_id => p_calc_formula_id,
889 x_quota_id => p_quota_id,
890 x_start_date => p_start_date,
891 x_end_date => p_end_date,
892 x_rate_schedule_id => p_rate_schedule_id,
893 x_attribute_category => p_attribute_category,
894 x_attribute1 => p_attribute1,
895 x_attribute2 => p_attribute2,
896 x_attribute3 => p_attribute3,
897 x_attribute4 => p_attribute4,
898 x_attribute5 => p_attribute5,
899 x_attribute6 => p_attribute6,
900 x_attribute7 => p_attribute7,
901 x_attribute8 => p_attribute8,
902 x_attribute9 => p_attribute9,
903 x_attribute10 => p_attribute10,
904 x_attribute11 => p_attribute11,
905 x_attribute12 => p_attribute12,
906 x_attribute13 => p_attribute13,
907 x_attribute14 => p_attribute14,
908 x_attribute15 => p_attribute15,
909 x_last_update_date => p_last_update_date,
910 x_last_updated_by => p_last_updated_by,
911 x_creation_date => p_creation_date,
912 x_created_by => p_created_by,
913 x_last_update_login => p_last_updated_by,
914 x_program_type => g_program_type,
915 x_object_version_number => p_rt_quota_asgn.object_version_number
916 );
917
918 -- Calling proc to add system note for delete
919 add_system_note(
920 p_rt_quota_asgn,
921 null,
922 'delete',
923 x_return_status,
924 x_msg_count,
925 x_msg_data
926 );
927 IF (x_return_status <> fnd_api.g_ret_sts_success)
928 THEN
929 RAISE fnd_api.g_exc_error;
930 END IF;
931
932
933 -- End of API body.
934 -- Standard check of p_commit.
935 IF fnd_api.to_boolean (p_commit)
936 THEN
937 COMMIT WORK;
938 END IF;
939
940 -- Standard call to get message count and if count is 1, get message info.
941 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
942 EXCEPTION
943 WHEN fnd_api.g_exc_error
944 THEN
945 ROLLBACK TO delete_rt_quota_asgn;
946 x_return_status := fnd_api.g_ret_sts_error;
947 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
948 WHEN fnd_api.g_exc_unexpected_error
949 THEN
950 ROLLBACK TO delete_rt_quota_asgn;
951 x_return_status := fnd_api.g_ret_sts_unexp_error;
952 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
953 WHEN OTHERS
954 THEN
955 ROLLBACK TO delete_rt_quota_asgn;
956 x_return_status := fnd_api.g_ret_sts_unexp_error;
957
958 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
959 THEN
960 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
961 END IF;
962
963 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
964 END delete_rate_table_assignment;
965
966 -- Start of comments
967 -- API name : get_formula_rate_tables
968 -- Type : Private.
969 -- Function :
970 -- Pre-reqs : None.
971 -- Parameters :
972 -- IN : p_api_version IN NUMBER Required
973 -- p_init_msg_list IN VARCHAR2 Optional
974 -- Default = FND_API.G_FALSE
975 -- p_commit IN VARCHAR2 Optional
976 -- Default = FND_API.G_FALSE
977 -- p_validation_level IN NUMBER Optional
978 -- Default = FND_API.G_VALID_LEVEL_FULL
979 -- p_quota_id IN NUMBER
980 -- OUT : x_return_status OUT VARCHAR2(1)
981 -- x_msg_count OUT NUMBER
982 -- x_msg_data OUT VARCHAR2(2000)
983 -- x_rt_quota_asgn OUT rt_quota_asgn_tbl_type
984 -- Version : Current version 1.0
985 -- Notes : Note text
986 --
987 -- End of comments
988 PROCEDURE get_formula_rate_tables (
989 p_api_version IN NUMBER,
990 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
991 p_commit IN VARCHAR2 := fnd_api.g_false,
992 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
993 p_type IN VARCHAR2 := 'FORMULA',
994 p_quota_id IN NUMBER,
995 p_calc_formula_id IN NUMBER,
996 x_calc_formulas OUT NOCOPY calc_formulas_tbl_type,
997 x_rate_tables OUT NOCOPY rt_quota_asgn_tbl_type,
998 x_return_status OUT NOCOPY VARCHAR2,
999 x_msg_count OUT NOCOPY NUMBER,
1000 x_msg_data OUT NOCOPY VARCHAR2
1001 )
1002 IS
1003 l_rate_tables rt_quota_asgn_tbl_type;
1004 l_calc_formulas calc_formulas_tbl_type;
1005 l_calc_rec calc_formulas_rec_type;
1006 l_api_name CONSTANT VARCHAR2 (30) := 'get_formula_rate_tables';
1007 l_api_version CONSTANT NUMBER := 1.0;
1008
1009 CURSOR calc_edge_curs (
1010 l_parent_id NUMBER
1011 )
1012 IS
1013 SELECT DISTINCT child_id
1014 FROM cn_calc_edges
1015 WHERE edge_type = 'FE' AND parent_id IN (SELECT calc_sql_exp_id
1016 FROM cn_formula_inputs
1017 WHERE calc_formula_id = l_parent_id
1018 UNION
1019 SELECT output_exp_id
1020 FROM cn_calc_formulas
1021 WHERE calc_formula_id = l_parent_id);
1022
1023 TYPE stack_type IS TABLE OF cn_calc_formulas.calc_formula_id%TYPE;
1024
1025 l_stack stack_type := stack_type() ;
1026 l_parent_calc_formula_id cn_calc_formulas.calc_formula_id%TYPE;
1027 l_child_calc_formula_id cn_calc_formulas.calc_formula_id%TYPE;
1028
1029 CURSOR rt_quota_asgn_curs (
1030 l_calc_formula_id NUMBER,
1031 l_quota_id NUMBER
1032 )
1033 IS
1034 SELECT rt_quota_asgn_id
1035 FROM cn_rt_quota_asgns
1036 WHERE quota_id = l_quota_id AND calc_formula_id = l_calc_formula_id;
1037
1038 l_rt_quota_asgn_id cn_rt_quota_asgns.rt_quota_asgn_id%TYPE;
1039 BEGIN
1040 -- Standard Start of API savepoint
1041 SAVEPOINT get_formula_rate_tables;
1042
1043 -- Standard call to check for call compatibility.
1044 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1045 THEN
1046 RAISE fnd_api.g_exc_unexpected_error;
1047 END IF;
1048
1049 -- Initialize message list if p_init_msg_list is set to TRUE.
1050 IF fnd_api.to_boolean (p_init_msg_list)
1051 THEN
1052 fnd_msg_pub.initialize;
1053 END IF;
1054
1055 -- Initialize API return status to success
1056 x_return_status := fnd_api.g_ret_sts_success;
1057
1058 IF p_calc_formula_id IS NOT NULL
1059 THEN
1060 l_stack := stack_type (p_calc_formula_id);
1061 END IF;
1062
1063 WHILE (l_stack.COUNT > 0)
1064 LOOP
1065 l_parent_calc_formula_id := l_stack (l_stack.LAST);
1066 l_stack.DELETE (l_stack.LAST);
1067
1068 SELECT NAME,
1069 calc_formula_id
1070 INTO l_calc_rec.NAME,
1071 l_calc_rec.calc_formula_id
1072 FROM cn_calc_formulas
1073 WHERE calc_formula_id = l_parent_calc_formula_id;
1074
1075 l_calc_formulas (l_calc_rec.calc_formula_id) := l_calc_rec;
1076
1077 -- clku, bug 2812184, only insert if we have not seen this quota/formula
1078 -- combination before
1079 OPEN rt_quota_asgn_curs (l_parent_calc_formula_id, p_quota_id);
1080
1081 FETCH rt_quota_asgn_curs
1082 INTO l_rt_quota_asgn_id;
1083
1084 IF rt_quota_asgn_curs%NOTFOUND
1085 THEN
1086 insert_formula_rate_tables (p_quota_id, l_parent_calc_formula_id, l_rate_tables);
1087 END IF;
1088
1089 CLOSE rt_quota_asgn_curs;
1090
1091 OPEN calc_edge_curs (l_parent_calc_formula_id);
1092
1093 LOOP
1094 FETCH calc_edge_curs
1095 INTO l_child_calc_formula_id;
1096
1097 IF calc_edge_curs%FOUND
1098 THEN
1099 l_stack.EXTEND;
1100 l_stack (l_stack.LAST) := l_child_calc_formula_id;
1101 ELSE
1102 EXIT;
1103 END IF;
1104 END LOOP;
1105
1106 CLOSE calc_edge_curs;
1107 END LOOP;
1108
1109 x_rate_tables := l_rate_tables;
1110 x_calc_formulas := l_calc_formulas;
1111
1112 -- End of API body.
1113 -- Standard check of p_commit.
1114 IF fnd_api.to_boolean (p_commit)
1115 THEN
1116 COMMIT WORK;
1117 END IF;
1118
1119 -- Standard call to get message count and if count is 1, get message info.
1120 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1121 EXCEPTION
1122 WHEN fnd_api.g_exc_error
1123 THEN
1124 ROLLBACK TO get_formula_rate_tables;
1125 x_return_status := fnd_api.g_ret_sts_error;
1126 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1127 WHEN fnd_api.g_exc_unexpected_error
1128 THEN
1129 ROLLBACK TO get_formula_rate_tables;
1130 x_return_status := fnd_api.g_ret_sts_unexp_error;
1131 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1132 WHEN OTHERS
1133 THEN
1134 ROLLBACK TO get_formula_rate_tables;
1135 x_return_status := fnd_api.g_ret_sts_unexp_error;
1136
1137 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1138 THEN
1139 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1140 END IF;
1141
1142 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1143 END get_formula_rate_tables;
1144
1145 -- Start of comments
1146 -- API name : validate_rate_table_assignment
1147 -- Type : Private.
1148 -- Function :
1149 -- Pre-reqs : None.
1150 -- Parameters :
1151 -- IN : p_api_version IN NUMBER Required
1152 -- p_init_msg_list IN VARCHAR2 Optional
1153 -- Default = FND_API.G_FALSE
1154 -- p_commit IN VARCHAR2 Optional
1155 -- Default = FND_API.G_FALSE
1156 -- p_validation_level IN NUMBER Optional
1157 -- Default = FND_API.G_VALID_LEVEL_FULL
1158 -- p_rt_quota_asgn IN quota_asgn_rec_type
1159 -- OUT : x_return_status OUT VARCHAR2(1)
1160 -- x_msg_count OUT NUMBER
1161 -- x_msg_data OUT VARCHAR2(2000)
1162 -- Version : Current version 1.0
1163 -- Notes : Note text
1164 --
1165 -- End of comments
1166 PROCEDURE validate_rate_table_assignment (
1167 p_api_version IN NUMBER,
1168 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1169 p_commit IN VARCHAR2 := fnd_api.g_false,
1170 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1171 p_action IN VARCHAR2,
1172 p_rt_quota_asgn IN OUT NOCOPY rt_quota_asgn_rec_type,
1173 p_old_rt_quota_asgn IN rt_quota_asgn_rec_type := g_miss_rt_quota_asgn_rec,
1174 x_return_status OUT NOCOPY VARCHAR2,
1175 x_msg_count OUT NOCOPY NUMBER,
1176 x_msg_data OUT NOCOPY VARCHAR2
1177 )
1178 IS
1179 l_api_name CONSTANT VARCHAR2 (30) := 'validate_rate_table_assignment';
1180 l_api_version CONSTANT NUMBER := 1.0;
1181 l_counter NUMBER;
1182 l_name VARCHAR2 (1000);
1183 l_loading_status VARCHAR2 (240);
1184 l_formula_type CN_QUOTAS.QUOTA_TYPE_CODE%TYPE ;
1185 l_formula_dim CN_CALC_FORMULAS.NUMBER_DIM%TYPE;
1186 l_rate_dim CN_RATE_SCHEDULES.NUMBER_DIM%TYPE;
1187 BEGIN
1188 -- Standard Start of API savepoint
1189 SAVEPOINT check_rt_quota_asgn;
1190
1191 -- Standard call to check for call compatibility.
1192 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1193 THEN
1194 RAISE fnd_api.g_exc_unexpected_error;
1195 END IF;
1196
1197 -- Initialize message list if p_init_msg_list is set to TRUE.
1198 IF fnd_api.to_boolean (p_init_msg_list)
1199 THEN
1200 fnd_msg_pub.initialize;
1201 END IF;
1202
1203 -- Initialize API return status to success
1204 x_return_status := fnd_api.g_ret_sts_success;
1205
1206 IF p_action = 'DELETE'
1207 THEN
1208 NULL;
1209 ELSE
1210 IF NOT cn_plan_element_pvt.is_valid_org(p_rt_quota_asgn.org_id,p_rt_quota_asgn.quota_id)
1211 THEN
1212 RAISE fnd_api.g_exc_error;
1213 END IF;
1214
1215 IF p_rt_quota_asgn.rate_schedule_id IS NULL
1216 THEN
1217 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1218 THEN
1219 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
1220 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('CALC_FORMULA_ID', 'PE_OBJECT_TYPE'));
1221 fnd_msg_pub.ADD;
1222 RAISE fnd_api.g_exc_error;
1223 END IF;
1224 ELSE
1225 BEGIN
1226 SELECT NAME
1227 INTO l_name
1228 FROM cn_rate_schedules
1229 WHERE rate_schedule_id = p_rt_quota_asgn.rate_schedule_id;
1230 EXCEPTION
1231 WHEN NO_DATA_FOUND
1232 THEN
1233 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1234 THEN
1235 fnd_message.set_name ('CN', 'CN_RATE_SCH_NOT_EXIST');
1236 fnd_msg_pub.ADD;
1237 END IF;
1238
1239 RAISE fnd_api.g_exc_error;
1240 END;
1241 END IF;
1242
1243 IF p_rt_quota_asgn.quota_id IS NULL
1244 THEN
1245 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1246 THEN
1247 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
1248 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('PE_NAME', 'INPUT_TOKEN'));
1249 fnd_msg_pub.ADD;
1250 RAISE fnd_api.g_exc_error;
1251 END IF;
1252 ELSE
1253 BEGIN
1254 SELECT name, quota_type_code
1255 INTO l_name , l_formula_type
1256 FROM cn_quotas
1257 WHERE quota_id = p_rt_quota_asgn.quota_id;
1258 EXCEPTION
1259 WHEN OTHERS
1260 THEN
1261 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1262 THEN
1263 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
1264 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('PE_NAME', 'INPUT_TOKEN'));
1265 fnd_msg_pub.ADD;
1266 END IF;
1267
1268 RAISE fnd_api.g_exc_error;
1269 END;
1270 END IF;
1271
1272 IF p_rt_quota_asgn.calc_formula_id IS NULL
1273 THEN
1274 IF l_formula_type = 'FORMULA' THEN
1275 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1276 THEN
1277 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
1278 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('CALC_FORMULA_ID', 'PE_OBJECT_TYPE'));
1279 fnd_msg_pub.ADD;
1280 RAISE fnd_api.g_exc_error;
1281 END IF;
1282 END IF ;
1283 ELSE
1284 BEGIN
1285 SELECT NAME
1286 INTO l_name
1287 FROM cn_calc_formulas fml
1288 WHERE fml.calc_formula_id = p_rt_quota_asgn.calc_formula_id;
1289 EXCEPTION
1290 WHEN NO_DATA_FOUND
1291 THEN
1292 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1293 THEN
1294 fnd_message.set_name ('CN', 'CN_FORMULA_NOT_EXIST');
1295 fnd_message.set_token ('FORMULA_NAME', p_rt_quota_asgn.calc_formula_id);
1296 fnd_msg_pub.ADD;
1297 END IF;
1298
1299 RAISE fnd_api.g_exc_error;
1300 END;
1301 END IF;
1302
1303 IF (p_rt_quota_asgn.start_date IS NULL)
1304 THEN
1305 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1306 THEN
1307 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
1308 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('START_DATE', 'PE_OBJECT_TYPE'));
1309 fnd_msg_pub.ADD;
1310 END IF;
1311
1312 RAISE fnd_api.g_exc_error;
1313 END IF;
1314
1315 -- 3.Start Date > End Date
1316 IF (p_rt_quota_asgn.end_date IS NOT NULL) AND (p_rt_quota_asgn.start_date > p_rt_quota_asgn.end_date)
1317 THEN
1318 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1319 THEN
1320 fnd_message.set_name ('CN', 'CN_DATE_RANGE_ERROR');
1321 fnd_msg_pub.ADD;
1322 END IF;
1323
1324 RAISE fnd_api.g_exc_error;
1325 END IF;
1326
1327 -- 4. Check date Effetcivity, quota rate assigns start date and end must
1328 -- be with start date and end date of the quota date
1329 cn_chk_plan_element_pkg.chk_date_effective (x_return_status => x_return_status,
1330 p_start_date => p_rt_quota_asgn.start_date,
1331 p_end_date => p_rt_quota_asgn.end_date,
1332 p_quota_id => p_rt_quota_asgn.quota_id,
1333 p_object_type => 'RATE',
1334 p_loading_status => l_loading_status,
1335 x_loading_status => l_loading_status
1336 );
1337
1338 IF x_return_status <> fnd_api.g_ret_sts_success
1339 THEN
1340 RAISE fnd_api.g_exc_error;
1341 END IF;
1342
1343 -- check that you dont have any rate table in the same date range
1344 SELECT COUNT (*)
1345 INTO l_counter
1346 FROM cn_rt_quota_asgns rta
1347 WHERE rta.quota_id = p_rt_quota_asgn.quota_id
1348 AND rta.rt_quota_asgn_id <> NVL (p_rt_quota_asgn.rt_quota_asgn_id, 0)
1349 AND NVL(rta.calc_formula_id,-1) = NVL(p_rt_quota_asgn.calc_formula_id,-1)
1350 AND rta.start_date <= NVL (p_rt_quota_asgn.end_date, rta.start_date)
1351 AND p_rt_quota_asgn.start_date <= NVL(rta.end_date,p_rt_quota_asgn.start_date) ;
1352
1353 IF l_counter > 0
1354 THEN
1355 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1356 THEN
1357 fnd_message.set_name ('CN', 'CN_DATE_OVERLAP');
1358 fnd_msg_pub.ADD;
1359 RAISE fnd_api.g_exc_error;
1360 END IF;
1361 END IF;
1362
1363 -- CHECK THAT NUMBER OF FORMULA DIMS = NUMBER OF RATE DIMS
1364 IF l_formula_type = 'FORMULA' THEN
1365 SELECT NUMBER_DIM INTO l_formula_dim FROM CN_CALC_FORMULAS
1366 WHERE calc_formula_id = p_rt_quota_asgn.calc_formula_id;
1367 SELECT NUMBER_DIM INTO l_rate_dim FROM CN_RATE_SCHEDULES
1368 WHERE rate_schedule_id = p_rt_quota_asgn.rate_schedule_id;
1369 IF l_formula_dim <> l_rate_dim THEN
1370 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1371 THEN
1372 fnd_message.set_name ('CN', 'CN_PE_DIMS_NOT_EQUAL');
1373 fnd_msg_pub.ADD;
1374 RAISE fnd_api.g_exc_error;
1375 END IF;
1376 END IF;
1377
1378 END IF;
1379 END IF;
1380
1381 -- End of API body.
1382 -- Standard check of p_commit.
1383 IF fnd_api.to_boolean (p_commit)
1384 THEN
1385 COMMIT WORK;
1386 END IF;
1387
1388 -- Standard call to get message count and if count is 1, get message info.
1389 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1390 EXCEPTION
1391 WHEN fnd_api.g_exc_error
1392 THEN
1393 ROLLBACK TO check_rt_quota_asgn;
1394 x_return_status := fnd_api.g_ret_sts_error;
1395 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1396 WHEN fnd_api.g_exc_unexpected_error
1397 THEN
1398 ROLLBACK TO check_rt_quota_asgn;
1399 x_return_status := fnd_api.g_ret_sts_unexp_error;
1400 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1401 WHEN OTHERS
1402 THEN
1403 ROLLBACK TO check_rt_quota_asgn;
1404 x_return_status := fnd_api.g_ret_sts_unexp_error;
1405
1406 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1407 THEN
1408 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1409 END IF;
1410
1411 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1412 END validate_rate_table_assignment;
1413 END cn_rt_quota_asgn_pvt;