Summary	Crew List view to assign persons in Crew Station Bill Based on Competence.
Keywords	Crew Station Bill
Description

This CrewList view was created to determine and assign persons in Crew Station Bill based on competence.
Selection

SQL statement

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)
           )
Field Specification

Start Page

Link to the documentation on the related Start Page Tile.
Reports, Views and Similar

Assign Persons in CSB based on Competence

Description

Selection

SQL statement

Field Specification

Start Page

Other
