[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* (1) START: DELETE ALL EXISTING NAMED ACCOUNT TERRITORIES */
PROCEDURE cleanup_na_territories ( p_mode VARCHAR2 )
IS
/* get all the Territories to DELETE */
CURSOR delterr IS
SELECT terr_id
from jtf_terr_all
where terr_group_flag = 'Y';
DELETE FROM jtf_terr_values_all jtv
WHERE jtv.terr_qual_id IN
( SELECT jtq.terr_qual_id
FROM jtf_terr_qual_all jtq, jtf_terr_all jt
WHERE jtq.terr_id = jt.terr_id
AND jt.terr_group_flag = 'Y' );
DELETE from JTF_TERR_QUAL_ALL jtq
WHERE jtq.terr_id IN
( SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE jt.terr_group_flag = 'Y' );
DELETE from JTF_TERR_QTYPE_USGS_ALL jtqu
WHERE jtqu.terr_id IN
( SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE jt.terr_group_flag = 'Y' );
DELETE from JTF_TERR_USGS_ALL jtu
WHERE jtu.terr_id IN
( SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE jt.terr_group_flag = 'Y' );
DELETE from JTF_TERR_RSC_ACCESS_ALL jtra
WHERE jtra.terr_rsc_id IN
( SELECT jtr.terr_rsc_id
FROM jtf_terr_rsc_all jtr, jtf_terr_all jt
WHERE jtr.terr_id = jt.terr_id
AND jt.terr_group_flag = 'Y' );
DELETE from JTF_TERR_RSC_ALL jtr
WHERE jtr.terr_id IN
( SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE jt.terr_group_flag = 'Y' );
DELETE from JTF_TERR_ALL jt
WHERE jt.terr_id IN
( SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE jt.terr_group_flag = 'Y' );
/* (1) END: DELETE ALL EXISTING NAMED ACCOUNT TERRITORIES */
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
l_row_inserted NUMBER := 0;
SELECT A.TERR_GROUP_ID
, A.TERR_GROUP_NAME
, A.RANK
, A.ACTIVE_FROM_DATE
, A.ACTIVE_TO_DATE
, A.PARENT_TERR_ID
, A.MATCHING_RULE_CODE
, A.CREATED_BY
, A.CREATION_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_DATE
, A.LAST_UPDATE_LOGIN
, A.Catch_all_resource_id
, A.catch_all_resource_type
, A.generate_catchall_flag
, A.NUM_WINNERS /* JDOCHERT: 07/29/03: BUG#3072230 */
, A.SELF_SERVICE_TYPE
, B.ORG_ID
FROM JTF_TTY_TERR_GROUPS A
, JTF_TERR_ALL B
WHERE A.parent_terr_id = b.terr_id
AND ( a.active_to_date >= SYSDATE OR a.active_to_date IS NULL )
AND a.active_from_date <= SYSDATE;
SELECT ra.access_type
FROM
jtf_tty_named_acct_rsc nar
, jtf_tty_terr_grp_accts tga
, jtf_tty_terr_grp_roles tgr
, jtf_tty_role_access ra
WHERE tga.terr_group_account_id = nar.terr_group_account_id
AND nar.terr_group_account_id = LP_terr_group_account_id
AND nar.rsc_role_code = tgr.role_code
AND ra.terr_group_role_id = tgr.terr_group_role_id
AND ra.access_type IN ('ACCOUNT')
UNION
SELECT ra.access_type
FROM
jtf_tty_named_acct_rsc nar
, jtf_tty_terr_grp_accts tga
, jtf_tty_terr_grp_roles tgr
, jtf_tty_role_access ra
WHERE tga.terr_group_account_id = nar.terr_group_account_id
AND nar.terr_group_account_id = LP_terr_group_account_id
AND nar.rsc_role_code = tgr.role_code
AND ra.terr_group_role_id = tgr.terr_group_role_id
AND NOT EXISTS (
SELECT NULL
FROM jtf_tty_role_prod_int rpi
WHERE rpi.terr_group_role_id = tgr.terr_group_role_id );
SELECT distinct a.access_type
from jtf_tty_role_access a
, jtf_tty_terr_grp_roles b
where a.terr_group_role_id = b.terr_group_role_id
and b.terr_group_id = l_terr_group_id;
SELECT distinct a.access_type
from jtf_tty_role_access a
, jtf_tty_terr_grp_roles b
where a.terr_group_role_id = b.terr_group_role_id
and b.terr_group_id = lp_terr_group_id
and b.role_code = lp_role
AND NOT EXISTS (
/* Product Interest does not exist for this role */
SELECT NULL
FROM jtf_tty_role_prod_int rpi
WHERE rpi.terr_group_role_id = B.TERR_GROUP_ROLE_ID )
order by a.access_type ;
SELECT distinct a.access_type
from jtf_tty_role_access a
, jtf_tty_terr_grp_roles b
where a.terr_group_role_id = b.terr_group_role_id
and b.terr_group_id = l_terr_group_id
and b.role_code = l_role
order by a.access_type ;
SELECT b.role_code role_code
--,a.interest_type_id
,b.terr_group_id
from jtf_tty_role_prod_int a
, jtf_tty_terr_grp_roles b
where a.terr_group_role_id(+) = b.terr_group_role_id
and b.terr_group_id = l_terr_group_id
and a.terr_group_role_id is null
order by b.role_code;
SELECT distinct
b.role_code role_code
, r.role_name role_name
from jtf_rs_roles_vl r
, jtf_tty_role_prod_int a
, jtf_tty_terr_grp_roles b
where r.role_code = b.role_code
and a.terr_group_role_id = b.terr_group_role_id
and b.terr_group_id = lp_terr_group_id
AND EXISTS (
/* Named Account exists with Salesperson with this role */
SELECT NULL
FROM jtf_tty_named_acct_rsc nar, jtf_tty_terr_grp_accts tga
WHERE tga.terr_group_account_id = nar.terr_group_account_id
AND nar.terr_group_account_id = lp_terr_group_account_id
AND tga.terr_group_id = b.terr_group_id
AND nar.rsc_role_code = b.role_code );
SELECT a.interest_type_id
from jtf_tty_role_prod_int a
, jtf_tty_terr_grp_roles b
where a.terr_group_role_id = b.terr_group_role_id
and b.terr_group_id = l_terr_group_id
and b.role_code = l_role;
SELECT distinct b.comparison_operator
,b.value1_char
from jtf_tty_terr_grp_accts a
, jtf_tty_acct_qual_maps b
where a.named_account_id = b.named_account_id
and a.terr_group_id = l_terr_group_id
and b.qual_usg_id = -1012
order by b.comparison_operator,b.value1_char;
SELECT distinct b.resource_id
, b.rsc_group_id
, b.rsc_resource_type
from jtf_tty_terr_grp_accts a
, jtf_tty_named_acct_rsc b
where a.terr_group_account_id = l_terr_group_acct_id
and a.terr_group_account_id = b.terr_group_account_id
and b.rsc_role_code = l_role;
SELECT substr(a.party_name, 1, 45) || ': ' || a.postal_code name
, b.named_account_id
, c.terr_group_account_id
, a.duns_number_c
from hz_parties a
, jtf_tty_named_accts b
, jtf_tty_terr_grp_accts c
where c.terr_group_id = LP_terr_group_id
and b.named_account_id = c.named_account_id
and a.party_id = b.party_id
and a.status = 'A'
AND a.DUNS_NUMBER_C IS NOT NULL
AND EXISTS (
/* Salesperson exists for this Named Account */
SELECT NULL
FROM jtf_tty_named_acct_rsc nar
WHERE nar.terr_group_account_id = C.TERR_GROUP_ACCOUNT_ID );
SELECT substr(a.party_name, 1, 45) || ': ' || a.postal_code name
, b.named_account_id
, c.terr_group_account_id
, a.duns_number_c
from hz_parties a
, jtf_tty_named_accts b
, jtf_tty_terr_grp_accts c
where c.terr_group_id = LP_terr_group_id
and b.named_account_id = c.named_account_id
and a.party_id = b.party_id
and a.status = 'A'
and exists (
/* Named Account has at least 1 Mapping Rule */
SELECT 1
from jtf_tty_acct_qual_maps d
where d.named_account_id = c.named_account_id )
AND EXISTS (
/* Salesperson exists for this Named Account */
SELECT NULL
FROM jtf_tty_named_acct_rsc nar
WHERE nar.terr_group_account_id = C.TERR_GROUP_ACCOUNT_ID );
SELECT substr(a.party_name, 1, 45) || ': ' || a.postal_code name
, b.named_account_id
, c.terr_group_account_id
, a.duns_number_c
from hz_parties a
, jtf_tty_named_accts b
, jtf_tty_terr_grp_accts c
where c.terr_group_id = LP_terr_group_id
and b.named_account_id = c.named_account_id
and a.party_id = b.party_id
and a.status = 'A'
AND a.DUNS_NUMBER_C IS NOT NULL
AND EXISTS (
/* Salesperson, with Role that has a Product
** Interest defined, exists for this Named Account */
SELECT NULL
FROM jtf_tty_named_acct_rsc nar
, jtf_tty_role_prod_int rpi
, jtf_tty_terr_grp_roles tgr
WHERE rpi.terr_group_role_id = tgr.terr_group_role_id
AND tgr.terr_group_id = C.TERR_GROUP_ID
AND tgr.role_code = nar.rsc_role_code
AND nar.terr_group_account_id = C.TERR_GROUP_ACCOUNT_ID );
SELECT substr(a.party_name, 1, 45) || ': ' || a.postal_code name
, b.named_account_id
, c.terr_group_account_id
, a.duns_number_c
from hz_parties a
, jtf_tty_named_accts b
, jtf_tty_terr_grp_accts c
where c.terr_group_id = LP_terr_group_id
and b.named_account_id = c.named_account_id
and a.party_id = b.party_id
and a.status = 'A'
and exists (
/* Named Account has at least 1 Mapping Rule */
SELECT 1
from jtf_tty_acct_qual_maps d
where d.named_account_id = c.named_account_id )
AND EXISTS (
/* Salesperson, with Role that has a Product
** Interest defined, exists for this Named Account */
SELECT NULL
FROM jtf_tty_named_acct_rsc nar
, jtf_tty_role_prod_int rpi
, jtf_tty_terr_grp_roles tgr
WHERE rpi.terr_group_role_id = tgr.terr_group_role_id
AND tgr.terr_group_id = C.TERR_GROUP_ID
AND tgr.role_code = nar.rsc_role_code
AND nar.terr_group_account_id = C.TERR_GROUP_ACCOUNT_ID );
SELECT b.qual_usg_id
, b.comparison_operator
, b.value1_char
, b.value2_char
FROM jtf_tty_acct_qual_maps b
WHERE b.qual_usg_id IN (-1007, -1012)
AND b.named_account_id = l_na_id
ORDER BY b.qual_usg_id;
SELECT -1120 qual_usg_id
, '=' comparison_operator
, hzp.duns_number_c value1_char
FROM hz_parties hzp, jtf_tty_named_accts na
where hzp.status = 'A'
AND hzp.party_id = na.party_id
AND na.named_account_id = l_na_id;
SELECT name
, description
, rank
, parent_territory_id
, terr_id
from jtf_terr_all
where terr_id = l_terr;
SELECT jtq.terr_qual_id
, jtq.qual_usg_id
FROM jtf_terr_qual_all jtq
WHERE jtq.terr_id = lp_terr_id;
SELECT jtv.TERR_VALUE_ID
, jtv.INCLUDE_FLAG
, jtv.COMPARISON_OPERATOR
, jtv.LOW_VALUE_CHAR
, jtv.HIGH_VALUE_CHAR
, jtv.LOW_VALUE_NUMBER
, jtv.HIGH_VALUE_NUMBER
, jtv.VALUE_SET
, jtv.INTEREST_TYPE_ID
, jtv.PRIMARY_INTEREST_CODE_ID
, jtv.SECONDARY_INTEREST_CODE_ID
, jtv.CURRENCY_CODE
, jtv.ORG_ID
, jtv.ID_USED_FLAG
, jtv.LOW_VALUE_CHAR_ID
FROM jtf_terr_values_all jtv
WHERE jtv.terr_qual_id = lp_terr_qual_id;
SELECT distinct b.role_code
from jtf_tty_role_access a
, jtf_tty_terr_grp_roles b
, jtf_tty_role_prod_int c
where a.terr_group_role_id = b.terr_group_role_id
and b.terr_group_id = l_terr_group_id
and a.access_type = 'ACCOUNT'
and c.terr_group_role_id = b.terr_group_role_id
and not exists ( SELECT 1
from jtf_tty_role_prod_int e
, jtf_tty_terr_grp_roles d
where e.terr_group_role_id (+) = d.terr_group_role_id
and d.terr_group_id = b.terr_group_id
and d.role_code = b.role_code
and e.interest_type_id is null);
** START: DELETE ALL EXISTING NAMED ACCOUNT TERRITORIES
** INCREMENTAL or TOTAL mode */
cleanup_na_territories(p_mode => p_mode);
SELECT JTF_TTY_TERR_GRP_ROLES_S.nextval
into l_id
FROM DUAL;
INSERT into JTF_TTY_TERR_GRP_ROLES(
TERR_GROUP_ROLE_ID
, OBJECT_VERSION_NUMBER
, TERR_GROUP_ID
, ROLE_CODE
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN)
VALUES(
l_overnon_role_tbl(i).grp_role_id
, 1
, terr_group.terr_group_id
, overlayandnon.role_code
, G_USER_ID
, sysdate
, G_USER_ID
, sysdate
, G_LOGIN_ID);
INSERT into JTF_TTY_ROLE_ACCESS(
TERR_GROUP_ROLE_ACCESS_ID
, OBJECT_VERSION_NUMBER
, TERR_GROUP_ROLE_ID
, ACCESS_TYPE
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN)
VALUES(
JTF_TTY_ROLE_ACCESS_S.nextval
, 1
, l_overnon_role_tbl(i).grp_role_id
, 'ACCOUNT'
, G_USER_ID
, sysdate
, G_USER_ID
, sysdate
, G_LOGIN_ID);
SELECT COUNT(*)
INTO l_na_count
from jtf_tty_terr_groups g
, jtf_tty_terr_grp_accts ga
, jtf_tty_named_accts a
where g.terr_group_id = ga.terr_group_id
AND ga.named_account_id = a.named_account_id
AND g.terr_group_id = TERR_GROUP.TERR_GROUP_ID
AND ROWNUM < 2;
SELECT COUNT(*)
INTO l_na_count
from jtf_tty_terr_groups tgrp
, jtf_tty_geo_grp_values gterr
where tgrp.terr_group_id = gterr.terr_group_id
AND ROWNUM < 2;
L_TERR_ALL_REC.LAST_UPDATE_DATE := TERR_GROUP.LAST_UPDATE_DATE;
L_TERR_ALL_REC.LAST_UPDATED_BY := G_USER_ID;
L_TERR_ALL_REC.LAST_UPDATE_LOGIN := G_LOGIN_ID;
L_TERR_ALL_REC.UPDATE_FLAG := 'N';
SELECT JTF_TERR_USGS_S.nextval
INTO l_terr_usg_id
FROM DUAL;
l_terr_usgs_tbl(1).LAST_UPDATE_DATE := terr_group.LAST_UPDATE_DATE;
l_terr_usgs_tbl(1).LAST_UPDATED_BY := G_USER_ID;
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:= G_LOGIN_ID;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE := terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY := G_USER_ID;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN := G_LOGIN_ID;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE := terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY := G_USER_ID;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN := G_LOGIN_ID;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(3).LAST_UPDATE_DATE := terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(3).LAST_UPDATED_BY := G_USER_ID;
l_terr_qualtypeusgs_tbl(3).LAST_UPDATE_LOGIN := G_LOGIN_ID;
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, CATCH_ALL_FLAG = 'N'
, TERR_GROUP_ID = TERR_GROUP.TERR_GROUP_ID
, NUM_WINNERS = TERR_GROUP.NUM_WINNERS
WHERE TERR_ID = X_TERR_ID;
L_TERR_ALL_REC.LAST_UPDATE_DATE := TERR_GROUP.LAST_UPDATE_DATE;
L_TERR_ALL_REC.LAST_UPDATED_BY := G_USER_ID;
L_TERR_ALL_REC.LAST_UPDATE_LOGIN := G_LOGIN_ID;
L_TERR_ALL_REC.UPDATE_FLAG := 'N';
SELECT JTF_TERR_USGS_S.nextval
into l_terr_usg_id
FROM DUAL;
l_terr_usgs_tbl(1).LAST_UPDATE_DATE := terr_group.LAST_UPDATE_DATE;
l_terr_usgs_tbl(1).LAST_UPDATED_BY := G_USER_ID;
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := G_LOGIN_ID;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := G_USER_ID;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := G_LOGIN_ID;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_terr_qual_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qual_tbl(1).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
UPDATE JTF_TERR_ALL
set TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = terr_group.TERR_GROUP_ID
, CATCH_ALL_FLAG = 'Y'
where terr_id = x_terr_id;
SELECT JTF_TERR_RSC_S.nextval
into l_terr_rsc_id
FROM DUAL;
l_TerrRsc_Tbl(1).LAST_UPDATE_DATE:=terr_group.LAST_UPDATE_DATE;
l_TerrRsc_Tbl(1).LAST_UPDATED_BY:=terr_group.LAST_UPDATED_BY;
l_TerrRsc_Tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT COUNT(*)
INTO l_terr_exists
FROM jtf_terr_all jt
WHERE jt.terr_id = naterr.terr_group_account_id * -100;
l_terr_all_rec.LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_all_rec.LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_all_rec.LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_all_rec.UPDATE_FLAG :='N';
SELECT JTF_TERR_USGS_S.nextval
into l_terr_usg_id
FROM DUAL;
l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_usgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_terr_qual_tbl(j).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
UPDATE JTF_TERR_ALL
set TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = terr_group.TERR_GROUP_ID
, CATCH_ALL_FLAG = 'N'
, NAMED_ACCOUNT_FLAG = 'Y'
, TERR_GROUP_ACCOUNT_ID = naterr.terr_group_account_id
where terr_id = x_terr_id;
SELECT JTF_TERR_RSC_S.nextval
into l_terr_rsc_id
FROM DUAL;
l_TerrRsc_Tbl(i).LAST_UPDATE_DATE:=terr_group.LAST_UPDATE_DATE;
l_TerrRsc_Tbl(i).LAST_UPDATED_BY:=terr_group.LAST_UPDATED_BY;
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT COUNT(*)
INTO l_terr_exists
FROM jtf_terr_all jt
WHERE jt.terr_id = naterr.terr_group_account_id * -10000;
l_terr_all_rec.LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_all_rec.LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_all_rec.LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_all_rec.UPDATE_FLAG :='N';
SELECT JTF_TERR_USGS_S.nextval
into l_terr_usg_id
FROM DUAL;
l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_usgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_terr_qual_tbl(j).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
UPDATE JTF_TERR_ALL
set TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = terr_group.TERR_GROUP_ID
, CATCH_ALL_FLAG = 'N'
, NAMED_ACCOUNT_FLAG = 'Y'
, TERR_GROUP_ACCOUNT_ID = naterr.terr_group_account_id
where terr_id = x_terr_id;
SELECT JTF_TERR_RSC_S.nextval
into l_terr_rsc_id
FROM DUAL;
l_TerrRsc_Tbl(i).LAST_UPDATE_DATE:=terr_group.LAST_UPDATE_DATE;
l_TerrRsc_Tbl(i).LAST_UPDATED_BY:=terr_group.LAST_UPDATED_BY;
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
/* delete the role and access */
/********************************************************/
if l_ovnon_flag = 'Y' then
for i in l_overnon_role_tbl.first.. l_overnon_role_tbl.last
loop
delete from jtf_tty_terr_grp_roles
where TERR_GROUP_ROLE_ID=l_overnon_role_tbl(i).grp_role_id;
delete from jtf_tty_role_access
where TERR_GROUP_ROLE_ID=l_overnon_role_tbl(i).grp_role_id;
SELECT COUNT( DISTINCT b.role_code )
into l_pi_count
from jtf_rs_roles_vl r
, jtf_tty_role_prod_int a
, jtf_tty_terr_grp_roles b
where r.role_code = b.role_code
and a.terr_group_role_id = b.terr_group_role_id
and b.terr_group_id = TERR_GROUP.TERR_GROUP_ID
AND EXISTS (
/* Named Account exists with Salesperson with this role */
SELECT NULL
FROM jtf_tty_named_acct_rsc nar, jtf_tty_terr_grp_accts tga
WHERE tga.terr_group_account_id = nar.terr_group_account_id
AND tga.terr_group_id = b.terr_group_id
AND nar.rsc_role_code = b.role_code )
AND ROWNUM < 2;
l_terr_all_rec.LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_all_rec.LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_all_rec.LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_all_rec.UPDATE_FLAG :='N';
SELECT JTF_TERR_USGS_S.nextval
into l_terr_usg_id
FROM DUAL;
l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_usgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_terr_qual_tbl(j).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := G_USER_ID;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= G_SYSDATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= G_LOGIN_ID;
UPDATE JTF_TERR_ALL
set TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = terr_group.TERR_GROUP_ID
where terr_id = x_terr_id;
l_terr_all_rec.LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_all_rec.LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_all_rec.LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_all_rec.UPDATE_FLAG :='N';
SELECT JTF_TERR_USGS_S.nextval
into l_terr_usg_id
FROM DUAL;
l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_usgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_terr_qual_tbl(j).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
UPDATE JTF_TERR_ALL
set TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = terr_group.TERR_GROUP_ID
, NAMED_ACCOUNT_FLAG = 'Y'
, TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id
where terr_id = x_terr_id;
l_terr_all_rec.LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_all_rec.LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_all_rec.LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_all_rec.UPDATE_FLAG :='N';
SELECT JTF_TERR_USGS_S.nextval
into l_terr_usg_id
FROM DUAL;
l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_usgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_terr_qual_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_terr_qual_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
UPDATE JTF_TERR_ALL
set TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = terr_group.TERR_GROUP_ID
, NAMED_ACCOUNT_FLAG = 'Y'
, TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id
where terr_id = x_terr_id;
SELECT JTF_TERR_RSC_S.nextval
into l_terr_rsc_id
FROM DUAL;
l_TerrRsc_Tbl(i).LAST_UPDATE_DATE:=terr_group.LAST_UPDATE_DATE;
l_TerrRsc_Tbl(i).LAST_UPDATED_BY:=terr_group.LAST_UPDATED_BY;
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_all_rec.LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_all_rec.LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_all_rec.LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_all_rec.UPDATE_FLAG :='N';
SELECT JTF_TERR_USGS_S.nextval
into l_terr_usg_id
FROM DUAL;
l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_usgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_terr_qual_tbl(j).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
UPDATE JTF_TERR_ALL
set TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = terr_group.TERR_GROUP_ID
, NAMED_ACCOUNT_FLAG = 'Y'
, TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id
where terr_id = x_terr_id;
l_terr_all_rec.LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_all_rec.LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_all_rec.LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_all_rec.UPDATE_FLAG :='N';
SELECT JTF_TERR_USGS_S.nextval
into l_terr_usg_id
FROM DUAL;
l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_usgs_tbl(1).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_terr_qual_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_terr_qual_tbl(i).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := terr_group.last_UPDATED_BY;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= terr_group.last_UPDATE_DATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= terr_group.last_UPDATE_LOGIN;
UPDATE JTF_TERR_ALL
set TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = terr_group.TERR_GROUP_ID
, NAMED_ACCOUNT_FLAG = 'Y'
, TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id
where terr_id = x_terr_id;
SELECT JTF_TERR_RSC_S.nextval
into l_terr_rsc_id
FROM DUAL;
l_TerrRsc_Tbl(i).LAST_UPDATE_DATE:=terr_group.LAST_UPDATE_DATE;
l_TerrRsc_Tbl(i).LAST_UPDATED_BY:=terr_group.LAST_UPDATED_BY;
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN:=terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= terr_group.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= terr_group.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= terr_group.LAST_UPDATE_LOGIN;
l_terr_group_rec.LAST_UPDATED_BY := terr_group.LAST_UPDATED_BY;
l_terr_group_rec.LAST_UPDATE_DATE := terr_group.LAST_UPDATE_DATE;
l_terr_group_rec.LAST_UPDATE_LOGIN := terr_group.LAST_UPDATE_LOGIN;
SELECT gterr.geo_territory_id
, gterr.geo_terr_name
FROM jtf_tty_geo_terr gterr
WHERE gterr.terr_group_id = l_terr_group_id;
SELECT ra.access_type
FROM
JTF_TTY_GEO_TERR_RSC grsc
, jtf_tty_terr_grp_roles tgr
, jtf_tty_role_access ra
WHERE grsc.GEO_TERRITORY_ID = l_geo_territory_id
AND grsc.rsc_role_code = tgr.role_code
AND ra.terr_group_role_id = tgr.terr_group_role_id
AND ra.access_type IN ('ACCOUNT')
UNION
SELECT ra.access_type
FROM
JTF_TTY_GEO_TERR_RSC grsc
, jtf_tty_terr_grp_roles tgr
, jtf_tty_role_access ra
WHERE grsc.GEO_TERRITORY_ID = l_geo_territory_id
AND grsc.rsc_role_code = tgr.role_code
AND ra.terr_group_role_id = tgr.terr_group_role_id
AND NOT EXISTS (
SELECT NULL
FROM jtf_tty_role_prod_int rpi
WHERE rpi.terr_group_role_id = tgr.terr_group_role_id );
SELECT -1007 qual_usg_id
, '=' comparison_operator
, main.postal_code value1_char
, main.geo_territory_id
from(
/* postal code */
select g.postal_code postal_code,
g.geo_id geo_id
, terr.geo_territory_id
from jtf_tty_geo_grp_values grpv,
jtf_tty_terr_groups tg,
jtf_tty_geo_terr terr,
jtf_tty_geographies g --postal_code level
where 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 ( (
grpv.geo_type = 'POSTAL_CODE'
and grpv.comparison_operator = '='
and g.geo_id = grpv.geo_id_from
and g.geo_type = 'POSTAL_CODE'
)
or
(
grpv.geo_type = 'POSTAL_CODE'
and grpv.comparison_operator = 'BETWEEN'
and g.geo_type = 'POSTAL_CODE'
and g.geo_id between grpv.geo_id_from and grpv.geo_id_to
)
)
union
select g.postal_code postal_code,
g.geo_id geo_id
, terr.geo_territory_id
from jtf_tty_geo_grp_values grpv,
jtf_tty_terr_groups tg,
jtf_tty_geo_terr terr,
jtf_tty_geographies g,
jtf_tty_geographies g1
where 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 (
(
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
)
)
union
select g.postal_code postal_code,
g.geo_id geo_id
, terr.geo_territory_id
from jtf_tty_terr_groups tg,
jtf_tty_geo_terr terr,
jtf_tty_geographies g,
jtf_tty_geo_terr_values tv
where 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
) main
where main.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
tv.geo_territory_id = terr.geo_territory_id
and main.geo_territory_id = terr.parent_geo_terr_id
)
and geo_territory_id = l_geo_territory_id;
SELECT distinct a.access_type
from jtf_tty_role_access a
, jtf_tty_terr_grp_roles b
where a.terr_group_role_id = b.terr_group_role_id
and b.terr_group_id = lp_terr_group_id
and b.role_code = lp_role
AND NOT EXISTS (
/* Product Interest does not exist for this role */
SELECT NULL
FROM jtf_tty_role_prod_int rpi
WHERE rpi.terr_group_role_id = B.TERR_GROUP_ROLE_ID )
order by a.access_type ;
SELECT b.role_code role_code
--,a.interest_type_id
,b.terr_group_id
from jtf_tty_role_prod_int a
, jtf_tty_terr_grp_roles b
where a.terr_group_role_id(+) = b.terr_group_role_id
and b.terr_group_id = l_terr_group_id
and a.terr_group_role_id is null
order by b.role_code;
SELECT distinct a.resource_id
, a.rsc_group_id
, nvl(a.rsc_resource_type,'RS_EMPLOYEE') rsc_resource_type
from jtf_tty_geo_terr_rsc a
, jtf_tty_geo_terr b
where a.geo_territory_id = b.geo_territory_id
and b.geo_territory_id = l_geo_territory_id
and a.rsc_role_code = l_role;
SELECT name
, description
, rank
, parent_territory_id
, terr_id
from jtf_terr_all
where terr_id = l_terr;
SELECT jtq.terr_qual_id
, jtq.qual_usg_id
FROM jtf_terr_qual_all jtq
WHERE jtq.terr_id = lp_terr_id;
SELECT jtv.TERR_VALUE_ID
, jtv.INCLUDE_FLAG
, jtv.COMPARISON_OPERATOR
, jtv.LOW_VALUE_CHAR
, jtv.HIGH_VALUE_CHAR
, jtv.LOW_VALUE_NUMBER
, jtv.HIGH_VALUE_NUMBER
, jtv.VALUE_SET
, jtv.INTEREST_TYPE_ID
, jtv.PRIMARY_INTEREST_CODE_ID
, jtv.SECONDARY_INTEREST_CODE_ID
, jtv.CURRENCY_CODE
, jtv.ORG_ID
, jtv.ID_USED_FLAG
, jtv.LOW_VALUE_CHAR_ID
FROM jtf_terr_values_all jtv
WHERE jtv.terr_qual_id = lp_terr_qual_id;
SELECT gterr.geo_territory_id
, gterr.geo_terr_name
FROM jtf_tty_geo_terr gterr
WHERE gterr.terr_group_id = lp_terr_group_id
AND EXISTS (
/* Salesperson, with Role that has a Product
** Interest defined, exists for this Named Account */
SELECT NULL
FROM jtf_tty_geo_terr_rsc grsc
, jtf_tty_role_prod_int rpi
, jtf_tty_terr_grp_roles tgr
WHERE rpi.terr_group_role_id = tgr.terr_group_role_id
AND tgr.terr_group_id = gterr.TERR_GROUP_ID
AND tgr.role_code = grsc.rsc_role_code
AND grsc.geo_territory_id = gterr.geo_territory_id );
SELECT distinct
b.role_code role_code
, r.role_name role_name
from jtf_rs_roles_vl r
, jtf_tty_role_prod_int a
, jtf_tty_terr_grp_roles b
where r.role_code = b.role_code
and a.terr_group_role_id = b.terr_group_role_id
and b.terr_group_id = lp_terr_group_id
AND EXISTS (
/* Named Account exists with Salesperson with this role */
SELECT NULL
FROM jtf_tty_geo_terr_rsc grsc, jtf_tty_geo_terr gterr
WHERE gterr.geo_territory_id = grsc.geo_territory_id
AND grsc.geo_territory_id = lp_geo_territory_id
AND gterr.terr_group_id = b.terr_group_id
AND grsc.rsc_role_code = b.role_code );
SELECT distinct a.access_type
from jtf_tty_role_access a
, jtf_tty_terr_grp_roles b
where a.terr_group_role_id = b.terr_group_role_id
and b.terr_group_id = l_terr_group_id
and b.role_code = l_role
order by a.access_type ;
SELECT a.interest_type_id
from jtf_tty_role_prod_int a
, jtf_tty_terr_grp_roles b
where a.terr_group_role_id = b.terr_group_role_id
and b.terr_group_id = l_terr_group_id
and b.role_code = l_role;
l_terr_all_rec.LAST_UPDATE_DATE := p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_all_rec.LAST_UPDATED_BY := p_terr_group_rec.LAST_UPDATED_BY;
l_terr_all_rec.LAST_UPDATE_LOGIN := p_terr_group_rec.LAST_UPDATE_LOGIN;
l_terr_all_rec.UPDATE_FLAG := 'N';
SELECT JTF_TERR_USGS_S.nextval
into l_terr_usg_id
FROM DUAL;
l_terr_usgs_tbl(1).LAST_UPDATE_DATE := p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_terr_group_rec.LAST_UPDATED_BY;
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_terr_group_rec.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_terr_group_rec.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE ;
l_terr_qual_tbl(j).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := p_terr_group_rec.last_UPDATED_BY;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= p_terr_group_rec.last_UPDATE_DATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= p_terr_group_rec.last_UPDATE_LOGIN;
UPDATE JTF_TERR_ALL
set TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_rec.TERR_GROUP_ID
where terr_id = x_terr_id;
SELECT JTF_TERR_RSC_S.nextval
into l_terr_rsc_id
FROM DUAL;
l_TerrRsc_Tbl(i).LAST_UPDATE_DATE:=p_terr_group_rec.LAST_UPDATE_DATE;
l_TerrRsc_Tbl(i).LAST_UPDATED_BY:=p_terr_group_rec.LAST_UPDATED_BY;
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN:=p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT COUNT( DISTINCT b.role_code )
into l_pi_count
from jtf_rs_roles_vl r
, jtf_tty_role_prod_int a
, jtf_tty_terr_grp_roles b
where r.role_code = b.role_code
and a.terr_group_role_id = b.terr_group_role_id
and b.terr_group_id = p_terr_group_rec.TERR_GROUP_ID
AND EXISTS (
/* Named Account exists with Salesperson with this role */
SELECT NULL
FROM jtf_tty_geo_terr_rsc grsc, jtf_tty_geo_terr gterr
WHERE grsc.geo_territory_id = gterr.geo_territory_id
AND gterr.terr_group_id = b.terr_group_id
AND grsc.rsc_role_code = b.role_code )
AND ROWNUM < 2;
l_terr_all_rec.LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_all_rec.LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_all_rec.LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
l_terr_all_rec.UPDATE_FLAG :='N';
SELECT JTF_TERR_USGS_S.nextval
into l_terr_usg_id
FROM DUAL;
l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_usgs_tbl(1).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE ;
l_terr_qual_tbl(j).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := G_USER_ID;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= G_SYSDATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= G_LOGIN_ID;
UPDATE JTF_TERR_ALL
set terr_group_FLAG = 'Y'
, terr_group_ID = p_terr_group_rec.TERR_GROUP_ID
where terr_id = x_terr_id;
l_terr_all_rec.LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_all_rec.LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_all_rec.LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
l_terr_all_rec.UPDATE_FLAG :='N';
SELECT JTF_TERR_USGS_S.nextval
into l_terr_usg_id
FROM DUAL;
l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_usgs_tbl(1).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE ;
l_terr_qual_tbl(j).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := p_terr_group_rec.last_UPDATED_BY;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= p_terr_group_rec.last_UPDATE_DATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= p_terr_group_rec.last_UPDATE_LOGIN;
UPDATE JTF_TERR_ALL
set TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_rec.TERR_GROUP_ID
where terr_id = x_terr_id;
l_terr_all_rec.LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_all_rec.LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_all_rec.LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
l_terr_all_rec.UPDATE_FLAG :='N';
SELECT JTF_TERR_USGS_S.nextval
into l_terr_usg_id
FROM DUAL;
l_terr_usgs_tbl(1).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_usgs_tbl(1).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN:=p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE ;
l_terr_qual_tbl(i).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := p_terr_group_rec.last_UPDATED_BY;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= p_terr_group_rec.last_UPDATE_DATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= p_terr_group_rec.last_UPDATE_LOGIN;
SELECT JTF_TERR_QTYPE_USGS_S.nextval
into l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_QUAL_S.nextval
into l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE ;
l_terr_qual_tbl(i).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
l_terr_values_tbl(k).LAST_UPDATED_BY := p_terr_group_rec.last_UPDATED_BY;
l_terr_values_tbl(k).LAST_UPDATE_DATE:= p_terr_group_rec.last_UPDATE_DATE;
l_terr_values_tbl(k).LAST_UPDATE_LOGIN:= p_terr_group_rec.last_UPDATE_LOGIN;
UPDATE JTF_TERR_ALL
set TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_rec.TERR_GROUP_ID
where terr_id = x_terr_id;
SELECT JTF_TERR_RSC_S.nextval
into l_terr_rsc_id
FROM DUAL;
l_TerrRsc_Tbl(i).LAST_UPDATE_DATE:=p_terr_group_rec.LAST_UPDATE_DATE;
l_TerrRsc_Tbl(i).LAST_UPDATED_BY:=p_terr_group_rec.LAST_UPDATED_BY;
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN:=p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;
SELECT JTF_TERR_RSC_ACCESS_S.nextval
into l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE:= p_terr_group_rec.LAST_UPDATE_DATE ;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY:= p_terr_group_rec.LAST_UPDATED_BY;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN:= p_terr_group_rec.LAST_UPDATE_LOGIN;