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;