pears.NetClientVacanciesSingleLineRefresh

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