pears.psHealthInsertUpdatePerson

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