Start a Conversation

Unsolved

This post is more than 5 years old

532

March 16th, 2016 07:00

Custom Reports Query - Termed Users w/ Active Access - IMG 6.9.1 (P5)

It appears the forum has changed since the last time I posted, I hope this reaches the correct group, if not, please let me know and I'll repost where appropriate.

So my issue is, there is a built-in report for terminated users (Leavers or Terminated Users), which will run successfully....meaning I get no errors and returns data.  However, it bases its findings on "Is Terminated" = 1 and "Is Deleted" = 0.

Here is the built-in query:

(select DISTINCT tMEU.USER_ID, tMEU.LAST_NAME || ', ' || tMEU.FIRST_NAME as UserName, tMEU.IS_TERMINATED,

tMEU.IS_DELETED, tMEU2.User_ID as Supervisor_ID, pUAA.ENTITLED_TYPE, pUAA.APPLICATION_NAME, pUAA.ACCOUNT_NAME,

pUAA.ENTITLEMENT_NAME

from avuser.PV_USER_ALL_ACCESS pUAA

left join avuser.t_master_enterprise_users tMEU on tMEU.ID = pUAA.ENTITLED_ID

left join avuser.T_MASTER_ENTERPRISE_USERS tMEU2 on tMEU.SUPERVISOR_ID = tMEU2.id

where tMEU.IS_TERMINATED = 1 or tMEU.IS_DELETED = 1)

-----------------------------------------

However, the above does not work for our environment, rather our would be based upon "Is Terminated" = 1 and "Is Disabled" = 0. 

(select DISTINCT tMEU.USER_ID, tMEU.LAST_NAME || ', ' || tMEU.FIRST_NAME as UserName, tMEU.IS_TERMINATED,

tMEU.IS_DISABLED, tMEU2.User_ID as Supervisor_ID, pUAA.ENTITLED_TYPE, pUAA.APPLICATION_NAME, pUAA.ACCOUNT_NAME,

pUAA.ENTITLEMENT_NAME

from avuser.PV_USER_ALL_ACCESS pUAA

left join avuser.t_master_enterprise_users tMEU on tMEU.ID = pUAA.ENTITLED_ID

left join avuser.T_MASTER_ENTERPRISE_USERS tMEU2 on tMEU.SUPERVISOR_ID = tMEU2.id

where tMEU.IS_TERMINATED = 1 or tMEU.IS_DELETED = 1)

I attempted to modify the above query by changing "DELETED" to "DISABLED", but I get an invalid identifier message. See below.

So, my question is how does IMG reference the "Is Disabled" field as shown in the Directory Account list?  Does this make sense?  See below:

No Responses!
No Events found!

Top