pears.psHealthInsertUpdatePerson [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.psHealthInsertUpdatePerson ======
<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"."psHealthInsertUpdatePerson"( 
  in @PersonID char(20),in "RecipientOrCarer" char(1) ) 
begin
  -- RecipientOrCarer R - Recip, C - Carer, U - User ( if needed ), null if just updating
  if exists(select * from "psHealthPerson" where "personid" = @Personid) or("RecipientOrCarer" is null) then
    update "psHealthPerson" set "transferbatch" = 0,"firstName" = "firstword"("forenames"),"lastName" = "surname","gender" = "sex","dateOfBirth" = "dob","addressLineOne" = "addr1",
      "addressLineTwo" = (if "addr3" > '' then "string"("addr2",', ',"addr3") else "addr2" endif),
      "addressCity" = "town","addressCounty" = "county",
      "addressCountry" = "country","addressPostcode" = "postcode","title" = "titleforpayroll" from "psHealthPerson" key join "person" where "person"."personid" = @Personid
  else
    insert into "psHealthPerson"( "addressCity","addressCountry","addressCounty","addressLineOne","addressLineTwo",
      "addressPostcode","dateOfBirth","email","firstName","gender","isActive","lastName","personid","pin",
      "primaryTelephone","psHealthPersonID","recipientflag",
      "secondaryTelephone","tenantBranchId","tenantCarerId","tenantCustomerId","title","transferbatch","username" ) select "town","country","county",
        "addr1",if "addr3" > '' then "string"("addr2",', ',"addr3") else "addr2" endif,
        "postcode","dob",(select first "number" from "phone" key join "phonetype" where "who" = 'P' and "whoid" = "personid" and "name" = 'E-mail'),
        "firstword"("forenames"),"sex",1,"surname","personid","round"(1000+"rand"()*9000,0),(select first "number" from "phone" key join "phonetype" where "who" = 'P' and "whoid" = "personid" and "name" = 'Mobile'),"uniquekey"("personid"),
        "RecipientOrCarer",(select first "number" from "phone" key join "phonetype" where "who" = 'P' and "whoid" = "personid" and "name" = 'Home Telephone'),"psHealthBranch"(null,null,"personid","recipientorcarer"),"appnumber",
        "appnumber","titleforpayroll",0,"psHealthUniqueUsername"("string"("FirstWord"("Forenames"),' ',"Surname"))
        from "person" where "personid" = @PersonID
  end if
end
</code>