eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JTF_TERR_JSP_REPORTS dependencies on JTF_TERR

Line 1: package body JTF_TERR_JSP_REPORTS AS

1: package body JTF_TERR_JSP_REPORTS AS
2: /* $Header: jtfpjrpb.pls 120.7.12010000.7 2008/11/26 06:08:11 gmarwah ship $ */
3: ---------------------------------------------------------
4: -- Start of Comments
5: -- ---------------------------------------------------

Line 6: -- PACKAGE NAME: JTF_TERR_JSP_REPORTS

2: /* $Header: jtfpjrpb.pls 120.7.12010000.7 2008/11/26 06:08:11 gmarwah ship $ */
3: ---------------------------------------------------------
4: -- Start of Comments
5: -- ---------------------------------------------------
6: -- PACKAGE NAME: JTF_TERR_JSP_REPORTS
7: -- ---------------------------------------------------
8: -- PURPOSE
9: -- JTF/A Territories JSP Reports Package
10: -- NOTES

Line 89: from jtf_terr j

85: -- all the territories we will be reporting on
86: /*
87: CURSOR cur_terr is
88: select j.terr_id, j.name, j.rank
89: from jtf_terr j
90: where NVL(j.end_date_active, sysdate) >= sysdate
91: AND j.start_date_active <= sysdate
92: AND EXISTS
93: ( select jtr.terr_id

Line 94: from jtf_terr_rsc jtr, jtf_terr_qual jtq

90: where NVL(j.end_date_active, sysdate) >= sysdate
91: AND j.start_date_active <= sysdate
92: AND EXISTS
93: ( select jtr.terr_id
94: from jtf_terr_rsc jtr, jtf_terr_qual jtq
95: where jtr.terr_id = jtq.terr_id
96: and jtr.resource_id = decode(lc_resource_id ,null, jtr.resource_id, lc_resource_id)
97: and jtq.qual_usg_id = decode(lc_qual_usg_id ,null, jtq.qual_usg_id, lc_qual_usg_id)
98: AND jtr.terr_id = j.terr_id

Line 105: from jtf_terr_transactions_v

101: -- qualifier types
102: CURSOR c_get_qual_types(ci_terr_id NUMBER) IS
103: select qual_type_usg_id, WF_NOTIFICATION.SubstituteSpecialChars(qualifier_type_name) qualifier_type_name,
104: WF_NOTIFICATION.SubstituteSpecialChars(qualifier_type_description) qualifier_type_description
105: from jtf_terr_transactions_v
106: where terr_id = ci_terr_id;
107:
108:
109: -- dynamic qualifiers

Line 116: FROM jtf_terr_qualifiers_v

112: TERR_ID,
113: QUAL_USG_ID,
114: ORG_ID,
115: WF_NOTIFICATION.SubstituteSpecialChars(qualifier_name) qualifier_name
116: FROM jtf_terr_qualifiers_v
117: WHERE qualifier_type_name <> 'RESOURCE'
118: and terr_id = ci_terr_id
119: and terr_qual_id is not null -- added becuse we have some real bad data in jtadom
120: ;

Line 130: JTF_TERR_QUAL_ALL JTQ ,

126: JTQ.QUAL_USG_ID,
127: JTQ.ORG_ID,
128: WF_NOTIFICATION.SUBSTITUTESPECIALCHARS(JSQ.NAME) QUALIFIER_NAME
129: FROM
130: JTF_TERR_QUAL_ALL JTQ ,
131: JTF_QUAL_USGS_ALL JQU ,
132: JTF_SEEDED_QUAL_ALL_TL JSQ ,
133: JTF_QUAL_TYPE_USGS_ALL JQTU ,
134: JTF_QUAL_TYPES JQT

Line 153: FROM jtf_terr_qualifiers_v

149: TERR_ID,
150: QUAL_USG_ID,
151: ORG_ID,
152: WF_NOTIFICATION.SubstituteSpecialChars(qualifier_name) qualifier_name
153: FROM jtf_terr_qualifiers_v
154: WHERE qualifier_type_name = 'RESOURCE'
155: and terr_id = ci_terr_id;
156:
157:

Line 180: -- more to come directly from the jtf_terr_values_desc_v view

176: , j1.LOW_VALUE_DATE
177: , j1.HIGH_VALUE_DATE
178: , DISPLAY_TYPE
179: , CONVERT_TO_ID_FLAG
180: -- more to come directly from the jtf_terr_values_desc_v view
181: FROM jtf_terr_values_desc_v j1
182: WHERE j1.terr_qual_id = ci_terr_qual_id
183: and j1.terr_value_id is not null -- added becuse we have some real bad data in jtadom
184: ORDER BY j1.LOW_VALUE_CHAR_DESC, j1.COMPARISON_OPERATOR;*/--COmmented for bug 7237992

