pears.NetCandidateShortlistedJobs

CREATE PROCEDURE "pears"."NetCandidateShortlistedJobs"( IN "pWebUserID" CHAR(20) ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  SELECT DISTINCT "vacancyid" FROM "pears"."Progress" WHERE "personid"
     = any(SELECT "personid" FROM "iqxnetuserlink" WHERE "personid" IS NOT NULL AND "iqxnetuserid" = "pWebUserID")
END /* DOC
2016-12-05 PC test and doc
2018-07-24 PC include improved error handler
2018-10-22 MHS IW-456 multi-cand fix and tests
 
Inputs:
pWebUserID
 
Outputs:
pResult
 
Tests:
invalid pWebUserID
valid pWebUserID no vacancies
valid pWebUserID no vacancies
 
*/
/* TEST
call NetTestSetup('');
select * from NetCandidateShortlistedJobs(null);
expect EOF;
delete from progress where personid='TEST';
select * from NetCandidateShortlistedJobs('test.candidate');
expect EOF;
insert into progress (progressid,vacancyid,status,staffid,personid) values ('TEST','TEST','#','TEST','TEST');
select * from NetCandidateShortlistedJobs('test.candidate');
expect pResult=TEST;
delete from progress where personid='TEST';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateShortlistedJobs" IS 
{CREATE PROCEDURE pears."NetCandidateShortlistedJobs"(IN pWebUserID CHAR(20)) 
RESULT( pResult CHAR(250) ) 
// IQXWeb
BEGIN
 
  SELECT DISTINCT vacancyid FROM "pears"."Progress" WHERE personid IN 
      (SELECT personid FROM iqxnetuserlink WHERE personid IS NOT NULL AND iqxnetuserid=pWebUserID)
 
END
 
/* DOC
2016-12-05 PC test and doc
2018-07-24 PC include improved error handler
2018-10-22 MHS IW-456 multi-cand fix and tests
 
Inputs:
    pWebUserID
 
Outputs:
    pResult
 
Tests:
    invalid pWebUserID
    valid pWebUserID no vacancies
    valid pWebUserID no vacancies
 
*/
 
/* TEST
call NetTestSetup('');
select * from NetCandidateShortlistedJobs(null);
expect EOF;
delete from progress where personid='TEST';
select * from NetCandidateShortlistedJobs('test.candidate');
expect EOF;
insert into progress (progressid,vacancyid,status,staffid,personid) values ('TEST','TEST','#','TEST','TEST');
select * from NetCandidateShortlistedJobs('test.candidate');
expect pResult=TEST;
delete from progress where personid='TEST';
*/
}