3

I'm new to BI Publisher and I'm using it through Oracle Fusion Applications.

I am trying to make a report relating to the Inactive Employees in an organization. However I am unable to figure out how to query for an inactive or terminated employee.

I initially used this query:

SELECT PERSON_ID, PERSON_NUMBER, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE

FROM PER_ALL_PEOPLE_F

WHERE TRUNC(SYSDATE) NOT BETWEEN TRUNC(EFFECTIVE_START_DATE) AND TRUNC(EFFECTIVE_END_DATE)

My other considerations were attributes from the PER_ALL_ASSIGNMENTS_M table including PRIMARY_WORK_RELATION_FLAG, PRIMARY_ASSIGNMENT_FLAG and ASSIGNMENT_TYPE considering that the employee's assignment details would help somehow. However I was unsuccessful.

I wanted to know if there was any other proper way to query for inactive employees. Is there any particular attribute in any table which would tell me for certain that an employee is active or terminated? When an employee is terminated in Oracle Fusion, which all table attributes get affected?

Thank you for your help.

George Santhosh
  • 119
  • 2
  • 13

2 Answers2

3

The easiest way to do this is simply :

SELECT * FROM YourTable t
WHERE TRUNC(t.END_DATE) <= trunc(sysdate)

Some times there is also an indication column like IS_ACTIVE or something. You can also consider adding it, and simply updating it to 1 for all the records returned from the above query.

Other then that, we can't really help you. We don't know your table structures, we don't know what data you store in them and which column indicates what .

sagi
  • 40,026
  • 6
  • 59
  • 84
  • I wanted to know if there are any other alternate approaches done in general beside the one I mentioned in the question. As for the 'active' status, I found an EMPLOYMENT_STATUS attribute in BEN_EXTRACT_REQ_DETAILS table. This unfortunately had inconsistent data. If you know of any other useful attributes in HCM tables, it will be helpful. – George Santhosh Aug 30 '16 at 03:54
  • Everyone design they DB differently, there is no generic solution. I told you, just update the `EMPLOYEMENT_STATUS` your self so it will be correct. – sagi Aug 30 '16 at 06:32
3

I have found what i was looking for. ASSIGNMENT_STATUS_TYPE='INACTIVE' was what I needed (As mentioned in the question, this solution is without considering 'EFFECTIVE_END_DATE') Getting the 'latest' assignment status of an employee was what I needed to find. The following query works if the employee has only one Assignment assigned.

 SELECT PAPF.PERSON_ID, PAPF.PERSON_NUMBER
 FROM PER_ALL_PEOPLE_F PAPF, PER_ALL_ASSIGNMENTS_M PAAM

 WHERE 1=1 
 AND TRUNC(PAAM.EFFECTIVE_START_DATE) = (SELECT MAX(TRUNC(PAAM_INNER.EFFECTIVE_START_DATE)) 
                FROM   PER_ALL_ASSIGNMENTS_M PAAM_INNER 
                WHERE  PAAM_INNER.PERSON_ID=PAAM.PERSON_ID 
                GROUP BY PAAM_INNER.PERSON_ID) 

 AND PAPF.PERSON_ID=PAAM.PERSON_ID 
 AND PAAM.PRIMARY_FLAG='Y' 
 AND PAAM.ASSIGNMENT_STATUS_TYPE='INACTIVE' 
 AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE 
 AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE 

 ORDER BY 1 ASC

I had help from the Oracle Support Community to get to an answer. Link: https://community.oracle.com/message/14000136#14000136

However in a case where an employee was given an assignment say starting from year 2000 and ending at 20015, then another assignment starting from 2016 till present, the above query will return one record of the said employee as 'Inactive' if the Max Effective_start_date condition is not checked. (Since one became Inactive on 2015), even though her current Assignment status is 'Active' and she is currently not terminated.

In such a case, it is wise to retrieve the record with the greatest 'EFFECTIVE_START_DATE' from the PER_ALL_ASSIGNMENTS_M table, ie, checking if EFFECTIVE_START_DATE = MAX(EFFECTIVE_START_DATE)

George Santhosh
  • 119
  • 2
  • 13