pears.NetOwnerVacancyEdit [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.NetOwnerVacancyEdit ======
<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"."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
}
</code>