[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 */
, 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;
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;
SELECT 'TRUE'
FROM jtf_terr_qtype_usgs_all jtqu
, jtf_terr_usgs_all jtu
, jtf_terr_all jt1
, jtf_qual_type_usgs jqtu
WHERE jtqu.terr_id = jt1.terr_id
AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
AND jqtu.qual_type_id = lp_qual_type_id
AND jtu.source_id = lp_source_id
AND jtu.terr_id = jt1.terr_id
AND NVL(jt1.end_date_active, lp_sysdate) >= lp_sysdate
AND jt1.start_date_active <= lp_sysdate
AND EXISTS (
SELECT jtrs.terr_rsc_id
FROM jtf_terr_rsc_all jtrs
WHERE NVL(jtrs.end_date_active, lp_sysdate) >= lp_sysdate
AND NVL(jtrs.start_date_active, lp_sysdate) <= lp_sysdate
AND jtrs.terr_id = jt1.terr_id )
AND NOT EXISTS (
SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE NVL(jt.end_date_active, lp_sysdate + 1) < lp_sysdate
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = jt1.terr_id)
AND jqtu.qual_type_id <> -1001
AND jtu.source_id <> -1003
AND rownum < 2;
SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.terr_id = jtdr.related_terr_id
AND jtdr.source_id = lp_source_id
AND jtdr.qual_type_id = lp_qual_type_id;
/* ARPATEL: 12/08/2003 Cursor used to update num_qual in jtf_terr_qtype_usgs_all for Oracle Sales */
CURSOR csr_get_terr_num_qual ( lp_source_id NUMBER
, lp_qual_type_id NUMBER ) IS
SELECT jtqu.terr_id
, jtqu.terr_qtype_usg_id
FROM jtf_terr_qtype_usgs_all jtqu
, jtf_terr_denorm_rules_all jtdr
, jtf_qual_type_usgs jqtu
WHERE jqtu.qual_type_id = LP_qual_type_id
AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
AND jtqu.terr_id = jtdr.terr_id
AND jtdr.resource_exists_flag = 'Y'
AND jtdr.terr_id = jtdr.related_terr_id
AND jtdr.source_id = LP_source_id;
SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.terr_id = jtdr.related_terr_id
AND jtdr.source_id = lp_source_id
AND jtdr.qual_type_id = lp_qual_type_id
AND jtdr.resource_exists_flag = 'Y';
SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.terr_id = jtdr.related_terr_id
AND jtdr.source_id = lp_source_id;
SELECT jqt.qual_type_id
FROM jtf_qual_type_usgs jqtu, jtf_qual_types jqt
WHERE jqtu.qual_type_id = jqt.qual_type_id
AND jqt.qual_type_id <> -1001
AND jqtu.source_id = lp_source_id;
g_terr_pkgspec.DELETE;
g_pkgname_tbl.Delete;
UPDATE jtf_terr_denorm_rules_all j
SET j.resource_exists_flag = 'Y'
WHERE EXISTS
( SELECT jtr.terr_id
FROM jtf_terr_rsc_all jtr
WHERE (jtr.end_date_active IS NULL OR jtr.end_date_active >= SYSDATE)
AND (jtr.start_date_active IS NULL OR jtr.start_date_active <= SYSDATE)
AND jtr.terr_id = j.terr_id
)
AND j.terr_id = j.related_terr_id
AND j.source_id = p_source_id;
UPDATE jtf_terr_denorm_rules_all jtdr
SET jtdr.ABSOLUTE_RANK = (
SELECT SUM(jt1.relative_rank)
FROM jtf_terr_denorm_rules_all jt1
WHERE jt1.related_terr_id = jt1.terr_id
/* JDOCHERT: 12/09/03: records in JTF_TERR_DENORM_RULES_ALL
** are no longer striped by QUAL_TYPE_ID, so commenting out the
** the following lines
*/
--ARPATEL: 11/12/03 Bug#3254575 */
--AND jt1.qual_type_id = lp_qual_type_id
--
--
AND jt1.terr_id IN
( SELECT jt.terr_id
FROM jtf_terr_all jt
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = csr_dnm.terr_id )
)
WHERE jtdr.source_id = p_source_id
--
-- JDOCHERT: 10/25/03: Following line was commented out
-- as real-time APIs depend on ABSOLUTE_RANK being set for
-- them to return results correctly
--
--AND jtdr.related_terr_id = jtdr.terr_id
--
AND jtdr.terr_id = CSR_DNM.terr_id;
UPDATE jtf_terr_denorm_rules_all j
SET j.resource_exists_flag = 'Y'
WHERE EXISTS
( SELECT jtr.terr_id
FROM jtf_terr_rsc_all jtr
WHERE (jtr.end_date_active IS NULL OR jtr.end_date_active >= SYSDATE)
AND (jtr.start_date_active IS NULL OR jtr.start_date_active <= SYSDATE)
AND jtr.terr_id = j.terr_id
)
AND j.terr_id = j.related_terr_id
AND j.source_id = p_source_id
AND j.qual_type_id = lp_qual_type_id;
UPDATE jtf_terr_denorm_rules_all jtdr
SET jtdr.ABSOLUTE_RANK = (
SELECT SUM(jt1.relative_rank)
FROM jtf_terr_denorm_rules_all jt1
WHERE jt1.related_terr_id = jt1.terr_id
/* ARPATEL: 11/12/03 Bug#3254575 */
AND jt1.qual_type_id = lp_qual_type_id
AND jt1.terr_id IN
( SELECT jt.terr_id
FROM jtf_terr_all jt
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = csr_dnm.terr_id )
)
WHERE jtdr.source_id = p_source_id
AND jtdr.qual_type_id = lp_qual_type_id
--
-- JDOCHERT: 10/25/03: Following line was commented out
-- as real-time APIs depend on ABSOLUTE_RANK being set for
-- to return results correctly
--
--AND jtdr.related_terr_id = jtdr.terr_id
--
AND jtdr.terr_id = CSR_DNM.terr_id;
Write_Log(2, 'START: UPDATE jtf_terr_qtype_usgs_all ');
/* ACHANDA 02/03/04 Bug 3373687 : disable the trigger before update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD DISABLE';
UPDATE jtf_terr_qtype_usgs_all qua
SET qua.num_qual = (
SELECT COUNT(*)
FROM jtf_terr_qual_all jtq
, jtf_qual_usgs_all jqu
, jtf_qual_type_usgs jqtu
, jtf_qual_type_denorm_v v
WHERE jtq.qual_usg_id = jqu.qual_usg_id
/* ACHANDA 03/08/2004 : Bug 3378530 : change the where clause to use index more selectively */
AND jqu.org_id = -3113
/*
AND ( (jtq.org_id = jqu.org_id) OR
(jtq.org_id IS NULL AND jqu.org_ID IS NULL)
)
*/
AND jqu.qual_type_usg_id = jqtu.qual_type_usg_id
AND jqtu.qual_type_id <> -1001
AND jqtu.source_id = p_source_id
AND jqtu.qual_type_id = v.related_id
AND v.qual_type_id = lp_qual_type_id
AND jtq.terr_id IN
( SELECT jt.terr_id
FROM jtf_terr_all jt
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = csr_dnm.terr_id )
)
WHERE qua.terr_qtype_usg_id = csr_dnm.terr_qtype_usg_id;
/* ACHANDA 02/03/04 Bug 3373687 : enable the trigger after update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD ENABLE';
Write_Log(2, 'END: UPDATE jtf_terr_qtype_usgs_all ');
UPDATE jtf_terr_denorm_rules_all j
SET j.resource_exists_flag = 'Y'
WHERE EXISTS
( SELECT jtr.terr_id
FROM jtf_terr_rsc_all jtr
WHERE (jtr.end_date_active IS NULL OR jtr.end_date_active >= SYSDATE)
AND (jtr.start_date_active IS NULL OR jtr.start_date_active <= SYSDATE)
AND jtr.terr_id = j.terr_id
)
AND j.terr_id = j.related_terr_id
AND j.source_id = p_source_id;
UPDATE jtf_terr_denorm_rules_all jtdr
SET jtdr.ABSOLUTE_RANK = (
SELECT SUM(jt1.relative_rank)
FROM jtf_terr_denorm_rules_all jt1
WHERE jt1.related_terr_id = jt1.terr_id
/* JDOCHERT: 12/09/03: records in JTF_TERR_DENORM_RULES_ALL
** are no longer striped by QUAL_TYPE_ID, so commenting out the
** the following lines
*/
/* ARPATEL: 11/12/03 Bug#3254575 */
--AND jt1.qual_type_id = p_qualifier_type_id
--
AND jt1.terr_id IN
( SELECT jt.terr_id
FROM jtf_terr_all jt
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = csr_dnm.terr_id )
)
WHERE jtdr.source_id = p_source_id
AND jtdr.terr_id = CSR_DNM.terr_id;
UPDATE jtf_terr_denorm_rules_all j
SET j.resource_exists_flag = 'Y'
WHERE EXISTS
( SELECT jtr.terr_id
FROM jtf_terr_rsc_all jtr
WHERE (jtr.end_date_active IS NULL OR jtr.end_date_active >= SYSDATE)
AND (jtr.start_date_active IS NULL OR jtr.start_date_active <= SYSDATE)
AND jtr.terr_id = j.terr_id
)
AND j.terr_id = j.related_terr_id
AND j.source_id = p_source_id
AND j.qual_type_id = LP_QUAL_TYPE_ID;
UPDATE jtf_terr_denorm_rules_all jtdr
SET jtdr.ABSOLUTE_RANK = (
SELECT SUM(jt1.relative_rank)
FROM jtf_terr_denorm_rules_all jt1
WHERE jt1.related_terr_id = jt1.terr_id
/* ARPATEL: 11/12/03 Bug#3254575 */
AND jt1.qual_type_id = p_qualifier_type_id
AND jt1.terr_id IN
( SELECT jt.terr_id
FROM jtf_terr_all jt
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = csr_dnm.terr_id )
)
WHERE jtdr.source_id = p_source_id
AND jtdr.qual_type_id = LP_QUAL_TYPE_ID
--
-- JDOCHERT: 10/25/03: Following line was commented out
-- as real-time APIs depend on ABSOLUTE_RANK being set for
-- to return results correctly
--
--AND jtdr.related_terr_id = jtdr.terr_id
--
AND jtdr.terr_id = CSR_DNM.terr_id;
Write_Log(2, 'START: UPDATE jtf_terr_qtype_usgs_all ');
/* ACHANDA 02/03/04 Bug 3373687 : disable the trigger before update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD DISABLE';
UPDATE jtf_terr_qtype_usgs_all qua
SET qua.num_qual = ( SELECT count(*)
FROM jtf_terr_qual_all jtq
, jtf_qual_usgs_all jqu
, jtf_qual_type_usgs jqtu
, jtf_qual_type_denorm_v v
WHERE jtq.qual_usg_id = jqu.qual_usg_id
/* ACHANDA 02/03/2004 : Bug 3378530 : change the where clause to use index more selectively */
AND jqu.org_id = -3113
/*
AND ( (jtq.org_id = jqu.org_id) OR
(jtq.org_id IS NULL AND jqu.org_ID IS NULL)
)
*/
AND jqu.qual_type_usg_id = jqtu.qual_type_usg_id
AND jqtu.qual_type_id <> -1001
AND jqtu.source_id = p_source_id
AND jqtu.qual_type_id = v.related_id
AND v.qual_type_id = p_qualifier_type_id
AND jtq.terr_id IN
( SELECT jt.terr_id
FROM jtf_terr_all jt
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = csr_dnm.terr_id )
)
WHERE qua.terr_qtype_usg_id = csr_dnm.terr_qtype_usg_id;
/* ACHANDA 02/03/04 Bug 3373687 : enable the trigger after update */
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD ENABLE';
Write_Log(2, 'END: UPDATE jtf_terr_qtype_usgs_all ');
UPDATE jtf_terr_rsc_all jtr
SET jtr.person_id =
( SELECT jrrev.source_id
FROM jtf_rs_resource_extns_vl jrrev
WHERE jrrev.category = 'EMPLOYEE'
AND jrrev.resource_id = jtr.resource_id )
WHERE jtr.resource_type= 'RS_EMPLOYEE'
AND jtr.terr_id IN
( SELECT jtu.terr_id
FROM jtf_terr_usgs_all jtu
WHERE jtu.source_id = p_source_id )
AND EXISTS
( SELECT jrrev.resource_id
FROM jtf_rs_resource_extns_vl jrrev
WHERE jrrev.resource_id = jtr.resource_id );
SELECT count(*)
INTO l_denorm_count
FROM jtf_terr_denorm_rules_all j
WHERE j.source_id = p_source_id
AND ( ( j.qual_type_id = p_qualifier_type_id )
OR
( p_source_id = -1001 AND p_qualifier_type_id IS NULL )
);
Write_Log(2, 'Inserted ' || l_denorm_count || ' rows into JTF_TERR_DENORM_RULES_ALL ');
SELECT count(*)
INTO l_mv1_count
FROM jtf_terr_qual_rules_mv j;
SELECT count(*)
INTO l_mv2_count
FROM jtf_terr_cnr_qual_like_mv j;
SELECT count(*)
INTO l_mv3_count
FROM jtf_terr_cnr_qual_btwn_mv j;
SELECT count(*)
INTO l_mv4_count
FROM jtf_terr_cnrg_equal_mv j;
SELECT count(*)
INTO l_mv5_count
FROM jtf_terr_cnrg_like_mv j;
SELECT count(*)
INTO l_mv6_count
FROM jtf_terr_cnrg_btwn_mv j;
Write_Log(2, 'Inserted ' || l_mv1_count || ' rows into JTF_TERR_QUAL_RULES_MV ');
Write_Log(2, 'Inserted ' || l_mv2_count || ' rows into JTF_TERR_CNR_QUAL_LIKE_MV ');
Write_Log(2, 'Inserted ' || l_mv3_count || ' rows into JTF_TERR_CNR_QUAL_BTWN_MV ');
Write_Log(2, 'Inserted ' || l_mv4_count || ' rows into JTF_TERR_CNRG_EQUAL_MV ');
Write_Log(2, 'Inserted ' || l_mv5_count || ' rows into JTF_TERR_CNRG_LIKE_MV ');
Write_Log(2, 'Inserted ' || l_mv6_count || ' rows into JTF_TERR_CNRG_LIKE_MV ');