1 PACKAGE BODY JTF_TTY_NACCT_SALES_PUB AS
2 /* $Header: jtfnacsb.pls 120.3 2005/10/22 17:42:26 shli ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2002 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +===========================================================================*/
8 -- Start of Comments
9 -- ---------------------------------------------------
10 -- PACKAGE NAME: JTF_TTY_NACCT_SALES_PUB
11 -- ---------------------------------------------------
12 -- PURPOSE
13 --
14 -- Procedures:
15 -- (see below for specification)
16 --
17 -- NOTES
18 --
19 -- HISTORY
20 --
21 -- 11/27/2002 EIHSU (Edward Hsu) Assign Subsidiary cursor fix
22 -- 12/03/2002 EIHSU Assign Subsidiary cursor fix again - phase 0 added
23 -- 12/25/2002 EIHSU Simple Search now calling Update Sales Team with user_id
24 -- fetching user_resource_id from user_id needed.
25 -- 01/01/2003 EIHSU Fix bugs 2726632, 2729173
26 -- 01/07/2003 EIHSU BUG 2729383
27 -- 01/23/2003 EIHSU BUG 2766624
28 -- 01/28/2003 EIHSU BUG 2774021
29 -- 02/05/2003 EIHSU BUG TO SET ASSIGN FLAG PROPERLY
30 -- 02/10/2003 EIHSU Cursor fix for assign flag
31 -- 02/10/2003 EIHSU assign flag variable used for insert row
32 -- 02/10/2003 EIHSU bug 2797295
33 -- 02/14/2003 EIHSU bug 2803830
34 -- 02/25/2003 EIHSU bug 2816957, 2816972
35 -- 02/27/2003 EIHSU bug 2826052
36 -- 02/27/2003 EIHSU bug 2828011
37 -- 04/17/2003 ARPATEL bug 2885573 - performance fixes.
38 -- 12/03/2003 ACHANDA bug 3265188 - performance fixes.
39
40 -- End of Comments
41 --
42 --*******************************************************
43 -- Start of Comments
44 --*******************************************************
45
46 --**************************************
47 -- PROCEDURE UPDATE_SALES_TEAM
48 --**************************************
49
50 -- input:
51 -- [list of] lp_resource_id, lp_group_id, lp_role_code
52 -- [list of] lp_party_id
53 -- FROM CALLING PAGE
54 -- lp_current_user_resource_id NOTE THIS PARAMETER NO LONGER USED
55 -- p_user_attribute1 IS NOW USED INSTEAD, value is USER_ID
56 -- lp_territory_group_id
57
58 PROCEDURE UPDATE_SALES_TEAM(
59 p_api_version_number IN NUMBER,
60 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
61 p_SQL_Trace IN VARCHAR2,
62 p_Debug_Flag IN VARCHAR2,
63 x_return_status OUT NOCOPY VARCHAR2,
64 x_msg_count OUT NOCOPY NUMBER,
65 x_msg_data OUT NOCOPY VARCHAR2,
66
67 p_user_resource_id IN NUMBER, -- NOTE THIS IS NOT USED, user_attr1 used for user_id instead.
68 p_terr_group_id IN NUMBER,
69 p_user_attribute1 IN VARCHAR2,
70 p_user_attribute2 IN VARCHAR2,
71 p_added_rscs_tbl IN SALESREP_RSC_TBL_TYPE,
72 p_removed_rscs_tbl IN SALESREP_RSC_TBL_TYPE,
73 p_affected_parties_tbl IN AFFECTED_PARTY_TBL_TYPE,
74 ERRBUF OUT NOCOPY VARCHAR2,
75 RETCODE OUT NOCOPY VARCHAR2
76 )
77 IS
78
79 l_user_id NUMBER := p_user_attribute1;
80 /*lp_user_resource_id NUMBER := p_user_resource_id; */
81 lp_user_resource_type VARCHAR2(30) := 'RS_EMPLOYEE';
82 lp_terr_group_id NUMBER := p_terr_group_id;
83 l_terr_group_id NUMBER;
84 lp_resource_id NUMBER;
85 lp_group_id NUMBER;
86 lp_role_code VARCHAR2(300);
87 lp_mgr_resource_id NUMBER;
88 lp_mgr_group_id NUMBER;
89 lp_mgr_role_code VARCHAR2(300);
90 lp_resource_type VARCHAR2(300);
91 t_resource_id NUMBER;
92 t_resource_type VARCHAR2(19);
93
94 lp_named_account_id NUMBER;
95
96 l_role_code VARCHAR2(300);
97 l_terr_group_account_id NUMBER;
98 l_directs_on_account NUMBER := 0;
99 l_assign_flag VARCHAR2(1);
100 l_resource_id_is_leaf VARCHAR2(1);
101 l_assigned_rsc_exists NUMBER := 0; -- 0 if no assigned rsc exists, 1 otherwise
102
103 l_find_subs VARCHAR2(1); -- are we processing subsidiaries?
104 l_master_pty_last NUMBER; -- last index of the master parties
105 l_sub_pty_index NUMBER; -- index where the subsidiaries will be added
106 l_acct_rsc_exist_count NUMBER; -- verify if existing rsc/group/role exists
107
108 l_change_id NUMBER;
109 l_user number;
110 l_login_id number;
111
112
113 new_seq_acct_rsc_id NUMBER;
114 new_seq_acct_rsc_dn_id NUMBER;
115 new_seq_RESOURCE_ACCT_SUMM_ID NUMBER;
116
117
118 -- LIST OF ALL GROUPS A GIVEN RESOURCE OWNS IN THE CONTEXT OF A PARENT GRUOP
119 cursor c_rsc_owned_grps(cl_parent_resource_id number, cl_group_id number) is
120 SELECT mgr.resource_id, mgr.group_id
121 FROM jtf_rs_rep_managers mgr,
122 jtf_rs_groups_denorm gd
123 WHERE mgr.hierarchy_type = 'MGR_TO_MGR'
124 AND mgr.resource_id = mgr.parent_resource_id
125 AND trunc(sysdate) BETWEEN mgr.start_date_active
126 AND NVL(mgr.end_date_active,trunc(sysdate))
127 AND mgr.group_id = gd.group_id
128 AND gd.parent_group_id = cl_group_id
129 AND mgr.resource_id = cl_parent_resource_id
130 AND rownum < 2;
131
132
133 -- LIST OF ALL DIRECTS TO REMOVE WHEN REMOVING A MANAGING RESOURCE
134 -- FROM A NAMED ACCOUNT IN THE CONTEXT OF A GROUP
135 cursor c_rsc_directs(cl_parent_resource_id number, cl_group_id number) is
136 SELECT DISTINCT RESOURCE_ID
137 FROM JTF_RS_REP_MANAGERS
138 WHERE group_id = cl_group_id
139 and resource_id <> cl_parent_resource_id
140 and parent_resource_id = cl_parent_resource_id;
141
142
143 -- ALL SUBSIDIARIES OF cl_party_id that is owned by lp_user_resource_id, p_terr_group_id
144 -- QUERY MODIFIED FOR TERR_GROUP_ACCOUNT_ID IN/OUT
145 cursor c_subsidiaries(cl_terr_group_account_id number) is
146 select distinct gao.terr_group_account_id
147 from hz_relationships hzr,
148 jtf_tty_named_accts nai,
149 jtf_tty_terr_grp_accts gai,
150 jtf_tty_named_accts nao,
151 jtf_tty_terr_grp_accts gao
152 where gao.named_account_id = nao.named_account_id
153 and nao.party_id = hzr.object_id -- these are the subsidiary parties
154 and hzr.subject_table_name = 'HZ_PARTIES'
155 and hzr.object_table_name = 'HZ_PARTIES'
156 and hzr.relationship_code IN ( 'GLOBAL_ULTIMATE_OF', 'HEADQUARTERS_OF', 'DOMESTIC_ULTIMATE_OF', 'PARENT_OF' )
157 and hzr.status = 'A'
158 and sysdate between hzr.start_date and nvl( hzr.end_date, sysdate)
159 and hzr.subject_id = nai.party_id -- this is the parent party
160 and nai.named_account_id = gai.named_account_id
161 and gai.terr_group_account_id = cl_terr_group_account_id
162 -- subsidiaries that are owned by user
163 and exists( select 'Y'
164 from jtf_tty_named_acct_rsc narsc ,
165 jtf_tty_my_resources_v repdn
166 -- jtf_tty_named_accts na,
167 -- jtf_tty_terr_grp_accts ga
168 where narsc.terr_group_account_id = gao.terr_group_account_id
169 -- and ga.named_account_id = na.named_account_id
170 and narsc.resource_id = repdn.resource_id
171 and narsc.rsc_group_id = repdn.group_id
172 and repdn.current_user_id = l_user_id );
173
174
175
176 /* this cursor return the managers details for the logged in person group with respect to the
177 effected resource */
178
179 cursor c_groups_manager(cl_current_user_id number, cl_eff_resource_id number
180 ) is
181 Select mem.resource_id, mem.group_id, rol.role_code
182 from jtf_rs_group_members mem,
183 jtf_rs_role_relations rlt,
184 jtf_rs_roles_b rol,
185 jtf_rs_group_members cgrpmem,
186 jtf_rs_resource_extns crsc,
187 jtf_rs_groups_denorm grpden
188 where crsc.user_id = cl_current_user_id
189 and crsc.resource_id = cgrpmem.resource_id
190 and cgrpmem.delete_flag = 'N'
191 and cgrpmem.group_id = mem.group_id
192 and rlt.role_resource_type = 'RS_GROUP_MEMBER'
193 and rlt.delete_flag = 'N'
194 and sysdate >= rlt.start_date_active
195 and ( rlt.end_date_active is null
196 or
197 sysdate <= rlt.end_date_active
198 )
199 and rlt.role_id = rol.role_id
200 and rol.manager_flag = 'Y'
201 and rlt.role_resource_id = mem.group_member_id
202 and mem.delete_flag = 'N'
203 and mem.group_id = grpden.parent_group_id
204 and grpden.group_id IN ( select grv1.group_id
205 from jtf_rs_group_members grv1
206 where grv1.resource_id = cl_eff_resource_id );
207
208
209
210 BEGIN
211
212 /***********************************************************
213 **** PHASE 0: API INTERNAL OPTIMIZATIONS
214 **** Populate G_AFFECT_PARTY_TBL with subsidiaries
215 **** if ASSIGN_SUBSIDIARIES has been selected for any resource
216 ************************************************************/
217
218
219 l_user := fnd_global.USER_ID;
220 l_login_id := fnd_global.LOGIN_ID;
221
222 -- populate the resource_type record type for all salesreps
223 -- bug 2726632
224 IF G_ADD_SALESREP_TBL is not null THEN
225 IF G_ADD_SALESREP_TBL.last > 0 THEN
226 FOR d in G_ADD_SALESREP_TBL.first..G_ADD_SALESREP_TBL.last LOOP
227 -- t_resource_id := G_ADD_SALESREP_TBL(d).resource_id;
228 -- select resource_type into t_resource_type
229 -- from jtf_rs_resources_vl
230 -- where resource_id = t_resource_id;
231 -- G_ADD_SALESREP_TBL(d).resource_type := t_resource_type;
232 G_ADD_SALESREP_TBL(d).resource_type := 'RS_EMPLOYEE';
233
234 OPEN c_groups_manager(l_user_id, G_ADD_SALESREP_TBL(d).resource_id);
235 FETCH c_groups_manager INTO lp_mgr_resource_id, lp_mgr_group_id, lp_mgr_role_code;
236 CLOSE c_groups_manager;
237
238 G_ADD_SALESREP_TBL(d).mgr_resource_id := lp_mgr_resource_id;
239 G_ADD_SALESREP_TBL(d).mgr_group_id := lp_mgr_group_id;
240 G_ADD_SALESREP_TBL(d).mgr_role_code := lp_mgr_role_code;
241
242 END LOOP;
243 END IF;
244 END IF;
245
246 IF G_REM_SALESREP_TBL is not null THEN
247 IF G_REM_SALESREP_TBL.last > 0 THEN
248 FOR d in G_REM_SALESREP_TBL.first..G_REM_SALESREP_TBL.last LOOP
249 -- t_resource_id := G_REM_SALESREP_TBL(d).resource_id;
250 -- select resource_type into t_resource_type
251 -- from jtf_rs_resources_vl
252 -- where resource_id = t_resource_id;
253 -- G_REM_SALESREP_TBL(d).resource_type := t_resource_type;
254 G_REM_SALESREP_TBL(d).resource_type := 'RS_EMPLOYEE';
255
256 OPEN c_groups_manager(l_user_id, G_REM_SALESREP_TBL(d).resource_id);
257 FETCH c_groups_manager INTO lp_mgr_resource_id, lp_mgr_group_id, lp_mgr_role_code;
258 CLOSE c_groups_manager;
259
260 G_REM_SALESREP_TBL(d).mgr_resource_id := lp_mgr_resource_id;
261 G_REM_SALESREP_TBL(d).mgr_group_id := lp_mgr_group_id;
262 G_REM_SALESREP_TBL(d).mgr_role_code := lp_mgr_role_code;
263
264 END LOOP;
265 END IF;
266 END IF;
267
268 -- tag all incoming accounts as non-subsidiary record
269 IF (G_AFFECT_PARTY_TBL is not null) THEN
270 IF (G_AFFECT_PARTY_TBL.last > 0) THEN
271 -- TAG the original inputs for affected parties
272 FOR n in G_AFFECT_PARTY_TBL.first.. G_AFFECT_PARTY_TBL.last LOOP
273 G_AFFECT_PARTY_TBL(n).attribute1 := 'N';
274 END LOOP;
275 END IF;
276 END IF;
277
278 -- do we need to subsidiary processing?
279 l_find_subs := 'N';
280 IF ((G_AFFECT_PARTY_TBL is not null) and (G_REM_SALESREP_TBL is not null)) THEN
281 IF ((G_AFFECT_PARTY_TBL.last > 0) and (G_REM_SALESREP_TBL.last > 0)) THEN
282 FOR m in G_REM_SALESREP_TBL.first.. G_REM_SALESREP_TBL.last LOOP
283 IF G_REM_SALESREP_TBL(m).attribute1 = 'Y' THEN
284 l_find_subs := 'Y';
285 EXIT;
286 END IF;
287 END LOOP;
288 END IF;
289 END IF;
290 IF ((G_AFFECT_PARTY_TBL is not null) and (G_ADD_SALESREP_TBL is not null)) THEN
291 IF ((G_AFFECT_PARTY_TBL.last > 0) and (G_ADD_SALESREP_TBL.last > 0)) THEN
292 FOR m in G_ADD_SALESREP_TBL.first.. G_ADD_SALESREP_TBL.last LOOP
293 IF G_ADD_SALESREP_TBL(m).attribute1 = 'Y' THEN
294 l_find_subs := 'Y';
295 EXIT;
296 END IF;
297 END LOOP;
298 END IF;
299 END IF;
300
301 -- subsidiary processing: add subsidiaries to G_AFFECT_PARTY_TBL
302 IF l_find_subs = 'Y' THEN
303 --dbms_output.put_line('l_find_subs = Y');
304 -- we start on next index value.
305 l_master_pty_last := G_AFFECT_PARTY_TBL.last;
306 l_sub_pty_index := G_AFFECT_PARTY_TBL.last + 1;
307
308 FOR p in G_AFFECT_PARTY_TBL.first.. l_master_pty_last LOOP
309 FOR c_sub in c_subsidiaries(G_AFFECT_PARTY_TBL(p).terr_group_account_id) LOOP
310 G_AFFECT_PARTY_TBL.extend;
311
312 G_AFFECT_PARTY_TBL(l_sub_pty_index).terr_group_account_id := c_sub.terr_group_account_id;
313 G_AFFECT_PARTY_TBL(l_sub_pty_index).attribute1 := 'Y';
314 l_sub_pty_index := l_sub_pty_index + 1;
315
316 END LOOP;
317 END LOOP;
318
319 END IF; -- l_find_subs = 'Y' ?
320
321 /***********************************************************
322 **** PHASE I: DATAMODEL MODIFICATIONS
323 **** Changes made only to JTF_TTY_NAMED_ACCT_RSC
324 **** JTF_TTY_ACCT_RSC_DN
325 ************************************************************/
326 --dbms_output.put_line('PHASE I ');
327
328 ---------------------------------------------
329 -- ADDING RESOURCES TO SALES TEAM
330 ---------------------------------------------
331 IF ((G_AFFECT_PARTY_TBL is not null) and (G_ADD_SALESREP_TBL is not null)) THEN
332 IF ((G_AFFECT_PARTY_TBL.last > 0) and (G_ADD_SALESREP_TBL.last > 0)) THEN
333
334 FOR j in G_AFFECT_PARTY_TBL.first.. G_AFFECT_PARTY_TBL.last LOOP
335 --dbms_output.put_line('Adding Resources to: G_AFFECT_PARTY_TBL =' || j || G_AFFECT_PARTY_TBL(j).party_id);
336
337 -- each named account exists in context of a territory group for resource
338 l_terr_group_account_id := G_AFFECT_PARTY_TBL(j).terr_group_account_id;
339
340 FOR i in G_ADD_SALESREP_TBL.first.. G_ADD_SALESREP_TBL.last LOOP
341 --dbms_output.put_line('Resource being Added: G_ADD_SALESREP_TBL =' || i || G_ADD_SALESREP_TBL(i).resource_id);
342
343 IF ((G_ADD_SALESREP_TBL(i).attribute1 = 'Y') OR
344 (G_ADD_SALESREP_TBL(i).attribute1 = 'N' and G_AFFECT_PARTY_TBL(j).attribute1 = 'N')
345 )
346 THEN
347
348 lp_resource_id := G_ADD_SALESREP_TBL(i).resource_id;
349 lp_group_id := G_ADD_SALESREP_TBL(i).group_id;
350 lp_role_code := G_ADD_SALESREP_TBL(i).role_code;
351 lp_resource_type := G_ADD_SALESREP_TBL(i).resource_type;
352 lp_mgr_resource_id := G_ADD_SALESREP_TBL(i).mgr_resource_id;
353
354 -- method of processing depends on whether resource is the user. Bug: 2816957
355 if lp_mgr_resource_id = lp_resource_id then
356 -- DOES RECORD PROCESSED EXIST? Bug: 2729383
357 select count(*) into l_acct_rsc_exist_count
358 from (
359 select account_resource_id
360 from jtf_tty_named_acct_rsc
361 where resource_id = lp_resource_id
362 and rsc_group_id = lp_group_id
363 and rsc_role_code = lp_role_code
364 and terr_group_account_id = l_terr_group_account_id
365 and assigned_flag = 'Y' -- still need a Y assign flag on NA/RSC to abort addition.
366 and rownum < 2
367 );
368 else
369 -- DOES RECORD PROCESSED EXIST? Bug: 2729383
370 select count(*) into l_acct_rsc_exist_count
371 from (
372 select account_resource_id
373 from jtf_tty_named_acct_rsc
374 where resource_id = lp_resource_id
375 and rsc_group_id = lp_group_id
376 and rsc_role_code = lp_role_code
377 and terr_group_account_id = l_terr_group_account_id
378 -- and assigned_flag = 'Y' bug 2803830
379 and rownum < 2
380 );
381
382 end if;
383
384
385 -- DOES RECORD TO BE PROCESSED EXIST?
386 IF l_acct_rsc_exist_count = 0 THEN
387
388 --is user resource_id a leaf node in hierarchy?
389 l_resource_id_is_leaf := 'Y';
390 FOR crd IN c_rsc_owned_grps(lp_resource_id, lp_group_id) LOOP
391 l_resource_id_is_leaf := 'N';
392 EXIT;
393 END LOOP; -- c_rsc_directs
394
395 -- set l_assign_flag for account
396 IF ( (lp_resource_id = lp_mgr_resource_id)
397 OR (l_resource_id_is_leaf = 'Y'))
398 THEN l_assign_flag := 'Y';
399 ELSE l_assign_flag := 'N';
400 END IF;
401
402 -- test if record already exists for this rsc/grp/role with assign Y
403
404
405 -- insert into jtf_tty_named_acct_rsc
406 select jtf_tty_named_acct_rsc_s.nextval into new_seq_acct_rsc_id
407 from dual;
408
409 --dbms_output.put_line('inserting to jtf_tty_named_acct_rsc ');
410 --dbms_output.put_line(' ' || ' //new_seq_acct_rsc_id=' || new_seq_acct_rsc_id ||
411 --' //l_terr_group_account_id=' || l_terr_group_account_id ||' //lp_resource_id=' ||
412 -- lp_resource_id ||' //lp_group_id=' || lp_group_id
413 --|| l_assign_flag ||' //lp_resource_type=' || lp_resource_type );
414
415 -- assigned flag Y because user is assigning this individual, may be himself.
416 insert into jtf_tty_named_acct_rsc (
417 account_resource_id,
418 object_version_number,
419 terr_group_account_id,
420 resource_id,
421 rsc_group_id,
422 rsc_role_code,
423 assigned_flag,
424 rsc_resource_type,
425 created_by,
426 creation_date,
427 last_updated_by,
428 last_update_date
429 )
430 VALUES (
431 new_seq_acct_rsc_id, --account_resource_id,
432 2, --object_version_number
433 l_terr_group_account_id, --terr_group_account_id
434 lp_resource_id, --resource_id,
435 lp_group_id, --rsc_group_id,
436 lp_role_code, --rsc_role_code,
437 l_assign_flag, --assigned_flag,
438 lp_resource_type, --rsc_resource_type
439 1, --created_by
440 sysdate, --creation_date
441 1, --last_updated_by
442 sysdate --last_update_date
443 );
444
445 -- if user exists as an account resource w/assign_flag = N then
446 -- delete user for this account from JTF_TTY_NAMED_ACCT_RSC
447 --dbms_output.put_line('deleting from JTF_TTY_NAMED_ACCT_RSC:' || '//lp_group_id:'
448 --|| lp_group_id ||'//p_role_code:' ||lp_role_code ||'//l_terr_group_account_id:'
449 --||l_terr_group_account_id || '//lp_user_resource_id:' || lp_user_resource_id);
450 -- Bug: 2726632
451 -- Bug: 2732533
452
453 /* JRADHAKR: Inserting values to jtf_tty_named_acct_changes table for GTP
454 to do an incremental and Total Mode */
455
456 select jtf_tty_named_acct_changes_s.nextval
457 into l_change_id
458 from sys.dual;
459
460 insert into jtf_tty_named_acct_changes
461 ( NAMED_ACCT_CHANGE_ID
462 , OBJECT_VERSION_NUMBER
463 , OBJECT_TYPE
464 , OBJECT_ID
465 , CHANGE_TYPE
466 , FROM_WHERE
467 , CREATED_BY
468 , CREATION_DATE
469 , LAST_UPDATED_BY
470 , LAST_UPDATE_DATE
471 , LAST_UPDATE_LOGIN
472 )
473 VALUES (
474 l_change_id
475 , 1
476 , 'TGA'
477 , l_terr_group_account_id
478 , 'UPDATE'
479 , 'UPDATE SALES TEAM'
480 , l_user
481 , sysdate
482 , l_user
483 , sysdate
484 , l_login_id
485 );
486
487 delete from jtf_tty_named_acct_rsc
488 where 1=1
489 --and rsc_group_id = lp_group_id
490 --and rsc_role_code = lp_role_code
491 and terr_group_account_id = l_terr_group_account_id
492 and resource_id = lp_mgr_resource_id
493 and assigned_flag = 'N';
494
495 END IF; -- DOES RECORD TO BE PROCESSED EXIST?
496
497 END IF; -- process this? (subsidiary logic)
498
499 END LOOP; -- G_ADD_SALESREP_TBL
500
501 END LOOP; -- LOOP G_AFFECT_PARTY_TBL
502
503 END IF; --((G_AFFECT_PARTY_TBL.last > 0) and (G_ADD_SALESREP_TBL.last > 0))
504 END IF; --((G_AFFECT_PARTY_TBL is not null) and (G_ADD_SALESREP_TBL is not null))
505
506
507 ---------------------------------------------
508 -- REMOVING RESOURCES IN SALES TEAM
509 ---------------------------------------------
510 -- Delete resource being removed from account (ALONG WITH ALL HIS DIRECTS)
511 IF ((G_AFFECT_PARTY_TBL is not null) and (G_REM_SALESREP_TBL is not null)) THEN
512 IF ((G_AFFECT_PARTY_TBL.last > 0) and (G_REM_SALESREP_TBL.last > 0)) THEN
513
514 FOR j in G_AFFECT_PARTY_TBL.first.. G_AFFECT_PARTY_TBL.last LOOP
515 --dbms_output.put_line('G_AFFECT_PARTY_TBL ' || j || G_AFFECT_PARTY_TBL(j).party_id);
516
517 -- each named account exists in context of a territory group for resource
518 l_terr_group_account_id := G_AFFECT_PARTY_TBL(j).terr_group_account_id;
519
520 /* JRADHAKR: Inserting values to jtf_tty_named_acct_changes table for GTP
521 to do an incremental and Total Mode */
522
523 select jtf_tty_named_acct_changes_s.nextval
524 into l_change_id
525 from sys.dual;
526
527 insert into jtf_tty_named_acct_changes
528 ( NAMED_ACCT_CHANGE_ID
529 , OBJECT_VERSION_NUMBER
530 , OBJECT_TYPE
531 , OBJECT_ID
532 , CHANGE_TYPE
533 , FROM_WHERE
534 , CREATED_BY
535 , CREATION_DATE
536 , LAST_UPDATED_BY
537 , LAST_UPDATE_DATE
538 , LAST_UPDATE_LOGIN
539 )
540 VALUES (
541 l_change_id
542 , 1
543 , 'TGA'
544 , l_terr_group_account_id
545 , 'UPDATE'
546 , 'UPDATE SALES TEAM'
547 , l_user
548 , sysdate
549 , l_user
550 , sysdate
551 , l_login_id
552 );
553
554 FOR i in G_REM_SALESREP_TBL.first.. G_REM_SALESREP_TBL.last LOOP
555 --dbms_output.put_line('G_REM_SALESREP_TBL ' || i || G_REM_SALESREP_TBL(i).resource_id);
556
557 IF ((G_REM_SALESREP_TBL(i).attribute1 = 'Y') OR
558 (G_REM_SALESREP_TBL(i).attribute1 = 'N' and G_AFFECT_PARTY_TBL(j).attribute1 = 'N')
559 )
560 THEN
561 lp_resource_id := G_REM_SALESREP_TBL(i).resource_id;
562 lp_group_id := G_REM_SALESREP_TBL(i).group_id;
563 lp_role_code := G_REM_SALESREP_TBL(i).role_code;
564 lp_resource_type := G_REM_SALESREP_TBL(i).resource_type;
565 lp_mgr_resource_id := G_REM_SALESREP_TBL(i).mgr_resource_id;
566
567 -- delete resource to be removed from sales team
568 --dbms_output.put_line('DELETING FROM jtf_tty_named_acct_rsc ');
569 --dbms_output.put_line(' ' ||
570 -- ' //l_terr_group_account_id=' || l_terr_group_account_id ||
571 -- ' //lp_resource_id=' || lp_resource_id ||
572 -- ' //lp_group_id=' || lp_group_id ||
573 -- ' //lp_role_code=' || lp_role_code || '//');
574
575 delete from jtf_tty_named_acct_rsc
576 where rsc_group_id = lp_group_id
577 and rsc_role_code = lp_role_code
578 and terr_group_account_id = l_terr_group_account_id
579 and resource_id = lp_resource_id;
580
581
582
583 -- if no one in user's hierarhy is assigned to this account
584 -- after this delete, add user to this account
585
586 -- if no one in user's hierarhy is assigned to this account
587 -- after this delete, set assigned_to_direct_flag to 'N' for user's NA
588
589 -- ACHANDA : bug 3265188 : change the IN clause to EXISTS to improve performance
590
591
592 select count(*) INTO l_directs_on_account
593 from jtf_tty_named_acct_rsc ar
594 where ar.terr_group_account_id = l_terr_group_account_id
595 and exists (
596 select 1
597 from jtf_tty_my_resources_v grv
598 , jtf_rs_groups_denorm grpd
599 WHERE ar.resource_id = grv.resource_id
600 and grpd.parent_group_id = grv.parent_group_id
601 and exists (
602 select 1
603 from jtf_rs_group_members grv1
604 where grpd.group_id = grv1.group_id
605 and grv1.resource_id = lp_resource_id )
606 and grv.CURRENT_USER_ID = l_user_id )
607 and rownum < 2;
608
609 --dbms_output.put_line('l_directs_on_account = ' || l_directs_on_account);
610
611 IF l_directs_on_account = 0 THEN
612 select jtf_tty_named_acct_rsc_s.nextval into new_seq_acct_rsc_id
613 from dual;
614
615 lp_mgr_group_id := G_REM_SALESREP_TBL(i).mgr_group_id;
616 lp_mgr_role_code := G_REM_SALESREP_TBL(i).mgr_role_code;
617
618 -- assigned flag N because user did not assign himself.
619 -- It is an auto assign to user when none of his directs are assigned.
620 insert into jtf_tty_named_acct_rsc (
621 account_resource_id,
622 object_version_number,
623 terr_group_account_id,
624 resource_id,
625 rsc_group_id,
626 rsc_role_code,
627 assigned_flag,
628 rsc_resource_type,
629 created_by,
630 creation_date,
631 last_updated_by,
632 last_update_date
633 )
634 VALUES (
635 new_seq_acct_rsc_id, --account_resource_id,
636 2, --object_version_number
637 l_terr_group_account_id, --terr_group_account_id
638 lp_mgr_resource_id, --resource_id,
639 lp_mgr_group_id, --rsc_group_id,
640 lp_mgr_role_code, --rsc_role_code,
641 'N', --assigned_flag,
642 lp_user_resource_type, --rsc_resource_type
643 1, --created_by
644 sysdate, --creation_date
645 1, --last_updated_by
646 sysdate --last_update_date
647 );
648
649
650
651
652 END IF; --l_directs_on_account = 0?
653
654 -- LOOP THROUGH ALL SUBORDINATES OF THIS RESOURCE_ID
655 -- remove all directs of this rem_resource_id from account
656 -- this cursor does not include lp_resource_id itself.
657
658 --bug 2828011: do not remove directs if user removing self.
659 IF lp_mgr_resource_id <> lp_resource_id THEN
660
661 FOR crd IN c_rsc_directs(lp_resource_id, lp_group_id) LOOP
662 -- delete subordinates from JTF_TTY_NAMED_ACCT_RSC
663 DELETE FROM JTF_TTY_NAMED_ACCT_RSC
664 WHERE rsc_role_code = lp_role_code
665 AND terr_group_account_id = l_terr_group_account_id
666 AND resource_id = crd.resource_id;
667
668
669 END LOOP; -- c_rsc_directs
670
671 END IF; -- lp_user_resource_id <> lp_resource_id ?
672
673 END IF; -- process this? (subsidiary logic)
674
675 END LOOP; -- G_REM_SALESREP_TBL
676
677 END LOOP; -- G_AFFECT_PARTY_TBL
678
679 END IF; -- ((G_AFFECT_PARTY_TBL.last > 0) and (G_REM_SALESREP_TBL.last > 0))
680 END IF; -- ((G_AFFECT_PARTY_TBL is not null) and (G_REM_SALESREP_TBL is not null))
681
682
683
684
685 /***********************************************************
686 **** PHASE II: PROCESS OTHER TABLES
687 **** Changes made only to JTF_TTY_RSC_ACCT_SUMM
688 **** JTF_TTY_TERR_GRP_ACCTS
689 ************************************************************/
690 --dbms_output.put_line('PHASE II ');
691
692
693 ---------------------------------------------
694 -- PROCESS JTF_TTY_RSC_ACCT_SUMM
695 ---------------------------------------------
696 IF (G_AFFECT_PARTY_TBL is not null) THEN
697 IF (G_AFFECT_PARTY_TBL.last > 0) THEN
698
699
700 ---------------------------------------------
701 -- PROCESS JTF_TTY_TERR_GRP_ACCTS
702 ---------------------------------------------
703 FOR i in G_AFFECT_PARTY_TBL.first.. G_AFFECT_PARTY_TBL.last LOOP
704 ----dbms_output.put_line('G_AFFECT_PARTY_TBL ' || i || G_AFFECT_PARTY_TBL(j).party_id);
705
706 -- each named account exists in context of a territory group for resource
707 l_terr_group_account_id := G_AFFECT_PARTY_TBL(i).terr_group_account_id;
708
709 -- set l_assigned_rsc_exists:0 if no assigned rsc exists, 1 otherwise
710 select count(*) into l_assigned_rsc_exists
711 from jtf_tty_named_acct_rsc
712 where terr_group_account_id = l_terr_group_account_id
713 and assigned_flag = 'Y'
714 and rownum < 2;
715
716 If l_assigned_rsc_exists = 0 then
717 l_assign_flag := 'N';
718 else
719 l_assign_flag := 'Y';
720 end if;
721
722 UPDATE JTF_TTY_TERR_GRP_ACCTS
723 SET DN_JNR_ASSIGNED_FLAG = l_assign_flag
724 WHERE TERR_GROUP_ACCOUNT_ID = l_terr_group_account_id;
725
726 END LOOP; -- G_AFFECT_PARTY_TBL
727
728 END IF; -- (G_AFFECT_PARTY_TBL.last > 0)
729 END IF; -- (G_AFFECT_PARTY_TBL is not null)
730
731 /* Start update jtf_terr_rsc_all */
732
733 BEGIN
734
735 FOR i IN G_AFFECT_PARTY_TBL.first.. G_AFFECT_PARTY_TBL.last LOOP
736
737 SELECT terr_group_id INTO l_terr_group_id
738 FROM jtf_tty_terr_grp_accts
739 WHERE terr_group_account_id = G_AFFECT_PARTY_TBL(i).terr_group_account_id;
740
741 Jtf_Tty_Gen_Terr_Pvt.update_terr_rscs_for_na
742 (G_AFFECT_PARTY_TBL(i).terr_group_account_id,
743 l_terr_group_id);
744
745
746 END LOOP;
747
748 EXCEPTION WHEN OTHERS
749 THEN NULL;
750 END;
751
752 END UPDATE_SALES_TEAM;
753
754 END JTF_TTY_NACCT_SALES_PUB;
[Home] [Help]