pears.NetCandidateValidPersonID

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