====== pears.ComplianceTable ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."ComplianceTable"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "pPersonID" char(20),in "pComplianceDomainID" char(20) default null,in "AtDate" date default null,in "pClientCode" char(12) default null ) 
result( "Description" char(200),"LongDescription" long varchar,"Behaviour" tinyint,"Status" tinyint,"Score" smallint,"AchievedScore" smallint,"Info" char(100),"TagLocation" char(3),"TagID" char(3),"TagChoiceID" char(4),"Type" char(10) ) 
begin
  -- Result.Status values 0=ok 1=fail 2=warn
  declare "DupeCheck" long varchar;
  declare "Cnt" smallint;
  declare "bFound" tinyint;
  declare local temporary table "ComplianceTmpTbl"(
    "Description" char(200) null,
    "LongDescription" long varchar null,
    "Behaviour" tinyint null,
    "Status" tinyint null,
    "Score" smallint null,
    "AchievedScore" smallint null,
    "Info" char(100) null,
    "SortOrder" smallint null,
    "TagLocation" char(3) null,
    "TagID" char(3) null,
    "TagChoiceID" char(4) null,
    "Type" char(10) null,) not transactional;
  set "AtDate" = "isnull"("AtDate",current date); --Explicit null may be passed in so we don't put the default on the parameter 
  if "pComplianceDomainID" is null then
    for "PFOR" as "PCUR" no scroll cursor for
      select "ComplianceDomainID" from "CompliancePersonDomain" where "PersonID" = "pPersonID" for read only
    do
      set "bFound" = 1;
      call "ComplianceTableAdd"("pPersonID","ComplianceDomainID","AtDate","DupeCheck","Cnt","pClientCode")
    end for;
    if "bFound" is null then
      for "PFOR1" as "PCUR1" no scroll cursor for
        select "cd"."ComplianceDomainID" from "ComplianceDomain" as "cd" join "Search" as "s" on "cd"."DepartmentID" = "s"."DepartmentID" where "s"."PersonID" = "pPersonID" and "cd"."Name" like 'Default%'
          and("cd"."ClientTempComplianceCode" is null or "cd"."ClientTempComplianceCode" = "pClientCode") for read only
      do
        call "ComplianceTableAdd"("pPersonID","ComplianceDomainID","AtDate","DupeCheck","Cnt","pClientCode")
      end for
    end if
  else call "ComplianceTableAdd"("pPersonID","pComplianceDomainID","AtDate","DupeCheck","Cnt","pClientCode")
  end if;
  select "Description","LongDescription","Behaviour","Status","Score","AchievedScore","Info","TagLocation","TagID","TagChoiceID","Type" from "ComplianceTmpTbl" order by "SortOrder" asc
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."ComplianceTable" IS 
{create procedure ComplianceTable 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

( in "pPersonID" char(20),in "pComplianceDomainID" char(20) default null,in "AtDate" date default null,in pClientCode char(12) default null ) 
result( "Description" char(200),"LongDescription" long varchar,"Behaviour" tinyint,"Status" tinyint,"Score" smallint,"AchievedScore" smallint,"Info" char(100),"TagLocation" char(3),"TagID" char(3),"TagChoiceID" char(4), "Type" char(10) ) 
begin
  -- Result.Status values 0=ok 1=fail 2=warn
  declare "DupeCheck" long varchar;
  declare "Cnt" smallint;
  declare "bFound" tinyint;
  declare local temporary table "ComplianceTmpTbl"(
    "Description" char(200) null,
    "LongDescription" long varchar null,
    "Behaviour" tinyint null,
    "Status" tinyint null,
    "Score" smallint null,
    "AchievedScore" smallint null,
    "Info" char(100) null,
    "SortOrder" smallint null,
    "TagLocation" char(3) null,
    "TagID" char(3) null,
    "TagChoiceID" char(4) null, 
    "Type" char(10) null, ) not transactional;
  set "AtDate" = "isnull"("AtDate",current date); --Explicit null may be passed in so we don't put the default on the parameter 
  if "pComplianceDomainID" is null then
    for "PFOR" as "PCUR" no scroll cursor for
      select "ComplianceDomainID" from "CompliancePersonDomain" where "PersonID" = "pPersonID" for read only
    do
      set "bFound" = 1;
      call "ComplianceTableAdd"("pPersonID","ComplianceDomainID","AtDate","DupeCheck","Cnt",pClientCode)
    end for;
    if "bFound" is null then
      for "PFOR1" as "PCUR1" no scroll cursor for
        select "cd"."ComplianceDomainID" from "ComplianceDomain" as "cd" join "Search" as "s" on "cd"."DepartmentID" = "s"."DepartmentID" where "s"."PersonID" = "pPersonID" and "cd"."Name" like 'Default%' 
          and (cd.ClientTempComplianceCode is null or cd.ClientTempComplianceCode=pClientCode) for read only
      do
        call "ComplianceTableAdd"("pPersonID","ComplianceDomainID","AtDate","DupeCheck","Cnt",pClientCode)
      end for
    end if
  else call "ComplianceTableAdd"("pPersonID","pComplianceDomainID","AtDate","DupeCheck","Cnt",pClientCode)
  end if;
  select "Description","LongDescription","Behaviour","Status","Score","AchievedScore","Info","TagLocation","TagID","TagChoiceID","Type" from "ComplianceTmpTbl" order by "SortOrder" asc
end
}
</code>