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 253: jtf_tty_geo_terr terr,

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

Line 254: jtf_tty_geo_terr_rsc rsc,

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

Line 256: jtf_tty_geo_terr_values tv

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

Line 270: from jtf_tty_geo_terr terr,

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

Line 271: jtf_tty_geo_terr_values tv

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

Line 382: from jtf_tty_geo_terr

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:

Line 412: from jtf_tty_geo_terr terr,

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
416: where rsc_id = rsc.resource_id

Line 413: jtf_tty_geo_terr_rsc rsc,

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
416: where rsc_id = rsc.resource_id
417: and rsc.geo_territory_id = terr.geo_territory_id

Line 431: jtf_tty_geo_terr terr,

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

Line 432: jtf_tty_geo_terr_rsc rsc,

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

Line 453: from jtf_tty_geo_terr terr,

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

Line 454: jtf_tty_geo_terr_rsc rsc,

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

Line 570: from jtf_tty_geo_terr terr,

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

Line 571: jtf_tty_geo_terr_values terrv,

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

Line 572: jtf_tty_geo_terr_rsc rsc,

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

Line 610: from jtf_tty_geo_terr terr

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:

Line 629: from jtf_tty_geo_terr terr1, jtf_tty_geo_terr terr2

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;

Line 647: from jtf_tty_geo_terr terr,

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

Line 648: jtf_tty_geo_terr_values terrv,

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

Line 660: /* delete from jtf_tty_geo_terr_values

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

Line 669: from jtf_tty_geo_terr_values

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;

Line 678: delete from jtf_tty_geo_terr_values gtv

674: when others then
675: null;
676: END;
677:
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

Line 682: from jtf_tty_geo_terr

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:

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

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:

Line 712: from jtf_tty_geo_terr

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

Line 731: insert into jtf_tty_geo_terr_values (geo_terr_value_id,object_version_number,

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

Line 780: , jtf_tty_geo_terr b

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

Line 829: FROM jtf_tty_geo_terr

825: , l_terr_id_new
826: , -1007
827: , 'Y'
828: , l_org_id -- ORgId
829: FROM jtf_tty_geo_terr
830: WHERE geo_territory_id = terr_id;
831:
832: --dbms_output.put_line(' AFterinserting qual id');
833:

Line 879: FROM jtf_tty_geo_terr

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

Line 929: FROM jtf_tty_geo_terr

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: