[Home] [Help]
PACKAGE BODY: APPS.CN_SFP_FORMULA_CMN_PKG
Source
1 PACKAGE BODY CN_SFP_FORMULA_CMN_PKG AS
2 /*$Header: cnvfscmb.pls 115.19 2003/11/13 21:10:28 fmburu ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CN_SFP_FORMULA_CMN_PKG';
5
6
7 PROCEDURE get_payout_for_attain
8 (
9 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
10 p_validation_level IN NUMBER :=
11 FND_API.G_VALID_LEVEL_FULL,
12 p_srp_quota_cate_id IN NUMBER,
13 p_est_achievement IN NUMBER,
14 p_what_if_flag IN BOOLEAN := FALSE,
15 x_estimated_payout OUT NOCOPY NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2
19 ) IS
20
21 l_formula_id NUMBER;
22 l_form_pkg_stmt VARCHAR2(1000);
23 l_est_achievement NUMBER;
24
25 l_formula_status VARCHAR2(30);
26 l_estimated_payout NUMBER;
27 l_return_status VARCHAR2(1);
28
29 l_object_version_number NUMBER;
30 l_org_id NUMBER;
31
32 l_api_name CONSTANT VARCHAR2(30) := 'get_payout_for_attain';
33
34 BEGIN
35 -- Standard Start of API savepoint
36 SAVEPOINT get_payout_for_attain;
37
38 -- Initialize message list if p_init_msg_list is set to TRUE.
39 IF FND_API.to_Boolean( p_init_msg_list ) THEN
40 FND_MSG_PUB.initialize;
41 END IF;
42 -- Initialize API return status to success
43 x_return_status := FND_API.G_RET_STS_SUCCESS;
44
45
46
47 -- Find the formula associated with the srp_quota_cate
48
49 SELECT rqc.calc_formula_id
50 INTO l_formula_id
51 FROM cn_srp_quota_cates sqc,
52 cn_role_quota_cates rqc
53 WHERE sqc.role_quota_cate_id = rqc.role_quota_cate_id
54 AND sqc.srp_quota_cate_id = p_srp_quota_cate_id
55 ;
56
57 IF l_formula_id IS NULL THEN
58 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
59 THEN
60 FND_MESSAGE.SET_NAME('CN', 'CN_FORMULA_NOT_ASSIGNED');
61 FND_MSG_PUB.Add;
62 END IF;
63 RAISE FND_API.G_EXC_ERROR;
64 END IF;
65
66 SELECT formula_status, org_id
67 INTO l_formula_status, l_org_id
68 FROM cn_calc_formulas
69 WHERE calc_formula_id = l_formula_id;
70
71 IF l_formula_status <> 'COMPLETE' THEN
72 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
73 THEN
74 FND_MESSAGE.SET_NAME('CN', 'CN_FORMULA_INCOMPLETE');
75 FND_MSG_PUB.Add;
76 END IF;
77 RAISE FND_API.G_EXC_ERROR;
78 END IF;
79
80 IF p_what_if_flag THEN -- What if scenario
81
82 --- ISSUE: Currency convert
83
84 SELECT
85 DECODE(qc.quota_unit_code, 'REVENUE', ( ROUND(NVL(sqc.est_achievement,0)/NVL(pt.rounding_factor, 1))*NVL(pt.rounding_factor, 1)),
86 'UNIT', (NVL(sqc.est_achievement,0)), ( ROUND(NVL(sqc.est_achievement,0)/NVL(pt.rounding_factor, 1))*NVL(pt.rounding_factor, 1)) ) , sqc.object_version_number
87 INTO l_est_achievement, l_object_version_number
88 FROM cn_srp_quota_cates sqc,
89 cn_role_details_v pt,
90 cn_quota_categories qc
91 WHERE sqc.srp_quota_cate_id = p_srp_quota_cate_id
92 AND sqc.role_id = pt.role_id
93 AND sqc.quota_category_id = qc.quota_category_id
94 ;
95 ELSE
96 l_est_achievement := p_est_achievement;
97 END IF;
98
99
100 l_form_pkg_stmt :=
101 'BEGIN cn_formula_' || abs(l_formula_id) || '_' || abs(l_org_id) || '_pkg.get_estimated_payout'
102 ||'(:p_srp_quota_cate_id, :p_est_achievement, :x_estimated_payout, :x_return_status) ; END ;'
103 ;
104
105
106 BEGIN
107
108 EXECUTE IMMEDIATE l_form_pkg_stmt
109 USING p_srp_quota_cate_id, l_est_achievement, IN OUT l_estimated_payout, IN OUT l_return_status;
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
114 THEN
115 FND_MESSAGE.SET_NAME('CN', 'CN_FORMU_UNEXP_ERR');
116 FND_MSG_PUB.Add;
117 END IF;
118 RAISE FND_API.G_EXC_ERROR;
119 END;
120
121 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
122 RAISE FND_API.G_EXC_ERROR;
123 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
125 ELSIF l_return_status = 'Z' THEN
126 /*
127 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
128 THEN
129 FND_MESSAGE.SET_NAME('CN', 'CN_FORM_DIV_BY_ZERO');
130 FND_MSG_PUB.Add;
131 END IF;
132 RAISE FND_API.G_EXC_ERROR;
133 */
134 -- Next line added after supressing divide by zero message
135 l_estimated_payout := 0;
136
137 END IF;
138
139 x_return_status := l_return_status;
140 x_estimated_payout := l_estimated_payout;
141
142 -- Standard call to get message count and if count is 1, get message
143 -- info.
144 FND_MSG_PUB.Count_And_Get
145 ( p_count => x_msg_count ,
146 p_data => x_msg_data );
147
148
149 EXCEPTION
150 WHEN FND_API.G_EXC_ERROR THEN
151 ROLLBACK TO get_payout_for_attain;
152 x_return_status := FND_API.G_RET_STS_ERROR ;
153 FND_MSG_PUB.Count_And_Get
154 (p_count => x_msg_count ,
155 p_data => x_msg_data );
156 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
157 ROLLBACK TO get_payout_for_attain;
158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
159 FND_MSG_PUB.Count_And_Get
160 (p_count => x_msg_count ,
161 p_data => x_msg_data );
162 WHEN OTHERS THEN
163 ROLLBACK TO get_payout_for_attain;
164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
165 IF FND_MSG_PUB.Check_Msg_Level
166 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
167 THEN
168 FND_MSG_PUB.Add_Exc_Msg
169 (G_PKG_NAME ,
170 l_api_name );
171 END IF;
172 FND_MSG_PUB.Count_And_Get
173 (p_count => x_msg_count ,
174 p_data => x_msg_data );
175
176 END;
177
178
179
180 PROCEDURE get_payout_for_pct_attain
181 (
182 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
183 p_validation_level IN NUMBER :=
184 FND_API.G_VALID_LEVEL_FULL,
185 p_srp_quota_cate_id IN NUMBER,
186 p_attain_percent IN NUMBER,
187 p_annual_quota IN NUMBER,
188 p_what_if_flag IN BOOLEAN := FALSE,
189 x_estimated_payout OUT NOCOPY NUMBER,
190 x_return_status OUT NOCOPY VARCHAR2,
191 x_msg_count OUT NOCOPY NUMBER,
192 x_msg_data OUT NOCOPY VARCHAR2
193 ) IS
194
195 l_est_achievement NUMBER;
196 l_api_name CONSTANT VARCHAR2(30) := 'get_payout_for_pct_attain';
197
198 BEGIN
199
200 -- Standard Start of API savepoint
201 SAVEPOINT get_payout_for_pct_attain;
202
203 -- Initialize message list if p_init_msg_list is set to TRUE.
204 IF FND_API.to_Boolean( p_init_msg_list ) THEN
205 FND_MSG_PUB.initialize;
206 END IF;
207 -- Initialize API return status to success
208 x_return_status := FND_API.G_RET_STS_SUCCESS;
209
210 l_est_achievement := p_attain_percent/100*p_annual_quota;
211
212 get_payout_for_attain
213 (
214 p_srp_quota_cate_id => p_srp_quota_cate_id,
215 p_est_achievement => l_est_achievement,
216 x_estimated_payout => x_estimated_payout,
217 x_return_status => x_return_status,
218 x_msg_count => x_msg_count,
219 x_msg_data => x_msg_data
220 );
221
222 -- Standard call to get message count and if count is 1, get message
223 -- info.
224 FND_MSG_PUB.Count_And_Get
225 ( p_count => x_msg_count ,
226 p_data => x_msg_data );
227
228 EXCEPTION
229 WHEN FND_API.G_EXC_ERROR THEN
230 ROLLBACK TO get_payout_for_pct_attain;
231 x_return_status := FND_API.G_RET_STS_ERROR ;
232 FND_MSG_PUB.Count_And_Get
233 (p_count => x_msg_count ,
234 p_data => x_msg_data );
235 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
236 ROLLBACK TO get_payout_for_pct_attain;
237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
238 FND_MSG_PUB.Count_And_Get
239 (p_count => x_msg_count ,
240 p_data => x_msg_data );
241 WHEN OTHERS THEN
242 ROLLBACK TO get_payout_for_pct_attain;
243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
244 IF FND_MSG_PUB.Check_Msg_Level
245 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
246 THEN
247 FND_MSG_PUB.Add_Exc_Msg
248 (G_PKG_NAME ,
252 (p_count => x_msg_count ,
249 l_api_name );
250 END IF;
251 FND_MSG_PUB.Count_And_Get
253 p_data => x_msg_data );
254
255
256 END;
257
258
259
260 PROCEDURE get_rates(
261 p_srp_quota_cate_id NUMBER ,
262 p_split_flag VARCHAR2 ,
263 p_itd_flag VARCHAR2,
264 p_amount IN NUMBER,
265 x_rate OUT NOCOPY NUMBER,
266 x_rate_tier_id OUT NOCOPY NUMBER,
267 x_tier_split OUT NOCOPY NUMBER) IS
268
269 --bug2731160: remove cn_rate_tiers use cn_role_quota_rates instead
270 CURSOR c_rates_info IS
271 SELECT rdt.maximum_amount,
272 rdt.minimum_amount,
273 sqr.comm_rate comm_rate,
274 rd.dim_unit_code tier_unit_code
275 FROM
276 cn_rate_dim_tiers rdt,
277 cn_rate_sch_dims rsd,
278 cn_rate_dimensions rd,
279 cn_srp_quota_rates sqr,
280 cn_role_quota_rates rqr
281 WHERE
282 sqr.role_quota_rate_id = rqr.role_quota_rate_id
283 AND rqr.rate_schedule_id = rsd.rate_schedule_id
284 AND rsd.rate_dimension_id = rdt.rate_dimension_id
285 AND rsd.rate_dimension_id = rd.rate_dimension_id
286 AND sqr.rate_tier_id = rdt.tier_sequence
287 AND sqr.srp_quota_cate_id = p_srp_quota_cate_id
288 ORDER BY rdt.minimum_amount;
289
290 /*
291 CURSOR c_rates_info IS
292 SELECT rdt.maximum_amount,
293 rdt.minimum_amount,
294 sqr.comm_rate comm_rate,
295 rd.dim_unit_code tier_unit_code
296 FROM
297 cn_rate_dim_tiers rdt,
298 cn_rate_sch_dims rsd,
299 cn_rate_tiers rt,
300 cn_rate_dimensions rd,
301 cn_srp_quota_rates sqr
302 WHERE sqr.rate_tier_id = rt.rate_tier_id
303 AND rt.rate_schedule_id = rsd.rate_schedule_id
304 AND rsd.rate_dimension_id = rdt.rate_dimension_id
305 AND rsd.rate_dimension_id = rd.rate_dimension_id
306 AND rt.rate_sequence = rdt.tier_sequence
307 AND sqr.srp_quota_cate_id = p_srp_quota_cate_id
308 ORDER BY rdt.minimum_amount
309 ;
310 */
311
312 i NUMBER;
313 j NUMBER;
314 l_rate NUMBER;
315 l_rate_factor NUMBER;
316
317 l_amount NUMBER;
318 l_counter NUMBER;
319
320 l_rates_info_tbl rates_info_tbl_type;
321
322 l_max_exceeded BOOLEAN := FALSE ;
323 l_max_rate NUMBER ;
324 l_max_tier_range NUMBER := 0 ;
325
326 BEGIN
327
328 l_amount := p_amount;
329
330 i := 0;
331
332 FOR rates_info_rec in c_rates_info LOOP
333 i := i + 1;
334 l_rates_info_tbl(i).comm_rate := rates_info_rec.comm_rate;
335 l_rates_info_tbl(i).maximum_amount := rates_info_rec.maximum_amount;
336 l_rates_info_tbl(i).minimum_amount := rates_info_rec.minimum_amount;
337
338
339 END LOOP;
340
341 IF i = 0 THEN
342 null; --error
343 END IF;
344
345 IF l_amount < l_rates_info_tbl(1).minimum_amount THEN
346 l_amount := l_rates_info_tbl(1).minimum_amount;
347 ELSIF l_amount >= l_rates_info_tbl(i).maximum_amount THEN
348 l_amount := l_rates_info_tbl(i).maximum_amount;
349 l_max_exceeded := TRUE ;
350 l_max_rate := l_rates_info_tbl(i).comm_rate;
351 l_max_tier_range := l_rates_info_tbl(i).maximum_amount - l_rates_info_tbl(i).minimum_amount ;
352 END IF;
353
354 l_rate_factor := 0;
355 FOR j IN l_rates_info_tbl.FIRST .. l_rates_info_tbl.LAST LOOP
356 IF p_split_flag = 'N' THEN
357 IF j = l_rates_info_tbl.LAST THEN
358 IF l_amount >= l_rates_info_tbl(j).minimum_amount
359 AND l_amount <= l_rates_info_tbl(j).maximum_amount THEN
360 l_rate := l_rates_info_tbl(j).comm_rate;
361 EXIT;
362 END IF;
363 ELSE
364 IF l_amount >= l_rates_info_tbl(j).minimum_amount
365 AND l_amount < l_rates_info_tbl(j).maximum_amount THEN
366 l_rate := l_rates_info_tbl(j).comm_rate;
367 EXIT;
368 END IF;
369 END IF;
370 ELSIF p_split_flag = 'P' THEN
371 IF l_amount >= l_rates_info_tbl(j).maximum_amount THEN
372 l_rate_factor := l_rate_factor + l_rates_info_tbl(j).comm_rate;
373
374 ELSE
375 l_rate_factor := l_rate_factor + (l_amount - l_rates_info_tbl(j).minimum_amount)*l_rates_info_tbl(j).comm_rate
376 / (l_rates_info_tbl(j).maximum_amount - l_rates_info_tbl(j).minimum_amount) ;
377 EXIT;
378 END IF;
379 ELSIF p_split_flag = 'Y' THEN
380 -- Added to fix bug when amount=0, BUG#3179883
381 IF l_amount = 0 THEN
382 l_rate_factor := l_rates_info_tbl(j).comm_rate ;
383 EXIT ;
384 ELSIF l_amount >= l_rates_info_tbl(j).maximum_amount THEN
385 l_rate_factor := l_rate_factor + (l_rates_info_tbl(j).maximum_amount - l_rates_info_tbl(j).minimum_amount)
389 l_rate_factor := l_rate_factor + (l_amount - l_rates_info_tbl(j).minimum_amount)
386 * l_rates_info_tbl(j).comm_rate;
387
388 ELSE
390 *l_rates_info_tbl(j).comm_rate;
391 EXIT;
392 END IF;
393
394 ELSE
395 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
396 END IF;
397 END LOOP;
398
399 -- deal with the exceeding amount
400 IF l_max_exceeded THEN
401 IF p_split_flag = 'Y' THEN
402 l_rate_factor := l_rate_factor + (p_amount - l_amount)*l_max_rate ;
403 l_amount := p_amount ;
404 ELSIF p_split_flag = 'P' THEN
405 l_rate_factor := l_rate_factor + (p_amount - l_amount)*l_max_rate/l_max_tier_range ;
406 END IF ;
407 END IF ;
408
409 -- return the rate required.
410 IF p_split_flag = 'N' THEN
411 x_rate := l_rate;
412 ELSIF p_split_flag = 'Y' THEN
413 IF l_amount = 0 THEN
414 x_rate := l_rate_factor;
415 ELSE
416 x_rate := l_rate_factor/l_amount;
417 END IF;
418 ELSIF p_split_flag = 'P' THEN
419 IF l_amount = 0 THEN
420 x_rate := 0;
421 ELSE
422 x_rate := l_rate_factor;
423 END IF;
424 END IF;
425
426
427
428 EXCEPTION WHEN OTHERS THEN
429 cn_message_pkg.debug('Exception in get_rates ' || Sqlerrm);
430 RAISE;
431
432 END;
433
434 END CN_SFP_FORMULA_CMN_PKG;