====== pears.VacancyKeyWordsUpdate ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."VacancyKeyWordsUpdate"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in @ResetAll tinyint default 0 ) 
begin
  if @ResetAll = 0 then
    update "VacancyKeyWords"
      set "StandardKeyWords" = (select "string"("v"."refcode",' ',"v"."position",' ',"c"."Name",' ',"c"."ClientCode",' ',"c"."Addr1",' ',"c"."Addr2",' ',"c"."Addr3",' ',"c"."Town",' ',"c"."County",' ',"c"."PostCode",' ',
        "v"."siteemail",' ',"v"."sitename",' ',"v"."sitecontact",' ',"p"."forenames",' ',"p"."surname",' ',
        (select "list"("n"."number",' ') from "Phone" as "n" where "n"."Who" = 'C' and "n"."number" like '% %' and "n"."WhoID" = "c"."CompanyID"),' ',
        (select "list"("replace"("n"."number",' ',''),' ') from "Phone" as "n" where "n"."Who" = 'C' and "n"."WhoID" = "c"."companyid"))),
      "TagKeyWords" = "nullif"("VacancyKeyWordsQuestion"("VacancyKeyWords"."VacancyID"),''),
      "CustomKeyWords" = "nullif"("VacancyKeyWordsCustom"("VacancyKeyWords"."VacancyID"),''),
      "RefreshRequired" = 0,
      "CustomRefreshRequired" = 0,
      "TagRefreshRequired" = 0 from // this is included abouve, so does not need to be done again 
      "VacancyKeyWords" join "vacancy" as "v" on("VacancyKeyWords"."VacancyID" = "v"."vacancyid"),"vacancy" as "v" key join "employment" as "e" key join("company" as "c","person" as "p")
      where "RefreshRequired" = 1;
    update "VacancyKeyWords"
      set "TagKeyWords" = "VacancyKeyWordsQuestion"("VacancyKeyWords"."VacancyID"),
      "TagRefreshRequired" = 0
      where "TagRefreshRequired" = 1;
    update "VacancyKeyWords"
      set "CustomKeyWords" = "nullif"("VacancyKeyWordsCustom"("VacancyKeyWords"."VacancyID"),''),
      "CustomRefreshRequired" = 0
      where "CustomRefreshRequired" = 1
  elseif @ResetAll = 1 then
    update "VacancyKeyWords" set "RefreshRequired" = 1 // All records will be refreshed on the next triggering of the procedure (normally by the event) 
  end if
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."VacancyKeyWordsUpdate" IS 
{create procedure VacancyKeyWordsUpdate 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

(IN @ResetAll tinyint default 0)
begin
    if @ResetAll = 0 then
        update VacancyKeyWords
            set
            StandardKeyWords = (select string(v.refcode,' ' ,v.position,' ',c.Name,' ',c.ClientCode,' ',c.Addr1,' ',c.Addr2,' ',c.Addr3,' ',c.Town,' ',c.County,' ',c.PostCode, ' ',
           v.siteemail,' ' ,v.sitename,' ',v.sitecontact,' ' , p.forenames,' ', p.surname,' ', 
                (select list(n.number,' ') from Phone n where n.Who = 'C' and n.number like '% %' and n.WhoID = c.CompanyID), ' ',
                (select list(replace(n.number,' ',''),' ') from Phone n  where n.Who = 'C' and n.WhoID =c.companyid ))), 
           TagKeyWords = nullif(VacancyKeyWordsQuestion(VacancyKeyWords.VacancyID),''),
           CustomKeyWords = nullif(VacancyKeyWordsCustom(VacancyKeyWords.VacancyID),''),
           RefreshRequired = 0, 
           CustomRefreshRequired = 0, 
           TagRefreshRequired = 0 // this is included abouve, so does not need to be done again 
           from VacancyKeyWords join vacancy v on (VacancyKeyWords.VacancyID =v.vacancyid) , vacancy v key join employment e key join (company c, person p) 
        where RefreshRequired = 1;
         update VacancyKeyWords 
             set
             TagKeyWords = VacancyKeyWordsQuestion(VacancyKeyWords.VacancyID), 
            TagRefreshRequired = 0   
        where TagRefreshRequired = 1;
         update VacancyKeyWords 
             set
             CustomKeyWords = nullif(VacancyKeyWordsCustom(VacancyKeyWords.VacancyID),''), 
            CustomRefreshRequired = 0   
        where CustomRefreshRequired = 1;
elseif @ResetAll = 1 
    then update VacancyKeyWords set RefreshRequired = 1; // All records will be refreshed on the next triggering of the procedure (normally by the event) 
end if;
end
}
</code>