pears.NetCandidateValidPersonID

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';
*/
}