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