[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.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
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 , j1.LOW_VALUE_NUMBER
166 , j1.HIGH_VALUE_NUMBER
167 , j1.INTEREST_TYPE
168 , j1.PRIMARY_INTEREST_CODE
169 , j1.SECONDARY_INTEREST_CODE
170 , j1.CURRENCY_DESC
171 , j1.LOW_VALUE_CHAR_ID
172 , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_NAME) CNR_GROUP_NAME
173 , WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE1_DESC) VALUE1_DESC
174 , WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE2_DESC) VALUE2_DESC
175 , WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE3_DESC) VALUE3_DESC
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
185 /*
186 SELECT distinct j1.TERR_VALUE_ID
187 , j1.COMPARISON_OPERATOR
188 , WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR) LOW_VALUE_CHAR_DESC
189 , WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR) HIGH_VALUE_CHAR_DESC
190 , j1.LOW_VALUE_NUMBER
191 , j1.HIGH_VALUE_NUMBER
192 , j1.INTEREST_TYPE_ID INTEREST_TYPE
193 , j1.PRIMARY_INTEREST_CODE_ID PRIMARY_INTEREST_CODE
194 , j1.SECONDARY_INTEREST_CODE_ID SECONDARY_INTEREST_CODE
195 , j1.CURRENCY_CODE CURRENCY_DESC
196 , j1.LOW_VALUE_CHAR_ID
197 , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME
198 , WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE1_ID) VALUE1_DESC
199 , WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE2_ID) VALUE2_DESC
200 , WF_NOTIFICATION.SubstituteSpecialChars(j1.VALUE3_ID) VALUE3_DESC
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
210 ORDER BY j1.COMPARISON_OPERATOR;
211 */
212
213 CURSOR c_get_terr_values (ci_terr_qual_id NUMBER) IS
214 SELECT distinct j1.TERR_VALUE_ID
215 , j1.COMPARISON_OPERATOR
216 , WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR) LOW_VALUE_CHAR_DESC
217 , WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR) HIGH_VALUE_CHAR_DESC
218 , j1.LOW_VALUE_NUMBER
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
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
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
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
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(
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
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;
271
272 /* ARPATEL: 10/16, bug#2832442 */
273 l_resource_id VARCHAR2(2000);
274 l_resource_type VARCHAR2(2000);
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
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
293 /* arpatel 05/06/02 enh# 2109535 */
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
303 OPEN c_get_operating_unit(p_param5);
304 FETCH c_get_operating_unit INTO l_terr_operating_unit;
305 CLOSE c_get_operating_unit;
306
307 elsif p_rpt_type = 'DEFINITION' then
308 if ((p_param5 is null) or (p_param5 = '')) then
309
310 /* ARPATEL: 10/16, bug#2832442 */
311 --parse the resource_id to extract the resource_type
312 select SUBSTR(p_param1, 1,INSTR(p_param1, 'R')-1)
313 into l_resource_id
314 from dual;
315
316 select SUBSTR(p_param1, INSTR(p_param1, 'R'))
317 into l_resource_type
318 from dual;
319
320 /* ARPATEL: 10/16, END OF bug#2832442 */
321
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 )
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 */
338 and jtr.resource_type = decode(l_resource_type ,null, jtr.resource_type, l_resource_type)
339 and jtq.qual_usg_id = decode(p_param2 ,null, jtq.qual_usg_id, p_param2)
340 and jtu.source_id = decode(p_param3 ,null, jtu.source_id, p_param3)
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
350 START WITH jt.terr_id = j.terr_id ) ;
351 else
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
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
370 AND trunc(j.last_update_date) >= p_param1
371 AND trunc(j.last_update_date) <= NVL(p_param2, sysdate)
372 AND ( TRUNC(j.end_date_active) >= NVL(p_param4, lp_sysdate)
373 AND
374 TRUNC(j.start_date_active) <= NVL(p_param4, lp_sysdate)
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
384 START WITH jt.terr_id = j.terr_id ) ;
385 end if;
386
387
388 -- list from terr_name_cur
389 loop
390 fetch terr_name_cur into rec_terr;
391 exit when terr_name_cur%notfound;
392 --dbms_output.put_line('territory cursor rec_terr.terr_id: ' || rec_terr.terr_id);
393 terr_rspan_row_number := 0;
394 rsc_access_rspan_row_number := 0;
395 tqual_rspan_row_number:= 0;
396 qval_rspan_row_number := 0;
397
398 lx_result_tbl.extend;
399 l_out_index := l_out_index + 1;
400 lx_result_tbl(l_out_index):= l_out_rec;
401
402 lx_result_tbl(l_out_index).column1 := rec_terr.terr_id;
403 lx_result_tbl(l_out_index).column2 := rec_terr.name;
404 lx_result_tbl(l_out_index).column3 := rec_terr.rank;
405 lx_result_tbl(l_out_index).column4 := rec_terr.description;
406 lx_result_tbl(l_out_index).column12 := rec_terr.start_date_active;
407 lx_result_tbl(l_out_index).column13 := rec_terr.end_date_active;
408 lx_result_tbl(l_out_index).column14 := rec_terr.last_update_date;
409
410 lx_result_tbl(l_out_index).column15 := l_out_index;
411 lx_result_tbl(l_out_index).column19 := 'TERR_PTY';
412
413 lx_result_tbl(l_out_index).column18 := l_terr_operating_unit; -- ADDED FOR BUG 7315889
414
415 terr_rspan_row_number := l_out_index;
416
417
418 -- list from c_get_qual_types
419 qual_type_count := 0;
420
421 IF p_rpt_type <> 'LOOKUP_TERR' THEN
422 for rec_qual_type in c_get_qual_types(rec_terr.terr_id) loop
423 --dbms_output.put_line(' qual types cursor: rec_qual_type.qual_type_usg_id= '|| rec_qual_type.qual_type_usg_id);
424 -- for HTML column formatting
425 qual_type_count := qual_type_count + 1;
426
427 lx_result_tbl.extend;
428 l_out_index := l_out_index + 1;
429 lx_result_tbl(l_out_index):= l_out_rec;
430
431 lx_result_tbl(l_out_index).column1 := rec_terr.terr_id;
432 lx_result_tbl(l_out_index).column2 := rec_terr.name;
433 lx_result_tbl(l_out_index).column3 := rec_terr.rank;
434 lx_result_tbl(l_out_index).column12 := rec_terr.start_date_active;
435 lx_result_tbl(l_out_index).column13 := rec_terr.end_date_active;
436 lx_result_tbl(l_out_index).column14 := rec_terr.last_update_date;
437
438 lx_result_tbl(l_out_index).column4 := rec_qual_type.qual_type_usg_id;
439 lx_result_tbl(l_out_index).column5 := rec_qual_type.qualifier_type_description;
440 lx_result_tbl(l_out_index).column6 := rec_qual_type.qualifier_type_name;
441
442 lx_result_tbl(l_out_index).column15 := l_out_index;
443 lx_result_tbl(l_out_index).column19 := 'QUAL_TYPE';
444
445 end loop;
446 END IF; --p_rpt_type <> 'LOOKUP_TERR'
447
448 -- loop through c_get_terr_qual
449 terr_qual_count := 0;
450
451 for rec_terr_qual in c_get_terr_qual(rec_terr.terr_id) loop
452 --dbms_output.put_line(' tx qual cursor: rec_terr_qual.terr_qual_id =' || rec_terr_qual.TERR_QUAL_ID);
453 qual_value_count := 0;
454
455 for rec_qual_value in c_get_terr_values(rec_terr_qual.TERR_QUAL_ID)loop
456 --dbms_output.put_line(' qual value cursor rec_qual_value.TERR_VALUE_ID = ' || rec_qual_value.TERR_VALUE_ID);
457 l_match_qual := 0;
458 qual_value_count := qual_value_count + 1;
459
460 lx_result_tbl.extend;
461 l_out_index := l_out_index + 1;
462 lx_result_tbl(l_out_index):= l_out_rec;
463
464 if qual_value_count = 1 then
465 qval_rspan_row_number := l_out_index;
466 end if;
467
468 lx_result_tbl(l_out_index).column1 := rec_terr.terr_id;
469 lx_result_tbl(l_out_index).column2 := rec_terr.name;
470 lx_result_tbl(l_out_index).column3 := rec_terr.rank;
471
472 lx_result_tbl(l_out_index).column4 := rec_terr_qual.qual_usg_id;
473 lx_result_tbl(l_out_index).column5 := rec_terr_qual.terr_qual_id;
474 lx_result_tbl(l_out_index).column6 := rec_terr_qual.qualifier_name;
475
476 lx_result_tbl(l_out_index).column7 := rec_qual_value.TERR_VALUE_ID;
477 lx_result_tbl(l_out_index).column8 := rec_qual_value.COMPARISON_OPERATOR;
478 lx_result_tbl(l_out_index).column15 := l_out_index;
479 lx_result_tbl(l_out_index).column20 := qval_rspan_row_number;
480 lx_result_tbl(l_out_index).column16 := 0;
481 lx_result_tbl(l_out_index).column17 := 0;
482 lx_result_tbl(l_out_index).column19 := 'QUAL_VAL';
483
484 if qual_value_count = 1 then
485 lx_result_tbl(l_out_index).column17 := terr_qual_count;
486 else
487 lx_result_tbl(l_out_index).column17 := 0;
488 end if;
489
490 --lx_result_tbl(l_out_index).column18 := qual_value_count;
491 --------------------------------------
492 -- Deal with all the display types
493 --------------------------------------
494
495 if rec_qual_value.display_type = 'CHAR' then
496 if rec_qual_value.CONVERT_TO_ID_FLAG = 'Y' then
497 lx_result_tbl(l_out_index).column9 := rec_qual_value.low_value_char_desc;
498 lx_result_tbl(l_out_index).column10 := rec_qual_value.high_value_char_desc;
499 else
500 lx_result_tbl(l_out_index).column9 := rec_qual_value.low_value_char_desc;
501 lx_result_tbl(l_out_index).column10 := rec_qual_value.high_value_char_desc;
502 end if;
503 elsif rec_qual_value.display_type = 'INTEREST_TYPE' then
504 lx_result_tbl(l_out_index).column9 := rec_qual_value.interest_type;
505 lx_result_tbl(l_out_index).column10 := rec_qual_value.primary_interest_code;
506 lx_result_tbl(l_out_index).column11 := rec_qual_value.secondary_interest_code;
507 elsif rec_qual_value.display_type = 'DATE' then
508 lx_result_tbl(l_out_index).column9 := rec_qual_value.low_value_date;
509 lx_result_tbl(l_out_index).column10 := rec_qual_value.high_value_date;
510 elsif rec_qual_value.display_type = 'NUMBER' then
511 lx_result_tbl(l_out_index).column9 := rec_qual_value.low_value_number;
512 lx_result_tbl(l_out_index).column10 := rec_qual_value.high_value_number;
513 -- Fix for bug 6964643. The display type is NUMERIC for the qualifier NO OF EMLOYEES.
514 elsif rec_qual_value.display_type = 'NUMERIC' then
515 lx_result_tbl(l_out_index).column9 := rec_qual_value.low_value_number;
516 lx_result_tbl(l_out_index).column10 := rec_qual_value.high_value_number;
517 elsif rec_qual_value.display_type = 'CURRENCY' then
518 lx_result_tbl(l_out_index).column9 := rec_qual_value.low_value_number;
519 lx_result_tbl(l_out_index).column10 := rec_qual_value.high_value_number;
520 lx_result_tbl(l_out_index).column11 := rec_qual_value.currency_desc;
521 elsif rec_qual_value.display_type = 'CHAR_2IDS' then
522 lx_result_tbl(l_out_index).column9 := rec_qual_value.value1_desc;
523 lx_result_tbl(l_out_index).column10 := rec_qual_value.value2_desc;
524 --elsif rec_qual_value.display_type = 'COMPETENCE' then
525
526 elsif rec_qual_value.display_type = 'DEP_2FIELDS' then
527 lx_result_tbl(l_out_index).column9 := rec_qual_value.value1_desc;
528 lx_result_tbl(l_out_index).column10 := rec_qual_value.value2_desc;
529
530 elsif rec_qual_value.display_type = 'DEP_2FIELDS_CHAR_2IDS' then
531 lx_result_tbl(l_out_index).column9 := rec_qual_value.value1_desc;
532 lx_result_tbl(l_out_index).column10 := rec_qual_value.value2_desc;
533
534 else
535 lx_result_tbl(l_out_index).column9 := rec_qual_value.low_value_char_desc;
536 lx_result_tbl(l_out_index).column10 := rec_qual_value.high_value_char_desc;
537 end if;
538 -- increment qual_value_count
539 --qual_value_count := qual_value_count + 1;
540
541 --LOOKUP_TERR processing for CNR
542 if p_rpt_type = 'LOOKUP_TERR' and rec_terr_qual.qual_usg_id = -1012 --CNR
543 and rec_qual_value.COMPARISON_OPERATOR IN ('BETWEEN','=','LIKE')
544 then
545 if rec_qual_value.COMPARISON_OPERATOR = 'BETWEEN'
546 then
547 begin
548 select 1 into l_match_qual from dual
549 where UPPER(p_param3) BETWEEN lx_result_tbl(l_out_index).column9 and lx_result_tbl(l_out_index).column10;
550
551 exception
552 when no_data_found then l_match_qual := 0;
553 end;
554
555 end if;
556
557 if rec_qual_value.COMPARISON_OPERATOR = '='
558 then
559 begin
560 select 1 into l_match_qual from dual
561 where UPPER(p_param3) = lx_result_tbl(l_out_index).column9;
562
563 exception
564 when no_data_found then l_match_qual := 0;
565 end;
566
567 end if;
568
569 if rec_qual_value.COMPARISON_OPERATOR = 'LIKE'
570 then
571 begin
572 select 1 into l_match_qual from dual
573 where UPPER(p_param3) LIKE lx_result_tbl(l_out_index).column9;
574
575 exception
576 when no_data_found then l_match_qual := 0;
577 end;
578
579 end if;
580
581 if l_match_qual = 0
582 then
583 if qual_value_count = 1 then
584 qval_rspan_row_number := 0;
585 end if;
586 l_out_index := l_out_index - 1;
587 qual_value_count := qual_value_count - 1;
588 lx_result_tbl.trim;
589 end if;
590
591 end if;--LOOKUP_TERR processing for CNR
592
593 --LOOKUP_TERR processing for Postal Code
594 if p_rpt_type = 'LOOKUP_TERR' and rec_terr_qual.qual_usg_id = -1007 --Postal Code
595 and rec_qual_value.COMPARISON_OPERATOR IN ('BETWEEN','=','LIKE')
596 then
597
598 if rec_qual_value.COMPARISON_OPERATOR = 'BETWEEN'
599 then
600 begin
601 select 1 into l_match_qual from dual
602 where UPPER(p_param4) BETWEEN lx_result_tbl(l_out_index).column9 and lx_result_tbl(l_out_index).column10;
603
604 exception
605 when no_data_found then l_match_qual := 0;
606 end;
607
608 end if;
609
610 if rec_qual_value.COMPARISON_OPERATOR = '='
611 then
612 begin
613 select 1 into l_match_qual from dual
614 where UPPER(p_param4) = lx_result_tbl(l_out_index).column9;
615
616 exception
617 when no_data_found then l_match_qual := 0;
618 end;
619
620 end if;
621
622 if rec_qual_value.COMPARISON_OPERATOR = 'LIKE'
623 then
624 begin
625 select 1 into l_match_qual from dual
626 where UPPER(p_param4) LIKE lx_result_tbl(l_out_index).column9;
627
628 exception
629 when no_data_found then l_match_qual := 0;
630 end;
631
632 end if;
633
634 if l_match_qual = 0
635 then
636 if qual_value_count = 1 then
637 qval_rspan_row_number := 0;
638 end if;
639 l_out_index := l_out_index - 1;
640 qual_value_count := qual_value_count - 1;
641 lx_result_tbl.trim;
642 end if;
643
644 end if;--LOOKUP_TERR processing for Postal Code
645
646 end loop; -- all values
647 if qval_rspan_row_number <> 0 then
648 lx_result_tbl(qval_rspan_row_number).column17 := qual_value_count;
649 end if;
650 end loop; -- all qualifier
651
652 -- loop through c_get_terr_rsc_qual
653 terr_rsc_qual_count := 0;
654
655 IF p_rpt_type <> 'LOOKUP_TERR' THEN
656 for rec_terr_rsc_qual in c_get_terr_rsc_qual(rec_terr.terr_id) loop
657 --dbms_output.put_line(' resource qual cursor rec_terr_rsc_qual.terr_qual_id = ' || rec_terr_rsc_qual.terr_qual_id);
658 rsc_qual_value_count := 0;
659
660 for rec_rsc_qual_value in c_get_terr_values(rec_terr_rsc_qual.TERR_qual_ID)loop
661
662 rsc_qual_value_count := rsc_qual_value_count + 1;
663
664 lx_result_tbl.extend;
665 l_out_index := l_out_index + 1;
666 lx_result_tbl(l_out_index):= l_out_rec;
667
668 if rsc_qual_value_count = 1 then
669 qval_rspan_row_number := l_out_index;
670 end if;
671
672 lx_result_tbl(l_out_index).column1 := rec_terr.terr_id;
673 lx_result_tbl(l_out_index).column2 := rec_terr.name;
674 lx_result_tbl(l_out_index).column3 := rec_terr.rank;
675
676 lx_result_tbl(l_out_index).column4 := rec_terr_rsc_qual.qual_usg_id;
677 lx_result_tbl(l_out_index).column5 := rec_terr_rsc_qual.terr_qual_id;
678 lx_result_tbl(l_out_index).column6 := rec_terr_rsc_qual.qualifier_name;
679
680 lx_result_tbl(l_out_index).column7 := rec_rsc_qual_value.TERR_VALUE_ID;
681 lx_result_tbl(l_out_index).column8 := rec_rsc_qual_value.COMPARISON_OPERATOR;
682 lx_result_tbl(l_out_index).column15 := l_out_index;
683 lx_result_tbl(l_out_index).column20 := qval_rspan_row_number;
684 lx_result_tbl(l_out_index).column16 := 0;
685 lx_result_tbl(l_out_index).column17 := 0;
686 lx_result_tbl(l_out_index).column19 := 'RSC_QUAL_VAL';
687
688 if rsc_qual_value_count = 1 then
689 lx_result_tbl(l_out_index).column17 := terr_rsc_qual_count;
690 else
691 lx_result_tbl(l_out_index).column17 := 0;
692 end if;
693
694 --lx_result_tbl(l_out_index).column18 := rsc_qual_value_count;
695
696 end loop; -- all values
697 if qval_rspan_row_number <> 0 then
698 lx_result_tbl(qval_rspan_row_number).column17 := rsc_qual_value_count;
699 end if;
700 end loop; -- all rsc rsc_qualifier
701 END IF; -- p_rpt_type <> 'LOOKUP_TERR'
702
703
704
705
706 -- list from c_get_resource(current_terr_id)
707 resource_count := 0;
708
709 IF p_rpt_type <> 'LOOKUP_TERR' THEN
710 for rec_res in c_get_resource(rec_terr.terr_id) loop
711 --dbms_output.put_line(' resource cursor: rec_res.resource_id= ' || rec_res.resource_id);
712 lx_result_tbl.extend;
713 l_out_index := l_out_index + 1;
714 lx_result_tbl(l_out_index):= l_out_rec;
715
716 lx_result_tbl(l_out_index).column1 := rec_terr.terr_id;
717 lx_result_tbl(l_out_index).column2 := rec_terr.name;
718 lx_result_tbl(l_out_index).column3 := rec_terr.rank;
719 lx_result_tbl(l_out_index).column12 := rec_terr.start_date_active;
720 lx_result_tbl(l_out_index).column13 := rec_terr.end_date_active;
721 lx_result_tbl(l_out_index).column14 := rec_terr.last_update_date;
722
723 lx_result_tbl(l_out_index).column4 := rec_res.resource_id;
724 lx_result_tbl(l_out_index).column5 := rec_res.resource_name;
725 lx_result_tbl(l_out_index).column6 := rec_res.terr_rsc_id; --resource_type;
726
727 lx_result_tbl(l_out_index).column15 := l_out_index;
728 lx_result_tbl(l_out_index).column19 := 'RESOURCE';
729 rsc_access_rspan_row_number := l_out_index;
730
731 -- list from c_get_rsc_access
732 rsc_access_count := 0;
733
734 for rec_rsc_access in c_get_rsc_access(rec_res.terr_rsc_id) loop
735 --dbms_output.put_line('rsc access cursor ');
736 -- for HTML column formatting
737 rsc_access_count := rsc_access_count + 1;
738
739 lx_result_tbl.extend;
740 l_out_index := l_out_index + 1;
741 lx_result_tbl(l_out_index):= l_out_rec;
742
743 --dbms_output.put_line('rec_terr.terr_id = ' || rec_terr.terr_id);
744
745 lx_result_tbl(l_out_index).column1 := rec_terr.terr_id;
746 lx_result_tbl(l_out_index).column2 := rec_terr.name;
747 lx_result_tbl(l_out_index).column3 := rec_terr.rank;
748 lx_result_tbl(l_out_index).column12 := rec_terr.start_date_active;
749 lx_result_tbl(l_out_index).column13 := rec_terr.end_date_active;
750 lx_result_tbl(l_out_index).column14 := rec_terr.last_update_date;
751
752 lx_result_tbl(l_out_index).column4 := rec_res.resource_id;
753 lx_result_tbl(l_out_index).column5 := rec_res.resource_name;
754 lx_result_tbl(l_out_index).column6 := rec_res.terr_rsc_id; --resource_type;
755
756 lx_result_tbl(l_out_index).column7 := rec_rsc_access.terr_rsc_access_id;
757 lx_result_tbl(l_out_index).column8 := rec_rsc_access.meaning;
758
759 lx_result_tbl(l_out_index).column15 := l_out_index;
760 lx_result_tbl(l_out_index).column19 := 'RSC_ACCESS';
761 lx_result_tbl(l_out_index).column16 := 0;
762 lx_result_tbl(l_out_index).column17 := 1;
763
764
765 end loop; -- resource accesses
766 -- --dbms_output.put_line('setting rsc_access at ' || rsc_access_rspan_row_number || 'rowspan: ' || rsc_access_count);
767 if rsc_access_rspan_row_number <> 0 then
768 lx_result_tbl(rsc_access_rspan_row_number).column17 := rsc_access_count;
769 end if;
770 end loop; -- resources
771 END IF; -- p_rpt_type <> 'LOOKUP_TERR'
772
773 -- this used to be row span count for resource but now we use in conjunction at bottom
774 --lx_result_tbl(terr_rspan_row_number).column16 := resource_count;
775
776 -----------------------------
777
778
779 if terr_rspan_row_number <> 0 then
780 lx_result_tbl(terr_rspan_row_number).column16 := l_out_index - terr_rspan_row_number + 1;
781 else
782 null;
783 --dbms_output.put_line('rowspan for row ' || terr_rspan_row_number || ': ' ||(l_out_index - terr_rspan_row_number + 1) );
784 end if;
785 end loop; -- all territories
786 x_result_tbl := lx_result_tbl;
787 --dbms_output.put_line('lx_result_tbl.last: ' || lx_result_tbl.last);
788 -- exception
789 -- when others then
790 -- return;
791
792
793 end DEFINITION_RPT;
794
795
796 ---------------------------------------------------------------
797 -- CHANGES_RPT
798 -- Notes: Territory Changes Report
799 -- NOT USED SINCE WE CALL DEFINITIONS REPORT
800 --
801 ---------------------------------------------------------------
802
803 PROCEDURE CHANGES_RPT (p_param1 in varchar2,
804 p_param2 in varchar2,
805 p_param3 in varchar2,
806 p_param4 in varchar2,
807 p_param5 in varchar2,
808 x_result_tbl OUT NOCOPY report_out_tbl_type)
809 IS
810
811 begin
812 null;
813 end CHANGES_RPT;
814
815 ---------------------------------------------------------------
816 -- INTEL_RPT
817 -- Notes: Territory Changes Report
818 -- NOT CURRENTLY USED AS SQL CALLS MADE FROM JSP
819 -- arpatel 11/13 Adding sql calls to INTEL_RPT
820 ---------------------------------------------------------------
821
822 PROCEDURE INTEL_RPT ( p_param1 in varchar2,
823 p_param2 in varchar2,
824 p_param3 in varchar2,
825 p_param4 in varchar2,
826 p_param5 in varchar2,
827 x_result_tbl OUT NOCOPY report_out_tbl_type)
828 IS
829 lx_result_tbl report_out_tbl_type; -- := report_out_tbl_type();
830 l_out_index number := 0;
831
832 --ACTIVE GLOBAL CURSOR
833 cursor c_ACTIVE_GLOBAL IS
834 SELECT 'All' name,
835 atc.ACTIVE_TERR_COUNT ACTIVE_TERR_COUNT,
836 tdac.TERR_DUAL_ASSGN_COUNT TERR_DUAL_ASSGN_COUNT,
837 tcc.TERR_CREATED_COUNT TERR_CREATED_COUNT,
838 tsdc.TERR_SOFT_DEL_COUNT TERR_SOFT_DEL_COUNT,
839 tuc.TERR_UPDATED_COUNT TERR_UPDATED_COUNT,
840 arc.ACTIVE_DIST_REP_COUNT ACTIVE_DIST_REP_COUNT,
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,
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
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
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
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
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
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
903 --ACTIVE COUNTRY CURSOR
904 cursor c_ACTIVE_BY_COUNTRY IS
905 SELECT
906 houo.name name,
907 NVL(atc.ACTIVE_TERR_COUNT, 0) ACTIVE_TERR_COUNT,
908 NVL(tdac.TERR_DUAL_ASSGN_COUNT, 0) TERR_DUAL_ASSGN_COUNT,
909 NVL(tcc.TERR_CREATED_COUNT, 0) TERR_CREATED_COUNT,
910 NVL(tsdc.TERR_SOFT_DEL_COUNT, 0) TERR_SOFT_DEL_COUNT,
911 NVL(tuc.TERR_UPDATED_COUNT, 0) TERR_UPDATED_COUNT,
912 NVL(arc.ACTIVE_DIST_REP_COUNT, 0) ACTIVE_DIST_REP_COUNT,
913 DECODE( arc.ACTIVE_DIST_REP_COUNT
914 , NULL, 'No Active Reps'
915 , ROUND((atc.ACTIVE_TERR_COUNT / arc.ACTIVE_DIST_REP_COUNT), 2)
916 ) TERR_PER_REP
917
918 FROM
919 hr_organization_units houo,
920 -- Total # of Active Territories -- 13918
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)
930 GROUP BY hou.name, hou.organization_id
931 ) atc,
932
933 -- Total # of Territories that have Internal and External Reps
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)
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
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,
953
954 -- Territories created last 7 days
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 )
964 AND jt.creation_date BETWEEN SYSDATE-7 AND SYSDATE+1
965 GROUP BY hou.name, hou.organization_id
966 ) tcc,
967
968 -- Total # of (SOFT) DELETED Territories
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 )
978 AND jt.END_DATE_ACTIVE BETWEEN SYSDATE-7 AND SYSDATE+1
979 GROUP BY hou.name, hou.organization_id
980 ) tsdc,
981
982 -- Total # of UPDATED Territories
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 )
992 AND jt.LAST_UPDATE_DATE BETWEEN SYSDATE-7 AND SYSDATE+1
993 GROUP BY hou.name, hou.organization_id
994 ) tuc,
995
996 -- Total Distinct # of People Assigned to Territories -- 2930
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'
1006 AND jtdr.terr_id = jtr.terr_id and jtdr.source_id = p_param2)
1007
1008 GROUP BY hou.name, hou.organization_id
1009 ) arc
1010 WHERE
1011 houo.organization_id = atc.organization_id
1012 AND houo.organization_id = tdac.organization_id(+)
1013 AND houo.organization_id = tcc.organization_id(+)
1014 AND houo.organization_id = tsdc.organization_id(+)
1015 AND houo.organization_id = tuc.organization_id(+)
1016 AND houo.organization_id = arc.organization_id(+)
1017 ORDER BY atc.ACTIVE_TERR_COUNT DESC;
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)
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
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)
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
1047 begin
1048
1049 lx_result_tbl := report_out_tbl_type();
1050 l_out_index := 0;
1051 --dbms_output.put_line('p_param1= ' || p_param1);
1052 --dbms_output.put_line('p_param2= ' || p_param2);
1053
1054 If p_param1 = 'ACTIVE_GLOBAL' then
1055 --dbms_output.put_line('ACTIVE_GLOBAL');
1056
1057 for actglobal_type in c_ACTIVE_GLOBAL
1058 loop
1059 l_out_index := l_out_index + 1;
1060 lx_result_tbl.extend;
1061 lx_result_tbl(l_out_index).column1 := actglobal_type.name;
1062 lx_result_tbl(l_out_index).column2 := actglobal_type.ACTIVE_TERR_COUNT;
1063 lx_result_tbl(l_out_index).column3 := actglobal_type.TERR_DUAL_ASSGN_COUNT;
1064 lx_result_tbl(l_out_index).column4 := actglobal_type.TERR_CREATED_COUNT;
1065 lx_result_tbl(l_out_index).column5 := actglobal_type.TERR_SOFT_DEL_COUNT;
1066 lx_result_tbl(l_out_index).column6 := actglobal_type.TERR_UPDATED_COUNT;
1067 lx_result_tbl(l_out_index).column7 := actglobal_type.ACTIVE_DIST_REP_COUNT;
1068 lx_result_tbl(l_out_index).column8 := actglobal_type.TERR_PER_REP;
1069 end loop;
1070
1071 elsif p_param1 = 'ACTIVE_BY_COUNTRY' then
1072 --dbms_output.put_line('ACTIVE_BY_COUNTRY');
1073
1074 for actcountry_type in c_ACTIVE_BY_COUNTRY
1075 loop
1076 l_out_index := l_out_index + 1;
1077 lx_result_tbl.extend;
1078 lx_result_tbl(l_out_index).column1 := actcountry_type.name;
1079 lx_result_tbl(l_out_index).column2 := actcountry_type.ACTIVE_TERR_COUNT;
1080 lx_result_tbl(l_out_index).column3 := actcountry_type.TERR_DUAL_ASSGN_COUNT;
1081 lx_result_tbl(l_out_index).column4 := actcountry_type.TERR_CREATED_COUNT;
1082 lx_result_tbl(l_out_index).column5 := actcountry_type.TERR_SOFT_DEL_COUNT;
1083 lx_result_tbl(l_out_index).column6 := actcountry_type.TERR_UPDATED_COUNT;
1084 lx_result_tbl(l_out_index).column7 := actcountry_type.ACTIVE_DIST_REP_COUNT;
1085 lx_result_tbl(l_out_index).column8 := actcountry_type.TERR_PER_REP;
1086 end loop;
1087
1088
1089 elsif p_param1 = 'NONACTIVE_GLOBAL' then
1090 --dbms_output.put_line('NONACTIVE_GLOBAL');
1091
1092 for nonactglobal_type in c_NONACTIVE_GLOBAL
1093 loop
1094 l_out_index := l_out_index + 1;
1095 lx_result_tbl.extend;
1096 lx_result_tbl(l_out_index).column1 := nonactglobal_type.name;
1097 lx_result_tbl(l_out_index).column2 := nonactglobal_type.INACTIVE_TERR_COUNT;
1098 end loop;
1099
1100 elsif p_param1 = 'NONACTIVE_BY_COUNTRY' then
1101 --dbms_output.put_line('NONACTIVE_BY_COUNTRY');
1102
1103 for nonactcountry_type in c_NONACTIVE_BY_COUNTRY
1104 loop
1105 l_out_index := l_out_index + 1;
1106 lx_result_tbl.extend;
1107 lx_result_tbl(l_out_index).column1 := nonactcountry_type.name;
1108 lx_result_tbl(l_out_index).column2 := nonactcountry_type.INACTIVE_TERR_COUNT;
1109 lx_result_tbl(l_out_index).column3 := nonactcountry_type.org_id;
1110 end loop;
1111 end if;
1112
1113 x_result_tbl := lx_result_tbl;
1114
1115 end INTEL_RPT;
1116
1117 ---------------------------------------------------------------
1118 -- SYSTEM_INFO_RPT
1119 -- Notes: Territory System Information Report
1120 --
1121 ---------------------------------------------------------------
1122
1123 PROCEDURE SYSTEM_INFO_RPT(p_param1 in varchar2,
1124 p_param2 in varchar2,
1125 p_param3 in varchar2,
1126 p_param4 in varchar2,
1127 p_param5 in varchar2,
1128 x_result_tbl OUT NOCOPY report_out_tbl_type)
1129 IS
1130 lx_result_tbl report_out_tbl_type := report_out_tbl_type();
1131 l_out_index number := 0;
1132
1133
1134 begin
1135 -- TERR_ADMINS
1136 lx_result_tbl.extend();
1137 l_out_index := l_out_index + 1;
1138 lx_result_tbl(l_out_index).column1 := 'TERR_ADMIN_COUNT';
1139 lx_result_tbl(l_out_index).column2 := 'FAKE_TERR_ADMIN_COUNT';
1140
1141 -- TERR_ADMIN_LOGINS
1142 lx_result_tbl.extend();
1143 l_out_index := l_out_index + 1;
1144 lx_result_tbl(l_out_index).column1 := 'TERR_ADMIN_LOGINS';
1145 lx_result_tbl(l_out_index).column2 := 'FAKE_TERR_ADMIN_LOGINS';
1146
1147 -- LOOKUP_USERS
1148 lx_result_tbl.extend();
1149 l_out_index := l_out_index + 1;
1150 lx_result_tbl(l_out_index).column1 := 'LOOKUP_USER_COUNT';
1151 lx_result_tbl(l_out_index).column2 := 'FAKE_LOOKUP_USER_COUNT';
1152
1153 -- LOOKUP_USER_LOGINS
1154 lx_result_tbl.extend();
1155 l_out_index := l_out_index + 1;
1156 lx_result_tbl(l_out_index).column1 := 'LOOKUP_USER_LOGINS';
1157 lx_result_tbl(l_out_index).column2 := 'FAKE_LOOKUP_USER_LOGINS';
1158
1159
1160 -- TIME
1161 lx_result_tbl.extend();
1162 l_out_index := l_out_index + 1;
1163 lx_result_tbl(l_out_index).column1 := 'TIME';
1164 lx_result_tbl(l_out_index).column2 := 'FAKE_TIME';
1165
1166 x_result_tbl := lx_result_tbl;
1167
1168 end SYSTEM_INFO_RPT;
1169
1170
1171
1172 ---------------------------------------------------------------
1173 -- REPORT_CONTROL
1174 -- Notes: Directs call to proper report generator.
1175 --
1176 --
1177 ---------------------------------------------------------------
1178 PROCEDURE REPORT_CONTROL (p_report in varchar2,
1179 p_param1 in varchar2,
1180 p_param2 in varchar2,
1181 p_param3 in varchar2,
1182 p_param4 in varchar2,
1183 p_param5 in varchar2,
1184 x_result_tbl OUT NOCOPY report_out_tbl_type)
1185 IS
1186
1187 begin
1188 --dbms_output.put_line('REPORT_CONTROL ');
1189 if p_report = 'DEFINITION' then
1190
1191 DEFINITION_RPT( p_param1 => p_param1, -- resource_id
1192 p_param2 => p_param2, -- qual_usg_id
1193 p_param3 => p_param3, -- source_id
1194 p_param4 => p_param4, -- active on
1195 p_param5 => p_param5, -- optional terr_id
1196 p_rpt_type => 'DEFINITION',
1197 x_result_tbl => x_result_tbl);
1198
1199 elsif p_report = 'CHANGES' then
1200 --dbms_output.put_line(' CHANGES');
1201 DEFINITION_RPT( p_param1 => p_param1, -- optional last_update_date >= this
1202 p_param2 => p_param2, -- last_update_date <= this
1203 p_param3 => p_param3, -- optional source_id
1204 p_param4 => p_param4, -- active on
1205 p_param5 => p_param5,
1206 p_rpt_type => 'CHANGES',
1207 x_result_tbl => x_result_tbl);
1208
1209 elsif p_report = 'INTEL' then
1210 --dbms_output.put_line('p_report = INTEL');
1211 INTEL_RPT( p_param1 => p_param1,
1212 p_param2 => p_param2,
1213 p_param3 => p_param3,
1214 p_param4 => p_param4,
1215 p_param5 => p_param5,
1216 x_result_tbl => x_result_tbl);
1217
1218 elsif p_report = 'LOOKUP_TERR' then
1219 --dbms_output.put_line('p_report = LOOKUP_TERR');
1220 DEFINITION_RPT( p_param1 => p_param1,
1221 p_param2 => p_param2,
1222 p_param3 => p_param3,
1223 p_param4 => p_param4,
1224 p_param5 => p_param5,
1225 p_rpt_type => 'LOOKUP_TERR',
1226 x_result_tbl => x_result_tbl);
1227
1228 elsif p_report = 'SYSTEM_INFO' then
1229 SYSTEM_INFO_RPT(p_param1 => p_param1,
1230 p_param2 => p_param2,
1231 p_param3 => p_param3,
1232 p_param4 => p_param4,
1233 p_param5 => p_param5,
1234 x_result_tbl => x_result_tbl);
1235
1236 end if; -- which report do we call?
1237 end REPORT_CONTROL;
1238
1239 end;