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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."DuplicateVacancyAndPlacement" IS 
{create function 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 if
  // end of placement section
  end; // end of atomic statment
  return(0)
exception
  when others then return(2)
end
}
</code>