Versions Compared

Version Old Version 10 New Version Current
Changes made by

Daria Chebotaieva

Arvin John Salandanan

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Expand
Code Block
languagesql
CREATE VIEW dbo.PW001SRV12
AS

SELECT P01.PIN                               PIN,
       CASE 
            WHEN c12.[TEXT] IS NOT NULL THEN c12.[TEXT]
            WHEN p0up03plan.PIN IS NOT NULL THEN 'Nearest planned to ' + c12plan.[TEXT] + ' ' + CONVERT(VARCHAR, p03plan.DATEFROM, 1)
            WHEN p0u2.AVPLAN IS NOT NULL THEN 'Available Planning'
            WHEN p0up0u2.AVPLAN IS NULL AND p0up0u2.AVRET IS NOT NULL THEN 'Available to Return'
            WHEN p0up0u2.AVPLAN IS NULL AND p0up0u2.AVRET IS NULL THEN 'Available'
       END                                AS [STATUS],
       P01.ALTERNATIVEPIN                 AS 'ALTERNATIVE PIN',
       P01pass.NAMEnativename                    AS 'Native Name',
       P01.NAME                           AS 'Full Name',
       P01.TITLENAME                      AS 'Title Name',
       P01.FIRSTNAME                      AS 'First Name',
       P01.MIDDLENAME                     AS 'Middle Name',
       P01.LASTNAME                       AS 'Last Name',
       P01.MAIDENNAME                     AS 'Maiden Name',
       CASE 
            WHEN p01.SEX = 'M' THEN 'Male'
            WHEN p01.SEX = 'F' THEN 'Female'
            ELSE 'Undefined'
       END                                AS Gender,
       c04.[TEXT]                         AS 'Marital Status',
       nat.NATIONALITY                    AS Nationality,
       ORG.NAME                           AS 'Organization',
       P01.CLIENT                            Numorgid,
       P01.EmploymentStartDate,
       P01.EmploymentEndDate,
       c55.NAME                           AS 'Termination Reason',
       p01Rank.[NAME]                     AS 'Current Rank',
       p01.COSTPLACE                      AS 'Department/Cost Place Code',
       c43.[NAME]                         AS 'Department/Cost Place',
       P01.ADDRESS1 + ' ' + P01.ADDRESS2  AS 'Address',
       P01.ADDRESS3                       AS 'City',
       P01.POSTCODE 'Post Code',
       P01.POSTPLACE 'Post Place',
       PC.COUNTRYNAME                     AS 'Address Country',
       P01.HOMEAIRPORT 'Home Airport',
       email.TELENO                       AS 'E-MAIL',
       mob.TELENO                         AS 'Mobile Phone',
       P01.BIRTHDATE 'Birth date',
       P01.PERSONALIDNO                   AS 'Personal ID',
       PASS.TDNUMBER 'Passport No',
       PASS.DATETO                        AS 'Passport Expiry',
       passc.COUNTRYNAME                  AS 'Passport Country of Issue',
       CASE 
            WHEN CONTRACTKIND = 0 THEN 'Main Contract'
            WHEN CONTRACTKIND = 1 THEN 'Sub Contract'
            WHEN CONTRACTKIND = 2 THEN 'Ammendment'
            ELSE ''
       END                                AS 'Contract Type',
       C32CONT.Text 'Contract Name',
       C02CONT.NAME 'Contract Rank',
       p20.DATESTART 'Contract Start',
       p20.DATEEND 'Contract End',
       p20.DURATION 'Trial period duration',
       p20.TRIALPERIODEND 'Trial period end',
       p20.PAYSCALETABLE 'Contract Pasycale Table',
       p20.PAYSCALECODE 'Contract Pasycale Code',
       p0u.EMP_PORT 'Employee Portal Access From',
       CASE 
            WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'
            WHEN p20.scanvalidity IN (1) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
            WHEN p20.scanvalidity IN (0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10006) + ' Not Confirmed'
       END                                AS 'Contract Scan Status',
FROM   PW001P01 P01        LEFT JOIN (
  
             SELECT P8.PIN,CONVERT(
                       P8.tdnumberVARCHAR(999),
                       P8.datefrom,REPLACE(
                       P8.dateto,   REPLACE(dbo.ad_RtfToText(Personal.NOTES), CHAR(13), ''),
                  p8.ISSUEDWHERE,        CHAR(10),
               p8.nativename,           ''
            p8.ISSUE_COUNTRY,          )
             p8.PASSPORTPRIORITY     )
           FROM   PW001P0N dbo.PW001P08Personal
P8           WHERE  Personal.PIN = p01.PIN
        JOIN dbo.PW001C23 C23        AND Personal.FIELDNO = 'p17'
       )          ON  P8.CODE = C23.CODE                    AS 'Notes - Personal Details'
