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
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.
Please also see
Understanding PeopleSoft Checklists
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
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
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
'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
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.
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
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
good one. thanks.
ReplyDeleteGood Explanation santhosh. Thank you very much
ReplyDeleteGood Explanation. Thanks :)
ReplyDeleteGood explanation. Thanks.
ReplyDeleteCurious 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.
ReplyDeleteBy 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)
Thanks for sharing information, its useful information!
ReplyDeleteBest Employment Agency NYC