pears.NetClientVacancyTemplate

CREATE PROCEDURE "pears"."NetClientVacancyTemplate"( IN "pWebUserID" CHAR(20),IN "pTemplateVacancyID" CHAR(20) ) 
RESULT( "VacancyID" CHAR(20),"Position" CHAR(50),"CompanyName" CHAR(60),"ContactName" CHAR(30),"Department" CHAR(30),"VacDescription" long VARCHAR ) 
BEGIN
  SELECT "v"."VacancyID",
    "v"."Position" AS "Position",
    "Company"."Name" AS "CompanyName",
    "Person"."Name" AS "ContactName",
    (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department",
    "v"."othernotes" AS "VacDescription"
    FROM "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person")
    WHERE "Company"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    AND "v"."vacancyid" = "pTemplateVacancyID" AND "v"."temp" = 1 AND "v"."Status" = '[' AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacancyTemplate" IS 
{CREATE PROCEDURE pears."NetClientVacancyTemplate"( IN "pWebUserID" CHAR(20),IN "pTemplateVacancyID" CHAR(20) ) 
RESULT( "VacancyID" CHAR(20),"Position" CHAR(50),"CompanyName" CHAR(60),"ContactName" CHAR(30),"Department" CHAR(30),"VacDescription" long VARCHAR ) 
BEGIN
  SELECT "v"."VacancyID",
    "v"."Position" AS "Position",
    "Company"."Name" AS "CompanyName",
    "Person"."Name" AS "ContactName",
    (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department",
    "v"."othernotes" AS "VacDescription"
    FROM "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person")
    WHERE "Company"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    AND "v"."vacancyid" = "pTemplateVacancyID" AND "v"."temp" = 1 AND "v"."Status" = '[' AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
END
}