4 -- Package
1 package body FND_CONC_TEMPLATES as
2 /* $Header: AFCPTPLB.pls 120.11.12020000.3 2012/12/04 15:28:34 ckclark ship $ */
3 --
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 xdo_templates_b.template_code%TYPE;
19 default_templ_shrt_name_opt fnd_application.application_short_name%TYPE;
20 default_templ_code_opt xdo_templates_b.template_code%TYPE;
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
115
112 GOTO check_template_output_type;
113 end if;
114
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',
168 format_type, def_output_type ) ) then
165 template_obtained, template_name, template_language,
166 format, request_language, iso_language, iso_territory,
167 template_app_name, template_code,
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(
222 template_obtained, template_name, template_language,
219 prog_app_name, conc_prog_name,
220 nls_lang, wo_xdo_iso_language, def_iso_territory,
221 'N',
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, '||
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 '||
356 ' and a.template_code = x.lob_code '||
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
460 -- Called if default template is set for defined program
461 -- in fnd_concurrent_program
462 --
463
464 procedure fill_default_template(
465 prog_app_name IN varchar2,
466 conc_prog_name IN varchar2,
467 def_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 = :def_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, def_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 = def_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 = :def_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) '||
558 ' and rownum = 1 '
559 INTO template_name, template_language, request_language,
560 iso_language, iso_territory, template_app_name, template_code,
561 format_type, def_output_type
562 USING prog_app_name, conc_prog_name, def_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 = def_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')
587 end if;
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;
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 def_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,
627 begin
624 format_type IN OUT NOCOPY varchar2,
625 def_output_type IN OUT NOCOPY varchar2 ) is
626
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 = :default_templ_shrt_name '||
638 ' and a.data_source_code = :conc_prog_name '||
639 ' and a.template_code = :def_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 default_templ_shrt_name, conc_prog_name, def_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 = default_templ_shrt_name
664 and a.data_source_code = conc_prog_name
665 and a.template_code = def_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'
722 and rownum = 1;
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)
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 = :default_templ_shrt_name '||
733 ' and a.data_source_code = :conc_prog_name '||
734 ' and a.template_code = :def_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 default_templ_shrt_name, conc_prog_name, def_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 = default_templ_shrt_name
753 and a.data_source_code = conc_prog_name
754 and a.template_code = def_templ_code
755 and a.template_code = x.lob_code
756 and file_status = 'E'
757 and lob_type in ('TEMPLATE','MLS_TEMPLATE')
758 and (a.dependency_flag is NULL or a.dependency_flag = 'P')
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 (def_templ_code is NULL) then
765
769 'select x.template_name, a.default_language, '||
766 -- Run for no default template
767 if ( xdo_columns_cntr = 2 ) then
768 EXECUTE IMMEDIATE
770 ' a.default_territory, a.application_short_name, '||
771 ' a.template_code, a.template_type_code, '||
772 ' a.default_output_type '||
773 ' from xdo_templates_b a, xdo_templates_vl x '||
774 ' where a.ds_app_short_name = :prog_app_name '||
775 ' and a.data_source_code = :conc_prog_name '||
776 ' and a.template_code = x.template_code '||
777 ' and a.template_status = ''E'' '||
778 ' and (a.dependency_flag is NULL or a.dependency_flag = ''P'') '||
779 ' and sysdate between a.start_date and nvl(a.end_date,sysdate) '||
780 ' and rownum = 1 '
781 INTO template_name, iso_language, iso_territory, template_app_name,
782 template_code, format_type, def_output_type
783 USING prog_app_name, conc_prog_name;
784 else
785 select x.template_name, a.default_language, a.default_territory,
786 a.application_short_name, a.template_code,
787 a.template_type_code
788 into template_name, iso_language, iso_territory,
789 template_app_name, template_code, format_type
790 from xdo_templates_b a, xdo_templates_vl x
791 where a.ds_app_short_name = prog_app_name
792 and a.data_source_code = conc_prog_name
793 and a.template_code = x.template_code
794 and a.template_status = 'E'
795 and (a.dependency_flag is NULL or a.dependency_flag = 'P')
796 and sysdate between a.start_date and nvl(a.end_date,sysdate)
797 and rownum = 1;
798 end if;
799 else
800
801 if ( xdo_columns_cntr = 2 ) then
802 EXECUTE IMMEDIATE
803 'select x.template_name, a.default_language, '||
804 ' a.default_territory, a.application_short_name, '||
805 ' a.template_code, a.template_type_code, '||
806 ' a.default_output_type '||
807 ' from xdo_templates_b a, xdo_templates_vl x '||
808 ' where a.ds_app_short_name = :prog_app_name '||
809 ' and a.data_source_code = :conc_prog_name '||
810 ' and a.template_code = :def_templ_code '||
811 ' and a.template_code = x.template_code '||
812 ' and a.template_status = ''E'' '||
813 ' and (a.dependency_flag is NULL or a.dependency_flag = ''P'') '||
814 ' and sysdate between a.start_date and nvl(a.end_date,sysdate) '||
815 ' and rownum = 1 '
816 INTO template_name, iso_language, iso_territory, template_app_name,
817 template_code, format_type, def_output_type
818 USING prog_app_name, conc_prog_name, def_templ_code;
819 else
820 select x.template_name, a.default_language, a.default_territory,
821 a.application_short_name, a.template_code,
822 a.template_type_code
823 into template_name, iso_language, iso_territory,
824 template_app_name, template_code, format_type
825 from xdo_templates_b a, xdo_templates_vl x
826 where a.ds_app_short_name = prog_app_name
827 and a.data_source_code = conc_prog_name
828 and a.template_code = def_templ_code
829 and a.template_code = x.template_code
830 and a.template_status = 'E'
831 and (a.dependency_flag is NULL or a.dependency_flag = 'P')
832 and sysdate between a.start_date and nvl(a.end_date,sysdate)
833 and rownum = 1;
834 end if;
835 end if;
836 end if;
837
838 if ( sql%rowcount > 0 ) then
842 select I.name
839
840 if ( iso_territory = '00' ) then
841 begin
843 into template_language
844 from fnd_iso_languages_vl I
845 where I.iso_language_2 = lower(iso_language);
846 exception
847 when NO_DATA_FOUND then
848 NULL;
849 end;
850 else
851 begin
852 select I.name||': '||T.territory_short_name
853 into template_language
854 from fnd_iso_languages_vl I, fnd_territories_vl T
855 where I.iso_language_2 = lower(iso_language)
856 and T.territory_code = upper(iso_territory);
857 exception
858 when NO_DATA_FOUND then
859 NULL;
860 end;
861 end if;
862 request_language := nls_lang;
863 template_obtained := 'Y';
864 else
865 template_obtained := 'N';
866 end if;
867
868 exception
869 when others then
870 NULL;
871 end fill_special_def_template;
872
873
874 -- NAME
875 -- find_the_format
876 -- Purpose
877 -- Called to obtain the correct lookup value for the associated tag.
878 --
879
880 procedure find_the_format(
881 format_type IN varchar2,
882 format IN OUT NOCOPY varchar2 ) is
883
884 begin
885
886 begin
887 select flv2.lookup_code
888 into format
889 from fnd_lookup_values_vl flv1, fnd_lookup_values_vl flv2
890 where flv1.lookup_type = 'XDO_TEMPLATE_TYPE'
891 and flv1.lookup_code = format_type
892 and flv2.lookup_type = flv1.tag;
893 exception
894 when NO_DATA_FOUND then
895 format := 'PDF';
896 when TOO_MANY_ROWS then
897 format := format_type;
898 end;
899
900 end;
901
902
903 -- NAME
904 -- get_iso_lang_and_terr
905 -- Purpose
906 -- Called to obtain the iso codes for a specific language and territory
907 --
908
909 procedure get_iso_lang_and_terr(
910 nls_lang IN varchar2,
911 nls_terr IN varchar2,
912 iso_lang IN OUT NOCOPY varchar2,
913 iso_terr IN OUT NOCOPY varchar2 ) is
914
915 begin
916 begin
917 SELECT lower(L1.iso_language), upper(L2.territory_code)
918 INTO iso_lang, iso_terr
919 FROM FND_LANGUAGES L1, FND_TERRITORIES_VL L2
920 WHERE L1.NLS_LANGUAGE = nls_lang
921 AND L2.NLS_TERRITORY = nls_terr
922 AND ROWNUM = 1;
923 exception
924 when NO_DATA_FOUND then
925 NULL;
926 end;
927 end get_iso_lang_and_terr;
928
929
930 -- NAME
931 -- get_def_iso_terr
932 -- Purpose
933 -- Called to obtain the default iso territory code for the specific lang
934 --
935
936 procedure get_def_iso_terr(
937 nls_lang IN varchar2,
938 def_iso_terr IN OUT NOCOPY varchar2 ) is
939
940 begin
941 begin
942 SELECT upper(L2.territory_code)
943 INTO def_iso_terr
944 FROM FND_LANGUAGES L1, FND_TERRITORIES_VL L2
945 WHERE L1.NLS_LANGUAGE = nls_lang
946 AND L1.NLS_TERRITORY = L2.NLS_TERRITORY
947 AND ROWNUM = 1;
948 exception
949 when NO_DATA_FOUND then
950 NULL;
951 end;
952 end get_def_iso_terr;
953
954
955 /* 7017250 */
956 -- NAME
957 -- get_template_info_options
958 -- Purpose
959 -- Called to obtain the info for templates when called from the Options
960 -- window and a new template has to be validated and setup in the
961 -- templates row
962 --
963 procedure get_template_info_options(
964 prog_app_id IN number,
965 prog_app_name IN varchar2,
966 conc_prog_name IN varchar2,
967 nls_lang IN varchar2,
968 nls_terr IN varchar2,
969 s_nls_lang IN varchar2,
970 s_nls_terr IN varchar2,
971 new_template_name IN varchar2,
975 format IN OUT NOCOPY varchar2,
972 template_obtained IN OUT NOCOPY varchar2,
973 template_name IN OUT NOCOPY varchar2,
974 template_language IN OUT NOCOPY varchar2,
976 request_language IN OUT NOCOPY varchar2,
977 iso_language IN OUT NOCOPY varchar2,
978 iso_territory IN OUT NOCOPY varchar2,
979 template_app_name IN OUT NOCOPY varchar2,
980 template_code IN OUT NOCOPY varchar2,
981 format_type IN OUT NOCOPY varchar2 ) is
982
983 def_iso_territory varchar2(2);
984 wo_xdo_iso_language varchar2(2);
985 wo_xdo_iso_territory varchar2(2);
986 terr_indep varchar2(1);
987 def_output_type varchar2(10);
988 l_po_output_type varchar2(10);
989 new_t_app_name varchar2(50);
990 new_template_code varchar2(80);
991
992 begin
993
994 -- Get the profile option for default output type override.
995 l_po_output_type := NULL;
996 FND_PROFILE.GET( 'FND_DEF_TEMPL_OUTPUT_TYPE', l_po_output_type );
997
998 get_iso_lang_and_terr( nls_lang, nls_terr,
999 wo_xdo_iso_language, wo_xdo_iso_territory );
1000
1001 begin
1002 SELECT application_short_name, template_code
1003 into new_t_app_name, new_template_code
1004 from xdo_templates_vl T
1005 where ds_app_short_name = prog_app_name
1006 and data_source_code = conc_prog_name
1007 and template_name = new_template_name;
1008 exception
1009 when no_data_found then
1010 NULL;
1011 end;
1012
1013 default_templ_shrt_name_opt := new_t_app_name;
1014 default_templ_code_opt := new_template_code;
1015 template_obtained := 'N';
1016 -- 1st new template, Request Language
1017 if (def_template_check(
1018 prog_app_name, conc_prog_name, new_template_code,
1019 nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
1020 'N',
1021 template_obtained, template_name, template_language,
1022 format, request_language, iso_language, iso_territory,
1023 template_app_name, template_code,
1024 format_type, def_output_type ) ) then
1025 GOTO check_template_output_type;
1026 end if;
1027
1028
1029 -- 2nd new template, request language with territory independence
1030 if (def_template_check(
1031 prog_app_name, conc_prog_name, new_template_code,
1032 nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
1033 'Y',
1034 template_obtained, template_name, template_language,
1035 format, request_language, iso_language, iso_territory,
1036 template_app_name, template_code,
1037 format_type, def_output_type ) ) then
1038 GOTO check_template_output_type;
1039 end if;
1040
1041
1042
1043 -- 2-A Request language with default territory
1044 get_def_iso_terr(nls_lang, def_iso_territory);
1045 if (def_template_check(
1046 prog_app_name, conc_prog_name, new_template_code,
1047 nls_lang, wo_xdo_iso_language, def_iso_territory,
1048 'N',
1049 template_obtained, template_name, template_language,
1050 format, request_language, iso_language, iso_territory,
1051 template_app_name, template_code,
1052 format_type, def_output_type ) ) then
1053 GOTO check_template_output_type;
1054 end if;
1055
1056
1057 l_special_template_case := '2B';
1058 -- 2-B Request language,default template, with first row for templates
1059 if (def_template_check(
1060 prog_app_name, conc_prog_name, new_template_code,
1061 nls_lang, wo_xdo_iso_language, def_iso_territory,
1062 'N',
1063 template_obtained, template_name, template_language,
1064 format, request_language, iso_language, iso_territory,
1065 template_app_name, template_code,
1066 format_type, def_output_type ) ) then
1067 GOTO check_template_output_type;
1068 end if;
1069
1070
1071
1072 -- 5th new template, session language/terr; then no terr
1073 get_iso_lang_and_terr( s_nls_lang, s_nls_terr,
1074 wo_xdo_iso_language, wo_xdo_iso_territory );
1075
1076 if (def_template_check(
1077 prog_app_name, conc_prog_name, new_template_code,
1078 s_nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
1079 'N',
1080 template_obtained, template_name, template_language,
1081 format, request_language, iso_language, iso_territory,
1082 template_app_name, template_code,
1083 format_type, def_output_type ) ) then
1084 request_language := nls_lang;
1085 GOTO check_template_output_type;
1086 end if;
1087
1088
1089
1090 -- 6th new template, session language with territory independence
1091 if (def_template_check(
1092 prog_app_name, conc_prog_name, new_template_code,
1093 s_nls_lang, wo_xdo_iso_language, wo_xdo_iso_territory,
1094 'Y',
1095 template_obtained, template_name, template_language,
1096 format, request_language, iso_language, iso_territory,
1097 template_app_name, template_code,
1098 format_type, def_output_type ) ) then
1099 request_language := nls_lang;
1100 GOTO check_template_output_type;
1101 end if;
1102
1103
1104
1105 l_special_template_case := '7';
1106 -- 7th Grab any default temp default language and territory
1107 if (def_template_check(
1111 template_obtained, template_name, template_language,
1108 prog_app_name, conc_prog_name, new_template_code,
1109 nls_lang, wo_xdo_iso_language, def_iso_territory,
1110 'N',
1112 format, request_language, iso_language, iso_territory,
1113 template_app_name, template_code,
1114 format_type, def_output_type ) ) then
1115 GOTO check_template_output_type;
1116 end if;
1117
1118
1119
1120 l_special_template_case := '10';
1121 -- 10th Final chk No new Template, get template from xdo_templates_b
1122 if (def_template_check(
1123 prog_app_name, conc_prog_name, new_template_code,
1124 nls_lang, wo_xdo_iso_language, def_iso_territory,
1125 'N',
1126 template_obtained, template_name, template_language,
1127 format, request_language, iso_language, iso_territory,
1128 template_app_name, template_code,
1129 format_type, def_output_type ) ) then
1130 GOTO check_template_output_type;
1131 end if;
1132
1133 << check_template_output_type >>
1134
1135 if ( template_obtained = 'Y' ) then
1136
1137 -- check if new column for default output type is present
1138 if ( xdo_columns_cntr = 2 ) then
1139 -- Default output type on template definition has priority
1140 if ( def_output_type is NULL ) then
1141 -- Check if profile option is set and use instead
1142 if (l_po_output_type is NULL) then
1143 -- Set the format to the value set by fnd_lookup_values_vl table
1144 find_the_format(format_type, format);
1145 else
1146 format := l_po_output_type;
1147 end if;
1148 else
1149 format := def_output_type;
1150 end if;
1151 else
1152 -- Check if profile option is set and use instead
1153 if (l_po_output_type is NULL) then
1154 -- Set the format to the value set by fnd_lookup_values_vl table
1155 find_the_format(format_type, format);
1156 else
1157 format := l_po_output_type;
1158 end if;
1159 end if;
1160 end if;
1161
1162 default_templ_shrt_name_opt := NULL;
1163 default_templ_code_opt := NULL;
1164
1165 end get_template_info_options;
1166
1167
1168 -- NAME
1169 -- def_template_check
1170 -- Purpose
1171 -- Setup to call proc data, return true if template is obtained
1172 --
1173
1174 function def_template_check
1175 (
1176 prog_app_name IN varchar2,
1177 conc_prog_name IN varchar2,
1178 def_templ_code IN varchar2,
1179 nls_lang IN varchar2,
1180 iso_lang IN varchar2,
1181 iso_terr IN varchar2,
1182 terr_indep IN varchar2,
1183 template_obtained IN OUT NOCOPY varchar2,
1184 template_name IN OUT NOCOPY varchar2,
1185 template_language IN OUT NOCOPY varchar2,
1186 format IN OUT NOCOPY varchar2,
1187 request_language IN OUT NOCOPY varchar2,
1188 iso_language IN OUT NOCOPY varchar2,
1189 iso_territory IN OUT NOCOPY varchar2,
1190 template_app_name IN OUT NOCOPY varchar2,
1191 template_code IN OUT NOCOPY varchar2,
1192 format_type IN OUT NOCOPY varchar2,
1193 def_output_type IN OUT NOCOPY varchar2 )
1194 return boolean is
1195
1196 begin
1197
1198 -- The default is checked to not allow the queries to run if these are null
1199 if ( (default_templ_code is not null and
1200 default_templ_shrt_name is not null) or
1201 (default_templ_code_opt is not null and
1202 default_templ_shrt_name_opt is not null) ) then
1203
1204 -- The special cases are queries that have where clauses that require
1205 -- a slight difference than the standard clauses used in the else.
1206 if (l_special_template_case in ('2B', '7', '10') ) then
1207
1208 fill_special_def_template(
1209 prog_app_name, conc_prog_name, def_templ_code, nls_lang,
1210 iso_lang, iso_terr, terr_indep, template_obtained,
1211 template_name, template_language, format, request_language,
1212 iso_language, iso_territory, template_app_name, template_code,
1213 format_type, def_output_type );
1214 else
1215 fill_default_template(
1216 prog_app_name, conc_prog_name, def_templ_code, nls_lang,
1217 iso_lang, iso_terr, terr_indep, template_obtained,
1218 template_name, template_language, format, request_language,
1219 iso_language, iso_territory, template_app_name, template_code,
1220 format_type, def_output_type );
1221 end if;
1222
1223 l_special_template_case := NULL;
1224 if (template_obtained = 'Y') then
1225 return(TRUE);
1226 else
1227 return(FALSE);
1228 end if;
1229 else
1230 return(FALSE);
1231 end if;
1232
1233 end def_template_check;
1234
1235
1236 -- NAME
1237 -- no_def_template_check
1238 -- Purpose
1239 -- Setup to call proc data, return true if template is obtained
1240 --
1241
1242 function no_def_template_check
1243
1244 (
1245 prog_app_name IN varchar2,
1246 conc_prog_name IN varchar2,
1247 nls_lang IN varchar2,
1248 iso_lang IN varchar2,
1249 iso_terr IN varchar2,
1253 template_language IN OUT NOCOPY varchar2,
1250 terr_indep IN varchar2,
1251 template_obtained IN OUT NOCOPY varchar2,
1252 template_name IN OUT NOCOPY varchar2,
1254 format IN OUT NOCOPY varchar2,
1255 request_language IN OUT NOCOPY varchar2,
1256 iso_language IN OUT NOCOPY varchar2,
1257 iso_territory IN OUT NOCOPY varchar2,
1258 template_app_name IN OUT NOCOPY varchar2,
1259 template_code IN OUT NOCOPY varchar2,
1260 format_type IN OUT NOCOPY varchar2,
1261 def_output_type IN OUT NOCOPY varchar2 )
1262 return boolean is
1263
1264 begin
1265
1266 -- The special cases are queries that have where clauses that require
1267 -- a slight difference than the standard clauses used in the else.
1268 if (l_special_template_case in ('4B') ) then
1269
1270 fill_special_def_template(
1271 prog_app_name, conc_prog_name, NULL, nls_lang,
1272 iso_lang, iso_terr, terr_indep, template_obtained,
1273 template_name, template_language, format, request_language,
1274 iso_language, iso_territory, template_app_name, template_code,
1275 format_type, def_output_type );
1276 elsif (l_special_template_case in ('10') ) then
1277 if ( default_templ_code is not null) then
1278 fill_special_def_template(
1279 default_templ_shrt_name, conc_prog_name, default_templ_code, nls_lang,
1280 iso_lang, iso_terr, terr_indep, template_obtained,
1281 template_name, template_language, format, request_language,
1282 iso_language, iso_territory, template_app_name, template_code,
1283 format_type, def_output_type );
1284 else
1285
1286 fill_special_def_template(
1287 prog_app_name, conc_prog_name, NULL, nls_lang,
1288 iso_lang, iso_terr, terr_indep, template_obtained,
1289 template_name, template_language, format, request_language,
1290 iso_language, iso_territory, template_app_name, template_code,
1291 format_type, def_output_type );
1292 end if;
1293 else
1294 fill_no_def_template(
1295 prog_app_name, conc_prog_name, nls_lang, iso_lang, iso_terr,
1296 terr_indep, template_obtained, template_name, template_language,
1297 format, request_language, iso_language, iso_territory,
1298 template_app_name, template_code,
1299 format_type, def_output_type );
1300 end if;
1301
1302 l_special_template_case := NULL;
1303 if (template_obtained = 'Y') then
1304 return(TRUE);
1305 else
1306 return(FALSE);
1307 end if;
1308
1309 end no_def_template_check;
1310
1311
1312
1313
1314 BEGIN
1315
1316 -- Set it and Forget It! -- Ronco
1317 -- Obtain the count if the default_output_type column
1318 -- exist in the xdo tables.
1319 -- Bug15935679 NZDT Logical Column Fix for 12.2 (backward compatible)
1320 select count(*) into xdo_columns_cntr
1321 from (
1322 select syn.table_name, col.column_name
1323 from user_synonyms syn, all_tab_columns col
1324 where syn.synonym_name in ('XDO_TEMPLATES_B', 'XDO_TEMPLATES_VL')
1325 and col.column_name = 'DEFAULT_OUTPUT_TYPE'
1326 and col.owner = syn.table_owner
1327 and col.table_name = syn.table_name
1328 UNION
1329 select col.table_name, col.column_name
1330 from user_tab_columns col
1331 where col.table_name in ('XDO_TEMPLATES_B', 'XDO_TEMPLATES_VL')
1332 and col.column_name = 'DEFAULT_OUTPUT_TYPE'
1333 );
1334
1335
1336 end FND_CONC_TEMPLATES;
[Home] [Help]