[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select tg.terr_group_name territory_group,
pterr.geo_terr_name manager_terr_name,
g.country_code country,
g.State_code state_province,
g.City_code city,
g.postal_code postal_code,
terr.geo_terr_name geo_terr_name,
terrv.geo_terr_value_id tv_id
from jtf_tty_geographies g,
jtf_tty_geo_terr terr,
jtf_tty_geo_terr pterr,
jtf_tty_geo_terr_values terrv,
jtf_tty_terr_groups tg
where terrv.geo_territory_id = terr_id
and terrv.geo_territory_id = terr.geo_territory_id
and terr.parent_geo_terr_id = pterr.geo_territory_id(+)
and terrv.geo_id = g.geo_id
and terr.terr_group_id = tg.terr_group_id;
select grpv.comparison_operator, grpv.geo_id_from, grpv.geo_id_to,
tg.terr_group_name territory_group,
pterr.geo_terr_name manager_terr_name,
terr.geo_terr_name geo_terr_name
from jtf_tty_geo_grp_values grpv,
jtf_tty_terr_groups tg,
jtf_tty_geo_terr terr,
jtf_tty_geo_terr pterr
where terr.geo_territory_id =terr_id
and terr.terr_group_id = tg.terr_group_id
and terr.terr_group_id = grpv.terr_group_id
and terr.parent_geo_terr_id = pterr.geo_territory_id(+);
select *
from(
/* postal code = */
select tg.terr_group_name territory_group,
terr.geo_terr_name manager_terr_name, /* the parent terr name */
g.country_code country,
g.State_code state_province,
g.City_code city,
g.postal_code postal_code,
null terr_name,
g.geo_id geo_id
from jtf_tty_geo_grp_values grpv,
jtf_tty_terr_groups tg,
jtf_tty_geo_terr terr,
jtf_tty_geo_terr_rsc rsc,
jtf_tty_geographies g --postal_code level
where
rsc.resource_id = l_rsc_id -- user works in this geo terr
and rsc.geo_territory_id = terr.geo_territory_id
and terr.terr_group_id = tg.terr_group_id
and terr.terr_group_id = grpv.terr_group_id
and terr.owner_resource_id < 0
and terr.parent_geo_terr_id < 0 -- default terr
and SYSDATE BETWEEN tg.active_from_date AND NVL(tg.active_to_date, SYSDATE+1)
and grpv.geo_type = 'POSTAL_CODE'
and grpv.comparison_operator = '='
and g.geo_id = grpv.geo_id_from
and g.geo_type = 'POSTAL_CODE'
union
/* postal code range*/
select tg.terr_group_name territory_group,
terr.geo_terr_name manager_terr_name, /* the parent terr name */
g.country_code country,
g.State_code state_province,
g.City_code city,
g.postal_code postal_code,
null terr_name,
g.geo_id geo_id
from jtf_tty_geo_grp_values grpv,
jtf_tty_terr_groups tg,
jtf_tty_geo_terr terr,
jtf_tty_geo_terr_rsc rsc,
jtf_tty_geographies g, --postal_code level
jtf_tty_geographies g1,
jtf_tty_geographies g2
where
rsc.resource_id = l_rsc_id -- user works in this geo terr
and rsc.geo_territory_id = terr.geo_territory_id
and terr.terr_group_id = tg.terr_group_id
and terr.terr_group_id = grpv.terr_group_id
and terr.owner_resource_id < 0
and terr.parent_geo_terr_id < 0 -- default terr
and SYSDATE BETWEEN tg.active_from_date AND NVL(tg.active_to_date, SYSDATE+1)
and grpv.geo_type = 'POSTAL_CODE'
and grpv.comparison_operator = 'BETWEEN'
and g.geo_type = 'POSTAL_CODE'
AND g1.geo_id = grpv.geo_id_from
AND g2.geo_id = grpv.geo_id_to
AND g.geo_name BETWEEN g1.geo_name and g2.geo_name
union
select tg.terr_group_name territory_group,
terr.geo_terr_name manager_terr_name, /* the parent terr name */
g.country_code country,
g.State_code state_province,
g.City_code city,
g.postal_code postal_code,
null terr_name,
g.geo_id geo_id
from jtf_tty_geo_grp_values grpv,
jtf_tty_terr_groups tg,
jtf_tty_geo_terr terr,
jtf_tty_geo_terr_rsc rsc,
jtf_tty_geographies g,
jtf_tty_geographies g1
where
rsc.resource_id = l_rsc_id -- user works in this geo terr
and rsc.geo_territory_id = terr.geo_territory_id
and terr.terr_group_id = tg.terr_group_id
and terr.terr_group_id = grpv.terr_group_id
and terr.owner_resource_id < 0
and terr.parent_geo_terr_id < 0 -- default terr
and SYSDATE BETWEEN tg.active_from_date AND NVL(tg.active_to_date, SYSDATE+1)
and (
(
grpv.geo_type = 'STATE'
and g1.geo_id = grpv.geo_id_from
and g.STATE_CODE = g1.state_Code
and g.country_code = g1.country_Code
and g.geo_type = 'POSTAL_CODE'
)
or
( grpv.geo_type = 'CITY'
AND g.geo_type = 'POSTAL_CODE'
AND g.country_code = g1.country_code
AND (
(g.state_code = g1.state_code AND g1.province_code is null)
or
(g1.province_code = g.province_code AND g1.state_code is null)
)
AND (g1.county_code is null or g.county_code = g1.county_code)
AND g.city_code = g1.city_code
AND grpv.geo_id_from = g1.geo_id
)
or
(
grpv.geo_type = 'COUNTRY'
AND grpv.geo_id_from = g1.geo_id
AND g.geo_type = 'POSTAL_CODE'
AND g.country_code = g1.country_code
)
or
(
grpv.geo_type = 'PROVINCE'
AND grpv.geo_id_from = g1.geo_id
AND g.geo_type = 'POSTAL_CODE'
AND g.country_code = g1.country_code
AND g.province_code = g1.province_code
)
or
(
grpv.geo_type = 'COUNTY'
AND grpv.geo_id_from = g1.geo_id
AND g.geo_type = 'POSTAL_CODE'
AND g.country_code = g1.country_code
AND g.county_code = g1.county_code
)
)
union
select tg.terr_group_name territory_group,
terr.geo_terr_name manager_terr_name, /* the parent terr name */
g.country_code country,
g.State_code state_province,
g.City_code city,
g.postal_code postal_code,
null terr_name,
g.geo_id geo_id
from jtf_tty_terr_groups tg,
jtf_tty_geo_terr terr,
jtf_tty_geo_terr_rsc rsc,
jtf_tty_geographies g,
jtf_tty_geo_terr_values tv
where
rsc.resource_id = l_rsc_id
and rsc.geo_territory_id = terr.geo_territory_id
and terr.terr_group_id = tg.terr_group_id
and terr.owner_resource_id >= 0
and terr.parent_geo_terr_id >= 0 -- not default terr
and tv.geo_territory_id = terr.geo_territory_id
and g.geo_id = tv.geo_id
and SYSDATE BETWEEN tg.active_from_date AND NVL(tg.active_to_date, SYSDATE+1)
)
where geo_id not in -- the terr the user owners
(
select tv.geo_id geo_id
from jtf_tty_geo_terr terr,
jtf_tty_geo_terr_values tv
where
terr.owner_resource_id = l_rsc_id
and tv.geo_territory_id = terr.geo_territory_id
);
delete from JTF_TTY_GEO_WEBADI_INTERFACE
where user_id = p_userid
and sysdate - creation_date >2;
select jtf_tty_geo_int_s.nextval into SEQ from dual;
select count(*) into id from JTF_TTY_GEO_WEBADI_INTERFACE;
else select max(id)+1 into id from JTF_TTY_GEO_WEBADI_INTERFACE;
select resource_id into l_rsc_id from jtf_rs_resource_extns
where user_id = p_userid;
INSERT INTO JTF_TTY_GEO_WEBADI_INTERFACE(jtf_tty_webadi_int_id, object_version_number,
user_id, user_sequence, territory_group,
manager_terr_name, country, state_province,
city, postal_code, geo_terr_name,geo_terr_value_id,
created_by,creation_date, last_updated_by,
last_update_date, last_update_login )
VALUES(id, 1, user_id, SEQ, pc.territory_group,
pc.manager_terr_name, pc.country, pc.state_province,
pc.city, pc.postal_code, null, null,
user_id, sysdate,user_id, sysdate,user_id);
INSERT INTO JTF_TTY_GEO_WEBADI_INTERFACE(jtf_tty_webadi_int_id, object_version_number,
user_id, user_sequence, territory_group,
manager_terr_name, country, state_province,
city, postal_code, geo_terr_name, geo_terr_value_id,
created_by, creation_date, last_updated_by,
last_update_date, last_update_login )
VALUES(id, 1, user_id, SEQ, pc.territory_group,
pc.manager_terr_name, pc.country, pc.state_province,
pc.city, pc.postal_code, pc.geo_terr_name,pc.tv_id,user_id,
sysdate,user_id, sysdate,user_id);
select count(*) into l_num
from jtf_tty_geo_terr
where geo_territory_id = terr_id
and owner_resource_id<0
and parent_geo_terr_id<0;
procedure UPDATE_GEO_TERR ( --p_user_sequence in varchar2,
p_terrgroup in varchar2,
p_manager_terr_name in varchar2,
p_country in varchar2,
p_state_province in varchar2,
p_city in varchar2,
p_postal_code in varchar2,
p_geo_terr_name in varchar2,
p_geo_terr_value_id in varchar2,
p_userid in varchar2
) IS
-- Check if the PC is in default terr the user works in
CURSOR CheckPCInDefTerr(rsc_id IN NUMBER, p_pc varchar2) IS
select count(g.postal_code) exist --, terr.geo_territory_id terr_id
--grpv.comparison_operator, grpv.geo_type, grpv.geo_id_from, geo_id_to, terr.geo_territory_id terr_id
from jtf_tty_geo_terr terr,
jtf_tty_geo_terr_rsc rsc,
jtf_tty_geo_grp_values grpv,
jtf_tty_geographies g
where rsc_id = rsc.resource_id
and rsc.geo_territory_id = terr.geo_territory_id
and terr.owner_resource_id <0
and terr.parent_geo_terr_id<0
and terr.terr_group_id = grpv.terr_group_id
and grpv.geo_type = 'POSTAL_CODE'
and grpv.comparison_operator = '='
and g.geo_id = grpv.geo_id_from
and g.geo_type = 'POSTAL_CODE'
and g.postal_code = p_pc
union
select count(g.postal_code) exist /* postal code range*/
from jtf_tty_geo_grp_values grpv,
jtf_tty_terr_groups tg,
jtf_tty_geo_terr terr,
jtf_tty_geo_terr_rsc rsc,
jtf_tty_geographies g, --postal_code level
jtf_tty_geographies g1,
jtf_tty_geographies g2
where
rsc.resource_id = rsc_id -- user works in this geo terr
and rsc.geo_territory_id = terr.geo_territory_id
and terr.terr_group_id = tg.terr_group_id
and terr.terr_group_id = grpv.terr_group_id
and terr.owner_resource_id < 0
and terr.parent_geo_terr_id < 0 -- default terr
and SYSDATE BETWEEN tg.active_from_date AND NVL(tg.active_to_date, SYSDATE+1)
and grpv.geo_type = 'POSTAL_CODE'
and grpv.comparison_operator = 'BETWEEN'
and g.geo_type = 'POSTAL_CODE'
and g.postal_code = p_pc
AND g1.geo_id = grpv.geo_id_from
AND g2.geo_id = grpv.geo_id_to
AND g.geo_name BETWEEN g1.geo_name and g2.geo_name
union
select count(g.postal_code) exist
from jtf_tty_geo_terr terr,
jtf_tty_geo_terr_rsc rsc,
jtf_tty_geo_grp_values grpv,
jtf_tty_geographies g,
jtf_tty_geographies g1
where rsc_id = rsc.resource_id
and rsc.geo_territory_id = terr.geo_territory_id
and terr.owner_resource_id <0
and terr.parent_geo_terr_id<0
and terr.terr_group_id = grpv.terr_group_id
and (
(
grpv.geo_type = 'STATE'
and g1.geo_id = grpv.geo_id_from
and g.STATE_CODE = g1.state_Code
and g.country_code = g1.country_Code
and g.geo_type = 'POSTAL_CODE'
and g.postal_code = p_pc
)
or
(
grpv.geo_type = 'COUNTY'
and g1.geo_id = grpv.geo_id_from
and g.county_code = g1.county_code
and g.country_code = g1.country_Code
and g.geo_type = 'POSTAL_CODE'
and g.postal_code = p_pc
)
or
( grpv.geo_type = 'CITY'
AND g.geo_type = 'POSTAL_CODE'
AND g.country_code = g1.country_code
AND (
(g.state_code = g1.state_code AND g1.province_code is null)
or
(g1.province_code = g.province_code AND g1.state_code is null)
)
AND (g1.county_code is null or g.county_code = g1.county_code)
AND g.city_code = g1.city_code
AND grpv.geo_id_from = g1.geo_id
and g.postal_code = p_pc
)
or
(
grpv.geo_type = 'COUNTRY'
AND grpv.geo_id_from = g1.geo_id
AND g.geo_type = 'POSTAL_CODE'
AND g.country_code = g1.country_code
and g.postal_code = p_pc
)
or
(
grpv.geo_type = 'PROVINCE'
AND grpv.geo_id_from = g1.geo_id
AND g.geo_type = 'POSTAL_CODE'
AND g.country_code = g1.country_code
AND g.province_code = g1.province_code
and g.postal_code = p_pc
)
);
select resource_id into rsc_id from jtf_rs_resource_extns
where user_id = l_user_id;
select terr.geo_territory_id into terr_id
from jtf_tty_geo_terr terr,
jtf_tty_geo_terr_values terrv,
jtf_tty_geo_terr_rsc rsc,
jtf_tty_geographies geog
where terr.geo_territory_id = terrv.geo_territory_id
and terrv.geo_id = geog.geo_id
and geog.postal_code = p_postal_code /* the PC is in the terr she works in */
and rsc.geo_territory_id = terr.geo_territory_id /* the terr she works in */
and rsc_id = rsc.resource_id /* who logged in*/
and rownum<2;
select count(terr.geo_territory_id) into i
from jtf_tty_geo_terr terr
where terr.owner_resource_id = rsc_id
and upper(terr.geo_terr_name) = upper(p_geo_terr_name);
select count(terr1.geo_territory_id) into i
from jtf_tty_geo_terr terr1, jtf_tty_geo_terr terr2
where terr1.geo_territory_id = terr2.parent_geo_terr_id
and upper(terr1.geo_terr_name) = upper(p_manager_terr_name)
and terr2.geo_terr_name = p_geo_terr_name;
select count(geog.postal_code) into found --geog.postal_code, terr.geo_terr_name
from jtf_tty_geo_terr terr,
jtf_tty_geo_terr_values terrv,
jtf_tty_geographies geog
where terr.owner_resource_id = rsc_id
and terr.geo_territory_id = terrv.geo_territory_id
and terrv.geo_id = geog.geo_id
and geog.postal_code = p_postal_code
and upper(terr.geo_terr_name) = upper(p_geo_terr_name);
/* delete from jtf_tty_geo_terr_values
where geo_terr_value_id = p_geo_terr_value_id;
select geo_id,geo_territory_id
into l_geo_id,l_terr_id
from jtf_tty_geo_terr_values
where geo_terr_value_id = p_geo_terr_value_id;
delete from jtf_tty_geo_terr_values gtv
where geo_id = l_geo_id
and geo_territory_id in (
select geo_territory_id
from jtf_tty_geo_terr
start with geo_territory_id = l_terr_id
connect by prior geo_territory_id=parent_geo_terr_id
);
if (trim(p_geo_terr_name) is not null) then -- insert
--insert into tmp values('enter',p_geo_terr_name); commit;
select jtf_tty_geo_terr_values_s.nextval into n from dual;
if trim(p_postal_code) is null then return; -- no need inserting new postal code
select geo_id into m -- geo_id
from jtf_tty_geographies
where postal_code=p_postal_code;
select geo_territory_id into terr_id --terr_id
from jtf_tty_geo_terr
where upper(geo_terr_name) = upper(p_geo_terr_name);
insert into jtf_tty_geo_terr_values (geo_terr_value_id,object_version_number,
geo_territory_id,geo_id, created_by,
creation_date,last_updated_by,
last_update_date,last_update_login)
values(n,1,terr_id, m, rsc_id, sysdate, rsc_id, sysdate, rsc_id);
SELECT
terr_id ,
org_id
INTO l_terr_id_new
, l_org_id
FROM jtf_terr_all
WHERE geo_territory_id = l_g_terr_id ; --its geo_terr_id
--dbms_output.put_line(' after selecting terr_id is'||terr_id);
BEGIN --get the terr_qual_id if null then insert
SELECT
c.terr_qual_id
INTO
l_terr_qual_id
FROM
jtf_terr_all a
, jtf_tty_geo_terr b
, jtf_terr_qual_all c
WHERE
b.geo_territory_id = a.geo_Territory_id
AND b.geo_territory_id = l_g_terr_id --its geo_terr_id
AND c.terr_id = a.terr_id
AND c.qual_usg_id = -1007;
END; -- end checking create or update
/* insert in terr_qual_all table using sequence*/
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
--dbms_output.put_line(' AFter select ing from seq qual id');
INSERT INTO jtf_terr_qual_all
( TERR_QUAL_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, TERR_ID
, QUAL_USG_ID
, OVERLAP_ALLOWED_FLAG
, ORG_ID )
SELECT
l_terr_qual_id
, SYSDATE
, LAST_UPDATED_BY
, SYSDATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, l_terr_id_new
, -1007
, 'Y'
, l_org_id -- ORgId
FROM jtf_tty_geo_terr
WHERE geo_territory_id = terr_id;
--dbms_output.put_line(' AFterinserting qual id');
table otherwise its a updated
*/
IF p_geo_terr_value_id IS NULL THEN
--dbms_output.put_line(' terr_value id is null ');
/* Insert a new row in terr_values_all table , using the geo_terr_value_id "n"*/
INSERT INTO jtf_terr_values_all
(
TERR_VALUE_ID
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,TERR_QUAL_ID
,COMPARISON_OPERATOR
,ID_USED_FLAG
,ORG_ID
,LOW_VALUE_CHAR -- TODO need to check
,SELF_SERVICE_TERR_VALUE_ID
)
SELECT
JTF_TERR_VALUES_S.NEXTVAL
,LAST_UPDATED_BY
,SYSDATE
,CREATED_BY
,SYSDATE
,LAST_UPDATE_LOGIN
,l_terr_qual_id
,'='
,'N'
, l_org_id
, p_postal_code
, n -- geo_terr_value_id
FROM jtf_tty_geo_terr
WHERE geo_territory_id = terr_id;
delete it and then insert it
*/
BEGIN
--dbms_output.put_line(' before deletin terr_value_id ');
DELETE FROM jtf_terr_values_all
WHERE SELF_SERVICE_TERR_VALUE_ID = to_number(p_geo_terr_value_id);
--dbms_output.put_line(' After deletin terr_value_id and before insertin');
--dbms_output.put_line(' After inserting terr_value_id ');
INSERT INTO jtf_terr_values_all
(
TERR_VALUE_ID
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,TERR_QUAL_ID
,COMPARISON_OPERATOR
,ID_USED_FLAG
,ORG_ID
,LOW_VALUE_CHAR -- TODO need to check
,SELF_SERVICE_TERR_VALUE_ID
)
SELECT
JTF_TERR_VALUES_S.NEXTVAL
,LAST_UPDATED_BY
,SYSDATE
,CREATED_BY
,SYSDATE
,LAST_UPDATE_LOGIN
,l_terr_qual_id
,'='
,'N'
, l_org_id
, p_postal_code
, to_number(p_geo_terr_value_id) -- geo_terr_value_id
FROM jtf_tty_geo_terr
WHERE geo_territory_id = terr_id;
/* ACHANDA: Inserting values to jtf_tty_named_acct_changes table for GTP
to do an incremental and Total Mode */
select jtf_tty_named_acct_changes_s.nextval
into l_change_id
from sys.dual;
insert into jtf_tty_named_acct_changes
( NAMED_ACCT_CHANGE_ID
, OBJECT_VERSION_NUMBER
, OBJECT_TYPE
, OBJECT_ID
, CHANGE_TYPE
, FROM_WHERE
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
)
VALUES (
l_change_id
, 1
, 'GT'
, terr_id
, 'UPDATE'
, 'Update Mapping'
, rsc_id
, sysdate
, rsc_id
, sysdate
, rsc_id
);
end UPDATE_GEO_TERR;