CREATE VIEW dbo.PW001SRV82
(
	PIN,
	"ALTERNATIVE PIN",
	"Full Name",
	"First Name",
	"Last  Name",
	"Middle Name",
	[Organization Name],
	"Rank",
	"Document Code",
	"Document Name",
	"Document No",
	"Document Status",
	"Date Issued",
	"Expiry Date",
	"Issued by",
	"Issued place",
	Comments,
	"Scan Status",
	NUMORGID,
	EMPLOYMENTSTARTDATE,
	EMPLOYMENTENDDATE,
	VESSEl_NUMORGID,
	[Vessel Name],
	[Department],
	[Position],
	[Nationality],
	[Department/Cost Place Code],
	[Department/Cost Place]
)
AS
SELECT P08.PIN,
       P01.ALTERNATIVEPIN  AS 'CAPE ID',
       P01.NAME,
       P01.FIRSTNAME,
       P01.LASTNAME,
       P01.MIDDLENAME,
       ORG.NAME 'Organization Name',
       C02.NAME               RANK,
       P08.CODE 'DOCUMENT CODE',
       C23.VISATYPE 'DOCUMENT Name',
       P08.TDNUMBER           NUMBER,
       CASE 
            WHEN (P08.DATETO > GETDATE() OR P08.DATETO IS NULL) THEN 'Current'
            WHEN (P08.DATETO <= GETDATE() OR P08.DATETO IS NULL) THEN 'Historical'
            ELSE 'N/A'
       END                    STATUS,
       P08.DATEFROM           ISSUED,
       P08.DATETO             EXPIRY,
       P08.ISSUEDBY,
       p08.ISSUE_COUNTRY,
       P08.COMMENTS,
       CASE 
            WHEN (P08.scanneddocno IS NULL OR doc.[DOCUMENT] IS NULL) THEN NCHAR(9940) + ' Missing'
            WHEN P08.scanvalidity = 1 AND scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
            WHEN (P08.scanvalidity = 0 OR P08.scanvalidity IS NULL) AND scanneddocno IS NOT NULL THEN NCHAR(10006) +
                 ' Not-Confirmed'
                 --ELSE 'N/A'
       END                 AS scan,
       P01.CLIENT             NUMORGID,
       P01.EMPLOYMENTSTARTDATE,
       P01.EMPLOYMENTENDDATE,
       ou.VesselID         AS VESSEl_NUMORGID,
       ves.[NAME]          AS 'Vessel Name',
       dep.[NAME]          AS 'Department',
       pos.[NAME]          AS 'Position',
       nat.NATIONALITY,
       p01.COSTPLACE       AS 'Department/Cost Place Code',
       c43.[NAME]          AS 'Department/Cost Place'
FROM   PW001P08 P08
       JOIN PW001P03 p03
            ON  p03.PIN = p08.PIN
            AND p03.DATETO IS NULL
            AND p03.PLANNED <> 'Y'
            AND p03.CODE 
                IN (SELECT c12.Code
                    FROM   pw001c12 c12
                    WHERE  c12.OPTIONS LIKE '%S%')
       LEFT JOIN (
                SELECT NUMORGID,
                       dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID,
                       dbo.ad_scanorgtree(NUMORGID, 4) AS DeptID
                FROM   pworg
            ) ou
            ON  ou.NUMORGID = p03.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 = p03.NUMORGID
       LEFT JOIN PW001P01 P01
            ON  P01.PIN = P08.PIN
       LEFT JOIN PWCOUNTRY nat
            ON  nat.COUNTRYCODE = p01.NATIONALITY
       LEFT JOIN PWORG ORG
            ON  ORG.NUMORGID = P01.CLIENT
       LEFT JOIN PW001C23 C23
            ON  C23.CODE = P08.CODE
       LEFT JOIN PW001C02 C02
            ON  C02.CODE = P01.RANK
       LEFT JOIN PW001OLEDOCS doc
            ON  doc.DOCNO = p08.scanneddocno
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE