====== pears.DashboardObjects ======
<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"."DashboardObjects"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "pDashboardObjectID" char(20) default null,in "pAll" smallint default null,in "pDashboard" smallint default null,in "pHomePage" smallint default null ) 
result( "DashboardObjectID" char(20),"UserName" char(60),"Type" char(50),"Title" char(250),"Descriptor" long varchar,"Shared" smallint,"AccessLevel" smallint,"OnDashboard" smallint,"OnHomePage" smallint,"Interval" smallint ) 
begin
  declare "uStaffID" char(20);
  declare "uLevel" smallint;
  declare "uDashInterval" smallint;
  declare "uHomeInterval" smallint;
  select "s"."staffid","isnull"("s"."ReportView",0),"s"."dashboardRefreshInterval","s"."homepageRefreshInterval" into "uStaffID","uLevel","uDashInterval","uHomeInterval" from "staff" as "s" where "s"."staffid" = "userstaffid";
  select "d"."DashboardObjectID",'' as "UserName","d"."Type","d"."Title","d"."Descriptor","d"."Shared","d"."AccessLevel","dd"."onDashboard","dd"."onHomePage",case when "pDashboard" = 1 then "uDashInterval" when "pHomePage" = 1 then "uHomeInterval" end
    from "DashboardObject" as "d" left outer join "DashboardDisplayedObject" as "dd" on "dd"."DashboardObjectID" = "d"."DashboardObjectID"
      and "dd"."staffid" = "uStaffid" where "d"."staffid" = "uStaffID" and("d"."DashboardObjectID" = "pDashboardObjectID" or "pAll" = 1
    or("pDashboard" = 1 and "dd"."onDashboard" > 0) or("pHomePage" = 1 and "dd"."onHomePage" > 0)) union all
  select "d"."DashboardObjectID","s"."name" as "UserName","d"."Type","d"."Title","d"."Descriptor","d"."Shared",null,"dd"."onDashboard","dd"."onHomePage",case when "pDashboard" = 1 then "uDashInterval" when "pHomePage" = 1 then "uHomeInterval" end
    from "DashboardObject" as "d" key join "staff" as "s","DashboardObject" as "d" left outer join "DashboardDisplayedObject" as "dd"
      on "dd"."DashboardObjectID" = "d"."DashboardObjectID" and "dd"."staffid" = "uStaffid" where "d"."staffid" <> "uStaffID" and "d"."Shared" = 1
    and "uLevel" >= "isnull"("d"."AccessLevel",0) and("d"."DashboardObjectID" = "pDashboardObjectID" or "pAll" = 1
    or("pDashboard" = 1 and "dd"."onDashboard" > 0) or("pHomePage" = 1 and "dd"."onHomePage" > 0))
    and "isnull"("s"."divisionid",'') = any(select "DivisionID" from "DashboardStaffDivisions"())
    order by "UserName" asc,"Title" asc
end
go

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

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

( in pDashboardObjectID char(20) default null,in pAll smallint default null,in pDashboard smallint default null,in pHomePage smallint default null ) 
result( DashboardObjectID char(20),UserName char(60),Type char(50),Title char(250),Descriptor long varchar,Shared smallint,AccessLevel smallint,OnDashboard smallint,OnHomePage smallint,Interval smallint ) 
begin
  declare uStaffID char(20);
  declare uLevel smallint;
  declare uDashInterval smallint;
  declare uHomeInterval smallint;
  select s.staffid,isnull(s.ReportView,0),s.dashboardRefreshInterval,s.homepageRefreshInterval into uStaffID,uLevel,uDashInterval,uHomeInterval from staff as s where s.staffid = userstaffid;
  select d.DashboardObjectID,'' as UserName,d.Type,d.Title,d.Descriptor,d.Shared,d.AccessLevel,dd.onDashboard,dd.onHomePage,case when pDashboard=1 then uDashInterval when pHomePage=1 then uHomeInterval end 
  from DashboardObject as d left outer join DashboardDisplayedObject as dd on dd.DashboardObjectID = d.DashboardObjectID and 
  dd.staffid = uStaffid where d.staffid = uStaffID and(d.DashboardObjectID = pDashboardObjectID or pAll = 1 
  or (pDashboard = 1 and dd.onDashboard > 0) or (pHomePage = 1 and dd.onHomePage > 0)) union all
  select d.DashboardObjectID,s.name as UserName,d.Type,d.Title,d.Descriptor,d.Shared,null,dd.onDashboard,dd.onHomePage,case when pDashboard=1 then uDashInterval when pHomePage=1 then uHomeInterval end 
  from DashboardObject as d key join staff as s,DashboardObject as d left outer join DashboardDisplayedObject as dd 
  on dd.DashboardObjectID = d.DashboardObjectID and dd.staffid = uStaffid where d.staffid <> uStaffID and d.Shared = 1 
  and uLevel >= isnull(d.AccessLevel,0) and(d.DashboardObjectID = pDashboardObjectID or pAll = 1 
  or (pDashboard = 1 and dd.onDashboard > 0) or (pHomePage = 1 and dd.onHomePage > 0)) 
  and isnull(s.divisionid,'') = any(select DivisionID from DashboardStaffDivisions())
    order by UserName asc,Title asc
end
}
</code>