1 Description | 2 SQL Statement | 3 View Sample | 4 Columns Specification

Summary	This view displays currently sailing persons assigned that are planned sign-off.
Keywords	Crew Change, Sign-off, Disembarkation, Flight Booking.

Description

This view displays current sailing persons assigned that are planned sign-off.

SQL Statement

CREATE VIEW dbo.PW001SRV132 AS
SELECT p01.PIN,
	P01.ALTERNATIVEPIN  as 'ALTERNATIVE PIN',
       p01.client              NUMORGID,
       P01.EMPLOYMENTSTARTDATE,
       P01.EMPLOYMENTENDDATE,
       p01.NAME,
       P08.NATIVENAME 'NATIVE NAME',
       p01.Lastname         AS LASTNAME,
       p01.Firstname        AS FIRSTNAME,
       p01.MIDDLENAME,
       p01.SEX                 GENDER,
	   P01.PERSONALIDNO 'Personal ID', 
       orgpos.NAME          AS POSITION,
       orgdep.NAME          AS DEPARTMENT,
       orgves.NAME          AS VESSEL,
       orgves.NUMORGID                     vessel_numorgid,
       ORG.NAME             AS ORGANIZATION,
       p01.BIRTHDATE        AS 'DATE OF BIRTH',
       P01.PLACEOFBIRTH 'PLACE OF BIRTH',
       nat.NATIONALITY      AS 'NATIONALITY ',
       p01.NATIONALITY 'NATIONALITY CODE',
       nat.ISO3166_ALPHA3 'NATIONALOTY CODE ISO',
       P08.TDNUMBER 'PASS NUMBER',
       p08.datefrom 'PASS ISSUE',
       p08.dateto 'PASS EXPIRY',
       P081.TDNUMBER           SBOOK,
       P081.DATEFROM 'SB ISSUE DATE',
       p081.issuedby 'SB ISSUE BY',
       P081.DATETO 'SBOOK EXPIRY',
       p08v.TDNUMBER 'VISA',
       p08v.datefrom 'VISA ISSUE',
       p08v.dateto 'VISA EXPIRY',
       c79d.[TEXT] 'Disembarkation Reason',
       --c79e.[TEXT] 'Embarkation Reason',
       p03.DATEFROM 'EMBARKATION DATE',
       ISNULL(portOn.portcode, portEst.portcode) 'EMBARKATION PORT CODE',
       ISNULL(portOn.NAME, portEst.NAME) 'EMBARKATION PORT',
       p03.TODATEESTIMATED  AS 'DISEMBARKATION DATE',
       portOff.portcode     AS 'DISEMBARKATION PORT CODE',
       portOff.NAME         AS 'DISEMBARKATION PORT',
       fb.GDSLOCATOR        AS 'Flight GDS Locator',
       STUFF(
           (
               SELECT ',' + CHAR(10) + al.AIRLINELOCATOR
               FROM   PWCCMAIRSEGMENTS al
               WHERE  al.seqno = fb.SEQNO
               ORDER BY
                      al.SEGNO
                      FOR XML PATH('')
           ),
           1,
           2,
           ''
       )                    AS 'Flight Airline Locator',
       CONVERT(
           VARCHAR(200),
           REPLACE(
               REPLACE(
                   REPLACE(
                       STUFF(
                           (
                               SELECT ',' --+ CHAR(10)
                                      + CASE 
                                             WHEN LEN(rd.CARRIER + CONVERT(VARCHAR, rd.FLIGHTNUM)) < 7 THEN SPACE(7 -LEN(rd.CARRIER + CONVERT(VARCHAR, rd.FLIGHTNUM)))
                                                  + rd.CARRIER + ' ' + CONVERT(VARCHAR, rd.FLIGHTNUM)
                                             ELSE rd.CARRIER + CONVERT(VARCHAR, rd.FLIGHTNUM)
                                        END + ', '-- SPACE(1)
                                      + CASE 
                                             WHEN LEFT(CONVERT(VARCHAR, rd.DEPT_DATE, 106), 1) = '0' THEN --REPLACE(
                                                                                                          --REPLACE(
                                                  UPPER(LEFT(CONVERT(VARCHAR, rd.DEPT_DATE, 106), 6))--,
                                                                                                     --  ' ',
                                                                                                     --    ''
                                                                                                     --),
                                                                                                     --      '0',
                                                                                                     --      ' '
                                                                                                     --  )
                                             ELSE -- REPLACE(
                                                  UPPER(LEFT(CONVERT(VARCHAR, rd.DEPT_DATE, 106), 6))--,
                                                                                                     --     ' ',
                                                                                                     --     ''
                                                                                                     --  )
                                        END + ', ' --SPACE(1)
                                      + ISNULL(
                                          (
                                              SELECT CASE 
                                                          WHEN ISNULL(
                                                                   CHARINDEX(
                                                                       '-',
                                                                       LTRIM(
                                                                           RTRIM(
                                                                               REPLACE(
                                                                                   REPLACE(
                                                                                       REPLACE(REPLACE([name], 'Int`l', ''), 'Int''l', ''),
                                                                                       'Apt.',
                                                                                       ''
                                                                                   ),
                                                                                   'Airport',
                                                                                   ''
                                                                               )
                                                                           )
                                                                       )
                                                                   ),
                                                                   0
                                                               ) = 0 THEN REPLACE(
                                                                   REPLACE(
                                                                       REPLACE(REPLACE([name], 'Int`l', ''), 'Int''l', ''),
                                                                       'Apt.',
                                                                       ''
                                                                   ),
                                                                   'Airport',
                                                                   ''
                                                               )
                                                          ELSE RTRIM(
                                                                   LEFT(
                                                                       LTRIM(
                                                                           RTRIM(
                                                                               REPLACE(
                                                                                   REPLACE(
                                                                                       REPLACE(REPLACE([name], 'Int`l', ''), 'Int''l', ''),
                                                                                       'Apt.',
                                                                                       ''
                                                                                   ),
                                                                                   'Airport',
                                                                                   ''
                                                                               )
                                                                           )
                                                                       ),
                                                                       CHARINDEX(
                                                                           '-',
                                                                           LTRIM(
                                                                               RTRIM(
                                                                                   REPLACE(
                                                                                       REPLACE(
                                                                                           REPLACE(REPLACE([name], 'Int`l', ''), 'Int''l', ''),
                                                                                           'Apt.',
                                                                                           ''
                                                                                       ),
                                                                                       'Airport',
                                                                                       ''
                                                                                   )
                                                                               )
                                                                           )
                                                                       ) -1
                                                                   )
                                                               )
                                                     END AS airport
                                              FROM   pw001c48
                                              WHERE  code = rd.DEPT_AIRPORT
                                          ),
                                          ''
                                      ) + ' ' +
                                      + ISNULL(
                                          (
                                              SELECT CASE 
                                                          WHEN ISNULL(
                                                                   CHARINDEX(
                                                                       '-',
                                                                       LTRIM(
                                                                           RTRIM(
                                                                               REPLACE(
                                                                                   REPLACE(
                                                                                       REPLACE(REPLACE([name], 'Int`l', ''), 'Int''l', ''),
                                                                                       'Apt.',
                                                                                       ''
                                                                                   ),
                                                                                   'Airport',
                                                                                   ''
                                                                               )
                                                                           )
                                                                       )
                                                                   ),
                                                                   0
                                                               ) = 0 THEN REPLACE(
                                                                   REPLACE(
                                                                       REPLACE(REPLACE([name], 'Int`l', ''), 'Int''l', ''),
                                                                       'Apt.',
                                                                       ''
                                                                   ),
                                                                   'Airport',
                                                                   ''
                                                               )
                                                          ELSE RTRIM(
                                                                   LEFT(
                                                                       LTRIM(
                                                                           RTRIM(
                                                                               REPLACE(
                                                                                   REPLACE(
                                                                                       REPLACE(REPLACE([name], 'Int`l', ''), 'Int''l', ''),
                                                                                       'Apt.',
                                                                                       ''
                                                                                   ),
                                                                                   'Airport',
                                                                                   ''
                                                                               )
                                                                           )
                                                                       ),
                                                                       CHARINDEX(
                                                                           '-',
                                                                           LTRIM(
                                                                               RTRIM(
                                                                                   REPLACE(
                                                                                       REPLACE(
                                                                                           REPLACE(REPLACE([name], 'Int`l', ''), 'Int''l', ''),
                                                                                           'Apt.',
                                                                                           ''
                                                                                       ),
                                                                                       'Airport',
                                                                                       ''
                                                                                   )
                                                                               )
                                                                           )
                                                                       ) -1
                                                                   )
                                                               )
                                                     END AS airport
                                              FROM   pw001c48
                                              WHERE  code = rd.ARRV_AIRPORT
                                          ),
                                          ''
                                      ) + ', ' +
                                      + ISNULL(
                                          CASE 
                                               WHEN rd.DEPT_TIME < 100 THEN '00'
                                               WHEN rd.DEPT_TIME < 1000 THEN '0'
                                               ELSE ''
                                          END + CONVERT(VARCHAR(4), rd.DEPT_TIME) + ' '-- + SPACE(1),
                                          ,
                                          ' ' --+ SPACE(1)
                                      )
                                      + ISNULL(
                                          CASE 
                                               WHEN rd.ARRV_TIME < 100 THEN '00'
                                               WHEN rd.ARRV_TIME < 1000 THEN '0'
                                               ELSE ''
                                          END + CONVERT(VARCHAR(4), rd.ARRV_TIME) + ' '--+ SPACE(1),
                                          ,
                                          ' '-- + SPACE(1)
                                      )
                               FROM   PWCCMAIRSEGMENTS rd
                               WHERE  rd.seqno = fb.SEQNO
                               ORDER BY
                                      rd.SEGNO
                                      FOR XML PATH('')
                           ),
                           1,
                           2,
                           ''
                       ),
                       ' ',
                       '<>'
                   ),
                   '><',
                   ''
               ),
               '<>',
               ' '
           )
       )                    AS 'Flight Route Details',
       CONVERT(
           VARCHAR(200),
           REPLACE(
               REPLACE(
                   REPLACE(
                       STUFF(
                           (
                               SELECT ',' + ' ' + fn.CARRIER + ' ' + CONVERT(VARCHAR, fn.FLIGHTNUM)
                               FROM   PWCCMAIRSEGMENTS fn
                               WHERE  fn.seqno = fb.SEQNO
                               ORDER BY
                                      fn.SEGNO
                                      FOR XML PATH('')
                           ),
                           1,
                           2,
                           ''
                       ),
                       ' ',
                       '<>'
                   ),
                   '><',
                   ''
               ),
               '<>',
               ' '
           )
       )                    AS 'Flight No',
       fb.DPTDATE           AS 'Flight Departure Date',
       LEFT(fbat.ETA, 2) + ':' + RIGHT(fbat.ETA, 2) AS 'Flight ETA',
       LEFT(fbat.ETD, 2) + ':' + RIGHT(fbat.ETD, 2) AS 'Flight ETD',
       STUFF(
           (
               SELECT DISTINCT ',' + CHAR(10) + tn.TICKETCODE
               FROM   PWCCMAIRSEGMENTS tn
               WHERE  tn.seqno = fb.SEQNO
                      --order by tn.SEGNO
                      FOR XML PATH('')
           ),
           1,
           2,
           ''
       )                    AS 'Flight Ticket Number',
       CASE 
            WHEN c20manning.NAME IS NULL THEN 'NOT ASSIGNED'
            ELSE C20MANNING.NAME
       END                  AS 'MANNING POOL',
       mobile.TELENO        AS MOBILE,
       email.TELENO         AS EMAIL,
       p01.HOMEAIRPORT,
       p01.COSTPLACE             AS 'Department/Cost Place Code',
       c43.[NAME]                AS 'Department/Cost Place'
