pears.NetOwnerVacancyEdit

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

CREATE PROCEDURE "pears"."NetOwnerVacancyEdit"( IN "pWebUserID" CHAR(20),IN "pObjID" CHAR(20),IN "pParentObject" CHAR(20),IN "pPosition" CHAR(50),IN "pTheir_Ref" CHAR(50),IN "pStart_Date__DT" CHAR(20) DEFAULT NULL,IN "pFinish_Date__DT" CHAR(20) DEFAULT NULL,IN "pExpiry_Date__DT" CHAR(20) DEFAULT NULL,IN "pOur_Ref" CHAR(20),IN "pContract_Ref" CHAR(20),IN "pNotes" long VARCHAR,IN "pOldPosition" CHAR(50),IN "pOldTheir_Ref" CHAR(50),
  IN "pOldStart_Date__DT" CHAR(20) DEFAULT NULL,IN "pOldFinish_Date__DT" CHAR(20) DEFAULT NULL,IN "pOldExpiry_Date__DT" CHAR(20) DEFAULT NULL,IN "pOldOur_Ref" CHAR(20),IN "pOldContract_Ref" CHAR(20),IN "pOldNotes" long VARCHAR ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "VacID" CHAR(20);
  DECLARE @deptID CHAR(20);
  DECLARE @PayrollCompanyID CHAR(20);
  DECLARE @DivisionID CHAR(20);
  DECLARE @TempdeskID CHAR(20);
  SET @DivisionID = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pWebUserID");
  IF @DivisionID IS NULL THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET @TempdeskID = (SELECT FIRST "staff"."tempdeskid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pWebUserID");
  IF "length"("trim"("pPosition")) = 0 THEN
    SELECT '101:~Please ensure the Position field is filled';
    RETURN
  END IF;
  IF "length"("trim"("pObjID")) = 0 THEN
    SET "VacID" = "uniquekey"("pPosition");
    SET @PayrollCompanyID = (SELECT "DefPayrollCoID" FROM "Division" WHERE "divisionid" = @DivisionID);
    SET @deptID = (SELECT "defaultdepartid" FROM "staff" WHERE "staffid" = (SELECT FIRST "staff"."staffid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pWebUserID"));
    INSERT INTO "vacancy"( "vacancyid","position","departmentid","employmentid","status","PayrollIdentifier","temp","tempdeskID" ) VALUES( "VacID","pPosition",@deptID,"pParentObject",'C',@PayrollCompanyID,1,@TempdeskID ) 
  ELSE
    SET "VacID" = "pObjID";
    UPDATE "vacancy" SET "position" = "pPosition" WHERE "vacancyid" = "VacID"
  END IF;
  UPDATE "vacancy" SET "TheirRef" = "pTheir_Ref" WHERE "vacancyid" = "VacID";
  IF "pStart_Date__DT" > '' THEN
    UPDATE "vacancy" SET "startdate" = "substr"("pStart_Date__DT",11,4)+'/'+"substr"("pStart_Date__DT",8,2)+'/'+"substr"("pStart_Date__DT",5,2) WHERE "vacancyid" = "VacID"
  ELSE
    UPDATE "vacancy" SET "startdate" = NULL WHERE "vacancyid" = "VacID"
  END IF;
  IF "pFinish_Date__DT" > '' THEN
    UPDATE "vacancy" SET "finishdate" = "substr"("pFinish_Date__DT",11,4)+'/'+"substr"("pFinish_Date__DT",8,2)+'/'+"substr"("pFinish_Date__DT",5,2) WHERE "vacancyid" = "VacID"
  ELSE
    UPDATE "vacancy" SET "finishdate" = NULL WHERE "vacancyid" = "VacID"
  END IF;
  IF "pExpiry_Date__DT" > '' THEN
    UPDATE "vacancy" SET "expiry" = "substr"("pExpiry_Date__DT",11,4)+'/'+"substr"("pExpiry_Date__DT",8,2)+'/'+"substr"("pExpiry_Date__DT",5,2) WHERE "vacancyid" = "VacID"
  ELSE
    UPDATE "vacancy" SET "expiry" = NULL WHERE "vacancyid" = "VacID"
  END IF;
  UPDATE "vacancy" SET "refcode" = "pOur_Ref" WHERE "vacancyid" = "VacID";
  UPDATE "vacancy" SET "Contractref" = "pContract_Ref" WHERE "vacancyid" = "VacID";
  UPDATE "vacancy" SET "othernotes" = "pNotes" WHERE "vacancyid" = "VacID";
  SELECT "string"('0:',"VacID",'#',"pParentObject",'~Success')
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerVacancyEdit" IS 
{CREATE PROCEDURE pears."NetOwnerVacancyEdit"( IN "pWebUserID" CHAR(20),IN "pObjID" CHAR(20),IN "pParentObject" CHAR(20),IN "pPosition" CHAR(50),IN "pTheir_Ref" CHAR(50),IN "pStart_Date__DT" CHAR(20) DEFAULT NULL,IN "pFinish_Date__DT" CHAR(20) DEFAULT NULL,IN "pExpiry_Date__DT" CHAR(20) DEFAULT NULL,IN "pOur_Ref" CHAR(20),IN "pContract_Ref" CHAR(20),IN "pNotes" long VARCHAR,IN "pOldPosition" CHAR(50),IN "pOldTheir_Ref" CHAR(50),
  IN "pOldStart_Date__DT" CHAR(20) DEFAULT NULL,IN "pOldFinish_Date__DT" CHAR(20) DEFAULT NULL,IN "pOldExpiry_Date__DT" CHAR(20) DEFAULT NULL,IN "pOldOur_Ref" CHAR(20),IN "pOldContract_Ref" CHAR(20),IN "pOldNotes" long VARCHAR ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "VacID" CHAR(20);
  DECLARE @deptID CHAR(20);
  DECLARE @PayrollCompanyID CHAR(20);
  DECLARE @DivisionID CHAR(20);
  DECLARE @TempdeskID CHAR(20);
  SET @DivisionID = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pWebUserID");
  IF @DivisionID IS NULL THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET @TempdeskID = (SELECT FIRST "staff"."tempdeskid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pWebUserID");
  IF "length"("trim"("pPosition")) = 0 THEN
    SELECT '101:~Please ensure the Position field is filled';
    RETURN
  END IF;
  IF "length"("trim"("pObjID")) = 0 THEN
    SET "VacID" = "uniquekey"("pPosition");
    SET @PayrollCompanyID = (SELECT "DefPayrollCoID" FROM "Division" WHERE "divisionid" = @DivisionID);
    SET @deptID = (SELECT "defaultdepartid" FROM "staff" WHERE "staffid" = (SELECT FIRST "staff"."staffid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pWebUserID"));
    INSERT INTO "vacancy"( "vacancyid","position","departmentid","employmentid","status","PayrollIdentifier","temp","tempdeskID" ) VALUES( "VacID","pPosition",@deptID,"pParentObject",'C',@PayrollCompanyID,1,@TempdeskID ) 
  ELSE
    SET "VacID" = "pObjID";
    UPDATE "vacancy" SET "position" = "pPosition" WHERE "vacancyid" = "VacID"
  END IF;
  UPDATE "vacancy" SET "TheirRef" = "pTheir_Ref" WHERE "vacancyid" = "VacID";
  IF "pStart_Date__DT" > '' THEN
    UPDATE "vacancy" SET "startdate" = "substr"("pStart_Date__DT",11,4)+'/'+"substr"("pStart_Date__DT",8,2)+'/'+"substr"("pStart_Date__DT",5,2) WHERE "vacancyid" = "VacID"
  ELSE
    UPDATE "vacancy" SET "startdate" = NULL WHERE "vacancyid" = "VacID"
  END IF;
  IF "pFinish_Date__DT" > '' THEN
    UPDATE "vacancy" SET "finishdate" = "substr"("pFinish_Date__DT",11,4)+'/'+"substr"("pFinish_Date__DT",8,2)+'/'+"substr"("pFinish_Date__DT",5,2) WHERE "vacancyid" = "VacID"
  ELSE
    UPDATE "vacancy" SET "finishdate" = NULL WHERE "vacancyid" = "VacID"
  END IF;
  IF "pExpiry_Date__DT" > '' THEN
    UPDATE "vacancy" SET "expiry" = "substr"("pExpiry_Date__DT",11,4)+'/'+"substr"("pExpiry_Date__DT",8,2)+'/'+"substr"("pExpiry_Date__DT",5,2) WHERE "vacancyid" = "VacID"
  ELSE
    UPDATE "vacancy" SET "expiry" = NULL WHERE "vacancyid" = "VacID"
  END IF;
  UPDATE "vacancy" SET "refcode" = "pOur_Ref" WHERE "vacancyid" = "VacID";
  UPDATE "vacancy" SET "Contractref" = "pContract_Ref" WHERE "vacancyid" = "VacID";
  UPDATE "vacancy" SET "othernotes" = "pNotes" WHERE "vacancyid" = "VacID";
  SELECT "string"('0:',"VacID",'#',"pParentObject",'~Success')
END
}