pears.NetOwnerPlacementList

CREATE PROCEDURE "pears"."NetOwnerPlacementList"( IN "pWebUserID" CHAR(20),IN "pSlice" INTEGER DEFAULT 0,IN "pSliceSize" INTEGER DEFAULT 20,IN "pCompanyID" CHAR(20),IN "pPeriodDate" CHAR(20) DEFAULT NULL ) 
RESULT( "CandidateName" CHAR(100),"PersonID" CHAR(20),"StartDate" CHAR(16),"LeaveDate" CHAR(16),"JobTitle" CHAR(100),"IsTemp" INTEGER,"ConsultantName" CHAR(100),"ContactName" CHAR(100) ) 
// IQXWeb
BEGIN
  DECLARE "x" INTEGER;
  DECLARE "y" INTEGER;
  SET "x" = "pSliceSize";
  SET "y" = "x"*"pSlice"+1;
  SELECT top "x" START at "y"
    "pe"."name" AS "CandidateName",
    "pe"."personid",
    "e"."startdate",
    "e"."leavedate",
    "v"."position",
    "p"."temp",
    "s"."name" AS "ConsultantName",
    "pc"."name" AS "ContactName"
    FROM "person" AS "pe"
      KEY JOIN "employment" AS "e"
      KEY JOIN "placement" AS "p"
      KEY JOIN "vacancy" AS "v"
      KEY JOIN "staff" AS "s"
      ,"vacancy" AS "v"
      KEY JOIN "employment" AS "em"
      KEY JOIN("person" AS "pc","company" AS "c")
    WHERE "isnull"("e"."noreemploy",0) = 0
    AND "c"."companyid" = "pCompanyID"
    AND "isnull"("e"."leavedate","today"()) <= "isnull"("pPeriodDate","today"())
    ORDER BY "e"."startdate" DESC,
    "pe"."keyname" ASC
END /* DOC
2018-02-14 PC OP-32 list of placement records
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerPlacementList" IS 
{CREATE PROCEDURE pears."NetOwnerPlacementList" (IN pWebUserID CHAR(20), IN pSlice INTEGER DEFAULT 0, IN pSliceSize INTEGER DEFAULT 20, pCompanyID CHAR(20),pPeriodDate CHAR(20) DEFAULT NULL)
RESULT(CandidateName CHAR(100),PersonID CHAR(20),StartDate CHAR(16),LeaveDate CHAR(16),JobTitle CHAR(100),IsTemp INTEGER,ConsultantName CHAR(100),ContactName CHAR(100))
// IQXWeb
BEGIN
    DECLARE x INTEGER;
    DECLARE y INTEGER;
    SET x=pSliceSize;
    SET y=x*pSlice+1;
 
    SELECT  top x START at y
        pe.name AS CandidateName,
        pe.personid,
        e.startdate,
        e.leavedate,
        v.position,
        p.temp,
        s.name AS ConsultantName,
        pc.name AS ContactName
    FROM 
        person pe 
            KEY JOIN employment e 
            KEY JOIN placement p 
            KEY JOIN vacancy v 
            KEY JOIN staff s,
        vacancy v 
            KEY JOIN employment em 
            KEY JOIN (person pc,company c) 
    WHERE 
        isnull(e.noreemploy,0)=0
        AND c.companyid=pCompanyID
        AND isnull(e.leavedate, today(*)) <= isnull(pPeriodDate, today(*))
    ORDER BY
        e.startdate DESC,
        pe.keyname ASC
END
 
/* DOC
2018-02-14 PC OP-32 list of placement records
 
*/
}