Skip to main content

 

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;

 

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