[Home] [Help]
PACKAGE BODY: APPS.CN_COMP_PLAN_PUB
Source
1 PACKAGE BODY CN_COMP_PLAN_PUB AS
2 /* $Header: cnpcpb.pls 120.8 2006/06/13 09:13:31 kjayapau ship $ */
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CN_COMP_PLAN_PUB';
4 g_file_name CONSTANT VARCHAR2 (12) := 'cnpcpb.pls';
5 g_rowid VARCHAR2 (30);
6 g_program_type VARCHAR2 (30);
7
8 --| ----------------------------------------------------------------------+
9 --| Procedure : valid_pe_assign
10 --| Desc : Procedure to validate plan element assignment to a compensation
11 --| plan.
12 --| --------------------------------------------------------------------+
13 PROCEDURE valid_pe_assign
14 (
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_msg_count OUT NOCOPY NUMBER,
17 x_msg_data OUT NOCOPY VARCHAR2,
18 p_cp_name IN VARCHAR2,
19 p_pe_name IN VARCHAR2,
20 p_cp_start_date IN DATE,
21 p_cp_end_date IN DATE,
22 p_loading_status IN VARCHAR2,
23 p_org_id IN NUMBER,
24 x_cp_id OUT NOCOPY NUMBER,
25 x_pe_id OUT NOCOPY NUMBER,
26 x_loading_status OUT NOCOPY VARCHAR2
27 )
28 IS
29 l_api_name CONSTANT VARCHAR2 (30) := 'valid_pe_assign';
30 l_dummy NUMBER;
31 l_rc_overlap cn_comp_plans.allow_rev_class_overlap%TYPE;
32 l_rev_class_total NUMBER := 0;
33 l_rev_class_total_unique NUMBER := 0;
34 l_quota_type_code cn_quotas.quota_type_code%TYPE;
35 l_quota_start_date DATE;
36 l_quota_end_date DATE;
37 l_flag BOOLEAN;
38
39 -- Since cn_dim_hier_periods is gone, we also need to change this
40 CURSOR check_overlap_curs (
41 l_cp_id NUMBER,
42 l_rev_class_hierarchy_id NUMBER
43 )
44 IS
45 SELECT COUNT (de.value_external_id),
46 COUNT (DISTINCT de.value_external_id)
47 FROM cn_dim_explosion de,
48 cn_quota_rules qr,
49 cn_quota_assigns qa,
50 cn_quotas_v q,
51 cn_dim_hierarchies dh
52 WHERE dh.header_dim_hierarchy_id = l_rev_class_hierarchy_id
53 AND ( ( q.end_date IS NULL
54 AND NVL (TRUNC (dh.end_date), TRUNC (q.start_date)) >=
55 TRUNC (q.start_date)
56 )
57 OR ( q.end_date IS NOT NULL
58 AND ( TRUNC (dh.start_date) BETWEEN TRUNC (q.start_date)
59 AND TRUNC (q.end_date)
60 OR ( TRUNC (dh.start_date) < TRUNC (q.start_date)
61 AND NVL (TRUNC (dh.end_date),
62 TRUNC (q.end_date))
63 BETWEEN TRUNC (q.start_date)
64 AND TRUNC (q.end_date)
65 )
66 )
67 )
68 )
69 AND de.dim_hierarchy_id = dh.dim_hierarchy_id
70 AND de.ancestor_external_id = qr.revenue_class_id
71 AND qr.quota_id = qa.quota_id
72 AND qa.comp_plan_id = l_cp_id
73 AND qa.quota_id = q.quota_id
74 -- only formula and external have revenue classes
75 -- Modified
76 AND q.quota_type_code IN ('EXTERNAL', 'FORMULA');
77 BEGIN
78 -- Initialize API return status to success
79 x_return_status := fnd_api.g_ret_sts_success;
80 x_loading_status := p_loading_status;
81
82 -- API body
83 -- Check if CP exist
84 BEGIN
85 SELECT comp_plan_id, allow_rev_class_overlap
86 INTO x_cp_id, l_rc_overlap
87 FROM cn_comp_plans
88 WHERE NAME = p_cp_name AND org_id = p_org_id;
89 EXCEPTION
90 WHEN NO_DATA_FOUND
91 THEN
92 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
93 THEN
94 fnd_message.set_name ('CN', 'CN_CP_NOT_EXIST');
95 fnd_message.set_token ('CP_NAME', p_cp_name);
96 fnd_msg_pub.ADD;
97 END IF;
98
99 x_loading_status := 'CN_CP_NOT_EXIST';
100 RAISE fnd_api.g_exc_error;
101 END;
102
103 -- Check if PE exist
104 BEGIN
105 SELECT quota_id, quota_type_code, start_date,
106 end_date
107 INTO x_pe_id, l_quota_type_code, l_quota_start_date,
108 l_quota_end_date
109 FROM cn_quotas_v
110 WHERE NAME = p_pe_name AND org_id = p_org_id ;
111 EXCEPTION
112 WHEN NO_DATA_FOUND
113 THEN
114 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
115 THEN
116 fnd_message.set_name ('CN', 'CN_PLN_NOT_EXIST');
117 fnd_message.set_token ('PE_NAME', p_pe_name);
118 fnd_msg_pub.ADD;
119 END IF;
120
121 x_loading_status := 'CN_PLN_NOT_EXIST';
122 RAISE fnd_api.g_exc_error;
123 END;
124
125 -- Check if already assigned( duplicate assigned )
126 BEGIN
127 SELECT 1
128 INTO l_dummy
129 FROM SYS.DUAL
130 WHERE NOT EXISTS (
131 SELECT 1
132 FROM cn_quota_assigns
133 WHERE quota_id = x_pe_id
134 AND comp_plan_id = x_cp_id);
135 EXCEPTION
136 WHEN NO_DATA_FOUND
137 THEN
138 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
139 THEN
140 fnd_message.set_name ('CN', 'PLN_QUOTA_ASSIGNED');
141 fnd_msg_pub.ADD;
142 END IF;
143
144 x_loading_status := 'PLN_QUOTA_ASSIGNED';
145 GOTO end_api;
146 END;
147
148 -- Check unique rev class of Allow_rev_class_overlap = N
149 IF l_rc_overlap = 'N'
150 THEN
151 OPEN check_overlap_curs (x_cp_id,
152 cn_global_var.g_rev_class_hierarchy_id
153 );
154
155 FETCH check_overlap_curs
156 INTO l_rev_class_total, l_rev_class_total_unique;
157
158 CLOSE check_overlap_curs;
159
160 IF l_rev_class_total <> l_rev_class_total_unique
161 THEN
162 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
163 THEN
164 fnd_message.set_name ('CN', 'PLN_PLAN_DUP_REV_CLASS');
165 fnd_message.set_token ('PLAN_NAME', p_cp_name);
166 fnd_msg_pub.ADD;
167 END IF;
168
169 x_loading_status := 'PLN_PLAN_DUP_REV_CLASS';
170 RAISE fnd_api.g_exc_error;
171 END IF;
172 END IF;
173
174 -- Date Range check for Plan element
175 IF (l_quota_end_date is null AND l_quota_start_date > p_cp_end_date) THEN
176 l_flag := true;
177 ELSE IF (p_cp_end_date is null AND l_quota_end_date < p_cp_start_date) THEN
178
179 l_flag := true;
180
181 ELSE IF (p_cp_end_date is not null AND
182 l_quota_end_date is not null AND
183 l_quota_start_date not between p_cp_start_date and p_cp_end_date OR
184 l_quota_end_date not between p_cp_start_date and p_cp_end_date) THEN
185
186 l_flag := true;
187
188 END IF;
189 END IF;
190 END IF;
191
192 IF l_flag = true
193 THEN
194 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
195 THEN
196 fnd_message.set_name ('CN', 'CN_PLAN_ELT_DISJOINT');
197 fnd_msg_pub.ADD;
198 END IF;
199
200 x_loading_status := 'CN_PLAN_ELT_DISJOINT';
201 RAISE fnd_api.g_exc_error;
202 END IF;
203
204
205 -- End of API body.
206 <<end_api>>
207 NULL;
208 -- Standard call to get message count and if count is 1, get message info.
209 fnd_msg_pub.count_and_get (p_count => x_msg_count,
210 p_data => x_msg_data,
211 p_encoded => fnd_api.g_false
212 );
213 EXCEPTION
214 WHEN fnd_api.g_exc_error
215 THEN
216 x_return_status := fnd_api.g_ret_sts_error;
217 fnd_msg_pub.count_and_get (p_count => x_msg_count,
218 p_data => x_msg_data,
219 p_encoded => fnd_api.g_false
220 );
221 WHEN fnd_api.g_exc_unexpected_error
222 THEN
223 x_return_status := fnd_api.g_ret_sts_unexp_error;
224 x_loading_status := 'UNEXPECTED_ERR';
225 fnd_msg_pub.count_and_get (p_count => x_msg_count,
226 p_data => x_msg_data,
227 p_encoded => fnd_api.g_false
228 );
229 WHEN OTHERS
230 THEN
231 x_return_status := fnd_api.g_ret_sts_unexp_error;
232 x_loading_status := 'UNEXPECTED_ERR';
233
234 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
235 THEN
236 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
237 END IF;
238
239 fnd_msg_pub.count_and_get (p_count => x_msg_count,
240 p_data => x_msg_data,
241 p_encoded => fnd_api.g_false
242 );
243 END valid_pe_assign;
244
245 --| ----------------------------------------------------------------------+
246 --| Procedure : Create_Plan_Element_Assign
247 --| Desc : Procedure to create a new plan element assigned
248 --| --------------------------------------------------------------------- +
249 PROCEDURE create_plan_element_assign (
250 p_api_version IN NUMBER,
251 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
252 p_commit IN VARCHAR2 := fnd_api.g_false,
253 p_validation_level IN NUMBER
254 := fnd_api.g_valid_level_full,
255 x_return_status OUT NOCOPY VARCHAR2,
256 x_msg_count OUT NOCOPY NUMBER,
257 x_msg_data OUT NOCOPY VARCHAR2,
258 p_comp_plan_name IN cn_comp_plans.NAME%TYPE,
259 p_comp_start_date IN DATE,
260 p_comp_end_date IN DATE,
261 p_plan_element_name IN cn_quotas.NAME%TYPE,
262 p_org_id IN cn_quotas.org_id%TYPE,
263 x_loading_status OUT NOCOPY VARCHAR2
264 )
265 IS
266 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Plan_Element_Assign';
267 l_api_version CONSTANT NUMBER := 1.0;
268 l_lk_meaning cn_lookups.meaning%TYPE;
269 l_comp_plan_id cn_quota_assigns.comp_plan_id%TYPE;
270 l_pe_id cn_quota_assigns.quota_id%TYPE;
271 l_quota_assign_id cn_quota_assigns.quota_assign_id%TYPE;
272 l_loading_status VARCHAR2 (1000);
273 BEGIN
274 -- Standard Start of API savepoint
275 SAVEPOINT create_plan_element_assign;
276
277 -- Standard call to check for call compatibility.
278 IF NOT fnd_api.compatible_api_call (l_api_version,
279 p_api_version,
280 l_api_name,
281 g_pkg_name
282 )
283 THEN
284 RAISE fnd_api.g_exc_unexpected_error;
285 END IF;
286
287 -- Initialize message list if p_init_msg_list is set to TRUE.
288 IF fnd_api.to_boolean (p_init_msg_list)
289 THEN
290 fnd_msg_pub.initialize;
291 END IF;
292
293 -- Initialize API return status to success
294 x_return_status := fnd_api.g_ret_sts_success;
295 l_loading_status := 'CN_INSERTED';
296 -- API body
297 --
298 -- Valid plan element assignment
299 --
300 valid_pe_assign (x_return_status => x_return_status,
301 x_msg_count => x_msg_count,
302 x_msg_data => x_msg_data,
303 p_cp_name => LTRIM (RTRIM (p_comp_plan_name)
304 ),
305 p_pe_name => LTRIM
306 (RTRIM (p_plan_element_name)
307 ),
308 p_cp_start_date => p_comp_start_date,
309 p_cp_end_date => p_comp_end_date,
310 p_loading_status => l_loading_status,
311 p_org_id => p_org_id,
312 x_cp_id => l_comp_plan_id,
313 x_pe_id => l_pe_id,
314 x_loading_status => x_loading_status
315 );
316
317 IF (x_return_status <> fnd_api.g_ret_sts_success)
318 THEN
319 RAISE fnd_api.g_exc_error;
320 ELSIF x_loading_status <> 'PLN_QUOTA_ASSIGNED'
321 THEN
322 -- Create comp plan into cn_comp_plans
323 cn_quota_assigns_pkg.begin_record
324 (x_operation => 'INSERT',
325 x_comp_plan_id => l_comp_plan_id,
326 x_quota_id => l_pe_id,
327 x_quota_assign_id => l_quota_assign_id,
328 x_quota_id_old => NULL,
329 x_quota_sequence => 0,
330 x_org_id => p_org_id
331 );
335 (p_comp_plan_id => l_comp_plan_id,
332 -- check the overlap
333 l_loading_status := x_loading_status;
334 cn_api.check_revenue_class_overlap
336 p_rc_overlap => NULL,
337 p_loading_status => l_loading_status,
338 x_loading_status => x_loading_status,
339 x_return_status => x_return_status
340 );
341
342 IF (x_return_status <> fnd_api.g_ret_sts_success)
343 THEN
344 RAISE fnd_api.g_exc_error;
345 END IF;
346 END IF;
347
348 -- End of API body.
349 -- Standard check of p_commit.
350 IF fnd_api.to_boolean (p_commit)
351 THEN
352 COMMIT WORK;
353 END IF;
354
355 -- Standard call to get message count and if count is 1, get message info.
356 fnd_msg_pub.count_and_get (p_count => x_msg_count,
357 p_data => x_msg_data,
358 p_encoded => fnd_api.g_false
359 );
360 EXCEPTION
361 WHEN fnd_api.g_exc_error
362 THEN
363 ROLLBACK TO create_plan_element_assign;
364 x_return_status := fnd_api.g_ret_sts_error;
365 fnd_msg_pub.count_and_get (p_count => x_msg_count,
366 p_data => x_msg_data,
367 p_encoded => fnd_api.g_false
368 );
369 WHEN fnd_api.g_exc_unexpected_error
370 THEN
371 ROLLBACK TO create_plan_element_assign;
372 x_loading_status := 'UNEXPECTED_ERR';
373 x_return_status := fnd_api.g_ret_sts_unexp_error;
374 fnd_msg_pub.count_and_get (p_count => x_msg_count,
375 p_data => x_msg_data,
376 p_encoded => fnd_api.g_false
377 );
378 WHEN OTHERS
379 THEN
380 ROLLBACK TO create_plan_element_assign;
381 x_loading_status := 'UNEXPECTED_ERR';
382 x_return_status := fnd_api.g_ret_sts_unexp_error;
383
384 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
385 THEN
386 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
387 END IF;
388
389 fnd_msg_pub.count_and_get (p_count => x_msg_count,
390 p_data => x_msg_data,
391 p_encoded => fnd_api.g_false
392 );
393 END create_plan_element_assign;
394
395 --|--------------------------------------------------------------------------+
396 --|Procedure: chk_cp_consistent
397 --|Desc : The same compensation plan already exist in the database, this
398 --| procedure will check if all input for this comp plan is as
399 --| the same as those exists in the database
400 --|--------------------------------------------------------------------------+
401 PROCEDURE chk_cp_consistent (
402 x_return_status OUT NOCOPY VARCHAR2,
403 p_cp_rec IN comp_plan_rec_type,
404 p_loading_status IN VARCHAR2,
405 x_loading_status OUT NOCOPY VARCHAR2
406 )
407 IS
408 l_api_name CONSTANT VARCHAR2 (30) := 'chk_cp_consistent';
409
410 CURSOR c_cp_csr
411 IS
412 SELECT NAME, description, start_date, end_date, status_code,
413 allow_rev_class_overlap
414 FROM cn_comp_plans
415 WHERE NAME = p_cp_rec.NAME
416 AND org_id = p_cp_rec.org_id;
417
418
419 l_cp_csr c_cp_csr%ROWTYPE;
420 l_lkup_meaning cn_lookups.meaning%TYPE;
421 BEGIN
422 x_return_status := fnd_api.g_ret_sts_success;
423 x_loading_status := p_loading_status;
424
425 OPEN c_cp_csr;
426
427 FETCH c_cp_csr
428 INTO l_cp_csr;
429
430 IF c_cp_csr%NOTFOUND
431 THEN
432 RAISE fnd_api.g_exc_unexpected_error;
433 END IF;
434
435 -- Check description consistent
436 IF (l_cp_csr.description <> p_cp_rec.description)
437 THEN
438 -- Error, check the msg level and add an error message to the
439 -- API message list
440 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
441 THEN
442 fnd_message.set_name ('CN', 'CN_CP_NOT_CONSISTENT');
443 fnd_message.set_token ('CP_NAME', p_cp_rec.NAME);
444 l_lkup_meaning :=
445 cn_api.get_lkup_meaning ('DESC', 'CP_OBJECT_TYPE');
446 fnd_message.set_token ('OBJ_NAME', l_lkup_meaning);
447 fnd_msg_pub.ADD;
448 END IF;
449
450 x_loading_status := 'CN_CP_NOT_CONSISTENT';
451 RAISE fnd_api.g_exc_error;
452 END IF;
453
454 -- Check start period consistent
455 IF (TRUNC (l_cp_csr.start_date) <> TRUNC (p_cp_rec.start_date))
459 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
456 THEN
457 -- Error, check the msg level and add an error message to the
458 -- API message list
460 THEN
461 fnd_message.set_name ('CN', 'CN_CP_NOT_CONSISTENT');
462 fnd_message.set_token ('CP_NAME', p_cp_rec.NAME);
463 l_lkup_meaning :=
464 cn_api.get_lkup_meaning ('START_DATE', 'CP_OBJECT_TYPE');
465 fnd_message.set_token ('OBJ_NAME', l_lkup_meaning);
466 fnd_msg_pub.ADD;
467 END IF;
468
469 x_loading_status := 'CN_CP_NOT_CONSISTENT';
470 RAISE fnd_api.g_exc_error;
471 END IF;
472
473 -- Check end period consistent
474 IF (NVL (TRUNC (l_cp_csr.end_date), fnd_api.g_miss_date) <>
475 NVL (TRUNC (p_cp_rec.end_date), fnd_api.g_miss_date)
476 )
477 THEN
478 -- Error, check the msg level and add an error message to the
479 -- API message list
480 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
481 THEN
482 fnd_message.set_name ('CN', 'CN_CP_NOT_CONSISTENT');
483 fnd_message.set_token ('CP_NAME', p_cp_rec.NAME);
484 l_lkup_meaning :=
485 cn_api.get_lkup_meaning ('END_DATE', 'CP_OBJECT_TYPE');
486 fnd_message.set_token ('OBJ_NAME', l_lkup_meaning);
487 fnd_msg_pub.ADD;
488 END IF;
489
490 x_loading_status := 'CN_CP_NOT_CONSISTENT';
491 RAISE fnd_api.g_exc_error;
492 END IF;
493
494 -- Check allow rc overlap consistent
495 IF (l_cp_csr.allow_rev_class_overlap <> p_cp_rec.rc_overlap)
496 THEN
497 -- Error, check the msg level and add an error message to the
498 -- API message list
499 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
500 THEN
501 fnd_message.set_name ('CN', 'CN_CP_NOT_CONSISTENT');
502 fnd_message.set_token ('CP_NAME', p_cp_rec.NAME);
503 l_lkup_meaning :=
504 cn_api.get_lkup_meaning ('REV_CLS_OVERLAP', 'CP_OBJECT_TYPE');
505 fnd_message.set_token ('OBJ_NAME', l_lkup_meaning);
506 fnd_msg_pub.ADD;
507 END IF;
508
509 x_loading_status := 'CN_CP_NOT_CONSISTENT';
510 RAISE fnd_api.g_exc_error;
511 END IF;
512
513 CLOSE c_cp_csr;
514 EXCEPTION
515 WHEN fnd_api.g_exc_error
516 THEN
517 x_return_status := fnd_api.g_ret_sts_error;
518 WHEN fnd_api.g_exc_unexpected_error
519 THEN
520 x_return_status := fnd_api.g_ret_sts_unexp_error;
521 x_loading_status := 'UNEXPECTED_ERR';
522 WHEN OTHERS
523 THEN
524 x_return_status := fnd_api.g_ret_sts_unexp_error;
525 x_loading_status := 'UNEXPECTED_ERR';
526
527 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
528 THEN
529 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
530 END IF;
531 END chk_cp_consistent;
532
533 --| ----------------------------------------------------------------------+
534 --| Procedure : valid_comp_plan
535 --| Desc : Procedure to validate comp plan, will not valid pe assigned
536 --| use valid_pe_assign() to check pe assignment
537 --| ---------------------------------------------------------------------+
538 PROCEDURE valid_comp_plan (
539 x_return_status OUT NOCOPY VARCHAR2,
540 x_msg_count OUT NOCOPY NUMBER,
541 x_msg_data OUT NOCOPY VARCHAR2,
542 p_cp_rec IN comp_plan_rec_type,
543 p_loading_status IN VARCHAR2,
544 x_loading_status OUT NOCOPY VARCHAR2
545 )
546 IS
547 l_api_name CONSTANT VARCHAR2 (30) := 'valid_comp_plan';
548 l_start_date cn_periods.start_date%TYPE;
549 l_end_date cn_periods.end_date%TYPE;
550 l_lkup_meaning cn_lookups.meaning%TYPE;
551 l_loading_status VARCHAR2 (30);
552 BEGIN
553 -- Initialize API return status to success
554 x_return_status := fnd_api.g_ret_sts_success;
555 x_loading_status := p_loading_status;
556 -- API body
557 -- Check if comp plan name is null
558 l_lkup_meaning := cn_api.get_lkup_meaning ('CP_NAME', 'CP_OBJECT_TYPE');
559 l_loading_status := x_loading_status; -- copy status to override NOCOPY
560
561 IF ((cn_api.chk_null_char_para (p_char_para => p_cp_rec.NAME,
562 p_obj_name => l_lkup_meaning,
563 p_loading_status => l_loading_status,
564 x_loading_status => x_loading_status
565 )
566 ) = fnd_api.g_true
567 )
568 THEN
569 RAISE fnd_api.g_exc_error;
570 END IF;
571
572 -- Check Start Date can not be missing or NULL
573 l_lkup_meaning :=
574 cn_api.get_lkup_meaning ('START_DATE', 'CP_OBJECT_TYPE');
575 l_loading_status := x_loading_status; -- copy status to override NOCOPY
576
577 IF ((cn_chk_plan_element_pkg.chk_miss_date_para
581 x_loading_status => x_loading_status
578 (p_date_para => p_cp_rec.start_date,
579 p_para_name => l_lkup_meaning,
580 p_loading_status => l_loading_status,
582 )
583 ) = fnd_api.g_true
584 )
585 THEN
586 RAISE fnd_api.g_exc_error;
587 ELSIF ((cn_chk_plan_element_pkg.chk_null_date_para
588 (p_date_para => p_cp_rec.start_date,
589 p_obj_name => l_lkup_meaning,
590 p_loading_status => l_loading_status,
591 x_loading_status => x_loading_status
592 )
593 ) = fnd_api.g_true
594 )
595 THEN
596 RAISE fnd_api.g_exc_error;
597 END IF;
598
599 -- Check if rc_overlap is null and must be 'Y' or 'N'
600 l_lkup_meaning :=
601 cn_api.get_lkup_meaning ('REV_CLS_OVERLAP', 'CP_OBJECT_TYPE');
602
603 IF (p_cp_rec.rc_overlap NOT IN ('Y', 'N'))
604 THEN
605 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
606 THEN
607 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
608 fnd_message.set_token ('OBJ_NAME', l_lkup_meaning);
609 fnd_msg_pub.ADD;
610 END IF;
611
612 x_loading_status := 'CN_INVALID_DATA';
613 RAISE fnd_api.g_exc_error;
614 END IF;
615
616 l_loading_status := x_loading_status; -- copy status to override NOCOPY
617
618 IF ((cn_api.chk_null_char_para (p_char_para => p_cp_rec.rc_overlap,
619 p_obj_name => l_lkup_meaning,
620 p_loading_status => l_loading_status,
621 x_loading_status => x_loading_status
622 )
623 ) = fnd_api.g_true
624 )
625 THEN
626 RAISE fnd_api.g_exc_error;
627 END IF;
628
629 -- Check if CP already exist, if so , check for consistency otherwise
630 -- check for start/end period range
631 BEGIN
632 SELECT 'CP_EXIST'
633 INTO x_loading_status
634 FROM cn_comp_plans
635 WHERE NAME = p_cp_rec.NAME
636 AND org_id = p_cp_rec.org_id;
637 EXCEPTION
638 WHEN NO_DATA_FOUND
639 THEN
640 NULL;
641 END;
642
643 IF x_loading_status = 'CP_EXIST'
644 THEN
645 --
646 -- Valid Rule : Check comp plan consistency
647 --
648 l_loading_status := x_loading_status;
649 -- copy status to override NOCOPY
650 chk_cp_consistent (x_return_status => x_return_status,
651 p_cp_rec => p_cp_rec,
652 p_loading_status => l_loading_status,
653 x_loading_status => x_loading_status
654 );
655
656 IF (x_return_status <> fnd_api.g_ret_sts_success)
657 THEN
658 RAISE fnd_api.g_exc_error;
659 END IF;
660 ELSE
661 --
662 -- Validate Rule : End period must be greater than Start period
663 --
664 IF ( p_cp_rec.end_date IS NOT NULL
665 AND p_cp_rec.end_date < p_cp_rec.start_date
666 )
667 THEN
668 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
669 THEN
670 fnd_message.set_name ('CN', 'CN_INVALID_DATE_RANGE');
671 fnd_msg_pub.ADD;
672 END IF;
673
674 x_loading_status := 'INVALID_END_DATE';
675 RAISE fnd_api.g_exc_error;
676 END IF;
677 END IF;
678
679 -- End of API body.
680 <<end_api>>
681 NULL;
682 -- Standard call to get message count and if count is 1, get message info.
683 fnd_msg_pub.count_and_get (p_count => x_msg_count,
684 p_data => x_msg_data,
685 p_encoded => fnd_api.g_false
686 );
687 EXCEPTION
688 WHEN fnd_api.g_exc_error
689 THEN
690 x_return_status := fnd_api.g_ret_sts_error;
691 fnd_msg_pub.count_and_get (p_count => x_msg_count,
692 p_data => x_msg_data,
693 p_encoded => fnd_api.g_false
694 );
695 WHEN fnd_api.g_exc_unexpected_error
696 THEN
697 x_return_status := fnd_api.g_ret_sts_unexp_error;
698 x_loading_status := 'UNEXPECTED_ERR';
699 fnd_msg_pub.count_and_get (p_count => x_msg_count,
700 p_data => x_msg_data,
701 p_encoded => fnd_api.g_false
702 );
706 x_loading_status := 'UNEXPECTED_ERR';
703 WHEN OTHERS
704 THEN
705 x_return_status := fnd_api.g_ret_sts_unexp_error;
707
708 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
709 THEN
710 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
711 END IF;
712
713 fnd_msg_pub.count_and_get (p_count => x_msg_count,
714 p_data => x_msg_data,
715 p_encoded => fnd_api.g_false
716 );
717 END valid_comp_plan;
718
719 --| ----------------------------------------------------------------------+
720 --| Procedure : Create_Comp_Plan
721 --| Desc : Procedure to create a new compensation plan or add a plan
722 --| element to an existing compensation plan
723 --| ---------------------------------------------------------------------+
724 PROCEDURE create_comp_plan (
725 p_api_version IN NUMBER,
726 p_init_msg_list IN VARCHAR2,
727 p_commit IN VARCHAR2,
728 p_validation_level IN NUMBER,
729 x_return_status OUT NOCOPY VARCHAR2,
730 x_msg_count OUT NOCOPY NUMBER,
731 x_msg_data OUT NOCOPY VARCHAR2,
732 p_comp_plan_rec IN comp_plan_rec_type,
733 x_loading_status OUT NOCOPY VARCHAR2,
734 x_comp_plan_id IN OUT NOCOPY NUMBER
735 )
736 IS
737 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Comp_Plan';
738 l_api_version CONSTANT NUMBER := 1.0;
739 l_cp_rec comp_plan_rec_type := g_miss_comp_plan_rec;
740 l_lk_meaning cn_lookups.meaning%TYPE;
741 l_index NUMBER;
742 l_start_date cn_periods.start_date%TYPE;
743 l_end_date cn_periods.start_date%TYPE;
744 l_status_code cn_comp_plans.status_code%TYPE;
745 l_new_cp_flag VARCHAR2 (1) := 'N';
746 l_p_comp_plan_rec comp_plan_rec_type;
747 l_oai_array jtf_usr_hks.oai_data_array_type;
748 l_bind_data_id NUMBER;
749 l_loading_status VARCHAR2 (30);
750 l_note_msg VARCHAR2 (240);
751 l_note_id NUMBER;
752 l_status VARCHAR2(30);
753
754 l_p_return_status VARCHAR2(50) ;
755 l_p_msg_count NUMBER ;
756 l_p_msg_data varchar2(240) ;
757
758 l_p_cp_rec CN_COMP_PLAN_PVT.comp_plan_rec_type;
759
760 BEGIN
761 -- Standard Start of API savepoint
762 SAVEPOINT create_comp_plan;
763
764 -- Standard call to check for call compatibility.
765 IF NOT fnd_api.compatible_api_call (l_api_version,
766 p_api_version,
767 l_api_name,
768 g_pkg_name
769 )
770 THEN
771 RAISE fnd_api.g_exc_unexpected_error;
772 END IF;
773
774 -- Initialize message list if p_init_msg_list is set to TRUE.
775 IF fnd_api.to_boolean (p_init_msg_list)
776 THEN
777 fnd_msg_pub.initialize;
778 END IF;
779
780 -- Initialize API return status to success
781 x_return_status := fnd_api.g_ret_sts_success;
782 x_loading_status := 'CN_INSERTED';
783 -- API body
784 l_p_comp_plan_rec := p_comp_plan_rec;
785
786 -- Validating the MOAC Org Id
787 mo_global.validate_orgid_pub_api(org_id => l_p_comp_plan_rec.ORG_ID,status => l_status);
788
789 --dbms_output.put_line('Going into pre processing ');
790 IF jtf_usr_hks.ok_to_execute ('CN_COMP_PLAN_PUB',
791 'CREATE_COMP_PLAN',
792 'B',
793 'C'
794 )
795 THEN
796 cn_comp_plan_cuhk.create_comp_plan_pre
797 (p_api_version => p_api_version,
798 p_init_msg_list => p_init_msg_list,
799 p_commit => fnd_api.g_false,
800 p_validation_level => p_validation_level,
801 x_return_status => x_return_status,
802 x_msg_count => x_msg_count,
803 x_msg_data => x_msg_data,
804 p_comp_plan_rec => l_p_comp_plan_rec,
805 x_loading_status => x_loading_status
806 );
807
808 IF (x_return_status = fnd_api.g_ret_sts_error)
809 THEN
810 RAISE fnd_api.g_exc_error;
811 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
812 THEN
813 RAISE fnd_api.g_exc_unexpected_error;
814 END IF;
815 END IF;
816
817 IF jtf_usr_hks.ok_to_execute ('CN_COMP_PLAN_PUB',
821 )
818 'CREATE_COMP_PLAN',
819 'B',
820 'V'
822 THEN
823 cn_comp_plan_vuhk.create_comp_plan_pre
824 (p_api_version => p_api_version,
825 p_init_msg_list => p_init_msg_list,
826 p_commit => fnd_api.g_false,
827 p_validation_level => p_validation_level,
828 x_return_status => x_return_status,
829 x_msg_count => x_msg_count,
830 x_msg_data => x_msg_data,
831 p_comp_plan_rec => l_p_comp_plan_rec,
832 x_loading_status => x_loading_status
833 );
834
835 IF (x_return_status = fnd_api.g_ret_sts_error)
836 THEN
837 RAISE fnd_api.g_exc_error;
838 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
839 THEN
840 RAISE fnd_api.g_exc_unexpected_error;
841 END IF;
842 END IF;
843
844 --dbms_output.put_line('Executed for pre processing API');
845
846 -- Trim spaces before/after user input string and assign default value
847 SELECT DECODE (l_p_comp_plan_rec.NAME,
848 fnd_api.g_miss_char, NULL,
849 LTRIM (RTRIM (l_p_comp_plan_rec.NAME))
850 )
851 INTO l_cp_rec.NAME
852 FROM SYS.DUAL;
853
854 SELECT DECODE (l_p_comp_plan_rec.start_date,
855 fnd_api.g_miss_date, NULL,
856 l_p_comp_plan_rec.start_date
857 )
858 INTO l_cp_rec.start_date
859 FROM SYS.DUAL;
860
861 SELECT DECODE (l_p_comp_plan_rec.end_date,
862 fnd_api.g_miss_date, NULL,
863 l_p_comp_plan_rec.end_date
864 )
865 INTO l_cp_rec.end_date
866 FROM SYS.DUAL;
867
868 SELECT DECODE (l_p_comp_plan_rec.description,
869 fnd_api.g_miss_char, NULL,
870 LTRIM (RTRIM (l_p_comp_plan_rec.description))
871 )
872 INTO l_cp_rec.description
873 FROM SYS.DUAL;
874
875 SELECT DECODE (l_p_comp_plan_rec.plan_element_name,
876 fnd_api.g_miss_char, NULL,
877 LTRIM (RTRIM (l_p_comp_plan_rec.plan_element_name))
878 )
879 INTO l_cp_rec.plan_element_name
880 FROM SYS.DUAL;
881
882 SELECT DECODE (l_p_comp_plan_rec.org_id,
883 fnd_api.g_miss_char, NULL,
884 LTRIM (RTRIM (l_p_comp_plan_rec.org_id))
885 )
886 INTO l_cp_rec.org_id
887 FROM SYS.DUAL;
888
889 -- Set status_code lookup code = INCOMPLETE
890 /* SELECT lookup_code INTO l_cp_rec.status
891 FROM cn_lookups
892 WHERE lookup_type = 'PLAN_OBJECT_STATUS'
893 AND Upper(meaning) = 'INCOMPLETE'; */
894 l_cp_rec.status := 'INCOMPLETE';
895
896 -- Get rc_overlap lookup_code
897 SELECT DECODE (l_p_comp_plan_rec.rc_overlap,
898 fnd_api.g_miss_char, 'No',
899 LTRIM (RTRIM (l_p_comp_plan_rec.rc_overlap))
900 )
901 INTO l_lk_meaning
902 FROM SYS.DUAL;
903
904 /* BEGIN
905 SELECT lookup_code INTO l_cp_rec.rc_overlap
906 FROM fnd_lookups
907 WHERE lookup_type = 'YES_NO'
908 AND Upper(meaning) = Upper(l_lk_meaning);
909 EXCEPTION
910 WHEN no_data_found THEN
911 l_cp_rec.rc_overlap := SUBSTRB(l_lk_meaning,1,1);
912 END; */
913 SELECT DECODE (l_p_comp_plan_rec.rc_overlap,
914 fnd_api.g_miss_char, 'N',
915 NULL, 'N',
916 LTRIM (RTRIM (l_p_comp_plan_rec.rc_overlap))
917 )
918 INTO l_cp_rec.rc_overlap
919 FROM SYS.DUAL;
920
921 --
922 -- Valid compensation plan
923 --
924 l_loading_status := x_loading_status; -- copy status to override NOCOPY
925 valid_comp_plan (x_return_status => x_return_status,
926 x_msg_count => x_msg_count,
927 x_msg_data => x_msg_data,
928 p_cp_rec => l_cp_rec,
929 p_loading_status => l_loading_status,
930 x_loading_status => x_loading_status
931 );
932
933 IF (x_return_status <> fnd_api.g_ret_sts_success)
934 THEN
935 RAISE fnd_api.g_exc_error;
936 ELSIF x_loading_status <> 'CP_EXIST'
937 THEN
938 -- Create comp plan into cn_comp_plans
939 cn_comp_plans_pkg.begin_record
940 (x_operation => 'INSERT',
941 x_rowid => g_rowid,
942 x_comp_plan_id => x_comp_plan_id,
943 x_name => l_cp_rec.NAME,
944 x_description => l_cp_rec.description,
945 x_start_date => l_cp_rec.start_date,
946 x_end_date => l_cp_rec.end_date,
947 x_status_code => l_cp_rec.status,
948 x_allow_rev_class_overlap => l_cp_rec.rc_overlap,
949 x_last_update_date => SYSDATE,
950 x_last_updated_by => fnd_global.user_id,
951 x_creation_date => SYSDATE,
952 x_created_by => fnd_global.user_id,
953 x_last_update_login => fnd_global.login_id,
954 x_program_type => g_program_type,
955 x_start_date_old => NULL,
956 x_end_date_old => NULL,
957 x_allow_rev_class_overlap_old => NULL,
958 x_attribute_category => l_p_comp_plan_rec.attribute_category,
959 x_attribute1 => l_p_comp_plan_rec.attribute1,
960 x_attribute2 => l_p_comp_plan_rec.attribute2,
964 x_attribute6 => l_p_comp_plan_rec.attribute6,
961 x_attribute3 => l_p_comp_plan_rec.attribute3,
962 x_attribute4 => l_p_comp_plan_rec.attribute4,
963 x_attribute5 => l_p_comp_plan_rec.attribute5,
965 x_attribute7 => l_p_comp_plan_rec.attribute7,
966 x_attribute8 => l_p_comp_plan_rec.attribute8,
967 x_attribute9 => l_p_comp_plan_rec.attribute9,
968 x_attribute10 => l_p_comp_plan_rec.attribute10,
969 x_attribute11 => l_p_comp_plan_rec.attribute11,
970 x_attribute12 => l_p_comp_plan_rec.attribute12,
971 x_attribute13 => l_p_comp_plan_rec.attribute13,
972 x_attribute14 => l_p_comp_plan_rec.attribute14,
973 x_attribute15 => l_p_comp_plan_rec.attribute15,
974 x_org_id => l_p_comp_plan_rec.org_id
975 );
976 l_new_cp_flag := 'Y';
977
978 /* Added the Notes for R12 */
979
980 l_status_code := l_cp_rec.status;
981 fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_CREATE');
982 fnd_message.set_token ('CP_NAME', l_cp_rec.NAME);
983 l_note_msg := fnd_message.get;
984 jtf_notes_pub.create_note
985 (p_api_version => 1.0,
986 x_return_status => x_return_status,
987 x_msg_count => x_msg_count,
988 x_msg_data => x_msg_data,
989 p_source_object_id => x_comp_plan_id,
990 p_source_object_code => 'CN_COMP_PLANS',
991 p_notes => l_note_msg,
992 p_notes_detail => l_note_msg,
993 p_note_type => 'CN_SYSGEN', -- for system generated
994 x_jtf_note_id => l_note_id -- returned
995 );
996 ELSE
997 -- Comp plan already exist, get comp_plan_id ,status_code
998 SELECT comp_plan_id, status_code
999 INTO x_comp_plan_id, l_status_code
1000 FROM cn_comp_plans
1001 WHERE NAME = l_cp_rec.NAME
1002 AND org_id = l_cp_rec.org_id;
1003 END IF;
1004
1005 -- If plan element name is not null, Create Plan Element Assignment
1006 IF (l_cp_rec.plan_element_name IS NOT NULL)
1007 THEN
1008 -- Create Plan Element Assignment, will set status code = INCOMPLETE
1009 create_plan_element_assign
1010 (p_api_version => 1.0,
1011 x_return_status => x_return_status,
1012 x_msg_count => x_msg_count,
1013 x_msg_data => x_msg_data,
1014 p_comp_plan_name => l_cp_rec.NAME,
1015 p_comp_start_date => l_cp_rec.start_date,
1016 p_comp_end_date => l_cp_rec.end_date,
1017 p_plan_element_name => l_cp_rec.plan_element_name,
1018 p_org_id => l_p_comp_plan_rec.org_id,
1019 x_loading_status => x_loading_status
1020 );
1021
1022 IF (x_return_status <> fnd_api.g_ret_sts_success)
1023 THEN
1024 -- fail validate
1025 RAISE fnd_api.g_exc_error;
1026 ELSIF (x_loading_status = 'PLN_QUOTA_ASSIGNED')
1027 THEN
1028 -- PE already assigned to this cp, need to show error mesg in upload
1029 -- function
1030 GOTO end_api_body;
1031 END IF;
1032 l_p_cp_rec.comp_plan_id := x_comp_plan_id;
1033
1034 cn_comp_plan_pvt.validate_comp_plan(
1035 p_api_version => l_api_version,
1036 p_init_msg_list => p_init_msg_list,
1037 p_commit => p_commit,
1038 p_validation_level => p_validation_level,
1039 p_comp_plan => l_p_cp_rec,
1040 x_return_status => l_p_return_status,
1041 x_msg_count => l_p_msg_count,
1042 x_msg_data => l_p_msg_data
1043 );
1044 END IF;
1045
1046 -- Pass all validation, set status = COMPLETE
1047 -- only if it's a new Comp plan or
1048 -- the original status_code = COMPLETE
1049 /*IF ( (l_new_cp_flag = 'Y')
1050 AND (l_cp_rec.plan_element_name IS NOT NULL)
1051 )
1052 OR ((l_new_cp_flag = 'N') AND (l_status_code = 'COMPLETE'))
1053 THEN
1054 cn_comp_plans_pkg.set_status (x_comp_plan_id => x_comp_plan_id,
1055 x_quota_id => NULL,
1056 x_rate_schedule_id => NULL,
1057 x_status_code => 'COMPLETE',
1058 x_event => NULL
1059 );
1060 END IF;*/
1061 -- End of API body.
1062 <<end_api_body>>
1063 NULL;
1064
1065 /* Post processing */
1066 -- dbms_output.put_line('calling post processing API');
1067 IF jtf_usr_hks.ok_to_execute ('CN_COMP_PLAN_PUB',
1068 'CREATE_COMP_PLAN',
1069 'A',
1070 'V'
1074 (p_api_version => p_api_version,
1071 )
1072 THEN
1073 cn_comp_plan_vuhk.create_comp_plan_post
1075 p_init_msg_list => p_init_msg_list,
1076 p_commit => fnd_api.g_false,
1077 p_validation_level => p_validation_level,
1078 x_return_status => x_return_status,
1079 x_msg_count => x_msg_count,
1080 x_msg_data => x_msg_data,
1081 p_comp_plan_rec => l_p_comp_plan_rec,
1082 x_loading_status => x_loading_status
1083 );
1084
1085 IF (x_return_status = fnd_api.g_ret_sts_error)
1086 THEN
1087 RAISE fnd_api.g_exc_error;
1088 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1089 THEN
1090 RAISE fnd_api.g_exc_unexpected_error;
1091 END IF;
1092 END IF;
1093
1094 IF jtf_usr_hks.ok_to_execute ('CN_COMP_PLAN_PUB',
1095 'CREATE_COMP_PLAN',
1096 'A',
1097 'C'
1098 )
1099 THEN
1100 cn_comp_plan_cuhk.create_comp_plan_post
1101 (p_api_version => p_api_version,
1102 p_init_msg_list => p_init_msg_list,
1103 p_commit => fnd_api.g_false,
1104 p_validation_level => p_validation_level,
1105 x_return_status => x_return_status,
1106 x_msg_count => x_msg_count,
1107 x_msg_data => x_msg_data,
1108 p_comp_plan_rec => l_p_comp_plan_rec,
1109 x_loading_status => x_loading_status
1110 );
1111
1112 IF (x_return_status = fnd_api.g_ret_sts_error)
1113 THEN
1114 RAISE fnd_api.g_exc_error;
1115 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1116 THEN
1117 RAISE fnd_api.g_exc_unexpected_error;
1118 END IF;
1119 END IF;
1120
1121 --dbms_output.put_line(' Executed for post processing API');
1122
1123 /* Following code is for message generation */
1124 IF jtf_usr_hks.ok_to_execute ('CN_COMP_PLAN_PUB',
1125 'CREATE_COMP_PLAN',
1126 'M',
1127 'M'
1128 )
1129 THEN
1130 IF (cn_comp_plan_cuhk.ok_to_generate_msg
1131 (p_comp_plan_rec => l_p_comp_plan_rec)
1132 )
1133 THEN
1134 -- XMLGEN.clearBindValues;
1135 -- XMLGEN.setBindValue('COMP_PLAN_NAME', l_cp_rec.name);
1136 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1137 jtf_usr_hks.load_bind_data (l_bind_data_id,
1138 'COMP_PLAN_NAME',
1139 l_cp_rec.NAME,
1140 'S',
1141 'T'
1142 );
1143 jtf_usr_hks.generate_message (p_prod_code => 'CN',
1144 p_bus_obj_code => 'CP',
1145 p_bus_obj_name => 'COMP_PLAN',
1146 p_action_code => 'I',
1147 /* I - Insert */
1148 p_bind_data_id => l_bind_data_id,
1149 p_oai_param => NULL,
1150 p_oai_array => l_oai_array,
1151 x_return_code => x_return_status
1152 );
1153
1154 IF (x_return_status = fnd_api.g_ret_sts_error)
1155 THEN
1156 RAISE fnd_api.g_exc_error;
1157 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1158 THEN
1159 RAISE fnd_api.g_exc_unexpected_error;
1160 END IF;
1161 END IF;
1162 END IF;
1163
1164 x_return_status := fnd_api.g_ret_sts_success;
1165
1166 -- Standard check of p_commit.
1167 IF fnd_api.to_boolean (p_commit)
1168 THEN
1169 COMMIT WORK;
1170 END IF;
1171
1172 -- Standard call to get message count and if count is 1, get message info.
1173 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1174 p_data => x_msg_data,
1175 p_encoded => fnd_api.g_false
1176 );
1177 EXCEPTION
1178 WHEN fnd_api.g_exc_error
1179 THEN
1180 ROLLBACK TO create_comp_plan;
1181 x_return_status := fnd_api.g_ret_sts_error;
1182 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1183 p_data => x_msg_data,
1184 p_encoded => fnd_api.g_false
1185 );
1186 WHEN fnd_api.g_exc_unexpected_error
1187 THEN
1188 ROLLBACK TO create_comp_plan;
1189 x_loading_status := 'UNEXPECTED_ERR';
1190 x_return_status := fnd_api.g_ret_sts_unexp_error;
1191 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1192 p_data => x_msg_data,
1193 p_encoded => fnd_api.g_false
1194 );
1195 WHEN OTHERS
1196 THEN
1197 ROLLBACK TO create_comp_plan;
1198 x_loading_status := 'UNEXPECTED_ERR';
1199 x_return_status := fnd_api.g_ret_sts_unexp_error;
1200
1201 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1202 THEN
1203 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1204 END IF;
1205
1206 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1207 p_data => x_msg_data,
1208 p_encoded => fnd_api.g_false
1209 );
1210 END create_comp_plan;
1211 END cn_comp_plan_pub;