[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_ALIGN_ACTIVATE_PKG
Source
1 PACKAGE BODY JTF_TTY_ALIGN_ACTIVATE_PKG AS
2 /* $Header: jtftralb.pls 120.0 2005/06/02 18:21:25 appldev ship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTF_TTY_ALIGN_ACTIVATE_PKG
6 -- ---------------------------------------------------
7 -- PURPOSE
8 --
9 -- Procedures:
10 -- (see below for specification)
11 --
12 -- NOTES
13 -- This package is publicly available for use
14 --
15 -- HISTORY
16 -- 12/31/03 SPAI Created
17 --
18 -- 05/28/04 ACHANDA fix bug # 3656850
19
20
21 g_pkg_name CONSTANT VARCHAR2(30) := 'JTF_TTY_ALIGN_ACTIVATE_PKG';
22
23 PROCEDURE Activate_Alignment
24 /*******************************************************************************
25 ** Start of comments
26 ** Procedure : Activate_Alignment
27 ** Description : Create named account assignments for the selected Territory Alignment.
28
29
30 ** Notes :
31 **
32 ** End of comments
33 ******************************************************************************/
34 ( p_api_version_number IN NUMBER
35 , p_init_msg_list IN VARCHAR2
36 , p_alignment_id IN NUMBER
37 , p_user_id IN NUMBER
38 , x_return_status OUT NOCOPY VARCHAR2
39 , x_msg_count OUT NOCOPY VARCHAR2
40 , x_msg_data OUT NOCOPY VARCHAR2
41 )
42 IS
43 -- Cursor to find all the named accounts associated with this alignment
44 cursor c_all_accounts IS
45 select ALAC.TERR_GROUP_ACCOUNT_ID
46 from JTF_TTY_ALIGN_ACCTS ALAC
47 where
48 ALAC.ALIGNMENT_ID = p_alignment_id;
49
50 cursor c_get_invalid_accts( c_alignment_id NUMBER, c_user_id NUMBER) IS
51 SELECT 'Y'
52 FROM jtf_tty_align_accts aa
53 WHERE aa.alignment_id = c_alignment_id
54 AND aa.terr_group_account_id NOT IN
55 ( SELECT ga.terr_group_account_id
56 FROM jtf_tty_terr_grp_accts ga,
57 jtf_tty_terr_groups ttygrp,
58 jtf_tty_named_acct_rsc narsc,
59 jtf_tty_srch_my_resources_v repdn
60 WHERE ttygrp.terr_group_id = ga.terr_group_id
61 AND ttygrp.active_from_date <= sysdate
62 AND ( ttygrp.active_to_date is null
63 or
64 ttygrp.active_to_date >= sysdate
65 )
66 AND ga.terr_group_account_id = narsc.terr_group_account_id
67 AND narsc.resource_id = repdn.resource_id
68 AND narsc.rsc_group_id = repdn.group_id
69 AND repdn.current_user_id = c_user_id
70 );
71
72 cursor c_get_invalid_roles (c_alignment_id NUMBER )
73 IS
74 SELECT 'Y'
75 FROM jtf_tty_pterr_accts alpa
76 , jtf_tty_align_pterr alpt
77 , jtf_tty_align_accts alac
78 , jtf_tty_terr_grp_accts tga
79 WHERE alpt.align_proposed_terr_id = alpa.align_proposed_terr_id
80 AND alpa.align_acct_id = alac.align_acct_id
81 AND alac.terr_group_account_id = tga.terr_group_account_id
82 AND alac.alignment_id = c_alignment_id
83 AND NOT EXISTS ( SELECT 'Y'
84 FROM jtf_tty_terr_grp_roles TGR
85 WHERE tgr.role_code = alpt.rsc_role_code
86 AND tgr.terr_group_id = tga.terr_group_id
87 );
88
89
90 account_rsc_table account_rsc_table_type;
91
92 l_api_version_number CONSTANT NUMBER := 1.0;
93 l_api_name CONSTANT VARCHAR2(30) := 'Activate_Alignment';
94 l_invalid_align_flag VARCHAR2(1);
95 l_return_status VARCHAR2(2);
96 l_invalid_align EXCEPTION;
97 l_login_id NUMBER ;
98
99
100 BEGIN
101 --dbms_output.put_line('begin Activate_Alignment');
102
103 l_invalid_align_flag := 'N';
104 l_return_status := 'S' ;
105
106 -- Standard call to check for call compatibility.
107 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
108 p_api_version_number,
109 l_api_name,
110 G_PKG_NAME)
111 THEN
112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
113 END IF;
114
115 -- Initialize message list if p_init_msg_list is set to TRUE.
116 IF FND_API.to_Boolean( p_init_msg_list )
117 THEN
118 FND_MSG_PUB.initialize;
119 END IF;
120
121 -- Debug Message
122 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
123 THEN
124 FND_MESSAGE.Set_Name('JTF', 'JTF_TTY_ALIGN_ACTIVATE_START');
125 FND_MSG_PUB.Add;
126 END IF;
127
128 -- API body
129 x_return_status := 'S' ;
130 l_login_id := FND_GLOBAL.login_id;
131
132 BEGIN
133 OPEN c_get_invalid_accts(c_alignment_id => p_alignment_id,
134 c_user_id => p_user_id);
135 FETCH c_get_invalid_accts INTO l_invalid_align_flag;
136 CLOSE c_get_invalid_accts;
137
138 IF ( l_invalid_align_flag = 'Y' )
139 THEN
140 l_return_status := 'IA';
141 raise l_invalid_align;
142 END IF;
143
144 END;
145
146 BEGIN
147 l_invalid_align_flag := 'N' ;
148 OPEN c_get_invalid_roles(c_alignment_id => p_alignment_id );
149 FETCH c_get_invalid_roles INTO l_invalid_align_flag;
150 CLOSE c_get_invalid_roles;
151
152 IF ( l_invalid_align_flag = 'Y' )
153 THEN
154 l_return_status := 'IR';
155 raise l_invalid_align;
156 END IF;
157
158 END;
159
160 OPEN c_all_accounts;
161 FETCH c_all_accounts BULK COLLECT INTO account_rsc_table;
162 CLOSE c_all_accounts;
163
164 IF account_rsc_table.COUNT > 0
165 THEN
166 --dbms_output.put_line('delete from JTF_TTY_NAMED_ACCT_RSC');
167 FORALL i IN account_rsc_table.FIRST .. account_rsc_table.LAST
168 -- delete old resource assignments owned by the RM for this named account.
169 delete from JTF_TTY_NAMED_ACCT_RSC NARS
170 WHERE NARS.TERR_GROUP_ACCOUNT_ID = account_rsc_table(i)
171 AND (NARS.resource_id, NARS.rsc_group_id, NARS.rsc_role_code) IN (
172 /* Salesperson directly/indirectly reports to user */
173 SELECT dir.resource_id
174 , grpmemo.group_id
175 , rol.role_code
176 FROM jtf_rs_roles_b rol
177 , jtf_rs_role_relations rlt
178 , jtf_rs_group_members grpmemo
179 , jtf_rs_resource_extns dir
180 WHERE ( rol.manager_flag = 'Y' or rol.member_flag = 'Y' )
181 AND rlt.role_id = rol.role_id
182 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
183 AND rlt.delete_flag = 'N'
184 AND SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active, SYSDATE+1)
185 AND rlt.role_resource_id = grpmemo.group_member_id
186 AND grpmemo.delete_flag = 'N'
187 AND grpmemo.resource_id = dir.resource_id
188 AND SYSDATE BETWEEN dir.start_date_active AND NVL(dir.end_date_active, SYSDATE+1)
189 AND grpmemo.group_id IN ( SELECT dv.group_id
190 FROM jtf_rs_group_usages usg
191 , jtf_rs_groups_denorm dv
192 , jtf_rs_rep_managers sgh
193 , jtf_rs_resource_extns mrsc
194 WHERE usg.usage = 'SALES'
195 AND usg.group_id = dv.group_id
196 -- AND dv.immediate_parent_flag = 'Y'
197 AND dv.parent_group_id = sgh.group_id
198 AND SYSDATE BETWEEN NVL(dv.start_date_active, SYSDATE-1)
199 AND NVL(dv.end_date_active, SYSDATE+1)
200 AND SYSDATE BETWEEN sgh.start_date_active AND NVL(sgh.end_date_active, SYSDATE+1)
201 AND sgh.hierarchy_type IN ('MGR_TO_MGR')
202 AND sgh.resource_id = sgh.parent_resource_id
203 AND mrsc.resource_id = sgh.resource_id
204 AND mrsc.user_id = p_user_id )
205 ) ;
206
207 --dbms_output.put_line('insert into jtf_tty_named_acct_rsc');
208 FORALL j IN account_rsc_table.FIRST .. account_rsc_table.LAST
209 insert into jtf_tty_named_acct_rsc
210 (ACCOUNT_RESOURCE_ID,
211 OBJECT_VERSION_NUMBER ,
212 TERR_GROUP_ACCOUNT_ID,
213 RESOURCE_ID ,
214 RSC_GROUP_ID,
215 RSC_ROLE_CODE,
216 ASSIGNED_FLAG,
217 RSC_RESOURCE_TYPE,
218 CREATED_BY ,
219 CREATION_DATE ,
220 LAST_UPDATED_BY ,
221 LAST_UPDATE_DATE ,
222 LAST_UPDATE_LOGIN
223 )
224 select
225 jtf_tty_named_acct_rsc_s.nextval
226 , 2
227 , ALAC.terr_group_account_id
228 , ALPT.RESOURCE_ID
229 , ALPT.RSC_GROUP_ID
230 , ALPT.RSC_ROLE_CODE
231 , 'Y'
232 , 'RS_EMPLOYEE'
233 , p_user_id
234 , sysdate
235 , p_user_id
236 , sysdate
237 , l_login_id
238 from
239 JTF_TTY_PTERR_ACCTS ALPA
240 , JTF_TTY_ALIGN_PTERR ALPT
241 , JTF_TTY_ALIGN_ACCTS ALAC
242 where
243 ALPA.ALIGN_PROPOSED_TERR_ID = ALPT.ALIGN_PROPOSED_TERR_ID
244 and ALAC.ALIGN_ACCT_ID = ALPA.ALIGN_ACCT_ID
245 and ALAC.TERR_GROUP_ACCOUNT_ID = account_rsc_table(j)
246 and ALAC.alignment_id = p_alignment_id
247 -- check to ensure inserted resource is still valid
248 and (ALPT.resource_id, ALPT.rsc_group_id, ALPT.rsc_role_code) IN
249 ( SELECT dir.resource_id
250 , grpmemo.group_id
251 , rol.role_code
252 FROM jtf_rs_roles_b rol
253 , jtf_rs_role_relations rlt
254 , jtf_rs_group_members grpmemo
255 , jtf_rs_resource_extns dir
256 , ( SELECT distinct dv.group_id, dv.immediate_parent_flag child_group_flag
257 FROM jtf_rs_group_usages usg
258 , jtf_rs_groups_denorm dv
259 , jtf_rs_rep_managers sgh
260 , jtf_rs_resource_extns mrsc
261 WHERE usg.usage = 'SALES'
262 AND usg.group_id = dv.group_id
263 AND ( dv.immediate_parent_flag = 'Y' OR dv.group_id = dv.parent_group_id )
264 AND dv.parent_group_id = sgh.group_id
265 AND SYSDATE BETWEEN NVL(dv.start_date_active, SYSDATE-1)
266 AND NVL(dv.end_date_active, SYSDATE+1)
267 AND SYSDATE BETWEEN sgh.start_date_active AND NVL(sgh.end_date_active, SYSDATE+1)
268 AND sgh.hierarchy_type IN ('MGR_TO_MGR')
269 AND sgh.resource_id = sgh.parent_resource_id
270 AND mrsc.resource_id = sgh.resource_id
271 AND mrsc.user_id = p_user_id ) MY_GRPS
272 WHERE
273 rlt.role_id = rol.role_id
274 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
275 AND rlt.delete_flag = 'N'
276 AND SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active, SYSDATE+1)
277 AND rlt.role_resource_id = grpmemo.group_member_id
278 AND grpmemo.delete_flag = 'N'
279 AND grpmemo.resource_id = dir.resource_id
280 AND SYSDATE BETWEEN dir.start_date_active AND NVL(dir.end_date_active, SYSDATE+1)
281 AND grpmemo.group_id = MY_GRPS.group_id
282 AND ( rol.manager_flag = 'Y' OR
283 ( rol.member_flag = 'Y' and MY_GRPS.child_group_flag = 'N' ) )
284 ) ;
285
286 FORALL j IN account_rsc_table.FIRST .. account_rsc_table.LAST
287 INSERT into jtf_tty_named_acct_changes
288 ( NAMED_ACCT_CHANGE_ID
289 ,OBJECT_VERSION_NUMBER
290 ,OBJECT_TYPE
291 ,OBJECT_ID
292 ,CHANGE_TYPE
293 ,FROM_WHERE
294 ,CREATED_BY
295 ,CREATION_DATE
296 ,LAST_UPDATED_BY
297 ,LAST_UPDATE_DATE
298 ,LAST_UPDATE_LOGIN
299 ) values
300 ( jtf_tty_named_acct_changes_s.nextval
301 ,1
302 ,'TGA'
303 ,account_rsc_table(j)
304 ,'UPDATE'
305 ,'ACTIVATE ALIGNMENT'
306 ,p_user_id
307 ,sysdate
308 ,p_user_id
309 ,sysdate
310 ,l_login_id );
311
312 --NOTE: Denorm/Summary tables (or their substitutes) need to be updated.
313 --dbms_output.put_line('commit changes');
314
315 END IF; --account_rsc_table is not null
316
317 -- set activated_on date for this alignment to sysdate
318 UPDATE JTF_TTY_ALIGNMENTS
319 SET activated_on = SYSDATE
320 ,alignment_status = 'A'
321 ,last_updated_by = p_user_id
322 ,last_update_date = sysdate
323 WHERE alignment_id = p_alignment_id;
324
325 COMMIT;
326
327 -- Standard call to get message count and if count is 1, get message info.
328 FND_MSG_PUB.Count_And_Get
329 ( p_count => x_msg_count,
330 p_data => x_msg_data
331 );
332 EXCEPTION
333 -- WHEN NO_DATA_FOUND THEN NULL;
334 WHEN l_invalid_align THEN
335 x_return_status := l_return_status;
336 WHEN OTHERS THEN
337 x_return_status := 'U';
338 x_msg_data := substr(sqlerrm, 1, 200) ;
339 IF (c_all_accounts%ISOPEN) THEN
340 CLOSE c_all_accounts;
341 END IF;
342 IF (c_get_invalid_accts%ISOPEN) THEN
343 CLOSE c_get_invalid_accts;
344 END IF;
345 IF (c_get_invalid_roles%ISOPEN) THEN
346 CLOSE c_get_invalid_roles;
347 END IF;
348 END Activate_Alignment;
349
350 END JTF_TTY_ALIGN_ACTIVATE_PKG;