pears.NetDashboardObjects

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

CREATE PROCEDURE "pears"."NetDashboardObjects"( IN "pWebUserID" CHAR(20),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 ) 
// IQXWeb
BEGIN
  DECLARE "uStaffID" CHAR(20);
  DECLARE "uLevel" SMALLINT;
  SELECT "u"."staffid","isnull"("s"."ReportView",0) INTO "uStaffID","uLevel" FROM "iqxnetuser" AS "u" KEY JOIN "staff" AS "s" WHERE "u"."iqxnetuserid" = "pWebUserID";
  SELECT "d"."DashboardObjectID",'' AS "UserName","d"."Type","d"."Title","d"."Descriptor","d"."Shared","d"."AccessLevel","dd"."onDashboard","dd"."onHomePage" 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" = "dd"."onDashboard" OR "pHomePage" = "dd"."onHomePage") UNION ALL
  SELECT "d"."DashboardObjectID","s"."name" AS "UserName","d"."Type","d"."Title","d"."Descriptor","d"."Shared",NULL,"dd"."onDashboard","dd"."onHomePage" 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" = "dd"."onDashboard" OR "pHomePage" = "dd"."onHomePage") AND "isnull"("s"."divisionid",'') = any(SELECT "DivisionID" FROM "DashboardStaffDivisions"())
    ORDER BY "UserName" ASC,"Title" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetDashboardObjects" IS 
{CREATE PROCEDURE pears.NetDashboardObjects( IN pWebUserID CHAR(20), 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) 
// IQXWeb
BEGIN
  DECLARE uStaffID CHAR(20);
  DECLARE uLevel SMALLINT;
  SELECT u.staffid, isnull(s.ReportView,0) INTO uStaffID, uLevel FROM iqxnetuser u KEY JOIN staff s WHERE u.iqxnetuserid = pWebUserID;
  SELECT d.DashboardObjectID,'' AS UserName, d.Type, d.Title, d.Descriptor, d.Shared, d.AccessLevel, dd.onDashboard, dd.onHomePage FROM DashboardObject d LEFT OUTER JOIN DashboardDisplayedObject dd ON dd.DashboardObjectID=d.DashboardObjectID AND dd.staffid=uStaffid WHERE d.staffid=uStaffID AND (d.DashboardObjectID=pDashboardObjectID OR pAll=1 OR pDashboard=dd.onDashboard OR pHomePage=dd.onHomePage)
  UNION ALL
  SELECT d.DashboardObjectID,s.name AS UserName, d.Type, d.Title, d.Descriptor, d.Shared, NULL, dd.onDashboard, dd.onHomePage FROM DashboardObject d KEY JOIN staff s, DashboardObject d LEFT OUTER JOIN DashboardDisplayedObject 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=dd.onDashboard OR pHomePage=dd.onHomePage) AND isnull(s.divisionid,'') IN (SELECT DivisionID FROM DashboardStaffDivisions())
  ORDER BY UserName, Title
END
}