Line 181: FROM jtf_terr_values_desc_v j1

177: , j1.HIGH_VALUE_DATE
178: , DISPLAY_TYPE
179: , CONVERT_TO_ID_FLAG
180: -- more to come directly from the jtf_terr_values_desc_v view
181: FROM jtf_terr_values_desc_v j1
182: WHERE j1.terr_qual_id = ci_terr_qual_id
183: and j1.terr_value_id is not null -- added becuse we have some real bad data in jtadom
184: ORDER BY j1.LOW_VALUE_CHAR_DESC, j1.COMPARISON_OPERATOR;*/--COmmented for bug 7237992
185: /*

Line 205: FROM jtf_terr_values_all j1, JTF_QUAL_USGS_ALL j2, JTF_TERR_QUAL_ALL j3

201: , null LOW_VALUE_DATE
202: , null HIGH_VALUE_DATE
203: , DISPLAY_TYPE
204: , CONVERT_TO_ID_FLAG
205: FROM jtf_terr_values_all j1, JTF_QUAL_USGS_ALL j2, JTF_TERR_QUAL_ALL j3
206: WHERE j1.terr_qual_id = ci_terr_qual_id
207: AND j1.terr_qual_id = j3.terr_qual_id
208: AND j2.QUAL_USG_ID = j3.QUAL_USG_ID
209: AND j1.terr_value_id is not null

Line 223: , 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

219: , j1.HIGH_VALUE_NUMBER
220: -- , j1.INTEREST_TYPE_ID INTEREST_TYPE
221: -- , j1.PRIMARY_INTEREST_CODE_ID PRIMARY_INTEREST_CODE
222: -- , j1.SECONDARY_INTEREST_CODE_ID SECONDARY_INTEREST_CODE
223: , 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
224: , 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
225: , 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
226:
227: , j1.CURRENCY_CODE CURRENCY_DESC

Line 224: , 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

220: -- , j1.INTEREST_TYPE_ID INTEREST_TYPE
221: -- , j1.PRIMARY_INTEREST_CODE_ID PRIMARY_INTEREST_CODE
222: -- , j1.SECONDARY_INTEREST_CODE_ID SECONDARY_INTEREST_CODE
223: , 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
224: , 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
225: , 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
226:
227: , j1.CURRENCY_CODE CURRENCY_DESC
228: , j1.LOW_VALUE_CHAR_ID

Line 225: , 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

221: -- , j1.PRIMARY_INTEREST_CODE_ID PRIMARY_INTEREST_CODE
222: -- , j1.SECONDARY_INTEREST_CODE_ID SECONDARY_INTEREST_CODE
223: , 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
224: , 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
225: , 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
226:
227: , j1.CURRENCY_CODE CURRENCY_DESC
228: , j1.LOW_VALUE_CHAR_ID
229: , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME

Line 230: , 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),

226:
227: , j1.CURRENCY_CODE CURRENCY_DESC
228: , j1.LOW_VALUE_CHAR_ID
229: , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME
230: , 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),
231: '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',
232: 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',
233: 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
234: , 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,

Line 231: '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',

227: , j1.CURRENCY_CODE CURRENCY_DESC
228: , j1.LOW_VALUE_CHAR_ID
229: , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME
230: , 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),
231: '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',
232: 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',
233: 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
234: , 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,
235: 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),

Line 232: 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',

228: , j1.LOW_VALUE_CHAR_ID
229: , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME
230: , 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),
231: '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',
232: 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',
233: 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
234: , 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,
235: 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),
236: '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

Line 233: 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

229: , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME
230: , 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),
231: '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',
232: 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',
233: 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
234: , 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,
235: 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),
236: '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
237: , 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),

Line 234: , 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,

230: , 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),
231: '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',
232: 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',
233: 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
234: , 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,
235: 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),
236: '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
237: , 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),
238: '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

Line 235: 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),

231: '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',
232: 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',
233: 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
234: , 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,
235: 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),
236: '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
237: , 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),
238: '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
239: , null LOW_VALUE_DATE

Line 236: '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

232: 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',
233: 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
234: , 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,
235: 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),
236: '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
237: , 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),
238: '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
239: , null LOW_VALUE_DATE
240: , null HIGH_VALUE_DATE

Line 237: , 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),

233: 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
234: , 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,
235: 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),
236: '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
237: , 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),
238: '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
239: , null LOW_VALUE_DATE
240: , null HIGH_VALUE_DATE
241: , DISPLAY_TYPE

Line 238: '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

234: , 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,
235: 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),
236: '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
237: , 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),
238: '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
239: , null LOW_VALUE_DATE
240: , null HIGH_VALUE_DATE
241: , DISPLAY_TYPE
242: , CONVERT_TO_ID_FLAG

Line 243: FROM jtf_terr_values_all j1, JTF_QUAL_USGS_ALL j2, JTF_TERR_QUAL_ALL j3

239: , null LOW_VALUE_DATE
240: , null HIGH_VALUE_DATE
241: , DISPLAY_TYPE
242: , CONVERT_TO_ID_FLAG
243: FROM jtf_terr_values_all j1, JTF_QUAL_USGS_ALL j2, JTF_TERR_QUAL_ALL j3
244: WHERE j1.terr_qual_id = ci_terr_qual_id
245: AND j1.terr_qual_id = j3.terr_qual_id
246: AND j2.QUAL_USG_ID = j3.QUAL_USG_ID
247: AND j1.terr_value_id is not null

Line 252: from jtf_terr_resources_v jtrv

248: ORDER BY j1.COMPARISON_OPERATOR;
249: -- resources
250: CURSOR c_get_resource(ci_terr_id NUMBER) IS
251: /* select resource_id, WF_NOTIFICATION.SubstituteSpecialChars(resource_name) resource_name, resource_type, terr_rsc_id
252: from jtf_terr_resources_v jtrv
253: where jtrv.terr_id = ci_terr_id
254: order by resource_name;
255: */
256: select resource_id, WF_NOTIFICATION.SubstituteSpecialChars(jtf_territory_resource_pvt.get_resource_name(

Line 256: select resource_id, WF_NOTIFICATION.SubstituteSpecialChars(jtf_territory_resource_pvt.get_resource_name(

252: from jtf_terr_resources_v jtrv
253: where jtrv.terr_id = ci_terr_id
254: order by resource_name;
255: */
256: select resource_id, WF_NOTIFICATION.SubstituteSpecialChars(jtf_territory_resource_pvt.get_resource_name(
257: RESOURCE_ID , DECODE( RESOURCE_TYPE , 'RS_SUPPLIER', 'RS_SUPPLIER_CONTACT' ,
258: RESOURCE_TYPE ) )) resource_name, resource_type, terr_rsc_id
259: from JTF_TERR_RSC
260: where terr_id = ci_terr_id

Line 259: from JTF_TERR_RSC

255: */
256: select resource_id, WF_NOTIFICATION.SubstituteSpecialChars(jtf_territory_resource_pvt.get_resource_name(
257: RESOURCE_ID , DECODE( RESOURCE_TYPE , 'RS_SUPPLIER', 'RS_SUPPLIER_CONTACT' ,
258: RESOURCE_TYPE ) )) resource_name, resource_type, terr_rsc_id
259: from JTF_TERR_RSC
260: where terr_id = ci_terr_id
261: order by resource_name;
262:
263: -- resource accesses

Line 266: from jtf_terr_rsc_access_v

262:
263: -- resource accesses
264: CURSOR c_get_rsc_access(ci_terr_rsc_id NUMBER) IS
265: select terr_rsc_access_id, access_type, WF_NOTIFICATION.SubstituteSpecialChars(meaning) meaning
266: from jtf_terr_rsc_access_v
267: where terr_rsc_id = ci_terr_rsc_id;
268:
269: lp_sysdate DATE := SYSDATE;
270: l_match_qual NUMBER := 0;

Line 279: FROM hr_operating_units hr, jtf_terr_all jt

275:
276: /*Added for bug 7315889 */
277: CURSOR c_get_operating_unit(ci_terr_id VARCHAR2) IS
278: SELECT distinct hr.name operating_unit
279: FROM hr_operating_units hr, jtf_terr_all jt
280: WHERE hr.organization_id = jt.org_id
281: AND jt.terr_id = ci_terr_id;
282:
283: -- ADDED FOR BUG 7315889

Line 288: --dbms_output.put_line('JTF_TERR_JSP_REPORTS.definition_rpt: BEGIN ');

284: l_terr_operating_unit hr_operating_units.name%TYPE;
285:
286: begin
287:
288: --dbms_output.put_line('JTF_TERR_JSP_REPORTS.definition_rpt: BEGIN ');
289: --dbms_output.put_line(p_param1 || ' / ' || p_param2 || ' / ' || p_param3 || ' / ' || p_param4 || ' / ' || p_param5);
290: -- loop through cur_terr
291: l_out_index := 0;
292:

Line 298: from jtf_terr_all j

294: if p_rpt_type = 'LOOKUP_TERR' and p_param5 IS NOT NULL then
295: open terr_name_cur for
296: select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
297: j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
298: from jtf_terr_all j
299: where j.terr_id = p_param5;
300:
301:
302: -- ADDED FOR BUG 7315889

Line 326: from jtf_terr j

322: --dbms_output.put_line('get territory by property');
323: open terr_name_cur for
324: select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
325: j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
326: from jtf_terr j
327: WHERE ( TRUNC(j.end_date_active) >= NVL(p_param4, lp_sysdate)
328: AND
329: TRUNC(j.start_date_active) <= NVL(p_param4, lp_sysdate)
330: )

Line 333: from jtf_terr_rsc jtr, jtf_terr_qual jtq, jtf_terr_usgs jtu

329: TRUNC(j.start_date_active) <= NVL(p_param4, lp_sysdate)
330: )
331: AND EXISTS
332: ( select jtr.terr_id
333: from jtf_terr_rsc jtr, jtf_terr_qual jtq, jtf_terr_usgs jtu
334: where jtr.terr_id = jtq.terr_id
335: and jtr.terr_id = jtu.terr_id
336: and jtr.resource_id = decode(l_resource_id ,null, jtr.resource_id, l_resource_id)
337: /* ARPATEL: 10/16, bug#2832442 */

Line 345: FROM jtf_terr_all jt

341: AND jtr.terr_id = j.terr_id
342: )
343: AND NOT EXISTS (
344: SELECT jt.terr_id
345: FROM jtf_terr_all jt
346: WHERE ( ( NVL(jt.end_date_active, lp_sysdate) <= NVL(p_param4, lp_sysdate) ) OR
347: ( NVL(jt.start_date_active, lp_sysdate) > NVL(p_param4, lp_sysdate) )
348: )
349: CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1

Line 356: from jtf_terr j

352: --dbms_output.put_line('get territory by_id mode ');
353: open terr_name_cur for
354: select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
355: j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
356: from jtf_terr j
357: where j.terr_id = p_param5;
358:
359: end if;
360: elsif p_rpt_type = 'CHANGES' then

Line 365: from jtf_terr j, jtf_terr_usgs jtu

361: --dbms_output.put_line('creating changes cursor ');
362: open terr_name_cur for
363: select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
364: j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
365: from jtf_terr j, jtf_terr_usgs jtu
366: where 1=1
367: --and j.terr_id = '19027'
368: AND j.terr_id = jtu.terr_id
369: AND j.start_date_active <= sysdate

Line 379: FROM jtf_terr_all jt

375: )
376: AND jtu.source_id = NVL(p_param3, jtu.source_id)
377: AND NOT EXISTS (
378: SELECT jt.terr_id
379: FROM jtf_terr_all jt
380: WHERE ( ( NVL(jt.end_date_active, lp_sysdate) <= NVL(p_param4, lp_sysdate) ) OR
381: ( NVL(jt.start_date_active, lp_sysdate) >= NVL(p_param4, lp_sysdate) )
382: )
383: CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1

Line 845: FROM jtf_terr_all jt

841: ROUND((atc.ACTIVE_TERR_COUNT / decode(arc.ACTIVE_DIST_REP_COUNT, 0, 1, arc.ACTIVE_DIST_REP_COUNT)),2) TERR_PER_REP
842: FROM
843: -- Total # of Active Territories
844: ( SELECT COUNT(*) ACTIVE_TERR_COUNT
845: FROM jtf_terr_all jt
846: WHERE EXISTS ( SELECT jtdr.terr_id
847: FROM jtf_terr_denorm_rules_all jtdr
848: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
849: ) atc,

Line 847: FROM jtf_terr_denorm_rules_all jtdr

843: -- Total # of Active Territories
844: ( SELECT COUNT(*) ACTIVE_TERR_COUNT
845: FROM jtf_terr_all jt
846: WHERE EXISTS ( SELECT jtdr.terr_id
847: FROM jtf_terr_denorm_rules_all jtdr
848: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
849: ) atc,
850:
851: -- Total # of Territories that have Internal and External Reps

Line 853: FROM jtf_terr_all jt

849: ) atc,
850:
851: -- Total # of Territories that have Internal and External Reps
852: ( SELECT COUNT(*) TERR_DUAL_ASSGN_COUNT
853: FROM jtf_terr_all jt
854: WHERE EXISTS ( SELECT jtdr.terr_id
855: FROM jtf_terr_denorm_rules_all jtdr
856: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
857: AND EXISTS ( SELECT jtr.terr_id

Line 855: FROM jtf_terr_denorm_rules_all jtdr

851: -- Total # of Territories that have Internal and External Reps
852: ( SELECT COUNT(*) TERR_DUAL_ASSGN_COUNT
853: FROM jtf_terr_all jt
854: WHERE EXISTS ( SELECT jtdr.terr_id
855: FROM jtf_terr_denorm_rules_all jtdr
856: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
857: AND EXISTS ( SELECT jtr.terr_id
858: FROM jtf_terr_rsc_all jtr
859: WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)

Line 858: FROM jtf_terr_rsc_all jtr

854: WHERE EXISTS ( SELECT jtdr.terr_id
855: FROM jtf_terr_denorm_rules_all jtdr
856: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
857: AND EXISTS ( SELECT jtr.terr_id
858: FROM jtf_terr_rsc_all jtr
859: WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)
860: AND jtr.terr_id = jt.terr_id )
861: AND EXISTS ( SELECT jtr.terr_id
862: FROM jtf_terr_rsc_all jtr

Line 862: FROM jtf_terr_rsc_all jtr

858: FROM jtf_terr_rsc_all jtr
859: WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)
860: AND jtr.terr_id = jt.terr_id )
861: AND EXISTS ( SELECT jtr.terr_id
862: FROM jtf_terr_rsc_all jtr
863: WHERE jtr.role IN ('SALES_REP', 'Sales Representative', 'SALES_MANAGER', 'Sales Manager', NULL)
864: AND jtr.terr_id = jt.terr_id )
865: ) tdac,
866:

Line 869: FROM jtf_terr_all jt

865: ) tdac,
866:
867: -- Territories created last 7 days
868: ( SELECT COUNT(*) TERR_CREATED_COUNT
869: FROM jtf_terr_all jt
870: WHERE EXISTS ( SELECT jtdr.terr_id
871: FROM jtf_terr_denorm_rules_all jtdr
872: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
873: and jt.creation_date BETWEEN SYSDATE-7 AND SYSDATE+1

Line 871: FROM jtf_terr_denorm_rules_all jtdr

867: -- Territories created last 7 days
868: ( SELECT COUNT(*) TERR_CREATED_COUNT
869: FROM jtf_terr_all jt
870: WHERE EXISTS ( SELECT jtdr.terr_id
871: FROM jtf_terr_denorm_rules_all jtdr
872: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
873: and jt.creation_date BETWEEN SYSDATE-7 AND SYSDATE+1
874: ) tcc,
875:

Line 878: FROM jtf_terr_all jt

874: ) tcc,
875:
876: -- Total # of (SOFT) DELETED Territories
877: ( SELECT COUNT(*) TERR_SOFT_DEL_COUNT
878: FROM jtf_terr_all jt
879: WHERE EXISTS ( SELECT jtdr.terr_id
880: FROM jtf_terr_denorm_rules_all jtdr
881: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
882: and jt.end_date_active BETWEEN SYSDATE-7 AND SYSDATE+1

Line 880: FROM jtf_terr_denorm_rules_all jtdr

876: -- Total # of (SOFT) DELETED Territories
877: ( SELECT COUNT(*) TERR_SOFT_DEL_COUNT
878: FROM jtf_terr_all jt
879: WHERE EXISTS ( SELECT jtdr.terr_id
880: FROM jtf_terr_denorm_rules_all jtdr
881: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
882: and jt.end_date_active BETWEEN SYSDATE-7 AND SYSDATE+1
883: ) tsdc,
884:

Line 887: FROM jtf_terr_all jt

883: ) tsdc,
884:
885: -- Total # of UPDATED Territories
886: ( SELECT COUNT(*) TERR_UPDATED_COUNT
887: FROM jtf_terr_all jt
888: WHERE EXISTS ( SELECT jtdr.terr_id
889: FROM jtf_terr_denorm_rules_all jtdr
890: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
891: and jt.last_update_date BETWEEN SYSDATE-7 AND SYSDATE+1

Line 889: FROM jtf_terr_denorm_rules_all jtdr

885: -- Total # of UPDATED Territories
886: ( SELECT COUNT(*) TERR_UPDATED_COUNT
887: FROM jtf_terr_all jt
888: WHERE EXISTS ( SELECT jtdr.terr_id
889: FROM jtf_terr_denorm_rules_all jtdr
890: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
891: and jt.last_update_date BETWEEN SYSDATE-7 AND SYSDATE+1
892: ) tuc,
893:

Line 896: FROM jtf_terr_rsc_all jtr

892: ) tuc,
893:
894: -- Total Distinct # of People Assigned to Active Territories -- 2930
895: ( SELECT COUNT(DISTINCT jtr.resource_id) ACTIVE_DIST_REP_COUNT
896: FROM jtf_terr_rsc_all jtr
897: WHERE EXISTS ( SELECT jtdr.terr_id
898: FROM jtf_terr_denorm_rules_all jtdr
899: WHERE jtdr.resource_exists_flag = 'Y'
900: AND jtdr.terr_id = jtr.terr_id and jtdr.source_id = p_param2)

Line 898: FROM jtf_terr_denorm_rules_all jtdr

894: -- Total Distinct # of People Assigned to Active Territories -- 2930
895: ( SELECT COUNT(DISTINCT jtr.resource_id) ACTIVE_DIST_REP_COUNT
896: FROM jtf_terr_rsc_all jtr
897: WHERE EXISTS ( SELECT jtdr.terr_id
898: FROM jtf_terr_denorm_rules_all jtdr
899: WHERE jtdr.resource_exists_flag = 'Y'
900: AND jtdr.terr_id = jtr.terr_id and jtdr.source_id = p_param2)
901: ) arc ;
902:

Line 925: FROM jtf_terr_all jt, hr_organization_units hou

921: ( SELECT
922: hou.name,
923: hou.organization_id,
924: COUNT(*) ACTIVE_TERR_COUNT
925: FROM jtf_terr_all jt, hr_organization_units hou
926: WHERE jt.org_id = hou.organization_id
927: AND EXISTS ( SELECT jtdr.terr_id
928: FROM jtf_terr_denorm_rules_all jtdr
929: WHERE jtdr.terr_id = jt.terr_id and jtdr.source_id = p_param2)

Line 928: FROM jtf_terr_denorm_rules_all jtdr

924: COUNT(*) ACTIVE_TERR_COUNT
925: FROM jtf_terr_all jt, hr_organization_units hou
926: WHERE jt.org_id = hou.organization_id
927: AND EXISTS ( SELECT jtdr.terr_id
928: FROM jtf_terr_denorm_rules_all jtdr
929: WHERE jtdr.terr_id = jt.terr_id and jtdr.source_id = p_param2)
930: GROUP BY hou.name, hou.organization_id
931: ) atc,
932:

Line 938: FROM jtf_terr_all jt, hr_organization_units hou

934: ( SELECT
935: hou.name,
936: hou.organization_id,
937: COUNT(*) TERR_DUAL_ASSGN_COUNT
938: FROM jtf_terr_all jt, hr_organization_units hou
939: WHERE jt.org_id = hou.organization_id
940: AND EXISTS ( SELECT jtdr.terr_id
941: FROM jtf_terr_denorm_rules_all jtdr
942: WHERE jtdr.terr_id = jt.terr_id and jtdr.source_id = p_param2)

Line 941: FROM jtf_terr_denorm_rules_all jtdr

937: COUNT(*) TERR_DUAL_ASSGN_COUNT
938: FROM jtf_terr_all jt, hr_organization_units hou
939: WHERE jt.org_id = hou.organization_id
940: AND EXISTS ( SELECT jtdr.terr_id
941: FROM jtf_terr_denorm_rules_all jtdr
942: WHERE jtdr.terr_id = jt.terr_id and jtdr.source_id = p_param2)
943: AND EXISTS ( SELECT jtr.terr_id
944: FROM jtf_terr_rsc_all jtr
945: WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)

Line 944: FROM jtf_terr_rsc_all jtr

940: AND EXISTS ( SELECT jtdr.terr_id
941: FROM jtf_terr_denorm_rules_all jtdr
942: WHERE jtdr.terr_id = jt.terr_id and jtdr.source_id = p_param2)
943: AND EXISTS ( SELECT jtr.terr_id
944: FROM jtf_terr_rsc_all jtr
945: WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)
946: AND jtr.terr_id = jt.terr_id )
947: AND EXISTS ( SELECT jtr.terr_id
948: FROM jtf_terr_rsc_all jtr

Line 948: FROM jtf_terr_rsc_all jtr

944: FROM jtf_terr_rsc_all jtr
945: WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)
946: AND jtr.terr_id = jt.terr_id )
947: AND EXISTS ( SELECT jtr.terr_id
948: FROM jtf_terr_rsc_all jtr
949: WHERE jtr.role IN ('SALES_REP', 'Sales Representative', 'SALES_MANAGER', 'Sales Manager', NULL)
950: AND jtr.terr_id = jt.terr_id )
951: GROUP BY hou.name, hou.organization_id
952: ) tdac,

Line 959: FROM jtf_terr_all jt, hr_organization_units hou

955: ( SELECT
956: hou.name,
957: hou.organization_id,
958: COUNT(*) TERR_CREATED_COUNT
959: FROM jtf_terr_all jt, hr_organization_units hou
960: WHERE jt.org_id = hou.organization_id
961: and EXISTS ( SELECT jtdr.terr_id
962: FROM jtf_terr_denorm_rules_all jtdr
963: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )

Line 962: FROM jtf_terr_denorm_rules_all jtdr

958: COUNT(*) TERR_CREATED_COUNT
959: FROM jtf_terr_all jt, hr_organization_units hou
960: WHERE jt.org_id = hou.organization_id
961: and EXISTS ( SELECT jtdr.terr_id
962: FROM jtf_terr_denorm_rules_all jtdr
963: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
964: AND jt.creation_date BETWEEN SYSDATE-7 AND SYSDATE+1
965: GROUP BY hou.name, hou.organization_id
966: ) tcc,

Line 973: FROM jtf_terr_all jt, hr_organization_units hou

969: ( SELECT
970: hou.name,
971: hou.organization_id,
972: COUNT(*) TERR_SOFT_DEL_COUNT
973: FROM jtf_terr_all jt, hr_organization_units hou
974: WHERE jt.org_id = hou.organization_id
975: and EXISTS ( SELECT jtdr.terr_id
976: FROM jtf_terr_denorm_rules_all jtdr
977: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )

Line 976: FROM jtf_terr_denorm_rules_all jtdr

972: COUNT(*) TERR_SOFT_DEL_COUNT
973: FROM jtf_terr_all jt, hr_organization_units hou
974: WHERE jt.org_id = hou.organization_id
975: and EXISTS ( SELECT jtdr.terr_id
976: FROM jtf_terr_denorm_rules_all jtdr
977: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
978: AND jt.END_DATE_ACTIVE BETWEEN SYSDATE-7 AND SYSDATE+1
979: GROUP BY hou.name, hou.organization_id
980: ) tsdc,

Line 987: FROM jtf_terr_all jt, hr_organization_units hou

983: ( SELECT
984: hou.name,
985: hou.organization_id,
986: COUNT(*) TERR_UPDATED_COUNT
987: FROM jtf_terr_all jt, hr_organization_units hou
988: WHERE jt.org_id = hou.organization_id
989: and EXISTS ( SELECT jtdr.terr_id
990: FROM jtf_terr_denorm_rules_all jtdr
991: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )

Line 990: FROM jtf_terr_denorm_rules_all jtdr

986: COUNT(*) TERR_UPDATED_COUNT
987: FROM jtf_terr_all jt, hr_organization_units hou
988: WHERE jt.org_id = hou.organization_id
989: and EXISTS ( SELECT jtdr.terr_id
990: FROM jtf_terr_denorm_rules_all jtdr
991: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
992: AND jt.LAST_UPDATE_DATE BETWEEN SYSDATE-7 AND SYSDATE+1
993: GROUP BY hou.name, hou.organization_id
994: ) tuc,

Line 1001: FROM jtf_terr_rsc_all jtr, hr_organization_units hou

997: ( SELECT
998: hou.name,
999: hou.organization_id,
1000: COUNT(DISTINCT jtr.resource_id) ACTIVE_DIST_REP_COUNT
1001: FROM jtf_terr_rsc_all jtr, hr_organization_units hou
1002: WHERE jtr.org_id = hou.organization_id
1003: AND EXISTS ( SELECT jtdr.terr_id
1004: FROM jtf_terr_denorm_rules_all jtdr
1005: WHERE jtdr.resource_exists_flag = 'Y'

Line 1004: FROM jtf_terr_denorm_rules_all jtdr

1000: COUNT(DISTINCT jtr.resource_id) ACTIVE_DIST_REP_COUNT
1001: FROM jtf_terr_rsc_all jtr, hr_organization_units hou
1002: WHERE jtr.org_id = hou.organization_id
1003: AND EXISTS ( SELECT jtdr.terr_id
1004: FROM jtf_terr_denorm_rules_all jtdr
1005: WHERE jtdr.resource_exists_flag = 'Y'
1006: AND jtdr.terr_id = jtr.terr_id and jtdr.source_id = p_param2)
1007:
1008: GROUP BY hou.name, hou.organization_id

Line 1022: FROM jtf_terr_all jt

1018:
1019: cursor c_NONACTIVE_GLOBAL IS
1020: SELECT 'All' name,
1021: COUNT(*) INACTIVE_TERR_COUNT
1022: FROM jtf_terr_all jt
1023: WHERE
1024: exists ( select jtua.terr_id
1025: from jtf_terr_usgs_all jtua
1026: where jtua.source_id = p_param2 and jtua.terr_id = jt.terr_id)

Line 1025: from jtf_terr_usgs_all jtua

1021: COUNT(*) INACTIVE_TERR_COUNT
1022: FROM jtf_terr_all jt
1023: WHERE
1024: exists ( select jtua.terr_id
1025: from jtf_terr_usgs_all jtua
1026: where jtua.source_id = p_param2 and jtua.terr_id = jt.terr_id)
1027: and NOT EXISTS ( SELECT jtdr.terr_id
1028: FROM jtf_terr_denorm_rules_all jtdr
1029: WHERE jtdr.terr_id = jt.terr_id );

Line 1028: FROM jtf_terr_denorm_rules_all jtdr

1024: exists ( select jtua.terr_id
1025: from jtf_terr_usgs_all jtua
1026: where jtua.source_id = p_param2 and jtua.terr_id = jt.terr_id)
1027: and NOT EXISTS ( SELECT jtdr.terr_id
1028: FROM jtf_terr_denorm_rules_all jtdr
1029: WHERE jtdr.terr_id = jt.terr_id );
1030:
1031: cursor c_NONACTIVE_BY_COUNTRY IS
1032: SELECT

Line 1036: FROM jtf_terr_all jt, hr_organization_units hou

1032: SELECT
1033: hou.name name,
1034: COUNT(*) INACTIVE_TERR_COUNT,
1035: hou.organization_id org_id
1036: FROM jtf_terr_all jt, hr_organization_units hou
1037: WHERE jt.org_id = hou.organization_id
1038: and exists ( select jtua.terr_id
1039: from jtf_terr_usgs_all jtua
1040: where jtua.source_id = p_param2 and jtua.terr_id = jt.terr_id)

Line 1039: from jtf_terr_usgs_all jtua

1035: hou.organization_id org_id
1036: FROM jtf_terr_all jt, hr_organization_units hou
1037: WHERE jt.org_id = hou.organization_id
1038: and exists ( select jtua.terr_id
1039: from jtf_terr_usgs_all jtua
1040: where jtua.source_id = p_param2 and jtua.terr_id = jt.terr_id)
1041: AND NOT EXISTS ( SELECT jtdr.terr_id
1042: FROM jtf_terr_denorm_rules_all jtdr
1043: WHERE jtdr.terr_id = jt.terr_id )

Line 1042: FROM jtf_terr_denorm_rules_all jtdr

1038: and exists ( select jtua.terr_id
1039: from jtf_terr_usgs_all jtua
1040: where jtua.source_id = p_param2 and jtua.terr_id = jt.terr_id)
1041: AND NOT EXISTS ( SELECT jtdr.terr_id
1042: FROM jtf_terr_denorm_rules_all jtdr
1043: WHERE jtdr.terr_id = jt.terr_id )
1044: GROUP BY hou.name, hou.organization_id
1045: ORDER BY INACTIVE_TERR_COUNT DESC;
1046: