[Home] [Help]
PACKAGE BODY: APPS.CN_ROLE_PLANS_PUB
Source
1 PACKAGE BODY cn_role_plans_pub AS
2 /* $Header: cnprlplb.pls 120.11 2007/07/26 01:10:20 appldev ship $ */
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CN_ROLE_PLANS_PUB';
4 g_file_name CONSTANT VARCHAR2 (12) := 'cnprlplb.pls';
5 g_last_update_date DATE := SYSDATE;
6 g_last_updated_by NUMBER := fnd_global.user_id;
7 g_creation_date DATE := SYSDATE;
8 g_created_by NUMBER := fnd_global.user_id;
9 g_last_update_login NUMBER := fnd_global.login_id;
10 g_miss_job_title NUMBER := -99;
11 g_rowid VARCHAR2 (15);
12 g_program_type VARCHAR2 (30);
13
14
15 PROCEDURE business_event(
16 p_operation IN VARCHAR2,
17 p_pre_or_post IN VARCHAR2,
18 p_role_plan_id IN cn_role_plans.role_plan_id%TYPE,
19 p_role_plan_rec IN role_plan_rec_type
20 ) IS
21
22 l_key VARCHAR2(80);
23 l_event_name VARCHAR2(80);
24 l_list wf_parameter_list_t;
25
26 BEGIN
27
28 -- p_operation = Add, Update, Remove
29 l_event_name := 'oracle.apps.cn.events.setup.roleplans.' || p_operation || '.' || p_pre_or_post;
30
31 --Get the item key
32 l_key := l_event_name || '-' || p_role_plan_id;
33
34 -- build parameter list as appropriate
35 IF (p_operation = 'create') THEN
36 wf_event.AddParameterToList('COMP_PLAN_ID',p_role_plan_rec.comp_plan_id,l_list);
37 wf_event.AddParameterToList('ROLE_ID',p_role_plan_rec.role_id,l_list);
38 wf_event.AddParameterToList('START_DATE',p_role_plan_rec.start_date,l_list);
39 wf_event.AddParameterToList('END_DATE',p_role_plan_rec.end_date,l_list);
40
41 ELSIF (p_operation = 'update') THEN
42 l_key := l_key || '-' || p_role_plan_rec.object_version_number;
43
44 wf_event.AddParameterToList('COMP_PLAN_ID',p_role_plan_rec.comp_plan_id,l_list);
45 wf_event.AddParameterToList('ROLE_ID',p_role_plan_rec.role_id,l_list);
46 wf_event.AddParameterToList('START_DATE',p_role_plan_rec.start_date,l_list);
47 wf_event.AddParameterToList('END_DATE',p_role_plan_rec.end_date,l_list);
48
49 ELSIF (p_operation = 'delete') THEN
53 wf_event.AddParameterToList('END_DATE',p_role_plan_rec.end_date,l_list);
50 wf_event.AddParameterToList('COMP_PLAN_ID',p_role_plan_rec.comp_plan_id,l_list);
51 wf_event.AddParameterToList('ROLE_ID',p_role_plan_rec.role_id,l_list);
52 wf_event.AddParameterToList('START_DATE',p_role_plan_rec.start_date,l_list);
54 END IF;
55
56 -- Raise Event
57 wf_event.raise
58 (p_event_name => l_event_name,
59 p_event_key => l_key,
60 p_parameters => l_list);
61
62 l_list.DELETE;
63
64 END business_event;
65
66
67 -- ----------------------------------------------------------------------------*
68 -- Function : valid_role_name
69 -- Desc : check if the role_name exists in cn_roles
70 -- ---------------------------------------------------------------------------*
71 FUNCTION valid_role_name (p_role_name cn_roles.NAME%TYPE)
72 RETURN BOOLEAN
73 IS
74 CURSOR l_cur (l_role_name cn_roles.NAME%TYPE)
75 IS
76 SELECT *
77 FROM cn_roles
78 WHERE NAME = l_role_name;
79
80 l_rec l_cur%ROWTYPE;
81 BEGIN
82 OPEN l_cur (p_role_name);
83
84 FETCH l_cur
85 INTO l_rec;
86
87 IF (l_cur%NOTFOUND)
88 THEN
89 CLOSE l_cur;
90
91 RETURN FALSE;
92 ELSE
93 CLOSE l_cur;
94
95 RETURN TRUE;
96 END IF;
97 END valid_role_name;
98
99 -- ----------------------------------------------------------------------------*
100 -- Function : valid_comp_plan_name
101 -- Desc : check if the comp_plan_name exists in cn_comp_plans
102 -- ---------------------------------------------------------------------------*
103 FUNCTION valid_comp_plan_name (
104 p_comp_plan_name cn_comp_plans.NAME%TYPE,
105 p_org_id cn_comp_plans.org_id%TYPE
106 )
107 RETURN BOOLEAN
108 IS
109 CURSOR l_cur (
110 l_comp_plan_name cn_comp_plans.NAME%TYPE,
111 l_org_id cn_comp_plans.org_id%TYPE
112 )
113 IS
114 SELECT *
115 FROM cn_comp_plans
116 WHERE NAME = l_comp_plan_name AND org_id = l_org_id;
117
118 l_rec l_cur%ROWTYPE;
119 BEGIN
120 OPEN l_cur (p_comp_plan_name, p_org_id);
121
122 FETCH l_cur
123 INTO l_rec;
124
125 IF (l_cur%NOTFOUND)
126 THEN
127 CLOSE l_cur;
128
129 RETURN FALSE;
130 ELSE
131 CLOSE l_cur;
132
133 RETURN TRUE;
134 END IF;
135 END valid_comp_plan_name;
136
137 -- ----------------------------------------------------------------------------*
138 -- Function : valid_role_plan_id
139 -- Desc : check if the role_plan_id exists in cn_roles
140 -- ---------------------------------------------------------------------------*
141 FUNCTION valid_role_plan_id (
142 p_role_plan_id cn_role_plans.role_plan_id%TYPE
143 )
144 RETURN BOOLEAN
145 IS
146 CURSOR l_cur (l_role_plan_id cn_role_plans.role_plan_id%TYPE)
147 IS
148 SELECT *
149 FROM cn_role_plans
150 WHERE role_plan_id = l_role_plan_id;
151
152 l_rec l_cur%ROWTYPE;
153 BEGIN
154 OPEN l_cur (p_role_plan_id);
155
156 FETCH l_cur
157 INTO l_rec;
158
159 IF (l_cur%NOTFOUND)
160 THEN
161 CLOSE l_cur;
162
163 RETURN FALSE;
164 ELSE
165 CLOSE l_cur;
166
167 RETURN TRUE;
168 END IF;
169 END valid_role_plan_id;
170
171 -- ----------------------------------------------------------------------------*
172 -- Function : is_exist
173 -- Desc : check if the role_plan_id exists in cn_role_plans
174 -- ---------------------------------------------------------------------------*
175 FUNCTION is_exist (p_role_plan_id cn_role_plans.role_plan_id%TYPE)
176 RETURN BOOLEAN
177 IS
178 CURSOR l_cur (l_role_plan_id cn_role_plans.role_plan_id%TYPE)
179 IS
180 SELECT *
181 FROM cn_role_plans
182 WHERE role_plan_id = l_role_plan_id;
183
184 l_rec l_cur%ROWTYPE;
185 BEGIN
186 OPEN l_cur (p_role_plan_id);
187
188 FETCH l_cur
189 INTO l_rec;
190
191 IF (l_cur%NOTFOUND)
192 THEN
193 CLOSE l_cur;
194
195 RETURN FALSE;
196 ELSE
197 CLOSE l_cur;
198
199 RETURN TRUE;
200 END IF;
201 END is_exist;
202
203 -- ----------------------------------------------------------------------------*
204 -- Procedure: check_valid_insert
205 -- Desc : check if the record is valid to insert into cn_role_plans
206 -- called in create_role_plan before inserting a role-plan
207 -- assignment
208 -- ----------------------------------------------------------------------------*
209 PROCEDURE check_valid_insert (
210 x_return_status OUT NOCOPY VARCHAR2,
211 x_msg_count OUT NOCOPY NUMBER,
212 x_msg_data OUT NOCOPY VARCHAR2,
213 p_role_plan_rec IN role_plan_rec_type,
214 x_role_id OUT NOCOPY cn_roles.role_id%TYPE,
215 x_comp_plan_id OUT NOCOPY cn_comp_plans.comp_plan_id%TYPE,
216 p_loading_status IN VARCHAR2,
217 x_loading_status OUT NOCOPY VARCHAR2
218 )
219 IS
223 CURSOR l_cur (
220 l_api_name CONSTANT VARCHAR2 (30) := 'check_valid_insert';
221
222 -- CHANTHON: Added ORG_ID as a param
224 l_role_id cn_roles.role_id%TYPE,
225 l_org_id cn_role_plans.org_id%TYPE
226 )
227 IS
228 SELECT start_date, end_date, comp_plan_id
229 FROM cn_role_plans
230 WHERE role_id = l_role_id AND org_id = l_org_id;
231
232 -- CHANTHON: Added the ORG_ID as a param
233 CURSOR l_cp_cur (
234 l_comp_plan_name cn_comp_plans.NAME%TYPE,
235 l_org_id cn_comp_plans.org_id%TYPE
236 )
237 IS
238 SELECT start_date, end_date
239 FROM cn_comp_plans
240 WHERE NAME = l_comp_plan_name AND org_id = l_org_id;
241
242 l_cp_rec l_cp_cur%ROWTYPE;
243 BEGIN
244 -- Initialize API return status to success
245 x_return_status := fnd_api.g_ret_sts_success;
246 x_loading_status := p_loading_status;
247
248 -- Start of API body
249
250 -- validate the following issues
251
252 -- role_name can not be missing or null
253 IF (cn_api.chk_miss_null_char_para
254 (p_char_para => p_role_plan_rec.role_name,
255 p_obj_name => g_role_name,
256 p_loading_status => x_loading_status,
257 x_loading_status => x_loading_status
258 ) = fnd_api.g_true
259 )
260 THEN
261 RAISE fnd_api.g_exc_error;
262 END IF;
263
264 -- comp_plan_name can not be missing or null
265 IF (cn_api.chk_miss_null_char_para
266 (p_char_para => p_role_plan_rec.comp_plan_name,
267 p_obj_name => g_cp_name,
268 p_loading_status => x_loading_status,
269 x_loading_status => x_loading_status
270 ) = fnd_api.g_true
271 )
272 THEN
273 RAISE fnd_api.g_exc_error;
274 END IF;
275
276 -- start_date can not be null
277 -- start_date can not be missing
278 -- start_date < end_date if end_date is null
279 IF ((cn_api.invalid_date_range
280 (p_start_date => p_role_plan_rec.start_date,
281 p_end_date => p_role_plan_rec.end_date,
282 p_end_date_nullable => fnd_api.g_true,
283 p_loading_status => x_loading_status,
284 x_loading_status => x_loading_status
285 )
286 ) = fnd_api.g_true
287 )
288 THEN
289 RAISE fnd_api.g_exc_error;
290 END IF;
291
292 -- role_name must exist in cn_roles
293 IF NOT valid_role_name (p_role_plan_rec.role_name)
294 THEN
295 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
296 THEN
297 fnd_message.set_name ('CN', 'CN_RL_ASGN_ROLE_NOT_EXIST');
298 fnd_message.set_token ('ROLE_NAME', p_role_plan_rec.role_name);
299 fnd_msg_pub.ADD;
300 END IF;
301
302 x_loading_status := 'CN_RL_ASGN_ROLE_NOT_EXIST';
303 RAISE fnd_api.g_exc_error;
304 ELSE
305 -- CHANTHON: ORG_ID need not be passed here
306 x_role_id := cn_api.get_role_id (p_role_plan_rec.role_name);
307 END IF;
308
309 -- comp_plan_name must exist in cn_comp_plans
310 IF NOT valid_comp_plan_name (p_role_plan_rec.comp_plan_name,
311 p_role_plan_rec.org_id
312 )
313 THEN
314 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
315 THEN
316 fnd_message.set_name ('CN', 'CN_RL_ASGN_CP_NOT_EXIST');
317 fnd_message.set_token ('COMP_PLAN',
318 p_role_plan_rec.comp_plan_name
319 );
320 fnd_msg_pub.ADD;
321 END IF;
322
323 x_loading_status := 'CN_RL_ASGN_CP_NOT_EXIST';
324 RAISE fnd_api.g_exc_error;
325 ELSE
326 x_comp_plan_id :=
327 cn_api.get_cp_id (p_role_plan_rec.comp_plan_name,
328 p_role_plan_rec.org_id
329 );
330 END IF;
331
332 -- (start_date, end_date) is within comp plan's (start_date, end_date)
333 OPEN l_cp_cur (p_role_plan_rec.comp_plan_name, p_role_plan_rec.org_id);
334
335 FETCH l_cp_cur
336 INTO l_cp_rec;
337
338 IF (l_cp_cur%NOTFOUND)
339 THEN
340 -- normally this won't happen as it has been valided previously
341 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
342 THEN
343 fnd_message.set_name ('CN', 'CN_RL_ASGN_CP_NOT_EXIST');
344 fnd_message.set_token ('COMP_PLAN',
345 p_role_plan_rec.comp_plan_name
346 );
347 fnd_msg_pub.ADD;
348 END IF;
349
350 x_loading_status := 'CN_RL_ASGN_CP_NOT_EXIST';
351
352 CLOSE l_cp_cur;
353
354 RAISE fnd_api.g_exc_error;
355 ELSE
356 IF NOT cn_api.date_range_within (p_role_plan_rec.start_date,
357 p_role_plan_rec.end_date,
361 THEN
358 l_cp_rec.start_date,
359 l_cp_rec.end_date
360 )
362 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
363 THEN
364 fnd_message.set_name ('CN', 'CN_RL_CP_DATE_RANGE_NOT_WITHIN');
365 fnd_message.set_token ('START_DATE',
366 p_role_plan_rec.start_date
367 );
368 fnd_message.set_token ('END_DATE', p_role_plan_rec.end_date);
369 fnd_message.set_token ('CP_START_DATE', l_cp_rec.start_date);
370 fnd_message.set_token ('CP_END_DATE', l_cp_rec.end_date);
371 fnd_message.set_token ('COMP_PLAN_NAME',
372 p_role_plan_rec.comp_plan_name
373 );
374 fnd_msg_pub.ADD;
375 END IF;
376
377 x_loading_status := 'CN_RL_CP_DATE_RANGE_NOT_WITHIN';
378
379 CLOSE l_cp_cur;
380
381 RAISE fnd_api.g_exc_error;
382 END IF;
383
384 CLOSE l_cp_cur;
385 END IF;
386
387 -- CHANTHON: Adding the Org_id in the cursor.
388 -- If existing any same role_id in cn_role_plans THEN
389 -- check no overlap and no gap
390 FOR l_rec IN l_cur (x_role_id, p_role_plan_rec.org_id)
391 LOOP
392 IF cn_api.date_range_overlap (l_rec.start_date,
393 l_rec.end_date,
394 p_role_plan_rec.start_date,
395 p_role_plan_rec.end_date
396 )
397 THEN
398 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
399 THEN
400 fnd_message.set_name ('CN', 'CN_RL_ROLE_PLAN_OVERLAP');
401 fnd_message.set_token ('COMP_PLAN_NAME',
402 cn_api.get_cp_name (l_rec.comp_plan_id)
403 );
404 fnd_message.set_token ('START_DATE', l_rec.start_date);
405 fnd_message.set_token ('END_DATE', l_rec.end_date);
406 fnd_msg_pub.ADD;
407 END IF;
408
409 x_loading_status := 'CN_RL_ROLE_PLAN_OVERLAP';
410 RAISE fnd_api.g_exc_error;
411 END IF;
412 END LOOP;
413
414 -- End of API body.
415
416 -- Standard call to get message count and if count is 1, get message info.
417 fnd_msg_pub.count_and_get (p_count => x_msg_count,
418 p_data => x_msg_data,
419 p_encoded => fnd_api.g_false
420 );
421 EXCEPTION
422 WHEN fnd_api.g_exc_error
423 THEN
424 x_return_status := fnd_api.g_ret_sts_error;
425 fnd_msg_pub.count_and_get (p_count => x_msg_count,
426 p_data => x_msg_data,
427 p_encoded => fnd_api.g_false
428 );
429 WHEN fnd_api.g_exc_unexpected_error
430 THEN
431 x_return_status := fnd_api.g_ret_sts_unexp_error;
432 x_loading_status := 'UNEXPECTED_ERR';
433 fnd_msg_pub.count_and_get (p_count => x_msg_count,
434 p_data => x_msg_data,
435 p_encoded => fnd_api.g_false
436 );
437 WHEN OTHERS
438 THEN
439 x_return_status := fnd_api.g_ret_sts_unexp_error;
440 x_loading_status := 'UNEXPECTED_ERR';
441
442 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
443 THEN
444 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
445 END IF;
446
447 fnd_msg_pub.count_and_get (p_count => x_msg_count,
448 p_data => x_msg_data,
449 p_encoded => fnd_api.g_false
450 );
451 END check_valid_insert;
452
453 -- ----------------------------------------------------------------------------*
454 -- Procedure: check_valid_update
455 -- Desc : check if the record is valid to update in cn_role_plans
456 -- called in update_role_plan before updating a role
457 -- ----------------------------------------------------------------------------*
458 PROCEDURE check_valid_update (
459 x_return_status OUT NOCOPY VARCHAR2,
460 x_msg_count OUT NOCOPY NUMBER,
461 x_msg_data OUT NOCOPY VARCHAR2,
462 p_role_plan_rec_old IN role_plan_rec_type,
463 p_role_plan_rec_new IN role_plan_rec_type,
464 x_role_plan_id_old OUT NOCOPY cn_role_plans.role_plan_id%TYPE,
465 x_role_id OUT NOCOPY cn_roles.role_id%TYPE,
466 x_comp_plan_id OUT NOCOPY cn_comp_plans.comp_plan_id%TYPE,
467 x_date_update_only OUT NOCOPY VARCHAR2,
468 p_loading_status IN VARCHAR2,
469 x_loading_status OUT NOCOPY VARCHAR2
470 )
471 IS
472 l_api_name CONSTANT VARCHAR2 (30) := 'check_valid_update';
473 tmp_start_date cn_role_plans.start_date%TYPE;
474 tmp_end_date cn_role_plans.end_date%TYPE;
475
476 -- CHANTHON: Adding ORG_ID.. This cursor is used to check for whether the
477 -- role id already exists for another role_plan_id. If it does then the date overlap is
478 -- being checked. So org_id is required.
479 CURSOR l_cur (
483 )
480 l_role_id cn_role_plans.role_id%TYPE,
481 l_role_plan_id cn_role_plans.role_plan_id%TYPE,
482 l_org_id cn_role_plans.org_id%TYPE
484 IS
485 SELECT start_date, end_date, comp_plan_id
486 FROM cn_role_plans
487 WHERE role_id = l_role_id
488 AND org_id = l_org_id
489 AND role_plan_id <> l_role_plan_id;
490
491 -- CHANTHON: Id based so ORG_ID not required
492 CURSOR l_old_cur (l_role_plan_id cn_role_plans.role_plan_id%TYPE)
493 IS
494 SELECT *
495 FROM cn_role_plans
496 WHERE role_plan_id = l_role_plan_id;
497
498 l_old_rec l_old_cur%ROWTYPE;
499
500 -- CHANTHON: Id based so ORG_ID not required
501 CURSOR l_cp_cur (l_comp_plan_id cn_comp_plans.comp_plan_id%TYPE)
502 IS
503 SELECT start_date, end_date
504 FROM cn_comp_plans
505 WHERE comp_plan_id = l_comp_plan_id;
506
507 l_cp_rec l_cp_cur%ROWTYPE;
508 BEGIN
509 -- Initialize API return status to success
510 x_return_status := fnd_api.g_ret_sts_success;
511 x_loading_status := p_loading_status;
512 -- Start of API body
513
514 -- validate the following issues
515
516 -- old role_plan_id must exist in cn_role_plans
517 x_role_plan_id_old :=
518 cn_api.get_role_plan_id (p_role_plan_rec_old.role_name,
519 p_role_plan_rec_old.comp_plan_name,
520 p_role_plan_rec_old.start_date,
521 p_role_plan_rec_old.end_date,
522 p_role_plan_rec_old.org_id
523 );
524
525 IF (x_role_plan_id_old IS NULL)
526 THEN
527 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
528 THEN
529 fnd_message.set_name ('CN', 'CN_RL_UPD_ROLE_PLAN_NOT_EXIST');
530 fnd_msg_pub.ADD;
531 END IF;
532
533 x_loading_status := 'CN_RL_UPD_ROLE_PLAN_NOT_EXIST';
534 RAISE fnd_api.g_exc_error;
535 END IF;
536
537 -- new role_name can not be null
538 -- note that new role_name can be missing
539 IF (cn_api.chk_null_char_para
540 (p_char_para => p_role_plan_rec_new.role_name,
541 p_obj_name => g_role_name,
542 p_loading_status => x_loading_status,
543 x_loading_status => x_loading_status
544 ) = fnd_api.g_true
545 )
546 THEN
547 RAISE fnd_api.g_exc_error;
548 END IF;
549
550 -- new comp_plan_name can not be null
551 -- note that new comp_plan_name can be missing
552 IF (cn_api.chk_null_char_para
553 (p_char_para => p_role_plan_rec_new.comp_plan_name,
554 p_obj_name => g_cp_name,
555 p_loading_status => x_loading_status,
556 x_loading_status => x_loading_status
557 ) = fnd_api.g_true
558 )
559 THEN
560 RAISE fnd_api.g_exc_error;
561 END IF;
562
563 -- new start_date can not be null
564 -- note that new start_date can be missing
565 IF (cn_api.chk_null_date_para
566 (p_date_para => p_role_plan_rec_new.start_date,
567 p_obj_name => g_start_date,
568 p_loading_status => x_loading_status,
569 x_loading_status => x_loading_status
570 ) = fnd_api.g_true
571 )
572 THEN
573 RAISE fnd_api.g_exc_error;
574 END IF;
575
576 -- if new start_date is missing then
577 -- tmp_start_date := old start_date
578 -- else
579 -- tmp_start_date := new start_date
580 -- end if
581
582 -- if new end_date is missing then
583 -- tmp_end_date := old end_date
584 -- else
585 -- tmp_end_date := new end_date
586 -- end if
587
588 -- check tmp_start_date < tmp_end_date if tmp_end_date is not null
589 OPEN l_old_cur (x_role_plan_id_old);
590
591 FETCH l_old_cur
592 INTO l_old_rec;
593
594 IF (l_old_cur%NOTFOUND)
595 THEN
596 -- normally, this should not happen as the existance has
597 -- been validated previously
598 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
599 THEN
600 fnd_message.set_name ('CN', 'CN_RL_UPD_ROLE_PLAN_NOT_EXIST');
601 fnd_msg_pub.ADD;
602 END IF;
603
604 x_loading_status := 'CN_RL_UPD_ROLE_PLAN_NOT_EXIST';
605
606 CLOSE l_old_cur;
607
608 RAISE fnd_api.g_exc_error;
609 ELSE
610 IF (p_role_plan_rec_new.start_date = fnd_api.g_miss_date)
611 THEN
612 tmp_start_date := l_old_rec.start_date;
613 ELSE
614 tmp_start_date := p_role_plan_rec_new.start_date;
615 END IF;
616
617 IF (p_role_plan_rec_new.end_date = fnd_api.g_miss_date)
618 THEN
619 tmp_end_date := l_old_rec.end_date;
620 ELSE
621 tmp_end_date := p_role_plan_rec_new.end_date;
622 END IF;
623
627 THEN
624 IF (tmp_end_date IS NOT NULL) AND (tmp_start_date > tmp_end_date)
625 THEN
626 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
628 fnd_message.set_name ('CN', 'CN_RL_INVALID_DATE_RANGE');
629 fnd_message.set_token ('START_DATE', tmp_start_date);
630 fnd_message.set_token ('END_DATE', tmp_end_date);
631 fnd_msg_pub.ADD;
632 END IF;
633
634 x_loading_status := 'CN_RL_INVALID_DATE_RANGE';
635
636 CLOSE l_old_cur;
637
638 RAISE fnd_api.g_exc_error;
639 END IF;
640
641 CLOSE l_old_cur;
642 END IF;
643
644 -- make sure the create_module is OSC if we are trying to change it within
645 -- OSC. if it were created in SFP (from a generic push) and we change it
646 -- in OSC then it would get out of sync with SFP (we would need a "pull"
647 -- operation to get it back in sync). the user needs to make the necessary
648 -- changes in SFP and reapply the push.
649 IF NVL (l_old_rec.create_module, 'OSC') = 'SFP'
650 THEN
651 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
652 THEN
653 fnd_message.set_name ('CN', 'CN_RP_CREATED_IN_SFP');
654 fnd_msg_pub.ADD;
655 END IF;
656
657 x_loading_status := 'CN_RP_CREATED_IN_SFP';
658 RAISE fnd_api.g_exc_error;
659 END IF;
660
661 -- if new role_name is not missing then new role_name must exist in cn_roles
662 IF (p_role_plan_rec_new.role_name <> fnd_api.g_miss_char)
663 THEN
664 x_role_id := cn_api.get_role_id (p_role_plan_rec_new.role_name);
665
666 IF (x_role_id IS NULL)
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_RL_ASGN_ROLE_NOT_EXIST');
671 fnd_message.set_token ('ROLE_NAME',
672 p_role_plan_rec_new.role_name
673 );
674 fnd_msg_pub.ADD;
675 END IF;
676
677 x_loading_status := 'CN_RL_ASGN_ROLE_NOT_EXIST';
678 RAISE fnd_api.g_exc_error;
679 END IF;
680 ELSE
681 OPEN l_old_cur (x_role_plan_id_old);
682
683 FETCH l_old_cur
684 INTO l_old_rec;
685
686 IF (l_old_cur%NOTFOUND)
687 THEN
688 -- normally, this should not happen as the existance has
689 -- been validated previously
690 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
691 THEN
692 fnd_message.set_name ('CN', 'CN_RL_UPD_ROLE_PLAN_NOT_EXIST');
693 fnd_msg_pub.ADD;
694 END IF;
695
696 x_loading_status := 'CN_RL_UPD_ROLE_PLAN_NOT_EXIST';
697
698 CLOSE l_old_cur;
699
700 RAISE fnd_api.g_exc_error;
701 ELSE
702 x_role_id := l_old_rec.role_id;
703
704 CLOSE l_old_cur;
705 END IF;
706 END IF;
707
708 -- if new comp_plan_name is not missing then
709 -- new comp_plan_name must exist in cn_comp_plans
710 IF (p_role_plan_rec_new.comp_plan_name <> fnd_api.g_miss_char)
711 THEN
712 x_comp_plan_id :=
713 cn_api.get_cp_id (p_role_plan_rec_new.comp_plan_name,
714 p_role_plan_rec_new.org_id
715 );
716
717 IF (x_comp_plan_id IS NULL)
718 THEN
719 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
720 THEN
721 fnd_message.set_name ('CN', 'CN_RL_ASGN_CP_NOT_EXIST');
722 fnd_message.set_token ('COMP_PLAN',
723 p_role_plan_rec_new.comp_plan_name
724 );
725 fnd_msg_pub.ADD;
726 END IF;
727
728 x_loading_status := 'CN_RL_ASGN_CP_NOT_EXIST';
729 RAISE fnd_api.g_exc_error;
730 END IF;
731 ELSE
732 OPEN l_old_cur (x_role_plan_id_old);
733
734 FETCH l_old_cur
735 INTO l_old_rec;
736
737 IF (l_old_cur%NOTFOUND)
738 THEN
739 -- normally, this should not happen as the existance has
740 -- been validated previously
741 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
742 THEN
743 fnd_message.set_name ('CN', 'CN_RL_UPD_ROLE_PLAN_NOT_EXIST');
744 fnd_msg_pub.ADD;
745 END IF;
746
747 x_loading_status := 'CN_RL_UPD_ROLE_PLAN_NOT_EXIST';
748
749 CLOSE l_old_cur;
750
751 RAISE fnd_api.g_exc_error;
752 ELSE
753 x_comp_plan_id := l_old_rec.comp_plan_id;
754
755 CLOSE l_old_cur;
756 END IF;
757 END IF;
758
759 -- (start_date, end_date) is within comp plan's (start_date, end_date)
760 OPEN l_cp_cur (x_comp_plan_id);
761
762 FETCH l_cp_cur
763 INTO l_cp_rec;
764
765 IF (l_cp_cur%NOTFOUND)
766 THEN
767 -- normally this won't happen as it has been valided previously
768 x_loading_status := 'CN_RL_ASGN_CP_NOT_EXIST';
769
770 CLOSE l_cp_cur;
771
772 RAISE fnd_api.g_exc_error;
773 ELSE
774 IF NOT cn_api.date_range_within (tmp_start_date,
778 )
775 tmp_end_date,
776 l_cp_rec.start_date,
777 l_cp_rec.end_date
779 THEN
780 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
781 THEN
782 fnd_message.set_name ('CN', 'CN_RL_CP_DATE_RANGE_NOT_WITHIN');
783 fnd_message.set_token ('START_DATE', tmp_start_date);
784 fnd_message.set_token ('END_DATE', tmp_end_date);
785 fnd_message.set_token ('CP_START_DATE', l_cp_rec.start_date);
786 fnd_message.set_token ('CP_END_DATE', l_cp_rec.end_date);
787 fnd_message.set_token ('COMP_PLAN_NAME',
788 cn_api.get_cp_name (x_comp_plan_id)
789 );
790 fnd_msg_pub.ADD;
791 END IF;
792
793 x_loading_status := 'CN_RL_CP_DATE_RANGE_NOT_WITHIN';
794
795 CLOSE l_cp_cur;
796
797 RAISE fnd_api.g_exc_error;
798 END IF;
799
800 CLOSE l_cp_cur;
801 END IF;
802
803 --CHANTHON: Adding the org_id param in the cursor. Should be the org_id of the updated record.
804 -- If existing any same role_id in cn_role_plans THEN
805 -- check no overlap
806 FOR l_rec IN l_cur (x_role_id,
807 x_role_plan_id_old,
808 p_role_plan_rec_new.org_id
809 )
810 LOOP
811 IF cn_api.date_range_overlap (l_rec.start_date,
812 l_rec.end_date,
813 tmp_start_date,
814 tmp_end_date
815 )
816 THEN
817 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
818 THEN
819 fnd_message.set_name ('CN', 'CN_RL_ROLE_PLAN_OVERLAP');
820 fnd_message.set_token ('COMP_PLAN_NAME',
821 cn_api.get_cp_name (l_rec.comp_plan_id)
822 );
823 fnd_message.set_token ('START_DATE', l_rec.start_date);
824 fnd_message.set_token ('END_DATE', l_rec.end_date);
825 fnd_msg_pub.ADD;
826 END IF;
827
828 x_loading_status := 'CN_RL_ROLE_PLAN_OVERLAP';
829 RAISE fnd_api.g_exc_error;
830 END IF;
831 END LOOP;
832
833 -- Checking if it is date_update_only
834 OPEN l_old_cur (x_role_plan_id_old);
835
836 FETCH l_old_cur
837 INTO l_old_rec;
838
839 IF (l_old_cur%NOTFOUND)
840 THEN
841 -- normally, this should not happen as the existence has
842 -- been validated previously
843 x_loading_status := 'CN_RL_UPD_ROLE_PLAN_NOT_EXIST';
844
845 CLOSE l_old_cur;
846
847 RAISE fnd_api.g_exc_error;
848 ELSE
849 IF ( (x_role_id <> l_old_rec.role_id)
850 OR (x_comp_plan_id <> l_old_rec.comp_plan_id)
851 )
852 THEN
853 x_date_update_only := fnd_api.g_false;
854 ELSE
855 x_date_update_only := fnd_api.g_true;
856 END IF;
857
858 CLOSE l_old_cur;
859 END IF;
860
861 -- End of API body.
862
863 -- Standard call to get message count and if count is 1, get message info.
864 fnd_msg_pub.count_and_get (p_count => x_msg_count,
865 p_data => x_msg_data,
866 p_encoded => fnd_api.g_false
867 );
868 EXCEPTION
869 WHEN fnd_api.g_exc_error
870 THEN
871 x_return_status := fnd_api.g_ret_sts_error;
872 fnd_msg_pub.count_and_get (p_count => x_msg_count,
873 p_data => x_msg_data,
874 p_encoded => fnd_api.g_false
875 );
876 WHEN fnd_api.g_exc_unexpected_error
877 THEN
878 x_return_status := fnd_api.g_ret_sts_unexp_error;
879 x_loading_status := 'UNEXPECTED_ERR';
880 fnd_msg_pub.count_and_get (p_count => x_msg_count,
881 p_data => x_msg_data,
882 p_encoded => fnd_api.g_false
883 );
884 WHEN OTHERS
885 THEN
886 x_return_status := fnd_api.g_ret_sts_unexp_error;
887 x_loading_status := 'UNEXPECTED_ERR';
888
889 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
890 THEN
891 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
892 END IF;
893
894 fnd_msg_pub.count_and_get (p_count => x_msg_count,
895 p_data => x_msg_data,
896 p_encoded => fnd_api.g_false
897 );
898 END check_valid_update;
899
900 -- ----------------------------------------------------------------------------*
901 -- Procedure: check_valid_delete
902 -- Desc : check if the record is valid to delete from cn_role_plans
903 -- called in delete_role_plan before deleting a role
904 -- ----------------------------------------------------------------------------*
905 PROCEDURE check_valid_delete (
906 x_return_status OUT NOCOPY VARCHAR2,
907 x_msg_count OUT NOCOPY NUMBER,
911 p_loading_status IN VARCHAR2,
908 x_msg_data OUT NOCOPY VARCHAR2,
909 p_role_plan_rec IN role_plan_rec_type,
910 x_role_plan_id OUT NOCOPY NUMBER,
912 x_loading_status OUT NOCOPY VARCHAR2
913 )
914 IS
915 l_api_name CONSTANT VARCHAR2 (30) := 'check_valid_delete';
916 BEGIN
917 -- Initialize API return status to success
918 x_return_status := fnd_api.g_ret_sts_success;
919 x_loading_status := p_loading_status;
920 -- Start of API body
921
922 -- Valide the following issues
923
924 -- Checke if the p_role_plan_id does exist.
925 x_role_plan_id :=
926 cn_api.get_role_plan_id (p_role_plan_rec.role_name,
927 p_role_plan_rec.comp_plan_name,
928 p_role_plan_rec.start_date,
929 p_role_plan_rec.end_date,
930 p_role_plan_rec.org_id
931 );
932
933 IF (x_role_plan_id IS NULL)
934 THEN
935 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
936 THEN
937 fnd_message.set_name ('CN', 'CN_RL_DEL_ROLE_PLAN_NOT_EXIST');
938 fnd_msg_pub.ADD;
939 END IF;
940
941 x_loading_status := 'CN_RL_DEL_ROLE_PLAN_NOT_EXIST';
942 RAISE fnd_api.g_exc_error;
943 END IF;
944
945 -- End of API body.
946
947 -- Standard call to get message count and if count is 1, get message info.
948 fnd_msg_pub.count_and_get (p_count => x_msg_count,
949 p_data => x_msg_data,
950 p_encoded => fnd_api.g_false
951 );
952 EXCEPTION
953 WHEN fnd_api.g_exc_error
954 THEN
955 x_return_status := fnd_api.g_ret_sts_error;
956 fnd_msg_pub.count_and_get (p_count => x_msg_count,
957 p_data => x_msg_data,
958 p_encoded => fnd_api.g_false
959 );
960 WHEN fnd_api.g_exc_unexpected_error
961 THEN
962 x_return_status := fnd_api.g_ret_sts_unexp_error;
963 x_loading_status := 'UNEXPECTED_ERR';
964 fnd_msg_pub.count_and_get (p_count => x_msg_count,
965 p_data => x_msg_data,
966 p_encoded => fnd_api.g_false
967 );
968 WHEN OTHERS
969 THEN
970 x_return_status := fnd_api.g_ret_sts_unexp_error;
971 x_loading_status := 'UNEXPECTED_ERR';
972
973 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
974 THEN
975 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
976 END IF;
977
978 fnd_msg_pub.count_and_get (p_count => x_msg_count,
979 p_data => x_msg_data,
980 p_encoded => fnd_api.g_false
981 );
982 END check_valid_delete;
983
984 -- --------------------------------------------------------------------------*
985 -- Procedure: srp_plan_assignment_for_insert
986 -- --------------------------------------------------------------------------*
987 PROCEDURE srp_plan_assignment_for_insert (
988 p_role_id IN cn_roles.role_id%TYPE,
989 p_role_plan_id IN cn_role_plans.role_plan_id%TYPE,
990 x_return_status OUT NOCOPY VARCHAR2,
991 p_loading_status IN VARCHAR2,
992 x_loading_status OUT NOCOPY VARCHAR2,
993 p_org_id IN cn_role_plans.org_id%TYPE
994 )
995 IS
996 /* CURSOR l_cur IS
997 select sr.srp_role_id srp_role_id,
998 nvl(srd.job_title_id, G_MISS_JOB_TITLE) job_title_id,
999 nvl(srd.plan_activate_status, 'NOT_PUSHED') push_status
1000 from cn_srp_roles sr,
1001 cn_srp_role_dtls srd
1002 where role_id = p_role_id
1003 and srd.role_model_id is NULL
1004 -- CHANGED FOR MODELING IMPACT
1005 and sr.srp_role_id = srd.srp_role_id(+);*/
1006
1007 --CHANTHON:Added org id in the cursor
1008 CURSOR l_cur
1009 IS
1010 SELECT srp_role_id
1011 FROM cn_srp_roles
1012 WHERE role_id = p_role_id AND org_id = p_org_id;
1013
1014 l_rec l_cur%ROWTYPE;
1015 l_return_status VARCHAR2 (2000);
1016 l_msg_count NUMBER;
1017 l_msg_data VARCHAR2 (2000);
1018 l_srp_plan_assign_id cn_srp_plan_assigns.srp_plan_assign_id%TYPE;
1019 l_loading_status VARCHAR2 (2000);
1020 BEGIN
1021 -- Initialize API return status to success
1022 x_return_status := fnd_api.g_ret_sts_success;
1023 x_loading_status := p_loading_status;
1024
1025 FOR l_rec IN l_cur
1026 LOOP
1027 -- see here if it is necessary to insert into cn_srp_plan_assigns
1028 -- the create_module here is OSC.
1029 -- if the job title not assigned yet (original OSC case) or
1030 -- status is PUSHED (salesrep push done, treat as OSC record), then
1031 -- call SPA.insert
1032 -- if l_rec.job_title_id = G_MISS_JOB_TITLE OR
1033 -- l_rec.push_status = 'PUSHED' THEN
1034
1035 -- dbms_output.put_line('insert into cn_srp_plan_assigns...');
1039 (p_api_version => 1.0,
1036 -- dbms_output.put_line('p_srp_role_id = ' || l_rec.srp_role_id);
1037 -- dbms_output.put_line('p_role_plan_id = ' || p_role_plan_id);
1038 cn_srp_plan_assigns_pvt.create_srp_plan_assigns
1040 x_return_status => l_return_status,
1041 x_msg_count => l_msg_count,
1042 x_msg_data => l_msg_data,
1043 p_srp_role_id => l_rec.srp_role_id,
1044 p_role_plan_id => p_role_plan_id,
1045 x_srp_plan_assign_id => l_srp_plan_assign_id,
1046 x_loading_status => l_loading_status
1047 );
1048
1049 IF (l_return_status <> fnd_api.g_ret_sts_success)
1050 THEN
1051 x_return_status := l_return_status;
1052 x_loading_status := l_loading_status;
1053 EXIT;
1054 END IF;
1055 -- end if;
1056 END LOOP;
1057 END srp_plan_assignment_for_insert;
1058
1059 -- --------------------------------------------------------------------------*
1060 -- Procedure: srp_plan_assignment_for_update
1061 -- --------------------------------------------------------------------------*
1062 PROCEDURE srp_plan_assignment_for_update (
1063 p_role_id IN cn_roles.role_id%TYPE,
1064 p_role_id_old IN cn_roles.role_id%TYPE,
1065 p_role_plan_id IN cn_role_plans.role_plan_id%TYPE,
1066 p_date_update_only IN VARCHAR2,
1067 x_return_status OUT NOCOPY VARCHAR2,
1068 p_loading_status IN VARCHAR2,
1069 x_loading_status OUT NOCOPY VARCHAR2,
1070 p_org_id IN cn_role_plans.org_id%TYPE,
1071 p_org_id_old IN cn_role_plans.org_id%TYPE
1072 )
1073 IS
1074 /* CURSOR l_cur IS
1075 select sr.srp_role_id srp_role_id,
1076 nvl(srd.job_title_id, G_MISS_JOB_TITLE) job_title_id,
1077 nvl(srd.plan_activate_status, 'NOT_PUSHED') push_status
1078 from cn_srp_roles sr,
1079 cn_srp_role_dtls srd
1080 where role_id = p_role_id
1081 and srd.role_model_id is NULL
1082 -- CHANGED FOR MODELING IMPACT
1083 and sr.srp_role_id = srd.srp_role_id(+);*/
1084 CURSOR l_cur
1085 IS
1086 SELECT srp_role_id
1087 FROM cn_srp_roles
1088 WHERE role_id = p_role_id AND org_id = p_org_id;
1089
1090 CURSOR l_cur_del
1091 IS
1092 SELECT srp_role_id
1093 FROM cn_srp_roles
1094 WHERE role_id = p_role_id_old AND org_id = p_org_id_old;
1095
1096 l_rec l_cur%ROWTYPE;
1097 l_rec_del l_cur_del%ROWTYPE;
1098 l_return_status VARCHAR2 (2000);
1099 l_msg_count NUMBER;
1100 l_msg_data VARCHAR2 (2000);
1101 l_srp_plan_assign_id cn_srp_plan_assigns.srp_plan_assign_id%TYPE;
1102 l_loading_status VARCHAR2 (2000);
1103 BEGIN
1104 -- Initialize API return status to success
1105 x_return_status := fnd_api.g_ret_sts_success;
1106 x_loading_status := p_loading_status;
1107
1108 -- see here if it is necessary to update cn_srp_plan_assigns...
1109 -- the create_module here is OSC.
1110 -- if the job title not assigned yet (original OSC case) or
1111 -- status is PUSHED (salesrep push done, treat as OSC record), then
1112 -- call SPA.update
1113 IF (p_date_update_only = fnd_api.g_true)
1114 THEN
1115 FOR l_rec IN l_cur
1116 LOOP
1117 -- if l_rec.job_title_id = G_MISS_JOB_TITLE OR
1118 -- l_rec.push_status = 'PUSHED' THEN
1119 cn_srp_plan_assigns_pvt.update_srp_plan_assigns
1120 (p_api_version => 1.0,
1121 x_return_status => l_return_status,
1122 x_msg_count => l_msg_count,
1123 x_msg_data => l_msg_data,
1124 p_srp_role_id => l_rec.srp_role_id,
1125 p_role_plan_id => p_role_plan_id,
1126 x_loading_status => l_loading_status
1127 );
1128
1129 IF (l_return_status <> fnd_api.g_ret_sts_success)
1130 THEN
1131 x_return_status := l_return_status;
1132 x_loading_status := l_loading_status;
1133 EXIT;
1134 END IF;
1135 -- end if;
1136 END LOOP;
1137 ELSE
1138 --CHANTHON: Updating the srp records. delete all the old records and
1139 --create new srp records.
1140 FOR l_rec_del IN l_cur_del
1141 LOOP
1142 -- if l_rec.job_title_id = G_MISS_JOB_TITLE OR
1143 -- l_rec.push_status = 'PUSHED' THEN
1144 cn_srp_plan_assigns_pvt.delete_srp_plan_assigns
1145 (p_api_version => 1.0,
1146 x_return_status => l_return_status,
1147 x_msg_count => l_msg_count,
1148 x_msg_data => l_msg_data,
1149 p_srp_role_id => l_rec_del.srp_role_id,
1150 p_role_plan_id => p_role_plan_id,
1154 IF (l_return_status <> fnd_api.g_ret_sts_success)
1151 x_loading_status => l_loading_status
1152 );
1153
1155 THEN
1156 x_return_status := l_return_status;
1157 x_loading_status := l_loading_status;
1158 EXIT;
1159 END IF;
1160 END LOOP;
1161
1162 FOR l_rec IN l_cur
1163 LOOP
1164 cn_srp_plan_assigns_pvt.create_srp_plan_assigns
1165 (p_api_version => 1.0,
1166 x_return_status => l_return_status,
1167 x_msg_count => l_msg_count,
1168 x_msg_data => l_msg_data,
1169 p_srp_role_id => l_rec.srp_role_id,
1170 p_role_plan_id => p_role_plan_id,
1171 x_srp_plan_assign_id => l_srp_plan_assign_id,
1172 x_loading_status => l_loading_status
1173 );
1174
1175 IF (l_return_status <> fnd_api.g_ret_sts_success)
1176 THEN
1177 x_return_status := l_return_status;
1178 x_loading_status := l_loading_status;
1179 EXIT;
1180 END IF;
1181 -- end if;
1182 END LOOP;
1183 END IF;
1184 END srp_plan_assignment_for_update;
1185
1186 -- --------------------------------------------------------------------------*
1187 -- Procedure: srp_plan_assignment_for_delete
1188 -- --------------------------------------------------------------------------*
1189 PROCEDURE srp_plan_assignment_for_delete (
1190 p_role_id IN cn_roles.role_id%TYPE,
1191 p_role_plan_id IN cn_role_plans.role_plan_id%TYPE,
1192 x_return_status OUT NOCOPY VARCHAR2,
1193 p_loading_status IN VARCHAR2,
1194 x_loading_status OUT NOCOPY VARCHAR2,
1195 p_org_id IN cn_role_plans.org_id%TYPE
1196 )
1197 IS
1198 CURSOR l_cur
1199 IS
1200 SELECT srp_role_id
1201 FROM cn_srp_roles
1202 WHERE role_id = p_role_id AND org_id = p_org_id;
1203
1204 l_rec l_cur%ROWTYPE;
1205 l_return_status VARCHAR2 (2000);
1206 l_msg_count NUMBER;
1207 l_msg_data VARCHAR2 (2000);
1208 l_srp_plan_assign_id cn_srp_plan_assigns.srp_plan_assign_id%TYPE;
1209 l_loading_status VARCHAR2 (2000);
1210 BEGIN
1211 -- Initialize API return status to success
1212 x_return_status := fnd_api.g_ret_sts_success;
1213 x_loading_status := p_loading_status;
1214
1215 FOR l_rec IN l_cur
1216 LOOP
1217 cn_srp_plan_assigns_pvt.delete_srp_plan_assigns
1218 (p_api_version => 1.0,
1219 p_validation_level => fnd_api.g_valid_level_full,
1220 x_return_status => l_return_status,
1221 x_msg_count => l_msg_count,
1222 x_msg_data => l_msg_data,
1223 p_srp_role_id => l_rec.srp_role_id,
1224 p_role_plan_id => p_role_plan_id,
1225 x_loading_status => l_loading_status
1226 );
1227
1228 IF (l_return_status <> fnd_api.g_ret_sts_success)
1229 THEN
1230 x_return_status := l_return_status;
1231 x_loading_status := l_loading_status;
1232 EXIT;
1233 END IF;
1234 END LOOP;
1235 END srp_plan_assignment_for_delete;
1236
1237 -- --------------------------------------------------------------------------*
1238 -- Procedure: create_role_plan
1239 -- --------------------------------------------------------------------------*
1240 -- CHANTHON: Added the out params role plan id and obj ver num
1241 PROCEDURE create_role_plan (
1242 p_api_version IN NUMBER,
1243 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1244 p_commit IN VARCHAR2 := fnd_api.g_false,
1245 p_validation_level IN NUMBER
1246 := fnd_api.g_valid_level_full,
1247 x_return_status OUT NOCOPY VARCHAR2,
1248 x_loading_status OUT NOCOPY VARCHAR2,
1249 x_msg_count OUT NOCOPY NUMBER,
1250 x_msg_data OUT NOCOPY VARCHAR2,
1251 p_role_plan_rec IN role_plan_rec_type
1252 := g_miss_role_plan_rec,
1253 x_role_plan_id OUT NOCOPY NUMBER,
1254 x_obj_ver_num OUT NOCOPY NUMBER
1255 )
1256 IS
1257 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Role_Plan';
1258 l_api_version CONSTANT NUMBER := 1.0;
1259 l_role_plan_id cn_role_plans.role_plan_id%TYPE;
1260 l_role_id cn_roles.role_id%TYPE;
1261 l_comp_plan_id cn_comp_plans.comp_plan_id%TYPE;
1262 l_object_version_number cn_role_plans.object_version_number%TYPE := 1;
1263 -- Declaration for user hooks
1264 l_rec role_plan_rec_type;
1265 l_oai_array jtf_usr_hks.oai_data_array_type;
1266 l_bind_data_id NUMBER;
1267 l_org_id NUMBER;
1268 l_status VARCHAR2(1);
1269 l_note_msg VARCHAR2 (240);
1270 l_note_id NUMBER;
1271 BEGIN
1272 -- Standard Start of API savepoint
1276 IF NOT fnd_api.compatible_api_call (l_api_version,
1273 SAVEPOINT create_role_plan;
1274
1275 -- Standard call to check for call compatibility.
1277 p_api_version,
1278 l_api_name,
1279 g_pkg_name
1280 )
1281 THEN
1282 RAISE fnd_api.g_exc_unexpected_error;
1283 END IF;
1284
1285 -- Initialize message list if p_init_msg_list is set to TRUE.
1286 IF fnd_api.to_boolean (p_init_msg_list)
1287 THEN
1288 fnd_msg_pub.initialize;
1289 END IF;
1290
1291 -- Initialize API return status to success
1292 x_return_status := fnd_api.g_ret_sts_success;
1293 x_loading_status := 'CN_INSERTED';
1294
1295 -- START OF MOAC ORG_ID VALIDATION
1296
1297 l_org_id := p_role_plan_rec.org_id;
1298 mo_global.validate_orgid_pub_api(org_id => l_org_id,
1299 status => l_status);
1300
1301 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1302 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1303 'cn.plsql.cn_role_plans_pub.create_role_plan.org_validate',
1304 'Validated org_id = ' || l_org_id || ' status = '||l_status);
1305 end if;
1306 -- END OF MOAC ORG_ID VALIDATION
1307
1308 -- Assign the parameter to a local variable to be passed to Pre, Post
1309 -- and Business APIs
1310 l_rec := p_role_plan_rec;
1311
1312 -- CHANTHON: Added to get the comp plan name and role name when the ids are provided
1313 IF (l_rec.role_name IS NULL AND l_rec.comp_plan_name IS NULL)
1314 THEN
1315 l_rec.comp_plan_name :=
1316 cn_api.get_cp_name (p_role_plan_rec.comp_plan_id);
1317 l_rec.role_name := cn_api.get_role_name (p_role_plan_rec.role_id);
1318 END IF;
1319
1320 -- User hooks
1321
1322 -- Customer pre-processing section
1323 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
1324 'CREATE_ROLE_PLAN',
1325 'B',
1326 'C'
1327 )
1328 THEN
1329 cn_role_plans_pub_cuhk.create_role_plan_pre
1330 (p_api_version => p_api_version,
1331 p_init_msg_list => p_init_msg_list,
1332 p_commit => p_commit,
1333 p_validation_level => p_validation_level,
1334 x_return_status => x_return_status,
1335 x_loading_status => x_loading_status,
1336 x_msg_count => x_msg_count,
1337 x_msg_data => x_msg_data,
1338 p_role_plan_rec => l_rec
1339 );
1340
1341 IF (x_return_status = fnd_api.g_ret_sts_error)
1342 THEN
1343 RAISE fnd_api.g_exc_error;
1344 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1345 THEN
1346 RAISE fnd_api.g_exc_unexpected_error;
1347 END IF;
1348 END IF;
1349
1350 -- Vertical industry pre-processing section
1351 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
1352 'CREATE_ROLE_PLAN',
1353 'B',
1354 'V'
1355 )
1356 THEN
1357 cn_role_plans_pub_vuhk.create_role_plan_pre
1358 (p_api_version => p_api_version,
1359 p_init_msg_list => p_init_msg_list,
1360 p_commit => p_commit,
1361 p_validation_level => p_validation_level,
1362 x_return_status => x_return_status,
1363 x_loading_status => x_loading_status,
1364 x_msg_count => x_msg_count,
1365 x_msg_data => x_msg_data,
1366 p_role_plan_rec => l_rec
1367 );
1368
1369 IF (x_return_status = fnd_api.g_ret_sts_error)
1370 THEN
1371 RAISE fnd_api.g_exc_error;
1372 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1373 THEN
1374 RAISE fnd_api.g_exc_unexpected_error;
1375 END IF;
1376 END IF;
1377
1378 -- Start of API body
1379
1380 -- CHANTHON: Changed the param from p_role_plan_rec to l_rec
1381 check_valid_insert (x_return_status => x_return_status,
1382 x_msg_count => x_msg_count,
1383 x_msg_data => x_msg_data,
1384 p_role_plan_rec => l_rec,
1385 x_role_id => l_role_id,
1386 x_comp_plan_id => l_comp_plan_id,
1387 p_loading_status => x_loading_status, -- in
1388 x_loading_status => x_loading_status -- out
1389 );
1390
1391 IF (x_return_status <> fnd_api.g_ret_sts_success)
1392 THEN
1393 RAISE fnd_api.g_exc_error;
1394 ELSE
1395 SELECT cn_role_plans_s.NEXTVAL
1396 INTO l_role_plan_id
1397 FROM DUAL;
1398
1402
1399 -- CHANTHON: Setting the role plan id out param
1400 x_role_plan_id := l_role_plan_id;
1401 --CHANTHON: Added org id and obj_ver_num in the insert
1403 --- *** Business Events *** ---
1404 business_event
1405 (p_operation => 'create',
1406 p_pre_or_post => 'pre',
1407 p_role_plan_id => l_role_plan_id,
1408 p_role_plan_rec => l_rec);
1409
1410
1411 cn_role_plans_pkg.insert_row
1412 (x_rowid => g_rowid,
1413 x_role_plan_id => l_role_plan_id,
1414 x_role_id => l_role_id,
1415 x_comp_plan_id => l_comp_plan_id,
1416 x_start_date => p_role_plan_rec.start_date,
1417 x_end_date => p_role_plan_rec.end_date,
1418 x_create_module => 'OSC',
1419 x_attribute_category => p_role_plan_rec.attribute_category,
1420 x_attribute1 => p_role_plan_rec.attribute1,
1421 x_attribute2 => p_role_plan_rec.attribute2,
1422 x_attribute3 => p_role_plan_rec.attribute3,
1423 x_attribute4 => p_role_plan_rec.attribute4,
1424 x_attribute5 => p_role_plan_rec.attribute5,
1425 x_attribute6 => p_role_plan_rec.attribute6,
1426 x_attribute7 => p_role_plan_rec.attribute7,
1427 x_attribute8 => p_role_plan_rec.attribute8,
1428 x_attribute9 => p_role_plan_rec.attribute9,
1429 x_attribute10 => p_role_plan_rec.attribute10,
1430 x_attribute11 => p_role_plan_rec.attribute11,
1431 x_attribute12 => p_role_plan_rec.attribute12,
1432 x_attribute13 => p_role_plan_rec.attribute13,
1433 x_attribute14 => p_role_plan_rec.attribute14,
1434 x_attribute15 => p_role_plan_rec.attribute15,
1435 x_created_by => g_created_by,
1436 x_creation_date => g_creation_date,
1437 x_last_update_login => g_last_update_login,
1438 x_last_update_date => g_last_update_date,
1439 x_last_updated_by => g_last_updated_by,
1440 x_org_id => p_role_plan_rec.org_id,
1441 x_object_version_number => l_object_version_number
1442 );
1443
1444 /* System Generated - Create Note Functionality */
1445 fnd_message.set_name ('CN', 'CN_ROLE_PLAN_CRE');
1446 fnd_message.set_token ('NEWVALUE', l_rec.role_name);
1447 fnd_message.set_token ('NAME', l_rec.comp_plan_name);
1448 l_note_msg := fnd_message.get;
1449 jtf_notes_pub.create_note
1450 (p_api_version => 1.0,
1451 x_return_status => x_return_status,
1452 x_msg_count => x_msg_count,
1453 x_msg_data => x_msg_data,
1454 p_source_object_id => l_rec.comp_plan_id,
1455 p_source_object_code => 'CN_COMP_PLANS',
1456 p_notes => l_note_msg,
1457 p_notes_detail => l_note_msg,
1458 p_note_type => 'CN_SYSGEN', -- for system generated
1459 x_jtf_note_id => l_note_id -- returned
1460 );
1461
1462 -- Call srp-plan assignment API to insert
1463 --CHANTHON: Added the org_id for insert into srp
1464 srp_plan_assignment_for_insert (p_role_id => l_role_id,
1465 p_role_plan_id => l_role_plan_id,
1466 x_return_status => x_return_status,
1467 p_loading_status => x_loading_status,
1468 x_loading_status => x_loading_status,
1469 p_org_id => l_rec.org_id
1470 );
1471
1472 IF (x_return_status <> fnd_api.g_ret_sts_success)
1473 THEN
1474 RAISE fnd_api.g_exc_error;
1475 END IF;
1476 END IF;
1477
1478 -- End of API body
1479
1480 -- Post processing hooks
1481
1482 -- User hooks
1483
1484 -- Customer post-processing section
1485 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
1486 'CREATE_ROLE_PLAN',
1487 'A',
1488 'V'
1489 )
1490 THEN
1491 cn_role_plans_pub_cuhk.create_role_plan_post
1492 (p_api_version => p_api_version,
1493 p_init_msg_list => p_init_msg_list,
1494 p_commit => p_commit,
1495 p_validation_level => p_validation_level,
1496 x_return_status => x_return_status,
1497 x_loading_status => x_loading_status,
1498 x_msg_count => x_msg_count,
1499 x_msg_data => x_msg_data,
1500 p_role_plan_rec => l_rec
1501 );
1502
1503 IF (x_return_status = fnd_api.g_ret_sts_error)
1507 THEN
1504 THEN
1505 RAISE fnd_api.g_exc_error;
1506 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1508 RAISE fnd_api.g_exc_unexpected_error;
1509 END IF;
1510 END IF;
1511
1512 -- Vertical industry post-processing section
1513 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
1514 'CREATE_ROLE_PLAN',
1515 'A',
1516 'C'
1517 )
1518 THEN
1519 cn_role_plans_pub_vuhk.create_role_plan_post
1520 (p_api_version => p_api_version,
1521 p_init_msg_list => p_init_msg_list,
1522 p_commit => p_commit,
1523 p_validation_level => p_validation_level,
1524 x_return_status => x_return_status,
1525 x_loading_status => x_loading_status,
1526 x_msg_count => x_msg_count,
1527 x_msg_data => x_msg_data,
1528 p_role_plan_rec => l_rec
1529 );
1530
1531 IF (x_return_status = fnd_api.g_ret_sts_error)
1532 THEN
1533 RAISE fnd_api.g_exc_error;
1534 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1535 THEN
1536 RAISE fnd_api.g_exc_unexpected_error;
1537 END IF;
1538 END IF;
1539
1540 -- Message enable hook
1541 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
1542 'CREATE_ROLE_PLAN',
1543 'M',
1544 'M'
1545 )
1546 THEN
1547 IF cn_role_plans_pub_cuhk.ok_to_generate_msg
1548 (p_role_plan_rec => l_rec)
1549 THEN
1550 -- Clear bind variables
1551 -- XMLGEN.clearBindValues;
1552
1553 -- Set values for bind variables,
1554 -- call this for all bind variables in the business object
1555 -- XMLGEN.setBindValue('SRP_PMT_PLAN_ID', x_srp_pmt_plan_id);
1556
1557 -- Get a ID for workflow/ business object instance
1558 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1559 -- Do this for all the bind variables in the Business Object
1560 jtf_usr_hks.load_bind_data (l_bind_data_id,
1561 'ROLE_PLAN_ID',
1562 l_role_plan_id,
1563 'S',
1564 'S'
1565 );
1566 -- Message generation API
1567 jtf_usr_hks.generate_message (p_prod_code => 'CN',
1568 p_bus_obj_code => 'ROLE_PLAN',
1569 p_bus_obj_name => 'ROLE_PLAN',
1570 p_action_code => 'I',
1571 p_bind_data_id => l_bind_data_id,
1572 p_oai_param => NULL,
1573 p_oai_array => l_oai_array,
1574 x_return_code => x_return_status
1575 );
1576
1577 IF (x_return_status = fnd_api.g_ret_sts_error)
1578 THEN
1579 RAISE fnd_api.g_exc_error;
1580 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1581 THEN
1582 RAISE fnd_api.g_exc_unexpected_error;
1583 END IF;
1584 END IF;
1585 END IF;
1586
1587 -- CHANTHON: Getting the Object Version Number
1588 x_obj_ver_num := l_object_version_number;
1589
1590 --- *** Business Events *** ---
1591 business_event
1592 (p_operation => 'create',
1593 p_pre_or_post => 'post',
1594 p_role_plan_id => l_role_plan_id,
1595 p_role_plan_rec => l_rec);
1596
1597
1598 -- Standard check of p_commit.
1599 IF fnd_api.to_boolean (p_commit)
1600 THEN
1601 COMMIT WORK;
1602 END IF;
1603
1604 -- Standard call to get message count and if count is 1, get message info.
1605 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1606 p_data => x_msg_data,
1607 p_encoded => fnd_api.g_false
1608 );
1609 EXCEPTION
1610 WHEN fnd_api.g_exc_error
1611 THEN
1612 ROLLBACK TO create_role_plan;
1613 x_return_status := fnd_api.g_ret_sts_error;
1614 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1615 p_data => x_msg_data,
1616 p_encoded => fnd_api.g_false
1617 );
1618 WHEN fnd_api.g_exc_unexpected_error
1619 THEN
1620 ROLLBACK TO create_role_plan;
1621 x_loading_status := 'UNEXPECTED_ERR';
1622 x_return_status := fnd_api.g_ret_sts_unexp_error;
1623 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1624 p_data => x_msg_data,
1625 p_encoded => fnd_api.g_false
1626 );
1627 WHEN OTHERS
1628 THEN
1629 ROLLBACK TO create_role_plan;
1633 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1630 x_loading_status := 'UNEXPECTED_ERR';
1631 x_return_status := fnd_api.g_ret_sts_unexp_error;
1632
1634 THEN
1635 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1636 END IF;
1637
1638 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1639 p_data => x_msg_data,
1640 p_encoded => fnd_api.g_false
1641 );
1642 END create_role_plan;
1643
1644 -- --------------------------------------------------------------------------*
1645 -- Procedure: Update_Role_Plan
1646 -- CHANTHON: Modified p_ovn to IN OUT param
1647 -- --------------------------------------------------------------------------*
1648 PROCEDURE update_role_plan (
1649 p_api_version IN NUMBER,
1650 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1651 p_commit IN VARCHAR2 := fnd_api.g_false,
1652 p_validation_level IN NUMBER
1653 := fnd_api.g_valid_level_full,
1654 x_return_status OUT NOCOPY VARCHAR2,
1655 x_loading_status OUT NOCOPY VARCHAR2,
1656 x_msg_count OUT NOCOPY NUMBER,
1657 x_msg_data OUT NOCOPY VARCHAR2,
1658 p_role_plan_rec_old IN role_plan_rec_type
1659 := g_miss_role_plan_rec,
1660 p_ovn IN OUT NOCOPY cn_role_plans.object_version_number%TYPE,
1661 p_role_plan_rec_new IN role_plan_rec_type
1662 := g_miss_role_plan_rec,
1663 p_role_plan_id IN cn_role_plans.role_plan_id%TYPE
1664 )
1665 IS
1666 --p_role_plan_id should be NULL if passing p_role_plan_rec_old
1667 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Role_Plan';
1668 l_api_version CONSTANT NUMBER := 1.0;
1669 l_role_plan_id_old cn_role_plans.role_plan_id%TYPE;
1670 l_role_id cn_roles.role_id%TYPE;
1671 l_comp_plan_id cn_comp_plans.comp_plan_id%TYPE;
1672 l_date_update_only VARCHAR2 (1);
1673 -- Declaration for user hooks
1674 l_rec_old role_plan_rec_type;
1675 l_rec_new role_plan_rec_type;
1676 l_oai_array jtf_usr_hks.oai_data_array_type;
1677 l_bind_data_id NUMBER;
1678 l_org_id NUMBER;
1679 l_status VARCHAR2(1);
1680 l_note_msg VARCHAR2 (240);
1681 l_note_id NUMBER;
1682 l_consolidated_note VARCHAR2(2000);
1683 l_consolidated_note_new VARCHAR2(2000);
1684 BEGIN
1685 -- Standard Start of API savepoint
1686 SAVEPOINT update_role_plan;
1687
1688 -- Standard call to check for call compatibility.
1689 IF NOT fnd_api.compatible_api_call (l_api_version,
1690 p_api_version,
1691 l_api_name,
1692 g_pkg_name
1693 )
1694 THEN
1695 RAISE fnd_api.g_exc_unexpected_error;
1696 END IF;
1697
1698 -- Initialize message list if p_init_msg_list is set to TRUE.
1699 IF fnd_api.to_boolean (p_init_msg_list)
1700 THEN
1701 fnd_msg_pub.initialize;
1702 END IF;
1703
1704 -- Initialize API return status to success
1705 x_return_status := fnd_api.g_ret_sts_success;
1706 x_loading_status := 'CN_UPDATED';
1707
1708 -- Assign the parameter to a local variable to be passed to Pre, Post
1709 -- and Business APIs
1710 l_rec_old := p_role_plan_rec_old;
1711 l_rec_new := p_role_plan_rec_new;
1712
1713 -- CHANTHON: Added to select the orignial record before updation BEGIN QUERY
1714 -- The role plan id should be passed in as null if the original record
1715 -- before updating is available.
1716 IF (p_role_plan_id IS NOT NULL)
1717 THEN
1718 SELECT cn_api.g_miss_char role_name,
1719 role_id,
1720 cn_api.g_miss_char comp_plan_name,
1721 comp_plan_id,
1722 start_date,
1723 end_date,
1724 attribute_category,
1725 attribute1,
1726 attribute2,
1727 attribute3,
1728 attribute4,
1729 attribute5,
1730 attribute6,
1731 attribute7,
1732 attribute8,
1733 attribute9,
1734 attribute10,
1735 attribute11,
1736 attribute12,
1737 attribute13,
1738 attribute14,
1739 attribute15,
1740 object_version_number,
1741 org_id
1742 INTO l_rec_old
1743 FROM cn_role_plans
1744 WHERE role_plan_id = p_role_plan_id;
1745 END IF;
1746
1747 -- CHANTHON: Added to select the orignial record before updation END OF QUERY
1748
1749 -- START OF MOAC ORG_ID VALIDATION
1750 l_org_id := l_rec_old.org_id;
1751 mo_global.validate_orgid_pub_api(org_id => l_org_id,
1752 status => l_status);
1753
1754 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1755 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1756 'cn.plsql.cn_role_plans_pub.update_role_plan.org_validate',
1757 'Validated org_id = ' || l_org_id || ' status = '||l_status);
1758 end if;
1759
1763 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1760 if (nvl(p_role_plan_rec_new.org_id, l_org_id)
1761 <> nvl(l_rec_old.org_id, l_org_id)) then
1762 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
1764 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
1765 'cn.plsql.cn_role_plans_pub.update_role_plan.error',
1766 true);
1767 end if;
1768
1769 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1770 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
1771 FND_MSG_PUB.Add;
1772 END IF;
1773
1774 RAISE FND_API.G_EXC_ERROR ;
1775 end if;
1776 -- END OF MOAC ORG_ID VALIDATION
1777
1778 -- CHANTHON: Added to get the comp plan and role names given the ids
1779 IF (l_rec_new.role_name IS NULL AND l_rec_new.comp_plan_name IS NULL)
1780 THEN
1781 l_rec_old.comp_plan_name :=
1782 cn_api.get_cp_name (l_rec_old.comp_plan_id);
1783 l_rec_old.role_name := cn_api.get_role_name (l_rec_old.role_id);
1784 l_rec_new.comp_plan_name :=
1785 cn_api.get_cp_name (p_role_plan_rec_new.comp_plan_id);
1786 l_rec_new.role_name :=
1787 cn_api.get_role_name (p_role_plan_rec_new.role_id);
1788 END IF;
1789
1790 -- User hooks
1791
1792 -- Customer pre-processing section
1793 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
1794 'UPDATE_ROLE_PLAN',
1795 'B',
1796 'C'
1797 )
1798 THEN
1799 cn_role_plans_pub_cuhk.update_role_plan_pre
1800 (p_api_version => p_api_version,
1801 p_init_msg_list => p_init_msg_list,
1802 p_commit => p_commit,
1803 p_validation_level => p_validation_level,
1804 x_return_status => x_return_status,
1805 x_loading_status => x_loading_status,
1806 x_msg_count => x_msg_count,
1807 x_msg_data => x_msg_data,
1808 p_role_plan_rec_old => l_rec_old,
1809 p_role_plan_rec_new => l_rec_new
1810 );
1811
1812 IF (x_return_status = fnd_api.g_ret_sts_error)
1813 THEN
1814 RAISE fnd_api.g_exc_error;
1815 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1816 THEN
1817 RAISE fnd_api.g_exc_unexpected_error;
1818 END IF;
1819 END IF;
1820
1821 -- Vertical industry pre-processing section
1822 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
1823 'UPDATE_ROLE_PLAN',
1824 'B',
1825 'V'
1826 )
1827 THEN
1828 cn_role_plans_pub_vuhk.update_role_plan_pre
1829 (p_api_version => p_api_version,
1830 p_init_msg_list => p_init_msg_list,
1831 p_commit => p_commit,
1832 p_validation_level => p_validation_level,
1833 x_return_status => x_return_status,
1834 x_loading_status => x_loading_status,
1835 x_msg_count => x_msg_count,
1836 x_msg_data => x_msg_data,
1837 p_role_plan_rec_old => l_rec_old,
1838 p_role_plan_rec_new => l_rec_new
1839 );
1840
1841 IF (x_return_status = fnd_api.g_ret_sts_error)
1842 THEN
1843 RAISE fnd_api.g_exc_error;
1844 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1845 THEN
1846 RAISE fnd_api.g_exc_unexpected_error;
1847 END IF;
1848 END IF;
1849
1850 --CHANTHON:
1851 -- Replaced p_role_plan_rec_old with l_rec_old,
1852 -- and p_role_plan_rec_new with l_rec_new
1853
1854 -- Start of API body
1855 check_valid_update (x_return_status => x_return_status,
1856 x_msg_count => x_msg_count,
1857 x_msg_data => x_msg_data,
1858 p_role_plan_rec_old => l_rec_old,
1859 p_role_plan_rec_new => l_rec_new,
1860 x_role_plan_id_old => l_role_plan_id_old,
1861 x_role_id => l_role_id,
1862 x_comp_plan_id => l_comp_plan_id,
1863 x_date_update_only => l_date_update_only,
1864 p_loading_status => x_loading_status, -- in
1865 x_loading_status => x_loading_status -- out
1866 );
1867
1868 -- x_return_status is failure for all failure cases,
1869 IF (x_return_status <> fnd_api.g_ret_sts_success)
1870 THEN
1871 RAISE fnd_api.g_exc_error;
1872 ELSE
1873 -- CHANTHON: Added ORG_ID in the update
1874
1875 --- *** Business Events *** ---
1876 business_event
1877 (p_operation => 'update',
1878 p_pre_or_post => 'pre',
1882 cn_role_plans_pkg.update_row
1879 p_role_plan_id => l_role_plan_id_old,
1880 p_role_plan_rec => l_rec_old);
1881
1883 (x_role_plan_id => l_role_plan_id_old,
1884 x_role_id => l_role_id,
1885 x_comp_plan_id => l_comp_plan_id,
1886 x_start_date => p_role_plan_rec_new.start_date,
1887 x_end_date => p_role_plan_rec_new.end_date,
1888 x_attribute_category => p_role_plan_rec_new.attribute_category,
1889 x_attribute1 => p_role_plan_rec_new.attribute1,
1890 x_attribute2 => p_role_plan_rec_new.attribute2,
1891 x_attribute3 => p_role_plan_rec_new.attribute3,
1892 x_attribute4 => p_role_plan_rec_new.attribute4,
1893 x_attribute5 => p_role_plan_rec_new.attribute5,
1894 x_attribute6 => p_role_plan_rec_new.attribute6,
1895 x_attribute7 => p_role_plan_rec_new.attribute7,
1896 x_attribute8 => p_role_plan_rec_new.attribute8,
1897 x_attribute9 => p_role_plan_rec_new.attribute9,
1898 x_attribute10 => p_role_plan_rec_new.attribute10,
1899 x_attribute11 => p_role_plan_rec_new.attribute11,
1900 x_attribute12 => p_role_plan_rec_new.attribute12,
1901 x_attribute13 => p_role_plan_rec_new.attribute13,
1902 x_attribute14 => p_role_plan_rec_new.attribute14,
1903 x_attribute15 => p_role_plan_rec_new.attribute15,
1904 x_created_by => g_created_by,
1905 x_creation_date => g_creation_date,
1906 x_last_update_login => g_last_update_login,
1907 x_last_update_date => g_last_update_date,
1908 x_last_updated_by => g_last_updated_by,
1909 x_object_version_number => p_ovn,
1910 x_org_id => l_rec_new.org_id
1911 );
1912
1913 l_consolidated_note := '';
1914 l_consolidated_note_new := '';
1915 --Notes when the Role is changed in RolePlam Assignment
1916 IF (l_rec_new.role_name <> fnd_api.g_miss_char AND l_rec_new.role_name IS NOT NULL
1917 AND l_rec_old.role_name <> fnd_api.g_miss_char AND l_rec_old.role_name IS NOT NULL
1918 AND l_rec_new.role_name <> l_rec_old.role_name)
1919 THEN
1920 --Notes for Old Role being unassigned
1921 fnd_message.set_name ('CN', 'CN_ROLE_PLAN_DEL');
1922 fnd_message.set_token ('NEWVALUE', l_rec_old.role_name);
1923 fnd_message.set_token ('NAME', l_rec_old.comp_plan_name);
1924 l_note_msg := fnd_message.get;
1925 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
1926 /*
1927 jtf_notes_pub.create_note
1928 (p_api_version => 1.0,
1929 x_return_status => x_return_status,
1930 x_msg_count => x_msg_count,
1931 x_msg_data => x_msg_data,
1932 p_source_object_id => l_rec_old.comp_plan_id,
1933 p_source_object_code => 'CN_COMP_PLANS',
1934 p_notes => l_note_msg,
1935 p_notes_detail => l_note_msg,
1936 p_note_type => 'CN_SYSGEN', -- for system generated
1937 x_jtf_note_id => l_note_id -- returned
1938 );
1939 */
1940 --Notes for New Role being assigned
1941 fnd_message.set_name ('CN', 'CN_ROLE_PLAN_CRE');
1942 fnd_message.set_token ('NEWVALUE', l_rec_new.role_name);
1943 fnd_message.set_token ('NAME', l_rec_new.comp_plan_name);
1944 l_note_msg := fnd_message.get;
1945 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
1946 /*
1947 jtf_notes_pub.create_note
1948 (p_api_version => 1.0,
1949 x_return_status => x_return_status,
1950 x_msg_count => x_msg_count,
1951 x_msg_data => x_msg_data,
1952 p_source_object_id => l_rec_new.comp_plan_id,
1953 p_source_object_code => 'CN_COMP_PLANS',
1954 p_notes => l_note_msg,
1955 p_notes_detail => l_note_msg,
1956 p_note_type => 'CN_SYSGEN', -- for system generated
1957 x_jtf_note_id => l_note_id -- returned
1958 );
1959 */
1960 ELSE IF (l_rec_new.comp_plan_name <> fnd_api.g_miss_char AND l_rec_new.comp_plan_name IS NOT NULL
1961 AND l_rec_old.comp_plan_name <> fnd_api.g_miss_char AND l_rec_old.comp_plan_name IS NOT NULL
1962 AND l_rec_new.comp_plan_name <> l_rec_old.comp_plan_name)
1963 THEN
1964 --Notes for Role being unassigned from Old Compplan
1965 fnd_message.set_name ('CN', 'CN_ROLE_PLAN_DEL');
1966 fnd_message.set_token ('NEWVALUE', l_rec_old.role_name);
1967 fnd_message.set_token ('NAME', l_rec_old.comp_plan_name);
1971 jtf_notes_pub.create_note
1968 l_note_msg := fnd_message.get;
1969 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
1970 /*
1972 (p_api_version => 1.0,
1973 x_return_status => x_return_status,
1974 x_msg_count => x_msg_count,
1975 x_msg_data => x_msg_data,
1976 p_source_object_id => l_rec_old.comp_plan_id,
1977 p_source_object_code => 'CN_COMP_PLANS',
1978 p_notes => l_note_msg,
1979 p_notes_detail => l_note_msg,
1980 p_note_type => 'CN_SYSGEN', -- for system generated
1981 x_jtf_note_id => l_note_id -- returned
1982 );
1983 */
1984 --Notes for Role being assigned to New Compplan
1985 fnd_message.set_name ('CN', 'CN_ROLE_PLAN_CRE');
1986 fnd_message.set_token ('NEWVALUE', l_rec_new.role_name);
1987 fnd_message.set_token ('NAME', l_rec_new.comp_plan_name);
1988 l_note_msg := fnd_message.get;
1989 l_consolidated_note_new := l_consolidated_note_new || l_note_msg || fnd_global.local_chr(10);
1990 /*
1991 jtf_notes_pub.create_note
1992 (p_api_version => 1.0,
1993 x_return_status => x_return_status,
1994 x_msg_count => x_msg_count,
1995 x_msg_data => x_msg_data,
1996 p_source_object_id => l_rec_new.comp_plan_id,
1997 p_source_object_code => 'CN_COMP_PLANS',
1998 p_notes => l_note_msg,
1999 p_notes_detail => l_note_msg,
2000 p_note_type => 'CN_SYSGEN', -- for system generated
2001 x_jtf_note_id => l_note_id -- returned
2002 );
2003 */
2004 ELSE IF (l_rec_new.start_date <> l_rec_old.start_date)
2005 THEN
2006 --Notes for changing start date for RolePlan assignment
2007 fnd_message.set_name ('CN', 'CN_ROLE_PLAN_START_DATE_UPD');
2008 fnd_message.set_token ('OLDVALUE', l_rec_old.start_date);
2009 fnd_message.set_token ('NEWVALUE', l_rec_new.start_date);
2010 fnd_message.set_token ('NAME', l_rec_new.role_name);
2011 l_note_msg := fnd_message.get;
2012 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
2013 /*
2014 jtf_notes_pub.create_note
2015 (p_api_version => 1.0,
2016 x_return_status => x_return_status,
2017 x_msg_count => x_msg_count,
2018 x_msg_data => x_msg_data,
2019 p_source_object_id => l_rec_old.comp_plan_id,
2020 p_source_object_code => 'CN_COMP_PLANS',
2021 p_notes => l_note_msg,
2022 p_notes_detail => l_note_msg,
2023 p_note_type => 'CN_SYSGEN', -- for system generated
2024 x_jtf_note_id => l_note_id -- returned
2025 );
2026 */
2027 END IF;
2028 IF ((l_rec_old.end_date is null OR l_rec_old.end_date = fnd_api.G_MISS_DATE)
2029 AND (l_rec_new.end_date is not null AND l_rec_new.end_date <> fnd_api.G_MISS_DATE))
2030 THEN
2031 --Notes for setting end date for RolePlan assignment
2032 fnd_message.set_name ('CN', 'CN_ROLE_PLAN_END_DATE_NULL_UPD');
2033 fnd_message.set_token ('NEWVALUE', l_rec_new.end_date);
2034 fnd_message.set_token ('NAME', l_rec_new.role_name);
2035 l_note_msg := fnd_message.get;
2036 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
2037 /*
2038 jtf_notes_pub.create_note
2039 (p_api_version => 1.0,
2040 x_return_status => x_return_status,
2041 x_msg_count => x_msg_count,
2042 x_msg_data => x_msg_data,
2043 p_source_object_id => l_rec_old.comp_plan_id,
2044 p_source_object_code => 'CN_COMP_PLANS',
2045 p_notes => l_note_msg,
2046 p_notes_detail => l_note_msg,
2047 p_note_type => 'CN_SYSGEN', -- for system generated
2048 x_jtf_note_id => l_note_id -- returned
2049 );
2050 */
2051 ELSE IF ((l_rec_new.end_date is null OR l_rec_new.end_date = fnd_api.G_MISS_DATE)
2052 AND (l_rec_old.end_date is not null AND l_rec_old.end_date <> fnd_api.G_MISS_DATE))
2053 THEN
2054 --Notes for removing end date for RolePlan assignment
2055 fnd_message.set_name ('CN', 'CN_ROLE_PLAN_END_DATE_UPD_NULL');
2056 fnd_message.set_token ('NAME', l_rec_new.role_name);
2060 jtf_notes_pub.create_note
2057 l_note_msg := fnd_message.get;
2058 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
2059 /*
2061 (p_api_version => 1.0,
2062 x_return_status => x_return_status,
2063 x_msg_count => x_msg_count,
2064 x_msg_data => x_msg_data,
2065 p_source_object_id => l_rec_old.comp_plan_id,
2066 p_source_object_code => 'CN_COMP_PLANS',
2067 p_notes => l_note_msg,
2068 p_notes_detail => l_note_msg,
2069 p_note_type => 'CN_SYSGEN', -- for system generated
2070 x_jtf_note_id => l_note_id -- returned
2071 );
2072 */
2073
2074 ELSE IF (l_rec_new.end_date <> l_rec_old.end_date)
2075 THEN
2076 --Notes for changing end date for RolePlan assignment
2077 fnd_message.set_name ('CN', 'CN_ROLE_PLAN_END_DATE_UPD');
2078 fnd_message.set_token ('OLDVALUE', l_rec_old.end_date);
2079 fnd_message.set_token ('NEWVALUE', l_rec_new.end_date);
2080 fnd_message.set_token ('NAME', l_rec_new.role_name);
2081 l_note_msg := fnd_message.get;
2082 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
2083 /*
2084 jtf_notes_pub.create_note
2085 (p_api_version => 1.0,
2086 x_return_status => x_return_status,
2087 x_msg_count => x_msg_count,
2088 x_msg_data => x_msg_data,
2089 p_source_object_id => l_rec_old.comp_plan_id,
2090 p_source_object_code => 'CN_COMP_PLANS',
2091 p_notes => l_note_msg,
2092 p_notes_detail => l_note_msg,
2093 p_note_type => 'CN_SYSGEN', -- for system generated
2094 x_jtf_note_id => l_note_id -- returned
2095 );
2096 */
2097 END IF;
2098 END IF;
2099 END IF;
2100 END IF;
2101 END IF;
2102
2103 IF LENGTH(l_consolidated_note) > 1 THEN
2104
2105 jtf_notes_pub.create_note (p_api_version => 1.0,
2106 x_return_status => x_return_status,
2107 x_msg_count => x_msg_count,
2108 x_msg_data => x_msg_data,
2109 p_source_object_id => l_rec_old.comp_plan_id,
2110 p_source_object_code => 'CN_COMP_PLANS',
2111 p_notes => l_consolidated_note,
2112 p_notes_detail => l_consolidated_note,
2113 p_note_type => 'CN_SYSGEN', -- for system generated
2114 x_jtf_note_id => l_note_id -- returned
2115 );
2116 END IF;
2117 IF LENGTH(l_consolidated_note_new) > 1 THEN
2118
2119 jtf_notes_pub.create_note (p_api_version => 1.0,
2120 x_return_status => x_return_status,
2121 x_msg_count => x_msg_count,
2122 x_msg_data => x_msg_data,
2123 p_source_object_id => l_rec_new.comp_plan_id,
2124 p_source_object_code => 'CN_COMP_PLANS',
2125 p_notes => l_consolidated_note_new,
2126 p_notes_detail => l_consolidated_note_new,
2127 p_note_type => 'CN_SYSGEN', -- for system generated
2128 x_jtf_note_id => l_note_id -- returned
2129 );
2130 END IF;
2131 --CHANTHON: Selecting the object version number
2132 SELECT object_version_number
2133 INTO p_ovn
2134 FROM cn_role_plans
2135 WHERE role_plan_id = l_role_plan_id_old;
2136
2137 -- Call srp assignment API to update
2138
2139 -- IF UPDATE is only for start_date and end_date THEN call srp_plan_assigns.update
2140 -- IF the update will change comp plan then
2141 -- call srp_plan_assign.delete then insert
2142
2143 --CHANTHON: Added the org_id for the original and updated comp plan
2144 srp_plan_assignment_for_update
2145 (p_role_id => l_role_id,
2146 p_role_id_old => l_rec_old.role_id,
2147 p_role_plan_id => l_role_plan_id_old,
2148 p_date_update_only => l_date_update_only,
2149 x_return_status => x_return_status,
2150 p_loading_status => x_loading_status,
2151 x_loading_status => x_loading_status,
2155
2152 p_org_id => l_rec_new.org_id,
2153 p_org_id_old => l_rec_old.org_id
2154 );
2156 IF (x_return_status <> fnd_api.g_ret_sts_success)
2157 THEN
2158 RAISE fnd_api.g_exc_error;
2159 END IF;
2160 END IF;
2161
2162 -- End of API body
2163
2164 --- *** Business Events *** ---
2165 business_event
2166 (p_operation => 'update',
2167 p_pre_or_post => 'post',
2168 p_role_plan_id => l_role_plan_id_old,
2169 p_role_plan_rec => l_rec_new);
2170
2171
2172 -- Post processing hooks
2173
2174 -- User hooks
2175
2176 -- Customer post-processing section
2177 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
2178 'UPDATE_ROLE_PLAN',
2179 'A',
2180 'V'
2181 )
2182 THEN
2183 cn_role_plans_pub_cuhk.update_role_plan_post
2184 (p_api_version => p_api_version,
2185 p_init_msg_list => p_init_msg_list,
2186 p_commit => p_commit,
2187 p_validation_level => p_validation_level,
2188 x_return_status => x_return_status,
2189 x_loading_status => x_loading_status,
2190 x_msg_count => x_msg_count,
2191 x_msg_data => x_msg_data,
2192 p_role_plan_rec_old => l_rec_old,
2193 p_role_plan_rec_new => l_rec_new
2194 );
2195
2196 IF (x_return_status = fnd_api.g_ret_sts_error)
2197 THEN
2198 RAISE fnd_api.g_exc_error;
2199 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2200 THEN
2201 RAISE fnd_api.g_exc_unexpected_error;
2202 END IF;
2203 END IF;
2204
2205 -- Vertical industry post-processing section
2206 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
2207 'UPDATE_ROLE_PLAN',
2208 'A',
2209 'C'
2210 )
2211 THEN
2212 cn_role_plans_pub_vuhk.update_role_plan_post
2213 (p_api_version => p_api_version,
2214 p_init_msg_list => p_init_msg_list,
2215 p_commit => p_commit,
2216 p_validation_level => p_validation_level,
2217 x_return_status => x_return_status,
2218 x_loading_status => x_loading_status,
2219 x_msg_count => x_msg_count,
2220 x_msg_data => x_msg_data,
2221 p_role_plan_rec_old => l_rec_old,
2222 p_role_plan_rec_new => l_rec_new
2223 );
2224
2225 IF (x_return_status = fnd_api.g_ret_sts_error)
2226 THEN
2227 RAISE fnd_api.g_exc_error;
2228 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2229 THEN
2230 RAISE fnd_api.g_exc_unexpected_error;
2231 END IF;
2232 END IF;
2233
2234 -- Message enable hook
2235 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
2236 'UPDATE_ROLE_PLAN',
2237 'M',
2238 'M'
2239 )
2240 THEN
2241 IF cn_role_plans_pub_cuhk.ok_to_generate_msg
2242 (p_role_plan_rec => l_rec_new)
2243 THEN
2244 -- Clear bind variables
2245 -- XMLGEN.clearBindValues;
2246
2247 -- Set values for bind variables,
2248 -- call this for all bind variables in the business object
2249 -- XMLGEN.setBindValue('SRP_PMT_PLAN_ID', x_srp_pmt_plan_id);
2250
2251 -- Get a ID for workflow/ business object instance
2252 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
2253 -- Do this for all the bind variables in the Business Object
2254 jtf_usr_hks.load_bind_data (l_bind_data_id,
2255 'ROLE_PLAN_ID',
2256 l_role_plan_id_old,
2257 'S',
2258 'S'
2259 );
2260 -- Message generation API
2261 jtf_usr_hks.generate_message (p_prod_code => 'CN',
2262 p_bus_obj_code => 'ROLE_PLAN',
2263 p_bus_obj_name => 'ROLE_PLAN',
2264 p_action_code => 'I',
2265 p_bind_data_id => l_bind_data_id,
2266 p_oai_param => NULL,
2267 p_oai_array => l_oai_array,
2268 x_return_code => x_return_status
2269 );
2270
2271 IF (x_return_status = fnd_api.g_ret_sts_error)
2272 THEN
2276 RAISE fnd_api.g_exc_unexpected_error;
2273 RAISE fnd_api.g_exc_error;
2274 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2275 THEN
2277 END IF;
2278 END IF;
2279 END IF;
2280
2281 -- Standard check of p_commit.
2282 IF fnd_api.to_boolean (p_commit)
2283 THEN
2284 COMMIT WORK;
2285 END IF;
2286
2287 -- Standard call to get message count and if count is 1, get message info.
2288 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2289 p_data => x_msg_data,
2290 p_encoded => fnd_api.g_false
2291 );
2292 EXCEPTION
2293 WHEN fnd_api.g_exc_error
2294 THEN
2295 ROLLBACK TO update_role_plan;
2296 x_return_status := fnd_api.g_ret_sts_error;
2297 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2298 p_data => x_msg_data,
2299 p_encoded => fnd_api.g_false
2300 );
2301 WHEN fnd_api.g_exc_unexpected_error
2302 THEN
2303 ROLLBACK TO update_role_plan;
2304 x_loading_status := 'UNEXPECTED_ERR';
2305 x_return_status := fnd_api.g_ret_sts_unexp_error;
2306 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2307 p_data => x_msg_data,
2308 p_encoded => fnd_api.g_false
2309 );
2310 WHEN OTHERS
2311 THEN
2312 ROLLBACK TO update_role_plan;
2313 x_loading_status := 'UNEXPECTED_ERR';
2314 x_return_status := fnd_api.g_ret_sts_unexp_error;
2315
2316 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2317 THEN
2318 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2319 END IF;
2320
2321 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2322 p_data => x_msg_data,
2323 p_encoded => fnd_api.g_false
2324 );
2325 END update_role_plan;
2326
2327 -- --------------------------------------------------------------------------*
2328 -- Procedure: Delete_Role_Plan
2329 -- --------------------------------------------------------------------------*
2330 PROCEDURE delete_role_plan (
2331 p_api_version IN NUMBER,
2332 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2333 p_commit IN VARCHAR2 := fnd_api.g_false,
2334 p_validation_level IN NUMBER
2335 := fnd_api.g_valid_level_full,
2336 x_return_status OUT NOCOPY VARCHAR2,
2337 x_loading_status OUT NOCOPY VARCHAR2,
2338 x_msg_count OUT NOCOPY NUMBER,
2339 x_msg_data OUT NOCOPY VARCHAR2,
2340 p_role_plan_rec IN role_plan_rec_type
2341 := g_miss_role_plan_rec
2342 )
2343 IS
2344 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Role_Plan';
2345 l_api_version CONSTANT NUMBER := 1.0;
2346 l_role_plan_id cn_role_plans.role_plan_id%TYPE;
2347 l_role_id cn_roles.role_id%TYPE;
2348 -- Declaration for user hooks
2349 l_rec role_plan_rec_type;
2350 l_oai_array jtf_usr_hks.oai_data_array_type;
2351 l_bind_data_id NUMBER;
2352 l_org_id NUMBER;
2353 l_status VARCHAR2(1);
2354 l_note_msg VARCHAR2 (240);
2355 l_note_id NUMBER;
2356 BEGIN
2357 -- Standard Start of API savepoint
2358 SAVEPOINT delete_role_plan;
2359
2360 -- Standard call to check for call compatibility.
2361 IF NOT fnd_api.compatible_api_call (l_api_version,
2362 p_api_version,
2363 l_api_name,
2364 g_pkg_name
2365 )
2366 THEN
2367 RAISE fnd_api.g_exc_unexpected_error;
2368 END IF;
2369
2370 -- Initialize message list if p_init_msg_list is set to TRUE.
2371 IF fnd_api.to_boolean (p_init_msg_list)
2372 THEN
2373 fnd_msg_pub.initialize;
2374 END IF;
2375
2376 -- Initialize API return status to success
2377 x_return_status := fnd_api.g_ret_sts_success;
2378 x_loading_status := 'CN_DELETED';
2379
2380 -- START OF MOAC ORG_ID VALIDATION
2381 l_org_id := p_role_plan_rec.org_id;
2382 mo_global.validate_orgid_pub_api(org_id => l_org_id,
2383 status => l_status);
2384
2385 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2386 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2387 'cn.plsql.cn_role_plans_pub.delete_role_plan.org_validate',
2388 'Validated org_id = ' || l_org_id || ' status = '||l_status);
2389 end if;
2390 -- END OF MOAC ORG_ID VALIDATION
2391
2392 -- Assign the parameter to a local variable to be passed to Pre, Post
2393 -- and Business APIs
2394 l_rec := p_role_plan_rec;
2395
2396 --CHANTHON: Added to get the comp plan name and role name
2397 IF ((l_rec.role_name IS NULL or l_rec.role_name = cn_api.G_MISS_CHAR)
2398 AND (l_rec.comp_plan_name IS NULL or l_rec.comp_plan_name = cn_api.G_MISS_CHAR))
2399 THEN
2400 l_rec.comp_plan_name :=
2404
2401 cn_api.get_cp_name (p_role_plan_rec.comp_plan_id);
2402 l_rec.role_name := cn_api.get_role_name (p_role_plan_rec.role_id);
2403 END IF;
2405 -- User hooks
2406
2407 -- Customer pre-processing section
2408 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
2409 'DELETE_ROLE_PLAN',
2410 'B',
2411 'C'
2412 )
2413 THEN
2414 cn_role_plans_pub_cuhk.delete_role_plan_pre
2415 (p_api_version => p_api_version,
2416 p_init_msg_list => p_init_msg_list,
2417 p_commit => p_commit,
2418 p_validation_level => p_validation_level,
2419 x_return_status => x_return_status,
2420 x_loading_status => x_loading_status,
2421 x_msg_count => x_msg_count,
2422 x_msg_data => x_msg_data,
2423 p_role_plan_rec => l_rec
2424 );
2425
2426 IF (x_return_status = fnd_api.g_ret_sts_error)
2427 THEN
2428 RAISE fnd_api.g_exc_error;
2429 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2430 THEN
2431 RAISE fnd_api.g_exc_unexpected_error;
2432 END IF;
2433 END IF;
2434
2435 -- Vertical industry pre-processing section
2436 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
2437 'DELETE_ROLE_PLAN',
2438 'B',
2439 'V'
2440 )
2441 THEN
2442 cn_role_plans_pub_vuhk.delete_role_plan_pre
2443 (p_api_version => p_api_version,
2444 p_init_msg_list => p_init_msg_list,
2445 p_commit => p_commit,
2446 p_validation_level => p_validation_level,
2447 x_return_status => x_return_status,
2448 x_loading_status => x_loading_status,
2449 x_msg_count => x_msg_count,
2450 x_msg_data => x_msg_data,
2451 p_role_plan_rec => l_rec
2452 );
2453
2454 IF (x_return_status = fnd_api.g_ret_sts_error)
2455 THEN
2456 RAISE fnd_api.g_exc_error;
2457 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2458 THEN
2459 RAISE fnd_api.g_exc_unexpected_error;
2460 END IF;
2461 END IF;
2462
2463 -- Start of API body
2464
2465 --- *** Business Events *** ---
2466 business_event
2467 (p_operation => 'delete',
2468 p_pre_or_post => 'pre',
2469 p_role_plan_id => l_role_plan_id,
2470 p_role_plan_rec => l_rec);
2471
2472
2473 -- CHANTHON: Changed p_role-plan_rec to l_rec
2474 check_valid_delete (x_return_status => x_return_status,
2475 x_msg_count => x_msg_count,
2476 x_msg_data => x_msg_data,
2477 p_role_plan_rec => l_rec,
2478 x_role_plan_id => l_role_plan_id,
2479 p_loading_status => x_loading_status, -- in
2480 x_loading_status => x_loading_status -- out
2481 );
2482
2483 IF (x_return_status <> fnd_api.g_ret_sts_success)
2484 THEN
2485 RAISE fnd_api.g_exc_error;
2486 ELSE
2487 -- need to call srp assignment API to delete
2488 l_role_id := cn_api.get_role_id(l_rec.role_name);
2489 srp_plan_assignment_for_delete
2490 (p_role_id => l_role_id,
2491 p_role_plan_id => l_role_plan_id,
2492 x_return_status => x_return_status,
2493 p_loading_status => x_loading_status,
2494 x_loading_status => x_loading_status,
2495 p_org_id => l_rec.org_id
2496 );
2497
2498 IF (x_return_status <> fnd_api.g_ret_sts_success)
2499 THEN
2500 RAISE fnd_api.g_exc_error;
2501 END IF;
2502
2503 -- Added as part of 12+ enhancment
2504 -- When a plan has been unassigned from role
2505 -- then we do not want rows in cn_scenario_plans_all
2506 -- table which contains role_plan_id
2507
2508 CN_SCENARIOS_PVT.delete_scenario_plans(p_api_version => p_api_version,
2509 p_init_msg_list => p_init_msg_list,
2510 p_commit => p_commit,
2511 p_validation_level => p_validation_level,
2512 p_role_plan_id => l_role_plan_id,
2513 p_comp_plan_id => null,
2514 p_role_id => null,
2515 x_return_status => x_return_status,
2519 IF (x_return_status = fnd_api.g_ret_sts_error)
2516 x_msg_count => x_msg_count,
2517 x_msg_data => x_msg_data);
2518
2520 THEN
2521 RAISE fnd_api.g_exc_error;
2522 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2523 THEN
2524 RAISE fnd_api.g_exc_unexpected_error;
2525 END IF;
2526 -- Ends 12+ enhancement
2527
2528 -- delete_row
2529 cn_role_plans_pkg.delete_row (x_role_plan_id => l_role_plan_id);
2530
2531 /* System Generated - Create Note Functionality */
2532 fnd_message.set_name ('CN', 'CN_ROLE_PLAN_DEL');
2533 fnd_message.set_token ('NEWVALUE', l_rec.role_name);
2534 fnd_message.set_token ('NAME', l_rec.comp_plan_name);
2535 l_note_msg := fnd_message.get;
2536 jtf_notes_pub.create_note
2537 (p_api_version => 1.0,
2538 x_return_status => x_return_status,
2539 x_msg_count => x_msg_count,
2540 x_msg_data => x_msg_data,
2541 p_source_object_id => l_rec.comp_plan_id,
2542 p_source_object_code => 'CN_COMP_PLANS',
2543 p_notes => l_note_msg,
2544 p_notes_detail => l_note_msg,
2545 p_note_type => 'CN_SYSGEN', -- for system generated
2546 x_jtf_note_id => l_note_id -- returned
2547 );
2548
2549 END IF;
2550
2551 -- End of API body
2552
2553 --- *** Business Events *** ---
2554 business_event
2555 (p_operation => 'delete',
2556 p_pre_or_post => 'post',
2557 p_role_plan_id => l_role_plan_id,
2558 p_role_plan_rec => l_rec);
2559
2560 -- Post processing hooks
2561
2562 -- User hooks
2563
2564 -- Customer post-processing section
2565 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
2566 'DELETE_ROLE_PLAN',
2567 'A',
2568 'V'
2569 )
2570 THEN
2571 cn_role_plans_pub_cuhk.delete_role_plan_post
2572 (p_api_version => p_api_version,
2573 p_init_msg_list => p_init_msg_list,
2574 p_commit => p_commit,
2575 p_validation_level => p_validation_level,
2576 x_return_status => x_return_status,
2577 x_loading_status => x_loading_status,
2578 x_msg_count => x_msg_count,
2579 x_msg_data => x_msg_data,
2580 p_role_plan_rec => l_rec
2581 );
2582
2583 IF (x_return_status = fnd_api.g_ret_sts_error)
2584 THEN
2585 RAISE fnd_api.g_exc_error;
2586 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2587 THEN
2588 RAISE fnd_api.g_exc_unexpected_error;
2589 END IF;
2590 END IF;
2591
2592 -- Vertical industry post-processing section
2593 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
2594 'DELETE_ROLE_PLAN',
2595 'A',
2596 'C'
2597 )
2598 THEN
2599 cn_role_plans_pub_vuhk.delete_role_plan_post
2600 (p_api_version => p_api_version,
2601 p_init_msg_list => p_init_msg_list,
2602 p_commit => p_commit,
2603 p_validation_level => p_validation_level,
2604 x_return_status => x_return_status,
2605 x_loading_status => x_loading_status,
2606 x_msg_count => x_msg_count,
2607 x_msg_data => x_msg_data,
2608 p_role_plan_rec => l_rec
2609 );
2610
2611 IF (x_return_status = fnd_api.g_ret_sts_error)
2612 THEN
2613 RAISE fnd_api.g_exc_error;
2614 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2615 THEN
2616 RAISE fnd_api.g_exc_unexpected_error;
2617 END IF;
2618 END IF;
2619
2620 -- Message enable hook
2621 IF jtf_usr_hks.ok_to_execute ('CN_ROLE_PLANS_PUB',
2622 'DELETE_ROLE_PLAN',
2623 'M',
2624 'M'
2625 )
2626 THEN
2627 IF cn_role_plans_pub_cuhk.ok_to_generate_msg
2628 (p_role_plan_rec => l_rec)
2629 THEN
2630 -- Clear bind variables
2631 -- XMLGEN.clearBindValues;
2632
2633 -- Set values for bind variables,
2634 -- call this for all bind variables in the business object
2635 -- XMLGEN.setBindValue('SRP_PMT_PLAN_ID', x_srp_pmt_plan_id);
2636
2637 -- Get a ID for workflow/ business object instance
2638 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
2639 -- Do this for all the bind variables in the Business Object
2640 jtf_usr_hks.load_bind_data (l_bind_data_id,
2641 'ROLE_PLAN_ID',
2642 l_role_plan_id,
2643 'S',
2644 'S'
2645 );
2646 -- Message generation API
2647 jtf_usr_hks.generate_message (p_prod_code => 'CN',
2648 p_bus_obj_code => 'ROLE_PLAN',
2649 p_bus_obj_name => 'ROLE_PLAN',
2650 p_action_code => 'I',
2651 p_bind_data_id => l_bind_data_id,
2652 p_oai_param => NULL,
2653 p_oai_array => l_oai_array,
2654 x_return_code => x_return_status
2655 );
2656
2657 IF (x_return_status = fnd_api.g_ret_sts_error)
2658 THEN
2659 RAISE fnd_api.g_exc_error;
2660 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2661 THEN
2662 RAISE fnd_api.g_exc_unexpected_error;
2663 END IF;
2664 END IF;
2665 END IF;
2666
2667 -- Standard check of p_commit.
2668 IF fnd_api.to_boolean (p_commit)
2669 THEN
2670 COMMIT WORK;
2671 END IF;
2672
2673 -- Standard call to get message count and if count is 1, get message info.
2674 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2675 p_data => x_msg_data,
2676 p_encoded => fnd_api.g_false
2677 );
2678 EXCEPTION
2679 WHEN fnd_api.g_exc_error
2680 THEN
2681 ROLLBACK TO delete_role_plan;
2682 x_return_status := fnd_api.g_ret_sts_error;
2683 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2684 p_data => x_msg_data,
2685 p_encoded => fnd_api.g_false
2686 );
2687 WHEN fnd_api.g_exc_unexpected_error
2688 THEN
2689 ROLLBACK TO delete_role_plan;
2690 x_loading_status := 'UNEXPECTED_ERR';
2691 x_return_status := fnd_api.g_ret_sts_unexp_error;
2692 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2693 p_data => x_msg_data,
2694 p_encoded => fnd_api.g_false
2695 );
2696 WHEN OTHERS
2697 THEN
2698 ROLLBACK TO delete_role_plan;
2699 x_loading_status := 'UNEXPECTED_ERR';
2700 x_return_status := fnd_api.g_ret_sts_unexp_error;
2701
2702 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2703 THEN
2704 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2705 END IF;
2706
2707 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2708 p_data => x_msg_data,
2709 p_encoded => fnd_api.g_false
2710 );
2711 END delete_role_plan;
2712
2713 END cn_role_plans_pub;