pears.ComplianceScore

CREATE FUNCTION "pears"."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" AS "v" KEY JOIN "employment" JOIN "company" AS "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