APPS.JTF_TTY_GEO_WEBADI_INT_PKG dependencies on JTF_TTY_GEO_TERR

Line 87: jtf_tty_geo_terr terr,

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

Line 88: jtf_tty_geo_terr pterr,

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

Line 89: jtf_tty_geo_terr_values terrv,

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(+)

Line 107: jtf_tty_geo_terr terr,

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

Line 108: jtf_tty_geo_terr pterr

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(+);

Line 129: jtf_tty_geo_terr terr,

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

Line 130: jtf_tty_geo_terr_rsc rsc,

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

Line 156: jtf_tty_geo_terr terr,

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

Line 157: jtf_tty_geo_terr_rsc rsc,

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

Line 186: jtf_tty_geo_terr terr,

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

Line 187: jtf_tty_geo_terr_rsc rsc,

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

Line 245: jtf_tty_geo_terr terr,

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

Line 246: jtf_tty_geo_terr_rsc rsc,

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

Line 248: jtf_tty_geo_terr_values tv

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

Line 262: from jtf_tty_geo_terr terr,

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

Line 263: jtf_tty_geo_terr_values tv

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: );

Line 374: from jtf_tty_geo_terr

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:

Line 404: from jtf_tty_geo_terr terr,

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

Line 405: jtf_tty_geo_terr_rsc rsc,

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

Line 423: jtf_tty_geo_terr terr,

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

Line 424: jtf_tty_geo_terr_rsc rsc,

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

Line 445: from jtf_tty_geo_terr terr,

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

Line 446: jtf_tty_geo_terr_rsc rsc,

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

Line 550: from jtf_tty_geo_terr terr,

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

Line 551: jtf_tty_geo_terr_values terrv,

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

Line 552: jtf_tty_geo_terr_rsc rsc,

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 */

Line 590: from jtf_tty_geo_terr terr

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:

Line 609: from jtf_tty_geo_terr terr1, jtf_tty_geo_terr terr2

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;

Line 627: from jtf_tty_geo_terr terr,

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

Line 628: jtf_tty_geo_terr_values terrv,

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

Line 640: /* delete from jtf_tty_geo_terr_values

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
641: where geo_terr_value_id = p_geo_terr_value_id;
642: */
643: BEGIN
644: /*remove recursively*/

Line 649: from jtf_tty_geo_terr_values

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;

Line 658: delete from jtf_tty_geo_terr_values gtv

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

Line 662: from jtf_tty_geo_terr

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:

Line 672: select jtf_tty_geo_terr_values_s.nextval into n from dual;

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:

Line 692: from jtf_tty_geo_terr

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

Line 711: insert into jtf_tty_geo_terr_values (geo_terr_value_id,object_version_number,

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);

Line 760: , jtf_tty_geo_terr b

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

Line 809: FROM jtf_tty_geo_terr

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:

Line 859: FROM jtf_tty_geo_terr

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

Line 909: FROM jtf_tty_geo_terr

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: