[Home] [Help]
PACKAGE BODY: APPS.CN_REVENUE_CLASS_PVT
Source
1 PACKAGE BODY CN_REVENUE_CLASS_PVT AS
2 --$Header: cnvrclsb.pls 120.2 2005/08/07 23:04:47 vensrini noship $
3
4 --Global Variables
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_REVENUE_CLASS_PVT';
7 G_LAST_UPDATE_DATE DATE := Sysdate;
8 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
9 G_CREATION_DATE DATE := Sysdate;
10 G_CREATED_BY NUMBER := fnd_global.user_id;
11 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
12
13 --=========================================================================
14 -- Start of comments
15 -- API name : Create_Revenue_class
16 -- Type : Private
17 -- Function : This private API can be used to create Revenue Class
18 -- Pre-reqs : None.
19 -- Parameters :
20 -- IN : p_api_version IN NUMBER Required
21 -- p_init_msg_list IN VARCHAR2 Optional
22 -- Default = FND_API.G_FALSE
23 -- p_commit IN VARCHAR2 Optional
24 -- Default = FND_API.G_FALSE
25 -- p_validation_level IN NUMBER Optional
26 -- Default = FND_API.G_VALID_LEVEL_FULL
27 --
28 -- OUT : x_return_status OUT VARCHAR2(1)
29 -- x_msg_count OUT NUMBER
30 -- x_msg_data OUT VARCHAR2(2000)
31 --
32 -- Version : Current version 1.0
33 -- previous version y.y
34 -- Changed....
35 -- Initial version 1.0
36 --
37 -- Notes : Note text
38 --
39 -- End of comments
40 --=========================================================================
41 PROCEDURE create_revenue_class
42 ( p_api_version IN NUMBER,
43 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
44 p_commit IN VARCHAR2 := FND_API.G_FALSE,
45 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
46 x_return_status OUT NOCOPY VARCHAR2,
47 x_msg_count OUT NOCOPY NUMBER,
48 x_msg_data OUT NOCOPY VARCHAR2,
49 x_loading_status OUT NOCOPY VARCHAR2,
50 x_revenue_class_id OUT NOCOPY NUMBER,
51 p_revenue_class_rec IN CN_REVENUE_CLASS_PVT.revenue_class_rec_type,
52 p_org_id IN NUMBER
53 )
54 IS
55
56 l_api_name CONSTANT VARCHAR2(30) := 'Create_Revenue_class';
57 l_api_version CONSTANT NUMBER := 1.0;
58 l_loading_status VARCHAR2(4000);
59 l_error_status NUMBER;
60 l_error_parameter VARCHAR2(30);
61 l_rowid VARCHAR2(4000);
62 l_sequence_number NUMBER;
63 l_count NUMBER;
64 l_revenue_class_id NUMBER;
65
66 Cursor get_rev_cls( p_revenue_class_name cn_revenue_classes.name%TYPE ) IS
67 select count(1)
68 from cn_revenue_classes
69 where name = p_revenue_class_name and org_id = p_org_id;
70
71 BEGIN
72
73 -- Standard Start of API savepoint
74 SAVEPOINT Create_Revenue_Class;
75 -- Standard call to check for call compatibility.
76 IF NOT FND_API.Compatible_API_Call ( l_api_version,
77 p_api_version,
78 l_api_name,
79 G_PKG_NAME )
80 THEN
81 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
82 END IF;
83
84 -- Initialize message list if p_init_msg_list is set to TRUE.
85 IF FND_API.to_Boolean( p_init_msg_list )
86 THEN
87 FND_MSG_PUB.initialize;
88 END IF;
89
90 -- Initialize API return status to success
91 x_return_status := FND_API.G_RET_STS_SUCCESS;
92 x_loading_status := 'CN_INSERTED';
93
94 -- API body
95 if p_revenue_class_rec.name is NULL THEN
96 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
97 THEN
98 fnd_message.set_name('CN', 'CN_REV_NAME_NOT_NULL');
99 fnd_msg_pub.add;
100 END IF;
101 x_loading_status := 'CN_REV_NAME_NOT_NULL';
102 RAISE FND_API.G_EXC_ERROR;
103 END IF;
104
105 -- Duplicate Check
106 l_count := 0;
107 open get_rev_cls(p_revenue_class_rec.name);
108 fetch get_rev_cls into l_count;
109 close get_rev_cls;
110
111 IF l_count >= 1 THEN
112
113 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
114 THEN
115 fnd_message.set_name('CN', 'CN_REV_CLASS_DUPLICATE');
116 fnd_msg_pub.add;
117 END IF;
118 x_loading_status := 'CN_REV_CLASS_DUPLICATE';
119 RAISE FND_API.G_EXC_ERROR;
120 END IF;
121
122
123
124 CN_REVENUE_CLASS_PKG.insert_row
125 (x_revenue_class_id => x_revenue_class_id
126 ,p_name => p_revenue_class_rec.name
127 ,p_description => p_revenue_class_rec.description
128 ,p_liability_account_id => p_revenue_class_rec.liability_account_id
129 ,p_expense_account_id => p_revenue_class_rec.expense_account_id
130 ,p_creation_date => sysdate
131 ,p_created_by => g_created_by
132 ,p_last_update_date => sysdate
133 ,p_last_updated_by => g_last_updated_by
134 ,p_last_update_login => g_last_update_login
135 ,p_org_id => p_org_id);
136
137 -- End of API body.
138
139 -- Standard check of p_commit.
140 IF FND_API.To_Boolean( p_commit )
141 THEN
142 COMMIT WORK;
143 END IF;
144
145 FND_MSG_PUB.Count_And_Get
146 (
147 p_count => x_msg_count ,
148 p_data => x_msg_data ,
149 p_encoded => FND_API.G_FALSE
150 );
151 EXCEPTION
152 WHEN FND_API.G_EXC_ERROR THEN
153 ROLLBACK TO Create_Revenue_Class;
154 x_return_status := FND_API.G_RET_STS_ERROR ;
155 FND_MSG_PUB.Count_And_Get
156 (
157 p_count => x_msg_count ,
158 p_data => x_msg_data ,
159 p_encoded => FND_API.G_FALSE
160 );
161 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
162 ROLLBACK TO Create_Revenue_Class;
163 x_loading_status := 'UNEXPECTED_ERR';
164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
165 FND_MSG_PUB.Count_And_Get
166 (
167 p_count => x_msg_count ,
168 p_data => x_msg_data ,
169 p_encoded => FND_API.G_FALSE
170 );
171 WHEN OTHERS THEN
172 ROLLBACK TO Create_Revenue_Class;
173 x_loading_status := 'UNEXPECTED_ERR';
174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
175 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
176 THEN
177 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
178 END IF;
179 FND_MSG_PUB.Count_And_Get
180 (
181 p_count => x_msg_count ,
182 p_data => x_msg_data ,
183 p_encoded => FND_API.G_FALSE
184 );
185 END Create_Revenue_Class;
186 --=========================================================================
187 -- Start of comments
188 -- API name : Update_Revenue_class
189 -- Type : Public
190 -- Function : This Public API can be used to update a Revenue Class
191 -- in Oracle Sales
192 -- Compensation.
193 -- Pre-reqs : None.
194 -- Parameters :
195 -- IN : p_api_version IN NUMBER Required
196 -- p_init_msg_list IN VARCHAR2 Optional
197 -- Default = FND_API.G_FALSE
198 -- p_commit IN VARCHAR2 Optional
199 -- Default = FND_API.G_FALSE
200 -- p_validation_level IN NUMBER Optional
201 -- Default = FND_API.G_VALID_LEVEL_FULL
202 --
203 -- OUT : x_return_status OUT VARCHAR2(1)
204 -- x_msg_count OUT NUMBER
205 -- x_msg_data OUT VARCHAR2(2000)
206 --
207 -- Version : Current version 1.0
208 -- previous version y.y
209 -- Changed....
210 -- Initial version 1.0
211 --
212 -- Notes : Note text
213 --
214 -- End of comments
215 --=========================================================================
216
217 PROCEDURE Update_Revenue_class
218 ( p_api_version IN NUMBER,
219 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
220 p_commit IN VARCHAR2 := FND_API.G_FALSE,
221 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_msg_count OUT NOCOPY NUMBER,
224 x_msg_data OUT NOCOPY VARCHAR2,
225 x_loading_status OUT NOCOPY VARCHAR2,
226 pold_revenue_class_rec IN OUT NOCOPY CN_REVENUE_CLASS_PVT.revenue_class_rec_type,
227 p_revenue_class_rec IN OUT NOCOPY CN_REVENUE_CLASS_PVT.revenue_class_rec_type
228 ) IS
229
230 CURSOR l_ovn_csr IS
231 SELECT nvl(object_version_number,1)
232 FROM cn_revenue_classes
233 WHERE revenue_class_id = p_revenue_class_rec.revenue_class_id;
234
235 l_api_name CONSTANT VARCHAR2(30) := 'Update_Revenue_Class';
236 l_api_version CONSTANT NUMBER := 1.0;
237 l_loading_st VARCHAR2(4000);
238 l_count NUMBER;
239
240 l_object_version_number NUMBER;
241
242
243 Cursor get_rev_cls( p_revenue_class_name cn_revenue_classes.name%TYPE,
244 p_revenue_class_id NUMBER ) IS
245 select count(1)
246 from cn_revenue_classes
247 where name = p_revenue_class_name
248 and revenue_class_id <> p_revenue_class_id;
249
250 BEGIN
251
252 -- Standard Start of API savepoint
253 SAVEPOINT Update_Revenue_Class;
254 -- Standard call to check for call compatibility.
255 IF NOT FND_API.Compatible_API_Call ( l_api_version,
256 p_api_version,
257 l_api_name,
258 G_PKG_NAME )
259 THEN
260 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
261 END IF;
262
263 -- Initialize message list if p_init_msg_list is set to TRUE.
264 IF FND_API.to_Boolean( p_init_msg_list )
265 THEN
266 FND_MSG_PUB.initialize;
267 END IF;
268
269 -- Initialize API return status to success
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271 x_loading_status := 'CN_UPDATED';
272
273 pold_revenue_class_rec.revenue_class_id := p_revenue_class_rec.revenue_class_id;
274
275 if p_revenue_class_rec.name is null THEN
276 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
277 THEN
278 fnd_message.set_name('CN', 'CN_REV_CLS_NOT_NULL');
279 fnd_msg_pub.add;
280 END IF;
281 x_loading_status := 'CN_REV_CLS_NOT_NULL';
282 RAISE FND_API.G_EXC_ERROR;
283 END IF;
284
285 if p_revenue_class_rec.revenue_Class_id is null THEN
286 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
287 THEN
288 fnd_message.set_name('CN', 'CN_REV_CLS_NOT_NULL');
289 fnd_msg_pub.add;
290 END IF;
291 x_loading_status := 'CN_REV_CLS_NOT_NULL';
292 RAISE FND_API.G_EXC_ERROR;
293 END IF;
294
295
296 -- Duplicate Check
297 l_count := 0;
298 open get_rev_cls(p_revenue_class_rec.name, p_revenue_class_rec.revenue_class_id);
299 fetch get_rev_cls into l_count;
300 close get_rev_cls;
301
302 IF l_count >= 1 THEN
303
304 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
305 THEN
306 fnd_message.set_name('CN', 'CN_REV_CLASS_DUPLICATE');
307 fnd_msg_pub.add;
308 END IF;
309 x_loading_status := 'CN_REV_CLASS_DUPLICATE';
310 RAISE FND_API.G_EXC_ERROR;
311 END IF;
312
313
314 -- check if the object version number is the same
315 OPEN l_ovn_csr;
316 FETCH l_ovn_csr INTO l_object_version_number;
317 CLOSE l_ovn_csr;
318
319 /* IF (nvl(l_object_version_number,1) <>
320 nvl(p_revenue_class_rec.object_version_number,1)) THEN
321
322 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
323 THEN
324 fnd_message.set_name('CN', 'CN_INVALID_OBJECT_VERSION');
325 fnd_msg_pub.add;
326 END IF;
327
328 x_loading_status := 'CN_INVALID_OBJECT_VERSION';
329 RAISE FND_API.G_EXC_ERROR;
330
331 end if;
332 */
333
334
335 CN_REVENUE_CLASS_PKG.update_row
336 (p_revenue_class_id => p_revenue_class_rec.revenue_class_id
337 ,p_name => p_revenue_class_rec.name
338 ,p_description => p_revenue_class_rec.description
339 ,p_liability_account_id => p_revenue_class_rec.liability_account_id
340 ,p_expense_account_id => p_revenue_class_rec.expense_account_id
341 ,p_object_version_number=> p_revenue_class_rec.object_version_number
342 ,p_last_update_date => sysdate
343 ,p_last_updated_by => g_last_updated_by
344 ,p_last_update_login => g_last_update_login);
345
346
347 -- End of API body.
348 -- Standard check of p_commit.
349 IF FND_API.To_Boolean( p_commit )
350 THEN
351 COMMIT WORK;
352 END IF;
353
354
355 -- Standard call to get message count and if count is 1, get message info.
356 FND_MSG_PUB.Count_And_Get
357 (
358 p_count => x_msg_count ,
359 p_data => x_msg_data ,
360 p_encoded => FND_API.G_FALSE
361 );
362
363 EXCEPTION
364 WHEN FND_API.G_EXC_ERROR THEN
365 ROLLBACK TO Update_Revenue_Class;
366 x_return_status := FND_API.G_RET_STS_ERROR ;
367
368 FND_MSG_PUB.Count_And_Get
369 (
370 p_count => x_msg_count ,
371 p_data => x_msg_data ,
372 p_encoded => FND_API.G_FALSE
373 );
374
375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
376 ROLLBACK TO Update_Revenue_Class;
377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
378 FND_MSG_PUB.Count_And_Get
379 (
380 p_count => x_msg_count ,
381 p_data => x_msg_data ,
382 p_encoded => FND_API.G_FALSE
383 );
384
385 WHEN OTHERS THEN
386 ROLLBACK TO Update_Revenue_Class;
387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
388 IF FND_MSG_PUB.Check_Msg_Level
389 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
390 THEN
391 FND_MSG_PUB.Add_Exc_Msg
392 (G_PKG_NAME,
393 l_api_name
394 );
395 END IF;
396
397 FND_MSG_PUB.Count_And_Get
398 (
399 p_count => x_msg_count ,
400 p_data => x_msg_data ,
401 p_encoded => FND_API.G_FALSE
402 );
403
404 END;
405 --=========================================================================
406 -- Start of comments
407 -- API name : Delete_Revenue_Class
408 -- Type : Private
409 -- Function : This Public API can be used to delete Revenue Class
410 -- Pre-reqs : None.
411 -- Parameters :
412 -- IN : p_api_version IN NUMBER Required
413 -- p_init_msg_list IN VARCHAR2 Optional
414 -- Default = FND_API.G_FALSE
415 -- p_commit IN VARCHAR2 Optional
416 -- Default = FND_API.G_FALSE
417 -- p_validation_level IN NUMBER Optional
418 -- Default = FND_API.G_VALID_LEVEL_FULL
419 -- p_ruleset_rec_type IN
420 --
421 -- OUT : x_return_status OUT VARCHAR2(1)
422 -- x_msg_count OUT NUMBER
423 -- x_msg_data OUT VARCHAR2(2000)
424 --
425 -- Version : Current version 1.0
426 -- previous version y.y
427 -- Changed....
428 -- Initial version 1.0
429 --
430 --
431 -- End of comments
432 --=========================================================================
433 PROCEDURE Delete_Revenue_Class
434 ( p_api_version IN NUMBER,
435 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
436 p_commit IN VARCHAR2 := FND_API.G_FALSE,
437 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
438 x_return_status OUT NOCOPY VARCHAR2,
439 x_msg_count OUT NOCOPY NUMBER,
440 x_msg_data OUT NOCOPY VARCHAR2,
441 x_loading_status OUT NOCOPY VARCHAR2,
442 p_revenue_class_id IN NUMBER
443 ) IS
444
445
446 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Revenue_Class';
447 l_api_version CONSTANT NUMBER := 1.0;
448 l_status NUMBER;
449
450 l_loading_status VARCHAR2(100);
451 l_error_parameter VARCHAR2(100);
452 l_error_status NUMBER;
453
454 l_count NUMBER := 0;
455 l_rule_count NUMBER := 0;
456 l_hierarchy_count NUMBER := 0;
457 l_quota_rule_count NUMBER := 0;
458 l_total_count NUMBER := 0;
459
460 --
461 -- cursor to get the revenue classes
462 --
463 cursor get_rev is
464 select name
465 from cn_revenue_classes
466 where revenue_class_id = p_revenue_class_id;
467
468 l_revenue_class_name cn_revenue_classes.name%TYPE;
469 l_env_org_id NUMBER;
470
471 BEGIN
472
473 -- Standard Start of API savepoint
474 SAVEPOINT Delete_Revenue_class;
475 -- Standard call to check for call compatibility.
476 IF NOT FND_API.Compatible_API_Call ( l_api_version,
477 p_api_version,
478 l_api_name,
479 G_PKG_NAME )
480 THEN
481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
482 END IF;
483
484 -- Initialize message list if p_init_msg_list is set to TRUE.
485 IF FND_API.to_Boolean( p_init_msg_list )
486 THEN
487 FND_MSG_PUB.initialize;
488 END IF;
489
490 -- Initialize API return status to success
491 x_return_status := FND_API.G_RET_STS_SUCCESS;
492
493 --
494 -- get revenue class
495 --
496 open get_rev;
497 fetch get_rev into l_revenue_class_name;
498 close get_rev;
499
500 --
501 -- get the rule count before the delete the the revenue class
502 --
503 SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
504 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
505 INTO l_env_org_id FROM dual;
506
507 SELECT count(rule_id)
508 INTO l_rule_count
509 FROM cn_rules_all_b
510 WHERE revenue_class_id = p_revenue_class_id
511 AND NVL(ORG_ID, l_env_org_id) = l_env_org_id;
512
513 --
514 -- get the quota rules before deleting the revenue class
515 --
516 SELECT count(quota_rule_id)
517 INTO l_quota_rule_count
518 FROM cn_quota_rules
519 WHERE revenue_class_id = p_revenue_Class_id;
520
521 --
522 -- get the hierarchy count
523 --
524
525 SELECT nvl(MAX(ref_count),0)
526 INTO l_hierarchy_count
527 FROM cn_hierarchy_nodes chn , cn_dim_hierarchies cdh
528 WHERE chn.external_id = p_revenue_Class_id
529 AND chn.dim_hierarchy_id = cdh.dim_hierarchy_id
530 AND cdh.header_dim_hierarchy_id = -1001;
531
532 IF (l_hierarchy_count = 0) THEN
533
534 -- Delete this revenue class from nodes table.
535 DELETE FROM cn_hierarchy_nodes
536 WHERE external_id = p_revenue_Class_id
537 AND dim_hierarchy_id IN (SELECT dim_hierarchy_id
538 FROM cn_dim_hierarchies
539 WHERE header_dim_hierarchy_id = -1001);
540 END IF;
541
542 l_total_count := l_rule_count + l_hierarchy_count + l_quota_rule_count;
543
544 IF (l_total_count <> 0) THEN
545 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
546 THEN
547 fnd_message.set_name('CN','REV_CLS_EXISTS_SW');
548 fnd_message.set_token('REV', l_revenue_class_name);
549 fnd_message.set_token('RULE_COUNT', l_rule_count);
550 fnd_message.set_token('QUOTA_RULE_COUNT',l_quota_rule_count);
551 fnd_message.set_token('HIERARCHY_COUNT', l_hierarchy_count);
552 fnd_msg_pub.add;
553 END IF;
554 x_loading_status := 'REV_CLS_EXISTS_SW';
555 RAISE FND_API.G_EXC_ERROR;
556 END IF;
557
558 -- delete the revenue class
559
560 CN_REVENUE_CLASS_PKG.Delete_row(p_revenue_Class_id);
561
562 -- End of API body.
563
564 -- Standard check of p_commit.
565 IF FND_API.To_Boolean( p_commit )
566 THEN
567 COMMIT WORK;
568 END IF;
569
570 -- Standard call to get message count and if count is 1, get message info.
571 FND_MSG_PUB.Count_And_Get
572 (
573 p_count => x_msg_count ,
574 p_data => x_msg_data ,
575 p_encoded => FND_API.G_FALSE
576 );
577
578 EXCEPTION
579 WHEN FND_API.G_EXC_ERROR THEN
580 ROLLBACK TO Delete_Revenue_Class;
581 x_return_status := FND_API.G_RET_STS_ERROR ;
582 FND_MSG_PUB.Count_And_Get
583 (
584 p_count => x_msg_count ,
585 p_data => x_msg_data ,
586 p_encoded => FND_API.G_FALSE
587 );
588
589 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
590 ROLLBACK TO Delete_Revenue_Class;
591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
592
593 FND_MSG_PUB.Count_And_Get
594 (
595 p_count => x_msg_count ,
596 p_data => x_msg_data ,
597 p_encoded => FND_API.G_FALSE
598 );
599
600 WHEN OTHERS THEN
601 ROLLBACK TO Delete_Revenue_Class;
602 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
603 IF FND_MSG_PUB.Check_Msg_Level
604 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
605 THEN
606 FND_MSG_PUB.Add_Exc_Msg
607 (G_PKG_NAME,
608 l_api_name
609 );
610 END IF;
611
612 FND_MSG_PUB.Count_And_Get
613 (
614 p_count => x_msg_count ,
615 p_data => x_msg_data ,
616 p_encoded => FND_API.G_FALSE
617 );
618
619 END;
620
621
622 END CN_REVENUE_CLASS_PVT;