USER ? RESPONSIBILITY ? PROFILE “GL: Data Access Set”(ACCESS_SET_ID)? LEDGER_SET ? LEDGER_ID(LEDGER_NAME) USER_NAME RGBK74 RESP_NAME GL Germany Support and Setup Secondary ACCESS_SET MSIAMER_LEDGER_SET -- Get all the responsibilities by special user RGBK74 SELECT R.RESPONSIBILITY_ID, R.RESPONSIBILITY_NAME
FROM FND_USER U, FND_USER_RESP_GROUPS G, FND_RESPONSIBILITY_VL R WHERE U.USER_ID = G.USER_ID
AND G.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
AND G.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID AND G.START_DATE < SYSDATE
AND NVL(G.END_DATE, SYSDATE) >= SYSDATE AND U.USER_NAME = 'RGBK74';
-- Get special responsibility by special user_name SELECT R.RESPONSIBILITY_ID, R.RESPONSIBILITY_NAME
FROM FND_USER U, FND_USER_RESP_GROUPS G, FND_RESPONSIBILITY_VL R WHERE U.USER_ID = G.USER_ID
AND G.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
AND G.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID AND G.START_DATE < SYSDATE
AND NVL(G.END_DATE, SYSDATE) >= SYSDATE
AND (UPPER(R.RESPONSIBILITY_NAME) LIKE '%SUPPORT%AND%SETUP%' OR UPPER(R.RESPONSIBILITY_NAME) LIKE '%TRANSACTION%' OR UPPER(R.RESPONSIBILITY_NAME) LIKE '%CLOSE%SUPPORT%' OR
UPPER(R.RESPONSIBILITY_NAME) LIKE '%IMPLEMENTATION SUPERUSER%') AND U.USER_NAME = 'RGBK74';
-- Get the profile \ SELECT V.PROFILE_OPTION_VALUE -- ACCESS_SET_ID
FROM APPS.FND_PROFILE_OPTIONS F, APPS.FND_PROFILE_OPTION_VALUES V WHERE F.PROFILE_OPTION_NAME = 'GL_ACCESS_SET_ID' AND F.APPLICATION_ID = V.APPLICATION_ID
AND F.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID AND V.LEVEL_VALUE = 56602; -- RESPONSIBILITY_ID
SELECT GASL.ACCESS_SET_ID, LEDGER_ID FROM GL_ACCESS_SET_LEDGERS GASL WHERE GASL.ACCESS_SET_ID = 1009;
-- GET THE LEDGER_ID BY ACCESS_SET_ID SELECT ASL.ACCESS_SET_ID, ASL.LEDGER_ID FROM APPS.FND_PROFILE_OPTIONS F, APPS.FND_PROFILE_OPTION_VALUES V, GL_ACCESS_SET_LEDGERS ASL
WHERE F.PROFILE_OPTION_NAME = 'GL_ACCESS_SET_ID' AND F.APPLICATION_ID = V.APPLICATION_ID
AND F.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID AND V.LEVEL_VALUE = 56602
AND ASL.ACCESS_SET_ID = V.PROFILE_OPTION_VALUE; -- ACCESS_SET_ID
-- L(Ledger), S(Ledger Set)
SELECT DISTINCT L.OBJECT_TYPE_CODE FROM GL_LEDGERS L;
FINAL SQL
----------------------------------------------------------------------- SELECT U.USER_ID, U.USER_NAME,
R.RESPONSIBILITY_ID, R.RESPONSIBILITY_NAME, ASL.ACCESS_SET_ID, GLE.LEDGER_ID,
GLE.NAME LEDGER_NAME
FROM FND_USER U, FND_USER_RESP_GROUPS G, FND_RESPONSIBILITY_VL R, APPS.FND_PROFILE_OPTIONS F, APPS.FND_PROFILE_OPTION_VALUES V, GL_ACCESS_SET_LEDGERS ASL, GL_LEDGERS GLE WHERE U.USER_ID = G.USER_ID
AND G.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
AND G.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID AND G.START_DATE < SYSDATE
AND NVL(G.END_DATE, SYSDATE) >= SYSDATE
AND (UPPER(R.RESPONSIBILITY_NAME) LIKE '%SUPPORT%AND%SETUP%' OR UPPER(R.RESPONSIBILITY_NAME) LIKE '%TRANSACTION%' OR UPPER(R.RESPONSIBILITY_NAME) LIKE '%CLOSE%SUPPORT%' OR
UPPER(R.RESPONSIBILITY_NAME) LIKE '%IMPLEMENTATION SUPERUSER%') AND F.PROFILE_OPTION_NAME = 'GL_ACCESS_SET_ID' AND F.APPLICATION_ID = V.APPLICATION_ID
AND F.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID AND ASL.ACCESS_SET_ID = V.PROFILE_OPTION_VALUE AND ASL.LEDGER_ID = GLE.LEDGER_ID --AND GLE.OBJECT_TYPE_CODE ='L'
AND V.LEVEL_VALUE = R.RESPONSIBILITY_ID AND U.USER_NAME = 'RGBK74'
AND R.RESPONSIBILITY_ID = 56602;