====== pears.ComplianceDeskFetch ======
<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"."ComplianceDeskFetch"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "pTempDeskID" char(20),"pDepartmentID" char(2),"pStaffIDField" char(30),"pstaffid" char(20) ) 
begin
  declare "errMsg" long varchar;
  declare "warnMsg" long varchar;
  declare "currScore" double;
  declare "possScore" double;
  declare "stat" tinyint;
  declare "percent" double;
  declare "bActive" tinyint;
  declare "infoplus" char(105);
  declare "personcurrentstates" char(30);
  declare "StaffField" char(30);
  declare local temporary table "TemppoolComplianceTmpTable"(
    "personid" char(20) null,) not transactional;set "StaffField" = "lower"("isnull"("pStaffIDField",''));
  set "personcurrentstates" = '['+"isnull"("nullif"("WPKGetSwitchValue"('TDEXPIRYCOMPSTATES',''),''),"WPKGetSwitchValue"('PERSONCURRENTSTATES',''))+']';
  if("StaffField" = 'staffid2') then
    insert into "TemppoolComplianceTmpTable"( "personid" ) select distinct "person"."personid" as "pPersonID" from "TempPoolMember" key join("person","Tempdesk") where "temppoolmember"."tempdeskid" like "pTempDeskID"
        and "person"."status" like "personcurrentstates" and "isnull"("person"."divisionid",'') = any(select "divisionid" from "DivisionsAllowed")
        and "tempdesk"."departmentid" = "pDepartmentid" and "person"."staffid2" = "pstaffid"
  else if("StaffField" = 'compliancestaffid') then
      insert into "TemppoolComplianceTmpTable"( "personid" ) select distinct "person"."personid" as "pPersonID" from "TempPoolMember" key join("person","Tempdesk") where "temppoolmember"."tempdeskid" like "pTempDeskID"
          and "person"."status" like "personcurrentstates" and "isnull"("person"."divisionid",'') = any(select "divisionid" from "DivisionsAllowed")
          and "tempdesk"."departmentid" = "pDepartmentid" and "person"."compliancestaffid" = "pstaffid"
    else if("StaffField" = 'staffid') then
        insert into "TemppoolComplianceTmpTable"( "personid" ) select distinct "person"."personid" as "pPersonID" from "TempPoolMember" key join("person","Tempdesk") where "temppoolmember"."tempdeskid" like "pTempDeskID"
            and "person"."status" like "personcurrentstates" and "isnull"("person"."divisionid",'') = any(select "divisionid" from "DivisionsAllowed")
            and "tempdesk"."departmentid" = "pDepartmentid" and "person"."staffid" = "pstaffid"
      else
        insert into "TemppoolComplianceTmpTable"( "personid" ) select distinct "person"."personid" as "pPersonID" from "TempPoolMember" key join("person","Tempdesk") where "temppoolmember"."tempdeskid" like "pTempDeskID"
            and "person"."status" like "personcurrentstates" and "isnull"("person"."divisionid",'') = any(select "divisionid" from "DivisionsAllowed")
            and "tempdesk"."departmentid" = "pDepartmentid"
      end if end if end if;
  delete from "CompliancePersonStatus" where "tempdeskid" = "ptempdeskid"; -- This field might legitimately contain %
  for "PFOR" as "PCUR" no scroll cursor for
    select distinct "personid" as "pPersonID" from "TemppoolComplianceTmpTable" for read only do
    set "errMsg" = null;
    set "warnMsg" = null;
    set "currScore" = 0;
    set "possScore" = 0;
    set "bActive" = 0;
    for "PFOR1" as "PCUR1" no scroll cursor for
      select "Description","Status","Info","Score","AchievedScore"
        from "ComplianceTable"("pPersonID") for read only do
      set "bActive" = 1;
      if "Info" = 'Missing' then
        set "infoplus" = ''
      else
        set "infoplus" = ' '+"info"
      end if;
      case "status"
      when 1 then set "errMsg" = "string"("errMsg","trim"("Description"),"infoplus",'. ')
      when 2 then set "warnMsg" = "string"("warnMsg","trim"("Description"),"infoplus",'. ')
      end case;
      set "currScore" = "currScore"+"isnull"("AchievedScore",0);
      set "possScore" = "possScore"+"isnull"("Score",0)
    end for;
    if "bActive" = 1 then
      if "warnMsg" is not null then
        set "warnMsg" = "string"('Warning: ',"warnMsg")
      end if;
      if "errMsg" is not null then
        set "stat" = 1
      else if "warnMsg" is not null then
          set "stat" = 2
        else
          set "stat" = 0
        end if end if;
      if "possScore" > 0 then
        set "percent" = ("currScore"/"possScore")*100
      else
        set "percent" = 0
      end if;
      insert into "CompliancePersonStatus"( "personid","tempdeskid","description","status","percentage","errordescription","warningdescription" ) values
        ( "pPersonID","pTempDeskID","string"("errMsg","warnMsg"),"stat","percent","errmsg","warnmsg" ) 
    end if
  end for
