Unsuccessful/Successful Logins with Status (Active/In Active/Locked) History from FND_USER

https://dbissues.blogspot.com/2017/12/unsuccessfulsuccessful-logins-with.html


Create Table

create  table XXX_USER_STATUS_HIST
(

user_id   number,
start_date  date,
end_Date   date,
Creation_date      date,
encrypted_user_password    varchar2(100)

)

---------------------------------------------------------------------------------------

Trigger to Maintain History

CREATE OR REPLACE TRIGGER XXX_USER_STATUS_HIST_TRG

BEFORE INSERT OR UPDATE ON "APPLSYS"."FND_USER#"

FOR EACH ROW

DECLARE

duplicate_info EXCEPTION;

PRAGMA EXCEPTION_INIT (duplicate_info, -00001);

BEGIN

insert into XXX_USER_STATUS_HIST

values

(:new.user_id , :new.start_date ,:new.end_date , sysdate , :new.encrypted_user_password );

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR (

num=> -20107,

msg=> 'Some Error Occurred.......');

END XXX_USER_STATUS_HIST_TRG;

------------------------------------------------------------------------------------------------

Query

select user_id, USER_NAME, DESCRIPTION,FAX,START_TIME, LOGIN   ,

(
select max(  case
                when  xx.ENCRYPTED_USER_PASSWORD = 'INVALID'  then  'LOCKED'
                when  xx.end_date is null then 'ACTIVE'
                when  xx.end_date is not null and ABC.start_time < xx.end_date then 'ACTIVE'
                else 'INACTIVE'
           end  )
from   XXX_USER_STATUS_HIST  XX
where  XX.user_id = ABC.USER_ID
AND    XX.creation_date = (  select  max(creation_date)  from  XXX_USER_STATUS_HIST  XX2 where XX2.user_id = ABC.USER_ID  and  xx2.creation_date <=  ABC.start_time  )
)
STATUS
/*,
(
select max(    xx.ENCRYPTED_USER_PASSWORD  )
from   XXX_USER_STATUS_HIST  XX
where  XX.user_id = ABC.USER_ID
AND    XX.creation_date = (  select  max(creation_date)  from  XXX_USER_STATUS_HIST  XX2 where XX2.user_id = ABC.USER_ID  and  xx2.creation_date <=  ABC.start_time  )
)
ENCRYPTED_USER_PASSWORD*/
from
(SELECT  b.USER_ID ,  b.USER_NAME, b.DESCRIPTION, b.FAX, u.ATTEMPT_TIME START_TIME , 'UnSuccessful Login' LOGIN
,

/*( \*select *\
case when  NVL(b.end_date, SYSDATE) >= SYSDATE
and b.encrypted_user_password <> 'INVALID'  THEN  'Active'
when NVL (b.end_date, SYSDATE) < SYSDATE
and b.encrypted_user_password <> 'INVALID' THEN 'In Active'
when encrypted_user_password = 'INVALID' THEN 'Locked'
Else null
END --from FND_USER where user_id = U.user_id
)*/
--'N/A'   STATUS,
b.ENCRYPTED_USER_PASSWORD

FROM FND_UNSUCCESSFUL_LOGINS U, FND_USER B
WHERE U.USER_ID = B.USER_ID
AND B.USER_NAME LIKE NVL(&USER_NAME, '%')
AND U.ATTEMPT_TIME
          BETWEEN CASE
                    WHEN LENGTH(&START_TIME) in (14,15)
                     THEN TO_DATE(&START_TIME, 'DD-MON-RR HH24:MI')
                    WHEN LENGTH(&START_TIME) in (8,9)
                     THEN TO_DATE(&START_TIME, 'DD-MON-RR')
                    ELSE U.ATTEMPT_TIME
                  END
          AND     CASE
                    WHEN LENGTH(&END_TIME) in (14,15)
                     THEN TO_DATE(&END_TIME, 'DD-MON-RR HH24:MI')
                    WHEN LENGTH(&END_TIME) in (8,9)
                     THEN TO_DATE(&END_TIME, 'DD-MON-RR') + 1
                    ELSE U.ATTEMPT_TIME
                  END


union

select  c.USER_ID ,  c.USER_NAME, c.DESCRIPTION, c.FAX, a.START_TIME START_TIME , 'Successful Login' LOGIN
,

/*( \*select*\
case when  NVL(c.end_date, SYSDATE) >= SYSDATE
and c.encrypted_user_password <> 'INVALID'  THEN  'Active'
when NVL (c.end_date, SYSDATE) < SYSDATE
and c.encrypted_user_password <> 'INVALID' THEN 'In Active'
when encrypted_user_password = 'INVALID' THEN 'Locked'
Else null
END --from FND_USER where user_id = a.user_id
)*/
--'N/A'  STATUS

 c.ENCRYPTED_USER_PASSWORD

from fnd_logins a, fnd_user c
where a.user_id = c.user_id
and c.USER_NAME LIKE NVL(&USER_NAME, '%')
and a.LOGIN_TYPE = 'FORM'
AND a.START_TIME
          BETWEEN CASE
                    WHEN LENGTH(&START_TIME) in (14,15)
                     THEN TO_DATE(&START_TIME, 'DD-MON-RR HH24:MI')
                    WHEN LENGTH(&START_TIME) in (8,9)
                     THEN TO_DATE(&START_TIME, 'DD-MON-RR')
                    ELSE a.START_TIME
                  END
          AND     CASE
                    WHEN LENGTH(&END_TIME) in (14,15)
                     THEN TO_DATE(&END_TIME, 'DD-MON-RR HH24:MI')
                    WHEN LENGTH(&END_TIME) in (8,9)
                     THEN TO_DATE(&END_TIME, 'DD-MON-RR') + 1
                    ELSE a.START_TIME
                  END

)  ABC

order by (START_TIME) desc