====== 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>
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" as "l" key join "person" as "p" where "p"."status" <> '.' and "l"."IQXNetUserID" = "pWebUserID") = 1 then
      set "rv" = (select first "p"."PersonID" from "iqxnetuserlink" as "l" key join "person" as "p" where "p"."status" <> '.' and "l"."IQXNetUserID" = "pWebUserID" order by 1 asc)
    -- Validate the supplied personid
    end if
  else set "rv" = (select first "PersonID" from "iqxnetuserlink" where "PersonID" = "pPersonID" and "IQXNetUserID" = "pWebUserID" order by 1 asc)
  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>