====== pears.AWRCheck ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original 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
}