====== pears.NetCandidateSetRegistrationComplete ======
<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"."NetCandidateSetRegistrationComplete"( in "pWebUserID" char(20),in "pPersonID" char(20) default null ) 
result( "pResult" char(250) ) 
// IQXWeb
begin
  declare "pnewstatus" char(1);
  set "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  if "pPersonID" is null then
    select '97:~Unique candidate link required';
    return
  end if;
  set "pnewstatus" = "isnull"((select "textvalue" from "iqxnetswitch" where "IQXNetSwitchid" = 'DEFAULTCANDSTATE'),(select top 1 "initialapplicantstatus" from "params" order by "dbid" asc),'C');
  update "person" set "status" = "pnewstatus" where "personid" = "pPersonID";
  delete from "IQXNetWizardState" where "IQXNetUserID" = "pWebUserID";
  select '0:~Success'
end /* DOC
2018-10-22 MHS IW-456 multi-cand fix and tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries. Also modified method of extracting switch value
2019-4-8 MHS added cleanup of IQXNetWizardState
*/
/* TEST
call NetTestSetup('');
update person set status='C' where personid='test';
update iqxnetswitch set textvalue='C' where iqxnetswitchid='DEFAULTCANDSTATE';
update params set initialapplicantstatus='R';
select isnull(status,'!') as A from person where personid='test';
expect A=C;
select pResult from NetCandidateSetRegistrationComplete('test.candidate','test9');
expect 97*;
select pResult from NetCandidateSetRegistrationComplete('test.candidate1',null);
expect 97*;
update params set initialapplicantstatus=null;
update iqxnetswitch set textvalue=null where iqxnetswitchid='DEFAULTCANDSTATE';
select pResult from NetCandidateSetRegistrationComplete('test.candidate','test');
expect pResult=0*;
select isnull(status,'!') as B from person where personid='test';
expect B=C;
update params set initialapplicantstatus='X';
select pResult from NetCandidateSetRegistrationComplete('test.candidate','test');
expect pResult=0*;
select isnull(status,'!') as D from person where personid='test';
expect D=X;
update iqxnetswitch set textvalue='R' where iqxnetswitchid='DEFAULTCANDSTATE';
call NetCandidateSetRegistrationComplete('test.candidate','test');
select isnull(status,'!') as C from person where personid='test';
expect C=R;
update person set status='C' where personid='test';
update iqxnetswitch set textvalue='C' where iqxnetswitchid='DEFAULTCANDSTATE';
update params set initialapplicantstatus='R';
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateSetRegistrationComplete" IS 
{create PROCEDURE pears."NetCandidateSetRegistrationComplete"(pWebUserID char(20),in pPersonID char(20) default null)
RESULT(pResult char(250) )
// IQXWeb
BEGIN
  declare pnewstatus char(1);
  set pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
  if pPersonID is null then
    select '97:~Unique candidate link required';
    return;
  end if;  
  set pnewstatus = isnull((select textvalue from iqxnetswitch where IQXNetSwitchid='DEFAULTCANDSTATE'), (select top 1 "initialapplicantstatus" from "params" order by dbid), 'C');
  update person set status=pnewstatus where personid=pPersonID;
  delete from IQXNetWizardState where IQXNetUserID=pWebUserID;
  select '0:~Success';
END

 /* DOC
2018-10-22 MHS IW-456 multi-cand fix and tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries. Also modified method of extracting switch value
2019-4-8 MHS added cleanup of IQXNetWizardState
*/

/* TEST
call NetTestSetup('');
update person set status='C' where personid='test';
update iqxnetswitch set textvalue='C' where iqxnetswitchid='DEFAULTCANDSTATE';
update params set initialapplicantstatus='R';
select isnull(status,'!') as A from person where personid='test';
expect A=C;
select pResult from NetCandidateSetRegistrationComplete('test.candidate','test9');
expect 97*;
select pResult from NetCandidateSetRegistrationComplete('test.candidate1',null);
expect 97*;
update params set initialapplicantstatus=null;
update iqxnetswitch set textvalue=null where iqxnetswitchid='DEFAULTCANDSTATE';
select pResult from NetCandidateSetRegistrationComplete('test.candidate','test');
expect pResult=0*;
select isnull(status,'!') as B from person where personid='test';
expect B=C;
update params set initialapplicantstatus='X';
select pResult from NetCandidateSetRegistrationComplete('test.candidate','test');
expect pResult=0*;
select isnull(status,'!') as D from person where personid='test';
expect D=X;
update iqxnetswitch set textvalue='R' where iqxnetswitchid='DEFAULTCANDSTATE';
call NetCandidateSetRegistrationComplete('test.candidate','test');
select isnull(status,'!') as C from person where personid='test';
expect C=R;
update person set status='C' where personid='test';
update iqxnetswitch set textvalue='C' where iqxnetswitchid='DEFAULTCANDSTATE';
update params set initialapplicantstatus='R';
*/
}
</code>