Let's say that you are drunk one night (or day) and think to yourself "Oracle be damned, I don't want this fancy Financial Reporting Web Studio I want the thick client!" So you open up some version of the Financial Reporting Studio client you have lying around on an old server and try to connect to your EPM 11.2 environment. What's the worst that could happen?
Well, okay, yeah, that does make sense. Perhaps you open the "About" dialog to try to figure out what is going on, to be greeted with this:
In this case there are multiple pieces of information we need to be aware of. The "Installed Build" is the actual version of FR Studio we have installed. In this case we have FR Studio 11.1.2.4.710 which equates to build 11.1.2.4.200. If you are wondering why you clearly haven't been working with EPM long enough ;).
The "Synchronized Build" is actually the version of the FR libraries installed alongside FR Studio. So if your FR Studio JARs are out of date with your environment you will also receive the error above.
The "Report Server Build" is what version of FR the environment you are connected to thinks it is.
If you were to leave it there nobody would think any lesser of you. But perhaps you might think "how does FR Studio know which version it is? Can we change it ourselves?".
I should say that this is all very much not supported. If things don't work then tough luck. But saying that we may as well use the very latest version of FR Studio that Oracle released.
To do that we need to download Financial Reporting patch 11.1.2.4.712 - patch ID 30670918. Once the ZIP has been downloaded you need the following file from it: 30670918\files\common\epmstatic\reporting_analysis\client\FinancialReportingStudio.exe. Please note that this actually installs FR Studio 11.1.2.4.711 - go figure :)
I'd recommend not installing this in the usual "C:\Oracle\Middleware" directory tree. It's old so you don't want it messing up your existing installations. It also bundles a version of Java 6 with it, so be aware! We'll assume from this point that we installed Financial Reporting Studio 11.1.2.4.711 to C:\FRStudio\.
Next we need to edit a JAR file, I would recommend using 7-Zip for that. First make a backup of C:\FRStudio\common\financialreporting\11.1.2.0\lib\HReports.jar. Then open the JAR file in 7-Zip and once inside edit the file under META-INF\MANIFEST.MF in a text editor. We need to edit the version strings in this file to match the "Report Server Version" string.
The original file will look like the below:
We need to amend this file to look like the below:
Once you have saved the file we can open up FR Studio again. In the About dialog things should look a little different now:
Points to note are the certification of SQL Server 2017 and the remove of Essbase Studio.
To download the files needed for the release you need to head over to Oracle eDelivery: https://edelivery.oracle.com
The below table details which files have changed from the initial release with bold:
ZIP File Name
Download Description
SHA1 Hash
V44413-01.zip
Oracle WebLogic Server 12.1.3.0.0 for Microsoft Windows x64 (64-bit)
02B2EB70A5B208EE20083E9DA833983D98F00DD5
V44425-01.zip
Oracle Data Integrator 12.1.3.0.0 for Microsoft Windows x64 (64-bit)
A4B97264516F1A75AA33F5FAA0D49AAB32C0558C
V886440-01.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) SOA Suite and Business Process Management
8D65D949C862CB4652D2D28D69BEEDD4DF918B39
V886442-01_1of2.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) SOA Quick Start (Part 1)
2B308A22414861073937345379CBEAE6346A37FA
V886442-01_2of2.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) SOA Quick Start (Part 2)
E2F05CA1060C266D119FEF7055158354FAF638D8
V886451-01_1of2.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) Data Integrator (Part 1)
D80310331C0003F27752AB4AEB8747A3FB609DB3
V886451-01_2of2.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) Data Integrator (Part 2)
76450D1FC463B7EBF2057C6C548D694409854211
V933015-01.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) Internet Directory for Microsoft Windows x64 (64-bit)
Oracle Enterprise Performance Management System - Client Installers 11.2.6.0.0
66A1A78C6E2E70E002B57BED8116D92AB64CA903
V1010597-01.zip
Oracle Data Relationship Management Analytics 11.2.6.0.0
7F7706EABBC82E21FB655F263D97444A11C2ECE1
V1010604-01.zip
Oracle Enterprise Performance Management System - Part 1 11.2.6.0.0
146D7423C2108E92CD164D6FE5073E7F879207CC
V1010606-01.zip
Oracle Enterprise Performance Management System - Part 2 11.2.6.0.0
DBCE7AC44F37BAD9E8713FDE5E1658375C9A45BF
V1010601-01.zip
Oracle Enterprise Performance Management System - Oracle HTTP Server 11.2.6.0.0
552795B5B0F479727EA88376D803ACF0C4B5E2CF
V1010607-01.zip
Oracle Enterprise Performance Management System - Part 3 11.2.6.0.0
DBCE7AC44F37BAD9E8713FDE5E1658375C9A45BF
V1010609-01.zip
Oracle Enterprise Performance Management System - Part 4 11.2.6.0.0
0ECFBAAB1AD673DB5CB4E6DE7B4D63A92A2FD783
V1010612-01.zip
Oracle Enterprise Performance Management System - Part 5 11.2.6.0.0
9E6489891B6BA1BB480BBB3425DF3A8A5E57979D
V1010614-01.zip
Oracle Enterprise Performance Management System - Part 6 11.2.6.0.0
B227BA1EBD1BFEB2846FF0DFC4E48AE05D413C53
V1010596-01.zip
Oracle Data Relationship Management 11.2.6.0.0
5A27A4EAA3EE790C9EB63AF65F4C0DD77D7001F2
V1010752-01.zip
Oracle Enterprise Performance Management System - Installation Documents and Readmes 11.2.6.0.0
3462FB62AEE699977FCDCD41E515F6C32EDC148F
I have a recommendation for people who have issues with Oracle's download manager or using the wget option - if you use Firefox as your browser you can install the Download Star add-in to download the ZIP files in an organised manner. Use the naming mask ${text} and choose to skip on conflict. Alternatively if you wish to use DownThemAll to download the files then use the renaming mask *text*.*ext*, it will automatically skip duplicates.
You only really need to download the ZIP files below. As before, use sha1sum to calculate the hashes:
Two points to note that are potentially very interesting are the "delivery of" Fusion Middleware 12.2.1.4 and Java version 1.8.0_251 and the certification of SQL Server 2017. It isn't clear how exactly the Middleware will be upgraded to 12.2.1.4,
To download the files needed for the release you need to head over to Oracle eDelivery: https://edelivery.oracle.com
The below table details which files have changed from the initial release with bold:
ZIP File Name
Download Description
SHA1 Hash
V44413-01.zip
Oracle WebLogic Server 12.1.3.0.0 for Microsoft Windows x64 (64-bit)
02B2EB70A5B208EE20083E9DA833983D98F00DD5
V44425-01.zip
Oracle Data Integrator 12.1.3.0.0 for Microsoft Windows x64 (64-bit)
A4B97264516F1A75AA33F5FAA0D49AAB32C0558C
V886440-01.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) SOA Suite and Business Process Management
8D65D949C862CB4652D2D28D69BEEDD4DF918B39
V886442-01_1of2.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) SOA Quick Start (Part 1)
2B308A22414861073937345379CBEAE6346A37FA
V886442-01_2of2.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) SOA Quick Start (Part 2)
E2F05CA1060C266D119FEF7055158354FAF638D8
V886451-01_1of2.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) Data Integrator (Part 1)
D80310331C0003F27752AB4AEB8747A3FB609DB3
V886451-01_2of2.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) Data Integrator (Part 2)
76450D1FC463B7EBF2057C6C548D694409854211
V933015-01.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) Internet Directory for Microsoft Windows x64 (64-bit)
Oracle Enterprise Performance Management System - Client Installers 11.2.5.0.0
D248837A92A5C694CA8C7544685046EFB8A3F7EF
V1008703-01.zip
Oracle Data Relationship Management Analytics 11.2.5.0.0
7F7706EABBC82E21FB655F263D97444A11C2ECE1
V1008716-01.zip
Oracle Enterprise Performance Management System - Part 1 11.2.5.0.0
DF6C1CBFBCC1E7F9AF6A8800184231C1BEE0533D
V1008720-01.zip
Oracle Enterprise Performance Management System - Part 2 11.2.5.0.0
518AAF30F52E219607DDD91B60F19BEC0A5CA5B1
V1008713-01.zip
Oracle Enterprise Performance Management System - Oracle HTTP Server 11.2.5.0.0
ACE46B4148C3C7ABA315616BEE3823CF38A4472B
V1008735-01.zip
Oracle Enterprise Performance Management System - Part 3 11.2.5.0.0
C9BFA2B8A748082EDF87C3DADEFCECDF226FCC1F
V1008724-01.zip
Oracle Enterprise Performance Management System - Part 4 11.2.5.0.0
1FDEC70A53B98C139EF380C352F6ED3928ACE172
V1008711-01.zip
Oracle Enterprise Performance Management System - Part 5 11.2.5.0.0
0C9ACBB4DEE7D8AB6EDA7509E086CB908337736D
V1008708-01.zip
Oracle Enterprise Performance Management System - Part 6 11.2.5.0.0
0D8362E10742DBDB2204AFA31E0E97194FE7A769
V1008702-01.zip
Oracle Data Relationship Management 11.2.5.0.0
7854110905ECDEACCB8504D844ACD652C26C4AA0
V1008701-01.zip
Oracle Enterprise Performance Management System - Installation Documents and Readmes 11.2.5.0.0
BFA99F8B9636D8AA7737E17A3C8AB2612BA6483E
I have a recommendation for people who have issues with Oracle's download manager or using the wget option - if you use Firefox as your browser you can install the Download Star add-in to download the ZIP files in an organised manner. Use the naming mask ${text} and choose to skip on conflict.
You only really need to download the ZIP files below. As before, use sha1sum to calculate the hashes:
Oracle Enterprise Performance Management System - Client Installers 11.2.4.0.0
2277D5254BF3A9AE34F338E7EEA8E6A76DA91B49
V1006313-01.zip
Oracle Data Relationship Management Analytics 11.2.4.0.0
B3AD6C166547E80F090D97DC8B162777E00E4138
V1006444-01.zip
Oracle Enterprise Performance Management System - Part 1 11.2.4.0.0
0E0B19CB59977A98C898DA1261116B3ECA19B4B9
V1006393-01.zip
Oracle Enterprise Performance Management System - Part 2 11.2.4.0.0
E0CDC5716E1B6C739B823A569142C6EE89DBE689
V1006446-01.zip
Oracle Enterprise Performance Management System - Oracle HTTP Server 11.2.4.0.0
77AAEFCF1F47CDEB2CEDCD4028F19178A27F562F
V1006394-01.zip
Oracle Enterprise Performance Management System - Part 3 11.2.4.0.0
EFFAB6DCD9F3D0EC5B2A02ABAB11A1539C3690C6
V1003501-01.zip
Oracle Enterprise Performance Management System - Part 4 11.2.4.0.0
6D83526A61C48262D84C4AC1B353A7823D16960A
V1006398-01.zip
Oracle Enterprise Performance Management System - Part 5 11.2.4.0.0
99A9F85F052AC4F9D2D0123C8944582094F42418
V1006405-01.zip
Oracle Enterprise Performance Management System - Part 6 11.2.4.0.0
204CC0D92B424054CB8F8F156136D51E28CF62A1
V1006312-01.zip
Oracle Data Relationship Management 11.2.4.0.0
FEA40603924505BAC8B966B41F69B13B2141B72D
V1006299-01.zip
Oracle Enterprise Performance Management System - Installation Documents and Readmes 11.2.4.0.0
7B1E17575F71F618AF263A41FC07DAE0C855F148
I have a recommendation for people who have issues with Oracle's download manager or using the wget option - if you use Firefox as your browser you can install the Download Star add-in to download the ZIP files in an organised manner. Use the naming mask ${text} and choose to skip on conflict.
You only really need to download the ZIP files below. As before, use sha1sum to calculate the hashes:
To download the files needed for the release you need to head over to Oracle eDelivery: https://edelivery.oracle.com
It appears that the downloads are correctly labelled as 11.2.3, unlike previous releases.
The below table details which files have changed from the initial release with bold:
ZIP File Name
Download Description
SHA1 Hash
V44413-01.zip
Oracle WebLogic Server 12.1.3.0.0 for Microsoft Windows x64 (64-bit)
02B2EB70A5B208EE20083E9DA833983D98F00DD5
V44425-01.zip
Oracle Data Integrator 12.1.3.0.0 for Microsoft Windows x64 (64-bit)
A4B97264516F1A75AA33F5FAA0D49AAB32C0558C
V886440-01.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) SOA Suite and Business Process Management
8D65D949C862CB4652D2D28D69BEEDD4DF918B39
V886442-01_1of2.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) SOA Quick Start (Part 1)
2B308A22414861073937345379CBEAE6346A37FA
V886442-01_2of2.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) SOA Quick Start (Part 2)
E2F05CA1060C266D119FEF7055158354FAF638D8
V886451-01_1of2.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) Data Integrator (Part 1)
D80310331C0003F27752AB4AEB8747A3FB609DB3
V886451-01_2of2.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) Data Integrator (Part 2)
76450D1FC463B7EBF2057C6C548D694409854211
V933015-01.zip
Oracle Fusion Middleware 12c (12.2.1.3.0) Internet Directory for Microsoft Windows x64 (64-bit)
Oracle Enterprise Performance Management System - Client Installers 11.2.3.0.0
E63DF3CCC7ED315A00BF66CEA0498818A4BDA7FD
V1003513-01.zip
Oracle Data Relationship Management Analytics 11.2.3.0.0
F1F115867DA2F441D2D2BC439570B4A2DEE63A99
V1003485-01.zip
Oracle Enterprise Performance Management System - Part 1 11.2.3.0.0
E3299DCC46A1B08F97CADD31C41B663B071EE8B3
V1003498-01.zip
Oracle Enterprise Performance Management System - Part 2 11.2.3.0.0
E3299DCC46A1B08F97CADD31C41B663B071EE8B3
V1003508-01.zip
Oracle Enterprise Performance Management System - Oracle HTTP Server 11.2.3.0.0
629A0198925BC2C267B7B2E65DD33CE9A1959AF5
V1003511-01.zip
Oracle Enterprise Performance Management System - Part 3 11.2.3.0.0
1DB1806D8F0DCBBC1DA61E8AA2D88E94A72B4E79
V1003501-01.zip
Oracle Enterprise Performance Management System - Part 4 11.2.3.0.0
6D83526A61C48262D84C4AC1B353A7823D16960A
V1003503-01.zip
Oracle Enterprise Performance Management System - Part 5 11.2.3.0.0
823E7ABBC3319D025011C43B76DD87CFDC92153E
V1003505-01.zip
Oracle Enterprise Performance Management System - Part 6 11.2.3.0.0
3C80681AF980434A4A75EE9407C1CEF40ACB54CD
V1003512-01.zip
Oracle Data Relationship Management 11.2.3.0.0
D11AA06B942C2EA58745FED32DEEBCBEE70C28CF
V1003532-01.zip
Oracle Enterprise Performance Management System - Installation Documents and Readmes 11.2.3.0.0
FB021DD0A0102603815B936EC0682CF1682331E8
I have a recommendation for people who have issues with Oracle's download manager or using the wget option - if you use Firefox as your browser you can install the Download Star add-in to download the ZIP files in an organised manner. Use the naming mask ${text} and choose to skip on conflict.
You only really need to download the ZIP files below. As before, use sha1sum to calculate the hashes:
An issue is described for Active Directory authentication in the document "Oracle Enterprise Performance Management System Tips and Tricks from EPM System Infrastructure Development: Issues 103 and Higher" as well as Oracle Support Document 2482392.1. The error that is shown is "EPMCSS-05318: Failed to validate Security Configuration. Failed to connect. Invalid values for host or port. Enter valid value(s)" and the cause is that TLS 1.2 is not supported by JDK 160_35. The given solution is to upgrade the version of the JDK to 1.7.0_161.
However I recently came across another method to support TLS 1.2 on versions of Java that do not otherwise support it. The advantage to this method is that it does not require large changes to the infrastructure I found this method on a Stack Overflow answer and can confirm that it does work.
The first step is to take a backup of the correct files in the JDK/JRockIt folders. For JRockIt the folder would be similar to: E:\Oracle\Middleware\jrockit_160_37\jre\lib\security\ and the files to backup are:
Drop these JAR files into the /jre/lib/ext/ folder - for the JRockIt installation this would be similar to: E:\Oracle\Middleware\jrockit_160_37\jre\lib\ext\.
Finally go to the java.security file, for the JRockIt installation this would be: E:\Oracle\Middleware\jrockit_160_37\jre\lib\security\java.security. Open the file and look for the list of security providers. By default this section will look like this:
After these changes all that is required is to restart the WebLogic servers and TLS 1.2 will be used by default.
Here is a snippet from Netmon showing how with this change, TLS 1.2 being used for the LDAP connection to MSAD:
Versus what happens without this change:
What we have done is install the Bouncy Castle security provider and unlock greater strength for cryptographic functions.
By default Java is constrained in what security it can provide, for example we are limited to AES keys with a maximum size of 128 bits. The policy files we downloaded makes sure that Java isn't limited by these default restrictions. These policy files are only valid for versions of Java lower than 8u161, 7u171 and 6u181 - versions of Java later than this already use the unlimited policy files.
The second part - and the most important - is that now Bouncy Castle is handling all SSL communications, instead of the default JRE providers. Bouncy Castle is a library to provide access to a wide range of cryptographic functions, and it uses the Java Cryptographic Extension (JCE) technology to transparently provide these to the JRE. Bouncy Castle supports not just TLS 1.2 but lots of other algorithms, whereas the default JCE provider has a more limited selection.
You can see how using Bouncy Castle we are able to support a wider range of cryptographic functions in EPM 11.1.2.x. Given that this is done at the JRE level it can be done for any JRE and as seen in the Stack Overflow answer, is generic to all Java installations.
Edit: Please note that if you set SSL certificates in WebLogic this will also upgrade the protocols used on the SSL listening ports from TLS 1.0 to TLS 1.2! This can cause issues with OHS, since OHS 11.1.1.7 (the version shipped with 11.1.2.3 and 11.1.2.4) can only support up to TLS 1.0.
Admittedly this is a pretty niche topic but I thought I may as well post it anyway.
On our EPM 11.1.2.3 system we had a mechanism whereby users could upload documents to Workspace and then our batch jobs could run with those files as parameters. In order to do this we would have to reconcile the folder and filename in R&A Workspace with the folder and filename in the RM1 folder. This required getting information out of the database and creating it as a view.
So in 11.1.2.3 a typical view entry would look like the below:
The definition of the view is available below.
/*
MAKE SURE TO CREATE SYNONYMS FIRST! EXAMPLES BELOW:
IF OBJECT_ID('dbo.s_RAF_CONTAINER') IS NOT NULL DROP SYNONYM [dbo].[s_RAF_CONTAINER]
GO
CREATE SYNONYM [dbo].[s_RAF_CONTAINER] FOR [RAF].[dbo].[V8_CONTAINER]
GO
IF OBJECT_ID('dbo.s_RAF_FOLDER') IS NOT NULL DROP SYNONYM [dbo].[s_RAF_FOLDER]
GO
CREATE SYNONYM [dbo].[s_RAF_FOLDER] FOR [RAF].[dbo].[V8_FOLDER]
GO
*/
IF OBJECT_ID('dbo.V_BATCH_FILE_LOCATION_FILE') IS NOT NULL DROP VIEW dbo.V_BATCH_FILE_LOCATION_FILE;
GO
CREATE VIEW dbo.V_BATCH_FILE_LOCATION_FILE AS
WITH ContainerAll AS (
SELECT
[CONTAINER_UUID] AS UUID,
[PARENT_FOLDER_UUID] AS ParentUUID,
[NAME] AS Name,
[LAST_MODIFIED_DATE] AS LastModified,
[OWNER_LOGIN] AS Owner,
[IS_FOLDER]
FROM
[dbo].[s_RAF_CONTAINER]
WHERE
[OWNER_LOGIN] IS NOT NULL
),
Container AS (
SELECT
UUID,
ParentUUID,
Name
FROM
ContainerAll
WHERE
[IS_FOLDER] = 0
),
[File] AS (
SELECT
UUID,
ParentUUID,
Name,
LastModified,
Owner
FROM
ContainerAll
WHERE
[IS_FOLDER] = 1
),
ContainerChild AS (
SELECT
c.UUID,
c.ParentUUID,
c.Name,
CAST('\' + (c.Name) as varchar(255)) AS Path
FROM
Container c
WHERE
c.ParentUUID IS NULL
UNION ALL
SELECT
c.UUID,
c.ParentUUID,
c.Name,
CAST((c2.Path + '\' + c.Name) as varchar(255)) AS Path
FROM
Container c
INNER JOIN ContainerChild c2 on c.ParentUUID = c2.UUID
),
Path AS (
SELECT
cc.UUID,
REPLACE(SUBSTRING(cc.Path, 6, LEN(cc.Path)), '\', '/') AS Path,
fs.FOLDER_FULL_PATH,
CASE
WHEN fs.FOLDER_FULL_PATH LIKE '/\%' THEN
REPLACE(SUBSTRING(fs.FOLDER_FULL_PATH, 2, LEN(fs.FOLDER_FULL_PATH)), '\', '/')
WHEN fs.FOLDER_FULL_PATH LIKE '/%' THEN
REPLACE('\' + SUBSTRING(fs.FOLDER_FULL_PATH, 2, LEN(fs.FOLDER_FULL_PATH)), '\', '/')
ELSE
REPLACE(fs.FOLDER_FULL_PATH, '\', '/')
END AS FileSystemPath
FROM
ContainerChild cc
JOIN [dbo].[s_RAF_FOLDER] fs ON cc.UUID = fs.CONTAINER_UUID
WHERE
cc.UUID IN (SELECT ParentUUID FROM [File])
)
SELECT
f.UUID,
f.Name,
p.Path,
p.FileSystemPath,
p.Path + '/' + f.Name AS FilePath,
p.FileSystemPath + '/' + f.UUID + '.1' AS FileFileSystemPath,
f.Owner,
f.LastModified
FROM
[File] f
JOIN Path p ON f.ParentUUID = p.UUID
WHERE
f.ParentUUID NOT IN ('CORPWORKSPACEPAGEFOLDER', 'DATASOURCESFOLD',
'REPORTMART', 'Script_Repository', 'SSR_TEMPLATE',
'SYSTEM', 'USERPOVFOLD')
Now in 11.2 the RA repository is no more - but there is something similar. So we wanted to create functionality that could do the same thing. In 11.2 the tables are very, very different. The file itself (in fact most document properties) are saved as hex values in the database table, so additionally we would have to change our scripts to pull the file data out of the table first and then run operations on it.
The document repository tables relevant for us are:
REPOSITORY_OBJECTS - gives us the UUID of a repository object, the object type, parent object, owner and last modified date
REPOSITORY_OBJECT_PROPERTIES - allows us to view the properties of an object, encoded as a hex string - properties include the name, description, file size, original filename and content
REPOSITORY_OBJECT_PROP_INDEX - is essentially the properties of the properties of an object, giving us the data type and string value for properties of a certain object
So seeing the data in these tables I knew we would be able to build a view to give the information we needed. The tables above had the information we needed - the object hierarchy, object names, UUIDs, owners and last modified dates. The only thing I needed to do was pull all the data together.
I wanted to keep the exact same view columns so I knew I would have to somehow change the existing view definition into something that can be used with 11.2. Now I am not the best SQL coder so I am sure there are much better ways of achieving the same result but I managed to adapt the view, so now it will give a view entry like the below:
The new definition of the view is available below.
/*
MAKE SURE TO CREATE SYNONYMS FIRST! EXAMPLES BELOW:
IF OBJECT_ID('dbo.s_DOCREP_OBJECTS') IS NOT NULL DROP SYNONYM [dbo].[s_DOCREP_OBJECTS]
GO
CREATE SYNONYM [dbo].[s_DOCREP_OBJECTS] FOR [HSS].[dbo].[REPOSITORY_OBJECTS]
GO
IF OBJECT_ID('dbo.s_DOCREP_OBJECT_PROP_INDEX') IS NOT NULL DROP SYNONYM [dbo].[s_DOCREP_OBJECT_PROP_INDEX]
GO
CREATE SYNONYM [dbo].[s_DOCREP_OBJECT_PROP_INDEX] FOR [HSS].[dbo].[REPOSITORY_OBJECT_PROP_INDEX]
GO
*/
IF OBJECT_ID('dbo.V_BATCH_FILE_LOCATION_FILE') IS NOT NULL DROP VIEW dbo.V_BATCH_FILE_LOCATION_FILE;
GO
CREATE VIEW dbo.V_BATCH_FILE_LOCATION_FILE AS
WWITH ContainerAll AS (
SELECT
DISTINCT do.[OBJECT_UUID] AS UUID,
do.[PARENT_FOLDER_UUID] AS ParentUUID,
dopi.STRING_VALUE AS Name,
do.LAST_ACCESSED AS LastModified,
do.AUTHOR AS Owner,
CASE
WHEN do.TYPE = 'FOLDER' THEN 0
ELSE 1
END AS IS_FOLDER
FROM
[dbo].[s_DOCREP_OBJECTS] as do
INNER JOIN
[dbo].[s_DOCREP_OBJECT_PROP_INDEX] as dopi
ON do.OBJECT_UUID=dopi.OBJECT_UUID COLLATE Latin1_General_CI_AS
WHERE
KEYWORD='NAME' AND NOT do.TYPE IN ('des', 'rog', 'kbk', 'dso', 'pov', 'rpt', 'bch', 'bjo')
),
Container AS (
SELECT
UUID,
ParentUUID,
Name
FROM
ContainerAll
WHERE
[IS_FOLDER] = 0
),
[File] AS (
SELECT
UUID,
ParentUUID,
Name,
LastModified,
Owner
FROM
ContainerAll
WHERE
[IS_FOLDER] = 1
),
ContainerChild AS (
SELECT
c.UUID,
c.ParentUUID,
c.Name,
CAST('\' + (c.Name) as varchar(999)) AS Path
FROM
Container c
UNION ALL
SELECT
c.UUID,
c.ParentUUID,
c.Name,
CAST((c2.Path + '\' + c.Name) as varchar(999)) AS Path
FROM
Container c
INNER JOIN ContainerChild c2 on c.ParentUUID = c2.UUID
),
Path AS (
SELECT
cc.UUID,
REPLACE(cc.Path, '\', '/') AS Path
FROM
ContainerChild cc
WHERE
cc.UUID IN (SELECT ParentUUID FROM [File])
)
SELECT
f.UUID COLLATE Latin1_General_CI_AS AS UUID,
f.Name COLLATE Latin1_General_CI_AS AS Name,
p.Path COLLATE Latin1_General_CI_AS AS Path,
FileSystemPath = NULL,
p.Path + '/' + f.Name COLLATE Latin1_General_CI_AS AS FilePath,
FileFileSystemPath = NULL,
f.Owner COLLATE Latin1_General_CI_AS AS Owner,
f.LastModified
FROM
[File] f
JOIN Path p ON f.ParentUUID = p.UUID
WHERE
f.ParentUUID NOT IN ('CORPWORKSPACEPAGEFOLDER', 'DATASOURCESFOLD',
'REPORTMART', 'Script_Repository', 'SSR_TEMPLATE',
'SYSTEM', 'USERPOVFOLD', 'ROOTfr', '')
AND p.Path + '/' + f.Name LIKE '/Root/%'
Hopefully this information will prove useful to someone! If someone finds a way to optimise the view and suggest improvements I am very open to that. For us the next step is to create a function that pulls the related object out of the SQL tables. So the query would do something like:
SELECT PROPERTY_VALUE FROM [HSS].[dbo].[REPOSITORY_OBJECT_PROPERTIES]
WHERE OBJECT_UUID = '...' AND PROPERTY_NAME = 'jcr:data'
