Summary	The view shows all the active crew members' employment jubilee data.
Keywords	Employment, Jubilee
Category	Crew List View

Description

The view shows all the active crew members' employment jubilee data.

The view contains the number of years they worked for the company in the current year and the jubilee celebration date.

View Sample

Main Data Selection

All active persons who worked in the company for more than a year within the current year.

SQL statement

Please remember to replace XXX in the view name dbo.PW001SRVXXX to the correct view id.

CREATE VIEW dbo.PW001SRVXXX
as
select 
	p01.Pin,
	p01.Name 'Full Name',
	p01.FIRSTNAME 'First Name',
	p01.LASTNAME 'Last Name',
	p01.MIDDLENAME 'Middle Name',
	CONVERT(DATE, p01.BIRTHDATE, 104) as Birthday,
	POS.NAME as 'Rank',   
	Year(p01.birthdate) 'Year of Birth',
	dateadd(yy,datediff(yy,p01.employmentstartdate,getdate()),employmentstartdate) as 'Celebration Date',
	Datediff(yy,p01.employmentstartdate,getdate()) 'Years Worked',
	datename(month,p01.employmentstartdate) 'Jubilee Month',
	p01.Client Numorgid,
	p01.EmploymentStartDate,
	p01.EmploymentEnddate
 from pw001p01 p01 
 left join pw001c02 pos on pos.code=p01.rank
 left join pw001p0p p0p on p0p.pin=p01.pin and p0p.pnumber='A' 
 left join pw001p1r p1r on p1r.pin=p01.pin and rateno=1 
where p01.EmploymentStartDate is not null and p01.EmploymentEnddate is null
and Datediff(yy,p01.employmentstartdate,getdate()) >0

Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
PIN	The PIN number from the Personal Details.
Full Name	The Full Name from the Personal Details.
First Name	The First Name from the Personal Details.
Middle Name	The Middle Name from the Personal Details.
Last Name	The Last Name from the Personal Details.
Birthday	The birth date from the Personal Details.
Rank	The Current Rank from the Personal Details.
Year of Birth	The birth year from the Personal Details.
Celebration Date	The jubilee celebration sate in current year.
Years Worked	Number of years worked in the company.
Jubilee Month	The birthday celebration month in current year.
Numorgid	The employment organization id from the Personal Details.
EmploymentStartDate	The employment start date from the Personal Details.
EmploymentEnddate	The employment start end from the Personal Details.

Start Page

Link to the documentation on the related Start Page Tile.

Other

To highlight the passed/upcoming celebration dates the following settings need to be configured.

According to the setting, the Celebration Date will be highlighted in :

yellow one day before the jubilee.
grey for all the past dates.

Adonis HR Reports, Views and Similar

Employment Anniversary/Jubilee (this year)