Oracle XML Publisher Queries

/* Formatted on 4/18/2013 9:53:37 AM (QP5 v5.114.809.3010) */
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'