Custom Event - How can we defer an action after triggering the event?

I am trying to find a way to defer an action after an event has been triggered. Does IFS have the option to delay an action for a specific amount of time after the event has been triggered?

For example - When the event gets triggered due to a change in the Business Opportunity status, then tell IFS “hey wait an hour, then call the first action” or is there something similar to Transaction_SYS.Deferred_Call where we tell oracle “hey wait an hour then run that pl/sql procedure call into the background”?

Page 1 / 1

Hi @jamie.malangyaon ,
you can try register your procedure as Database Task and then Schedule it via Custom Event to particular time.

For example:

DECLARE
   info_          VARCHAR2(2000);
   objid_         VARCHAR2(200);
   objversion_    VARCHAR2(200);
   attr_          VARCHAR2(2000);

   schedule_id_   NUMBER;
BEGIN
   Client_SYS.Clear_Attr(attr_);
   Client_SYS.Add_To_Attr('SCHEDULE_NAME'                , 'Refresh cache'          , attr_);
   Client_SYS.Add_To_Attr('ACTIVE_DB'                    , 'TRUE'                   , attr_);
   Client_SYS.Add_To_Attr('EXECUTION_PLAN'               , 'ON 2022-05-12 AT 21:00' , attr_);
   Client_SYS.Add_To_Attr('NEXT_EXECUTION_DATE'          , '2022-05-12-21.00.00'    , attr_);
   Client_SYS.Add_To_Attr('SCHEDULE_METHOD_ID'           , '60'                     , attr_);
   Client_SYS.Add_To_Attr('START_DATE'                   , '2022-05-12-20.10.36'    , attr_);
   Client_SYS.Add_To_Attr('LANG_CODE'                    , 'en'                     , attr_);
   Client_SYS.Add_To_Attr('BATCH_SCHEDULE_TYPE_DB'       , 'TASK'                   , attr_);
   Client_SYS.Add_To_Attr('CHECK_EXECUTING_DB'           , 'FALSE'                  , attr_);
   Client_SYS.Add_To_Attr('STREAM_MSG_ON_COMPLETION_DB'  , 'FALSE'                  , attr_);

   BATCH_SCHEDULE_API.New__(
      info_,
      objid_,
      objversion_,
      attr_,
      'DO'); 

   schedule_id_ := to_number(Client_SYS.Get_Item_Value('SCHEDULE_ID', attr_));

   Client_SYS.Clear_Attr(attr_);
   Client_SYS.Add_To_Attr('SCHEDULE_ID', schedule_id_ , attr_);
   Client_SYS.Add_To_Attr('NAME'       , 'APP_NAME_'  , attr_);
   Client_SYS.Add_To_Attr('VALUE'      , '<App Name>' , attr_);

   objid_      := NULL;
   objversion_ := NULL;
   BATCH_SCHEDULE_PAR_API.New__(
      info_,
      objid_,
      objversion_,
      attr_,
      'DO');

   Client_SYS.Clear_Attr(attr_);
   Client_SYS.Add_To_Attr('SCHEDULE_ID', schedule_id_    , attr_);
   Client_SYS.Add_To_Attr('NAME'       , 'APP_VERSION_'  , attr_);
   Client_SYS.Add_To_Attr('VALUE'      , '<App Version>' , attr_);

   objid_      := NULL;
   objversion_ := NULL;
   BATCH_SCHEDULE_PAR_API.New__(
      info_,
      objid_,
      objversion_,
      attr_,
      'DO');
END;

(Code is rewritten from console without test)

With dynamic time it can look like this:

DECLARE
   ...
      execution_time_   DATE;
   ...
BEGIN
   execution_time_ := SYSDATE + 1/24; -- execute after hour
   ...
   -- Client_SYS.Add_To_Attr('EXECUTION_PLAN' , 'ON 2022-05-12 AT 21:00' , attr_);
   Client_SYS.Add_To_Attr('EXECUTION_PLAN'    , 'ON ' || TO_CHAR(execution_time_, 'YYYY-MM-DD'), || ' AT ' || TO_CHAR(execution_time_, 'HH24:MI') , attr_);
   -- Client_SYS.Add_To_Attr('NEXT_EXECUTION_DATE' , '2022-05-12-21.00.00'    , attr_);
   Client_SYS.Add_To_Attr('NEXT_EXECUTION_DATE'    , TO_CHAR(execution_time_, 'YYYY-MM-DD-HH24.MI') || '.00' , attr_);
   ...
END;

Here is an example to schedule a background job for any PLSQL Block you want to run:

DECLARE
   attr_                VARCHAR2(32000);
   sql_msg_             VARCHAR2(32000);
   stmt_                VARCHAR2(32000);
BEGIN
      stmt_ :=
      'DECLARE
          info_ VARCHAR2(32000);
          objid_ VARCHAR2(2000);
          attr_cf_ VARCHAR2(32000);
          attr_ VARCHAR2(32000);
          row_key_   VARCHAR2(32000):=''&NEW:ROWKEY'';
          val_rowkey_ VARCHAR2(2000);

         CURSOR get_objid IS
           SELECT objid
           FROM Doc_Issue_CfV
           WHERE objkey =row_key_;

         CURSOR Get_Val(value_ VARCHAR2) IS
           SELECT t.objkey 
           FROM   C_DOC_SOURCE_CLV t
           WHERE t.CF$_SOURCE=value_;

       BEGIN
         OPEN get_objid;
         FETCH get_objid INTO objid_;
         CLOSE get_objid;
         IF objid_ IS NOT NULL THEN

            OPEN Get_cf_rowkey(''ABC'');
            FETCH Get_cf_rowkey INTO cf_val_rowkey_;
            CLOSE Get_cf_rowkey;

             Client_SYS.Clear_Attr(attr_cf_);
             Client_SYS.Add_To_Attr(''CF$_ABC'', cf_val_rowkey_, attr_cf_);
             Doc_Issue_Cfp.Cf_New__(info_, objid_, attr_cf_, attr_, ''DO'');
          END IF;
       END;';

      sql_msg_ := Message_SYS.Construct('RELEASE');
      Message_SYS.Add_Attribute(sql_msg_, 'SQL', stmt_);

      Client_SYS.Clear_Attr(attr_);
      Client_SYS.Add_To_Attr('SQL_DATA_', sql_msg_, attr_);
      Client_SYS.Add_To_Attr('MSG_', '', attr_);

      Transaction_SYS.Deferred_Call('Fnd_Event_Action_API.Action_Executeonlinesql', 'PARAMETER', attr_,
                                    Language_SYS.Translate_Constant('Event', 'UPDREV: Update ABC in document revision' ));

END;

Hi @jamie.malangyaon ,
you can try register your procedure as Database Task and then Schedule it via Custom Event to particular time.

For example:

DECLARE
   info_          VARCHAR2(2000);
   objid_         VARCHAR2(200);
   objversion_    VARCHAR2(200);
   attr_          VARCHAR2(2000);

   schedule_id_   NUMBER;
BEGIN
   Client_SYS.Clear_Attr(attr_);
   Client_SYS.Add_To_Attr('SCHEDULE_NAME'                , 'Refresh cache'          , attr_);
   Client_SYS.Add_To_Attr('ACTIVE_DB'                    , 'TRUE'                   , attr_);
   Client_SYS.Add_To_Attr('EXECUTION_PLAN'               , 'ON 2022-05-12 AT 21:00' , attr_);
   Client_SYS.Add_To_Attr('NEXT_EXECUTION_DATE'          , '2022-05-12-21.00.00'    , attr_);
   Client_SYS.Add_To_Attr('SCHEDULE_METHOD_ID'           , '60'                     , attr_);
   Client_SYS.Add_To_Attr('START_DATE'                   , '2022-05-12-20.10.36'    , attr_);
   Client_SYS.Add_To_Attr('LANG_CODE'                    , 'en'                     , attr_);
   Client_SYS.Add_To_Attr('BATCH_SCHEDULE_TYPE_DB'       , 'TASK'                   , attr_);
   Client_SYS.Add_To_Attr('CHECK_EXECUTING_DB'           , 'FALSE'                  , attr_);
   Client_SYS.Add_To_Attr('STREAM_MSG_ON_COMPLETION_DB'  , 'FALSE'                  , attr_);

   BATCH_SCHEDULE_API.New__(
      info_,
      objid_,
      objversion_,
      attr_,
      'DO'); 

   schedule_id_ := to_number(Client_SYS.Get_Item_Value('SCHEDULE_ID', attr_));

   Client_SYS.Clear_Attr(attr_);
   Client_SYS.Add_To_Attr('SCHEDULE_ID', schedule_id_ , attr_);
   Client_SYS.Add_To_Attr('NAME'       , 'APP_NAME_'  , attr_);
   Client_SYS.Add_To_Attr('VALUE'      , '<App Name>' , attr_);

   objid_      := NULL;
   objversion_ := NULL;
   BATCH_SCHEDULE_PAR_API.New__(
      info_,
      objid_,
      objversion_,
      attr_,
      'DO');

   Client_SYS.Clear_Attr(attr_);
   Client_SYS.Add_To_Attr('SCHEDULE_ID', schedule_id_    , attr_);
   Client_SYS.Add_To_Attr('NAME'       , 'APP_VERSION_'  , attr_);
   Client_SYS.Add_To_Attr('VALUE'      , '<App Version>' , attr_);

   objid_      := NULL;
   objversion_ := NULL;
   BATCH_SCHEDULE_PAR_API.New__(
      info_,
      objid_,
      objversion_,
      attr_,
      'DO');
