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

===== Description =====
Telephone - fax - email etc

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **phoneid** | char(20) | NOT NULL |  |  |
| phonetypeid | char(20) | NOT NULL |  |  |
| who | char(2) | NOT NULL |  |  |
| whoid | char(20) | NOT NULL |  |  |
| number | char(200) | NOT NULL |  |  |
| numberdigits | char(200) | NULL |  |  |
| numberdialable | char(200) | NULL |  |  |

===== Primary Key =====
  * phoneid

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| phonetype | phonetypeid | [[database:tables:pears_phonetype|pears.phonetype (phonetypeid)]] | NOT NULL;  |

===== Referenced By =====
  * No incoming foreign keys found.

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| phone_whoid | Index | who, whoid |  |
| phone_idwho | Index | whoid, who |  |
| phone_number | Index | number |  |
| phone_digits | Index | numberdigits |  |
| phone_dialable | Index | numberdialable |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| lsm_phoneupdate | before | insert,update order 1 |
| psHealthPhoneInsert | after | insert order 900 |
| PhoneKeyWords | after | insert,update order 20 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."phone"
-- Table comment: Telephone - fax - email etc
-- Statement count: 13

CREATE TABLE "pears"."phone" (
    "phoneid"                        char(20) NOT NULL
   ,"phonetypeid"                    char(20) NOT NULL
   ,"who"                            char(2) NOT NULL
   ,"whoid"                          char(20) NOT NULL
   ,"number"                         char(200) NOT NULL
   ,"numberdigits"                   char(200) NULL COMPUTE ("pears"."digitsonly"("number"))
   ,"numberdialable"                 char(200) NULL COMPUTE ("pears"."dialable"("number"))
   ,PRIMARY KEY ("phoneid" ASC) 
)
go


COMMENT ON TABLE "pears"."phone" IS 
	'Telephone - fax - email etc'
go


ALTER TABLE "pears"."phone"
    ADD NOT NULL FOREIGN KEY "phonetype" ("phonetypeid" ASC)
    REFERENCES "pears"."phonetype" ("phonetypeid")
go


CREATE INDEX "phone_whoid" ON "pears"."phone"
    ( "who","whoid" )
go


CREATE INDEX "phone_idwho" ON "pears"."phone"
    ( "whoid","who" )
go


CREATE INDEX "phone_number" ON "pears"."phone"
    ( "number" )
go


CREATE INDEX "phone_digits" ON "pears"."phone"
    ( "numberdigits" )
go


CREATE INDEX "phone_dialable" ON "pears"."phone"
    ( "numberdialable" )
go


create trigger "lsm_phoneupdate" before insert,update order 1 on
"pears"."phone"
referencing new as "np"
for each row
begin
  declare "spaycont" char(20);
  declare "saccont" char(20);
  if "np"."who" = 'P' then
    update "pay_employee" set "transferbatch" = 0 where "personid" = "np"."whoid" and "transferbatch" <> 0;
    update "accordemployee" set "transferbatch" = 0 where "personid" = "np"."whoid"
  else
    if "np"."who" = 'C' then
      update "companyaccount" set "transferbatch" = 0 where "companyid" = "np"."whoid" and "transferbatch" <> 0
    else
      select "ca"."timesheetcontact","ca"."accountscontact" into "spaycont","saccont" from "companyaccount" as "ca" join "employment" as "emp"
          on "emp"."companyid" = "ca"."companyid" where "emp"."employmentid" = "np"."whoid";
      if "np"."whoid" = "spaycont" or "np"."whoid" = "saccont" then
        update "companyaccount" as "ca" join "employment" as "emp" on "emp"."companyid" = "ca"."companyid" set "ca"."transferbatch" = 0
          where "emp"."employmentid" = "np"."whoid" and "transferbatch" <> 0
      end if
    end if
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."phone"."lsm_phoneupdate" IS 
{create trigger lsm_phoneupdate 
 before insert,update order 1 on
pears.phone
referencing new as np
for each row
begin
  declare spaycont char(20);
  declare saccont char(20);
  if np.who = 'P' then
    update pay_employee set transferbatch = 0 where personid = np.whoid and transferbatch <> 0;
    update accordemployee set transferbatch = 0 where personid = np.whoid
  else
    if np.who = 'C' then
      update companyaccount set transferbatch = 0 where companyid = np.whoid and transferbatch <> 0
    else
      select ca.timesheetcontact,ca.accountscontact into spaycont,saccont from companyaccount as ca join employment as emp on
        emp.companyid = ca.companyid where emp.employmentid = np.whoid;
      if np.whoid = spaycont or np.whoid = saccont then
        update companyaccount as ca join employment as emp on emp.companyid = ca.companyid set ca.transferbatch = 0 where
          emp.employmentid = np.whoid and transferbatch <> 0
      end if
    end if
  end if
end
}
go


create trigger "psHealthPhoneInsert" after insert order 900 on
"pears"."phone"
referencing new as "new_rec"
for each row
when("new_rec"."who" = 'P')
begin
  call "psHealthUpdatePhone"("new_rec"."whoid","new_rec"."phonetypeid","new_rec"."number")
end
go


create trigger "PhoneKeyWords" after insert,update order 20 on
"pears"."Phone"
referencing new as "NewRow"
for each row
begin
  case "NewRow"."Who"
  when 'P' then update "PersonKeyWords" set "RefreshRequired" = 1 where "PersonID" = "NewRow"."WhoID"
  when 'C' then update "CompanyKeyWords" set "RefreshRequired" = 1 where "CompanyID" = "NewRow"."WhoID"
  when 'CP' then update "CompanyKeyWords" set "RefreshRequired" = 1 from "Company" key join "Employment" where "EmploymentID" = "NewRow"."WhoID" and "CompanyKeyWords"."CompanyID" = "Company"."CompanyID"
  end case
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."phone"."PhoneKeyWords" IS 
{create trigger PhoneKeyWords 
after  insert, update  order 20 on 
   pears.Phone
  referencing new as NewRow 
 for each row
 begin
 case NewRow.Who
   when 'P'  then update PersonKeyWords set RefreshRequired = 1 where PersonID = NewRow.WhoID;
   when 'C'  then update CompanyKeyWords set RefreshRequired = 1 where CompanyID = NewRow.WhoID;
   when  'CP' then update CompanyKeyWords set RefreshRequired = 1  from Company Key join Employment where EmploymentID = NewRow.WhoID and CompanyKeyWords.CompanyID = Company.CompanyID;
 end 
 end
}
go
</code>