1 package body FND_CONC_TEMPLATES as
2 /* $Header: AFCPTPLB.pls 120.5.12010000.4 2009/01/30 18:07:29 jtoruno ship $ */
3 --
4 -- Package
5 -- FND_CONC_TEMPLATES
6 --
7 -- Purpose
8 -- Concurrent processing utilities for Templates and OPP
9 --
10
11 --
12 -- PRIVATE VARIABLES
13 --
14 -- 7017250 - This variable is used to determine if the new xdo columns exits.
15 xdo_columns_cntr number := null;
16
17 default_templ_shrt_name fnd_application.application_short_name%TYPE;
18 default_templ_code varchar2(50);
19 default_templ_shrt_name_opt fnd_application.application_short_name%TYPE;
20 default_templ_code_opt varchar2(50);
21 l_special_template_case varchar2(2);
22
23 --
24 -- Exception info.
25
26 --
27 -- PRIVATE FUNCTIONS
28 --
29 --
30
31 -- PUBLIC FUNCTIONS
32 --
33
34 -- NAME
35 -- get_template_information
36 -- Purpose
37 -- Called to determine the template information needed for OPP processing
38 --
39
40 procedure get_template_information(
41 prog_app_id IN number,
42 prog_app_name IN varchar2,
43 conc_prog_name IN varchar2,
44 nls_lang IN varchar2,
45 nls_terr IN varchar2,
46 s_nls_lang IN varchar2,
47 s_nls_terr IN varchar2,
48 template_obtained IN OUT NOCOPY varchar2,
49 template_name IN OUT NOCOPY varchar2,
50 template_language IN OUT NOCOPY varchar2,
51 format IN OUT NOCOPY varchar2,
52 request_language IN OUT NOCOPY varchar2,
53 iso_language IN OUT NOCOPY varchar2,
54 iso_territory IN OUT NOCOPY varchar2,
55 template_app_name IN OUT NOCOPY varchar2,
56 template_code IN OUT NOCOPY varchar2,
57 format_type IN OUT NOCOPY varchar2 ) is
58
59 def_iso_territory varchar2(2);
60 wo_xdo_iso_language varchar2(2);
61 wo_xdo_iso_territory varchar2(2);
62 terr_indep varchar2(1);
63 def_output_type varchar2(10);
64 l_po_output_type varchar2(10);
65
66 begin
67
68 -- Get the profile option for default output type override.
69 l_po_output_type := NULL;
70 FND_PROFILE.GET( 'FND_DEF_TEMPL_OUTPUT_TYPE', l_po_output_type );
71
72 get_iso_lang_and_terr(nls_lang, nls_terr,
73 wo_xdo_iso_language, wo_xdo_iso_territory);
74
75 -- Obtain a default template if set for a conc program definition
76 begin
77 select template_appl_short_name, template_code
78 into default_templ_shrt_name, default_templ_code
79 from fnd_concurrent_programs
80 where application_id = prog_app_id
81 and concurrent_program_name = conc_prog_name;
82 exception
83 when NO_DATA_FOUND then
84 default_templ_code := NULL;
85 default_templ_shrt_name := NULL;
86 end;
87
88 template_obtained := 'N';
89
90 -- 1st Default template, Request Language
91 if (def_template_check(
92 default_templ_shrt_name, conc_prog_name, default_templ_code,
93 nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
94 'N',
95 template_obtained, template_name, template_language,
96 format, request_language, iso_language, iso_territory,
97 template_app_name, template_code,
98 format_type, def_output_type ) ) then
99 GOTO check_template_output_type;
100 end if;
101
102
103 -- 2nd Default template, Request language with territory independence
104 if (def_template_check(
105 default_templ_shrt_name, conc_prog_name, default_templ_code,
106 nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
107 'Y',
108 template_obtained, template_name, template_language,
109 format, request_language, iso_language, iso_territory,
110 template_app_name, template_code,
111 format_type, def_output_type ) ) then
112 GOTO check_template_output_type;
113 end if;
114
115
116 -- 2-A Default template, Request language with default territory
117 get_def_iso_terr(nls_lang, def_iso_territory);
118 if (def_template_check(
119 default_templ_shrt_name, conc_prog_name, default_templ_code,
120 nls_lang, wo_xdo_iso_language, def_iso_territory,
121 'N',
122 template_obtained, template_name, template_language,
123 format, request_language, iso_language, iso_territory,
124 template_app_name, template_code,
125 format_type, def_output_type ) ) then
126 GOTO check_template_output_type;
127 end if;
128
129
130 l_special_template_case := '2B';
131 -- 2-B Default template, Request language with first row for templates
132 if (def_template_check(
133 default_templ_shrt_name, conc_prog_name, default_templ_code,
134 nls_lang, wo_xdo_iso_language, def_iso_territory,
135 'N',
136 template_obtained, template_name, template_language,
137 format, request_language, iso_language, iso_territory,
138 template_app_name, template_code,
139 format_type, def_output_type ) ) then
140 GOTO check_template_output_type;
141 end if;
142
143
144 -- 5th Default template, Session language/terr
145 get_iso_lang_and_terr(s_nls_lang, s_nls_terr,
146 wo_xdo_iso_language, wo_xdo_iso_territory);
147 if (def_template_check(
148 default_templ_shrt_name, conc_prog_name, default_templ_code,
149 s_nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
150 'N',
151 template_obtained, template_name, template_language,
152 format, request_language, iso_language, iso_territory,
153 template_app_name, template_code,
154 format_type, def_output_type ) ) then
155 request_language := nls_lang;
156 GOTO check_template_output_type;
157 end if;
158
159
160 -- 6th Default template, Session language with territory independence
161 if (def_template_check(
162 default_templ_shrt_name, conc_prog_name, default_templ_code,
163 s_nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
164 'Y',
165 template_obtained, template_name, template_language,
166 format, request_language, iso_language, iso_territory,
167 template_app_name, template_code,
168 format_type, def_output_type ) ) then
169 request_language := nls_lang;
170 GOTO check_template_output_type;
171 end if;
172
173
174 l_special_template_case := '7';
175 -- 7th Grab any default temp default language and territory
176 if (def_template_check(
177 default_templ_shrt_name, conc_prog_name, default_templ_code,
178 nls_lang, wo_xdo_iso_language, def_iso_territory,
179 'N',
180 template_obtained, template_name, template_language,
181 format, request_language, iso_language, iso_territory,
182 template_app_name, template_code,
183 format_type, def_output_type ) ) then
184 GOTO check_template_output_type;
185 end if;
186
187
188 get_iso_lang_and_terr(nls_lang, nls_terr,
189 wo_xdo_iso_language, wo_xdo_iso_territory);
190 -- 3rd No Def Template, Request language
191 if (no_def_template_check(
192 prog_app_name, conc_prog_name,
193 nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
194 'N',
195 template_obtained, template_name, template_language,
196 format, request_language, iso_language, iso_territory,
197 template_app_name, template_code,
198 format_type, def_output_type ) ) then
199 GOTO check_template_output_type;
200 end if;
201
202
203 -- 4th No Def Template, Request language and territory independent
204 if (no_def_template_check(
205 prog_app_name, conc_prog_name,
206 nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
207 'Y',
208 template_obtained, template_name, template_language,
209 format, request_language, iso_language, iso_territory,
210 template_app_name, template_code,
211 format_type, def_output_type ) ) then
212 GOTO check_template_output_type;
213 end if;
214
215
216 get_def_iso_terr(nls_lang, def_iso_territory);
217 -- 4-A Request language with default territory
218 if (no_def_template_check(
219 prog_app_name, conc_prog_name,
220 nls_lang, wo_xdo_iso_language, def_iso_territory,
221 'N',
222 template_obtained, template_name, template_language,
223 format, request_language, iso_language, iso_territory,
224 template_app_name, template_code,
225 format_type, def_output_type ) ) then
226 GOTO check_template_output_type;
227 end if;
228
229
230 l_special_template_case := '4B';
231 -- 4-B Request language with first row for templates
232 if (no_def_template_check(
233 prog_app_name, conc_prog_name,
234 nls_lang, wo_xdo_iso_language, def_iso_territory,
235 'N',
236 template_obtained, template_name, template_language,
237 format, request_language, iso_language, iso_territory,
238 template_app_name, template_code,
239 format_type, def_output_type ) ) then
240 GOTO check_template_output_type;
241 end if;
242
243
244 get_iso_lang_and_terr(s_nls_lang, s_nls_terr,
245 wo_xdo_iso_language, wo_xdo_iso_territory );
246 -- 8th No Default Template, session language with territory
247 if (no_def_template_check(
248 prog_app_name, conc_prog_name,
249 s_nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
250 'N',
251 template_obtained, template_name, template_language,
252 format, request_language, iso_language, iso_territory,
253 template_app_name, template_code,
254 format_type, def_output_type ) ) then
255 request_language := nls_lang;
256 GOTO check_template_output_type;
257 end if;
258
259
260 -- 9th No Default template, session language with territory independence
261 if (no_def_template_check(
262 prog_app_name, conc_prog_name,
263 s_nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
264 'Y',
265 template_obtained, template_name, template_language,
266 format, request_language, iso_language, iso_territory,
267 template_app_name, template_code,
268 format_type, def_output_type ) ) then
269 request_language := nls_lang;
270 GOTO check_template_output_type;
271 end if;
272
273
274 l_special_template_case := '10';
275 -- 10th Final chk No Def Template, get template from xdo_templates_b
276 if (no_def_template_check(
277 prog_app_name, conc_prog_name,
278 nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
279 'N',
280 template_obtained, template_name, template_language,
281 format, request_language, iso_language, iso_territory,
282 template_app_name, template_code,
283 format_type, def_output_type ) ) then
284 GOTO check_template_output_type;
285 end if;
286
287 << check_template_output_type >>
288
289 if ( template_obtained = 'Y' ) then
290 -- check if new column for default output type is present
291 if ( xdo_columns_cntr = 2 ) then
292 -- Default output type on template definition has priority
293 if ( def_output_type is NULL ) then
294 -- Check if profile option is set and use instead
295 if (l_po_output_type is NULL) then
296 -- Set the format to the value set by fnd_lookup_values_vl table
297 find_the_format(format_type, format);
298 else
299 format := l_po_output_type;
300 end if;
301 else
302 format := def_output_type;
303 end if;
304 else
305 -- Check if profile option is set and use instead
306 if (l_po_output_type is NULL) then
307 -- Set the format to the value set by fnd_lookup_values_vl table
308 find_the_format(format_type, format);
309 else
310 format := l_po_output_type;
311 end if;
312 end if;
313 end if;
314
315 end get_template_information;
316
317
318 -- NAME
319 -- fill_no_def_template
320 -- Purpose
321 -- Called if default template is NOT set for defined program
322 -- in fnd_concurrent_program
323 --
324
325 procedure fill_no_def_template(
326 prog_app_name IN varchar2,
327 conc_prog_name IN varchar2,
328 nls_lang IN varchar2,
329 iso_lang IN varchar2,
330 iso_terr IN varchar2,
331 terr_indep IN varchar2,
332 template_obtained IN OUT NOCOPY varchar2,
333 template_name IN OUT NOCOPY varchar2,
334 template_language IN OUT NOCOPY varchar2,
335 format IN OUT NOCOPY varchar2,
336 request_language IN OUT NOCOPY varchar2,
337 iso_language IN OUT NOCOPY varchar2,
338 iso_territory IN OUT NOCOPY varchar2,
339 template_app_name IN OUT NOCOPY varchar2,
340 template_code IN OUT NOCOPY varchar2,
341 format_type IN OUT NOCOPY varchar2,
342 def_output_type IN OUT NOCOPY varchar2 ) is
343
344 begin
345 if (terr_indep = 'N') then
346 if ( xdo_columns_cntr = 2 ) then
347 EXECUTE IMMEDIATE
348 'select a.template_name, I.NAME||'': ''||T.territory_short_name, '||
349 ' b.nls_language, x.language, x.territory, '||
350 ' a.application_short_name, a.template_code, '||
351 ' a.template_type_code, a.default_output_type '||
352 ' from xdo_lobs x, xdo_templates_vl a, fnd_languages b, '||
356 ' and a.template_code = x.lob_code '||
353 ' fnd_iso_languages_vl I, fnd_territories_vl T '||
354 ' where a.ds_app_short_name = :prog_app_name '||
355 ' and a.data_source_code = :conc_prog_name '||
357 ' and lower(x.language) = lower(:iso_lang) '||
358 ' and lower(x.territory) = lower(:iso_terr) '||
359 ' and b.nls_language = :nls_lang '||
360 ' and lower(b.iso_language) = I.iso_language_2 '||
361 ' and upper(x.territory) = T.territory_code '||
362 ' and file_status = ''E'' '||
363 ' and lob_type in (''TEMPLATE'',''MLS_TEMPLATE'') '||
364 ' and (a.dependency_flag is NULL or a.dependency_flag = ''P'') '||
365 ' and sysdate between a.start_date and nvl(a.end_date,sysdate) '||
366 ' and rownum = 1 '
367 INTO template_name, template_language, request_language,
368 iso_language, iso_territory, template_app_name,
369 template_code, format_type, def_output_type
370 USING prog_app_name, conc_prog_name, iso_lang, iso_terr, nls_lang;
371 else
372 select a.template_name, I.NAME||': '||T.territory_short_name,
373 b.nls_language, x.language, x.territory,
374 a.application_short_name, a.template_code,
375 a.template_type_code
376 into template_name, template_language, request_language,
377 iso_language, iso_territory, template_app_name,
378 template_code, format_type
379 from xdo_lobs x, xdo_templates_vl a, fnd_languages b,
380 fnd_iso_languages_vl I, fnd_territories_vl T
381 where a.ds_app_short_name = prog_app_name
382 and a.data_source_code = conc_prog_name
383 and a.template_code = x.lob_code
384 and lower(x.language) = lower(iso_lang)
385 and lower(x.territory) = lower(iso_terr)
386 and b.nls_language = nls_lang
387 and lower(b.iso_language) = I.iso_language_2
388 and upper(x.territory) = T.territory_code
389 and file_status = 'E'
390 and lob_type in ('TEMPLATE','MLS_TEMPLATE')
391 and (a.dependency_flag is NULL or a.dependency_flag = 'P')
392 and sysdate between a.start_date and nvl(a.end_date,sysdate)
393 and rownum = 1;
394 end if;
395 else
396 if ( xdo_columns_cntr = 2 ) then
397 EXECUTE IMMEDIATE
398 'select a.template_name, I.NAME, '||
399 ' b.nls_language, x.language, x.territory, '||
400 ' a.application_short_name, a.template_code, '||
401 ' a.template_type_code, a.default_output_type '||
402 ' from xdo_lobs x, xdo_templates_vl a, fnd_languages b, '||
403 ' fnd_iso_languages_vl I '||
404 ' where a.ds_app_short_name = :prog_app_name '||
405 ' and a.data_source_code = :conc_prog_name '||
406 ' and a.template_code = x.lob_code '||
407 ' and lower(x.language) = lower(:iso_lang) '||
408 ' and lower(x.territory) = ''00'' '||
409 ' and b.nls_language = :nls_lang '||
410 ' and lower(b.iso_language) = I.iso_language_2 '||
411 ' and file_status = ''E'' '||
412 ' and lob_type in (''TEMPLATE'',''MLS_TEMPLATE'') '||
413 ' and (a.dependency_flag is NULL or a.dependency_flag = ''P'') '||
414 ' and sysdate between a.start_date and nvl(a.end_date,sysdate) '||
415 ' and rownum = 1 '
416 INTO template_name, template_language, request_language,
417 iso_language, iso_territory, template_app_name,
418 template_code, format_type, def_output_type
419 USING prog_app_name, conc_prog_name, iso_lang, nls_lang;
420 else
421 select a.template_name, I.NAME,
422 b.nls_language, x.language, x.territory,
423 a.application_short_name, a.template_code,
424 a.template_type_code
425 into template_name, template_language, request_language,
426 iso_language, iso_territory, template_app_name,
427 template_code, format_type
428 from xdo_lobs x, xdo_templates_vl a, fnd_languages b,
429 fnd_iso_languages_vl I
430 where a.ds_app_short_name = prog_app_name
431 and a.data_source_code = conc_prog_name
432 and a.template_code = x.lob_code
433 and lower(x.language) = lower(iso_lang)
434 and lower(x.territory) = '00'
435 and b.nls_language = nls_lang
436 and lower(b.iso_language) = I.iso_language_2
437 and file_status = 'E'
438 and lob_type in ('TEMPLATE','MLS_TEMPLATE')
439 and (a.dependency_flag is NULL or a.dependency_flag = 'P')
440 and sysdate between a.start_date and nvl(a.end_date,sysdate)
441 and rownum = 1;
442 end if;
443 end if;
444
445 if ( sql%rowcount > 0 ) then
446 template_obtained := 'Y';
447 else
448 template_obtained := 'N';
449 end if;
450
451 exception
452 when others then
453 NULL;
454 end fill_no_def_template;
455
456
457 -- NAME
458 -- fill_default_template
459 -- Purpose
463
460 -- Called if default template is set for defined program
461 -- in fnd_concurrent_program
462 --
464 procedure fill_default_template(
465 prog_app_name IN varchar2,
466 conc_prog_name IN varchar2,
467 default_templ_code IN varchar2,
468 nls_lang IN varchar2,
469 iso_lang IN varchar2,
470 iso_terr IN varchar2,
471 terr_indep IN varchar2,
472 template_obtained IN OUT NOCOPY varchar2,
473 template_name IN OUT NOCOPY varchar2,
474 template_language IN OUT NOCOPY varchar2,
475 format IN OUT NOCOPY varchar2,
476 request_language IN OUT NOCOPY varchar2,
477 iso_language IN OUT NOCOPY varchar2,
478 iso_territory IN OUT NOCOPY varchar2,
479 template_app_name IN OUT NOCOPY varchar2,
480 template_code IN OUT NOCOPY varchar2,
481 format_type IN OUT NOCOPY varchar2,
482 def_output_type IN OUT NOCOPY varchar2 ) is
483
484 begin
485 if (terr_indep = 'N') then
486 if ( xdo_columns_cntr = 2 ) then
487 EXECUTE IMMEDIATE
488 'select a.template_name, I.NAME||'': ''||T.territory_short_name, '||
489 ' b.nls_language, x.language, x.territory, '||
490 ' a.application_short_name, a.template_code, '||
491 ' a.template_type_code, a.default_output_type '||
492 ' from xdo_lobs x, xdo_templates_vl a, fnd_languages b, '||
493 ' fnd_iso_languages_vl I, fnd_territories_vl T '||
494 ' where a.ds_app_short_name = :prog_app_name '||
495 ' and a.data_source_code = :conc_prog_name '||
496 ' and a.template_code = :default_templ_code '||
497 ' and a.template_code = x.lob_code '||
498 ' and lower(x.language) = lower(:iso_lang) '||
499 ' and lower(x.territory) = lower(:iso_terr) '||
500 ' and b.nls_language = :nls_lang '||
501 ' and lower(b.iso_language) = I.iso_language_2 '||
502 ' and upper(x.territory) = T.territory_code '||
503 ' and file_status = ''E'' '||
504 ' and lob_type in (''TEMPLATE'',''MLS_TEMPLATE'') '||
505 ' and (a.dependency_flag is NULL or a.dependency_flag = ''P'') '||
506 ' and sysdate between a.start_date and nvl(a.end_date,sysdate) '||
507 ' and rownum = 1 '
508 INTO template_name, template_language, request_language,
509 iso_language, iso_territory, template_app_name, template_code,
510 format_type, def_output_type
511 USING prog_app_name, conc_prog_name, default_templ_code, iso_lang,
512 iso_terr, nls_lang;
513 else
514 select a.template_name, I.NAME||': '||T.territory_short_name,
515 b.nls_language, x.language, x.territory,
516 a.application_short_name, a.template_code, a.template_type_code
517 into template_name, template_language, request_language,
518 iso_language, iso_territory, template_app_name, template_code,
519 format_type
520 from xdo_lobs x, xdo_templates_vl a, fnd_languages b,
521 fnd_iso_languages_vl I, fnd_territories_vl T
522 where a.ds_app_short_name = prog_app_name
523 and a.data_source_code = conc_prog_name
524 and a.template_code = default_templ_code
525 and a.template_code = x.lob_code
526 and lower(x.language) = lower(iso_lang)
527 and lower(x.territory) = lower(iso_terr)
528 and b.nls_language = nls_lang
529 and lower(b.iso_language) = I.iso_language_2
530 and upper(x.territory) = T.territory_code
531 and file_status = 'E'
532 and lob_type in ('TEMPLATE','MLS_TEMPLATE')
533 and (a.dependency_flag is NULL or a.dependency_flag = 'P')
534 and sysdate between a.start_date and nvl(a.end_date,sysdate)
535 and rownum = 1;
536 end if;
537 else
538 if ( xdo_columns_cntr = 2 ) then
539 EXECUTE IMMEDIATE
540 'select a.template_name, I.NAME, '||
541 ' b.nls_language, x.language, x.territory, '||
542 ' a.application_short_name, a.template_code, '||
543 ' a.template_type_code, a.default_output_type '||
544 ' from xdo_lobs x, xdo_templates_vl a, fnd_languages b, '||
545 ' fnd_iso_languages_vl I '||
546 ' where a.ds_app_short_name = :prog_app_name '||
547 ' and a.data_source_code = :conc_prog_name '||
548 ' and a.template_code = :default_templ_code '||
549 ' and a.template_code = x.lob_code '||
550 ' and lower(x.language) = lower(:iso_lang) '||
551 ' and lower(x.territory) = ''00'' '||
552 ' and b.nls_language = :nls_lang '||
553 ' and lower(b.iso_language) = I.iso_language_2 '||
554 ' and file_status = ''E'' '||
555 ' and lob_type in (''TEMPLATE'',''MLS_TEMPLATE'') '||
556 ' and (a.dependency_flag is NULL or a.dependency_flag = ''P'') '||
557 ' and sysdate between a.start_date and nvl(a.end_date,sysdate) '||
561 format_type, def_output_type
558 ' and rownum = 1 '
559 INTO template_name, template_language, request_language,
560 iso_language, iso_territory, template_app_name, template_code,
562 USING prog_app_name, conc_prog_name, default_templ_code, iso_lang,
563 nls_lang;
564
565 else
566 select a.template_name, I.NAME,
567 b.nls_language, x.language, x.territory,
568 a.application_short_name, a.template_code, a.template_type_code
569 into template_name, template_language, request_language,
570 iso_language, iso_territory, template_app_name, template_code,
571 format_type
572 from xdo_lobs x, xdo_templates_vl a, fnd_languages b,
573 fnd_iso_languages_vl I
574 where a.ds_app_short_name = prog_app_name
575 and a.data_source_code = conc_prog_name
576 and a.template_code = default_templ_code
577 and a.template_code = x.lob_code
578 and lower(x.language) = lower(iso_lang)
579 and lower(x.territory) = '00'
580 and b.nls_language = nls_lang
581 and lower(b.iso_language) = I.iso_language_2
582 and file_status = 'E'
583 and lob_type in ('TEMPLATE','MLS_TEMPLATE')
584 and (a.dependency_flag is NULL or a.dependency_flag = 'P')
585 and sysdate between a.start_date and nvl(a.end_date,sysdate)
586 and rownum = 1;
587 end if;
588 end if;
589 if ( sql%rowcount > 0 ) then
590 template_obtained := 'Y';
591 else
592 template_obtained := 'N';
593 end if;
594
595 exception
596 when others then
597 NULL;
598 end fill_default_template;
599
600
601 -- NAME
602 -- fill_spec_def_template
603 -- Purpose
604 -- Called if special query is requried to obtain template info
605 --
606
607 procedure fill_special_def_template(
608 prog_app_name IN varchar2,
609 conc_prog_name IN varchar2,
610 default_templ_code IN varchar2,
611 nls_lang IN varchar2,
612 iso_lang IN varchar2,
613 iso_terr IN varchar2,
614 terr_indep IN varchar2,
615 template_obtained IN OUT NOCOPY varchar2,
616 template_name IN OUT NOCOPY varchar2,
617 template_language IN OUT NOCOPY varchar2,
618 format IN OUT NOCOPY varchar2,
619 request_language IN OUT NOCOPY varchar2,
620 iso_language IN OUT NOCOPY varchar2,
621 iso_territory IN OUT NOCOPY varchar2,
622 template_app_name IN OUT NOCOPY varchar2,
623 template_code IN OUT NOCOPY varchar2,
624 format_type IN OUT NOCOPY varchar2,
625 def_output_type IN OUT NOCOPY varchar2 ) is
626
627 begin
628 if (l_special_template_case = '2B') then
629 if ( xdo_columns_cntr = 2 ) then
630 EXECUTE IMMEDIATE
631 'select a.template_name, I.NAME, '||
632 ' b.nls_language, x.language, x.territory, '||
633 ' a.application_short_name, a.template_code, '||
634 ' a.template_type_code, a.default_output_type '||
635 ' from xdo_lobs x, xdo_templates_vl a, fnd_languages b, '||
636 ' fnd_iso_languages_vl I '||
637 ' where a.ds_app_short_name = :prog_app_name '||
638 ' and a.data_source_code = :conc_prog_name '||
639 ' and a.template_code = :default_templ_code '||
640 ' and a.template_code = x.lob_code '||
641 ' and lower(x.language) = lower(:iso_lang) '||
642 ' and b.nls_language = :nls_lang '||
643 ' and lower(b.iso_language) = I.iso_language_2 '||
644 ' and file_status = ''E'' '||
645 ' and lob_type in (''TEMPLATE'',''MLS_TEMPLATE'') '||
646 ' and (a.dependency_flag is NULL or a.dependency_flag = ''P'') '||
647 ' and sysdate between a.start_date and nvl(a.end_date,sysdate) '||
648 ' and rownum = 1 '
649 INTO template_name, template_language, request_language,
650 iso_language, iso_territory, template_app_name, template_code,
651 format_type, def_output_type
652 USING prog_app_name, conc_prog_name, default_templ_code, iso_lang,
653 nls_lang;
654 else
655 select a.template_name, I.NAME,
656 b.nls_language, x.language, x.territory,
657 a.application_short_name, a.template_code, a.template_type_code
658 into template_name, template_language, request_language,
659 iso_language, iso_territory, template_app_name, template_code,
660 format_type
661 from xdo_lobs x, xdo_templates_vl a, fnd_languages b,
662 fnd_iso_languages_vl I
663 where a.ds_app_short_name = prog_app_name
664 and a.data_source_code = conc_prog_name
665 and a.template_code = default_templ_code
666 and a.template_code = x.lob_code
667 and lower(x.language) = lower(iso_lang)
668 and b.nls_language = nls_lang
669 and lower(b.iso_language) = I.iso_language_2
670 and file_status = 'E'
671 and lob_type in ('TEMPLATE','MLS_TEMPLATE')
672 and (a.dependency_flag is NULL or a.dependency_flag = 'P')
673 and sysdate between a.start_date and nvl(a.end_date,sysdate)
674 and rownum = 1;
675 end if;
676
677 elsif (l_special_template_case = '4B') then
678 if ( xdo_columns_cntr = 2 ) then
679 EXECUTE IMMEDIATE
680 'select a.template_name, I.NAME, '||
681 ' b.nls_language, x.language, x.territory, '||
682 ' a.application_short_name, a.template_code, '||
683 ' a.template_type_code, a.default_output_type '||
684 ' from xdo_lobs x, xdo_templates_vl a, fnd_languages b, '||
685 ' fnd_iso_languages_vl I '||
686 ' where a.ds_app_short_name = :prog_app_name '||
687 ' and a.data_source_code = :conc_prog_name '||
688 ' and a.template_code = x.lob_code '||
689 ' and lower(x.language) = lower(:iso_lang) '||
690 ' and b.nls_language = :nls_lang '||
691 ' and lower(b.iso_language) = I.iso_language_2 '||
692 ' and file_status = ''E'' '||
693 ' and lob_type in (''TEMPLATE'',''MLS_TEMPLATE'') '||
694 ' and (a.dependency_flag is NULL or a.dependency_flag = ''P'') '||
695 ' and sysdate between a.start_date and nvl(a.end_date,sysdate) '||
696 ' and rownum = 1 '
697 INTO template_name, template_language, request_language,
698 iso_language, iso_territory, template_app_name,
699 template_code, format_type, def_output_type
700 USING prog_app_name, conc_prog_name, iso_lang,
701 nls_lang;
702 else
703 select a.template_name, I.NAME,
704 b.nls_language, x.language, x.territory,
705 a.application_short_name, a.template_code,
706 a.template_type_code
707 into template_name, template_language, request_language,
708 iso_language, iso_territory, template_app_name,
709 template_code, format_type
710 from xdo_lobs x, xdo_templates_vl a, fnd_languages b,
711 fnd_iso_languages_vl I
712 where a.ds_app_short_name = prog_app_name
713 and a.data_source_code = conc_prog_name
714 and a.template_code = x.lob_code
715 and lower(x.language) = lower(iso_lang)
716 and b.nls_language = nls_lang
717 and lower(b.iso_language) = I.iso_language_2
718 and file_status = 'E'
719 and lob_type in ('TEMPLATE','MLS_TEMPLATE')
720 and (a.dependency_flag is NULL or a.dependency_flag = 'P')
721 and sysdate between a.start_date and nvl(a.end_date,sysdate)
722 and rownum = 1;
723 end if;
724 elsif (l_special_template_case = '7') then
725 if ( xdo_columns_cntr = 2 ) then
726 EXECUTE IMMEDIATE
727 'select a.template_name, a.default_language, '||
728 ' a.default_territory, a.application_short_name, '||
729 ' a.template_code, a.template_type_code, '||
730 ' a.default_output_type '||
731 ' from xdo_lobs x, xdo_templates_vl a '||
732 ' where a.ds_app_short_name = :template_app_name '||
733 ' and a.data_source_code = :conc_prog_name '||
734 ' and a.template_code = :default_templ_code '||
735 ' and a.template_code = x.lob_code '||
736 ' and file_status = ''E'' '||
737 ' and lob_type in (''TEMPLATE'',''MLS_TEMPLATE'') '||
738 ' and (a.dependency_flag is NULL or a.dependency_flag = ''P'') '||
739 ' and sysdate between a.start_date and nvl(a.end_date,sysdate) '||
740 ' and rownum = 1 '
741 INTO template_name, iso_language, iso_territory,
742 template_app_name, template_code, format_type,
743 def_output_type
744 USING template_app_name, conc_prog_name, default_templ_code;
745 else
746 select a.template_name, a.default_language, a.default_territory,
747 a.application_short_name, a.template_code,
748 a.template_type_code
749 into template_name, iso_language, iso_territory,
750 template_app_name, template_code, format_type
751 from xdo_lobs x, xdo_templates_vl a
752 where a.ds_app_short_name = template_app_name
753 and a.data_source_code = conc_prog_name
754 and a.template_code = default_templ_code
758 and (a.dependency_flag is NULL or a.dependency_flag = 'P')
755 and a.template_code = x.lob_code
756 and file_status = 'E'
757 and lob_type in ('TEMPLATE','MLS_TEMPLATE')
759 and sysdate between a.start_date and nvl(a.end_date,sysdate)
760 and rownum = 1;
761 end if;
762
763 elsif (l_special_template_case = '10') then
764 if (default_templ_code = NULL) then
765 -- Run for no default template
766 if ( xdo_columns_cntr = 2 ) then
767 EXECUTE IMMEDIATE
768 'select x.template_name, a.default_language, '||
769 ' a.default_territory, a.application_short_name, '||
770 ' a.template_code, a.template_type_code, '||
771 ' a.default_output_type '||
772 ' from xdo_templates_b a, xdo_templates_vl x '||
773 ' where a.ds_app_short_name = :prog_app_name '||
774 ' and a.data_source_code = :conc_prog_name '||
775 ' and a.template_code = x.template_code '||
776 ' and a.template_status = ''E'' '||
777 ' and (a.dependency_flag is NULL or a.dependency_flag = ''P'') '||
778 ' and sysdate between a.start_date and nvl(a.end_date,sysdate) '||
779 ' and rownum = 1 '
780 INTO template_name, iso_language, iso_territory, template_app_name,
781 template_code, format_type, def_output_type
782 USING prog_app_name, conc_prog_name;
783 else
784 select x.template_name, a.default_language, a.default_territory,
785 a.application_short_name, a.template_code,
786 a.template_type_code
787 into template_name, iso_language, iso_territory,
788 template_app_name, template_code, format_type
789 from xdo_templates_b a, xdo_templates_vl x
790 where a.ds_app_short_name = prog_app_name
791 and a.data_source_code = conc_prog_name
792 and a.template_code = x.template_code
796 and rownum = 1;
793 and a.template_status = 'E'
794 and (a.dependency_flag is NULL or a.dependency_flag = 'P')
795 and sysdate between a.start_date and nvl(a.end_date,sysdate)
797 end if;
798 else
799 if ( xdo_columns_cntr = 2 ) then
800 EXECUTE IMMEDIATE
801 'select x.template_name, a.default_language, '||
802 ' a.default_territory, a.application_short_name, '||
803 ' a.template_code, a.template_type_code, '||
804 ' a.default_output_type '||
805 ' from xdo_templates_b a, xdo_templates_vl x '||
806 ' where a.ds_app_short_name = :prog_app_name '||
807 ' and a.data_source_code = :conc_prog_name '||
811 ' and (a.dependency_flag is NULL or a.dependency_flag = ''P'') '||
808 ' and a.template_code = :default_templ_code '||
809 ' and a.template_code = x.template_code '||
810 ' and a.template_status = ''E'' '||
812 ' and sysdate between a.start_date and nvl(a.end_date,sysdate) '||
813 ' and rownum = 1 '
814 INTO template_name, iso_language, iso_territory, template_app_name,
815 template_code, format_type, def_output_type
816 USING prog_app_name, conc_prog_name, default_templ_code;
817 else
818 select x.template_name, a.default_language, a.default_territory,
819 a.application_short_name, a.template_code,
820 a.template_type_code
821 into template_name, iso_language, iso_territory,
822 template_app_name, template_code, format_type
823 from xdo_templates_b a, xdo_templates_vl x
824 where a.ds_app_short_name = prog_app_name
825 and a.data_source_code = conc_prog_name
826 and a.template_code = default_templ_code
827 and a.template_code = x.template_code
828 and a.template_status = 'E'
829 and (a.dependency_flag is NULL or a.dependency_flag = 'P')
830 and sysdate between a.start_date and nvl(a.end_date,sysdate)
831 and rownum = 1;
832 end if;
833 end if;
834 end if;
835
836 if ( sql%rowcount > 0 ) then
840 into template_language
837 if ( iso_territory = '00' ) then
838 begin
839 select I.name
841 from fnd_iso_languages_vl I
842 where I.iso_language_2 = lower(iso_language);
843 exception
844 when NO_DATA_FOUND then
845 NULL;
846 end;
847 else
848 begin
849 select I.name||': '||T.territory_short_name
850 into template_language
851 from fnd_iso_languages_vl I, fnd_territories_vl T
852 where I.iso_language_2 = lower(iso_language)
853 and T.territory_code = upper(iso_territory);
854 exception
855 when NO_DATA_FOUND then
856 NULL;
857 end;
858 end if;
859 request_language := nls_lang;
860 template_obtained := 'Y';
861 else
862 template_obtained := 'N';
863 end if;
864
865 exception
866 when others then
867 NULL;
868 end fill_special_def_template;
869
870
871 -- NAME
872 -- find_the_format
873 -- Purpose
874 -- Called to obtain the correct lookup value for the associated tag.
875 --
876
877 procedure find_the_format(
878 format_type IN varchar2,
879 format IN OUT NOCOPY varchar2 ) is
880
881 begin
882
883 begin
884 select flv2.lookup_code
885 into format
886 from fnd_lookup_values_vl flv1, fnd_lookup_values_vl flv2
887 where flv1.lookup_type = 'XDO_TEMPLATE_TYPE'
888 and flv1.lookup_code = format_type
889 and flv2.lookup_type = flv1.tag;
890 exception
891 when NO_DATA_FOUND then
892 format := 'PDF';
893 when TOO_MANY_ROWS then
894 format := format_type;
895 end;
896
897 end;
898
899
900 -- NAME
901 -- get_iso_lang_and_terr
902 -- Purpose
903 -- Called to obtain the iso codes for a specific language and territory
904 --
905
906 procedure get_iso_lang_and_terr(
907 nls_lang IN varchar2,
908 nls_terr IN varchar2,
909 iso_lang IN OUT NOCOPY varchar2,
910 iso_terr IN OUT NOCOPY varchar2 ) is
911
912 begin
913 begin
914 SELECT lower(L1.iso_language), upper(L2.territory_code)
915 INTO iso_lang, iso_terr
916 FROM FND_LANGUAGES L1, FND_TERRITORIES_VL L2
917 WHERE L1.NLS_LANGUAGE = nls_lang
918 AND L2.NLS_TERRITORY = nls_terr
919 AND ROWNUM = 1;
920 exception
921 when NO_DATA_FOUND then
922 NULL;
923 end;
924 end get_iso_lang_and_terr;
925
926
927 -- NAME
928 -- get_def_iso_terr
929 -- Purpose
930 -- Called to obtain the default iso territory code for the specific lang
931 --
932
933 procedure get_def_iso_terr(
934 nls_lang IN varchar2,
935 def_iso_terr IN OUT NOCOPY varchar2 ) is
936
937 begin
938 begin
939 SELECT upper(L2.territory_code)
940 INTO def_iso_terr
941 FROM FND_LANGUAGES L1, FND_TERRITORIES_VL L2
942 WHERE L1.NLS_LANGUAGE = nls_lang
943 AND L1.NLS_TERRITORY = L2.NLS_TERRITORY
944 AND ROWNUM = 1;
945 exception
946 when NO_DATA_FOUND then
947 NULL;
948 end;
949 end get_def_iso_terr;
950
951
952 /* 7017250 */
953 -- NAME
954 -- get_template_info_options
955 -- Purpose
956 -- Called to obtain the info for templates when called from the Options
957 -- window and a new template has to be validated and setup in the
958 -- templates row
959 --
960 procedure get_template_info_options(
961 prog_app_id IN number,
962 prog_app_name IN varchar2,
963 conc_prog_name IN varchar2,
964 nls_lang IN varchar2,
965 nls_terr IN varchar2,
966 s_nls_lang IN varchar2,
967 s_nls_terr IN varchar2,
968 new_template_name IN varchar2,
969 template_obtained IN OUT NOCOPY varchar2,
970 template_name IN OUT NOCOPY varchar2,
971 template_language IN OUT NOCOPY varchar2,
972 format IN OUT NOCOPY varchar2,
973 request_language IN OUT NOCOPY varchar2,
974 iso_language IN OUT NOCOPY varchar2,
975 iso_territory IN OUT NOCOPY varchar2,
976 template_app_name IN OUT NOCOPY varchar2,
977 template_code IN OUT NOCOPY varchar2,
978 format_type IN OUT NOCOPY varchar2 ) is
979
980 def_iso_territory varchar2(2);
981 wo_xdo_iso_language varchar2(2);
982 wo_xdo_iso_territory varchar2(2);
983 terr_indep varchar2(1);
984 def_output_type varchar2(10);
985 l_po_output_type varchar2(10);
986 new_t_app_name varchar2(50);
987 new_template_code varchar2(80);
988
989 begin
990
991 -- Get the profile option for default output type override.
992 l_po_output_type := NULL;
993 FND_PROFILE.GET( 'FND_DEF_TEMPL_OUTPUT_TYPE', l_po_output_type );
994
995 get_iso_lang_and_terr( nls_lang, nls_terr,
996 wo_xdo_iso_language, wo_xdo_iso_territory );
997
998 begin
999 SELECT application_short_name, template_code
1003 and data_source_code = conc_prog_name
1000 into new_t_app_name, new_template_code
1001 from xdo_templates_vl T
1002 where ds_app_short_name = prog_app_name
1004 and template_name = new_template_name;
1005 exception
1006 when no_data_found then
1007 NULL;
1008 end;
1009
1010 default_templ_shrt_name_opt := new_t_app_name;
1011 default_templ_code_opt := new_template_code;
1012 template_obtained := 'N';
1013 -- 1st new template, Request Language
1014 if (def_template_check(
1015 prog_app_name, conc_prog_name, new_template_code,
1016 nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
1017 'N',
1018 template_obtained, template_name, template_language,
1019 format, request_language, iso_language, iso_territory,
1020 template_app_name, template_code,
1021 format_type, def_output_type ) ) then
1022 GOTO check_template_output_type;
1023 end if;
1024
1025
1026 -- 2nd new template, request language with territory independence
1027 if (def_template_check(
1028 prog_app_name, conc_prog_name, new_template_code,
1029 nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
1030 'Y',
1031 template_obtained, template_name, template_language,
1032 format, request_language, iso_language, iso_territory,
1033 template_app_name, template_code,
1034 format_type, def_output_type ) ) then
1035 GOTO check_template_output_type;
1036 end if;
1037
1038
1039
1040 -- 2-A Request language with default territory
1041 get_def_iso_terr(nls_lang, def_iso_territory);
1042 if (def_template_check(
1043 prog_app_name, conc_prog_name, new_template_code,
1044 nls_lang, wo_xdo_iso_language, def_iso_territory,
1045 'N',
1046 template_obtained, template_name, template_language,
1047 format, request_language, iso_language, iso_territory,
1048 template_app_name, template_code,
1049 format_type, def_output_type ) ) then
1050 GOTO check_template_output_type;
1051 end if;
1052
1053
1054 l_special_template_case := '2B';
1055 -- 2-B Request language,default template, with first row for templates
1056 if (def_template_check(
1057 prog_app_name, conc_prog_name, new_template_code,
1058 nls_lang, wo_xdo_iso_language, def_iso_territory,
1059 'N',
1060 template_obtained, template_name, template_language,
1061 format, request_language, iso_language, iso_territory,
1062 template_app_name, template_code,
1063 format_type, def_output_type ) ) then
1064 GOTO check_template_output_type;
1065 end if;
1066
1067
1068
1069 -- 5th new template, session language/terr; then no terr
1070 get_iso_lang_and_terr( s_nls_lang, s_nls_terr,
1071 wo_xdo_iso_language, wo_xdo_iso_territory );
1072
1073 if (def_template_check(
1074 prog_app_name, conc_prog_name, new_template_code,
1075 s_nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
1076 'N',
1077 template_obtained, template_name, template_language,
1078 format, request_language, iso_language, iso_territory,
1079 template_app_name, template_code,
1080 format_type, def_output_type ) ) then
1081 request_language := nls_lang;
1082 GOTO check_template_output_type;
1083 end if;
1084
1085
1086
1087 -- 6th new template, session language with territory independence
1088 if (def_template_check(
1089 prog_app_name, conc_prog_name, new_template_code,
1090 s_nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
1091 'Y',
1092 template_obtained, template_name, template_language,
1093 format, request_language, iso_language, iso_territory,
1094 template_app_name, template_code,
1095 format_type, def_output_type ) ) then
1096 request_language := nls_lang;
1097 GOTO check_template_output_type;
1098 end if;
1099
1100
1101
1102 l_special_template_case := '7';
1103 -- 7th Grab any default temp default language and territory
1104 if (def_template_check(
1105 prog_app_name, conc_prog_name, new_template_code,
1106 nls_lang, wo_xdo_iso_language, def_iso_territory,
1107 'N',
1108 template_obtained, template_name, template_language,
1109 format, request_language, iso_language, iso_territory,
1110 template_app_name, template_code,
1111 format_type, def_output_type ) ) then
1112 GOTO check_template_output_type;
1113 end if;
1114
1115
1116
1117 l_special_template_case := '10';
1118 -- 10th Final chk No new Template, get template from xdo_templates_b
1119 if (def_template_check(
1120 prog_app_name, conc_prog_name, new_template_code,
1121 nls_lang, wo_xdo_iso_language, def_iso_territory,
1122 'N',
1123 template_obtained, template_name, template_language,
1124 format, request_language, iso_language, iso_territory,
1125 template_app_name, template_code,
1126 format_type, def_output_type ) ) then
1127 GOTO check_template_output_type;
1128 end if;
1129
1130 << check_template_output_type >>
1131
1132 if ( template_obtained = 'Y' ) then
1133
1134 -- check if new column for default output type is present
1135 if ( xdo_columns_cntr = 2 ) then
1136 -- Default output type on template definition has priority
1140 -- Set the format to the value set by fnd_lookup_values_vl table
1137 if ( def_output_type is NULL ) then
1138 -- Check if profile option is set and use instead
1139 if (l_po_output_type is NULL) then
1141 find_the_format(format_type, format);
1142 else
1143 format := l_po_output_type;
1144 end if;
1145 else
1146 format := def_output_type;
1147 end if;
1148 else
1149 -- Check if profile option is set and use instead
1150 if (l_po_output_type is NULL) then
1151 -- Set the format to the value set by fnd_lookup_values_vl table
1152 find_the_format(format_type, format);
1153 else
1154 format := l_po_output_type;
1155 end if;
1156 end if;
1157 end if;
1158
1159 default_templ_shrt_name_opt := NULL;
1160 default_templ_code_opt := NULL;
1161
1162 end get_template_info_options;
1163
1164
1165 -- NAME
1166 -- def_template_check
1167 -- Purpose
1168 -- Setup to call proc data, return true if template is obtained
1169 --
1170
1171 function def_template_check
1172 (
1173 prog_app_name IN varchar2,
1174 conc_prog_name IN varchar2,
1175 def_templ_code IN varchar2,
1176 nls_lang IN varchar2,
1177 iso_lang IN varchar2,
1178 iso_terr IN varchar2,
1179 terr_indep IN varchar2,
1180 template_obtained IN OUT NOCOPY varchar2,
1181 template_name IN OUT NOCOPY varchar2,
1182 template_language IN OUT NOCOPY varchar2,
1183 format IN OUT NOCOPY varchar2,
1184 request_language IN OUT NOCOPY varchar2,
1185 iso_language IN OUT NOCOPY varchar2,
1186 iso_territory IN OUT NOCOPY varchar2,
1187 template_app_name IN OUT NOCOPY varchar2,
1188 template_code IN OUT NOCOPY varchar2,
1189 format_type IN OUT NOCOPY varchar2,
1190 def_output_type IN OUT NOCOPY varchar2 )
1191 return boolean is
1192
1193 begin
1194
1195 -- The default is checked to not allow the queries to run if these are null
1196 if ( (default_templ_code is not null and
1197 default_templ_shrt_name is not null) or
1198 (default_templ_code_opt is not null and
1199 default_templ_shrt_name_opt is not null) ) then
1200
1201 -- The special cases are queries that have where clauses that require
1202 -- a slight difference than the standard clauses used in the else.
1203 if (l_special_template_case in ('2B', '7', '10') ) then
1204
1205 fill_special_def_template(
1206 prog_app_name, conc_prog_name, def_templ_code, nls_lang,
1207 iso_lang, iso_terr, terr_indep, template_obtained,
1208 template_name, template_language, format, request_language,
1209 iso_language, iso_territory, template_app_name, template_code,
1210 format_type, def_output_type );
1211 else
1212 fill_default_template(
1213 prog_app_name, conc_prog_name, def_templ_code, nls_lang,
1214 iso_lang, iso_terr, terr_indep, template_obtained,
1215 template_name, template_language, format, request_language,
1216 iso_language, iso_territory, template_app_name, template_code,
1217 format_type, def_output_type );
1218 end if;
1219
1220 l_special_template_case := NULL;
1221 if (template_obtained = 'Y') then
1222 return(TRUE);
1223 else
1224 return(FALSE);
1225 end if;
1226 else
1227 return(FALSE);
1228 end if;
1229
1230 end def_template_check;
1231
1232
1233 -- NAME
1234 -- no_def_template_check
1235 -- Purpose
1236 -- Setup to call proc data, return true if template is obtained
1237 --
1238
1239 function no_def_template_check
1240
1241 (
1242 prog_app_name IN varchar2,
1243 conc_prog_name IN varchar2,
1244 nls_lang IN varchar2,
1245 iso_lang IN varchar2,
1246 iso_terr IN varchar2,
1247 terr_indep IN varchar2,
1248 template_obtained IN OUT NOCOPY varchar2,
1249 template_name IN OUT NOCOPY varchar2,
1250 template_language IN OUT NOCOPY varchar2,
1251 format IN OUT NOCOPY varchar2,
1252 request_language IN OUT NOCOPY varchar2,
1253 iso_language IN OUT NOCOPY varchar2,
1254 iso_territory IN OUT NOCOPY varchar2,
1255 template_app_name IN OUT NOCOPY varchar2,
1256 template_code IN OUT NOCOPY varchar2,
1257 format_type IN OUT NOCOPY varchar2,
1258 def_output_type IN OUT NOCOPY varchar2 )
1259 return boolean is
1260
1261 begin
1262
1263 -- The special cases are queries that have where clauses that require
1264 -- a slight difference than the standard clauses used in the else.
1265 if (l_special_template_case in ('4B', '10') ) then
1266
1267 fill_special_def_template(
1268 prog_app_name, conc_prog_name, 'NULL', nls_lang,
1269 iso_lang, iso_terr, terr_indep, template_obtained,
1270 template_name, template_language, format, request_language,
1271 iso_language, iso_territory, template_app_name, template_code,
1272 format_type, def_output_type );
1273 else
1274 fill_no_def_template(
1275 prog_app_name, conc_prog_name, nls_lang, iso_lang, iso_terr,
1276 terr_indep, template_obtained, template_name, template_language,
1277 format, request_language, iso_language, iso_territory,
1278 template_app_name, template_code,
1279 format_type, def_output_type );
1280 end if;
1281
1282 l_special_template_case := NULL;
1283 if (template_obtained = 'Y') then
1284 return(TRUE);
1285 else
1286 return(FALSE);
1287 end if;
1288
1289 end no_def_template_check;
1290
1291
1292
1293 BEGIN
1294
1295 -- Set it and Forget It! -- Ronco
1296 -- Obtain the count if the default_output_type column
1297 -- exist in the xdo tables.
1298 select count(*) into xdo_columns_cntr from sys.all_tab_columns
1299 where table_name in ('XDO_TEMPLATES_B', 'XDO_TEMPLATES_VL')
1300 and column_name = 'DEFAULT_OUTPUT_TYPE';
1301
1302
1303 end FND_CONC_TEMPLATES;