All Tcode value is not displaying

[PreethiPandi]

Hi Team,

We are trying to fetch the Tcode information from the request(request type: Emergency Session request) but we got only one tcode (ME22N)

Please find the below query:

SELECT DISTINCT
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS REQUESTNUMBER ,
AR.ENDPOINTASCSV AS 'ENDPOINT NAME',
concat(U.USERNAME,'[',U.FIRSTNAME,' ',U.LASTNAME,']') AS 'USERNAME',
A.NAME AS 'ACCOUNTNAME',
EV.ENTITLEMENT_VALUE AS 'TCODE' ,
RAA.REQUEST_ACCESS_KEY as 'ReqAccessKey',
RAA.ATTRIBUTE_VALUE as Ent_Keys,
(select GROUP_CONCAT(ENTITLEMENT_VALUE) from entitlement_values where ENTITLEMENT_VALUEKEY IN (RAA.ATTRIBUTE_VALUE)) as 'Entitlement_Value_key',
EV.ENTITLEMENT_VALUE
FROM ARS_REQUESTS AR LEFT JOIN REQUEST_ACCESS RA ON AR.REQUESTKEY=RA.REQUESTKEY
left join request_access_attrs RAA on RAA.request_access_key=RA.REQUEST_ACCESSKEY
LEFT JOIN USERS U ON RA.USERKEY=U.USERKEY
LEFT JOIN ACCOUNTS A ON RA.ACCESSKEY=A.ACCOUNTKEY
LEFT JOIN ENTITLEMENT_VALUES EV ON RAA.ATTRIBUTE_VALUE=EV.ENTITLEMENT_VALUEKEY
WHERE RAA.ATTRIBUTE_NAME LIKE '%TCODE%' AND AR.REQUESTTYPE='23' and SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) = '24908132';

o/p:

If we are passing the entitlement value key directly we are getting the values,

select GROUP_CONCAT(ENTITLEMENT_VALUE) as 'tcode' from entitlement_values where ENTITLEMENT_VALUEKEY IN (12598529,12618579,12712090,12616869,12598603,12712076);

Please help us on this

[nimitdave]

@PreethiPandi Pls try with below and modify as required.

select ar.REQUESTKEY,ar.REQUESTTYPE,ar.STATUS,et.DISPLAYNAME ,ev.ENTITLEMENT_VALUE,ev.DISPLAYNAME
from ars_requests ar
inner join request_access ra on ra.REQUESTKEY =ar.REQUESTKEY
inner join entitlement_values ev on ra.ACCESSKEY =ev.ENTITLEMENT_VALUEKEY
inner join entitlement_types et on ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY
where SUBSTRING_INDEX(ar.JBPMPROCESSINSTANCEID, '.', - 1) = '<reqid>';

[PreethiPandi]

@nimitdave I tried the query which you have posted. But still Tcode Value is not displaying

[nimitdave]

@PreethiPandi , what the above is returning in your case , if its the emergency role then you can join with role_entitlements., to get the SAP roles for the emergency access  and then join with entitlements2 to get the tcodes.

The query I shared will give tcodes if tcodes are requeted directly in the request.

[PreethiPandi]

@nimitdave This is was it is returning