AP -SM Suppliers

select *
 
from (select distinct
       BOOK.SET_OF_BOOKS_ID,
       ORG.ORGANIZATION_ID ORG_ID,
       substr(USE.DESCRIPTION, 1, 3) ETABLISSEMENT,
       USE.USER_NAME,
       USE.EMAIL_ADDRESS,
       BOOK.SHORT_NAME,
       ORG.NAME ORGNAME,
       substr(USE.DESCRIPTION,instr(USE.DESCRIPTION,' ')+1) NOM,
       decode(substr(RTL.RESPONSIBILITY_NAME, 1, 1), '1', 'GL', '2', 'AP', '3', 'AR', '4', 'AP', '5', 'PO', '7', 'IEX', '9', 'FA', 'AUTRES') MODULE,   
       RTL.RESPONSIBILITY_NAME,
       trunc(USE.LAST_LOGON_DATE) LAST_LOGON,
       USE.START_DATE, -- Date de début de validité
       USE.END_DATE,
       USE.USER_ID,
       USE.FAX
--       MEN.MENU_NAME
  from
 
       APPS.GL_SETS_OF_BOOKS            BOOK,
       APPS.HR_ORGANIZATION_INFORMATION INF,
       APPS.HR_ALL_ORGANIZATION_UNITS   ORG,
 
       APPS.FND_PROFILE_OPTION_VALUES   VAL,
       APPS.FND_PROFILE_OPTIONS         OPT,
 
       APPS.FND_MENUS                   MEN,
       APPS.FND_RESPONSIBILITY_TL       RTL,
       APPS.FND_RESPONSIBILITY          RES,
       APPS.FND_USER_RESP_GROUPS        RGR,
       APPS.FND_USER                    USE
  where
--      USE.USER_ID > 1000
--      USE.USER_NAME           like :USER_ 
--        substr(RTL.RESPONSIBILITY_NAME, 1, 1) in ('1', '2', '3', '4', '5')   
        USE.USER_ID                = RGR.USER_ID
    and RGR.RESPONSIBILITY_ID      = RES.RESPONSIBILITY_ID
    and RES.RESPONSIBILITY_ID      = RTL.RESPONSIBILITY_ID
    and RTL.LANGUAGE               = 'F'
    and    (RES.END_DATE is null or RES.END_DATE >= sysdate)
    and    (USE.END_DATE is null or USE.END_DATE >= sysdate)
    and    (RGR.END_DATE is null or RGR.END_DATE >= sysdate)
    and RES.MENU_ID                = MEN.MENU_ID
 
    and OPT.PROFILE_OPTION_ID   = VAL.PROFILE_OPTION_ID
    and OPT.APPLICATION_ID      = VAL.APPLICATION_ID
    and VAL.LEVEL_ID            = 10003 -- Niveau responsabilité
    and VAL.LEVEL_VALUE         = RES.RESPONSIBILITY_ID
 
--    and OPT.PROFILE_OPTION_NAME = 'ORG_ID'
 
    and OPT.PROFILE_OPTION_NAME = decode(RES.APPLICATION_ID, 200, 'ORG_ID', 222, 'ORG_ID', 201, 'ORG_ID', 178, 'ORG_ID', 695, 'ORG_ID', 140, 'ORG_ID', 101, null)
--    and OPT.PROFILE_OPTION_NAME  = decode(RES.APPLICATION_ID, 101, 'GL_SET_OF_BKS_ID', null)
 
    and VAL.PROFILE_OPTION_VALUE = to_char(ORG.ORGANIZATION_ID)
    and ORG.ORGANIZATION_ID                 = INF.ORGANIZATION_ID
    and INF.ORG_INFORMATION_CONTEXT         = 'Operating Unit Information'
    and INF.ORG_INFORMATION2 is not null
    and INF.ORG_INFORMATION3                = to_char(BOOK.SET_OF_BOOKS_ID))
) o103234
 WHERE (NVL(o103234.SET_OF_BOOKS_ID,0) = APPS.GL_SECURITY_PKG.LOGIN_SOB_ID)