Skip to end of banner
Go to start of banner

Standard View

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Version History

« Previous Version 6 Next »

Summary

The script for the Standard view which can be used to create a “customized“ Standard view

Keywords

Standard View

Category

Crew List View

Description

The script for the Standard view that can be used to create a “customized“ Standard view by adding more fields/ tables or removing certain fields.

View Sample

Main Data Selection

Shows all the crew linked to the Current Client organization unit

SQL statement

 SQL statement
CREATE VIEW dbo.PW001SRV20 AS
SELECT P01.PIN PIN, 
P01.NAME NAME,
P01.TITLENAME TITLENAME,
P01.FIRSTNAME FIRSTNAME,
P01.MIDDLENAME MIDDLENAME,
P01.LASTNAME LASTNAME,
P01.MAIDENNAME MAIDENNAME,
P01.CALLINGNAME CALLINGNAME,
P01.SUFFIXNAME SUFFIXNAME,
C02.NAME RANK, C02.GROUPNO RANKSORT,
P01.NATIONALITY NATIONALITY, 
P01.CLIENT NUMORGID, 
ORG.NAME ORGNAME, 
PV.NAME PAYROLLVESSEL, 
P01.BIRTHDATE BIRTHDATE,
P01.PERSONALIDNO, 
P01.TELEPHONE PHONE,
P1P.PASSPORTEXPIRYDATE PASSPORTEXPIRY,
P1P.PASSPORTNO PASSPORTNO, 
P01.EMPLOYMENTSTARTDATE, 
P01.EMPLOYMENTENDDATE, 
P0P.STARTDATE P0PSTARTDATE, 
P0P.ENDDATE P0PENDDATE,
P01.PERSONGROUPCODE PERSONGROUP, 
C38.GROUPNO PAYROLLSTATUSSORTNO, 
C32.TEXT CONTRACTTYPE, 
P01.CONTRACTSTARTDATE, 
P01.CONTRACTEXPIRYDATE,
P01.ADDRESS1,
P01.ADDRESS2,
P01.ADDRESS3,
P01.POSTCODE,
P01.POSTPLACE,
PC.COUNTRYNAME AS ADDRESSCOUNTRY,
P01.HOMEAIRPORT, 
P01.HOMEAIRPORT2, 
P01.HOMEAIRPORT3, 
P0T.TELENO EMAIL,
PL.DOCUMENT PPICTURE
FROM
PW001P01 P01
LEFT JOIN PW001P1P P1P ON P01.PIN=P1P.PIN
LEFT JOIN PW001P0P P0P ON ((P01.PIN=P0P.PIN) AND (P0P.PNUMBER='A'))
LEFT JOIN PW001C02 C02 ON P0P.POSITIONID=C02.CODE
LEFT JOIN PWORG ORG ON P01.CLIENT=ORG.NUMORGID
LEFT JOIN PW001C32 C32 ON P01.CONTRACTTYPE=C32.CODE
LEFT JOIN PW001C38 C38 ON P01.PAYROLLSTATUS=C38.CODE
LEFT JOIN PWCOUNTRY PC ON P01.ADDRESS_COUNTRY = PC.COUNTRYCODE
LEFT JOIN PW001P01PICT PL ON P01.PIN = PL.PIN
LEFT JOIN PWORG PV ON P01.PAYROLLVSL=PV.NUMORGID
LEFT JOIN PW001P0T P0T ON ((P01.PIN = P0T.PIN) AND (P0T.TELETYPE = 6) AND (NOT P0T.TELEPRIORITY IS NULL) AND NOT EXISTS
(SELECT SEQUENCENO
FROM  PW001P0T P0T2
WHERE  (P0T.PIN = P0T2.PIN) AND (P0T2.TELETYPE = 6) AND ((P0T2.TELEPRIORITY < P0T.TELEPRIORITY) OR
((P0T2.TELEPRIORITY = P0T.TELEPRIORITY) AND (P0T2.SEQUENCENO < P0T.SEQUENCENO)))))

Columns Specification

Column

Description/ Location in APM

PIN 

PIN 

NAME

NAME

TITLENAME

TITLENAME

FIRSTNAME

FIRSTNAME

MIDDLENAME

MIDDLENAME

LASTNAME

LASTNAME

MAIDENNAME

MAIDENNAME

CALLINGNAME

CALLINGNAME

SUFFIXNAME

SUFFIXNAME

RANKSORT

Rank Sort number

NATIONALITY 

NATIONALITY 

ORGNAME 

Employment organization name

PAYROLLVESSEL 

PAYROLL VESSEL 

BIRTHDATE

BIRTHDATE

PERSONALIDNO 

PERSONALIDNO 

PHONE

PHONE

PASSPORTEXPIRY

PASSPORT EXPIRY DATE

PASSPORTNO 

PASSPORT NUMBER

EMPLOYMENTSTARTDATE 

EMPLOYMENT START DATE 

EMPLOYMENTENDDATE 

EMPLOYMENT END DATE 

P0PSTARTDATE 

Displayed in the view as PAYROLL SENIORITY START DATE

P0PENDDATE

Displayed in the view as PAYROLL SENIORITY END DATE

PERSONGROUP 

Cost Group Code from Personal details > Seniority

PAYROLLSTATUSSORTNO 

Sort number of the Payroll Status Code from Personal Details > Employment

CONTRACTTYPE 

CONTRACT TYPE NAME from Personal Details

CONTRACTSTARTDATE 

CONTRACT START DATE from Personal Details

CONTRACTEXPIRYDATE

CONTRACT EXPIRY DATE from Personal Details

ADDRESS1

ADDRESS1

ADDRESS2

ADDRESS2

ADDRESS3

ADDRESS3

POSTCODE

POSTCODE

POSTPLACE

POSTPLACE

ADDRESSCOUNTRY

ADDRESS COUNTRY

HOMEAIRPORT 

HOME AIRPORT 

HOMEAIRPORT2 

HOME AIRPORT 2 

HOMEAIRPORT3 

HOME AIRPORT 3 

EMAIL

EMAIL

PPICTURE

PICTURE

  • No labels