pears.NetOwnerEmployeeEdit

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

CREATE PROCEDURE "pears"."NetOwnerEmployeeEdit"( IN "pWebUserID" CHAR(20),IN "pObjID" CHAR(20),IN "pParentObject" CHAR(20),IN "pForenames" CHAR(50) DEFAULT NULL,IN "pSurname" CHAR(50) DEFAULT NULL,IN "pSalutation" CHAR(50) DEFAULT NULL,IN "pDirect_Phone" CHAR(50) DEFAULT NULL,IN "pMobile" CHAR(50) DEFAULT NULL,IN "pEmail" CHAR(50) DEFAULT NULL,IN "pDirect_Fax" CHAR(50) DEFAULT NULL,IN "pPosition" CHAR(50) DEFAULT NULL,IN "pDepartment" CHAR(50) DEFAULT NULL,IN "pNotes" CHAR(100) DEFAULT NULL,
  IN "pOldForenames" CHAR(50) DEFAULT NULL,IN "pOldSurname" CHAR(50) DEFAULT NULL,IN "pOldSalutation" CHAR(50) DEFAULT NULL,IN "pOldDirect_Phone" CHAR(50) DEFAULT NULL,IN "pOldMobile" CHAR(50) DEFAULT NULL,IN "pOldEmail" CHAR(50) DEFAULT NULL,IN "pOldDirect_Fax" CHAR(50) DEFAULT NULL,IN "pOldPosition" CHAR(50) DEFAULT NULL,IN "pOldDepartment" CHAR(50) DEFAULT NULL,IN "pOldNotes" CHAR(100) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "PersID" CHAR(20);
  DECLARE "ssql" CHAR(255);
  DECLARE "divid" CHAR(20);
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "divid" IS NULL THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "IQXNetHasPermission"("pWebUserID",'ADDCANDIDATES') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "pForenames" = '' OR "pSurname" = '' THEN
    SELECT '101:~Please ensure there is both a Forename and a Surname';
    RETURN
  END IF;
  IF("pObjID" = '') THEN
    SET "PersID" = "uniquekey"("pForenames"+"pSurname");
    INSERT INTO "person"( "personid","name","keyname","salutation","forenames","surname","status" ) VALUES( "PersID","pForenames"+' '+"pSurname","ucase"("pForenames")+' '+"ucase"("pSurname"),"pSalutation","pForenames","pSurname",'C' ) ;
    INSERT INTO "employment"( "employmentid","personid","companyid","temp" ) VALUES( "PersID","PersID","pParentObject",'0' ) 
  ELSE
    SET "PersID" = "pObjID";
    UPDATE "person" SET "forenames" = "pForenames","surname" = "pSurname","name" = "pForenames"+' '+"pSurname","keyname" = "ucase"("pForenames")+' '+"ucase"("pSurname"),"salutation" = "pSalutation" WHERE "personid" = "PersID"
  END IF;
  IF "pNotes" <> "pOldNotes" THEN
    UPDATE "employment" SET "note" = "pNotes" WHERE "personid" = "PersID"
  END IF;
  IF "pPosition" <> "pOldPosition" THEN
    UPDATE "employment" SET "position" = "pPosition" WHERE "personid" = "PersID"
  END IF;
  IF "pDepartment" <> "pOldDepartment" THEN
    UPDATE "employment" SET "department" = "pDepartment" WHERE "personid" = "PersID"
  END IF;
  IF "pDirect_Phone" <> "pOldDirect_Phone" THEN
    CALL "SetPhone"('CP','Direct Telephone',"PersID","pDirect_Phone")
  END IF;
  IF "pMobile" <> "pOldMobile" THEN
    CALL "SetPhone"('CP','Mobile',"PersID","pMobile")
  END IF;
  IF "pEmail" <> "pOldEmail" THEN
    CALL "SetPhone"('CP','E-mail',"PersID","pEmail")
  END IF;
  IF "pDirect_Fax" <> "pOldDirect_Fax" THEN
    CALL "SetPhone"('CP','Direct Fax',"PersID","pDirect_Fax")
  END IF;
  SELECT "string"('0:',"PersID",'#',"pParentObject",'~Success')
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerEmployeeEdit" IS 
{CREATE PROCEDURE pears."NetOwnerEmployeeEdit"( IN "pWebUserID" CHAR(20),IN "pObjID" CHAR(20),IN "pParentObject" CHAR(20),IN "pForenames" CHAR(50) DEFAULT NULL,IN "pSurname" CHAR(50) DEFAULT NULL,IN "pSalutation" CHAR(50) DEFAULT NULL,IN "pDirect_Phone" CHAR(50) DEFAULT NULL,IN "pMobile" CHAR(50) DEFAULT NULL,IN "pEmail" CHAR(50) DEFAULT NULL,IN "pDirect_Fax" CHAR(50) DEFAULT NULL,IN "pPosition" CHAR(50) DEFAULT NULL,IN "pDepartment" CHAR(50) DEFAULT NULL,IN "pNotes" CHAR(100) DEFAULT NULL,
  IN "pOldForenames" CHAR(50) DEFAULT NULL,IN "pOldSurname" CHAR(50) DEFAULT NULL,IN "pOldSalutation" CHAR(50) DEFAULT NULL,IN "pOldDirect_Phone" CHAR(50) DEFAULT NULL,IN "pOldMobile" CHAR(50) DEFAULT NULL,IN "pOldEmail" CHAR(50) DEFAULT NULL,IN "pOldDirect_Fax" CHAR(50) DEFAULT NULL,IN "pOldPosition" CHAR(50) DEFAULT NULL,IN "pOldDepartment" CHAR(50) DEFAULT NULL,IN "pOldNotes" CHAR(100) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "PersID" CHAR(20);
  DECLARE "ssql" CHAR(255);
  DECLARE "divid" CHAR(20);
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "divid" IS NULL THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "IQXNetHasPermission"("pWebUserID",'ADDCANDIDATES') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "pForenames" = '' OR "pSurname" = '' THEN
    SELECT '101:~Please ensure there is both a Forename and a Surname';
    RETURN
  END IF;
  IF("pObjID" = '') THEN
    SET "PersID" = "uniquekey"("pForenames"+"pSurname");
    INSERT INTO "person"( "personid","name","keyname","salutation","forenames","surname","status" ) VALUES( "PersID","pForenames"+' '+"pSurname","ucase"("pForenames")+' '+"ucase"("pSurname"),"pSalutation","pForenames","pSurname",'C' ) ;
    INSERT INTO "employment"( "employmentid","personid","companyid","temp" ) VALUES( "PersID","PersID","pParentObject",'0' ) 
  ELSE
    SET "PersID" = "pObjID";
    UPDATE "person" SET "forenames" = "pForenames","surname" = "pSurname","name" = "pForenames"+' '+"pSurname","keyname" = "ucase"("pForenames")+' '+"ucase"("pSurname"),"salutation" = "pSalutation" WHERE "personid" = "PersID"
  END IF;
  IF "pNotes" <> "pOldNotes" THEN
    UPDATE "employment" SET "note" = "pNotes" WHERE "personid" = "PersID"
  END IF;
  IF "pPosition" <> "pOldPosition" THEN
    UPDATE "employment" SET "position" = "pPosition" WHERE "personid" = "PersID"
  END IF;
  IF "pDepartment" <> "pOldDepartment" THEN
    UPDATE "employment" SET "department" = "pDepartment" WHERE "personid" = "PersID"
  END IF;
  IF "pDirect_Phone" <> "pOldDirect_Phone" THEN
    CALL "SetPhone"('CP','Direct Telephone',"PersID","pDirect_Phone")
  END IF;
  IF "pMobile" <> "pOldMobile" THEN
    CALL "SetPhone"('CP','Mobile',"PersID","pMobile")
  END IF;
  IF "pEmail" <> "pOldEmail" THEN
    CALL "SetPhone"('CP','E-mail',"PersID","pEmail")
  END IF;
  IF "pDirect_Fax" <> "pOldDirect_Fax" THEN
    CALL "SetPhone"('CP','Direct Fax',"PersID","pDirect_Fax")
  END IF;
  SELECT "string"('0:',"PersID",'#',"pParentObject",'~Success')
END
}