CREATE VIEW dbo.VIEWID AS

select
	p01.PIN,
	p01.NAME as Name,
	c06.TEXT as Document,
	p05.code as DocumentCode,
	p05.createtime as Created,
	case when p05.createdby = 'app' then p01.name end as CreatedBy,
	p05.changetime as Changed,
	case when p05.changedby = 'APP' then p01.name end as ChangesMadeBy,
	case when scanneddocno is NULL then 'Scan is missing' when p05.scanvalidity = 0 and scanneddocno is not NULL then 'Scan has not been approved' else 'Scan is valid' End as Status,
	p01.EMPLOYMENTENDDATE,
	p01.EMPLOYMENTSTARTDATE,
	p01.CLIENT NUMORGID

from pw001p05 p05
join pw001c06 c06 on c06.code = p05.code
join pw001p01 p01 on p01.PIN = p05.PIN

where 
((p05.CREATEDBY = 'APP' and p05.changedby IS NULL) or p05.CHANGEDBY = 'APP')
--and
--p05.SCANVALIDITY = 0