====== pears.NetAgencyCandidates ======
<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"."NetAgencyCandidates"( in "pWebUserID" char(20) ) 
result( "PersonID" char(20),"CandidateName" char(100),"CandidateReference" char(100),"CandidateStatus" char(50) ) 
// IQXWeb
begin
  declare @SecAgID char(20);
  set @SecAgID = (select top 1 "employment"."companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" order by "employment"."employmentid" asc);
  select "person"."personid",
    "person"."name",
    "pay_employee"."secondaryagencyref",
    (select "name" from "secondaryagencycandidatestatus" where "secondaryagencycandidatestatusid" = "pay_employee"."secondaryagencycandidatestatus") as "status"
    from "person" key join "pay_employee"
    where "pay_employee"."secondaryagencyid" = @secagid
    and "person"."status" = 'A'
    order by "person"."keyname" asc
end /* DOC
present a list of candidates who have worked for an agency in the past
2016-11-29 PC Doc and test
2018-4-17 MS hard-coded status='A' since that is standard and descriptions vary
Worked on tests
2019-03-04 PC IW-873 add top 1 and order by on subqueries
*/
/* TEST
call NetTestSetup('');
update person set status='A' where personid='TEST';
delete from pay_employee where personid='TEST';
select * from NetAgencyCandidates('test.agency');
expect EOF;
insert into pay_employee(personid,secondaryagencyid,secondaryagencyref) values ('TEST','TEST','TestRef');
select * from NetAgencyCandidates('test.agency');
expect PersonID=TEST,CandidateName=test,CandidateReference=TestRef;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyCandidates" IS 
{create PROCEDURE pears."NetAgencyCandidates"(in pWebUserID char(20))
result(PersonID char(20),CandidateName char(100),CandidateReference char(100), CandidateStatus char(50))
// IQXWeb
begin
  declare @SecAgID char(20);
  set @SecAgID=(select top 1 employment.companyid from employment key join iqxnetuserlink where iqxnetuserlink.iqxnetuserid = pwebuserid order by employment.employmentid);
  select person.personid,
    person.name,
    pay_employee.secondaryagencyref,
    (select name from secondaryagencycandidatestatus where secondaryagencycandidatestatusid=pay_employee.secondaryagencycandidatestatus) as status
    from person key join pay_employee
  where 
    pay_employee.secondaryagencyid = @secagid 
    and person.status='A'
  order by
    person.keyname asc
end

/* DOC
present a list of candidates who have worked for an agency in the past
2016-11-29 PC Doc and test
2018-4-17 MS hard-coded status='A' since that is standard and descriptions vary
          Worked on tests
2019-03-04 PC IW-873 add top 1 and order by on subqueries
*/

/* TEST
call NetTestSetup('');
update person set status='A' where personid='TEST';
delete from pay_employee where personid='TEST';
select * from NetAgencyCandidates('test.agency');
expect EOF;
insert into pay_employee(personid,secondaryagencyid,secondaryagencyref) values ('TEST','TEST','TestRef');
select * from NetAgencyCandidates('test.agency');
expect PersonID=TEST,CandidateName=test,CandidateReference=TestRef;
*/
}
</code>