====== pears.NetAlreadyApplied ======
<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 procedure "pears"."NetAlreadyApplied"( in "pWebUserId" char(20),in "JobID" char(20) default null ) 
result( "Applied" char(250) )  -- 20120424 has a person already applied for this job? Use in conjunction with Job Search
begin
  declare "AppliedFor" char(20);
  if "pWebUserId" is null then
    select '99:~You are not logged in';
    return
  end if;
  set "AppliedFor" = (select top 1 "status" from "progress" where "vacancyid" = "JobId" and "personid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserId") order by "actiondate" desc);
  if "AppliedFor" is not null then
    select '1:~Already Applied'
  else
    select '1:~Apply'
  end if
end /* DOC
2018-10-10 MHS IW-456 multi-cand fix and tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries
*/
/* TEST
call NetTestSetup('');
delete from progress where personid='TEST' and vacancyid='TEST';
select * from NetAlreadyApplied('test.candidate','TEST');
expect Applied=1:~Apply;
insert into progress (progressid,personid,vacancyid,staffid,status) values (uniquekey(''),'TEST','TEST',userstaffid,'A');
select * from NetAlreadyApplied('test.candidate','TEST');
expect Applied=1:~Already Applied;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAlreadyApplied" IS 
{create PROCEDURE pears."NetAlreadyApplied"( in "pWebUserId" char(20),in "JobID" char(20) default null ) 
result( "Applied" char(250) )  -- 20120424 has a person already applied for this job? Use in conjunction with Job Search
begin
  declare "AppliedFor" char(20);
  if "pWebUserId" is null then
    select '99:~You are not logged in';
    return
  end if;
  set "AppliedFor" = (select top 1 "status" from "progress" where "vacancyid" = "JobId" and "personid" in (select personid from iqxnetuserlink where iqxnetuserid=pWebUserId) order by actiondate desc);
  if "AppliedFor" is not null then
    select '1:~Already Applied'
  else
    select '1:~Apply'
  end if
end

/* DOC
2018-10-10 MHS IW-456 multi-cand fix and tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries
*/
 
/* TEST
call NetTestSetup('');
delete from progress where personid='TEST' and vacancyid='TEST';
select * from NetAlreadyApplied('test.candidate','TEST');
expect Applied=1:~Apply;
insert into progress (progressid,personid,vacancyid,staffid,status) values (uniquekey(''),'TEST','TEST',userstaffid,'A');
select * from NetAlreadyApplied('test.candidate','TEST');
expect Applied=1:~Already Applied;
*/
}
</code>