[Home] [Help]
PACKAGE BODY: APPS.CN_QUOTA_RULE_UPLIFTS_GRP
Source
1 PACKAGE BODY cn_quota_rule_uplifts_grp AS
2 /* $Header: cnxgqrub.pls 120.5.12020000.2 2012/08/27 09:02:39 nbombili ship $ */
3 g_pkg_name CONSTANT VARCHAR2 (50) := 'CN_QUOTA_RULES_UPLIFTS_GRP';
4 g_file_name CONSTANT VARCHAR2 (12) := 'cnxgqrub.pls';
5 g_program_type VARCHAR2 (30);
6
7 /* ****************** */
8 /* ADDED - SBADAMI */
9 /* ****************** */
10 -- API name : check_status
11 -- Type : Private
12 -- Pre-reqs : None.
13 -- Function : Raises error based on different statuses
14 -- Parameters :
15 -- IN : p_return_status IN VARCHAR2 Required
16 -- Version : Initial version 1.0
17 -- End of comments
18 PROCEDURE check_status (
19 p_return_status IN VARCHAR2
20 )
21 IS
22 BEGIN
23 IF p_return_status = fnd_api.g_ret_sts_error
24 THEN
25 RAISE fnd_api.g_exc_error;
26 ELSIF p_return_status = fnd_api.g_ret_sts_unexp_error
27 THEN
28 RAISE fnd_api.g_exc_unexpected_error;
29 END IF;
30 END;
31
32 -- ----------------------------------------------------------------------------+
33 -- Function : convert_pe_user_input
34 -- Desc : function to trim all blank spaces of user input
35 -- Assign defalut value if input is missing
36 -- ----------------------------------------------------------------------------+
37 FUNCTION convert_rev_uplift_user_input (
38 p_quota_name IN VARCHAR2,
39 p_rev_uplift_rec IN cn_quota_rule_uplift_pvt.quota_rule_uplift_rec_type,
40 x_return_status OUT NOCOPY VARCHAR2,
41 p_loading_status IN VARCHAR2,
42 x_loading_status OUT NOCOPY VARCHAR2
43 )
44 RETURN cn_chk_plan_element_pkg.pe_rec_type
45 IS
46 l_pe_rec cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec;
47 l_name VARCHAR2 (2000);
48 l_ret_val boolean := false;
49 BEGIN
50
51 -- Check if Org Id given correctly or not
52 l_ret_val := CN_OU_UTIL_PVT.is_valid_org(p_org_id => p_rev_uplift_rec.org_id);
53
54 -- First make sure you have an org_id to drive all searches
55 cn_chk_plan_element_pkg.validate_org_id (p_rev_uplift_rec.org_id);
56 l_pe_rec.org_id := p_rev_uplift_rec.org_id;
57 -- Convert the User Input.
58 x_return_status := fnd_api.g_ret_sts_success;
59 x_loading_status := p_loading_status;
60 -- Removing leading and trailing blanks
61 l_pe_rec.NAME := LTRIM (RTRIM (p_quota_name));
62 l_pe_rec.rev_class_name := LTRIM (RTRIM (p_rev_uplift_rec.rev_class_name));
63 l_pe_rec.rev_uplift_start_date := p_rev_uplift_rec.start_date;
64 l_pe_rec.rev_uplift_end_date := p_rev_uplift_rec.end_date;
65 -- Get the Revenue Class Id
66 l_pe_rec.rev_class_id := cn_api.get_rev_class_id (l_pe_rec.rev_class_name, p_rev_uplift_rec.org_id);
67 -- Get the Quota Id
68 l_pe_rec.quota_id := cn_chk_plan_element_pkg.get_quota_id (l_pe_rec.NAME, l_pe_rec.org_id);
69 -- Get the Quota Rule Id
70 l_pe_rec.quota_rule_id := cn_chk_plan_element_pkg.get_quota_rule_id (p_quota_id => l_pe_rec.quota_id, p_rev_class_id => l_pe_rec.rev_class_id);
71
72 -- Set the Payment Uplift Factor if the Input value is Null or G_MISS_NUM
73 -- Set the Quota Uplift Factor if the Input value is Null or G_MISS_NUM
74 -- Combined the two queries
75 SELECT DECODE (p_rev_uplift_rec.payment_factor, fnd_api.g_miss_num, 100, NULL, 100, p_rev_uplift_rec.payment_factor),
76 DECODE (p_rev_uplift_rec.quota_factor, fnd_api.g_miss_num, 100, NULL, 100, p_rev_uplift_rec.quota_factor)
77 INTO l_pe_rec.rev_class_payment_uplift,
78 l_pe_rec.rev_class_quota_uplift
79 FROM SYS.DUAL;
80
81 RETURN l_pe_rec;
82 END convert_rev_uplift_user_input;
83
84 -- ----------------------------------------------------------------------------+
85 -- Procedure: valid_quota_rule_uplifts
86 -- Desc : Validate the Quto Rules uplift Input Parameters like
87 -- Revenue Class Name,
88 -- Plan Element Name.
89 -- ----------------------------------------------------------------------------+
90 PROCEDURE valid_quota_rule_uplift (
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2,
94 p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type,
95 p_quota_rule_uplift_id IN NUMBER,
96 p_rev_class_name_old IN VARCHAR2,
97 p_start_date_old IN DATE,
98 p_end_date_old IN DATE,
99 p_loading_status IN VARCHAR2,
100 x_loading_status OUT NOCOPY VARCHAR2
101 )
102 IS
103 l_api_name CONSTANT VARCHAR2 (30) := 'valid_quota_Rule_uplifts';
104 l_same_pe NUMBER;
105 l_end_date DATE;
106
107 CURSOR quota_rule_uplifts_seq_curs (
108 p_quota_rule_id NUMBER
109 )
110 IS
111 SELECT end_date
112 FROM cn_quota_rule_uplifts
113 WHERE quota_rule_id = p_quota_rule_id
114 ORDER BY start_date DESC;
115
116 l_date_msg VARCHAR2 (100);
117 l_loading_status VARCHAR2 (80);
118 BEGIN
119 -- Initialize API return status to success
120 x_return_status := fnd_api.g_ret_sts_success;
121 x_loading_status := p_loading_status;
122
123 -- API body
124 -- check for required data in Plan Element Name
125 -- Check MISS and NULL ( Revenue class Name, Quota Name )
126 IF ((cn_api.chk_miss_char_para (p_char_para => p_pe_rec.NAME,
127 p_para_name => cn_chk_plan_element_pkg.g_pe_name,
128 p_loading_status => x_loading_status,
129 x_loading_status => l_loading_status
130 )
131 ) = fnd_api.g_true
132 )
133 THEN
134 RAISE fnd_api.g_exc_error;
135 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_pe_rec.NAME,
136 p_obj_name => cn_chk_plan_element_pkg.g_pe_name,
137 p_loading_status => x_loading_status,
138 x_loading_status => l_loading_status
139 )
140 ) = fnd_api.g_true
141 )
142 THEN
143 RAISE fnd_api.g_exc_error;
144 END IF;
145
146 --+
147 -- Check Valid Plan Element Name
148 --+
149 IF p_pe_rec.NAME IS NOT NULL AND p_pe_rec.quota_id IS NULL
150 THEN
151 -- Error, check the msg level and add an error message to the
152 -- API message list
153 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
154 THEN
155 fnd_message.set_name ('CN', 'CN_PLN_NOT_EXIST');
156 fnd_message.set_token ('PE_NAME', p_pe_rec.NAME);
157 fnd_msg_pub.ADD;
158 END IF;
159
160 x_loading_status := 'PLN_NOT_EXIST';
161 RAISE fnd_api.g_exc_error;
162 END IF;
163
164 --+
165 -- Check Revenue Class name is null or miss char
166 --+
167 IF ((cn_api.chk_miss_char_para (p_char_para => p_pe_rec.rev_class_name,
168 p_para_name => cn_chk_plan_element_pkg.g_rev_cls_name,
169 p_loading_status => x_loading_status,
170 x_loading_status => l_loading_status
171 )
172 ) = fnd_api.g_true
173 )
174 THEN
175 RAISE fnd_api.g_exc_error;
176 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_pe_rec.rev_class_name,
177 p_obj_name => cn_chk_plan_element_pkg.g_rev_cls_name,
178 p_loading_status => x_loading_status,
179 x_loading_status => l_loading_status
180 )
181 ) = fnd_api.g_true
182 )
183 THEN
184 RAISE fnd_api.g_exc_error;
185 END IF;
186
187 --+
188 -- Check Valid Revenue Class Name
189 --+
190 IF p_pe_rec.rev_class_name IS NOT NULL AND p_pe_rec.rev_class_id IS NULL
191 THEN
192 -- Error, check the msg level and add an error message to the
193 -- API message list
194 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
195 THEN
196 fnd_message.set_name ('CN', 'CN_REV_CLASS_NOT_EXIST');
197 fnd_msg_pub.ADD;
198 END IF;
199
200 x_loading_status := 'REV_CLASS_NOT_EXIST';
201 RAISE fnd_api.g_exc_error;
202 END IF;
203
204 --+
205 -- Check Start Date can not be missing or NULL
206 --+
207 IF ((cn_chk_plan_element_pkg.chk_miss_date_para (p_date_para => p_pe_rec.rev_uplift_start_date,
208 p_para_name => cn_chk_plan_element_pkg.g_uplift_start_date,
209 p_loading_status => x_loading_status,
210 x_loading_status => l_loading_status
211 )
212 ) = fnd_api.g_true
213 )
214 THEN
215 RAISE fnd_api.g_exc_error;
216 ELSIF ((cn_chk_plan_element_pkg.chk_null_date_para (p_date_para => p_pe_rec.rev_uplift_start_date,
217 p_obj_name => cn_chk_plan_element_pkg.g_uplift_start_date,
218 p_loading_status => x_loading_status,
219 x_loading_status => l_loading_status
220 )
221 ) = fnd_api.g_true
222 )
223 THEN
224 RAISE fnd_api.g_exc_error;
225 END IF;
226
227 --+
228 -- Check End Date must be Grater than Start Date
229 --+
230 IF p_pe_rec.rev_uplift_end_date IS NOT NULL
231 THEN
232 IF (TRUNC (p_pe_rec.rev_uplift_end_date) < TRUNC (p_pe_rec.rev_uplift_start_date))
233 THEN
234 -- Error, check the msg level and add an error message to the
235 -- API message list
236 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
237 THEN
238 fnd_message.set_name ('CN', 'CN_INVALID_DATE_RANGE');
239 fnd_msg_pub.ADD;
240 END IF;
241
242 x_loading_status := 'INVALID_DATE_RANGE';
243 RAISE fnd_api.g_exc_error;
244 END IF;
245 END IF;
246
247 --+
248 -- Check payment Uplift
249 --+
250 IF (cn_api.chk_miss_num_para (p_num_para => p_pe_rec.rev_class_payment_uplift,
251 p_para_name => cn_chk_plan_element_pkg.g_uplift_payment_factor,
252 p_loading_status => x_loading_status,
253 x_loading_status => l_loading_status
254 ) = fnd_api.g_true
255 )
256 THEN
257 RAISE fnd_api.g_exc_error;
258 ELSIF ((cn_api.chk_null_num_para (p_num_para => p_pe_rec.rev_class_payment_uplift,
259 p_obj_name => cn_chk_plan_element_pkg.g_uplift_payment_factor,
260 p_loading_status => x_loading_status,
261 x_loading_status => l_loading_status
262 )
263 ) = fnd_api.g_true
264 )
265 THEN
266 RAISE fnd_api.g_exc_error;
267 END IF;
268
269 --+
270 -- Check Quota Uplift
271 --+
272 IF (cn_api.chk_miss_num_para (p_num_para => p_pe_rec.rev_class_quota_uplift,
273 p_para_name => cn_chk_plan_element_pkg.g_uplift_quota_factor,
274 p_loading_status => x_loading_status,
275 x_loading_status => l_loading_status
276 ) = fnd_api.g_true
277 )
278 THEN
279 RAISE fnd_api.g_exc_error;
280 ELSIF ((cn_api.chk_null_num_para (p_num_para => p_pe_rec.rev_class_quota_uplift,
281 p_obj_name => cn_chk_plan_element_pkg.g_uplift_quota_factor,
282 p_loading_status => x_loading_status,
283 x_loading_status => l_loading_status
284 )
285 ) = fnd_api.g_true
286 )
287 THEN
288 RAISE fnd_api.g_exc_error;
289 END IF;
290
291 --+
292 -- Check for Duplicate Record for the Same Quota Rule with the same start date and end Date.
293 -- Duplicate check in update has been taken care in the other place .
294 IF p_rev_class_name_old IS NULL AND p_start_date_old IS NULL
295 THEN
296 SELECT COUNT (*)
297 INTO l_same_pe
298 FROM cn_quota_rule_uplifts qru
299 WHERE qru.quota_rule_id = p_pe_rec.quota_rule_id
300 AND TRUNC (qru.start_date) = TRUNC (p_pe_rec.rev_uplift_start_date)
301 AND qru.quota_rule_uplift_id <> NVL (p_quota_rule_uplift_id, 0);
302
303 IF l_same_pe <> 0
304 THEN
305 IF p_pe_rec.rev_uplift_end_date IS NOT NULL
306 THEN
307 l_date_msg := p_pe_rec.rev_uplift_start_date || '; End Date: ' || p_pe_rec.rev_uplift_end_date;
308 ELSE
309 l_date_msg := p_pe_rec.rev_uplift_start_date;
310 END IF;
311
312 -- Error, check the msg level and add an error message to the
313 -- API message list
314 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
315 THEN
316 fnd_message.set_name ('CN', 'CN_QUOTA_UPLIFT_EXISTS');
317 fnd_message.set_token ('PLAN_NAME', p_pe_rec.NAME);
318 fnd_message.set_token ('REVENUE_CLASS_NAME', p_pe_rec.rev_class_name);
319 fnd_message.set_token ('START_DATE', l_date_msg);
320 fnd_msg_pub.ADD;
321 END IF;
322
323 x_loading_status := 'QUOTA_UPLIFT_EXISTS';
324 RETURN;
325 END IF;
326 END IF;
327
328 --+
329 -- Check date Effectivity
330 --+
331 cn_chk_plan_element_pkg.chk_date_effective (x_return_status => x_return_status,
332 p_start_date => p_pe_rec.rev_uplift_start_date,
333 p_end_date => p_pe_rec.rev_uplift_end_date,
334 p_quota_id => p_pe_rec.quota_id,
335 p_object_type => 'UPLIFT',
336 p_loading_status => x_loading_status,
337 x_loading_status => l_loading_status
338 );
339 x_loading_status := l_loading_status;
340
341 IF (x_return_status <> fnd_api.g_ret_sts_success)
342 THEN
343 RAISE fnd_api.g_exc_error;
344 END IF;
345 EXCEPTION
346 WHEN fnd_api.g_exc_error
347 THEN
348 x_return_status := fnd_api.g_ret_sts_error;
349 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
350 WHEN fnd_api.g_exc_unexpected_error
351 THEN
352 x_return_status := fnd_api.g_ret_sts_unexp_error;
353 x_loading_status := 'UNEXPECTED_ERR';
354 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
355 WHEN OTHERS
356 THEN
357 x_return_status := fnd_api.g_ret_sts_unexp_error;
358 x_loading_status := 'UNEXPECTED_ERR';
359
360 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
361 THEN
362 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
363 END IF;
364
365 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
366 END valid_quota_rule_uplift;
367
368 -- ----------------------------------------------------------------------------+
369 -- Procedure: Check Valid Update
370 -- Desc :This procedure is called from update Quota Rule Uplifts.
371 -- Additional validation During Update
372 -- Called from UPDATE_QUOTA_RULE_UPLIFTS
373 -- ----------------------------------------------------------------------------+
374 PROCEDURE check_valid_update (
375 x_return_status OUT NOCOPY VARCHAR2,
376 x_msg_count OUT NOCOPY NUMBER,
377 x_msg_data OUT NOCOPY VARCHAR2,
378 p_quota_name IN VARCHAR2,
379 p_rev_class_name_old IN VARCHAR2,
380 p_start_date_old IN DATE,
381 p_end_date_old IN DATE,
382 p_new_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec,
383 x_quota_rule_uplift_id OUT NOCOPY NUMBER,
384 p_loading_status IN VARCHAR2,
385 x_loading_status OUT NOCOPY VARCHAR2
386 )
387 IS
388 l_api_name CONSTANT VARCHAR2 (30) := 'Check_Valid_Update';
389 l_same_pe NUMBER;
390 l_rev_class_id_old NUMBER;
391 l_quota_rule_id_old NUMBER;
392 l_quota_rule_uplift_id_old NUMBER;
393 l_date_msg VARCHAR2 (100);
394 l_loading_status VARCHAR2 (80);
395 l_org_id NUMBER;
396 BEGIN
397 --+
398 -- Initialize API return status to success
399 --+
400 x_return_status := fnd_api.g_ret_sts_success;
401 x_loading_status := p_loading_status;
402 --+
403 -- Get revenue Class ID
404 --+
405 l_rev_class_id_old := cn_api.get_rev_class_id (p_rev_class_name_old, p_new_pe_rec.org_id);
406 --+
407 -- Get quota rule ID
408 -- +
409 l_quota_rule_id_old := cn_chk_plan_element_pkg.get_quota_rule_id (p_quota_id => p_new_pe_rec.quota_id, p_rev_class_id => l_rev_class_id_old);
410
411 -- +
412 -- Check the old revenue class name is not null or miss char
413 --+
414 IF ((cn_api.chk_miss_char_para (p_char_para => p_rev_class_name_old,
415 p_para_name => cn_chk_plan_element_pkg.g_rev_cls_name,
416 p_loading_status => x_loading_status,
417 x_loading_status => l_loading_status
418 )
419 ) = fnd_api.g_true
420 )
421 THEN
422 RAISE fnd_api.g_exc_error;
423 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_rev_class_name_old,
424 p_obj_name => cn_chk_plan_element_pkg.g_rev_cls_name,
425 p_loading_status => x_loading_status,
426 x_loading_status => l_loading_status
427 )
428 ) = fnd_api.g_true
429 )
430 THEN
431 RAISE fnd_api.g_exc_error;
432 END IF;
433
434 --+
435 -- Check Old Start Date cannot be missing or NULL
436 --+
437 IF ((cn_chk_plan_element_pkg.chk_miss_date_para (p_date_para => p_start_date_old,
438 p_para_name => cn_chk_plan_element_pkg.g_uplift_start_date,
439 p_loading_status => x_loading_status,
440 x_loading_status => l_loading_status
441 )
442 ) = fnd_api.g_true
443 )
444 THEN
445 RAISE fnd_api.g_exc_error;
446 ELSIF ((cn_chk_plan_element_pkg.chk_null_date_para (p_date_para => p_start_date_old,
447 p_obj_name => cn_chk_plan_element_pkg.g_uplift_start_date,
448 p_loading_status => x_loading_status,
449 x_loading_status => l_loading_status
450 )
451 ) = fnd_api.g_true
452 )
453 THEN
454 RAISE fnd_api.g_exc_error;
455 END IF;
456
457 --+
458 -- Check the passed revenue class name is exists in the database
459 --+
460 IF p_rev_class_name_old IS NOT NULL AND l_rev_class_id_old IS NULL
461 THEN
462 -- Error, check the msg level and add an error message to the
463 -- API message list
464 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
465 THEN
466 fnd_message.set_name ('CN', 'CN_REV_CLASS_NOT_EXIST');
467 fnd_msg_pub.ADD;
468 END IF;
469
470 x_loading_status := 'REV_CLASS_NOT_EXIST';
471 RAISE fnd_api.g_exc_error;
472 END IF;
473
474 --+
475 -- Check the quota rule uplift id if the New Quota rule is exists.
476 --+
477 x_quota_rule_uplift_id :=
478 cn_chk_plan_element_pkg.get_quota_rule_uplift_id (p_new_pe_rec.quota_rule_id,
479 p_new_pe_rec.rev_uplift_start_date,
480 p_new_pe_rec.rev_uplift_end_date
481 );
482 --+
483 -- get the Quota Rule Uplift id using the Old values
484 --+
485 l_quota_rule_uplift_id_old := cn_chk_plan_element_pkg.get_quota_rule_uplift_id (l_quota_rule_id_old, p_start_date_old, p_end_date_old);
486
487 -- Error message if the Quota Rule Uplift Does Not exists in the Database
488 IF l_quota_rule_uplift_id_old IS NULL
489 THEN
490 IF p_end_date_old IS NOT NULL
491 THEN
492 l_date_msg := p_start_date_old || ' and end date ' || p_end_date_old;
493 ELSE
494 l_date_msg := p_start_date_old;
495 END IF;
496
497 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
498 THEN
499 fnd_message.set_name ('CN', 'CN_QUOTA_UPLIFT_NOT_EXIST');
500 fnd_message.set_token ('PLAN_NAME', p_quota_name);
501 fnd_message.set_token ('REVENUE_CLASS_NAME', p_rev_class_name_old);
502 fnd_message.set_token ('START_DATE', l_date_msg);
503 fnd_msg_pub.ADD;
504 END IF;
505
506 x_loading_status := 'QUOTA_RULE_UPLIFT_NOT_EXIST';
507 RAISE fnd_api.g_exc_error;
508 END IF;
509
510 -- Chances for duplicate record in the database is
511 -- case 1 if the old quota rule id
512 IF x_quota_rule_uplift_id IS NOT NULL AND x_quota_rule_uplift_id <> l_quota_rule_uplift_id_old
513 THEN
514 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
515 THEN
516 fnd_message.set_name ('CN', 'CN_QUOTA_UPLIFT_EXISTS');
517 fnd_msg_pub.ADD;
518 END IF;
519
520 x_loading_status := 'QUOTA_UPLIFT_EXISTS';
521 RAISE fnd_api.g_exc_error;
522 ELSE
523 x_quota_rule_uplift_id := l_quota_rule_uplift_id_old;
524 END IF;
525
526 --+
527 -- Call the Default validation, it has to pass all the rules
528 --+
529 valid_quota_rule_uplift (x_return_status => x_return_status,
530 x_msg_count => x_msg_count,
531 x_msg_data => x_msg_data,
532 p_pe_rec => p_new_pe_rec,
533 p_quota_rule_uplift_id => l_quota_rule_uplift_id_old,
534 p_rev_class_name_old => p_rev_class_name_old,
535 p_start_date_old => p_start_date_old,
536 p_end_date_old => p_end_date_old,
537 p_loading_status => x_loading_status,
538 x_loading_status => l_loading_status
539 );
540 x_loading_status := l_loading_status;
541
542 IF (x_return_status <> fnd_api.g_ret_sts_success)
543 THEN
544 RAISE fnd_api.g_exc_error;
545 END IF;
546
547 -- End of API body.
548 -- Standard call to get message count and if count is 1, get message info.
549 --+
550 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
551 EXCEPTION
552 WHEN fnd_api.g_exc_error
553 THEN
554 x_return_status := fnd_api.g_ret_sts_error;
555 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
556 WHEN fnd_api.g_exc_unexpected_error
557 THEN
558 x_return_status := fnd_api.g_ret_sts_unexp_error;
559 x_loading_status := 'UNEXPECTED_ERR';
560 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
561 WHEN OTHERS
562 THEN
563 x_return_status := fnd_api.g_ret_sts_unexp_error;
564 x_loading_status := 'UNEXPECTED_ERR';
565
566 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
567 THEN
568 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
569 END IF;
570
571 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
572 END check_valid_update;
573
574 -- ----------------------------------------------------------------------------+
575 --
576 -- Procedure Name: Create_Quota_Rule_uplift
577 --
578 -- ----------------------------------------------------------------------------+
579 PROCEDURE create_quota_rule_uplift (
580 p_api_version IN NUMBER,
581 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
582 p_commit IN VARCHAR2 := fnd_api.g_false,
583 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
584 x_return_status OUT NOCOPY VARCHAR2,
585 x_msg_count OUT NOCOPY NUMBER,
586 x_msg_data OUT NOCOPY VARCHAR2,
587 p_quota_name IN VARCHAR2,
588 p_rev_uplift_rec_tbl IN OUT NOCOPY cn_quota_rule_uplift_pvt.quota_rule_uplift_tbl_type,
589 x_loading_status OUT NOCOPY VARCHAR2
590 )
591 IS
592 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Quota_Rule_uplift';
593 l_api_version CONSTANT NUMBER := 1.0;
594 l_pe_rec cn_chk_plan_element_pkg.pe_rec_type;
595 --l_quota_rule_uplift_id NUMBER;
596 l_uplift_date_seq_rec_tbl uplift_date_seq_rec_tbl_type;
597 l_loading_status VARCHAR2 (80);
598 BEGIN
599 --
600 -- Standard Start of API savepoint
601 -- +
602 SAVEPOINT create_quota_rule_uplift;
603
604 --+
605 -- Standard call to check for call compatibility.
606 --+
607 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
608 THEN
609 RAISE fnd_api.g_exc_unexpected_error;
610 END IF;
611
612 --+
613 -- Initialize message list if p_init_msg_list is set to TRUE.
614 -- +
615 IF fnd_api.to_boolean (p_init_msg_list)
616 THEN
617 fnd_msg_pub.initialize;
618 END IF;
619
620 -- +
621 -- Initialize API return status to success
622 --+
623 x_return_status := fnd_api.g_ret_sts_success;
624 x_loading_status := 'CN_INSERTED';
625
626 -- +
627 -- API body
628 -- +
629 IF (p_rev_uplift_rec_tbl.COUNT <> 0)
630 THEN
631 FOR i IN p_rev_uplift_rec_tbl.FIRST .. p_rev_uplift_rec_tbl.LAST
632 LOOP
633 l_pe_rec :=
634 convert_rev_uplift_user_input (p_quota_name => p_quota_name,
635 p_rev_uplift_rec => p_rev_uplift_rec_tbl (i),
636 x_return_status => x_return_status,
637 p_loading_status => x_loading_status,
638 x_loading_status => l_loading_status
639 );
640 x_loading_status := l_loading_status;
641
642 IF (x_return_status <> fnd_api.g_ret_sts_success)
643 THEN
644 RAISE fnd_api.g_exc_error;
645 END IF;
646
647 --+
648 -- Validate the quota rule uplifts.
649 --+
650 valid_quota_rule_uplift (x_return_status => x_return_status,
651 x_msg_count => x_msg_count,
652 x_msg_data => x_msg_data,
653 p_pe_rec => l_pe_rec,
654 p_quota_rule_uplift_id => p_rev_uplift_rec_tbl (i).quota_rule_uplift_id,
655 p_rev_class_name_old => NULL,
656 p_start_date_old => NULL,
657 p_end_date_old => NULL,
658 p_loading_status => x_loading_status,
659 x_loading_status => l_loading_status
660 );
661 x_loading_status := l_loading_status;
662
663 -- raise error is status <> success
664 IF (x_return_status <> fnd_api.g_ret_sts_success)
665 THEN
666 RAISE fnd_api.g_exc_error;
667 ELSIF x_loading_status <> 'QUOTA_UPLIFT_EXISTS'
668 THEN
669 cn_quota_rule_uplifts_pkg.begin_record (x_operation => 'INSERT',
670 x_org_id => l_pe_rec.org_id,
671 x_quota_rule_uplift_id => p_rev_uplift_rec_tbl (i).quota_rule_uplift_id,
672 x_quota_rule_id => l_pe_rec.quota_rule_id,
673 x_quota_rule_id_old => l_pe_rec.quota_rule_id,
674 x_start_date => l_pe_rec.rev_uplift_start_date,
675 x_start_date_old => l_pe_rec.rev_uplift_start_date,
676 x_end_date => l_pe_rec.rev_uplift_end_date,
677 x_end_date_old => l_pe_rec.rev_uplift_end_date,
678 x_payment_factor => l_pe_rec.rev_class_payment_uplift,
679 x_payment_factor_old => l_pe_rec.rev_class_payment_uplift,
680 x_quota_factor => l_pe_rec.rev_class_quota_uplift,
681 x_quota_factor_old => l_pe_rec.rev_class_quota_uplift,
682 x_last_updated_by => fnd_global.user_id,
683 x_creation_date => SYSDATE,
684 x_created_by => fnd_global.user_id,
685 x_last_update_login => fnd_global.login_id,
686 x_last_update_date => SYSDATE,
687 x_program_type => g_program_type,
688 x_status_code => NULL,
689 x_object_version_number => p_rev_uplift_rec_tbl (i).object_version_number
690 );
691 l_uplift_date_seq_rec_tbl (i).start_date := l_pe_rec.rev_uplift_start_date;
692 l_uplift_date_seq_rec_tbl (i).start_date_old := p_rev_uplift_rec_tbl (i).start_date_old;
693 l_uplift_date_seq_rec_tbl (i).end_date := l_pe_rec.rev_uplift_end_date;
694 l_uplift_date_seq_rec_tbl (i).end_date_old := p_rev_uplift_rec_tbl (i).end_date_old;
695 l_uplift_date_seq_rec_tbl (i).quota_rule_id := l_pe_rec.quota_rule_id;
696 l_uplift_date_seq_rec_tbl (i).quota_rule_uplift_id := p_rev_uplift_rec_tbl (i).quota_rule_uplift_id;
697 ELSE
698 RAISE fnd_api.g_exc_error;
699 END IF;
700 END LOOP;
701
702 -- We need to check one level After than
703 -- +
704 -- Check the Sequence, are there any records exists before this
705 -- record, if exists it should be
706 --+
707 -- FOR i IN 1 .. l_uplift_date_seq_rec_tbl.COUNT
708 -- LOOP
709 FOR i IN l_uplift_date_seq_rec_tbl.FIRST..l_uplift_date_seq_rec_tbl.LAST LOOP
710 IF (( TRUNC (l_uplift_date_seq_rec_tbl (i).start_date_old) <> TRUNC (l_uplift_date_seq_rec_tbl (i).start_date)
711 OR NVL (TRUNC (l_uplift_date_seq_rec_tbl (i).end_date_old), fnd_api.g_miss_date) <>
712 NVL (TRUNC (l_uplift_date_seq_rec_tbl (i).end_date), fnd_api.g_miss_date)
713 )
714 )
715 THEN
716 --commented by Naren to fix bug 13606519
717 /*
718 cn_chk_plan_element_pkg.chk_uplift_iud (x_return_status => x_return_status,
719 p_start_date => l_uplift_date_seq_rec_tbl (i).start_date,
720 p_end_date => l_uplift_date_seq_rec_tbl (i).end_date,
721 p_iud_flag => 'I',
722 p_quota_rule_id => l_pe_rec.quota_rule_id,
723 p_quota_rule_uplift_id => p_rev_uplift_rec_tbl (i).quota_rule_uplift_id,
724 p_loading_status => x_loading_status,
725 x_loading_status => l_loading_status
726 );
727
728 */
729
730 --Added by Naren to fix bug 13606519
731
732 cn_chk_plan_element_pkg.chk_uplift_iud (x_return_status => x_return_status,
733 p_start_date => l_uplift_date_seq_rec_tbl (i).start_date,
734 p_end_date => l_uplift_date_seq_rec_tbl (i).end_date,
735 p_iud_flag => 'I',
736 p_quota_rule_id => l_uplift_date_seq_rec_tbl (i).quota_rule_id, --Here corresponding quote id has to be passed, not the last code id
737 p_quota_rule_uplift_id => p_rev_uplift_rec_tbl (i).quota_rule_uplift_id,
738 p_loading_status => x_loading_status,
739 x_loading_status => l_loading_status
740 );
741 --Addition ends here
742
743 x_loading_status := l_loading_status;
744
745 IF (x_return_status <> fnd_api.g_ret_sts_success)
746 THEN
747 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
748 THEN
749 fnd_message.set_name ('CN', 'CN_INVALID_DATE_SEQUENCE');
750 fnd_msg_pub.ADD;
751 END IF;
752
753 x_loading_status := 'CN_UPLIFT_UPDATE_NOT_ALLOWED';
754 RAISE fnd_api.g_exc_error;
755 END IF;
756 END IF;
757 END LOOP;
758 END IF; -- Table Count is Not Zero
759
760 -- End of API body.
761 -- Standard check of p_commit.
762 IF fnd_api.to_boolean (p_commit)
763 THEN
764 COMMIT WORK;
765 END IF;
766
767 <<end_api_body>>
768 NULL;
769 --+
770 -- Standard call to get message count and if count is 1, get message info.
771 --+
772 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
773 EXCEPTION
774 WHEN fnd_api.g_exc_error
775 THEN
776 ROLLBACK TO create_quota_rule_uplift;
777 x_return_status := fnd_api.g_ret_sts_error;
778 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
779 WHEN fnd_api.g_exc_unexpected_error
780 THEN
781 ROLLBACK TO create_quota_rule_uplift;
782 x_loading_status := 'UNEXPECTED_ERR';
783 x_return_status := fnd_api.g_ret_sts_unexp_error;
784 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
785 WHEN OTHERS
786 THEN
787 ROLLBACK TO create_quota_rule_uplift;
788 x_loading_status := 'UNEXPECTED_ERR';
789 x_return_status := fnd_api.g_ret_sts_unexp_error;
790
791 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
792 THEN
793 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
794 END IF;
795
796 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
797 END create_quota_rule_uplift;
798
799 -- ----------------------------------------------------------------------------+
800 --
801 -- Procedure Name: Update_Quota_Rule_uplift
802 --
803 -- ----------------------------------------------------------------------------+
804 PROCEDURE update_quota_rule_uplift (
805 p_api_version IN NUMBER,
806 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
807 p_commit IN VARCHAR2 := fnd_api.g_false,
808 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
809 x_return_status OUT NOCOPY VARCHAR2,
810 x_msg_count OUT NOCOPY NUMBER,
811 x_msg_data OUT NOCOPY VARCHAR2,
812 p_quota_name IN VARCHAR2,
813 p_rev_uplift_rec_tbl IN OUT NOCOPY cn_quota_rule_uplift_pvt.quota_rule_uplift_tbl_type,
814 x_loading_status OUT NOCOPY VARCHAR2
815 )
816 IS
817 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Quota_Rule_uplift';
818 l_api_version CONSTANT NUMBER := 1.0;
819 l_pe_rec cn_chk_plan_element_pkg.pe_rec_type;
820 --l_quota_rule_uplift_id NUMBER;
821 l_uplift_date_seq_rec_tbl uplift_date_seq_rec_tbl_type;
822 l_loading_status VARCHAR2 (80);
823 BEGIN
824 --
825 -- Standard Start of API savepoint
826 -- +
827 SAVEPOINT update_quota_rule_uplift;
828
829 --+
830 -- Standard call to check for call compatibility.
831 --+
832 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
833 THEN
834 RAISE fnd_api.g_exc_unexpected_error;
835 END IF;
836
837 --+
838 -- Initialize message list if p_init_msg_list is set to TRUE.
839 -- +
840 IF fnd_api.to_boolean (p_init_msg_list)
841 THEN
842 fnd_msg_pub.initialize;
843 END IF;
844
845 -- +
846 -- Initialize API return status to success
847 --+
848 x_return_status := fnd_api.g_ret_sts_success;
849 x_loading_status := 'CN_UPDATED';
850
851 -- +
852 -- API body
853 -- +
854 IF (p_rev_uplift_rec_tbl.COUNT <> 0)
855 THEN
856 -- Loop through each record
857 FOR i IN p_rev_uplift_rec_tbl.FIRST .. p_rev_uplift_rec_tbl.LAST
858 LOOP
859 -- Convert each record in the local variable
860 l_pe_rec :=
861 convert_rev_uplift_user_input (p_quota_name => p_quota_name,
862 p_rev_uplift_rec => p_rev_uplift_rec_tbl (i),
863 x_return_status => x_return_status,
864 p_loading_status => x_loading_status,
865 x_loading_status => l_loading_status
866 );
867 x_loading_status := l_loading_status;
868
869 IF (x_return_status <> fnd_api.g_ret_sts_success)
870 THEN
871 RAISE fnd_api.g_exc_error;
872 END IF;
873
874 --validate
875 check_valid_update (x_return_status => x_return_status,
876 x_msg_count => x_msg_count,
877 x_msg_data => x_msg_data,
878 p_quota_name => p_quota_name,
879 p_rev_class_name_old => p_rev_uplift_rec_tbl (i).rev_class_name_old,
880 p_start_date_old => p_rev_uplift_rec_tbl (i).start_date_old,
881 p_end_date_old => p_rev_uplift_rec_tbl (i).end_date_old,
882 p_new_pe_rec => l_pe_rec,
883 x_quota_rule_uplift_id => p_rev_uplift_rec_tbl (i).quota_rule_uplift_id,
884 p_loading_status => x_loading_status,
885 x_loading_status => l_loading_status
886 );
887 x_loading_status := l_loading_status;
888
889 -- If not success the Raise error
890 -- if PLN_QUOTA_UPLIFT_EXISTS
891 IF (x_return_status <> fnd_api.g_ret_sts_success)
892 THEN
893 RAISE fnd_api.g_exc_error;
894 ELSIF x_loading_status = 'CN_UPDATED'
895 THEN
896 cn_quota_rule_uplifts_pkg.begin_record (x_operation => 'UPDATE',
897 x_org_id => l_pe_rec.org_id,
898 x_quota_rule_uplift_id => p_rev_uplift_rec_tbl (i).quota_rule_uplift_id,
899 x_quota_rule_id => l_pe_rec.quota_rule_id,
900 x_quota_rule_id_old => l_pe_rec.quota_rule_id,
901 x_start_date => l_pe_rec.rev_uplift_start_date,
902 x_start_date_old => l_pe_rec.rev_uplift_start_date,
903 x_end_date => l_pe_rec.rev_uplift_end_date,
904 x_end_date_old => l_pe_rec.rev_uplift_end_date,
905 x_payment_factor => l_pe_rec.rev_class_payment_uplift,
906 x_payment_factor_old => l_pe_rec.rev_class_payment_uplift,
907 x_quota_factor => l_pe_rec.rev_class_quota_uplift,
908 x_quota_factor_old => l_pe_rec.rev_class_quota_uplift,
909 x_last_updated_by => fnd_global.user_id,
910 x_creation_date => SYSDATE,
911 x_created_by => fnd_global.user_id,
912 x_last_update_login => fnd_global.login_id,
913 x_last_update_date => SYSDATE,
914 x_program_type => g_program_type,
915 x_status_code => NULL,
916 x_object_version_number => p_rev_uplift_rec_tbl (i).object_version_number
917 );
918 l_uplift_date_seq_rec_tbl (i).start_date := l_pe_rec.rev_uplift_start_date;
919 l_uplift_date_seq_rec_tbl (i).start_date_old := p_rev_uplift_rec_tbl (i).start_date_old;
920 l_uplift_date_seq_rec_tbl (i).end_date := l_pe_rec.rev_uplift_end_date;
921 l_uplift_date_seq_rec_tbl (i).end_date_old := p_rev_uplift_rec_tbl (i).end_date_old;
922 l_uplift_date_seq_rec_tbl (i).quota_rule_id := l_pe_rec.quota_rule_id;
923 l_uplift_date_seq_rec_tbl (i).quota_rule_uplift_id := p_rev_uplift_rec_tbl (i).quota_rule_uplift_id;
924 ELSE
925 RAISE fnd_api.g_exc_error;
926 END IF;
927 END LOOP;
928
929 FOR i IN 1 .. l_uplift_date_seq_rec_tbl.COUNT
930 LOOP
931 IF (( TRUNC (l_uplift_date_seq_rec_tbl (i).start_date_old) <> TRUNC (l_uplift_date_seq_rec_tbl (i).start_date)
932 OR NVL (TRUNC (l_uplift_date_seq_rec_tbl (i).end_date_old), fnd_api.g_miss_date) <>
933 NVL (TRUNC (l_uplift_date_seq_rec_tbl (i).end_date), fnd_api.g_miss_date)
934 )
935 )
936 THEN
937 /*
938 cn_chk_plan_element_pkg.chk_uplift_iud (x_return_status => x_return_status,
939 p_start_date => l_uplift_date_seq_rec_tbl (i).start_date,
940 p_end_date => l_uplift_date_seq_rec_tbl (i).end_date,
941 p_iud_flag => 'U',
942 p_quota_rule_id => l_pe_rec.quota_rule_id,
943 p_quota_rule_uplift_id => p_rev_uplift_rec_tbl (i).quota_rule_uplift_id,
944 p_loading_status => x_loading_status,
945 x_loading_status => l_loading_status
946 );
947 x_loading_status := l_loading_status;
948 */
949 IF (x_return_status <> fnd_api.g_ret_sts_success)
950 THEN
951 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
952 THEN
953 fnd_message.set_name ('CN', 'CN_INVALID_DATE_SEQUENCE');
954 fnd_msg_pub.ADD;
955 END IF;
956
957 x_loading_status := 'INVALID_DATE_SEQUENCE';
958 RAISE fnd_api.g_exc_error;
959 END IF;
960 END IF;
961 END LOOP;
962 END IF; -- Table Count is Not Zero
963
964 -- End of API body.
965 -- Standard check of p_commit.
966 --+
967 IF fnd_api.to_boolean (p_commit)
968 THEN
969 COMMIT WORK;
970 END IF;
971
972 --+
973 -- Standard call to get message count and if count is 1, get message info.
974 --+
975 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
976 EXCEPTION
977 WHEN fnd_api.g_exc_error
978 THEN
979 ROLLBACK TO update_quota_rule_uplift;
980 x_return_status := fnd_api.g_ret_sts_error;
981 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
982 WHEN fnd_api.g_exc_unexpected_error
983 THEN
984 ROLLBACK TO update_quota_rule_uplift;
985 x_loading_status := 'UNEXPECTED_ERR';
986 x_return_status := fnd_api.g_ret_sts_unexp_error;
987 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
988 WHEN OTHERS
989 THEN
990 ROLLBACK TO update_quota_rule_uplift;
991 x_loading_status := 'UNEXPECTED_ERR';
992 x_return_status := fnd_api.g_ret_sts_unexp_error;
993
994 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
995 THEN
996 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
997 END IF;
998
999 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1000 END update_quota_rule_uplift;
1001
1002 -- ----------------------------------------------------------------------------+
1003 --
1004 -- Procedure Name: Delete_Quota_Rule_uplift
1005 --
1006 -- ----------------------------------------------------------------------------+
1007 PROCEDURE delete_quota_rule_uplift (
1008 p_api_version IN NUMBER,
1009 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1010 p_commit IN VARCHAR2 := fnd_api.g_false,
1011 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1012 x_return_status OUT NOCOPY VARCHAR2,
1013 x_msg_count OUT NOCOPY NUMBER,
1014 x_msg_data OUT NOCOPY VARCHAR2,
1015 p_quota_name IN VARCHAR2,
1016 p_rev_uplift_rec_tbl IN OUT NOCOPY cn_quota_rule_uplift_pvt.quota_rule_uplift_tbl_type,
1017 x_loading_status OUT NOCOPY VARCHAR2
1018 )
1019 IS
1020 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Quota_rule_uplift';
1021 l_api_version CONSTANT NUMBER := 1.0;
1022 l_quota_rule_uplift_id NUMBER;
1023 l_pe_rec cn_chk_plan_element_pkg.pe_rec_type;
1024 l_date_msg VARCHAR2 (100);
1025 l_loading_status VARCHAR2 (80);
1026 BEGIN
1027 -- Standard Start of API savepoint
1028 SAVEPOINT delete_quota_rule_uplift;
1029
1030 -- Standard call to check for call compatibility.
1031 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1032 THEN
1033 RAISE fnd_api.g_exc_unexpected_error;
1034 END IF;
1035
1036 -- Initialize message list if p_init_msg_list is set to TRUE.
1037 IF fnd_api.to_boolean (p_init_msg_list)
1038 THEN
1039 fnd_msg_pub.initialize;
1040 END IF;
1041
1042 -- Initialize API return status to success
1043 x_return_status := fnd_api.g_ret_sts_success;
1044 x_loading_status := 'CN_DELETED';
1045
1046 -- API body
1047 -- Store the User Input Value into The Local Variable.
1048 -- Standard check of p_commit.
1049 --+
1050 IF (p_rev_uplift_rec_tbl.COUNT <> 0)
1051 THEN
1052 -- Loop through each record we get from the Procedure call
1053 FOR i IN p_rev_uplift_rec_tbl.FIRST .. p_rev_uplift_rec_tbl.LAST
1054 LOOP
1055 -- Convert each record in the local variable with more necessary things.
1056 l_pe_rec :=
1057 convert_rev_uplift_user_input (p_quota_name => p_quota_name,
1058 p_rev_uplift_rec => p_rev_uplift_rec_tbl (i),
1059 x_return_status => x_return_status,
1060 p_loading_status => x_loading_status,
1061 x_loading_status => l_loading_status
1062 );
1063 x_loading_status := l_loading_status;
1064 -- get Quota Rule Uplift ID
1065 l_quota_rule_uplift_id :=
1066 cn_chk_plan_element_pkg.get_quota_rule_uplift_id (l_pe_rec.quota_rule_id, l_pe_rec.rev_uplift_start_date, l_pe_rec.rev_uplift_end_date);
1067
1068 -- if the Quota Rule uplift iD is null then Error message
1069 IF l_quota_rule_uplift_id IS NULL
1070 THEN
1071 -- The following if has been commented during R12 development
1072 -- as it is not needed.
1073 -- There were hard coded strings and also the if with start_date is
1074 -- not null is incorrect
1075 /*
1076 IF l_pe_rec.rev_uplift_start_date IS NOT NULL
1077 THEN
1078 l_date_msg := l_pe_rec.rev_uplift_start_date || ' and end date ' || l_pe_rec.rev_uplift_end_date;
1079 ELSE
1080 l_date_msg := l_pe_rec.rev_uplift_start_date;
1081 END IF;
1082 */
1083 l_date_msg := l_pe_rec.rev_uplift_start_date;
1084
1085 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1086 THEN
1087 fnd_message.set_name ('CN', 'CN_QUOTA_UPLIFT_NOT_EXIST');
1088 fnd_message.set_token ('PLAN_NAME', l_pe_rec.NAME);
1089 fnd_message.set_token ('REVENUE_CLASS_NAME', l_pe_rec.rev_class_name);
1090 fnd_message.set_token ('START_DATE', l_date_msg);
1091 fnd_msg_pub.ADD;
1092 END IF;
1093
1094 x_loading_status := 'QUOTA_UPLIFT_NOT_EXIST';
1095 RAISE fnd_api.g_exc_error;
1096 END IF;
1097
1098 -- Check whether delete is Allowed, this only first and last record can
1099 -- be deleted
1100 cn_chk_plan_element_pkg.chk_uplift_iud (x_return_status => x_return_status,
1101 p_start_date => l_pe_rec.rev_uplift_start_date,
1102 p_end_date => l_pe_rec.rev_uplift_end_date,
1103 p_iud_flag => 'D', --D Stands for delete
1104 p_quota_rule_id => l_pe_rec.quota_rule_id,
1105 p_quota_rule_uplift_id => l_quota_rule_uplift_id,
1106 p_loading_status => x_loading_status,
1107 x_loading_status => l_loading_status
1108 );
1109 x_loading_status := l_loading_status;
1110
1111 IF (x_return_status <> fnd_api.g_ret_sts_success)
1112 THEN
1113 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1114 THEN
1115 fnd_message.set_name ('CN', 'CN_UPLIFT_DELETE_NOT_ALLOWED');
1116 fnd_msg_pub.ADD;
1117 END IF;
1118
1119 x_loading_status := 'CN_UPLIFT_DELETE_NOT_ALLOWED';
1120 RAISE fnd_api.g_exc_error;
1121 END IF;
1122
1123 -- Check the Return status and the status is same as CN_DELETED.
1124 IF (x_return_status <> fnd_api.g_ret_sts_success)
1125 THEN
1126 RAISE fnd_api.g_exc_error;
1127 ELSIF x_loading_status = 'CN_DELETED'
1128 THEN
1129 cn_quota_rule_uplifts_pkg.begin_record (x_operation => 'DELETE',
1130 x_org_id => l_pe_rec.org_id,
1131 x_quota_rule_uplift_id => l_quota_rule_uplift_id,
1132 x_quota_rule_id => NULL,
1133 x_quota_rule_id_old => NULL,
1134 x_start_date => NULL,
1135 x_start_date_old => NULL,
1136 x_end_date => NULL,
1137 x_end_date_old => NULL,
1138 x_payment_factor => NULL,
1139 x_payment_factor_old => NULL,
1140 x_quota_factor => NULL,
1141 x_quota_factor_old => NULL,
1142 x_last_updated_by => NULL,
1143 x_creation_date => NULL,
1144 x_created_by => NULL,
1145 x_last_update_login => NULL,
1146 x_last_update_date => NULL,
1147 x_program_type => g_program_type,
1148 x_status_code => NULL,
1149 x_object_version_number => p_rev_uplift_rec_tbl (i).object_version_number
1150 );
1151 ELSE
1152 RAISE fnd_api.g_exc_error;
1153 END IF;
1154 END LOOP;
1155 END IF; -- Table Count is Not Zero
1156
1157 -- End of API body.
1158 -- Standard check of p_commit.
1159 --+
1160 IF fnd_api.to_boolean (p_commit)
1161 THEN
1162 COMMIT WORK;
1163 END IF;
1164
1165 --+
1166 -- Standard call to get message count and if count is 1, get message info.
1167 --+
1168 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1169 EXCEPTION
1170 WHEN fnd_api.g_exc_error
1171 THEN
1172 ROLLBACK TO delete_quota_rule_uplift;
1173 x_return_status := fnd_api.g_ret_sts_error;
1174 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1175 WHEN fnd_api.g_exc_unexpected_error
1176 THEN
1177 ROLLBACK TO delete_quota_rule_uplift;
1178 x_loading_status := 'UNEXPECTED_ERR';
1179 x_return_status := fnd_api.g_ret_sts_unexp_error;
1180 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1181 WHEN OTHERS
1182 THEN
1183 ROLLBACK TO delete_quota_rule_uplift;
1184 x_loading_status := 'UNEXPECTED_ERR';
1185 x_return_status := fnd_api.g_ret_sts_unexp_error;
1186
1187 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1188 THEN
1189 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1190 END IF;
1191
1192 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1193 END delete_quota_rule_uplift;
1194 END cn_quota_rule_uplifts_grp;