[Home] [Help]
PACKAGE BODY: APPS.CN_PLAN_ELEMENT_PUB
Source
1 PACKAGE BODY CN_PLAN_ELEMENT_PUB AS
2 /* $Header: cnppeb.pls 120.12 2007/10/19 11:56:08 rnagired ship $ */
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CN_PLAN_ELEMENT_PUB';
4 g_file_name CONSTANT VARCHAR2 (12) := 'cnppeb.pls';
5
6
7 PROCEDURE validate_payment_group_code (
8 x_return_status OUT NOCOPY VARCHAR2,
9 p_payment_group_code IN OUT NOCOPY cn_quotas.payment_group_code%TYPE
10 )
11 IS
12 l_api_name CONSTANT VARCHAR2 (30) := 'validate_payment_group_code';
13 l_tmp_exist NUMBER := 0;
14
15 BEGIN
16 -- set the Status
17 x_return_status := fnd_api.g_ret_sts_success;
18
19
20 IF p_payment_group_code is null
21 THEN
22 p_payment_group_code := 'STANDARD';
23
24 ELSE
25
26 -- Check/Valid quota_type_code
27 SELECT COUNT (*)
28 INTO l_tmp_exist
29 FROM cn_lookups
30 WHERE lookup_type = 'PAYMENT_GROUP_CODE' AND lookup_code = p_payment_group_code;
31
32 IF (l_tmp_exist = 0)
33 THEN
34 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
35 THEN
36 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
37 fnd_message.set_token ('OBJ_NAME', p_payment_group_code);
38 fnd_msg_pub.ADD;
39 END IF;
40
41 RAISE fnd_api.g_exc_error;
42 END IF;
43
44 END IF;
45
46 EXCEPTION
47 WHEN fnd_api.g_exc_error
48 THEN
49 x_return_status := fnd_api.g_ret_sts_error;
50
51 WHEN fnd_api.g_exc_unexpected_error
52 THEN
53 x_return_status := fnd_api.g_ret_sts_unexp_error;
54
55 WHEN OTHERS
56 THEN
57 x_return_status := fnd_api.g_ret_sts_unexp_error;
58
59 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
60 THEN
61 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
62 END IF;
63 END validate_payment_group_code;
64
65
66
67 /* ****************** */
68 /* ADDED - SBADAMI */
69 /* ****************** */
70
71 -- Start of comments
72 -- API name : check_org_id
73 -- Type : Private
74 -- Pre-reqs : None.
75 -- Function : Checks whether it is a valid org_id or not
76 -- Parameters :
77 -- IN : p_org_id IN NUMBER Required
78 -- Item organization id. Part of the unique key
79 -- that uniquely identifies an item record.
80 -- Version : Initial version 1.0
81 -- End of comments
82 PROCEDURE check_org_id (
83 p_org_id IN NUMBER
84 )
85 IS
86 BEGIN
87 IF p_org_id IS NULL
88 THEN
89 fnd_message.set_name ('FND', 'MO_OU_REQUIRED');
90 fnd_msg_pub.ADD;
91 RAISE fnd_api.g_exc_error;
92 END IF;
93 END;
94
95 /* ****************** */
96 /* ADDED - SBADAMI */
97 /* ****************** */
98 -- API name : check_status
99 -- Type : Private
100 -- Pre-reqs : None.
101 -- Function : Raises error based on different statuses
102 -- Parameters :
103 -- IN : p_return_status IN VARCHAR2 Required
104 -- Version : Initial version 1.0
105 -- End of comments
106 PROCEDURE check_status (
107 p_return_status IN VARCHAR2
108 )
109 IS
110 BEGIN
111 IF p_return_status = fnd_api.g_ret_sts_error
112 THEN
113 RAISE fnd_api.g_exc_error;
114 ELSIF p_return_status = fnd_api.g_ret_sts_unexp_error
115 THEN
116 RAISE fnd_api.g_exc_unexpected_error;
117 END IF;
118 END;
119
120 /* ****************** */
121 /* ADDED - SBADAMI */
122 /* ****************** */
123 -- API name : handle_User_Hooks
124 -- Type : Private
125 -- Pre-reqs : None.
126 -- Function : Raises error based on different statuses
127 -- Parameters :
128 -- IN : p_return_status IN VARCHAR2 Required
129 -- Version : Initial version 1.0
130 -- End of comments
131
132 -----------------------------------------------------------------------------+
133 --| Procedure : convert_to_lkup_code
134 --| Description :Convert the lookup meaning to lookup_code if the meaning is
135 --| valid Otherwise keep the invalid value, it passes the period type element
136 --| type and incentive type and returns the respective code to the calling
137 --| place.
138 --| Note: Before fetch the lookup type code from the lookup table remove the
139 --| left and right spaces.
140 --| Called From: Valid_Plan_Element Procedure
141 -----------------------------------------------------------------------------+
142 PROCEDURE convert_to_lkup_code (
143 p_element_type IN VARCHAR2,
144 p_incentive_type IN VARCHAR2,
145 p_payee_assign_flag IN VARCHAR2,
146 p_addup_from_rev_class_flag IN VARCHAR2,
147 p_vesting_flag IN VARCHAR2,
148 p_rt_sched_custom_flag IN VARCHAR2,
149 x_quota_type_code OUT NOCOPY VARCHAR2,
150 x_incentive_type_code OUT NOCOPY VARCHAR2,
151 x_payee_assign_flag OUT NOCOPY VARCHAR2,
152 x_vesting_flag OUT NOCOPY VARCHAR2,
153 x_rt_sched_custom_flag OUT NOCOPY VARCHAR2,
154 x_addup_from_rev_class_flag OUT NOCOPY VARCHAR2
155 )
156 IS
157 l_element_type cn_lookups.meaning%TYPE;
158 l_incentive_type cn_lookups.meaning%TYPE;
159 l_flag VARCHAR2 (10);
160 BEGIN
161 -- Trim code, remove all blank spaces at begin/end of the string
162 -- Assign NULL value if code = FND_API.G_MISS_CHAR
163 l_element_type := RTRIM (LTRIM (p_element_type));
164 l_incentive_type := RTRIM (LTRIM (p_incentive_type));
165
166 -- Convert x_quota_type_code
167 BEGIN
168 SELECT lookup_code
169 INTO x_quota_type_code
170 FROM cn_lookups
171 WHERE lookup_type = 'QUOTA_TYPE' AND UPPER (meaning) = UPPER (l_element_type);
172 EXCEPTION
173 WHEN NO_DATA_FOUND
174 THEN
175 IF l_element_type = fnd_api.g_miss_char
176 THEN
177 x_quota_type_code := fnd_api.g_miss_char;
178 ELSE
179 x_quota_type_code := SUBSTRB (l_element_type, 1, 30);
180 END IF;
181 END;
182
183 -- Convert x_incentive_type_code
184 BEGIN
185 SELECT lookup_code
186 INTO x_incentive_type_code
187 FROM cn_lookups
188 WHERE lookup_type = 'INCENTIVE_TYPE' AND UPPER (meaning) = UPPER (l_incentive_type);
189 EXCEPTION
190 WHEN NO_DATA_FOUND
191 THEN
192 IF l_incentive_type = fnd_api.g_miss_char
193 THEN
194 x_incentive_type_code := fnd_api.g_miss_char;
195 ELSE
196 x_incentive_type_code := SUBSTRB (l_incentive_type, 1, 30);
197 END IF;
198 END;
199
200 -- Convert x_payee_assign_flag
201 SELECT DECODE (p_payee_assign_flag, fnd_api.g_miss_char, 'No', NULL, 'No', LTRIM (RTRIM (p_payee_assign_flag)))
202 INTO l_flag
203 FROM SYS.DUAL;
204
205 BEGIN
206 SELECT lookup_code
207 INTO x_payee_assign_flag
208 FROM fnd_lookups
209 WHERE lookup_type = 'YES_NO' AND UPPER (meaning) = UPPER (l_flag);
210 EXCEPTION
211 WHEN NO_DATA_FOUND
212 THEN
213 x_payee_assign_flag := SUBSTRB (l_flag, 1, 1);
214 END;
215
216 -- Convert x_vesting_flag
217 SELECT DECODE (p_vesting_flag, fnd_api.g_miss_char, 'No', NULL, 'No', LTRIM (RTRIM (p_vesting_flag)))
218 INTO l_flag
219 FROM SYS.DUAL;
220
221 BEGIN
222 SELECT lookup_code
223 INTO x_vesting_flag
224 FROM fnd_lookups
225 WHERE lookup_type = 'YES_NO' AND UPPER (meaning) = UPPER (l_flag);
226 EXCEPTION
227 WHEN NO_DATA_FOUND
228 THEN
229 x_vesting_flag := SUBSTRB (l_flag, 1, 1);
230 END;
231
232 -- Convert x_addup_rev_class_flag
233 SELECT DECODE (p_addup_from_rev_class_flag, fnd_api.g_miss_char, 'No', NULL, 'No', LTRIM (RTRIM (p_addup_from_rev_class_flag)))
234 INTO l_flag
235 FROM SYS.DUAL;
236
237 BEGIN
238 SELECT lookup_code
239 INTO x_addup_from_rev_class_flag
240 FROM fnd_lookups
241 WHERE lookup_type = 'YES_NO' AND UPPER (meaning) = UPPER (l_flag);
242 EXCEPTION
243 WHEN NO_DATA_FOUND
244 THEN
245 x_addup_from_rev_class_flag := SUBSTRB (l_flag, 1, 1);
246 END;
247
248 -- Convert x_rate_cust_flag
249 SELECT DECODE (p_rt_sched_custom_flag, fnd_api.g_miss_char, 'No', NULL, 'No', LTRIM (RTRIM (p_rt_sched_custom_flag)))
250 INTO l_flag
251 FROM SYS.DUAL;
252
253 BEGIN
254 SELECT lookup_code
255 INTO x_rt_sched_custom_flag
256 FROM fnd_lookups
257 WHERE lookup_type = 'YES_NO' AND UPPER (meaning) = UPPER (l_flag);
258 EXCEPTION
259 WHEN NO_DATA_FOUND
260 THEN
261 x_rt_sched_custom_flag := SUBSTRB (l_flag, 1, 1);
262 END;
263 --
264 -- End of convert to lkup code
265 --
266 END convert_to_lkup_code;
267
268 -----------------------------------------------------------------------------+
269 --| Function : convert_pe_user_input
270 --| Description : function to trim all blank spaces of user input convert input
271 --| to correct lookup code Assign defalut value if input is missing
272 --| Called From : Create_plan_element. Update_plan_Element
273 -----------------------------------------------------------------------------+
274 FUNCTION convert_pe_user_input (
275 p_plan_element_rec IN plan_element_rec_type := g_miss_plan_element_rec,
276 x_return_status OUT NOCOPY VARCHAR2,
277 p_loading_status IN VARCHAR2,
278 x_loading_status OUT NOCOPY VARCHAR2
279 )
280 RETURN cn_chk_plan_element_pkg.pe_rec_type
281 IS
282 l_pe_rec cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec;
283 BEGIN
284 -- Set the Status
285 x_return_status := fnd_api.g_ret_sts_success;
286 x_loading_status := p_loading_status;
287 -- Remove the Left and Right Spaces.
288 l_pe_rec.NAME := p_plan_element_rec.NAME;
289 l_pe_rec.interval_name := p_plan_element_rec.interval_name;
290 l_pe_rec.start_date := p_plan_element_rec.start_date;
291 l_pe_rec.end_date := p_plan_element_rec.end_date;
292 l_pe_rec.vesting_flag := p_plan_element_rec.vesting_flag;
293 l_pe_rec.payee_assign_flag := p_plan_element_rec.payee_assign_flag;
294 l_pe_rec.addup_from_rev_class_flag := p_plan_element_rec.addup_from_rev_class_flag;
295 l_pe_rec.credit_type := p_plan_element_rec.credit_type;
296 l_pe_rec.package_name := p_plan_element_rec.package_name;
297 l_pe_rec.performance_goal := p_plan_element_rec.performance_goal;
298 l_pe_rec.payment_amount := p_plan_element_rec.payment_amount;
299 l_pe_rec.rt_sched_custom_flag := p_plan_element_rec.rt_sched_custom_flag;
300 l_pe_rec.description := p_plan_element_rec.description;
301 l_pe_rec.calc_formula_name := p_plan_element_rec.calc_formula_name;
302 l_pe_rec.quota_status := p_plan_element_rec.status;
303 --CHANTHON: Added this...
304 l_pe_rec.org_id := p_plan_element_rec.org_id;
305 l_pe_rec.indirect_credit := p_plan_element_rec.indirect_credit;
306 -- Get quota_id if this plan element already exist
307 BEGIN
308 SELECT quota_id
309 INTO l_pe_rec.quota_id
310 FROM cn_quotas_v
311 WHERE NAME = l_pe_rec.NAME
312 and org_id = l_pe_rec.ORG_ID;
313
314 x_loading_status := 'PLN_QUOTA_EXISTS';
315 EXCEPTION
316 WHEN NO_DATA_FOUND
317 THEN
318 l_pe_rec.quota_id := NULL;
319 END;
320
321 -- Get the formula ID
322 BEGIN
323 IF p_plan_element_rec.calc_formula_name IS NOT NULL
324 THEN
325 SELECT calc_formula_id
326 INTO l_pe_rec.calc_formula_id
327 FROM cn_calc_formulas
328 WHERE NAME = l_pe_rec.calc_formula_name
329 and org_id = l_pe_rec.ORG_ID;
330 END IF;
331 EXCEPTION
332 WHEN NO_DATA_FOUND
333 THEN
334 l_pe_rec.calc_formula_id := NULL;
335 END;
336
337 -- Get Credit Type ID
338 BEGIN
339 IF p_plan_element_rec.credit_type IS NOT NULL
340 THEN
341 SELECT credit_type_id
342 INTO l_pe_rec.credit_type_id
343 FROM cn_credit_types
344 WHERE NAME = l_pe_rec.credit_type and rownum=1;
345 END IF;
346 EXCEPTION
347 WHEN NO_DATA_FOUND
348 THEN
349 l_pe_rec.credit_type_id := NULL;
350 END;
351
352 -- Get Interval Type Id
353 BEGIN
354 IF p_plan_element_rec.interval_name IS NOT NULL
355 THEN
356 SELECT interval_type_id
357 INTO l_pe_rec.interval_type_id
358 FROM cn_interval_types
359 WHERE NAME = l_pe_rec.interval_name
360 and org_id = l_pe_rec.ORG_ID;
361 END IF;
362 EXCEPTION
363 WHEN NO_DATA_FOUND
364 THEN
365 l_pe_rec.interval_type_id := NULL;
366 END;
367
368 -- Convert the Lookup Type to Code
369 convert_to_lkup_code (p_element_type => p_plan_element_rec.element_type,
370 p_incentive_type => p_plan_element_rec.incentive_type,
371 x_quota_type_code => l_pe_rec.quota_type_code,
372 x_incentive_type_code => l_pe_rec.incentive_type_code,
373 p_vesting_flag => p_plan_element_rec.vesting_flag,
374 p_payee_assign_flag => p_plan_element_rec.payee_assign_flag,
375 p_rt_sched_custom_flag => p_plan_element_rec.rt_sched_custom_flag,
376 p_addup_from_rev_class_flag => p_plan_element_rec.addup_from_rev_class_flag,
377 x_vesting_flag => l_pe_rec.vesting_flag,
378 x_payee_assign_flag => l_pe_rec.payee_assign_flag,
379 x_rt_sched_custom_flag => l_pe_rec.rt_sched_custom_flag,
380 x_addup_from_rev_class_flag => l_pe_rec.addup_from_rev_class_flag
381 );
382
383 -- Assign Default value if null or G_MISS_NUM
384 SELECT DECODE (p_plan_element_rec.target, fnd_api.g_miss_num, 0, NULL, 0, p_plan_element_rec.target)
385 INTO l_pe_rec.target
386 FROM SYS.DUAL;
387
388 -- Assign Default value if null or G_MISS_NUM
389 SELECT DECODE (p_plan_element_rec.payment_amount, fnd_api.g_miss_num, 0, NULL, 0, p_plan_element_rec.payment_amount)
390 INTO l_pe_rec.payment_amount
391 FROM SYS.DUAL;
392
393 -- Assign Default value if null or G_MISS_NUM
394 SELECT DECODE (p_plan_element_rec.performance_goal, fnd_api.g_miss_num, 0, NULL, 0, p_plan_element_rec.performance_goal)
395 INTO l_pe_rec.performance_goal
396 FROM SYS.DUAL;
397
398 -- Return the Converted Value
399 RETURN l_pe_rec;
400 END convert_pe_user_input;
401
402 -- -------------------------------------------------------------------------+-+
403 -- Procedure: chk_pe_required
404 -- Desc : Check for necessary parameters for Creating a plan element.
405 -- Need : Plan Element Name, Valid Start and End period,
406 -- Can Not Missing/Null and quota_type_code
407 -- -------------------------------------------------------------------------+-+
408 PROCEDURE chk_pe_required (
409 x_return_status OUT NOCOPY VARCHAR2,
410 p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec,
411 p_loading_status IN VARCHAR2,
412 x_loading_status OUT NOCOPY VARCHAR2
413 )
414 IS
415 l_api_name CONSTANT VARCHAR2 (30) := 'Chk_Pe_Required';
416 l_loading_status VARCHAR2 (80);
417 BEGIN
418 x_return_status := fnd_api.g_ret_sts_success;
419 x_loading_status := p_loading_status;
420
421 -- Check if plan element name is missing or null
422 IF ((cn_api.chk_miss_char_para (p_char_para => p_pe_rec.NAME,
423 p_para_name => cn_chk_plan_element_pkg.g_pe_name,
424 p_loading_status => x_loading_status,
425 x_loading_status => l_loading_status
426 )
427 ) = fnd_api.g_true
428 )
429 THEN
430 RAISE fnd_api.g_exc_error;
431 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_pe_rec.NAME,
432 p_obj_name => cn_chk_plan_element_pkg.g_pe_name,
433 p_loading_status => x_loading_status,
434 x_loading_status => l_loading_status
435 )
436 ) = fnd_api.g_true
437 )
438 THEN
439 RAISE fnd_api.g_exc_error;
440 END IF;
441
442 -- Check quota_type_code can not be missing or NULL
443 IF ((cn_api.chk_miss_char_para (p_char_para => p_pe_rec.quota_type_code,
444 p_para_name => cn_chk_plan_element_pkg.g_element_type,
445 p_loading_status => x_loading_status,
446 x_loading_status => l_loading_status
447 )
448 ) = fnd_api.g_true
449 )
450 THEN
451 RAISE fnd_api.g_exc_error;
452 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_pe_rec.quota_type_code,
453 p_obj_name => cn_chk_plan_element_pkg.g_element_type,
454 p_loading_status => x_loading_status,
455 x_loading_status => l_loading_status
456 )
457 ) = fnd_api.g_true
458 )
459 THEN
460 RAISE fnd_api.g_exc_error;
461 END IF;
462
463 -- Check credit_type can not be missing or NULL
464 IF ((cn_api.chk_miss_char_para (p_char_para => p_pe_rec.credit_type,
465 p_para_name => cn_chk_plan_element_pkg.g_credit_type_name,
466 p_loading_status => x_loading_status,
467 x_loading_status => l_loading_status
468 )
469 ) = fnd_api.g_true
470 )
471 THEN
472 RAISE fnd_api.g_exc_error;
473 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_pe_rec.credit_type,
474 p_obj_name => cn_chk_plan_element_pkg.g_credit_type_name,
475 p_loading_status => x_loading_status,
476 x_loading_status => l_loading_status
477 )
478 ) = fnd_api.g_true
479 )
480 THEN
481 RAISE fnd_api.g_exc_error;
482 END IF;
483
484 -- Check credit_type_id can not be missing or NULL
485 IF (p_pe_rec.credit_type IS NOT NULL AND p_pe_rec.credit_type_id IS NULL)
486 THEN
487 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
488 THEN
489 fnd_message.set_name ('CN', 'CN_CREDIT_TYPE_NOT_EXIST');
490 fnd_message.set_token ('CREDIT_TYPE', p_pe_rec.credit_type);
491 fnd_msg_pub.ADD;
492 END IF;
493
494 x_loading_status := 'CREDIT_TYPE_NOT_EXIST';
495 RAISE fnd_api.g_exc_error;
496 END IF;
497
498 -- Check interval_type_id can not be missing or NULL
499 IF (p_pe_rec.interval_name IS NOT NULL AND p_pe_rec.interval_type_id IS NULL)
500 THEN
501 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
502 THEN
503 fnd_message.set_name ('CN', 'CN_INTERVAL_TYPE_NOT_EXIST');
504 fnd_message.set_token ('INTERVAL_NAME', p_pe_rec.interval_name);
505 fnd_msg_pub.ADD;
506 END IF;
507
508 x_loading_status := 'INTERVAL_TYPE_NOT_EXIST';
509 RAISE fnd_api.g_exc_error;
510 END IF;
511
512 -- Check incentive_type_code can not be missing or NULL
513 IF ((cn_api.chk_miss_char_para (p_char_para => p_pe_rec.incentive_type_code,
514 p_para_name => cn_chk_plan_element_pkg.g_incentive_type_code,
515 p_loading_status => x_loading_status,
516 x_loading_status => l_loading_status
517 )
518 ) = fnd_api.g_true
519 )
520 THEN
521 RAISE fnd_api.g_exc_error;
522 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_pe_rec.incentive_type_code,
523 p_obj_name => cn_chk_plan_element_pkg.g_incentive_type_code,
524 p_loading_status => x_loading_status,
525 x_loading_status => l_loading_status
526 )
527 ) = fnd_api.g_true
528 )
529 THEN
530 RAISE fnd_api.g_exc_error;
531 END IF;
532
533 -- Check Start Date can not be missing or NULL
534 IF ((cn_chk_plan_element_pkg.chk_miss_date_para (p_date_para => p_pe_rec.start_date,
535 p_para_name => cn_chk_plan_element_pkg.g_start_date,
536 p_loading_status => x_loading_status,
537 x_loading_status => l_loading_status
538 )
539 ) = fnd_api.g_true
540 )
541 THEN
542 RAISE fnd_api.g_exc_error;
543 ELSIF ((cn_chk_plan_element_pkg.chk_null_date_para (p_date_para => p_pe_rec.start_date,
544 p_obj_name => cn_chk_plan_element_pkg.g_start_date,
545 p_loading_status => x_loading_status,
546 x_loading_status => l_loading_status
547 )
548 ) = fnd_api.g_true
549 )
550 THEN
551 RAISE fnd_api.g_exc_error;
552 END IF;
553
554 -- Check interval name can not be missing or NULL
555 IF ((cn_api.chk_miss_char_para (p_char_para => p_pe_rec.interval_name,
556 p_para_name => cn_chk_plan_element_pkg.g_interval_name,
557 p_loading_status => x_loading_status,
558 x_loading_status => l_loading_status
559 )
560 ) = fnd_api.g_true
561 )
562 THEN
563 RAISE fnd_api.g_exc_error;
564 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_pe_rec.interval_name,
565 p_obj_name => cn_chk_plan_element_pkg.g_interval_name,
566 p_loading_status => x_loading_status,
567 x_loading_status => l_loading_status
568 )
569 ) = fnd_api.g_true
570 )
571 THEN
572 RAISE fnd_api.g_exc_error;
573 END IF;
574 -- end of chk_pe_required
575 EXCEPTION
576 WHEN fnd_api.g_exc_error
577 THEN
578 x_return_status := fnd_api.g_ret_sts_error;
579 WHEN fnd_api.g_exc_unexpected_error
580 THEN
581 x_return_status := fnd_api.g_ret_sts_unexp_error;
582 x_loading_status := 'UNEXPECTED_ERR';
583 WHEN OTHERS
584 THEN
585 x_return_status := fnd_api.g_ret_sts_unexp_error;
586 x_loading_status := 'UNEXPECTED_ERR';
587
588 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
589 THEN
590 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
591 END IF;
592 END chk_pe_required;
593
594 -- -------------------------------------------------------------------------+-+
595 -- Procedure: chk_pe_consistent
596 -- Desc : The same plan element already exist in the database, this
597 -- procedure will check if all input for this plan element is as
598 -- the same as those exists in the database
599 -- -------------------------------------------------------------------------+-+
600 PROCEDURE chk_pe_consistent (
601 x_return_status OUT NOCOPY VARCHAR2,
602 p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec,
603 p_loading_status IN VARCHAR2,
604 x_loading_status OUT NOCOPY VARCHAR2
605 )
606 IS
607 CURSOR c_pe_csr
608 IS
609 SELECT NAME,
610 description,
611 quota_type_code,
612 calc_formula_id,
613 target
614 FROM cn_quotas_v
615 WHERE NAME = p_pe_rec.NAME;
616
617 l_pe_csr c_pe_csr%ROWTYPE;
618 l_api_name CONSTANT VARCHAR2 (30) := 'chk_pe_consistent';
619 BEGIN
620 x_return_status := fnd_api.g_ret_sts_success;
621 x_loading_status := p_loading_status;
622
623 OPEN c_pe_csr;
624
625 FETCH c_pe_csr
626 INTO l_pe_csr;
627
628 IF c_pe_csr%NOTFOUND
629 THEN
630 RAISE fnd_api.g_exc_unexpected_error;
631 END IF;
632
633 -- Check description consistent
634 IF (l_pe_csr.description <> p_pe_rec.description)
635 THEN
636 -- Error, check the msg level and add an error message to the
637 -- API message list
638 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
639 THEN
640 fnd_message.set_name ('CN', 'CN_PLN_NOT_CONSISTENT');
641 fnd_message.set_token ('PLAN_NAME', p_pe_rec.NAME);
642 fnd_message.set_token ('OBJ_NAME', cn_chk_plan_element_pkg.g_desc);
643 fnd_msg_pub.ADD;
644 END IF;
645
646 x_loading_status := 'CN_PLN_NOT_CONSISTENT';
647 RAISE fnd_api.g_exc_error;
648 END IF;
649
650 -- Check quota_type_code consistent
651 IF (l_pe_csr.quota_type_code <> p_pe_rec.quota_type_code)
652 THEN
653 -- Error, check the msg level and add an error message to the
654 -- API message list
655 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
656 THEN
657 fnd_message.set_name ('CN', 'CN_PLN_NOT_CONSISTENT');
658 fnd_message.set_token ('PLAN_NAME', p_pe_rec.NAME);
659 fnd_message.set_token ('OBJ_NAME', cn_chk_plan_element_pkg.g_element_type);
660 fnd_msg_pub.ADD;
661 END IF;
662
663 x_loading_status := 'CN_PLN_NOT_CONSISTENT';
664 RAISE fnd_api.g_exc_error;
665 END IF;
666
667 -- Check target consistent
668 IF (l_pe_csr.target <> p_pe_rec.target)
669 THEN
670 -- Error, check the msg level and add an error message to the
671 -- API message list
672 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
673 THEN
674 fnd_message.set_name ('CN', 'CN_PLN_NOT_CONSISTENT');
675 fnd_message.set_token ('PLAN_NAME', p_pe_rec.NAME);
676 fnd_message.set_token ('OBJ_NAME', cn_chk_plan_element_pkg.g_target);
677 fnd_msg_pub.ADD;
678 END IF;
679
680 x_loading_status := 'CN_PLN_NOT_CONSISTENT';
681 RAISE fnd_api.g_exc_error;
682 END IF;
683
684 CLOSE c_pe_csr;
685 EXCEPTION
686 WHEN fnd_api.g_exc_error
687 THEN
688 x_return_status := fnd_api.g_ret_sts_error;
689 WHEN fnd_api.g_exc_unexpected_error
690 THEN
691 x_return_status := fnd_api.g_ret_sts_unexp_error;
692 x_loading_status := 'UNEXPECTED_ERR';
693 WHEN OTHERS
694 THEN
695 x_return_status := fnd_api.g_ret_sts_unexp_error;
696 x_loading_status := 'UNEXPECTED_ERR';
697
698 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
699 THEN
700 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
701 END IF;
702 END chk_pe_consistent;
703
704 -- -------------------------------------------------------------------------+-+
705 --| Procedure : chg_exprs
706 --| Description : Syncs expressions that are using a particular plan element
707 --| if the name is changed
708 -- -------------------------------------------------------------------------+-+
709 PROCEDURE chg_exprs (
710 p_quota_id NUMBER,
711 p_old_name VARCHAR2,
712 p_new_name VARCHAR2
713 )
714 IS
715 CURSOR get_exps
716 IS
717 SELECT calc_sql_exp_id,
718 DBMS_LOB.SUBSTR (piped_sql_select) sql_select,
719 DBMS_LOB.SUBSTR (piped_expression_disp) expr_disp
720 FROM cn_calc_sql_exps
721 WHERE '|' || DBMS_LOB.SUBSTR (piped_sql_select) LIKE '%|(' || p_quota_id || 'PE.%';
722
723 l_ss_start NUMBER;
724 l_ss_end NUMBER;
725 l_ed_start NUMBER;
726 l_ed_end NUMBER;
727 l_quota_id_len NUMBER := LENGTH ('' || p_quota_id);
728 l_quota_name_len NUMBER := LENGTH (p_old_name);
729 CONTINUE BOOLEAN;
730 l_ss_seg VARCHAR2 (4000);
731 l_ed_seg VARCHAR2 (80);
732 l_new_expr_disp VARCHAR2 (4000);
733 l_new_pexpr_disp VARCHAR2 (4000);
734 BEGIN
735 -- get all expressions using p_quota_id
736 FOR e IN get_exps
737 LOOP
738 l_ss_start := 1;
739 l_ed_start := 1;
740 l_new_expr_disp := NULL;
741 l_new_pexpr_disp := NULL;
742 CONTINUE := TRUE;
743
744 WHILE CONTINUE
745 LOOP
746 l_ss_end := INSTR (e.sql_select, '|', l_ss_start + 1);
747 l_ed_end := INSTR (e.expr_disp, '|', l_ed_start + 1);
748
749 IF l_ss_end = 0
750 THEN
751 CONTINUE := FALSE;
752 ELSE
753 l_ss_seg := SUBSTR (e.sql_select, l_ss_start, l_ss_end - l_ss_start);
754 l_ed_seg := SUBSTR (e.expr_disp, l_ed_start, l_ed_end - l_ed_start);
755
756 IF SUBSTR (l_ss_seg, 1, l_quota_id_len + 4) = '(' || p_quota_id || 'PE.'
757 AND SUBSTR (l_ed_seg, 1, l_quota_name_len + 1) = p_old_name || '.'
758 THEN
759 l_new_expr_disp := l_new_expr_disp || p_new_name || SUBSTR (l_ed_seg, l_quota_name_len + 1);
760 l_new_pexpr_disp := l_new_pexpr_disp || p_new_name || SUBSTR (l_ed_seg, l_quota_name_len + 1) || '|';
761 ELSE
762 l_new_expr_disp := l_new_expr_disp || l_ed_seg;
763 l_new_pexpr_disp := l_new_pexpr_disp || l_ed_seg || '|';
764 END IF;
765 END IF;
766
767 l_ss_start := l_ss_end + 1;
768 l_ed_start := l_ed_end + 1;
769 END LOOP;
770
771 -- update table
772 UPDATE cn_calc_sql_exps
773 SET expression_disp = l_new_expr_disp,
774 piped_expression_disp = l_new_pexpr_disp
775 WHERE calc_sql_exp_id = e.calc_sql_exp_id;
776 END LOOP;
777 END chg_exprs;
778
779 -- -------------------------------------------------------------------------+-+
780 --| Procedure : valid_lookup_code
781 --| Description : Valid lookup code for plan element. Just make sure the lookup
782 --| code is valid in cn_lookups or not null/not missing but not checking
783 --| correct setting for different plan element type
784 -- -------------------------------------------------------------------------+-+
785 PROCEDURE valid_lookup_code (
786 x_return_status OUT NOCOPY VARCHAR2,
787 p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec,
788 p_loading_status IN VARCHAR2,
789 x_loading_status OUT NOCOPY VARCHAR2
790 )
791 IS
792 l_api_name CONSTANT VARCHAR2 (30) := 'Valid_Lookup_Code';
793 l_tmp_exist NUMBER := 0;
794 BEGIN
795 -- set the Status
796 x_return_status := fnd_api.g_ret_sts_success;
797 x_loading_status := p_loading_status;
798
799 -- Check/Valid quota_type_code
800 SELECT COUNT (*)
801 INTO l_tmp_exist
802 FROM cn_lookups
803 WHERE lookup_type = 'QUOTA_TYPE' AND lookup_code = p_pe_rec.quota_type_code;
804
805 IF (l_tmp_exist = 0)
806 THEN
807 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
808 THEN
809 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
810 fnd_message.set_token ('OBJ_NAME', cn_chk_plan_element_pkg.g_element_type);
811 fnd_msg_pub.ADD;
812 END IF;
813
814 x_loading_status := 'CN_INVALID_DATA';
815 RAISE fnd_api.g_exc_error;
816 END IF;
817
818 -- Check/Valid Incentive Type
819 SELECT COUNT (*)
820 INTO l_tmp_exist
821 FROM cn_lookups
822 WHERE lookup_type = 'INCENTIVE_TYPE' AND lookup_code = p_pe_rec.incentive_type_code;
823
824 IF (l_tmp_exist = 0)
825 THEN
826 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
827 THEN
828 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
829 fnd_message.set_token ('OBJ_NAME', cn_chk_plan_element_pkg.g_incentive_type_code);
830 fnd_msg_pub.ADD;
831 END IF;
832
833 x_loading_status := 'CN_INVALID_DATA';
834 RAISE fnd_api.g_exc_error;
835 END IF;
836 -- End valid lookup code
837 EXCEPTION
838 WHEN fnd_api.g_exc_error
839 THEN
840 x_return_status := fnd_api.g_ret_sts_error;
841 WHEN fnd_api.g_exc_unexpected_error
842 THEN
843 x_return_status := fnd_api.g_ret_sts_unexp_error;
844 x_loading_status := 'UNEXPECTED_ERR';
845 WHEN OTHERS
846 THEN
847 x_return_status := fnd_api.g_ret_sts_unexp_error;
848 x_loading_status := 'UNEXPECTED_ERR';
849
850 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
851 THEN
852 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
853 END IF;
854 END valid_lookup_code;
855
856 -- -------------------------------------------------------------------------+-+
857 --| Procedure: Insert_rate_quotas
858 --| Description: Rate_quotas is a local procedure to create the Default rate
859 --| Quota Assigns if the quota type is formula and the formula has the rates in
860 --| formula rate Assigns. Another important thing is if you pass the custom
861 --| Quota Rate it will ignore the default create. it will use the custom one you
862 --| Pass through your API.
863 --| Called From: Create_plan_Element and Update_Plan_Element
864 -- -------------------------------------------------------------------------+-+
865 PROCEDURE update_rate_quotas (
866 p_api_version IN NUMBER,
867 p_init_msg_list IN VARCHAR2,
868 p_commit IN VARCHAR2,
869 p_validation_level IN NUMBER,
870 x_return_status OUT NOCOPY VARCHAR2,
871 x_msg_count OUT NOCOPY NUMBER,
872 x_msg_data OUT NOCOPY VARCHAR2,
873 p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type,
874 p_pe_rec_old IN cn_chk_plan_element_pkg.pe_rec_type,
875 p_rt_quota_asgns_rec_tbl IN rt_quota_asgns_rec_tbl_type := g_miss_rt_quota_asgns_rec_tbl,
876 p_quota_name IN VARCHAR2,
877 p_loading_status IN VARCHAR2,
878 x_loading_status OUT NOCOPY VARCHAR2
879 )
880 IS
881 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Rate_Quotas';
882 l_object_version_number NUMBER;
883 BEGIN
884 -- Record inserted successfully, check for rt_quota_assigns
885 -- Insert Rate Quota Assigs
886 -- first table count is 0
887
888 -- Set Status
889 x_return_status := fnd_api.g_ret_sts_success;
890 x_loading_status := p_loading_status;
891
892 -- Check if the Count is O and the QUOTA TYPE IS FORMULA
893 -- Call the Chk_formula_rate_date Procedure to check all the Start
894 -- Date and End date of Rate QUota assigns falls user the Quota Start
895 -- and end Date then insert through a batch by calling the Table Handler
896 IF NVL (p_pe_rec_old.calc_formula_id, 0) <> NVL (p_pe_rec.calc_formula_id, 0)
897 THEN
898 -- Call the Table Handler to Delete the Old Period quotas
899 cn_rt_quota_asgns_pkg.DELETE_RECORD (x_quota_id => p_pe_rec_old.quota_id, x_calc_formula_id => NULL, x_rt_quota_asgn_id => NULL);
900 END IF;
901
902 IF p_rt_quota_asgns_rec_tbl.COUNT = 0 AND p_pe_rec.quota_type_code <> 'NONE'
903 THEN
904 -- check all the formula rate start date fall under the quota date
905 -- clku, we do not check the date range of the formula rates against the PE date range anymore,
906 -- bug 1949943
907 /*cn_chk_plan_element_pkg.chk_formula_rate_date
908 (
909 x_return_status => x_return_status,
910 p_start_date => p_pe_rec.start_date,
911 p_end_date => p_pe_rec.end_date,
912 p_quota_name => p_pe_rec.name ,
913 p_calc_formula_id => p_pe_rec.calc_formula_id,
914 p_calc_formula_name => p_pe_rec.calc_formula_name,
915 p_loading_status => x_loading_status,
916 x_loading_status => x_loading_status ) ;
917 -- error if the status is not success
918 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
919 RAISE FND_API.G_EXC_ERROR ;
920 END IF;*/
921 IF p_pe_rec.calc_formula_id IS NOT NULL AND NVL (p_pe_rec_old.calc_formula_id, 0) <> NVL (p_pe_rec.calc_formula_id, 0)
922 THEN
923 -- check all the formula rate start date fall under the quota date
924 -- clku, we do not check the date range of the formula rates against the PE date range anymore,
925 -- bug 1949943
926 /*cn_chk_plan_element_pkg.chk_formula_rate_date
927 (
928 x_return_status => x_return_status,
929 p_start_date => p_pe_rec.start_date,
930 p_end_date => p_pe_rec.end_date,
931 p_quota_name => p_pe_rec.name ,
932 p_calc_formula_id => p_pe_rec.calc_formula_id,
933 p_calc_formula_name => p_pe_rec.calc_formula_name,
934 p_loading_status => x_loading_status,
935 x_loading_status => x_loading_status ) ;
936 -- error if the status is not success
937 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
938 RAISE FND_API.G_EXC_ERROR ;
939 END IF;*/
940
941 -- call the Table handler for batch insert. we betten
942 -- DO IN TABLE handler itself
943 cn_rt_quota_asgns_pkg.INSERT_RECORD (x_quota_id => p_pe_rec.quota_id, x_calc_formula_id => p_pe_rec.calc_formula_id);
944 END IF;
945 -- if the rt_table_count is > 0 and the quota type is FORMULA
946 ELSIF p_pe_rec.quota_type_code <> 'NONE' AND p_rt_quota_asgns_rec_tbl.COUNT > 0
947 THEN
948 -- call create_rt_quota_asgns_pvt package to validate and create
949 -- the rate Quota Assigns
950 cn_rt_quota_asgns_pvt.update_rt_quota_asgns (p_api_version => p_api_version,
951 p_init_msg_list => 'T',
952 p_commit => p_commit,
953 p_validation_level => p_validation_level,
954 x_return_status => x_return_status,
955 x_msg_count => x_msg_count,
956 x_msg_data => x_msg_data,
957 p_quota_name => p_quota_name,
958 p_org_id => p_pe_rec.org_id,
959 p_rt_quota_asgns_rec_tbl => p_rt_quota_asgns_rec_tbl,
960 x_loading_status => x_loading_status,
961 x_object_version_number => l_object_version_number
962 );
963
964 IF (x_return_status <> fnd_api.g_ret_sts_success)
965 THEN
966 RAISE fnd_api.g_exc_error;
967 END IF;
968 -- if table count is > 0 but the quota type code is NONE
969 -- then raise an error
970 ELSIF p_pe_rec.quota_type_code = 'NONE' AND p_rt_quota_asgns_rec_tbl.COUNT > 0
971 THEN
972 -- Error you cannot have rates for quota type is NONE
973 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
974 THEN
975 fnd_message.set_name ('CN', 'CN_QUOTA_CANNOT_HAVE_RATES');
976 fnd_message.set_token ('PLAN_NAME', p_quota_name);
977 fnd_msg_pub.ADD;
978 END IF;
979
980 x_loading_status := 'QUOTA_CANNOT_HAVE_RATES';
981 RAISE fnd_api.g_exc_error;
982 END IF;
983 -- End of rate_quotas
984 EXCEPTION
985 WHEN fnd_api.g_exc_error
986 THEN
987 x_return_status := fnd_api.g_ret_sts_error;
988 WHEN fnd_api.g_exc_unexpected_error
989 THEN
990 x_return_status := fnd_api.g_ret_sts_unexp_error;
991 x_loading_status := 'UNEXPECTED_ERR';
992 WHEN OTHERS
993 THEN
994 x_return_status := fnd_api.g_ret_sts_unexp_error;
995 x_loading_status := 'UNEXPECTED_ERR';
996
997 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
998 THEN
999 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1000 END IF;
1001 END update_rate_quotas;
1002
1003 -- -------------------------------------------------------------------------+-+
1004 --| Procedure: Insert_rate_quotas
1005 --| Description: Rate_quotas is a local procedure to create the Default rate
1006 --| Quota Assigns if the quota type is formula and the formula has the rates in
1007 --| formula rate Assigns. Another important thing is if you pass the custom
1008 --| Quota Rate it will ignore the default create. it will use the custom one you
1009 --| Pass through your API.
1010 --| Called From: Create_plan_Element and Update_Plan_Element
1011 -- -------------------------------------------------------------------------+-+
1012 PROCEDURE insert_rate_quotas (
1013 p_api_version IN NUMBER,
1014 p_init_msg_list IN VARCHAR2,
1015 p_commit IN VARCHAR2,
1016 p_validation_level IN NUMBER,
1017 x_return_status OUT NOCOPY VARCHAR2,
1018 x_msg_count OUT NOCOPY NUMBER,
1019 x_msg_data OUT NOCOPY VARCHAR2,
1020 p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type,
1021 p_rt_quota_asgns_rec_tbl IN rt_quota_asgns_rec_tbl_type := g_miss_rt_quota_asgns_rec_tbl,
1022 p_quota_name IN VARCHAR2,
1023 p_loading_status IN VARCHAR2,
1024 x_loading_status OUT NOCOPY VARCHAR2
1025 )
1026 IS
1027 l_api_name CONSTANT VARCHAR2 (30) := 'Insert_Rate_Quotas';
1028 l_object_version_number NUMBER;
1029 BEGIN
1030 -- Record inserted successfully, check for rt_quota_assigns
1031 -- Insert Rate Quota Assigs
1032 -- first table count is 0
1033
1034 -- Set Status
1035 x_return_status := fnd_api.g_ret_sts_success;
1036 x_loading_status := p_loading_status;
1037
1038 -- Check if the Count is O and the QUOTA TYPE IS FORMULA
1039 -- Call the Chk_formula_rate_date Procedure to check all the Start
1040 -- Date and End date of Rate QUota assigns falls user the Quota Start
1041 -- and end Date then insert through a batch by calling the Table Handler
1042
1043 -- Check the ITD flag
1044 IF p_rt_quota_asgns_rec_tbl.COUNT = 0 AND p_pe_rec.quota_type_code <> 'NONE'
1045 THEN
1046 IF p_pe_rec.calc_formula_id IS NOT NULL
1047 THEN
1048 -- check all the formula rate start date fall under the quota date
1049 -- clku, we do not check the date range of the formula rates against the PE date range anymore,
1050 -- bug 1949943
1051 /*cn_chk_plan_element_pkg.chk_formula_rate_date
1052 (
1053 x_return_status => x_return_status,
1054 p_start_date => p_pe_rec.start_date,
1055 p_end_date => p_pe_rec.end_date,
1056 p_quota_name => p_pe_rec.name ,
1057 p_calc_formula_id => p_pe_rec.calc_formula_id,
1058 p_calc_formula_name => p_pe_rec.calc_formula_name,
1059 p_loading_status => x_loading_status,
1060 x_loading_status => x_loading_status ) ;
1061 -- error if the status is not success
1062 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1063 RAISE FND_API.G_EXC_ERROR ;
1064 END IF;*/
1065 -- call the Table handler for batch insert.
1066 cn_rt_quota_asgns_pkg.INSERT_RECORD (x_quota_id => p_pe_rec.quota_id, x_calc_formula_id => p_pe_rec.calc_formula_id);
1067 END IF;
1068 -- if the rt_table_count is > 0 and the quota type is FORMULA
1069 ELSIF p_pe_rec.quota_type_code <> 'NONE' AND p_rt_quota_asgns_rec_tbl.COUNT > 0
1070 THEN
1071 -- call create_rt_quota_asgns_pvt package to validate and create
1072 -- the rate Quota Assigns
1073 cn_rt_quota_asgns_pvt.create_rt_quota_asgns (p_api_version => p_api_version,
1074 p_init_msg_list => 'T',
1075 p_commit => p_commit,
1076 p_validation_level => p_validation_level,
1077 x_return_status => x_return_status,
1078 x_msg_count => x_msg_count,
1079 x_msg_data => x_msg_data,
1080 p_quota_name => p_quota_name,
1081 p_org_id => p_pe_rec.org_id,
1082 p_rt_quota_asgns_rec_tbl => p_rt_quota_asgns_rec_tbl,
1083 x_loading_status => x_loading_status,
1084 x_object_version_number => l_object_version_number
1085 );
1086
1087 IF (x_return_status <> fnd_api.g_ret_sts_success)
1088 THEN
1089 RAISE fnd_api.g_exc_error;
1090 END IF;
1091 -- if table count is > 0 but the quota type code is NONE
1092 -- then raise an error
1093 ELSIF p_pe_rec.quota_type_code = 'NONE' AND p_rt_quota_asgns_rec_tbl.COUNT > 0
1094 THEN
1095 -- Error you cannot have rates for quota type is NONE
1096 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1097 THEN
1098 fnd_message.set_name ('CN', 'CN_QUOTA_CANNOT_HAVE_RATES');
1099 fnd_message.set_token ('PLAN_NAME', p_quota_name);
1100 fnd_msg_pub.ADD;
1101 END IF;
1102
1103 x_loading_status := 'QUOTA_CANNOT_HAVE_RATES';
1104 RAISE fnd_api.g_exc_error;
1105 END IF;
1106 -- End of rate_quotas
1107 EXCEPTION
1108 WHEN fnd_api.g_exc_error
1109 THEN
1110 x_return_status := fnd_api.g_ret_sts_error;
1111 WHEN fnd_api.g_exc_unexpected_error
1112 THEN
1113 x_return_status := fnd_api.g_ret_sts_unexp_error;
1114 x_loading_status := 'UNEXPECTED_ERR';
1115 WHEN OTHERS
1116 THEN
1117 x_return_status := fnd_api.g_ret_sts_unexp_error;
1118 x_loading_status := 'UNEXPECTED_ERR';
1119
1120 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1121 THEN
1122 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1123 END IF;
1124 END insert_rate_quotas;
1125
1126 -- -------------------------------------------------------------------------+-+
1127 --| Procedure: Create_Period_quotas
1128 --| Description: Period_quotas is a localprocedure to create the Default period
1129 --| Quota if the quota type is formula and the formula has the a ITD flag is
1130 --| set to Y then Create or Customize the Period Quotas
1131 --| Called From: Create_plan_Element and Update_Plan_Element
1132 -- -------------------------------------------------------------------------+-+
1133 PROCEDURE update_period_quotas (
1134 p_api_version IN NUMBER,
1135 p_init_msg_list IN VARCHAR2,
1136 p_commit IN VARCHAR2,
1137 p_validation_level IN NUMBER,
1138 x_return_status OUT NOCOPY VARCHAR2,
1139 x_msg_count OUT NOCOPY NUMBER,
1140 x_msg_data OUT NOCOPY VARCHAR2,
1141 p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type,
1142 p_pe_rec_old IN cn_chk_plan_element_pkg.pe_rec_type,
1143 p_period_quotas_rec_tbl IN period_quotas_rec_tbl_type := g_miss_period_quotas_rec_tbl,
1144 p_quota_name IN VARCHAR2,
1145 p_loading_status IN VARCHAR2,
1146 x_loading_status OUT NOCOPY VARCHAR2
1147 )
1148 IS
1149 l_tmp NUMBER;
1150 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Period_quotas';
1151 BEGIN
1152 -- Set Status
1153 x_return_status := fnd_api.g_ret_sts_success;
1154 x_loading_status := p_loading_status;
1155
1156 -- Check if the formula id is not null and has ITD flag is Y
1157 -- and check if table.COUNT is zero the call the table handler
1158 -- to create the Period Quotas.
1159 -- if the count is > 0 then Call the Group Package procedure
1160 -- to Create the Custom Period Quotas
1161 IF p_pe_rec_old.start_date <> p_pe_rec.start_date
1162 OR NVL (p_pe_rec.end_date, fnd_api.g_miss_date) <> NVL (p_pe_rec_old.end_date, fnd_api.g_miss_date)
1163 --clku, bug 3058608
1164 /*OR
1165 Nvl(p_pe_rec_old.calc_formula_id,0) <> Nvl(p_pe_rec.calc_formula_id,0)*/
1166 THEN
1167 -- Call the Table Handler to Delete the Old Period quotas
1168 cn_period_quotas_pkg.DELETE_RECORD (p_pe_rec_old.quota_id);
1169 -- Check the ITD flag
1170 -- 2462767, we do not check for formula ID anymore,IF p_pe_rec.calc_formula_id IS NOT NULL THEN
1171 -- clku, enhancement 2380234
1172 --IF Nvl(cn_api.get_itd_flag(p_pe_rec.calc_formula_id),'N') = 'Y'
1173 -- THEN
1174 cn_period_quotas_pkg.distribute_target (p_pe_rec.quota_id);
1175
1176 -- if count is zero create the default period quotas.
1177 IF p_period_quotas_rec_tbl.COUNT > 0
1178 THEN
1179 -- if count is > 0 then create the period quotas with the
1180 -- customised values.
1181 -- Call the Group package to create the Period Quotas
1182 cn_period_quotas_grp.update_period_quotas (p_api_version => p_api_version,
1183 p_init_msg_list => 'T',
1184 p_commit => p_commit,
1185 p_validation_level => p_validation_level,
1186 x_return_status => x_return_status,
1187 x_msg_count => x_msg_count,
1188 x_msg_data => x_msg_data,
1189 p_quota_name => p_quota_name,
1190 p_period_quotas_rec_tbl => p_period_quotas_rec_tbl,
1191 x_loading_status => x_loading_status
1192 );
1193 -- if the return status is not success then raise an Error
1194 END IF;
1195
1196 IF (x_return_status <> fnd_api.g_ret_sts_success)
1197 THEN
1198 RAISE fnd_api.g_exc_error;
1199 END IF;
1200 -- END IF; -- clku, enhancement 2380234
1201 ELSE
1202 --fix for the Bug 6193694
1203 IF p_period_quotas_rec_tbl.COUNT > 0
1204 THEN
1205 cn_period_quotas_grp.update_period_quotas (p_api_version => p_api_version,
1206 p_init_msg_list => 'T',
1207 p_commit => p_commit,
1208 p_validation_level =>p_validation_level,
1209 x_return_status => x_return_status,
1210 x_msg_count => x_msg_count,
1211 x_msg_data => x_msg_data,
1212 p_quota_name => p_quota_name,
1213 p_period_quotas_rec_tbl =>p_period_quotas_rec_tbl,
1214 x_loading_status => x_loading_status
1215 );
1216 END IF;
1217
1218 -- 2462767, we do not check for formula ID anymore,END IF; -- formula id is NOT NULL
1219 END IF;
1220 -- End Period_Quotas
1221 EXCEPTION
1222 WHEN fnd_api.g_exc_error
1223 THEN
1224 x_return_status := fnd_api.g_ret_sts_error;
1225 WHEN fnd_api.g_exc_unexpected_error
1226 THEN
1227 x_return_status := fnd_api.g_ret_sts_unexp_error;
1228 x_loading_status := 'UNEXPECTED_ERR';
1229 WHEN OTHERS
1230 THEN
1231 x_return_status := fnd_api.g_ret_sts_unexp_error;
1232 x_loading_status := 'UNEXPECTED_ERR';
1233
1234 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1235 THEN
1236 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1237 END IF;
1238 END update_period_quotas;
1239
1240 -- -------------------------------------------------------------------------+-+
1241 --| Procedure: Create_Period_quotas
1242 --| Description: Period_quotas is a localprocedure to create the Default period
1243 --| Quota if the quota type is formula and the formula has the a ITD flag is
1244 --| set to Y then Create or Customize the Period Quotas
1245 --| Called From: Create_plan_Element and Update_Plan_Element
1246 -- -------------------------------------------------------------------------+-+
1247 PROCEDURE create_period_quotas (
1248 p_api_version IN NUMBER,
1249 p_init_msg_list IN VARCHAR2,
1250 p_commit IN VARCHAR2,
1251 p_validation_level IN NUMBER,
1252 x_return_status OUT NOCOPY VARCHAR2,
1253 x_msg_count OUT NOCOPY NUMBER,
1254 x_msg_data OUT NOCOPY VARCHAR2,
1255 p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type,
1256 p_period_quotas_rec_tbl IN period_quotas_rec_tbl_type := g_miss_period_quotas_rec_tbl,
1257 p_quota_name IN VARCHAR2,
1258 p_loading_status IN VARCHAR2,
1259 x_loading_status OUT NOCOPY VARCHAR2,
1260 p_is_duplicate IN VARCHAR2
1261 )
1262 IS
1263 l_tmp NUMBER;
1264 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Period_quotas';
1265 BEGIN
1266 -- Set Status
1267 x_return_status := fnd_api.g_ret_sts_success;
1268 x_loading_status := p_loading_status;
1269
1270 -- Check if the formula id is not null and has ITD flag is Y
1271 -- and check if table.COUNT is zero the call the table handler
1272 -- to create the Period Quotas.
1273 -- if the count is > 0 then Call the Group Package procedure
1274 -- to Create the Custom Period Quotas
1275 -- 2462767, we do not check for formula ID anymore,IF p_pe_rec.calc_formula_id IS NOT NULL THEN
1276 -- Check the ITD flag
1277 -- clku, enhancement 2380234, we do not check for the itd flag anymore
1278 --IF Nvl(cn_api.get_itd_flag(p_pe_rec.calc_formula_id),'N') = 'Y' THEN
1279 -- if count is zero create the default period quotas.
1280 IF p_period_quotas_rec_tbl.COUNT = 0
1281 THEN
1282 cn_period_quotas_pkg.distribute_target (p_pe_rec.quota_id);
1283 ELSE
1284 -- if count is > 0 then create the period quotas with the
1285 -- customised values.
1286 l_tmp := p_period_quotas_rec_tbl.COUNT;
1287 -- Call the Group package to create the Period Quotas
1288 cn_period_quotas_grp.create_period_quotas (p_api_version => p_api_version,
1289 p_init_msg_list => 'T',
1290 p_commit => p_commit,
1291 p_validation_level => p_validation_level,
1292 x_return_status => x_return_status,
1293 x_msg_count => x_msg_count,
1294 x_msg_data => x_msg_data,
1295 p_quota_name => p_quota_name,
1296 p_period_quotas_rec_tbl => p_period_quotas_rec_tbl,
1297 x_loading_status => x_loading_status,
1298 p_is_duplicate => p_is_duplicate
1299 );
1300
1301 -- if the return status is not success then raise an Error
1302 IF (x_return_status <> fnd_api.g_ret_sts_success)
1303 THEN
1304 RAISE fnd_api.g_exc_error;
1305 END IF;
1306 END IF; -- period table count = 0
1307 -- ITD falg is N but they are passing the period quotas info its
1308 -- an error
1309 /*ELSIF Nvl(cn_api.get_itd_flag(p_pe_rec.calc_formula_id),'N') = 'N'
1310 AND p_period_quotas_rec_tbl.COUNT > 0 THEN
1311
1312 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1313 THEN
1314 FND_MESSAGE.SET_NAME ('CN' , 'CN_QUOTA_CANNOT_HAVE_PERIODS');
1315 FND_MSG_PUB.Add;
1316 END IF;
1317 x_loading_status := 'QUOTA_CANNOT_HAVE_PERIODS';
1318 RAISE FND_API.G_EXC_ERROR ;
1319 --END IF; -- clku, enhancement 2380234*/
1320 -- 2462767, we do not check for formula ID anymore,END IF; -- formula id is NOT NULL
1321 -- End Period_Quotas
1322 EXCEPTION
1323 WHEN fnd_api.g_exc_error
1324 THEN
1325 x_return_status := fnd_api.g_ret_sts_error;
1326 WHEN fnd_api.g_exc_unexpected_error
1327 THEN
1328 x_return_status := fnd_api.g_ret_sts_unexp_error;
1329 x_loading_status := 'UNEXPECTED_ERR';
1330 WHEN OTHERS
1331 THEN
1332 x_return_status := fnd_api.g_ret_sts_unexp_error;
1333 x_loading_status := 'UNEXPECTED_ERR';
1334
1335 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1336 THEN
1337 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1338 END IF;
1339 END create_period_quotas;
1340
1341 -- -------------------------------------------------------------------------+-+
1342 --| Procedure: Trx_factors
1343 --| Description: Trx_factors is a local procedure to create the Default trx
1344 --| factors and you customize the trx factors.
1345 -- -------------------------------------------------------------------------+-+
1346 PROCEDURE trx_factors (
1347 x_return_status OUT NOCOPY VARCHAR2,
1348 x_msg_count OUT NOCOPY NUMBER,
1349 x_msg_data OUT NOCOPY VARCHAR2,
1350 p_trx_factor_rec_tbl IN trx_factor_rec_tbl_type,
1351 p_quota_id IN NUMBER,
1352 p_quota_name IN VARCHAR2,
1353 p_loading_status IN VARCHAR2,
1354 x_loading_status OUT NOCOPY VARCHAR2
1355 )
1356 IS
1357 l_tmp NUMBER;
1358 OUTER NUMBER;
1359 INNER NUMBER;
1360 l_api_name CONSTANT VARCHAR2 (30) := 'Trx_Factors';
1361 l_trx_factor_rec_tbl trx_factor_rec_tbl_type;
1362 l_rev_class_id NUMBER;
1363 l_quota_rule_id NUMBER;
1364 l_meaning cn_lookups.meaning%TYPE;
1365 l_loading_status VARCHAR2 (80);
1366 BEGIN
1367 -- Set Status
1368 x_return_status := fnd_api.g_ret_sts_success;
1369 x_loading_status := p_loading_status;
1370
1371 -- Trx Factor data should be loaded from p_trx_factor_rec_tbl,
1372 -- Since we insert data with default value already, so need to
1373 -- Update with the new Factors
1374
1375 -- Sequence the trx factor passed
1376 FOR OUTER IN p_trx_factor_rec_tbl.FIRST .. p_trx_factor_rec_tbl.LAST
1377 LOOP
1378 l_tmp := 0;
1379
1380 IF l_trx_factor_rec_tbl.COUNT > 0
1381 THEN
1382 FOR INNER IN l_trx_factor_rec_tbl.FIRST .. l_trx_factor_rec_tbl.LAST
1383 LOOP
1384 IF (p_trx_factor_rec_tbl (OUTER).rev_class_name = l_trx_factor_rec_tbl (INNER).rev_class_name)
1385 THEN
1386 l_tmp := 1;
1387 END IF;
1388 END LOOP;
1389 END IF;
1390
1391 IF l_tmp = 0
1392 THEN
1393 l_trx_factor_rec_tbl (l_trx_factor_rec_tbl.COUNT + 1) := p_trx_factor_rec_tbl (OUTER);
1394 END IF;
1395 END LOOP;
1396
1397 -- Start update the Process
1398 -- here we avoid the duplicate fetch of revenue class for multiple trx
1399 FOR OUTER IN l_trx_factor_rec_tbl.FIRST .. l_trx_factor_rec_tbl.LAST
1400 LOOP
1401 -- Get revenue class ID from the Database
1402 l_rev_class_id := cn_api.get_rev_class_id (RTRIM (LTRIM (l_trx_factor_rec_tbl (OUTER).rev_class_name)),l_trx_factor_rec_tbl (OUTER).org_id);
1403
1404 -- Check the revenue class name is assigned.
1405 IF l_trx_factor_rec_tbl (OUTER).rev_class_name IS NULL
1406 THEN
1407 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1408 THEN
1409 fnd_message.set_name ('CN', 'CN_REV_CLASS_NOT_ASSIGNED');
1410 fnd_msg_pub.ADD;
1411 END IF;
1412
1413 x_loading_status := 'REV_CLASS_NOT_ASSIGNED';
1414 RAISE fnd_api.g_exc_error;
1415 END IF;
1416
1417 -- check the revenue class exists
1418 IF l_rev_class_id IS NULL AND l_trx_factor_rec_tbl (OUTER).rev_class_name IS NOT NULL
1419 THEN
1420 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1421 THEN
1422 fnd_message.set_name ('CN', 'CN_REV_CLASS_NOT_EXIST');
1423 fnd_msg_pub.ADD;
1424 END IF;
1425
1426 x_loading_status := 'CN_REV_CLASS_NOT_EXIST';
1427 RAISE fnd_api.g_exc_error;
1428 END IF;
1429
1430 -- get the quota rule id using the quota id and revenue class id
1431 l_quota_rule_id := cn_chk_plan_element_pkg.get_quota_rule_id (p_quota_id => p_quota_id, p_rev_class_id => l_rev_class_id);
1432
1433 -- Quota rule_id is null raise an error
1434 IF l_quota_rule_id IS NULL
1435 THEN
1436 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1437 THEN
1438 fnd_message.set_name ('CN', 'CN_QUOTA_RULE_NOT_EXIST');
1439 fnd_message.set_token ('PLAN_NAME', p_quota_name);
1440 fnd_message.set_token ('REVENUE_CLASS_NAME', l_trx_factor_rec_tbl (OUTER).rev_class_name);
1441 fnd_msg_pub.ADD;
1442 END IF;
1443
1444 x_loading_status := 'QUOTA_RULE_NOT_EXIST';
1445 RAISE fnd_api.g_exc_error;
1446 END IF;
1447
1448 FOR INNER IN p_trx_factor_rec_tbl.FIRST .. p_trx_factor_rec_tbl.LAST
1449 LOOP
1450 IF (p_trx_factor_rec_tbl (INNER).rev_class_name = l_trx_factor_rec_tbl (OUTER).rev_class_name)
1451 THEN
1452 -- More validation to be done. Update the Event Column
1453 l_meaning := cn_api.get_lkup_meaning (p_trx_factor_rec_tbl (INNER).trx_type, 'TRX TYPES');
1454
1455 IF l_meaning IS NULL
1456 THEN
1457 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1458 THEN
1459 fnd_message.set_name ('CN', 'CN_TRX_TYPE_NOT_EXIST');
1460 fnd_message.set_token ('TRANSACTION_TYPE', p_trx_factor_rec_tbl (INNER).trx_type);
1461 fnd_msg_pub.ADD;
1462 END IF;
1463
1464 x_loading_status := 'CN_TRX_TYPE_NOT_EXISTS';
1465 RAISE fnd_api.g_exc_error;
1466 END IF;
1467
1468 UPDATE cn_trx_factors
1469 SET event_factor = p_trx_factor_rec_tbl (OUTER).event_factor
1470 WHERE quota_rule_id = l_quota_rule_id AND quota_id = p_quota_id AND trx_type = p_trx_factor_rec_tbl (INNER).trx_type;
1471 END IF; -- trx Factor Exists
1472 END LOOP; -- Trx Loop
1473
1474 -- validate Rule :
1475 -- Check TRX_FACTORS
1476 -- 1. Key Factor's total = 100
1477 -- 2. Must have Trx_Factors
1478 cn_chk_plan_element_pkg.chk_trx_factor (x_return_status => x_return_status,
1479 p_quota_rule_id => l_quota_rule_id,
1480 p_rev_class_name => l_trx_factor_rec_tbl (OUTER).rev_class_name,
1481 p_loading_status => x_loading_status,
1482 x_loading_status => l_loading_status
1483 );
1484 x_loading_status := l_loading_status;
1485
1486 IF (x_return_status <> fnd_api.g_ret_sts_success) OR x_loading_status NOT IN ('CN_UPDATED', 'CN_INSERTED')
1487 THEN
1488 RAISE fnd_api.g_exc_error;
1489 END IF;
1490 END LOOP; -- Outer trx Loop
1491 -- End Trx Factors
1492 EXCEPTION
1493 WHEN fnd_api.g_exc_error
1494 THEN
1495 x_return_status := fnd_api.g_ret_sts_error;
1496 WHEN fnd_api.g_exc_unexpected_error
1497 THEN
1498 x_return_status := fnd_api.g_ret_sts_unexp_error;
1499 x_loading_status := 'UNEXPECTED_ERR';
1500 WHEN OTHERS
1501 THEN
1502 x_return_status := fnd_api.g_ret_sts_unexp_error;
1503 x_loading_status := 'UNEXPECTED_ERR';
1504
1505 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1506 THEN
1507 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1508 END IF;
1509 END trx_factors;
1510
1511 -- -------------------------------------------------------------------------+-+
1512 --| Procedure: Check_quota_exists
1513 --| Description: Check_quota_exists is a local procedure to check the quota is
1514 --| is exists
1515 --| Called From: Check_valid_Update
1516 -- -------------------------------------------------------------------------+-+
1517 PROCEDURE check_quota_exists (
1518 x_return_status OUT NOCOPY VARCHAR2,
1519 x_msg_count OUT NOCOPY NUMBER,
1520 x_msg_data OUT NOCOPY VARCHAR2,
1521 p_quota_name_old IN VARCHAR2,
1522 x_quota_id OUT NOCOPY NUMBER,
1523 p_loading_status IN VARCHAR2,
1524 x_loading_status OUT NOCOPY VARCHAR2
1525 )
1526 IS
1527 l_api_name CONSTANT VARCHAR2 (30) := 'Check_Quota_Exists';
1528 l_same_pe NUMBER;
1529 l_loading_status VARCHAR2 (80);
1530
1531 CURSOR c_pe_rec_old_csr (
1532 pe_name cn_quotas.NAME%TYPE
1533 )
1534 IS
1535 SELECT q.quota_id
1536 FROM cn_quotas_v q
1537 WHERE q.NAME = pe_name;
1538 BEGIN
1539 -- Initialize API return status to success
1540 x_return_status := fnd_api.g_ret_sts_success;
1541 x_loading_status := p_loading_status;
1542
1543 -- Get the Old record quota id and Formula id to update and
1544 -- delete the rate Quota assigns, in the table handler
1545 -- Check if old plan element name is missing or null
1546 IF ((cn_api.chk_miss_char_para (p_char_para => p_quota_name_old,
1547 p_para_name => cn_chk_plan_element_pkg.g_pe_name,
1548 p_loading_status => x_loading_status,
1549 x_loading_status => l_loading_status
1550 )
1551 ) = fnd_api.g_true
1552 )
1553 THEN
1554 RAISE fnd_api.g_exc_error;
1555 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_quota_name_old,
1556 p_obj_name => cn_chk_plan_element_pkg.g_pe_name,
1557 p_loading_status => x_loading_status,
1558 x_loading_status => l_loading_status
1559 )
1560 ) = fnd_api.g_true
1561 )
1562 THEN
1563 RAISE fnd_api.g_exc_error;
1564 END IF;
1565
1566 -- get the old plan element record
1567 OPEN c_pe_rec_old_csr (p_quota_name_old);
1568
1569 FETCH c_pe_rec_old_csr
1570 INTO x_quota_id;
1571
1572 CLOSE c_pe_rec_old_csr;
1573
1574 -- Check the Old Plan Element Exists in the Database
1575 IF x_quota_id IS NULL
1576 THEN
1577 IF p_quota_name_old IS NOT NULL
1578 THEN
1579 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1580 THEN
1581 fnd_message.set_name ('CN', 'CN_PLN_NOT_EXIST');
1582 fnd_message.set_token ('PE_NAME', p_quota_name_old);
1583 fnd_msg_pub.ADD;
1584 END IF;
1585
1586 x_loading_status := 'CN_PLN_NOT_EXIST';
1587 RAISE fnd_api.g_exc_error;
1588 END IF;
1589 END IF;
1590
1591 -- Standard message count
1592 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1593 -- end check_quota_exists
1594 EXCEPTION
1595 WHEN fnd_api.g_exc_error
1596 THEN
1597 x_return_status := fnd_api.g_ret_sts_error;
1598 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1599 WHEN fnd_api.g_exc_unexpected_error
1600 THEN
1601 x_return_status := fnd_api.g_ret_sts_unexp_error;
1602 x_loading_status := 'UNEXPECTED_ERR';
1603 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1604 WHEN OTHERS
1605 THEN
1606 x_return_status := fnd_api.g_ret_sts_unexp_error;
1607 x_loading_status := 'UNEXPECTED_ERR';
1608
1609 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1610 THEN
1611 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1612 END IF;
1613
1614 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1615 END check_quota_exists;
1616
1617 -- -------------------------------------------------------------------------+-+
1618 --| Procedure: Valid_plan_Element
1619 --| Description: Validate plan Element is a local procedure to Validate the Plan
1620 --| Element.
1621 --| Called From: Create_plan_Element and Update_Plan_Element
1622 -- -------------------------------------------------------------------------+-+
1623 PROCEDURE valid_plan_element (
1624 x_return_status OUT NOCOPY VARCHAR2,
1625 x_msg_count OUT NOCOPY NUMBER,
1626 x_msg_data OUT NOCOPY VARCHAR2,
1627 p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec,
1628 p_quota_name_old IN VARCHAR2,
1629 p_loading_status IN VARCHAR2,
1630 x_loading_status OUT NOCOPY VARCHAR2
1631 )
1632 IS
1633 l_api_name CONSTANT VARCHAR2 (30) := 'Valid_Plan_Element';
1634 l_same_pe NUMBER;
1635 l_loading_status VARCHAR2 (80);
1636 BEGIN
1637 -- Initialize API return status to success
1638 x_return_status := fnd_api.g_ret_sts_success;
1639 x_loading_status := p_loading_status;
1640 -- API body
1641 -- check for required data in Plan Element
1642 -- Check MISS and NULL parameters
1643 chk_pe_required (x_return_status => x_return_status,
1644 p_pe_rec => p_pe_rec,
1645 p_loading_status => x_loading_status,
1646 x_loading_status => l_loading_status
1647 );
1648 x_loading_status := l_loading_status;
1649
1650 IF (x_return_status <> fnd_api.g_ret_sts_success)
1651 THEN
1652 RAISE fnd_api.g_exc_error;
1653 END IF;
1654
1655 -- If Plan already exist, check for consistentent
1656 IF p_pe_rec.quota_id IS NOT NULL AND p_quota_name_old IS NULL
1657 THEN -- Plan Element Exists
1658 chk_pe_consistent (x_return_status => x_return_status,
1659 p_pe_rec => p_pe_rec,
1660 p_loading_status => x_loading_status,
1661 x_loading_status => l_loading_status
1662 );
1663 x_loading_status := l_loading_status;
1664
1665 IF (x_return_status <> fnd_api.g_ret_sts_success)
1666 THEN
1667 RAISE fnd_api.g_exc_error;
1668 END IF;
1669
1670 x_loading_status := 'PLN_QUOTA_EXISTS';
1671 GOTO end_api_body;
1672 END IF;
1673
1674 -- Validate Rule : End period must be greater than Start period
1675 IF (p_pe_rec.end_date IS NOT NULL AND TRUNC (p_pe_rec.end_date) < TRUNC (p_pe_rec.start_date))
1676 THEN
1677 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1678 THEN
1679 fnd_message.set_name ('CN', 'CN_INVALID_DATE_RANGE');
1680 fnd_msg_pub.ADD;
1681 END IF;
1682
1683 x_loading_status := 'INVALID_END_DATE';
1684 RAISE fnd_api.g_exc_error;
1685 END IF;
1686
1687 -- Formula name is not null but the ID is not in the Database
1688 -- Raise an Error
1689 IF (p_pe_rec.calc_formula_name IS NOT NULL AND p_pe_rec.calc_formula_id IS NULL)
1690 THEN
1691 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1692 THEN
1693 fnd_message.set_name ('CN', 'CN_FORMULA_NOT_EXIST');
1694 fnd_message.set_token ('FORMULA_NAME', p_pe_rec.calc_formula_name);
1695 fnd_msg_pub.ADD;
1696 END IF;
1697
1698 x_loading_status := 'FORMULA_NOT_EXIST';
1699 RAISE fnd_api.g_exc_error;
1700 END IF;
1701
1702 -- Validate All lookup codes, must have valid value
1703 valid_lookup_code (x_return_status => x_return_status,
1704 p_pe_rec => p_pe_rec,
1705 p_loading_status => x_loading_status,
1706 x_loading_status => l_loading_status
1707 );
1708 x_loading_status := l_loading_status;
1709
1710 IF (x_return_status <> fnd_api.g_ret_sts_success)
1711 THEN
1712 RAISE fnd_api.g_exc_error;
1713 END IF;
1714
1715 -- Validate the Quota Type with the Respective Column
1716 -- Check if the quota type is formula then the formula name must be not null
1717 -- Check if the quota type is formula the package name must be null
1718 IF (p_pe_rec.quota_type_code = 'FORMULA')
1719 THEN
1720 -- if Quota type is Formula, then Formula is Mandatory and
1721 -- Package name must be null
1722 cn_chk_plan_element_pkg.chk_formula_quota_pe (x_return_status => x_return_status,
1723 p_pe_rec => p_pe_rec,
1724 p_loading_status => x_loading_status,
1725 x_loading_status => l_loading_status
1726 );
1727 x_loading_status := l_loading_status;
1728
1729 IF (x_return_status <> fnd_api.g_ret_sts_success)
1730 THEN
1731 x_loading_status := 'INVALID_DATA';
1732 RAISE fnd_api.g_exc_error;
1733 END IF;
1734 ELSIF (p_pe_rec.quota_type_code = 'EXTERNAL')
1735 THEN
1736 -- if Quota type is External Package name is Mandatory and
1737 -- formula must be null
1738 cn_chk_plan_element_pkg.chk_external_quota_pe (x_return_status => x_return_status,
1739 p_pe_rec => p_pe_rec,
1740 p_loading_status => x_loading_status,
1741 x_loading_status => l_loading_status
1742 );
1743 x_loading_status := l_loading_status;
1744
1745 IF (x_return_status <> fnd_api.g_ret_sts_success)
1746 THEN
1747 x_loading_status := 'INVALID_DATA';
1748 RAISE fnd_api.g_exc_error;
1749 END IF;
1750 ELSIF (p_pe_rec.quota_type_code = 'NONE')
1751 THEN
1752 -- If quota type is NONE, both Formula and package must be null
1753 cn_chk_plan_element_pkg.chk_other_quota_pe (x_return_status => x_return_status,
1754 p_pe_rec => p_pe_rec,
1755 p_loading_status => x_loading_status,
1756 x_loading_status => l_loading_status
1757 );
1758 x_loading_status := l_loading_status;
1759
1760 IF (x_return_status <> fnd_api.g_ret_sts_success)
1761 THEN
1762 x_loading_status := 'INVALID_DATA';
1763 RAISE fnd_api.g_exc_error;
1764 END IF;
1765 END IF;
1766
1767 -- End of API body.
1768 <<end_api_body>>
1769 NULL;
1770 -- Standard call to get message count and if count is 1, get message info.
1771 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1772 -- end valid_plan_element
1773 EXCEPTION
1774 WHEN fnd_api.g_exc_error
1775 THEN
1776 x_return_status := fnd_api.g_ret_sts_error;
1777 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1778 WHEN fnd_api.g_exc_unexpected_error
1779 THEN
1780 x_return_status := fnd_api.g_ret_sts_unexp_error;
1781 x_loading_status := 'UNEXPECTED_ERR';
1782 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1783 WHEN OTHERS
1784 THEN
1785 x_return_status := fnd_api.g_ret_sts_unexp_error;
1786 x_loading_status := 'UNEXPECTED_ERR';
1787
1788 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1789 THEN
1790 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1791 END IF;
1792
1793 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1794 END valid_plan_element;
1795
1796 --|--------------------------------------------------------------------------+
1797 --|Procedure: Check Valid Update
1798 --|Description:This procedure is called from update plan element and it will be
1799 --|called only if there is a old plan element name passed. first to check the
1800 --| new plan element name is unique and it should pass all the validations.
1801 --|secondly if there is calc formula assigns before that we need to delete the
1802 --|old rate quota assigns. that will cacade if there is srp rate quota assigs.
1803 --|rate quota assigs, we would take care at the table handler level
1804 -- --------------------------------------------------------------------------+
1805 PROCEDURE check_valid_update (
1806 x_return_status OUT NOCOPY VARCHAR2,
1807 x_msg_count OUT NOCOPY NUMBER,
1808 x_msg_data OUT NOCOPY VARCHAR2,
1809 p_quota_name_old IN VARCHAR2,
1810 p_new_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec,
1811 x_old_pe_rec OUT NOCOPY cn_chk_plan_element_pkg.pe_rec_type,
1812 p_loading_status IN VARCHAR2,
1813 x_loading_status OUT NOCOPY VARCHAR2
1814 )
1815 IS
1816 l_api_name CONSTANT VARCHAR2 (30) := 'Check_Valid_Update';
1817 l_same_pe NUMBER;
1818 l_loading_status VARCHAR2 (80);
1819
1820 CURSOR c_pe_rec_old_csr (
1821 pe_name cn_quotas.NAME%TYPE
1822 )
1823 IS
1824 SELECT q.quota_id,
1825 q.calc_formula_id,
1826 cn_chk_plan_element_pkg.get_calc_formula_name (q.calc_formula_id),
1827 -- clku, 5/9/2002
1828 q.quota_type_code,
1829 q.start_date,
1830 q.end_date
1831 FROM cn_quotas_v q
1832 WHERE q.NAME = pe_name;
1833 BEGIN
1834 -- Initialize API return status to success
1835 x_return_status := fnd_api.g_ret_sts_success;
1836 x_loading_status := p_loading_status;
1837
1838 -- Get the Old record quota id and Formula id to update and
1839 -- delete the rate Quota assigns, in the table handler
1840 OPEN c_pe_rec_old_csr (p_quota_name_old);
1841
1842 FETCH c_pe_rec_old_csr
1843 INTO x_old_pe_rec.quota_id,
1844 x_old_pe_rec.calc_formula_id,
1845 x_old_pe_rec.calc_formula_name,
1846 --clku, 5/9/2002
1847 x_old_pe_rec.quota_type_code,
1848 x_old_pe_rec.start_date,
1849 x_old_pe_rec.end_date;
1850
1851 CLOSE c_pe_rec_old_csr;
1852
1853 -- Check the Old Plan Element Exists in the Database
1854 -- Update case 1
1855 -- if the old quota id is null you cannot update main plan element
1856 -- if the old quota is null but the you pass a bad pe name it is an error
1857 -- if old quota id is null then there is child update but ther should be
1858 -- new quota id there has to be a quota id for child update
1859 IF x_old_pe_rec.quota_id IS NULL
1860 THEN
1861 IF p_quota_name_old IS NOT NULL
1862 THEN
1863 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1864 THEN
1865 fnd_message.set_name ('CN', 'CN_PLN_NOT_EXIST');
1866 fnd_message.set_token ('PE_NAME', p_quota_name_old);
1867 fnd_msg_pub.ADD;
1868 END IF;
1869
1870 x_loading_status := 'CN_PLN_NOT_EXIST';
1871 RAISE fnd_api.g_exc_error;
1872 ELSIF p_new_pe_rec.quota_id IS NOT NULL
1873 THEN
1874 x_loading_status := 'PLN_QUOTA_EXISTS';
1875 END IF;
1876 ELSE
1877 -- Update case 2 ( else )
1878 -- if the old quota is not null then chances of update on both parent and child
1879 -- or just parent.
1880 -- Check the New Quota name, must be unique
1881 IF p_new_pe_rec.quota_id IS NOT NULL AND p_new_pe_rec.quota_id <> x_old_pe_rec.quota_id
1882 THEN
1883 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1884 THEN
1885 fnd_message.set_name ('CN', 'PLN_QUOTA_EXISTS');
1886 fnd_msg_pub.ADD;
1887 END IF;
1888
1889 x_loading_status := 'CN_PLN_EXISTS';
1890 RAISE fnd_api.g_exc_error;
1891 END IF;
1892 END IF;
1893
1894 -- Assiged quota to comp plan check if the start date and the end date changes.
1895 IF TRUNC (p_new_pe_rec.start_date) <> TRUNC (x_old_pe_rec.start_date)
1896 OR TRUNC (NVL (p_new_pe_rec.end_date, fnd_api.g_miss_date)) <> TRUNC (NVL (x_old_pe_rec.end_date, fnd_api.g_miss_date))
1897 THEN
1898 cn_chk_plan_element_pkg.chk_comp_plan_date (x_return_status => x_return_status,
1899 p_start_date => p_new_pe_rec.start_date,
1900 p_end_date => p_new_pe_rec.end_date,
1901 p_quota_id => x_old_pe_rec.quota_id,
1902 p_quota_name => p_quota_name_old,
1903 p_loading_status => x_loading_status,
1904 x_loading_status => l_loading_status
1905 );
1906 x_loading_status := l_loading_status;
1907
1908 IF (x_return_status <> fnd_api.g_ret_sts_success)
1909 THEN
1910 RAISE fnd_api.g_exc_error;
1911 END IF;
1912
1913 -- Check the Plan Element start date and end date fall with in the rt_formula_asgns
1914 IF p_new_pe_rec.calc_formula_id IS NOT NULL
1915 THEN
1916 IF p_new_pe_rec.calc_formula_id = x_old_pe_rec.calc_formula_id
1917 THEN
1918 cn_chk_plan_element_pkg.chk_rate_quota_date (x_return_status => x_return_status,
1919 p_start_date => p_new_pe_rec.start_date,
1920 p_end_date => p_new_pe_rec.end_date,
1921 p_quota_name => p_new_pe_rec.NAME,
1922 p_quota_id => p_new_pe_rec.quota_id,
1923 p_loading_status => x_loading_status,
1924 x_loading_status => l_loading_status
1925 );
1926 x_loading_status := l_loading_status;
1927 END IF;
1928
1929 -- error if the status is not success
1930 IF (x_return_status <> fnd_api.g_ret_sts_success)
1931 THEN
1932 RAISE fnd_api.g_exc_error;
1933 END IF;
1934 END IF;
1935
1936 -- Check the Plan Element start date and end date fall with in the uplift start date
1937 -- and end date
1938 IF p_new_pe_rec.quota_id IS NOT NULL
1939 THEN
1940 cn_chk_plan_element_pkg.chk_uplift_date (x_return_status => x_return_status,
1941 p_start_date => p_new_pe_rec.start_date,
1942 p_end_date => p_new_pe_rec.end_date,
1943 p_quota_name => p_new_pe_rec.NAME,
1944 p_quota_id => p_new_pe_rec.quota_id,
1945 p_loading_status => x_loading_status,
1946 x_loading_status => l_loading_status
1947 );
1948 x_loading_status := l_loading_status;
1949
1950 -- error if the status is not success
1951 IF (x_return_status <> fnd_api.g_ret_sts_success)
1952 THEN
1953 RAISE fnd_api.g_exc_error;
1954 END IF;
1955 END IF;
1956 END IF;
1957
1958 -- Go through the normal validation for update
1959 valid_plan_element (x_return_status => x_return_status,
1960 x_msg_count => x_msg_count,
1961 x_msg_data => x_msg_data,
1962 p_pe_rec => p_new_pe_rec,
1963 p_quota_name_old => p_quota_name_old,
1964 p_loading_status => x_loading_status,
1965 x_loading_status => l_loading_status
1966 );
1967 x_loading_status := l_loading_status;
1968
1969 -- Raise an Error if the Status is not success
1970 IF (x_return_status <> fnd_api.g_ret_sts_success)
1971 THEN
1972 RAISE fnd_api.g_exc_error;
1973 END IF;
1974
1975 -- Standard call to get message count and if count is 1, get message info.
1976 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1977 -- End of Check_valid_update
1978 EXCEPTION
1979 WHEN fnd_api.g_exc_error
1980 THEN
1981 x_return_status := fnd_api.g_ret_sts_error;
1982 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1983 WHEN fnd_api.g_exc_unexpected_error
1984 THEN
1985 x_return_status := fnd_api.g_ret_sts_unexp_error;
1986 x_loading_status := 'UNEXPECTED_ERR';
1987 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1988 WHEN OTHERS
1989 THEN
1990 x_return_status := fnd_api.g_ret_sts_unexp_error;
1991 x_loading_status := 'UNEXPECTED_ERR';
1992
1993 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1994 THEN
1995 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1996 END IF;
1997
1998 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1999 END check_valid_update;
2000
2001 --|--------------------------------------------------------------------------+
2002 --|Procedure: Create_plan_element
2003 --|Description:This is a Public procedure is used to create the Plan Element
2004 --|and create their respective child records
2005 -- --------------------------------------------------------------------------+
2006 PROCEDURE create_plan_element (
2007 p_api_version IN NUMBER := 0,
2008 p_init_msg_list IN VARCHAR2 := cn_api.g_false,
2009 p_commit IN VARCHAR2 := cn_api.g_false,
2010 p_validation_level IN NUMBER := cn_api.g_valid_level_full,
2011 x_return_status OUT NOCOPY VARCHAR2,
2012 x_msg_count OUT NOCOPY NUMBER,
2013 x_msg_data OUT NOCOPY VARCHAR2,
2014 p_plan_element_rec IN plan_element_rec_type := g_miss_plan_element_rec,
2015 p_revenue_class_rec_tbl IN revenue_class_rec_tbl_type := g_miss_revenue_class_rec_tbl,
2016 p_rev_uplift_rec_tbl IN rev_uplift_rec_tbl_type := g_miss_rev_uplift_rec_tbl,
2017 p_trx_factor_rec_tbl IN trx_factor_rec_tbl_type := g_miss_trx_factor_rec_tbl,
2018 p_period_quotas_rec_tbl IN period_quotas_rec_tbl_type := g_miss_period_quotas_rec_tbl,
2019 p_rt_quota_asgns_rec_tbl IN rt_quota_asgns_rec_tbl_type := g_miss_rt_quota_asgns_rec_tbl,
2020 x_loading_status OUT NOCOPY VARCHAR2,
2021 p_is_duplicate IN VARCHAR2 DEFAULT 'N'
2022 )
2023 IS
2024 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Plan_Element';
2025 l_api_version CONSTANT NUMBER := 1.0;
2026 l_pe_rec cn_chk_plan_element_pkg.pe_rec_type;
2027 l_trx_factor_rec_tbl trx_factor_rec_tbl_type;
2028 l_quota_rule_id cn_quota_rules.quota_rule_id%TYPE;
2029 l_per_quota_id cn_period_quotas.period_quota_id%TYPE;
2030 l_tmp NUMBER;
2031 l_meaning cn_lookups.meaning%TYPE;
2032 l_p_plan_element_rec plan_element_rec_type;
2033 l_p_revenue_class_rec_tbl revenue_class_rec_tbl_type;
2034 l_p_rev_uplift_rec_tbl rev_uplift_rec_tbl_type;
2035
2036 l_p_rev_uplift_rec_tbl1 cn_quota_rule_uplift_pvt.quota_rule_uplift_tbl_type;
2037
2038 l_p_trx_factor_rec_tbl trx_factor_rec_tbl_type;
2039 l_p_period_quotas_rec_tbl period_quotas_rec_tbl_type;
2040 l_p_rt_quota_asgns_rec_tbl rt_quota_asgns_rec_tbl_type;
2041 l_oai_array jtf_usr_hks.oai_data_array_type;
2042 l_bind_data_id NUMBER;
2043 g_last_update_date DATE := SYSDATE;
2044 g_last_updated_by NUMBER := fnd_global.user_id;
2045 g_creation_date DATE := SYSDATE;
2046 g_created_by NUMBER := fnd_global.user_id;
2047 g_last_update_login NUMBER := fnd_global.login_id;
2048 g_rowid VARCHAR2 (30);
2049 g_program_type VARCHAR2 (30);
2050 l_loading_status VARCHAR2 (80);
2051 l_org_id NUMBER;
2052 l_status VARCHAR2(1);
2053 p_payment_group_code l_p_plan_element_rec.payment_group_code%type;
2054
2055 BEGIN
2056 -- Standard Start of API savepoint
2057 SAVEPOINT create_plan_element;
2058
2059 -- Standard call to check for call compatibility.
2060 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
2061 THEN
2062 RAISE fnd_api.g_exc_unexpected_error;
2063 END IF;
2064
2065 -- Initialize message list if p_init_msg_list is set to TRUE.
2066 IF fnd_api.to_boolean (p_init_msg_list)
2067 THEN
2068 fnd_msg_pub.initialize;
2069 END IF;
2070
2071 -- Initialize API return status to success
2072 x_return_status := fnd_api.g_ret_sts_success;
2073 x_loading_status := 'CN_INSERTED';
2074
2075 -- START OF MOAC ORG_ID VALIDATION
2076 l_org_id := p_plan_element_rec.org_id;
2077 mo_global.validate_orgid_pub_api(org_id => l_org_id,
2078 status => l_status);
2079
2080 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2081 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2082 'cn.plsql.cn_plan_element_pub.create_plan_element.org_validate',
2083 'Validated org_id = ' || l_org_id || ' status = '||l_status);
2084 end if;
2085 -- END OF MOAC ORG_ID VALIDATION
2086
2087 -- API body
2088 l_p_plan_element_rec := p_plan_element_rec;
2089 l_p_revenue_class_rec_tbl := p_revenue_class_rec_tbl;
2090 l_p_rev_uplift_rec_tbl := p_rev_uplift_rec_tbl;
2091 l_p_trx_factor_rec_tbl := p_trx_factor_rec_tbl;
2092 l_p_period_quotas_rec_tbl := p_period_quotas_rec_tbl;
2093 l_p_rt_quota_asgns_rec_tbl := p_rt_quota_asgns_rec_tbl;
2094 p_payment_group_code := l_p_plan_element_rec.payment_group_code;
2095 -- Validate Payment group code
2096 validate_payment_group_code(x_return_status => x_return_status,
2097 p_payment_group_code => p_payment_group_code);
2098
2099 IF (x_return_status <> fnd_api.g_ret_sts_success)
2100 THEN
2101 RAISE fnd_api.g_exc_error;
2102 ELSE
2103 l_p_plan_element_rec.payment_group_code := p_payment_group_code;
2104 END IF;
2105
2106
2107
2108 /* pre processing call */
2109 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'CREATE_PLAN_ELEMENT', 'B', 'C')
2110 THEN
2111 cn_plan_element_cuhk.create_plan_element_pre (p_api_version => p_api_version,
2112 p_init_msg_list => p_init_msg_list,
2113 p_commit => fnd_api.g_false,
2114 p_validation_level => p_validation_level,
2115 x_return_status => x_return_status,
2116 x_msg_count => x_msg_count,
2117 x_msg_data => x_msg_data,
2118 p_plan_element_rec => l_p_plan_element_rec,
2119 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
2120 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
2121 p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
2122 p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
2123 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
2124 x_loading_status => x_loading_status
2125 );
2126
2127 IF (x_return_status = fnd_api.g_ret_sts_error)
2128 THEN
2129 RAISE fnd_api.g_exc_error;
2130 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2131 THEN
2132 RAISE fnd_api.g_exc_unexpected_error;
2133 END IF;
2134 END IF;
2135
2136 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'CREATE_PLAN_ELEMENT', 'B', 'V')
2137 THEN
2138 cn_plan_element_vuhk.create_plan_element_pre (p_api_version => p_api_version,
2139 p_init_msg_list => p_init_msg_list,
2140 p_commit => fnd_api.g_false,
2141 p_validation_level => p_validation_level,
2142 x_return_status => x_return_status,
2143 x_msg_count => x_msg_count,
2144 x_msg_data => x_msg_data,
2145 p_plan_element_rec => l_p_plan_element_rec,
2146 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
2147 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
2148 p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
2149 p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
2150 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
2151 x_loading_status => x_loading_status
2152 );
2153
2154 IF (x_return_status = fnd_api.g_ret_sts_error)
2155 THEN
2156 RAISE fnd_api.g_exc_error;
2157 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2158 THEN
2159 RAISE fnd_api.g_exc_unexpected_error;
2160 END IF;
2161 END IF;
2162
2163 -- Store the User Input Value into The Local Variable.
2164 l_pe_rec :=
2165 convert_pe_user_input (x_return_status => x_return_status,
2166 p_plan_element_rec => l_p_plan_element_rec,
2167 p_loading_status => x_loading_status,
2168 x_loading_status => l_loading_status
2169 );
2170 x_loading_status := l_loading_status;
2171
2172 IF (x_return_status <> fnd_api.g_ret_sts_success)
2173 THEN
2174 RAISE fnd_api.g_exc_error;
2175 END IF;
2176
2177 --
2178 -- Validate Plan Element
2179 -- if the Quota id is null then there must be change of insert
2180 -- in the revenue class, accelerator or trx factors.
2181 --
2182 IF l_pe_rec.quota_id IS NULL AND l_pe_rec.quota_status = 'COMPLETE'
2183 THEN
2184 valid_plan_element (x_return_status => x_return_status,
2185 x_msg_count => x_msg_count,
2186 x_msg_data => x_msg_data,
2187 p_pe_rec => l_pe_rec,
2188 p_quota_name_old => NULL,
2189 p_loading_status => x_loading_status,
2190 x_loading_status => l_loading_status
2191 );
2192 x_loading_status := l_loading_status;
2193 -- returns status false in any failure but return success status with
2194 -- record exists
2195 -- these are the two possibilities.
2196 END IF;
2197
2198 -- Case 1 Plan Element Does not exists then you can create the Plan Element
2199 -- and create the respective child records if the record passes thru
2200 -- RECORD variables. Possible child records are
2201 -- 1 Quota Rules, Rule Uplifts, Trx Factors, periods.
2202
2203 -- Case 2 Plan Element exists and adding new child records like quota rules,
2204 -- uplifts, trx factors, period quotas ( if no child record is passed
2205 -- then it is an error saying duplicate Plan element
2206 IF (x_return_status <> fnd_api.g_ret_sts_success)
2207 THEN
2208 RAISE fnd_api.g_exc_error;
2209 ELSIF (x_loading_status <> 'PLN_QUOTA_EXISTS')
2210 THEN
2211 --x_status_code VARCHAR2,
2212 --clku PAYMENT ENHANCEMENT
2213 --clku, bug 2854576
2214 -- fmburu r12
2215
2216 -- Plan Element does not exits, Create the New Plan Element
2217 cn_quotas_pkg.begin_record (x_operation => 'INSERT',
2218 x_org_id => l_pe_rec.org_id,
2219 x_object_version_number => l_pe_rec.object_version_number,
2220 x_rowid => g_rowid,
2221 x_indirect_credit => l_pe_rec.indirect_credit,
2222 x_quota_id => l_pe_rec.quota_id,
2223 x_name => l_pe_rec.NAME,
2224 x_target => l_pe_rec.target,
2225 x_quota_type_code => l_pe_rec.quota_type_code,
2226 x_usage_code => NULL,
2227 x_payment_amount => l_pe_rec.payment_amount,
2228 x_description => l_pe_rec.description,
2229 x_start_date => l_pe_rec.start_date,
2230 x_end_date => l_pe_rec.end_date,
2231 x_quota_status => l_pe_rec.quota_status,
2232 x_calc_formula_id => l_pe_rec.calc_formula_id,
2233 x_incentive_type_code => l_pe_rec.incentive_type_code,
2234 x_credit_type_id => l_pe_rec.credit_type_id,
2235 x_rt_sched_custom_flag => l_pe_rec.rt_sched_custom_flag,
2236 x_package_name => l_pe_rec.package_name,
2237 x_performance_goal => l_pe_rec.performance_goal,
2238 x_interval_type_id => l_pe_rec.interval_type_id,
2239 x_payee_assign_flag => l_pe_rec.payee_assign_flag,
2240 x_vesting_flag => l_pe_rec.vesting_flag,
2241 x_expense_account_id => l_p_plan_element_rec.expense_account_id,
2242 x_liability_account_id => l_p_plan_element_rec.liability_account_id,
2243 x_quota_group_code => l_p_plan_element_rec.quota_group_code, --clku PAYMENT ENHANCEMENT,
2244 x_payment_group_code => l_p_plan_element_rec.payment_group_code,
2245 x_quota_unspecified => NULL,
2246 x_last_update_date => g_last_update_date,
2247 x_last_updated_by => g_last_updated_by,
2248 x_creation_date => g_creation_date,
2249 x_created_by => g_created_by,
2250 x_last_update_login => g_last_update_login,
2251 x_program_type => g_program_type,
2252 x_period_type_code => NULL,
2253 x_start_num => NULL,
2254 x_end_num => NULL,
2255 x_addup_from_rev_class_flag => l_pe_rec.addup_from_rev_class_flag
2256 --clku, bug 2854576
2257 ,
2258 x_attribute_category => l_p_plan_element_rec.attribute_category,
2259 x_attribute1 => l_p_plan_element_rec.attribute1,
2260 x_attribute2 => l_p_plan_element_rec.attribute2,
2261 x_attribute3 => l_p_plan_element_rec.attribute3,
2262 x_attribute4 => l_p_plan_element_rec.attribute4,
2263 x_attribute5 => l_p_plan_element_rec.attribute5,
2264 x_attribute6 => l_p_plan_element_rec.attribute6,
2265 x_attribute7 => l_p_plan_element_rec.attribute7,
2266 x_attribute8 => l_p_plan_element_rec.attribute8,
2267 x_attribute9 => l_p_plan_element_rec.attribute9,
2268 x_attribute10 => l_p_plan_element_rec.attribute10,
2269 x_attribute11 => l_p_plan_element_rec.attribute11,
2270 x_attribute12 => l_p_plan_element_rec.attribute12,
2271 x_attribute13 => l_p_plan_element_rec.attribute13,
2272 x_attribute14 => l_p_plan_element_rec.attribute14,
2273 x_attribute15 => l_p_plan_element_rec.attribute15,
2274 x_salesrep_end_flag => l_p_plan_element_rec.sreps_enddated_flag
2275 );
2276 -- Record succefully inserted..
2277
2278 -- Call the Period Quotas local procedure to create Period Quotas
2279 create_period_quotas (p_api_version => p_api_version,
2280 p_init_msg_list => p_init_msg_list,
2281 p_commit => p_commit,
2282 p_validation_level => p_validation_level,
2283 x_return_status => x_return_status,
2284 x_msg_count => x_msg_count,
2285 x_msg_data => x_msg_data,
2286 p_pe_rec => l_pe_rec,
2287 p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
2288 p_quota_name => l_p_plan_element_rec.NAME,
2289 p_loading_status => x_loading_status,
2290 x_loading_status => l_loading_status,
2291 p_is_duplicate => p_is_duplicate
2292 );
2293 x_loading_status := l_loading_status;
2294
2295 -- Raise an Error if Fail Status
2296 IF (x_return_status <> fnd_api.g_ret_sts_success)
2297 THEN
2298 RAISE fnd_api.g_exc_error;
2299 ELSE
2300 x_loading_status := 'CN_INSERTED';
2301 END IF;
2302
2303 -- Record inserted successfully
2304
2305 -- Call the Rate_quotas Procedure to create rate quota Assigns
2306 insert_rate_quotas (p_api_version => p_api_version,
2307 p_init_msg_list => p_init_msg_list,
2308 p_commit => p_commit,
2309 p_validation_level => p_validation_level,
2310 x_return_status => x_return_status,
2311 x_msg_count => x_msg_count,
2312 x_msg_data => x_msg_data,
2313 p_pe_rec => l_pe_rec,
2314 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
2315 p_quota_name => l_p_plan_element_rec.NAME,
2316 p_loading_status => x_loading_status,
2317 x_loading_status => l_loading_status
2318 );
2319 x_loading_status := l_loading_status;
2320
2321 -- Raise an Error if the Status is Failedx
2322 IF (x_return_status <> fnd_api.g_ret_sts_success)
2323 THEN
2324 RAISE fnd_api.g_exc_error;
2325 ELSE
2326 x_loading_status := 'CN_INSERTED';
2327 END IF;
2328 -- Plan Quota exists then check for period quotas in passed then
2329 -- period quota customization.
2330 -- plan quota exists then check for the rt quota assigns if passed
2331 -- then insert the rt quota assigs into the table.
2332 ELSIF (x_loading_status = 'PLN_QUOTA_EXISTS') AND (l_p_period_quotas_rec_tbl.COUNT > 0 OR l_p_rt_quota_asgns_rec_tbl.COUNT > 0)
2333 THEN
2334 IF l_p_period_quotas_rec_tbl.COUNT > 0
2335 THEN
2336 x_loading_status := 'CN_INSERTED';
2337 -- Call the Period Quotas local procedure to create Period Quotas
2338 create_period_quotas (p_api_version => p_api_version,
2339 p_init_msg_list => p_init_msg_list,
2340 p_commit => p_commit,
2341 p_validation_level => p_validation_level,
2342 x_return_status => x_return_status,
2343 x_msg_count => x_msg_count,
2344 x_msg_data => x_msg_data,
2345 p_pe_rec => l_pe_rec,
2346 p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
2347 p_quota_name => l_p_plan_element_rec.NAME,
2348 p_loading_status => x_loading_status,
2349 x_loading_status => l_loading_status,
2350 p_is_duplicate => p_is_duplicate
2351 );
2352 x_loading_status := l_loading_status;
2353
2354 -- Raise an error if the Return status is not success
2355 IF (x_return_status = fnd_api.g_ret_sts_success)
2356 THEN
2357 x_loading_status := 'CN_INSERTED';
2358 ELSE
2359 RAISE fnd_api.g_exc_error;
2360 END IF;
2361 END IF;
2362
2363 -- Check for the Rate Quota Assigns
2364 IF (l_p_rt_quota_asgns_rec_tbl.COUNT > 0)
2365 THEN
2366 -- set the loading Status
2367 x_loading_status := 'CN_INSERTED';
2368 -- Call the Rate_quotas Procedure to create rate quota Assigns
2369 insert_rate_quotas (p_api_version => p_api_version,
2370 p_init_msg_list => p_init_msg_list,
2371 p_commit => p_commit,
2372 p_validation_level => p_validation_level,
2373 x_return_status => x_return_status,
2374 x_msg_count => x_msg_count,
2375 x_msg_data => x_msg_data,
2376 p_pe_rec => l_pe_rec,
2377 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
2378 p_quota_name => l_p_plan_element_rec.NAME,
2379 p_loading_status => x_loading_status,
2380 x_loading_status => l_loading_status
2381 );
2382 x_loading_status := l_loading_status;
2383
2384 -- Raise an Error, if the Return status is not success
2385 IF (x_return_status = fnd_api.g_ret_sts_success)
2386 THEN
2387 x_loading_status := 'CN_INSERTED';
2388 ELSE
2389 RAISE fnd_api.g_exc_error;
2390 END IF;
2391 END IF;
2392 -- Check if all the children is not passed then there is a duplicate
2393 -- Quotas
2394 ELSIF (x_loading_status = 'PLN_QUOTA_EXISTS')
2395 THEN
2396 -- Here the Quota exists but there is no child passed no revenue class,
2397 -- trx factors, accelarator, period quotas, rate quotas
2398 -- Raise an error saying duplicate record
2399 IF ( l_p_revenue_class_rec_tbl.COUNT = 0
2400 AND l_p_rev_uplift_rec_tbl.COUNT = 0
2401 AND l_p_trx_factor_rec_tbl.COUNT = 0
2402 AND l_p_period_quotas_rec_tbl.COUNT = 0
2403 AND l_p_rt_quota_asgns_rec_tbl.COUNT = 0
2404 )
2405 THEN
2406 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2407 THEN
2408 fnd_message.set_name ('CN', 'PLN_QUOTA_EXISTS');
2409 fnd_msg_pub.ADD;
2410 END IF;
2411
2412 GOTO end_api_body;
2413 ELSE
2414 -- If Children record passed, set the status as CN_INSERTED
2415 x_loading_status := 'CN_INSERTED';
2416 END IF;
2417 ELSE
2418 -- Un known loading status
2419 RAISE fnd_api.g_exc_error;
2420 END IF;
2421
2422 -- Issue the Commit and recreate the Save Point.
2423 IF fnd_api.to_boolean (p_commit)
2424 THEN
2425 COMMIT WORK;
2426 END IF;
2427
2428 -- Create new save point Revenue Class if it success all the PLAN_ELEMENT Validation
2429 -- and the status is CN_INSERTED and the table count for revenue class is > 0
2430 SAVEPOINT create_plan_element;
2431
2432 IF (x_loading_status = 'CN_INSERTED')
2433 THEN
2434 -- Check the Table count is > 0 then Call the Group Package with
2435 -- table record and the Quota Type.
2436 IF l_p_revenue_class_rec_tbl.COUNT > 0
2437 THEN
2438 -- call the group api to insert the quota rules and the trx factors.
2439 cn_quota_rules_grp.create_quota_rules (p_api_version => p_api_version,
2440 p_init_msg_list => 'T',
2441 p_commit => p_commit,
2442 p_validation_level => p_validation_level,
2443 x_return_status => x_return_status,
2444 x_msg_count => x_msg_count,
2445 x_msg_data => x_msg_data,
2446 p_quota_name => l_p_plan_element_rec.NAME,
2447 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
2448 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
2449 p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
2450 x_loading_status => x_loading_status
2451 );
2452
2453 -- standard check to insert status if the return status is not succes
2454 -- raise an error
2455 IF (x_return_status <> fnd_api.g_ret_sts_success)
2456 THEN
2457 RAISE fnd_api.g_exc_error;
2458 END IF;
2459 -- case 3:
2460 -- Plan Element Exists, Revenue Class record is not passed but
2461 -- cusomizing the trx factors.
2462 ELSIF (l_p_revenue_class_rec_tbl.COUNT = 0 AND l_p_trx_factor_rec_tbl.COUNT > 0)
2463 THEN
2464 -- Trx Factor data should be loaded from p_trx_factor_rec_tbl,
2465 -- Since we insert data with default value already, so need to
2466 -- Update with the new Factors
2467 -- Call the trx factors procedure
2468 trx_factors (x_return_status => x_return_status,
2469 x_msg_count => x_msg_count,
2470 x_msg_data => x_msg_data,
2471 p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
2472 p_quota_id => l_pe_rec.quota_id,
2473 p_quota_name => l_p_plan_element_rec.NAME,
2474 p_loading_status => x_loading_status,
2475 x_loading_status => l_loading_status
2476 );
2477 x_loading_status := l_loading_status;
2478
2479 -- Raise an Error if the return status not success
2480 IF (x_return_status <> fnd_api.g_ret_sts_success)
2481 THEN
2482 RAISE fnd_api.g_exc_error;
2483 END IF;
2484 END IF;
2485 END IF; -- end if x_loading_status = 'CN_INSERTED'
2486
2487 -- If Quota Exists, Quota Rule Exists or not then the quota Rule uplift
2488 -- Counter is > 0 then insert the Uplift Record.
2489 IF (x_return_status <> fnd_api.g_ret_sts_success)
2490 THEN
2491 RAISE fnd_api.g_exc_error;
2492 ELSIF l_p_rev_uplift_rec_tbl.COUNT > 0 AND x_loading_status = 'CN_INSERTED'
2493 THEN
2494 -- call the group API to create the quota rule uplifts
2495 FOR i IN l_p_rev_uplift_rec_tbl.FIRST .. l_p_rev_uplift_rec_tbl.LAST LOOP
2496 l_p_rev_uplift_rec_tbl1(i).org_id := l_p_rev_uplift_rec_tbl(i).org_id;
2497 l_p_rev_uplift_rec_tbl1(i).quota_rule_uplift_id := NULL;
2498 l_p_rev_uplift_rec_tbl1(i).quota_rule_id :=NULL;
2499 l_p_rev_uplift_rec_tbl1(i).start_date :=l_p_rev_uplift_rec_tbl(i).start_date;
2500 l_p_rev_uplift_rec_tbl1(i).end_date := l_p_rev_uplift_rec_tbl(i).end_date;
2501 l_p_rev_uplift_rec_tbl1(i).payment_factor :=l_p_rev_uplift_rec_tbl(i).rev_class_payment_uplift;
2502 l_p_rev_uplift_rec_tbl1(i).quota_factor :=l_p_rev_uplift_rec_tbl(i).rev_class_quota_uplift;
2503 l_p_rev_uplift_rec_tbl1(i).object_version_number := l_p_rev_uplift_rec_tbl(i).object_version_number;
2504 l_p_rev_uplift_rec_tbl1(i).rev_class_name := l_p_rev_uplift_rec_tbl(i).rev_class_name;
2505 l_p_rev_uplift_rec_tbl1(i).rev_class_name_old :=l_p_rev_uplift_rec_tbl(i).rev_class_name_old;
2506 l_p_rev_uplift_rec_tbl1(i).start_date_old :=l_p_rev_uplift_rec_tbl(i).start_date;
2507 l_p_rev_uplift_rec_tbl1(i).end_date_old := l_p_rev_uplift_rec_tbl(i).start_date_old;
2508 END LOOP;
2509 cn_quota_rule_uplifts_grp.create_quota_rule_uplift (p_api_version => p_api_version,
2510 p_init_msg_list => 'T',
2511 p_commit => p_commit,
2512 p_validation_level => p_validation_level,
2513 x_return_status => x_return_status,
2514 x_msg_count => x_msg_count,
2515 x_msg_data => x_msg_data,
2516 p_quota_name => l_p_plan_element_rec.NAME,
2517 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl1,--cn_quota_rule_uplift_pvt.quota_rule_uplift_tbl_type
2518 x_loading_status => x_loading_status
2519 );
2520
2521 -- Raise an Error if the Status is not success
2522 IF (x_return_status <> fnd_api.g_ret_sts_success)
2523 THEN
2524 RAISE fnd_api.g_exc_error;
2525 ELSIF (x_loading_status <> 'CN_INSERTED')
2526 THEN
2527 RAISE fnd_api.g_exc_error;
2528 END IF;
2529 END IF;
2530
2531 /* Post processing */
2532 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'CREATE_PLAN_ELEMENT', 'A', 'V')
2533 THEN
2534 cn_plan_element_vuhk.create_plan_element_post (p_api_version => p_api_version,
2535 p_init_msg_list => p_init_msg_list,
2536 p_commit => fnd_api.g_false,
2537 p_validation_level => p_validation_level,
2538 x_return_status => x_return_status,
2539 x_msg_count => x_msg_count,
2540 x_msg_data => x_msg_data,
2541 p_plan_element_rec => l_p_plan_element_rec,
2542 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
2543 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
2544 p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
2545 p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
2546 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
2547 x_loading_status => x_loading_status
2548 );
2549
2550 IF (x_return_status = fnd_api.g_ret_sts_error)
2551 THEN
2552 RAISE fnd_api.g_exc_error;
2553 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2554 THEN
2555 RAISE fnd_api.g_exc_unexpected_error;
2556 END IF;
2557 END IF;
2558
2559 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'CREATE_PLAN_ELEMENT', 'A', 'C')
2560 THEN
2561 cn_plan_element_cuhk.create_plan_element_post (p_api_version => p_api_version,
2562 p_init_msg_list => p_init_msg_list,
2563 p_commit => fnd_api.g_false,
2564 p_validation_level => p_validation_level,
2565 x_return_status => x_return_status,
2566 x_msg_count => x_msg_count,
2567 x_msg_data => x_msg_data,
2568 p_plan_element_rec => l_p_plan_element_rec,
2569 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
2570 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
2571 p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
2572 p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
2573 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
2574 x_loading_status => x_loading_status
2575 );
2576
2577 IF (x_return_status = fnd_api.g_ret_sts_error)
2578 THEN
2579 RAISE fnd_api.g_exc_error;
2580 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2581 THEN
2582 RAISE fnd_api.g_exc_unexpected_error;
2583 END IF;
2584 END IF;
2585
2586 /* Following code is for message generation */
2587 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'CREATE_PLAN_ELEMENT', 'M', 'M')
2588 THEN
2589 IF (cn_plan_element_cuhk.ok_to_generate_msg (p_plan_element_rec => l_p_plan_element_rec,
2590 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
2591 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
2592 p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
2593 p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
2594 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl
2595 )
2596 )
2597 THEN
2598 -- XMLGEN.clearBindValues;
2599 -- XMLGEN.setBindValue( 'QUOTA_NAME', l_p_plan_element_rec.name);
2600 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
2601 jtf_usr_hks.load_bind_data (l_bind_data_id, 'QUOTA_NAME', l_p_plan_element_rec.NAME, 'S', 'T');
2602 jtf_usr_hks.generate_message (p_prod_code => 'CN',
2603 p_bus_obj_code => 'PL',
2604 p_bus_obj_name => 'PLAN_ELEMENT',
2605 p_action_code => 'I', /* I - Insert */
2606 p_bind_data_id => l_bind_data_id,
2607 p_oai_param => NULL,
2608 p_oai_array => l_oai_array,
2609 x_return_code => x_return_status
2610 );
2611
2612 IF (x_return_status = fnd_api.g_ret_sts_error)
2613 THEN
2614 RAISE fnd_api.g_exc_error;
2615 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2616 THEN
2617 RAISE fnd_api.g_exc_unexpected_error;
2618 END IF;
2619 END IF;
2620 END IF;
2621
2622 x_return_status := fnd_api.g_ret_sts_success;
2623
2624 -- End of API body
2625 <<end_api_body>>
2626 NULL;
2627
2628 -- Standard check of p_commit.
2629 IF fnd_api.to_boolean (p_commit)
2630 THEN
2631 COMMIT WORK;
2632 END IF;
2633
2634 --
2635 -- Standard call to get message count and if count is 1, get message info.
2636 --
2637 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2638 EXCEPTION
2639 WHEN fnd_api.g_exc_error
2640 THEN
2641 ROLLBACK TO create_plan_element;
2642 x_return_status := fnd_api.g_ret_sts_error;
2643 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2644 WHEN fnd_api.g_exc_unexpected_error
2645 THEN
2646 ROLLBACK TO create_plan_element;
2647 x_loading_status := 'UNEXPECTED_ERR';
2648 x_return_status := fnd_api.g_ret_sts_unexp_error;
2649 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2650 WHEN COLLECTION_IS_NULL
2651 THEN
2652 ROLLBACK TO create_plan_element;
2653 x_loading_status := 'COLLECTION_IS_NULL';
2654 x_return_status := fnd_api.g_ret_sts_unexp_error;
2655
2656 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2657 THEN
2658 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2659 END IF;
2660
2661 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2662 WHEN SUBSCRIPT_BEYOND_COUNT
2663 THEN
2664 ROLLBACK TO create_plan_element;
2665 x_loading_status := 'SUBSCRIPT_BEYOND_COUNT';
2666 x_return_status := fnd_api.g_ret_sts_unexp_error;
2667
2668 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2669 THEN
2670 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2671 END IF;
2672
2673 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2674 WHEN SUBSCRIPT_OUTSIDE_LIMIT
2675 THEN
2676 ROLLBACK TO create_plan_element;
2677 x_loading_status := 'SUBSCRIPT_OUTSIDE_LIMIT';
2678 x_return_status := fnd_api.g_ret_sts_unexp_error;
2679
2680 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2681 THEN
2682 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2683 END IF;
2684
2685 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2686 WHEN OTHERS
2687 THEN
2688 ROLLBACK TO create_plan_element;
2689 x_loading_status := 'UNEXPECTED_ERR';
2690 x_return_status := fnd_api.g_ret_sts_unexp_error;
2691
2692 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2693 THEN
2694 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2695 END IF;
2696
2697 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2698 END create_plan_element;
2699
2700 --***********************Very Important Please Read**************************
2701 -- 1. You Must the pass the full new record for update and the Unique key
2702 -- on the _old column for indentifying the exact record.
2703 -- Here is an Simple Example to Update the QUotas
2704 -- You want to update the start date only on the Plan Element , but still
2705 -- you need to pass all the new values on your input parameter. I mean
2706 -- Plan Element Name, start_date, end date and other columns but you are only
2707 -- passing the new value on the start date others are still carring the old
2708 -- values, Because we need to validate the record again for certain business
2709 -- rule. with your old Plan Element Name in p_quota_name_old
2710 -- Detail Example
2711 -- Old Record in the Database
2712 -- Plan Element Name = 'Advanced Tec'
2713 -- Start Date = '01-JAN-99'
2714 -- End Date = '31-DEC-99'
2715
2716 -- Now your Input parameter will be as follows for just changing the Start date
2717 -- Assume you New Start Date will be 01-MAR-99
2718
2719 -- P_plan_element_rec.name := 'Advanced Tec';
2720 -- p_plan_element_rec.start_date := '01-MAR-99'
2721 -- p_plan_element_rec.end_date := '31-DEC-99'
2722 -- for other colums pass the old values
2723 -- p_quota_name_old := 'Advanced Tec';
2724
2725 -- For UPDATING THE CHILD RECORDS
2726
2727 -- 2. If you want to just update the Child records, Here also same as above
2728 -- but you will be passing the old value as a part of your pl/sql table
2729 -- still remenber you need to pass the P_quota_name_old to update the
2730 -- child records. This program is always quota driven
2731
2732 -- Example for Updating the Quota Rules
2733
2734 -- You Want to Modify you rules Target
2735 -- You Input Paramter is as follows
2736
2737 -- p_quota_name_old is Mandatory
2738 -- p_rev_class_rec_tbl.rev_class_name := 'All Hardware';
2739 -- p_rev_class_rec_tbl.rev_class_target : = New_value
2740 -- p_rev_class_rec_tbl.others_columns := Old values
2741 -- p_rev_class_rec_tbl.rev_class_name_old := 'All Hardware';
2742
2743 --***************************************************************************
2744 -- -------------------------------------------------------------------------+
2745 -- | Procedure: Update_Plan_Element
2746 -- | Description: This program will try to update the Plan Element.
2747 -- | Note: ** Important **
2748 -- | Update Plan Element with handled in different than the way you expect.
2749 -- -------------------------------------------------------------------------+
2750 PROCEDURE update_plan_element (
2751 p_api_version IN NUMBER := 0,
2752 p_init_msg_list IN VARCHAR2 := cn_api.g_false,
2753 p_commit IN VARCHAR2 := cn_api.g_false,
2754 p_validation_level IN NUMBER := cn_api.g_valid_level_full,
2755 x_return_status OUT NOCOPY VARCHAR2,
2756 x_msg_count OUT NOCOPY NUMBER,
2757 x_msg_data OUT NOCOPY VARCHAR2,
2758 p_new_plan_element_rec IN plan_element_rec_type := g_miss_plan_element_rec,
2759 p_quota_name_old IN VARCHAR2,
2760 p_revenue_class_rec_tbl IN revenue_class_rec_tbl_type := g_miss_revenue_class_rec_tbl,
2761 p_rev_uplift_rec_tbl IN rev_uplift_rec_tbl_type := g_miss_rev_uplift_rec_tbl,
2762 p_trx_factor_rec_tbl IN trx_factor_rec_tbl_type := g_miss_trx_factor_rec_tbl,
2763 p_period_quotas_rec_tbl IN period_quotas_rec_tbl_type := g_miss_period_quotas_rec_tbl,
2764 p_rt_quota_asgns_rec_tbl IN rt_quota_asgns_rec_tbl_type := g_miss_rt_quota_asgns_rec_tbl,
2765 x_loading_status OUT NOCOPY VARCHAR2
2766 )
2767 IS
2768 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Plan_Element';
2769 l_api_version CONSTANT NUMBER := 1.0;
2770 l_pe_rec cn_chk_plan_element_pkg.pe_rec_type;
2771 l_pe_rec_old cn_chk_plan_element_pkg.pe_rec_type;
2772 l_trx_factor_rec_tbl trx_factor_rec_tbl_type;
2773 l_quota_rule_id NUMBER;
2774 l_quota_id NUMBER;
2775 l_rev_class_id NUMBER;
2776 l_tmp NUMBER;
2777 l_p_new_plan_element_rec plan_element_rec_type;
2778 l_p_quota_name_old VARCHAR2 (80);
2779 l_p_revenue_class_rec_tbl revenue_class_rec_tbl_type;
2780 l_p_rev_uplift_rec_tbl rev_uplift_rec_tbl_type;
2781
2782 l_p_rev_uplift_rec_tbl1 cn_quota_rule_uplift_pvt.quota_rule_uplift_tbl_type;
2783
2784 l_p_trx_factor_rec_tbl trx_factor_rec_tbl_type;
2785 l_p_period_quotas_rec_tbl period_quotas_rec_tbl_type;
2786 l_p_rt_quota_asgns_rec_tbl rt_quota_asgns_rec_tbl_type;
2787 l_oai_array jtf_usr_hks.oai_data_array_type;
2788 l_bind_data_id NUMBER;
2789 g_last_update_date DATE := SYSDATE;
2790 g_last_updated_by NUMBER := fnd_global.user_id;
2791 g_creation_date DATE := SYSDATE;
2792 g_created_by NUMBER := fnd_global.user_id;
2793 g_last_update_login NUMBER := fnd_global.login_id;
2794 g_rowid VARCHAR2 (30);
2795 g_program_type VARCHAR2 (30);
2796 l_loading_status VARCHAR (80);
2797 l_org_id NUMBER;
2798 l_status VARCHAR2(1);
2799 p_payment_group_code l_p_new_plan_element_rec.payment_group_code%type;
2800
2801 CURSOR c_srp_period_quota_csr (
2802 pe_quota_id cn_quotas.quota_id%TYPE
2803 )
2804 IS
2805 SELECT srp_period_quota_id,org_id
2806 FROM cn_srp_period_quotas
2807 WHERE quota_id = pe_quota_id;
2808
2809 l_number_dim_old NUMBER;
2810 l_number_dim_new NUMBER;
2811 l_number_dim NUMBER;
2812
2813 CURSOR get_number_dim (
2814 l_quota_id NUMBER
2815 )
2816 IS
2817 SELECT ccf.number_dim
2818 FROM cn_quotas_v cq,
2819 cn_calc_formulas ccf
2820 WHERE cq.quota_id = l_quota_id AND cq.calc_formula_id = ccf.calc_formula_id;
2821 BEGIN
2822 -- Standard Start of API savepoint
2823 SAVEPOINT update_plan_element;
2824
2825 -- Standard call to check for call compatibility.
2826 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
2827 THEN
2828 RAISE fnd_api.g_exc_unexpected_error;
2829 END IF;
2830
2831 -- Initialize message list if p_init_msg_list is set to TRUE.
2832 IF fnd_api.to_boolean (p_init_msg_list)
2833 THEN
2834 fnd_msg_pub.initialize;
2835 END IF;
2836
2837 -- Initialize API return status to success
2838 x_return_status := fnd_api.g_ret_sts_success;
2839 x_loading_status := 'CN_UPDATED';
2840
2841 -- START OF MOAC ORG_ID VALIDATION
2842 l_org_id := p_new_plan_element_rec.org_id;
2843 mo_global.validate_orgid_pub_api(org_id => l_org_id,
2844 status => l_status);
2845
2846 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2847 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2848 'cn.plsql.cn_plan_element_pub.update_plan_element.org_validate',
2849 'Validated org_id = ' || l_org_id || ' status = '||l_status);
2850 end if;
2851 -- END OF MOAC ORG_ID VALIDATION
2852
2853 -- API body
2854 l_p_new_plan_element_rec := p_new_plan_element_rec;
2855 l_p_quota_name_old := p_quota_name_old;
2856 l_p_revenue_class_rec_tbl := p_revenue_class_rec_tbl;
2857 l_p_rev_uplift_rec_tbl := p_rev_uplift_rec_tbl;
2858 l_p_trx_factor_rec_tbl := p_trx_factor_rec_tbl;
2859 l_p_period_quotas_rec_tbl := p_period_quotas_rec_tbl;
2860 l_p_rt_quota_asgns_rec_tbl := p_rt_quota_asgns_rec_tbl;
2861
2862 -- Validate Payment Group code
2863
2864 validate_payment_group_code(x_return_status => x_return_status,
2865 p_payment_group_code => l_p_new_plan_element_rec.payment_group_code);
2866
2867 IF (x_return_status <> fnd_api.g_ret_sts_success)
2868 THEN
2869 RAISE fnd_api.g_exc_error;
2870 END IF;
2871
2872 /* pre processing call */
2873 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'UPDATE_PLAN_ELEMENT', 'B', 'C')
2874 THEN
2875 cn_plan_element_cuhk.update_plan_element_pre (p_api_version => p_api_version,
2876 p_init_msg_list => p_init_msg_list,
2877 p_commit => fnd_api.g_false,
2878 p_validation_level => p_validation_level,
2879 x_return_status => x_return_status,
2880 x_msg_count => x_msg_count,
2881 x_msg_data => x_msg_data,
2882 p_new_plan_element_rec => l_p_new_plan_element_rec,
2883 p_quota_name_old => l_p_quota_name_old,
2884 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
2885 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
2886 p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
2887 p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
2888 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
2889 x_loading_status => x_loading_status
2890 );
2891
2892 IF (x_return_status = fnd_api.g_ret_sts_error)
2893 THEN
2894 RAISE fnd_api.g_exc_error;
2895 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2896 THEN
2897 RAISE fnd_api.g_exc_unexpected_error;
2898 END IF;
2899 END IF;
2900
2901 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'UPDATE_PLAN_ELEMENT', 'B', 'V')
2902 THEN
2903 cn_plan_element_vuhk.update_plan_element_pre (p_api_version => p_api_version,
2904 p_init_msg_list => p_init_msg_list,
2905 p_commit => fnd_api.g_false,
2906 p_validation_level => p_validation_level,
2907 x_return_status => x_return_status,
2908 x_msg_count => x_msg_count,
2909 x_msg_data => x_msg_data,
2910 p_new_plan_element_rec => l_p_new_plan_element_rec,
2911 p_quota_name_old => l_p_quota_name_old,
2912 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
2913 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
2914 p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
2915 p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
2916 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
2917 x_loading_status => x_loading_status
2918 );
2919
2920 IF (x_return_status = fnd_api.g_ret_sts_error)
2921 THEN
2922 RAISE fnd_api.g_exc_error;
2923 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2924 THEN
2925 RAISE fnd_api.g_exc_unexpected_error;
2926 END IF;
2927 END IF;
2928
2929 -- Store the User Input Value into The Local Variable.
2930 l_pe_rec :=
2931 convert_pe_user_input (x_return_status => x_return_status,
2932 p_plan_element_rec => l_p_new_plan_element_rec,
2933 p_loading_status => x_loading_status,
2934 x_loading_status => l_loading_status
2935 );
2936 x_loading_status := l_loading_status;
2937 x_loading_status := 'CN_UPDATED';
2938
2939 -- Validate the Plan Element to Update
2940 -- Don't Validate the Plan Element if the i all the new value is Null
2941 -- ie the only way to by pass the validaion and insert the child record.
2942
2943 -- Case 1:
2944 -- Update Plan Element Only
2945 -- Pass the Old Plan Element Name and New Plan Element Record with the
2946 -- full record even if you are not updating that column
2947 --
2948 IF ( l_p_new_plan_element_rec.NAME IS NULL
2949 AND l_p_new_plan_element_rec.description IS NULL
2950 AND l_p_new_plan_element_rec.element_type IS NULL
2951 AND l_p_new_plan_element_rec.incentive_type IS NULL
2952 AND l_p_new_plan_element_rec.credit_type IS NULL
2953 AND l_p_new_plan_element_rec.calc_formula_name IS NULL
2954 AND l_p_new_plan_element_rec.package_name IS NULL
2955 AND l_p_new_plan_element_rec.start_date IS NULL
2956 AND l_p_new_plan_element_rec.end_date IS NULL
2957 AND l_p_new_plan_element_rec.interval_name IS NULL
2958 AND l_p_quota_name_old IS NOT NULL
2959 AND ( l_p_revenue_class_rec_tbl.COUNT > 0
2960 OR l_p_rev_uplift_rec_tbl.COUNT > 0
2961 OR l_p_trx_factor_rec_tbl.COUNT > 0
2962 OR l_p_period_quotas_rec_tbl.COUNT > 0
2963 OR l_p_rt_quota_asgns_rec_tbl.COUNT > 0
2964 )
2965 )
2966 THEN
2967 x_loading_status := 'CN_CHILD';
2968 -- Check quota exists
2969 check_quota_exists (x_return_status => x_return_status,
2970 x_msg_count => x_msg_count,
2971 x_msg_data => x_msg_data,
2972 p_quota_name_old => l_p_quota_name_old,
2973 x_quota_id => l_pe_rec.quota_id,
2974 p_loading_status => x_loading_status,
2975 x_loading_status => l_loading_status
2976 );
2977 x_loading_status := l_loading_status;
2978 ELSE
2979 -- Check Valid Update x
2980 check_valid_update (x_return_status => x_return_status,
2981 x_msg_count => x_msg_count,
2982 x_msg_data => x_msg_data,
2983 p_quota_name_old => l_p_quota_name_old,
2984 p_new_pe_rec => l_pe_rec,
2985 x_old_pe_rec => l_pe_rec_old,
2986 p_loading_status => x_loading_status,
2987 x_loading_status => l_loading_status
2988 );
2989 x_loading_status := l_loading_status;
2990 END IF;
2991
2992 -- Raise an Error
2993 IF (x_return_status <> fnd_api.g_ret_sts_success)
2994 THEN
2995 RAISE fnd_api.g_exc_error;
2996 ELSIF (x_loading_status = 'CN_UPDATED')
2997 THEN
2998 l_pe_rec.quota_id := l_pe_rec_old.quota_id;
2999 -- Table Handler
3000 cn_quotas_pkg.begin_record (x_operation => 'UPDATE',
3001 x_org_id => l_pe_rec.org_id,
3002 x_object_version_number => l_pe_rec.object_version_number,
3003 x_indirect_credit => l_pe_rec.indirect_credit,
3004 x_rowid => g_rowid,
3005 x_quota_id => l_pe_rec_old.quota_id,
3006 x_name => l_pe_rec.NAME,
3007 x_target => l_pe_rec.target,
3008 x_quota_type_code => l_pe_rec.quota_type_code,
3009 x_usage_code => NULL,
3010 x_payment_amount => l_pe_rec.payment_amount,
3011 x_description => l_pe_rec.description,
3012 x_start_date => l_pe_rec.start_date,
3013 x_end_date => l_pe_rec.end_date,
3014 x_quota_status => l_pe_rec.quota_status,
3015 x_calc_formula_id => l_pe_rec.calc_formula_id,
3016 x_incentive_type_code => l_pe_rec.incentive_type_code,
3017 x_credit_type_id => l_pe_rec.credit_type_id,
3018 x_rt_sched_custom_flag => l_pe_rec.rt_sched_custom_flag,
3019 x_package_name => l_pe_rec.package_name,
3020 x_performance_goal => l_pe_rec.performance_goal,
3021 x_interval_type_id => l_pe_rec.interval_type_id,
3022 x_payee_assign_flag => l_pe_rec.payee_assign_flag,
3023 x_vesting_flag => l_pe_rec.vesting_flag,
3024 x_expense_account_id => l_p_new_plan_element_rec.expense_account_id,
3025 x_liability_account_id => l_p_new_plan_element_rec.liability_account_id,
3026 x_quota_group_code => l_p_new_plan_element_rec.quota_group_code
3027 --clku PAYMENT ENHANCEMENT,
3028 ,
3029 x_payment_group_code => l_p_new_plan_element_rec.payment_group_code,
3030 x_quota_unspecified => NULL,
3031 x_last_update_date => g_last_update_date,
3032 x_last_updated_by => g_last_updated_by,
3033 x_creation_date => g_creation_date,
3034 x_created_by => g_created_by,
3035 x_last_update_login => g_last_update_login,
3036 x_program_type => g_program_type,
3037 x_period_type_code => NULL,
3038 x_start_num => NULL,
3039 x_end_num => NULL,
3040 x_addup_from_rev_class_flag => l_pe_rec.addup_from_rev_class_flag
3041 --clku, bug 2854576
3042 ,
3043 x_attribute_category => l_p_new_plan_element_rec.attribute_category,
3044 x_attribute1 => l_p_new_plan_element_rec.attribute1,
3045 x_attribute2 => l_p_new_plan_element_rec.attribute2,
3046 x_attribute3 => l_p_new_plan_element_rec.attribute3,
3047 x_attribute4 => l_p_new_plan_element_rec.attribute4,
3048 x_attribute5 => l_p_new_plan_element_rec.attribute5,
3049 x_attribute6 => l_p_new_plan_element_rec.attribute6,
3050 x_attribute7 => l_p_new_plan_element_rec.attribute7,
3051 x_attribute8 => l_p_new_plan_element_rec.attribute8,
3052 x_attribute9 => l_p_new_plan_element_rec.attribute9,
3053 x_attribute10 => l_p_new_plan_element_rec.attribute10,
3054 x_attribute11 => l_p_new_plan_element_rec.attribute11,
3055 x_attribute12 => l_p_new_plan_element_rec.attribute12,
3056 x_attribute13 => l_p_new_plan_element_rec.attribute13,
3057 x_attribute14 => l_p_new_plan_element_rec.attribute14,
3058 x_attribute15 => l_p_new_plan_element_rec.attribute15,
3059 x_salesrep_end_flag => l_p_new_plan_element_rec.sreps_enddated_flag
3060 );
3061
3062 -- update expressions using this plan element
3063 IF (l_p_quota_name_old <> l_pe_rec.NAME)
3064 THEN
3065 chg_exprs (l_pe_rec_old.quota_id, l_p_quota_name_old, l_pe_rec.NAME);
3066 END IF;
3067
3068 l_pe_rec.quota_id := l_pe_rec_old.quota_id;
3069 -- IF formula is changed and the ITD flag is Y then
3070 -- Call the Period Quotas to Insert or customise the
3071 -- New Period Quotas
3072 update_period_quotas (p_api_version => p_api_version,
3073 p_init_msg_list => p_init_msg_list,
3074 p_commit => p_commit,
3075 p_validation_level => p_validation_level,
3076 x_return_status => x_return_status,
3077 x_msg_count => x_msg_count,
3078 x_msg_data => x_msg_data,
3079 p_pe_rec => l_pe_rec,
3080 p_pe_rec_old => l_pe_rec_old,
3081 p_period_quotas_rec_tbl =>l_p_period_quotas_rec_tbl,
3082 p_quota_name => l_p_quota_name_old,
3083 p_loading_status => x_loading_status,
3084 x_loading_status => l_loading_status
3085 );
3086 x_loading_status := l_loading_status;
3087
3088 IF (x_return_status <> fnd_api.g_ret_sts_success)
3089 THEN
3090 RAISE fnd_api.g_exc_error;
3091 END IF;
3092
3093 -- check if we need to update the cn_srp_period_quotas ext table. If yes, update the table
3094
3095 -- if the new assignement is external package, we do not do anything
3096 IF l_pe_rec.quota_type_code <> 'EXTERNAL'
3097 THEN
3098 -- if the old assignement is external package, we wipe out the ext table and re-insert the record
3099 IF l_pe_rec_old.quota_type_code = 'EXTERNAL'
3100 THEN
3101 OPEN get_number_dim (l_pe_rec_old.quota_id);
3102
3103 FETCH get_number_dim
3104 INTO l_number_dim;
3105
3106 CLOSE get_number_dim;
3107
3108 IF l_number_dim > 1
3109 THEN
3110 FOR l_srp_period_quota_id IN c_srp_period_quota_csr (l_pe_rec_old.quota_id)
3111 LOOP
3112 cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('DELETE', l_srp_period_quota_id.srp_period_quota_id,l_srp_period_quota_id.org_id);
3113 END LOOP;
3114
3115 FOR l_srp_period_quota_id IN c_srp_period_quota_csr (l_pe_rec_old.quota_id)
3116 LOOP
3117 cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('INSERT', l_srp_period_quota_id.srp_period_quota_id,l_srp_period_quota_id.org_id, l_number_dim);
3118 END LOOP;
3119 END IF;
3120 ELSIF l_pe_rec.calc_formula_id <> l_pe_rec_old.calc_formula_id
3121 THEN
3122 SELECT number_dim
3123 INTO l_number_dim_old
3124 FROM cn_calc_formulas
3125 WHERE calc_formula_id = l_pe_rec_old.calc_formula_id;
3126
3127 SELECT number_dim
3128 INTO l_number_dim_new
3129 FROM cn_calc_formulas
3130 WHERE calc_formula_id = l_pe_rec.calc_formula_id;
3131
3132 IF l_number_dim_new <> l_number_dim_old
3133 THEN
3134 IF l_number_dim_new < l_number_dim_old
3135 THEN
3136 FOR l_srp_period_quota_id IN c_srp_period_quota_csr (l_pe_rec_old.quota_id)
3137 LOOP
3138 cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('DELETE', l_srp_period_quota_id.srp_period_quota_id,l_srp_period_quota_id.org_id);
3139 END LOOP;
3140 END IF;
3141
3142 -- if reduce # dims to 1, then no longer need _ext records
3143 IF l_number_dim_new > 1
3144 THEN
3145 FOR l_srp_period_quota_id IN c_srp_period_quota_csr (l_pe_rec_old.quota_id)
3146 LOOP
3147 cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('INSERT',
3148 l_srp_period_quota_id.srp_period_quota_id,
3149 l_number_dim_new
3150 );
3151 END LOOP;
3152 END IF;
3153 END IF;
3154 END IF;
3155 END IF;
3156
3157 update_rate_quotas (p_api_version => p_api_version,
3158 p_init_msg_list => p_init_msg_list,
3159 p_commit => p_commit,
3160 p_validation_level => p_validation_level,
3161 x_return_status => x_return_status,
3162 x_msg_count => x_msg_count,
3163 x_msg_data => x_msg_data,
3164 p_pe_rec => l_pe_rec,
3165 p_pe_rec_old => l_pe_rec_old,
3166 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
3167 p_quota_name => l_pe_rec.NAME,
3168 p_loading_status => x_loading_status,
3169 x_loading_status => l_loading_status
3170 );
3171 x_loading_status := l_loading_status;
3172
3173 IF (x_return_status <> fnd_api.g_ret_sts_success)
3174 THEN
3175 RAISE fnd_api.g_exc_error;
3176 END IF;
3177
3178 -- IF the aboce return Status IS success and one of COUNT is
3179 -- Greater than 0 Then set the status as CN_CHILD and
3180 -- Call the Respective child Procedures
3181 IF (x_return_status = fnd_api.g_ret_sts_success)
3182 AND (l_p_revenue_class_rec_tbl.COUNT > 0 OR l_p_trx_factor_rec_tbl.COUNT > 0 OR l_p_rev_uplift_rec_tbl.COUNT > 0)
3183 THEN
3184 x_loading_status := 'CN_CHILD';
3185 END IF;
3186 END IF;
3187
3188 -- Issue the Commit Before start the Child Process
3189 IF fnd_api.to_boolean (p_commit)
3190 THEN
3191 COMMIT WORK;
3192 END IF;
3193
3194 -- Create new save point
3195 SAVEPOINT update_plan_element;
3196
3197 -- Check for the Child Update
3198 IF x_loading_status = 'CN_CHILD'
3199 THEN
3200 IF l_p_revenue_class_rec_tbl.COUNT > 0
3201 THEN
3202 -- Call the Quota Rules Update Procedure if the Count IS > 0
3203 cn_quota_rules_grp.update_quota_rules (p_api_version => p_api_version,
3204 p_init_msg_list => 'T',
3205 p_commit => p_commit,
3206 p_validation_level => p_validation_level,
3207 x_return_status => x_return_status,
3208 x_msg_count => x_msg_count,
3209 x_msg_data => x_msg_data,
3210 p_quota_name => NVL (l_pe_rec.NAME, l_p_quota_name_old),
3211 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
3212 p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
3213 x_loading_status => x_loading_status
3214 );
3215
3216 -- if the Status is not success or the Loading Status is <> CN_UPDATED
3217 -- then Raise an Error
3218 IF (x_return_status <> fnd_api.g_ret_sts_success)
3219 THEN
3220 RAISE fnd_api.g_exc_error;
3221 ELSIF (x_loading_status <> 'CN_UPDATED')
3222 THEN
3223 RAISE fnd_api.g_exc_error;
3224 END IF;
3225 ELSIF (l_p_revenue_class_rec_tbl.COUNT = 0 AND l_p_trx_factor_rec_tbl.COUNT > 0)
3226 THEN
3227 -- Trx Factor data should be loaded from p_trx_factor_rec_tbl,
3228 -- Since we insert data with default value already, so need to
3229 -- Update with the new Factors
3230 FOR i IN l_p_trx_factor_rec_tbl.FIRST .. l_p_trx_factor_rec_tbl.LAST
3231 LOOP
3232 l_tmp := 0;
3233
3234 IF l_trx_factor_rec_tbl.COUNT > 0
3235 THEN
3236 FOR j IN l_trx_factor_rec_tbl.FIRST .. l_trx_factor_rec_tbl.LAST
3237 LOOP
3238 IF (l_p_trx_factor_rec_tbl (i).rev_class_name = l_trx_factor_rec_tbl (j).rev_class_name)
3239 THEN
3240 l_tmp := 1;
3241 END IF;
3242 END LOOP;
3243 END IF;
3244
3245 IF l_tmp = 0
3246 THEN
3247 l_trx_factor_rec_tbl (l_trx_factor_rec_tbl.COUNT + 1) := l_p_trx_factor_rec_tbl (i);
3248 END IF;
3249 END LOOP;
3250
3251 -- Process the Actual Trx factors Record
3252 FOR i IN l_trx_factor_rec_tbl.FIRST .. l_trx_factor_rec_tbl.LAST
3253 LOOP
3254 -- Get revenue Class ID
3255 l_rev_class_id := cn_api.get_rev_class_id (RTRIM (LTRIM (l_trx_factor_rec_tbl (i).rev_class_name)),l_trx_factor_rec_tbl (i).org_id);
3256 -- Get Quota Rule ID, you need it to update the Trx Factors
3257 l_quota_rule_id :=
3258 cn_chk_plan_element_pkg.get_quota_rule_id (p_quota_id => NVL (l_pe_rec.quota_id, l_pe_rec_old.quota_id),
3259 p_rev_class_id => l_rev_class_id
3260 );
3261
3262 -- Loop through each record and update the mached one only
3263 FOR j IN l_p_trx_factor_rec_tbl.FIRST .. l_p_trx_factor_rec_tbl.LAST
3264 LOOP
3265 -- If the Revenue class name of the Outer and the inner is same then
3266 -- Update the Trx factors
3267 IF (l_p_trx_factor_rec_tbl (j).rev_class_name = l_trx_factor_rec_tbl (i).rev_class_name)
3268 THEN
3269 -- Update the trx Factors
3270 UPDATE cn_trx_factors
3271 SET event_factor = l_p_trx_factor_rec_tbl (j).event_factor
3272 WHERE quota_rule_id = l_quota_rule_id
3273 AND quota_id = NVL (l_pe_rec.quota_id, l_pe_rec_old.quota_id)
3274 AND trx_type = l_p_trx_factor_rec_tbl (j).trx_type;
3275 END IF; -- trx Factor Exists
3276 END LOOP; -- Trx Loop
3277
3278 -- validate Rule :
3279 -- Check TRX_FACTORS
3280 -- 1. Key Factor's total = 100
3281 -- 2. Must have Trx_Factors
3282 cn_chk_plan_element_pkg.chk_trx_factor (x_return_status => x_return_status,
3283 p_quota_rule_id => l_quota_rule_id,
3284 p_rev_class_name => l_trx_factor_rec_tbl (i).rev_class_name,
3285 p_loading_status => x_loading_status,
3286 x_loading_status => l_loading_status
3287 );
3288 x_loading_status := l_loading_status;
3289
3290 -- Raise an Error if the Status Is not success
3291 IF (x_return_status <> fnd_api.g_ret_sts_success) OR x_loading_status NOT IN ('CN_UPDATED', 'CN_INSERTED')
3292 THEN
3293 RAISE fnd_api.g_exc_error;
3294 END IF;
3295 END LOOP; -- Outer trx Loop
3296 END IF;
3297
3298 -- Check the Rev Uplift Count, if > 0 then Process the Records
3299 IF l_p_rev_uplift_rec_tbl.COUNT > 0
3300 THEN
3301 FOR i IN l_p_rev_uplift_rec_tbl.FIRST .. l_p_rev_uplift_rec_tbl.LAST LOOP
3302 l_p_rev_uplift_rec_tbl1(i).org_id := l_p_rev_uplift_rec_tbl(i).org_id;
3303 l_p_rev_uplift_rec_tbl1(i).quota_rule_uplift_id := NULL;
3304 l_p_rev_uplift_rec_tbl1(i).quota_rule_id :=NULL;
3305 l_p_rev_uplift_rec_tbl1(i).start_date :=l_p_rev_uplift_rec_tbl(i).start_date;
3306 l_p_rev_uplift_rec_tbl1(i).end_date := l_p_rev_uplift_rec_tbl(i).end_date;
3307 l_p_rev_uplift_rec_tbl1(i).payment_factor :=l_p_rev_uplift_rec_tbl(i).rev_class_payment_uplift;
3308 l_p_rev_uplift_rec_tbl1(i).quota_factor :=l_p_rev_uplift_rec_tbl(i).rev_class_quota_uplift;
3309 l_p_rev_uplift_rec_tbl1(i).object_version_number := l_p_rev_uplift_rec_tbl(i).object_version_number;
3310 l_p_rev_uplift_rec_tbl1(i).rev_class_name := l_p_rev_uplift_rec_tbl(i).rev_class_name;
3311 l_p_rev_uplift_rec_tbl1(i).rev_class_name_old :=l_p_rev_uplift_rec_tbl(i).rev_class_name_old;
3312 l_p_rev_uplift_rec_tbl1(i).start_date_old :=l_p_rev_uplift_rec_tbl(i).start_date;
3313 l_p_rev_uplift_rec_tbl1(i).end_date_old := l_p_rev_uplift_rec_tbl(i).start_date_old;
3314 END LOOP;
3315
3316 -- call the group API to create the quota rule uplifts
3317 cn_quota_rule_uplifts_grp.update_quota_rule_uplift (p_api_version => p_api_version,
3318 p_init_msg_list => 'T',
3319 p_commit => p_commit,
3320 p_validation_level => p_validation_level,
3321 x_return_status => x_return_status,
3322 x_msg_count => x_msg_count,
3323 x_msg_data => x_msg_data,
3324 p_quota_name => NVL (l_pe_rec.NAME, l_p_quota_name_old),
3325 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl1,
3326 x_loading_status => x_loading_status
3327 );
3328
3329 -- Raise an Error if the Status is not SUCCESS or NOT CN_UPDATED
3330 IF (x_return_status <> fnd_api.g_ret_sts_success)
3331 THEN
3332 RAISE fnd_api.g_exc_error;
3333 ELSIF (x_loading_status <> 'CN_UPDATED')
3334 THEN
3335 RAISE fnd_api.g_exc_error;
3336 END IF;
3337 END IF;
3338
3339 -- Check the Period Quotas counter Parameter if it is > 0 THEN
3340 -- Update the Period QUotas records by calling the
3341 -- Group API's
3342 IF l_p_period_quotas_rec_tbl.COUNT > 0
3343 THEN
3344 -- Call Period Quotas rec Procedure
3345 cn_period_quotas_grp.update_period_quotas (p_api_version => p_api_version,
3346 p_init_msg_list => 'T',
3347 p_commit => p_commit,
3348 p_validation_level => p_validation_level,
3349 x_return_status => x_return_status,
3350 x_msg_count => x_msg_count,
3351 x_msg_data => x_msg_data,
3352 p_quota_name => NVL (l_pe_rec.NAME, l_p_quota_name_old),
3353 p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
3354 x_loading_status => x_loading_status
3355 );
3356
3357 -- If the Return status is not success or not CN_UPDATED then
3358 -- Raise an Error
3359 IF (x_return_status <> fnd_api.g_ret_sts_success)
3360 THEN
3361 RAISE fnd_api.g_exc_error;
3362 ELSIF (x_loading_status <> 'CN_UPDATED')
3363 THEN
3364 RAISE fnd_api.g_exc_error;
3365 END IF;
3366 END IF;
3367
3368 -- Check the Rate QUota assigns table Parameter count if > 0
3369 -- Then Call the Update_rate_quota_assigns Private Package
3370 -- Procedure to Update the rate Quota assigns
3371 IF l_p_rt_quota_asgns_rec_tbl.COUNT > 0
3372 THEN
3373 -- Call Update the Rate Quota Assisns procedure
3374 cn_rt_quota_asgns_pvt.update_rt_quota_asgns (p_api_version => p_api_version,
3375 p_init_msg_list => 'T',
3376 p_commit => p_commit,
3377 p_validation_level => p_validation_level,
3378 x_return_status => x_return_status,
3379 x_msg_count => x_msg_count,
3380 x_msg_data => x_msg_data,
3381 p_quota_name => NVL (l_pe_rec.NAME, l_p_quota_name_old),
3382 p_org_id => l_pe_rec.org_id,
3383 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
3384 x_loading_status => x_loading_status,
3385 x_object_version_number => l_pe_rec.object_version_number
3386 );
3387
3388 -- Raise an Error if the return status is not success or
3389 -- return loading status is NOT CN_UPDATED
3390 IF (x_return_status <> fnd_api.g_ret_sts_success)
3391 THEN
3392 RAISE fnd_api.g_exc_error;
3393 ELSIF (x_loading_status <> 'CN_UPDATED')
3394 THEN
3395 RAISE fnd_api.g_exc_error;
3396 END IF;
3397 END IF;
3398 END IF; -- end if x_loading_status = 'CN_CHILD'
3399
3400 /* Post processing */
3401 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'UPDATE_PLAN_ELEMENT', 'A', 'V')
3402 THEN
3403 cn_plan_element_vuhk.update_plan_element_post (p_api_version => p_api_version,
3404 p_init_msg_list => p_init_msg_list,
3405 p_commit => fnd_api.g_false,
3406 p_validation_level => p_validation_level,
3407 x_return_status => x_return_status,
3408 x_msg_count => x_msg_count,
3409 x_msg_data => x_msg_data,
3410 p_new_plan_element_rec => l_p_new_plan_element_rec,
3411 p_quota_name_old => l_p_quota_name_old,
3412 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
3413 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
3414 p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
3415 p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
3416 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
3417 x_loading_status => x_loading_status
3418 );
3419
3420 IF (x_return_status = fnd_api.g_ret_sts_error)
3421 THEN
3422 RAISE fnd_api.g_exc_error;
3423 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
3424 THEN
3425 RAISE fnd_api.g_exc_unexpected_error;
3426 END IF;
3427 END IF;
3428
3429 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'UPDATE_PLAN_ELEMENT', 'A', 'C')
3430 THEN
3431 cn_plan_element_cuhk.update_plan_element_post (p_api_version => p_api_version,
3432 p_init_msg_list => p_init_msg_list,
3433 p_commit => fnd_api.g_false,
3434 p_validation_level => p_validation_level,
3435 x_return_status => x_return_status,
3436 x_msg_count => x_msg_count,
3437 x_msg_data => x_msg_data,
3438 p_new_plan_element_rec => l_p_new_plan_element_rec,
3439 p_quota_name_old => l_p_quota_name_old,
3440 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
3441 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
3442 p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
3443 p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
3444 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
3445 x_loading_status => x_loading_status
3446 );
3447
3448 IF (x_return_status = fnd_api.g_ret_sts_error)
3449 THEN
3450 RAISE fnd_api.g_exc_error;
3451 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
3452 THEN
3453 RAISE fnd_api.g_exc_unexpected_error;
3454 END IF;
3455 END IF;
3456
3457 /* Following code is for message generation */
3458 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'UPDATE_PLAN_ELEMENT', 'M', 'M')
3459 THEN
3460 IF (cn_plan_element_cuhk.ok_to_generate_msg (p_plan_element_rec => l_p_new_plan_element_rec,
3461 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
3462 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
3463 p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
3464 p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
3465 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
3466 p_plan_element_name => l_p_quota_name_old
3467 )
3468 )
3469 THEN
3470 -- XMLGEN.clearBindValues;
3471 -- XMLGEN.setBindValue( 'QUOTA_NAME', l_p_new_plan_element_rec.name);
3472 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
3473 jtf_usr_hks.load_bind_data (l_bind_data_id, 'QUOTA_NAME', l_p_new_plan_element_rec.NAME, 'S', 'T');
3474 jtf_usr_hks.generate_message (p_prod_code => 'CN',
3475 p_bus_obj_code => 'PL',
3476 p_bus_obj_name => 'PLAN_ELEMENT',
3477 p_action_code => 'U', /* U - Update */
3478 p_bind_data_id => l_bind_data_id,
3479 p_oai_param => NULL,
3480 p_oai_array => l_oai_array,
3481 x_return_code => x_return_status
3482 );
3483
3484 IF (x_return_status = fnd_api.g_ret_sts_error)
3485 THEN
3486 RAISE fnd_api.g_exc_error;
3487 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
3488 THEN
3489 RAISE fnd_api.g_exc_unexpected_error;
3490 END IF;
3491 END IF;
3492 END IF;
3493
3494 x_return_status := fnd_api.g_ret_sts_success;
3495
3496 -- Standard check of p_commit.
3497 IF fnd_api.to_boolean (p_commit)
3498 THEN
3499 COMMIT WORK;
3500 END IF;
3501
3502 -- Standard call to get message count and if count is 1, get message info.
3503 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3504 -- End of Update Plan Element
3505 EXCEPTION
3506 WHEN fnd_api.g_exc_error
3507 THEN
3508 ROLLBACK TO update_plan_element;
3509 x_return_status := fnd_api.g_ret_sts_error;
3510 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3511 WHEN fnd_api.g_exc_unexpected_error
3512 THEN
3513 ROLLBACK TO update_plan_element;
3514 x_loading_status := 'UNEXPECTED_ERR';
3515 x_return_status := fnd_api.g_ret_sts_unexp_error;
3516 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3517 WHEN COLLECTION_IS_NULL
3518 THEN
3519 ROLLBACK TO update_plan_element;
3520 x_loading_status := 'COLLECTION_IS_NULL';
3521 x_return_status := fnd_api.g_ret_sts_unexp_error;
3522
3523 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3524 THEN
3525 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3526 END IF;
3527
3528 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3529 WHEN SUBSCRIPT_BEYOND_COUNT
3530 THEN
3531 ROLLBACK TO update_plan_element;
3532 x_loading_status := 'SUBSCRIPT_BEYOND_COUNT';
3533 x_return_status := fnd_api.g_ret_sts_unexp_error;
3534
3535 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3536 THEN
3537 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3538 END IF;
3539
3540 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3541 WHEN SUBSCRIPT_OUTSIDE_LIMIT
3542 THEN
3543 ROLLBACK TO update_plan_element;
3544 x_loading_status := 'SUBSCRIPT_OUTSIDE_LIMIT';
3545 x_return_status := fnd_api.g_ret_sts_unexp_error;
3546
3547 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3548 THEN
3549 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3550 END IF;
3551
3552 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3553 WHEN OTHERS
3554 THEN
3555 ROLLBACK TO update_plan_element;
3556 x_loading_status := 'UNEXPECTED_ERR';
3557 x_return_status := fnd_api.g_ret_sts_unexp_error;
3558
3559 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3560 THEN
3561 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3562 END IF;
3563
3564 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3565 END update_plan_element;
3566
3567 -- End Update Plan Element
3568
3569 /* ****************** */
3570 /* MODIFIED - SBADAMI */
3571 /* ****************** */
3572
3573 -- Start of comments
3574 -- API name : delete_plan_element
3575 -- Type : Public
3576 -- Pre-reqs : None.
3577 -- Purpose : The following API performs the following
3578 -- 1. Deletes the Plan Element and it's associated records in
3579 -- CN_QUOTAS, CN_QUOTA_RULES, CN_RT_QUOTA_ASGNS
3580 -- 2.
3581 -- 3.
3582 -- Parameters :
3583 -- IN : p_api_version IN NUMBER API version
3584 -- p_init_msg_list IN VARCHAR2 Initialize message list (default F)
3585 -- p_commit IN VARCHAR2 Commit flag (default F).
3586 -- p_validation_level IN NUMBER Validation level (default 100).
3587 -- x_return_status IN VARCHAR2 Return Status
3588 -- x_msg_count IN NUMBER Number of messages returned
3589 -- x_msg_data IN VARCHAR2 Contents of message if x_msg_count = 1
3590 -- x_loading_status IN VARCHAR2 Loading Status
3591 -- p_quota_name IN VARCHAR2 Plan element details
3592 -- p_revenue_class_rec_tbl Revenue class details
3593 -- p_rev_uplift_rec_tbl Revenue class uplift factor details
3594 -- p_rt_quota_asgns_rec_tbl Rate quota assigns details
3595 -- Version : Initial version 1.0
3596 -- End of comments
3597
3598 -- -------------------------------------------------------------------------+
3599 -- | Procedure: Delete_Plan_Element
3600 -- | Description: This program will Delete the Whole Plan Element if
3601 -- | you are not passing any Child records. IF you want to delete the Plan
3602 -- | Element just pass the Plan Element, don't pass any child records.
3603 -- -------------------------------------------------------------------------+
3604 PROCEDURE process_input_records (
3605 p_api_version IN NUMBER := 0,
3606 p_init_msg_list IN VARCHAR2 := cn_api.g_false,
3607 p_commit IN VARCHAR2 := cn_api.g_false,
3608 p_validation_level IN NUMBER := cn_api.g_valid_level_full,
3609 p_quota_rec IN OUT NOCOPY plan_element_rec_type ,
3610 p_revenue_class_rec_tbl IN OUT NOCOPY revenue_class_rec_tbl_type ,
3611 p_rev_uplift_rec_tbl IN OUT NOCOPY rev_uplift_rec_tbl_type ,
3612 p_rt_quota_asgns_rec_tbl IN OUT NOCOPY rt_quota_asgns_rec_tbl_type ,
3613 x_return_status OUT NOCOPY VARCHAR2,
3614 x_msg_count OUT NOCOPY NUMBER,
3615 x_msg_data OUT NOCOPY VARCHAR2,
3616 x_loading_status OUT NOCOPY VARCHAR2
3617 )
3618 IS
3619 l_api_name CONSTANT VARCHAR2 (30) := 'process_input_records';
3620 l_api_version CONSTANT NUMBER := 1.0;
3621 l_p_quota_name cn_quotas.NAME%TYPE;
3622 l_loading_status VARCHAR2 (80);
3623 l_org_id cn_quotas.org_id%TYPE;
3624 l_quota_id cn_quotas.quota_id%TYPE;
3625 BEGIN
3626 -- Standard Start of API savepoint
3627 SAVEPOINT process_input_records;
3628 -- Initialize API return status to success
3629 x_return_status := fnd_api.g_ret_sts_success;
3630 l_p_quota_name := p_quota_rec.NAME;
3631 x_loading_status := 'CN_DELETED';
3632
3633 /* Resolve the quota_id and quota_name from */
3634 /* 1. If checks if quota_name passed is g_miss_char */
3635 IF ((cn_api.chk_miss_char_para (p_char_para => l_p_quota_name,
3636 p_para_name => cn_chk_plan_element_pkg.g_pe_name,
3637 p_loading_status => x_loading_status,
3638 x_loading_status => l_loading_status
3639 )
3640 ) = fnd_api.g_true
3641 )
3642 THEN
3643 RAISE fnd_api.g_exc_error;
3644 END IF;
3645
3646 /* 2. if it is null character */
3647 IF ((cn_api.chk_null_char_para (p_char_para => l_p_quota_name,
3648 p_obj_name => cn_chk_plan_element_pkg.g_pe_name,
3649 p_loading_status => x_loading_status,
3650 x_loading_status => l_loading_status
3651 )
3652 ) = fnd_api.g_true
3653 )
3654 THEN
3655 RAISE fnd_api.g_exc_error;
3656 END IF;
3657
3658 /* 3. Org Id Validations to begin */
3659 l_org_id := p_quota_rec.org_id;
3660
3661 IF l_org_id IS NULL
3662 THEN
3663 -- Need to get from MOAC Team some utility to default the OU
3664 NULL;
3665 END IF;
3666
3667 -- l_org_id is still null we need to raise error
3668 check_org_id (l_org_id);
3669 -- Set the Plan Element Record Type to have the org_id
3670 p_quota_rec.org_id := l_org_id;
3671 /* 4. Get the Quota ID */
3672 l_quota_id := cn_chk_plan_element_pkg.get_quota_id (LTRIM (RTRIM (l_p_quota_name)), l_org_id);
3673
3674 -- check the Quota id if the Quota ID is Null and the Quota name is Not null
3675 -- Raise an Error
3676 IF l_quota_id IS NULL AND l_p_quota_name IS NOT NULL
3677 THEN
3678 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3679 THEN
3680 fnd_message.set_name ('CN', 'CN_PLN_NOT_EXIST');
3681 fnd_message.set_token ('PE_NAME', l_p_quota_name);
3682 fnd_msg_pub.ADD;
3683 END IF;
3684
3685 x_loading_status := 'CN_PLN_NOT_EXIST';
3686 RAISE fnd_api.g_exc_error;
3687 END IF;
3688
3689 p_quota_rec.quota_id := l_quota_id;
3690
3691 /* 5. Set all the child records org_id to be of the masters */
3692 IF p_revenue_class_rec_tbl.COUNT > 0
3693 THEN
3694 FOR i IN p_revenue_class_rec_tbl.FIRST .. p_revenue_class_rec_tbl.LAST
3695 LOOP
3696 p_revenue_class_rec_tbl (i).org_id := l_org_id;
3697 END LOOP;
3698 END IF;
3699
3700 IF p_rev_uplift_rec_tbl.COUNT > 0
3701 THEN
3702 FOR i IN p_rev_uplift_rec_tbl.FIRST .. p_revenue_class_rec_tbl.LAST
3703 LOOP
3704 p_rev_uplift_rec_tbl (i).org_id := l_org_id;
3705 END LOOP;
3706 END IF;
3707
3708 IF p_rt_quota_asgns_rec_tbl.COUNT > 0
3709 THEN
3710 FOR i IN p_rt_quota_asgns_rec_tbl.FIRST .. p_revenue_class_rec_tbl.LAST
3711 LOOP
3712 p_rt_quota_asgns_rec_tbl (i).org_id := l_org_id;
3713 END LOOP;
3714 END IF;
3715 EXCEPTION
3716 WHEN fnd_api.g_exc_error
3717 THEN
3718 ROLLBACK TO process_input_records;
3719 x_return_status := fnd_api.g_ret_sts_error;
3720 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3721 WHEN fnd_api.g_exc_unexpected_error
3722 THEN
3723 ROLLBACK TO process_input_records;
3724 x_loading_status := 'UNEXPECTED_ERR';
3725 x_return_status := fnd_api.g_ret_sts_unexp_error;
3726 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3727 WHEN OTHERS
3728 THEN
3729 ROLLBACK TO process_input_records;
3730 x_loading_status := 'UNEXPECTED_ERR';
3731 x_return_status := fnd_api.g_ret_sts_unexp_error;
3732
3733 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3734 THEN
3735 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3736 END IF;
3737
3738 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3739 END process_input_records;
3740
3741 PROCEDURE delete_plan_element (
3742 p_api_version IN NUMBER := 0,
3743 p_init_msg_list IN VARCHAR2 := cn_api.g_false,
3744 p_commit IN VARCHAR2 := cn_api.g_false,
3745 p_validation_level IN NUMBER := cn_api.g_valid_level_full,
3746 x_return_status OUT NOCOPY VARCHAR2,
3747 x_msg_count OUT NOCOPY NUMBER,
3748 x_msg_data OUT NOCOPY VARCHAR2,
3749 p_quota_rec IN plan_element_rec_type := g_miss_plan_element_rec,
3750 p_revenue_class_rec_tbl IN revenue_class_rec_tbl_type := g_miss_revenue_class_rec_tbl,
3751 p_rev_uplift_rec_tbl IN rev_uplift_rec_tbl_type := g_miss_rev_uplift_rec_tbl,
3752 p_rt_quota_asgns_rec_tbl IN rt_quota_asgns_rec_tbl_type := g_miss_rt_quota_asgns_rec_tbl,
3753 x_loading_status OUT NOCOPY VARCHAR2
3754 )
3755 IS
3756 l_quota_id NUMBER;
3757 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Plan_Element';
3758 l_api_version CONSTANT NUMBER := 1.0;
3759 l_p_quota_name cn_quotas.NAME%TYPE;
3760 l_p_revenue_class_rec_tbl revenue_class_rec_tbl_type;
3761 l_p_rev_uplift_rec_tbl rev_uplift_rec_tbl_type;
3762
3763 l_p_rev_uplift_rec_tbl1 cn_quota_rule_uplift_pvt.quota_rule_uplift_tbl_type;
3764
3765 l_p_rt_quota_asgns_rec_tbl rt_quota_asgns_rec_tbl_type;
3766 l_oai_array jtf_usr_hks.oai_data_array_type;
3767 l_bind_data_id NUMBER;
3768 g_last_update_date DATE := SYSDATE;
3769 g_last_updated_by NUMBER := fnd_global.user_id;
3770 g_creation_date DATE := SYSDATE;
3771 g_created_by NUMBER := fnd_global.user_id;
3772 g_last_update_login NUMBER := fnd_global.login_id;
3773 g_rowid VARCHAR2 (30);
3774 g_program_type VARCHAR2 (30);
3775 l_loading_status VARCHAR2 (80);
3776 l_org_id cn_quotas.org_id%TYPE;
3777 l_pvt_rec cn_plan_element_pvt.plan_element_rec_type;
3778 l_quota_rec plan_element_rec_type;
3779 l_return_status VARCHAR2 (1000);
3780 l_msg_data VARCHAR2 (2000);
3781 l_msg_count NUMBER;
3782 l_load_status VARCHAR2 (1000);
3783 l_val_org_id NUMBER;
3784 l_status VARCHAR2(1);
3785
3786 BEGIN
3787 -- Standard Start of API savepoint
3788 SAVEPOINT delete_plan_element;
3789
3790 -- Standard call to check for call compatibility.
3791 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
3792 THEN
3793 RAISE fnd_api.g_exc_unexpected_error;
3794 END IF;
3795
3796 -- Initialize message list if p_init_msg_list is set to TRUE.
3797 IF fnd_api.to_boolean (p_init_msg_list)
3798 THEN
3799 fnd_msg_pub.initialize;
3800 END IF;
3801
3802 -- Initialize API return status to success
3803 x_return_status := fnd_api.g_ret_sts_success;
3804 x_loading_status := 'CN_DELETED';
3805
3806 -- START OF MOAC ORG_ID VALIDATION
3807 l_val_org_id := p_quota_rec.org_id;
3808 mo_global.validate_orgid_pub_api(org_id => l_val_org_id,
3809 status => l_status);
3810
3811 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3812 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3813 'cn.plsql.cn_plan_element_pub.delete_plan_element.org_validate',
3814 'Validated org_id = ' || l_val_org_id || ' status = '||l_status);
3815 end if;
3816 -- END OF MOAC ORG_ID VALIDATION
3817
3818 -- API body
3819 l_quota_rec := p_quota_rec;
3820 l_p_quota_name := p_quota_rec.NAME;
3821 l_p_revenue_class_rec_tbl := p_revenue_class_rec_tbl;
3822 l_p_rev_uplift_rec_tbl := p_rev_uplift_rec_tbl;
3823 l_p_rt_quota_asgns_rec_tbl := p_rt_quota_asgns_rec_tbl;
3824
3825 -- ***TBD *** Need to call Process Records here
3826 process_input_records (p_quota_rec => l_quota_rec,
3827 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
3828 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
3829 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
3830 x_return_status => l_return_status,
3831 x_msg_count => l_msg_count,
3832 x_msg_data => l_msg_data,
3833 x_loading_status => l_load_status
3834 );
3835 x_loading_status := l_load_status;
3836
3837
3838
3839 /* 1. Calling BEFORE-CUSTOM Hook */
3840 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DELETE_PLAN_ELEMENT', 'B', 'C')
3841 THEN
3842 cn_plan_element_cuhk.delete_plan_element_pre (p_api_version => p_api_version,
3843 p_init_msg_list => p_init_msg_list,
3844 p_commit => fnd_api.g_false,
3845 p_validation_level => p_validation_level,
3846 x_return_status => x_return_status,
3847 x_msg_count => x_msg_count,
3848 x_msg_data => x_msg_data,
3849 p_quota_name => l_p_quota_name,
3850 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
3851 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
3852 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
3853 x_loading_status => x_loading_status
3854 );
3855 check_status (p_return_status => x_return_status);
3856 END IF;
3857
3858 /* 2. Calling BEFORE-VERTICAL Hook */
3859 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DELETE_PLAN_ELEMENT', 'B', 'V')
3860 THEN
3861 cn_plan_element_vuhk.delete_plan_element_pre (p_api_version => p_api_version,
3862 p_init_msg_list => p_init_msg_list,
3863 p_commit => fnd_api.g_false,
3864 p_validation_level => p_validation_level,
3865 x_return_status => x_return_status,
3866 x_msg_count => x_msg_count,
3867 x_msg_data => x_msg_data,
3868 p_quota_name => l_p_quota_name,
3869 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
3870 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
3871 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
3872 x_loading_status => x_loading_status
3873 );
3874 check_status (p_return_status => x_return_status);
3875 END IF;
3876
3877 /* ## MAIN IF BEGINS ## */
3878 IF (x_return_status = fnd_api.g_ret_sts_success AND x_loading_status = 'CN_DELETED')
3879 THEN
3880 -- This is calling the group API (cnxgqrub.pls)
3881 -- Needs refactoring
3882 -- Check if the Uplift Rec table count is > 0
3883 IF l_p_rev_uplift_rec_tbl.COUNT > 0
3884 THEN
3885 FOR i IN l_p_rev_uplift_rec_tbl.FIRST .. l_p_rev_uplift_rec_tbl.LAST LOOP
3886 l_p_rev_uplift_rec_tbl1(i).org_id := l_p_rev_uplift_rec_tbl(i).org_id;
3887 l_p_rev_uplift_rec_tbl1(i).quota_rule_uplift_id := NULL;
3888 l_p_rev_uplift_rec_tbl1(i).quota_rule_id :=NULL;
3889 l_p_rev_uplift_rec_tbl1(i).start_date :=l_p_rev_uplift_rec_tbl(i).start_date;
3890 l_p_rev_uplift_rec_tbl1(i).end_date := l_p_rev_uplift_rec_tbl(i).end_date;
3891 l_p_rev_uplift_rec_tbl1(i).payment_factor :=l_p_rev_uplift_rec_tbl(i).rev_class_payment_uplift;
3892 l_p_rev_uplift_rec_tbl1(i).quota_factor :=l_p_rev_uplift_rec_tbl(i).rev_class_quota_uplift;
3893 l_p_rev_uplift_rec_tbl1(i).object_version_number := l_p_rev_uplift_rec_tbl(i).object_version_number;
3894 l_p_rev_uplift_rec_tbl1(i).rev_class_name := l_p_rev_uplift_rec_tbl(i).rev_class_name;
3895 l_p_rev_uplift_rec_tbl1(i).rev_class_name_old :=l_p_rev_uplift_rec_tbl(i).rev_class_name_old;
3896 l_p_rev_uplift_rec_tbl1(i).start_date_old :=l_p_rev_uplift_rec_tbl(i).start_date;
3897 l_p_rev_uplift_rec_tbl1(i).end_date_old := l_p_rev_uplift_rec_tbl(i).start_date_old;
3898 END LOOP;
3899
3900 -- Call the Delete Quota Rule Uplifts Group Package Procedure
3901 cn_quota_rule_uplifts_grp.delete_quota_rule_uplift (p_api_version => p_api_version,
3902 p_init_msg_list => 'T',
3903 p_commit => p_commit,
3904 p_validation_level => p_validation_level,
3905 x_return_status => x_return_status,
3906 x_msg_count => x_msg_count,
3907 x_msg_data => x_msg_data,
3908 p_quota_name => l_p_quota_name,
3909 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl1,
3910 x_loading_status => x_loading_status
3911 );
3912 -- if the Return status is not success then Raise an Error
3913 check_status (p_return_status => x_return_status);
3914 END IF;
3915
3916 -- This is calling the group API (cnxvrqab.pls)
3917 -- Needs refactoring
3918 -- Check if the Rate Quota Assigns Table count is > 0
3919 IF l_p_rt_quota_asgns_rec_tbl.COUNT > 0
3920 THEN
3921 -- Call the rate_quota_assigns delete package procedure to delete the
3922 -- rate quota Assigns
3923 cn_rt_quota_asgns_pvt.delete_rt_quota_asgns (p_api_version => p_api_version,
3924 p_init_msg_list => 'T',
3925 p_commit => p_commit,
3926 p_validation_level => p_validation_level,
3927 x_return_status => x_return_status,
3928 x_msg_count => x_msg_count,
3929 x_msg_data => x_msg_data,
3930 p_quota_name => l_p_quota_name,
3931 p_org_id => p_quota_rec.org_id,
3932 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
3933 x_loading_status => x_loading_status
3934 );
3935 -- if the Return status is not success then Raise an Error
3936 check_status (p_return_status => x_return_status);
3937 END IF;
3938
3939 -- Check if the Revenue class table Count is Greater than 0 then
3940 -- Delete the revenue class by calling the Quota Rules Package
3941 IF l_p_revenue_class_rec_tbl.COUNT > 0
3942 THEN
3943 -- Call the Quota Rules group Package to Delete the Quota Rules
3944 -- It will cascade the Child records as well.
3945 -- Previously this used to call the group API. During the
3946 -- rewrite in R12 the group api for quota rules was eliminated and
3947 -- the code was added in cn_quota_rules_pvt itself.
3948 cn_quota_rule_pvt.delete_quota_rules (p_api_version => p_api_version,
3949 p_init_msg_list => p_init_msg_list,
3950 p_commit => p_commit,
3951 p_validation_level => p_validation_level,
3952 x_return_status => x_return_status,
3953 x_msg_count => x_msg_count,
3954 x_msg_data => x_msg_data,
3955 p_quota_name => l_p_quota_name,
3956 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
3957 x_loading_status => x_loading_status
3958 );
3959
3960 -- if the Return status is not success then Raise an Error
3961 IF (x_return_status <> fnd_api.g_ret_sts_success)
3962 THEN
3963 RAISE fnd_api.g_exc_error;
3964 END IF;
3965 END IF;
3966
3967 /* If no Child record is Passed then Delete the Parent Record
3968 The Plan Element. It will cascade the Rest of the Child
3969 Records */
3970 IF l_p_revenue_class_rec_tbl.COUNT = 0 AND l_p_rt_quota_asgns_rec_tbl.COUNT = 0 AND l_p_rev_uplift_rec_tbl.COUNT = 0
3971 THEN
3972 --l_pvt_rec.quota_id := l_quota_id;
3973 l_pvt_rec.quota_id := p_quota_rec.quota_id;
3974 l_pvt_rec.name := p_quota_rec.name;
3975 l_pvt_rec.org_id := p_quota_rec.org_id;
3976
3977
3978 -- Delete the Plan Element. Calls the private API rather than calling the PKG or TH
3979 cn_plan_element_pvt.delete_plan_element (p_api_version => p_api_version,
3980 p_init_msg_list => p_init_msg_list,
3981 p_commit => p_commit,
3982 p_validation_level => p_validation_level,
3983 p_plan_element => l_pvt_rec,
3984 x_return_status => x_return_status,
3985 x_msg_count => x_msg_count,
3986 x_msg_data => x_msg_data
3987 );
3988 check_status (p_return_status => x_return_status);
3989 END IF;
3990 END IF; /* ## MAIN IF ENDS ## */
3991
3992 /* Post processing */
3993 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DELETE_PLAN_ELEMENT', 'A', 'V')
3994 THEN
3995 cn_plan_element_vuhk.delete_plan_element_post (p_api_version => p_api_version,
3996 p_init_msg_list => p_init_msg_list,
3997 p_commit => fnd_api.g_false,
3998 p_validation_level => p_validation_level,
3999 x_return_status => x_return_status,
4000 x_msg_count => x_msg_count,
4001 x_msg_data => x_msg_data,
4002 p_quota_name => l_p_quota_name,
4003 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
4004 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
4005 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
4006 x_loading_status => x_loading_status
4007 );
4008 check_status (p_return_status => x_return_status);
4009 END IF;
4010
4011 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DELETE_PLAN_ELEMENT', 'A', 'C')
4012 THEN
4013 cn_plan_element_cuhk.delete_plan_element_post (p_api_version => p_api_version,
4014 p_init_msg_list => p_init_msg_list,
4015 p_commit => fnd_api.g_false,
4016 p_validation_level => p_validation_level,
4017 x_return_status => x_return_status,
4018 x_msg_count => x_msg_count,
4019 x_msg_data => x_msg_data,
4020 p_quota_name => l_p_quota_name,
4021 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
4022 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
4023 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
4024 x_loading_status => x_loading_status
4025 );
4026 check_status (p_return_status => x_return_status);
4027 END IF;
4028
4029 /* Following code is for message generation */
4030 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DELETE_PLAN_ELEMENT', 'M', 'M')
4031 THEN
4032 IF (cn_plan_element_cuhk.ok_to_generate_msg (p_plan_element_name => l_p_quota_name,
4033 p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
4034 p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
4035 p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl
4036 )
4037 )
4038 THEN
4039 -- XMLGEN.clearBindValues;
4040 -- XMLGEN.setBindValue('QUOTA_NAME', l_p_quota_name);
4041 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
4042 jtf_usr_hks.load_bind_data (l_bind_data_id, 'QUOTA_NAME', l_p_quota_name, 'S', 'T');
4043 jtf_usr_hks.generate_message (p_prod_code => 'CN',
4044 p_bus_obj_code => 'PL',
4045 p_bus_obj_name => 'PLAN_ELEMENT',
4046 p_action_code => 'D', /* D - Delete */
4047 p_bind_data_id => l_bind_data_id,
4048 p_oai_param => NULL,
4049 p_oai_array => l_oai_array,
4050 x_return_code => x_return_status
4051 );
4052 check_status (p_return_status => x_return_status);
4053 END IF;
4054 END IF;
4055
4056 x_return_status := fnd_api.g_ret_sts_success;
4057
4058 -- Standard Commit.
4059 IF fnd_api.to_boolean (p_commit)
4060 THEN
4061 COMMIT WORK;
4062 END IF;
4063
4064 --
4065 -- Standard call to get message count and if count is 1, get message info.
4066 --
4067 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4068 -- End of Delete Plan Element
4069 EXCEPTION
4070 WHEN fnd_api.g_exc_error
4071 THEN
4072 ROLLBACK TO delete_plan_element;
4073 x_return_status := fnd_api.g_ret_sts_error;
4074 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4075 WHEN fnd_api.g_exc_unexpected_error
4076 THEN
4077 ROLLBACK TO delete_plan_element;
4078 x_loading_status := 'UNEXPECTED_ERR';
4079 x_return_status := fnd_api.g_ret_sts_unexp_error;
4080 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4081 WHEN OTHERS
4082 THEN
4083 ROLLBACK TO delete_plan_element;
4084 x_loading_status := 'UNEXPECTED_ERR';
4085 x_return_status := fnd_api.g_ret_sts_unexp_error;
4086
4087 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
4088 THEN
4089 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4090 END IF;
4091
4092 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4093 END delete_plan_element;
4094
4095 -- -------------------------------------------------------------------------+
4096 -- | Procedure: Get_Plan_Element
4097 -- | Description: This is a local procedure, will be called from when you dup
4098 -- | Duplicate the Plan ELement.
4099 -- | It will populate all the PL/SQL Table ( Child records for the Given
4100 -- | Quota name and Pass it back to the calling Place.
4101 -- -------------------------------------------------------------------------+
4102 PROCEDURE get_plan_element (
4103 p_api_version IN NUMBER,
4104 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4105 p_commit IN VARCHAR2 := fnd_api.g_false,
4106 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
4107 x_return_status OUT NOCOPY VARCHAR2,
4108 x_msg_count OUT NOCOPY NUMBER,
4109 x_msg_data OUT NOCOPY VARCHAR2,
4110 p_plan_element_name IN cn_quotas.NAME%TYPE,
4111 p_org_id IN cn_quotas.org_id%TYPE,
4112 x_plan_element_rec OUT NOCOPY plan_element_rec_type,
4113 x_revenue_class_rec_tbl OUT NOCOPY revenue_class_rec_tbl_type,
4114 x_rev_uplift_rec_tbl OUT NOCOPY rev_uplift_rec_tbl_type,
4115 x_trx_factor_rec_tbl OUT NOCOPY trx_factor_rec_tbl_type,
4116 x_period_quotas_rec_tbl OUT NOCOPY period_quotas_rec_tbl_type,
4117 x_rt_quota_asgns_rec_tbl OUT NOCOPY rt_quota_asgns_rec_tbl_type,
4118 x_loading_status OUT NOCOPY VARCHAR2
4119 )
4120 IS
4121 l_api_name CONSTANT VARCHAR2 (30) := 'Get Plan Element';
4122 l_rule_index NUMBER;
4123 l_lift_index NUMBER;
4124 l_trx_index NUMBER;
4125 l_rt_index NUMBER;
4126 l_quota_rule_id NUMBER;
4127 l_period_index NUMBER;
4128 l_pe_name cn_quotas.NAME%TYPE;
4129 l_quota_id cn_quotas.quota_id%TYPE;
4130 l_revenue_class_id cn_quota_rules.revenue_class_id%TYPE;
4131
4132 -- Quotas Cursor
4133 CURSOR c_plan_element_rec_csr (
4134 pe_name cn_quotas.NAME%TYPE
4135 )
4136 IS
4137 SELECT q.quota_id quota_id,
4138 q.NAME,
4139 q.description,
4140 NULL period_type,
4141 cn_api.get_lkup_meaning (q.quota_type_code, 'QUOTA_TYPE') element_type,
4142 q.target,
4143 cn_api.get_lkup_meaning (q.incentive_type_code, 'INCENTIVE_TYPE') incentive_type,
4144 ct.NAME credit_type,
4145 cf.NAME calc_formula_name,
4146 q.rt_sched_custom_flag,
4147 q.package_name,
4148 q.performance_goal,
4149 q.payment_amount,
4150 q.start_date,
4151 q.end_date,
4152 q.quota_status,
4153 cit.NAME interval_name,
4154 q.payee_assign_flag,
4155 q.vesting_flag,
4156 q.addup_from_rev_class_flag,
4157 q.expense_account_id,
4158 q.liability_account_id,
4159 q.quota_group_code,
4160 q.attribute_category,
4161 q.attribute1,
4162 q.attribute2,
4163 q.attribute3,
4164 q.attribute4,
4165 q.attribute5,
4166 q.attribute6,
4167 q.attribute7,
4168 q.attribute8,
4169 q.attribute9,
4170 q.attribute10,
4171 q.attribute11,
4172 q.attribute12,
4173 q.attribute13,
4174 q.attribute14,
4175 q.attribute15,
4176 -- Bug 2531254
4177 q.payment_group_code,
4178 --CHANTHON:ADding org_id
4179 q.org_id,
4180 q.indirect_credit,
4181 q.salesreps_enddated_flag
4182 FROM cn_quotas q,
4183 cn_credit_types ct,
4184 cn_calc_formulas cf,
4185 cn_interval_types cit
4186 WHERE q.NAME = pe_name
4187 AND q.org_id = p_org_id
4188 --AND q.credit_type_id = ct.credit_type_id(+)
4189 --AND q.calc_formula_id = cf.calc_formula_id(+)
4190 --AND q.interval_type_id = cit.interval_type_id(+)
4191 AND q.credit_type_id = ct.credit_type_id(+)
4192 AND ct.org_id(+) = q.org_id
4193 AND q.calc_formula_id = cf.calc_formula_id(+)
4194 AND cf.org_id(+) = q.org_id
4195 AND q.interval_type_id = cit.interval_type_id(+)
4196 AND cit.org_id(+) = q.org_id
4197 AND delete_flag='N';
4198
4199 -- Quota Rules Cursor
4200 CURSOR c_quota_rules_rec_csr (
4201 pe_id cn_quotas.quota_id%TYPE
4202 )
4203 IS
4204 SELECT qr.quota_rule_id,
4205 rc.NAME rev_class_name,
4206 qr.target rev_class_target,
4207 qr.payment_amount rev_class_payment_amount,
4208 qr.performance_goal rev_class_performance_goal,
4209 qr.description,
4210 qr.attribute_category,
4211 qr.attribute1,
4212 qr.attribute2,
4213 qr.attribute3,
4214 qr.attribute4,
4215 qr.attribute5,
4216 qr.attribute6,
4217 qr.attribute7,
4218 qr.attribute8,
4219 qr.attribute9,
4220 qr.attribute10,
4221 qr.attribute11,
4222 qr.attribute12,
4223 qr.attribute13,
4224 qr.attribute14,
4225 qr.attribute15,
4226 qr.org_id
4227 FROM cn_revenue_classes rc,
4228 cn_quota_rules qr
4229 WHERE qr.revenue_class_id = rc.revenue_class_id AND quota_id = pe_id;
4230
4231 -- Quota rule uplifts
4232 CURSOR c_rule_uplift_rec_csr (
4233 p_quota_rule_id cn_quota_rules.quota_rule_id%TYPE
4234 )
4235 IS
4236 SELECT NULL rev_class_name,
4237 qru.start_date,
4238 qru.end_date,
4239 qru.payment_factor rev_class_payment_uplift,
4240 qru.quota_factor rev_class_quota_uplift,
4241 qru.attribute_category,
4242 qru.attribute1,
4243 qru.attribute2,
4244 qru.attribute3,
4245 qru.attribute4,
4246 qru.attribute5,
4247 qru.attribute6,
4248 qru.attribute7,
4249 qru.attribute8,
4250 qru.attribute9,
4251 qru.attribute10,
4252 qru.attribute11,
4253 qru.attribute12,
4254 qru.attribute13,
4255 qru.attribute14,
4256 qru.attribute15,
4257 NULL rev_class_name_old,
4258 NULL start_date1,
4259 NULL start_date2,
4260 qru.org_id org_id,
4261 qru.object_version_number object_version_number
4262 FROM cn_quota_rule_uplifts qru
4263 WHERE qru.quota_rule_id = p_quota_rule_id
4264 ORDER BY start_date;
4265
4266 -- Trx Factors Cursor
4267 CURSOR c_trx_factor_rec_csr (
4268 pe_id cn_quotas.quota_id%TYPE,
4269 p_quota_rule_id cn_quota_rules.quota_rule_id%TYPE
4270 )
4271 IS
4272 SELECT tf.trx_type,
4273 tf.event_factor,
4274 tf.org_id
4275 -- rev_class_name get it from previous cursor
4276 FROM cn_trx_factors tf
4277 WHERE tf.quota_id = pe_id AND tf.quota_rule_id = p_quota_rule_id;
4278
4279 -- Period Quotas Cursor
4280 CURSOR c_period_quotas_rec_csr (
4281 pe_id cn_quotas.quota_id%TYPE
4282 )
4283 IS
4284 SELECT cn_api.get_acc_period_name (period_id,org_id) period_name,
4285 period_target,
4286 period_payment,
4287 performance_goal,
4288 attribute1,
4289 attribute2,
4290 attribute3,
4291 attribute4,
4292 attribute5,
4293 attribute6,
4294 attribute7,
4295 attribute8,
4296 attribute9,
4297 attribute10,
4298 attribute11,
4299 attribute12,
4300 attribute13,
4301 attribute14,
4302 attribute15,
4303 NULL period_name_old,
4304 org_id
4305 FROM cn_period_quotas
4306 WHERE quota_id = pe_id;
4307
4308 -- Rate Quota Assigns Cursor
4309 CURSOR c_rt_quota_asgns_rec_csr (
4310 pe_id cn_quotas.quota_id%TYPE
4311 )
4312 IS
4313 SELECT cn_api.get_rate_table_name (rate_schedule_id) rate_schedule_name,
4314 cn_chk_plan_element_pkg.get_calc_formula_name (calc_formula_id) calc_formula_name,
4315 start_date,
4316 end_date,
4317 attribute_category,
4318 attribute1,
4319 attribute2,
4320 attribute3,
4321 attribute4,
4322 attribute5,
4323 attribute6,
4324 attribute7,
4325 attribute8,
4326 attribute9,
4327 attribute10,
4328 attribute11,
4329 attribute12,
4330 attribute13,
4331 attribute14,
4332 attribute15,
4333 NULL rate_schedule_name_old,
4334 NULL start_date1,
4335 NULL start_date2,
4336 org_id
4337 FROM cn_rt_quota_asgns
4338 WHERE quota_id = pe_id
4339 ORDER BY start_date;
4340 BEGIN
4341 -- Standard Start of API savepoint
4342 SAVEPOINT get_plan_element;
4343
4344 -- Standard call to check for call compatibility.
4345 IF NOT fnd_api.compatible_api_call (p_api_version, p_api_version, l_api_name, g_pkg_name)
4346 THEN
4347 RAISE fnd_api.g_exc_unexpected_error;
4348 END IF;
4349
4350 -- Initialize message list if p_init_msg_list is set to TRUE.
4351 IF fnd_api.to_boolean (p_init_msg_list)
4352 THEN
4353 fnd_msg_pub.initialize;
4354 END IF;
4355
4356 -- Initialize API return status to success
4357 x_return_status := fnd_api.g_ret_sts_success;
4358 x_loading_status := 'CN_RETURNED';
4359 -- API body
4360 -- Remove the spaces in the Quota name
4361 l_pe_name := LTRIM (RTRIM (p_plan_element_name));
4362
4363 -- Open the Plan ELement Cursor
4364 OPEN c_plan_element_rec_csr (l_pe_name);
4365
4366 FETCH c_plan_element_rec_csr
4367 INTO l_quota_id,
4368 x_plan_element_rec.NAME,
4369 x_plan_element_rec.description,
4370 x_plan_element_rec.period_type,
4371 x_plan_element_rec.element_type,
4372 x_plan_element_rec.target,
4373 x_plan_element_rec.incentive_type,
4374 x_plan_element_rec.credit_type,
4375 x_plan_element_rec.calc_formula_name,
4376 x_plan_element_rec.rt_sched_custom_flag,
4377 x_plan_element_rec.package_name,
4378 x_plan_element_rec.performance_goal,
4379 x_plan_element_rec.payment_amount,
4380 x_plan_element_rec.start_date,
4381 x_plan_element_rec.end_date,
4382 x_plan_element_rec.status,
4383 x_plan_element_rec.interval_name,
4384 x_plan_element_rec.payee_assign_flag,
4385 x_plan_element_rec.vesting_flag,
4386 x_plan_element_rec.addup_from_rev_class_flag,
4387 x_plan_element_rec.expense_account_id,
4388 x_plan_element_rec.liability_account_id,
4389 x_plan_element_rec.quota_group_code,
4390 x_plan_element_rec.attribute_category,
4391 x_plan_element_rec.attribute1,
4392 x_plan_element_rec.attribute2,
4393 x_plan_element_rec.attribute3,
4394 x_plan_element_rec.attribute4,
4395 x_plan_element_rec.attribute5,
4396 x_plan_element_rec.attribute6,
4397 x_plan_element_rec.attribute7,
4398 x_plan_element_rec.attribute8,
4399 x_plan_element_rec.attribute9,
4400 x_plan_element_rec.attribute10,
4401 x_plan_element_rec.attribute11,
4402 x_plan_element_rec.attribute12,
4403 x_plan_element_rec.attribute13,
4404 x_plan_element_rec.attribute14,
4405 x_plan_element_rec.attribute15,
4406 -- bug 2531254
4407 x_plan_element_rec.payment_group_code,
4408 --chanthon:org_id
4409 x_plan_element_rec.org_id,
4410 x_plan_element_rec.indirect_credit,
4411 x_plan_element_rec.sreps_enddated_flag;
4412
4413 CLOSE c_plan_element_rec_csr;
4414
4415 -- Check the Quota ID for for the Quota Name you Passed
4416 -- if the Quota ID id null then raise an Error
4417 IF l_quota_id IS NULL
4418 THEN
4419 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4420 THEN
4421 fnd_message.set_name ('CN', 'CN_PLN_NOT_EXIST');
4422 fnd_message.set_token ('PE_NAME', l_pe_name);
4423 fnd_msg_pub.ADD;
4424 END IF;
4425
4426 x_loading_status := 'CN_PLN_NOT_EXIST';
4427 GOTO end_api;
4428 END IF;
4429
4430 -- initilize the index values.
4431 l_rule_index := 1;
4432 l_trx_index := 1;
4433 l_period_index := 1;
4434 l_lift_index := 1;
4435 l_rt_index := 1;
4436
4437 -- Open the Quota Rules Cursor and Populate the corresponding
4438 -- Pl/SQL table for the Quota Rules
4439 FOR l_quota_rule IN c_quota_rules_rec_csr (l_quota_id)
4440 LOOP
4441 l_quota_rule_id := l_quota_rule.quota_rule_id;
4442 x_revenue_class_rec_tbl (l_rule_index).rev_class_name := l_quota_rule.rev_class_name;
4443 x_revenue_class_rec_tbl (l_rule_index).rev_class_target := l_quota_rule.rev_class_target;
4444 x_revenue_class_rec_tbl (l_rule_index).rev_class_payment_amount := l_quota_rule.rev_class_payment_amount;
4445 x_revenue_class_rec_tbl (l_rule_index).rev_class_performance_goal := l_quota_rule.rev_class_performance_goal;
4446 x_revenue_class_rec_tbl (l_rule_index).description := l_quota_rule.description;
4447 x_revenue_class_rec_tbl (l_rule_index).attribute_category := l_quota_rule.attribute_category;
4448 x_revenue_class_rec_tbl (l_rule_index).attribute1 := l_quota_rule.attribute1;
4449 x_revenue_class_rec_tbl (l_rule_index).attribute2 := l_quota_rule.attribute2;
4450 x_revenue_class_rec_tbl (l_rule_index).attribute3 := l_quota_rule.attribute3;
4451 x_revenue_class_rec_tbl (l_rule_index).attribute4 := l_quota_rule.attribute4;
4452 x_revenue_class_rec_tbl (l_rule_index).attribute5 := l_quota_rule.attribute5;
4453 x_revenue_class_rec_tbl (l_rule_index).attribute6 := l_quota_rule.attribute6;
4454 x_revenue_class_rec_tbl (l_rule_index).attribute7 := l_quota_rule.attribute7;
4455 x_revenue_class_rec_tbl (l_rule_index).attribute8 := l_quota_rule.attribute8;
4456 x_revenue_class_rec_tbl (l_rule_index).attribute9 := l_quota_rule.attribute9;
4457 x_revenue_class_rec_tbl (l_rule_index).attribute10 := l_quota_rule.attribute10;
4458 x_revenue_class_rec_tbl (l_rule_index).attribute11 := l_quota_rule.attribute11;
4459 x_revenue_class_rec_tbl (l_rule_index).attribute12 := l_quota_rule.attribute12;
4460 x_revenue_class_rec_tbl (l_rule_index).attribute13 := l_quota_rule.attribute13;
4461 x_revenue_class_rec_tbl (l_rule_index).attribute14 := l_quota_rule.attribute14;
4462 x_revenue_class_rec_tbl (l_rule_index).attribute15 := l_quota_rule.attribute15;
4463 x_revenue_class_rec_tbl (l_rule_index).org_id := l_quota_rule.org_id;
4464
4465 -- looping the Trx factors for the Given quota and Quota Rules
4466 -- Populate the PL/SQL Table
4467 OPEN c_trx_factor_rec_csr (l_quota_id, l_quota_rule_id);
4468
4469 LOOP
4470 FETCH c_trx_factor_rec_csr
4471 INTO x_trx_factor_rec_tbl (l_trx_index).trx_type,
4472 x_trx_factor_rec_tbl (l_trx_index).event_factor,
4473 x_trx_factor_rec_tbl (l_trx_index).org_id;
4474
4475 EXIT WHEN c_trx_factor_rec_csr%NOTFOUND;
4476 x_trx_factor_rec_tbl (l_trx_index).rev_class_name := x_revenue_class_rec_tbl (l_rule_index).rev_class_name;
4477 l_trx_index := l_trx_index + 1;
4478 END LOOP;
4479
4480 CLOSE c_trx_factor_rec_csr;
4481
4482 -- Looping the rule uplifs for the Given Quota ans Quota Rules
4483 -- Populate the PL/SQl Table
4484 OPEN c_rule_uplift_rec_csr (l_quota_rule_id);
4485
4486 LOOP
4487 FETCH c_rule_uplift_rec_csr
4488 INTO x_rev_uplift_rec_tbl (l_lift_index);
4489
4490 EXIT WHEN c_rule_uplift_rec_csr%NOTFOUND;
4491 x_rev_uplift_rec_tbl (l_lift_index).rev_class_name := x_revenue_class_rec_tbl (l_rule_index).rev_class_name;
4492 l_lift_index := l_lift_index + 1;
4493 END LOOP;
4494
4495 CLOSE c_rule_uplift_rec_csr;
4496
4497 l_rule_index := l_rule_index + 1;
4498 END LOOP;
4499
4500 -- Open the period Quotas Cursor to Populate the Periods Quotas
4501 -- PL/SQL table
4502 OPEN c_period_quotas_rec_csr (l_quota_id);
4503
4504 LOOP
4505 FETCH c_period_quotas_rec_csr
4506 INTO x_period_quotas_rec_tbl (l_period_index);
4507
4508 EXIT WHEN c_period_quotas_rec_csr%NOTFOUND;
4509 l_period_index := l_period_index + 1;
4510 END LOOP;
4511
4512 CLOSE c_period_quotas_rec_csr;
4513
4514 -- Open the Rate quota asgns Cursor to Populate the PL/SQL table
4515 OPEN c_rt_quota_asgns_rec_csr (l_quota_id);
4516
4517 LOOP
4518 FETCH c_rt_quota_asgns_rec_csr
4519 INTO x_rt_quota_asgns_rec_tbl (l_rt_index);
4520
4521 EXIT WHEN c_rt_quota_asgns_rec_csr%NOTFOUND;
4522 l_rt_index := l_rt_index + 1;
4523 END LOOP;
4524
4525 CLOSE c_rt_quota_asgns_rec_csr;
4526
4527 -- End of API body.
4528 -- Standard check of p_commit.
4529 IF fnd_api.to_boolean (p_commit)
4530 THEN
4531 COMMIT WORK;
4532 END IF;
4533
4534 <<end_api>>
4535 NULL;
4536 -- Standard call to get message count and if count is 1, get message info.
4537 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4538 -- END get plan element
4539 EXCEPTION
4540 WHEN fnd_api.g_exc_error
4541 THEN
4542 ROLLBACK TO get_plan_element;
4543 x_return_status := fnd_api.g_ret_sts_error;
4544 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4545 WHEN fnd_api.g_exc_unexpected_error
4546 THEN
4547 ROLLBACK TO get_plan_element;
4548 x_loading_status := 'UNEXPECTED_ERR';
4549 x_return_status := fnd_api.g_ret_sts_unexp_error;
4550 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4551 WHEN COLLECTION_IS_NULL
4552 THEN
4553 ROLLBACK TO get_plan_element;
4554 x_loading_status := 'COLLECTION_IS_NULL';
4555 x_return_status := fnd_api.g_ret_sts_unexp_error;
4556
4557 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
4558 THEN
4559 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4560 END IF;
4561
4562 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4563 WHEN NO_DATA_FOUND
4564 THEN
4565 ROLLBACK TO get_plan_element;
4566 x_loading_status := 'NO_DATA_FOUND';
4567 x_return_status := fnd_api.g_ret_sts_unexp_error;
4568
4569 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
4570 THEN
4571 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4572 END IF;
4573
4574 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4575 WHEN SUBSCRIPT_BEYOND_COUNT
4576 THEN
4577 ROLLBACK TO get_plan_element;
4578 x_loading_status := 'SUBSCRIPT_BEYOND_COUNT';
4579 x_return_status := fnd_api.g_ret_sts_unexp_error;
4580
4581 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
4582 THEN
4583 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4584 END IF;
4585
4586 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4587 WHEN SUBSCRIPT_OUTSIDE_LIMIT
4588 THEN
4589 ROLLBACK TO get_plan_element;
4590 x_loading_status := 'SUBSCRIPT_OUTSIDE_LIMIT';
4591 x_return_status := fnd_api.g_ret_sts_unexp_error;
4592
4593 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
4594 THEN
4595 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4596 END IF;
4597
4598 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4599 WHEN OTHERS
4600 THEN
4601 ROLLBACK TO get_plan_element;
4602 x_loading_status := 'UNEXPECTED_ERR';
4603 x_return_status := fnd_api.g_ret_sts_unexp_error;
4604
4605 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
4606 THEN
4607 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4608 END IF;
4609
4610 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4611 END get_plan_element;
4612
4613 -- -------------------------------------------------------------------------+
4614 -- | Procedure: Duplicate_Plan_Element
4615 -- | Description: This is a Public API to help the USer to Duplicate the
4616 -- | Existing Plan Element with just changing the Plan Element Name_2
4617 -- | Note: ** Important **
4618 -- | It creates all the Respective Child records for that Plan Element
4619 ----------------------------------------------------------------------------+
4620 PROCEDURE duplicate_plan_element (
4621 p_api_version IN NUMBER := 0,
4622 p_init_msg_list IN VARCHAR2 := cn_api.g_false,
4623 p_commit IN VARCHAR2 := cn_api.g_false,
4624 p_validation_level IN NUMBER := cn_api.g_valid_level_full,
4625 x_return_status OUT NOCOPY VARCHAR2,
4626 x_msg_count OUT NOCOPY NUMBER,
4627 x_msg_data OUT NOCOPY VARCHAR2,
4628 p_plan_element_name IN cn_quotas.NAME%TYPE := cn_api.g_miss_char,
4629 p_org_id IN NUMBER,
4630 x_plan_element_name OUT NOCOPY cn_quotas.NAME%TYPE,
4631 x_loading_status OUT NOCOPY VARCHAR2
4632 )
4633 IS
4634 l_api_name CONSTANT VARCHAR2 (30) := 'Duplicate_Plan_Element';
4635 l_api_version CONSTANT NUMBER := 1.0;
4636 l_plan_element_rec cn_plan_element_pub.plan_element_rec_type;
4637 l_revenue_class_rec_tbl cn_plan_element_pub.revenue_class_rec_tbl_type;
4638 l_rev_uplift_rec_tbl cn_plan_element_pub.rev_uplift_rec_tbl_type;
4639 l_pe_rec_tbl cn_chk_plan_element_pkg.pe_rec_tbl_type;
4640 l_trx_factor_rec_tbl cn_plan_element_pub.trx_factor_rec_tbl_type;
4641 l_period_quotas_rec_tbl cn_plan_element_pub.period_quotas_rec_tbl_type;
4642 l_rt_quota_asgns_rec_tbl cn_plan_element_pub.rt_quota_asgns_rec_tbl_type;
4643 l_length INTEGER := 30 - LENGTHB ('_2');
4644 l_name_too_long VARCHAR2 (1) := 'F';
4645 l_quota_id NUMBER;
4646 l_warning_flag VARCHAR2 (1) := 'F';
4647 l_p_plan_element_name cn_quotas.NAME%TYPE;
4648 l_x_plan_element_name cn_quotas.NAME%TYPE;
4649 l_oai_array jtf_usr_hks.oai_data_array_type;
4650 l_bind_data_id NUMBER;
4651 l_org_id NUMBER;
4652 l_status VARCHAR2(1);
4653 l_suffix varchar2(10) := null;
4654 l_prefix varchar2(10) := null;
4655
4656 BEGIN
4657 -- Standard Start of API savepoint
4658 SAVEPOINT duplicate_pe;
4659
4660 -- Standard call to check for call compatibility.
4661 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
4662 THEN
4663 RAISE fnd_api.g_exc_unexpected_error;
4664 END IF;
4665
4666 -- Initialize message list if p_init_msg_list is set to TRUE.
4667 IF fnd_api.to_boolean (p_init_msg_list)
4668 THEN
4669 fnd_msg_pub.initialize;
4670 END IF;
4671
4672 -- Initialize API return status to success
4673 x_return_status := fnd_api.g_ret_sts_success;
4674 x_loading_status := 'CN_INSERTED';
4675
4676 -- START OF MOAC ORG_ID VALIDATION
4677 l_org_id := p_org_id;
4678 mo_global.validate_orgid_pub_api(org_id => l_org_id,
4679 status => l_status);
4680
4681 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4682 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4683 'cn.plsql.cn_plan_element_pub.duplicate_plan_element.org_validate',
4684 'Validated org_id = ' || l_org_id || ' status = '||l_status);
4685 end if;
4686 -- END OF MOAC ORG_ID VALIDATION
4687
4688 -- API body
4689 l_p_plan_element_name := p_plan_element_name;
4690
4691 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DUPLICATE_PLAN_ELEMENT', 'B', 'C')
4692 THEN
4693 cn_plan_element_cuhk.duplicate_plan_element_pre (p_api_version => p_api_version,
4694 p_init_msg_list => p_init_msg_list,
4695 p_commit => fnd_api.g_false,
4696 p_validation_level => p_validation_level,
4697 x_return_status => x_return_status,
4698 x_msg_count => x_msg_count,
4699 x_msg_data => x_msg_data,
4700 p_plan_element_name => l_p_plan_element_name,
4701 x_plan_element_name => l_x_plan_element_name,
4702 x_loading_status => x_loading_status
4703 );
4704
4705 IF (x_return_status = fnd_api.g_ret_sts_error)
4706 THEN
4707 RAISE fnd_api.g_exc_error;
4708 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4709 THEN
4710 RAISE fnd_api.g_exc_unexpected_error;
4711 END IF;
4712 END IF;
4713
4714 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DUPLICATE_PLAN_ELEMENT', 'B', 'V')
4715 THEN
4716 cn_plan_element_vuhk.duplicate_plan_element_pre (p_api_version => p_api_version,
4717 p_init_msg_list => p_init_msg_list,
4718 p_commit => fnd_api.g_false,
4719 p_validation_level => p_validation_level,
4720 x_return_status => x_return_status,
4721 x_msg_count => x_msg_count,
4722 x_msg_data => x_msg_data,
4723 p_plan_element_name => l_p_plan_element_name,
4724 x_plan_element_name => l_x_plan_element_name,
4725 x_loading_status => x_loading_status
4726 );
4727
4728 IF (x_return_status = fnd_api.g_ret_sts_error)
4729 THEN
4730 RAISE fnd_api.g_exc_error;
4731 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4732 THEN
4733 RAISE fnd_api.g_exc_unexpected_error;
4734 END IF;
4735 END IF;
4736
4737 -- Call the Local Procedure get Plan Element to Populate the old
4738 -- Plan Element Information into the Record/Pl/SQL table
4739 get_plan_element (p_api_version => 1.0,
4740 x_return_status => x_return_status,
4741 x_msg_count => x_msg_count,
4742 x_msg_data => x_msg_data,
4743 p_plan_element_name => l_p_plan_element_name,
4744 p_org_id => p_org_id,
4745 x_plan_element_rec => l_plan_element_rec,
4746 x_revenue_class_rec_tbl => l_revenue_class_rec_tbl,
4747 x_rev_uplift_rec_tbl => l_rev_uplift_rec_tbl,
4748 x_trx_factor_rec_tbl => l_trx_factor_rec_tbl,
4749 x_period_quotas_rec_tbl => l_period_quotas_rec_tbl,
4750 x_rt_quota_asgns_rec_tbl => l_rt_quota_asgns_rec_tbl,
4751 x_loading_status => x_loading_status
4752 );
4753
4754 -- IF the Return Status is not success or Plan Element name
4755 IF (x_return_status <> fnd_api.g_ret_sts_success) OR (x_loading_status = 'CN_PLN_NOT_EXIST')
4756 THEN
4757 RAISE fnd_api.g_exc_error;
4758 END IF;
4759
4760 -- Check if the Plan Element name is > 30 Then Raise an Warning
4761 -- Commented the code because of inline copy new behavior
4762 /*
4763 IF l_plan_element_rec.NAME IS NOT NULL
4764 THEN
4765 IF (LENGTHB (l_plan_element_rec.NAME) > l_length)
4766 THEN
4767 l_x_plan_element_name := CONCAT (SUBSTRB (l_plan_element_rec.NAME, 1, l_length), '_2');
4768
4769 -- Add CN_DUP_PLN_NAME_TOO_LONG message
4770 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4771 THEN
4772 fnd_message.set_name ('CN', 'CN_DUP_PLN_NAME_TOO_LONG');
4773 fnd_message.set_token ('FROM_PE', l_p_plan_element_name);
4774 fnd_message.set_token ('TO_PE', l_x_plan_element_name);
4775 fnd_msg_pub.ADD;
4776 END IF;
4777
4778 l_warning_flag := 'Y';
4779 ELSE
4780 l_x_plan_element_name := CONCAT (l_plan_element_rec.NAME, '_2');
4781 END IF;
4782 END IF;
4783 */
4784
4785 -- Added this because of the enhancement in 12.1 when inline copy was enhanced
4786 -- Get quota id
4787 --Added check for delete_flag (bug 6467453) (hanaraya)
4788
4789 SELECT quota_id into l_quota_id from cn_quotas_all where org_id = l_org_id and name = l_plan_element_rec.NAME and delete_flag = 'N';
4790
4791 cn_plancopy_util_pvt.get_unique_name_for_component (
4792 p_id => l_quota_id,
4793 p_org_id => l_org_id,
4794 p_type => 'PLANELEMENT',
4795 p_suffix => l_suffix,
4796 p_prefix => l_prefix,
4797 x_name => l_x_plan_element_name,
4798 x_return_status => x_return_status,
4799 x_msg_count => x_msg_count,
4800 x_msg_data => x_msg_data
4801 );
4802
4803 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4804 RAISE FND_API.G_EXC_ERROR;
4805 END IF;
4806
4807 l_plan_element_rec.NAME := l_x_plan_element_name;
4808
4809 -- Check The Created Plan Element Already Exists in Database
4810 IF cn_chk_plan_element_pkg.get_quota_id (l_x_plan_element_name,p_org_id) IS NOT NULL
4811 THEN
4812 -- IF Plan Element Exists Raise an Error
4813 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4814 THEN
4815 fnd_message.set_name ('CN', 'PLN_QUOTA_EXISTS');
4816 fnd_msg_pub.ADD;
4817 END IF;
4818
4819 x_loading_status := 'CN_PLN_EXISTS';
4820 RAISE fnd_api.g_exc_error;
4821 END IF;
4822
4823 -- Call the Create_plan_element Procedure to Create the Plan ELement
4824 create_plan_element (p_api_version => 1.0,
4825 x_return_status => x_return_status,
4826 x_msg_count => x_msg_count,
4827 x_msg_data => x_msg_data,
4828 p_plan_element_rec => l_plan_element_rec,
4829 p_revenue_class_rec_tbl => l_revenue_class_rec_tbl,
4830 p_rev_uplift_rec_tbl => l_rev_uplift_rec_tbl,
4831 p_trx_factor_rec_tbl => l_trx_factor_rec_tbl,
4832 p_period_quotas_rec_tbl => l_period_quotas_rec_tbl,
4833 p_rt_quota_asgns_rec_tbl => l_rt_quota_asgns_rec_tbl,
4834 x_loading_status => x_loading_status,
4835 p_is_duplicate => 'Y'
4836 );
4837
4838 -- Raise an Error if the Status IS Success
4839 IF (x_loading_status = 'PLN_QUOTA_RULE_FACTORS_NOT_100') AND (x_return_status = fnd_api.g_ret_sts_success)
4840 THEN
4841 l_warning_flag := 'Y';
4842 ELSIF (x_return_status <> fnd_api.g_ret_sts_success) OR (x_loading_status = 'PLN_QUOTA_EXISTS') OR (x_loading_status = 'PLN_QUOTA_REV_EXISTS')
4843 THEN
4844 RAISE fnd_api.g_exc_error;
4845 END IF;
4846
4847 -- Check for the Warning Flag
4848 IF (x_return_status = fnd_api.g_ret_sts_success) AND (x_loading_status = 'CN_INSERTED')
4849 OR (x_loading_status = 'PLN_QUOTA_RULE_FACTORS_NOT_100')
4850 THEN
4851 x_loading_status := 'CN_INSERTED';
4852
4853 IF l_warning_flag = 'Y'
4854 THEN
4855 x_return_status := cn_api.g_ret_sts_warning;
4856 END IF;
4857 END IF;
4858
4859 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DUPLICATE_PLAN_ELEMENT', 'A', 'V')
4860 THEN
4861 cn_plan_element_vuhk.duplicate_plan_element_post (p_api_version => p_api_version,
4862 p_init_msg_list => p_init_msg_list,
4863 p_commit => fnd_api.g_false,
4864 p_validation_level => p_validation_level,
4865 x_return_status => x_return_status,
4866 x_msg_count => x_msg_count,
4867 x_msg_data => x_msg_data,
4868 p_plan_element_name => l_p_plan_element_name,
4869 x_plan_element_name => l_x_plan_element_name,
4870 x_loading_status => x_loading_status
4871 );
4872
4873 IF (x_return_status = fnd_api.g_ret_sts_error)
4874 THEN
4875 RAISE fnd_api.g_exc_error;
4876 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4877 THEN
4878 RAISE fnd_api.g_exc_unexpected_error;
4879 END IF;
4880 END IF;
4881
4882 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DUPLICATE_PLAN_ELEMENT', 'A', 'C')
4883 THEN
4884 cn_plan_element_cuhk.duplicate_plan_element_post (p_api_version => p_api_version,
4885 p_init_msg_list => p_init_msg_list,
4886 p_commit => fnd_api.g_false,
4887 p_validation_level => p_validation_level,
4888 x_return_status => x_return_status,
4889 x_msg_count => x_msg_count,
4890 x_msg_data => x_msg_data,
4891 p_plan_element_name => l_p_plan_element_name,
4892 x_plan_element_name => l_x_plan_element_name,
4893 x_loading_status => x_loading_status
4894 );
4895
4896 IF (x_return_status = fnd_api.g_ret_sts_error)
4897 THEN
4898 RAISE fnd_api.g_exc_error;
4899 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4900 THEN
4901 RAISE fnd_api.g_exc_unexpected_error;
4902 END IF;
4903 END IF;
4904
4905 -- x_plan_element_name := l_x_plan_element_name;
4906 x_plan_element_name := cn_chk_plan_element_pkg.get_quota_id (l_x_plan_element_name, p_org_id);
4907
4908 /* Following code is for message generation */
4909 IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DUPLICATE_PLAN_ELEMENT', 'M', 'M')
4910 THEN
4911 IF (cn_plan_element_cuhk.ok_to_generate_msg (p_plan_element_name => l_x_plan_element_name))
4912 THEN
4913 -- XMLGEN.clearBindValues;
4914 -- XMLGEN.setBindValue('QUOTA_NAME', l_plan_element_rec.name);
4915 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
4916 jtf_usr_hks.load_bind_data (l_bind_data_id, 'QUOTA_NAME', l_plan_element_rec.NAME, 'S', 'T');
4917 jtf_usr_hks.generate_message (p_prod_code => 'CN',
4918 p_bus_obj_code => 'PL',
4919 p_bus_obj_name => 'PLAN_ELEMENT',
4920 p_action_code => 'I', /* I - Insert */
4921 p_bind_data_id => l_bind_data_id,
4922 p_oai_param => NULL,
4923 p_oai_array => l_oai_array,
4924 x_return_code => x_return_status
4925 );
4926
4927 IF (x_return_status = fnd_api.g_ret_sts_error)
4928 THEN
4929 RAISE fnd_api.g_exc_error;
4930 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4931 THEN
4932 RAISE fnd_api.g_exc_unexpected_error;
4933 END IF;
4934 END IF;
4935 END IF;
4936
4937 x_return_status := fnd_api.g_ret_sts_success;
4938
4939 -- End of API body.
4940 -- Standard check of p_commit.
4941 IF fnd_api.to_boolean (p_commit)
4942 THEN
4943 COMMIT WORK;
4944 END IF;
4945
4946 -- Standard call to get message count and if count is 1, get message info.
4947 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4948 -- End of Duplicate Plan ELement
4949 EXCEPTION
4950 WHEN fnd_api.g_exc_error
4951 THEN
4952 ROLLBACK TO duplicate_pe;
4953 x_return_status := fnd_api.g_ret_sts_error;
4954 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4955 WHEN fnd_api.g_exc_unexpected_error
4956 THEN
4957 ROLLBACK TO duplicate_pe;
4958 x_loading_status := 'UNEXPECTED_ERR';
4959 x_return_status := fnd_api.g_ret_sts_unexp_error;
4960 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4961 WHEN OTHERS
4962 THEN
4963 ROLLBACK TO duplicate_pe;
4964 x_loading_status := 'UNEXPECTED_ERR';
4965 x_return_status := fnd_api.g_ret_sts_unexp_error;
4966
4967 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
4968 THEN
4969 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4970 END IF;
4971
4972 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4973 END duplicate_plan_element;
4974 END cn_plan_element_pub;