Summary	View to show all active sub contracts' information for crew
Keywords	Contracts datagroup
Category	Crew List View

Description

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

View Sample

Main Data Selection

All crew members employed under the current organization and units below that have at least one sub contract record in PW001P20 (Contracts datagroup).

Select only persons where employment state = employed, and where employment start is less than today.

Select only contract = Sub contract. If several - ordered by newest contract on top.
Scenario: Possibility to view all active sub contracts (active = not 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).

SQL statement

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

CREATE VIEW dbo.PW001SRV31 as 
SELECT 
p01.PIN,
p01.NAME,
c02.NAME AS RANK,
p01.EMPLOYMENTSTARTDATE,
p01.EMPLOYMENTENDDATE,
c32.TEXT as CURRENT_SUB_CONTRACT_TYPE,
c02a.NAME AS CURRENT_SUB_CONTRACT_RANK,
p20.DATESTART as CURRENT_SUB_CONTRACT_START,
p20.DATEEND as CURRENT_SUB_CONTRACT_END,
p01.Client AS NUMORGID
FROM pw001p01 p01
       LEFT JOIN pw001c02 c02
               ON  p01.RANK = c02.CODE
              JOIN pw001p20 p20
                   ON  p20.PIN = p01.PIN
                   AND p20.HISTORICAL = 'F' AND p20.CONTRACTKIND = 1
				   AND p20.DATEEND >= DATEADD(d,DATEDIFF(d,0,getdate()),0)
	   LEFT JOIN pw001c32 c32
		ON p20.CONTRACTTYPE = c32.CODE
	   LEFT JOIN pw001c02 c02a
               ON  c02a.CODE = p20.RANK WHERE  p01.employmentenddate IS NULL 
			AND (p01.employmentstartdate <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) 
			AND ISNULL(p01.employmentenddate,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
PIN	Select from Personal Details > Personal > Pin
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 > Employment State > Start Format dd/mm/yyyy
EMPLOYMENT END DATE	Select from Personal Details > Employment > Employment State > End Format dd/mm/yyyy
CURRENT CONTRACT TYPE	Select from Datagroup Contract > Current Sub contract Details > Type name of type
CURRENT CONTRACT RANK	Select from Datagroup Contract > Current Sub contract Details > Current Rank name of rank
CURRENT CONTRACT END	Select from Datagroup Contract > Current Sub contract > Start Format dd/mm/yyyy
CURRENT CONTRACT TRIAL PERIOD END	Select from Datagroup Contract > Current Sub contract > End Format dd/mm/yyyy

Start Page

N/A

Adonis HR Reports, Views and Similar

Sub Contracts