[Home] [Help]
PACKAGE BODY: APPS.CN_QUOTA_ASSIGN_PVT
Source
1 PACKAGE BODY cn_quota_assign_pvt AS
2 /*$Header: cnvpnagb.pls 120.9 2006/05/11 06:03:40 kjayapau ship $*/
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CN_QUOTA_ASSIGN_PVT';
4 g_end_of_time CONSTANT DATE := TO_DATE ('12-31-9999', 'MM-DD-YYYY');
5
6 -- Start of comments
7 -- API name : Create_Quota_Assign
8 -- Type : Private.
9 -- Function :
10 -- Pre-reqs : None.
11 -- Parameters :
12 -- IN : p_api_version IN NUMBER Required
13 -- p_init_msg_list IN VARCHAR2 Optional
14 -- Default = FND_API.G_FALSE
15 -- p_commit IN VARCHAR2 Optional
16 -- Default = FND_API.G_FALSE
17 -- p_validation_level IN NUMBER Optional
18 -- Default = FND_API.G_VALID_LEVEL_FULL
19 -- p_quota_assign IN quota_assign_rec_type
20 -- OUT : x_return_status OUT VARCHAR2(1)
21 -- x_msg_count OUT NUMBER
22 -- x_msg_data OUT VARCHAR2(2000)
23 -- Version : Current version 1.0
24 -- Notes : Note text
25 --
26 -- End of comments
27 PROCEDURE create_quota_assign (
28 p_api_version IN NUMBER,
29 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
30 p_commit IN VARCHAR2 := fnd_api.g_false,
31 p_validation_level IN NUMBER
32 := fnd_api.g_valid_level_full,
33 p_quota_assign IN OUT NOCOPY quota_assign_rec_type,
34 x_return_status OUT NOCOPY VARCHAR2,
35 x_msg_count OUT NOCOPY NUMBER,
36 x_msg_data OUT NOCOPY VARCHAR2
37 )
38 IS
39 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Quota_Assign';
40 l_api_version CONSTANT NUMBER := 1.0;
41 l_temp_count NUMBER;
42 l_msg_count NUMBER;
43 l_msg_data VARCHAR2 (2000);
44 l_quota_id cn_quotas.quota_id%TYPE;
45 l_quota_assign_id cn_quota_assigns.quota_assign_id%TYPE;
46 l_comp_plan_id cn_comp_plans.comp_plan_id%TYPE;
47 l_org_id cn_quota_assigns.org_id%TYPE;
48 l_quota_tbl cn_calc_sql_exps_pvt.num_tbl_type;
49 l_note_msg VARCHAR2 (240);
50 l_note_id NUMBER;
51 l_cp_name cn_comp_plans.NAME%TYPE;
52 l_pe_name cn_quotas.NAME%TYPE;
53
54 CURSOR objversion_cur IS
55 SELECT object_version_number
56 FROM cn_quota_assigns
57 WHERE quota_assign_id = p_quota_assign.quota_assign_id;
58
59 BEGIN
60 -- Standard Start of API savepoint
61 SAVEPOINT create_quota_assign;
62
63 -- Standard call to check for call compatibility.
64 IF NOT fnd_api.compatible_api_call (l_api_version,
65 p_api_version,
66 l_api_name,
67 g_pkg_name
68 )
69 THEN
70 RAISE fnd_api.g_exc_unexpected_error;
71 END IF;
72
73 -- Initialize message list if p_init_msg_list is set to TRUE.
74 IF fnd_api.to_boolean (p_init_msg_list)
75 THEN
76 fnd_msg_pub.initialize;
77 END IF;
78
79 -- Initialize API return status to success
80 x_return_status := fnd_api.g_ret_sts_success;
81
82 -- API body
83
84 -- Convert fnd_api.g_miss to NULL
85
86 -- 1. name can not be null
87 IF (p_quota_assign.comp_plan_id IS NULL)
88 OR (p_quota_assign.comp_plan_id = fnd_api.g_miss_num)
89 OR (p_quota_assign.quota_id IS NULL)
90 OR (p_quota_assign.quota_id = fnd_api.g_miss_num)
91 THEN
92 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
93 THEN
94 fnd_message.set_name ('CN', 'CN_REQ_PAR_MISSING');
95 fnd_msg_pub.ADD;
96 END IF;
97
98 RAISE fnd_api.g_exc_error;
99 END IF;
100
101 -- 2. quota assign name must be unique
102 SELECT COUNT (1)
103 INTO l_temp_count
104 FROM cn_quota_assigns
105 WHERE comp_plan_id = p_quota_assign.comp_plan_id
106 AND quota_id = p_quota_assign.quota_id
107 AND ROWNUM = 1;
108
109 IF l_temp_count <> 0
110 THEN
111 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
112 THEN
113 fnd_message.set_name ('CN', 'CN_INPUT_MUST_UNIQUE');
114 fnd_message.set_token ('INPUT_NAME',
115 cn_api.get_lkup_meaning ('PE_NAME',
116 'INPUT_TOKEN'
117 )
118 );
119 fnd_msg_pub.ADD;
120 END IF;
121
122 RAISE fnd_api.g_exc_error;
123 END IF;
124
125 -- 3. quota dates must overlap plan dates
126 SELECT COUNT (1)
127 INTO l_temp_count
128 FROM cn_comp_plans c, cn_quotas_v q
129 WHERE c.comp_plan_id = p_quota_assign.comp_plan_id
130 AND q.quota_id = p_quota_assign.quota_id
131 AND GREATEST (c.start_date, q.start_date) <=
132 LEAST (NVL (c.end_date, g_end_of_time),
133 NVL (q.end_date, g_end_of_time)
134 );
135
136 IF l_temp_count = 0
137 THEN
138 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
139 THEN
140 fnd_message.set_name ('CN', 'CN_PLAN_ELT_DISJOINT');
141 fnd_msg_pub.ADD;
142 END IF;
143
144 RAISE fnd_api.g_exc_error;
145 END IF;
146
147 -- 4. interdependent quotas must reference only quotas with lower
148 -- sequence number and must reference quotas in same plan
149 IF p_quota_assign.IDQ_FLAG is null THEN
150
151 cn_calc_sql_exps_pvt.get_dependent_plan_elts
152 (p_api_version => 1.0,
153 p_node_type => 'P',
154 p_node_id => p_quota_assign.quota_id,
155 x_plan_elt_id_tbl => l_quota_tbl,
156 x_return_status => x_return_status,
157 x_msg_count => l_msg_count,
158 x_msg_data => l_msg_data
159 );
160
161 FOR i IN 0 .. l_quota_tbl.COUNT - 1
162 LOOP
163 -- for each PE in this loop, make sure it exists in plan with
164 -- lower seq number
165 SELECT COUNT (1)
166 INTO l_temp_count
167 FROM cn_quota_assigns
168 WHERE comp_plan_id = p_quota_assign.comp_plan_id
169 AND quota_id = l_quota_tbl (i)
170 AND quota_sequence < p_quota_assign.quota_sequence;
171
172 IF l_temp_count = 0
173 THEN
174 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
175 THEN
176 fnd_message.set_name ('CN', 'CN_IDQ_REFERENCE_NOT_VALID');
177 fnd_msg_pub.ADD;
178 END IF;
179
180 RAISE fnd_api.g_exc_error;
181 END IF;
182 END LOOP;
183
184 END IF;
185
186 -- do comp plan quota assignment
187 SELECT DECODE (p_quota_assign.quota_id,
188 fnd_api.g_miss_num, NULL,
189 p_quota_assign.quota_id
190 ),
191 DECODE (p_quota_assign.comp_plan_id,
192 fnd_api.g_miss_num, NULL,
193 p_quota_assign.comp_plan_id
194 ),
195 DECODE (p_quota_assign.org_id,
196 fnd_api.g_miss_num, NULL,
197 p_quota_assign.org_id
198 )
199 INTO l_quota_id,
200 l_comp_plan_id,
201 l_org_id
202 FROM DUAL;
203
204 cn_quota_assigns_pkg.begin_record
205 (x_operation => 'INSERT',
206 x_quota_id => l_quota_id,
207 x_comp_plan_id => l_comp_plan_id,
208 x_quota_assign_id => p_quota_assign.quota_assign_id,
209 x_quota_sequence => p_quota_assign.quota_sequence,
210 x_quota_id_old => NULL,
211 x_org_id => l_org_id
212 );
213 cn_comp_plans_pkg.set_status (x_comp_plan_id => l_comp_plan_id,
214 x_quota_id => NULL,
215 x_rate_schedule_id => NULL,
216 x_status_code => 'INCOMPLETE',
217 x_event => NULL
218 );
219
220
221 IF (l_quota_id > 0 AND l_comp_plan_id > 0)
222 THEN
223 SELECT NAME
224 INTO l_pe_name
225 FROM cn_quotas_all
226 WHERE quota_id = l_quota_id;
227
228 SELECT NAME
229 INTO l_cp_name
230 FROM cn_comp_plans
231 WHERE comp_plan_id = l_comp_plan_id;
232
233 fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_ASPE_AS');
234 fnd_message.set_token ('PE_NAME', l_pe_name);
235 fnd_message.set_token ('CP_NAME', l_cp_name);
236 l_note_msg := fnd_message.get;
237 jtf_notes_pub.create_note
238 (p_api_version => 1.0,
239 x_return_status => x_return_status,
240 x_msg_count => x_msg_count,
241 x_msg_data => x_msg_data,
242 p_source_object_id => l_comp_plan_id,
243 --l_quota_assign_id,
244 p_source_object_code => 'CN_COMP_PLANS',
245 --'CN_QUOTA_ASSIGNS',
246 p_notes => l_note_msg,
247 p_notes_detail => l_note_msg,
248 p_note_type => 'CN_SYSGEN',
249 -- for system generated
250 x_jtf_note_id => l_note_id -- returned
251 );
252 END IF;
253
254
255 OPEN objversion_cur;
256 FETCH objversion_cur INTO p_quota_assign.OBJECT_VERSION_NUMBER;
257 CLOSE objversion_cur;
258
259 -- End of API body.
260 -- Standard check of p_commit.
261 IF fnd_api.to_boolean (p_commit)
262 THEN
263 COMMIT WORK;
264 END IF;
265
266 -- Standard call to get message count and if count is 1, get message info.
267 fnd_msg_pub.count_and_get (p_count => x_msg_count,
268 p_data => x_msg_data,
269 p_encoded => fnd_api.g_false
270 );
271 EXCEPTION
272 WHEN fnd_api.g_exc_error
273 THEN
274 ROLLBACK TO create_quota_assign;
275 x_return_status := fnd_api.g_ret_sts_error;
276 fnd_msg_pub.count_and_get (p_count => x_msg_count,
277 p_data => x_msg_data,
278 p_encoded => fnd_api.g_false
279 );
280 WHEN fnd_api.g_exc_unexpected_error
281 THEN
282 ROLLBACK TO create_quota_assign;
283 x_return_status := fnd_api.g_ret_sts_unexp_error;
284 fnd_msg_pub.count_and_get (p_count => x_msg_count,
285 p_data => x_msg_data,
286 p_encoded => fnd_api.g_false
287 );
288 WHEN OTHERS
289 THEN
290 ROLLBACK TO create_quota_assign;
291 x_return_status := fnd_api.g_ret_sts_unexp_error;
292
293 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
294 THEN
295 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
296 END IF;
297
298 fnd_msg_pub.count_and_get (p_count => x_msg_count,
299 p_data => x_msg_data,
300 p_encoded => fnd_api.g_false
301 );
302 END create_quota_assign;
303
304 -- Start of comments
305 -- API name : Update_Quota_Assign
306 -- Type : Private.
307 -- Function :
308 -- Pre-reqs : None.
309 -- Parameters :
310 -- IN : p_api_version IN NUMBER Required
311 -- p_init_msg_list IN VARCHAR2 Optional
312 -- Default = FND_API.G_FALSE
313 -- p_commit IN VARCHAR2 Optional
314 -- Default = FND_API.G_FALSE
315 -- p_validation_level IN NUMBER Optional
316 -- Default = FND_API.G_VALID_LEVEL_FULL
317 -- p_quota_assign IN quota_assign_rec_type
318 -- OUT : x_return_status OUT VARCHAR2(1)
319 -- x_msg_count OUT NUMBER
320 -- x_msg_data OUT VARCHAR2(2000)
321 -- Version : Current version 1.0
322 -- Notes : Note text
323 --
324 -- End of comments
325 PROCEDURE update_quota_assign (
326 p_api_version IN NUMBER,
327 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
328 p_commit IN VARCHAR2 := fnd_api.g_false,
329 p_validation_level IN NUMBER
330 := fnd_api.g_valid_level_full,
331 p_quota_assign IN OUT NOCOPY quota_assign_rec_type,
332 x_return_status OUT NOCOPY VARCHAR2,
333 x_msg_count OUT NOCOPY NUMBER,
334 x_msg_data OUT NOCOPY VARCHAR2
335 )
336 IS
337 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Quota_Assign';
338 l_api_version CONSTANT NUMBER := 1.0;
339
340 CURSOR l_old_quota_assign_cr
341 IS
342 SELECT *
343 FROM cn_quota_assigns
344 WHERE quota_assign_id = p_quota_assign.quota_assign_id;
345
346 CURSOR objversion_cur IS
347 SELECT object_version_number
348 FROM cn_quota_assigns
349 WHERE quota_assign_id = p_quota_assign.quota_assign_id;
350
351 l_old_quota_assign l_old_quota_assign_cr%ROWTYPE;
352 l_quota_assign quota_assign_rec_type;
353 l_temp_count NUMBER;
354 l_quota_id cn_quotas.quota_id%TYPE;
355 l_quota_assign_id cn_quota_assigns.quota_assign_id%TYPE;
356 l_comp_plan_id cn_comp_plans.comp_plan_id%TYPE;
357 l_quota_tbl cn_calc_sql_exps_pvt.num_tbl_type;
358 l_msg_count NUMBER;
359 l_msg_data VARCHAR2 (2000);
360 l_org_id cn_quota_assigns.org_id%TYPE;
361 l_note_msg VARCHAR2 (240);
362 l_note_id NUMBER;
363 l_cp_name cn_comp_plans.NAME%TYPE;
364 l_pe_name cn_quotas.NAME%TYPE;
365 l_consolidated_note VARCHAR2(2000);
366 old_seq CN_QUOTA_ASSIGNS.QUOTA_SEQUENCE%TYPE;
367
368 BEGIN
369 -- Standard Start of API savepoint
370 SAVEPOINT update_quota_assign;
371
372 -- Standard call to check for call compatibility.
373 IF NOT fnd_api.compatible_api_call (l_api_version,
374 p_api_version,
375 l_api_name,
376 g_pkg_name
377 )
378 THEN
379 RAISE fnd_api.g_exc_unexpected_error;
380 END IF;
381
382 -- Initialize message list if p_init_msg_list is set to TRUE.
383 IF fnd_api.to_boolean (p_init_msg_list)
384 THEN
385 fnd_msg_pub.initialize;
386 END IF;
387
388 -- Initialize API return status to success
389 x_return_status := fnd_api.g_ret_sts_success;
390
391 -- API body
392
393 -- 1. name can not be null
394 IF (p_quota_assign.comp_plan_id IS NULL)
395 OR (p_quota_assign.quota_id IS NULL)
396 THEN
397 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
398 THEN
399 fnd_message.set_name ('CN', 'CN_REQ_PAR_MISSING');
400 fnd_msg_pub.ADD;
401 END IF;
402
403 RAISE fnd_api.g_exc_error;
404 END IF;
405
406 -- Getting Old Sequence
407 select count(1) into l_temp_count from cn_quota_assigns where quota_id = p_quota_assign.quota_id and comp_plan_id = p_quota_assign.comp_plan_id;
408
409 IF l_temp_count <> 0
410 THEN
411 select QUOTA_SEQUENCE into old_seq from cn_quota_assigns where quota_id = p_quota_assign.quota_id and comp_plan_id = p_quota_assign.comp_plan_id;
412 ELSE
413 old_seq := p_quota_assign.quota_sequence;
414 END IF;
415
416
417 -- 2. quota assign name must be unique
418 SELECT COUNT (1)
419 INTO l_temp_count
420 FROM cn_quota_assigns
421 WHERE comp_plan_id = p_quota_assign.comp_plan_id
422 AND quota_id = p_quota_assign.quota_id
423 AND quota_assign_id <> p_quota_assign.quota_assign_id
424 AND ROWNUM = 1;
425
426 IF l_temp_count <> 0
427 THEN
428 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
429 THEN
430 fnd_message.set_name ('CN', 'CN_INPUT_MUST_UNIQUE');
431 fnd_message.set_token ('INPUT_NAME',
432 cn_api.get_lkup_meaning ('PE_NAME',
433 'INPUT_TOKEN'
434 )
435 );
436 fnd_msg_pub.ADD;
437 END IF;
438
439 RAISE fnd_api.g_exc_error;
440 END IF;
441
442 OPEN l_old_quota_assign_cr;
443
444 FETCH l_old_quota_assign_cr
445 INTO l_old_quota_assign;
446
447 CLOSE l_old_quota_assign_cr;
448
449 SELECT DECODE (p_quota_assign.comp_plan_id,
450 fnd_api.g_miss_num, l_old_quota_assign.comp_plan_id,
451 p_quota_assign.comp_plan_id
452 ),
453 DECODE (p_quota_assign.quota_id,
454 fnd_api.g_miss_num, l_old_quota_assign.quota_id,
455 p_quota_assign.quota_id
456 ),
457 p_quota_assign.quota_assign_id,
458 DECODE (p_quota_assign.org_id,
459 fnd_api.g_miss_num, l_old_quota_assign.org_id,
460 p_quota_assign.org_id
461 )
462 INTO l_comp_plan_id,
463 l_quota_id,
464 l_quota_assign_id,
465 l_org_id
466 FROM DUAL;
467
468 -- 3. quota dates must overlap plan dates
469 SELECT COUNT (1)
470 INTO l_temp_count
471 FROM cn_comp_plans c, cn_quotas_v q
472 WHERE c.comp_plan_id = p_quota_assign.comp_plan_id
473 AND q.quota_id = p_quota_assign.quota_id
474 AND GREATEST (c.start_date, q.start_date) <=
475 LEAST (NVL (c.end_date, g_end_of_time),
476 NVL (q.end_date, g_end_of_time)
477 );
478
479 IF l_temp_count = 0
480 THEN
481 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
482 THEN
483 fnd_message.set_name ('CN', 'CN_PLAN_ELT_DISJOINT');
484 fnd_msg_pub.ADD;
485 END IF;
486
487 RAISE fnd_api.g_exc_error;
488 END IF;
489
490 -- 4. interdependent quotas must reference only quotas with lower
491 -- sequence number and must reference quotas in same plan
492
493 IF p_quota_assign.IDQ_FLAG is null THEN
494
495 cn_calc_sql_exps_pvt.get_dependent_plan_elts
496 (p_api_version => 1.0,
497 p_node_type => 'P',
498 p_node_id => p_quota_assign.quota_id,
499 x_plan_elt_id_tbl => l_quota_tbl,
500 x_return_status => x_return_status,
501 x_msg_count => l_msg_count,
502 x_msg_data => l_msg_data
503 );
504
505 FOR i IN 0 .. l_quota_tbl.COUNT - 1
506 LOOP
507 -- for each PE in this loop, make sure it exists in plan with
508 -- lower seq number
509 SELECT COUNT (1)
510 INTO l_temp_count
511 FROM cn_quota_assigns
512 WHERE comp_plan_id = p_quota_assign.comp_plan_id
513 AND quota_id = l_quota_tbl (i)
514 AND quota_sequence < p_quota_assign.quota_sequence;
515
516 IF l_temp_count = 0
517 THEN
518 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
519 THEN
520 fnd_message.set_name ('CN', 'CN_IDQ_REFERENCE_NOT_VALID');
521 fnd_msg_pub.ADD;
522 END IF;
523
524 RAISE fnd_api.g_exc_error;
525 END IF;
526 END LOOP;
527
528 END IF;
529
530 -- 5. check object version number
531 IF l_old_quota_assign.object_version_number <>
532 p_quota_assign.object_version_number
533 THEN
534 fnd_message.set_name ('CN', 'CN_RECORD_CHANGED');
535 fnd_msg_pub.ADD;
536 RAISE fnd_api.g_exc_error;
537 END IF;
538
539 cn_quota_assigns_pkg.begin_record
540 (x_operation => 'UPDATE',
541 x_quota_id => l_quota_id,
542 x_comp_plan_id => l_comp_plan_id,
543 x_quota_assign_id => p_quota_assign.quota_assign_id,
544 x_quota_sequence => p_quota_assign.quota_sequence,
545 x_quota_id_old => l_old_quota_assign.quota_id,
546 x_org_id => l_org_id
547 );
548 cn_comp_plans_pkg.set_status (x_comp_plan_id => l_comp_plan_id,
549 x_quota_id => NULL,
550 x_rate_schedule_id => NULL,
551 x_status_code => 'INCOMPLETE',
552 x_event => NULL
553 );
554
555 l_consolidated_note := '';
556
557 IF (p_quota_assign.quota_sequence <> old_seq)
558 THEN
559 SELECT NAME
560 INTO l_pe_name
561 FROM cn_quotas_all
562 WHERE quota_id = l_quota_id;
563
564 SELECT NAME
565 INTO l_cp_name
566 FROM cn_comp_plans
567 WHERE comp_plan_id = l_comp_plan_id;
568
569 fnd_message.set_name ('CN', 'CN_PA_CP_QA_CALC_SEQ_NOTES');
570 fnd_message.set_token ('PE_NAME', l_pe_name);
571 fnd_message.set_token ('CP_NAME', l_cp_name);
572 fnd_message.set_token ('OLD_SEQ', old_seq);
573 fnd_message.set_token ('NEW_SEQ', p_quota_assign.quota_sequence);
574 l_consolidated_note := fnd_message.get;
575
576 ELSE IF (l_quota_id > 0 AND l_comp_plan_id > 0)
577 THEN
578 SELECT NAME
579 INTO l_pe_name
580 FROM cn_quotas_all
581 WHERE quota_id = l_quota_id;
582
583 SELECT NAME
584 INTO l_cp_name
585 FROM cn_comp_plans
586 WHERE comp_plan_id = l_comp_plan_id;
587
588 fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_ASPE_AS');
589 fnd_message.set_token ('PE_NAME', l_pe_name);
590 fnd_message.set_token ('CP_NAME', l_cp_name);
591 l_note_msg := fnd_message.get;
592 l_consolidated_note := l_note_msg || fnd_global.local_chr(10);
593 /*jtf_notes_pub.create_note
594 (p_api_version => 1.0,
595 x_return_status => x_return_status,
596 x_msg_count => x_msg_count,
597 x_msg_data => x_msg_data,
598 p_source_object_id => l_comp_plan_id,
599 --l_quota_assign_id,
600 p_source_object_code => 'CN_COMP_PLANS',
601 --'CN_QUOTA_ASSIGNS',
602 p_notes => l_note_msg,
603 p_notes_detail => l_note_msg,
604 p_note_type => 'CN_SYSGEN',
605 -- for system generated
606 x_jtf_note_id => l_note_id -- returned
607 );*/
608
609 SELECT NAME
610 INTO l_pe_name
611 FROM cn_quotas_all
612 WHERE quota_id = l_old_quota_assign.quota_id;
613
614 fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_ASPE_UNAS');
615 fnd_message.set_token ('PE_NAME', l_pe_name);
616 fnd_message.set_token ('CP_NAME', l_cp_name);
617 l_note_msg := fnd_message.get;
618 l_consolidated_note := l_consolidated_note || l_note_msg ||fnd_global.local_chr(10);
619
620 /*jtf_notes_pub.create_note
621 (p_api_version => 1.0,
622 x_return_status => x_return_status,
623 x_msg_count => x_msg_count,
624 x_msg_data => x_msg_data,
625 p_source_object_id => l_comp_plan_id,
626 p_source_object_code => 'CN_COMP_PLANS',
627 p_notes => l_note_msg,
628 p_notes_detail => l_note_msg,
629 p_note_type => 'CN_SYSGEN',
630 -- for system generated
631 x_jtf_note_id => l_note_id -- returned
632 );*/
633 END IF;
634 END IF;
635
636 IF LENGTH(l_consolidated_note) > 1 THEN
637
638 jtf_notes_pub.create_note (p_api_version => 1.0,
639 x_return_status => x_return_status,
640 x_msg_count => x_msg_count,
641 x_msg_data => x_msg_data,
642 p_source_object_id => l_comp_plan_id,
643 p_source_object_code => 'CN_COMP_PLANS',
644 p_notes => l_consolidated_note,
645 p_notes_detail => l_consolidated_note,
646 p_note_type => 'CN_SYSGEN', -- for system generated
647 x_jtf_note_id => l_note_id -- returned
648 );
649 END IF;
650
651 -- End of API body.
652 -- Standard check of p_commit.
653 OPEN objversion_cur;
654 FETCH objversion_cur into p_quota_assign.OBJECT_VERSION_NUMBER;
655 CLOSE objversion_cur;
656
657 IF fnd_api.to_boolean (p_commit)
658 THEN
659 COMMIT WORK;
660 END IF;
661
662 -- Standard call to get message count and if count is 1, get message info.
663 fnd_msg_pub.count_and_get (p_count => x_msg_count,
664 p_data => x_msg_data,
665 p_encoded => fnd_api.g_false
666 );
667 EXCEPTION
668 WHEN fnd_api.g_exc_error
669 THEN
670 ROLLBACK TO update_quota_assign;
671 x_return_status := fnd_api.g_ret_sts_error;
672 fnd_msg_pub.count_and_get (p_count => x_msg_count,
673 p_data => x_msg_data,
674 p_encoded => fnd_api.g_false
675 );
676 WHEN fnd_api.g_exc_unexpected_error
677 THEN
678 ROLLBACK TO update_quota_assign;
679 x_return_status := fnd_api.g_ret_sts_unexp_error;
680 fnd_msg_pub.count_and_get (p_count => x_msg_count,
681 p_data => x_msg_data,
682 p_encoded => fnd_api.g_false
683 );
684 WHEN OTHERS
685 THEN
686 ROLLBACK TO update_quota_assign;
687 x_return_status := fnd_api.g_ret_sts_unexp_error;
688
689 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
690 THEN
691 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
692 END IF;
693
694 fnd_msg_pub.count_and_get (p_count => x_msg_count,
695 p_data => x_msg_data,
696 p_encoded => fnd_api.g_false
697 );
698 END update_quota_assign;
699
700 -- Start of comments
701 -- API name : Delete_Quota_Assign
702 -- Type : Private.
703 -- Function :
704 -- Pre-reqs : None.
705 -- Parameters :
706 -- IN : p_api_version IN NUMBER Required
707 -- p_init_msg_list IN VARCHAR2 Optional
708 -- Default = FND_API.G_FALSE
709 -- p_commit IN VARCHAR2 Optional
710 -- Default = FND_API.G_FALSE
711 -- p_validation_level IN NUMBER Optional
712 -- Default = FND_API.G_VALID_LEVEL_FULL
713 -- p_quota_assign IN quota_assign_rec_type
714 -- OUT : x_return_status OUT VARCHAR2(1)
715 -- x_msg_count OUT NUMBER
716 -- x_msg_data OUT VARCHAR2(2000)
717 -- Version : Current version 1.0
718 -- Notes : Note text
719 --
720 -- End of comments
721 PROCEDURE delete_quota_assign (
722 p_api_version IN NUMBER,
723 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
724 p_commit IN VARCHAR2 := fnd_api.g_false,
725 p_validation_level IN NUMBER
726 := fnd_api.g_valid_level_full,
727 p_quota_assign IN quota_assign_rec_type,
728 x_return_status OUT NOCOPY VARCHAR2,
729 x_msg_count OUT NOCOPY NUMBER,
730 x_msg_data OUT NOCOPY VARCHAR2
731 )
732 IS
733 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Quota_Assign';
734 l_api_version CONSTANT NUMBER := 1.0;
735 l_temp_count NUMBER;
736 l_quota_id cn_quotas.quota_id%TYPE;
737 l_quota_assign_id cn_quota_assigns.quota_assign_id%TYPE;
738 l_comp_plan_id cn_comp_plans.comp_plan_id%TYPE;
739 l_org_id cn_quota_assigns.org_id%TYPE;
740 l_note_msg VARCHAR2 (240);
741 l_note_id NUMBER;
742 l_cp_name cn_comp_plans.NAME%TYPE;
743 l_pe_name cn_quotas.NAME%TYPE;
744 BEGIN
745 -- Standard Start of API savepoint
746 SAVEPOINT delete_quota_assign;
747
748 -- Standard call to check for call compatibility.
749 IF NOT fnd_api.compatible_api_call (l_api_version,
750 p_api_version,
751 l_api_name,
752 g_pkg_name
753 )
754 THEN
755 RAISE fnd_api.g_exc_unexpected_error;
756 END IF;
757
758 -- Initialize message list if p_init_msg_list is set to TRUE.
759 IF fnd_api.to_boolean (p_init_msg_list)
760 THEN
761 fnd_msg_pub.initialize;
762 END IF;
763
764 -- Initialize API return status to success
765 x_return_status := fnd_api.g_ret_sts_success;
766
767 -- API body
768
769 -- do comp plan quota assignment
770 BEGIN
771 SELECT quota_assign_id, quota_id, comp_plan_id, org_id
772 INTO l_quota_assign_id, l_quota_id, l_comp_plan_id, l_org_id
773 FROM cn_quota_assigns
774 WHERE quota_assign_id = p_quota_assign.quota_assign_id;
775 EXCEPTION
776 WHEN NO_DATA_FOUND
777 THEN
778 fnd_message.set_name ('CN', 'CN_RECORD_DELETED');
779 fnd_msg_pub.ADD;
780 RAISE fnd_api.g_exc_unexpected_error;
781 END;
782
783 cn_quota_assigns_pkg.begin_record
784 (x_operation => 'DELETE',
785 x_quota_id => l_quota_id,
786 x_comp_plan_id => l_comp_plan_id,
787 x_quota_assign_id => l_quota_assign_id,
788 x_quota_sequence => NULL,
789 x_quota_id_old => NULL,
790 x_org_id => l_org_id
791 );
792 cn_comp_plans_pkg.set_status (x_comp_plan_id => l_comp_plan_id,
793 x_quota_id => NULL,
794 x_rate_schedule_id => NULL,
795 x_status_code => 'INCOMPLETE',
796 x_event => NULL
797 );
798
799 -- During deltion the logical parent is CN_COMP_PLANS for CN_QUOTA_ASSIGNS
800 IF (l_quota_id > 0 AND l_comp_plan_id > 0)
801 THEN
802 SELECT NAME
803 INTO l_pe_name
804 FROM cn_quotas_all
805 WHERE quota_id = l_quota_id;
806
807 SELECT NAME
808 INTO l_cp_name
809 FROM cn_comp_plans
810 WHERE comp_plan_id = l_comp_plan_id;
811
812 fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_ASPE_UNAS');
813 fnd_message.set_token ('PE_NAME', l_pe_name);
814 fnd_message.set_token ('CP_NAME', l_cp_name);
815 l_note_msg := fnd_message.get;
816 jtf_notes_pub.create_note
817 (p_api_version => 1.0,
818 x_return_status => x_return_status,
819 x_msg_count => x_msg_count,
820 x_msg_data => x_msg_data,
821 p_source_object_id => l_comp_plan_id,
822 p_source_object_code => 'CN_COMP_PLANS',
823 p_notes => l_note_msg,
824 p_notes_detail => l_note_msg,
825 p_note_type => 'CN_SYSGEN',
826 -- for system generated
827 x_jtf_note_id => l_note_id -- returned
828 );
829 END IF;
830
831 -- End of API body.
832 -- Standard check of p_commit.
833 IF fnd_api.to_boolean (p_commit)
834 THEN
835 COMMIT WORK;
836 END IF;
837
838 -- Standard call to get message count and if count is 1, get message info.
839 fnd_msg_pub.count_and_get (p_count => x_msg_count,
840 p_data => x_msg_data,
841 p_encoded => fnd_api.g_false
842 );
843 EXCEPTION
844 WHEN fnd_api.g_exc_error
845 THEN
846 ROLLBACK TO delete_quota_assign;
847 x_return_status := fnd_api.g_ret_sts_error;
848 fnd_msg_pub.count_and_get (p_count => x_msg_count,
849 p_data => x_msg_data,
850 p_encoded => fnd_api.g_false
851 );
852 WHEN fnd_api.g_exc_unexpected_error
853 THEN
854 ROLLBACK TO delete_quota_assign;
855 x_return_status := fnd_api.g_ret_sts_unexp_error;
856 fnd_msg_pub.count_and_get (p_count => x_msg_count,
857 p_data => x_msg_data,
858 p_encoded => fnd_api.g_false
859 );
860 WHEN OTHERS
861 THEN
862 ROLLBACK TO delete_quota_assign;
863 x_return_status := fnd_api.g_ret_sts_unexp_error;
864
865 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
866 THEN
867 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
868 END IF;
869
870 fnd_msg_pub.count_and_get (p_count => x_msg_count,
871 p_data => x_msg_data,
872 p_encoded => fnd_api.g_false
873 );
874 END delete_quota_assign;
875
876 -- Start of comments
877 -- API name : Get_Quota_Assign
878 -- Type : Private.
879 -- Function :
880 -- Pre-reqs : None.
881 -- Parameters :
882 -- IN : p_api_version IN NUMBER Required
883 -- p_init_msg_list IN VARCHAR2 Optional
884 -- Default = FND_API.G_FALSE
885 -- p_commit IN VARCHAR2 Optional
886 -- Default = FND_API.G_FALSE
887 -- p_validation_level IN NUMBER Optional
888 -- Default = FND_API.G_VALID_LEVEL_FULL
889 -- p_comp_plan_id IN NUMBER
890 -- OUT : x_return_status OUT VARCHAR2(1)
891 -- x_msg_count OUT NUMBER
892 -- x_msg_data OUT VARCHAR2(2000)
893 -- x_quota_assign OUT quota_assign_tbl_type
894 -- Version : Current version 1.0
895 -- Notes : Note text
896 --
897 -- End of comments
898 PROCEDURE get_quota_assign (
899 p_api_version IN NUMBER,
900 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
901 p_commit IN VARCHAR2 := fnd_api.g_false,
902 p_validation_level IN NUMBER
903 := fnd_api.g_valid_level_full,
904 p_comp_plan_id IN NUMBER,
905 x_quota_assign OUT NOCOPY quota_assign_tbl_type,
906 x_return_status OUT NOCOPY VARCHAR2,
907 x_msg_count OUT NOCOPY NUMBER,
908 x_msg_data OUT NOCOPY VARCHAR2
909 )
910 IS
911 l_api_name CONSTANT VARCHAR2 (30) := 'Get_Quota_Assign';
912 l_api_version CONSTANT NUMBER := 1.0;
913 l_counter NUMBER;
914
915 CURSOR l_quota_assign_cr
916 IS
917 SELECT q.NAME, q.description, q.start_date, q.end_date,
918 qa.quota_assign_id, qa.quota_id, qa.comp_plan_id,
919 NVL (qa.quota_sequence, 0) quota_sequence,
920 qa.object_version_number, qa.org_id
921 FROM cn_quota_assigns qa, cn_quotas_v q
922 WHERE qa.comp_plan_id = p_comp_plan_id
923 AND qa.quota_id = q.quota_id
924 ORDER BY quota_sequence;
925 BEGIN
926 -- Standard Start of API savepoint
927 SAVEPOINT get_quota_assign;
928
929 -- Standard call to check for call compatibility.
930 IF NOT fnd_api.compatible_api_call (l_api_version,
931 p_api_version,
932 l_api_name,
933 g_pkg_name
934 )
935 THEN
936 RAISE fnd_api.g_exc_unexpected_error;
937 END IF;
938
939 -- Initialize message list if p_init_msg_list is set to TRUE.
940 IF fnd_api.to_boolean (p_init_msg_list)
941 THEN
942 fnd_msg_pub.initialize;
943 END IF;
944
945 -- Initialize API return status to success
946 x_return_status := fnd_api.g_ret_sts_success;
947 -- API body
948 x_quota_assign := g_miss_quota_assign_rec_tb;
949 l_counter := 0;
950
951 FOR l_quota_assign IN l_quota_assign_cr
952 LOOP
953 l_counter := l_counter + 1;
954 x_quota_assign (l_counter).quota_assign_id :=
955 l_quota_assign.quota_assign_id;
956 x_quota_assign (l_counter).NAME := l_quota_assign.NAME;
957 x_quota_assign (l_counter).description := l_quota_assign.description;
958 x_quota_assign (l_counter).quota_id := l_quota_assign.quota_id;
959 x_quota_assign (l_counter).comp_plan_id :=
960 l_quota_assign.comp_plan_id;
961 x_quota_assign (l_counter).start_date := l_quota_assign.start_date;
962 x_quota_assign (l_counter).end_date := l_quota_assign.end_date;
963 x_quota_assign (l_counter).quota_sequence :=
964 l_quota_assign.quota_sequence;
965 x_quota_assign (l_counter).object_version_number :=
966 l_quota_assign.object_version_number;
967 x_quota_assign (l_counter).org_id := l_quota_assign.org_id;
968 END LOOP;
969
970 -- End of API body.
971 -- Standard check of p_commit.
972 IF fnd_api.to_boolean (p_commit)
973 THEN
974 COMMIT WORK;
975 END IF;
976
977 -- Standard call to get message count and if count is 1, get message info.
978 fnd_msg_pub.count_and_get (p_count => x_msg_count,
979 p_data => x_msg_data,
980 p_encoded => fnd_api.g_false
981 );
982 EXCEPTION
983 WHEN fnd_api.g_exc_error
984 THEN
985 ROLLBACK TO get_quota_assign;
986 x_return_status := fnd_api.g_ret_sts_error;
987 fnd_msg_pub.count_and_get (p_count => x_msg_count,
988 p_data => x_msg_data,
989 p_encoded => fnd_api.g_false
990 );
991 WHEN fnd_api.g_exc_unexpected_error
992 THEN
993 ROLLBACK TO get_quota_assign;
994 x_return_status := fnd_api.g_ret_sts_unexp_error;
995 fnd_msg_pub.count_and_get (p_count => x_msg_count,
996 p_data => x_msg_data,
997 p_encoded => fnd_api.g_false
998 );
999 WHEN OTHERS
1000 THEN
1001 ROLLBACK TO get_quota_assign;
1002 x_return_status := fnd_api.g_ret_sts_unexp_error;
1003
1004 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1005 THEN
1006 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1007 END IF;
1008
1009 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1010 p_data => x_msg_data,
1011 p_encoded => fnd_api.g_false
1012 );
1013 END get_quota_assign;
1014 END cn_quota_assign_pvt;