Cancel Sales order- At header Level

Hello,

Requirement: To create an alert when sales order is cancelled for specific order type and Order Subtotal . I am trying to create an alert on oe_order_headers_all table.

Question: How do we find the Sales order subtotal  when the status is CANCELLED? I am aware of the API for cancelling sales order but when I pass the Sales Order HEADER_ID into the API then the Order Total is ZERO because on Cancelling a Sales order the Sales order lines Quantity is all Zero and therefore the Subtotal is Zero.

Any idea on how this can be achieved?