pears.CompanyKeyWordsUpdate

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original 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
}