[Home] [Help]
PACKAGE BODY: APPS.CN_PRD_QUOTA_PUB
Source
1 PACKAGE BODY CN_PRD_QUOTA_PUB AS
2 /*$Header: cnvpedbb.pls 120.2.12000000.2 2007/10/11 02:55:12 rnagired ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CN_PRD_QUOTA_PUB';
5
6 PROCEDURE distribute_target (
7 p_api_version IN NUMBER,
8 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
9 p_commit IN VARCHAR2 := fnd_api.g_false,
10 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
11 p_quota_id IN NUMBER,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_count OUT NOCOPY NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2
15 )
16 IS
17 l_api_name CONSTANT VARCHAR2 (30) := 'Distribute_Target';
18 l_api_version CONSTANT NUMBER := 1.0;
19
20 CURSOR period_quotas (
21 l_interval_number NUMBER,
22 l_period_year NUMBER
23 )
24 IS
25 SELECT spq.period_quota_id,
26 spq.period_id,
27 spq.quota_id,
28 spq.quarter_num,
29 spq.period_year
30 FROM cn_period_quotas spq,
31 cn_acc_period_statuses_v cp,
32 cn_cal_per_int_types cpit,
33 cn_quotas cq
34 WHERE spq.quota_id = p_quota_id
35 AND spq.quota_id = cq.quota_id
36 AND spq.period_id = cp.period_id
37 AND cp.period_id = cpit.cal_period_id
38 AND cpit.interval_type_id = cq.interval_type_id
39 AND cpit.interval_number = l_interval_number
40 AND spq.period_year = l_period_year
41 ORDER BY spq.period_id;
42
43 pq_rec period_quotas%ROWTYPE;
44
45 -- Get the period quotas that belong to the quota assignment for each
46 -- interval
47 CURSOR interval_counts
48 IS
49 SELECT COUNT (spq.period_quota_id) interval_count,
50 cpit.interval_number interval_number,
51 spq.period_year period_year
52 FROM cn_period_quotas spq,
53 cn_acc_period_statuses_v cp,
54 cn_cal_per_int_types cpit,
55 cn_quotas cq
56 WHERE spq.quota_id = p_quota_id
57 AND spq.quota_id = cq.quota_id
58 AND spq.period_id = cp.period_id
59 AND cp.period_id = cpit.cal_period_id
60 AND cpit.interval_type_id = cq.interval_type_id
61 GROUP BY cpit.interval_number,
62 spq.period_year;
63
64 interval_rec interval_counts%ROWTYPE;
65 l_period_count NUMBER;
66 l_running_total_target NUMBER;
67 l_total_periods NUMBER;
68 l_period_target NUMBER;
69 l_running_total_payment NUMBER;
70 l_period_payment NUMBER;
71 l_running_performance_goal NUMBER;
72 l_performance_goal NUMBER;
73 l_srp_quota_assign_id NUMBER (15);
74 l_quota_target NUMBER;
75 l_quota_payment NUMBER;
76 l_quota_performance_goal NUMBER;
77 l_dist_rule_code VARCHAR2 (30);
78 l_period_type_code VARCHAR2 (30);
79 l_period_performance_goal NUMBER;
80 BEGIN
81 -- Standard Start of API savepoint
82 SAVEPOINT get_prd_quota_year;
83
84 -- Standard call to check for call compatibility.
85 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
86 THEN
87 RAISE fnd_api.g_exc_unexpected_error;
88 END IF;
89
90 -- Initialize message list if p_init_msg_list is set to TRUE.
91 IF fnd_api.to_boolean (p_init_msg_list)
92 THEN
93 fnd_msg_pub.initialize;
94 END IF;
95
96 -- Initialize API return status to success
97 x_return_status := fnd_api.g_ret_sts_success;
98
99 -- API body
100 SELECT NVL (q.target, 0),
101 NVL (q.payment_amount, 0),
102 NVL (q.performance_goal, 0)
103 --,qa.period_target_dist_rule_code
104 ,
105 cn_chk_plan_element_pkg.get_interval_name (q.interval_type_id,q.org_id) period_type_code
106 INTO l_quota_target,
107 l_quota_payment,
108 l_performance_goal
109 --,l_dist_rule_code
110 ,
111 l_period_type_code
112 FROM cn_quotas q
113 WHERE q.quota_id = p_quota_id
114 --AND qa.period_target_dist_rule_code <> 'USER_DEFINED'
115 ;
116
117 -- Currently this is the only distribution rule we support
118 FOR interval_rec IN interval_counts
119 LOOP
120 -- Initialize for each interval
121 l_period_count := 0;
122 l_running_total_target := 0;
123 l_period_target := 0;
124 l_running_total_payment := 0;
125 l_period_payment := 0;
126 l_running_performance_goal := 0;
127 l_period_performance_goal := 0;
128
129 -- Now that we know the counts per quarter/year we can divide the
130 -- quota target correctly for each quarter and set the period quota
131 -- target.
132 FOR pq_rec IN period_quotas (l_interval_number => interval_rec.interval_number, l_period_year => interval_rec.period_year)
133 LOOP
134 l_period_count := l_period_count + 1;
135 l_period_target := ((l_quota_target * (l_period_count / interval_rec.interval_count)) - l_running_total_target);
136 l_running_total_target := l_running_total_target + l_period_target;
137 l_period_payment := ((l_quota_payment * (l_period_count / interval_rec.interval_count)) - l_running_total_payment);
138 l_running_total_payment := l_running_total_payment + l_period_payment;
139 l_period_performance_goal := ((l_performance_goal * (l_period_count / interval_rec.interval_count)) - l_running_performance_goal);
140 l_running_performance_goal := l_running_performance_goal + l_period_performance_goal;
141 /* UPDATE cn_srp_period_quotas
142 SET
143 target_amount = round(nvl(l_period_target, 0), g_ext_precision),
144 itd_target = round(nvl(l_running_total_target,0), g_ext_precision),
145 period_payment = round(nvl(l_period_payment,0), g_ext_precision),
146 itd_payment = round(nvl(l_running_total_payment,0), g_ext_precision),
147 performance_goal_ptd = round(nvl(l_period_performance_goal,0), g_ext_precision),
148 performance_goal_itd = round(nvl(l_running_performance_goal,0),g_ext_precision)
149 WHERE srp_period_quota_id = pq_rec.period_quota_id
150 ;*/
151 cn_period_quotas_pkg.begin_record (x_operation => 'UPDATE',
152 x_period_quota_id => pq_rec.period_quota_id,
153 x_period_id => pq_rec.period_id,
154 x_quota_id => pq_rec.quota_id,
155 x_period_target => NVL (l_period_target, 0),
156 x_itd_target => NVL (l_running_total_target, 0),
157 x_period_payment => NVL (l_period_payment, 0),
158 x_itd_payment => NVL (l_running_total_payment, 0),
159 x_quarter_num => pq_rec.quarter_num,
160 x_period_year => pq_rec.period_year,
161 x_creation_date => SYSDATE,
162 x_last_update_date => SYSDATE,
163 x_last_update_login => fnd_global.login_id,
164 x_last_updated_by => fnd_global.user_id,
165 x_created_by => fnd_global.user_id,
166 x_period_type_code => 'PERIOD',
167 x_performance_goal => NVL (l_period_performance_goal, 0)
168 );
169 END LOOP;
170 END LOOP;
171
172 -- End of API body.
173 -- Standard check of p_commit.
174 IF fnd_api.to_boolean (p_commit)
175 THEN
176 COMMIT WORK;
177 END IF;
178
179 -- Standard call to get message count and if count is 1, get message info.
180 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
181 EXCEPTION
182 WHEN fnd_api.g_exc_error
183 THEN
184 ROLLBACK TO get_prd_quota_year;
185 x_return_status := fnd_api.g_ret_sts_error;
186 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
187 WHEN fnd_api.g_exc_unexpected_error
188 THEN
189 ROLLBACK TO get_prd_quota_year;
190 x_return_status := fnd_api.g_ret_sts_unexp_error;
191 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
192 WHEN OTHERS
193 THEN
194 ROLLBACK TO get_prd_quota_year;
195 x_return_status := fnd_api.g_ret_sts_unexp_error;
196
197 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
198 THEN
199 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
200 END IF;
201
202 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
203 END distribute_target;
204
205
206 PROCEDURE Distribute_Prd_Quota
207 ( p_api_version IN NUMBER := CN_API.G_MISS_NUM,
208 p_init_msg_list IN VARCHAR2 := CN_API.G_FALSE,
209 p_commit IN VARCHAR2 := CN_API.G_FALSE,
210 p_validation_level IN NUMBER := CN_API.G_VALID_LEVEL_FULL,
211 p_pe_name IN CN_QUOTAS.NAME%TYPE,
212 p_target_amount IN CN_QUOTAS.target%TYPE,
213 p_fixed_amount IN CN_QUOTAS.payment_amount%TYPE,
214 p_performance_goal IN CN_QUOTAS.performance_goal%TYPE,
215 p_even_distribute IN VARCHAR2,
216 p_prd_quota_tbl IN prd_quota_tbl_type,
217 p_org_id IN NUMBER,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_count OUT NOCOPY NUMBER,
220 x_msg_data OUT NOCOPY VARCHAR2
221
222 ) IS
223
224 l_api_name CONSTANT VARCHAR2(30) := 'Distribute_Prd_Quota';
225 l_api_version CONSTANT NUMBER := 1.0;
226
227 l_quota_id CN_QUOTAS.quota_id%TYPE;
228
229 tbl_period_id CN_PERIOD_STATUSES.period_id%TYPE;
230
231 tbl_period_quota_id CN_PERIOD_QUOTAS.period_quota_id%TYPE;
232
233 tbl_quarter_num CN_PERIOD_QUOTAS.quarter_num%TYPE;
234
235 tbl_period_year CN_PERIOD_QUOTAS.period_year%TYPE;
236
237 l_prd_count NUMBER;
238
239 f_target_amount CN_QUOTAS.target%TYPE;
240 f_fixed_amount CN_QUOTAS.payment_amount%TYPE;
241 f_performance_goal CN_QUOTAS.performance_goal%TYPE;
242
243 G_LAST_UPDATE_DATE DATE := sysdate;
244 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
245 G_CREATION_DATE DATE := sysdate;
246 G_CREATED_BY NUMBER := fnd_global.user_id;
247 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
248
249 G_ROWID VARCHAR2(30);
250 G_PROGRAM_TYPE VARCHAR2(30);
251
252 l_org_id NUMBER;
253 l_status VARCHAR2(1);
254
255 CURSOR l_quota_id_cr (p_quota_name VARCHAR2) IS
256 SELECT *
257 from cn_quotas
258 where name = p_quota_name
259 AND org_id = l_org_id;
260
261 CURSOR l_prd_quota_cr(p_quota_id NUMBER) IS
262 SELECT period_quota_id, quarter_num, period_year, period_id, itd_target, itd_payment, performance_goal_itd
263 FROM cn_period_quotas
264 WHERE quota_id = p_quota_id;
265
266
267 CURSOR f_quota_row(p_quota_id NUMBER) IS
268 SELECT *
269 from cn_quotas
270 where quota_id = p_quota_id;
271
272 CURSOR l_period_id_cr(p_period_name VARCHAR2) IS
273 select *
274 from cn_period_statuses
275 where period_name = p_period_name
276 and org_id = l_org_id;
277
278 CURSOR tbl_period_quota_info_cr(p_period_id NUMBER, p_quota_id NUMBER) IS
279 select *
280 from cn_period_quotas
281 where quota_id = p_quota_id
282 and period_id = p_period_id;
283
284 f_quota_row_rec CN_QUOTAS%ROWTYPE;
285 f_quota_id_rec CN_QUOTAS%ROWTYPE;
286 f_period_id_rec CN_PERIOD_STATUSES%ROWTYPE;
287 f_period_quota_info_rec CN_PERIOD_QUOTAS%ROWTYPE;
288
289
290 BEGIN
291 -- Standard Start of API savepoint
292 SAVEPOINT Distribute_Prd_Quota;
293 -- Standard call to check for call compatibility.
294 IF NOT FND_API.Compatible_API_Call
295 (l_api_version ,
296 p_api_version ,
297 l_api_name ,
298 G_PKG_NAME )
299 THEN
300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
301 END IF;
302 -- Initialize message list if p_init_msg_list is set to TRUE.
303 IF FND_API.to_Boolean( p_init_msg_list ) THEN
304 FND_MSG_PUB.initialize;
305 END IF;
306 -- Initialize API return status to success
307 x_return_status := FND_API.G_RET_STS_SUCCESS;
308 -- API body
309
310 -- 1. IF the PE concerned exists.
311
312 IF p_pe_name IS NULL
313 THEN
314 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
315 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
316 FND_MESSAGE.SET_TOKEN('INPUT_NAME', cn_api.get_lkup_meaning('PE_NAME', 'INPUT_TOKEN'));
317 FND_MSG_PUB.Add;
318 END IF;
319 RAISE FND_API.G_EXC_ERROR ;
320 END IF;
321
322
323 -- Validate and default org id
324
325 l_org_id := p_org_id;
326 mo_global.validate_orgid_pub_api(org_id => l_org_id, status => l_status);
327
328 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
329 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
330 'cn.plsql.CN_PRD_QUOTA_PUB.Distribute_Prd_Quota.org_validate',
331 'Validated org_id = ' || l_org_id || ' status = '|| l_status);
332 end if;
333
334
335 -- Get the Quota ID if exist
336
337
338 OPEN l_quota_id_cr(p_pe_name);
339 FETCH l_quota_id_cr into f_quota_id_rec;
340
341 IF (l_quota_id_cr%NOTFOUND)
342 THEN
343 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
344 FND_MESSAGE.SET_NAME ('CN' , 'CN_PLN_NOT_EXIST');
345 FND_MESSAGE.SET_TOKEN ('PE_NAME' , p_pe_name);
346 FND_MSG_PUB.Add;
347 END IF;
348 RAISE FND_API.G_EXC_ERROR ;
349 END IF;
350
351 CLOSE l_quota_id_cr;
352
353 l_quota_id := f_quota_id_rec.quota_id;
354
355
356 select nvl(target, 0)
357 into f_target_amount
358 from cn_quotas
359 where quota_id = l_quota_id;
360
361
362 -- 2. If target_amount, fixed_amount and Performance Goal input is null, select from cn_quotas, else update cn_quotas
363 -- target_amount check and update
364 IF p_target_amount is NULL THEN
365
366
367 select nvl(target, 0)
368 into f_target_amount
369 from cn_quotas
370 where quota_id = l_quota_id;
371
372 ELSE
373
374
375
376 IF p_target_amount <> f_target_amount THEN
377
378
379 f_target_amount := p_target_amount;
380
381 OPEN f_quota_row(l_quota_id);
382 FETCH f_quota_row INTO f_quota_row_rec;
383 CLOSE f_quota_row;
384
385
386 cn_quotas_pkg.begin_record
387 (
388 x_operation => 'UPDATE'
389 ,x_rowid => G_ROWID
390 ,x_quota_id => f_quota_row_rec.quota_id
391 ,x_name => f_quota_row_rec.name
392 ,x_object_version_number => f_quota_row_rec.object_version_number
393 ,x_target => f_target_amount
394 ,x_quota_type_code => f_quota_row_rec.quota_type_code
395 ,x_usage_code => NULL
396 ,x_payment_amount => f_quota_row_rec.payment_amount
397 ,x_description => f_quota_row_rec.description
398 ,x_start_date => f_quota_row_rec.start_date
399 ,x_end_date => f_quota_row_rec.end_date
400 ,x_quota_status => f_quota_row_rec.quota_status
401 ,x_calc_formula_id => f_quota_row_rec.calc_formula_id
402 ,x_incentive_type_code => f_quota_row_rec.incentive_type_code
403 ,x_credit_type_id => f_quota_row_rec.credit_type_id
404 ,x_rt_sched_custom_flag => f_quota_row_rec.rt_sched_custom_flag
405 ,x_package_name => f_quota_row_rec.package_name
406 ,x_performance_goal => f_quota_row_rec.performance_goal
407 ,x_interval_type_id => f_quota_row_rec.interval_type_id
408 ,x_payee_assign_flag => f_quota_row_rec.payee_assign_flag
409 ,x_vesting_flag => f_quota_row_rec.vesting_flag
410 ,x_expense_account_id => f_quota_row_rec.expense_account_id
411 ,x_liability_account_id => f_quota_row_rec.liability_account_id
412 ,x_quota_group_code => f_quota_row_rec.quota_group_code
413 ,x_quota_unspecified => NULL
414 ,x_last_update_date => G_LAST_UPDATE_DATE
415 ,x_last_updated_by => G_LAST_UPDATED_BY
416 ,x_creation_date => G_CREATION_DATE
417 ,x_created_by => G_CREATED_BY
418 ,x_last_update_login => G_LAST_UPDATE_LOGIN
419 ,x_program_type => G_PROGRAM_TYPE
420 --,x_status_code => NULL
421 ,x_period_type_code => NULL
422 ,x_start_num => NULL
423 ,x_end_num => NULL
424 ,x_addup_from_rev_class_flag => f_quota_row_rec.addup_from_rev_class_flag
425 ,x_attribute1 => f_quota_row_rec.attribute1
426 ,x_attribute2 => f_quota_row_rec.attribute2
427 ,x_attribute3 => f_quota_row_rec.attribute3
428 ,x_attribute4 => f_quota_row_rec.attribute4
429 ,x_attribute5 => f_quota_row_rec.attribute5
430 ,x_attribute6 => f_quota_row_rec.attribute6
431 ,x_attribute7 => f_quota_row_rec.attribute7
432 ,x_attribute8 => f_quota_row_rec.attribute8
433 ,x_attribute9 => f_quota_row_rec.attribute9
434 ,x_attribute10 => f_quota_row_rec.attribute10
435 ,x_attribute11 => f_quota_row_rec.attribute11
436 ,x_attribute12 => f_quota_row_rec.attribute12
437 ,x_attribute13 => f_quota_row_rec.attribute13
438 ,x_attribute14 => f_quota_row_rec.attribute14
439 ,x_attribute15 => f_quota_row_rec.attribute15
440 ,x_payment_group_code => 'STANDARD'
441 ,x_indirect_credit => f_quota_row_rec.indirect_credit
442 ,x_org_id=> f_quota_row_rec.org_id
443 ,x_salesrep_end_flag => f_quota_row_rec.salesreps_enddated_flag
444 );
445
446 END IF;
447 END IF;
448
449
450 select nvl(payment_amount, 0)
451 into f_fixed_amount
452 from cn_quotas
453 where quota_id = l_quota_id;
454
455 -- fixed Amount check and update
456 IF p_fixed_amount is NULL THEN
457
458
459 select nvl(payment_amount, 0)
460 into f_fixed_amount
461 from cn_quotas
462 where quota_id = l_quota_id;
463
464 ELSE
465
466
467
468
469 IF p_fixed_amount <> nvl(f_fixed_amount,0) THEN
470
471
472
473 f_fixed_amount := p_fixed_amount;
474
475 OPEN f_quota_row(l_quota_id);
476 FETCH f_quota_row INTO f_quota_row_rec;
477 CLOSE f_quota_row;
478
479 cn_quotas_pkg.begin_record
480 (
481 x_operation => 'UPDATE'
482 ,x_rowid => G_ROWID
483 ,x_quota_id => f_quota_row_rec.quota_id
484 ,x_name => f_quota_row_rec.name
485 ,x_object_version_number => f_quota_row_rec.object_version_number
486 ,x_target => f_quota_row_rec.target
487 ,x_quota_type_code => f_quota_row_rec.quota_type_code
488 ,x_usage_code => NULL
489 ,x_payment_amount => f_fixed_amount
490 ,x_description => f_quota_row_rec.description
491 ,x_start_date => f_quota_row_rec.start_date
492 ,x_end_date => f_quota_row_rec.end_date
493 ,x_quota_status => f_quota_row_rec.quota_status
494 ,x_calc_formula_id => f_quota_row_rec.calc_formula_id
495 ,x_incentive_type_code => f_quota_row_rec.incentive_type_code
496 ,x_credit_type_id => f_quota_row_rec.credit_type_id
497 ,x_rt_sched_custom_flag => f_quota_row_rec.rt_sched_custom_flag
498 ,x_package_name => f_quota_row_rec.package_name
499 ,x_performance_goal => f_quota_row_rec.performance_goal
500 ,x_interval_type_id => f_quota_row_rec.interval_type_id
501 ,x_payee_assign_flag => f_quota_row_rec.payee_assign_flag
502 ,x_vesting_flag => f_quota_row_rec.vesting_flag
503 ,x_expense_account_id => f_quota_row_rec.expense_account_id
504 ,x_liability_account_id => f_quota_row_rec.liability_account_id
505 ,x_quota_group_code => f_quota_row_rec.quota_group_code
506 ,x_quota_unspecified => NULL
507 ,x_last_update_date => G_LAST_UPDATE_DATE
508 ,x_last_updated_by => G_LAST_UPDATED_BY
509 ,x_creation_date => G_CREATION_DATE
510 ,x_created_by => G_CREATED_BY
511 ,x_last_update_login => G_LAST_UPDATE_LOGIN
512 ,x_program_type => G_PROGRAM_TYPE
513 --,x_status_code => NULL
514 ,x_period_type_code => NULL
515 ,x_start_num => NULL
516 ,x_end_num => NULL
517 ,x_addup_from_rev_class_flag => f_quota_row_rec.addup_from_rev_class_flag
518 ,x_attribute1 => f_quota_row_rec.attribute1
519 ,x_attribute2 => f_quota_row_rec.attribute2
520 ,x_attribute3 => f_quota_row_rec.attribute3
521 ,x_attribute4 => f_quota_row_rec.attribute4
522 ,x_attribute5 => f_quota_row_rec.attribute5
523 ,x_attribute6 => f_quota_row_rec.attribute6
524 ,x_attribute7 => f_quota_row_rec.attribute7
525 ,x_attribute8 => f_quota_row_rec.attribute8
526 ,x_attribute9 => f_quota_row_rec.attribute9
527 ,x_attribute10 => f_quota_row_rec.attribute10
528 ,x_attribute11 => f_quota_row_rec.attribute11
529 ,x_attribute12 => f_quota_row_rec.attribute12
530 ,x_attribute13 => f_quota_row_rec.attribute13
531 ,x_attribute14 => f_quota_row_rec.attribute14
532 ,x_attribute15 => f_quota_row_rec.attribute15
533 ,x_payment_group_code => 'STANDARD'
534 ,x_indirect_credit => f_quota_row_rec.indirect_credit
535 ,x_org_id=> f_quota_row_rec.org_id
536 ,x_salesrep_end_flag => f_quota_row_rec.salesreps_enddated_flag
537 );
538
539 END IF;
540 END IF;
541
542
543 -- performance goal check and update
544
545 select nvl(performance_goal, 0)
546 into f_performance_goal
547 from cn_quotas
548 where quota_id = l_quota_id;
549
550 IF p_performance_goal is NULL THEN
551
552 select nvl(performance_goal, 0)
553 into f_performance_goal
554 from cn_quotas
555 where quota_id = l_quota_id;
556
557 ELSE
558 IF p_performance_goal <> nvl(f_performance_goal,0) THEN
559
560 f_performance_goal := p_performance_goal;
561
562 OPEN f_quota_row(l_quota_id);
563 FETCH f_quota_row INTO f_quota_row_rec;
564 CLOSE f_quota_row;
565
566 cn_quotas_pkg.begin_record
567 (
568 x_operation => 'UPDATE'
569 ,x_rowid => G_ROWID
570 ,x_quota_id => f_quota_row_rec.quota_id
571 ,x_name => f_quota_row_rec.name
572 ,x_object_version_number => f_quota_row_rec.object_version_number
573 ,x_target => f_quota_row_rec.target
574 ,x_quota_type_code => f_quota_row_rec.quota_type_code
575 ,x_usage_code => NULL
576 ,x_payment_amount => f_quota_row_rec.payment_amount
577 ,x_description => f_quota_row_rec.description
578 ,x_start_date => f_quota_row_rec.start_date
579 ,x_end_date => f_quota_row_rec.end_date
580 ,x_quota_status => f_quota_row_rec.quota_status
581 ,x_calc_formula_id => f_quota_row_rec.calc_formula_id
582 ,x_incentive_type_code => f_quota_row_rec.incentive_type_code
583 ,x_credit_type_id => f_quota_row_rec.credit_type_id
584 ,x_rt_sched_custom_flag => f_quota_row_rec.rt_sched_custom_flag
585 ,x_package_name => f_quota_row_rec.package_name
586 ,x_performance_goal => f_performance_goal
587 ,x_interval_type_id => f_quota_row_rec.interval_type_id
588 ,x_payee_assign_flag => f_quota_row_rec.payee_assign_flag
589 ,x_vesting_flag => f_quota_row_rec.vesting_flag
590 ,x_expense_account_id => f_quota_row_rec.expense_account_id
591 ,x_liability_account_id => f_quota_row_rec.liability_account_id
592 ,x_quota_group_code => f_quota_row_rec.quota_group_code
593 ,x_quota_unspecified => NULL
594 ,x_last_update_date => G_LAST_UPDATE_DATE
595 ,x_last_updated_by => G_LAST_UPDATED_BY
596 ,x_creation_date => G_CREATION_DATE
597 ,x_created_by => G_CREATED_BY
598 ,x_last_update_login => G_LAST_UPDATE_LOGIN
599 ,x_program_type => G_PROGRAM_TYPE
600 --,x_status_code => NULL
601 ,x_period_type_code => NULL
602 ,x_start_num => NULL
603 ,x_end_num => NULL
604 ,x_addup_from_rev_class_flag => f_quota_row_rec.addup_from_rev_class_flag
605 ,x_attribute1 => f_quota_row_rec.attribute1
606 ,x_attribute2 => f_quota_row_rec.attribute2
607 ,x_attribute3 => f_quota_row_rec.attribute3
608 ,x_attribute4 => f_quota_row_rec.attribute4
609 ,x_attribute5 => f_quota_row_rec.attribute5
610 ,x_attribute6 => f_quota_row_rec.attribute6
611 ,x_attribute7 => f_quota_row_rec.attribute7
612 ,x_attribute8 => f_quota_row_rec.attribute8
613 ,x_attribute9 => f_quota_row_rec.attribute9
614 ,x_attribute10 => f_quota_row_rec.attribute10
615 ,x_attribute11 => f_quota_row_rec.attribute11
616 ,x_attribute12 => f_quota_row_rec.attribute12
617 ,x_attribute13 => f_quota_row_rec.attribute13
618 ,x_attribute14 => f_quota_row_rec.attribute14
619 ,x_attribute15 => f_quota_row_rec.attribute15
620 ,x_payment_group_code => 'STANDARD'
621 ,x_indirect_credit => f_quota_row_rec.indirect_credit
622 ,x_org_id=> f_quota_row_rec.org_id
623 ,x_salesrep_end_flag => f_quota_row_rec.salesreps_enddated_flag
624 );
625
626 END IF;
627 END IF;
628
629
630
631 -- 3. if even distribute is Yes, we divide the Variables by the period number.
632 IF p_even_distribute = 'Y' THEN
633
634 -- Modified to call the Distribute_Target API to distribute target, 2527429
635
636 Distribute_Target
637 (p_api_version => p_api_version ,
638 p_init_msg_list => p_init_msg_list,
639 p_commit => p_commit ,
640 p_validation_level => p_validation_level,
641 p_quota_id => l_quota_id,
642 x_return_status => x_return_status,
643 x_msg_count => x_msg_count,
644 x_msg_data => x_msg_data);
645
646
647 -- 4. IF not evenly distributed, we update the cn_period_quotas using the values in the table.
648 ELSE --IF p_even_distribute = 'Y' THEN
649
650 IF p_prd_quota_tbl.COUNT > 0 THEN
651
652 FOR i IN 1 .. p_prd_quota_tbl.COUNT LOOP
653
654
655
656 IF p_prd_quota_tbl(i).period_name IS NULL
657 THEN
658 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
659 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
660 FND_MESSAGE.SET_TOKEN('INPUT_NAME', cn_api.get_lkup_meaning('PERIOD_NAME', 'INPUT_TOKEN'));
661 FND_MSG_PUB.Add;
662 END IF;
663 RAISE FND_API.G_EXC_ERROR ;
664 END IF;
665
666 OPEN l_period_id_cr(p_prd_quota_tbl(i).period_name);
667 FETCH l_period_id_cr into f_period_id_rec;
668 IF (l_period_id_cr%NOTFOUND)
669 THEN
670 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
671 FND_MESSAGE.SET_NAME ('CN' , 'CN_PERIOD_NOT_FOUND');
672 FND_MSG_PUB.Add;
673 END IF;
674 RAISE FND_API.G_EXC_ERROR ;
675 END IF;
676
677 CLOSE l_period_id_cr;
678
679 tbl_period_id := f_period_id_rec.period_id;
680
681 OPEN tbl_period_quota_info_cr(tbl_period_id, l_quota_id);
682 FETCH tbl_period_quota_info_cr into f_period_quota_info_rec;
683 IF (tbl_period_quota_info_cr%NOTFOUND)
684 THEN
685 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
686 FND_MESSAGE.SET_NAME ('CN' , 'CN_PERIOD_QUOTA_NOT_EXIST');
687 FND_MESSAGE.SET_TOKEN ('PERIOD_NAME' , p_prd_quota_tbl(i).period_name);
688 FND_MESSAGE.SET_TOKEN ('PLAN_NAME' , p_pe_name);
689 FND_MSG_PUB.Add;
690 END IF;
691 RAISE FND_API.G_EXC_ERROR ;
692 END IF;
693
694 CLOSE tbl_period_quota_info_cr;
695
696 tbl_period_quota_id := f_period_quota_info_rec.period_quota_id;
697 tbl_quarter_num := f_period_quota_info_rec.quarter_num;
698 tbl_period_year := f_period_quota_info_rec.period_year;
699
700
701
702
703 CN_PERIOD_QUOTAS_PKG.Begin_Record(
704 x_operation => 'UPDATE',
705 X_period_quota_id => tbl_period_quota_id,
706 x_period_id => tbl_period_id,
707 x_quota_id => l_quota_id,
708 x_period_target => p_prd_quota_tbl(i).PERIOD_TARGET,
709 x_itd_target => NULL,
710 x_period_payment => p_prd_quota_tbl(i).PERIOD_PAYMENT ,
711 x_itd_payment => NULL,
712 x_quarter_num => tbl_quarter_num,
713 x_period_year => tbl_period_year,
714 x_creation_date => sysdate,
715 x_last_update_date => sysdate,
716 x_last_update_login => fnd_global.login_id,
717 x_last_updated_by => fnd_global.user_id,
718 x_created_by => fnd_global.user_id,
719 x_period_type_code => 'PERIOD',
720 x_performance_goal => p_prd_quota_tbl(i).PERFORMANCE_GOAL);
721
722
723
724 END LOOP;
725
726 END IF; --IF p_prd_quota_tbl%COUNT > 0
727
728 END IF; --IF p_even_distribute = 'Y' THEN
729
730
731
732
733
734
735 -- End of API body.
736 -- Standard check of p_commit.
737 IF FND_API.To_Boolean( p_commit ) THEN
738 COMMIT WORK;
739 END IF;
740 -- Standard call to get message count and if count is 1, get message info.
741 FND_MSG_PUB.Count_And_Get
742 (p_count => x_msg_count ,
743 p_data => x_msg_data ,
744 p_encoded => FND_API.G_FALSE );
745 EXCEPTION
746 WHEN FND_API.G_EXC_ERROR THEN
747 ROLLBACK TO Distribute_Prd_Quota;
748 x_return_status := FND_API.G_RET_STS_ERROR ;
749 FND_MSG_PUB.Count_And_Get
750 (p_count => x_msg_count ,
751 p_data => x_msg_data ,
752 p_encoded => FND_API.G_FALSE );
753 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
754 ROLLBACK TO Distribute_Prd_Quota;
755 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
756 FND_MSG_PUB.Count_And_Get
757 (p_count => x_msg_count ,
758 p_data => x_msg_data ,
759 p_encoded => FND_API.G_FALSE );
760 WHEN OTHERS THEN
761 ROLLBACK TO Distribute_Prd_Quota;
762 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
763 IF FND_MSG_PUB.Check_Msg_Level
764 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
765 THEN
766 FND_MSG_PUB.Add_Exc_Msg
767 (G_PKG_NAME ,
768 l_api_name );
769 END IF;
770 FND_MSG_PUB.Count_And_Get
771 (p_count => x_msg_count ,
772 p_data => x_msg_data ,
773 p_encoded => FND_API.G_FALSE );
774 END Distribute_Prd_Quota;
775
776
777 END CN_PRD_QUOTA_PUB;