Pending worker report

Question

I am new to reporting, and I have to make a report using BI Publisher data modeler in SQL.

When I put effective start date later than today and DATE_START later than today, I get back both pending workers and employee records for the same person. Both records have start dates in the future.

Why is this the case and how do I identify who is actually the 'pending worker'?

How do I get back 'Pending Workers' that are yet to be hired as of today?

If possible, please provide the SQL or logic for the above. Thank you in advance for your response.

Bhaskar Konar · Answer

Hi @Sachin P,

Can you please share the report catalog / query so that we can replicate the issue in our instances and also can you share the requirement like you want the data as of some effective date like if you are executing the report today you want the records as of today etc.

Thank you.

Sachin P · Answer

Thanks Bhaskar and I appreciate it. This is how I was able to get the Pending workers in the first place. However, the report contains two rows of the same person, one as the pending worker and as employee. This is to identify those employees and pending workers with expiring visa status. Can you help in this logic

Bhaskar Konar · Answer

Hi @Sachin P,

Welcome to the Oracle Analytics & AI Community!

Pending workers are stored in the PER_ALL_ASSIGNMENTS_M table with an ASSIGNMENT_TYPE of ‘P’. Once they are converted to employees, the ASSIGNMENT_TYPE is updated to ‘E’

So possibly you can print the column 'ASSIGNMENT_TYPE' in your query or put a filter on it if you are only looking for Pending Workers accordingly.

Hope this help.

Thank you!