[Home] [Help]
PACKAGE BODY: APPS.CN_COMP_PLAN_PVT
Source
1 PACKAGE BODY cn_comp_plan_pvt AS
2 /*$Header: cnvcmpnb.pls 120.21 2007/03/28 11:36:04 hanaraya ship $*/
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CN_COMP_PLAN_PVT';
4
5
6 PROCEDURE business_event(
7 p_operation IN VARCHAR2,
8 p_pre_or_post IN VARCHAR2,
9 p_comp_plan IN comp_plan_rec_type
10 ) IS
11
12 l_key VARCHAR2(80);
13 l_event_name VARCHAR2(80);
14 l_list wf_parameter_list_t;
15
16 BEGIN
17
18 -- p_operation = Add, Update, Remove
19 l_event_name := 'oracle.apps.cn.events.setup.compplan.' || p_operation || '.' || p_pre_or_post;
20
21 --Get the item key
22 l_key := l_event_name || '-' || p_comp_plan.COMP_PLAN_ID;
23
24 -- build parameter list as appropriate
25 IF (p_operation = 'create') THEN
26 wf_event.AddParameterToList('COMP_PLAN_ID',p_comp_plan.COMP_PLAN_ID,l_list);
27 wf_event.AddParameterToList('NAME',p_comp_plan.NAME,l_list);
28
29 ELSIF (p_operation = 'update') THEN
30 l_key := l_key || '-' || p_comp_plan.OBJECT_VERSION_NUMBER;
31
32 wf_event.AddParameterToList('COMP_PLAN_ID',p_comp_plan.COMP_PLAN_ID,l_list);
33 wf_event.AddParameterToList('NAME',p_comp_plan.NAME,l_list);
34
35 ELSIF (p_operation = 'delete') THEN
36 wf_event.AddParameterToList('COMP_PLAN_ID',p_comp_plan.COMP_PLAN_ID,l_list);
37 wf_event.AddParameterToList('NAME',p_comp_plan.NAME,l_list);
38 END IF;
39
40 -- Raise Event
41 wf_event.raise
42 (p_event_name => l_event_name,
43 p_event_key => l_key,
44 p_parameters => l_list);
45
46 l_list.DELETE;
47
48 END business_event;
49
50
51 FUNCTION get_ovn (
52 p_id IN NUMBER
53 )
54 RETURN NUMBER
55 IS
56 l_num NUMBER;
57 BEGIN
58 SELECT object_version_number
59 INTO l_num
60 FROM cn_comp_plans_all
61 WHERE comp_plan_id = p_id;
62
63 RETURN l_num;
64 END;
65
66 PROCEDURE check_org_id (
67 p_id IN NUMBER
68 )
69 IS
70 BEGIN
71 IF p_id IS NULL
72 THEN
73 fnd_message.set_name ('FND', 'MO_OU_REQUIRED');
74 fnd_msg_pub.ADD;
75 RAISE fnd_api.g_exc_error;
76 END IF;
77 END;
78
79 -- Start of comments
80 -- API name : Create_Comp_Plan
81 -- Type : Private.
82 -- Function :
83 -- Pre-reqs : None.
84 -- Parameters :
85 -- IN : p_api_version IN NUMBER Required
86 -- p_init_msg_list IN VARCHAR2 Optional
87 -- Default = FND_API.G_FALSE
88 -- p_commit IN VARCHAR2 Optional
89 -- Default = FND_API.G_FALSE
90 -- p_validation_level IN NUMBER Optional
91 -- Default = FND_API.G_VALID_LEVEL_FULL
92 -- p_comp_plan IN comp_plan_rec_type
93 -- OUT : x_return_status OUT VARCHAR2(1)
94 -- x_msg_count OUT NUMBER
95 -- x_msg_data OUT VARCHAR2(2000)
96 -- x_comp_plan_id OUT NUMBER
97 -- Version : Current version 1.0
98 -- Notes : Note text
99 --
100 -- End of comments
101 PROCEDURE create_comp_plan (
102 p_api_version IN NUMBER,
103 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
104 p_commit IN VARCHAR2 := fnd_api.g_false,
105 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
106 p_comp_plan IN OUT NOCOPY comp_plan_rec_type,
107 x_comp_plan_id OUT NOCOPY NUMBER,
108 x_return_status OUT NOCOPY VARCHAR2,
109 x_msg_count OUT NOCOPY NUMBER,
110 x_msg_data OUT NOCOPY VARCHAR2
111 )
112 IS
113 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Comp_Plan';
114 l_api_version CONSTANT NUMBER := 1.0;
115 l_temp_count NUMBER;
116 l_comp_rec cn_comp_plan_pub.comp_plan_rec_type;
117 l_loading_status VARCHAR2 (50);
118 l_note_msg VARCHAR2 (240);
119 l_note_id NUMBER;
120 BEGIN
121 -- Standard Start of API savepoint
122 SAVEPOINT create_comp_plan;
123
124 -- Standard call to check for call compatibility.
125 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
126 THEN
127 RAISE fnd_api.g_exc_unexpected_error;
128 END IF;
129
130 -- Initialize message list if p_init_msg_list is set to TRUE.
131 IF fnd_api.to_boolean (p_init_msg_list)
132 THEN
133 fnd_msg_pub.initialize;
134 END IF;
135
136 -- Initialize API return status to success
137 x_return_status := fnd_api.g_ret_sts_success;
138 -- API body
139 x_comp_plan_id := p_comp_plan.comp_plan_id;
140 -- *** Check the ORG_ID is null or not ***
141 check_org_id (p_comp_plan.org_id);
142
143 -- Convert fnd_api.g_miss to NULL
144
145 -- 1. name can not be null
146 IF (p_comp_plan.NAME IS NULL)
147 OR (p_comp_plan.NAME = fnd_api.g_miss_char)
148 OR (p_comp_plan.start_date IS NULL)
149 OR (p_comp_plan.start_date = fnd_api.g_miss_date)
150 THEN
151 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
152 THEN
153 fnd_message.set_name ('CN', 'CN_REQ_PAR_MISSING');
154 fnd_msg_pub.ADD;
155 END IF;
156
157 RAISE fnd_api.g_exc_error;
158 END IF;
159
160 -- 2. comp plan name must be unique
161 SELECT COUNT (1)
162 INTO l_temp_count
163 FROM cn_comp_plans
164 WHERE NAME = p_comp_plan.NAME AND org_id = p_comp_plan.org_id AND ROWNUM = 1;
165
166 IF l_temp_count <> 0
167 THEN
168 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
169 THEN
170 fnd_message.set_name ('CN', 'CN_INPUT_MUST_UNIQUE');
171 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('NAME', 'INPUT_TOKEN'));
172 fnd_msg_pub.ADD;
173 END IF;
174
175 RAISE fnd_api.g_exc_error;
176 END IF;
177
178 -- start date > end date
179 IF (p_comp_plan.end_date IS NOT NULL) AND (p_comp_plan.start_date > p_comp_plan.end_date)
180 THEN
181 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
182 THEN
183 fnd_message.set_name ('CN', 'CN_DATE_RANGE_ERROR');
184 fnd_msg_pub.ADD;
185 END IF;
186
187 RAISE fnd_api.g_exc_error;
188 END IF;
189
190 -- calling public api
191 SELECT DECODE (p_comp_plan.NAME, fnd_api.g_miss_char, NULL, p_comp_plan.NAME),
192 DECODE (p_comp_plan.description, fnd_api.g_miss_char, NULL, p_comp_plan.description),
193 'INCOMPLETE',
194 DECODE (p_comp_plan.allow_rev_class_overlap, fnd_api.g_miss_char, NULL, p_comp_plan.allow_rev_class_overlap),
195 DECODE (p_comp_plan.start_date, fnd_api.g_miss_date, NULL, TRUNC (p_comp_plan.start_date)),
196 DECODE (p_comp_plan.end_date, fnd_api.g_miss_date, NULL, TRUNC (p_comp_plan.end_date)),
197 NULL,
198 DECODE (p_comp_plan.attribute_category, fnd_api.g_miss_char, NULL, p_comp_plan.attribute_category),
199 DECODE (p_comp_plan.attribute1, fnd_api.g_miss_char, NULL, p_comp_plan.attribute1),
200 DECODE (p_comp_plan.attribute2, fnd_api.g_miss_char, NULL, p_comp_plan.attribute2),
201 DECODE (p_comp_plan.attribute3, fnd_api.g_miss_char, NULL, p_comp_plan.attribute3),
202 DECODE (p_comp_plan.attribute4, fnd_api.g_miss_char, NULL, p_comp_plan.attribute4),
203 DECODE (p_comp_plan.attribute5, fnd_api.g_miss_char, NULL, p_comp_plan.attribute5),
204 DECODE (p_comp_plan.attribute6, fnd_api.g_miss_char, NULL, p_comp_plan.attribute6),
205 DECODE (p_comp_plan.attribute7, fnd_api.g_miss_char, NULL, p_comp_plan.attribute7),
206 DECODE (p_comp_plan.attribute8, fnd_api.g_miss_char, NULL, p_comp_plan.attribute8),
207 DECODE (p_comp_plan.attribute9, fnd_api.g_miss_char, NULL, p_comp_plan.attribute9),
208 DECODE (p_comp_plan.attribute10, fnd_api.g_miss_char, NULL, p_comp_plan.attribute10),
209 DECODE (p_comp_plan.attribute11, fnd_api.g_miss_char, NULL, p_comp_plan.attribute11),
210 DECODE (p_comp_plan.attribute12, fnd_api.g_miss_char, NULL, p_comp_plan.attribute12),
211 DECODE (p_comp_plan.attribute13, fnd_api.g_miss_char, NULL, p_comp_plan.attribute13),
212 DECODE (p_comp_plan.attribute14, fnd_api.g_miss_char, NULL, p_comp_plan.attribute14),
213 DECODE (p_comp_plan.attribute15, fnd_api.g_miss_char, NULL, p_comp_plan.attribute15),
214 DECODE (p_comp_plan.org_id, fnd_api.g_miss_char, NULL, p_comp_plan.org_id)
215 INTO l_comp_rec.NAME,
216 l_comp_rec.description,
217 l_comp_rec.status,
218 l_comp_rec.rc_overlap,
219 l_comp_rec.start_date,
220 l_comp_rec.end_date,
221 l_comp_rec.plan_element_name,
222 l_comp_rec.attribute_category,
223 l_comp_rec.attribute1,
224 l_comp_rec.attribute2,
225 l_comp_rec.attribute3,
226 l_comp_rec.attribute4,
227 l_comp_rec.attribute5,
228 l_comp_rec.attribute6,
229 l_comp_rec.attribute7,
230 l_comp_rec.attribute8,
231 l_comp_rec.attribute9,
232 l_comp_rec.attribute10,
233 l_comp_rec.attribute11,
234 l_comp_rec.attribute12,
235 l_comp_rec.attribute13,
236 l_comp_rec.attribute14,
237 l_comp_rec.attribute15,
238 l_comp_rec.org_id
239 FROM DUAL;
240
241 -- *** Adding the org_id ***
242 l_comp_rec.org_id := p_comp_plan.org_id;
243
244 --- *** Business Events ***---
245 business_event
246 (p_operation => 'create',
247 p_pre_or_post => 'pre',
248 p_comp_plan => p_comp_plan);
249
250
251 cn_comp_plan_pub.create_comp_plan (p_api_version => p_api_version,
252 p_init_msg_list => p_init_msg_list,
253 p_commit => p_commit,
254 p_validation_level => p_validation_level,
255 x_return_status => x_return_status,
256 x_msg_count => x_msg_count,
257 x_msg_data => x_msg_data,
258 p_comp_plan_rec => l_comp_rec,
259 x_comp_plan_id => x_comp_plan_id,
260 x_loading_status => l_loading_status
261 );
262
263 IF (x_return_status <> fnd_api.g_ret_sts_success)
264 THEN
265 RAISE fnd_api.g_exc_error;
266 END IF;
267
268 p_comp_plan.object_version_number := get_ovn (x_comp_plan_id);
269
270
271 --- *** Business Events *** ---
272 business_event
273 (p_operation => 'create',
274 p_pre_or_post => 'post',
275 p_comp_plan => p_comp_plan);
276
277
278 /* System Generated - Create Note Functionality */
279 /* This code is later needed when the Public --> Pvt instead of pvt --> public
280 fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_CREATE');
281 fnd_message.set_token ('CP_NAME', l_comp_rec.NAME);
282 l_note_msg := fnd_message.get;
283 jtf_notes_pub.create_note
284 (p_api_version => 1.0,
285 x_return_status => x_return_status,
286 x_msg_count => x_msg_count,
287 x_msg_data => x_msg_data,
288 p_source_object_id => x_comp_plan_id,
289 p_source_object_code => 'CN_COMP_PLANS',
290 p_notes => l_note_msg,
291 p_notes_detail => l_note_msg,
292 p_note_type => 'CN_SYSGEN', -- for system generated
293 x_jtf_note_id => l_note_id -- returned
294 );
295 */
296
297 -- End of API body.
298 -- Standard check of p_commit.
299 IF fnd_api.to_boolean (p_commit)
300 THEN
301 COMMIT WORK;
302 END IF;
303
304 -- Standard call to get message count and if count is 1, get message info.
305 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
306 EXCEPTION
307 WHEN fnd_api.g_exc_error
308 THEN
309 ROLLBACK TO create_comp_plan;
310 x_return_status := fnd_api.g_ret_sts_error;
311 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
312 WHEN fnd_api.g_exc_unexpected_error
313 THEN
314 ROLLBACK TO create_comp_plan;
315 x_return_status := fnd_api.g_ret_sts_unexp_error;
316 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
317 WHEN OTHERS
318 THEN
319 ROLLBACK TO create_comp_plan;
320 x_return_status := fnd_api.g_ret_sts_unexp_error;
321
322 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
323 THEN
324 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
325 END IF;
326
327 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
328 END create_comp_plan;
329
330 -- Start of comments
331 -- API name : Update_Comp_Plan
332 -- Type : Private.
333 -- Function :
334 -- Pre-reqs : None.
335 -- Parameters :
336 -- IN : p_api_version IN NUMBER Required
337 -- p_init_msg_list IN VARCHAR2 Optional
338 -- Default = FND_API.G_FALSE
339 -- p_commit IN VARCHAR2 Optional
340 -- Default = FND_API.G_FALSE
341 -- p_validation_level IN NUMBER Optional
342 -- Default = FND_API.G_VALID_LEVEL_FULL
343 -- p_comp_plan IN comp_plan_rec_type
344 -- OUT : x_return_status OUT VARCHAR2(1)
345 -- x_msg_count OUT NUMBER
346 -- x_msg_data OUT VARCHAR2(2000)
347 -- Version : Current version 1.0
348 -- Notes : Note text
349 --
350 -- End of comments
351 PROCEDURE update_comp_plan (
352 p_api_version IN NUMBER,
353 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
354 p_commit IN VARCHAR2 := fnd_api.g_false,
355 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
356 p_comp_plan IN OUT NOCOPY comp_plan_rec_type,
357 x_return_status OUT NOCOPY VARCHAR2,
358 x_msg_count OUT NOCOPY NUMBER,
359 x_msg_data OUT NOCOPY VARCHAR2
360 )
361 IS
362 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Comp_Plan';
363 l_api_version CONSTANT NUMBER := 1.0;
364 g_last_update_date DATE := SYSDATE;
365 g_last_updated_by NUMBER := fnd_global.user_id;
366 g_creation_date DATE := SYSDATE;
367 g_created_by NUMBER := fnd_global.user_id;
368 g_last_update_login NUMBER := fnd_global.login_id;
369 g_rowid VARCHAR2 (30);
370
371 CURSOR l_old_comp_plan_cr
372 IS
373 SELECT *
374 FROM cn_comp_plans
375 WHERE comp_plan_id = p_comp_plan.comp_plan_id;
376
377 l_old_comp_plan l_old_comp_plan_cr%ROWTYPE;
378 l_comp_plan comp_plan_rec_type;
379 l_temp_count NUMBER;
380 l_start_date DATE;
381 l_end_date DATE;
382 l_name cn_comp_plans.NAME%TYPE;
383 l_description cn_comp_plans.description%TYPE;
384 l_overlap cn_comp_plans.allow_rev_class_overlap%TYPE;
385 l_comp_plan_id cn_comp_plans.comp_plan_id%TYPE := p_comp_plan.comp_plan_id;
386 l_loading_status VARCHAR2 (50);
387 l_return_status VARCHAR2 (50);
388 l_attribute_category VARCHAR2 (150);
389 l_attribute1 VARCHAR2 (150);
390 l_attribute2 VARCHAR2 (150);
391 l_attribute3 VARCHAR2 (150);
392 l_attribute4 VARCHAR2 (150);
393 l_attribute5 VARCHAR2 (150);
394 l_attribute6 VARCHAR2 (150);
395 l_attribute7 VARCHAR2 (150);
396 l_attribute8 VARCHAR2 (150);
397 l_attribute9 VARCHAR2 (150);
398 l_attribute10 VARCHAR2 (150);
399 l_attribute11 VARCHAR2 (150);
400 l_attribute12 VARCHAR2 (150);
401 l_attribute13 VARCHAR2 (150);
402 l_attribute14 VARCHAR2 (150);
403 l_attribute15 VARCHAR2 (150);
404 l_org_id NUMBER;
405 l_note_msg VARCHAR2 (240);
406 l_note_id NUMBER;
407 l_consolidated_note VARCHAR2(2000);
408 BEGIN
409 -- Standard Start of API savepoint
410 SAVEPOINT update_comp_plan;
411
412 -- Standard call to check for call compatibility.
413 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
414 THEN
415 RAISE fnd_api.g_exc_unexpected_error;
416 END IF;
417
418 -- Initialize message list if p_init_msg_list is set to TRUE.
419 IF fnd_api.to_boolean (p_init_msg_list)
420 THEN
421 fnd_msg_pub.initialize;
422 END IF;
423
424 -- Initialize API return status to success
425 x_return_status := fnd_api.g_ret_sts_success;
426 -- API body
427 -- *** Check the ORG_ID is null or not ***
428 check_org_id (p_comp_plan.org_id);
429
430 -- 1. name can not be null
431 IF (p_comp_plan.NAME IS NULL) OR (p_comp_plan.start_date IS NULL)
432 THEN
433 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
434 THEN
435 fnd_message.set_name ('CN', 'CN_REQ_PAR_MISSING');
436 fnd_msg_pub.ADD;
437 END IF;
438
439 RAISE fnd_api.g_exc_error;
440 END IF;
441
442 -- 2. comp plan name must be unique
443 SELECT COUNT (1)
444 INTO l_temp_count
445 FROM cn_comp_plans
446 WHERE NAME = p_comp_plan.NAME AND comp_plan_id <> p_comp_plan.comp_plan_id AND org_id = p_comp_plan.org_id AND ROWNUM = 1;
447
448 IF l_temp_count <> 0
449 THEN
450 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
451 THEN
452 fnd_message.set_name ('CN', 'CN_INPUT_MUST_UNIQUE');
453 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('NAME', 'INPUT_TOKEN'));
454 fnd_msg_pub.ADD;
455 END IF;
456
457 RAISE fnd_api.g_exc_error;
458 END IF;
459
460 -- clku 7/10/2001, bug 1689518
461 -- 3. check whether the revenue classes of the plan elements assigned overlap,
462 -- give out a warning if revenue classes overlap.
463 /*IF p_comp_plan.allow_rev_class_overlap = 'N'
464 THEN
465 check_revenue_class_overlap (p_comp_plan_id => p_comp_plan.comp_plan_id,
466 p_rc_overlap => p_comp_plan.allow_rev_class_overlap,
467 p_loading_status => l_loading_status,
468 x_loading_status => l_loading_status,
469 x_return_status => l_return_status
470 );
471 -- don't care about the return status here. If it is not 'SUCCESS', we
472 -- just return the message as a warning message and let the user carry on
473 -- saving the Comp Plan.
474 END IF;*/
475
476 OPEN l_old_comp_plan_cr;
477
478 FETCH l_old_comp_plan_cr
479 INTO l_old_comp_plan;
480
481 CLOSE l_old_comp_plan_cr;
482
483 SELECT DECODE (p_comp_plan.start_date, fnd_api.g_miss_date, TRUNC (l_old_comp_plan.start_date), TRUNC (p_comp_plan.start_date)),
484 DECODE (p_comp_plan.end_date, fnd_api.g_miss_date, TRUNC (l_old_comp_plan.end_date), TRUNC (p_comp_plan.end_date))
485 INTO l_start_date,
486 l_end_date
487 FROM DUAL;
488
489 -- start date > end date
490 IF (l_end_date IS NOT NULL) AND (l_start_date > l_end_date)
491 THEN
492 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
493 THEN
494 fnd_message.set_name ('CN', 'CN_DATE_RANGE_ERROR');
495 fnd_msg_pub.ADD;
496 END IF;
497
498 RAISE fnd_api.g_exc_error;
499 END IF;
500
501 -- call table handler
502 SELECT DECODE (p_comp_plan.NAME, fnd_api.g_miss_char, l_old_comp_plan.NAME, p_comp_plan.NAME),
503 DECODE (p_comp_plan.description, fnd_api.g_miss_char, l_old_comp_plan.description, p_comp_plan.description),
504 DECODE (p_comp_plan.allow_rev_class_overlap,
505 fnd_api.g_miss_char, l_old_comp_plan.allow_rev_class_overlap,
506 p_comp_plan.allow_rev_class_overlap
507 ),
508 DECODE (p_comp_plan.attribute_category, fnd_api.g_miss_char, l_old_comp_plan.attribute_category, p_comp_plan.attribute_category),
509 DECODE (p_comp_plan.attribute1, fnd_api.g_miss_char, l_old_comp_plan.attribute1, p_comp_plan.attribute1),
510 DECODE (p_comp_plan.attribute2, fnd_api.g_miss_char, l_old_comp_plan.attribute2, p_comp_plan.attribute2),
511 DECODE (p_comp_plan.attribute3, fnd_api.g_miss_char, l_old_comp_plan.attribute3, p_comp_plan.attribute3),
512 DECODE (p_comp_plan.attribute4, fnd_api.g_miss_char, l_old_comp_plan.attribute4, p_comp_plan.attribute4),
513 DECODE (p_comp_plan.attribute5, fnd_api.g_miss_char, l_old_comp_plan.attribute5, p_comp_plan.attribute5),
514 DECODE (p_comp_plan.attribute6, fnd_api.g_miss_char, l_old_comp_plan.attribute6, p_comp_plan.attribute6),
515 DECODE (p_comp_plan.attribute7, fnd_api.g_miss_char, l_old_comp_plan.attribute7, p_comp_plan.attribute7),
516 DECODE (p_comp_plan.attribute8, fnd_api.g_miss_char, l_old_comp_plan.attribute8, p_comp_plan.attribute8),
517 DECODE (p_comp_plan.attribute9, fnd_api.g_miss_char, l_old_comp_plan.attribute9, p_comp_plan.attribute9),
518 DECODE (p_comp_plan.attribute10, fnd_api.g_miss_char, l_old_comp_plan.attribute10, p_comp_plan.attribute10),
519 DECODE (p_comp_plan.attribute11, fnd_api.g_miss_char, l_old_comp_plan.attribute11, p_comp_plan.attribute11),
520 DECODE (p_comp_plan.attribute12, fnd_api.g_miss_char, l_old_comp_plan.attribute12, p_comp_plan.attribute12),
521 DECODE (p_comp_plan.attribute13, fnd_api.g_miss_char, l_old_comp_plan.attribute13, p_comp_plan.attribute13),
522 DECODE (p_comp_plan.attribute14, fnd_api.g_miss_char, l_old_comp_plan.attribute14, p_comp_plan.attribute14),
523 DECODE (p_comp_plan.attribute15, fnd_api.g_miss_char, l_old_comp_plan.attribute15, p_comp_plan.attribute15),
524 DECODE (p_comp_plan.org_id, fnd_api.g_miss_char, l_old_comp_plan.org_id, p_comp_plan.org_id)
525 INTO l_name,
526 l_description,
527 l_overlap,
528 l_attribute_category,
529 l_attribute1,
530 l_attribute2,
531 l_attribute3,
532 l_attribute4,
533 l_attribute5,
534 l_attribute6,
535 l_attribute7,
536 l_attribute8,
537 l_attribute9,
538 l_attribute10,
539 l_attribute11,
540 l_attribute12,
541 l_attribute13,
542 l_attribute14,
543 l_attribute15,
544 l_org_id
545 FROM DUAL;
546
547 -- 3. check object version number
548 IF l_old_comp_plan.object_version_number <> p_comp_plan.object_version_number
549 THEN
550 fnd_message.set_name ('CN', 'CN_RECORD_CHANGED');
551 fnd_msg_pub.ADD;
552 RAISE fnd_api.g_exc_error;
553 END IF;
554
555 -- 4. check for consistency in date range assignment
556 -- check plan element assignments - they just have to intersect
557 -- role, salesrep assignments have to be contained with in comp plan range
558 SELECT COUNT (1)
559 INTO l_temp_count
560 FROM cn_quotas_v q,
561 cn_quota_assigns qa
562 WHERE q.quota_id = qa.quota_id
563 AND qa.comp_plan_id = l_comp_plan_id
564 AND GREATEST (start_date, l_start_date) > LEAST (NVL (end_date, l_end_date), l_end_date);
565
566 -- if end date null then cond doesn't pass, but that's okay
567 IF l_temp_count > 0
568 THEN
569 fnd_message.set_name ('CN', 'CN_PLAN_ELT_DISJOINT');
570 fnd_msg_pub.ADD;
571 RAISE fnd_api.g_exc_error;
572 END IF;
573
574 SELECT COUNT (1)
575 INTO l_temp_count
576 FROM cn_role_plans
577 WHERE comp_plan_id = l_comp_plan_id AND (start_date < l_start_date OR (end_date IS NULL AND l_end_date IS NOT NULL) OR (end_date > l_end_date));
578
579 IF l_temp_count > 0
580 THEN
581 fnd_message.set_name ('CN', 'CN_ROLE_NOT_WITHIN_PLAN');
582 fnd_msg_pub.ADD;
583 RAISE fnd_api.g_exc_error;
584 END IF;
585
586 -- since srp assignments always within role assignments, then we
587 -- don't need to check those
588 SELECT org_id
589 INTO l_org_id
590 FROM cn_comp_plans
591 WHERE comp_plan_id = l_comp_plan_id;
592
593 --- *** Business Events *** ---
594 business_event
595 (p_operation => 'update',
596 p_pre_or_post => 'pre',
597 p_comp_plan => p_comp_plan);
598
599
600 cn_comp_plans_pkg.begin_record (x_operation => 'UPDATE',
601 x_rowid => g_rowid,
602 x_comp_plan_id => l_comp_plan_id,
603 x_name => l_name,
604 x_description => l_description,
605 x_start_date => l_start_date,
606 x_end_date => l_end_date,
607 x_status_code => 'INCOMPLETE',
608 x_allow_rev_class_overlap => l_overlap,
609 x_last_update_date => g_last_update_date,
610 x_last_updated_by => g_last_updated_by,
611 x_creation_date => g_creation_date,
612 x_created_by => g_created_by,
613 x_last_update_login => g_last_update_login,
614 x_program_type => 'PL/SQL',
615 x_start_date_old => l_old_comp_plan.start_date,
616 x_end_date_old => l_old_comp_plan.end_date,
617 x_allow_rev_class_overlap_old => l_old_comp_plan.allow_rev_class_overlap,
618 x_attribute_category => l_attribute_category,
619 x_attribute1 => l_attribute1,
620 x_attribute2 => l_attribute2,
621 x_attribute3 => l_attribute3,
622 x_attribute4 => l_attribute4,
623 x_attribute5 => l_attribute5,
624 x_attribute6 => l_attribute6,
625 x_attribute7 => l_attribute7,
626 x_attribute8 => l_attribute8,
627 x_attribute9 => l_attribute9,
628 x_attribute10 => l_attribute10,
629 x_attribute11 => l_attribute11,
630 x_attribute12 => l_attribute12,
631 x_attribute13 => l_attribute13,
632 x_attribute14 => l_attribute14,
633 x_attribute15 => l_attribute15,
634 x_org_id => l_org_id
635 );
636 p_comp_plan.object_version_number := get_ovn (l_comp_plan_id);
637
638 --- *** Business Events *** ---
639
640 business_event
641 (p_operation => 'update',
642 p_pre_or_post => 'post',
643 p_comp_plan => p_comp_plan);
644
645
646 /* Adding Notes Information */
647
648 /* 1. Check if the name has been changed */
649 l_consolidated_note := '';
650 IF (p_comp_plan.NAME <> fnd_api.g_miss_char AND p_comp_plan.NAME IS NOT NULL AND p_comp_plan.NAME <> l_old_comp_plan.NAME)
651 THEN
652 -- Need to add note CNR12_NOTE_COMPPLAN_UPDATE
653 fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_UPDATE');
654 fnd_message.set_token ('OLD_CP_NAME', l_old_comp_plan.NAME);
655 fnd_message.set_token ('NEW_CP_NAME', p_comp_plan.NAME);
656 l_note_msg := fnd_message.get;
657 l_consolidated_note := l_note_msg || fnd_global.local_chr(10);
658
659 /*
660 jtf_notes_pub.create_note (p_api_version => 1.0,
661 x_return_status => x_return_status,
662 x_msg_count => x_msg_count,
663 x_msg_data => x_msg_data,
664 p_source_object_id => l_old_comp_plan.comp_plan_id,
665 p_source_object_code => 'CN_COMP_PLANS',
666 p_notes => l_note_msg,
667 p_notes_detail => l_note_msg,
668 p_note_type => 'CN_SYSGEN', -- for system generated
669 x_jtf_note_id => l_note_id -- returned
670 );
671 */
672 END IF;
673
674 /* 2. Check if the start date has been changed */
675 IF (p_comp_plan.start_date <> fnd_api.g_miss_date AND p_comp_plan.start_date IS NOT NULL
676 AND p_comp_plan.start_date <> l_old_comp_plan.start_date
677 )
678 THEN
679 -- Need to add note CNR12_NOTE_COMPPLAN_SDATE_CRE
680 fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_SDATE_CRE');
681 fnd_message.set_token ('OLD_ST_DATE', TO_CHAR (l_old_comp_plan.start_date));
682 fnd_message.set_token ('NEW_ST_DATE', TO_CHAR (p_comp_plan.start_date));
683 l_note_msg := fnd_message.get;
684 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
685
686 /*
687 jtf_notes_pub.create_note (p_api_version => 1.0,
688 x_return_status => x_return_status,
689 x_msg_count => x_msg_count,
690 x_msg_data => x_msg_data,
691 p_source_object_id => l_old_comp_plan.comp_plan_id,
692 p_source_object_code => 'CN_COMP_PLANS',
693 p_notes => l_note_msg,
694 p_notes_detail => l_note_msg,
695 p_note_type => 'CN_SYSGEN', -- for system generated
696 x_jtf_note_id => l_note_id -- returned
697 );
698 */
699 END IF;
700
701 /* 3. Check if the end date has been changed */
702 IF (p_comp_plan.end_date <> fnd_api.g_miss_date AND p_comp_plan.end_date IS NOT NULL AND p_comp_plan.end_date <> l_old_comp_plan.end_date)
703 THEN
704 -- Need to add note CNR12_NOTE_COMPPLAN_EDATE_UPD
705 fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_EDATE_UPD');
706 fnd_message.set_token ('OLD_END_DATE', TO_CHAR (l_old_comp_plan.end_date));
707 fnd_message.set_token ('NEW_END_DATE', TO_CHAR (p_comp_plan.end_date));
708 l_note_msg := fnd_message.get;
709 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
710 /*
711 jtf_notes_pub.create_note (p_api_version => 1.0,
712 x_return_status => x_return_status,
713 x_msg_count => x_msg_count,
714 x_msg_data => x_msg_data,
715 p_source_object_id => l_old_comp_plan.comp_plan_id,
716 p_source_object_code => 'CN_COMP_PLANS',
717 p_notes => l_note_msg,
718 p_notes_detail => l_note_msg,
719 p_note_type => 'CN_SYSGEN', -- for system generated
720 x_jtf_note_id => l_note_id -- returned
721 );
722 */
723 END IF;
724
725 /* 4. Allow Revenue Class Overlap flag --> Changed to N */
726 IF ( p_comp_plan.allow_rev_class_overlap <> fnd_api.g_miss_char
727 AND p_comp_plan.allow_rev_class_overlap IS NOT NULL
728 AND p_comp_plan.allow_rev_class_overlap <> l_old_comp_plan.allow_rev_class_overlap
729 AND p_comp_plan.allow_rev_class_overlap = 'N'
730 )
731 THEN
732 -- Need to add note CNR12_NOTE_COMPPLAN_ELIG_UPD2
733 fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_ELIG_UPD2');
734 l_note_msg := fnd_message.get;
735 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
736 /*
737 jtf_notes_pub.create_note (p_api_version => 1.0,
738 x_return_status => x_return_status,
739 x_msg_count => x_msg_count,
740 x_msg_data => x_msg_data,
741 p_source_object_id => l_old_comp_plan.comp_plan_id,
742 p_source_object_code => 'CN_COMP_PLANS',
743 p_notes => l_note_msg,
744 p_notes_detail => l_note_msg,
745 p_note_type => 'CN_SYSGEN', -- for system generated
746 x_jtf_note_id => l_note_id -- returned
747 );
748 */
749 END IF;
750
751 /* 5. Allow Revenue Class Overlap flag --> Changed to Y */
752 IF ( p_comp_plan.allow_rev_class_overlap <> fnd_api.g_miss_char
753 AND p_comp_plan.allow_rev_class_overlap IS NOT NULL
754 AND p_comp_plan.allow_rev_class_overlap <> l_old_comp_plan.allow_rev_class_overlap
755 AND p_comp_plan.allow_rev_class_overlap = 'Y'
756 )
757 THEN
758 -- Need to add note CNR12_NOTE_COMPPLAN_ELIG_UPD1
759 fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_ELIG_UPD1');
760 l_note_msg := fnd_message.get;
761 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
762 /*
763 jtf_notes_pub.create_note (p_api_version => 1.0,
764 x_return_status => x_return_status,
765 x_msg_count => x_msg_count,
766 x_msg_data => x_msg_data,
767 p_source_object_id => l_old_comp_plan.comp_plan_id,
768 p_source_object_code => 'CN_COMP_PLANS',
769 p_notes => l_note_msg,
770 p_notes_detail => l_note_msg,
771 p_note_type => 'CN_SYSGEN', -- for system generated
772 x_jtf_note_id => l_note_id -- returned
773 );
774 */
775 END IF;
776
777
778 IF LENGTH(l_consolidated_note) > 1 THEN
779
780 jtf_notes_pub.create_note (p_api_version => 1.0,
781 x_return_status => x_return_status,
782 x_msg_count => x_msg_count,
783 x_msg_data => x_msg_data,
784 p_source_object_id => l_old_comp_plan.comp_plan_id,
785 p_source_object_code => 'CN_COMP_PLANS',
786 p_notes => l_consolidated_note,
787 p_notes_detail => l_consolidated_note,
788 p_note_type => 'CN_SYSGEN', -- for system generated
789 x_jtf_note_id => l_note_id -- returned
790 );
791 END IF;
792
793 -- End of API body.
794 -- Standard check of p_commit.
795 IF fnd_api.to_boolean (p_commit)
796 THEN
797 COMMIT WORK;
798 END IF;
799
800 -- Standard call to get message count and if count is 1, get message info.
801 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
802 EXCEPTION
803 WHEN fnd_api.g_exc_error
804 THEN
805 ROLLBACK TO update_comp_plan;
806 x_return_status := fnd_api.g_ret_sts_error;
807 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
808 WHEN fnd_api.g_exc_unexpected_error
809 THEN
810 ROLLBACK TO update_comp_plan;
811 x_return_status := fnd_api.g_ret_sts_unexp_error;
812 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
813 WHEN OTHERS
814 THEN
815 ROLLBACK TO update_comp_plan;
816 x_return_status := fnd_api.g_ret_sts_unexp_error;
817
818 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
819 THEN
820 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
821 END IF;
822
823 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
824 END update_comp_plan;
825
826 -- Start of comments
827 -- API name : Delete_Comp_Plan
828 -- Type : Private.
829 -- Function :
830 -- Pre-reqs : None.
831 -- Parameters :
832 -- IN : p_api_version IN NUMBER Required
833 -- p_init_msg_list IN VARCHAR2 Optional
834 -- Default = FND_API.G_FALSE
835 -- p_commit IN VARCHAR2 Optional
836 -- Default = FND_API.G_FALSE
837 -- p_validation_level IN NUMBER Optional
838 -- Default = FND_API.G_VALID_LEVEL_FULL
839 -- p_comp_plan IN comp_plan_rec_type
840 -- OUT : x_return_status OUT VARCHAR2(1)
841 -- x_msg_count OUT NUMBER
842 -- x_msg_data OUT VARCHAR2(2000)
843 -- Version : Current version 1.0
844 -- Notes : Note text
845 --
846 -- End of comments
847 PROCEDURE delete_comp_plan (
848 p_api_version IN NUMBER,
849 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
850 p_commit IN VARCHAR2 := fnd_api.g_false,
851 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
852 p_comp_plan IN OUT NOCOPY comp_plan_rec_type,
853 x_return_status OUT NOCOPY VARCHAR2,
854 x_msg_count OUT NOCOPY NUMBER,
855 x_msg_data OUT NOCOPY VARCHAR2
856 )
857 IS
858 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Comp_Plan';
859 l_api_version CONSTANT NUMBER := 1.0;
860 l_temp_count NUMBER;
861 l_dummy_row_id VARCHAR2 (18);
862 l_comp_plan_id cn_comp_plans.comp_plan_id%TYPE := p_comp_plan.comp_plan_id;
863 l_note_msg VARCHAR2 (240);
864 l_note_id NUMBER;
865 l_org_id NUMBER := -999;
866 l_cp_name cn_comp_plans.NAME%TYPE := NULL;
867 BEGIN
868 -- Standard Start of API savepoint
869 SAVEPOINT delete_comp_plan;
870
871 -- Standard call to check for call compatibility.
872 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
873 THEN
874 RAISE fnd_api.g_exc_unexpected_error;
875 END IF;
876
877 -- Initialize message list if p_init_msg_list is set to TRUE.
878 IF fnd_api.to_boolean (p_init_msg_list)
879 THEN
880 fnd_msg_pub.initialize;
881 END IF;
882
883 -- Initialize API return status to success
884 x_return_status := fnd_api.g_ret_sts_success;
885 -- API body
886 -- *** Check the ORG_ID is null or not ***
887 check_org_id (p_comp_plan.org_id);
888 l_org_id := p_comp_plan.org_id;
889 l_comp_plan_id := p_comp_plan.comp_plan_id;
890 -- delete the comp plan
891 BEGIN
892 SELECT NAME INTO l_cp_name from CN_COMP_PLANS where comp_plan_id = l_comp_plan_id;
893 EXCEPTION
894 WHEN OTHERS THEN
895 NULL;
896 END;
897
898
899 --- *** Business Events *** ---
900 business_event
901 (p_operation => 'delete',
902 p_pre_or_post => 'pre',
903 p_comp_plan => p_comp_plan);
904
905 cn_comp_plans_pkg.begin_record (x_operation => 'DELETE',
906 x_rowid => l_dummy_row_id,
907 x_comp_plan_id => l_comp_plan_id,
908 x_name => NULL,
909 x_last_update_date => NULL,
910 x_last_updated_by => NULL,
911 x_creation_date => NULL,
912 x_created_by => NULL,
913 x_last_update_login => NULL,
914 x_description => NULL,
915 x_start_date => NULL,
916 x_start_date_old => NULL,
917 x_end_date => NULL,
918 x_end_date_old => NULL,
919 x_program_type => 'API',
920 x_status_code => NULL,
921 x_allow_rev_class_overlap => NULL,
922 x_allow_rev_class_overlap_old => NULL,
923 x_attribute_category => NULL,
924 x_attribute1 => NULL,
925 x_attribute2 => NULL,
926 x_attribute3 => NULL,
927 x_attribute4 => NULL,
928 x_attribute5 => NULL,
929 x_attribute6 => NULL,
930 x_attribute7 => NULL,
931 x_attribute8 => NULL,
932 x_attribute9 => NULL,
933 x_attribute10 => NULL,
934 x_attribute11 => NULL,
935 x_attribute12 => NULL,
936 x_attribute13 => NULL,
937 x_attribute14 => NULL,
938 x_attribute15 => NULL,
939 x_org_id => NULL
940 );
941 --- *** Business Events *** ---
942 business_event
943 (p_operation => 'delete',
944 p_pre_or_post => 'post',
945 p_comp_plan => p_comp_plan);
946
947
948 /* Added the Notes for R12 */
949 IF (l_org_id <> -999)
950 THEN
951 fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_DELETE');
952 fnd_message.set_token ('CP_NAME', l_cp_name);
953 l_note_msg := fnd_message.get;
954 jtf_notes_pub.create_note (p_api_version => 1.0,
955 x_return_status => x_return_status,
956 x_msg_count => x_msg_count,
957 x_msg_data => x_msg_data,
958 p_source_object_id => l_org_id,
959 p_source_object_code => 'CN_DELETED_OBJECTS',
960 p_notes => l_note_msg,
961 p_notes_detail => l_note_msg,
962 p_note_type => 'CN_SYSGEN', -- for system generated
963 x_jtf_note_id => l_note_id -- returned
964 );
965 END IF;
966
967 -- End of API body.
968 -- Standard check of p_commit.
969 IF fnd_api.to_boolean (p_commit)
970 THEN
971 COMMIT WORK;
972 END IF;
973
974 -- Standard call to get message count and if count is 1, get message info.
975 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
976 EXCEPTION
977 WHEN fnd_api.g_exc_error
978 THEN
979 ROLLBACK TO delete_comp_plan;
980 x_return_status := fnd_api.g_ret_sts_error;
981 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
982 WHEN fnd_api.g_exc_unexpected_error
983 THEN
984 ROLLBACK TO delete_comp_plan;
985 x_return_status := fnd_api.g_ret_sts_unexp_error;
986 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
987 WHEN OTHERS
988 THEN
989 ROLLBACK TO delete_comp_plan;
990 x_return_status := fnd_api.g_ret_sts_unexp_error;
991
992 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
993 THEN
994 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
995 END IF;
996
997 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
998 END delete_comp_plan;
999
1000 -- Start of comments
1001 -- API name : Get_Comp_Plan_Sum
1002 -- Type : Private.
1003 -- Function :
1004 -- Pre-reqs : None.
1005 -- Parameters :
1006 -- IN : p_api_version IN NUMBER Required
1007 -- p_init_msg_list IN VARCHAR2 Optional
1008 -- Default = FND_API.G_FALSE
1009 -- p_commit IN VARCHAR2 Optional
1010 -- Default = FND_API.G_FALSE
1011 -- p_validation_level IN NUMBER Optional
1012 -- Default = FND_API.G_VALID_LEVEL_FULL
1013 -- p_start_record IN NUMBER
1014 -- Default = -1
1015 -- p_fetch_size IN NUMBER
1016 -- Default = -1
1017 -- p_search_name IN VARCHAR2
1018 -- Default = '%'
1019 -- p_search_date IN DATE
1020 -- Default = FND_API.G_MISS_DATE
1021 -- p_search_status IN VARCHAR2
1022 -- Default = FND_API.G_MISS_CHAR
1023 -- OUT : x_return_status OUT VARCHAR2(1)
1024 -- x_msg_count OUT NUMBER
1025 -- x_msg_data OUT VARCHAR2(2000)
1026 -- x_comp_plan OUT comp_plan_tbl_type
1027 -- x_total_record OUT NUMBER
1028 -- Version : Current version 1.0
1029 -- Notes : Note text
1030 --
1031 -- End of comments
1032 PROCEDURE get_comp_plan_sum (
1033 p_api_version IN NUMBER,
1034 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1035 p_commit IN VARCHAR2 := fnd_api.g_false,
1036 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1037 p_start_record IN NUMBER := -1,
1038 p_fetch_size IN NUMBER := -1,
1039 p_search_name IN VARCHAR2 := '%',
1040 p_search_date IN DATE := fnd_api.g_miss_date,
1041 p_search_status IN VARCHAR2 := fnd_api.g_miss_char,
1042 x_comp_plan OUT NOCOPY comp_plan_tbl_type,
1043 x_total_record OUT NOCOPY NUMBER,
1044 x_return_status OUT NOCOPY VARCHAR2,
1045 x_msg_count OUT NOCOPY NUMBER,
1046 x_msg_data OUT NOCOPY VARCHAR2
1047 )
1048 IS
1049 l_api_name CONSTANT VARCHAR2 (30) := 'Get_Comp_Plan_Sum';
1050 l_api_version CONSTANT NUMBER := 1.0;
1051 l_counter NUMBER;
1052
1053 CURSOR l_comp_plan_cr
1054 IS
1055 SELECT *
1056 FROM cn_comp_plans
1057 WHERE UPPER (NAME) LIKE UPPER (p_search_name)
1058 AND status_code = DECODE (p_search_status, 'NULL', status_code, p_search_status)
1059 AND TRUNC (start_date) >= TRUNC (NVL (p_search_date, start_date))
1060 ORDER BY NAME;
1061 BEGIN
1062 -- Standard Start of API savepoint
1063 SAVEPOINT get_comp_plan_sum;
1064
1065 -- Standard call to check for call compatibility.
1066 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1067 THEN
1068 RAISE fnd_api.g_exc_unexpected_error;
1069 END IF;
1070
1071 -- Initialize message list if p_init_msg_list is set to TRUE.
1072 IF fnd_api.to_boolean (p_init_msg_list)
1073 THEN
1074 fnd_msg_pub.initialize;
1075 END IF;
1076
1077 -- Initialize API return status to success
1078 x_return_status := fnd_api.g_ret_sts_success;
1079 -- API body
1080 x_comp_plan := g_miss_comp_plan_rec_tb;
1081 l_counter := 0;
1082 x_total_record := 0;
1083
1084 FOR l_comp_plan IN l_comp_plan_cr
1085 LOOP
1086 x_total_record := x_total_record + 1;
1087
1088 IF (p_fetch_size = -1) OR (x_total_record >= p_start_record AND x_total_record <= (p_start_record + p_fetch_size - 1))
1089 THEN
1090 -- assign values of the row to x_srp_list
1091 l_counter := l_counter + 1;
1092 x_comp_plan (l_counter).comp_plan_id := l_comp_plan.comp_plan_id;
1093 x_comp_plan (l_counter).NAME := l_comp_plan.NAME;
1094 x_comp_plan (l_counter).description := l_comp_plan.description;
1095 x_comp_plan (l_counter).status_code := l_comp_plan.status_code;
1096 x_comp_plan (l_counter).complete_flag := l_comp_plan.complete_flag;
1097 x_comp_plan (l_counter).allow_rev_class_overlap := l_comp_plan.allow_rev_class_overlap;
1098 x_comp_plan (l_counter).start_date := l_comp_plan.start_date;
1099 x_comp_plan (l_counter).end_date := l_comp_plan.end_date;
1100 x_comp_plan (l_counter).object_version_number := l_comp_plan.object_version_number;
1101 END IF;
1102 END LOOP;
1103
1104 -- End of API body.
1105 -- Standard check of p_commit.
1106 IF fnd_api.to_boolean (p_commit)
1107 THEN
1108 COMMIT WORK;
1109 END IF;
1110
1111 -- Standard call to get message count and if count is 1, get message info.
1112 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1113 EXCEPTION
1114 WHEN fnd_api.g_exc_error
1115 THEN
1116 ROLLBACK TO get_comp_plan_sum;
1117 x_return_status := fnd_api.g_ret_sts_error;
1118 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1119 WHEN fnd_api.g_exc_unexpected_error
1120 THEN
1121 ROLLBACK TO get_comp_plan_sum;
1122 x_return_status := fnd_api.g_ret_sts_unexp_error;
1123 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1124 WHEN OTHERS
1125 THEN
1126 ROLLBACK TO get_comp_plan_sum;
1127 x_return_status := fnd_api.g_ret_sts_unexp_error;
1128
1129 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1130 THEN
1131 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1132 END IF;
1133
1134 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1135 END get_comp_plan_sum;
1136
1137 -- Start of comments
1138 -- API name : Get_Comp_Plan_Dtl
1139 -- Type : Private.
1140 -- Function :
1141 -- Pre-reqs : None.
1142 -- Parameters :
1143 -- IN : p_api_version IN NUMBER Required
1144 -- p_init_msg_list IN VARCHAR2 Optional
1145 -- Default = FND_API.G_FALSE
1146 -- p_commit IN VARCHAR2 Optional
1147 -- Default = FND_API.G_FALSE
1148 -- p_validation_level IN NUMBER Optional
1149 -- Default = FND_API.G_VALID_LEVEL_FULL
1150 -- p_comp_plan_id IN NUMBER
1151 -- OUT : x_return_status OUT VARCHAR2(1)
1152 -- x_msg_count OUT NUMBER
1153 -- x_msg_data OUT VARCHAR2(2000)
1154 -- x_comp_plan OUT comp_plan_tbl_type
1155 -- Version : Current version 1.0
1156 -- Notes : Note text
1157 --
1158 -- End of comments
1159 PROCEDURE get_comp_plan_dtl (
1160 p_api_version IN NUMBER,
1161 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1162 p_commit IN VARCHAR2 := fnd_api.g_false,
1163 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1164 p_comp_plan_id IN NUMBER,
1165 x_comp_plan OUT NOCOPY comp_plan_tbl_type,
1166 x_return_status OUT NOCOPY VARCHAR2,
1167 x_msg_count OUT NOCOPY NUMBER,
1168 x_msg_data OUT NOCOPY VARCHAR2
1169 )
1170 IS
1171 l_api_name CONSTANT VARCHAR2 (30) := 'Get_Comp_Plan_Dtl';
1172 l_api_version CONSTANT NUMBER := 1.0;
1173
1174 CURSOR l_comp_plan_cr
1175 IS
1176 SELECT *
1177 FROM cn_comp_plans
1178 WHERE comp_plan_id = p_comp_plan_id;
1179
1180 l_comp_plan l_comp_plan_cr%ROWTYPE;
1181 BEGIN
1182 -- Standard Start of API savepoint
1183 SAVEPOINT get_comp_plan_dtl;
1184
1185 -- Standard call to check for call compatibility.
1186 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1187 THEN
1188 RAISE fnd_api.g_exc_unexpected_error;
1189 END IF;
1190
1191 -- Initialize message list if p_init_msg_list is set to TRUE.
1192 IF fnd_api.to_boolean (p_init_msg_list)
1193 THEN
1194 fnd_msg_pub.initialize;
1195 END IF;
1196
1197 -- Initialize API return status to success
1198 x_return_status := fnd_api.g_ret_sts_success;
1199
1200 -- API body
1201 OPEN l_comp_plan_cr;
1202
1203 LOOP
1204 FETCH l_comp_plan_cr
1205 INTO l_comp_plan;
1206
1207 EXIT WHEN l_comp_plan_cr%NOTFOUND;
1208 x_comp_plan (1).comp_plan_id := l_comp_plan.comp_plan_id;
1209 x_comp_plan (1).NAME := l_comp_plan.NAME;
1210 x_comp_plan (1).description := l_comp_plan.description;
1211 x_comp_plan (1).status_code := l_comp_plan.status_code;
1212 x_comp_plan (1).complete_flag := l_comp_plan.complete_flag;
1213 x_comp_plan (1).allow_rev_class_overlap := l_comp_plan.allow_rev_class_overlap;
1214 x_comp_plan (1).start_date := l_comp_plan.start_date;
1215 x_comp_plan (1).end_date := l_comp_plan.end_date;
1216 x_comp_plan (1).object_version_number := l_comp_plan.object_version_number;
1217 x_comp_plan (1).attribute_category := l_comp_plan.attribute_category;
1218 x_comp_plan (1).attribute1 := l_comp_plan.attribute1;
1219 x_comp_plan (1).attribute2 := l_comp_plan.attribute2;
1220 x_comp_plan (1).attribute3 := l_comp_plan.attribute3;
1221 x_comp_plan (1).attribute4 := l_comp_plan.attribute4;
1222 x_comp_plan (1).attribute5 := l_comp_plan.attribute5;
1223 x_comp_plan (1).attribute6 := l_comp_plan.attribute6;
1224 x_comp_plan (1).attribute7 := l_comp_plan.attribute7;
1225 x_comp_plan (1).attribute8 := l_comp_plan.attribute8;
1226 x_comp_plan (1).attribute9 := l_comp_plan.attribute9;
1227 x_comp_plan (1).attribute10 := l_comp_plan.attribute10;
1228 x_comp_plan (1).attribute11 := l_comp_plan.attribute11;
1229 x_comp_plan (1).attribute12 := l_comp_plan.attribute12;
1230 x_comp_plan (1).attribute13 := l_comp_plan.attribute13;
1231 x_comp_plan (1).attribute14 := l_comp_plan.attribute14;
1232 x_comp_plan (1).attribute15 := l_comp_plan.attribute15;
1233 END LOOP;
1234
1235 IF l_comp_plan_cr%ROWCOUNT = 0
1236 THEN
1237 x_comp_plan := g_miss_comp_plan_rec_tb;
1238 END IF;
1239
1240 CLOSE l_comp_plan_cr;
1241
1242 -- End of API body.
1243 -- Standard check of p_commit.
1244 IF fnd_api.to_boolean (p_commit)
1245 THEN
1246 COMMIT WORK;
1247 END IF;
1248
1249 -- Standard call to get message count and if count is 1, get message info.
1250 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1251 EXCEPTION
1252 WHEN fnd_api.g_exc_error
1253 THEN
1254 ROLLBACK TO get_comp_plan_dtl;
1255 x_return_status := fnd_api.g_ret_sts_error;
1256 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1257 WHEN fnd_api.g_exc_unexpected_error
1258 THEN
1259 ROLLBACK TO get_comp_plan_dtl;
1260 x_return_status := fnd_api.g_ret_sts_unexp_error;
1261 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1262 WHEN OTHERS
1263 THEN
1264 ROLLBACK TO get_comp_plan_dtl;
1265 x_return_status := fnd_api.g_ret_sts_unexp_error;
1266
1267 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1268 THEN
1269 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1270 END IF;
1271
1272 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1273 END get_comp_plan_dtl;
1274
1275 -- Start of comments
1276 -- API name : Get_Sales_Role
1277 -- Type : Private.
1278 -- Function :
1279 -- Pre-reqs : None.
1280 -- Parameters :
1281 -- IN : p_api_version IN NUMBER Required
1282 -- p_init_msg_list IN VARCHAR2 Optional
1283 -- Default = FND_API.G_FALSE
1284 -- p_commit IN VARCHAR2 Optional
1285 -- Default = FND_API.G_FALSE
1286 -- p_validation_level IN NUMBER Optional
1287 -- Default = FND_API.G_VALID_LEVEL_FULL
1288 -- p_comp_plan_id IN NUMBER
1289 -- OUT : x_return_status OUT VARCHAR2(1)
1290 -- x_msg_count OUT NUMBER
1291 -- x_msg_data OUT VARCHAR2(2000)
1292 -- x_sales_role OUT sales_role_tbl_type
1293 -- Version : Current version 1.0
1294 -- Notes : Note text
1295 --
1296 -- End of comments
1297 PROCEDURE get_sales_role (
1298 p_api_version IN NUMBER,
1299 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1300 p_commit IN VARCHAR2 := fnd_api.g_false,
1301 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1302 p_comp_plan_id IN NUMBER,
1303 x_sales_role OUT NOCOPY sales_role_tbl_type,
1304 x_return_status OUT NOCOPY VARCHAR2,
1305 x_msg_count OUT NOCOPY NUMBER,
1306 x_msg_data OUT NOCOPY VARCHAR2
1307 )
1308 IS
1309 l_api_name CONSTANT VARCHAR2 (30) := 'Get_Sales_Role';
1310 l_api_version CONSTANT NUMBER := 1.0;
1311
1312 CURSOR l_sales_role_cr
1313 IS
1314 SELECT r.NAME,
1315 r.description,
1316 p.start_date,
1317 p.end_date,
1318 p.object_version_number,
1319 r.role_id
1320 FROM cn_role_plans p,
1321 cn_roles r
1322 WHERE p.comp_plan_id = p_comp_plan_id AND r.role_id = p.role_id;
1323
1324 l_sales_role l_sales_role_cr%ROWTYPE;
1325 l_counter NUMBER;
1326 BEGIN
1327 -- Standard Start of API savepoint
1328 SAVEPOINT get_sales_role;
1329
1330 -- Standard call to check for call compatibility.
1331 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1332 THEN
1333 RAISE fnd_api.g_exc_unexpected_error;
1334 END IF;
1335
1336 -- Initialize message list if p_init_msg_list is set to TRUE.
1337 IF fnd_api.to_boolean (p_init_msg_list)
1338 THEN
1339 fnd_msg_pub.initialize;
1340 END IF;
1341
1342 -- Initialize API return status to success
1343 x_return_status := fnd_api.g_ret_sts_success;
1344 -- API body
1345 x_sales_role := g_miss_sales_role_rec_tb;
1346 l_counter := 0;
1347
1348 FOR l_sales_role IN l_sales_role_cr
1349 LOOP
1350 l_counter := l_counter + 1;
1351 x_sales_role (l_counter).NAME := l_sales_role.NAME;
1352 x_sales_role (l_counter).description := l_sales_role.description;
1353 x_sales_role (l_counter).start_date := l_sales_role.start_date;
1354 x_sales_role (l_counter).end_date := l_sales_role.end_date;
1355 x_sales_role (l_counter).role_id := l_sales_role.role_id;
1356 x_sales_role (l_counter).object_version_number := l_sales_role.object_version_number;
1357 END LOOP;
1358
1359 -- End of API body.
1360 -- Standard check of p_commit.
1361 IF fnd_api.to_boolean (p_commit)
1362 THEN
1363 COMMIT WORK;
1364 END IF;
1365
1366 -- Standard call to get message count and if count is 1, get message info.
1367 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1368 EXCEPTION
1369 WHEN fnd_api.g_exc_error
1370 THEN
1371 ROLLBACK TO get_sales_role;
1372 x_return_status := fnd_api.g_ret_sts_error;
1373 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1374 WHEN fnd_api.g_exc_unexpected_error
1375 THEN
1376 ROLLBACK TO get_sales_role;
1377 x_return_status := fnd_api.g_ret_sts_unexp_error;
1378 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1379 WHEN OTHERS
1380 THEN
1381 ROLLBACK TO get_sales_role;
1382 x_return_status := fnd_api.g_ret_sts_unexp_error;
1383
1384 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1385 THEN
1386 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1387 END IF;
1388
1389 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1390 END get_sales_role;
1391
1392 -- Start of comments
1393 -- API name : Validate_Comp_Plan
1394 -- Type : Private.
1395 -- Function :
1396 -- Pre-reqs : None.
1397 -- Parameters :
1398 -- IN : p_api_version IN NUMBER Required
1399 -- p_init_msg_list IN VARCHAR2 Optional
1400 -- Default = FND_API.G_FALSE
1401 -- p_commit IN VARCHAR2 Optional
1402 -- Default = FND_API.G_FALSE
1403 -- p_validation_level IN NUMBER Optional
1404 -- Default = FND_API.G_VALID_LEVEL_FULL
1405 -- p_comp_plan IN comp_plan_rec_type
1406 -- OUT : x_return_status OUT VARCHAR2(1)
1407 -- x_msg_count OUT NUMBER
1408 -- x_msg_data OUT VARCHAR2(2000)
1409 -- Version : Current version 1.0
1410 -- Notes : Note text
1411 --
1412 -- End of comments
1413 PROCEDURE validate_comp_plan (
1414 p_api_version IN NUMBER,
1415 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1416 p_commit IN VARCHAR2 := fnd_api.g_false,
1417 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1418 p_comp_plan IN comp_plan_rec_type,
1419 x_return_status OUT NOCOPY VARCHAR2,
1420 x_msg_count OUT NOCOPY NUMBER,
1421 x_msg_data OUT NOCOPY VARCHAR2
1422 )
1423 IS
1424 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_Comp_Plan';
1425 l_api_version CONSTANT NUMBER := 1.0;
1426 l_temp_count NUMBER;
1427 l_dummy_row_id VARCHAR2 (18);
1428
1429 CURSOR l_old_comp_plan_cr
1430 IS
1431 SELECT *
1432 FROM cn_comp_plans
1433 WHERE comp_plan_id = p_comp_plan.comp_plan_id;
1434
1435 l_old_comp_plan l_old_comp_plan_cr%ROWTYPE;
1436 l_loading_status VARCHAR2 (50);
1437 l_p_loading_status VARCHAR2 (50);
1438 l_return_status VARCHAR2 (50);
1439 l_incomp_forms VARCHAR2 (500);
1440 l_status_code VARCHAR2 (30);
1441
1442 CURSOR incomp_formulas
1443 IS
1444 SELECT f.NAME
1445 FROM cn_quota_assigns qa,
1446 cn_quotas_v q,
1447 cn_calc_formulas f
1448 WHERE qa.comp_plan_id = p_comp_plan.comp_plan_id
1449 AND q.quota_id = qa.quota_id
1450 AND q.calc_formula_id = f.calc_formula_id
1451 AND f.formula_status = 'INCOMPLETE';
1452 BEGIN
1453 -- Standard Start of API savepoint
1454 SAVEPOINT validate_comp_plan;
1455
1456 -- Standard call to check for call compatibility.
1457 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1458 THEN
1459 RAISE fnd_api.g_exc_unexpected_error;
1460 END IF;
1461
1462 -- Initialize message list if p_init_msg_list is set to TRUE.
1463 IF fnd_api.to_boolean (p_init_msg_list)
1464 THEN
1465 fnd_msg_pub.initialize;
1466 END IF;
1467
1468 -- Initialize API return status to success
1469 x_return_status := fnd_api.g_ret_sts_success;
1470
1471 -- API body
1472 -- *** Check the ORG_ID is null or not ***
1473 --check_org_id(p_comp_plan.org_id);
1474
1475 -- 1. need to have plan element assigned
1476 SELECT COUNT (1)
1477 INTO l_temp_count
1478 FROM cn_quota_assigns
1479 WHERE comp_plan_id = p_comp_plan.comp_plan_id AND ROWNUM = 1;
1480
1481 IF l_temp_count = 0
1482 THEN
1483 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1484 THEN
1485 fnd_message.set_name ('CN', 'CN_NO_PE_ASSIGNED');
1486 fnd_msg_pub.ADD;
1487 END IF;
1488
1489 RAISE fnd_api.g_exc_error;
1490 END IF;
1491
1492 -- 2. cannot have incomplete formula assigned
1493 FOR f IN incomp_formulas
1494 LOOP
1495 IF l_incomp_forms IS NOT NULL
1496 THEN
1497 l_incomp_forms := l_incomp_forms || ', ';
1498 END IF;
1499
1500 l_incomp_forms := l_incomp_forms || f.NAME;
1501 END LOOP;
1502
1503 IF l_incomp_forms IS NOT NULL
1504 THEN
1505 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1506 THEN
1507 fnd_message.set_name ('CN', 'CN_INCOMP_FORMULA');
1508 fnd_message.set_token ('FORMULA_NAME', l_incomp_forms);
1509 fnd_msg_pub.ADD;
1510 END IF;
1511
1512 RAISE fnd_api.g_exc_error;
1513 END IF;
1514
1515 OPEN l_old_comp_plan_cr;
1516
1517 FETCH l_old_comp_plan_cr
1518 INTO l_old_comp_plan;
1519
1520 CLOSE l_old_comp_plan_cr;
1521
1522 l_p_loading_status := 'VALID_PLAN';
1523 cn_comp_plan_pvt.check_revenue_class_overlap(p_comp_plan_id => p_comp_plan.comp_plan_id,
1524 p_rc_overlap => l_old_comp_plan.allow_rev_class_overlap,
1525 p_loading_status => l_p_loading_status,
1526 x_loading_status => l_loading_status,
1527 x_return_status => x_return_status
1528 );
1529
1530 IF l_loading_status = 'VALID_PLAN' THEN
1531
1532 -- call table handler
1533 cn_comp_plans_pkg.end_record (x_rowid => l_dummy_row_id,
1534 x_comp_plan_id => p_comp_plan.comp_plan_id,
1535 x_name => l_old_comp_plan.NAME,
1536 x_description => l_old_comp_plan.description,
1537 x_start_date => l_old_comp_plan.start_date,
1538 x_end_date => l_old_comp_plan.end_date,
1539 x_program_type => NULL,
1540 x_status_code => l_old_comp_plan.status_code,
1541 x_allow_rev_class_overlap => l_old_comp_plan.allow_rev_class_overlap
1542 );
1543 END IF;
1544
1545 SELECT status_code
1546 INTO l_status_code
1547 FROM cn_comp_plans
1548 WHERE comp_plan_id = p_comp_plan.comp_plan_id;
1549
1550 IF l_status_code <> 'COMPLETE'
1551 THEN
1552 x_return_status := fnd_api.g_ret_sts_error;
1553 --RAISE fnd_api.g_exc_error;
1554 END IF;
1555
1556 -- End of API body.
1557 -- Standard check of p_commit.
1558 IF fnd_api.to_boolean (p_commit)
1559 THEN
1560 COMMIT WORK;
1561 END IF;
1562
1563 -- Standard call to get message count and if count is 1, get message info.
1564 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1565 EXCEPTION
1566 WHEN fnd_api.g_exc_error
1567 THEN
1568 ROLLBACK TO validate_comp_plan;
1569 x_return_status := fnd_api.g_ret_sts_error;
1570 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1571 WHEN fnd_api.g_exc_unexpected_error
1572 THEN
1573 ROLLBACK TO validate_comp_plan;
1574 x_return_status := fnd_api.g_ret_sts_unexp_error;
1575 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1576 WHEN OTHERS
1577 THEN
1578 ROLLBACK TO validate_comp_plan;
1579 x_return_status := fnd_api.g_ret_sts_unexp_error;
1580
1581 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1582 THEN
1583 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1584 END IF;
1585
1586 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1587 END validate_comp_plan;
1588
1589 --| -----------------------------------------------------------------------=
1590 --| Procedure Name : check_revenue_class_overlap
1591 --| Desc : Pass in Comp Plan ID
1592 --| pass in Comp Plan Name
1593 --| Note: Comented out the overlap check
1594 --| ---------------------------------------------------------------------=
1595 PROCEDURE check_revenue_class_overlap (
1596 p_comp_plan_id IN NUMBER,
1597 p_rc_overlap IN VARCHAR2,
1598 p_loading_status IN VARCHAR2,
1599 x_loading_status OUT NOCOPY VARCHAR2,
1600 x_return_status OUT NOCOPY VARCHAR2
1601 )
1602 IS
1603 l_rev_class_total NUMBER := 0;
1604 l_rev_class_total_unique NUMBER := 0;
1605 l_comp_plan_name cn_comp_plans.NAME%TYPE;
1606 l_rc_overlap VARCHAR2 (03);
1607
1608 /* CURSOR check_overlap_curs IS
1609 SELECT count(value_external_id), count( distinct value_external_id)
1610 FROM cn_dim_explosion de,
1611 cn_quota_rules qr,
1612 cn_quota_assigns qa,
1613 cn_quotas_v q,
1614 cn_dim_hier_periods dh,
1615 cn_periods cp
1616 WHERE dh.header_hierarchy_id = cn_global_var.g_rev_class_hierarchy_id
1617 AND cp.start_date >= q.start_date
1618 AND cp.end_date <= nvl(q.end_date,cp.end_date)
1619 AND cp.period_id = dh.period_id
1620 AND de.dim_hierarchy_id = dh.dim_hierarchy_id
1621 AND de.ancestor_external_id = qr.revenue_class_id
1622 AND qr.quota_id = qa.quota_id
1623 AND qa.comp_plan_id = p_comp_plan_id
1624 AND qa.quota_id = q.quota_id
1625 AND q.quota_type_code IN ('EXTERNAL', 'FORMULA')
1626 GROUP BY cp.period_id
1627 HAVING count(value_external_id) <> count( distinct value_external_id)
1628 ;
1629 */
1630
1631 -- Since cn_dim_hier_periods is gone, we also need to change this
1632 /*
1633 CURSOR check_overlap_curs
1634 IS
1635 SELECT COUNT (de.value_external_id),COUNT (distinct de.value_external_id)
1636 FROM cn_dim_explosion de,
1637 cn_quota_rules qr,
1638 cn_quota_assigns qa,
1639
1640 (select q1.quota_id, q1.start_date, q1.end_Date
1641 from cn_quotas_v q1, cn_quota_assigns qa
1642 where
1643 qa.comp_plan_id = p_comp_plan_id
1644 and qa.quota_id = q1.quota_id
1645 and exists
1646 (
1647 select 1 from cn_quotas_v q2, cn_quota_assigns qa1
1648 where
1649 qa1.comp_plan_id = p_comp_plan_id
1650 and qa1.quota_id = q2.quota_id
1651 and q1.quota_id <> q2.quota_id
1652 and ((q1.end_date is null OR trunc(q1.end_date) >= trunc(q2.start_Date))
1653 AND (q2.end_date is null OR trunc(q1.start_date) <= trunc(q2.end_date)))
1654
1655 )
1656 ) q,
1657
1658 cn_dim_hierarchies dh
1659 WHERE
1660 ( (q.end_date is null OR trunc(q.end_date) >= trunc(dh.start_date))
1661 AND (dh.end_date is null OR trunc(q.start_date) <= trunc(dh.end_date)) )
1662 AND de.dim_hierarchy_id = dh.dim_hierarchy_id
1663 AND de.ancestor_external_id = qr.revenue_class_id
1664 AND qr.quota_id = qa.quota_id
1665 AND qa.comp_plan_id = p_comp_plan_id
1666 AND qa.quota_id = q.quota_id; */
1667
1668 result boolean;
1669
1670 BEGIN
1671 x_return_status := fnd_api.g_ret_sts_success;
1672 x_loading_status := p_loading_status;
1673
1674 BEGIN
1675 SELECT NAME,
1676 NVL (p_rc_overlap, allow_rev_class_overlap)
1677 INTO l_comp_plan_name,
1678 l_rc_overlap
1679 FROM cn_comp_plans
1680 WHERE comp_plan_id = p_comp_plan_id;
1681 EXCEPTION
1682 WHEN NO_DATA_FOUND
1683 THEN
1684 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1685 THEN
1686 fnd_message.set_name ('CN', 'CN_CP_NOT_EXIST');
1687 fnd_msg_pub.ADD;
1688 END IF;
1689
1690 x_loading_status := 'CN_CP_NOT_EXIST';
1691 x_return_status := fnd_api.g_ret_sts_error;
1692 END;
1693
1694 IF l_rc_overlap = 'N'
1695 THEN
1696
1697 /* OPEN check_overlap_curs;
1698
1699 FETCH check_overlap_curs
1700 INTO l_rev_class_total,
1701 l_rev_class_total_unique;
1702
1703 CLOSE check_overlap_curs; */
1704
1705 result := CN_COMP_PLANS_PKG.check_unique_rev_class(p_comp_plan_id, l_comp_plan_name, l_rc_overlap);
1706
1707
1708 IF (result = false)
1709 THEN
1710
1711 /* IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1712 THEN
1713 fnd_message.set_name ('CN', 'PLN_PLAN_DUP_REV_CLASS');
1714 fnd_message.set_token ('PLAN_NAME', l_comp_plan_name);
1715 fnd_msg_pub.ADD;
1716 END IF; */
1717
1718 x_loading_status := 'PLN_PLAN_DUP_REV_CLASS';
1719 x_return_status := fnd_api.g_ret_sts_error;
1720 END IF;
1721 END IF;
1722 END check_revenue_class_overlap;
1723
1724 -- Get salespeople assigned to the plan
1725 PROCEDURE get_assigned_salesreps (
1726 p_comp_plan_id IN NUMBER,
1727 p_range_low IN NUMBER,
1728 p_range_high IN NUMBER,
1729 x_total_rows OUT NOCOPY NUMBER,
1730 x_result_tbl OUT NOCOPY srp_plan_assign_tbl_type
1731 )
1732 IS
1733 l_index NUMBER := 0;
1734
1735 CURSOR get_data
1736 IS
1737 SELECT spa.srp_plan_assign_id,
1738 spa.salesrep_id,
1739 spa.role_id,
1740 r.NAME role_name,
1741 s.NAME salesrep_name,
1742 s.employee_number,
1743 spa.start_date,
1744 spa.end_date
1745 FROM cn_srp_plan_assigns spa,
1746 cn_salesreps s,
1747 cn_roles r,
1748 cn_srp_roles sr,
1749 cn_role_plans rp
1750 WHERE spa.comp_plan_id = p_comp_plan_id
1751 AND spa.salesrep_id = s.salesrep_id
1752 AND spa.role_id = r.role_id
1753 AND sr.srp_role_id = spa.srp_role_id
1754 AND rp.role_plan_id = spa.role_plan_id
1755 ORDER BY s.NAME;
1756 BEGIN
1757 x_total_rows := 0;
1758
1759 FOR c IN get_data
1760 LOOP
1761 x_total_rows := x_total_rows + 1;
1762
1763 IF x_total_rows BETWEEN p_range_low AND p_range_high
1764 THEN
1765 l_index := l_index + 1;
1766 x_result_tbl (l_index) := c;
1767 END IF;
1768 END LOOP;
1769 END get_assigned_salesreps;
1770
1771
1772 -- =====================================================
1773 -- || Procedure: Duplicate_Comp_plan
1774 -- || Description: This Procedure creates a copy of Compplan
1775 -- || in the same Instance and Operating Unit.
1776 -- || This is a Shallow Copy means Children components
1777 -- || are not copied. Children components from the
1778 -- || original Compplan will point to this new
1779 -- || Compplan.
1780 -- =====================================================
1781 PROCEDURE duplicate_comp_plan (
1782 p_api_version IN NUMBER,
1783 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1784 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1785 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1786 p_comp_plan_id IN CN_COMP_PLANS.COMP_PLAN_ID%TYPE,
1787 p_org_id IN NUMBER,
1788 x_return_status OUT NOCOPY VARCHAR2,
1789 x_msg_count OUT NOCOPY NUMBER,
1790 x_msg_data OUT NOCOPY VARCHAR2,
1791 x_comp_plan_id OUT NOCOPY CN_COMP_PLANS.COMP_PLAN_ID%TYPE) IS
1792 l_api_name CONSTANT VARCHAR2(30) := 'Duplicate_Comp_Plan';
1793
1794 l_api_version CONSTANT NUMBER := 1.0;
1795
1796
1797 CURSOR get_comp_plan_data IS
1798 Select * from cn_comp_plans_all
1799 Where comp_plan_id = p_comp_plan_id
1800 And org_id = p_org_id;
1801
1802 CURSOR get_quota_assign_data IS
1803 Select * from cn_quota_assigns_all
1804 Where comp_plan_id = p_comp_plan_id;
1805
1806 l_comp_plan get_comp_plan_data%rowtype;
1807 l_comp_plan_rec comp_plan_rec_type;
1808
1809 l_quota_assign_rec CN_QUOTA_ASSIGN_PVT.quota_assign_rec_type;
1810
1811 l_unique_name varchar2(30);
1812
1813 BEGIN
1814
1815 -- Standard Start of API savepoint
1816
1817 SAVEPOINT duplicate_comp_plan;
1818
1819 -- Standard call to check for call compatibility.
1820
1821 IF NOT FND_API.Compatible_API_Call (l_api_version ,
1822 p_api_version ,
1823 l_api_name,
1824 G_PKG_NAME )
1825 THEN
1826
1827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1828
1829 END IF;
1830
1831 -- Initialize message list if p_init_msg_list is set to TRUE.
1832
1833 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1834
1835 FND_MSG_PUB.initialize;
1836
1837 END IF;
1838
1839 -- Initialize API return status to success
1840
1841 x_return_status := FND_API.G_RET_STS_SUCCESS;
1842
1843
1844 Open get_comp_plan_data;
1845 fetch get_comp_plan_data into l_comp_plan;
1846
1847 IF get_comp_plan_data%notfound THEN
1848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1849 END IF;
1850
1851 -- calling public api
1852 SELECT
1853 DECODE (l_comp_plan.description, fnd_api.g_miss_char, NULL, l_comp_plan.description),
1854 'INCOMPLETE',
1855 DECODE (l_comp_plan.allow_rev_class_overlap, fnd_api.g_miss_char, NULL, l_comp_plan.allow_rev_class_overlap),
1856 DECODE (l_comp_plan.start_date, fnd_api.g_miss_date, NULL, TRUNC (l_comp_plan.start_date)),
1857 DECODE (l_comp_plan.end_date, fnd_api.g_miss_date, NULL, TRUNC (l_comp_plan.end_date)),
1858 DECODE (l_comp_plan.attribute_category, fnd_api.g_miss_char, NULL, l_comp_plan.attribute_category),
1859 DECODE (l_comp_plan.attribute1, fnd_api.g_miss_char, NULL, l_comp_plan.attribute1),
1860 DECODE (l_comp_plan.attribute2, fnd_api.g_miss_char, NULL, l_comp_plan.attribute2),
1861 DECODE (l_comp_plan.attribute3, fnd_api.g_miss_char, NULL, l_comp_plan.attribute3),
1862 DECODE (l_comp_plan.attribute4, fnd_api.g_miss_char, NULL, l_comp_plan.attribute4),
1863 DECODE (l_comp_plan.attribute5, fnd_api.g_miss_char, NULL, l_comp_plan.attribute5),
1864 DECODE (l_comp_plan.attribute6, fnd_api.g_miss_char, NULL, l_comp_plan.attribute6),
1865 DECODE (l_comp_plan.attribute7, fnd_api.g_miss_char, NULL, l_comp_plan.attribute7),
1866 DECODE (l_comp_plan.attribute8, fnd_api.g_miss_char, NULL, l_comp_plan.attribute8),
1867 DECODE (l_comp_plan.attribute9, fnd_api.g_miss_char, NULL, l_comp_plan.attribute9),
1868 DECODE (l_comp_plan.attribute10, fnd_api.g_miss_char, NULL, l_comp_plan.attribute10),
1869 DECODE (l_comp_plan.attribute11, fnd_api.g_miss_char, NULL, l_comp_plan.attribute11),
1870 DECODE (l_comp_plan.attribute12, fnd_api.g_miss_char, NULL, l_comp_plan.attribute12),
1871 DECODE (l_comp_plan.attribute13, fnd_api.g_miss_char, NULL, l_comp_plan.attribute13),
1872 DECODE (l_comp_plan.attribute14, fnd_api.g_miss_char, NULL, l_comp_plan.attribute14),
1873 DECODE (l_comp_plan.attribute15, fnd_api.g_miss_char, NULL, l_comp_plan.attribute15),
1874 DECODE (l_comp_plan.org_id, fnd_api.g_miss_char, NULL, l_comp_plan.org_id)
1875 INTO
1876 l_comp_plan_rec.description,
1877 l_comp_plan_rec.status_code,
1878 l_comp_plan_rec.allow_rev_class_overlap,
1879 l_comp_plan_rec.start_date,
1880 l_comp_plan_rec.end_date,
1881 l_comp_plan_rec.attribute_category,
1882 l_comp_plan_rec.attribute1,
1883 l_comp_plan_rec.attribute2,
1884 l_comp_plan_rec.attribute3,
1885 l_comp_plan_rec.attribute4,
1886 l_comp_plan_rec.attribute5,
1887 l_comp_plan_rec.attribute6,
1888 l_comp_plan_rec.attribute7,
1889 l_comp_plan_rec.attribute8,
1890 l_comp_plan_rec.attribute9,
1891 l_comp_plan_rec.attribute10,
1892 l_comp_plan_rec.attribute11,
1893 l_comp_plan_rec.attribute12,
1894 l_comp_plan_rec.attribute13,
1895 l_comp_plan_rec.attribute14,
1896 l_comp_plan_rec.attribute15,
1897 l_comp_plan_rec.org_id
1898 FROM DUAL;
1899
1900 l_comp_plan_rec.object_version_number := 1;
1901
1902 CN_PLANCOPY_UTIL_PVT.get_unique_name_for_component(p_comp_plan_id,
1903 p_org_id,'PLAN',null,null,l_unique_name,
1904 x_return_status,x_msg_count,
1905 x_msg_data);
1906
1907 IF (x_return_status <> fnd_api.g_ret_sts_success)
1908 THEN
1909 RAISE fnd_api.g_exc_error;
1910 END IF;
1911
1912 l_comp_plan_rec.name := l_unique_name;
1913
1914 create_comp_plan(p_api_version,FND_API.G_FALSE,
1915 FND_API.G_FALSE,p_validation_level,
1916 l_comp_plan_rec,x_comp_plan_id,x_return_status,
1917 x_msg_count,x_msg_data);
1918
1919 IF (x_return_status <> fnd_api.g_ret_sts_success)
1920 THEN
1921 RAISE fnd_api.g_exc_error;
1922 END IF;
1923
1924 FOR j in get_quota_assign_data
1925 LOOP
1926
1927 l_quota_assign_rec.comp_plan_id := x_comp_plan_id;
1928 l_quota_assign_rec.quota_id := j.quota_id;
1929 l_quota_assign_rec.org_id := j.org_id;
1930 l_quota_assign_rec.object_version_number := 1;
1931 l_quota_assign_rec.quota_sequence := j.quota_sequence;
1932
1933 CN_QUOTA_ASSIGN_PVT.create_quota_assign(p_api_version,FND_API.G_FALSE,
1934 FND_API.G_FALSE,p_validation_level,
1935 l_quota_assign_rec,x_return_status,
1936 x_msg_count,x_msg_data);
1937
1938 IF (x_return_status <> fnd_api.g_ret_sts_success)
1939 THEN
1940 RAISE fnd_api.g_exc_error;
1941 END IF;
1942 END LOOP;
1943
1944
1945
1946 -- Standard check of p_commit.
1947
1948 IF FND_API.To_Boolean( p_commit ) THEN
1949 COMMIT WORK;
1950 END IF;
1951
1952 -- Standard call to get message count and if count is 1, get message info.
1953
1954 FND_MSG_PUB.Count_And_Get
1955 (p_count => x_msg_count ,
1956 p_data => x_msg_data ,
1957 p_encoded => FND_API.G_FALSE );
1958
1959 EXCEPTION
1960
1961 WHEN FND_API.G_EXC_ERROR THEN
1962 ROLLBACK TO duplicate_comp_plan;
1963 x_return_status := FND_API.G_RET_STS_ERROR ;
1964 FND_MSG_PUB.count_and_get
1965 (p_count => x_msg_count ,
1966 p_data => x_msg_data ,
1967 p_encoded => FND_API.G_FALSE );
1968
1969 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1970 ROLLBACK TO duplicate_comp_plan;
1971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1972 FND_MSG_PUB.count_and_get
1973 (p_count => x_msg_count ,
1974 p_data => x_msg_data ,
1975 p_encoded => FND_API.G_FALSE );
1976
1977 WHEN OTHERS THEN
1978 ROLLBACK TO duplicate_comp_plan;
1979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1980 IF FND_MSG_PUB.check_msg_level
1981 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1982 THEN
1983 FND_MSG_PUB.add_exc_msg
1984 (G_PKG_NAME ,
1985 l_api_name );
1986 END IF;
1987
1988 FND_MSG_PUB.count_and_get
1989 (p_count => x_msg_count ,
1990 p_data => x_msg_data ,
1991 p_encoded => FND_API.G_FALSE );
1992
1993 END duplicate_comp_plan;
1994
1995
1996
1997 END cn_comp_plan_pvt;