Query for Templates details
SELECT xtt.template_name "Template Name",
xtb.template_code "Template Code",
fat.application_name "Application Name",
xddt.data_source_name "Data Definition",
xtb.template_type_code "Template Type",
xtb.default_language "Language",
xtb.default_territory "Territory",
xl.file_name "File Name",
xl.file_data "File"
FROM xdo_templates_tl xtt,
xdo_templates_b xtb,
fnd_application_tl fat,
xdo_ds_definitions_tl xddt,
xdo_lobs xl
WHERE (xtt.template_name LIKE '%' OR xtt.template_name LIKE 'XX%')
AND xtt.LANGUAGE = 'US'
AND xtb.template_code = xtt.template_code
AND fat.application_id = xtb.application_id
AND fat.LANGUAGE = 'US'
AND xddt.data_source_code = xtb.data_source_code
AND xddt.LANGUAGE = 'US'
AND xl.lob_code = xtb.template_code
AND NVL (xl.program, 'X') <> 'RTF2XSLParser 5.6.3'
AND xl.LANGUAGE = xtb.default_language
AND xl.territory = xtb.default_territory;
Query for Data Definition
/* Formatted on 4/10/2014 8:49:20 AM (QP5 v5.114.809.3010) */
SELECT xddt.data_source_name "Data Source Name",
xddb.data_source_code "Code",
fat.application_name "Application Name"
FROM xdo_ds_definitions_b xddb,
xdo_ds_definitions_tl xddt,
fnd_application fa,
fnd_application_tl fat
WHERE xddb.data_source_code LIKE 'XX%'
AND xddt.data_source_code = xddb.data_source_code
AND xddt.LANGUAGE = 'US'
AND fa.application_short_name = xddb.application_short_name
AND fat.application_id = fa.application_id
AND fat.LANGUAGE = 'US'
Query for Data Definition and Template Detail
SELECT templatesvleo.template_id,
templatesvleo.application_id,
templatesvleo.application_short_name,
templatesvleo.template_code,
templatesvleo.ds_app_short_name,
templatesvleo.data_source_code,
templatesvleo.template_type_code,
templatesvleo.default_language,
templatesvleo.default_territory,
templatesvleo.template_status,
templatesvleo.use_alias_table,
templatesvleo.start_date,
templatesvleo.end_date,
templatesvleo.template_name,
templatesvleo.description,
templatesvleo.created_by,
templatesvleo.creation_date,
templatesvleo.last_updated_by,
templatesvleo.last_update_date,
templatesvleo.last_update_login,
templatesvleo.object_version_number,
templatesvleo.attribute_category,
templatesvleo.attribute1,
templatesvleo.attribute15,
templatesvleo.dependency_flag,
templatesvleo.mls_language,
templatesvleo.mls_territory,
templatesvleo.default_output_type,
(SELECT application_name
FROM fnd_application_vl
WHERE application_short_name =
templatesvleo.application_short_name)
application_name,
(SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'XDO_TEMPLATE_TYPE'
AND lookup_code = templatesvleo.template_type_code)
template_type,
(SELECT data_source_name
FROM xdo_ds_definitions_vl
WHERE data_source_code = templatesvleo.data_source_code
AND application_short_name =
templatesvleo.ds_app_short_name)
data_source_name,
(SELECT file_name
FROM xdo_lobs
WHERE ( ( lob_type = 'TEMPLATE'
AND xdo_file_type != 'RTF'
AND xdo_file_type = templatesvleo.template_type_code
AND xdo_lobs.LANGUAGE = templatesvleo.default_language
AND xdo_lobs.territory =
templatesvleo.default_territory)
OR (lob_type = 'TEMPLATE_SOURCE'
AND xdo_file_type IN ('RTF', '‘RTF - ETEXT')
AND xdo_lobs.LANGUAGE =
templatesvleo.default_language
AND xdo_lobs.territory =
templatesvleo.default_territory)
OR ( xdo_file_type = 'RTF'
AND lob_type = 'TEMPLATE_SOURCE'
AND LANGUAGE = templatesvleo.mls_language
AND territory = templatesvleo.mls_territory
AND EXISTS
(SELECT mls.lob_code
FROM xdo_lobs mls
WHERE mls.lob_type = 'MLS_TEMPLATE'
AND mls.lob_code =
templatesvleo.template_code
AND mls.application_short_name =
templatesvleo.application_short_name
AND mls.LANGUAGE =
templatesvleo.default_language
AND mls.territory =
templatesvleo.default_territory)
AND NOT EXISTS
(SELECT LOCAL.lob_code
FROM xdo_lobs LOCAL
WHERE LOCAL.lob_type = 'TEMPLATE_SOURCE'
AND LOCAL.lob_code =
templatesvleo.template_code
AND LOCAL.application_short_name =
templatesvleo.application_short_name
AND LOCAL.LANGUAGE =
templatesvleo.default_language
AND LOCAL.territory =
templatesvleo.default_territory)))
AND lob_code = templatesvleo.template_code
AND xdo_lobs.application_short_name =
templatesvleo.application_short_name)
default_template_file,
(SELECT file_name
FROM xdo_lobs
WHERE lob_type = 'TEMPLATE_SOURCE'
AND lob_code = templatesvleo.template_code
AND xdo_lobs.application_short_name =
templatesvleo.application_short_name
AND xdo_lobs.LANGUAGE = templatesvleo.mls_language
AND xdo_lobs.territory = templatesvleo.mls_territory)
mls_template_file,
(SELECT NAME
FROM fnd_iso_languages_vl
WHERE iso_language_2 = templatesvleo.default_language)
default_file_lang,
DECODE (templatesvleo.default_territory,
'00',
fndterritoriesvl.territory_short_name)
default_file_terr
FROM xdo_templates_vl templatesvleo,
fnd_application_vl fndapplicationvl,
fnd_territories_vl fndterritoriesvl
WHERE fndapplicationvl.application_short_name =
templatesvleo.application_short_name
AND fndterritoriesvl.territory_code(+) =
templatesvleo.default_territory
AND template_name = 'Template Name'
No comments:
Post a Comment