Versions Compared

Version Old Version 1 New Version Current
Changes made by

Daria Chebotaieva

Helen Ustych

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
minLevel1
maxLevel1
typeflat
separatorpipe
Page Properties

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.

Expand
Code Block
CREATE VIEW dbo.PW001SRVXXX
as
select 
	p01.Pin,
	p01.Name 'Full Name',
	p01.FIRSTNAME 'FisrFirst 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

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.

...