How to Delete Data Definition and Template in XML Publisher (Oracle Apps)

Data definition and templates are the main backbone of XML Publisher reports in Oracle Apps. Each data and template definition is unique. Once defined, you can not use the same name/code for others. First, you define the data definition and then the template. So, when you want to delete, you need to follow the reverse order i.e. delete the template and then the data definition.

Deletion is not allowed from the front end, so you have to do it by running the delete statement on the XML Publisher metadata table.

XDO_TEMPLATES_B, XDO_TEMPLATES_TL, and XDO_LOBS are the table that holds information about the data template in XML Publisher. Run the below command in sequence to get rid of the template definition.

DELETE FROM XDO_TEMPLATES_B
WHERE template_code = ‘XXINVPRINT’;

DELETE FROM XDO_TEMPLATES_TL
WHERE template_code = ‘XXINVPRINT’;

DELETE FROM XDO_LOBS
WHERE lob_code = ‘XXINVPRINT’;

XDO_DS_DEFINITIONS_B and XDO_DS_DEFINITIONS_TL are tables to hold the information about data definition.

DELETE FROM XDO_DS_DEFINITIONS_B
WHERE data_source_code = ‘XXINVPRINT’;

DELETE FROM XDO_DS_DEFINITIONS_TL 
WHERE data_source_code = ‘XXINVPRINT’;

Make sure to run commit.

Run a select query on the above table to make sure data and template data are deleted successfully. This method should work on all versions of Oracle Apps.