pears.NetCandidateLinkedRecords [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.NetCandidateLinkedRecords ======
<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"."NetCandidateLinkedRecords"( in "pWebUserID" char(20) ) 
result( "PersonID" char(20),"Name" char(30),"Address" char(250),"Division" char(30),"TaxMethod" char(30),"UnRegistered" smallint ) 
// IQXWeb
begin
  declare "unregstat" char(1);
  declare "actualstat" char(1);
  set "unregstat" = (select "isnull"("IQXNetSwitchValue"("pwebuserid",'UNREGCANDSTATE'),''));
  select "p"."personid","p"."name","getpersonaddressonline"("p"."personid"),"division"."name",
    case "isnull"((select "taxmethod" from "pay_employee" where "personid" = "p"."personid"),1)
    when 2 then 'Limited Company'
    when 2 then 'Self-Employed'
    else 'PAYE'
    end,
    if "p"."status" = "unregstat" then 1 else 0 endif
    from "iqxnetuserlink" as "l" key join "person" as "p" key left outer join "division"
    where "l"."iqxnetuserid" = "pWebUserID" and "p"."status" <> '.'
    order by "p"."keyname" asc,"p"."personid" asc
end /* DOC
2018-10-31 MHS created for IW-456
2018-12-19 MHS now exclude archived records
*/
/* TEST
call nettestsetup('');
select * from NetCandidateLinkedRecords(null);
expect EOF;
select * from NetCandidateLinkedRecords('test.candidate');
expect ROW;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateLinkedRecords" IS 
{create PROCEDURE pears."NetCandidateLinkedRecords"(pWebUserID char(20))
RESULT(PersonID char(20), Name char(30), Address char(250), Division char(30), TaxMethod char(30), UnRegistered smallint)
// IQXWeb
BEGIN
  declare unregstat char(1);
  declare actualstat char(1);
  set unregstat=(select isnull(IQXNetSwitchValue(pwebuserid,'UNREGCANDSTATE'),''));
  select p.personid, p.name, getpersonaddressonline(p.personid),division.name,
  case isnull((select taxmethod from pay_employee where personid=p.personid),1)
    when 2 then 'Limited Company'
    when 2 then 'Self-Employed'
    else 'PAYE'
    end,
  if p.status=unregstat then 1 else 0 endif
  from iqxnetuserlink l key join person p key left outer join division
  where l.iqxnetuserid=pWebUserID and p.status<>'.'
  order by p.keyname,p.personid
END

/* DOC
2018-10-31 MHS created for IW-456
2018-12-19 MHS now exclude archived records
*/

/* TEST
call nettestsetup('');
select * from NetCandidateLinkedRecords(null);
expect EOF;
select * from NetCandidateLinkedRecords('test.candidate');
expect ROW;
*/
}
</code>