December 28, 2012

Get e-mail addresses of the users of an ABAP system

ABAP users and its person number are stored on the table USR21, the email addresses (stored in table ADR6) in SAP are mapped through the person numbers to the users. So in order to obtain the e-mail addresses that are related to a user, we have to obtain its person number and obtain the e-mail addresses of ADR6 mapped to that number. It could be performed by a simple query with a select from multiple tables but I found more useful this time to perform the selection using the cross apply function.


Select MANDT, BNAME, p.PERSNUMBER, b.SMTP_ADDR
from psq.USR21 p
cross apply (Select * from tst.ADR6) b
where b.PERSNUMBER = p.PERSNUMBER and b.CLIENT = p.MANDT