Current logged in User Query for SAP B1 on HANA

[former_member184146]

Hi Experts,

               I am trying to achieve the current logged in user detail , below query  i tried on SAP B1 on SQL and it is working fine

SELECT T0.U_NAME FROM OUSR T0 WHERE T0.INTERNAL_K = $[USER]

but it is not working in B1 on HANA , I tried the below query

SELECT T0."U_NAME" FROM OUSR T0 WHERE T0."INTERNAL_K" = (SELECT CURRENT_USER FROM DUMMY)

any help will be appreciate

Regards,

Manish

[kothandaraman_nagarajan · Accepted solution]

Hi,

Please refer thread

Thanks & Regards,

Nagarajan

[former_member184146 · Accepted solution]

Hi Nagarajan,

                    That thread worked  ,Thanks a lot.

Regards,

Manish

[former_member205766]

Hi

Check the below link

With Regards

Balaji Sampath

[former_member184146]

Hi Balaji,

               Thanks for the response , I tried the below query as per Prasanna query

SELECT r."UserCode",

    (SELECT

        CASE

            WHEN "Action" = 'I' THEN 'OPEN'

            WHEN "Action" = 'O' THEN 'CLOSED'

        END

    FROM USR5 r1

    WHERE r1."UserCode" = r."UserCode" AND r1."Date" = r."Date" AND r1."Time" = MAX(r."Time")) AS "Login Status",

    r."Date", "ClientName", MAX(r."Time") AS "Login Time"

FROM USR5 r

WHERE r."ProcName" = 'SAP Business One.exe' AND CAST(r."Date" AS varchar(50)) = CAST(NOW() AS varchar(50))

GROUP BY r."UserCode",r."Date", "ClientName" HAVING MAX("Time") = MAX(r."Time")

but am not getting any result,query is running but with no result.

Regards,

Manish

[former_member205766]

Hi

Try the below it is worked on my system

select UserCode ,

(select case when Action = 'I' then 'OPEN' when Action = 'O' then 'CLOSED' end  from USR5 r1

where r1.UserCode = r.UserCode and r1.Date = r.Date and r1.Time = Max(r.Time)) [Login Status]

, DATE, ClientName, Max(r.Time) [Login Time]

from USR5 r where ProcName = 'SAP Business One.exe'  and CONVERT(VARCHAR(50), r.DATE ,103) = CONVERT(VARCHAR(50), getdate() ,103)

group by

UserCode , DATE, ClientName

having MAX(time) = Max(r.Time)

With Regards

Balaji Sampath

[former_member184146]

Hi,

   Not working on HANA  and that is the issue only and the above query giving the result of all users , i want only the logged in user name for the system on which am running the query.

--Manish

[former_member184146]

Thanks Balaji for your efforts.

Regards,

Manish