How to delete BI Publisher Templates/Data Definitions

When you try to update BI Publisher templates/data definitions you will notice that some fields are protected from update. What if you have an RTF template and one day you want to use Excel template instead? 

You can "end date" the current and create a new one.. but you will have to change the template code and change the Report "Short Name" in its Concurrent Program to pick up the new template.

If you don't wanna go through this hassle, use below code.

DECLARE
  -- Change the following two parameters
  VAR_TEMPLATECODE  VARCHAR2(100) := 'XXSD_POXRCIPS'; -- Template/Data Defintion Code
  BOO_DELETEDATADEF BOOLEAN := TRUE; -- delete the associated Data Defintion.
BEGIN
  FOR RS IN (SELECT NVL(T1.APPLICATION_SHORT_NAME,
                        T2.APPLICATION_SHORT_NAME) TEMPLATE_APP_NAME
                   ,NVL(T1.DATA_SOURCE_CODE, T2.DATA_SOURCE_CODE) DATA_SOURCE_CODE
                   ,T2.APPLICATION_SHORT_NAME DEF_APP_NAME
               FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2
              WHERE NVL(T1.TEMPLATE_CODE, T2.DATA_SOURCE_CODE) =
                    VAR_TEMPLATECODE
                AND T1.DATA_SOURCE_CODE(+) = T2.DATA_SOURCE_CODE) LOOP
    -- Deleting Template
    APPS.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('Template ' || VAR_TEMPLATECODE ||
                         ' deleted.');
    -- Deleting Data Definition
    IF BOO_DELETEDATADEF THEN
      APPS.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('Data Defintion ' ||
                           RS.DATA_SOURCE_CODE || ' deleted.');
    END IF;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Issue a COMMIT to make the changes or ROLLBACK to revert.');
EXCEPTION
  WHEN OTHERS THEN
    COMMIT;
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Unable to delete XML Publisher Template ' ||
                         VAR_TEMPLATECODE);
    DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 200));
END;