====== pears.TempTimesheetComplete ======
<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"."TempTimesheetComplete"( in @TempTimeSheetID char(20) ) 
returns char(250)
begin
  declare "jobmasterid" char(20);
  declare "placeid" char(20);
  declare "persid" char(20);
  declare "vacid" char(20);
  declare @period integer;
  declare "maxdate" date;
  declare "mindate" date;
  declare "TSNote" long varchar;
  declare "sminrate" double;
  declare "paid" double;
  declare "mperiod" integer;
  declare "mpersid" char(20);
  select "placementid","period","personid","string"("serialnumber",' - ',"dateformat"("startdate",'dd/mm/yy'),' to ',"dateformat"("enddate",'dd/mm/yy')) into "placeid",@period,"persid","TSNote" from "temptimesheet" where "temptimesheetid" = @TempTimeSheetID;
  select "vacancyid" into "vacid" from "placement" key join "employment" where "placementid" = "placeid";
  -- grade, speciality
  update "awrjobmaster" set "placementid" = "placeid" where "personid" = "persid" and "vacancyid" = "vacid" and "placementid" is null;
  -- grade, speciality
  select first "awrjobmasterid" into "jobmasterid" from "awrjobmaster" where "placementid" = "placeID";
  -- if there are duplicates ( should not be ) they need to be linked
  if(select "sum"("unitspaid"*"payrate") from "temptimesheetline" where "temptimesheetid" = @temptimesheetid) > 0 then
    if "jobmasterid" is not null then
      -- use shift dates if avail
      select "min"("shiftdate"),"max"("shiftdate") into "mindate","maxdate" from "tempshift" where "temptimesheetid" = @temptimesheetid;
      call "AWRInsertWeekly"("isnull"("mindate","weekmonthenddate"(@period,'W')),"isnull"("maxdate","weekmonthenddate"(@Period,'W')),'','T','',"jobmasterid","TSNote",@TempTimeSheetID)
    end if
  else update "AWRWeeklyDetail" set "disregard" = 1 where "TempTimesheetID"
       = (select "OriginalTempTimesheetID" from "TempTimesheetCancellation" where "ContraTempTimesheetID" = @TempTimeSheetID)
  end if;
  set "paid" = 0;
  select "min"("temptimesheetline"."payrate"),"period","personid" into "paid","mperiod","mpersid"
    from "temptimesheet" key join "temptimesheetline" key join "temppayband" as "p"
    where "p"."unit" like 'hour%' and "temptimesheet"."temptimesheetid" = @TempTimeSheetid
    group by "period","personid";
  if("isnull"("paid",0) > 0) and exists(select * from "pay_employee" where "personid" = "mpersid" and "taxmethod" = 1) then
    select "GetMinWage"("mpersid","mperiod") into "sminrate";
    if("paid" < "sminrate") then
      return "string"('Temp not being paid the minimum wage of £',"str"("sminrate","length"("sminrate")+3,2),'.  Cancel the provisional timesheet, change the rates against the placement and then complete the timesheet. ')
    end if end if;
  return null
end
</code>