Main Contracts

Owned by Anna Zelenskaya, created
Last updated: Dec 22, 2023
2 min read
1 Description | 2 View Sample | 3 Main Data Selection | 4 SQL statement | 5 Columns Specification | 6 Start Page | 7 Other

Summary

View to show all active main contracts' information for crew

Keywords

Contracts datagroup

Category

Crew List View

Description

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

View Sample

image-20231221-185829.png

 

Main Data Selection

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

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

Select only contract = Main contract. Ordered by newest contract on top.
Scenario: Possibility to view all active main 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).

Ignore amendments.

SQL statement

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

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

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 Contracts > Details> Type

name of type

CURRENT CONTRACT RANK

Select from Datagroup Contracts > 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

Format dd/mm/yyyy

CURRENT CONTRACT TRIAL PERIOD END

Select from Datagroup Contract > Current Main contract > trial period end date

Format dd/mm/yyyy

Start Page

N/A

Other