====== pears.TempTimesheetCancel ======
<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"."TempTimesheetCancel" IS 
{create function TempTimesheetCancel 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

(in tscancelid char(20),in tsid char(20),in shiftcancreasonid char(1),in irecreate smallint,in @CorrectionType char(20) default 'BOTH')
returns char(20)
begin atomic
  declare newid char(20);
  declare pid char(20);
  declare stdate date;
  declare contrasuffix char(10);
  declare replacesuffix char(10);
  -- Temporary table to link the old shifts to the new
  declare local temporary table localshifts(
    oldshiftid char(20) null,
    oldtemplateid char(20) null,
    oldstaffid char(20) null,
    newshiftid char(20) null,
    ) on commit delete rows;if trim(shiftcancreasonid) = '' then
    set shiftcancreasonid=null
  end if; -- Avoid ref integ. problems
  select isnull(WPKMaintainGetSwitchValue('TSCONTRASUFFIX',null,'L'),'(C)') into contrasuffix;
  select isnull(WPKMaintainGetSwitchValue('TSREPLACESUFFIX',null,'L'),'(A)') into replacesuffix;
  set newid=uniquekey(tsid);
  -- Temporary table to link the old shifts to the new
  update temptimesheet set timesheetcancelreasonid = if tscancelid > '' then tscancelid else null endif where temptimesheetid = tsid;
  -- newid is the id for both the contra timesheet and the replacement prov timesheet
  -- First create the contra timesheet (serialnumber suffixed with (C) or contrasuffix)
  insert into temptimesheet( temptimesheetid,placementid,serialnumber,period,periodlength,payrollidentifier,analysiscode,correctionflag,personid,tempdeskid,payrollnumber,theirref,description,
    currency,startdate,enddate,secondaryagencyid,staffid,taxmethod,holidaypaystatus,TSBarcode,NotTaxable,billed,paid,AWRWasInvolved,timesheetcancelreasonid, 
    invoiceprefix, invoiceemail, invoiceaddress ) 
    select newid,placementid,string(serialnumber,contrasuffix),period,periodlength,payrollidentifier,analysiscode,1,personid,tempdeskid,payrollnumber,theirref,string('CANCELLED-',description),
      currency,startdate,enddate,secondaryagencyid,userstaffid,taxmethod,holidaypaystatus,TSBarcode,NotTaxable,
      if (@CorrectionType = 'PAY'  or @CorrectionType = 'INV') then 1  else 0 endif, if @CorrectionType = 'CHARGE' then 1 else if @CorrectionType = 'INV' then paid else 0 endif endif,
      AWRWasInvolved,timesheetcancelreasonid,invoiceprefix, invoiceemail, invoiceaddress from temptimesheet where temptimesheetid = tsid;
  insert into temptimesheetline( TempTimeSheetLineID,TempTimeSheetID,TempPayBandID,LineNumber,UnitsPaid,UnitsCharged,PayRate,ChargeRate,Description,TempShiftID,ShiftTime,Grade,AWRJobmasterID) 
    select uniquekey(TempTimeSheetLineID),newid,TempPayBandID,LineNumber,0-UnitsPaid,0-UnitsCharged,PayRate,ChargeRate,Description,TempShiftID,ShiftTime,Grade,AWRJobmasterID from
      temptimesheetline where temptimesheetid = tsid;
  -- create pay_emp
  select personid,startdate into pid,stdate from temptimesheet where temptimesheetid = newid;
  call pay_checkemployment(pid,stdate);
  -- update ts
  update temptimesheet set holidaypaystatus = tempholidaycalc(p.personid,truncnum(period/100,0),remainder(period,100),-1),
    taxmethod = e.taxmethod, pay_employmentid = (select first pay_employmentid from pay_employment where enddate is null and personid = temptimesheet.personid),
    payrollnumber = p.payrollnumber from
    temptimesheet key join person as p key join pay_employee as e where temptimesheetid = newid;
  if exists(select * from person where personid = pid and isnull(payrollnumber,'') = '') then
    update temptimesheet set payrollnumber = 'NEW' where temptimesheetid = newid;
    update person set payrollnumber = 'NEW' where personid = pid
  end if;
  if (select first payrollcoidfromtemp from params) = 1 then
    update temptimesheet set payrollidentifier = (select first payrollidentifier from person where personid = pid) where temptimesheetid = newid;
  end if;
  -- call cancel
  if irecreate = 1 then
    -- Populate the local temp table
    insert into localshifts( oldshiftid,oldstaffid,newshiftid,oldtemplateid) select tempshiftid,staffid,uniquekey(tempshiftid),tempshifttemplateid from tempshift where temptimesheetid = tsid;
    -- Create the replacement shifts. Only tricky thing is cancelled shifts which were billed or paid. We attempt to recreate CBill and CPay values from the cancel reason
    insert into tempshift( Staffid,TempShiftID,VacancyID,PersonID,PlacementID,ShiftDate,TimeFrom,TimeTo,State,Note,TempShiftPlanID,BreakMinutes,EssentialSkillGradeID,CancelReason,
      WhenCancelled,CBill,CPay,CRefill,ReferenceCode,ClientConfirmed,TempConfirmed,ConfirmedWith,TempShiftTypeID,AnalysisCode,WhoCancelled,RecoveryHours,tempshifttemplateid) 
      select oldstaffid,l.newshiftid,VacancyID,PersonID,PlacementID,ShiftDate,TimeFrom,TimeTo,(if s.State = 'W' then 'B' else s.State
        endif),Note,TempShiftPlanID,BreakMinutes,EssentialSkillGradeID,CancelReason,
        WhenCancelled,c.Bill,c.Pay,CRefill,ReferenceCode,1,1,ConfirmedWith,TempShiftTypeID,AnalysisCode,WhoCancelled,RecoveryHours, oldtemplateid from
        localshifts as l join tempshift as s on l.oldshiftid = s.tempshiftid key left outer join tempshiftcancelreason as c;
    -- Create the replacement provisional (serialnumber suffixed with (A) or replacesuffix)
    insert into tempprovtimesheet( TempProvTimeSheetID,PlacementID,PersonID,VacancyID,TempDeskID,Period,PeriodLength,CorrectionFlag,Description,TheirRef,SerialNumber,ImageBlobID,ScanPathFilename,Orientation,ImageApproved) 
      select newid,placementid,personid,(select vacancyid from placement where placementid = temptimesheet.placementid),tempdeskid,period,periodlength,1,string('AMENDED-',description),
        theirref,string(serialnumber,replacesuffix),ImageBlobID,ScanPathFilename,Orientation,ImageApproved from temptimesheet where temptimesheetid = tsid;
    insert into tempprovtimesheetline( TempProvTimeSheetLineID,TempProvTimeSheetID,TempPayBandID,LineNumber,UnitsPaid,UnitsCharged,PayRate,ChargeRate,Description,tempshiftid,
      ShiftTime,Grade) 
      select uniquekey(TempTimeSheetLineID),newid,TempPayBandID,LineNumber,UnitsPaid,UnitsCharged,PayRate,ChargeRate,Description,
        (select first newshiftid from localshifts where oldshiftid = temptimesheetline.tempshiftid),
        ShiftTime,Grade from
        temptimesheetline where temptimesheetid = tsid;
    -- Link the new shifts to it
    insert into tempprovtimesheetshift( tempprovtimesheetid,tempshiftid) select newid,newshiftid from localshifts
  end if;
  -- Cancel the old shifts (unless already cancelled), using the designated cancel reason
  update tempshift set state = 'C',cancelreason = shiftcancreasonid,whencancelled = current timestamp,whocancelled = (select first staffid from temptimesheet where temptimesheetid = newid) where
    temptimesheetid = tsid and state <> 'C';
  --Record the cancellation in the TempTimesheetCancel table
  insert into TempTimesheetCancellation( TempTimeSheetCancellationID,OriginalTempTimesheetID,PayCorrected,ChargeCorrected,ReplacementProvTimesheetSerialNumber,ContraTempTimeSheetID,WhoCancelled,TimesheetCancelReasonID) values( 
    uniquekey(tsid),tsid,if @CorrectionType in( 'BOTH','PAY') then 1 else 0 endif,if @CorrectionType in( 'BOTH','CHARGE') then 1 else 0 endif,
    if irecreate = 1 then(select string(serialnumber,replacesuffix) from temptimesheet where temptimesheetid = tsid) else null endif,newid,UserStaffID, if tscancelid > '' then tscancelid else null endif) ;
  -- If expense benefit in operation, cancel out any EB timesheets or equiv. Actual content of procedure will vary depending on payroll system etc
  if(select ExpenseBenefit from TempDesk key join Vacancy key join Placement key join TempTimeSheet where TempTimeSheetID = tsid) = 1 then
    call CancelExpenseBenefitTimesheet(tsid,newid)
  end if;
  call temptimesheetcomplete(newid);
  -- Return the ID of the contra and replacement prov timesheets
  return newid
end
}
</code>