[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_ALIGN_WEBADI_INT_PKG
Source
1 PACKAGE BODY JTF_TTY_ALIGN_WEBADI_INT_PKG AS
2 /* $Header: jtftyawb.pls 120.0 2005/06/02 18:22:02 appldev ship $ */
3 -- ===========================================================================+
4 -- | Copyright (c) 1999 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +===========================================================================
8 -- Start of Comments
9 -- ---------------------------------------------------
10 -- PURPOSE
11 --
12 -- This package is used to return a list of column in order of selectivity.
13 -- And create indices on columns in order of input
14 --
15 --
16 -- Procedures:
17 -- (see below for specification)
18 --
19 -- NOTES
20 -- This package is publicly available for use
21 --
22 -- HISTORY
23 -- 05/02/2002 SHLI Created
24 -- 10/10/2003 SP Modified for bug 3162073
25 --
26 -- End of Comments
27 --
28 -- *******************************************************
29 -- Start of Comments
30 -- *******************************************************
31
32
33
34 procedure POPULATE_INTERFACE( p_userid in varchar2,
35 p_align_id in varchar2,
36 p_init_flag in varchar2,
37 x_seq out NOCOPY varchar2) IS
38
39
40 RESOURCE_NAME VARRAY_TYPE:=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
41 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
42 GROUP_NAME VARRAY_TYPE:=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
43 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
44 ROLE_NAME VARRAY_TYPE:=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
45 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
46 --RESOURCE_ID NARRAY_TYPE:=NARRAY_TYPE(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
47 --L_GROUP_ID NARRAY_TYPE:=NARRAY_TYPE(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
48 --ROLE_CODE VARRAY_TYPE:=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
49 -- null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
50 COL_SOLT -- NARRAY_TYPE:=NARRAY_TYPE(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
51 VARRAY_TYPE:=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
52 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
53 --COL_RSC NARRAY_TYPE:=NARRAY_TYPE(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
54 COL_USED NARRAY_TYPE:=NARRAY_TYPE(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
55 salesMgr NUMBER;
56 SEQ NUMBER;
57 ID NUMBER;
58 l_dnb_annual_rev VARCHAR2(30);
59 l_dnb_num_of_emp VARCHAR2(30);
60 l_prior_won VARCHAR2(30);
61 --NAMED_ACCOUNT VARCHAR2(360);
62 L_PARTY_ID VARCHAR2(30);
63 SITE_TYPE VARCHAR2(80);
64 l_var1 VARCHAR2(200);
65 l_var2 VARCHAR2(200);
66 i NUMBER;
67 j NUMBER;
68 k NUMBER;
69 --l_na_sales VARCHAR2(6000);
70 --l_al_sales VARCHAR2(6000);
71 l_getAlignNamedAccount VARCHAR2(6000);
72 --l_na_stats VARCHAR2(6000);
73 --l_al_stats VARCHAR2(6000);
74 l_align_id VARCHAR2(30);
75
76 --l_rsc_4_na_owned_by_user_dir VARCHAR2(6000);
77 --l_rsc_4_na_owned_by_indirect VARCHAR2(6000);
78 --foundRsc BOOLEAN := FALSE;
79
80 --TYPE RefCur IS REF CURSOR; -- define weak REF CURSOR type
81 --nastat RefCur; -- declare cursor variable
82 --sales RefCur; -- declare cursor variable
83
84 /*
85 cursor getStatisticByNA (userid in number) IS
86 SELECT role_code, MAX(num) num
87 FROM (
88 SELECT mydir.role_code role_code,
89 COUNT(role_code) num
90 FROM
91 jtf_tty_my_directs_gt mydir,
92 (
93 select terr_grp_acct_id
94 from jtf_tty_webadi_interface
95 where user_id = userid
96 ) tgaid_list
97 WHERE
98 mydir.current_user_id = userid
99 and mydir.resource_id in (
100 select -- NO_MERGE
101 repmgr.parent_resource_id
102 from jtf_rs_rep_managers repmgr,
103 jtf_tty_named_acct_rsc narsc,
104 jtf_tty_terr_grp_accts ga
105 where narsc.resource_id = repmgr.resource_id
106 and narsc.rsc_group_id = repmgr.group_id
107 AND narsc.terr_group_account_id = ga.terr_group_account_id
108 AND ga.terr_group_account_id = tgaid_list.terr_grp_acct_id
109 )
110
111 GROUP BY tgaid_list.terr_grp_acct_id, role_code
112 ORDER BY MAX(role_name)
113 )
114 GROUP BY role_code;
115 */
116
117
118 cursor getStatisticByNA (userid in number) IS
119 SELECT role_code, MAX(num) num
120 FROM (
121 SELECT mydir.role_code role_code, COUNT(mydir.role_code) num
122 FROM ( select distinct
123 tmp.terr_grp_acct_id,
124 repmgr.parent_resource_id resource_id,
125 grpmem.group_id group_id,
126 rol.role_code role_code
127 from jtf_tty_webadi_interface tmp,
128 jtf_tty_named_acct_rsc narsc,
129 jtf_rs_rep_managers repmgr,
130 jtf_rs_role_relations rlt,
131 jtf_rs_roles_b rol,
132 jtf_rs_group_members grpmem,
133 jtf_tty_my_directs_gt dir
134 where narsc.resource_id = repmgr.resource_id
135 and narsc.rsc_group_id = repmgr.group_id
136 AND narsc.terr_group_account_id = tmp.terr_grp_acct_id
137 AND repmgr.par_role_relate_id = rlt.role_relate_id
138 AND SYSDATE BETWEEN repmgr.start_date_active AND NVL(repmgr.end_date_active, SYSDATE+1)
139 AND rlt.role_id = rol.role_id
140 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
141 AND rlt.delete_flag = 'N'
142 AND SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active, SYSDATE+1)
143 AND rlt.role_resource_id = grpmem.group_member_id
144 AND grpmem.delete_flag = 'N'
145 AND tmp.user_id = userid
146 AND dir.current_user_id = userid
147 AND dir.dir_user_id <> userid
148 AND dir.resource_id = repmgr.parent_resource_id
149 AND dir.group_id = grpmem.group_id
150 AND dir.role_code = rol.role_code
151 AND tmp.user_id = dir.current_user_id
152 UNION ALL
153 select
154 tmp.terr_grp_acct_id,
155 narsc.resource_id resource_id,
156 narsc.rsc_group_id group_id,
157 narsc.rsc_role_code role_code
158 from jtf_tty_webadi_interface tmp,
159 jtf_tty_named_acct_rsc narsc,
160 jtf_tty_my_directs_gt dir
161 where narsc.terr_group_account_id = tmp.terr_grp_acct_id
162 AND dir.current_user_id = userid
163 AND dir.dir_user_id = userid
164 AND dir.resource_id = narsc.resource_id
165 AND dir.group_id = narsc.rsc_group_id
166 AND dir.role_code = narsc.rsc_role_code
167 AND tmp.user_id = userid
168 AND tmp.user_id = dir.current_user_id
169 ) mydir
170 GROUP BY mydir.terr_grp_acct_id, mydir.role_code
171 )
172 GROUP BY role_code;
173
174
175
176 cursor getStatisticByAlign (userid in number, p_align_id in number) IS
177 select role_code, MAX(num) num
178 from (
179 select ap.rsc_role_code role_code, count(ap.rsc_role_code) num
180 from JTF_TTY_ALIGN_ACCTS aa,
181 JTF_TTY_PTERR_ACCTS pa,
182 JTF_TTY_ALIGN_PTERR ap
183 -- jtf_rs_roles_vl rol
184 where
185 aa.alignment_id = p_align_id
186 and aa.align_acct_id = pa.align_acct_id
187 and pa.align_proposed_terr_id= ap.align_proposed_terr_id
188 and ap.resource_type = 'RS_EMPLOYEE'
189 -- and rol.role_code = ap.rsc_role_code
190 group by aa.terr_group_account_id, ap.rsc_role_code
191 -- ORDER BY MAX(rol.role_name)
192 )
193 group by role_code ;
194
195
196 cursor getNAFromInterface IS
197 SELECT jtf_tty_webadi_int_id, terr_grp_acct_id
198 FROM jtf_tty_webadi_interface
199 where user_id=p_userid;
200
201
202 cursor na_sales(userid in number, tgaid in number) IS
203 -- No duplicate salesperson caused by rollup
204 -- each dir in the view should appear once
205 select /* search directs */
206 mydir.resource_name, --mydir.resource_id,
207 mydir.group_name, --mydir.group_id,
208 mydir.role_name, mydir.role_code
209 FROM jtf_tty_my_directs_gt /*jtf_tty_my_directs_v*/ mydir
210 WHERE mydir.current_user_id = userid
211 and mydir.dir_user_id <> userid
212 and ( mydir.resource_id, mydir.group_id, mydir.role_code) in
213 ( select /*+ NO_MERGE */
214 repmgr.parent_resource_id,
215 grpmem.group_id,
216 rol.role_code
217 from jtf_tty_named_acct_rsc narsc,
218 jtf_rs_rep_managers repmgr,
219 jtf_rs_role_relations rlt,
220 jtf_rs_roles_b rol,
221 jtf_rs_group_members grpmem
222 where narsc.resource_id = repmgr.resource_id
223 and narsc.rsc_group_id = repmgr.group_id
224 AND narsc.terr_group_account_id = tgaid
225 AND repmgr.par_role_relate_id = rlt.role_relate_id
226 AND SYSDATE BETWEEN repmgr.start_date_active AND NVL(repmgr.end_date_active, SYSDATE+1)
227 AND rlt.role_id = rol.role_id
228 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
229 AND rlt.delete_flag = 'N'
230 AND SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active, SYSDATE+1)
231 AND rlt.role_resource_id = grpmem.group_member_id
232 AND grpmem.delete_flag = 'N'
233 )
234
235 UNION /* the user herself */
236 SELECT
237 mydir.resource_name ,--mydir.resource_id,
238 mydir.group_name, --mydir.group_id,
239 mydir.role_name, mydir.role_code
240 FROM jtf_tty_my_directs_gt /*jtf_tty_my_directs_v*/ mydir
241 WHERE mydir.current_user_id = userid
242 and mydir.dir_user_id = userid
243 and ( mydir.resource_id, mydir.group_id, mydir.role_code) in
244 ( select /*+ NO_MERGE */
245 narsc.resource_id, narsc.rsc_group_id, narsc.rsc_role_code
246 from jtf_tty_named_acct_rsc narsc
247 where narsc.terr_group_account_id = tgaid
248 );
249
250
251 /***********this squery is very slow, view needs tune *********/
252 /* cursor na_sales(userid in number, tgaid in number) IS
253 select DISTINCT
254 mydir.resource_name,
255 mydir.group_name,
256 mydir.role_name, mydir.role_code
257 FROM
258 jtf_tty_named_acct_rsc narsc,
259 jtf_tty_terr_grp_accts ga,
260 jtf_rs_rep_managers repmgr,
261 jtf_tty_my_directs_v mydir
262 WHERE narsc.resource_id = repmgr.resource_id
263 and narsc.rsc_group_id = repmgr.group_id
264 and repmgr.parent_resource_id = mydir.resource_id
265 and mydir.current_user_id = userid
266 AND narsc.terr_group_account_id = ga.terr_group_account_id
267 AND ga.terr_group_account_id = tgaid;
268 */
269
270 cursor al_sales(align_id in number, tgaid in number) IS
271 select rsc.resource_name, --ap.resource_id,
275 JTF_TTY_PTERR_ACCTS pa,
272 grp.group_name, --ap.rsc_group_id group_id,
273 rol.role_name, ap.rsc_role_code role_code
274 from JTF_TTY_ALIGN_ACCTS aa,
276 JTF_TTY_ALIGN_PTERR ap,
277 jtf_rs_resource_extns_vl rsc,
278 jtf_rs_groups_vl grp,
279 jtf_rs_roles_vl rol
280 where aa.terr_group_account_id = tgaid
281 and aa.alignment_id = align_id
282 and aa.align_acct_id = pa.align_acct_id
283 and pa.align_proposed_terr_id= ap.align_proposed_terr_id
284 and ap.resource_type = 'RS_EMPLOYEE'
285 and rsc.resource_id = ap.resource_id
286 and grp.group_id = ap.rsc_group_id
287 and rol.role_code = ap.rsc_role_code
288 order by ap.rsc_role_code, rsc.resource_name ;
289
290
291
292 BEGIN
293 --delete from tmp;
294 --insert into tmp values('1 start user_id=' || p_userid, to_char(sysdate,'HH:MI:SS'));commit;
295 -- remove existing old data for this userid
296 delete from JTF_TTY_WEBADI_INTERFACE
297 where user_id = to_number(p_userid);
298 -- and sysdate - creation_date >2;
299
300 select jtf_tty_interface_s.nextval into SEQ from dual;
301
302 begin
303 select resource_id into salesMgr from jtf_rs_resource_extns
304 where user_id = to_number(p_userid);
305
306 exception
307 when no_data_found then
308 x_seq := '-100';
309 return;
310 end;
311
312
313 /* build globle temp table */
314 delete from jtf_tty_my_directs_gt;
315 INSERT INTO jtf_tty_my_directs_gt
316 (
317 resource_id,
318 resource_name,
319 group_id,
320 group_name,
321 role_code,
322 role_name,
323 dir_user_id,
324 current_user_id,
325 parent_group_id,
326 current_user_role_code,
327 current_user_rsc_id
328 )
329 select
330 resource_id,
331 resource_name,
332 group_id,
333 group_name,
334 role_code,
335 role_name,
336 dir_user_id,
337 current_user_id,
338 parent_group_id,
339 current_user_role_code,
340 current_user_rsc_id
341 from jtf_tty_my_directs_v
342 where CURRENT_USER_ID = to_number(p_userid);
343
344 commit;
345
346 -- insert into tmp values('1.5 '||l_var1, l_var2); commit;
347
348 l_getAlignNamedAccount :=
349 ' INSERT into JTF_TTY_WEBADI_INTERFACE ' ||
350 ' ( USER_SEQUENCE,USER_ID,TERR_GRP_ACCT_ID,JTF_TTY_WEBADI_INT_ID,NAMED_ACCOUNT,SITE_TYPE,TRADE_NAME,DUNS, '||
351 ' GU_DUNS,GU_NAME,CITY,STATE,POSTAL_CODE,TERRITORY_GROUP, ALIGNMENT_ID, ' ||
352 ' CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE ' ||
353 ' ) ' ||
354 ' SELECT ' ||
355 seq || ' USER_SEQUENCE,'||
356 P_USERID || ' USER_ID,'||
357 ' ga.terr_group_account_id gaid, '||
358 ' na.named_account_id naid, '||
359 ' hzp.party_name named_account, '||
360 ' lkp.meaning site_type, '||
361 ' hzp.known_as trade_name, '||
362 ' hzp.duns_number_c site_duns, '||
363 ' GU.GU_DUNS gu_duns, ' ||
364 ' GU.GU_NAME gu_name, ' ||
365 ' hzp.city city, '||
366 ' hzp.state state, '||
367 ' hzp.postal_code postal_code, '||
368 ' ttygrp.terr_group_name grpname, '||
369 P_ALIGN_ID || ' ALIGNMENT_ID,' ||
370 P_USERID || ' CREATED_BY,' ||
371 '''' || sysdate|| '''' || ' CREATION_DATE,' ||
372 P_USERID || ' LAST_UPDATED_BY,'
373 || '''' || sysdate || '''' || ' LAST_UPDATE_DATE '||
374 ' from hz_parties hzp, '||
375 ' jtf_tty_named_accts na, '||
376 ' jtf_tty_terr_grp_accts ga, '||
377 ' fnd_lookups lkp, '||
378 ' jtf_tty_terr_groups ttygrp '||
379 ' , ( /* Global Ultimate */ ' ||
380 ' SELECT min(gup.party_name) GU_NAME ' ||
381 ' , min(gup.duns_number_c) GU_DUNS ' ||
382 ' , hzr.object_id GU_OBJECT_ID ' ||
383 ' FROM hz_parties gup ' ||
384 ' , hz_relationships hzr ' ||
385 ' WHERE hzr.subject_table_name = ''HZ_PARTIES'' ' ||
386 ' AND hzr.object_table_name = ''HZ_PARTIES'' ' ||
387 ' AND hzr.relationship_type = ''GLOBAL_ULTIMATE'' ' ||
388 ' AND hzr.relationship_code = ''GLOBAL_ULTIMATE_OF'' ' ||
389 ' AND hzr.status = ''A'' ' ||
390 ' AND hzr.subject_id = gup.party_id ' ||
391 ' AND gup.status = ''A'' ' ||
392 ' group by hzr.object_id ) GU ' ||
393 ' where hzp.party_id = na.party_id '||
397 ' and ttygrp.terr_group_id = ga.terr_group_id '||
394 ' and na.site_type_code = lkp.lookup_code '||
395 ' and lkp.lookup_type = ''JTF_TTY_SITE_TYPE_CODE'' '||
396 ' and na.named_account_id = ga.named_account_id '||
398 ' and ttygrp.active_from_date <= sysdate '||
399 ' and ( ttygrp.active_to_date is null '||
400 ' or '||
401 ' ttygrp.active_to_date >= sysdate '||
402 ' ) '||
403 ' and ga.terr_group_account_id IN '||
404 ' ( select /*+ NO_MERGE */ narsc.terr_group_account_id '||
405 ' from jtf_tty_named_acct_rsc narsc, '||
406 ' jtf_tty_srch_my_resources_v repdn '||
407 ' where narsc.resource_id = repdn.resource_id '||
408 ' and narsc.rsc_group_id = repdn.group_id '||
409 ' and repdn.current_user_id = :p_userid '||
410 ' ) '||
411 ' AND GU.GU_OBJECT_ID (+) = hzp.party_id ';
412
413
414 -- l_na_sales :=
415 /* remove duplicate salesperson caused by rollup */
416 /* each dir in the view should appear once */
417 /* ' select DISTINCT '||
418 ' dir.resource_name, dir.resource_id, '||
419 ' dir.group_name, dir.group_id, '||
420 ' dir.role_name, dir.role_code '||
421 ' FROM '||
422 ' jtf_tty_named_acct_rsc narsc, '||
423 ' jtf_tty_terr_grp_accts ga, '||
424 ' jtf_rs_rep_managers repmgr, '||
425 ' jtf_tty_my_directs_v mydir '||
426 ' WHERE narsc.resource_id = repmgr.resource_id '||
427 ' and narsc.rsc_group_id = repmgr.group_id '||
428 ' and repmgr.parent_resource_id = mydir.resource_id '||
429 ' and mydir.current_user_id = :0 '||
430 ' AND narsc.terr_group_account_id = ga.terr_group_account_id '||
431 ' AND ga.terr_group_account_id = :1; ';
432 */
433
434
435
436 /* say Jogn's log in, Sheela and JK are assigned to a NA, JK shows in dir query,
437 sheela rolls up to JK and JK shows in indir's query. Here we only want to see one JK instead of two.
438 so union dir and indir query
439 */
440 /*
441 l_na_sales :=
442 -- l_rsc_4_na_owned_by_user_dir
443 ' select '||
444 ' dir.resource_name, dir.resource_id, '||
445 ' dir.group_name, dir.group_id, '||
446 ' dir.role_name, dir.role_code '||
447 ' from '||
448 ' jtf_tty_my_directs_v dir, '||
449 ' jtf_tty_named_acct_rsc narsc, '||
450 ' jtf_tty_terr_grp_accts ga '||
451 ' where dir.current_user_id = :0 '||
452 ' and dir.resource_id = narsc.resource_id '||
453 ' and dir.role_code = narsc.rsc_role_code '||
454 ' and dir.group_id = narsc.rsc_group_id '||
455 ' and narsc.terr_group_account_id = ga.terr_group_account_id '||
456 ' and narsc.rsc_resource_type = ''RS_EMPLOYEE'' '||
457 ' and ga.terr_group_account_id = :1 '||
458 ' order by dir.role_code, dir.resource_name ' ||
459 ' UNION ' || -- union will remove duplicate.
460 --l_rsc_4_na_owned_by_indirect
461 ' select'||
462 ' dir.resource_name, dir.resource_id,'||
463 ' dir.group_name, dir.group_id, '||
464 ' dir.role_name, dir.role_code '||
465 ' from '||
466 ' jtf_tty_my_directs_v dir '||
467 ' where dir.current_user_id = :0 '||
468 ' and dir.dir_user_id <> :1 '||
469 ' and dir.resource_id IN ( select res.parent_resource_id '||
470 ' from jtf_rs_rep_managers res, '||
471 ' jtf_tty_named_acct_rsc narsc, '||
472 ' jtf_tty_terr_grp_accts ga '||
473 ' where res.resource_id = narsc.resource_id '||
474 ' and res.group_id = narsc.rsc_group_id '||
475 ' and res.role_code = narsc.rsc_role_code '||
476 ' and narsc.terr_group_account_id = ga.terr_group_account_id '||
477 ' and narsc.rsc_resource_type = ''RS_EMPLOYEE'' '||
478 ' and ga.terr_group_account_id = :2 ) '||
479 ' order by dir.role_code, dir.resource_name ';
480 */
481
482
483
484
485 /* Named accounts are from named accounts table, no matter what align_id is */
486 -- insert into tmp values('2 start querying NA l_getAlignNamedAccount=' || l_getAlignNamedAccount, to_char(sysdate,'HH:MI:SS'));commit;
487
488 EXECUTE IMMEDIATE l_getAlignNamedAccount USING to_number(p_userid);
489 COMMIT;
490
491 -- insert into tmp values('3. start statis', to_char(sysdate,'HH:MI:SS'));commit;
492
493 /* Nas are populated, now start collect sales*/
494 /* populate slots */
495 if p_init_flag='Y' or p_align_id is null then
496 i:=1;
497 for stat in getStatisticByNA(to_number(p_userid))
498 LOOP
499 if i+stat.num-1 <=30 then
500 for k in i..i+stat.num-1
501 loop
502 COL_SOLT(k) := stat.role_code;
503 end loop;
504 else x_seq := '-1';
505 return;
509
506 end if;
507 i:=i+stat.num;
508 END LOOP;
510 else /* by alignment */
511 i:=1;
512 for stat in getStatisticByAlign(to_number(p_userid), to_number(p_align_id) )
513 LOOP
514 if i+stat.num-1 <=30 then
515 for k in i..i+stat.num-1
516 loop
517 COL_SOLT(k) := stat.role_code;
518 end loop;
519 else x_seq := '-1';
520 return;
521 end if;
522 i:=i+stat.num;
523 END LOOP;
524 end if;
525
526 /* for each NA_ID */
527 --- insert into tmp values ( to_char(sysdate,'HH,MI:SS'), '4. finish analysis '); commit;
528
529 -- insert into tmp values('4 start update', to_char(sysdate,'HH:MI:SS'));commit;
530 FOR m IN getNAFromInterface
531 LOOP
532
533 l_dnb_annual_rev:= null;
534 l_dnb_num_of_emp:=null;
535 l_prior_won:=null;
536
537
538
539 begin
540 select metric_value into l_dnb_annual_rev
541 from JTF_TTY_ACCT_METRICS am
542 where m.JTF_TTY_WEBADI_INT_ID = am.named_account_id
543 and am.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
544 and am.metric_lookup_code = 'DNB_ANNUAL_REVENUE'
545 and rownum<2;
546
547 exception
548 when no_data_found then
549 null;
550 end;
551
552 begin
553 select metric_value into l_dnb_num_of_emp
554 from JTF_TTY_ACCT_METRICS am
555 where m.JTF_TTY_WEBADI_INT_ID = am.named_account_id
556 and am.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
557 and am.metric_lookup_code = 'DNB_NUM_EMPLOYEES'
558 and rownum<2;
559 exception
560 when no_data_found then
561 null;
562
563 end;
564
565 begin
566 select metric_value into l_prior_won
567 from JTF_TTY_ACCT_METRICS am
568 where m.JTF_TTY_WEBADI_INT_ID = am.named_account_id
569 and am.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
570 and am.metric_lookup_code = 'PRIOR_SALES'
571 and rownum<2;
572
573 exception
574 when no_data_found then
575 null;
576
577 end;
578
579
580 /* clear col_used flags */
581 COL_USED :=NARRAY_TYPE(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
582 RESOURCE_NAME :=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
583 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
584 GROUP_NAME :=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
585 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
586 ROLE_NAME :=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
587 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
588
589 /* if there are some newly created NA, which is not part of align_account,
590 those NA are valid and in the interface table but no rep in the align_account table.
591 In this case, the salesrep is from named account resource table */
592 /* for removed NA, are those taken care of in upload? */
593 begin
594 select alignment_id into l_align_id
595 from jtf_tty_align_accts
596 where terr_group_account_id = m.terr_grp_acct_id
597 and alignment_id = p_align_id;
598
599 exception
600 when no_data_found then
601 l_align_id:=null;
602 end;
603
604
605 /* get all sales for this NA */
606 if p_init_flag='Y' or l_align_id is null then
607 FOR SALES IN na_sales( to_number(p_userid), m.terr_grp_acct_id )
608 LOOP
609
610 --k:=0; -- not yet sloted
611 FOR j in 1..30
612 LOOP -- look into 30 slots
613 if SALES.role_code = COL_SOLT(j) and COL_USED(j)=0 then
614 COL_USED(j) :=1;
615
616 RESOURCE_NAME(j):=SALES.resource_name;
617 GROUP_NAME(j) :=SALES.group_name;
618 ROLE_NAME(j) :=SALES.role_name;
619 exit;
620 end if;
621 END LOOP; -- of slotting
622 END LOOP; -- of SALES
623 else -- of p_init_flag='Y' or l_align_id is null
624 FOR SALES IN al_sales( to_number(p_align_id), m.terr_grp_acct_id )
625 LOOP
626
627 --k:=0; -- not yet sloted
628 FOR j in 1..30
629 LOOP -- look into 30 slots
630 if SALES.role_code = COL_SOLT(j) and COL_USED(j)=0 then
631 COL_USED(j) :=1;
632
633 RESOURCE_NAME(j):=SALES.resource_name;
634 GROUP_NAME(j) :=SALES.group_name;
638 END LOOP; -- of slotting
635 ROLE_NAME(j) :=SALES.role_name;
636 exit;
637 end if;
639 END LOOP; -- of SALES
640
641 end if; -- of p_init_flag='Y' or l_align_id is null
642
643 --insert into tmp values('4.5 done with one salesrep', to_char(sysdate,'HH:MI:SS'));commit;
644
645 update JTF_TTY_WEBADI_INTERFACE -- /*+ INDEX(JTF_TTY_WEBADI_INTF_N2) */
646 set RESOURCE1_NAME=RESOURCE_NAME(1),GROUP1_NAME=GROUP_NAME(1),ROLE1_NAME=ROLE_NAME(1),
647 RESOURCE2_NAME=RESOURCE_NAME(2),GROUP2_NAME=GROUP_NAME(2),ROLE2_NAME=ROLE_NAME(2),
648 RESOURCE3_NAME=RESOURCE_NAME(3),GROUP3_NAME=GROUP_NAME(3),ROLE3_NAME=ROLE_NAME(3),
649 RESOURCE4_NAME=RESOURCE_NAME(4),GROUP4_NAME=GROUP_NAME(4),ROLE4_NAME=ROLE_NAME(4),
650 RESOURCE5_NAME=RESOURCE_NAME(5),GROUP5_NAME=GROUP_NAME(5),ROLE5_NAME=ROLE_NAME(5),
651 RESOURCE6_NAME=RESOURCE_NAME(6),GROUP6_NAME=GROUP_NAME(6),ROLE6_NAME=ROLE_NAME(6),
652 RESOURCE7_NAME=RESOURCE_NAME(7),GROUP7_NAME=GROUP_NAME(7),ROLE7_NAME=ROLE_NAME(7),
653 RESOURCE8_NAME=RESOURCE_NAME(8),GROUP8_NAME=GROUP_NAME(8),ROLE8_NAME=ROLE_NAME(8),
654 RESOURCE9_NAME=RESOURCE_NAME(9),GROUP9_NAME=GROUP_NAME(9),ROLE9_NAME=ROLE_NAME(9),
655 RESOURCE10_NAME=RESOURCE_NAME(10),GROUP10_NAME=GROUP_NAME(10),ROLE10_NAME=ROLE_NAME(10),
656 RESOURCE11_NAME=RESOURCE_NAME(11),GROUP11_NAME=GROUP_NAME(11),ROLE11_NAME=ROLE_NAME(11),
657 RESOURCE12_NAME=RESOURCE_NAME(12),GROUP12_NAME=GROUP_NAME(12),ROLE12_NAME=ROLE_NAME(12),
658 RESOURCE13_NAME=RESOURCE_NAME(13),GROUP13_NAME=GROUP_NAME(13),ROLE13_NAME=ROLE_NAME(13),
659 RESOURCE14_NAME=RESOURCE_NAME(14),GROUP14_NAME=GROUP_NAME(14),ROLE14_NAME=ROLE_NAME(14),
660 RESOURCE15_NAME=RESOURCE_NAME(15),GROUP15_NAME=GROUP_NAME(15),ROLE15_NAME=ROLE_NAME(15),
661 RESOURCE16_NAME=RESOURCE_NAME(16),GROUP16_NAME=GROUP_NAME(16),ROLE16_NAME=ROLE_NAME(16),
662 RESOURCE17_NAME=RESOURCE_NAME(17),GROUP17_NAME=GROUP_NAME(17),ROLE17_NAME=ROLE_NAME(17),
663 RESOURCE18_NAME=RESOURCE_NAME(18),GROUP18_NAME=GROUP_NAME(18),ROLE18_NAME=ROLE_NAME(18),
664 RESOURCE19_NAME=RESOURCE_NAME(19),GROUP19_NAME=GROUP_NAME(19),ROLE19_NAME=ROLE_NAME(19),
665 RESOURCE20_NAME=RESOURCE_NAME(20),GROUP20_NAME=GROUP_NAME(20),ROLE20_NAME=ROLE_NAME(20),
666 RESOURCE21_NAME=RESOURCE_NAME(21),GROUP21_NAME=GROUP_NAME(21),ROLE21_NAME=ROLE_NAME(21),
667 RESOURCE22_NAME=RESOURCE_NAME(22),GROUP22_NAME=GROUP_NAME(22),ROLE22_NAME=ROLE_NAME(22),
668 RESOURCE23_NAME=RESOURCE_NAME(23),GROUP23_NAME=GROUP_NAME(23),ROLE23_NAME=ROLE_NAME(23),
669 RESOURCE24_NAME=RESOURCE_NAME(24),GROUP24_NAME=GROUP_NAME(24),ROLE24_NAME=ROLE_NAME(24),
670 RESOURCE25_NAME=RESOURCE_NAME(25),GROUP25_NAME=GROUP_NAME(25),ROLE25_NAME=ROLE_NAME(25),
671 RESOURCE26_NAME=RESOURCE_NAME(26),GROUP26_NAME=GROUP_NAME(26),ROLE26_NAME=ROLE_NAME(26),
672 RESOURCE27_NAME=RESOURCE_NAME(27),GROUP27_NAME=GROUP_NAME(27),ROLE27_NAME=ROLE_NAME(27),
673 RESOURCE28_NAME=RESOURCE_NAME(28),GROUP28_NAME=GROUP_NAME(28),ROLE28_NAME=ROLE_NAME(28),
674 RESOURCE29_NAME=RESOURCE_NAME(29),GROUP29_NAME=GROUP_NAME(29),ROLE29_NAME=ROLE_NAME(29),
675 RESOURCE30_NAME=RESOURCE_NAME(30),GROUP30_NAME=GROUP_NAME(30),ROLE30_NAME=ROLE_NAME(30),
676 dnb_annual_rev=l_dnb_annual_rev,dnb_num_of_em=l_dnb_num_of_emp,prior_won=l_prior_won
677 where user_id = p_userid
678 and TERR_GRP_ACCT_ID =m.TERR_GRP_ACCT_ID;
679
680 END LOOP;
681 -- insert into tmp values('5 done', to_char(sysdate,'HH:MI:SS'));commit;
682 commit;
683 x_seq := to_char(seq);
684
685 END;
686
687
688
689 /************************************************************************/
690 /* UPLOAD *****/
691 /************************************************************************/
692 PROCEDURE CALCULATE_ALIGN_METRICS(
693 p_alignment_id IN NUMBER,
694 p_user_id IN NUMBER,
695 p_pterr_tbl IN NUMBER_TABLE_TYPE,
696 p_all_pterr_flag IN VARCHAR2
697 )
698 IS
699
700 CURSOR c_all_pterrs
701 IS
702 SELECT AA.align_proposed_terr_id
703 FROM JTF_TTY_ALIGN_PTERR AA
704 WHERE AA.ALIGNMENT_ID = p_alignment_id;
705
706 l_align_pterrs_tbl Number_table_type := Number_table_type();
707 l_sysdate DATE;
708 l_alignment_id NUMBER := 0;
709 l_user_id NUMBER;
710
711 begin
712
713 l_sysdate := SYSDATE;
714 l_user_id := p_user_id;
715 l_alignment_id := p_alignment_id;
716 IF p_all_pterr_flag = 'Y'
717 THEN
718 OPEN c_all_pterrs;
719 FETCH c_all_pterrs BULK COLLECT INTO l_align_pterrs_tbl;
720 CLOSE c_all_pterrs;
721 ELSE
722 l_align_pterrs_tbl := p_pterr_tbl;
723 END IF;
724
725 FORALL y IN l_align_pterrs_tbl.FIRST .. l_align_pterrs_tbl.LAST
726 delete from jtf_tty_pterr_metrics
727 where align_proposed_terr_id = l_align_pterrs_tbl(y);
728
729 --processing to insert into the JTF_TTY_PTERR_METRICS table: summ up metric values for the accounts owned by this rep
730 FORALL j IN l_align_pterrs_tbl.FIRST .. l_align_pterrs_tbl.LAST
731 insert into jtf_tty_pterr_metrics
732 ( align_pterr_metric_id
733 ,object_version_number
737 ,metric_value
734 ,align_proposed_terr_id
735 ,metric_lookup_type
736 ,metric_lookup_code
738 ,metric_value_percent
739 ,created_by
740 ,creation_date
741 ,last_updated_by
742 ,last_update_date
743 ,last_update_login
744 )
745 select
746 jtf_tty_pterr_metrics_s.nextval
747 , 1
748 , l_align_pterrs_tbl(j)
749 , 'JTF_TTY_ALIGN_METRICS'
750 , 'DNB_ANNUAL_REVENUE'
751 , pterr_list.metric_value
752 , pterr_list.metric_pct
753 , l_user_id
754 , l_sysdate
755 , l_user_id
756 , l_sysdate
757 , 1
758 from ( select pa.align_proposed_terr_id pterr_id
759 ,sum(am.metric_value) metric_value
760 ,round( (sum(am.metric_value)/ alm.align_metric_val )* 100, 2 ) metric_pct
761 from JTF_TTY_ACCT_METRICS AM,
762 jtf_tty_align_accts ac,
763 JTF_TTY_PTERR_ACCTS pa,
764 jtf_tty_terr_grp_accts ga,
765 jtf_tty_align_pterr ap,
766 ( select sum(ams.metric_value) align_metric_val
767 from jtf_tty_acct_metrics ams
768 ,jtf_tty_terr_grp_accts tga
769 ,jtf_tty_align_accts ala
770 where ala.alignment_id = l_alignment_id
771 and ala.terr_group_account_id = tga.terr_group_account_id
772 and tga.named_account_id = ams.named_account_id
773 and ams.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
774 and ams.metric_lookup_code = 'DNB_ANNUAL_REVENUE'
775 ) alm
776 where
777 pa.align_proposed_terr_id = ap.align_proposed_terr_id
778 and ap.alignment_id = l_alignment_id
779 and pa.align_acct_id = ac.align_acct_id
780 and ac.terr_group_account_id = ga.terr_group_account_id
781 and ga.named_account_id = am.named_account_id
782 and am.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
783 and am.metric_lookup_code = 'DNB_ANNUAL_REVENUE'
784 and pa.align_proposed_terr_id = l_align_pterrs_tbl(j)
785 and alm.align_metric_val > 0
786 group by alm.align_metric_val, pa.align_proposed_terr_id
787 ) pterr_list;
788
789 FORALL j IN l_align_pterrs_tbl.FIRST .. l_align_pterrs_tbl.LAST
790 insert into jtf_tty_pterr_metrics
791 ( align_pterr_metric_id
792 ,object_version_number
793 ,align_proposed_terr_id
794 ,metric_lookup_type
795 ,metric_lookup_code
796 ,metric_value
797 ,metric_value_percent
798 ,created_by
799 ,creation_date
800 ,last_updated_by
801 ,last_update_date
802 ,last_update_login
803 )
804 select
805 jtf_tty_pterr_metrics_s.nextval
806 , 1
807 , l_align_pterrs_tbl(j)
808 , 'JTF_TTY_ALIGN_METRICS'
809 , 'DNB_NUM_EMPLOYEES'
810 , pterr_list.metric_value
811 , pterr_list.metric_pct
812 , l_user_id
813 , l_sysdate
814 , l_user_id
815 , l_sysdate
816 , 1
817 from ( select pa.align_proposed_terr_id pterr_id
818 ,sum(am.metric_value) metric_value
819 ,round( (sum(am.metric_value)/ alm.align_metric_val )* 100, 2 ) metric_pct
820 from JTF_TTY_ACCT_METRICS AM,
821 jtf_tty_align_accts ac,
822 JTF_TTY_PTERR_ACCTS pa,
823 jtf_tty_terr_grp_accts ga,
824 jtf_tty_align_pterr ap,
825 ( select sum(ams.metric_value) align_metric_val
826 from jtf_tty_acct_metrics ams
827 ,jtf_tty_terr_grp_accts tga
828 ,jtf_tty_align_accts ala
829 where ala.alignment_id = l_alignment_id
830 and ala.terr_group_account_id = tga.terr_group_account_id
831 and tga.named_account_id = ams.named_account_id
832 and ams.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
833 and ams.metric_lookup_code = 'DNB_NUM_EMPLOYEES'
834 ) alm
835 where
836 pa.align_proposed_terr_id = ap.align_proposed_terr_id
837 and ap.alignment_id = l_alignment_id
838 and pa.align_acct_id = ac.align_acct_id
839 and ac.terr_group_account_id = ga.terr_group_account_id
840 and ga.named_account_id = am.named_account_id
841 and am.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
842 and am.metric_lookup_code = 'DNB_NUM_EMPLOYEES'
843 and pa.align_proposed_terr_id = l_align_pterrs_tbl(j)
844 and alm.align_metric_val > 0
845 group by alm.align_metric_val, pa.align_proposed_terr_id
846 ) pterr_list;
847
848 FORALL j IN l_align_pterrs_tbl.FIRST .. l_align_pterrs_tbl.LAST
849 insert into jtf_tty_pterr_metrics
850 ( align_pterr_metric_id
851 ,object_version_number
852 ,align_proposed_terr_id
853 ,metric_lookup_type
854 ,metric_lookup_code
855 ,metric_value
856 ,metric_value_percent
857 ,created_by
858 ,creation_date
859 ,last_updated_by
863 select
860 ,last_update_date
861 ,last_update_login
862 )
864 jtf_tty_pterr_metrics_s.nextval
865 , 1
866 , l_align_pterrs_tbl(j)
867 , 'JTF_TTY_ALIGN_METRICS'
868 , 'NUM_ACCOUNTS'
869 , pterr_list.metric_value
870 , pterr_list.metric_pct
871 , l_user_id
872 , l_sysdate
873 , l_user_id
874 , l_sysdate
875 , 1
876 from (select pa.align_proposed_terr_id pterr_id
877 ,count(pa.align_acct_id) metric_value
878 ,round( (count(pa.align_acct_id)/ alm.tot_align_metric_val )* 100, 2 ) metric_pct
879 from
880 JTF_TTY_PTERR_ACCTS pa,
881 jtf_tty_align_pterr ap,
882 ( select count(ala.terr_group_account_id) tot_align_metric_val
883 from jtf_tty_align_accts ala
884 where ala.alignment_id = l_alignment_id
885 ) alm
886 where
887 pa.align_proposed_terr_id = ap.align_proposed_terr_id
888 and ap.alignment_id = l_alignment_id
889 and pa.align_proposed_terr_id = l_align_pterrs_tbl(j)
890 and alm.tot_align_metric_val > 0
891 group by alm.tot_align_metric_val, pa.align_proposed_terr_id
892 ) pterr_list;
893
894 FORALL j IN l_align_pterrs_tbl.FIRST .. l_align_pterrs_tbl.LAST
895 insert into jtf_tty_pterr_metrics
896 ( align_pterr_metric_id
897 ,object_version_number
898 ,align_proposed_terr_id
899 ,metric_lookup_type
900 ,metric_lookup_code
901 ,metric_value
902 ,metric_value_percent
903 ,created_by
904 ,creation_date
905 ,last_updated_by
906 ,last_update_date
907 ,last_update_login
908 )
909 select
910 jtf_tty_pterr_metrics_s.nextval
911 , 1
912 , l_align_pterrs_tbl(j)
913 , 'JTF_TTY_ALIGN_METRICS'
914 , 'PRIOR_SALES'
915 , pterr_list.metric_value
916 , pterr_list.metric_pct
917 , l_user_id
918 , l_sysdate
919 , l_user_id
920 , l_sysdate
921 , 1
922 from ( select pa.align_proposed_terr_id pterr_id
923 ,sum(am.metric_value) metric_value
924 ,round( (sum(am.metric_value)/ alm.align_metric_val )* 100, 2 ) metric_pct
925 from JTF_TTY_ACCT_METRICS AM,
926 jtf_tty_align_accts ac,
927 JTF_TTY_PTERR_ACCTS pa,
928 jtf_tty_terr_grp_accts ga,
929 jtf_tty_align_pterr ap,
930 ( select sum(ams.metric_value) align_metric_val
931 from jtf_tty_acct_metrics ams
932 ,jtf_tty_terr_grp_accts tga
933 ,jtf_tty_align_accts ala
934 where ala.alignment_id = l_alignment_id
935 and ala.terr_group_account_id = tga.terr_group_account_id
936 and tga.named_account_id = ams.named_account_id
937 and ams.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
938 and ams.metric_lookup_code = 'PRIOR_SALES'
939 ) alm
940 where
941 pa.align_proposed_terr_id = ap.align_proposed_terr_id
942 and ap.alignment_id = l_alignment_id
943 and pa.align_acct_id = ac.align_acct_id
944 and ac.terr_group_account_id = ga.terr_group_account_id
945 and ga.named_account_id = am.named_account_id
946 and am.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
947 and am.metric_lookup_code = 'PRIOR_SALES'
948 and pa.align_proposed_terr_id = l_align_pterrs_tbl(j)
949 and alm.align_metric_val > 0
950 group by alm.align_metric_val, pa.align_proposed_terr_id
951 ) pterr_list;
952 end;
953
954
955 PROCEDURE UPDATE_ALIGNMENT_TEAM(
956 p_api_version_number IN NUMBER,
957 p_init_msg_list IN VARCHAR2,
958 p_SQL_Trace IN VARCHAR2,
959 p_Debug_Flag IN VARCHAR2,
960 p_alignment_id IN NUMBER,
961 p_user_id IN NUMBER,
962 p_user_attribute1 IN VARCHAR2,
963 p_added_rscs_tbl IN JTF_TTY_NACCT_SALES_PUB.SALESREP_RSC_TBL_TYPE,
964 p_removed_rscs_tbl IN JTF_TTY_NACCT_SALES_PUB.SALESREP_RSC_TBL_TYPE,
965 p_affected_parties_tbl IN JTF_TTY_NACCT_SALES_PUB.AFFECTED_PARTY_TBL_TYPE,
966 x_return_status OUT NOCOPY VARCHAR2,
967 x_msg_count OUT NOCOPY NUMBER,
968 x_msg_data OUT NOCOPY VARCHAR2
969 )
970 IS
971 l_align_acct_id NUMBER;
972 l_align_pterr_id NUMBER;
973 l_alignment_id NUMBER;
974 l_sysdate DATE;
975 l_imported_on DATE;
976 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ALIGNMENT_TEAM';
977 l_pterr_accts_num NUMBER;
978 l_count INTEGER := 0;
979 l_index INTEGER := 0;
980 l_found BOOLEAN := FALSE;
981 l_pterr_tbl_count INTEGER := 0;
982 l_user_id NUMBER;
983
984 align_pterrs_tbl Number_table_type := Number_table_type();
985 all_tg_accts_tbl Number_table_type := Number_table_type();
986 all_align_accts_tbl Number_table_type := Number_table_type();
987 pterrs_changed_tbl Number_table_type := Number_table_type();
988
989 cursor c_all_pterrs (c_align_acct_id NUMBER, c_alignment_id NUMBER ) IS
990 select AA.align_proposed_terr_id
991 from JTF_TTY_ALIGN_PTERR AA,
992 JTF_TTY_PTERR_ACCTS PA
993 where AA.ALIGNMENT_ID = c_alignment_id
994 and AA.ALIGN_PROPOSED_TERR_ID = PA.ALIGN_PROPOSED_TERR_ID
995 and PA.ALIGN_ACCT_ID = c_align_acct_id ;
996
997 cursor c_align_acct(c_terr_group_account_id NUMBER, c_alignment_id NUMBER ) IS
998 select align_acct_id
999 from JTF_TTY_ALIGN_ACCTS
1000 where terr_group_account_id = c_terr_group_account_id
1001 and alignment_id = c_alignment_id;
1002
1003 cursor c_all_align_accts ( c_alignment_id NUMBER ) IS
1004 select align_acct_id, terr_group_account_id
1005 from JTF_TTY_ALIGN_ACCTS
1006 where alignment_id = c_alignment_id;
1007
1008 cursor c_align_pterr(c_resource_id NUMBER, c_group_id NUMBER, c_role_code VARCHAR2,
1009 c_alignment_id NUMBER ) IS
1010 select align_proposed_terr_id
1011 from JTF_TTY_ALIGN_PTERR
1012 where alignment_id = c_alignment_id
1013 and resource_id = c_resource_id
1014 and rsc_group_id = c_group_id
1015 and rsc_role_code = c_role_code;
1016
1017 CURSOR c_all_tg_accounts( c_user_id NUMBER ) IS
1018 select ga.terr_group_account_id gaid
1019 from jtf_tty_terr_grp_accts ga,
1020 jtf_tty_terr_groups ttygrp
1021 where ttygrp.terr_group_id = ga.terr_group_id
1022 and ttygrp.active_from_date <= sysdate
1023 and ( ttygrp.active_to_date is null
1024 or
1025 ttygrp.active_to_date >= sysdate
1026 )
1027 and ga.terr_group_account_id IN
1028 ( select /*+ NO_MERGE */
1029 narsc.terr_group_account_id
1030 from jtf_tty_named_acct_rsc narsc,
1031 jtf_tty_srch_my_resources_v repdn
1032 where narsc.resource_id = repdn.resource_id
1033 and narsc.rsc_group_id = repdn.group_id
1034 and repdn.current_user_id = c_user_id
1035 );
1036
1037 CURSOR c_res_for_tg_account(c_tg_acct_id VARCHAR2, c_user_id NUMBER ) IS
1038 select narsc.resource_id resource_id,
1039 narsc.rsc_group_id group_id,
1040 narsc.rsc_role_code role_code
1041 from jtf_tty_named_acct_rsc narsc
1042 where narsc.terr_group_account_id = c_tg_acct_id
1043 and narsc.rsc_resource_type = 'RS_EMPLOYEE'
1044 and (narsc.resource_id, narsc.rsc_group_id, narsc.rsc_role_code ) IN
1045 ( select /*+ NO_MERGE */ mydir.resource_id, mydir.group_id, mydir.role_code
1046 from jtf_tty_srch_my_resources_v mydir
1047 where mydir.current_user_id = c_user_id );
1048
1049
1050 CURSOR c_direct_for_tg_account(c_tg_acct_id VARCHAR2, c_user_id NUMBER) IS
1051 select mydir.resource_id resource_id,
1052 mydir.group_id group_id,
1053 mydir.role_code role_code
1054 from jtf_tty_my_directs_v mydir
1055 where mydir.current_user_id = c_user_id
1056 and mydir.dir_user_id <> c_user_id
1057 and ( mydir.resource_id, mydir.group_id, mydir.role_code) in
1058 ( select /*+ NO_MERGE */
1059 repmgr.parent_resource_id,
1060 grpmem.group_id,
1061 rol.role_code
1062 from jtf_tty_named_acct_rsc narsc,
1063 jtf_rs_rep_managers repmgr,
1064 jtf_rs_role_relations rlt,
1065 jtf_rs_roles_b rol,
1066 jtf_rs_group_members grpmem
1067 where narsc.resource_id = repmgr.resource_id
1068 AND narsc.rsc_group_id = repmgr.group_id
1069 AND narsc.terr_group_account_id = c_tg_acct_id
1070 AND repmgr.par_role_relate_id = rlt.role_relate_id
1071 AND SYSDATE BETWEEN repmgr.start_date_active
1072 AND NVL(repmgr.end_date_active, SYSDATE+1)
1073 AND rlt.role_id = rol.role_id
1074 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
1075 AND rlt.delete_flag = 'N'
1076 AND SYSDATE BETWEEN rlt.start_date_active
1077 AND NVL(rlt.end_date_active, SYSDATE+1)
1078 AND rlt.role_resource_id = grpmem.group_member_id
1079 AND grpmem.delete_flag = 'N'
1080 );
1081
1082 begin
1083
1084 l_alignment_id := p_alignment_id;
1085 l_sysdate := SYSDATE;
1086
1087 --insert into tmp2 values('0.Start of UPDATE_ALIGNMENT_TEAM','Start of UPDATE_ALIGNMENT_TEAM'); commit;
1088 l_user_id := p_user_id;
1089
1090 select imported_on
1091 into l_imported_on
1092 from jtf_tty_alignments
1093 where alignment_id = l_alignment_id;
1094
1095 --Initial population of alignment datamodel if this is the first upload
1096 if l_imported_on is null then
1097
1098 --populate JTF_TTY_ALIGN_ACCTS
1099 OPEN c_all_tg_accounts( c_user_id => l_user_id );
1100 FETCH c_all_tg_accounts BULK COLLECT INTO all_tg_accts_tbl;
1101 CLOSE c_all_tg_accounts;
1102
1103 FORALL k IN all_tg_accts_tbl.FIRST .. all_tg_accts_tbl.LAST
1104 insert into JTF_TTY_ALIGN_ACCTS
1105 ( align_acct_id
1106 ,object_version_number
1107 ,alignment_id
1108 ,terr_group_account_id
1109 ,created_by
1110 ,creation_date
1111 ,last_updated_by
1112 ,last_update_date
1113 ,last_update_login
1114 ) values
1115 ( JTF_TTY_ALIGN_ACCTS_S.nextval
1116 ,1
1117 ,l_alignment_id
1118 ,all_tg_accts_tbl(k)
1119 ,G_USER
1120 ,l_sysdate
1121 ,G_USER
1122 ,l_sysdate
1123 ,G_LOGIN
1124 );
1125
1126 all_tg_accts_tbl := null;
1127 OPEN c_all_align_accts(c_alignment_id => l_alignment_id );
1128 FETCH c_all_align_accts BULK COLLECT INTO all_align_accts_tbl, all_tg_accts_tbl;
1129 CLOSE c_all_align_accts;
1130
1131 --insert into tmp2 values('10. b4 all_align_accts_tbl loop','b4 all_align_accts_tbl loop'); commit;
1132 FOR j in all_align_accts_tbl.FIRST .. all_align_accts_tbl.LAST
1133 LOOP
1134 --insert into tmp2 values('20. all_align_accts_tbl(j)', all_align_accts_tbl(j)); commit;
1135 --find the resources for this terr_group_account
1136 FOR res_rec in c_res_for_tg_account( c_tg_acct_id => all_tg_accts_tbl(j),
1137 c_user_id => l_user_id )
1138 LOOP
1139 --insert into tmp2 values('30. res_rec.resource_id, res_rec.group_id, res_rec.role_code', res_rec.resource_id || ' ' || res_rec.group_id || ' ' || res_rec.role_code); commit;
1140 --find the pterr associated with this resource
1141 l_found := FALSE;
1142 FOR align_rec in c_align_pterr( c_resource_id => res_rec.resource_id
1143 , c_group_id => res_rec.group_id
1144 , c_role_code => res_rec.role_code
1145 , c_alignment_id => l_alignment_id)
1146 LOOP
1147 --insert into tmp2 values('40. insert into JTF_TTY_PTERR_ACCTS, align_rec.align_proposed_terr_id', align_rec.align_proposed_terr_id); commit;
1148 --populate JTF_TTY_PTERR_ACCTS
1149 insert into JTF_TTY_PTERR_ACCTS
1150 ( align_pterr_acct_id
1151 ,object_version_number
1152 ,align_proposed_terr_id
1153 ,align_acct_id
1154 ,created_by
1155 ,creation_date
1156 ,last_updated_by
1157 ,last_update_date
1158 ,last_update_login
1159 ) values
1160 ( JTF_TTY_PTERR_ACCTS_S.nextval
1161 ,1
1162 ,align_rec.align_proposed_terr_id
1163 ,all_align_accts_tbl(j)
1164 ,G_USER
1165 ,l_sysdate
1166 ,G_USER
1167 ,l_sysdate
1168 ,G_LOGIN
1169 );
1170 l_found := TRUE;
1171 --insert into tmp2 values('50. END OF: insert into JTF_TTY_PTERR_ACCTS, align_rec.align_proposed_terr_id', align_rec.align_proposed_terr_id); commit;
1172 END LOOP;
1173 IF ( NOT l_found )
1174 THEN
1175 /* Get all user's directs which have the resource in their hierarchy */
1176 FOR direct_rec IN c_direct_for_tg_account( c_tg_acct_id => all_tg_accts_tbl(j),
1177 c_user_id => l_user_id )
1178 LOOP
1179 FOR align_pterr_rec in c_align_pterr( c_resource_id => direct_rec.resource_id
1180 , c_group_id => direct_rec.group_id
1181 , c_role_code => direct_rec.role_code
1182 , c_alignment_id => l_alignment_id )
1183 LOOP
1184 --insert into tmp2 values('40. insert into JTF_TTY_PTERR_ACCTS, align_rec.align_proposed_terr_id', align_rec.align_proposed_terr_id); commit;
1185 --populate JTF_TTY_PTERR_ACCTS
1186
1187 l_pterr_accts_num := 0;
1188
1189 select count(*)
1190 into l_pterr_accts_num
1191 from jtf_tty_pterr_accts
1192 where align_proposed_terr_id = align_pterr_rec.align_proposed_terr_id
1193 and align_acct_id = all_align_accts_tbl(j);
1194
1195 IF l_pterr_accts_num < 1
1196 THEN
1197 insert into JTF_TTY_PTERR_ACCTS
1198 ( align_pterr_acct_id
1199 ,object_version_number
1200 ,align_proposed_terr_id
1201 ,align_acct_id
1202 ,created_by
1203 ,creation_date
1204 ,last_updated_by
1205 ,last_update_date
1206 ,last_update_login
1207 ) values
1208 ( JTF_TTY_PTERR_ACCTS_S.nextval
1209 ,1
1210 ,align_pterr_rec.align_proposed_terr_id
1211 ,all_align_accts_tbl(j)
1215 ,l_sysdate
1212 ,G_USER
1213 ,l_sysdate
1214 ,G_USER
1216 ,G_LOGIN
1217 );
1218 END IF;
1219 --insert into tmp2 values('50. END OF: insert into JTF_TTY_PTERR_ACCTS, align_rec.align_proposed_terr_id', align_rec.align_proposed_terr_id); commit;
1220 END LOOP; -- end align_rec
1221 END LOOP; -- end direct_rec
1222 END IF; -- end not found
1223 END LOOP; -- end res_rec
1224 END LOOP; -- end j
1225
1226 calculate_align_metrics( l_alignment_id, l_user_id, align_pterrs_tbl, 'Y' );
1227
1228 end if; --imported_on is null
1229
1230 --update imported_on date for this alignment
1231 update jtf_tty_alignments
1232 set imported_on = l_sysdate
1233 where alignment_id = l_alignment_id;
1234
1235 ---------------------------------------------
1236 -- ADDING RESOURCES TO ALIGN TEAM
1237 ---------------------------------------------
1238
1239
1240 IF ((p_affected_parties_tbl is not null) and (p_added_rscs_tbl is not null) and
1241 (p_affected_parties_tbl.last > 0) and (p_added_rscs_tbl.last > 0)) THEN
1242
1243 FOR j in p_affected_parties_tbl.first .. p_affected_parties_tbl.last LOOP
1244 --dbms_output.put_line('Adding Resources to: G_AFFECT_PARTY_TBL =' || j || G_AFFECT_PARTY_TBL(j).party_id);
1245 --insert into tmp2 values('1.p_affected_parties_tbl.loop: TGA_ID =', p_affected_parties_tbl(j).terr_group_account_id); commit;
1246
1247 OPEN c_align_acct(c_terr_group_account_id => p_affected_parties_tbl(j).terr_group_account_id,
1248 c_alignment_id => l_alignment_id);
1249 FETCH c_align_acct into l_align_acct_id;
1250
1251 --check to see if alignment account exists
1252 if c_align_acct%notfound then
1253 --
1254 --create a new alignment account record
1255 select JTF_TTY_ALIGN_ACCTS_S.nextval
1256 into l_align_acct_id
1257 from dual;
1258
1259 --insert into tmp values('2. Create Align Account', l_align_acct_id); commit;
1260 insert into JTF_TTY_ALIGN_ACCTS
1261 ( align_acct_id
1262 ,object_version_number
1263 ,alignment_id
1264 ,terr_group_account_id
1265 ,created_by
1266 ,creation_date
1267 ,last_updated_by
1268 ,last_update_date
1269 ,last_update_login
1270 ) values
1271 ( l_align_acct_id
1272 ,1
1273 ,l_alignment_id
1274 ,p_affected_parties_tbl(j).terr_group_account_id
1275 ,G_USER
1276 ,l_sysdate
1277 ,G_USER
1278 ,l_sysdate
1279 ,G_LOGIN
1280 );
1281 --insert into tmp values('3. End of Create Align Account', l_align_acct_id); commit;
1282 end if;
1283
1284 CLOSE c_align_acct;
1285
1286 FOR i in p_added_rscs_tbl.first .. p_added_rscs_tbl.last LOOP
1287 --create new association between resource (pterr) and alignment account
1288
1289 OPEN c_align_pterr(c_resource_id => p_added_rscs_tbl(i).resource_id
1290 , c_group_id => p_added_rscs_tbl(i).group_id
1291 , c_role_code => p_added_rscs_tbl(i).role_code
1292 , c_alignment_id => l_alignment_id );
1293
1294 FETCH c_align_pterr into l_align_pterr_id;
1295
1296 --insert into tmp2 values('2. b4 create pterr. l_align_pterr_id =', l_align_pterr_id); commit;
1297 if c_align_pterr%notfound then
1298 --insert into tmp2 values('1.77775. b4 c_align_pterr. p_added_rscs_tbl(i).resource_id =', p_added_rscs_tbl(i).resource_id || ' ' || p_added_rscs_tbl(i).group_id || p_added_rscs_tbl(i).role_code); commit;
1299 -- create a proposed territory for this resource
1300 select JTF_TTY_ALIGN_PTERR_S.nextval
1301 into l_align_pterr_id
1302 from dual;
1303
1304 insert into JTF_TTY_ALIGN_PTERR
1305 ( align_proposed_terr_id
1306 ,object_version_number
1307 ,alignment_id
1308 ,resource_id
1309 ,rsc_group_id
1310 ,rsc_role_code
1311 ,resource_type
1312 ,proposed_quota
1313 ,created_by
1314 ,creation_date
1315 ,last_updated_by
1316 ,last_update_date
1317 ,last_update_login
1318 ) values
1319 ( l_align_pterr_id
1320 ,1
1321 ,l_alignment_id
1322 ,p_added_rscs_tbl(i).resource_id
1323 ,p_added_rscs_tbl(i).group_id
1324 ,p_added_rscs_tbl(i).role_code
1325 ,'RS_EMPLOYEE'
1326 ,0
1327 ,G_USER
1328 ,l_sysdate
1329 ,G_USER
1330 ,l_sysdate
1331 ,G_LOGIN
1332 );
1333 end if;
1337 l_pterr_accts_num := 0;
1334 CLOSE c_align_pterr;
1335
1336 --check if existing pterr is already associated with this account
1338
1339 select count(*)
1340 into l_pterr_accts_num
1341 from jtf_tty_pterr_accts
1342 where align_proposed_terr_id = l_align_pterr_id
1343 and align_acct_id = l_align_acct_id
1344 and rownum < 2;
1345
1346 if l_pterr_accts_num < 1
1347 then
1348 --insert into tmp2 values('3. b4 create pterr accts. l_align_pterr_id, l_align_acct_id =', l_align_pterr_id||' '||l_align_acct_id); commit;
1349
1350 insert into JTF_TTY_PTERR_ACCTS
1351 ( align_pterr_acct_id
1352 ,object_version_number
1353 ,align_proposed_terr_id
1354 ,align_acct_id
1355 ,created_by
1356 ,creation_date
1357 ,last_updated_by
1358 ,last_update_date
1359 ,last_update_login
1360 ) values
1361 ( JTF_TTY_PTERR_ACCTS_S.nextval
1362 ,1
1363 ,l_align_pterr_id
1364 ,l_align_acct_id
1365 ,G_USER
1366 ,l_sysdate
1367 ,G_USER
1368 ,l_sysdate
1369 ,G_LOGIN
1370 );
1371
1372 IF ( l_count = 0 )
1373 THEN
1374 l_count := l_count + 1;
1375 align_pterrs_tbl.EXTEND;
1376 align_pterrs_tbl(l_count) := l_align_pterr_id ;
1377 ELSE
1378 l_found := FALSE;
1379
1380 FOR k IN align_pterrs_tbl.FIRST .. align_pterrs_tbl.LAST
1381 LOOP
1382 IF align_pterrs_tbl(k) = l_align_pterr_id
1383 THEN
1384 l_found := TRUE;
1385 exit;
1386 END IF;
1387 END LOOP;
1388 IF ( NOT l_found )
1389 THEN
1390 l_count := l_count + 1;
1391 align_pterrs_tbl.EXTEND;
1392 align_pterrs_tbl(l_count) := l_align_pterr_id ;
1393 END IF;
1394 END IF; -- align_pterrs_tbl IS NULL
1395 end if; -- if pterr_accts_num < 0
1396 END LOOP; --end of p_added_rscs_tbl
1397 END LOOP; --end p_affected_parties_tbl
1398 END IF; -- Adding resources
1399
1400
1401 ---------------------------------------------
1402 -- REMOVING RESOURCES IN SALES TEAM
1403 ---------------------------------------------
1404 IF ((p_affected_parties_tbl is not null) and (p_removed_rscs_tbl is not null) and
1405 (p_affected_parties_tbl.last > 0) and (p_removed_rscs_tbl.last > 0)) THEN
1406
1407 FOR j in p_affected_parties_tbl.first .. p_affected_parties_tbl.last LOOP
1408
1409 OPEN c_align_acct(c_terr_group_account_id => p_affected_parties_tbl(j).terr_group_account_id
1410 , c_alignment_id => l_alignment_id );
1411 FETCH c_align_acct into l_align_acct_id;
1412 CLOSE c_align_acct;
1413
1414 if l_align_acct_id is null then
1415 exit;
1416 end if;
1417
1418 FOR i in p_removed_rscs_tbl.first .. p_removed_rscs_tbl.last LOOP
1419
1420 OPEN c_align_pterr(c_resource_id => p_removed_rscs_tbl(i).resource_id
1421 , c_group_id => p_removed_rscs_tbl(i).group_id
1422 , c_role_code => p_removed_rscs_tbl(i).role_code
1423 , c_alignment_id => l_alignment_id );
1424
1425 l_align_pterr_id := null;
1426 FETCH c_align_pterr into l_align_pterr_id;
1427 CLOSE c_align_pterr;
1428
1429 if l_align_pterr_id is null then
1430 exit;
1431 end if;
1432
1433 delete from JTF_TTY_PTERR_ACCTS
1434 where align_proposed_terr_id = l_align_pterr_id
1435 and align_acct_id = l_align_acct_id;
1436
1437 IF SQL%ROWCOUNT > 0
1438 THEN
1439 IF ( l_count = 0 )
1440 THEN
1441 l_count := l_count + 1;
1442 align_pterrs_tbl.EXTEND;
1443 align_pterrs_tbl(l_count) := l_align_pterr_id ;
1444 ELSE
1445 l_found := FALSE;
1446 FOR k IN align_pterrs_tbl.FIRST .. align_pterrs_tbl.LAST
1447 LOOP
1448 IF align_pterrs_tbl(k) = l_align_pterr_id
1449 THEN
1450 l_found := TRUE;
1451 exit;
1452 END IF;
1453 END LOOP;
1454 IF ( NOT l_found )
1455 THEN
1456 l_count := l_count + 1;
1457 align_pterrs_tbl.EXTEND;
1458 align_pterrs_tbl(l_count) := l_align_pterr_id ;
1459 END IF;
1460 END IF; -- align_pterrs_tbl IS NULL
1461 END IF; -- SQL%ROWCOUNT > 0
1462
1463 END LOOP; --end of p_removed_rscs_tbl
1464
1465 END LOOP; -- end of p_affected_parties_tbl
1466
1467 END IF; -- removing resources
1468
1469 /*-----------------------------------------------
1470 -- BUG 3162073: REMOVING ANY ACCOUNTS IN ALIGNMENT THAT ARE NO LONGER OWNED BY USER.
1471 -- The previous remove will remove salespersons only for changed rows in excel.
1472 -- Since the accounts no longer owned by user do not show up in excel, we have
1473 -- to process them seperately
1474 -----------------------------------------------*/
1475 delete from jtf_tty_align_accts
1476 where alignment_id = l_alignment_id
1477 and terr_group_account_id NOT IN
1478 ( select ga.terr_group_account_id
1479 from jtf_tty_terr_grp_accts ga,
1480 jtf_tty_terr_groups ttygrp
1481 where ttygrp.terr_group_id = ga.terr_group_id
1482 and ttygrp.active_from_date <= sysdate
1483 and ( ttygrp.active_to_date is null
1484 or
1485 ttygrp.active_to_date >= sysdate
1486 )
1487 and ga.terr_group_account_id IN
1488 ( select /*+ NO_MERGE */ narsc.terr_group_account_id
1489 from jtf_tty_named_acct_rsc narsc,
1490 jtf_tty_srch_my_resources_v repdn
1491 where narsc.resource_id = repdn.resource_id
1492 and narsc.rsc_group_id = repdn.group_id
1493 and repdn.current_user_id = l_user_id
1494 )
1495 );
1496
1497 delete from jtf_tty_pterr_accts
1498 where align_proposed_terr_id IN
1499 ( select align_proposed_terr_id
1500 from jtf_tty_align_pterr
1501 where alignment_id = l_alignment_id )
1502 and align_acct_id NOT IN
1503 ( select align_acct_id
1504 from jtf_tty_align_accts
1505 where alignment_id = l_alignment_id )
1506 returning align_proposed_terr_id BULK COLLECT INTO pterrs_changed_tbl;
1507
1508 IF ( pterrs_changed_tbl IS NOT NULL ) AND ( pterrs_changed_tbl.COUNT > 0 )
1509 THEN
1510 FOR i IN pterrs_changed_tbl.FIRST .. pterrs_changed_tbl.LAST
1511 LOOP
1512 IF ( l_count = 0 )
1513 THEN
1514 l_count := l_count + 1;
1515 align_pterrs_tbl.EXTEND;
1516 align_pterrs_tbl(l_count) := pterrs_changed_tbl(i);
1517 ELSE
1518 l_found := FALSE;
1519 FOR k IN align_pterrs_tbl.FIRST .. align_pterrs_tbl.LAST
1520 LOOP
1521 IF ( align_pterrs_tbl(k) = pterrs_changed_tbl(i) )
1522 THEN
1523 l_found := TRUE;
1524 exit;
1525 END IF;
1526 END LOOP;
1527 IF ( NOT l_found )
1528 THEN
1529 l_count := l_count + 1;
1530 align_pterrs_tbl.EXTEND;
1531 align_pterrs_tbl(l_count) := l_align_pterr_id ;
1532 END IF;
1533 END IF; -- align_pterrs_tbl IS NULL
1534 END LOOP; -- end pterrs_changed_tbl LOOP
1535 END IF; -- pterrs_changed_tbl IS NULL
1536
1537 ---------------------------------------------
1538 -- RE-CALCULATING PTERR METRICS FOR CHANGED PTERRS IN THE ALIGNMENT
1539 ---------------------------------------------
1540
1541 IF ( align_pterrs_tbl IS NOT NULL ) AND ( align_pterrs_tbl.COUNT > 0 )
1542 THEN
1543 calculate_align_metrics( l_alignment_id, l_user_id, align_pterrs_tbl, 'N' );
1544 END IF;
1545
1546
1547 --commit processing
1548 --insert into tmp2 values('END B4 COMMIT', 'END B4 COMMIT'); commit;
1549 ---- COMMIT; ---- for bne.c
1550
1551
1552 EXCEPTION
1553 WHEN NO_DATA_FOUND THEN NULL;
1554 --insert into tmp2 values('WHEN NO_DATA_FOUND THEN NULL', 'WHEN NO_DATA_FOUND THEN NULL'); commit;
1555 WHEN OTHERS THEN
1556 --insert into tmp2 values('WHEN OTHERS THEN', 'WHEN OTHERS THEN'); commit;
1557 fnd_message.set_name ('JTF', 'JTF_TTY_ALIGN_UNEXPECTED_ERROR');
1558 x_msg_data := fnd_message.get();
1559 fnd_message.set_name ('JTF', x_msg_data);
1560
1561 end;
1562
1563
1564 END JTF_TTY_ALIGN_WEBADI_INT_PKG;
1565