[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_ROLES_VUHK
Source
1 PACKAGE BODY jtf_rs_roles_vuhk AS
2 /* $Header: cnirsrob.pls 120.3 2005/07/29 11:13:16 mblum noship $ */
3 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_ROLES_VUHK';
4
5 -- helper procedure for the MOAC session context
6 PROCEDURE restore_context(p_acc_mode VARCHAR2,
7 p_org_id NUMBER) IS
8 BEGIN
9 IF p_acc_mode IS NOT NULL then
10 mo_global.set_policy_context(p_acc_mode, p_org_id);
11 END IF;
12 END restore_context;
13
14 -- Vertical Industry Procedure for pre processing in case of create
15 -- resource roles
16
17 PROCEDURE create_rs_resource_roles_pre
18 (P_ROLE_TYPE_CODE IN JTF_RS_ROLES_B.ROLE_TYPE_CODE%TYPE,
19 P_ROLE_CODE IN JTF_RS_ROLES_B.ROLE_CODE%TYPE,
20 P_ROLE_NAME IN JTF_RS_ROLES_TL.ROLE_NAME%TYPE,
21 P_ROLE_DESC IN JTF_RS_ROLES_TL.ROLE_DESC%TYPE,
22 P_ACTIVE_FLAG IN JTF_RS_ROLES_B.ACTIVE_FLAG%TYPE,
23 P_SEEDED_FLAG IN JTF_RS_ROLES_B.SEEDED_FLAG%TYPE,
24 P_MEMBER_FLAG IN JTF_RS_ROLES_B.MEMBER_FLAG%TYPE,
25 P_ADMIN_FLAG IN JTF_RS_ROLES_B.ADMIN_FLAG%TYPE,
26 P_LEAD_FLAG IN JTF_RS_ROLES_B.LEAD_FLAG%TYPE,
27 P_MANAGER_FLAG IN JTF_RS_ROLES_B.MANAGER_FLAG%TYPE,
28 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
29 X_MSG_COUNT OUT NOCOPY NUMBER,
30 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
31 BEGIN
32 x_return_status := fnd_api.g_ret_sts_success;
33 END create_rs_resource_roles_pre;
34
35 -- Vertical Industry Procedure for post processing in case of create
36 -- resource roles
37
38 PROCEDURE create_rs_resource_roles_post
39 (P_ROLE_TYPE_CODE IN JTF_RS_ROLES_B.ROLE_TYPE_CODE%TYPE,
40 P_ROLE_CODE IN JTF_RS_ROLES_B.ROLE_CODE%TYPE,
41 P_ROLE_NAME IN JTF_RS_ROLES_TL.ROLE_NAME%TYPE,
42 P_ROLE_DESC IN JTF_RS_ROLES_TL.ROLE_DESC%TYPE,
43 P_ACTIVE_FLAG IN JTF_RS_ROLES_B.ACTIVE_FLAG%TYPE,
44 P_SEEDED_FLAG IN JTF_RS_ROLES_B.SEEDED_FLAG%TYPE,
45 P_MEMBER_FLAG IN JTF_RS_ROLES_B.MEMBER_FLAG%TYPE,
46 P_ADMIN_FLAG IN JTF_RS_ROLES_B.ADMIN_FLAG%TYPE,
47 P_LEAD_FLAG IN JTF_RS_ROLES_B.LEAD_FLAG%TYPE,
48 P_MANAGER_FLAG IN JTF_RS_ROLES_B.MANAGER_FLAG%TYPE,
49 P_ROLE_ID IN JTF_RS_ROLES_B.ROLE_ID%TYPE,
50 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
51 X_MSG_COUNT OUT NOCOPY NUMBER,
52 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
53
54 BEGIN
55 x_return_status := fnd_api.g_ret_sts_success;
56 END create_rs_resource_roles_post;
57
58 -- Vertical Industry Procedure for pre processing in case of update
59 -- resource roles
60 PROCEDURE update_rs_resource_roles_pre
61 (P_ROLE_ID IN JTF_RS_ROLES_B.ROLE_ID%TYPE,
62 P_ROLE_TYPE_CODE IN JTF_RS_ROLES_B.ROLE_TYPE_CODE%TYPE,
63 P_ROLE_CODE IN JTF_RS_ROLES_B.ROLE_CODE%TYPE,
64 P_ROLE_NAME IN JTF_RS_ROLES_TL.ROLE_NAME%TYPE,
65 P_ROLE_DESC IN JTF_RS_ROLES_TL.ROLE_DESC%TYPE,
66 P_ACTIVE_FLAG IN JTF_RS_ROLES_B.ACTIVE_FLAG%TYPE,
67 P_SEEDED_FLAG IN JTF_RS_ROLES_B.SEEDED_FLAG%TYPE,
68 P_MEMBER_FLAG IN JTF_RS_ROLES_B.MEMBER_FLAG%TYPE,
69 P_ADMIN_FLAG IN JTF_RS_ROLES_B.ADMIN_FLAG%TYPE,
70 P_LEAD_FLAG IN JTF_RS_ROLES_B.LEAD_FLAG%TYPE,
71 P_MANAGER_FLAG IN JTF_RS_ROLES_B.MANAGER_FLAG%TYPE,
72 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
73 X_MSG_COUNT OUT NOCOPY NUMBER,
74 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
75
76 l_manager_flag jtf_rs_roles_b.manager_flag%TYPE;
77 l_member_flag jtf_rs_roles_b.member_flag%TYPE;
78
79 l_action_link_id NUMBER;
80 l_event_log_id NUMBER;
81 l_api_name VARCHAR2(30) := 'update_rs_resource_roles_pre';
82 l_return_status VARCHAR2(1);
83 l_loading_status VARCHAR2(30);
84 l_msg_count NUMBER;
85 l_msg_data VARCHAR2(2000);
86 l_temp_count NUMBER;
87 l_team_event VARCHAR2(50);
88 -- clku, fix max date year to 9999
89 l_max_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
90
91 l_orig_org_id NUMBER;
92 l_orig_acc_mode VARCHAR2(1);
93
94 -- cursor to get the old manager_flag
95 CURSOR mem_mgr_flag IS
96 SELECT manager_flag, member_flag
97 FROM jtf_rs_roles_b
98 WHERE role_id = p_role_id
99 AND role_type_code = 'SALES_COMP';
100
101 CURSOR srp_periods IS
102 SELECT cscg.salesrep_id,
103 cscg.comp_group_id,
104 intel.period_id,
105 greatest(cscg.start_date_active, intel.start_date) start_date,
106 decode(cscg.end_date_active, null, intel.end_date,
107 Least(cscg.end_date_active, intel.end_date)) end_date
108 FROM cn_srp_comp_groups_v cscg,
109 cn_srp_intel_periods intel
110 WHERE cscg.role_id = p_role_id
111 and intel.salesrep_id = cscg.salesrep_id
112 and cscg.start_date_active <= intel.end_date
113 and (cscg.end_date_active is null or
114 cscg.end_date_active >= intel.start_date);
115
116 -- get the team info associated with the reps who are inturn associated with the role
117 CURSOR srp_team(p_role_id NUMBER)IS
118 select distinct ct.name name,
119 ct.comp_team_id team_id,
120 greatest(scg.start_date_active, ct.start_date_active) start_date,
121 Least(nvl(ct.end_date_active, l_max_date) , nvl(scg.end_date_active, l_max_date)) end_date
122 from cn_srp_comp_teams_v srt, cn_comp_teams ct, cn_srp_comp_groups_v scg
123 where scg.role_id = p_role_id
124 and srt.salesrep_id = scg.salesrep_id
125 and srt.comp_team_id = ct.comp_team_id
126 and (scg.start_date_active <= ct.start_date_active
127 or scg.start_date_active between ct.start_date_active and nvl (ct.end_date_active, scg.start_date_active))
128 and nvl(scg.end_date_active, ct.start_date_active) >= ct.start_date_active;
129
130 CURSOR get_orgs IS
131 SELECT org_id
132 FROM cn_repositories_all
133 WHERE status = 'A';
134
135 BEGIN
136 x_return_status := fnd_api.g_ret_sts_success;
137
138 -- handle mark events
139 IF fnd_profile.value('CN_MARK_EVENTS') = 'Y' THEN
140
141 l_manager_flag := 'X';
142 l_member_flag := 'X';
143
144 -- get the old value of manager_flag and member_flag
145 OPEN mem_mgr_flag;
146 FETCH mem_mgr_flag INTO l_manager_flag, l_member_flag;
147 CLOSE mem_mgr_flag;
148
149 if l_manager_flag = 'X' and l_member_flag = 'X' then
150 return;
151 end if;
152
153 -- store MOAC session info in local variables
154 l_orig_org_id := mo_global.get_current_org_id;
155 l_orig_acc_mode := mo_global.get_access_mode;
156
157 -- loop through orgs
158 FOR o IN get_orgs LOOP
159 mo_global.set_policy_context('S', o.org_id);
160
161 IF (p_member_flag = 'Y' AND l_member_flag = 'N') THEN
162 cn_mark_events_pkg.log_event
163 (p_event_name => 'CHANGE_CP_ADD_SRP',
164 p_object_name => p_role_name,
165 p_object_id => p_role_id,
166 p_start_date => NULL,
167 p_start_date_old => NULL,
168 p_end_date => NULL,
169 p_end_date_old => NULL,
170 x_event_log_id => l_event_log_id,
171 p_org_id => o.org_id);
172
173 FOR srp_period IN srp_periods LOOP
174 cn_mark_events_pkg.mark_notify_salesreps
175 (p_salesrep_id => srp_period.salesrep_id,
176 p_comp_group_id => srp_period.comp_group_id,
177 p_period_id => srp_period.period_id,
178 p_start_date => srp_period.start_date,
179 p_end_date => srp_period.end_date,
180 p_revert_to_state => 'ROLL',
181 p_action => 'PULL_WITHIN',
182 p_action_link_id => NULL,
183 p_base_salesrep_id => NULL,
184 p_base_comp_group_id => NULL,
185 p_event_log_id => l_event_log_id,
186 x_action_link_id => l_action_link_id,
187 p_org_id => o.org_id);
188 END LOOP;
189 END IF;
190
191 -- manager flag changes from Y --> N
192 IF (p_manager_flag = 'N' AND l_manager_flag = 'Y') THEN
193 -- log event for the update of the manager_flag
194 cn_mark_events_pkg.log_event
195 (p_event_name => 'CHANGE_CP_DELETE_MGR',
196 p_object_name => p_role_name,
197 p_object_id => p_role_id,
198 p_start_date => NULL,
199 p_start_date_old => NULL,
200 p_end_date => NULL,
201 p_end_date_old => NULL,
202 x_event_log_id => l_event_log_id,
203 p_org_id => o.org_id);
204
205 FOR srp_period IN srp_periods LOOP
206 cn_mark_events_pkg.mark_notify_salesreps
207 (p_salesrep_id => srp_period.salesrep_id,
208 p_comp_group_id => srp_period.comp_group_id,
209 p_period_id => srp_period.period_id,
210 p_start_date => srp_period.start_date,
211 p_end_date => srp_period.end_date,
212 p_revert_to_state => 'CALC',
213 p_action => 'DELETE_DEST_WITHIN',
214 p_action_link_id => NULL,
215 p_base_salesrep_id => NULL,
216 p_base_comp_group_id => NULL,
217 p_role_id => p_role_id,
218 p_event_log_id => l_event_log_id,
219 x_action_link_id => l_action_link_id,
220 p_org_id => o.org_id);
221 END LOOP;
222 -- manager flag changes from N -> Y
223 ELSIF (p_manager_flag = 'Y' AND l_manager_flag = 'N') THEN
224 -- log event for the update of the manager_flag
225 cn_mark_events_pkg.log_event
226 (p_event_name => 'CHANGE_CP_ADD_MGR',
227 p_object_name => p_role_name,
228 p_object_id => p_role_id,
229 p_start_date => NULL,
230 p_start_date_old => NULL,
231 p_end_date => NULL,
232 p_end_date_old => NULL,
233 x_event_log_id => l_event_log_id,
234 p_org_id => o.org_id);
235
236 FOR srp_period IN srp_periods LOOP
237 cn_mark_events_pkg.mark_notify_salesreps
238 (p_salesrep_id => srp_period.salesrep_id,
239 p_comp_group_id => srp_period.comp_group_id,
240 p_period_id => srp_period.period_id,
241 p_start_date => srp_period.start_date,
242 p_end_date => srp_period.end_date,
243 p_revert_to_state => 'ROLL',
244 p_action => 'PULL_WITHIN',
245 p_action_link_id => NULL,
246 p_base_salesrep_id => NULL,
247 p_base_comp_group_id => NULL,
248 p_event_log_id => l_event_log_id,
249 x_action_link_id => l_action_link_id,
250 p_org_id => o.org_id);
251 END LOOP;
252 END IF; -- if mgr flag moved from Y to N
253
254 -- Team related events
255 IF ((l_manager_flag = 'Y' AND p_manager_flag = 'N') OR (l_member_flag = 'Y' and p_member_flag = 'N')) THEN
256 l_team_event := 'CHANGE_TEAM_DEL_REP';
257 ELSIF ((l_manager_flag = 'N' AND p_manager_flag = 'Y') OR (l_member_flag = 'N' and p_member_flag = 'Y')) THEN
258 l_team_event := 'CHANGE_TEAM_ADD_REP';
259 ELSIF (p_manager_flag = 'Y' OR p_member_flag = 'Y') THEN
260 l_team_event := 'CHANGE_TEAM_ADD_REP';
261 ELSIF (p_manager_flag = 'N' OR p_member_flag = 'N') THEN
262 l_team_event := 'CHANGE_TEAM_DEL_REP';
263 END IF;
264
265 FOR srp_tm_rec IN srp_team (p_role_id) LOOP
266
267 if srp_tm_rec.end_date = l_max_date then
268 srp_tm_rec.end_date := null;
269 end if;
270
271 cn_mark_events_pkg.mark_notify_team
272 (P_TEAM_ID => srp_tm_rec.team_id ,
273 P_TEAM_EVENT_NAME => l_team_event,
274 P_TEAM_NAME => srp_tm_rec.name,
275 P_START_DATE_ACTIVE => srp_tm_rec.start_date,
276 P_END_DATE_ACTIVE => srp_tm_rec.end_date,
277 P_EVENT_LOG_ID => l_event_log_id,
278 p_org_id => o.org_id);
279 END LOOP;
280 END LOOP; -- orgs
281
282 -- restore context
283 restore_context(l_orig_acc_mode, l_orig_org_id);
284 END IF; -- if mark events set to Y
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 x_return_status := fnd_api.g_ret_sts_unexp_error;
289 restore_context(l_orig_acc_mode, l_orig_org_id);
290 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
291 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
292 END IF;
293 FND_MSG_PUB.count_and_get
294 (p_count => x_msg_count ,
295 p_data => x_msg_data ,
296 p_encoded => FND_API.g_false);
297 END update_rs_resource_roles_pre;
298
299
300 -- Vertical Industry Procedure for post processing in case of update
301 -- resource roles
302
303 PROCEDURE update_rs_resource_roles_post
304 (P_ROLE_ID IN JTF_RS_ROLES_B.ROLE_ID%TYPE,
305 P_ROLE_TYPE_CODE IN JTF_RS_ROLES_B.ROLE_TYPE_CODE%TYPE,
306 P_ROLE_CODE IN JTF_RS_ROLES_B.ROLE_CODE%TYPE,
307 P_ROLE_NAME IN JTF_RS_ROLES_TL.ROLE_NAME%TYPE,
308 P_ROLE_DESC IN JTF_RS_ROLES_TL.ROLE_DESC%TYPE,
309 P_ACTIVE_FLAG IN JTF_RS_ROLES_B.ACTIVE_FLAG%TYPE,
310 P_SEEDED_FLAG IN JTF_RS_ROLES_B.SEEDED_FLAG%TYPE,
311 P_MEMBER_FLAG IN JTF_RS_ROLES_B.MEMBER_FLAG%TYPE,
312 P_ADMIN_FLAG IN JTF_RS_ROLES_B.ADMIN_FLAG%TYPE,
313 P_LEAD_FLAG IN JTF_RS_ROLES_B.LEAD_FLAG%TYPE,
314 P_MANAGER_FLAG IN JTF_RS_ROLES_B.MANAGER_FLAG%TYPE,
315 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
316 X_MSG_COUNT OUT NOCOPY NUMBER,
317 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
318 BEGIN
319 x_return_status := fnd_api.g_ret_sts_success;
320 END update_rs_resource_roles_post;
321
322 -- Vertical Industry Procedure for pre processing in case of delete
323 -- resource roles
324
325 PROCEDURE delete_rs_resource_roles_pre
326 (P_ROLE_ID IN JTF_RS_ROLES_B.ROLE_ID%TYPE,
327 P_ROLE_CODE IN JTF_RS_ROLES_B.ROLE_CODE%TYPE,
328 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
329 X_MSG_COUNT OUT NOCOPY NUMBER,
330 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
331
332 -- for API call to CN role details
333 l_return_status VARCHAR2(1);
334 l_loading_status VARCHAR2(30);
335 l_msg_count NUMBER;
336 l_msg_data VARCHAR2(2000);
337
338 BEGIN
339 x_return_status := fnd_api.g_ret_sts_success;
340 END delete_rs_resource_roles_pre;
341
342 -- Vertical Industry Procedure for post processing in case of delete
343 -- resource roles
344
345 PROCEDURE delete_rs_resource_roles_post
346 (P_ROLE_ID IN JTF_RS_ROLES_B.ROLE_ID%TYPE,
347 P_ROLE_CODE IN JTF_RS_ROLES_B.ROLE_CODE%TYPE,
348 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
349 X_MSG_COUNT OUT NOCOPY NUMBER,
350 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
351 BEGIN
352 x_return_status := fnd_api.g_ret_sts_success;
353 END delete_rs_resource_roles_post;
354
355
356 END jtf_rs_roles_vuhk;