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

Please remember to add labels to the article. Only the labels from the lit are allowed: /wiki/spaces/PnP/pages/4523197124

Description

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

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

Start Page

N/A

  • No labels