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
