[Home] [Help]
PACKAGE BODY: APPS.CN_PERIOD_QUOTAS_GRP
Source
1 PACKAGE BODY cn_period_quotas_grp AS
2 /* $Header: cnxgprdb.pls 120.4 2005/10/19 06:08:07 chanthon ship $ */
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CN_PERIOD_QUOTAS_GRP';
4 g_file_name CONSTANT VARCHAR2 (12) := 'cnxgprdb.pls';
5 g_last_update_date DATE := SYSDATE;
6 g_last_updated_by NUMBER := fnd_global.user_id;
7 g_creation_date DATE := SYSDATE;
8 g_created_by NUMBER := fnd_global.user_id;
9 g_last_update_login NUMBER := fnd_global.login_id;
10 g_rowid VARCHAR2 (30);
11 g_program_type VARCHAR2 (30);
12
13 -- ----------------------------------------------------------------------------+
14 -- Procedure: Valid_Period_Quotas
15 -- Desc : Validate the Period Quotas Input Parameters like Period Name,
16 -- Plan Element Name.
17 -- ----------------------------------------------------------------------------+
18 PROCEDURE valid_period_quotas (
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 p_quota_name IN VARCHAR2,
23 p_period_quotas_rec IN cn_plan_element_pub.period_quotas_rec_type,
24 x_quota_id OUT NOCOPY NUMBER,
25 x_period_id OUT NOCOPY NUMBER,
26 p_loading_status IN VARCHAR2,
27 x_loading_status OUT NOCOPY VARCHAR2,
28 p_is_duplicate IN VARCHAR2
29 )
30 IS
31 l_api_name CONSTANT VARCHAR2 (30) := 'Valid_Period_Quotas';
32 l_same_pe NUMBER;
33 l_calc_formula_id NUMBER;
34 l_quota_start_date DATE;
35 l_quota_end_date DATE;
36 l_period_start_date DATE;
37 l_period_end_date DATE;
38 l_loading_status VARCHAR2 (80);
39 l_quota_type_code cn_quotas.quota_type_code%TYPE;
40 l_org_id NUMBER;
41 BEGIN
42 -- Initialize API return status to success
43 x_return_status := fnd_api.g_ret_sts_success;
44 x_loading_status := p_loading_status;
45
46 -- API body
47 -- check for required data for Period_quotas
48 -- Check MISS and NULL ( Quota Name, Period_name )
49 IF ((cn_api.chk_miss_char_para (p_char_para => p_quota_name,
50 p_para_name => cn_chk_plan_element_pkg.g_pe_name,
51 p_loading_status => x_loading_status,
52 x_loading_status => l_loading_status
53 )
54 ) = fnd_api.g_true
55 )
56 THEN
57 RAISE fnd_api.g_exc_error;
58 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_quota_name,
59 p_obj_name => cn_chk_plan_element_pkg.g_pe_name,
60 p_loading_status => x_loading_status,
61 x_loading_status => l_loading_status
62 )
63 ) = fnd_api.g_true
64 )
65 THEN
66 RAISE fnd_api.g_exc_error;
67 END IF;
68
69 --+
70 -- Check Period name is not miss, not null
71 --+
72 IF ((cn_api.chk_miss_char_para (p_char_para => p_period_quotas_rec.period_name,
73 p_para_name => 'Period Name',
74 p_loading_status => x_loading_status,
75 x_loading_status => l_loading_status
76 )
77 ) = fnd_api.g_true
78 )
79 THEN
80 RAISE fnd_api.g_exc_error;
81 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_period_quotas_rec.period_name,
82 p_obj_name => 'Period Name',
83 p_loading_status => x_loading_status,
84 x_loading_status => l_loading_status
85 )
86 ) = fnd_api.g_true
87 )
88 THEN
89 RAISE fnd_api.g_exc_error;
90 END IF;
91
92
93 l_org_id := p_period_quotas_rec.org_id ;
94
95 --+
96 -- Get quota id, calc_formula_id
97 --+
98 BEGIN
99 SELECT quota_id,
100 calc_formula_id,
101 start_date,
102 end_date,
103 quota_type_code
104 INTO x_quota_id,
105 l_calc_formula_id,
106 l_quota_start_date,
107 l_quota_end_date,
108 l_quota_type_code
109 FROM cn_quotas_v
110 WHERE NAME = p_quota_name AND org_id = l_org_id;
111 EXCEPTION
112 WHEN NO_DATA_FOUND
113 THEN
114 x_quota_id := NULL;
115 l_calc_formula_id := NULL;
116 END;
117
118 --+
119 -- Check Quota ID is Not Null
120 --+
121 IF x_quota_id IS NULL AND p_quota_name IS NOT NULL
122 THEN
123 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
124 THEN
125 fnd_message.set_name ('CN', 'CN_PLN_NOT_EXIST');
126 fnd_message.set_token ('PE_NAME', p_quota_name);
127 fnd_msg_pub.ADD;
128 END IF;
129
130 x_loading_status := 'CN_PLN_NOT_EXIST';
131 RAISE fnd_api.g_exc_error;
132 END IF;
133
134 --+
135 -- Check Formula is Assiged to the Quota
136 --+
137 --Change made for duplicate.. added the quota status
138 IF (l_calc_formula_id IS NULL AND l_quota_type_code <> 'EXTERNAL'
139 AND p_is_duplicate = 'N')
140 THEN
141 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
142 THEN
143 fnd_message.set_name ('CN', 'CN_QUOTA_FORMULA_NOT_EXIST');
144 fnd_message.set_token ('PE_NAME', p_quota_name);
145 fnd_msg_pub.ADD;
146 END IF;
147
148 x_loading_status := 'QUOTA_FORMULA_NOT_EXIST';
149 RAISE fnd_api.g_exc_error;
150 END IF;
151
152 -- Disable this checking since we can have period quotas for BOTH ITD
153 -- AND NON-ITD FORMULA, bug 2422752
154 --+
155 -- Check Itd Flag is Y
156 --+
157
158 /*IF Nvl(cn_api.get_itd_flag(l_calc_formula_id),'N') <> 'Y' THEN
159 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
160 THEN
161 FND_MESSAGE.SET_NAME('CN' , 'CN_QUOTA_CANNOT_HAVE_PERIODS');
162 FND_MESSAGE.SET_TOKEN('PE_NAME',p_quota_name );
163 FND_MSG_PUB.Add;
164 END IF;
165 x_loading_status := 'QUOTA_CANNOT_HAVE_PERIODS';
166 RAISE FND_API.G_EXC_ERROR ;
167 END IF;*/
168
169 --+
170 -- get period id
171 --+
172 x_period_id := cn_api.get_acc_period_id (p_period_quotas_rec.period_name, l_org_id);
173
174 --+
175 -- Check Period ID is Not Null
176 --+
177 IF x_period_id IS NULL AND p_period_quotas_rec.period_name IS NOT NULL
178 THEN
179 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
180 THEN
181 fnd_message.set_name ('CN', 'CN_PERIOD_NOT_EXIST');
182 fnd_message.set_token ('PERIOD_NAME', p_period_quotas_rec.period_name);
183 fnd_msg_pub.ADD;
184 END IF;
185
186 x_loading_status := 'CN_PERIOD_NOT_EXIST';
187 RAISE fnd_api.g_exc_error;
188 END IF;
189
190 --+
191 -- Check period start date and end date is falling with the quota
192 -- Start date and end Date
193 --+
194 BEGIN
195 SELECT start_date,
196 end_date
197 INTO l_period_start_date,
198 l_period_end_date
199 FROM cn_acc_period_statuses_v
200 WHERE period_id = x_period_id AND org_id = l_org_id;
201
202 IF TRUNC (l_period_start_date) < TRUNC (cn_period_quotas_pkg.previous_period (l_quota_start_date,l_org_id))
203 OR TRUNC (l_period_end_date) > TRUNC (cn_api.next_period (NVL (l_quota_end_date, l_period_end_date), l_org_id))
204 THEN
205 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
206 THEN
207 fnd_message.set_name ('CN', 'CN_PRD_DT_NOT_WIN_QUOTA_DT');
208 fnd_msg_pub.ADD;
209 END IF;
210
211 x_loading_status := 'CN_PRD_DT_NOT_WIN_QUOTA_DT';
212 RAISE fnd_api.g_exc_error;
213 END IF;
214 EXCEPTION
215 WHEN NO_DATA_FOUND
216 THEN
217 NULL;
218 END;
219
220 --+
221 -- Check for Duplicate Record for the Same Quota.
222 --+
223 IF p_period_quotas_rec.period_name_old IS NULL
224 THEN
225 SELECT COUNT (*)
226 INTO l_same_pe
227 FROM cn_period_quotas pq
228 WHERE pq.period_id = x_period_id AND pq.quota_id = x_quota_id;
229
230 IF l_same_pe <> 0
231 THEN
232 -- Error, check the msg level and add an error message to the
233 -- API message list
234 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
235 THEN
236 fnd_message.set_name ('CN', 'CN_PERIOD_QUOTA_EXISTS');
237 fnd_message.set_token ('PERIOD_NAME', p_period_quotas_rec.period_name);
238 fnd_message.set_token ('PE_NAME', p_quota_name);
239 fnd_msg_pub.ADD;
240 END IF;
241
242 x_loading_status := 'PERIOD_QUOTA_EXISTS';
243 END IF;
244 END IF;
245 EXCEPTION
246 WHEN fnd_api.g_exc_error
247 THEN
248 x_return_status := fnd_api.g_ret_sts_error;
249 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
250 WHEN fnd_api.g_exc_unexpected_error
251 THEN
252 x_return_status := fnd_api.g_ret_sts_unexp_error;
253 x_loading_status := 'UNEXPECTED_ERR';
254 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
255 WHEN OTHERS
256 THEN
257 x_return_status := fnd_api.g_ret_sts_unexp_error;
258 x_loading_status := 'UNEXPECTED_ERR';
259
260 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
261 THEN
262 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
263 END IF;
264
265 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
266 END valid_period_quotas;
267
268 -- ----------------------------------------------------------------------------+
269 -- Procedure: Check Valid Update
270 -- Desc :This procedure is called from update Quota Rules.
271 -- ----------------------------------------------------------------------------+
272 PROCEDURE check_valid_update (
273 x_return_status OUT NOCOPY VARCHAR2,
274 x_msg_count OUT NOCOPY NUMBER,
275 x_msg_data OUT NOCOPY VARCHAR2,
276 p_quota_name IN VARCHAR2,
277 p_period_quotas_rec IN cn_plan_element_pub.period_quotas_rec_type,
278 x_period_quota_id_old OUT NOCOPY NUMBER,
279 x_period_id_old OUT NOCOPY NUMBER,
280 x_quota_id_old OUT NOCOPY NUMBER,
281 p_loading_status IN VARCHAR2,
282 x_loading_status OUT NOCOPY VARCHAR2
283 )
284 IS
285 l_api_name CONSTANT VARCHAR2 (30) := 'Check_Valid_Update';
286 l_same_pe NUMBER;
287 l_quota_id NUMBER;
288 l_period_id NUMBER;
289 l_period_quotas_rec cn_period_quotas%ROWTYPE;
290 l_meaning cn_lookups.meaning%TYPE;
291 l_loading_status VARCHAR2 (80);
292 BEGIN
293 -- Initialize API return status to success
294 x_return_status := fnd_api.g_ret_sts_success;
295 x_loading_status := p_loading_status;
296
297 --+
298 -- Check quota name is not null
299 --+
300 IF ((cn_api.chk_miss_char_para (p_char_para => p_quota_name,
301 p_para_name => cn_chk_plan_element_pkg.g_pe_name,
302 p_loading_status => x_loading_status,
303 x_loading_status => l_loading_status
304 )
305 ) = fnd_api.g_true
306 )
307 THEN
308 RAISE fnd_api.g_exc_error;
309 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_quota_name,
310 p_obj_name => cn_chk_plan_element_pkg.g_pe_name,
311 p_loading_status => x_loading_status,
312 x_loading_status => l_loading_status
313 )
314 ) = fnd_api.g_true
315 )
316 THEN
317 RAISE fnd_api.g_exc_error;
318 END IF;
319
320 --+
321 -- Get old Quota id
322 --+
323 x_quota_id_old := cn_chk_plan_element_pkg.get_quota_id (p_quota_name, p_period_quotas_rec.org_id);
324
325 --+
326 -- Raise an error if quota not exists in the database
327 --+
328 IF x_quota_id_old IS NULL AND p_quota_name IS NOT NULL
329 THEN
330 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
331 THEN
332 fnd_message.set_name ('CN', 'CN_PLN_NOT_EXIST');
333 fnd_message.set_token ('PE_NAME', p_quota_name);
334 fnd_msg_pub.ADD;
335 END IF;
336
337 x_loading_status := 'CN_PLN_NOT_EXIST';
338 RAISE fnd_api.g_exc_error;
339 END IF;
340
341 -- +
342 -- Check period name is not null or missing
343 --+
344 IF ((cn_api.chk_miss_char_para (p_char_para => p_period_quotas_rec.period_name_old,
345 p_para_name => 'Period Name',
346 p_loading_status => x_loading_status,
347 x_loading_status => l_loading_status
348 )
349 ) = fnd_api.g_true
350 )
351 THEN
352 RAISE fnd_api.g_exc_error;
353 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_period_quotas_rec.period_name_old,
354 p_obj_name => 'Period Name',
355 p_loading_status => x_loading_status,
356 x_loading_status => l_loading_status
357 )
358 ) = fnd_api.g_true
359 )
360 THEN
361 RAISE fnd_api.g_exc_error;
362 END IF;
363
364 --+
365 -- get period id
366 --+
367 x_period_id_old := cn_api.get_acc_period_id (p_period_quotas_rec.period_name_old,p_period_quotas_rec.org_id);
368
369 --+
370 -- Check Period ID is Not Null
371 --+
372 IF x_period_id_old IS NULL AND p_period_quotas_rec.period_name_old IS NOT NULL
373 THEN
374 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
375 THEN
376 fnd_message.set_name ('CN', 'CN_PERIOD_NOT_EXIST');
377 fnd_message.set_token ('PERIOD_NAME', p_period_quotas_rec.period_name_old);
378 fnd_msg_pub.ADD;
379 END IF;
380
381 x_loading_status := 'CN_PERIOD_NOT_EXIST';
382 RAISE fnd_api.g_exc_error;
383 END IF;
384
385 --+
386 -- Get quota period id to update the record
387 --+
388 BEGIN
389 SELECT *
390 INTO l_period_quotas_rec
391 FROM cn_period_quotas
392 WHERE period_id = x_period_id_old AND quota_id = x_quota_id_old;
393 EXCEPTION
394 WHEN OTHERS
395 THEN
396 l_period_quotas_rec.period_quota_id := NULL;
397 END;
398
399 --+
400 -- Check record exists in the database for the period_quota_id.
401 --+
402 IF l_period_quotas_rec.period_quota_id IS NULL
403 THEN
404 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
405 THEN
406 fnd_message.set_name ('CN', 'CN_PERIOD_QUOTA_NOT_EXIST');
407 fnd_message.set_token ('QUOTA_NAME', p_quota_name);
408 fnd_message.set_token ('PERIOD_NAME', p_period_quotas_rec.period_name_old);
409 fnd_msg_pub.ADD;
410 END IF;
411
412 x_loading_status := 'CN_PERIOD_QUOTA_NOT_EXIST';
413 RAISE fnd_api.g_exc_error;
414 END IF;
415
416 --+
417 -- Assign it to the out variable.
418 --+
419 x_period_quota_id_old := l_period_quotas_rec.period_quota_id;
420
421 --+
422 -- You cannot update columns other than amount columns.
423 --+
424 IF (l_period_quotas_rec.period_id <> x_period_id_old)
425 THEN
426 -- Error, check the msg level and add an error message to the
427 -- API message list
428 l_meaning := cn_api.get_lkup_meaning ('PERIOD_ID', 'PERIOD_OBJECT_TYPE');
429
430 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
431 THEN
432 fnd_message.set_name ('CN', 'CN_PERIOD_NOT_CONSISTENT');
433 fnd_message.set_token ('QUOTA_NAME', p_quota_name);
434 fnd_message.set_token ('PERIOD_NAME', p_period_quotas_rec.period_name);
435 fnd_message.set_token ('OBJ_NAME', l_meaning);
436 fnd_msg_pub.ADD;
437 END IF;
438
439 x_loading_status := 'CN_PERIOD_NOT_CONSISTENT';
440 RAISE fnd_api.g_exc_error;
441 END IF;
442
443 --+
444 -- Validate the period quotas
445 --+
446 valid_period_quotas (x_return_status => x_return_status,
447 x_msg_count => x_msg_count,
448 x_msg_data => x_msg_data,
449 p_quota_name => p_quota_name,
450 p_period_quotas_rec => p_period_quotas_rec,
451 x_quota_id => l_quota_id,
452 x_period_id => l_period_id,
453 p_loading_status => x_loading_status,
454 x_loading_status => l_loading_status,
455 p_is_duplicate => 'N'
456 ); -- Default Validations
457 x_loading_status := l_loading_status;
458
459 -- Raise an error if the status is not success
460 IF (x_return_status <> fnd_api.g_ret_sts_success)
461 THEN
462 RAISE fnd_api.g_exc_error;
463 END IF;
464
465 -- End of API body.
466 -- Standard call to get message count and if count is 1, get message info.
467 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
468 EXCEPTION
469 WHEN fnd_api.g_exc_error
470 THEN
471 x_return_status := fnd_api.g_ret_sts_error;
472 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
473 WHEN fnd_api.g_exc_unexpected_error
474 THEN
475 x_return_status := fnd_api.g_ret_sts_unexp_error;
476 x_loading_status := 'UNEXPECTED_ERR';
477 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
478 WHEN OTHERS
479 THEN
480 x_return_status := fnd_api.g_ret_sts_unexp_error;
481 x_loading_status := 'UNEXPECTED_ERR';
482
483 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
484 THEN
485 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
486 END IF;
487
488 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
489 END check_valid_update;
490
491 --|-----------------------------------------------------------------------+
492 --| Procedure Name: Create_Period_Quotas
493 --| Descr: Create a Period Quotas
494 --|-----------------------------------------------------------------------+
495 PROCEDURE create_period_quotas (
496 p_api_version IN NUMBER,
497 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
498 p_commit IN VARCHAR2 := fnd_api.g_false,
499 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
500 x_return_status OUT NOCOPY VARCHAR2,
501 x_msg_count OUT NOCOPY NUMBER,
502 x_msg_data OUT NOCOPY VARCHAR2,
503 p_quota_name IN VARCHAR2,
504 p_period_quotas_rec_tbl IN cn_plan_element_pub.period_quotas_rec_tbl_type := cn_plan_element_pub.g_miss_period_quotas_rec_tbl,
505 x_loading_status OUT NOCOPY VARCHAR2,
506 p_is_duplicate IN VARCHAR2 DEFAULT 'N'
507 )
508 IS
509 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Period_Quotas';
510 l_api_version CONSTANT NUMBER := 1.0;
511 l_period_id NUMBER;
512 l_quota_id NUMBER;
513 l_period_quota_id NUMBER;
514 l_tmp NUMBER;
515 l_loading_status VARCHAR2 (80);
516 BEGIN
517 --
518 -- Standard Start of API savepoint
519 -- +
520 SAVEPOINT create_period_quotas;
521
522 --+
523 -- Standard call to check for call compatibility.
524 --+
525 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
526 THEN
527 RAISE fnd_api.g_exc_unexpected_error;
528 END IF;
529
530 --+
531 -- Initialize message list if p_init_msg_list is set to TRUE.
532 -- +
533 IF fnd_api.to_boolean (p_init_msg_list)
534 THEN
535 fnd_msg_pub.initialize;
536 END IF;
537
538 -- +
539 -- Initialize API return status to success
540 --+
541 x_return_status := fnd_api.g_ret_sts_success;
542 x_loading_status := 'CN_INSERTED';
543
544 -- +
545 -- API body
546 -- +
547 IF (p_period_quotas_rec_tbl.COUNT <> 0)
548 THEN
549 -- Loop through each record and check go through the normal validations
550 -- and etc.
551 FOR i IN p_period_quotas_rec_tbl.FIRST .. p_period_quotas_rec_tbl.LAST
552 LOOP
553 valid_period_quotas (x_return_status => x_return_status,
554 x_msg_count => x_msg_count,
555 x_msg_data => x_msg_data,
556 p_quota_name => p_quota_name,
557 p_period_quotas_rec => p_period_quotas_rec_tbl (i),
558 x_quota_id => l_quota_id,
559 x_period_id => l_period_id,
560 p_loading_status => x_loading_status,
561 x_loading_status => l_loading_status,
562 p_is_duplicate => p_is_duplicate
563 );
564 x_loading_status := l_loading_status;
565 -- Check return status and insert if the status is CN_INSERTED
566 --ELSE Record Already exists
567 l_tmp := p_period_quotas_rec_tbl.COUNT;
568
569 --+
570 -- Raise an error if the return status is not success
571 --+
572 IF (x_return_status <> fnd_api.g_ret_sts_success)
573 THEN
574 RAISE fnd_api.g_exc_error;
575 ELSIF (x_return_status = fnd_api.g_ret_sts_success) AND (x_loading_status = 'CN_INSERTED')
576 THEN
577 cn_period_quotas_pkg.begin_record (x_operation => 'INSERT',
578 x_period_quota_id => l_period_quota_id,
579 x_period_id => l_period_id,
580 x_quota_id => l_quota_id,
581 x_period_target => p_period_quotas_rec_tbl (i).period_target,
582 x_itd_target => NULL,
583 x_period_payment => p_period_quotas_rec_tbl (i).period_payment,
584 x_itd_payment => NULL,
585 x_quarter_num => NULL,
586 x_period_year => NULL,
587 x_performance_goal => p_period_quotas_rec_tbl (i).performance_goal,
588 x_creation_date => g_creation_date,
589 x_last_update_date => g_last_update_date,
590 x_last_update_login => g_last_update_login,
591 x_last_updated_by => g_last_updated_by,
592 x_created_by => g_created_by,
593 x_period_type_code => NULL
594 );
595 END IF; -- CN_INSERTED.
596 END LOOP; -- Period Quotas
597
598 -- clku, bug 3637221 , we need to sync the itd values in case of inserting
599 -- non-zero period target, payment_amount or goal, particularly in
600 -- duplicating Plan Element
601 cn_period_quotas_pkg.sync_itd_values (x_quota_id => l_quota_id);
602 END IF; -- Table Count is Not Zero
603
604 --+
605 -- End of API body.
606 -- Standard check of p_commit.
607 --+
608 IF fnd_api.to_boolean (p_commit)
609 THEN
610 COMMIT WORK;
611 END IF;
612
613 --+
614 -- Standard call to get message count and if count is 1, get message info.
615 --+
616 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
617 EXCEPTION
618 WHEN fnd_api.g_exc_error
619 THEN
620 ROLLBACK TO create_period_quotas;
621 x_return_status := fnd_api.g_ret_sts_error;
622 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
623 WHEN fnd_api.g_exc_unexpected_error
624 THEN
625 ROLLBACK TO create_period_quotas;
626 x_loading_status := 'UNEXPECTED_ERR';
627 x_return_status := fnd_api.g_ret_sts_unexp_error;
628 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
629 WHEN OTHERS
630 THEN
631 ROLLBACK TO create_period_quotas;
632 x_loading_status := 'UNEXPECTED_ERR';
633 x_return_status := fnd_api.g_ret_sts_unexp_error;
634
635 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
636 THEN
637 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
638 END IF;
639
640 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
641 END create_period_quotas;
642
643 --|-----------------------------------------------------------------------+
644 --| Procedure Name: Update_Period_Quotas
645 --| Descr: Update a Period Quotas
646 --|----------------------------------------------------------------------- +
647 PROCEDURE update_period_quotas (
648 p_api_version IN NUMBER,
649 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
650 p_commit IN VARCHAR2 := fnd_api.g_false,
651 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
652 x_return_status OUT NOCOPY VARCHAR2,
653 x_msg_count OUT NOCOPY NUMBER,
654 x_msg_data OUT NOCOPY VARCHAR2,
655 p_quota_name IN VARCHAR2,
656 p_period_quotas_rec_tbl IN cn_plan_element_pub.period_quotas_rec_tbl_type := cn_plan_element_pub.g_miss_period_quotas_rec_tbl,
657 x_loading_status OUT NOCOPY VARCHAR2
658 )
659 IS
660 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Period_Quotas';
661 l_api_version CONSTANT NUMBER := 1.0;
662 l_period_id NUMBER;
663 l_quota_id NUMBER;
664 l_period_quota_id NUMBER;
665 l_tmp NUMBER;
666 l_loading_status VARCHAR2 (80);
667 BEGIN
668 --
669 -- Standard Start of API savepoint
670 -- +
671 SAVEPOINT update_plan_element;
672
673 --+
674 -- Standard call to check for call compatibility.
675 --+
676 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
677 THEN
678 RAISE fnd_api.g_exc_unexpected_error;
679 END IF;
680
681 --+
682 -- Initialize message list if p_init_msg_list is set to TRUE.
683 -- +
684 IF fnd_api.to_boolean (p_init_msg_list)
685 THEN
686 fnd_msg_pub.initialize;
687 END IF;
688
689 -- +
690 -- Initialize API return status to success
691 --+
692 x_return_status := fnd_api.g_ret_sts_success;
693 x_loading_status := 'CN_UPDATED';
694
695 -- +
696 -- API body
697 -- +
698 IF (p_period_quotas_rec_tbl.COUNT <> 0)
699 THEN
700 -- Loop through each record and check go through the normal validations
701 -- and etc.
702 FOR i IN p_period_quotas_rec_tbl.FIRST .. p_period_quotas_rec_tbl.LAST
703 LOOP
704 check_valid_update (x_return_status => x_return_status,
705 x_msg_count => x_msg_count,
706 x_msg_data => x_msg_data,
707 p_quota_name => p_quota_name,
708 p_period_quotas_rec => p_period_quotas_rec_tbl (i),
709 x_quota_id_old => l_quota_id,
710 x_period_id_old => l_period_id,
711 x_period_quota_id_old => l_period_quota_id,
712 p_loading_status => x_loading_status,
713 x_loading_status => l_loading_status
714 );
715 x_loading_status := l_loading_status;
716
717 IF (x_return_status <> fnd_api.g_ret_sts_success)
718 THEN
719 RAISE fnd_api.g_exc_error;
720 ELSIF (x_return_status = fnd_api.g_ret_sts_success) AND (x_loading_status = 'CN_UPDATED')
721 THEN
722 cn_period_quotas_pkg.begin_record (x_operation => 'UPDATE',
723 x_period_quota_id => l_period_quota_id,
724 x_period_id => l_period_id,
725 x_quota_id => l_quota_id,
726 x_period_target => p_period_quotas_rec_tbl (i).period_target,
727 x_itd_target => NULL,
728 x_period_payment => p_period_quotas_rec_tbl (i).period_payment,
729 x_itd_payment => NULL,
730 x_quarter_num => NULL,
731 x_period_year => NULL,
732 x_performance_goal => p_period_quotas_rec_tbl (i).performance_goal,
733 x_creation_date => g_creation_date,
734 x_last_update_date => g_last_update_date,
735 x_last_update_login => g_last_update_login,
736 x_last_updated_by => g_last_updated_by,
737 x_created_by => g_created_by,
738 x_period_type_code => NULL
739 );
740 END IF; -- CN_UPDATED.
741 END LOOP; -- Period Quotas
742 END IF; -- Table Count is Not Zero
743
744 --+
745 -- End of API body.
746 -- Standard check of p_commit.
747 --+
748 IF fnd_api.to_boolean (p_commit)
749 THEN
750 COMMIT WORK;
751 END IF;
752
753 --+
754 -- Standard call to get message count and if count is 1, get message info.
755 --+
756 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
757 EXCEPTION
758 WHEN fnd_api.g_exc_error
759 THEN
760 ROLLBACK TO update_plan_element;
761 x_return_status := fnd_api.g_ret_sts_error;
762 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
763 WHEN fnd_api.g_exc_unexpected_error
764 THEN
765 ROLLBACK TO update_plan_element;
766 x_loading_status := 'UNEXPECTED_ERR';
767 x_return_status := fnd_api.g_ret_sts_unexp_error;
768 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
769 WHEN OTHERS
770 THEN
771 ROLLBACK TO update_plan_element;
772 x_loading_status := 'UNEXPECTED_ERR';
773 x_return_status := fnd_api.g_ret_sts_unexp_error;
774
775 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
776 THEN
777 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
778 END IF;
779
780 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
781 END update_period_quotas;
782 END cn_period_quotas_grp;