CREATE VIEW dbo.VIEWID AS
 
select
	p01.PIN,
	p01.NAME as Name,
	c23.VISATYPE as Document,
	p08.code as DocumentCode,
	p08.createtime as Created,
	case when p08.createdby = 'app' then p01.name end as CreatedBy,
	p08.changetime as Changed,
	case when p08.changedby = 'APP' then p01.name end as ChangesMadeBy,
	case when scanneddocno is NULL then 'Scan is missing' when p08.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 pw001p08 p08
join pw001c23 c23 on c23.code = p08.code
join pw001p01 p01 on p01.PIN = p08.PIN

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