[Home] [Help]
PACKAGE BODY: APPS.CN_COMMISSION_CALC_PVT
Source
1 PACKAGE BODY CN_COMMISSION_CALC_PVT AS
2 -- $Header: cnvprcmb.pls 120.6 2005/11/21 22:56:29 raramasa noship $
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_COMMISSION_CALC_PVT';
4
5 g_cached_org_id NUMBER :=0;
6 g_cached_org_append VARCHAR2(100);
7
8
9 PROCEDURE get_Projected_Commission
10 (
11 p_srp_plan_assign_id IN NUMBER,
12 p_salesrep_id IN NUMBER,
13 p_start_period_id IN NUMBER,
14 p_end_period_id IN NUMBER,
15 p_quota_id IN NUMBER,
16 p_quota_name IN VARCHAR,
17 p_sales_credit_amt IN NUMBER,
18 x_proj_comm OUT NOCOPY NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2
22 )
23 IS
24 l_api_name CONSTANT VARCHAR2(30) := 'get_Projected_Commission';
25 l_api_version CONSTANT NUMBER := 1.0;
26
27 CURSOR pe_formula_cr IS
28 SELECT ccf.name formula_name
29 , ccf.calc_formula_id formula_id
30 , ccf.formula_status formula_status
31 , ccf.org_id org_id
32 FROM cn_quotas cq
33 , cn_calc_formulas ccf
34 WHERE cq.quota_id = p_quota_id
35 AND ccf.calc_formula_id = cq.calc_formula_id;
36
37 CURSOR formula_valid_cr(p_formula_id NUMBER, p_org_id NUMBER) IS
38 SELECT count(*)
39 FROM user_objects
40 WHERE object_name = 'CN_FORMULA_'||abs(p_formula_id)||'_'||abs(p_org_id)||'_PKG'
41 AND (object_type = 'PACKAGE' OR object_type = 'PACKAGE BODY');
42
43 l_pe_formula pe_formula_cr%ROWTYPE;
44 l_formula_count NUMBER := 0;
45
46 BEGIN
47
48 SAVEPOINT get_Projected_Commission;
49 x_return_status := FND_API.G_RET_STS_SUCCESS;
50
51 x_proj_comm := 0;
52
53 OPEN pe_formula_cr;
54 FETCH pe_formula_cr into l_pe_formula;
55 IF pe_formula_cr%NOTFOUND THEN
56 fnd_message.set_name('CN', 'CN_NO_QUOTA_FORMULA');
57 fnd_message.set_token('QUOTA_NAME', p_quota_name);
58 fnd_msg_pub.add;
59 CLOSE pe_formula_cr;
60 RAISE FND_API.G_EXC_ERROR;
61 ELSIF l_pe_formula.formula_status <> 'COMPLETE' THEN
62 fnd_message.set_name('CN', 'CN_INV_CALC_FORMULA');
63 fnd_message.set_token('QUOTA_NAME', p_quota_name);
64 fnd_msg_pub.add;
65 CLOSE pe_formula_cr;
66 RAISE FND_API.G_EXC_ERROR;
67 END IF;
68 CLOSE pe_formula_cr;
69
70 OPEN formula_valid_cr(l_pe_formula.formula_id,l_pe_formula.org_id);
71 FETCH formula_valid_cr INTO l_formula_count;
72 CLOSE formula_valid_cr;
73
74 IF l_formula_count <> 2 THEN
75 fnd_message.set_name('CN', 'CN_FORMULA_PKG_NOT_VALID');
76 fnd_message.set_token('FORMULA_NAME', l_pe_formula.formula_name);
77 fnd_msg_pub.add;
78 CLOSE pe_formula_cr;
79 RAISE FND_API.G_EXC_ERROR;
80 END IF;
81
82 EXECUTE IMMEDIATE
83 'BEGIN CN_FORMULA_'||abs(l_pe_formula.formula_id)||'_'||abs(l_pe_formula.org_id)||'_PKG.get_forecast_commission(:1,:2,:3,:4,:5,:6,:7);END;'
84 USING p_srp_plan_assign_id, p_salesrep_id, p_start_period_id,p_end_period_id,p_quota_id,p_sales_credit_amt,out x_proj_comm;
85
86 EXCEPTION
87 WHEN FND_API.G_EXC_ERROR THEN
88 x_return_status := FND_API.G_RET_STS_ERROR;
89 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
90 p_data => x_msg_data,
91 p_encoded => FND_API.G_FALSE );
92
93 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
94 x_proj_comm := 0;
95 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
96 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
97 p_data => x_msg_data,
98 p_encoded => FND_API.G_FALSE );
99
100 WHEN OTHERS THEN
101 x_proj_comm := 0;
102 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
103 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
104 p_data => x_msg_data,
105 p_encoded => FND_API.G_FALSE );
106
107 End get_Projected_Commission;
108
109 Procedure processRows(p_proj_comp_rec IN cn_proj_compensation_gtt%rowtype,
110 x_return_status OUT NOCOPY VARCHAR2,
111 x_msg_count OUT NOCOPY NUMBER,
112 x_msg_data OUT NOCOPY VARCHAR2
113 ) IS
114 CURSOR srpplanassign_cr(p_salesrep_id NUMBER,p_date DATE,p_quota_id NUMBER) IS
115 SELECT cspa.srp_plan_assign_id srp_plan_assign_id,
116 cspa.salesrep_id salesrep_id,
117 cspa.comp_plan_id comp_plan_id,
118 cspa.start_date start_date,
119 nvl(cspa.end_date,p_date) end_date
120 FROM cn_srp_plan_assigns cspa,
121 cn_srp_quota_assigns csqa
122 WHERE cspa.salesrep_id = p_salesrep_id
123 AND p_date BETWEEN cspa.start_date AND nvl(cspa.end_date,p_date)
124 AND csqa.quota_id = p_quota_id
125 AND csqa.srp_plan_assign_id = cspa.srp_plan_assign_id;
126
127 CURSOR compplan_cr(p_comp_plan_id NUMBER) IS
128 SELECT count(*) valid_compplan_cnt
129 FROM cn_comp_plans
130 WHERE comp_plan_id = p_comp_plan_id
131 AND status_code = 'INCOMPLETE';
132
133 CURSOR quotadetails_cr(p_salesrep_id NUMBER, p_revenueclass_id NUMBER, p_date DATE) IS
134 SELECT
135 sqa.quota_id
136 , q.interval_type_id interval_type_id
137 , q.credit_type_id credit_type_id
138 , cit.name interval_type_name
139 , cci.name credit_type_name
140 , q.name quota_name
141 , qr.revenue_class_id
142 from cn_srp_plan_assigns spa,
143 cn_srp_quota_assigns sqa,
144 cn_quotas q,
145 cn_quota_rules qr,
146 cn_dim_hierarchies dh,
147 jtf_rs_salesreps jrs,
148 jtf_rs_group_members mem,
149 jtf_rs_role_relations rr,
150 cn_interval_types cit,
151 cn_credit_types cci,
152 cn_repositories cr
153 where spa.salesrep_id = p_salesrep_id
154 and spa.start_date <= p_date
155 and nvl(spa.end_date, p_date) >= p_date
156 and jrs.salesrep_id = p_salesrep_id
157 and nvl(jrs.org_id, -9999) = nvl(spa.org_id, -9999)
158 and mem.resource_id = jrs.resource_id
159 and nvl(mem.delete_flag, 'N') <> 'Y'
160 and rr.role_id = spa.role_id
161 and rr.role_resource_id = mem.group_member_id
162 and rr.role_resource_type = 'RS_GROUP_MEMBER'
163 and nvl(rr.delete_flag, 'N') <> 'Y'
164 and exists (select 1 from cn_comp_plans where status_code = 'COMPLETE' AND comp_plan_id = spa.comp_plan_id)
165 and rr.start_date_active <= p_date
166 and nvl(rr.end_date_active, p_date) >= p_date
167 and rr.start_date_active <= nvl(spa.end_date, p_date)
168 and nvl(rr.end_date_active, nvl(spa.end_date, p_date)) >= spa.start_date
169 and sqa.srp_plan_assign_id = spa.srp_plan_assign_id
170 and q.quota_id = sqa.quota_id
171 and q.start_date <= p_date
172 and nvl(q.end_date, p_date) >= p_date
173 and qr.quota_id = sqa.quota_id
174 and dh.header_dim_hierarchy_id = cr.rev_class_hierarchy_id
175 and dh.start_date <= least(nvl(spa.end_date, p_date), nvl(q.end_date, p_date))
176 and nvl(dh.end_date, p_date) >= greatest(spa.start_date, q.start_date)
177 and exists (select 1 from cn_dim_explosion de
178 where de.dim_hierarchy_id = dh.dim_hierarchy_id
179 and de.ancestor_external_id = qr.revenue_class_id
180 and de.value_external_id = p_revenueclass_id)
181 AND cit.interval_type_id = q.interval_type_id
182 AND cci.credit_type_id = q.credit_type_id
183 order by greatest(dh.start_date, spa.start_date, q.start_date, rr.start_date_active, p_date),
184 least(nvl(dh.end_date, p_date),
185 nvl(spa.end_date, p_date),
186 nvl(q.end_date, p_date),
187 nvl(rr.end_date_active, p_date), p_date);
188
189 CURSOR periodquotas_cr(p_srp_plan_assign_id NUMBER, p_salesrep_id NUMBER, p_period_id NUMBER, p_quota_id NUMBER) IS
190 SELECT nvl(cspq.input_achieved_itd,0) input_achieved_itd
191 , nvl(cspq.itd_TARGET,0) target_itd
192 , nvl(cspq.target_amount,0) target_amount
193 FROM cn_srp_period_quotas cspq
194 WHERE cspq.srp_plan_assign_id = p_srp_plan_assign_id
195 AND cspq.salesrep_id = p_salesrep_id
196 AND cspq.quota_id = p_quota_id
197 AND cspq.period_id = p_period_id;
198
199 l_quotadetails quotadetails_cr%ROWTYPE;
200 l_srp_plan_assign srpplanassign_cr%ROWTYPE;
201 l_periodquotas periodquotas_cr%ROWTYPE;
202
203 l_stmt VARCHAR2(1000):='';
204 l_from_currency VARCHAR2(80) := '';
205
206 l_valid_forecast NUMBER := 0;
207 l_valid_compplan NUMBER := 0;
208 l_proj_comm_amt NUMBER := 0;
209 l_tot_inp_ach_itd NUMBER := 0;
210 l_tot_target_itd NUMBER := 0;
211 l_tot_target_amt NUMBER := 0;
212 l_quota_achievement NUMBER := 0;
213 l_revenueclass_id NUMBER := 0;
214 l_tot_proj_comm_amt NUMBER:=0;
215
216 l_return_status varchar2(1);
217 l_msg_count number;
218 l_msg_data varchar2(2000);
219
220 BEGIN
221
222 x_return_status := FND_API.G_RET_STS_SUCCESS;
223
224 l_tot_target_amt:=0;
225 l_tot_inp_ach_itd := 0;
226 l_tot_target_itd := 0;
227 l_tot_proj_comm_amt :=0;
228 l_proj_comm_amt := 0;
229 l_revenueclass_id := 0;
230
231 l_stmt := 'BEGIN ' || ':rev_class_id := ' ||'cn_clsfn_' || To_char(p_proj_comp_rec.ruleset_id) || g_cached_org_append || '.classify_' || To_char(p_proj_comp_rec.ruleset_id) ||'( :p_line_no);' || 'END;';
232
233 EXECUTE IMMEDIATE l_stmt USING OUT l_revenueclass_id, p_proj_comp_rec.line_id;
234
235 OPEN quotadetails_cr(p_proj_comp_rec.salesrep_id,l_revenueclass_id,p_proj_comp_rec.calc_date);
236 FETCH quotadetails_cr INTO l_quotadetails;
237 IF quotadetails_cr%NOTFOUND THEN
238 fnd_message.set_name('CN', 'CN_QUOTA_NOT_MAPPED');
239 fnd_message.set_token('LINE_NO', p_proj_comp_rec.line_id);
240 fnd_msg_pub.add;
241 CLOSE quotadetails_cr;
242 RAISE FND_API.G_EXC_ERROR;
243 END IF;
244 CLOSE quotadetails_cr;
245
246 FOR l_srp_plan_assign IN srpplanassign_cr(p_proj_comp_rec.salesrep_id,p_proj_comp_rec.calc_date,l_quotadetails.quota_id) LOOP
247
248 l_proj_comm_amt :=0;
249 l_valid_forecast := 1;
250 OPEN compplan_cr(l_srp_plan_assign.comp_plan_id);
251 FETCH compplan_cr INTO l_valid_compplan;
252 IF l_valid_compplan > 0 THEN
253 fnd_message.set_name('CN', 'CN_PR_CP_NOT_VALID');
254 fnd_message.set_token('LINE_NO', p_proj_comp_rec.line_id);
255 fnd_msg_pub.add;
256 CLOSE compplan_cr;
257 RAISE FND_API.G_EXC_ERROR;
258 END IF;
259 CLOSE compplan_cr;
260
261 get_Projected_Commission( p_srp_plan_assign_id => l_srp_plan_assign.srp_plan_assign_id,
262 p_salesrep_id => l_srp_plan_assign.salesrep_id,
263 p_start_period_id => p_proj_comp_rec.period_id,
264 p_end_period_id => p_proj_comp_rec.period_id,
265 p_quota_id => l_quotadetails.quota_id,
266 p_quota_name => l_quotadetails.quota_name,
267 p_sales_credit_amt => p_proj_comp_rec.sales_credit_amount,
268 x_proj_comm => l_proj_comm_amt,
269 x_return_status => l_return_status,
270 x_msg_count => l_msg_count,
271 x_msg_data => l_msg_data);
272
273 IF l_return_status <> 'S' THEN
274 x_return_status := l_return_status;
275 x_msg_count := l_msg_count;
276 x_msg_data := l_msg_data;
277 RAISE FND_API.G_EXC_ERROR;
278 END IF;
279
280 OPEN periodquotas_cr(l_srp_plan_assign.srp_plan_assign_id, l_srp_plan_assign.salesrep_id,p_proj_comp_rec.period_id,l_quotadetails.quota_id);
281 FETCH periodquotas_cr INTO l_periodquotas;
282 IF periodquotas_cr%NOTFOUND THEN
283 l_periodquotas.input_achieved_itd := 0;
284 l_periodquotas.target_itd :=0;
285 l_periodquotas.target_amount :=0;
286 END IF;
287 CLOSE periodquotas_cr;
288
289 l_tot_inp_ach_itd := l_tot_inp_ach_itd + l_periodquotas.input_achieved_itd;
290 l_tot_target_itd := l_tot_target_itd + l_periodquotas.target_itd;
291 l_tot_target_amt := l_tot_target_amt + l_periodquotas.target_amount;
292 l_tot_proj_comm_amt := l_tot_proj_comm_amt + NVL(l_proj_comm_amt,0) ;
293
294 END LOOP;
295
296 IF l_valid_forecast = 0 THEN
297 fnd_message.set_name('CN', 'CN_PR_SRPPLAN_NOT_FOUND');
298 fnd_message.set_token('LINE_NO', p_proj_comp_rec.line_id);
299 fnd_msg_pub.add;
300 RAISE FND_API.G_EXC_ERROR;
301 END IF;
302
303 l_from_currency := cn_general_utils.get_currency_code;
304
305 BEGIN
306 l_tot_proj_comm_amt := cn_api.convert_to_repcurr(l_tot_proj_comm_amt,
307 p_proj_comp_rec.calc_date,
308 CN_SYSTEM_PARAMETERS.value('CN_CONVERSION_TYPE',g_cached_org_id),
309 l_quotadetails.credit_type_id,
310 l_from_currency,
311 p_proj_comp_rec.currency_code,
312 g_cached_org_id);
313 l_tot_target_amt := cn_api.convert_to_repcurr(l_tot_target_amt,
314 p_proj_comp_rec.calc_date,
315 CN_SYSTEM_PARAMETERS.value('CN_CONVERSION_TYPE',g_cached_org_id),
316 l_quotadetails.credit_type_id,
317 l_from_currency,
318 p_proj_comp_rec.currency_code,
319 g_cached_org_id);
320 EXCEPTION
321 WHEN OTHERS THEN
322 fnd_message.set_name('CN','CN_CONV_CURR_FAIL');
323 fnd_msg_pub.ADD;
324 RAISE fnd_api.g_exc_error;
325 END;
326
327 IF l_tot_target_itd <> 0 THEN
328 l_quota_achievement := (l_tot_inp_ach_itd + l_tot_proj_comm_amt)/ l_tot_target_itd * 100;
329 ELSE
330 l_quota_achievement := 0;
331 END IF;
332
333 UPDATE cn_proj_compensation_gtt
334 SET PE_NAME = l_quotadetails.quota_name,
335 PROJ_COMP = l_tot_proj_comm_amt,
336 PE_QUOTA = l_tot_target_amt,
337 PE_ACHIEVED = l_quota_achievement,
338 PE_CREDIT = l_tot_inp_ach_itd+l_tot_proj_comm_amt,
339 PE_INTERVAL = l_quotadetails.interval_type_id,
340 CALC_STATUS = l_return_status
341 WHERE LINE_ID = p_proj_comp_rec.line_id;
342
343 EXCEPTION
344 WHEN FND_API.G_EXC_ERROR THEN
345 x_return_status := FND_API.G_RET_STS_ERROR;
346 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
347 p_data => x_msg_data,
348 p_encoded => FND_API.G_FALSE );
349
350 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
352 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
353 p_data => x_msg_data,
354 p_encoded => FND_API.G_FALSE );
355
356 WHEN OTHERS THEN
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
358 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
359 p_data => x_msg_data,
360 p_encoded => FND_API.G_FALSE );
361
362 END processRows;
363
364 Procedure calculate_Commission
365 (
366 p_api_version IN NUMBER,
367 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
368 x_inc_plnr_disclaimer OUT NOCOPY cn_repositories.income_planner_disclaimer%TYPE,
369 x_return_status OUT NOCOPY VARCHAR2,
370 x_msg_count OUT NOCOPY NUMBER,
371 x_msg_data OUT NOCOPY VARCHAR2
372 ) IS
373 l_api_name CONSTANT VARCHAR2(30) := 'calculate_Commission';
374 l_api_version CONSTANT NUMBER := 1.0;
375 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-RRRR');
376
377 BEGIN
378 null;
379 End calculate_Commission;
380
381 Procedure calculate_Commission
382 (
383 p_api_version IN NUMBER,
384 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
385 p_org_id IN NUMBER,
386 x_inc_plnr_disclaimer OUT NOCOPY cn_repositories.income_planner_disclaimer%TYPE,
387 x_return_status OUT NOCOPY VARCHAR2,
388 x_msg_count OUT NOCOPY NUMBER,
389 x_msg_data OUT NOCOPY VARCHAR2
390 ) IS
391 l_api_name CONSTANT VARCHAR2(30) := 'calculate_Commission';
392 l_api_version CONSTANT NUMBER := 1.0;
393 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-RRRR');
394
395 l_return_status varchar2(1);
396 l_msg_count number;
397 l_msg_data varchar2(2000);
398
399 l_inc_plnr_profile VARCHAR2(10) := '';
400
401 CURSOR repositories_cr IS
402 SELECT cpt.period_type period_type
403 , cr.set_of_books_id set_of_books_id
404 , cr.period_set_id period_set_id
405 , cr.period_type_id period_type_id
406 , cr.income_planner_disclaimer income_planner_disclaimer
407 FROM cn_period_types cpt,cn_repositories cr
408 WHERE cpt.period_type_id = cr.period_type_id
409 AND cpt.org_id = cr.org_id
410 AND cr.org_id = p_org_id;
411
412 CURSOR proj_comp_cr IS
413 SELECT *
414 FROM
415 cn_proj_compensation_gtt
416 WHERE salesrep_id IS NOT NULL
417 AND period_id IS NOT NULL
418 AND ruleset_id IS NOT NULL;
419
420 CURSOR proj_comp_srp_cr IS
421 SELECT line_id
422 FROM cn_proj_compensation_gtt
423 WHERE salesrep_id IS NULL;
424
425 CURSOR proj_comp_prd_cr IS
426 SELECT line_id
427 FROM cn_proj_compensation_gtt
428 WHERE period_id IS NULL;
429
430 CURSOR proj_comp_rs_cr IS
431 SELECT line_id
432 FROM cn_proj_compensation_gtt
433 WHERE ruleset_id IS NULL;
434
435 l_projcomp proj_comp_cr%ROWTYPE;
436 l_repositories repositories_cr%ROWTYPE;
437
438 TYPE l_salesrep_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
439 TYPE l_period_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
440 TYPE l_ruleset_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
441
442 l_salesrep_tbl dbms_utility.uncl_array;
443 l_period_tbl dbms_utility.uncl_array;
444 l_ruleset_tbl dbms_utility.uncl_array;
445
446 linenos VARCHAR2(1000):='';
447 tablength BINARY_INTEGER := 0;
448
449 BEGIN
450
451 -- Standard Start of API savepoint
452 SAVEPOINT calculate_Commission;
453 x_return_status := FND_API.G_RET_STS_SUCCESS;
454
455 --Standard Call to check for call compatibility
456 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,l_api_name,G_PKG_NAME) THEN
457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
458 END IF;
459
460 -- Initialize message list if p_init_msg_list is set to TRUE
461 IF FND_API.to_Boolean(p_init_msg_list) THEN
462 FND_MSG_PUB.initialize;
463 END IF;
464
465 MO_GLOBAL.SET_POLICY_CONTEXT ('S',p_org_id);
466 g_cached_org_id := p_org_id;
467
468 x_inc_plnr_disclaimer := '';
469
470 IF g_cached_org_id = -99 THEN
471 g_cached_org_append := '_MINUS99';
472 ELSE
473 g_cached_org_append := '_' || g_cached_org_id;
474 END IF;
475
476 OPEN repositories_cr;
477 FETCH repositories_cr INTO l_repositories;
478 CLOSE repositories_cr;
479
480 UPDATE cn_proj_compensation_gtt cpcg
481 SET line_id = cn_proj_compensation_gtt_s.NEXTVAL
482 , cpcg.period_id =
483 (
484 SELECT cps.period_id period_id
485 FROM cn_period_statuses cps
486 WHERE cps.period_set_id = l_repositories.period_set_id
487 AND cps.period_type_id = l_repositories.period_type_id
488 AND cpcg.calc_date between cps.start_date and cps.end_Date
489 AND cps.period_status = 'O'
490 AND cps.org_id = g_cached_org_id
491 )
492 , cpcg.salesrep_id = (
493 SELECT salesrep_id
494 FROM cn_salesreps cs
495 WHERE cs.resource_id = cpcg.resource_id
496 AND cs.org_id = g_cached_org_id
497 )
498 , cpcg.ruleset_id = (
499 SELECT ruleset_id
500 FROM cn_rulesets cr
501 WHERE cpcg.calc_date BETWEEN cr.start_date AND nvl(cr.end_date,cpcg.calc_date)
502 AND cr.module_type = 'PECLS'
503 and cr.org_id = g_cached_org_id
504 )
505 , cpcg.pe_name = FND_API.G_MISS_CHAR
506 , cpcg.proj_comp = 0
507 , cpcg.pe_quota = 0
508 , cpcg.pe_achieved = 0
509 , cpcg.pe_credit = 0
510 , cpcg.pe_interval = FND_API.G_MISS_NUM
511 , cpcg.calc_status = FND_API.G_RET_STS_ERROR;
512
513 OPEN proj_comp_srp_cr;
514 FETCH proj_comp_srp_cr BULK COLLECT INTO l_salesrep_tbl;
515
516 IF l_salesrep_tbl.count > 0 THEN
517 dbms_utility.table_to_comma(l_salesrep_tbl,tablength,linenos);
518 x_return_status := FND_API.G_RET_STS_ERROR;
519 fnd_message.set_name('CN','CN_INVALID_RES_ID');
520 fnd_message.set_token('LINE_NO', linenos);
521 fnd_msg_pub.ADD;
522 END IF;
523
524 CLOSE proj_comp_srp_cr;
525
526 OPEN proj_comp_prd_cr;
527 FETCH proj_comp_prd_cr BULK COLLECT INTO l_period_tbl;
528
529 IF l_period_tbl.count > 0 THEN
530 dbms_utility.table_to_comma(l_period_tbl,tablength,linenos);
531 x_return_status := FND_API.G_RET_STS_ERROR;
532 fnd_message.set_name('CN','CN_INVALID_PRD_ID');
533 fnd_message.set_token('LINE_NO', linenos);
534 fnd_msg_pub.ADD;
535 END IF;
536
537 CLOSE proj_comp_prd_cr;
538
539 OPEN proj_comp_rs_cr;
540 FETCH proj_comp_rs_cr BULK COLLECT INTO l_ruleset_tbl;
541
542 IF l_ruleset_tbl.count > 0 THEN
543 dbms_utility.table_to_comma(l_ruleset_tbl,tablength,linenos);
544 x_return_status := FND_API.G_RET_STS_ERROR;
545 fnd_message.set_name('CN','CN_INVALID_RS_ID');
546 fnd_message.set_token('LINE_NO', linenos);
547 fnd_msg_pub.ADD;
548 END IF;
549
550 CLOSE proj_comp_rs_cr;
551
552 FOR proj_comp_rec IN proj_comp_cr LOOP
553 processRows(p_proj_comp_rec => proj_comp_rec,
554 x_return_status => l_return_status,
555 x_msg_count => l_msg_count,
556 x_msg_data => l_msg_data);
557
558 IF l_return_status <> 'S' THEN
559 x_return_status := l_return_status;
560 x_msg_count := l_msg_count;
561 x_msg_data := l_msg_data;
562 END IF;
563 END LOOP;
564
565 --FND_PROFILE.GET('CN_CUST_DISCLAIMER',l_inc_plnr_profile);
566 CN_SYSTEM_PARAMETERS.GET_SYSTEM_PARAMETER(P_PROFILE_CODE => 'CN_CUST_DISCLAIMER',P_ORG_ID => g_cached_org_id,X_VALUE => l_inc_plnr_profile);
567
568 IF NVL(l_inc_plnr_profile,'N') = 'N' THEN
569 x_inc_plnr_disclaimer := FND_API.G_MISS_CHAR; -- temporarily hardcoded for testing purpose. need to add a new message
570 ELSE
571 x_inc_plnr_disclaimer := l_repositories.income_planner_disclaimer ;
572 END IF;
573
574 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
575 p_data => x_msg_data,
576 p_encoded => FND_API.G_FALSE );
577
578
579 EXCEPTION
580 WHEN FND_API.G_EXC_ERROR THEN
581 x_return_status := FND_API.G_RET_STS_ERROR;
582 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
583 p_data => x_msg_data,
584 p_encoded => FND_API.G_FALSE );
585
586
587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
589 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
590 p_data => x_msg_data,
591 p_encoded => FND_API.G_FALSE );
592
593
594 WHEN OTHERS THEN
595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
596 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
597 p_data => x_msg_data,
598 p_encoded => FND_API.G_FALSE );
599
600 End calculate_Commission;
601
602
603 End CN_COMMISSION_CALC_PVT;