-- --------------------------------------------------------------------------------
-- File Name	:	functions_x_user.sql
-- --------------------------------------------------------------------------------
-- Author	:	Danilo Vizzarro (http://www.danilovizzarro.it)
-- Download	:	http://www.danilovizzarro.it/scripts/
-- Date		:	27-MAR-2009
-- Version	:	1.0
-- --------------------------------------------------------------------------------
-- Usage	:	This script return the list of functions the 
--			USERNAME_TO_BE_CHECKED can acceed using a 
--			CURRENT_RESPONSIBILITY considering function and menu exclusions
--			The following variables should be replaced
--			USERNAME_TO_BE_CHECKED 
--			CURRENT_RESPONSIBILITY
-- --------------------------------------------------------------------------------
-- 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 FU.USER_NAME, FRTL.RESPONSIBILITY_NAME, FFL.USER_FUNCTION_NAME, FFF.FUNCTION_NAME
FROM FND_USER FU, 
FND_USER_RESP_GROUPS FURG, 
FND_RESPONSIBILITY FR, 
FND_COMPILED_MENU_FUNCTIONS FCMF, 
FND_FORM_FUNCTIONS FFF,
FND_RESPONSIBILITY_TL FRTL,
FND_FORM_FUNCTIONS_TL FFL
WHERE FURG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID 
AND FURG.RESPONSIBILITY_APPLICATION_ID = FR.APPLICATION_ID 
AND FR.MENU_ID = FCMF.MENU_ID 
AND FCMF.GRANT_FLAG = 'Y' 
AND FCMF.FUNCTION_ID = FFF.FUNCTION_ID 
AND FURG.USER_ID = FU.USER_ID 
AND SYSDATE BETWEEN FU.START_DATE AND NVL(FU.END_DATE, SYSDATE+1) 
AND SYSDATE BETWEEN FR.START_DATE AND NVL(FR.END_DATE, SYSDATE+1) 
AND FURG.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID
AND FR.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID
AND FRTL.LANGUAGE = 'US'
AND FFL.LANGUAGE = 'US'
AND FFF.FUNCTION_ID = FFL.FUNCTION_ID
AND (FURG.END_DATE > SYSDATE
OR FURG.END_DATE IS NULL)
AND FU.USER_NAME = 'USERNAME_TO_BE_CHECKED'
AND FRTL.RESPONSIBILITY_NAME = 'CURRENT_RESPONSIBILITY' 
AND FFF.FUNCTION_NAME NOT IN
(
SELECT FF.FUNCTION_NAME
FROM FND_RESPONSIBILITY R, FND_USER_RESP_GROUPS RG, FND_USER U, FND_RESP_FUNCTIONS RF, FND_FORM_FUNCTIONS FF, FND_RESPONSIBILITY_TL FRTL
WHERE RG.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID 
AND U.USER_ID = RG.USER_ID
AND RF.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
AND RF.RULE_TYPE = 'F' 
AND FF.FUNCTION_ID = RF.ACTION_ID
AND FRTL.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID 
AND FRTL.RESPONSIBILITY_ID = RG.RESPONSIBILITY_ID 
AND FRTL.LANGUAGE = 'US'
AND U.USER_NAME = UPPER('USERNAME_TO_BE_CHECKED') 
AND FRTL.RESPONSIBILITY_NAME = 'CURRENT_RESPONSIBILITY'
)
AND FFF.FUNCTION_NAME NOT IN
(
	SELECT FUNCTION_NAME
	FROM 
		(
		SELECT DISTINCT
			( 
			SELECT FUNCTION_NAME
			FROM FND_FORM_FUNCTIONS F
			WHERE F.FUNCTION_ID = ME.FUNCTION_ID
			) FUNCTION_NAME, MENU_ID
		FROM FND_MENU_ENTRIES ME
		START WITH 
		ME.MENU_ID IN
			( 
			SELECT RF.ACTION_ID
			FROM FND_RESPONSIBILITY R, FND_USER_RESP_GROUPS RG, FND_USER U, FND_RESP_FUNCTIONS RF, FND_RESPONSIBILITY_TL FRTL
			WHERE RG.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID 
			AND U.USER_ID = RG.USER_ID
			AND RF.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
			AND RF.RULE_TYPE = 'M'
			AND FRTL.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
			AND FRTL.RESPONSIBILITY_ID = RF.RESPONSIBILITY_ID
			AND U.USER_NAME = UPPER('USERNAME_TO_BE_CHECKED') 
			AND FRTL.RESPONSIBILITY_NAME = 'CURRENT_RESPONSIBILITY'
			)
		CONNECT BY 
		ME.MENU_ID = PRIOR ME.SUB_MENU_ID
		)
	WHERE FUNCTION_NAME IS NOT NULL
)
ORDER BY 1,2,3
/