pears.VacancyView

View - Adds company address and other useful information to Vacancy

CREATE VIEW "pears"."VacancyView"
  AS SELECT "vacancy"."vacancyid" AS "VacancyID",
    "vacancy"."employmentid" AS "EmploymentID",
    "vacancy"."departmentid" AS "VacancyDepartmentID",
    (SELECT "department"."name" FROM "pears"."department" WHERE "vacancy"."departmentid" = "department"."departmentid") AS "VacancyDepartment",
    "vacancy"."expiry" AS "VacancyExpiryDate",
    "vacancy"."entrydate" AS "VacancyEntryDate",
    "vacancy"."status" AS "VacancyStatusCode",
    (SELECT "status"."name" FROM "pears"."status" WHERE "status"."status" = "vacancy"."status" AND "status"."type" = 'V') AS "VacancyStatusName",
    (SELECT "status"."help" FROM "pears"."status" WHERE "status"."status" = "vacancy"."status" AND "status"."type" = 'V') AS "VacancyStatusDescription",
    "vacancy"."startdate" AS "VacancyStartDate",
    "vacancy"."ontargetearn" AS "VacancyOTE",
    "vacancy"."salary" AS "VacancySalary",
    "vacancy"."clientrate" AS "VacancyClientRate",
    "vacancy"."temprate" AS "VacancyTempRate",
    "vacancy"."temp" AS "VacancyTempFlag",
    "vacancy"."position" AS "VacancyPosition",
    "vacancy"."notes" AS "VacancyNotes",
    "vacancy"."othernotes" AS "VacancyOtherNotes",
    "vacancy"."whynotfilled" AS "VacancyWhyNotFilled",
    NULL AS "VacancyClassCode", -- removed when Origin introduced Now 2016
    (SELECT "origin"."descrip" FROM "pears"."origin" WHERE "vacancy"."originid" = "origin"."originid") AS "VacancyClassDescription",
    "vacancy"."refcode" AS "VacancyRefCode",
    "vacancy"."noofposts" AS "VacancyNoOfPosts",
    "staff"."name" AS "StaffName",
    "staff"."title" AS "StaffTitle",
    (SELECT "tempdesk"."name" FROM "pears"."tempdesk" WHERE "staff"."tempdeskid" = "tempdesk"."tempdeskid") AS "StaffTempDesk",
    (SELECT "division"."name" FROM "pears"."division" WHERE "staff"."divisionid" = "division"."divisionid") AS "StaffDivision",
    (SELECT "agencydetails"."name" FROM "pears"."agencydetails" WHERE "staff"."agencyid" = "agencydetails"."agencyid") AS "StaffAgency",
    (SELECT "department"."name" FROM "pears"."department" WHERE "staff"."defaultdepartid" = "department"."departmentid") AS "StaffDepartment",
    "company"."companyid" AS "ClientCompanyid",
    "company"."name" AS "ClientCompanyName",
    "company"."keyname" AS "ClientCompanyKeyname",
    "company"."addr1" AS "ClientCompanyAddr1",
    "company"."addr2" AS "ClientCompanyAddr2",
    "company"."addr3" AS "ClientCompanyAddr3",
    "company"."town" AS "ClientCompanyTown",
    "company"."county" AS "ClientCompanyCounty",
    "company"."country" AS "ClientCompanyCountry",
    "company"."postcode" AS "ClientCompanyPostcode",
    "company"."notes" AS "ClientCompanyNotes",
    "company"."clientcode" AS "ClientCompanyCode",
    "company"."alert" AS "ClientCompanyAlert",
    "pears"."GetCompanyAddressOnLine"("company"."companyid") AS "ClientAddressOnline",
    "pears"."getcompanyphoneinblock"("company"."companyid") AS "ClientCompanyPhones",
    "person"."name" AS "ClientPersonName",
    "person"."keyname" AS "ClientPersonKeyname",
    "person"."forenames" AS "ClientPersonForenames",
    "person"."surname" AS "ClientPersonSurname",
    "person"."salutation" AS "ClientPersonSalutation",
    "employment"."position" AS "ClientPersonPosition",
    "person"."alert" AS "ClientPersonAlert",
    "pears"."GetPersonPhoneInBlock"("person"."personid") AS "ClientPersonPhones"
    FROM "pears"."vacancy"
      KEY JOIN("pears"."staff","pears"."employment"
      KEY JOIN("pears"."company","pears"."person"))
