-- --------------------------------------------------------------------------------
-- File Name	:	forms_used_by_user.sql
-- --------------------------------------------------------------------------------
-- Author	:	Danilo Vizzarro (http://www.danilovizzarro.it)
-- Download	:	http://www.danilovizzarro.it/scripts/
-- Date		:	30-JAN-2009
-- Version	:	1.0
-- --------------------------------------------------------------------------------
-- Usage	:	This script return the forms used by the user 
--			USERNAME_TO_BE_CHECKED in the last 90 days		
--			The following variables should be replaced
--			USERNAME_TO_BE_CHECKED 			
-- --------------------------------------------------------------------------------
-- License	:	http://creativecommons.org/licenses/by-nc/3.0/
--			You are free:
--			-> to Share - to copy, distribute and transmit the work
--			-> to Remix - to adapt the work
--			Under the following conditions:
--			-> Attribution. You must attribute the work in the manner 
--			specified by the author or licensor (but not in any way that 
--			suggests that they endorse you or your use of the work).
--			-> Noncommercial. You may not use this work for commercial 
--			purposes.
-- --------------------------------------------------------------------------------

SELECT DISTINCT D.USER_NAME, E.RESPONSIBILITY_NAME, TO_CHAR(A.START_TIME,'DD-MON-YYYY HH24:MI:SS'), TO_CHAR(A.END_TIME,'DD-MON-YYYY HH24:MI:SS'), G.USER_FORM_NAME, TO_CHAR(F.START_TIME,'DD-MON-YYYY HH24:MI:SS') FORM_START_TIME, TO_CHAR(F.END_TIME,'DD-MON-YYYY HH24:MI:SS') FORM_END_TIME, F.START_TIME FORM_START_TIME_ORDER
FROM FND_LOGIN_RESPONSIBILITIES A, FND_RESPONSIBILITY B, FND_LOGINS C, FND_USER D, FND_RESPONSIBILITY_TL E, FND_LOGIN_RESP_FORMS F, FND_FORM_TL G
WHERE A.RESPONSIBILITY_ID = B.RESPONSIBILITY_ID
AND A.RESPONSIBILITY_ID = E.RESPONSIBILITY_ID
AND B.RESPONSIBILITY_ID = E.RESPONSIBILITY_ID
AND A.LOGIN_RESP_ID = F.LOGIN_RESP_ID
AND C.LOGIN_ID = A.LOGIN_ID
AND A.LOGIN_ID = F.LOGIN_ID
AND C.LOGIN_ID = F.LOGIN_ID
AND C.USER_ID = D.USER_ID
AND G.FORM_ID = F.FORM_ID
AND E.LANGUAGE = 'US'
AND G.LANGUAGE = 'US'
AND B.APPLICATION_ID = G.APPLICATION_ID
AND E.RESPONSIBILITY_NAME LIKE '%'
AND D.USER_NAME LIKE 'USERNAME_TO_BE_CHECKED'
AND A.START_TIME > SYSDATE - 90
ORDER BY FORM_START_TIME_ORDER DESC
/