pears.AWRCheck [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.AWRCheck ======
<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"."AWRCheck"( in "BookOrConfirmorPlaceorTSflag" char(1),in "placeid" char(20),in @speciality char(50),in @grade char(4),in "shiftid" char(20),in "WeekNo" integer ) 
result( "DoNothingflag" char(1),"AskOtherAgencyWorkflag" char(1),"ShowAWRDetailsflag" char(1),"AWRJobMasterID" char(20),"UserMessage" long varchar,"LaterExpansionStuff" long varchar ) 
begin
  -- B: Shift booking 
  -- C: Shift client confirm
  -- T: shift temp confim
  -- S: At timesheet, when shift change of grade or speciality
  -- P: Placement
  -- L: At timesheet, when placement 
  declare "WeeksToWarn" integer;
  declare "WeeksToWarnTimesheet" integer;
  declare "WeeksToWarnBooking" integer;
  declare "DateToStartChecking" date;
  declare "newid" char(20);
  declare "persid" char(20);
  declare "vacid" char(20);
  declare "jobmid" char(20);
  declare "AWRStat" smallint;
  declare "JAWRStat" smallint;
  declare "UMess" char(2000);
  declare "AWRLink" char(20);
  declare "coid" char(20);
  declare "LE" char(50);
  -- this could be a question or jobtitle or something else
  declare "LinkingQuestion" char(50);
  set "AWRLink" = null; -- set variables here
  -- set variables here
  set "WeeksToWarnTimesheet" = 11;
  set "WeeksToWarnBooking" = 11;
  set "DateToStartChecking" = '2011-10-03';
  if "varexists"('AWRStartPeriod') = 1 then
    set "DateToStartChecking" = "weekmonthenddate"("AWRStartPeriod",'W')
  end if;
  -- set variables here
  -- set variables here
  -- AWRJobMaster always insert personid and vacancyid 
  --
  -- check start date
  if "datediff"("day",current date,"DateToStartChecking") >= 0 then
    select 'Y','Y','Y','','','';
    return
  end if;
  set "Umess" = '';
  if "BookOrConfirmorPlaceorTSflag" = 'B' then
    select "personid","vacancyid" into "persid","vacid" from "tempshift" where "tempshiftid" = "shiftid";
    if exists(select * from "person" where "personid" = "persid" and "isnull"("directlyemployed",0) >= 1) then
      select 'Y','Y','Y','','','';
      return
    end if;
    -- rules
    -- dont ask again if already asked for booked ( or worked ) shift in this week or untimesheeted = role with no place id
    if not exists(select * from "tempshift" where "personid" = "persid" and "vacancyid" = "vacid" and "tempshiftid" <> "shiftid" and "state" in( 'P','B','W' ) and "weekcontaining"("shiftdate") = "WeekNO") and not exists(select * from "AWRJobMaster" where "personid" = "persid" and "vacancyid" = "vacid" and "placementid" is null) then
      select "AWRStatus" into "AWRstat" from "AWRVacancy" where "vacancyid" = "vacid";
      select first "AWRStatus","AWRjobmasterid" into "JAWRstat","jobmid" from "AWRjobmaster" where "placementid" = "AWRPlacement"("vacid","persid","shiftid");
      -- speciality and grade
      if "JAWRstat" is null then
        set "JAWRstat" = 4
      else
        if "AWRWeeks"("jobmid","WeekNo",null,null,null,null) = "WeeksToWarnBooking" then
          set "Umess" = "string"('This temp will qualify in ',12-"WeeksToWarnBooking",' week(s) so the AWR details must be checked for Rates, Holidays, Linked Placements and other work.')
        end if end if;
      -- 1 not known 2 applies 3 not
      case "JAWRStat"
      when 1 then
        -- need to check work so far here
        if "AWRGap"("jobmid","WeekNo",null,null,null,null) >= 6 then
          select 'N','Y','Y',"jobmid",'',''
        else
          select 'N','N','Y',"jobmid","UMess",''
        end if when 2 then
        if "AWRGap"("jobmid","WeekNo",null,null,null,null) >= 6 then
          select 'N','Y','N',"jobmid",'',''
        else
          select 'N','N','N',"jobmid","UMess",''
        end if when 3 then
        select 'Y','Y','Y','','',''
      when 4 then
        -- no record check vac might have changed
        if "AWRstat" = 3 then
          select 'Y','Y','Y','','',''
        else
          set "newid" = "uniquekey"("shiftid");
          select "getquestanswer"("string"('V',"vacancy"."departmentid"),'AWR',"vacancy"."vacancyid"),"LECode","company"."companyid" into "LinkingQuestion","LE","coid" from "tempshift" key join "vacancy" key join "employment" key join "company" key join "AWRCompany" where "tempshiftid" = "shiftid";
          if "LE" is not null then
            select first "AWRJobMasterID" into "AWRLink" from "AWRJobMaster" key join "placement" key join("vacancy","employment" as "e")
              -- find "master" linked record in case some already linked
              where "AWRJobMasterID" = "AWRLinkCode"
              -- LE code check
              and exists(select "CompanyID" from "AWRCompany" where "LECode" = "LE" and "companyid" = "e"."companyid")
              -- job title or question check
              and "getquestanswer"("string"('V',"vacancy"."departmentid"),'AWR',"vacancy"."vacancyid") = "LinkingQuestion"
              and "LinkingQuestion" <> ''
              and "e"."personid" = "persid"
          else
            select first "AWRJobMasterID" into "AWRLink" from "AWRJobMaster" key join "placement" key join("vacancy","employment" as "e")
              -- find "master" linked record in case some already linked
              where "AWRJobMasterID" = "AWRLinkCode"
              -- LE code check
              and "e"."companyid" = "coid"
              -- job title or question check
              and "isnull"("e"."position","vacancy"."position") = "LinkingQuestion"
              and "LinkingQuestion" <> ''
              and "e"."personid" = "persid"
          end if;
          insert into "AWRJobmaster"( "awrjobmasterid","placementid","grade","speciality","vacancyid","personid","AWRLinkCode" ) values( "newid","AWRPlacement"("vacid","persid","shiftid"),@grade,@speciality,"vacid","persid","AWRLink" ) ;
          select 'N','Y','Y',"newid",'',''
        end if
      end case
    else select 'Y','Y','Y',"newid","string"('','   x'),''
    end if end if;
  if "BookOrConfirmorPlaceorTSflag" = 'C' then
    select 'Y','Y','Y','','',''
  end if; -- testing code  select 'N','Y','Y',(select first awrjobmasterid from awrjobmaster where vacancyid is not null and personid is not null),'You must ask about other agency work and must check the AWR details client confirm',''
  if "BookOrConfirmorPlaceorTSflag" = 'P' then
    select "employment"."personid","placement"."vacancyid" into "persid","vacid" from "placement" key join "employment" where "placementid" = "placeid";
    if exists(select * from "person" where "personid" = "persid" and "isnull"("directlyemployed",0) >= 1) then
      select 'Y','Y','Y','','','';
      return
    end if;
    select first "awrvacancy"."AWRStatus" into "AWRstat" from "AWRVacancy" join "placement" on "AWRVacancy"."vacancyid" = "placement"."vacancyid" where "placement"."placementid" = "placeid";
    if "AWRStat" is null then
      set "Umess" = 'No Vacancy AWR has been set up. You will be asked to fill in AWR details for the Placement, later you should also update the Vacancy.';
      set "AWRStat" = 1
    end if;
    if "AWRStat" in( 1,2 ) then
      select "getquestanswer"("string"('V',"vacancy"."departmentid"),'AWR',"vacancy"."vacancyid"),"LECode","company"."companyid" into "LinkingQuestion","LE","coid" from "placement" key join "employment" key join "company" key join "AWRCompany","placement" key join "vacancy" where "placement"."placementid" = "placeid";
      if "LE" is not null then
        select first "AWRJobMasterID" into "AWRLink" from "AWRJobMaster" key join "placement" key join("vacancy","employment" as "e")
          -- find "master" linked record in case some already linked
          where "AWRJobMasterID" = "AWRLinkCode"
          -- LE code check
          and exists(select "CompanyID" from "AWRCompany" where "LECode" = "LE" and "companyid" = "e"."companyid")
          -- job title or question check
          and "getquestanswer"("string"('V',"vacancy"."departmentid"),'AWR',"vacancy"."vacancyid") = "LinkingQuestion"
          and "LinkingQuestion" <> ''
          and "e"."personid" = "persid"
      else
        select first "AWRJobMasterID" into "AWRLink" from "AWRJobMaster" key join "placement" key join("vacancy","employment" as "e")
          -- find "master" linked record in case some already linked
          where "AWRJobMasterID" = "AWRLinkCode"
          -- LE code check
          and "e"."companyid" = "coid"
          -- job title or question check
          and "isnull"("e"."position","vacancy"."position") = "LinkingQuestion"
          and "LinkingQuestion" <> ''
          and "e"."personid" = "persid"
      end if end if;
    case "AWRStat"
    when 1 then
      set "newid" = "uniquekey"("placeid");
      insert into "AWRJobmaster"( "awrjobmasterid","placementid","grade","speciality","vacancyid","personid","AWRLinkCode" ) values( "newid","placeid",@grade,@speciality,"vacid","persid","AWRLink" ) ;
      select 'N','Y','Y',"newid","UMess",''
    when 2 then
      set "newid" = "uniquekey"("placeid");
      insert into "AWRJobmaster"( "awrjobmasterid","placementid","grade","speciality","vacancyid","personid","AWRLinkCode" ) values( "newid","placeid",@grade,@speciality,"vacid","persid","AWRLink" ) ;
      select 'N','Y','N',"newid",'',''
    when 3 then
      select 'Y','Y','Y','','',''
    end case
  end if;
  if "BookOrConfirmorPlaceorTSflag" = 'T' then
    select 'Y','Y','Y','','',''
  end if; -- testing code    select 'N','Y','Y',(select first awrjobmasterid from awrjobmaster where vacancyid is not null and personid is not null),'You must ask about other agency work and must check the AWR details temp confirm',''
  if "BookOrConfirmorPlaceorTSflag" = 'S' then
    select 'Y','Y','Y','','',''
  end if; -- testing code    select 'N','N','Y',(select first awrjobmasterid from awrjobmaster where vacancyid is not null and personid is not null),'You must ask about other agency work and must check the AWR details shift timesheet',''
  if "BookOrConfirmorPlaceorTSflag" = 'L' then
    if "placeid" is null then
      select 'N','N','N',null,"string"('For AWR compatability if this timesheet relates to a vacancy you must create a placement before entering a timesheet.',"char"(13),"char"(10),' Go back, create the placement and try again.'),'';
      return
    end if;
    select "employment"."personid","placement"."vacancyid" into "persid","vacid" from "placement" key join "employment" where "placement"."placementid" = "placeid";
    if exists(select * from "person" where "personid" = "persid" and "isnull"("directlyemployed",0) >= 1) then
      select 'Y','Y','Y','','','';
      return
    end if;
    select first "AWRVacancy"."AWRStatus" into "AWRstat" from "AWRVacancy" join "placement" on "AWRVacancy"."vacancyid" = "placement"."vacancyid" where "placement"."placementid" = "placeid";
    select first "AWRStatus","AWRjobmasterid" into "JAWRstat","jobmid" from "AWRjobmaster" where "placementid" = "placeid";
    -- speciality and grade
    if "JAWRstat" is null then
      set "JAWRstat" = 4
    else
      if "AWRWeeks"("jobmid","WeekNo",null,null,null,null) = "WeeksToWarnTimesheet" then
        set "Umess" = "string"('This temp will qualify in ',12-"WeeksToWarnTimesheet",' week(s). Check the AWR Details for what needs to be done. Check for placements that should be linked or for work for other Agencies.')
      end if end if;
    -- 1 not known 2 applies 3 not
    case "JAWRStat"
    when 1 then
      -- need to check work so far here
      if "AWRGap"("jobmid","WeekNo",null,null,null,null) >= 6 then
        select 'N','Y','Y',"jobmid",'',''
      else
        select 'N','N','Y',"jobmid","UMess",''
      end if when 2 then
      if "AWRGap"("jobmid","WeekNo",null,null,null,null) >= 6 then
        select 'N','Y','N',"jobmid",'',''
      else
        select 'N','N','N',"jobmid","UMess",''
      end if when 3 then
      select 'Y','Y','Y','','',''
    when 4 then
      -- no record check vac might have changed
      if "AWRstat" = 3 then
        select 'Y','Y','Y','','',''
      else
        set "newid" = "uniquekey"("placeid");
        select "getquestanswer"("string"('V',"vacancy"."departmentid"),'AWR',"vacancy"."vacancyid"),"LECode","company"."companyid" into "LinkingQuestion","LE","coid" from "placement" key join "employment" key join "company" key join "AWRCompany","placement" key join "vacancy" where "placement"."placementid" = "placeid";
        if "LE" is not null then
          select first "AWRJobMasterID" into "AWRLink" from "AWRJobMaster" key join "placement" key join("vacancy","employment" as "e")
            -- find "master" linked record in case some already linked
            where "AWRJobMasterID" = "AWRLinkCode"
            -- LE code check
            and exists(select "CompanyID" from "AWRCompany" where "LECode" = "LE" and "companyid" = "e"."companyid")
            -- job title or question check
            and "isnull"("e"."position","vacancy"."position") = "LinkingQuestion"
            and "LinkingQuestion" <> ''
            and "e"."personid" = "persid"
        else
          select first "AWRJobMasterID" into "AWRLink" from "AWRJobMaster" key join "placement" key join("vacancy","employment" as "e")
            -- find "master" linked record in case some already linked
            where "AWRJobMasterID" = "AWRLinkCode"
            -- LE code check
            and "e"."companyid" = "coid"
            -- job title or question check
            and "getquestanswer"("string"('V',"vacancy"."departmentid"),'AWR',"vacancy"."vacancyid") = "LinkingQuestion"
            and "LinkingQuestion" <> ''
            and "e"."personid" = "persid"
        end if;
        insert into "AWRJobmaster"( "awrjobmasterid","placementid","grade","speciality","vacancyid","personid","AWRLinkCode" ) values( "newid","placeid",@grade,@speciality,"vacid","persid","AWRLink" ) ;
        select 'N','Y','Y',"newid",'',''
      end if
    end case
  end if
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."AWRCheck" IS 
{create procedure pears."AWRCheck"( in "BookOrConfirmorPlaceorTSflag" char(1),in "placeid" char(20),in @speciality char(50),in @grade char(4),in "shiftid" char(20),in "WeekNo" integer )
result(DoNothingflag char(1),AskOtherAgencyWorkflag char(1),ShowAWRDetailsflag char(1),AWRJobMasterID char(20),UserMessage long varchar,LaterExpansionStuff long varchar) 
begin
  -- B: Shift booking 
  -- C: Shift client confirm
  -- T: shift temp confim
  -- S: At timesheet, when shift change of grade or speciality
  -- P: Placement
  -- L: At timesheet, when placement 
  declare "WeeksToWarn" integer;
  declare "WeeksToWarnTimesheet" integer;
  declare "WeeksToWarnBooking" integer;
  declare "DateToStartChecking" date;
  declare "newid" char(20);
  declare "persid" char(20);
  declare "vacid" char(20);
  declare "jobmid" char(20);
  declare "AWRStat" smallint;
  declare "JAWRStat" smallint;
  declare "UMess" char(2000);
  declare "AWRLink" char(20);
  declare "coid" char(20);
  declare "LE" char(50);
  -- this could be a question or jobtitle or something else
  declare "LinkingQuestion" char(50);
  set "AWRLink" = null; -- set variables here
  -- set variables here
  set "WeeksToWarnTimesheet" = 11;
  set "WeeksToWarnBooking" = 11;
  set "DateToStartChecking" = '2011-10-03';
  if "varexists"('AWRStartPeriod') = 1 then
    set "DateToStartChecking" = "weekmonthenddate"("AWRStartPeriod",'W')
  end if;
  -- set variables here
  -- set variables here
  -- AWRJobMaster always insert personid and vacancyid 
  --
  -- check start date
  if "datediff"("day",current date,"DateToStartChecking") >= 0 then
    select 'Y','Y','Y','','','';
    return
  end if;
  set "Umess" = '';
  if "BookOrConfirmorPlaceorTSflag" = 'B' then
    select "personid","vacancyid" into "persid","vacid" from "tempshift" where "tempshiftid" = "shiftid";
    if exists(select * from "person" where "personid" = "persid" and "isnull"("directlyemployed",0) >= 1) then
      select 'Y','Y','Y','','','';
      return
    end if;
    -- rules
    -- dont ask again if already asked for booked ( or worked ) shift in this week or untimesheeted = role with no place id
    if not exists(select * from "tempshift" where "personid" = "persid" and "vacancyid" = "vacid" and "tempshiftid" <> "shiftid" and "state" in( 'P','B','W' ) and "weekcontaining"("shiftdate") = "WeekNO") and not exists(select * from "AWRJobMaster" where "personid" = "persid" and "vacancyid" = "vacid" and "placementid" is null) then
      select "AWRStatus" into "AWRstat" from "AWRVacancy" where "vacancyid" = "vacid";
      select first "AWRStatus","AWRjobmasterid" into "JAWRstat","jobmid" from "AWRjobmaster" where "placementid" = "AWRPlacement"("vacid","persid","shiftid");
      -- speciality and grade
      if "JAWRstat" is null then
        set "JAWRstat" = 4
      else
        if "AWRWeeks"("jobmid","WeekNo",null,null,null,null) = "WeeksToWarnBooking" then
          set "Umess" = "string"('This temp will qualify in ',12-"WeeksToWarnBooking",' week(s) so the AWR details must be checked for Rates, Holidays, Linked Placements and other work.')
        end if end if;
      -- 1 not known 2 applies 3 not
      case "JAWRStat"
      when 1 then
        -- need to check work so far here
        if "AWRGap"("jobmid","WeekNo",null,null,null,null) >= 6 then
          select 'N','Y','Y',"jobmid",'',''
        else
          select 'N','N','Y',"jobmid","UMess",''
        end if when 2 then
        if "AWRGap"("jobmid","WeekNo",null,null,null,null) >= 6 then
          select 'N','Y','N',"jobmid",'',''
        else
          select 'N','N','N',"jobmid","UMess",''
        end if when 3 then
        select 'Y','Y','Y','','',''
      when 4 then
        -- no record check vac might have changed
        if "AWRstat" = 3 then
          select 'Y','Y','Y','','',''
        else
          set "newid" = "uniquekey"("shiftid");
          select "getquestanswer"("string"('V',"vacancy"."departmentid"),'AWR',"vacancy"."vacancyid"),"LECode","company"."companyid" into "LinkingQuestion","LE","coid" from "tempshift" key join "vacancy" key join "employment" key join "company" key join "AWRCompany" where "tempshiftid" = "shiftid";
          if "LE" is not null then
            select first "AWRJobMasterID" into "AWRLink" from "AWRJobMaster" key join "placement" key join("vacancy","employment" as "e")
              -- find "master" linked record in case some already linked
              where "AWRJobMasterID" = "AWRLinkCode"
              -- LE code check
              and exists(select "CompanyID" from "AWRCompany" where "LECode" = "LE" and "companyid" = "e"."companyid")
              -- job title or question check
              and "getquestanswer"("string"('V',"vacancy"."departmentid"),'AWR',"vacancy"."vacancyid") = "LinkingQuestion"
              and "LinkingQuestion" <> ''
              and "e"."personid" = "persid"
          else
            select first "AWRJobMasterID" into "AWRLink" from "AWRJobMaster" key join "placement" key join("vacancy","employment" as "e")
              -- find "master" linked record in case some already linked
              where "AWRJobMasterID" = "AWRLinkCode"
              -- LE code check
              and "e"."companyid" = "coid"
              -- job title or question check
              and "isnull"("e"."position","vacancy"."position") = "LinkingQuestion"
              and "LinkingQuestion" <> ''
              and "e"."personid" = "persid"
          end if;
          insert into "AWRJobmaster"( "awrjobmasterid","placementid","grade","speciality","vacancyid","personid","AWRLinkCode" ) values( "newid","AWRPlacement"("vacid","persid","shiftid"),@grade,@speciality,"vacid","persid","AWRLink" ) ;
          select 'N','Y','Y',"newid",'',''
        end if
      end case
    else select 'Y','Y','Y',"newid","string"('','   x'),''
    end if end if;
  if "BookOrConfirmorPlaceorTSflag" = 'C' then
    select 'Y','Y','Y','','',''
  end if; -- testing code  select 'N','Y','Y',(select first awrjobmasterid from awrjobmaster where vacancyid is not null and personid is not null),'You must ask about other agency work and must check the AWR details client confirm',''
  if "BookOrConfirmorPlaceorTSflag" = 'P' then
    select "employment"."personid","placement"."vacancyid" into "persid","vacid" from "placement" key join "employment" where "placementid" = "placeid";
    if exists(select * from "person" where "personid" = "persid" and "isnull"("directlyemployed",0) >= 1) then
      select 'Y','Y','Y','','','';
      return
    end if;
    select first "awrvacancy"."AWRStatus" into "AWRstat" from "AWRVacancy" join "placement" on "AWRVacancy"."vacancyid" = "placement"."vacancyid" where "placement"."placementid" = "placeid";
    if "AWRStat" is null then
      set "Umess" = 'No Vacancy AWR has been set up. You will be asked to fill in AWR details for the Placement, later you should also update the Vacancy.';
      set "AWRStat" = 1
    end if;
    if "AWRStat" in( 1,2 ) then
      select "getquestanswer"("string"('V',"vacancy"."departmentid"),'AWR',"vacancy"."vacancyid"),"LECode","company"."companyid" into "LinkingQuestion","LE","coid" from "placement" key join "employment" key join "company" key join "AWRCompany","placement" key join "vacancy" where "placement"."placementid" = "placeid";
      if "LE" is not null then
        select first "AWRJobMasterID" into "AWRLink" from "AWRJobMaster" key join "placement" key join("vacancy","employment" as "e")
          -- find "master" linked record in case some already linked
          where "AWRJobMasterID" = "AWRLinkCode"
          -- LE code check
          and exists(select "CompanyID" from "AWRCompany" where "LECode" = "LE" and "companyid" = "e"."companyid")
          -- job title or question check
          and "getquestanswer"("string"('V',"vacancy"."departmentid"),'AWR',"vacancy"."vacancyid") = "LinkingQuestion"
          and "LinkingQuestion" <> ''
          and "e"."personid" = "persid"
      else
        select first "AWRJobMasterID" into "AWRLink" from "AWRJobMaster" key join "placement" key join("vacancy","employment" as "e")
          -- find "master" linked record in case some already linked
          where "AWRJobMasterID" = "AWRLinkCode"
          -- LE code check
          and "e"."companyid" = "coid"
          -- job title or question check
          and "isnull"("e"."position","vacancy"."position") = "LinkingQuestion"
          and "LinkingQuestion" <> ''
          and "e"."personid" = "persid"
      end if end if;
    case "AWRStat"
    when 1 then
      set "newid" = "uniquekey"("placeid");
      insert into "AWRJobmaster"( "awrjobmasterid","placementid","grade","speciality","vacancyid","personid","AWRLinkCode" ) values( "newid","placeid",@grade,@speciality,"vacid","persid","AWRLink" ) ;
      select 'N','Y','Y',"newid","UMess",''
    when 2 then
      set "newid" = "uniquekey"("placeid");
      insert into "AWRJobmaster"( "awrjobmasterid","placementid","grade","speciality","vacancyid","personid","AWRLinkCode" ) values( "newid","placeid",@grade,@speciality,"vacid","persid","AWRLink" ) ;
      select 'N','Y','N',"newid",'',''
    when 3 then
      select 'Y','Y','Y','','',''
    end case
  end if;
  if "BookOrConfirmorPlaceorTSflag" = 'T' then
    select 'Y','Y','Y','','',''
  end if; -- testing code    select 'N','Y','Y',(select first awrjobmasterid from awrjobmaster where vacancyid is not null and personid is not null),'You must ask about other agency work and must check the AWR details temp confirm',''
  if "BookOrConfirmorPlaceorTSflag" = 'S' then
    select 'Y','Y','Y','','',''
  end if; -- testing code    select 'N','N','Y',(select first awrjobmasterid from awrjobmaster where vacancyid is not null and personid is not null),'You must ask about other agency work and must check the AWR details shift timesheet',''
  if "BookOrConfirmorPlaceorTSflag" = 'L' then
    if "placeid" is null then
      select 'N','N','N',null,"string"('For AWR compatability if this timesheet relates to a vacancy you must create a placement before entering a timesheet.',"char"(13),"char"(10),' Go back, create the placement and try again.'),'';
      return
    end if;
    select "employment"."personid","placement"."vacancyid" into "persid","vacid" from "placement" key join "employment" where "placement"."placementid" = "placeid";
    if exists(select * from "person" where "personid" = "persid" and "isnull"("directlyemployed",0) >= 1) then
      select 'Y','Y','Y','','','';
      return
    end if;
    select first "AWRVacancy"."AWRStatus" into "AWRstat" from "AWRVacancy" join "placement" on "AWRVacancy"."vacancyid" = "placement"."vacancyid" where "placement"."placementid" = "placeid";
    select first "AWRStatus","AWRjobmasterid" into "JAWRstat","jobmid" from "AWRjobmaster" where "placementid" = "placeid";
    -- speciality and grade
    if "JAWRstat" is null then
      set "JAWRstat" = 4
    else
      if "AWRWeeks"("jobmid","WeekNo",null,null,null,null) = "WeeksToWarnTimesheet" then
        set "Umess" = "string"('This temp will qualify in ',12-"WeeksToWarnTimesheet",' week(s). Check the AWR Details for what needs to be done. Check for placements that should be linked or for work for other Agencies.')
      end if end if;
    -- 1 not known 2 applies 3 not
    case "JAWRStat"
    when 1 then
      -- need to check work so far here
      if "AWRGap"("jobmid","WeekNo",null,null,null,null) >= 6 then
        select 'N','Y','Y',"jobmid",'',''
      else
        select 'N','N','Y',"jobmid","UMess",''
      end if when 2 then
      if "AWRGap"("jobmid","WeekNo",null,null,null,null) >= 6 then
        select 'N','Y','N',"jobmid",'',''
      else
        select 'N','N','N',"jobmid","UMess",''
      end if when 3 then
      select 'Y','Y','Y','','',''
    when 4 then
      -- no record check vac might have changed
      if "AWRstat" = 3 then
        select 'Y','Y','Y','','',''
      else
        set "newid" = "uniquekey"("placeid");
        select "getquestanswer"("string"('V',"vacancy"."departmentid"),'AWR',"vacancy"."vacancyid"),"LECode","company"."companyid" into "LinkingQuestion","LE","coid" from "placement" key join "employment" key join "company" key join "AWRCompany","placement" key join "vacancy" where "placement"."placementid" = "placeid";
        if "LE" is not null then
          select first "AWRJobMasterID" into "AWRLink" from "AWRJobMaster" key join "placement" key join("vacancy","employment" as "e")
            -- find "master" linked record in case some already linked
            where "AWRJobMasterID" = "AWRLinkCode"
            -- LE code check
            and exists(select "CompanyID" from "AWRCompany" where "LECode" = "LE" and "companyid" = "e"."companyid")
            -- job title or question check
            and "isnull"("e"."position","vacancy"."position") = "LinkingQuestion"
            and "LinkingQuestion" <> ''
            and "e"."personid" = "persid"
        else
          select first "AWRJobMasterID" into "AWRLink" from "AWRJobMaster" key join "placement" key join("vacancy","employment" as "e")
            -- find "master" linked record in case some already linked
            where "AWRJobMasterID" = "AWRLinkCode"
            -- LE code check
            and "e"."companyid" = "coid"
            -- job title or question check
            and "getquestanswer"("string"('V',"vacancy"."departmentid"),'AWR',"vacancy"."vacancyid") = "LinkingQuestion"
            and "LinkingQuestion" <> ''
            and "e"."personid" = "persid"
        end if;
        insert into "AWRJobmaster"( "awrjobmasterid","placementid","grade","speciality","vacancyid","personid","AWRLinkCode" ) values( "newid","placeid",@grade,@speciality,"vacid","persid","AWRLink" ) ;
        select 'N','Y','Y',"newid",'',''
      end if
    end case
  end if
end
}
</code>