--Medical Documents 0809/0806/20222023
CREATE VIEW dbo.PW001SRVXXX
(
	PIN,
	"ALTERNATIVE 	PIN",
	"Full Name",
	"First Name",
	"Last  Name",
	"Middle Name",
	Organization,
	RankRANK,
	"Document Code",
	"Document Name",
	"Document Status",
	"Issued Date",
	"Expiry Date",
	"Issued by",
	Comments,
	"Scan Status",
	EMPLOYMENTSTARTDATE,
	EMPLOYMENTENDDATE,
	NUMORGID,
	[Department/Cost Place Code],
	[Department/Cost Place]
)
 AS
SELECT 
	P07.PIN,
	       P01.ALTERNATIVEPIN  AS 'CAPE ID',
       P01.NAME,
	
       P01.FIRSTNAME,
	       P01.LASTNAME,
	       P01.MIDDLENAME,
	
       ORG.NAME,
	
       C02.NAME,
	
       P07.CODE,
	
       C24.TEXT               MEDICAL,
	case when
       CASE 
            WHEN (P07.PLANNED = ('N')) AND (P07.EXPIRYDATE > GETDATE() orOR P07.EXPIRYDATE isIS NULL) thenTHEN 'Current'
		 when
            WHEN (P07.PLANNED = ('N')) AND (P07.EXPIRYDATE <= GETDATE() orOR P07.EXPIRYDATE isIS NULL) thenTHEN 'Historical'
		 when
            WHEN (P07.PLANNED = ('Y')) thenTHEN 'Planned'
		 else
            ELSE 'N/A'
	END as       END                 AS 'Document Status',
       	p07.DATEISSUED,
	
       P07.EXPIRYDATE,
	
       P07.ISSUEDBY,
	       P07.COMMENTS,
	case when P07.scanneddocno is NULL then
       CASE 
            WHEN (P07.scanneddocno IS NULL OR doc.[DOCUMENT] IS NULL) THEN NCHAR(9940) + ' Missing'
		 when
            WHEN P07.scanvalidity = 1 andAND scanneddocno isIS notNOT nullNULL thenTHEN NCHAR(10004) + ' Confirmed'
		 when 
            WHEN (P07.scanvalidity = 0 and OR P07.scanvalidity IS NULL) AND scanneddocno isIS notNOT nullNULL thenTHEN NCHAR(10006) + 
                 ' Not-Confirmed'
		 else
                 --ELSE 'N/A'
	end as
       END                 AS 'Scan Status',
	
       P01.EMPLOYMENTSTARTDATE,
	
       P01.EMPLOYMENTENDDATE,
       	P01.CLIENT             NUMORGID,
       p01.COSTPLACE       AS 'Department/Cost Place Code',
       c43.[NAME]          AS 'Department/Cost Place'
FROM   PW001P07 P07
       LEFT JOIN PW001P01 P01
            ON  P01.PIN = P07.PIN
       LEFT JOIN PWORG ORG
            ON  ORG.NUMORGID = P01.CLIENT
       LEFT JOIN PW001C24 C24
            ON  C24.CODE = P07.CODE
       LEFT JOIN PW001C02 C02
            ON  C02.CODE = P01.RANK
       LEFT JOIN PW001OLEDOCS doc
            ON  doc.DOCNO = p07.scanneddocno
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE