Hi,
We’re on Oracle 10g here, and looking back at the upgrade documents I have found the following notes relating to issues encountered:
Views to be created post upgrade if they failed during upgrade…interestingly you’ve most of them listed above:
– Create Views
CREATE VIEW RX56.CONTENTVARIANTS AS
SELECT T.TEMPLATE_ID VARIANTID,
C.CONTENTTYPEID,
T.LABEL VARIANTDESCRIPTION,
T.STYLESHEETNAME,
‘…/assembler/render’ ASSEMBLYURL,
T.OUTPUTFORMAT,
T.AATYPE,
T.DESCRIPTION,
T.PUBLISHWHEN,
T.LOCATIONPREFIX,
T.LOCATIONSUFFIX
FROM RX56.PSX_TEMPLATE T,
RX56.CONTENTTYPES C,
RX56.PSX_CONTENTTYPE_TEMPLATE CT
WHERE T.TEMPLATE_ID = CT.TEMPLATE_ID
AND C.CONTENTTYPEID = CT.CONTENTTYPEID;
/
create view RX56.PSX_COMMUNITY_PERMISSION_VIEW as
select acl.OBJECTID, acl.OBJECTTYPE, com.COMMUNITYID, aclperm.PERMISSION
from RX56.PSX_ACLS acl,
RX56.PSX_ACLENTRIES entry,
RX56.PSX_ACLENTRYPERMISSIONS aclperm,
RX56.RXCOMMUNITY com
where acl.ID = entry.ACLID
and entry.ID = aclperm.ENTRYID
and entry.TYPE = 10
and entry.NAME = com.NAME
and aclperm.PERMISSION = 40
union
select acl.OBJECTID, acl.OBJECTTYPE, com.COMMUNITYID, aclperm.PERMISSION
from RX56.PSX_ACLS acl,
RX56.PSX_ACLENTRIES entry,
RX56.PSX_ACLENTRYPERMISSIONS aclperm,
RX56.RXCOMMUNITY com
where acl.ID = entry.ACLID
and entry.ID = aclperm.ENTRYID
and entry.TYPE = 10
and entry.NAME = ‘AnyCommunity’
and aclperm.PERMISSION = 40
and com.NAME not in (select entry2.NAME
from RX56.PSX_ACLENTRIES entry2
where acl.ID = entry2.ACLID
and entry2.TYPE = 10);
/
create view RX56.RXSITECOMMUNITY as
select p.OBJECTID SITEID, p.COMMUNITYID
from RX56.PSX_COMMUNITY_PERMISSION_VIEW p
where p.OBJECTTYPE = 9
and p.PERMISSION = 40;
/
create view RX56.RXCONTENTTYPECOMMUNITY as
select p.OBJECTID CONTENTTYPEID, p.COMMUNITYID
from RX56.PSX_COMMUNITY_PERMISSION_VIEW p
where p.OBJECTTYPE = 2
and p.PERMISSION = 40;
/
create view RX56.RXVARIANTCOMMUNITY as
select p.OBJECTID VARIANTID, p.COMMUNITYID
from RX56.PSX_COMMUNITY_PERMISSION_VIEW p
where p.OBJECTTYPE = 4
and p.PERMISSION = 40;
/
create view RX56.RXWORKFLOWCOMMUNITY as
select p.OBJECTID WORKFLOWAPPID, p.COMMUNITYID
from RX56.PSX_COMMUNITY_PERMISSION_VIEW p
where p.OBJECTTYPE = 23
and p.PERMISSION = 40;
/
create view RX56.PSX_DISPLAYFORMATPROPERTY_VIEW as
select *
from RX56.PSX_DISPLAYFORMATPROPERTIES
union
select p.OBJECTID PROPERTYID,
‘sys_community’ PROPERTYNAME,
ltrim(to_char(p.COMMUNITYID)) PROPERTYVALUE,
‘from acls’ DESCRIPTION
from RX56.PSX_COMMUNITY_PERMISSION_VIEW p
where p.OBJECTTYPE = 31;
/
create view RX56.PSX_SEARCHPROPERTIES_VIEW as
select *
from RX56.PSX_SEARCHPROPERTIES
union
select p.OBJECTID PROPERTYID,
‘sys_community’ PROPERTYNAME,
ltrim(to_char(p.COMMUNITYID)) PROPERTYVALUE,
‘from acls’ DESCRIPTION
from RX56.PSX_COMMUNITY_PERMISSION_VIEW p
where p.OBJECTTYPE = 15
OR p.OBJECTTYPE = 18;
/
create view RX56.PSX_MENUVISIBILITY_VIEW AS
select *
from RX56.RXMENUVISIBILITY
where VISIBILITYCONTEXT <> ‘2’
union
select a.ACTIONID,
‘2’ VISIBILITYCONTEXT,
ltrim(to_char(C.COMMUNITYID)) VALUE,
‘from acls’ DESCRIPTION
from (select a2.actionid actionid
from RX56.RXMENUACTION a2
where (select count(*)
from RX56.PSX_ACLS ACL
where OBJECTTYPE = 107
and OBJECTID = a2.ACTIONID) <> 0) a,
RX56.RXCOMMUNITY C
where C.COMMUNITYID NOT IN (select COMMUNITYID
FROM RX56.PSX_COMMUNITY_PERMISSION_VIEW p
where p.OBJECTTYPE = 107
and p.OBJECTID = a.ACTIONID
and p.PERMISSION = 40);
/
CREATE VIEW RX56.PSX_RELATIONSHIPS AS
SELECT RL.RID,
CASE RN.CONFIG_NAME
WHEN ‘NewCopy’ THEN
‘New Copy’
WHEN ‘PromotableVersion’ THEN
‘Promotable Version’
WHEN ‘ActiveAssembly’ THEN
‘Active Assembly’
WHEN ‘ActiveAssembly-Mandatory’ THEN
‘Active Assembly - Mandatory’
WHEN ‘Translation-Mandatory’ THEN
‘Translation - Mandatory’
WHEN ‘FolderContent’ THEN
‘Folder Content’
ELSE
RN.CONFIG_NAME
END AS CONFIG,
RL.OWNER_ID AS OWNERID,
RL.OWNER_REVISION AS OWNERREVISION,
RL.DEPENDENT_ID AS DEPENDENTID,
RL.DEPENDENT_REVISION AS DEPENDENTREVISION,
‘’ AS DESCRIPTION
FROM RX56.PSX_OBJECTRELATIONSHIP RL, RX56.PSX_RELATIONSHIPCONFIGNAME RN
WHERE RL.CONFIG_ID = RN.CONFIG_ID;
/
No guarantees…but you might want to give it a go recreating these views.
Good luck