pears.NetCandidateEmploymentHistory

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('')
 
*/
}