[Home] [Help]
PACKAGE BODY: APPS.CN_QUOTA_RULE_UPLIFT_PVT
Source
1 PACKAGE BODY cn_quota_rule_uplift_pvt AS
2 /*$Header: cnvrlutb.pls 120.6 2006/05/17 00:28:59 chanthon ship $*/
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CN_QUOTA_RULE_UPLIFT_PVT';
4
5 -- -------------------------------------------------------------------------+-+
6 --| Procedure: add_system_note
7 --| Description: Insert notes for the create, update and delete
8 --| operations.
9 --| Called From: Create_quota_rule, Update_quota_rule
10 --| Delete_quota_rule
11 -- -------------------------------------------------------------------------+-+
12 PROCEDURE add_system_note(
13 p_quota_rule_uplift IN OUT NOCOPY quota_rule_uplift_rec_type,
14 p_quota_id IN NUMBER,
15 p_old_quota_rule_uplift IN cn_quota_rule_uplifts%ROWTYPE,
16 p_operation IN VARCHAR2,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2
20 )
21 IS
22
23 l_note_msg VARCHAR2 (2000);
24 l_plan_element_id NUMBER;
25 l_note_id NUMBER;
26 l_temp_old VARCHAR2 (200);
27 l_temp_new VARCHAR2 (200);
28 l_consolidated_note VARCHAR2(2000);
29
30 BEGIN
31 -- Initialize to success
32 x_return_status := fnd_api.g_ret_sts_success;
33 -- Initialize other fields
34 x_msg_data := fnd_api.g_null_char;
35 x_msg_count := fnd_api.g_null_num;
36 IF (p_operation <> 'update') THEN
37 IF (p_operation = 'create') THEN
38 fnd_message.set_name('CN','CNR12_NOTE_PE_ELIGPROD_CREATE');
39 fnd_message.set_token('PROD', p_quota_rule_uplift.rev_class_name);
40 fnd_message.set_token('ST_DATE', p_quota_rule_uplift.start_date);
41 fnd_message.set_token('ED_DATE', p_quota_rule_uplift.end_date);
42 l_plan_element_id := p_quota_id;
43 l_temp_new := 'CN_QUOTAS';
44 END IF;
45 IF (p_operation = 'delete') THEN
46 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_ELIG_EDATE_UPD');
47 fnd_message.set_token('ELIGPROD', p_quota_rule_uplift.rev_class_name);
48 fnd_message.set_token('ST_DATE', p_quota_rule_uplift.start_date);
49 fnd_message.set_token('ED_DATE', p_quota_rule_uplift.end_date);
50 l_plan_element_id := p_quota_id;
51 l_temp_new := 'CN_QUOTAS';
52 END IF;
53 l_note_msg := fnd_message.get;
54 jtf_notes_pub.create_note
55 (p_api_version => 1.0,
56 x_return_status => x_return_status,
57 x_msg_count => x_msg_count,
58 x_msg_data => x_msg_data,
59 p_source_object_id => l_plan_element_id,
60 p_source_object_code => l_temp_new,
61 p_notes => l_note_msg,
62 p_notes_detail => l_note_msg,
63 p_note_type => 'CN_SYSGEN', -- for system generated
64 x_jtf_note_id => l_note_id -- returned
65 );
66 ELSE
67 --DATE RANGE WAS CHANGED
68 l_consolidated_note := '';
69 IF (p_quota_rule_uplift.start_date <> p_quota_rule_uplift.start_date_old
70 OR p_quota_rule_uplift.end_date <> p_quota_rule_uplift.end_date_old) THEN
71 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_ELIGPROD_UPDATE');
72 fnd_message.set_token('PROD', p_quota_rule_uplift.rev_class_name);
73 fnd_message.set_token('OLD_ST_DATE', p_quota_rule_uplift.start_date_old);
74 fnd_message.set_token('OLD_ED_DATE', p_quota_rule_uplift.end_date_old);
75 fnd_message.set_token('NEW_ST_DATE', p_quota_rule_uplift.start_date);
76 fnd_message.set_token('NEW_ED_DATE', p_quota_rule_uplift.end_date);
77 l_plan_element_id := p_quota_id;
78 l_temp_new := 'CN_QUOTAS';
79
80 l_note_msg := fnd_message.get;
81 l_consolidated_note := l_note_msg || fnd_global.local_chr(10);
82 /* jtf_notes_pub.create_note
83 (p_api_version => 1.0,
84 x_return_status => x_return_status,
85 x_msg_count => x_msg_count,
86 x_msg_data => x_msg_data,
87 p_source_object_id => l_plan_element_id,
88 p_source_object_code => l_temp_new,
89 p_notes => l_note_msg,
90 p_notes_detail => l_note_msg,
91 p_note_type => 'CN_SYSGEN', -- for system generated
92 x_jtf_note_id => l_note_id -- returned
93 );*/
94 END IF;
95 --MULTIPLIER WAS CHANGED
96 IF (p_quota_rule_uplift.quota_factor <> p_old_quota_rule_uplift.quota_factor) THEN
97 fnd_message.set_name ('CN', 'CNR12_NOTE_FORMULA_INT_UPD');
98 fnd_message.set_token('PROD', p_quota_rule_uplift.rev_class_name);
99 fnd_message.set_token('ST_DATE', p_quota_rule_uplift.start_date);
100 fnd_message.set_token('ED_DATE', p_quota_rule_uplift.end_date);
101 fnd_message.set_token('OLD_MULTI',p_old_quota_rule_uplift.quota_factor);
102 fnd_message.set_token('NEW_MULTI',p_quota_rule_uplift.quota_factor );
103 l_plan_element_id := p_quota_id;
104 l_temp_new := 'CN_QUOTAS';
105
106 l_note_msg := fnd_message.get;
107 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
108 /* jtf_notes_pub.create_note
109 (p_api_version => 1.0,
110 x_return_status => x_return_status,
111 x_msg_count => x_msg_count,
112 x_msg_data => x_msg_data,
113 p_source_object_id => l_plan_element_id,
114 p_source_object_code => l_temp_new,
115 p_notes => l_note_msg,
116 p_notes_detail => l_note_msg,
117 p_note_type => 'CN_SYSGEN', -- for system generated
118 x_jtf_note_id => l_note_id -- returned
119 ); */
120 END IF;
121 --EARNINGS FACTOR WAS CHANGED
122 IF (p_quota_rule_uplift.payment_factor <> p_old_quota_rule_uplift.payment_factor) THEN
123 fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_ASPE_CRE');
124 fnd_message.set_token('ELIG', p_quota_rule_uplift.rev_class_name);
125 fnd_message.set_token('ST_DATE', p_quota_rule_uplift.start_date);
126 fnd_message.set_token('ED_DATE', p_quota_rule_uplift.end_date);
127 fnd_message.set_token('OLD_FACTOR', p_old_quota_rule_uplift.payment_factor);
128 fnd_message.set_token('NEW_FACTOR', p_quota_rule_uplift.payment_factor);
129 l_plan_element_id := p_quota_id;
130 l_temp_new := 'CN_QUOTAS';
131
132 l_note_msg := fnd_message.get;
133 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
134 /* jtf_notes_pub.create_note
135 (p_api_version => 1.0,
136 x_return_status => x_return_status,
137 x_msg_count => x_msg_count,
138 x_msg_data => x_msg_data,
139 p_source_object_id => l_plan_element_id,
140 p_source_object_code => l_temp_new,
141 p_notes => l_note_msg,
142 p_notes_detail => l_note_msg,
143 p_note_type => 'CN_SYSGEN', -- for system generated
144 x_jtf_note_id => l_note_id -- returned
145 );*/
146 END IF;
147
148 IF LENGTH(l_consolidated_note) > 1 THEN
149 jtf_notes_pub.create_note (p_api_version => 1.0,
150 x_return_status => x_return_status,
151 x_msg_count => x_msg_count,
152 x_msg_data => x_msg_data,
153 p_source_object_id => l_plan_element_id,
154 p_source_object_code => 'CN_QUOTAS',
155 p_notes => l_consolidated_note,
156 p_notes_detail => l_consolidated_note,
157 p_note_type => 'CN_SYSGEN', -- for system generated
158 x_jtf_note_id => l_note_id -- returned
159 );
160 END IF;
161
162
163 END IF;
164 EXCEPTION
165 WHEN fnd_api.g_exc_error
166 THEN
167 x_return_status := fnd_api.g_ret_sts_error;
168 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
169 WHEN fnd_api.g_exc_unexpected_error
170 THEN
171 x_return_status := fnd_api.g_ret_sts_unexp_error;
172 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
173 WHEN OTHERS
174 THEN
175 x_return_status := fnd_api.g_ret_sts_unexp_error;
176 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
177 THEN
178 fnd_msg_pub.add_exc_msg (g_pkg_name, 'add_system_note');
179 END IF;
180 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
181
182 END add_system_note;
183
184
185
186 -- Start of comments
187 -- API name : Delete_Quota_Rule_Uplift
188 -- Type : Private.
189 -- Function :
190 -- Pre-reqs : None.
191 -- Parameters :
192 -- IN : p_api_version IN NUMBER Required
193 -- p_init_msg_list IN VARCHAR2 Optional
194 -- Default = FND_API.G_FALSE
195 -- p_commit IN VARCHAR2 Optional
196 -- Default = FND_API.G_FALSE
197 -- p_validation_level IN NUMBER Optional
198 -- Default = FND_API.G_VALID_LEVEL_FULL
199 -- p_quota_rule_uplift IN quota_rule_uplift_rec_type
200 -- OUT : x_return_status OUT VARCHAR2(1)
201 -- x_msg_count OUT NUMBER
202 -- x_msg_data OUT VARCHAR2(2000)
203 -- Version : Current version 1.0
204 -- Notes : Note text
205 --
206 -- End of comments
207 PROCEDURE validate_uplift (
208 p_api_version IN NUMBER,
209 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
210 p_commit IN VARCHAR2 := fnd_api.g_false,
211 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
212 p_action IN VARCHAR2,
213 p_quota_rule_uplift IN OUT NOCOPY quota_rule_uplift_rec_type,
214 p_old_quota_rule_uplift IN quota_rule_uplift_rec_type := g_miss_quota_uplift_rec,
215 x_return_status OUT NOCOPY VARCHAR2,
216 x_msg_count OUT NOCOPY NUMBER,
217 x_msg_data OUT NOCOPY VARCHAR2
218 )
219 IS
220 l_api_name CONSTANT VARCHAR2 (30) := 'validate_uplift';
221 l_api_version CONSTANT NUMBER := 1.0;
222 l_temp_count NUMBER;
223 l_loading_status VARCHAR2 (240);
224 BEGIN
225 -- Standard Start of API savepoint
226 SAVEPOINT validate_uplift;
227
228 -- Standard call to check for call compatibility.
229 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
230 THEN
231 RAISE fnd_api.g_exc_unexpected_error;
232 END IF;
233
234 -- Initialize message list if p_init_msg_list is set to TRUE.
235 IF fnd_api.to_boolean (p_init_msg_list)
236 THEN
237 fnd_msg_pub.initialize;
238 END IF;
239
240 -- Initialize API return status to success
241 x_return_status := fnd_api.g_ret_sts_success;
242
243 -- if the Quota Rule uplift iD is null then Error message
244 IF p_quota_rule_uplift.quota_rule_uplift_id IS NULL
245 THEN
246 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
247 THEN
248 fnd_message.set_name ('CN', 'CN_INVALID_UPDATE_REC');
249 fnd_msg_pub.ADD;
250 END IF;
251
252 RAISE fnd_api.g_exc_error;
253 END IF;
254
255 IF p_action = 'DELETE'
256 THEN
257 -- Check wheather delete is Allowed, this only first and last record can be deleted
258 cn_chk_plan_element_pkg.chk_uplift_iud (x_return_status => x_return_status,
259 p_start_date => p_quota_rule_uplift.start_date,
260 p_end_date => p_quota_rule_uplift.end_date,
261 p_iud_flag => 'D',
262 p_quota_rule_id => p_quota_rule_uplift.quota_rule_id,
263 p_quota_rule_uplift_id => p_quota_rule_uplift.quota_rule_uplift_id,
264 p_loading_status => l_loading_status,
265 x_loading_status => l_loading_status
266 );
267
268 IF (x_return_status <> fnd_api.g_ret_sts_success)
269 THEN
270 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
271 THEN
272 fnd_message.set_name ('CN', 'CN_UPLIFT_DELETE_NOT_ALLOWED');
273 fnd_msg_pub.ADD;
274 END IF;
275
276 RAISE fnd_api.g_exc_error;
277 END IF;
278 END IF;
279
280 -- API body
281 IF (x_return_status <> fnd_api.g_ret_sts_success)
282 THEN
283 RAISE fnd_api.g_exc_error;
284 END IF;
285
286 -- End of API body.
287 -- Standard check of p_commit.
288 IF fnd_api.to_boolean (p_commit)
289 THEN
290 COMMIT WORK;
291 END IF;
292
293 -- Standard call to get message count and if count is 1, get message info.
294 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
295 EXCEPTION
296 WHEN fnd_api.g_exc_error
297 THEN
298 ROLLBACK TO validate_uplift;
299 x_return_status := fnd_api.g_ret_sts_error;
300 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
301 WHEN fnd_api.g_exc_unexpected_error
302 THEN
303 ROLLBACK TO validate_uplift;
304 x_return_status := fnd_api.g_ret_sts_unexp_error;
305 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
306 WHEN OTHERS
307 THEN
308 ROLLBACK TO validate_uplift;
309 x_return_status := fnd_api.g_ret_sts_unexp_error;
310
311 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
312 THEN
313 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
314 END IF;
315
316 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
317 END validate_uplift;
318
319 -- Start of comments
320 -- API name : Create_Quota_Uplift
321 -- Type : Private.
322 -- Function :
323 -- Pre-reqs : None.
324 -- Parameters :
325 -- IN : p_api_version IN NUMBER Required
326 -- p_init_msg_list IN VARCHAR2 Optional
327 -- Default = FND_API.G_FALSE
328 -- p_commit IN VARCHAR2 Optional
329 -- Default = FND_API.G_FALSE
330 -- p_validation_level IN NUMBER Optional
331 -- Default = FND_API.G_VALID_LEVEL_FULL
332 -- p_quota_rule_uplift IN quota_rule_uplift_rec_type
333 -- OUT : x_return_status OUT VARCHAR2(1)
334 -- x_msg_count OUT NUMBER
335 -- x_msg_data OUT VARCHAR2(2000)
336 -- x_quota_rule_uplift_id OUT NUMBER
337 -- Version : Current version 1.0
338 -- Notes : Note text
339 --
340 -- End of comments
341 PROCEDURE create_uplift (
342 p_api_version IN NUMBER,
343 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
344 p_commit IN VARCHAR2 := fnd_api.g_false,
345 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
346 p_quota_rule_uplift IN OUT NOCOPY quota_rule_uplift_rec_type,
347 x_return_status OUT NOCOPY VARCHAR2,
348 x_msg_count OUT NOCOPY NUMBER,
349 x_msg_data OUT NOCOPY VARCHAR2
350 )
351 IS
352 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Quota_Rule_Uplift';
353 l_api_version CONSTANT NUMBER := 1.0;
354 l_temp_count NUMBER;
355 l_loading_status VARCHAR2 (50);
356 l_rev_uplift_rec_tbl quota_rule_uplift_tbl_type;
357 l_quota_rule_id NUMBER;
358 l_quota_name cn_quotas.NAME%TYPE;
359 l_start_date DATE;
360 l_end_date DATE;
361 l_null_date CONSTANT DATE := TO_DATE ('31-12-9999', 'DD-MM-YYYY');
362 l_quota_id NUMBER;
363 BEGIN
364 -- Standard Start of API savepoint
365 SAVEPOINT create_quota_rule_uplift;
366
367 -- Standard call to check for call compatibility.
368 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
369 THEN
370 RAISE fnd_api.g_exc_unexpected_error;
371 END IF;
372
373 -- Initialize message list if p_init_msg_list is set to TRUE.
374 IF fnd_api.to_boolean (p_init_msg_list)
375 THEN
376 fnd_msg_pub.initialize;
377 END IF;
378
379 -- Initialize API return status to success
380 x_return_status := fnd_api.g_ret_sts_success;
381
382 -- API body
383
384 -- 1. name can not be null
385 --clku
386 IF (p_quota_rule_uplift.start_date IS NULL) OR (p_quota_rule_uplift.start_date = fnd_api.g_miss_date)
387 THEN
388 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
389 THEN
390 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
391 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('SD', 'INPUT_TOKEN'));
392 fnd_msg_pub.ADD;
393 END IF;
394
395 RAISE fnd_api.g_exc_error;
396 END IF;
397
398 -- 2. quota rule uplift name must be unique
399 SELECT DECODE (p_quota_rule_uplift.quota_rule_id, fnd_api.g_miss_num, NULL, p_quota_rule_uplift.quota_rule_id),
400 DECODE (p_quota_rule_uplift.start_date, fnd_api.g_miss_date, NULL, p_quota_rule_uplift.start_date),
401 DECODE (p_quota_rule_uplift.end_date, fnd_api.g_miss_date, NULL, p_quota_rule_uplift.end_date),
402 DECODE (p_quota_rule_uplift.payment_factor, NULL, 0, p_quota_rule_uplift.payment_factor),
403 DECODE (p_quota_rule_uplift.quota_factor, NULL, 0, p_quota_rule_uplift.quota_factor),
404 p_quota_rule_uplift.org_id,
405 p_quota_rule_uplift.quota_rule_id
406 INTO l_quota_rule_id,
407 l_rev_uplift_rec_tbl (1).start_date,
408 l_rev_uplift_rec_tbl (1).end_date,
409 l_rev_uplift_rec_tbl (1).payment_factor,
410 l_rev_uplift_rec_tbl (1).quota_factor,
411 l_rev_uplift_rec_tbl (1).org_id,
412 l_rev_uplift_rec_tbl (1).quota_rule_id
413 FROM DUAL;
414
415 SELECT q.start_date,
416 NVL (q.end_date, l_null_date),
417 q.quota_id
418 INTO l_start_date,
419 l_end_date,
420 l_quota_id
421 FROM cn_quotas q,
422 cn_quota_rules qr
423 WHERE qr.quota_rule_id = l_quota_rule_id AND q.quota_id = qr.quota_id;
424
425 IF (l_rev_uplift_rec_tbl (1).start_date < l_start_date) OR (NVL (l_rev_uplift_rec_tbl (1).end_date, l_null_date) > l_end_date)
426 THEN
427 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
428 THEN
429 fnd_message.set_name ('CN', 'CN_RC_INVALID_DATE_RANGE');
430 fnd_msg_pub.ADD;
431 END IF;
432
433 RAISE fnd_api.g_exc_error;
434 END IF;
435
436 SELECT r.NAME,
437 q.NAME
438 INTO l_rev_uplift_rec_tbl (1).rev_class_name,
439 l_quota_name
440 FROM cn_revenue_classes r,
441 cn_quotas q,
442 cn_quota_rules qr
443 WHERE qr.quota_rule_id = l_quota_rule_id AND r.revenue_class_id = qr.revenue_class_id AND q.quota_id = qr.quota_id;
444
445 cn_quota_rule_uplifts_grp.create_quota_rule_uplift (p_api_version => 1.0,
446 p_init_msg_list => 'T',
447 p_commit => 'F',
448 p_validation_level => 100,
449 x_return_status => x_return_status,
450 x_msg_count => x_msg_count,
451 x_msg_data => x_msg_data,
452 p_quota_name => l_quota_name,
453 p_rev_uplift_rec_tbl => l_rev_uplift_rec_tbl,
454 x_loading_status => l_loading_status
455 );
456 -- repopulate variables
457 p_quota_rule_uplift.org_id := l_rev_uplift_rec_tbl (1).org_id;
458 p_quota_rule_uplift.quota_rule_uplift_id := l_rev_uplift_rec_tbl (1).quota_rule_uplift_id;
459 p_quota_rule_uplift.quota_rule_id := l_rev_uplift_rec_tbl (1).quota_rule_id;
460 p_quota_rule_uplift.start_date := l_rev_uplift_rec_tbl (1).start_date;
461 p_quota_rule_uplift.end_date := l_rev_uplift_rec_tbl (1).end_date;
462 p_quota_rule_uplift.payment_factor := l_rev_uplift_rec_tbl (1).payment_factor;
463 p_quota_rule_uplift.quota_factor := l_rev_uplift_rec_tbl (1).quota_factor;
464 p_quota_rule_uplift.object_version_number := l_rev_uplift_rec_tbl (1).object_version_number;
465 p_quota_rule_uplift.rev_class_name := l_rev_uplift_rec_tbl (1).rev_class_name;
466 p_quota_rule_uplift.rev_class_name_old := l_rev_uplift_rec_tbl (1).rev_class_name_old;
467 p_quota_rule_uplift.start_date_old := l_rev_uplift_rec_tbl (1).start_date_old;
468 p_quota_rule_uplift.end_date_old := l_rev_uplift_rec_tbl (1).end_date_old;
469
470 IF (x_return_status <> fnd_api.g_ret_sts_success)
471 THEN
472 RAISE fnd_api.g_exc_error;
473 END IF;
474
475 -- Calling proc to add system note for create
476 add_system_note(
477 p_quota_rule_uplift,
478 l_quota_id,
479 null,
480 'create',
481 x_return_status,
482 x_msg_count,
483 x_msg_data
484 );
485 IF (x_return_status <> fnd_api.g_ret_sts_success)
486 THEN
487 RAISE fnd_api.g_exc_error;
488 END IF;
489
490 -- End of API body.
491 -- Standard check of p_commit.
492 IF fnd_api.to_boolean (p_commit)
493 THEN
494 COMMIT WORK;
495 END IF;
496
497 -- Standard call to get message count and if count is 1, get message info.
498 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
499 EXCEPTION
500 WHEN fnd_api.g_exc_error
501 THEN
502 ROLLBACK TO create_quota_rule_uplift;
503 x_return_status := fnd_api.g_ret_sts_error;
504 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
505 WHEN fnd_api.g_exc_unexpected_error
506 THEN
507 ROLLBACK TO create_quota_rule_uplift;
508 x_return_status := fnd_api.g_ret_sts_unexp_error;
509 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
510 WHEN OTHERS
511 THEN
512 ROLLBACK TO create_quota_rule_uplift;
513 x_return_status := fnd_api.g_ret_sts_unexp_error;
514
515 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
516 THEN
517 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
518 END IF;
519
520 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
521 END create_uplift;
522
523 -- Start of comments
524 -- API name : Update_Uplift
525 -- Type : Private.
526 -- Function :
527 -- Pre-reqs : None.
528 -- Parameters :
529 -- IN : p_api_version IN NUMBER Required
530 -- p_init_msg_list IN VARCHAR2 Optional
531 -- Default = FND_API.G_FALSE
532 -- p_commit IN VARCHAR2 Optional
533 -- Default = FND_API.G_FALSE
534 -- p_validation_level IN NUMBER Optional
535 -- Default = FND_API.G_VALID_LEVEL_FULL
536 -- p_quota_rule_uplift IN quota_rule_uplift_rec_type
537 -- OUT : x_return_status OUT VARCHAR2(1)
538 -- x_msg_count OUT NUMBER
539 -- x_msg_data OUT VARCHAR2(2000)
540 -- Version : Current version 1.0
541 -- Notes : Note text
542 --
543 -- End of comments
544 PROCEDURE update_uplift (
545 p_api_version IN NUMBER,
546 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
547 p_commit IN VARCHAR2 := fnd_api.g_false,
548 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
549 p_quota_rule_uplift IN OUT NOCOPY quota_rule_uplift_rec_type,
550 x_return_status OUT NOCOPY VARCHAR2,
551 x_msg_count OUT NOCOPY NUMBER,
552 x_msg_data OUT NOCOPY VARCHAR2
553 )
554 IS
555 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Quota_Rule_Uplift';
556 l_api_version CONSTANT NUMBER := 1.0;
557
558 CURSOR l_old_quota_rule_uplift_cr
559 IS
560 SELECT *
561 FROM cn_quota_rule_uplifts
562 WHERE quota_rule_uplift_id = p_quota_rule_uplift.quota_rule_uplift_id;
563
564 l_old_quota_rule_uplift l_old_quota_rule_uplift_cr%ROWTYPE;
565 l_quota_rule_uplift quota_rule_uplift_rec_type;
566 l_temp_count NUMBER;
567 l_loading_status VARCHAR2 (50);
568 l_rev_uplift_rec_tbl quota_rule_uplift_tbl_type;
569 l_quota_rule_id NUMBER;
570 l_quota_name cn_quotas.NAME%TYPE;
571 l_start_date DATE;
572 l_end_date DATE;
573 l_null_date CONSTANT DATE := TO_DATE ('31-12-9999', 'DD-MM-YYYY');
574 l_quota_id NUMBER;
575 BEGIN
576 -- Standard Start of API savepoint
577 SAVEPOINT update_quota_rule_uplift;
578
579 -- Standard call to check for call compatibility.
580 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
581 THEN
582 RAISE fnd_api.g_exc_unexpected_error;
583 END IF;
584
585 -- Initialize message list if p_init_msg_list is set to TRUE.
586 IF fnd_api.to_boolean (p_init_msg_list)
587 THEN
588 fnd_msg_pub.initialize;
589 END IF;
590
591 -- Initialize API return status to success
592 x_return_status := fnd_api.g_ret_sts_success;
593
594 -- API body
595
596 -- 1. name can not be null
597 IF (p_quota_rule_uplift.start_date IS NULL)
598 THEN
599 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
600 THEN
601 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
602 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('SD', 'INPUT_TOKEN'));
603 fnd_msg_pub.ADD;
604 END IF;
605
606 RAISE fnd_api.g_exc_error;
607 END IF;
608
609 -- 2. trx type must be unique
610 SELECT COUNT (1)
611 INTO l_temp_count
612 FROM cn_quota_rule_uplifts
613 WHERE quota_rule_id = p_quota_rule_uplift.quota_rule_id
614 AND TRUNC (start_date) = TRUNC (p_quota_rule_uplift.start_date)
615 AND quota_rule_uplift_id <> p_quota_rule_uplift.quota_rule_uplift_id
616 AND ROWNUM = 1;
617
618 IF l_temp_count <> 0
619 THEN
620 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
621 THEN
622 fnd_message.set_name ('CN', 'CN_INPUT_MUST_UNIQUE');
623 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('SD', 'INPUT_TOKEN'));
624 fnd_msg_pub.ADD;
625 END IF;
626
627 RAISE fnd_api.g_exc_error;
628 END IF;
629
630 OPEN l_old_quota_rule_uplift_cr;
631
632 FETCH l_old_quota_rule_uplift_cr
633 INTO l_old_quota_rule_uplift;
634
635 CLOSE l_old_quota_rule_uplift_cr;
636
637 SELECT DECODE (p_quota_rule_uplift.quota_rule_id, fnd_api.g_miss_num, l_old_quota_rule_uplift.quota_rule_id, p_quota_rule_uplift.quota_rule_id),
638 DECODE (p_quota_rule_uplift.start_date, fnd_api.g_miss_date, l_old_quota_rule_uplift.start_date, p_quota_rule_uplift.start_date),
639 DECODE (p_quota_rule_uplift.end_date, fnd_api.g_miss_date, l_old_quota_rule_uplift.end_date, p_quota_rule_uplift.end_date),
640 DECODE (p_quota_rule_uplift.payment_factor, NULL, 0, p_quota_rule_uplift.payment_factor),
641 DECODE (p_quota_rule_uplift.quota_factor, NULL, 0, p_quota_rule_uplift.quota_factor),
642 l_old_quota_rule_uplift.start_date,
643 l_old_quota_rule_uplift.end_date,
644 l_old_quota_rule_uplift.org_id
645 INTO l_rev_uplift_rec_tbl (1).quota_rule_id,
646 l_rev_uplift_rec_tbl (1).start_date,
647 l_rev_uplift_rec_tbl (1).end_date,
648 l_rev_uplift_rec_tbl (1).payment_factor,
649 l_rev_uplift_rec_tbl (1).quota_factor,
650 l_rev_uplift_rec_tbl (1).start_date_old,
651 l_rev_uplift_rec_tbl (1).end_date_old,
652 l_rev_uplift_rec_tbl (1).org_id
653 FROM DUAL;
654
655 l_quota_rule_id := l_rev_uplift_rec_tbl (1).quota_rule_id;
656
657 SELECT q.start_date,
658 NVL (q.end_date, l_null_date),
659 q.quota_id
660 INTO l_start_date,
661 l_end_date,
662 l_quota_id
663 FROM cn_quotas q,
664 cn_quota_rules qr
665 WHERE qr.quota_rule_id = l_quota_rule_id AND q.quota_id = qr.quota_id;
666
667 IF l_old_quota_rule_uplift.object_version_number <> p_quota_rule_uplift.object_version_number
668 THEN
669 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
670 THEN
671
672 fnd_message.set_name ('CN', 'CN_RECORD_CHANGED');
673 fnd_msg_pub.ADD;
674 END IF;
675
676 RAISE fnd_api.g_exc_error;
677 END IF;
678
679 IF (l_rev_uplift_rec_tbl (1).start_date < l_start_date) OR (NVL (l_rev_uplift_rec_tbl (1).end_date, l_null_date) > l_end_date)
680 THEN
681 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
682 THEN
683 fnd_message.set_name ('CN', 'CN_RC_INVALID_DATE_RANGE');
684 fnd_msg_pub.ADD;
685 END IF;
686
687 RAISE fnd_api.g_exc_error;
688 END IF;
689
690 SELECT r.NAME,
691 q.NAME
692 INTO l_rev_uplift_rec_tbl (1).rev_class_name,
693 l_quota_name
694 FROM cn_revenue_classes r,
695 cn_quotas q,
696 cn_quota_rules qr
697 WHERE qr.quota_rule_id = l_quota_rule_id AND r.revenue_class_id = qr.revenue_class_id AND q.quota_id = qr.quota_id;
698
699 SELECT r.NAME
700 INTO l_rev_uplift_rec_tbl (1).rev_class_name_old
701 FROM cn_revenue_classes r,
702 cn_quota_rules qr
703 WHERE qr.quota_rule_id = l_old_quota_rule_uplift.quota_rule_id AND r.revenue_class_id = qr.revenue_class_id;
704
705 cn_quota_rule_uplifts_grp.update_quota_rule_uplift (p_api_version => p_api_version,
706 p_init_msg_list => p_init_msg_list,
707 p_commit => p_commit,
708 p_validation_level => p_validation_level,
709 x_return_status => x_return_status,
710 x_msg_count => x_msg_count,
711 x_msg_data => x_msg_data,
712 p_quota_name => l_quota_name,
713 p_rev_uplift_rec_tbl => l_rev_uplift_rec_tbl,
714 x_loading_status => l_loading_status
715 );
716 -- repopulate variables
717 p_quota_rule_uplift.org_id := l_rev_uplift_rec_tbl (1).org_id;
718 p_quota_rule_uplift.quota_rule_uplift_id := l_rev_uplift_rec_tbl (1).quota_rule_uplift_id;
719 p_quota_rule_uplift.quota_rule_id := l_rev_uplift_rec_tbl (1).quota_rule_id;
720 p_quota_rule_uplift.start_date := l_rev_uplift_rec_tbl (1).start_date;
721 p_quota_rule_uplift.end_date := l_rev_uplift_rec_tbl (1).end_date;
722 p_quota_rule_uplift.payment_factor := l_rev_uplift_rec_tbl (1).payment_factor;
723 p_quota_rule_uplift.quota_factor := l_rev_uplift_rec_tbl (1).quota_factor;
724 p_quota_rule_uplift.object_version_number := l_rev_uplift_rec_tbl (1).object_version_number;
725 p_quota_rule_uplift.rev_class_name := l_rev_uplift_rec_tbl (1).rev_class_name;
726 p_quota_rule_uplift.rev_class_name_old := l_rev_uplift_rec_tbl (1).rev_class_name_old;
727 p_quota_rule_uplift.start_date_old := l_rev_uplift_rec_tbl (1).start_date_old;
728 p_quota_rule_uplift.end_date_old := l_rev_uplift_rec_tbl (1).end_date_old;
729
730 IF (x_return_status <> fnd_api.g_ret_sts_success)
731 THEN
732 RAISE fnd_api.g_exc_error;
733 END IF;
734
735 -- Calling proc to add system note for update
736 add_system_note(
737 p_quota_rule_uplift,
738 l_quota_id,
739 l_old_quota_rule_uplift,
740 'update',
741 x_return_status,
742 x_msg_count,
743 x_msg_data
744 );
745 IF (x_return_status <> fnd_api.g_ret_sts_success)
746 THEN
747 RAISE fnd_api.g_exc_error;
748 END IF;
749
750
751 -- End of API body.
752 -- Standard check of p_commit.
753 IF fnd_api.to_boolean (p_commit)
754 THEN
755 COMMIT WORK;
756 END IF;
757
758 -- Standard call to get message count and if count is 1, get message info.
759 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
760 EXCEPTION
761 WHEN fnd_api.g_exc_error
762 THEN
763 ROLLBACK TO update_quota_rule_uplift;
764 x_return_status := fnd_api.g_ret_sts_error;
765 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
766 WHEN fnd_api.g_exc_unexpected_error
767 THEN
768 ROLLBACK TO update_quota_rule_uplift;
769 x_return_status := fnd_api.g_ret_sts_unexp_error;
770 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
771 WHEN OTHERS
772 THEN
773 ROLLBACK TO update_quota_rule_uplift;
774 x_return_status := fnd_api.g_ret_sts_unexp_error;
775
776 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
777 THEN
778 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
779 END IF;
780
781 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
782 END update_uplift;
783
784 -- Start of comments
785 -- API name : Delete_Uplift
786 -- Type : Private.
787 -- Function :
788 -- Pre-reqs : None.
789 -- Parameters :
790 -- IN : p_api_version IN NUMBER Required
791 -- p_init_msg_list IN VARCHAR2 Optional
792 -- Default = FND_API.G_FALSE
793 -- p_commit IN VARCHAR2 Optional
794 -- Default = FND_API.G_FALSE
795 -- p_validation_level IN NUMBER Optional
796 -- Default = FND_API.G_VALID_LEVEL_FULL
797 -- p_quota_rule_uplift IN quota_rule_uplift_rec_type
798 -- OUT : x_return_status OUT VARCHAR2(1)
799 -- x_msg_count OUT NUMBER
800 -- x_msg_data OUT VARCHAR2(2000)
801 -- Version : Current version 1.0
802 -- Notes : Note text
803 --
804 -- End of comments
805 PROCEDURE delete_uplift (
806 p_api_version IN NUMBER,
807 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
808 p_commit IN VARCHAR2 := fnd_api.g_false,
809 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
810 p_quota_rule_uplift IN OUT NOCOPY quota_rule_uplift_rec_type,
811 x_return_status OUT NOCOPY VARCHAR2,
812 x_msg_count OUT NOCOPY NUMBER,
813 x_msg_data OUT NOCOPY VARCHAR2
814 )
815 IS
816 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Quota_Rule_Uplift';
817 l_api_version CONSTANT NUMBER := 1.0;
818 l_temp_count NUMBER;
819 l_loading_status VARCHAR2 (50);
820 l_rev_uplift_rec_tbl quota_rule_uplift_tbl_type;
821 l_quota_rule_id NUMBER;
822 l_quota_name cn_quotas.NAME%TYPE;
823 l_quota_id NUMBER;
824 BEGIN
825 -- Standard Start of API savepoint
826 SAVEPOINT delete_quota_rule_uplift;
827
828 -- Standard call to check for call compatibility.
829 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
830 THEN
831 RAISE fnd_api.g_exc_unexpected_error;
832 END IF;
833
834 -- Initialize message list if p_init_msg_list is set to TRUE.
835 IF fnd_api.to_boolean (p_init_msg_list)
836 THEN
837 fnd_msg_pub.initialize;
838 END IF;
839
840 -- Initialize API return status to success
841 x_return_status := fnd_api.g_ret_sts_success;
842
843 -- API body
844 SELECT r.NAME,
845 q.NAME,
846 qru.start_date,
847 qru.end_date,
848 q.org_id,
849 q.quota_id
850 INTO l_rev_uplift_rec_tbl (1).rev_class_name,
851 l_quota_name,
852 l_rev_uplift_rec_tbl (1).start_date,
853 l_rev_uplift_rec_tbl (1).end_date,
854 l_rev_uplift_rec_tbl (1).org_id,
855 l_quota_id
856 FROM cn_revenue_classes r,
857 cn_quotas q,
858 cn_quota_rules qr,
859 cn_quota_rule_uplifts qru
860 WHERE qr.quota_rule_id = qru.quota_rule_id
861 AND r.revenue_class_id = qr.revenue_class_id
862 AND q.quota_id = qr.quota_id
863 AND qru.quota_rule_uplift_id = p_quota_rule_uplift.quota_rule_uplift_id;
864
865 cn_quota_rule_uplifts_grp.delete_quota_rule_uplift (p_api_version => 1.0,
866 p_init_msg_list => 'T',
867 p_commit => 'F',
868 p_validation_level => 100,
869 x_return_status => x_return_status,
870 x_msg_count => x_msg_count,
871 x_msg_data => x_msg_data,
872 p_quota_name => l_quota_name,
873 p_rev_uplift_rec_tbl => l_rev_uplift_rec_tbl,
874 x_loading_status => l_loading_status
875 );
876
877
878 IF (x_return_status <> fnd_api.g_ret_sts_success)
879 THEN
880 RAISE fnd_api.g_exc_error;
881 END IF;
882
883 p_quota_rule_uplift.rev_class_name := l_rev_uplift_rec_tbl (1).rev_class_name;
884 -- Calling proc to add system note for delete
885 add_system_note(
886 p_quota_rule_uplift,
887 l_quota_id,
888 null,
889 'delete',
890 x_return_status,
891 x_msg_count,
892 x_msg_data
893 );
894 IF (x_return_status <> fnd_api.g_ret_sts_success)
895 THEN
896 RAISE fnd_api.g_exc_error;
897 END IF;
898
899 -- End of API body.
900 -- Standard check of p_commit.
901 IF fnd_api.to_boolean (p_commit)
902 THEN
903 COMMIT WORK;
904 END IF;
905
906 -- Standard call to get message count and if count is 1, get message info.
907 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
908 EXCEPTION
909 WHEN fnd_api.g_exc_error
910 THEN
911 ROLLBACK TO delete_quota_rule_uplift;
912 x_return_status := fnd_api.g_ret_sts_error;
913 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
914 WHEN fnd_api.g_exc_unexpected_error
915 THEN
916 ROLLBACK TO delete_quota_rule_uplift;
917 x_return_status := fnd_api.g_ret_sts_unexp_error;
918 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
919 WHEN OTHERS
920 THEN
921 ROLLBACK TO delete_quota_rule_uplift;
922 x_return_status := fnd_api.g_ret_sts_unexp_error;
923
924 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
925 THEN
926 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
927 END IF;
928
929 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
930 END delete_uplift;
931
932 END cn_quota_rule_uplift_pvt;