1 PACKAGE BODY JTF_RS_TEAM_MEMBER_VUHK AS
2 -- $Header: cntmmbrb.pls 120.1 2005/08/03 15:22:26 mblum noship $
3
4 -- helper procedure for the MOAC session context
5 PROCEDURE restore_context(p_acc_mode VARCHAR2,
6 p_org_id NUMBER) IS
7 BEGIN
8 IF p_acc_mode IS NOT NULL then
9 mo_global.set_policy_context(p_acc_mode, p_org_id);
10 END IF;
11 END restore_context;
12
13 PROCEDURE create_team_members_post
14 (P_TEAM_MEMBER_ID IN JTF_RS_TEAM_MEMBERS.TEAM_MEMBER_ID%TYPE,
15 P_TEAM_ID IN JTF_RS_TEAM_MEMBERS.TEAM_ID%TYPE,
16 P_TEAM_RESOURCE_ID IN JTF_RS_TEAM_MEMBERS.TEAM_RESOURCE_ID%TYPE,
17 P_RESOURCE_TYPE IN JTF_RS_TEAM_MEMBERS.RESOURCE_TYPE%TYPE,
18 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
19
20 t_team_name cn_comp_teams.name%type;
21 t_start_date_active cn_comp_teams.start_date_active%type;
22 t_end_date_active cn_comp_teams.end_date_active%type;
23 l_orig_org_id NUMBER;
24 l_orig_acc_mode VARCHAR2(1);
25
26 -- identify that the resource is a rep and the team is salescomp
27 CURSOR c_team_member IS
28 select ct.name, ct.start_date_active, ct.end_date_active
29 from cn_srp_comp_teams_v srpt, cn_comp_teams ct
30 where srpt.comp_team_id = P_TEAM_ID
31 and srpt.team_resource_id = P_TEAM_RESOURCE_ID
32 and ct.comp_team_id = P_TEAM_ID;
33
34 CURSOR get_orgs IS
35 SELECT org_id
36 FROM cn_salesreps
37 WHERE resource_id = p_team_resource_id;
38
39
40 BEGIN
41 X_RETURN_STATUS := fnd_api.g_ret_sts_success;
42
43 IF fnd_profile.value('CN_MARK_EVENTS') <> 'Y' THEN
44 RETURN;
45 END IF;
46
47 -- store MOAC session info in local variables
48 l_orig_org_id := mo_global.get_current_org_id;
49 l_orig_acc_mode := mo_global.get_access_mode;
50
51 -- loop through orgs
52 FOR o IN get_orgs LOOP
53 mo_global.set_policy_context('S', o.org_id);
54
55 OPEN c_team_member;
56 FETCH c_team_member INTO t_team_name, t_start_date_active, t_end_date_active;
57 IF (c_team_member%notfound) THEN
58 CLOSE c_team_member;
59 ELSE
60 CLOSE c_team_member;
61
62 cn_mark_events_pkg.mark_notify_team
63 (P_TEAM_ID => p_team_id,
64 P_TEAM_EVENT_NAME => 'CHANGE_TEAM_ADD_REP',
65 P_TEAM_NAME => t_team_name,
66 P_START_DATE_ACTIVE => t_start_date_active,
67 P_END_DATE_ACTIVE => t_end_date_active,
68 P_EVENT_LOG_ID => NULL,
69 p_org_id => o.org_id);
70 END IF;
71 END LOOP;
72
73 -- restore context
74 restore_context(l_orig_acc_mode, l_orig_org_id);
75 END create_team_members_post;
76
77 PROCEDURE delete_team_members_pre
78 (P_TEAM_ID IN JTF_RS_TEAM_MEMBERS.TEAM_ID%TYPE,
79 P_TEAM_RESOURCE_ID IN JTF_RS_TEAM_MEMBERS.TEAM_RESOURCE_ID%TYPE,
80 P_RESOURCE_TYPE IN JTF_RS_TEAM_MEMBERS.RESOURCE_TYPE%TYPE,
81 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
82
83 l_dummy NUMBER;
84 t_team_name cn_comp_teams.name%type;
85 t_start_date_active cn_comp_teams.start_date_active%type;
86 t_end_date_active cn_comp_teams.end_date_active%type;
87 l_orig_org_id NUMBER;
88 l_orig_acc_mode VARCHAR2(1);
89
90 -- identify that the resoiurce is a rep and the team is salescomp
91 CURSOR c_member IS
92 select 1
93 from cn_srp_comp_teams_v
94 where comp_team_id = P_TEAM_ID
95 and team_resource_id = P_TEAM_RESOURCE_ID;
96
97 -- get team info
98 CURSOR c_team_info IS
99 select name, start_date_active, end_date_active
100 from cn_comp_teams
101 where comp_team_id = P_TEAM_ID;
102
103 CURSOR get_orgs IS
104 SELECT org_id
105 FROM cn_salesreps
106 WHERE resource_id = p_team_resource_id;
107
108 BEGIN
109 X_RETURN_STATUS := fnd_api.g_ret_sts_success;
110
111 IF fnd_profile.value('CN_MARK_EVENTS') <> 'Y' THEN
112 RETURN;
113 END IF;
114
115 -- store MOAC session info in local variables
116 l_orig_org_id := mo_global.get_current_org_id;
117 l_orig_acc_mode := mo_global.get_access_mode;
118
119 -- loop through orgs
120 FOR o IN get_orgs LOOP
121 mo_global.set_policy_context('S', o.org_id);
122
123 OPEN c_member;
124 FETCH c_member INTO l_dummy;
125 IF (c_member%notfound) THEN
126 CLOSE c_member;
127 ELSE
128 CLOSE c_member;
129
130 cn_mark_events_pkg.mark_notify_team
131 (P_TEAM_ID => p_team_id,
132 P_TEAM_EVENT_NAME => 'CHANGE_TEAM_DEL_REP',
133 P_TEAM_NAME => null,
134 P_START_DATE_ACTIVE => null,
135 P_END_DATE_ACTIVE => null,
136 P_EVENT_LOG_ID => NULL,
137 p_org_id => o.org_id);
138 END IF;
139 END LOOP;
140
141 -- restore context
142 restore_context(l_orig_acc_mode, l_orig_org_id);
143 END delete_team_members_pre;
144
145 PROCEDURE update_team_members_pre
146 (P_TEAM_MEMBER_ID IN JTF_RS_TEAM_MEMBERS.TEAM_MEMBER_ID%TYPE,
147 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
148 BEGIN
149 x_return_status := fnd_api.g_ret_sts_success;
150 END update_team_members_pre;
151
152 PROCEDURE update_team_members_post
153 (P_TEAM_MEMBER_ID IN JTF_RS_TEAM_MEMBERS.TEAM_MEMBER_ID%TYPE,
154 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
155 BEGIN
156 x_return_status := fnd_api.g_ret_sts_success;
157 END update_team_members_post;
158
159 PROCEDURE delete_team_members_post
160 (P_TEAM_ID IN JTF_RS_TEAM_MEMBERS.TEAM_ID%TYPE,
161 P_TEAM_RESOURCE_ID IN JTF_RS_TEAM_MEMBERS.TEAM_RESOURCE_ID%TYPE,
162 P_RESOURCE_TYPE IN JTF_RS_TEAM_MEMBERS.RESOURCE_TYPE%TYPE,
163 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
164 BEGIN
165 x_return_status := fnd_api.g_ret_sts_success;
166 END delete_team_members_post;
167
168 PROCEDURE create_team_members_pre
169 (P_TEAM_ID IN JTF_RS_TEAM_MEMBERS.TEAM_ID%TYPE,
170 P_TEAM_RESOURCE_ID IN JTF_RS_TEAM_MEMBERS.TEAM_RESOURCE_ID%TYPE,
171 P_RESOURCE_TYPE IN JTF_RS_TEAM_MEMBERS.RESOURCE_TYPE%TYPE,
172 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
173 BEGIN
174 x_return_status := fnd_api.g_ret_sts_success;
175 END create_team_members_pre;
176
177 END jtf_rs_team_member_vuhk;
178