[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE gen_init_sql ( x_select_clause OUT NOCOPY VARCHAR2
, x_from_clause OUT NOCOPY VARCHAR2
, x_where_clause OUT NOCOPY VARCHAR2 )
IS
l_proc_name VARCHAR2(30) := 'Gen_Init_SQL';
/* build initial part of select clause */
x_select_clause := 'SELECT ' ||
'JTOV.TERR_ID, ' ||
'JTOV.NAME, ' ||
'JTOV.TERR_USAGE, ' ||
'JTOV.START_DATE_ACTIVE, ' ||
'JTOV.END_DATE_ACTIVE, ' ||
'JTOV.TEMPLATE_FLAG, ' ||
'JTOV.ESCALATION_TERRITORY_FLAG, ' ||
'JTOV.PARENT_TERR_NAME, ' ||
'JTOV.TERR_TYPE_NAME ';
CURSOR c_chk_str1 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '%', 1, 1) FROM DUAL;
CURSOR c_chk_str2 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '_', 1, 1) FROM DUAL;
CURSOR c_chk_str1 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '%', 1, 1) FROM DUAL;
CURSOR c_chk_str2 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '_', 1, 1) FROM DUAL;
CURSOR c_chk_str1 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '%', 1, 1) FROM DUAL;
CURSOR c_chk_str2 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '_', 1, 1) FROM DUAL;
CURSOR c_chk_str1 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '%', 1, 1) FROM DUAL;
CURSOR c_chk_str2 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '_', 1, 1) FROM DUAL;
CURSOR c_chk_str1 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '%', 1, 1) FROM DUAL;
CURSOR c_chk_str2 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '_', 1, 1) FROM DUAL;
CURSOR c_chk_str1 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '%', 1, 1) FROM DUAL;
CURSOR c_chk_str2 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '_', 1, 1) FROM DUAL;
CURSOR c_chk_str1 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '%', 1, 1) FROM DUAL;
CURSOR c_chk_str2 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '_', 1, 1) FROM DUAL;
l_select_clause VARCHAR2(32767);
/* Generate initial SQL statement: SELECT, FROM, and WHERE clauses
** Hint: Developer should modify gen_init_sql procedure.
*/
gen_init_sql( l_select_clause, l_from_clause, l_where_clause );
l_dsql_str := l_select_clause || l_from_clause || l_where_clause || l_order_by_clause;
SELECT
jtov.TERR_ID
, jtov.LAST_UPDATE_DATE
, jtov.LAST_UPDATED_BY
, jtov.CREATION_DATE
, jtov.CREATED_BY
, jtov.LAST_UPDATE_LOGIN
, jtov.REQUEST_ID
, jtov.PROGRAM_APPLICATION_ID
, jtov.PROGRAM_ID
, jtov.PROGRAM_UPDATE_DATE
, jtov.APPLICATION_SHORT_NAME
, jtov.NAME
, jtov.ENABLED_FLAG
, jtov.START_DATE_ACTIVE
, jtov.END_DATE_ACTIVE
, jtov.PLANNED_FLAG
, jtov.PARENT_TERRITORY_ID
, jtov.TERRITORY_TYPE_ID
, jtov.TEMPLATE_TERRITORY_ID
, jtov.TEMPLATE_FLAG
, jtov.ESCALATION_TERRITORY_ID
, jtov.ESCALATION_TERRITORY_FLAG
, jtov.OVERLAP_ALLOWED_FLAG
, jtov.RANK
, jtov.DESCRIPTION
, jtov.UPDATE_FLAG
, jtov.AUTO_ASSIGN_RESOURCES_FLAG
, jtov.ATTRIBUTE_CATEGORY
, jtov.ATTRIBUTE1
, jtov.ATTRIBUTE2
, jtov.ATTRIBUTE3
, jtov.ATTRIBUTE4
, jtov.ATTRIBUTE5
, jtov.ATTRIBUTE6
, jtov.ATTRIBUTE7
, jtov.ATTRIBUTE8
, jtov.ATTRIBUTE9
, jtov.ATTRIBUTE10
, jtov.ATTRIBUTE11
, jtov.ATTRIBUTE12
, jtov.ATTRIBUTE13
, jtov.ATTRIBUTE14
, jtov.ATTRIBUTE15
, jtov.ORG_ID
, jtov.TERR_TYPE_NAME
, jtov.PARENT_TERR_NAME
, jtov.ESCALATION_TERR_NAME
, jtov.TEMPLATE_TERR_NAME
, jtov.TERR_USG_ID
, jtov.SOURCE_ID
, jtov.TERR_USAGE
FROM JTF_TERR_OVERVIEW_V jtov
WHERE jtov.TERR_ID = p_terr_id;
SELECT
jtta.TERR_TYPE_ID
, jtta.LAST_UPDATED_BY
, jtta.LAST_UPDATE_DATE
, jtta.CREATED_BY
, jtta.CREATION_DATE
, jtta.LAST_UPDATE_LOGIN
, jtta.APPLICATION_SHORT_NAME
, jtta.NAME
, jtta.ENABLED_FLAG
, jtta.DESCRIPTION
, jtta.START_DATE_ACTIVE
, jtta.END_DATE_ACTIVE
, jtta.ATTRIBUTE_CATEGORY
, jtta.ATTRIBUTE1
, jtta.ATTRIBUTE2
, jtta.ATTRIBUTE3
, jtta.ATTRIBUTE4
, jtta.ATTRIBUTE5
, jtta.ATTRIBUTE6
, jtta.ATTRIBUTE7
, jtta.ATTRIBUTE8
, jtta.ATTRIBUTE9
, jtta.ATTRIBUTE10
, jtta.ATTRIBUTE11
, jtta.ATTRIBUTE12
, jtta.ATTRIBUTE13
, jtta.ATTRIBUTE14
, jtta.ATTRIBUTE15
, jtta.ORG_ID
FROM JTF_TERR_TYPES jtta
WHERE terr_type_id = p_terr_type_id;
SELECT
jtov.TERR_ID
, jtov.LAST_UPDATE_DATE
, jtov.LAST_UPDATED_BY
, jtov.CREATION_DATE
, jtov.CREATED_BY
, jtov.LAST_UPDATE_LOGIN
, jtov.REQUEST_ID
, jtov.PROGRAM_APPLICATION_ID
, jtov.PROGRAM_ID
, jtov.PROGRAM_UPDATE_DATE
, jtov.APPLICATION_SHORT_NAME
, jtov.NAME
, jtov.ENABLED_FLAG
, jtov.START_DATE_ACTIVE
, jtov.END_DATE_ACTIVE
, jtov.PLANNED_FLAG
, jtov.PARENT_TERRITORY_ID
, jtov.TERRITORY_TYPE_ID
, jtov.TEMPLATE_TERRITORY_ID
, jtov.TEMPLATE_FLAG
, jtov.ESCALATION_TERRITORY_ID
, jtov.ESCALATION_TERRITORY_FLAG
, jtov.OVERLAP_ALLOWED_FLAG
, jtov.RANK
, jtov.DESCRIPTION
, jtov.UPDATE_FLAG
, jtov.AUTO_ASSIGN_RESOURCES_FLAG
, jtov.ATTRIBUTE_CATEGORY
, jtov.ATTRIBUTE1
, jtov.ATTRIBUTE2
, jtov.ATTRIBUTE3
, jtov.ATTRIBUTE4
, jtov.ATTRIBUTE5
, jtov.ATTRIBUTE6
, jtov.ATTRIBUTE7
, jtov.ATTRIBUTE8
, jtov.ATTRIBUTE9
, jtov.ATTRIBUTE10
, jtov.ATTRIBUTE11
, jtov.ATTRIBUTE12
, jtov.ATTRIBUTE13
, jtov.ATTRIBUTE14
, jtov.ATTRIBUTE15
, jtov.ORG_ID
, jtov.TERR_TYPE_NAME
, jtov.PARENT_TERR_NAME
, jtov.ESCALATION_TERR_NAME
, jtov.TEMPLATE_TERR_NAME
, jtov.TERR_USG_ID
, jtov.SOURCE_ID
, jtov.TERR_USAGE
FROM JTF_TERR_OVERVIEW_V jtov
WHERE jtov.parent_territory_id = p_terr_id;
SELECT
jtua.TERR_USG_ID
, jtua.LAST_UPDATE_DATE
, jtua.LAST_UPDATED_BY
, jtua.CREATION_DATE
, jtua.CREATED_BY
, jtua.LAST_UPDATE_LOGIN
, jtua.TERR_ID
, jtua.SOURCE_ID
, jtua.ORG_ID
, jse.MEANING USAGE
FROM JTF_TERR_USGS jtua, JTF_SOURCES jse
WHERE jtua.terr_id = p_terr_id
AND jtua.SOURCE_ID = jse.SOURCE_ID;
SELECT
jttv.TERR_QTYPE_USG_ID
, jttv.LAST_UPDATED_BY
, jttv.LAST_UPDATE_DATE
, jttv.CREATED_BY
, jttv.CREATION_DATE
, jttv.LAST_UPDATE_LOGIN
, jttv.TERR_ID
, jttv.QUAL_TYPE_USG_ID
, jttv.ORG_ID
, jttv.SOURCE_ID
, jttv.QUAL_TYPE_ID
, jttv.QUALIFIER_TYPE_NAME
, jttv.QUALIFIER_TYPE_DESCRIPTION
FROM JTF_TERR_TRANSACTIONS_V jttv
WHERE jttv.terr_id = p_terr_id;
SELECT
jtqv.TERR_QUAL_ID
, jtqv.LAST_UPDATE_DATE
, jtqv.LAST_UPDATED_BY
, jtqv.CREATION_DATE
, jtqv.CREATED_BY
, jtqv.LAST_UPDATE_LOGIN
, jtqv.TERR_ID
, jtqv.QUAL_USG_ID
, jtqv.USE_TO_NAME_FLAG
, jtqv.GENERATE_FLAG
, jtqv.OVERLAP_ALLOWED_FLAG
, jtqv.QUALIFIER_MODE
, jtqv.ORG_ID
, jtqv.DISPLAY_TYPE
, jtqv.LOV_SQL
, jtqv.CONVERT_TO_ID_FLAG
, jtqv.QUAL_TYPE_ID
, jtqv.QUALIFIER_TYPE_NAME
, jtqv.QUALIFIER_TYPE_DESCRIPTION
, jtqv.QUALIFIER_NAME
FROM JTF_TERR_QUALIFIERS_V jtqv
WHERE jtqv.terr_id = p_terr_id;
SELECT
j1.TERR_VALUE_ID
, j1.LAST_UPDATED_BY
, j1.LAST_UPDATE_DATE
, j1.CREATED_BY
, j1.CREATION_DATE
, j1.LAST_UPDATE_LOGIN
, j1.TERR_QUAL_ID
, j1.INCLUDE_FLAG
, j1.COMPARISON_OPERATOR
, j3.CONVERT_TO_ID_FLAG ID_USED_FLAG -- modified for bug # 4691184
, j1.LOW_VALUE_CHAR_ID
, j1.LOW_VALUE_CHAR
, j1.HIGH_VALUE_CHAR
, j1.LOW_VALUE_NUMBER
, j1.HIGH_VALUE_NUMBER
, j1.VALUE_SET
, j1.INTEREST_TYPE_ID
, j1.PRIMARY_INTEREST_CODE_ID
, j1.SECONDARY_INTEREST_CODE_ID
, j1.CURRENCY_CODE
, j1.ORG_ID
FROM JTF_TERR_VALUES j1, JTF_TERR_QUAL j2, JTF_QUAL_USGS j3
WHERE j1.terr_qual_id = j2.terr_qual_id
AND j2.terr_id = p_terr_id
AND j3.qual_usg_id = j2.qual_usg_id;
SELECT
JTRV.TERR_RSC_ID
, JTRV.LAST_UPDATE_DATE
, JTRV.LAST_UPDATED_BY
, JTRV.CREATION_DATE
, JTRV.CREATED_BY
, JTRV.LAST_UPDATE_LOGIN
, JTRV.TERR_ID
, JTRV.RESOURCE_ID
, JTRV.RESOURCE_TYPE
, JTRV.ROLE
, JTRV.PRIMARY_CONTACT_FLAG
, JTRV.START_DATE_ACTIVE
, JTRV.END_DATE_ACTIVE
, JTRV.FULL_ACCESS_FLAG
, JTRV.ORG_ID
, JTRV.RESOURCE_NAME
FROM JTF_TERR_RESOURCES_V JTRV
WHERE terr_id = p_terr_id;
, l_terr_rec.LAST_UPDATE_DATE
, l_terr_rec.LAST_UPDATED_BY
, l_terr_rec.CREATION_DATE
, l_terr_rec.CREATED_BY
, l_terr_rec.LAST_UPDATE_LOGIN
, l_terr_rec.REQUEST_ID
, l_terr_rec.PROGRAM_APPLICATION_ID
, l_terr_rec.PROGRAM_ID
, l_terr_rec.PROGRAM_UPDATE_DATE
, l_terr_rec.APPLICATION_SHORT_NAME
, l_terr_rec.NAME
, l_terr_rec.ENABLED_FLAG
, l_terr_rec.START_DATE_ACTIVE
, l_terr_rec.END_DATE_ACTIVE
, l_terr_rec.PLANNED_FLAG
, l_terr_rec.PARENT_TERRITORY_ID
, l_terr_rec.TERRITORY_TYPE_ID
, l_terr_rec.TEMPLATE_TERRITORY_ID
, l_terr_rec.TEMPLATE_FLAG
, l_terr_rec.ESCALATION_TERRITORY_ID
, l_terr_rec.ESCALATION_TERRITORY_FLAG
, l_terr_rec.OVERLAP_ALLOWED_FLAG
, l_terr_rec.RANK
, l_terr_rec.DESCRIPTION
, l_terr_rec.UPDATE_FLAG
, l_terr_rec.AUTO_ASSIGN_RESOURCES_FLAG
, l_terr_rec.ATTRIBUTE_CATEGORY
, l_terr_rec.ATTRIBUTE1
, l_terr_rec.ATTRIBUTE2
, l_terr_rec.ATTRIBUTE3
, l_terr_rec.ATTRIBUTE4
, l_terr_rec.ATTRIBUTE5
, l_terr_rec.ATTRIBUTE6
, l_terr_rec.ATTRIBUTE7
, l_terr_rec.ATTRIBUTE8
, l_terr_rec.ATTRIBUTE9
, l_terr_rec.ATTRIBUTE10
, l_terr_rec.ATTRIBUTE11
, l_terr_rec.ATTRIBUTE12
, l_terr_rec.ATTRIBUTE13
, l_terr_rec.ATTRIBUTE14
, l_terr_rec.ATTRIBUTE15
, l_terr_rec.ORG_ID
, l_terr_rec.TERR_TYPE_NAME
, l_terr_rec.PARENT_TERR_NAME
, l_terr_rec.ESCALATION_TERR_NAME
, l_terr_rec.TEMPLATE_TERR_NAME
, l_terr_rec.TERR_USG_ID
, l_terr_rec.SOURCE_ID
, l_terr_rec.TERR_USAGE;
, l_terr_type_rec.LAST_UPDATED_BY
, l_terr_type_rec.LAST_UPDATE_DATE
, l_terr_type_rec.CREATED_BY
, l_terr_type_rec.CREATION_DATE
, l_terr_type_rec.LAST_UPDATE_LOGIN
, l_terr_type_rec.APPLICATION_SHORT_NAME
, l_terr_type_rec.NAME
, l_terr_type_rec.ENABLED_FLAG
, l_terr_type_rec.DESCRIPTION
, l_terr_type_rec.START_DATE_ACTIVE
, l_terr_type_rec.END_DATE_ACTIVE
, l_terr_type_rec.ATTRIBUTE_CATEGORY
, l_terr_type_rec.ATTRIBUTE1
, l_terr_type_rec.ATTRIBUTE2
, l_terr_type_rec.ATTRIBUTE3
, l_terr_type_rec.ATTRIBUTE4
, l_terr_type_rec.ATTRIBUTE5
, l_terr_type_rec.ATTRIBUTE6
, l_terr_type_rec.ATTRIBUTE7
, l_terr_type_rec.ATTRIBUTE8
, l_terr_type_rec.ATTRIBUTE9
, l_terr_type_rec.ATTRIBUTE10
, l_terr_type_rec.ATTRIBUTE11
, l_terr_type_rec.ATTRIBUTE12
, l_terr_type_rec.ATTRIBUTE13
, l_terr_type_rec.ATTRIBUTE14
, l_terr_type_rec.ATTRIBUTE15
, l_terr_type_rec.ORG_ID;
, l_terr_sub_terr_tbl(counter).LAST_UPDATE_DATE
, l_terr_sub_terr_tbl(counter).LAST_UPDATED_BY
, l_terr_sub_terr_tbl(counter).CREATION_DATE
, l_terr_sub_terr_tbl(counter).CREATED_BY
, l_terr_sub_terr_tbl(counter).LAST_UPDATE_LOGIN
, l_terr_sub_terr_tbl(counter).REQUEST_ID
, l_terr_sub_terr_tbl(counter).PROGRAM_APPLICATION_ID
, l_terr_sub_terr_tbl(counter).PROGRAM_ID
, l_terr_sub_terr_tbl(counter).PROGRAM_UPDATE_DATE
, l_terr_sub_terr_tbl(counter).APPLICATION_SHORT_NAME
, l_terr_sub_terr_tbl(counter).NAME
, l_terr_sub_terr_tbl(counter).ENABLED_FLAG
, l_terr_sub_terr_tbl(counter).START_DATE_ACTIVE
, l_terr_sub_terr_tbl(counter).END_DATE_ACTIVE
, l_terr_sub_terr_tbl(counter).PLANNED_FLAG
, l_terr_sub_terr_tbl(counter).PARENT_TERRITORY_ID
, l_terr_sub_terr_tbl(counter).TERRITORY_TYPE_ID
, l_terr_sub_terr_tbl(counter).TEMPLATE_TERRITORY_ID
, l_terr_sub_terr_tbl(counter).TEMPLATE_FLAG
, l_terr_sub_terr_tbl(counter).ESCALATION_TERRITORY_ID
, l_terr_sub_terr_tbl(counter).ESCALATION_TERRITORY_FLAG
, l_terr_sub_terr_tbl(counter).OVERLAP_ALLOWED_FLAG
, l_terr_sub_terr_tbl(counter).RANK
, l_terr_sub_terr_tbl(counter).DESCRIPTION
, l_terr_sub_terr_tbl(counter).UPDATE_FLAG
, l_terr_sub_terr_tbl(counter).AUTO_ASSIGN_RESOURCES_FLAG
, l_terr_sub_terr_tbl(counter).ATTRIBUTE_CATEGORY
, l_terr_sub_terr_tbl(counter).ATTRIBUTE1
, l_terr_sub_terr_tbl(counter).ATTRIBUTE2
, l_terr_sub_terr_tbl(counter).ATTRIBUTE3
, l_terr_sub_terr_tbl(counter).ATTRIBUTE4
, l_terr_sub_terr_tbl(counter).ATTRIBUTE5
, l_terr_sub_terr_tbl(counter).ATTRIBUTE6
, l_terr_sub_terr_tbl(counter).ATTRIBUTE7
, l_terr_sub_terr_tbl(counter).ATTRIBUTE8
, l_terr_sub_terr_tbl(counter).ATTRIBUTE9
, l_terr_sub_terr_tbl(counter).ATTRIBUTE10
, l_terr_sub_terr_tbl(counter).ATTRIBUTE11
, l_terr_sub_terr_tbl(counter).ATTRIBUTE12
, l_terr_sub_terr_tbl(counter).ATTRIBUTE13
, l_terr_sub_terr_tbl(counter).ATTRIBUTE14
, l_terr_sub_terr_tbl(counter).ATTRIBUTE15
, l_terr_sub_terr_tbl(counter).ORG_ID
, l_terr_sub_terr_tbl(counter).TERR_TYPE_NAME
, l_terr_sub_terr_tbl(counter).PARENT_TERR_NAME
, l_terr_sub_terr_tbl(counter).ESCALATION_TERR_NAME
, l_terr_sub_terr_tbl(counter).TEMPLATE_TERR_NAME
, l_terr_sub_terr_tbl(counter).TERR_USG_ID
, l_terr_sub_terr_tbl(counter).SOURCE_ID
, l_terr_sub_terr_tbl(counter).TERR_USAGE;
, l_terr_usgs_tbl(counter).LAST_UPDATE_DATE
, l_terr_usgs_tbl(counter).LAST_UPDATED_BY
, l_terr_usgs_tbl(counter).CREATION_DATE
, l_terr_usgs_tbl(counter).CREATED_BY
, l_terr_usgs_tbl(counter).LAST_UPDATE_LOGIN
, l_terr_usgs_tbl(counter).TERR_ID
, l_terr_usgs_tbl(counter).SOURCE_ID
, l_terr_usgs_tbl(counter).ORG_ID
, l_terr_usgs_tbl(counter).USAGE;
, l_terr_qtype_usgs_tbl(counter).LAST_UPDATED_BY
, l_terr_qtype_usgs_tbl(counter).LAST_UPDATE_DATE
, l_terr_qtype_usgs_tbl(counter).CREATED_BY
, l_terr_qtype_usgs_tbl(counter).CREATION_DATE
, l_terr_qtype_usgs_tbl(counter).LAST_UPDATE_LOGIN
, l_terr_qtype_usgs_tbl(counter).TERR_ID
, l_terr_qtype_usgs_tbl(counter).QUAL_TYPE_USG_ID
, l_terr_qtype_usgs_tbl(counter).ORG_ID
, l_terr_qtype_usgs_tbl(counter).SOURCE_ID
, l_terr_qtype_usgs_tbl(counter).QUAL_TYPE_ID
, l_terr_qtype_usgs_tbl(counter).QUALIFIER_TYPE_NAME
, l_terr_qtype_usgs_tbl(counter).QUALIFIER_TYPE_DESCRIPTION;
, l_terr_qual_tbl(counter).LAST_UPDATE_DATE
, l_terr_qual_tbl(counter).LAST_UPDATED_BY
, l_terr_qual_tbl(counter).CREATION_DATE
, l_terr_qual_tbl(counter).CREATED_BY
, l_terr_qual_tbl(counter).LAST_UPDATE_LOGIN
, l_terr_qual_tbl(counter).TERR_ID
, l_terr_qual_tbl(counter).QUAL_USG_ID
, l_terr_qual_tbl(counter).USE_TO_NAME_FLAG
, l_terr_qual_tbl(counter).GENERATE_FLAG
, l_terr_qual_tbl(counter).OVERLAP_ALLOWED_FLAG
, l_terr_qual_tbl(counter).QUALIFIER_MODE
, l_terr_qual_tbl(counter).ORG_ID
, l_terr_qual_tbl(counter).DISPLAY_TYPE
, l_terr_qual_tbl(counter).LOV_SQL
, l_terr_qual_tbl(counter).CONVERT_TO_ID_FLAG
, l_terr_qual_tbl(counter).QUAL_TYPE_ID
, l_terr_qual_tbl(counter).QUALIFIER_TYPE_NAME
, l_terr_qual_tbl(counter).QUALIFIER_TYPE_DESCRIPTION
, l_terr_qual_tbl(counter).QUALIFIER_NAME;
, l_terr_values_tbl(counter).LAST_UPDATED_BY
, l_terr_values_tbl(counter).LAST_UPDATE_DATE
, l_terr_values_tbl(counter).CREATED_BY
, l_terr_values_tbl(counter).CREATION_DATE
, l_terr_values_tbl(counter).LAST_UPDATE_LOGIN
, l_terr_values_tbl(counter).TERR_QUAL_ID
, l_terr_values_tbl(counter).INCLUDE_FLAG
, l_terr_values_tbl(counter).COMPARISON_OPERATOR
, l_terr_values_tbl(counter).ID_USED_FLAG
, l_terr_values_tbl(counter).LOW_VALUE_CHAR_ID
, l_terr_values_tbl(counter).LOW_VALUE_CHAR
, l_terr_values_tbl(counter).HIGH_VALUE_CHAR
, l_terr_values_tbl(counter).LOW_VALUE_NUMBER
, l_terr_values_tbl(counter).HIGH_VALUE_NUMBER
, l_terr_values_tbl(counter).VALUE_SET
, l_terr_values_tbl(counter).INTEREST_TYPE_ID
, l_terr_values_tbl(counter).PRIMARY_INTEREST_CODE_ID
, l_terr_values_tbl(counter).SECONDARY_INTEREST_CODE_ID
, l_terr_values_tbl(counter).CURRENCY_CODE
, l_terr_values_tbl(counter).ORG_ID;
, l_terr_rsc_tbl(counter).LAST_UPDATE_DATE
, l_terr_rsc_tbl(counter).LAST_UPDATED_BY
, l_terr_rsc_tbl(counter).CREATION_DATE
, l_terr_rsc_tbl(counter).CREATED_BY
, l_terr_rsc_tbl(counter).LAST_UPDATE_LOGIN
, l_terr_rsc_tbl(counter).TERR_ID
, l_terr_rsc_tbl(counter).RESOURCE_ID
, l_terr_rsc_tbl(counter).RESOURCE_TYPE
, l_terr_rsc_tbl(counter).ROLE
, l_terr_rsc_tbl(counter).PRIMARY_CONTACT_FLAG
, l_terr_rsc_tbl(counter).START_DATE_ACTIVE
, l_terr_rsc_tbl(counter).END_DATE_ACTIVE
, l_terr_rsc_tbl(counter).FULL_ACCESS_FLAG
, l_terr_rsc_tbl(counter).ORG_ID
, l_terr_rsc_tbl(counter).RESOURCE_NAME;
/* SELECT jt.escalation_territory_id */
SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE jt.terr_id = p_terr_id
AND NVL(jt.end_date_active, sysdate+1) > sysdate
AND NVL(jt.start_date_active, sysdate-1) < sysdate;
SELECT jt.parent_territory_id
FROM jtf_terr_all jt
WHERE jt.terr_id = p_terr_id;
Select JTR.TERR_RSC_ID,
JTR.TERR_ID,
JT.NAME,
JTR.RESOURCE_ID,
--JTRA.ACCESS_TYPE,
JTR.RESOURCE_TYPE,
JTR.ROLE,
JTR.PRIMARY_CONTACT_FLAG
From JTF_TERR_RSC_ALL JTR,
--JTF_TERR_RSC_ACCESS JTRA,
JTF_TERR_ALL JT
Where JT.TERR_ID = p_Terr_id
AND JTR.TERR_ID = JT.TERR_ID
--AND JTR.TERR_RSC_ID = JTRA.TERR_RSC_ID (+)
AND NVL(jtr.end_date_active, sysdate+1) > sysdate
AND NVL(jtr.start_date_active, sysdate-1) < sysdate
AND NVL(jt.end_date_active, sysdate+1) > sysdate
AND NVL(jt.start_date_active, sysdate-1) < sysdate
AND EXISTS (
SELECT 1
FROM JTF_TERR_RSC_ACCESS_ALL JTRA
WHERE JTRA.terr_rsc_id = JTR.terr_rsc_id
AND JTRA.trans_access_code = 'ESC_OWNER');