[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_NA_TERRGP
Source
1 PACKAGE BODY JTF_TTY_NA_TERRGP AS
2 /* $Header: jtfttgpb.pls 120.6 2006/07/07 20:55:51 mhtran ship $ */
3 -- Start of Comments
4 -- PURPOSE
5 -- Custom Assignment API
6 --
7 -- NOTES
8 -- ORACLE INTERNAL USE ONLY: NOT for customer use
9 --
10 -- HISTORY
11 -- 03/18/02 SGKUMAR Created
12 -- 03/20/02 SGKUMAR Created procedure insert_qualifiers
13 -- 03/20/02 SGKUMAR Created procedure set_winners
14 -- 12/30/02 sbehera set the assignment_flag='N' at the create time
15 -- 01/06/03 JDOCHERT FIX FOR BUG#2735965
16 -- 01/06/03 JDOCHERT FIX FOR BUG#2736765
17 -- 01/08/03 JDOCHERT FIX FOR BUG#2741455
18 -- 01/23/03 SGKUMAR FIX FOR BUG#2764268 (create_tgp_account modified)
19 -- 02/27/03 SGKUMAR for new tg named account, setting assigned_to_direct_flag 'N"
20 -- 04/01/03 SGKUMAR FIX FOR BUG#2872451 (assign_account modified)
21 -- checking for terr gp acct for the owner
22 -- 04/02/03 SGKUMAR FIX FOR BUG#2870683 (create_tgp_named_acct modified)
23 -- Performance Fix. (count(*) removed)
24 -- 04/07/03 SGKUMAR FIX FOR BUG#2821900 (check_hierachy added)
25 -- 07/08/03 SGKUMAR Create procedure log_event
26 -- for auditing.Modified delete_terrgp to invoke it
27 -- 07/14/03 SGKUMAR modified sum_rm_bin procedure to not use
28 -- the denorm table. Also removed code from other proce
29 -- dures not to summarize acct sum table and touch deno
30 -- rm table.
31 -- 10/21/03 SGKUMAR Modified sum_rm_bin procedure to count the number
32 -- of accounts by group also (added narsc.rsc_group_id
33 -- = repdn.group_id) and also to get sales hierarchy
34 -- by group not just by resource.
35 -- 01/15/04 SGKUMAR Modified sum_rm_bin procedure to count the number
36 -- of accounts for a resource who is a manager and a
37 -- rep of the same group or manager of a group and a
38 -- member in the parent group
39 -- End of Comments
40 -- End of Comments
41 ----
42
43
44 FUNCTION get_site_type_code( p_party_id NUMBER ) RETURN VARCHAR2
45 IS
46 l_site_type_code VARCHAR2(30);
47 l_chk_done VARCHAR2(1) := 'N' ;
48
49 BEGIN
50
51 hz_common_pub.disable_cont_source_security;
52
53 -- check for global ultimate
54
55 BEGIN
56
57 SELECT 'Y'
58 INTO l_chk_done
59 FROM DUAL
60 WHERE EXISTS ( SELECT 'Y'
61 FROM hz_relationships hzr
62 WHERE hzr.subject_table_name = 'HZ_PARTIES'
63 AND hzr.object_table_name = 'HZ_PARTIES'
64 AND hzr.relationship_type = 'GLOBAL_ULTIMATE'
65 AND hzr.relationship_code = 'GLOBAL_ULTIMATE_OF'
66 AND hzr.status = 'A'
67 AND SYSDATE BETWEEN hzr.start_date AND NVL(hzr.end_date, SYSDATE)
68 AND hzr.subject_id = p_party_id );
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN NULL;
71 END;
72
73 IF l_chk_done = 'Y'
74 THEN
75 l_site_type_code := 'GU' ;
76 RETURN l_site_type_code;
77 END IF;
78
79 -- check for domestic ultimate
80
81 BEGIN
82 SELECT 'Y'
83 INTO l_chk_done
84 FROM DUAL
85 WHERE EXISTS ( SELECT 'Y'
86 FROM hz_relationships hzr
87 WHERE hzr.subject_table_name = 'HZ_PARTIES'
88 AND hzr.object_table_name = 'HZ_PARTIES'
89 AND hzr.relationship_type = 'DOMESTIC_ULTIMATE'
90 AND hzr.relationship_code = 'DOMESTIC_ULTIMATE_OF'
91 AND hzr.status = 'A'
92 AND SYSDATE BETWEEN hzr.start_date AND NVL(hzr.end_date, SYSDATE)
93 AND hzr.subject_id = p_party_id );
94 EXCEPTION
95 WHEN NO_DATA_FOUND THEN NULL;
96 END;
97
98 IF l_chk_done = 'Y'
99 THEN
100 l_site_type_code := 'DU' ;
101 RETURN l_site_type_code;
102 END IF;
103
104 BEGIN
105
106 SELECT lkp.lookup_code
107 INTO l_site_type_code
108 FROM fnd_lookups lkp,
109 hz_parties hzp
110 WHERE lkp.lookup_type = 'JTF_TTY_SITE_TYPE_CODE'
111 AND hzp.hq_branch_ind = lkp.lookup_code
112 AND hzp.party_id = p_party_id;
113
114
115 EXCEPTION
116 WHEN NO_DATA_FOUND THEN
117 l_site_type_code := 'UN';
118
119 END;
120
121 RETURN( l_site_type_code);
122
123 EXCEPTION
124
125 WHEN OTHERS THEN
126 NULL;
127 --dbms_output.put_line( substr(sqlerrm, 1, 200) );
128
129 END get_site_type_code;
130
131 PROCEDURE get_site_type(p_party_id IN NUMBER,
132 x_party_type OUT NOCOPY VARCHAR2)
133 AS
134 site_type_code VARCHAR2(30);
135 BEGIN
136
137 site_type_code := get_site_type_code(p_party_id);
138
139 SELECT lkp.meaning
140 INTO x_party_type
141 FROM fnd_lookups lkp
142 WHERE lkp.lookup_type = 'JTF_TTY_SITE_TYPE_CODE'
143 AND lkp.lookup_code = site_type_code;
144
145 --
146 -- 01/06/03: JDOCHERT: FIX FOR BUG#2735965
147 --
148 EXCEPTION
149 WHEN NO_DATA_FOUND THEN
150 NULL;
151
152 END get_site_type;
153
154
155 /* This procedure accepts a string of Ids and populates a pl/sql table with the Ids.
156 */
157 PROCEDURE buildTable(p_id_str IN VARCHAR2,
158 x_id_table OUT NOCOPY jtf_terr_number_list)
159 IS
160 l_start integer;
161 idx integer;
162 foundStrPos integer;
163 l_err_msg VARCHAR2(2000);
164 BEGIN
165 l_start:=1;
166 foundStrPos:=1;
167 idx:=1;
168
169 x_id_table := jtf_terr_number_list();
170 WHILE (foundStrPos>0)
171 LOOP
172 foundStrPos := INSTR(p_id_str,',', l_start);
173 IF foundStrPos >0 THEN
174 x_id_table.extend();
175 x_id_table(idx) := to_number(substr(p_id_str, l_start, (foundStrPos-l_start)));
176 idx := idx + 1;
177 l_start :=foundStrPos+1;
178 END IF;
179 END LOOP;
180 x_id_table.extend();
181 x_id_table(idx) := to_number(substr(p_id_str, l_start));
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 l_err_msg := SQLCODE || ' : ' || substr(SQLERRM, 1, 1950);
186 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
187 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
188 'jtf.plsql.JTF_TTY_NA_TERRGP.buildTable',
189 l_err_msg);
190 END IF;
191 RAISE;
192 END buildTable;
193
194
195 PROCEDURE delete_bulk_TGA(p_tga_id_str IN VARCHAR2,
196 p_terr_gp_id_str IN VARCHAR2,
197 p_named_acct_id_str IN VARCHAR2,
198 p_change_type IN VARCHAR2,
199 x_return_status OUT NOCOPY VARCHAR2,
200 x_msg_count OUT NOCOPY NUMBER,
201 x_msg_data OUT NOCOPY VARCHAR2)
202 IS
203 head Number ;
204 tail Number ;
205 i Number ;
206 idx Number ;
207
208 l_terrGrpId_tbl jtf_terr_number_list;
209 l_grpAcctId_tbl jtf_terr_number_list;
210 l_acctId_tbl jtf_terr_number_list;
211
212 BEGIN
213
214 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
215 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
216 'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA',
217 'Start of the procedure JTF_TTY_NA_TERRGP.delete_bulk_TGA');
218 END IF;
219
220 x_return_status := FND_API.G_RET_STS_SUCCESS;
221 l_terrGrpId_tbl := jtf_terr_number_list();
222 l_grpAcctId_tbl := jtf_terr_number_list();
223 l_acctId_tbl := jtf_terr_number_list();
224
225
226 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
227 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
228 'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA',
229 'Building PL/SQL tables from Input Strings');
230 END IF;
231
232 buildTable(p_tga_id_str, l_grpAcctId_tbl);
233 buildTable(p_terr_gp_id_str, l_terrGrpId_tbl);
234 buildTable(p_named_acct_id_str, l_acctId_tbl);
235
236 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
237 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
238 'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA',
239 'Deleting from JTF_TTY... tables');
240 END IF;
241
242 -- Delete Named Account Resources
243 FORALL idx in l_grpAcctId_tbl.FIRST .. l_grpAcctId_tbl.LAST
244 DELETE from jtf_tty_named_acct_rsc j WHERE j.TERR_GROUP_ACCOUNT_ID = l_grpAcctId_tbl(idx);
245
246 -- Delete Terr Group Accounts
247 FORALL idx in l_grpAcctId_tbl.FIRST .. l_grpAcctId_tbl.LAST
248 DELETE from JTF_TTY_TERR_GRP_ACCTS j WHERE j.TERR_GROUP_ACCOUNT_ID = l_grpAcctId_tbl(idx);
249
250 -- Delete Named Accounts
251 FORALL idx in l_acctId_tbl.FIRST .. l_acctId_tbl.LAST
252 DELETE from JTF_TTY_NAMED_ACCTS A
253 WHERE A.named_account_id = l_acctId_tbl(idx)
254 AND NOT EXISTS
255 (SELECT 'Y'
256 FROM JTF_TTY_TERR_GRP_ACCTS tga
257 WHERE tga.named_account_id = A.named_account_id);
258
259 -- Delete Named Account Qual Maps
260 FORALL idx in l_acctId_tbl.FIRST .. l_acctId_tbl.LAST
261 DELETE from JTF_TTY_ACCT_QUAL_MAPS AQM
262 WHERE AQM.NAMED_ACCOUNT_ID = l_acctId_tbl(idx)
263 AND NOT EXISTS ( SELECT 'x'
264 FROM JTF_TTY_NAMED_ACCTS a
265 WHERE a.named_account_id = AQM.named_account_id);
266
267
268 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
269 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
270 'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA',
271 'Calling procedure JTF_TTY_GEN_TERR_PVT.delete_bulk_TGA');
272 END IF;
273
274 -- Deleting Territories data from the JTF_TERR.. tables
275 JTF_TTY_GEN_TERR_PVT.delete_bulk_TGA(l_terrGrpId_tbl,
276 l_grpAcctId_tbl,
277 p_change_type,
278 x_return_status,
279 x_msg_count,
280 x_msg_data );
281
282
283 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
284 -- debug message
285 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
286 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
287 'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA',
288 'JTF_TTY_GEN_TERR_PVT.delete_bulk_TGA API has failed');
289 END IF;
290
291 RAISE FND_API.G_EXC_ERROR;
292 END IF;
293
294 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
295 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
296 'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA',
297 'Returning from JTF_TTY_GEN_TERR_PVT.delete_bulk_TGA');
298 END IF;
299
300 COMMIT;
301
302 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
303 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
304 'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA',
305 'End of the procedure JTF_TTY_NA_TERRGP.delete_bulk_TGA');
306 END IF;
307
308 EXCEPTION
309 WHEN NO_DATA_FOUND THEN
310 NULL;
311 WHEN OTHERS THEN
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
313 x_msg_data := SQLCODE || ' : ' || SQLERRM;
314 x_msg_count := 1;
315 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
316 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
317 'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA.OTHERS',
318 substr(x_msg_data, 1, 4000));
319 END IF;
320
321 END delete_bulk_TGA;
322
323 PROCEDURE delete_terrgp(p_terr_gp_id IN NUMBER)
324 AS
325 p_user_id NUMBER;
326 BEGIN
327
328 p_user_id := fnd_global.user_id;
329 /* delete from the named acct sum only for affect resources */
330 /*
331 DELETE from jtf_tty_rsc_acct_summ j
332 WHERE j.RESOURCE_ID in (SELECT RESOURCE_ID
333 from jtf_tty_acct_rsc_dn j, jtf_tty_terr_grp_accts tga1
334 WHERE j.TERR_GROUP_ACCOUNT_ID = tga1.TERR_GROUP_ACCOUNT_ID
335 AND tga1.TERR_GROUP_ID = p_terr_gp_id)
336 AND j.RSC_GROUP_ID in (SELECT RSC_GROUP_ID
337 from jtf_tty_acct_rsc_dn j, jtf_tty_terr_grp_accts tga2
338 WHERE j.TERR_GROUP_ACCOUNT_ID = tga2.TERR_GROUP_ACCOUNT_ID
339 AND tga2.TERR_GROUP_ID = p_terr_gp_id);
340 */
341 /* sum the rsc acct sum table for the deletes resources only */
342 /* and for na's for different terr gp from the deleted one */
343 /*
344 insert into jtf_tty_rsc_acct_summ(
345 RESOURCE_ACCT_SUMM_ID,
346 OBJECT_VERSION_NUMBER,
347 RESOURCE_ID,
348 RSC_GROUP_ID,
349 RSC_RESOURCE_TYPE,
350 SITE_TYPE_CODE,
351 NUMBER_ACCOUNTS,
352 CREATED_BY,
353 CREATION_DATE,
354 LAST_UPDATED_BY,
355 LAST_UPDATE_DATE)
356 (select jtf_tty_rsc_acct_summ_s.nextval,
357 1,
358 ilv.RESOURCE_ID,
359 ilv.RSC_GROUP_ID,
360 'RS_EMPLOYEE',
361 ilv.site_type_code,
362 ilv.num_accts,
363 p_user_id,
364 sysdate,
365 p_user_id,
366 sysdate
367 FROM
368 (select narsc.RESOURCE_ID,
369 narsc.RSC_GROUP_ID,
370 'RS_EMPLOYEE',
371 na.site_type_code,
372 count(na.NAMED_ACCOUNT_ID) num_accts
373 from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
374 jtf_tty_terr_grp_accts tga
375 where na.named_account_id = tga.named_account_id
376 and narsc.resource_id in
377 (SELECT RESOURCE_ID
378 from jtf_tty_acct_rsc_dn j, jtf_tty_terr_grp_accts tga1
379 WHERE j.TERR_GROUP_ACCOUNT_ID = tga1.TERR_GROUP_ACCOUNT_ID
380 AND tga1.TERR_GROUP_ID = p_terr_gp_id)
381 and narsc.rsc_group_id in
382 (SELECT rsc_group_id
383 from jtf_tty_acct_rsc_dn j, jtf_tty_terr_grp_accts tga2
384 WHERE j.TERR_GROUP_ACCOUNT_ID = tga2.TERR_GROUP_ACCOUNT_ID
385 AND tga2.TERR_GROUP_ID = p_terr_gp_id)
386 and tga.terr_group_id <> p_terr_gp_id
387 and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
388 group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
389 */
390 /* delete the existing assignments */
391
392 DELETE FROM jtf_tty_named_acct_rsc j
393 WHERE j.TERR_GROUP_ACCOUNT_ID IN
394 (SELECT TERR_GROUP_ACCOUNT_ID
395 FROM JTF_TTY_TERR_GRP_ACCTS
396 WHERE TERR_GROUP_ID = p_terr_gp_id);
397 /*
398 DELETE from jtf_tty_acct_rsc_dn j
399 WHERE j.TERR_GROUP_ACCOUNT_ID in
400 (SELECT TERR_GROUP_ACCOUNT_ID
401 FROM JTF_TTY_TERR_GRP_ACCTS
402 WHERE TERR_GROUP_ID = p_terr_gp_id);
403 */
404
405 DELETE FROM JTF_TTY_TERR_GRP_ACCTS
406 WHERE TERR_GROUP_ID = p_terr_gp_id;
407
408 DELETE FROM JTF_TTY_NAMED_ACCTS na
409 WHERE na.NAMED_ACCOUNT_ID NOT IN
410 (SELECT named_account_id FROM JTF_TTY_TERR_GRP_ACCTS);
411 /* delete the na mappings if a na is deleted or no reference to it exists */
412
413 DELETE FROM JTF_TTY_ACCT_QUAL_MAPS nam
414 WHERE nam.NAMED_ACCOUNT_ID NOT IN
415 (SELECT named_account_id FROM JTF_TTY_NAMED_ACCTS);
416
417 /* delete all the terr gp owners, access and product */
418 DELETE FROM jtf_tty_terr_grp_owners
419 WHERE terr_group_id = p_terr_gp_id;
420
421 DELETE FROM jtf_tty_role_prod_int
422 WHERE terr_group_role_id IN
423 (SELECT terr_group_role_id FROM jtf_tty_terr_grp_roles
424 WHERE terr_group_id = p_terr_gp_id);
425
426
427 DELETE FROM jtf_tty_role_access
428 WHERE terr_group_role_id IN
429 (SELECT terr_group_role_id FROM jtf_tty_terr_grp_roles
430 WHERE terr_group_id = p_terr_gp_id);
431
432 DELETE FROM jtf_tty_terr_grp_roles
433 WHERE terr_group_id = p_terr_gp_id;
434
435 /* finally delete the terr gp itself */
436
437 DELETE FROM jtf_tty_terr_groups
438 WHERE terr_group_id = p_terr_gp_id;
439
440 /* GSST decom. by SHLI */
441 -- log_event(p_terr_gp_id, 'DELETE', 'Delete Territory Group', 'TG', fnd_global.user_id);
442 COMMIT;
443 END delete_terrgp;
444
445 PROCEDURE check_hierarchy(x_hierarchy_status OUT NOCOPY VARCHAR2,
446 p_group_id1 IN VARCHAR2,
447 p_group_id2 IN VARCHAR2)
448 AS
449 hierarchy_flag VARCHAR2(25);
450 BEGIN
451 SELECT 'SAME'
452 INTO x_hierarchy_status
453 FROM dual
454 WHERE (p_group_id1 IN
455 (SELECT parent_group_id FROM jtf_rs_groups_denorm
456 WHERE group_id = p_group_id2)
457 OR p_group_id1 IN
458 (SELECT group_id FROM jtf_rs_groups_denorm
459 WHERE parent_group_id = p_group_id2))
460 AND ROWNUM < 2;
461 EXCEPTION
462 WHEN NO_DATA_FOUND THEN
463 x_hierarchy_status := 'DIFFERENT';
464 END check_hierarchy;
465
466
467
468 PROCEDURE process_assign_accts(p_terr_gp_id IN NUMBER,
469 p_DownerRsc IN VARCHAR2,
470 p_NownerRsc IN VARCHAR2,
471 p_DownerGrp IN VARCHAR2,
472 p_NownerGrp IN VARCHAR2,
473 p_DownerRole IN VARCHAR2,
474 p_NownerRole IN VARCHAR2
475 )
476 AS
477 managed_group_id NUMBER;
478 i NUMBER;
479 p_user_id NUMBER;
480 x_group_id NUMBER;
481 child_group_id NUMBER;
482 result VARCHAR2(30);
483 idx NUMBER;
484 new_idx NUMBER;
485 del_idx NUMBER;
486 replace_idx NUMBER;
487 indx NUMBER;
488 FOUND NUMBER;
489 l_resource_id NUMBER;
490 l_group_id NUMBER;
491 match_found_flag VARCHAR2(1);
492
493 NewOwnerRsc mytabletype;
494 NewOwnerGrp mytabletype;
495 NewOwnerRole mytabletypev;
496
497 DelOwnerRsc mytabletype;
498 DelOwnerGrp mytabletype;
499 DelOwnerRole mytabletypev;
500
501 DelCandidateRsc mytabletype;
502 DelCandidateGrp mytabletype;
503 DelCandidateRole mytabletypev;
504
505 NewCandidateRsc mytabletype;
506 NewCandidateGrp mytabletype;
507 NewCandidateRole mytabletypev;
508
509 RplOwnerFromRsc mytabletype;
510 RplOwnerFromGrp mytabletype;
511 RplOwnerToRsc mytabletype;
512 RplOwnerToGrp mytabletype;
513 RplOwnerToRole mytabletypev;
514
515 terr_grp_id mytabletype;
516 terrgrpid_nodup mytabletype;
517 /* CURSOR NAHasOwnerAsParent
518 IS
519 SELECT tga.terr_group_account_id tga_id,
520 NAR.assigned_flag aflag,
521 tgo.rsc_group_id parentgrpid
522 FROM JTF_TTY_NAMED_ACCT_RSC NAR,
523 JTF_TTY_TERR_GRP_ACCTS TGA,
524 jtf_tty_terr_grp_owners tgo,
525 jtf_rs_groups_denorm gd
526 WHERE NAR.terr_group_account_id = TGA.terr_group_account_id
527 AND TGA.TERR_GROUP_ID = p_terr_gp_id
528 AND NAR.rsc_resource_type = 'RS_EMPLOYEE'
529 AND gd.group_id = NAR.RSC_GROUP_ID
530 AND gd.parent_group_id = tgo.rsc_group_id
531 AND tgo.rsc_resource_type = 'RS_EMPLOYEE'
532 AND tgo.TERR_GROUP_ID = p_terr_gp_id
533 AND sysdate BETWEEN gd.start_date_active AND nvl(gd.end_date_active, sysdate);
534 */
535 /* For each assignment */
536 /* check if a TG owner's rsc group a parent of the rep's rsc group*/
537 /* only one valid parentgrpid for each assignment ?*/
538
539 /*
540 CURSOR NAHasNoOwnerAsParent
541 IS
542 SELECT tga.terr_group_account_id tga_id,
543 NAR.rsc_group_id currentgrpid
544 FROM JTF_TTY_NAMED_ACCT_RSC NAR,
545 JTF_TTY_TERR_GRP_ACCTS TGA
546 WHERE NAR.terr_group_account_id = TGA.terr_group_account_id
547 AND TGA.TERR_GROUP_ID = p_terr_gp_id
548 AND NAR.rsc_resource_type = 'RS_EMPLOYEE'
549 AND NOT EXISTS
550 (
551 SELECT NULL
552 FROM jtf_tty_terr_grp_owners tgo,
553 jtf_rs_groups_denorm gd
554 WHERE gd.group_id = NAR.RSC_GROUP_ID
555 AND gd.parent_group_id = tgo.rsc_group_id
556 AND tgo.rsc_resource_type = 'RS_EMPLOYEE'
557 AND tgo.TERR_GROUP_ID = p_terr_gp_id
558 AND sysdate BETWEEN gd.start_date_active AND nvl(gd.end_date_active, sysdate)
559 );
560
561 CURSOR TGOwner
562 IS
563 SELECT rsc_group_id,
564 resource_id,
565 rsc_role_code
566 FROM jtf_tty_terr_grp_owners tgo
567 WHERE terr_group_id = p_terr_gp_id
568 AND rsc_resource_type = 'RS_EMPLOYEE';
569 */
570
571 BEGIN
572
573 p_user_id := Fnd_Global.user_id;
574
575 NewOwnerRsc := mytabletype();
576 NewOwnerGrp := mytabletype();
577 NewOwnerRole := mytabletypev();
578
579 DelOwnerRsc := mytabletype();
580 DelOwnerGrp := mytabletype();
581 DelOwnerRole := mytabletypev();
582
583 DelCandidateRsc := mytabletype();
584 DelCandidateGrp := mytabletype();
585 DelCandidateRole := mytabletypev();
586
587 NewCandidateRsc := mytabletype();
588 NewCandidateGrp := mytabletype();
589 NewCandidateRole := mytabletypev();
590
591 RplOwnerFromRsc := mytabletype();
592 RplOwnerFromGrp := mytabletype();
593 RplOwnerToRsc := mytabletype();
594 RplOwnerToGrp := mytabletype();
595 RplOwnerToRole := mytabletypev();
596
597 /* Build Candidate table*/
598
599
600 IF p_DownerRsc IS NOT NULL THEN generateNumList(p_DownerRsc, DelCandidateRsc); END IF;
601 IF p_DownerGrp IS NOT NULL THEN generateNumList(p_DownerGrp, DelCandidateGrp); END IF;
602 IF p_DownerRole IS NOT NULL THEN generateStrList(p_DownerRole, DelCandidateRole); END IF;
603
604 IF p_NownerRsc IS NOT NULL THEN generateNumList(p_NownerRsc, NewCandidateRsc); END IF;
605 IF p_NownerGrp IS NOT NULL THEN generateNumList(p_NownerGrp, NewCandidateGrp); END IF;
606 IF p_NownerRole IS NOT NULL THEN generateStrList(p_NownerRole, NewCandidateRole); END IF;
607
608
609 /* Build Del, replace table */
610
611 replace_idx :=0;
612 del_idx :=0;
613 FOUND :=0;
614
615 FOR idx IN DelCandidateGrp.FIRST .. DelCandidateGrp.LAST
616 LOOP
617 indx := 1; -- 1 .. NewCandidateGrp.count;
618 i := NewCandidateGrp.COUNT;
619 WHILE (FOUND=0 AND indx<= NewCandidateGrp.COUNT)
620 LOOP
621
622 BEGIN
623 /* if the new and old owner are in a same group */
624
625 IF DelCandidateGrp(idx) = NewCandidateGrp(indx) THEN
626 RplOwnerFromRsc.extend();
627 RplOwnerFromGrp.extend();
628 RplOwnerToRsc.extend();
629 RplOwnerToGrp.extend();
630 RplOwnerToRole.extend();
631
632 replace_idx := replace_idx+1;
633
634 RplOwnerFromRsc(replace_idx) :=DelCandidateRsc(idx);
635 RplOwnerFromGrp(replace_idx) :=DelCandidateGrp(idx);
636 RplOwnerToRsc(replace_idx) :=NewCandidateRsc(indx);
637 RplOwnerToGrp(replace_idx) :=NewCandidateGrp(indx);
638 RplOwnerToRole(replace_idx) :=NewCandidateRole(indx);
639
640 FOUND := 1;
641 ELSE /* child or parent */
642 result := NULL;
643
644 -- JRADHAKR: Removed the active_flag clause because
645 -- in 11.5.6 env there is no active_flag column
646 -- Fix for bug 4313953
647
648 SELECT 'PARENT_CHILD' INTO result
649 FROM jtf_rs_groups_denorm
650 WHERE SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE)
651 AND
652 ( group_id = DelCandidateGrp(idx)
653 AND parent_group_id = NewCandidateGrp(indx)
654 )
655 OR
656 ( parent_group_id = DelCandidateGrp(idx)
657 AND group_id = NewCandidateGrp(indx)
658 );
659
660 IF result IS NOT NULL THEN
661 RplOwnerFromRsc.extend();
662 RplOwnerFromGrp.extend();
663 RplOwnerToRsc.extend();
664 RplOwnerToGrp.extend();
665 RplOwnerToRole.extend();
666
667 replace_idx := replace_idx+1;
668
669 RplOwnerFromRsc(replace_idx) :=DelCandidateRsc(idx);
670 RplOwnerFromGrp(replace_idx) :=DelCandidateGrp(idx);
671 RplOwnerToRsc(replace_idx) :=NewCandidateRsc(indx);
672 RplOwnerToGrp(replace_idx) :=NewCandidateGrp(indx);
673 RplOwnerToRole(replace_idx) :=NewCandidateRole(indx);
674
675 FOUND := 1; -- done with new owner loop
676 END IF;
677
678 END IF; --of DelCandidateGrp(idx) = NewCandidateGrp(indx)
679
680 indx := indx +1;
681
682 EXCEPTION
683 WHEN NO_DATA_FOUND THEN
684 -- NO parent or child relation found
685 indx := indx +1;
686
687 END; -- of BEGIN
688 END LOOP; -- of while new owner candidate
689
690
691 IF FOUND=0 THEN -- not equal, parent or child found
692
693 indx :=1;
694 WHILE (FOUND=0 AND indx<= NewCandidateGrp.COUNT)
695 LOOP
696 BEGIN
697 SELECT 'Y' INTO result
698 FROM jtf_tty_named_acct_rsc narsc,
699 jtf_tty_terr_grp_accts tga,
700 jtf_rs_role_relations rlt ,
701 jtf_rs_group_members grpmem ,
702 jtf_rs_groups_denorm grpdn
703 WHERE narsc.terr_group_account_id = tga.terr_group_account_id
704 AND tga.terr_group_id = DelCandidateGrp(idx)
705 AND narsc.resource_id = grpmem.resource_id
706 AND narsc.rsc_group_id = grpmem.group_id
707 AND grpmem.group_member_id = rlt.role_resource_id
708 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
709 AND rlt.delete_flag = 'N'
710 AND SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active,SYSDATE+1)
711 AND grpmem.delete_flag = 'N'
712 AND grpmem.group_id = grpdn.group_id
713 AND SYSDATE BETWEEN grpdn.start_date_active AND NVL(grpdn.end_date_active, SYSDATE+1)
714 AND grpdn.parent_group_id = NewCandidateGrp(indx)
715 AND EXISTS ( SELECT 'Y'
716 FROM jtf_rs_groups_denorm grpdn1
717 WHERE narsc.rsc_group_id = grpdn1.group_id
718 AND grpdn1.parent_group_id = DelCandidateGrp(idx)
719 )
720 AND ROWNUM < 2;
721
722 IF result IS NOT NULL THEN
723
724 RplOwnerFromRsc.extend();
725 RplOwnerFromGrp.extend();
726 RplOwnerToRsc.extend();
727 RplOwnerToGrp.extend();
728 RplOwnerToRole.extend();
729
730 replace_idx := replace_idx+1;
731
732 RplOwnerFromRsc(replace_idx) :=DelCandidateRsc(idx);
733 RplOwnerFromGrp(replace_idx) :=DelCandidateGrp(idx);
734 RplOwnerToRsc(replace_idx) :=NewCandidateRsc(indx);
735 RplOwnerToGrp(replace_idx) :=NewCandidateGrp(indx);
736 RplOwnerToRole(replace_idx) :=NewCandidateRole(indx);
737
738 FOUND := 1; -- done with new owner loop
739 ELSE indx :=indx +1;
740
741 END IF;
742
743 EXCEPTION
744 WHEN NO_DATA_FOUND THEN
745 indx :=indx +1;
746
747 END;
748 END LOOP;
749
750 IF FOUND=0 THEN
751
752 DelOwnerRsc.extend();
753 DelOwnerGrp.extend();
754 DelOwnerRole.extend();
755
756 del_idx := del_idx+1;
757
758 DelOwnerRsc(del_idx) := DelCandidateRsc(idx);
759 DelOwnerGrp(del_idx) := DelCandidateGRP(idx);
760 DelOwnerRole(del_idx) := DelCandidateRole(idx);
761 END IF;
762 END IF; --found=0 ;
763
764 END LOOP; -- delete owner candidate
765
766
767 /* Build new owner table */
768 new_idx :=0;
769 IF NewCandidateGrp.COUNT>0 THEN
770 FOR idx IN NewCandidateGrp.FIRST .. NewCandidateGrp.LAST
771 LOOP
772 indx :=1;
773 FOUND :=0;
774
775 WHILE (FOUND=0 AND indx<= RplOwnerToGrp.COUNT)
776 LOOP
777 IF NewCandidateGrp(idx) = RplOwnerToGrp(indx) AND
778 NewCandidateRsc(idx) = RplOwnerToRsc(indx) THEN
779 FOUND :=1;
780 ELSE indx := indx+1;
781 END IF;
782 END LOOP;
783
784 IF FOUND = 0 THEN
785 NewOwnerRsc.extend();
786 NewOwnerGrp.extend();
787 NewOwnerRole.extend();
788
789 new_idx := new_idx+1;
790
791 NewOwnerRsc(new_idx) := NewCandidateRsc(idx);
792 NewOwnerGrp(new_idx) := NewCandidateGRP(idx);
793 NewOwnerRole(new_idx) := NewCandidateRole(idx);
794
795 END IF;
796
797 END LOOP; --FOR idx in NewCandidateGrp
798 END IF; -- NewCandidateGrp.count>0
799
800
801
802 /*-------------------------------------------------------------------------*/
803 /*--------------- Process delete, new and replace owners ------------------*/
804 /*-------------------------------------------------------------------------*/
805
806 /* New owners: assign all accounts to new owners*/
807 IF NewOwnerRsc.COUNT>0 THEN
808 FOR idx IN NewOwnerRsc.FIRST .. NewOwnerRsc.LAST
809 LOOP
810 assign_accts( p_terr_gp_id,
811 NewOwnerRsc(idx),
812 NewOwnerGrp(idx),
813 NewOwnerRole(idx),
814 'NO', -- not in use
815 p_user_id);
816 END LOOP;
817 END IF;
818
819
820 /* deleted owners: delete all account assignments from deleted owner's hierarchy*/
821 IF DelOwnerRsc.COUNT>0 THEN
822
823 FOR idx IN DelOwnerRsc.FIRST .. DelOwnerRsc.LAST
824 LOOP
825 delete_assign_accts(p_terr_gp_id,
826 DelOwnerRsc(idx),
827 DelOwnerGrp(idx),
828 DelOwnerRole(idx));
829 END LOOP;
830 END IF;
831
832 /* replaced owners */
833 IF RplOwnerFromRsc.COUNT>0 THEN
834
835 FOR idx IN RplOwnerFromRsc.FIRST .. RplOwnerFromRsc.LAST
836 LOOP
837 /*update all account assignments that are owned by deleted manager
838 id ( by resource_id and group_id ) and assigned_flag = 'N'
839 with replaced owner */
840
841 UPDATE jtf_tty_named_acct_rsc narsc
842 SET resource_id = RplOwnerToRsc(idx),
843 rsc_group_id = RplOwnerToGrp(idx),
844 rsc_role_code = RplOwnerToRole(idx)
845 WHERE narsc.resource_id = RplOwnerFromRsc(idx)
846 AND narsc.rsc_group_id = RplOwnerFromGrp(idx)
847 AND narsc.assigned_flag = 'N';
848
849
850
851 /* delete all the account assignments that roll-up to deleted owner
852 but not the new owner return terr_group_account_id
853 and Assign all the terr_group_account_id to new owner */
854
855
856 terr_grp_id := mytabletype();
857 terrgrpid_nodup := mytabletype();
858
859 -- SOLIN, bug4943336, performance tuning
860 DELETE FROM jtf_tty_named_acct_rsc narsc
861 WHERE narsc.terr_group_account_id
862 IN ( SELECT terr_group_account_id
863 FROM jtf_tty_terr_grp_accts,
864 jtf_rs_role_relations rlt ,
865 jtf_rs_group_members grpmem ,
866 jtf_rs_groups_denorm grpdn
867 WHERE terr_group_id = p_terr_gp_id
868 AND grpmem.resource_id = narsc.resource_id
869 AND grpmem.group_id = narsc.rsc_group_id
870 AND grpmem.group_member_id = rlt.role_resource_id
871 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
872 AND grpmem.group_id = grpdn.group_id
873 AND grpdn.parent_group_id = rplownerfromgrp(idx)
874 AND NOT EXISTS
875 ( select 'Y'
876 from jtf_rs_groups_denorm grpdn1
877 where grpmem.group_id = grpdn1.group_id
878 and grpdn1.parent_group_id = rplownertogrp(idx)
879 and sysdate between grpdn1.start_date_active
880 and nvl(grpdn1.end_date_active,sysdate+1)
881 )
882 )
883 RETURNING terr_group_account_id BULK COLLECT INTO terr_grp_id;
884
885
886 IF terr_grp_id.COUNT>0 THEN
887
888 FOR idx IN terr_grp_id.FIRST .. terr_grp_id.LAST
889 LOOP
890 indx := idx+1;
891 WHILE indx <=terr_grp_id.COUNT
892 LOOP
893 IF terr_grp_id(idx) IS NOT NULL AND terr_grp_id(indx) IS NOT NULL
894 AND terr_grp_id(idx) = terr_grp_id(indx) THEN
895 terr_grp_id(indx) := NULL;
896 END IF;
897
898 indx :=indx +1;
899 END LOOP;
900 END LOOP;
901
902
903 indx :=0;
904 FOR idx IN terr_grp_id.FIRST .. terr_grp_id.LAST
905 LOOP
906 IF terr_grp_id(idx) IS NOT NULL THEN
907 terrgrpid_nodup.extend();
908 indx := indx+1;
909 terrgrpid_nodup(indx) := terr_grp_id(idx);
910 END IF;
911 END LOOP;
912 END IF;
913
914 IF terrgrpid_nodup.COUNT>0 THEN
915 FORALL i IN terrgrpid_nodup.FIRST .. terrgrpid_nodup.LAST
916 INSERT INTO jtf_tty_named_acct_rsc(
917 ACCOUNT_RESOURCE_ID,
918 OBJECT_VERSION_NUMBER,
919 TERR_GROUP_ACCOUNT_ID,
920 RESOURCE_ID,
921 RSC_GROUP_ID,
922 RSC_ROLE_CODE,
923 ASSIGNED_FLAG,
924 RSC_RESOURCE_TYPE,
925 CREATED_BY,
926 CREATION_DATE,
927 LAST_UPDATED_BY,
928 LAST_UPDATE_DATE)
929 ( SELECT jtf_tty_named_acct_rsc_s.NEXTVAL,
930 1,
931 terrgrpid_nodup(i),
932 RplOwnerToRsc(idx),
933 RplOwnerToGrp(idx),
934 RplOwnerToRole(idx),
935 'N',
936 'RS_EMPLOYEE',
937 p_user_id,
938 SYSDATE,
939 p_user_id,
940 SYSDATE
941 FROM dual
942 );
943 END IF;
944
945
946 END LOOP;
947 END IF; -- RplOwnerFromRsc>0
948
949 /**************************************************/
950 /* For each assigment rolling up to the new owner */
951 /**************************************************/
952
953
954 /* for each assignment hasing a TG owner as a parent of its rep */
955 /*FOR tgaHasParent IN NAHasOwnerAsParent
956 LOOP
957
958 -- If the assign_flag='Y' check validation, if 'N', this NA should be assigned to new TG owner who is qualified as a parent IF tgaHasParent.aflag='N' THEN
959
960
961 DELETE from jtf_tty_named_acct_rsc j
962 WHERE j.TERR_GROUP_ACCOUNT_ID = tgaHasParent.tga_id;
963 --and ...;
964
965 FOR owner IN TGOwner
966 LOOP
967
968 --create assignment
969 INSERT INTO jtf_tty_named_acct_rsc(
970 ACCOUNT_RESOURCE_ID,
971 OBJECT_VERSION_NUMBER,
972 TERR_GROUP_ACCOUNT_ID,
973 RESOURCE_ID,
974 RSC_GROUP_ID,
975 RSC_ROLE_CODE,
976 ASSIGNED_FLAG,
977 RSC_RESOURCE_TYPE,
978 CREATED_BY,
979 CREATION_DATE,
980 LAST_UPDATED_BY,
981 LAST_UPDATE_DATE)
982 ( SELECT jtf_tty_named_acct_rsc_s.nextval,
983 1,
984 tgaHasParent.tga_id,
985 owner.resource_id,
986 owner.rsc_group_id,
987 owner.rsc_role_code,
988 'N',
989 'RS_EMPLOYEE',
990 p_user_id,
991 sysdate,
992 p_user_id,
993 sysdate
994 FROM dual
995 WHERE NOT EXISTS
996 ( SELECT NULL
997 FROM jtf_tty_named_acct_rsc r
998 WHERE r.TERR_GROUP_ACCOUNT_ID = tgaHasParent.tga_id
999 AND r.RESOURCE_ID = owner.resource_id
1000 AND r.RSC_ROLE_CODE = owner.rsc_role_code
1001 AND r.RSC_GROUP_ID = owner.rsc_group_id
1002 AND r.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE'
1003 AND r.RSC_GROUP_ID = tgaHasParent.parentgrpid
1004 )
1005 );
1006
1007 END LOOP;
1008
1009 --ELSE check resource and group menber role
1010 -- SELECT NULL INTO
1011 -- FROM
1012
1013
1014 END IF; -- flag ='N'
1015
1016 END LOOP; --for each assignment hasing a TG owner as a parent of its rep
1017
1018
1019 -- for each NA without hasing a TG owner as a parent of its rep
1020 FOR tgaNoParent IN NAHasNoOwnerAsParent
1021 LOOP
1022 FOR owner IN TGOwner
1023 LOOP
1024
1025 -- create assignment
1026 INSERT INTO jtf_tty_named_acct_rsc(
1027 ACCOUNT_RESOURCE_ID,
1028 OBJECT_VERSION_NUMBER,
1029 TERR_GROUP_ACCOUNT_ID,
1030 RESOURCE_ID,
1031 RSC_GROUP_ID,
1032 RSC_ROLE_CODE,
1033 ASSIGNED_FLAG,
1034 RSC_RESOURCE_TYPE,
1035 CREATED_BY,
1036 CREATION_DATE,
1037 LAST_UPDATED_BY,
1038 LAST_UPDATE_DATE)
1039 ( SELECT jtf_tty_named_acct_rsc_s.nextval,
1040 1,
1041 tgaNoParent.tga_id,
1042 owner.resource_id,
1043 owner.rsc_group_id,
1044 owner.rsc_role_code,
1045 'N',
1046 'RS_EMPLOYEE',
1047 p_user_id,
1048 sysdate,
1049 p_user_id,
1050 sysdate
1051 FROM dual
1052 WHERE NOT EXISTS
1053 ( SELECT NULL
1054 FROM jtf_tty_named_acct_rsc r
1055 WHERE r.TERR_GROUP_ACCOUNT_ID = tgaNoParent.tga_id
1056 AND r.RESOURCE_ID = owner.resource_id
1057 AND r.RSC_ROLE_CODE = owner.rsc_role_code
1058 AND r.RSC_GROUP_ID = owner.rsc_group_id
1059 AND r.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE'
1060
1061 )
1062 AND EXISTS
1063 (
1064 SELECT NULL
1065 FROM jtf_rs_groups_denorm gd
1066 WHERE owner.rsc_group_id = gd.parent_group_id
1067 AND gd.group_id = tgaNoParent.currentgrpid
1068 )
1069 );
1070
1071 END LOOP;
1072
1073 END LOOP;
1074
1075 */
1076
1077 COMMIT;
1078
1079 END process_assign_accts;
1080
1081
1082 PROCEDURE generateNumList(
1083 SourceStr IN VARCHAR2,
1084 TargetTab OUT NOCOPY mytabletype
1085 ) IS
1086 head NUMBER ;
1087 i NUMBER ;
1088 idx NUMBER ;
1089 s VARCHAR2(100);
1090 TargetArray mytabletype;
1091
1092 BEGIN
1093 head:=1;
1094 i:=1;
1095 idx:=1;
1096 TargetArray := mytabletype();
1097
1098 WHILE (i>0)
1099 LOOP
1100 i := INSTR(SourceStr, ',', head);
1101
1102 IF i>0 THEN
1103 TargetArray.extend();
1104 TargetArray(idx) := TO_NUMBER(SUBSTR(SourceStr, head, i-head));
1105
1106 idx := idx + 1;
1107 head :=i+1;
1108 END IF;
1109
1110 END LOOP;
1111
1112
1113 TargetArray.extend();
1114 TargetArray(idx) := TO_NUMBER(SUBSTR(SourceStr, head ));
1115
1116 TargetTab := TargetArray;
1117 /*
1118 FOR i IN TargetArray.FIRST .. TargetArray.last
1119 LOOP
1120 TargetTab.extend();
1121 TargetTab(i) := TargetArray(i);
1122 END LOOP;
1123 */
1124 END;
1125
1126 PROCEDURE generateStrList(
1127 SourceStr IN VARCHAR2,
1128 TargetTab OUT NOCOPY mytabletypev
1129 ) IS
1130 head NUMBER ;
1131 i NUMBER ;
1132 idx NUMBER ;
1133 s VARCHAR2(100);
1134 TargetArray mytabletypev;
1135
1136 BEGIN
1137 head:=1;
1138 i:=1;
1139 idx:=1;
1140 TargetArray := mytabletypev();
1141
1142 WHILE (i>0)
1143 LOOP
1144 i := INSTR(SourceStr, ',', head);
1145
1146 IF i>0 THEN
1147 TargetArray.extend();
1148 TargetArray(idx) := SUBSTR(SourceStr, head, i-head);
1149
1150 idx := idx + 1;
1151 head :=i+1;
1152 END IF;
1153
1154 END LOOP;
1155
1156
1157 TargetArray.extend();
1158 TargetArray(idx) := SUBSTR(SourceStr, head);
1159
1160 TargetTab := TargetArray;
1161
1162 END;
1163
1164
1165 /* old code for process_assign_accts below*/
1166 /* no need of denorm tables */
1167 /* and summarizing moving to concurrent program */
1168 /*
1169 DELETE from jtf_tty_acct_rsc_dn j
1170 WHERE j.TERR_GROUP_ACCOUNT_ID in
1171 (SELECT TERR_GROUP_ACCOUNT_ID
1172 FROM JTF_TTY_TERR_GRP_ACCTS
1173 WHERE TERR_GROUP_ID = p_terr_gp_id)
1174 AND j.RESOURCE_ID = p_resource_id
1175 AND j.RSC_GROUP_ID = p_group_id
1176 AND j.RSC_ROLE_CODE = p_role_code;
1177 */
1178 /* delete from the named acct sum */
1179 /*
1180 DELETE from jtf_tty_rsc_acct_summ j
1181 WHERE j.RESOURCE_ID = p_resource_id
1182 AND j.RSC_GROUP_ID = p_group_id
1183 AND j.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE';
1184 sum_res_gp_accts(p_user_id, p_resource_id, p_group_id);
1185 */
1186
1187 /* commented out by shli
1188 for group_data in groups_managed_c loop
1189 managed_group_id := group_data.group_id;
1190 DELETE from jtf_tty_named_acct_rsc j
1191 WHERE j.TERR_GROUP_ACCOUNT_ID in
1192 (SELECT TERR_GROUP_ACCOUNT_ID
1193 FROM JTF_TTY_TERR_GRP_ACCTS
1194 WHERE TERR_GROUP_ID = p_terr_gp_id)
1195 AND j.RESOURCE_ID IN (
1196 select resource_id
1197 from jtf_rs_group_members
1198 where group_id = managed_group_id);
1199 */
1200 /*
1201 DELETE from jtf_tty_acct_rsc_dn j
1202 WHERE j.TERR_GROUP_ACCOUNT_ID in
1203 (SELECT TERR_GROUP_ACCOUNT_ID
1204 FROM JTF_TTY_TERR_GRP_ACCTS
1205 WHERE TERR_GROUP_ID = p_terr_gp_id)
1206 AND j.RESOURCE_ID IN (
1207 select resource_id
1208 from jtf_rs_group_members
1209 where group_id = managed_group_id);
1210 */
1211 /* delete from the named acct sum */
1212 /*
1213 DELETE from jtf_tty_rsc_acct_summ j
1214 WHERE j.RESOURCE_ID IN (
1215 select resource_id
1216 from jtf_rs_group_members
1217 where group_id = managed_group_id);
1218 */
1219 /* re-summarize */
1220 /*
1221 insert into jtf_tty_rsc_acct_summ(
1222 RESOURCE_ACCT_SUMM_ID,
1223 OBJECT_VERSION_NUMBER,
1224 RESOURCE_ID,
1225 RSC_GROUP_ID,
1226 RSC_RESOURCE_TYPE,
1227 SITE_TYPE_CODE,
1228 NUMBER_ACCOUNTS,
1229 CREATED_BY,
1230 CREATION_DATE,
1231 LAST_UPDATED_BY,
1232 LAST_UPDATE_DATE)
1233 (select jtf_tty_rsc_acct_summ_s.nextval,
1234 1,
1235 ilv.RESOURCE_ID,
1236 ilv.RSC_GROUP_ID,
1237 'RS_EMPLOYEE',
1238 ilv.site_type_code,
1239 ilv.num_accts,
1240 p_user_id,
1241 sysdate,
1242 p_user_id,
1243 sysdate
1244 FROM
1245 (select narsc.RESOURCE_ID,
1246 narsc.RSC_GROUP_ID,
1247 'RS_EMPLOYEE',
1248 na.site_type_code,
1249 count(na.NAMED_ACCOUNT_ID) num_accts
1250 from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
1251 jtf_tty_terr_grp_accts tga
1252 where na.named_account_id = tga.named_account_id
1253 and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
1254 and narsc.RESOURCE_ID IN (
1255 select resource_id
1256 from jtf_rs_group_members
1257 where group_id = managed_group_id)
1258 group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
1259 */
1260 /* commented out by shli
1261 for child_gp_data in child_groups_c loop
1262 child_group_id := child_gp_data.group_id;
1263 DELETE from jtf_tty_named_acct_rsc j
1264 WHERE j.TERR_GROUP_ACCOUNT_ID in
1265 (SELECT TERR_GROUP_ACCOUNT_ID
1266 FROM JTF_TTY_TERR_GRP_ACCTS
1267 WHERE TERR_GROUP_ID = p_terr_gp_id)
1268 AND j.RESOURCE_ID IN (
1269 select resource_id
1270 from jtf_rs_group_members
1271 where group_id = child_group_id);
1272 */
1273 /*
1274 DELETE from jtf_tty_acct_rsc_dn j
1275 WHERE j.TERR_GROUP_ACCOUNT_ID in
1276 (SELECT TERR_GROUP_ACCOUNT_ID
1277 FROM JTF_TTY_TERR_GRP_ACCTS
1278 WHERE TERR_GROUP_ID = p_terr_gp_id)
1279 AND j.RESOURCE_ID IN (
1280 select resource_id
1281 from jtf_rs_group_members
1282 where group_id = child_group_id);
1283 */
1284 /* delete from the named acct sum */
1285 /*
1286 DELETE from jtf_tty_rsc_acct_summ j
1287 WHERE j.RESOURCE_ID IN (
1288 select resource_id
1289 from jtf_rs_group_members
1290 where group_id = child_group_id);
1291 */
1292 /* re-summarize */
1293 /*
1294 insert into jtf_tty_rsc_acct_summ(
1295 RESOURCE_ACCT_SUMM_ID,
1296 OBJECT_VERSION_NUMBER,
1297 RESOURCE_ID,
1298 RSC_GROUP_ID,
1299 RSC_RESOURCE_TYPE,
1300 SITE_TYPE_CODE,
1301 NUMBER_ACCOUNTS,
1302 CREATED_BY,
1303 CREATION_DATE,
1304 LAST_UPDATED_BY,
1305 LAST_UPDATE_DATE)
1306 (select jtf_tty_rsc_acct_summ_s.nextval,
1307 1,
1308 ilv.RESOURCE_ID,
1309 ilv.RSC_GROUP_ID,
1310 'RS_EMPLOYEE',
1311 ilv.site_type_code,
1312 ilv.num_accts,
1313 p_user_id,
1314 sysdate,
1315 p_user_id,
1316 sysdate
1317 FROM
1318 (select narsc.RESOURCE_ID,
1319 narsc.RSC_GROUP_ID,
1320 'RS_EMPLOYEE',
1321 na.site_type_code,
1322 count(na.NAMED_ACCOUNT_ID) num_accts
1323 from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
1324 jtf_tty_terr_grp_accts tga
1325 where na.named_account_id = tga.named_account_id
1326 and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
1327 and narsc.RESOURCE_ID IN (
1328 select resource_id
1329 from jtf_rs_group_members
1330 where group_id = child_group_id)
1331 group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
1332 */
1333
1334
1335
1336
1337 PROCEDURE assign_acct(p_terr_gp_id IN NUMBER,
1338 p_terr_gp_acct_id IN NUMBER,
1339 p_resource_id IN NUMBER,
1340 p_group_id IN NUMBER,
1341 p_role_code IN VARCHAR2,
1342 p_action_type IN VARCHAR2,
1343 p_user_id IN NUMBER)
1344 AS
1345 BEGIN
1346
1347
1348 INSERT INTO jtf_tty_named_acct_rsc(
1349 ACCOUNT_RESOURCE_ID,
1350 OBJECT_VERSION_NUMBER,
1351 TERR_GROUP_ACCOUNT_ID,
1352 RESOURCE_ID,
1353 RSC_GROUP_ID,
1354 RSC_ROLE_CODE,
1355 ASSIGNED_FLAG,
1356 RSC_RESOURCE_TYPE,
1357 CREATED_BY,
1358 CREATION_DATE,
1359 LAST_UPDATED_BY,
1360 LAST_UPDATE_DATE)
1361 (SELECT jtf_tty_named_acct_rsc_s.NEXTVAL,
1362 1,
1363 p_terr_gp_acct_id,
1364 p_resource_id,
1365 p_group_id,
1366 p_role_code,
1367 'N',
1368 'RS_EMPLOYEE',
1369 p_user_id,
1370 SYSDATE,
1371 p_user_id,
1372 SYSDATE
1373 FROM dual
1374 WHERE NOT EXISTS
1375 ( SELECT NULL FROM jtf_tty_named_acct_rsc r
1376 WHERE r.TERR_GROUP_ACCOUNT_ID = p_terr_gp_acct_id
1377 AND r.RESOURCE_ID = p_resource_id
1378 AND r.RSC_ROLE_CODE = p_role_code
1379 AND r.RSC_GROUP_ID = p_group_id
1380 AND r.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE')
1381 );
1382
1383
1384 /* now assign the appropriate user to the accounts
1385 ** in the DENORM table */
1386 --
1387 --
1388 /*
1389 INSERT into jtf_tty_acct_rsc_dn(
1390 ACCOUNT_RESOURCE_DN_ID,
1391 OBJECT_VERSION_NUMBER,
1392 TERR_GROUP_ACCOUNT_ID,
1393 RESOURCE_ID,
1394 RSC_GROUP_ID,
1395 RSC_ROLE_CODE,
1396 RSC_RESOURCE_TYPE,
1397 ASSIGNED_TO_DIRECT_FLAG,
1398 CREATED_BY,
1399 CREATION_DATE,
1400 LAST_UPDATED_BY,
1401 LAST_UPDATE_DATE)
1402 (select jtf_tty_acct_rsc_dn_s.nextval,
1403 1,
1404 p_terr_gp_acct_id,
1405 p_resource_id,
1406 p_group_id,
1407 p_role_code,
1408 'RS_EMPLOYEE',
1409 'N',
1410 p_user_id,
1411 sysdate,
1412 p_user_id,
1413 sysdate
1414 from dual
1415 WHERE NOT EXISTS
1416 ( SELECT NULL FROM jtf_tty_acct_rsc_dn r
1417 WHERE r.TERR_GROUP_ACCOUNT_ID = p_terr_gp_acct_id
1418 AND r.RESOURCE_ID = p_resource_id
1419 AND r.RSC_ROLE_CODE = p_role_code
1420 AND r.RSC_GROUP_ID = p_group_id
1421 AND r.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE')
1422 );
1423 */
1424 COMMIT;
1425
1426 END assign_acct;
1427
1428 PROCEDURE assign_accts(p_terr_gp_id IN NUMBER,
1429 p_resource_id IN NUMBER,
1430 p_group_id IN NUMBER,
1431 p_role_code IN VARCHAR2,
1432 p_action_type IN VARCHAR2,
1433 p_user_id IN NUMBER)
1434 AS
1435 BEGIN
1436
1437 /* sbehera 12/30/02 changed assigned_flag from 'Y' to 'N' */
1438 /* now assign the appropriate user to the accounts */
1439 --
1440 -- 01/07/03: JDOCHERT: FIX FOR BUG#2736765
1441 -- Added check (NOT EXISTS) to only INSERT a record
1442 -- if it is a new NA that does not already belong
1443 -- to the TG.
1444 -- 01/15/03: SGKUMAR: Also added if account does not
1445 -- belong to TG and additionally the same owner because a tga
1446 -- can belong to multiple owners
1447
1448 INSERT INTO jtf_tty_named_acct_rsc(
1449 ACCOUNT_RESOURCE_ID,
1450 OBJECT_VERSION_NUMBER,
1451 TERR_GROUP_ACCOUNT_ID,
1452 RESOURCE_ID,
1453 RSC_GROUP_ID,
1454 RSC_ROLE_CODE,
1455 ASSIGNED_FLAG,
1456 RSC_RESOURCE_TYPE,
1457 CREATED_BY,
1458 CREATION_DATE,
1459 LAST_UPDATED_BY,
1460 LAST_UPDATE_DATE)
1461 (SELECT jtf_tty_named_acct_rsc_s.NEXTVAL,
1462 1,
1463 a.TERR_GROUP_ACCOUNT_ID,
1464 p_resource_id,
1465 p_group_id,
1466 p_role_code,
1467 'N',
1468 'RS_EMPLOYEE',
1469 p_user_id,
1470 SYSDATE,
1471 p_user_id,
1472 SYSDATE
1473 FROM jtf_tty_terr_grp_accts a, dual
1474 WHERE terr_group_id = p_terr_gp_id
1475 AND NOT EXISTS
1476 ( SELECT NULL FROM jtf_tty_named_acct_rsc r
1477 WHERE r.TERR_GROUP_ACCOUNT_ID = a.TERR_GROUP_ACCOUNT_ID
1478 AND r.RESOURCE_ID = p_resource_id
1479 AND r.RSC_ROLE_CODE = p_role_code
1480 AND r.RSC_GROUP_ID = p_group_id
1481 AND r.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE')
1482 );
1483
1484
1485 /* now assign the appropriate user to the accounts
1486 ** in the DENORM table */
1487 --
1488 -- 01/06/03: JDOCHERT: FIX FOR BUG#2736765
1489 -- Added check (NOT EXISTS) to only INSERT a record
1490 -- if it is a new NA that does not already belong
1491 -- to the TG.
1492 -- 01/08/03: JDOCHERT: FIX FOR BUG#2741455
1493 -- Changed table in NOT EXISTS from jtf_tty_named_acct_rsc
1494 -- to jtf_tty_acct_rsc_dn
1495 --
1496 /*
1497 INSERT into jtf_tty_acct_rsc_dn(
1498 ACCOUNT_RESOURCE_DN_ID,
1499 OBJECT_VERSION_NUMBER,
1500 TERR_GROUP_ACCOUNT_ID,
1501 RESOURCE_ID,
1502 RSC_GROUP_ID,
1503 RSC_ROLE_CODE,
1504 RSC_RESOURCE_TYPE,
1505 ASSIGNED_TO_DIRECT_FLAG,
1506 CREATED_BY,
1507 CREATION_DATE,
1508 LAST_UPDATED_BY,
1509 LAST_UPDATE_DATE)
1510 (select jtf_tty_acct_rsc_dn_s.nextval,
1511 1,
1512 a.TERR_GROUP_ACCOUNT_ID,
1513 p_resource_id,
1514 p_group_id,
1515 p_role_code,
1516 'RS_EMPLOYEE',
1517 'N',
1518 p_user_id,
1519 sysdate,
1520 p_user_id,
1521 sysdate
1522 from jtf_tty_terr_grp_accts a, dual
1523 where terr_group_id = p_terr_gp_id
1524 AND NOT EXISTS
1525 ( SELECT NULL FROM jtf_tty_acct_rsc_dn r
1526 WHERE r.TERR_GROUP_ACCOUNT_ID = a.TERR_GROUP_ACCOUNT_ID
1527 AND r.RESOURCE_ID = p_resource_id
1528 AND r.RSC_ROLE_CODE = p_role_code
1529 AND r.RSC_GROUP_ID = p_group_id
1530 AND r.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE')
1531 );
1532 */
1533
1534 COMMIT;
1535
1536 END assign_accts;
1537
1538 PROCEDURE sum_owner_accts(p_user_id IN NUMBER,
1539 p_terr_gp_id IN NUMBER,
1540 p_action_type IN VARCHAR2)
1541 AS
1542 BEGIN
1543 /*
1544 delete from jtf_tty_rsc_acct_summ
1545 where resource_id in(
1546 select resource_id
1547 from jtf_tty_terr_grp_owners
1548 where terr_group_id = p_terr_gp_id)
1549 and rsc_group_id in(
1550 select rsc_group_id
1551 from jtf_tty_terr_grp_owners
1552 where terr_group_id = p_terr_gp_id);
1553
1554
1555 insert into jtf_tty_rsc_acct_summ(
1556 RESOURCE_ACCT_SUMM_ID,
1557 OBJECT_VERSION_NUMBER,
1558 RESOURCE_ID,
1559 RSC_GROUP_ID,
1560 RSC_RESOURCE_TYPE,
1561 SITE_TYPE_CODE,
1562 NUMBER_ACCOUNTS,
1563 CREATED_BY,
1564 CREATION_DATE,
1565 LAST_UPDATED_BY,
1566 LAST_UPDATE_DATE)
1567 (select jtf_tty_rsc_acct_summ_s.nextval,
1568 1,
1569 ilv.RESOURCE_ID,
1570 ilv.RSC_GROUP_ID,
1571 'RS_EMPLOYEE',
1572 ilv.site_type_code,
1573 ilv.num_accts,
1574 p_user_id,
1575 sysdate,
1576 p_user_id,
1577 sysdate
1578 FROM
1579 (select narsc.RESOURCE_ID,
1580 narsc.RSC_GROUP_ID,
1581 'RS_EMPLOYEE',
1582 na.site_type_code,
1583 count(na.NAMED_ACCOUNT_ID) num_accts
1584 from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
1585 jtf_tty_terr_grp_accts tga, jtf_tty_terr_grp_owners tgo
1586 where na.named_account_id = tga.named_account_id
1587 and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
1588 and narsc.RESOURCE_ID = tgo.RESOURCE_ID
1589 and narsc.RSC_GROUP_ID = tgo.RSC_GROUP_ID
1590 and tgo.TERR_GROUP_ID = p_terr_gp_id
1591 group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
1592 */
1593 COMMIT;
1594 END sum_owner_accts;
1595
1596 PROCEDURE sum_res_gp_accts(p_user_id IN NUMBER,
1597 p_resource_id IN NUMBER,
1598 p_rsc_group_id IN NUMBER)
1599 AS
1600 BEGIN
1601 /*
1602 delete from jtf_tty_rsc_acct_summ
1603 where RESOURCE_ID = p_resource_id
1604 and RSC_GROUP_ID = p_rsc_group_id;
1605 insert into jtf_tty_rsc_acct_summ(
1606 RESOURCE_ACCT_SUMM_ID,
1607 OBJECT_VERSION_NUMBER,
1608 RESOURCE_ID,
1609 RSC_GROUP_ID,
1610 RSC_RESOURCE_TYPE,
1611 SITE_TYPE_CODE,
1612 NUMBER_ACCOUNTS,
1613 CREATED_BY,
1614 CREATION_DATE,
1615 LAST_UPDATED_BY,
1616 LAST_UPDATE_DATE)
1617 (select jtf_tty_rsc_acct_summ_s.nextval,
1618 1,
1619 ilv.RESOURCE_ID,
1620 ilv.RSC_GROUP_ID,
1621 'RS_EMPLOYEE',
1622 ilv.site_type_code,
1623 ilv.num_accts,
1624 p_user_id,
1625 sysdate,
1626 p_user_id,
1627 sysdate
1628 FROM
1629 (select narsc.RESOURCE_ID,
1630 narsc.RSC_GROUP_ID,
1631 'RS_EMPLOYEE',
1632 na.site_type_code,
1633 count(na.NAMED_ACCOUNT_ID) num_accts
1634 from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
1635 jtf_tty_terr_grp_accts tga
1636 where na.named_account_id = tga.named_account_id
1637 and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
1638 and narsc.RESOURCE_ID = p_resource_id
1639 and narsc.RSC_GROUP_ID = p_rsc_group_id
1640 group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
1641 */
1642
1643 COMMIT;
1644 END sum_res_gp_accts;
1645
1646 PROCEDURE sum_rm_bin(
1647 x_return_status OUT NOCOPY VARCHAR2
1648 , x_error_message OUT NOCOPY VARCHAR2
1649 )
1650 IS
1651 L_USER_ID NUMBER := Fnd_Global.USER_ID();
1652 L_SYSDATE DATE := SYSDATE;
1653 p_user_id NUMBER;
1654 p_overlapping_account_flag NUMBER;
1655 p_owner_user_id NUMBER;
1656 p_owner_group_id NUMBER;
1657 p_resource_id NUMBER;
1658 p_num_accts NUMBER;
1659 p_site_type_code VARCHAR2(30);
1660 p_group_id NUMBER;
1661 p_count NUMBER;
1662 p_manager_id NUMBER;
1663 p_num_geos NUMBER;
1664
1665 CURSOR ALL_OWNER_USERS_C
1666 IS SELECT DISTINCT rs.user_id, tgo.rsc_group_id
1667 FROM JTF_TTY_TERR_GRP_OWNERS tgo,
1668 JTF_RS_RESOURCE_EXTNS rs
1669 WHERE rs.resource_id = tgo.resource_id;
1670
1671 CURSOR all_managers_c
1672 IS SELECT mdv.resource_id,
1673 mdv.group_id,
1674 mdv.dir_user_id
1675 FROM jtf_tty_my_resources_v mdv,
1676 jtf_rs_group_members mem,
1677 jtf_rs_roles_b rol,
1678 jtf_rs_role_relations rlt
1679 WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
1680 AND NVL(rlt.delete_flag, 'N') <> 'Y'
1681 AND SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active, SYSDATE)
1682 AND rlt.role_id = rol.role_id
1683 AND rol.manager_flag = 'Y'
1684 AND rlt.role_resource_id = mem.group_member_id
1685 AND NVL( mem.delete_flag, 'N') <> 'Y'
1686 AND mem.resource_id = mdv.resource_id
1687 AND rol.role_code = mdv.role_code
1688 AND mem.group_id = mdv.group_id
1689 AND mdv.parent_group_id = p_owner_group_id
1690 AND mdv.current_user_id = p_owner_user_id;
1691
1692 CURSOR all_salesreps_c
1693 IS SELECT DISTINCT sdv.resource_id,
1694 sdv.group_id,
1695 sdv.dir_user_id
1696 FROM jtf_tty_my_resources_v sdv,
1697 jtf_rs_group_members mem,
1698 jtf_rs_roles_b rol,
1699 jtf_rs_role_relations rlt
1700 WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
1701 AND NVL(rlt.delete_flag, 'N') <> 'Y'
1702 AND SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active, SYSDATE)
1703 AND rlt.role_id = rol.role_id
1704 AND rol.member_flag = 'Y'
1705 AND rlt.role_resource_id = mem.group_member_id
1706 AND NVL( mem.delete_flag, 'N') <> 'Y'
1707 AND mem.resource_id = sdv.resource_id
1708 AND mem.group_id = sdv.group_id
1709 AND sdv.role_code = rol.role_code
1710 AND sdv.parent_group_id = p_owner_group_id
1711 AND sdv.current_user_id = p_owner_user_id;
1712 /*
1713 and not exists (select mem1.resource_id from
1714 jtf_rs_group_members mem1,
1715 jtf_rs_roles_b rol1,
1716 jtf_rs_role_relations rlt1
1717 where rlt1.role_resource_type = 'RS_GROUP_MEMBER'
1718 and nvl(rlt1.delete_flag, 'N') <> 'Y'
1719 and sysdate between rlt1.start_date_active
1720 and nvl(rlt1.end_date_active, sysdate)
1721 and rlt1.role_id = rol1.role_id
1722 and rol1.manager_flag = 'Y'
1723 and rlt1.role_resource_id = mem1.group_member_id
1724 and nvl( mem1.delete_flag, 'N') <> 'Y'
1725 and mem1.resource_id = sdv.resource_id);
1726 */
1727
1728 CURSOR named_accounts_c IS
1729 SELECT COUNT(DISTINCT na.named_account_id) num_accounts
1730 FROM
1731 jtf_tty_named_accts na,
1732 jtf_tty_terr_grp_accts ga,
1733 jtf_tty_my_resources_v repdn,
1734 jtf_tty_named_acct_rsc narsc,
1735 jtf_rs_resource_extns rs,
1736 jtf_rs_group_members mem,
1737 jtf_tty_terr_groups ttygrp
1738 WHERE na.named_account_id = ga.named_account_id
1739 AND ga.terr_group_account_id = narsc.terr_group_account_id
1740 AND narsc.resource_id = repdn.resource_id
1741 AND narsc.rsc_group_id = repdn.group_id
1742 AND repdn.parent_group_id = p_group_id
1743 AND repdn.current_user_id = p_user_id
1744 AND rs.user_id = repdn.current_user_id
1745 AND rs.resource_id = mem.resource_id
1746 AND ttygrp.terr_group_id = ga.terr_group_id
1747 AND (ttygrp.active_from_date <= SYSDATE OR ttygrp.active_to_date IS NULL)
1748 AND (ttygrp.active_to_date >= SYSDATE OR ttygrp.active_to_date IS NULL)
1749 AND na.site_type_code = p_site_type_code;
1750
1751
1752 CURSOR named_accounts_all_c IS
1753 SELECT COUNT(DISTINCT na.named_account_id) num_accounts
1754 FROM
1755 jtf_tty_named_accts na,
1756 jtf_tty_terr_grp_accts ga,
1757 jtf_tty_my_resources_v repdn,
1758 jtf_tty_named_acct_rsc narsc,
1759 jtf_rs_resource_extns rs,
1760 jtf_rs_group_members mem,
1761 jtf_tty_terr_groups ttygrp
1762 WHERE na.named_account_id = ga.named_account_id
1763 AND ga.terr_group_account_id = narsc.terr_group_account_id
1764 AND narsc.resource_id = repdn.resource_id
1765 AND narsc.rsc_group_id = repdn.group_id
1766 AND repdn.parent_group_id = p_group_id
1767 AND repdn.current_user_id = p_user_id
1768 AND rs.user_id = repdn.current_user_id
1769 AND rs.resource_id = mem.resource_id
1770 AND ttygrp.terr_group_id = ga.terr_group_id
1771 AND (ttygrp.active_from_date <= SYSDATE OR ttygrp.active_to_date IS NULL)
1772 AND (ttygrp.active_to_date >= SYSDATE OR ttygrp.active_to_date IS NULL);
1773
1774 CURSOR resource_managers_c IS
1775 SELECT DISTINCT a.parent_resource_id manager_id
1776 FROM jtf_rs_rep_managers A
1777 WHERE a.hierarchy_type <> 'MGR_TO_ADMIN'
1778 AND a.reports_to_flag = 'Y'
1779 AND a.parent_resource_id <> a.resource_id
1780 AND SYSDATE BETWEEN a.start_date_active AND NVL(end_date_active, SYSDATE+1)
1781 AND a.resource_id = p_resource_id
1782 AND a.group_id = p_group_id;
1783
1784 CURSOR num_geos_c IS
1785 SELECT COUNT(gt.geo_territory_id) geo
1786 FROM jtf_tty_geo_terr_rsc gt, jtf_tty_geo_terr gterr, jtf_tty_terr_groups tg
1787 WHERE gt.geo_territory_id = gterr.geo_territory_id
1788 AND tg.terr_group_id = gterr.terr_group_id
1789 AND TRUNC(tg.active_from_date) <= TRUNC(SYSDATE)
1790 AND (tg.active_to_date IS NULL OR TRUNC(tg.active_to_date) >= TRUNC(SYSDATE))
1791 AND gt.resource_id = p_resource_id
1792 AND gt.rsc_group_id = p_group_id ;
1793
1794 CURSOR site_codes_c IS
1795 SELECT lookup_code site_type_code
1796 FROM fnd_lookups l
1797 WHERE lookup_type = 'JTF_TTY_SITE_TYPE_CODE';
1798
1799 CURSOR salesrep_named_accounts_c
1800 IS SELECT COUNT(na.NAMED_ACCOUNT_ID) num_accounts
1801 FROM jtf_tty_named_accts na, jtf_tty_named_acct_rsc narsc,
1802 jtf_tty_terr_grp_accts tga, jtf_tty_terr_groups tg
1803 WHERE na.named_account_id = tga.named_account_id
1804 AND tga.terr_group_id = tg.terr_group_id
1805 AND (tg.active_from_date <= SYSDATE OR tg.active_from_date IS NULL)
1806 AND (tg.active_to_date >= SYSDATE OR tg.active_to_date IS NULL)
1807 AND narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
1808 AND narsc.resource_id = p_resource_id
1809 AND narsc.RSC_GROUP_ID = p_group_id
1810 AND narsc.rsc_resource_type = 'RS_EMPLOYEE'
1811 AND na.site_type_code = p_site_type_code;
1812
1813 CURSOR salesrep_named_accounts_all_c
1814 IS SELECT COUNT(na.NAMED_ACCOUNT_ID) num_accounts
1815 FROM jtf_tty_named_accts na, jtf_tty_named_acct_rsc narsc,
1816 jtf_tty_terr_grp_accts tga, jtf_tty_terr_groups tg
1817 WHERE na.named_account_id = tga.named_account_id
1818 AND tga.terr_group_id = tg.terr_group_id
1819 AND (tg.active_from_date <= SYSDATE OR tg.active_from_date IS NULL)
1820 AND (tg.active_to_date >= SYSDATE OR tg.active_to_date IS NULL)
1821 AND narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
1822 AND narsc.resource_id = p_resource_id
1823 AND narsc.RSC_GROUP_ID = p_group_id
1824 AND narsc.rsc_resource_type = 'RS_EMPLOYEE';
1825 --and na.site_type_code = p_site_type_code;
1826
1827 BEGIN
1828 jtf_tty_workflow_pop_bin_pvt.print_log('In sum rm bin Procedure ');
1829 DELETE jtf_tty_rsc_acct_summ;
1830
1831 FOR owner_users IN all_owner_users_c LOOP
1832 p_owner_user_id := owner_users.user_id;
1833 p_owner_group_id := owner_users.rsc_group_id;
1834 FOR managers IN all_managers_c LOOP
1835 p_user_id := managers.dir_user_id;
1836 p_resource_id := managers.resource_id;
1837 p_group_id := managers.group_id;
1838 SELECT COUNT(RESOURCE_ACCT_SUMM_ID)
1839 INTO p_count
1840 FROM jtf_tty_rsc_acct_summ
1841 WHERE RESOURCE_ID = p_resource_id
1842 AND RSC_GROUP_ID = p_group_id;
1843
1844 IF (p_count < 1) THEN
1845 FOR numbergeos IN num_geos_c LOOP
1846 p_num_geos := numbergeos.geo;
1847 END LOOP;
1848 FOR sites IN site_codes_c LOOP
1849 p_site_type_code := sites.site_type_code;
1850 IF (p_site_type_code = 'ALL') THEN
1851 FOR namedaccounts IN named_accounts_all_c LOOP
1852 p_num_accts := namedaccounts.num_accounts;
1853 END LOOP;
1854 INSERT INTO jtf_tty_rsc_acct_summ(
1855 RESOURCE_ACCT_SUMM_ID,
1856 OBJECT_VERSION_NUMBER,
1857 RESOURCE_ID,
1858 RSC_GROUP_ID,
1859 RSC_RESOURCE_TYPE,
1860 SITE_TYPE_CODE,
1861 NUMBER_ACCOUNTS,
1862 NUMBER_GEOS,
1863 MANAGER_ID,
1864 CREATED_BY,
1865 CREATION_DATE,
1866 LAST_UPDATED_BY,
1867 LAST_UPDATE_DATE)
1868 VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
1869 1,
1870 p_resource_id,
1871 p_group_id,
1872 'RS_EMPLOYEE',
1873 p_site_type_code,
1874 p_num_accts,
1875 p_num_geos,
1876 -999,
1877 l_user_id,
1878 l_sysdate,
1879 l_user_id,
1880 l_sysdate);
1881 FOR res_managers IN resource_managers_c LOOP
1882 p_manager_id := res_managers.manager_id;
1883 INSERT INTO jtf_tty_rsc_acct_summ(
1884 RESOURCE_ACCT_SUMM_ID,
1885 OBJECT_VERSION_NUMBER,
1886 RESOURCE_ID,
1887 RSC_GROUP_ID,
1888 RSC_RESOURCE_TYPE,
1889 SITE_TYPE_CODE,
1890 NUMBER_ACCOUNTS,
1891 NUMBER_GEOS,
1892 MANAGER_ID,
1893 CREATED_BY,
1894 CREATION_DATE,
1895 LAST_UPDATED_BY,
1896 LAST_UPDATE_DATE)
1897 VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
1898 1,
1899 p_resource_id,
1900 p_group_id,
1901 'RS_EMPLOYEE',
1902 p_site_type_code,
1903 p_num_accts,
1904 p_num_geos,
1905 p_manager_id,
1906 l_user_id,
1907 l_sysdate,
1908 l_user_id,
1909 l_sysdate);
1910 END LOOP;
1911 ELSE
1912 FOR namedaccounts IN named_accounts_c LOOP
1913 p_num_accts := namedaccounts.num_accounts;
1914 END LOOP;
1915 INSERT INTO jtf_tty_rsc_acct_summ(
1916 RESOURCE_ACCT_SUMM_ID,
1917 OBJECT_VERSION_NUMBER,
1918 RESOURCE_ID,
1919 RSC_GROUP_ID,
1920 RSC_RESOURCE_TYPE,
1921 SITE_TYPE_CODE,
1922 NUMBER_ACCOUNTS,
1923 NUMBER_GEOS,
1924 MANAGER_ID,
1925 CREATED_BY,
1926 CREATION_DATE,
1927 LAST_UPDATED_BY,
1928 LAST_UPDATE_DATE)
1929 VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
1930 1,
1931 p_resource_id,
1932 p_group_id,
1933 'RS_EMPLOYEE',
1934 p_site_type_code,
1935 p_num_accts,
1936 p_num_geos,
1937 -999,
1938 l_user_id,
1939 l_sysdate,
1940 l_user_id,
1941 l_sysdate);
1942 FOR res_managers IN resource_managers_c LOOP
1943 p_manager_id := res_managers.manager_id;
1944 INSERT INTO jtf_tty_rsc_acct_summ(
1945 RESOURCE_ACCT_SUMM_ID,
1946 OBJECT_VERSION_NUMBER,
1947 RESOURCE_ID,
1948 RSC_GROUP_ID,
1949 RSC_RESOURCE_TYPE,
1950 SITE_TYPE_CODE,
1951 NUMBER_ACCOUNTS,
1952 NUMBER_GEOS,
1953 MANAGER_ID,
1954 CREATED_BY,
1955 CREATION_DATE,
1956 LAST_UPDATED_BY,
1957 LAST_UPDATE_DATE)
1958 VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
1959 1,
1960 p_resource_id,
1961 p_group_id,
1962 'RS_EMPLOYEE',
1963 p_site_type_code,
1964 p_num_accts,
1965 p_num_geos,
1966 p_manager_id,
1967 l_user_id,
1968 l_sysdate,
1969 l_user_id,
1970 l_sysdate);
1971 END LOOP;
1972 END IF;
1973 END LOOP;
1974 END IF;
1975 END LOOP;
1976 FOR salesreps IN all_salesreps_c LOOP
1977 p_user_id := salesreps.dir_user_id;
1978 p_resource_id := salesreps.resource_id;
1979 p_group_id := salesreps.group_id;
1980 SELECT COUNT(RESOURCE_ACCT_SUMM_ID)
1981 INTO p_count
1982 FROM jtf_tty_rsc_acct_summ
1983 WHERE RESOURCE_ID = p_resource_id
1984 AND RSC_GROUP_ID = p_group_id;
1985 IF (p_count < 1) THEN
1986 FOR numbergeos IN num_geos_c LOOP
1987 p_num_geos := numbergeos.geo;
1988 END LOOP;
1989 FOR sites IN site_codes_c LOOP
1990 p_site_type_code := sites.site_type_code;
1991 IF (p_site_type_code = 'ALL') THEN
1992 FOR namedaccounts IN salesrep_named_accounts_all_c LOOP
1993 p_num_accts := namedaccounts.num_accounts;
1994 END LOOP;
1995 INSERT INTO jtf_tty_rsc_acct_summ(
1996 RESOURCE_ACCT_SUMM_ID,
1997 OBJECT_VERSION_NUMBER,
1998 RESOURCE_ID,
1999 RSC_GROUP_ID,
2000 RSC_RESOURCE_TYPE,
2001 SITE_TYPE_CODE,
2002 NUMBER_ACCOUNTS,
2003 NUMBER_GEOS,
2004 MANAGER_ID,
2005 CREATED_BY,
2006 CREATION_DATE,
2007 LAST_UPDATED_BY,
2008 LAST_UPDATE_DATE)
2009 VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
2010 1,
2011 p_resource_id,
2012 p_group_id,
2013 'RS_EMPLOYEE',
2014 p_site_type_code,
2015 p_num_accts,
2016 p_num_geos,
2017 -999,
2018 l_user_id,
2019 l_sysdate,
2020 l_user_id,
2021 l_sysdate);
2022 FOR res_managers IN resource_managers_c LOOP
2023 p_manager_id := res_managers.manager_id;
2024 INSERT INTO jtf_tty_rsc_acct_summ(
2025 RESOURCE_ACCT_SUMM_ID,
2026 OBJECT_VERSION_NUMBER,
2027 RESOURCE_ID,
2028 RSC_GROUP_ID,
2029 RSC_RESOURCE_TYPE,
2030 SITE_TYPE_CODE,
2031 NUMBER_ACCOUNTS,
2032 NUMBER_GEOS,
2033 MANAGER_ID,
2034 CREATED_BY,
2035 CREATION_DATE,
2036 LAST_UPDATED_BY,
2037 LAST_UPDATE_DATE)
2038 VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
2039 1,
2040 p_resource_id,
2041 p_group_id,
2042 'RS_EMPLOYEE',
2043 p_site_type_code,
2044 p_num_accts,
2045 p_num_geos,
2046 p_manager_id,
2047 l_user_id,
2048 l_sysdate,
2049 l_user_id,
2050 l_sysdate);
2051 END LOOP;
2052 ELSE
2053 FOR namedaccounts IN salesrep_named_accounts_c LOOP
2054 p_num_accts := namedaccounts.num_accounts;
2055 END LOOP;
2056 INSERT INTO jtf_tty_rsc_acct_summ(
2057 RESOURCE_ACCT_SUMM_ID,
2058 OBJECT_VERSION_NUMBER,
2059 RESOURCE_ID,
2060 RSC_GROUP_ID,
2061 RSC_RESOURCE_TYPE,
2062 SITE_TYPE_CODE,
2063 NUMBER_ACCOUNTS,
2064 NUMBER_GEOS,
2065 MANAGER_ID,
2066 CREATED_BY,
2067 CREATION_DATE,
2068 LAST_UPDATED_BY,
2069 LAST_UPDATE_DATE)
2070 VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
2071 1,
2072 p_resource_id,
2073 p_group_id,
2074 'RS_EMPLOYEE',
2075 p_site_type_code,
2076 p_num_accts,
2077 p_num_geos,
2078 -999,
2079 l_user_id,
2080 l_sysdate,
2081 l_user_id,
2082 l_sysdate);
2083 FOR res_managers IN resource_managers_c LOOP
2084 p_manager_id := res_managers.manager_id;
2085 INSERT INTO jtf_tty_rsc_acct_summ(
2086 RESOURCE_ACCT_SUMM_ID,
2087 OBJECT_VERSION_NUMBER,
2088 RESOURCE_ID,
2089 RSC_GROUP_ID,
2090 RSC_RESOURCE_TYPE,
2091 SITE_TYPE_CODE,
2092 NUMBER_ACCOUNTS,
2093 NUMBER_GEOS,
2094 MANAGER_ID,
2095 CREATED_BY,
2096 CREATION_DATE,
2097 LAST_UPDATED_BY,
2098 LAST_UPDATE_DATE)
2099 VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
2100 1,
2101 p_resource_id,
2102 p_group_id,
2103 'RS_EMPLOYEE',
2104 p_site_type_code,
2105 p_num_accts,
2106 p_num_geos,
2107 p_manager_id,
2108 l_user_id,
2109 l_sysdate,
2110 l_user_id,
2111 l_sysdate);
2112 END LOOP;
2113 END IF;
2114 END LOOP;
2115 END IF;
2116 END LOOP;
2117 END LOOP;
2118 /*
2119
2120 FROM
2121 (select narsc.RESOURCE_ID,
2122 narsc.RSC_GROUP_ID,
2123 'RS_EMPLOYEE',
2124 na.site_type_code,
2125 count(na.NAMED_ACCOUNT_ID) num_accts
2126 from jtf_tty_named_accts na, jtf_tty_named_acct_rsc narsc,
2127 jtf_tty_terr_grp_accts tga, jtf_tty_terr_groups tg
2128 where na.named_account_id = tga.named_account_id
2129 and tga.terr_group_id = tg.terr_group_id
2130 and (tg.active_from_date <= sysdate or tg.active_from_date is null)
2131 and (tg.active_to_date >= sysdate or tg.active_to_date is null)
2132 and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
2133 and narsc.resource_id = p_resource_id
2134 and narsc.RSC_GROUP_ID = p_group_id
2135 group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
2136 END LOOP;
2137 END IF;
2138 END LOOP;
2139 END LOOP;
2140 */
2141
2142 COMMIT;
2143 jtf_tty_workflow_pop_bin_pvt.print_log('Summarized the RM bin');
2144 EXCEPTION
2145 WHEN OTHERS THEN
2146 jtf_tty_workflow_pop_bin_pvt.print_log('Exception others in sum_rm_bin '||SQLERRM);
2147 END sum_rm_bin;
2148
2149
2150
2151 PROCEDURE sum_accts(p_user_id IN NUMBER)
2152 AS
2153 return_status VARCHAR2(30);
2154 error_message VARCHAR2(45);
2155
2156 BEGIN
2157 jtf_tty_na_terrgp.sum_rm_bin(return_status, error_message);
2158 /*
2159 delete jtf_tty_rsc_acct_summ;
2160
2161 insert into jtf_tty_rsc_acct_summ(
2162 RESOURCE_ACCT_SUMM_ID,
2163 OBJECT_VERSION_NUMBER,
2164 RESOURCE_ID,
2165 RSC_GROUP_ID,
2166 RSC_RESOURCE_TYPE,
2167 SITE_TYPE_CODE,
2168 NUMBER_ACCOUNTS,
2169 CREATED_BY,
2170 CREATION_DATE,
2171 LAST_UPDATED_BY,
2172 LAST_UPDATE_DATE)
2173 (select jtf_tty_rsc_acct_summ_s.nextval,
2174 1,
2175 ilv.RESOURCE_ID,
2176 ilv.RSC_GROUP_ID,
2177 'RS_EMPLOYEE',
2178 ilv.site_type_code,
2179 ilv.num_accts,
2180 p_user_id,
2181 sysdate,
2182 p_user_id,
2183 sysdate
2184 FROM
2185 (select narsc.RESOURCE_ID,
2186 narsc.RSC_GROUP_ID,
2187 'RS_EMPLOYEE',
2188 na.site_type_code,
2189 count(na.NAMED_ACCOUNT_ID) num_accts
2190 from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
2191 jtf_tty_terr_grp_accts tga, jtf_tty_terr_groups tg
2192 where na.named_account_id = tga.named_account_id
2193 and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
2194 and tga.terr_group_id = tg.terr_group_id
2195 and (tg.active_from_date <= sysdate or tg.active_from_date is null)
2196 and (tg.active_to_date >= sysdate or tg.active_to_date is null)
2197 group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
2198
2199 commit;
2200 */
2201 END sum_accts;
2202
2203
2204 PROCEDURE delete_terrgp_owners_roles(p_terr_gp_id IN NUMBER)
2205 AS
2206
2207
2208 BEGIN
2209
2210 /* delete all the territory group owners */
2211 DELETE FROM jtf_tty_terr_grp_owners
2212 WHERE terr_group_id = p_terr_gp_id;
2213
2214 /* delete all the roles, first the product interests, access and finally the roles */
2215 DELETE FROM jtf_tty_role_prod_int
2216 WHERE terr_group_role_id IN (
2217 SELECT terr_group_role_id
2218 FROM jtf_tty_terr_grp_roles
2219 WHERE terr_group_id = p_terr_gp_id);
2220
2221 DELETE FROM jtf_tty_role_access
2222 WHERE terr_group_role_id IN (
2223 SELECT terr_group_role_id
2224 FROM jtf_tty_terr_grp_roles
2225 WHERE terr_group_id = p_terr_gp_id);
2226
2227 DELETE FROM jtf_tty_terr_grp_roles
2228 WHERE terr_group_id = p_terr_gp_id;
2229
2230 COMMIT;
2231 END delete_terrgp_owners_roles;
2232
2233 PROCEDURE enter_terrgp_details(p_terr_gp_id IN NUMBER,
2234 p_terr_gp_name IN VARCHAR2,
2235 p_description IN VARCHAR2,
2236 p_rank IN NUMBER,
2237 p_from_date IN DATE,
2238 p_end_date IN DATE,
2239 p_terr_id IN NUMBER,
2240 p_user_id IN NUMBER,
2241 p_matching_rule_code IN VARCHAR2 DEFAULT '1',
2242 p_workflow_item_type IN VARCHAR2 DEFAULT NULL,
2243 p_action_type IN VARCHAR2 DEFAULT 'INSERT',
2244 p_catch_all_user_id IN NUMBER,
2245 p_num_winners IN NUMBER,
2246 p_generate_na_flag IN VARCHAR2,
2247 p_group_type IN VARCHAR2 DEFAULT 'NAMED_ACCOUNT')
2248 AS
2249 p_workflow_process_name VARCHAR2(30) DEFAULT NULL;
2250 p_workflow_count NUMBER := 0;
2251 p_active_from_date DATE;
2252 p_active_to_date DATE;
2253
2254 BEGIN
2255 p_active_from_date := p_from_date;
2256 p_active_to_date := p_end_date;
2257 IF (p_from_date IS NOT NULL) THEN
2258 p_active_from_date := TRUNC(p_from_date);
2259 END IF;
2260 IF (p_end_date IS NOT NULL) THEN
2261 p_active_to_date := TRUNC(p_end_date + 1) - 1/(24 * 60 * 60);
2262 END IF;
2263
2264 IF (p_workflow_item_type IS NOT NULL) THEN
2265 SELECT COUNT(name)
2266 INTO p_workflow_count
2267 FROM wf_activities_vl
2268 WHERE item_type = p_workflow_item_type
2269 AND TYPE = 'PROCESS'
2270 AND TRUNC(NVL(end_date,SYSDATE)) >= TRUNC(SYSDATE);
2271 IF (p_workflow_count > 0) THEN
2272 SELECT name
2273 INTO p_workflow_process_name
2274 FROM wf_activities_vl
2275 WHERE item_type = p_workflow_item_type
2276 AND TYPE = 'PROCESS'
2277 AND TRUNC(NVL(end_date,SYSDATE)) >= TRUNC(SYSDATE)
2278 AND ROWNUM < 2 ;
2279 END IF;
2280 END IF;
2281 IF (p_action_type = 'INSERT') THEN
2282 INSERT INTO JTF_TTY_TERR_GROUPS(
2283 TERR_GROUP_ID,
2284 OBJECT_VERSION_NUMBER,
2285 TERR_GROUP_NAME,
2286 DESCRIPTION,
2287 RANK,
2288 ACTIVE_FROM_DATE,
2289 ACTIVE_TO_DATE,
2290 PARENT_TERR_ID,
2291 MATCHING_RULE_CODE,
2292 CREATED_BY,
2293 CREATION_DATE,
2294 LAST_UPDATED_BY,
2295 LAST_UPDATE_DATE,
2296 LAST_UPDATE_LOGIN,
2297 WORKFLOW_ITEM_TYPE,
2298 WORKFLOW_PROCESS_NAME,
2299 CATCH_ALL_RESOURCE_ID,
2300 CATCH_ALL_RESOURCE_TYPE,
2301 NUM_WINNERS,
2302 GENERATE_CATCHALL_FLAG,
2303 SELF_SERVICE_TYPE)
2304 VALUES(
2305 p_terr_gp_id,
2306 1,
2307 p_terr_gp_name,
2308 p_description,
2309 p_rank,
2310 p_active_from_date,
2311 p_active_to_date,
2312 p_terr_id,
2313 p_matching_rule_code,
2314 p_user_id,
2315 SYSDATE,
2316 p_user_id,
2317 SYSDATE,
2318 p_user_id,
2319 p_workflow_item_type,
2320 p_workflow_process_name,
2321 p_catch_all_user_id,
2322 'RS_EMPLOYEE',
2323 p_num_winners,
2324 p_generate_na_flag,
2325 p_group_type);
2326 ELSE
2327
2328 UPDATE JTF_TTY_TERR_GROUPS
2329 SET TERR_GROUP_NAME = p_terr_gp_name,
2330 DESCRIPTION = p_description,
2331 RANK = p_rank,
2332 ACTIVE_FROM_DATE = p_active_from_date,
2333 ACTIVE_TO_DATE = p_active_to_date,
2334 PARENT_TERR_ID = p_terr_id,
2335 MATCHING_RULE_CODE = p_matching_rule_code,
2336 WORKFLOW_ITEM_TYPE = p_workflow_item_type,
2337 WORKFLOW_PROCESS_NAME = p_workflow_process_name,
2338 CATCH_ALL_RESOURCE_ID = p_catch_all_user_id,
2339 CATCH_ALL_RESOURCE_TYPE = 'RS_EMPLOYEE',
2340 LAST_UPDATED_BY = p_user_id,
2341 LAST_UPDATE_DATE = SYSDATE,
2342 NUM_WINNERS = p_num_winners,
2343 GENERATE_CATCHALL_FLAG = p_generate_na_flag
2344 WHERE TERR_GROUP_ID = p_terr_gp_id;
2345 IF (p_group_type = 'GEOGRAPHY') THEN
2346 UPDATE JTF_TTY_GEO_TERR
2347 SET GEO_TERR_NAME = p_terr_gp_name
2348 WHERE TERR_GROUP_ID = p_terr_gp_id
2349 AND OWNER_RESOURCE_ID = -999
2350 AND GEO_TERRITORY_ID = - PARENT_GEO_TERR_ID;
2351 END IF;
2352
2353 END IF;
2354
2355 COMMIT;
2356
2357 END enter_terrgp_details;
2358
2359 PROCEDURE terrgp_define_role(p_terr_gp_id IN NUMBER,
2360 p_terr_gp_role_id IN NUMBER,
2361 p_user_id IN NUMBER,
2362 p_role_code IN VARCHAR2)
2363 AS
2364 BEGIN
2365
2366 /* create a role */
2367 INSERT INTO JTF_TTY_TERR_GRP_ROLES(
2368 TERR_GROUP_ROLE_ID,
2369 OBJECT_VERSION_NUMBER,
2370 TERR_GROUP_ID,
2371 ROLE_CODE,
2372 CREATED_BY,
2373 CREATION_DATE,
2374 LAST_UPDATED_BY,
2375 LAST_UPDATE_DATE,
2376 LAST_UPDATE_LOGIN)
2377 VALUES(
2378 p_terr_gp_role_id,
2379 1,
2380 p_terr_gp_id,
2381 p_role_code,
2382 p_user_id,
2383 SYSDATE,
2384 p_user_id,
2385 SYSDATE,
2386 p_user_id);
2387
2388 COMMIT;
2389
2390 END terrgp_define_role;
2391
2392
2393 PROCEDURE delete_assign_accts(p_terr_gp_id IN NUMBER,
2394 p_resource_id IN NUMBER,
2395 p_group_id IN NUMBER,
2396 p_role_code IN VARCHAR2)
2397 AS
2398 managed_group_id NUMBER;
2399 p_user_id NUMBER;
2400 x_group_id NUMBER;
2401 child_group_id NUMBER;
2402 CURSOR groups_managed_c
2403 IS
2404 SELECT mem.group_id
2405 FROM jtf_rs_group_members mem,
2406 jtf_rs_roles_b rol,
2407 jtf_rs_role_relations rlt
2408 WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
2409 AND NVL(rlt.delete_flag, 'N') <> 'Y'
2410 AND SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active, SYSDATE)
2411 AND rlt.role_id = rol.role_id
2412 AND rol.manager_flag = 'Y'
2413 AND rlt.role_resource_id = mem.group_member_id
2414 AND NVL( mem.delete_flag, 'N') <> 'Y'
2415 AND mem.resource_id = p_resource_id;
2416
2417 CURSOR child_groups_c
2418 IS
2419 SELECT group_id
2420 FROM jtf_rs_groups_denorm
2421 WHERE PARENT_GROUP_ID = managed_group_id;
2422
2423 CURSOR directs_c
2424 IS
2425 SELECT resource_id
2426 FROM jtf_rs_group_members
2427 WHERE group_id = x_group_id;
2428
2429 BEGIN
2430 p_user_id := Fnd_Global.user_id;
2431
2432 /* delete the existing assignments */
2433 DELETE FROM jtf_tty_named_acct_rsc j
2434 WHERE j.TERR_GROUP_ACCOUNT_ID IN
2435 (SELECT TERR_GROUP_ACCOUNT_ID
2436 FROM JTF_TTY_TERR_GRP_ACCTS
2437 WHERE TERR_GROUP_ID = p_terr_gp_id)
2438 AND j.RESOURCE_ID = p_resource_id
2439 AND j.RSC_GROUP_ID = p_group_id
2440 AND j.RSC_ROLE_CODE = p_role_code;
2441
2442 /* no need of denorm tables */
2443 /* and summarizing moving to concurrent program */
2444 /*
2445 DELETE from jtf_tty_acct_rsc_dn j
2446 WHERE j.TERR_GROUP_ACCOUNT_ID in
2447 (SELECT TERR_GROUP_ACCOUNT_ID
2448 FROM JTF_TTY_TERR_GRP_ACCTS
2449 WHERE TERR_GROUP_ID = p_terr_gp_id)
2450 AND j.RESOURCE_ID = p_resource_id
2451 AND j.RSC_GROUP_ID = p_group_id
2452 AND j.RSC_ROLE_CODE = p_role_code;
2453 */
2454 /* delete from the named acct sum */
2455 /*
2456 DELETE from jtf_tty_rsc_acct_summ j
2457 WHERE j.RESOURCE_ID = p_resource_id
2458 AND j.RSC_GROUP_ID = p_group_id
2459 AND j.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE';
2460 sum_res_gp_accts(p_user_id, p_resource_id, p_group_id);
2461 */
2462 FOR group_data IN groups_managed_c LOOP
2463 managed_group_id := group_data.group_id;
2464 DELETE FROM jtf_tty_named_acct_rsc j
2465 WHERE j.TERR_GROUP_ACCOUNT_ID IN
2466 (SELECT TERR_GROUP_ACCOUNT_ID
2467 FROM JTF_TTY_TERR_GRP_ACCTS
2468 WHERE TERR_GROUP_ID = p_terr_gp_id)
2469 AND j.RESOURCE_ID IN (
2470 SELECT resource_id
2471 FROM jtf_rs_group_members
2472 WHERE group_id = managed_group_id);
2473 /*
2474 DELETE from jtf_tty_acct_rsc_dn j
2475 WHERE j.TERR_GROUP_ACCOUNT_ID in
2476 (SELECT TERR_GROUP_ACCOUNT_ID
2477 FROM JTF_TTY_TERR_GRP_ACCTS
2478 WHERE TERR_GROUP_ID = p_terr_gp_id)
2479 AND j.RESOURCE_ID IN (
2480 select resource_id
2481 from jtf_rs_group_members
2482 where group_id = managed_group_id);
2483 */
2484 /* delete from the named acct sum */
2485 /*
2486 DELETE from jtf_tty_rsc_acct_summ j
2487 WHERE j.RESOURCE_ID IN (
2488 select resource_id
2489 from jtf_rs_group_members
2490 where group_id = managed_group_id);
2491 */
2492 /* re-summarize */
2493 /*
2494 insert into jtf_tty_rsc_acct_summ(
2495 RESOURCE_ACCT_SUMM_ID,
2496 OBJECT_VERSION_NUMBER,
2497 RESOURCE_ID,
2498 RSC_GROUP_ID,
2499 RSC_RESOURCE_TYPE,
2500 SITE_TYPE_CODE,
2501 NUMBER_ACCOUNTS,
2502 CREATED_BY,
2503 CREATION_DATE,
2504 LAST_UPDATED_BY,
2505 LAST_UPDATE_DATE)
2506 (select jtf_tty_rsc_acct_summ_s.nextval,
2507 1,
2508 ilv.RESOURCE_ID,
2509 ilv.RSC_GROUP_ID,
2510 'RS_EMPLOYEE',
2511 ilv.site_type_code,
2512 ilv.num_accts,
2513 p_user_id,
2514 sysdate,
2515 p_user_id,
2516 sysdate
2517 FROM
2518 (select narsc.RESOURCE_ID,
2519 narsc.RSC_GROUP_ID,
2520 'RS_EMPLOYEE',
2521 na.site_type_code,
2522 count(na.NAMED_ACCOUNT_ID) num_accts
2523 from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
2524 jtf_tty_terr_grp_accts tga
2525 where na.named_account_id = tga.named_account_id
2526 and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
2527 and narsc.RESOURCE_ID IN (
2528 select resource_id
2529 from jtf_rs_group_members
2530 where group_id = managed_group_id)
2531 group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
2532 */
2533 FOR child_gp_data IN child_groups_c LOOP
2534 child_group_id := child_gp_data.group_id;
2535 DELETE FROM jtf_tty_named_acct_rsc j
2536 WHERE j.TERR_GROUP_ACCOUNT_ID IN
2537 (SELECT TERR_GROUP_ACCOUNT_ID
2538 FROM JTF_TTY_TERR_GRP_ACCTS
2539 WHERE TERR_GROUP_ID = p_terr_gp_id)
2540 AND j.RESOURCE_ID IN (
2541 SELECT resource_id
2542 FROM jtf_rs_group_members
2543 WHERE group_id = child_group_id);
2544 /*
2545 DELETE from jtf_tty_acct_rsc_dn j
2546 WHERE j.TERR_GROUP_ACCOUNT_ID in
2547 (SELECT TERR_GROUP_ACCOUNT_ID
2548 FROM JTF_TTY_TERR_GRP_ACCTS
2549 WHERE TERR_GROUP_ID = p_terr_gp_id)
2550 AND j.RESOURCE_ID IN (
2551 select resource_id
2552 from jtf_rs_group_members
2553 where group_id = child_group_id);
2554 */
2555 /* delete from the named acct sum */
2556 /*
2557 DELETE from jtf_tty_rsc_acct_summ j
2558 WHERE j.RESOURCE_ID IN (
2559 select resource_id
2560 from jtf_rs_group_members
2561 where group_id = child_group_id);
2562 */
2563 /* re-summarize */
2564 /*
2565 insert into jtf_tty_rsc_acct_summ(
2566 RESOURCE_ACCT_SUMM_ID,
2567 OBJECT_VERSION_NUMBER,
2568 RESOURCE_ID,
2569 RSC_GROUP_ID,
2570 RSC_RESOURCE_TYPE,
2571 SITE_TYPE_CODE,
2572 NUMBER_ACCOUNTS,
2573 CREATED_BY,
2574 CREATION_DATE,
2575 LAST_UPDATED_BY,
2576 LAST_UPDATE_DATE)
2577 (select jtf_tty_rsc_acct_summ_s.nextval,
2578 1,
2579 ilv.RESOURCE_ID,
2580 ilv.RSC_GROUP_ID,
2581 'RS_EMPLOYEE',
2582 ilv.site_type_code,
2583 ilv.num_accts,
2584 p_user_id,
2585 sysdate,
2586 p_user_id,
2587 sysdate
2588 FROM
2589 (select narsc.RESOURCE_ID,
2590 narsc.RSC_GROUP_ID,
2591 'RS_EMPLOYEE',
2592 na.site_type_code,
2593 count(na.NAMED_ACCOUNT_ID) num_accts
2594 from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
2595 jtf_tty_terr_grp_accts tga
2596 where na.named_account_id = tga.named_account_id
2597 and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
2598 and narsc.RESOURCE_ID IN (
2599 select resource_id
2600 from jtf_rs_group_members
2601 where group_id = child_group_id)
2602 group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
2603 */
2604 END LOOP;
2605 END LOOP;
2606 COMMIT;
2607
2608 END delete_assign_accts;
2609
2610
2611 PROCEDURE terrgp_create_access(p_terr_gp_id IN NUMBER,
2612 p_terr_gp_role_id IN NUMBER,
2613 p_access_type IN VARCHAR2,
2614 p_access_code IN VARCHAR2,
2615 p_user_id IN NUMBER)
2616 AS
2617
2618 BEGIN
2619
2620 /* create role accesses for the role */
2621 INSERT INTO JTF_TTY_ROLE_ACCESS(
2622 TERR_GROUP_ROLE_ACCESS_ID,
2623 OBJECT_VERSION_NUMBER,
2624 TERR_GROUP_ROLE_ID ,
2625 ACCESS_TYPE ,
2626 TRANS_ACCESS_CODE,
2627 CREATED_BY ,
2628 CREATION_DATE ,
2629 LAST_UPDATED_BY ,
2630 LAST_UPDATE_DATE ,
2631 LAST_UPDATE_LOGIN)
2632 VALUES(
2633 JTF_TTY_ROLE_ACCESS_S.NEXTVAL,
2634 1,
2635 p_terr_gp_role_id,
2636 p_access_type,
2637 p_access_code,
2638 p_user_id,
2639 SYSDATE,
2640 p_user_id,
2641 SYSDATE,
2642 p_user_id);
2643
2644 COMMIT;
2645
2646 END terrgp_create_access;
2647
2648 PROCEDURE terrgp_define_interest(p_terr_gp_role_id IN NUMBER,
2649 p_interest_type_id IN NUMBER,
2650 p_cat_set_id IN NUMBER,
2651 p_cat_enabled_flag IN VARCHAR2,
2652 p_user_id IN NUMBER)
2653 AS
2654 BEGIN
2655 /* create product interests for the role */
2656 IF (p_cat_enabled_flag = 'N') THEN
2657 INSERT INTO JTF_TTY_ROLE_PROD_INT(
2658 TERR_GROUP_ROLE_PROD_INT_ID,
2659 OBJECT_VERSION_NUMBER ,
2660 TERR_GROUP_ROLE_ID ,
2661 INTEREST_TYPE_ID,
2662 CREATED_BY ,
2663 CREATION_DATE ,
2664 LAST_UPDATED_BY ,
2665 LAST_UPDATE_DATE ,
2666 LAST_UPDATE_LOGIN)
2667 VALUES(
2668 JTF_TTY_ROLE_PROD_INT_S.NEXTVAL,
2669 1,
2670 p_terr_gp_role_id,
2671 p_interest_type_id,
2672 p_user_id,
2673 SYSDATE,
2674 p_user_id,
2675 SYSDATE,
2676 p_user_id);
2677 ELSE
2678 INSERT INTO JTF_TTY_ROLE_PROD_INT(
2679 TERR_GROUP_ROLE_PROD_INT_ID,
2680 OBJECT_VERSION_NUMBER ,
2681 TERR_GROUP_ROLE_ID ,
2682 INTEREST_TYPE_ID,
2683 PRODUCT_CATEGORY_ID ,
2684 PRODUCT_CATEGORY_SET_ID ,
2685 CREATED_BY ,
2686 CREATION_DATE ,
2687 LAST_UPDATED_BY ,
2688 LAST_UPDATE_DATE ,
2689 LAST_UPDATE_LOGIN)
2690 VALUES(
2691 JTF_TTY_ROLE_PROD_INT_S.NEXTVAL,
2692 1,
2693 p_terr_gp_role_id,
2694 -999,
2695 p_interest_type_id,
2696 p_cat_set_id,
2697 p_user_id,
2698 SYSDATE,
2699 p_user_id,
2700 SYSDATE,
2701 p_user_id);
2702 END IF;
2703 COMMIT;
2704
2705 END terrgp_define_interest;
2706
2707 PROCEDURE terrgp_define_access(p_terr_gp_id IN NUMBER,
2708 p_terr_gp_role_id IN NUMBER,
2709 p_role_code IN VARCHAR2,
2710 p_access_type IN VARCHAR2,
2711 p_user_id IN NUMBER,
2712 p_interest_type_id IN NUMBER DEFAULT NULL)
2713 AS
2714
2715 BEGIN
2716 /* create a role, assign accesses to role and assign prod interests to role */
2717
2718 /* create a role */
2719 INSERT INTO JTF_TTY_TERR_GRP_ROLES(
2720 TERR_GROUP_ROLE_ID,
2721 OBJECT_VERSION_NUMBER,
2722 TERR_GROUP_ID,
2723 ROLE_CODE,
2724 CREATED_BY,
2725 CREATION_DATE,
2726 LAST_UPDATED_BY,
2727 LAST_UPDATE_DATE,
2728 LAST_UPDATE_LOGIN)
2729 VALUES(
2730 p_terr_gp_role_id,
2731 1,
2732 p_terr_gp_id,
2733 p_role_code,
2734 p_user_id,
2735 SYSDATE,
2736 p_user_id,
2737 SYSDATE,
2738 p_user_id);
2739
2740 /* create role accesses for the role */
2741 INSERT INTO JTF_TTY_ROLE_ACCESS(
2742 TERR_GROUP_ROLE_ACCESS_ID,
2743 OBJECT_VERSION_NUMBER,
2744 TERR_GROUP_ROLE_ID ,
2745 ACCESS_TYPE ,
2746 CREATED_BY ,
2747 CREATION_DATE ,
2748 LAST_UPDATED_BY ,
2749 LAST_UPDATE_DATE ,
2750 LAST_UPDATE_LOGIN)
2751 VALUES(
2752 JTF_TTY_ROLE_ACCESS_S.NEXTVAL,
2753 1,
2754 p_terr_gp_role_id,
2755 p_access_type,
2756 p_user_id,
2757 SYSDATE,
2758 p_user_id,
2759 SYSDATE,
2760 p_user_id);
2761
2762 /* create product interests for the role */
2763 INSERT INTO JTF_TTY_ROLE_PROD_INT(
2764 TERR_GROUP_ROLE_PROD_INT_ID,
2765 OBJECT_VERSION_NUMBER ,
2766 TERR_GROUP_ROLE_ID ,
2767 INTEREST_TYPE_ID,
2768 CREATED_BY ,
2769 CREATION_DATE ,
2770 LAST_UPDATED_BY ,
2771 LAST_UPDATE_DATE ,
2772 LAST_UPDATE_LOGIN)
2773 VALUES(
2774 JTF_TTY_ROLE_PROD_INT_S.NEXTVAL,
2775 1,
2776 p_terr_gp_role_id,
2777 p_interest_type_id,
2778 p_user_id,
2779 SYSDATE,
2780 p_user_id,
2781 SYSDATE,
2782 p_user_id);
2783
2784 COMMIT;
2785
2786 END terrgp_define_access;
2787
2788 PROCEDURE delete_tgp_named_account(p_terr_gp_id IN NUMBER,
2789 p_party_id IN NUMBER,
2790 p_tga_id IN NUMBER)
2791 AS
2792
2793 BEGIN
2794
2795 -- delete assignment for the grp account
2796 DELETE FROM jtf_tty_named_acct_rsc j
2797 WHERE j.TERR_GROUP_ACCOUNT_ID = p_tga_id;
2798
2799 -- delete grp account
2800 DELETE FROM JTF_TTY_TERR_GRP_ACCTS
2801 WHERE terr_group_account_id = p_tga_id;
2802
2803 -- delete named account if no references to it exist
2804 DELETE FROM JTF_TTY_NAMED_ACCTS
2805 WHERE party_id = p_party_id
2806 AND party_id NOT IN
2807 (SELECT party_id FROM JTF_TTY_NAMED_ACCTS na, JTF_TTY_TERR_GRP_ACCTS tga
2808 WHERE tga.named_account_id = na.named_account_id);
2809
2810 COMMIT;
2811
2812 END delete_tgp_named_account;
2813
2814
2815 PROCEDURE create_acct_mappings(p_acct_id IN NUMBER,
2816 p_party_id IN NUMBER,
2817 p_user_id IN NUMBER)
2818 AS
2819 p_business_name VARCHAR2(360) DEFAULT NULL;
2820 p_trade_name VARCHAR2(240) DEFAULT NULL;
2821 p_postal_code VARCHAR2(60) DEFAULT NULL;
2822 p_party_count NUMBER;
2823
2824
2825 BEGIN
2826 BEGIN
2827
2828 SELECT H3.party_name,
2829 H3.known_as,
2830 H1.postal_code
2831 INTO p_business_name,
2832 p_trade_name,
2833 p_postal_code
2834 FROM HZ_PARTIES H3,
2835 HZ_LOCATIONS H1,
2836 HZ_PARTY_SITES H2
2837 WHERE h3.party_id = h2.party_id
2838 AND h2.location_id = h1.location_id
2839 AND h3.party_id = p_party_id
2840 AND h2.identifying_address_flag = 'Y';
2841
2842 EXCEPTION
2843 WHEN NO_DATA_FOUND THEN
2844 NULL;
2845 END;
2846 IF (p_business_name IS NOT NULL) THEN
2847 /* key name for business name */
2848 INSERT INTO jtf_tty_acct_qual_maps
2849 (ACCOUNT_QUAL_MAP_ID,
2850 OBJECT_VERSION_NUMBER,
2851 NAMED_ACCOUNT_ID,
2852 QUAL_USG_ID,
2853 COMPARISON_OPERATOR,
2854 VALUE1_CHAR,
2855 VALUE2_CHAR,
2856 CREATED_BY,
2857 CREATION_DATE,
2858 LAST_UPDATED_BY,
2859 LAST_UPDATE_DATE)
2860 (
2861 SELECT jtf_tty_acct_qual_maps_s.NEXTVAL,
2862 1,
2863 p_acct_id,
2864 -1012,
2865 '=',
2866 UPPER(p_business_name),
2867 NULL,
2868 p_user_id,
2869 SYSDATE,
2870 p_user_id,
2871 SYSDATE FROM dual);
2872 END IF;
2873 /* key name for trade name */
2874 IF (p_trade_name IS NOT NULL) THEN
2875 INSERT INTO jtf_tty_acct_qual_maps
2876 (ACCOUNT_QUAL_MAP_ID,
2877 OBJECT_VERSION_NUMBER,
2878 NAMED_ACCOUNT_ID,
2879 QUAL_USG_ID,
2880 COMPARISON_OPERATOR,
2881 VALUE1_CHAR,
2882 VALUE2_CHAR,
2883 CREATED_BY,
2884 CREATION_DATE,
2885 LAST_UPDATED_BY,
2886 LAST_UPDATE_DATE)
2887 (SELECT jtf_tty_acct_qual_maps_s.NEXTVAL,
2888 1,
2889 p_acct_id,
2890 -1012,
2891 '=',
2892 UPPER(p_trade_name),
2893 NULL,
2894 p_user_id,
2895 SYSDATE,
2896 p_user_id,
2897 SYSDATE FROM dual);
2898 END IF;
2899
2900 /* key name for postal code */
2901 IF (p_postal_code IS NOT NULL) THEN
2902 INSERT INTO jtf_tty_acct_qual_maps
2903 (ACCOUNT_QUAL_MAP_ID,
2904 OBJECT_VERSION_NUMBER,
2905 NAMED_ACCOUNT_ID,
2906 QUAL_USG_ID,
2907 COMPARISON_OPERATOR,
2908 VALUE1_CHAR,
2909 VALUE2_CHAR,
2910 CREATED_BY,
2911 CREATION_DATE,
2912 LAST_UPDATED_BY,
2913 LAST_UPDATE_DATE)
2914 (SELECT jtf_tty_acct_qual_maps_s.NEXTVAL,
2915 1,
2916 p_acct_id,
2917 -1007,
2918 '=',
2919 p_postal_code,
2920 NULL,
2921 p_user_id,
2922 SYSDATE,
2923 p_user_id,
2924 SYSDATE FROM dual);
2925 END IF;
2926 END create_acct_mappings;
2927
2928 PROCEDURE create_tgp_named_account(p_terr_gp_id IN NUMBER,
2929 p_party_id IN NUMBER,
2930 p_user_id IN NUMBER,
2931 x_gp_acct_id OUT NOCOPY NUMBER)
2932 AS
2933 p_site_type_code VARCHAR2(30);
2934 p_mapping_flag VARCHAR2(1);
2935 p_account_count NUMBER(30);
2936 p_grp_acct_count NUMBER(30);
2937 p_account_id NUMBER(30);
2938 p_terr_gp_acct_id NUMBER(30);
2939
2940 BEGIN
2941 BEGIN
2942 SELECT 1
2943 INTO p_account_count
2944 FROM jtf_tty_named_accts
2945 WHERE party_id = p_party_id
2946 AND ROWNUM < 2;
2947 EXCEPTION
2948 WHEN NO_DATA_FOUND THEN
2949 p_account_count := 0;
2950 END;
2951
2952
2953 p_site_type_code := get_site_type_code(p_party_id);
2954
2955 -- create a new named account for the party, if one does not exist
2956 -- create postal code and customer key names for a new account 2780737
2957 IF (p_account_count < 1) THEN
2958
2959 SELECT JTF_TTY_NAMED_ACCTS_S.NEXTVAL
2960 INTO p_account_id
2961 FROM dual;
2962 INSERT INTO jtf_tty_named_accts
2963 (NAMED_ACCOUNT_ID,
2964 OBJECT_VERSION_NUMBER ,
2965 PARTY_ID ,
2966 MAPPING_COMPLETE_FLAG,
2967 SITE_TYPE_CODE,
2968 CREATED_BY ,
2969 CREATION_DATE ,
2970 LAST_UPDATED_BY ,
2971 LAST_UPDATE_DATE ,
2972 LAST_UPDATE_LOGIN
2973 )
2974 VALUES(p_account_id,
2975 2,
2976 p_party_id,
2977 'N',
2978 p_site_type_code,
2979 p_user_id,
2980 SYSDATE,
2981 p_user_id,
2982 SYSDATE,
2983 p_user_id
2984 );
2985 p_mapping_flag := 'N';
2986 create_acct_mappings(p_account_id, p_party_id, p_user_id);
2987 ELSE
2988
2989 SELECT named_account_id, mapping_complete_flag
2990 INTO p_account_id, p_mapping_flag
2991 FROM jtf_tty_named_accts
2992 WHERE party_id = p_party_id;
2993
2994 END IF;
2995
2996 -- check if any terr gp account exists
2997 BEGIN
2998 SELECT 1
2999 INTO p_grp_acct_count
3000 FROM JTF_TTY_TERR_GRP_ACCTS tga, JTF_TTY_NAMED_ACCTS tna
3001 WHERE tga.named_account_id = tna.named_account_id
3002 AND tga.terr_group_id = p_terr_gp_id
3003 AND tna.party_id = p_party_id
3004 AND ROWNUM < 2;
3005 EXCEPTION
3006 WHEN NO_DATA_FOUND THEN
3007 p_grp_acct_count := 0;
3008 END;
3009
3010
3011 IF (p_grp_acct_count = 1) THEN
3012 SELECT tga.terr_group_account_id
3013 INTO p_terr_gp_acct_id
3014 FROM JTF_TTY_TERR_GRP_ACCTS tga, JTF_TTY_NAMED_ACCTS tna
3015 WHERE tga.named_account_id = tna.named_account_id
3016 AND tga.terr_group_id = p_terr_gp_id
3017 AND tna.party_id = p_party_id;
3018 x_gp_acct_id := p_terr_gp_acct_id;
3019 END IF;
3020
3021 SELECT JTF_TTY_TERR_GRP_ACCTS_S.NEXTVAL
3022 INTO p_terr_gp_acct_id
3023 FROM dual;
3024
3025 -- assign a named account for the party to terr gp, if one does not exist
3026 IF (p_grp_acct_count < 1) THEN
3027 x_gp_acct_id := p_terr_gp_acct_id;
3028 INSERT INTO JTF_TTY_TERR_GRP_ACCTS
3029 (TERR_GROUP_ACCOUNT_ID,
3030 OBJECT_VERSION_NUMBER ,
3031 TERR_GROUP_ID ,
3032 NAMED_ACCOUNT_ID,
3033 DN_JNA_MAPPING_COMPLETE_FLAG,
3034 DN_JNA_SITE_TYPE_CODE,
3035 DN_JNR_ASSIGNED_FLAG ,
3036 CREATED_BY ,
3037 CREATION_DATE ,
3038 LAST_UPDATED_BY ,
3039 LAST_UPDATE_DATE ,
3040 LAST_UPDATE_LOGIN
3041 )
3042 VALUES(p_terr_gp_acct_id,
3043 2,
3044 p_terr_gp_id,
3045 p_account_id,
3046 p_mapping_flag,
3047 p_site_type_code,
3048 'N',
3049 p_user_id,
3050 SYSDATE,
3051 p_user_id,
3052 SYSDATE,
3053 p_user_id
3054 );
3055
3056 END IF;
3057
3058 COMMIT;
3059
3060 END create_tgp_named_account;
3061
3062 PROCEDURE add_orgs_to_terrgp(p_terr_gp_id IN NUMBER,
3063 p_party_id IN NUMBER,
3064 p_resource_id IN NUMBER,
3065 p_role_id IN NUMBER,
3066 p_user_id IN NUMBER,
3067 p_rsc_group_id IN NUMBER)
3068 AS
3069 p_site_type_code VARCHAR2(30);
3070 p_account_count NUMBER(30);
3071 p_rsc_acct_count NUMBER(30);
3072 p_account_id NUMBER(30);
3073 p_terr_gp_acct_id NUMBER(30);
3074 p_terr_gp_acct_rsc_id NUMBER(30);
3075 p_terr_gp_acct_rsc_dn_id NUMBER(30) := 0;
3076
3077 BEGIN
3078
3079 SELECT COUNT(*)
3080 INTO p_account_count
3081 FROM jtf_tty_named_accts
3082
3083 WHERE party_id = p_party_id;
3084 -- create a new named account for the party, if one does not exist
3085 SELECT JTF_TTY_NAMED_ACCTS_S.NEXTVAL
3086 INTO p_account_id
3087 FROM dual;
3088
3089 p_site_type_code := get_site_type_code(p_party_id);
3090
3091 IF (p_account_count < 1) THEN
3092
3093 INSERT INTO jtf_tty_named_accts
3094 (NAMED_ACCOUNT_ID,
3095 OBJECT_VERSION_NUMBER ,
3096 PARTY_ID ,
3097 MAPPING_COMPLETE_FLAG,
3098 SITE_TYPE_CODE,
3099 CREATED_BY ,
3100 CREATION_DATE ,
3101 LAST_UPDATED_BY ,
3102 LAST_UPDATE_DATE ,
3103 LAST_UPDATE_LOGIN
3104 )
3105 VALUES(p_account_id,
3106 2,
3107 p_party_id,
3108 'N',
3109 p_site_type_code,
3110 p_user_id,
3111 SYSDATE,
3112 p_user_id,
3113 SYSDATE,
3114 p_user_id
3115 );
3116 END IF;
3117
3118 -- check if any terr gp account exists
3119
3120 SELECT COUNT(tga.terr_group_account_id)
3121 INTO p_account_count
3122 FROM JTF_TTY_TERR_GRP_ACCTS tga, JTF_TTY_NAMED_ACCTS tna
3123 WHERE tga.named_account_id = tna.named_account_id
3124 AND tna.party_id = p_party_id;
3125
3126 SELECT JTF_TTY_TERR_GRP_ACCTS_S.NEXTVAL
3127 INTO p_terr_gp_acct_id
3128 FROM dual;
3129 -- assign a named account for the party to terr gp, if one does not exist
3130
3131 p_site_type_code := get_site_type_code(p_party_id);
3132
3133 IF (p_account_count < 1) THEN
3134
3135 INSERT INTO JTF_TTY_TERR_GRP_ACCTS
3136 (TERR_GROUP_ACCOUNT_ID,
3137 OBJECT_VERSION_NUMBER ,
3138 TERR_GROUP_ID ,
3139 NAMED_ACCOUNT_ID,
3140 DN_JNA_SITE_TYPE_CODE,
3141 DN_JNR_ASSIGNED_FLAG ,
3142 CREATED_BY ,
3143 CREATION_DATE ,
3144 LAST_UPDATED_BY ,
3145 LAST_UPDATE_DATE ,
3146 LAST_UPDATE_LOGIN
3147 )
3148 VALUES(p_terr_gp_acct_id,
3149 2,
3150 p_terr_gp_id,
3151 p_account_id,
3152 p_site_type_code,
3153 'N',
3154 p_user_id,
3155 SYSDATE,
3156 p_user_id,
3157 SYSDATE,
3158 p_user_id
3159 );
3160 END IF;
3161 -- assign resource to the named account
3162
3163 SELECT jtf_tty_named_acct_rsc_s.NEXTVAL
3164 INTO p_terr_gp_acct_rsc_id
3165 FROM dual;
3166
3167 INSERT INTO jtf_tty_named_acct_rsc
3168 (ACCOUNT_RESOURCE_ID,
3169 OBJECT_VERSION_NUMBER ,
3170 TERR_GROUP_ACCOUNT_ID,
3171 RESOURCE_ID ,
3172 RSC_GROUP_ID,
3173 RSC_ROLE_CODE,
3174 ASSIGNED_FLAG ,
3175 RSC_RESOURCE_TYPE,
3176 CREATED_BY ,
3177 CREATION_DATE ,
3178 LAST_UPDATED_BY ,
3179 LAST_UPDATE_DATE ,
3180 LAST_UPDATE_LOGIN
3181 )
3182 VALUES(p_terr_gp_acct_rsc_id,
3183 2,
3184 p_terr_gp_acct_id,
3185 p_resource_id,
3186 p_rsc_group_id,
3187 p_role_id,
3188 'N',
3189 'RS_EMPLOYEE',
3190 p_user_id,
3191 SYSDATE,
3192 p_user_id,
3193 SYSDATE,
3194 p_user_id
3195 );
3196 /* commenting for now */
3197 /*
3198 select jtf_tty_acct_rsc_dn_s.nextval
3199 into p_terr_gp_acct_rsc_dn_id
3200 from dual;
3201 insert into jtf_tty_acct_rsc_dn
3202 (ACCOUNT_RESOURCE_DN_ID,
3203 OBJECT_VERSION_NUMBER ,
3204 TERR_GROUP_ACCOUNT_ID,
3205 RESOURCE_ID ,
3206 RSC_GROUP_ID,
3207 RSC_ROLE_CODE,
3208 RSC_RESOURCE_TYPE,
3209 CREATED_BY ,
3210 CREATION_DATE ,
3211 LAST_UPDATED_BY ,
3212 LAST_UPDATE_DATE ,
3213 LAST_UPDATE_LOGIN
3214 )
3215 VALUES(p_terr_gp_acct_rsc_dn_id,
3216 2,
3217 p_terr_gp_acct_id,
3218 p_resource_id,
3219 p_rsc_group_id,
3220 p_role_id,
3221 'RS_EMPLOYEE',
3222 p_user_id,
3223 sysdate,
3224 p_user_id,
3225 sysdate,
3226 p_user_id
3227 );
3228 commit;
3229 */
3230 /*
3231 select count(*)
3232 into p_rsc_acct_count
3233 from jtf_tty_rsc_acct_summ
3234 where resource_id = p_resource_id
3235 and (rsc_group_id = p_rsc_group_id or p_rsc_group_id is null)
3236 and rsc_resource_type = 'RS_EMPLOYEE'
3237 and site_type_code = p_site_type_code;
3238 */
3239 -- if does not exist, create a new entry, else update the count for
3240 -- correct row
3241 /*
3242 if (p_rsc_acct_count = 0) then
3243 insert into jtf_tty_rsc_acct_summ
3244 (RESOURCE_ACCT_SUMM_ID,
3245 OBJECT_VERSION_NUMBER ,
3246 RESOURCE_ID ,
3247 RSC_GROUP_ID,
3248 RSC_RESOURCE_TYPE,
3249 SITE_TYPE_CODE,
3250 NUMBER_ACCOUNTS,
3251 CREATED_BY ,
3252 CREATION_DATE ,
3253 LAST_UPDATED_BY ,
3254 LAST_UPDATE_DATE ,
3255 LAST_UPDATE_LOGIN
3256 )
3257 VALUES(p_terr_gp_acct_rsc_dn_id,
3258 2,
3259 p_resource_id,
3260 p_rsc_group_id,
3261 'RS_EMPLOYEE',
3262 p_site_type_code,
3263 0,
3264 p_user_id,
3265 sysdate,
3266 p_user_id,
3267 sysdate,
3268 p_user_id);
3269
3270 else
3271
3272 update jtf_tty_rsc_acct_summ
3273 set NUMBER_ACCOUNTS = NUMBER_ACCOUNTS + 1
3274 where resource_id = p_resource_id
3275 and rsc_resource_type = 'RS_EMPLOYEE'
3276 and site_type_code = p_site_type_code
3277 and (rsc_group_id = p_rsc_group_id or p_rsc_group_id is null);
3278 end if;
3279
3280 commit;
3281 */
3282 END add_orgs_to_terrgp;
3283
3284 PROCEDURE terrgp_assign_owners(p_terr_gp_id IN NUMBER,
3285 p_rsc_gp_id IN NUMBER,
3286 p_resource_id IN NUMBER,
3287 p_role_code IN VARCHAR2,
3288 p_user_id IN NUMBER,
3289 p_resource_type IN VARCHAR2 DEFAULT 'RS_EMPLOYEE')
3290 AS
3291
3292 BEGIN
3293 INSERT INTO jtf_tty_terr_grp_owners(
3294 TERR_GROUP_OWNER_ID,
3295 OBJECT_VERSION_NUMBER,
3296 TERR_GROUP_ID,
3297 RSC_GROUP_ID,
3298 RESOURCE_ID,
3299 CREATED_BY,
3300 CREATION_DATE,
3301 LAST_UPDATED_BY,
3302 LAST_UPDATE_DATE,
3303 LAST_UPDATE_LOGIN,
3304 RSC_ROLE_CODE ,
3305 RSC_RESOURCE_TYPE
3306 )
3307 VALUES(jtf_tty_terr_grp_owners_s.NEXTVAL,
3308 1,
3309 p_terr_gp_id,
3310 p_rsc_gp_id,
3311 p_resource_id,
3312 p_user_id,
3313 SYSDATE,
3314 p_user_id,
3315 SYSDATE,
3316 p_user_id,
3317 p_role_code,
3318 'RS_EMPLOYEE');
3319
3320 COMMIT;
3321 END terrgp_assign_owners;
3322
3323
3324 /* SHLI GSST decom. */
3325 PROCEDURE log_event(p_object_id IN NUMBER,
3326 p_action_type IN VARCHAR2,
3327 p_from_where IN VARCHAR2,
3328 p_object_type IN VARCHAR2,
3329 p_user_id IN NUMBER)
3330 IS
3331 BEGIN
3332 INSERT INTO JTF_TTY_NAMED_ACCT_CHANGES(
3333 NAMED_ACCT_CHANGE_ID,
3334 OBJECT_VERSION_NUMBER,
3335 OBJECT_TYPE,
3336 OBJECT_ID,
3337 CHANGE_TYPE,
3338 FROM_WHERE,
3339 CREATED_BY,
3340 CREATION_DATE,
3341 LAST_UPDATED_BY,
3342 LAST_UPDATE_DATE
3343 )
3344 VALUES(JTF_TTY_NAMED_ACCT_CHANGES_S.NEXTVAL,
3345 1,
3346 p_object_type,
3347 p_object_id,
3348 p_action_type,
3349 p_from_where,
3350 p_user_id,
3351 SYSDATE,
3352 p_user_id,
3353 SYSDATE);
3354
3355 COMMIT;
3356 END log_event;
3357
3358 end JTF_TTY_NA_TERRGP;