CREATE VIEW dbo.PW001SRV710asselectp01PW001SRV710 as
select
	p01.Employmentstartdate,
	p01.Employmentenddate,
	p01.Client Numorgid,
	cad.PIN,
	cad.UField1 as 'Date Submitted',
	case when cad.Ufield2<>'' THEN 'Yes'
		 else '' 
	end as 'CV',
	cad.Ufield4 as 'Agency',
	cad.Ufield5 as 'Approved for HOD Interview',
	cad.Ufield6 as 'HOD Interview Date',
	cad.Ufield7 as 'Approved by HOD',
	cad.Ufield8 as 'Approval Date',
	cad.Ufield9 as 'Availability Date',
	cad.Ufield10 as 'In hold pool',
	cad.Ufield11 as 'Onboard date',
	ves.Name as 'Onboard Vessel',
	rank.Name as 'Onboard Position',
	cad.Ufield14 as 'Declined Position',
	cad.Ufield15 as 'Declined Reason'
	
from PW001U03 CADleftCAD
left join pw001p01 p01 on p01.pin=cad.pinleftpin
left join pworg ves on ves.numorgid=cad.ufield12leftufield12
left join pw001c02 rank on rank.code=cad.Ufield13where--Candidate is approved for HOD interviewcadUfield13
where cad.ufield5='Y'							--Candidate is not already approved and approved for HOD interview 
	  AND cad.ufield7='' 						--Candidate hasis not declined and already approved 
	  AND (cad.Ufield14='N' OR cad.Ufield14='')	--Candidate has not declined