Need to fetch changes attribute from assignments history in OTBI Report

Anup Karmankar

Hi Team

I want to fetch changes attribute from assignments history in OTBI Report.

Which attribute its help to fetch it from assignments event subject area.

Nathan CCC

Anup,

Yes you can build a query from subject area Workforce Management - Worker Assignment Event Real Time https://docs.oracle.com/en/cloud/saas/human-resources/24c/faohb/Workforce-Management--Worker-Assignment-Event-Real-SA-4.html#Workforce_Management__Worker_Assignment_Event_Real_SA_4_d1e20594 either by a) using an analysis in OTBI or b) using a report in OTBI with a data model with a data set with data source Oracle BI EE or data source is an analysis.

Yes this subject area includes the actions for each of the changes over time to the assignment(s) for work relationship(s) for a worker. This is the data you see in the table at the bottom in section "Employment History" of page "Employment Info" in work area "Personal Information" in navigation menu folder Me. The actions include events like Hire, Assignment Change, Manager Change etc.

The attribute in this subject area for the action of the change event is

"Assignment Event Details"."Action Code"

Regards, Nathan

Anup Karmankar

Hi Nathan,

Thank you for your help.

I have check action code but it is not giving other details like location, grade or cost code change.

Do we having any other attribute to find out above changes.

Thanks

Anup Karmankar

I need this attribute details in report

Nathan CCC

Hi,

Yes it does give you the changes to values like location, grade etc.

So the query will return 1 row for each version of an assignment over time. So a worker assignment is unique identified by 5 attributes; assignment id, effective start day, effective end day, effective sequence number, effective latest change. All the versions of an assignment over time share the same assignment id but will have different effective start and end days, and if you make multiple changes on the same day different sequence numbers, with the last sequence number each day flag as latest change = Y not N. This subject area is hard coded in its where clause to only give you the latest change each day so you can ignore the last 2 attributes in the unique identifier so when using this subject area a row is unique on only 3 attributes; assignment id, effective start date, effective end date.

So if worker assignment X was grade A on Mon then updated to grade B on Tue then your query will return 2 rows with the value in the attribute for grade in row 1 will have value A with end date Mon and in row 2 will have value B with start date Tue.

If you want to know which attributes were updated if the action was for example an assignment change then you must I assume compare the values in the row you are on to the previous row grouped by assignment ID order by date. Something alone the lines like "case when (max("grade" by "assignment id") <> "grade") then "this grade is different to the grade in a version of this assignment in the past or future" end)

Anup Karmankar

Hi Nathan,

When there is any change in department name then it is showing action code as assignment change but I want show changes attribute as department change so how can we show this in our report.

Thanks

Anup Karmankar

Action code attribute is not working out for this requirement

Nathan CCC

Hi Anup, Yes I understood your requirement. Action code tells you what type of change was made to the assignment. So if it is manager change then you know it is just the few manager attributes. But if it is assignment change then it can be one or more of many attributes. And you understand what is the data you get using the example if you query this history subject area. So you have the data you require. You just need to format it to present it in a different way. So to meet your requirement using the data in this subject area then I think you need to implement a solution where you must compare the values of all attributes you are interested in in the current row to the values in the previous row which is the last version of this assignment over time in the past to see if the value for each attribute has changed or not in this version of the assignment compared to the previous version of this assignment. Good hunting! Nathan