GO
 
COMMENT ON VIEW "pears"."VacancyView" IS 
	'View - Adds company address and other useful information to Vacancy'
GO
 
COMMENT TO PRESERVE FORMAT ON VIEW "pears"."VacancyView" IS 
{CREATE VIEW VacancyView 
AS SELECT vacancy.vacancyid AS VacancyID,
vacancy.employmentid AS EmploymentID,
vacancy.departmentid AS VacancyDepartmentID,
(SELECT department."name" FROM pears.department WHERE vacancy.departmentid=department.departmentid) AS VacancyDepartment,
vacancy.expiry AS VacancyExpiryDate,
vacancy.entrydate AS VacancyEntryDate,
vacancy.status AS VacancyStatusCode,
(SELECT "name" FROM pears.status WHERE STATUS.status=vacancy.status AND STATUS."type"='V') AS VacancyStatusName,
(SELECT help FROM pears.status WHERE STATUS.status=vacancy.status AND STATUS."type"='V') AS VacancyStatusDescription,
vacancy.startdate AS VacancyStartDate,
vacancy.ontargetearn AS VacancyOTE,
vacancy.salary AS VacancySalary,
vacancy.clientrate AS VacancyClientRate,
vacancy.temprate AS VacancyTempRate,
vacancy.temp AS VacancyTempFlag,
vacancy.position AS VacancyPosition,
vacancy.notes AS VacancyNotes,
vacancy.othernotes AS VacancyOtherNotes,
vacancy.whynotfilled AS VacancyWhyNotFilled,
NULL AS VacancyClassCode,-- removed when Origin introduced Now 2016
(SELECT descrip FROM pears.origin WHERE vacancy.originid=origin.originid) AS VacancyClassDescription,
vacancy.refcode AS VacancyRefCode,
vacancy.noofposts AS VacancyNoOfPosts,
staff.name AS StaffName,
staff.title AS StaffTitle,
(SELECT name FROM pears.tempdesk WHERE staff.tempdeskid = tempdesk.tempdeskid) AS StaffTempDesk,
(SELECT name FROM pears.division WHERE staff.divisionid = division.divisionid) AS StaffDivision,
(SELECT name FROM pears.agencydetails WHERE staff.agencyid = agencydetails.agencyid) AS StaffAgency,
(SELECT name FROM pears.department WHERE staff.defaultdepartid = department.departmentid) AS StaffDepartment,
company.companyid AS ClientCompanyid,
company."name" AS ClientCompanyName,
company.keyname AS ClientCompanyKeyname,
company.addr1 AS ClientCompanyAddr1,
company.addr2 AS ClientCompanyAddr2,
company.addr3 AS ClientCompanyAddr3,
company.town AS ClientCompanyTown,
company.county AS ClientCompanyCounty,
company.country AS ClientCompanyCountry,
company.postcode AS ClientCompanyPostcode,
company.notes AS ClientCompanyNotes,
company.clientcode AS ClientCompanyCode,
company.alert AS ClientCompanyAlert,
pears.GetCompanyAddressOnLine(company.companyid) AS ClientAddressOnline,
pears.getcompanyphoneinblock(company.companyid) AS ClientCompanyPhones,
person."name" AS ClientPersonName,
person.keyname AS ClientPersonKeyname,
person.forenames AS ClientPersonForenames,
person.surname AS ClientPersonSurname,
person.salutation AS ClientPersonSalutation,
employment.position AS ClientPersonPosition,
person.alert AS ClientPersonAlert,
pears.GetPersonPhoneInBlock(person.personid) AS ClientPersonPhones
FROM pears.vacancy KEY JOIN
(pears.staff,pears.employment KEY JOIN
(pears.company,pears.person))
}