CREATE VIEW dbo.[PW001SRV14] AS
WITH dummy AS 
     (
         SELECT P1.PIN,
                P1.NUMORGID,
                P1.EMPLOYMENTSTARTDATE,
                P1.EMPLOYMENTENDDATE
         FROM   (
                    SELECT NULL     PIN,
                           NULL     NUMORGID,
                           NULL     EMPLOYMENTSTARTDATE,
                           NULL     EMPLOYMENTENDDATE
                ) AS P1
     ),
     cteStationRequiredDocs AS (
         SELECT STATIONSEQNO,
                CERTIFICATECODE,
                'Competence' AS CertType
         FROM   WEB_CP_CSB_REQ_CERTIFICATION
         UNION ALL
         SELECT STATIONSEQNO,
                CERTIFICATECODE,
                'Medical'               AS CertType
         FROM   WEB_CP_CSB_REQ_MEDICAL  AS wccrm
     ),
     cteStationReqDocsCount AS (
         SELECT csrdCount.STATIONSEQNO,
                COUNT(csrdCount.CERTIFICATECODE)StationReqDocCount
         FROM   cteStationRequiredDocs AS csrdCount
         GROUP BY
                csrdCount.STATIONSEQNO
     ),
     cteCompetence AS (
         SELECT *
         FROM   (
                    SELECT tp05.PIN,
                           tp05.CODE,
                           tp05.EXPIRYDATE,
                           ROW_NUMBER() OVER(
                               PARTITION BY PIN,
                               CODE ORDER BY ISNULL(tp05.EXPIRYDATE, '99991231') DESC,
                               ISNULL(tp05.DATEFROM, '99991231') DESC
                           )         AS Nr
                    FROM   pw001p05     tp05
                    WHERE  code IN (SELECT DISTINCT csrd1.CERTIFICATECODE
                                    FROM   cteStationRequiredDocs AS csrd1)
                ) p05
         WHERE  p05.Nr = 1
     ),
     cteMedical AS (
         SELECT *
         FROM   (
                    SELECT PIN,
                           tp07.CODE,
                           tp07.EXPIRYDATE,
                           ROW_NUMBER() OVER(
                               PARTITION BY PIN,
                               CODE ORDER BY ISNULL(tp07.EXPIRYDATE, '99991231') DESC,
                               ISNULL(tp07.DATEISSUED, '99991231') DESC
                           )         AS Nr
                    FROM   pw001p07     tp07
                    WHERE  tp07.code IN (SELECT DISTINCT csrd2.CERTIFICATECODE
                                         FROM   cteStationRequiredDocs AS csrd2)
                ) p07
         WHERE  p07.Nr = 1
     ),
     cteCrewDocs AS (
         SELECT csrd.STATIONSEQNO,
                heldDocs.PIN,
                COUNT(heldDocs.Code)       AcquiredDocs
         FROM   cteStationRequiredDocs  AS csrd
                LEFT JOIN (
                         SELECT *
                         FROM   cteCompetence p05
                         UNION ALL 
                         SELECT *
                         FROM   cteMedical P07
                     ) heldDocs
                     ON  csrd.CERTIFICATECODE = heldDocs.code
         GROUP BY
                csrd.STATIONSEQNO,
                heldDocs.PIN
     )

