1 Package BODY JTF_TTY_GEO_WEBADI_INT_PKG AS
2 /* $Header: jtfgtwpb.pls 120.4 2005/09/26 21:08:50 vbghosh ship $ */
3 -- ===========================================================================+
4 -- | Copyright (c) 1999 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +===========================================================================
8 -- Start of Comments
9 -- ---------------------------------------------------
10 -- PURPOSE
11 --
12 -- This package is used to return a list of column in order of selectivity.
13 -- And create indices on columns in order of input
14 --
15 --
16 -- Procedures:
17 -- (see below for specification)
18 --
19 -- NOTES
20 -- This package is publicly available for use
21 --
22 -- HISTORY
23 -- 05/02/2002 SHLI Created
24 -- 12/22/2003 ACHANDA Modified to insert record in jtf_tty_named_acct_changes
25 -- so that GTP can perform increamental processing
26 -- 12/30/2003 SGKUMAR Modified to show data for the active territory
27 -- groups
28 -- 01/02/2004 SGKUMAR Modified update_geo_terr to check if Postal Code
29 -- is being assigned to geo territory for the appropriate
30 -- parent territory.
31 -- 01/07/2004 SGKUMAR Modified POPULATE_INTERFACE to retrieve PCs based on
32 -- geo name ranges instead of geo id range for postal code
33 -- ranges for geo terr group. Modified UPDATE_GEO_TERR also.
34 -- 09/26/2005 VBGHOSH Added code to create corresponding values in terr_q
35 -- uall_all and terr_values_all table
36 -- End of Comments
37 --
38 -- *******************************************************
39 -- Start of Comments
40 -- *******************************************************
41
42
43 procedure POPULATE_INTERFACE( p_userid in varchar2,
44 p_geoterrlist in varchar2,
45 x_seq out NOCOPY varchar2) IS
46
47
48 --RESOURCE_NAME VARRAY_TYPE:=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
49 -- null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
50 GEO_TERR_LIST VARRAY_TYPE:=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,
51 null,null,null,null,null,null,null,null,null,null);
52 -- GEO_SIGN_FLAG VARRAY_TYPE:=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,
53 -- null,null,null,null,null,null,null,null,null,null);
54 -- COL_USED NARRAY_TYPE:=NARRAY_TYPE(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
55
56 salesMgr NUMBER;
57 SEQ NUMBER;
58 ID NUMBER;
59 user_id NUMBER;
60 l_rsc_id NUMBER;
61 L_PARENT_TERR_ID NUMBER;
62 l_var1 VARCHAR2(100);
63 l_var2 VARCHAR2(100);
64 l_v VARCHAR2(1);
65 i NUMBER;
66 j NUMBER;
67 k NUMBER;
68 geoterrnum NUMBER;
69
70
71 --TYPE RefCur IS REF CURSOR; -- define weak REF CURSOR type
72 --nastat RefCur; -- declare cursor variable
73 --na RefCur; -- declare cursor variable
74
75
76
77 CURSOR signed_nd_terr_pc(terr_id IN number) IS
78 select tg.terr_group_name territory_group,
79 pterr.geo_terr_name manager_terr_name,
80 g.country_code country,
81 g.State_code state_province,
82 g.City_code city,
83 g.postal_code postal_code,
84 terr.geo_terr_name geo_terr_name,
85 terrv.geo_terr_value_id tv_id
86 from jtf_tty_geographies g,
87 jtf_tty_geo_terr terr,
88 jtf_tty_geo_terr pterr,
89 jtf_tty_geo_terr_values terrv,
90 jtf_tty_terr_groups tg
91 where terrv.geo_territory_id = terr_id
92 and terrv.geo_territory_id = terr.geo_territory_id
93 and terr.parent_geo_terr_id = pterr.geo_territory_id(+)
94 and terrv.geo_id = g.geo_id
95 and terr.terr_group_id = tg.terr_group_id;
96
97
98
99
100 CURSOR getDefTerrGeo(terr_id IN number) IS
101 select grpv.comparison_operator, grpv.geo_id_from, grpv.geo_id_to,
102 tg.terr_group_name territory_group,
103 pterr.geo_terr_name manager_terr_name,
104 terr.geo_terr_name geo_terr_name
105 from jtf_tty_geo_grp_values grpv,
106 jtf_tty_terr_groups tg,
107 jtf_tty_geo_terr terr,
108 jtf_tty_geo_terr pterr
109 where terr.geo_territory_id =terr_id
110 and terr.terr_group_id = tg.terr_group_id
111 and terr.terr_group_id = grpv.terr_group_id
112 and terr.parent_geo_terr_id = pterr.geo_territory_id(+);
113
114
115 CURSOR unsigned_terr_pc(l_rsc_id IN number) IS
116 select *
117 from(
118 /* postal code = */
119 select tg.terr_group_name territory_group,
120 terr.geo_terr_name manager_terr_name, /* the parent terr name */
121 g.country_code country,
122 g.State_code state_province,
123 g.City_code city,
124 g.postal_code postal_code,
125 null terr_name,
126 g.geo_id geo_id
127 from jtf_tty_geo_grp_values grpv,
128 jtf_tty_terr_groups tg,
129 jtf_tty_geo_terr terr,
130 jtf_tty_geo_terr_rsc rsc,
131 jtf_tty_geographies g --postal_code level
132 where
133 rsc.resource_id = l_rsc_id -- user works in this geo terr
134 and rsc.geo_territory_id = terr.geo_territory_id
135 and terr.terr_group_id = tg.terr_group_id
136 and terr.terr_group_id = grpv.terr_group_id
137 and terr.owner_resource_id < 0
138 and terr.parent_geo_terr_id < 0 -- default terr
139 and SYSDATE BETWEEN tg.active_from_date AND NVL(tg.active_to_date, SYSDATE+1)
140 and grpv.geo_type = 'POSTAL_CODE'
141 and grpv.comparison_operator = '='
142 and g.geo_id = grpv.geo_id_from
143 and g.geo_type = 'POSTAL_CODE'
144 union
145 /* postal code range*/
146 select tg.terr_group_name territory_group,
147 terr.geo_terr_name manager_terr_name, /* the parent terr name */
148 g.country_code country,
149 g.State_code state_province,
150 g.City_code city,
151 g.postal_code postal_code,
152 null terr_name,
153 g.geo_id geo_id
154 from jtf_tty_geo_grp_values grpv,
155 jtf_tty_terr_groups tg,
156 jtf_tty_geo_terr terr,
157 jtf_tty_geo_terr_rsc rsc,
158 jtf_tty_geographies g, --postal_code level
159 jtf_tty_geographies g1,
160 jtf_tty_geographies g2
161 where
162 rsc.resource_id = l_rsc_id -- user works in this geo terr
163 and rsc.geo_territory_id = terr.geo_territory_id
164 and terr.terr_group_id = tg.terr_group_id
165 and terr.terr_group_id = grpv.terr_group_id
166 and terr.owner_resource_id < 0
167 and terr.parent_geo_terr_id < 0 -- default terr
168 and SYSDATE BETWEEN tg.active_from_date AND NVL(tg.active_to_date, SYSDATE+1)
169 and grpv.geo_type = 'POSTAL_CODE'
170 and grpv.comparison_operator = 'BETWEEN'
171 and g.geo_type = 'POSTAL_CODE'
172 AND g1.geo_id = grpv.geo_id_from
173 AND g2.geo_id = grpv.geo_id_to
174 AND g.geo_name BETWEEN g1.geo_name and g2.geo_name
175 union
176 select tg.terr_group_name territory_group,
177 terr.geo_terr_name manager_terr_name, /* the parent terr name */
178 g.country_code country,
179 g.State_code state_province,
180 g.City_code city,
181 g.postal_code postal_code,
182 null terr_name,
183 g.geo_id geo_id
184 from jtf_tty_geo_grp_values grpv,
185 jtf_tty_terr_groups tg,
186 jtf_tty_geo_terr terr,
187 jtf_tty_geo_terr_rsc rsc,
188 jtf_tty_geographies g,
189 jtf_tty_geographies g1
190 where
191 rsc.resource_id = l_rsc_id -- user works in this geo terr
192 and rsc.geo_territory_id = terr.geo_territory_id
193 and terr.terr_group_id = tg.terr_group_id
194 and terr.terr_group_id = grpv.terr_group_id
195 and terr.owner_resource_id < 0
196 and terr.parent_geo_terr_id < 0 -- default terr
197 and SYSDATE BETWEEN tg.active_from_date AND NVL(tg.active_to_date, SYSDATE+1)
198 and (
199 (
200 grpv.geo_type = 'STATE'
201 and g1.geo_id = grpv.geo_id_from
202 and g.STATE_CODE = g1.state_Code
203 and g.country_code = g1.country_Code
204 and g.geo_type = 'POSTAL_CODE'
205 )
206 or
207 ( grpv.geo_type = 'CITY'
208 AND g.geo_type = 'POSTAL_CODE'
209 AND g.country_code = g1.country_code
210 AND (
211 (g.state_code = g1.state_code AND g1.province_code is null)
212 or
213 (g1.province_code = g.province_code AND g1.state_code is null)
214 )
215 AND (g1.county_code is null or g.county_code = g1.county_code)
216 AND g.city_code = g1.city_code
217 AND grpv.geo_id_from = g1.geo_id
218 )
219 or
220 (
221 grpv.geo_type = 'COUNTRY'
222 AND grpv.geo_id_from = g1.geo_id
223 AND g.geo_type = 'POSTAL_CODE'
224 AND g.country_code = g1.country_code
225 )
226 or
227 (
228 grpv.geo_type = 'PROVINCE'
229 AND grpv.geo_id_from = g1.geo_id
230 AND g.geo_type = 'POSTAL_CODE'
231 AND g.country_code = g1.country_code
232 AND g.province_code = g1.province_code
233 )
234 )
235 union
236 select tg.terr_group_name territory_group,
237 terr.geo_terr_name manager_terr_name, /* the parent terr name */
238 g.country_code country,
239 g.State_code state_province,
240 g.City_code city,
241 g.postal_code postal_code,
242 null terr_name,
243 g.geo_id geo_id
244 from jtf_tty_terr_groups tg,
245 jtf_tty_geo_terr terr,
246 jtf_tty_geo_terr_rsc rsc,
247 jtf_tty_geographies g,
248 jtf_tty_geo_terr_values tv
249 where
250 rsc.resource_id = l_rsc_id
251 and rsc.geo_territory_id = terr.geo_territory_id
252 and terr.terr_group_id = tg.terr_group_id
253 and terr.owner_resource_id >= 0
254 and terr.parent_geo_terr_id >= 0 -- not default terr
255 and tv.geo_territory_id = terr.geo_territory_id
256 and g.geo_id = tv.geo_id
257 and SYSDATE BETWEEN tg.active_from_date AND NVL(tg.active_to_date, SYSDATE+1)
258 )
259 where geo_id not in -- the terr the user owners
260 (
261 select tv.geo_id geo_id
262 from jtf_tty_geo_terr terr,
263 jtf_tty_geo_terr_values tv
264 where
265 terr.owner_resource_id = l_rsc_id
266 and tv.geo_territory_id = terr.geo_territory_id
267 );
268
269
270 BEGIN
271
272 -- remove existing old data for this userid
273 delete from JTF_TTY_GEO_WEBADI_INTERFACE
274 where user_id = p_userid
275 and sysdate - creation_date >2;
276
277 select jtf_tty_geo_int_s.nextval into SEQ from dual;
278
279 select count(*) into id from JTF_TTY_GEO_WEBADI_INTERFACE;
280 if id=0 then id:=1;
281 else select max(id)+1 into id from JTF_TTY_GEO_WEBADI_INTERFACE;
282 end if;
283
284 user_id := to_number(p_userid);
285
286 begin
287 select resource_id into l_rsc_id from jtf_rs_resource_extns
288 where user_id = p_userid;
289
290 exception
291 when no_data_found then
292 x_seq := '-100';
293 return;
294 end;
295
296 -- p_geoterrlist in format of: a,bb,ac,ddd,ee,ffff,
297 geoterrnum := 0;
298 k:=1; -- search start
299 j:=1; -- index
300 while j>0 loop
301 j:= instr(p_geoterrlist,',',k);
302 if j>0 then geoterrnum := geoterrnum+1;
303 GEO_TERR_LIST(geoterrnum) := substr(p_geoterrlist,k,j-k);
304 -- GEO_SIGN_FLAG(geoterrnum) := substr(p_geoterrlist,j-1,1);
305 k := j+1;
306 end if;
307 end loop;
308
309
310 -- dbms_output.put_line(l_na_query);
311 -- insert into tmp values(GEO_SIGN_FLAG(i), ''); commit;
312
313 for i in 1..geoterrnum loop
314 -- insert into tmp values(GEO_TERR_LIST(i),'no sign'); commit;
315 if GEO_TERR_LIST(i)<0 then -- unsigned geo terr -999999
316 /* find the pc from all terr the user works in, minus
317 the pc from all terr the user owners */
318
319 FOR pc IN unsigned_terr_pc(l_rsc_id)
320 LOOP
321 INSERT INTO JTF_TTY_GEO_WEBADI_INTERFACE(jtf_tty_webadi_int_id, object_version_number,
322 user_id, user_sequence, territory_group,
323 manager_terr_name, country, state_province,
324 city, postal_code, geo_terr_name,geo_terr_value_id,
325 created_by,creation_date, last_updated_by,
326 last_update_date, last_update_login )
327 VALUES(id, 1, user_id, SEQ, pc.territory_group,
328 pc.manager_terr_name, pc.country, pc.state_province,
329 pc.city, pc.postal_code, null, null,
330 user_id, sysdate,user_id, sysdate,user_id);
331 -- insert into tmp values('two','two'); commit;
332 id := id+1;
333 EXIT WHEN unsigned_terr_pc%NOTFOUND;
334 END LOOP;
335
336
337 ------------------------------------------------------------------------------------------------------
338 else
339 FOR pc IN signed_nd_terr_pc(to_number(GEO_TERR_LIST(i)))
340 LOOP
341 --insert into tmp values(GEO_TERR_LIST(i)||'value', pc.postal_code); commit;
342 INSERT INTO JTF_TTY_GEO_WEBADI_INTERFACE(jtf_tty_webadi_int_id, object_version_number,
346 created_by, creation_date, last_updated_by,
343 user_id, user_sequence, territory_group,
344 manager_terr_name, country, state_province,
345 city, postal_code, geo_terr_name, geo_terr_value_id,
347 last_update_date, last_update_login )
348 VALUES(id, 1, user_id, SEQ, pc.territory_group,
349 pc.manager_terr_name, pc.country, pc.state_province,
350 pc.city, pc.postal_code, pc.geo_terr_name,pc.tv_id,user_id,
351 sysdate,user_id, sysdate,user_id);
352 id := id+1;
353 EXIT WHEN signed_nd_terr_pc%NOTFOUND;
354 END LOOP; -- of fetch
355 --end if; -- l_v='Y'
356 end if;
357 END LOOP; -- of for
358
359 commit;
360
361 x_seq := to_char(SEQ);
362
363 END;
364
365
366
367
368 procedure isDefaultTerr(terr_id IN number, flag out NOCOPY varchar2) IS
369
370
371 l_num number;
372 begin
373 select count(*) into l_num
374 from jtf_tty_geo_terr
375 where geo_territory_id = terr_id
376 and owner_resource_id<0
377 and parent_geo_terr_id<0;
378
379
380 if l_num>0 then flag :='Y';
381 else flag :='N';
382 end if;
383
384 end;
385
386
387
388 procedure UPDATE_GEO_TERR ( --p_user_sequence in varchar2,
389 p_terrgroup in varchar2,
390 p_manager_terr_name in varchar2,
391 p_country in varchar2,
392 p_state_province in varchar2,
393 p_city in varchar2,
394 p_postal_code in varchar2,
395 p_geo_terr_name in varchar2,
396 p_geo_terr_value_id in varchar2,
397 p_userid in varchar2
398 ) IS
399
400 -- Check if the PC is in default terr the user works in
401 CURSOR CheckPCInDefTerr(rsc_id IN NUMBER, p_pc varchar2) IS
402 select count(g.postal_code) exist --, terr.geo_territory_id terr_id
403 --grpv.comparison_operator, grpv.geo_type, grpv.geo_id_from, geo_id_to, terr.geo_territory_id terr_id
404 from jtf_tty_geo_terr terr,
405 jtf_tty_geo_terr_rsc rsc,
406 jtf_tty_geo_grp_values grpv,
407 jtf_tty_geographies g
408 where rsc_id = rsc.resource_id
409 and rsc.geo_territory_id = terr.geo_territory_id
410 and terr.owner_resource_id <0
411 and terr.parent_geo_terr_id<0
412 and terr.terr_group_id = grpv.terr_group_id
413 and grpv.geo_type = 'POSTAL_CODE'
414 and grpv.comparison_operator = '='
415 and g.geo_id = grpv.geo_id_from
416 and g.geo_type = 'POSTAL_CODE'
417 and g.postal_code = p_pc
418
419 union
420 select count(g.postal_code) exist /* postal code range*/
421 from jtf_tty_geo_grp_values grpv,
422 jtf_tty_terr_groups tg,
423 jtf_tty_geo_terr terr,
424 jtf_tty_geo_terr_rsc rsc,
425 jtf_tty_geographies g, --postal_code level
426 jtf_tty_geographies g1,
427 jtf_tty_geographies g2
428 where
429 rsc.resource_id = rsc_id -- user works in this geo terr
430 and rsc.geo_territory_id = terr.geo_territory_id
431 and terr.terr_group_id = tg.terr_group_id
432 and terr.terr_group_id = grpv.terr_group_id
433 and terr.owner_resource_id < 0
434 and terr.parent_geo_terr_id < 0 -- default terr
435 and SYSDATE BETWEEN tg.active_from_date AND NVL(tg.active_to_date, SYSDATE+1)
436 and grpv.geo_type = 'POSTAL_CODE'
437 and grpv.comparison_operator = 'BETWEEN'
438 and g.geo_type = 'POSTAL_CODE'
439 and g.postal_code = p_pc
440 AND g1.geo_id = grpv.geo_id_from
441 AND g2.geo_id = grpv.geo_id_to
442 AND g.geo_name BETWEEN g1.geo_name and g2.geo_name
443 union
444 select count(g.postal_code) exist
445 from jtf_tty_geo_terr terr,
446 jtf_tty_geo_terr_rsc rsc,
447 jtf_tty_geo_grp_values grpv,
448 jtf_tty_geographies g,
449 jtf_tty_geographies g1
450 where rsc_id = rsc.resource_id
451 and rsc.geo_territory_id = terr.geo_territory_id
452 and terr.owner_resource_id <0
453 and terr.parent_geo_terr_id<0
454 and terr.terr_group_id = grpv.terr_group_id
455 and (
456 (
457 grpv.geo_type = 'STATE'
458 and g1.geo_id = grpv.geo_id_from
459 and g.STATE_CODE = g1.state_Code
460 and g.country_code = g1.country_Code
464 or
461 and g.geo_type = 'POSTAL_CODE'
462 and g.postal_code = p_pc
463 )
465 ( grpv.geo_type = 'CITY'
466 AND g.geo_type = 'POSTAL_CODE'
467 AND g.country_code = g1.country_code
468 AND (
469 (g.state_code = g1.state_code AND g1.province_code is null)
470 or
471 (g1.province_code = g.province_code AND g1.state_code is null)
472 )
473 AND (g1.county_code is null or g.county_code = g1.county_code)
474 AND g.city_code = g1.city_code
475 AND grpv.geo_id_from = g1.geo_id
476 and g.postal_code = p_pc
477 )
478 or
479 (
480 grpv.geo_type = 'COUNTRY'
481 AND grpv.geo_id_from = g1.geo_id
482 AND g.geo_type = 'POSTAL_CODE'
483 AND g.country_code = g1.country_code
484 and g.postal_code = p_pc
485 )
486 or
487 (
488 grpv.geo_type = 'PROVINCE'
489 AND grpv.geo_id_from = g1.geo_id
490 AND g.geo_type = 'POSTAL_CODE'
491 AND g.country_code = g1.country_code
492 AND g.province_code = g1.province_code
493 and g.postal_code = p_pc
494 )
495 );
496
497
498
499
500 terr_id number;
501 found number;
502 i number;
503 in_def_terr number;
504 in_reg_terr number;
505 n number;
506 m number;
507 l_user_id varchar2(1000);
508 rsc_id number;
509 x_msg_data varchar2(100);
510 l_geo_id number;
511 l_terr_id number;
512 l_change_id number;
513 l_terr_count number;
514
515 l_terr_id_new NUMBER;
516 l_terr_qual_id NUMBER;
517 l_rank NUMBER;
518 l_geo_name VARCHAR2(360); --from geographies
519 l_terr_value_id NUMBER; --value id corresponding to postal code
520 l_org_id NUMBER;
521
522 l_g_terr_id NUMBER;
523
524
525 begin
526
527 --l_user_id := fnd_global.user_id;
528 -- for proxy user, a user_id is passed in
529 l_user_id := p_userid;
530
531
532
533
534
535
536
537 --insert into tmp values('glb userid',l_user_id); commit;
538 select resource_id into rsc_id from jtf_rs_resource_extns
539 where user_id = l_user_id;
540
541 --Does this Postal Code belong to the current user, i.e., does the user have permission
542 --to assign this postal code to the territories he created?
543 begin
544 in_reg_terr :=1;
545 in_def_terr :=0;
546
547 --insert into tmp values(l_user_id,p_terrgroup); commit;
548 -- check if a regular terr the user working on has the postal code.
549 select terr.geo_territory_id into terr_id
550 from jtf_tty_geo_terr terr,
551 jtf_tty_geo_terr_values terrv,
552 jtf_tty_geo_terr_rsc rsc,
553 jtf_tty_geographies geog
554 where terr.geo_territory_id = terrv.geo_territory_id
555 and terrv.geo_id = geog.geo_id
556 and geog.postal_code = p_postal_code /* the PC is in the terr she works in */
557 and rsc.geo_territory_id = terr.geo_territory_id /* the terr she works in */
558 and rsc_id = rsc.resource_id /* who logged in*/
559 and rownum<2;
560 exception
561 when no_data_found then -- no postalcode - resource_id mapping found
562 in_reg_terr :=0;
563 end;
564
565 if in_reg_terr = 0 then -- check the default geo terr
566 -- start: the resource is not working in any default terr.
567 -- or the default terr does not have any postalcode.
568 FOR tgeo in CheckPCInDefTerr(rsc_id, p_postal_code) -- each grp_value entry
569 LOOP
570
571 if tgeo.exist>0 then in_def_terr:=1;
572 else in_def_terr:=0;
573 end if;
574
575
576 END LOOP;
577
578 end if;
579 if in_reg_terr=0 and in_def_terr =0 and trim(p_postal_code) is not null then
580 /*trim(p_postal_code) is null means the pc is to be removed */
581 fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_POSTAL_CODE');
582 x_msg_data := fnd_message.get();
583 fnd_message.set_name ('JTF', x_msg_data);
584 return;
585 end if;
586 /* Does the Geography territory attached to the postal codes is created by the current user?
587 check the ownership */
588 if trim(p_geo_terr_name) is not null and trim(p_geo_terr_name)<>' ' then
589 select count(terr.geo_territory_id) into i
590 from jtf_tty_geo_terr terr
591 where terr.owner_resource_id = rsc_id
592 and upper(terr.geo_terr_name) = upper(p_geo_terr_name);
593 -- and terr.parent_geo_terr_id = terr_id; removed (sgkumar) parent terr can be default terr
594
595
596 if i=0 then fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_TERR_NAME');
597 x_msg_data := fnd_message.get();
598 fnd_message.set_name ('JTF', x_msg_data);
599 return;
600 end if;
601 end if;
602
603
604 /* Does the Geography territory attached to the postal codes belong
605 to the correct parent territory (sgkumar)*/
606
607 if (trim(p_geo_terr_name) is not null and trim(p_geo_terr_name) <> ' ') then
608 select count(terr1.geo_territory_id) into i
609 from jtf_tty_geo_terr terr1, jtf_tty_geo_terr terr2
610 where terr1.geo_territory_id = terr2.parent_geo_terr_id
611 and upper(terr1.geo_terr_name) = upper(p_manager_terr_name)
612 and terr2.geo_terr_name = p_geo_terr_name;
613 -- and terr.parent_geo_terr_id = terr_id;
614
615 if i=0 then fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_TERR_NAME');
616 x_msg_data := fnd_message.get();
617 fnd_message.set_name ('JTF', x_msg_data);
618 return;
619 end if;
620 end if;
621
622 -- pass the validation, now do the update
623 -- check if the p_pc exists in the PCs the user owners.
624 -- no need check for unsigned because it wont exist?
625
626 select count(geog.postal_code) into found --geog.postal_code, terr.geo_terr_name
627 from jtf_tty_geo_terr terr,
628 jtf_tty_geo_terr_values terrv,
629 jtf_tty_geographies geog
630 where terr.owner_resource_id = rsc_id
631 and terr.geo_territory_id = terrv.geo_territory_id
632 and terrv.geo_id = geog.geo_id
633 and geog.postal_code = p_postal_code
634 and upper(terr.geo_terr_name) = upper(p_geo_terr_name);
635
636 if found=0 then
637 -- the p_geo_terr_value_id is from the old assignment but pc/terr_name is new
638 -- remove the old assignment, only happens in no-default terr
639 -- p_geo_terr_value_id can be null
640 /* delete from jtf_tty_geo_terr_values
644 /*remove recursively*/
641 where geo_terr_value_id = p_geo_terr_value_id;
642 */
643 BEGIN
645 l_geo_id :=0;
646 l_terr_id :=0;
647 select geo_id,geo_territory_id
648 into l_geo_id,l_terr_id
649 from jtf_tty_geo_terr_values
650 where geo_terr_value_id = p_geo_terr_value_id;
651 exception
652 when no_data_found then
653 null;
654 when others then
655 null;
656 END;
657
658 delete from jtf_tty_geo_terr_values gtv
659 where geo_id = l_geo_id
660 and geo_territory_id in (
661 select geo_territory_id
662 from jtf_tty_geo_terr
663 start with geo_territory_id = l_terr_id
664 connect by prior geo_territory_id=parent_geo_terr_id
665 );
666
667 if (trim(p_geo_terr_name) is not null) then -- insert
668 --insert into tmp values('enter',p_geo_terr_name); commit;
669 -- n: geo_terr_value_id
670 if trim(p_geo_terr_value_id) is null then
671 -- new geo_terr_value_id
672 select jtf_tty_geo_terr_values_s.nextval into n from dual;
673 else n:=trim(p_geo_terr_value_id);
674 end if;
675
676
677 -- dbms_output.put_line(' Postal code ******* is'||p_postal_code);
678
679 if trim(p_postal_code) is null then return; -- no need inserting new postal code
680 end if;
681
682 -- m: geo_id
683 select geo_id into m -- geo_id
684 from jtf_tty_geographies
685 where postal_code=p_postal_code;
686
687 begin
688 -- terr_id
689
690 -- dbms_output.put_line(' GEO TERR NAME ******* is'||p_geo_terr_name);
691 select geo_territory_id into terr_id --terr_id
692 from jtf_tty_geo_terr
693 where upper(geo_terr_name) = upper(p_geo_terr_name);
694 --and owner_resource_id = rsc_id; --10121
695 exception
696 when no_data_found then
697
698 fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_TERR_NAME');
699 x_msg_data := fnd_message.get();
700 fnd_message.set_name ('JTF', x_msg_data);
701 return;
702
703 when others then
704 fnd_message.set_name ('JTF', 'JTF_TTY_DUPLICATE_TERRITORY_NAME');
705 x_msg_data := fnd_message.get();
706 fnd_message.set_name ('JTF', x_msg_data);
707 return;
708 end;
709
710
711 insert into jtf_tty_geo_terr_values (geo_terr_value_id,object_version_number,
712 geo_territory_id,geo_id, created_by,
713 creation_date,last_updated_by,
714 last_update_date,last_update_login)
715 values(n,1,terr_id, m, rsc_id, sysdate, rsc_id, sysdate, rsc_id);
716
717
718
719 l_g_terr_id := terr_id;
720
721
722
723
724 /* Vbghosh:
725 Get the terr_id from the geo_terr_id
726 */
727
728
729 /* get the terr_id from the corresponding geo_terr_id*/
730 BEGIN
731
732 --dbms_output.put_line(' GEO TERR ID ******* is'||terr_id);
733
734 SELECT
735 terr_id ,
736 org_id
737 INTO l_terr_id_new
738 , l_org_id
739 FROM jtf_terr_all
740 WHERE geo_territory_id = l_g_terr_id ; --its geo_terr_id
741
742 --dbms_output.put_line(' after selecting terr_id is'||terr_id);
743
744 EXCEPTION
745 WHEN NO_DATA_FOUND THEN
746 RAISE;
747 WHEN OTHERS THEN
748 --dbms_output.put_line('ERROR '||SQLERRM);
749 RAISE;
750
751 END; --getting terr_id
752
753 BEGIN --get the terr_qual_id if null then insert
754 SELECT
755 c.terr_qual_id
756 INTO
757 l_terr_qual_id
758 FROM
759 jtf_terr_all a
760 , jtf_tty_geo_terr b
761 , jtf_terr_qual_all c
762 WHERE
763 b.geo_territory_id = a.geo_Territory_id
764 AND b.geo_territory_id = l_g_terr_id --its geo_terr_id
765 AND c.terr_id = a.terr_id
766 AND c.qual_usg_id = -1007;
767
768 --dbms_output.put_line(' terr_qual_id ID ******* is'|| l_terr_qual_id);
769
770 EXCEPTION
771 WHEN NO_DATA_FOUND THEN
772 --dbms_output.put_line('ERROR '||SQLERRM);
773 NULL;
774 WHEN OTHERS THEN
775 --dbms_output.put_line('ERROR '||SQLERRM);
776 RAISE;
777 END; -- end checking create or update
778
779 IF l_terr_qual_id IS NULL THEN -- need to create using sequence
780 --dbms_output.put_line(' terr_qual_id ID ******* is NULL');
781 /* insert in terr_qual_all table using sequence*/
782 SELECT JTF_TERR_QUAL_S.NEXTVAL
783 INTO l_terr_qual_id
784 FROM DUAL;
785 --dbms_output.put_line(' AFter select ing from seq qual id');
786
787 INSERT INTO jtf_terr_qual_all
788 ( TERR_QUAL_ID
789 , LAST_UPDATE_DATE
790 , LAST_UPDATED_BY
791 , CREATION_DATE
792 , CREATED_BY
793 , LAST_UPDATE_LOGIN
794 , TERR_ID
795 , QUAL_USG_ID
796 , OVERLAP_ALLOWED_FLAG
797 , ORG_ID )
798 SELECT
799 l_terr_qual_id
800 , SYSDATE
801 , LAST_UPDATED_BY
802 , SYSDATE
803 , CREATED_BY
804 , LAST_UPDATE_LOGIN
805 , l_terr_id_new
806 , -1007
807 , 'Y'
808 , l_org_id -- ORgId
809 FROM jtf_tty_geo_terr
810 WHERE geo_territory_id = terr_id;
811
812 --dbms_output.put_line(' AFterinserting qual id');
813
814
815 END IF;
816
817
818
819
820 /* vbghosh: if parameter p_geo_terr_value_id or l_terr_value_id is null
821 then it is a new create and a new value has to be inseted in terr_values_all
822 table otherwise its a updated
823 */
824
825 IF p_geo_terr_value_id IS NULL THEN
826 --dbms_output.put_line(' terr_value id is null ');
827
828
829
830 /* Insert a new row in terr_values_all table , using the geo_terr_value_id "n"*/
831 INSERT INTO jtf_terr_values_all
832 (
833 TERR_VALUE_ID
834 ,LAST_UPDATED_BY
835 ,LAST_UPDATE_DATE
836 ,CREATED_BY
837 ,CREATION_DATE
838 ,LAST_UPDATE_LOGIN
839 ,TERR_QUAL_ID
840 ,COMPARISON_OPERATOR
841 ,ID_USED_FLAG
842 ,ORG_ID
843 ,LOW_VALUE_CHAR -- TODO need to check
844 ,SELF_SERVICE_TERR_VALUE_ID
845 )
846 SELECT
847 JTF_TERR_VALUES_S.NEXTVAL
848 ,LAST_UPDATED_BY
849 ,SYSDATE
850 ,CREATED_BY
851 ,SYSDATE
852 ,LAST_UPDATE_LOGIN
853 ,l_terr_qual_id
854 ,'='
855 ,'N'
856 , l_org_id
857 , p_postal_code
858 , n -- geo_terr_value_id
859 FROM jtf_tty_geo_terr
860 WHERE geo_territory_id = terr_id;
861
862 --dbms_output.put_line(' Inserting terr_value id ');
863 ELSE
864 /* get the corresponding self_service_value_id from the terr_value_table
865 delete it and then insert it
866 */
867 BEGIN
868 --dbms_output.put_line(' before deletin terr_value_id ');
869 DELETE FROM jtf_terr_values_all
870 WHERE SELF_SERVICE_TERR_VALUE_ID = to_number(p_geo_terr_value_id);
871 --dbms_output.put_line(' After deletin terr_value_id and before insertin');
872 --dbms_output.put_line(' After inserting terr_value_id ');
873
874 EXCEPTION
875 WHEN NO_DATA_FOUND THEN
876 NULL;
877 WHEN OTHERS THEN
878 RAISE;
879 END;
880
881 INSERT INTO jtf_terr_values_all
882 (
883 TERR_VALUE_ID
884 ,LAST_UPDATED_BY
885 ,LAST_UPDATE_DATE
886 ,CREATED_BY
887 ,CREATION_DATE
888 ,LAST_UPDATE_LOGIN
889 ,TERR_QUAL_ID
890 ,COMPARISON_OPERATOR
891 ,ID_USED_FLAG
892 ,ORG_ID
893 ,LOW_VALUE_CHAR -- TODO need to check
894 ,SELF_SERVICE_TERR_VALUE_ID
895 )
896 SELECT
897 JTF_TERR_VALUES_S.NEXTVAL
898 ,LAST_UPDATED_BY
899 ,SYSDATE
900 ,CREATED_BY
901 ,SYSDATE
902 ,LAST_UPDATE_LOGIN
903 ,l_terr_qual_id
904 ,'='
905 ,'N'
906 , l_org_id
907 , p_postal_code
908 , to_number(p_geo_terr_value_id) -- geo_terr_value_id
909 FROM jtf_tty_geo_terr
910 WHERE geo_territory_id = terr_id;
911
912
913
914
915 END IF;
916
917
918
919 /* ACHANDA: Inserting values to jtf_tty_named_acct_changes table for GTP
920 to do an incremental and Total Mode */
921
922 select jtf_tty_named_acct_changes_s.nextval
923 into l_change_id
924 from sys.dual;
925
926 insert into jtf_tty_named_acct_changes
927 ( NAMED_ACCT_CHANGE_ID
928 , OBJECT_VERSION_NUMBER
929 , OBJECT_TYPE
930 , OBJECT_ID
931 , CHANGE_TYPE
932 , FROM_WHERE
933 , CREATED_BY
934 , CREATION_DATE
935 , LAST_UPDATED_BY
936 , LAST_UPDATE_DATE
937 , LAST_UPDATE_LOGIN
938 )
939 VALUES (
940 l_change_id
941 , 1
942 , 'GT'
943 , terr_id
944 , 'UPDATE'
945 , 'Update Mapping'
946 , rsc_id
947 , sysdate
948 , rsc_id
949 , sysdate
950 , rsc_id
951 );
952
953 end if;
954 end if; -- of found=0
955
956 commit;
957
958 exception
959 when others then
960 fnd_message.set_name ('JTF', 'JTF_TTY_UNEXPECTED_ERROR');
961 x_msg_data := fnd_message.get();
962 fnd_message.set_name ('JTF', x_msg_data);
963 end UPDATE_GEO_TERR;
964
965
966 END JTF_TTY_GEO_WEBADI_INT_PKG;
[Home] [Help]