END;

(Code is rewritten from console without test)

With dynamic time it can look like this:

DECLARE
   ...
      execution_time_   DATE;
   ...
BEGIN
   execution_time_ := SYSDATE + 1/24; -- execute after hour
   ...
   -- Client_SYS.Add_To_Attr('EXECUTION_PLAN' , 'ON 2022-05-12 AT 21:00' , attr_);
   Client_SYS.Add_To_Attr('EXECUTION_PLAN'    , 'ON ' || TO_CHAR(execution_time_, 'YYYY-MM-DD'), || ' AT ' || TO_CHAR(execution_time_, 'HH24:MI') , attr_);
   -- Client_SYS.Add_To_Attr('NEXT_EXECUTION_DATE' , '2022-05-12-21.00.00'    , attr_);
   Client_SYS.Add_To_Attr('NEXT_EXECUTION_DATE'    , TO_CHAR(execution_time_, 'YYYY-MM-DD-HH24.MI') || '.00' , attr_);
   ...
END;

This is great. I had no idea we could schedule a database task through a procedure! I will definitely test this out. Thank you so much!

Here is an example to schedule a background job for any PLSQL Block you want to run:

DECLARE
   attr_                VARCHAR2(32000);
   sql_msg_             VARCHAR2(32000);
   stmt_                VARCHAR2(32000);
BEGIN
      stmt_ :=
      'DECLARE
          info_ VARCHAR2(32000);
          objid_ VARCHAR2(2000);
          attr_cf_ VARCHAR2(32000);
          attr_ VARCHAR2(32000);
          row_key_   VARCHAR2(32000):=''&NEW:ROWKEY'';
          val_rowkey_ VARCHAR2(2000);

         CURSOR get_objid IS
           SELECT objid
           FROM Doc_Issue_CfV
           WHERE objkey =row_key_;

         CURSOR Get_Val(value_ VARCHAR2) IS
           SELECT t.objkey 
           FROM   C_DOC_SOURCE_CLV t
           WHERE t.CF$_SOURCE=value_;

       BEGIN
         OPEN get_objid;
         FETCH get_objid INTO objid_;
         CLOSE get_objid;
         IF objid_ IS NOT NULL THEN

            OPEN Get_cf_rowkey(''ABC'');
            FETCH Get_cf_rowkey INTO cf_val_rowkey_;
            CLOSE Get_cf_rowkey;

             Client_SYS.Clear_Attr(attr_cf_);
             Client_SYS.Add_To_Attr(''CF$_ABC'', cf_val_rowkey_, attr_cf_);
             Doc_Issue_Cfp.Cf_New__(info_, objid_, attr_cf_, attr_, ''DO'');
          END IF;
       END;';

      sql_msg_ := Message_SYS.Construct('RELEASE');
      Message_SYS.Add_Attribute(sql_msg_, 'SQL', stmt_);

      Client_SYS.Clear_Attr(attr_);
      Client_SYS.Add_To_Attr('SQL_DATA_', sql_msg_, attr_);
      Client_SYS.Add_To_Attr('MSG_', '', attr_);

      Transaction_SYS.Deferred_Call('Fnd_Event_Action_API.Action_Executeonlinesql', 'PARAMETER', attr_,
                                    Language_SYS.Translate_Constant('Event', 'UPDREV: Update ABC in document revision' ));

END;

Hi Tomas, thanks for this but where in here would it delay my task for a specified amount of time? I am looking for a procedure that will post a background job but won’t allow it to execute until after 1 hour. During that hour, allow other background jobs to run. (I’ve tried using DBSM_LOCK.SLEEP but that prevents other background jobs to execute within the hour).

Missed the part with the delay. Go for the option with a scheduled task, that is best.

Hi @jamie.malangyaon and @Tomas Ruderfelt ,
the best solution could be usage of both.
Register Fnd_Event_Action_API.Action_Executeonlinesql as Database Task (if it is not already registered)
And then via Custom Event create Schedule of this procedure with dynamic SQL command as input.
So you will have only one Database Task, what can be used for various code.

BTW, for dynamic statement string I recommend you usage of “literal quoting” (Oracle Live SQL - Script: Alternative Quoting Mechanism (''Q'') for String Literals):

stmt := q'tinsert into MY_TBL (Col1, Col2) values('ER0002', 'ER0003')]';
-- instead of
stmt := 'insert into MY_TBL (Col1, Col2) values(''ER0002'', ''ER0003'')';

Great post, I’ve been looking for more clarification on how to do exactly this!

Another thing I did on top of this was define the method in the Batch Configuration Queue so I could make sure these schedules didn't interfere with other events. The schedule of the task still remains true even with a shorter execution plan of the Queue. Other actions where the ExecuteOnlineSQL method is used and a different Queue is specified in the code are unaffected.

I also defined a table for ease of use for management. It has variable durations, email address or reference to defined groups, and escalation groups per type of task.

I would like to format some HTML inside the block but it was low priority at the moment, but all suggestions and examples are welcome

I’ll share my tested and published code in case anyone else needs an example with all the suggestions from above all together...although this post is by far one of the most informative. Thanks!

DECLARE
info_ varchar2(2000);
	objid_ varchar2(200);
	objversion_ varchar2(200);
	attr_ varchar2(32676);
	sql_msg_ varchar2(32676);
	stmt_ varchar2(32676);
	schedule_id_   number;
	method_id_ varchar2(5) := '633';/*prod is 632*/
	execution_time_ date;
	action_ varchar2(5):= 'DO';
	needs_validation_ number;
	min_multip_ number;
BEGIN
	min_multip_ := IFSAPP.TI_EMAIL_ACTION_BRIDGE_CLP.get_cf$_time('ScrapValidation');
	execution_time_ := SYSDATE + (60*min_multip_)/86400;
	stmt_ := q's
	DECLARE
		info_ varchar2(32000) := '';
		objkey_ varchar2(4000) := '&NEW:ROWKEY';
		objversion_ varchar2(32000) := TO_CHAR(TO_DATE('&NEW:ROWVERSION', 'YYYY-MM-DD-HH24.MI.SS'),'YYYYMMDDHH24MISS');
		inside_attr_ varchar2(32000);
		action_ varchar2(5) := 'DO';
		to_user_name_ varchar2(100);
		from_user_name_ varchar2(100);
		subject_ varchar2(1000);
		text_ varchar2(32676);
		date_created_ varchar2(100);
		location_no_ varchar2(100);
		part_ varchar2(100);
		quantity_ number;
		valid_ varchar2(100);
		emails_ varchar2(4000);

	CURSOR recs_ IS
		SELECT IFSAPP.TI_EMAIL_ACTION_BRIDGE_CLP.get_cf$_emails('ScrapValidation') emails, NVL(cf$_needs_validation,0) valid, cf$_part_no, cf$_waste_group, cf$_location_no, cf$_quantity, cf$_defect, TO_CHAR(cf$_date_created, 'MM-DD-YY HH:MI:SS PM') cf$_date_created, cf$_checked, cf$_needs_validation, objkey 
		FROM IFSAPP.ti_scrap_designation_clv WHERE cf$_needs_validation = 1 AND objkey = objkey_
	;

	BEGIN

	FOR rec_ IN recs_ LOOP
		date_created_ := rec_.cf$_date_created ;
		location_no_ := rec_.cf$_location_no ;
		part_ := rec_.cf$_waste_group ;
		quantity_ := rec_.cf$_quantity ;
		valid_ := rec_.valid ;
		emails_ := rec_.emails ;

		IF valid_ = 1 THEN 
		CLIENT_SYS.Clear_Attr(inside_attr_);
		CLIENT_SYS.Add_To_Attr('CF$_CHECKED' , 'No', inside_attr_);
		IFSAPP.TI_SCRAP_DESIGNATION_CLP.Modify__ (info_, objkey_, objversion_, inside_attr_, action_);

		IFSAPP.COMMAND_SYS.mail(
			from_user_name_ => 'email@email.com' /*Fnd_Session_API.Get_Fnd_User*/,
			to_user_name_ => emails_,
			text_ => 
				'Part: ' || part_
				|| '         Quantity: ' || quantity_
				|| '         Location: ' || location_no_
				|| '         Made: ' || date_created_
				,
			subject_ => 'Waste Not Checked In'
		);
		END IF;
	END LOOP;
	END;
	]'; 
		CLIENT_SYS.Clear_Attr(attr_);
		CLIENT_SYS.Add_To_Attr('SCHEDULE_NAME' , 'Waste Manbale Arrival Check', attr_);
		CLIENT_SYS.Add_To_Attr('ACTIVE_DB', 'TRUE', attr_);
		Client_SYS.Add_To_Attr('EXECUTION_PLAN', 'ON ' || TO_CHAR(execution_time_, 'YYYY-MM-DD') || ' AT ' || TO_CHAR(execution_time_, 'HH24:MI') , attr_);
		Client_SYS.Add_To_Attr('NEXT_EXECUTION_DATE', TO_CHAR(execution_time_, 'YYYY-MM-DD-HH24.MI') || '.00' , attr_);
		CLIENT_SYS.Add_To_Attr('SCHEDULE_METHOD_ID', method_id_, attr_);
		CLIENT_SYS.Add_To_Attr('START_DATE', SYSDATE, attr_);
		CLIENT_SYS.Add_To_Attr('LANG_CODE', 'en', attr_);
		CLIENT_SYS.Add_To_Attr('BATCH_SCHEDULE_TYPE_DB', 'TASK', attr_);
		CLIENT_SYS.Add_To_Attr('CHECK_EXECUTING_DB', 'FALSE', attr_);
		CLIENT_SYS.Add_To_Attr('STREAM_MSG_ON_COMPLETION_DB', 'FALSE', attr_);
	BATCH_SCHEDULE_API.New__(info_,     objid_, objversion_, attr_, action_); 

		schedule_id_ := to_number(Client_SYS.Get_Item_Value('SCHEDULE_ID', attr_));
		sql_msg_ := MESSAGE_SYS.Construct('UPD');
		MESSAGE_SYS.Add_Attribute(sql_msg_, 'SQL', stmt_);

		CLIENT_SYS.Clear_Attr(attr_);
		CLIENT_SYS.Add_To_Attr('SCHEDULE_ID', schedule_id_ , attr_);
		CLIENT_SYS.Add_To_Attr('NAME', 'SQL_DATA_', attr_);
		CLIENT_SYS.Add_To_Attr('VALUE', sql_msg_, attr_);
		objid_      := NULL;
		objversion_ := NULL;
	BATCH_SCHEDULE_PAR_API.New__(info_, objid_, objversion_, attr_, action_);

		CLIENT_SYS.Clear_Attr(attr_);
		CLIENT_SYS.Add_To_Attr('SCHEDULE_ID', schedule_id_ , attr_);
		CLIENT_SYS.Add_To_Attr('NAME', 'MSG_', attr_);
		CLIENT_SYS.Add_To_Attr('VALUE', '', attr_);
		objid_      := NULL;
		objversion_ := NULL;
	BATCH_SCHEDULE_PAR_API.New__(info_, objid_, objversion_, attr_, action_);
END;

Reply

Did you find what you're looking for? If not:

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded