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