[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
last_update_date DATE,
description VARCHAR2(2000)
);
select j.terr_id, j.name, j.rank
from jtf_terr j
where NVL(j.end_date_active, sysdate) >= sysdate
AND j.start_date_active <= sysdate
AND EXISTS
( select jtr.terr_id
from jtf_terr_rsc jtr, jtf_terr_qual jtq
where jtr.terr_id = jtq.terr_id
and jtr.resource_id = decode(lc_resource_id ,null, jtr.resource_id, lc_resource_id)
and jtq.qual_usg_id = decode(lc_qual_usg_id ,null, jtq.qual_usg_id, lc_qual_usg_id)
AND jtr.terr_id = j.terr_id
);
select qual_type_usg_id, WF_NOTIFICATION.SubstituteSpecialChars(qualifier_type_name) qualifier_type_name,
WF_NOTIFICATION.SubstituteSpecialChars(qualifier_type_description) qualifier_type_description
from jtf_terr_transactions_v
where terr_id = ci_terr_id;
/* SELECT TERR_QUAL_ID,
TERR_ID,
QUAL_USG_ID,
ORG_ID,
WF_NOTIFICATION.SubstituteSpecialChars(qualifier_name) qualifier_name
FROM jtf_terr_qualifiers_v
WHERE qualifier_type_name <> 'RESOURCE'
and terr_id = ci_terr_id
and terr_qual_id is not null -- added becuse we have some real bad data in jtadom
;
SELECT
JTQ.TERR_QUAL_ID,
JTQ.TERR_ID,
JTQ.QUAL_USG_ID,
JTQ.ORG_ID,
WF_NOTIFICATION.SUBSTITUTESPECIALCHARS(JSQ.NAME) QUALIFIER_NAME
FROM
JTF_TERR_QUAL_ALL JTQ ,
JTF_QUAL_USGS_ALL JQU ,
JTF_SEEDED_QUAL_ALL_TL JSQ ,
JTF_QUAL_TYPE_USGS_ALL JQTU ,
JTF_QUAL_TYPES JQT
WHERE JTQ.QUAL_USG_ID = JQU.QUAL_USG_ID
AND JTQ.ORG_ID = JQU.ORG_ID
AND JQU.SEEDED_QUAL_ID = JSQ.SEEDED_QUAL_ID
AND JSQ.LANGUAGE = USERENV('LANG')
AND JQU.QUAL_TYPE_USG_ID = JQTU.QUAL_TYPE_USG_ID
AND JQTU.QUAL_TYPE_ID = JQT.QUAL_TYPE_ID
AND jtq.terr_id = ci_terr_id
AND jtq.terr_qual_id is not null
AND jqt.qual_type_id <> -1001 ;
SELECT TERR_QUAL_ID,
TERR_ID,
QUAL_USG_ID,
ORG_ID,
WF_NOTIFICATION.SubstituteSpecialChars(qualifier_name) qualifier_name
FROM jtf_terr_qualifiers_v
WHERE qualifier_type_name = 'RESOURCE'
and terr_id = ci_terr_id;
SELECT j1.TERR_VALUE_ID
, j1.COMPARISON_OPERATOR
-- , WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR_DESC) LOW_VALUE_CHAR_DESC
--, WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR_DESC) HIGH_VALUE_CHAR_DESC
-- Commented for bug 8365663
,decode(j2.display_type, 'CHAR'
,decode(j1.id_used_flag, 'Y', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char_id, NULL)
,'N', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL), NULL)
,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL)
,WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR)) low_value_char_desc
,decode(j2.display_type, 'CHAR'
,jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.high_value_char, NULL)
,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.low_value_char_id, NULL)
, WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR)) high_value_char_desc
, j1.LOW_VALUE_NUMBER
, j1.HIGH_VALUE_NUMBER
, j1.INTEREST_TYPE
, j1.PRIMARY_INTEREST_CODE
, j1.SECONDARY_INTEREST_CODE
, j1.CURRENCY_DESC
, j1.LOW_VALUE_CHAR_ID
, WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_NAME) CNR_GROUP_NAME
, WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE1_DESC) VALUE1_DESC
, WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE2_DESC) VALUE2_DESC
, WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE3_DESC) VALUE3_DESC
, j1.LOW_VALUE_DATE
, j1.HIGH_VALUE_DATE
, DISPLAY_TYPE
, CONVERT_TO_ID_FLAG
-- more to come directly from the jtf_terr_values_desc_v view
FROM jtf_terr_values_desc_v j1
WHERE j1.terr_qual_id = ci_terr_qual_id
and j1.terr_value_id is not null -- added becuse we have some real bad data in jtadom
ORDER BY j1.LOW_VALUE_CHAR_DESC, j1.COMPARISON_OPERATOR;*/--COmmented for bug 7237992
SELECT distinct j1.TERR_VALUE_ID
, j1.COMPARISON_OPERATOR
, WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR) LOW_VALUE_CHAR_DESC
, WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR) HIGH_VALUE_CHAR_DESC
, j1.LOW_VALUE_NUMBER
, j1.HIGH_VALUE_NUMBER
, j1.INTEREST_TYPE_ID INTEREST_TYPE
, j1.PRIMARY_INTEREST_CODE_ID PRIMARY_INTEREST_CODE
, j1.SECONDARY_INTEREST_CODE_ID SECONDARY_INTEREST_CODE
, j1.CURRENCY_CODE CURRENCY_DESC
, j1.LOW_VALUE_CHAR_ID
, WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME
, WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE1_ID) VALUE1_DESC
, WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE2_ID) VALUE2_DESC
, WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE3_ID) VALUE3_DESC
, null LOW_VALUE_DATE
, null HIGH_VALUE_DATE
, DISPLAY_TYPE
, CONVERT_TO_ID_FLAG
FROM jtf_terr_values_all j1, JTF_QUAL_USGS_ALL j2, JTF_TERR_QUAL_ALL j3
WHERE j1.terr_qual_id = ci_terr_qual_id
AND j1.terr_qual_id = j3.terr_qual_id
AND j2.QUAL_USG_ID = j3.QUAL_USG_ID
AND j1.terr_value_id is not null
ORDER BY j1.COMPARISON_OPERATOR;
SELECT distinct j1.TERR_VALUE_ID
, j1.COMPARISON_OPERATOR
--, WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR) LOW_VALUE_CHAR_DESC
--, WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR) HIGH_VALUE_CHAR_DESC
-- Commented for bug 8365663
,decode(j2.display_type, 'CHAR'
,decode(j1.id_used_flag, 'Y', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char_id, NULL)
,'N', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL), NULL)
,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL)
,WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR))
low_value_char_desc
,decode(j2.display_type, 'CHAR'
,jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.high_value_char, NULL)
,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.low_value_char_id, NULL)
, WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR))
high_value_char_desc
, j1.LOW_VALUE_NUMBER
, j1.HIGH_VALUE_NUMBER
-- , j1.INTEREST_TYPE_ID INTEREST_TYPE
-- , j1.PRIMARY_INTEREST_CODE_ID PRIMARY_INTEREST_CODE
-- , j1.SECONDARY_INTEREST_CODE_ID SECONDARY_INTEREST_CODE
, decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.interest_type_id, NULL), NULL) INTEREST_TYPE
, decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.primary_interest_code_id, NULL), NULL) PRIMARY_INTEREST_CODE
, decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.secondary_interest_code_id, NULL), NULL) SECONDARY_INTEREST_CODE
, j1.CURRENCY_CODE CURRENCY_DESC
, j1.LOW_VALUE_CHAR_ID
, WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME
, WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, j1.value2_id),
'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_2FIELDS',
jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_3FIELDS_CHAR_3IDS',
jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), NULL)) VALUE1_DESC
, WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2,
j1.value2_id, j1.value3_id), 'DEP_2FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999),
'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999), NULL)) VALUE2_DESC
, WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_3FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, NULL),
'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, j1.value4_id), NULL)) VALUE3_DESC
, null LOW_VALUE_DATE
, null HIGH_VALUE_DATE
, DISPLAY_TYPE
, CONVERT_TO_ID_FLAG
FROM jtf_terr_values_all j1, JTF_QUAL_USGS_ALL j2, JTF_TERR_QUAL_ALL j3
WHERE j1.terr_qual_id = ci_terr_qual_id
AND j1.terr_qual_id = j3.terr_qual_id
AND j2.QUAL_USG_ID = j3.QUAL_USG_ID
AND j1.terr_value_id is not null
ORDER BY j1.COMPARISON_OPERATOR;
/* select resource_id, WF_NOTIFICATION.SubstituteSpecialChars(resource_name) resource_name, resource_type, terr_rsc_id
from jtf_terr_resources_v jtrv
where jtrv.terr_id = ci_terr_id
order by resource_name;
select resource_id, WF_NOTIFICATION.SubstituteSpecialChars(jtf_territory_resource_pvt.get_resource_name(
RESOURCE_ID , DECODE( RESOURCE_TYPE , 'RS_SUPPLIER', 'RS_SUPPLIER_CONTACT' ,
RESOURCE_TYPE ) )) resource_name, resource_type, terr_rsc_id
from JTF_TERR_RSC
where terr_id = ci_terr_id
order by resource_name;
select terr_rsc_access_id, access_type, WF_NOTIFICATION.SubstituteSpecialChars(meaning) meaning
from jtf_terr_rsc_access_v
where terr_rsc_id = ci_terr_rsc_id;
SELECT distinct hr.name operating_unit
FROM hr_operating_units hr, jtf_terr_all jt
WHERE hr.organization_id = jt.org_id
AND jt.terr_id = ci_terr_id;
select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
from jtf_terr_all j
where j.terr_id = p_param5;
select SUBSTR(p_param1, 1,INSTR(p_param1, 'R')-1)
into l_resource_id
from dual;
select SUBSTR(p_param1, INSTR(p_param1, 'R'))
into l_resource_type
from dual;
select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
from jtf_terr j
WHERE ( TRUNC(j.end_date_active) >= NVL(p_param4, lp_sysdate)
AND
TRUNC(j.start_date_active) <= NVL(p_param4, lp_sysdate)
)
AND EXISTS
( select jtr.terr_id
from jtf_terr_rsc jtr, jtf_terr_qual jtq, jtf_terr_usgs jtu
where jtr.terr_id = jtq.terr_id
and jtr.terr_id = jtu.terr_id
and jtr.resource_id = decode(l_resource_id ,null, jtr.resource_id, l_resource_id)
/* ARPATEL: 10/16, bug#2832442 */
and jtr.resource_type = decode(l_resource_type ,null, jtr.resource_type, l_resource_type)
and jtq.qual_usg_id = decode(p_param2 ,null, jtq.qual_usg_id, p_param2)
and jtu.source_id = decode(p_param3 ,null, jtu.source_id, p_param3)
AND jtr.terr_id = j.terr_id
)
AND NOT EXISTS (
SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE ( ( NVL(jt.end_date_active, lp_sysdate) <= NVL(p_param4, lp_sysdate) ) OR
( NVL(jt.start_date_active, lp_sysdate) > NVL(p_param4, lp_sysdate) )
)
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = j.terr_id ) ;
select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
from jtf_terr j
where j.terr_id = p_param5;
select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
from jtf_terr j, jtf_terr_usgs jtu
where 1=1
--and j.terr_id = '19027'
AND j.terr_id = jtu.terr_id
AND j.start_date_active <= sysdate
AND trunc(j.terr_update_date) >= p_param1
AND trunc(j.terr_update_date) <= NVL(p_param2, sysdate)
AND ( TRUNC(j.end_date_active) >= NVL(p_param4, lp_sysdate)
AND
TRUNC(j.start_date_active) <= NVL(p_param4, lp_sysdate)
)
AND jtu.source_id = NVL(p_param3, jtu.source_id)
AND NOT EXISTS (
SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE ( ( NVL(jt.end_date_active, lp_sysdate) <= NVL(p_param4, lp_sysdate) ) OR
( NVL(jt.start_date_active, lp_sysdate) >= NVL(p_param4, lp_sysdate) )
)
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = j.terr_id ) ;
lx_result_tbl(l_out_index).column14 := rec_terr.last_update_date;
lx_result_tbl(l_out_index).column14 := rec_terr.last_update_date;
select 1 into l_match_qual from dual
where UPPER(p_param3) BETWEEN lx_result_tbl(l_out_index).column9 and lx_result_tbl(l_out_index).column10;
select 1 into l_match_qual from dual
where UPPER(p_param3) = lx_result_tbl(l_out_index).column9;
select 1 into l_match_qual from dual
where UPPER(p_param3) LIKE lx_result_tbl(l_out_index).column9;
select 1 into l_match_qual from dual
where UPPER(p_param4) BETWEEN lx_result_tbl(l_out_index).column9 and lx_result_tbl(l_out_index).column10;
select 1 into l_match_qual from dual
where UPPER(p_param4) = lx_result_tbl(l_out_index).column9;
select 1 into l_match_qual from dual
where UPPER(p_param4) LIKE lx_result_tbl(l_out_index).column9;
lx_result_tbl(l_out_index).column14 := rec_terr.last_update_date;
lx_result_tbl(l_out_index).column14 := rec_terr.last_update_date;
SELECT 'All' name,
atc.ACTIVE_TERR_COUNT ACTIVE_TERR_COUNT,
tdac.TERR_DUAL_ASSGN_COUNT TERR_DUAL_ASSGN_COUNT,
tcc.TERR_CREATED_COUNT TERR_CREATED_COUNT,
tsdc.TERR_SOFT_DEL_COUNT TERR_SOFT_DEL_COUNT,
tuc.TERR_UPDATED_COUNT TERR_UPDATED_COUNT,
arc.ACTIVE_DIST_REP_COUNT ACTIVE_DIST_REP_COUNT,
ROUND((atc.ACTIVE_TERR_COUNT / decode(arc.ACTIVE_DIST_REP_COUNT, 0, 1, arc.ACTIVE_DIST_REP_COUNT)),2) TERR_PER_REP
FROM
-- Total # of Active Territories
( SELECT COUNT(*) ACTIVE_TERR_COUNT
FROM jtf_terr_all jt
WHERE EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
) atc,
-- Total # of Territories that have Internal and External Reps
( SELECT COUNT(*) TERR_DUAL_ASSGN_COUNT
FROM jtf_terr_all jt
WHERE EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
AND EXISTS ( SELECT jtr.terr_id
FROM jtf_terr_rsc_all jtr
WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)
AND jtr.terr_id = jt.terr_id )
AND EXISTS ( SELECT jtr.terr_id
FROM jtf_terr_rsc_all jtr
WHERE jtr.role IN ('SALES_REP', 'Sales Representative', 'SALES_MANAGER', 'Sales Manager', NULL)
AND jtr.terr_id = jt.terr_id )
) tdac,
-- Territories created last 7 days
( SELECT COUNT(*) TERR_CREATED_COUNT
FROM jtf_terr_all jt
WHERE EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
and jt.creation_date BETWEEN SYSDATE-7 AND SYSDATE+1
) tcc,
-- Total # of (SOFT) DELETED Territories
( SELECT COUNT(*) TERR_SOFT_DEL_COUNT
FROM jtf_terr_all jt
WHERE EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
and jt.end_date_active BETWEEN SYSDATE-7 AND SYSDATE+1
) tsdc,
-- Total # of UPDATED Territories
( SELECT COUNT(*) TERR_UPDATED_COUNT
FROM jtf_terr_all jt
WHERE EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
and jt.last_update_date BETWEEN SYSDATE-7 AND SYSDATE+1
) tuc,
-- Total Distinct # of People Assigned to Active Territories -- 2930
( SELECT COUNT(DISTINCT jtr.resource_id) ACTIVE_DIST_REP_COUNT
FROM jtf_terr_rsc_all jtr
WHERE EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.resource_exists_flag = 'Y'
AND jtdr.terr_id = jtr.terr_id and jtdr.source_id = p_param2)
) arc ;
SELECT
houo.name name,
NVL(atc.ACTIVE_TERR_COUNT, 0) ACTIVE_TERR_COUNT,
NVL(tdac.TERR_DUAL_ASSGN_COUNT, 0) TERR_DUAL_ASSGN_COUNT,
NVL(tcc.TERR_CREATED_COUNT, 0) TERR_CREATED_COUNT,
NVL(tsdc.TERR_SOFT_DEL_COUNT, 0) TERR_SOFT_DEL_COUNT,
NVL(tuc.TERR_UPDATED_COUNT, 0) TERR_UPDATED_COUNT,
NVL(arc.ACTIVE_DIST_REP_COUNT, 0) ACTIVE_DIST_REP_COUNT,
DECODE( arc.ACTIVE_DIST_REP_COUNT
, NULL, 'No Active Reps'
, ROUND((atc.ACTIVE_TERR_COUNT / arc.ACTIVE_DIST_REP_COUNT), 2)
) TERR_PER_REP
FROM
hr_organization_units houo,
-- Total # of Active Territories -- 13918
( SELECT
hou.name,
hou.organization_id,
COUNT(*) ACTIVE_TERR_COUNT
FROM jtf_terr_all jt, hr_organization_units hou
WHERE jt.org_id = hou.organization_id
AND EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.terr_id = jt.terr_id and jtdr.source_id = p_param2)
GROUP BY hou.name, hou.organization_id
) atc,
-- Total # of Territories that have Internal and External Reps
( SELECT
hou.name,
hou.organization_id,
COUNT(*) TERR_DUAL_ASSGN_COUNT
FROM jtf_terr_all jt, hr_organization_units hou
WHERE jt.org_id = hou.organization_id
AND EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.terr_id = jt.terr_id and jtdr.source_id = p_param2)
AND EXISTS ( SELECT jtr.terr_id
FROM jtf_terr_rsc_all jtr
WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)
AND jtr.terr_id = jt.terr_id )
AND EXISTS ( SELECT jtr.terr_id
FROM jtf_terr_rsc_all jtr
WHERE jtr.role IN ('SALES_REP', 'Sales Representative', 'SALES_MANAGER', 'Sales Manager', NULL)
AND jtr.terr_id = jt.terr_id )
GROUP BY hou.name, hou.organization_id
) tdac,
-- Territories created last 7 days
( SELECT
hou.name,
hou.organization_id,
COUNT(*) TERR_CREATED_COUNT
FROM jtf_terr_all jt, hr_organization_units hou
WHERE jt.org_id = hou.organization_id
and EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
AND jt.creation_date BETWEEN SYSDATE-7 AND SYSDATE+1
GROUP BY hou.name, hou.organization_id
) tcc,
-- Total # of (SOFT) DELETED Territories
( SELECT
hou.name,
hou.organization_id,
COUNT(*) TERR_SOFT_DEL_COUNT
FROM jtf_terr_all jt, hr_organization_units hou
WHERE jt.org_id = hou.organization_id
and EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
AND jt.END_DATE_ACTIVE BETWEEN SYSDATE-7 AND SYSDATE+1
GROUP BY hou.name, hou.organization_id
) tsdc,
-- Total # of UPDATED Territories
( SELECT
hou.name,
hou.organization_id,
COUNT(*) TERR_UPDATED_COUNT
FROM jtf_terr_all jt, hr_organization_units hou
WHERE jt.org_id = hou.organization_id
and EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
AND jt.LAST_UPDATE_DATE BETWEEN SYSDATE-7 AND SYSDATE+1
GROUP BY hou.name, hou.organization_id
) tuc,
-- Total Distinct # of People Assigned to Territories -- 2930
( SELECT
hou.name,
hou.organization_id,
COUNT(DISTINCT jtr.resource_id) ACTIVE_DIST_REP_COUNT
FROM jtf_terr_rsc_all jtr, hr_organization_units hou
WHERE jtr.org_id = hou.organization_id
AND EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.resource_exists_flag = 'Y'
AND jtdr.terr_id = jtr.terr_id and jtdr.source_id = p_param2)
GROUP BY hou.name, hou.organization_id
) arc
WHERE
houo.organization_id = atc.organization_id
AND houo.organization_id = tdac.organization_id(+)
AND houo.organization_id = tcc.organization_id(+)
AND houo.organization_id = tsdc.organization_id(+)
AND houo.organization_id = tuc.organization_id(+)
AND houo.organization_id = arc.organization_id(+)
ORDER BY atc.ACTIVE_TERR_COUNT DESC;
SELECT 'All' name,
COUNT(*) INACTIVE_TERR_COUNT
FROM jtf_terr_all jt
WHERE
exists ( select jtua.terr_id
from jtf_terr_usgs_all jtua
where jtua.source_id = p_param2 and jtua.terr_id = jt.terr_id)
and NOT EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.terr_id = jt.terr_id );
SELECT
hou.name name,
COUNT(*) INACTIVE_TERR_COUNT,
hou.organization_id org_id
FROM jtf_terr_all jt, hr_organization_units hou
WHERE jt.org_id = hou.organization_id
and exists ( select jtua.terr_id
from jtf_terr_usgs_all jtua
where jtua.source_id = p_param2 and jtua.terr_id = jt.terr_id)
AND NOT EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.terr_id = jt.terr_id )
GROUP BY hou.name, hou.organization_id
ORDER BY INACTIVE_TERR_COUNT DESC;
lx_result_tbl(l_out_index).column6 := actglobal_type.TERR_UPDATED_COUNT;
lx_result_tbl(l_out_index).column6 := actcountry_type.TERR_UPDATED_COUNT;
DEFINITION_RPT( p_param1 => p_param1, -- optional last_update_date >= this
p_param2 => p_param2, -- last_update_date <= this
p_param3 => p_param3, -- optional source_id
p_param4 => p_param4, -- active on
p_param5 => p_param5,
p_rpt_type => 'CHANGES',
x_result_tbl => x_result_tbl);