How the Various Date Fields on Employement Data Page are Updated

In recent past, I was asked to prepare a report which should have list of all the new joiners since 1st Jan 2014 and I positively nodded to do the same as it appeared quite easy. I instinctively started writing max effective dated query on JOB record with use of Action 'HIR' as additional criteria hoping to get the desired result.

But it wasn't as easy as I thought it would be since there were few scenarios for which the query wasn't working. Then suddenly three fields 'Last Start Date', 'First Start Date' and 'Original Start Date' on the Employment Data page drew my attention. I did  little analysis as to which one of these three will serve my need . We will talk about each of these these three fields in detail and explain how they perfectly sorted out my problem.

'Last Start Date' Field in PeopleSoft

This field gets updated with the value of EFFDT field on Job Data page when the Action 'HIR' is used i.e when a person is hired and also in the subsequent instances whenever Action 'REH' is used i.e whenever the person is rehired in the organization and also, this field cannot be overridden. Well, it became clear that this particular field suits my need perfectly as it tracks the latest start date i.e hire as well as rehire date in case the person has been rehired. Hence, I quickly replaced my SQL with this one:

SELECT J.EMPLID FROM PS_JOB J
 WHERE J.LAST_HIRE_DT >= '<Enter the Date>'
   AND J.ACTION IN ('HIR', 'REH')
   AND EXISTS (SELECT 'X' FROM PS_JOB J1
                WHERE J1.EMPLID = J.EMPLID
                  AND J1.HR_STATUS = 'A'
                  AND J1.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2
                                   WHERE J2.EMPLID = J1.EMPLID
                                     AND J2.EMPL_RCD = J1.EMPL_RCD
                                     AND J2.EFFDT <= SYSDATE
                                 )

                  AND J1.EFFSEQ = (SELECT MAX(J3.EFFSEQ) FROM PS_JOB J3
                                    WHERE J3.EMPLID = J1.EMPLID
                                      AND J3.EMPL_RCD = J1.EMPL_RCD
                                      AND J3.EFFDT = J1.EFFDT
                                  )
               )

   The above query will return all the new joiners since a particular date.

'First Start Date' Field in PeopleSoft

This Field is updated only when Action 'HIR' is used in Job Data page i.e when the person is first hired in the organization. It gets defaulted with the value of EFFDT field on the Job Data page and remains unchanged in subsequent transactions including rehires. It cannot be overridden.

'Original Start Date' Field in PeopleSoft

This particular field gets defaulted with the value of EFFDT on the Job Data page but can be overridden. As far as I understand, the purpose of having this field is to track the hire date which HR Admins want to keep different from the hire date stored in the field 'First Start Date'


There are other fields too in the list that are updated in similar fashion at different levels and we will try to understand the purpose of having them in PeopleSoft with a live scenario. Note that all these fields are updated at two different levels Organizational Instance and Organization Assignment.
 

Get more details on these two entities at the link - PeopleSoft 9.1 Person Model

Bobby Singh joined company XYZ on February 2, 2002. At the end of 2006, company ABC bought company XYZ; it completed the takeover on January 1, 2007. When the HR administrator at company ABC first enters Bobby in the system, he creates a job data record with the action of HIR and an effective date of January 1, 2007, reflecting Bobby's hire date with the new company.Though the field ‘Original Start Date’ gets defaulted to ‘01/01/2007’, HR admin overrides it to the EFFDT when Bobby actually joined the organization XYZ which later on bought by ABC, i.e. 02/02/2002.
The field 'Org Instance Service Date' is used to calculate the Service period for an employee and gets defaulted with the value of the field EFFDT on Job data but it can be override on the Employment Data page in case HR admin wants to keep the Service date different.

After one year, Bobby gets terminated effective from January 2 2008 and the action affects two fields (Highlighted in red) as shown below:
 

In the following year Bobby again gets rehired in the organization effective from January 5 2009 which affects below fields (Highlighted in red): 

After two years Bobby gets an additional assignment in the same organization effective from May 10 2011. The action affects below fields (Highlighted in red) 

Did you observe something here as to how all these fields are getting updated at two different levels?

Whenever we use any of the following Actions 'HIR', 'REH', ADD and 'TER', the fields at both levels are updated since these actions have direct impact on the Organizational Instance.

Whenever we use any of the following Actions 'ADL', 'ASG' and 'ASC', the fields at Organizational Assignment level are updated since these actions have their impact on the Organizational Assignment only.