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