Custom Event to Stop and Trigger Email

Well, you can send the email via background task. try using Transaction_SYS.Deferred_Call(); this would help you handy in this kinda situation.

Cheers!

Thanks @EntShehaM. I assume the deferred call will create the background job regardless if the primary transaction was terminated with Error_sys.General() then?

Hi @EntShehaM 

I tried this but it didnt appear to kick off the deferred call. Here is the code that is relevant. I get the Error message displayed, but nothing in the background jobs. Maybe I am missing something. 

IF not operational_status_ = 'In Operation'  THEN

   Client_SYS.Clear_Attr(arguments_);

   Client_SYS.Add_To_Attr('FROM_USER_NAME', 'IFSAPP' , arguments_);

   Client_SYS.Add_To_Attr('TO_USER_NAME', 'david.harmer@company.com' ,arguments_);

   Client_SYS.Add_To_Attr('TEXT', 'TOOL IS OUT OF OPERATION!!' , arguments_);

   Client_SYS.Add_To_Attr('SUBJECT', 'XX TRIED TO BOOK OUT TOOL X WHICH IS OUT OF CAL BY X' , arguments_);

   

  -- Call the procedure

  transaction_sys.deferred_call(procedure_name_ => 'command_sys.Mail',

                                argument_type_db_ => 'PARAMETER',

                                arguments_ => arguments_,

                                description_ => 'Email QA - Attempt to use Out of Calibration tool (from event)');

   Error_SYS.Record_General('ManufToolDetailUsage', 'Event Cal Tool Usage: Tool ' || mch_code_ || ' is shown to be out of calibration, contact your Supervisor.');

END IF;

Try modify the code I attached herewith, two codes are for two different requirements. from one you can get the email sending method and the other one how to use the background job. you can combine the method and write your own for the scenario. 

also try setup 2 event actions too. one for trigger an email and one for error message this may also work.

hope this would help...

Hi @EntShehaM {Hi bro! }

Deferred call is the right way to go for for this but since there’s an exception (Error_sys) call at the end, it will rollback the whole thing.

If you use a sub program with autonomous transaction, then the rollback will not affect the posting of background job.

@david.harmer , below is the modified code with what I mentioned above, and I noticed the parameters to call command_sys.mail were wrong (missing underscores) and fixed it too  .Pls try whether it works for you.

DECLARE

PROCEDURE send_email_deferred(arguments_ IN VARCHAR2) IS
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	transaction_sys.deferred_call(procedure_name_ => 'command_sys.Mail',
                                argument_type_db_ => 'PARAMETER',
                                arguments_ => arguments_,
                                description_ => 'Email QA - Attempt to use Out of Calibration tool (from event)');
	commit;							
END send_email_deferred;
BEGIN

	IF not operational_status_ = 'In Operation'  THEN
	   Client_SYS.Clear_Attr(arguments_);
	   Client_SYS.Add_To_Attr('FROM_USER_NAME_', 'IFSAPP' , arguments_);
	   Client_SYS.Add_To_Attr('TO_USER_NAME_', 'david.harmer@company.com' ,arguments_);
	   Client_SYS.Add_To_Attr('TEXT_', 'TOOL IS OUT OF OPERATION!!' , arguments_);
	   Client_SYS.Add_To_Attr('SUBJECT_', 'XX TRIED TO BOOK OUT TOOL X WHICH IS OUT OF CAL BY X' , arguments_);   

	   -- Call the procedure
	   send_email_deferred(arguments_);

	   Error_SYS.Record_General('ManufToolDetailUsage', 'CEVENTERROR: Event Cal Tool Usage: Tool ' || mch_code_ || ' is shown to be out of calibration, contact your Supervisor.');

	END IF;
END;

Cheers!

Damith