Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
CREATE PROCEDURE "pears"."ComplianceTableAdd"( /* Application Maintained Function / Procedure - DO NOT EDIT*/ IN "pPersonID" CHAR(20),IN "pComplianceDomainID" CHAR(20),IN "AtDate" DATE,INOUT "DupeCheck" long VARCHAR,INOUT "Cnt" SMALLINT,IN "pClientCode" CHAR(12) DEFAULT NULL ) BEGIN -- Result.Status values 0=ok 1=fail 2=warn DECLARE "curdate" DOUBLE; DECLARE "tdesc" CHAR(200); DECLARE "tldesc" long VARCHAR; DECLARE "ttype" CHAR(1); DECLARE "texpiry" SMALLINT; DECLARE "texpirybehaviour" SMALLINT; DECLARE "tval" DOUBLE; DECLARE "xstatus" tinyint; DECLARE "xachieved" SMALLINT; DECLARE "xinfo" CHAR(100); DECLARE "xdate" CHAR(20); DECLARE "bok" tinyint; SET "DupeCheck" = "isnull"("DupeCheck",''); IF "charindex"("string"('|',"pComplianceDomainID",'|'),"DupeCheck") > 0 THEN RETURN END IF; SET "DupeCheck" = "string"("DupeCheck",'|',"pComplianceDomainID",'|'); SET "Cnt" = "isnull"("Cnt",0); SET "curdate" = "datediff"("day","date"('1899-12-30'),"AtDate"); FOR "PFOR" AS "PCUR" no scroll cursor FOR SELECT "cdtag"."tagid" AS "tid", CASE "cdtag"."taglocation" WHEN 'P' THEN 'P' WHEN 'A%' THEN "string"('A',"cd"."departmentid") WHEN 'R%' THEN "string"('R',"cd"."departmentid") END AS "tloc", IF "cdtag"."tagchoiceid" = 'N/A' THEN NULL ELSE "cdtag"."tagchoiceid" endif AS "tcid", "cdtag"."behaviour" AS "tbehaviour","cdtag"."score" AS "tscore","cdtag"."minvalue" AS "tmin", IF "cdtag"."taglocation" LIKE 'R%' THEN 'Ref' ELSE 'App' endif AS "dtype" FROM "ComplianceDomainTag" AS "cdtag" KEY JOIN "ComplianceDomain" AS "cd" WHERE "cdtag"."ComplianceDomainID" = "pComplianceDomainID" ORDER BY "cdtag"."SortOrder" ASC FOR READ ONLY do SELECT "string"("tag"."description","ifnull"("tc"."description",'',': '),"tc"."description"), "string"("tag"."longdescription","ifnull"("tc"."longdescription",'',': '),"tc"."longdescription"), "tag"."tagtype","tag"."expiry","tag"."expirybehaviour", IF "tag"."tagtype" IN( 'T','U' ) THEN (SELECT FIRST 1.0 FROM "tagvalue" WHERE "taglocation" = "tloc" AND "tagid" = "tid" AND "id" = "pPersonID" AND "textvalue" <> '') ELSE (SELECT "max"("value") FROM "tagvalue" WHERE "taglocation" = "tloc" AND "tagid" = "tid" AND "id" = "pPersonID" AND "value" <> 0 AND("tcid" IS NULL OR "tagchoiceid" = "tcid")) endif INTO "tdesc","tldesc","ttype","texpiry","texpirybehaviour","tval" FROM "tag" LEFT OUTER JOIN "tagchoice" AS "tc" ON "tc"."taglocation" = "tloc" AND "tc"."tagid" = "tid" AND "tc"."tagchoiceid" = "tcid" WHERE "tag"."taglocation" = "tloc" AND "tag"."tagid" = "tid"; IF "tval" IS NULL THEN SET "xstatus" = "tbehaviour"; SET "xachieved" = 0; SET "xinfo" = 'Missing' ELSE IF "ttype" = 'D' AND "texpirybehaviour" > 0 AND "tval"-"isnull"("texpiry",0) <= "curdate" THEN SET "xachieved" = 0; SET "xdate" = "dateformat"("dateadd"("day","tval","date"('1899-12-30')),'dd/mm/yy'); IF "tval" <= "curdate" THEN SET "xstatus" = "texpirybehaviour"; SET "xinfo" = "string"('Expired ',"xdate") ELSE SET "xstatus" = 2; SET "xinfo" = "string"('Expiring ',"xdate") END IF ELSE IF "tmin" > 1 AND "ttype" IN( 'N','G','S' ) AND "tval" < "tmin" THEN SET "xstatus" = "tbehaviour"; SET "xachieved" = 0; SET "xinfo" = "string"('< ',"tmin") ELSE SET "xstatus" = 0; SET "xachieved" = "tscore"; SET "xinfo" = 'Ok' END IF END IF END IF; SET "cnt" = "cnt"+1; IF "xstatus" = 3 THEN // Score ONLY SET "xstatus" = 0 END IF; INSERT INTO "ComplianceTmpTbl"( "Description","LongDescription","Behaviour","Status","Score","AchievedScore","Info","SortOrder","TagLocation","TagID","TagChoiceID","type" ) VALUES ( "tdesc","tldesc","tbehaviour","xstatus","tscore","xachieved","xinfo","cnt","tloc","tid","tcid","dtype" ) END FOR; FOR "PFOR" AS "PCURD" no scroll cursor FOR SELECT "cdt"."documenttypeid" AS "tDoctypeID","dt"."description" AS "dtdesc","isnull"("expirylead",0) AS "texpirylead", "cdt"."behaviour" AS "tbehaviour","cdt"."score" AS "tscore" FROM "ComplianceDocumentType" AS "cdt" KEY JOIN "documenttype" AS "dt" WHERE "cdt"."ComplianceDomainID" = "pComplianceDomainID" AND "isnull"("dt"."iscompliance",0) = 1 AND "isnull"("dt"."defunct",0) = 0 ORDER BY "dt"."SortOrder" ASC FOR READ ONLY do SET "tdesc" = NULL; SELECT top 1 "string"("dtdesc",':',"o"."description"),"o"."expirydate" INTO "tdesc","texpiry" FROM "oledocument" AS "o" JOIN "blobstore" AS "b" ON("class" = 'O' AND "o"."oledocumentid" = "b"."id") WHERE "o"."documenttypeid" = "tDoctypeID" AND "o"."ownertype" = 'p' AND "ownerid" = "ppersonid" AND "whenarchived" IS NULL ORDER BY "o"."defunct" ASC,"whenarchived" ASC; IF "tdesc" IS NULL THEN SET "tdesc" = "dtdesc"; SET "xstatus" = "tbehaviour"; SET "xachieved" = 0; SET "xinfo" = 'Missing' ELSE IF(NOT(("texpiry" IS NOT NULL) AND("texpirylead" > 0))) OR("texpirybehaviour" = 0) THEN SET "xstatus" = 0; SET "xachieved" = "tscore"; SET "xinfo" = 'Ok' ELSE IF "dateadd"("day","texpirylead","texpiry") <= "curdate" THEN SET "xachieved" = 0; SET "xstatus" = "texpirybehaviour"; SET "xinfo" = "string"('Expired ',"xdate") ELSE SET "xstatus" = 2; SET "xinfo" = "string"('Expiring ',"xdate") END IF END IF END IF; SET "cnt" = "cnt"+1; IF "xstatus" = 3 THEN // Score ONLY SET "xstatus" = 0 END IF; INSERT INTO "ComplianceTmpTbl"( "Description","LongDescription","Behaviour","Status","Score","AchievedScore","Info","SortOrder","type" ) VALUES ( "tdesc","tdesc","tbehaviour","xstatus","tscore","xachieved","xinfo","cnt",'Doc' ) END FOR; FOR "PFOR" AS "PCURR" no scroll cursor FOR SELECT "referencecount","score" AS "rscore","string"(CASE "reftype" WHEN 1 THEN "isnull"((SELECT "WPKMaintainGetSwitchValue"('REFERTYPE1','','L')),'Personal') WHEN 2 THEN "isnull"((SELECT "WPKMaintainGetSwitchValue"('REFERTYPE2','','L')),'Professional') WHEN 3 THEN 'Other' WHEN 4 THEN "isnull"((SELECT "WPKMaintainGetSwitchValue"('REFERTYPE3','','L')),'/N/A') END, ' ( ',"referencecount",' required, ',"rcnt",' returned)') AS "rdesc", "isnull"((SELECT "count"() FROM "referencerequest" AS "r" WHERE "r"."reftype" = "c"."reftype" AND "personid" = "pPersonID" AND "whencompleted" IS NOT NULL),0) AS "rcnt", IF "rcnt" >= "referencecount" THEN "score" ELSE 0 endif AS "tscore" FROM "ComplianceReferenceType" AS "c" WHERE "referencecount" > 0 AND "c"."ComplianceDomainID" = "pComplianceDomainID" ORDER BY "c"."reftype" ASC do SET "cnt" = "cnt"+1; INSERT INTO "ComplianceTmpTbl"( "Description","LongDescription","Status","Score","AchievedScore","Info","SortOrder","type" ) VALUES ( "rdesc","rdesc",IF "tscore" = 1 THEN 0 ELSE 1 endif,"rscore","tscore",IF "tscore" = 1 THEN 'Ok' ELSE 'Missing' endif,"cnt",'Ref Count' ) END FOR; FOR "PFOR1" AS "PCUR1" no scroll cursor FOR SELECT "ld"."ComplianceLinkedDomainID","ld"."IfTagLocation","ld"."IfTagID","ld"."IfTagChoiceID","ld"."IfTagChoiceNegate",(SELECT "DepartmentID" FROM "ComplianceDomain" WHERE "ComplianceDomainID" = "pComplianceDomainID") AS "DeptID" FROM "ComplianceLinkedDomain" AS "ld" JOIN "ComplianceDomain" AS "cd" ON "ld"."ComplianceLinkedDomainID" = "cd"."ComplianceDomainID" WHERE "ld"."ComplianceDomainID" = "pComplianceDomainID" AND("cd"."ClientTempComplianceCode" IS NULL OR "cd"."ClientTempComplianceCode" = "pClientCode") FOR READ ONLY do SET "bok" = 0; IF "IfTagLocation" IS NULL OR "IfTagID" IS NULL OR "IfTagChoiceID" IS NULL THEN SET "bok" = 1 ELSE SELECT FIRST 1 INTO "bok" FROM "tagvalue" WHERE "id" = "pPersonID" AND "taglocation" = (IF "iftaglocation" = 'P' THEN 'P' ELSE 'A'+"DeptID" endif) AND "tagid" = "iftagid" AND "tagchoiceid" = "iftagchoiceid"; IF "IfTagChoiceNegate" = 1 THEN IF "bok" = 1 THEN SET "bok" = 0 ELSE SET "bok" = 1 END IF END IF END IF; IF "bok" = 1 THEN CALL "ComplianceTableAdd"("pPersonID","ComplianceLinkedDomainID","AtDate","DupeCheck","Cnt","pClientCode") END IF END FOR END GO COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."ComplianceTableAdd" IS {CREATE PROCEDURE ComplianceTableAdd /* Application Maintained Function / Procedure - DO NOT EDIT*/ ( IN pPersonID CHAR(20),IN pComplianceDomainID CHAR(20),IN AtDate DATE,INOUT DupeCheck long VARCHAR,INOUT Cnt SMALLINT,IN pClientCode CHAR(12) DEFAULT NULL ) BEGIN -- Result.Status values 0=ok 1=fail 2=warn DECLARE curdate DOUBLE; DECLARE tdesc CHAR(200); DECLARE tldesc long VARCHAR; DECLARE ttype CHAR(1); DECLARE texpiry SMALLINT; DECLARE texpirybehaviour SMALLINT; DECLARE tval DOUBLE; DECLARE xstatus tinyint; DECLARE xachieved SMALLINT; DECLARE xinfo CHAR(100); DECLARE xdate CHAR(20); DECLARE bok tinyint; SET DupeCheck = isnull(DupeCheck,''); IF charindex(string('|',pComplianceDomainID,'|'),DupeCheck) > 0 THEN RETURN END IF; SET DupeCheck = string(DupeCheck,'|',pComplianceDomainID,'|'); SET Cnt = isnull(Cnt,0); SET curdate = datediff(DAY,DATE('1899-12-30'),AtDate); FOR PFOR AS PCUR no scroll cursor FOR SELECT cdtag.tagid AS tid, CASE cdtag.taglocation WHEN 'P' THEN 'P' WHEN 'A%' THEN string( 'A', cd.departmentid ) WHEN 'R%' THEN string( 'R', cd.departmentid ) END AS tloc, IF cdtag.tagchoiceid = 'N/A' THEN NULL ELSE cdtag.tagchoiceid endif AS tcid, cdtag.behaviour AS tbehaviour,cdtag.score AS tscore,cdtag.minvalue AS tmin, IF cdtag.taglocation LIKE 'R%' THEN 'Ref' ELSE 'App' END IF AS dtype FROM ComplianceDomainTag AS cdtag KEY JOIN ComplianceDomain AS cd WHERE cdtag.ComplianceDomainID = pComplianceDomainID ORDER BY cdtag.SortOrder ASC FOR READ ONLY do SELECT string(tag.description,ifnull(tc.description,'',': '),tc.description), string(tag.longdescription,ifnull(tc.longdescription,'',': '),tc.longdescription), tag.tagtype,tag.expiry,tag.expirybehaviour, IF tag.tagtype IN( 'T','U' ) THEN (SELECT FIRST 1.0 FROM tagvalue WHERE taglocation = tloc AND tagid = tid AND id = pPersonID AND textvalue <> '') ELSE (SELECT MAX(VALUE) FROM tagvalue WHERE taglocation = tloc AND tagid = tid AND id = pPersonID AND VALUE <> 0 AND(tcid IS NULL OR tagchoiceid = tcid)) endif INTO tdesc,tldesc,ttype,texpiry,texpirybehaviour,tval FROM tag LEFT OUTER JOIN tagchoice AS tc ON tc.taglocation = tloc AND tc.tagid = tid AND tc.tagchoiceid = tcid WHERE tag.taglocation = tloc AND tag.tagid = tid; IF tval IS NULL THEN SET xstatus = tbehaviour; SET xachieved = 0; SET xinfo = 'Missing' ELSE IF ttype = 'D' AND texpirybehaviour > 0 AND tval-isnull(texpiry,0) <= curdate THEN SET xachieved = 0; SET xdate = dateformat(dateadd(DAY,tval,DATE('1899-12-30')),'dd/mm/yy'); IF tval <= curdate THEN SET xstatus = texpirybehaviour; SET xinfo = string('Expired ',xdate) ELSE SET xstatus = 2; SET xinfo = string('Expiring ',xdate) END IF ELSE IF tmin > 1 AND ttype IN( 'N','G','S' ) AND tval < tmin THEN SET xstatus = tbehaviour; SET xachieved = 0; SET xinfo = string('< ',tmin) ELSE SET xstatus = 0; SET xachieved = tscore; SET xinfo = 'Ok' END IF END IF END IF; SET cnt = cnt+1; IF xstatus = 3 THEN // Score ONLY SET xstatus = 0 END IF; INSERT INTO ComplianceTmpTbl( Description,LongDescription,Behaviour,STATUS,Score,AchievedScore,Info,SortOrder,TagLocation,TagID,TagChoiceID,TYPE ) VALUES ( tdesc,tldesc,tbehaviour,xstatus,tscore,xachieved,xinfo,cnt,tloc,tid,tcid,dtype ) END FOR; FOR PFOR AS PCURD no scroll cursor FOR SELECT cdt.documenttypeid AS tDoctypeID, dt.description AS dtdesc, isnull(expirylead,0) AS texpirylead, cdt.behaviour AS tbehaviour,cdt.score AS tscore FROM ComplianceDocumentType cdt KEY JOIN documenttype dt WHERE cdt.ComplianceDomainID = pComplianceDomainID AND isnull(dt.iscompliance,0)=1 AND isnull(dt.defunct,0)=0 ORDER BY dt.SortOrder ASC FOR READ ONLY do SET tdesc = NULL; SELECT top 1 string(dtdesc,':', o.description) , o.expirydate INTO tdesc,texpiry FROM oledocument o JOIN blobstore b ON (class = 'O' AND o.oledocumentid = b.id) WHERE o.documenttypeid = tDoctypeID AND o.ownertype = 'p' AND ownerid = ppersonid AND whenarchived IS NULL ORDER BY o.defunct,whenarchived; IF tdesc IS NULL THEN SET tdesc = dtdesc; SET xstatus = tbehaviour; SET xachieved = 0; SET xinfo = 'Missing' ; ELSE IF (NOT ((texpiry IS NOT NULL) AND (texpirylead>0))) OR (texpirybehaviour=0) THEN SET xstatus = 0; SET xachieved = tscore; SET xinfo = 'Ok' ; ELSE IF dateadd( DAY, texpirylead, texpiry) <= curdate THEN SET xachieved = 0; SET xstatus = texpirybehaviour; SET xinfo = string('Expired ',xdate); ELSE SET xstatus = 2; SET xinfo = string('Expiring ',xdate); END IF END IF END IF ; SET cnt = cnt+1; IF xstatus = 3 THEN // Score ONLY SET xstatus = 0 END IF; INSERT INTO ComplianceTmpTbl( Description,LongDescription,Behaviour,STATUS,Score,AchievedScore,Info,SortOrder, TYPE) VALUES ( tdesc,tdesc,tbehaviour,xstatus,tscore,xachieved,xinfo,cnt,'Doc') END FOR; FOR PFOR AS PCURR no scroll cursor FOR SELECT referencecount, score AS rscore, string(CASE reftype WHEN 1 THEN isnull((SELECT WPKMaintainGetSwitchValue ('REFERTYPE1', '','L')), 'Personal') WHEN 2 THEN isnull((SELECT WPKMaintainGetSwitchValue ('REFERTYPE2', '','L')), 'Professional') WHEN 3 THEN 'Other' WHEN 4 THEN isnull((SELECT WPKMaintainGetSwitchValue ('REFERTYPE3', '','L')), '/N/A') END, ' ( ',referencecount,' required, ', rcnt, ' returned)') AS rdesc, isnull((SELECT COUNT(*) FROM referencerequest r WHERE r.reftype=c.reftype AND personid = pPersonID AND whencompleted IS NOT NULL ),0) AS rcnt, IF rcnt>=referencecount THEN score ELSE 0 END IF AS tscore FROM ComplianceReferenceType c WHERE referencecount>0 AND c.ComplianceDomainID= pComplianceDomainID ORDER BY c.reftype do SET cnt = cnt+1; INSERT INTO ComplianceTmpTbl( Description,LongDescription,STATUS, Score,AchievedScore,Info,SortOrder, TYPE) VALUES ( rdesc,rdesc, IF tscore=1 THEN 0 ELSE 1 END IF, rscore,tscore, IF tscore=1 THEN 'Ok' ELSE 'Missing' END IF, cnt,'Ref Count') END FOR; FOR PFOR1 AS PCUR1 no scroll cursor FOR SELECT ld.ComplianceLinkedDomainID,ld.IfTagLocation,ld.IfTagID,ld.IfTagChoiceID,ld.IfTagChoiceNegate,(SELECT DepartmentID FROM ComplianceDomain WHERE ComplianceDomainID = pComplianceDomainID) AS DeptID FROM ComplianceLinkedDomain ld JOIN ComplianceDomain cd ON ld.ComplianceLinkedDomainID=cd.ComplianceDomainID WHERE ld.ComplianceDomainID = pComplianceDomainID AND (cd.ClientTempComplianceCode IS NULL OR cd.ClientTempComplianceCode=pClientCode) FOR READ ONLY do SET bok = 0; IF IfTagLocation IS NULL OR IfTagID IS NULL OR IfTagChoiceID IS NULL THEN SET bok = 1 ELSE SELECT FIRST 1 INTO bok FROM tagvalue WHERE id = pPersonID AND taglocation = (IF iftaglocation = 'P' THEN 'P' ELSE 'A'+DeptID endif) AND tagid = iftagid AND tagchoiceid = iftagchoiceid; IF IfTagChoiceNegate=1 THEN IF bok=1 THEN SET bok=0 ELSE SET bok=1 END IF; END IF; END IF; IF bok = 1 THEN CALL ComplianceTableAdd(pPersonID,ComplianceLinkedDomainID,AtDate,DupeCheck,Cnt,pClientCode) END IF END FOR END }