Versions Compared

Version Old Version 1 New Version 2
Changes made by

Anna Zelenskaya

Anna Zelenskaya

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Show if
groupIdse45c9fb3-68ab-4c08-972a-bdd4f555637e
users
matchUsingany

This section is only visible for the Adonis employees.

Customer

The view was originally created for Eckero Group

Page Properties

Summary

view View to show all active main contracts' information for crew

Keywords

contracts Contracts datagroup

Category

Crew List View

Description

View to show all active main contracts' information for crew.

View Sample

...

Main Data Selection

Select only persons where employment state = employed, and where employment start is less than today, and end date is greater than today and/or null.

Select only contract = Main contract. Ordered by newest contract on top.
Scenario: Possibility to view all active main contracts (active = not old/terminated ones) for employees in your organization (user privileges in ACC: the user should not be able to see all contracts only contracts for the employees linked to companies where the user has access to in Organization tab > ‘Can Open Client’ column).

Ignore amendments.

SQL statement

Please remember to update the View ID in the view from dbo.PW001SRVXX to the correct one.

sql select statement
Expand
Code Block
titlesql select statement
Code Block
CREATE VIEW dbo.PW001SRV30 as
SELECT
P01.PIN,
P01.NAME,
C02.NAME AS RANK,
C32.TEXT AS CURRENT_CONTRACT_TYPE,
C02C.NAME AS CURRENT_CONTRACT_RANK,
--P20.DATESTART AS CURRENT_CONTRACT_START
Cast(P20.DATESTART as date) CURRENT_CONTRACT_START,
Cast(P20U.ENDDATE as date) CURRENT_CONTRACT_END,
Cast(P20.TRIALPERIODEND as date) CURRENT_CONTRACT_TRIALPERIODEND_END,
p01.Client AS NUMORGID,
p01.EMPLOYMENTSTARTDATE,
p01.EMPLOYMENTENDDATE
FROM PW001P01 P01
LEFT JOIN PW001C02 C02
	ON C02.CODE = P01.RANK
JOIN PW001P20 P20
	ON P20.PIN = P01.PIN
	AND P20.HISTORICAL = 'F'
	AND P20.CONTRACTTYPE IS NOT NULL 
	--AND P20.CONTRACTTYPE = 'PERM'
	AND CONTRACTKIND = 0
LEFT JOIN PW001C02 C02C
	ON C02C.CODE = P20.RANK
LEFT JOIN PW001P20U P20U
	ON P20U.CONTRACT_SEQNO = P20.SEQUENCENO
LEFT JOIN pw001c32 c32
		ON p20.CONTRACTTYPE = c32.CODE

WHERE P01.EMPLOYMENTENDDATE IS NULL

Columns Specification

Column

Description/ Location in APM

PIN

Select from Personal Details > Personal > Pin

Start Page

...

NAME

LastName FirstName (no comma, only space)

Select from Personal Details > Personal > Name

RANK

Select from Personal Details > Employment, Rank, Current Rank; name of current rank

EMPLOYMENT START DATE

Select from Personal Details > Employment > Start

Format dd/mm/yyyy

EMPLOYMENT END DATE

Select from Personal Details > Employment > End

Format dd/mm/yyyy

CURRENT CONTRACT TYPE

[Select from Datagroup Contract, Details, Type; name of type]

CURRENT CONTRACT RANK

[Select from Datagroup Contract, Details, Current Rank; name of rank]

CURRENT CONTRACT END

[Select from Datagroup Contract, Other info, Customized field "ENDDATE" (Contract End Date); value of end date]

CURRENT CONTRACT TRIAL PERIOD END

[Select from Datagroup Contract, Current Main contract; trial period end date]

Start Page

N/A

Other