[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_PAYGROUP_PVT
Source
1 PACKAGE BODY CN_SRP_PAYGROUP_PVT as
2 -- $Header: cnvsdpgb.pls 120.16 2006/09/28 07:03:35 chanthon noship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_Srp_PayGroup_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvsdgpb.pls';
6
7 ---------------------------------------------------------------------+
8 -- Procedure : Validate_Assignment
9 -- Description : Procedure to validate the date range for assignment of
10 -- a salesperson to a paygroup
11 ---------------------------------------------------------------------+
12
13 PROCEDURE Validate_Assignment
14 (
15 x_return_status OUT NOCOPY VARCHAR2 ,
16 x_msg_count OUT NOCOPY NUMBER ,
17 x_msg_data OUT NOCOPY VARCHAR2,
18 p_salesrep_id IN NUMBER,
19 p_org_id IN NUMBER,
20 p_start_date IN DATE,
21 p_end_date IN DATE,
22 p_pay_group_id IN NUMBER,
23 p_srp_pay_group_id IN NUMBER,
24 p_loading_status IN VARCHAR2,
25 x_loading_status OUT NOCOPY VARCHAR2,
26 x_status OUT NOCOPY VARCHAR2
27 ) IS
28
29 l_count NUMBER := 0;
30 l_api_name CONSTANT VARCHAR2(30) := 'Validate_assignment';
31 l_dummy NUMBER;
32 l_srp_start_date DATE;
33 l_srp_end_date DATE;
34 l_pp_start_date DATE;
35 l_pp_end_date DATE;
36 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
37
38 BEGIN
39
40 --
41 -- Initialize API return status to success
42 --
43 x_return_status := FND_API.G_RET_STS_SUCCESS;
44 x_loading_status := p_loading_status ;
45
46 -- Check if already exist( duplicate assigned,unique key violation check)
47 SELECT COUNT(1) INTO l_dummy
48 FROM cn_srp_pay_groups_all
49 WHERE salesrep_id = p_salesrep_id
50 AND pay_group_id = p_pay_group_id
51 AND start_date = p_start_date
52 AND ( (end_date = p_end_date) OR
53 (end_date IS NULL AND p_end_date IS NULL) )
54 AND ((p_srp_pay_group_id IS NOT NULL AND
55 srp_pay_group_id<> p_srp_pay_group_id)
56 OR
57 (p_srp_pay_group_id IS NULL));
58
59 IF l_dummy > 0 THEN
60 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
61 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PAY_GRP_EXIST');
62 FND_MSG_PUB.Add;
63 END IF;
64 x_loading_status := 'CN_SRP_PAY_GRP_EXIST';
65 RAISE FND_API.G_EXC_ERROR ;
66 END IF;
67
68
69 -- Check if Salesrep active
70 -- Cannot assign a pmt plan to an inactive rep because we need the
71 -- cn_srp_periods in order to create cn_srp_period_quotas. It's also a
72 -- reasonable business requirement
73 SELECT start_date_active, end_date_active
74 INTO l_srp_start_date, l_srp_end_date
75 FROM cn_salesreps
76 WHERE salesrep_id = p_salesrep_id
77 AND org_id = p_org_id;
78
79 IF l_srp_start_date IS NULL THEN
80 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
81 FND_MESSAGE.SET_NAME('CN','SRP_MUST_ACTIVATE_REP');
82 FND_MSG_PUB.Add;
83 END IF;
84 x_loading_status := 'SRP_MUST_ACTIVATE_REP';
85 RAISE FND_API.G_EXC_ERROR ;
86 END IF;
87
88
89 -- Check if date range invalid
90 -- will check : if start_date is null
91 -- if start_date/end_date is missing
92 -- if start_date > end_date
93 IF ( (cn_api.invalid_date_range
94 (p_start_date => p_start_date,
95 p_end_date => p_end_date,
96 p_end_date_nullable => FND_API.G_TRUE,
97 p_loading_status => x_loading_status,
98 x_loading_status => x_loading_status)) = FND_API.G_TRUE ) THEN
99 RAISE FND_API.G_EXC_ERROR ;
100 END IF;
101
102
103 --
104 --
105 -- Validate Rule :Start or end date is outside of the processing
106 -- period range define in rep detail
107 --
108 -- Oct 26 1999 ACHUNG
109 -- Change: srp_pmt_plan.end_date not forced.
110 -- if srp_pmt_plan.end_date is null, no need to check between srp and pmt
111 -- start date/end date range
112 --
113 IF ( (
114 ( l_srp_start_date IS NOT NULL) AND ( l_srp_end_date IS NOT NULL)
115 AND(
116 ( (p_start_date NOT BETWEEN l_srp_start_date AND l_srp_end_date)AND ((p_end_date IS NULL) OR (p_end_date > l_srp_end_date)))
117 OR (p_start_date NOT BETWEEN l_srp_start_date AND l_srp_end_date)
118 OR ((p_end_date IS NULL) OR (p_end_date > l_srp_end_date))
119 )
120 )--End of first condition in IF
121
122 OR (
123 ( l_srp_start_date IS NOT NULL) AND ( l_srp_end_date IS NULL) AND
124 (p_start_date < l_srp_start_date )
125 ) --ENd of 2nd condition in IF
126
127 ) -- end of IF
128
129 THEN
130 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
131 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPP_PG_PRDS_NI_SRP_PRDS');
132 FND_MSG_PUB.Add;
133 END IF;
134 x_loading_status := 'CN_SPP_PG_PRDS_NI_SRP_PRDS';
135 RAISE FND_API.G_EXC_ERROR ;
136 END IF;
137
138
139 --
140 -- Validate Rule :Start or end date is outside of the processing
141 -- period range define in payment plan definition
142 --
143 -- Oct 26 1999 ACHUNG
144 -- Change: srp_pmt_plan.end_date not forced.
145 -- if srp_pmt_plan.end_date is null, no need to check between srp and pmt
146 -- start date/end date range
147 --
148
149 SELECT start_date, end_date
150 INTO l_pp_start_date, l_pp_end_date
151 FROM cn_pay_groups_all
152 WHERE pay_group_id = p_pay_group_id;
153 IF ( (
154 ( l_pp_start_date IS NOT NULL) AND ( l_pp_end_date IS NOT NULL)
155 AND(
156 ( (p_start_date NOT BETWEEN l_pp_start_date AND l_pp_end_date)AND ((p_end_date IS NULL)OR (p_end_date > l_pp_end_date)))
157 OR (p_start_date NOT BETWEEN l_pp_start_date AND l_pp_end_date)
158 OR ((p_end_date IS NULL) OR (p_end_date > l_pp_end_date))
159 )
160 )--End of first condition in IF
161
162 OR (
163 ( l_pp_start_date IS NOT NULL) AND ( l_pp_end_date IS NULL) AND
164 (p_start_date < l_pp_start_date)
165 ) --ENd of 2nd condition in IF
166
167 ) -- end of IF
168
169
170 THEN
171 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
172 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPP_PRDS_NI_PAY_GRP_PRDS');
173 FND_MSG_PUB.Add;
174 END IF;
175 x_loading_status := 'CN_SPP_PRDS_NI_PAY_GRP_PRDS';
176 RAISE FND_API.G_EXC_ERROR ;
177 END IF;
178
179 --
180 -- Check if the current assignment dates do not fit within the effectivity of the
181 -- pay group.
182 --
183 SELECT COUNT(1)
184 INTO l_count
185 FROM cn_pay_groups_all
186 WHERE (( p_start_date NOT BETWEEN start_date AND end_date )
187 OR (p_end_date NOT BETWEEN start_date AND end_date))
188 AND pay_group_id = p_pay_group_id;
189
190 IF l_count <> 0
191 THEN
192 --Error condition
193 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
194 THEN
195 fnd_message.set_name('CN', 'CN_INVALID_SRP_PGRP_ASGN_DT');
196 fnd_msg_pub.add;
197 END IF;
198
199 x_loading_status := 'CN_INVALID_SRP_PGRP_ASGN_DT';
200 RAISE FND_API.G_EXC_ERROR;
201 END IF;
202
203 --
204 -- Check for overlapping assignments
205 --
206 SELECT count(1)
207 INTO l_count
208 FROM cn_srp_pay_groups_all
209 WHERE p_start_date between start_date AND Nvl(end_date, p_start_date)
210 AND salesrep_id = p_salesrep_id
211 AND org_id = p_org_id
212 AND srp_pay_group_id <> p_srp_pay_group_id;
213
214 IF l_count <> 0
215 THEN
216 --Error condition
217 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
218 THEN
219 fnd_message.set_name('CN', 'CN_OVERLAP_SRP_PGRP_ASGN');
220 fnd_msg_pub.add;
221 END IF;
222
223 x_loading_status := 'CN_OVERLAP_SRP_PGRP_ASGN';
224 RAISE FND_API.G_EXC_ERROR;
225 END IF;
226
227 SELECT count(1)
228 INTO l_count
229 FROM cn_srp_pay_groups_all
230 WHERE Nvl(p_end_date, l_null_date) between start_date
231 AND Nvl(end_date, Nvl(p_end_date, l_null_date))
232 AND salesrep_id = p_salesrep_id
233 AND org_id = p_org_id
234 AND srp_pay_group_id <> p_srp_pay_group_id;
235
236
237 IF l_count <> 0
238 THEN
239 --Error condition
240 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
241 THEN
242 fnd_message.set_name('CN', 'CN_OVERLAP_SRP_PGRP_ASGN');
243 fnd_msg_pub.add;
244 END IF;
245 x_loading_status := 'CN_OVERLAP_SRP_PGRP_ASGN';
246 RAISE FND_API.G_EXC_ERROR;
247 END IF;
248
249 SELECT count(1)
250 INTO l_count
251 FROM cn_srp_pay_groups_all
252 WHERE salesrep_id = p_salesrep_id
253 AND org_id = p_org_id
254 AND p_start_date <= start_date
255 AND Nvl(p_end_date, l_null_date) >= Nvl(end_date, l_null_date)
256 AND srp_pay_group_id <> p_srp_pay_group_id;
257
258
259 IF l_count <> 0
260 THEN
261 --Error condition
262 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
263 THEN
264 fnd_message.set_name('CN', 'CN_OVERLAP_SRP_PGRP_ASGN');
265 fnd_msg_pub.add;
266 END IF;
267
268 x_loading_status := 'CN_OVERLAP_SRP_PGRP_ASGN';
269 RAISE FND_API.G_EXC_ERROR;
270 END IF;
271
272 -- End of Validate Assignment
273 EXCEPTION
274
275 WHEN FND_API.G_EXC_ERROR THEN
276 x_return_status := FND_API.G_RET_STS_ERROR ;
277
278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
279 x_loading_status := 'UNEXPECTED_ERR';
280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
281
282 WHEN OTHERS THEN
283 x_loading_status := 'UNEXPECTED_ERR';
284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
285 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
286 THEN
287 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
288 END IF;
289
290 END Validate_Assignment;
291
292
293 -----------------------------------------------------------------------+
294 -- Procedure : Validate_end_date
295 -- Description : Procedure to validate that the end date coincides with
296 -- the end date of a pay period
297 -----------------------------------------------------------------------+
298
299 PROCEDURE Validate_end_date
300 (
301 x_return_status OUT NOCOPY VARCHAR2 ,
302 x_msg_count OUT NOCOPY NUMBER ,
303 x_msg_data OUT NOCOPY VARCHAR2,
304 p_salesrep_id IN NUMBER,
305 p_org_id IN NUMBER,
306 p_assign_end_date IN DATE,
307 p_loading_status IN VARCHAR2,
308 x_loading_status OUT NOCOPY VARCHAR2,
309 x_status OUT NOCOPY VARCHAR2
310 ) IS
311
312 l_count NUMBER := 0;
313 l_count2 NUMBER := 0;
314 l_api_name CONSTANT VARCHAR2(30) := 'Validate_end_date';
315
316
317
318 BEGIN
319
320 --
321 -- Initialize API return status to success
322 --
323 x_return_status := FND_API.G_RET_STS_SUCCESS;
324 x_loading_status := p_loading_status ;
325
326
327 /* cn_posting_details is obsolete - we now validate against pmt worksheets
328
329 -- Added new checking logic below
330 -- Check if any of the periods after this new assignment end date has been used
331 -- in cn_posting_details, if so error.
332 BEGIN
333 select 1 into l_count from dual where not exists
334 (select 1 from cn_srp_periods_all csp, cn_posting_details_sum_all cpd
335 where cpd.credited_salesrep_id = p_salesrep_id
336 AND cpd.org_id = p_org_id
337 AND cpd.pay_period_id = csp.period_id
338 AND csp.salesrep_id = cpd.credited_salesrep_id
339 AND csp.end_date > p_assign_end_date);
340 EXCEPTION
341 WHEN NO_DATA_FOUND THEN
342 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
343 THEN
344 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_PGRP_ASGN_END_DT');
345 FND_MSG_PUB.Add;
346 END IF;
347 x_loading_status := 'CN_INVALID_PGRP_ASGN_END_DT';
348 RAISE FND_API.G_EXC_ERROR ;
349 END;
350 */
351
352
353 EXCEPTION
354
355 WHEN FND_API.G_EXC_ERROR THEN
356 x_return_status := FND_API.G_RET_STS_ERROR ;
357
358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
359 x_loading_status := 'UNEXPECTED_ERR';
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
361
362 WHEN OTHERS THEN
363 x_loading_status := 'UNEXPECTED_ERR';
364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
365 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
366 THEN
367 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
368 END IF;
369
370 END Validate_end_date;
371
372
373
374 -- --------------------------------------------------------------------------*
375 -- Procedure: srp_plan_assignment_for_delete
376 -- --------------------------------------------------------------------------*
377 PROCEDURE srp_plan_assignment_for_delete
378 (p_role_id IN cn_roles.role_id%TYPE,
379 p_role_plan_id IN cn_role_plans.role_plan_id%TYPE,
380 p_salesrep_id IN cn_salesreps.salesrep_id%TYPE,
381 p_org_id IN cn_salesreps.org_id%TYPE,
382 x_return_status OUT NOCOPY VARCHAR2,
383 p_loading_status IN VARCHAR2,
384 x_loading_status OUT NOCOPY VARCHAR2 ) IS
385
386 CURSOR l_cur IS
387 SELECT srp_role_id
388 FROM cn_srp_roles
389 WHERE role_id = p_role_id
390 and salesrep_id =p_salesrep_id
391 AND org_id = p_org_id;
392
393
394 l_rec l_cur%ROWTYPE;
395
396 l_return_status VARCHAR2(2000);
397 l_msg_count NUMBER;
398 l_msg_data VARCHAR2(2000);
399 l_srp_plan_assign_id cn_srp_plan_assigns.srp_plan_assign_id%TYPE;
400 l_loading_status VARCHAR2(2000);
401
402 BEGIN
403
404 -- Initialize API return status to success
405 x_return_status := FND_API.G_RET_STS_SUCCESS;
406 x_loading_status := p_loading_status;
407
408 FOR l_rec IN l_cur
409 LOOP
410
411 cn_srp_plan_assigns_pvt.delete_srp_plan_assigns
412 (
413 p_api_version => 1.0,
414 x_return_status => l_return_status,
415 x_msg_count => l_msg_count,
416 x_msg_data => l_msg_data,
417 p_srp_role_id => l_rec.srp_role_id,
418 p_role_plan_id => p_role_plan_id,
419 x_loading_status => l_loading_status);
420
421 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
422 x_return_status := l_return_status;
423 x_loading_status := l_loading_status;
424 EXIT;
425 END IF;
426
427 END LOOP;
428 END srp_plan_assignment_for_delete;
429
430
431 PROCEDURE business_event
432 (p_operation IN VARCHAR2,
433 p_paygroup_assign_rec IN paygroup_assign_rec) IS
434
435 l_key VARCHAR2(80);
436 l_event_name VARCHAR2(80);
437 l_list wf_parameter_list_t;
438 BEGIN
439 -- p_operation = Add, Update, Remove
440 l_event_name := 'oracle.apps.cn.resource.PayGroupAssign.' || p_operation;
441
442 --Get the item key
443 -- for create - event_name || srp_paygroup_id
444 -- for update - event_name || srp_paygroup_id || ovn
445 -- for delete - event_name || srp_paygroup_id
446 l_key := l_event_name || '-' || p_paygroup_assign_rec.srp_pay_group_id;
447
448 -- build parameter list as appropriate
449 IF (p_operation = 'Add') THEN
450 wf_event.AddParameterToList('SALESREP_ID',p_paygroup_assign_rec.salesrep_id,l_list);
451 wf_event.AddParameterToList('PAY_GROUP_ID',p_paygroup_assign_rec.pay_group_id,l_list);
452 wf_event.AddParameterToList('START_DATE',p_paygroup_assign_rec.assignment_start_date,l_list);
453 wf_event.AddParameterToList('END_DATE',p_paygroup_assign_rec.assignment_end_date,l_list);
454 wf_event.AddParameterToList('LOCK_FLAG',p_paygroup_assign_rec.lock_flag,l_list);
455 ELSIF (p_operation = 'Update') THEN
456 l_key := l_key || '-' || p_paygroup_assign_rec.object_version_number;
457 wf_event.AddParameterToList('SRP_PAY_GROUP_ID',p_paygroup_assign_rec.srp_pay_group_id,l_list);
458 wf_event.AddParameterToList('SALESREP_ID',p_paygroup_assign_rec.salesrep_id,l_list);
459 wf_event.AddParameterToList('PAY_GROUP_ID',p_paygroup_assign_rec.pay_group_id,l_list);
460 wf_event.AddParameterToList('START_DATE',p_paygroup_assign_rec.assignment_start_date,l_list);
461 wf_event.AddParameterToList('END_DATE',p_paygroup_assign_rec.assignment_end_date,l_list);
462 wf_event.AddParameterToList('LOCK_FLAG',p_paygroup_assign_rec.lock_flag,l_list);
463 ELSIF (p_operation = 'Remove') THEN
464 wf_event.AddParameterToList('SRP_PAY_GROUP_ID',p_paygroup_assign_rec.srp_pay_group_id,l_list);
465 END IF;
466
467 -- Raise Event
468 wf_event.raise
469 (p_event_name => l_event_name,
470 p_event_key => l_key,
471 p_parameters => l_list);
472
473 l_list.DELETE;
474 END business_event;
475
476
477 -- --------------------------------------------------------------------------*
478 -- Procedure: Create_Srp_Pay_Group
479 -- --------------------------------------------------------------------------*
480 PROCEDURE Create_Srp_Pay_Group
481 ( p_api_version IN NUMBER ,
482 p_init_msg_list IN VARCHAR2,
483 p_commit IN VARCHAR2,
484 p_validation_level IN NUMBER,
485 x_return_status OUT NOCOPY VARCHAR2 ,
486 x_loading_status OUT NOCOPY VARCHAR2 ,
487 x_msg_count OUT NOCOPY NUMBER ,
488 x_msg_data OUT NOCOPY VARCHAR2 ,
489 p_paygroup_assign_rec IN OUT NOCOPY PayGroup_assign_rec
490 ) IS
491
492 l_api_name CONSTANT VARCHAR2(30) := 'Create_Srp_Pay_Group';
493 l_api_version CONSTANT NUMBER := 1.0;
494 l_srp_pay_group_id cn_srp_pay_groups.srp_pay_group_id%TYPE;
495 l_role_id cn_roles.role_id%TYPE;
496 l_loading_status VARCHAR2(2000);
497 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
498 l_status VARCHAR2(30);
499 l_employee_number cn_salesreps.employee_number%TYPE;
500 l_employee_name cn_salesreps.name%TYPE;
501 l_pay_group_name cn_pay_groups.name%TYPE;
502
503 CURSOR get_roles (p_salesrep_id NUMBER) IS
504 SELECT role_id, srp_role_id,start_date, nvl(end_date,l_null_date) end_date
505 FROM cn_srp_roles
506 WHERE salesrep_id = p_salesrep_id
507 AND org_id = p_paygroup_assign_rec.org_id;
508
509 CURSOR get_role_plans(p_role_id NUMBER) IS
510 SELECT role_plan_id
511 FROM cn_role_plans
512 WHERE role_id = p_role_id
513 AND org_id = p_paygroup_assign_rec.org_id;
514
515 CURSOR get_plan_assigns
516 (p_role_id NUMBER,
517 p_salesrep_id NUMBER) IS
518 SELECT comp_plan_id,
519 start_date,
520 end_date
521 FROM cn_srp_plan_assigns_all
522 WHERE role_id = p_role_id
523 AND salesrep_id = p_salesrep_id
524 AND org_id = p_paygroup_assign_rec.org_id;
525
526 BEGIN
527 -- Standard Start of API savepoint
528
529 SAVEPOINT Create_Srp_Pay_Group;
530
531 -- Standard call to check for call compatibility.
532
533 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
534 p_api_version ,
535 l_api_name ,
536 G_PKG_NAME )
537 THEN
538 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
539 END IF;
540
541 -- Initialize message list if p_init_msg_list is set to TRUE.
542 IF FND_API.to_Boolean( p_init_msg_list ) THEN
543 FND_MSG_PUB.initialize;
544 END IF;
545
546 -- Initialize API return status to success
547 x_return_status := FND_API.G_RET_STS_SUCCESS;
548 x_loading_status := 'CN_CREATED';
549
550 SELECT cn_srp_pay_groups_s.NEXTVAL
551 INTO l_srp_pay_group_id
552 FROM dual;
553
554 Validate_assignment
555 (x_return_status => x_return_status,
556 x_msg_count => x_msg_count,
557 x_msg_data => x_msg_data,
558 p_salesrep_id => p_paygroup_assign_rec.salesrep_id,
559 p_org_id => p_paygroup_assign_rec.org_id,
560 p_start_date => p_paygroup_assign_rec.assignment_start_date,
561 p_end_date => p_paygroup_assign_rec.assignment_end_date,
562 p_pay_group_id => p_paygroup_assign_rec.pay_group_id,
563 p_srp_pay_group_id=> l_srp_pay_group_id,
564 p_loading_status => x_loading_status,
565 x_loading_status => x_loading_status,
566 x_status => l_status );
567
568 SELECT name, employee_number
569 INTO l_employee_name, l_employee_number
570 FROM cn_salesreps
571 WHERE salesrep_id = p_paygroup_assign_rec.salesrep_id
572 AND org_id = p_paygroup_assign_rec.org_id;
573
574 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
575 THEN
576 RAISE FND_API.G_EXC_ERROR ;
577 ELSIF x_loading_status = 'CN_INVALID_SRP_PGRP_ASGN_DT'
578 THEN
579 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
580 THEN
581 fnd_message.set_name('CN', 'CN_INVALID_SRP_PGRP_ASGN_DT');
582 fnd_msg_pub.add;
583 END IF;
584 x_loading_status := 'CN_INVALID_SRP_PGRP_ASGN_DT';
585 RAISE FND_API.G_EXC_ERROR;
586 ELSIF x_loading_status = 'CN_OVERLAP_SRP_PGRP_ASGN'
587 THEN
588 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
589 THEN
590 fnd_message.set_name('CN', 'CN_OVERLAP_SRP_PGRP_ASGN');
591 fnd_msg_pub.add;
592 END IF;
593 x_loading_status := 'CN_OVERLAP_SRP_PGRP_ASGN';
594 RAISE FND_API.G_EXC_ERROR;
595 ELSIF ( x_return_status = FND_API.G_RET_STS_SUCCESS )
596 THEN
597
598 Validate_end_date
599 (x_return_status => x_return_status,
600 x_msg_count => x_msg_count,
601 x_msg_data => x_msg_data,
602 p_salesrep_id => p_paygroup_assign_rec.salesrep_id,
603 p_org_id => p_paygroup_assign_rec.org_id,
604 p_assign_end_date => p_paygroup_assign_rec.assignment_end_date,
605 p_loading_status => x_loading_status,
606 x_loading_status => x_loading_status,
607 x_status => l_status );
608
609 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
610 THEN
611 RAISE FND_API.G_EXC_ERROR ;
612 END IF;
613
614 -- ready to insert
615 CN_SRP_Pay_Groups_Pkg.Begin_Record(
616 x_operation => 'INSERT',
617 x_srp_pay_group_id => l_srp_pay_group_id,
618 x_salesrep_id => p_paygroup_assign_rec.salesrep_id,
619 x_pay_group_id => p_paygroup_assign_rec.pay_group_id,
620 x_start_date => p_paygroup_assign_rec.assignment_start_date,
621 x_end_date => p_paygroup_assign_rec.assignment_end_date,
622 x_lock_flag => p_paygroup_assign_rec.lock_flag,
623 x_role_pay_group_id => p_paygroup_assign_rec.role_pay_group_id,
624 x_org_id => p_paygroup_assign_rec.org_id,
625 x_attribute_category=> p_paygroup_assign_rec.attribute_category,
626 x_attribute1 => p_paygroup_assign_rec.attribute1,
627 x_attribute2 => p_paygroup_assign_rec.attribute2,
628 x_attribute3 => p_paygroup_assign_rec.attribute3,
629 x_attribute4 => p_paygroup_assign_rec.attribute4,
630 x_attribute5 => p_paygroup_assign_rec.attribute5,
631 x_attribute6 => p_paygroup_assign_rec.attribute6,
632 x_attribute7 => p_paygroup_assign_rec.attribute7,
633 x_attribute8 => p_paygroup_assign_rec.attribute8,
634 x_attribute9 => p_paygroup_assign_rec.attribute9,
635 x_attribute10 => p_paygroup_assign_rec.attribute10,
636 x_attribute11 => p_paygroup_assign_rec.attribute10,
637 x_attribute12 => p_paygroup_assign_rec.attribute12,
638 x_attribute13 => p_paygroup_assign_rec.attribute13,
639 x_attribute14 => p_paygroup_assign_rec.attribute14,
640 x_attribute15 => p_paygroup_assign_rec.attribute15,
641 x_last_update_date => Sysdate,
642 x_last_updated_by => fnd_global.user_id,
643 x_creation_date => Sysdate,
644 x_created_by => fnd_global.user_id,
645 x_last_update_login => fnd_global.login_id,
646 x_object_version_number => p_paygroup_assign_rec.object_version_number);
647
648 p_paygroup_assign_rec.srp_pay_group_id := l_srp_pay_group_id;
649
650 -- raise business event
651 business_event
652 (p_operation => 'Add',
653 p_paygroup_assign_rec => p_paygroup_assign_rec);
654
655
656 else
657 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
658 THEN
659 FND_MESSAGE.SET_NAME ('CN' , 'CN_SRP_PAY_GROUPS_EXIST');
660 FND_MSG_PUB.Add;
661 END IF;
662 x_loading_status := 'CN_SRP_PAY_GROUPS_EXIST';
663 RAISE FND_API.G_EXC_ERROR ;
664 END IF;
665
666
667 -- Call cn_srp_periods_pvt api to affect the records in cn_srp_periods
668 FOR roles IN get_roles(p_paygroup_assign_rec.salesrep_id)
669 LOOP
670 --Added by Zack 1/15/02 to populate cn_srp_plan_assigns
671 IF ((roles.start_date <= p_paygroup_assign_rec.assignment_start_date
672 AND nvl(roles.end_date,l_null_date) >= p_paygroup_assign_rec.assignment_start_date)
673 OR (roles.start_date <= nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date)
674 AND nvl(roles.end_date,l_null_date) >= nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date))
675 OR (p_paygroup_assign_rec.assignment_start_date <= nvl(roles.end_date, l_null_date)
676 AND nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date) >= nvl(roles.end_date, l_null_date))
677 OR (p_paygroup_assign_rec.assignment_start_date <= roles.start_date
678 AND nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date) >= roles.start_date)
679 ) THEN
680
681 FOR role_plans IN get_role_plans(roles.role_id) LOOP
682 cn_srp_plan_assigns_pvt.Update_Srp_Plan_Assigns
683 (p_api_version => 1.0,
684 x_return_status => x_return_status,
685 x_msg_count => x_msg_count,
686 x_msg_data => x_msg_data,
687 p_srp_role_id => roles.srp_role_id,
688 p_role_plan_id => role_plans.role_plan_id,
689 x_loading_status => x_loading_status );
690
691 IF ( x_return_status = FND_API.G_RET_STS_ERROR) THEN
692 RAISE FND_API.G_EXC_ERROR;
693 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
695 END IF;
696
697 -- clku bug 2758073
698 -- mark event for intel calc, when new plan assignement is populated
699 -- after paygroup is assigned
700
701 select name
702 into l_pay_group_name
703 from cn_pay_groups_all
704 where pay_group_id = p_paygroup_assign_rec.pay_group_id;
705
706 cn_mark_events_pkg.mark_event_srp_pay_group
707 ('CHANGE_SRP_PAY_GROUP', -- event name
708 l_pay_group_name, -- object name
709 p_paygroup_assign_rec.pay_group_id, -- object id
710 p_paygroup_assign_rec.salesrep_id, -- srp_object_id
711 null, -- start date
712 p_paygroup_assign_rec.assignment_start_date, -- start date old
713 null, -- end date
714 p_paygroup_assign_rec.assignment_end_date,
715 p_paygroup_assign_rec.org_id); -- org ID
716 END LOOP;
717 END IF;
718
719 FOR plans IN get_plan_assigns(roles.role_id, p_paygroup_assign_rec.salesrep_id)
720 LOOP
721 -- Added by Zack, check the start_date and end_date of plan assignment, populate the intersection
722 -- part with the pay group assignment date.
723
724 IF nvl(plans.end_date,l_null_date) > nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date) THEN
725 plans.end_date := p_paygroup_assign_rec.assignment_end_date;
726 END IF;
727
728 IF plans.start_date < p_paygroup_assign_rec.assignment_start_date THEN
729 plans.start_date := p_paygroup_assign_rec.assignment_start_date;
730 END IF;
731
732 IF nvl(plans.end_date, l_null_date) > plans.start_date THEN
733
734 cn_srp_periods_pvt.create_srp_periods
735 ( p_api_version => p_api_version,
736 x_return_status => x_return_status,
737 x_msg_count => x_msg_count,
738 x_msg_data => x_msg_data,
739 p_salesrep_id => p_paygroup_assign_rec.salesrep_id,
740 p_role_id => roles.role_id,
741 p_comp_plan_id => plans.comp_plan_id,
742 p_start_date => plans.start_date,
743 p_end_date => plans.end_date,
744 x_loading_status => x_loading_status);
745 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
746 THEN
747 RAISE FND_API.G_EXC_ERROR;
748 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
749 THEN
750 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
751 END IF;
752
753 END IF;
754 END LOOP;
755 END LOOP;
756
757 -- End of API body
758
759 -- Standard check of p_commit.
760
761 IF FND_API.To_Boolean( p_commit ) THEN
762 COMMIT WORK;
763 END IF;
764
765 -- Standard call to get message count and if count is 1, get message info
766 FND_MSG_PUB.Count_And_Get
767 (
768 p_count => x_msg_count ,
769 p_data => x_msg_data ,
770 p_encoded => FND_API.G_FALSE
771 );
772
773 EXCEPTION
774 WHEN FND_API.G_EXC_ERROR THEN
775 ROLLBACK TO Create_Srp_Pay_Group;
776 x_return_status := FND_API.G_RET_STS_ERROR ;
777 FND_MSG_PUB.Count_And_Get
778 (
779 p_count => x_msg_count ,
780 p_data => x_msg_data ,
781 p_encoded => FND_API.G_FALSE
782 );
783 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
784 ROLLBACK TO Create_Srp_Pay_Group;
785 x_loading_status := 'UNEXPECTED_ERR';
786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
787 FND_MSG_PUB.Count_And_Get
788 (
789 p_count => x_msg_count ,
790 p_data => x_msg_data ,
791 p_encoded => FND_API.G_FALSE
792 );
793 WHEN OTHERS THEN
794 ROLLBACK TO Create_Srp_Pay_Group;
795 x_loading_status := 'UNEXPECTED_ERR';
796 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
797 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
798 THEN
799 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
800 END IF;
801 FND_MSG_PUB.Count_And_Get
802 (
803 p_count => x_msg_count ,
804 p_data => x_msg_data ,
805 p_encoded => FND_API.G_FALSE
806 );
807 END create_srp_pay_group;
808
809
810 -- --------------------------------------------------------------------------*
811 -- Procedure: Update_Srp_Pay_Group
812 -- --------------------------------------------------------------------------*
813 PROCEDURE Update_Srp_Pay_Group
814 ( p_api_version IN NUMBER ,
815 p_init_msg_list IN VARCHAR2,
816 p_commit IN VARCHAR2,
817 p_validation_level IN NUMBER,
818 x_return_status OUT NOCOPY VARCHAR2 ,
819 x_loading_status OUT NOCOPY VARCHAR2 ,
820 x_msg_count OUT NOCOPY NUMBER ,
821 x_msg_data OUT NOCOPY VARCHAR2 ,
822 p_paygroup_assign_rec IN OUT NOCOPY PayGroup_assign_rec
823 ) IS
824
825 l_api_name CONSTANT VARCHAR2(30) := 'Update_Srp_Pay_Group';
826 l_api_version CONSTANT NUMBER := 1.0;
827 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
828
829 CURSOR get_roles (p_salesrep_id NUMBER) IS
830 SELECT role_id,srp_role_id,start_date, nvl(end_date,l_null_date) end_date
831 FROM cn_srp_roles
832 WHERE salesrep_id = p_salesrep_id
833 AND org_id = p_paygroup_assign_rec.org_id;
834
835 CURSOR get_role_plans(p_role_id NUMBER) IS
836 SELECT role_plan_id
837 FROM cn_role_plans_all
838 WHERE role_id = p_role_id
839 AND org_id = p_paygroup_assign_rec.org_id;
840
841 CURSOR get_plan_assigns
842 (p_role_id NUMBER,
843 p_salesrep_id NUMBER) IS
844 SELECT comp_plan_id,
845 start_date,
846 end_date
847 FROM cn_srp_plan_assigns_all
848 WHERE role_id = p_role_id
849 AND salesrep_id = p_salesrep_id
850 AND org_id = p_paygroup_assign_rec.org_id;
851
852 -- clku
853 CURSOR payee_check_curs(l_salesrep_id NUMBER) IS
854 select srp_role_id from cn_srp_roles where
855 salesrep_id = l_salesrep_id
856 and role_id = 54
857 AND org_id = p_paygroup_assign_rec.org_id;
858
859 CURSOR payee_assign_date_curs(l_payee_id NUMBER) IS
860 select salesrep_id, start_date, end_date
861 from cn_srp_payee_assigns_all
862 where payee_id = l_payee_id
863 AND org_id = p_paygroup_assign_rec.org_id;
864
865 l_payee_assign_date_rec payee_assign_date_curs%ROWTYPE;
866 l_ws_count NUMBER;
867
868 l_date_range_action_tbl cn_api.date_range_action_tbl_type;
869 l_ovn_old NUMBER;
870 l_old_assignment_start_date DATE;
871 l_old_assignment_end_date DATE;
872 l_old_salesrep_id NUMBER;
873 l_old_lock_flag VARCHAR2(1);
874 l_status VARCHAR2(30);
875 l_employee_number cn_salesreps.employee_number%TYPE;
876 l_employee_name cn_salesreps.name%TYPE;
877 l_pay_group_name cn_pay_groups.name%TYPE;
878 l_dummy NUMBER;
879 l_srp_role_id NUMBER;
880
881
882
883 BEGIN
884 -- Standard Start of API savepoint
885
886 SAVEPOINT Update_Srp_Pay_Group;
887
888 -- Standard call to check for call compatibility.
889
890 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
891 p_api_version ,
892 l_api_name ,
893 G_PKG_NAME )
894 THEN
895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
896 END IF;
897
898 -- Initialize message list if p_init_msg_list is set to TRUE.
899 IF FND_API.to_Boolean( p_init_msg_list ) THEN
900 FND_MSG_PUB.initialize;
901 END IF;
902
903 -- Initialize API return status to success
904 x_return_status := FND_API.G_RET_STS_SUCCESS;
905 x_loading_status := 'CN_UPDATED';
906
907
908 -- get the current object version number
909 select object_version_number, salesrep_id, start_date, end_date, lock_flag
910 into l_ovn_old, l_old_salesrep_id,
911 l_old_assignment_start_date, l_old_assignment_end_date, l_old_lock_flag
912 from cn_srp_pay_groups_all
913 where srp_pay_group_id = p_paygroup_assign_rec.srp_pay_group_id;
914
915 IF l_ovn_old <> p_paygroup_assign_rec.object_version_number THEN
916 --
917 --Raise an error if the object_version numbers don't match
918 --
919 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
920 THEN
921 fnd_message.set_name('CN', 'CL_INVALID_OVN');
922 fnd_msg_pub.add;
923 END IF;
924 x_loading_status := 'CL_INVALID_OVN';
925 RAISE FND_API.G_EXC_ERROR;
926 END IF;
927
928 IF p_paygroup_assign_rec.salesrep_id <> l_old_salesrep_id
929 THEN
930 --
931 --Raise an error since the salesrep should not be updated
932 --Instead, the assignment dates should be changed to reflect this.
933 --
934 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
935 THEN
936 fnd_message.set_name('CN', 'CN_INVALID_UPD_SRP_PGRP');
937 fnd_msg_pub.add;
938 END IF;
939 x_loading_status := 'CN_INVALID_UPD_SRP_PGRP';
940 RAISE FND_API.G_EXC_ERROR;
941 END IF;
942
943 -- can't change lock flag from Y to N
944 IF l_old_lock_flag = 'Y' AND p_paygroup_assign_rec.lock_flag = 'N' THEN
945 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
946 THEN
947 fnd_message.set_name('CN', 'CN_CANNOT_UPDATE_LOCK');
948 fnd_msg_pub.add;
949 END IF;
950 x_loading_status := 'CN_CANNOT_UPDATE_LOCK';
951 RAISE FND_API.G_EXC_ERROR;
952 END IF;
953
954 -- can't change lock from N to Y if it is manual assignment
955 IF l_old_lock_flag = 'N' AND p_paygroup_assign_rec.lock_flag = 'Y' AND
956 p_paygroup_assign_rec.role_pay_group_id IS NULL THEN
957 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
958 THEN
959 fnd_message.set_name('CN', 'CN_CANNOT_UPDATE_LOCK');
960 fnd_msg_pub.add;
961 END IF;
962 x_loading_status := 'CN_CANNOT_UPDATE_LOCK';
963 RAISE FND_API.G_EXC_ERROR;
964 END IF;
965
966 Validate_assignment
967 (x_return_status => x_return_status,
968 x_msg_count => x_msg_count,
969 x_msg_data => x_msg_data,
970 p_salesrep_id => p_paygroup_assign_rec.salesrep_id,
971 p_org_id => p_paygroup_assign_rec.org_id,
972 p_start_date => p_paygroup_assign_rec.assignment_start_date,
973 p_end_date => p_paygroup_assign_rec.assignment_end_date,
974 p_pay_group_id => p_paygroup_assign_rec.pay_group_id,
975 p_srp_pay_group_id => p_paygroup_assign_rec.srp_pay_group_id,
976 p_loading_status => x_loading_status,
977 x_loading_status => x_loading_status,
978 x_status => l_status );
979
980 SELECT name, employee_number
981 INTO l_employee_name, l_employee_number
982 FROM cn_salesreps
983 WHERE salesrep_id = p_paygroup_assign_rec.salesrep_id
984 AND org_id = p_paygroup_assign_rec.org_id;
985
986 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
987 THEN
988 RAISE FND_API.G_EXC_ERROR ;
989 ELSIF x_loading_status = 'CN_INVALID_SRP_PGRP_ASGN_DT'
990 THEN
991 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
992 THEN
993 fnd_message.set_name('CN', 'CN_INVALID_SRP_PGRP_ASGN_DT');
994 fnd_msg_pub.add;
995 END IF;
996 x_loading_status := 'CN_INVALID_SRP_PGRP_ASGN_DT';
997 RAISE FND_API.G_EXC_ERROR;
998 ELSIF x_loading_status = 'CN_OVERLAP_SRP_PGRP_ASGN'
999 THEN
1000 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1001 THEN
1002 fnd_message.set_name('CN', 'CN_OVERLAP_SRP_PGRP_ASGN');
1003 fnd_msg_pub.add;
1004 END IF;
1005 x_loading_status := 'CN_OVERLAP_SRP_PGRP_ASGN';
1006 RAISE FND_API.G_EXC_ERROR;
1007 ELSIF ( x_return_status = FND_API.G_RET_STS_SUCCESS )
1008 THEN
1009
1010 Validate_end_date
1011 (x_return_status => x_return_status,
1012 x_msg_count => x_msg_count,
1013 x_msg_data => x_msg_data,
1014 p_salesrep_id => p_paygroup_assign_rec.salesrep_id,
1015 p_org_id => p_paygroup_assign_rec.org_id,
1016 p_assign_end_date => p_paygroup_assign_rec.assignment_end_date,
1017 p_loading_status => x_loading_status,
1018 x_loading_status => x_loading_status,
1019 x_status => l_status );
1020
1021
1022 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1023 THEN
1024 RAISE FND_API.G_EXC_ERROR ;
1025 END IF;
1026
1027 --clku, payee check enhancement
1028 OPEN payee_check_curs(p_paygroup_assign_rec.salesrep_id);
1029 Fetch payee_check_curs into l_srp_role_id;
1030 IF payee_check_curs%FOUND THEN
1031 cn_api.get_date_range_diff_action
1032 ( start_date_new => p_paygroup_assign_rec.assignment_start_date
1033 ,end_date_new => p_paygroup_assign_rec.assignment_end_date
1034 ,start_date_old => l_old_assignment_start_date
1035 ,end_date_old => l_old_assignment_end_date
1036 ,x_date_range_action_tbl => l_date_range_action_tbl );
1037
1038 FOR i IN 1..l_date_range_action_tbl.COUNT LOOP
1039 if l_date_range_action_tbl(i).action_flag = 'D' THEN
1040
1041 -- check if there is any salesrep having this payee assigned within
1042 -- the deleting paygroup date range
1043 For l_payee_assign_date_rec IN payee_assign_date_curs
1044 (p_paygroup_assign_rec.salesrep_id) LOOP
1045 -- check if there is any date range over between
1046 -- srp paygroup date and payee assign date
1047 IF CN_API.date_range_overlap
1048 (l_date_range_action_tbl(i).start_date,
1049 l_date_range_action_tbl(i).end_date,
1050 l_payee_assign_date_rec.start_date,
1051 l_payee_assign_date_rec.end_date) = true THEN
1052
1053 -- Raise Error
1054 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1055 THEN
1056 fnd_message.set_name('CN', 'CN_PA_ASGN_DATE');
1057 fnd_msg_pub.add;
1058 END IF;
1059
1060 x_loading_status := 'CN_PA_ASGN_DATE';
1061 RAISE FND_API.G_EXC_ERROR;
1062
1063
1064 END IF;
1065 END LOOP;
1066
1067 -- check if the payee has any worksheet
1068 SELECT count(*)
1069 into l_ws_count
1070 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
1071 cn_payruns_all prun
1072 WHERE w.salesrep_id = p_paygroup_assign_rec.salesrep_id
1073 AND w.org_id = p_paygroup_assign_rec.org_id
1074 AND prun.pay_period_id = prd.period_id
1075 AND prun.payrun_id = w.payrun_id
1076 AND prd.org_id = p_paygroup_assign_rec.org_id
1077 AND prun.pay_group_id = p_paygroup_assign_rec.pay_group_id
1078 AND w.quota_id is null
1079 AND (
1080 (prd.start_date BETWEEN l_date_range_action_tbl(i).start_date
1081 AND nvl(l_date_range_action_tbl(i).end_date,l_null_date))
1082 OR
1083 (prd.end_date between l_date_range_action_tbl(i).start_date
1084 AND nvl(l_date_range_action_tbl(i).end_date,l_null_date))
1085 );
1086
1087 IF l_ws_count > 0 THEN
1088 -- Raise Error
1089 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1090 THEN
1091 fnd_message.set_name('CN', 'CN_SRP_PG_WS');
1092 fnd_msg_pub.add;
1093 END IF;
1094
1095 x_loading_status := 'CN_SRP_PG_WS';
1096 RAISE FND_API.G_EXC_ERROR;
1097 END IF;
1098
1099 END IF; --if l_date_range_action_tbl(i).action_flag = 'D'
1100 END LOOP; -- FOR i IN 1..l_date_range_action_tbl.COUNT LOOP
1101 END IF; -- if salesrep is payee
1102
1103 Close payee_check_curs;
1104
1105
1106 --***********************************************************************
1107 -- Added By Zack Li, fixed by Matt Blum
1108 -- Date 02/14/06
1109 --
1110 -- Shorten the end_date assignment
1111 -- Check for the shortened date range, if worksheet already been used,
1112 -- if so, cannot shorten
1113 --***********************************************************************
1114
1115 IF ( ((l_old_assignment_end_date IS NOT NULL) AND
1116 (p_paygroup_assign_rec.assignment_end_date IS NOT NULL) AND
1117 (l_old_assignment_end_date > p_paygroup_assign_rec.assignment_end_date))
1118 OR
1119 ((l_old_assignment_end_date IS NULL) AND
1120 (p_paygroup_assign_rec.assignment_end_date IS NOT NULL)) ) THEN
1121 SELECT count(1) INTO l_dummy
1122 FROM cn_payment_worksheets W, cn_period_statuses prd, cn_payruns prun
1123 WHERE w.salesrep_id = p_paygroup_assign_rec.salesrep_id
1124 AND w.org_id = p_paygroup_assign_rec.org_id
1125 AND prun.pay_period_id = prd.period_id
1126 AND prd.org_id = p_paygroup_assign_rec.org_id
1127 AND prun.payrun_id = w.payrun_id
1128 AND greatest(prd.start_date, p_paygroup_assign_rec.assignment_end_date) <
1129 least(prd.end_date, nvl(l_old_assignment_end_date, prd.end_date));
1130
1131 if l_dummy > 0 then
1132 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1133 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPG_CANNOT_SHORTEN_ED');
1134 FND_MSG_PUB.Add;
1135 END IF;
1136 x_loading_status := 'CN_SPG_CANNOT_SHORTEN_ED';
1137 RAISE FND_API.G_EXC_ERROR ;
1138 end if;
1139
1140 END IF ; -- end IF end date change
1141
1142 -- Check if during the old date range assign, any worksheet already
1143 -- been used, if so, cannot shrink start_date. If not been used, start
1144 -- date can be extend or shrink.
1145
1146 IF l_old_assignment_start_date < p_paygroup_assign_rec.assignment_start_date THEN
1147 SELECT count(1) INTO l_dummy
1148 FROM cn_payment_worksheets W, cn_period_statuses prd, cn_payruns prun
1149 WHERE w.salesrep_id = p_paygroup_assign_rec.salesrep_id
1150 AND w.org_id = p_paygroup_assign_rec.org_id
1151 AND prun.pay_period_id = prd.period_id
1152 AND prd.org_id = p_paygroup_assign_rec.org_id
1153 AND prun.payrun_id = w.payrun_id
1154 AND greatest(prd.start_date, l_old_assignment_start_date) <
1155 least(prd.end_date, p_paygroup_assign_rec.assignment_start_date);
1156
1157 if l_dummy > 0 then
1158 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1159 THEN
1160 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPG_UPDATE_NOT_ALLOWED');
1161 FND_MSG_PUB.Add;
1162 END IF;
1163 x_loading_status := 'CN_SPG_UPDATE_NOT_ALLOWED';
1164 RAISE FND_API.G_EXC_ERROR ;
1165 END if;
1166
1167 END IF ; -- end IF start date change
1168
1169 -- if the lock_flag is being set, then blow away role_pay_group_id
1170 IF p_paygroup_assign_rec.lock_flag = 'Y' THEN
1171 p_paygroup_assign_rec.role_pay_group_id := NULL;
1172 END IF;
1173
1174 CN_SRP_Pay_Groups_Pkg.Begin_Record(
1175 x_operation => 'UPDATE',
1176 x_srp_pay_group_id => p_paygroup_assign_rec.srp_pay_group_id,
1177 x_salesrep_id => p_paygroup_assign_rec.salesrep_id,
1178 x_pay_group_id => p_paygroup_assign_rec.pay_group_id,
1179 x_start_date => p_paygroup_assign_rec.assignment_start_date,
1180 x_end_date => p_paygroup_assign_rec.assignment_end_date,
1181 x_lock_flag => p_paygroup_assign_rec.lock_flag,
1182 x_role_pay_group_id => p_paygroup_assign_rec.role_pay_group_id,
1183 x_org_id => p_paygroup_assign_rec.org_id,
1184 x_attribute_category=> p_paygroup_assign_rec.attribute_category,
1185 x_attribute1 => p_paygroup_assign_rec.attribute1,
1186 x_attribute2 => p_paygroup_assign_rec.attribute2,
1187 x_attribute3 => p_paygroup_assign_rec.attribute3,
1188 x_attribute4 => p_paygroup_assign_rec.attribute4,
1189 x_attribute5 => p_paygroup_assign_rec.attribute5,
1190 x_attribute6 => p_paygroup_assign_rec.attribute6,
1191 x_attribute7 => p_paygroup_assign_rec.attribute7,
1192 x_attribute8 => p_paygroup_assign_rec.attribute8,
1193 x_attribute9 => p_paygroup_assign_rec.attribute9,
1194 x_attribute10 => p_paygroup_assign_rec.attribute10,
1195 x_attribute11 => p_paygroup_assign_rec.attribute10,
1196 x_attribute12 => p_paygroup_assign_rec.attribute12,
1197 x_attribute13 => p_paygroup_assign_rec.attribute13,
1198 x_attribute14 => p_paygroup_assign_rec.attribute14,
1199 x_attribute15 => p_paygroup_assign_rec.attribute15,
1200 x_last_update_date => Sysdate,
1201 x_last_updated_by => fnd_global.user_id,
1202 x_creation_date => Sysdate,
1203 x_created_by => fnd_global.user_id,
1204 x_last_update_login => fnd_global.login_id,
1205 x_object_version_number => p_paygroup_assign_rec.object_version_number);
1206
1207 -- raise business event
1208 business_event
1209 (p_operation => 'Update',
1210 p_paygroup_assign_rec => p_paygroup_assign_rec);
1211
1212 END IF; -- if validate success
1213
1214 -- Call cn_srp_periods_pvt api to affect the records in cn_srp_periods
1215 FOR roles IN get_roles(p_paygroup_assign_rec.salesrep_id)
1216 LOOP
1217
1218 -- Added by Zack 01/15/02, update cn_srp_plan_assign if necessary.
1219 -- clku, bug 2772005, nvl the end dates here
1220 IF(
1221 (p_paygroup_assign_rec.assignment_start_date <> l_old_assignment_start_date )
1222 AND
1223 ( (roles.start_date <= least(p_paygroup_assign_rec.assignment_start_date, l_old_assignment_start_date)
1224 AND roles.end_date >= least(p_paygroup_assign_rec.assignment_start_date, l_old_assignment_start_date) )
1225 OR
1226 (roles.start_date <= greatest(p_paygroup_assign_rec.assignment_start_date, l_old_assignment_start_date)
1227 AND roles.end_date >= greatest(p_paygroup_assign_rec.assignment_start_date, l_old_assignment_start_date) ) )
1228 OR
1229 (nvl(p_paygroup_assign_rec.assignment_end_date, l_null_date) <> nvl(l_old_assignment_end_date, l_null_date) )
1230 AND
1231 ( (roles.start_date <= least(nvl(p_paygroup_assign_rec.assignment_end_date, l_null_date), nvl(l_old_assignment_end_date, l_null_date))
1232 AND roles.end_date >= least(nvl(p_paygroup_assign_rec.assignment_end_date, l_null_date), nvl(l_old_assignment_end_date, l_null_date)) )
1233 OR
1234 (roles.start_date <= greatest(nvl(p_paygroup_assign_rec.assignment_end_date, l_null_date), nvl(l_old_assignment_end_date, l_null_date))
1235 AND roles.end_date >= greatest(nvl(p_paygroup_assign_rec.assignment_end_date, l_null_date), nvl(l_old_assignment_end_date, l_null_date)) ) )
1236 ) THEN
1237 FOR role_plans IN get_role_plans(roles.role_id) LOOP
1238 cn_srp_plan_assigns_pvt.Update_Srp_Plan_Assigns
1239 (
1240 p_api_version => 1.0,
1241 x_return_status => x_return_status,
1242 x_msg_count => x_msg_count,
1243 x_msg_data => x_msg_data,
1244 p_srp_role_id => roles.srp_role_id,
1245 p_role_plan_id => role_plans.role_plan_id,
1246 x_loading_status => x_loading_status );
1247
1248 IF ( x_return_status = FND_API.G_RET_STS_ERROR) THEN
1249 RAISE FND_API.G_EXC_ERROR;
1250 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1252 END IF;
1253
1254 -- clku bug 2758073
1255 -- mark event for intel calc, when plan assignement's date
1256 -- range change as a result of the paygroup daterange change
1257
1258 select name
1259 into l_pay_group_name
1260 from cn_pay_groups_all
1261 where pay_group_id = p_paygroup_assign_rec.pay_group_id;
1262
1263 cn_mark_events_pkg.mark_event_srp_pay_group
1264 ('CHANGE_SRP_PAY_GROUP_DATE', -- event name
1265 l_pay_group_name, -- object name
1266 p_paygroup_assign_rec.pay_group_id, -- object id
1267 p_paygroup_assign_rec.salesrep_id, -- srp_object_id
1268 p_paygroup_assign_rec.assignment_start_date, -- start date
1269 l_old_assignment_start_date, -- start date old
1270 p_paygroup_assign_rec.assignment_end_date, -- end date
1271 l_old_assignment_end_date, -- end date old
1272 p_paygroup_assign_rec.org_id); -- org ID
1273 END LOOP;
1274 END IF;
1275
1276 FOR plans IN get_plan_assigns(roles.role_id, p_paygroup_assign_rec.salesrep_id)
1277 LOOP
1278 -- Added by Zack, check the start_date and end_date of plan assignment, populate the intersection
1279 -- part with the pay group assignment date.
1280
1281 IF nvl(plans.end_date,l_null_date) > nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date) THEN
1282 plans.end_date := p_paygroup_assign_rec.assignment_end_date;
1283 END IF;
1284
1285 IF plans.start_date < p_paygroup_assign_rec.assignment_start_date THEN
1286 plans.start_date := p_paygroup_assign_rec.assignment_start_date;
1287 END IF;
1288
1289 IF nvl(plans.end_date, l_null_date) > plans.start_date THEN
1290 cn_srp_periods_pvt.create_srp_periods
1291 ( p_api_version => p_api_version,
1292 x_return_status => x_return_status,
1293 x_msg_count => x_msg_count,
1294 x_msg_data => x_msg_data,
1295 p_salesrep_id => p_paygroup_assign_rec.salesrep_id,
1296 p_role_id => roles.role_id,
1297 p_comp_plan_id => plans.comp_plan_id,
1298 p_start_date => plans.start_date,
1299 p_end_date => plans.end_date,
1300 x_loading_status => x_loading_status);
1301 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
1302 THEN
1303 RAISE FND_API.G_EXC_ERROR;
1304 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1305 THEN
1306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1307 END IF;
1308 END IF;
1309 END LOOP;
1310 END LOOP;
1311
1312 -- End of API body
1313
1314 -- Standard check of p_commit.
1315
1316 IF FND_API.To_Boolean( p_commit ) THEN
1317 COMMIT WORK;
1318 END IF;
1319
1320 -- Standard call to get message count and if count is 1, get message info
1321 FND_MSG_PUB.Count_And_Get
1322 (
1323 p_count => x_msg_count ,
1324 p_data => x_msg_data ,
1325 p_encoded => FND_API.G_FALSE
1326 );
1327
1328 EXCEPTION
1329 WHEN FND_API.G_EXC_ERROR THEN
1330 ROLLBACK TO Update_Srp_Pay_Group;
1331 x_return_status := FND_API.G_RET_STS_ERROR ;
1332 FND_MSG_PUB.Count_And_Get
1333 (
1334 p_count => x_msg_count ,
1335 p_data => x_msg_data ,
1336 p_encoded => FND_API.G_FALSE
1337 );
1338 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1339 ROLLBACK TO Update_Srp_Pay_Group;
1340 x_loading_status := 'UNEXPECTED_ERR';
1341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1342 FND_MSG_PUB.Count_And_Get
1343 (
1344 p_count => x_msg_count ,
1345 p_data => x_msg_data ,
1346 p_encoded => FND_API.G_FALSE
1347 );
1348 WHEN OTHERS THEN
1349 ROLLBACK TO Update_Srp_Pay_Group;
1350 x_loading_status := 'UNEXPECTED_ERR';
1351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1352 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1353 THEN
1354 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1355 END IF;
1356 FND_MSG_PUB.Count_And_Get
1357 (
1358 p_count => x_msg_count ,
1359 p_data => x_msg_data ,
1360 p_encoded => FND_API.G_FALSE
1361 );
1362
1363 END update_srp_pay_group;
1364
1365 -- --------------------------------------------------------------------------*
1366 -- Procedure: Valid_Delete_Srp_Pay_Group
1367 -- --------------------------------------------------------------------------*
1368 PROCEDURE valid_delete_srp_pay_group
1369 ( p_paygroup_assign_rec IN paygroup_assign_rec ,
1370 p_init_msg_list IN VARCHAR2,
1371 x_loading_status OUT NOCOPY VARCHAR2 ,
1372 x_return_status OUT NOCOPY VARCHAR2 ,
1373 x_msg_count OUT NOCOPY NUMBER ,
1374 x_msg_data OUT NOCOPY VARCHAR2
1375 ) IS
1376
1377
1378 l_api_name CONSTANT VARCHAR2(30) := 'Valid_Delete_Srp_Pay_Group';
1379 l_srp_role_id NUMBER;
1380 l_ws_count NUMBER;
1381 l_null_date CONSTANT DATE := to_date('12/31/9999','MM/DD/YYYY');
1382 l_count NUMBER;
1383 l_srp_pay_group_id cn_srp_pay_groups.srp_pay_group_id%TYPE;
1384
1385 -- clku
1386 CURSOR payee_check_curs(l_salesrep_id NUMBER) IS
1387 select srp_role_id from cn_srp_roles where
1388 salesrep_id = l_salesrep_id
1389 and role_id = 54
1390 AND org_id = p_paygroup_assign_rec.org_id;
1391
1392 CURSOR payee_assign_date_curs(l_payee_id NUMBER) IS
1393 select salesrep_id, start_date, end_date
1394 from cn_srp_payee_assigns_all
1395 where payee_id = l_payee_id
1396 AND org_id = p_paygroup_assign_rec.org_id;
1397
1398 l_payee_assign_date_rec payee_assign_date_curs%ROWTYPE;
1399
1400
1401
1402 BEGIN
1403 -- Initialize message list if p_init_msg_list is set to TRUE.
1404 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1405 FND_MSG_PUB.initialize;
1406 END IF;
1407
1408 -- Initialize API return status to success
1409 x_return_status := FND_API.G_RET_STS_SUCCESS;
1410 x_loading_status := 'CN_DELETED';
1411
1412 --clku, payee check enhancement
1413 OPEN payee_check_curs(p_paygroup_assign_rec.salesrep_id);
1414 Fetch payee_check_curs into l_srp_role_id;
1415 IF payee_check_curs%FOUND THEN
1416 -- check if there is any salesrep having this payee assigned within
1417 -- the deleting paygroup date range
1418 For l_payee_assign_date_rec IN payee_assign_date_curs
1419 (p_paygroup_assign_rec.salesrep_id) LOOP
1420 -- check if there is any date range over between
1421 -- srp paygroup date and payee assign date
1422 IF CN_API.date_range_overlap
1423 (p_paygroup_assign_rec.assignment_start_date,
1424 p_paygroup_assign_rec.assignment_end_date,
1425 l_payee_assign_date_rec.start_date,
1426 l_payee_assign_date_rec.end_date) = true THEN
1427
1428 -- Raise Error
1429 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1430 THEN
1431 fnd_message.set_name('CN', 'CN_PA_ASGN_DATE');
1432 fnd_msg_pub.add;
1433 END IF;
1434
1435 x_loading_status := 'CN_PA_ASGN_DATE';
1436 RAISE FND_API.G_EXC_ERROR;
1437 END IF;
1438 END LOOP;
1439
1440 -- check if the payee has any worksheet
1441 SELECT count(*)
1442 into l_ws_count
1443 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
1444 cn_payruns_all prun
1445 WHERE w.salesrep_id = p_paygroup_assign_rec.salesrep_id
1446 AND w.org_id = p_paygroup_assign_rec.org_id
1447 AND prun.pay_period_id = prd.period_id
1448 AND prun.payrun_id = w.payrun_id
1449 AND prd.org_id = p_paygroup_assign_rec.org_id
1450 AND prun.pay_group_id = p_paygroup_assign_rec.pay_group_id
1451 AND w.quota_id is null
1452 AND (
1453 (prd.start_date BETWEEN p_paygroup_assign_rec.assignment_start_date
1454 AND nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date))
1455 OR
1456 (prd.end_date between p_paygroup_assign_rec.assignment_start_date
1457 AND nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date))
1458 );
1459
1460 IF l_ws_count > 0 THEN
1461 -- Raise Error
1462 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1463 THEN
1464 fnd_message.set_name('CN', 'CN_SRP_PG_WS');
1465 fnd_msg_pub.add;
1466 END IF;
1467
1468 x_loading_status := 'CN_SRP_PG_WS';
1469 RAISE FND_API.G_EXC_ERROR;
1470 END IF;
1471
1472 END IF;
1473
1474 Close payee_check_curs;
1475
1476 SELECT SRP_PAY_GROUP_ID
1477 INTO l_srp_pay_group_id
1478 FROM cn_srp_pay_groups_all
1479 WHERE pay_group_id = p_paygroup_assign_rec.pay_group_id
1480 AND start_date=p_paygroup_assign_rec.assignment_start_date
1481 AND (end_date =p_paygroup_assign_rec.assignment_end_date OR
1482 end_date IS NULL)
1483 AND salesrep_id =p_paygroup_assign_rec.salesrep_id;
1484
1485 SELECT COUNT(1) INTO l_count from cn_srp_pay_groups_all
1486 WHERE srp_pay_group_id = l_srp_pay_group_id
1487 AND salesrep_id = p_paygroup_assign_rec.salesrep_id
1488 AND pay_group_id= p_paygroup_assign_rec.pay_group_id
1489 AND org_id = p_paygroup_assign_rec.org_id
1490 -- AND (lock_flag='N'OR lock_flag IS NULL)
1491 AND (start_date between p_paygroup_assign_rec.assignment_start_date AND
1492 nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date))
1493 AND (nvl(end_date,l_null_date) between
1494 p_paygroup_assign_rec.assignment_start_date AND
1495 nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date))
1496 AND NOT EXISTS
1497 (SELECT 1 FROM cn_payment_worksheets_all W,
1498 cn_period_statuses_all prd, cn_payruns_all prun
1499 WHERE w.salesrep_id = p_paygroup_assign_rec.salesrep_id
1500 AND w.org_id = p_paygroup_assign_rec.org_id
1501 AND prun.pay_period_id = prd.period_id
1502 AND prun.payrun_id = w.payrun_id
1503 AND prun.pay_group_id = p_paygroup_assign_rec.pay_group_id
1504 AND prd.org_id = p_paygroup_assign_rec.org_id
1505 AND ((prd.start_date BETWEEN
1506 p_paygroup_assign_rec.assignment_start_date AND
1507 nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date))
1508 OR (prd.end_date between
1509 p_paygroup_assign_rec.assignment_start_date AND
1510 nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date))));
1511
1512 IF l_count = 0 THEN
1513 --Error condition
1514 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1515 THEN
1516 fnd_message.set_name('CN', 'CN_SRP_PG_WS');
1517 fnd_msg_pub.add;
1518 END IF;
1519
1520 x_loading_status := 'CN_SRP_PG_WS';
1521 RAISE FND_API.G_EXC_ERROR;
1522
1523 END IF;
1524 EXCEPTION
1525 WHEN FND_API.G_EXC_ERROR THEN
1526 x_return_status := FND_API.G_RET_STS_ERROR ;
1527 FND_MSG_PUB.Count_And_Get
1528
1529
1530 (
1531 p_count => x_msg_count ,
1532 p_data => x_msg_data ,
1533 p_encoded => FND_API.G_FALSE
1534 );
1535 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1536 x_loading_status := 'UNEXPECTED_ERR';
1537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1538 FND_MSG_PUB.Count_And_Get
1539 (
1540 p_count => x_msg_count ,
1541 p_data => x_msg_data ,
1542 p_encoded => FND_API.G_FALSE
1543 );
1544 WHEN OTHERS THEN
1545 x_loading_status := 'UNEXPECTED_ERR';
1546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1547 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1548 THEN
1549 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1550 END IF;
1551 FND_MSG_PUB.Count_And_Get
1552 (
1553 p_count => x_msg_count ,
1554 p_data => x_msg_data ,
1555 p_encoded => FND_API.G_FALSE
1556 );
1557
1558
1559 END valid_delete_srp_pay_group;
1560
1561 -- --------------------------------------------------------------------------*
1562 -- Procedure: Delete_Srp_Pay_Group
1563 -- --------------------------------------------------------------------------*
1564 PROCEDURE Delete_Srp_Pay_Group
1565 ( p_api_version IN NUMBER ,
1566 p_init_msg_list IN VARCHAR2,
1567 p_commit IN VARCHAR2,
1568 p_validation_level IN NUMBER,
1569 x_return_status OUT NOCOPY VARCHAR2 ,
1570 x_loading_status OUT NOCOPY VARCHAR2 ,
1571 x_msg_count OUT NOCOPY NUMBER ,
1572 x_msg_data OUT NOCOPY VARCHAR2 ,
1573 p_paygroup_assign_rec IN PayGroup_assign_rec
1574 ) IS
1575
1576 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Srp_Pay_Group';
1577 l_api_version CONSTANT NUMBER := 1.0;
1578 l_role_id cn_roles.role_id%TYPE;
1579 l_loading_status VARCHAR2(2000);
1580 l_null_date CONSTANT DATE := to_date('12/31/9999','MM/DD/YYYY');
1581 -- Declaration for user hooks
1582
1583 l_count NUMBER(15);
1584 l_start_date DATE;
1585 l_end_date DATE;
1586 l_srp_pay_group_id cn_srp_pay_groups.srp_pay_group_id%TYPE;
1587 l_paygroup_assign_rec paygroup_assign_rec;
1588
1589 CURSOR get_role_plans(l_role_id cn_roles.role_id%TYPE) IS
1590 SELECT role_plan_id,role_id
1591 FROM cn_role_plans_all
1592 WHERE role_id = l_role_id
1593 AND org_id = p_paygroup_assign_rec.org_id;
1594
1595 CURSOR get_salesreps(l_role_id NUMBER) IS
1596 SELECT srp_role_id,salesrep_id
1597 FROM cn_srp_roles
1598 WHERE role_id = l_role_id
1599 AND org_id = p_paygroup_assign_rec.org_id;
1600
1601 CURSOR get_roles (p_salesrep_id cn_salesreps.salesrep_id%TYPE) IS
1602 SELECT role_id, srp_role_id,start_date, nvl(end_date,l_null_date) end_date
1603 FROM cn_srp_roles
1604 WHERE salesrep_id = p_salesrep_id
1605 AND org_id = p_paygroup_assign_rec.org_id;
1606
1607 CURSOR get_plan_assigns
1608 (p_role_id NUMBER,
1609 p_salesrep_id NUMBER) IS
1610 SELECT comp_plan_id,
1611 start_date,
1612 end_date
1613 FROM cn_srp_plan_assigns_all
1614 WHERE role_id = p_role_id
1615 AND salesrep_id = p_salesrep_id
1616 AND org_id = p_paygroup_assign_rec.org_id;
1617
1618 CURSOR get_srp_pg(l_salesrep_id NUMBER) IS
1619 select pay_group_id,start_date,end_date
1620 from cn_srp_pay_groups_all
1621 where salesrep_id = l_salesrep_id
1622 AND org_id = p_paygroup_assign_rec.org_id;
1623
1624
1625
1626 BEGIN
1627 -- Standard Start of API savepoint
1628
1629 SAVEPOINT Delete_Srp_Pay_Group;
1630
1631 -- Standard call to check for call compatibility.
1632
1633 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1634 p_api_version ,
1635 l_api_name ,
1636 G_PKG_NAME )
1637 THEN
1638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1639 END IF;
1640
1641 -- Initialize message list if p_init_msg_list is set to TRUE.
1642 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1643 FND_MSG_PUB.initialize;
1644 END IF;
1645
1646 -- Initialize API return status to success
1647 x_return_status := FND_API.G_RET_STS_SUCCESS;
1648 x_loading_status := 'CN_DELETED';
1649
1650 -- validate delete
1651 valid_delete_srp_pay_group
1652 ( p_paygroup_assign_rec => p_paygroup_assign_rec,
1653 p_init_msg_list => p_init_msg_list,
1654 x_loading_status => x_loading_status,
1655 x_return_status => x_return_status,
1656 x_msg_count => x_msg_count,
1657 x_msg_data => x_msg_data);
1658
1659 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1660 RAISE fnd_api.g_exc_error;
1661 END IF;
1662
1663 -- if made it here, then OK to delete
1664 SELECT SRP_PAY_GROUP_ID
1665 INTO l_srp_pay_group_id
1666 FROM cn_srp_pay_groups_all
1667 WHERE pay_group_id = p_paygroup_assign_rec.pay_group_id
1668 AND start_date=p_paygroup_assign_rec.assignment_start_date
1669 AND (end_date =p_paygroup_assign_rec.assignment_end_date OR
1670 end_date IS NULL)
1671 AND salesrep_id =p_paygroup_assign_rec.salesrep_id;
1672
1673 DELETE FROM cn_srp_pay_groups_all
1674 WHERE srp_pay_group_id = l_srp_pay_group_id;
1675
1676 -- raise business event
1677 l_paygroup_assign_rec.srp_pay_group_id := l_srp_pay_group_id;
1678 business_event
1679 (p_operation => 'Remove',
1680 p_paygroup_assign_rec => l_paygroup_assign_rec);
1681
1682 SELECT count (*), min(start_date),nvl(max(end_date),l_null_date) end_date
1683 INTO l_count,l_start_date,l_end_date
1684 FROM cn_srp_pay_groups_all
1685 WHERE salesrep_id = p_paygroup_assign_rec.salesrep_id
1686 AND org_id = p_paygroup_assign_rec.org_id;
1687
1688 --Modified for bug fix 3137894.
1689
1690 IF l_count = 0 THEN
1691 FOR roles IN get_roles(p_paygroup_assign_rec.salesrep_id)
1692 LOOP
1693 FOR role_plans IN get_role_plans(roles.role_id)
1694 LOOP
1695 srp_plan_assignment_for_delete
1696 (p_role_id => role_plans.role_id,
1697 p_role_plan_id => role_plans.role_plan_id,
1698 p_salesrep_id => p_paygroup_assign_rec.salesrep_id,
1699 p_org_id => p_paygroup_assign_rec.org_id,
1700 x_return_status => x_return_status,
1701 p_loading_status => l_loading_status,
1702 x_loading_status => x_loading_status);
1703
1704 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1705 RAISE FND_API.G_EXC_ERROR;
1706 END IF;
1707 END LOOP ;
1708 END LOOP;
1709 END IF;
1710
1711 IF l_count > 0 THEN
1712 FOR paygroups in get_srp_pg(p_paygroup_assign_rec.salesrep_id)
1713 LOOP
1714 FOR roles IN get_roles(p_paygroup_assign_rec.salesrep_id)
1715 LOOP
1716 IF ((roles.start_date <= paygroups.start_date AND roles.end_date >=
1717 paygroups.start_date) OR
1718 (roles.start_date <= paygroups.end_date AND roles.end_date >=
1719 paygroups.end_date ) ) THEN
1720
1721 FOR role_plans IN get_role_plans(roles.role_id)
1722 LOOP
1723 cn_srp_plan_assigns_pvt.Update_Srp_Plan_Assigns
1724 ( p_api_version => 1.0,
1725 x_return_status => x_return_status,
1726 x_msg_count => x_msg_count,
1727 x_msg_data => x_msg_data,
1728 p_srp_role_id => roles.srp_role_id,
1729 p_role_plan_id => role_plans.role_plan_id,
1730 x_loading_status => x_loading_status );
1731 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1732 RAISE FND_API.G_EXC_ERROR;
1733 END IF;
1734 END LOOP;
1735 END if;
1736 FOR plans IN get_plan_assigns
1737 (roles.role_id,p_paygroup_assign_rec.salesrep_id)
1738 LOOP
1739 -- Added to check the start_date and end_date of plan assignment, populate the intersection
1740 -- part with the pay group assignment date.
1741
1742 IF nvl(plans.end_date,l_null_date) > nvl(paygroups.end_date,l_null_date) THEN
1743 plans.end_date := l_end_date;
1744 END IF;
1745 IF plans.start_date < paygroups.start_date THEN
1746 plans.start_date := l_start_date;
1747 END IF;
1748
1749 IF nvl(plans.end_date, l_null_date) > plans.start_date THEN
1750 cn_srp_periods_pvt.create_srp_periods
1751 ( p_api_version => p_api_version,
1752 x_return_status => x_return_status,
1753 x_msg_count => x_msg_count,
1754 x_msg_data => x_msg_data,
1755 p_salesrep_id => p_paygroup_assign_rec.salesrep_id,
1756 p_role_id => roles.role_id,
1757 p_comp_plan_id => plans.comp_plan_id,
1758 p_start_date => plans.start_date,
1759 p_end_date => plans.end_date,
1760 x_loading_status => x_loading_status);
1761 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1762 RAISE FND_API.G_EXC_ERROR;
1763 END IF;
1764 END IF;
1765 END LOOP;
1766 END LOOP;
1767 END LOOP;
1768 END IF;
1769
1770
1771 -- End of API body
1772
1773 -- Standard check of p_commit.
1774
1775
1776 IF FND_API.To_Boolean( p_commit ) THEN
1777 COMMIT WORK;
1778 END IF;
1779
1780
1781 -- Standard call to get message count and if count is 1, get message info.
1782
1783 FND_MSG_PUB.Count_And_Get
1784 (
1785 p_count => x_msg_count ,
1786 p_data => x_msg_data ,
1787 p_encoded => FND_API.G_FALSE
1788 );
1789
1790 EXCEPTION
1791 WHEN FND_API.G_EXC_ERROR THEN
1792 ROLLBACK TO Delete_Srp_Pay_Group;
1793 x_return_status := FND_API.G_RET_STS_ERROR ;
1794 FND_MSG_PUB.Count_And_Get
1795
1796
1797 (
1798 p_count => x_msg_count ,
1799 p_data => x_msg_data ,
1800 p_encoded => FND_API.G_FALSE
1801 );
1802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1803 ROLLBACK TO Delete_Srp_Pay_Group;
1804 x_loading_status := 'UNEXPECTED_ERR';
1805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1806 FND_MSG_PUB.Count_And_Get
1807 (
1808 p_count => x_msg_count ,
1809 p_data => x_msg_data ,
1810 p_encoded => FND_API.G_FALSE
1811 );
1812 WHEN OTHERS THEN
1813 ROLLBACK TO Delete_Srp_Pay_Group;
1814 x_loading_status := 'UNEXPECTED_ERR';
1815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1816 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1817 THEN
1818 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1819 END IF;
1820 FND_MSG_PUB.Count_And_Get
1821 (
1822 p_count => x_msg_count ,
1823 p_data => x_msg_data ,
1824 p_encoded => FND_API.G_FALSE
1825 );
1826 END Delete_Srp_Pay_Group;
1827
1828 -- --------------------------------------------------------------------------*
1829 -- Procedure: Delete_Mass_Asgn_Srp_Pay_Groups
1830 -- --------------------------------------------------------------------------*
1831
1832 PROCEDURE Delete_Mass_Asgn_Srp_Pay
1833 (p_api_version IN NUMBER,
1834 p_init_msg_list IN VARCHAR2,
1835 p_commit IN VARCHAR2,
1836 p_validation_level IN NUMBER,
1837 x_return_status OUT NOCOPY VARCHAR2,
1838 x_msg_count OUT NOCOPY NUMBER,
1839 x_msg_data OUT NOCOPY VARCHAR2,
1840 p_srp_role_id IN NUMBER,
1841 p_role_pay_group_id IN NUMBER,
1842 x_loading_status OUT NOCOPY VARCHAR2
1843 ) IS
1844
1845 l_return_status VARCHAR2(2000);
1846 l_msg_count NUMBER;
1847 l_msg_data VARCHAR2(2000);
1848 l_srp_pay_group_id cn_srp_pay_groups.srp_pay_group_id%TYPE;
1849 l_loading_status VARCHAR2(2000);
1850
1851 newrec CN_Srp_PayGroup_PVT.PayGroup_assign_rec;
1852 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
1853 l_pay_group_id cn_pay_groups.pay_group_id%TYPE;
1854 l_pg_start_date cn_pay_groups.start_date%TYPE;
1855 l_pg_end_date cn_pay_groups.end_date%TYPE;
1856 l_srp_start_date cn_srp_roles.start_date%TYPE;
1857 l_srp_end_date cn_pmt_plans.end_date%TYPE;
1858 l_start_date cn_srp_pay_groups.start_date%TYPE;
1859 l_end_date cn_srp_pay_groups.start_date%TYPE;
1860 l_org_id cn_srp_pay_groups.org_id%TYPE;
1861 l_lock_flag cn_srp_pay_groups.lock_flag%TYPE;
1862 l_count NUMBER;
1863 l_null_date CONSTANT DATE := to_date('12/31/9999','MM/DD/YYYY');
1864
1865 BEGIN
1866 -- vensrini. Fix to delete role assignments when resource is in two orgs.
1867 SELECT org_id
1868 INTO l_org_id
1869 FROM cn_role_pay_groups
1870 WHERE role_pay_group_id = p_role_pay_group_id;
1871
1872 select salesrep_id, start_date, end_date
1873 into l_salesrep_id, l_srp_start_date, l_srp_end_date
1874 from cn_srp_roles
1875 where srp_role_id = p_srp_role_id
1876 AND org_id = l_org_id; -- vensrini
1877
1878 -- make sure dates overlap
1879 SELECT COUNT(1) INTO l_count
1880 FROM cn_role_pay_groups
1881 WHERE role_pay_group_id = p_role_pay_group_id
1882 AND Greatest(l_srp_start_date, start_date) <=
1883 Least(Nvl(l_srp_end_date, l_null_date),
1884 Nvl(end_date, l_null_date));
1885
1886 IF l_count = 0 THEN
1887 -- nothing to do... return
1888 RETURN;
1889 END IF;
1890
1891 BEGIN
1892
1893 select spp.start_date, spp.end_date, spp.salesrep_id,
1894 spp.lock_flag,cpp.pay_group_id, spp.org_id
1895 into l_start_date, l_end_date, l_salesrep_id,
1896 l_lock_flag,l_pay_group_id, l_org_id
1897 from cn_srp_pay_groups_all spp, cn_pay_groups_all cpp
1898 where spp.role_pay_group_id = p_role_pay_group_id
1899 AND spp.salesrep_id = l_salesrep_id
1900 AND cpp.pay_group_id = spp.pay_group_id
1901 AND Greatest(spp.start_date, l_srp_start_date) <=
1902 Least(Nvl(spp.end_date,l_null_date),
1903 Nvl(l_srp_end_date,l_null_date));
1904 EXCEPTION
1905 WHEN no_data_found THEN
1906 RAISE FND_API.G_EXC_ERROR;
1907
1908 END;
1909
1910 IF l_lock_flag = 'Y'
1911 THEN
1912 RAISE FND_API.G_EXC_ERROR;
1913 END IF;
1914 SELECT count(*)
1915 into l_count
1916 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
1917 cn_payruns_all prun
1918 WHERE w.salesrep_id = l_salesrep_id
1919 AND w.org_id = l_org_id
1920 AND prun.pay_period_id = prd.period_id
1921 AND prun.payrun_id = w.payrun_id
1922 AND prun.pay_group_id = l_pay_group_id
1923 AND prd.org_id = l_org_id
1924 AND ((prd.start_date BETWEEN l_start_date AND nvl(l_end_date,l_null_date)) OR
1925 (prd.end_date between l_start_date AND nvl(l_end_date,l_null_date)) );
1926
1927 IF l_count > 0
1928 THEN
1929 -- Making it a direct assignment if paysheets exist - for Bug 5557049.
1930 Update cn_srp_pay_groups_all
1931 set role_pay_group_id = null
1932 where role_pay_group_id = p_role_pay_group_id
1933 and salesrep_id = l_salesrep_id
1934 and org_id = l_org_id;
1935 RAISE FND_API.G_EXC_ERROR;
1936 END IF;
1937
1938 newrec.assignment_start_date := l_start_date;
1939 newrec.assignment_end_date := l_end_date;
1940 newrec.salesrep_id := l_salesrep_id;
1941 newrec.org_id := l_org_id;
1942 newrec.pay_group_id := l_pay_group_id;
1943
1944 delete_srp_pay_group
1945 (
1946 p_api_version => 1.0,
1947 x_return_status => l_return_status,
1948 x_loading_status => l_loading_status,
1949 x_msg_count => l_msg_count,
1950 x_msg_data => l_msg_data,
1951 p_paygroup_assign_rec=> newrec);
1952
1953 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1954 RAISE fnd_api.g_exc_error;
1955 END IF;
1956
1957 x_return_status := l_return_status;
1958 x_loading_status := l_loading_status;
1959 EXCEPTION
1960 WHEN FND_API.G_EXC_ERROR THEN
1961 NULL;
1962
1963 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1964 NULL;
1965 WHEN OTHERS THEN
1966 NULL;
1967
1968
1969 END Delete_Mass_Asgn_Srp_Pay;
1970
1971 END CN_Srp_PayGroup_PVT ;