[Home] [Help]
PACKAGE BODY: APPS.CN_CHK_PLAN_ELEMENT_PKG
Source
1 PACKAGE BODY CN_CHK_PLAN_ELEMENT_PKG AS
2 /* $Header: cnchkpeb.pls 120.5 2005/10/17 05:30:54 chanthon ship $ */
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CN_CHK_PLAN_ELEMENT_PKG';
4 g_file_name CONSTANT VARCHAR2 (12) := 'cnchkpeb.pls';
5
6 -- ----------------------------------------------------------------------------+
7 -- Procedure: valid_rate_table
8 -- Desc : Valid input for Rate Table
9 -- ----------------------------------------------------------------------------+
10 PROCEDURE valid_rate_table (
11 x_return_status OUT NOCOPY VARCHAR2,
12 p_pe_rec IN pe_rec_type := g_miss_pe_rec,
13 p_loading_status IN VARCHAR2,
14 x_loading_status OUT NOCOPY VARCHAR2
15 )
16 IS
17 l_api_name CONSTANT VARCHAR2 (30) := 'valid_rate_table';
18 l_tmp NUMBER := 0;
19 -- l_tier_unit_code cn_rate_schedules.tier_unit_code%TYPE
20 -- := FND_API.G_MISS_CHAR;
21 l_comm_unit_code cn_rate_schedules.commission_unit_code%TYPE := fnd_api.g_miss_char;
22 BEGIN
23 /*
24 x_return_status := FND_API.G_RET_STS_SUCCESS;
25 x_loading_status := p_loading_status;
26
27 -- Rate Table CAN NOT NULL if plan element type is not 'DRAW or MANUAL'
28 IF (p_pe_rec.quota_type_code IN
29 ('TARGET','REVENUE','UNIT_BASED_QUOTA','UNIT_BASED_NON_QUOTA',
30 'DISCOUNT','MARGIN') ) THEN
31 IF (p_pe_rec.rate_table_id IS NULL) THEN
32 -- Rasie error when user Pass in Rate table Name = NULL
33 IF (cn_api.pe_char_field_cannot_null
34 ( p_char_field => p_pe_rec.rate_table_name,
35 p_pe_type => p_pe_rec.quota_type_code,
36 p_obj_name => G_RATE_TB,
37 p_token1 => NULL ,
38 p_token2 => NULL ,
39 p_token3 => NULL ,
40 p_loading_status => x_loading_status,
41 x_loading_status => x_loading_status) = FND_API.G_FALSE) THEN
42 RAISE FND_API.G_EXC_ERROR ;
43 END IF;
44 -- Rasie error when user missing Pass in Rate table Name
45 IF ( (cn_api.chk_miss_char_para
46 ( p_char_para => p_pe_rec.rate_table_name,
47 p_para_name => G_RATE_TB,
48 p_loading_status => x_loading_status,
49 x_loading_status => x_loading_status)) = FND_API.G_TRUE) THEN
50 RAISE FND_API.G_EXC_ERROR ;
51 END IF;
52 -- Rasie error when user pass in rate table name not exist in
53 -- cn_rate_schedules
54 IF (CN_API.get_rate_table_id(p_pe_rec.rate_table_name)) IS NULL THEN
55 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
56 THEN
57 FND_MESSAGE.SET_NAME ('CN' , 'CN_RATE_SCH_NOT_EXIST');
58 FND_MSG_PUB.Add;
59 END IF;
60 x_loading_status := 'CN_RATE_SCH_NOT_EXIST';
61 RAISE FND_API.G_EXC_ERROR ;
62 END IF ;
63 ELSIF p_pe_rec.rate_table_name IS NULL THEN
64 -- Rate_table_id not null and rate_table_name null, check if
65 -- Rate_table_id is exist in DB
66 IF (CN_API.get_rate_table_name(p_pe_rec.rate_table_id)) IS NULL THEN
67 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
68 THEN
69 FND_MESSAGE.SET_NAME ('CN' , 'CN_RATE_SCH_NOT_EXIST');
70 FND_MSG_PUB.Add;
71 END IF;
72 x_loading_status := 'CN_RATE_SCH_NOT_EXIST';
73 RAISE FND_API.G_EXC_ERROR ;
74 END IF ;
75 ELSE
76 -- If rate_table_id and rate_table_name both are not missing or null,
77 -- make sure they're compatible, access to same record in
78 -- CN_RATE_SCHEDULES
79 IF(p_pe_rec.rate_table_id <>
80 CN_API.get_rate_table_id(p_pe_rec.rate_table_name)) THEN
81 -- Error, check the msg level and add an error message to the
82 -- API message list
83 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
84 THEN
85 FND_MESSAGE.SET_NAME ('CN' , 'CN_VALUE_ID_ERROR');
86 FND_MESSAGE.SET_TOKEN('VALUE_NAME', G_RATE_TB || ' : '
87 || p_pe_rec.rate_table_name);
88 FND_MESSAGE.SET_TOKEN('ID_NAME',G_RATE_TB_ID || ' : ' ||
89 p_pe_rec.rate_table_id);
90 FND_MSG_PUB.Add;
91 END IF;
92 x_loading_status := 'CN_VALUE_ID_ERROR';
93 RAISE FND_API.G_EXC_ERROR ;
94 END IF;
95 END IF;
96 END IF ; -- end quota_type_code
97 --+
98 -- check tier/commission unit code
99 --+
100 SELECT tier_unit_code, commission_unit_code
101 INTO l_tier_unit_code, l_comm_unit_code
102 FROM cn_rate_schedules rs
103 WHERE rs.rate_schedule_id = p_pe_rec.rate_table_id;
104 -- check tier unit code
105 IF p_pe_rec.quota_type_code IN ('TARGET','UNIT_BASED_QUOTA') THEN
106 IF l_tier_unit_code <> 'PERCENT' THEN
107 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
108 THEN
109 FND_MESSAGE.SET_NAME ('CN' , 'PLN_QUOTA_SCHED_INCOMPAT_TP');
110 FND_MESSAGE.SET_TOKEN ('PLAN_NAME',NULL);
111 FND_MESSAGE.SET_TOKEN ('QUOTA_NAME',p_pe_rec.name);
112 FND_MSG_PUB.Add;
113 END IF;
114 x_loading_status := 'PLN_QUOTA_SCHED_INCOMPAT_TP';
115 RAISE FND_API.G_EXC_ERROR ;
116 END IF;
117 ELSIF p_pe_rec.quota_type_code IN ('REVENUE','UNIT_BASED_NON_QUOTA') THEN
118 IF l_tier_unit_code <> 'AMOUNT' THEN
119 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
120 THEN
121 FND_MESSAGE.SET_NAME ('CN' , 'PLN_QUOTA_SCHED_INCOMPAT_RA');
122 FND_MESSAGE.SET_TOKEN ('PLAN_NAME',NULL);
123 FND_MESSAGE.SET_TOKEN ('QUOTA_NAME',p_pe_rec.name);
124 FND_MSG_PUB.Add;
125 END IF;
126 x_loading_status := 'PLN_QUOTA_SCHED_INCOMPAT_RA';
127 RAISE FND_API.G_EXC_ERROR ;
128 END IF;
129 ELSIF p_pe_rec.quota_type_code IN ('DISCOUNT','MARGIN') THEN
130 IF l_tier_unit_code <> 'PERCENT' THEN
131 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
132 THEN
133 FND_MESSAGE.SET_NAME ('CN' , 'PLN_QUOTA_SCHED_INCOMPAT_DP');
134 FND_MESSAGE.SET_TOKEN ('PLAN_NAME',NULL);
135 FND_MESSAGE.SET_TOKEN ('QUOTA_NAME',p_pe_rec.name);
136 FND_MSG_PUB.Add;
137 END IF;
138 x_loading_status := 'PLN_QUOTA_SCHED_INCOMPAT_DP';
139 RAISE FND_API.G_EXC_ERROR ;
140 END IF;
141 END IF;
142 -- check commission unit code
143 IF p_pe_rec.payment_type_code IN ('PAYMENT','TRANSACTION') THEN
144 IF l_comm_unit_code <> 'PERCENT' THEN
145 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
146 THEN
147 FND_MESSAGE.SET_NAME ('CN' , 'PLN_QUOTA_SCHED_INCOMPAT_PP');
148 FND_MESSAGE.SET_TOKEN ('PLAN_NAME',NULL);
149 FND_MESSAGE.SET_TOKEN ('QUOTA_NAME',p_pe_rec.name);
150 FND_MSG_PUB.Add;
151 END IF;
152 x_loading_status := 'PLN_QUOTA_SCHED_INCOMPAT_PP';
153 RAISE FND_API.G_EXC_ERROR ;
154 END IF;
155 ELSIF p_pe_rec.payment_type_code = 'FIXED' THEN
156 IF l_comm_unit_code <> 'AMOUNT' THEN
157 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
158 THEN
159 FND_MESSAGE.SET_NAME ('CN' , 'PLN_QUOTA_SCHED_INCOMPAT_FA');
160 FND_MESSAGE.SET_TOKEN('PLAN_NAME',NULL);
161 FND_MESSAGE.SET_TOKEN('QUOTA_NAME',p_pe_rec.name);
162 FND_MSG_PUB.Add;
163 END IF;
164 x_loading_status := 'PLN_QUOTA_SCHED_INCOMPAT_FA';
165 RAISE FND_API.G_EXC_ERROR ;
166 END IF;
167 END IF ;
168 -- Check if rate table doesn't have any rate tiers
169 SELECT COUNT(*)
170 INTO l_tmp
171 FROM cn_rate_tiers rt
172 WHERE rt.rate_schedule_id = p_pe_rec.rate_table_id
173 ;
174 IF l_tmp = 0 THEN
175 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
176 THEN
177 FND_MESSAGE.SET_NAME ('CN' , 'PLN_SCHEDULE_NO_TIERS');
178 FND_MESSAGE.SET_TOKEN('PLAN_NAME',NULL);
179 FND_MESSAGE.SET_TOKEN('QUOTA_NAME',p_pe_rec.name);
180 FND_MESSAGE.SET_TOKEN('SCHEDULE_NAME',p_pe_rec.rate_table_name);
181 FND_MSG_PUB.Add;
182 END IF;
183 x_loading_status := 'PLN_SCHEDULE_NO_TIERS';
184 RAISE FND_API.G_EXC_ERROR ;
185 END IF;
186
187 EXCEPTION
188 WHEN FND_API.G_EXC_ERROR THEN
189 x_return_status := FND_API.G_RET_STS_ERROR ;
190
191 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
193 x_loading_status := 'UNEXPECTED_ERR';
194
195 WHEN OTHERS THEN
196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
197 x_loading_status := 'UNEXPECTED_ERR';
198 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
199 THEN
200 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
201 END IF;
202 */
203 fnd_message.set_name ('CN', 'CN_PACKAGE_OBSELETE');
204 fnd_msg_pub.ADD;
205 x_return_status := fnd_api.g_ret_sts_unexp_error;
206 x_loading_status := 'CN_PACKAGE_OBSELETE';
207 RAISE fnd_api.g_exc_error;
208 END valid_rate_table;
209
210 -- ----------------------------------------------------------------------------+
211 -- Procedure: valid_disc_rate_table
212 -- Desc : Valid input for Discount Rate Table
213 -- ----------------------------------------------------------------------------+
214 PROCEDURE valid_disc_rate_table (
215 x_return_status OUT NOCOPY VARCHAR2,
216 p_pe_rec IN pe_rec_type := g_miss_pe_rec,
217 p_loading_status IN VARCHAR2,
218 x_loading_status OUT NOCOPY VARCHAR2
219 )
220 IS
221 l_api_name CONSTANT VARCHAR2 (30) := 'valid_disc_rate_table';
222 l_tmp NUMBER := 0;
223 -- l_tier_unit_code cn_rate_schedules.tier_unit_code%TYPE
224 -- := FND_API.G_MISS_CHAR;
225 l_comm_unit_code cn_rate_schedules.commission_unit_code%TYPE := fnd_api.g_miss_char;
226 BEGIN
227 /* x_return_status := FND_API.G_RET_STS_SUCCESS;
228 x_loading_status := p_loading_status;
229
230 -- Disc Rate Table CAN NOT NULL if plan element type is Rev quota, Rev non
231 -- quota and discount option code = payment or quota
232 IF (p_pe_rec.quota_type_code IN ('TARGET','REVENUE') ) AND
233 (p_pe_rec.disc_option_code IN ('PAYMENT','QUOTA')) THEN
234 IF (p_pe_rec.disc_rate_table_id IS NULL) THEN
235 -- Rasie error when user Pass in Discount Rate table Name = NULL
236 IF (cn_api.pe_char_field_cannot_null
237 ( p_char_field => p_pe_rec.disc_rate_table_name,
238 p_pe_type => p_pe_rec.quota_type_code,
239 p_obj_name => G_DISC_RATE_TB,
240 p_token1 => G_DISC_OPTION ||' = '||
241 cn_api.get_lkup_meaning
242 (p_pe_rec.disc_option_code,'DISCOUNT_OPTION'),
243 p_token2 => NULL ,
244 p_token3 => NULL ,
245 p_loading_status => x_loading_status,
246 x_loading_status => x_loading_status) = FND_API.G_FALSE) THEN
247 RAISE FND_API.G_EXC_ERROR ;
248 END IF;
249 -- Rasie error when user missing Pass in Discount Rate table Name
250 IF ( (cn_api.chk_miss_char_para
251 ( p_char_para => p_pe_rec.disc_rate_table_name,
252 p_para_name => G_DISC_RATE_TB,
253 p_loading_status => x_loading_status,
254 x_loading_status => x_loading_status)) = FND_API.G_TRUE) THEN
255 RAISE FND_API.G_EXC_ERROR ;
256 END IF;
257 -- Rasie error when user pass in dicsount rate table name not exist in
258 -- cn_rate_schedules
259 IF (CN_API.get_rate_table_id(p_pe_rec.disc_rate_table_name)) IS NULL
260 THEN
261 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
262 THEN
263 FND_MESSAGE.SET_NAME ('CN' , 'PLN_QUOTA_DISC_SCHED_REQUIRED');
264 FND_MSG_PUB.Add;
265 END IF;
266 x_loading_status := 'PLN_QUOTA_DISC_SCHED_REQUIRED';
267 RAISE FND_API.G_EXC_ERROR ;
268 END IF ;
269 ELSIF p_pe_rec.disc_rate_table_name IS NULL THEN
270 -- Disc_Rate_table_id not null and disc_rate_table_name null, check if
271 -- Disc_rate_table_id is exist in DB
272 IF (CN_API.get_rate_table_name(p_pe_rec.disc_rate_table_id)) IS NULL
273 THEN
274 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
275 THEN
276 FND_MESSAGE.SET_NAME ('CN' ,'PLN_QUOTA_DISC_SCHED_REQUIRED');
277 FND_MSG_PUB.Add;
278 END IF;
279 x_loading_status := 'PLN_QUOTA_DISC_SCHED_REQUIRED';
280 RAISE FND_API.G_EXC_ERROR ;
281 END IF ;
282 ELSE
283 -- If disc_rate_table_id and disc_rate_table_name both are not
284 -- missing or null,
285 -- make sure they're compatible, access to same record in
286 -- CN_RATE_SCHEDULES
287 IF(p_pe_rec.disc_rate_table_id <>
288 CN_API.get_rate_table_id(p_pe_rec.disc_rate_table_name)) THEN
289 -- Error, check the msg level and add an error message to the
290 -- API message list
291 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
292 THEN
293 FND_MESSAGE.SET_NAME ('CN' , 'CN_VALUE_ID_ERROR');
294 FND_MESSAGE.SET_TOKEN('VALUE_NAME',G_DISC_RATE_TB ||' : '
295 ||p_pe_rec.disc_rate_table_name);
296 FND_MESSAGE.SET_TOKEN('ID_NAME',G_DISC_RATE_TB_ID || ' : '||
297 p_pe_rec.disc_rate_table_id);
298 FND_MSG_PUB.Add;
299 END IF;
300 x_loading_status := 'CN_VALUE_ID_ERROR';
301 RAISE FND_API.G_EXC_ERROR ;
302 END IF;
303 END IF;
304
305 -- Check discount rate table <> rate table
306 IF p_pe_rec.rate_table_id = p_pe_rec.disc_rate_table_id THEN
307 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
308 THEN
309 FND_MESSAGE.SET_NAME ('CN' , 'DISC_RATE_TABLE_SAME');
310 FND_MSG_PUB.Add;
311 END IF;
312 x_loading_status := 'DISC_RATE_TABLE_SAME';
313 RAISE FND_API.G_EXC_ERROR ;
314 END IF;
315
316 --+
317 -- check tier/commission unit code
318 --+
319 SELECT tier_unit_code, commission_unit_code
320 INTO l_tier_unit_code, l_comm_unit_code
321 FROM cn_rate_schedules rs
322 WHERE rs.rate_schedule_id = p_pe_rec.disc_rate_table_id;
323 IF l_tier_unit_code <> 'PERCENT' OR
324 l_comm_unit_code <> 'PERCENT' THEN
325 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
326 THEN
327 FND_MESSAGE.SET_NAME ('CN' , 'PLN_QUOTA_DISC_SCHED_NOT_PP');
328 FND_MESSAGE.SET_TOKEN('PLAN_NAME',NULL);
329 FND_MESSAGE.SET_TOKEN('QUOTA_NAME',p_pe_rec.name);
330 FND_MSG_PUB.Add;
331 END IF;
332 x_loading_status := 'PLN_QUOTA_DISC_SCHED_NOT_PP';
333 RAISE FND_API.G_EXC_ERROR ;
334 END IF;
335
336 -- Check if rate table doesn't have any rate tiers
337 SELECT COUNT(*)
338 INTO l_tmp
339 FROM cn_rate_tiers rt
340 WHERE rt.rate_schedule_id = p_pe_rec.disc_rate_table_id
341 ;
342 IF l_tmp = 0 THEN
343 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
344 THEN
345 FND_MESSAGE.SET_NAME ('CN' , 'PLN_SCHEDULE_NO_TIERS');
346 FND_MESSAGE.SET_TOKEN('PLAN_NAME',NULL);
347 FND_MESSAGE.SET_TOKEN('QUOTA_NAME',p_pe_rec.name);
348 FND_MESSAGE.set_token
349 ('SCHEDULE_NAME',p_pe_rec.disc_rate_table_name);
350 FND_MSG_PUB.Add;
351 END IF;
352 x_loading_status := 'PLN_SCHEDULE_NO_TIERS';
353 RAISE FND_API.G_EXC_ERROR ;
354 END IF;
355
356 END IF ; -- end quota_type_code
357
358 EXCEPTION
359 WHEN FND_API.G_EXC_ERROR THEN
360 x_return_status := FND_API.G_RET_STS_ERROR ;
361
362 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
364 x_loading_status := 'UNEXPECTED_ERR';
365
366 WHEN OTHERS THEN
367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
368 x_loading_status := 'UNEXPECTED_ERR';
369 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
370 THEN
371 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
372 END IF;
373 */
374 fnd_message.set_name ('CN', 'CN_PACKAGE_OBSELETE');
375 fnd_msg_pub.ADD;
376 x_return_status := fnd_api.g_ret_sts_unexp_error;
377 x_loading_status := 'CN_PACKAGE_OBSELETE';
378 RAISE fnd_api.g_exc_error;
379 END valid_disc_rate_table;
380
381 -- ----------------------------------------------------------------------------+
382 -- Procedure: validate_org_id
383 -- Desc : Valid input for Org ID
384 -- ----------------------------------------------------------------------------+
385 PROCEDURE validate_org_id (
386 org_id IN NUMBER
387 )
388 IS
389 l_api_name CONSTANT VARCHAR2 (30) := 'valid_revenue_class';
390 BEGIN
391 IF org_id IS NULL
392 THEN
393 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
394 THEN
395 fnd_message.set_name ('CN', 'CN_INPUT_CANT_NULL');
396 fnd_message.set_token ('INPUT_NAME', cn_api.get_lkup_meaning ('ORGANIZATION', 'PE_OBJECT_TYPE'));
397 fnd_msg_pub.ADD;
398 END IF;
399
400 RAISE fnd_api.g_exc_error;
401 END IF;
402 END validate_org_id;
403
404 -- ----------------------------------------------------------------------------+
405 -- Procedure: valid_revenue_class
406 -- Desc : Check input for Revenue Class
407 -- ----------------------------------------------------------------------------+
408 PROCEDURE valid_revenue_class (
409 x_return_status OUT NOCOPY VARCHAR2,
410 p_pe_rec IN pe_rec_type := g_miss_pe_rec,
411 p_revenue_class_id_old IN NUMBER := NULL,
412 p_loading_status IN VARCHAR2,
413 x_loading_status OUT NOCOPY VARCHAR2
414 )
415 IS
416 l_api_name CONSTANT VARCHAR2 (30) := 'valid_revenue_class';
417 l_loading_status VARCHAR2 (80);
418 BEGIN
419 -- Added the one more parameter to check the old revenue class
420 x_return_status := fnd_api.g_ret_sts_success;
421 x_loading_status := p_loading_status;
422
423 -- Revenue Class CAN NOT NULL if plan element type is COMMISSION
424 IF (p_pe_rec.incentive_type_code IN ('COMMISSION'))
425 THEN
426 IF (p_pe_rec.rev_class_id IS NULL)
427 THEN
428 -- Rasie error when user Pass in revenue class Name = NULL
429 IF (cn_api.pe_char_field_cannot_null (p_char_field => p_pe_rec.rev_class_name,
430 p_pe_type => p_pe_rec.quota_type_code,
431 p_obj_name => g_rev_cls_name,
432 p_token1 => NULL,
433 p_token2 => NULL,
434 p_token3 => NULL,
435 p_loading_status => x_loading_status,
436 x_loading_status => l_loading_status
437 ) = fnd_api.g_false
438 )
439 THEN
440 RAISE fnd_api.g_exc_error;
441 END IF;
442
443 -- Rasie error when user missing Pass in revenue class Name
444 IF ((cn_api.chk_miss_char_para (p_char_para => p_pe_rec.rev_class_name,
445 p_para_name => g_rev_cls_name,
446 p_loading_status => x_loading_status,
447 x_loading_status => l_loading_status
448 )
449 ) = fnd_api.g_true
450 )
451 THEN
452 RAISE fnd_api.g_exc_error;
453 END IF;
454
455 -- Rasie error when user pass in revenue class name not exist in
456 -- cn_revenue_classes
457 IF (cn_api.get_rev_class_id (p_pe_rec.rev_class_name, p_pe_rec.org_id)) IS NULL
458 THEN
459 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
460 THEN
461 fnd_message.set_name ('CN', 'CN_REV_CLASS_NOT_EXIST');
462 fnd_msg_pub.ADD;
463 END IF;
464
465 x_loading_status := 'CN_REV_CLASS_NOT_EXIST';
466 RAISE fnd_api.g_exc_error;
467 END IF;
468 ELSIF p_pe_rec.rev_class_name IS NULL
469 THEN
470 -- Rev_class_id not null and rev_class_name null, check if
471 -- Rev_class_id is exist in DB
472 IF (cn_api.get_rev_class_name (p_pe_rec.rev_class_id)) IS NULL
473 THEN
474 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
475 THEN
476 fnd_message.set_name ('CN', 'CN_REV_CLASS_NOT_EXIST');
477 fnd_msg_pub.ADD;
478 END IF;
479
480 x_loading_status := 'CN_REV_CLASS_NOT_EXIST';
481 RAISE fnd_api.g_exc_error;
482 END IF;
483 ELSE
484 -- If rev_class_id and rev_class_name both are not
485 -- missing or null,
486 -- make sure they're compatible, access to same record in
487 -- CN_RATE_SCHEDULES
488 IF (p_pe_rec.rev_class_id <> cn_api.get_rev_class_id (p_pe_rec.rev_class_name, p_pe_rec.org_id))
489 THEN
490 -- Error, check the msg level and add an error message to the
491 -- API message list
492 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
493 THEN
494 fnd_message.set_name ('CN', 'CN_VALUE_ID_ERROR');
495 fnd_message.set_token ('VALUE_NAME', g_rev_cls_name || ' : ' || p_pe_rec.rev_class_name);
496 fnd_message.set_token ('ID_NAME', g_rev_cls_id || ' : ' || p_pe_rec.rev_class_id);
497 fnd_msg_pub.ADD;
498 END IF;
499
500 x_loading_status := 'CN_VALUE_ID_ERROR';
501 RAISE fnd_api.g_exc_error;
502 END IF;
503 END IF;
504
505 --+
506 -- Validate Rule :
507 -- rev_class_target >= 0,
508 --+
509 IF p_pe_rec.rev_class_target < 0
510 THEN
511 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
512 THEN
513 fnd_message.set_name ('CN', 'CN_REV_TARGET_GT_ZERO');
514 fnd_msg_pub.ADD;
515 END IF;
516
517 x_loading_status := 'CN_REV_TARGET_GT_ZERO';
518 RAISE fnd_api.g_exc_error;
519 END IF;
520
521 --+
522 -- Validate Rule :
523 -- Checks if p_pe_rec.rev_class_id is a parent in a hierarchy
524 -- for any other p_pe_rec.rev_class_id already saved in the database
525 -- for the p_pe_rec.quota_id
526 IF (NOT (cn_quota_rules_pkg.check_rev_class_hier (x_revenue_class_id => p_pe_rec.rev_class_id,
527 x_revenue_class_id_old => p_revenue_class_id_old,
528 x_quota_id => p_pe_rec.quota_id,
529 x_start_period_id => NULL,
530 x_end_period_id => NULL
531 )
532 )
533 )
534 THEN
535 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
536 THEN
537 fnd_msg_pub.ADD;
538 END IF;
539
540 x_loading_status := 'REV_CLASS_HIER_CHECK';
541 RAISE fnd_api.g_exc_error;
542 END IF;
543 END IF; -- end quota_type_code
544 EXCEPTION
545 WHEN fnd_api.g_exc_error
546 THEN
547 x_return_status := fnd_api.g_ret_sts_error;
548 WHEN fnd_api.g_exc_unexpected_error
549 THEN
550 x_return_status := fnd_api.g_ret_sts_unexp_error;
551 x_loading_status := 'UNEXPECTED_ERR';
552 WHEN OTHERS
553 THEN
554 x_return_status := fnd_api.g_ret_sts_unexp_error;
555 x_loading_status := 'UNEXPECTED_ERR';
556
557 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
558 THEN
559 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
560 END IF;
561 END valid_revenue_class;
562
563 -- ----------------------------------------------------------------------------+
564 -- Procedure: chk_dr_man_pe
565 -- Desc : Check input for DRAW and MANUAL type plan element
566 -- ----------------------------------------------------------------------------+
567 PROCEDURE chk_dr_man_pe (
568 x_return_status OUT NOCOPY VARCHAR2,
569 p_pe_rec IN pe_rec_type := g_miss_pe_rec,
570 p_loading_status IN VARCHAR2,
571 x_loading_status OUT NOCOPY VARCHAR2
572 )
573 IS
574 l_api_name CONSTANT VARCHAR2 (30) := 'chk_dr_man_pe';
575 l_yes fnd_lookups.meaning%TYPE;
576 l_no fnd_lookups.meaning%TYPE;
577 BEGIN
578 /* x_return_status := FND_API.G_RET_STS_SUCCESS;
579 x_loading_status := p_loading_status;
580
581 SELECT meaning INTO l_yes FROM fnd_lookups
582 WHERE lookup_code = 'Y' AND lookup_type = 'YES_NO';
583 SELECT meaning INTO l_no FROM fnd_lookups
584 WHERE lookup_code = 'N' AND lookup_type = 'YES_NO';
585 --+
586 -- Validate Rule :
587 -- trx_group_code = NULL, payment_type_code=NULL, disc_option_code =NONE
588 -- cumulative flag = N, split_flag = N, itd_flag = N
589 -- Payment Amount =
590 -- NULL : if it's MANUAL type pe
591 -- NOT NULL : if it's DRAW type pe
592 -- Rate Table = NULL, Discount Rate Table = NULL, Revenue Class = NULL
593 --+
594 -- Check trx_group_code for DRAW and MANUAUL PE type
595 IF ( (cn_api.pe_char_field_must_null
596 ( p_char_field => p_pe_rec.trx_group_code,
597 p_pe_type => p_pe_rec.quota_type_code,
598 p_obj_name => G_TRX_GROUP,
599 p_token1 => NULL ,
600 p_token2 => NULL ,
601 p_token3 => NULL ,
602 p_loading_status => x_loading_status,
603 x_loading_status => x_loading_status)) = FND_API.G_FALSE) THEN
604 RAISE FND_API.G_EXC_ERROR ;
605 END IF;
606 -- Check payment_type_code for DRAW and MANUAUL PE type
607 IF ( (cn_api.pe_char_field_must_null
608 ( p_char_field => p_pe_rec.payment_type_code,
609 p_pe_type => p_pe_rec.quota_type_code,
610 p_obj_name => G_PAYMENT_TYPE,
611 p_token1 => NULL ,
612 p_token2 => NULL ,
613 p_token3 => NULL ,
614 p_loading_status => x_loading_status,
615 x_loading_status => x_loading_status)) = FND_API.G_FALSE) THEN
616 RAISE FND_API.G_EXC_ERROR ;
617 END IF;
618 -- Check disc_option_code for DRAW and MANUAUL PE type
619 IF (p_pe_rec.disc_option_code <> 'NONE') THEN
620 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
621 THEN
622 FND_MESSAGE.SET_NAME ('CN' , 'CN_DISC_OPTION_MUST_NONE');
623 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
624 cn_api.get_lkup_meaning
625 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
626 FND_MSG_PUB.Add;
627 END IF;
628 x_loading_status := 'CN_DISC_OPTION_MUST_NONE';
629 RAISE FND_API.G_EXC_ERROR ;
630 END IF;
631 -- Check cumulative_flag = N for DRAW and MANUAUL PE type
632 IF (p_pe_rec.cumulative_flag <> 'N') THEN
633 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
634 THEN
635 FND_MESSAGE.SET_NAME ('CN' , 'CN_CUM_FLAG_MUST_BE');
636 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
637 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
638 cn_api.get_lkup_meaning
639 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
640 FND_MESSAGE.SET_TOKEN ('TOKEN1',NULL);
641 FND_MSG_PUB.Add;
642 END IF;
643 x_loading_status := 'CN_CUM_FLAG_MUST_BE';
644 RAISE FND_API.G_EXC_ERROR ;
645 END IF;
646 -- Check split_flag = N for DRAW and MANUAUL PE type
647 IF (p_pe_rec.split_flag <> 'N') THEN
648 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
649 THEN
650 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPLIT_FLAG_MUST_BE');
651 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
652 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
653 cn_api.get_lkup_meaning
654 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
655 FND_MESSAGE.SET_TOKEN ('TOKEN1',NULL);
656 FND_MESSAGE.SET_TOKEN ('TOKEN2',NULL);
657 FND_MESSAGE.SET_TOKEN ('TOKEN3',NULL);
658 FND_MSG_PUB.Add;
659 END IF;
660 x_loading_status := 'CN_SPLIT_FLAG_MUST_BE';
661 RAISE FND_API.G_EXC_ERROR ;
662 END IF;
663 -- Check itd_flag = N for DRAW and MANUAUL PE type
664 IF (p_pe_rec.itd_flag <> 'N') THEN
665 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
666 THEN
667 FND_MESSAGE.SET_NAME ('CN' , 'CN_ITD_FLAG_MUST_BE');
668 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
669 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
670 cn_api.get_lkup_meaning
671 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
672 FND_MESSAGE.SET_TOKEN ('TOKEN1',NULL);
673 FND_MSG_PUB.Add;
674 END IF;
675 x_loading_status := 'CN_ITD_FLAG_MUST_BE';
676 RAISE FND_API.G_EXC_ERROR ;
677 END IF;
678 -- Payment Amount =
679 -- NULL : if it's MANUAL type pe
680 -- NOT NULL : if it's DRAW type pe
681 IF (p_pe_rec.quota_type_code = 'DRAW') THEN
682 IF (cn_api.pe_num_field_cannot_null
683 ( p_num_field => p_pe_rec.payment_amount,
684 p_pe_type => p_pe_rec.quota_type_code,
685 p_obj_name => G_DRAW_AMOUNT,
686 p_token1 => NULL ,
687 p_token2 => NULL ,
688 p_token3 => NULL ,
689 p_loading_status => x_loading_status,
690 x_loading_status => x_loading_status) = FND_API.G_FALSE) THEN
691 RAISE FND_API.G_EXC_ERROR ;
692 END IF;
693 ELSIF (p_pe_rec.quota_type_code = 'MANUAL') THEN
694 IF (cn_api.pe_num_field_must_null
695 ( p_num_field => p_pe_rec.payment_amount,
696 p_pe_type => p_pe_rec.quota_type_code,
697 p_obj_name => G_PAYMENT_AMOUT,
698 p_token1 => NULL ,
699 p_token2 => NULL ,
700 p_token3 => NULL ,
701 p_loading_status => x_loading_status,
702 x_loading_status => x_loading_status) = FND_API.G_FALSE) THEN
703 RAISE FND_API.G_EXC_ERROR ;
704 END IF;
705 END IF;
706 -- Check for Rate Table = NULL in DRAW and MANUAUL PE type
707 IF (cn_api.pe_num_field_must_null
708 ( p_num_field => p_pe_rec.rate_table_id,
709 p_pe_type => p_pe_rec.quota_type_code,
710 p_obj_name => G_RATE_TB,
711 p_token1 => NULL ,
712 p_token2 => NULL ,
713 p_token3 => NULL ,
714 p_loading_status => x_loading_status,
715 x_loading_status => x_loading_status) = FND_API.G_FALSE) THEN
716 RAISE FND_API.G_EXC_ERROR ;
717 END IF;
718 -- Check for Discount Rate Table = NULL in DRAW and MANUAUL PE type
719 IF (cn_api.pe_num_field_must_null
720 ( p_num_field => p_pe_rec.disc_rate_table_id,
721 p_pe_type => p_pe_rec.quota_type_code,
722 p_obj_name => G_DISC_RATE_TB,
723 p_token1 => NULL ,
724 p_token2 => NULL ,
725 p_token3 => NULL ,
726 p_loading_status => x_loading_status,
727 x_loading_status => x_loading_status) = FND_API.G_FALSE) THEN
728 RAISE FND_API.G_EXC_ERROR ;
729 END IF;
730 -- Check for Revenue Class = NULL in DRAW and MANUAUL PE type
731 IF (cn_api.pe_num_field_must_null
732 ( p_num_field => p_pe_rec.rev_class_id,
733 p_pe_type => p_pe_rec.quota_type_code,
734 p_obj_name => G_REV_CLS_NAME,
735 p_token1 => NULL ,
736 p_token2 => NULL ,
737 p_token3 => NULL ,
738 p_loading_status => x_loading_status,
739 x_loading_status => x_loading_status) = FND_API.G_FALSE) THEN
740 RAISE FND_API.G_EXC_ERROR ;
741 END IF;
742 -- Check for target =0 in DRAW and MANUAUL PE type: target = 0
743 IF (p_pe_rec.target <> 0) THEN
744 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
745 THEN
746 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_TARGET_MUST_BE');
747 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE','= 0');
748 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
749 cn_api.get_lkup_meaning
750 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
751 FND_MESSAGE.SET_TOKEN ('TOKEN1',NULL);
752 FND_MSG_PUB.Add;
753 END IF;
754 x_loading_status := 'CN_PE_TARGET_MUST_BE';
755 RAISE FND_API.G_EXC_ERROR ;
756 END IF;
757
758 EXCEPTION
759 WHEN FND_API.G_EXC_ERROR THEN
760 x_return_status := FND_API.G_RET_STS_ERROR ;
761
762 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
764 x_loading_status := 'UNEXPECTED_ERR';
765
766 WHEN OTHERS THEN
767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
768 x_loading_status := 'UNEXPECTED_ERR';
769 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
770 THEN
771 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
772 END IF;
773 */
774 fnd_message.set_name ('CN', 'CN_PACKAGE_OBSELETE');
775 fnd_msg_pub.ADD;
776 x_return_status := fnd_api.g_ret_sts_unexp_error;
777 x_loading_status := 'CN_PACKAGE_OBSELETE';
778 RAISE fnd_api.g_exc_error;
779 END chk_dr_man_pe;
780
781 -- ----------------------------------------------------------------------------+
782 -- Procedure: chk_revenue_quota_pe
783 -- Desc : Check input for REVENUE QUOTA type plan element
784 -- ----------------------------------------------------------------------------+
785 PROCEDURE chk_revenue_quota_pe (
786 x_return_status OUT NOCOPY VARCHAR2,
787 p_pe_rec IN pe_rec_type := g_miss_pe_rec,
788 p_loading_status IN VARCHAR2,
789 x_loading_status OUT NOCOPY VARCHAR2
790 )
791 IS
792 l_api_name CONSTANT VARCHAR2 (30) := 'chk_revenue_quota_pe';
793 l_yes fnd_lookups.meaning%TYPE;
794 l_no fnd_lookups.meaning%TYPE;
795 BEGIN
796 /* x_return_status := FND_API.G_RET_STS_SUCCESS;
797 x_loading_status := p_loading_status;
798
799 SELECT meaning INTO l_yes FROM fnd_lookups
800 WHERE lookup_code = 'Y' AND lookup_type = 'YES_NO';
801 SELECT meaning INTO l_no FROM fnd_lookups
802 WHERE lookup_code = 'N' AND lookup_type = 'YES_NO';
803
804 --+
805 -- Validate Rule : cumulative flag Y only
806 --+
807 IF (p_pe_rec.cumulative_flag <> 'Y') THEN
808 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
809 THEN
810 FND_MESSAGE.SET_NAME ('CN' , 'CN_CUM_FLAG_MUST_BE');
811 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_yes);
812 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
813 cn_api.get_lkup_meaning
814 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
815 FND_MESSAGE.SET_TOKEN ('TOKEN1',NULL);
816 FND_MSG_PUB.Add;
817 END IF;
818 x_loading_status := 'CN_CUM_FLAG_MUST_BE';
819 RAISE FND_API.G_EXC_ERROR ;
820 END IF;
821 --+
822 -- Check input for 'Group By' case (trx_group_code = 'GROUP')
823 --+
824 IF p_pe_rec.trx_group_code = 'GROUP' THEN
825 --+
826 -- Validate Rule : Groupby
827 -- ITD Flag = N ,split flag = N , target > 0
828 -- Payment Amount
829 -- NOT NULL : if payment type code = Payment amount %
830 -- NULL : if payment type code = Fixed amount or applied Trx %
831 --+
832 -- Check itd_flag = N
833 IF (p_pe_rec.itd_flag <> 'N') THEN
834 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
835 THEN
836 FND_MESSAGE.SET_NAME ('CN' , 'CN_ITD_FLAG_MUST_BE');
837 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
838 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
839 cn_api.get_lkup_meaning
840 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
841 FND_MESSAGE.SET_TOKEN ('TOKEN1',G_TRX_GROUP||' = '||
842 cn_api.get_lkup_meaning
843 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'))
844 ;
845 FND_MSG_PUB.Add;
846 END IF;
847 x_loading_status := 'CN_ITD_FLAG_MUST_BE';
848 RAISE FND_API.G_EXC_ERROR ;
849 END IF;
850 -- Check split_flag = N
851 IF (p_pe_rec.split_flag <> 'N') THEN
852 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
853 THEN
854 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPLIT_FLAG_MUST_BE');
855 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
856 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
857 cn_api.get_lkup_meaning
858 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
859 FND_MESSAGE.SET_TOKEN ('TOKEN1',G_TRX_GROUP ||' = '||
860 cn_api.get_lkup_meaning
861 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'))
862 ;
863 FND_MESSAGE.SET_TOKEN ('TOKEN2',NULL);
864 FND_MESSAGE.SET_TOKEN ('TOKEN3',NULL);
865 FND_MSG_PUB.Add;
866 END IF;
867 x_loading_status := 'CN_SPLIT_FLAG_MUST_BE';
868 RAISE FND_API.G_EXC_ERROR ;
869 END IF;
870 -- Check target > 0
871 IF (p_pe_rec.target <= 0) THEN
872 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
873 THEN
874 FND_MESSAGE.SET_NAME ('CN' , 'CN_TARGET_CANNOT_ZERO');
875 FND_MSG_PUB.Add;
876 END IF;
877 x_loading_status := 'CN_TARGET_CANNOT_ZERO';
878 RAISE FND_API.G_EXC_ERROR ;
879 END IF;
880 -- Check Payment Amount
881 -- NOT NULL : if payment type code = Payment amount %
882 -- NULL : if payment type code = Fixed amount or applied Trx %
883 IF p_pe_rec.payment_type_code = 'PAYMENT' THEN
884 IF (cn_api.pe_num_field_cannot_null
885 ( p_num_field => p_pe_rec.payment_amount,
886 p_pe_type => p_pe_rec.quota_type_code,
887 p_obj_name => G_PAYMENT_AMOUT,
888 p_token1 => G_TRX_GROUP||' = '||
889 cn_api.get_lkup_meaning
890 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
891 p_token2 => G_PAYMENT_TYPE ||' = '||
892 cn_api.get_lkup_meaning
893 (p_pe_rec.payment_type_code,'QUOTA_PAYMENT_TYPE'),
894 p_token3 => NULL ,
895 p_loading_status => x_loading_status,
896 x_loading_status => x_loading_status) = FND_API.g_false)
897 THEN
898 RAISE FND_API.G_EXC_ERROR ;
899 END IF;
900 ELSIF p_pe_rec.payment_type_code IN ('TRANSACTION','FIXED') AND
901 (cn_api.pe_num_field_must_null
902 ( p_num_field => p_pe_rec.payment_amount,
903 p_pe_type => p_pe_rec.quota_type_code,
904 p_obj_name => G_PAYMENT_AMOUT,
905 p_token1 => G_TRX_GROUP||' = '||
906 cn_api.get_lkup_meaning
907 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
908 p_token2 => G_PAYMENT_TYPE||' = '||
909 cn_api.get_lkup_meaning
910 (p_pe_rec.payment_type_code,'QUOTA_PAYMENT_TYPE'),
911 p_token3 => NULL ,
912 p_loading_status => x_loading_status,
913 x_loading_status => x_loading_status) = FND_API.g_false)
914 THEN
915 RAISE FND_API.G_EXC_ERROR ;
916 END IF;
917 END IF ;
918 --+
919 -- Check input for 'Individual' case (trx_group_code = 'INDIVIDUAL')
920 --+
921 IF p_pe_rec.trx_group_code = 'INDIVIDUAL' THEN
922 -- Check for Non-Interval-To-Date case : itd_flag = 'N'
923 --+
924 -- Validate Rule :
925 -- target > 0,
926 -- split_flag = N if Payment Type = Payment amount %or Fixed Amount
927 -- Payment Amount
928 -- NOT NULL : if payment type code = Payment amount %
929 -- NULL : if payment type code = Fixed amount or applied Trx %
930 --+
931 IF p_pe_rec.itd_flag = 'N' THEN
932 -- Check target > 0
933 IF (p_pe_rec.target <= 0) THEN
934 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
935 THEN
936 FND_MESSAGE.SET_NAME ('CN' , 'CN_TARGET_CANNOT_ZERO');
937 FND_MSG_PUB.Add;
938 END IF;
939 x_loading_status := 'CN_TARGET_CANNOT_ZERO';
940 RAISE FND_API.G_EXC_ERROR ;
941 END IF;
942 -- Check split_flag = N if Payment Type = Payment amount %
943 -- or Fixed Amount
944 IF (p_pe_rec.payment_type_code IN ('PAYMENT','FIXED')) AND
945 (p_pe_rec.split_flag <> 'N') THEN
946 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
947 THEN
948 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPLIT_FLAG_MUST_BE');
949 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
950 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
951 cn_api.get_lkup_meaning
952 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
953 FND_MESSAGE.SET_TOKEN ('TOKEN1',G_TRX_GROUP||' = '||
954 cn_api.get_lkup_meaning
955 (p_pe_rec.trx_group_code,
956 'QUOTA_TRX_GROUP'));
957 FND_MESSAGE.SET_TOKEN ('TOKEN2',G_PAYMENT_TYPE||' = '||
958 cn_api.get_lkup_meaning
959 (p_pe_rec.payment_type_code,
960 'QUOTA_PAYMENT_TYPE'));
961 FND_MESSAGE.SET_TOKEN ('TOKEN3',G_ITD||' = '||
962 p_pe_rec.itd_flag);
963 FND_MSG_PUB.Add;
964 END IF;
965 x_loading_status := 'CN_SPLIT_FLAG_MUST_BE';
966 RAISE FND_API.G_EXC_ERROR ;
967 END IF;
968 -- Check Payment Amount
969 -- NOT NULL : if payment type code = Payment amount %
970 -- NULL : if payment type code = Fixed amount or applied Trx %
971 IF p_pe_rec.payment_type_code = 'PAYMENT' THEN
972 IF (cn_api.pe_num_field_cannot_null
973 ( p_num_field => p_pe_rec.payment_amount,
974 p_pe_type => p_pe_rec.quota_type_code,
975 p_obj_name => G_PAYMENT_AMOUT,
976 p_token1 => G_TRX_GROUP||' = '||
977 cn_api.get_lkup_meaning
978 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
979 p_token2 => G_PAYMENT_TYPE||' = '||
980 cn_api.get_lkup_meaning
981 (p_pe_rec.payment_type_code,'QUOTA_PAYMENT_TYPE'),
982 p_token3 => G_ITD||' = '||p_pe_rec.itd_flag,
983 p_loading_status => x_loading_status,
984 x_loading_status => x_loading_status) = FND_API.g_false)
985 THEN
986 RAISE FND_API.G_EXC_ERROR ;
987 END IF;
988 ELSIF p_pe_rec.payment_type_code IN ('TRANSACTION','FIXED') AND
989 (cn_api.pe_num_field_must_null
990 ( p_num_field => p_pe_rec.payment_amount,
991 p_pe_type => p_pe_rec.quota_type_code,
992 p_obj_name => G_PAYMENT_AMOUT,
993 p_token1 => G_TRX_GROUP||' = '||
994 cn_api.get_lkup_meaning
995 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
996 p_token2 => G_PAYMENT_TYPE||' = '||
997 cn_api.get_lkup_meaning
998 (p_pe_rec.payment_type_code,'QUOTA_PAYMENT_TYPE'),
999 p_token3 => G_ITD||' = '||p_pe_rec.itd_flag,
1000 p_loading_status => x_loading_status,
1001 x_loading_status => x_loading_status) = FND_API.G_FALSE)
1002 THEN
1003 RAISE FND_API.G_EXC_ERROR ;
1004 END IF;
1005 ELSIF p_pe_rec.itd_flag = 'Y' THEN
1006 -- Check for Interval-To-Date case : itd_flag = 'Y'
1007 --+
1008 -- Validate Rule :
1009 -- target = 0,
1010 -- No Fixed Amount payment type allowed
1011 -- split_flag = N if Payment Type= Payment amount % or Applied Trx%
1012 -- Payment Amount = 0
1013 --+
1014 -- Check target = 0
1015 IF (p_pe_rec.target <> 0) THEN
1016 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1017 THEN
1018 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_TARGET_MUST_BE');
1019 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE','= 0');
1020 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1021 cn_api.get_lkup_meaning
1022 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1023 FND_MESSAGE.SET_TOKEN ('TOKEN1',
1024 G_ITD||' = '||p_pe_rec.itd_flag);
1025 FND_MSG_PUB.Add;
1026 END IF;
1027 x_loading_status := 'CN_PE_TARGET_MUST_BE';
1028 RAISE FND_API.G_EXC_ERROR ;
1029 END IF;
1030 -- Check no 'Fixed Amount' payment type allowed
1031 IF p_pe_rec.payment_type_code = 'FIXED' THEN
1032 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1033 THEN
1034 FND_MESSAGE.SET_NAME ('CN' ,'CN_ITD_NO_FIXED_AMOUNT');
1035 FND_MSG_PUB.Add;
1036 END IF;
1037 x_loading_status := 'CN_ITD_NO_FIXED_AMOUNT';
1038 RAISE FND_API.G_EXC_ERROR ;
1039 END IF;
1040 -- Check split_flag = N if Payment Type = Payment amount %
1041 -- or Applied Trx %
1042 IF (p_pe_rec.payment_type_code IN ('PAYMENT','TRANSACTION')) AND
1043 (p_pe_rec.split_flag <> 'N') THEN
1044 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1045 THEN
1046 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPLIT_FLAG_MUST_BE');
1047 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
1048 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1049 cn_api.get_lkup_meaning
1050 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1051 FND_MESSAGE.SET_TOKEN ('TOKEN1', G_TRX_GROUP||' = '||
1052 cn_api.get_lkup_meaning
1053 (p_pe_rec.trx_group_code,
1054 'QUOTA_TRX_GROUP'));
1055 FND_MESSAGE.SET_TOKEN ('TOKEN2', G_PAYMENT_TYPE||' = '||
1056 cn_api.get_lkup_meaning
1057 (p_pe_rec.payment_type_code,
1058 'QUOTA_PAYMENT_TYPE'));
1059 FND_MESSAGE.SET_TOKEN ('TOKEN3',
1060 G_ITD||' = '||p_pe_rec.itd_flag);
1061 FND_MSG_PUB.Add;
1062 END IF;
1063 x_loading_status := 'CN_SPLIT_FLAG_MUST_BE';
1064 RAISE FND_API.G_EXC_ERROR ;
1065 END IF;
1066 -- Check Payment Amount : Must be 0
1067 IF (p_pe_rec.payment_amount <> 0) THEN
1068 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1069 THEN
1070 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_PAYMENT_AMT_MUST_BE');
1071 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE','= 0');
1072 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1073 cn_api.get_lkup_meaning
1074 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1075 FND_MESSAGE.SET_TOKEN ('TOKEN1',G_TRX_GROUP||' = '||
1076 cn_api.get_lkup_meaning
1077 (p_pe_rec.trx_group_code,
1078 'QUOTA_TRX_GROUP'));
1079 FND_MESSAGE.SET_TOKEN ('TOKEN2',G_PAYMENT_TYPE||' = '||
1080 cn_api.get_lkup_meaning
1081 (p_pe_rec.payment_type_code,
1082 'QUOTA_PAYMENT_TYPE'));
1083 FND_MESSAGE.SET_TOKEN ('TOKEN3',
1084 G_ITD||' = '||p_pe_rec.itd_flag);
1085 FND_MSG_PUB.Add;
1086 END IF;
1087 x_loading_status := 'CN_PE_PAYMENT_AMT_MUST_BE';
1088 RAISE FND_API.G_EXC_ERROR ;
1089 END IF;
1090 END IF ; -- end ITD_FLAG
1091 END IF ; -- end INDIVIDUAL
1092 -- Check rate table
1093 valid_rate_table
1094 ( x_return_status => x_return_status,
1095 p_pe_rec => p_pe_rec,
1096 p_loading_status => x_loading_status,
1097 x_loading_status => x_loading_status);
1098 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1099 RAISE FND_API.G_EXC_ERROR ;
1100 END IF;
1101 -- Check discount rate table
1102 IF (p_pe_rec.disc_option_code IN ('PAYMENT','QUOTA')) THEN
1103 valid_disc_rate_table
1104 ( x_return_status => x_return_status,
1105 p_pe_rec => p_pe_rec,
1106 p_loading_status => x_loading_status,
1107 x_loading_status => x_loading_status);
1108 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1109 RAISE FND_API.G_EXC_ERROR ;
1110 END IF;
1111 ELSE
1112 -- Check for Discount Rate Table must be NULL
1113 IF (cn_api.pe_num_field_must_null
1114 ( p_num_field => p_pe_rec.disc_rate_table_id,
1115 p_pe_type => p_pe_rec.quota_type_code,
1116 p_obj_name => G_DISC_RATE_TB,
1117 p_token1 =>
1118 G_DISC_OPTION||' = '||
1119 cn_api.get_lkup_meaning
1120 (p_pe_rec.disc_option_code,'DISCOUNT_OPTION'),
1121 p_token2 => NULL ,
1122 p_token3 => NULL ,
1123 p_loading_status => x_loading_status,
1124 x_loading_status => x_loading_status) = FND_API.G_FALSE) THEN
1125 RAISE FND_API.G_EXC_ERROR ;
1126 END IF;
1127 END IF;
1128
1129 -- Check rc
1130 valid_revenue_class
1131 ( x_return_status => x_return_status,
1132 p_pe_rec => p_pe_rec,
1133 p_loading_status => x_loading_status,
1134 x_loading_status => x_loading_status);
1135 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1136 RAISE FND_API.G_EXC_ERROR ;
1137 END IF;
1138
1139
1140 EXCEPTION
1141 WHEN FND_API.G_EXC_ERROR THEN
1142 x_return_status := FND_API.G_RET_STS_ERROR ;
1143
1144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1146 x_loading_status := 'UNEXPECTED_ERR';
1147
1148 WHEN OTHERS THEN
1149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1150 x_loading_status := 'UNEXPECTED_ERR';
1151 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1152 THEN
1153 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1154 END IF;
1155 */
1156 fnd_message.set_name ('CN', 'CN_PACKAGE_OBSELETE');
1157 fnd_msg_pub.ADD;
1158 x_return_status := fnd_api.g_ret_sts_unexp_error;
1159 x_loading_status := 'CN_PACKAGE_OBSELETE';
1160 RAISE fnd_api.g_exc_error;
1161 END chk_revenue_quota_pe;
1162
1163 -- ----------------------------------------------------------------------------+
1164 -- Procedure: chk_unit_quota_pe
1165 -- Desc : Check input for UNIT QUOTA type plan element
1166 -- ----------------------------------------------------------------------------+
1167 PROCEDURE chk_unit_quota_pe (
1168 x_return_status OUT NOCOPY VARCHAR2,
1169 p_pe_rec IN pe_rec_type := g_miss_pe_rec,
1170 p_loading_status IN VARCHAR2,
1171 x_loading_status OUT NOCOPY VARCHAR2
1172 )
1173 IS
1174 l_api_name CONSTANT VARCHAR2 (30) := 'chk_unit_quota_pe';
1175 l_yes fnd_lookups.meaning%TYPE;
1176 l_no fnd_lookups.meaning%TYPE;
1177 BEGIN
1178 /* x_return_status := FND_API.G_RET_STS_SUCCESS;
1179 x_loading_status := p_loading_status;
1180
1181 SELECT meaning INTO l_yes FROM fnd_lookups
1182 WHERE lookup_code = 'Y' AND lookup_type = 'YES_NO';
1183 SELECT meaning INTO l_no FROM fnd_lookups
1184 WHERE lookup_code = 'N' AND lookup_type = 'YES_NO';
1185 --+
1186 -- Validate Rule : cumulative flag Y only
1187 --+
1188 IF (p_pe_rec.cumulative_flag <> 'Y') THEN
1189 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1190 THEN
1191 FND_MESSAGE.SET_NAME ('CN' , 'CN_CUM_FLAG_MUST_BE');
1192 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_yes);
1193 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1194 cn_api.get_lkup_meaning
1195 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1196 FND_MESSAGE.SET_TOKEN ('TOKEN1',NULL);
1197 FND_MSG_PUB.Add;
1198 END IF;
1199 x_loading_status := 'CN_CUM_FLAG_MUST_BE';
1200 RAISE FND_API.G_EXC_ERROR ;
1201 END IF;
1202 --+
1203 -- Check input for 'Group By' case (trx_group_code = 'GROUP')
1204 --+
1205 IF p_pe_rec.trx_group_code = 'GROUP' THEN
1206 --+
1207 -- Validate Rule : Groupby
1208 -- ITD Flag = N ,split flag = N , target > 0
1209 -- Payment Amount
1210 -- NOT NULL : if payment type code = Payment amount %
1211 -- NULL : if payment type code = Fixed amount or applied Trx %
1212 --+
1213 -- Check itd_flag = N
1214 IF (p_pe_rec.itd_flag <> 'N') THEN
1215 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1216 THEN
1217 FND_MESSAGE.SET_NAME ('CN' , 'CN_ITD_FLAG_MUST_BE');
1218 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
1219 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1220 cn_api.get_lkup_meaning
1221 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1222 FND_MESSAGE.SET_TOKEN ('TOKEN1', G_TRX_GROUP||' = '||
1223 cn_api.get_lkup_meaning
1224 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'));
1225 FND_MSG_PUB.Add;
1226 END IF;
1227 x_loading_status := 'CN_ITD_FLAG_MUST_BE';
1228 RAISE FND_API.G_EXC_ERROR ;
1229 END IF;
1230 -- Check split_flag = N
1231 IF (p_pe_rec.split_flag <> 'N') THEN
1232 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1233 THEN
1234 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPLIT_FLAG_MUST_BE');
1235 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
1236 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1237 cn_api.get_lkup_meaning
1238 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1239 FND_MESSAGE.SET_TOKEN ('TOKEN1', G_TRX_GROUP||' = '||
1240 cn_api.get_lkup_meaning
1241 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'))
1242 ;
1243 FND_MESSAGE.SET_TOKEN ('TOKEN2',NULL);
1244 FND_MESSAGE.SET_TOKEN ('TOKEN3',NULL);
1245 FND_MSG_PUB.Add;
1246 END IF;
1247 x_loading_status := 'CN_SPLIT_FLAG_MUST_BE';
1248 RAISE FND_API.G_EXC_ERROR ;
1249 END IF;
1250 -- Check target > 0
1251 IF (p_pe_rec.target <= 0) THEN
1252 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1253 THEN
1254 FND_MESSAGE.SET_NAME ('CN' , 'CN_TARGET_CANNOT_ZERO');
1255 FND_MSG_PUB.Add;
1256 END IF;
1257 x_loading_status := 'CN_TARGET_CANNOT_ZERO';
1258 RAISE FND_API.G_EXC_ERROR ;
1259 END IF;
1260 -- Check Payment Amount
1261 -- NOT NULL : if payment type code = Payment amount %
1262 -- NULL : if payment type code = Fixed amount or applied Trx %
1263 IF p_pe_rec.payment_type_code = 'PAYMENT' THEN
1264 IF (cn_api.pe_num_field_cannot_null
1265 ( p_num_field => p_pe_rec.payment_amount,
1266 p_pe_type => p_pe_rec.quota_type_code,
1267 p_obj_name => G_PAYMENT_AMOUT,
1268 p_token1 => G_TRX_GROUP||' = '||
1269 cn_api.get_lkup_meaning
1270 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
1271 p_token2 => G_PAYMENT_TYPE||' = '||
1272 cn_api.get_lkup_meaning
1273 (p_pe_rec.payment_type_code,
1274 'QUOTA_PAYMENT_TYPE'),
1275 p_token3 => NULL ,
1276 p_loading_status => x_loading_status,
1277 x_loading_status => x_loading_status) = FND_API.g_false)
1278 THEN
1279 RAISE FND_API.G_EXC_ERROR ;
1280 END IF;
1281 ELSIF p_pe_rec.payment_type_code IN ('TRANSACTION','FIXED') AND
1282 (cn_api.pe_num_field_must_null
1283 ( p_num_field => p_pe_rec.payment_amount,
1284 p_pe_type => p_pe_rec.quota_type_code,
1285 p_obj_name => G_PAYMENT_AMOUT,
1286 p_token1 => G_TRX_GROUP||' = '||
1287 cn_api.get_lkup_meaning
1288 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
1289 p_token2 => G_PAYMENT_TYPE||' = '||
1290 cn_api.get_lkup_meaning
1291 (p_pe_rec.payment_type_code,'QUOTA_PAYMENT_TYPE'),
1292 p_token3 => NULL ,
1293 p_loading_status => x_loading_status,
1294 x_loading_status => x_loading_status) = FND_API.g_false)
1295 THEN
1296 RAISE FND_API.G_EXC_ERROR ;
1297 END IF;
1298 END IF ;
1299 --+
1300 -- Check input for 'Individual' case (trx_group_code = 'INDIVIDUAL')
1301 --+
1302 IF p_pe_rec.trx_group_code = 'INDIVIDUAL' THEN
1303 -- Check for Non-Interval-To-Date case : itd_flag = 'N'
1304 --+
1305 -- Validate Rule :
1306 -- target > 0,
1307 -- split_flag = N if Payment Type = Payment amount %or Fixed Amount
1308 -- Payment Amount
1309 -- NOT NULL : if payment type code = Payment amount %
1310 -- NULL : if payment type code = Fixed amount or applied Trx %
1311 --+
1312 IF p_pe_rec.itd_flag = 'N' THEN
1313 -- Check target > 0
1314 IF (p_pe_rec.target <= 0) THEN
1315 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1316 THEN
1317 FND_MESSAGE.SET_NAME ('CN' , 'CN_TARGET_CANNOT_ZERO');
1318 FND_MSG_PUB.Add;
1319 END IF;
1320 x_loading_status := 'CN_TARGET_CANNOT_ZERO';
1321 RAISE FND_API.G_EXC_ERROR ;
1322 END IF;
1323 -- Check split_flag = N if Payment Type = Payment amount %
1324 -- or Fixed Amount
1325 IF (p_pe_rec.payment_type_code IN ('PAYMENT','FIXED')) AND
1326 (p_pe_rec.split_flag <> 'N') THEN
1327 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1328 THEN
1329 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPLIT_FLAG_MUST_BE');
1330 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
1331 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1332 cn_api.get_lkup_meaning
1333 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1334 FND_MESSAGE.SET_TOKEN ('TOKEN1', G_TRX_GROUP||' = '||
1335 cn_api.get_lkup_meaning
1336 (p_pe_rec.trx_group_code,
1337 'QUOTA_TRX_GROUP'));
1338 FND_MESSAGE.SET_TOKEN ('TOKEN2',G_PAYMENT_TYPE||' = '||
1339 cn_api.get_lkup_meaning
1340 (p_pe_rec.payment_type_code,
1341 'QUOTA_PAYMENT_TYPE'));
1342 FND_MESSAGE.SET_TOKEN ('TOKEN3',
1343 G_ITD||' = '||p_pe_rec.itd_flag);
1344 FND_MSG_PUB.Add;
1345 END IF;
1346 x_loading_status := 'CN_SPLIT_FLAG_MUST_BE';
1347 RAISE FND_API.G_EXC_ERROR ;
1348 END IF;
1349 -- Check Payment Amount
1350 -- NOT NULL : if payment type code = Payment amount %
1351 -- NULL : if payment type code = Fixed amount or applied Trx %
1352 IF p_pe_rec.payment_type_code = 'PAYMENT' THEN
1353 IF (cn_api.pe_num_field_cannot_null
1354 ( p_num_field => p_pe_rec.payment_amount,
1355 p_pe_type => p_pe_rec.quota_type_code,
1356 p_obj_name => G_PAYMENT_AMOUT,
1357 p_token1 => G_TRX_GROUP||' = '||
1358 cn_api.get_lkup_meaning
1359 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
1360 p_token2 => G_PAYMENT_TYPE||' = '||
1361 cn_api.get_lkup_meaning
1362 (p_pe_rec.payment_type_code,
1363 'QUOTA_PAYMENT_TYPE'),
1364 p_token3 => G_ITD||' = '||p_pe_rec.itd_flag,
1365 p_loading_status => x_loading_status,
1366 x_loading_status => x_loading_status) = FND_API.g_false)
1367 THEN
1368 RAISE FND_API.G_EXC_ERROR ;
1369 END IF;
1370 ELSIF p_pe_rec.payment_type_code IN ('TRANSACTION','FIXED') AND
1371 (cn_api.pe_num_field_must_null
1372 ( p_num_field => p_pe_rec.payment_amount,
1373 p_pe_type => p_pe_rec.quota_type_code,
1374 p_obj_name => G_PAYMENT_AMOUT,
1375 p_token1 => G_TRX_GROUP||' = '||
1376 cn_api.get_lkup_meaning
1377 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
1378 p_token2 => G_PAYMENT_TYPE||' = '||
1379 cn_api.get_lkup_meaning
1380 (p_pe_rec.payment_type_code,
1381 'QUOTA_PAYMENT_TYPE'),
1382 p_token3 => G_ITD||' = '||p_pe_rec.itd_flag,
1383 p_loading_status => x_loading_status,
1384 x_loading_status => x_loading_status) = FND_API.G_FALSE)
1385 THEN
1386 RAISE FND_API.G_EXC_ERROR ;
1387 END IF;
1388 ELSIF p_pe_rec.itd_flag = 'Y' THEN
1389 -- Check for Interval-To-Date case : itd_flag = 'Y'
1390 --+
1391 -- Validate Rule :
1392 -- target = 0,
1393 -- No Fixed Amount payment type allowed
1394 -- split_flag = N if Payment Type= Payment amount % or Applied Trx%
1395 -- Payment Amount = 0
1396 --+
1397 -- Check target = 0
1398 IF (p_pe_rec.target <> 0) THEN
1399 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1400 THEN
1401 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_TARGET_MUST_BE');
1402 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE','= 0');
1403 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1404 cn_api.get_lkup_meaning
1405 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1406 FND_MESSAGE.SET_TOKEN ('TOKEN1',
1407 G_ITD||' = '||p_pe_rec.itd_flag);
1408 FND_MSG_PUB.Add;
1409 END IF;
1410 x_loading_status := 'CN_PE_TARGET_MUST_BE';
1411 RAISE FND_API.G_EXC_ERROR ;
1412 END IF;
1413 -- Check no 'Fixed Amount' payment type allowed
1414 IF p_pe_rec.payment_type_code = 'FIXED' THEN
1415 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1416 THEN
1417 FND_MESSAGE.SET_NAME ('CN' ,'CN_ITD_NO_FIXED_AMOUNT');
1418 FND_MSG_PUB.Add;
1419 END IF;
1420 x_loading_status := 'CN_ITD_NO_FIXED_AMOUNT';
1421 RAISE FND_API.G_EXC_ERROR ;
1422 END IF;
1423 -- Check split_flag = N if Payment Type = Payment amount %
1424 -- or Applied Trx %
1425 IF (p_pe_rec.payment_type_code IN ('PAYMENT','TRANSACTION')) AND
1426 (p_pe_rec.split_flag <> 'N') THEN
1427 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1428 THEN
1429 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPLIT_FLAG_MUST_BE');
1430 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
1431 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1432 cn_api.get_lkup_meaning
1433 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1434 FND_MESSAGE.SET_TOKEN ('TOKEN1', G_TRX_GROUP||' = '||
1435 cn_api.get_lkup_meaning
1436 (p_pe_rec.trx_group_code,
1437 'QUOTA_TRX_GROUP'));
1438 FND_MESSAGE.SET_TOKEN ('TOKEN2', G_PAYMENT_TYPE||' = '||
1439 cn_api.get_lkup_meaning
1440 (p_pe_rec.payment_type_code,
1441 'QUOTA_PAYMENT_TYPE'));
1442 FND_MESSAGE.SET_TOKEN ('TOKEN3',
1443 G_ITD||' = '||p_pe_rec.itd_flag);
1444 FND_MSG_PUB.Add;
1445 END IF;
1446 x_loading_status := 'CN_SPLIT_FLAG_MUST_BE';
1447 RAISE FND_API.G_EXC_ERROR ;
1448 END IF;
1449 -- Check Payment Amount : Must be 0
1450 IF (p_pe_rec.payment_amount <> 0) THEN
1451 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1452 THEN
1453 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_PAYMENT_AMT_MUST_BE');
1454 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE','= 0');
1455 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1456 cn_api.get_lkup_meaning
1457 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1458 FND_MESSAGE.SET_TOKEN ('TOKEN1', G_TRX_GROUP||' = '||
1459 cn_api.get_lkup_meaning
1460 (p_pe_rec.trx_group_code,
1461 'QUOTA_TRX_GROUP'));
1462 FND_MESSAGE.SET_TOKEN ('TOKEN2', G_PAYMENT_TYPE||' = '||
1463 cn_api.get_lkup_meaning
1464 (p_pe_rec.payment_type_code,
1465 'QUOTA_PAYMENT_TYPE'));
1466 FND_MESSAGE.SET_TOKEN ('TOKEN3',
1467 G_ITD||' = '||p_pe_rec.itd_flag);
1468 FND_MSG_PUB.Add;
1469 END IF;
1470 x_loading_status := 'CN_PE_PAYMENT_AMT_MUST_BE';
1471 RAISE FND_API.G_EXC_ERROR ;
1472 END IF;
1473 END IF ; -- end ITD_FLAG
1474 END IF ; -- end INDIVIDUAL
1475 -- Check rate table
1476 valid_rate_table
1477 ( x_return_status => x_return_status,
1478 p_pe_rec => p_pe_rec,
1479 p_loading_status => x_loading_status,
1480 x_loading_status => x_loading_status);
1481 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1482 RAISE FND_API.G_EXC_ERROR ;
1483 END IF;
1484 -- Check discount rate table = Not Allowed
1485 -- Discount Option Code must = NONE is validate when calling
1486 -- valid_lookup_code() from valid_plan_element
1487 IF (cn_api.pe_num_field_must_null
1488 ( p_num_field => p_pe_rec.disc_rate_table_id,
1489 p_pe_type => p_pe_rec.quota_type_code,
1490 p_obj_name => G_DISC_RATE_TB,
1491 p_token1 => NULL ,
1492 p_token2 => NULL ,
1493 p_token3 => NULL ,
1494 p_loading_status => x_loading_status,
1495 x_loading_status => x_loading_status) = FND_API.g_false)
1496 THEN
1497 RAISE FND_API.G_EXC_ERROR ;
1498 END IF;
1499 -- Check rc
1500 valid_revenue_class
1501 ( x_return_status => x_return_status,
1502 p_pe_rec => p_pe_rec,
1503 p_loading_status => x_loading_status,
1504 x_loading_status => x_loading_status);
1505 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1506 RAISE FND_API.G_EXC_ERROR ;
1507 END IF;
1508
1509 EXCEPTION
1510 WHEN FND_API.G_EXC_ERROR THEN
1511 x_return_status := FND_API.G_RET_STS_ERROR ;
1512
1513 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1515 x_loading_status := 'UNEXPECTED_ERR';
1516
1517 WHEN OTHERS THEN
1518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1519 x_loading_status := 'UNEXPECTED_ERR';
1520 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1521 THEN
1522 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1523 END IF;
1524 */
1525 fnd_message.set_name ('CN', 'CN_PACKAGE_OBSELETE');
1526 fnd_msg_pub.ADD;
1527 x_return_status := fnd_api.g_ret_sts_unexp_error;
1528 x_loading_status := 'CN_PACKAGE_OBSELETE';
1529 RAISE fnd_api.g_exc_error;
1530 END chk_unit_quota_pe;
1531
1532 -- ----------------------------------------------------------------------------+
1533 -- Procedure: chk_revenue_non_quota_pe
1534 -- Desc : Check input for REVENUE QUOTA type plan element
1535 -- ----------------------------------------------------------------------------+
1536 PROCEDURE chk_revenue_non_quota_pe (
1537 x_return_status OUT NOCOPY VARCHAR2,
1538 p_pe_rec IN pe_rec_type := g_miss_pe_rec,
1539 p_loading_status IN VARCHAR2,
1540 x_loading_status OUT NOCOPY VARCHAR2
1541 )
1542 IS
1543 l_api_name CONSTANT VARCHAR2 (30) := 'chk_revenue_non_quota_pe';
1544 l_yes fnd_lookups.meaning%TYPE;
1545 l_no fnd_lookups.meaning%TYPE;
1546 BEGIN
1547 /* x_return_status := FND_API.G_RET_STS_SUCCESS;
1548 x_loading_status := p_loading_status;
1549
1550 SELECT meaning INTO l_yes FROM fnd_lookups
1551 WHERE lookup_code = 'Y' AND lookup_type = 'YES_NO';
1552 SELECT meaning INTO l_no FROM fnd_lookups
1553 WHERE lookup_code = 'N' AND lookup_type = 'YES_NO';
1554 --+
1555 -- Validate Rule :
1556 -- target = 0, ITD Flag = N
1557 --+
1558 -- Check target = 0
1559 IF (p_pe_rec.target <> 0) THEN
1560 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1561 THEN
1562 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_TARGET_MUST_BE');
1563 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE','= 0');
1564 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1565 cn_api.get_lkup_meaning
1566 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1567 FND_MESSAGE.SET_TOKEN ('TOKEN1',NULL);
1568 FND_MSG_PUB.Add;
1569 END IF;
1570 x_loading_status := 'CN_PE_TARGET_MUST_BE';
1571 RAISE FND_API.G_EXC_ERROR ;
1572 END IF;
1573 -- Check itd_flag = N
1574 IF (p_pe_rec.itd_flag <> 'N') THEN
1575 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1576 THEN
1577 FND_MESSAGE.SET_NAME ('CN' , 'CN_ITD_FLAG_MUST_BE');
1578 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
1579 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1580 cn_api.get_lkup_meaning
1581 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1582 FND_MESSAGE.SET_TOKEN ('TOKEN1',NULL);
1583 FND_MSG_PUB.Add;
1584 END IF;
1585 x_loading_status := 'CN_ITD_FLAG_MUST_BE';
1586 RAISE FND_API.G_EXC_ERROR ;
1587 END IF;
1588 -- Check input for 'Group By' case (trx_group_code = 'GROUP')
1589 --+
1590 IF p_pe_rec.trx_group_code = 'GROUP' THEN
1591 --+
1592 -- Validate Rule : Groupby
1593 -- Cumulative Flag = N ,split flag = N ,
1594 -- Payment Amount
1595 -- NOT NULL : if payment type code = Payment amount %
1596 -- NULL : if payment type code = Fixed amount or applied Trx %
1597 --+
1598 -- Check Cumulative Flag = N
1599 IF (p_pe_rec.cumulative_flag <> 'N') THEN
1600 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1601 THEN
1602 FND_MESSAGE.SET_NAME ('CN' , 'CN_CUM_FLAG_MUST_BE');
1603 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
1604 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1605 cn_api.get_lkup_meaning
1606 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1607 FND_MESSAGE.SET_TOKEN ('TOKEN1', G_TRX_GROUP||' = '||
1608 cn_api.get_lkup_meaning
1609 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'));
1610 FND_MSG_PUB.Add;
1611 END IF;
1612 x_loading_status := 'CN_CUM_FLAG_MUST_BE';
1613 RAISE FND_API.G_EXC_ERROR ;
1614 END IF;
1615 -- Check split_flag = N
1616 IF (p_pe_rec.split_flag <> 'N') THEN
1617 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1618 THEN
1619 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPLIT_FLAG_MUST_BE');
1620 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
1621 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1622 cn_api.get_lkup_meaning
1623 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1624 FND_MESSAGE.SET_TOKEN ('TOKEN1', G_TRX_GROUP||' = '||
1625 cn_api.get_lkup_meaning
1626 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'));
1627 FND_MESSAGE.SET_TOKEN ('TOKEN2',NULL);
1628 FND_MESSAGE.SET_TOKEN ('TOKEN3',NULL);
1629 FND_MSG_PUB.Add;
1630 END IF;
1631 x_loading_status := 'CN_SPLIT_FLAG_MUST_BE';
1632 RAISE FND_API.G_EXC_ERROR ;
1633 END IF;
1634 -- Check Payment Amount
1635 -- NOT NULL : if payment type code = Payment amount %
1636 -- NULL : if payment type code = Fixed amount or applied Trx %
1637 IF p_pe_rec.payment_type_code = 'PAYMENT' THEN
1638 IF (cn_api.pe_num_field_cannot_null
1639 ( p_num_field => p_pe_rec.payment_amount,
1640 p_pe_type => p_pe_rec.quota_type_code,
1641 p_obj_name => G_PAYMENT_AMOUT,
1642 p_token1 => G_TRX_GROUP||' = '||
1643 cn_api.get_lkup_meaning
1644 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
1645 p_token2 => G_PAYMENT_TYPE||' = '||
1646 cn_api.get_lkup_meaning
1647 (p_pe_rec.payment_type_code,
1648 'QUOTA_PAYMENT_TYPE'),
1649 p_token3 => NULL ,
1650 p_loading_status => x_loading_status,
1651 x_loading_status => x_loading_status) = FND_API.g_false)
1652 THEN
1653 RAISE FND_API.G_EXC_ERROR ;
1654 END IF;
1655 ELSIF p_pe_rec.payment_type_code IN ('TRANSACTION','FIXED') AND
1656 (cn_api.pe_num_field_must_null
1657 ( p_num_field => p_pe_rec.payment_amount,
1658 p_pe_type => p_pe_rec.quota_type_code,
1659 p_obj_name => G_PAYMENT_AMOUT,
1660 p_token1 => G_TRX_GROUP||' = '||
1661 cn_api.get_lkup_meaning
1662 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
1663 p_token2 => G_PAYMENT_TYPE||' = '||
1664 cn_api.get_lkup_meaning
1665 (p_pe_rec.payment_type_code,
1666 'QUOTA_PAYMENT_TYPE'),
1667 p_token3 => NULL ,
1668 p_loading_status => x_loading_status,
1669 x_loading_status => x_loading_status) = FND_API.g_false)
1670 THEN
1671 RAISE FND_API.G_EXC_ERROR ;
1672 END IF;
1673 END IF ; -- end GROUP BY
1674
1675 --+
1676 -- Check input for 'Individual' case (trx_group_code = 'INDIVIDUAL')
1677 --+
1678 IF p_pe_rec.trx_group_code = 'INDIVIDUAL' THEN
1679 IF p_pe_rec.payment_type_code = 'TRANSACTION' THEN
1680 -- Check for Payment Type = Applied Trx % case
1681 --+
1682 -- Validate Rule :
1683 -- payment amount = NULL
1684 -- split flag = N if cumulative flag = N
1685 --+
1686 -- Check payment amount = NULL
1687 IF (cn_api.pe_num_field_must_null
1688 ( p_num_field => p_pe_rec.payment_amount,
1689 p_pe_type => p_pe_rec.quota_type_code,
1690 p_obj_name => G_PAYMENT_AMOUT,
1691 p_token1 => G_TRX_GROUP||' = '||
1692 cn_api.get_lkup_meaning
1693 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
1694 p_token2 => G_PAYMENT_TYPE||' = '||
1695 cn_api.get_lkup_meaning
1696 (p_pe_rec.payment_type_code,
1697 'QUOTA_PAYMENT_TYPE'),
1698 p_token3 => NULL ,
1699 p_loading_status => x_loading_status,
1700 x_loading_status => x_loading_status) = FND_API.g_false)
1701 THEN
1702 RAISE FND_API.G_EXC_ERROR ;
1703 END IF;
1704 -- Check split flag = N if cumulative flag = N
1705 IF (p_pe_rec.cumulative_flag = 'N') AND
1706 (p_pe_rec.split_flag <> 'N') THEN
1707 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1708 THEN
1709 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPLIT_FLAG_MUST_BE');
1710 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
1711 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1712 cn_api.get_lkup_meaning
1713 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1714 FND_MESSAGE.SET_TOKEN ('TOKEN1', G_TRX_GROUP||' = '||
1715 cn_api.get_lkup_meaning
1716 (p_pe_rec.trx_group_code,
1717 'QUOTA_TRX_GROUP'));
1718 FND_MESSAGE.SET_TOKEN ('TOKEN2', G_PAYMENT_TYPE||' = '||
1719 cn_api.get_lkup_meaning
1720 (p_pe_rec.payment_type_code,
1721 'QUOTA_PAYMENT_TYPE'));
1722 FND_MESSAGE.SET_TOKEN ('TOKEN3',G_ACCMULATE||' = '||
1723 p_pe_rec.cumulative_flag);
1724 FND_MSG_PUB.Add;
1725 END IF;
1726 x_loading_status := 'CN_SPLIT_FLAG_MUST_BE';
1727 RAISE FND_API.G_EXC_ERROR ;
1728 END IF;
1729 ELSIF p_pe_rec.payment_type_code IN ('PAYMENT','FIXED') THEN
1730 -- Check for Payment Type = Payment Amount % or Fixed Amount case
1731 --+
1732 -- Validate Rule :
1733 -- split flag = N
1734 -- Payment Amount
1735 -- NOT NULL : if payment type code = Payment amount %
1736 -- NULL : if payment type code = Fixed amount
1737 --+
1738 -- Check split_flag = N
1739 IF (p_pe_rec.split_flag <> 'N') THEN
1740 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1741 THEN
1742 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPLIT_FLAG_MUST_BE');
1743 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
1744 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1745 cn_api.get_lkup_meaning
1746 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1747 FND_MESSAGE.SET_TOKEN ('TOKEN1', G_TRX_GROUP||' = '||
1748 cn_api.get_lkup_meaning
1749 (p_pe_rec.trx_group_code,
1750 'QUOTA_TRX_GROUP'));
1751 FND_MESSAGE.SET_TOKEN ('TOKEN2', G_PAYMENT_TYPE||' = '||
1752 cn_api.get_lkup_meaning
1753 (p_pe_rec.payment_type_code,
1754 'QUOTA_PAYMENT_TYPE'));
1755 FND_MESSAGE.SET_TOKEN ('TOKEN3',NULL);
1756 FND_MSG_PUB.Add;
1757 END IF;
1758 x_loading_status := 'CN_SPLIT_FLAG_MUST_BE';
1759 RAISE FND_API.G_EXC_ERROR ;
1760 END IF;
1761 -- Check Payment Amount
1762 -- NOT NULL : if payment type code = Payment amount %
1763 -- NULL : if payment type code = Fixed amount
1764 IF p_pe_rec.payment_type_code = 'PAYMENT' THEN
1765 IF (cn_api.pe_num_field_cannot_null
1766 ( p_num_field => p_pe_rec.payment_amount,
1767 p_pe_type => p_pe_rec.quota_type_code,
1768 p_obj_name => G_PAYMENT_AMOUT,
1769 p_token1 => G_TRX_GROUP||' = '||
1770 cn_api.get_lkup_meaning
1771 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
1772 p_token2 => G_PAYMENT_TYPE||' = '||
1773 cn_api.get_lkup_meaning
1774 (p_pe_rec.payment_type_code,
1775 'QUOTA_PAYMENT_TYPE'),
1776 p_token3 => NULL ,
1777 p_loading_status => x_loading_status,
1778 x_loading_status => x_loading_status) = FND_API.g_false)
1779 THEN
1780 RAISE FND_API.G_EXC_ERROR ;
1781 END IF;
1782 ELSIF p_pe_rec.payment_type_code = 'FIXED' AND
1783 (cn_api.pe_num_field_must_null
1784 ( p_num_field => p_pe_rec.payment_amount,
1785 p_pe_type => p_pe_rec.quota_type_code,
1786 p_obj_name => G_PAYMENT_AMOUT,
1787 p_token1 => G_TRX_GROUP||' = '||
1788 cn_api.get_lkup_meaning
1789 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
1790 p_token2 => G_PAYMENT_TYPE||' = '||
1791 cn_api.get_lkup_meaning
1792 (p_pe_rec.payment_type_code,
1793 'QUOTA_PAYMENT_TYPE'),
1794 p_token3 => NULL ,
1795 p_loading_status => x_loading_status,
1796 x_loading_status => x_loading_status) = FND_API.g_false)
1797 THEN
1798 RAISE FND_API.G_EXC_ERROR ;
1799 END IF;
1800 END IF ; -- end IF payment_type_code = TRANSACTION
1801 END IF ; -- end INDIVIDUAL
1802
1803 -- Check rate table
1804 valid_rate_table
1805 ( x_return_status => x_return_status,
1806 p_pe_rec => p_pe_rec,
1807 p_loading_status => x_loading_status,
1808 x_loading_status => x_loading_status);
1809 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1810 RAISE FND_API.G_EXC_ERROR ;
1811 END IF;
1812 -- Check discount rate table
1813 IF (p_pe_rec.disc_option_code IN ('PAYMENT','QUOTA')) THEN
1814 valid_disc_rate_table
1815 ( x_return_status => x_return_status,
1816 p_pe_rec => p_pe_rec,
1817 p_loading_status => x_loading_status,
1818 x_loading_status => x_loading_status);
1819 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1820 RAISE FND_API.G_EXC_ERROR ;
1821 END IF;
1822 ELSE
1823 -- Check for Discount Rate Table must be NULL
1824 IF (cn_api.pe_num_field_must_null
1825 ( p_num_field => p_pe_rec.disc_rate_table_id,
1826 p_pe_type => p_pe_rec.quota_type_code,
1827 p_obj_name => G_DISC_RATE_TB,
1828 p_token1 =>
1829 G_DISC_OPTION||' = '||
1830 cn_api.get_lkup_meaning
1831 (p_pe_rec.disc_option_code,'DISCOUNT_OPTION'),
1832 p_token2 => NULL ,
1833 p_token3 => NULL ,
1834 p_loading_status => x_loading_status,
1835 x_loading_status => x_loading_status) = FND_API.G_FALSE) THEN
1836 RAISE FND_API.G_EXC_ERROR ;
1837 END IF;
1838 END IF;
1839 -- Check rc
1840 valid_revenue_class
1841 ( x_return_status => x_return_status,
1842 p_pe_rec => p_pe_rec,
1843 p_loading_status => x_loading_status,
1844 x_loading_status => x_loading_status);
1845 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1846 RAISE FND_API.G_EXC_ERROR ;
1847 END IF;
1848
1849 EXCEPTION
1850 WHEN FND_API.G_EXC_ERROR THEN
1851 x_return_status := FND_API.G_RET_STS_ERROR ;
1852
1853 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1855 x_loading_status := 'UNEXPECTED_ERR';
1856
1857 WHEN OTHERS THEN
1858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1859 x_loading_status := 'UNEXPECTED_ERR';
1860 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1861 THEN
1862 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1863 END IF;
1864 */
1865 fnd_message.set_name ('CN', 'CN_PACKAGE_OBSELETE');
1866 fnd_msg_pub.ADD;
1867 x_return_status := fnd_api.g_ret_sts_unexp_error;
1868 x_loading_status := 'CN_PACKAGE_OBSELETE';
1869 RAISE fnd_api.g_exc_error;
1870 END chk_revenue_non_quota_pe;
1871
1872 -- ----------------------------------------------------------------------------+
1873 -- Procedure: chk_unit_non_quota_pe
1874 -- Desc : Check input for UNIT NONE QUOTA type plan element
1875 -- ----------------------------------------------------------------------------+
1876 PROCEDURE chk_unit_non_quota_pe (
1877 x_return_status OUT NOCOPY VARCHAR2,
1878 p_pe_rec IN pe_rec_type := g_miss_pe_rec,
1879 p_loading_status IN VARCHAR2,
1880 x_loading_status OUT NOCOPY VARCHAR2
1881 )
1882 IS
1883 l_api_name CONSTANT VARCHAR2 (30) := 'chk_unit_non_quota_pe';
1884 l_yes fnd_lookups.meaning%TYPE;
1885 l_no fnd_lookups.meaning%TYPE;
1886 BEGIN
1887 /* x_return_status := FND_API.G_RET_STS_SUCCESS;
1888 x_loading_status := p_loading_status;
1889
1890 SELECT meaning INTO l_yes FROM fnd_lookups
1891 WHERE lookup_code = 'Y' AND lookup_type = 'YES_NO';
1892 SELECT meaning INTO l_no FROM fnd_lookups
1893 WHERE lookup_code = 'N' AND lookup_type = 'YES_NO';
1894 --+
1895 -- Validate Rule :
1896 -- target = 0, ITD Flag = N
1897 --+
1898 -- Check target = 0
1899 IF (p_pe_rec.target <> 0) THEN
1900 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1901 THEN
1902 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_TARGET_MUST_BE');
1903 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE','= 0');
1904 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1905 cn_api.get_lkup_meaning
1906 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1907 FND_MESSAGE.SET_TOKEN ('TOKEN1',NULL);
1908 FND_MSG_PUB.Add;
1909 END IF;
1910 x_loading_status := 'CN_PE_TARGET_MUST_BE';
1911 RAISE FND_API.G_EXC_ERROR ;
1912 END IF;
1913 -- Check itd_flag = N
1914 IF (p_pe_rec.itd_flag <> 'N') THEN
1915 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1916 THEN
1917 FND_MESSAGE.SET_NAME ('CN' , 'CN_ITD_FLAG_MUST_BE');
1918 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
1919 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1920 cn_api.get_lkup_meaning
1921 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1922 FND_MESSAGE.SET_TOKEN ('TOKEN1',NULL);
1923 FND_MSG_PUB.Add;
1924 END IF;
1925 x_loading_status := 'CN_ITD_FLAG_MUST_BE';
1926 RAISE FND_API.G_EXC_ERROR ;
1927 END IF;
1928 -- Check input for 'Group By' case (trx_group_code = 'GROUP')
1929 --+
1930 IF p_pe_rec.trx_group_code = 'GROUP' THEN
1931 --+
1932 -- Validate Rule : Groupby
1933 -- Cumulative Flag = N ,split flag = N ,
1934 -- Payment Amount
1935 -- NOT NULL : if payment type code = Payment amount %
1936 -- NULL : if payment type code = Fixed amount or applied Trx %
1937 --+
1938 -- Check Cumulative Flag = N
1939 IF (p_pe_rec.cumulative_flag <> 'N') THEN
1940 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1941 THEN
1942 FND_MESSAGE.SET_NAME ('CN' , 'CN_CUM_FLAG_MUST_BE');
1943 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
1944 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1945 cn_api.get_lkup_meaning
1946 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1947 FND_MESSAGE.SET_TOKEN ('TOKEN1', G_TRX_GROUP||' = '||
1948 cn_api.get_lkup_meaning
1949 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'))
1950 ;
1951 FND_MSG_PUB.Add;
1952 END IF;
1953 x_loading_status := 'CN_CUM_FLAG_MUST_BE';
1954 RAISE FND_API.G_EXC_ERROR ;
1955 END IF;
1956 -- Check split_flag = N
1957 IF (p_pe_rec.split_flag <> 'N') THEN
1958 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1959 THEN
1960 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPLIT_FLAG_MUST_BE');
1961 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
1962 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
1963 cn_api.get_lkup_meaning
1964 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
1965 FND_MESSAGE.SET_TOKEN ('TOKEN1', G_TRX_GROUP||' = '||
1966 cn_api.get_lkup_meaning
1967 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'))
1968 ;
1969 FND_MESSAGE.SET_TOKEN ('TOKEN2',NULL);
1970 FND_MESSAGE.SET_TOKEN ('TOKEN3',NULL);
1971 FND_MSG_PUB.Add;
1972 END IF;
1973 x_loading_status := 'CN_SPLIT_FLAG_MUST_BE';
1974 RAISE FND_API.G_EXC_ERROR ;
1975 END IF;
1976 -- Check Payment Amount
1977 -- NOT NULL : if payment type code = Payment amount %
1978 -- NULL : if payment type code = Fixed amount or applied Trx %
1979 IF p_pe_rec.payment_type_code = 'PAYMENT' THEN
1980 IF (cn_api.pe_num_field_cannot_null
1981 ( p_num_field => p_pe_rec.payment_amount,
1982 p_pe_type => p_pe_rec.quota_type_code,
1983 p_obj_name => G_PAYMENT_AMOUT,
1984 p_token1 => G_TRX_GROUP||' = '||
1985 cn_api.get_lkup_meaning
1986 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
1987 p_token2 => G_PAYMENT_TYPE||' = '||
1988 cn_api.get_lkup_meaning
1989 (p_pe_rec.payment_type_code,
1990 'QUOTA_PAYMENT_TYPE'),
1991 p_token3 => NULL ,
1992 p_loading_status => x_loading_status,
1993 x_loading_status => x_loading_status) = FND_API.g_false)
1994 THEN
1995 RAISE FND_API.G_EXC_ERROR ;
1996 END IF;
1997 ELSIF p_pe_rec.payment_type_code IN ('TRANSACTION','FIXED') AND
1998 (cn_api.pe_num_field_must_null
1999 ( p_num_field => p_pe_rec.payment_amount,
2000 p_pe_type => p_pe_rec.quota_type_code,
2001 p_obj_name => G_PAYMENT_AMOUT,
2002 p_token1 => G_TRX_GROUP||' = '||
2003 cn_api.get_lkup_meaning
2004 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
2005 p_token2 => G_PAYMENT_TYPE||' = '||
2006 cn_api.get_lkup_meaning
2007 (p_pe_rec.payment_type_code,
2008 'QUOTA_PAYMENT_TYPE'),
2009 p_token3 => NULL ,
2010 p_loading_status => x_loading_status,
2011 x_loading_status => x_loading_status) = FND_API.g_false)
2012 THEN
2013 RAISE FND_API.G_EXC_ERROR ;
2014 END IF;
2015 END IF ; -- end GROUP BY
2016
2017 --+
2018 -- Check input for 'Individual' case (trx_group_code = 'INDIVIDUAL')
2019 --+
2020 IF p_pe_rec.trx_group_code = 'INDIVIDUAL' THEN
2021 IF p_pe_rec.payment_type_code = 'TRANSACTION' THEN
2022 -- Check for Payment Type = Applied Trx % case
2023 --+
2024 -- Validate Rule :
2025 -- payment amount = NULL
2026 -- split flag = N if cumulative flag = N
2027 --+
2028 -- Check payment amount = NULL
2029 IF (cn_api.pe_num_field_must_null
2030 ( p_num_field => p_pe_rec.payment_amount,
2031 p_pe_type => p_pe_rec.quota_type_code,
2032 p_obj_name => G_PAYMENT_AMOUT,
2033 p_token1 => G_TRX_GROUP||' = '||
2034 cn_api.get_lkup_meaning
2035 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
2036 p_token2 => G_PAYMENT_TYPE||' = '||
2037 cn_api.get_lkup_meaning
2038 (p_pe_rec.payment_type_code,
2039 'QUOTA_PAYMENT_TYPE'),
2040 p_token3 => NULL ,
2041 p_loading_status => x_loading_status,
2042 x_loading_status => x_loading_status) = FND_API.g_false)
2043 THEN
2044 RAISE FND_API.G_EXC_ERROR ;
2045 END IF;
2046 -- Check split flag = N if cumulative flag = N
2047 IF (p_pe_rec.cumulative_flag = 'N') AND
2048 (p_pe_rec.split_flag <> 'N') THEN
2049 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2050 THEN
2051 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPLIT_FLAG_MUST_BE');
2052 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
2053 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
2054 cn_api.get_lkup_meaning
2055 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
2056 FND_MESSAGE.SET_TOKEN ('TOKEN1', G_TRX_GROUP||' = '||
2057 cn_api.get_lkup_meaning
2058 (p_pe_rec.trx_group_code,
2059 'QUOTA_TRX_GROUP'));
2060 FND_MESSAGE.SET_TOKEN ('TOKEN2', G_PAYMENT_TYPE||' = '||
2061 cn_api.get_lkup_meaning
2062 (p_pe_rec.payment_type_code,
2063 'QUOTA_PAYMENT_TYPE'));
2064 FND_MESSAGE.SET_TOKEN ('TOKEN3', G_ACCMULATE||' = '||
2065 p_pe_rec.cumulative_flag);
2066 FND_MSG_PUB.Add;
2067 END IF;
2068 x_loading_status := 'CN_SPLIT_FLAG_MUST_BE';
2069 RAISE FND_API.G_EXC_ERROR ;
2070 END IF;
2071 ELSIF p_pe_rec.payment_type_code IN ('PAYMENT','FIXED') THEN
2072 -- Check for Payment Type = Payment Amount % or Fixed Amount case
2073 --+
2074 -- Validate Rule :
2075 -- split flag = N
2076 -- Payment Amount
2077 -- NOT NULL : if payment type code = Payment amount %
2078 -- NULL : if payment type code = Fixed amount
2079 --+
2080 -- Check split_flag = N
2081 IF (p_pe_rec.split_flag <> 'N') THEN
2082 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2083 THEN
2084 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPLIT_FLAG_MUST_BE');
2085 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
2086 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
2087 cn_api.get_lkup_meaning
2088 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
2089 FND_MESSAGE.SET_TOKEN ('TOKEN1', G_TRX_GROUP||' = '||
2090 cn_api.get_lkup_meaning
2091 (p_pe_rec.trx_group_code,
2092 'QUOTA_TRX_GROUP'));
2093 FND_MESSAGE.SET_TOKEN ('TOKEN2',G_PAYMENT_TYPE||' = '||
2094 cn_api.get_lkup_meaning
2095 (p_pe_rec.payment_type_code,
2096 'QUOTA_PAYMENT_TYPE'));
2097 FND_MESSAGE.SET_TOKEN ('TOKEN3',NULL);
2098 FND_MSG_PUB.Add;
2099 END IF;
2100 x_loading_status := 'CN_SPLIT_FLAG_MUST_BE';
2101 RAISE FND_API.G_EXC_ERROR ;
2102 END IF;
2103 -- Check Payment Amount
2104 -- NOT NULL : if payment type code = Payment amount %
2105 -- NULL : if payment type code = Fixed amount
2106 IF p_pe_rec.payment_type_code = 'PAYMENT' THEN
2107 IF (cn_api.pe_num_field_cannot_null
2108 ( p_num_field => p_pe_rec.payment_amount,
2109 p_pe_type => p_pe_rec.quota_type_code,
2110 p_obj_name => G_PAYMENT_AMOUT,
2111 p_token1 => G_TRX_GROUP||' = '||
2112 cn_api.get_lkup_meaning
2113 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
2114 p_token2 => G_PAYMENT_TYPE||' = '||
2115 cn_api.get_lkup_meaning
2116 (p_pe_rec.payment_type_code,
2117 'QUOTA_PAYMENT_TYPE'),
2118 p_token3 => NULL ,
2119 p_loading_status => x_loading_status,
2120 x_loading_status => x_loading_status) = FND_API.g_false)
2121 THEN
2122 RAISE FND_API.G_EXC_ERROR ;
2123 END IF;
2124 ELSIF p_pe_rec.payment_type_code = 'FIXED' AND
2125 (cn_api.pe_num_field_must_null
2126 ( p_num_field => p_pe_rec.payment_amount,
2127 p_pe_type => p_pe_rec.quota_type_code,
2128 p_obj_name => G_PAYMENT_AMOUT,
2129 p_token1 => G_TRX_GROUP||' = '||
2130 cn_api.get_lkup_meaning
2131 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
2132 p_token2 => G_PAYMENT_TYPE||' = '||
2133 cn_api.get_lkup_meaning
2134 (p_pe_rec.payment_type_code,
2135 'QUOTA_PAYMENT_TYPE'),
2136 p_token3 => NULL ,
2137 p_loading_status => x_loading_status,
2138 x_loading_status => x_loading_status) = FND_API.g_false)
2139 THEN
2140 RAISE FND_API.G_EXC_ERROR ;
2141 END IF;
2142 END IF ; -- end IF payment_type_code = TRANSACTION
2143 END IF ; -- end INDIVIDUAL
2144
2145 -- Check rate table
2146 valid_rate_table
2147 ( x_return_status => x_return_status,
2148 p_pe_rec => p_pe_rec,
2149 p_loading_status => x_loading_status,
2150 x_loading_status => x_loading_status);
2151 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2152 RAISE FND_API.G_EXC_ERROR ;
2153 END IF;
2154 -- Check discount rate table = Not Allowed
2155 -- Discount Option Code must = NONE is validate when calling
2156 -- valid_lookup_code() from valid_plan_element
2157 IF (cn_api.pe_num_field_must_null
2158 ( p_num_field => p_pe_rec.disc_rate_table_id,
2159 p_pe_type => p_pe_rec.quota_type_code,
2160 p_obj_name => G_DISC_RATE_TB,
2161 p_token1 => NULL ,
2162 p_token2 => NULL ,
2163 p_token3 => NULL ,
2164 p_loading_status => x_loading_status,
2165 x_loading_status => x_loading_status) = FND_API.g_false)
2166 THEN
2167 RAISE FND_API.G_EXC_ERROR ;
2168 END IF;
2169 -- Check rc
2170 valid_revenue_class
2171 ( x_return_status => x_return_status,
2172 p_pe_rec => p_pe_rec,
2173 p_loading_status => x_loading_status,
2174 x_loading_status => x_loading_status);
2175 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2176 RAISE FND_API.G_EXC_ERROR ;
2177 END IF;
2178
2179 EXCEPTION
2180 WHEN FND_API.G_EXC_ERROR THEN
2181 x_return_status := FND_API.G_RET_STS_ERROR ;
2182
2183 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2185 x_loading_status := 'UNEXPECTED_ERR';
2186
2187 WHEN OTHERS THEN
2188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2189 x_loading_status := 'UNEXPECTED_ERR';
2190 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2191 THEN
2192 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2193 END IF;
2194 */
2195 fnd_message.set_name ('CN', 'CN_PACKAGE_OBSELETE');
2196 fnd_msg_pub.ADD;
2197 x_return_status := fnd_api.g_ret_sts_unexp_error;
2198 x_loading_status := 'CN_PACKAGE_OBSELETE';
2199 RAISE fnd_api.g_exc_error;
2200 END chk_unit_non_quota_pe;
2201
2202 -- ----------------------------------------------------------------------------+
2203 -- Procedure: chk_discount_margin_pe
2204 -- Desc : Check input for DISCOUNT or MARGIN type plan element
2205 -- ----------------------------------------------------------------------------+
2206 PROCEDURE chk_discount_margin_pe (
2207 x_return_status OUT NOCOPY VARCHAR2,
2208 p_pe_rec IN pe_rec_type := g_miss_pe_rec,
2209 p_loading_status IN VARCHAR2,
2210 x_loading_status OUT NOCOPY VARCHAR2
2211 )
2212 IS
2213 l_api_name CONSTANT VARCHAR2 (30) := 'chk_disc_margin_pe';
2214 l_yes fnd_lookups.meaning%TYPE;
2215 l_no fnd_lookups.meaning%TYPE;
2216 BEGIN
2217 /* x_return_status := FND_API.G_RET_STS_SUCCESS;
2218 x_loading_status := p_loading_status;
2219
2220 SELECT meaning INTO l_yes FROM fnd_lookups
2221 WHERE lookup_code = 'Y' AND lookup_type = 'YES_NO';
2222 SELECT meaning INTO l_no FROM fnd_lookups
2223 WHERE lookup_code = 'N' AND lookup_type = 'YES_NO';
2224 --+
2225 -- Validate Rule :
2226 -- target = 0
2227 -- split_flag = N, cumulative_flag = N , itd_flag = N
2228 -- Apply Txn Type = 'GroupBy' NOT ALLOWED
2229 -- Payment Amount
2230 -- NOT NULL : if payment type code = Payment amount %
2231 -- NULL : if payment type code = Fixed amount or applied Trx %
2232 --+
2233 -- Check target = 0
2234 IF (p_pe_rec.target <> 0) THEN
2235 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2236 THEN
2237 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_TARGET_MUST_BE');
2238 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE','= 0');
2239 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
2240 cn_api.get_lkup_meaning
2241 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
2242 FND_MESSAGE.SET_TOKEN ('TOKEN1',NULL);
2243 FND_MSG_PUB.Add;
2244 END IF;
2245 x_loading_status := 'CN_PE_TARGET_MUST_BE';
2246 RAISE FND_API.G_EXC_ERROR ;
2247 END IF;
2248 -- Check split_flag = N
2249 IF (p_pe_rec.split_flag <> 'N') THEN
2250 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2251 THEN
2252 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPLIT_FLAG_MUST_BE');
2253 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
2254 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
2255 cn_api.get_lkup_meaning
2256 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
2257 FND_MESSAGE.SET_TOKEN ('TOKEN1',NULL);
2258 FND_MESSAGE.SET_TOKEN ('TOKEN2',NULL);
2259 FND_MESSAGE.SET_TOKEN ('TOKEN3',NULL);
2260 FND_MSG_PUB.Add;
2261 END IF;
2262 x_loading_status := 'CN_SPLIT_FLAG_MUST_BE';
2263 RAISE FND_API.G_EXC_ERROR ;
2264 END IF;
2265 -- Check cumulative_flag = N for Discount and Margin PE type
2266 IF (p_pe_rec.cumulative_flag <> 'N') THEN
2267 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2268 THEN
2269 FND_MESSAGE.SET_NAME ('CN' , 'CN_CUM_FLAG_MUST_BE');
2270 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
2271 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
2272 cn_api.get_lkup_meaning
2273 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
2274 FND_MESSAGE.SET_TOKEN ('TOKEN1',NULL);
2275 FND_MSG_PUB.Add;
2276 END IF;
2277 x_loading_status := 'CN_CUM_FLAG_MUST_BE';
2278 RAISE FND_API.G_EXC_ERROR ;
2279 END IF;
2280 -- Check itd_flag = N for Discount and Margin PE type
2281 IF (p_pe_rec.itd_flag <> 'N') THEN
2282 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2283 THEN
2284 FND_MESSAGE.SET_NAME ('CN' , 'CN_ITD_FLAG_MUST_BE');
2285 FND_MESSAGE.SET_TOKEN ('OBJ_VALUE',l_no);
2286 FND_MESSAGE.SET_TOKEN ('PLAN_TYPE',
2287 cn_api.get_lkup_meaning
2288 (p_pe_rec.quota_type_code,'QUOTA_TYPE'));
2289 FND_MESSAGE.SET_TOKEN ('TOKEN1',NULL);
2290 FND_MSG_PUB.Add;
2291 END IF;
2292 x_loading_status := 'CN_ITD_FLAG_MUST_BE';
2293 RAISE FND_API.G_EXC_ERROR ;
2294 END IF;
2295 -- Check Apply Txn Type = 'GroupBy' NOT ALLOWED
2296 IF p_pe_rec.trx_group_code = 'GROUP' THEN
2297 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2298 THEN
2299 FND_MESSAGE.SET_NAME ('CN' , 'DISC_MARGIN_INDIVIDUAL_ONLY');
2300 FND_MSG_PUB.Add;
2301 END IF;
2302 x_loading_status := 'DISC_MARGIN_INDIVIDUAL_ONLY';
2303 RAISE FND_API.G_EXC_ERROR ;
2304 END IF;
2305 -- Check Payment Amount
2306 -- NOT NULL : if payment type code = Payment amount %
2307 -- NULL : if payment type code = Fixed amount or applied Trx %
2308 IF p_pe_rec.payment_type_code = 'PAYMENT' THEN
2309 IF (cn_api.pe_num_field_cannot_null
2310 ( p_num_field => p_pe_rec.payment_amount,
2311 p_pe_type => p_pe_rec.quota_type_code,
2312 p_obj_name => G_PAYMENT_AMOUT,
2313 p_token1 => G_TRX_GROUP||' = '||
2314 cn_api.get_lkup_meaning
2315 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
2316 p_token2 => G_PAYMENT_TYPE||' = '||
2317 cn_api.get_lkup_meaning
2318 (p_pe_rec.payment_type_code,
2319 'QUOTA_PAYMENT_TYPE'),
2320 p_token3 => NULL ,
2321 p_loading_status => x_loading_status,
2322 x_loading_status => x_loading_status) = FND_API.g_false)
2323 THEN
2324 RAISE FND_API.G_EXC_ERROR ;
2325 END IF;
2326 ELSIF p_pe_rec.payment_type_code IN ('TRANSACTION','FIXED') AND
2327 (cn_api.pe_num_field_must_null
2328 ( p_num_field => p_pe_rec.payment_amount,
2329 p_pe_type => p_pe_rec.quota_type_code,
2330 p_obj_name => G_PAYMENT_AMOUT,
2331 p_token1 => G_TRX_GROUP||' = '||
2332 cn_api.get_lkup_meaning
2333 (p_pe_rec.trx_group_code,'QUOTA_TRX_GROUP'),
2334 p_token2 => G_PAYMENT_TYPE||' = '||
2335 cn_api.get_lkup_meaning
2336 (p_pe_rec.payment_type_code,
2337 'QUOTA_PAYMENT_TYPE'),
2338 p_token3 => NULL ,
2339 p_loading_status => x_loading_status,
2340 x_loading_status => x_loading_status) = FND_API.g_false)
2341 THEN
2342 RAISE FND_API.G_EXC_ERROR ;
2343 END IF;
2344 -- Check rate table
2345 valid_rate_table
2346 ( x_return_status => x_return_status,
2347 p_pe_rec => p_pe_rec,
2348 p_loading_status => x_loading_status,
2349 x_loading_status => x_loading_status);
2350 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2351 RAISE FND_API.G_EXC_ERROR ;
2352 END IF;
2353 -- Check discount rate table = Not Allowed
2354 IF (cn_api.pe_num_field_must_null
2355 ( p_num_field => p_pe_rec.disc_rate_table_id,
2356 p_pe_type => p_pe_rec.quota_type_code,
2357 p_obj_name => G_DISC_RATE_TB,
2358 p_token1 => NULL ,
2359 p_token2 => NULL ,
2360 p_token3 => NULL ,
2361 p_loading_status => x_loading_status,
2362 x_loading_status => x_loading_status) = FND_API.g_false)
2363 THEN
2364 RAISE FND_API.G_EXC_ERROR ;
2365 END IF;
2366
2367 -- Check rc
2368 valid_revenue_class
2369 ( x_return_status => x_return_status,
2370 p_pe_rec => p_pe_rec,
2371 p_loading_status => x_loading_status,
2372 x_loading_status => x_loading_status);
2373 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2374 RAISE FND_API.G_EXC_ERROR ;
2375 END IF;
2376
2377 EXCEPTION
2378 WHEN FND_API.G_EXC_ERROR THEN
2379 x_return_status := FND_API.G_RET_STS_ERROR ;
2380
2381 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2383 x_loading_status := 'UNEXPECTED_ERR';
2384
2385 WHEN OTHERS THEN
2386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2387 x_loading_status := 'UNEXPECTED_ERR';
2388 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2389 THEN
2390 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2391 END IF;
2392 */
2393 fnd_message.set_name ('CN', 'CN_PACKAGE_OBSELETE');
2394 fnd_msg_pub.ADD;
2395 x_return_status := fnd_api.g_ret_sts_unexp_error;
2396 x_loading_status := 'CN_PACKAGE_OBSELETE';
2397 RAISE fnd_api.g_exc_error;
2398 END chk_discount_margin_pe;
2399
2400 -- ----------------------------------------------------------------------------+
2401 -- Procedure: chk_trx_factor
2402 -- Desc : Check Trx Factors
2403 -- Error when
2404 -- 1. No factors assigned
2405 -- 2. key factors don't total to 100% (Warning)
2406 -- ----------------------------------------------------------------------------+
2407 PROCEDURE chk_trx_factor (
2408 x_return_status OUT NOCOPY VARCHAR2,
2409 p_quota_rule_id NUMBER,
2410 p_rev_class_name VARCHAR2,
2411 p_loading_status IN VARCHAR2,
2412 x_loading_status OUT NOCOPY VARCHAR2
2413 )
2414 IS
2415 CURSOR c_factors
2416 IS
2417 SELECT event_factor,
2418 trx_type
2419 FROM cn_trx_factors
2420 WHERE quota_rule_id = p_quota_rule_id;
2421
2422 l_factor_csr c_factors%ROWTYPE;
2423 key_factor_total NUMBER;
2424 l_api_name CONSTANT VARCHAR2 (30) := 'chk_trx_factor';
2425 l_pe_name cn_quotas.NAME%TYPE;
2426 BEGIN
2427 x_return_status := fnd_api.g_ret_sts_success;
2428 x_loading_status := p_loading_status;
2429 key_factor_total := 0;
2430
2431 OPEN c_factors;
2432
2433 LOOP
2434 FETCH c_factors
2435 INTO l_factor_csr;
2436
2437 IF c_factors%ROWCOUNT = 0
2438 THEN
2439 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2440 THEN
2441 fnd_message.set_name ('CN', 'PLN_QUOTA_RULE_NO_FACTORS');
2442 fnd_message.set_token ('REV_CLASS_NAME', p_rev_class_name);
2443 fnd_msg_pub.ADD;
2444 END IF;
2445
2446 x_loading_status := 'PLN_QUOTA_RULE_NO_FACTORS';
2447 RAISE fnd_api.g_exc_error;
2448 ELSE
2449 IF c_factors%NOTFOUND
2450 THEN
2451 IF key_factor_total <> 100
2452 THEN
2453 -- Warning message only.
2454 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2455 THEN
2456 SELECT q.NAME
2457 INTO l_pe_name
2458 FROM cn_quotas q,
2459 cn_quota_rules qr
2460 WHERE qr.quota_rule_id = p_quota_rule_id AND q.quota_id = qr.quota_id;
2461
2462 fnd_message.set_name ('CN', 'PLN_QUOTA_RULE_FACTORS_NOT_100');
2463 fnd_message.set_token ('PLAN_NAME', NULL);
2464 fnd_message.set_token ('QUOTA_NAME', l_pe_name);
2465 fnd_message.set_token ('REV_CLASS_NAME', p_rev_class_name);
2466 fnd_msg_pub.ADD;
2467 RAISE fnd_api.g_exc_error;
2468 END IF;
2469
2470 x_loading_status := 'PLN_QUOTA_RULE_FACTORS_NOT_100';
2471 GOTO end_loop;
2472 END IF;
2473
2474 EXIT; -- exit loop
2475 ELSE
2476 IF (l_factor_csr.trx_type = 'ORD' OR l_factor_csr.trx_type = 'INV' OR l_factor_csr.trx_type = 'PMT')
2477 THEN
2478 key_factor_total := key_factor_total + l_factor_csr.event_factor;
2479
2480 END IF;
2481 END IF; -- sqlnotfound
2482 END IF; -- rowcount
2483 END LOOP;
2484
2485 <<end_loop>>
2486 NULL;
2487
2488
2489 CLOSE c_factors;
2490 END chk_trx_factor;
2491
2492 --| -----------------------------------------------------------------------+
2493 --| Function Name : Get_Quota_id
2494 --| ---------------------------------------------------------------------+
2495 FUNCTION get_quota_id (
2496 p_quota_name VARCHAR2,
2497 p_org_id NUMBER
2498 )
2499 RETURN cn_quotas.quota_id%TYPE
2500 IS
2501 l_quota_id cn_quotas.quota_id%TYPE;
2502 BEGIN
2503 SELECT quota_id
2504 INTO l_quota_id
2505 FROM cn_quotas_v
2506 WHERE NAME = p_quota_name
2507 AND org_id = p_org_id ;
2508
2509 RETURN l_quota_id;
2510 EXCEPTION
2511 WHEN NO_DATA_FOUND
2512 THEN
2513 RETURN NULL;
2514 END get_quota_id;
2515
2516 --| -----------------------------------------------------------------------+
2517 --| Function Name : Get_calc_formula_name
2518 --| ---------------------------------------------------------------------+
2519 FUNCTION get_calc_formula_name (
2520 p_calc_formula_id NUMBER
2521 )
2522 RETURN cn_calc_formulas.NAME%TYPE
2523 IS
2524 l_cf_name cn_calc_formulas.NAME%TYPE;
2525 BEGIN
2526 SELECT NAME
2527 INTO l_cf_name
2528 FROM cn_calc_formulas
2529 WHERE calc_formula_id = p_calc_formula_id;
2530
2531 RETURN l_cf_name;
2532 EXCEPTION
2533 WHEN NO_DATA_FOUND
2534 THEN
2535 RETURN NULL;
2536 END get_calc_formula_name;
2537
2538 --| -----------------------------------------------------------------------+
2539 --| Function Name : Get_calc_formula_name
2540 --| ---------------------------------------------------------------------+
2541 FUNCTION get_calc_formula_id (
2542 p_calc_formula_name VARCHAR2,
2543 p_org_id NUMBER
2544 )
2545 RETURN cn_calc_formulas.calc_formula_id%TYPE
2546 IS
2547 l_cf_id cn_calc_formulas.calc_formula_id%TYPE;
2548 BEGIN
2549 SELECT calc_formula_id
2550 INTO l_cf_id
2551 FROM cn_calc_formulas
2552 WHERE NAME = p_calc_formula_name and org_id = p_org_id;
2553
2554 RETURN l_cf_id;
2555 EXCEPTION
2556 WHEN NO_DATA_FOUND
2557 THEN
2558 RETURN NULL;
2559 END get_calc_formula_id;
2560
2561 --| -----------------------------------------------------------------------+
2562 --| Function Name : Get_Credit_Type
2563 --| ---------------------------------------------------------------------+
2564 FUNCTION get_credit_type (
2565 p_credit_type_id NUMBER
2566 )
2567 RETURN cn_credit_types.NAME%TYPE
2568 IS
2569 l_c_type cn_credit_types.NAME%TYPE;
2570 BEGIN
2571 SELECT NAME
2572 INTO l_c_type
2573 FROM cn_credit_types
2574 WHERE credit_type_id = p_credit_type_id;
2575
2576 RETURN l_c_type;
2577 EXCEPTION
2578 WHEN NO_DATA_FOUND
2579 THEN
2580 RETURN NULL;
2581 END get_credit_type;
2582
2583 --| -----------------------------------------------------------------------+
2584 --| Function Name : get_interval_name
2585 --| Desc : To Get the Interval Name using the Interval Type ID
2586 --| ---------------------------------------------------------------------+
2587 FUNCTION get_interval_name (
2588 p_interval_type_id NUMBER,
2589 p_org_id NUMBER
2590 )
2591 RETURN cn_interval_types.NAME%TYPE
2592 IS
2593 l_name cn_interval_types.NAME%TYPE;
2594 BEGIN
2595 SELECT NAME
2596 INTO l_name
2597 FROM cn_interval_types
2598 WHERE interval_type_id = p_interval_type_id
2599 AND org_id = p_org_id;
2600
2601 RETURN l_name;
2602 EXCEPTION
2603 WHEN NO_DATA_FOUND
2604 THEN
2605 RETURN NULL;
2606 END get_interval_name;
2607
2608 --| -----------------------------------------------------------------------+
2609 --| Function Name : get_quota_rule_id
2610 --| Desc : Get the Quota Rule ID using the quota_id, Revenue_class_id
2611 --| ---------------------------------------------------------------------+
2612 FUNCTION get_quota_rule_id (
2613 p_quota_id NUMBER,
2614 p_rev_class_id NUMBER
2615 )
2616 RETURN cn_quota_rules.quota_rule_id%TYPE
2617 IS
2618 l_quota_rule_id cn_quota_rules.quota_rule_id%TYPE;
2619 BEGIN
2620 SELECT quota_rule_id
2621 INTO l_quota_rule_id
2622 FROM cn_quota_rules
2623 WHERE quota_id = p_quota_id AND revenue_class_id = p_rev_class_id;
2624
2625 RETURN l_quota_rule_id;
2626 EXCEPTION
2627 WHEN NO_DATA_FOUND
2628 THEN
2629 RETURN NULL;
2630 END get_quota_rule_id;
2631
2632 --| -----------------------------------------------------------------------+
2633 --| Function Name : get_uplift_start_date
2634 --| Desc : Get theuplift start Date using quota id, quota Rule ID
2635 --| ---------------------------------------------------------------------+
2636 FUNCTION get_uplift_start_date (
2637 p_quota_rule_id NUMBER
2638 )
2639 RETURN cn_quota_rule_uplifts.start_date%TYPE
2640 IS
2641 l_start_date cn_quota_rule_uplifts.start_date%TYPE;
2642
2643 CURSOR get_date
2644 IS
2645 SELECT end_date + 1
2646 FROM cn_quota_rule_uplifts
2647 WHERE quota_rule_id = p_quota_rule_id
2648 ORDER BY end_date DESC;
2649 BEGIN
2650 OPEN get_date;
2651
2652 FETCH get_date
2653 INTO l_start_date;
2654
2655 CLOSE get_date;
2656
2657 RETURN l_start_date;
2658 END get_uplift_start_date;
2659
2660 --| -----------------------------------------------------------------------+
2661 --| Function Name : get_quota_rule_uplift_id
2662 --| Desc : Get the Quota Rule UPLIFT ID using the quota_rule_id,
2663 -- start Date, end Date
2664 --| ---------------------------------------------------------------------+
2665 FUNCTION get_quota_rule_uplift_id (
2666 p_quota_rule_id NUMBER,
2667 p_start_date DATE,
2668 p_end_date DATE
2669 )
2670 RETURN cn_quota_rule_uplifts.quota_rule_uplift_id%TYPE
2671 IS
2672 l_quota_rule_uplift_id cn_quota_rule_uplifts.quota_rule_uplift_id%TYPE;
2673
2674 CURSOR get_quota_rule_uplift_id_curs
2675 IS
2676 SELECT quota_rule_uplift_id
2677 FROM cn_quota_rule_uplifts
2678 WHERE quota_rule_id = p_quota_rule_id AND TRUNC (start_date) = TRUNC (p_start_date) AND TRUNC (end_date) = TRUNC (p_end_date);
2679
2680 CURSOR get_quota_rule_uplift_id_curs1
2681 IS
2682 SELECT quota_rule_uplift_id
2683 FROM cn_quota_rule_uplifts
2684 WHERE quota_rule_id = p_quota_rule_id AND TRUNC (start_date) = TRUNC (p_start_date) AND TRUNC (end_date) IS NULL;
2685 BEGIN
2686 IF p_end_date IS NOT NULL
2687 THEN
2688 OPEN get_quota_rule_uplift_id_curs;
2689
2690 FETCH get_quota_rule_uplift_id_curs
2691 INTO l_quota_rule_uplift_id;
2692
2693 CLOSE get_quota_rule_uplift_id_curs;
2694 ELSE
2695 OPEN get_quota_rule_uplift_id_curs1;
2696
2697 FETCH get_quota_rule_uplift_id_curs1
2698 INTO l_quota_rule_uplift_id;
2699
2700 CLOSE get_quota_rule_uplift_id_curs1;
2701 END IF;
2702
2703 RETURN l_quota_rule_uplift_id;
2704 END get_quota_rule_uplift_id;
2705
2706 --| -----------------------------------------------------------------------+
2707 --| Function Name : get_rt_quota_asgn_id
2708 --| Desc : Get the rt Quota Asgn ID using the quota_id,
2709 --| start Date, end Date
2710 --| ---------------------------------------------------------------------+
2711 FUNCTION get_rt_quota_asgn_id (
2712 p_quota_id NUMBER,
2713 p_rate_schedule_id NUMBER,
2714 p_calc_formula_id NUMBER,
2715 p_start_date DATE,
2716 p_end_date DATE
2717 )
2718 RETURN cn_rt_quota_asgns.rt_quota_asgn_id%TYPE
2719 IS
2720 l_rt_quota_asgn_id cn_rt_quota_asgns.rt_quota_asgn_id%TYPE;
2721
2722 CURSOR get_rt_quota_asgn_id_curs
2723 IS
2724 SELECT rt_quota_asgn_id
2725 FROM cn_rt_quota_asgns
2726 WHERE quota_id = p_quota_id
2727 AND rate_schedule_id = p_rate_schedule_id
2728 AND calc_formula_id = p_calc_formula_id
2729 AND TRUNC (start_date) = TRUNC (p_start_date)
2730 AND TRUNC (end_date) = TRUNC (p_end_date);
2731
2732 CURSOR get_rt_quota_asgn_id_curs1
2733 IS
2734 SELECT rt_quota_asgn_id
2735 FROM cn_rt_quota_asgns
2736 WHERE quota_id = p_quota_id
2737 AND rate_schedule_id = p_rate_schedule_id
2738 AND calc_formula_id = p_calc_formula_id
2739 AND TRUNC (start_date) = TRUNC (p_start_date)
2740 AND TRUNC (end_date) IS NULL;
2741 BEGIN
2742 IF p_end_date IS NOT NULL
2743 THEN
2744 OPEN get_rt_quota_asgn_id_curs;
2745
2746 FETCH get_rt_quota_asgn_id_curs
2747 INTO l_rt_quota_asgn_id;
2748
2749 CLOSE get_rt_quota_asgn_id_curs;
2750 ELSE
2751 OPEN get_rt_quota_asgn_id_curs1;
2752
2753 FETCH get_rt_quota_asgn_id_curs1
2754 INTO l_rt_quota_asgn_id;
2755
2756 CLOSE get_rt_quota_asgn_id_curs1;
2757 END IF;
2758
2759 RETURN l_rt_quota_asgn_id;
2760 END get_rt_quota_asgn_id;
2761
2762 -- This Procedure check whether the
2763 -- whether Parent Plan Element's date range is within the referenced Element's
2764 -- date range
2765 PROCEDURE check_create_pe_self_ref (
2766 x_calc_formula_id IN NUMBER,
2767 x_parent_start_date IN DATE,
2768 x_parent_end_date IN DATE
2769 )
2770 IS
2771 l_parent_calc_formula_id cn_calc_formulas.calc_formula_id%TYPE;
2772 l_child_calc_formula_id cn_calc_formulas.calc_formula_id%TYPE;
2773 parent_quota_id NUMBER;
2774 child_quota_id NUMBER;
2775 nt cn_calc_sql_exps_pvt.num_tbl_type;
2776 new_ss VARCHAR2 (4000);
2777 l_calc_sql_exp VARCHAR2 (4000);
2778 l_parent_start_date DATE;
2779 l_parent_end_date DATE;
2780 l_child_start_date DATE;
2781 l_child_end_date DATE;
2782 rs VARCHAR2 (50);
2783 mc NUMBER;
2784 md VARCHAR2 (50);
2785 BEGIN
2786 cn_calc_sql_exps_pvt.get_dependent_plan_elts (p_api_version => 1.0,
2787 p_node_type => 'F',
2788 p_node_id => x_calc_formula_id,
2789 x_plan_elt_id_tbl => nt,
2790 x_return_status => rs,
2791 x_msg_count => mc,
2792 x_msg_data => md
2793 );
2794
2795 IF rs <> 'S'
2796 THEN
2797 RAISE fnd_api.g_exc_error;
2798 ELSE --if return status = 'S'
2799 IF (nt.COUNT > 0)
2800 THEN
2801 FOR i IN 0 .. (nt.COUNT - 1)
2802 LOOP
2803 child_quota_id := nt (i);
2804 -- check for parent and child plan element date range
2805 l_parent_start_date := x_parent_start_date;
2806 l_parent_end_date := x_parent_end_date;
2807
2808 SELECT start_date,
2809 end_date
2810 INTO l_child_start_date,
2811 l_child_end_date
2812 FROM cn_quotas
2813 WHERE quota_id = child_quota_id;
2814
2815 -- check date range between the parent and child plan element
2816 IF (l_parent_start_date < l_child_start_date)
2817 THEN
2818 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2819 THEN
2820 -- Need to define message 'CN_PE_CANNOT_REF_ITSEF' in SEED115
2821 fnd_message.set_name ('CN', 'CN_PPE_WITHIN_CPE');
2822 fnd_msg_pub.ADD;
2823 END IF;
2824
2825 RAISE fnd_api.g_exc_error;
2826 END IF; -- l_child_start_date < l_parent_start_date
2827
2828 IF ((l_parent_end_date IS NULL) AND (l_child_end_date IS NOT NULL))
2829 THEN
2830 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2831 THEN
2832 -- Need to define message 'CN_PE_CED_CANNOT_BEFORE_PED' in SEED115
2833 fnd_message.set_name ('CN', 'CN_PPE_WITHIN_CPE');
2834 fnd_msg_pub.ADD;
2835 END IF;
2836
2837 RAISE fnd_api.g_exc_error;
2838 END IF; -- ((l_child_end_date is NULL) AND (l_parent_child_date is not NULL))
2839
2840 IF ((l_parent_end_date IS NOT NULL) AND (l_child_end_date IS NOT NULL)) AND (l_parent_end_date > l_child_end_date)
2841 THEN
2842 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2843 THEN
2844 -- Need to define message 'CN_PE_CED_CANNOT_BEFORE_PED' in SEED115
2845 fnd_message.set_name ('CN', 'CN_PPE_WITHIN_CPE');
2846 fnd_msg_pub.ADD;
2847 END IF;
2848
2849 RAISE fnd_api.g_exc_error;
2850 END IF; -- ( l_child_end_date > l_parent_end_date )
2851 END LOOP; -- for
2852 END IF; -- nt.count>0
2853 END IF;
2854 END check_create_pe_self_ref;
2855
2856 PROCEDURE validate_formula (
2857 p_plan_element IN cn_chk_plan_element_pkg.pe_rec_type --cn_plan_element_pvt.plan_element_rec_type
2858 )
2859 IS
2860 l_loading_status VARCHAR2 (100);
2861 x_return_status VARCHAR2 (100);
2862 x_loading_status VARCHAR2 (100);
2863 l_formula_type cn_calc_formulas.formula_type%TYPE;
2864 l_api_name VARCHAR2 (100) := 'validate_formula';
2865 l_calc_name cn_calc_formulas.NAME%TYPE;
2866 BEGIN
2867
2868 -- Validate the Quota Type with the Respective Column
2869 -- Check if the quota type is formula then the formula name must be not null
2870 -- Check if the quota type is formula the package name must be null
2871 IF (p_plan_element.quota_type_code = 'FORMULA')
2872 THEN
2873
2874 BEGIN
2875 SELECT NAME
2876 INTO l_calc_name
2877 FROM cn_calc_formulas
2878 WHERE calc_formula_id = p_plan_element.calc_formula_id;
2879 EXCEPTION
2880 WHEN NO_DATA_FOUND
2881 THEN
2882 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2883 THEN
2884 fnd_message.set_name ('CN', 'CN_FORMULA_NOT_EXIST');
2885 fnd_message.set_token ('FORMULA_NAME', p_plan_element.calc_formula_id);
2886 fnd_msg_pub.ADD;
2887 END IF;
2888
2889 x_loading_status := 'FORMULA_NOT_EXIST';
2890 RAISE fnd_api.g_exc_error;
2891 END;
2892
2893 -- if Quota type is Formula, then Formula is Mandatory and
2894 -- Package name must be null
2895 cn_chk_plan_element_pkg.chk_formula_quota_pe (x_return_status => x_return_status,
2896 p_pe_rec => p_plan_element,
2897 p_loading_status => x_loading_status,
2898 x_loading_status => l_loading_status
2899 );
2900 x_loading_status := l_loading_status;
2901
2902 IF (x_return_status <> fnd_api.g_ret_sts_success)
2903 THEN
2904 RAISE fnd_api.g_exc_error;
2905 END IF;
2906 ELSIF (p_plan_element.quota_type_code = 'EXTERNAL')
2907 THEN
2908 -- if Quota type is External Package name is Mandatory and
2909 -- formula must be null
2910 cn_chk_plan_element_pkg.chk_external_quota_pe (x_return_status => x_return_status,
2911 p_pe_rec => p_plan_element,
2912 p_loading_status => x_loading_status,
2913 x_loading_status => l_loading_status
2914 );
2915
2916 IF (x_return_status <> fnd_api.g_ret_sts_success)
2917 THEN
2918 RAISE fnd_api.g_exc_error;
2919 END IF;
2920 ELSIF (p_plan_element.quota_type_code = 'NONE')
2921 THEN
2922 -- If quota type is NONE, both Formula and package must be null
2923 cn_chk_plan_element_pkg.chk_other_quota_pe (x_return_status => x_return_status,
2924 p_pe_rec => p_plan_element,
2925 p_loading_status => x_loading_status,
2926 x_loading_status => l_loading_status
2927 );
2928
2929 IF (x_return_status <> fnd_api.g_ret_sts_success)
2930 THEN
2931 RAISE fnd_api.g_exc_error;
2932 END IF;
2933 END IF;
2934
2935 -- 2.1 check For match of the INCENTIVE_TYPE_CODE against the type of the formula assigned.
2936 IF (p_plan_element.calc_formula_id IS NOT NULL)
2937 THEN
2938 BEGIN
2939 SELECT formula_type
2940 INTO l_formula_type
2941 FROM cn_calc_formulas
2942 WHERE calc_formula_id = p_plan_element.calc_formula_id;
2943 EXCEPTION
2944 WHEN NO_DATA_FOUND
2945 THEN
2946 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2947 THEN
2948 fnd_message.set_name ('CN', 'CN_INVALID_DATA');
2949 fnd_message.set_token ('OBJ_NAME', cn_chk_plan_element_pkg.g_formula_id);
2950 fnd_msg_pub.ADD;
2951 END IF;
2952
2953 RAISE fnd_api.g_exc_error;
2954 END;
2955
2956 IF (p_plan_element.incentive_type_code = 'BONUS') AND (l_formula_type = 'C')
2957 THEN
2958 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2959 THEN
2960 fnd_message.set_name ('CN', 'CN_CANNOT_ASSIGN_FORMULA');
2961 fnd_message.set_token ('FORMULA_TYPE', cn_api.get_lkup_meaning ('COMMISSION', 'INCENTIVE_TYPE'));
2962 fnd_message.set_token ('PE_INC_TYPE', cn_api.get_lkup_meaning ('BONUS', 'INCENTIVE_TYPE'));
2963 fnd_msg_pub.ADD;
2964 END IF;
2965
2966 RAISE fnd_api.g_exc_error;
2967 END IF;
2968
2969 IF (p_plan_element.incentive_type_code = 'COMMISSION') AND (l_formula_type = 'B')
2970 THEN
2971 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2972 THEN
2973 fnd_message.set_name ('CN', 'CN_CANNOT_ASSIGN_FORMULA');
2974 fnd_message.set_token ('FORMULA_TYPE', cn_api.get_lkup_meaning ('BONUS', 'INCENTIVE_TYPE'));
2975 fnd_message.set_token ('PE_INC_TYPE', cn_api.get_lkup_meaning ('COMMISSION', 'INCENTIVE_TYPE'));
2976 fnd_msg_pub.ADD;
2977 END IF;
2978
2979 RAISE fnd_api.g_exc_error;
2980 END IF;
2981 END IF;
2982
2983 -- check the date range or the referenced plan element in formula assigned if any.
2984 IF (p_plan_element.calc_formula_id IS NOT NULL)
2985 THEN
2986 check_create_pe_self_ref (x_calc_formula_id => p_plan_element.calc_formula_id,
2987 x_parent_start_date => p_plan_element.start_date,
2988 x_parent_end_date => p_plan_element.end_date
2989 );
2990 END IF;
2991 END validate_formula;
2992
2993 -- ----------------------------------------------------------------------------+
2994 -- Procedure: chk_formula_quota_pe
2995 -- Desc : Check input for Formula Quota type plan element
2996 -- ----------------------------------------------------------------------------+
2997 PROCEDURE chk_formula_quota_pe (
2998 x_return_status OUT NOCOPY VARCHAR2,
2999 p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type ,--cn_plan_element_pvt.plan_element_rec_type,
3000 p_loading_status IN VARCHAR2,
3001 x_loading_status OUT NOCOPY VARCHAR2
3002 )
3003 IS
3004 l_api_name CONSTANT VARCHAR2 (30) := 'chk_formula_quota_pe';
3005 l_loading_status VARCHAR2 (80);
3006 BEGIN
3007 x_return_status := fnd_api.g_ret_sts_success;
3008 x_loading_status := p_loading_status;
3009
3010 --+
3011 -- Validate Rule : if quota type is formula
3012 -- package Name = NULL, calc_formula_id must be not NULL.
3013 -- incentive_type should not be Manual.
3014 IF ((cn_api.pe_char_field_must_null (p_char_field => p_pe_rec.package_name,
3015 p_pe_type => p_pe_rec.quota_type_code,
3016 p_obj_name => g_package_name,
3017 p_token1 => NULL,
3018 p_token2 => NULL,
3019 p_token3 => NULL,
3020 p_loading_status => x_loading_status,
3021 x_loading_status => l_loading_status
3022 )
3023 ) = fnd_api.g_false
3024 )
3025 THEN
3026 RAISE fnd_api.g_exc_error;
3027 END IF;
3028
3029 IF (cn_api.pe_num_field_cannot_null (p_num_field => p_pe_rec.calc_formula_id,
3030 p_pe_type => p_pe_rec.quota_type_code,
3031 p_obj_name => g_formula_name,
3032 p_token1 => NULL,
3033 p_token2 => NULL,
3034 p_token3 => NULL,
3035 p_loading_status => x_loading_status,
3036 x_loading_status => l_loading_status
3037 ) = fnd_api.g_false
3038 )
3039 THEN
3040 RAISE fnd_api.g_exc_error;
3041 END IF;
3042
3043 IF (p_pe_rec.incentive_type_code = 'MANUAL')
3044 THEN
3045 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3046 THEN
3047 fnd_message.set_name ('CN', 'CN_INCENTIVE_TYPE_MUST_BE');
3048 fnd_message.set_token ('OBJ_VALUE', 'Bonus or Commission');
3049 fnd_message.set_token ('PLAN_TYPE', cn_api.get_lkup_meaning (p_pe_rec.quota_type_code, 'QUOTA_TYPE'));
3050 fnd_msg_pub.ADD;
3051 END IF;
3052
3053 x_loading_status := 'CN_INCENTIVE_TYPE_MUST_BE';
3054 RAISE fnd_api.g_exc_error;
3055 END IF;
3056 EXCEPTION
3057 WHEN fnd_api.g_exc_error
3058 THEN
3059 x_return_status := fnd_api.g_ret_sts_error;
3060 WHEN fnd_api.g_exc_unexpected_error
3061 THEN
3062 x_return_status := fnd_api.g_ret_sts_unexp_error;
3063 x_loading_status := 'UNEXPECTED_ERR';
3064 WHEN OTHERS
3065 THEN
3066 x_return_status := fnd_api.g_ret_sts_unexp_error;
3067 x_loading_status := 'UNEXPECTED_ERR';
3068
3069 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3070 THEN
3071 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3072 END IF;
3073 END chk_formula_quota_pe;
3074
3075 -- ----------------------------------------------------------------------------+
3076 -- Procedure: chk_external_quota_pe
3077 -- Desc : Check input for External Quota type plan element
3078 -- ----------------------------------------------------------------------------+
3079 PROCEDURE chk_external_quota_pe (
3080 x_return_status OUT NOCOPY VARCHAR2,
3081 p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type , --cn_plan_element_pvt.plan_element_rec_type,
3082 p_loading_status IN VARCHAR2,
3083 x_loading_status OUT NOCOPY VARCHAR2
3084 )
3085 IS
3086 l_api_name CONSTANT VARCHAR2 (30) := 'chk_external_quota_pe';
3087 l_loading_status VARCHAR2 (80);
3088 BEGIN
3089 x_return_status := fnd_api.g_ret_sts_success;
3090 x_loading_status := p_loading_status;
3091
3092 --+
3093 -- Validate Rule : if quota type is EXTERNAL
3094 -- package Name must not be Null, Calc_Fromula_id must be null.
3095 -- incentive_type should not be MANUAL
3096 IF ((cn_api.pe_num_field_must_null (p_num_field => p_pe_rec.calc_formula_id,
3097 p_pe_type => p_pe_rec.quota_type_code,
3098 p_obj_name => g_formula_name,
3099 p_token1 => NULL,
3100 p_token2 => NULL,
3101 p_token3 => NULL,
3102 p_loading_status => x_loading_status,
3103 x_loading_status => l_loading_status
3104 )
3105 ) = fnd_api.g_false
3106 )
3107 THEN
3108 RAISE fnd_api.g_exc_error;
3109 END IF;
3110
3111 IF (cn_api.pe_char_field_cannot_null (p_char_field => p_pe_rec.package_name,
3112 p_pe_type => p_pe_rec.quota_type_code,
3113 p_obj_name => g_package_name,
3114 p_token1 => NULL,
3115 p_token2 => NULL,
3116 p_token3 => NULL,
3117 p_loading_status => x_loading_status,
3118 x_loading_status => l_loading_status
3119 ) = fnd_api.g_false
3120 )
3121 THEN
3122 RAISE fnd_api.g_exc_error;
3123 END IF;
3124
3125 IF (p_pe_rec.incentive_type_code = 'MANUAL')
3126 THEN
3127 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3128 THEN
3129 fnd_message.set_name ('CN', 'CN_INCENTIVE_TYPE_MUST_BE');
3130 fnd_message.set_token ('OBJ_VALUE', 'Bonus or Commission');
3131 fnd_message.set_token ('PLAN_TYPE', cn_api.get_lkup_meaning (p_pe_rec.quota_type_code, 'QUOTA_TYPE'));
3132 fnd_msg_pub.ADD;
3133 END IF;
3134
3135 x_loading_status := 'CN_INCENTIVE_TYPE_MUST_BE';
3136 RAISE fnd_api.g_exc_error;
3137 END IF;
3138 EXCEPTION
3139 WHEN fnd_api.g_exc_error
3140 THEN
3141 x_return_status := fnd_api.g_ret_sts_error;
3142 WHEN fnd_api.g_exc_unexpected_error
3143 THEN
3144 x_return_status := fnd_api.g_ret_sts_unexp_error;
3145 x_loading_status := 'UNEXPECTED_ERR';
3146 WHEN OTHERS
3147 THEN
3148 x_return_status := fnd_api.g_ret_sts_unexp_error;
3149 x_loading_status := 'UNEXPECTED_ERR';
3150
3151 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3152 THEN
3153 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3154 END IF;
3155 END chk_external_quota_pe;
3156
3157 -- ----------------------------------------------------------------------------+
3158 -- Procedure: chk_others_quota_pe
3159 -- Desc : Check input for other Quota type plan element
3160 -- ----------------------------------------------------------------------------+
3161 PROCEDURE chk_other_quota_pe (
3162 x_return_status OUT NOCOPY VARCHAR2,
3163 p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type , --cn_plan_element_pvt.plan_element_rec_type,
3164 p_loading_status IN VARCHAR2,
3165 x_loading_status OUT NOCOPY VARCHAR2
3166 )
3167 IS
3168 l_api_name CONSTANT VARCHAR2 (30) := 'chk_other_quota_pe';
3169 l_loading_status VARCHAR2 (80);
3170 BEGIN
3171 x_return_status := fnd_api.g_ret_sts_success;
3172 x_loading_status := p_loading_status;
3173
3174 --+
3175 -- Validate Rule : if quota type is OTHER
3176 -- package Name must be Null, Calc_Fromula_id must be null.
3177 -- incentive_type must me MANUAL
3178 IF ((cn_api.pe_num_field_must_null (p_num_field => p_pe_rec.calc_formula_id,
3179 p_pe_type => p_pe_rec.quota_type_code,
3180 p_obj_name => g_formula_name,
3181 p_token1 => NULL,
3182 p_token2 => NULL,
3183 p_token3 => NULL,
3184 p_loading_status => x_loading_status,
3185 x_loading_status => l_loading_status
3186 )
3187 ) = fnd_api.g_false
3188 )
3189 THEN
3190 RAISE fnd_api.g_exc_error;
3191 END IF;
3192
3193 IF ((cn_api.pe_char_field_must_null (p_char_field => p_pe_rec.package_name,
3194 p_pe_type => p_pe_rec.quota_type_code,
3195 p_obj_name => g_package_name,
3196 p_token1 => NULL,
3197 p_token2 => NULL,
3198 p_token3 => NULL,
3199 p_loading_status => x_loading_status,
3200 x_loading_status => l_loading_status
3201 )
3202 ) = fnd_api.g_false
3203 )
3204 THEN
3205 RAISE fnd_api.g_exc_error;
3206 END IF;
3207
3208 IF (p_pe_rec.incentive_type_code NOT IN ('MANUAL'))
3209 THEN
3210 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3211 THEN
3212 fnd_message.set_name ('CN', 'CN_INCENTIVE_TYPE_MUST_BE');
3213 fnd_message.set_token ('OBJ_VALUE', 'Manual');
3214 fnd_message.set_token ('PLAN_TYPE', cn_api.get_lkup_meaning (p_pe_rec.quota_type_code, 'QUOTA_TYPE'));
3215 fnd_msg_pub.ADD;
3216 END IF;
3217
3218 x_loading_status := 'CN_INCENTIVE_TYPE_MUST_BE';
3219 RAISE fnd_api.g_exc_error;
3220 END IF;
3221 EXCEPTION
3222 WHEN fnd_api.g_exc_error
3223 THEN
3224 x_return_status := fnd_api.g_ret_sts_error;
3225 WHEN fnd_api.g_exc_unexpected_error
3226 THEN
3227 x_return_status := fnd_api.g_ret_sts_unexp_error;
3228 x_loading_status := 'UNEXPECTED_ERR';
3229 WHEN OTHERS
3230 THEN
3231 x_return_status := fnd_api.g_ret_sts_unexp_error;
3232 x_loading_status := 'UNEXPECTED_ERR';
3233
3234 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3235 THEN
3236 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3237 END IF;
3238 END chk_other_quota_pe;
3239
3240 --| -----------------------------------------------------------------------+
3241 --| Procedure Name : chk_miss_date_para
3242 --| Desc : Check for missing parameters -- Date type
3243 --| ---------------------------------------------------------------------+
3244 FUNCTION chk_miss_date_para (
3245 p_date_para IN DATE,
3246 p_para_name IN VARCHAR2,
3247 p_loading_status IN VARCHAR2,
3248 x_loading_status OUT NOCOPY VARCHAR2
3249 )
3250 RETURN VARCHAR2
3251 IS
3252 l_return_code VARCHAR2 (1) := fnd_api.g_false;
3253 BEGIN
3254 x_loading_status := p_loading_status;
3255
3256 IF (p_date_para = fnd_api.g_miss_date)
3257 THEN
3258 -- Error, check the msg level and add an error message to the
3259 -- API message list
3260 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3261 THEN
3262 fnd_message.set_name ('CN', 'CN_MISS_PARAMETER');
3263 fnd_message.set_token ('PARA_NAME', p_para_name);
3264 fnd_msg_pub.ADD;
3265 END IF;
3266
3267 x_loading_status := 'CN_MISS_PARAMETER';
3268 l_return_code := fnd_api.g_true;
3269 END IF;
3270
3271 RETURN l_return_code;
3272 END chk_miss_date_para;
3273
3274 --| -----------------------------------------------------------------------+
3275 --| Function Name : chk_null_date_para
3276 --| Desc : Check for Null parameters -- Date type
3277 --| ---------------------------------------------------------------------+
3278 FUNCTION chk_null_date_para (
3279 p_date_para IN DATE,
3280 p_obj_name IN VARCHAR2,
3281 p_loading_status IN VARCHAR2,
3282 x_loading_status OUT NOCOPY VARCHAR2
3283 )
3284 RETURN VARCHAR2
3285 IS
3286 l_return_code VARCHAR2 (1) := fnd_api.g_false;
3287 BEGIN
3288 x_loading_status := p_loading_status;
3289
3290 IF (p_date_para IS NULL)
3291 THEN
3292 -- Error, check the msg level and add an error message to the
3293 -- API message list
3294 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3295 THEN
3296 fnd_message.set_name ('CN', 'CN_CANNOT_NULL');
3297 fnd_message.set_token ('OBJ_NAME', p_obj_name);
3298 fnd_msg_pub.ADD;
3299 END IF;
3300
3301 x_loading_status := 'CN_CANNOT_NULL';
3302 l_return_code := fnd_api.g_true;
3303 END IF;
3304
3305 RETURN l_return_code;
3306 END chk_null_date_para;
3307
3308 --| -----------------------------------------------------------------------+
3309 --| PROCEDURE Name : chk_date_effective
3310 --| Desc : Check Date effectivity for accelerator
3311 --| -----------------------------------------------------------------------+
3312 PROCEDURE chk_date_effective (
3313 x_return_status OUT NOCOPY VARCHAR2,
3314 p_start_date IN DATE,
3315 p_end_date IN DATE,
3316 p_quota_id IN NUMBER,
3317 p_object_type IN VARCHAR2,
3318 p_loading_status IN VARCHAR2,
3319 x_loading_status OUT NOCOPY VARCHAR2
3320 )
3321 IS
3322 l_tmp NUMBER;
3323
3324 CURSOR quota_curs
3325 IS
3326 SELECT start_date,
3327 end_date
3328 FROM cn_quotas
3329 WHERE quota_id = p_quota_id;
3330
3331 l_record_info quota_curs%ROWTYPE;
3332 l_api_name CONSTANT VARCHAR2 (30) := 'chk_date_effective';
3333 BEGIN
3334 x_loading_status := p_loading_status;
3335 x_return_status := fnd_api.g_ret_sts_success;
3336
3337 IF p_quota_id IS NOT NULL
3338 THEN
3339 OPEN quota_curs;
3340
3341 FETCH quota_curs
3342 INTO l_record_info;
3343
3344 CLOSE quota_curs;
3345
3346 IF ( TRUNC (p_start_date) < TRUNC (l_record_info.start_date)
3347 OR (p_end_date IS NULL AND l_record_info.end_date IS NOT NULL)
3348 OR (p_end_date IS NOT NULL AND l_record_info.end_date IS NOT NULL AND TRUNC (p_end_date) > TRUNC (l_record_info.end_date))
3349 )
3350 THEN
3351 IF UPPER (p_object_type) = 'UPLIFT'
3352 THEN
3353 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3354 THEN
3355 fnd_message.set_name ('CN', 'CN_UPLIFT_DATE_EFFECTIVE');
3356 fnd_msg_pub.ADD;
3357 END IF;
3358
3359 x_loading_status := 'UPLIFT_DATE_EFFECTIVE';
3360 RAISE fnd_api.g_exc_error;
3361 ELSE
3362 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3363 THEN
3364 fnd_message.set_name ('CN', 'CN_RATE_DATE_EFFECTIVE');
3365 fnd_msg_pub.ADD;
3366 END IF;
3367
3368 x_loading_status := 'RATE_DATE_EFFECTIVE';
3369 RAISE fnd_api.g_exc_error;
3370 END IF;
3371 END IF;
3372 END IF;
3373 EXCEPTION
3374 WHEN fnd_api.g_exc_error
3375 THEN
3376 x_return_status := fnd_api.g_ret_sts_error;
3377 WHEN fnd_api.g_exc_unexpected_error
3378 THEN
3379 x_return_status := fnd_api.g_ret_sts_unexp_error;
3380 x_loading_status := 'UNEXPECTED_ERR';
3381 WHEN OTHERS
3382 THEN
3383 x_return_status := fnd_api.g_ret_sts_unexp_error;
3384 x_loading_status := 'UNEXPECTED_ERR';
3385
3386 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3387 THEN
3388 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3389 END IF;
3390 END chk_date_effective;
3391
3392 --| -----------------------------------------------------------------------+
3393 --| PROCEDURE Name : chk_rate_quota_update_delete
3394 --| Desc : Check rate Quota Update Delete
3395 --| -----------------------------------------------------------------------+
3396 PROCEDURE chk_rate_quota_iud (
3397 x_return_status OUT NOCOPY VARCHAR2,
3398 p_start_date IN DATE,
3399 p_end_date IN DATE,
3400 p_iud_flag IN VARCHAR2,
3401 p_quota_id IN NUMBER,
3402 p_calc_formula_id IN NUMBER,
3403 p_rt_quota_asgn_id IN NUMBER,
3404 p_loading_status IN VARCHAR2,
3405 x_loading_status OUT NOCOPY VARCHAR2
3406 )
3407 IS
3408 CURSOR prev
3409 IS
3410 SELECT start_date,
3411 end_date
3412 FROM cn_rt_quota_asgns
3413 WHERE quota_id = p_quota_id
3414 AND calc_formula_id = p_calc_formula_id
3415 AND rt_quota_asgn_id <> NVL (p_rt_quota_asgn_id, 0)
3416 AND TRUNC (start_date) < TRUNC (p_start_date)
3417 ORDER BY start_date DESC;
3418
3419 CURSOR NEXT
3420 IS
3421 SELECT start_date,
3422 end_date
3423 FROM cn_rt_quota_asgns
3424 WHERE quota_id = p_quota_id
3425 AND calc_formula_id = p_calc_formula_id
3426 AND rt_quota_asgn_id <> NVL (p_rt_quota_asgn_id, 0)
3427 AND TRUNC (start_date) > TRUNC (p_start_date)
3428 ORDER BY start_date ASC;
3429
3430 l_start_date DATE;
3431 l_end_date DATE;
3432 BEGIN
3433 -- Initialize API return status to success
3434 x_return_status := fnd_api.g_ret_sts_success;
3435 x_loading_status := p_loading_status;
3436
3437 --+
3438 -- get are there any record previously
3439 --+
3440 IF p_iud_flag IN ('I', 'U')
3441 THEN
3442 OPEN prev;
3443
3444 FETCH prev
3445 INTO l_start_date,
3446 l_end_date;
3447
3448 CLOSE prev;
3449
3450 IF l_start_date IS NOT NULL AND TRUNC (NVL (l_end_date, fnd_api.g_miss_date)) + 1 <> TRUNC (p_start_date)
3451 THEN
3452 x_loading_status := 'CN_RATE_OP_NOT_ALLOWED';
3453 x_return_status := fnd_api.g_ret_sts_error;
3454 END IF;
3455
3456 l_start_date := NULL;
3457 l_end_date := NULL;
3458
3459 IF x_return_status <> fnd_api.g_ret_sts_error
3460 THEN
3461 --+
3462 -- Get are there any records available after this
3463 --+
3464 OPEN NEXT;
3465
3466 FETCH NEXT
3467 INTO l_start_date,
3468 l_end_date;
3469
3470 CLOSE NEXT;
3471
3472 IF l_start_date IS NOT NULL AND TRUNC (l_start_date) - 1 <> TRUNC (NVL (p_end_date, fnd_api.g_miss_date))
3473 THEN
3474 x_loading_status := 'CN_RATE_OP_NOT_ALLOWED';
3475 x_return_status := fnd_api.g_ret_sts_error;
3476 END IF;
3477 END IF;
3478 ELSIF p_iud_flag = 'D'
3479 THEN
3480 -- You cannot deletE the middle record in the rates
3481 -- delete middle record may cause invalid seq and
3482 -- date overlap
3483 OPEN prev;
3484
3485 FETCH prev
3486 INTO l_start_date,
3487 l_end_date;
3488
3489 CLOSE prev;
3490
3491 IF l_start_date IS NOT NULL
3492 THEN
3493 l_start_date := NULL;
3494 l_end_date := NULL;
3495
3496 OPEN NEXT;
3497
3498 FETCH NEXT
3499 INTO l_start_date,
3500 l_end_date;
3501
3502 CLOSE NEXT;
3503
3504 IF l_start_date IS NOT NULL
3505 THEN
3506 x_loading_status := 'CN_RATE_OP_NOT_ALLOWED';
3507 x_return_status := fnd_api.g_ret_sts_error;
3508 END IF;
3509 END IF;
3510 END IF;
3511 END chk_rate_quota_iud;
3512
3513 --| -----------------------------------------------------------------------+
3514 --| PROCEDURE Name : chk_Uplift_insert_update_delete
3515 --| Desc : Check Uplift Insert Update Delete
3516 --| -----------------------------------------------------------------------+
3517 PROCEDURE chk_uplift_iud (
3518 x_return_status OUT NOCOPY VARCHAR2,
3519 p_start_date IN DATE,
3520 p_end_date IN DATE,
3521 p_iud_flag IN VARCHAR2,
3522 p_quota_rule_id IN NUMBER,
3523 p_quota_rule_uplift_id IN NUMBER,
3524 p_loading_status IN VARCHAR2,
3525 x_loading_status OUT NOCOPY VARCHAR2
3526 )
3527 IS
3528 CURSOR prev
3529 IS
3530 SELECT start_date,
3531 end_date
3532 FROM cn_quota_rule_uplifts
3533 WHERE quota_rule_id = p_quota_rule_id
3534 AND quota_rule_uplift_id <> NVL (p_quota_rule_uplift_id, 0)
3535 AND TRUNC (start_date) < TRUNC (p_start_date)
3536 ORDER BY start_date DESC;
3537
3538 CURSOR NEXT
3539 IS
3540 SELECT start_date,
3541 end_date
3542 FROM cn_quota_rule_uplifts
3543 WHERE quota_rule_id = p_quota_rule_id
3544 AND quota_rule_uplift_id <> NVL (p_quota_rule_uplift_id, 0)
3545 AND TRUNC (start_date) > TRUNC (p_start_date)
3546 ORDER BY start_date ASC;
3547
3548 l_start_date DATE;
3549 l_end_date DATE;
3550 BEGIN
3551 -- Initialize API return status to success
3552 x_return_status := fnd_api.g_ret_sts_success;
3553 x_loading_status := p_loading_status;
3554
3555 IF p_iud_flag IN ('I', 'U')
3556 THEN
3557 --+
3558 -- get are there any record previously
3559 --+
3560 OPEN prev;
3561
3562 FETCH prev
3563 INTO l_start_date,
3564 l_end_date;
3565
3566 CLOSE prev;
3567
3568 IF l_start_date IS NOT NULL AND TRUNC (NVL (l_end_date, fnd_api.g_miss_date)) + 1 <> TRUNC (p_start_date)
3569 THEN
3570 x_loading_status := 'CN_UPLIFT_OP_NOT_ALLOWED';
3571 x_return_status := fnd_api.g_ret_sts_error;
3572 END IF;
3573
3574 l_start_date := NULL;
3575 l_end_date := NULL;
3576
3577 IF x_return_status <> fnd_api.g_ret_sts_error
3578 THEN
3579 --+
3580 -- Get are there any records available after this
3581 --+
3582 OPEN NEXT;
3583
3584 FETCH NEXT
3585 INTO l_start_date,
3586 l_end_date;
3587
3588 CLOSE NEXT;
3589
3590 IF l_start_date IS NOT NULL AND TRUNC (l_start_date) - 1 <> TRUNC (NVL (p_end_date, fnd_api.g_miss_date))
3591 THEN
3592 x_loading_status := 'CN_UPLIFT_OP_NOT_ALLOWED';
3593 x_return_status := fnd_api.g_ret_sts_error;
3594 END IF;
3595 END IF;
3596 ELSIF p_iud_flag = 'D'
3597 THEN
3598 -- You cannot delete the middle record in the rates
3599 -- delete middle record may cause invalid seq and
3600 -- date overlap
3601 OPEN prev;
3602
3603 FETCH prev
3604 INTO l_start_date,
3605 l_end_date;
3606
3607 CLOSE prev;
3608
3609 IF l_start_date IS NOT NULL
3610 THEN
3611 l_start_date := NULL;
3612 l_end_date := NULL;
3613
3614 OPEN NEXT;
3615
3616 FETCH NEXT
3617 INTO l_start_date,
3618 l_end_date;
3619
3620 CLOSE NEXT;
3621
3622 IF l_start_date IS NOT NULL
3623 THEN
3624 x_loading_status := 'CN_UPLIFT_OP_NOT_ALLOWED';
3625 x_return_status := fnd_api.g_ret_sts_error;
3626 END IF;
3627 END IF;
3628 END IF;
3629 END chk_uplift_iud;
3630
3631 --| -----------------------------------------------------------------------+
3632 --| Function Name : Get_Quota_type
3633 --| ---------------------------------------------------------------------+
3634 FUNCTION get_quota_type (
3635 p_quota_id NUMBER
3636 )
3637 RETURN cn_quotas.quota_type_code%TYPE
3638 IS
3639 l_quota_type cn_quotas.quota_type_code%TYPE;
3640 BEGIN
3641 SELECT quota_type_code
3642 INTO l_quota_type
3643 FROM cn_quotas
3644 WHERE quota_id = p_quota_id;
3645
3646 RETURN l_quota_type;
3647 EXCEPTION
3648 WHEN NO_DATA_FOUND
3649 THEN
3650 RETURN NULL;
3651 END get_quota_type;
3652
3653 --| -----------------------------------------------------------------------+
3654 --| PROCEDURE Name : chk_formula_rate_date
3655 --| Desc : Check Date effectivity for rate
3656 --| -----------------------------------------------------------------------+
3657 PROCEDURE chk_formula_rate_date (
3658 x_return_status OUT NOCOPY VARCHAR2,
3659 p_start_date IN DATE,
3660 p_end_date IN DATE,
3661 p_quota_name IN VARCHAR2,
3662 p_calc_formula_id IN NUMBER,
3663 p_calc_formula_name IN VARCHAR2,
3664 p_loading_status IN VARCHAR2,
3665 x_loading_status OUT NOCOPY VARCHAR2
3666 )
3667 IS
3668 l_null_date CONSTANT DATE := TO_DATE ('31-12-3000', 'DD-MM-YYYY');
3669 l_tmp NUMBER;
3670 /* CURSOR rt_formula_curs IS
3671 SELECT Count(1)
3672 FROM cn_rt_formula_asgns
3673 WHERE calc_formula_id = p_calc_formula_id
3674 and ( start_Date < p_start_date
3675 or ( p_end_date IS NOT NULL
3676 and end_date IS NULL )
3677 or end_date > p_end_date ); */
3678 l_record_found NUMBER;
3679 l_api_name CONSTANT VARCHAR2 (30) := 'chk_formula_rate_date';
3680 l_temp_start_date DATE := NULL;
3681 l_temp_end_date DATE := NULL;
3682 l_temp_count NUMBER;
3683 BEGIN
3684 x_loading_status := p_loading_status;
3685 x_return_status := fnd_api.g_ret_sts_success;
3686
3687 IF p_calc_formula_id IS NOT NULL
3688 THEN
3689 -- OPEN rt_formula_curs;
3690 -- FETCH rt_formula_curs INTO l_record_found;
3691 -- CLOSE rt_formula_curs;
3692
3693 -- IF l_record_found > 0 THEN
3694 SELECT MIN (start_date),
3695 MAX (NVL (end_date, l_null_date))
3696 INTO l_temp_start_date,
3697 l_temp_end_date
3698 FROM cn_rt_formula_asgns
3699 WHERE calc_formula_id = p_calc_formula_id;
3700
3701 IF l_temp_start_date IS NOT NULL AND ((p_start_date < l_temp_start_date) OR (NVL (p_end_date, l_null_date) > l_temp_end_date))
3702 THEN
3703 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3704 THEN
3705 fnd_message.set_name ('CN', 'CN_FRT_DT_NOT_WITHIN_QUOTA');
3706 fnd_msg_pub.ADD;
3707 END IF;
3708
3709 x_loading_status := 'RT_DATE_NOT_WITHIN_QUOTA';
3710 RAISE fnd_api.g_exc_error;
3711 END IF;
3712 END IF;
3713 EXCEPTION
3714 WHEN fnd_api.g_exc_error
3715 THEN
3716 x_return_status := fnd_api.g_ret_sts_error;
3717 WHEN fnd_api.g_exc_unexpected_error
3718 THEN
3719 x_return_status := fnd_api.g_ret_sts_unexp_error;
3720 x_loading_status := 'UNEXPECTED_ERR';
3721 WHEN OTHERS
3722 THEN
3723 x_return_status := fnd_api.g_ret_sts_unexp_error;
3724 x_loading_status := 'UNEXPECTED_ERR';
3725
3726 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3727 THEN
3728 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3729 END IF;
3730 END chk_formula_rate_date;
3731
3732 --| -----------------------------------------------------------------------+
3733 --| PROCEDURE Name : chk_comp_plan_date
3734 --| Desc : Check chk_comp_plan_date
3735 --| This program will check the start date and end date when
3736 --| when user try to update the plan element start date and
3737 --| and end date after the plan element has been assigned to
3738 --| to a comp plan.
3739 --| Case 1
3740 --| comp_plan_start_date must greater than quota start date
3741 --| Case 2
3742 --| comp_plan_end_date must less than quota end date
3743 --| Case 3
3744 --| comp Plan end date is null and Quota end is not null
3745 --|
3746 --| All the above three cases cannot be accepted when a PE
3747 --| Start date and end date changes, if plan element already
3748 --| to a comp plan
3749 --| We don't do case 1, 2 and 3 any more. All we check here is whether
3750 --| plan element date and comp plan date overlap or not.
3751 --| Last modified by Kai Chen, 11/15/99
3752 --| -----------------------------------------------------------------------+
3753 PROCEDURE chk_comp_plan_date (
3754 x_return_status OUT NOCOPY VARCHAR2,
3755 p_start_date IN DATE,
3756 p_end_date IN DATE,
3757 p_quota_name IN VARCHAR2,
3758 p_quota_id IN NUMBER,
3759 p_loading_status IN VARCHAR2,
3760 x_loading_status OUT NOCOPY VARCHAR2
3761 )
3762 IS
3763 l_tmp NUMBER;
3764
3765 CURSOR comp_plan_curs
3766 IS
3767 SELECT cp.start_date,
3768 cp.end_date
3769 FROM cn_quota_assigns cq,
3770 cn_comp_plans cp
3771 WHERE cq.comp_plan_id = cp.comp_plan_id AND cq.quota_id = p_quota_id;
3772
3773 l_record_found NUMBER;
3774 l_api_name CONSTANT VARCHAR2 (30) := 'chk_comp_plan_date';
3775 BEGIN
3776 x_loading_status := p_loading_status;
3777 x_return_status := fnd_api.g_ret_sts_success;
3778
3779 IF p_quota_id IS NOT NULL
3780 THEN
3781 FOR l_rec IN comp_plan_curs
3782 LOOP
3783 IF (NOT cn_api.date_range_overlap (l_rec.start_date, l_rec.end_date, p_start_date, p_end_date))
3784 THEN
3785 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3786 THEN
3787 fnd_message.set_name ('CN', 'CN_CP_DT_NOT_WITHIN_QUOTA');
3788 fnd_msg_pub.ADD;
3789 END IF;
3790
3791 x_loading_status := 'CP_DATE_NOT_WITHIN_QUOTA';
3792 RAISE fnd_api.g_exc_error;
3793 END IF;
3794 END LOOP;
3795 END IF;
3796 EXCEPTION
3797 WHEN fnd_api.g_exc_error
3798 THEN
3799 x_return_status := fnd_api.g_ret_sts_error;
3800 WHEN fnd_api.g_exc_unexpected_error
3801 THEN
3802 x_return_status := fnd_api.g_ret_sts_unexp_error;
3803 x_loading_status := 'UNEXPECTED_ERR';
3804 WHEN OTHERS
3805 THEN
3806 x_return_status := fnd_api.g_ret_sts_unexp_error;
3807 x_loading_status := 'UNEXPECTED_ERR';
3808
3809 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3810 THEN
3811 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3812 END IF;
3813 END chk_comp_plan_date;
3814
3815 --| -----------------------------------------------------------------------+
3816 --| PROCEDURE Name : chk_uplift_date
3817 --| Desc : Check Date effectivity for rate
3818 --| -----------------------------------------------------------------------+
3819 PROCEDURE chk_uplift_date (
3820 x_return_status OUT NOCOPY VARCHAR2,
3821 p_start_date IN DATE,
3822 p_end_date IN DATE,
3823 p_quota_name IN VARCHAR2,
3824 p_quota_id IN NUMBER,
3825 p_loading_status IN VARCHAR2,
3826 x_loading_status OUT NOCOPY VARCHAR2
3827 )
3828 IS
3829 l_tmp NUMBER;
3830
3831 CURSOR uplift_curs
3832 IS
3833 SELECT COUNT (1)
3834 FROM cn_quota_rule_uplifts u,
3835 cn_quota_rules r
3836 WHERE r.quota_id = p_quota_id
3837 AND r.quota_rule_id = u.quota_rule_id
3838 AND (u.start_date < p_start_date OR (p_end_date IS NOT NULL AND u.end_date IS NULL) OR u.end_date > p_end_date);
3839
3840 l_record_found NUMBER;
3841 l_api_name CONSTANT VARCHAR2 (30) := 'chk_uplift_date';
3842 BEGIN
3843 x_loading_status := p_loading_status;
3844 x_return_status := fnd_api.g_ret_sts_success;
3845
3846 IF p_quota_id IS NOT NULL
3847 THEN
3848 OPEN uplift_curs;
3849
3850 FETCH uplift_curs
3851 INTO l_record_found;
3852
3853 CLOSE uplift_curs;
3854
3855 IF l_record_found > 0
3856 THEN
3857 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3858 THEN
3859 fnd_message.set_name ('CN', 'CN_UPLIFT_DT_NOT_WIN_QUOTA');
3860 fnd_msg_pub.ADD;
3861 END IF;
3862
3863 x_loading_status := 'UPLIFT_DATE_NOT_WIN_QUOTA';
3864 RAISE fnd_api.g_exc_error;
3865 END IF;
3866 END IF;
3867 EXCEPTION
3868 WHEN fnd_api.g_exc_error
3869 THEN
3870 x_return_status := fnd_api.g_ret_sts_error;
3871 WHEN fnd_api.g_exc_unexpected_error
3872 THEN
3873 x_return_status := fnd_api.g_ret_sts_unexp_error;
3874 x_loading_status := 'UNEXPECTED_ERR';
3875 WHEN OTHERS
3876 THEN
3877 x_return_status := fnd_api.g_ret_sts_unexp_error;
3878 x_loading_status := 'UNEXPECTED_ERR';
3879
3880 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3881 THEN
3882 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3883 END IF;
3884 END chk_uplift_date;
3885
3886 --| -----------------------------------------------------------------------+
3887 --| PROCEDURE Name : chk_rate_quota_date
3888 --| Desc : Check Date effectivity for rate
3889 --| -----------------------------------------------------------------------+
3890 PROCEDURE chk_rate_quota_date (
3891 x_return_status OUT NOCOPY VARCHAR2,
3892 p_start_date IN DATE,
3893 p_end_date IN DATE,
3894 p_quota_name IN VARCHAR2,
3895 p_quota_id IN NUMBER,
3896 p_loading_status IN VARCHAR2,
3897 x_loading_status OUT NOCOPY VARCHAR2
3898 )
3899 IS
3900 l_tmp NUMBER;
3901
3902 CURSOR rate_quota_curs
3903 IS
3904 SELECT COUNT (1)
3905 FROM cn_rt_quota_asgns u
3906 WHERE u.quota_id = p_quota_id
3907 AND (u.start_date < p_start_date OR (p_end_date IS NOT NULL AND u.end_date IS NULL) OR u.end_date > p_end_date);
3908
3909 l_record_found NUMBER;
3910 l_api_name CONSTANT VARCHAR2 (30) := 'chk_rate_quota_date';
3911 BEGIN
3912 x_loading_status := p_loading_status;
3913 x_return_status := fnd_api.g_ret_sts_success;
3914
3915 IF p_quota_id IS NOT NULL
3916 THEN
3917 OPEN rate_quota_curs;
3918
3919 FETCH rate_quota_curs
3920 INTO l_record_found;
3921
3922 CLOSE rate_quota_curs;
3923
3924 IF l_record_found > 0
3925 THEN
3926 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3927 THEN
3928 fnd_message.set_name ('CN', 'CN_RQ_DT_NOT_WIN_QUOTA');
3929 fnd_msg_pub.ADD;
3930 END IF;
3931
3932 x_loading_status := 'RQ_DATE_NOT_WIN_QUOTA';
3933 RAISE fnd_api.g_exc_error;
3934 END IF;
3935 END IF;
3936 EXCEPTION
3937 WHEN fnd_api.g_exc_error
3938 THEN
3939 x_return_status := fnd_api.g_ret_sts_error;
3940 WHEN fnd_api.g_exc_unexpected_error
3941 THEN
3942 x_return_status := fnd_api.g_ret_sts_unexp_error;
3943 x_loading_status := 'UNEXPECTED_ERR';
3944 WHEN OTHERS
3945 THEN
3946 x_return_status := fnd_api.g_ret_sts_unexp_error;
3947 x_loading_status := 'UNEXPECTED_ERR';
3948
3949 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3950 THEN
3951 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3952 END IF;
3953 END chk_rate_quota_date;
3954 END cn_chk_plan_element_pkg;