[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_ROLE_RELATE_VUHK
Source
1 PACKAGE BODY JTF_RS_ROLE_RELATE_VUHK AS
2 -- $Header: cnisrrlb.pls 120.9.12010000.2 2008/10/13 09:56:33 vakulkar ship $
3
4 /***********************************************************************
5 This is a user hook API for the jtf_rs_role_relate_pvt package
6 ************************************************************************/
7
8 -- declare global variables...
9 G_PKG_NAME CONSTANT VARCHAR2(30) := 'jtf_rs_role_relate_vuhk';
10 G_PAYEE_ROLE CONSTANT NUMBER := 54;
11
12 -- global variables to pass values from update_res_role_relate_pre to
13 -- update_res_role_relate_post
14 -- NOTE: this IS OKAY in the jsp environment since they are only referenced
15 -- in the pre and post procedures, where they are called one after another
16 -- with the SAME connection object
17 g_group_id NUMBER;
18 g_start_date_old DATE;
19 g_end_date_old DATE;
20 g_manager_flag VARCHAR2(1);
21 g_event_log_id NUMBER;
22 g_resource_id NUMBER;
23
24 -- clku: TEAM ROLE enhancement
25 -- Global variable for connecting pre and post update hooks
26 g_tm_start_date_old DATE;
27 g_tm_end_date_old DATE;
28 g_team_id NUMBER;
29 g_team_name VARCHAR2(30);
30
31 -- should we display the debug messages? comment out this line if not.
32 PROCEDURE debugmsg (msg VARCHAR2) IS
33 BEGIN
34 -- dbms_output.put_line(msg); -- comment me out before checking in file :-)
35 null;
36 END debugmsg;
37
38 -- these procedures copied straight out of cn_srp_roles_pub API
39
40 -- ==========================================================================
41 -- Procedure: srp_plan_assignment_for_insert
42 -- already in single-org context
43 -- ==========================================================================
44 PROCEDURE srp_plan_assignment_for_insert
45 (p_role_id IN cn_roles.role_id%TYPE,
46 p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
47 x_return_status OUT NOCOPY VARCHAR2,
48 p_loading_status IN VARCHAR2,
49 x_loading_status OUT NOCOPY VARCHAR2 ) IS
50
51 CURSOR l_cur IS
52 SELECT role_plan_id, create_module
53 FROM cn_role_plans
54 WHERE role_id = p_role_id;
55
56 l_rec l_cur%ROWTYPE;
57
58 l_return_status VARCHAR2(200);
59 l_msg_count NUMBER;
60 l_msg_data VARCHAR2(2000);
61 l_srp_plan_assign_id cn_srp_plan_assigns.srp_plan_assign_id%TYPE;
62 l_loading_status VARCHAR2(2000);
63
64 BEGIN
65
66 -- Initialize API return status to success
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68 x_loading_status := p_loading_status;
69
70 FOR l_rec IN l_cur LOOP
71 debugmsg('insert into cn_srp_plan_assigns...');
72 debugmsg('p_srp_role_id = ' || p_srp_role_id);
73 debugmsg('l_rec.role_plan_id = ' || l_rec.role_plan_id);
74
75 cn_srp_plan_assigns_pvt.create_srp_plan_assigns
76 (p_api_version => 1.0,
77 x_return_status => l_return_status,
78 x_msg_count => l_msg_count,
79 x_msg_data => l_msg_data,
80 p_srp_role_id => p_srp_role_id,
81 p_role_plan_id => l_rec.role_plan_id,
82 x_srp_plan_assign_id => l_srp_plan_assign_id,
83 x_loading_status => l_loading_status);
84
85 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
86 x_return_status := l_return_status;
87 x_loading_status := l_loading_status;
88 EXIT;
89 END IF;
90
91 END LOOP;
92 END srp_plan_assignment_for_insert;
93
94 -- ==========================================================================
95 -- Procedure: srp_pmt_plan_asgn_for_insert
96 -- already in single-org context
97 -- ==========================================================================
98 PROCEDURE srp_pmt_plan_asgn_for_insert
99 (p_role_id IN cn_roles.role_id%TYPE,
100 p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
101 x_return_status OUT NOCOPY VARCHAR2,
102 p_loading_status IN VARCHAR2,
103 x_loading_status OUT NOCOPY VARCHAR2 ) IS
104
105 CURSOR l_cur IS
106 SELECT role_pmt_plan_id
107 FROM cn_role_pmt_plans
108 WHERE role_id = p_role_id;
109
110 l_rec l_cur%ROWTYPE;
111
112 l_return_status VARCHAR2(200);
113 l_msg_count NUMBER;
114 l_msg_data VARCHAR2(2000);
115 l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
116 l_loading_status VARCHAR2(2000);
117
118 BEGIN
119
120 -- Initialize API return status to success
121 x_return_status := FND_API.G_RET_STS_SUCCESS;
122 x_loading_status := p_loading_status;
123
124 FOR l_rec IN l_cur LOOP
125 debugmsg('insert into cn_srp_pmt_plans...');
126 debugmsg('p_srp_role_id = ' || p_srp_role_id);
127 debugmsg('l_rec.role_pmt_plan_id = ' || l_rec.role_pmt_plan_id);
128
129 cn_srp_pmt_plans_pvt.create_mass_asgn_srp_pmt_plan
130 (p_api_version => 1.0,
131 x_return_status => l_return_status,
132 x_msg_count => l_msg_count,
133 x_msg_data => l_msg_data,
134 p_srp_role_id => p_srp_role_id,
135 p_role_pmt_plan_id => l_rec.role_pmt_plan_id,
136 x_srp_pmt_plan_id => l_srp_pmt_plan_id,
137 x_loading_status => l_loading_status);
138
139 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
140 x_return_status := l_return_status;
141 x_loading_status := l_loading_status;
142 EXIT;
143 END IF;
144
145 END LOOP;
146 END srp_pmt_plan_asgn_for_insert;
147
148 -- ==========================================================================
149 -- Procedure: srp_pay_groups_asgn_for_insert
150 -- already in single-org context
151 -- ==========================================================================
152 PROCEDURE srp_pay_groups_asgn_for_insert
153 (p_role_id IN cn_roles.role_id%TYPE,
154 p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
155 x_return_status OUT NOCOPY VARCHAR2,
156 p_loading_status IN VARCHAR2,
157 x_loading_status OUT NOCOPY VARCHAR2 ) IS
158
159 CURSOR l_cur IS
160 SELECT role_pay_group_id
161 FROM cn_role_pay_groups
162 WHERE role_id = p_role_id;
163
164 l_rec l_cur%ROWTYPE;
165
166 l_return_status VARCHAR2(200);
167 l_msg_count NUMBER;
168 l_msg_data VARCHAR2(2000);
169 l_srp_pay_group_id cn_srp_pay_groups.srp_pay_group_id%TYPE;
170 l_loading_status VARCHAR2(2000);
171
172 BEGIN
173
174 -- Initialize API return status to success
175 x_return_status := FND_API.G_RET_STS_SUCCESS;
176 x_loading_status := p_loading_status;
177
178 FOR l_rec IN l_cur LOOP
179 debugmsg('insert into cn_srp_pay_groups...');
180 debugmsg('p_srp_role_id = ' || p_srp_role_id);
181 debugmsg('l_rec.role_pay_group_id = ' || l_rec.role_pay_group_id);
182
183 -- strange to call PUB here, but there for historical reason
184 cn_srp_paygroup_pub.create_mass_asgn_srp_pay
185 (p_api_version => 1.0,
186 x_return_status => l_return_status,
187 x_msg_count => l_msg_count,
188 x_msg_data => l_msg_data,
189 p_srp_role_id => p_srp_role_id,
190 p_role_pay_group_id => l_rec.role_pay_group_id,
191 x_srp_pay_group_id => l_srp_pay_group_id,
192 x_loading_status => l_loading_status);
193
194 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
195 x_return_status := l_return_status;
196 x_loading_status := l_loading_status;
197 EXIT;
198 END IF;
199
200 END LOOP;
201 END srp_pay_groups_asgn_for_insert;
202
203 -- ==========================================================================
204 -- Procedure: ins_srp_intel_prd
205 -- already in single-org context
206 -- ==========================================================================
207 PROCEDURE ins_srp_intel_prd
208 (p_salesrep_id IN cn_srp_roles.salesrep_id%TYPE,
209 p_start_date IN cn_srp_roles.start_date%TYPE,
210 p_end_date IN cn_srp_roles.end_date%TYPE,
211 x_msg_count OUT NOCOPY NUMBER,
212 x_msg_data OUT NOCOPY VARCHAR2,
213 x_return_status OUT NOCOPY VARCHAR2,
214 p_loading_status IN VARCHAR2,
215 x_loading_status OUT NOCOPY VARCHAR2) IS
216
217 CURSOR l_cur(l_srp_id IN NUMBER,
218 l_s_prd_id IN NUMBER,
219 l_e_prd_id IN NUMBER) IS
220 SELECT a1.period_id, a1.start_date, a1.end_date
221 FROM cn_period_statuses a1, cn_repositories r
222 WHERE (a1.period_id BETWEEN l_s_prd_id AND l_e_prd_id)
223 AND a1.period_status in ('O', 'F')
224 AND a1.period_set_id = r.period_set_id
225 AND a1.period_type_id = r.period_type_id
226 AND a1.org_id = r.org_id
227 AND NOT exists ( SELECT * FROM cn_srp_intel_periods a2
228 WHERE a2.salesrep_id = l_srp_id AND
229 a2.period_id = a1.period_id AND
230 a2.org_id = a1.org_id);
231
232 l_rec l_cur%ROWTYPE;
233 l_start_period_id NUMBER;
234 l_end_period_id NUMBER;
235 l_api_name CONSTANT VARCHAR2(30) := 'ins_srp_intel_prd';
236 l_org_id NUMBER;
237
238 BEGIN
239 -- Initialize API return status to success
240 x_return_status := FND_API.G_RET_STS_SUCCESS;
241 x_loading_status := p_loading_status;
242
243 -- get org id
244 l_org_id := mo_global.get_current_org_id;
245
246 -- bug 1942390 hlchen
247 --l_start_period_id := cn_api.get_acc_period_id(p_start_date);
248 l_start_period_id := cn_api.get_acc_period_id_fo(p_start_date, l_org_id);
249 l_end_period_id := cn_api.get_acc_period_id(p_end_date, l_org_id);
250
251 debugmsg(' p_start_date = ' || p_start_date ||
252 ' p_end_date = ' || p_end_date ||
253 ' l_start_period_id = ' || l_start_period_id ||
254 ' l_end_period_id = ' || l_end_period_id);
255
256 FOR l_rec IN l_cur(p_salesrep_id, l_start_period_id, l_end_period_id) LOOP
257 debugmsg(' salesrep_id = ' || p_salesrep_id ||
258 ' l_rec.period_id = ' || l_rec.period_id);
259
260 cn_intel_calc_pkg.insert_row
261 (x_srp_intel_period_id => '',
262 x_salesrep_id => p_salesrep_id,
263 x_org_id => l_org_id,
264 x_period_id => l_rec.period_id,
265 x_start_date => l_rec.start_date,
266 x_end_date => l_rec.end_date,
267 x_processing_status_code => 'CLEAN',
268 x_process_all_flag => 'Y',
269 x_attribute_category => '',
270 x_attribute1 => '',
271 x_attribute2 => '',
272 x_attribute3 => '',
273 x_attribute4 => '',
274 x_attribute5 => '',
275 x_attribute6 => '',
276 x_attribute7 => '',
277 x_attribute8 => '',
278 x_attribute9 => '',
279 x_attribute10 => '',
280 x_attribute11 => '',
281 x_attribute12 => '',
282 x_attribute13 => '',
283 x_attribute14 => '',
284 x_attribute15 => '',
285 x_created_by => fnd_global.user_id,
286 x_creation_date => sysdate,
287 x_last_update_login => fnd_global.login_id,
288 x_last_update_date => sysdate,
289 x_last_updated_by => fnd_global.user_id);
290 END LOOP;
291 EXCEPTION
292 WHEN OTHERS THEN
293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
294 x_loading_status := 'UNEXPECTED_ERR';
295 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
296 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name );
297 END IF;
298 FND_MSG_PUB.Count_And_Get
299 (p_count => x_msg_count ,
300 p_data => x_msg_data ,
301 p_encoded => FND_API.G_FALSE);
302 END ins_srp_intel_prd;
303
304 -- ==========================================================================
305 -- Procedure: srp_plan_assignment_for_update
306 -- already in single-org context
307 -- ==========================================================================
308 PROCEDURE srp_plan_assignment_for_update
309 (p_role_id IN cn_roles.role_id%TYPE,
310 p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
311 p_date_update_only IN VARCHAR2,
312 x_return_status OUT NOCOPY VARCHAR2,
313 p_loading_status IN VARCHAR2,
314 x_loading_status OUT NOCOPY VARCHAR2 ) IS
315
316
317 CURSOR l_cur IS
318 SELECT role_plan_id, create_module
319 FROM cn_role_plans
320 WHERE role_id = p_role_id;
321
322 l_rec l_cur%ROWTYPE;
323 l_return_status VARCHAR2(2000);
324 l_msg_count NUMBER;
325 l_msg_data VARCHAR2(2000);
326 l_srp_plan_assign_id cn_srp_plan_assigns.srp_plan_assign_id%TYPE;
327 l_loading_status VARCHAR2(2000);
328
329 BEGIN
330 -- Initialize API return status to success
331 x_return_status := FND_API.G_RET_STS_SUCCESS;
332 x_loading_status := p_loading_status;
333
334 IF (p_date_update_only = FND_API.G_TRUE) THEN
335 FOR l_rec IN l_cur LOOP
336 debugmsg('update cn_srp_plan_assigns.......');
337 debugmsg('p_srp_role_id = ' || p_srp_role_id);
338 debugmsg('l_rec.role_plan_id = ' || l_rec.role_plan_id);
339
340 cn_srp_plan_assigns_pvt.update_srp_plan_assigns
341 (p_api_version => 1.0,
342 x_return_status => l_return_status,
343 x_msg_count => l_msg_count,
344 x_msg_data => l_msg_data,
345 p_srp_role_id => p_srp_role_id,
346 p_role_plan_id => l_rec.role_plan_id,
347 x_loading_status => l_loading_status);
348
349 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
350 x_return_status := l_return_status;
351 x_loading_status := l_loading_status;
352 EXIT;
353 END IF;
354 END LOOP;
355
356 ELSE -- updating whole assignment
357
358 FOR l_rec IN l_cur LOOP
359 cn_srp_plan_assigns_pvt.delete_srp_plan_assigns
360 (p_api_version => 1.0,
361 x_return_status => l_return_status,
362 x_msg_count => l_msg_count,
363 x_msg_data => l_msg_data,
364 p_srp_role_id => p_srp_role_id,
365 p_role_plan_id => l_rec.role_plan_id,
366 x_loading_status => l_loading_status);
367
368 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
369 x_return_status := l_return_status;
370 x_loading_status := l_loading_status;
371 EXIT;
372 END IF;
373
374 cn_srp_plan_assigns_pvt.create_srp_plan_assigns
375 (p_api_version => 1.0,
376 x_return_status => l_return_status,
377 x_msg_count => l_msg_count,
378 x_msg_data => l_msg_data,
379 p_srp_role_id => p_srp_role_id,
380 p_role_plan_id => l_rec.role_plan_id,
381 x_srp_plan_assign_id => l_srp_plan_assign_id,
382 x_loading_status => l_loading_status);
383
384 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
385 x_return_status := l_return_status;
386 x_loading_status := l_loading_status;
387 EXIT;
388 END IF;
389
390 END LOOP;
391 END IF;
392
393 END srp_plan_assignment_for_update;
394
395 -- ==========================================================================
396 -- Procedure: srp_pmt_plan_asgn_for_update
397 -- already in single-org context
398 -- ==========================================================================
399 PROCEDURE srp_pmt_plan_asgn_for_update
400 (p_role_id IN cn_roles.role_id%TYPE,
401 p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
402 p_date_update_only IN VARCHAR2,
403 x_return_status OUT NOCOPY VARCHAR2,
404 p_loading_status IN VARCHAR2,
405 x_loading_status OUT NOCOPY VARCHAR2 ) IS
406
407
408 CURSOR l_cur IS
409 SELECT role_pmt_plan_id
410 FROM cn_role_pmt_plans
411 WHERE role_id = p_role_id;
412
413 l_rec l_cur%ROWTYPE;
414 l_return_status VARCHAR2(2000);
415 l_msg_count NUMBER;
416 l_msg_data VARCHAR2(2000);
417 l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
418 l_loading_status VARCHAR2(2000);
419 created_in_osc BOOLEAN;
420
421 BEGIN
422
423 -- Initialize API return status to success
424 x_return_status := FND_API.G_RET_STS_SUCCESS;
425 x_loading_status := p_loading_status;
426
427 FOR l_rec IN l_cur LOOP
428
429 debugmsg('update cn_srp_pmt_plans.......');
430 debugmsg('p_srp_role_id = ' || p_srp_role_id);
431 debugmsg('l_rec.role_pmt_plan_id = ' || l_rec.role_pmt_plan_id);
432
433 cn_srp_pmt_plans_pvt.update_mass_asgn_srp_pmt_plan
434 (p_api_version => 1.0,
435 x_return_status => l_return_status,
436 x_msg_count => l_msg_count,
437 x_msg_data => l_msg_data,
438 p_srp_role_id => p_srp_role_id,
439 p_role_pmt_plan_id => l_rec.role_pmt_plan_id,
440 x_loading_status => l_loading_status);
441
442 debugmsg('l_return_status = ' || l_return_status);
443 debugmsg('l_msg_data = ' || l_msg_data);
444 debugmsg('l_msg_count = ' || l_msg_count);
445 debugmsg('l_loading_status = ' || l_loading_status);
446
447 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
448 x_return_status := l_return_status;
449 x_loading_status := l_loading_status;
450 EXIT;
451 END IF;
452 END LOOP;
453
454 END srp_pmt_plan_asgn_for_update;
455
456 -- ==========================================================================
457 -- Procedure: srp_pay_group_asgn_for_update
458 -- already in single-org context
459 -- ==========================================================================
460 PROCEDURE srp_pay_group_asgn_for_update
461 (p_role_id IN cn_roles.role_id%TYPE,
462 p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
463 p_date_update_only IN VARCHAR2,
464 x_return_status OUT NOCOPY VARCHAR2,
465 p_loading_status IN VARCHAR2,
466 x_loading_status OUT NOCOPY VARCHAR2 ) IS
467
468
469 CURSOR l_cur IS
470 SELECT role_pay_group_id
471 FROM cn_role_pay_groups
472 WHERE role_id = p_role_id;
473
474 l_rec l_cur%ROWTYPE;
475 l_return_status VARCHAR2(2000);
476 l_msg_count NUMBER;
477 l_msg_data VARCHAR2(2000);
478 l_srp_pay_group_id cn_srp_pay_groups.srp_pay_group_id%TYPE;
479 l_loading_status VARCHAR2(2000);
480
481 BEGIN
482 -- Initialize API return status to success
483 x_return_status := FND_API.G_RET_STS_SUCCESS;
484 x_loading_status := p_loading_status;
485
486 FOR l_rec IN l_cur LOOP
487
488 debugmsg('update cn_srp_pay_groups.......');
489 debugmsg('p_srp_role_id = ' || p_srp_role_id);
490 debugmsg('l_rec.role_pay_group_id = ' || l_rec.role_pay_group_id);
491
492 -- strange to call PUB here, but there for historical reason
493 cn_srp_paygroup_pub.update_mass_asgn_srp_pay
494 (p_api_version => 1.0,
495 x_return_status => l_return_status,
496 x_msg_count => l_msg_count,
497 x_msg_data => l_msg_data,
498 p_srp_role_id => p_srp_role_id,
499 p_role_pay_group_id => l_rec.role_pay_group_id,
500 x_srp_pay_group_id => l_srp_pay_group_id,
501 x_loading_status => l_loading_status);
502
503 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
504 x_return_status := l_return_status;
505 x_loading_status := l_loading_status;
506 EXIT;
507 END IF;
508 END LOOP;
509
510 END srp_pay_group_asgn_for_update;
511
512 -- ==========================================================================
513 -- Procedure: srp_plan_assignment_for_delete
514 -- already in single-org context
515 -- ==========================================================================
516 PROCEDURE srp_plan_assignment_for_delete
517 (p_role_id IN cn_roles.role_id%TYPE,
518 p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
519 x_return_status OUT NOCOPY VARCHAR2,
520 p_loading_status IN VARCHAR2,
521 x_loading_status OUT NOCOPY VARCHAR2 ) IS
522
523 CURSOR l_cur IS
524 SELECT role_plan_id
525 FROM cn_role_plans
526 WHERE role_id = p_role_id;
527
528 l_rec l_cur%ROWTYPE;
529 l_return_status VARCHAR2(2000);
530 l_msg_count NUMBER;
531 l_msg_data VARCHAR2(2000);
532 l_srp_plan_assign_id cn_srp_plan_assigns.srp_plan_assign_id%TYPE;
533 l_loading_status VARCHAR2(2000);
534
535 BEGIN
536 -- Initialize API return status to success
537 x_return_status := FND_API.G_RET_STS_SUCCESS;
538 x_loading_status := p_loading_status;
539
540 FOR l_rec IN l_cur LOOP
541 cn_srp_plan_assigns_pvt.delete_srp_plan_assigns
542 (p_api_version => 1.0,
543 p_init_msg_list => fnd_api.g_false,
544 p_commit => fnd_api.g_false,
545 p_validation_level => fnd_api.g_valid_level_full,
546 x_return_status => l_return_status,
547 x_msg_count => l_msg_count,
548 x_msg_data => l_msg_data,
549 p_srp_role_id => p_srp_role_id,
550 p_role_plan_id => l_rec.role_plan_id,
551 x_loading_status => l_loading_status);
552
553 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
554 x_return_status := l_return_status;
555 x_loading_status := l_loading_status;
556 EXIT;
557 END IF;
558 END LOOP;
559 END srp_plan_assignment_for_delete;
560
561 -- ==========================================================================
562 -- Procedure: srp_pmt_plan_asgn_for_delete
563 -- already in single-org context
564 -- ==========================================================================
565 PROCEDURE srp_pmt_plan_asgn_for_delete
566 (p_role_id IN cn_roles.role_id%TYPE,
567 p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
568 x_return_status OUT NOCOPY VARCHAR2,
569 p_loading_status IN VARCHAR2,
570 x_loading_status OUT NOCOPY VARCHAR2 ) IS
571
572 CURSOR l_cur IS
573 SELECT role_pmt_plan_id
574 FROM cn_role_pmt_plans
575 WHERE role_id = p_role_id;
576
577 l_rec l_cur%ROWTYPE;
578 l_return_status VARCHAR2(2000);
579 l_msg_count NUMBER;
580 l_msg_data VARCHAR2(2000);
581 l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
582 l_loading_status VARCHAR2(2000);
583
584 BEGIN
585 -- Initialize API return status to success
586 x_return_status := FND_API.G_RET_STS_SUCCESS;
587 x_loading_status := p_loading_status;
588
589 FOR l_rec IN l_cur LOOP
590 cn_srp_pmt_plans_pvt.delete_mass_asgn_srp_pmt_plan
591 (p_api_version => 1.0,
592 p_init_msg_list => fnd_api.g_false,
593 p_commit => fnd_api.g_false,
594 p_validation_level => fnd_api.g_valid_level_full,
595 x_return_status => l_return_status,
596 x_msg_count => l_msg_count,
597 x_msg_data => l_msg_data,
598 p_srp_role_id => p_srp_role_id,
599 p_role_pmt_plan_id => l_rec.role_pmt_plan_id,
600 x_loading_status => l_loading_status);
601
602 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
603 x_return_status := l_return_status;
604 x_loading_status := l_loading_status;
605 EXIT;
606 END IF;
607 END LOOP;
608 END srp_pmt_plan_asgn_for_delete;
609
610 -- ==========================================================================
611 -- Procedure: val_srp_pg_asgn_for_del
612 -- Validate if resource has worksheet
613 -- ==========================================================================
614 procedure val_srp_pg_asgn_for_del
615 (p_srp_role_id NUMBER,
616 x_return_status OUT NOCOPY VARCHAR2
617 ) IS
618 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
619 l_count number;
620
621 l_srp_start_period_id NUMBER;
622 l_pay_run_period_id NUMBER;
623
624 l_salesrep_id NUMBER;
625 l_max_pay_date DATE;
626 l_count_pay NUMBER;
627
628 cursor get_srp_roles IS
629 SELECT srp_role_id, org_id, start_date, salesrep_id
630 FROM cn_srp_roles
631 WHERE srp_role_id = p_srp_role_id;
632
633 BEGIN
634 FOR srp_role IN get_srp_roles LOOP
635 SELECT count(*)
636 INTO l_count
637 FROM cn_srp_roles srp, cn_srp_plan_assigns plan
638 WHERE srp.srp_role_id = srp_role.srp_role_id
639 AND srp.org_id = srp_role.org_id
640 AND srp.org_id = plan.org_id
641 AND srp.salesrep_id = plan.salesrep_id
642 AND ( srp.start_date > plan.end_date OR nvl(srp.end_date, l_null_date) < plan.start_date);
643
644 IF l_count = 0 THEN
645 -- There is comp plan for the resource. Need to check if there is a worksheet. If there is a worksheet
646 -- then throw exception
647 l_srp_start_period_id := cn_api.get_acc_period_id(srp_role.start_date, srp_role.org_id);
648 l_salesrep_id := srp_role.salesrep_id;
649
650
651
652 -- get count of worksheets
653 SELECT count(*) into l_count_pay
654 FROM cn_payment_worksheets W, cn_period_statuses prd, cn_payruns prun
655 WHERE w.salesrep_id = l_salesrep_id
656 AND w.org_id = srp_role.org_id
657 AND w.org_id = prd.org_id
658 AND prd.org_id = prun.org_id
659 AND prun.pay_period_id = prd.period_id
660 AND prun.payrun_id = w.payrun_id;
661
662 IF l_count_pay > 0 THEN
663 select max(pay_date) into l_max_pay_date
664 from cn_payment_worksheets W, cn_payruns prun
665 WHERE w.salesrep_id = l_salesrep_id
666 AND prun.payrun_id = w.payrun_id
667 AND prun.org_id = w.org_id
668 AND w.org_id = srp_role.org_id;
669
670 -- get the period's end date of max(pay_date) payruns
671 SELECT cn_api.get_acc_period_id(prd.end_date, srp_role.org_id)
672 into l_pay_run_period_id
673 FROM cn_payment_worksheets W, cn_period_statuses prd, cn_payruns prun
674 WHERE w.salesrep_id = l_salesrep_id
675 AND w.org_id = srp_role.org_id
676 AND w.org_id = prun.org_id
677 AND prun.org_id = prd.org_id
678 AND prun.pay_period_id = prd.period_id
679 AND prun.payrun_id = w.payrun_id
680 AND prun.pay_date = l_max_pay_date
681 AND ROWNUM = 1; -- this check is for offcycle payruns created with the same pay dates.
682
683 IF l_srp_start_period_id <= l_pay_run_period_id THEN
684 -- Modified by chanthon for bug 5525795 - User friendly error message requested
685 -- before throwing the vertical hook error.
686 x_return_status := FND_API.G_RET_STS_ERROR;
687 -- RAISE FND_API.G_EXC_ERROR;
688 END IF;
689 END IF; -- l_count_pay check
690 END IF; -- l_count check
691 END LOOP;
692
693 END; --VALIDATE_SRP_PAY_GROUP_ASGN_FOR_DEL
694
695 -- ==========================================================================
696 -- Procedure: srp_pay_group_asgn_for_delete
697 -- already in single-org context
698 -- ==========================================================================
699 PROCEDURE srp_pay_group_asgn_for_delete
700 (p_role_id IN cn_roles.role_id%TYPE,
701 p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
702 x_return_status OUT NOCOPY VARCHAR2,
703 p_loading_status IN VARCHAR2,
704 x_loading_status OUT NOCOPY VARCHAR2 ) IS
705
706 CURSOR l_cur IS
707 SELECT role_pay_group_id
708 FROM cn_role_pay_groups
709 WHERE role_id = p_role_id;
710
711 l_rec l_cur%ROWTYPE;
712 l_return_status VARCHAR2(2000);
713 l_msg_count NUMBER;
714 l_msg_data VARCHAR2(2000);
715 l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
716 l_loading_status VARCHAR2(2000);
717
718 BEGIN
719 -- Initialize API return status to success
720 x_return_status := FND_API.G_RET_STS_SUCCESS;
721 x_loading_status := p_loading_status;
722 -- Removing this validation for bug 5557049
723 /* --Prevent delete if wrksheet exist - vensrini
724 val_srp_pg_asgn_for_del(p_srp_role_id => p_srp_role_id,
725 x_return_status => x_return_status);
726 --Prevent delete if wrksheet exist - vensrini
727
728 -- Added by chanthon for bug 5525795 - User friendly error message requested
729 -- before throwing the vertical hook error.
730 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
731 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
732 THEN
733 fnd_message.set_name ('CN', 'CN_SRP_PG_WS');
734 FND_MSG_PUB.add;
735 END IF;
736 x_loading_status := 'CN_SRP_PG_WS';
737 ElSE */
738 -- End: Added by chanthon --
739 -- End: Removed this validation for bug 5557049
740 FOR l_rec IN l_cur LOOP
741 cn_srp_paygroup_pvt.delete_mass_asgn_srp_pay
742 (p_api_version => 1.0,
743 p_init_msg_list => fnd_api.g_false,
744 p_commit => fnd_api.g_false,
745 p_validation_level => fnd_api.g_valid_level_full,
746 x_return_status => l_return_status,
747 x_msg_count => l_msg_count,
748 x_msg_data => l_msg_data,
749 p_srp_role_id => p_srp_role_id,
750 p_role_pay_group_id => l_rec.role_pay_group_id,
751 x_loading_status => l_loading_status);
752
753 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
754 x_return_status := l_return_status;
755 x_loading_status := l_loading_status;
756 EXIT;
757 END IF;
758 END LOOP;
759 -- END IF;
760 END srp_pay_group_asgn_for_delete;
761
762 -- ==========================================================================
763 -- Procedure: mark_notify
764 -- already in single-org context
765 -- ==========================================================================
766 PROCEDURE mark_notify
767 (p_salesrep_id NUMBER,
768 p_role_id NUMBER DEFAULT NULL,
769 p_group_id NUMBER,
770 p_operation VARCHAR2,
771 p_start_date DATE,
772 p_end_date DATE,
773 p_manager_flag VARCHAR2,
774 p_event_log_id NUMBER)
775 IS
776 l_srp cn_rollup_pvt.srp_group_rec_type;
777 l_srp_tbl cn_rollup_pvt.srp_group_tbl_type;
778 l_return_status VARCHAR2(30);
779 l_msg_count NUMBER;
780 l_msg_data VARCHAR2(256);
781 p_action_link_id NUMBER;
782 l_action_link_id NUMBER;
783 l_revert_to_state VARCHAR2(30);
784 l_action VARCHAR2(30);
785 l_start_date DATE;
786 l_end_date DATE;
787 l_org_id NUMBER;
788
789 -- cursor to find all periods in the date range for each srp
790 -- Assume: 1. p_start_date is not null
791 -- 2. p.start_date and p.end_date are not null
792 -- cursor to find all periods in the date range for each srp
793 CURSOR periods(p_salesrep_id NUMBER, p_start_date DATE, p_end_date DATE) IS
794 SELECT p.period_id,
795 greatest(p_start_date, p.start_date) start_date,
796 Decode(p_end_date, NULL, p.end_date,
797 Least(p_end_date, p.end_date)) end_date
798 FROM cn_srp_intel_periods p
799 WHERE p.salesrep_id = p_salesrep_id
800 AND p.org_id = l_org_id
801 AND (p_end_date IS NULL OR p.start_date <= p_end_date)
802 AND (p.end_date >= p_start_date);
803
804 BEGIN
805 -- get org id
806 l_org_id := mo_global.get_current_org_id;
807
808 IF (p_operation = 'I') THEN
809 l_revert_to_state := 'NCALC';
810 l_action := 'XROLL';
811 ELSIF (p_operation = 'D') THEN
812 l_revert_to_state := 'NCALC';
813 l_action := 'SOURCE_CLS';
814 END IF;
815
816 cn_mark_events_pkg.mark_notify_salesreps
817 (p_salesrep_id => p_salesrep_id,
818 p_org_id => l_org_id,
819 p_comp_group_id => p_group_id,
820 p_period_id => null,
821 p_start_date => p_start_date,
822 p_end_date => p_end_date,
823 p_revert_to_state => l_revert_to_state,
824 p_action => l_action,
825 p_action_link_id => NULL,
826 p_base_salesrep_id => NULL,
827 p_base_comp_group_id => NULL,
828 p_event_log_id => p_event_log_id,
829 x_action_link_id => p_action_link_id);
830
831 IF (p_operation = 'I') THEN
832 IF (p_manager_flag = 'N') THEN
833 l_revert_to_state := 'ROLL';
834 l_action := 'PULL_BELOW';
835 ELSE
836 l_revert_to_state := 'ROLL';
837 l_action := 'PULL';
838 END IF;
839 ELSE
840 l_revert_to_state := 'CALC';
841 l_action := 'DELETE_DEST';
842 END IF;
843
844 -- for each period active for this salesrep, call mark_notify_salesrep
845 FOR prd IN periods(p_salesrep_id, p_start_date, p_end_date) LOOP
846 cn_mark_events_pkg.mark_notify_salesreps
847 (p_salesrep_id => p_salesrep_id,
848 p_org_id => l_org_id,
849 p_comp_group_id => p_group_id,
850 p_period_id => prd.period_id,
851 p_start_date => prd.start_date,
852 p_end_date => prd.end_date,
853 p_revert_to_state => l_revert_to_state,
854 p_action => l_action,
855 p_action_link_id => p_action_link_id,
856 p_base_salesrep_id => NULL,
857 p_base_comp_group_id => NULL,
858 p_role_id => p_role_id,
859 p_event_log_id => p_event_log_id,
860 x_action_link_id => l_action_link_id);
861 END LOOP;
862
863 -- find the ancestors of l_salesrep and call mark_notify for all of them.
864 l_srp.salesrep_id := p_salesrep_id;
865 l_srp.group_id := p_group_id;
866 l_srp.start_date := p_start_date;
867 l_srp.end_date := p_end_date;
868
869 cn_rollup_pvt.get_ancestor_salesrep
870 (p_api_version => 1.0,
871 p_init_msg_list => FND_API.G_false,
872 p_commit => FND_API.G_false,
873 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
874 x_return_status => l_return_status,
875 x_msg_count => l_msg_count,
876 x_msg_data => l_msg_data,
877 p_srp => l_srp,
878 p_org_id => l_org_id,
879 x_srp => l_srp_tbl);
880
881 IF (l_srp_tbl.COUNT > 0) THEN
882 FOR i IN l_srp_tbl.first..l_srp_tbl.last LOOP
883 FOR prd IN periods(l_srp_tbl(i).salesrep_id,
884 l_srp_tbl(i).start_date,
885 l_srp_tbl(i).end_date) LOOP
886 cn_mark_events_pkg.mark_notify_salesreps
887 (p_salesrep_id => l_srp_tbl(i).salesrep_id,
888 p_org_id => l_org_id,
889 p_comp_group_id => l_srp_tbl(i).group_id,
890 p_period_id => prd.period_id,
891 p_start_date => prd.start_date,
892 p_end_date => prd.end_date,
893 p_revert_to_state => 'CALC',
894 p_action => NULL,
895 p_action_link_id => p_action_link_id,
896 p_base_salesrep_id => NULL,
897 p_base_comp_group_id => NULL,
898 p_event_log_id => p_event_log_id,
899 x_action_link_id => l_action_link_id);
900 END LOOP;
901 END LOOP;
902 END IF;
903
904 l_srp_tbl.DELETE;
905 END mark_notify;
906
907 -- helper procedure for the MOAC session context
908 PROCEDURE restore_context(p_acc_mode VARCHAR2,
909 p_org_id NUMBER) IS
910 BEGIN
911 IF p_acc_mode IS NOT NULL then
912 mo_global.set_policy_context(p_acc_mode, p_org_id);
913 END IF;
914 END restore_context;
915
916 -- ====================================================================
917 -- Here are the actual user hook procedures. They include ==
918 -- create, insert, and update for resourece roles ==
919 -- We call the "post" hooks on insert and update since these are ==
920 -- executed after the DML operations to resource roles, but we ==
921 -- call the "pre" hook on the delete ==
922 -- ====================================================================
923
924
925 /*for create resource role relate */
926
927 PROCEDURE create_res_role_relate_post
928 (P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
929 P_ROLE_RESOURCE_TYPE IN JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_TYPE%TYPE,
930 P_ROLE_RESOURCE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE,
931 P_ROLE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_ID%TYPE,
932 P_START_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE,
933 P_END_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE
934 DEFAULT NULL,
935 P_DATA OUT NOCOPY VARCHAR2,
936 P_COUNT OUT NOCOPY NUMBER,
937 P_RETURN_CODE OUT NOCOPY VARCHAR2) IS
938
939 l_srp_plan_assign_id cn_srp_plan_assigns.srp_plan_assign_id%TYPE;
940 x_loading_status VARCHAR2(30);
941 l_salesrep_id NUMBER;
942 l_org_id NUMBER;
943 l_orig_acc_mode VARCHAR2(1);
944 l_orig_org_id NUMBER;
945 l_usage VARCHAR2(30);
946 l_start_date DATE;
947 l_end_date DATE;
948 l_count NUMBER;
949 l_api_name VARCHAR2(30) := 'create_res_role_relate_post';
950
951 -- get the salesrep ID's, org ID's
952 CURSOR get_srp_org_info IS
953 select salesrep_id, org_id
954 from jtf_rs_salesreps
955 where resource_id = p_role_resource_id
956 AND p_role_resource_type = 'RS_INDIVIDUAL'
957 UNION ALL
958 select salesrep_id, org_id
959 from jtf_rs_group_members gm, jtf_rs_salesreps s
960 where gm.group_member_id = p_role_resource_id
961 and gm.resource_id = s.resource_id
962 and delete_flag = 'N'
963 AND p_role_resource_type = 'RS_GROUP_MEMBER'
964 UNION ALL
965 select salesrep_id, org_id
966 from jtf_rs_team_members tm, jtf_rs_salesreps s
967 where tm.team_member_id = p_role_resource_id
968 and tm.team_resource_id = s.resource_id
969 and resource_type = 'INDIVIDUAL'
970 and delete_flag = 'N'
971 AND p_role_resource_type = 'RS_TEAM_MEMBER';
972
973 -- the check for valid insert and the actual insert row to role relations
974 -- is done by the public API... this hook calls the srp_plan assignment,
975 -- linking salesreps to comp plans, and it inserts into
976 -- cn_srp_intel_periods for intelligent calculation.
977
978 -- for mark events
979 l_manager_flag VARCHAR2(1);
980 l_group_id NUMBER;
981 l_event_log_id NUMBER;
982 l_event_name VARCHAR2(30);
983
984 l_max_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
985
986 --clku, team member rs role mark event
987
988 l_tm_start_date_old DATE;
989 l_tm_end_date_old DATE;
990 l_team_id NUMBER;
991 l_team_name VARCHAR2(30);
992 l_team_event_name VARCHAR(60);
993 l_role_id NUMBER;
994 l_date_range_action_tbl cn_api.date_range_action_tbl_type;
995 l_role_resource_type jtf_rs_role_relations.role_resource_type%TYPE;
996
997 -- clku, bug 3718575
998 l_resource_category VARCHAR2(30);
999
1000 -- run in single-org context
1001 CURSOR team_member_role_relate IS
1002 SELECT t.start_date_active, t.end_date_active, rr.role_id,
1003 sr.salesrep_id, tm.team_id, t.team_name
1004 from jtf_rs_team_members tm,
1005 jtf_rs_salesreps sr,
1006 jtf_rs_team_usages tu,
1007 jtf_rs_role_relations rr,
1008 jtf_rs_roles_b rb,
1009 jtf_rs_teams_vl t
1010 where rr.role_relate_id = p_role_relate_id
1011 and rr.role_resource_type(+) = 'RS_TEAM_MEMBER'
1012 and tm.resource_type = 'INDIVIDUAL'
1013 and tm.delete_flag = 'N'
1014 and tu.team_id = tm.team_id
1015 and tu.usage = 'SALES_COMP'
1016 and sr.resource_id = tm.team_resource_id
1017 and (sr.org_id is null or sr.org_id = (select org_id from cn_repositories))
1018 and rr.role_resource_id(+) = tm.team_member_id
1019 and rr.delete_flag(+) = 'N'
1020 and rb.role_id(+) = rr.role_id
1021 and rb.role_type_code(+) = 'SALES_COMP'
1022 and t.team_id = tm.team_id;
1023
1024 -- cursor to get the information about this role relation (single-org)
1025 CURSOR role_relate_info IS
1026 SELECT r.manager_flag, r.group_id, s.salesrep_id
1027 FROM jtf_rs_group_usages u,
1028 jtf_rs_group_mbr_role_vl r,
1029 cn_rs_salesreps s,
1030 jtf_rs_roles_b ro
1031 WHERE r.role_relate_id = p_role_relate_id
1032 AND u.group_id = r.group_id
1033 AND u.usage = 'SALES_COMP'
1034 AND ro.role_id = r.role_id
1035 AND ro.role_type_code = 'SALES_COMP'
1036 AND s.resource_id = r.resource_id;
1037
1038 -- get the team info associated with the reps who are in turn
1039 -- associated with the role (single-org)
1040 CURSOR srp_team_relate_info (p_salesrep_id NUMBER,
1041 p_role_relate_id NUMBER) IS
1042 SELECT ct.name name,
1043 ct.comp_team_id team_id,
1044 greatest(r.start_date_active, ct.start_date_active) start_date,
1045 least(nvl(ct.end_date_active, l_max_date),
1046 nvl(r.end_date_active, l_max_date)) end_date
1047 FROM jtf_rs_group_usages u,
1048 jtf_rs_group_mbr_role_vl r,
1049 cn_rs_salesreps s, -- single-org view
1050 jtf_rs_roles_b ro,
1051 cn_srp_comp_teams_v srt,
1052 cn_comp_teams ct
1053 WHERE r.role_relate_id = p_role_relate_id
1054 AND s.salesrep_id = p_salesrep_id -- safe since single-org context
1055 AND u.group_id = r.group_id
1056 AND u.usage = 'SALES_COMP'
1057 AND ro.role_id = r.role_id
1058 AND ro.role_type_code = 'SALES_COMP'
1059 AND s.resource_id = r.resource_id
1060 AND s.salesrep_id = srt.salesrep_id
1061 AND srt.comp_team_id = ct.comp_team_id
1062 AND (r.start_date_active <= ct.start_date_active
1063 or r.start_date_active between ct.start_date_active
1064 and nvl (ct.end_date_active, r.start_date_active))
1065 AND nvl(r.end_date_active, ct.start_date_active) >= ct.start_date_active;
1066
1067 -- clku, bug 3718575 get the resource category information
1068 CURSOR resource_category_info IS
1069 SELECT category
1070 FROM jtf_rs_resource_extns
1071 where resource_id = P_ROLE_RESOURCE_ID;
1072
1073 BEGIN
1074 -- Initialize API return status to success
1075 p_return_code := fnd_api.g_ret_sts_success;
1076
1077 -- get usage for the role (can't fail)
1078 select role_type_code into l_usage
1079 from jtf_rs_roles_b
1080 where role_id = P_ROLE_ID;
1081
1082 -- only proceed if usage is SALES_COMP or SALES_COMP_PAYMENT_ANALIST
1083 IF l_usage <> 'SALES_COMP' AND l_usage <> 'SALES_COMP_PAYMENT_ANALIST' THEN
1084 RETURN;
1085 END IF;
1086
1087 -- see if salesrep tied to resource
1088 l_salesrep_id := NULL;
1089 l_org_id := NULL;
1090 OPEN get_srp_org_info;
1091 FETCH get_srp_org_info INTO l_salesrep_id, l_org_id;
1092 CLOSE get_srp_org_info;
1093
1094 -- if trying to assign a SALES_COMP role to a non-salesrep, then error
1095 IF l_salesrep_id IS NULL AND p_role_resource_type = 'RS_INDIVIDUAL' AND
1096 l_usage = 'SALES_COMP' THEN
1097 FND_MESSAGE.SET_NAME('CN', 'CN_RES_MUST_BE_SRP');
1098 -- A sales compensation role cannot be assigned to a resource
1099 -- that is not a salesperson.
1100 FND_MSG_PUB.ADD;
1101 RAISE FND_API.G_EXC_ERROR;
1102 end if;
1103
1104 IF p_role_resource_type = 'RS_INDIVIDUAL' THEN
1105 -- looks like spelling error but this is correct...
1106 IF l_usage = 'SALES_COMP_PAYMENT_ANALIST' THEN
1107 select count(1) into l_count
1108 from jtf_rs_role_relations rr, jtf_rs_roles_b r
1109 where rr.role_resource_id = P_ROLE_RESOURCE_ID
1110 and rr.role_resource_type = 'RS_INDIVIDUAL'
1111 and rr.delete_flag = 'N'
1112 and r.role_id = rr.role_id
1113 and r.role_type_code = 'SALES_COMP'
1114 -- Bug 4083951 by mnativ
1115 -- AND NVL(e1,s2) >= s2 AND s1 <= NVL(e2,s1)
1116 -- s1,e1 = IN params, s2,e2 = existing role assignment
1117 AND NVL(TRUNC(P_END_DATE_ACTIVE),TRUNC(rr.start_date_active))
1118 >= TRUNC(rr.start_date_active)
1119 AND TRUNC(P_START_DATE_ACTIVE)
1120 <= NVL(TRUNC(rr.end_date_active),TRUNC(P_START_DATE_ACTIVE));
1121
1122 if l_count <> 0 then
1123 FND_MESSAGE.SET_NAME('CN', 'CN_SRP_CANNOT_HAVE_ANAL_ROLE');
1124 -- A salesperson cannot be assigned a payment analyst role.
1125 FND_MSG_PUB.ADD;
1126 RAISE FND_API.G_EXC_ERROR;
1127 end if;
1128 end if; -- l_usage = 'SALES_COMP_PAYMENT_ANALIST'
1129
1130 IF l_usage = 'SALES_COMP' THEN
1131 select count(1) into l_count
1132 from jtf_rs_role_relations rr, jtf_rs_roles_b r
1133 where rr.role_resource_id = P_ROLE_RESOURCE_ID
1134 and rr.role_resource_type = 'RS_INDIVIDUAL'
1135 and rr.delete_flag = 'N'
1136 and r.role_id = rr.role_id
1137 and r.role_type_code = 'SALES_COMP_PAYMENT_ANALIST'
1138 -- Bug 4083951 by mnativ
1139 -- AND NVL(e1,s2) >= s2 AND s1 <= NVL(e2,s1)
1140 -- s1,e1 = IN params, s2,e2 = existing role assignment
1141 AND NVL(TRUNC(P_END_DATE_ACTIVE),TRUNC(rr.start_date_active))
1142 >= TRUNC(rr.start_date_active)
1143 AND TRUNC(P_START_DATE_ACTIVE)
1144 <= NVL(TRUNC(rr.end_date_active),TRUNC(P_START_DATE_ACTIVE));
1145
1146 if l_count <> 0 then
1147 FND_MESSAGE.SET_NAME('CN', 'CN_SRP_CANNOT_HAVE_ANAL_ROLE');
1148 -- A salesperson cannot be assigned a payment analyst role.
1149 FND_MSG_PUB.ADD;
1150 RAISE FND_API.G_EXC_ERROR;
1151 end if;
1152
1153 -- check PAYEE role
1154 IF p_role_id = G_PAYEE_ROLE then
1155 -- payee cannot have any other sales comp role
1156 select count(1) into l_count
1157 from jtf_rs_role_relations rr, jtf_rs_roles_b r
1158 where rr.role_resource_id = P_ROLE_RESOURCE_ID
1159 and rr.role_resource_type = 'RS_INDIVIDUAL'
1160 and rr.delete_flag = 'N'
1161 and r.role_id = rr.role_id
1162 and r.role_id <> G_PAYEE_ROLE
1163 and r.role_type_code = 'SALES_COMP';
1164
1165 if l_count <> 0 then
1166 FND_MESSAGE.SET_NAME('CN', 'CN_PAYEE_CANNOT_HAVE_SC_ROLE');
1167 -- A salesperson cannot be assigned both the Payee role
1168 -- and another sales compensation role.
1169 FND_MSG_PUB.ADD;
1170 RAISE FND_API.G_EXC_ERROR;
1171 end if;
1172 ELSE -- p_role_id <> G_PAYEE_ROLE
1173 -- NON-payee cannot have payee role
1174 select count(1) into l_count
1175 from jtf_rs_role_relations rr
1176 where rr.role_resource_id = P_ROLE_RESOURCE_ID
1177 and rr.role_resource_type = 'RS_INDIVIDUAL'
1178 and rr.delete_flag = 'N'
1179 and rr.role_id = G_PAYEE_ROLE;
1180
1181 if l_count <> 0 then
1182 FND_MESSAGE.SET_NAME('CN', 'CN_PAYEE_CANNOT_HAVE_SC_ROLE');
1183 -- A salesperson cannot be assigned both the Payee role
1184 -- and another sales compensation role.
1185 FND_MSG_PUB.ADD;
1186 RAISE FND_API.G_EXC_ERROR;
1187 end if; -- l_count <> 0
1188 end if; -- payee role ck
1189 end if; -- l_usage = 'SALES_COMP' ok
1190 END IF; -- if salesrep_id is not null
1191
1192 -- done with validation - now process data
1193 -- store MOAC session info in local variables
1194 l_orig_org_id := mo_global.get_current_org_id;
1195 l_orig_acc_mode := mo_global.get_access_mode;
1196
1197 -- loop through orgs
1198 FOR r IN get_srp_org_info LOOP
1199 mo_global.set_policy_context('S', r.org_id);
1200
1201 -- do mark event processing
1202 -- only return rows for SALES_COMP roles of RS_GROUP_MEMBER type
1203 IF fnd_profile.value('CN_MARK_EVENTS') = 'Y' THEN
1204 -- Team Member Role section
1205 IF P_ROLE_RESOURCE_TYPE = 'RS_TEAM_MEMBER' THEN
1206 OPEN team_member_role_relate;
1207 FETCH team_member_role_relate INTO
1208 l_tm_start_date_old, l_tm_end_date_old, l_role_id,
1209 l_salesrep_id, l_team_id, l_team_name;
1210 IF (team_member_role_relate%notfound) THEN
1211 CLOSE team_member_role_relate;
1212 ELSE
1213 CLOSE team_member_role_relate;
1214
1215 cn_api.get_date_range_diff_action
1216 ( start_date_new => P_START_DATE_ACTIVE
1217 ,end_date_new => P_END_DATE_ACTIVE
1218 ,start_date_old => l_tm_start_date_old
1219 ,end_date_old => l_tm_end_date_old
1220 ,x_date_range_action_tbl => l_date_range_action_tbl );
1221
1222 FOR i IN 1..l_date_range_action_tbl.COUNT LOOP
1223
1224 if l_date_range_action_tbl(i).action_flag = 'I' THEN
1225
1226 l_team_event_name := 'CHANGE_TEAM_ADD_REP';
1227 else
1228 l_team_event_name := 'CHANGE_TEAM_DEL_REP';
1229 end if;
1230
1231 cn_mark_events_pkg.mark_notify_team
1232 (P_TEAM_ID => l_team_id,
1233 P_TEAM_EVENT_NAME => l_team_event_name,
1234 P_TEAM_NAME => l_team_name,
1235 P_START_DATE_ACTIVE => l_date_range_action_tbl(i).start_date,
1236 P_END_DATE_ACTIVE => l_date_range_action_tbl(i).end_date,
1237 P_EVENT_LOG_ID => NULL,
1238 p_org_id => r.org_id);
1239
1240 END LOOP;
1241 END IF;
1242 END IF; -- RS_TEAM_MEMBER
1243 -- end Team Member Role section
1244
1245 IF P_ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER' THEN
1246 OPEN role_relate_info;
1247 FETCH role_relate_info
1248 INTO l_manager_flag, l_group_id,l_salesrep_id;
1249
1250 IF (role_relate_info%notfound) THEN
1251 CLOSE role_relate_info;
1252 ELSE
1253 CLOSE role_relate_info;
1254
1255 IF (l_manager_flag = 'N') THEN
1256 l_event_name := 'CHANGE_CP_ADD_SRP';
1257 ELSE
1258 l_event_name := 'CHANGE_CP_ADD_MGR';
1259 END IF;
1260
1261 cn_mark_events_pkg.log_event
1262 (p_event_name => l_event_name,
1263 p_object_name => NULL,
1264 p_object_id => p_role_relate_id,
1265 p_start_date => p_start_date_active,
1266 p_start_date_old => NULL,
1267 p_end_date => p_end_date_active,
1268 p_end_date_old => NULL,
1269 x_event_log_id => l_event_log_id,
1270 p_org_id => r.org_id);
1271 mark_notify
1272 (p_salesrep_id => l_salesrep_id,
1273 p_group_id => l_group_id,
1274 p_operation => 'I',
1275 p_start_date => p_start_date_active,
1276 p_end_date => p_end_date_active,
1277 p_manager_flag => l_manager_flag,
1278 p_event_log_id => l_event_log_id);
1279 END IF; -- if cursor%notfound
1280
1281 -- mark team related changes
1282 -- clku swap input para
1283 FOR srp_tm_rec IN srp_team_relate_info
1284 (l_salesrep_id, P_ROLE_RELATE_ID) LOOP
1285 if srp_tm_rec.end_date = l_max_date then
1286 srp_tm_rec.end_date := null;
1287 end if;
1288
1289 cn_mark_events_pkg.mark_notify_team
1290 (P_TEAM_ID => srp_tm_rec.team_id ,
1291 P_TEAM_EVENT_NAME => 'CHANGE_TEAM_ADD_REP',
1292 P_TEAM_NAME => srp_tm_rec.name,
1293 P_START_DATE_ACTIVE => srp_tm_rec.start_date,
1294 P_END_DATE_ACTIVE => srp_tm_rec.end_date,
1295 P_EVENT_LOG_ID => l_event_log_id,
1296 p_org_id => r.org_id);
1297 END LOOP;
1298 END IF; -- RS_GROUP_MEMBER
1299 end if; -- mark events on
1300
1301 -- =====================================================================
1302 -- only process rest of hook for RS_INDIVIDUAL type
1303 -- =====================================================================
1304 IF p_role_resource_type = 'RS_INDIVIDUAL' then
1305 -- we're all set to go - assign role
1306 x_loading_status := 'CN_INSERTED';
1307
1308 -- insert into the sales comp tables
1309 -- we're already in a loop to cycle through all the applicable orgs
1310 -- associated with the salesreps assigned to the given resource
1311 srp_plan_assignment_for_insert
1312 (p_role_id => P_ROLE_ID,
1313 p_srp_role_id => P_ROLE_RELATE_ID,
1314 x_return_status => P_RETURN_CODE,
1315 p_loading_status => x_loading_status,
1316 x_loading_status => x_loading_status);
1317
1318 IF (P_RETURN_CODE <> FND_API.G_RET_STS_SUCCESS ) THEN
1319 RAISE FND_API.G_EXC_ERROR;
1320 END IF;
1321
1322 srp_pmt_plan_asgn_for_insert
1323 (p_role_id => P_ROLE_ID,
1324 p_srp_role_id => P_ROLE_RELATE_ID,
1325 x_return_status => P_RETURN_CODE,
1326 p_loading_status => x_loading_status,
1327 x_loading_status => x_loading_status);
1328
1329 IF (P_RETURN_CODE <> FND_API.G_RET_STS_SUCCESS ) THEN
1330 RAISE FND_API.G_EXC_ERROR;
1331 END IF;
1332
1333 -- clku 3718575
1334 open resource_category_info;
1335 fetch resource_category_info into l_resource_category;
1336 close resource_category_info;
1337
1338 IF l_resource_category is not null then
1339 IF l_resource_category <> 'TBH' then
1340
1341 srp_pay_groups_asgn_for_insert
1342 (p_role_id => P_ROLE_ID,
1343 p_srp_role_id => P_ROLE_RELATE_ID,
1344 x_return_status => P_RETURN_CODE,
1345 p_loading_status => x_loading_status,
1346 x_loading_status => x_loading_status);
1347
1348 IF (P_RETURN_CODE <> FND_API.G_RET_STS_SUCCESS ) THEN
1349 RAISE FND_API.G_EXC_ERROR;
1350 END IF;
1351
1352 END IF; -- clku, not TBH
1353 END IF; -- clku not null
1354
1355 -- Insert into cn_srp_intel_periods for intelligent calculation
1356 ins_srp_intel_prd
1357 (p_salesrep_id => r.salesrep_id,
1358 p_start_date => p_start_date_active,
1359 p_end_date => p_end_date_active,
1360 x_msg_count => P_COUNT,
1361 x_msg_data => P_DATA,
1362 x_return_status => P_RETURN_CODE,
1363 p_loading_status => x_loading_status,
1364 x_loading_status => x_loading_status);
1365
1366 IF (P_RETURN_CODE <> FND_API.G_RET_STS_SUCCESS ) THEN
1367 RAISE FND_API.G_EXC_ERROR;
1368 END IF;
1369 END IF; -- RS_INDIVIDUAL
1370 END LOOP; -- orgs loop
1371
1372 -- restore context
1373 restore_context(l_orig_acc_mode, l_orig_org_id);
1374 -- end of API body
1375
1376 EXCEPTION
1377 WHEN FND_API.G_EXC_ERROR THEN
1378 P_RETURN_CODE := FND_API.G_RET_STS_ERROR ;
1379 restore_context(l_orig_acc_mode, l_orig_org_id);
1380 FND_MSG_PUB.Count_And_Get
1381 (p_count => p_count ,
1382 p_data => p_data ,
1383 p_encoded => FND_API.G_FALSE );
1384 WHEN OTHERS THEN
1385 p_return_code := fnd_api.g_ret_sts_unexp_error;
1386 restore_context(l_orig_acc_mode, l_orig_org_id);
1387 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1388 THEN
1389 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name );
1390 END IF;
1391 FND_MSG_PUB.count_and_get
1392 (p_count => p_count ,
1393 p_data => p_data ,
1394 p_encoded => FND_API.g_false);
1395 END create_res_role_relate_post;
1396
1397 PROCEDURE update_res_role_relate_post
1398 (P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
1399 P_START_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE
1400 DEFAULT FND_API.G_MISS_DATE,
1401 P_END_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE
1402 DEFAULT FND_API.G_MISS_DATE,
1403 P_OBJECT_VERSION_NUM IN JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
1404 P_DATA OUT NOCOPY VARCHAR2,
1405 P_COUNT OUT NOCOPY NUMBER,
1406 P_RETURN_CODE OUT NOCOPY VARCHAR2) IS
1407
1408 l_role_id NUMBER := NULL;
1409 l_salesrep_id NUMBER;
1410 x_loading_status VARCHAR2(2000);
1411 l_api_name VARCHAR2(30) := 'update_res_role_relate_post';
1412
1413 l_start_date DATE;
1414 l_end_date DATE;
1415 l_count NUMBER;
1416 l_max_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
1417 -- clku, team role enhancement
1418 l_team_event_name VARCHAR(60);
1419 l_date_range_action_tbl cn_api.date_range_action_tbl_type;
1420 l_role_resource_type jtf_rs_role_relations.role_resource_type%TYPE;
1421 l_role_resource_id jtf_rs_role_relations.role_resource_id%TYPE;
1422
1423 -- Bug 4083951 by mnativ
1424 l_usage VARCHAR2(30);
1425 -- clku, bug 3718575
1426 l_resource_category VARCHAR2(30);
1427
1428 l_orig_org_id NUMBER;
1429 l_orig_acc_mode VARCHAR2(1);
1430
1431 CURSOR get_srp_org_info IS
1432 select salesrep_id, org_id
1433 from jtf_rs_salesreps
1434 where resource_id = l_role_resource_id
1435 AND l_role_resource_type = 'RS_INDIVIDUAL'
1436 UNION ALL
1437 select salesrep_id, org_id
1438 from jtf_rs_group_members gm, jtf_rs_salesreps s
1439 where gm.group_member_id = l_role_resource_id
1440 and gm.resource_id = s.resource_id
1441 and delete_flag = 'N'
1442 AND l_role_resource_type = 'RS_GROUP_MEMBER'
1443 UNION ALL
1444 select salesrep_id, org_id
1445 from jtf_rs_team_members tm, jtf_rs_salesreps s
1446 where tm.team_member_id = l_role_resource_id
1447 and tm.team_resource_id = s.resource_id
1448 and resource_type = 'INDIVIDUAL'
1449 and delete_flag = 'N'
1450 AND l_role_resource_type = 'RS_TEAM_MEMBER';
1451
1452 -- get the team info associated with the reps who are inturn
1453 -- associated with the role (single-org)
1454 CURSOR srp_team_relate_info (p_salesrep_id NUMBER,
1455 p_role_relate_id NUMBER,
1456 l_start_date DATE,
1457 l_end_date DATE) IS
1458 SELECT ct.name name,
1459 ct.comp_team_id team_id,
1460 greatest(l_start_date, ct.start_date_active) start_date,
1461 least(nvl(ct.end_date_active, l_max_date),
1462 nvl(l_end_date, l_max_date)) end_date
1463 FROM jtf_rs_group_usages u,
1464 jtf_rs_group_mbr_role_vl r,
1465 cn_rs_salesreps s,
1466 jtf_rs_roles_b ro,
1467 cn_srp_comp_teams_v srt,
1468 cn_comp_teams ct
1469 WHERE r.role_relate_id = p_role_relate_id
1470 AND s.salesrep_id = p_salesrep_id
1471 AND u.group_id = r.group_id
1472 AND u.usage = 'SALES_COMP'
1473 AND ro.role_id = r.role_id
1474 AND s.resource_id = r.resource_id
1475 AND s.salesrep_id = srt.salesrep_id
1476 AND srt.comp_team_id = ct.comp_team_id
1477 AND (l_start_date <= ct.start_date_active
1478 or l_start_date between ct.start_date_active
1479 and nvl (ct.end_date_active, l_start_date));
1480
1481 -- clku, bug 3718575 get the resource category information
1482 CURSOR resource_category_info IS
1483 select category
1484 from jtf_rs_resource_extns re, jtf_rs_role_relations rr
1485 where re.resource_id = rr.role_resource_id
1486 and rr.role_relate_id = P_ROLE_RELATE_ID;
1487
1488 BEGIN
1489 debugmsg('Inside vertical hook update_role_relate_post');
1490 p_return_code := fnd_api.g_ret_sts_success;
1491
1492 -- get usage for the role (can't fail)
1493 select rr.role_resource_type, r.role_type_code, r.role_id, rr.role_resource_id
1494 INTO l_role_resource_type, l_usage, l_role_id, l_role_resource_id
1495 from jtf_rs_role_relations rr, jtf_rs_roles_b r
1496 where rr.role_relate_id = p_role_relate_id
1497 and rr.role_id = r.role_id;
1498
1499 -- only proceed if usage is SALES_COMP or SALES_COMP_PAYMENT_ANALIST
1500 IF l_usage <> 'SALES_COMP' AND l_usage <> 'SALES_COMP_PAYMENT_ANALIST' THEN
1501 RETURN;
1502 END IF;
1503
1504 -- Sales Comp role assignment may NOT overlap with Pmt Analyst role assignment
1505 IF l_role_resource_type = 'RS_INDIVIDUAL' THEN
1506 IF l_usage = 'SALES_COMP' THEN
1507 SELECT COUNT(1) INTO l_count
1508 FROM jtf_rs_role_relations rrr
1509 WHERE role_relate_id = P_ROLE_RELATE_ID
1510 AND EXISTS
1511 (
1512 SELECT NULL
1513 FROM jtf_rs_role_relations rr,
1514 jtf_rs_roles_b r
1515 WHERE rrr.role_resource_id = rr.role_resource_id
1516 AND rrr.role_relate_id <> rr.role_relate_id
1517 AND rr.role_resource_type = 'RS_INDIVIDUAL'
1518 AND rr.delete_flag = 'N'
1519 AND r.role_id = rr.role_id
1520 AND r.role_type_code = 'SALES_COMP_PAYMENT_ANALIST'
1521 -- AND NVL(e1,s2) >= s2 AND s1 <= NVL(e2,s1)
1522 -- s1,e1 = IN params, s2,e2 = existing role assignment
1523 AND NVL(TRUNC(P_END_DATE_ACTIVE),TRUNC(rr.start_date_active))
1524 >= TRUNC(rr.start_date_active)
1525 AND TRUNC(P_START_DATE_ACTIVE)
1526 <= NVL(TRUNC(rr.end_date_active),TRUNC(P_START_DATE_ACTIVE)));
1527
1528 if l_count <> 0 then
1529 FND_MESSAGE.SET_NAME('CN', 'CN_SRP_CANNOT_HAVE_ANAL_ROLE');
1530 -- A salesperson cannot be assigned a payment analyst role.
1531 FND_MSG_PUB.ADD;
1532 RAISE FND_API.G_EXC_ERROR;
1533 end if;
1534 ELSIF l_usage = 'SALES_COMP_PAYMENT_ANALIST' THEN
1535 -- Pmt Analyst role assignment may NOT everlap with Sales Comp role assignment
1536 SELECT COUNT(1) INTO l_count
1537 FROM jtf_rs_role_relations rrr
1538 WHERE role_relate_id = P_ROLE_RELATE_ID
1539 AND EXISTS
1540 (
1541 SELECT NULL
1542 FROM jtf_rs_role_relations rr,
1543 jtf_rs_roles_b r
1544 WHERE rrr.role_resource_id = rr.role_resource_id
1545 AND rrr.role_relate_id <> rr.role_relate_id
1546 AND rr.role_resource_type = 'RS_INDIVIDUAL'
1547 AND rr.delete_flag = 'N'
1548 AND r.role_id = rr.role_id
1549 AND r.role_type_code = 'SALES_COMP'
1550 -- AND NVL(e1,s2) >= s2 AND s1 <= NVL(e2,s1)
1551 -- s1,e1 = IN params, s2,e2 = existing role assignment
1552 AND NVL(TRUNC(P_END_DATE_ACTIVE),TRUNC(rr.start_date_active))
1553 >= TRUNC(rr.start_date_active)
1554 AND TRUNC(P_START_DATE_ACTIVE)
1555 <= NVL(TRUNC(rr.end_date_active),TRUNC(P_START_DATE_ACTIVE))
1556 );
1557
1558 if l_count <> 0 then
1559 FND_MESSAGE.SET_NAME('CN', 'CN_SRP_CANNOT_HAVE_ANAL_ROLE');
1560 -- A salesperson cannot be assigned a payment analyst role.
1561 FND_MSG_PUB.ADD;
1562 RAISE FND_API.G_EXC_ERROR;
1563 end if;
1564 END IF; -- l_usage = 'SALES_COMP_PAYMENT_ANALIST'
1565 END IF; -- RS_INDIVIDUAL
1566 -- End Bug 4083951
1567
1568 -- done with validation - now process data
1569 -- store MOAC session info in local variables
1570 l_orig_org_id := mo_global.get_current_org_id;
1571 l_orig_acc_mode := mo_global.get_access_mode;
1572
1573 -- loop through orgs
1574 FOR r IN get_srp_org_info LOOP
1575 mo_global.set_policy_context('S', r.org_id);
1576
1577 IF l_role_resource_type = 'RS_INDIVIDUAL' THEN
1578 -- update the sales comp tables
1579 -- we're already in a loop to cycle through all the applicable orgs
1580 -- associated with the salesreps assigned to the given resource
1581 srp_plan_assignment_for_update
1582 (p_role_id => l_role_id,
1583 p_srp_role_id => P_ROLE_RELATE_ID,
1584 p_date_update_only => fnd_api.g_true,
1585 x_return_status => P_RETURN_CODE,
1586 p_loading_status => x_loading_status,
1587 x_loading_status => x_loading_status);
1588
1589 IF (P_RETURN_CODE <> FND_API.G_RET_STS_SUCCESS ) THEN
1590 RAISE FND_API.G_EXC_ERROR;
1591 END IF;
1592
1593 srp_pmt_plan_asgn_for_update
1594 (p_role_id => l_role_id,
1595 p_srp_role_id => P_ROLE_RELATE_ID,
1596 p_date_update_only => fnd_api.g_true,
1597 x_return_status => P_RETURN_CODE,
1598 p_loading_status => x_loading_status,
1599 x_loading_status => x_loading_status);
1600
1601 IF (P_RETURN_CODE <> FND_API.G_RET_STS_SUCCESS ) THEN
1602 RAISE FND_API.G_EXC_ERROR;
1603 END IF;
1604
1605 -- clku 3718575
1606 open resource_category_info;
1607 fetch resource_category_info into l_resource_category;
1608 close resource_category_info;
1609
1610 IF l_resource_category is not null then
1611 IF l_resource_category <> 'TBH' then
1612
1613 srp_pay_group_asgn_for_update
1614 (p_role_id => l_role_id,
1615 p_srp_role_id => P_ROLE_RELATE_ID,
1616 p_date_update_only => fnd_api.g_true,
1617 x_return_status => P_RETURN_CODE,
1618 p_loading_status => x_loading_status,
1619 x_loading_status => x_loading_status);
1620
1621 END IF; -- clku, not TBH
1622 END IF; -- clku not null
1623
1624 IF (P_RETURN_CODE <> FND_API.G_RET_STS_SUCCESS ) THEN
1625 RAISE FND_API.G_EXC_ERROR;
1626 END IF;
1627
1628 -- Insert into cn_srp_intel_periods for intelligent calculation
1629 ins_srp_intel_prd
1630 (p_salesrep_id => r.salesrep_id,
1631 p_start_date => p_start_date_active,
1632 p_end_date => p_end_date_active,
1633 x_msg_count => P_COUNT,
1634 x_msg_data => P_DATA,
1635 x_return_status => P_RETURN_CODE,
1636 p_loading_status => x_loading_status,
1637 x_loading_status => x_loading_status);
1638
1639 IF (P_RETURN_CODE <> FND_API.G_RET_STS_SUCCESS ) THEN
1640 RAISE FND_API.G_EXC_ERROR;
1641 END IF;
1642
1643 END IF; -- RS_INDIVIDUAL
1644
1645 -- check mark events
1646 IF fnd_profile.value('CN_MARK_EVENTS') = 'Y' THEN
1647 -- Team Member Role section
1648 IF l_role_resource_type = 'RS_TEAM_MEMBER' THEN
1649 IF g_team_id is not null then
1650 cn_api.get_date_range_diff_action
1651 ( start_date_new => P_START_DATE_ACTIVE
1652 ,end_date_new => P_END_DATE_ACTIVE
1653 ,start_date_old => g_tm_start_date_old
1654 ,end_date_old => g_tm_end_date_old
1655 ,x_date_range_action_tbl => l_date_range_action_tbl );
1656
1657 FOR i IN 1..l_date_range_action_tbl.COUNT LOOP
1658
1659 if l_date_range_action_tbl(i).action_flag = 'I' THEN
1660
1661 l_team_event_name := 'CHANGE_TEAM_ADD_REP';
1662
1663 cn_mark_events_pkg.mark_notify_team
1664 (P_TEAM_ID => g_team_id,
1665 P_TEAM_EVENT_NAME => l_team_event_name,
1666 P_TEAM_NAME => g_team_name,
1667 P_START_DATE_ACTIVE => l_date_range_action_tbl(i).start_date,
1668 P_END_DATE_ACTIVE => l_date_range_action_tbl(i).end_date,
1669 P_EVENT_LOG_ID => NULL,
1670 p_org_id => r.org_id);
1671 end if;
1672 END LOOP;
1673 END IF; -- if team not null
1674 END IF; -- RS_TEAM_MEMBER
1675 -- end Team Member Role section
1676
1677 IF l_role_resource_type = 'RS_GROUP_MEMBER' AND
1678 g_resource_id IS NOT NULL THEN
1679 -- g_resource_id should point to the resource corresponding
1680 -- to the group member resource in p_role_resource_id
1681
1682 -- insert the period (p_start_date_active, g_start_date_old)
1683 -- which becomes active.
1684 IF (p_start_date_active < g_start_date_old) THEN
1685 IF (p_end_date_active IS NOT NULL
1686 AND p_end_date_active < g_start_date_old) THEN
1687 l_end_date := p_end_date_active;
1688 ELSE
1689 l_end_date := g_start_date_old - 1;
1690 END IF;
1691 mark_notify
1692 (p_salesrep_id => r.salesrep_id,
1693 p_group_id => g_group_id,
1694 p_operation => 'I',
1695 p_start_date => p_start_date_active,
1696 p_end_date => l_end_date,
1697 p_manager_flag => g_manager_flag,
1698 p_event_log_id => g_event_log_id);
1699
1700 -- mark team related changes
1701 FOR srp_tm_rec IN srp_team_relate_info ( r.salesrep_id,
1702 P_ROLE_RELATE_ID,
1703 p_start_date_active,
1704 l_end_date) LOOP
1705 if srp_tm_rec.end_date = l_max_date then
1706 srp_tm_rec.end_date := null;
1707 end if;
1708
1709 cn_mark_events_pkg.mark_notify_team
1710 (P_TEAM_ID => srp_tm_rec.team_id ,
1711 P_TEAM_EVENT_NAME => 'CHANGE_TEAM_ADD_REP',
1712 P_TEAM_NAME => srp_tm_rec.name,
1713 P_START_DATE_ACTIVE => srp_tm_rec.start_date,
1714 P_END_DATE_ACTIVE => srp_tm_rec.end_date,
1715 P_EVENT_LOG_ID => g_event_log_id,
1716 p_org_id => r.org_id);
1717 END LOOP;
1718 END IF;
1719
1720 -- insert the period (g_end_date_old, p_end_date_active)
1721 -- which becomes active.
1722 IF ((p_end_date_active IS NULL AND g_end_date_old IS NOT NULL) OR p_end_date_active > g_end_date_old) THEN
1723 IF (g_end_date_old < p_start_date_active) THEN
1724 l_start_date := p_start_date_active;
1725 ELSE
1726 l_start_date := g_end_date_old + 1;
1727 END IF;
1728 mark_notify
1729 (p_salesrep_id => r.salesrep_id,
1730 p_group_id => g_group_id,
1731 p_operation => 'I',
1732 p_start_date => l_start_date,
1733 p_end_date => p_end_date_active,
1734 p_manager_flag => g_manager_flag,
1735 p_event_log_id => g_event_log_id);
1736
1737 -- mark team related changes
1738 FOR srp_tm_rec IN srp_team_relate_info (r.salesrep_id,
1739 P_ROLE_RELATE_ID,
1740 l_start_date,
1741 p_end_date_active) LOOP
1742 if srp_tm_rec.end_date = l_max_date then
1743 srp_tm_rec.end_date := null;
1744 end if;
1745
1746 cn_mark_events_pkg.mark_notify_team
1747 (P_TEAM_ID => srp_tm_rec.team_id ,
1748 P_TEAM_EVENT_NAME => 'CHANGE_TEAM_ADD_REP',
1749 P_TEAM_NAME => srp_tm_rec.name,
1750 P_START_DATE_ACTIVE => srp_tm_rec.start_date,
1751 P_END_DATE_ACTIVE => srp_tm_rec.end_date,
1752 P_EVENT_LOG_ID => g_event_log_id,
1753 p_org_id => r.org_id);
1754 END LOOP;
1755 END IF;
1756 END IF; -- RS_GROUP_MEMBER
1757 END IF; -- if mark events turned on
1758 END LOOP; -- orgs loop
1759
1760 -- restore context
1761 restore_context(l_orig_acc_mode, l_orig_org_id);
1762
1763 EXCEPTION
1764 WHEN FND_API.G_EXC_ERROR THEN
1765 P_RETURN_CODE := FND_API.G_RET_STS_ERROR ;
1766 restore_context(l_orig_acc_mode, l_orig_org_id);
1767 FND_MSG_PUB.Count_And_Get
1768 (p_count => p_count ,
1769 p_data => p_data ,
1770 p_encoded => FND_API.G_FALSE );
1771 WHEN OTHERS THEN
1772 p_return_code := fnd_api.g_ret_sts_unexp_error;
1773 restore_context(l_orig_acc_mode, l_orig_org_id);
1774 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1775 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name );
1776 END IF;
1777 FND_MSG_PUB.count_and_get
1778 (p_count => p_count ,
1779 p_data => p_data ,
1780 p_encoded => FND_API.g_false);
1781 END update_res_role_relate_post;
1782
1783 PROCEDURE delete_res_role_relate_pre
1784 (P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
1785 P_OBJECT_VERSION_NUM IN JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
1786 P_DATA OUT NOCOPY VARCHAR2,
1787 P_COUNT OUT NOCOPY NUMBER,
1788 P_RETURN_CODE OUT NOCOPY VARCHAR2) IS
1789
1790 cursor get_role_id is
1791 select rr.role_id
1792 from jtf_rs_role_relations rr,
1793 jtf_rs_roles_b r
1794 where rr.role_relate_id = p_role_relate_id
1795 AND rr.role_id = r.role_id
1796 AND r.role_type_code = 'SALES_COMP'
1797 AND rr.role_resource_type = 'RS_INDIVIDUAL'
1798 AND nvl(rr.delete_flag,'N') = 'N';
1799
1800 l_role_id NUMBER := NULL;
1801 x_loading_status VARCHAR2(2000);
1802 l_api_name VARCHAR2(30) := 'delete_res_role_relate_pre';
1803 -- for mark events
1804 l_manager_flag VARCHAR2(1);
1805 l_group_id NUMBER;
1806 l_event_log_id NUMBER;
1807 l_salesrep_id NUMBER;
1808 p_start_date_active DATE;
1809 p_end_date_active DATE;
1810 l_event_name VARCHAR2(30);
1811 l_count NUMBER;
1812 l_usage VARCHAR2(30);
1813 l_return_status VARCHAR2(200);
1814 l_msg_count NUMBER;
1815 l_msg_data VARCHAR2(2000);
1816 l_max_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
1817
1818 --clku, team member rs role mark event
1819
1820 l_tm_start_date DATE;
1821 l_tm_end_date DATE;
1822 l_rr_start_date DATE;
1823 l_rr_end_date DATE;
1824 l_team_id NUMBER;
1825 l_team_name VARCHAR2(30);
1826 l_team_event_name VARCHAR(60);
1827 l_date_range_action_tbl cn_api.date_range_action_tbl_type;
1828 l_role_resource_id jtf_rs_role_relations.role_resource_id%TYPE;
1829 l_role_resource_type jtf_rs_role_relations.role_resource_type%TYPE;
1830
1831 l_orig_org_id NUMBER;
1832 l_orig_acc_mode VARCHAR(1);
1833
1834 -- clku, bug 3718575
1835 l_resource_category VARCHAR2(30);
1836
1837 CURSOR get_srp_org_info IS
1838 select salesrep_id, org_id
1839 from jtf_rs_salesreps
1840 where resource_id = l_role_resource_id
1841 AND l_role_resource_type = 'RS_INDIVIDUAL'
1842 UNION ALL
1843 select salesrep_id, org_id
1844 from jtf_rs_group_members gm, jtf_rs_salesreps s
1845 where gm.group_member_id = l_role_resource_id
1846 and gm.resource_id = s.resource_id
1847 and delete_flag = 'N'
1848 AND l_role_resource_type = 'RS_GROUP_MEMBER'
1849 UNION ALL
1850 select salesrep_id, org_id
1851 from jtf_rs_team_members tm, jtf_rs_salesreps s
1852 where tm.team_member_id = l_role_resource_id
1853 and tm.team_resource_id = s.resource_id
1854 and resource_type = 'INDIVIDUAL'
1855 and delete_flag = 'N'
1856 AND l_role_resource_type = 'RS_TEAM_MEMBER';
1857
1858 -- run in single-org context
1859 CURSOR team_member_role_relate IS
1860 SELECT t.start_date_active, t.end_date_active, rr.role_id,
1861 sr.salesrep_id, tm.team_id, t.team_name
1862 from jtf_rs_team_members tm,
1863 jtf_rs_salesreps sr,
1864 jtf_rs_team_usages tu,
1865 jtf_rs_role_relations rr,
1866 jtf_rs_roles_b rb,
1867 jtf_rs_teams_vl t
1868 where rr.role_relate_id = p_role_relate_id
1869 and rr.role_resource_type(+) = 'RS_TEAM_MEMBER'
1870 and tm.resource_type = 'INDIVIDUAL'
1871 and tm.delete_flag = 'N'
1872 and tu.team_id = tm.team_id
1873 and tu.usage = 'SALES_COMP'
1874 and sr.resource_id = tm.team_resource_id
1875 and (sr.org_id is null or sr.org_id = (select org_id from cn_repositories))
1876 and rr.role_resource_id(+) = tm.team_member_id
1877 and rr.delete_flag(+) = 'N'
1878 and rb.role_id(+) = rr.role_id
1879 and rb.role_type_code(+) = 'SALES_COMP'
1880 and t.team_id = tm.team_id;
1881
1882 -- cursor to get the information about this role relation (single-org)
1883 CURSOR role_relate_info IS
1884 SELECT r.manager_flag, r.group_id, s.salesrep_id,
1885 r.start_date_active, r.end_date_active
1886 FROM jtf_rs_group_usages u,
1887 jtf_rs_group_mbr_role_vl r,
1888 cn_rs_salesreps s
1889 WHERE r.role_relate_id = p_role_relate_id
1890 AND u.group_id = r.group_id
1891 AND u.usage = 'SALES_COMP'
1892 AND s.resource_id = r.resource_id;
1893
1894 -- get the team info associated with the reps who are inturn
1895 -- associated with the role (single-org)
1896 CURSOR srp_team_relate_info (p_salesrep_id NUMBER, p_role_relate_id NUMBER) IS
1897 SELECT ct.name name,
1898 ct.comp_team_id team_id,
1899 greatest(r.start_date_active, ct.start_date_active) start_date,
1900 Least(nvl(ct.end_date_active, l_max_date), nvl(r.end_date_active, l_max_date)) end_date
1901 FROM jtf_rs_group_usages u,
1902 jtf_rs_group_mbr_role_vl r,
1903 cn_rs_salesreps s,
1904 jtf_rs_roles_b ro,
1905 cn_srp_comp_teams_v srt,
1906 cn_comp_teams ct
1907 WHERE r.role_relate_id = p_role_relate_id
1908 AND s.salesrep_id = p_salesrep_id
1909 AND u.group_id = r.group_id
1910 AND u.usage = 'SALES_COMP'
1911 AND ro.role_id = r.role_id
1912 AND ro.role_type_code = 'SALES_COMP'
1913 AND s.resource_id = r.resource_id
1914 AND s.salesrep_id = srt.salesrep_id
1915 AND srt.comp_team_id = ct.comp_team_id
1916 AND (r.start_date_active <= ct.start_date_active
1917 or r.start_date_active between ct.start_date_active and nvl (ct.end_date_active, r.start_date_active))
1918 AND nvl(r.end_date_active, ct.start_date_active) >= ct.start_date_active;
1919
1920 -- clku, bug 3718575 get the resource category information
1921 CURSOR resource_category_info IS
1922 select category
1923 from jtf_rs_resource_extns re, jtf_rs_role_relations rr
1924 where re.resource_id = rr.role_resource_id
1925 and rr.role_relate_id = P_ROLE_RELATE_ID;
1926
1927 BEGIN
1928 p_return_code := fnd_api.g_ret_sts_success;
1929
1930 -- get usage for the role (can't fail)
1931 select rr.role_resource_type, r.role_type_code, r.role_id,
1932 rr.role_resource_id, start_date_active, end_date_active
1933 INTO l_role_resource_type, l_usage, l_role_id,
1934 l_role_resource_id, l_rr_start_date, l_rr_end_date
1935 from jtf_rs_role_relations rr, jtf_rs_roles_b r
1936 where rr.role_relate_id = p_role_relate_id
1937 and rr.role_id = r.role_id;
1938
1939 -- only proceed if usage is SALES_COMP or SALES_COMP_PAYMENT_ANALIST
1940 IF l_usage <> 'SALES_COMP' AND l_usage <> 'SALES_COMP_PAYMENT_ANALIST' THEN
1941 RETURN;
1942 END IF;
1943
1944 -- done with validation - now process data
1945 -- store MOAC session info in local variables
1946 l_orig_org_id := mo_global.get_current_org_id;
1947 l_orig_acc_mode := mo_global.get_access_mode;
1948
1949 -- loop through orgs
1950 FOR r IN get_srp_org_info LOOP
1951 mo_global.set_policy_context('S', r.org_id);
1952
1953 IF l_role_resource_type = 'RS_INDIVIDUAL' THEN
1954 -- if deleting a payee role, make sure no payee assigned over that period
1955 if l_role_id = G_PAYEE_ROLE then
1956 select count(1) into l_count
1957 from cn_srp_roles sr, cn_srp_payee_assigns spa
1958 where sr.srp_role_id = p_role_relate_id
1959 and spa.payee_id = sr.salesrep_id
1960 and sr.start_date <= nvl(spa.end_date, sr.start_date)
1961 and spa.start_date <= nvl(sr.end_date, spa.start_date);
1962
1963 if l_count <> 0 then
1964 FND_MESSAGE.SET_NAME('CN', 'CN_PA_ASGN_DATE');
1965 FND_MSG_PUB.ADD;
1966 RAISE FND_API.G_EXC_ERROR;
1967 end if;
1968 end if;
1969
1970 -- update the sales comp tables
1971 -- we're already in a loop to cycle through all the applicable orgs
1972 -- associated with the salesreps assigned to the given resource
1973
1974 -- clku 3718575
1975 open resource_category_info;
1976 fetch resource_category_info into l_resource_category;
1977 close resource_category_info;
1978
1979 IF l_resource_category is not null then
1980 IF l_resource_category <> 'TBH' then
1981
1982 srp_pay_group_asgn_for_delete
1983 (p_role_id => l_role_id,
1984 p_srp_role_id => P_ROLE_RELATE_ID,
1985 x_return_status => P_RETURN_CODE,
1986 p_loading_status => x_loading_status,
1987 x_loading_status => x_loading_status);
1988
1989 END IF; -- clku, not TBH
1990 END IF; -- clku not null
1991
1992 IF (p_return_code <> fnd_api.g_ret_sts_success) THEN
1993 RAISE FND_API.G_EXC_ERROR;
1994 END IF;
1995
1996 srp_plan_assignment_for_delete
1997 (p_role_id => l_role_id,
1998 p_srp_role_id => P_ROLE_RELATE_ID,
1999 x_return_status => P_RETURN_CODE,
2000 p_loading_status => x_loading_status,
2001 x_loading_status => x_loading_status);
2002
2003 IF (p_return_code <> fnd_api.g_ret_sts_success) THEN
2004 RAISE FND_API.G_EXC_ERROR;
2005 END IF;
2006
2007 srp_pmt_plan_asgn_for_delete
2008 (p_role_id => l_role_id,
2009 p_srp_role_id => P_ROLE_RELATE_ID,
2010 x_return_status => P_RETURN_CODE,
2011 p_loading_status => x_loading_status,
2012 x_loading_status => x_loading_status);
2013
2014 IF (p_return_code <> fnd_api.g_ret_sts_success) THEN
2015 RAISE FND_API.G_EXC_ERROR;
2016 END IF;
2017 END IF; -- RS_INDIVIDUAL
2018
2019 -- handle mark events
2020 IF fnd_profile.value('CN_MARK_EVENTS') = 'Y' THEN
2021 -- Team Member Role section
2022 IF l_role_resource_type = 'RS_TEAM_MEMBER' THEN
2023 OPEN team_member_role_relate;
2024 FETCH team_member_role_relate INTO
2025 l_tm_start_date, l_tm_end_date, l_role_id,
2026 l_salesrep_id, l_team_id, l_team_name;
2027 IF (team_member_role_relate%notfound) THEN
2028 CLOSE team_member_role_relate;
2029 ELSE
2030 CLOSE team_member_role_relate;
2031
2032 cn_api.get_date_range_diff_action
2033 ( start_date_new => l_tm_start_date
2034 ,end_date_new => l_tm_end_date
2035 ,start_date_old => l_rr_start_date
2036 ,end_date_old => l_rr_end_date
2037 ,x_date_range_action_tbl => l_date_range_action_tbl );
2038
2039 FOR i IN 1..l_date_range_action_tbl.COUNT LOOP
2040 if l_date_range_action_tbl(i).action_flag = 'I' THEN
2041 l_team_event_name := 'CHANGE_TEAM_ADD_REP';
2042 else
2043 l_team_event_name := 'CHANGE_TEAM_DEL_REP';
2044 end if;
2045
2046 cn_mark_events_pkg.mark_notify_team
2047 (P_TEAM_ID => l_team_id,
2048 P_TEAM_EVENT_NAME => l_team_event_name,
2049 P_TEAM_NAME => l_team_name,
2050 P_START_DATE_ACTIVE => l_date_range_action_tbl(i).start_date,
2051 P_END_DATE_ACTIVE => l_date_range_action_tbl(i).end_date,
2052 P_EVENT_LOG_ID => NULL,
2053 p_org_id => r.org_id);
2054 END LOOP;
2055 END IF;
2056 END IF; -- RS_TEAM_MEMBER
2057 -- end Team Member Role section
2058
2059 IF l_role_resource_type = 'RS_GROUP_MEMBER' THEN
2060 OPEN role_relate_info;
2061 FETCH role_relate_info
2062 INTO l_manager_flag, l_group_id, l_salesrep_id,
2063 p_start_date_active, p_end_date_active;
2064 IF (role_relate_info%notfound) THEN
2065 CLOSE role_relate_info;
2066 ELSE
2067 CLOSE role_relate_info;
2068
2069 -- the cursor will only retrieve rows for SALES_COMP roles of type
2070 -- RS_GROUP_MEMBER
2071 IF (l_manager_flag = 'N') THEN
2072 l_event_name := 'CHANGE_CP_DELETE_SRP';
2073 ELSE
2074 l_event_name := 'CHANGE_CP_DELETE_MGR';
2075 END IF;
2076
2077 cn_mark_events_pkg.log_event
2078 (p_event_name => l_event_name,
2079 p_object_name => NULL,
2080 p_object_id => p_role_relate_id,
2081 p_start_date => NULL,
2082 p_start_date_old => p_start_date_active,
2083 p_end_date => NULL,
2084 p_end_date_old => p_end_date_active,
2085 x_event_log_id => l_event_log_id,
2086 p_org_id => r.org_id);
2087
2088 mark_notify
2089 (p_salesrep_id => l_salesrep_id,
2090 p_role_id => l_role_id,
2091 p_group_id => l_group_id,
2092 p_operation => 'D',
2093 p_start_date => p_start_date_active,
2094 p_end_date => p_end_date_active,
2095 p_manager_flag => l_manager_flag,
2096 p_event_log_id => l_event_log_id );
2097
2098 -- mark team related changes
2099 -- clku swap input para
2100 FOR srp_tm_rec IN srp_team_relate_info
2101 (l_salesrep_id, P_ROLE_RELATE_ID) LOOP
2102 if srp_tm_rec.end_date = l_max_date then
2103 srp_tm_rec.end_date := null;
2104 end if;
2105
2106 cn_mark_events_pkg.mark_notify_team
2107 (P_TEAM_ID => srp_tm_rec.team_id ,
2108 P_TEAM_EVENT_NAME => 'CHANGE_TEAM_DEL_REP',
2109 P_TEAM_NAME => srp_tm_rec.name,
2110 P_START_DATE_ACTIVE => srp_tm_rec.start_date,
2111 P_END_DATE_ACTIVE => srp_tm_rec.end_date,
2112 P_EVENT_LOG_ID => l_event_log_id,
2113 p_org_id => r.org_id);
2114 END LOOP;
2115 END IF;
2116 END IF; -- RS_GROUP_MEMBER
2117 END IF; -- mark events
2118 END LOOP; -- orgs
2119
2120 -- restore context
2121 restore_context(l_orig_acc_mode, l_orig_org_id);
2122
2123 EXCEPTION
2124 WHEN FND_API.G_EXC_ERROR THEN
2125 P_RETURN_CODE := FND_API.G_RET_STS_ERROR ;
2126 restore_context(l_orig_acc_mode, l_orig_org_id);
2127 FND_MSG_PUB.Count_And_Get
2128 (p_count => p_count ,
2129 p_data => p_data ,
2130 p_encoded => FND_API.G_FALSE );
2131 WHEN OTHERS THEN
2132 p_return_code := fnd_api.g_ret_sts_unexp_error;
2133 restore_context(l_orig_acc_mode, l_orig_org_id);
2134 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2135 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name );
2136 END IF;
2137 FND_MSG_PUB.count_and_get
2138 (p_count => p_count ,
2139 p_data => p_data ,
2140 p_encoded => FND_API.g_false);
2141 END;
2142
2143 PROCEDURE update_res_role_relate_pre
2144 (P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
2145 P_START_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE
2146 DEFAULT FND_API.G_MISS_DATE,
2147 P_END_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE
2148 DEFAULT FND_API.G_MISS_DATE,
2149 P_OBJECT_VERSION_NUM IN JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
2150 P_DATA OUT NOCOPY VARCHAR2,
2151 P_COUNT OUT NOCOPY NUMBER,
2152 P_RETURN_CODE OUT NOCOPY VARCHAR2) IS
2153
2154 l_api_name VARCHAR2(30) := 'update_res_role_relate_pre';
2155 l_start_date DATE;
2156 l_end_date DATE;
2157 l_event_name VARCHAR2(30);
2158 l_role_id NUMBER;
2159 l_salesrep_id NUMBER;
2160 l_usage VARCHAR2(30);
2161
2162 l_orig_org_id NUMBER;
2163 l_orig_acc_mode VARCHAR2(1);
2164 --variable added for bug 6914823
2165 l_res_start_date DATE;
2166
2167 CURSOR payee_assign_date_curs(l_payee_id NUMBER) IS
2168 select salesrep_id, start_date, end_date from cn_srp_payee_assigns
2169 where payee_id = l_payee_id;
2170
2171 l_max_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
2172 --clku, team member rs role mark event
2173
2174 l_team_event_name VARCHAR(60);
2175 l_date_range_action_tbl cn_api.date_range_action_tbl_type;
2176 l_role_resource_type jtf_rs_role_relations.role_resource_type%TYPE;
2177 l_role_resource_id jtf_rs_role_relations.role_resource_id%TYPE;
2178
2179 -- get the salesrep ID's, org ID's
2180 CURSOR get_srp_org_info IS
2181 select salesrep_id, org_id
2182 from jtf_rs_salesreps
2183 where resource_id = l_role_resource_id
2184 AND l_role_resource_type = 'RS_INDIVIDUAL'
2185 UNION ALL
2186 select salesrep_id, org_id
2187 from jtf_rs_group_members gm, jtf_rs_salesreps s
2188 where gm.group_member_id = l_role_resource_id
2189 and gm.resource_id = s.resource_id
2190 and delete_flag = 'N'
2191 AND l_role_resource_type = 'RS_GROUP_MEMBER'
2192 UNION ALL
2193 select salesrep_id, org_id
2194 from jtf_rs_team_members tm, jtf_rs_salesreps s
2195 where tm.team_member_id = l_role_resource_id
2196 and tm.team_resource_id = s.resource_id
2197 and resource_type = 'INDIVIDUAL'
2198 and delete_flag = 'N'
2199 AND l_role_resource_type = 'RS_TEAM_MEMBER';
2200
2201 -- run in single-org context
2202 CURSOR team_member_role_relate IS
2203 SELECT rr.start_date_active, rr.end_date_active, tm.team_id, t.team_name
2204 from jtf_rs_team_members tm,
2205 jtf_rs_salesreps sr,
2206 jtf_rs_team_usages tu,
2207 jtf_rs_role_relations rr,
2208 jtf_rs_roles_b rb,
2209 jtf_rs_teams_vl t
2210 where rr.role_relate_id = p_role_relate_id
2211 and rr.role_resource_type(+) = 'RS_TEAM_MEMBER'
2212 and tm.resource_type = 'INDIVIDUAL'
2213 and tm.delete_flag = 'N'
2214 and tu.team_id = tm.team_id
2215 and tu.usage = 'SALES_COMP'
2216 and sr.resource_id = tm.team_resource_id
2217 and (sr.org_id is null or sr.org_id = (select org_id from cn_repositories))
2218 and rr.role_resource_id(+) = tm.team_member_id
2219 and rr.delete_flag(+) = 'N'
2220 and rb.role_id(+) = rr.role_id
2221 and rb.role_type_code(+) = 'SALES_COMP'
2222 and t.team_id = tm.team_id;
2223
2224 -- clku
2225
2226 -- cursor to get the information about this role relation
2227 CURSOR role_relate_info IS
2228 SELECT r.manager_flag, r.group_id, s.salesrep_id, s.resource_id,
2229 r.start_date_active, r.end_date_active, r.role_id
2230 FROM jtf_rs_group_usages u,
2231 jtf_rs_group_mbr_role_vl r,
2232 cn_rs_salesreps s
2233 WHERE r.role_relate_id = p_role_relate_id
2234 AND u.group_id = r.group_id
2235 AND u.usage = 'SALES_COMP'
2236 AND s.resource_id = r.resource_id;
2237
2238 -- get the team info associated with the reps who are inturn associated with the role
2239 CURSOR srp_team_relate_info (p_salesrep_id NUMBER,
2240 p_role_relate_id NUMBER,
2241 l_start_date DATE,
2242 l_end_date DATE) IS
2243 SELECT ct.name name,
2244 ct.comp_team_id team_id,
2245 greatest(l_start_date, ct.start_date_active) start_date,
2246 Least(nvl(ct.end_date_active, l_max_date), nvl(l_end_date, l_max_date)) end_date
2247 FROM jtf_rs_group_usages u,
2248 jtf_rs_group_mbr_role_vl r,
2249 cn_rs_salesreps s,
2250 jtf_rs_roles_b ro,
2251 cn_srp_comp_teams_v srt,
2252 cn_comp_teams ct
2253 WHERE r.role_relate_id = p_role_relate_id
2254 AND s.salesrep_id = p_salesrep_id
2255 AND u.group_id = r.group_id
2256 AND u.usage = 'SALES_COMP'
2257 AND ro.role_id = r.role_id
2258 AND s.resource_id = r.resource_id
2259 AND s.salesrep_id = srt.salesrep_id
2260 AND srt.comp_team_id = ct.comp_team_id
2261 AND (l_start_date <= ct.start_date_active
2262 or l_start_date between ct.start_date_active and nvl (ct.end_date_active, l_start_date));
2263
2264 CURSOR payee_role_info IS
2265 SELECT salesrep_id, start_date, end_date, role_id
2266 FROM cn_srp_roles
2267 WHERE srp_role_id = p_role_relate_id;
2268
2269 BEGIN
2270 debugmsg('Inside vertical hook update_role_relate_pre');
2271 p_return_code := fnd_api.g_ret_sts_success;
2272
2273 -- get usage for the role (can't fail)
2274 select rr.role_resource_type, r.role_type_code, r.role_id, rr.role_resource_id
2275 INTO l_role_resource_type, l_usage, l_role_id, l_role_resource_id
2276 from jtf_rs_role_relations rr, jtf_rs_roles_b r
2277 where rr.role_relate_id = p_role_relate_id
2278 and rr.role_id = r.role_id;
2279
2280 -- only proceed if usage is SALES_COMP or SALES_COMP_PAYMENT_ANALIST
2281 IF l_usage <> 'SALES_COMP' AND l_usage <> 'SALES_COMP_PAYMENT_ANALIST' THEN
2282 RETURN;
2283 END IF;
2284
2285 -- done with validation - now process data
2286 -- store MOAC session info in local variables
2287 l_orig_org_id := mo_global.get_current_org_id;
2288 l_orig_acc_mode := mo_global.get_access_mode;
2289
2290 -- loop through orgs
2291 FOR r IN get_srp_org_info LOOP
2292 mo_global.set_policy_context('S', r.org_id);
2293
2294 -- make sure we are not deleting a payee role interval during a payee
2295 -- assignment to a salesrep
2296 -- only applies to RS_INDIVIDUAL
2297 IF l_role_resource_type = 'RS_INDIVIDUAL' AND l_role_id = G_PAYEE_ROLE then
2298 open payee_role_info;
2299 FETCH payee_role_info
2300 INTO l_salesrep_id, g_start_date_old, g_end_date_old, l_role_id;
2301 IF (payee_role_info%notfound) THEN
2302 CLOSE payee_role_info;
2303 ELSE
2304 CLOSE payee_role_info;
2305
2306 g_resource_id := l_role_resource_id; -- for RS_INDIVIDUAL
2307
2308 cn_api.get_date_range_diff_action
2309 (start_date_new => P_START_DATE_ACTIVE,
2310 end_date_new => P_END_DATE_ACTIVE,
2311 start_date_old => g_start_date_old,
2312 end_date_old => g_end_date_old,
2313 x_date_range_action_tbl => l_date_range_action_tbl);
2314
2315 FOR i IN 1..l_date_range_action_tbl.COUNT LOOP
2316 if l_date_range_action_tbl(i).action_flag = 'D' THEN
2317 -- check if there is any salesrep having this payee assigned within
2318 -- the deleting paygroup date range
2319 For l_payee_assign_date_rec IN payee_assign_date_curs(l_salesrep_id) LOOP
2320 -- check if there is any date range over between
2321 -- srp paygroup date and payee assign date
2322 IF CN_API.date_range_overlap
2323 (l_date_range_action_tbl(i).start_date,
2324 l_date_range_action_tbl(i).end_date,
2325 l_payee_assign_date_rec.start_date,
2326 l_payee_assign_date_rec.end_date) = true THEN
2327
2328 -- Raise Error
2329 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2330 THEN
2331 fnd_message.set_name('CN', 'CN_PA_ASGN_DATE');
2332 fnd_msg_pub.add;
2333 END IF;
2334 RAISE FND_API.G_EXC_ERROR;
2335 END IF;
2336 END LOOP;
2337 END IF; --if l_date_range_action_tbl(i).action_flag = 'D'
2338 END LOOP; -- FOR i IN 1..l_date_range_action_tbl.COUNT LOOP
2339 END IF; -- if cursor found
2340 END IF; -- payee role
2341
2342 IF fnd_profile.value('CN_MARK_EVENTS') = 'Y' THEN
2343
2344 IF l_role_resource_type = 'RS_TEAM_MEMBER' THEN
2345 -- reset global variables
2346 g_tm_start_date_old := null;
2347 g_tm_end_date_old := null;
2348 g_team_id := null;
2349 g_team_name := null;
2350
2351 OPEN team_member_role_relate;
2352 FETCH team_member_role_relate INTO
2353 g_tm_start_date_old, g_tm_end_date_old, g_team_id, g_team_name;
2354 IF (team_member_role_relate%notfound) THEN
2355 CLOSE team_member_role_relate;
2356 ELSE
2357 CLOSE team_member_role_relate;
2358
2359 cn_api.get_date_range_diff_action
2360 ( start_date_new => P_START_DATE_ACTIVE
2361 ,end_date_new => P_END_DATE_ACTIVE
2362 ,start_date_old => g_tm_start_date_old
2363 ,end_date_old => g_tm_end_date_old
2364 ,x_date_range_action_tbl => l_date_range_action_tbl );
2365
2366 FOR i IN 1..l_date_range_action_tbl.COUNT LOOP
2367
2368 if l_date_range_action_tbl(i).action_flag = 'D' THEN
2369
2370 l_team_event_name := 'CHANGE_TEAM_DEL_REP';
2371
2372 cn_mark_events_pkg.mark_notify_team
2373 (P_TEAM_ID => g_team_id,
2374 P_TEAM_EVENT_NAME => l_team_event_name,
2375 P_TEAM_NAME => g_team_name,
2376 P_START_DATE_ACTIVE => l_date_range_action_tbl(i).start_date,
2377 P_END_DATE_ACTIVE => l_date_range_action_tbl(i).end_date,
2378 P_EVENT_LOG_ID => NULL,
2379 p_org_id => r.org_id);
2380 end if; -- action_flag
2381 END LOOP; -- date range action tbl
2382 END IF; -- team member not found
2383 END IF; -- team member
2384 -- Team Member Role section
2385 -- ENHANCEMENT END
2386
2387 -- reset g_salesrep_id to null before trying to set it to another value
2388 g_resource_id := NULL;
2389
2390 IF l_role_resource_type = 'RS_GROUP_MEMBER' then
2391 OPEN role_relate_info;
2392 FETCH role_relate_info
2393 INTO g_manager_flag, g_group_id, l_salesrep_id, g_resource_id,
2394 g_start_date_old, g_end_date_old, l_role_id;
2395 IF (role_relate_info%notfound) THEN
2396 CLOSE role_relate_info;
2397 ELSE
2398 CLOSE role_relate_info;
2399
2400 IF (g_manager_flag = 'Y') THEN
2401 l_event_name := 'CHANGE_CP_SRP_DATE';
2402 ELSIF (g_manager_flag = 'N') THEN
2403 l_event_name := 'CHANGE_CP_MGR_DATE';
2404 END IF;
2405 --code added for bug 6914823
2406 if (p_start_date_active = fnd_api.g_miss_date)
2407 OR
2408 (p_start_date_active > p_end_date_active)
2409 OR
2410 (p_start_date_active is NULL)
2411 then
2412 l_res_start_date:= g_start_date_old;
2413 else
2414 l_res_start_date := p_start_date_active;
2415 end if;
2416 -- end of code addition
2417 cn_mark_events_pkg.log_event
2418 (p_event_name => l_event_name,
2419 p_object_name => NULL,
2420 p_object_id => p_role_relate_id,
2421 p_start_date => l_res_start_date,--parameter changed for bug 6914823
2422 p_start_date_old => g_start_date_old,
2423 p_end_date => p_end_date_active,
2424 p_end_date_old => g_end_date_old,
2425 x_event_log_id => g_event_log_id,
2426 p_org_id => r.org_id);
2427
2428 -- delete the period (g_start_date_old, p_start_date_active)
2429 -- which is not active any more
2430 IF (p_start_date_active > g_start_date_old) THEN
2431 IF (g_end_date_old IS NOT NULL AND g_end_date_old < p_start_date_active) THEN
2432 l_end_date := g_end_date_old;
2433 ELSE
2434 l_end_date := p_start_date_active - 1;
2435 END IF;
2436
2437 mark_notify
2438 (p_salesrep_id => l_salesrep_id,
2439 p_role_id => l_role_id,
2440 p_group_id => g_group_id,
2441 p_operation => 'D',
2442 p_start_date => g_start_date_old,
2443 p_end_date => l_end_date,
2444 p_manager_flag => g_manager_flag,
2445 p_event_log_id => g_event_log_id );
2446
2447 -- mark team related changes
2448 FOR srp_tm_rec IN srp_team_relate_info ( l_salesrep_id,
2449 P_ROLE_RELATE_ID,
2450 g_start_date_old,
2451 l_end_date) LOOP
2452 if srp_tm_rec.end_date = l_max_date then
2453 srp_tm_rec.end_date := null;
2454 end if;
2455
2456 cn_mark_events_pkg.mark_notify_team
2457 (P_TEAM_ID => srp_tm_rec.team_id ,
2458 P_TEAM_EVENT_NAME => 'CHANGE_TEAM_DEL_REP',
2459 P_TEAM_NAME => srp_tm_rec.name,
2460 P_START_DATE_ACTIVE => srp_tm_rec.start_date,
2461 P_END_DATE_ACTIVE => srp_tm_rec.end_date,
2462 P_EVENT_LOG_ID => g_event_log_id,
2463 p_org_id => r.org_id);
2464 END LOOP;
2465 END IF;
2466 END IF;
2467
2468 -- delete the period (p_end_date_active, g_end_date_old)
2469 -- which is not active any more
2470 IF ((g_end_date_old IS NULL AND p_end_date_active IS NOT NULL)
2471 OR p_end_date_active < g_end_date_old) THEN
2472 IF (p_end_date_active < g_start_date_old) THEN
2473 l_start_date := g_start_date_old;
2474 ELSE
2475 l_start_date := p_end_date_active + 1;
2476 END IF;
2477
2478 mark_notify
2479 (p_salesrep_id => l_salesrep_id,
2480 p_role_id => l_role_id,
2481 p_group_id => g_group_id,
2482 p_operation => 'D',
2483 p_start_date => l_start_date,
2484 p_end_date => g_end_date_old,
2485 p_manager_flag => g_manager_flag,
2486 p_event_log_id => g_event_log_id );
2487
2488 -- mark team related changes
2489 FOR srp_tm_rec IN srp_team_relate_info ( l_salesrep_id,
2490 P_ROLE_RELATE_ID,
2491 l_start_date,
2492 g_end_date_old) LOOP
2493
2494 if srp_tm_rec.end_date = l_max_date then
2495 srp_tm_rec.end_date := null;
2496 end if;
2497
2498 cn_mark_events_pkg.mark_notify_team
2499 (P_TEAM_ID => srp_tm_rec.team_id ,
2500 P_TEAM_EVENT_NAME => 'CHANGE_TEAM_DEL_REP',
2501 P_TEAM_NAME => srp_tm_rec.name,
2502 P_START_DATE_ACTIVE => srp_tm_rec.start_date,
2503 P_END_DATE_ACTIVE => srp_tm_rec.end_date,
2504 P_EVENT_LOG_ID => g_event_log_id,
2505 p_org_id => r.org_id);
2506 END LOOP;
2507 END IF;
2508 END IF; -- group member
2509 END IF; -- mark events
2510 END LOOP; -- orgs
2511
2512 -- restore context
2513 restore_context(l_orig_acc_mode, l_orig_org_id);
2514 EXCEPTION
2515 WHEN FND_API.G_EXC_ERROR THEN
2516 P_RETURN_CODE := FND_API.G_RET_STS_ERROR ;
2517 restore_context(l_orig_acc_mode, l_orig_org_id);
2518 FND_MSG_PUB.Count_And_Get
2519 (p_count => p_count ,
2520 p_data => p_data ,
2521 p_encoded => FND_API.G_FALSE );
2522 WHEN OTHERS THEN
2523 p_return_code := fnd_api.g_ret_sts_unexp_error;
2524 restore_context(l_orig_acc_mode, l_orig_org_id);
2525 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2526 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name );
2527 END IF;
2528 FND_MSG_PUB.count_and_get
2529 (p_count => p_count ,
2530 p_data => p_data ,
2531 p_encoded => FND_API.g_false);
2532 END update_res_role_relate_pre;
2533
2534 -- ===================================================================
2535 -- these are the procedures that aren't used in the body, but since ==
2536 -- they are declared in the spec, they need to be declared here ==
2537 -- with null bodies. ==
2538 -- ===================================================================
2539
2540 /*for create resource role relate */
2541
2542 PROCEDURE create_res_role_relate_pre
2543 (P_ROLE_RESOURCE_TYPE JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_TYPE%TYPE,
2544 P_ROLE_RESOURCE_ID JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE,
2545 P_ROLE_ID JTF_RS_ROLE_RELATIONS.ROLE_ID%TYPE,
2546 P_START_DATE_ACTIVE JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE,
2547 P_END_DATE_ACTIVE JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE ,
2548 P_DATA OUT NOCOPY VARCHAR2,
2549 P_COUNT OUT NOCOPY NUMBER,
2550 P_RETURN_CODE OUT NOCOPY VARCHAR2) IS
2551 BEGIN
2552 p_return_code := fnd_api.g_ret_sts_success ;
2553 END create_res_role_relate_pre;
2554
2555 PROCEDURE delete_res_role_relate_post
2556 (P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
2557 P_OBJECT_VERSION_NUM IN JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
2558 P_DATA OUT NOCOPY VARCHAR2,
2559 P_COUNT OUT NOCOPY NUMBER,
2560 P_RETURN_CODE OUT NOCOPY VARCHAR2) IS
2561 BEGIN
2562 p_return_code := fnd_api.g_ret_sts_success;
2563 END delete_res_role_relate_post;
2564
2565 FUNCTION Ok_To_Generate_Msg
2566 (P_DATA OUT NOCOPY VARCHAR2,
2567 P_COUNT OUT NOCOPY NUMBER,
2568 P_RETURN_CODE OUT NOCOPY VARCHAR2)
2569 RETURN BOOLEAN IS
2570 BEGIN
2571 p_return_code := fnd_api.g_ret_sts_success;
2572 return false;
2573 END Ok_To_Generate_Msg;
2574
2575 END jtf_rs_role_relate_vuhk;