pears.TempTimesheetComplete [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.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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."TempTimesheetComplete" IS 
{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>