pears.NetClientPreviousEmployees

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

CREATE PROCEDURE "pears"."NetClientPreviousEmployees"( IN "pWebUserID" CHAR(20) ) 
RESULT( "Name" CHAR(100),"PersonID" CHAR(20),"KeyName" CHAR(100) ) 
BEGIN
  DECLARE LOCAL TEMPORARY TABLE "Cos"(
    "CompanyID" CHAR(20) NULL,
    ) NOT transactional;
  -- Get all the Companies the WebUser is Associated with
  DECLARE LOCAL TEMPORARY TABLE "Pes"(
    "PersonID" CHAR(20) NULL,
    ) NOT transactional;INSERT INTO "Cos"( "CompanyID" ) 
    SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID";
  -- Get all the People who have worked in the period for those Companies
  INSERT INTO "Pes"( "PersonID" ) 
    SELECT DISTINCT "e"."personid"
      FROM "employment" AS "e" KEY JOIN "placement"
        ,"employment" AS "e" JOIN "Cos" ON "e"."CompanyID" = "Cos"."CompanyID"
      WHERE NOT "e"."personid" = any(SELECT "personid" FROM "employment" WHERE "companyid" = "e"."companyid" AND "noreemploy" = 1)
      AND "e"."startdate" >= "IQXNetCompanyStartDate"("e"."companyid");
  -- Get the Peoples details
  SELECT "person"."name","person"."personid","person"."keyname" FROM "person" JOIN "PES" ON "Pes"."PersonID" = "Person"."PersonID"
    ORDER BY "person"."keyname" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientPreviousEmployees" IS 
{CREATE PROCEDURE pears."NetClientPreviousEmployees"( IN "pWebUserID" CHAR(20) ) 
RESULT( "Name" CHAR(100),"PersonID" CHAR(20),"KeyName" CHAR(100) ) 
BEGIN
  DECLARE LOCAL TEMPORARY TABLE "Cos"(
    "CompanyID" CHAR(20) NULL,
    ) NOT transactional;
  -- Get all the Companies the WebUser is Associated with
  DECLARE LOCAL TEMPORARY TABLE "Pes"(
    "PersonID" CHAR(20) NULL,
    ) NOT transactional;INSERT INTO "Cos"( "CompanyID" ) 
    SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID";
  -- Get all the People who have worked in the period for those Companies
  INSERT INTO "Pes"( "PersonID" ) 
    SELECT DISTINCT "e"."personid"
      FROM "employment" AS "e" KEY JOIN "placement"
        ,"employment" AS "e" JOIN "Cos" ON "e"."CompanyID" = "Cos"."CompanyID"
      WHERE NOT "e"."personid" = any(SELECT "personid" FROM "employment" WHERE "companyid" = "e"."companyid" AND "noreemploy" = 1)
      AND "e"."startdate" >= "IQXNetCompanyStartDate"("e"."companyid");
  -- Get the Peoples details
  SELECT "person"."name","person"."personid","person"."keyname" FROM "person" JOIN "PES" ON "Pes"."PersonID" = "Person"."PersonID"
    ORDER BY "person"."keyname" ASC
END
}