====== pears.ComplianceTableAdd ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
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
}