====== pears.NetCandidateValidPersonID ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateValidPersonID" IS 
{create FUNCTION pears."NetCandidateValidPersonID"( in pWebUserID char(20), in pPersonID char(20) default null )
RETURNS CHAR(20)
BEGIN
	DECLARE rv CHAR(20);
  set pPersonID=nullif(trim(pPersonID),'');
  if pPersonID is null then -- Return a valid personid only if a unique one is available 
     if (select count(*) from iqxnetuserlink l key join person p where p.status<>'.' and l.IQXNetUserID=pWebUserID)=1 then
        set rv=(select first p.PersonID from iqxnetuserlink l key join person p where p.status<>'.' and l.IQXNetUserID=pWebUserID order by 1)
     end if
  else  -- Validate the supplied personid
     set rv=(select first PersonID from iqxnetuserlink where PersonID=pPersonID and IQXNetUserID=pWebUserID order by 1)
  end if;
	RETURN rv;
END

/* DOC
2018-10-19 MHS created for IW-456
2018-12-19 MHS now exclude archived records
*/

/* TEST
call NetTestSetup('');
select isnull(NetCandidateValidPersonID(null),'NULL');
expect NULL;
select isnull(NetCandidateValidPersonID('test.client'),'NULL');
expect NULL;
select isnull(NetCandidateValidPersonID('test.candidate'),'NULL');
expect TEST;
insert into iqxnetuserlink (iqxnetuserlinkid, personid, iqxnetuserid) values ('TEST_XX1','TEST', 'TEST.CANDIDATE');
select isnull(NetCandidateValidPersonID('test.candidate'),'NULL');
expect NULL;
select isnull(NetCandidateValidPersonID('test.candidate','TEST'),'NULL');
expect TEST;
delete from iqxnetuserlink where iqxnetuserlinkid='TEST_XX1';
*/
}
</code>