[Home] [Help]
PACKAGE BODY: APPS.CN_QUOTA_RULES_GRP
Source
1 PACKAGE BODY CN_QUOTA_RULES_GRP AS
2 /* $Header: cnxgqrb.pls 120.4 2005/10/18 07:26:19 chanthon noship $ */
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CN_QUOTA_RULES_GRP';
4 g_file_name CONSTANT VARCHAR2 (12) := 'cnxgqrb.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 -- Function : convert_pe_user_input
15 -- Desc : function to trim all blank spaces of user input
16 -- Assign defalut value if input is missing
17 -- ----------------------------------------------------------------------------+
18 FUNCTION convert_rev_class_user_input (
19 p_quota_name IN VARCHAR2,
20 p_revenue_class_rec IN cn_plan_element_pub.revenue_class_rec_type,
21 x_return_status OUT NOCOPY VARCHAR2,
22 p_loading_status IN VARCHAR2,
23 x_loading_status OUT NOCOPY VARCHAR2
24 )
25 RETURN cn_chk_plan_element_pkg.pe_rec_type
26 IS
27 l_pe_rec cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec;
28 BEGIN
29 x_return_status := fnd_api.g_ret_sts_success;
30 x_loading_status := p_loading_status;
31 l_pe_rec.NAME := LTRIM (RTRIM (p_quota_name));
32 l_pe_rec.org_id := p_revenue_class_rec.org_id;
33 l_pe_rec.rev_class_name := LTRIM (RTRIM (p_revenue_class_rec.rev_class_name));
34 l_pe_rec.rev_class_id := cn_api.get_rev_class_id (l_pe_rec.rev_class_name,p_revenue_class_rec.org_id);
35
36 -- Get the Plan Information for further use.
37 BEGIN
38 SELECT quota_id, quota_type_code, incentive_type_code, credit_type_id
39 INTO l_pe_rec.quota_id, l_pe_rec.quota_type_code, l_pe_rec.incentive_type_code, l_pe_rec.credit_type_id
40 FROM cn_quotas_v
41 WHERE NAME = l_pe_rec.NAME and org_id = p_revenue_class_rec.org_id;
42 EXCEPTION
43 WHEN NO_DATA_FOUND
44 THEN
45 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
46 THEN
47 fnd_message.set_name ('CN', 'CN_PLN_NOT_EXIST');
48 fnd_message.set_token ('PE_NAME', l_pe_rec.NAME);
49 fnd_msg_pub.ADD;
50 END IF;
51
52 x_loading_status := 'CN_PLN_NOT_EXIST';
53 RAISE fnd_api.g_exc_error;
54 END;
55
56 -- Set the Default Rev Class Target
57 SELECT DECODE (p_revenue_class_rec.rev_class_target, fnd_api.g_miss_num, 0, NULL, 0, p_revenue_class_rec.rev_class_target)
58 INTO l_pe_rec.rev_class_target
59 FROM SYS.DUAL;
60
61 -- Set the Default value for Payment Amount
62 SELECT DECODE (p_revenue_class_rec.rev_class_payment_amount, fnd_api.g_miss_num, 0, NULL, 0, p_revenue_class_rec.rev_class_payment_amount)
63 INTO l_pe_rec.rev_class_payment_amount
64 FROM SYS.DUAL;
65
66 -- Set the Default Value for Performance Goal
67 SELECT DECODE (p_revenue_class_rec.rev_class_performance_goal, fnd_api.g_miss_num, 0, NULL, 0, p_revenue_class_rec.rev_class_performance_goal)
68 INTO l_pe_rec.rev_class_performance_goal
69 FROM SYS.DUAL;
70
71 RETURN l_pe_rec;
72 END convert_rev_class_user_input;
73
74 -- ----------------------------------------------------------------------------+
75 -- Procedure: valid_quota_element
76 -- Desc : Validate the Quto Rules Input Parameters like Revenue Class Name,
77 -- Plan Element Name.
78 -- ----------------------------------------------------------------------------+
79 PROCEDURE valid_quota_rules (
80 x_return_status OUT NOCOPY VARCHAR2,
81 x_msg_count OUT NOCOPY NUMBER,
82 x_msg_data OUT NOCOPY VARCHAR2,
83 p_rev_class_name_old IN VARCHAR2 := NULL,
84 p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type,
85 p_loading_status IN VARCHAR2,
86 x_loading_status OUT NOCOPY VARCHAR2
87 )
88 IS
89 l_api_name CONSTANT VARCHAR2 (30) := 'Valid_Quota_Rules';
90 l_same_pe NUMBER;
91 l_loading_status VARCHAR2 (80);
92 BEGIN
93 -- Initialize API return status to success
94 x_return_status := fnd_api.g_ret_sts_success;
95 x_loading_status := p_loading_status;
96
97 -- API body
98 -- check for required data in Quotas.
99 -- Check MISS and NULL ( Revenue class Name, Quota Name )
100 IF ((cn_api.chk_miss_char_para (p_char_para => p_pe_rec.NAME,
101 p_para_name => cn_chk_plan_element_pkg.g_pe_name,
102 p_loading_status => x_loading_status,
103 x_loading_status => l_loading_status
104 )
105 ) = fnd_api.g_true
106 )
107 THEN
108 RAISE fnd_api.g_exc_error;
109 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_pe_rec.NAME,
110 p_obj_name => cn_chk_plan_element_pkg.g_pe_name,
111 p_loading_status => x_loading_status,
112 x_loading_status => l_loading_status
113 )
114 ) = fnd_api.g_true
115 )
116 THEN
117 RAISE fnd_api.g_exc_error;
118 END IF;
119
120 -- Check rev class name is not miss, not null
121 IF ((cn_api.chk_miss_char_para (p_char_para => p_pe_rec.rev_class_name,
122 p_para_name => cn_chk_plan_element_pkg.g_rev_cls_name,
123 p_loading_status => x_loading_status,
124 x_loading_status => l_loading_status
125 )
126 ) = fnd_api.g_true
127 )
128 THEN
129 RAISE fnd_api.g_exc_error;
130 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_pe_rec.rev_class_name,
131 p_obj_name => cn_chk_plan_element_pkg.g_rev_cls_name,
132 p_loading_status => x_loading_status,
133 x_loading_status => l_loading_status
134 )
135 ) = fnd_api.g_true
136 )
137 THEN
138 RAISE fnd_api.g_exc_error;
139 END IF;
140
141 --+
142 -- Check wheather revenue class allowed for this Quota Type.
143 --+
144 IF (p_pe_rec.incentive_type_code NOT IN ('COMMISSION', 'BONUS'))
145 THEN
146 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
147 THEN
148 fnd_message.set_name ('CN', 'CN_CANNOT_HAVE_REV_CLASS');
149 fnd_message.set_token ('OBJ_VALUE', 'MANUAL');
150 fnd_message.set_token ('PLAN_TYPE', cn_api.get_lkup_meaning (p_pe_rec.quota_type_code, 'QUOTA_TYPE'));
151 fnd_message.set_token ('TOKEN1', NULL);
152 fnd_message.set_token ('TOKEN2', NULL);
153 fnd_message.set_token ('TOKEN3', NULL);
154 fnd_msg_pub.ADD;
155 END IF;
156
157 x_loading_status := 'CN_CANNOT_HAVE_REV_CLASS';
158 RAISE fnd_api.g_exc_error;
159 END IF;
160
161 --+
162 -- Check the revenue class name is exists in the Database.
163 --+
164 IF p_pe_rec.rev_class_id IS NULL AND p_pe_rec.rev_class_name IS NOT NULL
165 THEN
166 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
167 THEN
168 fnd_message.set_name ('CN', 'CN_REV_CLASS_NOT_EXIST');
169 fnd_msg_pub.ADD;
170 END IF;
171
172 x_loading_status := 'CN_REV_CLASS_NOT_EXIST';
173 RAISE fnd_api.g_exc_error;
174 END IF;
175
176 --+
177 -- Check for Duplicate Record for the Same Quota.
178 -- only checks if the old value is null ( always nuill except UPDATE )
179 --+
180 IF p_rev_class_name_old IS NULL
181 THEN
182 SELECT COUNT (*)
183 INTO l_same_pe
184 FROM cn_quota_rules qr
185 WHERE qr.revenue_class_id = (SELECT revenue_class_id
186 FROM cn_revenue_classes
187 WHERE NAME = p_pe_rec.rev_class_name
188 AND org_id = p_pe_rec.org_id) AND qr.quota_id = p_pe_rec.quota_id;
189
190 IF l_same_pe <> 0
191 THEN
192 -- Error, check the msg level and add an error message to the
193 -- API message list
194 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
195 THEN
196 fnd_message.set_name ('CN', 'PLN_QUOTA_REV_EXISTS');
197 fnd_message.set_token ('PLAN_NAME', p_pe_rec.NAME);
198 fnd_message.set_token ('REVENUE_CLASS_NAME', p_pe_rec.rev_class_name);
199 fnd_msg_pub.ADD;
200 END IF;
201
202 x_loading_status := 'PLN_QUOTA_REV_EXISTS';
203 END IF;
204 END IF;
205 -- end of valid quota rules
206 EXCEPTION
207 WHEN fnd_api.g_exc_error
208 THEN
209 x_return_status := fnd_api.g_ret_sts_error;
210 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
211 WHEN fnd_api.g_exc_unexpected_error
212 THEN
213 x_return_status := fnd_api.g_ret_sts_unexp_error;
214 x_loading_status := 'UNEXPECTED_ERR';
215 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
216 WHEN OTHERS
217 THEN
218 x_return_status := fnd_api.g_ret_sts_unexp_error;
219 x_loading_status := 'UNEXPECTED_ERR';
220
221 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
222 THEN
223 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
224 END IF;
225
226 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
227 END valid_quota_rules;
228
229 -- ----------------------------------------------------------------------------+
230 -- Procedure: Check Valid Update
231 -- Desc :This procedure is called from update Quota Rules.
232 -- ----------------------------------------------------------------------------+
233 PROCEDURE check_valid_update (
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2,
237 p_rev_class_name_old IN VARCHAR2,
238 x_rev_class_id_old OUT NOCOPY NUMBER,
239 x_quota_rule_id_old OUT NOCOPY NUMBER,
240 p_new_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec,
241 p_loading_status IN VARCHAR2,
242 x_loading_status OUT NOCOPY VARCHAR2
243 )
244 IS
245 l_api_name CONSTANT VARCHAR2 (30) := 'Check_Valid_Update';
246 l_same_pe NUMBER;
247 l_loading_status VARCHAR2 (80);
248 BEGIN
249 -- Initialize API return status to success
250 x_return_status := fnd_api.g_ret_sts_success;
251 x_loading_status := p_loading_status;
252 -- get old revenue class id using old revenue class name
253 x_rev_class_id_old := cn_api.get_rev_class_id (p_rev_class_name_old,p_new_pe_rec.org_id);
254
255 -- Old revenue class exists and valid in the database
256 IF p_rev_class_name_old IS NULL
257 THEN
258 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
259 THEN
260 fnd_message.set_name ('CN', 'CN_REV_CLASS_NOT_ASSIGNED');
261 fnd_msg_pub.ADD;
262 END IF;
263
264 x_loading_status := 'REV_CLASS_NOT_ASSIGNED';
265 RAISE fnd_api.g_exc_error;
266 END IF;
267
268 IF x_rev_class_id_old IS NULL AND p_rev_class_name_old IS NOT NULL
269 THEN
270 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
271 THEN
272 fnd_message.set_name ('CN', 'CN_REV_CLASS_NOT_EXIST');
273 fnd_msg_pub.ADD;
274 END IF;
275
276 x_loading_status := 'CN_REV_CLASS_NOT_EXIST';
277 RAISE fnd_api.g_exc_error;
278 END IF;
279
280 SELECT COUNT (*)
281 INTO l_same_pe
282 FROM cn_quota_rules qr
283 WHERE qr.revenue_class_id = (SELECT revenue_class_id
284 FROM cn_revenue_classes
285 WHERE revenue_class_id = x_rev_class_id_old) AND qr.quota_id = p_new_pe_rec.quota_id;
286
287 IF l_same_pe = 0
288 THEN
289 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
290 THEN
291 fnd_message.set_name ('CN', 'CN_REV_CLASS_NOT_ASSIGNED');
292 fnd_msg_pub.ADD;
293 END IF;
294
295 x_loading_status := 'REV_CLASS_NOT_ASSIGNED';
296 RAISE fnd_api.g_exc_error;
297 END IF;
298
299 IF p_new_pe_rec.rev_class_id <> NVL (x_rev_class_id_old, 0)
300 THEN
301 SELECT COUNT (*)
302 INTO l_same_pe
303 FROM cn_quota_rules qr
304 WHERE qr.revenue_class_id = (SELECT revenue_class_id
305 FROM cn_revenue_classes
306 WHERE revenue_class_id = p_new_pe_rec.rev_class_id) AND qr.quota_id = p_new_pe_rec.quota_id;
307
308 IF l_same_pe > 0
309 THEN
310 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
311 THEN
312 fnd_message.set_name ('CN', 'PLN_QUOTA_REV_EXIST');
313 fnd_message.set_token ('PLAN_NAME', p_new_pe_rec.NAME);
314 fnd_message.set_token ('REVENUE_CLASS_NAME', p_new_pe_rec.rev_class_name);
315 fnd_msg_pub.ADD;
316 END IF;
317
318 x_loading_status := 'PLN_QUOTA_REV_EXISTS';
319 RAISE fnd_api.g_exc_error;
320 END IF;
321 END IF;
322
323 --+
324 -- get the Old quota Rule ID, Used for Update or Delete
325 --+
326 x_quota_rule_id_old := cn_chk_plan_element_pkg.get_quota_rule_id (p_quota_id => p_new_pe_rec.quota_id, p_rev_class_id => x_rev_class_id_old);
327
328 IF x_quota_rule_id_old IS 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_QUOTA_RULE_NOT_EXIST');
333 fnd_message.set_token ('PLAN_NAME', p_new_pe_rec.NAME);
334 fnd_message.set_token ('REVENUE_CLASS_NAME', p_new_pe_rec.rev_class_name);
335 fnd_msg_pub.ADD;
336 END IF;
337
338 x_loading_status := 'QUOTA_RULE_NOT_EXIST';
339 RAISE fnd_api.g_exc_error;
340 END IF;
341
342 -- Default Validations
343 valid_quota_rules (x_return_status => x_return_status,
344 x_msg_count => x_msg_count,
345 x_msg_data => x_msg_data,
346 p_rev_class_name_old => p_rev_class_name_old,
347 p_pe_rec => p_new_pe_rec,
348 p_loading_status => x_loading_status,
349 x_loading_status => l_loading_status
350 );
351 x_loading_status := l_loading_status;
352
353 IF (x_return_status <> fnd_api.g_ret_sts_success)
354 THEN
355 RAISE fnd_api.g_exc_error;
356 END IF;
357
358 -- End of API body.
359 -- Standard call to get message count and if count is 1, get message info.
360 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
361 EXCEPTION
362 WHEN fnd_api.g_exc_error
363 THEN
364 x_return_status := fnd_api.g_ret_sts_error;
365 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
366 WHEN fnd_api.g_exc_unexpected_error
367 THEN
368 x_return_status := fnd_api.g_ret_sts_unexp_error;
369 l_loading_status := 'UNEXPECTED_ERR';
370 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
371 WHEN OTHERS
372 THEN
373 x_return_status := fnd_api.g_ret_sts_unexp_error;
374 x_loading_status := 'UNEXPECTED_ERR';
375
376 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
377 THEN
378 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
379 END IF;
380
381 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
382 END check_valid_update;
383
384 --|/*-----------------------------------------------------------------------+
385 --| Procedure Name: Create_Quota_Rules
386 --| Descr: Create a Quota Rules
387 --|----------------------------------------------------------------------- */
388 PROCEDURE create_quota_rules (
389 p_api_version IN NUMBER,
390 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
391 p_commit IN VARCHAR2 := fnd_api.g_false,
392 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
393 x_return_status OUT NOCOPY VARCHAR2,
394 x_msg_count OUT NOCOPY NUMBER,
395 x_msg_data OUT NOCOPY VARCHAR2,
396 p_quota_name IN VARCHAR2,
397 p_revenue_class_rec_tbl IN cn_plan_element_pub.revenue_class_rec_tbl_type := cn_plan_element_pub.g_miss_revenue_class_rec_tbl,
398 p_rev_uplift_rec_tbl IN cn_plan_element_pub.rev_uplift_rec_tbl_type := cn_plan_element_pub.g_miss_rev_uplift_rec_tbl,
399 p_trx_factor_rec_tbl IN cn_plan_element_pub.trx_factor_rec_tbl_type := cn_plan_element_pub.g_miss_trx_factor_rec_tbl,
400 x_loading_status OUT NOCOPY VARCHAR2
401 )
402 IS
403 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Quota_Rules';
404 l_api_version CONSTANT NUMBER := 1.0;
405 l_pe_rec cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec;
406 l_revenue_class_rec cn_plan_element_pub.revenue_class_rec_type;
407 l_trx_factor_rec cn_plan_element_pub.trx_factor_rec_type;
408 l_quota_rule_id NUMBER;
409 l_meaning cn_lookups.meaning%TYPE;
410 l_loading_status VARCHAR2 (80);
411 BEGIN
412 --
413 -- Standard Start of API savepoint
414 -- +
415 SAVEPOINT create_plan_element;
416
417 --+
418 -- Standard call to check for call compatibility.
419 --+
420 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
421 THEN
422 RAISE fnd_api.g_exc_unexpected_error;
423 END IF;
424
425 --+
426 -- Initialize message list if p_init_msg_list is set to TRUE.
427 -- +
428 IF fnd_api.to_boolean (p_init_msg_list)
429 THEN
430 fnd_msg_pub.initialize;
431 END IF;
432
433 -- +
434 -- Initialize API return status to success
435 --+
436 x_return_status := fnd_api.g_ret_sts_success;
437 x_loading_status := 'CN_INSERTED';
438
439 -- +
440 -- API body
441 -- +
442 IF (p_revenue_class_rec_tbl.COUNT <> 0)
443 THEN
444 -- Loop through each record and check go through the normal validations
445 -- and etc.
446 FOR i IN p_revenue_class_rec_tbl.FIRST .. p_revenue_class_rec_tbl.LAST
447 LOOP
448 -- convert the user input into the local record
449 l_pe_rec :=
450 convert_rev_class_user_input (p_quota_name => p_quota_name,
451 p_revenue_class_rec => p_revenue_class_rec_tbl (i),
452 x_return_status => x_return_status,
453 p_loading_status => x_loading_status,
454 x_loading_status => l_loading_status
455 );
456 x_loading_status := l_loading_status;
457
458 IF (x_return_status <> fnd_api.g_ret_sts_success)
459 THEN
460 RAISE fnd_api.g_exc_error;
461 END IF;
462
463 -- Validate Quota Rules
464 valid_quota_rules (x_return_status => x_return_status,
465 x_msg_count => x_msg_count,
466 x_msg_data => x_msg_data,
467 p_pe_rec => l_pe_rec,
468 p_loading_status => x_loading_status,
469 x_loading_status => l_loading_status
470 );
471 x_loading_status := l_loading_status;
472
473 IF (x_return_status <> fnd_api.g_ret_sts_success)
474 THEN
475 RAISE fnd_api.g_exc_error;
476 END IF;
477
478 -- Check not required if already exists in the database
479 -- Already passed the validation while insert the record
480 -- Now it is thinking that there is possibility for the
481 -- trx factor update( no insert allowed for trx)
482 -- or uplift insert
483 IF x_loading_status <> 'QUOTA_RULE_NOT_EXIST'
484 THEN
485 cn_chk_plan_element_pkg.valid_revenue_class (x_return_status => x_return_status,
486 p_pe_rec => l_pe_rec,
487 p_loading_status => x_loading_status,
488 x_loading_status => l_loading_status
489 );
490 x_loading_status := l_loading_status;
491 END IF;
492
493 -- Check return status and insert if the status is CN_INSERTED
494 -- then inser the Quota Rules, Insert the trx
495 --ELSE Record Already exists, but Trx count > 0
496 -- Update trx factors
497 -- EXLSE Record Already Exists
498 IF (x_return_status <> fnd_api.g_ret_sts_success)
499 THEN
500 RAISE fnd_api.g_exc_error;
501 ELSIF (x_return_status = fnd_api.g_ret_sts_success) AND (x_loading_status = 'CN_INSERTED')
502 THEN
503 IF l_pe_rec.incentive_type_code IN ('COMMISSION', 'BONUS')
504 THEN
505 cn_quota_rules_pkg.begin_record (x_operation => 'INSERT',
506 x_object_version_number => l_pe_rec.object_version_number,
507 x_org_id => l_pe_rec.org_id,
508 x_quota_rule_id => l_pe_rec.quota_rule_id,
509 x_quota_id => l_pe_rec.quota_id,
510 x_revenue_class_id => l_pe_rec.rev_class_id,
511 x_revenue_class_name => l_pe_rec.rev_class_name,
512 x_target => l_pe_rec.rev_class_target,
513 x_revenue_class_id_old => l_pe_rec.rev_class_id,
514 x_target_old => l_pe_rec.rev_class_target,
515 x_payment_amount => l_pe_rec.rev_class_payment_amount,
516 x_performance_goal => l_pe_rec.rev_class_performance_goal,
517 x_last_update_date => g_last_update_date,
518 x_last_updated_by => g_last_updated_by,
519 x_creation_date => g_creation_date,
520 x_created_by => g_created_by,
521 x_last_update_login => g_last_update_login,
522 x_program_type => g_program_type,
523 x_status_code => NULL,
524 x_payment_amount_old => NULL,
525 x_performance_goal_old => NULL
526 );
527
528 -- Insert the trx Factor fix each revenue Class you insert only if pass the
529 -- trx factor record otherwise it default.
530 -- Trx Factor data should be loaded from p_trx_factor_rec_tbl,
531 -- Since we insert data with default value already, so need to
532 -- delete then insert it again
533 IF (p_trx_factor_rec_tbl.COUNT <> 0)
534 THEN
535 FOR i IN p_trx_factor_rec_tbl.FIRST .. p_trx_factor_rec_tbl.LAST
536 LOOP
537 IF (p_trx_factor_rec_tbl.EXISTS (i)) AND (p_trx_factor_rec_tbl (i).rev_class_name = l_pe_rec.rev_class_name)
538 THEN
539 l_meaning := cn_api.get_lkup_meaning (p_trx_factor_rec_tbl (i).trx_type, 'TRX TYPES');
540
541 IF l_meaning IS NULL
542 THEN
543 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
544 THEN
545 fnd_message.set_name ('CN', 'CN_TRX_TYPE_NOT_EXISTS');
546 fnd_msg_pub.ADD;
547 END IF;
548
549 x_loading_status := 'CN_TRX_TYPE_NOT_EXISTS';
550 RAISE fnd_api.g_exc_error;
551 END IF;
552
553 UPDATE cn_trx_factors
554 SET event_factor = p_trx_factor_rec_tbl (i).event_factor
555 WHERE quota_rule_id = l_pe_rec.quota_rule_id
556 AND quota_id = l_pe_rec.quota_id
557 AND trx_type = p_trx_factor_rec_tbl (i).trx_type;
558 END IF; -- trx Factor Exists
559 END LOOP; -- Trx Loop
560
561 --+
562 -- validate Rule :
563 -- Check TRX_FACTORS
564 -- 1. Key Factor's total = 100
565 -- 2. Must have Trx_Factors
566 --+
567 cn_chk_plan_element_pkg.chk_trx_factor (x_return_status => x_return_status,
568 p_quota_rule_id => l_pe_rec.quota_rule_id,
569 p_rev_class_name => l_pe_rec.rev_class_name,
570 p_loading_status => x_loading_status,
571 x_loading_status => l_loading_status
572 );
573 x_loading_status := l_loading_status;
574
575 IF (x_return_status <> fnd_api.g_ret_sts_success) OR x_loading_status <> 'CN_INSERTED'
576 THEN
577 RAISE fnd_api.g_exc_error;
578 END IF;
579 END IF; -- end (p_trx_factor_rec_tbl.COUNT <> 0)
580 END IF; -- Element_type COMMISSION, BONUES
581 ELSIF (x_loading_status = 'PLN_QUOTA_REV_EXISTS')
582 THEN
583 IF (p_trx_factor_rec_tbl.COUNT = 0 AND p_rev_uplift_rec_tbl.COUNT = 0)
584 THEN
585 RAISE fnd_api.g_exc_error;
586 ELSIF p_trx_factor_rec_tbl.COUNT <> 0
587 THEN
588 -- Custom trx factors it means we need to update
589 -- exisiting trx factors.
590 NULL;
591 -- Taken care in the calling Place.
592 ELSIF p_rev_uplift_rec_tbl.COUNT > 0
593 THEN
594 x_loading_status := 'CN_INSERTED'; -- Calling Place will handle this
595 END IF; -- Case
596 END IF; -- CN_INSERTED.
597 END LOOP; -- Revenue Class
598 END IF; -- Table Count is Not Zero
599
600 -- End of API body.
601 -- Standard check of p_commit.
602 IF fnd_api.to_boolean (p_commit)
603 THEN
604 COMMIT WORK;
605 END IF;
606
607 --+
608 -- Standard call to get message count and if count is 1, get message info.
609 --+
610 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
611 EXCEPTION
612 WHEN fnd_api.g_exc_error
613 THEN
614 ROLLBACK TO create_plan_element;
615 x_return_status := fnd_api.g_ret_sts_error;
616 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
617 WHEN fnd_api.g_exc_unexpected_error
618 THEN
619 ROLLBACK TO create_plan_element;
620 x_loading_status := 'UNEXPECTED_ERR';
621 x_return_status := fnd_api.g_ret_sts_unexp_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 OTHERS
624 THEN
625 ROLLBACK TO create_plan_element;
626 x_loading_status := 'UNEXPECTED_ERR';
627 x_return_status := fnd_api.g_ret_sts_unexp_error;
628
629 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
630 THEN
631 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
632 END IF;
633
634 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
635 END create_quota_rules;
636
637 --|-------------------------------------------------------------------------+
638 --| Procedure Name: Update_Quota_Rules
639 --| Descr: Update a Quota Rules
640 --|-------------------------------------------------------------------------+
641 PROCEDURE update_quota_rules (
642 p_api_version IN NUMBER,
643 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
644 p_commit IN VARCHAR2 := fnd_api.g_false,
645 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
646 x_return_status OUT NOCOPY VARCHAR2,
647 x_msg_count OUT NOCOPY NUMBER,
648 x_msg_data OUT NOCOPY VARCHAR2,
649 p_quota_name IN VARCHAR2,
650 p_revenue_class_rec_tbl IN cn_plan_element_pub.revenue_class_rec_tbl_type := cn_plan_element_pub.g_miss_revenue_class_rec_tbl,
651 p_trx_factor_rec_tbl IN cn_plan_element_pub.trx_factor_rec_tbl_type := cn_plan_element_pub.g_miss_trx_factor_rec_tbl,
652 x_loading_status OUT NOCOPY VARCHAR2
653 )
654 IS
655 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Quota_Rules';
656 l_api_version CONSTANT NUMBER := 1.0;
657 l_pe_rec cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec;
658 l_revenue_class_rec cn_plan_element_pub.revenue_class_rec_type;
659 l_trx_factor_rec cn_plan_element_pub.trx_factor_rec_type;
660 l_quota_rule_id NUMBER;
661 l_rev_class_id_old NUMBER;
662 l_loading_status VARCHAR2 (80);
663 BEGIN
664 --
665 -- Standard Start of API savepoint
666 -- +
667 SAVEPOINT update_plan_element;
668
669 --+
670 -- Standard call to check for call compatibility.
671 --+
672 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
673 THEN
674 RAISE fnd_api.g_exc_unexpected_error;
675 END IF;
676
677 --+
678 -- Initialize message list if p_init_msg_list is set to TRUE.
679 -- +
680 IF fnd_api.to_boolean (p_init_msg_list)
681 THEN
682 fnd_msg_pub.initialize;
683 END IF;
684
685 -- +
686 -- Initialize API return status to success
687 --+
688 x_return_status := fnd_api.g_ret_sts_success;
689 x_loading_status := 'CN_UPDATED';
690
691 -- +
692 -- API body
693 -- +
694 IF (p_revenue_class_rec_tbl.COUNT <> 0)
695 THEN
696 FOR i IN p_revenue_class_rec_tbl.FIRST .. p_revenue_class_rec_tbl.LAST
697 LOOP
698 -- Convert the User input into the local variable.
699 l_pe_rec :=
700 convert_rev_class_user_input (p_quota_name => p_quota_name,
701 p_revenue_class_rec => p_revenue_class_rec_tbl (i),
702 x_return_status => x_return_status,
703 p_loading_status => x_loading_status,
704 x_loading_status => l_loading_status
705 );
706 x_loading_status := l_loading_status;
707
708 -- if Any Error Raise an Error
709 IF (x_return_status <> fnd_api.g_ret_sts_success)
710 THEN
711 RAISE fnd_api.g_exc_error;
712 END IF;
713
714 -- Check for valid Update
715 check_valid_update (x_return_status => x_return_status,
716 x_msg_count => x_msg_count,
717 x_msg_data => x_msg_data,
718 p_rev_class_name_old => p_revenue_class_rec_tbl (i).rev_class_name_old,
719 x_rev_class_id_old => l_rev_class_id_old,
720 x_quota_rule_id_old => l_quota_rule_id,
721 p_new_pe_rec => l_pe_rec,
722 p_loading_status => x_loading_status,
723 x_loading_status => l_loading_status
724 );
725 x_loading_status := l_loading_status;
726
727 -- If not success then Raise an Error
728 IF (x_return_status <> fnd_api.g_ret_sts_success)
729 THEN
730 RAISE fnd_api.g_exc_error;
731 END IF;
732
733 --+
734 -- IF you change the Revenue Class check for nested child
735 --+
736 IF l_pe_rec.rev_class_id <> l_rev_class_id_old
737 THEN
738 cn_chk_plan_element_pkg.valid_revenue_class (x_return_status => x_return_status,
739 p_pe_rec => l_pe_rec,
740 p_revenue_class_id_old => l_rev_class_id_old,
741 p_loading_status => x_loading_status,
742 x_loading_status => l_loading_status
743 );
744 x_loading_status := l_loading_status;
745 END IF;
746
747 -- if faliure raise an error
748 IF (x_return_status <> fnd_api.g_ret_sts_success)
749 THEN
750 RAISE fnd_api.g_exc_error;
751 END IF;
752
753 IF (x_return_status <> fnd_api.g_ret_sts_success)
754 THEN
755 RAISE fnd_api.g_exc_error;
756 ELSIF (x_return_status = fnd_api.g_ret_sts_success) AND (x_loading_status = 'CN_UPDATED')
757 THEN
758 IF l_pe_rec.incentive_type_code IN ('COMMISSION', 'BONUS')
759 THEN
760 cn_quota_rules_pkg.begin_record (x_operation => 'UPDATE',
761 x_quota_rule_id => l_quota_rule_id,
762 x_object_version_number => l_pe_rec.object_version_number,
763 x_org_id => l_pe_rec.org_id,
764 x_quota_id => l_pe_rec.quota_id,
765 x_revenue_class_id => l_pe_rec.rev_class_id,
766 x_revenue_class_name => l_pe_rec.rev_class_name,
767 x_target => l_pe_rec.rev_class_target,
768 x_payment_amount => l_pe_rec.rev_class_payment_amount,
769 x_performance_goal => l_pe_rec.rev_class_performance_goal,
770 x_revenue_class_id_old => l_rev_class_id_old,
771 x_target_old => l_pe_rec.rev_class_target,
772 x_last_update_date => g_last_update_date,
773 x_last_updated_by => g_last_updated_by,
774 x_creation_date => g_creation_date,
775 x_created_by => g_created_by,
776 x_last_update_login => g_last_update_login,
777 x_program_type => g_program_type,
778 x_status_code => NULL,
779 x_payment_amount_old => NULL,
780 x_performance_goal_old => NULL
781 );
782
783 -- Insert the trx Factor fix each revenue Class you insert only if pass the
784 -- trx factor record otherwise it default.
785 IF (p_trx_factor_rec_tbl.COUNT <> 0)
786 THEN
787 FOR i IN p_trx_factor_rec_tbl.FIRST .. p_trx_factor_rec_tbl.LAST
788 LOOP
789 IF (p_trx_factor_rec_tbl.EXISTS (i)) AND (p_trx_factor_rec_tbl (i).rev_class_name = l_pe_rec.rev_class_name)
790 THEN
791 UPDATE cn_trx_factors
792 SET event_factor = p_trx_factor_rec_tbl (i).event_factor
793 WHERE quota_rule_id = l_quota_rule_id AND trx_type = p_trx_factor_rec_tbl (i).trx_type;
794 END IF; -- trx Factor Exists
795 END LOOP; -- Trx Loop
796
797 --+
798 -- validate Rule :
799 -- Check TRX_FACTORS
800 -- 1. Key Factor's total = 100
801 -- 2. Must have Trx_Factors
802 --+
803 cn_chk_plan_element_pkg.chk_trx_factor (x_return_status => x_return_status,
804 p_quota_rule_id => l_quota_rule_id,
805 p_rev_class_name => l_pe_rec.rev_class_name,
806 p_loading_status => x_loading_status,
807 x_loading_status => l_loading_status
808 );
809 x_loading_status := l_loading_status;
810
811 -- If the status is <> S or if the loading status is changed THEN
812 -- Raise an Error
813 IF (x_return_status <> fnd_api.g_ret_sts_success) OR x_loading_status <> 'CN_UPDATED'
814 THEN
815 RAISE fnd_api.g_exc_error;
816 END IF;
817 END IF; -- End if (p_trx_factor_rec_tbl.COUNT <> 0)
818 END IF; -- end if for Element_type = COMMISSION, BONUES
819 ELSIF (x_loading_status = 'PLN_QUOTA_REV_EXISTS')
820 THEN
821 IF (p_trx_factor_rec_tbl.COUNT = 0 AND p_revenue_class_rec_tbl.COUNT = 0)
822 THEN
823 RAISE fnd_api.g_exc_error;
824 ELSIF p_trx_factor_rec_tbl.COUNT <> 0
825 THEN
826 -- insert into the trx_factors
827 NULL;
828 END IF;
829 END IF; -- Not success
830 END LOOP; -- Revenue Class
831 END IF; -- Table Count is Not Zero
832
833 -- End of API body.
834 -- Standard check of p_commit.
835 IF fnd_api.to_boolean (p_commit)
836 THEN
837 COMMIT WORK;
838 END IF;
839
840 --+
841 -- Standard call to get message count and if count is 1, get message info.
842 --+
843 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
844 EXCEPTION
845 WHEN fnd_api.g_exc_error
846 THEN
847 ROLLBACK TO update_plan_element;
848 x_return_status := fnd_api.g_ret_sts_error;
849 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
850 WHEN fnd_api.g_exc_unexpected_error
851 THEN
852 ROLLBACK TO update_plan_element;
853 x_loading_status := 'UNEXPECTED_ERR';
854 x_return_status := fnd_api.g_ret_sts_unexp_error;
855 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
856 WHEN OTHERS
857 THEN
858 ROLLBACK TO update_plan_element;
859 x_loading_status := 'UNEXPECTED_ERR';
860 x_return_status := fnd_api.g_ret_sts_unexp_error;
861
862 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
863 THEN
864 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
865 END IF;
866
867 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
868 END update_quota_rules;
869
870 --|-------------------------------------------------------------------------+
871 --| Procedure Name: Delete_Quota_Rules
872 --| Descr: Delete a Quota Rules
873 --|-------------------------------------------------------------------------+
874 PROCEDURE delete_quota_rules (
875 p_api_version IN NUMBER,
876 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
877 p_commit IN VARCHAR2 := fnd_api.g_false,
878 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
879 x_return_status OUT NOCOPY VARCHAR2,
880 x_msg_count OUT NOCOPY NUMBER,
881 x_msg_data OUT NOCOPY VARCHAR2,
882 p_quota_name IN VARCHAR2,
883 p_revenue_class_rec_tbl IN cn_plan_element_pub.revenue_class_rec_tbl_type := cn_plan_element_pub.g_miss_revenue_class_rec_tbl,
884 p_trx_factor_rec_tbl IN cn_plan_element_pub.trx_factor_rec_tbl_type := cn_plan_element_pub.g_miss_trx_factor_rec_tbl,
885 x_loading_status OUT NOCOPY VARCHAR2
886 )
887 IS
888 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Plan_Element';
889 l_api_version CONSTANT NUMBER := 1.0;
890 l_rev_rec cn_quota_rules%ROWTYPE;
891 l_loading_status VARCHAR2 (80);
892 BEGIN
893 -- Standard Start of API savepoint
894 SAVEPOINT delete_plan_element;
895
896 -- Standard call to check for call compatibility.
897 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
898 THEN
899 RAISE fnd_api.g_exc_unexpected_error;
900 END IF;
901
902 -- Initialize message list if p_init_msg_list is set to TRUE.
903 IF fnd_api.to_boolean (p_init_msg_list)
904 THEN
905 fnd_msg_pub.initialize;
906 END IF;
907
908 -- Initialize API return status to success
909 x_return_status := fnd_api.g_ret_sts_success;
910 x_loading_status := 'CN_DELETED';
911
912 -- API body
913 -- Store the User Input Value into The Local Variable.
914 -- Standard check of p_commit.
915 --+
916 -- Check if plan element name is missing or null even for Delete the Q Rule
917 IF ((cn_api.chk_miss_char_para (p_char_para => p_quota_name,
918 p_para_name => cn_chk_plan_element_pkg.g_pe_name,
919 p_loading_status => x_loading_status,
920 x_loading_status => l_loading_status
921 )
922 ) = fnd_api.g_true
923 )
924 THEN
925 RAISE fnd_api.g_exc_error;
926 ELSIF ((cn_api.chk_null_char_para (p_char_para => p_quota_name,
927 p_obj_name => cn_chk_plan_element_pkg.g_pe_name,
928 p_loading_status => x_loading_status,
929 x_loading_status => l_loading_status
930 )
931 ) = fnd_api.g_true
932 )
933 THEN
934 RAISE fnd_api.g_exc_error;
935 END IF;
936
937 -- Get the Quota ID
938 l_rev_rec.quota_id := cn_chk_plan_element_pkg.get_quota_id (LTRIM (RTRIM (p_quota_name)),p_revenue_class_rec_tbl(1).org_id);
939
940 -- Raise an Error If quota id is null but name is not null
941 IF l_rev_rec.quota_id IS NULL AND p_quota_name IS NOT NULL
942 THEN
943 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
944 THEN
945 fnd_message.set_name ('CN', 'CN_PLN_NOT_EXIST');
946 fnd_message.set_token ('PE_NAME', p_quota_name);
947 fnd_msg_pub.ADD;
948 END IF;
949
950 x_loading_status := 'CN_PLN_NOT_EXIST';
951 RAISE fnd_api.g_exc_error;
952 END IF;
953
954 -- Loop Through Each Record and Delete IT
955 IF p_revenue_class_rec_tbl.COUNT > 0
956 THEN
957 FOR i IN 1 .. p_revenue_class_rec_tbl.COUNT
958 LOOP
959 -- Get Revenue Class ID
960 l_rev_rec.revenue_class_id := cn_api.get_rev_class_id (p_revenue_class_rec_tbl (i).rev_class_name,p_revenue_class_rec_tbl (i).org_id);
961
962 -- Raise an Error if the Revenue Class iD is Null and Name IS not NUll
963 IF l_rev_rec.revenue_class_id IS NULL AND p_revenue_class_rec_tbl (i).rev_class_name IS NOT NULL
964 THEN
965 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
966 THEN
967 fnd_message.set_name ('CN', 'CN_REV_CLASS_NOT_EXIST');
968 fnd_msg_pub.ADD;
969 END IF;
970
971 x_loading_status := 'CN_REV_CLASS_NOT_EXIST';
972 RAISE fnd_api.g_exc_error;
973 END IF;
974
975 -- Get the Quota Rule ID
976 l_rev_rec.quota_rule_id := cn_chk_plan_element_pkg.get_quota_rule_id (l_rev_rec.quota_id, l_rev_rec.revenue_class_id);
977
978 IF l_rev_rec.quota_rule_id IS NULL
979 THEN
980 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
981 THEN
982 fnd_message.set_name ('CN', 'CN_QUOTA_RULE_NOT_EXIST');
983 fnd_message.set_token ('PLAN_NAME', p_quota_name);
984 fnd_message.set_token ('REVENUE_CLASS_NAME', p_revenue_class_rec_tbl (i).rev_class_name);
985 fnd_msg_pub.ADD;
986 END IF;
987
988 x_loading_status := 'QUOTA_RULE_NOT_EXIST';
989 RAISE fnd_api.g_exc_error;
990 END IF;
991
992 IF (x_return_status <> fnd_api.g_ret_sts_success)
993 THEN
994 RAISE fnd_api.g_exc_error;
995 ELSIF (x_return_status = fnd_api.g_ret_sts_success AND x_loading_status = 'CN_DELETED')
996 THEN
997 -- Delete Record;
998 cn_quota_rules_pkg.DELETE_RECORD (x_quota_id => l_rev_rec.quota_id,
999 x_quota_rule_id => l_rev_rec.quota_rule_id,
1000 x_revenue_class_id => l_rev_rec.revenue_class_id
1001 );
1002 END IF;
1003 END LOOP;
1004 END IF;
1005
1006 -- standard Commit
1007 IF fnd_api.to_boolean (p_commit)
1008 THEN
1009 COMMIT WORK;
1010 END IF;
1011
1012 --+
1013 -- Standard call to get message count and if count is 1, get message info.
1014 --+
1015 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1016 EXCEPTION
1017 WHEN fnd_api.g_exc_error
1018 THEN
1019 ROLLBACK TO delete_plan_element;
1020 x_return_status := fnd_api.g_ret_sts_error;
1021 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1022 WHEN fnd_api.g_exc_unexpected_error
1023 THEN
1024 ROLLBACK TO delete_plan_element;
1025 x_loading_status := 'UNEXPECTED_ERR';
1026 x_return_status := fnd_api.g_ret_sts_unexp_error;
1027 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1028 WHEN OTHERS
1029 THEN
1030 ROLLBACK TO delete_plan_element;
1031 x_loading_status := 'UNEXPECTED_ERR';
1032 x_return_status := fnd_api.g_ret_sts_unexp_error;
1033
1034 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1035 THEN
1036 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1037 END IF;
1038
1039 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1040 END delete_quota_rules;
1041 END cn_quota_rules_grp;