pears.AWRLinkedPlacements

CREATE PROCEDURE "pears"."AWRLinkedPlacements"( IN "JobMid" CHAR(20),IN "placeid" CHAR(20) ) 
RESULT( "Jobmasterid" CHAR(20),"Linkedflag" CHAR(1) ) 
BEGIN
  -- return a list of already linked AWR records in the correct order and potential linked ones
  DECLARE @lecode CHAR(50);
  DECLARE @linkcode CHAR(50);
  DECLARE @personid CHAR(20);
  DECLARE @companyid CHAR(20);
  SELECT "LECode","AWRLinkCode","employment"."personid","company"."companyid" INTO @lecode,@linkcode,@personid,@companyid FROM "AWRCompany" KEY JOIN "company" KEY JOIN "employment" KEY JOIN "placement" KEY JOIN "AWRjobmaster" WHERE "AWRJobmasterid" = "jobmid";
  IF @LECode IS NULL THEN
    SELECT "AWRJobmasterid",(IF "AWRLinkCode" IS NOT NULL AND "AWRLinkCode" = @linkcode THEN 'Y'
      ELSE 'N'
      endif) FROM "AWRJobmaster" KEY JOIN "placement" KEY JOIN "employment" KEY JOIN "company" KEY JOIN "AWRcompany" WHERE "company"."companyid" = @companyid AND "AWRJobMasterID" <> "JobMid" AND "employment"."personid" = @personid
  ELSE
    SELECT "AWRJobmasterid",(IF "AWRLinkCode" IS NOT NULL AND "AWRLinkCode" = @linkcode THEN 'Y'
      ELSE 'N'
      endif) FROM "AWRJobmaster" KEY JOIN "placement" KEY JOIN "employment" KEY JOIN "company" KEY JOIN "AWRcompany" WHERE "LECode" = @LECode AND "AWRJobMasterID" <> "JobMid" AND "employment"."personid" = @personid
  END IF
END