Versions Compared

Version Old Version 1 New Version Current
Changes made by

Anna Zelenskaya

Anna Zelenskaya

Saved on

Key

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

...

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).

...

Expand
titlesql select statement
Code Block
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

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 , Details, Type; > Current Sub contract Details > Type

name of type]

CURRENT CONTRACT RANK

[Select from Datagroup Contract , Details, > Current Sub 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 Sub contract > Start

Format dd/mm/yyyy

CURRENT CONTRACT TRIAL PERIOD END

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

Format dd/mm/yyyy

Start Page

N/A