Oracle XML Publisher Queries

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"