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

Source


4  |  Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA|
1 PACKAGE BODY JTF_TERR_DIAGNOSTIC_TEST AS
2 /* $Header: jtftrdtb.pls 120.1.12000000.2 2007/12/17 14:54:09 vpalle ship $ */
3 /*=======================================================================+
5  |                            All rights reserved.                       |
6  +=======================================================================+
7  | FILENAME                                                              |
8  |   jtavstgb.pls                                                        |
9  |                                                                       |
10  | DESCRIPTION                                                           |
11  |   - This package is for general territory testing                     |
12  |                                                                       |
13  | NOTES                                                                 |
14  |                                                                       |
15  | Date          Developer        Change                                 |
16  | ------        ---------------  -------------------------------------- |
17  | 26-Sept-2002   arpatel          Created.                              |
18  +======================================================================*/
19 
20   ------------------------------------------------------------
21   -- procedure to initialize test datastructures
22   -- executed prior to test run - leave body as null otherwize
23   ------------------------------------------------------------
24   PROCEDURE init IS
25   BEGIN
26    -- test writer could insert special setup code here
27    null;
28   END init;
29 
30  ------------------------------------------------------------
31  -- procedure to cleanup any  test datastructures that were setup in the init
32 -- procedure call executes after test run - leave body as null otherwize
33 ------------------------------------------------------------
34   PROCEDURE cleanup IS
35   BEGIN
36    -- test writer could insert special cleanup code here
37    NULL;
38   END cleanup;
39 
40   ------------------------------------------------------------
41 -- procedure to execute the PLSQL test
42 -- the inputs needed for the test are passed in and a report object and CLOB are
43 -- returned.
44 ------------------------------------------------------------
45  PROCEDURE runtest(inputs IN  JTF_DIAG_INPUTTBL,
46                     report OUT NOCOPY JTF_DIAG_REPORT,
47                     reportClob OUT NOCOPY CLOB) IS
48 
49      --sql stmt vars
50      l_active_sales_terrs VARCHAR2(360);
51      l_terr_res_assign    VARCHAR2(360);
52      l_active_reps        VARCHAR2(360);
53      l_num_values         VARCHAR2(360);
54      l_parties            VARCHAR2(360);
55      l_party_sites        VARCHAR2(360);
56      l_locations          VARCHAR2(360);
57      l_lookup_code        VARCHAR2(360);
58      l_meaning            VARCHAR2(360);
59      l_source_id          NUMBER;
60      l_percent_count      NUMBER;
61      l_terr_with_inv_res  NUMBER;
62 
63      --framwork vars
64      reportStr LONG;
65      counter   NUMBER;
66      c_userid  VARCHAR2(50);
67      statusStr VARCHAR2(50);
68      errStr    VARCHAR2(4000);
69      fixInfo   VARCHAR2(4000);
70      isFatal   VARCHAR2(50);
71 
72      CURSOR C_valid_terrs(lc_source_id NUMBER)
73      IS
74      select qta.description name, count(*) terr_count
75      from jtf_terr_denorm_rules_all jtdr
76         , jtf_qual_types qta
77      where jtdr.source_id = lc_source_id
78       and jtdr.terr_id = jtdr.related_terr_id
79       and jtdr.qual_type_id = qta.qual_type_id
80      group by qta.description
81      order by 1;
82 
83      CURSOR C_valid_terrs_with_reps(lc_source_id NUMBER)
84      IS
85      select qta.description name, count(*) terr_count
86      from jtf_terr_denorm_rules_all jtdr
87         , jtf_qual_types_all qta
88      where jtdr.source_id = lc_source_id
89       and jtdr.terr_id = jtdr.related_terr_id
90       and jtdr.qual_type_id = qta.qual_type_id
91       and jtdr.resource_exists_flag = 'Y'
92      group by qta.description
93      order by 1;
94 
95      CURSOR C_num_values(lc_source_id NUMBER)
96      IS
97      select seed.name, count(*) value_count
98       from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
99          , jtf_seeded_qual_all_b seed
100          , jtf_qual_usgs_all jqu
101      where jtv.terr_qual_id = jtq.terr_qual_id
102        and jtq.terr_id IN ( select jt.terr_id from jtf_terr_all jt,
103                                                    jtf_terr_usgs_all jtu
104                             where jtu.source_id = lc_source_id
105                               and jtu.terr_id = jt.terr_id
106                               and jt.start_date_active <= SYSDATE
107                               and NVL(jt.end_date_active, SYSDATE) >= SYSDATE  )
108        and jtq.qual_usg_id = jqu.qual_usg_id
112       group by seed.name
109        and jqu.seeded_qual_id = seed.seeded_qual_id
110        and jqu.org_id = -3113
111        and (seed.org_id = -3114 OR seed.org_id is null)
113       order by 2 desc;
114 
115       CURSOR C_num_values_ALL(lc_source_id NUMBER)
116       IS
117       select seed.name, count(*) value_count
118       from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
119          , jtf_seeded_qual_all_b seed
120          , jtf_qual_usgs_all jqu
121      where jtv.terr_qual_id = jtq.terr_qual_id
122        and jtq.qual_usg_id = jqu.qual_usg_id
123        and jqu.seeded_qual_id = seed.seeded_qual_id
124        and jqu.org_id = -3113
125        and (seed.org_id = -3114 OR seed.org_id is null)
126       group by seed.name
127       order by 2 desc;
128 
129       CURSOR C_values_in_MV
130       IS
131       select seed.name, count(*) value_count
132       from ( SELECT /*+ ORDERED */
133 		  r.rowid rule_rowid
134 		, q.rowid qual_rowid
135 		, v.rowid val_rowid
136 		, r.terr_id terr_id
137 		, r.absolute_rank absolute_rank
138 		, r.related_terr_id related_terr_id
139 		, r.top_level_terr_id top_level_terr_id
140 		, r.num_winners num_winners
141 		, r.source_id source_id
142 		, q.terr_qual_id terr_qual_id
143 		, q.qual_usg_id qual_usg_id
144 		, v.terr_value_id  terr_value_id
145 		, v.comparison_operator comparison_operator
146 		, v.low_value_char_id low_value_char_id
147 	   FROM jtf_terr_denorm_rules_all r
148 	        , jtf_terr_qual_all q
149  	        , jtf_terr_values_all v
150 	   WHERE r.source_id = -1001
151 		  AND q.terr_id = r.related_terr_id
152 		  AND v.terr_qual_id = q.terr_qual_id
153 		  AND q.qual_usg_id <> -1102
154 		  AND NOT ( q.qual_usg_id = -1012 AND
155 		            ( v.comparison_operator = 'LIKE' OR v.comparison_operator = 'BETWEEN' )
156 		          )) mv
157          , jtf_qual_usgs_all jqu
158          , jtf_seeded_qual_all_b seed
159       where mv.qual_usg_id = jqu.qual_usg_id
160         and jqu.seeded_qual_id = seed.seeded_qual_id
161         and jqu.org_id = -3113
162         and (seed.org_id = -3114 OR seed.org_id is null)
163       group by seed.name
164       order by 2 desc;
165 
166       CURSOR C_values_in_System(lc_source_id NUMBER)
167       IS
168       select seed.name, jtv.comparison_operator operator, count(*) value_count
169       from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
170          , jtf_qual_usgs_all jqu
171          , jtf_seeded_qual_all_b seed
172       where jtv.terr_qual_id = jtq.terr_qual_id
173         and jtq.terr_id IN ( select jt.terr_id from jtf_terr_all jt,
174                       jtf_terr_usgs_all jtu
175                       where jtu.source_id = lc_source_id
176                         and jtu.terr_id = jt.terr_id
177                         and jt.start_date_active <= SYSDATE
178                         AND NVL(jt.end_date_active, SYSDATE) >= SYSDATE  )
179         and jtq.qual_usg_id = jqu.qual_usg_id
180         and jqu.seeded_qual_id = seed.seeded_qual_id
181         and jqu.org_id = -3113
182         and (seed.org_id = -3114 OR seed.org_id is null)
183       group by seed.name, jtv.comparison_operator
184       order by 3 desc;
185 
186       CURSOR C_values_in_system_ALL(lc_source_id NUMBER)
187       IS
188       select seed.name, jtv.comparison_operator operator, count(*) value_count
189       from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
190          , jtf_qual_usgs_all jqu
191          , jtf_seeded_qual_all_b seed
192       where jtv.terr_qual_id = jtq.terr_qual_id
193         and jtq.qual_usg_id = jqu.qual_usg_id
194         and jqu.seeded_qual_id = seed.seeded_qual_id
195         and jqu.org_id = -3113
196         and (seed.org_id = -3114 OR seed.org_id is null)
197       group by seed.name, jtv.comparison_operator
198       order by 3 desc;
199 
200       CURSOR C_values_in_System_warnings(lc_source_id NUMBER)
201       IS
202       select jta.name terr_name ,seed.name qual_name, jtv.comparison_operator operator, count(*) value_count
203       from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
204          , jtf_terr_all jta
205          , jtf_qual_usgs_all jqu
206          , jtf_seeded_qual_all_b seed
207       where jtv.terr_qual_id = jtq.terr_qual_id
208         and jtq.terr_id IN ( select jt.terr_id from jtf_terr_all jt,
209           jtf_terr_usgs_all jtu
210                       where jtu.source_id = lc_source_id
211                         and jtu.terr_id = jt.terr_id
212                         and jt.start_date_active <= SYSDATE
213                         AND NVL(jt.end_date_active, SYSDATE) >= SYSDATE  )
214         and jtq.qual_usg_id = jqu.qual_usg_id
215         and jqu.seeded_qual_id = seed.seeded_qual_id
216         and jta.terr_id = jtq.terr_id
217         and jqu.org_id = -3113
218         and (seed.org_id = -3114 OR seed.org_id is null)
219         and UPPER(jtv.comparison_operator) NOT IN ('=','LIKE','BETWEEN')
220       group by jta.name, seed.name, jtv.comparison_operator
221       order by 3 desc;
222 
223       CURSOR C_values_in_MV_per_qual
224       IS
225       select seed.name, mv.comparison_operator operator, count(*) value_count
226       from  ( SELECT /*+ ORDERED */
227 		  r.rowid rule_rowid
228 		, q.rowid qual_rowid
229 		, v.rowid val_rowid
230 		, r.terr_id terr_id
231 		, r.absolute_rank absolute_rank
232 		, r.related_terr_id related_terr_id
233 		, r.top_level_terr_id top_level_terr_id
234 		, r.num_winners num_winners
235 		, r.source_id source_id
236 		, q.terr_qual_id terr_qual_id
237 		, q.qual_usg_id qual_usg_id
238 		, v.terr_value_id  terr_value_id
239 		, v.comparison_operator comparison_operator
240 		, v.low_value_char_id low_value_char_id
241 	   FROM jtf_terr_denorm_rules_all r
242 	        , jtf_terr_qual_all q
246 		  AND v.terr_qual_id = q.terr_qual_id
243  	        , jtf_terr_values_all v
244 	   WHERE r.source_id = -1001
245 		  AND q.terr_id = r.related_terr_id
247 		  AND q.qual_usg_id <> -1102
248 		  AND NOT ( q.qual_usg_id = -1012 AND
249 		            ( v.comparison_operator = 'LIKE' OR v.comparison_operator = 'BETWEEN' )
250 		          )) mv
251          , jtf_qual_usgs_all jqu
252          , jtf_seeded_qual_all_b seed
253       where mv.qual_usg_id = jqu.qual_usg_id
254       and jqu.seeded_qual_id = seed.seeded_qual_id
255       and jqu.org_id = -3113
256       and (seed.org_id = -3114 OR seed.org_id is null)
257       group by seed.name, mv.comparison_operator
258       order by 3 desc;
259 
260       CURSOR C_Usages IS
261       select source_id, meaning, lookup_code
262       from jtf_sources_all
263       order by 1 desc;
264 
265    BEGIN
266 
267     --This set-up of the CLOB must be done
268     JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
269     JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
270     JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
271 
272     JTF_DIAGNOSTIC_COREAPI.line_out('SUMMARY WARNING INFORMATION');
273     JTF_DIAGNOSTIC_COREAPI.line_out('========================================================');
274     JTF_DIAGNOSTIC_COREAPI.brprint;
275     JTF_DIAGNOSTIC_COREAPI.brprint;
276 
277     for sources in C_Usages loop
278 
279       l_source_id := sources.source_id;
280       l_lookup_code := sources.lookup_code;
281       l_meaning := sources.meaning;
282 
283       --show warnings for each usage here
284     JTF_DIAGNOSTIC_COREAPI.line_out('  WARNINGS for '||l_meaning);
285     JTF_DIAGNOSTIC_COREAPI.brprint;
286     JTF_DIAGNOSTIC_COREAPI.line_out('  ------------------------------------------------------------');
287     JTF_DIAGNOSTIC_COREAPI.brprint;
288 
289     JTF_DIAGNOSTIC_COREAPI.line_out('  1. '||l_meaning||' qualifiers using non-performant operators: ');
290 
291     for vals_in_system in C_values_in_system_warnings(l_source_id)
292     loop
293       if C_values_in_system_warnings%notfound
294       then
295         JTF_DIAGNOSTIC_COREAPI.line_out('NONE');
296       end if;
297       JTF_DIAGNOSTIC_COREAPI.brprint;
298       JTF_DIAGNOSTIC_COREAPI.line_out('  Territory = ' ||vals_in_system.terr_name ||'; Qualifier = ' || vals_in_system.qual_name);
299       JTF_DIAGNOSTIC_COREAPI.brprint;
300       JTF_DIAGNOSTIC_COREAPI.line_out('                  ' ||
301                                       '                  ' ||
302                                       ' Operator = '''|| vals_in_system.operator||'''');
303     end loop;
304       JTF_DIAGNOSTIC_COREAPI.brprint;
305       JTF_DIAGNOSTIC_COREAPI.brprint;
306 
307       select count(*)
308       into l_percent_count
309       from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
310          , jtf_qual_usgs_all jqu
311       where jtv.terr_qual_id = jtq.terr_qual_id
312         and jtq.terr_id IN ( select jt.terr_id from jtf_terr_all jt,
313             jtf_terr_usgs_all jtu
314                       where jtu.source_id = l_source_id
315                         and jtu.terr_id = jt.terr_id
316                         and jt.start_date_active <= SYSDATE
317                         AND NVL(jt.end_date_active, SYSDATE) >= SYSDATE  )
318         and jtq.qual_usg_id = jqu.qual_usg_id
319         and jqu.org_id = -3113
320         and SUBSTR(jtv.low_value_char,0,1) = '%'
321         and jtv.comparison_operator = 'LIKE';
322 
323       JTF_DIAGNOSTIC_COREAPI.line_out('  2. No. of '||l_meaning||' qualifier values using % as a first char = '||l_percent_count);
324       JTF_DIAGNOSTIC_COREAPI.brprint;
325       JTF_DIAGNOSTIC_COREAPI.brprint;
326 
327 
328 
329     end loop;
330 
331     JTF_DIAGNOSTIC_COREAPI.brprint;
332 
333     JTF_DIAGNOSTIC_COREAPI.line_out('GENERAL INFORMATION');
334     JTF_DIAGNOSTIC_COREAPI.line_out('========================================================');
335     JTF_DIAGNOSTIC_COREAPI.brprint;
336     JTF_DIAGNOSTIC_COREAPI.brprint;
337 
338     --Total Number of Parties
339     --===========================
340     SELECT COUNT(*)
341     into l_parties
342     FROM hz_parties;
343 
344     JTF_DIAGNOSTIC_COREAPI.line_out('  Total Number of Parties = '||l_parties);
345     JTF_DIAGNOSTIC_COREAPI.brprint;
346     JTF_DIAGNOSTIC_COREAPI.brprint;
347 
348     --Total Number of Party Sites
349     --================================
350     SELECT COUNT(*)
351     into l_party_sites
352     FROM hz_party_sites;
353 
354     JTF_DIAGNOSTIC_COREAPI.line_out('  Total Number of Party Sites = '||l_party_sites);
355     JTF_DIAGNOSTIC_COREAPI.brprint;
356     JTF_DIAGNOSTIC_COREAPI.brprint;
357 
358     --Total Number of Locations
359     --==============================
360     SELECT COUNT(*)
361     into l_locations
362     FROM hz_locations;
363 
364     JTF_DIAGNOSTIC_COREAPI.line_out( '  Total Number of Locations = '||l_locations);
365     JTF_DIAGNOSTIC_COREAPI.brprint;
366     JTF_DIAGNOSTIC_COREAPI.brprint;
367 
368     JTF_DIAGNOSTIC_COREAPI.brprint;
369 
370   -- for Usage specific information
371   for sources in C_Usages loop
372 
373     l_source_id := sources.source_id;
374     l_lookup_code := sources.lookup_code;
375     l_meaning := sources.meaning;
376 
377     JTF_DIAGNOSTIC_COREAPI.line_out('USAGE: '||l_meaning);
378     JTF_DIAGNOSTIC_COREAPI.line_out('========================================================');
379     JTF_DIAGNOSTIC_COREAPI.brprint;
383     JTF_DIAGNOSTIC_COREAPI.line_out('  WARNINGS');
380     JTF_DIAGNOSTIC_COREAPI.brprint;
381 
382     --show warnings for each usage here
384     JTF_DIAGNOSTIC_COREAPI.brprint;
385     JTF_DIAGNOSTIC_COREAPI.line_out('  --------------------------------------------------------');
386     JTF_DIAGNOSTIC_COREAPI.brprint;
387 
388     JTF_DIAGNOSTIC_COREAPI.line_out('  1. '||l_meaning||' qualifiers using non-performant operators: ');
389 
390     for vals_in_system in C_values_in_system_warnings(l_source_id)
391     loop
392       JTF_DIAGNOSTIC_COREAPI.brprint;
393       JTF_DIAGNOSTIC_COREAPI.line_out('  Territory = ' ||vals_in_system.terr_name ||'; Qualifier = ' || vals_in_system.qual_name);
394       JTF_DIAGNOSTIC_COREAPI.brprint;
395       JTF_DIAGNOSTIC_COREAPI.line_out('                  ' ||
396                                       '                  ' ||
397                                       ' Operator = '''|| vals_in_system.operator||'''');
398     end loop;
399       JTF_DIAGNOSTIC_COREAPI.brprint;
400       JTF_DIAGNOSTIC_COREAPI.brprint;
401 
402       select count(*)
403       into l_percent_count
404       from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
405          , jtf_qual_usgs_all jqu
406       where jtv.terr_qual_id = jtq.terr_qual_id
407         and jtq.terr_id IN ( select jt.terr_id from jtf_terr_all jt,
408             jtf_terr_usgs_all jtu
409                       where jtu.source_id = l_source_id
410                         and jtu.terr_id = jt.terr_id
411                         and jt.start_date_active <= SYSDATE
412                         AND NVL(jt.end_date_active, SYSDATE) >= SYSDATE  )
413         and jtq.qual_usg_id = jqu.qual_usg_id
414         and jqu.org_id = -3113
415         and SUBSTR(jtv.low_value_char,0,1) = '%'
416         and jtv.comparison_operator = 'LIKE';
417 
418       JTF_DIAGNOSTIC_COREAPI.line_out('  2. No. of '||l_meaning||' qualifier values using % as a first char = '||l_percent_count);
419       JTF_DIAGNOSTIC_COREAPI.brprint;
420       JTF_DIAGNOSTIC_COREAPI.brprint;
421 
422     --show other informational info here...
423     JTF_DIAGNOSTIC_COREAPI.line_out('  ADDITIONAL INFORMATION');
424     JTF_DIAGNOSTIC_COREAPI.brprint;
425     JTF_DIAGNOSTIC_COREAPI.line_out('  --------------------------------------------------------');
426     JTF_DIAGNOSTIC_COREAPI.brprint;
427 
428     --#1. Number of ACTIVE Territories
429     --======================================
430     select count(*)
431     into l_active_sales_terrs
432     from jtf_terr_all jta, jtf_terr_usgs_all jtua
433     where jtua.terr_id = jta.terr_id
434       and jtua.source_id = l_source_id
435       and jta.start_date_active <= SYSDATE
436       AND NVL(jta.end_date_active, SYSDATE) >= SYSDATE;
437 
438       JTF_DIAGNOSTIC_COREAPI.line_out('  1. Number of ACTIVE '||l_meaning||' Territories = '||l_active_sales_terrs);
439       JTF_DIAGNOSTIC_COREAPI.brprint;
440       JTF_DIAGNOSTIC_COREAPI.brprint;
441 
442     --#2. Number of VALID Territories for Assignment
443     --====================================================
444 
445     JTF_DIAGNOSTIC_COREAPI.line_out('  2. Number of ACTIVE '||l_meaning||' Territories VALID for Assignment: ');
446 
447     for sales_terr in C_valid_terrs(l_source_id)
448     loop
449       JTF_DIAGNOSTIC_COREAPI.brprint;
450       JTF_DIAGNOSTIC_COREAPI.line_out('  Transaction Type = ' || sales_terr.name || ', Territory Count = ' || sales_terr.terr_count);
451     end loop;
452       JTF_DIAGNOSTIC_COREAPI.brprint;
453       JTF_DIAGNOSTIC_COREAPI.brprint;
454 
455     --#3. Number of VALID Sales Territories with Reps for Assignment
456     --==============================================================
457 
458     JTF_DIAGNOSTIC_COREAPI.line_out('  3. Number of ACTIVE '||l_meaning||' Territories VALID for Assignment, with Reps: ');
459 
460     for sales_terr_with_rep in C_valid_terrs_with_reps(l_source_id)
461     loop
462       JTF_DIAGNOSTIC_COREAPI.brprint;
463       JTF_DIAGNOSTIC_COREAPI.line_out('  Transaction Type = ' || sales_terr_with_rep.name || ', Territory Count = ' || sales_terr_with_rep.terr_count);
464   end loop;
465       JTF_DIAGNOSTIC_COREAPI.brprint;
466       JTF_DIAGNOSTIC_COREAPI.brprint;
467 
468     --#4. Total Number of Values in System
469     --=====================================
470       select count(*)
471       into l_num_values
472       from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
473       where jtv.terr_qual_id = jtq.terr_qual_id
474         and jtq.terr_id IN ( select jt.terr_id from jtf_terr_all jt,
475           jtf_terr_usgs_all jtu
476                       where jtu.source_id = l_source_id
477                         and jtu.terr_id = jt.terr_id
478                         and jt.start_date_active <= SYSDATE
479                         AND NVL(jt.end_date_active, SYSDATE + 1) >= SYSDATE  );
480 
481     JTF_DIAGNOSTIC_COREAPI.line_out('  4. Number of Values for ACTIVE '||l_meaning||' Territories = '||l_num_values);
482     JTF_DIAGNOSTIC_COREAPI.brprint;
483     JTF_DIAGNOSTIC_COREAPI.brprint;
484 
485 
486     --#5. Total # of Territory Resource Assignments
487     --=============================================
488     select count(*)
489     into l_terr_res_assign
490     from jtf_terr_rsc_all jtr
491     where jtr.start_date_active <= SYSDATE
492       AND NVL(jtr.end_date_active, SYSDATE) >= SYSDATE
493       AND EXISTS ( SELECT jtdr.terr_id
494                   FROM jtf_terr_denorm_rules_all jtdr
495                   WHERE jtdr.source_id = l_source_id );
496 
500 
497     JTF_DIAGNOSTIC_COREAPI.line_out( '  5. Number of ACTIVE '||l_meaning||' Territory Resource Assignments = '||l_terr_res_assign);
498     JTF_DIAGNOSTIC_COREAPI.brprint;
499     JTF_DIAGNOSTIC_COREAPI.brprint;
501     --#6. Total # of DISTINCT Territory Resources (Total # of Active Reps)
502     --====================================================================
503     SELECT COUNT (*)
504     into l_active_reps FROM (
505     select DISTINCT jtr.resource_id, jtr.resource_type
506     from    jtf_terr_rsc_all jtr
507     where jtr.start_date_active <= SYSDATE
508       AND NVL(jtr.end_date_active, SYSDATE) >= SYSDATE
509       AND EXISTS ( SELECT jtdr.terr_id
510                   FROM jtf_terr_denorm_rules_all jtdr
511                   WHERE jtdr.source_id = l_source_id ) );
512 
513     JTF_DIAGNOSTIC_COREAPI.line_out('  6. Number of ACTIVE '||l_meaning||' Territory Resources (Total # of Active Reps) = '||l_active_reps);
514     JTF_DIAGNOSTIC_COREAPI.brprint;
515     JTF_DIAGNOSTIC_COREAPI.brprint;
516 
517     --#7. Total Number of Values per each Qualifier in System
518     --========================================================
519 
520     JTF_DIAGNOSTIC_COREAPI.line_out('  7. Number of ALL values for each '||l_meaning||' Qualifier: ');
521 
522     for num_values_rec in C_num_values_ALL(l_source_id)
523     loop
524       JTF_DIAGNOSTIC_COREAPI.brprint;
525       JTF_DIAGNOSTIC_COREAPI.line_out('  '||num_values_rec.name || ' = ' || num_values_rec.value_count);
526     end loop;
527       JTF_DIAGNOSTIC_COREAPI.brprint;
528       JTF_DIAGNOSTIC_COREAPI.brprint;
529 
530     --#8. Total Number of Values per each Qualifier Operator in System
531     --=================================================================
532 
533     JTF_DIAGNOSTIC_COREAPI.line_out('  8. Number of ALL values for each '||l_meaning||' Qualifier, per operator: ');
534 
535     for vals_in_system in C_values_in_system_ALL(l_source_id)
536     loop
537       JTF_DIAGNOSTIC_COREAPI.brprint;
538       JTF_DIAGNOSTIC_COREAPI.line_out('  '||vals_in_system.name || ' with '''||vals_in_system.operator ||''' Operator = '||vals_in_system.value_count);
539     end loop;
540       JTF_DIAGNOSTIC_COREAPI.brprint;
541       JTF_DIAGNOSTIC_COREAPI.brprint;
542 
543     --#9. Total Number of Values per each Qualifier in System
544     --========================================================
545 
546     JTF_DIAGNOSTIC_COREAPI.line_out('  9. Number of ACTIVE values for each '||l_meaning||' Qualifier: ');
547 
548     for num_values_rec in C_num_values(l_source_id)
549     loop
550       JTF_DIAGNOSTIC_COREAPI.brprint;
551       JTF_DIAGNOSTIC_COREAPI.line_out('  '||num_values_rec.name || ' = ' || num_values_rec.value_count);
552     end loop;
553       JTF_DIAGNOSTIC_COREAPI.brprint;
554       JTF_DIAGNOSTIC_COREAPI.brprint;
555 
556     --#10. Total Number of Values per each Qualifier Operator in System
557     --=================================================================
558 
559     JTF_DIAGNOSTIC_COREAPI.line_out('  10. Number of ACTIVE values for each '||l_meaning||' Qualifier, per operator: ');
560 
561     for vals_in_system in C_values_in_system(l_source_id)
562     loop
563       JTF_DIAGNOSTIC_COREAPI.brprint;
564       JTF_DIAGNOSTIC_COREAPI.line_out('  '||vals_in_system.name || ' with '''||vals_in_system.operator ||''' Operator = '||vals_in_system.value_count);
565     end loop;
566       JTF_DIAGNOSTIC_COREAPI.brprint;
567       JTF_DIAGNOSTIC_COREAPI.brprint;
568 
569 
570     if l_source_id = -1001 then --sales
571       --Total Number of values per Qualifier in MV
572     --==============================================
573     JTF_DIAGNOSTIC_COREAPI.line_out('  11. Number of values for each Qualifier in MV (only for '||l_meaning||') : ');
574 
575     for vals_in_MV in C_values_in_MV
576     loop
577       JTF_DIAGNOSTIC_COREAPI.brprint;
578       JTF_DIAGNOSTIC_COREAPI.line_out('  '||vals_in_MV.name || ' = ' || vals_in_MV.value_count);
579     end loop;
580       JTF_DIAGNOSTIC_COREAPI.brprint;
581       JTF_DIAGNOSTIC_COREAPI.brprint;
582 
583 
584     --Total Number of Values per each Qualifier Operator in MV
585     --=============================================================
586     JTF_DIAGNOSTIC_COREAPI.line_out('  12. Number of Values for each Qualifier, per operator in MV (only for '||l_meaning||') : ');
587 
588     for vals_in_MV_per_qual in C_values_in_MV_per_qual
589     loop
590       JTF_DIAGNOSTIC_COREAPI.brprint;
591       JTF_DIAGNOSTIC_COREAPI.line_out('  '||vals_in_MV_per_qual.name || ' with '''|| vals_in_MV_per_qual.operator||''' Operator = '|| vals_in_MV_per_qual.value_count);
592     end loop;
593       JTF_DIAGNOSTIC_COREAPI.brprint;
594       JTF_DIAGNOSTIC_COREAPI.brprint;
595 
596   end if;
597      JTF_DIAGNOSTIC_COREAPI.brprint;
598  end loop;
599 
600        statusStr := 'SUCCESS';
601          reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
602          report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
603     EXCEPTION when others then
604       JTF_DIAGNOSTIC_COREAPI.ERRORPRINT(sqlerrm);
605       reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
606          report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport('FAILURE','Exception thrown',fixInfo,isFatal);
607    END runTest;
608 
609   ------------------------------------------------------------
610   -- procedure to report name back to framework
611   ------------------------------------------------------------
612   PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
613   BEGIN
614     name := 'Territory Diagnostics Tests ';
615   END getComponentName;
616 
617   ------------------------------------------------------------
618   -- procedure to report test description back to framework
619   ------------------------------------------------------------
620   PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
621   BEGIN
622     descStr := 'Territory set-up information using various criteria';
623   END getTestDesc;
624 
625   ------------------------------------------------------------
626   -- procedure to report test name back to framework
627   ------------------------------------------------------------
628   PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
629   BEGIN
630     name := 'Territory set-up information';
631   END getTestName;
632 
633 
634 END JTF_TERR_DIAGNOSTIC_TEST;
635 
636