pears.ComplianceScore

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."ComplianceScore" IS 
{CREATE FUNCTION ComplianceScore 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( IN "pPersonID" CHAR(20),IN "pVacancyID" CHAR(20) DEFAULT NULL ) 
RETURNS DOUBLE
BEGIN
  DECLARE "act" DOUBLE;
  DECLARE "poss" DOUBLE;
  DECLARE "pComplianceDomainID" CHAR(20);
  DECLARE pClientCode CHAR(12);
  DECLARE "pDate" DATE;
  DECLARE "pDeptID" CHAR(2);
  SET "act" = 0;
  SET "poss" = 0;
  IF "pVacancyID" IS NOT NULL THEN
    SELECT v."ComplianceDomainID",v."StartDate",v."DepartmentID",c.TempComplianceCode INTO "pComplianceDomainID","pDate","pDeptID",pClientCode FROM vacancy v KEY JOIN employment JOIN company c WHERE v."vacancyid" = "pVacancyID";
    IF "pComplianceDomainID" IS NULL THEN
      SELECT FIRST "ComplianceDomainID" INTO "pComplianceDomainID" FROM "ComplianceDomain" WHERE "DepartmentID" = "pDeptID" AND "Name" LIKE 'Default%'
      AND (ClientTempComplianceCode IS NULL OR ClientTempComplianceCode=pClientCode) 
      ORDER BY ClientTempComplianceCode DESC, Name ASC
    END IF;
    IF "pComplianceDomainID" IS NULL THEN
      RETURN 0
    END IF
  END IF;
  FOR "PFOR" AS "PCUR" no scroll cursor FOR
    SELECT "AchievedScore","Score"
      FROM "ComplianceTable"("pPersonID","pComplianceDomainID","pDate",pClientCode) FOR READ ONLY do
    SET "act" = "act"+"isnull"("AchievedScore",0.0);
    SET "poss" = "poss"+"isnull"("Score",0.0)
  END FOR;
  IF "poss" <= 0 THEN
    RETURN 0
  ELSE
    RETURN("act"/"poss")*100.0
  END IF
END
}