[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_PLAN_ASSIGNS_PVT
Source
1 PACKAGE BODY CN_SRP_PLAN_ASSIGNS_PVT AS
2 /* $Header: cnvspab.pls 120.2 2006/03/20 15:40:54 mblum noship $ */
3
4 -- Global variablefor the translatable name for all Plan Assign objects.
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SRP_PLAN_ASSIGNS_PVT';
6 G_NULL_DATE CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
7
8 --| ----------------------------------------------------------------------+
9 --| Procedure : valid_srp_plan_assign
10 --| Desc : Procedure to validate srp plan assignment to a salesrep
11 --| ---------------------------------------------------------------------+
12 PROCEDURE valid_srp_plan_assign
13 (
14 x_return_status OUT NOCOPY VARCHAR2 ,
15 x_msg_count OUT NOCOPY NUMBER ,
16 x_msg_data OUT NOCOPY VARCHAR2 ,
17 p_srp_role_id IN NUMBER,
18 p_role_plan_id IN NUMBER,
19 x_srp_plan_assigns_row IN OUT NOCOPY cn_srp_plan_assigns%ROWTYPE ,
20 x_role_id OUT NOCOPY NUMBER,
21 p_loading_status IN VARCHAR2 ,
22 x_loading_status OUT NOCOPY VARCHAR2
23 )
24 IS
25 l_api_name CONSTANT VARCHAR2(30) := 'valid_srp_plan_assign';
26 l_dummy NUMBER;
27 l_srp_roles_row cn_srp_roles%ROWTYPE;
28 l_role_plans_row cn_role_plans%ROWTYPE;
29 l_srp_pay_grp_sd cn_srp_pay_groups.start_date%TYPE;
30 l_srp_pay_grp_ed cn_srp_pay_groups.end_date%TYPE;
31 l_spg_max_sd cn_srp_pay_groups.start_date%TYPE;
32 l_pay_group_id cn_srp_pay_groups.pay_group_id%TYPE;
33
34 l_temp_count NUMBER;
35 BEGIN
36 -- Initialize API return status to success
37 x_return_status := FND_API.G_RET_STS_SUCCESS;
38 x_loading_status := p_loading_status;
39 -- API body
40 -- Check if Comp Plan does assign to the sales role
41 BEGIN
42 SELECT *
43 INTO l_role_plans_row
44 FROM cn_role_plans
45 WHERE role_plan_id = p_role_plan_id;
46 EXCEPTION
47 WHEN NO_DATA_FOUND THEN
48 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
49 FND_MESSAGE.SET_NAME ('CN' , 'CN_ROLE_PLAN_ID_NOT_EXIST');
50 FND_MSG_PUB.Add;
51 END IF;
52 x_loading_status := 'CN_ROLE_PLAN_ID_NOT_EXIST';
53 RAISE FND_API.G_EXC_ERROR ;
54 END;
55 x_srp_plan_assigns_row.role_plan_id := p_role_plan_id;
56 x_srp_plan_assigns_row.comp_plan_id := l_role_plans_row.comp_plan_id;
57 x_srp_plan_assigns_row.org_id := l_role_plans_row.org_id; --MOAC
58
59
60 -- Check if Role does assign to the salesrep
61 BEGIN
62 SELECT *
63 INTO l_srp_roles_row
64 FROM cn_srp_roles
65 WHERE srp_role_id = p_srp_role_id
66 AND org_id = l_role_plans_row.org_id; --MOAC
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
70 FND_MESSAGE.SET_NAME ('CN' , 'CN_SRP_ROLE_ID_NOT_EXIST');
71 FND_MSG_PUB.Add;
72 END IF;
73 x_loading_status := 'CN_SRP_ROLE_ID_NOT_EXIST';
74 RAISE FND_API.G_EXC_ERROR ;
75 END;
76 x_srp_plan_assigns_row.srp_role_id := p_srp_role_id;
77 x_srp_plan_assigns_row.salesrep_id := l_srp_roles_row.salesrep_id;
78 x_srp_plan_assigns_row.role_id := l_srp_roles_row.role_id;
79
80
81 -- Check if pased in role_plan_id and srp_role_id are use the same role
82 IF l_role_plans_row.role_id <> l_srp_roles_row.role_id THEN
83 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
84 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPA_ROLE_ID_NOT_SAME');
85 FND_MSG_PUB.Add;
86 END IF;
87 x_loading_status := 'CN_SPA_ROLE_ID_NOT_SAME';
88 RAISE FND_API.G_EXC_ERROR ;
89 END IF;
90 x_role_id := l_role_plans_row.role_id;
91
92 -- Get correct start date/end date
93 -- Must be in range of these 3 :
94 -- 1. srp_roles : start date / end date
95 -- 2. role_plans: start date / end date
96 -- 3. srp_pay_groups : min(start date) / max(end date)
97
98
99 -- check whether paygroup assignment exists or not
100 SELECT count(1)
101 INTO l_temp_count
102 FROM cn_srp_pay_groups
103 WHERE salesrep_id = x_srp_plan_assigns_row.salesrep_id
104 AND org_id = x_srp_plan_assigns_row.org_id; -- MOAC
105
106
107 -- Get srp_pay_groups : max(end date) from max(start_date) record
108 -- if it's NULL, get the end_date from cn_pay_groups
109 IF l_temp_count > 0 THEN
110
111 -- Get srp_pay_groups : min(start date)
112 SELECT MIN(start_date), MAX(start_date)
113 INTO l_srp_pay_grp_sd, l_spg_max_sd
114 FROM cn_srp_pay_groups
115 WHERE salesrep_id = x_srp_plan_assigns_row.salesrep_id
116 AND org_id = x_srp_plan_assigns_row.org_id; -- MOAC
117
118 BEGIN
119 SELECT Decode(spg.end_date, NULL, pg.end_date,spg.end_date),
120 spg.pay_group_id
121 INTO l_srp_pay_grp_ed,l_pay_group_id
122 FROM cn_srp_pay_groups spg, cn_pay_groups pg
123 WHERE spg.pay_group_id = pg.pay_group_id
124 AND spg.salesrep_id = x_srp_plan_assigns_row.salesrep_id
125 AND spg.org_id = x_srp_plan_assigns_row.org_id -- MOAC
126 AND spg.start_date = l_spg_max_sd;
127 EXCEPTION
128 WHEN no_data_found THEN
129 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
130 FND_MESSAGE.Set_Name('CN', 'CN_PAY_GROUP_NOT_FOUND');
131 FND_MESSAGE.SET_TOKEN('PAY_GROUP_ID', l_pay_group_id);
132 FND_MSG_PUB.Add;
133 END IF;
134 x_loading_status := 'CN_PAY_GROUP_NOT_FOUND';
135 RAISE FND_API.G_EXC_ERROR ;
136 END;
137 ELSE
138 --l_srp_pay_grp_sd := l_role_plans_row.start_date;
139 --l_srp_pay_grp_ed := l_role_plans_row.end_date;
140 x_loading_status := 'CN_SPA_NO_INTERSECT_DATE';
141 GOTO end_of_valid_srp_plan_assign;
142 END IF;
143
144 -- start_date : compare rule 1 and rule 2
145 IF l_srp_roles_row.start_date < l_role_plans_row.start_date THEN
146 x_srp_plan_assigns_row.start_date := l_role_plans_row.start_date;
147 ELSE
148 x_srp_plan_assigns_row.start_date := l_srp_roles_row.start_date;
149 END IF;
150 -- start_date : compare with rule 3
151 IF x_srp_plan_assigns_row.start_date < l_srp_pay_grp_sd THEN
152 x_srp_plan_assigns_row.start_date := l_srp_pay_grp_sd;
153 END IF;
154 -- end_date : compare rule 1 and rule 2
155 IF l_srp_roles_row.end_date IS NULL THEN
156 x_srp_plan_assigns_row.end_date := l_role_plans_row.end_date;
157 ELSIF l_role_plans_row.end_date IS NULL THEN
158 x_srp_plan_assigns_row.end_date := l_srp_roles_row.end_date;
159 ELSIF l_srp_roles_row.end_date > l_role_plans_row.end_date THEN
160 x_srp_plan_assigns_row.end_date := l_role_plans_row.end_date;
161 ELSE
162 x_srp_plan_assigns_row.end_date := l_srp_roles_row.end_date;
163 END IF;
164 -- end_date : compare with rule 3
165 IF x_srp_plan_assigns_row.end_date IS NULL THEN
166 x_srp_plan_assigns_row.end_date := l_srp_pay_grp_ed;
167 ELSIF l_srp_pay_grp_ed IS NOT NULL AND
168 x_srp_plan_assigns_row.end_date > l_srp_pay_grp_ed THEN
169 x_srp_plan_assigns_row.end_date := l_srp_pay_grp_ed;
170 END IF;
171
172 -- check if no intersection between these 3 start date/end date
173 -- If so, at INSERT() : no insert happened
174 -- at UPDATE() : delete this record since should not exist now.
175 IF (x_srp_plan_assigns_row.end_date IS NOT NULL) AND
176 (x_srp_plan_assigns_row.start_date > x_srp_plan_assigns_row.end_date) THEN
177 x_loading_status := 'CN_SPA_NO_INTERSECT_DATE';
178 GOTO end_of_valid_srp_plan_assign;
179 END IF;
180
181 -- check if duplicate
182 BEGIN
183 IF x_srp_plan_assigns_row.srp_plan_assign_id IS NULL THEN
184 SELECT 1 INTO l_dummy FROM dual
185 WHERE NOT EXISTS
186 (SELECT 1
187 FROM cn_srp_plan_assigns
188 WHERE role_plan_id = p_role_plan_id
189 AND srp_role_id = p_srp_role_id);
190 END IF ;
191 EXCEPTION
192 WHEN NO_DATA_FOUND THEN
193 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
194 FND_MESSAGE.Set_Name('CN', 'SRP_PLAN_ASSIGNED');
195 FND_MSG_PUB.Add;
196 END IF;
197 x_loading_status := 'SRP_PLAN_ASSIGNED';
198 RAISE FND_API.G_EXC_ERROR ;
199 END;
200 << end_of_valid_srp_plan_assign >>
201 NULL;
202 -- End of API body.
203 EXCEPTION
204 WHEN FND_API.G_EXC_ERROR THEN
205 x_return_status := FND_API.G_RET_STS_ERROR ;
206 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
208 x_loading_status := 'UNEXPECTED_ERR';
209 WHEN OTHERS THEN
210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
211 x_loading_status := 'UNEXPECTED_ERR';
212 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
213 THEN
214 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
215 END IF;
216 END valid_srp_plan_assign;
217
218 PROCEDURE business_event
219 (p_srp_plan_assign_id IN NUMBER,
220 p_srp_role_id IN NUMBER,
221 p_role_plan_id IN NUMBER,
222 p_salesrep_id IN NUMBER,
223 p_role_id IN NUMBER,
224 p_comp_plan_id IN NUMBER,
225 p_start_date IN DATE,
226 p_end_date IN DATE) IS
227
228 l_key VARCHAR2(80);
229 l_event_name VARCHAR2(80);
230 l_list wf_parameter_list_t;
231 BEGIN
232 -- p_operation = Add, Update, Remove
233 l_event_name := 'oracle.apps.cn.resource.PlanAssign.Add';
234
235 --Get the item key
236 -- for create - event_name || srp_paygroup_id
237 l_key := l_event_name || '-' || p_srp_plan_assign_id;
238
239 -- build parameter list as appropriate
240 wf_event.AddParameterToList('SRP_PLAN_ASSIGN_ID',p_srp_plan_assign_id,l_list);
241 wf_event.AddParameterToList('SRP_ROLE_ID',p_srp_role_id,l_list);
242 wf_event.AddParameterToList('ROLE_PLAN_ID',p_role_plan_id,l_list);
243 wf_event.AddParameterToList('COMP_PLAN_ID',p_comp_plan_id,l_list);
244 wf_event.AddParameterToList('ROLE_ID',p_role_id,l_list);
245 wf_event.AddParameterToList('SALESREP_ID',p_salesrep_id,l_list);
246 wf_event.AddParameterToList('START_DATE',p_start_date,l_list);
247 wf_event.AddParameterToList('END_DATE',p_end_date,l_list);
248
249 -- Raise Event
250 wf_event.raise
251 (p_event_name => l_event_name,
252 p_event_key => l_key,
253 p_parameters => l_list);
254
255 l_list.DELETE;
256 END business_event;
257
258
259 --| -----------------------------------------------------------------------+
260 --| Procedure : Create_Srp_Plan_Assigns
261 --| Desc : Procedure to create a new comp plan assignment to an salesrep
262 --| -----------------------------------------------------------------------+
263 PROCEDURE Create_Srp_Plan_Assigns
264 (p_api_version IN NUMBER,
265 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
266 p_commit IN VARCHAR2 := FND_API.G_FALSE,
267 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
268 x_return_status OUT NOCOPY VARCHAR2,
269 x_msg_count OUT NOCOPY NUMBER,
270 x_msg_data OUT NOCOPY VARCHAR2,
271 p_srp_role_id IN NUMBER,
272 p_role_plan_id IN NUMBER,
273 p_attribute_rec IN cn_global_var.attribute_rec_type := CN_GLOBAL_VAR.G_MISS_ATTRIBUTE_REC,
274 x_srp_plan_assign_id OUT NOCOPY NUMBER,
275 x_loading_status OUT NOCOPY VARCHAR2
276 ) IS
277
278 l_api_name CONSTANT VARCHAR2(30) := 'Create_Srp_Plan_Assigns';
279 l_api_version CONSTANT NUMBER := 1.0;
280 l_spa_row cn_srp_plan_assigns%ROWTYPE ;
281 l_role_id cn_roles.role_id%TYPE;
282 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
283
284 l_temp_count NUMBER;
285 l_temp_start_date DATE;
286 l_temp_end_date DATE;
287
288 CURSOR pg_cur(srp_id number, l_org_id number)
289 IS
290 select start_date, end_date
291 from cn_srp_pay_groups
292 where salesrep_id = srp_id
293 and org_id = l_org_id; -- MOAC
294
295 pg_cur_rec pg_cur%ROWTYPE;
296
297 BEGIN
298 -- Standard Start of API savepoint
299 SAVEPOINT Create_Srp_Plan_Assigns;
300 -- Standard call to check for call compatibility.
301 IF NOT FND_API.compatible_api_call
302 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
303 THEN
304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
305 END IF;
306 -- Initialize message list if p_init_msg_list is set to TRUE.
307 IF FND_API.to_Boolean( p_init_msg_list ) THEN
308 FND_MSG_PUB.initialize;
309 END IF;
310 -- Initialize API return status to success
311 x_return_status := FND_API.G_RET_STS_SUCCESS;
312 x_loading_status := 'CN_INSERTED';
313 -- API body
314 --
315 -- Valid compensation plan assignment
316 --
317 valid_srp_plan_assign
318 ( x_return_status => x_return_status,
319 x_msg_count => x_msg_count,
320 x_msg_data => x_msg_data,
321 p_srp_role_id => p_srp_role_id,
322 p_role_plan_id => p_role_plan_id,
323 x_srp_plan_assigns_row => l_spa_row,
324 x_role_id => l_role_id,
325 p_loading_status => x_loading_status,
326 x_loading_status => x_loading_status
327 );
328
329 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
330 RAISE FND_API.G_EXC_ERROR ;
331 ELSIF x_loading_status = 'CN_SPA_NO_INTERSECT_DATE' THEN
332 x_loading_status := 'CN_INSERTED';
333 GOTO end_of_create_srp_plan_assigns;
334 ELSE
335 -- Create comp plan assignment into cn_srp_plan_assigns
336 cn_srp_plan_assigns_pkg.insert_row
337 (x_srp_plan_assign_id => x_srp_plan_assign_id
338 ,x_srp_role_id => l_spa_row.srp_role_id
339 ,x_role_plan_id => l_spa_row.role_plan_id
340 ,x_salesrep_id => l_spa_row.salesrep_id
341 ,x_role_id => l_spa_row.role_id
342 ,x_comp_plan_id => l_spa_row.comp_plan_id
343 ,x_start_date => l_spa_row.start_date
344 ,x_end_date => l_spa_row.end_date
345 ,x_created_by => FND_GLOBAL.USER_ID
346 ,x_creation_date => sysdate
347 ,x_last_update_date => sysdate
348 ,x_last_updated_by => FND_GLOBAL.USER_ID
349 ,x_last_update_login => FND_GLOBAL.LOGIN_ID
350 ,x_attribute_category => p_attribute_rec.attribute_category
351 ,x_attribute1 => p_attribute_rec.attribute1
352 ,x_attribute2 => p_attribute_rec.attribute2
353 ,x_attribute3 => p_attribute_rec.attribute3
354 ,x_attribute4 => p_attribute_rec.attribute4
355 ,x_attribute5 => p_attribute_rec.attribute5
356 ,x_attribute6 => p_attribute_rec.attribute6
357 ,x_attribute7 => p_attribute_rec.attribute7
358 ,x_attribute8 => p_attribute_rec.attribute8
359 ,x_attribute9 => p_attribute_rec.attribute9
360 ,x_attribute10 => p_attribute_rec.attribute10
361 ,x_attribute11 => p_attribute_rec.attribute11
362 ,x_attribute12 => p_attribute_rec.attribute12
363 ,x_attribute13 => p_attribute_rec.attribute13
364 ,x_attribute14 => p_attribute_rec.attribute14
365 ,x_attribute15 => p_attribute_rec.attribute15
366 );
367
368 -- create business event
369 business_event
370 (p_srp_plan_assign_id => x_srp_plan_assign_id,
371 p_srp_role_id => l_spa_row.srp_role_id,
372 p_role_plan_id => l_spa_row.role_plan_id,
373 p_salesrep_id => l_spa_row.salesrep_id,
374 p_role_id => l_spa_row.role_id,
375 p_comp_plan_id => l_spa_row.comp_plan_id,
376 p_start_date => l_spa_row.start_date,
377 p_end_date => l_spa_row.end_date);
378
379 -- Check if there're any pay group assignments inside this time period
380
381
382 FOR pg_cur_rec IN pg_cur(l_spa_row.salesrep_id,
383 l_spa_row.org_id) LOOP
384 IF(pg_cur_rec.start_date<=l_spa_row.start_date) THEN
385 l_temp_start_date := l_spa_row.start_date;
386 ELSE
387 l_temp_start_date := pg_cur_rec.start_date;
388 END IF;
389
390 IF(nvl(pg_cur_rec.end_date,l_null_date) >= nvl(l_spa_row.end_date,l_null_date)) THEN
391 l_temp_end_date := l_spa_row.end_date;
392 ELSE
393 l_temp_end_date := pg_cur_rec.end_date;
394 END IF;
395
396 -- check intersect
397 IF l_temp_start_date <= nvl(l_temp_end_date, l_null_date) then
398 -- Create entry in cn_srp_periods
399 CN_SRP_PERIODS_PVT.Create_Srp_Periods
400 (p_api_version => 1.0,
401 x_return_status => x_return_status,
402 x_msg_count => x_msg_count,
403 x_msg_data => x_msg_data,
404 p_role_id => l_role_id,
405 p_comp_plan_id => l_spa_row.comp_plan_id,
406 p_salesrep_id => l_spa_row.salesrep_id,
407 p_start_date => l_temp_start_date,
408 p_end_date => l_temp_end_date,
409 x_loading_status => x_loading_status
410 );
411
412
413 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
414 RAISE FND_API.G_EXC_ERROR ;
415 END IF;
416 END IF;
417 END LOOP;
418
419 -- insert all child records
420 cn_srp_quota_assigns_pkg.insert_record
421 (x_srp_plan_assign_id => x_srp_plan_assign_id
422 ,x_quota_id => null);
423
424 END IF;
425 -- End of API body.
426 << end_of_create_srp_plan_assigns >>
427 NULL;
428 -- Standard check of p_commit.
429 IF FND_API.To_Boolean( p_commit ) THEN
430 COMMIT WORK;
431 END IF;
432 -- Standard call to get message count and if count is 1, get message info.
433 FND_MSG_PUB.Count_And_Get
434 (
435 p_count => x_msg_count ,
436 p_data => x_msg_data ,
437 p_encoded => FND_API.G_FALSE
438 );
439
440 EXCEPTION
441 WHEN FND_API.G_EXC_ERROR THEN
442 ROLLBACK TO Create_Srp_Plan_Assigns;
443 x_return_status := FND_API.G_RET_STS_ERROR ;
444 FND_MSG_PUB.Count_And_Get
445 (
446 p_count => x_msg_count ,
447 p_data => x_msg_data ,
448 p_encoded => FND_API.G_FALSE
449 );
450 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
451 ROLLBACK TO Create_Srp_Plan_Assigns;
452 x_loading_status := 'UNEXPECTED_ERR';
453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
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 WHEN OTHERS THEN
461 ROLLBACK TO Create_Srp_Plan_Assigns;
462 x_loading_status := 'UNEXPECTED_ERR';
463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
464 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
465 THEN
466 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
467 END IF;
468 FND_MSG_PUB.Count_And_Get
469 (
470 p_count => x_msg_count ,
471 p_data => x_msg_data ,
472 p_encoded => FND_API.G_FALSE
473 );
474 END Create_Srp_Plan_Assigns;
475
476 --| ----------------------------------------------------------------------+
477 --| Procedure : Update_Srp_Plan_Assigns
478 --| Desc : Procedure to update a comp plan assignment to an salesrep
479 --| ----------------------------------------------------------------------+
480
481 PROCEDURE Update_Srp_Plan_Assigns
482 (
483 p_api_version IN NUMBER,
484 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
485 p_commit IN VARCHAR2 := FND_API.G_FALSE,
486 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
487 x_return_status OUT NOCOPY VARCHAR2,
488 x_msg_count OUT NOCOPY NUMBER,
489 x_msg_data OUT NOCOPY VARCHAR2,
490 p_srp_role_id IN NUMBER,
491 p_role_plan_id IN NUMBER,
492 p_attribute_rec IN CN_GLOBAL_VAR.attribute_rec_type := CN_GLOBAL_VAR.G_MISS_ATTRIBUTE_REC,
493 x_loading_status OUT NOCOPY VARCHAR2
494 ) IS
495
496 l_api_name CONSTANT VARCHAR2(30) := 'Update_Srp_Plan_Assigns';
497 l_api_version CONSTANT NUMBER := 1.0;
498 l_spa_row cn_srp_plan_assigns%ROWTYPE ;
499 l_role_id cn_roles.role_id%TYPE;
500 l_old_start_date cn_srp_plan_assigns.start_date%TYPE;
501 l_old_end_date cn_srp_plan_assigns.end_date%TYPE;
502 l_srp_plan_assigns_id cn_srp_plan_assigns.srp_plan_assign_id%TYPE;
503
504 l_temp_count NUMBER;
505 l_temp_start_date DATE;
506 l_temp_end_date DATE;
507 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
508
509 CURSOR pg_cur(srp_id number, l_org_id number) IS
510 select start_date, end_date
511 from cn_srp_pay_groups
512 where salesrep_id = srp_id
513 and org_id = l_org_id; --MOAC
514
515 pg_cur_rec pg_cur%ROWTYPE;
516 BEGIN
517 -- Standard Start of API savepoint
518 SAVEPOINT Update_Srp_Plan_Assigns;
519 -- Standard call to check for call compatibility.
520 IF NOT FND_API.compatible_api_call
521 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
522 THEN
523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
524 END IF;
525 -- Initialize message list if p_init_msg_list is set to TRUE.
526 IF FND_API.to_Boolean( p_init_msg_list ) THEN
527 FND_MSG_PUB.initialize;
528 END IF;
529 -- Initialize API return status to success
530 x_return_status := FND_API.G_RET_STS_SUCCESS;
531 x_loading_status := 'CN_UPDATED';
532 -- API body
533 -- Check old rec exist in cn_srp_plan_assigns
534 BEGIN
535 SELECT * INTO l_spa_row
536 FROM cn_srp_plan_assigns
537 WHERE srp_role_id = p_srp_role_id
538 AND role_plan_id = p_role_plan_id;
539 EXCEPTION
540 WHEN NO_DATA_FOUND THEN
541 -- CN_SRP_PLAN_ASSIGNS_NOT_EXIST, create new record
542 CN_SRP_PLAN_ASSIGNS_PVT.Create_Srp_Plan_Assigns
543 (p_api_version => 1.0,
544 x_return_status => x_return_status,
545 x_msg_count => x_msg_count,
546 x_msg_data => x_msg_data,
547 p_srp_role_id => p_srp_role_id,
548 p_role_plan_id => p_role_plan_id,
549 x_srp_plan_assign_id => l_srp_plan_assigns_id,
550 x_loading_status => x_loading_status
551 );
552 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
553 RAISE FND_API.G_EXC_ERROR ;
554 ELSE
555 GOTO end_of_update_srp_plan_assigns;
556 END IF;
557 END;
558 l_old_start_date := l_spa_row.start_date;
559 l_old_end_date := l_spa_row.end_date;
560 --
561 -- Valid compensation plan assignment
562 --
563 valid_srp_plan_assign
564 ( x_return_status => x_return_status,
565 x_msg_count => x_msg_count,
566 x_msg_data => x_msg_data,
567 p_srp_role_id => p_srp_role_id,
568 p_role_plan_id => p_role_plan_id,
569 x_srp_plan_assigns_row => l_spa_row,
570 x_role_id => l_role_id,
571 p_loading_status => x_loading_status,
572 x_loading_status => x_loading_status
573 );
574 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
575 RAISE FND_API.G_EXC_ERROR ;
576 ELSIF x_loading_status = 'CN_SPA_NO_INTERSECT_DATE' THEN
577 -- Delete entry in cn_srp_plan_assigns
578 Delete_Srp_Plan_Assigns
579 (p_api_version => 1.0,
580 x_return_status => x_return_status,
581 x_msg_count => x_msg_count,
582 x_msg_data => x_msg_data,
583 p_srp_role_id => p_srp_role_id,
584 p_role_plan_id => p_role_plan_id,
585 x_loading_status => x_loading_status
586 );
587 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
588 RAISE FND_API.G_EXC_ERROR ;
589 ELSE
590 x_loading_status := 'CN_UPDATED';
591 END IF;
592 ELSE
593 -- Update comp plan assignment into cn_srp_plan_assigns
594 cn_srp_plan_assigns_pkg.update_row
595 (x_srp_plan_assign_id => l_spa_row.srp_plan_assign_id
596 ,x_srp_role_id => l_spa_row.srp_role_id
597 ,x_role_plan_id => l_spa_row.role_plan_id
598 ,x_salesrep_id => l_spa_row.salesrep_id
599 ,x_role_id => l_spa_row.role_id
600 ,x_comp_plan_id => l_spa_row.comp_plan_id
601 ,x_start_date => l_spa_row.start_date
602 ,x_end_date => l_spa_row.end_date
603 ,x_last_update_date => sysdate
604 ,x_last_updated_by => fnd_global.user_id
605 ,x_last_update_login => fnd_global.login_id
606 ,x_attribute_category => p_attribute_rec.attribute_category
607 ,x_attribute1 => p_attribute_rec.attribute1
608 ,x_attribute2 => p_attribute_rec.attribute2
609 ,x_attribute3 => p_attribute_rec.attribute3
610 ,x_attribute4 => p_attribute_rec.attribute4
611 ,x_attribute5 => p_attribute_rec.attribute5
612 ,x_attribute6 => p_attribute_rec.attribute6
613 ,x_attribute7 => p_attribute_rec.attribute7
614 ,x_attribute8 => p_attribute_rec.attribute8
615 ,x_attribute9 => p_attribute_rec.attribute9
616 ,x_attribute10 => p_attribute_rec.attribute10
617 ,x_attribute11 => p_attribute_rec.attribute11
618 ,x_attribute12 => p_attribute_rec.attribute12
619 ,x_attribute13 => p_attribute_rec.attribute13
620 ,x_attribute14 => p_attribute_rec.attribute14
621 ,x_attribute15 => p_attribute_rec.attribute15
622 );
623 -- Create new entry into cn_srp_periods if extend date range
624 IF (l_spa_row.start_date < l_old_start_date) OR
625 ( (Nvl(l_old_end_date,FND_API.G_MISS_DATE) <>
626 Nvl(l_spa_row.end_date,FND_API.G_MISS_DATE)) AND
627 ( (l_spa_row.end_date IS NULL) OR
628 ((l_spa_row.end_date IS NOT NULL) AND (l_old_end_date IS NOT NULL)
629 AND (l_old_end_date < l_spa_row.end_date)) )
630 ) THEN
631
632 FOR pg_cur_rec IN pg_cur(l_spa_row.salesrep_id,
633 l_spa_row.org_id) LOOP
634 IF(pg_cur_rec.start_date<=l_spa_row.start_date) THEN
635 l_temp_start_date := l_spa_row.start_date;
636 ELSE
637 l_temp_start_date := pg_cur_rec.start_date;
638 END IF;
639
640 IF(nvl(pg_cur_rec.end_date,l_null_date) >= nvl(l_spa_row.end_date,l_null_date)) THEN
641 l_temp_end_date := l_spa_row.end_date;
642 ELSE
643 l_temp_end_date := pg_cur_rec.end_date;
644 END IF;
645
646 IF l_temp_start_date <= nvl(l_temp_end_date, l_null_date) THEN
647 -- Create entry in cn_srp_periods
648 CN_SRP_PERIODS_PVT.Create_Srp_Periods
649 (p_api_version => 1.0,
650 x_return_status => x_return_status,
651 x_msg_count => x_msg_count,
652 x_msg_data => x_msg_data,
653 p_role_id => l_role_id,
654 p_comp_plan_id => l_spa_row.comp_plan_id,
655 p_salesrep_id => l_spa_row.salesrep_id,
656 p_start_date => l_temp_start_date,
657 p_end_date => l_temp_end_date,
658 x_loading_status => x_loading_status
659 );
660 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
661 RAISE FND_API.G_EXC_ERROR ;
662 END IF;
663 END IF;
664 END LOOP;
665 END IF;
666 END IF;
667 -- End of API body.
668 << end_of_update_srp_plan_assigns >>
669 NULL;
670 -- Standard check of p_commit.
671 IF FND_API.To_Boolean( p_commit ) THEN
672 COMMIT WORK;
673 END IF;
674 -- Standard call to get message count and if count is 1, get message info.
675 FND_MSG_PUB.Count_And_Get
676 (
677 p_count => x_msg_count ,
678 p_data => x_msg_data ,
679 p_encoded => FND_API.G_FALSE
680 );
681
682 EXCEPTION
683 WHEN FND_API.G_EXC_ERROR THEN
684 ROLLBACK TO Update_Srp_Plan_Assigns;
685 x_return_status := FND_API.G_RET_STS_ERROR ;
686 FND_MSG_PUB.Count_And_Get
687 (
688 p_count => x_msg_count ,
689 p_data => x_msg_data ,
690 p_encoded => FND_API.G_FALSE
691 );
692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
693 ROLLBACK TO Update_Srp_Plan_Assigns;
694 x_loading_status := 'UNEXPECTED_ERR';
695 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
696 FND_MSG_PUB.Count_And_Get
697 (
698 p_count => x_msg_count ,
699 p_data => x_msg_data ,
700 p_encoded => FND_API.G_FALSE
701 );
702 WHEN OTHERS THEN
703 ROLLBACK TO Update_Srp_Plan_Assigns;
704 x_loading_status := 'UNEXPECTED_ERR';
705 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
706 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
707 THEN
708 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
709 END IF;
710 FND_MSG_PUB.Count_And_Get
711 (
712 p_count => x_msg_count ,
713 p_data => x_msg_data ,
714 p_encoded => FND_API.G_FALSE
715 );
716 END Update_Srp_Plan_Assigns;
717
718 --| -----------------------------------------------------------------------+
719 --| Procedure : Delete_Srp_Plan_Assigns
720 --| Desc : Procedure to create a new comp plan assignment to an salesrep
721 --| -----------------------------------------------------------------------+
722
723 PROCEDURE Delete_Srp_Plan_Assigns
724 (
725 p_api_version IN NUMBER,
726 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
727 p_commit IN VARCHAR2 := FND_API.G_FALSE,
728 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
729 x_return_status OUT NOCOPY VARCHAR2,
730 x_msg_count OUT NOCOPY NUMBER,
731 x_msg_data OUT NOCOPY VARCHAR2,
732 p_srp_role_id IN NUMBER,
733 p_role_plan_id IN NUMBER,
734 x_loading_status OUT NOCOPY VARCHAR2
735 ) IS
736
737 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Srp_Plan_Assigns';
738 l_api_version CONSTANT NUMBER := 1.0;
739 l_srp_plan_assign_id cn_srp_plan_assigns.srp_plan_assign_id%TYPE;
740
741 BEGIN
742 -- Standard Start of API savepoint
743 SAVEPOINT Delete_Srp_Plan_Assigns;
744 -- Standard call to check for call compatibility.
745 IF NOT FND_API.compatible_api_call
746 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
747 THEN
748 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
749 END IF;
750 -- Initialize message list if p_init_msg_list is set to TRUE.
751 IF FND_API.to_Boolean( p_init_msg_list ) THEN
752 FND_MSG_PUB.initialize;
753 END IF;
754 -- Initialize API return status to success
755 x_return_status := FND_API.G_RET_STS_SUCCESS;
756 x_loading_status := 'CN_DELETED';
757 -- API body
758 -- Check if record exist in db
759 BEGIN
760 SELECT srp_plan_assign_id INTO l_srp_plan_assign_id
761 FROM cn_srp_plan_assigns
762 WHERE srp_role_id = p_srp_role_id
763 AND role_plan_id = p_role_plan_id;
764 EXCEPTION
765 WHEN NO_DATA_FOUND THEN
766 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
767 -- FND_MESSAGE.Set_Name('CN', 'CN_SRP_PLAN_ASSIGNS_NOT_EXIST');
768 -- FND_MSG_PUB.Add;
769 -- END IF;
770 -- x_loading_status := 'CN_SRP_PLAN_ASSIGNS_NOT_EXIST';
771 -- RAISE FND_API.G_EXC_ERROR ;
772
773 -- CN_SRP_PLAN_ASSIGNS_NOT_EXIST, nothing to delete, exit api
774 GOTO end_of_delete_srp_plan_assigns;
775 END;
776 -- Delete detail record
777 cn_srp_quota_assigns_pkg.delete_record
778 (x_srp_plan_assign_id => l_srp_plan_assign_id
779 ,x_quota_id => null);
780
781 -- Delete comp plan assignment from cn_srp_plan_assigns
782 cn_srp_plan_assigns_pkg.delete_row
783 (x_srp_plan_assign_id => l_srp_plan_assign_id);
784 -- End of API body.
785 << end_of_delete_srp_plan_assigns >>
786 NULL;
787 -- Standard check of p_commit.
788 IF FND_API.To_Boolean( p_commit ) THEN
789 COMMIT WORK;
790 END IF;
791 -- Standard call to get message count and if count is 1, get message info.
792 FND_MSG_PUB.Count_And_Get
793 (
794 p_count => x_msg_count ,
795 p_data => x_msg_data ,
796 p_encoded => FND_API.G_FALSE
797 );
798
799 EXCEPTION
800 WHEN FND_API.G_EXC_ERROR THEN
801 ROLLBACK TO Delete_Srp_Plan_Assigns;
802 x_return_status := FND_API.G_RET_STS_ERROR ;
803 FND_MSG_PUB.Count_And_Get
804 (
805 p_count => x_msg_count ,
806 p_data => x_msg_data ,
807 p_encoded => FND_API.G_FALSE
808 );
809 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
810 ROLLBACK TO Delete_Srp_Plan_Assigns;
811 x_loading_status := 'UNEXPECTED_ERR';
812 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
813 FND_MSG_PUB.Count_And_Get
814 (
815 p_count => x_msg_count ,
816 p_data => x_msg_data ,
817 p_encoded => FND_API.G_FALSE
818 );
819 WHEN OTHERS THEN
820 ROLLBACK TO Delete_Srp_Plan_Assigns;
821 x_loading_status := 'UNEXPECTED_ERR';
822 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
823 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
824 THEN
825 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
826 END IF;
827 FND_MSG_PUB.Count_And_Get
828 (
829 p_count => x_msg_count ,
830 p_data => x_msg_data ,
831 p_encoded => FND_API.G_FALSE
832 );
833 END Delete_Srp_Plan_Assigns;
834
835 END CN_SRP_PLAN_ASSIGNS_PVT ;