pears.ComplianceSummary

CREATE FUNCTION "pears"."ComplianceSummary"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "pPersonID" CHAR(20) ) 
RETURNS long VARCHAR
BEGIN
  DECLARE "errMsg" long VARCHAR;
  DECLARE "warnMsg" long VARCHAR;
  DECLARE "act" DOUBLE;
  DECLARE "poss" DOUBLE;
  DECLARE "xScore" CHAR(20);
  SET "act" = 0;
  SET "poss" = 0;
  FOR "PFOR" AS "PCUR" no scroll cursor FOR
    SELECT "Description","Status","Info","AchievedScore","Score"
      FROM "ComplianceTable"("pPersonID") FOR READ ONLY do
    CASE "status"
    WHEN 1 THEN SET "errMsg" = "string"("errMsg","Description",' ',"Info",'. ')
    WHEN 2 THEN SET "warnMsg" = "string"("warnMsg","Description",' ',"Info",'. ')
    END CASE;
    SET "act" = "act"+"isnull"("AchievedScore",0.0);
    SET "poss" = "poss"+"isnull"("Score",0.0)
  END FOR;
  IF "warnMsg" IS NOT NULL THEN
    SET "warnMsg" = "string"('Warning: ',"warnMsg")
  END IF;
  IF "poss" <= 0 THEN
    SET "xScore" = ''
  ELSE
    SET "xScore" = "trim"("str"(("act"/"poss")*100.0))+'% '
  END IF;
  RETURN "string"("xScore","errMsg","warnMsg")
END