[Home] [Help]
PACKAGE BODY: APPS.CN_PLAN_ELEMENT_PVT
Source
1 PACKAGE BODY cn_plan_element_pvt AS
2 /*$Header: cnvpeb.pls 120.24 2007/11/14 15:40:42 hanaraya ship $*/
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CN_PLAN_ELEMENT_PVT';
4
5 -- Returns a plan element record type given a quota_id
6 FUNCTION get_plan_element (
7 p_quota_id NUMBER
8 )
9 RETURN plan_element_rec_type
10 IS
11 CURSOR c_plan_element_csr
12 IS
13 SELECT pe.quota_id,
14 pe.NAME,
15 pe.description,
16 pe.quota_type_code,
17 pe.target,
18 pe.payment_amount,
19 pe.performance_goal,
20 pe.incentive_type_code,
21 pe.start_date,
22 pe.end_date,
23 pe.credit_type_id,
24 pe.interval_type_id,
25 pe.calc_formula_id,
26 pe.liability_account_id,
27 pe.expense_account_id,
28 'liability_account_cc',
29 'expense_account_cc',
30 pe.vesting_flag,
31 pe.quota_group_code,
32 pe.payment_group_code,
33 pe.attribute_category,
34 pe.attribute1,
35 pe.attribute2,
36 pe.attribute3,
37 pe.attribute4,
38 pe.attribute5,
39 pe.attribute6,
40 pe.attribute7,
41 pe.attribute8,
42 pe.attribute9,
43 pe.attribute10,
44 pe.attribute11,
45 pe.attribute12,
46 pe.attribute13,
47 pe.attribute14,
48 pe.attribute15,
49 pe.addup_from_rev_class_flag,
50 pe.payee_assign_flag,
51 pe.package_name,
52 pe.object_version_number,
53 pe.org_id,
54 pe.indirect_credit,
55 pe.quota_status,
56 pe.salesreps_enddated_flag,
57 NULL
58 FROM cn_quotas_v pe
59 WHERE pe.quota_id = p_quota_id;
60
61 l_plan_element plan_element_rec_type;
62 BEGIN
63 -- fetch the old record
64 OPEN c_plan_element_csr;
65
66 FETCH c_plan_element_csr
67 INTO l_plan_element;
68
69 IF c_plan_element_csr%NOTFOUND
70 THEN
71 fnd_message.set_name ('CN', 'CN_INVALID_UPDATE_REC');
72 fnd_msg_pub.ADD;
73
74 CLOSE c_plan_element_csr;
75
76 RAISE fnd_api.g_exc_error;
77 END IF;
78
79 CLOSE c_plan_element_csr;
80
81 RETURN l_plan_element;
82 END;
83
84 -------------------------------------------------------------------------+++++++++++++++++++++++
85 -- Procedure : is_valid_org
86 -- Description : validates that the org id is valid and consistent with that of the planelement
87 -------------------------------------------------------------------------++++++++++++++++++++++++
88 FUNCTION is_valid_org (
89 p_org_id NUMBER,
90 p_quota_id NUMBER := NULL
91 )
92 RETURN BOOLEAN
93 IS
94 l_return VARCHAR2 (100) := NULL;
95 l_dummy NUMBER;
96 l_ret_val BOOLEAN := FALSE;
97 BEGIN
98 l_return := mo_global.check_valid_org (p_org_id);
99
100 IF l_return = 'Y'
101 THEN
102 l_ret_val := TRUE;
103
104 IF p_quota_id IS NOT NULL
105 THEN
106 BEGIN
107 SELECT 1
108 INTO l_dummy
109 FROM DUAL
110 WHERE EXISTS (SELECT 1
111 FROM cn_quotas_v
112 WHERE quota_id = p_quota_id AND org_id = p_org_id);
113 EXCEPTION
114 WHEN NO_DATA_FOUND
115 THEN
116 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
117 THEN
118 fnd_message.set_name ('FND', 'MO_ORG_INVALID');
119 fnd_msg_pub.ADD;
120 END IF;
121
122 RETURN FALSE;
123 END;
124 END IF;
125 END IF;
126
127 RETURN l_ret_val;
128 EXCEPTION
129 WHEN OTHERS
130 THEN
131 RETURN FALSE;
132 END is_valid_org;
133
134 -- -------------------------------------------------------------------------+-+
135 --| Procedure: add_system_note
136 --| Description: Insert notes for the create, update and delete
137 --| operations.
138 --| Called From: Create_plan_Element, Update_Plan_Element
139 --| Delete_Plan_Element
140 -- -------------------------------------------------------------------------+-+
141 PROCEDURE add_system_note(
142 p_plan_element_old IN OUT NOCOPY plan_element_rec_type,
143 p_plan_element_new IN OUT NOCOPY plan_element_rec_type,
144 p_operation IN VARCHAR2,
145 x_return_status OUT NOCOPY VARCHAR2,
146 x_msg_count OUT NOCOPY NUMBER,
147 x_msg_data OUT NOCOPY VARCHAR2
148 )
149 IS
150
151 l_note_msg VARCHAR2 (2000);
152 l_consolidated_note VARCHAR2(2000);
153 l_plan_element_id NUMBER;
154 l_note_id NUMBER;
155 l_temp_old VARCHAR2 (200);
156 l_temp_new VARCHAR2 (200);
157 l_temp_1 VARCHAR2 (200);
158 l_temp_2 VARCHAR2 (200);
159
160 BEGIN
161 -- Initialize to success
162 x_return_status := fnd_api.g_ret_sts_success;
163 -- Initialize other fields
164 x_msg_data := fnd_api.g_null_char;
165 x_msg_count := fnd_api.g_null_num;
166
167 IF (p_operation <> 'update') THEN
168 IF (p_operation = 'create') THEN
169 fnd_message.set_name('CN','CNR12_NOTE_PE_NAME_CREATE');
170 fnd_message.set_token('PE_NAME', p_plan_element_new.NAME);
171 l_plan_element_id := p_plan_element_new.quota_id;
172 l_temp_new := 'CN_QUOTAS';
173 END IF;
174 IF (p_operation = 'delete') THEN
175 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_NAME_DELETE');
176 fnd_message.set_token('PE_NAME', p_plan_element_old.NAME);
177 l_plan_element_id := p_plan_element_old.org_id;
178 l_temp_new := 'CN_DELETED_OBJECTS';
179 END IF;
180 l_note_msg := fnd_message.get;
181 jtf_notes_pub.create_note
182 (p_api_version => 1.0,
183 x_return_status => x_return_status,
184 x_msg_count => x_msg_count,
185 x_msg_data => x_msg_data,
186 p_source_object_id => l_plan_element_id,
187 p_source_object_code => l_temp_new,
188 p_notes => l_note_msg,
189 p_notes_detail => l_note_msg,
190 p_note_type => 'CN_SYSGEN', -- for system generated
191 x_jtf_note_id => l_note_id -- returned
192 );
193 ELSIF (p_operation = 'update') THEN
194 l_consolidated_note := '';
195 -- CHECK IF PE NAME WAS UPDATED
196 IF (p_plan_element_old.NAME <> p_plan_element_new.NAME) THEN
197 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_NAME_UPDATE');
198 fnd_message.set_token('PE_NAME_OLD', p_plan_element_old.NAME);
199 fnd_message.set_token('PE_NAME_NEW', p_plan_element_new.NAME);
200 l_plan_element_id := p_plan_element_new.quota_id;
201 l_note_msg := fnd_message.get;
202 l_consolidated_note := l_note_msg || fnd_global.local_chr(10);
203 /* jtf_notes_pub.create_note
204 (p_api_version => 1.0,
205 x_return_status => x_return_status,
206 x_msg_count => x_msg_count,
207 x_msg_data => x_msg_data,
208 p_source_object_id => l_plan_element_id,
209 p_source_object_code => 'CN_QUOTAS',
210 p_notes => l_note_msg,
211 p_notes_detail => l_note_msg,
212 p_note_type => 'CN_SYSGEN', -- for system generated
213 x_jtf_note_id => l_note_id -- returned
214 );*/
215 END IF;
216 -- CHECK IF START DATE WAS UPDATED
217 IF (p_plan_element_old.start_date <> p_plan_element_new.start_date) THEN
218 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_STDATE_UPDATE');
219 fnd_message.set_token('PE_START_OLD', p_plan_element_old.start_date);
220 fnd_message.set_token('PE_START_NEW', p_plan_element_new.start_date);
221 l_plan_element_id := p_plan_element_new.quota_id;
222 l_note_msg := fnd_message.get;
223 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
224 /* jtf_notes_pub.create_note
225 (p_api_version => 1.0,
226 x_return_status => x_return_status,
227 x_msg_count => x_msg_count,
228 x_msg_data => x_msg_data,
229 p_source_object_id => l_plan_element_id,
230 p_source_object_code => 'CN_QUOTAS',
231 p_notes => l_note_msg,
232 p_notes_detail => l_note_msg,
233 p_note_type => 'CN_SYSGEN', -- for system generated
234 x_jtf_note_id => l_note_id -- returned
235 );*/
236 END IF;
237 -- CHECK IF END DATE WAS UPDATED
238 IF (NVL(p_plan_element_old.end_date, fnd_api.g_miss_date)
239 <> NVL(p_plan_element_new.end_date, fnd_api.g_miss_date)) THEN
240 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_ENDATE_UPDATE');
241 IF (p_plan_element_old.end_date IS NULL) THEN
242 fnd_message.set_token('PE_END_OLD', 'NULL');
243 ELSE
244 fnd_message.set_token('PE_END_OLD', p_plan_element_old.end_date);
245 END IF;
246 IF (p_plan_element_new.end_date IS NULL) THEN
247 fnd_message.set_token('PE_END_NEW', 'NULL');
248 ELSE
249 fnd_message.set_token('PE_END_NEW', p_plan_element_new.end_date);
250 END IF;
251 l_plan_element_id := p_plan_element_new.quota_id;
252 l_note_msg := fnd_message.get;
253 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
254 /* jtf_notes_pub.create_note
255 (p_api_version => 1.0,
256 x_return_status => x_return_status,
257 x_msg_count => x_msg_count,
258 x_msg_data => x_msg_data,
259 p_source_object_id => l_plan_element_id,
260 p_source_object_code => 'CN_QUOTAS',
261 p_notes => l_note_msg,
262 p_notes_detail => l_note_msg,
263 p_note_type => 'CN_SYSGEN', -- for system generated
264 x_jtf_note_id => l_note_id -- returned
265 );*/
266 END IF;
267 -- CHECK IF DESCRIPTION WAS UPDATED
268 IF (p_plan_element_old.description <> p_plan_element_new.description) THEN
269 fnd_message.set_name ('CN','CNR12_NOTE_PE_DESC_UPDATE');
270 l_plan_element_id := p_plan_element_new.quota_id;
271 l_note_msg := fnd_message.get;
272 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
273 /* jtf_notes_pub.create_note
274 (p_api_version => 1.0,
275 x_return_status => x_return_status,
276 x_msg_count => x_msg_count,
277 x_msg_data => x_msg_data,
278 p_source_object_id => l_plan_element_id,
279 p_source_object_code => 'CN_QUOTAS',
280 p_notes => l_note_msg,
281 p_notes_detail => l_note_msg,
282 p_note_type => 'CN_SYSGEN', -- for system generated
283 x_jtf_note_id => l_note_id -- returned
284 );*/
285 END IF;
286
287 -- CHECK IF INTERVAL TYPE WAS UPDATED
288 IF (p_plan_element_old.interval_type_id <> p_plan_element_new.interval_type_id) THEN
289 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_INTTYPE_UPDATE');
290 l_plan_element_id := p_plan_element_new.quota_id;
291 select NAME into l_temp_old from CN_INTERVAL_TYPES
292 where interval_type_id = p_plan_element_old.interval_type_id
293 and org_id = p_plan_element_old.org_id;
294 select NAME into l_temp_new from CN_INTERVAL_TYPES
295 where interval_type_id = p_plan_element_new.interval_type_id
296 and org_id = p_plan_element_new.org_id;
297 fnd_message.set_token('PE_OLD_INTERVAL', l_temp_old);
298 fnd_message.set_token('PE_NEW_INTERVAL', l_temp_new);
299 l_note_msg := fnd_message.get;
300 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
301 /* jtf_notes_pub.create_note
302 (p_api_version => 1.0,
303 x_return_status => x_return_status,
304 x_msg_count => x_msg_count,
305 x_msg_data => x_msg_data,
306 p_source_object_id => l_plan_element_id,
307 p_source_object_code => 'CN_QUOTAS',
308 p_notes => l_note_msg,
309 p_notes_detail => l_note_msg,
310 p_note_type => 'CN_SYSGEN', -- for system generated
311 x_jtf_note_id => l_note_id -- returned
312 );*/
313 END IF;
314
315 -- CHECK IF FORMULA TYPE WAS UPDATED
316 IF (p_plan_element_old.quota_type_code <> p_plan_element_new.quota_type_code) THEN
317 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_FORTYPE_UPDATE');
318 l_plan_element_id := p_plan_element_new.quota_id;
319 l_temp_1 := cn_api.get_lkup_meaning(p_plan_element_old.quota_type_code, 'QUOTA_TYPE');
320 l_temp_2 := cn_api.get_lkup_meaning(p_plan_element_new.quota_type_code, 'QUOTA_TYPE');
321 fnd_message.set_token('PE_OLD_FOR', l_temp_1);
322 fnd_message.set_token('PE_NEW_FOR', l_temp_2);
323 l_note_msg := fnd_message.get;
324 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
325 /* jtf_notes_pub.create_note
326 (p_api_version => 1.0,
327 x_return_status => x_return_status,
328 x_msg_count => x_msg_count,
329 x_msg_data => x_msg_data,
330 p_source_object_id => l_plan_element_id,
331 p_source_object_code => 'CN_QUOTAS',
332 p_notes => l_note_msg,
333 p_notes_detail => l_note_msg,
334 p_note_type => 'CN_SYSGEN', -- for system generated
335 x_jtf_note_id => l_note_id -- returned
336 );*/
337 END IF;
338
339 -- CHECK IF FORMULA TYPE/NAME WAS UPDATED
340 IF (p_plan_element_old.quota_type_code <> p_plan_element_new.quota_type_code
341 OR p_plan_element_old.calc_formula_id <> p_plan_element_new.calc_formula_id
342 OR p_plan_element_old.package_name <> p_plan_element_new.package_name) THEN
343 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_FOR_TYPE_UPDATE');
344 l_plan_element_id := p_plan_element_new.quota_id;
345 IF (p_plan_element_old.quota_type_code = 'FORMULA') THEN
346 IF (p_plan_element_old.calc_formula_id IS NULL) THEN
347 l_temp_old := '';
348 ELSE
349 SELECT NAME INTO l_temp_old FROM CN_CALC_FORMULAS
350 WHERE CALC_FORMULA_ID = p_plan_element_old.calc_formula_id;
351 END IF;
352 ELSE
353 l_temp_old := p_plan_element_old.package_name;
354 END IF;
355 IF (p_plan_element_new.quota_type_code = 'FORMULA') THEN
356 SELECT NAME INTO l_temp_new FROM CN_CALC_FORMULAS
357 WHERE CALC_FORMULA_ID = p_plan_element_new.calc_formula_id;
358 ELSE
359 l_temp_new := p_plan_element_new.package_name;
360 END IF;
361 l_temp_1 := cn_api.get_lkup_meaning(p_plan_element_old.quota_type_code, 'QUOTA_TYPE');
362 l_temp_2 := cn_api.get_lkup_meaning(p_plan_element_new.quota_type_code, 'QUOTA_TYPE');
363 fnd_message.set_token('FORMULA_TYPE_OLD', l_temp_1);
364 fnd_message.set_token('FORMULA_NAME_OLD', l_temp_old);
365 fnd_message.set_token('FORMULA_TYPE_NEW', l_temp_2);
366 fnd_message.set_token('FORMULA_NAME_NEW', l_temp_new);
367 l_note_msg := fnd_message.get;
368 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
369 /* jtf_notes_pub.create_note
370 (p_api_version => 1.0,
371 x_return_status => x_return_status,
372 x_msg_count => x_msg_count,
373 x_msg_data => x_msg_data,
374 p_source_object_id => l_plan_element_id,
375 p_source_object_code => 'CN_QUOTAS',
376 p_notes => l_note_msg,
377 p_notes_detail => l_note_msg,
378 p_note_type => 'CN_SYSGEN', -- for system generated
379 x_jtf_note_id => l_note_id -- returned
380 );*/
381 END IF;
382
383 -- CHECK IF PAYMENT GROUP WAS UPDATED
384 IF (p_plan_element_old.payment_group_code <> p_plan_element_new.payment_group_code) THEN
385 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_PAYGRP_UPDATE');
386 fnd_message.set_token('PAYGRP_OLD', p_plan_element_old.payment_group_code);
387 fnd_message.set_token('PAYGRP_NEW', p_plan_element_new.payment_group_code);
388 l_plan_element_id := p_plan_element_new.quota_id;
389 l_note_msg := fnd_message.get;
390 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
391 /* jtf_notes_pub.create_note
392 (p_api_version => 1.0,
393 x_return_status => x_return_status,
394 x_msg_count => x_msg_count,
395 x_msg_data => x_msg_data,
396 p_source_object_id => l_plan_element_id,
397 p_source_object_code => 'CN_QUOTAS',
398 p_notes => l_note_msg,
399 p_notes_detail => l_note_msg,
400 p_note_type => 'CN_SYSGEN', -- for system generated
401 x_jtf_note_id => l_note_id -- returned
402 );*/
403 END IF;
404
405 -- CHECK IF CREDIT TYPE WAS UPDATED
406 IF (p_plan_element_old.credit_type_id <> p_plan_element_new.credit_type_id) THEN
407 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_CRTYPE_UPDATE');
408 select name into l_temp_old from cn_credit_types_vl
409 where credit_type_id = p_plan_element_old.credit_type_id
410 and org_id = p_plan_element_old.org_id;
411 select name into l_temp_new from cn_credit_types_vl
412 where credit_type_id = p_plan_element_new.credit_type_id
413 and org_id = p_plan_element_new.org_id;
414 fnd_message.set_token('PE_OLD_CREDIT', l_temp_old);
415 fnd_message.set_token('PE_NEW_CREDIT', l_temp_new);
416 l_plan_element_id := p_plan_element_new.quota_id;
417 l_note_msg := fnd_message.get;
418 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
419 /* jtf_notes_pub.create_note
420 (p_api_version => 1.0,
421 x_return_status => x_return_status,
422 x_msg_count => x_msg_count,
423 x_msg_data => x_msg_data,
424 p_source_object_id => l_plan_element_id,
425 p_source_object_code => 'CN_QUOTAS',
426 p_notes => l_note_msg,
427 p_notes_detail => l_note_msg,
428 p_note_type => 'CN_SYSGEN', -- for system generated
429 x_jtf_note_id => l_note_id -- returned
430 );*/
431 END IF;
432
433 -- CHECK IF PAID THRU PARTY WAS UPDATED
434 IF (p_plan_element_old.payee_assign_flag <> p_plan_element_new.payee_assign_flag) THEN
435 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_THIRDPARY_UPDATE');
436 select meaning into l_temp_old from cn_lookups
437 where lookup_code = NVL(p_plan_element_old.payee_assign_flag, 'N')
438 and lookup_type = 'YES_NO';
439 select meaning into l_temp_new from cn_lookups
440 where lookup_code = NVL(p_plan_element_new.payee_assign_flag, 'N')
441 and lookup_type = 'YES_NO';
442 fnd_message.set_token('PE_OLD_PAYEE', l_temp_old);
443 fnd_message.set_token('PE_NEW_PAYEE', l_temp_new);
444 l_plan_element_id := p_plan_element_new.quota_id;
445 l_note_msg := fnd_message.get;
446 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
447 /* jtf_notes_pub.create_note
448 (p_api_version => 1.0,
449 x_return_status => x_return_status,
450 x_msg_count => x_msg_count,
451 x_msg_data => x_msg_data,
452 p_source_object_id => l_plan_element_id,
453 p_source_object_code => 'CN_QUOTAS',
454 p_notes => l_note_msg,
455 p_notes_detail => l_note_msg,
456 p_note_type => 'CN_SYSGEN', -- for system generated
457 x_jtf_note_id => l_note_id -- returned
458 );*/
459 END IF;
460
461 -- CHECK IF LIABILITY A/C WAS UPDATED
462 IF (p_plan_element_old.liability_account_id <> p_plan_element_new.liability_account_id) THEN
463 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_LIA_UPDATE');
464 SELECT
465 DECODE(LA.CODE_COMBINATION_ID,NULL,NULL, LA.SEGMENT1||'-'||LA.SEGMENT2||'-'||LA.SEGMENT3||'-'||LA.SEGMENT4 ||'-'||LA.SEGMENT5)
466 INTO l_temp_old FROM GL_CODE_COMBINATIONS LA
467 WHERE LA.CODE_COMBINATION_ID = p_plan_element_old.liability_account_id;
468 SELECT
469 DECODE(LA.CODE_COMBINATION_ID,NULL,NULL, LA.SEGMENT1||'-'||LA.SEGMENT2||'-'||LA.SEGMENT3||'-'||LA.SEGMENT4 ||'-'||LA.SEGMENT5)
470 INTO l_temp_new FROM GL_CODE_COMBINATIONS LA
471 WHERE LA.CODE_COMBINATION_ID = p_plan_element_new.liability_account_id;
472 fnd_message.set_token('PE_OLD_LIA', l_temp_old);
473 fnd_message.set_token('PE_NEW_LIA', l_temp_new);
474 l_plan_element_id := p_plan_element_new.quota_id;
475 l_note_msg := fnd_message.get;
476 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
477 /* jtf_notes_pub.create_note
478 (p_api_version => 1.0,
479 x_return_status => x_return_status,
480 x_msg_count => x_msg_count,
481 x_msg_data => x_msg_data,
482 p_source_object_id => l_plan_element_id,
483 p_source_object_code => 'CN_QUOTAS',
484 p_notes => l_note_msg,
485 p_notes_detail => l_note_msg,
486 p_note_type => 'CN_SYSGEN', -- for system generated
487 x_jtf_note_id => l_note_id -- returned
488 );*/
489 END IF;
490
491 -- CHECK IF EXPENSE A/C WAS UPDATED
492 IF (p_plan_element_old.expense_account_id <> p_plan_element_new.expense_account_id) THEN
493 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_EXP_UPDATE');
494 SELECT
495 DECODE(LA.CODE_COMBINATION_ID,NULL,NULL, LA.SEGMENT1||'-'||LA.SEGMENT2||'-'||LA.SEGMENT3||'-'||LA.SEGMENT4 ||'-'||LA.SEGMENT5)
496 INTO l_temp_old FROM GL_CODE_COMBINATIONS LA
497 WHERE LA.CODE_COMBINATION_ID = p_plan_element_old.expense_account_id;
498 SELECT
499 DECODE(LA.CODE_COMBINATION_ID,NULL,NULL, LA.SEGMENT1||'-'||LA.SEGMENT2||'-'||LA.SEGMENT3||'-'||LA.SEGMENT4 ||'-'||LA.SEGMENT5)
500 INTO l_temp_new FROM GL_CODE_COMBINATIONS LA
501 WHERE LA.CODE_COMBINATION_ID = p_plan_element_new.expense_account_id;
502 fnd_message.set_token('PE_EXP_OLD', l_temp_old);
503 fnd_message.set_token('PE_EXP_NEW', l_temp_new);
504 l_plan_element_id := p_plan_element_new.quota_id;
505 l_note_msg := fnd_message.get;
506 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
507 /* jtf_notes_pub.create_note
508 (p_api_version => 1.0,
509 x_return_status => x_return_status,
510 x_msg_count => x_msg_count,
511 x_msg_data => x_msg_data,
512 p_source_object_id => l_plan_element_id,
513 p_source_object_code => 'CN_QUOTAS',
514 p_notes => l_note_msg,
515 p_notes_detail => l_note_msg,
516 p_note_type => 'CN_SYSGEN', -- for system generated
517 x_jtf_note_id => l_note_id -- returned
518 );*/
519 END IF;
520
521 -- CHECK IF CREDIT ROLLUP WAS UPDATED
522 IF (p_plan_element_old.indirect_credit_code <> p_plan_element_new.indirect_credit_code) THEN
523 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_CRROLL_UPDATE');
524 l_temp_old := cn_api.get_lkup_meaning(p_plan_element_old.indirect_credit_code, 'INDIRECT_CREDIT_TYPE');
525 l_temp_new := cn_api.get_lkup_meaning(p_plan_element_new.indirect_credit_code, 'INDIRECT_CREDIT_TYPE');
526 fnd_message.set_token('PE_CR_ROLL_OLD', l_temp_old);
527 fnd_message.set_token('PE_CR_ROLL_NEW', l_temp_new);
528 l_plan_element_id := p_plan_element_new.quota_id;
529 l_note_msg := fnd_message.get;
530 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
531 /* jtf_notes_pub.create_note
532 (p_api_version => 1.0,
533 x_return_status => x_return_status,
534 x_msg_count => x_msg_count,
535 x_msg_data => x_msg_data,
536 p_source_object_id => l_plan_element_id,
537 p_source_object_code => 'CN_QUOTAS',
538 p_notes => l_note_msg,
539 p_notes_detail => l_note_msg,
540 p_note_type => 'CN_SYSGEN', -- for system generated
541 x_jtf_note_id => l_note_id -- returned
542 );*/
543 END IF;
544 -- CHECK IF VARIABLE 1- TARGET IS CHANGED
545 IF (p_plan_element_old.target <> p_plan_element_new.target) THEN
546 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_VAR1_UPD');
547 fnd_message.set_token('OLD_VAL', p_plan_element_old.target);
548 fnd_message.set_token('NEW_VAL', p_plan_element_new.target);
549 l_plan_element_id := p_plan_element_new.quota_id;
550 l_note_msg := fnd_message.get;
551 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
552 /* jtf_notes_pub.create_note
553 (p_api_version => 1.0,
554 x_return_status => x_return_status,
555 x_msg_count => x_msg_count,
556 x_msg_data => x_msg_data,
557 p_source_object_id => l_plan_element_id,
558 p_source_object_code => 'CN_QUOTAS',
559 p_notes => l_note_msg,
560 p_notes_detail => l_note_msg,
561 p_note_type => 'CN_SYSGEN', -- for system generated
562 x_jtf_note_id => l_note_id -- returned
563 );*/
564 END IF;
565 -- CHECK IF VARIABLE 2- PAYMENT AMOUNT IS CHANGED
566 IF (p_plan_element_old.payment_amount <> p_plan_element_new.payment_amount) THEN
567 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_VAR2_UPD');
568 fnd_message.set_token('OLD_VAL', p_plan_element_old.payment_amount);
569 fnd_message.set_token('NEW_VAL', p_plan_element_new.payment_amount);
570 l_plan_element_id := p_plan_element_new.quota_id;
571 l_note_msg := fnd_message.get;
572 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
573 /* jtf_notes_pub.create_note
574 (p_api_version => 1.0,
575 x_return_status => x_return_status,
576 x_msg_count => x_msg_count,
577 x_msg_data => x_msg_data,
578 p_source_object_id => l_plan_element_id,
579 p_source_object_code => 'CN_QUOTAS',
580 p_notes => l_note_msg,
581 p_notes_detail => l_note_msg,
582 p_note_type => 'CN_SYSGEN', -- for system generated
583 x_jtf_note_id => l_note_id -- returned
584 );*/
585 END IF;
586 -- CHECK IF VARIABLE 3- PERFORMANCE GOAL IS CHANGED
587 IF (p_plan_element_old.performance_goal <> p_plan_element_new.performance_goal) THEN
588 fnd_message.set_name ('CN', 'CNR12_NOTE_PE_VAR3_UPD');
589 fnd_message.set_token('OLD_VAL', p_plan_element_old.performance_goal);
590 fnd_message.set_token('NEW_VAL', p_plan_element_new.performance_goal);
591 l_plan_element_id := p_plan_element_new.quota_id;
592 l_note_msg := fnd_message.get;
593 l_consolidated_note := l_consolidated_note || l_note_msg || fnd_global.local_chr(10);
594 /* jtf_notes_pub.create_note
595 (p_api_version => 1.0,
596 x_return_status => x_return_status,
597 x_msg_count => x_msg_count,
598 x_msg_data => x_msg_data,
599 p_source_object_id => l_plan_element_id,
600 p_source_object_code => 'CN_QUOTAS',
601 p_notes => l_note_msg,
602 p_notes_detail => l_note_msg,
603 p_note_type => 'CN_SYSGEN', -- for system generated
604 x_jtf_note_id => l_note_id -- returned
605 );*/
606 END IF;
607
608 IF LENGTH(l_consolidated_note) > 1 THEN
609 jtf_notes_pub.create_note (p_api_version => 1.0,
610 x_return_status => x_return_status,
611 x_msg_count => x_msg_count,
612 x_msg_data => x_msg_data,
613 p_source_object_id => l_plan_element_id,
614 p_source_object_code => 'CN_QUOTAS',
615 p_notes => l_consolidated_note,
616 p_notes_detail => l_consolidated_note,
617 p_note_type => 'CN_SYSGEN', -- for system generated
618 x_jtf_note_id => l_note_id -- returned
619 );
620 END IF;
621
622
623 END IF;
624
625 EXCEPTION
626 WHEN fnd_api.g_exc_error
627 THEN
628 x_return_status := fnd_api.g_ret_sts_error;
629 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
630 WHEN fnd_api.g_exc_unexpected_error
631 THEN
632 x_return_status := fnd_api.g_ret_sts_unexp_error;
633 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
634 WHEN OTHERS
635 THEN
636 x_return_status := fnd_api.g_ret_sts_unexp_error;
637 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
638 THEN
639 fnd_msg_pub.add_exc_msg (g_pkg_name, 'add_system_note');
640 END IF;
641 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
642
643 END add_system_note;
644
645 -------------------------------------------------------------------------+-+
646 --- Add message
647 -------------------------------------------------------------------------+-+
648 PROCEDURE set_message (
649 p_plan_name VARCHAR2,
650 p_pe_name VARCHAR2,
651 message_name VARCHAR2,
652 token_name VARCHAR2,
653 token_value VARCHAR2
654 )
655 IS
656 BEGIN
657 cn_message_pkg.set_message (appl_short_name => 'CN',
658 message_name => message_name,
659 token_name1 => 'QUOTA_NAME',
660 token_value1 => p_pe_name,
661 token_name2 => 'PLAN_NAME',
662 token_value2 => p_plan_name,
663 token_name3 => token_name,
664 token_value3 => token_value,
665 token_name4 => NULL,
666 token_value4 => NULL,
667 TRANSLATE => TRUE
668 );
669 fnd_msg_pub.ADD;
670 END set_message;
671
672 -- -------------------------------------------------------------------------+-+
673 --| Procedure: Insert_rate_quotas
674 --| Description: Rate_quotas is a local procedure to create the Default rate
675 --| Quota Assigns if the quota type is formula and the formula has the rates in
676 --| formula rate Assigns. Another important thing is if you pass the custom
677 --| Quota Rate it will ignore the default create. it will use the custom one you
678 --| Pass through your API.
679 --| Called From: Create_plan_Element and Update_Plan_Element
680 -- -------------------------------------------------------------------------+-+
681
682 PROCEDURE update_rate_quotas (
683 p_api_version IN NUMBER,
684 p_init_msg_list IN VARCHAR2,
685 p_commit IN VARCHAR2,
686 p_validation_level IN NUMBER,
687 x_return_status OUT NOCOPY VARCHAR2,
688 x_msg_count OUT NOCOPY NUMBER,
689 x_msg_data OUT NOCOPY VARCHAR2,
690 p_pe_rec IN plan_element_rec_type,
691 p_pe_rec_old IN plan_element_rec_type,
692 p_rt_quota_asgns_rec_tbl IN cn_plan_element_pub.rt_quota_asgns_rec_tbl_type := g_miss_rt_quota_asgns_rec_tbl,
693 p_quota_name IN VARCHAR2,
694 p_loading_status IN VARCHAR2,
695 x_loading_status OUT NOCOPY VARCHAR2
696 )
697 IS
698 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Rate_Quotas';
699 l_object_version_number NUMBER;
700 BEGIN
701 -- Record inserted successfully, check for rt_quota_assigns
702 -- Insert Rate Quota Assigs
703 -- first table count is 0
704
705 -- Set Status
706 x_return_status := fnd_api.g_ret_sts_success;
707 x_loading_status := p_loading_status;
708
709 IF p_pe_rec.quota_type_code <> 'NONE'
710 THEN
711 -- Check if the Count is O and the QUOTA TYPE IS FORMULA
712 -- Call the Chk_formula_rate_date Procedure to check all the Start
713 -- Date and End date of Rate QUota assigns falls user the Quota Start
714 -- and end Date then insert through a batch by calling the Table Handler
715 IF NVL (p_pe_rec_old.calc_formula_id, 0) <> NVL (p_pe_rec.calc_formula_id, 0)
716 THEN
717 -- Call the Table Handler to Delete the Old Period quotas
718 cn_rt_quota_asgns_pkg.DELETE_RECORD (x_quota_id => p_pe_rec_old.quota_id, x_calc_formula_id => NULL, x_rt_quota_asgn_id => NULL);
719 END IF;
720
721 IF p_rt_quota_asgns_rec_tbl.COUNT = 0
722 THEN
723 IF p_pe_rec.calc_formula_id IS NOT NULL AND NVL (p_pe_rec_old.calc_formula_id, 0) <> NVL (p_pe_rec.calc_formula_id, 0)
724 THEN
725 -- if called from public api then we need to insert defaults
726 -- else the defaults are inserted by direct rate tables assignment calls
727 --IF p_pe_rec.call_type = cn_plan_element_pvt.g_public_api
728 --THEN
729 cn_rt_quota_asgns_pkg.INSERT_RECORD (x_quota_id => p_pe_rec.quota_id, x_calc_formula_id => p_pe_rec.calc_formula_id);
730 --END IF;
731 END IF;
732 -- if the rt_table_count is > 0 and the quota type is FORMULA
733 ELSIF p_rt_quota_asgns_rec_tbl.COUNT > 0
734 THEN
735 -- call create_rt_quota_asgns_pvt package to validate and create
736 -- the rate Quota Assigns
737 /*cn_rt_quota_asgns_pvt.update_rt_quota_asgns (p_api_version => p_api_version,
738 p_init_msg_list => 'T',
739 p_commit => p_commit,
740 p_validation_level => p_validation_level,
741 x_return_status => x_return_status,
742 x_msg_count => x_msg_count,
743 x_msg_data => x_msg_data,
744 p_quota_name => p_quota_name,
745 p_rt_quota_asgns_rec_tbl => p_rt_quota_asgns_rec_tbl,
746 x_loading_status => x_loading_status
747 );*/
748 cn_rt_quota_asgns_pvt.update_rt_quota_asgns (p_api_version => p_api_version,
749 p_init_msg_list => 'T',
750 p_commit => p_commit,
751 p_validation_level => p_validation_level,
752 x_return_status => x_return_status,
753 x_msg_count => x_msg_count,
754 x_msg_data => x_msg_data,
755 p_quota_name => p_quota_name,
756 p_rt_quota_asgns_rec_tbl => p_rt_quota_asgns_rec_tbl,
757 x_loading_status => x_loading_status,
758 p_org_id => p_pe_rec.org_id,
759 x_object_version_number => l_object_version_number
760 );
761
762
763 IF (x_return_status <> fnd_api.g_ret_sts_success)
764 THEN
765 RAISE fnd_api.g_exc_error;
766 END IF;
767 END IF;
768 ELSIF p_rt_quota_asgns_rec_tbl.COUNT > 0
769 THEN
770 -- if table count is > 0 but the quota type code is NONE
771 -- then raise an error
772 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
773 THEN
774 fnd_message.set_name ('CN', 'CN_QUOTA_CANNOT_HAVE_RATES');
775 fnd_message.set_token ('PLAN_NAME', p_quota_name);
776 fnd_msg_pub.ADD;
777 END IF;
778
779 x_loading_status := 'QUOTA_CANNOT_HAVE_RATES';
780 RAISE fnd_api.g_exc_error;
781 END IF;
782 -- End of rate_quotas
783 EXCEPTION
784 WHEN fnd_api.g_exc_error
785 THEN
786 x_return_status := fnd_api.g_ret_sts_error;
787 WHEN fnd_api.g_exc_unexpected_error
788 THEN
789 x_return_status := fnd_api.g_ret_sts_unexp_error;
790 x_loading_status := 'UNEXPECTED_ERR';
791 WHEN OTHERS
792 THEN
793 x_return_status := fnd_api.g_ret_sts_unexp_error;
794 x_loading_status := 'UNEXPECTED_ERR';
795
796 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
797 THEN
798 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
799 END IF;
800 END update_rate_quotas;
801
802 -- -------------------------------------------------------------------------+-+
803 --| Procedure : update_exprs
804 --| Description : Syncs expressions that are using a particular plan element
805 --| if the name is changed
806 -- -------------------------------------------------------------------------+-+
807 PROCEDURE update_exprs (
808 p_quota_id NUMBER,
809 p_old_name VARCHAR2,
810 p_new_name VARCHAR2
811 )
812 IS
813 CURSOR get_exps
814 IS
815 SELECT calc_sql_exp_id,
816 DBMS_LOB.SUBSTR (piped_sql_select) sql_select,
817 DBMS_LOB.SUBSTR (piped_expression_disp) expr_disp
818 FROM cn_calc_sql_exps
819 WHERE '|' || DBMS_LOB.SUBSTR (piped_sql_select) LIKE '%|(' || p_quota_id || 'PE.%';
820
821 l_ss_start NUMBER;
822 l_ss_end NUMBER;
823 l_ed_start NUMBER;
824 l_ed_end NUMBER;
825 l_quota_id_len NUMBER := LENGTH ('' || p_quota_id);
826 l_quota_name_len NUMBER := LENGTH (p_old_name);
827 CONTINUE BOOLEAN;
828 l_ss_seg VARCHAR2 (4000);
829 l_ed_seg VARCHAR2 (80);
830 l_new_expr_disp VARCHAR2 (4000);
831 l_new_pexpr_disp VARCHAR2 (4000);
832 BEGIN
833 -- get all expressions using p_quota_id
834 FOR e IN get_exps
835 LOOP
836 l_ss_start := 1;
837 l_ed_start := 1;
838 l_new_expr_disp := NULL;
839 l_new_pexpr_disp := NULL;
840 CONTINUE := TRUE;
841
842 WHILE CONTINUE
843 LOOP
844 l_ss_end := INSTR (e.sql_select, '|', l_ss_start + 1);
845 l_ed_end := INSTR (e.expr_disp, '|', l_ed_start + 1);
846
847 IF l_ss_end = 0
848 THEN
849 CONTINUE := FALSE;
850 ELSE
851 l_ss_seg := SUBSTR (e.sql_select, l_ss_start, l_ss_end - l_ss_start);
852 l_ed_seg := SUBSTR (e.expr_disp, l_ed_start, l_ed_end - l_ed_start);
853
854 IF SUBSTR (l_ss_seg, 1, l_quota_id_len + 4) = '(' || p_quota_id || 'PE.'
855 AND SUBSTR (l_ed_seg, 1, l_quota_name_len + 1) = p_old_name || '.'
856 THEN
857 l_new_expr_disp := l_new_expr_disp || p_new_name || SUBSTR (l_ed_seg, l_quota_name_len + 1);
858 l_new_pexpr_disp := l_new_pexpr_disp || p_new_name || SUBSTR (l_ed_seg, l_quota_name_len + 1) || '|';
859 ELSE
860 l_new_expr_disp := l_new_expr_disp || l_ed_seg;
861 l_new_pexpr_disp := l_new_pexpr_disp || l_ed_seg || '|';
862 END IF;
863 END IF;
864
865 l_ss_start := l_ss_end + 1;
866 l_ed_start := l_ed_end + 1;
867 END LOOP;
868
869 -- update table
870 UPDATE cn_calc_sql_exps
871 SET expression_disp = l_new_expr_disp,
872 piped_expression_disp = l_new_pexpr_disp
873 WHERE calc_sql_exp_id = e.calc_sql_exp_id;
874 END LOOP;
875 END update_exprs;
876
877 --------------------------------------------------------------------
878
879 --------------------------------------------------------------------
880 PROCEDURE validate_types (
881 p_plan_element IN plan_element_rec_type,
882 x_return_status OUT NOCOPY VARCHAR2
883 )
884 IS
885 --l_api_name CONSTANT VARCHAR2 (30) := 'Valid_Lookup_Code';
886 l_tmp_exist NUMBER := 0;
887 l_temp VARCHAR2 (1000) := NULL;
888 l_plan_element CN_CHK_PLAN_ELEMENT_PKG.pe_rec_type;
889 l_lookup_type CN_LOOKUPS.lookup_type%TYPE;
890 BEGIN
891 -- set the Status
892 x_return_status := fnd_api.g_ret_sts_success;
893 l_lookup_type := '';
894 -- Check/Valid quota_type_code
895 IF p_plan_element.quota_type_code NOT IN ('EXTERNAL', 'FORMULA')
896 THEN
897 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
898 THEN
899 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
900 fnd_message.set_token ('OBJ_NAME', cn_chk_plan_element_pkg.g_element_type);
901 fnd_msg_pub.ADD;
902 END IF;
903
904 RAISE fnd_api.g_exc_error;
905 END IF;
906
907 -- Check/Valid Incentive Type
908 l_lookup_type := 'INCENTIVE_TYPE';
909 SELECT COUNT (*)
910 INTO l_tmp_exist
911 FROM cn_lookups
912 WHERE lookup_type = l_lookup_type
913 AND lookup_code = p_plan_element.incentive_type_code;
914
915 IF (l_tmp_exist = 0)
916 THEN
917 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
918 THEN
919 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
920 fnd_message.set_token ('OBJ_NAME', cn_chk_plan_element_pkg.g_incentive_type_code);
921 fnd_msg_pub.ADD;
922 END IF;
923
924 RAISE fnd_api.g_exc_error;
925 END IF;
926
927 l_lookup_type := 'PAYMENT_GROUP_CODE';
928 SELECT COUNT (*)
929 INTO l_tmp_exist
930 FROM cn_lookups
931 WHERE lookup_type = l_lookup_type
932 AND lookup_code = p_plan_element.payment_group_code;
933
934 IF (l_tmp_exist = 0)
935 THEN
936 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
937 THEN
938 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
939 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('PAYMENT_GROUP', 'PE_OBJECT_TYPE'));
940 fnd_msg_pub.ADD;
941 END IF;
942
943 RAISE fnd_api.g_exc_error;
944 END IF;
945
946 BEGIN
947 SELECT lookup_code
948 INTO l_temp
949 FROM cn_lookups
950 WHERE lookup_type = 'QUOTA_GROUP_CODE' AND lookup_code = p_plan_element.quota_group_code;
951 EXCEPTION
952 WHEN NO_DATA_FOUND
953 THEN
954 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
955 THEN
956 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
957 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('QUOTA_GROUP', 'PE_OBJECT_TYPE'));
958 fnd_msg_pub.ADD;
959 END IF;
960
961 RAISE fnd_api.g_exc_error;
962 WHEN OTHERS
963 THEN
964 RAISE fnd_api.g_exc_error;
965 END;
966
967 BEGIN
968 SELECT lookup_code
969 INTO l_temp
970 FROM cn_lookups
971 WHERE lookup_type = 'PLAN_ELEMENT_STATUS_TYPE' AND lookup_code = p_plan_element.quota_status;
972 EXCEPTION
973 WHEN NO_DATA_FOUND
974 THEN
975 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
976 THEN
977 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
978 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('STATUS', 'PE_OBJECT_TYPE'));
979 fnd_msg_pub.ADD;
980 END IF;
981
982 RAISE fnd_api.g_exc_error;
983 WHEN OTHERS
984 THEN
985 RAISE fnd_api.g_exc_error;
986 END;
987
988 -- Validate Indirect Credit
989 l_lookup_type := 'INDIRECT_CREDIT_TYPE';
990 SELECT COUNT (*)
991 INTO l_tmp_exist
992 FROM cn_lookups
993 WHERE lookup_type = l_lookup_type
994 AND lookup_code = p_plan_element.indirect_credit_code;
995
996 IF (l_tmp_exist = 0)
997 THEN
998 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
999 THEN
1000 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
1001 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('INDIRECT_CREDIT_TYPE', 'PE_OBJECT_TYPE'));
1002 fnd_msg_pub.ADD;
1003 END IF;
1004
1005 RAISE fnd_api.g_exc_error;
1006 END IF;
1007
1008 -- validate that the formula is okay
1009 IF (p_plan_element.calc_formula_id IS NOT NULL OR p_plan_element.quota_status <> g_new_status)
1010 THEN
1011 l_plan_element.quota_id := p_plan_element.quota_id;
1012 l_plan_element.name := p_plan_element.name;
1013 l_plan_element.description := p_plan_element.description;
1014 l_plan_element.start_date := p_plan_element.start_date;
1015 l_plan_element.end_date := p_plan_element.end_date;
1016 l_plan_element.quota_status := p_plan_element.quota_status;
1017 l_plan_element.object_version_number := p_plan_element.object_version_number;
1018 l_plan_element.org_id := p_plan_element.org_id;
1019 l_plan_element.indirect_credit := p_plan_element.indirect_credit_code;
1020 l_plan_element.quota_type_code := p_plan_element.quota_type_code;
1021 l_plan_element.target := p_plan_element.target;
1022 l_plan_element.payment_amount := p_plan_element.payment_amount;
1023 l_plan_element.performance_goal := p_plan_element.performance_goal;
1024 l_plan_element.incentive_type_code := p_plan_element.incentive_type_code;
1025 l_plan_element.credit_type_id := p_plan_element.credit_type_id;
1026 l_plan_element.interval_type_id := p_plan_element.calc_formula_id;
1027 l_plan_element.calc_formula_id := p_plan_element.calc_formula_id;
1028 l_plan_element.vesting_flag := p_plan_element.vesting_flag;
1029 l_plan_element.addup_from_rev_class_flag := p_plan_element.addup_from_rev_class_flag;
1030 l_plan_element.payee_assign_flag := p_plan_element.payee_assign_flag;
1031 l_plan_element.package_name := p_plan_element.package_name;
1032
1033 cn_chk_plan_element_pkg.validate_formula (l_plan_element);
1034 END IF;
1035
1036 --- the following are not validated on a new row
1037 IF (p_plan_element.credit_type_id IS NOT NULL)
1038 THEN
1039 SELECT COUNT (1)
1040 INTO l_tmp_exist
1041 FROM cn_credit_types
1042 WHERE credit_type_id = p_plan_element.credit_type_id AND org_id = p_plan_element.org_id;
1043
1044 IF (l_tmp_exist = 0)
1045 THEN
1046 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1047 THEN
1048 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
1049 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('CREDIT_TYPE', 'PE_OBJECT_TYPE'));
1050 fnd_msg_pub.ADD;
1051 END IF;
1052
1053 RAISE fnd_api.g_exc_error;
1054 END IF;
1055 END IF;
1056
1057 IF (p_plan_element.interval_type_id IS NOT NULL)
1058 THEN
1059 SELECT COUNT (*)
1060 INTO l_tmp_exist
1061 FROM cn_interval_types
1062 WHERE interval_type_id = p_plan_element.interval_type_id AND org_id = p_plan_element.org_id;
1063
1064 -- FROM:chk_pe_required Check interval_type_id can not be missing or NULL
1065 IF (l_tmp_exist = 0)
1066 THEN
1067 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1068 THEN
1069 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
1070 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('PERIOD_TYPE', 'PE_OBJECT_TYPE'));
1071 fnd_msg_pub.ADD;
1072 END IF;
1073
1074 RAISE fnd_api.g_exc_error;
1075 END IF;
1076 END IF;
1077
1078 -- verify that the columns below are valid when not new
1079 IF p_plan_element.quota_status = cn_plan_element_pvt.g_new_status
1080 THEN
1081 RETURN;
1082 END IF;
1083
1084 IF (p_plan_element.credit_type_id IS NULL)
1085 THEN
1086 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1087 THEN
1088 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
1089 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('CREDIT_TYPE', 'PE_OBJECT_TYPE'));
1090 fnd_msg_pub.ADD;
1091 END IF;
1092
1093 RAISE fnd_api.g_exc_error;
1094 END IF;
1095
1096 -- FROM:chk_pe_required Check interval_type_id can not be missing or NULL
1097 IF (p_plan_element.interval_type_id IS NULL)
1098 THEN
1099 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1100 THEN
1101 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
1102 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('PERIOD_TYPE', 'PE_OBJECT_TYPE'));
1103 fnd_msg_pub.ADD;
1104 END IF;
1105
1106 RAISE fnd_api.g_exc_error;
1107 END IF;
1108
1109 IF (p_plan_element.calc_formula_id IS NULL AND p_plan_element.quota_type_code = 'FORMULA')
1110 THEN
1111 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1112 THEN
1113 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
1114 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('FORMULA', 'QUOTA_TYPE'));
1115 fnd_msg_pub.ADD;
1116 END IF;
1117
1118 RAISE fnd_api.g_exc_error;
1119 END IF;
1120 END validate_types;
1121
1122 -- clku
1123 -- procedure that validates the Liability account ID and Expense Account ID.
1124 -- clku
1125 -- procedure that validates the Liability account ID and Expense Account ID.
1126 PROCEDURE check_ccids (
1127 p_account_type IN VARCHAR2,
1128 p_account_input IN VARCHAR2,
1129 x_ccid OUT NOCOPY NUMBER
1130 )
1131 IS
1132 kff fnd_flex_key_api.flexfield_type;
1133 str fnd_flex_key_api.structure_type;
1134 seg fnd_flex_key_api.segment_type;
1135 seg_list fnd_flex_key_api.segment_list;
1136 j NUMBER;
1137 i NUMBER;
1138 nsegs NUMBER;
1139 l_count NUMBER;
1140 l_ccid NUMBER;
1141 segment_descr VARCHAR2 (2000);
1142 sql_stmt VARCHAR2 (2000);
1143 where_stmt VARCHAR2 (2000);
1144 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
1145 ccid NUMBER;
1146 ccid_value VARCHAR2 (2000);
1147 l_account_type gl_code_combinations.account_type%TYPE;
1148
1149 TYPE curtype IS REF CURSOR;
1150
1151 ccid_cur curtype;
1152 BEGIN
1153 SELECT chart_of_accounts_id
1154 INTO l_chart_of_accounts_id
1155 FROM gl_sets_of_books gsb,
1156 cn_repositories cr
1157 WHERE cr.set_of_books_id = gsb.set_of_books_id;
1158
1159 fnd_flex_key_api.set_session_mode ('customer_data');
1160 kff := fnd_flex_key_api.find_flexfield ('SQLGL', 'GL#');
1161 str := fnd_flex_key_api.find_structure (kff, l_chart_of_accounts_id);
1162 fnd_flex_key_api.get_segments (kff, str, TRUE, nsegs, seg_list);
1163 --
1164 -- The segments in the seg_list array are sorted in display order.
1165 -- i.e. sorted by segment number.
1166 --
1167 sql_stmt := 'SELECT COUNT(*)';
1168 where_stmt := ' ';
1169
1170 FOR i IN 1 .. nsegs
1171 LOOP
1172 seg := fnd_flex_key_api.find_segment (kff, str, seg_list (i));
1173 segment_descr := segment_descr || seg.segment_name;
1174 where_stmt := where_stmt || seg.column_name;
1175
1176 IF i <> nsegs
1177 THEN
1178 segment_descr := segment_descr || str.segment_separator;
1179 where_stmt := where_stmt || '||''' || str.segment_separator || '''||';
1180 END IF;
1181 END LOOP;
1182
1183 sql_stmt :=
1184 sql_stmt
1185 || ' FROM gl_code_combinations '
1186 ||
1187 -- Modified By Hithanki for Bug Fix : 2938387 05-May-2003
1188 -- ' WHERE chart_of_accounts_id = '||l_chart_of_accounts_id||
1189 ' WHERE chart_of_accounts_id = :1 '
1190 || ' AND enabled_flag = ''Y'''
1191 -- Modified By Hithanki for Bug Fhix : 2938387 05-May-2003
1192 -- || ' AND ' || where_stmt || ' = ''' || p_account_input || '''';
1193 || ' AND '
1194 || where_stmt
1195 || ' = :2 ';
1196
1197 -- OPEN ccid_cur FOR sql_stmt;
1198 -- Modified By Hithanki for Bug Fix : 2938387 05-May-2003
1199 OPEN ccid_cur
1200 FOR sql_stmt USING l_chart_of_accounts_id, p_account_input;
1201
1202 FETCH ccid_cur
1203 INTO l_count;
1204
1205 CLOSE ccid_cur;
1206
1207 IF (l_count = 0 AND p_account_type = 'L')
1208 THEN
1209 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1210 THEN
1211 -- Need to define message 'CN_E_CANNOT_REF_ITSEF' in SEED115
1212 fnd_message.set_name ('CN', 'CN_INV_ACC_NO');
1213 fnd_msg_pub.ADD;
1214 END IF;
1215
1216 RAISE fnd_api.g_exc_error;
1217 END IF;
1218
1219 IF (l_count = 0 AND p_account_type = 'E')
1220 THEN
1221 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1222 THEN
1223 -- Need to define message 'CN_E_CANNOT_REF_ITSEF' in SEED115
1224 fnd_message.set_name ('CN', 'CN_INV_ACC_NO');
1225 fnd_msg_pub.ADD;
1226 END IF;
1227
1228 RAISE fnd_api.g_exc_error;
1229 END IF;
1230
1231 sql_stmt := 'SELECT code_combination_id';
1232 where_stmt := ' ';
1233
1234 FOR i IN 1 .. nsegs
1235 LOOP
1236 seg := fnd_flex_key_api.find_segment (kff, str, seg_list (i));
1237 segment_descr := segment_descr || seg.segment_name;
1238 where_stmt := where_stmt || seg.column_name;
1239
1240 IF i <> nsegs
1241 THEN
1242 segment_descr := segment_descr || str.segment_separator;
1243 where_stmt := where_stmt || '||''' || str.segment_separator || '''||';
1244 END IF;
1245 END LOOP;
1246
1247 sql_stmt :=
1248 sql_stmt
1249 || ' FROM gl_code_combinations '
1250 ||
1251 -- Modified By Hithanki for Bug Fix : 2938387 05-May-2003
1252 -- ' WHERE chart_of_accounts_id = '||l_chart_of_accounts_id||
1253 ' WHERE chart_of_accounts_id = :1 '
1254 || ' AND enabled_flag = ''Y'''
1255 -- Modified By Hithanki for Bug Fix : 2938387 05-May-2003
1256 -- || ' AND ' || where_stmt || ' = ''' || p_account_input || '''';
1257 || ' AND '
1258 || where_stmt
1259 || ' = :2 ';
1260
1261 -- OPEN ccid_cur FOR sql_stmt;
1262 -- Modified By Hithanki for Bug Fix : 2938387 05-May-2003
1263 OPEN ccid_cur
1264 FOR sql_stmt USING l_chart_of_accounts_id, p_account_input;
1265
1266 FETCH ccid_cur
1267 INTO l_ccid;
1268
1269 CLOSE ccid_cur;
1270
1271 x_ccid := l_ccid;
1272 END check_ccids;
1273
1274 /*
1275 * Get the account id given the code combination
1276 */
1277 PROCEDURE validate_and_update_ccids (
1278 p_plan_element IN OUT NOCOPY plan_element_rec_type
1279 )
1280 IS
1281 l_id NUMBER;
1282 BEGIN
1283 --clku
1284 -- validate the code combination and get the ccid only if the ccid is NULL
1285 -- For Liability Account
1286 IF (p_plan_element.liability_account_id IS NULL)
1287 THEN
1288 IF (p_plan_element.liability_account_cc IS NOT NULL)
1289 THEN
1290 check_ccids (p_account_type => 'L', p_account_input => p_plan_element.liability_account_cc,
1291 x_ccid => p_plan_element.liability_account_id);
1292 END IF;
1293 ELSE
1294 BEGIN
1295 SELECT code_combination_id
1296 INTO l_id
1297 FROM gl_code_combinations
1298 WHERE code_combination_id = p_plan_element.liability_account_id AND account_type = 'L';
1299 EXCEPTION
1300 WHEN NO_DATA_FOUND
1301 THEN
1302 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1303 THEN
1304 fnd_message.set_name ('CN', 'CN_IMP_INVLD_LIABLTY_CODE');
1305 fnd_msg_pub.ADD;
1306 END IF;
1307
1308 RAISE fnd_api.g_exc_error;
1309 END;
1310 END IF;
1311
1312 --clku
1313 -- validate the code combination and get the ccid only if the ccid is NULL
1314 -- For Expense Account
1315 IF (p_plan_element.expense_account_id IS NULL)
1316 THEN
1317 IF (p_plan_element.expense_account_cc IS NOT NULL)
1318 THEN
1319 check_ccids (p_account_type => 'E', p_account_input => p_plan_element.expense_account_cc, x_ccid => p_plan_element.expense_account_id);
1320 END IF;
1321 ELSE
1322 BEGIN
1323 SELECT code_combination_id
1324 INTO l_id
1325 FROM gl_code_combinations
1326 WHERE code_combination_id = p_plan_element.expense_account_id AND account_type = 'E';
1327 EXCEPTION
1328 WHEN NO_DATA_FOUND
1329 THEN
1330 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1331 THEN
1332 fnd_message.set_name ('CN', 'CN_IMP_INVLD_EXPENS_CODE');
1333 fnd_msg_pub.ADD;
1334 END IF;
1335
1336 RAISE fnd_api.g_exc_error;
1337 END;
1338 END IF;
1339 END validate_and_update_ccids;
1340
1341 -- Start of comments
1342 -- API name : Create_Plan_Element
1343 -- Type : Private.
1344 -- Function :
1345 -- Pre-reqs : None.
1346 -- Parameters :
1347 -- IN : p_api_version IN NUMBER Required
1348 -- p_init_msg_list IN VARCHAR2 Optional
1349 -- Default = FND_API.G_FALSE
1350 -- p_commit IN VARCHAR2 Optional
1351 -- Default = FND_API.G_FALSE
1352 -- p_validation_level IN NUMBER Optional
1353 -- Default = FND_API.G_VALID_LEVEL_FULL
1354 -- p_plan_element IN plan_element_rec_type
1355 -- OUT : x_return_status OUT VARCHAR2(1)
1356 -- x_msg_count OUT NUMBER
1357 -- x_msg_data OUT VARCHAR2(2000)
1358 -- x_plan_element_id OUT NUMBER
1359 -- Version : Current version 1.0
1360 -- Notes : Note text
1361 --
1362 -- End of comments
1363 PROCEDURE create_plan_element (
1364 p_api_version IN NUMBER,
1365 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1366 p_commit IN VARCHAR2 := fnd_api.g_false,
1367 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1368 p_plan_element IN OUT NOCOPY plan_element_rec_type,
1369 x_return_status OUT NOCOPY VARCHAR2,
1370 x_msg_count OUT NOCOPY NUMBER,
1371 x_msg_data OUT NOCOPY VARCHAR2
1372 )
1373 IS
1374 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Plan_Element';
1375 l_api_version CONSTANT NUMBER := 1.0;
1376 l_temp_count NUMBER;
1377 l_loading_status VARCHAR2 (50);
1378 l_credit_type_name cn_credit_types.NAME%TYPE := NULL;
1379 l_calc_formula_name cn_calc_formulas.NAME%TYPE := NULL;
1380 l_interval_type_name cn_interval_types.NAME%TYPE := NULL;
1381 l_formula_type cn_calc_formulas.formula_type%TYPE := NULL;
1382 g_last_update_date DATE := SYSDATE;
1383 g_last_updated_by NUMBER := fnd_global.user_id;
1384 g_creation_date DATE := SYSDATE;
1385 g_created_by NUMBER := fnd_global.user_id;
1386 g_last_update_login NUMBER := fnd_global.login_id;
1387 g_remove_this VARCHAR2 (1) := '#';
1388 BEGIN
1389 -- Standard Start of API savepoint
1390 SAVEPOINT create_plan_element;
1391
1392 -- Standard call to check for call compatibility.
1393 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1394 THEN
1395 RAISE fnd_api.g_exc_unexpected_error;
1396 END IF;
1397
1398 -- Initialize message list if p_init_msg_list is set to TRUE.
1399 IF fnd_api.to_boolean (p_init_msg_list)
1400 THEN
1401 fnd_msg_pub.initialize;
1402 END IF;
1403
1404 -- Initialize API return status to success
1405 x_return_status := fnd_api.g_ret_sts_success;
1406
1407 -- API body
1408 IF p_plan_element.quota_id IS NULL
1409 THEN
1410 SELECT cn_quotas_s.NEXTVAL
1411 INTO p_plan_element.quota_id
1412 FROM DUAL;
1413 END IF;
1414
1415 -- validate plan element
1416 validate_plan_element (p_api_version => p_api_version,
1417 p_plan_element => p_plan_element,
1418 p_action => 'CREATE',
1419 x_return_status => x_return_status,
1420 x_msg_count => x_msg_count,
1421 x_msg_data => x_msg_data
1422 );
1423
1424 -- raise an error if validate was not successful
1425 IF (x_return_status <> fnd_api.g_ret_sts_success)
1426 THEN
1427 RAISE fnd_api.g_exc_error;
1428 END IF;
1429
1430 -- update the account ids the UIs give a concatenated code
1431 validate_and_update_ccids (p_plan_element);
1432 -- update the table
1433 cn_quotas_pkg.begin_record (x_operation => 'INSERT',
1434 x_rowid => g_remove_this,
1435 x_quota_id => p_plan_element.quota_id,
1436 x_object_version_number => p_plan_element.object_version_number,
1437 x_name => p_plan_element.NAME,
1438 x_target => NVL (p_plan_element.target, 0),
1439 x_quota_type_code => p_plan_element.quota_type_code,
1440 x_usage_code => NULL,
1441 x_payment_amount => NVL (p_plan_element.payment_amount, 0),
1442 x_description => p_plan_element.description,
1443 x_start_date => p_plan_element.start_date,
1444 x_end_date => p_plan_element.end_date,
1445 x_quota_status => p_plan_element.quota_status,
1446 x_calc_formula_id => p_plan_element.calc_formula_id,
1447 x_incentive_type_code => p_plan_element.incentive_type_code,
1448 x_credit_type_id => p_plan_element.credit_type_id,
1449 x_rt_sched_custom_flag => NULL,
1450 x_package_name => p_plan_element.package_name,
1451 x_performance_goal => NVL (p_plan_element.performance_goal, 0),
1452 x_interval_type_id => p_plan_element.interval_type_id,
1453 x_payee_assign_flag => p_plan_element.payee_assign_flag,
1454 x_vesting_flag => p_plan_element.vesting_flag,
1455 x_expense_account_id => p_plan_element.expense_account_id,
1456 x_liability_account_id => p_plan_element.liability_account_id,
1457 x_quota_group_code => p_plan_element.quota_group_code,
1458 --clku PAYMENT ENHANCEMENT
1459 x_payment_group_code => p_plan_element.payment_group_code,
1460 x_quota_unspecified => NULL,
1461 x_last_update_date => g_last_update_date,
1462 x_last_updated_by => g_last_updated_by,
1463 x_creation_date => g_creation_date,
1464 x_created_by => g_created_by,
1465 x_last_update_login => g_last_update_login,
1466 x_program_type => NULL,
1467 --x_status_code => NULL,
1468 x_period_type_code => NULL,
1469 x_start_num => NULL,
1470 x_end_num => NULL,
1471 x_addup_from_rev_class_flag => p_plan_element.addup_from_rev_class_flag,
1472 x_attribute_category => p_plan_element.attribute_category,
1473 x_attribute1 => p_plan_element.attribute1,
1474 x_attribute2 => p_plan_element.attribute2,
1475 x_attribute3 => p_plan_element.attribute3,
1476 x_attribute4 => p_plan_element.attribute4,
1477 x_attribute5 => p_plan_element.attribute5,
1478 x_attribute6 => p_plan_element.attribute6,
1479 x_attribute7 => p_plan_element.attribute7,
1480 x_attribute8 => p_plan_element.attribute8,
1481 x_attribute9 => p_plan_element.attribute9,
1482 x_attribute10 => p_plan_element.attribute10,
1483 x_attribute11 => p_plan_element.attribute11,
1484 x_attribute12 => p_plan_element.attribute12,
1485 x_attribute13 => p_plan_element.attribute13,
1486 x_attribute14 => p_plan_element.attribute14,
1487 x_attribute15 => p_plan_element.attribute15,
1488 x_indirect_credit => p_plan_element.indirect_credit_code,
1489 x_org_id => p_plan_element.org_id,
1490 x_salesrep_end_flag => p_plan_element.sreps_enddated_flag
1491 );
1492 -- Record inserted successfully
1493 -- insert the periods given that we always have a start and end date
1494 cn_period_quotas_pkg.distribute_target (p_plan_element.quota_id);
1495
1496 IF p_plan_element.quota_status <> cn_plan_element_pvt.g_new_status
1497 THEN
1498 -- Call the Rate_quotas Procedure to create rate quota Assigns
1499 cn_rt_quota_asgns_pkg.INSERT_RECORD (x_quota_id => p_plan_element.quota_id, x_calc_formula_id => p_plan_element.calc_formula_id);
1500 END IF;
1501
1502 -- Raise an Error if the Status is Failedx
1503 IF (x_return_status <> fnd_api.g_ret_sts_success)
1504 THEN
1505 RAISE fnd_api.g_exc_error;
1506 END IF;
1507 -- Calling proc to add system note for create
1508 add_system_note(
1509 p_plan_element,
1510 p_plan_element,
1511 'create',
1512 x_return_status,
1513 x_msg_count,
1514 x_msg_data
1515 );
1516 IF (x_return_status <> fnd_api.g_ret_sts_success)
1517 THEN
1518 RAISE fnd_api.g_exc_error;
1519 END IF;
1520
1521 -- End of API body.
1522 -- Standard check of p_commit.
1523 IF fnd_api.to_boolean (p_commit)
1524 THEN
1525 COMMIT WORK;
1526 END IF;
1527
1528 -- Standard call to get message count and if count is 1, get message info.
1529 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1530 EXCEPTION
1531 WHEN fnd_api.g_exc_error
1532 THEN
1533 ROLLBACK TO create_plan_element;
1534 x_return_status := fnd_api.g_ret_sts_error;
1535 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1536 WHEN fnd_api.g_exc_unexpected_error
1537 THEN
1538 ROLLBACK TO create_plan_element;
1539 x_return_status := fnd_api.g_ret_sts_unexp_error;
1540 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1541 WHEN OTHERS
1542 THEN
1543 ROLLBACK TO create_plan_element;
1544 x_return_status := fnd_api.g_ret_sts_unexp_error;
1545
1546 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1547 THEN
1548 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1549 END IF;
1550
1551 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1552 END create_plan_element;
1553
1554 -- Start of comments
1555 -- API name : Update_Plan_Element
1556 -- Type : Private.
1557 -- Function :
1558 -- Pre-reqs : None.
1559 -- Parameters :
1560 -- IN : p_api_version IN NUMBER Required
1561 -- p_init_msg_list IN VARCHAR2 Optional
1562 -- Default = FND_API.G_FALSE
1563 -- p_commit IN VARCHAR2 Optional
1564 -- Default = FND_API.G_FALSE
1565 -- p_validation_level IN NUMBER Optional
1566 -- Default = FND_API.G_VALID_LEVEL_FULL
1567 -- p_plan_element IN plan_element_rec_type
1568 -- OUT : x_return_status OUT VARCHAR2(1)
1569 -- x_msg_count OUT NUMBER
1570 -- x_msg_data OUT VARCHAR2(2000)
1571 -- Version : Current version 1.0
1572 -- Notes : Note text
1573 --
1574 -- End of comments
1575 PROCEDURE update_plan_element (
1576 p_api_version IN NUMBER,
1577 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1578 p_commit IN VARCHAR2 := fnd_api.g_false,
1579 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1580 p_plan_element IN OUT NOCOPY plan_element_rec_type,
1581 x_return_status OUT NOCOPY VARCHAR2,
1582 x_msg_count OUT NOCOPY NUMBER,
1583 x_msg_data OUT NOCOPY VARCHAR2
1584 )
1585 IS
1586 CURSOR l_old_plan_element_cr (
1587 qid NUMBER
1588 )
1589 IS
1590 SELECT pe.quota_id,
1591 pe.NAME,
1592 pe.description,
1593 pe.quota_type_code,
1594 pe.target,
1595 pe.payment_amount,
1596 pe.performance_goal,
1597 pe.incentive_type_code,
1598 pe.start_date,
1599 pe.end_date,
1600 pe.credit_type_id,
1601 pe.interval_type_id,
1602 pe.calc_formula_id,
1603 pe.liability_account_id,
1604 pe.expense_account_id,
1605 'liability_account_cc',
1606 'expense_account_cc',
1607 pe.vesting_flag,
1608 pe.quota_group_code,
1609 pe.payment_group_code,
1610 pe.attribute_category,
1611 pe.attribute1,
1612 pe.attribute2,
1613 pe.attribute3,
1614 pe.attribute4,
1615 pe.attribute5,
1616 pe.attribute6,
1617 pe.attribute7,
1618 pe.attribute8,
1619 pe.attribute9,
1620 pe.attribute10,
1621 pe.attribute11,
1622 pe.attribute12,
1623 pe.attribute13,
1624 pe.attribute14,
1625 pe.attribute15,
1626 pe.addup_from_rev_class_flag,
1627 pe.payee_assign_flag,
1628 pe.package_name,
1629 pe.object_version_number,
1630 pe.org_id,
1631 pe.indirect_credit,
1632 pe.quota_status,
1633 pe.salesreps_enddated_flag,
1634 NULL
1635 FROM cn_quotas_v pe
1636 WHERE pe.quota_id = qid;
1637
1638 CURSOR get_number_dim (
1639 l_quota_id NUMBER
1640 )
1641 IS
1642 SELECT ccf.number_dim
1643 FROM cn_quotas_v cq,
1644 cn_calc_formulas ccf
1645 WHERE cq.quota_id = l_quota_id AND cq.calc_formula_id = ccf.calc_formula_id;
1646
1647 CURSOR c_srp_period_quota_csr (
1648 pe_quota_id cn_quotas.quota_id%TYPE
1649 )
1650 IS
1651 SELECT srp_period_quota_id
1652 FROM cn_srp_period_quotas
1653 WHERE quota_id = pe_quota_id;
1654
1655 g_last_update_date DATE := SYSDATE;
1656 g_last_updated_by NUMBER := fnd_global.user_id;
1657 g_creation_date DATE := SYSDATE;
1658 g_created_by NUMBER := fnd_global.user_id;
1659 g_last_update_login NUMBER := fnd_global.login_id;
1660 g_row_id NUMBER;
1661 l_old_plan_element plan_element_rec_type;
1662 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Plan_Element';
1663 l_api_version CONSTANT NUMBER := 1.0;
1664 l_ccid NUMBER (15);
1665 l_la_ccid NUMBER (15);
1666 l_ea_ccid NUMBER (15);
1667 l_payeechk NUMBER;
1668 l_credit_type_name cn_credit_types.NAME%TYPE := NULL;
1669 l_calc_formula_name cn_calc_formulas.NAME%TYPE := NULL;
1670 l_interval_type_name cn_interval_types.NAME%TYPE := NULL;
1671 l_formula_type cn_calc_formulas.formula_type%TYPE := NULL;
1672 l_number_dim_old NUMBER;
1673 l_number_dim_new NUMBER;
1674 l_number_dim NUMBER;
1675 s_tot_target NUMBER;
1676 s_tot_payment_amount NUMBER;
1677 s_tot_performance_goal NUMBER;
1678 l_loading_status VARCHAR2 (100);
1679 x_loading_status VARCHAR (100);
1680 BEGIN
1681 -- Standard Start of API savepoint
1682 SAVEPOINT update_plan_element;
1683
1684 -- Standard call to check for call compatibility.
1685 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1686 THEN
1687 RAISE fnd_api.g_exc_unexpected_error;
1688 END IF;
1689
1690 -- Initialize message list if p_init_msg_list is set to TRUE.
1691 IF fnd_api.to_boolean (p_init_msg_list)
1692 THEN
1693 fnd_msg_pub.initialize;
1694 END IF;
1695
1696 -- Initialize API return status to success
1697 x_return_status := fnd_api.g_ret_sts_success;
1698 l_old_plan_element := get_plan_element (p_plan_element.quota_id);
1699 -- validate the plan element
1700 validate_plan_element (p_api_version => p_api_version,
1701 p_plan_element => p_plan_element,
1702 p_old_plan_element => l_old_plan_element,
1703 p_action => 'UPDATE',
1704 x_return_status => x_return_status,
1705 x_msg_count => x_msg_count,
1706 x_msg_data => x_msg_data
1707 );
1708
1709 -- in case of error, raise exception
1710 IF (x_return_status <> fnd_api.g_ret_sts_success)
1711 THEN
1712 RAISE fnd_api.g_exc_error;
1713 END IF;
1714
1715 -- Check the Plan Element start date and end date fall with in the rt_formula_asgns
1716 IF p_plan_element.calc_formula_id IS NOT NULL
1717 THEN
1718 IF p_plan_element.calc_formula_id = l_old_plan_element.calc_formula_id
1719 THEN
1720 cn_chk_plan_element_pkg.chk_rate_quota_date (x_return_status => x_return_status,
1721 p_start_date => p_plan_element.start_date,
1722 p_end_date => p_plan_element.end_date,
1723 p_quota_name => p_plan_element.NAME,
1724 p_quota_id => p_plan_element.quota_id,
1725 p_loading_status => x_loading_status,
1726 x_loading_status => l_loading_status
1727 );
1728 x_loading_status := l_loading_status;
1729 END IF;
1730
1731 -- error if the status is not success
1732 IF (x_return_status <> fnd_api.g_ret_sts_success)
1733 THEN
1734 RAISE fnd_api.g_exc_error;
1735 END IF;
1736 END IF;
1737
1738
1739 -- 4. Select the Target, Fixed Amount and Goal
1740 IF p_plan_element.addup_from_rev_class_flag = 'Y'
1741 THEN
1742 SELECT SUM (target)
1743 INTO p_plan_element.target
1744 FROM cn_quota_rules
1745 WHERE quota_id = p_plan_element.quota_id;
1746
1747 SELECT SUM (payment_amount)
1748 INTO p_plan_element.payment_amount
1749 FROM cn_quota_rules
1750 WHERE quota_id = p_plan_element.quota_id;
1751
1752 SELECT SUM (performance_goal)
1753 INTO p_plan_element.performance_goal
1754 FROM cn_quota_rules
1755 WHERE quota_id = p_plan_element.quota_id;
1756 END IF;
1757
1758 -- update the accounts data
1759 validate_and_update_ccids (p_plan_element);
1760
1761 -- call the table handler
1762 cn_quotas_pkg.begin_record (x_operation => 'UPDATE',
1763 x_rowid => g_row_id,
1764 x_quota_id => p_plan_element.quota_id,
1765 x_object_version_number => p_plan_element.object_version_number,
1766 x_name => p_plan_element.NAME,
1767 x_target => NVL (p_plan_element.target, 0),
1768 x_quota_type_code => p_plan_element.quota_type_code,
1769 x_usage_code => NULL,
1770 x_payment_amount => NVL (p_plan_element.payment_amount, 0),
1771 x_description => p_plan_element.description,
1772 x_start_date => p_plan_element.start_date,
1773 x_end_date => p_plan_element.end_date,
1774 x_quota_status => p_plan_element.quota_status,
1775 x_calc_formula_id => p_plan_element.calc_formula_id,
1776 x_incentive_type_code => p_plan_element.incentive_type_code,
1777 x_credit_type_id => p_plan_element.credit_type_id,
1778 x_rt_sched_custom_flag => NULL,
1779 x_package_name => p_plan_element.package_name,
1780 x_performance_goal => NVL (p_plan_element.performance_goal, 0),
1781 x_interval_type_id => p_plan_element.interval_type_id,
1782 x_payee_assign_flag => p_plan_element.payee_assign_flag,
1783 x_vesting_flag => p_plan_element.vesting_flag,
1784 x_expense_account_id => p_plan_element.expense_account_id,
1785 x_liability_account_id => p_plan_element.liability_account_id,
1786 x_quota_group_code => p_plan_element.quota_group_code,
1787 x_payment_group_code => p_plan_element.payment_group_code,
1788 x_quota_unspecified => NULL,
1789 x_last_update_date => g_last_update_date,
1790 x_last_updated_by => g_last_updated_by,
1791 x_creation_date => NULL,
1792 x_created_by => NULL,
1793 x_last_update_login => g_last_update_login,
1794 x_program_type => NULL,
1795 --x_status_code => p_plan_element.quota_status,
1796 x_period_type_code => NULL,
1797 x_start_num => NULL,
1798 x_end_num => NULL,
1799 x_addup_from_rev_class_flag => p_plan_element.addup_from_rev_class_flag,
1800 x_attribute_category => p_plan_element.attribute_category,
1801 x_attribute1 => p_plan_element.attribute1,
1802 x_attribute2 => p_plan_element.attribute2,
1803 x_attribute3 => p_plan_element.attribute3,
1804 x_attribute4 => p_plan_element.attribute4,
1805 x_attribute5 => p_plan_element.attribute5,
1806 x_attribute6 => p_plan_element.attribute6,
1807 x_attribute7 => p_plan_element.attribute7,
1808 x_attribute8 => p_plan_element.attribute8,
1809 x_attribute9 => p_plan_element.attribute9,
1810 x_attribute10 => p_plan_element.attribute10,
1811 x_attribute11 => p_plan_element.attribute11,
1812 x_attribute12 => p_plan_element.attribute12,
1813 x_attribute13 => p_plan_element.attribute13,
1814 x_attribute14 => p_plan_element.attribute14,
1815 x_attribute15 => p_plan_element.attribute15,
1816 x_indirect_credit => p_plan_element.indirect_credit_code,
1817 x_org_id => p_plan_element.org_id,
1818 x_salesrep_end_flag =>p_plan_element.sreps_enddated_flag
1819 );
1820
1821 -- update expressions using this plan element
1822 IF (p_plan_element.NAME <> l_old_plan_element.NAME)
1823 THEN
1824 update_exprs (p_plan_element.quota_id, l_old_plan_element.NAME, p_plan_element.NAME);
1825 END IF;
1826 -- Commented out for Bug 4722521------------------------------------------------
1827 -- delete period quotas and distribute them again
1828 IF l_old_plan_element.start_date <> p_plan_element.start_date
1829 OR NVL (p_plan_element.end_date, fnd_api.g_miss_date)
1830 <> NVL (l_old_plan_element.end_date, fnd_api.g_miss_date)
1831 THEN
1832 -- Call the Table Handler to Delete the Old Period quotas
1833 -- cn_period_quotas_pkg.DELETE_RECORD (p_plan_element.quota_id);
1834 cn_period_quotas_pkg.distribute_target (p_plan_element.quota_id);
1835 END IF;
1836 -- Commented out for Bug 4722521------------------------------------------------
1837 -- check if we need to update the cn_srp_period_quotas ext table. If yes, update the table
1838 -- if the new assignement is external package, we do not do anything
1839 IF p_plan_element.quota_type_code <> 'EXTERNAL'
1840 THEN
1841 -- if the old assignement is external package, we wipe out the ext table and re-insert the record
1842 IF l_old_plan_element.quota_type_code = 'EXTERNAL'
1843 THEN
1844 OPEN get_number_dim (l_old_plan_element.quota_id);
1845
1846 FETCH get_number_dim
1847 INTO l_number_dim;
1848
1849 CLOSE get_number_dim;
1850
1851 IF l_number_dim > 1
1852 THEN
1853 FOR l_srp_period_quota_id IN c_srp_period_quota_csr (l_old_plan_element.quota_id)
1854 LOOP
1855 cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('DELETE',
1856 l_srp_period_quota_id.srp_period_quota_id,
1857 l_old_plan_element.org_id
1858 );
1859 END LOOP;
1860
1861 FOR l_srp_period_quota_id IN c_srp_period_quota_csr (l_old_plan_element.quota_id)
1862 LOOP
1863 cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('INSERT',
1864 l_srp_period_quota_id.srp_period_quota_id,
1865 l_old_plan_element.org_id,
1866 l_number_dim
1867 );
1868 END LOOP;
1869 END IF;
1870 ELSIF p_plan_element.calc_formula_id <> l_old_plan_element.calc_formula_id
1871 THEN
1872 SELECT number_dim
1873 INTO l_number_dim_old
1874 FROM cn_calc_formulas
1875 WHERE calc_formula_id = l_old_plan_element.calc_formula_id;
1876
1877 SELECT number_dim
1878 INTO l_number_dim_new
1879 FROM cn_calc_formulas
1880 WHERE calc_formula_id = p_plan_element.calc_formula_id;
1881
1882 IF l_number_dim_new <> l_number_dim_old
1883 THEN
1884 IF l_number_dim_new < l_number_dim_old
1885 THEN
1886 FOR l_srp_period_quota_id IN c_srp_period_quota_csr (l_old_plan_element.quota_id)
1887 LOOP
1888 cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('DELETE',
1889 l_srp_period_quota_id.srp_period_quota_id,
1890 l_old_plan_element.org_id
1891 );
1892 END LOOP;
1893 END IF;
1894
1895 -- if reduce # dims to 1, then no longer need _ext records
1896 IF l_number_dim_new > 1
1897 THEN
1898 FOR l_srp_period_quota_id IN c_srp_period_quota_csr (l_old_plan_element.quota_id)
1899 LOOP
1900 cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('INSERT',
1901 l_srp_period_quota_id.srp_period_quota_id,
1902 l_old_plan_element.org_id,
1903 l_number_dim_new
1904 );
1905 END LOOP;
1906 END IF;
1907 END IF;
1908 END IF;
1909 END IF;
1910
1911 -- if necessary attach the default rate tables from the formula
1912 update_rate_quotas (p_api_version => p_api_version,
1913 p_init_msg_list => p_init_msg_list,
1914 p_commit => p_commit,
1915 p_validation_level => p_validation_level,
1916 x_return_status => x_return_status,
1917 x_msg_count => x_msg_count,
1918 x_msg_data => x_msg_data,
1919 p_pe_rec => p_plan_element,
1920 p_pe_rec_old => l_old_plan_element,
1921 p_rt_quota_asgns_rec_tbl => g_miss_rt_quota_asgns_rec_tbl,
1922 p_quota_name => p_plan_element.NAME,
1923 p_loading_status => x_loading_status,
1924 x_loading_status => l_loading_status
1925 );
1926 x_loading_status := l_loading_status;
1927
1928 IF (x_return_status <> fnd_api.g_ret_sts_success)
1929 THEN
1930 RAISE fnd_api.g_exc_error;
1931 END IF;
1932
1933 -- Calling proc to add system note for update
1934 add_system_note(
1935 l_old_plan_element,
1936 p_plan_element,
1937 'update',
1938 x_return_status,
1939 x_msg_count,
1940 x_msg_data
1941 );
1942 IF (x_return_status <> fnd_api.g_ret_sts_success)
1943 THEN
1944 RAISE fnd_api.g_exc_error;
1945 END IF;
1946
1947 -- End of API body.
1948 -- Standard check of p_commit.
1949 IF fnd_api.to_boolean (p_commit)
1950 THEN
1951 COMMIT WORK;
1952 END IF;
1953
1954 -- Standard call to get message count and if count is 1, get message info.
1955 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1956 EXCEPTION
1957 WHEN fnd_api.g_exc_error
1958 THEN
1959 ROLLBACK TO update_plan_element;
1960 x_return_status := fnd_api.g_ret_sts_error;
1961 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1962 WHEN fnd_api.g_exc_unexpected_error
1963 THEN
1964 ROLLBACK TO update_plan_element;
1965 x_return_status := fnd_api.g_ret_sts_unexp_error;
1966 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1967 WHEN OTHERS
1968 THEN
1969 ROLLBACK TO update_plan_element;
1970 x_return_status := fnd_api.g_ret_sts_unexp_error;
1971
1972 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1973 THEN
1974 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1975 END IF;
1976
1977 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1978 END update_plan_element;
1979
1980 -- Start of comments
1981 -- API name : Delete_Plan_Element
1982 -- Type : Private.
1983 -- Function :
1984 -- Pre-reqs : None.
1985 -- Parameters :
1986 -- IN : p_api_version IN NUMBER Required
1987 -- p_init_msg_list IN VARCHAR2 Optional
1988 -- Default = FND_API.G_FALSE
1989 -- p_commit IN VARCHAR2 Optional
1990 -- Default = FND_API.G_FALSE
1991 -- p_validation_level IN NUMBER Optional
1992 -- Default = FND_API.G_VALID_LEVEL_FULL
1993 -- p_plan_element IN plan_element_rec_type
1994 -- OUT : x_return_status OUT VARCHAR2(1)
1995 -- x_msg_count OUT NUMBER
1996 -- x_msg_data OUT VARCHAR2(2000)
1997 -- Version : Current version 1.0
1998 -- Notes : Note text
1999 --
2000 -- End of comments
2001 PROCEDURE delete_plan_element (
2002 p_api_version IN NUMBER,
2003 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2004 p_commit IN VARCHAR2 := fnd_api.g_false,
2005 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2006 p_plan_element IN OUT NOCOPY plan_element_rec_type,
2007 x_return_status OUT NOCOPY VARCHAR2,
2008 x_msg_count OUT NOCOPY NUMBER,
2009 x_msg_data OUT NOCOPY VARCHAR2
2010 )
2011 IS
2012 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Plan_Element';
2013 l_api_version CONSTANT NUMBER := 1.0;
2014 l_quota_name cn_quotas.NAME%TYPE;
2015 BEGIN
2016 -- Standard Start of API savepoint
2017 SAVEPOINT delete_plan_element;
2018
2019 -- Standard call to check for call compatibility.
2020 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
2021 THEN
2022 RAISE fnd_api.g_exc_unexpected_error;
2023 END IF;
2024
2025 -- Initialize message list if p_init_msg_list is set to TRUE.
2026 IF fnd_api.to_boolean (p_init_msg_list)
2027 THEN
2028 fnd_msg_pub.initialize;
2029 END IF;
2030
2031 -- Initialize API return status to success
2032 x_return_status := fnd_api.g_ret_sts_success;
2033 -- validate plan element
2034 validate_plan_element (p_api_version => p_api_version,
2035 p_plan_element => p_plan_element,
2036 p_action => 'DELETE',
2037 x_return_status => x_return_status,
2038 x_msg_count => x_msg_count,
2039 x_msg_data => x_msg_data
2040 );
2041
2042 IF (x_return_status <> fnd_api.g_ret_sts_success)
2043 THEN
2044 RAISE fnd_api.g_exc_error;
2045 END IF;
2046
2047 -- API body
2048 SELECT NAME
2049 INTO l_quota_name
2050 FROM cn_quotas_v
2051 WHERE quota_id = p_plan_element.quota_id;
2052
2053 -- Call the Delete Record Table Handler
2054 cn_quotas_pkg.DELETE_RECORD (x_quota_id => p_plan_element.quota_id, x_name => l_quota_name);
2055
2056 -- Calling proc to add system note for delete
2057 add_system_note(
2058 p_plan_element,
2059 p_plan_element,
2060 'delete',
2061 x_return_status,
2062 x_msg_count,
2063 x_msg_data
2064 );
2065 IF (x_return_status <> fnd_api.g_ret_sts_success)
2066 THEN
2067 RAISE fnd_api.g_exc_error;
2068 END IF;
2069
2070 -- End of API body.
2071 -- Standard check of p_commit.
2072 IF fnd_api.to_boolean (p_commit)
2073 THEN
2074 COMMIT WORK;
2075 END IF;
2076
2077 -- Standard call to get message count and if count is 1, get message info.
2078 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2079 EXCEPTION
2080 WHEN fnd_api.g_exc_error
2081 THEN
2082 ROLLBACK TO delete_plan_element;
2083 x_return_status := fnd_api.g_ret_sts_error;
2084 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2085 WHEN fnd_api.g_exc_unexpected_error
2086 THEN
2087 ROLLBACK TO delete_plan_element;
2088 x_return_status := fnd_api.g_ret_sts_unexp_error;
2089 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2090 WHEN OTHERS
2091 THEN
2092 ROLLBACK TO delete_plan_element;
2093 x_return_status := fnd_api.g_ret_sts_unexp_error;
2094
2095 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2096 THEN
2097 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2098 END IF;
2099
2100 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2101 END delete_plan_element;
2102
2103 -- Start of comments
2104 -- API name : Validate_Plan_Element
2105 -- Type : Private.
2106 -- Function :
2107 -- Pre-reqs : None.
2108 -- Parameters :
2109 -- IN : p_api_version IN NUMBER Required
2110 -- p_init_msg_list IN VARCHAR2 Optional
2111 -- Default = FND_API.G_FALSE
2112 -- p_commit IN VARCHAR2 Optional
2113 -- Default = FND_API.G_FALSE
2114 -- p_validation_level IN NUMBER Optional
2115 -- Default = FND_API.G_VALID_LEVEL_FULL
2116 -- p_plan_element IN plan_element_rec_type
2117 -- OUT : x_return_status OUT VARCHAR2(1)
2118 -- x_msg_count OUT NUMBER
2119 -- x_msg_data OUT VARCHAR2(2000)
2120 -- Version : Current version 1.0
2121 -- Notes : Note text
2122 --
2123 -- End of comments
2124 PROCEDURE validate_plan_element (
2125 p_api_version IN NUMBER,
2126 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2127 p_commit IN VARCHAR2 := fnd_api.g_false,
2128 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2129 p_action IN VARCHAR2,
2130 p_plan_element IN OUT NOCOPY plan_element_rec_type,
2131 p_old_plan_element IN plan_element_rec_type := NULL,
2132 x_return_status OUT NOCOPY VARCHAR2,
2133 x_msg_count OUT NOCOPY NUMBER,
2134 x_msg_data OUT NOCOPY VARCHAR2
2135 )
2136 IS
2137
2138 --Added by hanaraya for bug 6505174
2139 CURSOR uplift_curs(p_quota_id cn_quotas.quota_id%TYPE, p_start_date cn_quotas.start_date%TYPE, p_end_date cn_quotas.end_date%TYPE)
2140 IS
2141 SELECT COUNT (1)
2142 FROM cn_quota_rule_uplifts qru,
2143 cn_quota_rules qr
2144 WHERE qr.quota_id = p_quota_id
2145 AND qr.quota_rule_id = qru.quota_rule_id
2146 AND (qru.start_date < p_start_date OR (p_end_date IS NOT NULL AND qru.end_date IS NULL) OR qru.end_date > p_end_date);
2147
2148 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_Plan_Element';
2149 l_api_version CONSTANT NUMBER := 1.0;
2150 l_formula_type cn_calc_formulas.formula_type%TYPE := NULL;
2151 l_temp_count NUMBER;
2152 l_quota_id NUMBER;
2153 l_payeechk NUMBER;
2154 l_uplift_dt_range NUMBER; --Added by hanaraya for bug 6505174
2155 BEGIN
2156 -- Standard Start of API savepoint
2157 SAVEPOINT validate_plan_element;
2158
2159 -- Standard call to check for call compatibility.
2160 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
2161 THEN
2162 RAISE fnd_api.g_exc_unexpected_error;
2163 END IF;
2164
2165 -- Initialize message list if p_init_msg_list is set to TRUE.
2166 IF fnd_api.to_boolean (p_init_msg_list)
2167 THEN
2168 fnd_msg_pub.initialize;
2169 END IF;
2170
2171 -- Initialize API return status to success
2172 x_return_status := fnd_api.g_ret_sts_success;
2173 p_plan_element.start_date := TRUNC (p_plan_element.start_date);
2174 p_plan_element.end_date := TRUNC (p_plan_element.end_date);
2175
2176
2177 -- API body
2178 IF (p_action = 'DELETE')
2179 THEN
2180 SELECT COUNT (*)
2181 INTO l_temp_count
2182 FROM cn_quotas_v
2183 WHERE quota_id = p_plan_element.quota_id;
2184
2185 IF l_temp_count = 0
2186 THEN
2187 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2188 THEN
2189 fnd_message.set_name ('CN', 'CN_INVALID_DEL_REC');
2190 fnd_msg_pub.ADD;
2191 END IF;
2192
2193 RAISE fnd_api.g_exc_error;
2194 END IF;
2195
2196 -- check whether the plan element is already assigned to a complan
2197 BEGIN
2198 SELECT 1
2199 INTO l_temp_count
2200 FROM SYS.DUAL
2201 WHERE NOT EXISTS (SELECT 1
2202 FROM cn_quota_assigns
2203 WHERE quota_id = p_plan_element.quota_id);
2204 EXCEPTION
2205 WHEN NO_DATA_FOUND
2206 THEN
2207 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2208 THEN
2209 fnd_message.set_name ('CN', 'PLN_QUOTA_DELETE_NA');
2210 fnd_msg_pub.ADD;
2211 END IF;
2212 RAISE fnd_api.g_exc_error;
2213 END;
2214 ELSE
2215 -- check whether user has access to this org
2216 IF (p_action = 'UPDATE')
2217 THEN
2218 -- better check that org_id first or you will cry
2219 IF NOT is_valid_org (p_plan_element.org_id, p_plan_element.quota_id)
2220 THEN
2221 RAISE fnd_api.g_exc_error;
2222 END IF;
2223
2224 -- 1. check object version number
2225 IF p_old_plan_element.object_version_number <> p_plan_element.object_version_number
2226 THEN
2227 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2228 THEN
2229 fnd_message.set_name ('CN', 'CN_RECORD_CHANGED');
2230 fnd_msg_pub.ADD;
2231 END IF;
2232
2233 RAISE fnd_api.g_exc_error;
2234 END IF;
2235
2236 -- 2. plan element name must be unique
2237 SELECT COUNT (1)
2238 INTO l_temp_count
2239 FROM cn_quotas_all pe
2240 WHERE NAME = p_plan_element.NAME AND p_plan_element.quota_id <> pe.quota_id AND p_plan_element.org_id = pe.org_id AND delete_flag = 'N';
2241
2242 IF l_temp_count <> 0
2243 THEN
2244 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2245 THEN
2246 fnd_message.set_name ('CN', 'CN_INPUT_MUST_UNIQUE');
2247 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('PE_NAME', 'INPUT_TOKEN'));
2248 fnd_msg_pub.ADD;
2249 END IF;
2250
2251 RAISE fnd_api.g_exc_error;
2252 END IF;
2253
2254 --Added by hanaraya for bug 6505174
2255 -- Check for date range overlap between plan element and quota rule uplifts
2256
2257 OPEN uplift_curs(p_plan_element.quota_id, p_plan_element.start_date, p_plan_element.end_date);
2258
2259 FETCH uplift_curs
2260 INTO l_uplift_dt_range;
2261
2262 CLOSE uplift_curs;
2263
2264 IF l_uplift_dt_range > 0
2265 THEN
2266 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2267 THEN
2268 fnd_message.set_name ('CN', 'CN_UPLIFT_DT_NOT_WIN_QUOTA');
2269 fnd_msg_pub.ADD;
2270 END IF;
2271 RAISE fnd_api.g_exc_error;
2272 END IF;
2273
2274 ELSIF (p_action = 'CREATE')
2275 THEN
2276 -- better check that org_id first or you will cry
2277 IF NOT is_valid_org (p_plan_element.org_id)
2278 THEN
2279 RAISE fnd_api.g_exc_error;
2280 END IF;
2281
2282 -- 2. plan element name must be unique
2283 SELECT COUNT (1)
2284 INTO l_temp_count
2285 FROM cn_quotas_all pe
2286 WHERE NAME = p_plan_element.NAME AND org_id = p_plan_element.org_id AND delete_flag = 'N';
2287
2288 IF l_temp_count <> 0
2289 THEN
2290 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2291 THEN
2292 fnd_message.set_name ('CN', 'CN_INPUT_MUST_UNIQUE');
2293 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('PE_NAME', 'INPUT_TOKEN'));
2294 fnd_msg_pub.ADD;
2295 END IF;
2296
2297 RAISE fnd_api.g_exc_error;
2298 END IF;
2299 END IF;
2300
2301 --###########################################################################
2302 --## VALIDATION FOR BOTH UPDATE AND CREATE
2303 --###########################################################################
2304
2305 -- 1. name can not be null
2306 IF (p_plan_element.NAME IS NULL) OR (p_plan_element.NAME = fnd_api.g_miss_char)
2307 THEN
2308 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2309 THEN
2310 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
2311 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('PE_NAME', 'INPUT_TOKEN'));
2312 fnd_msg_pub.ADD;
2313 END IF;
2314
2315 RAISE fnd_api.g_exc_error;
2316 END IF;
2317
2318 -- start date is not null
2319 IF (p_plan_element.start_date IS NULL) OR (p_plan_element.start_date = fnd_api.g_miss_date)
2320 THEN
2321 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2322 THEN
2323 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
2324 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('SD', 'INPUT_TOKEN'));
2325 fnd_msg_pub.ADD;
2326 END IF;
2327
2328 RAISE fnd_api.g_exc_error;
2329 END IF;
2330
2331 -- start date > end date
2332 IF (p_plan_element.end_date IS NOT NULL) AND (p_plan_element.start_date > p_plan_element.end_date)
2333 THEN
2334 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2335 THEN
2336 fnd_message.set_name ('CN', 'CN_DATE_RANGE_ERROR');
2337 fnd_msg_pub.ADD;
2338 END IF;
2339
2340 RAISE fnd_api.g_exc_error;
2341 END IF;
2342
2343
2344
2345 ----------------------------------------------------
2346 -- Validate All lookup codes, must have valid value
2347 ----------------------------------------------------
2348 validate_types (p_plan_element => p_plan_element, x_return_status => x_return_status);
2349
2350
2351
2352 IF (p_plan_element.target IS NULL)
2353 THEN
2354 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2355 THEN
2356 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
2357 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('VARIABLE(S)', 'PE_OBJECT_TYPE'));
2358 fnd_msg_pub.ADD;
2359 END IF;
2360
2361 RAISE fnd_api.g_exc_error;
2362 END IF;
2363
2364 IF (p_plan_element.payment_amount IS NULL)
2365 THEN
2366 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2367 THEN
2368 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
2369 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('VARIABLE(S)', 'PE_OBJECT_TYPE'));
2370 fnd_msg_pub.ADD;
2371 END IF;
2372
2373 RAISE fnd_api.g_exc_error;
2374 END IF;
2375
2376 IF (p_plan_element.performance_goal IS NULL)
2377 THEN
2378 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2379 THEN
2380 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
2381 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('VARIABLE(S)', 'PE_OBJECT_TYPE'));
2382 fnd_msg_pub.ADD;
2383 END IF;
2384
2385 RAISE fnd_api.g_exc_error;
2386 END IF;
2387
2388 IF (p_plan_element.payee_assign_flag NOT IN ('Y', 'N'))
2389 THEN
2390 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2391 THEN
2392 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
2393 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('PAYEE_ASSIGN', 'PE_OBJECT_TYPE'));
2394 fnd_msg_pub.ADD;
2395 END IF;
2396
2397 RAISE fnd_api.g_exc_error;
2398 END IF;
2399
2400 IF p_plan_element.addup_from_rev_class_flag NOT IN ('Y', 'N')
2401 THEN
2402 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2403 THEN
2404 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
2405 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('ADD_FROM_REVCLASS', 'PE_OBJECT_TYPE'));
2406 fnd_msg_pub.ADD;
2407 END IF;
2408
2409 RAISE fnd_api.g_exc_error;
2410 END IF;
2411
2412
2413 ---changes made for fixing the bug # 2739896
2414 IF p_plan_element.payee_assign_flag = 'Y'
2415 THEN
2416 SELECT COUNT (*)
2417 INTO l_payeechk
2418 FROM cn_quota_assigns cqa
2419 WHERE cqa.quota_id = p_plan_element.quota_id
2420 AND EXISTS (SELECT 1
2421 FROM cn_srp_plan_assigns cspa
2422 WHERE cspa.comp_plan_id = cqa.comp_plan_id AND EXISTS (SELECT 1
2423 FROM cn_srp_roles csr
2424 WHERE csr.salesrep_id = cspa.salesrep_id AND csr.role_id = 54));
2425
2426 IF (l_payeechk > 0)
2427 THEN
2428 fnd_message.set_name ('CN', 'CN_PAYEE_ASGN_FLAG_CHECK');
2429 fnd_msg_pub.ADD;
2430 RAISE fnd_api.g_exc_error;
2431 END IF;
2432 END IF;
2433 END IF; -- END OF DELETE VALIDATION
2434
2435 -- End of API body.
2436 <<end_api_body>>
2437 -- Standard call to get message count and if count is 1, get message info.
2438 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2439 EXCEPTION
2440 WHEN fnd_api.g_exc_error
2441 THEN
2442 ROLLBACK TO validate_plan_element;
2443 x_return_status := fnd_api.g_ret_sts_error;
2444 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2445 WHEN fnd_api.g_exc_unexpected_error
2446 THEN
2447 ROLLBACK TO validate_plan_element;
2448 x_return_status := fnd_api.g_ret_sts_unexp_error;
2449 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2450 WHEN OTHERS
2451 THEN
2452 ROLLBACK TO validate_plan_element;
2453 x_return_status := fnd_api.g_ret_sts_unexp_error;
2454
2455 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2456 THEN
2457 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2458 END IF;
2459
2460 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2461 END validate_plan_element;
2462
2463 -- Start of comments
2464 -- API name : Duplicate_Plan_Element
2465 -- Type : Private.
2466 -- Function :
2467 -- Pre-reqs : None.
2468 -- Parameters :
2469 PROCEDURE duplicate_plan_element (
2470 p_api_version IN NUMBER := cn_api.g_miss_num,
2471 p_init_msg_list IN VARCHAR2 := cn_api.g_false,
2472 p_commit IN VARCHAR2 := cn_api.g_false,
2473 p_validation_level IN NUMBER := cn_api.g_valid_level_full,
2474 p_quota_id IN cn_quotas.quota_id%TYPE := NULL,
2475 x_plan_element OUT NOCOPY plan_element_rec_type,
2476 x_return_status OUT NOCOPY VARCHAR2,
2477 x_msg_count OUT NOCOPY NUMBER,
2478 x_msg_data OUT NOCOPY VARCHAR2,
2479 x_loading_status OUT NOCOPY VARCHAR2
2480 )
2481 IS
2482 BEGIN
2483 NULL;
2484 END duplicate_plan_element;
2485
2486 PROCEDURE check_rate_dim (
2487 p_quota_id IN NUMBER
2488 )
2489 IS
2490 l_api_name CONSTANT VARCHAR2 (30) := 'check_rate_dim';
2491 l_same_pe NUMBER;
2492
2493 CURSOR c_rate_schedule_csr (
2494 pe_quota_id cn_quotas.quota_id%TYPE
2495 )
2496 IS
2497 SELECT qa.rate_schedule_id
2498 FROM cn_rt_quota_asgns qa
2499 WHERE qa.quota_id = pe_quota_id;
2500
2501 CURSOR c_rt_formula_csr (
2502 pe_quota_id cn_quotas.quota_id%TYPE,
2503 pe_rate_schedule_id cn_rt_quota_asgns.rate_schedule_id%TYPE
2504 )
2505 IS
2506 SELECT rtq.calc_formula_id
2507 FROM cn_rt_quota_asgns rtq
2508 WHERE rtq.quota_id = pe_quota_id AND rtq.rate_schedule_id = pe_rate_schedule_id;
2509
2510 CURSOR c_formula_input_csr (
2511 pe_calc_formula_id cn_formula_inputs.calc_formula_id%TYPE
2512 )
2513 IS
2514 SELECT fi.formula_input_id
2515 FROM cn_formula_inputs fi
2516 WHERE fi.calc_formula_id = pe_calc_formula_id;
2517
2518 l_cumulative_flag cn_formula_inputs.cumulative_flag%TYPE;
2519 l_split_flag cn_formula_inputs.split_flag%TYPE;
2520 l_rate_dim_sequence cn_formula_inputs.rate_dim_sequence%TYPE;
2521 l_dim_unit_code cn_rate_dimensions.dim_unit_code%TYPE;
2522 l_quota_name cn_quotas.NAME%TYPE;
2523 BEGIN
2524 -- Initialize API return status to success
2525 FOR l_rate_schedule_id IN c_rate_schedule_csr (p_quota_id)
2526 LOOP
2527 FOR l_calc_formula_id IN c_rt_formula_csr (p_quota_id, l_rate_schedule_id.rate_schedule_id)
2528 LOOP
2529 FOR l_formula_input_id IN c_formula_input_csr (l_calc_formula_id.calc_formula_id)
2530 LOOP
2531 SELECT cumulative_flag,
2532 split_flag,
2533 rate_dim_sequence
2534 INTO l_cumulative_flag,
2535 l_split_flag,
2536 l_rate_dim_sequence
2537 FROM cn_formula_inputs
2538 WHERE formula_input_id = l_formula_input_id.formula_input_id;
2539
2540 IF (l_cumulative_flag = 'Y') OR (l_split_flag = 'Y')
2541 THEN
2542 SELECT cd.dim_unit_code
2543 INTO l_dim_unit_code
2544 FROM cn_rate_dimensions cd,
2545 cn_rate_sch_dims cs
2546 WHERE cs.rate_dim_sequence = l_rate_dim_sequence
2547 AND cs.rate_schedule_id = l_rate_schedule_id.rate_schedule_id
2548 AND cd.rate_dimension_id = cs.rate_dimension_id;
2549
2550 -- clku bug 2426405
2551 IF (l_dim_unit_code <> 'PERCENT') AND (l_dim_unit_code <> 'AMOUNT') AND (l_dim_unit_code <> 'EXPRESSION')
2552 THEN
2553 SELECT NAME
2554 INTO l_quota_name
2555 FROM cn_quotas
2556 WHERE quota_id = p_quota_id;
2557
2558 cn_message_pkg.set_message (appl_short_name => 'CN',
2559 message_name => 'CN_RATE_DIM_MUST_NUMERIC',
2560 token_name1 => 'QUOTA_NAME',
2561 token_value1 => l_quota_name,
2562 token_name2 => NULL,
2563 token_value2 => NULL,
2564 token_name3 => NULL,
2565 token_value3 => NULL,
2566 token_name4 => NULL,
2567 token_value4 => NULL,
2568 TRANSLATE => TRUE
2569 );
2570 fnd_msg_pub.ADD;
2571 END IF;
2572 END IF;
2573 END LOOP;
2574 END LOOP;
2575 END LOOP;
2576 END check_rate_dim;
2577
2578 -- Check that the plan element is valid
2579 PROCEDURE validate_plan_element (
2580 p_api_version IN NUMBER,
2581 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2582 p_commit IN VARCHAR2 := fnd_api.g_false,
2583 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2584 p_comp_plan_id IN NUMBER := NULL,
2585 p_quota_id IN NUMBER,
2586 x_status_code OUT NOCOPY VARCHAR2,
2587 x_return_status OUT NOCOPY VARCHAR2,
2588 x_msg_count OUT NOCOPY NUMBER,
2589 x_msg_data OUT NOCOPY VARCHAR2
2590 )
2591 IS
2592 CURSOR rt_quota_asgns_curs
2593 IS
2594 SELECT rqa.rate_schedule_id,
2595 rs.NAME
2596 FROM cn_rt_quota_asgns rqa,
2597 cn_rate_schedules rs
2598 WHERE rqa.quota_id = p_quota_id AND rqa.rate_schedule_id = rs.rate_schedule_id;
2599
2600 CURSOR rules
2601 IS
2602 SELECT qr.quota_rule_id,
2603 qr.revenue_class_id,
2604 rc.NAME rev_class_name,
2605 q.quota_type_code
2606 FROM cn_quotas q,
2607 cn_quota_rules_all qr,
2608 cn_revenue_classes_all rc
2609 WHERE qr.quota_id = p_quota_id
2610 AND qr.revenue_class_id = rc.revenue_class_id
2611 AND q.quota_id = qr.quota_id
2612 AND q.quota_type_code IN ('FORMULA', 'EXTERNAL');
2613
2614 CURSOR factors (
2615 p_quota_rule_id NUMBER
2616 )
2617 IS
2618 SELECT event_factor,
2619 trx_type
2620 FROM cn_trx_factors
2621 WHERE quota_rule_id = p_quota_rule_id;
2622
2623 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_Plan_Element';
2624 l_api_version CONSTANT NUMBER := 1.0;
2625 factor_rec factors%ROWTYPE;
2626 key_factor_total NUMBER := 0;
2627 rule_rec rules%ROWTYPE;
2628 recinfo rt_quota_asgns_curs%ROWTYPE;
2629 x_formula_name cn_calc_formulas.NAME%TYPE;
2630 x_calc_formula_id cn_calc_formulas.calc_formula_id%TYPE;
2631 l_tmp NUMBER;
2632 l_plan_name cn_comp_plans.NAME%TYPE;
2633 l_plan_element plan_element_rec_type;
2634 g_incomplete VARCHAR2 (30) := 'INCOMPLETE';
2635 g_complete VARCHAR2 (30) := 'COMPLETE';
2636 l_temp_status_code VARCHAR2 (30) := g_complete;
2637 BEGIN
2638 -- Standard Start of API savepoint
2639 SAVEPOINT validate_plan_element_2;
2640
2641 -- Standard call to check for call compatibility.
2642 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
2643 THEN
2644 RAISE fnd_api.g_exc_unexpected_error;
2645 END IF;
2646
2647 -- Initialize message list if p_init_msg_list is set to TRUE.
2648 IF fnd_api.to_boolean (p_init_msg_list)
2649 THEN
2650 fnd_msg_pub.initialize;
2651 END IF;
2652
2653 -- Initialize API return status to success
2654 x_return_status := fnd_api.g_ret_sts_success;
2655 -- fill the rec
2656 l_plan_element := get_plan_element (p_quota_id);
2657 -- validate the plan element
2658 validate_plan_element (p_api_version => p_api_version,
2659 p_plan_element => l_plan_element,
2660 p_old_plan_element => l_plan_element,
2661 p_action => 'UPDATE',
2662 x_return_status => x_return_status,
2663 x_msg_count => x_msg_count,
2664 x_msg_data => x_msg_data
2665 );
2666
2667 -- in case of error, raise exception
2668 IF (x_return_status <> fnd_api.g_ret_sts_success)
2669 THEN
2670 l_temp_status_code := g_incomplete;
2671 RAISE fnd_api.g_exc_error;
2672 END IF;
2673
2674 IF p_comp_plan_id IS NOT NULL
2675 THEN
2676 SELECT NAME
2677 INTO l_plan_name
2678 FROM cn_comp_plans
2679 WHERE comp_plan_id = p_comp_plan_id;
2680 END IF;
2681
2682 SELECT cf.NAME,
2683 q.calc_formula_id
2684 INTO x_formula_name,
2685 x_calc_formula_id
2686 FROM cn_quotas q,
2687 cn_calc_formulas cf
2688 WHERE q.quota_id = p_quota_id AND q.calc_formula_id = cf.calc_formula_id(+) AND q.quota_type_code IN ('EXTERNAL', 'FORMULA');
2689
2690 IF l_plan_element.quota_type_code IN ('FORMULA', 'EXTERNAL')
2691 THEN
2692 IF l_plan_element.quota_type_code = 'FORMULA'
2693 THEN
2694 check_rate_dim (p_quota_id);
2695 END IF;
2696
2697 IF l_plan_element.calc_formula_id IS NULL AND l_plan_element.quota_type_code = 'FORMULA'
2698 THEN
2699 l_temp_status_code := g_incomplete;
2700 set_message (p_plan_name => l_plan_name,
2701 p_pe_name => l_plan_element.NAME,
2702 message_name => 'PLN_QUOTA_NO_FORMULA',
2703 token_name => NULL,
2704 token_value => NULL
2705 );
2706 ELSIF l_plan_element.package_name IS NULL AND l_plan_element.quota_type_code = 'EXTERNAL'
2707 THEN
2708 l_temp_status_code := g_incomplete;
2709 set_message (p_plan_name => l_plan_name,
2710 p_pe_name => l_plan_element.NAME,
2711 message_name => 'PLN_QUOTA_NO_PACKAGE',
2712 token_name => NULL,
2713 token_value => NULL
2714 );
2715 END IF;
2716
2717 -- Check Schedule exists.
2718 IF l_plan_element.quota_type_code IN ('FORMULA', 'EXTERNAL')
2719 THEN
2720 SELECT COUNT (1)
2721 INTO l_tmp
2722 FROM cn_rt_quota_asgns
2723 WHERE quota_id = p_quota_id;
2724
2725 IF (l_tmp = 0)
2726 THEN
2727 l_temp_status_code := g_incomplete;
2728 set_message (p_plan_name => l_plan_name,
2729 p_pe_name => l_plan_element.NAME,
2730 message_name => 'PLN_QUOTA_NO_SCHEDULE',
2731 token_name => NULL,
2732 token_value => NULL
2733 );
2734 END IF;
2735 END IF;
2736
2737 IF l_temp_status_code = g_complete AND l_plan_element.incentive_type_code <> 'BONUS'
2738 THEN
2739 OPEN rules;
2740
2741 LOOP
2742 FETCH rules
2743 INTO rule_rec;
2744
2745
2746 -- Need to distinguish between no rows and the all rows found
2747 IF rules%ROWCOUNT = 0
2748 THEN
2749 l_temp_status_code := g_incomplete;
2750 set_message (p_plan_name => l_plan_name,
2751 p_pe_name => l_plan_element.NAME,
2752 message_name => 'PLN_QUOTA_NO_RULES',
2753 token_name => NULL,
2754 token_value => NULL
2755 );
2756 EXIT; -- exit loop
2757 ELSE
2758 IF rules%NOTFOUND
2759 THEN
2760 EXIT;
2761 ELSE
2762 IF l_temp_status_code = g_complete
2763 THEN
2764 key_factor_total := 0;
2765 OPEN factors (rule_rec.quota_rule_id);
2766
2767 LOOP
2768 FETCH factors
2769 INTO factor_rec;
2770
2771 IF factors%ROWCOUNT = 0
2772 THEN
2773 l_temp_status_code := g_incomplete;
2774 set_message (p_plan_name => l_plan_name,
2775 p_pe_name => l_plan_element.NAME,
2776 message_name => 'PLN_QUOTA_RULE_NO_FACTORS',
2777 token_name => 'REV_CLASS_NAME',
2778 token_value => rule_rec.rev_class_name
2779 );
2780 EXIT; -- exit loop
2781 ELSE
2782 IF factors%NOTFOUND
2783 THEN
2784 IF key_factor_total <> 100
2785 THEN
2786 l_temp_status_code := g_incomplete;
2787 set_message (p_plan_name => l_plan_name,
2788 p_pe_name => l_plan_element.NAME,
2789 message_name => 'PLN_QUOTA_RULE_FACTORS_NOT_100',
2790 token_name => 'REV_CLASS_NAME',
2791 token_value => rule_rec.rev_class_name
2792 );
2793 END IF;
2794
2795 EXIT;
2796 ELSE
2797 IF (factor_rec.trx_type = 'ORD' OR factor_rec.trx_type = 'INV' OR factor_rec.trx_type = 'PMT')
2798 THEN
2799 key_factor_total := key_factor_total + factor_rec.event_factor;
2800 END IF;
2801 END IF;
2802 END IF;
2803 END LOOP;
2804
2805 CLOSE factors;
2806 END IF;
2807 END IF; -- sqlnotfound
2808 END IF; -- rowcount
2809 END LOOP;
2810
2811 CLOSE rules;
2812 END IF;
2813 END IF;
2814
2815 -- pass the status back to the calling program.
2816 -- all problems will be written to a table for review
2817 -- we just need to tell the comp plan that it is invalid
2818 x_status_code := l_temp_status_code;
2819
2820 -- End of API body.
2821 -- Standard check of p_commit.
2822 IF fnd_api.to_boolean (p_commit)
2823 THEN
2824 COMMIT WORK;
2825 END IF;
2826
2827 -- Standard call to get message count and if count is 1, get message info.
2828 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2829 EXCEPTION
2830 WHEN fnd_api.g_exc_error
2831 THEN
2832 ROLLBACK TO validate_plan_element_2;
2833 x_return_status := fnd_api.g_ret_sts_error;
2834 x_status_code := g_incomplete;
2835 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2836 WHEN fnd_api.g_exc_unexpected_error
2837 THEN
2838 ROLLBACK TO validate_plan_element_2;
2839 x_return_status := fnd_api.g_ret_sts_unexp_error;
2840 x_status_code := g_incomplete;
2841 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2842 WHEN OTHERS
2843 THEN
2844 ROLLBACK TO validate_plan_element_2;
2845 x_return_status := fnd_api.g_ret_sts_unexp_error;
2846 x_status_code := g_incomplete;
2847
2848 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2849 THEN
2850 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2851 END IF;
2852
2853 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2854 END validate_plan_element;
2855 END cn_plan_element_pvt;