FROM   PW001P01 p01
       LEFT JOIN PWORG ORG
            ON  P01.CLIENT = ORG.NUMORGID
       JOIN PW001P03 p03
            ON  p03.PIN = p01.PIN
            AND ISNULL(p03.DATETO, GETDATE()) >= GETDATE()
       JOIN PW001C12 c12
            ON  c12.CODE = p03.CODE
            AND c12.OPTIONS LIKE '%S%'
       LEFT JOIN PWORG orgves
            ON  orgves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3)
       LEFT JOIN PWORG orgdep
            ON  orgdep.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 4)
       LEFT JOIN PWORG orgpos
            ON  orgpos.NUMORGID = p03.NUMORGID
            AND orgpos.ORGTYPE = 5
       LEFT JOIN PWCOUNTRY nat
            ON  nat.COUNTRYCODE = p01.NATIONALITY
       LEFT JOIN PWROT_SHIFT_ACTIVITIES psa
            ON  psa.ACTIVITIES = p03.SEQUENCENO
       LEFT JOIN PWROT_ACTIVE_SHIFT pas
            ON  pas.SEQUENCENO = psa.SEQUENCENO
       LEFT JOIN PWCCMCHANGECREWLIST cclOff
            ON  cclOff.CURRENTACT = p03.SEQUENCENO
       LEFT JOIN PWORGVESACT ccOff
            ON  ccOff.SEQNO = cclOff.SEQNO
       LEFT JOIN PWPORT portOff
            ON  portOff.PORTCODE = ccOff.PLACEFROM
       LEFT JOIN PWCCMCHANGECREWLIST cclOn
            ON  cclOn.NEXTACT = p03.SEQUENCENO
       LEFT JOIN PWORGVESACT ccOn
            ON  ccOn.SEQNO = cclOn.SEQNO
       LEFT JOIN PWPORT portOn
            ON  portOn.PORTCODE = ccOn.PLACEFROM
       LEFT JOIN PWORGVESACT ccEst
            ON  ccEst.SEQNO = pas.SPD_CC_SIGNOFF
       LEFT JOIN PWPORT portEst
            ON  portEst.PORTCODE = ccEst.PLACEFROM
       LEFT JOIN PW001P08 P08
            ON  P08.PIN = P01.PIN
            AND P08.CODE = 'P'
       LEFT JOIN PW001P08 P081
            ON  P081.PIN = P01.PIN
            AND P081.CODE = 'SFR'
       LEFT JOIN PW001P08 P08v
            ON  P08v.PIN = P01.PIN
            AND P08v.CODE = 'VISASH'
       LEFT JOIN PW001C20 c20manning
            ON  c20manning.CODE = MANAGENTCODE
       LEFT JOIN PWCCMFLIGHTARRANGEMENTS fb
            ON  fb.pin = p01.pin
            AND fb.SEQNOM = ISNULL(ccOff.SEQNO, ccEst.SEQNO)
            AND fb.SIGNON = 0
            AND fb.BOOKING_STATUS IN ('ISSUED', 'APPROVED', 'REISSUED')
       LEFT JOIN (
                SELECT SEQNOM,
                       PIN,
                       /*CASE 
                            WHEN SCHEDULED_DEPARTURE_TIME < 100 THEN '00'
                            WHEN SCHEDULED_DEPARTURE_TIME < 1000 THEN '0'
                            ELSE ''
                       END + CONVERT(VARCHAR(4), SCHEDULED_DEPARTURE_TIME) AS 'ETD',
                       SCHEDULED_DEPARTURE_TIME,*/
                       SCHEDULED_ARRIVAL_TIME AS ETA,
                       SCHEDULED_DEPARTURE_TIME AS ETD
                FROM   PWCCMFLIGHTARRANGEMENTS
                WHERE  signon = 0
                       AND BOOKING_STATUS IN ('ISSUED', 'APPROVED', 'REISSUED')
            ) fbat
            ON  fbat.pin = p01.pin
            AND fbat.SEQNOM = ISNULL(cclOff.SEQNO, ccEst.SEQNO)
       LEFT JOIN PW001C79 c79d
            ON  c79d.CODE = p03.DISEMBARKATION
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE
       LEFT JOIN PW001P0T mobile
            ON  mobile.PIN = p01.PIN
            AND mobile.TELETYPE = 3
            AND NOT EXISTS (
                    SELECT 1
                    FROM   pw001p0t t
                    WHERE  t.PIN = mobile.PIN
                           AND t.TELETYPE = mobile.TELETYPE
                           AND (
                                   t.TELEPRIORITY < mobile.TELEPRIORITY
                                   OR (
                                          t.TELEPRIORITY = mobile.TELEPRIORITY
                                          AND t.SEQUENCENO > mobile.SEQUENCENO
                                      )
                               )
                )
       LEFT JOIN PW001P0T email
            ON  email.PIN = p01.PIN
            AND email.TELETYPE = 6
            AND NOT EXISTS (
                    SELECT 1
                    FROM   pw001p0t t
                    WHERE  t.PIN = email.PIN
                           AND t.TELETYPE = email.TELETYPE
                           AND (
                                   t.TELEPRIORITY < email.TELEPRIORITY
                                   OR (
                                          t.TELEPRIORITY = email.TELEPRIORITY
                                          AND t.SEQUENCENO > email.SEQUENCENO
                                      )
                               )
            )

