[Home] [Help]
PACKAGE BODY: APPS.CN_PLAN_ELEMENT_PVT
Source
1 PACKAGE BODY cn_plan_element_pvt AS
2 /*$Header: cnvpeb.pls 120.26 2010/10/27 22:09:40 mguo 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 ------ Bug 8796904 ------
1716 -- Assiged quota to comp plan check if the start date and the end date changes.
1717 IF Trunc(p_plan_element.start_date) <> Trunc(l_old_plan_element.start_date) OR
1718 Trunc(Nvl(p_plan_element.end_date,fnd_api.g_miss_date))
1719 <> Trunc(Nvl(l_old_plan_element.end_date,fnd_api.G_MISS_DATE )) THEN
1720
1721 cn_chk_plan_element_pkg.chk_comp_plan_date
1722 (
1723 x_return_status => x_return_status,
1724 p_start_date => p_plan_element.start_date,
1725 p_end_date => p_plan_element.end_date,
1726 p_quota_id => l_old_plan_element.quota_id,
1727 p_quota_name => p_plan_element.NAME,
1728 p_loading_status => x_loading_status,
1729 x_loading_status => l_loading_status
1730 );
1731 x_loading_status := l_loading_status;
1732 END IF;
1733
1734 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1735 RAISE FND_API.G_EXC_ERROR ;
1736 END IF;
1737
1738
1739 -- Check the Plan Element start date and end date fall with in the rt_formula_asgns
1740 IF p_plan_element.calc_formula_id IS NOT NULL
1741 THEN
1742 IF p_plan_element.calc_formula_id = l_old_plan_element.calc_formula_id
1743 THEN
1744 cn_chk_plan_element_pkg.chk_rate_quota_date (x_return_status => x_return_status,
1745 p_start_date => p_plan_element.start_date,
1746 p_end_date => p_plan_element.end_date,
1747 p_quota_name => p_plan_element.NAME,
1748 p_quota_id => p_plan_element.quota_id,
1749 p_loading_status => x_loading_status,
1750 x_loading_status => l_loading_status
1751 );
1752 x_loading_status := l_loading_status;
1753 END IF;
1754
1755 -- error if the status is not success
1756 IF (x_return_status <> fnd_api.g_ret_sts_success)
1757 THEN
1758 RAISE fnd_api.g_exc_error;
1759 END IF;
1760 END IF;
1761
1762
1763 -- 4. Select the Target, Fixed Amount and Goal
1764 IF p_plan_element.addup_from_rev_class_flag = 'Y'
1765 THEN
1766 SELECT SUM (target)
1767 INTO p_plan_element.target
1768 FROM cn_quota_rules
1769 WHERE quota_id = p_plan_element.quota_id;
1770
1771 SELECT SUM (payment_amount)
1772 INTO p_plan_element.payment_amount
1773 FROM cn_quota_rules
1774 WHERE quota_id = p_plan_element.quota_id;
1775
1776 SELECT SUM (performance_goal)
1777 INTO p_plan_element.performance_goal
1778 FROM cn_quota_rules
1779 WHERE quota_id = p_plan_element.quota_id;
1780 END IF;
1781
1782 -- update the accounts data
1783 validate_and_update_ccids (p_plan_element);
1784
1785 -- call the table handler
1786 cn_quotas_pkg.begin_record (x_operation => 'UPDATE',
1787 x_rowid => g_row_id,
1788 x_quota_id => p_plan_element.quota_id,
1789 x_object_version_number => p_plan_element.object_version_number,
1790 x_name => p_plan_element.NAME,
1791 x_target => NVL (p_plan_element.target, 0),
1792 x_quota_type_code => p_plan_element.quota_type_code,
1793 x_usage_code => NULL,
1794 x_payment_amount => NVL (p_plan_element.payment_amount, 0),
1795 x_description => p_plan_element.description,
1796 x_start_date => p_plan_element.start_date,
1797 x_end_date => p_plan_element.end_date,
1798 x_quota_status => p_plan_element.quota_status,
1799 x_calc_formula_id => p_plan_element.calc_formula_id,
1800 x_incentive_type_code => p_plan_element.incentive_type_code,
1801 x_credit_type_id => p_plan_element.credit_type_id,
1802 x_rt_sched_custom_flag => NULL,
1803 x_package_name => p_plan_element.package_name,
1804 x_performance_goal => NVL (p_plan_element.performance_goal, 0),
1805 x_interval_type_id => p_plan_element.interval_type_id,
1806 x_payee_assign_flag => p_plan_element.payee_assign_flag,
1807 x_vesting_flag => p_plan_element.vesting_flag,
1808 x_expense_account_id => p_plan_element.expense_account_id,
1809 x_liability_account_id => p_plan_element.liability_account_id,
1810 x_quota_group_code => p_plan_element.quota_group_code,
1811 x_payment_group_code => p_plan_element.payment_group_code,
1812 x_quota_unspecified => NULL,
1813 x_last_update_date => g_last_update_date,
1814 x_last_updated_by => g_last_updated_by,
1815 x_creation_date => NULL,
1816 x_created_by => NULL,
1817 x_last_update_login => g_last_update_login,
1818 x_program_type => NULL,
1819 --x_status_code => p_plan_element.quota_status,
1820 x_period_type_code => NULL,
1821 x_start_num => NULL,
1822 x_end_num => NULL,
1823 x_addup_from_rev_class_flag => p_plan_element.addup_from_rev_class_flag,
1824 x_attribute_category => p_plan_element.attribute_category,
1825 x_attribute1 => p_plan_element.attribute1,
1826 x_attribute2 => p_plan_element.attribute2,
1827 x_attribute3 => p_plan_element.attribute3,
1828 x_attribute4 => p_plan_element.attribute4,
1829 x_attribute5 => p_plan_element.attribute5,
1830 x_attribute6 => p_plan_element.attribute6,
1831 x_attribute7 => p_plan_element.attribute7,
1832 x_attribute8 => p_plan_element.attribute8,
1833 x_attribute9 => p_plan_element.attribute9,
1834 x_attribute10 => p_plan_element.attribute10,
1835 x_attribute11 => p_plan_element.attribute11,
1836 x_attribute12 => p_plan_element.attribute12,
1837 x_attribute13 => p_plan_element.attribute13,
1838 x_attribute14 => p_plan_element.attribute14,
1839 x_attribute15 => p_plan_element.attribute15,
1840 x_indirect_credit => p_plan_element.indirect_credit_code,
1841 x_org_id => p_plan_element.org_id,
1842 x_salesrep_end_flag =>p_plan_element.sreps_enddated_flag
1843 );
1844
1845 -- update expressions using this plan element
1846 IF (p_plan_element.NAME <> l_old_plan_element.NAME)
1847 THEN
1848 update_exprs (p_plan_element.quota_id, l_old_plan_element.NAME, p_plan_element.NAME);
1849 END IF;
1850 -- Commented out for Bug 4722521------------------------------------------------
1851 -- delete period quotas and distribute them again
1852 IF l_old_plan_element.start_date <> p_plan_element.start_date
1853 OR NVL (p_plan_element.end_date, fnd_api.g_miss_date)
1854 <> NVL (l_old_plan_element.end_date, fnd_api.g_miss_date)
1855 THEN
1856 -- Call the Table Handler to Delete the Old Period quotas
1857 -- cn_period_quotas_pkg.DELETE_RECORD (p_plan_element.quota_id);
1858 cn_period_quotas_pkg.distribute_target (p_plan_element.quota_id);
1859 END IF;
1860 -- Commented out for Bug 4722521------------------------------------------------
1861 -- check if we need to update the cn_srp_period_quotas ext table. If yes, update the table
1862 -- if the new assignement is external package, we do not do anything
1863 IF p_plan_element.quota_type_code <> 'EXTERNAL'
1864 THEN
1865 -- if the old assignement is external package, we wipe out the ext table and re-insert the record
1866 IF l_old_plan_element.quota_type_code = 'EXTERNAL'
1867 THEN
1868 OPEN get_number_dim (l_old_plan_element.quota_id);
1869
1870 FETCH get_number_dim
1871 INTO l_number_dim;
1872
1873 CLOSE get_number_dim;
1874
1875 IF l_number_dim > 1
1876 THEN
1877 FOR l_srp_period_quota_id IN c_srp_period_quota_csr (l_old_plan_element.quota_id)
1878 LOOP
1879 cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('DELETE',
1880 l_srp_period_quota_id.srp_period_quota_id,
1881 l_old_plan_element.org_id
1882 );
1883 END LOOP;
1884
1885 FOR l_srp_period_quota_id IN c_srp_period_quota_csr (l_old_plan_element.quota_id)
1886 LOOP
1887 cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('INSERT',
1888 l_srp_period_quota_id.srp_period_quota_id,
1889 l_old_plan_element.org_id,
1890 l_number_dim
1891 );
1892 END LOOP;
1893 END IF;
1894 ELSIF p_plan_element.calc_formula_id <> l_old_plan_element.calc_formula_id
1895 THEN
1896 SELECT number_dim
1897 INTO l_number_dim_old
1898 FROM cn_calc_formulas
1899 WHERE calc_formula_id = l_old_plan_element.calc_formula_id;
1900
1901 SELECT number_dim
1902 INTO l_number_dim_new
1903 FROM cn_calc_formulas
1904 WHERE calc_formula_id = p_plan_element.calc_formula_id;
1905
1906 IF l_number_dim_new <> l_number_dim_old
1907 THEN
1908 IF l_number_dim_new < l_number_dim_old
1909 THEN
1910 FOR l_srp_period_quota_id IN c_srp_period_quota_csr (l_old_plan_element.quota_id)
1911 LOOP
1912 cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('DELETE',
1913 l_srp_period_quota_id.srp_period_quota_id,
1914 l_old_plan_element.org_id
1915 );
1916 END LOOP;
1917 END IF;
1918
1919 -- if reduce # dims to 1, then no longer need _ext records
1920 IF l_number_dim_new > 1
1921 THEN
1922 FOR l_srp_period_quota_id IN c_srp_period_quota_csr (l_old_plan_element.quota_id)
1923 LOOP
1924 cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('INSERT',
1925 l_srp_period_quota_id.srp_period_quota_id,
1926 l_old_plan_element.org_id,
1927 l_number_dim_new
1928 );
1929 END LOOP;
1930 END IF;
1931 END IF;
1932 END IF;
1933 END IF;
1934
1935 -- if necessary attach the default rate tables from the formula
1936 update_rate_quotas (p_api_version => p_api_version,
1937 p_init_msg_list => p_init_msg_list,
1938 p_commit => p_commit,
1939 p_validation_level => p_validation_level,
1940 x_return_status => x_return_status,
1941 x_msg_count => x_msg_count,
1942 x_msg_data => x_msg_data,
1943 p_pe_rec => p_plan_element,
1944 p_pe_rec_old => l_old_plan_element,
1945 p_rt_quota_asgns_rec_tbl => g_miss_rt_quota_asgns_rec_tbl,
1946 p_quota_name => p_plan_element.NAME,
1947 p_loading_status => x_loading_status,
1948 x_loading_status => l_loading_status
1949 );
1950 x_loading_status := l_loading_status;
1951
1952 IF (x_return_status <> fnd_api.g_ret_sts_success)
1953 THEN
1954 RAISE fnd_api.g_exc_error;
1955 END IF;
1956
1957 -- Calling proc to add system note for update
1958 add_system_note(
1959 l_old_plan_element,
1960 p_plan_element,
1961 'update',
1962 x_return_status,
1963 x_msg_count,
1964 x_msg_data
1965 );
1966 IF (x_return_status <> fnd_api.g_ret_sts_success)
1967 THEN
1968 RAISE fnd_api.g_exc_error;
1969 END IF;
1970
1971 -- End of API body.
1972 -- Standard check of p_commit.
1973 IF fnd_api.to_boolean (p_commit)
1974 THEN
1975 COMMIT WORK;
1976 END IF;
1977
1978 -- Standard call to get message count and if count is 1, get message info.
1979 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1980 EXCEPTION
1981 WHEN fnd_api.g_exc_error
1982 THEN
1983 ROLLBACK TO update_plan_element;
1984 x_return_status := fnd_api.g_ret_sts_error;
1985 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1986 WHEN fnd_api.g_exc_unexpected_error
1987 THEN
1988 ROLLBACK TO update_plan_element;
1989 x_return_status := fnd_api.g_ret_sts_unexp_error;
1990 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1991 WHEN OTHERS
1992 THEN
1993 ROLLBACK TO update_plan_element;
1994 x_return_status := fnd_api.g_ret_sts_unexp_error;
1995
1996 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1997 THEN
1998 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1999 END IF;
2000
2001 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2002 END update_plan_element;
2003
2004 -- Start of comments
2005 -- API name : Delete_Plan_Element
2006 -- Type : Private.
2007 -- Function :
2008 -- Pre-reqs : None.
2009 -- Parameters :
2010 -- IN : p_api_version IN NUMBER Required
2011 -- p_init_msg_list IN VARCHAR2 Optional
2012 -- Default = FND_API.G_FALSE
2013 -- p_commit IN VARCHAR2 Optional
2014 -- Default = FND_API.G_FALSE
2015 -- p_validation_level IN NUMBER Optional
2016 -- Default = FND_API.G_VALID_LEVEL_FULL
2017 -- p_plan_element IN plan_element_rec_type
2018 -- OUT : x_return_status OUT VARCHAR2(1)
2019 -- x_msg_count OUT NUMBER
2020 -- x_msg_data OUT VARCHAR2(2000)
2021 -- Version : Current version 1.0
2022 -- Notes : Note text
2023 --
2024 -- End of comments
2025 PROCEDURE delete_plan_element (
2026 p_api_version IN NUMBER,
2027 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2028 p_commit IN VARCHAR2 := fnd_api.g_false,
2029 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2030 p_plan_element IN OUT NOCOPY plan_element_rec_type,
2031 x_return_status OUT NOCOPY VARCHAR2,
2032 x_msg_count OUT NOCOPY NUMBER,
2033 x_msg_data OUT NOCOPY VARCHAR2
2034 )
2035 IS
2036 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Plan_Element';
2037 l_api_version CONSTANT NUMBER := 1.0;
2038 l_quota_name cn_quotas.NAME%TYPE;
2039 BEGIN
2040 -- Standard Start of API savepoint
2041 SAVEPOINT delete_plan_element;
2042
2043 -- Standard call to check for call compatibility.
2044 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
2045 THEN
2046 RAISE fnd_api.g_exc_unexpected_error;
2047 END IF;
2048
2049 -- Initialize message list if p_init_msg_list is set to TRUE.
2050 IF fnd_api.to_boolean (p_init_msg_list)
2051 THEN
2052 fnd_msg_pub.initialize;
2053 END IF;
2054
2055 -- Initialize API return status to success
2056 x_return_status := fnd_api.g_ret_sts_success;
2057 -- validate plan element
2058 validate_plan_element (p_api_version => p_api_version,
2059 p_plan_element => p_plan_element,
2060 p_action => 'DELETE',
2061 x_return_status => x_return_status,
2062 x_msg_count => x_msg_count,
2063 x_msg_data => x_msg_data
2064 );
2065
2066 IF (x_return_status <> fnd_api.g_ret_sts_success)
2067 THEN
2068 RAISE fnd_api.g_exc_error;
2069 END IF;
2070
2071 -- API body
2072 SELECT NAME
2073 INTO l_quota_name
2074 FROM cn_quotas_v
2075 WHERE quota_id = p_plan_element.quota_id;
2076
2077 -- Call the Delete Record Table Handler
2078 cn_quotas_pkg.DELETE_RECORD (x_quota_id => p_plan_element.quota_id, x_name => l_quota_name);
2079
2080 -- Calling proc to add system note for delete
2081 add_system_note(
2082 p_plan_element,
2083 p_plan_element,
2084 'delete',
2085 x_return_status,
2086 x_msg_count,
2087 x_msg_data
2088 );
2089 IF (x_return_status <> fnd_api.g_ret_sts_success)
2090 THEN
2091 RAISE fnd_api.g_exc_error;
2092 END IF;
2093
2094 -- End of API body.
2095 -- Standard check of p_commit.
2096 IF fnd_api.to_boolean (p_commit)
2097 THEN
2098 COMMIT WORK;
2099 END IF;
2100
2101 -- Standard call to get message count and if count is 1, get message info.
2102 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2103 EXCEPTION
2104 WHEN fnd_api.g_exc_error
2105 THEN
2106 ROLLBACK TO delete_plan_element;
2107 x_return_status := fnd_api.g_ret_sts_error;
2108 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2109 WHEN fnd_api.g_exc_unexpected_error
2110 THEN
2111 ROLLBACK TO delete_plan_element;
2112 x_return_status := fnd_api.g_ret_sts_unexp_error;
2113 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2114 WHEN OTHERS
2115 THEN
2116 ROLLBACK TO delete_plan_element;
2117 x_return_status := fnd_api.g_ret_sts_unexp_error;
2118
2119 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2120 THEN
2121 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2122 END IF;
2123
2124 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2125 END delete_plan_element;
2126
2127 -- Start of comments
2128 -- API name : Validate_Plan_Element
2129 -- Type : Private.
2130 -- Function :
2131 -- Pre-reqs : None.
2132 -- Parameters :
2133 -- IN : p_api_version IN NUMBER Required
2134 -- p_init_msg_list IN VARCHAR2 Optional
2135 -- Default = FND_API.G_FALSE
2136 -- p_commit IN VARCHAR2 Optional
2137 -- Default = FND_API.G_FALSE
2138 -- p_validation_level IN NUMBER Optional
2139 -- Default = FND_API.G_VALID_LEVEL_FULL
2140 -- p_plan_element IN plan_element_rec_type
2141 -- OUT : x_return_status OUT VARCHAR2(1)
2142 -- x_msg_count OUT NUMBER
2143 -- x_msg_data OUT VARCHAR2(2000)
2144 -- Version : Current version 1.0
2145 -- Notes : Note text
2146 --
2147 -- End of comments
2148 PROCEDURE validate_plan_element (
2149 p_api_version IN NUMBER,
2150 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2151 p_commit IN VARCHAR2 := fnd_api.g_false,
2152 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2153 p_action IN VARCHAR2,
2154 p_plan_element IN OUT NOCOPY plan_element_rec_type,
2155 p_old_plan_element IN plan_element_rec_type := NULL,
2156 x_return_status OUT NOCOPY VARCHAR2,
2157 x_msg_count OUT NOCOPY NUMBER,
2158 x_msg_data OUT NOCOPY VARCHAR2
2159 )
2160 IS
2161
2162 --Added by hanaraya for bug 6505174
2163 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)
2164 IS
2165 SELECT COUNT (1)
2166 FROM cn_quota_rule_uplifts qru,
2167 cn_quota_rules qr
2168 WHERE qr.quota_id = p_quota_id
2169 AND qr.quota_rule_id = qru.quota_rule_id
2170 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);
2171
2172 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_Plan_Element';
2173 l_api_version CONSTANT NUMBER := 1.0;
2174 l_formula_type cn_calc_formulas.formula_type%TYPE := NULL;
2175 l_temp_count NUMBER;
2176 l_quota_id NUMBER;
2177 l_payeechk NUMBER;
2178 l_uplift_dt_range NUMBER; --Added by hanaraya for bug 6505174
2179 BEGIN
2180 -- Standard Start of API savepoint
2181 SAVEPOINT validate_plan_element;
2182
2183 -- Standard call to check for call compatibility.
2184 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
2185 THEN
2186 RAISE fnd_api.g_exc_unexpected_error;
2187 END IF;
2188
2189 -- Initialize message list if p_init_msg_list is set to TRUE.
2190 IF fnd_api.to_boolean (p_init_msg_list)
2191 THEN
2192 fnd_msg_pub.initialize;
2193 END IF;
2194
2195 -- Initialize API return status to success
2196 x_return_status := fnd_api.g_ret_sts_success;
2197 p_plan_element.start_date := TRUNC (p_plan_element.start_date);
2198 p_plan_element.end_date := TRUNC (p_plan_element.end_date);
2199
2200
2201 -- API body
2202 IF (p_action = 'DELETE')
2203 THEN
2204 SELECT COUNT (*)
2205 INTO l_temp_count
2206 FROM cn_quotas_v
2207 WHERE quota_id = p_plan_element.quota_id;
2208
2209 IF l_temp_count = 0
2210 THEN
2211 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2212 THEN
2213 fnd_message.set_name ('CN', 'CN_INVALID_DEL_REC');
2214 fnd_msg_pub.ADD;
2215 END IF;
2216
2217 RAISE fnd_api.g_exc_error;
2218 END IF;
2219
2220 -- check whether the plan element is already assigned to a complan
2221 BEGIN
2222 SELECT 1
2223 INTO l_temp_count
2224 FROM SYS.DUAL
2225 WHERE NOT EXISTS (SELECT 1
2226 FROM cn_quota_assigns
2227 WHERE quota_id = p_plan_element.quota_id);
2228 EXCEPTION
2229 WHEN NO_DATA_FOUND
2230 THEN
2231 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2232 THEN
2233 fnd_message.set_name ('CN', 'PLN_QUOTA_DELETE_NA');
2234 fnd_msg_pub.ADD;
2235 END IF;
2236 RAISE fnd_api.g_exc_error;
2237 END;
2238 ELSE
2239 -- check whether user has access to this org
2240 IF (p_action = 'UPDATE')
2241 THEN
2242 -- better check that org_id first or you will cry
2243 IF NOT is_valid_org (p_plan_element.org_id, p_plan_element.quota_id)
2244 THEN
2245 RAISE fnd_api.g_exc_error;
2246 END IF;
2247
2248 -- 1. check object version number
2249 IF p_old_plan_element.object_version_number <> p_plan_element.object_version_number
2250 THEN
2251 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2252 THEN
2253 fnd_message.set_name ('CN', 'CN_RECORD_CHANGED');
2254 fnd_msg_pub.ADD;
2255 END IF;
2256
2257 RAISE fnd_api.g_exc_error;
2258 END IF;
2259
2260 -- 2. plan element name must be unique
2261 SELECT COUNT (1)
2262 INTO l_temp_count
2263 FROM cn_quotas_all pe
2264 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';
2265
2266 IF l_temp_count <> 0
2267 THEN
2268 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2269 THEN
2270 fnd_message.set_name ('CN', 'CN_INPUT_MUST_UNIQUE');
2271 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('PE_NAME', 'INPUT_TOKEN'));
2272 fnd_msg_pub.ADD;
2273 END IF;
2274
2275 RAISE fnd_api.g_exc_error;
2276 END IF;
2277
2278 --Added by hanaraya for bug 6505174
2279 -- Check for date range overlap between plan element and quota rule uplifts
2280
2281 OPEN uplift_curs(p_plan_element.quota_id, p_plan_element.start_date, p_plan_element.end_date);
2282
2283 FETCH uplift_curs
2284 INTO l_uplift_dt_range;
2285
2286 CLOSE uplift_curs;
2287
2288 IF l_uplift_dt_range > 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_UPLIFT_DT_NOT_WIN_QUOTA');
2293 fnd_msg_pub.ADD;
2294 END IF;
2295 RAISE fnd_api.g_exc_error;
2296 END IF;
2297
2298 ELSIF (p_action = 'CREATE')
2299 THEN
2300 -- better check that org_id first or you will cry
2301 IF NOT is_valid_org (p_plan_element.org_id)
2302 THEN
2303 RAISE fnd_api.g_exc_error;
2304 END IF;
2305
2306 -- 2. plan element name must be unique
2307 SELECT COUNT (1)
2308 INTO l_temp_count
2309 FROM cn_quotas_all pe
2310 WHERE NAME = p_plan_element.NAME AND org_id = p_plan_element.org_id AND delete_flag = 'N';
2311
2312 IF l_temp_count <> 0
2313 THEN
2314 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2315 THEN
2316 fnd_message.set_name ('CN', 'CN_INPUT_MUST_UNIQUE');
2317 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('PE_NAME', 'INPUT_TOKEN'));
2318 fnd_msg_pub.ADD;
2319 END IF;
2320
2321 RAISE fnd_api.g_exc_error;
2322 END IF;
2323 END IF;
2324
2325 --###########################################################################
2326 --## VALIDATION FOR BOTH UPDATE AND CREATE
2327 --###########################################################################
2328
2329 -- 1. name can not be null
2330 IF (p_plan_element.NAME IS NULL) OR (p_plan_element.NAME = fnd_api.g_miss_char)
2331 THEN
2332 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2333 THEN
2334 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
2335 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('PE_NAME', 'INPUT_TOKEN'));
2336 fnd_msg_pub.ADD;
2337 END IF;
2338
2339 RAISE fnd_api.g_exc_error;
2340 END IF;
2341
2342 -- start date is not null
2343 IF (p_plan_element.start_date IS NULL) OR (p_plan_element.start_date = fnd_api.g_miss_date)
2344 THEN
2345 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2346 THEN
2347 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
2348 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('SD', 'INPUT_TOKEN'));
2349 fnd_msg_pub.ADD;
2350 END IF;
2351
2352 RAISE fnd_api.g_exc_error;
2353 END IF;
2354
2355 -- start date > end date
2356 IF (p_plan_element.end_date IS NOT NULL) AND (p_plan_element.start_date > p_plan_element.end_date)
2357 THEN
2358 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2359 THEN
2360 fnd_message.set_name ('CN', 'CN_DATE_RANGE_ERROR');
2361 fnd_msg_pub.ADD;
2362 END IF;
2363
2364 RAISE fnd_api.g_exc_error;
2365 END IF;
2366
2367
2368
2369 ----------------------------------------------------
2370 -- Validate All lookup codes, must have valid value
2371 ----------------------------------------------------
2372 validate_types (p_plan_element => p_plan_element, x_return_status => x_return_status);
2373
2374
2375
2376 IF (p_plan_element.target 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.payment_amount IS NULL)
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_INPUT_CANT_NULL');
2393 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('VARIABLE(S)', '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.performance_goal IS NULL)
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_INPUT_CANT_NULL');
2405 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('VARIABLE(S)', 'PE_OBJECT_TYPE'));
2406 fnd_msg_pub.ADD;
2407 END IF;
2408
2409 RAISE fnd_api.g_exc_error;
2410 END IF;
2411
2412 IF (p_plan_element.payee_assign_flag NOT IN ('Y', 'N'))
2413 THEN
2414 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2415 THEN
2416 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
2417 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('PAYEE_ASSIGN', 'PE_OBJECT_TYPE'));
2418 fnd_msg_pub.ADD;
2419 END IF;
2420
2421 RAISE fnd_api.g_exc_error;
2422 END IF;
2423
2424 IF p_plan_element.addup_from_rev_class_flag NOT IN ('Y', 'N')
2425 THEN
2426 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2427 THEN
2428 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
2429 fnd_message.set_token ('OBJ_NAME', cn_api.get_lkup_meaning ('ADD_FROM_REVCLASS', 'PE_OBJECT_TYPE'));
2430 fnd_msg_pub.ADD;
2431 END IF;
2432
2433 RAISE fnd_api.g_exc_error;
2434 END IF;
2435
2436
2437 ---changes made for fixing the bug # 2739896
2438 IF p_plan_element.payee_assign_flag = 'Y'
2439 THEN
2440 SELECT COUNT (*)
2441 INTO l_payeechk
2442 FROM cn_quota_assigns cqa
2443 WHERE cqa.quota_id = p_plan_element.quota_id
2444 AND EXISTS (SELECT 1
2445 FROM cn_srp_plan_assigns cspa
2446 WHERE cspa.comp_plan_id = cqa.comp_plan_id AND EXISTS (SELECT 1
2447 FROM cn_srp_roles csr
2448 WHERE csr.salesrep_id = cspa.salesrep_id AND csr.role_id = 54));
2449
2450 IF (l_payeechk > 0)
2451 THEN
2452 fnd_message.set_name ('CN', 'CN_PAYEE_ASGN_FLAG_CHECK');
2453 fnd_msg_pub.ADD;
2454 RAISE fnd_api.g_exc_error;
2455 END IF;
2456 END IF;
2457 END IF; -- END OF DELETE VALIDATION
2458
2459 -- End of API body.
2460 <<end_api_body>>
2461 -- Standard call to get message count and if count is 1, get message info.
2462 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2463 EXCEPTION
2464 WHEN fnd_api.g_exc_error
2465 THEN
2466 ROLLBACK TO validate_plan_element;
2467 x_return_status := fnd_api.g_ret_sts_error;
2468 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2469 WHEN fnd_api.g_exc_unexpected_error
2470 THEN
2471 ROLLBACK TO validate_plan_element;
2472 x_return_status := fnd_api.g_ret_sts_unexp_error;
2473 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2474 WHEN OTHERS
2475 THEN
2476 ROLLBACK TO validate_plan_element;
2477 x_return_status := fnd_api.g_ret_sts_unexp_error;
2478
2479 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2480 THEN
2481 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2482 END IF;
2483
2484 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2485 END validate_plan_element;
2486
2487 -- Start of comments
2488 -- API name : Duplicate_Plan_Element
2489 -- Type : Private.
2490 -- Function :
2491 -- Pre-reqs : None.
2492 -- Parameters :
2493 PROCEDURE duplicate_plan_element (
2494 p_api_version IN NUMBER := cn_api.g_miss_num,
2495 p_init_msg_list IN VARCHAR2 := cn_api.g_false,
2496 p_commit IN VARCHAR2 := cn_api.g_false,
2497 p_validation_level IN NUMBER := cn_api.g_valid_level_full,
2498 p_quota_id IN cn_quotas.quota_id%TYPE := NULL,
2499 x_plan_element OUT NOCOPY plan_element_rec_type,
2500 x_return_status OUT NOCOPY VARCHAR2,
2501 x_msg_count OUT NOCOPY NUMBER,
2502 x_msg_data OUT NOCOPY VARCHAR2,
2503 x_loading_status OUT NOCOPY VARCHAR2
2504 )
2505 IS
2506 BEGIN
2507 NULL;
2508 END duplicate_plan_element;
2509
2510 PROCEDURE check_rate_dim (
2511 p_quota_id IN NUMBER
2512 )
2513 IS
2514 l_api_name CONSTANT VARCHAR2 (30) := 'check_rate_dim';
2515 l_same_pe NUMBER;
2516
2517 CURSOR c_rate_schedule_csr (
2518 pe_quota_id cn_quotas.quota_id%TYPE
2519 )
2520 IS
2521 SELECT qa.rate_schedule_id
2522 FROM cn_rt_quota_asgns qa
2523 WHERE qa.quota_id = pe_quota_id;
2524
2525 CURSOR c_rt_formula_csr (
2526 pe_quota_id cn_quotas.quota_id%TYPE,
2527 pe_rate_schedule_id cn_rt_quota_asgns.rate_schedule_id%TYPE
2528 )
2529 IS
2530 SELECT rtq.calc_formula_id
2531 FROM cn_rt_quota_asgns rtq
2532 WHERE rtq.quota_id = pe_quota_id AND rtq.rate_schedule_id = pe_rate_schedule_id;
2533
2534 CURSOR c_formula_input_csr (
2535 pe_calc_formula_id cn_formula_inputs.calc_formula_id%TYPE
2536 )
2537 IS
2538 SELECT fi.formula_input_id
2539 FROM cn_formula_inputs fi
2540 WHERE fi.calc_formula_id = pe_calc_formula_id;
2541
2542 l_cumulative_flag cn_formula_inputs.cumulative_flag%TYPE;
2543 l_split_flag cn_formula_inputs.split_flag%TYPE;
2544 l_rate_dim_sequence cn_formula_inputs.rate_dim_sequence%TYPE;
2545 l_dim_unit_code cn_rate_dimensions.dim_unit_code%TYPE;
2546 l_quota_name cn_quotas.NAME%TYPE;
2547 BEGIN
2548 -- Initialize API return status to success
2549 FOR l_rate_schedule_id IN c_rate_schedule_csr (p_quota_id)
2550 LOOP
2551 FOR l_calc_formula_id IN c_rt_formula_csr (p_quota_id, l_rate_schedule_id.rate_schedule_id)
2552 LOOP
2553 FOR l_formula_input_id IN c_formula_input_csr (l_calc_formula_id.calc_formula_id)
2554 LOOP
2555 SELECT cumulative_flag,
2556 split_flag,
2557 rate_dim_sequence
2558 INTO l_cumulative_flag,
2559 l_split_flag,
2560 l_rate_dim_sequence
2561 FROM cn_formula_inputs
2562 WHERE formula_input_id = l_formula_input_id.formula_input_id;
2563
2564 IF (l_cumulative_flag = 'Y') OR (l_split_flag = 'Y')
2565 THEN
2566 SELECT cd.dim_unit_code
2567 INTO l_dim_unit_code
2568 FROM cn_rate_dimensions cd,
2569 cn_rate_sch_dims cs
2570 WHERE cs.rate_dim_sequence = l_rate_dim_sequence
2571 AND cs.rate_schedule_id = l_rate_schedule_id.rate_schedule_id
2572 AND cd.rate_dimension_id = cs.rate_dimension_id;
2573
2574 -- clku bug 2426405
2575 IF (l_dim_unit_code <> 'PERCENT') AND (l_dim_unit_code <> 'AMOUNT') AND (l_dim_unit_code <> 'EXPRESSION')
2576 THEN
2577 SELECT NAME
2578 INTO l_quota_name
2579 FROM cn_quotas
2580 WHERE quota_id = p_quota_id;
2581
2582 cn_message_pkg.set_message (appl_short_name => 'CN',
2583 message_name => 'CN_RATE_DIM_MUST_NUMERIC',
2584 token_name1 => 'QUOTA_NAME',
2585 token_value1 => l_quota_name,
2586 token_name2 => NULL,
2587 token_value2 => NULL,
2588 token_name3 => NULL,
2589 token_value3 => NULL,
2590 token_name4 => NULL,
2591 token_value4 => NULL,
2592 TRANSLATE => TRUE
2593 );
2594 fnd_msg_pub.ADD;
2595 END IF;
2596 END IF;
2597 END LOOP;
2598 END LOOP;
2599 END LOOP;
2600 END check_rate_dim;
2601
2602 -- Check that the plan element is valid
2603 PROCEDURE validate_plan_element (
2604 p_api_version IN NUMBER,
2605 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2606 p_commit IN VARCHAR2 := fnd_api.g_false,
2607 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2608 p_comp_plan_id IN NUMBER := NULL,
2609 p_quota_id IN NUMBER,
2610 x_status_code OUT NOCOPY VARCHAR2,
2611 x_return_status OUT NOCOPY VARCHAR2,
2612 x_msg_count OUT NOCOPY NUMBER,
2613 x_msg_data OUT NOCOPY VARCHAR2
2614 )
2615 IS
2616 CURSOR rt_quota_asgns_curs
2617 IS
2618 SELECT rqa.rate_schedule_id,
2619 rs.NAME
2620 FROM cn_rt_quota_asgns rqa,
2621 cn_rate_schedules rs
2622 WHERE rqa.quota_id = p_quota_id AND rqa.rate_schedule_id = rs.rate_schedule_id;
2623
2624 CURSOR rules
2625 IS
2626 SELECT qr.quota_rule_id,
2627 qr.revenue_class_id,
2628 rc.NAME rev_class_name,
2629 q.quota_type_code
2630 FROM cn_quotas q,
2631 cn_quota_rules_all qr,
2632 cn_revenue_classes_all rc
2633 WHERE qr.quota_id = p_quota_id
2634 AND qr.revenue_class_id = rc.revenue_class_id
2635 AND q.quota_id = qr.quota_id
2636 AND q.quota_type_code IN ('FORMULA', 'EXTERNAL');
2637
2638 CURSOR factors (
2639 p_quota_rule_id NUMBER
2640 )
2641 IS
2642 SELECT event_factor,
2643 trx_type
2644 FROM cn_trx_factors
2645 WHERE quota_rule_id = p_quota_rule_id;
2646
2647 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_Plan_Element';
2648 l_api_version CONSTANT NUMBER := 1.0;
2649 factor_rec factors%ROWTYPE;
2650 key_factor_total NUMBER := 0;
2651 rule_rec rules%ROWTYPE;
2652 recinfo rt_quota_asgns_curs%ROWTYPE;
2653 x_formula_name cn_calc_formulas.NAME%TYPE;
2654 x_calc_formula_id cn_calc_formulas.calc_formula_id%TYPE;
2655 l_tmp NUMBER;
2656 l_plan_name cn_comp_plans.NAME%TYPE;
2657 l_plan_element plan_element_rec_type;
2658 g_incomplete VARCHAR2 (30) := 'INCOMPLETE';
2659 g_complete VARCHAR2 (30) := 'COMPLETE';
2660 l_temp_status_code VARCHAR2 (30) := g_complete;
2661 BEGIN
2662 -- Standard Start of API savepoint
2663 SAVEPOINT validate_plan_element_2;
2664
2665 -- Standard call to check for call compatibility.
2666 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
2667 THEN
2668 RAISE fnd_api.g_exc_unexpected_error;
2669 END IF;
2670
2671 -- Initialize message list if p_init_msg_list is set to TRUE.
2672 IF fnd_api.to_boolean (p_init_msg_list)
2673 THEN
2674 fnd_msg_pub.initialize;
2675 END IF;
2676
2677 -- Initialize API return status to success
2678 x_return_status := fnd_api.g_ret_sts_success;
2679 -- fill the rec
2680 l_plan_element := get_plan_element (p_quota_id);
2681 -- validate the plan element
2682 validate_plan_element (p_api_version => p_api_version,
2683 p_plan_element => l_plan_element,
2684 p_old_plan_element => l_plan_element,
2685 p_action => 'UPDATE',
2686 x_return_status => x_return_status,
2687 x_msg_count => x_msg_count,
2688 x_msg_data => x_msg_data
2689 );
2690
2691 -- in case of error, raise exception
2692 IF (x_return_status <> fnd_api.g_ret_sts_success)
2693 THEN
2694 l_temp_status_code := g_incomplete;
2695 RAISE fnd_api.g_exc_error;
2696 END IF;
2697
2698 IF p_comp_plan_id IS NOT NULL
2699 THEN
2700 SELECT NAME
2701 INTO l_plan_name
2702 FROM cn_comp_plans
2703 WHERE comp_plan_id = p_comp_plan_id;
2704 END IF;
2705
2706 SELECT cf.NAME,
2707 q.calc_formula_id
2708 INTO x_formula_name,
2709 x_calc_formula_id
2710 FROM cn_quotas q,
2711 cn_calc_formulas cf
2712 WHERE q.quota_id = p_quota_id AND q.calc_formula_id = cf.calc_formula_id(+) AND q.quota_type_code IN ('EXTERNAL', 'FORMULA');
2713
2714 IF l_plan_element.quota_type_code IN ('FORMULA', 'EXTERNAL')
2715 THEN
2716 IF l_plan_element.quota_type_code = 'FORMULA'
2717 THEN
2718 check_rate_dim (p_quota_id);
2719 END IF;
2720
2721 IF l_plan_element.calc_formula_id IS NULL AND l_plan_element.quota_type_code = 'FORMULA'
2722 THEN
2723 l_temp_status_code := g_incomplete;
2724 set_message (p_plan_name => l_plan_name,
2725 p_pe_name => l_plan_element.NAME,
2726 message_name => 'PLN_QUOTA_NO_FORMULA',
2727 token_name => NULL,
2728 token_value => NULL
2729 );
2730 ELSIF l_plan_element.package_name IS NULL AND l_plan_element.quota_type_code = 'EXTERNAL'
2731 THEN
2732 l_temp_status_code := g_incomplete;
2733 set_message (p_plan_name => l_plan_name,
2734 p_pe_name => l_plan_element.NAME,
2735 message_name => 'PLN_QUOTA_NO_PACKAGE',
2736 token_name => NULL,
2737 token_value => NULL
2738 );
2739 END IF;
2740
2741 -- Check Schedule exists.
2742 IF l_plan_element.quota_type_code IN ('FORMULA', 'EXTERNAL')
2743 THEN
2744 SELECT COUNT (1)
2745 INTO l_tmp
2746 FROM cn_rt_quota_asgns
2747 WHERE quota_id = p_quota_id;
2748
2749 IF (l_tmp = 0)
2750 THEN
2751 l_temp_status_code := g_incomplete;
2752 set_message (p_plan_name => l_plan_name,
2753 p_pe_name => l_plan_element.NAME,
2754 message_name => 'PLN_QUOTA_NO_SCHEDULE',
2755 token_name => NULL,
2756 token_value => NULL
2757 );
2758 END IF;
2759 END IF;
2760
2761 IF l_temp_status_code = g_complete AND l_plan_element.incentive_type_code <> 'BONUS'
2762 THEN
2763 OPEN rules;
2764
2765 LOOP
2766 FETCH rules
2767 INTO rule_rec;
2768
2769
2770 -- Need to distinguish between no rows and the all rows found
2771 IF rules%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_NO_RULES',
2777 token_name => NULL,
2778 token_value => NULL
2779 );
2780 EXIT; -- exit loop
2781 ELSE
2782 IF rules%NOTFOUND
2783 THEN
2784 EXIT;
2785 ELSE
2786 IF l_temp_status_code = g_complete
2787 THEN
2788 key_factor_total := 0;
2789 OPEN factors (rule_rec.quota_rule_id);
2790
2791 LOOP
2792 FETCH factors
2793 INTO factor_rec;
2794
2795 IF factors%ROWCOUNT = 0
2796 THEN
2797 l_temp_status_code := g_incomplete;
2798 set_message (p_plan_name => l_plan_name,
2799 p_pe_name => l_plan_element.NAME,
2800 message_name => 'PLN_QUOTA_RULE_NO_FACTORS',
2801 token_name => 'REV_CLASS_NAME',
2802 token_value => rule_rec.rev_class_name
2803 );
2804 EXIT; -- exit loop
2805 ELSE
2806 IF factors%NOTFOUND
2807 THEN
2808 IF key_factor_total <> 100
2809 THEN
2810 l_temp_status_code := g_incomplete;
2811 set_message (p_plan_name => l_plan_name,
2812 p_pe_name => l_plan_element.NAME,
2813 message_name => 'PLN_QUOTA_RULE_FACTORS_NOT_100',
2814 token_name => 'REV_CLASS_NAME',
2815 token_value => rule_rec.rev_class_name
2816 );
2817 END IF;
2818
2819 EXIT;
2820 ELSE
2821 IF (factor_rec.trx_type = 'ORD' OR factor_rec.trx_type = 'INV' OR factor_rec.trx_type = 'PMT')
2822 THEN
2823 key_factor_total := key_factor_total + factor_rec.event_factor;
2824 END IF;
2825 END IF;
2826 END IF;
2827 END LOOP;
2828
2829 CLOSE factors;
2830 END IF;
2831 END IF; -- sqlnotfound
2832 END IF; -- rowcount
2833 END LOOP;
2834
2835 CLOSE rules;
2836 END IF;
2837 END IF;
2838
2839 -- pass the status back to the calling program.
2840 -- all problems will be written to a table for review
2841 -- we just need to tell the comp plan that it is invalid
2842 x_status_code := l_temp_status_code;
2843
2844 -- End of API body.
2845 -- Standard check of p_commit.
2846 IF fnd_api.to_boolean (p_commit)
2847 THEN
2848 COMMIT WORK;
2849 END IF;
2850
2851 -- Standard call to get message count and if count is 1, get message info.
2852 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2853 EXCEPTION
2854 WHEN fnd_api.g_exc_error
2855 THEN
2856 ROLLBACK TO validate_plan_element_2;
2857 x_return_status := fnd_api.g_ret_sts_error;
2858 x_status_code := g_incomplete;
2859 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2860 WHEN fnd_api.g_exc_unexpected_error
2861 THEN
2862 ROLLBACK TO validate_plan_element_2;
2863 x_return_status := fnd_api.g_ret_sts_unexp_error;
2864 x_status_code := g_incomplete;
2865 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2866 WHEN OTHERS
2867 THEN
2868 ROLLBACK TO validate_plan_element_2;
2869 x_return_status := fnd_api.g_ret_sts_unexp_error;
2870 x_status_code := g_incomplete;
2871
2872 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2873 THEN
2874 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2875 END IF;
2876
2877 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2878 END validate_plan_element;
2879 END cn_plan_element_pvt;