[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 2007/08/10 20:40:34 rnagired 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 cn_chk_plan_element_pkg.chk_uplift_iud (x_return_status => x_return_status,
717 p_start_date => l_uplift_date_seq_rec_tbl (i).start_date,
718 p_end_date => l_uplift_date_seq_rec_tbl (i).end_date,
719 p_iud_flag => 'I',
720 p_quota_rule_id => l_pe_rec.quota_rule_id,
721 p_quota_rule_uplift_id => p_rev_uplift_rec_tbl (i).quota_rule_uplift_id,
722 p_loading_status => x_loading_status,
723 x_loading_status => l_loading_status
724 );
725 x_loading_status := l_loading_status;
726
727 IF (x_return_status <> fnd_api.g_ret_sts_success)
728 THEN
729 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
730 THEN
731 fnd_message.set_name ('CN', 'CN_INVALID_DATE_SEQUENCE');
732 fnd_msg_pub.ADD;
733 END IF;
734
735 x_loading_status := 'CN_UPLIFT_UPDATE_NOT_ALLOWED';
736 RAISE fnd_api.g_exc_error;
737 END IF;
738 END IF;
739 END LOOP;
740 END IF; -- Table Count is Not Zero
741
742 -- End of API body.
743 -- Standard check of p_commit.
744 IF fnd_api.to_boolean (p_commit)
745 THEN
746 COMMIT WORK;
747 END IF;
748
749 <<end_api_body>>
750 NULL;
751 --+
752 -- Standard call to get message count and if count is 1, get message info.
753 --+
754 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
755 EXCEPTION
756 WHEN fnd_api.g_exc_error
757 THEN
758 ROLLBACK TO create_quota_rule_uplift;
759 x_return_status := fnd_api.g_ret_sts_error;
760 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
761 WHEN fnd_api.g_exc_unexpected_error
762 THEN
763 ROLLBACK TO create_quota_rule_uplift;
764 x_loading_status := 'UNEXPECTED_ERR';
765 x_return_status := fnd_api.g_ret_sts_unexp_error;
766 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
767 WHEN OTHERS
768 THEN
769 ROLLBACK TO create_quota_rule_uplift;
770 x_loading_status := 'UNEXPECTED_ERR';
771 x_return_status := fnd_api.g_ret_sts_unexp_error;
772
773 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
774 THEN
775 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
776 END IF;
777
778 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
779 END create_quota_rule_uplift;
780
781 -- ----------------------------------------------------------------------------+
782 --
783 -- Procedure Name: Update_Quota_Rule_uplift
784 --
785 -- ----------------------------------------------------------------------------+
786 PROCEDURE update_quota_rule_uplift (
787 p_api_version IN NUMBER,
788 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
789 p_commit IN VARCHAR2 := fnd_api.g_false,
790 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
791 x_return_status OUT NOCOPY VARCHAR2,
792 x_msg_count OUT NOCOPY NUMBER,
793 x_msg_data OUT NOCOPY VARCHAR2,
794 p_quota_name IN VARCHAR2,
795 p_rev_uplift_rec_tbl IN OUT NOCOPY cn_quota_rule_uplift_pvt.quota_rule_uplift_tbl_type,
796 x_loading_status OUT NOCOPY VARCHAR2
797 )
798 IS
799 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Quota_Rule_uplift';
800 l_api_version CONSTANT NUMBER := 1.0;
801 l_pe_rec cn_chk_plan_element_pkg.pe_rec_type;
802 --l_quota_rule_uplift_id NUMBER;
803 l_uplift_date_seq_rec_tbl uplift_date_seq_rec_tbl_type;
804 l_loading_status VARCHAR2 (80);
805 BEGIN
806 --
807 -- Standard Start of API savepoint
808 -- +
809 SAVEPOINT update_quota_rule_uplift;
810
811 --+
812 -- Standard call to check for call compatibility.
813 --+
814 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
815 THEN
816 RAISE fnd_api.g_exc_unexpected_error;
817 END IF;
818
819 --+
820 -- Initialize message list if p_init_msg_list is set to TRUE.
821 -- +
822 IF fnd_api.to_boolean (p_init_msg_list)
823 THEN
824 fnd_msg_pub.initialize;
825 END IF;
826
827 -- +
828 -- Initialize API return status to success
829 --+
830 x_return_status := fnd_api.g_ret_sts_success;
831 x_loading_status := 'CN_UPDATED';
832
833 -- +
834 -- API body
835 -- +
836 IF (p_rev_uplift_rec_tbl.COUNT <> 0)
837 THEN
838 -- Loop through each record
839 FOR i IN p_rev_uplift_rec_tbl.FIRST .. p_rev_uplift_rec_tbl.LAST
840 LOOP
841 -- Convert each record in the local variable
842 l_pe_rec :=
843 convert_rev_uplift_user_input (p_quota_name => p_quota_name,
844 p_rev_uplift_rec => p_rev_uplift_rec_tbl (i),
845 x_return_status => x_return_status,
846 p_loading_status => x_loading_status,
847 x_loading_status => l_loading_status
848 );
849 x_loading_status := l_loading_status;
850
851 IF (x_return_status <> fnd_api.g_ret_sts_success)
852 THEN
853 RAISE fnd_api.g_exc_error;
854 END IF;
855
856 --validate
857 check_valid_update (x_return_status => x_return_status,
858 x_msg_count => x_msg_count,
859 x_msg_data => x_msg_data,
860 p_quota_name => p_quota_name,
861 p_rev_class_name_old => p_rev_uplift_rec_tbl (i).rev_class_name_old,
862 p_start_date_old => p_rev_uplift_rec_tbl (i).start_date_old,
863 p_end_date_old => p_rev_uplift_rec_tbl (i).end_date_old,
864 p_new_pe_rec => l_pe_rec,
865 x_quota_rule_uplift_id => p_rev_uplift_rec_tbl (i).quota_rule_uplift_id,
866 p_loading_status => x_loading_status,
867 x_loading_status => l_loading_status
868 );
869 x_loading_status := l_loading_status;
870
871 -- If not success the Raise error
872 -- if PLN_QUOTA_UPLIFT_EXISTS
873 IF (x_return_status <> fnd_api.g_ret_sts_success)
874 THEN
875 RAISE fnd_api.g_exc_error;
876 ELSIF x_loading_status = 'CN_UPDATED'
877 THEN
878 cn_quota_rule_uplifts_pkg.begin_record (x_operation => 'UPDATE',
879 x_org_id => l_pe_rec.org_id,
880 x_quota_rule_uplift_id => p_rev_uplift_rec_tbl (i).quota_rule_uplift_id,
881 x_quota_rule_id => l_pe_rec.quota_rule_id,
882 x_quota_rule_id_old => l_pe_rec.quota_rule_id,
883 x_start_date => l_pe_rec.rev_uplift_start_date,
884 x_start_date_old => l_pe_rec.rev_uplift_start_date,
885 x_end_date => l_pe_rec.rev_uplift_end_date,
886 x_end_date_old => l_pe_rec.rev_uplift_end_date,
887 x_payment_factor => l_pe_rec.rev_class_payment_uplift,
888 x_payment_factor_old => l_pe_rec.rev_class_payment_uplift,
889 x_quota_factor => l_pe_rec.rev_class_quota_uplift,
890 x_quota_factor_old => l_pe_rec.rev_class_quota_uplift,
891 x_last_updated_by => fnd_global.user_id,
892 x_creation_date => SYSDATE,
893 x_created_by => fnd_global.user_id,
894 x_last_update_login => fnd_global.login_id,
895 x_last_update_date => SYSDATE,
896 x_program_type => g_program_type,
897 x_status_code => NULL,
898 x_object_version_number => p_rev_uplift_rec_tbl (i).object_version_number
899 );
900 l_uplift_date_seq_rec_tbl (i).start_date := l_pe_rec.rev_uplift_start_date;
901 l_uplift_date_seq_rec_tbl (i).start_date_old := p_rev_uplift_rec_tbl (i).start_date_old;
902 l_uplift_date_seq_rec_tbl (i).end_date := l_pe_rec.rev_uplift_end_date;
903 l_uplift_date_seq_rec_tbl (i).end_date_old := p_rev_uplift_rec_tbl (i).end_date_old;
904 l_uplift_date_seq_rec_tbl (i).quota_rule_id := l_pe_rec.quota_rule_id;
905 l_uplift_date_seq_rec_tbl (i).quota_rule_uplift_id := p_rev_uplift_rec_tbl (i).quota_rule_uplift_id;
906 ELSE
907 RAISE fnd_api.g_exc_error;
908 END IF;
909 END LOOP;
910
911 FOR i IN 1 .. l_uplift_date_seq_rec_tbl.COUNT
912 LOOP
913 IF (( TRUNC (l_uplift_date_seq_rec_tbl (i).start_date_old) <> TRUNC (l_uplift_date_seq_rec_tbl (i).start_date)
914 OR NVL (TRUNC (l_uplift_date_seq_rec_tbl (i).end_date_old), fnd_api.g_miss_date) <>
915 NVL (TRUNC (l_uplift_date_seq_rec_tbl (i).end_date), fnd_api.g_miss_date)
916 )
917 )
918 THEN
919 /*
920 cn_chk_plan_element_pkg.chk_uplift_iud (x_return_status => x_return_status,
921 p_start_date => l_uplift_date_seq_rec_tbl (i).start_date,
922 p_end_date => l_uplift_date_seq_rec_tbl (i).end_date,
923 p_iud_flag => 'U',
924 p_quota_rule_id => l_pe_rec.quota_rule_id,
925 p_quota_rule_uplift_id => p_rev_uplift_rec_tbl (i).quota_rule_uplift_id,
926 p_loading_status => x_loading_status,
927 x_loading_status => l_loading_status
928 );
929 x_loading_status := l_loading_status;
930 */
931 IF (x_return_status <> fnd_api.g_ret_sts_success)
932 THEN
933 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
934 THEN
935 fnd_message.set_name ('CN', 'CN_INVALID_DATE_SEQUENCE');
936 fnd_msg_pub.ADD;
937 END IF;
938
939 x_loading_status := 'INVALID_DATE_SEQUENCE';
940 RAISE fnd_api.g_exc_error;
941 END IF;
942 END IF;
943 END LOOP;
944 END IF; -- Table Count is Not Zero
945
946 -- End of API body.
947 -- Standard check of p_commit.
948 --+
949 IF fnd_api.to_boolean (p_commit)
950 THEN
951 COMMIT WORK;
952 END IF;
953
954 --+
955 -- Standard call to get message count and if count is 1, get message info.
956 --+
957 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
958 EXCEPTION
959 WHEN fnd_api.g_exc_error
960 THEN
961 ROLLBACK TO update_quota_rule_uplift;
962 x_return_status := fnd_api.g_ret_sts_error;
963 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
964 WHEN fnd_api.g_exc_unexpected_error
965 THEN
966 ROLLBACK TO update_quota_rule_uplift;
967 x_loading_status := 'UNEXPECTED_ERR';
968 x_return_status := fnd_api.g_ret_sts_unexp_error;
969 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
970 WHEN OTHERS
971 THEN
972 ROLLBACK TO update_quota_rule_uplift;
973 x_loading_status := 'UNEXPECTED_ERR';
974 x_return_status := fnd_api.g_ret_sts_unexp_error;
975
976 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
977 THEN
978 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
979 END IF;
980
981 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
982 END update_quota_rule_uplift;
983
984 -- ----------------------------------------------------------------------------+
985 --
986 -- Procedure Name: Delete_Quota_Rule_uplift
987 --
988 -- ----------------------------------------------------------------------------+
989 PROCEDURE delete_quota_rule_uplift (
990 p_api_version IN NUMBER,
991 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
992 p_commit IN VARCHAR2 := fnd_api.g_false,
993 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
994 x_return_status OUT NOCOPY VARCHAR2,
995 x_msg_count OUT NOCOPY NUMBER,
996 x_msg_data OUT NOCOPY VARCHAR2,
997 p_quota_name IN VARCHAR2,
998 p_rev_uplift_rec_tbl IN OUT NOCOPY cn_quota_rule_uplift_pvt.quota_rule_uplift_tbl_type,
999 x_loading_status OUT NOCOPY VARCHAR2
1000 )
1001 IS
1002 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Quota_rule_uplift';
1003 l_api_version CONSTANT NUMBER := 1.0;
1004 l_quota_rule_uplift_id NUMBER;
1005 l_pe_rec cn_chk_plan_element_pkg.pe_rec_type;
1006 l_date_msg VARCHAR2 (100);
1007 l_loading_status VARCHAR2 (80);
1008 BEGIN
1009 -- Standard Start of API savepoint
1010 SAVEPOINT delete_quota_rule_uplift;
1011
1012 -- Standard call to check for call compatibility.
1013 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1014 THEN
1015 RAISE fnd_api.g_exc_unexpected_error;
1016 END IF;
1017
1018 -- Initialize message list if p_init_msg_list is set to TRUE.
1019 IF fnd_api.to_boolean (p_init_msg_list)
1020 THEN
1021 fnd_msg_pub.initialize;
1022 END IF;
1023
1024 -- Initialize API return status to success
1025 x_return_status := fnd_api.g_ret_sts_success;
1026 x_loading_status := 'CN_DELETED';
1027
1028 -- API body
1029 -- Store the User Input Value into The Local Variable.
1030 -- Standard check of p_commit.
1031 --+
1032 IF (p_rev_uplift_rec_tbl.COUNT <> 0)
1033 THEN
1034 -- Loop through each record we get from the Procedure call
1035 FOR i IN p_rev_uplift_rec_tbl.FIRST .. p_rev_uplift_rec_tbl.LAST
1036 LOOP
1037 -- Convert each record in the local variable with more necessary things.
1038 l_pe_rec :=
1039 convert_rev_uplift_user_input (p_quota_name => p_quota_name,
1040 p_rev_uplift_rec => p_rev_uplift_rec_tbl (i),
1041 x_return_status => x_return_status,
1042 p_loading_status => x_loading_status,
1043 x_loading_status => l_loading_status
1044 );
1045 x_loading_status := l_loading_status;
1046 -- get Quota Rule Uplift ID
1047 l_quota_rule_uplift_id :=
1048 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);
1049
1050 -- if the Quota Rule uplift iD is null then Error message
1051 IF l_quota_rule_uplift_id IS NULL
1052 THEN
1053 -- The following if has been commented during R12 development
1054 -- as it is not needed.
1055 -- There were hard coded strings and also the if with start_date is
1056 -- not null is incorrect
1057 /*
1058 IF l_pe_rec.rev_uplift_start_date IS NOT NULL
1059 THEN
1060 l_date_msg := l_pe_rec.rev_uplift_start_date || ' and end date ' || l_pe_rec.rev_uplift_end_date;
1061 ELSE
1062 l_date_msg := l_pe_rec.rev_uplift_start_date;
1063 END IF;
1064 */
1065 l_date_msg := l_pe_rec.rev_uplift_start_date;
1066
1067 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1068 THEN
1069 fnd_message.set_name ('CN', 'CN_QUOTA_UPLIFT_NOT_EXIST');
1070 fnd_message.set_token ('PLAN_NAME', l_pe_rec.NAME);
1071 fnd_message.set_token ('REVENUE_CLASS_NAME', l_pe_rec.rev_class_name);
1072 fnd_message.set_token ('START_DATE', l_date_msg);
1073 fnd_msg_pub.ADD;
1074 END IF;
1075
1076 x_loading_status := 'QUOTA_UPLIFT_NOT_EXIST';
1077 RAISE fnd_api.g_exc_error;
1078 END IF;
1079
1080 -- Check whether delete is Allowed, this only first and last record can
1081 -- be deleted
1082 cn_chk_plan_element_pkg.chk_uplift_iud (x_return_status => x_return_status,
1083 p_start_date => l_pe_rec.rev_uplift_start_date,
1084 p_end_date => l_pe_rec.rev_uplift_end_date,
1085 p_iud_flag => 'D', --D Stands for delete
1086 p_quota_rule_id => l_pe_rec.quota_rule_id,
1087 p_quota_rule_uplift_id => l_quota_rule_uplift_id,
1088 p_loading_status => x_loading_status,
1089 x_loading_status => l_loading_status
1090 );
1091 x_loading_status := l_loading_status;
1092
1093 IF (x_return_status <> fnd_api.g_ret_sts_success)
1094 THEN
1095 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1096 THEN
1097 fnd_message.set_name ('CN', 'CN_UPLIFT_DELETE_NOT_ALLOWED');
1098 fnd_msg_pub.ADD;
1099 END IF;
1100
1101 x_loading_status := 'CN_UPLIFT_DELETE_NOT_ALLOWED';
1102 RAISE fnd_api.g_exc_error;
1103 END IF;
1104
1105 -- Check the Return status and the status is same as CN_DELETED.
1106 IF (x_return_status <> fnd_api.g_ret_sts_success)
1107 THEN
1108 RAISE fnd_api.g_exc_error;
1109 ELSIF x_loading_status = 'CN_DELETED'
1110 THEN
1111 cn_quota_rule_uplifts_pkg.begin_record (x_operation => 'DELETE',
1112 x_org_id => l_pe_rec.org_id,
1113 x_quota_rule_uplift_id => l_quota_rule_uplift_id,
1114 x_quota_rule_id => NULL,
1115 x_quota_rule_id_old => NULL,
1116 x_start_date => NULL,
1117 x_start_date_old => NULL,
1118 x_end_date => NULL,
1119 x_end_date_old => NULL,
1120 x_payment_factor => NULL,
1121 x_payment_factor_old => NULL,
1122 x_quota_factor => NULL,
1123 x_quota_factor_old => NULL,
1124 x_last_updated_by => NULL,
1125 x_creation_date => NULL,
1126 x_created_by => NULL,
1127 x_last_update_login => NULL,
1128 x_last_update_date => NULL,
1129 x_program_type => g_program_type,
1130 x_status_code => NULL,
1131 x_object_version_number => p_rev_uplift_rec_tbl (i).object_version_number
1132 );
1133 ELSE
1134 RAISE fnd_api.g_exc_error;
1135 END IF;
1136 END LOOP;
1137 END IF; -- Table Count is Not Zero
1138
1139 -- End of API body.
1140 -- Standard check of p_commit.
1141 --+
1142 IF fnd_api.to_boolean (p_commit)
1143 THEN
1144 COMMIT WORK;
1145 END IF;
1146
1147 --+
1148 -- Standard call to get message count and if count is 1, get message info.
1149 --+
1150 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1151 EXCEPTION
1152 WHEN fnd_api.g_exc_error
1153 THEN
1154 ROLLBACK TO delete_quota_rule_uplift;
1155 x_return_status := fnd_api.g_ret_sts_error;
1156 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1157 WHEN fnd_api.g_exc_unexpected_error
1158 THEN
1159 ROLLBACK TO delete_quota_rule_uplift;
1160 x_loading_status := 'UNEXPECTED_ERR';
1161 x_return_status := fnd_api.g_ret_sts_unexp_error;
1162 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1163 WHEN OTHERS
1164 THEN
1165 ROLLBACK TO delete_quota_rule_uplift;
1166 x_loading_status := 'UNEXPECTED_ERR';
1167 x_return_status := fnd_api.g_ret_sts_unexp_error;
1168
1169 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1170 THEN
1171 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1172 END IF;
1173
1174 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1175 END delete_quota_rule_uplift;
1176 END cn_quota_rule_uplifts_grp;