pears.NetCandidateLinkedRecords

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;
*/
}