====== pears.NetCandidateEmploymentHistory ======
<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"."NetCandidateEmploymentHistory"( in "pWebUserID" char(20),in "pPersonID" char(20) default null ) 
result( "CompanyName" char(60),"Position" char(60),"ActionDate" char(20),"PositionState" char(50),"VacancyID" char(20),"ThisProgressID" char(20),"ProgressState" char(10),"ProjectRejectStatus" char(10) ) 
// IQXWeb
begin
  set "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  select distinct
    "c"."name",
    "v"."position",
    "p"."actiondate",
    (if "pPersonID" is null then
      "isnull"((select(if "final" = 1 then "s"."name" endif) from "status" as "s" where "type" = 'R' and "s"."status" = "ProjectRejectStatus"),'In progress')
    else
      (select "s"."name" from "status" as "s" where "type" = 'R' and "s"."status" = "ProgressState") // change this to give actual status
    endif) as "FinalProgressState",
    "v"."vacancyid",
    (select "max"("progressid") from "progress" where "vacancyid" = "v"."vacancyid" and "personid" = "p"."personid") as "ThisProgressID",
    (select "Status" from "progress" where "progressid" = "ThisProgressID") as "ProgressState",
    (select "rejectstatus" from "status" as "s" where "type" = 'R' and "s"."status" = "ProgressState") as "ProjectRejectStatus"
    from "progress" as "p"
      key join "vacancy" as "v"
      key join "employment" as "e"
      key join "company" as "c"
      ,"progress" as "p"
      key join "person" as "pe"
    where "p"."personid" = "pPersonID"
    order by "p"."actiondate" desc
end /* DOC
2018-04-03 PC IW-375 job search history
use pWebUserID from candidate portal or pPersonID from other portal
2018-10-22 MHS IW-456 multi-cand fix. Would only work for candidate portal (if at all) but no sign of it being used anywhere.
2019-03-05 PC added parameters to output to avoid schema mismatch
*/
/* TEST
call NetTestSetup('')

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateEmploymentHistory" IS 
{create PROCEDURE pears."NetCandidateEmploymentHistory"(in pWebUserID char(20),pPersonID char(20) default null)
RESULT(CompanyName char(60),Position char(60),ActionDate char(20),PositionState char(50),VacancyID char(20),ThisProgressID char(20),ProgressState char(10),ProjectRejectStatus char(10))
// IQXWeb
BEGIN
    
  set pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
 
    select distinct
        c.name,
        v.position,
        p.actiondate,
        (if pPersonID is null then 
            isnull((select (if final=1 then s.name end if) from status s where type='R' and s.status=ProjectRejectStatus),'In progress')
        ELSE 
            (select s.name from status s where type='R' and s.status=ProgressState) // change this to give actual status
        end if) as FinalProgressState,
        v.vacancyid,
        (select max(progressid) from progress where vacancyid=v.vacancyid and personid=p.personid) as ThisProgressID,
        (select Status from progress where progressid=ThisProgressID) as ProgressState,
        (select rejectstatus from status s where type='R' and s.status=ProgressState) as ProjectRejectStatus
    from 
        progress p
            key join vacancy v
            key join employment e
            key join company c,
        progress p 
            key join person pe
    where 
        p.personid=pPersonID
    order by 
        p.actiondate desc

END

/* DOC
2018-04-03 PC IW-375 job search history
    use pWebUserID from candidate portal or pPersonID from other portal
2018-10-22 MHS IW-456 multi-cand fix. Would only work for candidate portal (if at all) but no sign of it being used anywhere.
2019-03-05 PC added parameters to output to avoid schema mismatch
*/

/* TEST
call NetTestSetup('')

*/
}
</code>