Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
CREATE PROCEDURE "pears"."NetCandidateAssignmentDetails"( IN "pWebUserID" CHAR(20),IN "pLineID" CHAR(20) ) RESULT( "CandidateName" CHAR(60),"CandidateAddress" CHAR(150),"CompanyName" CHAR(60),"ReportTo" CHAR(100),"StartDate" CHAR(20),"Duration" CHAR(10),"Placement" CHAR(60),"PhotoID" CHAR(100),"DressCode" CHAR(100),"LocationOfWorkName" CHAR(60),"LocationOfWorkAddress" CHAR(150),"MainOfficeAddress" CHAR(150),"HoursOfWork" CHAR(120),"Experience" CHAR(100),"Risks" CHAR(500),"ExpensesPayable" CHAR(200),"PayRate" CHAR(100),"IntervalsOfPay" CHAR(60),"ConsultantContact" CHAR(80) ) -- 20131219 Dataset for Candidate Assignment page -- 20140311 Modified for multiple choice on Photo ID -- 20140725 Modified to combine site and office address -- 20141125 Layout of timings modified -- 20150803 Modified to show candidate weekly or monthly paid -- 20171023 increase size of IntervalsOfPay parameter -- 20180129 Refer to NetAssignmentDatasheet BEGIN DECLARE "HealthSafetyExtraText" CHAR(500); DECLARE "ExpensesPayableExtraText" CHAR(200); DECLARE "HoursOfWorkExtraText" CHAR(100); DECLARE "ConfirmRateExtraText" CHAR(100); DECLARE "MinWage" DECIMAL(10,2); SET "MinWage" = (SELECT "substring"("FieldSelect",24,"length"("FieldSelect")-24) FROM "pears"."CustomWPFields" WHERE "fieldcategory" = 'plc' AND "fieldname" = 'MinPayRate'); SET "HealthSafetyExtraText" = 'No specific Health and Safety risks identified, however please re-check when you arrive at the premises. Please ensure you familiarise yourself with any applicable procedures when you arrive on site. Please ensure that you use any Personal Proctective Equipment (PPE) which may be required for your assignment. This will minimise any risk to your health and safety. Failure to do so may put you at an increased risk of injury.'; SET "ExpensesPayableExtraText" = 'Not Applicable (unless prior agreement has been made)'; SET "HoursOfWorkExtraText" = 'Standard day. Please discuss with your agent.'; SET "ConfirmRateExtraText" = 'Please confirm rates with your consultant'; SELECT "p"."name" AS "CandidateName", "p"."addr1"+(IF "p"."addr2" <> '' THEN "char"(13)+"p"."addr2" endif)+(IF "p"."addr3" <> '' THEN "char"(13)+"p"."addr3" endif)+(IF "p"."town" <> '' THEN "char"(13)+"p"."town" endif)+(IF "p"."postcode" <> '' THEN "char"(13)+"p"."postcode" endif) AS "CandidateAddress", "c"."name" AS "CompanyName", "isnull"("v"."sitecontact",'Reception / Security') AS "ReportTo", "dateformat"("isnull"("s"."shiftdate","e"."startdate","v"."startdate"),'dd/mm/yyyy') AS "StartDate", "dateformat"("isnull"((IF "s"."timeto" < "s"."timefrom" THEN "s"."shiftdate"+1 ELSE "s"."shiftdate" endif),"e"."leavedate","v"."finishdate"),'dd/mm/yyyy') AS "Duration", "v"."position" AS "Placement", (IF "PhotoList" <> '' THEN "PhotoList" ELSE '' endif) AS "PhotoID",(IF "EquipmentList" <> '' THEN "EquipmentList" ELSE 'Dress Code not specified' endif) AS "DressCode","v"."sitename" AS "LocationOfWorkName", (IF "isnull"("v"."addr1",'') <> '' THEN("v"."addr1"+(IF "v"."addr2" <> '' THEN "char"(13)+"v"."addr2" endif)+(IF "v"."addr3" <> '' THEN "char"(13)+"v"."addr3" endif)+(IF "v"."town" <> '' THEN "char"(13)+"v"."town" endif)+(IF "v"."postcode" <> '' THEN "char"(13)+"v"."postcode" endif)) ELSE("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif) +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif) +(IF "c"."town" <> '' THEN ', '+"c"."town" endif) +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif)) endif) AS "PlaceOfWorkAddress", ("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif) +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif) +(IF "c"."town" <> '' THEN ', '+"c"."town" endif) +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif)) AS "MainOfficeAddress", CAST("dateformat"("s"."TimeFrom",'hh:nn') AS CHAR)+' - '+CAST("dateformat"("s"."TimeTo",'hh:nn') AS CHAR) AS "HoursOfWork", "isnull"('','None specified') AS "Experience", "isnull"(NULL,"HealthSafetyExtraText") AS "Risks", "isnull"(NULL,"ExpensesPayableExtraText") AS "ExpensesPayable", (IF "isnull"(CAST("NetRate" AS CHAR(10)),'0.00') = '0.00' THEN "ConfirmRateExtraText" ELSE CAST("NetRate" AS CHAR(10)) endif) AS "PayRate", (IF(SELECT "payfrequency" FROM "pay_employee" WHERE "personid" = "p"."personid") = '52' THEN 'Weekly' ELSE 'Monthly' endif) AS "IntervalsOfPay","st"."name"+' ('+"st"."email"+')' AS "ConsultantContact", (SELECT "replace"("list"("string"("c"."description","char"(13)) ORDER BY "c"."description" ASC),',','') FROM "tagvalue" AS "tv" KEY JOIN "tagchoice" AS "c" WHERE "tv"."tagid" = 'DR' AND "tv"."taglocation" = 'V' AND "tv"."id" = "v"."vacancyid" AND "isnull"("c"."DoNotPublishToWeb",'0') = '0') AS "EquipmentList", (SELECT "replace"("list"("string"("c"."description","char"(13)) ORDER BY "c"."description" ASC),',','') FROM "tagvalue" AS "tv" KEY JOIN "tagchoice" AS "c" WHERE "tv"."tagid" = 'ID' AND "tv"."taglocation" = 'V' AND "tv"."id" = "v"."vacancyid" AND "isnull"("c"."DoNotPublishToWeb",'0') = '0') AS "PhotoList", (SELECT "placementid" FROM "placement" AS "pla" KEY JOIN "employment" AS "emp" WHERE "pla"."vacancyid" = "v"."vacancyid" AND "emp"."personid" = "p"."personid") AS "PlaID", (IF "substring"("GetMinPayRate"("PlaID","MinWage"),2) = '' THEN NULL ELSE "substring"("GetMinPayRate"("PlaID","MinWage"),2) endif) AS "MinRate", CAST("isnull"("MinRate","substring"("GetMinPayRateVacancy"("v"."vacancyid","MinWage"),2),0) AS DECIMAL(10,2)) AS "NetRate" FROM "tempshift" AS "s" KEY JOIN "vacancy" AS "v" KEY JOIN "employment" AS "e" KEY JOIN "company" AS "c" ,"tempshift" AS "s" LEFT OUTER JOIN "person" AS "p" ,"vacancy" AS "v" KEY JOIN "staff" AS "st" WHERE "s"."tempshiftid" = "pLineID" UNION SELECT "pe"."name" AS "CandidateName", "pe"."addr1"+(IF "pe"."addr2" <> '' THEN "char"(13)+"pe"."addr2" endif)+(IF "pe"."addr3" <> '' THEN "char"(13)+"pe"."addr3" endif)+(IF "pe"."town" <> '' THEN "char"(13)+"pe"."town" endif)+(IF "pe"."postcode" <> '' THEN "char"(13)+"pe"."postcode" endif) AS "CandidateAddress", "c"."name" AS "CompanyName", "isnull"("v"."sitecontact",'Reception / Security') AS "ReportTo", "dateformat"("isnull"("e"."startdate","v"."startdate"),'dd/mm/yyyy') AS "StartDate", "dateformat"("isnull"("e"."leavedate","v"."finishdate"),'dd/mm/yyyy') AS "Duration", "e"."position" AS "Placement", (IF "PhotoList" <> '' THEN "PhotoList" ELSE '' endif) AS "PhotoID",(IF "EquipmentList" <> '' THEN "EquipmentList" ELSE 'Dress Code not specified' endif) AS "DressCode","v"."sitename" AS "LocationOfWorkName", (IF "isnull"("v"."addr1",'') <> '' THEN("v"."addr1"+(IF "v"."addr2" <> '' THEN "char"(13)+"v"."addr2" endif)+(IF "v"."addr3" <> '' THEN "char"(13)+"v"."addr3" endif)+(IF "v"."town" <> '' THEN "char"(13)+"v"."town" endif)+(IF "v"."postcode" <> '' THEN "char"(13)+"v"."postcode" endif)) ELSE("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif) +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif) +(IF "c"."town" <> '' THEN ', '+"c"."town" endif) +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif)) endif) AS "PlaceOfWorkAddress", ("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif) +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif) +(IF "c"."town" <> '' THEN ', '+"c"."town" endif) +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif)) AS "MainOfficeAddress", 'Normal Work Pattern: '+(IF "isnull"("pl"."workmonday","v"."workmonday") = '1' THEN 'Mon, ' endif) +(IF "isnull"("pl"."worktuesday","v"."worktuesday") = '1' THEN 'Tue, ' endif) +(IF "isnull"("pl"."workwednesday","v"."workwednesday") = '1' THEN 'Wed, ' endif) +(IF "isnull"("pl"."workthursday","v"."workthursday") = '1' THEN 'Thu, ' endif) +(IF "isnull"("pl"."workFriday","v"."workFriday") = '1' THEN 'Fri, ' endif) +(IF "isnull"("pl"."worksaturday","v"."worksaturday") = '1' THEN 'Sat, ' endif) +(IF "isnull"("pl"."worksunday","v"."worksunday") = '1' THEN 'Sun' endif) +"char"(13) +(IF CAST("pl"."workStartTime" AS CHAR) = '0' AND CAST("v"."workStartTime" AS CHAR) = '0' THEN '' ELSE 'Normal Start Time: '+(IF "pl"."workStartTime" <> '00:00' THEN CAST("dateformat"("pl"."workStartTime",'hh:nn') AS CHAR) ELSE CAST("dateformat"("v"."workStartTime",'hh:nn') AS CHAR) endif) endif) +"char"(13) +'Normal Daily Hours: '+(IF "isnull"("pl"."worknormalhours",'0') <> '0' THEN CAST("pl"."WorkNormalHours" AS CHAR) ELSE "isnull"(CAST("v"."WorkNormalHours" AS CHAR),"HoursOfWorkExtraText") endif) AS "HoursOfWork", "isnull"('','None specified') AS "Experience", "isnull"(NULL,"HealthSafetyExtraText") AS "Risks", "isnull"(NULL,"ExpensesPayableExtraText") AS "ExpensesPayable", (IF "isnull"(CAST("NetRate" AS CHAR(10)),'0.00') = '0.00' THEN "ConfirmRateExtraText" ELSE CAST("NetRate" AS CHAR(10)) endif) AS "PayRate", (IF(SELECT "payfrequency" FROM "pay_employee" WHERE "personid" = "p"."personid") = '52' THEN 'Weekly' ELSE 'Monthly' endif) AS "IntervalsOfPay","st"."name"+' ('+"st"."email"+')' AS "ConsultantContact", (SELECT "replace"("list"("string"("c"."description","char"(13)) ORDER BY "c"."description" ASC),',','') FROM "tagvalue" AS "tv" KEY JOIN "tagchoice" AS "c" WHERE "tv"."tagid" = 'DR' AND "tv"."taglocation" = 'V' AND "tv"."id" = "v"."vacancyid" AND "isnull"("c"."DoNotPublishToWeb",'0') = '0') AS "EquipmentList", (SELECT "replace"("list"("string"("c"."description","char"(13)) ORDER BY "c"."description" ASC),',','') FROM "tagvalue" AS "tv" KEY JOIN "tagchoice" AS "c" WHERE "tv"."tagid" = 'ID' AND "tv"."taglocation" = 'V' AND "tv"."id" = "v"."vacancyid" AND "isnull"("c"."DoNotPublishToWeb",'0') = '0') AS "PhotoList", '' AS "PlaID", (IF "substring"("GetMinPayRate"("pl"."placementid","MinWage"),2) = '' THEN "MinWage" ELSE "substring"("GetMinPayRate"("pl"."placementid","MinWage"),2) endif) AS "MinRate", CAST("isnull"(CAST("isnull"("MinRate","substring"("GetMinPayRateVacancy"("v"."vacancyid","MinWage"),2),"e"."salary") AS CHAR),0) AS DECIMAL(10,2)) AS "NetRate" FROM "employment" AS "e" KEY JOIN "person" AS "p" ,"employment" AS "e" KEY JOIN "company" AS "c" ,"employment" AS "e" KEY JOIN "person" AS "pe" ,"employment" AS "e" KEY JOIN "placement" AS "pl" KEY JOIN "vacancy" AS "v" KEY JOIN "staff" AS "st" WHERE "e"."employmentid" = "pLineID" END GO COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateAssignmentDetails" IS {CREATE PROCEDURE pears."NetCandidateAssignmentDetails"( IN "pWebUserID" CHAR(20),IN "pLineID" CHAR(20) ) RESULT( "CandidateName" CHAR(60),"CandidateAddress" CHAR(150),"CompanyName" CHAR(60),"ReportTo" CHAR(100),"StartDate" CHAR(20),"Duration" CHAR(10),"Placement" CHAR(60),"PhotoID" CHAR(100),"DressCode" CHAR(100),"LocationOfWorkName" CHAR(60),"LocationOfWorkAddress" CHAR(150),"MainOfficeAddress" CHAR(150),"HoursOfWork" CHAR(120),"Experience" CHAR(100),"Risks" CHAR(500),"ExpensesPayable" CHAR(200),"PayRate" CHAR(100),"IntervalsOfPay" CHAR(60),"ConsultantContact" CHAR(80) ) -- 20131219 Dataset for Candidate Assignment page -- 20140311 Modified for multiple choice on Photo ID -- 20140725 Modified to combine site and office address -- 20141125 Layout of timings modified -- 20150803 Modified to show candidate weekly or monthly paid -- 20171023 increase size of IntervalsOfPay parameter -- 20180129 Refer to NetAssignmentDatasheet BEGIN DECLARE "HealthSafetyExtraText" CHAR(500); DECLARE "ExpensesPayableExtraText" CHAR(200); DECLARE "HoursOfWorkExtraText" CHAR(100); DECLARE "ConfirmRateExtraText" CHAR(100); DECLARE "MinWage" DECIMAL(10,2); SET "MinWage" = (SELECT "substring"("FieldSelect",24,"length"("FieldSelect")-24) FROM "pears"."CustomWPFields" WHERE "fieldcategory" = 'plc' AND "fieldname" = 'MinPayRate'); SET "HealthSafetyExtraText" = 'No specific Health and Safety risks identified, however please re-check when you arrive at the premises. Please ensure you familiarise yourself with any applicable procedures when you arrive on site. Please ensure that you use any Personal Proctective Equipment (PPE) which may be required for your assignment. This will minimise any risk to your health and safety. Failure to do so may put you at an increased risk of injury.'; SET "ExpensesPayableExtraText" = 'Not Applicable (unless prior agreement has been made)'; SET "HoursOfWorkExtraText" = 'Standard day. Please discuss with your agent.'; SET "ConfirmRateExtraText" = 'Please confirm rates with your consultant'; SELECT "p"."name" AS "CandidateName", "p"."addr1"+(IF "p"."addr2" <> '' THEN "char"(13)+"p"."addr2" endif)+(IF "p"."addr3" <> '' THEN "char"(13)+"p"."addr3" endif)+(IF "p"."town" <> '' THEN "char"(13)+"p"."town" endif)+(IF "p"."postcode" <> '' THEN "char"(13)+"p"."postcode" endif) AS "CandidateAddress", "c"."name" AS "CompanyName", "isnull"("v"."sitecontact",'Reception / Security') AS "ReportTo", "dateformat"("isnull"("s"."shiftdate","e"."startdate","v"."startdate"),'dd/mm/yyyy') AS "StartDate", "dateformat"("isnull"((IF "s"."timeto" < "s"."timefrom" THEN "s"."shiftdate"+1 ELSE "s"."shiftdate" endif),"e"."leavedate","v"."finishdate"),'dd/mm/yyyy') AS "Duration", "v"."position" AS "Placement", (IF "PhotoList" <> '' THEN "PhotoList" ELSE '' endif) AS "PhotoID",(IF "EquipmentList" <> '' THEN "EquipmentList" ELSE 'Dress Code not specified' endif) AS "DressCode","v"."sitename" AS "LocationOfWorkName", (IF "isnull"("v"."addr1",'') <> '' THEN("v"."addr1"+(IF "v"."addr2" <> '' THEN "char"(13)+"v"."addr2" endif)+(IF "v"."addr3" <> '' THEN "char"(13)+"v"."addr3" endif)+(IF "v"."town" <> '' THEN "char"(13)+"v"."town" endif)+(IF "v"."postcode" <> '' THEN "char"(13)+"v"."postcode" endif)) ELSE("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif) +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif) +(IF "c"."town" <> '' THEN ', '+"c"."town" endif) +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif)) endif) AS "PlaceOfWorkAddress", ("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif) +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif) +(IF "c"."town" <> '' THEN ', '+"c"."town" endif) +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif)) AS "MainOfficeAddress", CAST("dateformat"("s"."TimeFrom",'hh:nn') AS CHAR)+' - '+CAST("dateformat"("s"."TimeTo",'hh:nn') AS CHAR) AS "HoursOfWork", "isnull"('','None specified') AS "Experience", "isnull"(NULL,"HealthSafetyExtraText") AS "Risks", "isnull"(NULL,"ExpensesPayableExtraText") AS "ExpensesPayable", (IF "isnull"(CAST("NetRate" AS CHAR(10)),'0.00') = '0.00' THEN "ConfirmRateExtraText" ELSE CAST("NetRate" AS CHAR(10)) endif) AS "PayRate", (IF(SELECT "payfrequency" FROM "pay_employee" WHERE "personid" = "p"."personid") = '52' THEN 'Weekly' ELSE 'Monthly' endif) AS "IntervalsOfPay","st"."name"+' ('+"st"."email"+')' AS "ConsultantContact", (SELECT "replace"("list"("string"("c"."description","char"(13)) ORDER BY "c"."description" ASC),',','') FROM "tagvalue" AS "tv" KEY JOIN "tagchoice" AS "c" WHERE "tv"."tagid" = 'DR' AND "tv"."taglocation" = 'V' AND "tv"."id" = "v"."vacancyid" AND "isnull"("c"."DoNotPublishToWeb",'0') = '0') AS "EquipmentList", (SELECT "replace"("list"("string"("c"."description","char"(13)) ORDER BY "c"."description" ASC),',','') FROM "tagvalue" AS "tv" KEY JOIN "tagchoice" AS "c" WHERE "tv"."tagid" = 'ID' AND "tv"."taglocation" = 'V' AND "tv"."id" = "v"."vacancyid" AND "isnull"("c"."DoNotPublishToWeb",'0') = '0') AS "PhotoList", (SELECT "placementid" FROM "placement" AS "pla" KEY JOIN "employment" AS "emp" WHERE "pla"."vacancyid" = "v"."vacancyid" AND "emp"."personid" = "p"."personid") AS "PlaID", (IF "substring"("GetMinPayRate"("PlaID","MinWage"),2) = '' THEN NULL ELSE "substring"("GetMinPayRate"("PlaID","MinWage"),2) endif) AS "MinRate", CAST("isnull"("MinRate","substring"("GetMinPayRateVacancy"("v"."vacancyid","MinWage"),2),0) AS DECIMAL(10,2)) AS "NetRate" FROM "tempshift" AS "s" KEY JOIN "vacancy" AS "v" KEY JOIN "employment" AS "e" KEY JOIN "company" AS "c" ,"tempshift" AS "s" LEFT OUTER JOIN "person" AS "p" ,"vacancy" AS "v" KEY JOIN "staff" AS "st" WHERE "s"."tempshiftid" = "pLineID" UNION SELECT "pe"."name" AS "CandidateName", "pe"."addr1"+(IF "pe"."addr2" <> '' THEN "char"(13)+"pe"."addr2" endif)+(IF "pe"."addr3" <> '' THEN "char"(13)+"pe"."addr3" endif)+(IF "pe"."town" <> '' THEN "char"(13)+"pe"."town" endif)+(IF "pe"."postcode" <> '' THEN "char"(13)+"pe"."postcode" endif) AS "CandidateAddress", "c"."name" AS "CompanyName", "isnull"("v"."sitecontact",'Reception / Security') AS "ReportTo", "dateformat"("isnull"("e"."startdate","v"."startdate"),'dd/mm/yyyy') AS "StartDate", "dateformat"("isnull"("e"."leavedate","v"."finishdate"),'dd/mm/yyyy') AS "Duration", "e"."position" AS "Placement", (IF "PhotoList" <> '' THEN "PhotoList" ELSE '' endif) AS "PhotoID",(IF "EquipmentList" <> '' THEN "EquipmentList" ELSE 'Dress Code not specified' endif) AS "DressCode","v"."sitename" AS "LocationOfWorkName", (IF "isnull"("v"."addr1",'') <> '' THEN("v"."addr1"+(IF "v"."addr2" <> '' THEN "char"(13)+"v"."addr2" endif)+(IF "v"."addr3" <> '' THEN "char"(13)+"v"."addr3" endif)+(IF "v"."town" <> '' THEN "char"(13)+"v"."town" endif)+(IF "v"."postcode" <> '' THEN "char"(13)+"v"."postcode" endif)) ELSE("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif) +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif) +(IF "c"."town" <> '' THEN ', '+"c"."town" endif) +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif)) endif) AS "PlaceOfWorkAddress", ("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif) +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif) +(IF "c"."town" <> '' THEN ', '+"c"."town" endif) +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif)) AS "MainOfficeAddress", 'Normal Work Pattern: '+(IF "isnull"("pl"."workmonday","v"."workmonday") = '1' THEN 'Mon, ' endif) +(IF "isnull"("pl"."worktuesday","v"."worktuesday") = '1' THEN 'Tue, ' endif) +(IF "isnull"("pl"."workwednesday","v"."workwednesday") = '1' THEN 'Wed, ' endif) +(IF "isnull"("pl"."workthursday","v"."workthursday") = '1' THEN 'Thu, ' endif) +(IF "isnull"("pl"."workFriday","v"."workFriday") = '1' THEN 'Fri, ' endif) +(IF "isnull"("pl"."worksaturday","v"."worksaturday") = '1' THEN 'Sat, ' endif) +(IF "isnull"("pl"."worksunday","v"."worksunday") = '1' THEN 'Sun' endif) +"char"(13) +(IF CAST("pl"."workStartTime" AS CHAR) = '0' AND CAST("v"."workStartTime" AS CHAR) = '0' THEN '' ELSE 'Normal Start Time: '+(IF "pl"."workStartTime" <> '00:00' THEN CAST("dateformat"("pl"."workStartTime",'hh:nn') AS CHAR) ELSE CAST("dateformat"("v"."workStartTime",'hh:nn') AS CHAR) endif) endif) +"char"(13) +'Normal Daily Hours: '+(IF "isnull"("pl"."worknormalhours",'0') <> '0' THEN CAST("pl"."WorkNormalHours" AS CHAR) ELSE "isnull"(CAST("v"."WorkNormalHours" AS CHAR),"HoursOfWorkExtraText") endif) AS "HoursOfWork", "isnull"('','None specified') AS "Experience", "isnull"(NULL,"HealthSafetyExtraText") AS "Risks", "isnull"(NULL,"ExpensesPayableExtraText") AS "ExpensesPayable", (IF "isnull"(CAST("NetRate" AS CHAR(10)),'0.00') = '0.00' THEN "ConfirmRateExtraText" ELSE CAST("NetRate" AS CHAR(10)) endif) AS "PayRate", (IF(SELECT "payfrequency" FROM "pay_employee" WHERE "personid" = "p"."personid") = '52' THEN 'Weekly' ELSE 'Monthly' endif) AS "IntervalsOfPay","st"."name"+' ('+"st"."email"+')' AS "ConsultantContact", (SELECT "replace"("list"("string"("c"."description","char"(13)) ORDER BY "c"."description" ASC),',','') FROM "tagvalue" AS "tv" KEY JOIN "tagchoice" AS "c" WHERE "tv"."tagid" = 'DR' AND "tv"."taglocation" = 'V' AND "tv"."id" = "v"."vacancyid" AND "isnull"("c"."DoNotPublishToWeb",'0') = '0') AS "EquipmentList", (SELECT "replace"("list"("string"("c"."description","char"(13)) ORDER BY "c"."description" ASC),',','') FROM "tagvalue" AS "tv" KEY JOIN "tagchoice" AS "c" WHERE "tv"."tagid" = 'ID' AND "tv"."taglocation" = 'V' AND "tv"."id" = "v"."vacancyid" AND "isnull"("c"."DoNotPublishToWeb",'0') = '0') AS "PhotoList", '' AS "PlaID", (IF "substring"("GetMinPayRate"("pl"."placementid","MinWage"),2) = '' THEN "MinWage" ELSE "substring"("GetMinPayRate"("pl"."placementid","MinWage"),2) endif) AS "MinRate", CAST("isnull"(CAST("isnull"("MinRate","substring"("GetMinPayRateVacancy"("v"."vacancyid","MinWage"),2),"e"."salary") AS CHAR),0) AS DECIMAL(10,2)) AS "NetRate" FROM "employment" AS "e" KEY JOIN "person" AS "p" ,"employment" AS "e" KEY JOIN "company" AS "c" ,"employment" AS "e" KEY JOIN "person" AS "pe" ,"employment" AS "e" KEY JOIN "placement" AS "pl" KEY JOIN "vacancy" AS "v" KEY JOIN "staff" AS "st" WHERE "e"."employmentid" = "pLineID" END }