[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_CUSTOMIZE_PUB
Source
1 PACKAGE BODY CN_Srp_Customize_Pub as
2 -- $Header: cnpsrpcb.pls 120.1 2005/10/27 16:04:41 mblum noship $ -+
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SRP_CUSTOMIZE_PUB';
5
6 PROCEDURE Update_srp_quota_assign(
7 p_api_version IN NUMBER,
8 p_init_msg_list IN VARCHAR2,
9 p_commit IN VARCHAR2,
10 p_validation_level IN NUMBER,
11 p_srp_quota_assign_id IN NUMBER,
12 p_customized_flag IN VARCHAR2,
13 p_quota IN NUMBER,
14 p_fixed_amount IN NUMBER,
15 p_goal IN NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 x_loading_status OUT NOCOPY VARCHAR2,
20 x_status OUT NOCOPY VARCHAR2
21 ) IS
22
23 l_api_name CONSTANT VARCHAR2(30) := 'Update_srp_quota_assign';
24 l_api_version CONSTANT NUMBER := 1.0;
25
26 l_customized_flag_old CN_SRP_QUOTA_ASSIGNS.customized_flag%TYPE;
27 l_target_old CN_SRP_QUOTA_ASSIGNS.target%TYPE;
28 l_ptdr_code_old CN_SRP_QUOTA_ASSIGNS.period_target_dist_rule_code%TYPE;
29 l_ptu_code_old CN_SRP_QUOTA_ASSIGNS.period_target_unit_code%TYPE;
30 l_payment_amount_old CN_SRP_QUOTA_ASSIGNS.payment_amount%TYPE;
31 l_performance_goal_old CN_SRP_QUOTA_ASSIGNS.performance_goal%TYPE;
32 l_quota_id NUMBER;
33 l_org_id NUMBER;
34 l_status VARCHAR2(1);
35
36 BEGIN
37
38 --
39 -- Standard Start of API savepoint
40 --
41 SAVEPOINT update_srp_quota_assign;
42
43 --
44 -- Standard call to check for call compatibility.
45 --
46 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
47 p_api_version ,
48 l_api_name ,
49 G_PKG_NAME )
50 THEN
51 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
52 END IF;
53
54 --
55 -- Initialize message list if p_init_msg_list is set to TRUE.
56 --
57 IF FND_API.to_Boolean( p_init_msg_list ) THEN
58 FND_MSG_PUB.initialize;
59 END IF;
60
61 --
62 -- Initialize API return status to success
63 --
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65 x_loading_status := 'CN_UPDATED';
66
67 select customized_flag
68 ,target
69 ,period_target_dist_rule_code
70 ,period_target_unit_code
71 ,payment_amount
72 ,performance_goal
73 ,quota_id
74 ,org_id
75 into
76 l_customized_flag_old
77 ,l_target_old
78 ,l_ptdr_code_old
79 ,l_ptu_code_old
80 ,l_payment_amount_old
81 ,l_performance_goal_old
82 ,l_quota_id
83 ,l_org_id
84 from cn_srp_quota_assigns_all
85 where srp_quota_assign_id = p_srp_quota_assign_id;
86
87 -- validate org ID
88 mo_global.validate_orgid_pub_api
89 (org_id => l_org_id,
90 status => l_status);
91
92 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
93 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
94 'cn.plsql.cn_srp_customize_pub.update_srp_quota_assign.org_validate',
95 'Validated org_id = ' || l_org_id || ' status = ' || l_status);
96 end if;
97
98
99 cn_srp_quota_assigns_pkg.update_record(
100 p_srp_quota_assign_id
101 ,p_quota
102 ,l_target_old
103 ,null --x_start_period_id
104 ,null --x_salesrep_id NUMBER
105 ,p_customized_flag
106 ,l_customized_flag_old
107 ,l_quota_id
108 ,null --x_rate_schedule_id
109 ,l_ptdr_code_old
110 ,null --x_attributes_changed VARCHAR2
111 ,null --x_distribute_target_flag
112 ,p_fixed_amount --x_payment_amount NUMBER
113 ,l_payment_amount_old
114 ,p_goal
115 ,l_performance_goal_old
116 ,l_ptu_code_old
117 ,l_ptu_code_old
118 ,Sysdate --g_last_update_date
119 ,fnd_global.user_id --g_last_updated_by
120 ,fnd_global.login_id); -- g_last_update_login);
121
122 -- Standard check of p_commit.
123 IF FND_API.To_Boolean( p_commit ) THEN
124 COMMIT WORK;
125 END IF;
126 --
127 -- Standard call to get message count and if count is 1, get message info.
128 --
129
130 FND_MSG_PUB.Count_And_Get
131 (
132 p_count => x_msg_count ,
133 p_data => x_msg_data ,
134 p_encoded => FND_API.G_FALSE
135 );
136
137 EXCEPTION
138 WHEN FND_API.G_EXC_ERROR THEN
139 ROLLBACK TO update_srp_quota_assign;
140 x_return_status := FND_API.G_RET_STS_ERROR ;
141 FND_MSG_PUB.Count_And_Get
142 (
143 p_count => x_msg_count ,
144 p_data => x_msg_data ,
145 p_encoded => FND_API.G_FALSE
146 );
147 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
148 ROLLBACK TO update_srp_quota_assign;
149 x_loading_status := 'UNEXPECTED_ERR';
150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
151 FND_MSG_PUB.Count_And_Get
152 (
153 p_count => x_msg_count ,
154 p_data => x_msg_data ,
155 p_encoded => FND_API.G_FALSE
156 );
157 WHEN OTHERS THEN
158 ROLLBACK TO update_srp_quota_assign;
159 x_loading_status := 'UNEXPECTED_ERR';
160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
161 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
162 THEN
163 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
164 END IF;
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 END Update_srp_quota_assign;
172
173 PROCEDURE Change_srp_quota_custom_flag(
174 p_api_version IN NUMBER,
175 p_init_msg_list IN VARCHAR2,
176 p_commit IN VARCHAR2,
177 p_validation_level IN NUMBER,
178 p_srp_quota_assign_id IN NUMBER,
179 p_customized_flag IN VARCHAR2,
180 x_return_status OUT NOCOPY VARCHAR2,
181 x_msg_count OUT NOCOPY NUMBER,
182 x_msg_data OUT NOCOPY VARCHAR2,
183 x_loading_status OUT NOCOPY VARCHAR2
184 ) IS
185
186 l_api_name CONSTANT VARCHAR2(30) := 'Change_srp_quota_custom_flag';
187 l_api_version CONSTANT NUMBER := 1.0;
188 l_customized_flag_old CN_SRP_QUOTA_ASSIGNS.customized_flag%TYPE;
189 l_target_old CN_SRP_QUOTA_ASSIGNS.target%TYPE;
190 l_ptdr_code_old CN_SRP_QUOTA_ASSIGNS.period_target_dist_rule_code%TYPE;
191 l_ptu_code_old CN_SRP_QUOTA_ASSIGNS.period_target_unit_code%TYPE;
192 l_payment_amount_old CN_SRP_QUOTA_ASSIGNS.payment_amount%TYPE;
193 l_performance_goal_old CN_SRP_QUOTA_ASSIGNS.performance_goal%TYPE;
194
195 l_quota_id CN_QUOTAS.quota_id%TYPE;
196 l_org_id NUMBER;
197 l_status VARCHAR2(1);
198
199 BEGIN
200
201 --
202 -- Standard Start of API savepoint
203 --
204 SAVEPOINT change_srp_quota_custom_flag;
205
206 --
207 -- Standard call to check for call compatibility.
208 --
209 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
210 p_api_version ,
211 l_api_name ,
212 G_PKG_NAME )
213 THEN
214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
215 END IF;
216
217 --
218 -- Initialize message list if p_init_msg_list is set to TRUE.
219 --
220 IF FND_API.to_Boolean( p_init_msg_list ) THEN
221 FND_MSG_PUB.initialize;
222 END IF;
223
224 --
225 -- Initialize API return status to success
226 --
227 x_return_status := FND_API.G_RET_STS_SUCCESS;
228 x_loading_status := 'CN_UPDATED';
229
230 --
231 -- modified By Kumar.
232 --
233 select customized_flag
234 ,quota_id
235 ,target
236 ,period_target_dist_rule_code
237 ,period_target_unit_code
238 ,payment_amount
239 ,performance_goal
240 ,org_id
241 into l_customized_flag_old
242 ,l_quota_id
243 ,l_target_old
244 ,l_ptdr_code_old
245 ,l_ptu_code_old
246 ,l_payment_amount_old
247 ,l_performance_goal_old
248 ,l_org_id
249 from cn_srp_quota_assigns_all
250 where srp_quota_assign_id = p_srp_quota_assign_id;
251
252 -- validate org ID
253 mo_global.validate_orgid_pub_api
254 (org_id => l_org_id,
255 status => l_status);
256
257 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
258 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
259 'cn.plsql.cn_srp_customize_pub.change_srp_quota_custom_flag.org_validate',
260 'Validated org_id = ' || l_org_id || ' status = ' || l_status);
261 end if;
262
263 cn_srp_quota_assigns_pkg.update_record(
264 p_srp_quota_assign_id
265 ,nvl(l_target_old,0)
266 ,nvl(l_target_old,0)
267 ,null --x_start_period_id
268 ,null --x_salesrep_id NUMBER
269 ,p_customized_flag
270 ,l_customized_flag_old
271 ,l_quota_id
272 ,null --x_rate_schedule_id
273 ,l_ptdr_code_old
274 ,null --x_attributes_changed VARCHAR2
275 ,null --x_distribute_target_flag
276 ,l_payment_amount_old
277 ,l_payment_amount_old
278 ,l_performance_goal_old
279 ,l_performance_goal_old
280 ,l_ptu_code_old
281 ,l_ptu_code_old
282 ,Sysdate --g_last_update_date
283 ,fnd_global.user_id --g_last_updated_by
284 ,fnd_global.login_id); --g_last_update_login);
285
286 -- Standard check of p_commit.
287 IF FND_API.To_Boolean( p_commit ) THEN
288 COMMIT WORK; END IF;
289 --
290 -- Standard call to get message count and if count is 1, get message info.
291 --
292
293 FND_MSG_PUB.Count_And_Get
294 (
295 p_count => x_msg_count ,
296 p_data => x_msg_data ,
297 p_encoded => FND_API.G_FALSE
298 );
299 EXCEPTION
300 WHEN FND_API.G_EXC_ERROR THEN
301 ROLLBACK TO change_srp_quota_custom_flag;
302 x_return_status := FND_API.G_RET_STS_ERROR ;
303 FND_MSG_PUB.Count_And_Get
304 (
305 p_count => x_msg_count ,
306 p_data => x_msg_data ,
307 p_encoded => FND_API.G_FALSE
308 );
309 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
310 ROLLBACK TO change_srp_quota_custom_flag;
311 x_loading_status := 'UNEXPECTED_ERR';
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
313 FND_MSG_PUB.Count_And_Get
314 (
315 p_count => x_msg_count ,
316 p_data => x_msg_data ,
317 p_encoded => FND_API.G_FALSE
318 );
319 WHEN OTHERS THEN
320 ROLLBACK TO change_srp_quota_custom_flag;
321 x_loading_status := 'UNEXPECTED_ERR';
322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
323 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
324 THEN
325 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
326 END IF;
327 FND_MSG_PUB.Count_And_Get
328 (
329 p_count => x_msg_count ,
330 p_data => x_msg_data ,
331 p_encoded => FND_API.G_FALSE
332 );
333
334 END Change_srp_quota_custom_flag ;
335
336
337
338 PROCEDURE Update_Srp_Quota_Rules(
339 p_api_version IN NUMBER,
340 p_init_msg_list IN VARCHAR2,
341 p_commit IN VARCHAR2,
342 p_validation_level IN NUMBER,
343 p_quota_rule_id IN NUMBER,
344 p_srp_quota_rule_id IN NUMBER,
345 p_target IN NUMBER,
346 p_payment_amount IN NUMBER,
347 p_performance_goal IN NUMBER,
348 x_return_status OUT NOCOPY VARCHAR2,
349 x_msg_count OUT NOCOPY NUMBER,
350 x_msg_data OUT NOCOPY VARCHAR2,
351 x_loading_status OUT NOCOPY VARCHAR2
352 ) IS
353 l_api_name CONSTANT VARCHAR2(30) := 'Update_srp_quota_rules';
354 l_api_version CONSTANT NUMBER := 1.0;
355
356 l_org_id NUMBER;
357 l_status VARCHAR2(1);
358
359 BEGIN
360 --
361 -- Standard Start of API savepoint
362 --
363 SAVEPOINT update_srp_quota_rules;
364
365 --
366 -- Standard call to check for call compatibility.
367 --
368 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
369 p_api_version ,
370 l_api_name ,
371 G_PKG_NAME )
372 THEN
373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
374 END IF;
375
376 --
377 -- Initialize message list if p_init_msg_list is set to TRUE.
378 --
379 IF FND_API.to_Boolean( p_init_msg_list ) THEN
380 FND_MSG_PUB.initialize;
381 END IF;
382
383 --
384 -- Initialize API return status to success
385 --
386 x_return_status := FND_API.G_RET_STS_SUCCESS;
387 x_loading_status := 'CN_UPDATED';
388
389 SELECT org_id INTO l_org_id
390 FROM cn_srp_quota_rules_all
391 WHERE srp_quota_rule_id = p_srp_quota_rule_id;
392
393 -- validate org ID
394 mo_global.validate_orgid_pub_api
395 (org_id => l_org_id,
396 status => l_status);
397
398 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
399 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
400 'cn.plsql.cn_srp_customize_pub.update_srp_quota_rules.org_validate',
401 'Validated org_id = ' || l_org_id || ' status = ' || l_status);
402 end if;
403
404 cn_srp_quota_rules_pkg.update_record(
405 x_quota_rule_id => p_quota_rule_id,
406 x_srp_quota_rule_id => p_srp_quota_rule_id,
407 x_target => p_target,
408 x_payment_amount => p_payment_amount,
409 x_performance_goal => p_performance_goal);
410
411
412 -- Standard check of p_commit.
413 IF FND_API.To_Boolean( p_commit ) THEN
414 COMMIT WORK; END IF;
415 --
416 -- Standard call to get message count and if count is 1, get message info.
417 --
418
419 FND_MSG_PUB.Count_And_Get
420 (
421 p_count => x_msg_count ,
422 p_data => x_msg_data ,
423 p_encoded => FND_API.G_FALSE
424 );
425
426 EXCEPTION
427 WHEN FND_API.G_EXC_ERROR THEN
428 ROLLBACK TO Update_Srp_Quota_Rules;
429 x_return_status := FND_API.G_RET_STS_ERROR ;
430 FND_MSG_PUB.Count_And_Get
431 (
432 p_count => x_msg_count ,
433 p_data => x_msg_data ,
434 p_encoded => FND_API.G_FALSE
435 );
436 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
437 ROLLBACK TO Update_Srp_Quota_Rules;
438 x_loading_status := 'UNEXPECTED_ERR';
439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
440 FND_MSG_PUB.Count_And_Get
441 (
442 p_count => x_msg_count ,
443 p_data => x_msg_data ,
444 p_encoded => FND_API.G_FALSE
445 );
446 WHEN OTHERS THEN
447 ROLLBACK TO Update_Srp_Quota_Rules;
448 x_loading_status := 'UNEXPECTED_ERR';
449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
450 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
451 THEN
452 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
453 END IF;
454 FND_MSG_PUB.Count_And_Get
455 (
456 p_count => x_msg_count ,
457 p_data => x_msg_data ,
458 p_encoded => FND_API.G_FALSE
459 );
460
461 END Update_Srp_Quota_Rules;
462
463
464 PROCEDURE Update_Srp_Rule_Uplifts
465 (p_api_version IN NUMBER,
466 p_init_msg_list IN VARCHAR2,
467 p_commit IN VARCHAR2,
468 p_validation_level IN NUMBER,
469
470 p_srp_rule_uplift_id IN NUMBER,
471 p_payment_factor IN NUMBER,
472 p_quota_factor IN NUMBER,
473
474 x_return_status OUT NOCOPY VARCHAR2,
475 x_msg_count OUT NOCOPY NUMBER,
476 x_msg_data OUT NOCOPY VARCHAR2,
477 x_loading_status OUT NOCOPY VARCHAR2
478 ) IS
479
480 l_api_name CONSTANT VARCHAR2(30) := 'Update_Srp_Rule_Uplifts';
481 l_api_version CONSTANT NUMBER := 1.0;
482
483 l_org_id NUMBER;
484 l_status VARCHAR2(1);
485
486 BEGIN
487 --
488 -- Standard Start of API savepoint
489 --
490 SAVEPOINT update_srp_rule_uplifts;
491
492 --
493 -- Standard call to check for call compatibility.
494 --
495 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
496 p_api_version ,
497 l_api_name ,
498 G_PKG_NAME )
499 THEN
500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
501 END IF;
502
503 --
504 -- Initialize message list if p_init_msg_list is set to TRUE.
505 --
506 IF FND_API.to_Boolean( p_init_msg_list ) THEN
507 FND_MSG_PUB.initialize;
508 END IF;
509
510 --
511 -- Initialize API return status to success
512 --
513 x_return_status := FND_API.G_RET_STS_SUCCESS;
514 x_loading_status := 'CN_UPDATED';
515
516 SELECT org_id INTO l_org_id
517 FROM cn_srp_rule_uplifts_all
518 WHERE srp_rule_uplift_id = p_srp_rule_uplift_id;
519
520 -- validate org ID
521 mo_global.validate_orgid_pub_api
522 (org_id => l_org_id,
523 status => l_status);
524
525 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
526 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
527 'cn.plsql.cn_srp_customize_pub.update_srp_rule_uplifts.org_validate',
528 'Validated org_id = ' || l_org_id || ' status = ' || l_status);
529 end if;
530
531
532 cn_srp_rule_uplifts_pkg.update_record
533 (p_srp_rule_uplift_id => p_srp_rule_uplift_id
534 ,p_payment_factor => p_payment_factor
535 ,p_quota_factor => p_quota_factor
536 ,p_last_update_date => sysdate
537 ,p_last_updated_by => fnd_global.user_id
538 ,p_last_update_login => fnd_global.login_id);
539
540
541 -- Standard check of p_commit.
542 IF FND_API.To_Boolean( p_commit ) THEN
543 COMMIT WORK; END IF;
544
545 --
546 -- Standard call to get message count and if count is 1, get message info.
547 --
548
549 FND_MSG_PUB.Count_And_Get
550 (
551 p_count => x_msg_count ,
552 p_data => x_msg_data ,
553 p_encoded => FND_API.G_FALSE
554 );
555
556 EXCEPTION
557 WHEN FND_API.G_EXC_ERROR THEN
558 ROLLBACK TO Update_Srp_Rule_Uplifts;
559 x_return_status := FND_API.G_RET_STS_ERROR ;
560 FND_MSG_PUB.Count_And_Get
561 (
562 p_count => x_msg_count ,
563 p_data => x_msg_data ,
564 p_encoded => FND_API.G_FALSE
565 );
566 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
567 ROLLBACK TO Update_Srp_Rule_Uplifts;
568 x_loading_status := 'UNEXPECTED_ERR';
569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
570 FND_MSG_PUB.Count_And_Get
571 (
572 p_count => x_msg_count ,
573 p_data => x_msg_data ,
574 p_encoded => FND_API.G_FALSE
575 );
576 WHEN OTHERS THEN
577 ROLLBACK TO Update_Srp_Rule_Uplifts;
578 x_loading_status := 'UNEXPECTED_ERR';
579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
580 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
581 THEN
582 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
583 END IF;
584 FND_MSG_PUB.Count_And_Get
585 (
586 p_count => x_msg_count ,
587 p_data => x_msg_data ,
588 p_encoded => FND_API.G_FALSE
589 );
590
591 END Update_Srp_Rule_Uplifts;
592
593
594 END CN_Srp_Customize_Pub ;