pears.NetCandidateGetJobsAppliedFor

CREATE PROCEDURE "pears"."NetCandidateGetJobsAppliedFor"( IN "pWebUserID" CHAR(20) ) 
RESULT( "progressid" CHAR(20),"vacancyid" CHAR(20),"refcode" CHAR(20),"position" CHAR(50),"department" CHAR(30),"name" CHAR(60),"InterviewDate" DATE,"InterviewTime" TIME,"startdate" DATE,"FinishDate" DATE,"rejectionreason" CHAR(100),"notes" long VARCHAR,"staff" CHAR(60),"email" CHAR(100),"entrydate" DATE,"dateapplied" DATE,"vacancystatus" CHAR(1) ) 
// IQXWeb
BEGIN
  SELECT "p"."progressid",
    "v"."vacancyid",
    "v"."refcode",
    "v"."position",
    (SELECT "name" FROM "department" WHERE "department"."departmentid" = "v"."departmentid"),
    "s"."name",
    "p"."InterviewDate",
    "p"."InterviewTime",
    "v"."startdate",
    "v"."FinishDate",
    "p"."rejectionreason",
    "v"."notes",
    "u"."name" AS "staff",
    "u"."email",
    "v"."entrydate",
    (SELECT FIRST "HistoryDate" FROM "progresshistory" WHERE "progresshistory"."ProgressID" = "p"."ProgressID" ORDER BY "HistoryDate" ASC) AS "dateapplied",
    "v"."status" AS "vacancystatus"
    FROM "progress" AS "p"
      KEY JOIN "vacancy" AS "v"
      KEY JOIN("department" AS "d","staff" AS "u")
      ,"progress" AS "p" JOIN "status" AS "s" ON "s"."type" = 'r' AND "s"."status" = "p"."status"
    WHERE "p"."personid" = any(SELECT "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid")
    ORDER BY "dateapplied" ASC
END /* DOC
3/2/2020 AW initialise
*/
/* TEST
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateGetJobsAppliedFor" IS 
{CREATE PROCEDURE pears."NetCandidateGetJobsAppliedFor"(IN pWebUserID CHAR(20))
RESULT(progressid CHAR(20), vacancyid CHAR(20), refcode CHAR(20), POSITION CHAR(50), department CHAR(30), name CHAR(60), InterviewDate DATE, InterviewTime TIME, startdate DATE, FinishDate DATE, rejectionreason CHAR(100), notes long VARCHAR, staff CHAR(60), email CHAR(100), entrydate DATE, dateapplied DATE, vacancystatus CHAR(1) )
// IQXWeb
BEGIN
 
    SELECT
        p.progressid,
        v.vacancyid,
        v.refcode,
        v.position,
        (SELECT name FROM department WHERE  department.departmentid = v.departmentid ),
        s.name,
        p.InterviewDate,
        p.InterviewTime,
        v.startdate,
        v.FinishDate,
        p.rejectionreason,
        v.notes,
        u.name AS staff,
        u.email,
        v.entrydate,
        (SELECT FIRST HistoryDate FROM progresshistory WHERE progresshistory.ProgressID = p.ProgressID ORDER BY HistoryDate) AS dateapplied,
        v.status AS vacancystatus
    FROM
        progress AS p
        KEY JOIN vacancy AS v
        KEY JOIN (department AS d, staff AS u),
        progress AS p JOIN STATUS AS s ON s.type='r' AND s.status=p.status
    WHERE
        p.personid IN (SELECT personid FROM iqxnetuserlink WHERE iqxnetuserid = pwebuserid)
   ORDER BY  dateapplied
 
END
 
/* DOC
  3/2/2020 AW initialise
*/
 
/* TEST
*/
}