--Competence Documents
CREATE VIEW dbo.PW001SRVXXX
(
	PIN,
	"Full Name",
	"First Name",
	"Last  Name",
	"Middle Name",
	Organization,
	"Rank",
	"Document Code",
	"Document Name",
	"Document No",
	"Document Status",
	"Date From",
	"Date To",
	"Expiry Date",
	"Issued by",
	"Issued place",
	Comments,
	"Scan Status",
	NUMORGID,
	EMPLOYMENTSTARTDATE,
	EMPLOYMENTENDDATE,
	[Department/Cost Place Code],
	[Department/Cost Place]
)
AS
SELECT 	P01.PIN,
	
       P01.NAME,
	
       P01.FIRSTNAME,
	       P01.LASTNAME,
	
       P01.MIDDLENAME,
	
       ORG.NAME,
	       C02.[NAME]     AS RankName,
	       C06.CODE       AS CompetenceCode,
	
       C06.[TEXT]     AS CompetenceName,
	
       P05.CNUMBER as   AS No,
	case when
       CASE 
            WHEN (P05.PLANNED = ('N')) AND (P05.EXPIRYDATE > GETDATE() orOR P05.EXPIRYDATE isIS NULL) thenTHEN 'Current'
		 when            WHEN (P05.PLANNED = ('N')) AND (P05.EXPIRYDATE <= GETDATE() orOR P05.EXPIRYDATE isIS NULL) thenTHEN 'Historical'
		 when
            WHEN (P05.PLANNED = ('Y')) thenTHEN 'Planned'
		 else
            ELSE 'N/A'
	END Status,
	
       END               STATUS,
       P05.DATEFROM,
	
       P05.DATETO,
	       P05.EXPIRYDATE,
	       P05.ISSUEDBY,
	
       P05.ISSUEDPLACE,
	
       P05.COMMENTS,
	case when P05.scanneddocno is NULL then
       CASE 
            WHEN (P05.scanneddocno IS NULL OR doc.[DOCUMENT] IS NULL) THEN NCHAR(9940) + ' Missing'
		 when
            WHEN P05.scanvalidity = 1 andAND scanneddocno isIS notNOT nullNULL thenTHEN NCHAR(10004) + ' Confirmed'
		 when 
            WHEN (P05.scanvalidity = 0 and OR P05.scanvalidity IS NULL) AND scanneddocno isIS notNOT nullNULL thenTHEN NCHAR(10006) + 
                 ' Not-Confirmed'
		 else
                 --ELSE 'N/A'
	end as
       END            AS scan,
	       P01.CLIENT     AS NUMORGID,
	       P01.EMPLOYMENTSTARTDATE,
	P01.EMPLOYMENTENDDATE 
FROM
       P01.EMPLOYMENTENDDATE,
       p01.COSTPLACE  AS 'Department/Cost Place Code',
       c43.[NAME]     AS 'Department/Cost Place'
FROM   PW001P01 P01
       JOIN PW001P05 P05
            ON  P05.PIN = P01.PIN
       LEFT JOIN PWORG ORG
            ON  ORG.NUMORGID = P01.CLIENT
       LEFT JOIN PW001C06 C06
            ON  C06.CODE = P05.CODE
       LEFT JOIN PW001C02 C02
            ON  C02.CODE = P01.[RANK]
       LEFT JOIN PW001OLEDOCS doc
            ON  doc.DOCNO = p05.scanneddocno
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE