pears.NetPersonProfileSet

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

CREATE PROCEDURE "pears"."NetPersonProfileSet"( IN "pWebUserID" CHAR(20),IN "ppersonid" CHAR(20),IN "pdob" CHAR(20) DEFAULT 'NOT-SET',IN "phomephone" CHAR(250) DEFAULT 'NOT-SET',IN "pemail" CHAR(250) DEFAULT 'NOT-SET',IN "pOLDdob" CHAR(20) DEFAULT 'NOT-SET',IN "pOLDhomephone" CHAR(250) DEFAULT 'NOT-SET',IN "pOLDemail" CHAR(250) DEFAULT 'NOT-SET' ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "dateofbirth" DATE;
  IF NOT "ppersonid" = any(SELECT "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid") THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "isnull"("pdob",'') <> "isnull"("pOLDdob",'') THEN
    SET "dateofbirth" = "IQXNetStringToDate"("pdob");
    IF "trim"("isnull"("pdob",'')) <> '' AND "dateofbirth" IS NULL THEN
      SELECT '1:~Invalid date of birth';
      RETURN
    END IF;
    UPDATE "person" SET "dob" = "dateofbirth" WHERE "personid" = "ppersonid"
  END IF;
  IF "isnull"("phomephone",'') <> "isnull"("pOLDhomephone",'') THEN
    CALL "setphone"('P','Home Telephone',"ppersonid","phomephone")
  END IF;
  IF "isnull"("pemail",'') <> "isnull"("pOLDemail",'') THEN
    CALL "setphone"('P','E-mail',"ppersonid","pemail");
    UPDATE "iqxnetuserlink" KEY JOIN "iqxnetuser" SET "iqxnetuser"."emailaddress" = "pemail" WHERE "iqxnetuserlink"."personid" = "ppersonid"
  END IF;
  SELECT '0:~Success'
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetPersonProfileSet" IS 
{CREATE PROCEDURE pears."NetPersonProfileSet"( IN "pWebUserID" CHAR(20),IN "ppersonid" CHAR(20),IN "pdob" CHAR(20) DEFAULT 'NOT-SET',IN "phomephone" CHAR(250) DEFAULT 'NOT-SET',IN "pemail" CHAR(250) DEFAULT 'NOT-SET',IN "pOLDdob" CHAR(20) DEFAULT 'NOT-SET',IN "pOLDhomephone" CHAR(250) DEFAULT 'NOT-SET',IN "pOLDemail" CHAR(250) DEFAULT 'NOT-SET' ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "dateofbirth" DATE;
  IF NOT "ppersonid" = any(SELECT "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid") THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "isnull"("pdob",'') <> "isnull"("pOLDdob",'') THEN
    SET "dateofbirth" = "IQXNetStringToDate"("pdob");
    IF "trim"("isnull"("pdob",'')) <> '' AND "dateofbirth" IS NULL THEN
      SELECT '1:~Invalid date of birth';
      RETURN
    END IF;
    UPDATE "person" SET "dob" = "dateofbirth" WHERE "personid" = "ppersonid"
  END IF;
  IF "isnull"("phomephone",'') <> "isnull"("pOLDhomephone",'') THEN
    CALL "setphone"('P','Home Telephone',"ppersonid","phomephone")
  END IF;
  IF "isnull"("pemail",'') <> "isnull"("pOLDemail",'') THEN
    CALL "setphone"('P','E-mail',"ppersonid","pemail");
    UPDATE "iqxnetuserlink" KEY JOIN "iqxnetuser" SET "iqxnetuser"."emailaddress" = "pemail" WHERE "iqxnetuserlink"."personid" = "ppersonid"
  END IF;
  SELECT '0:~Success'
END
}