Tuesday, August 12, 2014

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.

Please also see
Simplified Way to Provide a Page Access in PeopleSoft
Understanding Dynamic prompts in PeopleSoft
Implementing parallel processing using Application Engine in PeopleSoft
Adding and Maintaining Person Of Interest in PeopleSoft
PeopleSoft Set Control Field
Peoplesoft Row Level Security Search Records
SQL Query for Max Effective (MAX (EFFDT) dated row from JOB table
PeopleSoft Set Control Field Concept and Tableset Sharing 
Understanding Future dated security in PeopleSoft 
PeopleSoft 9.1 Person Model
PeopleSoft HRMS Online Training

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

Please also see
'Override Position Data' and 'Use Position Data' Option in Job Data Page
Hiring a Person in PeopleSoft
How to find the List of Users Assigned to a Role
FTE For Multiple Jobs in PoeopleSoft
PeopleSoft 9.1 Person Model
How to Resolve a Row Level Security Issue in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft
SQL Query to find all the Direct Reports for a Manager 
Understanding Component Interface in PeopleSoft 
How the FTE is Calculated in PeopleSoft
 

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.

Please also see
Simplified Way to Provide a Page Access in PeopleSoft
PeopleSoft Set Control Field
Adding and Maintaining Person Of Interest in PeopleSoft
Unable to See Future dated transactions in Job Data page in PeopleSoft
Hiring a Person in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft
Understanding Component Interface in PeopleSoft
SQL Query to find all the Direct Reports for a Manager 
How to find the List of Users Assigned to a Role
How the FTE is Calculated in PeopleSoft
Understanding Future dated security in PeopleSoft
How to Resolve a Row Level Security Issue in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft 
'Override Position Data' and 'Use Position Data' Option in Job Data Page 
FTE For Multiple Jobs in PoeopleSoft 


Want to learn PeopleSoft Core HR module thoroughly? I have several videos with total duration of 12 hours.


Following is the link to the YouTube videos Core HR
Click here to see course contents

Click here to know how it works

However, if you want to save money by purchasing whole module instead of in parts then visit this page to get more details PeopleSoft Functional and technical online training

5 comments:

  1. good one. thanks.

    ReplyDelete
  2. Good Explanation santhosh. Thank you very much

    ReplyDelete
  3. Good Explanation. Thanks :)

    ReplyDelete
  4. Good explanation. Thanks.

    ReplyDelete
  5. Curious situation...we have TER rows followed by extensions in some cases where employees have had future dated terminations (status is set to inactive and terminated with pay) but then had contracts extended. HR will add the extensions which are dated after the TER row (however they are set to inactive and terminated with pay...because of the previous TER row) but when compensation tries to delete the TER, it won't allow it since it is not the final row in Job Data.

    By fluke, I found that if I attempt to delete, invoke the error that says "The Termination row cannot be deleted because the controlling instance is terminated as of '###-##-##' (18180,60)" and then click the SAVE button, I will generate a second WARNING which indicates "Warning -- Badge data will be updated for this employee. (100, 1513)
    The Effective Dated row which was inserted in Badge Table when employee was Terminated will be removed upon successful save."

    The strange thing is...we never even touch the badge table and there is no data whatsoever stored there. Do you know of ANY reason this might be happening? When terminating a person via Integration Broker or directly online...what is triggered and where does the effective date for that termination get stored?

    Any help or insight you could provide would be greatly appreciated :O)

    ReplyDelete