View Sample

Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
PIN	Personal Details > Personal > PIN
ALTERNATIVEPIN	Personal Details > Personal > Alternative PIN
NUMORGID	Personal Details > Employment > Organization’s NUMORGID
EMPLOYMENTSTARTDATE	Personal Details > Employment > Employment Start Date
EMPLOYMENTENDDATE	Personal Details > Employment > Employment End Date
NAME	Personal Details > Personal > Full Name
NATIVE NAME	Personal Details > Personal > Native Name
FIRST NAME	Personal Details > Personal > First Name
MIDDLE NAME	Personal Details > Personal > Middle Name
LAST NAME	Personal Details > Personal > Last Name
Gender	Personal Details > Personal > Gender
Address	Personal Details > Personal > Address1 + Address 2
Post Code	Personal Details > Personal > Post Code
Post Place	Personal Details > Personal > Post Place
Personal ID	Personal Details > Personal > Personal ID
Position	Datagroups > Activity > Position
Department	Datagroups > Activity > Department
Vessel Name	Datagroups > Activity > Vessel
vessel_numorgid	Datagroups > Activity > Vessel NUMORGID
department_numorgid	Datagroups > Activity > Department NUMORGID
Date of Birth	Personal Details > Personal > Date of Birth
Place of Birth	Personal Details > Personal > Place of Birth
Manning Pool	Personal Details > Employment > Manning Pool
Nationality	Personal Details > Personal > Nationality
Nationality Code	Personal Details > Personal > Nationaity Code
Natinality Code ISO	Personal Details > Personal > Nationality Code ISO
Passport Number	Datagroups > Travel Documents > Passport Number
Passport Issue	Datagroups > Travel Documents > Passport Issue Date
Passport Expiry	Datagroups > Travel Documents > Passport Expiry Date
Seamans Book Number	Datagroups > Travel Documents > Seamans Book number
Seamans Book Issued Date	Datagroups > Travel Documents > Seamans Book Issued Date
Seamans Book Issued By	Datagroups > Travel Documents > Seamans Book Issued By
Seamans Book Expiry	Datagroups > Travel Documents > Seamans Book Expiry
Visa Number	Datagroups > Travel Documents > VISA number
Visa Issued Date	Datagroups > Travel Documents > VISA Issued Date
Visa Expiry Date	Datagroups > Travel Documents > VISA Expiry Date
Embarkation Reason	Datagroups > Activities > Embarkation Reason
Embarkation Date	Datagroups > Activities > Embarkation Date From
Embarkation Port Code	Datagroups > Activities > Embarkation Port Code
Embarkation Port Name	Datagroups > Activities > Embarkation Port Name
Disembarkation Date	Datagroups > Activities > Disembarkation Date
Disembarkation Port Code	Datagroups > Activities > Disembarkation Port code
Disembarkation Port Name	Datagroups > Activities > Disembarkation Port Name
Flight GDS Locator	Datagroups > Flight Details > GDS Locator
Flight Airline Location	Datagroups > Flight Details > Airline Location
Flight Route Details	Datagroups > Flight Details > Route Details
Flight Number	Datagroups > Flight Details > Flight Number
Flight Arrival Date	Datagroups > Flight Details > Arrival Date
Flight ETA	Datagroups > Flight Details > ETA
Flight ETD	Datagroups > Flight Details > ETD
Flight Ticket Number	Datagroups > Flight Details > Ticket Number
NOTES	Personal Details > Others > Notes
Mobile	Personal Details > Personal > Telecommunication > Mobile
Email	Personal Details > Personal > Telecommunication > Email
Home Airport	Personal Details > Personal > Home Airport
Contract Type	Datagroups > Contracts > Current Contract > Contract Type
Contract Name	Datagroups > Contracts > Current Contract > Contract Name
Contract Rank	Datagroups > Contracts > Current Contract > Contract Rank
Contract Start	Datagroups > Contracts > Current Contract > Contract Start Date
Contract End	Datagroups > Contracts > Current Contract > Contract End Date
Trial period duration	Datagroups > Contracts > Current Contract > Trial period duration
Trial period end	Datagroups > Contracts > Current Contract > Trial period end
Contract Payscale Table	Datagroups > Contracts > Current Contract > linked Payscale Table
Contract Payscale Code	Datagroups > Contracts > Current Contract > linked Payscale Code
Employee Portal Acess From	Personal Details > Employee Portal > Customized Field
Duration of Employment	Datagroups > Contracts > Current Contract > Customized Field
Current Rank Code	Personal Details > Employment > Current Rank Code
Current Rank Name	Personal Details > Employment > Current Rank Name
Organization	Personal Details > Employment > linked Organization unit
Contract Scan Status	Datagroups > Contracts > Current > Linked scan status
Department/Cost Place Code	Personal Details > Personal > Payroll > Cost Place Code
Department/Cost Place	Personal Details > Personal > Payroll > Cost Place

Reports, Views and Similar

Disembarking List (with Flight Information)

Description

SQL Statement

View Sample

Columns Specification