SELECT ves.NUMORGID                     AS VesselID,
       ves.NAME                         AS VesselName,
       wccs.SEQNO                       AS StationSeqno,
       safePlan.[DESCRIPTION]           AS SafetyPlan,
       wccs.code                        AS StationCode,
       wccs.NAME                        AS StationName,
       STUFF(
           (
               SELECT (
                          ' || ' + 'MaxPax:' + CONVERT(VARCHAR, wccsl.MAXPAX) + '- ' + wccsl.NAME
                      )
               FROM   WEB_CP_CSB_STN_SFTYLVL AS wccss
                      LEFT JOIN WEB_CP_CSB_SAFETY_LEVELS AS wccsl
                           ON  wccsl.SEQNO = wccss.SAFETY_LEVEL
               WHERE  wccs.seqno = wccss.station
                      FOR XML PATH('')
           ),
           1,
           3,
           ''
       )                                AS StationSafetyLevels,
       si.SAFETYID,
       si.RANK                          AS SafetyID_Rank,
       si.TITLE                         AS SafetyID_Title,
       STUFF(
           (
               SELECT (' | ' + reQcert.CERTIFICATECODE)
               FROM   cteStationRequiredDocs reqCert
               WHERE  wccs.SEQNO = reqCert.STATIONSEQNO
                      FOR XML PATH('')
           ),
           1,
           3,
           ''
       )                                AS StationRequiredCertificates,
       rdc.StationReqDocCount           AS StationRequiredCertificateCount,
       COALESCE(crewDocCount.AcquiredDocs, 0) AS CrewMemberDocs,
       P03.PIN,
       P01.EMPLOYMENTSTARTDATE,
       P01.EMPLOYMENTENDDATE,
       P01.CLIENT                          NUMORGID,
       p03.ActivityRank,
       p03.ActivityCode,
       P03.IsPlannedActivity,
       p03.DATEFROM                     AS ActivityStartDate,
       P03.TODATEESTIMATED                 ActivityEndDate
FROM   WEB_CP_CSB_STATIONS              AS wccs
       JOIN WEB_CP_CSB_SAFETYPLAN       AS safePlan
            ON  wccs.REVNR = safePlan.REVISIONNR
            AND safePlan.IS_ACTIVE = 1
            AND (NOT (safePlan.[IS_PASSIVE] = 1))
       LEFT JOIN cteStationReqDocsCount rdc
            ON  wccs.SEQNO = rdc.STATIONSEQNO
       LEFT JOIN WEB_CP_CSB_STN_SFTYID  AS wccss
            ON  wccs.SEQNO = wccss.STATION
       LEFT JOIN WEB_CP_CSB_SAFETYID    AS SI
            ON  wccss.SAFETYID = SI.SEQNO
       LEFT JOIN pworg ves
            ON  ves.NUMORGID = wccs.NUMORGID
       LEFT JOIN (
                SELECT p3.PIN,
                       p3.CODE    AS ActivityCode,
                       p3.[RANK]  AS ActivityRank,
                       p3.DATEFROM,
                       P3.DATETO,
                       P3.TODATEESTIMATED,
                       dbo.ad_scanorgtree(P3.NUMORGID, 3) AS ActivityVesselID,
                       ISNULL(P3.PLANNED, 'N')IsPlannedActivity
                FROM   PW001P03   AS P3
                WHERE  P3.DATETO IS NULL
                       AND EXISTS(
                               SELECT 1
                               FROM   PW001C12 C12
                               WHERE  C12.CODE = P3.CODE
                                      AND C12.OPTIONS LIKE '%S%'
                           )
            ) P03
            ON  P03.ActivityVesselID = ves.NUMORGID
            AND p03.datefrom = (
                    SELECT MIN(tp3.DATEFROM)
                    FROM   PW001P03 tp3
                    WHERE  tp3.pin = p03.pin
                           AND tp3.dateto IS NULL
                           AND dbo.ad_scanorgtree(tp3.NUMORGID, 3) = P03.ACTIVITYVESSELID
                           AND EXISTS(
                                   SELECT 1
                                   FROM   PW001C12 tc12
                                   WHERE  tc12.CODE = tp3.CODE
                                          AND tc12.OPTIONS LIKE '%S%'
                               )
                )
            AND CASE 
                     WHEN si.[RANK] IS NOT NULL THEN si.[RANK]
                     ELSE P03.ActivityRank
                END = P03.ActivityRank
       LEFT JOIN cteCrewDocs crewDocCount
            ON  p03.PIN = crewDocCount.PIN
            AND crewDocCount.STATIONSEQNO = wccs.SEQNO
       LEFT JOIN PW001P01 P01
            ON  P01.PIN = P03.PIN
WHERE  (NOT (wccs.[IS_PASSIVE] = 1))
       AND (
               COALESCE(crewDocCount.AcquiredDocs, 0) = COALESCE(rdc.StationReqDocCount, 0)
           )