Query to get BI/XML Publisher Data Definition and Template details

--Query to get XML Publisher Data Definition and Template details
SELECT fcp.user_concurrent_program_name "Program Name",
         fcp1.concurrent_program_name "Program Short Name",
         fat.application_name program_application_name,
         fet.executable_name "Executable Name",
         fat1.application_name executable_application_name,
         fet.execution_file_name,
         flv.meaning execution_method,
         xlo.lob_type,
         xlo.file_name "Data/Brusting File",
         xds.data_source_code,
         xds.data_source_name,
         xdt.template_code,
         xlt.language,
         xlt.file_name
    FROM fnd_concurrent_programs_tl fcp,
         fnd_concurrent_programs fcp1,
         fnd_executables fet,
         fnd_lookup_values flv,
         fnd_application_tl fat,
         fnd_application_tl fat1,
         xdo_lobs xlo,
         xdo_ds_definitions_vl xds,
         xdo_templates_b xdt,
         xdo_templates_tl xdtl,
         xdo_lobs xlt
   WHERE 1=1   
     AND (fcp.application_id = :p_app_id OR :p_app_id IS NULL)
     AND fcp.language = 'US'
     AND fcp.concurrent_program_id = fcp1.concurrent_program_id
     AND fcp.application_id = fcp1.application_id
     AND fcp1.executable_id = fet.executable_id
     AND fcp1.executable_application_id = fet.application_id
     AND flv.lookup_code = fet.execution_method_code
     AND flv.language = 'US'
     AND FLV.LOOKUP_TYPE = 'CP_EXECUTION_METHOD_CODE'
     AND fcp1.application_id = fat.application_id
     AND fet.application_id = fat1.application_id
     AND fat.language = 'US'
     AND fat1.language = 'US'
     AND fet.executable_name = 'XDODTEXE'
     AND fcp1.concurrent_program_name = xlo.lob_code(+)
     AND xlo.lob_type IN ('DATA_TEMPLATE', 'BURSTING_FILE')
     AND (fcp1.concurrent_program_name = :p_program_code OR :p_program_code IS NULL)
     AND fcp1.concurrent_program_name = xds.data_source_code
     AND xds.data_source_code = xdt.data_source_code(+)
     AND xdt.template_code = xdtl.template_code(+)
     AND xdtl.language = 'US'
     AND xds.data_source_name = xdtl.template_name
     AND xlt.lob_type = 'TEMPLATE_SOURCE'
     AND xdt.template_code = xlt.lob_code
ORDER BY 1;

Namespace prefix 'ref' used but not declared. Error in BI Publisher Report.

Below error comes when we used higher version of BI Publisher and reports will be completing with warning.

[1/6/19 1:57:03 AM] [798109:RT11983076] Output file was found but is zero sized - Deleted
[1/6/19 1:57:03 AM] [UNEXPECTED] [798109:RT11983076] java.lang.reflect.InvocationTargetException
Caused by: org.xml.sax.SAXException: Namespace prefix 'ref' used but not declared.
oracle.xdo.parser.v2.XPathException: Namespace prefix 'ref' used but not declared.
        at oracle.xdo.parser.v2.XSLProcessor.reportException(XSLProcessor.java:806)
        at oracle.xdo.parser.v2.XSLProcessor.newXSLStylesheet(XSLProcessor.java:614)


In case MS Word (rtf template) Properties of the XML Field has source something like ‘<?ref:xdo0002?>’ then you will face this error.


Solution:
To resolve this issue we need to set "form field size" option as "Backward Compatible". See as follows.


Navigation: Open report Template => BI Publisher Tab => Option (highlighted as yellow) => Window will open => Select "Build" => Select "Backward Compatible" from "form field size" section.

 

Save the changes and upload the changed template and retest.

Query to Delete XML Publisher Data Definition and Template from Backend

----Script/Query to Delete XML Publisher Data Definition and Template from Backend--
DECLARE
   -- Change the following two parameters
   var_templateCode    VARCHAR2 (100) := 'XXXXXXXXX';     -- Template Code
   boo_deleteDataDef   BOOLEAN := TRUE;     -- delete the associated Data Def. then TRUE unless make it as FALSE.
BEGIN
   FOR RS
      IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,
                 T1.DATA_SOURCE_CODE,
                 T2.APPLICATION_SHORT_NAME DEF_APP_NAME
            FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2
           WHERE T1.TEMPLATE_CODE = var_templateCode
                 AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)
   LOOP
      XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);

      DELETE FROM XDO_LOBS
            WHERE     LOB_CODE = var_templateCode
                  AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
                  AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');

      DELETE FROM XDO_CONFIG_VALUES
            WHERE     APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
                  AND TEMPLATE_CODE = var_templateCode
                  AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
                  AND CONFIG_LEVEL = 50;

      DBMS_OUTPUT.PUT_LINE ('Selected template has been ' || var_templateCode || ' deleted.');

      IF boo_deleteDataDef
      THEN
         XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,
                                            RS.DATA_SOURCE_CODE);

         DELETE FROM XDO_LOBS
               WHERE LOB_CODE = RS.DATA_SOURCE_CODE
                     AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
                     AND LOB_TYPE IN
                            ('XML_SCHEMA',
                             'DATA_TEMPLATE',
                             'XML_SAMPLE',
                             'BURSTING_FILE');

         DELETE FROM XDO_CONFIG_VALUES
               WHERE     APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
                     AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
                     AND CONFIG_LEVEL = 30;

         DBMS_OUTPUT.PUT_LINE (
            'Selected Data Definition has been ' || RS.DATA_SOURCE_CODE || ' deleted.');
      END IF;
   END LOOP;
  COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (
         'Unable to delete XML Publisher Template ' || var_templateCode);
      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));
END;
/

Query to get location of rtf Template of Bursting Control file

SELECT 'xdo://'||
       xtb.application_short_name||'.'||
       xtb.template_code ||'.'||
       xtb.default_language ||'.'||
       xtb.default_territory rtf_template_location
  FROM xdo_templates_b xtb
 WHERE 1=1
   AND xtb.template_code = 'RTF_TEMPLATE_CODE'