CODEWITHSUNDEEP
sqloim
suri4u
suri4u

Reputation: 77

Using SQL query - How to identify the attribute's that a OIM request has updated + OIM 11g R2 PS3

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.

Upvotes: 0

Views: 786

Answers (1)

Berkley Lamb
Berkley Lamb

Reputation: 293

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';

Upvotes: 1

Related Questions