Oracle Apps Knowledge Sharing

Query to get XML Template, Data source and template file details in Oracle Apps R12

Test Instance: R12.2.5

Query:

SELECT   xtv.application_short_name,
         xtv.template_code,
         xtv.ds_app_short_name,
         xtv.data_source_code,
         xtv.template_type_code, 
         xtv.template_name,
         xtv.description,	
         fu.user_name created_by,		 
         (SELECT   application_name
            FROM   fnd_application_vl
           WHERE   application_short_name =
                      xtv.application_short_name)
            application_name,
         (SELECT   meaning
            FROM   fnd_lookups
           WHERE   lookup_type = 'XDO_TEMPLATE_TYPE'
                   AND lookup_code = xtv.template_type_code)
            template_type,
         (SELECT   data_source_name
            FROM   xdo_ds_definitions_vl
           WHERE   data_source_code = xtv.data_source_code
                   AND application_short_name =
                         xtv.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 = xtv.template_type_code
                      AND xdo_lobs.LANGUAGE = xtv.default_language
                      AND xdo_lobs.territory =
                            xtv.default_territory)
                    OR (lob_type = 'TEMPLATE_SOURCE'
                        AND xdo_file_type IN ('RTF', 'RTF - ETEXT')
                        AND xdo_lobs.LANGUAGE =
                              xtv.default_language
                        AND xdo_lobs.territory =
                              xtv.default_territory)
                    OR (    xdo_file_type = 'RTF'
                        AND lob_type = 'TEMPLATE_SOURCE'
                        AND LANGUAGE = xtv.mls_language
                        AND territory = xtv.mls_territory
                        AND EXISTS
                              (SELECT   mls.lob_code
                                 FROM   xdo_lobs mls
                                WHERE   mls.lob_type = 'MLS_TEMPLATE'
                                        AND mls.lob_code =
                                              xtv.template_code
                                        AND mls.application_short_name =
                                              xtv.application_short_name
                                        AND mls.LANGUAGE =
                                              xtv.default_language
                                        AND mls.territory =
                                              xtv.default_territory)
                        AND NOT EXISTS
                              (SELECT   LOCAL.lob_code
                                 FROM   xdo_lobs LOCAL
                                WHERE   LOCAL.lob_type = 'TEMPLATE_SOURCE'
                                        AND LOCAL.lob_code =
                                              xtv.template_code
                                        AND LOCAL.application_short_name =
                                              xtv.application_short_name
                                        AND LOCAL.LANGUAGE =
                                              xtv.default_language
                                        AND LOCAL.territory =
                                              xtv.default_territory)))
                   AND lob_code = xtv.template_code
                   AND xdo_lobs.application_short_name =
                         xtv.application_short_name)
            default_template_file,
         (SELECT   file_name
            FROM   xdo_lobs
           WHERE   lob_type = 'TEMPLATE_SOURCE'
                   AND lob_code = xtv.template_code
                   AND xdo_lobs.application_short_name =
                         xtv.application_short_name
                   AND xdo_lobs.LANGUAGE = xtv.mls_language
                   AND xdo_lobs.territory = xtv.mls_territory)
            mls_template_file,
         (SELECT   NAME
            FROM   fnd_iso_languages_vl
           WHERE   iso_language_2 = xtv.default_language)
            default_file_lang,
         DECODE (xtv.default_territory,
                 '00',
                 ftv.territory_short_name)
            default_file_terr,
         xtv.default_language,
         xtv.default_territory,
         xtv.template_status,
         xtv.use_alias_table,
         xtv.start_date,
         xtv.end_date,
         xtv.created_by,
         xtv.creation_date,
         xtv.last_updated_by,
         xtv.last_update_date,
         xtv.last_update_login,
         xtv.object_version_number,
         xtv.dependency_flag,
         xtv.mls_language,
         xtv.mls_territory,
         xtv.default_output_type			
  FROM   xdo_templates_vl xtv,
         fnd_application_vl fndapplicationvl,
         fnd_territories_vl ftv,
         fnd_user fu
 WHERE   fndapplicationvl.application_short_name =
            xtv.application_short_name
   AND ftv.territory_code(+) =  xtv.default_territory
   AND xtv.created_by = fu.user_id		 
         --AND template_name = 'Template Name'

Sample Output: