[Home] [Help]
PACKAGE BODY: APPS.CN_QUOTA_RULE_PVT
Source
1 PACKAGE BODY cn_quota_rule_pvt AS
2 /*$Header: cnvqtrlb.pls 120.6 2006/05/25 11:27:22 chanthon ship $*/
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CN_QUOTA_RULE_PVT';
4 g_last_update_date DATE := SYSDATE;
5 g_last_updated_by NUMBER := fnd_global.user_id;
6 g_creation_date DATE := SYSDATE;
7 g_created_by NUMBER := fnd_global.user_id;
8 g_last_update_login NUMBER := fnd_global.login_id;
9 g_rowid VARCHAR2 (30);
10 g_program_type VARCHAR2 (30);
11 g_quota_rule_not_exists VARCHAR2 (30) := 'QUOTA_RULE_NOT_EXISTS';
12 g_quota_rule_exists VARCHAR2 (30) := 'QUOTA_RULE_EXISTS';
13
14 --- convert the public rec to the private one
15 FUNCTION convert_rev_class_user_input (
16 p_quota_name IN VARCHAR2,
17 p_revenue_class_rec IN cn_plan_element_pub.revenue_class_rec_type,
18 p_old_revenue_class_name IN VARCHAR2 := NULL,
19 x_loading_status IN OUT NOCOPY VARCHAR2
20 )
21 RETURN quota_rule_rec_type
22 IS
23 l_quota_rule quota_rule_rec_type;
24 l_loading_status VARCHAR2 (2000);
25 l_old_name cn_revenue_classes.NAME%TYPE;
26 l_old_revenue_class_id NUMBER;
27 l_old_quota_rule_id NUMBER;
28 BEGIN
29 l_quota_rule.plan_element_name := LTRIM (RTRIM (p_quota_name));
30 l_quota_rule.revenue_class_name := LTRIM (RTRIM (p_revenue_class_rec.rev_class_name));
31 l_old_name := LTRIM (RTRIM (p_old_revenue_class_name));
32 l_quota_rule.revenue_class_id := cn_api.get_rev_class_id (l_quota_rule.revenue_class_name, l_quota_rule.org_id);
33
34 -- API body
35 -- Store the User Input Value into The Local Variable.
36 -- Standard check of p_commit.
37 --+
38 -- Check if plan element name is missing or null even for Delete the Q Rule
39 IF ((cn_api.chk_miss_char_para (p_char_para => l_quota_rule.plan_element_name,
40 p_para_name => cn_chk_plan_element_pkg.g_pe_name,
41 p_loading_status => l_loading_status,
42 x_loading_status => l_loading_status
43 )
44 ) = fnd_api.g_true
45 )
46 THEN
47 RAISE fnd_api.g_exc_error;
48 ELSIF ((cn_api.chk_null_char_para (p_char_para => l_quota_rule.plan_element_name,
49 p_obj_name => cn_chk_plan_element_pkg.g_pe_name,
50 p_loading_status => l_loading_status,
51 x_loading_status => l_loading_status
52 )
53 ) = fnd_api.g_true
54 )
55 THEN
56 RAISE fnd_api.g_exc_error;
57 END IF;
58
59 -- Check rev class name is not miss, not null
60 IF ((cn_api.chk_miss_char_para (p_char_para => l_quota_rule.revenue_class_name,
61 p_para_name => cn_chk_plan_element_pkg.g_rev_cls_name,
62 p_loading_status => x_loading_status,
63 x_loading_status => l_loading_status
64 )
65 ) = fnd_api.g_true
66 )
67 THEN
68 RAISE fnd_api.g_exc_error;
69 ELSIF ((cn_api.chk_null_char_para (p_char_para => l_quota_rule.revenue_class_name,
70 p_obj_name => cn_chk_plan_element_pkg.g_rev_cls_name,
71 p_loading_status => x_loading_status,
72 x_loading_status => l_loading_status
73 )
74 ) = fnd_api.g_true
75 )
76 THEN
77 RAISE fnd_api.g_exc_error;
78 END IF;
79
80 -- Get the Quota ID
81 l_quota_rule.quota_id := cn_chk_plan_element_pkg.get_quota_id (l_quota_rule.plan_element_name, l_quota_rule.org_id);
82
83 -- Raise an Error If quota id is null but name is not null
84 IF l_quota_rule.quota_id IS NULL AND l_quota_rule.plan_element_name IS NOT NULL
85 THEN
86 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
87 THEN
88 fnd_message.set_name ('CN', 'CN_PLN_NOT_EXIST');
89 fnd_message.set_token ('PE_NAME', p_quota_name);
90 fnd_msg_pub.ADD;
91 END IF;
92
93 RAISE fnd_api.g_exc_error;
94 END IF;
95
96 -- Get Revenue Class ID
97 l_quota_rule.revenue_class_id := cn_api.get_rev_class_id (l_quota_rule.revenue_class_name, l_quota_rule.org_id);
98
99 -- Raise an Error if the Revenue Class iD is Null and Name IS not NUll
100 IF l_quota_rule.revenue_class_id IS NULL AND l_quota_rule.revenue_class_name IS NOT NULL
101 THEN
102 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
103 THEN
104 fnd_message.set_name ('CN', 'CN_REV_CLASS_NOT_EXIST');
105 fnd_msg_pub.ADD;
106 END IF;
107
108 RAISE fnd_api.g_exc_error;
109 END IF;
110
111 -- Get the Quota Rule ID
112 l_quota_rule.quota_rule_id := cn_chk_plan_element_pkg.get_quota_rule_id (l_quota_rule.quota_id, l_quota_rule.revenue_class_id);
113
114 IF l_old_name IS NOT NULL
115 THEN
116 l_old_revenue_class_id := cn_api.get_rev_class_id (l_old_name, l_quota_rule.org_id);
117
118 IF l_old_revenue_class_id IS NULL
119 THEN
120 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
121 THEN
122 fnd_message.set_name ('CN', 'CN_REV_CLASS_NOT_EXIST');
123 fnd_msg_pub.ADD;
124 END IF;
125
126 RAISE fnd_api.g_exc_error;
127 END IF;
128
129 l_old_quota_rule_id :=
130 cn_chk_plan_element_pkg.get_quota_rule_id (p_quota_id => l_quota_rule.quota_id,
131 p_rev_class_id => l_old_revenue_class_id);
132
133 IF l_old_quota_rule_id IS NULL
134 THEN
135 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
136 THEN
137 fnd_message.set_name ('CN', 'CN_QUOTA_RULE_NOT_EXIST');
138 fnd_message.set_token ('PLAN_NAME', l_quota_rule.plan_element_name);
139 fnd_message.set_token ('REVENUE_CLASS_NAME', l_old_name);
140 fnd_msg_pub.ADD;
141 END IF;
142
143 RAISE fnd_api.g_exc_error;
144 END IF;
145
146 -- we are updating an existing rev assignment. Use the quota_rule_id
147 l_quota_rule.quota_rule_id := l_old_quota_rule_id;
148 END IF;
149
150 IF l_quota_rule.quota_rule_id IS NOT NULL
151 THEN
152 x_loading_status := g_quota_rule_exists;
153 ELSE
154 x_loading_status := g_quota_rule_not_exists;
155 END IF;
156
157 -- Set the Default value for Payment Amount
158 l_quota_rule.target := NVL (p_revenue_class_rec.rev_class_target, 0);
159 -- Set the Default value for Payment Amount
160 l_quota_rule.payment_amount := NVL (p_revenue_class_rec.rev_class_payment_amount, 0);
161 -- Set the Default Value for Performance Goal
162 l_quota_rule.performance_goal := NVL (p_revenue_class_rec.rev_class_performance_goal, 0);
163 x_loading_status := 'CN_UPDATED';
164 RETURN l_quota_rule;
165 END convert_rev_class_user_input;
166
167
168 -- -------------------------------------------------------------------------+-+
169 --| Procedure: add_system_note
170 --| Description: Insert notes for the create, update and delete
171 --| operations.
172 --| Called From: Create_quota_rule, Update_quota_rule
173 --| Delete_quota_rule
174 -- -------------------------------------------------------------------------+-+
175 PROCEDURE add_system_note(
176 p_quota_rule_old IN OUT NOCOPY quota_rule_rec_type,
177 p_quota_rule_new IN OUT NOCOPY quota_rule_rec_type,
178 p_operation IN VARCHAR2,
179 x_return_status OUT NOCOPY VARCHAR2,
180 x_msg_count OUT NOCOPY NUMBER,
181 x_msg_data OUT NOCOPY VARCHAR2
182 )
183 IS
184
185 l_note_msg VARCHAR2 (2000);
186 l_plan_element_id NUMBER;
187 l_note_id NUMBER;
188 l_temp_old VARCHAR2 (200);
189 l_temp_new VARCHAR2 (200);
190 l_temp_rc_old VARCHAR2 (200);
191
192 BEGIN
193 -- Initialize to success
194 x_return_status := fnd_api.g_ret_sts_success;
195 -- Initialize other fields
196 x_msg_data := fnd_api.g_null_char;
197 x_msg_count := fnd_api.g_null_num;
198 select name into l_temp_old from cn_quotas_v where quota_id = p_quota_rule_new.quota_id;
199 IF (p_operation = 'create') THEN
200 fnd_message.set_name('CN','CNR12_NOTE_PE_PROD_UPDATE');
201 fnd_message.set_token('ELIG_PROD', p_quota_rule_new.revenue_class_name);
202 fnd_message.set_token('PE_NAME', l_temp_old);
203 l_plan_element_id := p_quota_rule_new.quota_id;
204 l_temp_new := 'CN_QUOTAS';
205 END IF;
206 IF (p_operation = 'delete') THEN
207 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_ELIGPROD_DELETE');
208 fnd_message.set_token('PROD', p_quota_rule_new.revenue_class_name);
209 fnd_message.set_token('PE_NAME', l_temp_old);
210 l_plan_element_id := p_quota_rule_new.quota_id;
211 l_temp_new := 'CN_QUOTAS';
212 END IF;
213 IF (p_operation = 'update') THEN
214 select NAME into l_temp_rc_old from cn_revenue_classes where
215 revenue_class_id = p_quota_rule_old.revenue_class_id
216 and org_id = p_quota_rule_old.org_id;
217 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_PROD_CHANGE');
218 fnd_message.set_token('PROD_OLD', l_temp_rc_old);
219 fnd_message.set_token('PROD_NEW', p_quota_rule_new.revenue_class_name);
220 l_plan_element_id := p_quota_rule_new.quota_id;
221 l_temp_new := 'CN_QUOTAS';
222 END IF;
223 l_note_msg := fnd_message.get;
224 jtf_notes_pub.create_note
225 (p_api_version => 1.0,
226 x_return_status => x_return_status,
227 x_msg_count => x_msg_count,
228 x_msg_data => x_msg_data,
229 p_source_object_id => l_plan_element_id,
230 p_source_object_code => l_temp_new,
231 p_notes => l_note_msg,
232 p_notes_detail => l_note_msg,
233 p_note_type => 'CN_SYSGEN', -- for system generated
234 x_jtf_note_id => l_note_id -- returned
235 );
236
237 EXCEPTION
238 WHEN fnd_api.g_exc_error
239 THEN
240 x_return_status := fnd_api.g_ret_sts_error;
241 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
242 WHEN fnd_api.g_exc_unexpected_error
243 THEN
244 x_return_status := fnd_api.g_ret_sts_unexp_error;
245 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
246 WHEN OTHERS
247 THEN
248 x_return_status := fnd_api.g_ret_sts_unexp_error;
249 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
250 THEN
251 fnd_msg_pub.add_exc_msg (g_pkg_name, 'add_system_note');
252 END IF;
253 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
254
255 END add_system_note;
256
257
258
259
260
261 -- Start of comments
262 -- API name : validate_quota_rule
263 -- Type : Private.
264 -- Function :
265 -- Pre-reqs : None.
266 -- Parameters :
267 -- IN : p_api_version IN NUMBER Required
268 -- p_init_msg_list IN VARCHAR2 Optional
269 -- Default = FND_API.G_FALSE
270 -- p_commit IN VARCHAR2 Optional
271 -- Default = FND_API.G_FALSE
272 -- p_validation_level IN NUMBER Optional
273 -- Default = FND_API.G_VALID_LEVEL_FULL
274 -- p_quota_rule IN quota_rule_rec_type
275 -- OUT : x_return_status OUT VARCHAR2(1)
276 -- x_msg_count OUT NUMBER
277 -- x_msg_data OUT VARCHAR2(2000)
278 -- Version : Current version 1.0
279 -- Notes : Note text
280 --
281 -- End of comments
282 PROCEDURE validate_quota_rule (
283 p_api_version IN NUMBER,
284 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
285 p_commit IN VARCHAR2 := fnd_api.g_false,
286 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
287 p_action IN VARCHAR2,
288 p_quota_rule IN OUT NOCOPY quota_rule_rec_type,
289 p_old_quota_rule IN quota_rule_rec_type := g_quota_rule_rec,
290 x_return_status OUT NOCOPY VARCHAR2,
291 x_msg_count OUT NOCOPY NUMBER,
292 x_msg_data OUT NOCOPY VARCHAR2
293 )
294 IS
295 CURSOR quota_csr (
296 c_quota_id NUMBER
297 )
298 IS
299 SELECT quota_id,
300 NAME,
301 incentive_type_code,
302 quota_type_code,
303 org_id
304 FROM cn_quotas
305 WHERE quota_id = c_quota_id;
306
307 CURSOR c_uplift_csr
308 IS
309 SELECT *
310 FROM cn_quota_rule_uplifts
311 WHERE quota_rule_id = p_quota_rule.quota_rule_id;
312
313 l_rec quota_csr%ROWTYPE;
314 l_uplift_rec c_uplift_csr%ROWTYPE;
315 l_temp_count NUMBER;
316 l_quota_id NUMBER;
317 l_revenue_class_id NUMBER;
318 l_ret_val BOOLEAN;
319 l_same_pe NUMBER;
320 l_api_name CONSTANT VARCHAR2 (30) := 'validate_quota_rule';
321 l_api_version CONSTANT NUMBER := 1.0;
322 checkif_parent_revclass BOOLEAN := TRUE;
323 l_loading_status VARCHAR2 (2000);
324 x_loading_status varchar2(2000) ;
325 BEGIN
326 -- Standard Start of API savepoint
327 SAVEPOINT validate_quota_rule;
328
329 -- Standard call to check for call compatibility.
330 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
331 THEN
332 RAISE fnd_api.g_exc_unexpected_error;
333 END IF;
334
335 -- Initialize message list if p_init_msg_list is set to TRUE.
336 IF fnd_api.to_boolean (p_init_msg_list)
337 THEN
338 fnd_msg_pub.initialize;
339 END IF;
340
341 -- init the return status
342 x_return_status := fnd_api.g_ret_sts_success;
343
344 -- revenue class cannot be null
345 IF p_quota_rule.revenue_class_id IS NULL
346 THEN
347 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
348 THEN
349 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
350 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('RC', 'INPUT_TOKEN'));
351 fnd_msg_pub.ADD;
352 END IF;
353
354 RAISE fnd_api.g_exc_error;
355 END IF;
356
357 -- quota_id cannot be null
358 IF p_quota_rule.quota_id IS NULL
359 THEN
360 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
361 THEN
362 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
363 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('PE', 'INPUT_TOKEN'));
364 fnd_msg_pub.ADD;
365 END IF;
366
367 RAISE fnd_api.g_exc_error;
368 END IF;
369
370 BEGIN
371 SELECT NAME
372 INTO p_quota_rule.revenue_class_name
373 FROM cn_revenue_classes_all
374 WHERE revenue_class_id = p_quota_rule.revenue_class_id;
375 EXCEPTION
376 WHEN NO_DATA_FOUND
377 THEN
378 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
379 THEN
380 fnd_message.set_name ('CN', 'CN_REV_CLASS_NOT_EXIST');
381 fnd_msg_pub.ADD;
382 END IF;
383
384 RAISE fnd_api.g_exc_error;
385 END;
386
387 -- should only assign revenue class to these types
388 OPEN quota_csr (p_quota_rule.quota_id);
389
390 FETCH quota_csr
391 INTO l_rec;
392
393 IF quota_csr%NOTFOUND
394 THEN
395 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
396 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('PE', 'INPUT_TOKEN'));
397 fnd_msg_pub.ADD;
398
399 CLOSE quota_csr;
400
401 RAISE fnd_api.g_exc_error;
402 END IF;
403
404 p_quota_rule.org_id := l_rec.org_id;
405
406 CLOSE quota_csr;
407
408 -- only the quota_rule_id is required for delete
409 IF p_action = 'DELETE'
410 THEN
411 BEGIN
412 SELECT quota_id,
413 revenue_class_id
414 INTO p_quota_rule.quota_id,
415 p_quota_rule.revenue_class_id
416 FROM cn_quota_rules
417 WHERE quota_rule_id = p_quota_rule.quota_rule_id;
418 EXCEPTION
419 WHEN NO_DATA_FOUND
420 THEN
421 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
422 THEN
423 fnd_message.set_name ('CN', '###CN_QUOTA_RULE_NOT_EXIST###');
424 fnd_message.set_token ('PLAN_NAME', p_quota_rule.quota_id);
425 fnd_message.set_token ('REVENUE_CLASS_NAME', p_quota_rule.revenue_class_name);
426 fnd_msg_pub.ADD;
427 END IF;
428
429 RAISE fnd_api.g_exc_error;
430 END;
431
432 ELSE
433 -- target, payment_amount and performance_goal cannot be null or less than zero
434 IF p_quota_rule.target < 0
435 THEN
436 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
437 THEN
438 fnd_message.set_name ('CN', 'CN_REV_TARGET_GT_ZERO');
439 fnd_msg_pub.ADD;
440 END IF;
441
442 RAISE fnd_api.g_exc_error;
443 END IF;
444
445 -- 1. name can not be null
446 IF (p_quota_rule.target IS NULL)
447 THEN
448 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
449 THEN
450 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
451 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('VARIABLES', 'PE_OBJECT_TYPE'));
452 fnd_msg_pub.ADD;
453 END IF;
454
455 RAISE fnd_api.g_exc_error;
456 END IF;
457
458 IF (p_quota_rule.payment_amount IS NULL)
459 THEN
460 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
461 THEN
462 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
463 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('VARIABLES', 'PE_OBJECT_TYPE'));
464 fnd_msg_pub.ADD;
465 END IF;
466
467 RAISE fnd_api.g_exc_error;
468 END IF;
469
470 IF (p_quota_rule.org_id IS NULL)
471 THEN
472 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
473 THEN
474 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
475 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('ORGANIZATION', 'PE_OBJECT_TYPE'));
476 fnd_msg_pub.ADD;
477 END IF;
478
479 RAISE fnd_api.g_exc_error;
480 END IF;
481
482 IF (p_quota_rule.org_id <> l_rec.org_id)
483 THEN
484 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
485 THEN
486 fnd_message.set_name ('CN', 'CN_INCONSISTENT_DATA');
487 fnd_message.set_token ('INPUT1', cn_api.get_lkup_meaning ('ORGANIZATION', 'PE_OBJECT_TYPE'));
488 fnd_message.set_token ('INPUT2', cn_api.get_lkup_meaning ('PE', 'INPUT_TOKEN'));
489 fnd_message.set_token ('INPUT3', ' ');
490 fnd_message.set_token ('INPUT4', ' ');
491 fnd_message.set_token ('INPUT5', ' ');
492 fnd_msg_pub.ADD;
493 END IF;
494
495 RAISE fnd_api.g_exc_error;
496 END IF;
497
498 IF (p_quota_rule.performance_goal IS NULL)
499 THEN
500 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
501 THEN
502 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
503 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('VARIABLES', 'PE_OBJECT_TYPE'));
504 fnd_msg_pub.ADD;
505 END IF;
506
507 RAISE fnd_api.g_exc_error;
508 END IF;
509
510 IF l_rec.incentive_type_code NOT IN ('COMMISSION', 'BONUS')
511 THEN
512 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
513 THEN
514 fnd_message.set_name ('CN', 'CN_CANNOT_HAVE_REV_CLASS');
515 fnd_message.set_token ('OBJ_VALUE', 'MANUAL');
516 fnd_message.set_token ('PLAN_TYPE', cn_api.get_lkup_meaning (l_rec.incentive_type_code, 'QUOTA_TYPE'));
517 fnd_msg_pub.ADD;
518 END IF;
519
520 RAISE fnd_api.g_exc_error;
521 END IF;
522
523 -- create validations only
524 IF p_action = 'CREATE'
525 THEN
526 -- 2. revenue class must be unique
527 SELECT COUNT (1)
528 INTO l_temp_count
529 FROM cn_quota_rules
530 WHERE quota_id = p_quota_rule.quota_id AND revenue_class_id = p_quota_rule.revenue_class_id AND ROWNUM = 1;
531 -- update validations only
532 ELSIF p_action = 'UPDATE'
533 THEN
534 -- check the object version number
535 IF NVL (p_quota_rule.object_version_number, -1) <> p_old_quota_rule.object_version_number
536 THEN
537 fnd_message.set_name ('CN', 'CN_RECORD_CHANGED');
538 fnd_msg_pub.ADD;
539 RAISE fnd_api.g_exc_error;
540 END IF;
541
542 -- cannot change the planelement assignment of a quota_rule_assignment
543 SELECT COUNT (*)
544 INTO l_same_pe
545 FROM cn_quota_rules qr
546 WHERE qr.revenue_class_id = p_old_quota_rule.revenue_class_id
547 AND qr.quota_id = p_quota_rule.quota_id
548 AND qr.quota_rule_id = p_quota_rule.quota_rule_id;
549
550 IF l_same_pe = 0
551 THEN
552 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
553 THEN
554 fnd_message.set_name ('CN', 'CN_INCONSISTENT_DATA');
555 fnd_message.set_token ('INPUT1', cn_api.get_lkup_meaning ('RC_ASSIGN', 'PE_OBJECT_TYPE'));
556 fnd_message.set_token ('INPUT2', cn_api.get_lkup_meaning ('PE', 'INPUT_TOKEN'));
557 fnd_message.set_token ('INPUT3', cn_api.get_lkup_meaning ('RC', 'INPUT_TOKEN'));
558 fnd_message.set_token ('INPUT4', ' ');
559 fnd_message.set_token ('INPUT5', ' ');
560 fnd_msg_pub.ADD;
561 END IF;
562
563 RAISE fnd_api.g_exc_error;
564 END IF;
565
566 -- check that
567 OPEN c_uplift_csr ;
568 LOOP
569 FETCH c_uplift_csr INTO l_uplift_rec;
570 IF c_uplift_csr%NOTFOUND
571 THEN
572 EXIT ;
573 END IF;
574
575 cn_chk_plan_element_pkg.chk_uplift_iud (x_return_status => x_return_status,
576 p_start_date => l_uplift_rec.start_date,
577 p_end_date => l_uplift_rec.end_date,
578 p_iud_flag => 'U',
579 p_quota_rule_id => p_quota_rule.quota_rule_id,
580 p_quota_rule_uplift_id => l_uplift_rec.quota_rule_uplift_id,
581 p_loading_status => x_loading_status,
582 x_loading_status => l_loading_status
583 );
584
585 x_loading_status := l_loading_status;
586
587 IF (x_return_status <> fnd_api.g_ret_sts_success)
588 THEN
589 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
590 THEN
591 fnd_message.set_name ('CN', 'CN_INVALID_DATE_SEQUENCE');
592 fnd_msg_pub.ADD;
593 END IF;
594
595 x_loading_status := 'INVALID_DATE_SEQUENCE';
596 RAISE fnd_api.g_exc_error;
597 END IF;
598 END LOOP ;
599 CLOSE c_uplift_csr;
600
601 -- ensure that the transaction factors add up to 100
602 cn_chk_plan_element_pkg.chk_trx_factor (x_return_status => x_return_status,
603 p_quota_rule_id => p_quota_rule.quota_rule_id,
604 p_rev_class_name => p_quota_rule.revenue_class_name,
605 p_loading_status => l_loading_status,
606 x_loading_status => l_loading_status
607 );
608
609
610 -- if updating and revenue class is not updated skip revclass hierarchy check
611 checkif_parent_revclass := FALSE;
612
613 -- revenue class must be unique
614 SELECT COUNT (1)
615 INTO l_temp_count
616 FROM cn_quota_rules
617 WHERE quota_id = p_quota_rule.quota_id
618 AND revenue_class_id = p_quota_rule.revenue_class_id
619 AND quota_rule_id <> p_quota_rule.quota_rule_id
620 AND ROWNUM = 1;
621
622 END IF; -- if update end
623
624 IF l_temp_count <> 0
625 THEN
626 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
627 THEN
628 fnd_message.set_name ('CN', 'PLN_QUOTA_REV_EXIST');
629 fnd_message.set_token ('PLAN_NAME', l_rec.NAME);
630 fnd_message.set_token ('REVENUE_CLASS_NAME', p_quota_rule.revenue_class_name);
631 fnd_msg_pub.ADD;
632 END IF;
633
634 RAISE fnd_api.g_exc_error;
635 END IF;
636
637 -- performance gain: do the hierarchy traversal after everything else is okay
638 -- and only if there is a change
639 IF checkif_parent_revclass
640 THEN
641 l_ret_val :=
642 cn_quota_rules_pkg.check_rev_class_hier (x_revenue_class_id => p_quota_rule.revenue_class_id,
643 x_revenue_class_id_old => p_old_quota_rule.revenue_class_id,
644 x_quota_id => p_quota_rule.quota_id,
645 x_start_period_id => NULL,
646 x_end_period_id => NULL
647 );
648
649 -- Validate Rule :
650 -- Checks if p_quota_rule.rev_class_id is a parent in a hierarchy
651 -- for any other p_quota_rule.rev_class_id already saved in the database
652 -- for the p_quota_rule.quota_id
653 IF (NOT l_ret_val)
654 THEN
655 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
656 THEN
657 fnd_msg_pub.ADD;
658 END IF;
659
660 RAISE fnd_api.g_exc_error;
661 END IF;
662 END IF;
663 END IF;
664
665 -- Standard call to get message count and if count is 1, get message info.
666 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
667 EXCEPTION
668 WHEN fnd_api.g_exc_error
669 THEN
670 ROLLBACK TO validate_quota_rule;
671 x_return_status := fnd_api.g_ret_sts_error;
672 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
673 WHEN fnd_api.g_exc_unexpected_error
674 THEN
675 ROLLBACK TO validate_quota_rule;
676 x_return_status := fnd_api.g_ret_sts_unexp_error;
677 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
678 WHEN OTHERS
679 THEN
680 ROLLBACK TO validate_quota_rule;
681 x_return_status := fnd_api.g_ret_sts_unexp_error;
682
683 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
684 THEN
685 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
686 END IF;
687
688 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
689 END validate_quota_rule;
690
691 -- Start of comments
692 -- API name : Create_Quota_Rule
693 -- Type : Private.
694 -- Function :
695 -- Pre-reqs : None.
696 -- Parameters :
697 -- IN : p_api_version IN NUMBER Required
698 -- p_init_msg_list IN VARCHAR2 Optional
699 -- Default = FND_API.G_FALSE
700 -- p_commit IN VARCHAR2 Optional
701 -- Default = FND_API.G_FALSE
702 -- p_validation_level IN NUMBER Optional
703 -- Default = FND_API.G_VALID_LEVEL_FULL
704 -- p_quota_rule IN quota_rule_rec_type
705 -- OUT : x_return_status OUT VARCHAR2(1)
706 -- x_msg_count OUT NUMBER
707 -- x_msg_data OUT VARCHAR2(2000)
708 -- x_quota_rule_id OUT NUMBER
709 -- Version : Current version 1.0
710 -- Notes : Note text
711 --
712 -- End of comments
713 PROCEDURE create_quota_rule (
714 p_api_version IN NUMBER,
715 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
716 p_commit IN VARCHAR2 := fnd_api.g_false,
717 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
718 p_quota_rule IN OUT NOCOPY quota_rule_rec_type,
719 x_return_status OUT NOCOPY VARCHAR2,
720 x_msg_count OUT NOCOPY NUMBER,
721 x_msg_data OUT NOCOPY VARCHAR2
722 )
723 IS
724 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Quota_Rule';
725 l_api_version CONSTANT NUMBER := 1.0;
726 BEGIN
727 -- Standard Start of API savepoint
728 SAVEPOINT create_quota_rule;
729
730 -- Standard call to check for call compatibility.
731 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
732 THEN
733 RAISE fnd_api.g_exc_unexpected_error;
734 END IF;
735
736 -- Initialize message list if p_init_msg_list is set to TRUE.
737 IF fnd_api.to_boolean (p_init_msg_list)
738 THEN
739 fnd_msg_pub.initialize;
740 END IF;
741
742 -- Initialize API return status to success
743 x_return_status := fnd_api.g_ret_sts_success;
744
745 -- get the primary key if you dont already have it
746 IF p_quota_rule.quota_rule_id IS NULL
747 THEN
748 SELECT cn_quota_rules_s.NEXTVAL
749 INTO p_quota_rule.quota_rule_id
750 FROM DUAL;
751 END IF;
752
753 -- validate the record before inserting
754 validate_quota_rule (p_api_version => p_api_version,
755 p_quota_rule => p_quota_rule,
756 p_action => 'CREATE',
757 x_return_status => x_return_status,
758 x_msg_count => x_msg_count,
759 x_msg_data => x_msg_data
760 );
761
762 IF (x_return_status <> fnd_api.g_ret_sts_success)
763 THEN
764 RAISE fnd_api.g_exc_error;
765 END IF;
766
767 -- call table handler to insert data
768 cn_quota_rules_pkg.begin_record (x_operation => 'INSERT',
769 x_object_version_number => p_quota_rule.object_version_number,
770 x_quota_rule_id => p_quota_rule.quota_rule_id,
771 x_quota_id => p_quota_rule.quota_id,
772 x_org_id => p_quota_rule.org_id,
773 x_revenue_class_id => p_quota_rule.revenue_class_id,
774 x_revenue_class_name => p_quota_rule.revenue_class_name,
775 x_target => p_quota_rule.target,
776 x_revenue_class_id_old => NULL,
777 x_target_old => NULL,
778 x_payment_amount => p_quota_rule.payment_amount,
779 x_performance_goal => p_quota_rule.performance_goal,
780 x_last_update_date => g_last_update_date,
781 x_last_updated_by => g_last_updated_by,
782 x_creation_date => g_creation_date,
783 x_created_by => g_created_by,
784 x_last_update_login => g_last_update_login,
785 x_program_type => g_program_type,
786 x_status_code => NULL,
787 x_payment_amount_old => NULL,
788 x_performance_goal_old => NULL
789 );
790
791 IF (x_return_status <> fnd_api.g_ret_sts_success)
792 THEN
793 RAISE fnd_api.g_exc_error;
794 END IF;
795
796 -- Calling proc to add system note for create
797 add_system_note(
798 p_quota_rule,
799 p_quota_rule,
800 'create',
801 x_return_status,
802 x_msg_count,
803 x_msg_data
804 );
805 IF (x_return_status <> fnd_api.g_ret_sts_success)
806 THEN
807 RAISE fnd_api.g_exc_error;
808 END IF;
809
810 -- End of API body.
811 -- Standard check of p_commit.
812 IF fnd_api.to_boolean (p_commit)
813 THEN
814 COMMIT WORK;
815 END IF;
816
817 -- Standard call to get message count and if count is 1, get message info.
818 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
819 EXCEPTION
820 WHEN fnd_api.g_exc_error
821 THEN
822 ROLLBACK TO create_quota_rule;
823 x_return_status := fnd_api.g_ret_sts_error;
824 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
825 WHEN fnd_api.g_exc_unexpected_error
826 THEN
827 ROLLBACK TO create_quota_rule;
828 x_return_status := fnd_api.g_ret_sts_unexp_error;
829 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
830 WHEN OTHERS
831 THEN
832 ROLLBACK TO create_quota_rule;
833 x_return_status := fnd_api.g_ret_sts_unexp_error;
834
835 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
836 THEN
837 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
838 END IF;
839
840 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
841 END create_quota_rule;
842
843 -- Start of comments
844 -- API name : Create_Quota_Rules
845 -- Type : Private.
846 -- Function :
847 -- Pre-reqs : None.
848 -- Parameters :
849 -- IN : p_api_version IN NUMBER Required
850 -- p_init_msg_list IN VARCHAR2 Optional
851 -- Default = FND_API.G_FALSE
852 -- p_commit IN VARCHAR2 Optional
853 -- Default = FND_API.G_FALSE
854 -- p_validation_level IN NUMBER Optional
855 -- Default = FND_API.G_VALID_LEVEL_FULL
856 -- p_revenue_class_rec_tbl IN cn_plan_element_pub.revenue_class_rec_tbl_typ
857 -- OUT : x_return_status OUT VARCHAR2(1)
858 -- x_msg_count OUT NUMBER
859 -- x_msg_data OUT VARCHAR2(2000)
860 -- x_quota_rule_id OUT NUMBER
861 -- Version : Current version 1.0
862 -- Notes : Note text
863 --
864 -- End of comments
865 PROCEDURE create_quota_rules (
866 p_api_version IN NUMBER,
867 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
868 p_commit IN VARCHAR2 := fnd_api.g_false,
869 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
870 p_quota_name IN VARCHAR2,
871 p_revenue_class_rec_tbl IN cn_plan_element_pub.revenue_class_rec_tbl_type := cn_plan_element_pub.g_miss_revenue_class_rec_tbl,
872 p_rev_uplift_rec_tbl IN cn_plan_element_pub.rev_uplift_rec_tbl_type := cn_plan_element_pub.g_miss_rev_uplift_rec_tbl,
873 p_trx_factor_rec_tbl IN cn_plan_element_pub.trx_factor_rec_tbl_type := cn_plan_element_pub.g_miss_trx_factor_rec_tbl,
874 x_loading_status OUT NOCOPY VARCHAR2,
875 x_return_status OUT NOCOPY VARCHAR2,
876 x_msg_count OUT NOCOPY NUMBER,
877 x_msg_data OUT NOCOPY VARCHAR2
878 )
879 IS
880 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Quota_Rules';
881 l_api_version CONSTANT NUMBER := 1.0;
882 l_revclass_rec quota_rule_rec_type;
883 l_loading_status VARCHAR2 (80);
884 BEGIN
885 --
886 -- Standard Start of API savepoint
887 -- +
888 SAVEPOINT create_plan_element;
889
890 --+
891 -- Standard call to check for call compatibility.
892 --+
893 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
894 THEN
895 RAISE fnd_api.g_exc_unexpected_error;
896 END IF;
897
898 --+
899 -- Initialize message list if p_init_msg_list is set to TRUE.
900 -- +
901 IF fnd_api.to_boolean (p_init_msg_list)
902 THEN
903 fnd_msg_pub.initialize;
904 END IF;
905
906 -- +
907 -- Initialize API return status to success
908 --+
909 x_return_status := fnd_api.g_ret_sts_success;
910 x_loading_status := 'CN_INSERTED';
911
912 -- +
913 -- API body
914 -- +
915 IF (p_revenue_class_rec_tbl.COUNT <> 0)
916 THEN
917 -- Loop through each record and check go through the normal validations
918 -- and etc.
919 FOR i IN p_revenue_class_rec_tbl.FIRST .. p_revenue_class_rec_tbl.LAST
920 LOOP
921 -- convert the user input into the local record
922 l_revclass_rec :=
923 convert_rev_class_user_input (p_quota_name => p_quota_name,
924 p_revenue_class_rec => p_revenue_class_rec_tbl (i),
925 x_loading_status => x_loading_status
926 );
927
928 -- Check return status and insert if the status is CN_INSERTED
929 -- then inser the Quota Rules, Insert the trx
930 --ELSE Record Already exists, but Trx count > 0
931 -- Update trx factors
932 -- EXLSE Record Already Exists
933 IF (x_return_status <> fnd_api.g_ret_sts_success)
934 THEN
935 RAISE fnd_api.g_exc_error;
936 END IF;
937
938 IF (x_loading_status = g_quota_rule_not_exists)
939 THEN
940 -- call create_quota_rule
941 cn_quota_rule_pvt.create_quota_rule (p_api_version => p_api_version,
942 p_init_msg_list => p_init_msg_list,
943 p_commit => p_commit,
944 p_validation_level => p_validation_level,
945 p_quota_rule => l_revclass_rec,
946 x_return_status => x_return_status,
947 x_msg_count => x_msg_count,
948 x_msg_data => x_msg_data
949 );
950
951 IF (x_return_status <> fnd_api.g_ret_sts_success)
952 THEN
953 x_loading_status := 'CN_UPDATE_FAILED';
954 RAISE fnd_api.g_exc_error;
955 END IF;
956
957 -- call create_trx_factors
958 cn_trx_factor_pvt.update_trx_factors (p_api_version => p_api_version,
959 p_init_msg_list => p_init_msg_list,
960 p_commit => p_commit,
961 p_validation_level => p_validation_level,
962 p_trx_factor_rec_tbl => p_trx_factor_rec_tbl,
963 p_org_id => l_revclass_rec.org_id,
964 p_quota_name => l_revclass_rec.plan_element_name,
965 p_revenue_class_name => l_revclass_rec.revenue_class_name,
966 x_return_status => x_return_status,
967 x_msg_count => x_msg_count,
968 x_msg_data => x_msg_data,
969 x_loading_status => l_loading_status
970 );
971 x_loading_status := l_loading_status;
972 ELSIF (x_loading_status = g_quota_rule_exists)
973 THEN
974 IF (p_trx_factor_rec_tbl.COUNT = 0 AND p_rev_uplift_rec_tbl.COUNT = 0)
975 THEN
976 RAISE fnd_api.g_exc_error;
977 ELSIF p_trx_factor_rec_tbl.COUNT <> 0
978 THEN
979 -- Custom trx factors it means we need to update
980 -- exisiting trx factors.
981 NULL;
982 -- Taken care in the calling Place.
983 ELSIF p_rev_uplift_rec_tbl.COUNT > 0
984 THEN
985 x_loading_status := 'CN_INSERTED'; -- Calling Place will handle this
986 END IF;
987
988 x_loading_status := 'PLN_QUOTA_REV_EXISTS'; -- Case
989 END IF; -- CN_INSERTED.
990 END LOOP; -- Revenue Class
991 END IF; -- Table Count is Not Zero
992
993 -- End of API body.
994 -- Standard check of p_commit.
995 IF fnd_api.to_boolean (p_commit)
996 THEN
997 COMMIT WORK;
998 END IF;
999
1000 --+
1001 -- Standard call to get message count and if count is 1, get message info.
1002 --+
1003 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1004 EXCEPTION
1005 WHEN fnd_api.g_exc_error
1006 THEN
1007 ROLLBACK TO create_quota_rules;
1008 x_return_status := fnd_api.g_ret_sts_error;
1009 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1010 WHEN fnd_api.g_exc_unexpected_error
1011 THEN
1012 ROLLBACK TO create_quota_rules;
1013 x_loading_status := 'UNEXPECTED_ERR';
1014 x_return_status := fnd_api.g_ret_sts_unexp_error;
1015 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1016 WHEN OTHERS
1017 THEN
1018 ROLLBACK TO create_quota_rules;
1019 x_loading_status := 'UNEXPECTED_ERR';
1020 x_return_status := fnd_api.g_ret_sts_unexp_error;
1021
1022 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1023 THEN
1024 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1025 END IF;
1026
1027 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1028 END create_quota_rules;
1029
1030 -- Start of comments
1031 -- API name : Update_Quota_Rule
1032 -- Type : Private.
1033 -- Function :
1034 -- Pre-reqs : None.
1035 -- Parameters :
1036 -- IN : p_api_version IN NUMBER Required
1037 -- p_init_msg_list IN VARCHAR2 Optional
1038 -- Default = FND_API.G_FALSE
1039 -- p_commit IN VARCHAR2 Optional
1040 -- Default = FND_API.G_FALSE
1041 -- p_validation_level IN NUMBER Optional
1042 -- Default = FND_API.G_VALID_LEVEL_FULL
1043 -- p_quota_rule IN quota_rule_rec_type
1044 -- OUT : x_return_status OUT VARCHAR2(1)
1045 -- x_msg_count OUT NUMBER
1046 -- x_msg_data OUT VARCHAR2(2000)
1047 -- Version : Current version 1.0
1048 -- Notes : Note text
1049 --
1050 -- End of comments
1051 PROCEDURE update_quota_rule (
1052 p_api_version IN NUMBER,
1053 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1054 p_commit IN VARCHAR2 := fnd_api.g_false,
1055 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1056 p_quota_rule IN OUT NOCOPY quota_rule_rec_type,
1057 x_return_status OUT NOCOPY VARCHAR2,
1058 x_msg_count OUT NOCOPY NUMBER,
1059 x_msg_data OUT NOCOPY VARCHAR2
1060 )
1061 IS
1062 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Quota_Rule';
1063 l_api_version CONSTANT NUMBER := 1.0;
1064
1065 CURSOR l_old_quota_rule_cr
1066 IS
1067 SELECT *
1068 FROM cn_quota_rules
1069 WHERE quota_rule_id = p_quota_rule.quota_rule_id;
1070
1071 l_old_quota_rule l_old_quota_rule_cr%ROWTYPE;
1072 l_old_rec quota_rule_rec_type;
1073 BEGIN
1074 -- Standard Start of API savepoint
1075 SAVEPOINT update_quota_rule;
1076
1077 -- Standard call to check for call compatibility.
1078 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1079 THEN
1080 RAISE fnd_api.g_exc_unexpected_error;
1081 END IF;
1082
1083 -- Initialize message list if p_init_msg_list is set to TRUE.
1084 IF fnd_api.to_boolean (p_init_msg_list)
1085 THEN
1086 fnd_msg_pub.initialize;
1087 END IF;
1088
1089 -- Initialize API return status to success
1090 x_return_status := fnd_api.g_ret_sts_success;
1091
1092 -- API body
1093 OPEN l_old_quota_rule_cr;
1094
1095 FETCH l_old_quota_rule_cr
1096 INTO l_old_quota_rule;
1097
1098 CLOSE l_old_quota_rule_cr;
1099
1100 l_old_rec.quota_rule_id := l_old_quota_rule.quota_rule_id;
1101 l_old_rec.revenue_class_name := l_old_quota_rule.NAME;
1102 l_old_rec.revenue_class_id := l_old_quota_rule.revenue_class_id;
1103 l_old_rec.quota_id := l_old_quota_rule.quota_id;
1104 l_old_rec.description := l_old_quota_rule.description;
1105 l_old_rec.target := l_old_quota_rule.target;
1106 l_old_rec.payment_amount := l_old_quota_rule.payment_amount;
1107 l_old_rec.performance_goal := l_old_quota_rule.performance_goal;
1108 l_old_rec.object_version_number := l_old_quota_rule.object_version_number;
1109 l_old_rec.org_id := l_old_quota_rule.org_id;
1110 -- validate this update
1111 validate_quota_rule (p_api_version => p_api_version,
1112 p_quota_rule => p_quota_rule,
1113 p_old_quota_rule => l_old_rec,
1114 p_action => 'UPDATE',
1115 x_return_status => x_return_status,
1116 x_msg_count => x_msg_count,
1117 x_msg_data => x_msg_data
1118 );
1119
1120 IF (x_return_status <> fnd_api.g_ret_sts_success)
1121 THEN
1122 RAISE fnd_api.g_exc_error;
1123 END IF;
1124
1125 -- update table using the handler
1126 cn_quota_rules_pkg.begin_record (x_operation => 'UPDATE',
1127 x_object_version_number => p_quota_rule.object_version_number,
1128 x_quota_rule_id => p_quota_rule.quota_rule_id,
1129 x_quota_id => p_quota_rule.quota_id,
1130 x_org_id => p_quota_rule.org_id,
1131 x_revenue_class_id => p_quota_rule.revenue_class_id,
1132 x_revenue_class_name => p_quota_rule.revenue_class_name,
1133 x_target => p_quota_rule.target,
1134 x_payment_amount => p_quota_rule.payment_amount,
1135 x_performance_goal => p_quota_rule.performance_goal,
1136 x_revenue_class_id_old => l_old_rec.revenue_class_id,
1137 x_target_old => l_old_rec.target,
1138 x_last_update_date => g_last_update_date,
1139 x_last_updated_by => g_last_updated_by,
1140 x_creation_date => l_old_quota_rule.creation_date,
1141 x_created_by => l_old_quota_rule.created_by,
1142 x_last_update_login => g_last_update_login,
1143 x_program_type => g_program_type,
1144 x_status_code => NULL,
1145 x_payment_amount_old => NULL,
1146 x_performance_goal_old => NULL
1147 );
1148
1149 -- Calling proc to add system note for update
1150 IF (l_old_rec.revenue_class_id <> p_quota_rule.revenue_class_id) THEN
1151 add_system_note(
1152 l_old_rec,
1153 p_quota_rule,
1154 'update',
1155 x_return_status,
1156 x_msg_count,
1157 x_msg_data
1158 );
1159 END IF;
1160
1161 IF (x_return_status <> fnd_api.g_ret_sts_success)
1162 THEN
1163 RAISE fnd_api.g_exc_error;
1164 END IF;
1165
1166
1167 -- End of API body.
1168 -- Standard check of p_commit.
1169 IF fnd_api.to_boolean (p_commit)
1170 THEN
1171 COMMIT WORK;
1172 END IF;
1173
1174 -- Standard call to get message count and if count is 1, get message info.
1175 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1176 EXCEPTION
1177 WHEN fnd_api.g_exc_error
1178 THEN
1179 ROLLBACK TO update_quota_rule;
1180 x_return_status := fnd_api.g_ret_sts_error;
1181 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1182 WHEN fnd_api.g_exc_unexpected_error
1183 THEN
1184 ROLLBACK TO update_quota_rule;
1185 x_return_status := fnd_api.g_ret_sts_unexp_error;
1186 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1187 WHEN OTHERS
1188 THEN
1189 ROLLBACK TO update_quota_rule;
1190 x_return_status := fnd_api.g_ret_sts_unexp_error;
1191
1192 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1193 THEN
1194 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1195 END IF;
1196
1197 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1198 END update_quota_rule;
1199
1200 --|-------------------------------------------------------------------------+
1201 --| Procedure Name: Update_Quota_Rules
1202 --| Descr: Update a Quota Rules
1203 --|-------------------------------------------------------------------------+
1204 PROCEDURE update_quota_rules (
1205 p_api_version IN NUMBER,
1206 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1207 p_commit IN VARCHAR2 := fnd_api.g_false,
1208 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1209 p_quota_name IN VARCHAR2,
1210 p_revenue_class_rec_tbl IN cn_plan_element_pub.revenue_class_rec_tbl_type := cn_plan_element_pub.g_miss_revenue_class_rec_tbl,
1211 p_trx_factor_rec_tbl IN cn_plan_element_pub.trx_factor_rec_tbl_type := cn_plan_element_pub.g_miss_trx_factor_rec_tbl,
1212 x_loading_status OUT NOCOPY VARCHAR2,
1213 x_return_status OUT NOCOPY VARCHAR2,
1214 x_msg_count OUT NOCOPY NUMBER,
1215 x_msg_data OUT NOCOPY VARCHAR2
1216 )
1217 IS
1218 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Quota_Rules';
1219 l_api_version CONSTANT NUMBER := 1.0;
1220 l_revclass_rec quota_rule_rec_type;
1221 l_quota_rule_id NUMBER;
1222 l_rev_class_id_old NUMBER;
1223 l_loading_status VARCHAR2 (80);
1224 BEGIN
1225 --
1226 -- Standard Start of API savepoint
1227 -- +
1228 SAVEPOINT update_quota_rules;
1229
1230 --+
1231 -- Standard call to check for call compatibility.
1232 --+
1233 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1234 THEN
1235 RAISE fnd_api.g_exc_unexpected_error;
1236 END IF;
1237
1238 --+
1239 -- Initialize message list if p_init_msg_list is set to TRUE.
1240 -- +
1241 IF fnd_api.to_boolean (p_init_msg_list)
1242 THEN
1243 fnd_msg_pub.initialize;
1244 END IF;
1245
1246 -- +
1247 -- Initialize API return status to success
1248 --+
1249 x_return_status := fnd_api.g_ret_sts_success;
1250 x_loading_status := 'CN_UPDATED';
1251
1252 -- +
1253 -- API body
1254 -- +
1255 IF (p_revenue_class_rec_tbl.COUNT <> 0)
1256 THEN
1257 FOR i IN p_revenue_class_rec_tbl.FIRST .. p_revenue_class_rec_tbl.LAST
1258 LOOP
1259 -- Convert the User input into the local variable.
1260 l_revclass_rec :=
1261 convert_rev_class_user_input (p_quota_name => p_quota_name,
1262 p_revenue_class_rec => p_revenue_class_rec_tbl (i),
1263 p_old_revenue_class_name => p_revenue_class_rec_tbl (i).rev_class_name_old,
1264 x_loading_status => l_loading_status
1265 );
1266 x_loading_status := l_loading_status;
1267
1268 IF (x_loading_status = g_quota_rule_exists)
1269 THEN
1270 cn_quota_rule_pvt.update_quota_rule (p_api_version => p_api_version,
1271 p_init_msg_list => p_init_msg_list,
1272 p_commit => p_commit,
1273 p_validation_level => p_validation_level,
1274 p_quota_rule => l_revclass_rec,
1275 x_return_status => x_return_status,
1276 x_msg_count => x_msg_count,
1277 x_msg_data => x_msg_data
1278 );
1279 x_loading_status := 'CN_UPDATED';
1280
1281 IF (x_return_status <> fnd_api.g_ret_sts_success)
1282 THEN
1283 x_loading_status := 'CN_UPDATE_FAILED';
1284 RAISE fnd_api.g_exc_error;
1285 END IF;
1286
1287 cn_trx_factor_pvt.update_trx_factors (p_api_version => p_api_version,
1288 p_init_msg_list => p_init_msg_list,
1289 p_commit => p_commit,
1290 p_validation_level => p_validation_level,
1291 p_trx_factor_rec_tbl => p_trx_factor_rec_tbl,
1292 p_org_id => l_revclass_rec.org_id,
1293 p_quota_name => l_revclass_rec.plan_element_name,
1294 p_revenue_class_name => l_revclass_rec.revenue_class_name,
1295 x_return_status => x_return_status,
1296 x_msg_count => x_msg_count,
1297 x_msg_data => x_msg_data,
1298 x_loading_status => l_loading_status
1299 );
1300 x_loading_status := l_loading_status;
1301
1302 IF (x_return_status <> fnd_api.g_ret_sts_success)
1303 THEN
1304 RAISE fnd_api.g_exc_error;
1305 END IF;
1306 ELSIF x_loading_status = g_quota_rule_exists
1307 THEN
1308 IF (p_trx_factor_rec_tbl.COUNT = 0 AND p_revenue_class_rec_tbl.COUNT = 0)
1309 THEN
1310 RAISE fnd_api.g_exc_error;
1311 END IF;
1312
1313 x_loading_status := 'PLN_QUOTA_REV_EXISTS';
1314 END IF; -- Not success
1315 END LOOP; -- Revenue Class
1316 END IF; -- Table Count is Not Zero
1317
1318 -- End of API body.
1319 -- Standard check of p_commit.
1320 IF fnd_api.to_boolean (p_commit)
1321 THEN
1322 COMMIT WORK;
1323 END IF;
1324
1325 --+
1326 -- Standard call to get message count and if count is 1, get message info.
1327 --+
1328 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1329 EXCEPTION
1330 WHEN fnd_api.g_exc_error
1331 THEN
1332 ROLLBACK TO update_quota_rules;
1333 x_return_status := fnd_api.g_ret_sts_error;
1334 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1335 WHEN fnd_api.g_exc_unexpected_error
1336 THEN
1337 ROLLBACK TO update_quota_rules;
1338 x_loading_status := 'UNEXPECTED_ERR';
1339 x_return_status := fnd_api.g_ret_sts_unexp_error;
1340 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1341 WHEN OTHERS
1342 THEN
1343 ROLLBACK TO update_quota_rules;
1344 x_loading_status := 'UNEXPECTED_ERR';
1345 x_return_status := fnd_api.g_ret_sts_unexp_error;
1346
1347 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1348 THEN
1349 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1350 END IF;
1351
1352 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1353 END update_quota_rules;
1354
1355 -- Start of comments
1356 -- API name : Delete_Quota_Rule
1357 -- Type : Private.
1358 -- Function :
1359 -- Pre-reqs : None.
1360 -- Parameters :
1361 -- IN : p_api_version IN NUMBER Required
1362 -- p_init_msg_list IN VARCHAR2 Optional
1363 -- Default = FND_API.G_FALSE
1364 -- p_commit IN VARCHAR2 Optional
1365 -- Default = FND_API.G_FALSE
1366 -- p_validation_level IN NUMBER Optional
1367 -- Default = FND_API.G_VALID_LEVEL_FULL
1368 -- p_quota_rule IN quota_rule_rec_type
1369 -- OUT : x_return_status OUT VARCHAR2(1)
1370 -- x_msg_count OUT NUMBER
1371 -- x_msg_data OUT VARCHAR2(2000)
1372 -- Version : Current version 1.0
1373 -- Notes : Note text
1374 --
1375 -- End of comments
1376 PROCEDURE delete_quota_rule (
1377 p_api_version IN NUMBER,
1378 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1379 p_commit IN VARCHAR2 := fnd_api.g_false,
1380 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1381 p_quota_rule IN OUT NOCOPY quota_rule_rec_type,
1382 x_return_status OUT NOCOPY VARCHAR2,
1383 x_msg_count OUT NOCOPY NUMBER,
1384 x_msg_data OUT NOCOPY VARCHAR2
1385 )
1386 IS
1387 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Quota_Rule';
1388 l_api_version CONSTANT NUMBER := 1.0;
1389 l_quota_id NUMBER;
1390 l_revenue_class_id NUMBER;
1391 l_quota_name cn_quotas.NAME%TYPE;
1392 BEGIN
1393 -- Standard Start of API savepoint
1394 SAVEPOINT delete_quota_rule;
1395
1396 -- Standard call to check for call compatibility.
1397 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1398 THEN
1399 RAISE fnd_api.g_exc_unexpected_error;
1400 END IF;
1401
1402 -- Initialize message list if p_init_msg_list is set to TRUE.
1403 IF fnd_api.to_boolean (p_init_msg_list)
1404 THEN
1405 fnd_msg_pub.initialize;
1406 END IF;
1407
1408 -- Initialize API return status to success
1409 x_return_status := fnd_api.g_ret_sts_success;
1410 -- API body
1411 validate_quota_rule (p_api_version => p_api_version,
1412 p_quota_rule => p_quota_rule,
1413 p_action => 'DELETE',
1414 x_return_status => x_return_status,
1415 x_msg_count => x_msg_count,
1416 x_msg_data => x_msg_data
1417 );
1418
1419 IF (x_return_status <> fnd_api.g_ret_sts_success)
1420 THEN
1421 RAISE fnd_api.g_exc_error;
1422 END IF;
1423
1424 -- Delete Record;
1425 cn_quota_rules_pkg.DELETE_RECORD (x_quota_id => p_quota_rule.quota_id,
1426 x_quota_rule_id => p_quota_rule.quota_rule_id,
1427 x_revenue_class_id => p_quota_rule.revenue_class_id
1428 );
1429
1430 -- Calling proc to add system note for delete
1431 add_system_note(
1432 p_quota_rule,
1433 p_quota_rule,
1434 'delete',
1435 x_return_status,
1436 x_msg_count,
1437 x_msg_data
1438 );
1439 IF (x_return_status <> fnd_api.g_ret_sts_success)
1440 THEN
1441 RAISE fnd_api.g_exc_error;
1442 END IF;
1443
1444 -- End of API body.
1445 -- Standard check of p_commit.
1446 IF fnd_api.to_boolean (p_commit)
1447 THEN
1448 COMMIT WORK;
1449 END IF;
1450
1451 -- Standard call to get message count and if count is 1, get message info.
1452 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1453 EXCEPTION
1454 WHEN fnd_api.g_exc_error
1455 THEN
1456 ROLLBACK TO delete_quota_rule;
1457 x_return_status := fnd_api.g_ret_sts_error;
1458 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1459 WHEN fnd_api.g_exc_unexpected_error
1460 THEN
1461 ROLLBACK TO delete_quota_rule;
1462 x_return_status := fnd_api.g_ret_sts_unexp_error;
1463 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1464 WHEN OTHERS
1465 THEN
1466 ROLLBACK TO delete_quota_rule;
1467 x_return_status := fnd_api.g_ret_sts_unexp_error;
1468
1469 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1470 THEN
1471 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1472 END IF;
1473
1474 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1475 END delete_quota_rule;
1476
1477 --|-------------------------------------------------------------------------+
1478 --| Procedure Name: Delete_Quota_Rules
1479 --| Descr: Delete a Quota Rules
1480 --|-------------------------------------------------------------------------+
1481 PROCEDURE delete_quota_rules (
1482 p_api_version IN NUMBER,
1483 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1484 p_commit IN VARCHAR2 := fnd_api.g_false,
1485 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1486 p_quota_name IN VARCHAR2,
1487 p_revenue_class_rec_tbl IN cn_plan_element_pub.revenue_class_rec_tbl_type,
1488 x_return_status OUT NOCOPY VARCHAR2,
1489 x_msg_count OUT NOCOPY NUMBER,
1490 x_msg_data OUT NOCOPY VARCHAR2,
1491 x_loading_status OUT NOCOPY VARCHAR2
1492 )
1493 IS
1494 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Plan_Element';
1495 l_api_version CONSTANT NUMBER := 1.0;
1496 l_loading_status VARCHAR2 (80);
1497 l_rec quota_rule_rec_type;
1498 BEGIN
1499 -- Standard Start of API savepoint
1500 SAVEPOINT delete_plan_element;
1501
1502 -- Standard call to check for call compatibility.
1503 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1504 THEN
1505 RAISE fnd_api.g_exc_unexpected_error;
1506 END IF;
1507
1508 -- Initialize message list if p_init_msg_list is set to TRUE.
1509 IF fnd_api.to_boolean (p_init_msg_list)
1510 THEN
1511 fnd_msg_pub.initialize;
1512 END IF;
1513
1514 -- Initialize API return status to success
1515 x_return_status := fnd_api.g_ret_sts_success;
1516 x_loading_status := 'CN_DELETED';
1517
1518 -- API body
1519
1520 -- Loop Through Each Record and Delete IT
1521 IF p_revenue_class_rec_tbl.COUNT > 0
1522 THEN
1523 FOR i IN 1 .. p_revenue_class_rec_tbl.COUNT
1524 LOOP
1525 l_rec :=
1526 convert_rev_class_user_input (p_quota_name => p_quota_name,
1527 p_revenue_class_rec => p_revenue_class_rec_tbl (i),
1528 x_loading_status => x_loading_status
1529 );
1530 -- call the private api
1531 cn_quota_rule_pvt.delete_quota_rule (p_api_version => p_api_version,
1532 p_init_msg_list => p_init_msg_list,
1533 p_commit => p_commit,
1534 p_validation_level => p_validation_level,
1535 p_quota_rule => l_rec,
1536 x_return_status => x_return_status,
1537 x_msg_count => x_msg_count,
1538 x_msg_data => x_msg_data
1539 );
1540
1541 IF (x_return_status <> fnd_api.g_ret_sts_success)
1542 THEN
1543 x_loading_status := 'QUOTA_RULE_DELETE_FAILED';
1544 RAISE fnd_api.g_exc_error;
1545 END IF;
1546 END LOOP;
1547 END IF;
1548
1549 -- standard Commit
1550 IF fnd_api.to_boolean (p_commit)
1551 THEN
1552 COMMIT WORK;
1553 END IF;
1554
1555 --+
1556 -- Standard call to get message count and if count is 1, get message info.
1557 --+
1558 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1559 EXCEPTION
1560 WHEN fnd_api.g_exc_error
1561 THEN
1562 ROLLBACK TO delete_plan_element;
1563 x_return_status := fnd_api.g_ret_sts_error;
1564 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1565 WHEN fnd_api.g_exc_unexpected_error
1566 THEN
1567 ROLLBACK TO delete_plan_element;
1568 x_loading_status := 'UNEXPECTED_ERR';
1569 x_return_status := fnd_api.g_ret_sts_unexp_error;
1570 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1571 WHEN OTHERS
1572 THEN
1573 ROLLBACK TO delete_plan_element;
1574 x_loading_status := 'UNEXPECTED_ERR';
1575 x_return_status := fnd_api.g_ret_sts_unexp_error;
1576
1577 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1578 THEN
1579 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1580 END IF;
1581
1582 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1583 END delete_quota_rules;
1584 END cn_quota_rule_pvt;