Summary	The view shows the crew members' birthday information and highlights the nearest upcoming dates with some colors. Only the crew members who are currently onboard are included.
Keywords	Birthday
Category	Crew List View

Description

The view shows the crew members' birthday information and highlights the nearest upcoming dates with some colors. Only the crew members who are currently onboard are included.

View Sample

Main Data Selection

All crew members that are currently on board.

SQL statement

CREATE VIEW dbo.PW001SRV134 AS 

SELECT p01.pin               AS PIN,
       P01.ALTERNATIVEPIN    AS 'ALTERNATIVE PIN',
       CASE 
            WHEN p01.sex = 'M' THEN 'Mr'
            WHEN p01.sex = 'F' THEN 'Mrs'
       END                   AS Gender,
       p01.NAME              AS 'Full Name',
       p01.FIRSTNAME         AS 'First Name',
       p01.MIDDLENAME        AS 'Middle Name',
       p01.LASTNAME          AS 'Last Name',
       DATEADD(yy, DATEDIFF(yy, p01.birthdate, GETDATE()), birthdate) 'Celebration Date',
       MONTH(p01.birthdate)  AS 'Month Nr',
       DATENAME(
           mm,
           DATEADD(yy, DATEDIFF(yy, p01.birthdate, GETDATE()), p01.birthdate)
       )                        MONTH,
       CONVERT(
           INT,
           DATENAME(
               d,
               DATEADD(YY, DATEDIFF(YY, P01.BIRTHDATE, GETDATE()), BIRTHDATE)
           )
       )                     AS DATE,
       DATENAME(
           wk,
           DATEADD(yy, DATEDIFF(yy, p01.birthdate, GETDATE()), birthdate)
       )                        Week,
       DATENAME(
           weekday,
           DATEADD(yy, DATEDIFF(yy, p01.birthdate, GETDATE()), birthdate)
       )                        DAY,
       DATEDIFF(yy, p01.birthdate, GETDATE()) Age,
       p01.Birthdate,
       p01.COUNTRYOFBIRTH + ' - ' + COUNTRY.COUNTRYNAME AS 'Country / Location',
       PCAB.Cabin,
       POS.NAME              AS Position,
       P0T.TELENO            AS Email,
       mob.Teleno            AS 'Mobile Phone',
       p01.RANK              AS 'Current Rank',
       p01.Client               numorgid,
       VSL.NUMORGID          AS vessel_numorgid,
       p01.EmploymentStartDate,
       p01.EmploymentEnddate,
       p01.COSTPLACE             AS 'Department/Cost Place Code',
       c43.[NAME]                AS 'Department/Cost Place'
FROM   dbo.pw001p01 p01
       JOIN dbo.PW001P03 P03
            ON  P01.PIN = P03.PIN
            AND ISNULL(P03.PLANNED,'N') <> 'Y'
            AND (P03.DATETO IS NULL OR P03.DATETO >= GETDATE())
            AND P03.DATEFROM <= GETDATE()
       LEFT JOIN Pw001C12 act
            ON  act.code = p03.code
       JOIN PWORG VSL
            ON  VSL.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 3)
       LEFT JOIN dbo.PWORG DEP
            ON  DEP.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 4)
       LEFT JOIN dbo.PWORG POS
            ON  POS.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 5)
       LEFT JOIN dbo.PWORGVESCAB PCAB
            ON  PCAB.CABINID = p03.cabinid
       LEFT JOIN PWCOUNTRY COUNTRY
            ON  P01.COUNTRYOFBIRTH = COUNTRY.COUNTRYCODE
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE
       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)
                                              )
                                       )
                        )
                )
       LEFT JOIN PW001P0T mob
            ON  (
                    (P01.PIN = mob.PIN)
                    AND (mob.TELETYPE = 3)
                    AND (NOT mob.TELEPRIORITY IS NULL)
                    AND NOT EXISTS
                        (
                            SELECT SEQUENCENO
                            FROM   PW001P0T mob2
                            WHERE  (mob.PIN = mob2.PIN)
                                   AND (mob2.TELETYPE = 3)
                                   AND (
                                           (mob2.TELEPRIORITY < mob.TELEPRIORITY)
                                           OR (
                                                  (mob2.TELEPRIORITY = mob.TELEPRIORITY)
                                                  AND (mob2.SEQUENCENO < mob.SEQUENCENO)
                                              )
                                       )
                        )
                )
WHERE  act.OPTIONS              LIKE '%S%'

Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
`PIN`	The PIN number from the Personal Details.
`Gender`	The Gender number from the Personal Details.
`Full Name`	The Full Name from the Personal Details.
`First Name`	The First Name from the Personal Details.
`Middle Name`	The Middle Name from the Personal Details.
`Last Name`	The Last Name from the Personal Details.
`Celebration Date`	The birthday Celebration Date in current year.
`Month Nr`	The number of celebration month in current year.
`Month`	The celebration month in current year.
`Date`	The celebration date in current year.
`Week`	The week in current year.
`Day`	The celebration day in current year.
`Age`	The age the person is turning in current year.
`Birthdate`	The birth date from the Personal Details.
`Country / Location`	The birth place from the Personal Details.
`Cabin`	The cabin of the person from activity.
`Position`	The position of the person from activity.
`Email`	The e-mail address from the Personal Details that has the highest priority.
`Mobile Phone`	The mobile phone from the Personal Details that has the highest priority.
`Current Rank`	The current rank from the Personal Details.
`numorgid`	The employment organization id from the Personal Details.
`EmploymentStartDate`	The employment start date from the Personal Details.
`EmploymentEnddate`	The employment end date from the Personal Details.

Start Page

N/A

Other

To highlight the passed/upcoming birth dates the following settings need to be configured.

Reports, Views and Similar

Birthday View (crew onboard)