[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_ENGINE_GEN_PVT
Source
1 Package Body JTF_TERR_ENGINE_GEN_PVT AS
2 /* $Header: jtfvtegb.pls 120.3.12010000.2 2008/11/27 07:00:43 gmarwah ship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTF_TERR_ENGINE_GEN_PVT
6 -- ---------------------------------------------------
7 -- PURPOSE
8 -- Joint task force core territory manager public api's.
9 -- This packe is used to generate the complete territory
10 -- Engine based on tha data setup in the JTF territory tables
11 --
12 -- Procedures:
13 -- (see below for specification)
14 --
15 -- NOTES
16 -- This package is publicly available for use
17 --
18 -- HISTORY
19 -- 01/27/00 VNEDUNGA Created
20 -- 02/24/00 VNEDUNGA A New beginnig
21 -- 03/20/00 VNEDUNGA Replace ' in a char value to ''
22 -- Changing the add_terr_pkgspec to
23 -- a pointer to track pl/sql table
24 -- index
25 -- 04/10/00 VNEDUNGA Adding special processing for
26 -- qualifers that have master detail
27 -- relationship
28 -- 04/14/00 VNEDUNGA Changing the code to use the meta data
29 -- for special processing
30 -- 05/01/00 VNEDUNGA Adding currency convertion routine
31 -- for Currency Type Qualifier
32 -- 05/17/00 VNEDUNGA Fixed code in get_expression_char function
33 -- to eliminate extar space in the value
34 -- eg: '94089 '
35 -- 07/05/00 jdochert Removed hard-coded reference to APPS (Bug#1343904)
36 -- Added call to fnd_installation.get_app_info instead
37 --
38 -- 09/17/00 JDOCHERT BUG#1408610 FIX
39 --
40 -- 04/24/03 JRADHAKR BUG#2925153 FIX
41 --
42 -- 05/28/03 JDOCHERT DUNS# QUALIFIER SUPPORT
43 --
44 -- 03/08/04 ACHANDA BUG#3380047, 3378530 FIX
45 --
46 -- End of Comments
47 --
48 --------------------------------------------------
49 --- GLOBAL Declarations Starts here -----
50 --------------------------------------------------
51
52 -- Stores the org_id for use in package Names
53 g_cached_org_append VARCHAR2(15);
54 --
55 -- Identifies the Package associated a
56 -- a territory with child nodes
57 g_terr_pkgspec terr_pkgspec_tbl_type;
58
59 -- Stores the position with the table spec
60 g_stack_pointer NUMBER := 0;
61
62 -- Store the information passed as
63 -- Concurrent program parameters
64 -- Module that uses Territories
65 g_source_id NUMBER := 0;
66
67 g_abs_source_id NUMBER := 0;
68
69 -- Type of transaction for which the
70 -- the package is being generated
71 g_qualifier_type VARCHAR2(60);
72
73 -- Id of the corresponding transaction type
74 g_qual_type_id NUMBER := 0;
75
76 TYPE t_pkgname IS TABLE OF VARCHAR2(256)
77 INDEX BY BINARY_INTEGER;
78
79 g_pkgname_tbl t_pkgname;
80 g_Pointer NUMBER := 0;
81 G_Debug BOOLEAN := FALSE;
82 g_ProgramStatus NUMBER := 0;
83
84 /* Global System Variables */
85 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
86 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
87 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
88 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
89 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
90 G_APP_SHORT_NAME VARCHAR2(15) := FND_GLOBAL.Application_Short_Name;
91 G_SYSDATE DATE := SYSDATE;
92
93
94 --------------------------------------------------------------------
95 -- Logging PROCEDURE
96 --
97 -- which = 1. write to log
98 -- which = 2, write to output
99 --------------------------------------------------------------------
100 --
101 PROCEDURE Write_Log(which number, mssg varchar2 ) IS
102
103 l_mssg VARCHAR2(4000);
104 l_sub_mssg VARCHAR2(255);
105 l_begin NUMBER := 1;
106 l_mssg_length NUMBER := 0;
107 l_time VARCHAR2(60) := TO_CHAR(SYSDATE, 'mm/dd/yyyy hh24:mi:ss');
108
109 BEGIN
110 --
111 l_mssg := mssg;
112
113 /* If the output message and if debug flag is set then also write
114 ** to the log file
115 */
116 If Which = 2 Then
117 FND_FILE.PUT(1, mssg);
118 FND_FILE.NEW_LINE(1, 1);
119 End IF;
120
121 l_sub_mssg := 'Time = ' || l_time;
122 --FND_FILE.PUT_LINE(FND_FILE.LOG, l_sub_mssg);
123 --dbms_output.put_line('LOG: ' || l_sub_mssg);
124
125 l_mssg := l_sub_mssg || ' => ' || l_mssg;
126
127 /* get total message length */
128 l_mssg_length := LENGTH(l_mssg);
129
130 /* Output message in 250 maximum character lines */
131 WHILE ( l_mssg_length > 250 ) LOOP
132
133 /* get message substring */
134 l_sub_mssg := SUBSTR(l_mssg, l_begin, 250);
135
136 /* write message to log file */
137 FND_FILE.PUT_LINE(FND_FILE.LOG, l_sub_mssg);
138 --dbms_output.put_line('LOG: ' || l_mssg );
139
140 /* Increment message start position to output from */
141 l_begin := l_begin + 250;
142
143 /* Decrement message length to be output */
144 l_mssg_length := l_mssg_length - 250;
145
146 END LOOP;
147
148 /* get last remaining part of message, i.e, when
149 ** there is less than 250 characters left to be output */
150 l_sub_mssg := SUBSTR(l_mssg, l_begin);
151 FND_FILE.PUT_LINE(FND_FILE.LOG, l_sub_mssg);
152 --dbms_output.put_line('LOG: ' || l_mssg );
153 --
154 END Write_Log;
155
156
157 /* (1) START: ENABLE/DISABLE TERRITORY TRIGGERS */
158 PROCEDURE alter_triggers(p_status VARCHAR2)
159 IS
160 BEGIN
161
162 IF (p_status = 'DISABLE') THEN
163
164 BEGIN
165 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORIES_BIUD DISABLE';
166 EXCEPTION
167 WHEN OTHERS THEN
168 NULL;
169 END;
170
171 BEGIN
172 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORY_RSC_BIUD DISABLE';
173 EXCEPTION
174 WHEN OTHERS THEN
175 NULL;
176 END;
177
178 BEGIN
179 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORY_VALUES_BIUD DISABLE';
180 EXCEPTION
181 WHEN OTHERS THEN
182 NULL;
183 END;
184
185 BEGIN
186 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD DISABLE';
187 EXCEPTION
188 WHEN OTHERS THEN
189 NULL;
190 END;
191
192 BEGIN
193 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_RSC_ACCESS_BIUD DISABLE';
194 EXCEPTION
195 WHEN OTHERS THEN
196 NULL;
197 END;
198
199 ELSIF (p_status = 'ENABLE') THEN
200
201 BEGIN
202 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORIES_BIUD ENABLE';
203 EXCEPTION
204 WHEN OTHERS THEN
205 NULL;
206 END;
207
208 BEGIN
209 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORY_RSC_BIUD ENABLE';
210 EXCEPTION
211 WHEN OTHERS THEN
212 NULL;
213 END;
214
215 BEGIN
216 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORY_VALUES_BIUD ENABLE';
217 EXCEPTION
218 WHEN OTHERS THEN
219 NULL;
220 END;
221
222 BEGIN
223 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD ENABLE';
224 EXCEPTION
225 WHEN OTHERS THEN
226 NULL;
227 END;
228
229 BEGIN
230 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_RSC_ACCESS_BIUD ENABLE';
231 EXCEPTION
232 WHEN OTHERS THEN
233 NULL;
234 END;
235
236 END IF;
237
238 END alter_triggers;
239
240
241 /* (1) START: DELETE ALL EXISTING NAMED ACCOUNT TERRITORIES */
242 PROCEDURE cleanup_na_territories ( p_mode VARCHAR2 )
243 IS
244
245 /* get all the Territories to DELETE */
246 CURSOR delterr IS
247 SELECT terr_id
248 from jtf_terr_all
249 where terr_group_flag = 'Y';
250
251 BEGIN
252
253 /* TOTAL mode => re-generate all NA territories */
254 IF (p_mode = 'TOTAL') THEN
255
256 --DELETE territory value records
257 DELETE FROM jtf_terr_values_all jtv
258 WHERE jtv.terr_qual_id IN
259 ( SELECT jtq.terr_qual_id
260 FROM jtf_terr_qual_all jtq, jtf_terr_all jt
261 WHERE jtq.terr_id = jt.terr_id
262 AND jt.terr_group_flag = 'Y' );
263
264 --Delete Territory Qualifer records
265 DELETE from JTF_TERR_QUAL_ALL jtq
266 WHERE jtq.terr_id IN
267 ( SELECT jt.terr_id
268 FROM jtf_terr_all jt
269 WHERE jt.terr_group_flag = 'Y' );
270
271
272 --Delete Territory qual type usgs
273 DELETE from JTF_TERR_QTYPE_USGS_ALL jtqu
274 WHERE jtqu.terr_id IN
275 ( SELECT jt.terr_id
276 FROM jtf_terr_all jt
277 WHERE jt.terr_group_flag = 'Y' );
278
279
280 --Delete Territory usgs
281 DELETE from JTF_TERR_USGS_ALL jtu
282 WHERE jtu.terr_id IN
283 ( SELECT jt.terr_id
284 FROM jtf_terr_all jt
285 WHERE jt.terr_group_flag = 'Y' );
286
287
288 --Delete Territory Resource Access
289 DELETE from JTF_TERR_RSC_ACCESS_ALL jtra
290 WHERE jtra.terr_rsc_id IN
291 ( SELECT jtr.terr_rsc_id
292 FROM jtf_terr_rsc_all jtr, jtf_terr_all jt
293 WHERE jtr.terr_id = jt.terr_id
294 AND jt.terr_group_flag = 'Y' );
295
296
297 -- Delete the Territory Resource records
298 DELETE from JTF_TERR_RSC_ALL jtr
299 WHERE jtr.terr_id IN
300 ( SELECT jt.terr_id
301 FROM jtf_terr_all jt
302 WHERE jt.terr_group_flag = 'Y' );
303
304
305 --Delete Territory record
306 DELETE from JTF_TERR_ALL jt
307 WHERE jt.terr_id IN
308 ( SELECT jt.terr_id
309 FROM jtf_terr_all jt
310 WHERE jt.terr_group_flag = 'Y' );
311
312
313 END IF;
314 /* (1) END: DELETE ALL EXISTING NAMED ACCOUNT TERRITORIES */
315
316 EXCEPTION
317 WHEN NO_DATA_FOUND THEN
318 NULL;
319
320 END cleanup_na_territories;
321
322
323
324 /*----------------------------------------------------------
325 This procedure will create Named account and Overlay Territory
326 from the Named accounts.
327 ----------------------------------------------------------*/
328 PROCEDURE generate_named_overlay_terr(p_mode VARCHAR2)
329 IS
330
331
332 TYPE terrqual_type IS RECORD(
333 terr_qual_id NUMBER:=FND_API.G_MISS_NUM
334 );
335
336 TYPE terrqual_tbl_type IS TABLE OF terrqual_type
337 INDEX BY BINARY_INTEGER;
338
339
340 TYPE seeded_qual_type IS RECORD(
341 seeded_qualifier_id NUMBER:=FND_API.G_MISS_NUM
342 );
343
344 TYPE seeded_qual_tbl_type IS TABLE OF seeded_qual_type
345 INDEX BY BINARY_INTEGER;
346
347 TYPE role_typ IS RECORD(
348 grp_role_id NUMBER:=FND_API.G_MISS_NUM
349 );
350
351 TYPE grp_role_tbl_type IS TABLE OF role_typ
352 INDEX BY BINARY_INTEGER;
353
354 l_terrqual_tbl terrqual_tbl_type;
355 l_terrqual_empty_tbl terrqual_tbl_type;
356
357 l_overnon_role_tbl grp_role_tbl_type;
358 l_overnon_role_empty_tbl grp_role_tbl_type;
359
360 l_terr_qual_id NUMBER;
361 l_id_used_flag VARCHAR2(1);
362 l_low_value_char_id NUMBER;
363 l_qual_usgs_id NUMBER;
364 l_terr_usg_id NUMBER;
365 l_qual_type_usg_id NUMBER;
366 l_terr_qtype_usg_id NUMBER;
367 l_terr_type_usg_id NUMBER;
368 l_type_qtype_usg_id NUMBER;
369 l_terr_rsc_id NUMBER;
370 l_terr_rsc_access_id NUMBER;
371 l_access_type VARCHAR2(30);
372
373 l_api_version_number CONSTANT NUMBER := 1.0;
374 l_init_msg_list varchar2(1);
375 l_commit varchar2(1);
376 x_return_status varchar2(1);
377 x_msg_count number;
378 x_msg_data varchar2(2000);
379
380 i NUMBER;
381 j NUMBER;
382 k NUMBER;
383 l NUMBER;
384 a NUMBER;
385
386 l_prev_seedqual number;
387 l_prev_terr_id number;
388
389 l_qualifier NUMBER;
390
391 x_terr_id NUMBER;
392
393 l_terr_all_rec JTF_TERRITORY_PVT.terr_all_rec_type;
394 l_terr_usgs_tbl JTF_TERRITORY_PVT.terr_usgs_tbl_type;
395 l_terr_qualtypeusgs_tbl JTF_TERRITORY_PVT.terr_qualtypeusgs_tbl_type;
396 l_terr_qual_tbl JTF_TERRITORY_PVT.terr_qual_tbl_type;
397 l_terr_values_tbl JTF_TERRITORY_PVT.terr_values_tbl_type;
398
399 /* Customer Name Range + Postal Code Qualifier Support */
400 l_terr_qual_tbl_mc1 JTF_TERRITORY_PVT.terr_qual_tbl_type;
401 l_terr_values_tbl_mc1 JTF_TERRITORY_PVT.terr_values_tbl_type;
402 /* DUNS# Qualifier Support */
403 l_terr_qual_tbl_mc2 JTF_TERRITORY_PVT.terr_qual_tbl_type;
404 l_terr_values_tbl_mc2 JTF_TERRITORY_PVT.terr_values_tbl_type;
405
406 l_terr_usgs_empty_tbl JTF_TERRITORY_PVT.terr_usgs_tbl_type;
407 l_terr_qualtypeusgs_empty_tbl JTF_TERRITORY_PVT.terr_qualtypeusgs_tbl_type;
408 l_terr_qual_empty_tbl JTF_TERRITORY_PVT.terr_qual_tbl_type;
409 l_terr_values_empty_tbl JTF_TERRITORY_PVT.terr_values_tbl_type;
410
411 x_terr_usgs_out_tbl JTF_TERRITORY_PVT.terr_usgs_out_tbl_type;
412 x_terr_qualtypeusgs_out_tbl JTF_TERRITORY_PVT.terr_qualtypeusgs_out_tbl_type;
413 x_terr_qual_out_tbl JTF_TERRITORY_PVT.terr_qual_out_tbl_type;
414 x_terr_values_out_tbl JTF_TERRITORY_PVT.terr_values_out_tbl_type;
415
416 l_TerrRsc_Tbl JTF_TERRITORY_RESOURCE_PVT.TerrResource_tbl_type;
417 l_TerrRsc_Access_Tbl JTF_TERRITORY_RESOURCE_PVT.TerrRsc_Access_tbl_type ;
418 l_TerrRsc_empty_Tbl JTF_TERRITORY_RESOURCE_PVT.TerrResource_tbl_type;
419 l_TerrRsc_Access_empty_Tbl JTF_TERRITORY_RESOURCE_PVT.TerrRsc_Access_tbl_type ;
420 x_TerrRsc_Out_Tbl JTF_TERRITORY_RESOURCE_PVT.TerrResource_out_tbl_type;
421 x_TerrRsc_Access_Out_Tbl JTF_TERRITORY_RESOURCE_PVT.TerrRsc_Access_out_tbl_type;
422
423 l_commitcount NUMBER := 1000;
424 l_row_inserted NUMBER := 0;
425 l_pi_count NUMBER := 0;
426 l_prev_qual_usg_id NUMBER;
427 l_na_catchall_flag VARCHAR2(1);
428 l_overlap_catchall_flag VARCHAR2(1);
429
430 l_role_counter NUMBER := 0;
431
432
433 /* Active Territory Groups with
434 ** Active Top-Level Territories */
435 /* bug#2933116: JDOCHERT: 05/27/03: support for DUNS# Qualifier */
436 CURSOR grp IS
437 SELECT A.TERR_GROUP_ID
438 , A.TERR_GROUP_NAME
439 , A.RANK
440 , A.ACTIVE_FROM_DATE
441 , A.ACTIVE_TO_DATE
442 , A.PARENT_TERR_ID
443 , A.MATCHING_RULE_CODE
444 , A.CREATED_BY
445 , A.CREATION_DATE
446 , A.LAST_UPDATED_BY
447 , A.LAST_UPDATE_DATE
448 , A.LAST_UPDATE_LOGIN
449 , A.Catch_all_resource_id
450 , A.catch_all_resource_type
451 , A.generate_catchall_flag
452 , A.NUM_WINNERS /* JDOCHERT: 07/29/03: BUG#3072230 */
453 , B.ORG_ID
454 FROM JTF_TTY_TERR_GROUPS A
455 , JTF_TERR_ALL B
456 WHERE A.parent_terr_id = b.terr_id
457 AND ( a.active_to_date >= SYSDATE OR a.active_to_date IS NULL )
458 AND a.active_from_date <= SYSDATE;
459
460 /* JDOCHERT: /05/29/03:
461 ** Transaction Types for a NON-OVERLAY territory are
462 ** determined by all salesteam members on this Named Account
463 ** having Roles without Product Interests defined
464 ** so there is no Overlay Territories to assign
465 ** Leads and Opportunities. If all Roles have Product Interests
466 ** then only ACCOUNT transaction type should
467 ** be used in Non-Overlay Named Account definition
468 */
469 CURSOR get_NON_OVLY_na_trans(LP_terr_group_account_id NUMBER) IS
470 SELECT ra.access_type
471 FROM
472 jtf_tty_named_acct_rsc nar
473 , jtf_tty_terr_grp_accts tga
474 , jtf_tty_terr_grp_roles tgr
475 , jtf_tty_role_access ra
476 WHERE tga.terr_group_account_id = nar.terr_group_account_id
477 AND nar.terr_group_account_id = LP_terr_group_account_id
478 AND nar.rsc_role_code = tgr.role_code
479 AND ra.terr_group_role_id = tgr.terr_group_role_id
480 AND ra.access_type IN ('ACCOUNT')
481 UNION
482 SELECT ra.access_type
483 FROM
484 jtf_tty_named_acct_rsc nar
485 , jtf_tty_terr_grp_accts tga
486 , jtf_tty_terr_grp_roles tgr
487 , jtf_tty_role_access ra
488 WHERE tga.terr_group_account_id = nar.terr_group_account_id
489 AND nar.terr_group_account_id = LP_terr_group_account_id
490 AND nar.rsc_role_code = tgr.role_code
491 AND ra.terr_group_role_id = tgr.terr_group_role_id
492 AND NOT EXISTS (
493 SELECT NULL
494 FROM jtf_tty_role_prod_int rpi
495 WHERE rpi.terr_group_role_id = tgr.terr_group_role_id );
496
497
498 /* Access Types for a Territory Group */
499 CURSOR na_access(l_terr_group_id number) IS
500 SELECT distinct a.access_type
501 from jtf_tty_role_access a
502 , jtf_tty_terr_grp_roles b
503 where a.terr_group_role_id = b.terr_group_role_id
504 and b.terr_group_id = l_terr_group_id;
505
506 /* Access Types for a particular Role within a Territory Group */
507 CURSOR NON_OVLY_role_access( lp_terr_group_id number
508 , lp_role varchar2) IS
509 SELECT distinct a.access_type
510 from jtf_tty_role_access a
511 , jtf_tty_terr_grp_roles b
512 where a.terr_group_role_id = b.terr_group_role_id
513 and b.terr_group_id = lp_terr_group_id
514 and b.role_code = lp_role
515 AND NOT EXISTS (
516 /* Product Interest does not exist for this role */
517 SELECT NULL
518 FROM jtf_tty_role_prod_int rpi
519 WHERE rpi.terr_group_role_id = B.TERR_GROUP_ROLE_ID )
520 order by a.access_type ;
521
522
523 /* Access Types for a particular Role within a Territory Group */
524 CURSOR role_access(l_terr_group_id number,l_role varchar2) IS
525 SELECT distinct a.access_type
526 from jtf_tty_role_access a
527 , jtf_tty_terr_grp_roles b
528 where a.terr_group_role_id = b.terr_group_role_id
529 and b.terr_group_id = l_terr_group_id
530 and b.role_code = l_role
531 order by a.access_type ;
532
533 /* Roles WITHOUT a Product Iterest defined */
534 CURSOR role_interest_nonpi(l_terr_group_id number) IS
535 SELECT b.role_code role_code
536 --,a.interest_type_id
537 ,b.terr_group_id
538 from jtf_tty_role_prod_int a
539 , jtf_tty_terr_grp_roles b
540 where a.terr_group_role_id(+) = b.terr_group_role_id
541 and b.terr_group_id = l_terr_group_id
542 and a.terr_group_role_id is null
543 order by b.role_code;
544
545 /* Roles WITH a Product Iterest defined */
546 CURSOR role_pi( lp_terr_group_id NUMBER
547 , lp_terr_group_account_id NUMBER) IS
548 SELECT distinct
549 b.role_code role_code
550 , r.role_name role_name
551 from jtf_rs_roles_vl r
552 , jtf_tty_role_prod_int a
553 , jtf_tty_terr_grp_roles b
554 where r.role_code = b.role_code
555 and a.terr_group_role_id = b.terr_group_role_id
556 and b.terr_group_id = lp_terr_group_id
557 AND EXISTS (
558 /* Named Account exists with Salesperson with this role */
559 SELECT NULL
560 FROM jtf_tty_named_acct_rsc nar, jtf_tty_terr_grp_accts tga
561 WHERE tga.terr_group_account_id = nar.terr_group_account_id
562 AND nar.terr_group_account_id = lp_terr_group_account_id
563 AND tga.terr_group_id = b.terr_group_id
564 AND nar.rsc_role_code = b.role_code );
565
566 /* Product Interest for a Role */
567 CURSOR role_pi_interest(l_terr_group_id number,l_role varchar2) IS
568 SELECT a.interest_type_id
569 from jtf_tty_role_prod_int a
570 , jtf_tty_terr_grp_roles b
571 where a.terr_group_role_id = b.terr_group_role_id
572 and b.terr_group_id = l_terr_group_id
573 and b.role_code = l_role;
574
575 /* Named Account Catch-All Customer Keyname values */
576 CURSOR catchall_cust(l_terr_group_id number) IS
577 SELECT distinct b.comparison_operator
578 ,b.value1_char
579 from jtf_tty_terr_grp_accts a
580 , jtf_tty_acct_qual_maps b
581 where a.named_account_id = b.named_account_id
582 and a.terr_group_id = l_terr_group_id
583 and b.qual_usg_id = -1012
584 order by b.comparison_operator,b.value1_char;
585
586 /* JRADHAKR changed the parameter from l_terr_group_id to l_terr_group_acct_id
587 since the resource is specific for a terr_group_account */
588 CURSOR resource_grp(l_terr_group_acct_id number,l_role varchar2) IS
589 SELECT distinct b.resource_id
590 , b.rsc_group_id
591 , b.rsc_resource_type
592 from jtf_tty_terr_grp_accts a
593 , jtf_tty_named_acct_rsc b
594 where a.terr_group_account_id = l_terr_group_acct_id
595 and a.terr_group_account_id = b.terr_group_account_id
596 and b.rsc_role_code = l_role;
597
598 /* Should Unassigned NAs go to Sales Manager or NA Catch-All? */
599 -- WHERE c.dn_jnr_assigned_flag = 'Y';
600
601
602 /* get the DUNS# for the Named Account:
603 ** used for NAMED ACCOUNT territory creation */
604 CURSOR get_party_duns(LP_terr_group_id number) IS
605 SELECT substr(a.party_name, 1, 45) || ': ' || a.postal_code name
606 , b.named_account_id
607 , c.terr_group_account_id
608 , a.duns_number_c
609 from hz_parties a
610 , jtf_tty_named_accts b
611 , jtf_tty_terr_grp_accts c
612 where c.terr_group_id = LP_terr_group_id
613 and b.named_account_id = c.named_account_id
614 and a.party_id = b.party_id
615 and a.status = 'A'
616 AND a.DUNS_NUMBER_C IS NOT NULL
617 AND EXISTS (
618 /* Salesperson exists for this Named Account */
619 SELECT NULL
620 FROM jtf_tty_named_acct_rsc nar
621 WHERE nar.terr_group_account_id = C.TERR_GROUP_ACCOUNT_ID );
622
623
624 /* get the PARTY_NAME + POSTAL_CODE for the Named Account:
625 ** used for NAMED ACCOUNT territory creation */
626 CURSOR get_party_name(LP_terr_group_id number) IS
627 SELECT substr(a.party_name, 1, 45) || ': ' || a.postal_code name
628 , b.named_account_id
629 , c.terr_group_account_id
630 , a.duns_number_c
631 from hz_parties a
632 , jtf_tty_named_accts b
633 , jtf_tty_terr_grp_accts c
634 where c.terr_group_id = LP_terr_group_id
635 and b.named_account_id = c.named_account_id
636 and a.party_id = b.party_id
637 and a.status = 'A'
638 and exists (
639 /* Named Account has at least 1 Mapping Rule */
640 SELECT 1
641 from jtf_tty_acct_qual_maps d
642 where d.named_account_id = c.named_account_id )
643 AND EXISTS (
644 /* Salesperson exists for this Named Account */
645 SELECT NULL
646 FROM jtf_tty_named_acct_rsc nar
647 WHERE nar.terr_group_account_id = C.TERR_GROUP_ACCOUNT_ID );
648
649
650 /* get the DUNS# for the Named Account:
651 ** used for OVERLAY territory creation */
652 CURSOR get_OVLY_party_duns(LP_terr_group_id number) IS
653 SELECT substr(a.party_name, 1, 45) || ': ' || a.postal_code name
654 , b.named_account_id
655 , c.terr_group_account_id
656 , a.duns_number_c
657 from hz_parties a
658 , jtf_tty_named_accts b
659 , jtf_tty_terr_grp_accts c
660 where c.terr_group_id = LP_terr_group_id
661 and b.named_account_id = c.named_account_id
662 and a.party_id = b.party_id
663 and a.status = 'A'
664 AND a.DUNS_NUMBER_C IS NOT NULL
665 AND EXISTS (
666 /* Salesperson, with Role that has a Product
667 ** Interest defined, exists for this Named Account */
668 SELECT NULL
669 FROM jtf_tty_named_acct_rsc nar
670 , jtf_tty_role_prod_int rpi
671 , jtf_tty_terr_grp_roles tgr
672 WHERE rpi.terr_group_role_id = tgr.terr_group_role_id
673 AND tgr.terr_group_id = C.TERR_GROUP_ID
674 AND tgr.role_code = nar.rsc_role_code
675 AND nar.terr_group_account_id = C.TERR_GROUP_ACCOUNT_ID );
676
677
678 /* get the PARTY_NAME + POSTAL_CODE for the Named Account
679 ** used for OVERLAY territory creation */
680 CURSOR get_OVLY_party_name(LP_terr_group_id number) IS
681 SELECT substr(a.party_name, 1, 45) || ': ' || a.postal_code name
682 , b.named_account_id
683 , c.terr_group_account_id
684 , a.duns_number_c
685 from hz_parties a
686 , jtf_tty_named_accts b
687 , jtf_tty_terr_grp_accts c
688 where c.terr_group_id = LP_terr_group_id
689 and b.named_account_id = c.named_account_id
690 and a.party_id = b.party_id
691 and a.status = 'A'
692 and exists (
693 /* Named Account has at least 1 Mapping Rule */
694 SELECT 1
695 from jtf_tty_acct_qual_maps d
696 where d.named_account_id = c.named_account_id )
697 AND EXISTS (
698 /* Salesperson, with Role that has a Product
699 ** Interest defined, exists for this Named Account */
700 SELECT NULL
701 FROM jtf_tty_named_acct_rsc nar
702 , jtf_tty_role_prod_int rpi
703 , jtf_tty_terr_grp_roles tgr
704 WHERE rpi.terr_group_role_id = tgr.terr_group_role_id
705 AND tgr.terr_group_id = C.TERR_GROUP_ID
706 AND tgr.role_code = nar.rsc_role_code
707 AND nar.terr_group_account_id = C.TERR_GROUP_ACCOUNT_ID );
708
709
710 /* Should Unassigned NAs go to Sales Manager or NA Catch-All? */
711 -- WHERE c.dn_jnr_assigned_flag = 'Y';
712
713 /* get Customer Keynames and Postal Code mappings
714 ** for the Named Account */
715 /* bug#2925153: JRADHAKR: Added value2_char */
716 CURSOR match_rule1( l_na_id number) IS
717 SELECT b.qual_usg_id
718 , b.comparison_operator
719 , b.value1_char
720 , b.value2_char
721 FROM jtf_tty_acct_qual_maps b
722 WHERE b.qual_usg_id IN (-1007, -1012)
723 AND b.named_account_id = l_na_id
724 ORDER BY b.qual_usg_id;
725
726
727 /* get DUNS# for the Named Account */
728 /* bug#2933116: JDOCHERT: 05/27/03: support for DUNS# Qualifier */
729 CURSOR match_rule3(l_na_id number) IS
730 SELECT -1120 qual_usg_id
731 , '=' comparison_operator
732 , hzp.duns_number_c value1_char
733 FROM hz_parties hzp, jtf_tty_named_accts na
734 where hzp.status = 'A'
735 AND hzp.party_id = na.party_id
736 AND na.named_account_id = l_na_id;
737
738
739 /* Get Top-Level Parent Territory details */
740 CURSOR topterr(l_terr number) IS
741 SELECT name
742 , description
743 , rank
744 , parent_territory_id
745 , terr_id
746 from jtf_terr_all
747 where terr_id = l_terr;
748
749 /* get Qualifiers used in a territory */
750 CURSOR csr_get_qual( lp_terr_id NUMBER) IS
751 SELECT jtq.terr_qual_id
752 , jtq.qual_usg_id
753 FROM jtf_terr_qual_all jtq
754 WHERE jtq.terr_id = lp_terr_id;
755
756 /* get Values used in a territory qualifier */
757 CURSOR csr_get_qual_val ( lp_terr_qual_id NUMBER ) IS
758 SELECT jtv.TERR_VALUE_ID
759 , jtv.INCLUDE_FLAG
760 , jtv.COMPARISON_OPERATOR
761 , jtv.LOW_VALUE_CHAR
762 , jtv.HIGH_VALUE_CHAR
763 , jtv.LOW_VALUE_NUMBER
764 , jtv.HIGH_VALUE_NUMBER
765 , jtv.VALUE_SET
766 , jtv.INTEREST_TYPE_ID
767 , jtv.PRIMARY_INTEREST_CODE_ID
768 , jtv.SECONDARY_INTEREST_CODE_ID
769 , jtv.CURRENCY_CODE
770 , jtv.ORG_ID
771 , jtv.ID_USED_FLAG
772 , jtv.LOW_VALUE_CHAR_ID
773 FROM jtf_terr_values_all jtv
774 WHERE jtv.terr_qual_id = lp_terr_qual_id;
775
776
777 /* get those roles for a territory Group that
778 ** do not have Product Interest defined */
779 CURSOR role_no_pi(l_terr_group_id number) IS
780 SELECT distinct b.role_code
781 from jtf_tty_role_access a
782 , jtf_tty_terr_grp_roles b
783 , jtf_tty_role_prod_int c
784 where a.terr_group_role_id = b.terr_group_role_id
785 and b.terr_group_id = l_terr_group_id
786 and a.access_type = 'ACCOUNT'
787 and c.terr_group_role_id = b.terr_group_role_id
788 and not exists ( SELECT 1
789 from jtf_tty_role_prod_int e
790 , jtf_tty_terr_grp_roles d
791 where e.terr_group_role_id (+) = d.terr_group_role_id
792 and d.terr_group_id = b.terr_group_id
793 and d.role_code = b.role_code
794 and e.interest_type_id is null);
795
796 l_overlay_top number;
797 l_overlay number;
798 l_nacat number;
799 l_id number;
800 l_ovnon_flag varchar2(1):='N';
801
802 l_na_count number;
803
804 l_terr_exists NUMBER;
805
806 BEGIN
807
808 /* JDOCHERT: 07/09/03:
809 ** START: Disable triggers in
810 ** TOTAL mode */
811 IF (p_mode = 'TOTAL') THEN
812 alter_triggers(p_status => 'DISABLE');
813 END IF;
814
815 /* (1) JDOCHERT: 07/01/03:
816 ** START: DELETE ALL EXISTING NAMED ACCOUNT TERRITORIES
817 ** INCREMENTAL or TOTAL mode */
818 cleanup_na_territories(p_mode => p_mode);
819
820 /* Set Global Application Short Name */
821 IF G_APP_SHORT_NAME IS NULL THEN
822 G_APP_SHORT_NAME := 'JTF';
823 END IF;
824
825 /* (2) START: CREATE NAMED ACCOUNT TERRITORY CREATION
826 ** FOR EACH TERRITORY GROUP */
827 for terr_group in grp LOOP
828
829 write_log(2, '');
830 write_log(2, '----------------------------------------------------------');
831 write_log(2, 'BEGIN: Territory Creation for Territory Group: ' ||
832 terr_group.terr_group_id || ' : ' ||
833 terr_group.terr_group_name );
834
835 /* reset these processing values for the Territory Group */
836 l_na_catchall_flag := 'N';
837 l_overlap_catchall_flag := 'N';
838 l_ovnon_flag := 'N';
839 l_overnon_role_tbl := l_overnon_role_empty_tbl;
840
841
842 /** Roles with No Product Interest */
843 i:=0;
844 for overlayandnon in role_no_pi(terr_group.terr_group_id) loop
845
846 l_ovnon_flag:='Y';
847 i :=i +1;
848
849 SELECT JTF_TTY_TERR_GRP_ROLES_S.nextval
850 into l_id
851 FROM DUAL;
852
853 l_overnon_role_tbl(i).grp_role_id:= l_id;
854 --
855
856 INSERT into JTF_TTY_TERR_GRP_ROLES(
857 TERR_GROUP_ROLE_ID
858 , OBJECT_VERSION_NUMBER
859 , TERR_GROUP_ID
860 , ROLE_CODE
861 , CREATED_BY
862 , CREATION_DATE
863 , LAST_UPDATED_BY
864 , LAST_UPDATE_DATE
865 , LAST_UPDATE_LOGIN)
866 VALUES(
867 l_overnon_role_tbl(i).grp_role_id
868 , 1
869 , terr_group.terr_group_id
870 , overlayandnon.role_code
871 , G_USER_ID
872 , sysdate
873 , G_USER_ID
874 , sysdate
875 , G_LOGIN_ID);
876 INSERT into JTF_TTY_ROLE_ACCESS(
877 TERR_GROUP_ROLE_ACCESS_ID
878 , OBJECT_VERSION_NUMBER
879 , TERR_GROUP_ROLE_ID
880 , ACCESS_TYPE
881 , CREATED_BY
882 , CREATION_DATE
883 , LAST_UPDATED_BY
884 , LAST_UPDATE_DATE
885 , LAST_UPDATE_LOGIN)
886 VALUES(
887 JTF_TTY_ROLE_ACCESS_S.nextval
888 , 1
889 , l_overnon_role_tbl(i).grp_role_id
890 , 'ACCOUNT'
891 , G_USER_ID
892 , sysdate
893 , G_USER_ID
894 , sysdate
895 , G_LOGIN_ID);
896
897 end loop; /* for overlayandnon in role_no_pi */
898
899
900
901 /* does Territory Group have at least 1 Named Account? */
902 SELECT COUNT(*)
903 INTO l_na_count
904 from jtf_tty_terr_groups g
905 , jtf_tty_terr_grp_accts ga
906 , jtf_tty_named_accts a
907 where g.terr_group_id = ga.terr_group_id
908 AND ga.named_account_id = a.named_account_id
909 AND g.terr_group_id = TERR_GROUP.TERR_GROUP_ID
910 AND ROWNUM < 2;
911
912
913
914 /*********************************************************************/
915 /*********************************************************************/
916 /************** NON-OVERLAY TERRITORY CREATION ***********************/
917 /*********************************************************************/
918 /*********************************************************************/
919
920 /* BEGIN: if Territory Group exists with Named Accounts
921 ** then auto-create territory definitions */
922 IF (l_na_count > 0) THEN
923
924 /***************************************************************/
925 /* (3) START: CREATE PLACEHOLDER TERRITORY FOR TERRITORY GROUP */
926 /***************************************************************/
927 L_TERR_USGS_TBL := L_TERR_USGS_EMPTY_TBL;
928 L_TERR_QUALTYPEUSGS_TBL := L_TERR_QUALTYPEUSGS_EMPTY_TBL;
929 L_TERR_QUAL_TBL := L_TERR_QUAL_EMPTY_TBL;
930 L_TERR_VALUES_TBL := L_TERR_VALUES_EMPTY_TBL;
931 L_TERRRSC_TBL := L_TERRRSC_EMPTY_TBL;
932 L_TERRRSC_ACCESS_TBL := L_TERRRSC_ACCESS_EMPTY_TBL;
933
934 /* TERRITORY HEADER */
935 L_TERR_ALL_REC.TERR_ID := terr_group.terr_group_id;
936 L_TERR_ALL_REC.LAST_UPDATE_DATE := TERR_GROUP.LAST_UPDATE_DATE;
937 L_TERR_ALL_REC.LAST_UPDATED_BY := G_USER_ID;
938 L_TERR_ALL_REC.CREATION_DATE := TERR_GROUP.CREATION_DATE;
939 L_TERR_ALL_REC.CREATED_BY := G_USER_ID ;
940 L_TERR_ALL_REC.LAST_UPDATE_LOGIN := G_LOGIN_ID;
941 L_TERR_ALL_REC.APPLICATION_SHORT_NAME:= G_APP_SHORT_NAME;
942 L_TERR_ALL_REC.NAME := TERR_GROUP.TERR_GROUP_NAME;
943 L_TERR_ALL_REC.START_DATE_ACTIVE := TERR_GROUP.ACTIVE_FROM_DATE ;
944 L_TERR_ALL_REC.END_DATE_ACTIVE := TERR_GROUP.ACTIVE_TO_DATE;
945 L_TERR_ALL_REC.PARENT_TERRITORY_ID := TERR_GROUP.PARENT_TERR_ID;
946 L_TERR_ALL_REC.RANK := TERR_GROUP.RANK;
947 L_TERR_ALL_REC.TEMPLATE_TERRITORY_ID := NULL;
948 L_TERR_ALL_REC.TEMPLATE_FLAG := 'N';
949 L_TERR_ALL_REC.ESCALATION_TERRITORY_ID := NULL;
950 L_TERR_ALL_REC.ESCALATION_TERRITORY_FLAG := 'N';
951 L_TERR_ALL_REC.OVERLAP_ALLOWED_FLAG := NULL;
952 L_TERR_ALL_REC.DESCRIPTION := TERR_GROUP.TERR_GROUP_NAME;
953 L_TERR_ALL_REC.UPDATE_FLAG := 'N';
954 L_TERR_ALL_REC.AUTO_ASSIGN_RESOURCES_FLAG:= NULL;
955 L_TERR_ALL_REC.NUM_WINNERS := NULL ;
956
957 /* ORG_ID IS SET TO SAME VALUE AS TERRITORY
958 ** GROUP's Top-Level Parent Territory */
959 l_terr_all_rec.ORG_ID := terr_group.ORG_ID;
960
961
962 /* ORACLE SALES AND TELESALES USAGE */
963 SELECT JTF_TERR_USGS_S.nextval
964 INTO l_terr_usg_id
965 FROM DUAL;
966
967 l_terr_usgs_tbl(1).SOURCE_ID := -1001;
968 l_terr_usgs_tbl(1).TERR_USG_ID := l_terr_usg_id;
969 l_terr_usgs_tbl(1).LAST_UPDATE_DATE := terr_group.LAST_UPDATE_DATE;
970 l_terr_usgs_tbl(1).LAST_UPDATED_BY := G_USER_ID;
971 l_terr_usgs_tbl(1).CREATION_DATE := terr_group.CREATION_DATE;
972 l_terr_usgs_tbl(1).CREATED_BY := G_USER_ID;
973 l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:= G_LOGIN_ID;
974 l_terr_usgs_tbl(1).TERR_ID := null;
975 l_terr_usgs_tbl(1).ORG_ID := terr_group.ORG_ID;
976
977
978 /* ACCOUNT TRANSACTION TYPE */
979 SELECT JTF_TERR_QTYPE_USGS_S.nextval
980 into l_terr_qtype_usg_id
981 FROM DUAL;
982
983 l_terr_qualtypeusgs_tbl(1).QUAL_TYPE_USG_ID := -1001;
984 l_terr_qualtypeusgs_tbl(1).TERR_QUAL_TYPE_USG_ID := l_terr_qtype_usg_id;
985 l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE := terr_group.LAST_UPDATE_DATE;
986 l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY := G_USER_ID;
987 l_terr_qualtypeusgs_tbl(1).CREATION_DATE := terr_group.CREATION_DATE;
988 l_terr_qualtypeusgs_tbl(1).CREATED_BY := G_USER_ID;
989 l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN := G_LOGIN_ID;
990 l_terr_qualtypeusgs_tbl(1).TERR_ID := null;
991 l_terr_qualtypeusgs_tbl(1).ORG_ID := terr_group.ORG_ID;
992
993 /* LEAD TRANSACTION TYPE */
994 SELECT JTF_TERR_QTYPE_USGS_S.nextval
995 into l_terr_qtype_usg_id
996 FROM DUAL;
997
998 l_terr_qualtypeusgs_tbl(2).QUAL_TYPE_USG_ID := -1002;
999 l_terr_qualtypeusgs_tbl(2).TERR_QUAL_TYPE_USG_ID := l_terr_qtype_usg_id;
1000 l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE := terr_group.LAST_UPDATE_DATE;
1001 l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY := G_USER_ID;
1002 l_terr_qualtypeusgs_tbl(2).CREATION_DATE := terr_group.CREATION_DATE;
1003 l_terr_qualtypeusgs_tbl(2).CREATED_BY := G_USER_ID;
1004 l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN := G_LOGIN_ID;
1005 l_terr_qualtypeusgs_tbl(2).TERR_ID := null;
1006 l_terr_qualtypeusgs_tbl(2).ORG_ID := terr_group.ORG_ID;
1007
1008 /* OPPORTUNITY TRANSACTION TYPE */
1009 SELECT JTF_TERR_QTYPE_USGS_S.nextval
1010 into l_terr_qtype_usg_id
1011 FROM DUAL;
1012
1013 l_terr_qualtypeusgs_tbl(3).QUAL_TYPE_USG_ID := -1003;
1014 l_terr_qualtypeusgs_tbl(3).TERR_QUAL_TYPE_USG_ID := l_terr_qtype_usg_id;
1015 l_terr_qualtypeusgs_tbl(3).LAST_UPDATE_DATE := terr_group.LAST_UPDATE_DATE;
1016 l_terr_qualtypeusgs_tbl(3).LAST_UPDATED_BY := G_USER_ID;
1017 l_terr_qualtypeusgs_tbl(3).CREATION_DATE := terr_group.CREATION_DATE;
1018 l_terr_qualtypeusgs_tbl(3).CREATED_BY := G_USER_ID;
1019 l_terr_qualtypeusgs_tbl(3).LAST_UPDATE_LOGIN := G_LOGIN_ID;
1020 l_terr_qualtypeusgs_tbl(3).TERR_ID := null;
1021 l_terr_qualtypeusgs_tbl(3).ORG_ID := terr_group.ORG_ID;
1022
1023 l_init_msg_list := FND_API.G_TRUE;
1024
1025
1026 /* set org context using ORG_ID of Territory
1027 ** Group'S TOP-LEVEL PARENT TERRITORY */
1028 -- 07/08/03: JDOCHERT: bug#3023653
1029 --
1030 --MO_GLOBAL.SET_ORG_CONTEXT(TERR_GROUP.ORG_ID, NULL);
1031 --
1032
1033 /* CALL CREATE TERRITORY API */
1034 jtf_territory_pvt.create_territory (
1035 p_api_version_number => l_api_version_number,
1036 p_init_msg_list => l_init_msg_list,
1037 p_commit => l_commit,
1038 p_validation_level => fnd_api.g_valid_level_NONE,
1039 x_return_status => x_return_status,
1040 x_msg_count => x_msg_count,
1041 x_msg_data => x_msg_data,
1042 p_terr_all_rec => l_terr_all_rec,
1043 p_terr_usgs_tbl => l_terr_usgs_tbl,
1044 p_terr_qualtypeusgs_tbl => l_terr_qualtypeusgs_tbl,
1045 p_terr_qual_tbl => l_terr_qual_tbl,
1046 p_terr_values_tbl => l_terr_values_tbl,
1047 x_terr_id => x_terr_id,
1048 x_terr_usgs_out_tbl => x_terr_usgs_out_tbl,
1049 x_terr_qualtypeusgs_out_tbl => x_terr_qualtypeusgs_out_tbl,
1050 x_terr_qual_out_tbl => x_terr_qual_out_tbl,
1051 x_terr_values_out_tbl => x_terr_values_out_tbl
1052 );
1053
1054 /* BEGIN: SUCCESSFUL TERRITORY CREATION? */
1055 IF X_RETURN_STATUS = 'S' THEN
1056
1057 /* JDOCHERT: 01/08/03: ADDED TERR_GROUP_ID */
1058 UPDATE JTF_TERR_ALL
1059 SET TERR_GROUP_FLAG = 'Y'
1060 , CATCH_ALL_FLAG = 'N'
1061 , TERR_GROUP_ID = TERR_GROUP.TERR_GROUP_ID
1062 , NUM_WINNERS = TERR_GROUP.NUM_WINNERS
1063 WHERE TERR_ID = X_TERR_ID;
1064
1065 L_NACAT := X_TERR_ID;
1066
1067 WRITE_LOG(2,' Top level Named Account territory created: TERR_ID# '||X_TERR_ID);
1068
1069 ELSE
1070 WRITE_LOG(2,'ERROR: PLACEHOLDER TERRITORY CREATION FAILED ' ||
1071 'FOR TERRITORY_GROUP_ID# ' ||TERR_GROUP.TERR_GROUP_ID);
1072 X_MSG_DATA := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
1073 WRITE_LOG(2,X_MSG_DATA);
1074
1075 END IF;
1076 /* END: SUCCESSFUL TERRITORY CREATION? */
1077 /*************************************************************/
1078 /* (3) END: CREATE PLACEHOLDER TERRITORY FOR TERRITORY GROUP */
1079 /*************************************************************/
1080
1081
1082 /****************************************************************/
1083 /* (4) START: CREATE NA CATCH-ALL TERRITORY FOR TERRITORY GROUP */
1084 /****************************************************************/
1085
1086 IF ( terr_group.matching_rule_code IN ('1', '2') AND
1087 terr_group.generate_catchall_flag = 'Y' ) THEN
1088
1089 /* RESET TABLES */
1090 L_TERR_USGS_TBL := L_TERR_USGS_EMPTY_TBL;
1091 L_TERR_QUALTYPEUSGS_TBL := L_TERR_QUALTYPEUSGS_EMPTY_TBL;
1092 L_TERR_QUAL_TBL := L_TERR_QUAL_EMPTY_TBL;
1093 L_TERR_VALUES_TBL := L_TERR_VALUES_EMPTY_TBL;
1094 L_TERRRSC_TBL := L_TERRRSC_EMPTY_TBL;
1095 L_TERRRSC_ACCESS_TBL := L_TERRRSC_ACCESS_EMPTY_TBL;
1096
1097
1098 /* TERRITORY HEADER */
1099 /* Ensure static TERR_ID to benefit TAP Performance */
1100 L_TERR_ALL_REC.TERR_ID := terr_group.terr_group_id * -1;
1101 L_TERR_ALL_REC.LAST_UPDATE_DATE := TERR_GROUP.LAST_UPDATE_DATE;
1102 L_TERR_ALL_REC.LAST_UPDATED_BY := G_USER_ID;
1103 L_TERR_ALL_REC.CREATION_DATE := TERR_GROUP.CREATION_DATE;
1104 L_TERR_ALL_REC.CREATED_BY := G_USER_ID;
1105 L_TERR_ALL_REC.LAST_UPDATE_LOGIN := G_LOGIN_ID;
1106 L_TERR_ALL_REC.APPLICATION_SHORT_NAME := G_APP_SHORT_NAME;
1107 L_TERR_ALL_REC.NAME := TERR_GROUP.TERR_GROUP_NAME ||' (CATCH-ALL)';
1108 L_TERR_ALL_REC.START_DATE_ACTIVE := TERR_GROUP.ACTIVE_FROM_DATE ;
1109 L_TERR_ALL_REC.END_DATE_ACTIVE := TERR_GROUP.ACTIVE_TO_DATE;
1110 L_TERR_ALL_REC.PARENT_TERRITORY_ID := X_TERR_ID;
1111
1112 --
1113 -- 01/20/03: JDOCHERT: CHANGE RANK OF CATCH-ALL
1114 -- TO BE LESS THAT NAMED ACCOUNT TERRITORIES
1115 --
1116 L_TERR_ALL_REC.RANK := TERR_GROUP.RANK + 100;
1117 --
1118
1119 L_TERR_ALL_REC.TEMPLATE_TERRITORY_ID := NULL;
1120 L_TERR_ALL_REC.TEMPLATE_FLAG := 'N';
1121 L_TERR_ALL_REC.ESCALATION_TERRITORY_ID := NULL;
1122 L_TERR_ALL_REC.ESCALATION_TERRITORY_FLAG := 'N';
1123 L_TERR_ALL_REC.OVERLAP_ALLOWED_FLAG := NULL;
1124 L_TERR_ALL_REC.DESCRIPTION := TERR_GROUP.TERR_GROUP_NAME||' (CATCH-ALL)';
1125 L_TERR_ALL_REC.UPDATE_FLAG := 'N';
1126 L_TERR_ALL_REC.AUTO_ASSIGN_RESOURCES_FLAG := NULL;
1127
1128 /* ORG_ID IS SET TO SAME VALUE AS TERRITORY
1129 ** GROUP's Top-Level Parent Territory */
1130 l_terr_all_rec.ORG_ID := terr_group.ORG_ID;
1131 l_terr_all_rec.NUM_WINNERS := null ;
1132
1133
1134 /* Oracle Sales and Telesales Usage */
1135 SELECT JTF_TERR_USGS_S.nextval
1136 into l_terr_usg_id
1137 FROM DUAL;
1138
1139 l_terr_usgs_tbl(1).TERR_USG_ID := l_terr_usg_id;
1140 l_terr_usgs_tbl(1).LAST_UPDATE_DATE := terr_group.LAST_UPDATE_DATE;
1141 l_terr_usgs_tbl(1).LAST_UPDATED_BY := G_USER_ID;
1142 l_terr_usgs_tbl(1).CREATION_DATE := terr_group.CREATION_DATE;
1143 l_terr_usgs_tbl(1).CREATED_BY := G_USER_ID;
1144 l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := G_LOGIN_ID;
1145 l_terr_usgs_tbl(1).TERR_ID := null;
1146 l_terr_usgs_tbl(1).SOURCE_ID := -1001;
1147 l_terr_usgs_tbl(1).ORG_ID := terr_group.ORG_ID;
1148
1149
1150 i:=0;
1151 FOR actype in na_access(terr_group.terr_group_id) LOOP
1152
1153 i:=i+1;
1154 if actype.access_type='ACCOUNT' then
1155
1156 /* ACCOUNT TRANSACTION TYPE */
1157 SELECT JTF_TERR_QTYPE_USGS_S.nextval
1158 into l_terr_qtype_usg_id
1159 FROM DUAL;
1160
1161 l_terr_qualtypeusgs_tbl(i).TERR_QUAL_TYPE_USG_ID := l_terr_qtype_usg_id;
1162 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := terr_group.LAST_UPDATE_DATE;
1163 l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := G_USER_ID;
1164 l_terr_qualtypeusgs_tbl(i).CREATION_DATE := terr_group.CREATION_DATE;
1165 l_terr_qualtypeusgs_tbl(i).CREATED_BY := G_USER_ID;
1166 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := G_LOGIN_ID;
1167 l_terr_qualtypeusgs_tbl(i).TERR_ID := null;
1168 l_terr_qualtypeusgs_tbl(i).QUAL_TYPE_USG_ID := -1001;
1169 l_terr_qualtypeusgs_tbl(i).ORG_ID := terr_group.ORG_ID;
1170
1171 elsif actype.access_type='LEAD' then
1172
1173 /* LEAD TRANSACTION TYPE */
1174 SELECT JTF_TERR_QTYPE_USGS_S.nextval
1175 INTO l_terr_qtype_usg_id
1176 FROM DUAL;
1177
1178 l_terr_qualtypeusgs_tbl(i).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
1179 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
1180 l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1181 l_terr_qualtypeusgs_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
1182 l_terr_qualtypeusgs_tbl(i).CREATED_BY := terr_group.CREATED_BY;
1183 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1184 l_terr_qualtypeusgs_tbl(i).TERR_ID:= null;
1185 l_terr_qualtypeusgs_tbl(i).QUAL_TYPE_USG_ID:=-1002;
1186 l_terr_qualtypeusgs_tbl(i).ORG_ID:=terr_group.ORG_ID;
1187
1188 elsif actype.access_type='OPPORTUNITY' then
1189
1190 /* OPPORTUNITY TRANSACTION TYPE */
1191 SELECT JTF_TERR_QTYPE_USGS_S.nextval
1192 into l_terr_qtype_usg_id
1193 FROM DUAL;
1194
1195 l_terr_qualtypeusgs_tbl(i).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
1196 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
1197 l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1198 l_terr_qualtypeusgs_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
1199 l_terr_qualtypeusgs_tbl(i).CREATED_BY := terr_group.CREATED_BY;
1200 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1201 l_terr_qualtypeusgs_tbl(i).TERR_ID:= null;
1202 l_terr_qualtypeusgs_tbl(i).QUAL_TYPE_USG_ID:=-1003;
1203 l_terr_qualtypeusgs_tbl(i).ORG_ID:=terr_group.ORG_ID;
1204
1205 end if;
1206 end loop;
1207
1208
1209 /*
1210 ** Customer Name Range Qualifier -1012 */
1211 SELECT JTF_TERR_QUAL_S.nextval
1212 into l_terr_qual_id
1213 FROM DUAL;
1214
1215 l_terr_qual_tbl(1).TERR_QUAL_ID :=l_terr_qual_id;
1216 l_terr_qual_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
1217 l_terr_qual_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1218 l_terr_qual_tbl(1).CREATION_DATE:= terr_group.CREATION_DATE;
1219 l_terr_qual_tbl(1).CREATED_BY := terr_group.CREATED_BY;
1220 l_terr_qual_tbl(1).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1221 l_terr_qual_tbl(1).TERR_ID:=null;
1222 l_terr_qual_tbl(1).QUAL_USG_ID :=-1012;
1223 l_terr_qual_tbl(1).QUALIFIER_MODE:=NULL;
1224 l_terr_qual_tbl(1).OVERLAP_ALLOWED_FLAG:='N';
1225 l_terr_qual_tbl(1).USE_TO_NAME_FLAG:=NULL;
1226 l_terr_qual_tbl(1).GENERATE_FLAG:=NULL;
1227 l_terr_qual_tbl(1).ORG_ID:=terr_group.ORG_ID;
1228
1229 /*
1230 ** VARCHAR2 data value */
1231 l_id_used_flag :='N' ;
1232
1233 /*
1234 ** get all the Customer Name Range Values for all the Named Accounts
1235 ** that belong to this Territory Group */
1236 k:=0;
1237 FOR cust_value in catchall_cust(terr_group.TERR_GROUP_ID) LOOP
1238
1239 k:=k+1;
1240
1241 l_terr_values_tbl(k).TERR_VALUE_ID:=null;
1242
1243 l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
1244 l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
1245 l_terr_values_tbl(k).CREATED_BY := terr_group.CREATED_BY;
1246 l_terr_values_tbl(k).CREATION_DATE:= terr_group.CREATION_DATE;
1247 l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
1248 l_terr_values_tbl(k).TERR_QUAL_ID :=l_terr_qual_id ;
1249 l_terr_values_tbl(k).INCLUDE_FLAG :=NULL;
1250 l_terr_values_tbl(k).COMPARISON_OPERATOR := cust_value.COMPARISON_OPERATOR;
1251 l_terr_values_tbl(k).LOW_VALUE_CHAR:= cust_value.value1_char;
1252
1253 l_terr_values_tbl(k).HIGH_VALUE_CHAR:=null;
1254 l_terr_values_tbl(k).LOW_VALUE_NUMBER :=null;
1255 l_terr_values_tbl(k).HIGH_VALUE_NUMBER :=null;
1256 l_terr_values_tbl(k).VALUE_SET :=NULL;
1257 l_terr_values_tbl(k).INTEREST_TYPE_ID :=null;
1258 l_terr_values_tbl(k).PRIMARY_INTEREST_CODE_ID:=null;
1259 l_terr_values_tbl(k).SECONDARY_INTEREST_CODE_ID:=null;
1260 l_terr_values_tbl(k).CURRENCY_CODE :=null;
1261 l_terr_values_tbl(k).ORG_ID :=terr_group.ORG_ID;
1262 l_terr_values_tbl(k).ID_USED_FLAG :=l_id_used_flag;
1263 l_terr_values_tbl(k).LOW_VALUE_CHAR_ID :=null;
1264
1265 l_terr_values_tbl(k).qualifier_tbl_index := 1;
1266
1267 end loop;
1268
1269 l_init_msg_list := FND_API.G_TRUE;
1270
1271 -- 07/08/03: JDOCHERT: bug#3023653
1272 --mo_global.set_org_context(terr_group.ORG_ID,null);
1273 --
1274 jtf_territory_pvt.create_territory (
1275 p_api_version_number => l_api_version_number,
1276 p_init_msg_list => l_init_msg_list,
1277 p_commit => l_commit,
1278 p_validation_level => fnd_api.g_valid_level_NONE,
1279 x_return_status => x_return_status,
1280 x_msg_count => x_msg_count,
1281 x_msg_data => x_msg_data,
1282 p_terr_all_rec => l_terr_all_rec,
1283 p_terr_usgs_tbl => l_terr_usgs_tbl,
1284 p_terr_qualtypeusgs_tbl => l_terr_qualtypeusgs_tbl,
1285 p_terr_qual_tbl => l_terr_qual_tbl,
1286 p_terr_values_tbl => l_terr_values_tbl,
1287 x_terr_id => x_terr_id,
1288 x_terr_usgs_out_tbl => x_terr_usgs_out_tbl,
1289 x_terr_qualtypeusgs_out_tbl => x_terr_qualtypeusgs_out_tbl,
1290 x_terr_qual_out_tbl => x_terr_qual_out_tbl,
1291 x_terr_values_out_tbl => x_terr_values_out_tbl
1292 );
1293
1294 write_log(2,' NAMED ACCOUNT CATCH ALL TERRITORY CREATED: TERR_ID# '||x_terr_id);
1295
1296 /* BEGIN: Successful Territory creation? */
1297 IF x_return_status = 'S' THEN
1298
1299 /* JDOCHERT: 01/08/03: Added TERR_GROUP_ID and CATCH_ALL_FLAG */
1300 UPDATE JTF_TERR_ALL
1301 set TERR_GROUP_FLAG = 'Y'
1302 , TERR_GROUP_ID = terr_group.TERR_GROUP_ID
1303 , CATCH_ALL_FLAG = 'Y'
1304 where terr_id = x_terr_id;
1305
1306 l_init_msg_list :=FND_API.G_TRUE;
1307
1308 SELECT JTF_TERR_RSC_S.nextval
1309 into l_terr_rsc_id
1310 FROM DUAL;
1311
1312 l_TerrRsc_Tbl(1).terr_id := x_terr_id;
1313 l_TerrRsc_Tbl(1).TERR_RSC_ID :=l_terr_rsc_id;
1314 l_TerrRsc_Tbl(1).LAST_UPDATE_DATE:=terr_group.LAST_UPDATE_DATE;
1315 l_TerrRsc_Tbl(1).LAST_UPDATED_BY:=terr_group.LAST_UPDATED_BY;
1316 l_TerrRsc_Tbl(1).CREATION_DATE:=terr_group.CREATION_DATE;
1317 l_TerrRsc_Tbl(1).CREATED_BY:=terr_group.CREATED_BY;
1318 l_TerrRsc_Tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
1319 l_TerrRsc_Tbl(1).RESOURCE_ID:=terr_group.catch_all_resource_id;
1320 l_TerrRsc_Tbl(1).RESOURCE_TYPE:=terr_group.catch_all_resource_type;
1321
1322 --l_TerrRsc_Tbl(1).ROLE:=tran_type.role_code;
1323 l_TerrRsc_Tbl(1).ROLE:='SALES_ADMIN';
1324 l_TerrRsc_Tbl(1).PRIMARY_CONTACT_FLAG:='N';
1325 l_TerrRsc_Tbl(1).START_DATE_ACTIVE:=terr_group.active_from_date ;
1326 l_TerrRsc_Tbl(1).END_DATE_ACTIVE:=terr_group.active_to_date ;
1327 l_TerrRsc_Tbl(1).ORG_ID:=terr_group.ORG_ID;
1328 l_TerrRsc_Tbl(1).FULL_ACCESS_FLAG:='Y';
1329 l_TerrRsc_Tbl(1).GROUP_ID:=-999;
1330
1331 a:=0;
1332 --
1333 FOR rsc_acc in na_access(terr_group.terr_group_id) LOOP
1334
1335 a := a+1;
1336
1337 /* ACCOUNT ACCESS TYPE */
1338 IF rsc_acc.access_type= 'ACCOUNT' then
1339
1340 SELECT JTF_TERR_RSC_ACCESS_S.nextval
1341 INTO l_terr_rsc_access_id
1342 FROM DUAL;
1343
1344 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
1345 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
1346 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1347 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
1348 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
1349 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1350 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
1351 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'ACCOUNT';
1352 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
1353 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:= 1;
1354
1355 /* OPPORTUNITY ACCESS TYPE */
1356 ELSIF rsc_acc.access_type= 'OPPORTUNITY' then
1357
1358 SELECT JTF_TERR_RSC_ACCESS_S.nextval
1359 into l_terr_rsc_access_id
1360 FROM DUAL;
1361
1362 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
1363 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
1364 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1365 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
1366 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
1367 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1368 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
1369 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'OPPOR';
1370 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
1371 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:= 1;
1372
1373 /* LEAD ACCESS TYPE */
1374 elsif rsc_acc.access_type= 'LEAD' then
1375
1376 SELECT JTF_TERR_RSC_ACCESS_S.nextval
1377 into l_terr_rsc_access_id
1378 FROM DUAL;
1379
1380 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
1381 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
1382 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1383 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
1384 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
1385 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1386 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
1387 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'LEAD';
1388 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
1389 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:=1;
1390
1391 end if;
1392 end loop; /* End of rsc_acc */
1393
1394 l_init_msg_list := FND_API.G_TRUE;
1395
1396 -- 07/08/03: JDOCHERT: bug#3023653
1397 jtf_territory_resource_pvt.create_terrresource (
1398 p_api_version_number => l_Api_Version_Number,
1399 p_init_msg_list => l_Init_Msg_List,
1400 p_commit => l_Commit,
1401 p_validation_level => fnd_api.g_valid_level_NONE,
1402 x_return_status => x_Return_Status,
1403 x_msg_count => x_Msg_Count,
1404 x_msg_data => x_msg_data,
1405 p_terrrsc_tbl => l_TerrRsc_tbl,
1406 p_terrrsc_access_tbl => l_terrRsc_access_tbl,
1407 x_terrrsc_out_tbl => x_TerrRsc_Out_Tbl,
1408 x_terrrsc_access_out_tbl => x_TerrRsc_Access_Out_Tbl
1409 );
1410
1411 if x_Return_Status='S' then
1412 write_log( 2,' RESOURCE CREATED FOR NAMED ACCOUNT CATCH ALL TERRITORY ' ||
1413 x_terr_id);
1414 else
1415 write_log( 2,' FAILED IN RESOURCE CREATION FOR NAMED ACCOUNT CATCH ALL TERRITORY' ||
1416 x_terr_id);
1417 x_msg_data := fnd_msg_pub.get(1, fnd_api.g_false);
1418 write_log(2, x_msg_data);
1419 end if;
1420
1421 /* else of -if the catch all territory creation failed */
1422 else
1423 x_msg_data := fnd_msg_pub.get(1, fnd_api.g_false);
1424 write_log(2,x_msg_data);
1425 WRITE_LOG(2,'ERROR: NA CATCH-ALL TERRITORY CREATION FAILED ' ||
1426 'FOR TERRITORY_GROUP_ID# ' ||TERR_GROUP.TERR_GROUP_ID);
1427 end if;
1428
1429 END IF; /* ( terr_group.matching_rule_code IN ('1', '2') AND
1430 terr_group.generate_catchall_flag = 'Y' ) THEN */
1431
1432 /* END: Successful Territory creation? */
1433 /**************************************************************/
1434 /* (4) END: CREATE NA CATCH-ALL TERRITORY FOR TERRITORY GROUP */
1435 /**************************************************************/
1436
1437
1438 /***************************************************************/
1439 /* (5) START: CREATE NA TERRITORIES FOR NAs IN TERRITORY GROUP */
1440 /* USING DUNS# QUALIFIER */
1441 /***************************************************************/
1442 IF ( terr_group.matching_rule_code IN ('2', '3') ) THEN
1443
1444 FOR naterr in get_party_duns(terr_group.terr_group_id) LOOP
1445
1446 --write_log(2,'na '||naterr.named_account_id);
1447 l_terr_usgs_tbl:=l_terr_usgs_empty_tbl;
1448 l_terr_qualtypeusgs_tbl:=l_terr_qualtypeusgs_empty_tbl;
1449
1450 l_terr_qual_tbl := l_terr_qual_empty_tbl;
1451 l_terr_values_tbl := l_terr_values_empty_tbl;
1452
1453
1454 l_TerrRsc_Tbl := l_TerrRsc_empty_Tbl;
1455 l_TerrRsc_Access_Tbl := l_TerrRsc_Access_empty_Tbl;
1456
1457 /* TERRITORY HEADER */
1458 /* Ensure static TERR_ID to benefit TAP Performance */
1459 BEGIN
1460
1461 l_terr_exists := 0;
1462
1463 SELECT COUNT(*)
1464 INTO l_terr_exists
1465 FROM jtf_terr_all jt
1466 WHERE jt.terr_id = naterr.terr_group_account_id * -100;
1467
1468 IF (l_terr_exists = 0) THEN
1469 l_terr_all_rec.TERR_ID := naterr.terr_group_account_id * -100;
1470 ELSE
1471 l_terr_all_rec.TERR_ID := NULL;
1472 END IF;
1473
1474 EXCEPTION
1475 WHEN NO_DATA_FOUND THEN
1476 l_terr_all_rec.TERR_ID := naterr.terr_group_account_id * -100;
1477 END;
1478
1479 l_terr_all_rec.LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
1480 l_terr_all_rec.LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1481 l_terr_all_rec.CREATION_DATE:= terr_group.CREATION_DATE;
1482 l_terr_all_rec.CREATED_BY := terr_group.CREATED_BY ;
1483 l_terr_all_rec.LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1484
1485 l_terr_all_rec.APPLICATION_SHORT_NAME:= G_APP_SHORT_NAME;
1486 l_terr_all_rec.NAME:= naterr.name || ' (DUNS#)';
1487 l_terr_all_rec.start_date_active := terr_group.active_from_date ;
1488 l_terr_all_rec.end_date_active := terr_group.active_to_date;
1489 l_terr_all_rec.PARENT_TERRITORY_ID:= l_nacat;
1490 l_terr_all_rec.RANK := terr_group.RANK + 10;
1491 l_terr_all_rec.TEMPLATE_TERRITORY_ID:= NULL;
1492 l_terr_all_rec.TEMPLATE_FLAG := 'N';
1493 l_terr_all_rec.ESCALATION_TERRITORY_ID := NULL;
1494 l_terr_all_rec.ESCALATION_TERRITORY_FLAG := 'N';
1495 l_terr_all_rec.OVERLAP_ALLOWED_FLAG := NULL;
1496 l_terr_all_rec.DESCRIPTION:= naterr.name || ' (DUNS#)';
1497 l_terr_all_rec.UPDATE_FLAG :='N';
1498 l_terr_all_rec.AUTO_ASSIGN_RESOURCES_FLAG :=NULL;
1499
1500 l_terr_all_rec.ORG_ID :=terr_group.ORG_ID ;
1501 l_terr_all_rec.NUM_WINNERS :=null ;
1502
1503
1504 /* Oracle Sales and Telesales Usage */
1505 SELECT JTF_TERR_USGS_S.nextval
1506 into l_terr_usg_id
1507 FROM DUAL;
1508
1509 l_terr_usgs_tbl(1).TERR_USG_ID := l_terr_usg_id;
1510 l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
1511 l_terr_usgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1512 l_terr_usgs_tbl(1).CREATION_DATE:= terr_group.CREATION_DATE;
1513 l_terr_usgs_tbl(1).CREATED_BY := terr_group.CREATED_BY;
1514 l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
1515 l_terr_usgs_tbl(1).TERR_ID:= null;
1516 l_terr_usgs_tbl(1).SOURCE_ID:=-1001;
1517 l_terr_usgs_tbl(1).ORG_ID:= terr_group.ORG_ID;
1518 i:=0;
1519
1520 /* BEGIN: For each Access Type defined for the Territory Group */
1521 for acctype in get_NON_OVLY_na_trans(naterr.terr_group_account_id) LOOP
1522
1523 i:=i+1;
1524
1525 /* ACCOUNT TRANSACTION TYPE */
1526 if acctype.access_type='ACCOUNT' then
1527
1528 SELECT JTF_TERR_QTYPE_USGS_S.nextval
1529 into l_terr_qtype_usg_id
1530 FROM DUAL;
1531 l_terr_qualtypeusgs_tbl(i).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
1532 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
1533 l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1534 l_terr_qualtypeusgs_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
1535 l_terr_qualtypeusgs_tbl(i).CREATED_BY := terr_group.CREATED_BY;
1536 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1537 l_terr_qualtypeusgs_tbl(i).TERR_ID:= null;
1538 l_terr_qualtypeusgs_tbl(i).QUAL_TYPE_USG_ID:=-1001;
1539 l_terr_qualtypeusgs_tbl(i).ORG_ID:=terr_group.ORG_ID;
1540
1541 /* LEAD TRANSACTION TYPE */
1542 elsif acctype.access_type='LEAD' then
1543
1544 SELECT JTF_TERR_QTYPE_USGS_S.nextval
1545 into l_terr_qtype_usg_id
1546 FROM DUAL;
1547 l_terr_qualtypeusgs_tbl(i).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
1548 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
1549 l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1550 l_terr_qualtypeusgs_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
1551 l_terr_qualtypeusgs_tbl(i).CREATED_BY := terr_group.CREATED_BY;
1552 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1553 l_terr_qualtypeusgs_tbl(i).TERR_ID:= null;
1554 l_terr_qualtypeusgs_tbl(i).QUAL_TYPE_USG_ID:=-1002;
1555 l_terr_qualtypeusgs_tbl(i).ORG_ID:=terr_group.ORG_ID;
1556
1557 /* OPPORTUNITY TRANSACTION TYPE */
1558 elsif acctype.access_type='OPPORTUNITY' then
1559
1560 SELECT JTF_TERR_QTYPE_USGS_S.nextval
1561 into l_terr_qtype_usg_id
1562 FROM DUAL;
1563 l_terr_qualtypeusgs_tbl(i).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
1564 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
1565 l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1566 l_terr_qualtypeusgs_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
1567 l_terr_qualtypeusgs_tbl(i).CREATED_BY := terr_group.CREATED_BY;
1568 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1569 l_terr_qualtypeusgs_tbl(i).TERR_ID:= null;
1570 l_terr_qualtypeusgs_tbl(i).QUAL_TYPE_USG_ID:=-1003;
1571 l_terr_qualtypeusgs_tbl(i).ORG_ID:=terr_group.ORG_ID;
1572
1573 end if;
1574
1575 end loop;
1576 /* END: For each Access Type defined for the Territory Group */
1577
1578
1579 /*
1580 ** get Named Account Customer Keyname and Postal Code Mapping
1581 ** rules, to use as territory definition qualifier values
1582 */
1583 j:=0;
1584 K:=0;
1585 l_prev_qual_usg_id:=1;
1586 FOR qval IN match_rule3( naterr.named_account_id ) LOOP
1587
1588 /* new qualifier, i.e., if there is a qualifier in
1589 ** Addition to DUNS# */
1590 IF l_prev_qual_usg_id <> qval.qual_usg_id THEN
1591
1592 j:=j+1;
1593
1594 SELECT JTF_TERR_QUAL_S.nextval
1595 into l_terr_qual_id
1596 FROM DUAL;
1597
1598 l_terr_qual_tbl(j).TERR_QUAL_ID :=l_terr_qual_id;
1599 l_terr_qual_tbl(j).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
1600 l_terr_qual_tbl(j).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1601 l_terr_qual_tbl(j).CREATION_DATE:= terr_group.CREATION_DATE;
1602 l_terr_qual_tbl(j).CREATED_BY := terr_group.CREATED_BY;
1603 l_terr_qual_tbl(j).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1604 l_terr_qual_tbl(j).TERR_ID:=null;
1605 l_terr_qual_tbl(j).QUAL_USG_ID :=qval.qual_usg_id;
1606 l_terr_qual_tbl(j).QUALIFIER_MODE:=NULL;
1607 l_terr_qual_tbl(j).OVERLAP_ALLOWED_FLAG:='N';
1608 l_terr_qual_tbl(j).USE_TO_NAME_FLAG:=NULL;
1609 l_terr_qual_tbl(j).GENERATE_FLAG:=NULL;
1610 l_terr_qual_tbl(j).ORG_ID:=terr_group.ORG_ID;
1611 l_prev_qual_usg_id:= qval.qual_usg_id;
1612
1613 END IF;
1614
1615 k:=k+1;
1616
1617 l_terr_values_tbl(k).TERR_VALUE_ID:=null;
1618 l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
1619 l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
1620 l_terr_values_tbl(k).CREATED_BY := terr_group.CREATED_BY;
1621 l_terr_values_tbl(k).CREATION_DATE:= terr_group.CREATION_DATE;
1622 l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
1623 l_terr_values_tbl(k).TERR_QUAL_ID :=l_terr_qual_id ;
1624 l_terr_values_tbl(k).INCLUDE_FLAG :=NULL;
1625 l_terr_values_tbl(k).COMPARISON_OPERATOR := qval.COMPARISON_OPERATOR;
1626 l_terr_values_tbl(k).LOW_VALUE_CHAR:= qval.value1_char;
1627 l_terr_values_tbl(k).HIGH_VALUE_CHAR:= NULL;
1628 l_terr_values_tbl(k).LOW_VALUE_NUMBER :=null;
1629 l_terr_values_tbl(k).HIGH_VALUE_NUMBER :=null;
1630 l_terr_values_tbl(k).VALUE_SET :=NULL;
1631 l_terr_values_tbl(k).INTEREST_TYPE_ID :=null;
1632 l_terr_values_tbl(k).PRIMARY_INTEREST_CODE_ID:=null;
1633 l_terr_values_tbl(k).SECONDARY_INTEREST_CODE_ID:=null;
1634 l_terr_values_tbl(k).CURRENCY_CODE :=null;
1635 l_terr_values_tbl(k).ORG_ID :=terr_group.ORG_ID;
1636 l_terr_values_tbl(k).ID_USED_FLAG :='N';
1637 l_terr_values_tbl(k).LOW_VALUE_CHAR_ID :=null;
1638
1639 l_terr_values_tbl(k).qualifier_tbl_index := j;
1640
1641 end loop; /* qval IN pqual */
1642
1643
1644 l_init_msg_list :=FND_API.G_TRUE;
1645
1646 -- 07/08/03: JDOCHERT: bug#3023653
1647 --mo_global.set_org_context(terr_group.ORG_ID,null);
1648 --
1649
1650 jtf_territory_pvt.create_territory (
1651 p_api_version_number => l_api_version_number,
1652 p_init_msg_list => l_init_msg_list,
1653 p_commit => l_commit,
1654 p_validation_level => fnd_api.g_valid_level_NONE,
1655 x_return_status => x_return_status,
1656 x_msg_count => x_msg_count,
1657 x_msg_data => x_msg_data,
1658 p_terr_all_rec => l_terr_all_rec,
1659 p_terr_usgs_tbl => l_terr_usgs_tbl,
1660 p_terr_qualtypeusgs_tbl => l_terr_qualtypeusgs_tbl,
1661 p_terr_qual_tbl => l_terr_qual_tbl,
1662 p_terr_values_tbl => l_terr_values_tbl,
1663 x_terr_id => x_terr_id,
1664 x_terr_usgs_out_tbl => x_terr_usgs_out_tbl,
1665 x_terr_qualtypeusgs_out_tbl => x_terr_qualtypeusgs_out_tbl,
1666 x_terr_qual_out_tbl => x_terr_qual_out_tbl,
1667 x_terr_values_out_tbl => x_terr_values_out_tbl
1668 );
1669
1670 write_log(2,' NA territory created = '||naterr.name);
1671
1672
1673 /* BEGIN: Successful Territory creation? */
1674 if x_return_status = 'S' then
1675
1676 -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID and CATCH_ALL_FLAG
1677 -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
1678 UPDATE JTF_TERR_ALL
1679 set TERR_GROUP_FLAG = 'Y'
1680 , TERR_GROUP_ID = terr_group.TERR_GROUP_ID
1681 , CATCH_ALL_FLAG = 'N'
1682 , NAMED_ACCOUNT_FLAG = 'Y'
1683 , TERR_GROUP_ACCOUNT_ID = naterr.terr_group_account_id
1684 where terr_id = x_terr_id;
1685
1686 --write_log(2,terr_group.terr_group_id);
1687 --write_log(2,tran_type.role_code);
1688 l_init_msg_list :=FND_API.G_TRUE;
1689 i := 0;
1690 a := 0;
1691
1692 FOR tran_type in role_interest_nonpi(terr_group.Terr_gROUP_ID)
1693 LOOP
1694 --dbms_output.put_line('tran_type.role_code '||tran_type.role_code);
1695
1696 /* JRADHAKR changed the parameter from l_terr_group_id to l_terr_group_acct_id */
1697 FOR rsc in resource_grp(naterr.terr_group_account_id,tran_type.role_code)
1698 loop
1699 i:=i+1;
1700
1701 SELECT JTF_TERR_RSC_S.nextval
1702 into l_terr_rsc_id
1703 FROM DUAL;
1704
1705 l_TerrRsc_Tbl(i).terr_id := x_terr_id;
1706 l_TerrRsc_Tbl(i).TERR_RSC_ID :=l_terr_rsc_id;
1707 l_TerrRsc_Tbl(i).LAST_UPDATE_DATE:=terr_group.LAST_UPDATE_DATE;
1708 l_TerrRsc_Tbl(i).LAST_UPDATED_BY:=terr_group.LAST_UPDATED_BY;
1709 l_TerrRsc_Tbl(i).CREATION_DATE:=terr_group.CREATION_DATE;
1710 l_TerrRsc_Tbl(i).CREATED_BY:=terr_group.CREATED_BY;
1711 l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
1712 --l_TerrRsc_Tbl(i).TERR_ID:=terr_group.TERRITORY_ID;
1713 l_TerrRsc_Tbl(i).RESOURCE_ID:=rsc.resource_id;
1714 l_TerrRsc_Tbl(i).RESOURCE_TYPE:=rsc.rsc_resource_type;
1715 l_TerrRsc_Tbl(i).ROLE:=tran_type.role_code;
1716 --l_TerrRsc_Tbl(i).ROLE:=l_role;
1717 l_TerrRsc_Tbl(i).PRIMARY_CONTACT_FLAG:='N';
1718 l_TerrRsc_Tbl(i).START_DATE_ACTIVE:=terr_group.active_from_date ;
1719 l_TerrRsc_Tbl(i).END_DATE_ACTIVE:=terr_group.active_to_date ;
1720 l_TerrRsc_Tbl(i).ORG_ID:=terr_group.ORG_ID;
1721 l_TerrRsc_Tbl(i).FULL_ACCESS_FLAG:='Y';
1722 l_TerrRsc_Tbl(i).GROUP_ID:=rsc.rsc_group_id;
1723 --dbms_output.put_line('rsc.resource_id '||rsc.resource_id);
1724
1725
1726 FOR rsc_acc in NON_OVLY_role_access(terr_group.terr_group_id,tran_type.role_code) LOOP
1727 --dbms_output.put_line('rsc_acc.access_type '||rsc_acc.access_type);
1728 a := a+1;
1729
1730 /* ACCOUNT ACCESS TYPE */
1731 IF (rsc_acc.access_type= 'ACCOUNT') THEN
1732
1733 SELECT JTF_TERR_RSC_ACCESS_S.nextval
1734 into l_terr_rsc_access_id
1735 FROM DUAL;
1736 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
1737 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
1738 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1739 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
1740 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
1741 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1742 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
1743 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'ACCOUNT';
1744 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
1745 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:= i;
1746
1747 /* OPPORTUNITY ACCESS TYPE */
1748 elsif rsc_acc.access_type= 'OPPORTUNITY' then
1749
1750 SELECT JTF_TERR_RSC_ACCESS_S.nextval
1751 into l_terr_rsc_access_id
1752 FROM DUAL;
1753 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
1754 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
1755 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1756 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
1757 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
1758 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1759 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
1760 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'OPPOR';
1761 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
1762 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:= i;
1763
1764
1765 /* LEAD ACCESS TYPE */
1766 elsif rsc_acc.access_type= 'LEAD' then
1767
1768 SELECT JTF_TERR_RSC_ACCESS_S.nextval
1769 into l_terr_rsc_access_id
1770 FROM DUAL;
1771 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
1772 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
1773 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1774 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
1775 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
1776 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1777 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
1778 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'LEAD';
1779 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
1780 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:= i;
1781 end if;
1782 end loop; /* FOR rsc_acc in NON_OVLY_role_access */
1783
1784 end loop; /* FOR rsc in resource_grp */
1785
1786 end loop;/* FOR tran_type in role_interest_nonpi */
1787
1788 l_init_msg_list :=FND_API.G_TRUE;
1789
1790 -- 07/08/03: JDOCHERT: bug#3023653
1791 jtf_territory_resource_pvt.create_terrresource (
1792 p_api_version_number => l_Api_Version_Number,
1793 p_init_msg_list => l_Init_Msg_List,
1794 p_commit => l_Commit,
1795 p_validation_level => fnd_api.g_valid_level_NONE,
1796 x_return_status => x_Return_Status,
1797 x_msg_count => x_Msg_Count,
1798 x_msg_data => x_msg_data,
1799 p_terrrsc_tbl => l_TerrRsc_tbl,
1800 p_terrrsc_access_tbl => l_terrRsc_access_tbl,
1801 x_terrrsc_out_tbl => x_TerrRsc_Out_Tbl,
1802 x_terrrsc_access_out_tbl => x_TerrRsc_Access_Out_Tbl
1803 );
1804
1805 if x_Return_Status='S' then
1806 write_log(2,' Resource created for NA territory # ' ||x_terr_id);
1807 else
1808 x_msg_data := substr(fnd_msg_pub.get(1, fnd_api.g_false),1,254);
1809 write_log(2,x_msg_data);
1810 write_log(2, ' Failed in resource creation for NA territory # ' ||
1811 x_terr_id);
1812 end if;
1813
1814 else
1815 x_msg_data := substr(fnd_msg_pub.get(1, fnd_api.g_false),1,254);
1816 write_log(2,substr(x_msg_data,1,254));
1817 WRITE_LOG(2,'ERROR: NA TERRITORY CREATION FAILED ' ||
1818 'FOR NAMED_ACCOUNT_ID# ' || naterr.named_account_id );
1819 end if; /* END: Successful Territory creation? */
1820
1821 end loop; /* naterr in get_party_duns */
1822 END IF; /* ( terr_group.matching_rule_code IN ('3') THEN */
1823 /*************************************************************/
1824 /* (5) END: CREATE NA TERRITORIES FOR NAs IN TERRITORY GROUP */
1825 /* USING DUNS# QUALIFIER */
1826 /*************************************************************/
1827
1828 /* dbms_output.put_line('terr_group.terr_group_name='||
1829 terr_group.terr_group_name);
1830 dbms_output.put_line('terr_group.matching_rule_code='||
1831 terr_group.matching_rule_code);*/
1832
1833 /***************************************************************/
1834 /* (6) START: CREATE NA TERRITORIES FOR NAs IN TERRITORY GROUP */
1835 /* USING CUSTOMER NAME RANGE AND POSTAL CODE QUALIFIERS */
1836 /***************************************************************/
1837 IF ( terr_group.matching_rule_code IN ('1', '2') ) THEN
1838 FOR naterr in get_party_name(terr_group.terr_group_id) LOOP
1839
1840 --write_log(2,'na '||naterr.named_account_id);
1841 l_terr_usgs_tbl:=l_terr_usgs_empty_tbl;
1842 l_terr_qualtypeusgs_tbl:=l_terr_qualtypeusgs_empty_tbl;
1843
1844 l_terr_qual_tbl := l_terr_qual_empty_tbl;
1845 l_terr_values_tbl := l_terr_values_empty_tbl;
1846
1847
1848 l_TerrRsc_Tbl := l_TerrRsc_empty_Tbl;
1849 l_TerrRsc_Access_Tbl := l_TerrRsc_Access_empty_Tbl;
1850
1851 /* TERRITORY HEADER */
1852 /* Ensure static TERR_ID to benefit TAP Performance */
1853 BEGIN
1854
1855 l_terr_exists := 0;
1856
1857 SELECT COUNT(*)
1858 INTO l_terr_exists
1859 FROM jtf_terr_all jt
1860 WHERE jt.terr_id = naterr.terr_group_account_id * -10000;
1861
1862 IF (l_terr_exists = 0) THEN
1863 l_terr_all_rec.TERR_ID := naterr.terr_group_account_id * -10000;
1864 ELSE
1865 l_terr_all_rec.TERR_ID := NULL;
1866 END IF;
1867
1868 EXCEPTION
1869 WHEN NO_DATA_FOUND THEN
1870 l_terr_all_rec.TERR_ID := naterr.terr_group_account_id * -10000;
1871 END;
1872
1873 l_terr_all_rec.LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
1874 l_terr_all_rec.LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1875 l_terr_all_rec.CREATION_DATE:= terr_group.CREATION_DATE;
1876 l_terr_all_rec.CREATED_BY := terr_group.CREATED_BY ;
1877 l_terr_all_rec.LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1878
1879 l_terr_all_rec.APPLICATION_SHORT_NAME:= G_APP_SHORT_NAME;
1880 l_terr_all_rec.NAME:= naterr.name;
1881 l_terr_all_rec.start_date_active := terr_group.active_from_date ;
1882 l_terr_all_rec.end_date_active := terr_group.active_to_date;
1883 l_terr_all_rec.PARENT_TERRITORY_ID:= l_nacat;
1884 l_terr_all_rec.RANK := terr_group.RANK + 20;
1885 l_terr_all_rec.TEMPLATE_TERRITORY_ID:= NULL;
1886 l_terr_all_rec.TEMPLATE_FLAG := 'N';
1887 l_terr_all_rec.ESCALATION_TERRITORY_ID := NULL;
1888 l_terr_all_rec.ESCALATION_TERRITORY_FLAG := 'N';
1889 l_terr_all_rec.OVERLAP_ALLOWED_FLAG := NULL;
1890 l_terr_all_rec.DESCRIPTION:= naterr.name;
1891 l_terr_all_rec.UPDATE_FLAG :='N';
1892 l_terr_all_rec.AUTO_ASSIGN_RESOURCES_FLAG :=NULL;
1893
1894 l_terr_all_rec.ORG_ID :=terr_group.ORG_ID ;
1895 l_terr_all_rec.NUM_WINNERS :=null ;
1896
1897
1898 /* Oracle Sales and Telesales Usage */
1899 SELECT JTF_TERR_USGS_S.nextval
1900 into l_terr_usg_id
1901 FROM DUAL;
1902
1903 l_terr_usgs_tbl(1).TERR_USG_ID := l_terr_usg_id;
1904 l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
1905 l_terr_usgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1906 l_terr_usgs_tbl(1).CREATION_DATE:= terr_group.CREATION_DATE;
1907 l_terr_usgs_tbl(1).CREATED_BY := terr_group.CREATED_BY;
1908 l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
1909 l_terr_usgs_tbl(1).TERR_ID:= null;
1910 l_terr_usgs_tbl(1).SOURCE_ID:=-1001;
1911 l_terr_usgs_tbl(1).ORG_ID:= terr_group.ORG_ID;
1912 i:=0;
1913
1914 /* BEGIN: For each Access Type defined for the Territory Group */
1915 for acctype in get_NON_OVLY_na_trans(naterr.terr_group_account_id) LOOP
1916
1917 i:=i+1;
1918
1919 /* ACCOUNT TRANSACTION TYPE */
1920 if acctype.access_type='ACCOUNT' then
1921
1922 SELECT JTF_TERR_QTYPE_USGS_S.nextval
1923 into l_terr_qtype_usg_id
1924 FROM DUAL;
1925 l_terr_qualtypeusgs_tbl(i).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
1926 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
1927 l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1928 l_terr_qualtypeusgs_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
1929 l_terr_qualtypeusgs_tbl(i).CREATED_BY := terr_group.CREATED_BY;
1930 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1931 l_terr_qualtypeusgs_tbl(i).TERR_ID:= null;
1932 l_terr_qualtypeusgs_tbl(i).QUAL_TYPE_USG_ID:=-1001;
1933 l_terr_qualtypeusgs_tbl(i).ORG_ID:=terr_group.ORG_ID;
1934
1935 /* LEAD TRANSACTION TYPE */
1936 elsif acctype.access_type='LEAD' then
1937
1938 SELECT JTF_TERR_QTYPE_USGS_S.nextval
1939 into l_terr_qtype_usg_id
1940 FROM DUAL;
1941 l_terr_qualtypeusgs_tbl(i).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
1942 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
1943 l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1944 l_terr_qualtypeusgs_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
1945 l_terr_qualtypeusgs_tbl(i).CREATED_BY := terr_group.CREATED_BY;
1946 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1947 l_terr_qualtypeusgs_tbl(i).TERR_ID:= null;
1948 l_terr_qualtypeusgs_tbl(i).QUAL_TYPE_USG_ID:=-1002;
1949 l_terr_qualtypeusgs_tbl(i).ORG_ID:=terr_group.ORG_ID;
1950
1951 /* OPPORTUNITY TRANSACTION TYPE */
1952 elsif acctype.access_type='OPPORTUNITY' then
1953
1954 SELECT JTF_TERR_QTYPE_USGS_S.nextval
1955 into l_terr_qtype_usg_id
1956 FROM DUAL;
1957 l_terr_qualtypeusgs_tbl(i).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
1958 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
1959 l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1960 l_terr_qualtypeusgs_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
1961 l_terr_qualtypeusgs_tbl(i).CREATED_BY := terr_group.CREATED_BY;
1962 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1963 l_terr_qualtypeusgs_tbl(i).TERR_ID:= null;
1964 l_terr_qualtypeusgs_tbl(i).QUAL_TYPE_USG_ID:=-1003;
1965 l_terr_qualtypeusgs_tbl(i).ORG_ID:=terr_group.ORG_ID;
1966
1967 end if;
1968
1969 end loop;
1970 /* END: For each Access Type defined for the Territory Group */
1971
1972
1973 /*
1974 ** get Named Account Customer Keyname and Postal Code Mapping
1975 ** rules, to use as territory definition qualifier values
1976 */
1977 j:=0;
1978 K:=0;
1979 l_prev_qual_usg_id:=1;
1980 FOR qval IN match_rule1( naterr.named_account_id ) LOOP
1981
1982 /* new qualifier, i.e., Customer Name Range or Postal Code:
1983 ** driven by ORDER BY on p_qual */
1984 IF l_prev_qual_usg_id <> qval.qual_usg_id THEN
1985
1986 j:=j+1;
1987
1988 SELECT JTF_TERR_QUAL_S.nextval
1989 into l_terr_qual_id
1990 FROM DUAL;
1991
1992 l_terr_qual_tbl(j).TERR_QUAL_ID :=l_terr_qual_id;
1993 l_terr_qual_tbl(j).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
1994 l_terr_qual_tbl(j).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
1995 l_terr_qual_tbl(j).CREATION_DATE:= terr_group.CREATION_DATE;
1996 l_terr_qual_tbl(j).CREATED_BY := terr_group.CREATED_BY;
1997 l_terr_qual_tbl(j).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
1998 l_terr_qual_tbl(j).TERR_ID:=null;
1999 l_terr_qual_tbl(j).QUAL_USG_ID :=qval.qual_usg_id;
2000 l_terr_qual_tbl(j).QUALIFIER_MODE:=NULL;
2001 l_terr_qual_tbl(j).OVERLAP_ALLOWED_FLAG:='N';
2002 l_terr_qual_tbl(j).USE_TO_NAME_FLAG:=NULL;
2003 l_terr_qual_tbl(j).GENERATE_FLAG:=NULL;
2004 l_terr_qual_tbl(j).ORG_ID:=terr_group.ORG_ID;
2005 l_prev_qual_usg_id:= qval.qual_usg_id;
2006
2007 END IF;
2008
2009 k:=k+1;
2010
2011 l_terr_values_tbl(k).TERR_VALUE_ID:=null;
2012 l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
2013 l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
2014 l_terr_values_tbl(k).CREATED_BY := terr_group.CREATED_BY;
2015 l_terr_values_tbl(k).CREATION_DATE:= terr_group.CREATION_DATE;
2016 l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
2017 l_terr_values_tbl(k).TERR_QUAL_ID :=l_terr_qual_id ;
2018 l_terr_values_tbl(k).INCLUDE_FLAG :=NULL;
2019 l_terr_values_tbl(k).COMPARISON_OPERATOR := qval.COMPARISON_OPERATOR;
2020 l_terr_values_tbl(k).LOW_VALUE_CHAR:= qval.value1_char;
2021 l_terr_values_tbl(k).HIGH_VALUE_CHAR:=qval.value2_char;
2022 l_terr_values_tbl(k).LOW_VALUE_NUMBER :=null;
2023 l_terr_values_tbl(k).HIGH_VALUE_NUMBER :=null;
2024 l_terr_values_tbl(k).VALUE_SET :=NULL;
2025 l_terr_values_tbl(k).INTEREST_TYPE_ID :=null;
2026 l_terr_values_tbl(k).PRIMARY_INTEREST_CODE_ID:=null;
2027 l_terr_values_tbl(k).SECONDARY_INTEREST_CODE_ID:=null;
2028 l_terr_values_tbl(k).CURRENCY_CODE :=null;
2029 l_terr_values_tbl(k).ORG_ID :=terr_group.ORG_ID;
2030 l_terr_values_tbl(k).ID_USED_FLAG :='N';
2031 l_terr_values_tbl(k).LOW_VALUE_CHAR_ID :=null;
2032
2033 l_terr_values_tbl(k).qualifier_tbl_index := j;
2034
2035 end loop; /* qval IN pqual */
2036
2037
2038 l_init_msg_list :=FND_API.G_TRUE;
2039
2040 -- 07/08/03: JDOCHERT: bug#3023653
2041 --mo_global.set_org_context(terr_group.ORG_ID,null);
2042 --
2043
2044 jtf_territory_pvt.create_territory (
2045 p_api_version_number => l_api_version_number,
2046 p_init_msg_list => l_init_msg_list,
2047 p_commit => l_commit,
2048 p_validation_level => fnd_api.g_valid_level_NONE,
2049 x_return_status => x_return_status,
2050 x_msg_count => x_msg_count,
2051 x_msg_data => x_msg_data,
2052 p_terr_all_rec => l_terr_all_rec,
2053 p_terr_usgs_tbl => l_terr_usgs_tbl,
2054 p_terr_qualtypeusgs_tbl => l_terr_qualtypeusgs_tbl,
2055 p_terr_qual_tbl => l_terr_qual_tbl,
2056 p_terr_values_tbl => l_terr_values_tbl,
2057 x_terr_id => x_terr_id,
2058 x_terr_usgs_out_tbl => x_terr_usgs_out_tbl,
2059 x_terr_qualtypeusgs_out_tbl => x_terr_qualtypeusgs_out_tbl,
2060 x_terr_qual_out_tbl => x_terr_qual_out_tbl,
2061 x_terr_values_out_tbl => x_terr_values_out_tbl
2062 );
2063
2064 write_log(2,' NA territory created = '||naterr.name);
2065
2066
2067 /* BEGIN: Successful Territory creation? */
2068 if x_return_status = 'S' then
2069
2070 -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID and CATCH_ALL_FLAG
2071 -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
2072 UPDATE JTF_TERR_ALL
2073 set TERR_GROUP_FLAG = 'Y'
2074 , TERR_GROUP_ID = terr_group.TERR_GROUP_ID
2075 , CATCH_ALL_FLAG = 'N'
2076 , NAMED_ACCOUNT_FLAG = 'Y'
2077 , TERR_GROUP_ACCOUNT_ID = naterr.terr_group_account_id
2078 where terr_id = x_terr_id;
2079
2080 --write_log(2,terr_group.terr_group_id);
2081 --write_log(2,tran_type.role_code);
2082 l_init_msg_list :=FND_API.G_TRUE;
2083 i := 0;
2084 a := 0;
2085
2086 FOR tran_type in role_interest_nonpi(terr_group.Terr_gROUP_ID)
2087 LOOP
2088 --dbms_output.put_line('tran_type.role_code '||tran_type.role_code);
2089
2090 /* JRADHAKR changed the parameter from l_terr_group_id to l_terr_group_acct_id */
2091 FOR rsc in resource_grp(naterr.terr_group_account_id,tran_type.role_code)
2092 loop
2093 i:=i+1;
2094
2095 SELECT JTF_TERR_RSC_S.nextval
2096 into l_terr_rsc_id
2097 FROM DUAL;
2098
2099 l_TerrRsc_Tbl(i).terr_id := x_terr_id;
2100 l_TerrRsc_Tbl(i).TERR_RSC_ID :=l_terr_rsc_id;
2101 l_TerrRsc_Tbl(i).LAST_UPDATE_DATE:=terr_group.LAST_UPDATE_DATE;
2102 l_TerrRsc_Tbl(i).LAST_UPDATED_BY:=terr_group.LAST_UPDATED_BY;
2103 l_TerrRsc_Tbl(i).CREATION_DATE:=terr_group.CREATION_DATE;
2104 l_TerrRsc_Tbl(i).CREATED_BY:=terr_group.CREATED_BY;
2105 l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
2106 --l_TerrRsc_Tbl(i).TERR_ID:=terr_group.TERRITORY_ID;
2107 l_TerrRsc_Tbl(i).RESOURCE_ID:=rsc.resource_id;
2108 l_TerrRsc_Tbl(i).RESOURCE_TYPE:=rsc.rsc_resource_type;
2109 l_TerrRsc_Tbl(i).ROLE:=tran_type.role_code;
2110 --l_TerrRsc_Tbl(i).ROLE:=l_role;
2111 l_TerrRsc_Tbl(i).PRIMARY_CONTACT_FLAG:='N';
2112 l_TerrRsc_Tbl(i).START_DATE_ACTIVE:=terr_group.active_from_date ;
2113 l_TerrRsc_Tbl(i).END_DATE_ACTIVE:=terr_group.active_to_date ;
2114 l_TerrRsc_Tbl(i).ORG_ID:=terr_group.ORG_ID;
2115 l_TerrRsc_Tbl(i).FULL_ACCESS_FLAG:='Y';
2116 l_TerrRsc_Tbl(i).GROUP_ID:=rsc.rsc_group_id;
2117 --dbms_output.put_line('rsc.resource_id '||rsc.resource_id);
2118
2119
2120 FOR rsc_acc in NON_OVLY_role_access(terr_group.terr_group_id,tran_type.role_code) LOOP
2121 --dbms_output.put_line('rsc_acc.access_type '||rsc_acc.access_type);
2122 a := a+1;
2123
2124 /* ACCOUNT ACCESS TYPE */
2125 IF (rsc_acc.access_type= 'ACCOUNT') THEN
2126
2127 SELECT JTF_TERR_RSC_ACCESS_S.nextval
2128 into l_terr_rsc_access_id
2129 FROM DUAL;
2130 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
2131 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
2132 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2133 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
2134 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
2135 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2136 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
2137 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'ACCOUNT';
2138 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
2139 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:= i;
2140
2141 /* OPPORTUNITY ACCESS TYPE */
2142 elsif rsc_acc.access_type= 'OPPORTUNITY' then
2143
2144 SELECT JTF_TERR_RSC_ACCESS_S.nextval
2145 into l_terr_rsc_access_id
2146 FROM DUAL;
2147 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
2148 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
2149 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2150 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
2151 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
2152 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2153 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
2154 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'OPPOR';
2155 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
2156 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:= i;
2157
2158
2159 /* LEAD ACCESS TYPE */
2160 elsif rsc_acc.access_type= 'LEAD' then
2161
2162 SELECT JTF_TERR_RSC_ACCESS_S.nextval
2163 into l_terr_rsc_access_id
2164 FROM DUAL;
2165 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
2166 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
2167 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2168 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
2169 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
2170 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2171 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
2172 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'LEAD';
2173 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
2174 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:= i;
2175 end if;
2176 end loop; /* FOR rsc_acc in NON_OVLY_role_access */
2177
2178 end loop; /* FOR rsc in resource_grp */
2179
2180 end loop;/* FOR tran_type in role_interest_nonpi */
2181
2182 l_init_msg_list :=FND_API.G_TRUE;
2183
2184 -- 07/08/03: JDOCHERT: bug#3023653
2185 jtf_territory_resource_pvt.create_terrresource (
2186 p_api_version_number => l_Api_Version_Number,
2187 p_init_msg_list => l_Init_Msg_List,
2188 p_commit => l_Commit,
2189 p_validation_level => fnd_api.g_valid_level_NONE,
2190 x_return_status => x_Return_Status,
2191 x_msg_count => x_Msg_Count,
2192 x_msg_data => x_msg_data,
2193 p_terrrsc_tbl => l_TerrRsc_tbl,
2194 p_terrrsc_access_tbl => l_terrRsc_access_tbl,
2195 x_terrrsc_out_tbl => x_TerrRsc_Out_Tbl,
2196 x_terrrsc_access_out_tbl => x_TerrRsc_Access_Out_Tbl
2197 );
2198
2199 if x_Return_Status='S' then
2200 write_log(2,' Resource created for NA territory # ' ||x_terr_id);
2201 else
2202 x_msg_data := substr(fnd_msg_pub.get(1, fnd_api.g_false),1,254);
2203 write_log(2,x_msg_data);
2204 write_log(2, ' Failed in resource creation for NA territory # ' ||
2205 x_terr_id);
2206 end if;
2207
2208 else
2209 x_msg_data := substr(fnd_msg_pub.get(1, fnd_api.g_false),1,254);
2210 write_log(2,substr(x_msg_data,1,254));
2211 WRITE_LOG(2,'ERROR: NA TERRITORY CREATION FAILED ' ||
2212 'FOR NAMED_ACCOUNT_ID# ' || naterr.named_account_id );
2213 end if; /* END: Successful Territory creation? */
2214
2215 end loop; /* naterr in get_party_name */
2216 END IF; /* terr_group.matching_rule_code IN ('1', '2') THEN */
2217 /*************************************************************/
2218 /* (6) END: CREATE NA TERRITORIES FOR NAs IN TERRITORY GROUP */
2219 /* USING CUSTOMER NAME RANGE AND POSTAL CODE QUALIFIERS */
2220 /*************************************************************/
2221
2222 /********************************************************/
2223 /* delete the role and access */
2224 /********************************************************/
2225 if l_ovnon_flag = 'Y' then
2226
2227 for i in l_overnon_role_tbl.first.. l_overnon_role_tbl.last
2228 loop
2229 delete from jtf_tty_terr_grp_roles
2230 where TERR_GROUP_ROLE_ID=l_overnon_role_tbl(i).grp_role_id;
2231 --dbms_output.put_line('deleted');
2232 delete from jtf_tty_role_access
2233 where TERR_GROUP_ROLE_ID=l_overnon_role_tbl(i).grp_role_id;
2234 end loop;
2235 end if;
2236
2237
2238 end if;
2239 /* END: if Territory Group exists with Named Accounts
2240 ** then auto-create territory definitions */
2241
2242
2243
2244 /*********************************************************************/
2245 /*********************************************************************/
2246 /************** OVERLAY TERRITORY CREATION ***************************/
2247 /*********************************************************************/
2248 /*********************************************************************/
2249
2250 /* if any role with PI and Account access and no non pi role exist */
2251 /* we need to create a new branch with Named Account */
2252
2253 /* OVERLAY BRANCH */
2254 BEGIN
2255
2256 SELECT COUNT( DISTINCT b.role_code )
2257 into l_pi_count
2258 from jtf_rs_roles_vl r
2259 , jtf_tty_role_prod_int a
2260 , jtf_tty_terr_grp_roles b
2261 where r.role_code = b.role_code
2262 and a.terr_group_role_id = b.terr_group_role_id
2263 and b.terr_group_id = TERR_GROUP.TERR_GROUP_ID
2264 AND EXISTS (
2265 /* Named Account exists with Salesperson with this role */
2266 SELECT NULL
2267 FROM jtf_tty_named_acct_rsc nar, jtf_tty_terr_grp_accts tga
2268 WHERE tga.terr_group_account_id = nar.terr_group_account_id
2269 AND tga.terr_group_id = b.terr_group_id
2270 AND nar.rsc_role_code = b.role_code )
2271 AND ROWNUM < 2;
2272
2273 EXCEPTION
2274 WHEN OTHERS THEN
2275 NUll;
2276 END;
2277
2278
2279 /* are there overlay roles, i.e., are there roles with Product
2280 ** Interests defined for this Territory Group */
2281 if l_pi_count > 0 then
2282
2283 /***************************************************************/
2284 /* (7) START: CREATE TOP-LEVEL TERRITORY FOR OVERLAY BRANCH OF */
2285 /* TERRITORY GROUP */
2286 /***************************************************************/
2287 FOR topt in topterr(terr_group.PARENT_TERR_ID) LOOP
2288
2289 l_terr_usgs_tbl:=l_terr_usgs_empty_tbl;
2290 l_terr_qualtypeusgs_tbl:=l_terr_qualtypeusgs_empty_tbl;
2291 l_terr_qual_tbl:=l_terr_qual_empty_tbl;
2292 l_terr_values_tbl:=l_terr_values_empty_tbl;
2293 l_TerrRsc_Tbl := l_TerrRsc_empty_Tbl;
2294 l_TerrRsc_Access_Tbl := l_TerrRsc_Access_empty_Tbl;
2295
2296 l_terr_all_rec.TERR_ID := null;
2297 l_terr_all_rec.LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
2298 l_terr_all_rec.LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2299 l_terr_all_rec.CREATION_DATE:= terr_group.CREATION_DATE;
2300 l_terr_all_rec.CREATED_BY := terr_group.CREATED_BY ;
2301 l_terr_all_rec.LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2302
2303 l_terr_all_rec.APPLICATION_SHORT_NAME:= G_APP_SHORT_NAME;
2304 l_terr_all_rec.NAME:= terr_group.terr_group_name || ' (OVERLAY)';
2305 l_terr_all_rec.start_date_active := terr_group.active_from_date ;
2306 l_terr_all_rec.end_date_active := terr_group.active_to_date;
2307 l_terr_all_rec.PARENT_TERRITORY_ID:= topt.PARENT_TERRITORY_ID;
2308 l_terr_all_rec.RANK := topt.RANK;
2309 l_terr_all_rec.TEMPLATE_TERRITORY_ID:= NULL;
2310 l_terr_all_rec.TEMPLATE_FLAG := 'N';
2311 l_terr_all_rec.ESCALATION_TERRITORY_ID := NULL;
2312 l_terr_all_rec.ESCALATION_TERRITORY_FLAG := 'N';
2313 l_terr_all_rec.OVERLAP_ALLOWED_FLAG := NULL;
2314 l_terr_all_rec.DESCRIPTION:= topt.DESCRIPTION;
2315 l_terr_all_rec.UPDATE_FLAG :='N';
2316 l_terr_all_rec.AUTO_ASSIGN_RESOURCES_FLAG :=NULL;
2317
2318 l_terr_all_rec.ORG_ID :=terr_group.ORG_ID ;
2319 l_terr_all_rec.NUM_WINNERS :=l_pi_count ;
2320
2321 /* ORACLE SALES AND TELESALES USAGE */
2322 SELECT JTF_TERR_USGS_S.nextval
2323 into l_terr_usg_id
2324 FROM DUAL;
2325
2326 l_terr_usgs_tbl(1).TERR_USG_ID := l_terr_usg_id;
2327 l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
2328 l_terr_usgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2329 l_terr_usgs_tbl(1).CREATION_DATE:= terr_group.CREATION_DATE;
2330 l_terr_usgs_tbl(1).CREATED_BY := terr_group.CREATED_BY;
2331 l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
2332 l_terr_usgs_tbl(1).TERR_ID:= null;
2333 l_terr_usgs_tbl(1).SOURCE_ID:=-1001;
2334 l_terr_usgs_tbl(1).ORG_ID:= terr_group.ORG_ID;
2335
2336
2337 /* LEAD TRANSACTION TYPE */
2338 SELECT JTF_TERR_QTYPE_USGS_S.nextval
2339 into l_terr_qtype_usg_id
2340 FROM DUAL;
2341
2342 l_terr_qualtypeusgs_tbl(1).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
2343 l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
2344 l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2345 l_terr_qualtypeusgs_tbl(1).CREATION_DATE:= terr_group.CREATION_DATE;
2346 l_terr_qualtypeusgs_tbl(1).CREATED_BY := terr_group.CREATED_BY;
2347 l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2348 l_terr_qualtypeusgs_tbl(1).TERR_ID:= null;
2349 l_terr_qualtypeusgs_tbl(1).QUAL_TYPE_USG_ID:=-1002;
2350 l_terr_qualtypeusgs_tbl(1).ORG_ID:=terr_group.ORG_ID;
2351
2352 /* OPPORTUNITY TRANSACTION TYPE */
2353 SELECT JTF_TERR_QTYPE_USGS_S.nextval
2354 into l_terr_qtype_usg_id
2355 FROM DUAL;
2356
2357 l_terr_qualtypeusgs_tbl(2).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
2358 l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
2359 l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2360 l_terr_qualtypeusgs_tbl(2).CREATION_DATE:= terr_group.CREATION_DATE;
2361 l_terr_qualtypeusgs_tbl(2).CREATED_BY := terr_group.CREATED_BY;
2362 l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2363 l_terr_qualtypeusgs_tbl(2).TERR_ID:= null;
2364 l_terr_qualtypeusgs_tbl(2).QUAL_TYPE_USG_ID:=-1003;
2365 l_terr_qualtypeusgs_tbl(2).ORG_ID:=terr_group.ORG_ID;
2366
2367
2368 /*
2369 ** get Top-Level Parent's Qualifier and values and
2370 ** aad them to Overlay branch top-level territory
2371 */
2372 j:=0;
2373 k:=0;
2374 l_prev_qual_usg_id:=1;
2375 FOR csr_qual IN csr_get_qual ( topt.terr_id ) LOOP
2376
2377 j:=j+1;
2378
2379 SELECT JTF_TERR_QUAL_S.nextval
2380 INTO l_terr_qual_id
2381 FROM DUAL;
2382
2383 l_terr_qual_tbl(j).TERR_QUAL_ID := l_terr_qual_id;
2384 l_terr_qual_tbl(j).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
2385 l_terr_qual_tbl(j).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2386 l_terr_qual_tbl(j).CREATION_DATE:= terr_group.CREATION_DATE;
2387 l_terr_qual_tbl(j).CREATED_BY := terr_group.CREATED_BY;
2388 l_terr_qual_tbl(j).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2389 l_terr_qual_tbl(j).TERR_ID:= null;
2390
2391 /* Top_level Parent's Qualifier */
2392 l_terr_qual_tbl(j).QUAL_USG_ID := csr_qual.qual_usg_id;
2393
2394 l_terr_qual_tbl(j).QUALIFIER_MODE:= NULL;
2395 l_terr_qual_tbl(j).OVERLAP_ALLOWED_FLAG:='Y';
2396 l_terr_qual_tbl(j).USE_TO_NAME_FLAG:=NULL;
2397 l_terr_qual_tbl(j).GENERATE_FLAG:=NULL;
2398 l_terr_qual_tbl(j).ORG_ID:=terr_group.ORG_ID;
2399
2400
2401 FOR csr_qual_val IN csr_get_qual_val (csr_qual.terr_qual_id) LOOP
2402
2403 k:=k+1;
2404
2405 l_terr_values_tbl(k).TERR_VALUE_ID := NULL;
2406 l_terr_values_tbl(k).LAST_UPDATED_BY := G_USER_ID;
2407 l_terr_values_tbl(k).LAST_UPDATE_DATE:= G_SYSDATE;
2408 l_terr_values_tbl(k).CREATED_BY := G_USER_ID;
2409 l_terr_values_tbl(k).CREATION_DATE:= G_SYSDATE;
2410 l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= G_LOGIN_ID;
2411
2412 l_terr_values_tbl(k).TERR_QUAL_ID := l_terr_qual_id ;
2413
2414 l_terr_values_tbl(k).INCLUDE_FLAG := csr_qual_val.INCLUDE_FLAG;
2415 l_terr_values_tbl(k).COMPARISON_OPERATOR := csr_qual_val.COMPARISON_OPERATOR;
2416 l_terr_values_tbl(k).LOW_VALUE_CHAR := csr_qual_val.LOW_VALUE_CHAR;
2417 l_terr_values_tbl(k).HIGH_VALUE_CHAR := csr_qual_val.HIGH_VALUE_CHAR;
2418 l_terr_values_tbl(k).LOW_VALUE_NUMBER := csr_qual_val.LOW_VALUE_NUMBER;
2419 l_terr_values_tbl(k).HIGH_VALUE_NUMBER := csr_qual_val.HIGH_VALUE_NUMBER;
2420 l_terr_values_tbl(k).VALUE_SET := csr_qual_val.VALUE_SET;
2421 l_terr_values_tbl(k).INTEREST_TYPE_ID := csr_qual_val.INTEREST_TYPE_ID;
2422 l_terr_values_tbl(k).PRIMARY_INTEREST_CODE_ID := csr_qual_val.PRIMARY_INTEREST_CODE_ID;
2423 l_terr_values_tbl(k).SECONDARY_INTEREST_CODE_ID := csr_qual_val.SECONDARY_INTEREST_CODE_ID;
2424 l_terr_values_tbl(k).CURRENCY_CODE := csr_qual_val.CURRENCY_CODE;
2425 l_terr_values_tbl(k).ID_USED_FLAG := csr_qual_val.ID_USED_FLAG;
2426 l_terr_values_tbl(k).LOW_VALUE_CHAR_ID := csr_qual_val.LOW_VALUE_CHAR_ID;
2427
2428 l_terr_values_tbl(k).ORG_ID := terr_group.org_id;
2429
2430 /* What Qualifier Values relate to Qualifier */
2431 l_terr_values_tbl(k).qualifier_tbl_index := j;
2432
2433
2434 END LOOP; /* csr_qual_val IN csr_get_qual_val */
2435 end loop; /* csr_qual IN csr_get_qual */
2436
2437
2438 l_init_msg_list :=FND_API.G_TRUE;
2439
2440 -- 07/08/03: JDOCHERT: bug#3023653
2441 --mo_global.set_org_context(terr_group.ORG_ID,null);
2442 --
2443 jtf_territory_pvt.create_territory (
2444 p_api_version_number => l_api_version_number,
2445 p_init_msg_list => l_init_msg_list,
2446 p_commit => l_commit,
2447 p_validation_level => fnd_api.g_valid_level_NONE,
2448 x_return_status => x_return_status,
2449 x_msg_count => x_msg_count,
2450 x_msg_data => x_msg_data,
2451 p_terr_all_rec => l_terr_all_rec,
2452 p_terr_usgs_tbl => l_terr_usgs_tbl,
2453 p_terr_qualtypeusgs_tbl => l_terr_qualtypeusgs_tbl,
2454 p_terr_qual_tbl => l_terr_qual_tbl,
2455 p_terr_values_tbl => l_terr_values_tbl,
2456 x_terr_id => x_terr_id,
2457 x_terr_usgs_out_tbl => x_terr_usgs_out_tbl,
2458 x_terr_qualtypeusgs_out_tbl => x_terr_qualtypeusgs_out_tbl,
2459 x_terr_qual_out_tbl => x_terr_qual_out_tbl,
2460 x_terr_values_out_tbl => x_terr_values_out_tbl
2461 );
2462
2463 write_log(2,' OVERLAY Top level Territory Created,territory_id# '||x_terr_id);
2464
2465 if x_return_status = 'S' then
2466
2467 -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID
2468 UPDATE JTF_TERR_ALL
2469 set TERR_GROUP_FLAG = 'Y'
2470 , TERR_GROUP_ID = terr_group.TERR_GROUP_ID
2471 where terr_id = x_terr_id;
2472
2473 end if;
2474
2475 l_overlay_top :=x_terr_id;
2476
2477 end loop;/* top level territory */
2478 /***************************************************************/
2479 /* (7) END: CREATE TOP-LEVEL TERRITORY FOR OVERLAY BRANCH OF */
2480 /* TERRITORY GROUP */
2481 /***************************************************************/
2482
2483
2484 /***************************************************************/
2485 /* (8) START: CREATE OVERLAY TERRITORIES FOR TERRITORY GROUP */
2486 /* USING DUNS# QUALIFIER */
2487 /***************************************************************/
2488 IF ( terr_group.matching_rule_code IN ('2', '3') ) THEN
2489
2490 FOR overlayterr in get_OVLY_party_duns(terr_group.terr_group_id) LOOP
2491
2492 l_terr_usgs_tbl:=l_terr_usgs_empty_tbl;
2493 l_terr_qualtypeusgs_tbl:=l_terr_qualtypeusgs_empty_tbl;
2494 l_terr_qual_tbl:=l_terr_qual_empty_tbl;
2495 l_terr_values_tbl:=l_terr_values_empty_tbl;
2496 l_TerrRsc_Tbl := l_TerrRsc_empty_Tbl;
2497 l_TerrRsc_Access_Tbl := l_TerrRsc_Access_empty_Tbl;
2498
2499 l_terr_all_rec.TERR_ID := null;
2500 l_terr_all_rec.LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
2501 l_terr_all_rec.LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2502 l_terr_all_rec.CREATION_DATE:= terr_group.CREATION_DATE;
2503 l_terr_all_rec.CREATED_BY := terr_group.CREATED_BY ;
2504 l_terr_all_rec.LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2505
2506 l_terr_all_rec.APPLICATION_SHORT_NAME:= G_APP_SHORT_NAME;
2507 l_terr_all_rec.NAME:= overlayterr.name || ' (OVERLAY DUNS#)';
2508 l_terr_all_rec.start_date_active := terr_group.active_from_date ;
2509 l_terr_all_rec.end_date_active := terr_group.active_to_date;
2510 l_terr_all_rec.PARENT_TERRITORY_ID:= l_overlay_top;
2511 l_terr_all_rec.RANK := terr_group.RANK + 10;
2512 l_terr_all_rec.TEMPLATE_TERRITORY_ID:= NULL;
2513 l_terr_all_rec.TEMPLATE_FLAG := 'N';
2514 l_terr_all_rec.ESCALATION_TERRITORY_ID := NULL;
2515 l_terr_all_rec.ESCALATION_TERRITORY_FLAG := 'N';
2516 l_terr_all_rec.OVERLAP_ALLOWED_FLAG := NULL;
2517 l_terr_all_rec.DESCRIPTION:= overlayterr.name || ' (OVERLAY_DUNS#)';
2518 l_terr_all_rec.UPDATE_FLAG :='N';
2519 l_terr_all_rec.AUTO_ASSIGN_RESOURCES_FLAG :=NULL;
2520
2521 l_terr_all_rec.ORG_ID :=terr_group.ORG_ID ;
2522 l_terr_all_rec.NUM_WINNERS :=null ;
2523
2524
2525 SELECT JTF_TERR_USGS_S.nextval
2526 into l_terr_usg_id
2527 FROM DUAL;
2528
2529 l_terr_usgs_tbl(1).TERR_USG_ID := l_terr_usg_id;
2530 l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
2531 l_terr_usgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2532 l_terr_usgs_tbl(1).CREATION_DATE:= terr_group.CREATION_DATE;
2533 l_terr_usgs_tbl(1).CREATED_BY := terr_group.CREATED_BY;
2534 l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
2535 l_terr_usgs_tbl(1).TERR_ID:= null;
2536 l_terr_usgs_tbl(1).SOURCE_ID := -1001;
2537 l_terr_usgs_tbl(1).ORG_ID:= terr_group.ORG_ID;
2538
2539 SELECT JTF_TERR_QTYPE_USGS_S.nextval
2540 into l_terr_qtype_usg_id
2541 FROM DUAL;
2542
2543 l_terr_qualtypeusgs_tbl(1).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
2544 l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
2545 l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2546 l_terr_qualtypeusgs_tbl(1).CREATION_DATE:= terr_group.CREATION_DATE;
2547 l_terr_qualtypeusgs_tbl(1).CREATED_BY := terr_group.CREATED_BY;
2548 l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2549 l_terr_qualtypeusgs_tbl(1).TERR_ID:= null;
2550 l_terr_qualtypeusgs_tbl(1).QUAL_TYPE_USG_ID:=-1002;
2551 l_terr_qualtypeusgs_tbl(1).ORG_ID:=terr_group.ORG_ID;
2552
2553 SELECT JTF_TERR_QTYPE_USGS_S.nextval
2554 into l_terr_qtype_usg_id
2555 FROM DUAL;
2556
2557 l_terr_qualtypeusgs_tbl(2).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
2558 l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
2559 l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2560 l_terr_qualtypeusgs_tbl(2).CREATION_DATE:= terr_group.CREATION_DATE;
2561 l_terr_qualtypeusgs_tbl(2).CREATED_BY := terr_group.CREATED_BY;
2562 l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2563 l_terr_qualtypeusgs_tbl(2).TERR_ID:= null;
2564 l_terr_qualtypeusgs_tbl(2).QUAL_TYPE_USG_ID:=-1003;
2565 l_terr_qualtypeusgs_tbl(2).ORG_ID:=terr_group.ORG_ID;
2566
2567 SELECT JTF_TERR_QUAL_S.nextval
2568 into l_terr_qual_id
2569 FROM DUAL;
2570
2571 j:=0;
2572 K:=0;
2573 l_prev_qual_usg_id:=1;
2574
2575 for qval in match_rule3(overlayterr.named_account_id)
2576 loop
2577
2578 if l_prev_qual_usg_id <> qval.qual_usg_id then
2579
2580 j:=j+1;
2581 SELECT JTF_TERR_QUAL_S.nextval
2582 into l_terr_qual_id
2583 FROM DUAL;
2584
2585 l_terr_qual_tbl(j).TERR_QUAL_ID :=l_terr_qual_id;
2586 l_terr_qual_tbl(j).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
2587 l_terr_qual_tbl(j).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2588 l_terr_qual_tbl(j).CREATION_DATE:= terr_group.CREATION_DATE;
2589 l_terr_qual_tbl(j).CREATED_BY := terr_group.CREATED_BY;
2590 l_terr_qual_tbl(j).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2591 l_terr_qual_tbl(j).TERR_ID:=null;
2592 l_terr_qual_tbl(j).QUAL_USG_ID :=qval.qual_usg_id;
2593 l_terr_qual_tbl(j).QUALIFIER_MODE:=NULL;
2594 l_terr_qual_tbl(j).OVERLAP_ALLOWED_FLAG:='N';
2595 l_terr_qual_tbl(j).USE_TO_NAME_FLAG:=NULL;
2596 l_terr_qual_tbl(j).GENERATE_FLAG:=NULL;
2597 l_terr_qual_tbl(j).ORG_ID:=terr_group.ORG_ID;
2598 l_prev_qual_usg_id:= qval.qual_usg_id;
2599 end if;
2600
2601 k:=k+1;
2602
2603 l_terr_values_tbl(k).TERR_VALUE_ID:=null;
2604
2605 l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
2606 l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
2607 l_terr_values_tbl(k).CREATED_BY := terr_group.CREATED_BY;
2608 l_terr_values_tbl(k).CREATION_DATE:= terr_group.CREATION_DATE;
2609 l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
2610 l_terr_values_tbl(k).TERR_QUAL_ID :=l_terr_qual_id ;
2611 l_terr_values_tbl(k).INCLUDE_FLAG :=NULL;
2612 l_terr_values_tbl(k).COMPARISON_OPERATOR := qval.COMPARISON_OPERATOR;
2613 l_terr_values_tbl(k).LOW_VALUE_CHAR:= qval.value1_char;
2614 l_terr_values_tbl(k).HIGH_VALUE_CHAR:= NULL;
2615 l_terr_values_tbl(k).LOW_VALUE_NUMBER :=null;
2616 l_terr_values_tbl(k).HIGH_VALUE_NUMBER :=null;
2617 l_terr_values_tbl(k).VALUE_SET :=NULL;
2618 l_terr_values_tbl(k).INTEREST_TYPE_ID :=null;
2619 l_terr_values_tbl(k).PRIMARY_INTEREST_CODE_ID:=null;
2620 l_terr_values_tbl(k).SECONDARY_INTEREST_CODE_ID:=null;
2621 l_terr_values_tbl(k).CURRENCY_CODE :=null;
2622 l_terr_values_tbl(k).ORG_ID :=terr_group.ORG_ID;
2623 l_terr_values_tbl(k).ID_USED_FLAG :='N';
2624 l_terr_values_tbl(k).LOW_VALUE_CHAR_ID :=null;
2625
2626
2627 l_terr_values_tbl(k).qualifier_tbl_index := j;
2628
2629 end loop;
2630
2631 l_init_msg_list :=FND_API.G_TRUE;
2632
2633 -- 07/08/03: JDOCHERT: bug#3023653
2634 --mo_global.set_org_context(terr_group.ORG_ID,null);
2635 --
2636
2637 jtf_territory_pvt.create_territory (
2638 p_api_version_number => l_api_version_number,
2639 p_init_msg_list => l_init_msg_list,
2640 p_commit => l_commit,
2641 p_validation_level => fnd_api.g_valid_level_NONE,
2642 x_return_status => x_return_status,
2643 x_msg_count => x_msg_count,
2644 x_msg_data => x_msg_data,
2645 p_terr_all_rec => l_terr_all_rec,
2646 p_terr_usgs_tbl => l_terr_usgs_tbl,
2647 p_terr_qualtypeusgs_tbl => l_terr_qualtypeusgs_tbl,
2648 p_terr_qual_tbl => l_terr_qual_tbl,
2649 p_terr_values_tbl => l_terr_values_tbl,
2650 x_terr_id => x_terr_id,
2651 x_terr_usgs_out_tbl => x_terr_usgs_out_tbl,
2652 x_terr_qualtypeusgs_out_tbl => x_terr_qualtypeusgs_out_tbl,
2653 x_terr_qual_out_tbl => x_terr_qual_out_tbl,
2654 x_terr_values_out_tbl => x_terr_values_out_tbl
2655 );
2656
2657 write_log(2,' Named Account OVERLAY territory created: '||l_terr_all_rec.NAME);
2658
2659
2660 if x_return_status = 'S' then
2661
2662 -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID
2663 -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
2664 UPDATE JTF_TERR_ALL
2665 set TERR_GROUP_FLAG = 'Y'
2666 , TERR_GROUP_ID = terr_group.TERR_GROUP_ID
2667 , NAMED_ACCOUNT_FLAG = 'Y'
2668 , TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id
2669 where terr_id = x_terr_id;
2670
2671 l_overlay:=x_terr_id;
2672
2673 for pit in role_pi(terr_group.terr_group_id, overlayterr.terr_group_account_id) loop
2674
2675
2676 l_terr_usgs_tbl:=l_terr_usgs_empty_tbl;
2677 l_terr_qualtypeusgs_tbl:=l_terr_qualtypeusgs_empty_tbl;
2678 l_terr_qual_tbl:=l_terr_qual_empty_tbl;
2679 l_terr_values_tbl:=l_terr_values_empty_tbl;
2680 l_TerrRsc_Tbl := l_TerrRsc_empty_Tbl;
2681 l_TerrRsc_Access_Tbl := l_TerrRsc_Access_empty_Tbl;
2682
2683 l_role_counter := l_role_counter + 1;
2684
2685 l_terr_all_rec.TERR_ID := overlayterr.terr_group_account_id * -30 * l_role_counter;
2686 l_terr_all_rec.LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
2687 l_terr_all_rec.LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2688 l_terr_all_rec.CREATION_DATE:= terr_group.CREATION_DATE;
2689 l_terr_all_rec.CREATED_BY := terr_group.CREATED_BY ;
2690 l_terr_all_rec.LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2691
2692 l_terr_all_rec.APPLICATION_SHORT_NAME:= G_APP_SHORT_NAME;
2693
2694 l_terr_all_rec.NAME:= overlayterr.name || ': ' ||
2695 pit.role_name || ' (OVERLAY DUNS#)';
2696
2697 l_terr_all_rec.start_date_active := terr_group.active_from_date ;
2698 l_terr_all_rec.end_date_active := terr_group.active_to_date;
2699 l_terr_all_rec.PARENT_TERRITORY_ID:= l_overlay;
2700 l_terr_all_rec.RANK := terr_group.RANK+10;
2701 l_terr_all_rec.TEMPLATE_TERRITORY_ID:= NULL;
2702 l_terr_all_rec.TEMPLATE_FLAG := 'N';
2703 l_terr_all_rec.ESCALATION_TERRITORY_ID := NULL;
2704 l_terr_all_rec.ESCALATION_TERRITORY_FLAG := 'N';
2705 l_terr_all_rec.OVERLAP_ALLOWED_FLAG := NULL;
2706
2707 l_terr_all_rec.DESCRIPTION:= overlayterr.name || ': ' ||
2708 pit.role_name || ' (OVERLAY DUNS#)';
2709
2710 l_terr_all_rec.UPDATE_FLAG :='N';
2711 l_terr_all_rec.AUTO_ASSIGN_RESOURCES_FLAG :=NULL;
2712
2713 l_terr_all_rec.ORG_ID :=terr_group.ORG_ID ;
2714 l_terr_all_rec.NUM_WINNERS :=null ;
2715
2716 SELECT JTF_TERR_USGS_S.nextval
2717 into l_terr_usg_id
2718 FROM DUAL;
2719
2720 l_terr_usgs_tbl(1).TERR_USG_ID := l_terr_usg_id;
2721 l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
2722 l_terr_usgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2723 l_terr_usgs_tbl(1).CREATION_DATE:= terr_group.CREATION_DATE;
2724 l_terr_usgs_tbl(1).CREATED_BY := terr_group.CREATED_BY;
2725 l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
2726 l_terr_usgs_tbl(1).TERR_ID:= null;
2727 l_terr_usgs_tbl(1).SOURCE_ID:=-1001;
2728 l_terr_usgs_tbl(1).ORG_ID:= terr_group.ORG_ID;
2729
2730 i := 0;
2731 K:= 0;
2732 for acc_type in role_access(terr_group.terr_group_id,pit.role_code) loop
2733 --i:=i+1;
2734 --dbms_output.put_line('acc type '||acc_type.access_type);
2735 if acc_type.access_type= 'OPPORTUNITY' then
2736 i:=i+1;
2737 SELECT JTF_TERR_QTYPE_USGS_S.nextval
2738 into l_terr_qtype_usg_id
2739 FROM DUAL;
2740
2741 l_terr_qualtypeusgs_tbl(i).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
2742 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
2743 l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2744 l_terr_qualtypeusgs_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
2745 l_terr_qualtypeusgs_tbl(i).CREATED_BY := terr_group.CREATED_BY;
2746 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2747 l_terr_qualtypeusgs_tbl(i).TERR_ID:= null;
2748 l_terr_qualtypeusgs_tbl(i).QUAL_TYPE_USG_ID:=-1003;
2749 l_terr_qualtypeusgs_tbl(i).ORG_ID:=terr_group.ORG_ID;
2750
2751 SELECT JTF_TERR_QUAL_S.nextval
2752 into l_terr_qual_id
2753 FROM DUAL;
2754 /* opp expected purchase */
2755
2756 l_terr_qual_tbl(i).TERR_QUAL_ID :=l_terr_qual_id;
2757 l_terr_qual_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
2758 l_terr_qual_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2759 l_terr_qual_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
2760 l_terr_qual_tbl(i).CREATED_BY := terr_group.CREATED_BY;
2761 l_terr_qual_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2762 l_terr_qual_tbl(i).TERR_ID:=null;
2763 l_terr_qual_tbl(i).QUAL_USG_ID :=-1023;
2764 l_terr_qual_tbl(i).QUALIFIER_MODE:=NULL;
2765 l_terr_qual_tbl(i).OVERLAP_ALLOWED_FLAG:='N';
2766 l_terr_qual_tbl(i).USE_TO_NAME_FLAG:=NULL;
2767 l_terr_qual_tbl(i).GENERATE_FLAG:=NULL;
2768 l_terr_qual_tbl(i).ORG_ID:=terr_group.ORG_ID;
2769
2770 for qval in role_pi_interest(terr_group.terr_group_id,pit.role_code) loop
2771 k:=k+1;
2772
2773 l_terr_values_tbl(k).TERR_VALUE_ID:=null;
2774
2775 l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
2776 l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
2777 l_terr_values_tbl(k).CREATED_BY := terr_group.CREATED_BY;
2778 l_terr_values_tbl(k).CREATION_DATE:= terr_group.CREATION_DATE;
2779 l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
2780 l_terr_values_tbl(k).TERR_QUAL_ID :=l_terr_qual_id ;
2781 l_terr_values_tbl(k).INCLUDE_FLAG :=NULL;
2782 l_terr_values_tbl(k).COMPARISON_OPERATOR :='=';
2783 l_terr_values_tbl(k).LOW_VALUE_CHAR:= null;
2784 l_terr_values_tbl(k).HIGH_VALUE_CHAR:=null;
2785 l_terr_values_tbl(k).LOW_VALUE_NUMBER :=null;
2786 l_terr_values_tbl(k).HIGH_VALUE_NUMBER :=null;
2787 l_terr_values_tbl(k).VALUE_SET :=NULL;
2788 l_terr_values_tbl(k).INTEREST_TYPE_ID :=qval.interest_type_id;
2789 l_terr_values_tbl(k).PRIMARY_INTEREST_CODE_ID:=null;
2790 l_terr_values_tbl(k).SECONDARY_INTEREST_CODE_ID:=null;
2791 l_terr_values_tbl(k).CURRENCY_CODE :=null;
2792 l_terr_values_tbl(k).ORG_ID :=terr_group.ORG_ID;
2793 l_terr_values_tbl(k).ID_USED_FLAG :='N';
2794 l_terr_values_tbl(k).LOW_VALUE_CHAR_ID :=null;
2795
2796 l_terr_values_tbl(k).qualifier_tbl_index := i;
2797
2798 end loop;
2799
2800 elsif acc_type.access_type= 'LEAD' then
2801
2802 i:=i+1;
2803 SELECT JTF_TERR_QTYPE_USGS_S.nextval
2804 into l_terr_qtype_usg_id
2805 FROM DUAL;
2806
2807 l_terr_qualtypeusgs_tbl(i).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
2808 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
2809 l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2810 l_terr_qualtypeusgs_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
2811 l_terr_qualtypeusgs_tbl(i).CREATED_BY := terr_group.CREATED_BY;
2812 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2813 l_terr_qualtypeusgs_tbl(i).TERR_ID:= null;
2814 l_terr_qualtypeusgs_tbl(i).QUAL_TYPE_USG_ID:=-1002;
2815 l_terr_qualtypeusgs_tbl(i).ORG_ID:=terr_group.ORG_ID;
2816
2817 SELECT JTF_TERR_QUAL_S.nextval
2818 into l_terr_qual_id
2819 FROM DUAL;
2820
2821 /* lead expected purchase */
2822 l_terr_qual_tbl(i).TERR_QUAL_ID :=l_terr_qual_id;
2823 l_terr_qual_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
2824 l_terr_qual_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2825 l_terr_qual_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
2826 l_terr_qual_tbl(i).CREATED_BY := terr_group.CREATED_BY;
2827 l_terr_qual_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2828 l_terr_qual_tbl(i).TERR_ID:=null;
2829 l_terr_qual_tbl(i).QUAL_USG_ID :=-1018;
2830 l_terr_qual_tbl(i).QUALIFIER_MODE:=NULL;
2831 l_terr_qual_tbl(i).OVERLAP_ALLOWED_FLAG:='N';
2832 l_terr_qual_tbl(i).USE_TO_NAME_FLAG:=NULL;
2833 l_terr_qual_tbl(i).GENERATE_FLAG:=NULL;
2834 l_terr_qual_tbl(i).ORG_ID:=terr_group.ORG_ID;
2835
2836 for qval in role_pi_interest(terr_group.terr_group_id,pit.role_code) loop
2837
2838 k:=k+1;
2839
2840 l_terr_values_tbl(k).TERR_VALUE_ID:=null;
2841
2842 l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
2843 l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
2844 l_terr_values_tbl(k).CREATED_BY := terr_group.CREATED_BY;
2845 l_terr_values_tbl(k).CREATION_DATE:= terr_group.CREATION_DATE;
2846 l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
2847 l_terr_values_tbl(k).TERR_QUAL_ID :=l_terr_qual_id ;
2848 l_terr_values_tbl(k).INCLUDE_FLAG :=NULL;
2849 l_terr_values_tbl(k).COMPARISON_OPERATOR :='=';
2850 l_terr_values_tbl(k).LOW_VALUE_CHAR:= null;
2851 l_terr_values_tbl(k).HIGH_VALUE_CHAR:=null;
2852 l_terr_values_tbl(k).LOW_VALUE_NUMBER :=null;
2853 l_terr_values_tbl(k).HIGH_VALUE_NUMBER :=null;
2854 l_terr_values_tbl(k).VALUE_SET :=NULL;
2855 l_terr_values_tbl(k).INTEREST_TYPE_ID := qval.interest_type_id;
2856 l_terr_values_tbl(k).PRIMARY_INTEREST_CODE_ID:=null;
2857 l_terr_values_tbl(k).SECONDARY_INTEREST_CODE_ID:=null;
2858 l_terr_values_tbl(k).CURRENCY_CODE :=null;
2859 l_terr_values_tbl(k).ORG_ID :=terr_group.ORG_ID;
2860 l_terr_values_tbl(k).ID_USED_FLAG :='N';
2861 l_terr_values_tbl(k).LOW_VALUE_CHAR_ID :=null;
2862
2863
2864 l_terr_values_tbl(k).qualifier_tbl_index := i;
2865
2866 end loop;
2867
2868 else
2869 write_log(2,' OVERLAY and NON_OVERLAY role exist for '||terr_group.terr_group_id);
2870 --l_terr_qualtypeusgs_tbl(1).ORG_ID:=terr_group.ORG_ID;
2871 end if;
2872
2873 end loop;
2874
2875 l_init_msg_list :=FND_API.G_TRUE;
2876
2877 -- 07/08/03: JDOCHERT: bug#3023653
2878 --mo_global.set_org_context(terr_group.ORG_ID,null);
2879 --
2880
2881 jtf_territory_pvt.create_territory (
2882 p_api_version_number => l_api_version_number,
2883 p_init_msg_list => l_init_msg_list,
2884 p_commit => l_commit,
2885 p_validation_level => fnd_api.g_valid_level_NONE,
2886 x_return_status => x_return_status,
2887 x_msg_count => x_msg_count,
2888 x_msg_data => x_msg_data,
2889 p_terr_all_rec => l_terr_all_rec,
2890 p_terr_usgs_tbl => l_terr_usgs_tbl,
2891 p_terr_qualtypeusgs_tbl => l_terr_qualtypeusgs_tbl,
2892 p_terr_qual_tbl => l_terr_qual_tbl,
2893 p_terr_values_tbl => l_terr_values_tbl,
2894 x_terr_id => x_terr_id,
2895 x_terr_usgs_out_tbl => x_terr_usgs_out_tbl,
2896 x_terr_qualtypeusgs_out_tbl => x_terr_qualtypeusgs_out_tbl,
2897 x_terr_qual_out_tbl => x_terr_qual_out_tbl,
2898 x_terr_values_out_tbl => x_terr_values_out_tbl
2899 );
2900
2901 if (x_return_status = 'S') then
2902
2903 -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID
2904 -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
2905 UPDATE JTF_TERR_ALL
2906 set TERR_GROUP_FLAG = 'Y'
2907 , TERR_GROUP_ID = terr_group.TERR_GROUP_ID
2908 , NAMED_ACCOUNT_FLAG = 'Y'
2909 , TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id
2910 where terr_id = x_terr_id;
2911
2912
2913 write_log(2,' OVERLAY PI Territory Created = '||l_terr_all_rec.NAME);
2914
2915 else
2916 x_msg_data := fnd_msg_pub.get(1, fnd_api.g_false);
2917 write_log(2,x_msg_data);
2918 write_log(2, 'Failed in OVERLAY PI Territory Creation for TERR_GROUP_ACCOUNT_ID#'||
2919 overlayterr.terr_group_account_id);
2920
2921 end if;
2922
2923
2924 --dbms_output.put_line('pit.role '||pit.role_code);
2925 i:=0;
2926
2927 /* JRADHAKR changed the parameter from l_terr_group_id to l_terr_group_acct_id */
2928 for rsc in resource_grp(overlayterr.terr_group_account_id,pit.role_code) loop
2929
2930 i:=i+1;
2931
2932 SELECT JTF_TERR_RSC_S.nextval
2933 into l_terr_rsc_id
2934 FROM DUAL;
2935
2936 l_TerrRsc_Tbl(i).terr_id := x_terr_id;
2937 l_TerrRsc_Tbl(i).TERR_RSC_ID :=l_terr_rsc_id;
2938 l_TerrRsc_Tbl(i).LAST_UPDATE_DATE:=terr_group.LAST_UPDATE_DATE;
2939 l_TerrRsc_Tbl(i).LAST_UPDATED_BY:=terr_group.LAST_UPDATED_BY;
2940 l_TerrRsc_Tbl(i).CREATION_DATE:=terr_group.CREATION_DATE;
2941 l_TerrRsc_Tbl(i).CREATED_BY:=terr_group.CREATED_BY;
2942 l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
2943 --l_TerrRsc_Tbl(i).TERR_ID:=terr_group.TERRITORY_ID;
2944 l_TerrRsc_Tbl(i).RESOURCE_ID:=rsc.resource_id;
2945 l_TerrRsc_Tbl(i).RESOURCE_TYPE:=rsc.rsc_resource_type;
2946 l_TerrRsc_Tbl(i).ROLE:=pit.role_code;
2947 --l_TerrRsc_Tbl(i).ROLE:=l_role;
2948 l_TerrRsc_Tbl(i).PRIMARY_CONTACT_FLAG:='N';
2949 l_TerrRsc_Tbl(i).START_DATE_ACTIVE:=terr_group.active_from_date ;
2950 l_TerrRsc_Tbl(i).END_DATE_ACTIVE:=terr_group.active_to_date ;
2951 l_TerrRsc_Tbl(i).ORG_ID:=terr_group.ORG_ID;
2952 l_TerrRsc_Tbl(i).FULL_ACCESS_FLAG:='Y';
2953 l_TerrRsc_Tbl(i).GROUP_ID:=rsc.rsc_group_id;
2954
2955
2956 a := 0;
2957
2958 for rsc_acc in role_access(terr_group.terr_group_id,pit.role_code) loop
2959
2960 /**
2961 a := a+1; -- JDOCHERT: 05/28/03: put a := a+1; inside 2*IF statements
2962 -- that follow: ACCOUNT access should not be given for
2963 -- Product Overlay territories
2964 --
2965 if rsc_acc.access_type= 'ACCOUNT' then
2966
2967 SELECT JTF_TERR_RSC_ACCESS_S.nextval
2968 into l_terr_rsc_access_id
2969 FROM DUAL;
2970 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
2971 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
2972 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2973 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
2974 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
2975 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2976 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
2977 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'ACCOUNT';
2978 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
2979 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:= i;
2980
2981 els
2982 **/
2983
2984 if rsc_acc.access_type= 'OPPORTUNITY' then
2985
2986 a := a+1;
2987
2988 SELECT JTF_TERR_RSC_ACCESS_S.nextval
2989 into l_terr_rsc_access_id
2990 FROM DUAL;
2991
2992 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
2993 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
2994 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
2995 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
2996 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
2997 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
2998 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
2999 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'OPPOR';
3000 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
3001 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:= i;
3002
3003 elsif rsc_acc.access_type= 'LEAD' then
3004
3005 a := a+1;
3006
3007 SELECT JTF_TERR_RSC_ACCESS_S.nextval
3008 into l_terr_rsc_access_id
3009 FROM DUAL;
3010
3011 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
3012 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
3013 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3014 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
3015 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
3016 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
3017 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
3018 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'LEAD';
3019 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
3020 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:= i;
3021 end if;
3022 end loop; /* rsc_acc in role_access */
3023
3024 l_init_msg_list :=FND_API.G_TRUE;
3025
3026 -- 07/08/03: JDOCHERT: bug#3023653
3027 jtf_territory_resource_pvt.create_terrresource (
3028 p_api_version_number => l_Api_Version_Number,
3029 p_init_msg_list => l_Init_Msg_List,
3030 p_commit => l_Commit,
3031 p_validation_level => fnd_api.g_valid_level_NONE,
3032 x_return_status => x_Return_Status,
3033 x_msg_count => x_Msg_Count,
3034 x_msg_data => x_msg_data,
3035 p_terrrsc_tbl => l_TerrRsc_tbl,
3036 p_terrrsc_access_tbl => l_terrRsc_access_tbl,
3037 x_terrrsc_out_tbl => x_TerrRsc_Out_Tbl,
3038 x_terrrsc_access_out_tbl => x_TerrRsc_Access_Out_Tbl
3039 );
3040
3041 if x_Return_Status='S' then
3042 write_log(2,'Resource created for Product Interest OVERLAY Territory '||l_terr_all_rec.NAME);
3043 else
3044 write_log(2,'Failed in Resource creation for Product Interest OVERLAY Territory# '||
3045 x_terr_id);
3046 end if;
3047
3048 end loop; /* rsc in resource_grp */
3049
3050 end loop;
3051
3052 else
3053 x_msg_data := fnd_msg_pub.get(1, fnd_api.g_false);
3054 write_log(2,x_msg_data);
3055 write_log(2,'Failed in OVERLAY Territory Creation for Territory Group: ' ||
3056 terr_group.terr_group_id || ' : ' ||
3057 terr_group.terr_group_name );
3058 end if; /* if (x_return_status = 'S' */
3059 end loop; /* overlayterr in get_OVLY_party_duns */
3060 END IF; /* ( terr_group.matching_rule_code IN ('2','3') THEN */
3061 /***************************************************************/
3062 /* (8) END: CREATE OVERLAY TERRITORIES FOR TERRITORY GROUP */
3063 /* USING DUNS# QUALIFIER */
3064 /***************************************************************/
3065
3066
3067 /***************************************************************/
3068 /* (9) START: CREATE OVERLAY TERRITORIES FOR TERRITORY GROUP */
3069 /* USING CUSTOMER NAME RANGE AND POSTAL CODE QUALIFIERS */
3070 /***************************************************************/
3071 IF ( terr_group.matching_rule_code IN ('1', '2') ) THEN
3072
3073 for overlayterr in get_OVLY_party_name(terr_group.terr_group_id) loop
3074
3075 l_terr_usgs_tbl:=l_terr_usgs_empty_tbl;
3076 l_terr_qualtypeusgs_tbl:=l_terr_qualtypeusgs_empty_tbl;
3077 l_terr_qual_tbl:=l_terr_qual_empty_tbl;
3078 l_terr_values_tbl:=l_terr_values_empty_tbl;
3079 l_TerrRsc_Tbl := l_TerrRsc_empty_Tbl;
3080 l_TerrRsc_Access_Tbl := l_TerrRsc_Access_empty_Tbl;
3081
3082 l_terr_all_rec.TERR_ID := null;
3083 l_terr_all_rec.LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
3084 l_terr_all_rec.LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3085 l_terr_all_rec.CREATION_DATE:= terr_group.CREATION_DATE;
3086 l_terr_all_rec.CREATED_BY := terr_group.CREATED_BY ;
3087 l_terr_all_rec.LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
3088
3089 l_terr_all_rec.APPLICATION_SHORT_NAME:= G_APP_SHORT_NAME;
3090 l_terr_all_rec.NAME:= overlayterr.name || ' (OVERLAY)';
3091 l_terr_all_rec.start_date_active := terr_group.active_from_date ;
3092 l_terr_all_rec.end_date_active := terr_group.active_to_date;
3093 l_terr_all_rec.PARENT_TERRITORY_ID:= l_overlay_top;
3094 l_terr_all_rec.RANK := terr_group.RANK + 20;
3095 l_terr_all_rec.TEMPLATE_TERRITORY_ID:= NULL;
3096 l_terr_all_rec.TEMPLATE_FLAG := 'N';
3097 l_terr_all_rec.ESCALATION_TERRITORY_ID := NULL;
3098 l_terr_all_rec.ESCALATION_TERRITORY_FLAG := 'N';
3099 l_terr_all_rec.OVERLAP_ALLOWED_FLAG := NULL;
3100 l_terr_all_rec.DESCRIPTION:= overlayterr.name || ' (OVERLAY)';
3101 l_terr_all_rec.UPDATE_FLAG :='N';
3102 l_terr_all_rec.AUTO_ASSIGN_RESOURCES_FLAG :=NULL;
3103
3104 l_terr_all_rec.ORG_ID :=terr_group.ORG_ID ;
3105 l_terr_all_rec.NUM_WINNERS :=null ;
3106
3107
3108 SELECT JTF_TERR_USGS_S.nextval
3109 into l_terr_usg_id
3110 FROM DUAL;
3111
3112 l_terr_usgs_tbl(1).TERR_USG_ID := l_terr_usg_id;
3113 l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
3114 l_terr_usgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3115 l_terr_usgs_tbl(1).CREATION_DATE:= terr_group.CREATION_DATE;
3116 l_terr_usgs_tbl(1).CREATED_BY := terr_group.CREATED_BY;
3117 l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
3118 l_terr_usgs_tbl(1).TERR_ID:= null;
3119 l_terr_usgs_tbl(1).SOURCE_ID := -1001;
3120 l_terr_usgs_tbl(1).ORG_ID:= terr_group.ORG_ID;
3121
3122 SELECT JTF_TERR_QTYPE_USGS_S.nextval
3123 into l_terr_qtype_usg_id
3124 FROM DUAL;
3125
3126 l_terr_qualtypeusgs_tbl(1).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
3127 l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
3128 l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3129 l_terr_qualtypeusgs_tbl(1).CREATION_DATE:= terr_group.CREATION_DATE;
3130 l_terr_qualtypeusgs_tbl(1).CREATED_BY := terr_group.CREATED_BY;
3131 l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
3132 l_terr_qualtypeusgs_tbl(1).TERR_ID:= null;
3133 l_terr_qualtypeusgs_tbl(1).QUAL_TYPE_USG_ID:=-1002;
3134 l_terr_qualtypeusgs_tbl(1).ORG_ID:=terr_group.ORG_ID;
3135
3136 SELECT JTF_TERR_QTYPE_USGS_S.nextval
3137 into l_terr_qtype_usg_id
3138 FROM DUAL;
3139
3140 l_terr_qualtypeusgs_tbl(2).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
3141 l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
3142 l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3143 l_terr_qualtypeusgs_tbl(2).CREATION_DATE:= terr_group.CREATION_DATE;
3144 l_terr_qualtypeusgs_tbl(2).CREATED_BY := terr_group.CREATED_BY;
3145 l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
3146 l_terr_qualtypeusgs_tbl(2).TERR_ID:= null;
3147 l_terr_qualtypeusgs_tbl(2).QUAL_TYPE_USG_ID:=-1003;
3148 l_terr_qualtypeusgs_tbl(2).ORG_ID:=terr_group.ORG_ID;
3149
3150 SELECT JTF_TERR_QUAL_S.nextval
3151 into l_terr_qual_id
3152 FROM DUAL;
3153
3154 j:=0;
3155 K:=0;
3156 l_prev_qual_usg_id:=1;
3157
3158 for qval in match_rule1(overlayterr.named_account_id)
3159 loop
3160
3161 if l_prev_qual_usg_id <> qval.qual_usg_id then
3162
3163 j:=j+1;
3164 SELECT JTF_TERR_QUAL_S.nextval
3165 into l_terr_qual_id
3166 FROM DUAL;
3167
3168 l_terr_qual_tbl(j).TERR_QUAL_ID :=l_terr_qual_id;
3169 l_terr_qual_tbl(j).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
3170 l_terr_qual_tbl(j).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3171 l_terr_qual_tbl(j).CREATION_DATE:= terr_group.CREATION_DATE;
3172 l_terr_qual_tbl(j).CREATED_BY := terr_group.CREATED_BY;
3173 l_terr_qual_tbl(j).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
3174 l_terr_qual_tbl(j).TERR_ID:=null;
3175 l_terr_qual_tbl(j).QUAL_USG_ID :=qval.qual_usg_id;
3176 l_terr_qual_tbl(j).QUALIFIER_MODE:=NULL;
3177 l_terr_qual_tbl(j).OVERLAP_ALLOWED_FLAG:='N';
3178 l_terr_qual_tbl(j).USE_TO_NAME_FLAG:=NULL;
3179 l_terr_qual_tbl(j).GENERATE_FLAG:=NULL;
3180 l_terr_qual_tbl(j).ORG_ID:=terr_group.ORG_ID;
3181 l_prev_qual_usg_id:= qval.qual_usg_id;
3182 end if;
3183
3184 k:=k+1;
3185
3186 l_terr_values_tbl(k).TERR_VALUE_ID:=null;
3187
3188 l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
3189 l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
3190 l_terr_values_tbl(k).CREATED_BY := terr_group.CREATED_BY;
3191 l_terr_values_tbl(k).CREATION_DATE:= terr_group.CREATION_DATE;
3192 l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
3193 l_terr_values_tbl(k).TERR_QUAL_ID :=l_terr_qual_id ;
3194 l_terr_values_tbl(k).INCLUDE_FLAG :=NULL;
3195 l_terr_values_tbl(k).COMPARISON_OPERATOR := qval.COMPARISON_OPERATOR;
3196 l_terr_values_tbl(k).LOW_VALUE_CHAR:= qval.value1_char;
3197 l_terr_values_tbl(k).HIGH_VALUE_CHAR:= qval.value2_char;
3198 l_terr_values_tbl(k).LOW_VALUE_NUMBER :=null;
3199 l_terr_values_tbl(k).HIGH_VALUE_NUMBER :=null;
3200 l_terr_values_tbl(k).VALUE_SET :=NULL;
3201 l_terr_values_tbl(k).INTEREST_TYPE_ID :=null;
3202 l_terr_values_tbl(k).PRIMARY_INTEREST_CODE_ID:=null;
3203 l_terr_values_tbl(k).SECONDARY_INTEREST_CODE_ID:=null;
3204 l_terr_values_tbl(k).CURRENCY_CODE :=null;
3205 l_terr_values_tbl(k).ORG_ID :=terr_group.ORG_ID;
3206 l_terr_values_tbl(k).ID_USED_FLAG :='N';
3207 l_terr_values_tbl(k).LOW_VALUE_CHAR_ID :=null;
3208
3209
3210 l_terr_values_tbl(k).qualifier_tbl_index := j;
3211
3212 end loop;
3213
3214 l_init_msg_list :=FND_API.G_TRUE;
3215
3216 -- 07/08/03: JDOCHERT: bug#3023653
3217 --mo_global.set_org_context(terr_group.ORG_ID,null);
3218 --
3219
3220 jtf_territory_pvt.create_territory (
3221 p_api_version_number => l_api_version_number,
3222 p_init_msg_list => l_init_msg_list,
3223 p_commit => l_commit,
3224 p_validation_level => fnd_api.g_valid_level_NONE,
3225 x_return_status => x_return_status,
3226 x_msg_count => x_msg_count,
3227 x_msg_data => x_msg_data,
3228 p_terr_all_rec => l_terr_all_rec,
3229 p_terr_usgs_tbl => l_terr_usgs_tbl,
3230 p_terr_qualtypeusgs_tbl => l_terr_qualtypeusgs_tbl,
3231 p_terr_qual_tbl => l_terr_qual_tbl,
3232 p_terr_values_tbl => l_terr_values_tbl,
3233 x_terr_id => x_terr_id,
3234 x_terr_usgs_out_tbl => x_terr_usgs_out_tbl,
3235 x_terr_qualtypeusgs_out_tbl => x_terr_qualtypeusgs_out_tbl,
3236 x_terr_qual_out_tbl => x_terr_qual_out_tbl,
3237 x_terr_values_out_tbl => x_terr_values_out_tbl
3238 );
3239
3240 write_log(2,' OVERLAY Territory Created,territory_id# '||x_terr_id);
3241
3242
3243 if x_return_status = 'S' then
3244
3245 -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID
3246 -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
3247 UPDATE JTF_TERR_ALL
3248 set TERR_GROUP_FLAG = 'Y'
3249 , TERR_GROUP_ID = terr_group.TERR_GROUP_ID
3250 , NAMED_ACCOUNT_FLAG = 'Y'
3251 , TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id
3252 where terr_id = x_terr_id;
3253
3254 l_overlay:=x_terr_id;
3255
3256 for pit in role_pi( terr_group.terr_group_id
3257 , overlayterr.terr_group_account_id) LOOP
3258
3259 l_terr_usgs_tbl:=l_terr_usgs_empty_tbl;
3260 l_terr_qualtypeusgs_tbl:=l_terr_qualtypeusgs_empty_tbl;
3261 l_terr_qual_tbl:=l_terr_qual_empty_tbl;
3262 l_terr_values_tbl:=l_terr_values_empty_tbl;
3263 l_TerrRsc_Tbl := l_TerrRsc_empty_Tbl;
3264 l_TerrRsc_Access_Tbl := l_TerrRsc_Access_empty_Tbl;
3265
3266 l_role_counter := l_role_counter + 1;
3267
3268 l_terr_all_rec.TERR_ID := overlayterr.terr_group_account_id * -40 * l_role_counter;
3269 l_terr_all_rec.LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
3270 l_terr_all_rec.LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3271 l_terr_all_rec.CREATION_DATE:= terr_group.CREATION_DATE;
3272 l_terr_all_rec.CREATED_BY := terr_group.CREATED_BY ;
3273 l_terr_all_rec.LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
3274
3275 l_terr_all_rec.APPLICATION_SHORT_NAME:= G_APP_SHORT_NAME;
3276
3277 l_terr_all_rec.NAME:= overlayterr.name || ' ' || pit.role_name || ' (OVERLAY)';
3278
3279 l_terr_all_rec.start_date_active := terr_group.active_from_date ;
3280 l_terr_all_rec.end_date_active := terr_group.active_to_date;
3281 l_terr_all_rec.PARENT_TERRITORY_ID:= l_overlay;
3282 l_terr_all_rec.RANK := terr_group.RANK+10;
3283 l_terr_all_rec.TEMPLATE_TERRITORY_ID:= NULL;
3284 l_terr_all_rec.TEMPLATE_FLAG := 'N';
3285 l_terr_all_rec.ESCALATION_TERRITORY_ID := NULL;
3286 l_terr_all_rec.ESCALATION_TERRITORY_FLAG := 'N';
3287 l_terr_all_rec.OVERLAP_ALLOWED_FLAG := NULL;
3288 l_terr_all_rec.DESCRIPTION:= pit.role_code||' '||overlayterr.name||' (OVERLAY)';
3289 l_terr_all_rec.UPDATE_FLAG :='N';
3290 l_terr_all_rec.AUTO_ASSIGN_RESOURCES_FLAG :=NULL;
3291
3292 l_terr_all_rec.ORG_ID :=terr_group.ORG_ID ;
3293 l_terr_all_rec.NUM_WINNERS :=null ;
3294
3295 SELECT JTF_TERR_USGS_S.nextval
3296 into l_terr_usg_id
3297 FROM DUAL;
3298
3299 l_terr_usgs_tbl(1).TERR_USG_ID := l_terr_usg_id;
3300 l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
3301 l_terr_usgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3302 l_terr_usgs_tbl(1).CREATION_DATE:= terr_group.CREATION_DATE;
3303 l_terr_usgs_tbl(1).CREATED_BY := terr_group.CREATED_BY;
3304 l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
3305 l_terr_usgs_tbl(1).TERR_ID:= null;
3306 l_terr_usgs_tbl(1).SOURCE_ID:=-1001;
3307 l_terr_usgs_tbl(1).ORG_ID:= terr_group.ORG_ID;
3308
3309 i := 0;
3310 K:= 0;
3311 for acc_type in role_access(terr_group.terr_group_id,pit.role_code) loop
3312 --i:=i+1;
3313 --dbms_output.put_line('acc type '||acc_type.access_type);
3314 if acc_type.access_type= 'OPPORTUNITY' then
3315 i:=i+1;
3316 SELECT JTF_TERR_QTYPE_USGS_S.nextval
3317 into l_terr_qtype_usg_id
3318 FROM DUAL;
3319
3320 l_terr_qualtypeusgs_tbl(i).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
3321 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
3322 l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3323 l_terr_qualtypeusgs_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
3324 l_terr_qualtypeusgs_tbl(i).CREATED_BY := terr_group.CREATED_BY;
3325 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
3326 l_terr_qualtypeusgs_tbl(i).TERR_ID:= null;
3327 l_terr_qualtypeusgs_tbl(i).QUAL_TYPE_USG_ID:=-1003;
3328 l_terr_qualtypeusgs_tbl(i).ORG_ID:=terr_group.ORG_ID;
3329
3330 SELECT JTF_TERR_QUAL_S.nextval
3331 into l_terr_qual_id
3332 FROM DUAL;
3333 /* opp expected purchase */
3334
3335 l_terr_qual_tbl(i).TERR_QUAL_ID :=l_terr_qual_id;
3336 l_terr_qual_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
3337 l_terr_qual_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3338 l_terr_qual_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
3339 l_terr_qual_tbl(i).CREATED_BY := terr_group.CREATED_BY;
3340 l_terr_qual_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
3341 l_terr_qual_tbl(i).TERR_ID:=null;
3342 l_terr_qual_tbl(i).QUAL_USG_ID :=-1023;
3343 l_terr_qual_tbl(i).QUALIFIER_MODE:=NULL;
3344 l_terr_qual_tbl(i).OVERLAP_ALLOWED_FLAG:='N';
3345 l_terr_qual_tbl(i).USE_TO_NAME_FLAG:=NULL;
3346 l_terr_qual_tbl(i).GENERATE_FLAG:=NULL;
3347 l_terr_qual_tbl(i).ORG_ID:=terr_group.ORG_ID;
3348
3349 for qval in role_pi_interest(terr_group.terr_group_id,pit.role_code) loop
3350 k:=k+1;
3351
3352 l_terr_values_tbl(k).TERR_VALUE_ID:=null;
3353
3354 l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
3355 l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
3356 l_terr_values_tbl(k).CREATED_BY := terr_group.CREATED_BY;
3357 l_terr_values_tbl(k).CREATION_DATE:= terr_group.CREATION_DATE;
3358 l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
3359 l_terr_values_tbl(k).TERR_QUAL_ID :=l_terr_qual_id ;
3360 l_terr_values_tbl(k).INCLUDE_FLAG :=NULL;
3361 l_terr_values_tbl(k).COMPARISON_OPERATOR :='=';
3362 l_terr_values_tbl(k).LOW_VALUE_CHAR:= null;
3363 l_terr_values_tbl(k).HIGH_VALUE_CHAR:=null;
3364 l_terr_values_tbl(k).LOW_VALUE_NUMBER :=null;
3365 l_terr_values_tbl(k).HIGH_VALUE_NUMBER :=null;
3366 l_terr_values_tbl(k).VALUE_SET :=NULL;
3367 l_terr_values_tbl(k).INTEREST_TYPE_ID :=qval.interest_type_id;
3368 l_terr_values_tbl(k).PRIMARY_INTEREST_CODE_ID:=null;
3369 l_terr_values_tbl(k).SECONDARY_INTEREST_CODE_ID:=null;
3370 l_terr_values_tbl(k).CURRENCY_CODE :=null;
3371 l_terr_values_tbl(k).ORG_ID :=terr_group.ORG_ID;
3372 l_terr_values_tbl(k).ID_USED_FLAG :='N';
3373 l_terr_values_tbl(k).LOW_VALUE_CHAR_ID :=null;
3374
3375 l_terr_values_tbl(k).qualifier_tbl_index := i;
3376
3377 end loop;
3378
3379 elsif acc_type.access_type= 'LEAD' then
3380
3381 i:=i+1;
3382 SELECT JTF_TERR_QTYPE_USGS_S.nextval
3383 into l_terr_qtype_usg_id
3384 FROM DUAL;
3385
3386 l_terr_qualtypeusgs_tbl(i).TERR_QUAL_TYPE_USG_ID:= l_terr_qtype_usg_id;
3387 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
3388 l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3389 l_terr_qualtypeusgs_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
3390 l_terr_qualtypeusgs_tbl(i).CREATED_BY := terr_group.CREATED_BY;
3391 l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
3392 l_terr_qualtypeusgs_tbl(i).TERR_ID:= null;
3393 l_terr_qualtypeusgs_tbl(i).QUAL_TYPE_USG_ID:=-1002;
3394 l_terr_qualtypeusgs_tbl(i).ORG_ID:=terr_group.ORG_ID;
3395
3396 SELECT JTF_TERR_QUAL_S.nextval
3397 into l_terr_qual_id
3398 FROM DUAL;
3399
3400 /* lead expected purchase */
3401 l_terr_qual_tbl(i).TERR_QUAL_ID :=l_terr_qual_id;
3402 l_terr_qual_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
3403 l_terr_qual_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3404 l_terr_qual_tbl(i).CREATION_DATE:= terr_group.CREATION_DATE;
3405 l_terr_qual_tbl(i).CREATED_BY := terr_group.CREATED_BY;
3406 l_terr_qual_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
3407 l_terr_qual_tbl(i).TERR_ID:=null;
3408 l_terr_qual_tbl(i).QUAL_USG_ID :=-1018;
3409 l_terr_qual_tbl(i).QUALIFIER_MODE:=NULL;
3410 l_terr_qual_tbl(i).OVERLAP_ALLOWED_FLAG:='N';
3411 l_terr_qual_tbl(i).USE_TO_NAME_FLAG:=NULL;
3412 l_terr_qual_tbl(i).GENERATE_FLAG:=NULL;
3413 l_terr_qual_tbl(i).ORG_ID:=terr_group.ORG_ID;
3414
3415 for qval in role_pi_interest(terr_group.terr_group_id,pit.role_code) loop
3416
3417 k:=k+1;
3418
3419 l_terr_values_tbl(k).TERR_VALUE_ID:=null;
3420
3421 l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
3422 l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
3423 l_terr_values_tbl(k).CREATED_BY := terr_group.CREATED_BY;
3424 l_terr_values_tbl(k).CREATION_DATE:= terr_group.CREATION_DATE;
3425 l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
3426 l_terr_values_tbl(k).TERR_QUAL_ID :=l_terr_qual_id ;
3427 l_terr_values_tbl(k).INCLUDE_FLAG :=NULL;
3428 l_terr_values_tbl(k).COMPARISON_OPERATOR :='=';
3429 l_terr_values_tbl(k).LOW_VALUE_CHAR:= null;
3430 l_terr_values_tbl(k).HIGH_VALUE_CHAR:=null;
3431 l_terr_values_tbl(k).LOW_VALUE_NUMBER :=null;
3432 l_terr_values_tbl(k).HIGH_VALUE_NUMBER :=null;
3433 l_terr_values_tbl(k).VALUE_SET :=NULL;
3434 l_terr_values_tbl(k).INTEREST_TYPE_ID := qval.interest_type_id;
3435 l_terr_values_tbl(k).PRIMARY_INTEREST_CODE_ID:=null;
3436 l_terr_values_tbl(k).SECONDARY_INTEREST_CODE_ID:=null;
3437 l_terr_values_tbl(k).CURRENCY_CODE :=null;
3438 l_terr_values_tbl(k).ORG_ID :=terr_group.ORG_ID;
3439 l_terr_values_tbl(k).ID_USED_FLAG :='N';
3440 l_terr_values_tbl(k).LOW_VALUE_CHAR_ID :=null;
3441
3442
3443 l_terr_values_tbl(k).qualifier_tbl_index := i;
3444
3445 end loop;
3446
3447 else
3448 write_log(2,' OVERLAY and NON_OVERLAY role exist for '||terr_group.terr_group_id);
3449 --l_terr_qualtypeusgs_tbl(1).ORG_ID:=terr_group.ORG_ID;
3450 end if;
3451
3452 end loop;
3453
3454 l_init_msg_list :=FND_API.G_TRUE;
3455
3456 -- 07/08/03: JDOCHERT: bug#3023653
3457 --mo_global.set_org_context(terr_group.ORG_ID,null);
3458 --
3459
3460 --mo_global.set_org_context(204,null);
3461 --AS_UTILITY_PVT.file_debug(' winners # '||terr_group.NUM_WINNERS);
3462 --AS_UTILITY_PVT.file_debug(' migration of territory_group # '||terr_group.TERRITORY_GROUP_ID);
3463 jtf_territory_pvt.create_territory (
3464 p_api_version_number => l_api_version_number,
3465 p_init_msg_list => l_init_msg_list,
3466 p_commit => l_commit,
3467 p_validation_level => fnd_api.g_valid_level_NONE,
3468 x_return_status => x_return_status,
3469 x_msg_count => x_msg_count,
3470 x_msg_data => x_msg_data,
3471 p_terr_all_rec => l_terr_all_rec,
3472 p_terr_usgs_tbl => l_terr_usgs_tbl,
3473 p_terr_qualtypeusgs_tbl => l_terr_qualtypeusgs_tbl,
3474 p_terr_qual_tbl => l_terr_qual_tbl,
3475 p_terr_values_tbl => l_terr_values_tbl,
3476 x_terr_id => x_terr_id,
3477 x_terr_usgs_out_tbl => x_terr_usgs_out_tbl,
3478 x_terr_qualtypeusgs_out_tbl => x_terr_qualtypeusgs_out_tbl,
3479 x_terr_qual_out_tbl => x_terr_qual_out_tbl,
3480 x_terr_values_out_tbl => x_terr_values_out_tbl
3481 );
3482
3483 IF (x_return_status = 'S') THEN
3484
3485 -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID
3486 -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
3487 UPDATE JTF_TERR_ALL
3488 set TERR_GROUP_FLAG = 'Y'
3489 , TERR_GROUP_ID = terr_group.TERR_GROUP_ID
3490 , NAMED_ACCOUNT_FLAG = 'Y'
3491 , TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id
3492 where terr_id = x_terr_id;
3493
3494
3495 write_log(2,' OVERLAY CNR territory created:' || l_terr_all_rec.NAME);
3496
3497 else
3498 x_msg_data := fnd_msg_pub.get(1, fnd_api.g_false);
3499 write_log(2,x_msg_data);
3500 write_log(2,'Failed in OVERLAY CNR territory treation for ' ||
3501 'TERR_GROUP_ACCOUNT_ID = ' ||
3502 overlayterr.terr_group_account_id );
3503
3504 end if; /* IF (x_return_status = 'S') */
3505
3506
3507 --dbms_output.put_line('pit.role '||pit.role_code);
3508 i:=0;
3509
3510 /* JRADHAKR changed the parameter from l_terr_group_id to l_terr_group_acct_id */
3511 for rsc in resource_grp( overlayterr.terr_group_account_id
3512 , pit.role_code) loop
3513
3514 i:=i+1;
3515
3516 SELECT JTF_TERR_RSC_S.nextval
3517 into l_terr_rsc_id
3518 FROM DUAL;
3519
3520 l_TerrRsc_Tbl(i).terr_id := x_terr_id;
3521 l_TerrRsc_Tbl(i).TERR_RSC_ID :=l_terr_rsc_id;
3522 l_TerrRsc_Tbl(i).LAST_UPDATE_DATE:=terr_group.LAST_UPDATE_DATE;
3523 l_TerrRsc_Tbl(i).LAST_UPDATED_BY:=terr_group.LAST_UPDATED_BY;
3524 l_TerrRsc_Tbl(i).CREATION_DATE:=terr_group.CREATION_DATE;
3525 l_TerrRsc_Tbl(i).CREATED_BY:=terr_group.CREATED_BY;
3526 l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
3527 --l_TerrRsc_Tbl(i).TERR_ID:=terr_group.TERRITORY_ID;
3528 l_TerrRsc_Tbl(i).RESOURCE_ID:=rsc.resource_id;
3529 l_TerrRsc_Tbl(i).RESOURCE_TYPE:=rsc.rsc_resource_type;
3530 l_TerrRsc_Tbl(i).ROLE:=pit.role_code;
3531 --l_TerrRsc_Tbl(i).ROLE:=l_role;
3532 l_TerrRsc_Tbl(i).PRIMARY_CONTACT_FLAG:='N';
3533 l_TerrRsc_Tbl(i).START_DATE_ACTIVE:=terr_group.active_from_date ;
3534 l_TerrRsc_Tbl(i).END_DATE_ACTIVE:=terr_group.active_to_date ;
3535 l_TerrRsc_Tbl(i).ORG_ID:=terr_group.ORG_ID;
3536 l_TerrRsc_Tbl(i).FULL_ACCESS_FLAG:='Y';
3537 l_TerrRsc_Tbl(i).GROUP_ID:=rsc.rsc_group_id;
3538
3539
3540 a := 0;
3541
3542 for rsc_acc in role_access(terr_group.terr_group_id,pit.role_code) loop
3543
3544 /**
3545 a := a+1; -- JDOCHERT: 05/28/03: put a := a+1; inside 2*IF statements
3546 -- that follow: ACCOUNT access should not be given for
3547 -- Product Overlay territories
3548 --
3549 if rsc_acc.access_type= 'ACCOUNT' then
3550
3551 SELECT JTF_TERR_RSC_ACCESS_S.nextval
3552 into l_terr_rsc_access_id
3553 FROM DUAL;
3554 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
3555 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
3556 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3557 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
3558 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
3559 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
3560 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
3561 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'ACCOUNT';
3562 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
3563 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:= i;
3564
3565 els
3566 **/
3567
3568 if rsc_acc.access_type= 'OPPORTUNITY' then
3569
3570 a := a+1;
3571
3572 SELECT JTF_TERR_RSC_ACCESS_S.nextval
3573 into l_terr_rsc_access_id
3574 FROM DUAL;
3575
3576 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
3577 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
3578 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3579 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
3580 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
3581 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
3582 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
3583 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'OPPOR';
3584 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
3585 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:= i;
3586
3587 elsif rsc_acc.access_type= 'LEAD' then
3588
3589 a := a+1;
3590
3591 SELECT JTF_TERR_RSC_ACCESS_S.nextval
3592 into l_terr_rsc_access_id
3593 FROM DUAL;
3594
3595 l_TerrRsc_Access_Tbl(a).TERR_RSC_ACCESS_ID:= l_terr_rsc_access_id;
3596 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
3597 l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
3598 l_TerrRsc_Access_Tbl(a).CREATION_DATE:= terr_group.CREATION_DATE;
3599 l_TerrRsc_Access_Tbl(a).CREATED_BY := terr_group.CREATED_BY;
3600 l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
3601 l_TerrRsc_Access_Tbl(a).TERR_RSC_ID:= l_terr_rsc_id ;
3602 l_TerrRsc_Access_Tbl(a).ACCESS_TYPE:= 'LEAD';
3603 l_TerrRsc_Access_Tbl(a).ORG_ID:= terr_group.ORG_ID;
3604 l_TerrRsc_Access_Tbl(a).qualifier_tbl_index:= i;
3605 end if;
3606 end loop; /* rsc_acc in role_access */
3607
3608 l_init_msg_list :=FND_API.G_TRUE;
3609
3610 -- 07/08/03: JDOCHERT: bug#3023653
3611 jtf_territory_resource_pvt.create_terrresource (
3612 p_api_version_number => l_Api_Version_Number,
3613 p_init_msg_list => l_Init_Msg_List,
3614 p_commit => l_Commit,
3615 p_validation_level => fnd_api.g_valid_level_NONE,
3616 x_return_status => x_Return_Status,
3617 x_msg_count => x_Msg_Count,
3618 x_msg_data => x_msg_data,
3619 p_terrrsc_tbl => l_TerrRsc_tbl,
3620 p_terrrsc_access_tbl => l_terrRsc_access_tbl,
3621 x_terrrsc_out_tbl => x_TerrRsc_Out_Tbl,
3622 x_terrrsc_access_out_tbl => x_TerrRsc_Access_Out_Tbl
3623 );
3624
3625 if x_Return_Status='S' then
3626 write_log(2,'Resource created for Product Interest OVERLAY Territory# '||
3627 x_terr_id);
3628 else
3629 write_log(2,'Failed in Resource creation for Product Interest OVERLAY Territory# '||
3630 x_terr_id);
3631 end if;
3632
3633 end loop; /* rsc in resource_grp */
3634
3635 end loop;
3636
3637 else
3638 x_msg_data := fnd_msg_pub.get(1, fnd_api.g_false);
3639 write_log(2,x_msg_data);
3640 write_log(2,'Failed in OVERLAY Territory Creation for Territory Group: ' ||
3641 terr_group.terr_group_id || ' : ' ||
3642 terr_group.terr_group_name );
3643 end if;
3644
3645 end loop; /* for overlayterr in get_OVLY_party_name */
3646 END IF; /* IF ( terr_group.matching_rule_code IN ('1', '2') ) THEN */
3647 /***************************************************************/
3648 /* (9) END: CREATE OVERLAY TERRITORIES FOR TERRITORY GROUP */
3649 /* USING CUSTOMER NAME RANGE AND POSTAL CODE QUALIFIERS */
3650 /***************************************************************/
3651
3652
3653 end if; /* l_pi_count*/
3654
3655 write_log(2, '');
3656 write_log(2,'END: Territory Creation for Territory Group: ' ||
3657 terr_group.terr_group_id || ' : ' ||
3658 terr_group.terr_group_name );
3659 write_log(2, '');
3660 write_log(2, '----------------------------------------------------------');
3661
3662 END LOOP;
3663 /****************************************************
3664 ** (2) END: CREATE NAMED ACCOUNT TERRITORY CREATION
3665 ** FOR EACH TERRITORY GROUP
3666 *****************************************************/
3667
3668 /* JDOCHERT: 07/09/03:
3669 ** START: Disable triggers in
3670 ** TOTAL mode */
3671 IF (p_mode = 'TOTAL') THEN
3672 alter_triggers(p_status => 'ENABLE');
3673 END IF;
3674
3675 END generate_named_overlay_terr;
3676
3677
3678
3679 PROCEDURE generate_api (
3680 errbuf OUT NOCOPY VARCHAR2,
3681 retcode OUT NOCOPY VARCHAR2,
3682 p_source_id IN NUMBER,
3683 p_qualifier_type_id IN NUMBER,
3684 --p_mode IN VARCHAR2,
3685 p_record_limit IN NUMBER DEFAULT 100,
3686 p_debug_flag IN VARCHAR2,
3687 p_sql_trace IN VARCHAR2
3688 )
3689 AS
3690
3691 --
3692 -- 05/01/01 JDOCHERT: PART OF BUG#1714243 bug FIX
3693 --
3694 CURSOR csr_get_terr ( lp_source_id NUMBER
3695 , lp_qual_type_id NUMBER
3696 , lp_sysdate DATE ) IS
3697 SELECT 'TRUE'
3698 FROM jtf_terr_qtype_usgs_all jtqu
3699 , jtf_terr_usgs_all jtu
3700 , jtf_terr_all jt1
3701 , jtf_qual_type_usgs jqtu
3702 WHERE jtqu.terr_id = jt1.terr_id
3703 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
3704 AND jqtu.qual_type_id = lp_qual_type_id
3705 AND jtu.source_id = lp_source_id
3706 AND jtu.terr_id = jt1.terr_id
3707 AND NVL(jt1.end_date_active, lp_sysdate) >= lp_sysdate
3708 AND jt1.start_date_active <= lp_sysdate
3709 AND EXISTS (
3710 SELECT jtrs.terr_rsc_id
3711 FROM jtf_terr_rsc_all jtrs
3712 WHERE NVL(jtrs.end_date_active, lp_sysdate) >= lp_sysdate
3713 AND NVL(jtrs.start_date_active, lp_sysdate) <= lp_sysdate
3714 AND jtrs.terr_id = jt1.terr_id )
3715 AND NOT EXISTS (
3716 SELECT jt.terr_id
3717 FROM jtf_terr_all jt
3718 WHERE NVL(jt.end_date_active, lp_sysdate + 1) < lp_sysdate
3719 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
3720 START WITH jt.terr_id = jt1.terr_id)
3721 AND jqtu.qual_type_id <> -1001
3722 AND jtu.source_id <> -1003
3723 AND rownum < 2;
3724
3725 /* all the possible winning territories
3726 ** in the system
3727 */
3728 CURSOR csr_get_denorm_terr ( lp_source_id NUMBER
3729 , lp_qual_type_id NUMBER) IS
3730 SELECT jtdr.terr_id
3731 FROM jtf_terr_denorm_rules_all jtdr
3732 WHERE jtdr.terr_id = jtdr.related_terr_id
3733 AND jtdr.source_id = lp_source_id
3734 AND jtdr.qual_type_id = lp_qual_type_id;
3735
3736 /* ARPATEL: 12/08/2003 Cursor used to update num_qual in jtf_terr_qtype_usgs_all for Oracle Sales */
3737 CURSOR csr_get_terr_num_qual ( lp_source_id NUMBER
3738 , lp_qual_type_id NUMBER ) IS
3739 SELECT jtqu.terr_id
3740 , jtqu.terr_qtype_usg_id
3741 FROM jtf_terr_qtype_usgs_all jtqu
3742 , jtf_terr_denorm_rules_all jtdr
3743 , jtf_qual_type_usgs jqtu
3744 WHERE jqtu.qual_type_id = LP_qual_type_id
3745 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
3746 AND jtqu.terr_id = jtdr.terr_id
3747 AND jtdr.resource_exists_flag = 'Y'
3748 AND jtdr.terr_id = jtdr.related_terr_id
3749 AND jtdr.source_id = LP_source_id;
3750
3751 CURSOR csr_get_RSC_denorm_terr ( lp_source_id NUMBER
3752 , lp_qual_type_id NUMBER) IS
3753 SELECT jtdr.terr_id
3754 FROM jtf_terr_denorm_rules_all jtdr
3755 WHERE jtdr.terr_id = jtdr.related_terr_id
3756 AND jtdr.source_id = lp_source_id
3757 AND jtdr.qual_type_id = lp_qual_type_id
3758 AND jtdr.resource_exists_flag = 'Y';
3759
3760 /* ARPATEL: 12/03/2003: Oracle Sales only has one record per territory in terr_denorm_rules_all (no longer strpied by TX type) */
3761 CURSOR csr_get_SALES_denorm_terr ( lp_source_id NUMBER ) IS
3762 SELECT jtdr.terr_id
3763 FROM jtf_terr_denorm_rules_all jtdr
3764 WHERE jtdr.terr_id = jtdr.related_terr_id
3765 AND jtdr.source_id = lp_source_id;
3766
3767 CURSOR csr_get_transactions(lp_source_id NUMBER) IS
3768 SELECT jqt.qual_type_id
3769 FROM jtf_qual_type_usgs jqtu, jtf_qual_types jqt
3770 WHERE jqtu.qual_type_id = jqt.qual_type_id
3771 AND jqt.qual_type_id <> -1001
3772 AND jqtu.source_id = lp_source_id;
3773
3774 dummy NUMBER(15);
3775 x_terr_count NUMBER(15);
3776 x_package_max NUMBER(15);
3777 x_package_count NUMBER(15);
3778 package_name VARCHAR2(30);
3779 package_desc VARCHAR2(100);
3780 l_index NUMBER;
3781 l_terr_id NUMBER;
3782
3783 l_status VARCHAR(10);
3784
3785 lp_qual_type_id NUMBER;
3786
3787 l_mv1_count NUMBER;
3788 l_mv2_count NUMBER;
3789 l_mv3_count NUMBER;
3790 l_mv4_count NUMBER;
3791 l_mv5_count NUMBER;
3792 l_mv6_count NUMBER;
3793
3794 l_denorm_count NUMBER;
3795
3796 BEGIN
3797 -- Initialize Global variables
3798 g_terr_pkgspec.DELETE;
3799 g_pkgname_tbl.Delete;
3800 g_Pointer := 0;
3801 G_Debug := FALSE;
3802 g_stack_pointer := 0;
3803 g_source_id := p_source_id;
3804 g_abs_source_id := ABS(p_source_id);
3805 g_qual_type_id := p_qualifier_type_id;
3806
3807 --g_cached_org_append := '_' || fnd_profile.value('ORG_ID');
3808
3809 -- Initialize
3810 --SELECT name g_qualifier_type
3811 -- INTO g_qualifier_type
3812 -- FROM jtf_qual_types
3813 -- WHERE qual_type_id = p_qualifier_type_id;
3814
3815 --
3816 --If the SQL trace flag is turned on, then turm on the trace
3817 /* ARPATEL: 12/15/2003 Bug#3305019 */
3818 --If upper(p_SQL_Trace) = 'Y' Then
3819 -- dbms_session.set_sql_trace(TRUE);
3820 --Else
3821 -- dbms_session.set_sql_trace(FALSE);
3822 --End If;
3823
3824 -- If the debug flag is set, Then turn on the debug message logging
3825 If upper( rtrim(p_Debug_Flag) ) = 'Y' Then
3826 G_Debug := TRUE;
3827 End If;
3828
3829 If G_Debug Then
3830 Write_Log(2, 'Inside Generate_API initialize');
3831 Write_Log(2, 'source_id - ' || TO_CHAR(p_Source_Id) );
3832 Write_Log(2, 'qualifier_type_id - ' || TO_CHAR(p_qualifier_type_id) );
3833 End If;
3834
3835 -- 01/15/03: JDOCHERT:
3836 -- Only for Oracle Sales and Telesales
3837 /* ACHANDA : Commented out as the territory creation is removed to the package JTF_TTY_GEN_TERR_PVT */
3838 /*
3839 IF (p_source_id = -1001) THEN
3840
3841 -- 12/31/02 sbehera added call to
3842 -- generate territory for NA and OVERLAY
3843 If G_Debug Then
3844 Write_Log(2, 'START: generate_named_overlay_terr');
3845 End If;
3846
3847 --
3848 -- 1159NA: Territory Creation
3849 --ARPATEL 09/16 1159 branch fix: removed ref to JTF_TTY_NA_GEO_TERR_PVT
3850 -- ACHANDA : Bug # 3233322 : the following line is commented out as the territory
3851 -- creation is removed to a package JTF_TTY_GEN_TERR_PVT
3852 --generate_named_overlay_terr(p_mode => 'TOTAL');
3853 --
3854
3855 If G_Debug Then
3856 Write_Log(2, 'END: generate_named_overlay_terr');
3857 End If;
3858
3859 END IF;
3860 */
3861
3862 -- 1159: Transaction Type is optional: GTP will
3863 -- run for all the valid transaction types for a Usage
3864 IF ( p_source_id IS NOT NULL AND p_qualifier_type_id IS NULL ) THEN
3865
3866 --ARPATEL: 12/03/2003 call denorm package only once for Oracle Sales.
3867 IF (p_source_id = -1001) THEN
3868
3869 --dbms_output.put_line('GEN: B4 JTF_TERR_DENORM_RULES_PVT.Populate_API');
3870 JTF_TERR_DENORM_RULES_PVT.Populate_API( P_ERROR_CODE => retcode
3871 , P_ERROR_MSG => errbuf
3872 , P_SOURCE_ID => p_source_id
3873 , p_qual_type_id => lp_qual_type_id );
3874 --dbms_output.put_line('AFTER: B4 JTF_TERR_DENORM_RULES_PVT.Populate_API');
3875
3876
3877 /* populate resource_exists_flag and absolute rank for Oracle Sales */
3878 UPDATE jtf_terr_denorm_rules_all j
3879 SET j.resource_exists_flag = 'Y'
3880 WHERE EXISTS
3881 ( SELECT jtr.terr_id
3882 FROM jtf_terr_rsc_all jtr
3883 WHERE (jtr.end_date_active IS NULL OR jtr.end_date_active >= SYSDATE)
3884 AND (jtr.start_date_active IS NULL OR jtr.start_date_active <= SYSDATE)
3885 AND jtr.terr_id = j.terr_id
3886 )
3887 AND j.terr_id = j.related_terr_id
3888 AND j.source_id = p_source_id;
3889
3890
3891 --dbms_output.put_line('UPDATE jtf_terr_denorm_rules_all, SET jtdr.resource_exists_flag ');
3892 FOR csr_dnm IN csr_get_SALES_denorm_terr( p_source_id ) LOOP
3893
3894 UPDATE jtf_terr_denorm_rules_all jtdr
3895 SET jtdr.ABSOLUTE_RANK = (
3896 SELECT SUM(jt1.relative_rank)
3897 FROM jtf_terr_denorm_rules_all jt1
3898 WHERE jt1.related_terr_id = jt1.terr_id
3899
3900 /* JDOCHERT: 12/09/03: records in JTF_TERR_DENORM_RULES_ALL
3901 ** are no longer striped by QUAL_TYPE_ID, so commenting out the
3902 ** the following lines
3903 */
3904 --ARPATEL: 11/12/03 Bug#3254575 */
3905 --AND jt1.qual_type_id = lp_qual_type_id
3906 --
3907 --
3908
3909 AND jt1.terr_id IN
3910 ( SELECT jt.terr_id
3911 FROM jtf_terr_all jt
3912 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
3913 START WITH jt.terr_id = csr_dnm.terr_id )
3914 )
3915 WHERE jtdr.source_id = p_source_id
3916
3917 --
3918 -- JDOCHERT: 10/25/03: Following line was commented out
3919 -- as real-time APIs depend on ABSOLUTE_RANK being set for
3920 -- them to return results correctly
3921 --
3922 --AND jtdr.related_terr_id = jtdr.terr_id
3923 --
3924
3925 AND jtdr.terr_id = CSR_DNM.terr_id;
3926
3927 END LOOP;
3928
3929 --dbms_output.put_line('GEN: AFTER: UPDATE jtf_terr_denorm_rules_all, SET jtdr.resource_exists_flag ');
3930
3931 END IF;
3932
3933
3934 OPEN csr_get_transactions (p_source_id);
3935 LOOP
3936 FETCH csr_get_transactions INTO lp_qual_type_id;
3937 EXIT WHEN csr_get_transactions%NOTFOUND;
3938
3939 --dbms_output.put_line('Value of lp_qual_type_id='||TO_CHAR(lp_qual_type_id));
3940
3941 -- Oracle Sales => new architecture
3942 /* JTF_TERR_<SOURCE_ID>_<TRANS>_DYN API architecture */
3943 --
3944 JTF_TERR_ENGINE_GEN2_PVT.Generate_API(
3945 ERRBUF => errbuf,
3946 RETCODE => retcode,
3947 p_Source_Id => p_source_id, --Source Name
3948 p_qualifier_type_id => lp_qual_type_id,
3949 p_Debug_Flag => p_debug_flag,
3950 p_SQL_Trace => p_sql_trace );
3951
3952 /* ACHANDA 03/08/2004 : Bug 3380047 : Program should terminate with error */
3953 /* if one of the dynamically created packages are in invalid status */
3954 If (RETCODE = 2) Then
3955 g_ProgramStatus := 2;
3956 End If;
3957
3958 --dbms_output.put_line(' ');
3959 --dbms_output.put_line('Calling denorm API for ' || p_source_id ||
3960 --'/' || p_qualifier_type_id);
3961 --dbms_output.put_line(' ');
3962
3963
3964 /* build denormalised territory hierarchy table */
3965 -- ARPATEL: 12/03/2003
3966 -- Only process denorm records for each transaction type
3967 -- IF THE USAGE IS NOT ORACLE SALES
3968 --
3969 IF ( p_source_id <> -1001 ) THEN
3970
3971 JTF_TERR_DENORM_RULES_PVT.Populate_API( P_ERROR_CODE => retcode
3972 , P_ERROR_MSG => errbuf
3973 , P_SOURCE_ID => p_source_id
3974 , p_qual_type_id => lp_qual_type_id );
3975
3976 /* Setting resource_exists flag */
3977 BEGIN
3978
3979 /* get all territories that have resources attached */
3980 UPDATE jtf_terr_denorm_rules_all j
3981 SET j.resource_exists_flag = 'Y'
3982 WHERE EXISTS
3983 ( SELECT jtr.terr_id
3984 FROM jtf_terr_rsc_all jtr
3985 WHERE (jtr.end_date_active IS NULL OR jtr.end_date_active >= SYSDATE)
3986 AND (jtr.start_date_active IS NULL OR jtr.start_date_active <= SYSDATE)
3987 AND jtr.terr_id = j.terr_id
3988 )
3989 AND j.terr_id = j.related_terr_id
3990 AND j.source_id = p_source_id
3991 AND j.qual_type_id = lp_qual_type_id;
3992
3993
3994 EXCEPTION
3995 WHEN OTHERS THEN
3996 NULL;
3997 END;
3998
3999
4000 /* Setting Absolute Rank */
4001 BEGIN
4002
4003 FOR csr_dnm IN csr_get_denorm_terr( p_source_id
4004 , lp_qual_type_id) LOOP
4005
4006 UPDATE jtf_terr_denorm_rules_all jtdr
4007 SET jtdr.ABSOLUTE_RANK = (
4008 SELECT SUM(jt1.relative_rank)
4009 FROM jtf_terr_denorm_rules_all jt1
4010 WHERE jt1.related_terr_id = jt1.terr_id
4011
4012 /* ARPATEL: 11/12/03 Bug#3254575 */
4013 AND jt1.qual_type_id = lp_qual_type_id
4014
4015 AND jt1.terr_id IN
4016 ( SELECT jt.terr_id
4017 FROM jtf_terr_all jt
4018 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
4019 START WITH jt.terr_id = csr_dnm.terr_id )
4020 )
4021 WHERE jtdr.source_id = p_source_id
4022 AND jtdr.qual_type_id = lp_qual_type_id
4023
4024 --
4025 -- JDOCHERT: 10/25/03: Following line was commented out
4026 -- as real-time APIs depend on ABSOLUTE_RANK being set for
4027 -- to return results correctly
4028 --
4029 --AND jtdr.related_terr_id = jtdr.terr_id
4030 --
4031
4032 AND jtdr.terr_id = CSR_DNM.terr_id;
4033
4034 END LOOP;
4035
4036
4037 EXCEPTION
4038 WHEN OTHERS THEN
4039 NULL;
4040 END;
4041
4042 END IF; --p_source_id <> -1001
4043
4044
4045 /* ONLY FOR ORACLE SALES */
4046 IF ( p_source_id = -1001 ) THEN
4047
4048 If G_Debug Then
4049 Write_Log(2, 'START: UPDATE jtf_terr_qtype_usgs_all ');
4050 End If;
4051
4052 /* ACHANDA 02/03/04 Bug 3373687 : disable the trigger before update */
4053 BEGIN
4054 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD DISABLE';
4055 EXCEPTION
4056 WHEN OTHERS THEN
4057 NULL;
4058 END;
4059
4060 /* ARPATEL: 12/03/2003 populate
4061 ** jtf_terr_qtype_usgs_all.num_qual column ONLY
4062 ** for Oracle Sales (-1001) */
4063 FOR csr_dnm IN csr_get_terr_num_qual( p_source_id
4064 , lp_qual_type_id) LOOP
4065
4066 --dbms_output.put_line('GEN: UPDATE jtf_terr_qtype_usgs_all '||lp_qual_type_id);
4067
4068 UPDATE jtf_terr_qtype_usgs_all qua
4069 SET qua.num_qual = (
4070 SELECT COUNT(*)
4071 FROM jtf_terr_qual_all jtq
4072 , jtf_qual_usgs_all jqu
4073 , jtf_qual_type_usgs jqtu
4074 , jtf_qual_type_denorm_v v
4075 WHERE jtq.qual_usg_id = jqu.qual_usg_id
4076 /* ACHANDA 03/08/2004 : Bug 3378530 : change the where clause to use index more selectively */
4077 AND jqu.org_id = -3113
4078 /*
4079 AND ( (jtq.org_id = jqu.org_id) OR
4080 (jtq.org_id IS NULL AND jqu.org_ID IS NULL)
4081 )
4082 */
4083 AND jqu.qual_type_usg_id = jqtu.qual_type_usg_id
4084 AND jqtu.qual_type_id <> -1001
4085 AND jqtu.source_id = p_source_id
4086 AND jqtu.qual_type_id = v.related_id
4087 AND v.qual_type_id = lp_qual_type_id
4088 AND jtq.terr_id IN
4089 ( SELECT jt.terr_id
4090 FROM jtf_terr_all jt
4091 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
4092 START WITH jt.terr_id = csr_dnm.terr_id )
4093 )
4094 WHERE qua.terr_qtype_usg_id = csr_dnm.terr_qtype_usg_id;
4095
4096 --dbms_output.put_line('AFTER: GEN: UPDATE jtf_terr_qtype_usgs_all '||lp_qual_type_id);
4097
4098 END LOOP;
4099
4100 /* ACHANDA 02/03/04 Bug 3373687 : enable the trigger after update */
4101 BEGIN
4102 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD ENABLE';
4103 EXCEPTION
4104 WHEN OTHERS THEN
4105 NULL;
4106 END;
4107
4108 If G_Debug Then
4109 Write_Log(2, 'END: UPDATE jtf_terr_qtype_usgs_all ');
4110 End If;
4111
4112 END IF; -- end of p_source_id = -1001
4113
4114 -- 01/26/03: JDOCHERT:
4115 -- Only for Oracle Sales and Telesales
4116 --
4117 /* JTF_TAE_<SOURCE_ID>_<TRANS>_DYN API architecture */
4118 --
4119 -- This is required since currently we only support TAE for
4120 -- Accounts, Leads, and Opportunities
4121 --
4122 IF ( lp_qual_type_id IN (-1002, -1003, -1004) ) THEN
4123
4124 /* New TAE architecture */
4125 JTF_TAE_GEN_PVT.Generate_API( ERRBUF => errbuf,
4126 RETCODE => retcode,
4127 p_Source_Id => p_source_id,
4128 p_Trans_Object_Type_Id => lp_qual_type_id,
4129 p_target_type => 'TAP',
4130 p_Debug_Flag => p_debug_flag,
4131 p_SQL_Trace => p_sql_trace );
4132
4133 /* ACHANDA 03/08/2004 : Bug 3380047 : Program should terminate with error */
4134 /* if one of the dynamically created packages are in invalid status */
4135 If (RETCODE = 2) Then
4136 g_ProgramStatus := 2;
4137 End If;
4138
4139 END IF;
4140
4141
4142
4143 END LOOP;
4144 CLOSE csr_get_transactions;
4145
4146
4147 ELSIF ( p_source_id IS NOT NULL AND
4148 p_qualifier_type_id IS NOT NULL ) THEN
4149
4150
4151 LP_QUAL_TYPE_ID := p_qualifier_type_id;
4152
4153 /* Real Time API architecture */
4154 JTF_TERR_ENGINE_GEN2_PVT.Generate_API(
4155 ERRBUF => errbuf,
4156 RETCODE => retcode,
4157 p_Source_Id => p_source_id,
4158 p_qualifier_type_id => LP_QUAL_TYPE_ID,
4159 p_Debug_Flag => p_debug_flag,
4160 p_SQL_Trace => p_sql_trace );
4161
4162 /* ACHANDA 03/08/2004 : Bug 3380047 : Program should terminate with error */
4163 /* if one of the dynamically created packages are in invalid status */
4164 If (RETCODE = 2) Then
4165 g_ProgramStatus := 2;
4166 End If;
4167
4168 --dbms_output.put_line(' ');
4169 --dbms_output.put_line('Calling denorm API for ' || p_source_id ||
4170 --'/' || p_qualifier_type_id);
4171 --dbms_output.put_line(' ');
4172
4173 /* build denormalised territory hierarchy table */
4174 JTF_TERR_DENORM_RULES_PVT.Populate_API( P_ERROR_CODE => retcode
4175 , P_ERROR_MSG => errbuf
4176 , P_SOURCE_ID => p_source_id
4177 , p_qual_type_id => LP_QUAL_TYPE_ID );
4178
4179
4180 /* ARPATEL: 12/04 Special handling for Oracle Sales, records
4181 ** in denorm_rules all no longer striped by transaction type
4182 */
4183 IF ( p_source_id = -1001 ) THEN
4184
4185 BEGIN
4186 /* get all territories that have resources attached */
4187 UPDATE jtf_terr_denorm_rules_all j
4188 SET j.resource_exists_flag = 'Y'
4189 WHERE EXISTS
4190 ( SELECT jtr.terr_id
4191 FROM jtf_terr_rsc_all jtr
4192 WHERE (jtr.end_date_active IS NULL OR jtr.end_date_active >= SYSDATE)
4193 AND (jtr.start_date_active IS NULL OR jtr.start_date_active <= SYSDATE)
4194 AND jtr.terr_id = j.terr_id
4195 )
4196 AND j.terr_id = j.related_terr_id
4197 AND j.source_id = p_source_id;
4198
4199 EXCEPTION
4200 WHEN OTHERS THEN
4201 NULL;
4202 END;
4203
4204 BEGIN
4205
4206 FOR csr_dnm IN csr_get_SALES_denorm_terr( p_source_id ) LOOP
4207
4208 UPDATE jtf_terr_denorm_rules_all jtdr
4209 SET jtdr.ABSOLUTE_RANK = (
4210 SELECT SUM(jt1.relative_rank)
4211 FROM jtf_terr_denorm_rules_all jt1
4212 WHERE jt1.related_terr_id = jt1.terr_id
4213
4214
4215 /* JDOCHERT: 12/09/03: records in JTF_TERR_DENORM_RULES_ALL
4216 ** are no longer striped by QUAL_TYPE_ID, so commenting out the
4217 ** the following lines
4218 */
4219 /* ARPATEL: 11/12/03 Bug#3254575 */
4220 --AND jt1.qual_type_id = p_qualifier_type_id
4221 --
4222
4223 AND jt1.terr_id IN
4224 ( SELECT jt.terr_id
4225 FROM jtf_terr_all jt
4226 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
4227 START WITH jt.terr_id = csr_dnm.terr_id )
4228 )
4229 WHERE jtdr.source_id = p_source_id
4230 AND jtdr.terr_id = CSR_DNM.terr_id;
4231
4232 END LOOP;
4233
4234 EXCEPTION
4235 WHEN OTHERS THEN
4236 NULL;
4237 END;
4238
4239
4240 ELSE --p_source_id <> -1001
4241
4242 /* moved from jtf_terr_denorm_rules_pvt */
4243 BEGIN
4244 /* get all territories that have resources attached */
4245 UPDATE jtf_terr_denorm_rules_all j
4246 SET j.resource_exists_flag = 'Y'
4247 WHERE EXISTS
4248 ( SELECT jtr.terr_id
4249 FROM jtf_terr_rsc_all jtr
4250 WHERE (jtr.end_date_active IS NULL OR jtr.end_date_active >= SYSDATE)
4251 AND (jtr.start_date_active IS NULL OR jtr.start_date_active <= SYSDATE)
4252 AND jtr.terr_id = j.terr_id
4253 )
4254 AND j.terr_id = j.related_terr_id
4255 AND j.source_id = p_source_id
4256 AND j.qual_type_id = LP_QUAL_TYPE_ID;
4257 EXCEPTION
4258 WHEN OTHERS THEN
4259 NULL;
4260 END;
4261
4262
4263 BEGIN
4264
4265 FOR csr_dnm IN csr_get_denorm_terr( p_source_id
4266 , LP_QUAL_TYPE_ID ) LOOP
4267
4268 UPDATE jtf_terr_denorm_rules_all jtdr
4269 SET jtdr.ABSOLUTE_RANK = (
4270 SELECT SUM(jt1.relative_rank)
4271 FROM jtf_terr_denorm_rules_all jt1
4272 WHERE jt1.related_terr_id = jt1.terr_id
4273 /* ARPATEL: 11/12/03 Bug#3254575 */
4274 AND jt1.qual_type_id = p_qualifier_type_id
4275 AND jt1.terr_id IN
4276 ( SELECT jt.terr_id
4277 FROM jtf_terr_all jt
4278 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
4279 START WITH jt.terr_id = csr_dnm.terr_id )
4280 )
4281 WHERE jtdr.source_id = p_source_id
4282 AND jtdr.qual_type_id = LP_QUAL_TYPE_ID
4283
4284 --
4285 -- JDOCHERT: 10/25/03: Following line was commented out
4286 -- as real-time APIs depend on ABSOLUTE_RANK being set for
4287 -- to return results correctly
4288 --
4289 --AND jtdr.related_terr_id = jtdr.terr_id
4290 --
4291
4292 AND jtdr.terr_id = CSR_DNM.terr_id;
4293
4294
4295 END LOOP;
4296
4297 EXCEPTION
4298 WHEN OTHERS THEN
4299 NULL;
4300 END;
4301
4302 end if; --p_source_id = -1001
4303
4304 IF ( p_source_id = -1001 ) THEN
4305
4306 If G_Debug Then
4307 Write_Log(2, 'START: UPDATE jtf_terr_qtype_usgs_all ');
4308 End If;
4309
4310 /* ACHANDA 02/03/04 Bug 3373687 : disable the trigger before update */
4311 BEGIN
4312 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD DISABLE';
4313 EXCEPTION
4314 WHEN OTHERS THEN
4315 NULL;
4316 END;
4317
4318 /* ARPATEL: 12/03/2003 populate num_qual column ONLY for Oracle Sales (-1001) */
4319 FOR csr_dnm IN csr_get_terr_num_qual( p_source_id
4320 , LP_QUAL_TYPE_ID ) LOOP
4321
4322 --dbms_output.put_line('GEN: UPDATE jtf_terr_qtype_usgs_all '||lp_qual_type_id);
4323 UPDATE jtf_terr_qtype_usgs_all qua
4324 SET qua.num_qual = ( SELECT count(*)
4325 FROM jtf_terr_qual_all jtq
4326 , jtf_qual_usgs_all jqu
4327 , jtf_qual_type_usgs jqtu
4328 , jtf_qual_type_denorm_v v
4329 WHERE jtq.qual_usg_id = jqu.qual_usg_id
4330 /* ACHANDA 02/03/2004 : Bug 3378530 : change the where clause to use index more selectively */
4331 AND jqu.org_id = -3113
4332 /*
4333 AND ( (jtq.org_id = jqu.org_id) OR
4334 (jtq.org_id IS NULL AND jqu.org_ID IS NULL)
4335 )
4336 */
4337 AND jqu.qual_type_usg_id = jqtu.qual_type_usg_id
4338 AND jqtu.qual_type_id <> -1001
4339 AND jqtu.source_id = p_source_id
4340 AND jqtu.qual_type_id = v.related_id
4341 AND v.qual_type_id = p_qualifier_type_id
4342 AND jtq.terr_id IN
4343 ( SELECT jt.terr_id
4344 FROM jtf_terr_all jt
4345 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
4346 START WITH jt.terr_id = csr_dnm.terr_id )
4347 )
4348 WHERE qua.terr_qtype_usg_id = csr_dnm.terr_qtype_usg_id;
4349
4350 --dbms_output.put_line('AFTER: GEN: UPDATE jtf_terr_qtype_usgs_all '||lp_qual_type_id);
4351
4352 END LOOP;
4353
4354 /* ACHANDA 02/03/04 Bug 3373687 : enable the trigger after update */
4355 BEGIN
4356 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD ENABLE';
4357 EXCEPTION
4358 WHEN OTHERS THEN
4359 NULL;
4360 END;
4361
4362 If G_Debug Then
4363 Write_Log(2, 'END: UPDATE jtf_terr_qtype_usgs_all ');
4364 End If;
4365
4366 END IF; -- end of p_source_id = -1001
4367
4368
4369 -- 01/26/03: JDOCHERT:
4370 -- Only for Oracle Sales and Telesales
4371 IF ( p_source_id = -1001 AND
4372 p_qualifier_type_id IN (-1002, -1003, -1004) ) THEN
4373
4374 /* New TAE Batch architecture */
4375 JTF_TAE_GEN_PVT.Generate_API( ERRBUF => errbuf,
4376 RETCODE => retcode,
4377 p_Source_Id => p_source_id,
4378 p_Trans_Object_Type_Id => p_qualifier_type_id,
4379 p_target_type => 'TAP',
4380 p_Debug_Flag => p_debug_flag,
4381 p_SQL_Trace => p_sql_trace );
4382
4383 /* ACHANDA 03/08/2004 : Bug 3380047 : Program should terminate with error */
4384 /* if one of the dynamically created packages are in invalid status */
4385 If (RETCODE = 2) Then
4386 g_ProgramStatus := 2;
4387 End If;
4388
4389
4390 END IF;
4391
4392 END IF;
4393
4394 /* Oracle Sales and Telesales */
4395 IF (p_source_id = -1001) THEN
4396
4397 BEGIN
4398
4399 /* JDOCHERT: 05/07/03: BUG#2947497 FIX */
4400 BEGIN
4401 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORY_RSC_BIUD DISABLE';
4402 EXCEPTION
4403 WHEN OTHERS THEN
4404 NULL;
4405 END;
4406
4407
4408 /* PERSON_ID required for OSO TAP */
4409 UPDATE jtf_terr_rsc_all jtr
4410 SET jtr.person_id =
4411 ( SELECT jrrev.source_id
4412 FROM jtf_rs_resource_extns_vl jrrev
4413 WHERE jrrev.category = 'EMPLOYEE'
4414 AND jrrev.resource_id = jtr.resource_id )
4415 WHERE jtr.resource_type= 'RS_EMPLOYEE'
4416 AND jtr.terr_id IN
4417 ( SELECT jtu.terr_id
4418 FROM jtf_terr_usgs_all jtu
4419 WHERE jtu.source_id = p_source_id )
4420 AND EXISTS
4421 ( SELECT jrrev.resource_id
4422 FROM jtf_rs_resource_extns_vl jrrev
4423 WHERE jrrev.resource_id = jtr.resource_id );
4424
4425
4426 /* JDOCHERT: 05/07/03: BUG#2947497 FIX */
4427 BEGIN
4428 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORY_RSC_BIUD ENABLE';
4429 EXCEPTION
4430 WHEN OTHERS THEN
4431 NULL;
4432 END;
4433
4434 EXCEPTION
4435 WHEN OTHERS THEN
4436 --dbms_output.put_line('UPDATING JTF_TERR_RSC_ALL.PERSON_ID: ERROR = ' || sqlerrm);
4437 NULL;
4438 END;
4439
4440 /* JDOCHERT: 12/09/03: bug#3307414
4441 ** since records in JTF_TERR_DENORM_RULES_ALL
4442 ** are no longer striped by QUAL_TYPE_ID for
4443 ** ORACLE SALES, the records in JTF_TERR_DENORM_ALL
4444 ** are no longer required
4445 **/
4446 --
4447 --DELETE jtf_terr_denorm_all
4448 --WHERE source_id = p_source_id;
4449 --
4450 --INSERT INTO jtf_terr_denorm_all (
4451 -- source_id
4452 -- , terr_id
4453 -- , absolute_rank
4454 -- , related_terr_id
4455 -- , top_level_terr_id
4456 -- , num_winners )
4457 --SELECT DISTINCT
4458 -- j.source_id
4459 -- , j.terr_id
4460 -- , j.absolute_rank
4461 -- , j.related_terr_id
4462 -- , j.top_level_terr_id
4463 -- , j.num_winners
4464 --FROM jtf_terr_denorm_rules_all j
4465 --WHERE j.source_id = -1001;
4466 --
4467
4468 END IF;
4469
4470
4471 /* analyse table */
4472 BEGIN
4473
4474 /* JDOCHERT: 06/17/03: bug#2991180 */
4475 FND_STATS.GATHER_TABLE_STATS(
4476 ownname => 'JTF',
4477 tabname => 'JTF_TERR_DENORM_RULES_ALL',
4478 percent => 20,
4479 degree => NULL, /* JDOCHERT: 04/10/03: bug#2896552 */
4480 partname => NULL,
4481 backup_flag => 'NOBACKUP',
4482 cascade => TRUE,
4483 granularity => 'DEFAULT',
4484 hmode => 'FULL'
4485 );
4486
4487
4488 /* JDOCHERT: 12/09/03: bug#3307414
4489 ** since records in JTF_TERR_DENORM_RULES_ALL
4490 ** are no longer striped by QUAL_TYPE_ID for
4491 ** ORACLE SALES, the records in JTF_TERR_DENORM_ALL
4492 ** are no longer required
4493 **/
4494 -- /* JDOCHERT: 06/17/03: bug#2991180 */
4495 -- FND_STATS.GATHER_TABLE_STATS(
4496 -- ownname => 'JTF',
4497 -- tabname => 'JTF_TERR_DENORM_ALL',
4498 -- percent => 20,
4499 -- degree => NULL, /* JDOCHERT: 04/10/03: bug#2896552 */
4500 -- partname => NULL,
4501 -- backup_flag => 'NOBACKUP',
4502 -- cascade => TRUE,
4503 -- granularity => 'DEFAULT'
4504 -- );
4505 --
4506
4507
4508 /* JDOCHERT: 06/17/03: bug#2991180 */
4509 FND_STATS.GATHER_TABLE_STATS(
4510 ownname => 'JTF',
4511 tabname => 'JTF_TERR_QUAL_ALL',
4512 percent => 20,
4513 degree => NULL, /* JDOCHERT: 04/10/03: bug#2896552 */
4514 partname => NULL,
4515 backup_flag => 'NOBACKUP',
4516 cascade => TRUE,
4517 granularity => 'DEFAULT',
4518 hmode => 'FULL'
4519 );
4520
4521 /* JDOCHERT: 06/17/03: bug#2991180 */
4522 FND_STATS.GATHER_TABLE_STATS(
4523 ownname => 'JTF',
4524 tabname => 'JTF_TERR_VALUES_ALL',
4525 percent => 20,
4526 degree => NULL, /* JDOCHERT: 04/10/03: bug#2896552 */
4527 partname => NULL,
4528 backup_flag => 'NOBACKUP',
4529 cascade => TRUE,
4530 granularity => 'DEFAULT',
4531 hmode => 'FULL'
4532 );
4533
4534 EXCEPTION
4535 WHEN OTHERS THEN
4536
4537 If G_Debug Then
4538 Write_Log(1,'Program terminated with OTHERS exception. ' || SQLERRM);
4539 End If;
4540
4541 g_ProgramStatus := 1;
4542
4543 END;
4544
4545 IF (G_Debug) THEN
4546
4547 SELECT count(*)
4548 INTO l_denorm_count
4549 FROM jtf_terr_denorm_rules_all j
4550 WHERE j.source_id = p_source_id
4551 AND ( ( j.qual_type_id = p_qualifier_type_id )
4552 OR
4553 ( p_source_id = -1001 AND p_qualifier_type_id IS NULL )
4554 );
4555
4556 Write_Log(2, ' ');
4557 Write_Log(2, '/***************** BEGIN: DENORM STATUS *********************/');
4558 Write_Log(2, 'Populating denorm table - JTF_TERR_DENORM_RULES_ALL ');
4559 Write_Log(2, 'Inserted ' || l_denorm_count || ' rows into JTF_TERR_DENORM_RULES_ALL ');
4560
4561
4562 /* JDOCHERT: 12/09/03: bug#3307414
4563 ** since records in JTF_TERR_DENORM_RULES_ALL
4564 ** are no longer striped by QUAL_TYPE_ID for
4565 ** ORACLE SALES, the records in JTF_TERR_DENORM_ALL
4566 ** are no longer required
4567 **/
4568 --
4569 --SELECT count(*)
4570 --INTO l_denorm_count
4571 --FROM jtf_terr_denorm_all j
4572 --WHERE j.source_id = p_source_id;
4573 --
4574 --Write_Log(2, 'Populating denorm table - JTF_TERR_DENORM_ALL ');
4575 --Write_Log(2, 'Inserted ' || l_denorm_count || ' rows into JTF_TERR_DENORM_ALL ');
4576 --
4577
4578 Write_Log(2, ' ');
4579 Write_Log(2, '/***************** END: DENORM STATUS ***********************/');
4580
4581
4582 END IF;
4583
4584 /* commit work so that Materialized view can be refreshed */
4585 COMMIT;
4586
4587
4588 /* Oracle Sales and Telesale ONLY: Refresh MVs */
4589 IF (p_source_id = -1001) THEN
4590
4591 /* Refresh Materialized view */
4592 -- Commented the refresh statement as these materialized views are no more used in R12.
4593 -- DBMS_MVIEW.REFRESH('JTF_TERR_QUAL_RULES_MV', 'C', '', TRUE, FALSE, 0,4,0, TRUE);
4594
4595 /* analyse Materialized view */
4596 BEGIN
4597
4598 /* JDOCHERT: 05/07/03: bug#2948883 */
4599 FND_STATS.GATHER_TABLE_STATS(
4600 ownname => 'APPS',
4601 tabname => 'JTF_TERR_QUAL_RULES_MV',
4602 percent => 20,
4603 degree => NULL, /* JDOCHERT: 04/10/03: bug#2896552 */
4604 partname => NULL,
4605 backup_flag => 'NOBACKUP',
4606 cascade => TRUE,
4607 granularity => 'DEFAULT',
4608 hmode => 'FULL'
4609 );
4610
4611 EXCEPTION
4612 WHEN OTHERS THEN
4613
4614 If G_Debug Then
4615 Write_Log(1,'Program terminated with OTHERS exception. ' || SQLERRM);
4616 End If;
4617
4618 g_ProgramStatus := 1;
4619 END;
4620
4621 /* Refresh Materialized view */
4622 -- Commented the refresh statement as these materialized views are no more used in R12.
4623 -- DBMS_MVIEW.REFRESH('JTF_TERR_CNR_QUAL_LIKE_MV', 'C', '', TRUE, FALSE, 0,4,0, TRUE);
4624
4625 /* analyse Materialized view */
4626 BEGIN
4627
4628 /* JDOCHERT: 05/07/03: bug#2948883 */
4629 FND_STATS.GATHER_TABLE_STATS(
4630 ownname => 'APPS',
4631 tabname => 'JTF_TERR_CNR_QUAL_LIKE_MV',
4632 percent => 20,
4633 degree => NULL, /* JDOCHERT: 04/10/03: bug#2896552 */
4634 partname => NULL,
4635 backup_flag => 'NOBACKUP',
4636 cascade => TRUE,
4637 granularity => 'DEFAULT',
4638 hmode => 'FULL'
4639 );
4640
4641 EXCEPTION
4642 WHEN OTHERS THEN
4643
4644 If G_Debug Then
4645 Write_Log(1,'Program terminated with OTHERS exception. ' || SQLERRM);
4646 End If;
4647
4648 g_ProgramStatus := 1;
4649 END;
4650
4651 /* Refresh Materialized view */
4652 -- Commented the refresh statement as these materialized views are no more used in R12.
4653 --DBMS_MVIEW.REFRESH('JTF_TERR_CNR_QUAL_BTWN_MV', 'C', '', TRUE, FALSE, 0,4,0, TRUE);
4654
4655 /* analyse Materialized view */
4656 BEGIN
4657
4658 /* JDOCHERT: 05/07/03: bug#2948883 */
4659 FND_STATS.GATHER_TABLE_STATS(
4660 ownname => 'APPS',
4661 tabname => 'JTF_TERR_CNR_QUAL_BTWN_MV',
4662 percent => 20,
4663 degree => NULL, /* JDOCHERT: 04/10/03: bug#2896552 */
4664 partname => NULL,
4665 backup_flag => 'NOBACKUP',
4666 cascade => TRUE,
4667 granularity => 'DEFAULT',
4668 hmode => 'FULL'
4669 );
4670
4671 EXCEPTION
4672 WHEN OTHERS THEN
4673
4674 If G_Debug Then
4675 Write_Log(1,'Program terminated with OTHERS exception. ' || SQLERRM);
4676 End If;
4677
4678 g_ProgramStatus := 1;
4679 END;
4680
4681 /* Refresh Materialized view */
4682 -- Commented the refresh statement as these materialized views are no more used in R12.
4683 -- DBMS_MVIEW.REFRESH('JTF_TERR_CNRG_EQUAL_MV', 'C', '', TRUE, FALSE, 0,4,0, TRUE);
4684
4685 /* analyse Materialized view */
4686 BEGIN
4687
4688 /* JDOCHERT: 05/07/03: bug#2948883 */
4689 FND_STATS.GATHER_TABLE_STATS(
4690 ownname => 'APPS',
4691 tabname => 'JTF_TERR_CNRG_EQUAL_MV',
4692 percent => 20,
4693 degree => NULL, /* JDOCHERT: 04/10/03: bug#2896552 */
4694 partname => NULL,
4695 backup_flag => 'NOBACKUP',
4696 cascade => TRUE,
4697 granularity => 'DEFAULT',
4698 hmode => 'FULL'
4699 );
4700
4701 EXCEPTION
4702 WHEN OTHERS THEN
4703
4704 If G_Debug Then
4705 Write_Log(1,'Program terminated with OTHERS exception. ' || SQLERRM);
4706 End If;
4707
4708 g_ProgramStatus := 1;
4709 END;
4710
4711 /* Refresh Materialized view */
4712 -- Commented the refresh statement as these materialized views are no more used in R12.
4713 -- DBMS_MVIEW.REFRESH('JTF_TERR_CNRG_LIKE_MV', 'C', '', TRUE, FALSE, 0,4,0, TRUE);
4714
4715 /* analyse Materialized view */
4716 BEGIN
4717
4718 /* JDOCHERT: 05/07/03: bug#2948883 */
4719 FND_STATS.GATHER_TABLE_STATS(
4720 ownname => 'APPS',
4721 tabname => 'JTF_TERR_CNRG_LIKE_MV',
4722 percent => 20,
4723 degree => NULL, /* JDOCHERT: 04/10/03: bug#2896552 */
4724 partname => NULL,
4725 backup_flag => 'NOBACKUP',
4726 cascade => TRUE,
4727 granularity => 'DEFAULT',
4728 hmode => 'FULL'
4729 );
4730
4731 EXCEPTION
4732 WHEN OTHERS THEN
4733
4734 If G_Debug Then
4735 Write_Log(1,'Program terminated with OTHERS exception. ' || SQLERRM);
4736 End If;
4737
4738 g_ProgramStatus := 1;
4739 END;
4740
4741 /* Refresh Materialized view */
4742 -- Commented the refresh statement as these materialized views are no more used in R12.
4743 --DBMS_MVIEW.REFRESH('JTF_TERR_CNRG_BTWN_MV', 'C', '', TRUE, FALSE, 0,4,0, TRUE);
4744
4745 /* analyse Materialized view */
4746 BEGIN
4747
4748 /* JDOCHERT: 05/07/03: bug#2948883 */
4749 FND_STATS.GATHER_TABLE_STATS(
4750 ownname => 'APPS',
4751 tabname => 'JTF_TERR_CNRG_BTWN_MV',
4752 percent => 20,
4753 degree => NULL, /* JDOCHERT: 04/10/03: bug#2896552 */
4754 partname => NULL,
4755 backup_flag => 'NOBACKUP',
4756 cascade => TRUE,
4757 granularity => 'DEFAULT',
4758 hmode => 'FULL'
4759 );
4760
4761 EXCEPTION
4762 WHEN OTHERS THEN
4763
4764 If G_Debug Then
4765 Write_Log(1,'Program terminated with OTHERS exception. ' || SQLERRM);
4766 End If;
4767
4768 g_ProgramStatus := 1;
4769 END;
4770
4771
4772 -- Commented the following script as the materialized views reffered are no more used
4773 -- and stubbed in R12.
4774 /*
4775 IF (G_Debug) THEN
4776
4777 SELECT count(*)
4778 INTO l_mv1_count
4779 FROM jtf_terr_qual_rules_mv j;
4780
4781 SELECT count(*)
4782 INTO l_mv2_count
4783 FROM jtf_terr_cnr_qual_like_mv j;
4784
4785 SELECT count(*)
4786 INTO l_mv3_count
4787 FROM jtf_terr_cnr_qual_btwn_mv j;
4788
4789
4790 SELECT count(*)
4791 INTO l_mv4_count
4792 FROM jtf_terr_cnrg_equal_mv j;
4793
4794 SELECT count(*)
4795 INTO l_mv5_count
4796 FROM jtf_terr_cnrg_like_mv j;
4797
4798 SELECT count(*)
4799 INTO l_mv6_count
4800 FROM jtf_terr_cnrg_btwn_mv j;
4801
4802 Write_Log(2, ' ');
4803 Write_Log(2, '/ ***************** BEGIN: MV STATUS ********************* /');
4804 Write_Log(2, ' ');
4805 Write_Log(2, 'Refreshing materialized view - JTF_TERR_QUAL_RULES_MV ');
4806 Write_Log(2, 'Inserted ' || l_mv1_count || ' rows into JTF_TERR_QUAL_RULES_MV ');
4807 Write_Log(2, ' ');
4808 Write_Log(2, 'Refreshing materialized view - JTF_TERR_CNR_QUAL_LIKE_MV ');
4809 Write_Log(2, 'Inserted ' || l_mv2_count || ' rows into JTF_TERR_CNR_QUAL_LIKE_MV ');
4810 Write_Log(2, ' ');
4811 Write_Log(2, 'Refreshing materialized view - JTF_TERR_CNR_QUAL_BTWN_MV ');
4812 Write_Log(2, 'Inserted ' || l_mv3_count || ' rows into JTF_TERR_CNR_QUAL_BTWN_MV ');
4813 Write_Log(2, ' ');
4814 Write_Log(2, 'Refreshing materialized view - JTF_TERR_CNRG_EQUAL_MV ');
4815 Write_Log(2, 'Inserted ' || l_mv4_count || ' rows into JTF_TERR_CNRG_EQUAL_MV ');
4816 Write_Log(2, ' ');
4817 Write_Log(2, 'Refreshing materialized view - JTF_TERR_CNRG_LIKE_MV ');
4818 Write_Log(2, 'Inserted ' || l_mv5_count || ' rows into JTF_TERR_CNRG_LIKE_MV ');
4819 Write_Log(2, ' ');
4820 Write_Log(2, 'Refreshing materialized view - JTF_TERR_CNRG_LIKE_MV ');
4821 Write_Log(2, 'Inserted ' || l_mv6_count || ' rows into JTF_TERR_CNRG_LIKE_MV ');
4822 Write_Log(2, ' ');
4823 Write_Log(2, '/ ***************** END: MV STATUS *********************** /');
4824 Write_Log(2, ' ');
4825
4826 END IF;
4827 */
4828 END IF; /* (p_source_id = -1001) */
4829
4830
4831 /* ACHANDA 03/08/2004 : Bug 3380047 : Added to force the program error out if the */
4832 /* dynamically created packages are in invalid status */
4833 IF (g_ProgramStatus = 2) THEN
4834 ERRBUF := 'One or more of the dynamically created packages are in invalid status : see log for details.';
4835 RETCODE := 2;
4836 ELSIF (g_ProgramStatus = 1 OR retcode = 1) THEN
4837 ERRBUF := 'Program Completed with exceptions';
4838 RetCODE := 1;
4839 ElSIF (g_ProgramStatus = 0 OR retcode = 0) THEN
4840 ERRBUF := 'Program completed successfully.';
4841 RetCode := 0;
4842 End If;
4843
4844 Write_Log(2,ERRBUF);
4845
4846 EXCEPTION
4847 WHEN utl_file.invalid_path OR utl_file.invalid_mode OR
4848 utl_file.invalid_filehandle OR utl_file.invalid_operation OR
4849 utl_file.write_error Then
4850 ERRBUF := 'Program terminated with exception. Error writing to output file.';
4851 RETCODE := 2;
4852
4853 WHEN OTHERS THEN
4854 If G_Debug Then
4855 Write_Log(1,'Program terminated with OTHERS exception. ' || SQLERRM);
4856 End If;
4857 ERRBUF := 'Program terminated with OTHERS exception. ' || SQLERRM;
4858 RETCODE := 2;
4859 END generate_api;
4860
4861
4862
4863 -- 01/15/01: JDOCHERT: STUBBED OUT - ARCHITECTURE: OBSOLETE SINCE 11.5.4
4864 -- tHIS FUNCTION WILL BUILD THE RULE EXPRESSION
4865 FUNCTION build_rule_expression (
4866 p_terr_id IN NUMBER,
4867 p_start_date IN DATE,
4868 p_end_date IN DATE
4869 )
4870 RETURN BOOLEAN
4871 AS
4872 BEGIN
4873 NULL;
4874 END build_rule_expression;
4875
4876
4877
4878 END JTF_TERR_ENGINE_GEN_PVT;
4879