CREATE VIEW dbo.PW001SRV58
AS

SELECT final.PIN,
       final.ALTERNATIVEPIN,
       [Full Name],
       [Organization Name],
       [Current Rank],
       [Nationality],
       --[Document Type],
       [Document Code],
       [Document Name],
       [Document Status],
       [Issued Date],
       [Expiry Date],
       VESSEL_NUMORGID,
       [Embarkation Date],
       [Disembarkation Date],
       [Activity Vessel],
       [Activity Department],
       [Activity Position],
       [Scan Status],
       NUMORGID,
       EMPLOYMENTSTARTDATE,
       EMPLOYMENTENDDATE
FROM   (
           SELECT p01.PIN,
                  P01.ALTERNATIVEPIN,
                  p01.CLIENT                NUMORGID,
                  P01.EMPLOYMENTSTARTDATE,
                  P01.EMPLOYMENTENDDATE,
                  p01.[NAME]             AS 'Full Name',
                  org.[NAME]             AS 'Organization Name',
                  c02.NAME               AS 'Current Rank',
                  nat.NATIONALITY        AS 'Nationality',
                  --doc.[TYPE]             AS 'Document Type',
                  doc.CODE               AS 'Document Code',
                  docName.[TEXT]         AS 'Document Name',
                  doc.[STATUS]           AS 'Document Status',
                  docDetails.DATEFROM    AS 'Issued Date',
                  docDetails.EXPIRYDATE  AS 'Expiry Date',
                  ves.NUMORGID           AS VESSEL_NUMORGID,
                  p03plan.DATEFROM       AS 'Embarkation Date',
                  ISNULL(p03plan.DATETO, p03plan.TODATEESTIMATED) AS 'Disembarkation Date',
                  ves.[NAME]             AS 'Activity Vessel',
                  dep.[NAME]             AS 'Activity Department',
                  pos.[NAME]             AS 'Activity Position',
                  CASE 
                       WHEN (
                                docDetails.scanneddocno IS NULL
                                OR oledoc.[DOCUMENT] IS NULL
                            ) THEN NCHAR(9940) + ' Missing'
                       WHEN docDetails.SCANVALIDITY = 1
                            AND docDetails.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
                       WHEN (
                                docDetails.SCANVALIDITY = 0
                                OR docDetails.SCANVALIDITY IS NULL
                            )
                            AND scanneddocno IS NOT NULL THEN NCHAR(10006) +
                            ' Not-Confirmed'
                            --ELSE 'N/A'
                  END                    AS 'Scan Status'
           FROM   (
                      SELECT prc.PIN,
                             prc.[TYPE],
                             CASE 
                                  WHEN p05.CODE IS NULL THEN 'Missing'
                                  WHEN p05.CODE IS NOT NULL THEN 'Expiring'
                             END AS STATUS,
                             prc.COMPETENCE AS CODE,
                             p05.SEQUENCENO
                      FROM   (
                                 SELECT DISTINCT p03.PIN,
                                        CASE 
                                             WHEN c12.CODE IS NOT NULL THEN 'Competence/Certificate'
                                        END AS TYPE,
                                        prc.COMPETENCE
                                 FROM   PW001P03 p03
                                        JOIN PW001C12 c12
                                             ON  c12.CODE = p03.CODE
                                             AND c12.OPTIONS LIKE '%S%'
                                        JOIN PWORGPRC prc
                                             ON  prc.NUMORGID = p03.NUMORGID
                                 WHERE  p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
                                        OR  p03.DATETO IS NULL
                             ) prc
                             LEFT JOIN PW001P05 p05
                                  ON  p05.PIN = prc.PIN
                                  AND prc.COMPETENCE = p05.CODE
                  ) doc
                  LEFT JOIN PW001P01 p01
                       ON  p01.PIN = doc.PIN
                  LEFT JOIN PWORG org
                       ON  org.NUMORGID = p01.CLIENT
                  LEFT JOIN PW001P0P p0p
                       ON  p0p.PIN = p01.PIN
                  LEFT JOIN PW001C02 c02
                       ON  c02.CODE = p0p.POSITIONID
                  LEFT JOIN PWCOUNTRY nat
                       ON  nat.COUNTRYCODE = p01.NATIONALITY
                  LEFT JOIN (
                           SELECT CODE,
                                  [TEXT]
                           FROM   PW001C06
                           UNION ALL
                           SELECT CODE,
                                  VISATYPE AS [TEXT]
                           FROM   PW001C23
                           UNION ALL
                           SELECT CODE,
                                  [TEXT]
                           FROM   PW001C24
                       )docName
                       ON  docName.CODE = doc.CODE
                  LEFT JOIN (
                           SELECT PIN,
                                  SEQUENCENO,
                                  DATEFROM,
                                  EXPIRYDATE,
                                  SCANNEDDOCNO,
                                  SCANVALIDITY
                           FROM   PW001P05 
                           UNION ALL 
                           SELECT PIN,
                                  SEQUENCENO,
                                  DATEISSUED AS DATEFROM,
                                  EXPIRYDATE,
                                  SCANNEDDOCNO,
                                  SCANVALIDITY
                           FROM   PW001P07
                           UNION ALL
                           SELECT PIN,
                                  SEQUENCENO,
                                  DATEFROM,
                                  DATETO AS EXPIRYDATE,
                                  SCANNEDDOCNO,
                                  SCANVALIDITY
                           FROM   PW001P08
                       ) docDetails
                       ON  docDetails.SEQUENCENO = doc.SEQUENCENO
                       AND docDetails.PIN = p01.PIN
                  JOIN PW001P03 p03plan
                       ON  p03plan.PIN = p01.PIN
                       AND p03plan.PLANNED = 'Y'
                       AND p03plan.CODE IN (SELECT t.CODE
                                            FROM   pw001c12 t
                                            WHERE  t.OPTIONS LIKE '%S%')
                       AND NOT EXISTS (
                               SELECT 1
                               FROM   PW001P03 p03t
                               WHERE  p03t.PIN = p01.PIN
                                      AND p03t.PLANNED = 'Y'
                                      AND p03t.CODE IN (SELECT t.CODE
                                                        FROM   PW001C12 t
                                                        WHERE  t.OPTIONS LIKE '%S%')
                                      AND p03t.DATEFROM < p03plan.DateFrom
                           )
                  LEFT JOIN (
                           SELECT NUMORGID,
                                  dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID,
                                  dbo.ad_scanorgtree(NUMORGID, 4) AS DeptID
                           FROM   pworg
                       ) ou
                       ON  ou.NUMORGID = p03plan.NUMORGID
                  LEFT JOIN pworg ves
                       ON  ves.NUMORGID = ou.VesselID
                  LEFT JOIN pworg dep
                       ON  dep.NUMORGID = ou.DeptID
                  LEFT JOIN pworg pos
                       ON  pos.NUMORGID = p03plan.NUMORGID
                  LEFT JOIN PW001OLEDOCS oledoc
                       ON  oledoc.DOCNO = docDetails.scanneddocno
       ) final
WHERE  final.[Expiry Date] <= DATEADD(YEAR, 1, CAST(GETDATE() AS DATE))
       OR  Final.[Expiry Date] IS NULL