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
Description/ Location in APM
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
