Template File Missing
The following SQL links together the XML publisher Report Definition, Data Source, Template, and Template file, to identify any reports that have a template where the template file definition is missing.
Here's what the template definition looks like when the template file definition is missing:
If you a run an XML publisher report with a template file missing, you will generally get a message like this in your standard output log:
SQL to search for report templates missing a file:
select RD.REPORT_DEFN_ID, RD.DESCR as REPORT_DESCR, RD.PT_REPORT_STATUS, RD.DS_ID as DATA_SOURCE_ID, RD.PT_TEMPLATE_TYPE, RT.TMPLDEFN_ID, RT.IS_DEFAULT as IS_DEFAULT_TEMPLATE, RD.LASTUPDOPRID as REPORT_LASTUPDOPRID, RD.LASTUPDDTTM as REPORT_LASTUPDDTTM, DS.DS_TYPE, DS.DESCR as DATA_SOURCE_DESCR, DS.ACTIVE_FLAG, DS.SCHEMAFILE, DS.SAMPLEDATFILE, DS.REGISTERED_BY as DATA_SOURCE_REGISTERED_BY, DS.LASTUPDOPRID as DATA_SOURCE_LASTUPDOPRID, DS.LASTUPDDTTM as DATA_SOURCE_LASTUPDDTTM, TD.DESCR as TEMPLATE_DESCR, TD.REGISTERED_BY as TEMPLATE_REGISTERED_BY, TD.LASTUPDOPRID as TEMPLATE_LASTUPDOPRID, TD.LASTUPDDTTM as TEMPLATE_LASTUPDDTTM from PSXPRPTDEFN RD inner join PSXPRPTTMPL RT on RD.REPORT_DEFN_ID = RT.REPORT_DEFN_ID inner join PSXPDATASRC DS on DS.DS_TYPE = RD.DS_TYPE and DS.DS_ID = RD.DS_ID inner join PSXPTMPLDEFN TD on RT.TMPLDEFN_ID = TD.TMPLDEFN_ID left outer join PSXPTMPLFILEDEF TFD on TD.TMPLDEFN_ID = TFD.TMPLDEFN_ID where TFD.TMPLDEFN_ID is null order by RD.REPORT_DEFN_ID, RT.TMPLDEFN_ID;