1 PACKAGE BODY jtf_rs_group_usage_vuhk AS
2 /* $Header: cnirsgub.pls 120.1 2005/08/04 15:14:08 mblum noship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_GROUP_USAGE_VUHK';
5
6 -- helper procedure for the MOAC session context
7 PROCEDURE restore_context(p_acc_mode VARCHAR2,
8 p_org_id NUMBER) IS
9 BEGIN
10 IF p_acc_mode IS NOT NULL then
11 mo_global.set_policy_context(p_acc_mode, p_org_id);
12 END IF;
13 END restore_context;
14
15 /* Vertcal Industry Procedure for pre processing in case of create resource group usage */
16 PROCEDURE create_group_usage_pre
17 (P_GROUP_ID IN NUMBER,
18 P_USAGE IN VARCHAR2,
19 X_RETURN_STATUS OUT NOCOPY VARCHAR2
20 ) IS
21 BEGIN
22 x_return_status := fnd_api.g_ret_sts_success;
23 END create_group_usage_pre;
24
25 /* Vertcal Industry Procedure for post processing in case of create resource group usage */
26 PROCEDURE create_group_usage_post
27 (P_GROUP_USAGE_ID IN NUMBER,
28 P_GROUP_ID IN NUMBER,
29 P_USAGE IN VARCHAR2,
30 X_RETURN_STATUS OUT NOCOPY VARCHAR2
31 ) IS
32
33 l_event_log_id NUMBER;
34 l_start_date DATE;
35 l_end_date DATE;
36 l_group_name VARCHAR2(60);
37 l_action_link_id NUMBER;
38 p_action_link_id NUMBER;
39 l_srp cn_rollup_pvt.srp_group_rec_type;
40 l_srp_tbl cn_rollup_pvt.srp_group_tbl_type;
41 l_return_status VARCHAR2(30);
42 l_msg_count NUMBER;
43 l_msg_data VARCHAR2(256);
44 i NUMBER;
45 l_api_name VARCHAR2(30) := 'create_group_usage_post';
46 --clku , fix max date year to 9999
47 l_max_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
48
49 l_orig_org_id NUMBER;
50 l_orig_acc_mode VARCHAR2(1);
51
52 -- cursor to get the start_date and end_date of the new group
53 CURSOR dates IS
54 SELECT start_date_active, end_date_active, name
55 FROM cn_comp_groups
56 WHERE comp_group_id = p_group_id;
57
58 -- cursor to find all srps in the new group
59 CURSOR srp_periods IS
60 SELECT cscg.salesrep_id,
61 cscg.comp_group_id,
62 intel.period_id,
63 greatest(cscg.start_date_active, intel.start_date) start_date,
64 decode(cscg.end_date_active, null, intel.end_date,
65 Least(cscg.end_date_active, intel.end_date)) end_date
66 FROM cn_srp_comp_groups_v cscg,
67 cn_srp_intel_periods intel
68 WHERE cscg.comp_group_id = p_group_id
69 and intel.salesrep_id = cscg.salesrep_id
70 and cscg.start_date_active <= intel.end_date
71 and (cscg.end_date_active is null or cscg.end_date_active >= intel.start_date);
72
73 -- cursor to find all periods in the date range for each srp
74 CURSOR periods(p_salesrep_id NUMBER, p_start_date DATE, p_end_date DATE) IS
75 SELECT p.period_id,
76 greatest(p_start_date, p.start_date) start_date,
77 Decode(p_end_date, NULL, p.end_date, Least(p_end_date, p.end_date)) end_date
78 FROM cn_srp_intel_periods p
79 WHERE p.salesrep_id = p_salesrep_id
80 AND (p_end_date IS NULL OR p.start_date <= p_end_date)
81 AND (p.end_date >= p_start_date);
82
83 -- check if the member is part of a team
84 CURSOR srp_team(p_salesrep_id NUMBER, p_group_id NUMBER)IS
85 select ct.name name,
86 ct.comp_team_id team_id,
87 greatest(cg.start_date_active, ct.start_date_active) start_date,
88 Least(nvl(ct.end_date_active, l_max_date), nvl(cg.end_date_active, l_max_date)) end_date
89 from cn_srp_comp_teams_v srt, cn_comp_teams ct, cn_comp_groups cg
90 where srt.salesrep_id = p_salesrep_id
91 and srt.comp_team_id = ct.comp_team_id
92 and cg.comp_group_id = p_group_id
93 and (cg.start_date_active <= ct.start_date_active
94 or cg.start_date_active between ct.start_date_active and nvl (ct.end_date_active, cg.start_date_active))
95 and nvl(cg.end_date_active, ct.start_date_active) >= ct.start_date_active;
96
97 CURSOR get_orgs IS
98 SELECT org_id FROM cn_repositories_all WHERE status = 'A';
99
100 BEGIN
101 x_return_status := fnd_api.g_ret_sts_success;
102
103 IF fnd_profile.value('CN_MARK_EVENTS') <> 'Y' THEN
104 RETURN;
105 END IF;
106
107 -- store MOAC session info in local variables
108 l_orig_org_id := mo_global.get_current_org_id;
109 l_orig_acc_mode := mo_global.get_access_mode;
110
111 -- loop through orgs
112 FOR o IN get_orgs LOOP
113 mo_global.set_policy_context('S', o.org_id);
114
115 -- if the usage is SALES_COMP, then find all srps in this group and their ancestors in the comp group hierarchy.
116 -- call mark_notify_salesreps for each of them
117 IF (p_usage = 'SALES_COMP') THEN
118 -- get the start_date and end_date of the new group
119 OPEN dates;
120 FETCH dates INTO l_start_date, l_end_date, l_group_name;
121 IF (dates%notfound) THEN
122 CLOSE dates;
123 ELSE
124 CLOSE dates;
125
126 cn_mark_events_pkg.log_event
127 ( p_event_name => 'CHANGE_CP_HIER_ADD',
128 p_object_name => l_group_name,
129 p_object_id => p_group_id,
130 p_start_date => l_start_date,
131 p_start_date_old => NULL,
132 p_end_date => l_end_date,
133 p_end_date_old => NULL,
134 x_event_log_id => l_event_log_id,
135 p_org_id => o.org_id);
136 cn_mark_events_pkg.mark_notify_salesreps
137 ( p_salesrep_id => NULL,
138 p_comp_group_id => p_group_id,
139 p_period_id => null,
140 p_start_date => l_start_date,
141 p_end_date => l_end_date,
142 p_revert_to_state => 'NCALC',
143 p_action => 'XROLL',
144 p_action_link_id => NULL,
145 p_base_salesrep_id => NULL,
146 p_base_comp_group_id => NULL,
147 p_event_log_id => l_event_log_id,
148 x_action_link_id => p_action_link_id,
149 p_org_id => o.org_id);
150
151 i := 0;
152 FOR srp_period IN srp_periods LOOP
153 -- get the info about the first salesrep to be used to get all the ancestors.
154 -- if the salesrep is not a manager and the ancestors will include managers in the same group, then
155 -- we can get the info of a manager in this group and use it to find ancestors.
156 IF (i = 0) THEN
157 l_srp.salesrep_id := srp_period.salesrep_id;
158 l_srp.group_id := srp_period.comp_group_id;
159 i := i + 1;
160 END IF;
161 cn_mark_events_pkg.mark_notify_salesreps
162 ( p_salesrep_id => srp_period.salesrep_id,
163 p_comp_group_id => srp_period.comp_group_id,
164 p_period_id => srp_period.period_id,
165 p_start_date => srp_period.start_date,
166 p_end_date => srp_period.end_date,
167 p_revert_to_state => 'CALC',
168 p_action => 'PULL',
169 p_action_link_id => p_action_link_id,
170 p_base_salesrep_id => NULL,
171 p_base_comp_group_id => NULL,
172 p_event_log_id => l_event_log_id,
173 x_action_link_id => l_action_link_id,
174 p_org_id => o.org_id);
175
176 -- check if this rep belongs to a team
177 FOR srp_tm_rec IN srp_team (srp_period.salesrep_id, p_group_id) LOOP
178
179 if srp_tm_rec.end_date = l_max_date then
180 srp_tm_rec.end_date := null;
181 end if;
182
183 cn_mark_events_pkg.mark_notify_team
184 (P_TEAM_ID => srp_tm_rec.team_id ,
185 P_TEAM_EVENT_NAME => 'CHANGE_TEAM_ADD_REP',
186 P_TEAM_NAME => srp_tm_rec.name,
187 P_START_DATE_ACTIVE => srp_tm_rec.start_date,
188 P_END_DATE_ACTIVE => srp_tm_rec.end_date,
189 P_EVENT_LOG_ID => l_event_log_id,
190 p_org_id => o.org_id);
191 END LOOP;
192 END LOOP;
193
194 -- find the ancestors of this salesrep and call mark_notify for all of them
195 -- not that we use l_start_date and l_end_date since this date range is the super range which covers
196 -- the date effectivity of all the salesreps in this group.
197 l_srp.start_date := l_start_date;
198 l_srp.end_date := l_end_date;
199 cn_rollup_pvt.get_ancestor_salesrep
200 ( p_api_version => 1.0,
201 p_init_msg_list => FND_API.G_false,
202 p_commit => FND_API.G_false,
203 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
204 x_return_status => l_return_status,
205 x_msg_count => l_msg_count,
206 x_msg_data => l_msg_data,
207 p_srp => l_srp,
208 p_org_id => o.org_id,
209 x_srp => l_srp_tbl);
210
211 IF (l_srp_tbl.COUNT > 0) THEN
212 FOR i IN l_srp_tbl.first..l_srp_tbl.last LOOP
213 FOR prd IN periods(l_srp_tbl(i).salesrep_id, l_srp_tbl(i).start_date, l_srp_tbl(i).end_date) LOOP
214 cn_mark_events_pkg.mark_notify_salesreps
215 ( p_salesrep_id => l_srp_tbl(i).salesrep_id,
216 p_comp_group_id => l_srp_tbl(i).group_id,
217 p_period_id => prd.period_id,
218 p_start_date => prd.start_date,
219 p_end_date => prd.end_date,
220 p_revert_to_state => 'CALC',
221 p_action => NULL,
222 p_action_link_id => p_action_link_id,
223 p_base_salesrep_id => NULL,
224 p_base_comp_group_id => NULL,
225 p_event_log_id => l_event_log_id,
226 x_action_link_id => l_action_link_id,
227 p_org_id => o.org_id);
228 END LOOP;
229
230 -- check if this rep belongs to a team
231 FOR srp_tm_rec IN srp_team (l_srp_tbl(i).salesrep_id, l_srp_tbl(i).group_id) LOOP
232 if srp_tm_rec.end_date = l_max_date then
233 srp_tm_rec.end_date := null;
234 end if;
235
236 cn_mark_events_pkg.mark_notify_team
237 (P_TEAM_ID => srp_tm_rec.team_id ,
238 P_TEAM_EVENT_NAME => 'CHANGE_TEAM_ADD_REP',
239 P_TEAM_NAME => srp_tm_rec.name,
240 P_START_DATE_ACTIVE => srp_tm_rec.start_date,
241 P_END_DATE_ACTIVE => srp_tm_rec.end_date,
242 P_EVENT_LOG_ID => l_event_log_id,
243 p_org_id => o.org_id);
244 END LOOP;
245 END LOOP;
246 END IF;
247
248 l_srp_tbl.DELETE;
249 END IF;
250 END IF; -- dates found
251 END LOOP; -- orgs
252
253 -- restore context
254 restore_context(l_orig_acc_mode, l_orig_org_id);
255
256 EXCEPTION
257 WHEN OTHERS THEN
258 x_return_status := fnd_api.g_ret_sts_unexp_error;
259 restore_context(l_orig_acc_mode, l_orig_org_id);
260 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
261 THEN
262 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
263 END IF;
264 FND_MSG_PUB.count_and_get
265 (
266 p_count => l_msg_count ,
267 p_data => l_msg_data ,
268 p_encoded => FND_API.g_false
269 );
270 END create_group_usage_post;
271
272 /* Vertcal Industry Procedure for pre processing in case of delete resource group usage */
273 PROCEDURE delete_group_usage_pre
274 (P_GROUP_ID IN NUMBER,
275 P_USAGE IN VARCHAR2,
276 X_RETURN_STATUS OUT NOCOPY VARCHAR2
277 ) IS
278
279 l_event_log_id NUMBER;
280 l_start_date DATE;
281 l_end_date DATE;
282 l_group_name VARCHAR2(60);
283 l_action_link_id NUMBER;
284 l_srp cn_rollup_pvt.srp_group_rec_type;
285 l_srp_tbl cn_rollup_pvt.srp_group_tbl_type;
286 l_return_status VARCHAR2(30);
287 l_msg_count NUMBER;
288 l_msg_data VARCHAR2(256);
289 i NUMBER;
290 l_api_name VARCHAR2(30) := 'delete_group_usage_pre';
291 --clku , fix max date year to 9999
292 l_max_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
293
294 l_orig_org_id NUMBER;
295 l_orig_acc_mode VARCHAR2(1);
296
297 -- cursor to get the start_date and end_date of the new group
298 CURSOR dates IS
299 SELECT start_date_active, end_date_active, name
300 FROM cn_comp_groups
301 WHERE comp_group_id = p_group_id;
302
303 -- cursor to find all srps in the new group
304 CURSOR srp_periods IS
305 SELECT cscg.salesrep_id,
306 cscg.comp_group_id,
307 intel.period_id,
308 greatest(cscg.start_date_active, intel.start_date) start_date,
309 decode(cscg.end_date_active, null, intel.end_date,
310 Least(cscg.end_date_active, intel.end_date)) end_date
311 FROM cn_srp_comp_groups_v cscg,
312 cn_srp_intel_periods intel
313 WHERE cscg.comp_group_id = p_group_id
314 and intel.salesrep_id = cscg.salesrep_id
315 and cscg.start_date_active <= intel.end_date
316 and (cscg.end_date_active is null or cscg.end_date_active >= intel.start_date);
317
318 -- cursor to find all periods in the date range for each srp
319 CURSOR periods(p_salesrep_id NUMBER, p_start_date DATE, p_end_date DATE) IS
320 SELECT p.period_id,
321 greatest(p_start_date, p.start_date) start_date,
322 Decode(p_end_date, NULL, p.end_date, Least(p_end_date, p.end_date)) end_date
323 FROM cn_srp_intel_periods p
324 WHERE p.salesrep_id = p_salesrep_id
325 AND (p_end_date IS NULL OR p.start_date <= p_end_date)
326 AND (p.end_date >= p_start_date);
327
328 -- check if the member is part of a team
329 CURSOR srp_team(p_salesrep_id NUMBER, p_group_id NUMBER)IS
330 select ct.name name,
331 ct.comp_team_id team_id,
332 greatest(cg.start_date_active, ct.start_date_active) start_date,
333 Least(nvl(ct.end_date_active, l_max_date), nvl(cg.end_date_active, l_max_date)) end_date
334 from cn_srp_comp_teams_v srt, cn_comp_teams ct, cn_comp_groups cg
335 where srt.salesrep_id = p_salesrep_id
336 and srt.comp_team_id = ct.comp_team_id
337 and cg.comp_group_id = p_group_id
338 and (cg.start_date_active <= ct.start_date_active
339 or cg.start_date_active between ct.start_date_active and nvl (ct.end_date_active, cg.start_date_active))
340 and nvl(cg.end_date_active, ct.start_date_active) >= ct.start_date_active;
341
342 CURSOR get_orgs IS
343 SELECT org_id FROM cn_repositories_all WHERE status = 'A';
344
345 BEGIN
346 x_return_status := fnd_api.g_ret_sts_success;
347
348 IF fnd_profile.value('CN_MARK_EVENTS') <> 'Y' THEN
349 RETURN;
350 END IF;
351
352 -- store MOAC session info in local variables
353 l_orig_org_id := mo_global.get_current_org_id;
354 l_orig_acc_mode := mo_global.get_access_mode;
355
356 -- loop through orgs
357 FOR o IN get_orgs LOOP
358 mo_global.set_policy_context('S', o.org_id);
359
360 -- if the usage is SALES_COMP, then find all srps in this group and their ancestors in the comp group hierarchy.
361 -- call mark_notify_salesreps for each of them
362 IF (p_usage = 'SALES_COMP') THEN
363 -- get the start_date and end_date of the new group
364 OPEN dates;
365 FETCH dates INTO l_start_date, l_end_date, l_group_name;
366 IF (dates%notfound) THEN
367 CLOSE dates;
368 ELSE
369 CLOSE dates;
370
371 cn_mark_events_pkg.log_event
372 ( p_event_name => 'CHANGE_CP_HIER_DELETE',
373 p_object_name => l_group_name,
374 p_object_id => p_group_id,
375 p_start_date => l_start_date,
376 p_start_date_old => NULL,
377 p_end_date => l_end_date,
378 p_end_date_old => NULL,
379 x_event_log_id => l_event_log_id,
380 p_org_id => o.org_id);
381
382 i := 0;
383 FOR srp_period IN srp_periods LOOP
384 -- get the info about the first salesrep to be used to get all the ancestors.
385 -- if the salesrep is not a manager and the ancestors will include managers in the same group, then
386 -- we can get the info of a manager in this group and use it to find ancestors.
387 IF (i = 0) THEN
388 l_srp.salesrep_id := srp_period.salesrep_id;
389 l_srp.group_id := srp_period.comp_group_id;
390 i := i + 1;
391 END IF;
392
393 cn_mark_events_pkg.mark_notify_salesreps
394 ( p_salesrep_id => srp_period.salesrep_id,
395 p_comp_group_id => srp_period.comp_group_id,
396 p_period_id => srp_period.period_id,
397 p_start_date => srp_period.start_date,
398 p_end_date => srp_period.end_date,
399 p_revert_to_state => 'CALC',
400 p_action => 'DELETE_DEST_XROLL',
401 p_action_link_id => NULL,
402 p_base_salesrep_id => NULL,
403 p_base_comp_group_id => NULL,
404 p_event_log_id => l_event_log_id,
405 x_action_link_id => l_action_link_id,
406 p_org_id => o.org_id);
407
408 -- check if this rep belongs to a team
409 FOR srp_tm_rec IN srp_team (srp_period.salesrep_id, p_group_id) LOOP
410
411 if srp_tm_rec.end_date = l_max_date then
412 srp_tm_rec.end_date := null;
413 end if;
414
415 cn_mark_events_pkg.mark_notify_team
416 (P_TEAM_ID => srp_tm_rec.team_id ,
417 P_TEAM_EVENT_NAME => 'CHANGE_TEAM_DEL_REP',
418 P_TEAM_NAME => srp_tm_rec.name,
419 P_START_DATE_ACTIVE => srp_tm_rec.start_date,
420 P_END_DATE_ACTIVE => srp_tm_rec.end_date,
421 P_EVENT_LOG_ID => l_event_log_id,
422 p_org_id => o.org_id);
423 END LOOP;
424 END LOOP;
425
426 -- find the ancestors of this salesrep and call mark_notify for all of them
427 -- not that we use l_start_date and l_end_date since this date range is the super range which covers
428 -- the date effectivity of all the salesreps in this group.
429 l_srp.start_date := l_start_date;
430 l_srp.end_date := l_end_date;
431 cn_rollup_pvt.get_ancestor_salesrep
432 ( p_api_version => 1.0,
433 p_init_msg_list => FND_API.G_false,
434 p_commit => FND_API.G_false,
435 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
436 x_return_status => l_return_status,
437 x_msg_count => l_msg_count,
438 x_msg_data => l_msg_data,
439 p_srp => l_srp,
440 p_org_id => o.org_id,
441 x_srp => l_srp_tbl);
442
443 IF (l_srp_tbl.COUNT > 0) THEN
444 FOR i IN l_srp_tbl.first..l_srp_tbl.last LOOP
445 FOR prd IN periods(l_srp_tbl(i).salesrep_id, l_srp_tbl(i).start_date, l_srp_tbl(i).end_date) LOOP
446 cn_mark_events_pkg.mark_notify_salesreps
447 ( p_salesrep_id => l_srp_tbl(i).salesrep_id,
448 p_comp_group_id => l_srp_tbl(i).group_id,
449 p_period_id => prd.period_id,
450 p_start_date => prd.start_date,
451 p_end_date => prd.end_date,
452 p_revert_to_state => 'CALC',
453 p_action => 'DELETE_SOURCE',
454 p_action_link_id => NULL,
455 p_base_salesrep_id => NULL,
456 p_base_comp_group_id => p_group_id,
457 p_event_log_id => l_event_log_id,
458 x_action_link_id => l_action_link_id,
459 p_org_id => o.org_id);
460 END LOOP;
461
462 -- check if this rep belongs to a team
463 FOR srp_tm_rec IN srp_team (l_srp_tbl(i).salesrep_id, l_srp_tbl(i).group_id) LOOP
464
465 if srp_tm_rec.end_date = l_max_date then
466 srp_tm_rec.end_date := null;
467 end if;
468
469 cn_mark_events_pkg.mark_notify_team
470 (P_TEAM_ID => srp_tm_rec.team_id ,
471 P_TEAM_EVENT_NAME => 'CHANGE_TEAM_DEL_REP',
472 P_TEAM_NAME => srp_tm_rec.name,
473 P_START_DATE_ACTIVE => srp_tm_rec.start_date,
474 P_END_DATE_ACTIVE => srp_tm_rec.end_date,
475 P_EVENT_LOG_ID => l_event_log_id,
476 p_org_id => o.org_id);
477 END LOOP;
478 END LOOP;
479 END IF;
480
481 l_srp_tbl.DELETE;
482 END IF;
483 END IF; -- dates found
484 END LOOP; -- orgs
485
486 -- restore context
487 restore_context(l_orig_acc_mode, l_orig_org_id);
488
489 EXCEPTION
490 WHEN OTHERS THEN
491 x_return_status := fnd_api.g_ret_sts_unexp_error;
492 restore_context(l_orig_acc_mode, l_orig_org_id);
493 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
494 THEN
495 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
496 END IF;
497 FND_MSG_PUB.count_and_get
498 (
499 p_count => l_msg_count ,
500 p_data => l_msg_data ,
501 p_encoded => FND_API.g_false
502 );
503 END delete_group_usage_pre;
504
505
506 /* Vertcal Industry Procedure for post processing in case of delete resource group usage */
507 PROCEDURE delete_group_usage_post
508 (P_GROUP_ID IN NUMBER,
509 P_USAGE IN VARCHAR2,
510 X_RETURN_STATUS OUT NOCOPY VARCHAR2
511 ) IS
512 BEGIN
513 x_return_status := fnd_api.g_ret_sts_success;
514 END delete_group_usage_post;
515
516 END jtf_rs_group_usage_vuhk;
[Home] [Help]