[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
TYPE g_last_updated_by_tab IS TABLE OF jtf_tty_terr_groups.last_updated_by%TYPE;
TYPE g_last_update_date_tab IS TABLE OF jtf_tty_terr_groups.last_update_date%TYPE;
TYPE g_last_update_login_tab IS TABLE OF jtf_tty_terr_groups.last_update_login%TYPE;
/* (1) START: DELETE ALL EXISTING NAMED ACCOUNT AND GEOGRAPHY TERRITORIES */
PROCEDURE cleanup_na_territories ( p_mode VARCHAR2 )
IS
BEGIN
IF G_Debug THEN
Write_Log(2, 'Start deleting all the territories');
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 AND GEOGRAPHY TERRITORIES */
IF G_Debug THEN
Write_Log(2, 'Finish deleting all the territories');
This procedure will delete territories corresponding to a particulat self service geography territory
----------------------------------------------------------------------------------------------------*/
PROCEDURE delete_geo_terr(p_geo_territory_id IN NUMBER)
IS
BEGIN
IF G_Debug THEN
Write_Log(2, 'Start deleting the territories corresponding to the self-service geography territory : ' || p_geo_territory_id);
DELETE FROM JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
( SELECT TERR_QUAL_ID
FROM JTF_TERR_QUAL_ALL A
,JTF_TERR_ALL B
WHERE B.GEO_TERRITORY_ID = p_geo_territory_id
AND B.TERR_ID = A.TERR_ID );
DELETE FROM JTF_TERR_QUAL_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );
DELETE FROM JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );
DELETE FROM JTF_TERR_USGS_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );
DELETE FROM JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
( SELECT TERR_RSC_ID
FROM JTF_TERR_RSC_ALL A
,JTF_TERR_ALL B
WHERE B.GEO_TERRITORY_ID = p_geo_territory_id
AND B.TERR_ID = A.TERR_ID );
DELETE FROM JTF_TERR_RSC_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );
DELETE FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id;
Write_Log(2, 'Error in procedure delete_geo_terr');
END delete_geo_terr;
This procedure will delete territories corresponding to a particulat territory group account
-------------------------------------------------------------------------------------------*/
PROCEDURE delete_TGA(p_terr_grp_acct_id IN NUMBER
,p_terr_group_id IN NUMBER
,p_catchall_terr_id IN NUMBER
,p_change_type IN VARCHAR2)
IS
BEGIN
IF G_Debug THEN
Write_Log(2, 'Start deleting the territories corresponding to the territory group account : ' || p_terr_grp_acct_id);
DELETE FROM JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
( SELECT TERR_QUAL_ID
FROM JTF_TERR_QUAL_ALL A
,JTF_TERR_ALL B
WHERE B.TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id
AND B.TERR_ID = A.TERR_ID );
DELETE FROM JTF_TERR_QUAL_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );
DELETE FROM JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );
DELETE FROM JTF_TERR_USGS_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );
DELETE FROM JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
( SELECT TERR_RSC_ID
FROM JTF_TERR_RSC_ALL A
,JTF_TERR_ALL B
WHERE B.TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id
AND B.TERR_ID = A.TERR_ID );
DELETE FROM JTF_TERR_RSC_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );
DELETE FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id;
/* if the user has deleted the TGA or update the mapping */
/* then delete the customer key name values from jtf_terr_values_all */
/* corresponding to the catch-all territory which are not present */
/* in jtf_tty_acct_qual_maps for the territory group */
/* but we do not need to do this if only sales team has been updated */
IF (p_change_type <> 'SALES_TEAM_UPDATE') THEN
DELETE FROM jtf_terr_values_all jtv
WHERE NOT EXISTS
(SELECT 1
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 = p_terr_group_id
AND B.qual_usg_id = -1012
AND B.COMPARISON_OPERATOR = jtv.COMPARISON_OPERATOR
AND B.VALUE1_CHAR = jtv.LOW_VALUE_CHAR)
AND terr_qual_id =
(SELECT terr_qual_id FROM jtf_terr_qual_all WHERE terr_id = p_catchall_terr_id);
Write_Log(2, 'Error in procedure delete_TGA');
END delete_TGA;
This procedure will delete territories from the JTF_TERR...
tables for the specified Terr Group Account Ids.
----------------------------------------------------------*/
PROCEDURE delete_bulk_TGA(p_terrGrpId_tbl IN jtf_terr_number_list,
p_grpAcctId_tbl IN jtf_terr_number_list,
p_change_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
idx integer;
'jtf.plsql.JTF_TTY_GEN_TERR_PVT.delete_bulk_TGA',
'Start of the procedure JTF_TTY_GEN_TERR_PVT.delete_bulk_TGA');
'jtf.plsql.JTF_TTY_GEN_TERR_PVT.delete_bulk_TGA',
'Deleting from JTF_TERR... tables');
DELETE from JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
( SELECT TERR_QUAL_ID
FROM JTF_TERR_QUAL_ALL A
,JTF_TERR_ALL B
WHERE B.TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx)
AND B.TERR_ID = A.TERR_ID );
DELETE from JTF_TERR_QUAL_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx) );
DELETE from JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx) );
DELETE from JTF_TERR_USGS_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx) );
DELETE from JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
( SELECT TERR_RSC_ID
FROM JTF_TERR_RSC_ALL A
,JTF_TERR_ALL B
WHERE B.TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx)
AND B.TERR_ID = A.TERR_ID );
DELETE from JTF_TERR_RSC_ALL Where TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx) );
DELETE from JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx);
/* if the user has deleted the TGA or update the mapping */
/* then delete the customer key name values from jtf_terr_values_all */
/* corresponding to the catch-all territory which are not present */
/* in jtf_tty_acct_qual_maps for the territory group */
/* but we do not need to do this if only sales team has been updated */
IF (p_change_type <> 'SALES_TEAM_UPDATE') THEN
forall idx in p_terrGrpId_tbl.FIRST .. p_terrGrpId_tbl.LAST
DELETE from jtf_terr_values_all jtv
WHERE NOT EXISTS
(SELECT 1
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 = p_terrGrpId_tbl(idx)
AND B.qual_usg_id = -1012
AND B.COMPARISON_OPERATOR = jtv.COMPARISON_OPERATOR
AND B.VALUE1_CHAR = jtv.LOW_VALUE_CHAR)
AND terr_qual_id =
( SELECT terr_qual_id
FROM jtf_terr_qual_all tqa,
jtf_terr_all ta
WHERE tqa.terr_id = ta.terr_id
AND ta.catch_all_flag='Y'
AND enabled_flag='Y'
AND SYSDATE BETWEEN ta.start_date_active AND NVL(ta.end_date_active, SYSDATE+1)
AND ta.terr_group_id = p_terrGrpId_tbl(idx)
);
'jtf.plsql.JTF_TTY_GEN_TERR_PVT.delete_bulk_TGA',
'End of the procedure JTF_TTY_GEN_TERR_PVT.delete_bulk_TGA');
'jtf.plsql.JTF_TTY_GEN_TERR_PVT.delete_bulk_TGA.OTHERS',
substr(x_msg_data, 1, 4000));
END delete_bulk_TGA;
This procedure will delete territories corresponding to the territory group accounts that have been deleted
----------------------------------------------------------------------------------------------------------*/
PROCEDURE process_TGA_delete
IS
/* Territory Group Accounts that are deleted */
CURSOR terr_grp_acct_delete(l_date DATE) IS
SELECT DISTINCT A.object_id
,B.terr_group_id
FROM jtf_tty_named_acct_changes A
,jtf_terr_all B
WHERE A.creation_date <= l_date
AND A.change_type = 'DELETE'
AND A.object_type = 'TGA'
AND A.object_id = B.terr_group_account_id
/* no need to process the deleted TGA if the corresponding TG has been updated */
AND NOT EXISTS (
SELECT 1
FROM jtf_tty_named_acct_changes F
WHERE F.object_type = 'TG'
AND F.object_id = B.terr_group_id
AND F.creation_date <= l_date);
Write_Log(2, 'open the cursor terr_grp_acct_delete');
OPEN terr_grp_acct_delete(g_cutoff_time);
FETCH terr_grp_acct_delete BULK COLLECT INTO
l_terr_group_account_id
,l_terr_group_id
LIMIT g_commit_chunk_size;
Write_Log(2, 'START: delete_TGA');
delete_TGA(l_terr_group_account_id(i)
,l_terr_group_id(i)
,l_terr_group_id(i) * -1
,'DELETE_TGA');
Write_Log(2, 'END: delete_TGA');
' have been deleted successfully.');
EXIT WHEN terr_grp_acct_delete%NOTFOUND;
CLOSE terr_grp_acct_delete;
Write_Log(2, 'Error in procedure process_TGA_delete');
IF (terr_grp_acct_delete%ISOPEN) THEN
CLOSE terr_grp_acct_delete;
END process_TGA_delete;
This procedure will delete territories corresponding to a particulat territory group
-----------------------------------------------------------------------------------*/
PROCEDURE delete_TG( p_terr_grp_id IN NUMBER,
p_terr_id IN VARCHAR2,
p_terr_creation_flag IN VARCHAR2)
IS
BEGIN
IF G_Debug THEN
Write_Log(2, 'Start deleting the territories for the territory group : ' || p_terr_grp_id);
DELETE FROM JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
( SELECT TERR_QUAL_ID
FROM JTF_TERR_QUAL_ALL A
,JTF_TERR_ALL B
WHERE B.TERR_GROUP_ID = p_terr_grp_id
AND B.TERR_ID = A.TERR_ID );
DELETE FROM JTF_TERR_QUAL_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );
DELETE FROM JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );
DELETE FROM JTF_TERR_USGS_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );
DELETE FROM JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
( SELECT TERR_RSC_ID
FROM JTF_TERR_RSC_ALL A
,JTF_TERR_ALL B
WHERE B.TERR_GROUP_ID = p_terr_grp_id
AND B.TERR_ID = A.TERR_ID );
DELETE FROM JTF_TERR_RSC_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );
DELETE FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id;
--Delete Territory Values
DELETE FROM JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
( SELECT TERR_QUAL_ID
FROM JTF_TERR_QUAL_ALL A
,JTF_TERR_ALL B
WHERE B.TERR_GROUP_ID = p_terr_grp_id
AND B.TERR_ID = A.TERR_ID
AND B.TERR_ID <> p_terr_id );
DELETE FROM JTF_TERR_QUAL_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL
WHERE TERR_GROUP_ID = p_terr_grp_id
AND TERR_ID <> p_terr_id
);
DELETE FROM JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL
WHERE TERR_GROUP_ID = p_terr_grp_id
);
DELETE FROM JTF_TERR_USGS_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL
WHERE TERR_GROUP_ID = p_terr_grp_id
-- AND TERR_ID <> p_terr_id
);
DELETE FROM JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
( SELECT TERR_RSC_ID
FROM JTF_TERR_RSC_ALL A
,JTF_TERR_ALL B
WHERE B.TERR_GROUP_ID = p_terr_grp_id
AND B.TERR_ID = A.TERR_ID
);
DELETE FROM JTF_TERR_RSC_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL
WHERE TERR_GROUP_ID = p_terr_grp_id
);
DELETE FROM JTF_TERR_ALL
WHERE TERR_GROUP_ID = p_terr_grp_id
AND TERR_ID <> p_terr_id;
Write_Log(2, 'delete_TG : Error in procedure delete_TG');
END delete_TG;
This procedure will delete territories corresponding to the territory groups that have been deleted
--------------------------------------------------------------------------------------------------*/
PROCEDURE process_TG_delete
IS
/* Territory Groups that are deleted */
CURSOR terr_grp_delete(l_date DATE) IS
/* Get the territory groups that have been physically deleted */
SELECT A.object_id
FROM jtf_tty_named_acct_changes A
WHERE A.creation_date <= l_date
AND A.change_type = 'DELETE'
AND A.object_type = 'TG'
UNION
/* Get the territory groups which has expired as end_date_active < sysdate */
SELECT DISTINCT A.terr_group_id
FROM jtf_terr_all A
WHERE A.terr_group_flag = 'Y'
AND A.end_date_active < l_date;
Write_Log(2, 'Open the cursor terr_grp_delete');
OPEN terr_grp_delete(g_cutoff_time);
FETCH terr_grp_delete BULK COLLECT INTO
l_object_id
LIMIT g_commit_chunk_size;
Write_Log(2, 'START: delete_TG');
delete_TG(l_object_id(i), NULL, NULL);
Write_Log(2, 'END: delete_TG');
' have been deleted successfully.');
EXIT WHEN terr_grp_delete%NOTFOUND;
CLOSE terr_grp_delete;
Write_Log(2, 'Error in procedure process_TG_delete');
IF (terr_grp_delete%ISOPEN) THEN
CLOSE terr_grp_delete;
END process_TG_delete;
,p_last_updated_by IN g_last_updated_by_tab
,p_last_update_date IN g_last_update_date_tab
,p_last_update_login IN g_last_update_login_tab
,p_org_id IN g_org_id_tab
,p_terr_id IN g_terr_id_tab
,p_overlay_top IN g_terr_id_tab)
IS
l_terr_all_rec JTF_TERRITORY_PVT.terr_all_rec_type;
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_geo_terr gtr
, jtf_tty_terr_grp_roles tgr
, jtf_tty_role_access ra
WHERE grsc.GEO_TERRITORY_ID = l_geo_territory_id
AND gtr.geo_territory_id = grsc.geo_territory_id
AND grsc.rsc_role_code = tgr.role_code
AND tgr.terr_group_id = gtr.terr_group_id
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_geo_terr gtr
, jtf_tty_terr_grp_roles tgr
, jtf_tty_role_access ra
WHERE grsc.GEO_TERRITORY_ID = l_geo_territory_id
AND gtr.geo_territory_id = grsc.geo_territory_id
AND grsc.rsc_role_code = tgr.role_code
AND tgr.terr_group_id = gtr.terr_group_id
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 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'
UNION
/* postal code range */
SELECT g.postal_code postal_code
,g.geo_id geo_id
,terr.geo_territory_id 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
jtf_tty_geographies g1,
jtf_tty_geographies g2
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 = 'BETWEEN'
AND g1.geo_id = grpv.geo_id_from
AND g2.geo_id = grpv.geo_id_to
AND g.geo_name BETWEEN g1.geo_name AND g2.geo_name
UNION
SELECT g.postal_code postal_code
,g.geo_id geo_id
,terr.geo_territory_id 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 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
,a.product_category_id
,a.product_category_set_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.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);
delete_geo_terr(p_geo_territory_id(x));
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
, p_terr_group_id(x)
, 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 1
INTO l_geo_count
FROM jtf_tty_geo_terr gterr
WHERE gterr.geo_territory_id = p_geo_territory_id(x)
AND ( gterr.parent_geo_terr_id < 0
OR EXISTS (
SELECT 1
FROM jtf_tty_geo_terr_values gtval
WHERE gterr.geo_territory_id = gtval.geo_territory_id));
l_terr_all_rec.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_all_rec.LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
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_LAST_UPDATE_DATE(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qual_tbl(j).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_UPDATED_BY(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_UPDATE_DATE(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_UPDATE_LOGIN(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, CATCH_ALL_FLAG = 'N'
, GEO_TERR_FLAG = 'Y'
, GEO_TERRITORY_ID = p_geo_territory_id(x)
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_last_update_date(x);
l_TerrRsc_Tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
/* 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 = p_TERR_GROUP_ID(x)
AND EXISTS (
/* Geography Territory 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_LAST_UPDATE_DATE(x);
l_terr_all_rec.LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
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_LAST_UPDATE_DATE(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
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_LAST_UPDATE_DATE(x);
l_terr_qual_tbl(j).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, CATCH_ALL_FLAG = 'N'
, GEO_TERR_FLAG = 'Y'
, GEO_TERRITORY_ID = p_geo_territory_id(x)
WHERE terr_id = x_terr_id;
l_terr_all_rec.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_all_rec.LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
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_LAST_UPDATE_DATE(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qual_tbl(i).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qual_tbl(i).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, CATCH_ALL_FLAG = 'N'
, GEO_TERR_FLAG = 'Y'
, GEO_TERRITORY_ID = p_geo_territory_id(x)
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_last_update_date(x);
l_TerrRsc_Tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
,p_last_updated_by IN g_last_updated_by_tab
,p_last_update_date IN g_last_update_date_tab
,p_last_update_login IN g_last_update_login_tab
,p_num_winners IN g_num_winners_tab
,p_org_id IN g_org_id_tab
,p_change_type IN g_change_type_tab)
IS
l_terr_all_rec JTF_TERRITORY_PVT.terr_all_rec_type;
SELECT gterr.geo_territory_id
, gterr.geo_terr_name
FROM jtf_tty_geo_terr gterr
WHERE gterr.terr_group_id = l_terr_group_id
AND ( gterr.parent_geo_terr_id < 0
OR EXISTS (
SELECT 1
FROM jtf_tty_geo_terr_values gtval
WHERE gterr.geo_territory_id = gtval.geo_territory_id));
SELECT ra.access_type
FROM
JTF_TTY_GEO_TERR_RSC grsc
, jtf_tty_geo_terr gtr
, jtf_tty_terr_grp_roles tgr
, jtf_tty_role_access ra
WHERE grsc.GEO_TERRITORY_ID = l_geo_territory_id
AND gtr.geo_territory_id = grsc.geo_territory_id
AND grsc.rsc_role_code = tgr.role_code
AND tgr.terr_group_id = gtr.terr_group_id
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_geo_terr gtr
, jtf_tty_terr_grp_roles tgr
, jtf_tty_role_access ra
WHERE grsc.GEO_TERRITORY_ID = l_geo_territory_id
AND gtr.geo_territory_id = grsc.geo_territory_id
AND grsc.rsc_role_code = tgr.role_code
AND tgr.terr_group_id = gtr.terr_group_id
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 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'
UNION
/* postal code range */
SELECT g.postal_code postal_code
,g.geo_id geo_id
,terr.geo_territory_id 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
jtf_tty_geographies g1,
jtf_tty_geographies g2
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 = 'BETWEEN'
AND g1.geo_id = grpv.geo_id_from
AND g2.geo_id = grpv.geo_id_to
AND g.geo_name BETWEEN g1.geo_name AND g2.geo_name
UNION
SELECT g.postal_code postal_code
,g.geo_id geo_id
,terr.geo_territory_id 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 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
,a.product_category_id
,a.product_category_set_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.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);
IF (p_change_type(x) = 'UPDATE') THEN
IF G_Debug THEN
Write_Log(2, 'create_geo_terr_for_TG : START: delete_TG');
delete_TG(p_terr_group_id(x), NULL, NULL);
Write_Log(2, 'create_geo_terr_for_TG : END: delete_TG');
' have been deleted successfully.');
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
, p_terr_group_id(x)
, 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 tgrp
, jtf_tty_geo_grp_values gterr
WHERE tgrp.terr_group_id = gterr.terr_group_id
AND tgrp.terr_group_id = p_terr_group_id(x)
AND ROWNUM < 2;
L_TERR_ALL_REC.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
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 := p_LAST_UPDATE_DATE(x);
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 := p_LAST_UPDATE_DATE(x);
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 := p_LAST_UPDATE_DATE(x);
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 := p_LAST_UPDATE_DATE(x);
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 = p_TERR_GROUP_ID(x)
, NUM_WINNERS = p_NUM_WINNERS(x)
WHERE TERR_ID = X_TERR_ID;
l_terr_all_rec.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_all_rec.LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
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_LAST_UPDATE_DATE(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qual_tbl(j).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_UPDATED_BY(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_UPDATE_DATE(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_UPDATE_LOGIN(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, CATCH_ALL_FLAG = 'N'
, GEO_TERR_FLAG = 'Y'
, GEO_TERRITORY_ID = geo_terr.geo_territory_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_last_update_date(x);
l_TerrRsc_Tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
/* 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 = p_TERR_GROUP_ID(x)
AND EXISTS (
/* Geography territory 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_LAST_UPDATE_DATE(x);
l_terr_all_rec.LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
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_LAST_UPDATE_DATE(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qual_tbl(j).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
UPDATE JTF_TERR_ALL
SET terr_group_FLAG = 'Y'
, terr_group_ID = p_TERR_GROUP_ID(x)
WHERE terr_id = x_terr_id;
l_terr_all_rec.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_all_rec.LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
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_LAST_UPDATE_DATE(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
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_LAST_UPDATE_DATE(x);
l_terr_qual_tbl(j).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, CATCH_ALL_FLAG = 'N'
, GEO_TERR_FLAG = 'Y'
, GEO_TERRITORY_ID = overlayterr.geo_territory_id
WHERE terr_id = x_terr_id;
l_terr_all_rec.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_all_rec.LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
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_LAST_UPDATE_DATE(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qual_tbl(i).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_qual_tbl(i).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_LAST_UPDATED_BY(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_LAST_UPDATE_DATE(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, CATCH_ALL_FLAG = 'N'
, GEO_TERR_FLAG = 'Y'
, GEO_TERRITORY_ID = overlayterr.geo_territory_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_last_update_date(x);
l_TerrRsc_Tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
,p_last_updated_by IN g_last_updated_by_tab
,p_last_update_date IN g_last_update_date_tab
,p_last_update_login IN g_last_update_login_tab
,p_org_id IN g_org_id_tab
,p_terr_id IN g_terr_id_tab
,p_overlay_top IN g_terr_id_tab
,p_catchall_terr_id IN g_terr_id_tab
,p_change_type IN g_change_type_tab
,p_terr_attr_cat IN g_terr_attr_cat_tab
,p_terr_attribute1 IN g_terr_attribute_tab
,p_terr_attribute2 IN g_terr_attribute_tab
,p_terr_attribute3 IN g_terr_attribute_tab
,p_terr_attribute4 IN g_terr_attribute_tab
,p_terr_attribute5 IN g_terr_attribute_tab
,p_terr_attribute6 IN g_terr_attribute_tab
,p_terr_attribute7 IN g_terr_attribute_tab
,p_terr_attribute8 IN g_terr_attribute_tab
,p_terr_attribute9 IN g_terr_attribute_tab
,p_terr_attribute10 IN g_terr_attribute_tab
,p_terr_attribute11 IN g_terr_attribute_tab
,p_terr_attribute12 IN g_terr_attribute_tab
,p_terr_attribute13 IN g_terr_attribute_tab
,p_terr_attribute14 IN g_terr_attribute_tab
,p_terr_attribute15 IN g_terr_attribute_tab)
IS
TYPE role_typ IS RECORD(
grp_role_id NUMBER:=FND_API.G_MISS_NUM
);
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 tga.terr_group_id = tgr.terr_group_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 tga.terr_group_id = tgr.terr_group_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
AND b.role_code = l_role
ORDER BY a.access_type ;
SELECT DISTINCT a.access_type, a.trans_access_code
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
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
,a.product_category_id
,a.product_category_set_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.resource_id
, b.rsc_group_id
, b.rsc_resource_type
, b.start_date
, b.end_date
, to_char(null) attribute_category
, b.attribute1 attribute1
, b.attribute2 attribute2
, b.attribute3 attribute3
, b.attribute4 attribute4
, b.attribute5 attribute5
, to_char(null) attribute6
, to_char(null) attribute7
, to_char(null) attribute8
, to_char(null) attribute9
, to_char(null) attribute10
, to_char(null) attribute11
, to_char(null) attribute12
, to_char(null) attribute13
, to_char(null) attribute14
, to_char(null) attribute15
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
, c.start_date
, c.end_date
FROM hz_parties a
, jtf_tty_named_accts b
, jtf_tty_terr_grp_accts c
WHERE c.terr_group_account_id = LP_terr_group_acct_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 OR l_matching_rule_code = '4' OR l_matching_rule_code = '5')
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 /*+ index(b JTF_TTY_NAMED_ACCTS_U1) */ SUBSTR(a.party_name, 1, 45) || ': ' || a.postal_code name
, b.named_account_id
, c.terr_group_account_id
, a.duns_number_c
, c.start_date
, c.end_date
FROM hz_parties a
, jtf_tty_named_accts b
, jtf_tty_terr_grp_accts c
WHERE c.terr_group_account_id = LP_terr_group_acct_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
FROM hz_parties a
, jtf_tty_named_accts b
, jtf_tty_terr_grp_accts c
WHERE c.terr_group_account_id = LP_terr_group_acct_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 OR lp_matching_rule_code = '4' OR lp_matching_rule_code = '5')
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_account_id = LP_terr_group_acct_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 DECODE(l_matching_rule_code, '4', -1129, '2', -1120, '3', -1120, '5',-1005, -1001) qual_usg_id
, '=' comparison_operator
, DECODE(l_matching_rule_code, '4', hzp.party_number, '2', hzp.duns_number_c, '3', hzp.duns_number_c) value1_char
, DECODE(l_matching_rule_code, '5', na.party_site_id, hzp.party_id) value1_num
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 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 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);
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_account_id = l_terr_group_acct_id
AND b.qual_usg_id = -1012
ORDER BY b.comparison_operator,b.value1_char;
delete_TGA(p_terr_grp_acct_id(x)
,p_terr_group_id(x)
,p_catchall_terr_id(x)
,p_change_type(x));
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
, p_terr_group_id(x)
, 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_grp_accts ga
, jtf_tty_named_accts a
WHERE ga.named_account_id = a.named_account_id
AND ga.terr_group_account_id = p_terr_grp_acct_id(x)
AND ROWNUM < 2;
SELECT terr_qual_id
INTO l_terr_qual_id
FROM jtf_terr_qual_all
WHERE terr_id = p_catchall_terr_id(x)
AND qual_usg_id = -1012;
SELECT COUNT(*)
INTO l_cust_count
FROM jtf_terr_values_all
WHERE comparison_operator = catchall.comparison_operator
AND low_value_char = catchall.value1_char
AND terr_qual_id = l_terr_qual_id;
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
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 := p_last_update_date(x);
l_terr_all_rec.LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qual_tbl(j).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, 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 := p_last_update_date(x);
l_TerrRsc_Tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
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 := p_last_update_date(x);
l_terr_all_rec.LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qual_tbl(j).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, 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 := p_last_update_date(x);
l_TerrRsc_Tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
/* 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 = p_terr_group_id(x)
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 := p_last_update_date(x);
l_terr_all_rec.LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_qual_tbl(j).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_TERR_GROUP_ID(x)
, 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 := p_last_update_date(x);
l_terr_all_rec.LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qual_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qual_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, 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 := p_last_update_date(x);
l_TerrRsc_Tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_all_rec.LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_all_rec.LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_qual_tbl(j).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, 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 := p_last_update_date(x);
l_terr_all_rec.LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qual_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qual_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_TERR_GROUP_ID(x)
, 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 := p_last_update_date(x);
l_TerrRsc_Tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
,p_last_updated_by IN g_last_updated_by_tab
,p_last_update_date IN g_last_update_date_tab
,p_last_update_login IN g_last_update_login_tab
,p_catch_all_resource_id IN g_catch_all_resource_id_tab
,p_catch_all_resource_type IN g_catch_all_resource_type_tab
,p_generate_catchall_flag IN g_generate_catchall_flag_tab
,p_num_winners IN g_num_winners_tab
,p_org_id IN g_org_id_tab
,p_change_type IN g_change_type_tab
,p_terr_type_id IN VARCHAR2
,p_terr_id IN VARCHAR2
,p_terr_creation_flag IN VARCHAR2
)
IS
TYPE role_typ IS RECORD(
grp_role_id NUMBER:=FND_API.G_MISS_NUM
);
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 tga.terr_group_id = tgr.terr_group_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 tga.terr_group_id = tgr.terr_group_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
AND b.role_code = l_role
ORDER BY a.access_type ;
SELECT DISTINCT a.access_type, a.trans_access_code
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
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
,a.product_category_id
,a.product_category_set_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.resource_id
, b.rsc_group_id
, b.rsc_resource_type
, b.start_date
, b.end_date
, to_char(null) attribute_category
, b.attribute1 attribute1
, b.attribute2 attribute2
, b.attribute3 attribute3
, b.attribute4 attribute4
, b.attribute5 attribute5
, to_char(null) attribute6
, to_char(null) attribute7
, to_char(null) attribute8
, to_char(null) attribute9
, to_char(null) attribute10
, to_char(null) attribute11
, to_char(null) attribute12
, to_char(null) attribute13
, to_char(null) attribute14
, to_char(null) attribute15
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
, c.start_date
, c.end_date
, to_char(null) attribute_category
, c.attribute1
, c.attribute2
, c.attribute3
, c.attribute4
, c.attribute5
, c.attribute6
, c.attribute7
, c.attribute8
, c.attribute9
, c.attribute10
, c.attribute11
, c.attribute12
, c.attribute13
, c.attribute14
, c.attribute15
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 OR l_matching_rule_code = '4')
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 /*+ index(b JTF_TTY_NAMED_ACCTS_U1) */ SUBSTR(a.party_name, 1, 45) || ': ' || a.postal_code name
, b.named_account_id
, c.terr_group_account_id
, a.duns_number_c
, c.start_date
, c.end_date
, to_char(null) attribute_category
, c.attribute1
, c.attribute2
, c.attribute3
, c.attribute4
, c.attribute5
, c.attribute6
, c.attribute7
, c.attribute8
, c.attribute9
, c.attribute10
, c.attribute11
, c.attribute12
, c.attribute13
, c.attribute14
, c.attribute15
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
, to_char(null) attribute_category
, c.attribute1
, c.attribute2
, c.attribute3
, c.attribute4
, c.attribute5
, c.attribute6
, c.attribute7
, c.attribute8
, c.attribute9
, c.attribute10
, c.attribute11
, c.attribute12
, c.attribute13
, c.attribute14
, c.attribute15
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 OR l_matching_rule_code = '4')
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
, to_char(null) attribute_category
, c.attribute1
, c.attribute2
, c.attribute3
, c.attribute4
, c.attribute5
, c.attribute6
, c.attribute7
, c.attribute8
, c.attribute9
, c.attribute10
, c.attribute11
, c.attribute12
, c.attribute13
, c.attribute14
, c.attribute15
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 DECODE(l_matching_rule_code, '4', -1129, '2', -1120, '3', -1120, '5',-1005, -1001) qual_usg_id
, '=' comparison_operator
, DECODE(l_matching_rule_code, '4', hzp.party_number, '2', hzp.duns_number_c, '3', hzp.duns_number_c) value1_char
, DECODE(l_matching_rule_code, '5', na.party_site_id, hzp.party_id) value1_num
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 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 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 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);
IF (p_change_type(x) = 'UPDATE') THEN
IF G_Debug THEN
Write_Log(2, 'create_na_terr_for_TG : START: delete_TG');
delete_TG(p_terr_group_id(x), p_terr_id ,p_terr_creation_flag );
Write_Log(2, 'create_na_terr_for_TG : END: delete_TG');
' have been deleted successfully.');
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
, p_terr_group_id(x)
, 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 = p_terr_group_id(x)
AND ROWNUM < 2;*/
L_TERR_ALL_REC.LAST_UPDATE_DATE := p_last_update_date(x);
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 := p_last_update_date(x);
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 := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, CATCH_ALL_FLAG = 'N'
, TERR_GROUP_ID = p_terr_group_id(x)
, NUM_WINNERS = p_num_winners(x)
WHERE TERR_ID = X_TERR_ID;
L_TERR_ALL_REC.LAST_UPDATE_DATE := p_last_update_date(x);
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 := p_last_update_date(x);
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 := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(1).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qual_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, 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 := p_last_update_date(x);
l_TerrRsc_Tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
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 := p_last_update_date(x);
l_terr_all_rec.LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qual_tbl(j).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, 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 := p_last_update_date(x);
l_TerrRsc_Tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
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 := p_last_update_date(x);
l_terr_all_rec.LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qual_tbl(j).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, 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 := p_last_update_date(x);
l_TerrRsc_Tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
/* 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 = p_terr_group_id(x)
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 := p_last_update_date(x);
l_terr_all_rec.LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(j).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qual_tbl(j).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_TERR_GROUP_ID(x)
WHERE terr_id = x_terr_id;
l_terr_all_rec.LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_all_rec.LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_qual_tbl(j).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_TERR_GROUP_ID(x)
, 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 := p_last_update_date(x);
l_terr_all_rec.LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qual_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qual_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, 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 := p_last_update_date(x);
l_TerrRsc_Tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_all_rec.LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_all_rec.LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(2).LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_qual_tbl(j).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(j).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_terr_group_id(x)
, 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 := p_last_update_date(x);
l_terr_all_rec.LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_all_rec.LAST_UPDATE_LOGIN := p_last_update_login(x);
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_last_update_date(x);
l_terr_usgs_tbl(1).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_usgs_tbl(1).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qual_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qualtypeusgs_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_QUAL_S.NEXTVAL
INTO l_terr_qual_id
FROM DUAL;
l_terr_qual_tbl(i).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_qual_tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_qual_tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
l_terr_values_tbl(k).LAST_UPDATED_BY := p_last_updated_by(x);
l_terr_values_tbl(k).LAST_UPDATE_DATE := p_last_update_date(x);
l_terr_values_tbl(k).LAST_UPDATE_LOGIN := p_last_update_login(x);
UPDATE JTF_TERR_ALL
SET TERR_GROUP_FLAG = 'Y'
, TERR_GROUP_ID = p_TERR_GROUP_ID(x)
, 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 := p_last_update_date(x);
l_TerrRsc_Tbl(i).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := p_last_update_date(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := p_last_updated_by(x);
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := p_last_update_login(x);
This procedure will delete and recreate the deafult territory corresponding to geo territory
-------------------------------------------------------------------------------------------*/
PROCEDURE process_parent_geo_terr(p_geo_territory_id IN NUMBER)
IS
CURSOR geo_default_terr(l_geo_territory_id NUMBER) IS
SELECT B.geo_territory_id
,B.geo_terr_name
,B.terr_group_id
,C.rank
,C.active_from_date
,C.active_to_date
,C.created_by
,C.creation_date
,C.last_updated_by
,C.last_update_date
,C.last_update_login
,D.org_id
,-1 * B.terr_group_id
,E.terr_id
FROM jtf_tty_geo_terr B
,jtf_tty_terr_groups C
,jtf_terr_all D
,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch
WHERE B.geo_territory_id = l_geo_territory_id
AND B.terr_group_id = C.terr_group_id
AND C.parent_terr_id = D.terr_id
AND E.terr_group_id(+) = C.terr_group_id -- outer-join is necessary as overlay branch may not exist
AND E.name(+) = C.terr_group_name || ' (OVERLAY)'
AND E.terr_group_flag(+) = 'Y';
l_last_updated_by g_last_updated_by_tab;
l_last_update_date g_last_update_date_tab;
l_last_update_login g_last_update_login_tab;
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_org_id
,l_terr_id
,l_overlay_top
LIMIT g_commit_chunk_size;
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_org_id
,l_terr_id
,l_overlay_top);
l_last_updated_by.TRIM(l_no_of_records);
l_last_update_date.TRIM(l_no_of_records);
l_last_update_login.TRIM(l_no_of_records);
This procedure will create/update territories corresponding to
the geography territories that have been updated
-------------------------------------------------------------*/
PROCEDURE create_terr_for_gt(p_geo_terr_id IN NUMBER,
p_from_where IN VARCHAR2)
IS
CURSOR geo_terr_update(l_geo_terr_id NUMBER) IS
SELECT p_from_where from_where
,p_geo_terr_id geo_territory_id
,B.geo_terr_name
,B.terr_group_id
,C.rank
,C.active_from_date
,C.active_to_date
,C.created_by
,C.creation_date
,C.last_updated_by
,C.last_update_date
,C.last_update_login
,D.org_id
,-1 * B.terr_group_id
,E.terr_id
FROM jtf_tty_geo_terr B
,jtf_tty_terr_groups C
,jtf_terr_all D
,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch
WHERE B.geo_territory_id = l_geo_terr_id
AND B.terr_group_id = C.terr_group_id
AND C.parent_terr_id = D.terr_id
AND E.terr_group_id(+) = C.terr_group_id -- outer-join is necessary as overlay branch may not exist
AND E.name(+) = C.terr_group_name || ' (OVERLAY)'
AND E.terr_group_flag(+) = 'Y';
/* All the child territories of the territories that have been updated */
CURSOR child_terr(l_geo_terr_id NUMBER) IS
SELECT geo_territory_id
FROM JTF_TTY_GEO_TERR
WHERE geo_territory_id <> l_geo_terr_id
START WITH geo_territory_id = l_geo_terr_id
CONNECT BY PRIOR geo_territory_id = parent_geo_terr_id;
l_last_updated_by g_last_updated_by_tab;
l_last_update_date g_last_update_date_tab;
l_last_update_login g_last_update_login_tab;
Write_Log(2, 'Open the cursor geo_terr_update');
OPEN geo_terr_update(p_geo_terr_id);
FETCH geo_terr_update BULK COLLECT INTO
l_from_where
,l_geo_territory_id
,l_geo_terr_name
,l_terr_group_id
,l_rank
,l_active_from_date
,l_active_to_date
,l_created_by
,l_creation_date
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_org_id
,l_terr_id
,l_overlay_top
LIMIT g_commit_chunk_size;
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_org_id
,l_terr_id
,l_overlay_top);
IF (l_from_where(i) = 'Update Mapping') THEN
BEGIN
/* Get the parent territory id and recreate it */
SELECT A.parent_geo_terr_id
INTO l_parent_geo_terr_id
FROM jtf_tty_geo_terr A
WHERE A.geo_territory_id = l_geo_territory_id(i);
END IF; /* end if l_from_where(i) = 'Update Mapping' */
l_last_updated_by.TRIM(l_no_of_records);
l_last_update_date.TRIM(l_no_of_records);
l_last_update_login.TRIM(l_no_of_records);
EXIT WHEN geo_terr_update%NOTFOUND;
CLOSE geo_terr_update;
IF (p_from_where = 'Update Geography Territory Sales Team') THEN
COMMIT;
Write_Log(2, 'Error in procedure process_GT_update');
IF (geo_terr_update%ISOPEN) THEN
CLOSE geo_terr_update;
This procedure will create/update territories corresponding to
the territory group account that have been updated
-------------------------------------------------------------*/
PROCEDURE process_TGA_update
IS
/* Territory groups that are created/updated */
CURSOR terr_grp_acct(l_date DATE) IS
SELECT A.terr_group_account_id
,C.terr_group_id
,C.rank
,C.active_from_date
,C.active_to_date
,C.matching_rule_code
,C.generate_catchall_flag
,C.created_by
,C.creation_date
,C.last_updated_by
,C.last_update_date
,C.last_update_login
,D.ORG_ID
,F.terr_id -- placeholder territory for non-overlay branch
,E.terr_id -- placeholder territory for overlay branch
,C.terr_group_id * -1 -- catch-all territory id
-- if the change type is both sales team update and mapping update , we need to process only mapping update
,MIN(A.change_type) change_type
,to_char(null) -- attribute_category
,B.attribute1
,B.attribute2
,B.attribute3
,B.attribute4
,B.attribute5
,B.attribute6
,B.attribute7
,B.attribute8
,B.attribute9
,B.attribute10
,B.attribute11
,B.attribute12
,B.attribute13
,B.attribute14
,B.attribute15
FROM (
/* Get the territory group account for which the sales team has been updated */
SELECT A.OBJECT_ID terr_group_account_id
,'SALES_TEAM_UPDATE' change_type
FROM JTF_TTY_NAMED_ACCT_CHANGES A
WHERE A.creation_date <= l_date
AND A.change_type = 'UPDATE'
AND A.object_type = 'TGA'
AND NOT EXISTS
/* if the territory group account is already deleted , no need to process the insert/update */
( SELECT 1
FROM jtf_tty_named_acct_changes B
WHERE A.object_id = B.object_id
AND B.object_type = 'TGA'
AND B.change_type = 'DELETE')
UNION
/* Get the territory group account for which the mapping **
** of the corresponding named account has been updated */
SELECT B.terr_group_account_id terr_group_account_id
,'MAPPING_UPDATE' change_type
FROM jtf_tty_named_acct_changes A
,jtf_tty_terr_grp_accts B
WHERE A.creation_date <= l_date
AND A.change_type = 'UPDATE'
AND A.object_type = 'NA'
AND A.object_id = B.named_account_id) A
,jtf_tty_terr_grp_accts B
,jtf_tty_terr_groups C
,jtf_terr_all D -- to get the org_id of the parent territory
,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch
,jtf_terr_all F -- to get the terr_id for the placeholder territory of non-overlay branch
WHERE A.terr_group_account_id = B.terr_group_account_id
AND B.terr_group_id = C.terr_group_id
AND C.parent_terr_id = D.terr_id
AND E.terr_group_id(+) = C.terr_group_id -- outer-join is necessary as overlay branch may not exist
AND E.name(+) = C.terr_group_name || ' (OVERLAY)'
AND E.terr_group_flag(+) = 'Y'
AND F.terr_group_id = C.terr_group_id -- outer-join is necessary as overlay branch may not exist
AND F.name = C.terr_group_name
AND F.terr_group_flag = 'Y'
AND NVL(F.named_account_flag ,'N') <> 'Y'
/* no need to process the TGA if it is part of the TG that has been updated */
AND NOT EXISTS (
SELECT 1
FROM jtf_tty_named_acct_changes F
WHERE F.object_type = 'TG'
AND F.object_id = C.terr_group_id
AND F.creation_date <= l_date)
GROUP BY
A.terr_group_account_id
,C.terr_group_id
,C.rank
,C.active_from_date
,C.active_to_date
,C.matching_rule_code
,C.generate_catchall_flag
,C.created_by
,C.creation_date
,C.last_updated_by
,C.last_update_date
,C.last_update_login
,D.ORG_ID
,F.terr_id
,E.terr_id
,C.terr_group_id * -1
,B.attribute1
,B.attribute2
,B.attribute3
,B.attribute4
,B.attribute5
,B.attribute6
,B.attribute7
,B.attribute8
,B.attribute9
,B.attribute10
,B.attribute11
,B.attribute12
,B.attribute13
,B.attribute14
,B.attribute15;
l_last_updated_by g_last_updated_by_tab;
l_last_update_date g_last_update_date_tab;
l_last_update_login g_last_update_login_tab;
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_ORG_ID
,l_terr_id
,l_overlay_top
,l_catchall_terr_id
,l_change_type
,l_terr_attr_cat
,l_terr_attribute1
,l_terr_attribute2
,l_terr_attribute3
,l_terr_attribute4
,l_terr_attribute5
,l_terr_attribute6
,l_terr_attribute7
,l_terr_attribute8
,l_terr_attribute9
,l_terr_attribute10
,l_terr_attribute11
,l_terr_attribute12
,l_terr_attribute13
,l_terr_attribute14
,l_terr_attribute15
LIMIT g_commit_chunk_size;
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_ORG_ID
,l_terr_id
,l_overlay_top
,l_catchall_terr_id
,l_change_type
,l_terr_attr_cat
,l_terr_attribute1
,l_terr_attribute2
,l_terr_attribute3
,l_terr_attribute4
,l_terr_attribute5
,l_terr_attribute6
,l_terr_attribute7
,l_terr_attribute8
,l_terr_attribute9
,l_terr_attribute10
,l_terr_attribute11
,l_terr_attribute12
,l_terr_attribute13
,l_terr_attribute14
,l_terr_attribute15);
l_last_updated_by.TRIM(l_no_of_records);
l_last_update_date.TRIM(l_no_of_records);
l_last_update_login.TRIM(l_no_of_records);
Write_Log(2, 'Error in procedure process_TGA_update');
END process_TGA_update;
This procedure will create/update territories corresponding to
the territory groups that have been created or updated
----------------------------------------------------------*/
PROCEDURE process_TG_update
IS
/* Named Account Territory groups that are created/updated */
CURSOR na_terr_grp(l_date DATE) IS
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
, B.ORG_ID
, MIN(C.change_type) change_type
-- if there is both insert and update to a territory group , we need to process only the insert
FROM JTF_TTY_TERR_GROUPS A
,JTF_TERR_ALL B
,JTF_TTY_NAMED_ACCT_CHANGES C
WHERE C.creation_date <= l_date
AND C.change_type IN ('INSERT', 'UPDATE')
AND C.object_type = 'TG'
AND C.object_id = A.terr_group_id
AND A.parent_terr_id = B.terr_id
AND A.self_service_type = 'NAMED_ACCOUNT'
AND NOT EXISTS
/* if the territory group is already deleted , no need to process the insert/update */
( SELECT 1
FROM jtf_tty_named_acct_changes D
WHERE D.object_id = C.object_id
AND D.object_type = 'TG'
AND D.change_type = 'DELETE')
GROUP BY
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
, B.ORG_ID;
/* Geography Territory groups that are created/updated */
CURSOR geo_terr_grp(l_date DATE) IS
SELECT A.TERR_GROUP_ID
, A.TERR_GROUP_NAME
, A.RANK
, A.ACTIVE_FROM_DATE
, A.ACTIVE_TO_DATE
, A.PARENT_TERR_ID
, A.CREATED_BY
, A.CREATION_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_DATE
, A.LAST_UPDATE_LOGIN
, A.NUM_WINNERS
, B.ORG_ID
, MIN(C.change_type) change_type
-- if there is both insert and update to a territory group , we need to process only the insert
FROM JTF_TTY_TERR_GROUPS A
,JTF_TERR_ALL B
,JTF_TTY_NAMED_ACCT_CHANGES C
WHERE C.creation_date <= l_date
AND C.change_type IN ('INSERT', 'UPDATE')
AND C.object_type = 'TG'
AND C.object_id = A.terr_group_id
AND A.parent_terr_id = B.terr_id
AND A.self_service_type = 'GEOGRAPHY'
AND NOT EXISTS
/* if the territory group is already deleted , no need to process the insert/update */
( SELECT 1
FROM jtf_tty_named_acct_changes D
WHERE D.object_id = C.object_id
AND D.object_type = 'TG'
AND D.change_type = 'DELETE')
GROUP BY
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
, B.ORG_ID;
l_last_updated_by g_last_updated_by_tab;
l_last_update_date g_last_update_date_tab;
l_last_update_login g_last_update_login_tab;
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_catch_all_resource_id
,l_catch_all_resource_type
,l_generate_catchall_flag
,l_num_winners
,l_org_id
,l_change_type
LIMIT g_commit_chunk_size;
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_catch_all_resource_id
,l_catch_all_resource_type
,l_generate_catchall_flag
,l_num_winners
,l_org_id
,l_change_type
,NULL
,NULL
,NULL);
l_last_updated_by.TRIM(l_no_of_records);
l_last_update_date.TRIM(l_no_of_records);
l_last_update_login.TRIM(l_no_of_records);
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_num_winners
,l_org_id
,l_change_type
LIMIT g_commit_chunk_size;
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_num_winners
,l_org_id
,l_change_type);
l_last_updated_by.TRIM(l_no_of_records);
l_last_update_date.TRIM(l_no_of_records);
l_last_update_login.TRIM(l_no_of_records);
Write_Log(2, 'Error in procedure process_TG_update');
END process_TG_update;
This procedure will delete territories corresponding to the
geography territories that have been deleted
----------------------------------------------------------*/
PROCEDURE process_GT_delete
IS
/* Geography territories that are deleted */
CURSOR geo_terr_delete(l_date DATE) IS
SELECT DISTINCT A.object_id
FROM jtf_tty_named_acct_changes A
,jtf_terr_all B
WHERE A.creation_date <= l_date
AND A.change_type = 'DELETE'
AND A.object_type = 'GT'
AND A.object_id = B.geo_territory_id
/* no need to process the deleted GT if the corresponding TG has been updated */
AND NOT EXISTS (
SELECT 1
FROM jtf_tty_named_acct_changes F
WHERE F.object_type = 'TG'
AND F.object_id = B.terr_group_id
AND F.creation_date <= l_date);
/* Parent of the geography territories that are deleted */
CURSOR geo_terr_parent_delete(l_date DATE) IS
SELECT DISTINCT A.object_id
FROM jtf_tty_named_acct_changes A
,jtf_terr_all B
WHERE A.creation_date <= l_date
AND A.change_type = 'DELETE PARENT'
AND A.object_type = 'GT'
AND A.object_id = B.geo_territory_id
/* no need to process the GT if the corresponding TG has been updated */
AND NOT EXISTS (
SELECT 1
FROM jtf_tty_named_acct_changes F
WHERE F.object_type = 'TG'
AND F.object_id = B.terr_group_id
AND F.creation_date <= l_date);
Write_Log(2, 'open the cursor geo_terr_delete');
OPEN geo_terr_delete(g_cutoff_time);
FETCH geo_terr_delete BULK COLLECT INTO
l_geo_territory_id
LIMIT g_commit_chunk_size;
Write_Log(2, 'START: delete_geo_terr');
delete_geo_terr(l_geo_territory_id(i));
Write_Log(2, 'END: delete_geo_terr');
' have been deleted successfully.');
EXIT WHEN geo_terr_delete%NOTFOUND;
CLOSE geo_terr_delete;
Write_Log(2, 'open the cursor geo_terr_parent_delete');
OPEN geo_terr_parent_delete(g_cutoff_time);
FETCH geo_terr_parent_delete BULK COLLECT INTO
l_geo_territory_id
LIMIT g_commit_chunk_size;
/* Delete and recreate the default geography territory */
process_parent_geo_terr(l_geo_territory_id(i));
EXIT WHEN geo_terr_parent_delete%NOTFOUND;
CLOSE geo_terr_parent_delete;
Write_Log(2, 'Error in procedure process_GT_delete');
IF (geo_terr_delete%ISOPEN) THEN
CLOSE geo_terr_delete;
IF (geo_terr_parent_delete%ISOPEN) THEN
CLOSE geo_terr_parent_delete;
END process_GT_delete;
Write_Log(2, 'START: process_TG_delete');
process_TG_delete;
Write_Log(2, 'END: process_TG_delete');
Write_Log(2, 'START: process_TG_update');
process_TG_update;
Write_Log(2, 'END: process_TG_update');
Write_Log(2, 'START: process_TGA_delete');
process_TGA_delete;
Write_Log(2, 'END: process_TGA_delete');
Write_Log(2, 'START: process_GT_delete');
process_GT_delete;
Write_Log(2, 'END: process_GT_delete');
Write_Log(2, 'START: process_TGA_update');
process_TGA_update;
Write_Log(2, 'END: process_TGA_update');
Write_Log(2, 'START: process_GT_update');
Write_Log(2, 'END: process_GT_update');
DELETE jtf_tty_named_acct_changes
WHERE creation_date <= g_cutoff_time;
This procedure will delete all the territories and recreate
the territories for all the territory groups in total mode
----------------------------------------------------------*/
PROCEDURE terr_initial_load
IS
/* Active Named Account Territory Groups with active Top-Level Territories */
CURSOR na_terr_grp IS
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
, B.ORG_ID
, 'INSERT'
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
AND a.self_service_type = 'NAMED_ACCOUNT';
SELECT A.TERR_GROUP_ID
, A.TERR_GROUP_NAME
, A.RANK
, A.ACTIVE_FROM_DATE
, A.ACTIVE_TO_DATE
, A.PARENT_TERR_ID
, A.CREATED_BY
, A.CREATION_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_DATE
, A.LAST_UPDATE_LOGIN
, A.NUM_WINNERS
, B.ORG_ID
, 'INSERT'
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
AND a.self_service_type = 'GEOGRAPHY';
l_last_updated_by g_last_updated_by_tab;
l_last_update_date g_last_update_date_tab;
l_last_update_login g_last_update_login_tab;
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_catch_all_resource_id
,l_catch_all_resource_type
,l_generate_catchall_flag
,l_num_winners
,l_org_id
,l_change_type
LIMIT g_commit_chunk_size;
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_catch_all_resource_id
,l_catch_all_resource_type
,l_generate_catchall_flag
,l_num_winners
,l_org_id
,l_change_type
,NULL
,NULL
,NULL);
l_last_updated_by.TRIM(l_no_of_records);
l_last_update_date.TRIM(l_no_of_records);
l_last_update_login.TRIM(l_no_of_records);
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_num_winners
,l_org_id
,l_change_type
LIMIT g_commit_chunk_size;
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_num_winners
,l_org_id
,l_change_type);
l_last_updated_by.TRIM(l_no_of_records);
l_last_update_date.TRIM(l_no_of_records);
l_last_update_login.TRIM(l_no_of_records);
/* Territory groups that are created/updated */
CURSOR terr_grp_acct(l_terr_grp_acct_id NUMBER)
IS
SELECT B.terr_group_account_id
,C.terr_group_id
,C.rank
,C.active_from_date
,C.active_to_date
,C.matching_rule_code
,C.generate_catchall_flag
,C.created_by
,C.creation_date
,C.last_updated_by
,C.last_update_date
,C.last_update_login
,D.ORG_ID
,F.terr_id -- placeholder territory for non-overlay branch
,E.terr_id -- placeholder territory for overlay branch
,C.terr_group_id * -1 -- catch-all territory id
,'MAPPING_UPDATE' change_type
,to_char(null) -- attribute_category
,B.attribute1
,B.attribute2
,B.attribute3
,B.attribute4
,B.attribute5
,B.attribute6
,B.attribute7
,B.attribute8
,B.attribute9
,B.attribute10
,B.attribute11
,B.attribute12
,B.attribute13
,B.attribute14
,B.attribute15
FROM
jtf_tty_terr_grp_accts B
,jtf_tty_terr_groups C
,jtf_terr_all D -- to get the org_id of the parent territory
,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch
,jtf_terr_all F -- to get the terr_id for the placeholder territory of non-overlay branch
WHERE B.terr_group_account_id = l_terr_grp_acct_id
AND B.terr_group_id = C.terr_group_id
AND C.terr_group_id = D.terr_group_id
AND D.terr_group_flag = 'Y'
AND D.catch_all_flag = 'N'
AND nvl(D.named_account_flag,'N') = 'N'
AND E.terr_group_id(+) = C.terr_group_id -- outer-join is necessary as overlay branch may not exist
AND E.name(+) = C.terr_group_name || ' (OVERLAY)'
AND E.terr_group_flag(+) = 'Y'
AND F.terr_group_id = C.terr_group_id -- outer-join is necessary as overlay branch may not exist
AND F.name = C.terr_group_name
AND F.terr_group_flag = 'Y'
AND NVL(F.named_account_flag ,'N') <> 'Y';
l_last_updated_by g_last_updated_by_tab;
l_last_update_date g_last_update_date_tab;
l_last_update_login g_last_update_login_tab;
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_ORG_ID
,l_terr_id
,l_overlay_top
,l_catchall_terr_id
,l_change_type
,l_terr_attr_cat
,l_terr_attribute1
,l_terr_attribute2
,l_terr_attribute3
,l_terr_attribute4
,l_terr_attribute5
,l_terr_attribute6
,l_terr_attribute7
,l_terr_attribute8
,l_terr_attribute9
,l_terr_attribute10
,l_terr_attribute11
,l_terr_attribute12
,l_terr_attribute13
,l_terr_attribute14
,l_terr_attribute15
LIMIT g_commit_chunk_size;
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_ORG_ID
,l_terr_id
,l_overlay_top
,l_catchall_terr_id
,l_change_type
,l_terr_attr_cat
,l_terr_attribute1
,l_terr_attribute2
,l_terr_attribute3
,l_terr_attribute4
,l_terr_attribute5
,l_terr_attribute6
,l_terr_attribute7
,l_terr_attribute8
,l_terr_attribute9
,l_terr_attribute10
,l_terr_attribute11
,l_terr_attribute12
,l_terr_attribute13
,l_terr_attribute14
,l_terr_attribute15);
l_last_updated_by.TRIM(l_no_of_records);
l_last_update_date.TRIM(l_no_of_records);
l_last_update_login.TRIM(l_no_of_records);
and geography territory for update or created named account TG
or geography territory group.
----------------------------------------------------------*/
PROCEDURE create_terr_for_TG( p_terr_group_id IN NUMBER
,p_territory_type IN VARCHAR2
,p_change_type IN VARCHAR2
,p_terr_type_id IN VARCHAR2
,p_terr_id IN VARCHAR2
,p_terr_creation_flag IN VARCHAR2
)
IS
/* Named Account Territory groups that are created/updated */
CURSOR na_terr_grp(l_terr_group_id NUMBER) IS
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
, B.ORG_ID
, p_change_type change_type
FROM JTF_TTY_TERR_GROUPS A
,JTF_TERR_ALL B
,JTF_TERR_ALL C
WHERE A.parent_terr_id = B.terr_id
AND B.terr_id = C.parent_territory_id
AND B.org_id = C.org_id
AND C.terr_id = p_terr_id
AND A.terr_group_id = l_terr_group_id
AND A.self_service_type = 'NAMED_ACCOUNT';
/* Geography Territory groups that are created/updated */
CURSOR geo_terr_grp(l_terr_group_id NUMBER) IS
SELECT A.TERR_GROUP_ID
, A.TERR_GROUP_NAME
, A.RANK
, A.ACTIVE_FROM_DATE
, A.ACTIVE_TO_DATE
, A.PARENT_TERR_ID
, A.CREATED_BY
, A.CREATION_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_DATE
, A.LAST_UPDATE_LOGIN
, A.NUM_WINNERS
, B.ORG_ID
, p_change_type change_type
FROM JTF_TTY_TERR_GROUPS A
,JTF_TERR_ALL B
WHERE A.terr_group_id = l_terr_group_id
AND A.parent_terr_id = B.terr_id
AND A.self_service_type = 'GEOGRAPHY';
l_last_updated_by g_last_updated_by_tab := g_last_updated_by_tab();
l_last_update_date g_last_update_date_tab := g_last_update_date_tab();
l_last_update_login g_last_update_login_tab := g_last_update_login_tab();
l_last_updated_by.extend ;
l_last_update_date.extend ;
l_last_update_login.extend ;
,l_last_updated_by(1)
,l_last_update_date(1)
,l_last_update_login(1)
,l_catch_all_resource_id(1)
,l_catch_all_resource_type(1)
,l_generate_catchall_flag(1)
,l_num_winners(1)
,l_org_id(1)
,l_change_type(1);
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_catch_all_resource_id
,l_catch_all_resource_type
,l_generate_catchall_flag
,l_num_winners
,l_org_id
,l_change_type
,p_terr_type_id
,p_terr_id
,p_terr_creation_flag);
,l_last_updated_by(1)
,l_last_update_date(1)
,l_last_update_login(1)
,l_num_winners(1)
,l_org_id(1)
,l_change_type(1);
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_num_winners
,l_org_id
,l_change_type);
PROCEDURE delete_catch_all_terr_for_TG(p_terr_group_id IN NUMBER)
IS
l_catchall_terr_id NUMBER;
SELECT terr_id
FROM JTF_TERR_ALL
WHERE CATCH_ALL_FLAG='Y'
AND TERR_GROUP_ID = l_terr_grp_id;
DELETE FROM JTF_TERR_RSC_ACCESS_ALL A
WHERE A.TERR_RSC_ID IN ( SELECT B.TERR_RSC_ID
FROM JTF_TERR_RSC_ALL B
WHERE B.terr_id = l_catchall_terr_id );
DELETE FROM JTF_TERR_RSC_ALL
WHERE TERR_ID = l_catchall_terr_id;
DELETE FROM JTF_TERR_ALL
WHERE TERR_ID = l_catchall_terr_id;
END delete_catch_all_terr_for_TG;
PROCEDURE delete_catchall_terrrsc_for_TG(p_terr_group_id IN NUMBER)
IS
l_catchall_terr_id NUMBER;
SELECT terr_id
FROM JTF_TERR_ALL
WHERE CATCH_ALL_FLAG='Y'
AND TERR_GROUP_ID = l_terr_grp_id;
DELETE FROM JTF_TERR_RSC_ACCESS_ALL A
WHERE A.TERR_RSC_ID IN ( SELECT B.TERR_RSC_ID
FROM JTF_TERR_RSC_ALL B
WHERE B.terr_id = l_catchall_terr_id );
DELETE FROM JTF_TERR_RSC_ALL
WHERE TERR_ID IN
( SELECT terr_id
FROM JTF_TERR_ALL
WHERE CATCH_ALL_FLAG='Y'
AND TERR_GROUP_ID = p_terr_group_id
);
END delete_catchall_terrrsc_for_TG;
SELECT DISTINCT a.access_type, a.trans_access_code
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 jtf_terr_rsc_s.NEXTVAL
INTO l_terr_rsc_id
FROM DUAL;
SELECT terr_id INTO l_catchall_terr_id
FROM JTF_TERR_ALL
WHERE CATCH_ALL_FLAG='Y'
AND TERR_GROUP_ID = p_terr_group_id;
INSERT INTO jtf_terr_rsc_all
(
TERR_RSC_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
TERR_ID,
RESOURCE_ID,
RESOURCE_TYPE,
ROLE,
START_DATE_ACTIVE,
ORG_ID,
FULL_ACCESS_FLAG,
GROUP_ID
)
VALUES
(
l_terr_rsc_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
l_catchall_terr_id,
p_resource_id,
'RS_EMPLOYEE',
p_role_code,
SYSDATE,
p_org_id,
'Y',
p_group_id
);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
INSERT INTO jtf_terr_rsc_access_all
( TERR_RSC_ACCESS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
TERR_RSC_ID,
ACCESS_TYPE,
ORG_ID,
OBJECT_VERSION_NUMBER,
TRANS_ACCESS_CODE
)
VALUES
(
l_terr_rsc_access_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
l_terr_rsc_id,
l_qual_type,
p_org_id,
0,
l_trans_access_code
);
PROCEDURE Delete_Territory_or_tg(p_terr_Id IN VARCHAR2) IS
l_tg_id NUMBER;
SELECT terr_id,
TERR_GROUP_ID
FROM jtf_terr_all
CONNECT BY parent_territory_id = PRIOR terr_id
AND TERR_ID <> 1
AND CATCH_ALL_FLAG <> 'Y'
AND NAMED_ACCOUNT_FLAG <> 'Y'
AND TERR_GROUP_FLAG = 'Y'
START WITH terr_id = to_number(p_terr_Id);
JTF_TTY_NA_TERRGP.delete_terrgp(c.TERR_GROUP_ID);
JTF_TERRITORY_PVT.Delete_Territory
( 1.0,
NULL,
'T',
0,
l_s,
l_n,
l_s,
p_terr_id
);
This procedure will update the sales team for a named account in
a territory group
----------------------------------------------------------*/
PROCEDURE update_terr_rscs_for_na(p_terr_grp_acct_id IN NUMBER,
p_terr_group_id IN NUMBER)
IS
TYPE role_typ IS RECORD(
grp_role_id NUMBER:=FND_API.G_MISS_NUM
);
SELECT b.role_code role_code
,b.terr_group_id
FROM jtf_tty_terr_grp_roles b
WHERE b.terr_group_id = l_terr_group_id
ORDER BY b.role_code;
SELECT DISTINCT b.resource_id
, b.rsc_group_id
, b.rsc_resource_type
, b.start_date
, b.end_date
, to_char(null) attribute_category
, b.attribute1 attribute1
, b.attribute2 attribute2
, b.attribute3 attribute3
, b.attribute4 attribute4
, b.attribute5 attribute5
, to_char(null) attribute6
, to_char(null) attribute7
, to_char(null) attribute8
, to_char(null) attribute9
, to_char(null) attribute10
, to_char(null) attribute11
, to_char(null) attribute12
, to_char(null) attribute13
, to_char(null) attribute14
, to_char(null) attribute15
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 terr_id, org_id, trunc(start_date_active), trunc(end_date_active)
FROM jtf_terr_all
WHERE terr_group_account_id = l_terr_grp_acct_id;
SELECT DISTINCT a.access_type, a.trans_access_code
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 ;
DELETE FROM JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
( SELECT TERR_RSC_ID
FROM JTF_TERR_RSC_ALL A
,JTF_TERR_ALL B
WHERE B.TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id
AND B.TERR_ID = A.TERR_ID );
DELETE FROM JTF_TERR_RSC_ALL WHERE TERR_ID IN
( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );
SELECT JTF_TERR_RSC_S.NEXTVAL
INTO l_terr_rsc_id
FROM DUAL;
l_TerrRsc_Tbl(i).LAST_UPDATE_DATE := sysdate;
l_TerrRsc_Tbl(i).LAST_UPDATED_BY := l_user_id;
l_TerrRsc_Tbl(i).LAST_UPDATE_LOGIN := l_login_id;
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_DATE := sysdate;
l_TerrRsc_Access_Tbl(a).LAST_UPDATED_BY := l_user_id;
l_TerrRsc_Access_Tbl(a).LAST_UPDATE_LOGIN := l_login_id;
Write_Log(2, 'Error in procedure update_terr_rscs_for_na');
END update_terr_rscs_for_na;
This procedure will update the attribute and date for a
named account in a territory group
----------------------------------------------------------*/
PROCEDURE update_terr_for_na( p_terr_grp_acct_id IN NUMBER,
p_terr_group_id IN NUMBER)
IS
BEGIN
UPDATE jtf_terr_all jta
set (jta.ATTRIBUTE1, jta.ATTRIBUTE2, jta.ATTRIBUTE3,
jta.ATTRIBUTE4, jta.ATTRIBUTE5, jta.ATTRIBUTE6,
jta.ATTRIBUTE7, jta.ATTRIBUTE8, jta.ATTRIBUTE9,
jta.ATTRIBUTE10, jta.ATTRIBUTE11, jta.ATTRIBUTE12,
jta.ATTRIBUTE13, jta.ATTRIBUTE14, jta.ATTRIBUTE15,
jta.START_DATE_ACTIVE, jta.END_DATE_ACTIVE ) = (SELECT tty.ATTRIBUTE1, tty.ATTRIBUTE2, tty.ATTRIBUTE3,
tty.ATTRIBUTE4, tty.ATTRIBUTE5, tty.ATTRIBUTE6,
tty.ATTRIBUTE7, tty.ATTRIBUTE8, tty.ATTRIBUTE9,
tty.ATTRIBUTE10, tty.ATTRIBUTE11, tty.ATTRIBUTE12,
tty.ATTRIBUTE13, tty.ATTRIBUTE14, tty.ATTRIBUTE15,
NVL( tty.START_DATE, TRUNC(jta.start_date_active) ),
NVL( tty.END_DATE, TRUNC(jta.end_date_active) )
FROM jtf_tty_terr_grp_accts tty
WHERE tty.terr_group_account_id = jta.terr_group_account_id
AND tty.terr_group_id = jta.terr_group_id)
WHERE jta.terr_group_account_id = p_terr_grp_acct_id
AND jta.terr_group_id = p_terr_group_id;
END update_terr_for_na;