====== pears.NetClientVacanciesSingleLineRefresh ======
<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"."NetClientVacanciesSingleLineRefresh"( in "pWebUserID" char(20),in "pLineNo" char(21) default null ) 
result( "ResultString" long varchar ) 
-- 20120528 return values for Client Comment, Temp and location for a single line, to allow refresh without redrawing entire table
-- 20130213 created from GenevaNetClientVacanciesSingleLineRefresh
begin
  declare "LookupType" char(1);
  declare "LookupID" char(20);
  declare "SecAgencyCode" char(1);
  set "LookupType" = "substring"("pLineNo",1,1);
  set "LookupID" = "substring"("pLineNo",2);
  set "SecAgencyCode" = 'A';
  if "LookupType" = 'V' then
    select "isnull"((select "name" from "person" where "personid" = "t"."personid"),"GetQuestAnswer"('S','TS2',"t"."tempshiftid"))+(if(select "status" from "person" where "personid" = "t"."personid") = "SecAgencyCode" then ' (S)' endif)
      +':'
      +"substring"("GetQuestAnswer"('S','TS3',"t"."tempshiftid"),1,149)+':'+"p"."ClientNote" as "ResultString"
      from "vacancy" as "v"
        key join "tempshift" as "t"
        key join "Employment" as "e"
        key join("Company","Person")
        key join "tempshiftplan" as "p"
      where "t"."tempshiftid" = "LookupID"
  else
    select "isnull"((select "name" from "person" where "personid" = "ts"."personid"),"GetQuestAnswer"('S','TS2',"p"."tempshiftplanid"))+(if(select "status" from "person" where "personid" = "ts"."personid") = "SecAgencyCode" then ' (S)' endif)
      +':'
      +"substring"("GetQuestAnswer"('S','TS3',"p"."tempshiftplanid"),1,149)+':'+"p"."ClientNote" as "ResultString"
      from "TempShiftPlan" as "p"
        key join "Vacancy" as "v"
        key join "Employment" as "e"
        key join("Company","Person" as "Pe")
        left outer join "tempshift" as "ts" on "p"."tempshiftplanid" = "ts"."tempshiftplanid" and "ts"."state" <> 'C'
      where "p"."TempShiftPlanID" = "LookupID"
  end if
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacanciesSingleLineRefresh" IS 
{create procedure pears."NetClientVacanciesSingleLineRefresh"( in "pWebUserID" char(20),in "pLineNo" char(21) default null ) 
result( "ResultString" long varchar ) 
-- 20120528 return values for Client Comment, Temp and location for a single line, to allow refresh without redrawing entire table
-- 20130213 created from GenevaNetClientVacanciesSingleLineRefresh
begin
  declare "LookupType" char(1);
  declare "LookupID" char(20);
  declare "SecAgencyCode" char(1);
  set "LookupType" = "substring"("pLineNo",1,1);
  set "LookupID" = "substring"("pLineNo",2);
  set "SecAgencyCode" = 'A';
  if "LookupType" = 'V' then
    select "isnull"((select "name" from "person" where "personid" = "t"."personid"),"GetQuestAnswer"('S','TS2',"t"."tempshiftid"))+(if(select "status" from "person" where "personid" = "t"."personid") = "SecAgencyCode" then ' (S)' endif)
      +':'
      +"substring"("GetQuestAnswer"('S','TS3',"t"."tempshiftid"),1,149)+':'+"p"."ClientNote" as "ResultString"
      from "vacancy" as "v"
        key join "tempshift" as "t"
        key join "Employment" as "e"
        key join("Company","Person")
        key join "tempshiftplan" as "p"
      where "t"."tempshiftid" = "LookupID"
  else
    select "isnull"((select "name" from "person" where "personid" = "ts"."personid"),"GetQuestAnswer"('S','TS2',"p"."tempshiftplanid"))+(if(select "status" from "person" where "personid" = "ts"."personid") = "SecAgencyCode" then ' (S)' endif)
      +':'
      +"substring"("GetQuestAnswer"('S','TS3',"p"."tempshiftplanid"),1,149)+':'+"p"."ClientNote" as "ResultString"
      from "TempShiftPlan" as "p"
        key join "Vacancy" as "v"
        key join "Employment" as "e"
        key join("Company","Person" as "Pe")
        left outer join "tempshift" as "ts" on "p"."tempshiftplanid" = "ts"."tempshiftplanid" and "ts"."state" <> 'C'
      where "p"."TempShiftPlanID" = "LookupID"
  end if
end
}
</code>