Script to delete data definition and template in XML Publisher Reports in Oracle apps

Hi! This post will show you the script to delete the data definition and template in XML Publisher reports in Oracle apps.

Script to delete template and data definition in XML Publisher Reports:

BEGIN

DELETE from xdo_templates_b WHERE template_code = 'XXEMP';

DELETE from xdo_templates_tl WHERE template_code = 'XXEMP';

DELETE from xdo_lobs WHERE lob_code = 'XXEMP';

DELETE from xdo_ds_definitions_tl WHERE data_source_code = 'XXEMP';

DELETE from xdo_ds_definitions_b WHERE data_source_code = 'XXEMP';

COMMIT;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line('An unknown error occurred '||SQLERRM);

END;

In the above script, XXEMP is the sample template or data definition code which is nothing but the concurrent program short name.

Happy Learning!!

Query to find order holds in Oracle apps R12

Hi! This post is to show the query to find holds on sales orders in Oracle apps R12.

Below is the query that you can use to find holds on a specific order:

SELECT ooh.order_number,

       ohd.hold_id,

       ohd.name hold_name

FROM oe_order_headers_all ooh,

     oe_order_holds_all ooha,

     oe_hold_sources_all ohs,

     oe_hold_definitions ohd

WHERE ooh.header_id = ooha.header_id

  AND ohs.hold_id = ohd.hold_id

  AND ohs.hold_source_id = ooha.hold_source_id

  AND ooh.order_number = <order_number>;

Happy learning!!