Planned Position changes (promotions)
1 Description | 2 SQL Statement | 3 Field Specification
Summary | The view shows crew with planned promotions. |
|---|---|
Keywords | promotion |
Description
The view shows the crew who have planned promotions performed in the Rotation Planning Module.
SQL Statement
CREATE VIEW dbo.PW001SRV230
AS
SELECT
P01.PIN,
P01.NAME,
P01.LASTNAME,
P01.FIRSTNAME,
P01.SEX,
COUNTRY.COUNTRYNAME AS 'Nationality',
orgves.NAME AS Vessel,
P01.BIRTHDATE,
P08.TDNUMBER AS PASSPORT,
P08.DATETO AS 'PASSPORT EXPIRY DATE',
P081.TDNUMBER AS SBOOK,
P081.DATETO AS 'SBOOK EXPIRY DATE',
p01.contractstartdate AS "Contract_StartDate",
p01.Contractexpirydate AS "Contract_ExpiryDate",
c02.NAME AS 'Current rank',
pas2.DATEFROM AS 'Date of position change',
c02prom.Name AS 'Position after the position change',
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
P01.CLIENT NUMORGID
FROM PW001P01 AS P01
JOIN PWROT_ACTIVE_SHIFT AS pas
ON pas.PIN = P01.PIN
AND pas.DATEFROM <= CONVERT(VARCHAR, GETDATE(), 112)
AND pas.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
JOIN PWROT_ACTIVE_SHIFT AS pas2
ON pas.PROMOTED_TO = pas2.SEQUENCENO
LEFT JOIN PW001C02 AS c02
ON c02.CODE = pas.POSITION
LEFT JOIN PW001C02 AS c02prom
ON c02prom.CODE = pas2.POSITION
LEFT JOIN PW001P08 AS P08
ON P08.PIN = P01.PIN
AND P08.CODE = 'PASS'
LEFT JOIN PW001P08 AS P081
ON P081.PIN = P01.PIN
AND P081.CODE = 'SBOOK'
LEFT JOIN PWCOUNTRY AS COUNTRY
ON COUNTRY.COUNTRYCODE = P01.NATIONALITY
JOIN PW001P03 p03
ON p03.PIN = p01.PIN
AND p03.PLANNED = 'N'
AND p03.DATEFROM <= GETDATE()
AND ISNULL(p03.DATETO, GETDATE()) >= GETDATE()
LEFT JOIN PWORG orgves
ON orgves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3)Field Specification
N/A