====== pears.NetAgencyCandidateSubmit ======
<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"."NetAgencyCandidateSubmit"( in "pWebUserID" char(20),in "pPersonID" char(20),in "pVacancyID" char(20) default null,in "pShiftPlanID" char(20) default null ) 
result( "pResult" char(250) ) 
-- 20120814 modified to check if candidate has already been selected for that vacancy
begin
  declare @SecAgID char(20);
  declare @sname char(100);
  if "IQXNetHasPermission"("pWebUserID",'SUBMITCANDIDATES') = 0 then
    select '99:~Permission denied';
    return
  end if;
  set @SecAgID = (select first "employment"."companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid");
  if "trim"("isnull"("pShiftPlanID",'')) <> '' then
    if not "pshiftplanid" = any(select "tempshiftplanid" from "cascadedshift" where "secondaryagencyid" = @secagid) then
      select '99:~Permission denied';
      return
    end if;
    insert into "tempshiftprogress"( "tempshiftprogressid","tempshiftplanid","personid","staffid","status" ) values( "uniquekey"("ppersonid"+"pshiftplanid"),"pshiftplanid","ppersonid","userstaffid",'A' ) 
  else
    if not "pvacancyid" = any(select "vacancyid" from "cascadedvacancy" where "secondaryagencyid" = @secagid) then
      select '99:~Permission denied';
      return
    end if;
    set @sname = (select first "name" from "person" where "personid" = "ppersonid");
    if(select(if "count"() = 0 then 'FALSE'
        else 'TRUE'
        endif) as "IsPresent" from "progress" as "pr" left outer join "status" as "s" on "s"."status" = "pr"."status" where "pr"."personid" = "pPersonID" and "pr"."vacancyid" = "pVacancyID" and "s"."type" = 'R' and "s"."final" = 0) = 'FALSE' then
      insert into "progress"( "progressid","vacancyid","personid","staffid","status","actiondate" ) values( "uniquekey"("ppersonid"+"pvacancyid"),"pvacancyid","ppersonid","userstaffid",'A',current date ) 
    else
      select '99:~'+@sname+' has already been submitted';
      return
    end if end if;
  call "IQXNetPopup"("pWebUserID","pPersonID",null,"pvacancyid",null,null,'SUBMITCANDIDATE','Secondary Agency candidate submitted',@sname);
  select '0:~Success'
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyCandidateSubmit" IS 
{create procedure pears."NetAgencyCandidateSubmit"( in "pWebUserID" char(20),in "pPersonID" char(20),in "pVacancyID" char(20) default null,in "pShiftPlanID" char(20) default null ) 
result( "pResult" char(250) ) 
-- 20120814 modified to check if candidate has already been selected for that vacancy
begin
  declare @SecAgID char(20);
  declare @sname char(100);
  if "IQXNetHasPermission"("pWebUserID",'SUBMITCANDIDATES') = 0 then
    select '99:~Permission denied';
    return
  end if;
  set @SecAgID = (select first "employment"."companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid");
  if "trim"("isnull"("pShiftPlanID",'')) <> '' then
    if not "pshiftplanid" = any(select "tempshiftplanid" from "cascadedshift" where "secondaryagencyid" = @secagid) then
      select '99:~Permission denied';
      return
    end if;
    insert into "tempshiftprogress"( "tempshiftprogressid","tempshiftplanid","personid","staffid","status" ) values( "uniquekey"("ppersonid"+"pshiftplanid"),"pshiftplanid","ppersonid","userstaffid",'A' ) 
  else
    if not "pvacancyid" = any(select "vacancyid" from "cascadedvacancy" where "secondaryagencyid" = @secagid) then
      select '99:~Permission denied';
      return
    end if;
    set @sname = (select first "name" from "person" where "personid" = "ppersonid");
    if(select(if "count"() = 0 then 'FALSE'
        else 'TRUE'
        endif) as "IsPresent" from "progress" as "pr" left outer join "status" as "s" on "s"."status" = "pr"."status" where "pr"."personid" = "pPersonID" and "pr"."vacancyid" = "pVacancyID" and "s"."type" = 'R' and "s"."final" = 0) = 'FALSE' then
      insert into "progress"( "progressid","vacancyid","personid","staffid","status","actiondate" ) values( "uniquekey"("ppersonid"+"pvacancyid"),"pvacancyid","ppersonid","userstaffid",'A',current date ) 
    else
      select '99:~'+@sname+' has already been submitted';
      return
    end if end if;
  call "IQXNetPopup"("pWebUserID",pPersonID,null,"pvacancyid",null,null,'SUBMITCANDIDATE','Secondary Agency candidate submitted',@sname);
  select '0:~Success'
end
}
</code>