end
go

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

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

(  in pTempDeskID char(20), pDepartmentID char(2), pStaffIDField char(30), pstaffid char(20)  ) 
begin
  declare errMsg long varchar;
  declare warnMsg long varchar;
  declare currScore double;
  declare possScore double;
  declare stat tinyint;
  declare percent double;
  declare bActive tinyint;
  declare infoplus char(105);
  declare personcurrentstates char(30);
  declare StaffField char(30); 
  declare local temporary table TemppoolComplianceTmpTable ( personid char(20)) not transactional; 
  set StaffField = lower(isnull (pStaffIDField,'')); 
  set personcurrentstates = '['+isnull(nullif(WPKGetSwitchValue ('TDEXPIRYCOMPSTATES',''),''), WPKGetSwitchValue ('PERSONCURRENTSTATES','') ) +']';
  if (StaffField='staffid2') then
      insert into  TemppoolComplianceTmpTable  (personid)  select distinct person.personid as pPersonID from TempPoolMember key join (person, Tempdesk) where temppoolmember.tempdeskid like pTempDeskID 
      and person.status like personcurrentstates and isnull(person.divisionid,'') = any(select divisionid from DivisionsAllowed) 
     and tempdesk.departmentid = pDepartmentid and person.staffid2 = pstaffid 
  else if  (StaffField='compliancestaffid') then    
      insert into TemppoolComplianceTmpTable  (personid) select distinct person.personid as pPersonID from TempPoolMember key join (person, Tempdesk) where temppoolmember.tempdeskid like pTempDeskID 
       and person.status like personcurrentstates and isnull(person.divisionid,'') = any(select divisionid from DivisionsAllowed) 
      and tempdesk.departmentid = pDepartmentid and person.compliancestaffid = pstaffid 
  else if  (StaffField='staffid') then
      insert into TemppoolComplianceTmpTable  (personid) select distinct person.personid as pPersonID from TempPoolMember key join (person, Tempdesk) where temppoolmember.tempdeskid like pTempDeskID 
      and person.status like personcurrentstates and isnull(person.divisionid,'') = any(select divisionid from DivisionsAllowed) 
      and tempdesk.departmentid = pDepartmentid and person.staffid =  pstaffid
  else 
      insert into TemppoolComplianceTmpTable  (personid) select distinct person.personid as pPersonID from TempPoolMember key join (person, Tempdesk) where temppoolmember.tempdeskid like pTempDeskID 
      and person.status like personcurrentstates and isnull(person.divisionid,'') = any(select divisionid from DivisionsAllowed) 
      and tempdesk.departmentid = pDepartmentid ; 
   end if end if end if  ; 
  delete from CompliancePersonStatus where tempdeskid = ptempdeskid; -- This field might legitimately contain %
  for PFOR as PCUR no scroll cursor for
    select distinct personid as pPersonID from TemppoolComplianceTmpTable for read only do
    set errMsg = null;
    set warnMsg = null;
    set currScore = 0;
    set possScore = 0;
    set bActive = 0;
    for PFOR1 as PCUR1 no scroll cursor for
      select Description,Status,Info,Score,AchievedScore
        from ComplianceTable(pPersonID) for read only do
      set bActive = 1;
      if Info = 'Missing' then
        set infoplus = ''
      else
        set infoplus = ' '+info
      end if;
      case status
      when 1 then set errMsg = string(errMsg,trim(Description),infoplus,'. ')
      when 2 then set warnMsg = string(warnMsg,trim(Description),infoplus,'. ')
      end case;
      set currScore = currScore+isnull(AchievedScore,0);
      set possScore = possScore+isnull(Score,0)
    end for;
    if bActive = 1 then
      if warnMsg is not null then
        set warnMsg = string('Warning: ',warnMsg)
      end if;
      if errMsg is not null then
        set stat = 1
      else if warnMsg is not null then
          set stat = 2
        else
          set stat = 0
        end if end if;
      if possScore > 0 then
        set percent = (currScore/possScore)*100
      else
        set percent = 0
      end if;
      insert into CompliancePersonStatus( personid,tempdeskid,description,status,percentage,errordescription, warningdescription )
          values( pPersonID,pTempDeskID,string(errMsg,warnMsg),stat,percent,errmsg, warnmsg ) 
    end if
  end for
end
}
</code>