--Travel Documents 9/6/2023
CREATE VIEW dbo.PW001SRVXXX

(
	PIN,
	"ALTERNATIVE PIN",
	"Full Name",
	"First Name",
	"Last  Name",
	"Middle Name",
	Organization,
	"Rank",
	"Document Code",
	"Document Name",
	"Document No",
	"Document Status",
	"Date Issued",
	"Expiry Date",
	"Issued by",
	"Issued place",
	Comments,
	"Scan Status",
	NUMORGID,
	EMPLOYMENTSTARTDATE,
	EMPLOYMENTENDDATE,
	[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 'EMPLOYMENT ORG',
	
       C02.NAME               RANK,
	
       P08.CODE 'DOCUMENT CODE',
	       C23.VISATYPE 'DOCUMENT Name',
       	P08.TDNUMBER           NUMBER,
	case when
       CASE 
            WHEN (P08.DATETO > GETDATE() orOR P08.DATETO isIS NULL) thenTHEN 'Current'
		 when
            WHEN (P08.DATETO <= GETDATE() orOR P08.DATETO isIS NULL) thenTHEN 'Historical'
		 else            ELSE 'N/A'
	END Status,
	
       END                    STATUS,
       P08.DATEFROM           ISSUED,
	       P08.DATETO             EXPIRY,
	
       P08.ISSUEDBY,
	
       p08.ISSUE_COUNTRY,
	
       P08.COMMENTS,
	case when P08.scanneddocno is NULL then
       CASE 
            WHEN (P08.scanneddocno IS NULL OR doc.[DOCUMENT] IS NULL) THEN NCHAR(9940) + ' Missing'
		 when
            WHEN P08.scanvalidity = 1 andAND scanneddocno isIS notNOT nullNULL thenTHEN NCHAR(10004) + ' Confirmed'
		 when 
            WHEN (P08.scanvalidity = 0 andOR P08.scanvalidity IS NULL) AND scanneddocno isIS notNOT nullNULL thenTHEN NCHAR(10006) + 
                 ' Not-Confirmed'
		 else                 --ELSE 'N/A'
	end as
       END                 AS scan,
	       P01.CLIENT             NUMORGID,
	
       P01.EMPLOYMENTSTARTDATE,
	       P01.EMPLOYMENTENDDATE,
       p01.COSTPLACE       AS 'Department/Cost Place Code',
       c43.[NAME]          AS 'Department/Cost Place'
FROM   PW001P08 P08
       LEFT JOIN PW001P01 P01
            ON  P01.PIN = P08.PIN
       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