Inserting a value upon new data creation on Custom Logical Unit

Hello @ludovic.rougean 

You need to call a cursor in the top before the Begin

Something like:

Declare

    info_ varchar2(300);
    objid1__  varchar2(300);
    objversion2__  varchar2(300);
    attr_ varchar2(1000);
    id1_ number  := 258;

    ROWKEY__ VARCHAR2(120)  := '&OLD:ROWKEY';

  CURSOR GET_DATA IS

  SELECT OBJKEY, OBJVERSION 
  FROM IFSAPP.t_g_t_t_b_f_clv
  WHERE IFSAPP.t_g_t_t_b_f_clv.OBJKEY = ROWKEY__ ;  --- BE SURE THE FIELD IS THE CORRECT--

BEGIN

    -- IF YOU ARE ONLY PASSING THE 'CF$_ID1' TO ATTR_ YOU JUST CAN ADD THE  'CF$_ID1' TO THE MODIFY PROCEDURE-- this is only because you are only passing one variable .

FOR REC IN GET_DATA LOOP

    objid1__  := REC.OBJKEY;

   objversion2__  := REC.OBJVERSION ;
    ifsapp.T_G_T_T_B_F_CLP.Modify__(info_, objid1__, objversion2__ id1_, 'DO');

    COMMIT;

END LOOP;

END;

I hope this help!

JL

https://www.linkedin.com/in/juan-luis-sanchez-gota-44640476/

The problem is that when you use PRAGMA AUTONOMOUS_TRANSACTION; you actually starts a new session to run just this PL/SQL Block.

The record you are inserting is not committed (and can’t be at this stage) in the original session so you will never be able to find objid and objversion and never be able to update a record that does not exist yet.

The only way to solve the default value is to post the PL/SQL Block you have as a background job. That you do with method: Transaction_SYS.Deferred_Call

One last thing, you are using &OLD:ROWKEY, that will not work since you are triggering on INSERT. Use &NEW:ROWKEY instead.

Example: (This is not a custom LU but you get the idea about how to post a background job.)

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,

Thanks to both of you for your advice. I combined your 2 suggestions, and it works !

I just share the codes for others:

DECLARE
   attr_                VARCHAR2(32000);
   sql_msg_             VARCHAR2(32000);
   stmt_                VARCHAR2(32000);
BEGIN
      stmt_ :=
      'DECLARE
          info_ VARCHAR2(32000);
          objid1__ VARCHAR2(2000);
		  objversion2__  varchar2(300);
          attr_ VARCHAR2(32000);
          row_key_   VARCHAR2(32000):=''&NEW:ROWKEY'';
		   id1_ VARCHAR2(32000)  := ''MYID2'';

         CURSOR get_objid IS
           SELECT OBJKEY, OBJVERSION
           FROM IFSAPP.l_r_n_t_g_t_clv
           WHERE IFSAPP.l_r_n_t_g_t_clv.OBJKEY =row_key_;

       BEGIN	 
		FOR REC IN get_objid LOOP
			objid1__  := REC.OBJKEY;
			objversion2__  := REC.OBJVERSION ;
			Client_SYS.clear_attr(attr_);
			Client_SYS.add_to_attr(''CF$_ID1'',id1_, attr_);
			ifsapp.l_r_n_t_g_t_CLP.Modify__(info_, objid1__, objversion2__, attr_, ''DO'');
			COMMIT;
		END LOOP;
       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 ID1' ));

END; 

It has created an ID: MYID2 through a background job called: “Update ID1”

Hello @ludovic.rougean 

Excellent work!.

Thanks for sharing too.

JL