FROM   PW001P01 ANDP01
C23.OPTIONS LIKE '%T%'     LEFT JOIN (
         WHERE  NOT EXISTS (   SELECT P8.PIN,
                       SELECTP8.tdnumber,
1                       P8.datefrom,
    FROM      PW001P08 t            P8.dateto,
                      JOIN dbo.PW001C23 C23p8.ISSUEDWHERE,
                       p8.nativename,
                   ON  t.CODE = C23.CODEp8.ISSUE_COUNTRY,
                       p8.PASSPORTPRIORITY
               AND C23.OPTIONSFROM LIKE '%T%' dbo.PW001P08 P8
                       JOIN dbo.PW001C23 WHEREC23
 t.PIN = p8.PIN                         ON  P8.CODE = C23.CODE
     AND (                      AND C23.OPTIONS LIKE '%T%'
                WHERE t.PASSPORTPRIORITY <NOT p8.PASSPORTPRIORITYEXISTS (
                           SELECT 1
            OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY            FROM   PW001P08 t
                          AND t.SEQUENCENO < p8.SEQUENCENO     JOIN dbo.PW001C23 C23
                               )        ON  t.CODE = C23.CODE
           )             ) pass             ON AND pass.PIN = P01.PINC23.OPTIONS LIKE '%T%'
           LEFT JOIN PWCOUNTRY passc             ONWHERE  passct.COUNTRYCODEPIN = pass.ISSUE_COUNTRYp8.PIN
             LEFT JOIN PW001P0P P0P             ON  ((P01.PIN = P0P.PIN) AND (P0P.PNUMBER
= 'A'))        LEFT JOIN PW001C02 p01Rank             ON  p01Rank.CODE = p01.[RANK]               LEFTt.PASSPORTPRIORITY JOIN< Pw001C33p8.PASSPORTPRIORITY
c33             ON  c33.code = p01.CATHEGORYA        LEFT JOIN PW001C02 C02             ON OR P0Pt.POSITIONIDPASSPORTPRIORITY = C02p8.CODEPASSPORTPRIORITY
       LEFT JOIN PWORG ORG             ON  P01.CLIENT = ORG.NUMORGID        LEFT JOIN PWCOUNTRY PC    AND t.SEQUENCENO < p8.SEQUENCENO
     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 PW001P20) p20pass
            ON  p20pass.PIN = p01P01.PIN
       LEFT JOIN PWCOUNTRY passc
 AND p20.HISTORICAL = 'F'        ON  passc.COUNTRYCODE = pass.ISSUE_COUNTRY
AND NOT EXISTS (    LEFT JOIN PW001P0P P0P
            ON SELECT 1((P01.PIN = P0P.PIN) AND (P0P.PNUMBER = 'A'))
       LEFT JOIN PW001C02 p01Rank
   FROM   PW001P20 t     ON  p01Rank.CODE = p01.[RANK]
       LEFT JOIN Pw001C33 c33
WHERE  t.PIN = p20.PIN        ON  c33.code = p01.CATHEGORYA
       LEFT JOIN PW001C02 C02
    AND t.HISTORICAL = 'F'     ON  P0P.POSITIONID = C02.CODE
       LEFT JOIN PWORG  ORG
      AND t.DATESTART > p20.DATESTART   ON  P01.CLIENT = ORG.NUMORGID
         )
       LEFT JOIN PW001OLEDOCSPWCOUNTRY sdocPC
            ON  sdocP01.DOCNOADDRESS_COUNTRY = p20PC.SCANNEDDOCNOCOUNTRYCODE
       LEFT JOIN PW001C02PW001P01PICT C02CONTPL
            ON  p20P01.RANKPIN = C02CONTPL.CODEPIN
       LEFT JOIN PW001C32PWORG C32CONTPV
            ON  p20P01.CONTRACTTYPEPAYROLLVSL = C32CONTPV.codeNUMORGID
       LEFT JOIN PW001P0TPW001P20 emailp20
            ON  emailp20.PIN = p01.PIN
            AND emailp20.TELETYPEHISTORICAL = 6'F'
            AND email.TELEPRIORITYNOT ISEXISTS NOT(
NULL             AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P0TPW001P20 t
                    WHERE  t.PIN = emailp20.PIN
                           AND t.TELETYPEHISTORICAL = email.TELETYPE'F'
                           AND t.TELEPRIORITYDATESTART IS> NOTp20.DATESTART
NULL                )
       LEFT JOIN PW001OLEDOCS sdoc
AND (           ON  sdoc.DOCNO = p20.SCANNEDDOCNO
       LEFT JOIN PW001C02 C02CONT
         t.TELEPRIORITY < email.TELEPRIORITY ON  p20.RANK = C02CONT.CODE
       LEFT JOIN PW001C32 C32CONT
            ON  p20.CONTRACTTYPE = C32CONT.code
  OR t.TELEPRIORITY = email.TELEPRIORITY  LEFT JOIN PW001P0T email
            ON  email.PIN = p01.PIN
             AND t.SEQUENCENO < email.SEQUENCENOTELETYPE = 6
            AND email.TELEPRIORITY IS NOT NULL
            )AND NOT EXISTS (
             )       SELECT LEFT1
JOIN PW001P0T mob             ON  mob.PIN = p01.PIN FROM   PW001P0T t
       AND mob.TELETYPE = 3          WHERE  t.PIN AND= mobemail.TELEPRIORITY IS NOT NULLPIN
                     AND NOT EXISTS (   AND t.TELETYPE = email.TELETYPE
              SELECT   1          AND t.TELEPRIORITY IS NOT NULL
      FROM   PW001P0T t                 AND (
  WHERE  t.PIN = mob.PIN                            AND t.TELETYPETELEPRIORITY =< mob.TELETYPEemail.TELEPRIORITY
                                   ANDOR t.TELEPRIORITY IS= NOTemail.TELEPRIORITY
NULL                            AND (      AND t.SEQUENCENO < email.SEQUENCENO
                          t.TELEPRIORITY < mob.TELEPRIORITY   )
                )
       LEFT JOIN PW001P0T mob
    OR t.TELEPRIORITY = mob.TELEPRIORITY     ON  mob.PIN = p01.PIN
            AND mob.TELETYPE = 3
          AND t.SEQUENCENO <AND mob.SEQUENCENOTELEPRIORITY IS NOT NULL
            AND NOT EXISTS (
            )        SELECT 1
       )        LEFT JOIN PWCOUNTRY nat  FROM   PW001P0T t
      ON  nat.COUNTRYCODE = p01.NATIONALITY        LEFT JOIN PW001P0UWHERE p0u t.PIN = mob.PIN
         ON  p0u.PIN = p01.PIN        LEFT JOIN PW001C55 c55             ON  p01.STOPREASONAND t.TELETYPE = c55mob.CODETELETYPE
        LEFT JOIN PW001C43 c43             ON  c43.CODE =AND p01t.COSTPLACETELEPRIORITY IS NOT NULL
    LEFT JOIN PW001C04 c04             ON  c04.CODE = p01.MARITALSTATUS    AND (
  LEFT JOIN dbo.PW001P03 P03cur             ON  P01.PIN = P03cur.PIN             AND (P03cur.DATETO IS NULL OR P03cur.DATETO >= GETDATE())t.TELEPRIORITY < mob.TELEPRIORITY
                     AND P03cur.DATEFROM <= GETDATE()           OR t.TELEPRIORITY AND= P03cur.PLANNED <> 'Y'mob.TELEPRIORITY
                  LEFT JOIN PW001C12 c12             ON AND c12t.CODESEQUENCENO =< p03cur.CODE

Columns Specification

mob.SEQUENCENO
                               )
                )
       LEFT JOIN PWCOUNTRY nat
            ON  nat.COUNTRYCODE = p01.NATIONALITY
       LEFT JOIN PW001P0U p0u
            ON  p0u.PIN = p01.PIN
       LEFT JOIN PW001C55 c55
            ON  p01.STOPREASON = c55.CODE
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE
       LEFT JOIN PW001C04 c04
            ON  c04.CODE = p01.MARITALSTATUS
       LEFT JOIN PW001P03 p03plan
            ON  p03plan.PIN = p01.PIN
            AND p03plan.PLANNED = 'Y'
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P03 p03t
                    WHERE  p03t.PIN = p01.PIN
                           AND p03t.PLANNED = 'Y'
                           AND p03t.DATEFROM < p03plan.DateFrom
                )
       LEFT JOIN PW001C12 c12plan
            ON  c12plan.CODE = p03plan.CODE
       LEFT JOIN dbo.PW001P03 P03cur
            ON  P01.PIN = P03cur.PIN
            AND (P03cur.DATETO IS NULL OR P03cur.DATETO >= GETDATE())
            AND P03cur.DATEFROM <= GETDATE()
            AND P03cur.PLANNED <> 'Y'
       LEFT JOIN PW001C12 c12
            ON  c12.CODE = p03cur.CODE
       LEFT JOIN PW001P0U p0u2
            ON  p0u2.PIN = p01.PIN
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P03 p03t
                    WHERE  p03t.PIN = p01.PIN
                           --AND p03t.PLANNED = 'N'
                           AND p03t.CODE IN (SELECT t.CODE
                                             FROM   PW001C12 t
                                             WHERE  t.OPTIONS LIKE '%S%')
                               --AND p03t.DATEFROM < CAST(GETDATE() AS DATE)
                )

