[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_PRD_QUOTA_PUB
Source
1 PACKAGE BODY CN_SRP_PRD_QUOTA_PUB AS
2 /*$Header: cnvspdbb.pls 120.2 2005/10/27 16:05:39 mblum noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CN_SRP_PRD_QUOTA_PUB';
5
6
7 PROCEDURE Distribute_Srp_Prd_Quota
8 ( p_api_version IN NUMBER := CN_API.G_MISS_NUM,
9 p_init_msg_list IN VARCHAR2 := CN_API.G_FALSE,
10 p_commit IN VARCHAR2 := CN_API.G_FALSE,
11 p_validation_level IN NUMBER := CN_API.G_VALID_LEVEL_FULL,
12 p_salesrep_name IN CN_SALESREPS.NAME%TYPE,
13 p_employee_number IN CN_SALESREPS.EMPLOYEE_NUMBER%TYPE,
14 p_role_name IN CN_ROLES.NAME%TYPE,
15 p_cp_name IN CN_COMP_PLANS.NAME%TYPE,
16 p_srp_plan_start_date IN CN_SRP_PLAN_ASSIGNS.START_DATE%TYPE,
17 p_srp_plan_end_date IN CN_SRP_PLAN_ASSIGNS.END_DATE%TYPE,
18 p_pe_name IN CN_QUOTAS.NAME%TYPE,
19 p_target_amount IN CN_SRP_QUOTA_ASSIGNS.target%TYPE,
20 p_fixed_amount IN CN_SRP_QUOTA_ASSIGNS.payment_amount%TYPE,
21 p_performance_goal IN CN_SRP_QUOTA_ASSIGNS.performance_goal%TYPE,
22 p_even_distribute IN VARCHAR2,
23 p_srp_prd_quota_tbl IN srp_prd_quota_tbl_type,
24 p_org_id IN NUMBER,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2
28
29 ) IS
30
31 l_api_name CONSTANT VARCHAR2(30) := 'Distribute_Srp_Prd_Quota';
32 l_api_version CONSTANT NUMBER := 1.0;
33
34 l_quota_id CN_QUOTAS.quota_id%TYPE;
35 l_salesrep_id CN_SALESREPS.salesrep_id%TYPE;
36 l_role_id CN_ROLES.role_id%TYPE;
37 l_comp_plan_id CN_COMP_PLANS.comp_plan_id%TYPE;
38 l_srp_plan_assign_id CN_SRP_PLAN_ASSIGNS.srp_plan_assign_id%TYPE;
39 l_srp_quota_assign_id CN_SRP_quota_ASSIGNS.srp_quota_assign_id%TYPE;
40 l_customized_flag CN_SRP_quota_ASSIGNS.customized_flag%TYPE;
41
42 l_period_target_unit_code CN_SRP_quota_ASSIGNS.period_target_unit_code%TYPE;
43
44 tbl_period_id CN_PERIOD_STATUSES.period_id%TYPE;
45 tbl_srp_period_quota_id CN_SRP_PERIOD_QUOTAS.srp_period_quota_id%TYPE;
46 tbl_quarter_num CN_SRP_PERIOD_QUOTAS_V.quarter_num%TYPE;
47 tbl_period_year CN_SRP_PERIOD_QUOTAS_V.period_year%TYPE;
48 l_prd_count NUMBER;
49 l_upd_srp_quota_assign VARCHAR2(1);
50 l_commission_payed_ptd CN_SRP_PERIOD_QUOTAS.commission_payed_ptd%TYPE;
51
52 f_target_amount CN_QUOTAS.target%TYPE;
53 f_fixed_amount CN_QUOTAS.payment_amount%TYPE;
54 f_performance_goal CN_QUOTAS.performance_goal%TYPE;
55
56 G_LAST_UPDATE_DATE DATE := sysdate;
57 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
58 G_CREATION_DATE DATE := sysdate;
59 G_CREATED_BY NUMBER := fnd_global.user_id;
60 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
61
62 G_ROWID VARCHAR2(30);
63 G_PROGRAM_TYPE VARCHAR2(30);
64
65 l_status varchar2(4000);
66 l_loading_status varchar2(50);
67 l_return_status VARCHAR2(50);
68 l_msg_count NUMBER;
69 l_msg_data VARCHAR2(2000);
70 l_org_id NUMBER;
71
72 CURSOR l_quota_id_cr (p_quota_name VARCHAR2) IS
73 SELECT *
74 from cn_quotas
75 where name = p_quota_name AND delete_flag = 'N';
76
77 CURSOR l_salesrep_id_cr (p_salesrep_name VARCHAR2,
78 p_employee_number VARCHAR2) IS
79 SELECT *
80 from cn_salesreps
81 where name = p_salesrep_name
82 and employee_number = p_employee_number
83 AND org_id = l_org_id;
84
85 CURSOR l_role_id_cr (p_role_name VARCHAR2) IS
86 SELECT *
87 from cn_roles
88 where name = p_role_name;
89
90 CURSOR l_comp_plan_id_cr (p_comp_plan_name VARCHAR2) IS
91 SELECT *
92 from cn_comp_plans
93 where name = p_comp_plan_name
94 AND org_id = l_org_id;
95
96 CURSOR l_srp_plan_assign_id_cr (p_salesrep_id NUMBER,
97 p_role_id NUMBER,
98 p_comp_plan_id NUMBER,
99 p_srp_plan_sd DATE,
100 p_srp_plan_ed DATE) IS
101 SELECT *
102 from cn_srp_plan_assigns
103 where salesrep_id = p_salesrep_id
104 and role_id = p_role_id
105 and comp_plan_id = p_comp_plan_id
106 and start_date = p_srp_plan_sd
107 and end_date = p_srp_plan_ed
108 AND org_id = l_org_id;
109
110 CURSOR l_srp_quota_assign_id_cr (p_srp_plan_assign_id NUMBER,
111 p_quota_id NUMBER) IS
112 SELECT *
113 from cn_srp_quota_assigns
114 where srp_plan_assign_id = p_srp_plan_assign_id
115 and quota_id = p_quota_id;
116
117 CURSOR l_srp_prd_quota_cr(p_srp_quota_assign_id NUMBER) IS
118 SELECT *
119 FROM cn_srp_period_quotas_v
120 WHERE srp_quota_assign_id = p_srp_quota_assign_id;
121
122 CURSOR f_quota_row(p_quota_id NUMBER) IS
123 SELECT *
124 from cn_quotas
125 where quota_id = p_quota_id AND delete_flag = 'N';
126
127 CURSOR tbl_period_quota_info_cr(p_period_id NUMBER,
128 p_srp_quota_assign_id NUMBER) IS
129 select *
130 from cn_srp_period_quotas_v
131 where srp_quota_assign_id = p_srp_quota_assign_id
132 and period_id = p_period_id;
133
134 CURSOR l_period_id_cr(p_period_name VARCHAR2) IS
135 select *
136 from cn_period_statuses
137 where period_name = p_period_name
138 AND org_id = l_org_id;
139
140 f_quota_row_rec CN_QUOTAS%ROWTYPE;
141 f_quota_id_rec CN_QUOTAS%ROWTYPE;
142 f_salesrep_id_rec CN_SALESREPS%ROWTYPE;
143 f_role_id_rec CN_ROLES%ROWTYPE;
144 f_comp_plan_id_rec CN_COMP_PLANS%ROWTYPE;
145 f_srp_plan_assign_id_rec CN_SRP_PLAN_ASSIGNS%ROWTYPE;
146 f_srp_quota_assign_id_rec CN_SRP_QUOTA_ASSIGNS%ROWTYPE;
147 f_period_id_rec CN_PERIOD_STATUSES%ROWTYPE;
148 f_period_quota_info_rec cn_srp_period_quotas_v%ROWTYPE;
149
150
151
152 BEGIN
153 -- Standard Start of API savepoint
154 SAVEPOINT Distribute_Srp_Prd_Quota;
155 -- Standard call to check for call compatibility.
156 IF NOT FND_API.Compatible_API_Call
157 (l_api_version ,
158 p_api_version ,
159 l_api_name ,
160 G_PKG_NAME )
161 THEN
162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
163 END IF;
164 -- Initialize message list if p_init_msg_list is set to TRUE.
165 IF FND_API.to_Boolean( p_init_msg_list ) THEN
166 FND_MSG_PUB.initialize;
167 END IF;
168 -- Initialize API return status to success
169 x_return_status := FND_API.G_RET_STS_SUCCESS;
170 -- API body
171
172 -- 0. if the org ID is valid
173 l_org_id := p_org_id;
174 mo_global.validate_orgid_pub_api
175 (org_id => l_org_id,
176 status => l_status);
177
178 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
179 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
180 'cn.plsql.cn_srp_prd_quota_pub.distribute_srp_prd_quota.org_validate',
181 'Validated org_id = ' || l_org_id || ' status = ' || l_status);
182 end if;
183
184 -- 1. IF the PE concerned exists.
185
186 IF p_pe_name IS NULL
187 THEN
188 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
189 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
190 FND_MESSAGE.SET_TOKEN('INPUT_NAME', cn_api.get_lkup_meaning('PE_NAME', 'INPUT_TOKEN'));
191 FND_MSG_PUB.Add;
192 END IF;
193 RAISE FND_API.G_EXC_ERROR ;
194 END IF;
195
196 -- Get the Quota ID if exist
197 OPEN l_quota_id_cr(p_pe_name);
198 FETCH l_quota_id_cr into f_quota_id_rec;
199
200 IF (l_quota_id_cr%NOTFOUND)
201 THEN
202 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
203 FND_MESSAGE.SET_NAME ('CN' , 'CN_PLN_NOT_EXIST');
204 FND_MESSAGE.SET_TOKEN ('PE_NAME' , p_pe_name);
205 FND_MSG_PUB.Add;
206 END IF;
207 RAISE FND_API.G_EXC_ERROR ;
208 END IF;
209
210 CLOSE l_quota_id_cr;
211
212 l_quota_id := f_quota_id_rec.quota_id;
213
214
215
216 -- 2. get the salesrep_id using salesrep_name and employee_number
217 IF p_salesrep_name IS NULL
218 THEN
219 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
220 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
221 FND_MESSAGE.SET_TOKEN('INPUT_NAME', cn_api.get_lkup_meaning('SR_NAME', 'INPUT_TOKEN'));
222 FND_MSG_PUB.Add;
223 END IF;
224 RAISE FND_API.G_EXC_ERROR ;
225 END IF;
226
227 OPEN l_salesrep_id_cr(p_salesrep_name, p_employee_number);
228 FETCH l_salesrep_id_cr into f_salesrep_id_rec;
229
230 IF (l_salesrep_id_cr%NOTFOUND)
231 THEN
232 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
233 FND_MESSAGE.SET_NAME ('CN' , 'CN_SRP_NOT_FOUND');
234 FND_MSG_PUB.Add;
235 END IF;
236 RAISE FND_API.G_EXC_ERROR ;
237 END IF;
238
239 CLOSE l_salesrep_id_cr;
240 l_salesrep_id := f_salesrep_id_rec.salesrep_id;
241
242 -- 3. get Role ID
243
244 IF p_role_name IS NULL
245 THEN
246 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
247 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
248 FND_MESSAGE.SET_TOKEN('INPUT_NAME', cn_api.get_lkup_meaning('ROLE_NAME', 'INPUT_TOKEN'));
249 FND_MSG_PUB.Add;
250 END IF;
251 RAISE FND_API.G_EXC_ERROR ;
252 END IF;
253
254 OPEN l_role_id_cr(p_role_name);
255 FETCH l_role_id_cr into f_role_id_rec;
256
257 IF (l_role_id_cr%NOTFOUND)
258 THEN
259 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
260 FND_MESSAGE.SET_NAME ('CN' , 'CN_QM_INVALID_SRPROLE');
261 FND_MSG_PUB.Add;
262 END IF;
263 RAISE FND_API.G_EXC_ERROR ;
264 END IF;
265
266 CLOSE l_role_id_cr;
267
268 l_role_id := f_role_id_rec.role_id;
269
270 -- 4. get Comp Plan ID
271 IF p_cp_name IS NULL
272 THEN
273 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
274 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
275 FND_MESSAGE.SET_TOKEN('INPUT_NAME', cn_api.get_lkup_meaning('CP_NAME', 'INPUT_TOKEN'));
276 FND_MSG_PUB.Add;
277 END IF;
278 RAISE FND_API.G_EXC_ERROR ;
279 END IF;
280
281 OPEN l_comp_plan_id_cr(p_cp_name);
282 FETCH l_comp_plan_id_cr into f_comp_plan_id_rec;
283
284 IF (l_comp_plan_id_cr%NOTFOUND)
285 THEN
286 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
287 FND_MESSAGE.SET_NAME ('CN' , 'CN_CP_NOT_EXIST');
288 FND_MESSAGE.SET_TOKEN ('CP_NAME' , p_cp_name);
289 FND_MSG_PUB.Add;
290 END IF;
291 RAISE FND_API.G_EXC_ERROR ;
292 END IF;
293
294 CLOSE l_comp_plan_id_cr;
295
296 l_comp_plan_id := f_comp_plan_id_rec.comp_plan_id;
297
298 -- 5. get srp_plan_assign_id using salesrep_id, role_id, comp_plan_id, start_date, end_date
299
300 IF p_srp_plan_start_date IS NULL
301 THEN
302 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
303 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
304 FND_MESSAGE.SET_TOKEN('INPUT_NAME', cn_api.get_lkup_meaning('SD', 'INPUT_TOKEN'));
305 FND_MSG_PUB.Add;
306 END IF;
307 RAISE FND_API.G_EXC_ERROR ;
308 END IF;
309
310 OPEN l_srp_plan_assign_id_cr(l_salesrep_id, l_role_id, l_comp_plan_id, p_srp_plan_start_date, p_srp_plan_end_date);
311 FETCH l_srp_plan_assign_id_cr into f_srp_plan_assign_id_rec;
312
313 IF (l_srp_plan_assign_id_cr%NOTFOUND)
314 THEN
315 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
316 FND_MESSAGE.SET_NAME ('CN' , 'CN_SRP_PLN_ASSIGN_NOT_EXIST');
317 FND_MSG_PUB.Add;
318 END IF;
319 RAISE FND_API.G_EXC_ERROR ;
320 END IF;
321
322 l_srp_plan_assign_id := f_srp_plan_assign_id_rec.srp_plan_assign_id;
323
324 -- 6. get the srp_quota_assign_id
325
326 OPEN l_srp_quota_assign_id_cr(l_srp_plan_assign_id, l_quota_id);
327 FETCH l_srp_quota_assign_id_cr into f_srp_quota_assign_id_rec;
328
329 IF (l_srp_quota_assign_id_cr%NOTFOUND)
330 THEN
331 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
332 FND_MESSAGE.SET_NAME ('CN' , 'CN_QUOTA_ASSIGN_NOT_EXIST');
333 FND_MSG_PUB.Add;
334 END IF;
335 RAISE FND_API.G_EXC_ERROR ;
336 END IF;
337
338 l_srp_quota_assign_id := f_srp_quota_assign_id_rec.srp_quota_assign_id;
339 l_customized_flag := f_srp_quota_assign_id_rec.customized_flag;
340
341 If l_customized_flag <> 'Y'
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_PE_NOT_CUSTOMIZABLE');
345 FND_MSG_PUB.Add;
346 END IF;
347 RAISE FND_API.G_EXC_ERROR ;
348 END IF;
349
350 select nvl(target, 0)
351 into f_target_amount
352 from cn_srp_quota_assigns
353 where srp_quota_assign_id = l_srp_quota_assign_id;
354
355
356 -- 2. If target_amount, fixed_amount and Performance Goal input is null, select from cn_quotas, else update cn_quotas
357 -- target_amount check and update
358
359 l_upd_srp_quota_assign := 'N';
360
361 IF p_target_amount is NULL THEN
362
363 select nvl(target, 0)
364 into f_target_amount
365 from cn_srp_quota_assigns
366 where srp_quota_assign_id = l_srp_quota_assign_id;
367
368 ELSE
369 IF p_target_amount <> f_target_amount THEN
370
371 f_target_amount := p_target_amount;
372
373 l_upd_srp_quota_assign := 'Y';
374
375 END IF;
376 END IF;
377
378 select nvl(payment_amount, 0)
379 into f_fixed_amount
380 from cn_srp_quota_assigns
381 where srp_quota_assign_id = l_srp_quota_assign_id;
382
383 -- fixed Amount check and update
384 IF p_fixed_amount is NULL THEN
385
386 select nvl(payment_amount, 0)
387 into f_fixed_amount
388 from cn_srp_quota_assigns
389 where srp_quota_assign_id = l_srp_quota_assign_id;
390
391 ELSE
392
393 IF p_fixed_amount <> nvl(f_fixed_amount,0) THEN
394
395 f_fixed_amount := p_fixed_amount;
396 l_upd_srp_quota_assign := 'Y';
397
398 END IF;
399 END IF;
400
401 select nvl(performance_goal, 0)
402 into f_performance_goal
403 from cn_srp_quota_assigns
404 where srp_quota_assign_id = l_srp_quota_assign_id;
405
406 -- performance goal check and update
407
408 IF p_performance_goal is NULL THEN
409
410 select nvl(performance_goal, 0)
411 into f_performance_goal
412 from cn_srp_quota_assigns
413 where srp_quota_assign_id = l_srp_quota_assign_id;
414
415 ELSE
416 IF p_performance_goal <> nvl(f_performance_goal,0) THEN
417
418 f_performance_goal := p_performance_goal;
419 l_upd_srp_quota_assign := 'Y';
420
421 END IF;
422 END IF;
423
424
425 If l_upd_srp_quota_assign = 'Y' THEN
426
427
428 CN_SRP_CUSTOMIZE_PUB.update_srp_quota_assign
429 (p_api_version => 1.0,
430 p_init_msg_list => 'T',
431 p_commit => 'F',
432 p_validation_level => 100,
433 p_srp_quota_assign_id => l_srp_quota_assign_id,
434 p_customized_flag => l_customized_flag,
435 p_quota => f_target_amount,
436 p_fixed_amount => f_fixed_amount,
437 p_goal => f_performance_goal,
438 x_return_status => l_return_status,
439 x_msg_count => l_msg_count,
440 x_msg_data => l_msg_data,
441 x_loading_status => l_loading_status,
442 x_status => l_status);
443
444 END IF; --If l_upd_srp_quota_assign = 'Y'
445
446
447 -- 3. if even distribute is Yes, we divide the Variables by the period number.
448 IF p_even_distribute = 'Y' THEN
449
450 -- Modified to call the Distribute_Target API to distribute target, 2527429
451
452 -- CN_SRP_PRD_QUOTA_PVT.distribute_target
453 CN_SRP_PERIOD_QUOTAS_PKG.Distribute_Target
454 ( x_srp_quota_assign_id => l_srp_quota_assign_id,
455 x_target => 0,
456 x_period_target_unit_code => NULL);
457
458
459 -- 4. IF not evenly distributed, we update the cn_period_quotas using the values in the table.
460 ELSE --IF p_even_distribute = 'Y' THEN
461
462 IF p_srp_prd_quota_tbl.COUNT > 0 THEN
463
464 FOR i IN 1 .. p_srp_prd_quota_tbl.COUNT LOOP
465
466 IF p_srp_prd_quota_tbl(i).period_name IS NULL
467 THEN
468 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
469 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
470 FND_MESSAGE.SET_TOKEN('INPUT_NAME', cn_api.get_lkup_meaning('PERIOD_NAME', 'INPUT_TOKEN'));
471 FND_MSG_PUB.Add;
472 END IF;
473 RAISE FND_API.G_EXC_ERROR ;
474 END IF;
475
476 OPEN l_period_id_cr(p_srp_prd_quota_tbl(i).period_name);
477 FETCH l_period_id_cr into f_period_id_rec;
478 IF (l_period_id_cr%NOTFOUND)
479 THEN
480 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
481 FND_MESSAGE.SET_NAME ('CN' , 'CN_PERIOD_NOT_FOUND');
482 FND_MSG_PUB.Add;
483 END IF;
484 RAISE FND_API.G_EXC_ERROR ;
485 END IF;
486
487 CLOSE l_period_id_cr;
488
489 tbl_period_id := f_period_id_rec.period_id;
490
491 OPEN tbl_period_quota_info_cr(tbl_period_id, l_srp_quota_assign_id);
492 FETCH tbl_period_quota_info_cr into f_period_quota_info_rec;
493 IF (tbl_period_quota_info_cr%NOTFOUND)
494 THEN
495 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
496 FND_MESSAGE.SET_NAME ('CN' , 'CN_SRP_PERIOD_QUOTA_NOT_EXIST');
497 FND_MSG_PUB.Add;
498 END IF;
499 RAISE FND_API.G_EXC_ERROR ;
500 END IF;
501
502 CLOSE tbl_period_quota_info_cr;
503
504 tbl_srp_period_quota_id := f_period_quota_info_rec.srp_period_quota_id;
505 tbl_period_id := f_period_quota_info_rec.period_id;
506 tbl_quarter_num := f_period_quota_info_rec.quarter_num;
507 tbl_period_year := f_period_quota_info_rec.period_year;
508
509 select commission_payed_ptd
510 into l_commission_payed_ptd
511 from cn_srp_period_quotas
512 where srp_period_quota_id = tbl_srp_period_quota_id;
513
514 CN_SRP_PERIOD_QUOTAS_PKG.Begin_Record
515 (x_operation => 'UPDATE'
516 ,x_period_target_unit_code => f_srp_quota_assign_id_rec.period_target_unit_code
517 ,x_srp_period_quota_id => tbl_srp_period_quota_id
518 ,x_srp_quota_assign_id => l_srp_quota_assign_id
519 ,x_srp_plan_assign_id => l_srp_plan_assign_id
520 ,x_quota_id => l_quota_id
521 ,x_period_id => tbl_period_id
522 ,x_target_amount => p_srp_prd_quota_tbl(i).PERIOD_TARGET
523 ,x_period_payment =>p_srp_prd_quota_tbl(i).PERIOD_PAYMENT
524 ,x_performance_goal => p_srp_prd_quota_tbl(i).PERFORMANCE_GOAL
525 ,x_quarter_num => tbl_quarter_num
526 ,x_period_year => tbl_period_year
527 ,x_quota_type_code => f_srp_quota_assign_id_rec.quota_type_code
528 ,x_salesrep_id => l_salesrep_id
529 ,x_end_date => NULL
530 ,x_commission_payed_ptd => l_commission_payed_ptd
531 ,x_creation_date => sysdate
532 ,x_created_by => fnd_global.user_id
533 ,x_last_update_date => sysdate
534 ,x_last_updated_by => fnd_global.user_id
535 ,x_last_update_login => fnd_global.login_id);
536
537 END LOOP;
538
539 END IF; --IF p_srp_prd_quota_tbl%COUNT > 0
540
541 END IF; --IF p_even_distribute = 'Y' THEN
542
543 -- End of API body.
544 -- Standard check of p_commit.
545 IF FND_API.To_Boolean( p_commit ) THEN
546 COMMIT WORK;
547 END IF;
548 -- Standard call to get message count and if count is 1, get message info.
549 FND_MSG_PUB.Count_And_Get
550 (p_count => x_msg_count ,
551 p_data => x_msg_data ,
552 p_encoded => FND_API.G_FALSE );
553 EXCEPTION
554 WHEN FND_API.G_EXC_ERROR THEN
555 ROLLBACK TO Distribute_Srp_Prd_Quota;
556 x_return_status := FND_API.G_RET_STS_ERROR ;
557 FND_MSG_PUB.Count_And_Get
558 (p_count => x_msg_count ,
559 p_data => x_msg_data ,
560 p_encoded => FND_API.G_FALSE );
561 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
562 ROLLBACK TO Distribute_Srp_Prd_Quota;
563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
564 FND_MSG_PUB.Count_And_Get
565 (p_count => x_msg_count ,
566 p_data => x_msg_data ,
567 p_encoded => FND_API.G_FALSE );
568 WHEN OTHERS THEN
569 ROLLBACK TO Distribute_Srp_Prd_Quota;
570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
571 IF FND_MSG_PUB.Check_Msg_Level
572 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
573 THEN
574 FND_MSG_PUB.Add_Exc_Msg
575 (G_PKG_NAME ,
576 l_api_name );
577 END IF;
578 FND_MSG_PUB.Count_And_Get
579 (p_count => x_msg_count ,
580 p_data => x_msg_data ,
581 p_encoded => FND_API.G_FALSE );
582 END Distribute_Srp_Prd_Quota;
583
584
585 END CN_SRP_PRD_QUOTA_PUB;