Skip to main content
Solved

Loop only executes 1 line

  • March 11, 2024
  • 1 reply
  • 38 views

Forum|alt.badge.img+1

 

My loop is only executing the modify method once. What I don't understand is that with the same logic it correctly returns the oracle output. can you help me?NOTE: The cursor is returning more than 1 line.

 

DECLARE
  -- VARIAVEIS DA MODIFICAÇÃO
  p0_ VARCHAR2(32000);
  p1_ VARCHAR2(32000);
  p2_ VARCHAR2(32000);
  p3_ VARCHAR2(32000) := 'ACTIVE' || chr(31) || 'FALSE' || chr(30);
  p4_ VARCHAR2(32000) := 'DO';

  user_ VARCHAR2(32000);
  --VARIAVEIS DA MENSAGEM
  stream_ FND_STREAM_TAB%ROWTYPE;

  --BUSCAR OBJID E OBJVERSION DO USUÁRIO
  CURSOR GET IS(
    SELECT a.OBJID, a.OBJVERSION, a.IDENTITY
      FROM FND_USER a
     WHERE a.ACTIVE = 'TRUE'
       AND a.IDENTITY IN
           (SELECT b.USERNAME
              FROM ORACLE_ACCOUNT b
             WHERE b.PROFILE = 'DEFAULT'
                  --    AND (b.LAST_LOGIN <= (SYSDATE - 34) OR b.LAST_LOGIN IS NULL)
               AND (NVL(b.LAST_LOGIN, b.OBJVERSION)) <= (SYSDATE - 34)));

BEGIN

  FOR REC IN GET LOOP

    user_ := REC.IDENTITY;
    p1_   := REC.OBJID;
    p2_   := REC.OBJVERSION;

  DBMS_OUTPUT.PUT_LINE('Column1: ' || user_ || ', Column2: ' || p1_);

      FND_USER_API.Modify__(info_       => p0_,
                            objid_      => p1_,
                            objversion_ => p2_,
                            attr_       => p3_,
                            action_     => p4_);

      /*                stream_.from_user   := '';
        stream_.to_user     := 'IFSSIM';
        stream_.header      := 'Usuário bloqueado!';
        stream_.message     := 'O usuário ' || user_ ||
                               ' foi bloqueado por não acessar o sistema a 34 dias.';
        stream_.stream_type := 'GENERAL';
        stream_.lu_name     := '';
        stream_.url         := '';

        FND_STREAM_API.New_Stream_Item(stream_);
      */

  END LOOP;

END;

 

Best answer by Guilherme Rosa

Hey guys,What happens is that in the loop it is necessary to reference the ATTR value because when using the modification function it erases the ATTR, so only the first activation of the LOOP works.

 

 

The loop should look like this:

  FOR REC IN GET LOOP

    user_ := REC.IDENTITY;
    p1_   := REC.OBJID;
    p2_   := REC.OBJVERSION;
    Client_SYS.Add_To_Attr('ACTIVE', 'FALSE', p3_);

  DBMS_OUTPUT.PUT_LINE('Column1: ' || user_ || ', Column2: ' || p1_);

      FND_USER_API.Modify__(info_       => p0_,
                            objid_      => p1_,
                            objversion_ => p2_,
                            attr_       => p3_,
                            action_     => p4_);

      /*                stream_.from_user   := '';
        stream_.to_user     := 'IFSSIM';
        stream_.header      := 'Usuário bloqueado!';
        stream_.message     := 'O usuário ' || user_ ||
                               ' foi bloqueado por não acessar o sistema a 34 dias.';
        stream_.stream_type := 'GENERAL';
        stream_.lu_name     := '';
        stream_.url         := '';

        FND_STREAM_API.New_Stream_Item(stream_);
      */

  END LOOP;

 

I hope it was useful

Bye!!

View original
Did this topic help you find an answer to your question?

1 reply

Forum|alt.badge.img+1
  • Author
  • Do Gooder (Customer)
  • 1 reply
  • Answer
  • March 12, 2024
Hey guys,What happens is that in the loop it is necessary to reference the ATTR value because when using the modification function it erases the ATTR, so only the first activation of the LOOP works.

 

 

The loop should look like this:

  FOR REC IN GET LOOP

    user_ := REC.IDENTITY;
    p1_   := REC.OBJID;
    p2_   := REC.OBJVERSION;
    Client_SYS.Add_To_Attr('ACTIVE', 'FALSE', p3_);

  DBMS_OUTPUT.PUT_LINE('Column1: ' || user_ || ', Column2: ' || p1_);

      FND_USER_API.Modify__(info_       => p0_,
                            objid_      => p1_,
                            objversion_ => p2_,
                            attr_       => p3_,
                            action_     => p4_);

      /*                stream_.from_user   := '';
        stream_.to_user     := 'IFSSIM';
        stream_.header      := 'Usuário bloqueado!';
        stream_.message     := 'O usuário ' || user_ ||
                               ' foi bloqueado por não acessar o sistema a 34 dias.';
        stream_.stream_type := 'GENERAL';
        stream_.lu_name     := '';
        stream_.url         := '';

        FND_STREAM_API.New_Stream_Item(stream_);
      */

  END LOOP;

 

I hope it was useful

Bye!!


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings