We extend contractor term date in OIM to 80 days but some times it gets extended by admins/managers more than 80 days. When it gets extended, OIM creates a request id. Now, we would like to know all the users who term date is more than 80 days from the day(request creation date) they got extended.
Is there a way to get the details of the users and the request creation date that happened on termination date attribute in a SQL query so that we can create a BI report.
As i have a requestid which was created yesterday i am using it for developing the query. I tried below query by joining usr, request and request_beneficiary tables but it doesn't return anything. Are there any other tables which i need to use to accomplish this use case.
-- Even try with specific requestid req3.request_id=123456
-- Tried with the request id's beneficiary key too.
SELECT
req3.request_key rk,
usr2.usr_login buid,
usr2.usr_status,
req3.request_creation_date,
req3.request_model_name,
to_char(usr2.usr_udf_terminationdate, 'MM-DD-YYYY') AS Terminationdate
FROM
request req3,
request_beneficiary reqb1,
usr usr2
WHERE
req3.request_key = reqb1.request_key
AND beneficiary_key = usr2.usr_key
and usr2.usr_status = 'Active'
AND usr2.usr_emp_type IN ( 'Contractor');
If anyone has done this type of use case. can you please provide your inputs.
Appreciate your inputs and suggestions
Thanks in advance.
I'm sure you've already figure this out, but here is some SQL that should get you to the data you need.
SELECT r.request_key rk,
R.Request_Creation_Date,
Red.Entity_Field_Name,
Red.Entity_Field_Value,
usr_status,
usr_end_date,
usr_udf_terminationdate
FROM request r
INNER JOIN Request_Entities re
ON R.Request_Key = re.request_key
INNER JOIN Request_Entity_data red
ON re.request_entity_key = red.request_entity_key
INNER JOIN usr
ON Re.Entity_Key = usr.usr_key
WHERE request_model_name = 'Modify User Profile';