pears.NetCandidateRegistration [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetCandidateRegistration ======
<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"."NetCandidateRegistration"( in "pWebUserID" char(20),in "pDepartmentID" char(10),in "pPersonID" char(20) default null ) 
result( "departmentid" char(10),"personid" char(20),"departmentname" char(100),"temp" smallint,"perm" smallint ) 
//IQXNet
begin
  set "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  select "department"."departmentid",
    "isnull"("search"."personid","pPersonID") as "personid",
    "department"."name",
    "search"."temp",
    "search"."permanent"
    from "department" left outer join "search" on "department"."departmentid" = "search"."departmentid" and "search"."personid" = "pPersonID"
    where "department"."departmentid" = "pDepartmentID"
end /* DOC
2016-12-05 PC test and doc
2018-12-21 MHS if multiple person links, only show the registration linked to explicit pPersonID
Sort tests

Inputs:
pWebUserID
pDepartmentID

Outputs:
departmentid
personid
departmentname
temp
perm

Tests:
invalid pWebUserID
valid pWebUserID, invalid pDepartmentID
valid pWebUserID, pDepartmentID, no depts
valid pWebUserID, pDepartmentID, temp
valid pWebUserID, pDepartmentID, perm
*/
/* TEST
call NetTestSetup('');
select * from NetCandidateRegistration(null,null); 
expect EOF;
select * from NetCandidateRegistration('test.candidate',null); 
expect EOF;
delete from search where personid='TEST';
select *,isnull(temp,'9') as A,isnull(perm,'9') as B from NetCandidateRegistration('test.candidate','~~'); 
expect departmentid=~~,personid=TEST,departmentname=test,A=9,B=9;
insert into search (searchid,temp,permanent,personid,departmentid) values ('test.candidate',1,0,'TEST','~~');
select *,isnull(temp,'9') as C,isnull(perm,'9') as D from NetCandidateRegistration('test.candidate','~~'); 
expect departmentid=~~,personid=TEST,departmentname=test,C=1,D=0;
update search set permanent=1 where personid='test' and departmentid='~~';
select *,isnull(temp,'9') as E,isnull(perm,'9') as F from NetCandidateRegistration('test.candidate','~~'); 
expect departmentid=~~,personid=TEST,departmentname=test,E=1,F=1;
delete from search where personid='TEST';
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateRegistration" IS 
{create PROCEDURE pears."NetCandidateRegistration"( in pWebUserID char(20),in pDepartmentID char(10), in pPersonID char(20) default null ) 
result( departmentid char(10),personid char(20),departmentname char(100),temp smallint,perm smallint ) 
//IQXNet
begin
  set pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
  select department.departmentid,
    isnull(search.personid, pPersonID) as personid,
    department.name,
    search.temp,
    search.permanent
    from department left outer join search on department.departmentid = search.departmentid and search.personid = pPersonID
    where department.departmentid = pDepartmentID
end

/* DOC
2016-12-05 PC test and doc
2018-12-21 MHS if multiple person links, only show the registration linked to explicit pPersonID
           Sort tests

Inputs:
    pWebUserID
    pDepartmentID

Outputs:
    departmentid
    personid
    departmentname
    temp
    perm

Tests:
    invalid pWebUserID
    valid pWebUserID, invalid pDepartmentID
    valid pWebUserID, pDepartmentID, no depts
    valid pWebUserID, pDepartmentID, temp
    valid pWebUserID, pDepartmentID, perm
*/

/* TEST
call NetTestSetup('');
select * from NetCandidateRegistration(null,null); 
expect EOF;
select * from NetCandidateRegistration('test.candidate',null); 
expect EOF;
delete from search where personid='TEST';
select *,isnull(temp,'9') as A,isnull(perm,'9') as B from NetCandidateRegistration('test.candidate','~~'); 
expect departmentid=~~,personid=TEST,departmentname=test,A=9,B=9;
insert into search (searchid,temp,permanent,personid,departmentid) values ('test.candidate',1,0,'TEST','~~');
select *,isnull(temp,'9') as C,isnull(perm,'9') as D from NetCandidateRegistration('test.candidate','~~'); 
expect departmentid=~~,personid=TEST,departmentname=test,C=1,D=0;
update search set permanent=1 where personid='test' and departmentid='~~';
select *,isnull(temp,'9') as E,isnull(perm,'9') as F from NetCandidateRegistration('test.candidate','~~'); 
expect departmentid=~~,personid=TEST,departmentname=test,E=1,F=1;
delete from search where personid='TEST';
*/
}
</code>