IF EXISTS (SELECT * FROM sys.Procedures WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}GetTabsByPackageID]'))
    DROP PROCEDURE {databaseOwner}[{objectQualifier}GetTabsByPackageID]
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetTabsByPackageID]
    @PortalID  Int, -- Null for Host menu items
    @PackageId Int, -- Not Null!
    @ForHost   Bit  -- 0: Get pages for a specific portal (or host pages only)
                    -- 1: Get all host pages and portal pages
AS
BEGIN
    WITH TabV AS (SELECT V.TabId, D.ModuleId 
                   FROM (SELECT ROW_NUMBER() OVER (PARTITION BY TabId ORDER BY Version DESC) AS RowNumber, TabVersionId, TabId
                           FROM {databaseOwner}[{objectQualifier}TabVersions] WHERE IsPublished = 1) V
                   JOIN {databaseOwner}[{objectQualifier}TabVersionDetails] D ON (V.TabVersionId = D.TabVersionId AND D.Action != 2 AND V.RowNumber =1))
    SELECT * FROM {databaseOwner}[{objectQualifier}vw_Tabs]
     WHERE (IsNull(PortalId, -1) = IsNull(@PortalID, -1) Or @ForHost = 1)
       AND IsDeleted = 0
       AND TabId IN (SELECT M.TabId FROM {databaseOwner}[{objectQualifier}vw_Modules] M
                      INNER JOIN {databaseOwner}[{objectQualifier}DesktopModules] DM ON M.DesktopModuleID = DM.DesktopModuleID
                      LEFT  JOIN TabV ON TabV.TabId = M.TabId AND TabV.ModuleId = M.ModuleID
                      WHERE DM.PackageID = @PackageId AND (M.IsDeleted = 0 OR (TabV.TabId IS NOT NULL AND TabV.ModuleId IS NOT NULL)))
    ORDER BY PortalID, TabName
    OPTION (OPTIMIZE FOR (@PortalID UNKNOWN, @PackageId UNKNOWN, @ForHost UNKNOWN));
END; -- PROCEDURE

IF EXISTS (SELECT * FROM sys.Procedures WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}GetModulePackagesInUse]'))
    DROP PROCEDURE {databaseOwner}[{objectQualifier}GetModulePackagesInUse]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetModulePackagesInUse]
    @PortalID Int,  -- Null|-1 for Host menu items
    @ForHost  Bit   -- 0: Get in use for portal or host only
                    -- 1: Get in use for all host pages and portal pages
AS
BEGIN
    WITH TabV AS (SELECT V.TabId, D.ModuleId 
                   FROM (SELECT ROW_NUMBER() OVER (PARTITION BY TabId ORDER BY Version DESC) AS RowNumber, TabVersionId, TabId
                           FROM {databaseOwner}[{objectQualifier}TabVersions] WHERE IsPublished = 1) V
                   JOIN {databaseOwner}[{objectQualifier}TabVersionDetails] D ON (V.TabVersionId = D.TabVersionId AND D.Action != 2 AND V.RowNumber = 1))
    SELECT * FROM {databaseOwner}[{objectQualifier}Packages]
     WHERE PackageID IN (SELECT DISTINCT PackageID
                          FROM      {databaseOwner}[{objectQualifier}vw_ModulesOnTabs] M
                          LEFT JOIN TabV ON M.TabId = TabV.TabId AND TabV.ModuleId = M.ModuleID
                          WHERE (IsNull(M.PortalID, -1) = ISNull(@PortalID, -1) OR (@ForHost = 1)) -- scope
                            AND (M.IsDeleted = 0 OR TabV.TabId IS NOT NULL))
    ORDER BY FriendlyName
    OPTION (OPTIMIZE FOR (@PortalID UNKNOWN, @ForHost = 0));
END; -- PROCEDURE