I have made it so it only lists one row per user, so it will show only two permission lists:
ONE of the permission lists that gives the user this application designer access
ONE of the permission lists that gives the user this data mover access
If a user has permission lists that give read only and write access, this SQL will show (one of) the write access permission list (even though I think (haven't tested) that the user will have read only, since PeopleSoft tends to prioritize the display-only flag...)
WITH
APP_DESIGNER_ACCESS AS (
SELECT PA.CLASSID AS PERMISSION_LIST
,MAX(PA.AUTHORIZEDACTIONS) MAX_ACCESS
,CASE MAX(PA.AUTHORIZEDACTIONS) WHEN 0 THEN 'NO ACCESS'
WHEN 1 THEN 'READ'
ELSE 'WRITE'
END APP_DESIGNER_ACCESS
FROM PSAUTHITEM PA
WHERE PA.MENUNAME = 'APPLICATION_DESIGNER'
AND BARNAME
<> 'CHANGE_CONTROL'
GROUP BY CLASSID
)
,
DATA_MOVER_ACCESS AS (
SELECT PA.CLASSID AS PERMISSION_LIST
,'YES' AS DATA_MOVER_ACCESS
FROM PSAUTHITEM PA
WHERE PA.MENUNAME = 'DATA_MOVER'
GROUP BY CLASSID
)
------------------------------------------------------------------------------------------------------------------------------
SELECT OPC.OPRID
,OPD.OPRDEFNDESC
,OPD.LASTSIGNONDTTM
,NVL(TRUNC(SYSDATE) - TRUNC(OPD.LASTSIGNONDTTM),99999) AS
LAST_SIGNON_DAYS_AGO
,CASE OPD.ACCTLOCK WHEN 1 THEN 'LOCKED' ELSE 'OPRID ACTIVE' END AS ACCOUNT_STATUS
,NVL(OPC.APP_DESIGNER_ACCESS,'NO ACCESS') AS
APP_DESIGNER_ACCESS
,NVL(OPC.DATA_MOVER_ACCESS,'NO') AS
DATA_MOVER_ACCESS
,OPC.APP_DESIGNER_PERM_LIST AS
SAMPLE_APP_DESIGNER_PERM_LIST
,OPC.DATA_MOVER_PERM_LIST AS
SAMPLE_DATA_MOVER_PERM_LIST
FROM (
------------------------------------------------------------------------------------------------------------------------------
SELECT OC.OPRID
,MAX(APPD.APP_DESIGNER_ACCESS) as
APP_DESIGNER_ACCESS
,MAX(DMA.DATA_MOVER_ACCESS) as
DATA_MOVER_ACCESS
,NVL(MAX(APPD.PERMISSION_LIST), 'NONE') as
APP_DESIGNER_PERM_LIST
,NVL(MAX(DMA.PERMISSION_LIST) , 'NONE') as
DATA_MOVER_PERM_LIST
FROM PSOPRCLS OC
,(SELECT OC.OPRID
,MAX(AD.PERMISSION_LIST) AS PERMISSION_LIST
,MAX(AD.APP_DESIGNER_ACCESS) AS
APP_DESIGNER_ACCESS
FROM
APP_DESIGNER_ACCESS AD
,PSOPRCLS OC
WHERE AD.PERMISSION_LIST = OC.OPRCLASS
AND AD.MAX_ACCESS = (SELECT MAX(SAD.MAX_ACCESS)
FROM
APP_DESIGNER_ACCESS SAD
WHERE SAD.PERMISSION_LIST = OC.OPRCLASS
)
GROUP BY OC.OPRID
) APPD
,(SELECT OC.OPRID
,MAX(DM.PERMISSION_LIST) AS PERMISSION_LIST
,MAX(DM.DATA_MOVER_ACCESS) AS
DATA_MOVER_ACCESS
FROM
DATA_MOVER_ACCESS DM
,PSOPRCLS OC
WHERE DM.PERMISSION_LIST = OC.OPRCLASS
GROUP BY OC.OPRID
) DMA
WHERE APPD.OPRID (+) = OC.OPRID
AND
APPD.PERMISSION_LIST (+) = OC.OPRCLASS
AND
DMA.OPRID (+) = OC.OPRID
AND
DMA.PERMISSION_LIST (+) = OC.OPRCLASS
GROUP BY OC.OPRID
------------------------------------------------------------------------------------------------------------------------------
) OPC --select* from dual
, PSOPRDEFN OPD
WHERE (OPC.APP_DESIGNER_ACCESS
|| OPC.DATA_MOVER_ACCESS) IS NOT NULL
AND OPD.OPRID = OPC.OPRID
------------------------------------------------------------------------------------------------------------------------------
;
Hope it helps!
No comments:
Post a Comment