[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_DEFINITION_REPORT_PVT
Source
1 PACKAGE BODY JTF_TERR_DEFINITION_REPORT_PVT AS
2 /* $Header: jtftrrdb.pls 120.0 2005/06/02 18:21:49 appldev ship $ */
3
4 /*----------------------------------------------------------------
5 PROCEDURE report_wrapper
6 ----------------------------------------------------------------*/
7
8 PROCEDURE report_wrapper
9 ( p_response IN varchar2,
10 p_srp IN number, -- our resource_id
11 --p_usg IN number,
12 p_qual in number) -- our qual_usg_id
13 IS
14 g_image_prefix varchar2(250) := '/OA_MEDIA/'||icx_sec.getid(icx_sec.pv_language_code)||'/';
15 l_response varchar2(2000);
16 l_srp number;
17 --l_sgrp number;
18 l_usg number;
19 l_qual number;
20 l_usg_name varchar2(250);
21 l_ziplo varchar2(2000);
22 l_ziphi varchar2(2000);
23 l_agent varchar2(2000);
24 l_rec_count number;
25
26 l_display_type varchar2(100);
27 l_convert_to_id_flag varchar2(1);
28
29 p_territory_id NUMBER;
30 p_territory_type_id NUMBER;
31
32 -- cursor of result territory searches with resource and qualifier restrictions
33 CURSOR cur_terr is
34 select j.terr_id, wf_notification.substitutespecialchars(j.name) name, j.rank
35 from jtf_terr j
36 where NVL(j.end_date_active, sysdate) >= sysdate
37 AND j.start_date_active <= sysdate
38 AND EXISTS
39 ( select jtr.terr_id
40 from jtf_terr_rsc jtr, jtf_terr_qual jtq --, jtf_terr_usgs jtu
41 where jtr.terr_id = jtq.terr_id
42 and jtr.resource_id = decode(l_srp,-1,jtr.resource_id,l_srp)
43 and jtq.qual_usg_id = decode(l_qual, -1, jtq.qual_usg_id, l_qual)
44 --and jtu.source_id = decode(l_usg, -1, jtu.source_id, l_usg)
45 AND jtr.terr_id = j.terr_id
46 );
47
48 -- cursor to get salesreps DELIVERY
49 CURSOR c_get_salesrep(ci_terr_id NUMBER) IS
50 select distinct resource_id, wf_notification.substitutespecialchars(resource_name) resource_name
51 from jtf_terr_resources_v jtrv
52 where jtrv.terr_id = ci_terr_id
53 order by resource_name;
54
55 -- cursor to get dynamic qualifiers
56 CURSOR c_get_terr_qual (p_template_terr_id NUMBER) IS
57 SELECT distinct TERR_QUAL_ID,
58 TERR_ID,
59 QUAL_USG_ID,
60 ORG_ID,
61 wf_notification.substitutespecialchars(qualifier_name) qualifier_name
62 FROM jtf_terr_qualifiers_v
63 WHERE terr_id = p_template_terr_id;
64
65 -- cursor to get values for dynamic qualifier values
66 CURSOR c_get_terr_values (p_terr_qual_id NUMBER) IS
67 SELECT j1.TERR_VALUE_ID
68 , wf_notification.substitutespecialchars(j1.COMPARISON_OPERATOR) COMPARISON_OPERATOR
69 , wf_notification.substitutespecialchars(j1.LOW_VALUE_CHAR_DESC) LOW_VALUE_CHAR_DESC
70 , wf_notification.substitutespecialchars(j1.HIGH_VALUE_CHAR_DESC) HIGH_VALUE_CHAR_DESC
71 , j1.LOW_VALUE_NUMBER
72 , j1.HIGH_VALUE_NUMBER
73 , wf_notification.substitutespecialchars(j1.INTEREST_TYPE) INTEREST_TYPE
74 , wf_notification.substitutespecialchars(j1.PRIMARY_INTEREST_CODE) PRIMARY_INTEREST_CODE
75 , wf_notification.substitutespecialchars(j1.SECONDARY_INTEREST_CODE) SECONDARY_INTEREST_CODE
76 , wf_notification.substitutespecialchars(j1.CURRENCY_CODE) CURRENCY_CODE
77 , j1.LOW_VALUE_CHAR_ID
78 , wf_notification.substitutespecialchars(j1.CNR_GROUP_NAME) CNR_GROUP_NAME
79 , wf_notification.substitutespecialchars(DISPLAY_TYPE) DISPLAY_TYPE
80 , wf_notification.substitutespecialchars(CONVERT_TO_ID_FLAG) CONVERT_TO_ID_FLAG
81 --bug#3175772
82 , VALUE1_ID
83 , wf_notification.substitutespecialchars(VALUE1_DESC) VALUE1_DESC
84 , VALUE2_ID
85 , wf_notification.substitutespecialchars(VALUE2_DESC) VALUE2_DESC
86 , VALUE3_ID
87 , wf_notification.substitutespecialchars(VALUE3_DESC) VALUE3_DESC
88 FROM jtf_terr_values_desc_v j1
89 WHERE j1.terr_qual_id = p_terr_qual_id
90 ORDER BY j1.LOW_VALUE_CHAR_DESC, j1.COMPARISON_OPERATOR;
91
92 BEGIN
93
94 l_srp := p_srp;
95 l_qual := p_qual;
96 --l_usg := p_usg;
97 l_response := p_response;
98 fnd_client_info.set_org_context(fnd_profile.value('ORG_ID'));
99
100 if (icx_sec.validateSession(c_function_code => 'JTF_TERR_DFN_RPT', c_validate_only => 'Y')) then
101
102 if l_response = 'Excel' then
103 xls(l_srp,
104 l_qual);
105 else
106 l_agent := owa_util.get_cgi_env('SCRIPT_NAME');
107 htp.htmlopen;
108 htp.headOpen;
109 htp.title('Territory Definitions');
110
111 htp.headClose;
112 htp.bodyopen(cattributes=>'bgcolor="#CCCCCC"');
113 htp.formOpen( curl => l_agent||'/'||'jtf_terr_definition_report.xls', cmethod => 'POST', cattributes => ' name="excel"');
114 htp.formHidden( cname => 'p_srp', cvalue => l_srp);
115 --htp.formHidden( cname => 'p_sgrp', cvalue => l_sgrp);
116 htp.formHidden( cname => 'p_qual', cvalue => l_qual);
117 htp.formClose;
118
119 htp.p('<CENTER>');
120 htp.tableOpen('border="0" ');
121 htp.tableRowOpen( calign => 'TOP' );
122 --htp.tableData( htf.img(curl=>g_image_prefix||'oppty.gif'));
123 htp.tableData( '<FONT size=+1 face="times new roman">' || 'Territory Definitions Report', cnowrap => 'TRUE');
124 --htp.tabledata(htf.anchor('JavaScript:document.excel.submit();',htf.img(curl=>g_image_prefix||'up_excel.gif')),calign=>'Right',cattributes=>'width="100%"');
125 htp.tableRowClose;
126 /*
127 htp.tableRowOpen( calign => 'TOP' );
128 htp.tableData('Usage: ' ||l_usg_name);
129 htp.tableRowClose;
130 */
131 htp.tableClose;
132 htp.br;
133
134 htp.tableOpen (cattributes => 'COLS=4 border="1" width="100%"');
135 fnd_client_info.set_org_context(fnd_profile.value_wnps('ORG_ID'));
136 -- display territories
137 l_rec_count := 0;
138 FOR rec_terr in cur_terr loop
139 htp.tableRowOpen;
140 htp.p('<TR NOWRAP bgcolor="#999999" >');
141 htp.p('<th colspan="2" width="50%" align="center">');
142 htp.p(rec_terr.name);
143 htp.p('</th>');
144 htp.p('<th colspan="2" width="50%" align="center">');
145 htp.p('Rank: ' ||rec_terr.rank);
146 htp.p('</th>');
147 htp.tableRowClose;
148 l_rec_count := l_rec_count + 1;
149 -- display sales reps
150 FOR rec_get_salesrep in c_get_salesrep(rec_terr.terr_id) loop
151 htp.tableRowOpen;
152 htp.p('<td colspan="2" width="50%" align="left"><bold>');
153 htp.p(rec_get_salesrep.resource_name);
154 htp.p('</bold></td>');
155 --htp.p('<td colspan="2" width="50%" align="left">');
156 --htp.p(rec_get_salesrep.resource_id);
157 --htp.p('</td>');
158 --htp.tableRowClose;
159 END LOOP;
160 -- display qualifiers
161 FOR rec_get_terr_qual in c_get_terr_qual(rec_terr.terr_id) loop
162 htp.tableRowOpen;
163 htp.p('<TR NOWRAP bgcolor="#999999" >');
164 htp.p('<td colspan="2" align="center" width="50%">');
165 htp.p(rec_get_terr_qual.qualifier_name);
166 htp.p('</td>');
167 if rec_get_terr_qual.qual_usg_id = -1012 then
168 htp.p('<td align="center">');
169 htp.p('');
170 htp.p('</td>');
171 htp.p('<td align="center">');
172 htp.p('Customer Name Range Group');
173 htp.p('</td>');
174 end if;
175 htp.tableRowClose;
176 -- display qualifier values
177 FOR rec_get_terr_values in c_get_terr_values(rec_get_terr_qual.terr_qual_id) loop
178 -- output qualifier values according to display_type and convert_to_id_flag
179 l_display_type := rec_get_terr_values.display_type;
180 l_convert_to_id_flag := rec_get_terr_values.convert_to_id_flag;
181
182 If (l_display_type = 'CHAR') then
183 If (l_convert_to_id_flag = 'Y') then
184 htp.tableRowOpen;
185 htp.p('<td align="left"><bold>');
186 htp.p(rec_get_terr_values.comparison_operator);
187 htp.p('</bold></td>');
188 htp.p('<td align="left"><bold>');
189 htp.p(rec_get_terr_values.low_value_char_desc);
190 htp.p('</bold></td>');
191 htp.p('<td align="left">');
192 htp.p(rec_get_terr_values.low_value_char_id);
193 htp.p('</td>');
194 htp.p('<td align="left"><bold>');
195 htp.p('');
196 htp.p('</bold></td>');
197 htp.tableRowClose;
198 else -- (l_convert_to_id_flag = 'N')
199 htp.tableRowOpen;
200 htp.p('<td align="left"><bold>');
201 htp.p(rec_get_terr_values.comparison_operator);
202 htp.p('</bold></td>');
203 htp.p('<td align="left"><bold>');
204 htp.p(rec_get_terr_values.low_value_char_desc);
205 htp.p('</bold></td>');
206 htp.p('<td align="left">');
207 htp.p(rec_get_terr_values.high_value_char_desc);
208 htp.p('</td>');
209 htp.p('<td align="left"><bold>');
210 htp.p(rec_get_terr_values.CNR_GROUP_NAME);
211 htp.p('</bold></td>');
212 htp.tableRowClose;
213 End if;
214 elsif (l_display_type = 'INTEREST_TYPE') then
215 htp.tableRowOpen;
216 htp.p('<td align="left"><bold>');
217 htp.p(rec_get_terr_values.comparison_operator);
218 htp.p('</bold></td>');
219 htp.p('<td align="left"><bold>');
220 htp.p(rec_get_terr_values.interest_type);
221 htp.p('</bold></td>');
222 htp.p('<td align="left">');
223 htp.p(rec_get_terr_values.primary_interest_code);
224 htp.p('</td>');
225 htp.p('<td align="left"><bold>');
226 htp.p(rec_get_terr_values.secondary_interest_code);
227 htp.p('</bold></td>');
228 htp.tableRowClose;
229 elsif (l_display_type = 'NUMBER') then
230 htp.tableRowOpen;
231 htp.p('<td align="left"><bold>');
232 htp.p(rec_get_terr_values.comparison_operator);
233 htp.p('</bold></td>');
234 htp.p('<td align="left"><bold>');
235 htp.p(rec_get_terr_values.low_value_number);
236 htp.p('</bold></td>');
237 htp.p('<td align="left">');
238 htp.p(rec_get_terr_values.high_value_number);
239 htp.p('</td>');
240 htp.p('<td align="left"><bold>');
241 htp.p('');
242 htp.p('</bold></td>');
243 htp.tableRowClose;
244 elsif (l_display_type = 'CURRENCY') then
245 htp.tableRowOpen;
246 htp.p('<td align="left"><bold>');
247 htp.p(rec_get_terr_values.comparison_operator);
248 htp.p('</bold></td>');
249 htp.p('<td align="left"><bold>');
250 htp.p(rec_get_terr_values.low_value_number);
251 htp.p('</bold></td>');
252 htp.p('<td align="left">');
253 htp.p(rec_get_terr_values.high_value_number);
254 htp.p('</td>');
255 htp.p('<td align="left"><bold>');
256 htp.p(rec_get_terr_values.currency_code);
257 htp.p('</bold></td>');
258 htp.tableRowClose;
259 -- bug#3175772
260 elsif (l_display_type = 'DEP_2FIELDS_CHAR_2IDS') then
261 If (l_convert_to_id_flag = 'Y') then
262 htp.tableRowOpen;
263 htp.p('<td align="left"><bold>');
264 htp.p(rec_get_terr_values.comparison_operator);
265 htp.p('</bold></td>');
266 htp.p('<td align="left"><bold>');
267 htp.p(rec_get_terr_values.value1_desc );
268 htp.p('</bold></td>');
269 htp.p('<td align="left">');
270 htp.p(rec_get_terr_values.value1_id);
271 htp.p('</td>');
272 htp.p('<td align="left"><bold>');
273 htp.p('');
274 htp.p('</bold></td>');
275 htp.tableRowClose;
276 if rec_get_terr_values.value2_id is not null
277 then
278 htp.tableRowOpen;
279 htp.p('<td align="left"><bold>');
280 htp.p(rec_get_terr_values.comparison_operator);
281 htp.p('</bold></td>');
282 htp.p('<td align="left"><bold>');
283 htp.p(rec_get_terr_values.value2_desc );
284 htp.p('</bold></td>');
285 htp.p('<td align="left">');
286 htp.p(rec_get_terr_values.value2_id);
287 htp.p('</td>');
288 htp.p('<td align="left"><bold>');
289 htp.p('');
290 htp.p('</bold></td>');
291 htp.tableRowClose;
292 end if;
293
294 else -- (l_convert_to_id_flag = 'N')
295 htp.tableRowOpen;
296 htp.p('<td align="left"><bold>');
297 htp.p(rec_get_terr_values.comparison_operator);
298 htp.p('</bold></td>');
299 htp.p('<td align="left"><bold>');
300 htp.p(rec_get_terr_values.low_value_char_desc);
301 htp.p('</bold></td>');
302 htp.p('<td align="left">');
303 htp.p(rec_get_terr_values.high_value_char_desc);
304 htp.p('</td>');
305 htp.p('<td align="left"><bold>');
306 htp.p(rec_get_terr_values.CNR_GROUP_NAME);
307 htp.p('</bold></td>');
308 htp.tableRowClose;
309 End if;
310 else
311 htp.tableRowOpen;
312 htp.p('<td colspan="4" align="left"><bold>');
313 htp.p('NO DATA DISPLAYED');
314 htp.p('</bold></td>');
315 htp.tableRowClose;
316 end if;
317 END LOOP;
318 END LOOP;
319 END LOOP;
320 If l_rec_count = 0 then
321 htp.p('No territories meet search criteria.');
322 End if;
323
324 htp.tableClose;
325 htp.bodyClose;
326 htp.htmlClose;
327 end if; -- whether we're using excel or not
328 else
329 htp.p('Invalid session');
330 end if;
331 EXCEPTION
332 when no_data_found then
333 htp.p('Territory Definition Report: No Data Found!!!');
334 htp.print('Territory Definition Report: No Data Found!!!');
335 when others then
336 htp.p('Territory Definition Report: No Data Found!!!');
337 htp.print('report_wrapper: ' || substr(SQLERRM, 1,200));
338 END report_wrapper;
339
340 /*----------------------------------------------------------------
341 PROCEDURE XLS
342 ----------------------------------------------------------------*/
343
344 PROCEDURE XLS
345 ( p_srp IN number,
346 --p_sgrp IN number,
347 p_qual in number)
348 IS
349 g_image_prefix varchar2(250) := '/OA_MEDIA/'||icx_sec.getid(icx_sec.pv_language_code)||'/';
350 l_response varchar2(2000);
351 v_tab varchar2(1);
352 v_tabrow varchar2(32747);
353 l_rec_count number;
354
355 l_srp number;
356 l_sgrp number;
357 l_qual number;
358 l_counter number;
359
360 l_display_type varchar2(100);
361 l_convert_to_id_flag varchar2(1);
362 h1_divider varchar2(100) := '****************************************';
363 h2_divider varchar2(100) := '===================';
364 h3_divider varchar2(100) := '-------------------';
365
366 p_territory_id as_territories.territory_id%type ;
367 p_territory_type_id as_territories.territory_type_id%type ;
368
369 -- cursor of result territory searches with resource and qualifier restrictions
370 CURSOR cur_terr is
371 select terr_id, wf_notification.substitutespecialchars(name) name, rank from jtf_terr_all
372 where terr_id in
373 (select jtr.terr_id from jtf_terr_rsc jtr, jtf_terr_qual jtq
374 where jtr.terr_id = jtq.terr_id
375 and jtr.resource_id = decode(l_srp,-1,jtr.resource_id,l_srp)
376 and jtq.qual_usg_id = decode(l_qual, -1, jtq.qual_usg_id, l_qual));
377
378 -- cursor to get salesreps DELIVERY
379 CURSOR c_get_salesrep(ci_terr_id NUMBER) IS
380 select distinct resource_id, wf_notification.substitutespecialchars(resource_name) resource_name
381 from jtf_terr_resources_v jtrv
382 where jtrv.terr_id = ci_terr_id
383 order by resource_name;
384
385 -- cursor to get dynamic qualifiers
386 CURSOR c_get_terr_qual (p_template_terr_id NUMBER) IS
387 SELECT distinct TERR_QUAL_ID,
388 TERR_ID,
389 QUAL_USG_ID,
390 ORG_ID,
391 wf_notification.substitutespecialchars(qualifier_name) qualifier_name
392 FROM jtf_terr_qualifiers_v
393 WHERE terr_id = p_template_terr_id;
394
395 -- cursor to get values for dynamic qualifier values
396 CURSOR c_get_terr_values (p_terr_qual_id NUMBER) IS
397 SELECT j1.TERR_VALUE_ID
398 , wf_notification.substitutespecialchars(j1.COMPARISON_OPERATOR) COMPARISON_OPERATOR
399 , wf_notification.substitutespecialchars(j1.LOW_VALUE_CHAR_DESC) LOW_VALUE_CHAR_DESC
400 , wf_notification.substitutespecialchars(j1.HIGH_VALUE_CHAR_DESC) HIGH_VALUE_CHAR_DESC
401 , j1.LOW_VALUE_NUMBER
402 , j1.HIGH_VALUE_NUMBER
403 , wf_notification.substitutespecialchars(j1.INTEREST_TYPE) INTEREST_TYPE
404 , wf_notification.substitutespecialchars(j1.PRIMARY_INTEREST_CODE) PRIMARY_INTEREST_CODE
405 , wf_notification.substitutespecialchars(j1.SECONDARY_INTEREST_CODE) SECONDARY_INTEREST_CODE
406 , wf_notification.substitutespecialchars(j1.CURRENCY_CODE) CURRENCY_CODE
407 , j1.LOW_VALUE_CHAR_ID
408 , wf_notification.substitutespecialchars(j1.CNR_GROUP_NAME) CNR_GROUP_NAME
409 , wf_notification.substitutespecialchars(DISPLAY_TYPE) DISPLAY_TYPE
410 , wf_notification.substitutespecialchars(CONVERT_TO_ID_FLAG) CONVERT_TO_ID_FLAG
411 FROM jtf_terr_values_desc_v j1
412 WHERE j1.terr_qual_id = p_terr_qual_id
413 ORDER BY j1.LOW_VALUE_CHAR_DESC, j1.COMPARISON_OPERATOR;
414
415 BEGIN
416
417 l_srp := p_srp;
418 --l_sgrp := p_sgrp;
419 l_qual := p_qual ;
420
421 owa_util.mime_header('application/excel');
422 fnd_client_info.set_org_context(fnd_profile.value('ORG_ID'));
423
424 SELECT FND_GLOBAL.TAB
425 INTO v_tab
426 FROM dual;
427
428 v_tabrow := null;
429 -- display territories
430 l_rec_count := 0;
431
432 FOR rec_terr in cur_terr loop
433 /*
434 if cur_terr%rowcount > 1 then
435 v_tabrow := v_tabrow||' '||v_tab;
436 htp.p(v_tabrow);
437 htp.p(v_tabrow);
438 v_tabrow := null;
439 end if;
440 */
441 v_tabrow := null;
442 htp.p(v_tabrow); -- add a blank before each territory
443 v_tabrow := null;
444 v_tabrow := v_tabrow||rec_terr.name||v_tab;
445 v_tabrow := v_tabrow||'Rank: '||rec_terr.rank||v_tab;
446 htp.p(v_tabrow); -- print out prepared line
447 v_tabrow := null;
448 v_tabrow := v_tabrow||h1_divider||v_tab;
449 v_tabrow := v_tabrow||h1_divider||v_tab;
450 htp.p(v_tabrow); -- divider since we dont' know how to format rows in XLS
451 l_rec_count := l_rec_count + 1;
452 -- display sales reps
453 FOR rec_get_salesrep in c_get_salesrep(rec_terr.terr_id) loop
454 v_tabrow := null;
455 v_tabrow := v_tabrow||rec_get_salesrep.resource_name||v_tab;
456 --v_tabrow := v_tabrow||rec_get_salesrep.resource_id||v_tab;
457 htp.p(v_tabrow);
458 END LOOP;
459 v_tabrow := null;
460 v_tabrow := v_tabrow||h2_divider||v_tab;
461 htp.p(v_tabrow);
462 -- display qualifiers
463 FOR rec_get_terr_qual in c_get_terr_qual(rec_terr.terr_id) loop
464 v_tabrow := null;
465 htp.p(v_tabrow); -- put in a CR before each qualifier
466 v_tabrow := null;
467 v_tabrow := v_tabrow||rec_get_terr_qual.qualifier_name||v_tab;
468 --v_tabrow := v_tabrow||rec_get_terr_qual.terr_qual_id||v_tab;
469 if rec_get_terr_qual.qual_usg_id = -1012 then
470 v_tabrow := v_tabrow||v_tab||v_tab||'Customer Name Range Group'||v_tab;
471 end if;
472 htp.p(v_tabrow);
473 v_tabrow := null;
474 v_tabrow := v_tabrow||h3_divider||v_tab;
475 htp.p(v_tabrow);
476
477 -- display qualifier values
478 l_counter := 0;
479 FOR rec_get_terr_values in c_get_terr_values(rec_get_terr_qual.terr_qual_id) loop
480 -- output qualifier values according to display_type and convert_to_id_flag
481 l_display_type := rec_get_terr_values.display_type;
482 l_convert_to_id_flag := rec_get_terr_values.convert_to_id_flag;
483 l_counter := l_counter + 1;
484 If (l_display_type = 'CHAR') then
485 If (l_convert_to_id_flag = 'Y') then
486 v_tabrow := null;
487 v_tabrow := v_tabrow||rec_get_terr_values.comparison_operator||v_tab;
488 v_tabrow := v_tabrow||rec_get_terr_values.low_value_char_desc||v_tab;
489 --v_tabrow := v_tabrow||rec_get_terr_values.low_value_char_id|| 'CHAR_Y l_counter:'||l_counter|| v_tab;
490 v_tabrow := v_tabrow||rec_get_terr_values.low_value_char_id||v_tab;
491 htp.p(v_tabrow);
492 else -- (l_convert_to_id_flag = 'N')
493 v_tabrow := null;
494 v_tabrow := v_tabrow||rec_get_terr_values.comparison_operator||v_tab;
495 v_tabrow := v_tabrow||rec_get_terr_values.low_value_char_desc||v_tab;
496 --v_tabrow := v_tabrow||rec_get_terr_values.high_value_char_desc|| 'CHAR_N l_counter:'||l_counter|| v_tab;
497 v_tabrow := v_tabrow||rec_get_terr_values.high_value_char_desc||v_tab;
498 v_tabrow := v_tabrow||rec_get_terr_values.cnr_group_name|| v_tab;
499 htp.p(v_tabrow);
500 End if;
501 elsif (l_display_type = 'INTEREST_TYPE') then
502 v_tabrow := null;
503 v_tabrow := v_tabrow||rec_get_terr_values.comparison_operator||v_tab;
504 v_tabrow := v_tabrow||rec_get_terr_values.interest_type||v_tab;
505 v_tabrow := v_tabrow||rec_get_terr_values.primary_interest_code||v_tab;
506 --v_tabrow := v_tabrow||rec_get_terr_values.secondary_interest_code|| 'INT_T l_counter:'||l_counter|| v_tab;
507 v_tabrow := v_tabrow||rec_get_terr_values.secondary_interest_code||v_tab;
508 htp.p(v_tabrow);
509 elsif (l_display_type = 'NUMBER') then
510 v_tabrow := null;
511 v_tabrow := v_tabrow||rec_get_terr_values.comparison_operator||v_tab;
512 v_tabrow := v_tabrow||rec_get_terr_values.low_value_number||v_tab;
513 --v_tabrow := v_tabrow||rec_get_terr_values.high_value_number|| 'NUM l_counter:'||l_counter|| v_tab;
514 v_tabrow := v_tabrow||rec_get_terr_values.high_value_number||v_tab;
515 htp.p(v_tabrow);
516 elsif (l_display_type = 'CURRENCY') then
517 v_tabrow := null;
518 v_tabrow := v_tabrow||rec_get_terr_values.comparison_operator||v_tab;
519 v_tabrow := v_tabrow||rec_get_terr_values.low_value_number||v_tab;
520 v_tabrow := v_tabrow||rec_get_terr_values.high_value_number||v_tab;
521 --v_tabrow := v_tabrow||rec_get_terr_values.currency_code|| 'CUR l_counter:'||l_counter|| v_tab;
522 v_tabrow := v_tabrow||rec_get_terr_values.currency_code||v_tab;
523 htp.p(v_tabrow);
524 else
525 v_tabrow := null;
526 v_tabrow := v_tabrow||'NO DATA DISPLAYED'||v_tab;
527 htp.p(v_tabrow);
528 end if;
529 END LOOP;
530 END LOOP;
531 END LOOP;
532 If l_rec_count = 0 then
533 v_tabrow := null;
534 v_tabrow := v_tabrow||'No territories meet search criteria.'||v_tab;
535 htp.p(v_tabrow);
536 End if;
537
538 EXCEPTION
539 when no_data_found then
540 htp.print('Territory Definition excel: No Data Found!!!');
541 when others then
542 htp.print('Territory Definition excel: '||substr(SQLERRM, 1,200));
543 END XLS;
544
545 END;