====== pears.CompanyKeyWordsUpdate ======
<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"."CompanyKeyWordsUpdate"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in @ResetAll tinyint default 0 ) 
begin
  if @ResetAll = 0 then
    update "CompanyKeyWords"
      set "StandardKeyWords" = (select "string"("c"."Name",' ',"c"."ClientCode",' ',"c"."Addr1",' ',"c"."Addr2",' ',"c"."Addr3",' ',"c"."Town",' ',"c"."County",' ',"c"."PostCode",' ',
        (select "list"(distinct "string"("p"."forenames",' ',"p"."surname",' '),' ') from "employment" as "e" key join "person" as "p" where "p"."status" = 'L' and "e"."companyid" = "CompanyKeyWords"."CompanyID" and "isnull"("leavedate",current date) >= current date),
        (select "list"("n"."number",' ') from "Phone" as "n" where "n"."Who" = 'C' and "n"."number" like '% %' and "n"."WhoID" = "CompanyKeyWords"."CompanyID"),' ',
        (select "list"("replace"("n"."number",' ',''),' ') from "Phone" as "n" where "n"."Who" = 'C' and "n"."WhoID" = "CompanyKeyWords"."CompanyID"))
        from "Company" as "c" where "c"."CompanyID" = "CompanyKeyWords"."CompanyID"),
      "TagKeyWords" = "nullif"("CompanyKeyWordsQuestion"("CompanyKeyWords"."CompanyID"),''),
      "CustomKeyWords" = "nullif"("CompanyKeyWordsCustom"("CompanyKeyWords"."CompanyID"),''),
      "RefreshRequired" = 0,
      "CustomRefreshRequired" = 0,
      "TagRefreshRequired" = 0 // this is included abouve, so does not need to be done again 
      where "RefreshRequired" = 1;
    // carry out any tag only updates required 
    update "CompanyKeyWords"
      set "TagKeyWords" = "CompanyKeyWordsQuestion"("CompanyKeyWords"."CompanyID"),
      "TagRefreshRequired" = 0
      where "TagRefreshRequired" = 1;
    update "CompanyKeyWords"
      set "CustomKeyWords" = "nullif"("CompanyKeyWordsCustom"("CompanyKeyWords"."CompanyID"),''),
      "CustomRefreshRequired" = 0
      where "CustomRefreshRequired" = 1
  elseif @ResetAll = 1 then
    update "CompanyKeyWords" 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"."CompanyKeyWordsUpdate" IS 
{create procedure CompanyKeyWordsUpdate 

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

(IN @ResetAll tinyint default 0)
begin
    if @ResetAll = 0 then
        update CompanyKeyWords
            set
            StandardKeyWords = (select string(c.Name,' ',c.ClientCode,' ',c.Addr1,' ',c.Addr2,' ',c.Addr3,' ',c.Town,' ',c.County,' ',c.PostCode, ' ',
                (select list(distinct string(p.forenames,' ', p.surname,' '),' ') from employment e key join person p where p.status = 'L' and e.companyid =CompanyKeyWords.CompanyID and isnull(leavedate, current date)>=current date), 
                (select list(n.number,' ') from Phone n where n.Who = 'C' and n.number like '% %' and n.WhoID = CompanyKeyWords.CompanyID),' ', 
                (select list(replace(n.number,' ',''),' ') from Phone n where n.Who = 'C' and n.WhoID =CompanyKeyWords.CompanyID ) ) 
                from Company c  where c.CompanyID = CompanyKeyWords.CompanyID), 
           TagKeyWords = nullif(CompanyKeyWordsQuestion(CompanyKeyWords.CompanyID),''),
           CustomKeyWords = nullif(CompanyKeyWordsCustom(CompanyKeyWords.CompanyID),''),
           RefreshRequired = 0, 
           CustomRefreshRequired = 0, 
             TagRefreshRequired = 0 // this is included abouve, so does not need to be done again 
         where RefreshRequired = 1;
         // carry out any tag only updates required 
         update CompanyKeyWords 
             set 
             TagKeyWords = CompanyKeyWordsQuestion(CompanyKeyWords.CompanyID), 
             TagRefreshRequired = 0 
         where TagRefreshRequired = 1; 
         update CompanyKeyWords 
             set 
             CustomKeyWords = nullif(CompanyKeyWordsCustom(CompanyKeyWords.CompanyID),''), 
             CustomRefreshRequired = 0 
         where CustomRefreshRequired = 1; 
elseif @ResetAll = 1 
    then update CompanyKeyWords set RefreshRequired = 1; // All records will be refreshed on the next triggering of the procedure (normally by the event) 
end if;
end
}
</code>