1 Package BODY JTF_TTY_GEO_WEBADI_INT_PKG AS
2 /* $Header: jtfgtwpb.pls 120.7 2011/02/24 16:53:45 sseshaiy 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 or
235 (
236 grpv.geo_type = 'COUNTY'
237 AND grpv.geo_id_from = g1.geo_id
238 AND g.geo_type = 'POSTAL_CODE'
239 AND g.country_code = g1.country_code
240 AND g.county_code = g1.county_code
241 )
242 )
243 union
244 select tg.terr_group_name territory_group,
245 terr.geo_terr_name manager_terr_name, /* the parent terr name */
246 g.country_code country,
247 g.State_code state_province,
248 g.City_code city,
249 g.postal_code postal_code,
250 null terr_name,
251 g.geo_id geo_id
252 from jtf_tty_terr_groups tg,
253 jtf_tty_geo_terr terr,
254 jtf_tty_geo_terr_rsc rsc,
255 jtf_tty_geographies g,
256 jtf_tty_geo_terr_values tv
257 where
258 rsc.resource_id = l_rsc_id
259 and rsc.geo_territory_id = terr.geo_territory_id
260 and terr.terr_group_id = tg.terr_group_id
261 and terr.owner_resource_id >= 0
262 and terr.parent_geo_terr_id >= 0 -- not default terr
263 and tv.geo_territory_id = terr.geo_territory_id
264 and g.geo_id = tv.geo_id
265 and SYSDATE BETWEEN tg.active_from_date AND NVL(tg.active_to_date, SYSDATE+1)
266 )
267 where geo_id not in -- the terr the user owners
268 (
269 select tv.geo_id geo_id
270 from jtf_tty_geo_terr terr,
271 jtf_tty_geo_terr_values tv
272 where
273 terr.owner_resource_id = l_rsc_id
274 and tv.geo_territory_id = terr.geo_territory_id
275 );
276
277
278 BEGIN
279
280 -- remove existing old data for this userid
281 delete from JTF_TTY_GEO_WEBADI_INTERFACE
282 where user_id = p_userid
283 and sysdate - creation_date >2;
284
285 select jtf_tty_geo_int_s.nextval into SEQ from dual;
286
287 select count(*) into id from JTF_TTY_GEO_WEBADI_INTERFACE;
288 if id=0 then id:=1;
289 else select max(id)+1 into id from JTF_TTY_GEO_WEBADI_INTERFACE;
290 end if;
294 begin
291
292 user_id := to_number(p_userid);
293
295 select resource_id into l_rsc_id from jtf_rs_resource_extns
296 where user_id = p_userid;
297
298 exception
299 when no_data_found then
300 x_seq := '-100';
301 return;
302 end;
303
304 -- p_geoterrlist in format of: a,bb,ac,ddd,ee,ffff,
305 geoterrnum := 0;
306 k:=1; -- search start
307 j:=1; -- index
308 while j>0 loop
309 j:= instr(p_geoterrlist,',',k);
310 if j>0 then geoterrnum := geoterrnum+1;
311 GEO_TERR_LIST(geoterrnum) := substr(p_geoterrlist,k,j-k);
312 -- GEO_SIGN_FLAG(geoterrnum) := substr(p_geoterrlist,j-1,1);
313 k := j+1;
314 end if;
315 end loop;
316
317
318 -- dbms_output.put_line(l_na_query);
319 -- insert into tmp values(GEO_SIGN_FLAG(i), ''); commit;
320
321 for i in 1..geoterrnum loop
322 -- insert into tmp values(GEO_TERR_LIST(i),'no sign'); commit;
323 if GEO_TERR_LIST(i)<0 then -- unsigned geo terr -999999
324 /* find the pc from all terr the user works in, minus
325 the pc from all terr the user owners */
326
327 FOR pc IN unsigned_terr_pc(l_rsc_id)
328 LOOP
329 INSERT INTO JTF_TTY_GEO_WEBADI_INTERFACE(jtf_tty_webadi_int_id, object_version_number,
330 user_id, user_sequence, territory_group,
331 manager_terr_name, country, state_province,
332 city, postal_code, geo_terr_name,geo_terr_value_id,
333 created_by,creation_date, last_updated_by,
334 last_update_date, last_update_login )
335 VALUES(id, 1, user_id, SEQ, pc.territory_group,
336 pc.manager_terr_name, pc.country, pc.state_province,
337 pc.city, pc.postal_code, null, null,
338 user_id, sysdate,user_id, sysdate,user_id);
339 -- insert into tmp values('two','two'); commit;
340 id := id+1;
341 EXIT WHEN unsigned_terr_pc%NOTFOUND;
342 END LOOP;
343
344
345 ------------------------------------------------------------------------------------------------------
346 else
347 FOR pc IN signed_nd_terr_pc(to_number(GEO_TERR_LIST(i)))
348 LOOP
349 --insert into tmp values(GEO_TERR_LIST(i)||'value', pc.postal_code); commit;
350 INSERT INTO JTF_TTY_GEO_WEBADI_INTERFACE(jtf_tty_webadi_int_id, object_version_number,
351 user_id, user_sequence, territory_group,
352 manager_terr_name, country, state_province,
353 city, postal_code, geo_terr_name, geo_terr_value_id,
354 created_by, creation_date, last_updated_by,
355 last_update_date, last_update_login )
356 VALUES(id, 1, user_id, SEQ, pc.territory_group,
357 pc.manager_terr_name, pc.country, pc.state_province,
358 pc.city, pc.postal_code, pc.geo_terr_name,pc.tv_id,user_id,
359 sysdate,user_id, sysdate,user_id);
360 id := id+1;
361 EXIT WHEN signed_nd_terr_pc%NOTFOUND;
362 END LOOP; -- of fetch
363 --end if; -- l_v='Y'
364 end if;
365 END LOOP; -- of for
366
367 commit;
368
369 x_seq := to_char(SEQ);
370
371 END;
372
373
374
375
376 procedure isDefaultTerr(terr_id IN number, flag out NOCOPY varchar2) IS
377
378
379 l_num number;
380 begin
381 select count(*) into l_num
382 from jtf_tty_geo_terr
383 where geo_territory_id = terr_id
384 and owner_resource_id<0
385 and parent_geo_terr_id<0;
386
387
388 if l_num>0 then flag :='Y';
389 else flag :='N';
390 end if;
391
392 end;
393
394
395
396 procedure UPDATE_GEO_TERR ( --p_user_sequence in varchar2,
397 p_terrgroup in varchar2,
398 p_manager_terr_name in varchar2,
399 p_country in varchar2,
400 p_state_province in varchar2,
401 p_city in varchar2,
402 p_postal_code in varchar2,
403 p_geo_terr_name in varchar2,
404 p_geo_terr_value_id in varchar2,
405 p_userid in varchar2
406 ) IS
407
408 -- Check if the PC is in default terr the user works in
409 CURSOR CheckPCInDefTerr(rsc_id IN NUMBER, p_pc varchar2) IS
410 select count(g.postal_code) exist --, terr.geo_territory_id terr_id
411 --grpv.comparison_operator, grpv.geo_type, grpv.geo_id_from, geo_id_to, terr.geo_territory_id terr_id
412 from jtf_tty_geo_terr terr,
413 jtf_tty_geo_terr_rsc rsc,
414 jtf_tty_geo_grp_values grpv,
415 jtf_tty_geographies g
419 and terr.parent_geo_terr_id<0
416 where rsc_id = rsc.resource_id
417 and rsc.geo_territory_id = terr.geo_territory_id
418 and terr.owner_resource_id <0
420 and terr.terr_group_id = grpv.terr_group_id
421 and grpv.geo_type = 'POSTAL_CODE'
422 and grpv.comparison_operator = '='
423 and g.geo_id = grpv.geo_id_from
424 and g.geo_type = 'POSTAL_CODE'
425 and g.postal_code = p_pc
426
427 union
428 select count(g.postal_code) exist /* postal code range*/
429 from jtf_tty_geo_grp_values grpv,
430 jtf_tty_terr_groups tg,
431 jtf_tty_geo_terr terr,
432 jtf_tty_geo_terr_rsc rsc,
433 jtf_tty_geographies g, --postal_code level
434 jtf_tty_geographies g1,
435 jtf_tty_geographies g2
436 where
437 rsc.resource_id = rsc_id -- user works in this geo terr
438 and rsc.geo_territory_id = terr.geo_territory_id
439 and terr.terr_group_id = tg.terr_group_id
440 and terr.terr_group_id = grpv.terr_group_id
441 and terr.owner_resource_id < 0
442 and terr.parent_geo_terr_id < 0 -- default terr
443 and SYSDATE BETWEEN tg.active_from_date AND NVL(tg.active_to_date, SYSDATE+1)
444 and grpv.geo_type = 'POSTAL_CODE'
445 and grpv.comparison_operator = 'BETWEEN'
446 and g.geo_type = 'POSTAL_CODE'
447 and g.postal_code = p_pc
448 AND g1.geo_id = grpv.geo_id_from
449 AND g2.geo_id = grpv.geo_id_to
450 AND g.geo_name BETWEEN g1.geo_name and g2.geo_name
451 union
452 select count(g.postal_code) exist
453 from jtf_tty_geo_terr terr,
454 jtf_tty_geo_terr_rsc rsc,
455 jtf_tty_geo_grp_values grpv,
456 jtf_tty_geographies g,
457 jtf_tty_geographies g1
458 where rsc_id = rsc.resource_id
459 and rsc.geo_territory_id = terr.geo_territory_id
460 and terr.owner_resource_id <0
461 and terr.parent_geo_terr_id<0
462 and terr.terr_group_id = grpv.terr_group_id
463 and (
464 (
465 grpv.geo_type = 'STATE'
466 and g1.geo_id = grpv.geo_id_from
467 and g.STATE_CODE = g1.state_Code
468 and g.country_code = g1.country_Code
469 and g.geo_type = 'POSTAL_CODE'
470 and g.postal_code = p_pc
471 )
472 or
473 (
474 grpv.geo_type = 'COUNTY'
475 and g1.geo_id = grpv.geo_id_from
476 and g.county_code = g1.county_code
477 and g.country_code = g1.country_Code
478 and g.geo_type = 'POSTAL_CODE'
479 and g.postal_code = p_pc
480 )
481 or
482 ( grpv.geo_type = 'CITY'
483 AND g.geo_type = 'POSTAL_CODE'
484 AND g.country_code = g1.country_code
485 AND (
486 (g.state_code = g1.state_code AND g1.province_code is null)
487 or
488 (g1.province_code = g.province_code AND g1.state_code is null)
489 )
490 AND (g1.county_code is null or g.county_code = g1.county_code)
491 AND g.city_code = g1.city_code
492 AND grpv.geo_id_from = g1.geo_id
493 and g.postal_code = p_pc
494 )
495 or
496 (
497 grpv.geo_type = 'COUNTRY'
498 AND grpv.geo_id_from = g1.geo_id
499 AND g.geo_type = 'POSTAL_CODE'
500 AND g.country_code = g1.country_code
501 and g.postal_code = p_pc
502 )
503 or
504 (
505 grpv.geo_type = 'PROVINCE'
506 AND grpv.geo_id_from = g1.geo_id
507 AND g.geo_type = 'POSTAL_CODE'
508 AND g.country_code = g1.country_code
509 AND g.province_code = g1.province_code
510 and g.postal_code = p_pc
511 )
512 );
513
514
515
516
517 terr_id number;
518 found number;
519 i number;
520 in_def_terr number;
521 in_reg_terr number;
522 n number;
523 m number;
524 l_user_id varchar2(1000);
525 rsc_id number;
526 x_msg_data varchar2(100);
527 l_geo_id number;
528 l_terr_id number;
529 l_change_id number;
530 l_terr_count number;
531
532 l_terr_id_new NUMBER;
533 l_terr_qual_id NUMBER;
534 l_rank NUMBER;
535 l_geo_name VARCHAR2(360); --from geographies
536 l_terr_value_id NUMBER; --value id corresponding to postal code
537 l_org_id NUMBER;
538
539 l_g_terr_id NUMBER;
540
541
542 begin
543
544 --l_user_id := fnd_global.user_id;
545 -- for proxy user, a user_id is passed in
546 l_user_id := p_userid;
547
548
549 IF p_geo_terr_name IS NULL THEN
550 /*trim(p_postal_code) is null means the pc is to be removed */
554 RETURN;
551 fnd_message.set_name ('JTF', 'JTY_TTY_EMPTY_TERR_NAME');
552 x_msg_data := fnd_message.get();
553 fnd_message.set_name ('JTF', x_msg_data);
555 END IF;
556
557 --insert into tmp values('glb userid',l_user_id); commit;
558 select resource_id into rsc_id from jtf_rs_resource_extns
559 where user_id = l_user_id;
560
561 --Does this Postal Code belong to the current user, i.e., does the user have permission
562 --to assign this postal code to the territories he created?
563 begin
564 in_reg_terr :=1;
565 in_def_terr :=0;
566
567 --insert into tmp values(l_user_id,p_terrgroup); commit;
568 -- check if a regular terr the user working on has the postal code.
569 select terr.geo_territory_id into terr_id
570 from jtf_tty_geo_terr terr,
571 jtf_tty_geo_terr_values terrv,
572 jtf_tty_geo_terr_rsc rsc,
573 jtf_tty_geographies geog
574 where terr.geo_territory_id = terrv.geo_territory_id
575 and terrv.geo_id = geog.geo_id
576 and geog.postal_code = p_postal_code /* the PC is in the terr she works in */
577 and rsc.geo_territory_id = terr.geo_territory_id /* the terr she works in */
578 and rsc_id = rsc.resource_id /* who logged in*/
579 and rownum<2;
580 exception
581 when no_data_found then -- no postalcode - resource_id mapping found
582 in_reg_terr :=0;
583 end;
584
585 if in_reg_terr = 0 then -- check the default geo terr
586 -- start: the resource is not working in any default terr.
587 -- or the default terr does not have any postalcode.
588 FOR tgeo in CheckPCInDefTerr(rsc_id, p_postal_code) -- each grp_value entry
589 LOOP
590
591 if tgeo.exist>0 then in_def_terr:=1;
592 else in_def_terr:=0;
593 end if;
594
595
596 END LOOP;
597
598 end if;
599 if in_reg_terr=0 and in_def_terr =0 and trim(p_postal_code) is not null then
600 /*trim(p_postal_code) is null means the pc is to be removed */
601 fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_POSTAL_CODE');
602 x_msg_data := fnd_message.get();
603 fnd_message.set_name ('JTF', x_msg_data);
604 return;
605 end if;
606 /* Does the Geography territory attached to the postal codes is created by the current user?
607 check the ownership */
608 if trim(p_geo_terr_name) is not null and trim(p_geo_terr_name)<>' ' then
609 select count(terr.geo_territory_id) into i
610 from jtf_tty_geo_terr terr
611 where terr.owner_resource_id = rsc_id
612 and upper(terr.geo_terr_name) = upper(p_geo_terr_name);
613 -- and terr.parent_geo_terr_id = terr_id; removed (sgkumar) parent terr can be default terr
614
615
616 if i=0 then fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_TERR_NAME');
617 x_msg_data := fnd_message.get();
618 fnd_message.set_name ('JTF', x_msg_data);
619 return;
620 end if;
621 end if;
622
623
624 /* Does the Geography territory attached to the postal codes belong
625 to the correct parent territory (sgkumar)*/
626
627 if (trim(p_geo_terr_name) is not null and trim(p_geo_terr_name) <> ' ') then
628 select count(terr1.geo_territory_id) into i
629 from jtf_tty_geo_terr terr1, jtf_tty_geo_terr terr2
630 where terr1.geo_territory_id = terr2.parent_geo_terr_id
631 and upper(terr1.geo_terr_name) = upper(p_manager_terr_name)
632 and terr2.geo_terr_name = p_geo_terr_name;
633 -- and terr.parent_geo_terr_id = terr_id;
634
635 if i=0 then fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_TERR_NAME');
636 x_msg_data := fnd_message.get();
637 fnd_message.set_name ('JTF', x_msg_data);
638 return;
639 end if;
640 end if;
641
642 -- pass the validation, now do the update
643 -- check if the p_pc exists in the PCs the user owners.
644 -- no need check for unsigned because it wont exist?
645
646 select count(geog.postal_code) into found --geog.postal_code, terr.geo_terr_name
647 from jtf_tty_geo_terr terr,
648 jtf_tty_geo_terr_values terrv,
649 jtf_tty_geographies geog
650 where terr.owner_resource_id = rsc_id
651 and terr.geo_territory_id = terrv.geo_territory_id
652 and terrv.geo_id = geog.geo_id
653 and geog.postal_code = p_postal_code
654 and upper(terr.geo_terr_name) = upper(p_geo_terr_name);
655
656 if found=0 then
657 -- the p_geo_terr_value_id is from the old assignment but pc/terr_name is new
658 -- remove the old assignment, only happens in no-default terr
659 -- p_geo_terr_value_id can be null
660 /* delete from jtf_tty_geo_terr_values
661 where geo_terr_value_id = p_geo_terr_value_id;
662 */
663 BEGIN
664 /*remove recursively*/
665 l_geo_id :=0;
666 l_terr_id :=0;
667 select geo_id,geo_territory_id
668 into l_geo_id,l_terr_id
669 from jtf_tty_geo_terr_values
670 where geo_terr_value_id = p_geo_terr_value_id;
671 exception
672 when no_data_found then
673 null;
677
674 when others then
675 null;
676 END;
678 delete from jtf_tty_geo_terr_values gtv
679 where geo_id = l_geo_id
680 and geo_territory_id in (
681 select geo_territory_id
682 from jtf_tty_geo_terr
683 start with geo_territory_id = l_terr_id
684 connect by prior geo_territory_id=parent_geo_terr_id
685 );
686
687 if (trim(p_geo_terr_name) is not null) then -- insert
688 --insert into tmp values('enter',p_geo_terr_name); commit;
689 -- n: geo_terr_value_id
690 if trim(p_geo_terr_value_id) is null then
691 -- new geo_terr_value_id
692 select jtf_tty_geo_terr_values_s.nextval into n from dual;
693 else n:=trim(p_geo_terr_value_id);
694 end if;
695
696
697 -- dbms_output.put_line(' Postal code ******* is'||p_postal_code);
698
699 if trim(p_postal_code) is null then return; -- no need inserting new postal code
700 end if;
701
702 -- m: geo_id
703 select geo_id into m -- geo_id
704 from jtf_tty_geographies
705 where postal_code=p_postal_code;
706
707 begin
708 -- terr_id
709
710 -- dbms_output.put_line(' GEO TERR NAME ******* is'||p_geo_terr_name);
711 select geo_territory_id into terr_id --terr_id
712 from jtf_tty_geo_terr
713 where upper(geo_terr_name) = upper(p_geo_terr_name);
714 --and owner_resource_id = rsc_id; --10121
715 exception
716 when no_data_found then
717
718 fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_TERR_NAME');
719 x_msg_data := fnd_message.get();
720 fnd_message.set_name ('JTF', x_msg_data);
721 return;
722
723 when others then
724 fnd_message.set_name ('JTF', 'JTF_TTY_DUPLICATE_TERRITORY_NAME');
725 x_msg_data := fnd_message.get();
726 fnd_message.set_name ('JTF', x_msg_data);
727 return;
728 end;
729
730
731 insert into jtf_tty_geo_terr_values (geo_terr_value_id,object_version_number,
732 geo_territory_id,geo_id, created_by,
733 creation_date,last_updated_by,
734 last_update_date,last_update_login)
735 values(n,1,terr_id, m, rsc_id, sysdate, rsc_id, sysdate, rsc_id);
736
737
738
739 l_g_terr_id := terr_id;
740
741
742
743
744 /* Vbghosh:
745 Get the terr_id from the geo_terr_id
746 */
747
748
749 /* get the terr_id from the corresponding geo_terr_id*/
750 BEGIN
751
752 --dbms_output.put_line(' GEO TERR ID ******* is'||terr_id);
753
754 SELECT
755 terr_id ,
756 org_id
757 INTO l_terr_id_new
758 , l_org_id
759 FROM jtf_terr_all
760 WHERE geo_territory_id = l_g_terr_id ; --its geo_terr_id
761
762 --dbms_output.put_line(' after selecting terr_id is'||terr_id);
763
764 EXCEPTION
765 WHEN NO_DATA_FOUND THEN
766 RAISE;
767 WHEN OTHERS THEN
768 --dbms_output.put_line('ERROR '||SQLERRM);
769 RAISE;
770
771 END; --getting terr_id
772
773 BEGIN --get the terr_qual_id if null then insert
774 SELECT
775 c.terr_qual_id
776 INTO
777 l_terr_qual_id
778 FROM
779 jtf_terr_all a
780 , jtf_tty_geo_terr b
781 , jtf_terr_qual_all c
782 WHERE
783 b.geo_territory_id = a.geo_Territory_id
784 AND b.geo_territory_id = l_g_terr_id --its geo_terr_id
785 AND c.terr_id = a.terr_id
786 AND c.qual_usg_id = -1007;
787
788 --dbms_output.put_line(' terr_qual_id ID ******* is'|| l_terr_qual_id);
789
790 EXCEPTION
791 WHEN NO_DATA_FOUND THEN
792 --dbms_output.put_line('ERROR '||SQLERRM);
793 NULL;
794 WHEN OTHERS THEN
795 --dbms_output.put_line('ERROR '||SQLERRM);
796 RAISE;
797 END; -- end checking create or update
798
799 IF l_terr_qual_id IS NULL THEN -- need to create using sequence
800 --dbms_output.put_line(' terr_qual_id ID ******* is NULL');
801 /* insert in terr_qual_all table using sequence*/
802 SELECT JTF_TERR_QUAL_S.NEXTVAL
803 INTO l_terr_qual_id
804 FROM DUAL;
805 --dbms_output.put_line(' AFter select ing from seq qual id');
806
807 INSERT INTO jtf_terr_qual_all
808 ( TERR_QUAL_ID
809 , LAST_UPDATE_DATE
810 , LAST_UPDATED_BY
811 , CREATION_DATE
812 , CREATED_BY
813 , LAST_UPDATE_LOGIN
814 , TERR_ID
815 , QUAL_USG_ID
816 , OVERLAP_ALLOWED_FLAG
817 , ORG_ID )
818 SELECT
819 l_terr_qual_id
820 , SYSDATE
821 , LAST_UPDATED_BY
822 , SYSDATE
823 , CREATED_BY
824 , LAST_UPDATE_LOGIN
825 , l_terr_id_new
826 , -1007
827 , 'Y'
831
828 , l_org_id -- ORgId
829 FROM jtf_tty_geo_terr
830 WHERE geo_territory_id = terr_id;
832 --dbms_output.put_line(' AFterinserting qual id');
833
834
835 END IF;
836
837
838
839
840 /* vbghosh: if parameter p_geo_terr_value_id or l_terr_value_id is null
841 then it is a new create and a new value has to be inseted in terr_values_all
842 table otherwise its a updated
843 */
844
845 IF p_geo_terr_value_id IS NULL THEN
846 --dbms_output.put_line(' terr_value id is null ');
847
848
849
850 /* Insert a new row in terr_values_all table , using the geo_terr_value_id "n"*/
851 INSERT INTO jtf_terr_values_all
852 (
853 TERR_VALUE_ID
854 ,LAST_UPDATED_BY
855 ,LAST_UPDATE_DATE
856 ,CREATED_BY
857 ,CREATION_DATE
858 ,LAST_UPDATE_LOGIN
859 ,TERR_QUAL_ID
860 ,COMPARISON_OPERATOR
861 ,ID_USED_FLAG
862 ,ORG_ID
863 ,LOW_VALUE_CHAR -- TODO need to check
864 ,SELF_SERVICE_TERR_VALUE_ID
865 )
866 SELECT
867 JTF_TERR_VALUES_S.NEXTVAL
868 ,LAST_UPDATED_BY
869 ,SYSDATE
870 ,CREATED_BY
871 ,SYSDATE
872 ,LAST_UPDATE_LOGIN
873 ,l_terr_qual_id
874 ,'='
875 ,'N'
876 , l_org_id
877 , p_postal_code
878 , n -- geo_terr_value_id
879 FROM jtf_tty_geo_terr
880 WHERE geo_territory_id = terr_id;
881
882 --dbms_output.put_line(' Inserting terr_value id ');
883 ELSE
884 /* get the corresponding self_service_value_id from the terr_value_table
885 delete it and then insert it
886 */
887 BEGIN
888 --dbms_output.put_line(' before deletin terr_value_id ');
889 DELETE FROM jtf_terr_values_all
890 WHERE SELF_SERVICE_TERR_VALUE_ID = to_number(p_geo_terr_value_id);
891 --dbms_output.put_line(' After deletin terr_value_id and before insertin');
892 --dbms_output.put_line(' After inserting terr_value_id ');
893
894 EXCEPTION
895 WHEN NO_DATA_FOUND THEN
896 NULL;
897 WHEN OTHERS THEN
898 RAISE;
899 END;
900
901 INSERT INTO jtf_terr_values_all
902 (
903 TERR_VALUE_ID
904 ,LAST_UPDATED_BY
905 ,LAST_UPDATE_DATE
906 ,CREATED_BY
907 ,CREATION_DATE
908 ,LAST_UPDATE_LOGIN
909 ,TERR_QUAL_ID
910 ,COMPARISON_OPERATOR
911 ,ID_USED_FLAG
912 ,ORG_ID
913 ,LOW_VALUE_CHAR -- TODO need to check
914 ,SELF_SERVICE_TERR_VALUE_ID
915 )
916 SELECT
917 JTF_TERR_VALUES_S.NEXTVAL
918 ,LAST_UPDATED_BY
919 ,SYSDATE
920 ,CREATED_BY
921 ,SYSDATE
922 ,LAST_UPDATE_LOGIN
923 ,l_terr_qual_id
924 ,'='
925 ,'N'
926 , l_org_id
927 , p_postal_code
928 , to_number(p_geo_terr_value_id) -- geo_terr_value_id
929 FROM jtf_tty_geo_terr
930 WHERE geo_territory_id = terr_id;
931
932
933
934
935 END IF;
936
937
938
939 /* ACHANDA: Inserting values to jtf_tty_named_acct_changes table for GTP
940 to do an incremental and Total Mode */
941
942 select jtf_tty_named_acct_changes_s.nextval
943 into l_change_id
944 from sys.dual;
945
946 insert into jtf_tty_named_acct_changes
947 ( NAMED_ACCT_CHANGE_ID
948 , OBJECT_VERSION_NUMBER
949 , OBJECT_TYPE
950 , OBJECT_ID
951 , CHANGE_TYPE
952 , FROM_WHERE
953 , CREATED_BY
954 , CREATION_DATE
955 , LAST_UPDATED_BY
956 , LAST_UPDATE_DATE
957 , LAST_UPDATE_LOGIN
958 )
959 VALUES (
960 l_change_id
961 , 1
962 , 'GT'
963 , terr_id
964 , 'UPDATE'
965 , 'Update Mapping'
966 , rsc_id
967 , sysdate
968 , rsc_id
969 , sysdate
970 , rsc_id
971 );
972
973 end if;
974 end if; -- of found=0
975
976 commit;
977
978 exception
979 when others then
980 fnd_message.set_name ('JTF', 'JTF_TTY_UNEXPECTED_ERROR');
981 x_msg_data := fnd_message.get();
982 fnd_message.set_name ('JTF', x_msg_data);
983 end UPDATE_GEO_TERR;
984
985
986 END JTF_TTY_GEO_WEBADI_INT_PKG;
[Home] [Help]