[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_JSP_REPORTS
Source
1 package body JTF_TERR_JSP_REPORTS AS
2 /* $Header: jtfpjrpb.pls 120.7.12010000.11 2009/07/24 09:15:14 ppillai 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
11 -- This package is publicly available for use
12 --
13 -- HISTORY
14 -- 09/18/2001 EIHSU Created
15 -- 11/13/2001 ARPATEL Added SQL statements for intelligence report JSP
16 -- 11/20/2001 EIHSU removed dbms output messages
17 -- added active_on date conditions into def, chg reports
18 -- 05/06/2002 ARPATEL Added support for p_rpt_type = 'LOOKUP_TERR' in
19 -- definition_rpt proc - enh# 2109535
20 -- 05/18/2004 ACHANDA Bug # 3610389 : Make call to WF_NOTIFICATION.SubstituteSpecialChars
21 -- before rendering the data in jsp
22 -- 28/07/2008 GMARWAH Modified for Bug 7237992
23 -- 18/08/2008 GMARWAH Modified for bug 731589 to display operating unit
24 -- End of Comments
25 --
26
27 type terr_name_rec_type is record
28 (terr_id NUMBER,
29 name VARCHAR2(2000),
30 rank NUMBER,
31 start_date_active DATE,
32 end_date_active DATE,
33 last_update_date DATE,
34 description VARCHAR2(2000)
35 );
36
37 type terr_name_cur_type is REF CURSOR RETURN terr_name_rec_type;
38
39
40
41 ---------------------------------------------------------------
42 -- definition_rpt
43 -- Notes: This procedure handles Definitions and Changes Reports
44 -- arpatel 05/06/2002 - also handles Lookup territory details by terr_id
45 --
46 --
47 ---------------------------------------------------------------
48 procedure definition_rpt (p_param1 in varchar2,
49 p_param2 in varchar2,
50 p_param3 in varchar2,
51 p_param4 in varchar2,
52 p_param5 in varchar2,
53 p_rpt_type in varchar2,
54 x_result_tbl OUT NOCOPY report_out_tbl_type)
55 is
56
57 --lc_resource_id number := p_param1;
58 --lc_qual_usg_id number := p_param2;
59
60 ll_terr_id number;
61 ll_terr_name varchar2(300);
62 ll_terr_rank number;
63 l_out_index number;
64 l_out_rec report_out_rec_type;
65 lx_result_tbl report_out_tbl_type := report_out_tbl_type();
66
67 qual_type_count number;
68 resource_count number;
69 rsc_access_count number;
70 terr_qual_count number;
71 terr_rsc_qual_count number;
72 qual_value_count number;
73 rsc_qual_value_count number;
74 cumulative_qual_val_count number;
75 qual_colspan number;
76 terr_colspan number;
77 terr_rspan_row_number number;
78 rsc_access_rspan_row_number number;
79 tqual_rspan_row_number number;
80 qval_rspan_row_number number;
81
82 terr_name_cur terr_name_cur_type;
83 rec_terr terr_name_rec_type;
84 rpt_type VARCHAR2(30);
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
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
99 );
100 */
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
110 CURSOR c_get_terr_qual (ci_terr_id NUMBER) IS
111 /* SELECT TERR_QUAL_ID,
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 ;
121 */-- Commented for bug 7237992
122
123 SELECT
124 JTQ.TERR_QUAL_ID,
125 JTQ.TERR_ID,
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
135 WHERE JTQ.QUAL_USG_ID = JQU.QUAL_USG_ID
136 AND JTQ.ORG_ID = JQU.ORG_ID
137 AND JQU.SEEDED_QUAL_ID = JSQ.SEEDED_QUAL_ID
138 AND JSQ.LANGUAGE = USERENV('LANG')
139 AND JQU.QUAL_TYPE_USG_ID = JQTU.QUAL_TYPE_USG_ID
140 AND JQTU.QUAL_TYPE_ID = JQT.QUAL_TYPE_ID
141 AND jtq.terr_id = ci_terr_id
142 AND jtq.terr_qual_id is not null
143 AND jqt.qual_type_id <> -1001 ;
144
145
146
147 CURSOR c_get_terr_rsc_qual (ci_terr_id NUMBER) IS
148 SELECT TERR_QUAL_ID,
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
158 -- dynamic qualifier values
159 /*
160 CURSOR c_get_terr_values (ci_terr_qual_id NUMBER) IS
161 SELECT j1.TERR_VALUE_ID
162 , j1.COMPARISON_OPERATOR
163 -- , WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR_DESC) LOW_VALUE_CHAR_DESC
164 --, WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR_DESC) HIGH_VALUE_CHAR_DESC
165 -- Commented for bug 8365663
166 ,decode(j2.display_type, 'CHAR'
167 ,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)
168 ,'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)
169 ,'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)
170 ,WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR)) low_value_char_desc
171
172 ,decode(j2.display_type, 'CHAR'
173 ,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)
174 ,'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)
175 , WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR)) high_value_char_desc
176 , j1.LOW_VALUE_NUMBER
177 , j1.HIGH_VALUE_NUMBER
178 , j1.INTEREST_TYPE
179 , j1.PRIMARY_INTEREST_CODE
180 , j1.SECONDARY_INTEREST_CODE
181 , j1.CURRENCY_DESC
182 , j1.LOW_VALUE_CHAR_ID
183 , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_NAME) CNR_GROUP_NAME
184 , WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE1_DESC) VALUE1_DESC
185 , WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE2_DESC) VALUE2_DESC
186 , WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE3_DESC) VALUE3_DESC
187 , j1.LOW_VALUE_DATE
188 , j1.HIGH_VALUE_DATE
189 , DISPLAY_TYPE
190 , CONVERT_TO_ID_FLAG
191 -- more to come directly from the jtf_terr_values_desc_v view
192 FROM jtf_terr_values_desc_v j1
193 WHERE j1.terr_qual_id = ci_terr_qual_id
194 and j1.terr_value_id is not null -- added becuse we have some real bad data in jtadom
195 ORDER BY j1.LOW_VALUE_CHAR_DESC, j1.COMPARISON_OPERATOR;*/--COmmented for bug 7237992
196 /*
197 SELECT distinct j1.TERR_VALUE_ID
198 , j1.COMPARISON_OPERATOR
199 , WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR) LOW_VALUE_CHAR_DESC
200 , WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR) HIGH_VALUE_CHAR_DESC
201 , j1.LOW_VALUE_NUMBER
202 , j1.HIGH_VALUE_NUMBER
203 , j1.INTEREST_TYPE_ID INTEREST_TYPE
204 , j1.PRIMARY_INTEREST_CODE_ID PRIMARY_INTEREST_CODE
205 , j1.SECONDARY_INTEREST_CODE_ID SECONDARY_INTEREST_CODE
206 , j1.CURRENCY_CODE CURRENCY_DESC
207 , j1.LOW_VALUE_CHAR_ID
208 , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME
209 , WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE1_ID) VALUE1_DESC
210 , WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE2_ID) VALUE2_DESC
211 , WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE3_ID) VALUE3_DESC
212 , null LOW_VALUE_DATE
213 , null HIGH_VALUE_DATE
214 , DISPLAY_TYPE
215 , CONVERT_TO_ID_FLAG
216 FROM jtf_terr_values_all j1, JTF_QUAL_USGS_ALL j2, JTF_TERR_QUAL_ALL j3
217 WHERE j1.terr_qual_id = ci_terr_qual_id
218 AND j1.terr_qual_id = j3.terr_qual_id
219 AND j2.QUAL_USG_ID = j3.QUAL_USG_ID
220 AND j1.terr_value_id is not null
221 ORDER BY j1.COMPARISON_OPERATOR;
222 */
223
224 CURSOR c_get_terr_values (ci_terr_qual_id NUMBER) IS
225 SELECT distinct j1.TERR_VALUE_ID
226 , j1.COMPARISON_OPERATOR
227 --, WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR) LOW_VALUE_CHAR_DESC
228 --, WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR) HIGH_VALUE_CHAR_DESC
229 -- Commented for bug 8365663
230 ,decode(j2.display_type, 'CHAR'
231 ,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)
232 ,'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)
233 ,'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)
234 ,WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR))
235 low_value_char_desc
236
237 ,decode(j2.display_type, 'CHAR'
238 ,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)
239 ,'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)
240 , WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR))
241 high_value_char_desc
242 , j1.LOW_VALUE_NUMBER
243 , j1.HIGH_VALUE_NUMBER
244 -- , j1.INTEREST_TYPE_ID INTEREST_TYPE
245 -- , j1.PRIMARY_INTEREST_CODE_ID PRIMARY_INTEREST_CODE
246 -- , j1.SECONDARY_INTEREST_CODE_ID SECONDARY_INTEREST_CODE
247 , 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
248 , 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
249 , 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
250
251 , j1.CURRENCY_CODE CURRENCY_DESC
252 , j1.LOW_VALUE_CHAR_ID
253 , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME
254 , 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),
255 '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',
256 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',
257 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
258 , 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,
259 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),
260 '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
261 , 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),
262 '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
263 , null LOW_VALUE_DATE
264 , null HIGH_VALUE_DATE
265 , DISPLAY_TYPE
266 , CONVERT_TO_ID_FLAG
267 FROM jtf_terr_values_all j1, JTF_QUAL_USGS_ALL j2, JTF_TERR_QUAL_ALL j3
268 WHERE j1.terr_qual_id = ci_terr_qual_id
269 AND j1.terr_qual_id = j3.terr_qual_id
270 AND j2.QUAL_USG_ID = j3.QUAL_USG_ID
271 AND j1.terr_value_id is not null
272 ORDER BY j1.COMPARISON_OPERATOR;
273 -- resources
274 CURSOR c_get_resource(ci_terr_id NUMBER) IS
275 /* select resource_id, WF_NOTIFICATION.SubstituteSpecialChars(resource_name) resource_name, resource_type, terr_rsc_id
276 from jtf_terr_resources_v jtrv
277 where jtrv.terr_id = ci_terr_id
278 order by resource_name;
279 */
280 select resource_id, WF_NOTIFICATION.SubstituteSpecialChars(jtf_territory_resource_pvt.get_resource_name(
281 RESOURCE_ID , DECODE( RESOURCE_TYPE , 'RS_SUPPLIER', 'RS_SUPPLIER_CONTACT' ,
282 RESOURCE_TYPE ) )) resource_name, resource_type, terr_rsc_id
283 from JTF_TERR_RSC
284 where terr_id = ci_terr_id
285 order by resource_name;
286
287 -- resource accesses
288 CURSOR c_get_rsc_access(ci_terr_rsc_id NUMBER) IS
289 select terr_rsc_access_id, access_type, WF_NOTIFICATION.SubstituteSpecialChars(meaning) meaning
290 from jtf_terr_rsc_access_v
291 where terr_rsc_id = ci_terr_rsc_id;
292
293 lp_sysdate DATE := SYSDATE;
294 l_match_qual NUMBER := 0;
295
296 /* ARPATEL: 10/16, bug#2832442 */
297 l_resource_id VARCHAR2(2000);
298 l_resource_type VARCHAR2(2000);
299
300 /*Added for bug 7315889 */
301 CURSOR c_get_operating_unit(ci_terr_id VARCHAR2) IS
302 SELECT distinct hr.name operating_unit
303 FROM hr_operating_units hr, jtf_terr_all jt
304 WHERE hr.organization_id = jt.org_id
305 AND jt.terr_id = ci_terr_id;
306
307 -- ADDED FOR BUG 7315889
308 l_terr_operating_unit hr_operating_units.name%TYPE;
309
310 begin
311
312 --dbms_output.put_line('JTF_TERR_JSP_REPORTS.definition_rpt: BEGIN ');
313 --dbms_output.put_line(p_param1 || ' / ' || p_param2 || ' / ' || p_param3 || ' / ' || p_param4 || ' / ' || p_param5);
314 -- loop through cur_terr
315 l_out_index := 0;
316
317 /* arpatel 05/06/02 enh# 2109535 */
318 if p_rpt_type = 'LOOKUP_TERR' and p_param5 IS NOT NULL then
319 open terr_name_cur for
320 select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
321 j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
322 from jtf_terr_all j
323 where j.terr_id = p_param5;
324
325
326 -- ADDED FOR BUG 7315889
327 OPEN c_get_operating_unit(p_param5);
328 FETCH c_get_operating_unit INTO l_terr_operating_unit;
329 CLOSE c_get_operating_unit;
330
331 elsif p_rpt_type = 'DEFINITION' then
332 if ((p_param5 is null) or (p_param5 = '')) then
333
334 /* ARPATEL: 10/16, bug#2832442 */
335 --parse the resource_id to extract the resource_type
336 select SUBSTR(p_param1, 1,INSTR(p_param1, 'R')-1)
337 into l_resource_id
338 from dual;
339
340 select SUBSTR(p_param1, INSTR(p_param1, 'R'))
341 into l_resource_type
342 from dual;
343
344 /* ARPATEL: 10/16, END OF bug#2832442 */
345
346 --dbms_output.put_line('get territory by property');
347 open terr_name_cur for
348 select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
349 j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
350 from jtf_terr j
351 WHERE ( TRUNC(j.end_date_active) >= NVL(p_param4, lp_sysdate)
352 AND
353 TRUNC(j.start_date_active) <= NVL(p_param4, lp_sysdate)
354 )
355 AND EXISTS
356 ( select jtr.terr_id
357 from jtf_terr_rsc jtr, jtf_terr_qual jtq, jtf_terr_usgs jtu
358 where jtr.terr_id = jtq.terr_id
359 and jtr.terr_id = jtu.terr_id
360 and jtr.resource_id = decode(l_resource_id ,null, jtr.resource_id, l_resource_id)
361 /* ARPATEL: 10/16, bug#2832442 */
362 and jtr.resource_type = decode(l_resource_type ,null, jtr.resource_type, l_resource_type)
363 and jtq.qual_usg_id = decode(p_param2 ,null, jtq.qual_usg_id, p_param2)
364 and jtu.source_id = decode(p_param3 ,null, jtu.source_id, p_param3)
365 AND jtr.terr_id = j.terr_id
366 )
367 AND NOT EXISTS (
368 SELECT jt.terr_id
369 FROM jtf_terr_all jt
370 WHERE ( ( NVL(jt.end_date_active, lp_sysdate) <= NVL(p_param4, lp_sysdate) ) OR
371 ( NVL(jt.start_date_active, lp_sysdate) > NVL(p_param4, lp_sysdate) )
372 )
373 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
374 START WITH jt.terr_id = j.terr_id ) ;
375 else
376 --dbms_output.put_line('get territory by_id mode ');
377 open terr_name_cur for
378 select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
379 j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
380 from jtf_terr j
381 where j.terr_id = p_param5;
382
383 end if;
384 elsif p_rpt_type = 'CHANGES' then
385 --dbms_output.put_line('creating changes cursor ');
386 open terr_name_cur for
387 select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
388 j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
389 from jtf_terr j, jtf_terr_usgs jtu
390 where 1=1
391 --and j.terr_id = '19027'
392 AND j.terr_id = jtu.terr_id
393 AND j.start_date_active <= sysdate
394 AND trunc(j.terr_update_date) >= p_param1
395 AND trunc(j.terr_update_date) <= NVL(p_param2, sysdate)
396 AND ( TRUNC(j.end_date_active) >= NVL(p_param4, lp_sysdate)
397 AND
398 TRUNC(j.start_date_active) <= NVL(p_param4, lp_sysdate)
399 )
400 AND jtu.source_id = NVL(p_param3, jtu.source_id)
401 AND NOT EXISTS (
402 SELECT jt.terr_id
403 FROM jtf_terr_all jt
404 WHERE ( ( NVL(jt.end_date_active, lp_sysdate) <= NVL(p_param4, lp_sysdate) ) OR
405 ( NVL(jt.start_date_active, lp_sysdate) >= NVL(p_param4, lp_sysdate) )
406 )
407 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
408 START WITH jt.terr_id = j.terr_id ) ;
409 end if;
410
411
412 -- list from terr_name_cur
413 loop
414 fetch terr_name_cur into rec_terr;
415 exit when terr_name_cur%notfound;
416 --dbms_output.put_line('territory cursor rec_terr.terr_id: ' || rec_terr.terr_id);
417 terr_rspan_row_number := 0;
418 rsc_access_rspan_row_number := 0;
419 tqual_rspan_row_number:= 0;
420 qval_rspan_row_number := 0;
421
422 lx_result_tbl.extend;
423 l_out_index := l_out_index + 1;
424 lx_result_tbl(l_out_index):= l_out_rec;
425
426 lx_result_tbl(l_out_index).column1 := rec_terr.terr_id;
427 lx_result_tbl(l_out_index).column2 := rec_terr.name;
428 lx_result_tbl(l_out_index).column3 := rec_terr.rank;
429 lx_result_tbl(l_out_index).column4 := rec_terr.description;
430 lx_result_tbl(l_out_index).column12 := rec_terr.start_date_active;
431 lx_result_tbl(l_out_index).column13 := rec_terr.end_date_active;
432 lx_result_tbl(l_out_index).column14 := rec_terr.last_update_date;
433
434 lx_result_tbl(l_out_index).column15 := l_out_index;
435 lx_result_tbl(l_out_index).column19 := 'TERR_PTY';
436
437 lx_result_tbl(l_out_index).column18 := l_terr_operating_unit; -- ADDED FOR BUG 7315889
438
439 terr_rspan_row_number := l_out_index;
440
441
442 -- list from c_get_qual_types
443 qual_type_count := 0;
444
445 IF p_rpt_type <> 'LOOKUP_TERR' THEN
446 for rec_qual_type in c_get_qual_types(rec_terr.terr_id) loop
447 --dbms_output.put_line(' qual types cursor: rec_qual_type.qual_type_usg_id= '|| rec_qual_type.qual_type_usg_id);
448 -- for HTML column formatting
449 qual_type_count := qual_type_count + 1;
450
451 lx_result_tbl.extend;
452 l_out_index := l_out_index + 1;
453 lx_result_tbl(l_out_index):= l_out_rec;
454
455 lx_result_tbl(l_out_index).column1 := rec_terr.terr_id;
456 lx_result_tbl(l_out_index).column2 := rec_terr.name;
457 lx_result_tbl(l_out_index).column3 := rec_terr.rank;
458 lx_result_tbl(l_out_index).column12 := rec_terr.start_date_active;
459 lx_result_tbl(l_out_index).column13 := rec_terr.end_date_active;
460 lx_result_tbl(l_out_index).column14 := rec_terr.last_update_date;
461
462 lx_result_tbl(l_out_index).column4 := rec_qual_type.qual_type_usg_id;
463 lx_result_tbl(l_out_index).column5 := rec_qual_type.qualifier_type_description;
464 lx_result_tbl(l_out_index).column6 := rec_qual_type.qualifier_type_name;
465
466 lx_result_tbl(l_out_index).column15 := l_out_index;
467 lx_result_tbl(l_out_index).column19 := 'QUAL_TYPE';
468
469 end loop;
470 END IF; --p_rpt_type <> 'LOOKUP_TERR'
471
472 -- loop through c_get_terr_qual
473 terr_qual_count := 0;
474
475 for rec_terr_qual in c_get_terr_qual(rec_terr.terr_id) loop
476 --dbms_output.put_line(' tx qual cursor: rec_terr_qual.terr_qual_id =' || rec_terr_qual.TERR_QUAL_ID);
477 qual_value_count := 0;
478
479 for rec_qual_value in c_get_terr_values(rec_terr_qual.TERR_QUAL_ID)loop
480 --dbms_output.put_line(' qual value cursor rec_qual_value.TERR_VALUE_ID = ' || rec_qual_value.TERR_VALUE_ID);
481 l_match_qual := 0;
482 qual_value_count := qual_value_count + 1;
483
484 lx_result_tbl.extend;
485 l_out_index := l_out_index + 1;
486 lx_result_tbl(l_out_index):= l_out_rec;
487
488 if qual_value_count = 1 then
489 qval_rspan_row_number := l_out_index;
490 end if;
491
492 lx_result_tbl(l_out_index).column1 := rec_terr.terr_id;
493 lx_result_tbl(l_out_index).column2 := rec_terr.name;
494 lx_result_tbl(l_out_index).column3 := rec_terr.rank;
495
496 lx_result_tbl(l_out_index).column4 := rec_terr_qual.qual_usg_id;
497 lx_result_tbl(l_out_index).column5 := rec_terr_qual.terr_qual_id;
498 lx_result_tbl(l_out_index).column6 := rec_terr_qual.qualifier_name;
499
500 lx_result_tbl(l_out_index).column7 := rec_qual_value.TERR_VALUE_ID;
501 lx_result_tbl(l_out_index).column8 := rec_qual_value.COMPARISON_OPERATOR;
502 lx_result_tbl(l_out_index).column15 := l_out_index;
503 lx_result_tbl(l_out_index).column20 := qval_rspan_row_number;
504 lx_result_tbl(l_out_index).column16 := 0;
505 lx_result_tbl(l_out_index).column17 := 0;
506 lx_result_tbl(l_out_index).column19 := 'QUAL_VAL';
507
508 if qual_value_count = 1 then
509 lx_result_tbl(l_out_index).column17 := terr_qual_count;
510 else
511 lx_result_tbl(l_out_index).column17 := 0;
512 end if;
513
514 --lx_result_tbl(l_out_index).column18 := qual_value_count;
515 --------------------------------------
516 -- Deal with all the display types
517 --------------------------------------
518
519 if rec_qual_value.display_type = 'CHAR' then
520 if rec_qual_value.CONVERT_TO_ID_FLAG = 'Y' then
521 lx_result_tbl(l_out_index).column9 := rec_qual_value.low_value_char_desc;
522 lx_result_tbl(l_out_index).column10 := rec_qual_value.high_value_char_desc;
523 else
524 lx_result_tbl(l_out_index).column9 := rec_qual_value.low_value_char_desc;
525 lx_result_tbl(l_out_index).column10 := rec_qual_value.high_value_char_desc;
526 end if;
527 elsif rec_qual_value.display_type = 'INTEREST_TYPE' then
528 lx_result_tbl(l_out_index).column9 := rec_qual_value.interest_type;
529 lx_result_tbl(l_out_index).column10 := rec_qual_value.primary_interest_code;
530 lx_result_tbl(l_out_index).column11 := rec_qual_value.secondary_interest_code;
531 elsif rec_qual_value.display_type = 'DATE' then
532 lx_result_tbl(l_out_index).column9 := rec_qual_value.low_value_date;
533 lx_result_tbl(l_out_index).column10 := rec_qual_value.high_value_date;
534 elsif rec_qual_value.display_type = 'NUMBER' then
535 lx_result_tbl(l_out_index).column9 := rec_qual_value.low_value_number;
536 lx_result_tbl(l_out_index).column10 := rec_qual_value.high_value_number;
537 -- Fix for bug 6964643. The display type is NUMERIC for the qualifier NO OF EMLOYEES.
538 elsif rec_qual_value.display_type = 'NUMERIC' then
539 lx_result_tbl(l_out_index).column9 := rec_qual_value.low_value_number;
540 lx_result_tbl(l_out_index).column10 := rec_qual_value.high_value_number;
541 elsif rec_qual_value.display_type = 'CURRENCY' then
542 lx_result_tbl(l_out_index).column9 := rec_qual_value.low_value_number;
543 lx_result_tbl(l_out_index).column10 := rec_qual_value.high_value_number;
544 lx_result_tbl(l_out_index).column11 := rec_qual_value.currency_desc;
545 elsif rec_qual_value.display_type = 'CHAR_2IDS' then
546 lx_result_tbl(l_out_index).column9 := rec_qual_value.value1_desc;
547 lx_result_tbl(l_out_index).column10 := rec_qual_value.value2_desc;
548 --elsif rec_qual_value.display_type = 'COMPETENCE' then
549
550 elsif rec_qual_value.display_type = 'DEP_2FIELDS' then
551 lx_result_tbl(l_out_index).column9 := rec_qual_value.value1_desc;
552 lx_result_tbl(l_out_index).column10 := rec_qual_value.value2_desc;
553
554 elsif rec_qual_value.display_type = 'DEP_2FIELDS_CHAR_2IDS' then
555 lx_result_tbl(l_out_index).column9 := rec_qual_value.value1_desc;
556 lx_result_tbl(l_out_index).column10 := rec_qual_value.value2_desc;
557
558 else
559 lx_result_tbl(l_out_index).column9 := rec_qual_value.low_value_char_desc;
560 lx_result_tbl(l_out_index).column10 := rec_qual_value.high_value_char_desc;
561 end if;
562 -- increment qual_value_count
563 --qual_value_count := qual_value_count + 1;
564
565 --LOOKUP_TERR processing for CNR
566 if p_rpt_type = 'LOOKUP_TERR' and rec_terr_qual.qual_usg_id = -1012 --CNR
567 and rec_qual_value.COMPARISON_OPERATOR IN ('BETWEEN','=','LIKE')
568 then
569 if rec_qual_value.COMPARISON_OPERATOR = 'BETWEEN'
570 then
571 begin
572 select 1 into l_match_qual from dual
573 where UPPER(p_param3) BETWEEN lx_result_tbl(l_out_index).column9 and lx_result_tbl(l_out_index).column10;
574
575 exception
576 when no_data_found then l_match_qual := 0;
577 end;
578
579 end if;
580
581 if rec_qual_value.COMPARISON_OPERATOR = '='
582 then
583 begin
584 select 1 into l_match_qual from dual
585 where UPPER(p_param3) = lx_result_tbl(l_out_index).column9;
586
587 exception
588 when no_data_found then l_match_qual := 0;
589 end;
590
591 end if;
592
593 if rec_qual_value.COMPARISON_OPERATOR = 'LIKE'
594 then
595 begin
596 select 1 into l_match_qual from dual
597 where UPPER(p_param3) LIKE lx_result_tbl(l_out_index).column9;
598
599 exception
600 when no_data_found then l_match_qual := 0;
601 end;
602
603 end if;
604
605 if l_match_qual = 0
606 then
607 if qual_value_count = 1 then
608 qval_rspan_row_number := 0;
609 end if;
610 l_out_index := l_out_index - 1;
611 qual_value_count := qual_value_count - 1;
612 lx_result_tbl.trim;
613 end if;
614
615 end if;--LOOKUP_TERR processing for CNR
616
617 --LOOKUP_TERR processing for Postal Code
618 if p_rpt_type = 'LOOKUP_TERR' and rec_terr_qual.qual_usg_id = -1007 --Postal Code
619 and rec_qual_value.COMPARISON_OPERATOR IN ('BETWEEN','=','LIKE')
620 then
621
622 if rec_qual_value.COMPARISON_OPERATOR = 'BETWEEN'
623 then
624 begin
625 select 1 into l_match_qual from dual
626 where UPPER(p_param4) BETWEEN lx_result_tbl(l_out_index).column9 and lx_result_tbl(l_out_index).column10;
627
628 exception
629 when no_data_found then l_match_qual := 0;
630 end;
631
632 end if;
633
634 if rec_qual_value.COMPARISON_OPERATOR = '='
635 then
636 begin
637 select 1 into l_match_qual from dual
638 where UPPER(p_param4) = lx_result_tbl(l_out_index).column9;
639
640 exception
641 when no_data_found then l_match_qual := 0;
642 end;
643
644 end if;
645
646 if rec_qual_value.COMPARISON_OPERATOR = 'LIKE'
647 then
648 begin
649 select 1 into l_match_qual from dual
650 where UPPER(p_param4) LIKE lx_result_tbl(l_out_index).column9;
651
652 exception
653 when no_data_found then l_match_qual := 0;
654 end;
655
656 end if;
657
658 if l_match_qual = 0
659 then
660 if qual_value_count = 1 then
661 qval_rspan_row_number := 0;
662 end if;
663 l_out_index := l_out_index - 1;
664 qual_value_count := qual_value_count - 1;
665 lx_result_tbl.trim;
666 end if;
667
668 end if;--LOOKUP_TERR processing for Postal Code
669
670 end loop; -- all values
671 if qval_rspan_row_number <> 0 then
672 lx_result_tbl(qval_rspan_row_number).column17 := qual_value_count;
673 end if;
674 end loop; -- all qualifier
675
676 -- loop through c_get_terr_rsc_qual
677 terr_rsc_qual_count := 0;
678
679 IF p_rpt_type <> 'LOOKUP_TERR' THEN
680 for rec_terr_rsc_qual in c_get_terr_rsc_qual(rec_terr.terr_id) loop
681 --dbms_output.put_line(' resource qual cursor rec_terr_rsc_qual.terr_qual_id = ' || rec_terr_rsc_qual.terr_qual_id);
682 rsc_qual_value_count := 0;
683
684 for rec_rsc_qual_value in c_get_terr_values(rec_terr_rsc_qual.TERR_qual_ID)loop
685
686 rsc_qual_value_count := rsc_qual_value_count + 1;
687
688 lx_result_tbl.extend;
689 l_out_index := l_out_index + 1;
690 lx_result_tbl(l_out_index):= l_out_rec;
691
692 if rsc_qual_value_count = 1 then
693 qval_rspan_row_number := l_out_index;
694 end if;
695
696 lx_result_tbl(l_out_index).column1 := rec_terr.terr_id;
697 lx_result_tbl(l_out_index).column2 := rec_terr.name;
698 lx_result_tbl(l_out_index).column3 := rec_terr.rank;
699
700 lx_result_tbl(l_out_index).column4 := rec_terr_rsc_qual.qual_usg_id;
701 lx_result_tbl(l_out_index).column5 := rec_terr_rsc_qual.terr_qual_id;
702 lx_result_tbl(l_out_index).column6 := rec_terr_rsc_qual.qualifier_name;
703
704 lx_result_tbl(l_out_index).column7 := rec_rsc_qual_value.TERR_VALUE_ID;
705 lx_result_tbl(l_out_index).column8 := rec_rsc_qual_value.COMPARISON_OPERATOR;
706 lx_result_tbl(l_out_index).column15 := l_out_index;
707 lx_result_tbl(l_out_index).column20 := qval_rspan_row_number;
708 lx_result_tbl(l_out_index).column16 := 0;
709 lx_result_tbl(l_out_index).column17 := 0;
710 lx_result_tbl(l_out_index).column19 := 'RSC_QUAL_VAL';
711
712 if rsc_qual_value_count = 1 then
713 lx_result_tbl(l_out_index).column17 := terr_rsc_qual_count;
714 else
715 lx_result_tbl(l_out_index).column17 := 0;
716 end if;
717
718 --lx_result_tbl(l_out_index).column18 := rsc_qual_value_count;
719
720 end loop; -- all values
721 if qval_rspan_row_number <> 0 then
722 lx_result_tbl(qval_rspan_row_number).column17 := rsc_qual_value_count;
723 end if;
724 end loop; -- all rsc rsc_qualifier
725 END IF; -- p_rpt_type <> 'LOOKUP_TERR'
726
727
728
729
730 -- list from c_get_resource(current_terr_id)
731 resource_count := 0;
732
733 IF p_rpt_type <> 'LOOKUP_TERR' THEN
734 for rec_res in c_get_resource(rec_terr.terr_id) loop
735 --dbms_output.put_line(' resource cursor: rec_res.resource_id= ' || rec_res.resource_id);
736 lx_result_tbl.extend;
737 l_out_index := l_out_index + 1;
738 lx_result_tbl(l_out_index):= l_out_rec;
739
740 lx_result_tbl(l_out_index).column1 := rec_terr.terr_id;
741 lx_result_tbl(l_out_index).column2 := rec_terr.name;
742 lx_result_tbl(l_out_index).column3 := rec_terr.rank;
743 lx_result_tbl(l_out_index).column12 := rec_terr.start_date_active;
744 lx_result_tbl(l_out_index).column13 := rec_terr.end_date_active;
745 lx_result_tbl(l_out_index).column14 := rec_terr.last_update_date;
746
747 lx_result_tbl(l_out_index).column4 := rec_res.resource_id;
748 lx_result_tbl(l_out_index).column5 := rec_res.resource_name;
749 lx_result_tbl(l_out_index).column6 := rec_res.terr_rsc_id; --resource_type;
750
751 lx_result_tbl(l_out_index).column15 := l_out_index;
752 lx_result_tbl(l_out_index).column19 := 'RESOURCE';
753 rsc_access_rspan_row_number := l_out_index;
754
755 -- list from c_get_rsc_access
756 rsc_access_count := 0;
757
758 for rec_rsc_access in c_get_rsc_access(rec_res.terr_rsc_id) loop
759 --dbms_output.put_line('rsc access cursor ');
760 -- for HTML column formatting
761 rsc_access_count := rsc_access_count + 1;
762
763 lx_result_tbl.extend;
764 l_out_index := l_out_index + 1;
765 lx_result_tbl(l_out_index):= l_out_rec;
766
767 --dbms_output.put_line('rec_terr.terr_id = ' || rec_terr.terr_id);
768
769 lx_result_tbl(l_out_index).column1 := rec_terr.terr_id;
770 lx_result_tbl(l_out_index).column2 := rec_terr.name;
771 lx_result_tbl(l_out_index).column3 := rec_terr.rank;
772 lx_result_tbl(l_out_index).column12 := rec_terr.start_date_active;
773 lx_result_tbl(l_out_index).column13 := rec_terr.end_date_active;
774 lx_result_tbl(l_out_index).column14 := rec_terr.last_update_date;
775
776 lx_result_tbl(l_out_index).column4 := rec_res.resource_id;
777 lx_result_tbl(l_out_index).column5 := rec_res.resource_name;
778 lx_result_tbl(l_out_index).column6 := rec_res.terr_rsc_id; --resource_type;
779
780 lx_result_tbl(l_out_index).column7 := rec_rsc_access.terr_rsc_access_id;
781 lx_result_tbl(l_out_index).column8 := rec_rsc_access.meaning;
782
783 lx_result_tbl(l_out_index).column15 := l_out_index;
784 lx_result_tbl(l_out_index).column19 := 'RSC_ACCESS';
785 lx_result_tbl(l_out_index).column16 := 0;
786 lx_result_tbl(l_out_index).column17 := 1;
787
788
789 end loop; -- resource accesses
790 -- --dbms_output.put_line('setting rsc_access at ' || rsc_access_rspan_row_number || 'rowspan: ' || rsc_access_count);
791 if rsc_access_rspan_row_number <> 0 then
792 lx_result_tbl(rsc_access_rspan_row_number).column17 := rsc_access_count;
793 end if;
794 end loop; -- resources
795 END IF; -- p_rpt_type <> 'LOOKUP_TERR'
796
797 -- this used to be row span count for resource but now we use in conjunction at bottom
798 --lx_result_tbl(terr_rspan_row_number).column16 := resource_count;
799
800 -----------------------------
801
802
803 if terr_rspan_row_number <> 0 then
804 lx_result_tbl(terr_rspan_row_number).column16 := l_out_index - terr_rspan_row_number + 1;
805 else
806 null;
807 --dbms_output.put_line('rowspan for row ' || terr_rspan_row_number || ': ' ||(l_out_index - terr_rspan_row_number + 1) );
808 end if;
809 end loop; -- all territories
810 x_result_tbl := lx_result_tbl;
811 --dbms_output.put_line('lx_result_tbl.last: ' || lx_result_tbl.last);
812 -- exception
813 -- when others then
814 -- return;
815
816
817 end DEFINITION_RPT;
818
819
820 ---------------------------------------------------------------
821 -- CHANGES_RPT
822 -- Notes: Territory Changes Report
823 -- NOT USED SINCE WE CALL DEFINITIONS REPORT
824 --
825 ---------------------------------------------------------------
826
827 PROCEDURE CHANGES_RPT (p_param1 in varchar2,
828 p_param2 in varchar2,
829 p_param3 in varchar2,
830 p_param4 in varchar2,
831 p_param5 in varchar2,
832 x_result_tbl OUT NOCOPY report_out_tbl_type)
833 IS
834
835 begin
836 null;
837 end CHANGES_RPT;
838
839 ---------------------------------------------------------------
840 -- INTEL_RPT
841 -- Notes: Territory Changes Report
842 -- NOT CURRENTLY USED AS SQL CALLS MADE FROM JSP
843 -- arpatel 11/13 Adding sql calls to INTEL_RPT
844 ---------------------------------------------------------------
845
846 PROCEDURE INTEL_RPT ( p_param1 in varchar2,
847 p_param2 in varchar2,
848 p_param3 in varchar2,
849 p_param4 in varchar2,
850 p_param5 in varchar2,
851 x_result_tbl OUT NOCOPY report_out_tbl_type)
852 IS
853 lx_result_tbl report_out_tbl_type; -- := report_out_tbl_type();
854 l_out_index number := 0;
855
856 --ACTIVE GLOBAL CURSOR
857 cursor c_ACTIVE_GLOBAL IS
858 SELECT 'All' name,
859 atc.ACTIVE_TERR_COUNT ACTIVE_TERR_COUNT,
860 tdac.TERR_DUAL_ASSGN_COUNT TERR_DUAL_ASSGN_COUNT,
861 tcc.TERR_CREATED_COUNT TERR_CREATED_COUNT,
862 tsdc.TERR_SOFT_DEL_COUNT TERR_SOFT_DEL_COUNT,
863 tuc.TERR_UPDATED_COUNT TERR_UPDATED_COUNT,
864 arc.ACTIVE_DIST_REP_COUNT ACTIVE_DIST_REP_COUNT,
865 ROUND((atc.ACTIVE_TERR_COUNT / decode(arc.ACTIVE_DIST_REP_COUNT, 0, 1, arc.ACTIVE_DIST_REP_COUNT)),2) TERR_PER_REP
866 FROM
867 -- Total # of Active Territories
868 ( SELECT COUNT(*) ACTIVE_TERR_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 ) atc,
874
875 -- Total # of Territories that have Internal and External Reps
876 ( SELECT COUNT(*) TERR_DUAL_ASSGN_COUNT
877 FROM jtf_terr_all jt
878 WHERE EXISTS ( SELECT jtdr.terr_id
879 FROM jtf_terr_denorm_rules_all jtdr
880 WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
881 AND EXISTS ( SELECT jtr.terr_id
882 FROM jtf_terr_rsc_all jtr
883 WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)
884 AND jtr.terr_id = jt.terr_id )
885 AND EXISTS ( SELECT jtr.terr_id
886 FROM jtf_terr_rsc_all jtr
887 WHERE jtr.role IN ('SALES_REP', 'Sales Representative', 'SALES_MANAGER', 'Sales Manager', NULL)
888 AND jtr.terr_id = jt.terr_id )
889 ) tdac,
890
891 -- Territories created last 7 days
892 ( SELECT COUNT(*) TERR_CREATED_COUNT
893 FROM jtf_terr_all jt
894 WHERE EXISTS ( SELECT jtdr.terr_id
895 FROM jtf_terr_denorm_rules_all jtdr
896 WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
897 and jt.creation_date BETWEEN SYSDATE-7 AND SYSDATE+1
898 ) tcc,
899
900 -- Total # of (SOFT) DELETED Territories
901 ( SELECT COUNT(*) TERR_SOFT_DEL_COUNT
902 FROM jtf_terr_all jt
903 WHERE EXISTS ( SELECT jtdr.terr_id
904 FROM jtf_terr_denorm_rules_all jtdr
905 WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
906 and jt.end_date_active BETWEEN SYSDATE-7 AND SYSDATE+1
907 ) tsdc,
908
909 -- Total # of UPDATED Territories
910 ( SELECT COUNT(*) TERR_UPDATED_COUNT
911 FROM jtf_terr_all jt
912 WHERE EXISTS ( SELECT jtdr.terr_id
913 FROM jtf_terr_denorm_rules_all jtdr
914 WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
915 and jt.last_update_date BETWEEN SYSDATE-7 AND SYSDATE+1
916 ) tuc,
917
918 -- Total Distinct # of People Assigned to Active Territories -- 2930
919 ( SELECT COUNT(DISTINCT jtr.resource_id) ACTIVE_DIST_REP_COUNT
920 FROM jtf_terr_rsc_all jtr
921 WHERE EXISTS ( SELECT jtdr.terr_id
922 FROM jtf_terr_denorm_rules_all jtdr
923 WHERE jtdr.resource_exists_flag = 'Y'
924 AND jtdr.terr_id = jtr.terr_id and jtdr.source_id = p_param2)
925 ) arc ;
926
927 --ACTIVE COUNTRY CURSOR
928 cursor c_ACTIVE_BY_COUNTRY IS
929 SELECT
930 houo.name name,
931 NVL(atc.ACTIVE_TERR_COUNT, 0) ACTIVE_TERR_COUNT,
932 NVL(tdac.TERR_DUAL_ASSGN_COUNT, 0) TERR_DUAL_ASSGN_COUNT,
933 NVL(tcc.TERR_CREATED_COUNT, 0) TERR_CREATED_COUNT,
934 NVL(tsdc.TERR_SOFT_DEL_COUNT, 0) TERR_SOFT_DEL_COUNT,
935 NVL(tuc.TERR_UPDATED_COUNT, 0) TERR_UPDATED_COUNT,
936 NVL(arc.ACTIVE_DIST_REP_COUNT, 0) ACTIVE_DIST_REP_COUNT,
937 DECODE( arc.ACTIVE_DIST_REP_COUNT
938 , NULL, 'No Active Reps'
939 , ROUND((atc.ACTIVE_TERR_COUNT / arc.ACTIVE_DIST_REP_COUNT), 2)
940 ) TERR_PER_REP
941
942 FROM
943 hr_organization_units houo,
944 -- Total # of Active Territories -- 13918
945 ( SELECT
946 hou.name,
947 hou.organization_id,
948 COUNT(*) ACTIVE_TERR_COUNT
949 FROM jtf_terr_all jt, hr_organization_units hou
950 WHERE jt.org_id = hou.organization_id
951 AND EXISTS ( SELECT jtdr.terr_id
952 FROM jtf_terr_denorm_rules_all jtdr
953 WHERE jtdr.terr_id = jt.terr_id and jtdr.source_id = p_param2)
954 GROUP BY hou.name, hou.organization_id
955 ) atc,
956
957 -- Total # of Territories that have Internal and External Reps
958 ( SELECT
959 hou.name,
960 hou.organization_id,
961 COUNT(*) TERR_DUAL_ASSGN_COUNT
962 FROM jtf_terr_all jt, hr_organization_units hou
963 WHERE jt.org_id = hou.organization_id
964 AND EXISTS ( SELECT jtdr.terr_id
965 FROM jtf_terr_denorm_rules_all jtdr
966 WHERE jtdr.terr_id = jt.terr_id and jtdr.source_id = p_param2)
967 AND EXISTS ( SELECT jtr.terr_id
968 FROM jtf_terr_rsc_all jtr
969 WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)
970 AND jtr.terr_id = jt.terr_id )
971 AND EXISTS ( SELECT jtr.terr_id
972 FROM jtf_terr_rsc_all jtr
973 WHERE jtr.role IN ('SALES_REP', 'Sales Representative', 'SALES_MANAGER', 'Sales Manager', NULL)
974 AND jtr.terr_id = jt.terr_id )
975 GROUP BY hou.name, hou.organization_id
976 ) tdac,
977
978 -- Territories created last 7 days
979 ( SELECT
980 hou.name,
981 hou.organization_id,
982 COUNT(*) TERR_CREATED_COUNT
983 FROM jtf_terr_all jt, hr_organization_units hou
984 WHERE jt.org_id = hou.organization_id
985 and EXISTS ( SELECT jtdr.terr_id
986 FROM jtf_terr_denorm_rules_all jtdr
987 WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
988 AND jt.creation_date BETWEEN SYSDATE-7 AND SYSDATE+1
989 GROUP BY hou.name, hou.organization_id
990 ) tcc,
991
992 -- Total # of (SOFT) DELETED Territories
993 ( SELECT
994 hou.name,
995 hou.organization_id,
996 COUNT(*) TERR_SOFT_DEL_COUNT
997 FROM jtf_terr_all jt, hr_organization_units hou
998 WHERE jt.org_id = hou.organization_id
999 and EXISTS ( SELECT jtdr.terr_id
1000 FROM jtf_terr_denorm_rules_all jtdr
1001 WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
1002 AND jt.END_DATE_ACTIVE BETWEEN SYSDATE-7 AND SYSDATE+1
1003 GROUP BY hou.name, hou.organization_id
1004 ) tsdc,
1005
1006 -- Total # of UPDATED Territories
1007 ( SELECT
1008 hou.name,
1009 hou.organization_id,
1010 COUNT(*) TERR_UPDATED_COUNT
1011 FROM jtf_terr_all jt, hr_organization_units hou
1012 WHERE jt.org_id = hou.organization_id
1013 and EXISTS ( SELECT jtdr.terr_id
1014 FROM jtf_terr_denorm_rules_all jtdr
1015 WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
1016 AND jt.LAST_UPDATE_DATE BETWEEN SYSDATE-7 AND SYSDATE+1
1017 GROUP BY hou.name, hou.organization_id
1018 ) tuc,
1019
1020 -- Total Distinct # of People Assigned to Territories -- 2930
1021 ( SELECT
1022 hou.name,
1023 hou.organization_id,
1024 COUNT(DISTINCT jtr.resource_id) ACTIVE_DIST_REP_COUNT
1025 FROM jtf_terr_rsc_all jtr, hr_organization_units hou
1026 WHERE jtr.org_id = hou.organization_id
1027 AND EXISTS ( SELECT jtdr.terr_id
1028 FROM jtf_terr_denorm_rules_all jtdr
1029 WHERE jtdr.resource_exists_flag = 'Y'
1030 AND jtdr.terr_id = jtr.terr_id and jtdr.source_id = p_param2)
1031
1032 GROUP BY hou.name, hou.organization_id
1033 ) arc
1034 WHERE
1035 houo.organization_id = atc.organization_id
1036 AND houo.organization_id = tdac.organization_id(+)
1037 AND houo.organization_id = tcc.organization_id(+)
1038 AND houo.organization_id = tsdc.organization_id(+)
1039 AND houo.organization_id = tuc.organization_id(+)
1040 AND houo.organization_id = arc.organization_id(+)
1041 ORDER BY atc.ACTIVE_TERR_COUNT DESC;
1042
1043 cursor c_NONACTIVE_GLOBAL IS
1044 SELECT 'All' name,
1045 COUNT(*) INACTIVE_TERR_COUNT
1046 FROM jtf_terr_all jt
1047 WHERE
1048 exists ( select jtua.terr_id
1049 from jtf_terr_usgs_all jtua
1050 where jtua.source_id = p_param2 and jtua.terr_id = jt.terr_id)
1051 and NOT EXISTS ( SELECT jtdr.terr_id
1052 FROM jtf_terr_denorm_rules_all jtdr
1053 WHERE jtdr.terr_id = jt.terr_id );
1054
1055 cursor c_NONACTIVE_BY_COUNTRY IS
1056 SELECT
1057 hou.name name,
1058 COUNT(*) INACTIVE_TERR_COUNT,
1059 hou.organization_id org_id
1060 FROM jtf_terr_all jt, hr_organization_units hou
1061 WHERE jt.org_id = hou.organization_id
1062 and exists ( select jtua.terr_id
1063 from jtf_terr_usgs_all jtua
1064 where jtua.source_id = p_param2 and jtua.terr_id = jt.terr_id)
1065 AND NOT EXISTS ( SELECT jtdr.terr_id
1066 FROM jtf_terr_denorm_rules_all jtdr
1067 WHERE jtdr.terr_id = jt.terr_id )
1068 GROUP BY hou.name, hou.organization_id
1069 ORDER BY INACTIVE_TERR_COUNT DESC;
1070
1071 begin
1072
1073 lx_result_tbl := report_out_tbl_type();
1074 l_out_index := 0;
1075 --dbms_output.put_line('p_param1= ' || p_param1);
1076 --dbms_output.put_line('p_param2= ' || p_param2);
1077
1078 If p_param1 = 'ACTIVE_GLOBAL' then
1079 --dbms_output.put_line('ACTIVE_GLOBAL');
1080
1081 for actglobal_type in c_ACTIVE_GLOBAL
1082 loop
1083 l_out_index := l_out_index + 1;
1084 lx_result_tbl.extend;
1085 lx_result_tbl(l_out_index).column1 := actglobal_type.name;
1086 lx_result_tbl(l_out_index).column2 := actglobal_type.ACTIVE_TERR_COUNT;
1087 lx_result_tbl(l_out_index).column3 := actglobal_type.TERR_DUAL_ASSGN_COUNT;
1088 lx_result_tbl(l_out_index).column4 := actglobal_type.TERR_CREATED_COUNT;
1089 lx_result_tbl(l_out_index).column5 := actglobal_type.TERR_SOFT_DEL_COUNT;
1090 lx_result_tbl(l_out_index).column6 := actglobal_type.TERR_UPDATED_COUNT;
1091 lx_result_tbl(l_out_index).column7 := actglobal_type.ACTIVE_DIST_REP_COUNT;
1092 lx_result_tbl(l_out_index).column8 := actglobal_type.TERR_PER_REP;
1093 end loop;
1094
1095 elsif p_param1 = 'ACTIVE_BY_COUNTRY' then
1096 --dbms_output.put_line('ACTIVE_BY_COUNTRY');
1097
1098 for actcountry_type in c_ACTIVE_BY_COUNTRY
1099 loop
1100 l_out_index := l_out_index + 1;
1101 lx_result_tbl.extend;
1102 lx_result_tbl(l_out_index).column1 := actcountry_type.name;
1103 lx_result_tbl(l_out_index).column2 := actcountry_type.ACTIVE_TERR_COUNT;
1104 lx_result_tbl(l_out_index).column3 := actcountry_type.TERR_DUAL_ASSGN_COUNT;
1105 lx_result_tbl(l_out_index).column4 := actcountry_type.TERR_CREATED_COUNT;
1106 lx_result_tbl(l_out_index).column5 := actcountry_type.TERR_SOFT_DEL_COUNT;
1107 lx_result_tbl(l_out_index).column6 := actcountry_type.TERR_UPDATED_COUNT;
1108 lx_result_tbl(l_out_index).column7 := actcountry_type.ACTIVE_DIST_REP_COUNT;
1109 lx_result_tbl(l_out_index).column8 := actcountry_type.TERR_PER_REP;
1110 end loop;
1111
1112
1113 elsif p_param1 = 'NONACTIVE_GLOBAL' then
1114 --dbms_output.put_line('NONACTIVE_GLOBAL');
1115
1116 for nonactglobal_type in c_NONACTIVE_GLOBAL
1117 loop
1118 l_out_index := l_out_index + 1;
1119 lx_result_tbl.extend;
1120 lx_result_tbl(l_out_index).column1 := nonactglobal_type.name;
1121 lx_result_tbl(l_out_index).column2 := nonactglobal_type.INACTIVE_TERR_COUNT;
1122 end loop;
1123
1124 elsif p_param1 = 'NONACTIVE_BY_COUNTRY' then
1125 --dbms_output.put_line('NONACTIVE_BY_COUNTRY');
1126
1127 for nonactcountry_type in c_NONACTIVE_BY_COUNTRY
1128 loop
1129 l_out_index := l_out_index + 1;
1130 lx_result_tbl.extend;
1131 lx_result_tbl(l_out_index).column1 := nonactcountry_type.name;
1132 lx_result_tbl(l_out_index).column2 := nonactcountry_type.INACTIVE_TERR_COUNT;
1133 lx_result_tbl(l_out_index).column3 := nonactcountry_type.org_id;
1134 end loop;
1135 end if;
1136
1137 x_result_tbl := lx_result_tbl;
1138
1139 end INTEL_RPT;
1140
1141 ---------------------------------------------------------------
1142 -- SYSTEM_INFO_RPT
1143 -- Notes: Territory System Information Report
1144 --
1145 ---------------------------------------------------------------
1146
1147 PROCEDURE SYSTEM_INFO_RPT(p_param1 in varchar2,
1148 p_param2 in varchar2,
1149 p_param3 in varchar2,
1150 p_param4 in varchar2,
1151 p_param5 in varchar2,
1152 x_result_tbl OUT NOCOPY report_out_tbl_type)
1153 IS
1154 lx_result_tbl report_out_tbl_type := report_out_tbl_type();
1155 l_out_index number := 0;
1156
1157
1158 begin
1159 -- TERR_ADMINS
1160 lx_result_tbl.extend();
1161 l_out_index := l_out_index + 1;
1162 lx_result_tbl(l_out_index).column1 := 'TERR_ADMIN_COUNT';
1163 lx_result_tbl(l_out_index).column2 := 'FAKE_TERR_ADMIN_COUNT';
1164
1165 -- TERR_ADMIN_LOGINS
1166 lx_result_tbl.extend();
1167 l_out_index := l_out_index + 1;
1168 lx_result_tbl(l_out_index).column1 := 'TERR_ADMIN_LOGINS';
1169 lx_result_tbl(l_out_index).column2 := 'FAKE_TERR_ADMIN_LOGINS';
1170
1171 -- LOOKUP_USERS
1172 lx_result_tbl.extend();
1173 l_out_index := l_out_index + 1;
1174 lx_result_tbl(l_out_index).column1 := 'LOOKUP_USER_COUNT';
1175 lx_result_tbl(l_out_index).column2 := 'FAKE_LOOKUP_USER_COUNT';
1176
1177 -- LOOKUP_USER_LOGINS
1178 lx_result_tbl.extend();
1179 l_out_index := l_out_index + 1;
1180 lx_result_tbl(l_out_index).column1 := 'LOOKUP_USER_LOGINS';
1181 lx_result_tbl(l_out_index).column2 := 'FAKE_LOOKUP_USER_LOGINS';
1182
1183
1184 -- TIME
1185 lx_result_tbl.extend();
1186 l_out_index := l_out_index + 1;
1187 lx_result_tbl(l_out_index).column1 := 'TIME';
1188 lx_result_tbl(l_out_index).column2 := 'FAKE_TIME';
1189
1190 x_result_tbl := lx_result_tbl;
1191
1192 end SYSTEM_INFO_RPT;
1193
1194
1195
1196 ---------------------------------------------------------------
1197 -- REPORT_CONTROL
1198 -- Notes: Directs call to proper report generator.
1199 --
1200 --
1201 ---------------------------------------------------------------
1202 PROCEDURE REPORT_CONTROL (p_report in varchar2,
1203 p_param1 in varchar2,
1204 p_param2 in varchar2,
1205 p_param3 in varchar2,
1206 p_param4 in varchar2,
1207 p_param5 in varchar2,
1208 x_result_tbl OUT NOCOPY report_out_tbl_type)
1209 IS
1210
1211 begin
1212 --dbms_output.put_line('REPORT_CONTROL ');
1213 if p_report = 'DEFINITION' then
1214
1215 DEFINITION_RPT( p_param1 => p_param1, -- resource_id
1216 p_param2 => p_param2, -- qual_usg_id
1217 p_param3 => p_param3, -- source_id
1218 p_param4 => p_param4, -- active on
1219 p_param5 => p_param5, -- optional terr_id
1220 p_rpt_type => 'DEFINITION',
1221 x_result_tbl => x_result_tbl);
1222
1223 elsif p_report = 'CHANGES' then
1224 --dbms_output.put_line(' CHANGES');
1225 DEFINITION_RPT( p_param1 => p_param1, -- optional last_update_date >= this
1226 p_param2 => p_param2, -- last_update_date <= this
1227 p_param3 => p_param3, -- optional source_id
1228 p_param4 => p_param4, -- active on
1229 p_param5 => p_param5,
1230 p_rpt_type => 'CHANGES',
1231 x_result_tbl => x_result_tbl);
1232
1233 elsif p_report = 'INTEL' then
1234 --dbms_output.put_line('p_report = INTEL');
1235 INTEL_RPT( p_param1 => p_param1,
1236 p_param2 => p_param2,
1237 p_param3 => p_param3,
1238 p_param4 => p_param4,
1239 p_param5 => p_param5,
1240 x_result_tbl => x_result_tbl);
1241
1242 elsif p_report = 'LOOKUP_TERR' then
1243 --dbms_output.put_line('p_report = LOOKUP_TERR');
1244 DEFINITION_RPT( p_param1 => p_param1,
1245 p_param2 => p_param2,
1246 p_param3 => p_param3,
1247 p_param4 => p_param4,
1248 p_param5 => p_param5,
1249 p_rpt_type => 'LOOKUP_TERR',
1250 x_result_tbl => x_result_tbl);
1251
1252 elsif p_report = 'SYSTEM_INFO' then
1253 SYSTEM_INFO_RPT(p_param1 => p_param1,
1254 p_param2 => p_param2,
1255 p_param3 => p_param3,
1256 p_param4 => p_param4,
1257 p_param5 => p_param5,
1258 x_result_tbl => x_result_tbl);
1259
1260 end if; -- which report do we call?
1261 end REPORT_CONTROL;
1262
1263 end;