====== pears.NetOwnerTimesheetCreate ======
<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 procedure "pears"."NetOwnerTimesheetCreate"( in "pWebUserID" char(20),in "pStartDate" char(25),in "pLineData" long varchar ) 
result( "AcName" char(60),"SiteAddr" char(150),"JobTitle" char(60),"TempName" char(60),"PayBand" char(100),"Units" double,"TimesheetNumber" char(20) ) 
begin
  declare "provtsid" char(20);
  declare "persid" char(20);
  declare "vacid" char(20);
  declare "rateid" char(20);
  declare "deskid" char(20);
  declare "divid" char(20);
  declare "perd" integer;
  declare "lineid" char(20);
  declare "bandid" char(20);
  declare "lineno" integer;
  declare "nUnits" double;
  declare "nPayrate" double;
  declare "nChargerate" double;
  declare "chained" char(3);
  declare "checkval" char(50);
  declare "startdate" date;
  declare "errmsg" char(60);
  declare "thisline" char(150);
  declare "i" integer;
  declare "cnt" integer;
  declare "cr" char(1);
  declare "tab" char(1);
  declare local temporary table "IDs"(
    "ID" char(20) null,
    "RowCnt" integer null,
    "Msg" char(50) null,
    ) not transactional;
  set "chained" = (select "connection_property"('chained'));
  set "errmsg" = null;
  set "cr" = "char"(13);
  set "tab" = "char"(9);
  set "checkval" = '.';
  set "startdate" = "iqxnetstringtodate"("pStartDate");
  if "startdate" is null then
    set "errmsg" = 'Invalid start date'
  else
    if "abs"("datediff"("week",current date,"startdate")) > 5 then
      set "errmsg" = 'Start date out of range'
    else
      set "perd" = "weekcontaining"("startdate");
      if "perd" is null then
        set "errmsg" = 'Payroll week out of range'
      end if end if end if;
  set "divid" = (select first "staff"."divisionid" from "iqxnetuser" key join "staff" where "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  if "divid" is null then
    set "errmsg" = 'No staff division set'
  end if;
  set "deskid" = (select first "staff"."tempdeskid" from "iqxnetuser" key join "staff" where "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  if "deskid" is null then
    set "errmsg" = 'No staff temp desk set'
  end if;
  if "errmsg" is not null then
    select "errmsg",null,null,null,null,null;
    return
  end if;
  set "Cnt" = 0;
  "outerloop": loop
    set "i" = "charindex"("cr","plinedata");
    if "i" = 0 then
      leave "outerloop"
    end if;
    set "thisline" = "left"("plinedata","i"-1);
    set "plinedata" = "stuff"("plinedata",1,"i",null);
    set "errmsg" = null;
    set "Cnt" = "Cnt"+1;
    set "LineID" = "uniquekey"("Cnt");
    set "i" = "charindex"("tab","thisline");
    if "i" = 0 then
      set "vacid" = null
    else
      set "vacid" = "left"("thisline","i"-1);
      set "thisline" = "stuff"("thisline",1,"i",null);
      set "vacid" = (select first "vacancy"."vacancyid" from "vacancy" key join "employment" key join "company" where "vacancy"."vacancyid" = "vacid" and "company"."divisionid" = "divid");
      if "vacid" is null then
        set "errmsg" = 'Invalid vacancy ID'
      end if end if;
    set "i" = "charindex"("tab","thisline");
    if "i" = 0 then
      set "persid" = null
    else
      set "persid" = "left"("thisline","i"-1);
      set "thisline" = "stuff"("thisline",1,"i",null);
      set "persid" = (select first "personid" from "person" where "personid" = "persid" and "divisionid" = "divid");
      if "persid" is null then
        set "errmsg" = "isnull"("errmsg",'Invalid person ID')
      end if end if;
    set "i" = "charindex"("tab","thisline");
    if "i" = 0 then
      set "rateid" = null
    else
      set "rateid" = "left"("thisline","i"-1);
      set "thisline" = "stuff"("thisline",1,"i",null);
      set "bandid" = null;
      select "temppaybandid","payrate","chargerate" into "bandid","npayrate","nchargerate" from "tempjobrate" where "tempjobrateid" = "rateid" and "vacancyid" = "vacid";
      if "bandid" is null then
        set "errmsg" = "isnull"("errmsg",'Invalid rate ID')
      end if end if;
    begin
      set "nunits" = cast("trim"("thisline") as double)
    exception
      when others then set "errmsg" = "isnull"("errmsg",'Invalid unit value')
    end;
    insert into "IDs"( "ID","RowCnt","msg" ) values( "LineID","Cnt","errmsg" ) ;
    if "errmsg" is null then
      if "vacid"+"persid" <> "checkval" then
        set "lineno" = 0;
        set "provtsid" = "uniquekey"("checkval");
        set "checkval" = "vacid"+"persid";
        insert into "TempProvTimeSheet"( "TempProvTimeSheetID","PersonID","VacancyID","TempDeskID","Period","PeriodLength","ExtNumber","TheirRef" ) values
          ( "provtsid","persid","vacid","deskid","perd",'W',2,null ) ;
        if "chained" = 'On' then
          begin atomic
            call "NetNumberProvTimesheet"("provtsid")
          end
        else call "NetNumberProvTimesheet"("provtsid")
        end if end if;
      set "lineno" = "lineno"+1;
      insert into "TempProvTimesheetLine"( "TempProvTimeSheetLineID","TempProvTimeSheetID","TempPayBandID","LineNumber","UnitsPaid","UnitsCharged","PayRate","ChargeRate" ) values
        ( "lineid","provtsid","bandid","lineno","nUnits","nUnits","nPayrate","nChargeRate" ) 
    end if
  end loop "outerloop";
  select "isnull"((select "name" from "company" where "companyid" = "getinvoicecompanyid"("co"."clientcode")),"ids"."msg") as "acname","string"("co"."name",', ',"co"."addr1") as "siteaddr","vacancy"."position","person"."name",
    "string"((select "Description" from "temppayband" where "temppaybandid" = "l"."temppaybandid"),' ',"trim"("str"("l"."payrate",12,2)),'/',"trim"("str"("l"."chargerate",12,2))) as "payband","l"."unitspaid","t"."serialnumber"
    from "ids" left outer join "tempprovtimesheetline" as "l" on "ids"."id" = "l"."tempprovtimesheetlineid" key join "tempprovtimesheet" as "t" key join("person",("vacancy" key join "employment" key join "company" as "co"))
    order by "ids"."rowcnt" asc
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerTimesheetCreate" IS 
{create procedure pears."NetOwnerTimesheetCreate"( in "pWebUserID" char(20),in "pStartDate" char(25),in "pLineData" long varchar ) 
result( "AcName" char(60),"SiteAddr" char(150),"JobTitle" char(60),"TempName" char(60),"PayBand" char(100),"Units" double,"TimesheetNumber" char(20) ) 
begin
  declare "provtsid" char(20);
  declare "persid" char(20);
  declare "vacid" char(20);
  declare "rateid" char(20);
  declare "deskid" char(20);
  declare "divid" char(20);
  declare "perd" integer;
  declare "lineid" char(20);
  declare "bandid" char(20);
  declare "lineno" integer;
  declare "nUnits" double;
  declare "nPayrate" double;
  declare "nChargerate" double;
  declare "chained" char(3);
  declare "checkval" char(50);
  declare "startdate" date;
  declare "errmsg" char(60);
  declare "thisline" char(150);
  declare "i" integer;
  declare "cnt" integer;
  declare "cr" char(1);
  declare "tab" char(1);
  declare local temporary table "IDs"(
    "ID" char(20) null,
    "RowCnt" integer null,
    "Msg" char(50) null,
    ) not transactional;
  set "chained" = (select "connection_property"('chained'));
  set "errmsg" = null;
  set "cr" = "char"(13);
  set "tab" = "char"(9);
  set "checkval" = '.';
  set "startdate" = "iqxnetstringtodate"("pStartDate");
  if "startdate" is null then
    set "errmsg" = 'Invalid start date'
  else
    if "abs"("datediff"("week",current date,"startdate")) > 5 then
      set "errmsg" = 'Start date out of range'
    else
      set "perd" = "weekcontaining"("startdate");
      if "perd" is null then
        set "errmsg" = 'Payroll week out of range'
      end if
    end if end if;
  set "divid" = (select first "staff"."divisionid" from "iqxnetuser" key join "staff" where "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  if "divid" is null then
    set "errmsg" = 'No staff division set'
  end if;
  set "deskid" = (select first "staff"."tempdeskid" from "iqxnetuser" key join "staff" where "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  if "deskid" is null then
    set "errmsg" = 'No staff temp desk set'
  end if;
  if "errmsg" is not null then
    select "errmsg",null,null,null,null,null;
    return
  end if;
  set "Cnt" = 0;
  "outerloop": loop
    set "i" = "charindex"("cr","plinedata");
    if "i" = 0 then
      leave "outerloop"
    end if;
    set "thisline" = "left"("plinedata","i"-1);
    set "plinedata" = "stuff"("plinedata",1,"i",null);
    set "errmsg" = null;
    set "Cnt" = "Cnt"+1;
    set "LineID" = "uniquekey"("Cnt");
    set "i" = "charindex"("tab","thisline");
    if "i" = 0 then
      set "vacid" = null
    else
      set "vacid" = "left"("thisline","i"-1);
      set "thisline" = "stuff"("thisline",1,"i",null);
      set "vacid" = (select first "vacancy"."vacancyid" from "vacancy" key join "employment" key join "company" where "vacancy"."vacancyid" = "vacid" and "company"."divisionid" = "divid");
      if "vacid" is null then
        set "errmsg" = 'Invalid vacancy ID'
      end if end if;
    set "i" = "charindex"("tab","thisline");
    if "i" = 0 then
      set "persid" = null
    else
      set "persid" = "left"("thisline","i"-1);
      set "thisline" = "stuff"("thisline",1,"i",null);
      set "persid" = (select first "personid" from "person" where "personid" = "persid" and "divisionid" = "divid");
      if "persid" is null then
        set "errmsg" = "isnull"("errmsg",'Invalid person ID')
      end if end if;
    set "i" = "charindex"("tab","thisline");
    if "i" = 0 then
      set "rateid" = null
    else
      set "rateid" = "left"("thisline","i"-1);
      set "thisline" = "stuff"("thisline",1,"i",null);
      set "bandid" = null;
      select "temppaybandid","payrate","chargerate" into "bandid","npayrate","nchargerate" from "tempjobrate" where "tempjobrateid" = "rateid" and "vacancyid" = "vacid";
      if "bandid" is null then
        set "errmsg" = "isnull"("errmsg",'Invalid rate ID')
      end if end if;
    begin
      set "nunits" = cast("trim"("thisline") as double)
    exception
      when others then set "errmsg" = "isnull"("errmsg",'Invalid unit value')
    end;
    insert into "IDs"( "ID","RowCnt","msg" ) values( "LineID","Cnt","errmsg" ) ;
    if "errmsg" is null then
      if "vacid"+"persid" <> "checkval" then
        set "lineno" = 0;
        set "provtsid" = "uniquekey"("checkval");
        set "checkval" = "vacid"+"persid";
        insert into "TempProvTimeSheet"( "TempProvTimeSheetID","PersonID","VacancyID","TempDeskID","Period","PeriodLength","ExtNumber","TheirRef" ) values
          ( "provtsid","persid","vacid","deskid","perd",'W',2,null ) ;
        if "chained" = 'On' then
          begin atomic
            call "NetNumberProvTimesheet"("provtsid")
          end
        else call "NetNumberProvTimesheet"("provtsid")
        end if end if;
      set "lineno" = "lineno"+1;
      insert into "TempProvTimesheetLine"( "TempProvTimeSheetLineID","TempProvTimeSheetID","TempPayBandID","LineNumber","UnitsPaid","UnitsCharged","PayRate","ChargeRate" ) values
        ( "lineid","provtsid","bandid","lineno","nUnits","nUnits","nPayrate","nChargeRate" ) 
    end if
  end loop "outerloop";
  select "isnull"((select "name" from "company" where "companyid" = "getinvoicecompanyid"("co"."clientcode")),"ids"."msg") as "acname","string"("co"."name",', ',"co"."addr1") as "siteaddr","vacancy"."position","person"."name",
    "string"((select "Description" from "temppayband" where "temppaybandid" = "l"."temppaybandid"),' ',"trim"("str"("l"."payrate",12,2)),'/',"trim"("str"("l"."chargerate",12,2))) as "payband","l"."unitspaid","t"."serialnumber"
    from "ids" left outer join "tempprovtimesheetline" as "l" on "ids"."id" = "l"."tempprovtimesheetlineid" key join "tempprovtimesheet" as "t" key join("person",("vacancy" key join "employment" key join "company" as "co"))
    order by "ids"."rowcnt" asc
end
}
</code>