pears.DuplicateVacancyAndPlacement [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.DuplicateVacancyAndPlacement ======
<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 function "pears"."DuplicateVacancyAndPlacement"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in @OldVacancyID char(20),in @NewVacancyID char(20),in @NewEmploymentID char(20),in @OldPlacementID char(20) default null,in @NewPlacementID char(20) default null,in @CloseOffOld char(1) default 'N' ) 
returns smallint
begin
  declare @VacancyAllowed char(250);
  declare @OldCandEmploymentID char(20);
  declare @NewCandEmploymentID char(20);
  declare @NewCandEmploymentCompanyID char(20);
  declare @ExplainStringFrom char(250);
  declare @ExplainStringTo char(250);
  declare @MovedPerson char(50);
  --Check new vacancy allowed
  set @VacancyAllowed = (select "vacancyallowed"(@NewEmploymentID));
  if @VacancyAllowed <> '' then return(1)
  end if;
  begin atomic
    insert into "vacancy"( "VacancyID","DepartmentID","EmploymentID","Status","temp","Position","ContractRef",
      "ClientDepartment","NoOfPosts","StartDate","FinishDate","TempDeskID","StaffID","SiteName","SiteContact",
      "addr1","addr2","addr3","town","county","country","postcode","SitePhoneNumbers","Notes","OtherNotes",
      "ErNi","HolidayAllowance","Discount","Salary","Expiry","TheirRef","Currency","PayrollIdentifier",
      "TempJobTypeID","InvAddr1","InvAddr2","InvAddr3","InvTown","InvCounty","InvCountry","InvPostCode",
      "ClassCode","AnalysisCode","EntryDate","Role","CascadeDateTime","WorkMonday","WorkTuesday","WorkWednesday","WorkThursday","WorkFriday","WorkSaturday","WorkSunday","WorkStartTime","WorkNormalHours" ) 
      select @NewVacancyID,"DepartmentID",@NewEmploymentID,"Status","temp","Position","ContractRef",
        "ClientDepartment","NoOfPosts","StartDate","FinishDate","TempDeskID","StaffID","SiteName","SiteContact",
        "addr1","addr2","addr3","town","county","country","postcode","SitePhoneNumbers","Notes","OtherNotes",
        "ErNi","HolidayAllowance","Discount","Salary","Expiry","TheirRef","Currency","PayrollIdentifier",
        "TempJobTypeID","InvAddr1","InvAddr2","InvAddr3","InvTown","InvCounty","InvCountry","InvPostCode",
        "ClassCode","AnalysisCode",current date,"Role","CascadeDateTime","WorkMonday","WorkTuesday","WorkWednesday","WorkThursday","WorkFriday","WorkSaturday","WorkSunday","WorkStartTime","WorkNormalHours"
        from "vacancy" where "vacancyid" = @OldVacancyID; //NB Vacancy refcode inserted by trigger in Vacancy table
    -- Copy questionnaire
    insert into "tagvalue"( "taglocation","tagid","tagchoiceid","id","value","textvalue" ) 
      select "taglocation","tagid","tagchoiceid",@NewVacancyID,"value","textvalue" from "tagvalue"
        where "id" = @OldVacancyID and "taglocation" like 'V%';
    -- Copy requirements
    insert into "criterion"( "searchlocation",
      "id","critid","sourcelocation","dictionaryid",
      "tagid","tagchoiceid","notflag","matchtype","textvalue","value","uppervalue","taganytotal",
      "extravalue","extrauppervalue","extramatchtype" ) 
      select "searchlocation",@NewVacancyID,"critid","sourcelocation","dictionaryid",
        "tagid","tagchoiceid","notflag","matchtype","textvalue","value","uppervalue","taganytotal",
        "extravalue","extrauppervalue","extramatchtype" from "criterion"
        where "id" = @OldVacancyID and "searchlocation" like 'V%';
    -- Copy rates
    insert into "TempJobRate"( "TempJobRateID","VacancyID","TempPayBandID","PayRate","ChargeRate","StartDate","EndDate","Grade" ) 
      select(select "uniquekey"("TempJobRateID")),@NewVacancyID,"TempPayBandID","PayRate","ChargeRate","StartDate","EndDate","Grade"
        from "TempJobRate" where "vacancyid" = @OldVacancyID;
    -- Copy T & A rules
    insert into "cardreaderoverridesettings"( "CardReaderSettingsID","Companyid","defaultshiftlength","MachineID","maxbreaklength","MaxEarlyness","MaxExtraBreakTime","MaxLateness","maxshiftlength","MinBeforeExtending","MinBeforeExtendStart","MinBreakLength","VacancyID" ) 
      select "uniquekey"("VacancyID"),"Companyid","defaultshiftlength","MachineID","maxbreaklength","MaxEarlyness","MaxExtraBreakTime","MaxLateness","maxshiftlength","MinBeforeExtending","MinBeforeExtendStart","MinBreakLength",@NewVacancyID from "cardreaderoverridesettings"
        where "vacancyid" = @OldVacancyID;
    -- Cascade Rules
    insert into "CascadeRule"( "CascadeRuleID","CompanyID","VacancyID","AgencyStoredSelectionID","CascadeHours","HoursFrom","CascadeLevel","CustomFunction","VacancyRule" ) 
      select "uniquekey"("CascadeRuleID"),"CompanyID",@NewVacancyID,"AgencyStoredSelectionID","CascadeHours","HoursFrom","CascadeLevel","CustomFunction","VacancyRule"
        from "CascadeRule" where "vacancyid" = @OldVacancyID;
    -- PLACEMENT
    if @OldPlacementID is not null and @NewPlacementID is not null then
      -- Candidates Employment
      set @OldCandEmploymentID = (select "EmploymentID" from "Placement" where "PlacementID" = @OldPlacementID);
      set @NewCandEmploymentID = "uniquekey"(@OldCandEmploymentID);
      set @NewCandEmploymentCompanyID = (select "CompanyID" from "Employment" where "EmploymentID" = @NewEmploymentID);
      insert into "Employment"( "EmploymentID","temp","personid","companyid","startdate","leavedate","noreemploy","note","salary","position","department","Concurrent","ExtendedNotes" ) 
        select @NewCandEmploymentID,"temp","personid",@NewCandEmploymentCompanyID,"startdate","leavedate","noreemploy","note","salary","position","department","Concurrent","ExtendedNotes"
          from "Employment" where "EmploymentID" = @OldCandEmploymentID;
      -- Placement
      insert into "placement"( "placementid","placedate","employmentid","vacancyid","departmentid","staffid","temp","clientrate","temprate","note","State","RefCode","TheirRef","ContractRef","DaysPerWeek",
        "TransferBatch","Currency","TempJobTypeID","ExtendedNotes","ExpenseBenefitSchemeID","WorkMonday","WorkTuesday","WorkWednesday","WorkThursday","WorkFriday","WorkSaturday","WorkSunday","WorkNormalHours","WorkStartTime","documenttemplateid" ) 
        select @NewPlacementID,"placedate",@NewCandEmploymentID,@NewVacancyID,"departmentid","staffid","temp","clientrate","temprate","note","State","RefCode","TheirRef","ContractRef","DaysPerWeek",
          "TransferBatch","Currency","TempJobTypeID","ExtendedNotes","ExpenseBenefitSchemeID","WorkMonday","WorkTuesday","WorkWednesday","WorkThursday","WorkFriday","WorkSaturday","WorkSunday","WorkNormalHours","WorkStartTime","documenttemplateid"
          from "Placement" where "PlacementID" = @OldPlacementID;
      -- Placement Rates
      insert into "TempJobRate"( "TempJobRateID","PlacementID","TempPayBandID","PayRate","ChargeRate","StartDate","EndDate","Grade" ) 
        select(select "uniquekey"("TempJobRateID")),@NewPlacementID,"TempPayBandID","PayRate","ChargeRate","StartDate","EndDate","Grade"
          from "TempJobRate" where "PlacementID" = @OldPlacementID;
      -- Placement Elements
      insert into "PlacementElement"( "PlacementElementTypeID","PlacementID","Value","Volatile" ) 
        select "UniqueKey"("PlacementElementTypeID"),"PlacementID","Value","Volatile"
          from "PlacementElement" where "PlacementID" = @OldPlacementID;
      -- Descriptive Text
      set @ExplainStringFrom = (select "string"("Company"."Name",' - ',"Person"."Name",' - ',"Vacancy"."RefCode") from "Vacancy" key join "Employment" key join("Person","Company") where "Vacancy"."VacancyID" = @OldVacancyID);
      set @ExplainStringTo = (select "string"("Company"."Name",' - ',"Person"."Name",' - ',"Vacancy"."RefCode") from "Vacancy" key join "Employment" key join("Person","Company") where "Vacancy"."VacancyID" = @NewVacancyID);
      set @MovedPerson = (select "Person"."Name" from "Person" key join "Employment" key join "Placement" where "PlacementID" = @OldPlacementID);
      -- Close off old Placement
      if @CloseOffOld = 'Y' then
        update "Employment" set "LeaveDate" = "StartDate","ExtendedNotes" = "trim"("string"('Employment cancelled, transferred to ',@ExplainStringTo,"char"(13),"char"(13),"ExtendedNotes"))
          where "EmploymentID" = @OldCandEmploymentID;
        update "Placement" set "ExtendedNotes" = "trim"("string"('Placement cancelled, transferred to ',@ExplainStringTo,"char"(13),"char"(13),"ExtendedNotes"))
          where "PlacementID" = @OldPlacementID
      end if;
      -- Add explanatory text to new Employment & Placement
      update "Employment" set "ExtendedNotes" = "trim"("string"('Employment transfered from ',@ExplainStringFrom,"char"(13),"char"(13),"ExtendedNotes"))
        where "EmploymentID" = @NewCandEmploymentID;
      update "Placement" set "ExtendedNotes" = "trim"("string"('Placement transfered from ',@ExplainStringFrom,"char"(13),"char"(13),"ExtendedNotes"))
        where "PlacementID" = @NewPlacementID;
      -- Update Audit Trail
      call "AuditLog"('VACANCY',@OldVacancyID,'Placement Cancelled and moved',"string"(@ExplainStringFrom,' - ',@MovedPerson),@ExplainStringTo);
      call "AuditLog"('VACANCY',@NewVacancyID,'Copied Vacancy',@ExplainStringFrom,@ExplainStringTo);
      call "AuditLog"('PLACEMENT',@OldPlacementID,'Placement Cancelled and moved',@ExplainStringFrom,@ExplainStringTo);
      call "AuditLog"('PLACEMENT',@NewPlacementID,'Moved Placement',@ExplainStringFrom,@ExplainStringTo)
    // end of placement section
    end if
  end; // end of atomic statment
  return(0)
exception
  when others then return(2)
end
</code>