Columns Specification

Column

Description/ Location in APM

PIN

Personal Details > Personal > PIN

Notes - Personal Details

Personal Details > Personal > Notes

Alternative PIN

Personal Details > Personal > Alternative PIN> Personal > Alternative PIN

Native Name

Datagroups > Travel Documents > Passport > Native Name

Full Name

Personal Details > Personal > Full Name

Title Name

Personal Details > Personal > Title Name

First Name

Personal Details > Personal > First Name

Middle Name

Personal Details > Personal > Middle Name

Last Name

Personal Details > Personal > Last Name

Maiden Name

Personal Details > Personal > Maiden Name

Gender

Personal Details > Personal > Gender

Organization

Personal Details > Employment > Organization’s Name

Current Rank

Personal Details > Employment > Current Rank

Numorgid

Personal Details > Employment > Organization’s No

EmploymentStartDate

Personal Details > Employment > Employment Start Date

EmploymentEndDate

Personal Details > Employment > Employment End Date

Termination Reason

Personal Details > Employment > Termination Reason

Nationality

Personal Details > Personal > Nationality

Birth date

Personal Details > Personal > Birth date

Personal ID

Personal Details > Personal > Personal ID

Passport No

Travel Documents > Current Passport No

Passport Expiry

Travel Documents > Current Passport Date

Passport Country of Issue

Travel Documents > Current Passport Issue Country

Address

Personal Details > Personal > Address

City

Personal Details > Personal > City

Post Code

Personal Details > Personal > Post Code

Post Place

Personal Details > Personal > Post Place (used for City)

Address Country

Personal Details > Personal > Address Country

Home Airport

Personal Details > Personal > Home Airport 1

E-MAIL

Personal Details > Personal > Telecommunication > E-mail with highest priority (e.g. 1 has more priority than 4)

Mobilr Phone

Personal Details > Personal > Telecommunication > Mobile Phone with highest priority (e.g. 1 has more priority than 4)

Marital Status

Personal Details > Personal > Marital Status

Contract Type

Contracts Datagorup > Current contract type: Main, Amendment, Sub.

Contract Name

Contracts Datagorup > Current contract name.

Contract Rank

Contracts Datagorup > Current contract rank.

Contract Start

Contracts Datagorup > Current contract Start Date.

Contract End

Contracts Datagorup > Current contract End Date.

Trial period duration

Contracts Datagorup > Current contract trial period duration.

Trial period end

Contracts Datagorup > Current contract trial period end.

Contract Pasycale Table

Contracts Datagorup > Current contract payscale table.

Contract Pasycale Code

Contracts Datagorup > Current contract payscale code.

Contract Scan Status

Contracts Datagorup > Current contract scan status.

Emplyee Portal Access From

Personal Details > Personal > Customised Fields > Emplyee Portal Access From

STATUS

  • The current Activity Name should be set as a status.

  • If no current activity + if the crew doesn’t have any sea-service activities records of any status + if the person has a date added in the customized field “Available for Planning from“ in Personal Details, then Available Planning status should be used.

  • If the crew doesn’t have any current activity, and if the crew is already planned to haver some activity, then the status should be Planned to “Nearest Planned Activity Name“ + “Nearest Planned Date From“.

  • If no current activity + if the crew doesn’t have any sea-service activities records of any status + if no planned activities, then Available Planning status should be used.

  • If no current activity + if the Available Planning is not applicable + if the person has a date added in the customized field “Available to Return“, then the Available to Return status should be used.

  • If no current activity + if the Available Planning is not applicable + Available to Return is not applicable, then the Available status should be used.

Start Page

...

  • “Available to Return“, then the Available to Return status should be used.

  • If no current activity + if the Available Planning is not applicable + Available to Return is not applicable, then the Available status should be used.

Additional Details

It's been requested in the view to have value of several rtf fields aggregated & shown in the same cell. For this purpose it is required to convert those to txt. We found specific script that should be added as database function to be used if similar request is raised.

Jira Legacy
serverSystem Jira
serverId762e1a68-a646-3eb7-8dc8-5cefb51b6652
keyAPM-2854